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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Конструктор

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

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

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

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

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

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

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

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

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

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

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

Немного истории

На заре развития программ для создания электронных таблиц балом правил Lotus 1-2-3. Его превосходство было настолько полным, что усилия Microsoft, направленные на разработку собственного программного обеспечения (Excel), как альтернативы Lotus, казались пустой тратой времени. А теперь перенесёмся в 2010 год! Excel доминирует среди электронных таблиц более, чем Lotus кода-либо за всю свою историю, а число людей, которые до сих пор используют Lotus, стремится к нулю. Как это могло произойти? Что послужило причиной для такого драматического разворота событий?

Аналитики выделяют два основных фактора:

• Во-первых, компания Lotus решила, что эта новомодная GUI-платформа с названием Windows – это всего лишь мимолётное увлечение, которое долго не протянет. Они отказались создавать версию Lotus 1-2-3 для Windows (впрочем, только несколько лет), предсказывая, что DOS-версия их программного обеспечения – это всё, что когда-либо будет нужно потребителям. Microsoft, естественно, разработала Excel специально под Windows.
• Во-вторых, Microsoft разработала в Excel такой инструмент, как сводные таблицы, которого не было в Lotus 1-2-3. Сводные таблицы, эксклюзивная для Excel вещь, оказалась так ошеломительно полезна, что люди были склонны осваивать новый программный пакет Excel, а не продолжать работать в Lotus 1-2-3, в котором их не было.

Сводные таблицы вместе с недооценкой успеха Windows в целом, сыграли похоронный марш для Lotus 1-2-3 и положили начало успеху Microsoft Excel.

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

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

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

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

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

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

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

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

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

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

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

Предположим, что необходимо подготовить отчет о продажах Товаров, но с разбивкой по Регионам продажи. Для этого добавим поле Регион продажи, поставив соответствующую галочку в Списке полей. Поле Регион продажи будет добавлено в область Названия строк Списка полей (к полю Товар). Поменяв в области Названия строк Списка полей порядок следования полей Товар и Регион продажи, получим следующий результат.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Основы

Этот раздел даёт базовые понятия по работе в Excel.

1. Лента меню: Когда Вы запускаете Excel, на Ленте меню открывается вкладка Главная. Узнайте, как сворачивать и настраивать Ленту.Примеры: Панель быстрого доступа, Вкладка Разработчик.
2. Рабочая книга: Книгой называют файл Excel. Когда Вы запускаете Excel, автоматически создаётся пустая рабочая книга.Примеры: Сохраняем в формате Excel 97-2003, Просмотр нескольких рабочих книг, Рабочая область, Автовосстановление.
3. Рабочий лист: Лист – это набор ячеек, в которых Вы храните и обрабатываете данные. По умолчанию каждая рабочая книга Excel содержит три листа (в Excel 2010 и более ранних версиях).Примеры: Масштаб, Разделяем лист, Закрепление областей, Группировка листов, Консолидация, Просмотр нескольких листов, Проверка правописания.
4. Форматирование ячеек: При форматировании ячеек в Excel, мы изменяем лишь внешний вид содержимого, не изменяя самого значения.Примеры: Десятичные знаки, Денежный против финансового, Форматы даты и времени, Дроби, Текст в число, Число в текст, Пользовательские числовые форматы, Формат по образцу, Стили ячеек, Темы.
5. Найти и выделить: В этой главе Вы научитесь использовать два очень полезных инструмента Excel: Найти и заменить и Перейти.Примеры: Особенности инструмента «Найти», Удаляем пустые строки, Отличия по строкам, Копируем только видимые ячейки, Строка поиска.
6. Шаблоны: Вместо того, чтобы создавать рабочую книгу Excel с чистого листа, Вы можете создать рабочую книгу на базе шаблона. Существует множество бесплатных шаблонов, которые так и просятся, чтобы их использовали.Примеры: Календарь, Бюджет, Планировщик питания, Счет-фактура, Автоматизированный счет-фактура, Шаблоны по умолчанию.
7. Проверка данных: Используйте проверку данных в Excel и будьте уверены, что пользователи введут в ячейку только подходящее значение.Примеры: Отбросить недопустимые даты, Ограничение бюджета, Предотвращение дублирования записей, Коды продуктов, Выпадающий список, Зависимые выпадающие списки.
8. Сочетания клавиш: Сочетания клавиш позволяют увеличивать скорость работы, используя клавиатуру вместо мыши.Примеры: Функциональные клавиши, Подсказки клавиш.
9. Печать: Эта глава научит вас отправлять листы Excel на печать и задавать некоторые важные настройки при печати.Примеры: Режимы просмотра, Разрывы страниц, Верхние и нижние колонтитулы, Номера страниц, Печать заголовков, Центрирование на странице, Печать сетки и заголовков строк/столбцов, Область печати.
10. Обмен данными и общий доступ: Узнайте, как можно отправить данные Excel в документ Word или в другие файлы. Кроме этого, из множества приведенных примеров вы узнаете, как предоставить общий доступ к книге Excel.Примеры: Лист Excel в Word, Сохранить в PDF, Общий доступ к книге, SkyDrive, Excel Online, Импорт данных из Access, Microsoft Query, Импорт и экспорт текстовых файлов, XML.
11. Защита: Зашифруйте файл Excel при помощи пароля. Только зная пароль, можно будет открыть этот файл.Примеры: Защита книги, Защита листа, Блокировка ячеек, Книга только для чтения, Пометить как окончательную.

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

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

