Excel - читать данные из другого xls-файла без копирования содержимого

В настоящее время у меня есть один исходный файл под названием SRC с индексом Ci и некоторыми столбцами Cn. Этот файл индексируется без дубликатов в первом столбце, а затем есть данные, которые я хочу прочитать из одного из столбцов в зависимости от ситуации. (Данные - это цены, даты и т. д. Для каждого индекса).

У меня есть некоторое количество целевых файлов Excel (назовите их A, B, C и т. д.), В которых я должен искать соответствующий столбец для каждого соответствующего индекса в моем исходном SRC.

В настоящее время я использую именованный диапазон и VLOOKUP, чтобы найти индекс соответствия и соответствующие данные:

VLOOKUP($A6;price_data;3;FALSE)

Это решение работает, но моя проблема в размере файла. Использование именованного диапазона или даже прямой ссылки на другой файл заставляет Excel включать данные из моего SRC в каждый из моих файлов назначения A, B, C, ...

Сам вопрос: Как искать данные из исходного файла, не увеличивая размер моих конечных файлов?

Дополнительный тест с двумя файлами: A) все еще имеет именованный диапазон, связанный с другим файлом, и B) я удалил именованный диапазон, не изменяя никаких уравнений. Затем переименовал оба файла в .zip, чтобы проверить содержимое.

Файл A содержит каталог xl / externalLinks с файлом externalLink1.xml, размер которого примерно такой же, как у моего исходного SRC.

Файл B не содержит xl / externalLinks, и его размер намного меньше. A = 1,2 МБ и B = 25 кБ.

3
0
163
2

Ответы 2

Один из вариантов - создать функцию vba и связать ее с открытым файлом. Он получит / обновит значения при открытии файла. вы получите то, что хотите, но открытие файла станет немного медленнее.

РЕДАКТИРОВАТЬ1

Это то, что я сделал

  1. Создал файл с именем main с одним столбцом, содержащим идентификаторы
  2. Создал еще один файл, назвал его детали и создал в нем две колонки идентификатор продукта и название продукта.
  3. Сохранены оба
  4. Проверил размеры файлов
  5. Открыл оба и вставил vlookup в главный загружаемый файл названия продуктов для идентификаторов, присутствующих в основном файле из файла сведений.
  6. Сохранил основной файл и закрыл оба
  7. Снова проверил размер файла

Только основной файл имеет другой размер

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

saikon 31.10.2018 16:01

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

usmanhaq 31.10.2018 16:21

Под «исходными данными» я подразумеваю файл, из которого мы получаем данные.

usmanhaq 31.10.2018 17:08

Да, я совершенно уверен, что это увеличивает размер файла. Провел тест с двумя файлами: A) по-прежнему имеет именованный диапазон, связанный с другим файлом, и B) я удалил именованный диапазон, не изменил никаких уравнений. Затем переименовал оба файла в .zip, чтобы проверить содержимое. Файл A содержит каталог xl / externalLinks с файлом externalLink1.xml, размер которого примерно такой же, как у моего исходного SRC. Файл B не содержит xl / externalLinks, и его размер намного меньше. A = 1,2 МБ и B = 25 кБ.

saikon 01.11.2018 06:13

Вы можете проверить мои шаги, которые я выполнил, я отредактировал ответ.

usmanhaq 01.11.2018 08:23

Та же проблема с вашей отредактированной процедурой. Всякий раз, когда используется ссылка на другой файл, данные копируются в файл под xl / externalLinks. Это не меняет результат при использовании ВПР напрямую без именованного диапазона между ними. Просто делает обновление ссылок намного более трудоемким.

saikon 01.11.2018 08:43

Данные копируются и в случае запроса мощности. запрос копирует таблицу на новый лист, на который можно ссылаться. Я думаю, попробуйте vba, если это не сработает, вы можете найти другие решения.

usmanhaq 01.11.2018 09:47

Найдите ниже решение VBA, вы можете связать эту функцию с открытой книгой, и она обновит ваши данные.

Я также обновил vba_lookup, он содержал некоторые ошибки, которые теперь исправлены.

Я тоже вставил тестовый звонок, можете проверить по своим данным.

Function vba_lookup(lookup_value, lookup_range As Range, column_position As Integer)

    Dim found_value As Range
    Dim returning_value As Range

On Error GoTo NOTFOUND

    Set found_value = lookup_range.Columns(1).Find(lookup_value, LookAt:=XlLookAt.xlWhole)
    Set returning_value = found_value.Offset(0, column_position - 1)
    vba_lookup = returning_value.Value
    Exit Function

NOTFOUND:
vba_lookup = "#N/A"

End Function

Sub test_vlookup_from_other_file()

Call vlookup_from_other_file("Main", "F2:F11", "A2:A11", 2, "C:\Users\user\Desktop", "temp.xlsx", "Sheet3", "E8:F16")

End Sub


Sub vlookup_from_other_file(main_sheet_name As String, paste_col_range As String, search_col_range As String, column_position As Integer, file_directory_path As String, file_name As String, file_sheet_name As String, lookup_range As String)


    'Application.ScreenUpdating = False
    Dim this_workbook As Workbook

    Set this_workbook = Application.ThisWorkbook

    Workbooks.Open Filename:=file_directory_path & "\" & file_name 

    this_workbook.Activate

    lookup_range = "[" & file_name & "]" & file_sheet_name & "!" & lookup_range

    row_end = Range(paste_col_range).Rows.Count

    For row_num = 1 To row_end
        Sheets(main_sheet_name).Range(paste_col_range).Cells(row_num, 1).Value = vba_lookup(Sheets(main_sheet_name).Range(search_col_range).Cells(row_num, 1).Value, Range(lookup_range), column_position)
    Next

    Windows(file_name).Close

    'Application.ScreenUpdating = True

End Sub

Аргументы функции следующие:

  • main_sheet_name: имя листа диапазона, в который функция будет вставлять полученные значения
  • paste_col_range: столбец, в который будут вставлены значения
  • search_col_range: столбец, в котором будет выполняться поиск
  • column_position: позиция столбца столбца данных, который будет возвращен при обнаружении совпадения
  • file_name: имя файла, в котором функция будет искать значения
  • file_directory_path: путь к каталогу указанного выше файла
  • file_sheet_name: имя листа указанного выше файла
  • lookup_range: диапазон, в котором функция должна искать значения

Частично это работает. Для find_index требуется .Row в конце. find_index = lookup_range.Columns(1).Find(lookup_value, LookAt:=XlLookAt.xlWhole).Row И эта функция работает только тогда, когда открыт отдельный источник данных, на который ссылается price_data. Он работает с '[filename.xlsx]Sheet1'!$A:$R, но не работает с 'path[filename.xlsx]Sheet1'!$A:$R, когда файл не открыт.

saikon 06.11.2018 13:21

это увеличивает размер? я не думаю, что так будет.

usmanhaq 06.11.2018 13:23

И это все равно увеличивает размер файла. Размер файла увеличивается, пока определено имя внешнего файла.

saikon 06.11.2018 13:28

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

usmanhaq 06.11.2018 13:44

Я обновил макрос, надеюсь, теперь он не увеличит размер файла.

usmanhaq 08.11.2018 08:56

Другие вопросы по теме