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

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

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

Возьмем следующие исходные данные. Это таблица продаж.

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

В таблице видно кто, что, когда и на какую сумму продал.

Чтобы узнать общую сумму продаж на каждому продавцу придется взять калькулятор и посчитать. Или можно посчитать с помощью формулы excel. Для Иванова это будет следующая формула: =D2+D5+D8+D11. Так и ошибиться не долго!

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

  1. Щелкните курсором на ячейку A1, чтобы excel понял с какими данными мы собираемся работать.
  2. Выберите в меню Данные — Сводная таблица… Далее появится серия диалогов, для настройки сводной таблицы.
  3. В первом диалоге нужно указать параметры сводной таблицы. Ничего не меняйте в нем и нажмите Далее.
  4. Второй диалог попросит выделить данные для сводной таблицы. Excel сам выделил всю нашу таблицу (т.к. у нас фокус стоит на ячейке A1), ничего не меняйте здесь и нажмите Далее.
  5. В третьем диалоге нужно указать где будем создавать сводную таблицу. Выберите флаг Существующий лист, затем щелкните ячейку F1 и нажмите готово.

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

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

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

Из диалога Список полей сводной таблицы перетащите поле Фамилия в область Перетащите сюда поля строк. Форма сводной таблицы немного изменилась.

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

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

Теперь перетащите поле Сумма в область данных.

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

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

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

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

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

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

Отчет получился слишком подробным. Сгруппируем его по месяцам.

  1. Щелкните правой кнопкой по полю отчета Дата, в появившемся меню выберите Группа и структура, затем Группировать…
  2. В следующем диалоге Excel просит нас указать как именно группировать и сразу предлагает по месяцам (выделено). Ничего не меняем и нажимаем ОК.

Теперь дата продажи в отчете сгруппирована по месяцам.

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

Изменение сводного отчета Excel

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

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

Получили отчет по продажам продуктов по месяцам.

А вот что получится, если мы заменим поле Дата на Фамилия.

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

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

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

Резюме

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

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

Источник: https://www.supasoft.ru/docs/prototype-8/lesson-1

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

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

Люди, которые хоть раз искали работу через интернет и не только, наверняка часто натыкались на вакансии, где основным требованием к соискателю являлось уверенное знание пакета программ Microsoft Office, и в частности программы Microsoft Excel.

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

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

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

Пошаговая инструкция по созданию сводных таблиц с формулами в программе Microsoft Excel

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

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

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

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

Рисунок 1. Что такое сводные таблицы в Excel и для чего они нужны?

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

Что такое формулы в Excel и для чего они нужны?

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

Для формул в Microsoft Excel применяются стандартные математические операторы:

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

Рисунок 1. Что такое формулы в Excel и для чего они нужны?

  • Во время умножения всегда необходимо использовать знак «*». Его упущение, как в письменном варианте вычислений, недопустимо. Иными словами, если Вы запишите (4+7)8, то данную запись программа не сможет распознать, так как после круглой скобки не стоит знак умножения «*».
  • Программа Excel многими используется вместо калькулятора. Например, если Вы введёте в строку формул 2+3, то в выбранной ячейке отобразится результат 5.

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

Рисунок 2. Что такое формулы в Excel и для чего они нужны?

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

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

Рисунок 3. Что такое формулы в Excel и для чего они нужны?

Создание базы данных для внесения её в сводную таблицу Excel 2003, 2007, 2010

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

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

Шаг 1.

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

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

Рисунок 1. Создание базы данных для внесения её в сводную таблицу Excel 2003, 2007, 2010

Шаг 2.

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

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

Рисунок 2. Создание базы данных для внесения её в сводную таблицу Excel 2003, 2007, 2010

Шаг 3.

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

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

Рисунок 3. Создание базы данных для внесения её в сводную таблицу Excel 2003, 2007, 2010

Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?

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

Шаг 1.

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

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

Рисунок 1. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?

Шаг 2.

  • Перед Вами откроется окошко, в котором потребуется выбрать диапазон или ввести название таблицы.
  • Также в окне есть возможность выбора места для отчета. Создадим его на новом листе.
  • Пометьте точками пункты, которые указаны на скриншоте ниже и нажмите кнопку «ОК».
