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

Создав общую таблицу, в каком либо из текстовых документов, можно осуществить её анализ, сделав в Excel сводные таблицы.

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

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

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

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

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

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

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

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

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

Категорию «Товары» мы поставим в виде строк. В «Названия строк» мы переносим необходимое нам поле.

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

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

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

Указываем периоды даты и шаг. Подтверждаем выбор.

Видим такую таблицу.

Сделаем перенос поля «Сумма» к области «Значения».

Стало видно отображение чисел, а нам необходим именно числовой формат

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

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

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

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

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

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

Далее для сведения данных в поле выбираем «Количество». Подтверждаем выбор.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Шаг 1.

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

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

Шаг 2.

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

Рисунок 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 с формулами?

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

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

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

Начало работы

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

Например, рассмотрим одни и те же финансовые расходы в разных месяцах.

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

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

1. Для начала ее необходимо полностью выделить.

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

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

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

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

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

7. Для завершения настроек нужно нажать на кнопку «OK».

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

9. На этом этапе необходимо указать, какое поле будет:
   1. столбцом;
   2. строкой;
   3. значением для анализа.

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

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

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

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

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

14. Если таблица вам не понравилась, можно попробовать построить ее немного по-другому. Для этого нужно поменять поля в областях построения.

15. Снова закрываем помощник для построения.

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

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

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

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

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

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

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

4. Можно выбрать то, что нравится больше всего.

5. Для вставки выбранного варианта достаточно нажать на кнопку «OK».

6. В итоге вы получите следующий результат.

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

Анализ

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

Рассмотрим каждую из них более детально.

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

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

• изменить имя;

• вызвать окно настроек.

В окне параметров вы увидите много чего интересного.

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

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

1. Для начала нужно выделить какую-нибудь ячейку. Затем нажмите на кнопку «Активное поле». В появившемся меню кликните на пункт «Параметры поля».

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

3. Помимо этого, можно настроить числовой формат. Для этого нужно нажать на соответствующую кнопку.

4. В результате появится окно «Формат ячеек».

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

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

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

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

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

1. Выделите какой-нибудь столбец. Затем нажмите на кнопку «Вставить срез».
2. В появившемся окне, в качестве примера, выберите одно из предложенных полей (в будущем вы можете выделять их в неограниченном количестве). После того как что-нибудь будет выбрано, сразу же активируется кнопка «OK». Нажмите на неё.

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

4. Можно кликнуть на любой из пунктов. Сразу после этого в поле сумма изменятся все значения.

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

6. В любой момент всё можно вернуть в исходный вид. Для этого нужно кликнуть на иконку в правом верхнем углу этого окошка.

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

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

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

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

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

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

1. Затем нас попросят выбрать диапазон значений.

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

3. Сразу после этого адрес подставится автоматически. Здесь всё очень просто, поскольку рассчитано для чайников. Для завершения построения нажмите на кнопку «OK».

4. Редактор Excel предложит нам всего один вариант, поскольку таблица очень простая (для примера больше и не нужно).

5. Попробуйте снова нажать на иконку «Вставить временную шкалу» (она расположена на вкладке «Анализ»).

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

7. Благодаря этому появится окошко, в котором можно будет выбирать нужную дату при помощи бегунка.

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

Обновить

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

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

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

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

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

Действия

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

• очистить таблицу;
• выделить;
• переместить её.

Вычисления

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

К ним относятся:

• вычисляемое поле;

• вычисляемый объект;

• порядок вычислений (в списке отображаются добавленные формулы);

• вывести формулы (информации нет, так как нет добавленных формул).

Сервис

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

Показать

При помощи этого инструмента можно настроить внешний вид рабочего пространства редактора.

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

• настроить отображение боковой панели со списком полей;

• включить или выключить кнопки «плюс/мину»с;

• настроить отображение заголовков полей.

Конструктор

При работе со сводными таблицами помимо вкладки «Анализ» также появится еще одна – «Конструктор». Здесь вы сможете изменить внешний вид вашего объекта вплоть до неузнаваемости по сравнению с вариантом по умолчанию.

Можно настроить:

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

• общие итоги:
  • отключить для строк и столбцов;
  • включить для строк и столбцов;
  • включить только для строк;
  • включить только для столбцов.

• макет отчета:
  • показать в сжатой форме;
  • показать в форме структуры;
  • показать в табличной форме;
  • повторять все подписи элементов;
  • не повторять подписи элементов.

