Возникла необходимость вести обработку данных. Ввиду наличия разрывных частей таблицы обработка требует дополнительных ручных манипуляций, а также затрудняет автоматизацию обработки статистики. Было решено убрать разделители, содержащие дату сдачи тестов и краткую сводку. Возникает проблема, что для каждого результата должна указываться эта дата, а также принимающий тестолог. Проблема решается добавлением колонки с этой информацией. Далее объясняется один из простых методов вынесения такой информации без применения знаний программирования.
Исходный документ изображён на рисунке далее.
![](https://habrastorage.org/files/a89/775/b08/a89775b08b514af3a3004b5822d3d215.png)
Хоть в примере и указывается работа только в листе с именем «Октябрь», конечный вариант предполагает слияние всех таких таблиц, а это два десятка листов. Поэтому ручное копирование с заголовков дат напротив каждого из результатов теста отнимает много времени.
Первым делом необходимо выделить весь документ ([Ctrl]+A, [Ctrl]+A) и применить фильтр. Т.к. заголовки дат занесены в объединённые ячейки, которые начинаются с первой колонки, то и фильтр по этим ячейкам будет в первом фильтре. Нас интересуют пока-что только даты, поэтому в списке фильтра выбираем только даты.
![](https://habrastorage.org/files/ed6/818/e65/ed6818e656c84a52af606367e0a9bc3e.png)
Фильтр сработал на славу и отобразил только строки, содержащие даты сдачи тестов.
![](https://habrastorage.org/files/f00/9ea/92d/f009ea92d5b14314ab6d4390ba63e824.png)
Напротив первой даты ставим какой-то символ или число, которое, по сути, будет являться флагом, что ячейка слева содержит дату. Копируем эту ячейку и, выделив всю колонку, вставим значение.
![](https://habrastorage.org/files/3e2/e73/6e4/3e2e736e48354e659957022e555d973d.png)
Теперь сделаем условие в ячейке справа от нашего флага, позволяющее отобразить ту или иную дату. Формула для условия простая: если в ячейке слева есть единичка, то берём значение даты ещё левее; если же единички нет, то берём дату, которая есть в предыдущей строке.
![](https://habrastorage.org/files/e3e/7ad/b48/e3e7adb4865c46f3aca808e2dbb4b5d9.png)
Не забудем для всей колонки выбрать формат ячеек «Дата».
Отменяем фильтр, чтобы отобразились все строки, выделяем ячейку с нашей формулой и перетаскиваем её уголок до самого низа листа – на все строки.
![](https://habrastorage.org/files/7a5/b7a/6e0/7a5b7a6e050b402097181b58f0fad5b8.png)
В результате напротив каждой строки появится дата. Если по заголовку дата меняется, то и в новой колонке дата сменится на новую.
![](https://habrastorage.org/files/68f/092/6eb/68f0926eb50d4c4d8ff92169439a3134.png)
В дальнейшем потребуется удалять ненужные строки, но в колонке даты содержатся формулы с ссылками, поэтому необходимо избавиться от ссылок, оставив только текст. Достаточно выделить всю колонку, копировать её и вставить с параметром «только значения».
![](https://habrastorage.org/files/9ab/d9e/da9/9abd9eda93ee43f58eaeb9805b6e005f.png)
Следующим шагом будет удаление лишних строк. В рассматриваемом примере каждый учащийся имеет пометку, с какого он подразделения, воспользуемся этим как признаком, что это запись о результате теста. В первом фильтре скроем все записи по результатам тестов, а также запись о тестологе, проводящим тестирование (эта информация нам ещё пригодится).
![](https://habrastorage.org/files/13b/45a/9b3/13b45a9b3d6548daae7a0ba351be0d4d.png)
Отобразившиеся строки нам не понадобятся, за исключением строки с заголовками столбцов.
![](https://habrastorage.org/files/988/556/f0a/988556f0a8a74204b45001bd0c141095.png)
Выделим все строки, кроме содержащей имена столбцов и удалим их.
![](https://habrastorage.org/files/829/5de/fe5/8295defe57ad41b4b38519b4f61bbc5d.png)
Т.к. мы выделили строку фильтра, то фильтрация элементов автоматически отключится.
![](https://habrastorage.org/files/ae0/14f/138/ae014f138fe449da85cad0523d2c901a.png)
Включим фильтр снова и отберём только записи о тестологе.
![](https://habrastorage.org/files/7dd/86b/80f/7dd86b80f48c4c54bfb6ea8a68260ab5.png)
Чтобы напротив каждой записи поставить пометку, кто принимал тест, можно воспользоваться тем же методом, что использовался для расстановки дат. Но есть одно отличие – даты в разграничивающих заголовках стояли перед списком тестируемых, а вот указание, кто принимал тест стоит после списка.
Расставим единички напротив каждой фамилии тестолога, а вот формулу начнём писать с конца. Условие не сильно отличается – если стоит в ячейке единичка, то также отобразим фамилию, а если нет – отобразим фамилию с нижней строки (для дат выбирали ячейку выше).
![](https://habrastorage.org/files/8ea/df6/2dd/8eadf62dd4144db1a50c53b4007485c5.png)
Отключаем фильтр и растягиваем формулу до самого верха.
![](https://habrastorage.org/files/dd2/068/077/dd2068077de646eaa72a3886f2469626.png)
Фамилии экзаменаторов расставлены для каждой записи.
![](https://habrastorage.org/files/291/550/5f8/2915505f8c7f4c95a6a8749cdea6680f.png)
Аналогичным образом, как для дат, удалим информацию о ссылках, скопировав столбец и вставив только значения.
![](https://habrastorage.org/files/f64/234/aef/f64234aefe754349923e56020e25d253.png)
Поставим фильтр на фамилии обратно и удалим лишние строки.
![](https://habrastorage.org/files/929/993/6a2/9299936a2d6b48baabe79ccb7de140e2.png)
Удалим фильтр и столбец «для единичек», дадим имена новым колонкам и поставим новый фильтр. Теперь у нас есть таблица, содержащая данные в приемлемом для обработки виде.
![](https://habrastorage.org/files/94e/2cf/681/94e2cf681114420cbddb81611a9f603c.png)
Полученная таблица позволяет применять фильтры по датам, позволяет считать средние значения и их количество путём комбинации фильтра и выделения. Например, выбираем фильтр на определённую неделю и выделяем колонку с процентом прохождения теста – внизу справа Excel отобразит количество элементов и их среднее значение. Также данную таблицу можно использовать как источник данных для других приложений, не беспокоясь о фильтрации заголовков между записями и другими выходящими проблемами.
Исходный документ изображён на рисунке далее.
![](https://habrastorage.org/files/a89/775/b08/a89775b08b514af3a3004b5822d3d215.png)
Хоть в примере и указывается работа только в листе с именем «Октябрь», конечный вариант предполагает слияние всех таких таблиц, а это два десятка листов. Поэтому ручное копирование с заголовков дат напротив каждого из результатов теста отнимает много времени.
Первым делом необходимо выделить весь документ ([Ctrl]+A, [Ctrl]+A) и применить фильтр. Т.к. заголовки дат занесены в объединённые ячейки, которые начинаются с первой колонки, то и фильтр по этим ячейкам будет в первом фильтре. Нас интересуют пока-что только даты, поэтому в списке фильтра выбираем только даты.
![](https://habrastorage.org/files/ed6/818/e65/ed6818e656c84a52af606367e0a9bc3e.png)
Фильтр сработал на славу и отобразил только строки, содержащие даты сдачи тестов.
![](https://habrastorage.org/files/f00/9ea/92d/f009ea92d5b14314ab6d4390ba63e824.png)
Напротив первой даты ставим какой-то символ или число, которое, по сути, будет являться флагом, что ячейка слева содержит дату. Копируем эту ячейку и, выделив всю колонку, вставим значение.
![](https://habrastorage.org/files/3e2/e73/6e4/3e2e736e48354e659957022e555d973d.png)
Теперь сделаем условие в ячейке справа от нашего флага, позволяющее отобразить ту или иную дату. Формула для условия простая: если в ячейке слева есть единичка, то берём значение даты ещё левее; если же единички нет, то берём дату, которая есть в предыдущей строке.
![](https://habrastorage.org/files/e3e/7ad/b48/e3e7adb4865c46f3aca808e2dbb4b5d9.png)
Не забудем для всей колонки выбрать формат ячеек «Дата».
Отменяем фильтр, чтобы отобразились все строки, выделяем ячейку с нашей формулой и перетаскиваем её уголок до самого низа листа – на все строки.
![](https://habrastorage.org/files/7a5/b7a/6e0/7a5b7a6e050b402097181b58f0fad5b8.png)
В результате напротив каждой строки появится дата. Если по заголовку дата меняется, то и в новой колонке дата сменится на новую.
![](https://habrastorage.org/files/68f/092/6eb/68f0926eb50d4c4d8ff92169439a3134.png)
В дальнейшем потребуется удалять ненужные строки, но в колонке даты содержатся формулы с ссылками, поэтому необходимо избавиться от ссылок, оставив только текст. Достаточно выделить всю колонку, копировать её и вставить с параметром «только значения».
![](https://habrastorage.org/files/9ab/d9e/da9/9abd9eda93ee43f58eaeb9805b6e005f.png)
Следующим шагом будет удаление лишних строк. В рассматриваемом примере каждый учащийся имеет пометку, с какого он подразделения, воспользуемся этим как признаком, что это запись о результате теста. В первом фильтре скроем все записи по результатам тестов, а также запись о тестологе, проводящим тестирование (эта информация нам ещё пригодится).
![](https://habrastorage.org/files/13b/45a/9b3/13b45a9b3d6548daae7a0ba351be0d4d.png)
Отобразившиеся строки нам не понадобятся, за исключением строки с заголовками столбцов.
![](https://habrastorage.org/files/988/556/f0a/988556f0a8a74204b45001bd0c141095.png)
Выделим все строки, кроме содержащей имена столбцов и удалим их.
![](https://habrastorage.org/files/829/5de/fe5/8295defe57ad41b4b38519b4f61bbc5d.png)
Т.к. мы выделили строку фильтра, то фильтрация элементов автоматически отключится.
![](https://habrastorage.org/files/ae0/14f/138/ae014f138fe449da85cad0523d2c901a.png)
Включим фильтр снова и отберём только записи о тестологе.
![](https://habrastorage.org/files/7dd/86b/80f/7dd86b80f48c4c54bfb6ea8a68260ab5.png)
Чтобы напротив каждой записи поставить пометку, кто принимал тест, можно воспользоваться тем же методом, что использовался для расстановки дат. Но есть одно отличие – даты в разграничивающих заголовках стояли перед списком тестируемых, а вот указание, кто принимал тест стоит после списка.
Расставим единички напротив каждой фамилии тестолога, а вот формулу начнём писать с конца. Условие не сильно отличается – если стоит в ячейке единичка, то также отобразим фамилию, а если нет – отобразим фамилию с нижней строки (для дат выбирали ячейку выше).
![](https://habrastorage.org/files/8ea/df6/2dd/8eadf62dd4144db1a50c53b4007485c5.png)
Отключаем фильтр и растягиваем формулу до самого верха.
![](https://habrastorage.org/files/dd2/068/077/dd2068077de646eaa72a3886f2469626.png)
Фамилии экзаменаторов расставлены для каждой записи.
![](https://habrastorage.org/files/291/550/5f8/2915505f8c7f4c95a6a8749cdea6680f.png)
Аналогичным образом, как для дат, удалим информацию о ссылках, скопировав столбец и вставив только значения.
![](https://habrastorage.org/files/f64/234/aef/f64234aefe754349923e56020e25d253.png)
Поставим фильтр на фамилии обратно и удалим лишние строки.
![](https://habrastorage.org/files/929/993/6a2/9299936a2d6b48baabe79ccb7de140e2.png)
Удалим фильтр и столбец «для единичек», дадим имена новым колонкам и поставим новый фильтр. Теперь у нас есть таблица, содержащая данные в приемлемом для обработки виде.
![](https://habrastorage.org/files/94e/2cf/681/94e2cf681114420cbddb81611a9f603c.png)
Полученная таблица позволяет применять фильтры по датам, позволяет считать средние значения и их количество путём комбинации фильтра и выделения. Например, выбираем фильтр на определённую неделю и выделяем колонку с процентом прохождения теста – внизу справа Excel отобразит количество элементов и их среднее значение. Также данную таблицу можно использовать как источник данных для других приложений, не беспокоясь о фильтрации заголовков между записями и другими выходящими проблемами.