Посмотрите внимательно на рабочий лист табличного процессора:

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

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

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

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

Для выделения строки – по названию строки (по цифре).

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

Для выделения столбца с помощью горячих клавиш ставим курсор в любую ячейку нужного столбца – нажимаем Ctrl + пробел. Для выделения строки – Shift + пробел.

Как изменить границы ячеек

Если информация при заполнении таблицы не помещается нужно изменить границы ячеек:

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

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

Примечание. Чтобы вернуть прежний размер, можно нажать кнопку «Отмена» или комбинацию горячих клавиш CTRL+Z. Но она срабатывает тогда, когда делаешь сразу. Позже – не поможет.

Чтобы вернуть строки в исходные границы, открываем меню инструмента: «Главная»-«Формат» и выбираем «Автоподбор высоты строки»

Для столбцов такой метод не актуален. Нажимаем «Формат» — «Ширина по умолчанию». Запоминаем эту цифру. Выделяем любую ячейку в столбце, границы которого необходимо «вернуть». Снова «Формат» — «Ширина столбца» — вводим заданный программой показатель (как правило это 8,43 — количество символов шрифта Calibri с размером в 11 пунктов). ОК.

Как вставить столбец или строку

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

Нажимаем правой кнопкой мыши – выбираем в выпадающем меню «Вставить» (или жмем комбинацию горячих клавиш CTRL+SHIFT+»=»).

Отмечаем «столбец» и жмем ОК.

Совет. Для быстрой вставки столбца нужно выделить столбец в желаемом месте и нажать CTRL+SHIFT+»=».

Все эти навыки пригодятся при составлении таблицы в программе Excel. Нам придется расширять границы, добавлять строки /столбцы в процессе работы.

Пошаговое создание таблицы с формулами

1. Заполняем вручную шапку – названия столбцов. Вносим данные – заполняем строки. Сразу применяем на практике полученные знания – расширяем границы столбцов, «подбираем» высоту для строк.
2. Чтобы заполнить графу «Стоимость», ставим курсор в первую ячейку. Пишем «=». Таким образом, мы сигнализируем программе Excel: здесь будет формула. Выделяем ячейку В2 (с первой ценой). Вводим знак умножения (*). Выделяем ячейку С2 (с количеством). Жмем ВВОД.
3. Когда мы подведем курсор к ячейке с формулой, в правом нижнем углу сформируется крестик. Он указываем на маркер автозаполнения. Цепляем его левой кнопкой мыши и ведем до конца столбца. Формула скопируется во все ячейки.
4. Обозначим границы нашей таблицы. Выделяем диапазон с данными. Нажимаем кнопку: «Главная»-«Границы» (на главной странице в меню «Шрифт»). И выбираем «Все границы».

