Сводные таблицы Excel от А до Я

Сводная таблица в Excel

Например, мы используем таблицу продаж товаров в различных отраслях.

Из таблички видно, в каком отделе, что, когда и на какую сумму было продано. Чтобы найти объем продаж по каждому отделу, необходимо рассчитать вручную на калькуляторе. Или создайте другую электронную таблицу Excel, где вы используете формулы для отображения итогов. Анализировать информацию таким образом непродуктивно. Недостаточно долго, чтобы ошибаться.

Наиболее рациональным решением будет создание сводной таблицы в Excel:

1. Выберите ячейку A1, чтобы Excel знал, с какой информацией вам нужно работать.
2. Выберите «Сводная таблица» в меню «Вставка».
3. Откроется меню «Создать сводную таблицу», где выбираем область и указываем место. Так как мы поместили курсор в ячейку с данными, то поле диапазона заполнится автоматически. Если курсор находится в пустой ячейке, необходимо ввести диапазон вручную. Сводную таблицу можно создать на том же листе или на другом. Если мы хотим, чтобы сводные данные были на существующей странице, не забудьте выделить для них место. На странице появится следующая форма:
4. Создадим таблицу, показывающую объем продаж по отделам. В списке полей сводной таблицы выбираем названия интересующих нас столбцов. Получаем результаты по каждому отделу.

Просто, быстро и качественно.

Важные нюансы:

• Первая строка в области, указанной для сводки данных, должна быть заполнена.
• В базовой тарелке у каждой колонки должен быть свой заголовок — так проще настроить сводный отчет.
• В Excel вы можете использовать таблицы Access, SQL Server и т д в качестве источника информации.

Постановка задачи

Итак, сводные таблицы можно использовать абсолютно на любых данных, но чаще всего Excel используется для анализа различных финансовых показателей компаний или проектов, поэтому давайте рассмотрим следующий пример (скачать файл).

Допустим, вы работаете в компании, которая является поставщиком фруктов и овощей в сетевые супермаркеты в нескольких крупных городах страны.

По каждой доставке в базе содержится информация, которую можно выгрузить примерно в следующую таблицу:

Здесь в каждой строке мы видим дату заказа, название сети супермаркетов, город, в который была произведена доставка, категорию товара, его название, цену на дату доставки, количество заказов и общая сумма сделки.

Может быть гораздо больше информации. Для упрощения задачи я взял только минимальный набор данных. Однако данных много и таблица состоит из нескольких тысяч строк.

Каждую компанию в первую очередь интересует прибыль и поэтому может возникнуть необходимость найти ответы на ряд вопросов, например:

1. Определите, в каком из городов доход был самым последним.
2. Какая дистрибьюторская сеть принесла компании наибольший доход.
3. Определите категорию продукта и конкретный продукт, который принес наибольшую прибыль.

В таблице приведены данные за два года, поэтому у руководства компании могут появиться примерно одни и те же вопросы применительно к конкретному интервалу времени, например, какой товар был наиболее востребован прошлым летом, или в прошлом году, какова была динамика продаж товаров различных категорий в течение года (по месяцам, кварталам) или кто из покупателей в прошлом месяце был наиболее значимым для компании. Также часто необходимо выявить лидеров продаж, например, создать ТОП-5 товаров, на которые был наибольший спрос в определенное время.

Решение задачи формулами

На первый взгляд, все эти задачи легко решаются простыми формулами и стандартными функциями.

Так, например, чтобы определить город, приносивший максимальный доход, нужно просто сложить сумму всех доставок по каждому из городов. Это можно сделать с помощью функции СУММЕСЛИ.

То есть нам предстоит создать отдельную таблицу, которая с помощью функции СУММЕСЛИ суммирует данные по каждому из городов.

Во-первых, вам нужно создать список уникальных значений. Для этого скопируйте значения столбца с названиями городов (столбец С) и вставьте их на новый лист. Кроме того, используя удаление дубликатов, мы оставим только уникальные значения.

Теперь воспользуемся функцией СУММЕСЛИ.

Сначала указываем диапазон значений, в котором искать условие (это столбец городов в исходной таблице), а затем задаем само условие. Эта строка нужна нам для суммирования итогов по конкретному городу, поэтому мы указываем ячейку с его названием в новой таблице. Ну а теперь задаем диапазон, значения которого необходимо просуммировать при выполнении условия — это столбец итогов.

Доход по городам с использованием функции СУММЕСЛИ Доход по городам с использованием функции СУММЕСЛИ

Получаем доход, полученный в конкретном городе. Распространяем формулу на всю выборку новой таблицы и получаем результат.

В результате значений общего дохода мы можем легко определить победителя и ответить на первый вопрос.

Для остальных задач также необходимо создавать отдельные таблицы и использовать формулы, которые могут быть довольно сложными, для расчета значений.

Такой подход к формированию необходимых отчетов очень трудоемок и требует не только времени на создание отчета, но и внимательности со стороны пользователя, ведь ошибиться в формуле при обработке большого количества достаточно легко данных. Ну а про аппетиты правительства вообще нет смысла говорить. Как только ответ на первый вопрос окажется на столе, тут же появится несколько, и вам придется заново пройтись по формулам и стянуть нужные данные в компактную таблицу…

Это не наш метод, тем более что сводные таблицы позволяют делать ровно то же самое, но в разы быстрее.

Читайте также: Сортировка и фильтрация данных в Excel

Создание сводной таблицы в Excel

Сводная таблица всегда строится на основе матрицы данных, которая должна иметь строку заголовка.

В моем примере у нас есть простой диапазон значений, но первая строка диапазона содержит заголовки столбцов, а значит, такой диапазон подходит и для создания сводной таблицы.

