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

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

В этом курсе:

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

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

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

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

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

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

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

В поле Таблица или диапазон проверьте диапазон ячеек.

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

Нажмите кнопку ОК.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Если вы используете Excel для Mac 2011 или более ранней версии, кнопка «Сводная таблица» находится на вкладке Данные в группе Анализ.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Выполните одно из следующих действий:

Чтобы создать сводную таблицу вручную, выберите создать собственную сводную таблицу.

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

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

Изменение исходных данных и конечной ячейки

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

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

Выполните одно из следующих действий:

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

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

Примечание: Вы также можете выбрать пустой лист, на котором должна отображаться Сводная таблица (назначение). В этом случае, если при выборе элемента «вставить > сводной таблицы» выбрать недопустимый источник, появится область Вставка сводной таблицы, в которой будет предложено указать источник.

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

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

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

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

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

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

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

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

диалоговое окно «Дополнительные вычисления»» />

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

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

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

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

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

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.

Источник:
http://support.microsoft.com/ru-ru/office/%D1%81%D0%BE%D0%B7%D0%B4%D0%B0%D0%BD%D0%B8%D0%B5-%D1%81%D0%B2%D0%BE%D0%B4%D0%BD%D0%BE%D0%B9-%D1%82%D0%B0%D0%B1%D0%BB%D0%B8%D1%86%D1%8B-%D0%B4%D0%BB%D1%8F-%D0%B0%D0%BD%D0%B0%D0%BB%D0%B8%D0%B7%D0%B0-%D0%B4%D0%B0%D0%BD%D0%BD%D1%8B%D1%85-%D0%BB%D0%B8%D1%81%D1%82%D0%B0-a9a84538-bfe9-40a9-a8e9-f99134456576

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

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

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

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

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

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

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

Читайте также  ВПР (функция ВПР) - Служба поддержки Office

— Можно ли отчет сделать не по выручке, а по прибыли?

— Можно ли товары показать по строкам, а регионы по столбцам?

— Можно ли такие таблицы делать для каждого менеджера в отдельности?

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

Рассмотрим, как создать сводную таблицу в Excel.

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

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

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

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

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

Макет таблицы настраивается в панели Поля сводной таблицы, которая находится в правой части листа.

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

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

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

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

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

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

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

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

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

Посмотрим, как это работает в действии. Создадим пока такую же таблицу, как уже была создана с помощью функции СУММЕСЛИМН. Для этого перетащим в область Значения поле «Выручка», в область Строки перетащим поле «Область» (регион продаж), в Столбцы – «Товар».

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

На ее построение потребовалось буквально 5-10 секунд.

Работа со сводными таблицами в Excel

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

Заменим выручку на прибыль.

Товары и области меняются местами также перетягиванием мыши.

Для фильтрации сводных таблиц есть несколько инструментов. В данном случае просто поместим поле «Менеджер» в область фильтров.

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

Источник данных сводной таблицы Excel

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

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

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

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

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

В целом требований немного, но их следует знать.

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

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

или
через команду во вкладке Данные – Обновить все.

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

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

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

Источник:
http://statanaliz.info/excel/svodnye-tablitsy/kak-v-excel-sdelat-svodnuyu-tablitsu/

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

Здравствуй уважаемый, читатель!

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

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

Возникает закономерный вопрос, где же применение сводной таблицы даст наибольший эффект:

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

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

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

  1. Каждый без исключения столбец обязан иметь собственный заголовок шапки;
  2. Все строки и столбики вы обязаны заполнить, пробелы должны отсутствовать.
  3. Для всех столбцов данных, должены быть определенные форматы ячеек, для тех данных, которые должны в них хранятся (пример, для поля “Дата” нужен формат календарной даты, а для поля “Контрагент” — формат текста и т.п.)
  4. Значения в этих ячейках должны быть “единоличным”, это значит такими которые не делятся (к примеру, “Договор №23 от 03.09.2016 года” должен быть записан в 3 разных столбцах “Документ”, “Номер” и “Дата”, это позволит создавать гибкую и удобную систему). Также это возможно при помощи функции СЦЕПИТЬ.
  5. Если вы ведете расходно-доходную табличку в которой кроме суммирования еще есть надобность отнять, прибавить и прочее, то и в базу первоначальных данных, в случаях с отрицательными значениями, вводите данные которые уже изначально со знаком “-” и тогда в свёрнутом виде вы получите нужный вам результат;
  6. Сама конструкция вашей сводной таблицы обязана иметь оптимальный вид.

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

Как создается сводная таблица в Excel

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

На панели управления выбираем вкладку «Вставка» и получаем на выбор 2 варианта создания вашей сводной таблицы:

  1. Рекомендуемые сводные таблицы (этот пункт рекомендуется использовать начинающим, но не бойтесь, это ненадолго, уловите суть создания, попрактикуетесь и всё, будете работать по второму пункту).
  2. Сводная таблица (используется при ручной настройке таблицы в основном используется опытными пользователями)

