Ссылка на динамический именованный диапазон между книгами

993
picobit

Я унаследовал эту структуру данных. У меня есть куча продуктов и информация о поставщиках, выложенная так:

Supplier Order Number Name Cost ACME 00123 A 1 ACME 00321 B 2 ACME 20798 C 3 ACME 11010 D 4 ACME 32333 E 5 ACME 20001 F 6 Babar 20001 G 7 Babar 54687 H 8 Babar 69441 I 9 Babar 65777 J 10

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

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

В этом примере у меня Range_ACME определен как:

= ДВССЫЛ ( "В" & MATCH (Лист1 $ A $ 2, Лист1 $ А:! $ А, 0) & ": D", & (MATCH (Лист1 $ A $ 2, Лист1 $ А:! $ А, 0) + COUNTIF (! Лист1 $ A: $ A, Лист1 $ A $ 2) -1))

(На самом деле, я использую другую именованную ячейку в качестве значения поиска MATCH, но я упростил ее для целей тестирования.) Так что я могу найти стоимость для элемента 00123 ACME следующим образом:

= ВПР ( "00123", Range_ACME, 2, ложь)

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

= Лист1 $ B $ 2: $ D $ 7

Ссылка на динамический именованный диапазон между книгами

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

1
Ответ Криса выполним, но я пока оставлю это без ответа. Я все еще хотел бы знать, возможно ли то, что я хочу сделать. Если это невозможно, то почему? picobit 8 лет назад 0

1 ответ на вопрос

2
Chris Rogers

Для простого решения этой проблемы и демонстрации того, что необходимо, я воссоздал предоставленные вами листы и создал именованные диапазоны в книге наборов данных ACME.xlsx относительно их заголовков столбцов данных.

  • поставщик =Sheet1!$A:$A
  • порядок =Sheet1!$B:$B
  • название =Sheet1!$C:$C
  • Стоимость =Sheet1!$D:$D

Затем в другой книге я создал точки входа для значений, которые вы хотите найти (Поставщик и номер заказа). Имя элемента, который вы хотите вернуть, находится в следующей точке входа.

Other Workbook

В ячейку B3создаваемой книги я вошел

=INDEX('C:\ACME.xlsx'!Name,MATCH(B1&B2,'C:\ACME.xlsx'!Supplier&'C:\ACME.xlsx'!Order,0))и нажал CTRL+ SHIFT+ENTER

Это дает нам желаемый результат. Теперь, если вы измените ячейку B1на ACME, имя элемента автоматически изменится на F, и вам не понадобится открывать книгу, содержащую структуру данных, если в формуле указан полный путь к файлу.

Итак, наш ответ: да. Объедините название поставщика и номер заказа. picobit 8 лет назад 0
Будучи формулой массива, это очень медленно. Простое применение формулы к 400 ячейкам и поиск по 500 элементам приводит к тому, что операции фильтра / редактирования занимают более 10-15 секунд на моем поколении 2 i7. picobit 8 лет назад 0

Похожие вопросы