Возникла необходимость вести обработку данных. Ввиду наличия разрывных частей таблицы обработка требует дополнительных ручных манипуляций, а также затрудняет автоматизацию обработки статистики. Было решено убрать разделители, содержащие дату сдачи тестов и краткую сводку. Возникает проблема, что для каждого результата должна указываться эта дата, а также принимающий тестолог. Проблема решается добавлением колонки с этой информацией. Далее объясняется один из простых методов вынесения такой информации без применения знаний программирования.
Исходный документ изображён на рисунке далее.
Хоть в примере и указывается работа только в листе с именем «Октябрь», конечный вариант предполагает слияние всех таких таблиц, а это два десятка листов. Поэтому ручное копирование с заголовков дат напротив каждого из результатов теста отнимает много времени.
Первым делом необходимо выделить весь документ ([Ctrl]+A, [Ctrl]+A) и применить фильтр. Т.к. заголовки дат занесены в объединённые ячейки, которые начинаются с первой колонки, то и фильтр по этим ячейкам будет в первом фильтре. Нас интересуют пока-что только даты, поэтому в списке фильтра выбираем только даты.
Фильтр сработал на славу и отобразил только строки, содержащие даты сдачи тестов.
Напротив первой даты ставим какой-то символ или число, которое, по сути, будет являться флагом, что ячейка слева содержит дату. Копируем эту ячейку и, выделив всю колонку, вставим значение.
Теперь сделаем условие в ячейке справа от нашего флага, позволяющее отобразить ту или иную дату. Формула для условия простая: если в ячейке слева есть единичка, то берём значение даты ещё левее; если же единички нет, то берём дату, которая есть в предыдущей строке.
Не забудем для всей колонки выбрать формат ячеек «Дата».
Отменяем фильтр, чтобы отобразились все строки, выделяем ячейку с нашей формулой и перетаскиваем её уголок до самого низа листа – на все строки.
В результате напротив каждой строки появится дата. Если по заголовку дата меняется, то и в новой колонке дата сменится на новую.
В дальнейшем потребуется удалять ненужные строки, но в колонке даты содержатся формулы с ссылками, поэтому необходимо избавиться от ссылок, оставив только текст. Достаточно выделить всю колонку, копировать её и вставить с параметром «только значения».
Следующим шагом будет удаление лишних строк. В рассматриваемом примере каждый учащийся имеет пометку, с какого он подразделения, воспользуемся этим как признаком, что это запись о результате теста. В первом фильтре скроем все записи по результатам тестов, а также запись о тестологе, проводящим тестирование (эта информация нам ещё пригодится).
Отобразившиеся строки нам не понадобятся, за исключением строки с заголовками столбцов.
Выделим все строки, кроме содержащей имена столбцов и удалим их.
Т.к. мы выделили строку фильтра, то фильтрация элементов автоматически отключится.
Включим фильтр снова и отберём только записи о тестологе.
Чтобы напротив каждой записи поставить пометку, кто принимал тест, можно воспользоваться тем же методом, что использовался для расстановки дат. Но есть одно отличие – даты в разграничивающих заголовках стояли перед списком тестируемых, а вот указание, кто принимал тест стоит после списка.
Расставим единички напротив каждой фамилии тестолога, а вот формулу начнём писать с конца. Условие не сильно отличается – если стоит в ячейке единичка, то также отобразим фамилию, а если нет – отобразим фамилию с нижней строки (для дат выбирали ячейку выше).
Отключаем фильтр и растягиваем формулу до самого верха.
Фамилии экзаменаторов расставлены для каждой записи.
Аналогичным образом, как для дат, удалим информацию о ссылках, скопировав столбец и вставив только значения.
Поставим фильтр на фамилии обратно и удалим лишние строки.
Удалим фильтр и столбец «для единичек», дадим имена новым колонкам и поставим новый фильтр. Теперь у нас есть таблица, содержащая данные в приемлемом для обработки виде.
Полученная таблица позволяет применять фильтры по датам, позволяет считать средние значения и их количество путём комбинации фильтра и выделения. Например, выбираем фильтр на определённую неделю и выделяем колонку с процентом прохождения теста – внизу справа Excel отобразит количество элементов и их среднее значение. Также данную таблицу можно использовать как источник данных для других приложений, не беспокоясь о фильтрации заголовков между записями и другими выходящими проблемами.
Исходный документ изображён на рисунке далее.
Хоть в примере и указывается работа только в листе с именем «Октябрь», конечный вариант предполагает слияние всех таких таблиц, а это два десятка листов. Поэтому ручное копирование с заголовков дат напротив каждого из результатов теста отнимает много времени.
Первым делом необходимо выделить весь документ ([Ctrl]+A, [Ctrl]+A) и применить фильтр. Т.к. заголовки дат занесены в объединённые ячейки, которые начинаются с первой колонки, то и фильтр по этим ячейкам будет в первом фильтре. Нас интересуют пока-что только даты, поэтому в списке фильтра выбираем только даты.
Фильтр сработал на славу и отобразил только строки, содержащие даты сдачи тестов.
Напротив первой даты ставим какой-то символ или число, которое, по сути, будет являться флагом, что ячейка слева содержит дату. Копируем эту ячейку и, выделив всю колонку, вставим значение.
Теперь сделаем условие в ячейке справа от нашего флага, позволяющее отобразить ту или иную дату. Формула для условия простая: если в ячейке слева есть единичка, то берём значение даты ещё левее; если же единички нет, то берём дату, которая есть в предыдущей строке.
Не забудем для всей колонки выбрать формат ячеек «Дата».
Отменяем фильтр, чтобы отобразились все строки, выделяем ячейку с нашей формулой и перетаскиваем её уголок до самого низа листа – на все строки.
В результате напротив каждой строки появится дата. Если по заголовку дата меняется, то и в новой колонке дата сменится на новую.
В дальнейшем потребуется удалять ненужные строки, но в колонке даты содержатся формулы с ссылками, поэтому необходимо избавиться от ссылок, оставив только текст. Достаточно выделить всю колонку, копировать её и вставить с параметром «только значения».
Следующим шагом будет удаление лишних строк. В рассматриваемом примере каждый учащийся имеет пометку, с какого он подразделения, воспользуемся этим как признаком, что это запись о результате теста. В первом фильтре скроем все записи по результатам тестов, а также запись о тестологе, проводящим тестирование (эта информация нам ещё пригодится).
Отобразившиеся строки нам не понадобятся, за исключением строки с заголовками столбцов.
Выделим все строки, кроме содержащей имена столбцов и удалим их.
Т.к. мы выделили строку фильтра, то фильтрация элементов автоматически отключится.
Включим фильтр снова и отберём только записи о тестологе.
Чтобы напротив каждой записи поставить пометку, кто принимал тест, можно воспользоваться тем же методом, что использовался для расстановки дат. Но есть одно отличие – даты в разграничивающих заголовках стояли перед списком тестируемых, а вот указание, кто принимал тест стоит после списка.
Расставим единички напротив каждой фамилии тестолога, а вот формулу начнём писать с конца. Условие не сильно отличается – если стоит в ячейке единичка, то также отобразим фамилию, а если нет – отобразим фамилию с нижней строки (для дат выбирали ячейку выше).
Отключаем фильтр и растягиваем формулу до самого верха.
Фамилии экзаменаторов расставлены для каждой записи.
Аналогичным образом, как для дат, удалим информацию о ссылках, скопировав столбец и вставив только значения.
Поставим фильтр на фамилии обратно и удалим лишние строки.
Удалим фильтр и столбец «для единичек», дадим имена новым колонкам и поставим новый фильтр. Теперь у нас есть таблица, содержащая данные в приемлемом для обработки виде.
Полученная таблица позволяет применять фильтры по датам, позволяет считать средние значения и их количество путём комбинации фильтра и выделения. Например, выбираем фильтр на определённую неделю и выделяем колонку с процентом прохождения теста – внизу справа Excel отобразит количество элементов и их среднее значение. Также данную таблицу можно использовать как источник данных для других приложений, не беспокоясь о фильтрации заголовков между записями и другими выходящими проблемами.
