У меня есть большая таблица данных о персонале, и я хотел бы создать таблицы на других листах с отфильтрованными наборами данных из основного набора данных. У меня есть опыт в написании обширных макросов VBA, и я мог бы сделать то, что мне нужно, с базой данных и SQL за считанные минуты, но у меня такое чувство, будто я иду по муравейнику с ботинком.
Я попробовал сделать это с помощью сводной таблицы, однако у меня нет опыта работы со сводными таблицами, и я не смог получить результаты, которые бы меня удовлетворили.
Большой набор данных о персонале содержит такие данные, как дата начала работы, текущий отдел, набор навыков специалистов и т. д. (обычные данные, которые вы ожидаете получить).
Я хотел бы создать динамическую таблицу на других рабочих листах, которая, например, отображала бы всех сотрудников, работающих в отделе X, или всех сотрудников, имеющих навыки специалиста Y. Очевидно, я хочу, чтобы таблицы рабочих листов обновлялись при изменении или добавлении данных в основной набор данных.
Правильно ли я понимаю, что здесь есть простое решение или мне нужно искать его, используя реальную базу данных?
решение1
Я хотел бы предложить метод, основанный на функции Excel, которая фильтрует/извлекает записи из исходной таблицы в другую.
Исходная таблица:
Как это работает:
- Если ячейки критериев пусты, то формула не возвращает запись.
- Формула работает как для одного, так и для нескольких критериев.
Ситуация 1:
Ситуация 2:
- Формула массива в ячейке
B31:
{=IFERROR(IF(AND(ISBLANK($B$27),ISBLANK($C$27),ISBLANK($D$27),ISBLANK($E$27),ISBLANK($F$27),ISBLANK($G$27),ISBLANK($H$27))," ",INDEX($B$2:$H$21,SMALL(IF(MMULT(($B$2:$H$21=$B$27:$H$27)*1,{1;1;1;1;1;1;1})=COUNTA($B$27:$H$27),MATCH(ROW($B$2:$B$21),ROW($B$2:$B$21)),""),ROWS($A$1:A1)),COLUMNS($A$1:A1))),"")}
- Закончите формулу сCtrl+Shift+Enterи заполнить поперек.
- Ключевой особенностью этого упражнения является
MMULTфункциональность. - MMULT (умножение матриц) возвращает матричное произведение двух массивов.
- Количество столбцов массива array1 равно количеству строк массива array2.
- Результат массива содержит то же количество строк, что и массив1, а также то же количество столбцов, что и массив2.
При необходимости измените ссылки на ячейки в формуле.