© Сорокин Б.Б. 2016 г.

Группировка данных по нескольким параметрам

Проблема вот в чем, получается что ивановых две строчки, а козликовых три, и т.д. А как сделать что бы напротив одной строчки иванова, вставали в ячейки в столбиках с названиями даты получения спецодежды? Нужно не общее кол-во а так же по столбикам! Чтобы была только одна строчка Козликов, (и так остальные фамилии) но все его вещи были в одну строчку, в столбиках с названиями спецодежды стояла дата, если ему получать ее, либо если ему не получать - слово нет. вот это я не могу придумать.

Что бы понять суть вопроса необходимо посмотреть таблицу примера.

таблица примера

Из приведенного выше фрагмента таблицы видно что в столбцах A:A - G:G данные расположены вертикально. В столбцах H:H и далее, для получения удобной для использования формы, данные разворачивают горизонтально по наименованиям спецодежды. Естественно нужно, чтобы ФИО и спец одежда были сгруппированы в одну строку. В одной таблице этого не сделать.

Для решения проблемы исходную таблицу необходимо слегка переделать и разделить на две части: базу данных и выходную таблицу.

Первая часть таблицы (столбцы A:A - G:G) будет базой данных.

таблица базы данных

Первый столбец базы данных - ключевое поле с уникальными данными, которые позволяют связать между собой таблицу базы и выходную таблицу. Для достижения уникальности, ключевое поле формируются из значений трех полей "след. Дата выдачи", "таб N" и "спецодежда" с помощью функции СЦЕПИТЬ().

Оставшаяся часть - будет выходной таблицей.

выходная таблица

Для заполнения выходной таблицы достаточно заполнить поле "таб №" остальные данные будут отобраны в помощью функции ВПР() из базы данных и вспомогательной сводной таблицы и внесены в выходную форму.

Поле "фио" заполняется с помощью формулы =ВПР(A6;ТАБ_Н;2;ЛОЖЬ), где область ТАБ_Н - дополнительная область в базе данных для того, чтобы обеспечить положение первого столбца области полю "таб N" базы данных.

Данные в поля "Выдано" отбираются из допонительной сводной таблицы (область СТ) с помощью формулы.

формула формула поля выдано

Отбор выполняется на основе ключевого значения формируемого функцией СЦЕПИТЬ($A6;C$4). Если такое значение находится в области данных СТ то формула возвращает эти данные, если нет - формула возвращает значение "нет". Необходимость дополнительной сводной таблица вызвана тем, что в поле "Выдано" должны попадать только данные последней выдачи, а не других предыдущих, которые могут присутствовать в базе данных. Следовательно данные из базы необходимо сгруппировать по табельному номеру, спецодежде и отсортировать по дате выдачи по убыванию, так чтобы в первой строке оказалась последняя выдача.

область данных СТ

"Доп. Поле" это техническое поле обеспечивающее формирование ключевого поля в области СТ с использованием формулы.

=ЕСЛИ(C5=0;B4;C5)

Ключевое поле формируется формулой.

=СЦЕПИТЬ(B5;D5)

Данные в поля "След.Выдача" выходной таблицы отбираются непосредственно из базы данных с помощью формулы.

формула формула поля След.Выдача

Отбор выполняется из области "База_данных" на основе ключевого значения формируемого функцией СЦЕПИТЬ($B$1;$A6;C$4). Если такое значение находится то формула возвращает эти данные, если нет - формула возвращает значение "нет".

Для исключения технических ошибок при вводе данных в базу и отборе данных в выходную таблицу используются списки, которые создаются заранее и дополняются по мере необходимости.

списки

Список "СО" обеспечивает внесение значений в поле "спецодежда" базы данных с помощью выпадающего меню.

выпадающий список

Список "ГОД" обеспечивает выбор значения в поле "Год" выходной таблицы.

выпадающий список выходной таблицы

Области "База_данных", "ТАБ_Н", "СО", "СТ" лучше сразу задавать с запасом, чем потом долго и нервно искать почему выходная таблица выдает не все данные. А причина проста - добавленные данные вышли за пределы области. Соответственно надо увеличивать эти области, что скучно делать каждый раз, проще сразу указать с запасом.

Конечно, возможны и другие варианты решения задачи, но я придумал так. На таком принципе у меня работает большое количество выходных форм исходными данными для которых могут быть как базы формируемые в ручную, так и копируемые из выгрузки различных специальных программ типа 1С Бухгалтерия или М-Аптека Льгота.

Файл с примером можно скачать здесь.