В чем полезность сводных таблиц
Многие специалисты используют Microsoft Excel для вышеперечисленных целей, поскольку программа существенно экономит время профессионала, беря всю трудоемкую работу с данными на себя.
Еще одна отличительная особенность, которая сделала Excel популярным, заключается в том, что для создания довольно нетривиальных инструментов анализа данных (сводные таблицы являются одним из них) не требуется специальных знаний в области программирования.
Но прежде чем вы начнете использовать всю «прибыль» сводных таблиц Excel, вам нужно приложить немало усилий, чтобы освоить довольно сложную технику создания таблиц и работы с ними. Но, в конце концов, эффект от приобретения нового навыка, как вы увидите, окупит потраченные время, энергию и усилия.
В этой статье мы решили рассказать об основных технологиях создания сводных таблиц в Excel и отметить некоторые особенности методов, которые могут вам пригодиться в дальнейшей работе.
Во-первых, мы стремимся понять, что такое сводная таблица и «с чем ее едят».
Сводные таблицы — это список, содержащий образцы данных из любого источника (базы данных, таблиц Excel и др.), Которые необходимы специалисту для дальнейшего анализа данных, изменения структуры таблиц и т.д.
Сводные таблицы позволяют изменять визуальное представление данных, представляя их с новой точки зрения без изменения самого источника.
Создание сводной таблицы в Excel
Перед созданием сводной таблицы необходимо подготовить таблицу с исходными данными для дальнейшего анализа:
- во-первых, все столбцы должны быть озаглавлены;
- во-вторых, нужно убедиться, что нет пустых ячеек и строк (иначе при создании сводной таблицы могут возникнуть ошибки при автозаполнении пробелов, что может исказить информацию);
- в-третьих, столбец может содержать значения только одного формата (например, в столбце «Дата покупки» допускается только значение типа «Дата», «Имя» будет содержать только текстовые строки и т д);
- в-четвертых, в ячейках не должно быть перечисления (то есть неправильно писать адрес в ячейке в виде перечисления: «Город, улица, дом, квартира» что-то отдельно, например город, если вы можно сделать меньше, чем эта информация).
Требования очень похожи на те, которые предлагаются при проектировании реляционных баз данных, поэтому специалистам (или, по крайней мере, обладающим базовыми знаниями) в этой области не составит труда провести параллель и освоить новый программный инструмент.
Итак, мы подошли к основному вопросу, который нас интересовал — создать сводную таблицу в Excel. Мы начинаем это понимать.
В статье мы приведем примеры на обучающем столе, на котором отображается информация о закупке товаров, например, из магазина одежды.
Сначала давайте откроем таблицу, убедимся, что она соответствует всем требованиям, и с уверенностью перейдем к следующему шагу. Найдите вкладку «Вставка» на панели быстрого доступа. Перед нами появится лента с доступными функциями. Мы создаем сводную таблицу, поэтому ищем кнопку с таким же именем. Она будет первой в панели.
«Щелкните» по иконке и перед нами появится диалоговое окно со списком необходимых параметров:
- диапазон ячеек, из которых будут взяты данные (это могут быть ячейки из текущей таблицы Excel или внешнего источника данных — другой таблицы Excel или модели данных из СУБД)
- Место, где будет размещен отчет сводной таблицы (на текущем листе или на новом. Рекомендуется создавать на новом листе, но вы сами делаете выбор исходя из своей задачи)
После выбора всех параметров нажмите ОК, и на новом листе будет выделена область, в которой будет размещена сводная таблица, а справа отобразится панель управления, с помощью которой мы можем преобразовать обычную таблицу в информативный отчет.
Половина битвы сделана: сводная таблица создана, осталось заполнить ее данными. Для этого перейдите в правое окно «Поля сводной таблицы».
Вверху мы видим область, в которой перечислены все атрибуты исходной таблицы: «Дата получения», «Название продукта», «Отдел» и так далее.
Теперь вы можете искажать и искажать данные по своему усмотрению, изобретая концепции для сводной таблицы.
Для начала попробуем объединить данные по отделам и рассчитать общую стоимость товаров, поступивших в каждый отдел.
Чтобы добавить поле в таблицу, необходимо установить флажок рядом с названием атрибута в списке полей в правом верхнем углу. В нашем случае — «Отдел» и «Стоимость товара в лоте».
В окне, посвященном Сводной таблице, есть три строки с названиями отделов (левый столбец) и стоимостью товаров по каждому отделу. Помимо суммы, вы можете рассчитать максимальное, минимальное, среднее значение и выполнить другие математические операции. Окончательный результат для всех трех отделов показан внизу таблицы.
Посмотрите, куда попали данные, в нижней части панели настроек: поле «Отдел», содержащее текстовые значения, перемещено в область «Строки», а числовые данные — в «Значения».
То же самое произойдет, если мы добавим в таблицу какое-либо числовое поле (например, «Количество»): в текущей таблице будет столбец, а в области «Значения» — «Сумма для поля количества».
Теперь в нашей таблице отображается информация не только о количестве товаров, поступивших в каждый отдел, но и о том, в каком количестве.
Если нас не устраивает такая ориентация таблицы, то из области «Строки» можно перетащить «Отдел» в область «Столбцы»: переместить курсор, удерживая кнопку мыши.
Таблица стала намного компактнее: в строке отображается результат вычислений, а в столбцах — отделы, которым соответствуют данные.
Такое расположение может быть удобно при небольшом количестве отделов (как в примере), но если их много, мы получим очень вытянутый стол, который будет невозможно разглядеть с первого взгляда и в котором будет сложно найти необходимые данные. Такая таблица нам точно не нужна.
Как мы уже отмечали, количество — далеко не единственная операция, которую можно применить к данным. Вы можете увидеть, какие формулы доступны нам, щелкнув имя поля в области «Значения» и выбрав «Параметры поля значений».
Появится окно со списком операций, которые можно применить к данным. Среди них: количество, среднее значение, максимум, минимум, отклонение, дисперсия и другие — все, что может понадобиться для качественного анализа.
К каждой строке поля «Значения» можно применить другую формулу. Например, мы добавляем в таблицу поле «Цена» и отображаем максимальные значения для каждого отдела.
Итак, какие выводы можно сделать из полученной таблицы? В основном магазин покупал Аксессуары (на сумму 267 660 рублей), а самый дорогой товар стоил 2700 рублей.
Как вы могли заметить, мы никогда не использовали область «Фильтры» на панели параметров. Этот раздел необходим для сортировки данных по заданным критериям.
Например, давайте добавим данные в сводную таблицу, чтобы мы могли фильтровать записи по «дате получения». Для этого поставьте галочку напротив поля «Дата получения». Это поле сразу переместится в область «Линии».
Мы добавили данные, теперь нам нужно создать фильтр. Для этого просто перетащите «Дата прибытия» в область «Фильтры», как мы делали раньше с столбцами и строками. Получаем вот что:
Над всеми строками появилось еще одно поле — «Дата получения», в котором мы можем выбрать дату, нажав на треугольник в конце строки (Все).
Теперь легко узнать, по какой общей стоимости пришел товар, сколько было самым дорогим в тот или иной день. Например, на 05.04.2017.
Помимо установки конкретной даты, вы можете выбрать название отделов. То есть вы можете создать сводную таблицу, в которой будет содержаться информация о том, сколько Аксессуаров получили на 05.04.2017 и какова цена самого дорогого товара.
Как обновлять таблицу
Многие пользователи, которые научились создавать сводные таблицы с использованием известного алгоритма, часто сталкиваются с трудностью: они изменяют данные в источнике (у нас есть таблица Excel), но в сводной таблице изменений не происходит.
Решение этой проблемы занимает не более пары секунд: достаточно нажать кнопку, но не все знают о ее существовании и местонахождении. Поэтому мы считаем необходимым поговорить о том, как обновлять данные в сводных таблицах.
Если вы вносите какие-либо изменения в данные только в одном столбце, просто щелкните правой кнопкой мыши ячейки в этом столбце и найдите функцию «Обновить». После нажатия кнопки «Обновить» таблица преобразуется.
Если изменения произошли более чем в одном столбце, выберите произвольную область таблицы и на панели перейдите в раздел «Анализ», в котором также есть функция «Обновить». Вы можете выбрать «Обновить», тогда функция будет применена к выбранной области или «Обновить все». Выберите то, что вам нужно, и сводная таблица изменится мгновенно.
Удаление Сводной таблицы
Теперь, когда мы понимаем, как создавать и редактировать сводные таблицы, давайте посмотрим, как их удалять.
Также эта операция не требует большой последовательности действий: выберите область, в которой расположена таблица (для экономии времени используйте клавиши Ctrl + A — выбрать все), и нажмите удалить (или щелкните правой кнопкой мыши, затем щелкните «Удалить» в появившемся списке)
Если вам нужно удалить столбец в сводной таблице, снимите флажок рядом с именем в списке полей таблицы.
Добавление новых столбцов/таблиц
Выше мы описали, как обновить данные в сводной таблице, если эти данные попадают в диапазон, указанный при создании таблицы.
Но что делать, если нужно добавить в сводную таблицу дополнительный столбец с данными?
Сначала вам нужно вставить новый столбец в исходную таблицу (источник данных), а затем увеличить диапазон для сводной таблицы. Если вам нужно добавить целую таблицу, вам нужно сначала объединить ее с исходной, а затем изменить диапазон.
Здесь мы добавили в источник столбец «Цена с НДС». Теперь перейдите в раздел «Анализ» и откройте «Источник данных».
Программа автоматически переведет вас на лист, где находится источник, и в диалоговом окне попросит указать диапазон значений. Выберите исходную таблицу с пополнением данных, нажмите кнопку ОК. Затем вам нужно обновить сводную таблицу, как показано выше.
После обновления таблицы в списке полей появится новая — «Цена с НДС». Теперь мы можем использовать значения этого поля в анализе, чтобы изучить ситуацию с новой точки зрения.
Заключение
Как видите, работать со сводными таблицами не сложно и не интересно. Достаточно понять технологию создания и основные принципы.
Поэтому, прочитав нашу статью и повторив все шаги со своими данными, вы уже можете отметить возможность работы со сводными таблицами Excel в своем резюме.
Фактически, сводные таблицы часто являются достойным аналогом реляционных баз данных. Операции, которые можно выполнять в Excel простым «перетаскиванием», реализованы в программировании с использованием сложных запросов.
Разумеется, на этом наша статья не заканчивается полезной информацией по этой теме. Есть много других нюансов, которые невозможно обобщить в одной статье.
Но наша работа заключалась в том, чтобы обучить тех, кто еще не знаком с таким мощным инструментом анализа, как сводные таблицы, рассказать о многих возможностях, которые предлагает Microsoft Excel, чтобы напомнить опытному пользователю о моментах, которые могут быть забыты. И, похоже, нам это удалось.