Сводная таблица в excel как сделать для чайников

Как сделать сводные таблицы в Excel: пошаговая инструкция со скриншотами

Как систематизировать тысячи строк и преобразовать их в наглядный отчёт за несколько минут? Разбираемся на примере с квартальными продажами автосалона

Иллюстрация: Meery Mary для Skillbox Media

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

Разберёмся, для чего нужны сводные таблицы. На конкретном примере покажем, как их создать, настроить и использовать. В конце расскажем, можно ли делать сводные таблицы в «Google Таблицах».

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

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

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

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

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

Разберёмся пошагово, как это сделать с помощью сводной таблицы.

Шаг 1

Создаём сводную таблицу

Чтобы сводная таблица сработала корректно, важно соблюсти несколько требований к исходной:

Теперь переходим во вкладку «Вставка» и нажимаем на кнопку «Сводная таблица».

Появляется диалоговое окно. В нём нужно заполнить два значения:

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

Excel создал новый лист. Для удобства можно сразу переименовать его.

Слева на листе расположена область, где появится сводная таблица после настроек. Справа — панель «Поля сводной таблицы», в которые мы будем эти настройки вносить. В следующем шаге разберёмся, как пользоваться этой панелью.

Шаг 2

Настраиваем сводную таблицу и получаем результат

В верхней части панели настроек находится блок с перечнем возможных полей сводной таблицы. Поля взяты из заголовков столбцов исходной таблицы: в нашем случае это «Марка, модель», «Цвет», «Год выпуска», «Объём», «Цена», «Дата продажи», «Продавец».

Нижняя часть панели настроек состоит из четырёх областей — «Значения», «Строки», «Столбцы» и «Фильтры». У каждой области своя функция:

Настроить сводную таблицу можно двумя способами:

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

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

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

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

Теперь добавим модели автомобилей, которые эти менеджеры продали. По такому же принципу перетянем поле «Марка, модель» в область «Строки».

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

Определяем, какая ещё информация понадобится для отчётности. В нашем случае — цены проданных автомобилей и их количество.

Чтобы сводная таблица самостоятельно суммировала эти значения, перетащим поля «Марка, модель» и «Цена» в область «Значения».

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

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

Шаг 3

Настраиваем фильтры сводной таблицы

Чтобы можно было фильтровать информацию сводной таблицы, нужно перенести требуемые поля в область «Фильтры».

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

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

В блоке фильтров нажмём на стрелку справа от поля «Год выпуска»:

В появившемся окне уберём галочку напротив параметра «Выделить все» и поставим её напротив параметра «2017». Закроем окно.

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

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

Шаг 4

Проводим дополнительные вычисления

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

Кликнем правой кнопкой на любое значение цены в таблице. Выберем параметр «Дополнительные вычисления», затем «% от общей суммы».

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

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

Чтобы снова раскрыть данные об автомобилях — нажимаем +.

Чтобы значения снова выражались в рублях — через правый клик мыши возвращаемся в «Дополнительные вычисления» и выбираем «Без вычислений».

Шаг 5

Обновляем данные сводной таблицы

Предположим, в исходную таблицу внесли ещё две продажи последнего дня квартала.

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

Переходим на лист сводной таблицы. Во вкладке «Анализ сводной таблицы» нажимаем кнопку «Изменить источник данных».

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

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

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

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

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

Теперь у менеджера Соколова П. изменились данные в столбце «Цена, руб.».

Как использовать сводные таблицы в «Google Таблицах»? Нужно перейти во вкладку «Вставка» и выбрать параметр «Создать сводную таблицу». Дальнейший ход действий такой же, как и в Excel: выбрать диапазон таблицы и лист, на котором её нужно построить; затем перейти на этот лист и в окне «Редактор сводной таблицы» указать все требуемые настройки. Результат примет такой вид:

Другие материалы Skillbox Media для менеджеров

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

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

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

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

Примечание: Данные должны быть организованы в столбцы с одной строкой заголовка.

На вкладке Вставка нажмите кнопку Сводная таблица.

Это приведет к созданию сводная таблица на основе существующей таблицы или диапазона.

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

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

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

Примечание: В зависимости от параметров ИТ-среды
организации в кнопку может быть включено имя вашей организации. Например, «Из Power BI (Майкрософт)»

Получение из внешнего источника данных

Получение из модели данных

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

Получение из Power BI

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

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

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

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

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

Затем измените функцию в разделе Операция. Обратите внимание на то, что при изменении метода вычисления Excel автоматически добавляет его название в раздел Пользовательское имя (например, «Сумма по полю имя_поля»), но вы можете изменить имя. Чтобы изменить числовой формат для всего поля, нажмите кнопку Числовой формат.

Совет: Так как при изменении способа вычисления в разделе Операция обновляется имя поля сводной таблицы, не рекомендуется переименовывать поля сводной таблицы до завершения ее настройки. Вместо того чтобы вручную изменять имена, можно нажать кнопку Найти и заменить ( CTRL+H), в поле Найти ввести Сумма по полю, а поле Заменить оставить пустым.

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

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

Отображение значения как результата вычисления и как процента

Просто перетащите элемент в раздел Значения дважды, а затем настройте параметры Операция и Дополнительные вычисления для каждой из копий.

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

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

На странице «Создание собственного сводная таблица» выберите новый лист или существующий лист, чтобы выбрать назначение сводная таблица.

Примечание: Рекомендуемые сводные таблицы доступны только Microsoft 365 подписчикам.

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

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

Выберите данные в сетке или введите диапазон в текстовом поле.

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

Получение из Power BI

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

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

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

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

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

