Вернуть несколько элементов из таблицы

Question

У меня есть файл Excel с двумя листами. В "Листе 1" у меня есть данные, которые отформатированы в виде таблицы. Он имеет три столбца:

MyData[Name]         MyData[Month]         MyData[sales]

На "Листе 2" я хочу выбрать несколько пунктов из MyData которые соответствуют критерию (что MyData[Name] знак равно John).

Я нашел это отличное видео, которое объясняет, как это сделать, с формулой массива, которая объединяет INDEX, SMALL, ROWS и IF. Проблема в том, что в видео используются регулярные диапазоны с жестко закодированными номерами строк.

Есть ли способ сделать этот выбор, но использовать имена столбцов вместо жестко закодированных диапазонов?

Answer 1

Да, хотя я не уверен насчет относительных ссылок с использованием структурированных ссылок, поэтому предложите две формулы массива, исходя из вашего критерия выбора (например, John) в А1 вашего второго листа:

В Б1:

=IF(ROWS(A$1:A1)<=COUNTIF(MyData[[#All],[Name]],$A$1),INDEX(MyData[[#All],[Month]],SMALL(IF(MyData[[#All],[Name]]=$A$1,ROW(MyData[[#All],[Name]])-ROW(MyData[[#Headers],[Name]])+1),ROWS(B$1:B1))),"")  

В С1:

 =IF(ROWS(B$1:B1)<=COUNTIF(MyData[[#All],[Name]],$A$1),INDEX(MyData[[#All],[sales]],SMALL(IF(MyData[[#All],[Name]]=$A$1,ROW(MyData[[#All],[Name]])-ROW(MyData[[#Headers],[Name]])+1),ROWS(C$1:C1))),"")  

оба вводятся на второй лист с помощью Ctrl+Shift+Enter и копируются до тех пор, пока значения не будут возвращены.

Answer 2

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

Стандартные формулы в G4:H4:

=IFERROR(INDEX(MyData[Month], AGGREGATE(15, 6, ROW(MyData)-ROW(MyData[#Headers])/(MyData[Name]=$F$4), ROW(1:1))), "")
=IFERROR(INDEX(MyData[Sales], AGGREGATE(15, 6, ROW(MyData)-ROW(MyData[#Headers])/(MyData[Name]=$F$4), ROW(1:1))), "")

При этом используется функция AGGREGATE¹ в ее МАЛЕНЬКОЙ форме, чтобы вызвать ошибку в любых несовпадающих строках и впоследствии игнорировать ошибки при извлечении нескольких совпадающих элементов.

_{Function Функция AGGREGATE была представлена ​​в Excel 2010. Она не была доступна в предыдущих версиях.}