Читайте также:  Как сделать время в excel?

Рисунок 2. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?

Шаг 3.

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

Рисунок 3. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?

Шаг 4.

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

Рисунок 4. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?

Шаг 5.

  • Далее по той же схеме в колонке справа необходимо зажать левую кнопку мышки на заголовке «Товары» и перетащить его в область «Названия строк».

Рисунок 5. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?

Шаг 6.

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

Рисунок 6. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?

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

Рисунок 7. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?

  • Далее Ваша таблица преобразуется таким вот образом.

Рисунок 8. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?

Шаг 7.

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

Рисунок 9. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?

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

Рисунок 10. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?

  • В открывшемся окошке в разделе «Условные форматы» выберите пункт «Числовой» и по желанию можете отметить галочкой пункт «Разделитель групп разрядов».
  • Далее нажмите «ОК». Как можно заметить, простые числа изменились и теперь отображаются не просто 650, а 650,00.

Рисунок 11. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?

Шаг 8.

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

Рисунок 12. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?

  • Точно таким же способом можно фильтровать строки и столбцы. К примеру, наименования товаров и даты.
  • Отметив галочками поля «Брюки» и «Костюм», легко выяснить, сколько с их продажи получилось выручки в целом, или конкретными продавцами.

Рисунок 13. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?

  • Также в графе «Значения» можно задать определённые параметры заголовку. На данный момент там установлен параметр «Сумма».
  • То есть, если посмотреть на таблицу, можно увидеть, что продавцом Ромой в феврале было продано рубашек на общую сумму 1 800,00 рублей.
  • Для того, чтобы узнать их количество, необходимо щёлкнуть мышкой по пункту «Сумма» и выбрать из меню строчку «Параметры полей значений».

Рисунок 14. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?

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

Рисунок 15. Как сделать сводную таблицу в Excel 2003, 2007, 2010 с формулами?

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

Для того, чтобы её лучше понять, советуем самостоятельно посидеть и поэкспериментировать с изменениями значений. Если что Вам будет непонятно, обращайтесь к встроенной справке Microsoft Excel. Она в нём исчерпывающая.

ВИДЕО: Создание сводной таблицы в Excel

Источник: https://mobilkoy.ru/kak-sdelat-svodnuyu-tablicu-v-excel-2003-2007-2010-s-formulami-poshagovaya-instrukciya

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

Рубрика Excel

Также статьи о работе с таблицами в Экселе:

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

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

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

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

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

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

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

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

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

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

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

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

  • Как быстро сделать сводную таблицу в excel?
  • Как быстро сделать сводную таблицу в excel?
  • Осталась у нас область «Значения», в которую мы перенесем поле «Масса».
  • Как быстро сделать сводную таблицу в excel?

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

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

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

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

Источник: http://officeprogs.ru/excel/kak-delaetsya-svodnaya-tablitsa-v-excel.html

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

Сводная таблица MS Excel — это мощный инструмент анализа данных.

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

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

Читайте также:  Как из формулы сделать текст в excel?

Требования к исходным данным.

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

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

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

  • В каждом столбце содержатся только однотипные данные, в столбце А только Даты, в столбце В только Документы, в столбце С только Клиенты, Деньги в Деньгах, Поставщики в Поставщиках, Категории Товаров в Категориях и так далее… В такой таблице мы можем очень легко использовать Фильтр;
  • Столбцы с однотипными данными не повторяются;
  • В таблице нет никаких итоговых строк, только «чистые данные»;
  • В таблице нет пустых ячеек в текстовых данных, в каждой строчке есть название и Клиента, и Товара, и Поставщика, и Менеджера и так далее…

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

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

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

В чем ее «неправильность»? Правильно, в одном столбце и Клиенты, и Товарные категории, и Наименование товара… Чтобы из такой таблицы построить Сводную, нужно еще помучиться, а именно растащить по разным столбцам разнотипные данные… Как это сделать читайте в статье «Как быстро построить сводную таблицу из отчета 1C или SAP?».

Вот еще один пример «Неправильного массива»:

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