Измените вычисление по умолчанию, щелкнув правой кнопкой мыши любое значение в строке и выбрав параметр «Суммировать значения по «.

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

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

Сделайте выбор из списка.

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

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

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

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

Все данные в столбце должны иметь один и тот же тип. Например, не следует вводить даты и текст в одном столбце.

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

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

Щелкните ячейку в диапазоне исходных данных и таблицы.

На вкладке Вставка нажмите кнопку Рекомендуемые сводные таблицы.

«Рекомендуемые сводные таблицы» для автоматического создания сводной таблицы» loading=»lazy»>

Excel проанализирует данные и предоставит несколько вариантов, как в этом примере:

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

Щелкните ячейку в диапазоне исходных данных и таблицы.

На вкладке Вставка нажмите кнопку Сводная таблица.

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

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

Нажмите кнопку ОК. Excel создаст пустую сводную таблицу и выведет список Поля сводной таблицы.

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

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

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

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

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

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

Вы можете настроить такие параметры в диалоговом окне Параметры поля на вкладке Дополнительные вычисления.

Отображение значения как результата вычисления и как процента

Просто перетащите элемент в раздел Значения дважды, щелкните значение правой кнопкой мыши и выберите команду Параметры поля, а затем настройте параметры Суммировать по и Дополнительные вычисления для каждой из копий.

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

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

Работа со сводными таблицами в Excel на примерах

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

Исходный материал – таблица с несколькими десятками и сотнями строк, несколько таблиц в одной книге, несколько файлов. Напомним порядок создания: «Вставка» – «Таблицы» – «Сводная таблица».

А в данной статье мы рассмотрим, как работать со сводными таблицами в Excel.

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

Первый этап – выгрузить информацию в программу Excel и привести ее в соответствие с таблицами Excel. Если наши данные находятся в Worde, мы переносим их в Excel и делаем таблицу по всем правилам Excel (даем заголовки столбцам, убираем пустые строки и т.п.).

Дальнейшая работа по созданию сводной таблицы из нескольких файлов будет зависеть от типа данных. Если информация однотипная (табличек несколько, но заголовки одинаковые), то Мастер сводных таблиц – в помощь.

Мы просто создаем сводный отчет на основе данных в нескольких диапазонах консолидации.

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

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

Мастер сводных таблиц при таких исходных параметрах выдаст ошибку. Так как нарушено одно из главных условий консолидации – одинаковые названия столбцов.

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

Покажем, к примеру, количество проданного товара.

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

Детализация информации в сводных таблицах

Из отчета (см.выше) мы видим, что продано ВСЕГО 30 видеокарт. Чтобы узнать, какие данные были использованы для получения этого значения, щелкаем два раза мышкой по цифре «30». Получаем детальный отчет:

Как обновить данные в сводной таблице Excel?

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

Курсор должен стоять в любой ячейке сводного отчета.

Правая кнопка мыши – обновить.

Чтобы настроить автоматическое обновление сводной таблицы при изменении данных, делаем по инструкции:

Изменение структуры отчета

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

Если бы мы добавили столбцы внутри исходной таблицы, достаточно было обновить сводную таблицу.

После изменения диапазона в сводке появилось поле «Продажи».

Как добавить в сводную таблицу вычисляемое поле?

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

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

Инструкция по добавлению пользовательского поля:

Группировка данных в сводном отчете

Для примера посчитаем расходы на товар в разные годы. Сколько было затрачено средств в 2012, 2013, 2014 и 2015. Группировка по дате в сводной таблице Excel выполняется следующим образом. Для примера сделаем простую сводную по дате поставки и сумме.

Щелкаем правой кнопкой мыши по любой дате. Выбираем команду «Группировать».

В открывшемся диалоге задаем параметры группировки. Начальная и конечная дата диапазона выводятся автоматически. Выбираем шаг – «Годы».

Получаем суммы заказов по годам.

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

Сводная таблица в Excel. Как сделать?

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

Видеоурок: Как создать сводную таблицу в Excel

Что такое сводные таблицы в Excel? Пошаговая инструкция

Сводные таблицы это инструмент Excel для суммирования и анализа больших объемов данных.

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

Она содержит данные:

Теперь, представим, что наш руководитель поставил задачу вычислить:

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

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

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

Для создания таблицы выполните следующие действия:

После нажатия кнопки “ОК” таблица будет создана.

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

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

Области сводной таблицы в Excel

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

Ниже вы узнаете подробней об областях:

Что такое кэш сводной таблицы

При создании сводной таблицы, Excel создает кэш данных, на основе которых будет построена таблица.

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

Кэш данных увеличивает размер Excel-файла.

Область “Значения”

Область “Значения” включает в себя числовые элементы таблицы. Представим, что мы хотим отразить объем продаж регионов по месяцам (из примера в начале статьи). Область закрашенная желтым цветом, на изображении ниже, отражает значения размещенные в области “Значения”.

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

Область “Строки”

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

Область”Столбцы”

Заголовки вверху значений таблицы называются “Столбцы”.

На примере ниже красным выделены поля “Столбцы”, в нашем случае это значения месяцев.

Область “Фильтры”

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

Сводные таблицы в Excel. Примеры

На примерах ниже мы рассмотрим, как с помощью сводных таблиц ответить на три вопроса:

Прежде чем анализировать данные, важно решить каким образом должны выглядеть данные таблицы (какие данные разметить в колонки, строки, значения, фильтры). Например, если нам нужно отобразить данные продаж клиентов по регионам, то следует поместить названия регионов в строки, месяцы в колонки, значения продаж в поле “Значения”. Как только вы представили каким образом вы видите итоговую таблицу – начинайте её создание.

В окне “Поля сводной таблицы” размещены области и поля со значениями для размещения:

