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

Содержание

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

Индык Игорь Викторович
e-mail:

СМОТРЕТЬ УКРАИНОЯЗЫЧНЫЙ ВАРИАНТ СТАТЬИ

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

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

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

Откроется следующее диалоговое окно:

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

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

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

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

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

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

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

а) в верхней части окна настроек отмечаем все названия необходимых нам столбцов:

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

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

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

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

Настройка нашей таблицы должна выглядеть вот так:

Тогда наша сводная таблица будет иметь следующий вид:

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

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

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

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

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

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

Сводные таблицы необходимы для суммирования, анализа и представления данных, находящихся в «больших» исходных таблицах, в различных разрезах. Рассмотрим процесс создания несложных Сводных таблиц.

Сводные таблицы (Вставка/ Таблицы/ Сводная таблица) могут пригодиться, если одновременно выполняются следующие условия:

  • имеется исходная таблица с множеством строк (записей), речь идет о нескольких десятках и сотнях строк;
  • необходимо провести анализ данных, который требует выборки (фильтрации) данных, их группировки (суммирования, подсчета) и представления данных в различных разрезах (подготовки отчетов);
  • этот анализ затруднительно провести на основе исходной таблицы с использованием других стредств: фильтра (CTRL+SHIFT+L), формул, Расширенного фильтра;
  • исходная таблица удовлетворяет определенным требованиям (см. ниже).

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

Подготовка исходной таблицы

Начнем с требований к исходной таблице.

  • каждый столбец должен иметь заголовок;
  • в каждый столбец должны вводиться значения только в одном формате (например, столбец «Дата поставки» должен содержать все значения только в формате Дата; столбец «Поставщик» — названия компаний только в текстовом формате или можно вводить Код поставщика в числовом формате);
  • в таблице должны отсутствовать полностью незаполненные строки и столбцы;
  • в ячейки должны вводиться «атомарные» значения, т.е. только те, которые нельзя разнести в разные столбцы. Например, нельзя в одну ячейку вводить адрес в формате: «Город, Название улицы, дом №». Нужно создать 3 одноименных столбца, иначе Сводная таблица будет работать неэффективно (в случае, если Вам нужна информация, например, в разрезе города);
  • избегайте таблиц с «неправильной» структурой (см. рисунок ниже).

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

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

Несколько облегчит процесс построения Сводной таблицы, тот факт, если исходная таблица будет преобразована в формат EXCEL 2007 (Вставка/ Таблицы/ Таблица). Для этого сначала приведите исходную таблицу в соответствие с вышеуказанными требованиями, затем выделите любую ячейку таблицы и вызовите окно меню Вставка/ Таблицы/ Таблица. Все поля окна будут автоматически заполнены, нажмите ОК.

Создание таблицы в формате EXCEL 2007 добавляет новые возможности:

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

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

В таблице имеются столбцы:

  • Товар – наименование партии товара, например, «Апельсины»;
  • Группа – группа товара, например, «Апельсины» входят в группу «Фрукты»;
  • Поставщик – компания-поставщик Товаров, Поставщик может поставлять несколько Групп Товаров;
  • Дата поставки – Дата поставки Товара Поставщиком;
  • Регион продажи – Регион, в котором была реализована партия Товара;
  • Продажи – Стоимость, по которой удалось реализовать партию Товара;
  • Сбыт – срок фактической реализации Товара в Регионе (в днях);
  • Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.

Через Диспетчер имен (Формулы/ Определенные имена/ Диспетчер имен) откорректируем Имя таблицы на «Исходная_таблица».

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

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

Имея исходную таблицу в формате EXCEL 2007, для создания Сводной таблицы достаточно выделить любую ячейку исходной таблицы и в меню Работа с таблицами/ Конструктор/ Сервис выбрать пункт Сводная таблица.

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

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

Структура Сводной таблицы в общем виде может быть представлена так:

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

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

Теперь поставим галочку в Списке полей у поля Продажи.

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

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

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

Если возникли вопросы о том, какие же данные из исходной таблицы были использованы для подсчета тех или иных значений Сводной таблицы, то достаточно двойного клика мышкой на конкретном значении в Сводной таблице, чтобы был создан отдельный лист с отобранными из исходной таблицей строками. Например, посмотрим какие записи были использованы для суммирования продаж Товара «Апельсины». Для этого дважды кликнем на значении 646720. Будет создан отдельный лист только со строками исходной таблицы относящихся к Товару «Апельсины».

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

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

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

Удалить Сводную таблицу можно несколькими способами. Первый – просто удалить лист со Сводной таблицей (если на нем нет других полезных данных, например исходной таблицы). Второй способ — удалить только саму Сводную таблицу: выделите любую ячейку Сводной таблицы, нажмите CTRL+A (будет выделена вся Сводная таблица), нажмите клавишу Delete.

Изменение функции итогов

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

Изменение порядка сортировки

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

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

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

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

Изменение формата числовых значений

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

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

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

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

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

Добавление столбцов

Добавление поля Регион продажи в область строк привело к тому, что Сводная таблица развернулась на 144 строки. Это не всегда удобно. Т.к. продажи осуществлялись только в 6 регионах, то поле Регион продажи имеет смысл разместить в области столбцов.

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

Меняем столбцы местами

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

Удаление полей

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

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

Добавление фильтра

Предположим, что необходимо подготовить отчет о продажах Групп Товаров, причем его нужно сделать в 2-х вариантах: один для партий Товаров принесших прибыль, другой – для убыточных. Для этого:

  • Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все;
  • Ставим галочки в Списке полей у полей Группа, Продажи и Прибыль;
  • Переносим поле Прибыль из области Названия строк Списка полей в область Фильтр отчета;

Вид получившейся Сводной таблицы должен быть таким:

Теперь воспользовавшись Выпадающим (раскрывающимся) списком в ячейке B1 (поле Прибыль) можно, например, построить отчет о продажах Групп Товаров, принесших прибыль.

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

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

Очистить фильтр можно через меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить фильтры.

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

