Очистка данных, используемых для создания сводной таблицы Excel
Рабочий лист, показанный на рис. 2.4, выглядит отлично. Однако его нельзя использовать в качестве источника данных для сводной таблицы. Можете ли вы определить причины проблем, связанные с использованием этого набора данных?
- Отсутствует отдельный столбец с информацией о модели. Она отображается в столбце Регион. Чтобы решить эту проблему, вставьте новый столбец Категория оборудования и введите название категории оборудования в каждую его строку.
- В таблице имеются пустые строки (например, строки 8 и 15), которые следует удалить.
- Пустые ячейки используются для структурированного представления данных, например это может список, какие компьютерные программы бесплатно можно скачать. Тот, кто будет изучать отчет, может предположить, что ячейки
А4:А5
представляют регион Средний Запад. В данном случае так оно и есть, но чтобы не гадать лишний раз, заполните эти и подобные им ячейки находящимися выше значениями. - Рабочий лист представляет один столбец данных, в котором выводятся данные за месяц. Этот столбец несколько раз продублирован на рабочем листе. Столбцы с С по F следует переформатировать так, чтобы название месяца выводилось в одном столбце, а объем продаж для этого месяца — в следующем столбце. Этот процесс требует выполнения множественных операций копирования и вставки или написания нескольких строк VBA-кода.

Рис. 2.4. На красивое форматирование этого отчета ушло немало времени, однако по некоторым причинам использовать его в качестве источника данных для сводной таблицы нельзя
Существует весьма хитрый метод заполнения пустых ячеек (только в английской версии программы). Выделите весь диапазон данных. Перейдите на вкладку Главная (Ноmе)
ленты и щелкните в разделе Редактирование (Editing)
на треугольной кнопке Найти
и выделить (Find & Select)
. В раскрывающемся меню выберите команду Перейти (Go То Special)
. В диалоговом окне Переход
щелкните на кнопке Выделить
. В диалоговом окне Выделение группы ячеек (Go То Special)
установите флажок Пустые ячейки (Blanks)
. Выделив все пустые ячейки, начните ввод формулы нажатием клавиши <=>, затем нажмите клавишу <↑>, а после этого — комбинацию клавиш Ctrl+Enter
, чтобы занести формулу во все пустые ячейки. Не забудьте скопировать и вставить специальные значения для преобразования формул в значения.
После внесения всех изменений данные будут готовы для использования в качестве источника данных сводной таблицы. Как видно на рис. 2.5, для каждого столбца представлены заголовки. В таблице нет пустых ячеек, строк и столбцов. Данные по продажам за месяц теперь представлены в одном столбце Е, а не в нескольких столбцах.

Рис. 2.5. Данные представлены в пяти столбцах и идеально подготовлены для анализа сводной таблицы