Теперь при печати границы столбцов и строк будут видны.

С помощью меню «Шрифт» можно форматировать данные таблицы Excel, как в программе Word.

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

Урок 46. Как создать сводную таблицу в Excel?

Приветствую вас, посетители блога!

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

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

Еще кое-что: статья написана не только для тех, кто занимается интернет-маркетингом, но и для людей, занимающимся другими сферами деятельности. Поэтому этот урок я разделю на две части:

1. Выгрузка отчета из Метрики;
2. Создание сводной таблицы;

Тем, кто не интересуется интернет-маркетингом первую часть можно не читать!

Выгрузка отчета из Метрики

Итак. Давайте выгрузим отчет о расходах рекламных кампаний Директ из Яндекс.Метрики в XLSX-файл. Для этого перейдите в отчет “Директ-расходы”: 

Затем нажмите на небольшую кнопку “Экспорт” в правом верхнем углу отчета и выберите “XLSX” из графы “Данные из таблицы”: 

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

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

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

• Убираем все объединенные ячейки;
• Убираем пустые ячейки;

После всех подготовок исходная таблица примет вид: 

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

Ну-с начнем создавать:

1. Перейдите во вкладку “Вставка”: 
2. Во вкладке “Вставка” найдите кнопку “Сводная таблица” (крайняя с лева). Нажмите на нее: 
3. В появившемся окошке укажите куда поместить отчет: на новый лист или на существующий. Нажмите “ОК”: 

И вот перед вами конструктор сводной таблицы. Наша задача – перетащите все необходимые данные таблицы в определенные поля сводной таблицы. Делается это в области с права: 

Как видите здесь четыре области:

• Фильтр отчета. Предназначается для фильтрации данных;
• Названия строк. Сюда переносятся анализируемые поля. Например, рекламная кампания, объявление и ключевая фраза;
• Названия столбцов. Здесь указываются значения. Подставлять ничего не нужно, данные из поля значения перенесутся автоматически;
• Значения. Сюда переносятся данные, с помощью которых анализируются поля, перенесенные в область “Названия строк”;

• Кажется доходчиво объяснил.
• Итак, вот пример моей сводной таблицы, где я просматриваю расходы, переходы по каждой кампании, по каждому объявлению, по каждой фразе: 
• В области “Названия строк” сохранена изначальная иерархия, то есть сперва кампании, потом объявления, затем ключевые фразы. Благодаря этому сводная таблица принимает следующий вид: 
• Как видите, все данные вложены по порядку. А вся таблица выглядит так: 
• Как создать сводную таблицу в Excel вы теперь знаете, осталось только сделать ее восприятие проще.

Упрощаем восприятие данных таблицы

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

Сделаем это следующим образом:

1. Во вкладке “Конструктор” выберите любой, понравившийся вам, стиль оформления таблицы: 
2. Затем переименуйте столбцы. Дважды щелкните по столбцу и дайте название;
3. Присвойте денежный формат столбцу с расходами. Нажмите правой кнопкой мыши на ячейке с данными о расходах и выберите “Числовой формат”, затем в списке с лева найдите пункт “Денежный формат”: 
4. Указываем гистограмму в каждой ячейке столбца “Расходы”. Выделите ячейки столбца “Расходы” и на вкладке “Главная” найдите кнопку “Условную форматирование”, нажмите на нее. В выпадающем списке выберите пункт “Гистограмма”: 

Результат: 

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

Ну а на этом все! Думаю, что на вопрос “Как создать сводную таблицу в Excel?” я ответил.

До свидания!

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

Оформите свою таблицу – выберите для неё образ

Автофильтр – сортировка и фильтрация

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