• пустые строки:
  • вставить пустую строку после каждого элемента;
  • удалить пустую строку после каждого элемента.

• параметры стилей сводной таблицы (здесь можно включить/выключить каждый пункт):
  • заголовки строк;
  • заголовки столбцов;
  • чередующиеся строки;
  • чередующиеся столбцы.

• настроить стиль оформления элементов.

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

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

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

Сортировка значений

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

Для этого нужно сделать следующее.

1. Кликните на треугольник около нужного поля.
2. В результате этого вы увидите следующее меню. Здесь вы можете выбрать нужный вариант сортировки («от А до Я» или «от Я до А»).

Если стандартного варианта недостаточно, вы можете в этом же меню кликнуть на пункт «Дополнительные параметры сортировки».

В результате этого вы увидите следующее окно. Для более детальной настройки нужно нажать на кнопку «Дополнительно».

Здесь всё настроено в автоматическом режиме. Если вы уберете эту галочку, то сможете указать необходимый вам ключ.

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

Описанные выше действия подходят для современных редакторов (2007, 2010, 2013 и 2016 года). В старой версии всё выглядит иначе. Возможностей, разумеется, там намного меньше.

Для того чтобы создать сводную таблицу в Экселе 2003 года, нужно сделать следующее.

1. Перейти в раздел меню «Данные» и выбрать соответствующий пункт.

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

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

4. Для завершения настроек жмем на «Готово».

5. В результате этого вы увидите следующее. Здесь нужно перетащить поля в соответствующие области.

6. К примеру, может получиться вот такой результат.

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

Заключение

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

Если данного самоучителя вам недостаточно, дополнительную информацию можно найти в онлайн справке компании Microsoft.

Видеоинструкция

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

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

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

Date	Invoice Ref	Amount	Sales Rep.	Region
01/01/2016	2016-0001	$819	Barnes	North
01/01/2016	2016-0002	$456	Brown	South
01/01/2016	2016-0003	$538	Jones	South
01/01/2016	2016-0004	$1,009	Barnes	North
01/02/2016	2016-0005	$486	Jones	South
01/02/2016	2016-0006	$948	Smith	North
01/02/2016	2016-0007	$740	Barnes	North
01/03/2016	2016-0008	$543	Smith	North
01/03/2016	2016-0009	$820	Brown	South
…	…	…	…	…

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

1. Выбираем любую ячейку из диапазона данных или весь диапазон, который будет использоваться в сводной таблице.ВНИМАНИЕ: Если выбрать одну ячейку из диапазона данных, Excel автоматически определит и выберет весь диапазон данных для сводной таблицы. Для того, чтобы Excel выбрал диапазон правильно, должны быть выполнены следующие условия:
   • Каждый столбец в диапазоне данных должен иметь своё уникальное название;
   • Данные не должны содержать пустых строк.
2. Кликаем кнопку Сводная таблица (Pivot Table) в разделе Таблицы (Tables) на вкладке Вставка (Insert) Ленты меню Excel.
3. На экране появится диалоговое окно Создание сводной таблицы (Create PivotTable), как показано на рисунке ниже.Убедитесь, что выбранный диапазон соответствует диапазону ячеек, который должен быть использован для создания сводной таблицы. Здесь же можно указать, куда должна быть вставлена создаваемая сводная таблица. Можно выбрать существующий лист, чтобы вставить на него сводную таблицу, либо вариант – На новый лист (New worksheet). Кликаем ОК.
4. Появится пустая сводная таблица, а также панель Поля сводной таблицы (Pivot Table Field List) с несколькими полями данных. Обратите внимание, что это заголовки из исходной таблицы данных.
5. В панели Поля сводной таблицы (Pivot Table Field List):
   • Перетаскиваем Sales Rep. в область Строки (Row Labels);
   • Перетаскиваем Amount в Значения (Values);
   • Проверяем: в области Значения (Values) должно быть значение Сумма по полю Amount (Sum of Amount), а не Количество по полю Amount (Count of Amount).

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

   • В области Σ Значения (Σ Values) кликаем на Количество по полю Amount (Count of Amount) и выбираем опцию Параметры полей значений (Value Field Settings);
   • На вкладке Операция (Summarise Values By) выбираем операцию Сумма (Sum);
   • Кликаем ОК.

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

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

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

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

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

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

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: /> Перевел: Антон Андронов

Правила перепечаткиЕще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение: