У меня есть диапазон в Excel (B3:C8), из которого я хочу отфильтровать англичан. В SQL это было бы очень просто:
SELECT Persons FROM [myTable] WHERE Nationality = 'English'
Как я могу применить аналогичную фильтрацию к диапазону, где результатом является не одно значение, а диапазон?
Примечание: в Excel есть кнопка «Фильтр», но все, что он делает, это скрывает нежелательные строки. Я не хочу скрытых строк.
Вот как я хочу, чтобы мой стол выглядел так. Как должна выглядеть формула G3?
Чтобы перевести имена в диапазон, вы можете сделать свои данные таблицей, а затем создать сводную таблицу с национальностью в качестве фильтра отчета и персоной в качестве метки строки. Затем выберите английский из списка национальностей. Смотрите снимок экрана ниже (игнорируйте столбец D, поскольку он не использовался);
Введите это в G3 и перетащите вниз. Это формула массива, поэтому необходимо вводить с помощью Ctrl Shft Enter
=IFERROR(INDEX($B$3:$B$8,LARGE(($C$3:$C$8=$E$3)*(ROW($B$3:$B$8)-2),COUNTIF($C$3:$C$8,$E$3)-(ROWS($3:3)-1))),"")
Обратите внимание, что IfError доступен только в XL 2007/10, в противном случае вам нужно будет использовать:
=IF(ISERROR(INDEX($B$3:$B$8,LARGE(($C$3:$C$8=$E$3)*(ROW($B$3:$B$8)-2),COUNTIF($C$3:$C$8,$E$3)-(ROWS($3:3)-1)))),"",INDEX($B$3:$B$8,LARGE(($C$3:$C$8=$E$3)*(ROW($B$3:$B$8)-2),COUNTIF($C$3:$C$8,$E$3)-(ROWS($3:3)-1))))
Эта версия будет работать в любой версии Excel и дает результаты в указанном порядке
В G3:
=IF(ROWS(G$3:G3)>COUNTIF(C$3:C$8,E$3),"",INDEX(B$3:B$8,SMALL(IF(C$3:C$8=E$3,ROW(C$3:C$8)-ROW(C$3)+1),ROWS(G$3:G3))))
подтверждается с помощью сочетания клавиш CTRL+SHIFT+ENTER (нажата вместе) и копируется по мере необходимости
Одним из способов будет использование этого оператора IF = IF($ E $ 3 = C3, B3, ""), который даст вам имя человека, если национальность соответствует фильтру, или пробел, если нет.
=If($E$3=English,B3,"")
Вы можете сделать это более обобщенным, выполнив:
=If($E$3=C3,B3,"")