Несколько итогов для одного поля

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

  • Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все;
  • Поставьте галочки напротив полей Товар и Продажи в верхней части Списка полей. Поле Продажи будет автоматически помещено в область Значения;
  • Перетащите мышкой еще одну копию поля Продажи в ту же область Значения. В Сводной таблице появится 2 столбца подсчитывающими суммы продаж;
  • в Сводной таблице выделите любое значение поля Продажи, вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество. Задача решена.

Отключаем строки итогов

Строку итогов можно отключить через меню: Работа со сводными таблицами/ Конструктор/ Макет/ Общие итоги. Не забудьте предварительно выделить любую ячейку Сводной таблицы.

Группируем числа и Даты

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

  • Очистим ранее созданный отчет: выделите любое значение Сводной таблицы, нажмите пункт меню Работа со сводными таблицами/ Параметры/ Действия/ Очистить/ Очистить все;
  • Поставьте галочку напротив поля Сбыт (срок фактической реализации Товара) в верхней части Списка полей. Поле Сбыт будет автоматически помещено в область Значения;
  • выделите единственное значение поля Сбыт в Сводной таблице, вызовите правой клавишей мыши контекстное меню и выберите пункт Итоги по/ Количество.
  • Перетащите мышкой еще одну копию поля Сбыт в область Названия строк;

Теперь Сводная таблица показывает сколько партий Товара сбывалось за 5, 6, 7, … дней. Всего 66 строк. Сгруппируем значения с шагом 10. Для этого:

  • Выделите одно значение Сводной таблицы в столбце Названия строк;
  • В меню Работа со сводными таблицами/ Параметры/ Группировать выберите пункт Группировка по полю;
  • Появившееся окно заполните, как показано на рисунке ниже;
  • Нажмите ОК.

Теперь Сводная таблица показывает сколько партий Товара сбывалось в период от 1 до 10 дней, в период 11-20 дней и т.д.

Чтобы разгруппировать значения выберите пункт Разгруппировать в меню Работа со сводными таблицами/ Параметры/ Группировать.

Аналогичную группировку можно провести по полю Дата поставки. В этом случае окно Группировка по полю будет выглядеть так:

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

Условное форматирование ячеек Сводной таблицы

К ячейкам Сводной таблицы можно применить правила Условного форматирования как и к ячейкам обычного диапазона.
Выделим, например, ячейки с 10 наибольшими объемами продаж. Для этого:

  • Выделите все ячейки содержащие значения продаж;
  • Выберите пункт меню Главная/ Стили/ Условное форматирование/ Правила отбора первых и последних значений/ 10 первых элементов;
  • Нажмите ОК.

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. Для завершения настроек нужно нажать на кнопку «OK».
  1. В результате этого вы увидите пустой шаблон, для работы со сводными таблицами.
  1. На этом этапе необходимо указать, какое поле будет:
    1. столбцом;
    2. строкой;
    3. значением для анализа.

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

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

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

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

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

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

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

Анализ

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

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

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

  • изменить имя;
  • вызвать окно настроек.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Обновить

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

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

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

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

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

Действия

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

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

Вычисления

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

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

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

Сервис

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

Показать

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

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

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

Конструктор

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Отчеты в MS EXCEL

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

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

В таблице имеются столбцы:

  • Товар – наименование партии товара, например, «Апельсины»;
  • Группа – группа товара, например, «Апельсины» входят в группу «Фрукты»;
  • Дата поставки – Дата поставки Товара Поставщиком;
  • Регион продажи – Регион, в котором была реализована партия Товара;
  • Продажи – Стоимость, по которой удалось реализовать партию Товара;
  • Сбыт – срок фактической реализации Товара в Регионе (в днях);
  • Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.

Через Диспетчер имен откорректируем имя таблицы на «Исходная_таблица» (см. файл примера).

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

Отчет №1 Суммарные продажи Товаров

Найдем суммарные продажи каждого Товара.
Задача решается достаточно просто с помощью функции СУММЕСЛИ(), однако само построение отчета требует определенных навыков работы с некоторыми средствами EXCEL.

Итак, приступим. Для начала нам необходимо сформировать перечень названий Товаров. Т.к. в столбце Товар исходной таблицы названия повторяются, то нам нужно из него выбрать только уникальные значения. Это можно сделать несколькими способами: формулами (см. статью Отбор уникальных значений), через меню Данные/ Работа с данными/ Удалить дубликаты или с помощью Расширенного фильтра. Если воспользоваться первым способом, то при добавлении новых Товаров в исходную таблицу, новые названия будут включаться в список автоматически. Но, здесь для простоты воспользуемся вторым способом. Для этого:

  • Перейдите на лист с исходной таблицей;
  • Вызовите Расширенный фильтр (Данные/ Сортировка и фильтр/ Дополнительно);
  • Заполните поля как показано на рисунке ниже: переключатель установите в позицию Скопировать результат в другое место; в поле Исходный диапазон введите $A$4:$A$530; Поставьте флажок Только уникальные записи.

  • Скопируйте полученный список на лист, в котором будет размещен отчет;
  • Отсортируйте перечень товаров (Данные/ Сортировка и фильтр/ Сортировка от А до Я).

Должен получиться следующий список.

В ячейке B6 введем нижеследующую формулу, затем скопируем ее Маркером заполнения вниз до конца списка:

=СУММЕСЛИ(Исходная_Таблица;A6;Исходная_Таблица)

Для того, чтобы понять сруктурированные ссылки на поля в таблицах в формате EXCEL 2007 можно почитать Справку EXCEL (клавиша F1) в разделе Основные сведения о листах и таблицах Excel > Использование таблиц Excel.

Также можно легко подсчитать количество партий каждого Товара:

=СЧЁТЕСЛИ(Исходная_Таблица;A6)

Отчет №2 Продажи Товаров по Регионам

Найдем суммарные продажи каждого Товара в Регионах.
Воспользуемся перечнем Товаров, созданного для Отчета №1. Аналогичным образом получим перечень названий Регионов (в поле Исходный диапазон Расширенного фильтра введите $D$4:$D$530).
Скопируйте полученный вертикальный диапазон в Буфер обмена и транспонируйте его в горизонтальный. Полученный диапазон, содержащий названия Регионов, разместите в заголовке отчета.