Тут целый букет «неправильностей»:

  • Во-первых, столбцы «Поставщик» и «Категория» имеют пустые ячейки, соответственно мы не можем воспользоваться фильтром;
  • Во-вторых, практически в каждом столбце есть строки «Итого…», они совершенно не нужны для построения Сводной таблицы, более того они будут только мешать;
  • В-третьих, столбец «Общий итог» так же не нужен;
  • В-четвертых, однотипные данные, а именно «Деньги», стоят аж в трех столбцах: «янв», «фев» и «мар», что значительно затруднит построение Сводной таблицы, а значит, нам придется с ними, что-то делать…

Но это все для отдельного разговора, если вы хотите научиться быстро, преобразовывать такие «кривые таблицы» в «правильные массивы» читайте статьи: «Как быстро построить сводную таблицу из отчета 1C или SAP?» и «Как быстро преобразовать таблицу в массив для сводной таблицы?»

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

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

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

В открывшемся диалоговом окне «Создание сводной таблицы» нажимаем «ОК»:

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

MS Excel создаст новый лист, на котором обозначит место вставки Сводной таблицы, а справа выведет окно настройки полей Сводной таблицы, в котором вы увидите все названия столбцов своего массива:

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

Начинаем настройку Сводной таблицы. Какой разрез мы хотим получить? Товарные категории в разрезе Менеджеров — пожалуйста. Наводим мышку на поле «Категория», нажимаем на нем левой кнопкой мышки и тащим его в поле «СТРОКИ». Поле «Менеджер» тащим в «КОЛОННЫ», «Сумму» тащим в «ЗНАЧЕНИЯ»:

Источник: https://e-xcel.ru/index.php/svodnye-tablitsy/kak-postroit-svodnuyu-tablitsu

Глава 7. Сводные таблицы

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

Скачайте файл svodnie-tablici. На листе данные этого файла находятся  двести записей о продажах товаров (на практике число анализируемых записей обычно на один-два порядка больше). Каждая запись представляет собой строчку в таблице и содержит информацию:

  • Дата совершения продажи;
  • Наименование товара;
  • Наименование покупателя товара;
  • Сумма сделки.

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

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

На все эти вопросы помогают ответить сводные таблицы.

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

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

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

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

  • Как быстро сделать сводную таблицу в excel?
  • Поля сводной таблицы
  • На новом листе слева появилась заготовка сводной таблицы, справа окно со списком полей и четырьмя окошками: фильтр отчёта, названия столбцов, названия строк, суммарные значения.

Как быстро сделать сводную таблицу в excel?Напомним, сводная таблица должна давать ответы на поставленные вопросы. Например, ответим на три первых вопроса: о сумме продаж, о самом активном покупателе и самом популярном товаре. Для этого нужно отметить в окне справа поля Наименование товара, Покупатель, Сумма. Программа разместит поле Сумма в окошко Суммарные значения (в самом низу справа), а остальные два поля – в окошко Названия строк. Перетащите одно из полей в окошко Названия столбцов. Получится примерно так:

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

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

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

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

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

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

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

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

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

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

  1. Как быстро сделать сводную таблицу в excel?
  2. Скачать пример сводных таблиц Excel: svodnie-tablici
  3. Практический пример со сводными таблицами: Экспорт данных из 1С в Excel на примере отчёта о движении денежных средств
  4. Предыдущая глава: Глава 6. Основные функции Excel
  5. Содержание учебника: Содержание

Источник: http://finexcel.ru/glavnaia/uchebnik-excel/svodnye-tablicy/

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

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

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

Сводные таблицы в Эксель

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

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

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

Как добавить новую строку в таблице в программе Excel Объединить таблицы таблицами в ворде Как сделать диаграмму в программе Microsoft Excel Как подписать оси диаграммы в Excel

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

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

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

  8. В развернутом меню выберите один из двух предложенных вариантов. В рассматриваемом случае нужно указать пункт «Сводная таблица».
  9. Далее перед вами отобразится маленькое окошко, в котором вам предложат выбрать имя и диапазон таблицы. Если вас устраивает уже указанное значение, ничего менять не нужно.
  10. Внизу укажите, куда следует поместить таблицу, на новый или существующий лист. Далее просто нажмите на кнопку «Ок».
  11. На листе откроется форма создания общей таблицы. Справа от рабочего поля программы находится список полей, а под ним расположены такие области: фильтр, значение наименования строк и столбцов.
  12. Выберите необходимые ячейки и перетащите их в соответствующие области. Здесь нет четкого алгоритма, все зависит от расположения информации в источнике.
  13. Конкретно для этого примера: пол и дата должны находиться в области «Фильтр», категория – «Столбцы», имя – «Строки», сумма ЗП – «Значение».
  14. Как быстро сделать сводную таблицу в excel?