Поместите курсор таблицы в любую ячейку диапазона и на вкладке «Вставка» выберите «Сводная таблица.

Excel автоматически выделит весь неразрывный диапазон значений, и появится окно создания сводной таблицы, где будет указана абсолютная ссылка на этот диапазон.

Абсолютным и относительным ссылкам уже посвящено отдельное подробное видео, поэтому я не буду на этом останавливаться. Я просто хочу отметить, что, используя такой фиксированный диапазон в качестве источника данных сводной таблицы, мы не сможем в конечном итоге добавить в него новую информацию. Точнее, если в исходной таблице появляются новые строки, то для того, чтобы информация из них появлялась в сводной таблице, нам нужно вручную настроить эту область в настройках.

На самом деле умные таблицы полностью решают эту проблему. Поэтому перед созданием новой сводной таблицы стоит преобразовать исходные данные в смарт-таблицу.

Делается это очень просто – также устанавливаем курсор таблицы на любую ячейку диапазона и либо выбираем Таблица на вкладке Вставка, либо просто нажимаем комбинацию клавиш Ctrl+T.

Так как ряд заголовков уже существует в области, мы не снимаем соответствующий флажок.

Оставьте флажок «Таблица заголовков», снимите флажок «Таблица заголовков

Ну, так же, как и раньше, мы создадим сводную таблицу, но на основе смарт-таблицы.

Теперь источником данных является уже не фиксированная область на листе, а отдельный объект Table1. Это гарантирует нам, что новые данные автоматически добавляются в сводную таблицу при ее обновлении.

Вставьте сводную таблицу на новый лист.

Сводная таблица строится на основе данных Таблицы 1 и размещается на новом листе Сводная таблица строится на основе данных Таблицы 1 и размещается на новом листе.

На новом листе появится подсказка, сообщающая нам, что это лист сводной таблицы, а в правой части окна появится панель инструментов, чтобы мы могли спроектировать нужный нам отчет.

Совет 1 и боковая панель для создания сводной таблицы 2. Совет 1 и боковая панель для создания сводной таблицы 2.

Эту панель инструментов можно условно разделить на две части. Вверху находится список так называемых полей. Вы можете легко убедиться, что имена полей совпадают с именами заголовков исходной таблицы. То есть по названию поля мы легко можем понять, какой столбец данных за ним стоит.

Поля сводной таблицы соответствуют заголовкам смарт-таблиц Поля сводных таблиц соответствуют заголовкам смарт-таблиц

В нижней части расположены четыре области, которые относятся к четырем конструктивным элементам сводной таблицы. В зависимости от того, в какую область мы перетаскиваем то или иное поле, его данные окажутся в той или иной части сводной таблицы.

Области, образующие сводную таблицу Области, формирующие сводную таблицу

Например, нам нужно ответить на вопрос — поставка товаров в какой город позволила нам получить максимальный доход?

То есть мы должны в первую очередь получить список городов. Для этого я захватываю мышкой поле «По» и перетаскиваю его в область «Линии». Мы сразу получаем список названий всех городов из столбца City в исходной таблице.

Поле «Город» генерирует список уникальных названий городов из соответствующего столбца смарт-таблицы. Поле «Город» генерирует список уникальных названий городов из соответствующего столбца смарт-таблицы

То есть область Rows позволяет размещать данные в строках.

Если мы перетащим это поле в область Columns, то получим тот же список, но в одну строку, то есть каждое название стало заголовком отдельного столбца.

Поле в области «Столбцы» создает заголовки столбцов сводной таблицы. Поле в области «Столбцы» создает заголовки столбцов сводной таблицы

Я хочу вернуть поле в строки и закончить создание первой сводной таблицы. В конце концов, нам нужно вычислить общий доход по городам, поэтому мы просто перетаскиваем поле «Итого» в «Значения». Мы получаем точно такой же диск, как и раньше, но всего несколькими щелчками мыши.

Сводная таблица и таблица, полученные с помощью функции СУММЕСЛИМН Сводная таблица и таблица, полученные с помощью функции СУММЕСЛИМН

Пока не будем обращать внимание на внешний вид сводной таблицы, а сосредоточимся на ее функционале.

Обратите внимание, что в области строк есть имя поля, а в области значений фраза «Сумма по полю». Эта же фраза заменяется в заголовке соответствующего столбца сводной таблицы. Он указывает на то, что при формировании значений столбца сводной таблицы суммировались значения столбца Total смарт-таблицы.

Если щелкнуть маленький черный треугольник и выбрать в меню «Параметры поля значения», появится окно, в котором доступны все возможные операции. Чаще всего приходится использовать суммирование или подсчитывать количество значений.

Возможные операции в диапазоне значений Возможные операции в диапазоне значений

Так как у нас есть числовые значения в столбце Total исходной таблицы, Excel автоматически выбрал сумму, когда вы перетаскиваете поле в диапазон. Если столбец содержит текст, число будет выбрано по умолчанию.

Итак, в колонке клиентов в смарт-таблице есть названия торговых сетей, поэтому, если я перетащу их в область значений, мы увидим количество для этого поля. По сути, это значение показывает, сколько доставок было в том или ином городе, то есть сколько сделок было совершено.

Количество доставок в каждом городе Количество доставок в каждом городе

Ну а теперь создадим еще одну сводную таблицу, которая ответит на второй вопрос — какая сеть магазинов позволила компании получить наибольшую выручку?

Перейдем на лист с исходными данными и аналогично создадим еще одну сводную таблицу на новом листе.

В первую очередь нам нужен список сетей магазинов, поэтому перетащите поле «Клиент» в область «Линии». Итак, поле Total находится в значениях. Все готово!

