Excel - Использование VLOOKUP для создания сводной таблицы

Question

Мне нужна небольшая помощь с некоторыми Excel.

Employee  Locations     Hours   OT
Mr.One    Station 1     40      6
Mrs.Seven Station 2     30      6
Mr.Two    Station 3     30      4
Mr.Three  Station 4     40      4
Mrs.Eight Station 1     32      6
Mr.Four   Station 2     32      7
Mrs.Nine  Station 3     40      6
Mr.Five   Station 4     40      7
Mr.Six    Station 1     25      2
Mrs.Ten   Station 2     40      3
Mr.Eleven Station 3     60      1

У меня есть таблица с двумя листами. Один из них представляет собой таблицу данных (показанную выше), а другой - сводную информацию, в которой столбец Locations является списком проверки данных. Я хочу использовать список проверки данных, чтобы вытащить всех людей и информацию из определенного места. Я пытался использовать VLOOKUP() но я знаю, как использовать его только для извлечения одного человека за раз, а не группы записей, специфичных для определенного местоположения.

Answer 1

Если вы используете Excel 2007 или более позднюю версию , вы можете использовать следующее решение (функция IFERROR является ограничивающим фактором). В примерной сводной таблице, показанной на снимке экрана ниже, я использовал эту формулу, заполнив несколько строк, чтобы вернуть сотрудников, которые соответствуют местоположению, выбранному из раскрывающегося списка в B2:

=IFERROR(INDEX(Data!$A$1:$A$12,SMALL(IF(Data!$B$1:$B$12=Summary!$B$1,ROW(Data!$A$1:$A$12),2000000),ROW()-3)),"")

Эта формула должна быть введена как формула массива. Сделайте это, нажав Ctrl+Shift+Enter.

В столбце часов эта формула массива была заполнена:

=IFERROR(INDEX(Data!$C$1:$C$12,SMALL(IF(Data!$B$1:$B$12=Summary!$B$1,ROW(Data!$C$1:$C$12),2000000),ROW()-3)),"")

В отношении формул следует обратить внимание на последний аргумент в функции SMALL ROW()-3 . Это возвращает 1 для первой записи в строке 4. Если ваша сводная таблица начинается с другой строки, вам необходимо настроить этот аргумент так, чтобы он возвращал 1 для первой строки вашей таблицы. Например, если ваша таблица начинается со строки 2, вам нужно изменить аргумент на ROW()-1 .