В ячейке B8 введем нижеследующую формулу:

=СУММЕСЛИМН(Исходная_Таблица;
Исходная_Таблица;$A8;
Исходная_Таблица;B$7)

Формула вернет суммарные продажи Товара, название которого размещено в ячейке А8, в Регионе из ячейки В7. Обратите внимание на использование смешанной адресации (ссылки $A8 и B$7), она понадобится при копировании формулы для остальных незаполненных ячеек таблицы.

Скопировать вышеуказанную формулу в ячейки справа с помощью Маркера заполнения не получится (это было сделано для Отчета №1), т.к. в этом случае в ячейке С8 формула будет выглядеть так:

=СУММЕСЛИМН(Исходная_Таблица;
Исходная_Таблица;$A8;
Исходная_Таблица;C$7)

Ссылки, согласно правил относительной адресации, теперь стали указывать на другие столбцы исходной таблицы (на те, что правее), что, естественно, не правильно. Обойти это можно, скопировав формулу из ячейки B8, в Буфер обмена, затем вставить ее в диапазон С8:G8, нажав CTRL+V.
В ячейки ниже формулу можно скопировать Маркером заполнения.

Отчет №3 Фильтрация Товаров по прибыльности

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

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

После ввода формулы не забудьте вместо простого нажатия клавиши ENTER нажать CTRL+SHIFT+ENTER.

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

=СУММПРОИЗВ((Исходная_Таблица=A8)*
ЕСЛИ($B$5=»(Все)»;1;(Исходная_Таблица=$B$5)))

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

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

Отчет №4 Статистика сроков сбыта Товаров

Вернемся к исходной таблице. Каждая партия Товара сбывалась определенное количество дней (см. столбец Сбыт в исходной таблице). Необходимо подготовить отчет о количестве партий, которые удалось сбыть за за период от 1 до 10 дней, 11-20 дней; 21-30 и т.д.

Вышеуказанные диапазоны сформируем нехитрыми формулами в столбце B.

Количество партий, сбытые за определенный период времени, будем подсчитывать с помощью формулы ЧАСТОТА(), которую нужно ввести как формулу массива:

=ЧАСТОТА(Исходная_Таблица;A7:A12)

Для ввода формулы выделите диапазон С6:С12, затем в Строке формул введите вышеуказанную формулу и нажмите CTRL+SHIFT+ENTER.

Отчет №5 Статистика поставок Товаров

Теперь подготовим отчет о поставках Товаров за месяц.
Сначала создадим перечень месяцев по годам. В исходной таблице самая ранняя дата поставки 11.07.2009. Вычислить ее можно с помощью формулы:
=МИН(Исходная_Таблица)

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

Применив соответствующий формат ячеек, изменим отображение дат:

Формула для подсчета количества поставленных партий Товаров за месяц:

=СУММПРОИЗВ((Исходная_Таблица>=B9)*
(Исходная_Таблица<B10))

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

Теперь для вывода промежуточных итогов по годам создадим структуру через пункт меню Данные/ Структура/ Промежуточные итоги:

  • Выделите любую ячейку модифицированной таблицы;
  • Вызовите окно Промежуточные итоги через пункт меню Данные/ Структура/ Промежуточные итоги;
  • Заполните поля как показано на рисунке:

После нажатия ОК, таблица будет изменена следующим образом:

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

Резюме:

Отчеты, аналогичные созданным, можно сделать, естественно, с помощью Сводных таблиц или с применением Фильтра к исходной таблице или с помощью других функций БДСУММ(), БИЗВЛЕЧЬ(), БСЧЁТ() и др. Выбор подхода зависит конкретной ситуации.

Как создать отчет в excel. Как сделать профессионально выглядящий отчет в Excel

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

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

В программе Excel 2007 (MS Excel 2010|2013) сводная таблица используется, в первую очередь, для составления математического или экономического анализа данных.

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

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

Табличный процессор Эксель может справиться даже с самыми объемными и сложными документами. Составить отчет и обобщить данные не составит труда для программы.

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

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

Совет! Дополнительные макеты сводных таблиц можно скачать с официального сайта компании «Майкрософт «.

  • Нажмите клавишу ОК, и программа сразу добавит выбранную таблицу (или пустой макет) на открытый лист документа. Также программа автоматически определит порядок расположения строк, согласно представляемой информации;
  • Чтобы выделить элементы таблицы и упорядочить их вручную, отсортируйте содержимое. Также данные можно фильтровать. По сути, сводная табличка – это прототип небольшой базы данных.
    Фильтрация крайне необходима, когда появляется необходимость быстрого просмотра только определенных колонок и строчек. Ниже приведен пример сводной таблицы по продажам после фильтрования содержимого.
    Таким образом можно быстро просмотреть объемы продаж в отдельных регионах (в нашем случае, запад и Юг);

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

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

Пустая форма заполняется путем перетаскивания на отдельные области необходимых элементов данных.

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

Таким образом можно анализировать данные всего документа или нескольких документов/листов сразу.

Проводить анализ внешних данных тоже можно с помощью сводных таблиц.

Найдем суммарные продажи каждого Товара в Регионах.
Воспользуемся перечнем Товаров, созданного для Отчета №1. Аналогичным образом получим перечень названий Регионов (в поле Исходный диапазон введите $D$4:$D$530).
Скопируйте полученный вертикальный диапазон в Буфер обмена и его в горизонтальный. Полученный диапазон, содержащий названия Регионов, разместите в заголовке отчета.

В ячейке B 8 введем нижеследующую формулу:

СУММЕСЛИМН(Исходная_Таблица;
Исходная_Таблица;$A8;
Исходная_Таблица;B$7)

Формула вернет суммарные продажи Товара, название которого размещено в ячейке А8 , в Регионе из ячейки В7 . Обратите внимание на использование (ссылки $A8 и B$7), она понадобится при копировании формулы для остальных незаполненных ячеек таблицы.

