Сводная таблица в Excel – удобный инструмент для анализа и представления данных. Но что делать, если данные находятся в разных источниках? Разберем, как сделать сводную таблицу из нескольких листов в Excel.
Даже если есть ERP и BI-системы, без Excel финансовому директору не обойтись. Всевозможные расчеты, сводные таблицы, удобные графики - в Excel можно сделать практически все что угодно. Но нужно знать, как это сделать.
Сводная таблица в Excel средствами Power Query
Для начала необходимо отметить, что Excel может работать с исходными таблицами различного размера. Но заголовки и шапка этих таблиц должны быть одинаковы. Это нужно для того, чтобы программа правильно интерпретировала используемые данные. В противном случае может возникать ошибка.
Допустим, необходимо , данные для которой надо взять из разных листов. Такая ситуация может возникать в случае, если в компании несколько распределенных точек сбыта, складских помещений, разные заказчики одинаковых групп товаров. В таком случае отчеты будут предоставлять разные филиаы. И чтобы их адекватно проанализировать, имеет смысл создать общую для всей компании таблицу данных, на основании которой в дальнейшем будет построена сводная таблица.
В этом случае необходимо создать чистый новый лист в программе Excel.
В этом листе перейти во вкладку «Данные».
Далее нажать «Создать запрос», в выпавшем списке выбрать «Из файла» и затем – «Из книги». Создадим сводную таблицу из нескольких листов на примере – два магазина прислали отчеты о наличии у них ящиков различного цвета на стеллажах. Данные каждого магазина сохранены на одном листе. Из этих листов сформирована «Книга1», с которой мы работаем.
В появившемся окне надо указать книгу, откуда программа должна взять данные и нажать кнопку «Импорт».
Появится окно под названием «Навигатор». В нем надо выбрать лист, из которого будут взяты данные. Указать можно любой лист.
Программа покажет данные в окне предпросмотра, которые предстоит взять из указанного листа.
В нашем примере видно, что указанный лист содержит множество ячеек с данными «null». Это неверно, так как программа будет обрабатывать и эти ячейки. Чтобы сократить область обрабатываемых значений и удалить такие нулевые ячейки, необходимо исправить исходный файл. Для этого нужно перейти в исходную таблицу и нажать «Ctrl + End». Будет выделена последняя активная ячейка таблицы. Надо удалить все ячейки правее и ниже таблицы, добиваясь того, чтобы при нажатии «Ctrl + End» становилась активной нижняя правая ячейка таблицы.
После этого источник данных не будет содержать лишней информации.
Можно удалить строчки «Навигация» и «Измененный тип». И приступить к редактированию данных в разделе «Источник». В главном окне редактора будет отображаться перечень всех листов указанной книги. В нашем случае «Лист1» и «Лист2».
Затем в строке «Data» нажать иконку с двумя стрелками, как указано на рисунке.
В появившемся окне снять галочку с пункта «Использовать исходное имя столбца как префикс». И нажать «ОК». Появится таблица в которой собраны все данные.
Таблица будет перестроена. Дублирующую строку с «шапкой» можно удалить. Для этого в фильтре столбца «Склад» снять галочку с пункта «Склад» и нажать «ОК». Затем в этом же фильтре нажать «Удалить пустые». Соответствующие строки будут удалены.
В появившемся окне «Загрузить в» поставить переключатель в позицию «Только создать подключение» и нажать кнопку «Загрузить». Появится запрос, на основании которого и будет строиться сводная таблица.
В появившемся окне установить переключатель в положение «Использовать внешний источник данных» и нажать кнопку «Выбрать подключение».
В появившемся окне выбрать имя сформированного запроса, в нашем случае – «сводная» и нажать кнопку «Открыть».
Появится конструктор сводной таблицы. Данные можно добавлять и перемещать как в обычной сводной таблице.
Сводная таблица в версиях Excel до 2016 года
В программах Excel, изданных до 2003 года включительно, сводные таблицы из разных источников создавались через опцию «построить сводную по нескольким диапазонам консолидации». Однако подобное построение таблицы не дает возможности полноценно анализировать весь объем полученной информации. Таблица, построенная таким образом, может использоваться только для самого простого анализа данных.
В версиях с 2007 года надо было добавлять специальный «Мастер сводных таблиц», где новые диапазоны данных по одному указывались через диалоговые окна. Полученные сводные таблицы также обладали достаточно ограниченной функциональностью.
Что такое сводные таблицы Excel? Вероятно, непосвященный человек предположит самое очевидное - это таблицы, в которые собраны (сведены) данные из нескольких других разрозненных источников, отвечающие некоторому запросу. На самом деле это заключение не совсем верно.
Сводные таблицы Excel – это инструмент, который собирает по заданному алгоритму в удобные таблицы-отчеты информацию чаще всего из одной таблицы базы данных ! (Возможность использования в качестве источника нескольких диапазонов консолидации существует, но в этой статье рассматриваться не будет.)
При помощи сводных таблиц из хаотичного набора строк-записей базы данных можно за считанные секунды извлечь нужные, отсечь в них лишнюю информацию, добавить недостающую и представить пользователю в красивом и удобном для восприятия виде.
Если вы постоянно работаете в программе MS Excel с большими объемами информации и ничего не знаете о сводных таблицах, то считайте, что вы «заколачиваете гвозди» калькулятором, не зная его истинного предназначения!
Когда следует применять сводные таблицы?
Во–первых, тогда, когда работаешь с большим объемом статистических данных, анализировать которые очень трудоемко при помощи сортировки и фильтров.
Во-вторых, тогда, когда приходится через определенные промежутки времени повторять (пересчитывать) отчеты в связи с изменениями источника – таблицы базы данных.
В-третьих, при анализе изменений данных базы в разрезе различных временных периодов или иных критериев.
Это основные ситуации, при которых раскрывается вся действительно волшебная сила инструмента сводные таблицы Excel.
Создание сводных таблиц и работа с ними никак не влияет на содержание источника – базу данных!!! Сводные таблицы Excel позволяют взглянуть на эти данные под разными ракурсами, не изменяя ничего в базе!!!
Это заключительная и самая важная статья обзорного цикла о хранении информации и управлении большими табличными объемами данных.
В предыдущих статьях цикла мы создали , научились , применять и , вводить .
Создание шаблона сводной таблицы.
Сводную таблицу будем создавать в той же книге Excel , где расположен источник информации — таблица базы данных, но на другом листе.
Продолжаем работу с учебной мини-базой БД2 «Выпуск металлоконструкций участком №2», с которой работали во всех статьях этого цикла.
1. Открываем в MS Excel файл .
2. Активируем («щелкаем мышкой») любую ячейку внутри таблицы базы.
3. Выполняем команду главного меню программы «Данные» - «Сводная таблица…». Эта команда запускает работу мини-сервиса «Мастер сводных таблиц».
4. Не долго размышляя над вариантами выбора положений переключателей в выпадающих окнах «Мастера…», настраиваем их (точнее – не трогаем их) так, как показано ниже на снимках экрана, двигаясь между окнами с помощью кнопок «Далее».
На втором шаге «Мастер…» сам выберет диапазон, если вы правильно подготовили базу данных и выполнили п.2 этого раздела статьи.
5.
На третьем шаге «Мастера…» нажимаем кнопку «Готово». Шаблон сводной таблицы сформирован и размещен на новом листе того же файла, где расположена база данных!
Кроме пока пустого шаблона на вновь созданном листе файла Excel вы видите панель «Сводные таблицы» со своим инструментарием и окно «Список полей сводной таблицы», появляющееся при активации любой ячейки внутри сводной таблицы. Появляются они автоматически, я их просто расположил рядом с шаблоном и подкорректировал размеры. Если панели «Сводные таблицы» вы не видите, то вызовите ее через главное меню командой «Вид» — «Панели инструментов» — «Сводная таблица»
В MS Excel 2007 и более новых версиях «Мастер…» упразднен потому, что 99% процентов пользователей никогда не меняют предложенных настроек переключателей и проходят эти три шага, просто соглашаясь с предложенными вариантами (мы тоже так поступили).
В MS Excel 2007 по команде «Сводная таблица» выпадает диалоговое окно «Создание сводной таблицы», в котором достаточно указать источник данных для анализа и место расположения создаваемой сводной таблицы.
Создание рабочих сводных таблиц Excel.
В созданном шаблоне имеются четыре зоны, в которые для создания сводной таблицы следует поместить названия полей-столбцов таблицы базы данных, перетащив их при помощи левой кнопки мыши из окна «Список полей сводной таблицы». Напоминаю, что в базе данных полем называется столбец с названием.
Внимание!!!
Элементами окна «Список полей сводной таблицы» являются заголовки полей базы данных!
1. Если поместить один из элементов «Списка полей сводной таблицы» в самую верхнюю зону шаблона, где он станет «Полем страниц» , то мы получим удобный фильтр записей этого поля .
2. Если перетащить один элемент (или два, реже – три и более) «Списка полей сводной таблицы» в левую зону шаблона, где он станет «Полем строк» заголовки строк сводной таблицы.
3. Если перетащить один элемент (или два, реже – три и более) «Списка …» в верхнюю зону шаблона, где он станет «Полем столбцов» , то мы получим из записей этого поля таблицы базы данных заголовки столбцов сводной таблицы.
4. Если перетащить один элемент (или два, реже – три и более) «Списка …» в центральную область шаблона, где он станет «Элементом данных» , то мы получим из записей этого поля базы данных значения сводной таблицы.
Значения – элементы данных – расположатся в строгом соответствии с правилами двухмерных таблиц, то есть на пересечении соответствующих заголовков строк и заголовков столбцов!
Не очень понятно? Перейдем к практическим примерам — все станет ясно!
Задача №9:
Сколько всего тонн металлоконструкций изготовлено по каждому заказу?
Для ответа на этот вопрос выполним всего два действия! Схема этих действий показана на предыдущем рисунке синими стрелками.
1. Перетащим мышью элемент «Заказ» из окна «Список полей сводной таблицы» в зону «Поля строк» пустого шаблона.
2.
Перетащим мышью элемент «Общая масса, т» из окна «Список полей сводной таблицы» в зону «Элементы данных» шаблона. Результат – на снимке экрана слева. Думаю, пояснения не требуются.
Заголовок «Элементов данных» «Сумма по полю Общая масса, т» расположился выше заголовка «Поля строк» и левее места, где может расположиться заголовок «Поля столбцов». Обратите на это внимание!
Задача №10:
Сколько тонн металлоконструкций изготовлено по каждому заказу по датам?
Продолжаем работу.
3. Для ответа на вопрос задачи №10 достаточно добавить в нашу сводную таблицу элемент «Дата» из окна «Список полей сводной таблицы» в зону «Поля столбцов» шаблона.
4.
Записи можно сгруппировать, например, по месяцам. Для этого на панели «Сводные таблицы» нажимаем вкладку «Сводная таблица» и выбираем «Группа и структура» — «Группировать…». В окне «Группирование» делаем настройки в соответствии со скриншотом слева.
Так как все записи нашей базы данных сделаны в апреле, то после группировки мы видим всего два столбца – «апр» и «Общий итог». Когда в исходной базе данных появятся записи, датированные маем и июнем, в сводной таблице добавятся соответствующие два столбца.
Задача №11:
Когда и сколько тонн и штук марок изготовлено всего по заказу №3?
5. Для начала перетащим поле «Заказ» из окна «Список полей сводной таблицы» или прямо из самой сводной таблицы в самый верх листа в зону «Поля страниц» шаблона.
7. Поле «Количество, шт» добавим в область «Элементы данных».
Необходимая для ответа на вопрос задачи конфигурация сводной таблицы Excel сформирована.
8. Нажмем на кнопку со стрелкой вниз в ячейке B1 и в выпавшем списке вместо записи «(Все)» выберем «3», соответствующую интересующему нас заказу №3 и закроем список, нажав кнопку «OK».
Ответ на вопрос задачи на снимке экрана ниже этого текста.
Форматирование сводной таблицы.
Если сводную таблицу нужно распечатать и показать кому-нибудь, или просто немного порадовать самого себя, то ее нужно отформатировать – придать достойный внешний вид и переформулировать некоторые «неадекватные» заголовки.
Заголовки «Сумма по полю Общая масса, т» и «Сумма по полю Кол-во, шт» звучат, вроде, и понятно, но как-то не по-русски. Переименуем их в более благозвучные «Общая масса изделий, т» и «Количество изделий, шт».
1. Продолжим работу с созданной таблицей. Активируем мышью ячейку B5.
2. На панели инструментов «Сводная таблица» нажимаем кнопку «Параметры поля» (выделена справа на снимке, расположенном ниже).
3. В выпавшем окне «Вычисление поля сводной таблицы» меняем имя и жмем кнопку «OK».
4.
По аналогичному алгоритму переименовываем и второй заголовок поля, предварительно «встав» мышью на ячейку B6.
5. На панели «Сводная таблица» нажимаем кнопку «Формат отчета» (выделена слева на снимке, расположенном выше — над п.3).
6. В появившемся окне «Автоформат» выбираем из предложенных вариантов форматирования, например, «Отчет 6» и нажимаем «OK» .
Внешний вид нашей сводной таблицы существенно преобразился.
Заключение.
Обращаю ваше внимание на несколько очень важных моментов!
При изменении источника (например, добавление очередной записи в базу данных) в самой сводной таблице изменения автоматически не наступят!!!
Для того чтобы изменения в базе отражались в сводной таблице, необходимо каждый раз «вручную» нажимать кнопку «Обновить данные» (значок кнопки – « ! ») на панели «Сводные таблицы»!!!
Помещайте в область «Полей страниц» поля, содержащие текстовую информацию , которая может стать заголовком для формируемой сводной таблицы .
В области "Полей строк" и «Полей столбцов» размещайте поля, содержащие текстовую информацию , которая может стать заголовками для строк и столбцов создаваемой сводной таблицы.
В зоне «Элементов данных» располагайте преимущественно числовую информацию.
Действуйте, располагая поля так, как вы действовали бы, делая это на листе бумаге! Не бойтесь ошибиться. Все легко исправляется.
Созданные двумя-тремя движениями мыши в одном из предыдущих разделов этой статьи сводные таблицы очень быстро дали ответ на весьма непростые вопросы! Если заказы изготавливаются в течение нескольких месяцев, а число наименований марок превышает несколько тысяч, то, сколько вам потребуется времени для решения рассмотренных выше задач? Час? День? Сводные таблицы Excel делают это мгновенно, многократно и без ошибок!!!
О сводных таблицах Excel написано много хороших «толстых» и «тонких» книг. В первую очередь это книги Билла Джелена («Мистера Excel») и Майкла Александера. Тем, кто заинтересовался этой темой, рекомендую их прочитать.
Конечно, подробно рассказать об этом чудесном инструменте в короткой статье очень сложно, практически невозможно. С одной стороны мне хотелось затронуть больше практически важных аспектов, но с другой – в ознакомительной статье не хотелось «залезать в дебри», отпугивая пользователей объемом и сложностью информации. Если этот «волшебный инструмент» MS Excel вызовет интерес у читателей блога, то в будущем эта тема будет продолжена выпуском небольшой брошюры с важными и ценными практическими рекомендациями.
Обязательно возьмите себе на вооружение сводные таблицы Excel! Ваша ценность как специалиста увеличится многократно!
На этом цикл из шести статей, представляющих собой общий обзор темы обработки больших объемов информации в Excel завершен.
Прошу уважающих труд автора подписаться на анонсы статей в окне, расположенном в конце каждой статьи или в окне вверху страницы!
Уважаемые читатели, вопросы, отзывы, и замечания оставляйте в комментариях внизу страницы.
Источником данных для сводной таблицы служит список данных, где, как правило, каждый столбец выступает в роли поля сводной таблицы. Но что если к вам пришла, только с виду похожая на сводную, таблица (она отформатирована и внешне напоминает ее, но использовать инструменты работы со сводными таблицами невозможно). И вам необходимо превратить ее в список данных, т.е. выполнить обратную операцию. В этом посте вы узнаете, как преобразовать сводную таблицу с двумя переменными в список данных.
На рисунке показан принцип, который я описал. Т.е. в диапазоне A2:E5 находится исходная сводная таблица, которая преобразуется в список данных (диапазон H2:J14). Вторая таблица представляет тот же набор данных, только в другом ракурсе. Каждое значение исходной сводной таблицы выглядит в виде строки, состоящее из пункта поля строки, поля столбца и соответствующего им значения. Такое отображение данных бывает полезно, когда необходимо отсортировать и манипулировать данными другими способами.
Для того чтобы реализовать возможность создания такого списка, мы воспользуемся инструментами сводной таблицы. Добавим кнопку Мастер сводных таблиц в панель быстрогодоступа, которая недоступна нам на ленте, но осталась как рудимент от более ранних версий Excel.
Перейдите по вкладке Файл -> Параметры . В появившемся диалоговом окне Параметры Excel, во вкладке Панель быстрого доступа в левом поле найдите пункт Мастер сводных таблиц и диаграмм и добавьте его в правый. Нажмите ОК.
Теперь на панели быстрого доступа у вас появился новый значок.
Щелкните по этой вкладке, чтобы запустить Мастер сводных таблиц.
На первом шаге мастера необходимо выбрать тип источника данных сводной таблицы. Устанавливаем переключатель В нескольких диапазонах консолидации и жмем Далее.
На шаге 2а укажите, как следует создавать поля страницы. Поместите переключатель Создать поля страницы -> Далее.
На шаге 2б, в поле Диапазон выберите диапазон, содержащий данные, и щелкните Добавить. В нашем случае это будет местоположение исходной сводной таблицы A1:E4.
На третьем шаге необходимо определиться, куда необходимо поместить сводную таблицу, и нажмите кнопку Готово.
Excel создаст сводную таблицу с данными. В левой части экрана вы увидите область Список полей сводной таблицы. Уберите все пункты с полей строк и столбцов. Более подробно о я писал в предыдущей статье.
У вас получится небольшая сводная таблица, состоящая из одной ячейки, которая содержит сумму всех значений исходной таблицы.
Дважды щелкните по этой ячейке. Excel создаст новый лист, который будет содержать таблицу со списком значений.
Заголовки этой таблицы представляют общую информацию, вероятно, вы захотите сделать их более информативными.
Сводная таблица применяется для быстрого анализа большого объема данных. Она позволяет объединять информацию из разных таблиц и листов, подсчитать общий результат. Этот универсальный аналитический инструмент существенно расширяет возможности программы Excel.
Можно сформировать новые итоги по исходным параметрам, поменяв строки и столбцы местами. Можно произвести фильтрацию данных, показав разные элементы. А также наглядно детализировать область.
Сводная таблица в Excel
Для примера используем таблицу реализации товара в разных торговых филиалах.
Из таблички видно, в каком отделе, что, когда и на какую сумму было продано. Чтобы найти величину продаж по каждому отделу, придется посчитать вручную на калькуляторе. Либо сделать еще одну таблицу Excel, где посредством формул показать итоги. Такими методами анализировать информацию непродуктивно. Недолго и ошибиться.
Самое рациональное решение – это создание сводной таблицы в Excel:
Просто, быстро и качественно.
Важные нюансы:
- Первая строка заданного для сведения данных диапазона должна быть заполнена.
- В базовой табличке каждый столбец должен иметь свой заголовок – проще настроить сводный отчет.
- В Excel в качестве источника информации можно использовать таблицы Access, SQL Server и др.
Как сделать сводную таблицу из нескольких таблиц
Часто требуется создавать сводные отчеты из нескольких таблиц. Есть пара табличек с информацией. Нужно объединить их в одну общую. Для науки придумаем остатки на складах в двух магазинах.
Порядок создания сводной таблицы из нескольких листов такой же.
Создадим отчет с помощью мастера сводных таблиц:
Как видите всего в несколько кликов можно создавать сложные отчеты из нескольких листов или таблиц разного объема информации.
Как работать со сводными таблицами в Excel
Начнем с простейшего: добавления и удаления столбцов. Для примера рассмотрим сводную табличку продаж по разным отделам (см. выше).
Справа от сводной таблицы у нас была область задач, где мы выбирали столбцы в списке полей. Если она исчезла, просто щелкаем мышью по табличке.
Добавим в сводную таблицу еще одно поле для отчета. Для этого установим галочку напротив «Даты» (или напротив «Товара»). Отчет сразу меняется – появляется динамика продаж по дням в каждом отделе.
Сгруппируем данные в отчете по месяцам. Для этого щелкаем правой кнопкой мыши по полю «Дата». Нажимаем «Группировать». Выбираем «по месяцам». Получается сводная таблица такого вида:
Чтобы изменить параметры в сводной таблице , достаточно снять галочки напротив имеющихся полей строк и установить у других полей. Сделаем отчет по наименованиям товаров, а не по отделам.
А вот что получится, если мы уберем «дату» и добавим «отдел»:
А вот такой отчет можно сделать, если перетащить поля между разными областями :
Чтобы название строки сделать названием столбца, выбираем это название, щелкаем по всплывающему меню. Нажимаем «переместить в название столбцов». Таким способом мы переместили дату в столбцы.
Поле «Отдел» мы проставили перед наименованиями товаров. Воспользовавшись разделом меню «переместить в начало».
Покажем детали по конкретному продукту . На примере второй сводной таблицы, где отображены остатки на складах. Выделяем ячейку. Щелкаем правой кнопкой мыши – «развернуть».
В открывшемся меню выбираем поле с данными, которые необходимо показать.
Когда нажимаем на сводную таблицу, становится доступной закладка с параметрами отчета. С ее помощью можно менять заголовки, источники данных, группировать информацию.
Проверка правильности выставленных коммунальных счетов
С помощью сводных таблиц Excel легко проверить, насколько правильно обслуживающие организации начисляют квартплату. Другой положительный момент – экономия. Если мы будем ежемесячно контролировать, сколько расходуется света, газа, то сможем найти резерв для экономии средств на оплату квартиры.
Для начала предлагаем составить сводную таблицу тарифов по всем коммунальным платежам. Для разных городов данные будут свои.
Для примера мы сделали сводную табличку тарифов для Москвы:
Для учебных целей возьмем семью из 4 человек, которые проживают в квартире 60 кв. м. Чтобы контролировать коммунальные платежи, необходимо создать таблицы для расчета на каждый месяц.
Первый столбец = первому столбцу из сводной таблицы. Второй – формула для расчета вида:
Тариф * количество человек / показания счетчика / площадь
Наши формулы ссылаются на лист, где расположена сводная таблица с тарифами.
Если при расчете коммунальных платежей применяются льготы, их тоже можно внести в формулы. Информацию по начислениям требуйте в бухгалтерии своей обслуживающей организации. Когда меняются тарифы – просто измените данные в ячейках.
Сводная таблица – это инструмент, который помогает проанализировать данные компании. Создать такую таблицу можно в разных текстовых редакторах.
Для создания необходимо выбрать заголовки и для строк и столбцы из созданного отчёта. Удобнее всего отчёты делать в Экселе. Вы можете применить фильтр ко всему отчёту или к отдельной его части.
Сводная таблица в excel как сделать
Первым делом разберём версию 2007 года. У некоторых пользователей ещё осталась старая версия редактора. Для начала нужно создать таблицу для построения отчёта. В ней не должно быть пустых ячеек и строк. Разберём процесс создания отчёта на самом простом примере:
- Запускаем эксель 2007 года. На верхней панели выбираем вкладку «Вставка».
- Выбираем «Создание рекомендуемых сводных таблиц».
- Появится окно, в котором будут представлены варианты рамок. Вы можете предварительно посмотреть, как будет выглядеть ваш отчёт, если нажмёте на макет. После этого можете выбрать основной. Нажимаете на Ок и отчёт появится на листе. Кроме этого, эксель сам определит порядок расположения строк.
- В программе можно отфильтровать и упорядочить значения. Для этого есть специальный значок на панели.
Это самый простой пример того, как можно сделать сводную таблицу в старой версии эксель.
Сводные таблицы в excel 2010 и 2013 года – пример работы в excel
Создание отчёта в эксель 2010 и 2013 практически одинаково. Процесс в этих версиях немного отличается от создания в версии 2007 года. Но, некоторые моменты похожи: перед началом работы, необходимо создать простую таблицу, в которой указать все данные, необходимые для вас. Верхняя строка должна содержать название столбцов.
Теперь можно делать отчёт в экселе:
Что можно делать с листом:
- Вам будет доступна панель инструментов для управления значениями. Все поля и их заголовки будут перечислены во вкладке «Выберите поля для добавления в отчёт». Для добавления нужных пунктов необходимо выбрать нужные поля и список будет создан.
- Есть возможность управлять расположением полей. Вы можете перетаскивать столбцы в нужные окна, а также сортировать в удобном для вас порядке. Также можно отфильтровать пункты. Для этого нужно перетащить пункт в окно «Фильтр». Если вам необходимо произвести расчёт по отдельному полю, то перетащите его в окно «Значение».
- Вы можете воспользоваться дополнительными опциями. Найти их можно по следующему принципу: «Параметры» «Работа со сводными таблицами». Там очень много инструментов, которые имеют большое количество настроек и дополнительных функций.
Как создать сводную таблицу при помощи мастера сводных таблиц
Инструмент «Мастер сводных таблиц» не отображается на главной панели. Именно поэтому его необходимо сначала активировать. Для этого переходим «Файл» «Параметры» «Панель быстрого доступа». Перед вами откроется список «выбрать команды из», отмечаете «Команды на ленте». Теперь выбираете мастера сводных таблиц и добавляете его. После всех выполненных действий на главной панели появится значок выбранной функции.
Теперь можно перейти и к самому процессу создания сводного отчёта при помощи мастера:
Если источником был выбран простой отчёт, в котором есть все необходимые данные для отчёта, вам остаётся только сделать его. Задаёте диапазон, то есть выбираете данные простой таблицы. Выбираете место для размещения и нажимаете на кнопку «Готово».
Если вы выбираете источник, данные которого необходимо консолидировать из других источников, тогда необходимо поставить галочку напротив этого пункта. Теперь нужно указать, как вы будете создавать поля страницы. Также нужно будет указать и диапазон. Для этого, сначала выделяем один диапазон и нажимаем на кнопку «добавить». Также добавляются и последующие значения.
Чтобы не запутаться в диапазонах, им нужно давать имена. Для этого нужно выделить один из диапазонов
списка и указать число полей для него, после чего каждому полю даёте имя. И так со всеми диапазонами.
Сводные таблицы excel 2010 из нескольких листов
Если информация в отчётах однотипная, то легче всего воспользоваться мастером сводных таблиц. Если отчёт имеет сложную структуру, то действовать нужно по-другому. Например, есть две таблицы , одна содержит приход товара, а другая – количество проданного товара в разных магазинах. Главное, чтобы были одинаковые заголовки, меньше времени будете переносить их в один отчёт. В этом случае отчёт делается следующем образом:
Теперь нужно создать сводный отчёт:
- Вставка.
- Сводный отчёт.
- Вписываем диапазон и место.
Но, что делать, если нужно создать отчёт из нескольких таблиц. Вручную работать сложновато. Для этого можно воспользоваться специальной надстройкой ЕXCEL. Скачивается она на сайте разработчика.
Итак, мы разобрали, как создавать сводные таблицы в excel. Нужно проявить немного усидчивости и внимательности и тогда результат не заставит себя ждать.
Загрузка...