1. Рекомендуемые сводные таблицы.

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

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

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

2. Сводная таблица (мастер сводных таблиц)

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

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

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

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

Ну вот форма то та, конечно, но вот результат не тот, а именно поле «Вес, кг» собирает по критерию — количество значений, а нам надо суммировать, а значит подводим курсор мыши к области значений «ЗНАЧЕНИЕ» и на указаном поле «Количество по полю Вес, кг», нажимаем левую кнопку мыши вызывая контекстное меню. Нам нужно выбрать последний пункт «Параметр полей значений».

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

Читайте также  Как в excel сделать разделитель разрядов пробелом?

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

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

Ну что же сводная таблица с выборкой фруктов у нас сделана. Но что же делать если нам нужно и интересно знать, а как же всё-таки происходит движение по странам. Да и любому будет интересно получить данные из сводной таблицы под разными углами, а поскольку мы уже отформатировали таблицу и всё сделали для идеальной работы. Мы просто копируем нашу табличку и в поле необходимой области «СТРОКИ» меняем вычисляемые значения местами. Указываем первым вычисляемым значением «Страна», вот и всё с 1 исходной таблицы данных мы получили 2 сводные таблицы нужных нам данных.

Еще стоить поговорить о том, что при манипуляциях со сводными таблицами, Excel дополнительно формирует новое меню в панеле управления для работы с данными таблиц:

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

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

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

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

Пример можно взять здесь.

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

Не забудьте поблагодарить автора!

Золото убило больше душ, чем железо – тел.
В. Скотт

Источник:
http://topexcel.ru/kak-sozdat-svodnuyu-tablicu-v-excel/

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

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

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

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

1

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

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

2

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

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

  1. При составлении отчета использовать не выручку, а прибыль.
  1. Показать по колонкам – регионы, а по рядам – товары.
  2. Делать аналогичные отчеты для каждого менеджера по отдельности.

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

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

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

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

3

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

4

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

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

5

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

6

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

7

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

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

Составные компоненты

В состав сводной таблицы входят такие компоненты:

  1. Область значений. Под ней подразумевается главная часть таблицы со значениями. Excel их получает с помощью агрегирования исходных данных тем методом, который выберет пользователь. Как правило, это делается с помощью Суммирования. Этот метод установлен автоматически при условии, что все данные в исходном диапазоне находятся в ячейке, имеющей числовой формат. Если же хотя бы в одной ячейке есть текст или отсутствует какая-либо информация, то автоматически будет осуществляться подсчет количества ячеек. Всего есть около 20 различных видов вычислений. наиболее просто изменить его с помощью правого клика по какой-угодно ячейке необходимого поля сводной таблицы и осуществить выбор метода агрегирования.
  2. Область строк. Собственно, сюда входят названия строк, находящихся в крайнем левом столбце. Это все значения столбца, уникальные в своем роде и которые были выбраны пользователем. Здесь может быть несколько полей. Такая таблица называется многоуровневой. Как правило, здесь находятся любые не количественные данные, такие как названия товаров, регионов и так далее.
  3. Область столбцов. То же самое, что и область строк, только касается столбцов. Сюда могут входить годы, месяцы, а также группы выпускаемой продукции.
  4. Область фильтра. Применяется для того, чтобы показывать лишь определенные значения, соответствующие конкретному признаку. Например, есть возможность отображения данных лишь по конкретной отрасли, за определенный период и так далее. В этом случае необходимо поместить поле фильтрации в область фильтра и выбрать необходимое значение в списке, который раскрывается.

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

8

После этого перед нами появится полноценная сводная таблица.

9

И что удивительно? Чтобы ее создать, потребовалось всего 10 секунд.

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

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

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

10

С помощью простого перетаскивания можно поменять местами товары и области.

11

В нашем случае мы воспользовались областью фильтров, куда поместили поле «Менеджер».

12

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

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

Полезные советы по подбору источника данных

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

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

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

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

13

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

14

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

Как создать дашборд в Excel

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

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

Как правило, в компаниях созданием умных таблиц и ограничиваются, в то время как дашборды имеют огромное количество преимуществ:

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

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

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

  1. Фигуры и объекты Word Art. Они позволяют рисовать все, что угодно, вплоть до инженерных чертежей. Кроме этого, есть множество текстовых меток, которые позволяют описать любую составную часть дашборда.
  2. Использование сводных таблиц.
  3. Графики, которые могут в качестве данных также использовать исходный диапазон.

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

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

Источник:
http://office-guru.ru/excel/tablicy-svodnye-tablicy/kak-rabotat-so-svodnymi-tablicami-v-excel-s-primerami.html

Как сделать сводную таблицу в excel. Что это такое?

Статья посвящена тому, как сделать сводную таблицу в Excel. Но для начала краткое объяснение.

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