Я хочу знать, где было продано больше всего хот-догов, а где меньше всего. Для этого нажмем опцию Sort Largest to Smallest (Сортировка по убыванию). Теперь мы видим, что на торговой точке (Game) 2 было продано больше всего хот-догов, а список с информацией изменился таким образом, что данные теперь расположены в порядке убывания значений продаж хот-догов.

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

Добавляем новые столбцы

Итак, у Вас есть таблица, она симпатично отформатирована, и вдруг Вы осознаёте, что забыли включить в нее ещё два столбца!!! Не проблема… для этого тоже есть специальная команда. На вкладке Table Tools / Design (Работа с таблицами / Конструктор) имеется команда Resize Table (Размер таблицы). Когда нажмёте эту кнопку, будет выделена ранее установленная Вами область таблицы, теперь нужно растянуть выделение на новую область, т.е. добавить дополнительные столбцы. Затем нажмите ОК.

Вуаля! Два новых столбца добавлены. Я сдвинул столбец итогов (TOTAL) на край таблицы и обновил формулы, чтобы включить новые столбцы с данными.

Новая таблица выглядит вот так! Добавлены столбцы Burgers и Chips.

Использование нескольких таблиц для создания сводной таблицы

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

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

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

Чтобы объединить несколько таблиц в списке полей сводной таблицы:

Можно импортировать их из реляционной базы данных, например, Microsoft SQL Server, Oracle или Microsoft Access. Вы можете импортировать несколько таблиц одновременно.

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

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

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

Щелкните Данные > Получение внешних данных > Из других источников > С сервера SQL Server.

В поле Имя сервера введите сетевое имя компьютера с запущенным сервером SQL Server.

В разделе Учетные данные входа в систему выберите команду Использовать проверку подлинности Windows, если вы подключаетесь с помощью своих учетных данных. В противном случае введите имя пользователя и пароль, предоставленные администратором базы данных.

Нажмите клавишу ВВОД и в разделе Выбор базы данных и таблицы выберите нужную базу данных, а затем щелкните Разрешить выбор нескольких таблиц.

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

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

В диалоговом окне Импорт данных выберите элемент Отчет сводной таблицы.

Нажмите кнопку ОК, чтобы начать импорт и заполнить список полей.

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

Каждую таблицу можно развернуть и свернуть для просмотра ее полей. Так как таблицы связаны, вы можете создать сводную таблицу, перетянув поля из любой таблицы в область ЗНАЧЕНИЯ, СТРОКИ или СТОЛБЦЫ.

Перетащите числовые поля в область ЗНАЧЕНИЯ. Например, если используется образец базы данных Adventure Works, вы можете перетащить поле «ОбъемПродаж» из таблицы «ФактПродажиЧерезИнтернет».

Перетащите поля даты или территории в область СТРОКИ или СТОЛБЦЫ, чтобы проанализировать объем продаж по дате или территории сбыта.

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

Работа с другими типами баз данных

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

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

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

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

Если не обратить на это внимание, в дальнейшем могут возникнуть проблемы. Условия таковы:

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

Рассмотрим два метода создания табличного отчета.

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

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

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

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

1

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

2

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

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

3

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

4

1. Откроется окно для формирования таблички. В нем находится список полей и области настроек. В верхней части окна выбираем нужные поля. После этого перетаскиваем их в нужные области.

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

5

Когда таблица сформирована, нужно применить выбранные фильтры. Разберем этот этап по шагам. Стоит вспомнить условие: нужно определить продажи женских теннисных кроссовок.

1. Открываем раздел «Пол» в таблице и выбираем «Женский», после этого кликаем «ОК».

6

1. Применяем фильтр к виду спорта – согласно условию, необходимо поставить галочку в графе «Теннис» и кликнуть «ОК».

Результат: на листе представлены только запрошенные данные. Информация о сумме в нескольких строчках с одинаковым наименованием суммируется.

7

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

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

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

8

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

9

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

10

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

11

1. Заполняем форму сводной таблички по правилам классического метода и выставляем фильтры.