Доходы от отдельных сетей магазиновДоходы от отдельных сетей магазинов

Ну и последняя задача – определить категорию товаров, которые принесли наибольший доход.

Действие по аналогии.

Общий оборот по категориям продуктов Общий доход по категориям продуктов

Вы можете сделать отчет более информативным, если также перенесете Items в область Lines. Тогда мы сможем получать информацию не только по определенным категориям товаров, но и по товарам внутри категории.

При этом важно соблюдать «вложенность» полей. Это означает, что наши продукты относятся к категориям, а не наоборот. Этот порядок задается порядком полей в области видимости. Если мы сейчас изменим их порядок, то получим следующее — будет отображаться список всех товаров, а вложенной информацией станет их принадлежность к какой-либо товарной категории.

В данном случае крайне неинформативно, поэтому верну все как было.

Но не забудьте район Столбцов. Например, перетащим в него поле «Клиент». Мы получим подробный отчет об объеме заказа по каждой товарной категории из отдельных сетей магазинов. В то же время каждую категорию можно развернуть, чтобы увидеть подробную информацию о каждом продукте и сети.

Подробный отчет по сетям магазинов и категориям товаров Подробный отчет по сетям магазинов и категориям товаров

На первый взгляд создание сводной таблицы с использованием полей может показаться довольно сложным и непредсказуемым, но небольшая практика быстро расставит все на свои места и позволит сразу переходить к нужным областям при создании отчетов.

Итак, у нас есть отчеты, которые отвечают на поставленные задачи. Осталось только немного отформатировать данные в таблицах, что сделает их более приятными для чтения.

Вариант 1: Обычная сводная таблица

Процесс создания мы рассмотрим на примере Microsoft Excel 2010, но алгоритм можно применить и к другим современным версиям этого приложения.

1. За основу возьмем таблицу выплат заработной платы сотрудникам в компании. Он содержит ФИО работника, пол, категорию, дату и сумму выплаты. Это означает, что каждому эпизоду выплаты одному сотруднику соответствует отдельная строка. Нам нужно сгруппировать случайно размещенные данные в этой таблице в одну сводную таблицу, при этом информация берется только за третий квартал 2016 года. Разберем, как это сделать на конкретном примере.
2. Прежде всего, давайте преобразуем исходную таблицу в динамическую. Это нужно для того, чтобы при добавлении строк и других данных они автоматически подтягивались в сводную таблицу. Наведите указатель мыши на ячейку, затем нажмите кнопку «Форматировать как таблицу» в блоке «Стили» на ленте и выберите любой понравившийся стиль таблицы.
3. Откроется диалоговое окно, в котором нас попросят указать координаты расположения стола. Однако по умолчанию координаты, предлагаемые программой, уже покрывают всю таблицу. Так что нам остается только согласиться и нажать «ОК». Однако пользователи должны знать, что они могут изменить эти настройки здесь, если захотят.
4. Таблица становится динамической и может автоматически расширяться. Также он получает имя, которое пользователь может при желании изменить на любое удобное для себя. Вы можете просмотреть или изменить название вкладки «Дизайн».
5. Чтобы приступить к созданию напрямую, выберите вкладку «Вставка». Здесь мы нажимаем на первую кнопку на ленте, которая называется «Сводная таблица». Откроется меню, где нужно выбрать, что мы будем создавать: таблицу или диаграмму. Наконец, нажмите «Сводная таблица».
6. В новом окне мы должны снова выбрать область или имя таблицы. Как видите, программа уже подтянула название нашей таблицы, так что тут больше делать нечего. В нижней части диалогового окна вы можете выбрать, где должна быть создана сводная таблица: на новом листе (по умолчанию) или на том же листе. Конечно, в большинстве случаев гораздо удобнее вести его на отдельном листе.
7. После этого на новом листе откроется форма для создания сводной таблицы.
8. В правой части окна находится список полей, а ниже четыре области: имена строк, имена столбцов, значения, фильтр отчета. Перетаскиваем нужные нам поля таблицы в области, соответствующие нашим потребностям. Не существует жесткого правила, какие поля перемещать, потому что все зависит от исходной таблицы и от конкретных задач, которые можно изменить.
9. В конкретном случае мы переместили поля «Пол» и «Дата» в область «Фильтр отчетов», «Категория персонала» в «Имя столбца», «Имя» в «Имя строки», «Сумма зарплаты» в «Значения». «. «. Следует отметить, что все арифметические вычисления данных, взятых из другой таблицы, возможны только в последней области. Пока мы проделывали такие манипуляции с переносом полей в области, соответственно изменилась и сама таблица в левой части окна.
10. Вот сводная таблица. Над ним отображаются фильтры по полу и дате.

Вариант 2: Мастер сводных таблиц

Создать сводную таблицу можно с помощью инструмента Мастер сводных таблиц, но для этого нужно сразу вывести ее на Панель быстрого доступа».

1. Перейдите в пункт меню «Файл» и нажмите «Параметры».

Заходим в раздел «Панель быстрого доступа» и выбираем команды из команд на ленте. В списке элементов найдите «Мастер сводных таблиц и сводных диаграмм». Выберите его, нажмите кнопку «Добавить», а затем «ОК».

В результате наших действий на панели быстрого доступа появился новый значок. Щелкаем по нему.

Это откроет мастер сводных таблиц. Есть четыре варианта источника данных, из которого будет формироваться сводная таблица, из которых указываем подходящий. Внизу следует выбрать, что мы будем создавать: сводную таблицу или диаграмму. Делаем выбор и идем «Далее».

Появится окно с диапазоном таблицы данных, который вы можете изменить при желании. Нам не нужно этого делать, поэтому мы просто идем «Далее».