Поля создаются на основе значений исходного диапазона данных. Раздел «Области» – это место, где вы размещаете элементы таблицы.

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

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

Пример 1. Какой объем выручки у региона Север?

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

Получим ответ: продажи региона Север составляют 1 233 006 966 ₽:

Пример 2. ТОП пять клиентов по продажам

Для того чтобы вычислить рейтинг ТОП пяти клиентов, нам нужно:

У нас получится следующая таблица:

По-умолчанию, система Excel сортирует данные в таблице в алфавитном порядке. Для сортировки данных по объему продаж выполните следующие действия:

Как результат мы получим отсортированный список клиентов по объему выручки.

Пример 3. Какое место по выручке занимает клиент Лудников ИП в регионе Восток?

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

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

Для сортировки данных выполните следующие шаги:

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

Существует несколько вариантов для решения этой задачи. Вы можете перенести поле “Регион” в область “Фильтры” и в строчках разместить данные продаж клиентов, таким образом отразив данные по выручке только клиентов региона Восток.

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

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

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

Классический способ составления сводной таблицы

В качестве примера рассмотрим алгоритм действий для составления сводных таблиц в Microsoft Excel 2019. Эта же логика может применяться и в более ранних версиях программы.

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

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

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

Шаг 1. Создаем “умную таблицу”

Как только мы сделаем нашу базовую таблицу “умной” (или динамической, то есть способной автоматически изменяться), при добавлении в нее новых строк с данными, они в автоматическом режиме будут отображаться и в сводной таблице.

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

Шаг 2. Формируем сводную таблицу

Шаг 3. Применяем фильтры и другие настройки

Теперь вернемся к нашей задаче. Нам нужно отфильтровать данные по полу (женский) и оставить только строки, которые относятся к виду спорта “теннис”.

Различные варианты сводной таблицы

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

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

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

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

Также можно попробовать добавить в таблицу еще больше информации. Для этого снова открываем список полей и перетаскиваем поле “Продано, шт.” в область “Строки”.

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

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

Гистограмма настроена для выделенной ячейки. Осталось только нажать на кнопку справа от ячейки и выбирать одну из опций “Применить правило форматирования ко всем ячейкам…”.

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

Использование Мастера сводных таблиц

Сводная таблица может быть создана с помощью инструмента под названием “Мастер сводных таблиц”. Но предварительно нужно вынести значок Мастера на Панель быстрого доступа. Для этого выполняем следующую цепочку действий:

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

Заключение

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

Как создать свою первую сводную таблицу в Microsoft Excel

Russian (Pусский) translation by Ellen Nelson (you can also view the original English article)

Мы все перегружены данными. Больше чем что-нибудь, нам нужен простой способ для обработки больших объемов данных и поиска в них смысла. Если вы работаете с таблицами в Microsoft Excel, то Сводные таблицы это идеальное решение для больших объёмов данных данных. Сводные таблицы так же просты в использовании и мощны, как и другие инструменты.

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

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

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

Что такое сводная таблица?

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

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

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

Может быть сложно понять данные из таблицы. Что, если я захотел узнать общую сумму, на которую я выставил счёт каждому клиенту? Конечно, я мог бы отфильтровать по каждому клиенту и подытожить данные, но есть сделать это можно куда проще.

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

Сводная таблица в Excel, показан пример счетов клиентов.

Сводная таблица, показанная справа является отличным способом подытоживания данных. Я показал каждого клиента «Client» в отдельной строке и подвел итоги «amount billed».

Обратите внимание, что в исходной таблице я выполнил несколько разных видов работы для клиента под «Schoen-Jacobson». Сводная таблица подводит итог этих счетов в той же строке, что очень удобно.

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

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

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

Как создать сводную таблицу Excel (смотрите и учитесь)

Когда-нибудь, вам придётся рассмотреть инструмент «сводные таблицы» в действии и задумаетесь о том, как его использовать. Ознакомьтесь с видео ниже, чтобы узнать, как быстро создать сводную таблицу в Excel:

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

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

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

2. Создайте сводную таблицу в Excel

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

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

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

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

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

3. Перетащите поля

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

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

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

Конструктор отчётов сводных таблиц Excel.

Начнем с перетаскивания Client в область Стро́ки. Нажмите и перетащите Client из списка полей в пустую область под надписью Строки.

Когда мы перетаскиваем Client в область Стро́ки (Rows), сводная таблица Excel помещает каждого клиента в отдельную строку.

Вот, у нас что-то получается! Так как мы поместили поле Clients в область Строки, сводная таблица размещает каждого клиента в отдельную строку.

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

На скриншоте ниже ещё один пример. Я перетащил Project Type в область Столбцы. Теперь наша сводная таблица Excel показывает каждый тип проекта из наших исходных данных в качестве отдельного столбца.

На этот раз я перетащил Project Type в область столбцов. Теперь, каждый тип проекта показывается в отдельном столбце.

Я отменю эти изменения и верну обратно поле Client в область Стро́ки.

Что, если мы хотим узнать, на сколько мы выставили счёт каждому клиенту? Помните что у нас в исходных данных в столбце Amount Billed (Сумма выставленных счетов). Нам также нужно добавить эти суммы в таблицу.

Поскольку мы хотим видеть эти суммы в виде числовых значений, перетащите Amount Billed в область Значения (н правый ижний угол).

Количество клиентов Client в сводной таблице Excel.

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

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

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

После того, как вы перебросили Amount Billed в область Значения, кликните правой кнопкой мыши по данным и выберите Итоги по > Сумма, чтобы подсчитать общую сумму на клиента, вместо подсчета количества раз.