Читайте также:  Как сделать невидимым лист в excel для других?

Параметры таблицы

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

  1. Нажмите на кнопку, которая находится возле пункта «Дата».
  2. В отобразившемся окне поставьте флажок возле значения «Выделить несколько…».
  3. Уберите флажки, которые расположены напротив всех дат, не относящихся к третьему кварталу, и кликните на клавишу «Ок».
  4. Как быстро сделать сводную таблицу в excel?

  5. По такому же принципу можно выбрать определенный пол для отчета.
  6. Как быстро сделать сводную таблицу в excel?

  7. При необходимости вы можете поменять поля местами. Для этого в разделе «Параметры» необходимо перейти в блок «Список полей», и просто переставить их между собой.
  8. После выполнения этих действий у вас появится деление по месяцам и возможность сортировать информацию по категории сотрудников.
  9. При необходимости можно выставить дату выше имени, в таком случае числа выплат будут подразделяться на ФИО персонала.
  10. Если вам необходимо превратить числа в гистограмму, выделите ячейку, в которой находится сумма, и откройте раздел «Главная».
  11. После этого необходимо кликнуть на надпись «Условное форматирование» и, развернув строку «Гистограммы», выбрать понравившуюся заливку.
  12. Получилось, что правило форматирования отобразилось только для одной ячейки. Если вы хотите применить его ко всем полям, разверните появившийся значок рядом с числом и в контекстном меню установите флажок возле строки «Ко всем ячейкам…».

Встроенный инструмент системы

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

  1. Перейдите во вкладку «Файл» и в появившемся списке выберите строку «Параметры».
  2. Перед вами появится новая вкладка, в которой слева необходимо отметить пункт «Панель быстрого доступа».
  3. Из левого столбца в перечне выберите «Мастер сводных таблиц и диаграмм», и нажмите на пункт «Добавить». Для окончания переноса кликните на клавишу «Ок».

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

  1. В «Шаге 1» определите, где находятся данные, которые будут применяться во время формирования таблицы, и определите вид отчета, после чего кликните на пункт «Далее».
  2. На втором этапе можно указать диапазон исходных данных, но в описанном в статье примере эти действия предпринимать нет необходимости, поэтому просто нажмите «Далее».
  3. В последнем шаге необходимо выбрать место расположения создаваемой таблицы, затем подтвердите свои действия кнопкой «Готово».
  4. После выполнения этих шагов откроется точно такой же документ, который был описан в предыдущем способе.
  5. Для заполнения суммарной таблицы выполните такие же действия, которые описывались в стандартном способе создания таблицы.

Заключение

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

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

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

Источник: https://public-pc.com/delaem-svodnuyu-tablitsu-v-excel/

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

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

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

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

  • Как создать
  • Как расставить поля
  • Как работать с данными

Как создать

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

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

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

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

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

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

Как расставить поля

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

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

В качестве строк выберем «Товары». Аналогичным образом перетягиваем нужное поле в область «Названия строк».

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

Если бы у нас в исходной таблице был столбец «Единицы». То данная сводная таблица показывала бы, какой продавец, в каком месяце, сколько продал единиц товара по определенной цене.

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

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

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

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

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

Как работать с данными

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

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

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

В следующем окне из списка выбираем «Количество», выберите то, что подходит конкретно в вашем случае, и нажимаем «ОК».

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

Теперь сделаем, чтобы общий фильтр для таблицы был по месяцам. Меняем области: в «Фильтр отчета» перетаскиваем поле «Дата», в «Названия столбцов» – «Продавец».

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

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

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

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

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

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

(1

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

Ссылка на основную публикацию
Adblock
detector