Затем «мастер сводных таблиц» предлагает выбрать место, куда следует поместить новый объект: на тот же лист или на новый. Делаем выбор и подтверждаем его кнопкой «Готово».

Новый лист открывается точно такой же формы, как и при обычном создании сводной таблицы.

Все дальнейшие действия выполняются по тому же алгоритму, который был описан выше (см вариант 1).

Настройка сводной таблицы

Как мы помним из условий задачи, в таблице должны остаться только данные за третий квартал. При этом информация отображается за весь период. Покажем на примере, как его настроить.

1. Чтобы привести таблицу к нужному виду, нажмите кнопку рядом с фильтром «Дата». В нем поставьте галочку напротив надписи «Выбрать еще элементы». Затем снимите галочки со всех дат, которые не вписываются в период третьего квартала. В нашем случае это всего лишь одна дата. Подтверждаем действие.
2. Таким же образом мы можем отфильтровать по полу и выбрать, например, только одного мужчину для отчета.
3. Сводная таблица выглядит так.
4. Чтобы продемонстрировать, что вы можете управлять информацией в таблице по своему усмотрению, снова откройте форму списка полей. Перейдите на вкладку «Параметры» и нажмите «Список полей». Мы перемещаем поле «Дата» из области «Фильтр отчетов» в «Имя строки» и меняем области между полями «Категория персонала» и «Пол». Все операции выполняются с помощью простого перетаскивания элементов.
5. Теперь таблица выглядит совсем иначе. Столбцы разделены по полу, строки теперь разделены по месяцам, а фильтрация теперь может выполняться по категории персонала.
6. Если названия строк перенесены в список полей и дата установлена ​​выше имени, то именно даты выплат разбиваются на имена сотрудников.
7. Вы также можете отображать числовые значения таблицы в виде гистограммы. Для этого выделите ячейку с числовым значением, перейдите на вкладку «Главная», нажмите «Условное форматирование», выберите пункт «Гистограммы» и укажите понравившийся вид.
8. Гистограмма отображается только в одной ячейке. Чтобы применить правило гистограммы ко всем ячейкам таблицы, нажмите появившуюся рядом с гистограммой кнопку и в открывшемся окне переместите переключатель в положение «Ко всем ячейкам».
9. В результате наша сводная таблица стала выглядеть более презентабельно.

Второй способ создания предоставляет несколько дополнительных функций, но в большинстве случаев функционала первого варианта достаточно для выполнения поставленных задач. Сводные таблицы могут формировать данные в отчеты практически по любым критериям, которые пользователь указывает в настройках.

3. Как добавить поле

Чтобы иметь возможность добавить поле в нужную область, установите флажок рядом с названием.

По умолчанию Microsoft Excel добавляет поля в раздел «Макет» следующим образом:

• В область строк добавлены нечисловые значения;
• К диапазону добавляются числовые значения;
• Дата и время добавляются в область столбца.

4. Как удалить поле из сводной таблицы?

Чтобы удалить любое поле, вы можете сделать следующее:

• Снимите флажок рядом с тем, который вы ранее отметили.
• Щелкните правой кнопкой мыши поле и выберите «Удалить……».

И еще один простой и наглядный способ удалить поле. Перейдите к макету таблицы, нажмите на ненужный элемент и перетащите его за пределы макета. Как только вы вытащите его из рамки, рядом со значком появится галочка. Отпустите кнопку мыши и наблюдайте, как ваш стол мгновенно меняется.

5. Как упорядочить поля?

Изменить расположение индикаторов можно тремя способами:

1. Перетащите поле между 4 областями раздела с помощью мыши. Кроме того, вы можете нажать и удерживать имя в разделе «Поле», а затем перетащить его в нужную область в разделе «Макет». Это удалит его из текущей области и поместит в новое место.
2. Щелкните правой кнопкой мыши имя в разделе «Поле» и выберите область, куда вы хотите его добавить:
3. Щелкните поле в разделе «Макет», чтобы выбрать его. Это немедленно отобразит доступные параметры:

Любые внесенные вами изменения применяются немедленно.

Ну а если вы поняли, что сделали что-то не так, не забывайте, что есть «волшебная» комбинация клавиш CTRL+Z, которая отменяет сделанные вами изменения (если вы не сохранили их, нажав нужную клавишу).

6. Выберите функцию для значений (необязательно)

По умолчанию Microsoft Excel использует функцию суммы для чисел, которые вы вводите в диапазоне значений. Когда вы помещаете в эту область нечисловые (текстовые, даты или логические) или нулевые значения, к ним применяется функция Count».

Но, конечно, вы можете выбрать другой метод расчета. Щелкните правой кнопкой мыши поле значения, которое вы хотите изменить, выберите «Параметры поля значения», а затем выберите нужную функцию.

Я думаю, что названия операций говорят сами за себя, и здесь нет необходимости в дополнительных пояснениях. В крайнем случае попробуйте сами разные варианты.

Здесь же вы можете изменить имя на что-то более удобное и понятное для вас. Ведь он фигурирует в таблице, а значит и должен выглядеть соответственно.

В Excel 2010 и ниже опция «Суммировать значения по» также доступна на ленте — на вкладке «Параметры», в группе «Расчеты».

7. Используем различные вычисления в полях значения (необязательно)

Еще одна полезная функция позволяет вам представлять значения по-разному, например, отображать итоги в процентах или ранжировать значения от самого низкого до самого высокого и наоборот. Полный список вариантов расчета доступен здесь .

Это называется «Дополнительные расчеты». Доступ к ним можно получить, открыв вкладку «Параметры…», как описано выше.