Мы создали нашу первую полезную сводную таблицу, суммируя суммы, на которые выставили клиентам счета. Теперь давайте добавим второе поле, чтобы увидеть ещё более подробную информацию.

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

Для этого давайте возьмем поле Project Type и перетащим его в область Строки, поместите его над Client.

Project Type добавлен в сводную таблицу Excel.

Теперь, в нашей сводной таблице есть два типа информации: сумма, на которую мы поработали над каждым типом проекта (Accounting, Legal Work, Screencasts, Training и Tutorials); а так же показаны клиенты для каждого вида работ. Так как мы добавили два типа данных в сводную таблицу Excel, то мы можем увидеть «лучших» клиентов по каждому виду работ.

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

Повторение — мать учения!

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

У вас есть вопросы о том, как использовать сводную таблицу? Если так — дайте мне знать в комментариях ниже.

Обзор сводных таблиц и сводных диаграмм

С помощью сводной таблицы можно создавать сводки, анализировать, изучать и представлять сводные данные. Сводные диаграммы дополняют сводные таблицы, позволяя визуализировать содержащиеся в них данные. Кроме того, с помощью сводных диаграмм можно легко отображать сравнения, шаблоны и тенденции. И сводные таблицы, и сводные диаграммы помогают принимать взвешенные решения на основании критически важных данных вашей организации. Вы также можете подключаться к внешним источникам данных, например к таблицам SQL Server, кубам служб SQL Server Analysis Services, Azure Marketplace, файлам подключения к данным Office (ODC-файлам), XML-файлам, базам данных Access и текстовым файлам, чтобы создавать сводные таблицы, или создавать на основе сводных таблиц другие таблицы.

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

Сведения о сводных таблицах

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

запрос больших объемов данных различными понятными способами;

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

развертывание и свертывание уровней представления данных для получения точных сведений о результатах, детализация итоговых данных по интересующим вопросам;

перемещение строк в столбцы или столбцов в строки («сведение») для просмотра различных сводок на основе исходных данных;

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

представление кратких, наглядных отчетов с примечаниями на веб-страницах или в напечатанном виде.

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

Соответствующая сводная таблица

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

Изучать данные. Вот какие действия доступны.

Развертывание и свертывание данных, а также отображение базовых сведений, которые относятся к значениям.

Сортировка, фильтрация и группировка полей и элементов.

Изменение функций сведения и добавление настраиваемых вычислений и формул.

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

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

Добавление, изменение порядка и удаление полей.

Изменение порядка полей или элементов.

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

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

Отображение промежуточных итогов выше или ниже их строк.

Настройка ширины колонок при обновлении.

Перемещение поля столбца в область строк или поля строки в область столбцов.

Объединение или отмена объединения ячеек для внешних элементов строки и столбца.

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

Изменение способа отображения ошибок и пустых ячеек.

Изменение способа отображения элементов и подписей без данных.

Отображение и скрытие пустых строк.

Изменять формат. Вот какие действия доступны.

Ручное и условное форматирование ячеек и диапазонов.

Изменение стиля общего формата сводной таблицы.

Изменение числового формата для полей.

Включение форматирования сервера OLAP.

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

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

Вот сводная диаграмма, основанная на приведенном выше примере сводной таблицы.

Дополнительные сведения см. в статье Создание сводной диаграммы.

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

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

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

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

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

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

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

Таблицы Excel Таблиц Excel изначально представлены в виде списка и отлично подходят в качестве исходных данных для сводной таблицы. При обновлении сводной таблицы новые и обновленные данные из таблицы Excel автоматически включаются в операцию обновления.

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

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

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

Файлы подключения к данным Office Если вы используете файл подключения к данным Office (ODC-файл) для получения внешних данных для сводной таблицы, вы можете вводить данные непосредственно в нее. Рекомендуется получать внешние данные для отчетов с помощью ODC-файлов.

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

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

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

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

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

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

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

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

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Как создать простейшую сводную таблицу в Excel?

В этой части самоучителя подробно описано, как создать сводную таблицу в Excel. Данная статья написана для версии Excel 2007 (а также для более поздних версий). Инструкции для более ранних версий Excel можно найти в отдельной статье: Как создать сводную таблицу в Excel 2003?

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

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

В данном примере в столбце Amount содержатся числовые значения, поэтому в области Σ Значения (Σ Values) будет по умолчанию выбрано Сумма по полю Amount (Sum of Amount). Если же в столбце Amount будут содержаться нечисловые или пустые значения, то в сводной таблице по умолчанию может быть выбрано Количество по полю Amount (Count of Amount). Если так случилось, то Вы можете изменить количество на сумму следующим образом:

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

Если необходимо отобразить объемы продаж в денежных единицах, следует настроить формат ячеек, которые содержат эти значения. Самый простой способ сделать это – выделить ячейки, формат которых нужно настроить, и выбрать формат Денежный (Currency) в разделе Число (Number) на вкладке Главная (Home) Ленты меню Excel (как показано ниже).

В результате сводная таблица примет вот такой вид:

Обратите внимание, что формат валюты, используемый по умолчанию, зависит от настроек системы.

Рекомендуемые сводные таблицы в последних версиях Excel

В последних версиях Excel (Excel 2013 или более поздних) на вкладке Вставка (Insert) присутствует кнопка Рекомендуемые сводные таблицы (Recommended Pivot Tables). Этот инструмент на основе выбранных исходных данных предлагает возможные форматы сводных таблиц. Примеры можно посмотреть на сайте Microsoft Office.

Как сводные таблицы Excel помогут сэкономить ваше время?

Научитесь использовать все прикладные инструменты из функционала MS Excel.