Скопировать вышеуказанную формулу в ячейки справа с помощью не получится (это было сделано для Отчета №1), т.к. в этом случае в ячейке С8 формула будет выглядеть так:

СУММЕСЛИМН(Исходная_Таблица;
Исходная_Таблица;$A8;
Исходная_Таблица;C$7)

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

ЧАСТОТА(Исходная_Таблица;A7:A12)

Для ввода формулы выделите диапазон С6:С12 , затем в Строке формул введите вышеуказанную формулу и нажмите CTRL + SHIFT + ENTER .

Теперь подготовим отчет о поставках Товаров за месяц.
Сначала создадим перечень месяцев по годам. В исходной таблице самая ранняя дата поставки 11.07.2009. Вычислить ее можно с помощью формулы:
=МИН(Исходная_Таблица)

Создадим перечень дат — , начиная с самой ранней даты поставки. Для этого воспользуемся формулой:
=КОНМЕСЯЦА($C$5;-1)+1

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

Применив соответствующий формат ячеек, изменим отображение дат:

Формула для подсчета количества поставленных партий Товаров за месяц:

СУММПРОИЗВ((Исходная_Таблица>=B9)*
(Исходная_Таблица

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

Теперь для вывода по годам создадим структуру через пункт меню :

  • Выделите любую ячейку модифицированной таблицы;
  • Вызовите окно через пункт меню Данные/ Структура/ Промежуточные итоги ;
  • Заполните поля как показано на рисунке:

После нажатия ОК, таблица будет изменена следующим образом:

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

Резюме :

Отчеты, аналогичные созданным, можно сделать, естественно, с помощью или с применением Фильтра к исходной таблице или с помощью других функций БДСУММ() , БИЗВЛЕЧЬ() , БСЧЁТ() и др. Выбор подхода зависит конкретной ситуации.

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

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

Простые альтернативы ВПР и ГПР, если искомые значения не в первом столбце таблицы: ПРОСМОТР, ИНДЕКС+ПОИСКПОЗ

Результат:

В данном случае, кроме функции СИМВОЛ (CHAR) (для отображения кавычек) используется функция ЕСЛИ (IF), позволяющая изменять текст в зависимости от того, наблюдается ли положительная динамика продаж, и функция ТЕКСТ (TEXT), позволяющая отобразить число в любом формате. Её синтаксис описан ниже:

ТЕКСТ (значение; формат )

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

Автоматизировать можно и более сложные тексты. В моей практике была автоматизация длинных, но рутинных комментариев к управленческой отчётности в формате «ПОКАЗАТЕЛЬ упал/вырос на XX относительно плана в основном из-за роста/снижения ФАКТОРА1 на XX, роста/снижения ФАКТОРА2 на YY…» с меняющимся списком факторов. Если вы пишете такие комментарии часто и процесс их написания можно алгоритмизировать — стоит один раз озадачиться созданием формулы или макроса, которые избавят вас хотя бы от части работы.

Как сохранить данные в каждой ячейке после объединения

При объединении ячеек сохраняется только одно значение. Excel предупреждает об этом при попытке объединить ячейки:

Соответственно, если у вас была формула, зависящая от каждой ячейки, она перестанет работать после их объединения (ошибка #Н/Д в строках 3–4 примера):

Чтобы объединить ячейки и при этом сохранить данные в каждой из них (возможно, у вас есть формула, как в этом абстрактном примере; возможно, вы хотите объединить ячейки, но сохранить все данные на будущее или скрыть их намеренно), объедините любые ячейки на листе, выделите их, а затем с помощью команды «Формат по образцу» перенесите форматирование на те ячейки, которые вам и нужно объединить:

Как построить сводную из нескольких источников данных

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

Сделать это можно следующим образом: «Файл» → «Параметры» → «Панель быстрого доступа» → «Все команды» → «Мастер сводных таблиц и диаграмм» → «Добавить»:

После этого на ленте появится соответствующая иконка, нажатие на которую вызывает того самого мастера:

При щелчке на неё появляется диалоговое окно:

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

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

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

Отчёт сводной таблицы готов. В фильтре «Страница 1» вы можете выбрать только один из источников данных, если это необходимо:

Как рассчитать количество вхождений текста A в текст B («МТС тариф СуперМТС» — два вхождения аббревиатуры МТС)

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

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

Чтобы найти вхождение определённой текстовой строки в другую, нужно удалить все её вхождения в исходную и сравнить длину полученной строки с исходной:

ДЛСТР(“Тариф МТС Супер МТС”) – ДЛСТР(“Тариф Супер”) = 6

А затем разделить эту разницу на длину той строки, которую мы искали:

6 / ДЛСТР (“МТС”) = 2

Именно два раза строка «МТС» входит в исходную.

Осталось записать этот алгоритм на языке формул (обозначим «текстом» тот текст, в котором мы ищем вхождения, а «искомым» — тот, число вхождений которого нас интересует):

=(ДЛСТР(текст )-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(текст );ПРОПИСН(искомый );“”)))/ДЛСТР(искомый )

В нашем примере формула выглядит следующим образом:

=(ДЛСТР(A2)-ДЛСТР(ПОДСТАВИТЬ(ПРОПИСН(A2);ПРОПИСН($E$1);“”)))/ДЛСТР($E$1)

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

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

Правда же, эта таблица мало информативна и в таком виде не представляет пользы? А вот сводная таблица, сформированная из этих данных:

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

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

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

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

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

Чтобы сделать сводную таблицу на основании своих данных – выполните такую последовательность действий:

  1. Установите курсор в любую ячейку таблицы
  2. Нажмите на ленте: Вставка – Сводная таблица
  3. Укажите расположение будущей сводной таблицы. Чтобы поместить ее на новый лист – установите галку «На новый лист». Чтобы выбрать расположение на существующих листах – выберите «На существующий лист» и в поле «Диапазон» укажите расположение верней левой ячейки сводной таблицы;
  1. Нажмите Ок

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

  1. «Выберите поля для добавления в отчет» — это заголовки всех столбцов, которые есть в таблице. Этими данными мы будем заполнять следующие 4 блока
  2. Фильтры – список полей, по которым будет применяться фильтр. Эти поля появляются над сводной таблицей
  3. Колонны – область, где задается, что будет содержаться в столбцах
  4. Строки – область, где указывается, что будет содержаться в строках
  5. Значения – задаем то, что будет отображаться или рассчитываться на пересечении строк или столбцов. То есть, основное тело таблицы

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

Если теперь мы захотим, чтобы в столбцах данные были разбиты по группам товаров. Перетянем поле «Группа товара» в «Колонны», получаем результат:

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

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

А теперь нам, к примеру, захотелось узнать, кто из менеджеров продает больше всего. Снимем все фильтры, уберем галку «Регион» из строк. Получаем список менеджеров и их продажи. Кликнем правой кнопкой мыши в любой из строк «продажи» колонки «Общий итог», в контекстном меню выбираем Сортировка – Сортировка по убыванию . Естественно, сверху будет менеджер с наибольшими продажами, снизу – с наименьшими.

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

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

Вы можете настраивать макет сводной таблицы в части логики построения. Выделите любую ее ячейку и найдите на ленте Конструктор – Макет . Здесь можно сделать настройки по четырем пунктам:

  1. Промежуточные итоги – включить или отключить итоги для промежуточных групп внутри таблицы
  2. Общие итоги – настроить расчет общих итогов по всей таблице
  3. Макет отчета – способ компоновки данных для наибольшего удобства
  4. Пустые строки – вставить или удалить пустые строки в конце каждой категории для улучшения восприятия данных.

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

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

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

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

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

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

Плюсы использования такого вида группировки данных:

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

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

Пример создания сводной таблицы Excel – алгоритм для чайников

Ознакомившись с базовыми теоретическими нюансами про сводные таблицы в Excel, давайте перейдем к применению их на деле. Для старта создания сводной таблицы в Excel 2016, 2010 или 2007 необходимо установить программное обеспечение. Как правило, если вы пользуетесь программами системы Microsoft Office, то Excel уже есть на вашем компьютере.

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

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

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

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

Первую строку не оставляем пустой, иначе программа выдаст ошибку. Если есть источник, с которого планируете переноситься данные, то выберите его в пункте «Использовать внешний источник данных». Под внешним источником подразумевается другая книга Excel или набор моделей данных из СУБД.

Заранее озаглавьте каждый столбик

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

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

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

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

Вы можете свободно корректировать ширину столбиков для оптимального расположения данных. Просто раздвигайте ширину столбцов или высоту строк так, как вы привыкли в Excel.
Если вам не нравится такая ориентация, вы можете перетащить мышкой названия строк в область столбцов — просто наведите мышь, нажмите левую кнопку и тяните.
Что касается подсчета результатов, то сумма – далеко не единственная функция. Чтобы посмотреть, какие Excel предлагает еще, нажмите на название в области значений и выберите последнюю команду.
В параметрах полей значений вы найдете множество вариантов для анализа.
Для каждого значения можно выбрать свою функцию. Например, добавим поле «Цена» и найдем максимальную цену товара в каждом отделе. Фактически, узнаем сколько стоит самый дорогой.
Теперь мы видим, что в отдел «Аксессуары» поступило товаров на сумму 267660 рублей, при этом самый дорогостоящий имеет цену 2700 рублей.
Область «Фильтры» позволяет установить критерий отбора записей. Добавим поле «Дата поступление», просто поставив около него галочку.
Сейчас сводная таблица в Excel выглядит неудобно, если нам необходимо провести анализ по дате. Поэтому переместим дату из строк в фильтры — просто перетяните, как было указано выше.
Итогом этих действий стало появление еще одного поля сверху. Чтобы выбрать дату, нажмем на стрелочку около слова «Все».
Теперь нам доступен выбор конкретного дня, чтобы открыть список, щелкайте по треугольнику в правом углу.
Также можно выбрать и значения для отдела.
Снимите галочки с тех, которые вас не интересуют, и вы получите только нужную информацию.

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

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

Важным вопросом является то, как сделать и обновить сводную таблицу в Excel 2010 или другой версии. Это актуально тогда, когда вы собираетесь добавить новые данные. Если обновление будет проходить только для одного столбца, то необходимо на любом её месте щелкнуть правой кнопкой мыши. В появившемся окне нужно нажать «Обновить».
Если же подобное действие необходимо провести сразу с несколькими столбцами и строками, то выделите любую зону и на верхней панели откройте вкладку «Анализ» и кликните на значок «Обновить». Дальше выбирайте желаемое действие.
Если сводная таблица в Excel не нужна, то стоит разобраться, как её удалить. Это не составит большого труда. Выделите все составляющие вручную, или используя сочетание клавиш «CTRL+A». Далее нажмите клавишу «DELETE» и поле будет очищено.

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

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

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

Для этого нам понадобится мастер сводных таблиц. Добавим его на панель быстрого доступа (самый верх окна – слева). Нажмите выпадающую стрелочку и выберите «Другие команды».
Выберите все команды.
И найдите мастер сводных таблиц Excel, кликните по нему, затем на «Добавить» и ОК.
Значок появится сверху.
У вас должны быть две идентичные по полям таблицы на разных листах. У нас это данные о поступлениях в отделы за май и июнь. Нажимайте на ярлык мастера сводных таблиц и выбирайте консолидацию диапазонов.
Нам нужно несколько полей, а не одно.
На следующем этапе выделите первый диапазон и нажмите кнопку «Добавить». Затем переключитесь на другой лист ( щелкните по его названию внизу) и снова «Добавить». У вас будут созданы два диапазона.
Не стоит выделять всю таблицу целиком. Нам нужна информация о поступлениях в отделы, поэтому мы выделили диапазон, начиная со столбца «Отдел».
Дайте имя каждому. Кликайте кружочек 1, затем в поле вписывайте «май», кликайте кружочек 2 и вписывайте в поле 2 «июнь». Не забывайте менять диапазоны в области. Должен быть выделен тот, который именуем.
Щелкайте «Далее» и создавайте на новом листе.
После нажатия на «Готово» получим результат. Это многомерная таблица, так что управлять ей довольно сложно. Поэтому мы и выбрали диапазон меньше, чтобы не запутаться в измерениях.
Обратите внимание, что у нас уже нет четких названий полей. Их можно вытащить, нажав на пункты в верхней области.
Снимая или устанавливая галочки, вы регулируете значения, которые вам необходимо увидеть. Неудобно и то, что расчет проводится для всех значений одинаковый.
Как видите, у нас одно значение в соответствующей области.

Изменение структуры отчёта

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

Вам откроются на выбор три типа для структуризации информации:

  • Сжатая форма

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

  • Структурированная форма

Все показатели подаются иерархично: от малого к большому.

  • Табличная форма

Информация представляется под видом реестра. Это позволяет легко переносить ячейки на новые листы.

Остановив выбор на подходящем макете, вы закрепляете внесенные коррективы.

Итак, мы рассказали, как составить поля сводной таблицы MS Excel 2016 (в 2007, 2010 действуйте по аналогии). Надеемся, эта информация поможет вам осуществлять быстрый анализ консолидированных данных.

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

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

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

Вариант 1: Обычная сводная таблица

Мы будем рассматривать процесс создания на примере Microsoft Excel 2010, однако алгоритм применим и для других современных версий этого приложения.

  1. За основу возьмем таблицу выплат заработной платы работникам предприятия. В ней указаны имена работников, пол, категория, дата и сумма выплаты. То есть каждому эпизоду выплаты отдельному работнику соответствует отдельная строчка. Нам предстоит сгруппировать хаотично расположенные данные в этой таблице в одну сводную таблицу, при этом сведения будут браться только за третий квартал 2016 года. Посмотрим, как это сделать на конкретном примере.
  2. Прежде всего преобразуем исходную таблицу в динамическую. Это нужно для того, чтобы при добавлении строк и других данных они автоматически подтягивались в сводную таблицу. Наводим курсор на любую ячейку, затем в расположенном на ленте блоке «Стили» кликаем по кнопке «Форматировать как таблицу» и выбираем любой понравившийся стиль таблицы.
  3. Открывается диалоговое окно, которое нам предлагает указать координаты расположения таблицы. Впрочем, по умолчанию координаты, которые предлагает программа, и так охватывают всю таблицу. Так что нам остается только согласиться и нажать на «OK». Но пользователи должны знать, что при желании они тут могут изменить эти параметры.
  4. Таблица превращается в динамическую и автоматически растягивающуюся. Она также получает имя, которое при желании пользователь может изменить на любое удобное ему. Просмотреть или изменить имя можно на вкладке «Конструктор».
  5. Чтобы непосредственно начать создание, выбираем вкладку «Вставка». Здесь жмем на первую кнопку в ленте, которая так и называется «Сводная таблица». Откроется меню, где следует выбрать, что мы собираемся создавать: таблицу или диаграмму. В конце нажимаем «Сводная таблица».
  6. В новом окне нам опять надо выбрать диапазон или название таблицы. Как видим, программа уже сама подтянула имя нашей таблицы, так что тут ничего больше делать не нужно. В нижней части диалогового окна можно выбрать место, где будет создаваться сводная таблица: на новом листе (по умолчанию) или на этом же. Конечно, в большинстве случаев намного удобнее держать ее на отдельном листе.
  7. После этого на новом листе откроется форма создания сводной таблицы.
  8. В правой части окна расположен список полей, а ниже четыре области: названия строк, названия столбцов, значения, фильтр отчета. Просто перетаскиваем мышкой необходимые нам поля таблицы в соответствующие потребностям области. Тут не существует какого-либо четкого установленного правила, какие поля следует перемещать, ведь все зависит от таблицы-первоисточника и от конкретных задач, которые могут меняться.
  9. В конкретном случае мы переместили поля «Пол» и «Дата» в область «Фильтр отчета», «Категория персонала» — в «Названия столбцов», «Имя» — в «Название строк», «Сумма заработной платы» — в «Значения». Следует отметить, что все арифметические расчеты данных, подтянутых из другой таблицы, возможны только в последней области. Во время того, как мы проделывали такие манипуляции с переносом полей в области, соответственно изменялась и сама таблица в левой части окна.
  10. Получилась вот такая сводная таблица. Над ней отображаются фильтры по полу и дате.

Вариант 2: Мастер сводных таблиц

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

  1. Переходим в пункт меню «Файл» и кликаем на «Параметры».
  2. Заходим в раздел «Панель быстрого доступа» и выбираем команды из команд на ленте. В списке элементов ищем «Мастер сводных таблиц и диаграмм». Выделяем его, жмем на кнопку «Добавить», а потом «OK».
  3. В результате наших действий на «Панели быстрого доступа» появился новый значок. Кликаем по нему.
  4. После этого открывается «Мастер сводных таблиц». Есть четыре варианта источника данных, откуда будет формироваться сводная таблица, из которых указываем подходящий. Внизу следует выбрать, что мы собираемся создавать: сводную таблицу или диаграмму. Осуществляем выбор и идем «Далее».
  5. Появляется окно с диапазоном таблицы с данными, который при желании можно изменить. Нам этого делать не надо, поэтому просто переходим «Далее».
  6. Затем «Мастер сводных таблиц» предлагает выбрать место, где будет размещаться новый объект: на этом же листе или на новом. Делаем выбор и подтверждаем его кнопкой «Готово».
  7. Откроется новый лист в точности с такой же формой, которая была при обычном способе создания сводной таблицы.
  8. Все дальнейшие действия выполняются по тому же алгоритму, который был описан выше (см. Вариант 1).

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

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

  1. Для приведения таблицы к нужному виду кликаем на кнопку около фильтра «Дата». В нем устанавливаем галочку напротив надписи «Выделить несколько элементов». Далее снимаем галочки со всех дат, которые не вписываются в период третьего квартала. В нашем случае это всего лишь одна дата. Подтверждаем действие.
  2. Таким же образом мы можем воспользоваться фильтром по полу и выбрать для отчета, например, только одних мужчин.
  3. Сводная таблица приобрела такой вид.
  4. Для демонстрации того, что управлять информацией в таблице можно как угодно, снова открываем форму списка полей. Переходим на вкладку «Параметры», и щелкаем на «Список полей». Перемещаем поле «Дата» из области «Фильтр отчета» в «Название строк», а между полями «Категория персонала» и «Пол» производим обмен областями. Все операции выполняем с помощью простого перетягивания элементов.
  5. Теперь таблица выглядит совсем по-другому. Столбцы делятся по полам, в строках появилась разбивка по месяцам, а фильтрацию теперь можно осуществлять по категории персонала.
  6. Если же в списке полей название строк переместить и поставить выше дату, чем имя, тогда именно даты выплат будут подразделяться на имена сотрудников.
  7. Можно также отобразить числовые значения таблицы в виде гистограммы. Для этого выделяем ячейку с числовым значением, заходим на вкладку «Главная», жмем «Условное форматирование», выбираем пункт «Гистограммы» и указываем понравившийся вид.
  8. Гистограмма появляется только в одной ячейке. Чтобы применить правило гистограммы для всех ячеек таблицы, кликаем на кнопку, которая появилась рядом с гистограммой, и в открывшемся окне переводим переключатель в позицию «Ко всем ячейкам».
  9. В итоге наша сводная таблица стала выглядеть более презентабельно.

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

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

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

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

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

План обучения будет прост:

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

Ручной режим

Данный метод самый простой. Делается это следующим образом.

  1. Открыв пустой лист, вы увидите большое количество одинаковых ячеек.

  1. Выделите любое количество строк и столбцов.

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

  1. Сразу после этого у вас появится обычная элементарная табличка.

Теперь можете приступать к заполнению данных.

Существует и другой способ ручного рисования таблицы.

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

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

Автоматический режим

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

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

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

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

Дальше можете делать, что душе угодно.

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

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

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

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

Только после этого (иконка курсора изменит внешний вид) палец можно отпустить.

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

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

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

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

Иногда не получается правильно подобрать поля для столбцов и строк. И в итоге ничего путного не выходит. Для таких случаев разработчики Microsoft подготовили свои варианты анализа данных.

Работает это очень просто.

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

Готовые шаблоны в Excel 2016

Для особо ленивых данная программа позволяет создавать по-настоящему «крутые» таблицы всего одним кликом.

При запуске Экселя вам на выбор предлагаются следующие варианты:

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

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

Кликаем на какой-нибудь понравившийся вариант.

Нажимаем на кнопку «Создать».

В результате этого вы получаете готовый вариант очень большой и сложной таблицы.

Оформление

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

Рассмотрим вкратце основные манипуляции с ячейками.

Создание заголовка

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

  1. Сначала переходим на вкладку «Главная» и нажимаем на пункт меню «Вставить строки на лист».
  1. Выделяем появившуюся строчку и нажимаем на пункт меню «Объединить ячейки».
  1. Далее пишем любой заголовок.

Изменение высоты элементов

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

В результате этого высота строки будет больше.

Выравнивание текста

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

Кликаем на кнопку «По середине» и получаем желанный результат.

Теперь заголовок смотрится куда лучше.

Изменение стиля

Также рекомендуется изменить шрифт и увеличить кегль (размер по вертикали). Сделать это можно вручную при помощи панели инструментов.

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

Эффект будет очень красивым.

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

Для того чтобы изменить количество элементов в таблице, можно воспользоваться кнопкой «Вставить».

Вы можете добавить:

  • ячейки;
  • строки;
  • столбцы;
  • целый лист.

Удаление элементов

Уничтожить ячейку или что-нибудь еще можно точно так же. Для этого есть своя кнопка.

Заливка ячеек

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

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

Формат элементов

При желании можно сделать с таблицей что угодно. Для этого достаточно нажать на кнопку «Формат».

В результате этого вы сумеете:

  • вручную или автоматически изменить высоту строк;
  • вручную или автоматически изменить ширину столбцов;
  • скрыть или отобразить ячейки;
  • переименовать лист;
  • изменить цвет ярлыка;
  • защитить лист;
  • блокировать элемент;
  • указать формат ячеек.

Формат содержимого

Если нажать на последний из вышеописанных пунктов, то появится следующее:

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

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

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

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

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

Кроме этого, рекомендуется ознакомиться с описанием всех функций.

Рассмотрим самую простую операцию – умножение ячеек.

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

=C3*D3

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

Значения в колонке «Общая стоимость» будут зависеть от полей «Количество» и «Стоимость 1 кг». Это и есть прелесть динамики.

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

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

Использование графики

Иногда в ячейках вместо текста используют фотографии. Сделать это очень легко.

Выбираем пустой элемент. Переходим на вкладку «Вставка». Выбираем раздел «Иллюстрации». Кликаем на пункт «Рисунки».

  1. Указываем файл и кликаем на кнопку «Вставить».
  1. Результат вас не разочарует. Смотрится очень красиво (в зависимости от подобранного рисунка).

Экспорт в Word

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

  1. Выделите область данных.
  1. Нажмите на горячие клавиши Ctrl+C.
  2. Откройте документ
  3. Теперь используем кнопки Ctrl+V.
  4. Итог будет следующим.

Онлайн-сервисы

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

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

Способы печати

Распечатка документов Word, как правило, задача несложная. Но с таблицами в Excel всё иначе. Самая большая проблема заключается в том, что «на глаз» сложно определить границы печати. И очень часто в принтере появляются практически пустые листы, на которых находится всего 1-2 строки таблицы.

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

  1. Открываем документ. Он выглядит вполне обычно.
  1. Далее нажмите на горячие клавиши Ctrl+P. В появившемся окне мы видим, что информация не помещается на один лист. У нас исчезла графа «Общая стоимость». Кроме того, внизу нам подсказывают, что при печати будет использовано 2 страницы.

В версии 2007 года, для этого нужно было нажать на кнопку «Просмотр».

  1. Для отмены нажимаем горячую клавишу Esc. В результате появится вертикальная пунктирная линия, которая показывает границы печати.

Увеличить пространство при печати можно следующим образом.

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

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

  1. Снова нажимаем на Ctrl+P. Теперь мы видим, что информация помещается на один лист.

Отличие версий продукции Майкрософт

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

Пример рабочей области Excel 2003.

В современных 2007, 2010, 2013, а тем более 2016 версиях всё намного «круче».

Многие пункты меню находятся в разных разделах. Некоторые из них вовсе изменили своё название. Например, привычные нам «Формулы», в далёком 2003 назывались «Функциями». И они занимали не так уж много места.

Сейчас же для них отведена целая вкладка.

Ограничения и возможности разных версий

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

Пример самых основных параметров.

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

Обратите внимание, что версию 2003 года даже не рассматривают, так как её поддержка прекращена.

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

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

Как создать таблицу в Экселе пошагово

Как создать таблицу в Экселе пошагово

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

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

Эксель — ваш универсальный электронный помощник

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

При помощи Excel вы сможете выполнять сложные расчёты, не прибегая к программированию в большинстве случаев. Впрочем, возможность использовать дополнительный код тоже заложена — при помощи внутреннего языка программирования VBA (расшифровывается «Visual Basic for Application»).

Excel позволяет работать с тремя типами данных:

  • числа
  • текст
  • формулы

Рабочее поле Excel понятно организовано: все столбцы пронумерованы буквами латинского алфавита, а строки — цифрами.

Формулы позволяют автоматизировать подсчёты. Если, например, в ячейке А1 меняется значение, то во всех остальных ячейках, которые содержат формулы с обозначением ячейки А1, идёт автоматический пересчёт данных.

Примеры задач, которые могут быть решены функционалом Excel:

  • Однотипные сложные расчёты
  • Автоматизация вычислений
  • Наглядное представление данных в диаграммах
  • Статистический анализ
  • Подготовка таблиц и диаграмм к печати
  • Создание отчётов
  • Решение задач на оптимизацию

Работать на компьютере или в «облаке»? Эксель и Гугл Таблицы

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

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

Есть возможность упростить взаимодействие участников команды при коллективной работе с данными. В Excel это решается при помощи сетевого доступа. Однако проще использовать Гугл Таблицы (или Google Spreadsheets).

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

Функционал Гугл Таблиц практически не отличается от Excel. К тому же им можно пользоваться бесплатно, что имеет важное значение для компаний.

Минусом Гугл Таблиц может явиться необходимость постоянного работающего интернета под рукой. Но кого этот «недостаток» остановит во время всеобщей доступности Сети?

Преимущества работы с таблицами в Экселе

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

Каждой таблице в документе присваиваются название и свойства.

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

В чём преимущества работы с Таблицами?

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

Как создать таблицу в Экселе пошагово за 3 клика

Этот и все дальнейшие примеры рассмотрены для версии MS Excel 2016.

Запустите программу или откройте нужный вам документ Excel при помощи функции: Файл → Открыть.

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

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

Диапазон с данными преобразован в Таблицу.

Как подвести итог в таблице

Активизируйте любую из ячеек в Таблице, кликнув на неё мышью.

Выполните следующую команду: Верхнее меню → Работа с таблицами → Строка итогов (нужно поставить галочку).

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

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

Как ввести автоматическую нумерацию строк в таблице

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

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

Поставьте курсор в любую ячейку Таблицы в столбце для нумерации. Разместите в ней текст: =СТРОКА ()-СТРОКА (ВашаТаблица)+1. Функция автоматически возвратит всем ячейкам столбца нумерацию по порядку.

Как создать «срез» по строкам таблицы

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

Excel создаст в рабочей области листа окно (одно или несколько) и присвоит ему название.

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

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

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

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

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

У нас есть таблица, куда занесены следующие данные: артикул, наименование товара, ID магазина, дата продажи, количество, цена.

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

Задача

  • На какую сумму продано товара за весь период в магазине с ID #11?

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

На новом листе щёлкните на окне сводной таблицы. Справа появится меню.

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

Ответ: 6230.

Если вы хотите скрыть ненужные строки и оставить, например, только итог по ID #11, проделайте следующее.

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

Умные таблицы в Экселе — 2 секрета эффективной работы

Секрет 1.

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

Верхнее меню → Работа с таблицами → Параметры стилей таблиц.

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

Секрет 2.

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

Разведение данных по разным документам (их ещё называют «книги») помогает избежать неразберихи и неумеренного роста размера файлов.

Для связывания данных на разных листах в пределах одного документа служит функция Лист.

Для связывания данных нескольких документов используйте команду Параметры вставки → Вставить связь.

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

Понравилась статья «Как создать таблицу в Экселе пошагово»? Оставьте ваш комментарий и поделитесь статьёй с друзьями.

Удалённая профессия Специалист по автоворонкам

Автоворонки — мощный канал для увеличения аудитории, интернет-продаж и автоматизации бизнеса. Более 2 млн. предпринимателей по всему миру уже внедрили автоворонки в свой бизнес. За 2 года количество запросов «автоворонки для бизнеса» увеличилось в 6,0 раз. Специалист по автоворонкам — это востребованная и высокооплачиваемая профессия.

Хотите получить востребованную интернет-профессию, работать и жить где угодно, покончить с финансовым рабством, уделять больше времени родным и друзьям и путешествовать с семьёй? Приходите на курс «Инфомаркетинг и автоматические воронки продаж»! Получить востребованную профессию.

Ещё больше интересных статей здесь: https://seostayer.ru/

До новых встреч!

Публикую очередной кроссворд! Попробуйте разгадать. 🙂 Правила .

Вам может быть интересно:

  • Работа в Китае учителем английского языка. На что обратить внимание при заключении рабочего контракта
  • Яндекс Деньги: что это за сервис и как им пользоваться, преимущества и недостатки системы
  • Самостоятельная поездка в Китай: что нужно знать, что взять с собой из России
  • Как развести огонь без спичек, переправиться через реку, построить укрытие для ночлега и развести нодью. Курс выживания в тайге
  • Интернет-лазутчики, China Mobile, Золотой Щит, VPN или как пользоваться интернетом в Китае