Сводная (перекрёстная) таблица – удобный инструмент компактного отображения объёмных анализируемых данных. Название произошло от возможности интерактивного изменения (сведения) областей структуры такой таблицы. В результате создаётся возможность по-разному отображать неизменные анализируемые данные.

Структура сводной таблицы содержит четыре области.

  • Строки. В этой располагаемой слева области обычно размещаются данные, подлежащие группировке.
  • Столбцы (колонки). В этой верхней области находятся заголовки.
  • Значения. Область в центре таблицы содержит данные, подлежащие расчёту.
  • Фильтры (необязательная). Посредством этой верхней области из общего массива данных можно вычленять нужные.

Приведённые определения звучат довольно абстрактно. Они наполнятся конкретикой в процессе создания и редактирования сводной таблицы.

Как сделать сводную таблицу в Excel — пошаговая инструкция.

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

1. Щёлкаем любую ячейку с данными на листе «Данные», например, С2.

2. Переходим на вкладку «Вставка» и щёлкаем инструмент «Сводная таблица».

3. В появившемся окне «Создание сводной таблицы»:

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

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

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

4. Для создания на макете реальной сводной таблицы в её областях – «Названия строк», «Названия полей», «Значения» и «Фильтр», должны появиться поля. При этом необходимо руководствоваться следующими соображениями:

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

• Поля, по которым должна производиться группировка выбранных числовых полей, должны находиться в области «Строки». Они появятся там автоматически при установке галочки рядом с названиями текстовых полей. Активируем таковые – «Федеральный округ» и «Регион».

5. А теперь посмотрим результат произведённых действий на листе сводной таблицы. Ведь её простейший вариант уже создан! Цветными прямоугольниками на ней выделены:

  • жёлтым – область строк;
  • голубым – область значений;
  • зелёным – область столбцов.

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

Мы узнали, как сделать сводную таблицу в эксель (простую). Но источник данных может быть распределён по нескольким таблицам. Сделать сводную таблицу в excel можно и из нескольких таблиц.

Источник:
http://www.teryra.com/kak-sdelat-svodnuyu-tablicu-v-excel/

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. Кликаем по текущему значения фильтра “Пол”, выбираем из списка “женский” и нажимаем кнопку OK.
  2. Аналогичным образом настраиваем фильтр по “Виду спорта”, оставив только “теннис”.
  3. Таким образом, мы получили финальную таблицу с нужно нам информацией.

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

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

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

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

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

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

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

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

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

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

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

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

  1. Открываем меню Файл, кликаем по строке “Параметры”, далее – “Панель быстрого доступа”. Выбрав “Команды не на ленте” в предлагаемом перечне нам нужен пункт “Мастер сводных таблиц и диаграмм”. Отмечаем его курсором, нажимаем “Добавить >>” и завершаем настройки кликом по кнопке OK.
  2. В самом верхнем левом углу окна программы появится значок, нажав на который, запускаем Мастер сводных таблиц.
  3. В открывшемся окне необходимо выбрать источник данных, и на выбор может предлагаться до четырех опций. В нашем случае останавливаемся на первом варианте, т.е. создаем таблицу из списка или базы данных Excel. В нижней части окна выбираем пункт “сводная таблица” и нажимаем “Далее”.
  4. Появится следующее окно, где нужно указать координаты исходной таблицы, из которой будет сформирована сводная таблица. Если мы согласны с диапазоном, присвоенным программой автоматически, кликаем по кнопке “Далее”, либо сначала выделяем нужную область и затем уже двигаемся дальше.
  5. Аналогично ранее рассмотренному примеру выбираем место для размещения сводной таблицы и кликаем “Готово”. На выбор предлагаются две опции.
    • на новом листе
    • на существующем листе (нужно выбрать конкретный лист).
  6. Будет создана уже знакомая нам форма для конструирования сводной таблицы. Далее приступаем к ее настройке согласно нашим пожеланиям и задачам.

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

  1. Если в исходной таблице данные не совсем корректны, они в таком же виде перенесутся и в сводную. То есть, если, например, в исходной таблицы в одной строке написано “Кроссовки беговые, размер 35″, а в другой “Кросовки беговые, размер 35″, то в сводной таблице оба этих варианта будут отображены как две отдельные строки, и никакой консолидации данных по ним не будет.
  2. Если первоначальная таблица не была преобразована в “умную” на первоначальном этапе, и сводная таблица создана из простой, необходимо проверить, чтобы все заголовки в шапке таблицы были заполнены. В противном случае, программа может выдать ошибку.
  3. Если сводная таблица построена на базе простой (не “умной”), то после любых изменений исходной таблицы нужно обновлять данные сводной с помощью кнопки “Обновить все” во вкладке “Данные”. Однако, при этом нужно учитывать, что обновление не удастся сделать, если были добавлены строки или столбцы за пределами ранее указанных границ.

Заключение

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

Источник:
http://microexcel.ru/svodnye-tabliczy/