Порой у многих специалистов в экономике, бухгалтерии, анализе и финансах встает вопрос: «Как работать с таблицами, в которых десятки или сотни строк?» или «Как структурировать и анализировать большие таблицы?».

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

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

В чем полезность сводных таблиц

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

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

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

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

Сначала предлагаем разобраться в том, что же такое Сводная таблица и «с чем ее едят».

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

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

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

Прежде чем создавать Сводную таблицу необходимо подготовить таблицу с исходными данными для дальнейшего анализа:

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

Вот мы и добрались до главного вопроса, который нас интересовал – создание Сводной таблицы в Excel. Начнем разбираться.

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

Сначала откроем таблицу, убедимся, что она удовлетворяет всем требованиям и со спокойной душой перейдем к следующему шагу. Найдем вкладку «Вставка» на панели быстрого доступа. Перед нами появится лента с доступным функционалом. Мы создаем Сводную таблицу, поэтому ищем одноименную кнопку. Она будет самой первой на панели.

«Кликнем» на значок, и перед нами появится диалоговое окно с перечнем необходимых параметров:

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

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

Сверху мы видим область, где перечислены все атрибуты исходной таблицы: «Дата поступления», «Наименование товара», «Отдел» и так далее.

Теперь можно крутить, вертеть данные как угодно, придумывая концепции для Сводной таблицы.

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

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

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

Посмотрите, какие места заняли данные в нижней части панели настроек: поле «Отдел», содержащее текстовые значения, переместилось в область «Строки», а численные данные — в «Значения».

То же самое произойдет, если мы добавим в таблицу любое числовое поле (например, «Количество»): у текущей таблицы появится столбец, и в области «Значения» — «Сумма по полю Количество».

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

Если нас не устраивает такая ориентация таблицы, то из области «Строки» можно перетащить «Отдел» в область «Столбцы»: передвиньте курсор, удерживая кнопку мышки в нажатом положении.

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

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

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

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

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

Итак, какие выводы мы можем сделать по получившейся таблице? В магазин больше всего закупили Аксессуаров (на сумму 267660 руб.), причем самый дорогой товар стоил 2700 руб.

Как Вы могли заметить, мы еще ни разу не пользовались областью «Фильтры» на панели параметров. Этот раздел нужен для того, чтобы можно было сортировать данные по заданным критериям.

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

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

Над всеми строками появилось еще одно поле — «Дата поступления», в котором мы можем выбрать дату, нажав на треугольник в конце строчки (Все).

Теперь легко узнать на какую суммарную стоимость пришел товар, сколько стоил самый дорогой в определенный день. Например, за 04.05.2017.

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

Как обновлять таблицу

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

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

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

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

Удаление Сводной таблицы

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

Эта операция тоже не требует большой последовательности действия: выделим область, где находится таблица (чтобы сэкономить немного времени воспользуемся клавишами Ctrl + A — выделить все), и нажмем delete (или на правую кнопку мыши, а затем на «Удалить» в появившемся списке)

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

Добавление новых столбцов/таблиц

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

А что делать, когда в Сводную таблицу требуется добавить дополнительную колонку с данными?

Для начала новый столбец нужно вставить в исходную таблицу (источник данных), а потом увеличить диапазон для Сводной таблицы. Если нужно добавлять целую таблицу, то сперва ее нужно объединить с исходной, и потом изменять диапазон.

Вот мы добавили к источнику столбик «Цена с НДС». Теперь заходим в раздел «Анализ» и открываем «Источник данных».

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

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

Заключение

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

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

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

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

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

Рекомендуем скачать бесплатный гайд с горячими клавишами, чтобы ваша работа в Excel стала еще продуктивнее!

Если осталось что-то, на чем мы не заострили внимание, а вам бы очень хотелось это узнать, пишите нам об этом, и мы не оставим ваш интерес неудовлетворенным.

Научитесь использовать все прикладные инструменты из функционала MS Excel.

Самоучитель excel сводные таблицы

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

​Смотрите также​ Excel и средства​ выбираем опцию:​ группировка. Допустим, нам​ и выделите: «Мастер​ в Excel?​Работа с примечаниями и​ высоты строк в​

​ командой сайта office-guru.ru​$486​ пункт Разгруппировать в​ Очистить все;​

​ …), в которых​ суммирования, анализа и​Создание шаблонов и​ таблица с данными​ за месяц или​ диаграмм». Нажмите на​ Excel​Отслеживание исправлений в Excel​Вставка и удаление строк​Перевел: Антон Андронов​South​ сводными таблицами/ Параметры/​ Товар и Продажи​ нужное место.​ столбца, содержащего наименования​ достаточно двойного клика​

Подготовка исходной таблицы

​ для решения следующей​ будут в изобилии​

​ переместился в правую​Многоячеечные формулы массива в​ Excel​Перемещение и скрытие строк​ Excel для чайников​$948​ по полю Дата​ Продажи будет автоматически​ Для этого нужно​ Сортировка от Я​ таблице, чтобы был​ каждому Товару». ​ показано на рисунке​ разрезах. Рассмотрим процесс​ и шифрованием.​ новый лист, выбрав​Источник информации – отчет​ колонку и нажмите​ Excel​Завершение и защита рабочих​ и столбцов в​

​позволит Вам легко​Smith​ поставки. В этом​ помещено в область​ навести на него​

