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

Формулы в сводной таблице. Как сделать?

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

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

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

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

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

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

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

Выбираем Параметры — раздел Сервис — Формулы

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

Ок. Готово.

Ссылка на сводную таблицу

Если вам все-таки нужно сослаться на сводную таблицу, то ссылка в большинстве случае будет иметь вид = ПОЛУЧИТЬ.ДАННЫЕ(…

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

Если есть вопросы, пишите нам в комментарии!

Поделитесь нашей статьей в ваших соцсетях:

Рубрика Excel

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

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

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

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

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

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

image

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

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

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

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

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

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

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

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

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

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

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

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

Блог > Microsoft Office > Windows > Полезные советы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Шаг 1.

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

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

Шаг 2.

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

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

Шаг 3.

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

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

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

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

Шаг 1.

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

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

Шаг 2.

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

Рисунок 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

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

Microsoft Excel В мир цифровых технологий не обойтись без уникального инструмента — программы Эксель (Microsoft Excel). Она имеет безграничные возможности, нужно только научиться ими пользоваться. В данной статье рассмотрим такой навык, как составление сводной таблицы. Это сведение в одну простую таблицу консолидированной информации из исходника. В Эксель 2007 и выше уже есть готовая кнопка «Вставка — Сводные таблицы», но как ей воспользоваться правильно? Рассмотрим на примере Эксель 2010. 1. Открываем на рабочем столе иконку с названием программы Microsoft Excel 2010. Перед нами открывается рабочая область «Книга 1» Книга 1 2. Заполняем страницу массивом информации, которую потом будем консолидировать по заданным параметрам. Либо открываем сразу данную страницу, если она готова. заполнение данных 3. На этой же странице, либо на следующей выбираем ячейку с которой начнется сводная таблица. Далее в меню выбираем «Вставка» — «Сводная таблица» — Вставить сводную таблицу. (зеленый маркер) вставка сводной таблицы 4. Открывается окно для того, чтобы можно было задать необходимые параметры. параметры сводной таблицы 5. В строке «Выбрать таблицу или диапазон» нажимаем кнопку, изображающую таблицу справа и выбираем курсором на странице нашу заполненную таблицу целиком. выбор диапозона диапазон таблиц 6. Далее еще раз нажимаем на туже кнопку справа, ниже выбираем на новый лист формировать сводную таблицу или этот же. Выбираем по желанию и нажимаем «Ок» и перед нами появляется вот такого вида страница: вид страницы 7. Далее, путем выбора галочек в верхней табличке формируем искомые данные. форматирование данных Если поставить все галочки, Эксель сделает общий срез с использованием всех данных исходной таблицы. Если ставить отдельные галочки, то и сводная информация будет появляться только по определенным параметрам. Таким образом, можно «поиграть» значениями итоговой таблицы. значение итоговой таблицы значения таблицы эксель 8. В нижних табличках можно настроить необходимый вид сводной таблицы. Поменять значение и количество местами, изменить параметры. Теперь Вы научились в одно мгновение сводить значения большой таблицы в одну аккуратную, только с необходимыми результатами. Успехов в освоении мира Excel!

Содержание статьи:

  1. Создание сводной таблицы;
  2. Управление списком полей таблицы;
  3. Вычисляемые поля;
  4. Параметры.

image

В появившемся окне необходимо выбрать источник данных, который представляет:

  1. Диапазон (может находиться в другой книге);
  2. Таблица данных (указывается ее имя);
  3. Данные из внешнего источника, полученные по SQL-запросу из базы данных и т.п.

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

image

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

image

  • Названия строк – отображает значения поля в виде названия строк. По сути данная область ничего не меняет, если назначить ей все имеющиеся поля, то получим исходную таблицу;
  • Названия столбцов – значения полей отображаются в виде названий столбцов. Действие напоминает обычное транспонирование;
  • Значения – со значениями столбцов, помещенными в эту область, производятся вычисления. При этом одно поле может быть назначено ей несколько раз;
  • Фильтр отчета – сюда помещаются поля, значения которых служат для фильтрации строк исходных данных, которые сводная таблица должна проигнорировать.
Месяц Дата Кол-во Курс Изменение
Январь 10.01.2013 1 30,4215 0,0488
Январь 11.01.2013 1 30,3650 -0,0565
Январь 12.01.2013 1 30,2537 -0,1113
Сентябрь 28.09.2013 1 32,3451 0,1715

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

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

image

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

Если предоставленных операций и вычислений недостаточно, то эксель позволяет создать свое вычисляемое поле в сводной таблице. Для этого выделите ячейку из области таблицы, перейдите на вкладку «Параметры» («Анализ» для Excel 2013) появившейся ленты. Далее в разделе «Сервис» кликните по пиктограмме «Формулы», из раскрывающегося меню (в версии 2010 и выше путь отличается: Раздел «Вычисления» -> Раскрывающийся список «Поля, элементы и наборы») выберите пункт «Вычисляемое поле…». Должно появиться окно:

image

Задайте понятное имя, и запишите формулу, используя любые функции (имейте в виду, что вычисляемые поля не работают с текстом). В качестве примера умножим курс на 1000 и вычтем 13 процентов (=Курс*1000*0,87). Назовем поле «ЗП», добавим в область значений и в качестве операции применим максимум. Посмотрите новый вид отчета:

image

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

image

image

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

Теперь законченная сводная таблица выглядит так на листе Excel:

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

  1. Имя сводной таблицы;
  2. Объединение и выравнивание подписей;
  3. Вывод значений для пустых ячеек;
  4. Автоматическое изменение ширины столбцов;
  5. Отображение общих итогов по строкам и столбцам;
  6. Сортировку;
  7. Печать;
  8. Обновление и др.

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

  • Вперёд >

Добавить комментарий

Оцените статью
Рейтинг автора
4,8
Материал подготовил
Максим Коновалов
Наш эксперт
Написано статей
127
А как считаете Вы?
Напишите в комментариях, что вы думаете – согласны
ли со статьей или есть что добавить?
Добавить комментарий