Зацепка. Функция «Несколько метрик» может быть особенно полезна, когда вы добавляете одно и то же поле более одного раза и, как в нашем примере, одновременно показываете общий объем продаж и объем продаж в процентах от общего объема. Согласитесь, создавать такую ​​таблицу с обычными формулами придется долго. А тут — пара минут работы!

Итак, процесс создания завершен. Теперь пришло время немного поэкспериментировать, чтобы выбрать настройку, которая лучше всего подходит для вашего набора данных.

Форматирования сводной таблицы

Сначала поговорим о заголовках. Есть желающие сразу поменять, но есть один нюанс, который следует учитывать.

Заголовок меняется самым обычным способом — кликаем по ячейке с ним и потом меняем текст.

Изменить заголовки полей в диапазоне значений Изменить заголовки полей в диапазоне значений

Также можно выделить нужную ячейку с заголовком и нажать клавишу F2 для перехода в режим редактирования ее содержимого.

Важно помнить, что в сводной таблице имя заголовка не может совпадать с именем поля. То есть, если я захочу переименовать «Итог поля итог» в «Итого», из этого ничего не выйдет и появится ошибка.

Имя заголовка столбца не может совпадать с именем соответствующего поля. Заголовок столбца не может совпадать с именем соответствующего поля

Правда, этот нюанс можно обойти. Если вы добавите пробел в конце слова, это будет другое значение для Excel, и пользователь не увидит разницы.

Я просто хочу переименовать поля в «Количество заказов» и «Количество заказов». Первый заголовок также можно изменить на «Город».

Сводная таблица с измененными заголовками столбцов Сводная таблица с измененными заголовками столбцов

Осталось отформатировать значения самостоятельно. Прежде всего, давайте изменим числовой формат, сделав его денежным. В этом случае сразу приходит на ум использование соответствующего инструмента на вкладке «Главная.

Изменить формат числа в ячейке Изменить формат числа в ячейке

Однако если в столбце выбрана только одна ячейка, форматирование повлияет только на эту ячейку. В этом случае правильнее будет изменить формат числа для всего столбца, а для этого достаточно выбрать пункт Формат числа из контекстного меню, которое вызывается нажатием правой кнопки мыши на одной из ячеек столбца.

Затем в появившемся окне укажите нужный формат и задайте параметры.

Форматирование будет применено ко всем столбцам сразу.

Ну и еще на контекстной вкладке Дизайн, которая появляется только при выборе сводной таблицы, можно задать стиль таблицы в целом. Для этого необходимо либо выбрать одну из готовых цветовых схем, либо создать собственное стилевое оформление, указав форматирование для каждого элемента сводной таблицы в отдельности.

Общие и промежуточные итоги

А если говорить о контекстной вкладке Конструктор, то сразу следует сказать о настройках сводной таблицы, связанных с макетом.

Макет определяет, в какой части сводной таблицы должен появиться тот или иной элемент, то есть определяет структуру. В дополнение к данным, которые автоматически подтягиваются в сводную таблицу из источника, сводная таблица сама генерирует общие и промежуточные итоги для каждого столбца и строки.

Мы также можем контролировать расположение и видимость итогов и промежуточных итогов. Для этого есть соответствующие инструменты на контекстной вкладке Конструктор.

Промежуточные итоги в моем примере формируются из итогов по каждой категории товаров и появляются по умолчанию в строке с названием категории, то есть в шапке группы.

То есть, если мы просуммируем значения по каждому продукту, то получим значение, указанное в промежуточных итогах.

Не всегда необходимо отображать это значение. Поэтому, когда список расширяется, это довольно запутанно, если вы точно не знаете, что это значит. В этом случае вы можете отключить промежуточные итоги, выбрав соответствующую опцию.

Промежуточные итоги будут отображаться только в случае объединенной категории, когда данные по отдельным продуктам не отображаются.

Вы также можете отображать промежуточные итоги в отдельной строке внизу каждой категории товаров (второй пункт меню). Опять же, промежуточные итоги будут отображаться объединенными в основной строке, а при расширении категории они будут смещены в отдельную строку ниже.

Общие итоги также автоматически генерируются для каждой строки и столбца и не всегда необходимы. В соответствующем меню мы можем полностью отключить отображение итогов в сводной таблице, либо оставить итоги только по столбцам или только по строкам.

Макет сводной таблицы

Что ж, выбор макета также влияет на внешний вид сводной таблицы. Есть три варианта.

Во-первых, это сжатая форма. Этот параметр установлен по умолчанию, и мы видим его сразу после создания сводной таблицы.

При выборе второго варианта — схема структуры, для каждого поля в сводной таблице будет выделен отдельный столбец. Это значит, что теперь в первой колонке отображается только категория товаров, а во второй — сами товары.

Форма таблицы аналогична форме структуры, но промежуточные итоги из строки с названием категории смещены вниз.

В этом же меню есть еще одна настройка, позволяющая повторять или не повторять метки элементов.

Теперь вкладка отображается только в одной строке, и это опция неповторяющихся подписей. Если вы выберете второй вариант, название категории будет дублироваться в каждой строке.

Дублирующиеся товары — название категории в каждой строке Дублирующиеся товары — название категории в каждой строке

Ну а теперь со знанием дела приведем отчет к нужному виду — сводную таблицу вернем в сжатый вид, а потом пропустим промежуточные итоги по каждой категории.

Используя соответствующий инструмент, вставьте пустые строки после каждой категории, чтобы визуально отделить их друг от друга.

Ну а чтобы быстро скрыть или развернуть все категории, можно воспользоваться контекстным меню, вызываемым правым кликом по соответствующей ячейке. Есть раздел, где выбираем нужный вариант.