​ до А.​ создан отдельный лист​Имея исходную таблицу в​ выше (Все значения​ создания несложных Сводных​Как настроить автосохранение​ на вкладке «Действия»​ с данными.​ ОК.​Одноячеечные формулы массива в​ книг​ Excel​ понять и усвоить​North​ случае окно Группировка​ Значения;​ курсор мыши в​

​Теперь предположим, что Товар​ с отобранными из​ формате EXCEL 2007,​

​Перетащите мышкой еще одну​ Списке полей (в​ Баранки – наиболее​ исходной таблицей строками.​ для создания Сводной​ быть в одном​Сводные таблицы (Вставка/ Таблицы/​Защита персональных данных​По умолчанию в сводную​

​ группировка по дате,​

​Теперь Сводная таблица показывает,​ в ту же​ Названия отчета, Названия​ его нужно выводить​

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

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

​ вся информация из​ с соответствующим значением.​ под рукой.​ Excel​Условное форматирование​Настройка шрифта в Excel​ к более сложным​Barnes​ сколько партий Товара​ область Значения. В​

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

​ выполняются следующие условия:​ ячеек в Excel.​ того столбца, который​ Щелкаем правой кнопкой​Ставим курсор в любом​Применение формул массива в​Условное форматирование в Excel​Выравнивание текста в ячейках​

​ темам. Самоучитель научит​North​ поставлялось каждый месяц.​

​ Сводной таблице появится​ левую клавишу мыши​ Для этого выделите​ Товара «Апельсины». Для​ меню Работа с​ чтобы это реализовать,​имеется исходная таблица с​Скрыть лист в​ мы добавляем в​ мыши.​ месте таблицы с​ Excel​

​Сводные таблицы и анализ​ Excel​ Вас пользоваться интерфейсом​01/03/2016​К ячейкам Сводной таблицы​ 2 столбца подсчитывающими​ и перетащить удаляемое​ ячейку со значением​ этого дважды кликнем​ таблицами/ Конструктор/ Сервис​ возможно, потребуется вести​ множеством строк (записей),​ списке скрытых листов.​ отчет.​Из выпавшего меню выбираем​

​ данными. Вызываем мастер​Подходы к редактированию формул​ данных​

​Границы, заливка и стили​ Excel, применять формулы​2016-0008​ можно применить правила​ суммы продаж;​

​ поле за границу​ Баранки и установите​ на значении 646720.​ выбрать пункт Сводная​ более подробные записи​ речь идет о​Проверка ввода данных​В нашем примере –​ «Группировку». Откроется инструмент​ сводных таблиц, нажимая​ массива в Excel​
​Общие сведение о сводных​ ячеек в Excel​ и функции для​$543​ Условного форматирования как​в Сводной таблице выделите​ Списка полей.​ курсор на границу​ Будет создан отдельный​

Детализация данных Сводной таблицы

​ таблица.​ (см. рисунок выше),​ нескольких десятках и​ в Excel и​ ВСЕ товары, ВСЕ​ вида:​ на соответствующий инструмент,​Настройка интерфейса​ таблицах в Excel​Числовое форматирование в Excel​ решения самых различных​Smith​ и к ячейкам​ любое значение поля​Другой способ – снять​ ячейки (курсор должен​ лист только со​В появившемся окне нажмем​ а не указывать​ сотнях строк;​ ее особенности.​ даты, ВСЕ суммы​В полях «Начиная с»​ который теперь уже​Как настроить Ленту в​

Обновление Сводной таблицы

​Сведение данных, фильтры, срезы​Основные сведения о листе​ задач, строить графики​North​ обычного диапазона.​ Продажи, вызовите правой​ галочку напротив удаляемого​ принять вид креста​ строками исходной таблицы​ ОК, согласившись с​ для каждого региона​необходимо провести анализ данных,​Автоматическое создание таблиц​ и магазины. Возможно,​ и «По» Excel​ расположенный напанели быстрого​ Excel 2013​ и сводные диаграммы​ Excel​ и диаграммы, работать​01/03/2016​Выделим, например, ячейки​

Удаление Сводной таблицы

​ клавишей мыши контекстное​ поля в верхней​ со стрелками).​ относящихся к Товару​ тем, что Сводная​ суммарные продажи).​ который требует выборки​ Excel.​ пользователю не нужны​ автоматически проставил начальную​ доступа.​Режим сенсорного управления Лентой​Анализ «что если” в​​Переименование, вставка и удаление​​ со сводными таблицами​2016-0009​ с 10 наибольшими​​ меню и выберите​​ части Списка полей.​

Изменение функции итогов

​Затем, нажав левую клавишу​ «Апельсины».​ таблица будет размещена​Более детальные советы по​ (фильтрации) данных, их​Автоматическое добавление строк​ некоторые элементы. Они​ и конечную даты​На первом шаге выбираем​ в Excel 2013​ Excel​
​ листа в Excel​ и многое другое.​$820​ объемами продаж. Для​ пункт Итоги по/​ Но, в этом​ мыши, перетащите ячейку​Если после создания Сводной​ на отдельном листе.​ построению таблиц изложены​ группировки (суммирования, подсчета)​ и столбцов в​

​ просто загромождают отчет​

​ диапазона данных. Определяемся​ источник данных для​Стили ссылок в Microsoft​Решаем задачи с помощью​Копирование, перемещение и изменение​Самоучитель был создан специально​Brown​ этого:​ Количество. Задача решена.​ случае поле будет​ на самую верхнюю​ таблицы в исходную​На отдельном листе появится​ в одноименной статье​

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

​ удалено сразу из​ позицию в списке​ таблицу добавлялись новые​ заготовка Сводной таблицы​ Советы по построению​ в различных разрезах​

​Стили таблиц для​ на главном. Уберем​ Для нашего примера​ Нажимаем «Далее». Чтобы​Хотите узнать об Excel​Как задать простое логическое​

Изменение формата числовых значений

​ Excel​ Excel, точнее для​…​ значения продаж;​ через меню: Работа​ всех областей Списка​ прямо под заголовок​ записи (строки), то​ и Список полей,​ таблиц.​

​ (подготовки отчетов);​ автоматического форматирования диапазонов​ ненужные элементы.​ – либо месяцы,​

Добавление новых полей

​ собрать информацию в​ еще больше?​ условие в Excel​Группировка листов в Excel​ «полных чайников». Информация​…​Выберите пункт меню Главная/​ со сводными таблицами/​ полей (если оно​ столбца.​ эти данные не​ размещенный справа от​Несколько облегчит процесс построения​этот анализ затруднительно провести​ ячеек.​Нажимаем на стрелочку у​ либо кварталы. Остановимся​ сводный отчет из​Специально для Вас​

​Используем логические функции Excel​Разметка страницы​ дается поэтапно, начиная​…​ Стили/ Условное форматирование/​ Конструктор/ Макет/ Общие​ использовалось в нескольких​После того как будет​ будут автоматически учтены​

Добавление столбцов

​ листа (отображается только​ Сводной таблицы, тот​ на основе исходной​Возможности умной таблицы.​ названия столбца, где​ на месяцах.​ нескольких листов, выбираем:​ мы припасли целых​ для задания сложных​Форматирование полей и ориентация​ с самых азов.​Сводная таблица в Excel​ Правила отбора первых​

​ итоги. Не забудьте​ областях).​

​ отпущена клавиша мыши,​

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

Удаление полей

​Получаем отчет, в котором​ «в списке или​ два простых и​ условий​ страницы в Excel​ От раздела к​ может резюмировать данные,​ и последних значений/​ предварительно выделить любую​Предположим, что необходимо подготовить​ значение Баранки будет​ Чтобы обновить Сводную​ находится в диапазоне​

​ таблица будет преобразована​ других стредств: фильтра​ помощью таблиц.​ информации.​ четко видны суммы​ базе данных Microsoft​ полезных самоучителя: 300​Функция ЕСЛИ в Excel​Вставка разрывов страниц, печать​ разделу самоучителя предлагаются​ представленные в данной​

Добавление фильтра

​ 10 первых элементов;​ ячейку Сводной таблицы.​ отчет о продажах​ перемещено на самую​ таблицу выделите любую​ ячеек Сводной таблицы).​ в формат EXCEL​ (​Абсолютные относительные адреса​

​CTRL+SHIFT+L​ ячеек в формуле.​

​ название поля. В​ Поэкспериментируем и установим​​На втором шаге определяем​​ и 30 функций​Подсчет и суммирование в​ в Excel​ и захватывающие вещи.​

​ записей или сумму​Начнем с самого распространенного​ отчет о сроках​ его нужно сделать​

​ списке.​ выберите пункт меню:​ общем виде может​ Таблица). Для этого​), формул, Расширенного фильтра;​Автоматический пересчет формул​ нашем примере –​

​ шаг – «Кварталы».​ диапазон данных, на​ Excel за 30​ Excel​Печать книг​

​ Пройдя весь курс,​ значений по любому​ вопроса: «​ сбыта. В результате​ в 2-х вариантах:​

Несколько итогов для одного поля

​Теперь добавим разделитель групп​ меню Работа со​ быть представлена так:​ сначала приведите исходную​исходная таблица удовлетворяет определенным​

Отключаем строки итогов

​ Товаров принесших прибыль,​ значений (поле Продажи).​ Данные/ Обновить. Того​ Т.к. требуется определить​ с вышеуказанными требованиями,​Пользователи часто избегают использования​ формулах.​

Группируем числа и Даты

​ остановимся на названии.​Если фамилия продавцов для​ как у нас​ командой сайта office-guru.ru​ и СЧЕТЕСЛИ​Задаем область печати в​ на практике и​ показывает общую сумму​«​ Товара сбывалось в​ другой – для​

​ таблице, диапазон обозначится​Автор: Антон Андронов​ функции СУММ и​Настройка полей и масштаба​ инструментами Excel, которые​ четырех продавцов за​помогают резюмировать большие​ до 10 дней,​

​ СУММЕСЛИ​ при печати в​ позволят решить 80%​ первый квартал 2016​ объёмы данных в​ в период 11-20​ выделите любое значение​

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

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

​ Excel​ всех Ваших задач.​ года:​

Условное форматирование ячеек Сводной таблицы

​ сравнительной таблице. Лучше​ дней и т.д.​ Сводной таблицы, нажмите​ контекстное меню и​ вызовите правой клавишей​
​ размещены названия Товаров.​ Все поля окна​ освоить любой новый​Зависимости формул и​

Что такое сводная таблица?

​ всего это объяснить​ Для этого:​​ пункт меню Работа​ выберите пункт меню​​ мыши контекстное меню​

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

​ поместить сводную таблицу.​ быстро обрабатывать большие​Вычисляем средневзвешенные значения при​Математические операторы и ссылки​Вы навсегда забудете вопрос:​ сводная таблица. В​​ на примере.​​Очистим ранее созданный отчет:​​ со сводными таблицами/​​ Числовой формат…​​ и выберите пункт​​ галочку в Списке​​ нажмите ОК.​​ требуется приложить усилия​Проверка вводимых значений.​​Жмем ОК – сводная​​ ячейке с данными​ Жмем «Готово» и​

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

​ таблиц Excel в​ зафиксированы данные: дата​ Параметры/ Действия/ Очистить/​ Группа, Продажи и​Предположим, что необходимо подготовить​

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

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

​Date​Поставьте галочку напротив поля​Переносим поле Прибыль из​ Товаров, но с​ нем нет других​ попадут в область​ к таблице;​ как создавать и​​Имена диапазонов с​ детально описаны решения​​ отчет такого вида:​ Ставим галочки –​ сформировать различные отчеты​

​ и времени в​ в Excel​ которые затем будут​ быстро извлечь данные​

​), номер счета-фактуры (​ Сбыт (срок фактической​
​ области Названия строк​
​ разбивкой по Регионам​

​ полезных данных, например​

Самоучитель по Microsoft Excel для чайников

​ Названия строк Списка​при создании таблицы к​​ применять Сводные таблицы.​ относительным адресом ссылки.​ различных задач в​Видны итоги по месяцам​ получаем:​ по одним и​ Excel​Создание сложных формул в​ годами пылиться на​ из любой части​Invoice Ref​ реализации Товара) в​ Списка полей в​ продажи. Для этого​ исходной таблицы). Второй​

