Лучший способ получить доступ к данным из другой книги Excel

Question

Что было бы лучшим способом получить доступ только для чтения к данным, хранящимся в другой книге Excel. Мне известны две возможности, каждая со своими плюсами и минусами.

Вариант 1) Ссылка на данные напрямую

Пример: = [test1.xls] Sheet1!$ A $ 1

• pro: данные обновляются при открытии книги
• Pro: работает, даже если ссылка на рабочую книгу закрыта
• con: операция не может быть управляема данными
• Pro: работает во всех таблицах, без ограничений безопасности

Вариант 2) Ссылка на данные с использованием опций INDIRECT и ADDRESS

Пример: = НЕПРЯМОЙ (АДРЕС (B7, B6, 1, ИСТИНА, B4))

где B7 содержит индекс строки, B6 содержит индекс столбца, B4 содержит имя рабочей книги / листа

• Pro: расположение данных может быть управляемым данными
• con: не работает, когда ссылка на книгу закрыта
• con: обходной путь для автоматической загрузки указанной книги не работает на уровне безопасности макросов по умолчанию

Answer 1

Если вы не делаете этого много или вам нужно 1 значение ячейки, как в вашем примере, вариант 1 - хороший способ. Если вам нужно:

1. Делайте такие вещи часто
2. Создавайте динамические запросы на основе того, что еще может происходить в книге (или принимайте пользовательский ввод)
3. Получить много данных, или фильтровать / сортировать данные, которые вы захватываете
4. Обработайте данные, которые вы вводите, прежде чем представлять их
5. и т.п.

Вы можете написать 1 или более макросов, которые используют ADO. Вы можете использовать ADO в Excel для извлечения данных из Excel, других баз данных или текстовых файлов в ваши электронные таблицы. Я использую это тонну. Поэтому у меня есть специальный модуль класса для обработки деталей для меня.

Answer 2

Если причина, по которой вы беспокоитесь о том, является ли метод управляемым данными, заключается в том, что вы обеспокоены устареванием ссылки при изменении данных в другой книге, вы можете избежать этой проблемы, используя именованный диапазон.

Чтобы создать именованный диапазон: В книге, в которой есть данные, выберите ячейки, в которых есть данные, щелкните в поле имени (где отображаются строка и столбец ячеек) и введите имя.

Ссылка на диапазон: для ссылки на именованный диапазон из другого листа:

'[MyData.xls] Лист1'!MyNamedRange

Если вам нужно конкретное значение, скажем, из таблицы, вы можете использовать различные функции поиска, такие как vlookup, hlookup, index, чтобы выбрать конкретное значение.

Например, если у меня была следующая таблица в книге «Sales.xls» на листе "Бакалея", и я определил именованный диапазон "tableSales" для всей таблицы,

Товар Продажа Бананы 343 Виноград 123 Яйца 756

Затем я могу посмотреть продажи "Яйца" в другой книге со следующим:

= ВПР ("Яйцо", '[Sales.xls] Бакалея'!tableSales, 2, ложь)

(первый параметр vlookup - это значение поиска, второй - диапазон таблицы, третий параметр указывает, какой столбец нужно извлечь, и если для 4-го задано значение true, он попытается найти приблизительное совпадение)