Лучшие практики анализа данных подразумевают под собой работу со значениями, представленными в плоской структуре (в плоской таблице). Это структура, в которой каждый столбец отвечает за свой вид данных. И именно такую структуру плоской таблицы мы формируем, когда создаем сводную таблицу (PivotTable).
В работе мы сталкиваемся не только с плоскими, но и другими табличными структурами, в которых расположены данные. В качестве примера рассмотрим таблицу, в которой закреплен оклад сотрудников в разрезе должностей и отделов:
Чтобы использовать и проанализировать данные из таблицы выше, ее необходимо преобразовать в плоскую структуру, которая будет состоять из трех столбцов: Должность, Отдел, Оклад.
Для решения этой задачи можно использовать функционал PowerQuery, а можно применить функцию MS Excel: Индекс() + Поискпоз() (англ. версия: INDEX() + MATCH()).
Итак, формула, которая позволит сформировать данные в плоской структуре, для нашего примера будет выглядеть таким образом:
Рассмотрим формулу детально: =ИНДЕКС(Данные!A:F;ПОИСКПОЗ(A2;Данные!A:A;0);ПОИСКПОЗ(B2;Данные!$1:$1;0))
- ИНДЕКС(Данные!A:F;) – данная функция определяет массив искомых значений
- ПОИСКПОЗ(A2;Данные!A:A;0) – первая функция Поискпоз() определяет искомую строку – это порядковый номер записи по строкам
- ПОИСКПОЗ(B2;Данные!$1:$1;0) — вторая функция Поискпоз() определяет искомый столбец – это порядковый номер записи по столбцам.
Таким образом, сочетание функций Индекс() + Поискпоз() помогает быстро определить искомую информацию в заданном диапазоне и преобразовать данные в плоскую структуру для последующего анализа.
Хотите получить инструменты MS Excel по суммированию, настройке, извлечению данных для создания отчетности, справочников, аналитических справок, дашбордов? Участвуйте в тренинге «Анализ данных и визуализация: продвинутый МS Excel»