Раздел 1: Основы Excel

Раздел 2: Формулы и функции

Раздел 3: Работа с данными

Раздел 4: Расширенные возможности Excel

Раздел 5: Продвинутая работа с формулами в Excel

Раздел 6: Дополнительно

​ отчеты в книге​Если это огромная сеть​​ простых примерах​Разделение листов и просмотр​Панель быстрого доступа и​При этом Excel создаст​Invoice Ref​ сколько партий Товара​ таблицы напротив поля​ меню Работа со​

​ по каждому Товару,​ Списка полей Значения.​
​Товар – наименование партии​

​в таблице должны отсутствовать​

Примеры работы со сводными таблицами в Excel

​ в Excel для​ точку на запятую​Как удалить промежуточные итоги?​ Excel, выбираем кнопку​ магазинов и продажи​Полезно знать​ книги Excel в​ режимы просмотра книги​ новую таблицу (как​Amount​

​ сбывалось за 5,​ Прибыль появился значок​ сводными таблицами/ Параметры/​ мы не заботились​Несколькими кликами мыши (точнее​ товара, например, «Апельсины»;​ полностью незаполненные строки​ анализа графика.​

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

​ в Excel.​ Просто на вкладке​

​ «Обновить все» (или​ идут, то в​Статистические функции Excel, которые​

​ Активное поле/ Свернуть​ о функции итогов​ шестью) мы создали​Группа – группа товара,​ и столбцы;​Построение графиков в​​ макет выбираем «Не​ комбинацию клавиш CTRL+ALT+F5).​ течение одного квартала​ необходимо знать​Сортировка данных в Excel​ книг​

​ перечислены все продажи​Region​ дней. Всего 66​ можно сняв галочку​ все поле, можно​ – все Продажи,​

​ свернуть Сводную таблицу,​ относящиеся к одному​ по каждому Товару.​ в группу «Фрукты»;​

​ сводными таблицами» (необходимо​ будет очень сложно.​

Как обновить данные в сводной таблице Excel?

​Уровни сортировки в Excel​Сохранение книг и общий​

​Фильтрация данных в Excel​ доступ​

​ сразу все цены​ «чужих» таблиц, периодически​ выбираем меню «Параметры».​ отчета уйдет не​ формулах Excel​

Некоторые секреты форматирования

​Фильтр в Excel —​Сохранение и автовосстановление книг​ не говорим о​01/01/2016​ Названия строк;​ фильтры.​ привело к тому,​ то нужно изменить​ уникальных значений с​

​ поставки Товара Поставщиком;​ ячейку вводить адрес​

​ такой ситуации сводная​ Excel​Расширенный фильтр в Excel​Экспорт книг Excel​ созданы показанные выше​$456​ сводными таблицами/ Параметры/​

​ данных доступен через​ развернулась на 144​ этого в Сводной​ столбцу).​ в котором была​

​ Название улицы, дом​Уравнения и задачи​ редактирование шаблона таблиц.​ откуда взялась сумма​В разделе «Данные» устанавливаем​ таблица просто необходима.​Знакомство с именами ячеек​Работа с группами и​

Работа с итогами

​Общий доступ к книгам​ сводные таблицы. Главная​

​Brown​ Группировать выберите пункт​ выпадающий список в​ строки. Это не​ таблице выделите любое​

​Если требуется, например,​ реализована партия Товара;​

​ Excel​ цель первой части​South​

​ Группировка по полю;​ заголовках строк и​ всегда удобно. Т.к.​ значение поля Продажи,​

​ определить объемы продаж​Продажи – Стоимость, по​

Детализация информации

​ 3 одноименных столбца,​3 примера использования​ изменения адресов относительных​ Excel. Но можно​ «Обновить при открытии​ мастера сводных таблиц.​ Excel​Группы и промежуточные итоги​Основы работы с ячейками​ самоучителя – ответить​01/01/2016​

​Как присвоить имя ячейке​ в Excel​Ячейка в Excel —​ на вопрос: «​2016-0003​

​ показано на рисунке​Предположим, что требуется подсчитать​ в 6 регионах,​ мыши контекстное меню​ то для этого​ партию Товара;​

​ будет работать неэффективно​Надстройка поиск решения​Простые расчеты и​ разбить сводную таблицу​Теперь каждый раз при​ Excel он почему-то​ или диапазону в​Таблицы в Excel​ базовые понятия​Что же такое сводная​

​ таблица в Excel?​Jones​

Самоучитель Excel с примерами для пользователей среднего уровня

​Нажмите ОК.​ и суммарные продажи​ продажи имеет смысл​ Итоги по/ Количество.​ Списке полей у​ реализации Товара в​ Вам нужна информация,​ параметров.​ формул.​В марте продано двуспальных​ измененными данными будет​ настройках:​5 полезных правил и​ таблиц в Excel​Копирование, перемещение и удаление​«. В следующих частях​South​Теперь Сводная таблица показывает​