Развернуть или скрыть поле или все поля сразу Развернуть или скрыть поле или все поля сразу

Ну а если кнопки свернуть не нужны, их можно скрыть. Для этого отключите отображение на вкладке Контекстный анализ.

Поправим заголовки, выберем нужный стиль и наш отчет готов.

Как сделать сводную таблицу из нескольких таблиц

Часто вы будете создавать сводные отчеты из нескольких таблиц. Есть пара информационных щитов. Мы должны объединить их в общую. Для науки составим остатки на складах в двух магазинах.

Процедура создания сводной таблицы из нескольких листов такая же.

Создадим отчет с помощью Мастера сводных таблиц:

1. Вызываем меню «Мастер сводных таблиц и диаграмм». Для этого нажмите кнопку «Настроить панель быстрого доступа» и выберите «Дополнительные команды». Здесь на вкладке «Настройки» находим «Мастер сводных таблиц». Добавление инструмента на панель быстрого доступа После добавления:
2. Ставим курсор на первую табличку и нажимаем инструмент «Мастера». В открывшемся окне обратите внимание, что мы хотим создать таблицу в «несколько областей консолидации». Это означает, что мы должны объединить несколько мест с информацией. Тип отчета — «сводная таблица». «Дальше».
3. Следующим шагом будет создание полей. «Дальше».
4. Прописываем диапазон данных, по которым будем формировать сводный отчет. Выберите первую область вместе с заголовком — «добавить». Вторая область вместе с названием столбцов снова «добавить».
5. Теперь выберите первую область в списке. Ставим птицу на агрегат. Это первое поле сводного отчета. Даем ему имя — «Магазин 1». Выберите второй диапазон данных — поставьте птицу под цифрой «2». Название поля «Магазин 2». Нажимаем «Далее».
6. Выберите, где вы хотите разместить сводную таблицу. На существующем листе или новом. Лучше выбрать новый лист, чтобы не было нахлестов и смещений. У нас получилось так:

Как видите, всего за несколько кликов можно создавать сложные отчеты из нескольких листов или таблиц с разным объемом информации.

Как работать со сводными таблицами в Excel

Начнем с самого простого: добавления и удаления столбцов. Для примера рассмотрим сводную таблицу продаж по разным отделам (см выше).

Справа от сводной таблицы у нас была область задач, где мы выбирали столбцы в списке полей. Если он исчез, просто нажмите на планшет.

Давайте добавим еще одно поле в сводную таблицу для отчета. Для этого поставьте галочку напротив «Дата» (или напротив «Товар»). Отчет сразу меняется — отображается динамика продаж за день в каждом отделе.

Сгруппируем данные в отчете по месяцам. Для этого щелкните правой кнопкой мыши поле «Дата». Нажмите «Группировать». Выберите «по месяцам». В результате получается такая сводная таблица:

Чтобы изменить параметры в сводной таблице, снимите флажки рядом с существующими полями строки и введите другие поля. Давайте создадим отчет по названию продукта, а не по отделу.

А вот что будет, если убрать «дату» и добавить «отдел»:

Но такой отчет можно создать, перетаскивая поля между разными областями:

Чтобы изменить заголовок строки на заголовок столбца, выберите этот заголовок, щелкните контекстное меню. Нажмите «Перейти к заголовкам столбцов». Таким образом, мы переместили дату в столбцы.

Ставим поле «Отдел» перед названиями товаров. С помощью пункта меню «Перейти к началу».

Мы покажем детали о конкретном продукте. На примере второй сводной таблицы, которая показывает остатки на складах. Выберите ячейку. Щелкните правой кнопкой мыши — «развернуть».

В открывшемся меню выберите поле с данными, которые вы хотите отобразить.

Когда мы нажимаем на сводную таблицу, становится доступной вкладка с параметрами отчета. С помощью help можно менять заголовки, источники данных, групповую информацию.

Проверка правильности выставленных коммунальных счетов

С помощью сводных таблиц Excel легко проверить, насколько правильно сервисные организации начисляют арендную плату. Еще один положительный момент – экономия. Если ежемесячно проверять, сколько расходуется электричества и газа, можно найти резерв для экономии средств на оплату квартиры.

Для начала предлагаем создать сводную таблицу тарифов по всем счетам за электроэнергию. Для разных городов данные будут разными.

Для примера мы создали сводную таблицу тарифов для Москвы:

В учебных целях возьмем семью из 4 человек, проживающую в квартире площадью 60 кв м. Для проверки счетов за электроэнергию необходимо составить таблицы расчета на каждый месяц.

Первый столбец = первый столбец из сводной таблицы. Вторая – формула расчета просмотра:

= ставка * количество человек / показания счетчика / площадь

Для простоты рекомендуем создать промежуточную колонку, куда следует заносить показания счетчика (переменная составляющая).

Наши формулы относятся к листу, на котором размещена сводная таблица цен.

Если льготы используются при расчете счетов за электроэнергию, их также можно ввести в формулы. Сведения о начислениях запрашивайте в бухгалтерии обслуживающей вас организации. При смене тарифов вы меняете только данные в ячейках.

Работа со списком показателей сводной таблицы

Панель, формально называемая списком полей, является основным инструментом, используемым для упорядочивания таблицы в соответствии с вашими требованиями. Вы можете адаптировать его на свой вкус, чтобы сделать его более практичным .

Чтобы изменить способ отображения рабочего пространства, нажмите кнопку «Инструменты» и выберите нужный макет.

Вы также можете изменить размер панели по горизонтали, перетащив разделитель, отделяющий панель от листа.

Закрытие и открытие панели редактирования.

