У меня есть лист Excel с необработанными данными, и я хочу сделать его в другой таблице для отчетов.
Как я могу перевести следующий псевдокод в Excel?
select ColA, ColB, ColC from SheetA where (SheetA.ColD in ("x","y","z") or SheetA.ColE in ("1","2","3"))
Я ищу решение в духе VLOOKUP MATCH и INDEX.
Скопировано из комментариев: в Excel 2010 (и других) вы можете создавать запросы к файлам Excel. В разделе "Получение внешних данных" ленты данных (слева) выберите "Из других источников", а затем "Из запроса Microsoft". У вас уже должна быть настройка источника данных для "Файлы Excel". После этого вы можете создать что-то в MS Query, которое будет работать.
Если вы действительно хотите получить формулу ответа, вам придется столкнуться с каким-то беспорядком. Во-первых, наиболее важным шагом для облегчения загрузки является добавление поля к исходным данным для обработки WHERE . Я собираюсь назвать этот ColF и его формула будет выглядеть примерно так:
=SUM(IFERROR(MATCH([@ColD],x,0),0),IFERROR(MATCH([@ColD],y,0),0),IFERROR(MATCH([@ColD],z,0),0),IFERROR(MATCH([@ColE],range1,0),0),IFERROR(MATCH([@ColE],range2,0),0),IFERROR(MATCH([@ColE],range3,0),0))>0
Для вашего пользовательского представления вам нужно создать другую таблицу с таким же количеством строк, что и в оригинале. В дополнение к ColA , ColB , ColC , вам может понадобиться поле для поиска. Вы можете поместить формулу в каждый столбец, но это просто раздуто без причины и замедлит вычисления. Часть поиска выглядит следующим образом (обратите внимание, что это формула массива, введенная с помощью CTRL+ALT+ENTER):
{=IFERROR(SMALL(IF(Table1[ColF],ROW(Table1[ColF])),ROW()-1),"")}
Это будет список всех номеров строк из ваших исходных данных, которые соответствуют критериям, определенным формулой в ColF . Там будет некоторое количество пробелов в конце. Следующие несколько полей просто INDEX на основе Row .
=IFERROR(INDEX(Table1[ColA],[@Row]-1),"")
=IFERROR(INDEX(Table1[ColB],[@Row]-1),"")
=IFERROR(INDEX(Table1[ColC],[@Row]-1),"")
Там твой стол. Вам придется разбираться с пустыми строками внизу любым удобным для вас способом. Автофильтр будет работать просто отлично. Как я указывал ранее, вы можете избавиться от поля Row , сделав другие формулы более длинными массивами:
{=IFERROR(INDEX(Table1[ColA],SMALL(IF(Table1[ColF],ROW(Table1[ColF])),ROW()-1)-1),"")}
... но это все замедлит.