☰

Список значений на основе критериев

tag-icon • tag-icon microsoft-excel array
Список значений на основе критериев

У меня на рабочем листе есть таблица под названием «DealSetup», которая выглядит следующим образом (упрощенная версия):

   A           B
1  John Doe    1
2  Jane Doe    1
3  Mark Doe    2
4  Doug Doe    1
5  Mary Doe    2

У меня есть таблица на рабочем листе под названием "List", в которую я хотел бы перетащить имена из рабочего листа "DealSetup". Однако я хочу только перечислить имена на основе значения в столбце B.

Например, на листе "List" я хотел бы использовать какой-то INDEX или функцию массива, чтобы просмотреть лист "DealSetup" и вытащить всех, у кого значение столбца B равно 1, и перечислить их. Но мне не нужны никакие пробелы. Это должно выглядеть так:

 EVERYONE WITH B COLUMN VALUE OF 1:  
   A  
1  John Doe
2  Jane Doe
3  Doug Doe

Или вот так:

 EVERYONE WITH B COLUMN VALUE OF 2:  
   A  
1  Mark Doe
2  Mary Doe

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

решение1

Предполагая, что ваши данные в DealSetup!A$2:B$6вы можете использовать эту формулу вList!A2

=IFERROR(INDEX(DealSetup!A$2:A$6,SMALL(IF(DealSetup!B$2:B$6=C$1,ROW(DealSetup!A$2:A$6)-ROW(DealSetup!A$2)+1),ROWS(A$2:A2))),"")

подтвердить с помощью CTRL+ SHIFT+ ENTERи копировать вниз до тех пор, пока требуется и далее. Когда у вас закончатся имена, вы получите пробелы.

Предполагает критерии (1,2 и т.д.) вC1

Смотри приложениерабочая тетрадь- попробуйте изменить C1на 2

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

=IFERROR(INDEX(DealSetup!A$2:A$6,SMALL(IF(DealSetup!B$2:B$6=C$1,IF(COUNTIF(A$1:A1,DealSetup!A$2:A$6)=0,ROW(DealSetup!A$2:A$6)-ROW(DealSetup!A$2)+1)),1)),"")

решение2

Если вам нужно решение с формулами, то у вас будут пробелы внизу. Нет способа заставить ячейки, содержащие формулу, расти и уменьшаться по мере изменения вашего списка. Ваша таблица с формулами должна быть той же высоты, что и ваши необработанные данные, чтобы вы ничего не пропустили, если, скажем, у всех есть столбец 1in B. Если вы знаете, что этого никогда не произойдет, вы можете уменьшить количество строк на Listлисте, но это рискованно. Вот формула, которая вернет их все по порядку с пробелами внизу. Это формула массива, поэтому введите ее с помощью Ctrl+ Shift+ Enter: (Барри Гудини уже представил похожее решение)

{=IFERROR(INDEX(DealSetup!$A:$A,SMALL(IF(DealSetup!$B:$B=1,ROW(DealSetup!$B:$B)),ROW())),"")}

Если вы не можете принять пробелы внизу, то это должно быть либо PivotTable, либо решение VBA. Этот вопрос довольно старый, поэтому я не ожидаю увидеть OP здесь в ближайшее время, к сожалению.

Связанный контент