чтобы закрыть список полей в сводной таблице, достаточно нажать кнопку «Закрыть» (X) в правом верхнем углу панели. А вот как заставить его появиться снова не так очевидно 🙂

Чтобы показать его снова, щелкните правой кнопкой мыши в любом месте таблицы и выберите «Показать…» в контекстном меню.

Вы также можете нажать кнопку «Список полей» на ленте, которая находится на вкладке меню «Анализ».

Воспользуйтесь рекомендациями программы.

Как вы только что видели, создавать сводные таблицы довольно просто даже для чайников. Однако Microsoft делает еще один шаг вперед и предлагает автоматически генерировать отчет, наиболее подходящий для ваших исходных данных. Все, что вам нужно, это 4 клика:

1. Щелкните любую ячейку в исходном диапазоне ячеек или таблице.
2. На вкладке Вставка выберите Рекомендуемые сводные таблицы. Программа сразу отобразит несколько раскладок на основе ваших данных.
3. Нажмите на любой макет, чтобы увидеть предварительный просмотр.
4. Если вас устраивает предложение, нажмите кнопку «ОК» и добавьте понравившийся вариант на новый лист.

Как вы можете видеть на снимке экрана выше, Excel смог предоставить некоторые базовые настройки для моих исходных данных, которые намного отстают от сводных таблиц, которые мы создали вручную несколько минут назад. Конечно это только мое мнение 🙂

Но при всем при этом использование рекомендаций — это быстрый способ начать работу, особенно когда у вас много данных и вы не знаете, с чего начать. А потом этот вариант можно легко изменить по своему вкусу.

Давайте улучшим результат.

Теперь, когда вы знаете основы, вы можете перейти к вкладкам «Анализ» и «Дизайн» инструментов в Excel 2016 и 2013 (вкладки «Параметры» и «Дизайн» в 2010 и 2007). Они появляются, как только вы щелкаете в любом месте таблицы.

Вы также можете получить доступ к параметрам и функциям, доступным для определенного элемента, щелкнув по нему правой кнопкой мыши (мы уже говорили об этом, когда создавали его).

После того, как вы создали таблицу на основе исходных данных, вы можете уточнить ее, чтобы обеспечить более глубокий анализ.

Чтобы улучшить дизайн, перейдите на вкладку «Дизайн», где вы найдете множество предустановленных стилей. Чтобы получить собственный стиль, нажмите кнопку «Создать стиль…» внизу галереи «Стили сводной таблицы».

Чтобы настроить макет определенного поля, щелкните его, а затем нажмите кнопку «Параметры» на вкладке «Анализ» в Excel 2016 и 2013 (вкладка «Параметры» в 2010 и 2007). Кроме того, вы можете щелкнуть поле правой кнопкой мыши и выбрать «Параметры…» в контекстном меню.

На скриншоте ниже показан новый дизайн и макет.

Я изменил цветовую схему, а также постарался сделать таблицу более компактной. Для этого изменим параметры представления товара. Какие параметры я использовал — видно на скриншоте.

Я думаю, что стало еще лучше.

Как избавиться от заголовков «Метки строк» ​​и «Метки столбцов».

При создании сводной таблицы Excel по умолчанию использует сокращенную форму. Этот макет отображает «Ярлыки строк» ​​и «Ярлыки столбцов» в качестве заголовков. Согласитесь, это не очень информативно, особенно для новичков.

Простой способ избавиться от этих нелепых заголовков — перейти от компактного макета к структурированному или табличному формату. Для этого откройте вкладку «Дизайн», щелкните раскрывающийся список «Макет отчета» и выберите «Просмотреть в схеме» или «Просмотреть в виде таблицы» .

И вот что мы получаем в результате.

Появляются настоящие имена, как вы можете видеть на изображении справа, что имеет гораздо больше смысла.

Другое решение — перейти на вкладку «Анализ», нажать кнопку «Заголовки полей», отключить их. Однако это удалит не только все заголовки, но и выпадающие фильтры и сортировку. А для анализа данных отсутствие фильтров обычно не годится.

Как обновить сводную таблицу.

Хотя отчет связан с исходными данными, вы можете быть удивлены, узнав, что Excel не обновляет его автоматически. Это можно считать небольшим недостатком. Вы можете обновить его, выполнив операцию обновления вручную, или это произойдет автоматически при открытии файла.

Как обновить вручную.

1. Щелкните в любом месте хранилища.
2. На вкладке «Анализ» нажмите кнопку «Обновить» или нажмите клавиши ALT+F5.

Кроме того, вы можете щелкнуть правой кнопкой мыши и выбрать «Обновить» в появившемся контекстном меню.

Чтобы обновить все сводные таблицы в файле, нажмите кнопку «Обновить», а затем нажмите «Обновить все».

Примечание. Если внешний вид сводной таблицы сильно меняется после обновления, установите флажки «Автоматически изменять размер столбцов при обновлении» и «Сохранить форматирование ячеек при обновлении». Для этого откройте «Параметры сводной таблицы», как показано на изображении, и вы найдете там эти флажки.

После запуска обновления вы можете просмотреть статус или отменить его, если передумаете. Просто нажмите стрелку на кнопке «Обновить», а затем либо «Обновить статус», либо «Отменить обновление».

Автоматическое обновление сводной таблицы при открытии файла.

1. откройте вкладку параметров, как мы только что сделали.
2. В диалоговом окне «Параметры…» перейдите на вкладку «Данные» и установите флажок «Обновлять при открытом файле».

Как переместить на новое место?

Может быть, вы хотите переместить свое творение в новую книгу? Перейдите на вкладку «Анализ», нажмите кнопку «Действия», а затем — «Переместить…». Выберите новый пункт назначения и нажмите OK.

Как удалить сводную таблицу?

Если конкретный сводный отчет вам больше не нужен, вы можете удалить его несколькими способами.

• Если таблица находится на отдельном листе, просто удалите этот лист.
• Если он размещен вместе с какими-то другими данными на листе, выделите все мышью и нажмите клавишу Delete.
• Щелкните в любом месте сводной таблицы, которую хотите удалить, перейдите на вкладку «Анализ» (см снимок экрана выше) => группа «Действия», щелкните маленькую стрелку под кнопкой «Выбрать», выберите «Вся сводная таблица» и нажмите «Удалить.

Примечание. Если у вас есть сводная диаграмма, описанная выше процедура удаления сделает ее диаграммой по умолчанию, которую больше нельзя изменить или обновить.

Анализ

Как только вы добавите (неважно как) сводную таблицу, вы увидите новую вкладку «Анализ» на панели инструментов. Содержит большое количество различных инструментов и функций.

Рассмотрим каждый из них подробнее.

Активное поле

С помощью этого инструмента вы можете сделать следующее:

1. Сначала нужно выделить ячейку. Затем нажмите кнопку «Активное поле». В появившемся меню нажмите «Параметры поля».
2. Сразу после этого вы увидите следующее окно. Здесь вы можете указать тип операции, которая будет использоваться для суммирования данных в выбранном поле.
3. Кроме того, вы можете настроить числовой формат. Для этого нажмите на соответствующую кнопку.
4. Откроется окно «Формат ячеек».

Здесь вы можете указать, в какой форме вы хотите отображать результат анализа информации.

Группировать

Благодаря этому инструменту вы можете настроить группировку по выбранным значениям.

Вставить срез

Редактор Microsoft Excel позволяет создавать интерактивные сводные таблицы. В этом случае ничего сложного делать не нужно.

1. Выберите столбец. Затем нажмите на кнопку «Вставить диск».
2. В появившемся окне, например, выберите одно из предложенных полей (в дальнейшем их можно будет выбрать неограниченное количество). После того, как что-то выбрано, сразу активируется кнопка «ОК». Нажмите здесь.
3. В результате появится маленькое окошко, которое можно переместить куда угодно. Он предложит все возможные уникальные значения, которые есть в этом поле. Благодаря этому инструменту вы сможете снять сумму только в определенные месяцы (в данном случае). По умолчанию информация отображается за все время.
4. Вы можете нажать на любой из пунктов. Сразу после этого изменятся все значения в поле суммы.
5. Таким образом, можно будет выбрать любой период времени.
6. В любой момент все можно вернуть в первоначальный вид. Для этого нажмите на иконку в правом верхнем углу этого окна.

В этом случае мы могли отсортировать отчет по месяцам, потому что у нас было соответствующее поле. Но есть более мощный инструмент для работы с датами.

Вставить временную шкалу

Если вы нажмете на соответствующую кнопку на панели инструментов, то, скорее всего, увидите эту ошибку. Дело в том, что в нашей таблице нет ячеек, которым явно нужен формат данных «Дата.

В качестве примера создадим небольшую таблицу с разными датами.

Далее необходимо построить сводную таблицу.

Вернитесь на вкладку «Вставка». Нажмите на значок «Таблица». В появившемся подменю выбираем нужный нам вариант.

1. Затем нас просят выбрать диапазон значений.
2. Для этого достаточно выделить всю таблицу.
3. Сразу после этого адрес будет заменен автоматически. Здесь все очень просто, ведь он рассчитан на чайников. Нажмите кнопку OK, чтобы завершить сборку».
4. Редактор Excel предложит нам только один вариант, так как таблица очень простая (для примера больше и не нужно).
5. Попробуйте еще раз щелкнуть значок «Вставить временную шкалу» (на вкладке «Анализ»).
6. На этот раз ошибок не будет. Вам будет предложено выбрать поле для сортировки. Поставьте галочку и нажмите кнопку ОК».
7. Благодаря этому появится окно, в котором можно выбрать нужную дату с помощью ползунка.
8. Выбираем другой месяц, а данных нет, так как все расходы в таблице указаны только за март.

Источник данных

Если вы решили изменить поля, на которых будет основываться построение, гораздо проще сделать это в настройках, вместо того, чтобы удалять таблицу и создавать ее заново с учетом новых предпочтений.

Для этого нажмите на значок «Источник данных». Затем выберите пункт меню с таким же названием.

В результате появится окно, где можно снова выбрать нужный объем информации.

Действия

С помощью этого инструмента вы сможете:

• снять со стола;
• выделять;
• подвинь это.

Сервис

Здесь вы можете создать сводную диаграмму или изменить рекомендуемый тип таблицы.

Показать

Этот инструмент позволяет настроить внешний вид рабочей области редактирования.

Благодаря этому вы сможете:

• настроить отображение боковой панели со списком полей;
• включить или отключить кнопки плюс/минус;
• настроить отображение заголовков полей.

Что вызывает ошибку «Недопустимая ссылка на источник данных» в Excel

При работе с книгами Excel и построении сводных таблиц может возникнуть ошибка, препятствующая завершению операции. Основных причин может быть две, обе зависят от стороннего файла, с которым работает пользователь.

Первый — когда в процессе создания сводной таблицы используются данные из другого файла Excel, имя которого содержит квадратные скобки. Для устранения этой причины достаточно убрать скобки из имени файла.

Второй — используемый для работы файл, извлеченный пользователем из Internet Explorer, что также выдает ошибку «Неверная ссылка на источник данных». Решением проблемы, как и в первом случае, станет небольшая корректировка используемого файла, а точнее его расположения. Достаточно сохранить используемый файл и можно продолжить работу с ним.