#Руководства
- 13 май 2022
-
0
Как систематизировать тысячи строк и преобразовать их в наглядный отчёт за несколько минут? Разбираемся на примере с квартальными продажами автосалона
Иллюстрация: Meery Mary для Skillbox Media
Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.
Сводная таблица — инструмент для анализа данных в Excel. Она собирает информацию из обычных таблиц, обрабатывает её, группирует в блоки, проводит необходимые вычисления и показывает итог в виде наглядного отчёта. При этом все параметры этого отчёта пользователь может настроить под себя и свои потребности.
Разберёмся, для чего нужны сводные таблицы. На конкретном примере покажем, как их создать, настроить и использовать. В конце расскажем, можно ли делать сводные таблицы в «Google Таблицах».
Сводные таблицы удобно применять, когда нужно сформировать отчёт на основе большого объёма информации. Они суммируют значения, расположенные не по порядку, группируют данные из разных участков исходной таблицы в одном месте и сами проводят дополнительные расчёты.
Вид сводной таблицы можно настраивать под себя самостоятельно парой кликов мыши — менять расположение строк и столбцов, фильтровать итоги и переносить блоки отчёта с одного места в другое для лучшей наглядности.
Разберём на примере. Представьте небольшой автосалон, в котором работают три менеджера по продажам. В течение квартала данные об их продажах собирались в обычную таблицу: модель автомобиля, его характеристики, цена, дата продажи и ФИО продавца.
Скриншот: Skillbox Media
В конце квартала планируется выдача премий. Нужно проанализировать, кто принёс больше прибыли салону. Для этого нужно сгруппировать все проданные автомобили под каждым менеджером, рассчитать суммы продаж и определить итоговый процент продаж за квартал.
Разберёмся пошагово, как это сделать с помощью сводной таблицы.
Создаём сводную таблицу
Чтобы сводная таблица сработала корректно, важно соблюсти несколько требований к исходной:
- у каждого столбца исходной таблицы есть заголовок;
- в каждом столбце применяется только один формат — текст, число, дата;
- нет пустых ячеек и строк.
Теперь переходим во вкладку «Вставка» и нажимаем на кнопку «Сводная таблица».
Скриншот: Skillbox Media
Появляется диалоговое окно. В нём нужно заполнить два значения:
- диапазон исходной таблицы, чтобы сводная могла забрать оттуда все данные;
- лист, куда она перенесёт эти данные для дальнейшей обработки.
В нашем случае выделяем весь диапазон таблицы продаж вместе с шапкой. И выбираем «Новый лист» для размещения сводной таблицы — так будет проще перемещаться между исходными данными и сводным отчётом. Жмём «Ок».
Скриншот: Skillbox Media
Excel создал новый лист. Для удобства можно сразу переименовать его.
Слева на листе расположена область, где появится сводная таблица после настроек. Справа — панель «Поля сводной таблицы», в которые мы будем эти настройки вносить. В следующем шаге разберёмся, как пользоваться этой панелью.
Скриншот: Skillbox Media
Настраиваем сводную таблицу и получаем результат
В верхней части панели настроек находится блок с перечнем возможных полей сводной таблицы. Поля взяты из заголовков столбцов исходной таблицы: в нашем случае это «Марка, модель», «Цвет», «Год выпуска», «Объём», «Цена», «Дата продажи», «Продавец».
Нижняя часть панели настроек состоит из четырёх областей — «Значения», «Строки», «Столбцы» и «Фильтры». У каждой области своя функция:
- «Значения» — проводит вычисления на основе выбранных данных из исходной таблицы и относит результаты в сводную таблицу. По умолчанию Excel суммирует выбранные данные, но можно выбрать другие действия. Например, рассчитать среднее, показать минимум или максимум, перемножить.
Если данные выбранного поля в числовом формате, программа просуммирует их значения (например, рассчитает общую стоимость проданных автомобилей). Если формат данных текстовый — программа покажет количество ячеек (например, определит количество проданных авто).
- «Строки» и «Столбцы» — отвечают за визуальное расположение полей в сводной таблице. Если выбрать строки, то поля разместятся построчно. Если выбрать столбцы — поля разместятся по столбцам.
- «Фильтры» — отвечают за фильтрацию итоговых данных в сводной таблице. После построения сводной таблицы панель фильтров появляется отдельно от неё. В ней можно выбрать, какие данные нужно показать в сводной таблице, а какие — скрыть. Например, можно показывать продажи только одного из менеджеров или только за выбранный период.
Настроить сводную таблицу можно двумя способами:
- Поставить галочку напротив нужного поля — тогда Excel сам решит, где нужно разместить это значение в сводной таблице, и сразу заберёт его туда.
- Выбрать необходимые для сводной таблицы поля из перечня и перетянуть их в нужную область вручную.
Первый вариант не самый удачный: Excel редко ставит данные так, чтобы с ними было удобно работать, поэтому сводная таблица получается неинформативной. Остановимся на втором варианте — он предполагает индивидуальные настройки для каждого отчёта.
В случае с нашим примером нужно, чтобы сводная таблица отразила ФИО менеджеров по продаже, проданные автомобили и их цены. Остальные поля — технические характеристики авто и дату продажи — можно будет использовать для фильтрации.
Таблица получится наглядной, если фамилии менеджеров мы расположим построчно. Находим в верхней части панели поле «Продавец», зажимаем его мышкой и перетягиваем в область «Строки».
После этого в левой части листа появится первый блок сводной таблицы: фамилии менеджеров по продажам.
Скриншот: Skillbox
Теперь добавим модели автомобилей, которые эти менеджеры продали. По такому же принципу перетянем поле «Марка, модель» в область «Строки».
В левую часть листа добавился второй блок. При этом сводная таблица сама сгруппировала все автомобили по менеджерам, которые их продали.
Скриншот: Skillbox Media
Определяем, какая ещё информация понадобится для отчётности. В нашем случае — цены проданных автомобилей и их количество.
Чтобы сводная таблица самостоятельно суммировала эти значения, перетащим поля «Марка, модель» и «Цена» в область «Значения».
Скриншот: Skillbox Media
Теперь мы видим, какие автомобили продал каждый менеджер, сколько и по какой цене, — сводная таблица самостоятельно сгруппировала всю эту информацию. Более того, напротив фамилий менеджеров можно посмотреть, сколько всего автомобилей они продали за квартал и сколько денег принесли автосалону.
По такому же принципу можно добавлять другие поля в необходимые области и удалять их оттуда — любой срез информации настроится автоматически. В нашем примере внесённых данных в сводной таблице будет достаточно. Ниже рассмотрим, как настроить фильтры для неё.
Настраиваем фильтры сводной таблицы
Чтобы можно было фильтровать информацию сводной таблицы, нужно перенести требуемые поля в область «Фильтры».
В нашем примере перетянем туда все поля, не вошедшие в основной состав сводной таблицы: объём, дату продажи, год выпуска и цвет.
Скриншот: Skillbox Media
Для примера отфильтруем данные по году выпуска: настроим фильтр так, чтобы сводная таблица показала только проданные авто 2017 года.
В блоке фильтров нажмём на стрелку справа от поля «Год выпуска»:
Скриншот: Skillbox Media
В появившемся окне уберём галочку напротив параметра «Выделить все» и поставим её напротив параметра «2017». Закроем окно.
Скриншот: Skillbox Media
Теперь сводная таблица показывает только автомобили 2017 года выпуска, которые менеджеры продали за квартал. Чтобы снова показать таблицу в полном объёме, нужно в том же блоке очистить установленный фильтр.
Скриншот: Skillbox Media
Фильтры можно выбирать и удалять как удобно — в зависимости от того, какую информацию вы хотите увидеть в сводной таблице.
Проводим дополнительные вычисления
Сейчас в нашей сводной таблице все продажи менеджеров отображаются в рублях. Предположим, нам нужно понять, каков процент продаж каждого продавца в общем объёме. Можно рассчитать это вручную, а можно воспользоваться дополнениями сводных таблиц.
Кликнем правой кнопкой на любое значение цены в таблице. Выберем параметр «Дополнительные вычисления», затем «% от общей суммы».
Скриншот: Skillbox
Теперь вместо цен автомобилей в рублях отображаются проценты: какой процент каждый проданный автомобиль составил от общей суммы продаж всего автосалона за квартал. Проценты напротив фамилий менеджеров — их общий процент продаж в этом квартале.
Скриншот: Skillbox Media
Можно свернуть подробности с перечнями автомобилей, кликнув на знак – слева от фамилии менеджера. Тогда таблица станет короче, а данные, за которыми мы шли, — кто из менеджеров поработал лучше в этом квартале, — будут сразу перед глазами.
Скриншот: Skillbox Media
Чтобы снова раскрыть данные об автомобилях — нажимаем +.
Чтобы значения снова выражались в рублях — через правый клик мыши возвращаемся в «Дополнительные вычисления» и выбираем «Без вычислений».
Обновляем данные сводной таблицы
Предположим, в исходную таблицу внесли ещё две продажи последнего дня квартала.
Скриншот: Skillbox
В сводную таблицу эти данные самостоятельно не добавятся — изменился диапазон исходной таблицы. Поэтому нужно поменять первоначальные параметры.
Переходим на лист сводной таблицы. Во вкладке «Анализ сводной таблицы» нажимаем кнопку «Изменить источник данных».
Скриншот: Skillbox Media
Кнопка переносит нас на лист исходной таблицы, где нужно выбрать новый диапазон. Добавляем в него две новые строки и жмём «ОК».
Скриншот: Skillbox Media
После этого данные в сводной таблице меняются автоматически: у менеджера Трегубова М. вместо восьми продаж становится десять.
Скриншот: Skillbox Media
Когда в исходной таблице нужно изменить информацию в рамках текущего диапазона, данные в сводной таблице автоматически не изменятся. Нужно будет обновить их вручную.
Например, поменяем цены двух автомобилей в таблице с продажами.
Скриншот: Skillbox Media
Чтобы данные сводной таблицы тоже обновились, переходим на её лист и во вкладке «Анализ сводной таблицы» нажимаем кнопку «Обновить».
Теперь у менеджера Соколова П. изменились данные в столбце «Цена, руб.».
Скриншот: Skillbox Media
Как использовать сводные таблицы в «Google Таблицах»? Нужно перейти во вкладку «Вставка» и выбрать параметр «Создать сводную таблицу». Дальнейший ход действий такой же, как и в Excel: выбрать диапазон таблицы и лист, на котором её нужно построить; затем перейти на этот лист и в окне «Редактор сводной таблицы» указать все требуемые настройки. Результат примет такой вид:
Скриншот: Skillbox Media
Научитесь: Excel + Google Таблицы с нуля до PRO
Узнать больше
Создание отчетов при помощи сводных таблиц
Видео
Лирическое вступление или мотивация
Представьте себя в роли руководителя отдела продаж. У Вашей компании есть два склада, с которых вы отгружаете заказчикам, допустим, овощи-фрукты. Для учета проданного в Excel заполняется вот такая таблица:
В ней каждая отдельная строка содержит полную информацию об одной отгрузке (сделке, партии):
- кто из наших менеджеров заключил сделку
- с каким из заказчиков
- какого именно товара и на какую сумму продано
- с какого из наших складов была отгрузка
- когда (месяц и день месяца)
Естественно, если менеджеры по продажам знают свое дело и пашут всерьез, то каждый день к этой таблице будет дописываться несколько десятков строк и к концу, например, года или хотя бы квартала размеры таблицы станут ужасающими. Однако еще больший ужас вызовет у Вас необходимость создания отчетов по этим данным. Например:
- Сколько и каких товаров продали в каждом месяце? Какова сезонность продаж?
- Кто из менеджеров сколько заказов заключил и на какую сумму? Кому из менеджеров сколько премиальных полагается?
- Кто входит в пятерку наших самых крупных заказчиков?
… и т.д.
Ответы на все вышеперечисленные и многие аналогичные вопросы можно получить легче, чем Вы думаете. Нам потребуется один из самых ошеломляющих инструментов Microsof Excel – сводные таблицы.
Поехали…
Если у вас Excel 2003 или старше
Ставим активную ячейку в таблицу с данными (в любое место списка) и жмем в меню Данные – Сводная таблица (Data – PivotTable and PivotChartReport). Запускается трехшаговый Мастер сводных таблиц (Pivot Table Wizard). Пройдем по его шагам с помощью кнопок Далее (Next) и Назад (Back) и в конце получим желаемое.
Шаг 1. Откуда данные и что надо на выходе?
На этом шаге необходимо выбрать откуда будут взяты данные для сводной таблицы. В нашем с Вами случае думать нечего – “в списке или базе данных Microsoft Excel”. Но. В принципе, данные можно загружать из внешнего источника (например, корпоративной базы данных на SQL или Oracle). Причем Excel “понимает” практически все существующие типы баз данных, поэтому с совместимостью больших проблем скорее всего не будет. Вариант В нескольких диапазонах консолидации (Multiple consolidation ranges) применяется, когда список, по которому строится сводная таблица, разбит на несколько подтаблиц, и их надо сначала объединить (консолидировать) в одно целое. Четвертый вариант “в другой сводной таблице…” нужен только для того, чтобы строить несколько различных отчетов по одному списку и не загружать при этом список в оперативную память каждый раз.
Вид отчета – на Ваш вкус – только таблица или таблица сразу с диаграммой.
Шаг 2. Выделите исходные данные, если нужно
На втором шаге необходимо выделить диапазон с данными, но, скорее всего, даже этой простой операции делать не придется – как правило Excel делает это сам.
Шаг 3. Куда поместить сводную таблицу?
На третьем последнем шаге нужно только выбрать местоположение для будущей сводной таблицы. Лучше для этого выбирать отдельный лист – тогда нет риска что сводная таблица “перехлестнется” с исходным списком и мы получим кучу циклических ссылок. Жмем кнопку Готово (Finish) и переходим к самому интересному – этапу конструирования нашего отчета.
Работа с макетом
То, что Вы увидите далее, называется макетом (layout) сводной таблицы. Работать с ним несложно – надо перетаскивать мышью названия столбцов (полей) из окна Списка полей сводной таблицы (Pivot Table Field List) в области строк (Rows), столбцов (Columns), страниц (Pages) и данных (Data Items) макета. Единственный нюанс – делайте это поточнее, не промахнитесь! В процессе перетаскивания сводная таблица у Вас на глазах начнет менять вид, отображая те данные, которые Вам необходимы. Перебросив все пять нужных нам полей из списка, Вы должны получить практически готовый отчет.
Останется его только достойно отформатировать:
Если у вас Excel 2007 или новее
В последних версиях Microsoft Excel 2007-2010 процедура построения сводной таблицы заметно упростилась. Поставьте активную ячейку в таблицу с исходными данными и нажмите кнопку Сводная таблица (Pivot Table) на вкладке Вставка (Insert). Вместо 3-х шагового Мастера из прошлых версий отобразится одно компактное окно с теми же настройками:
В нем, также как и ранее, нужно выбрать источник данных и место вывода сводной таблицы, нажать ОК и перейти к редактированию макета. Теперь это делать значительно проще, т.к. можно переносить поля не на лист, а в нижнюю часть окна Список полей сводной таблицы, где представлены области:
- Названия строк (Row labels)
- Названия столбцов (Column labels)
- Значения (Values) – раньше это была область элементов данных – тут происходят вычисления.
- Фильтр отчета (Report Filter) – раньше она называлась Страницы (Pages), смысл тот же.
Перетаскивать поля в эти области можно в любой последовательности, риск промахнуться (в отличие от прошлых версий) – минимален.
P.S.
Единственный относительный недостаток сводных таблиц – отсутствие автоматического обновления (пересчета) при изменении данных в исходном списке. Для выполнения такого пересчета необходимо щелкнуть по сводной таблице правой кнопкой мыши и выбрать в контекстном меню команду Обновить (Refresh).
Ссылки по теме
- Настройка вычислений в сводных таблицах
- Группировка дат и чисел с нужным шагом в сводных таблицах
- Сводная таблица по нескольким диапазонам с разных листов
Для анализа больших и сложных таблиц обычно используют
Сводные таблицы
. С помощью формул также можно осуществить группировку и анализ имеющихся данных. Создадим несложные отчеты с помощью формул.
В качестве исходной будем использовать
таблицу в формате EXCEL 2007
(
), содержащую информацию о продажах партий продуктов. В строках таблицы приведены данные о поставке партии продукта и его сбыте. Аналогичная таблица использовалась в статье
Сводные таблицы
.
В таблице имеются столбцы:
Товар
– наименование партии товара, например, «
Апельсины
»;
Группа
– группа товара, например, «
Апельсины
» входят в группу «
Фрукты
»;
Дата поставки
– Дата поставки Товара Поставщиком;
Регион продажи
– Регион, в котором была реализована партия Товара;
Продажи
– Стоимость, по которой удалось реализовать партию Товара;
Сбыт
– срок фактической реализации Товара в Регионе (в днях);
Прибыль
– отметка о том, была ли получена прибыль от реализованной партии Товара.
Через
Диспетчер имен
откорректируем
имя
таблицы на «
Исходная_таблица
» (см.
файл примера
).
С помощью формул создадим 5 несложных отчетов, которые разместим на отдельных листах.
Отчет №1 Суммарные продажи Товаров
Найдем суммарные продажи каждого Товара. Задача решается достаточно просто с помощью функции
СУММЕСЛИ()
, однако само построение отчета требует определенных навыков работы с некоторыми средствами EXCEL.
Итак, приступим. Для начала нам необходимо сформировать перечень названий Товаров. Т.к. в столбце Товар исходной таблицы названия повторяются, то нам нужно из него выбрать только
уникальные
значения. Это можно сделать несколькими способами: формулами (см. статью
Отбор уникальных значений
), через меню
или с помощью
Расширенного фильтра
. Если воспользоваться первым способом, то при добавлении новых Товаров в исходную таблицу, новые названия будут включаться в список автоматически. Но, здесь для простоты воспользуемся вторым способом. Для этого:
- Перейдите на лист с исходной таблицей;
-
Вызовите
Расширенный фильтр
(
);
-
Заполните поля как показано на рисунке ниже: переключатель установите в позицию
Скопировать результат в другое место
; в поле Исходный диапазон введите $A$4:$A$530; Поставьте флажок
Только уникальные записи
.
- Скопируйте полученный список на лист, в котором будет размещен отчет;
-
Отсортируйте перечень товаров (
).
Должен получиться следующий список.
В ячейке
B6
введем нижеследующую формулу, затем скопируем ее
Маркером заполнения
вниз до конца списка:
=СУММЕСЛИ(Исходная_Таблица[Товар];A6;Исходная_Таблица[Продажи])
Для того, чтобы понять сруктурированные ссылки на поля в
таблицах в формате EXCEL 2007
можно почитать Справку EXCEL (клавиша
F1
) в разделе
Основные сведения о листах и таблицах Excel > Использование таблиц Excel
.
Также можно легко подсчитать количество партий каждого Товара:
=СЧЁТЕСЛИ(Исходная_Таблица[Товар];A6)
Отчет №2 Продажи Товаров по Регионам
Найдем суммарные продажи каждого Товара в Регионах. Воспользуемся перечнем Товаров, созданного для Отчета №1. Аналогичным образом получим перечень названий Регионов (в поле Исходный диапазон
Расширенного фильтра
введите $D$4:$D$530). Скопируйте полученный вертикальный диапазон в
Буфер обмена
и
транспонируйте
его в горизонтальный. Полученный диапазон, содержащий названия Регионов, разместите в заголовке отчета.
В ячейке
B
8
введем нижеследующую формулу:
=СУММЕСЛИМН(Исходная_Таблица[Продажи]; Исходная_Таблица[Товар];$A8; Исходная_Таблица[Регион продажи];B$7)
Формула вернет суммарные продажи Товара, название которого размещено в ячейке
А8
, в Регионе из ячейки
В7
. Обратите внимание на использование
смешанной адресации
(ссылки $A8 и B$7), она понадобится при копировании формулы для остальных незаполненных ячеек таблицы.
Скопировать вышеуказанную формулу в ячейки справа с помощью
Маркера заполнения
не получится (это было сделано для Отчета №1), т.к. в этом случае в ячейке
С8
формула будет выглядеть так:
=СУММЕСЛИМН(Исходная_Таблица[Сбыт, дней]; Исходная_Таблица[Группа];$A8; Исходная_Таблица[Продажи];C$7)
Ссылки, согласно правил
относительной адресации
, теперь стали указывать на другие столбцы исходной таблицы (на те, что правее), что, естественно, не правильно. Обойти это можно, скопировав формулу из ячейки
B8
, в
Буфер обмена
, затем вставить ее в диапазон
С8:
G
8
, нажав
CTRL
+
V
. В ячейки ниже формулу можно скопировать
Маркером заполнения
.
Отчет №3 Фильтрация Товаров по прибыльности
Вернемся к исходной таблице. Каждая партия Товара либо принесла прибыль, либо не принесла (см. столбец Прибыль в исходной таблице). Подсчитаем продажи по Группам Товаров в зависимости от прибыльности. Для этого будем фильтровать с помощью формул записи исходной таблицы по полю Прибыль.
Создадим
Выпадающий (раскрывающийся) список
на основе
Проверки данных
со следующими значениями:
(Все); Да; Нет
. Если будет выбрано значение фильтра
(Все)
, то при расчете продаж будут учтены все записи исходной таблицы. Если будет выбрано значение фильтра «
Да»
, то будут учтены только прибыльные партии Товаров, если будет выбрано «
Нет»
, то только убыточные.
Суммарные продажи подсчитаем следующей
формулой массива
:
=СУММПРОИЗВ((Исходная_Таблица[Группа]=A8)* ЕСЛИ($B$5=”(Все)”;1;(Исходная_Таблица[Прибыль]=$B$5))* Исходная_Таблица[Продажи])
После ввода формулы не забудьте вместо простого нажатия клавиши
ENTER
нажать
CTRL
+
SHIFT
+
ENTER
.
Количество партий по каждой группе Товара, в зависимости от прибыльности, можно подсчитать аналогичной формулой.
=СУММПРОИЗВ((Исходная_Таблица[Группа]=A8)* ЕСЛИ($B$5=”(Все)”;1;(Исходная_Таблица[Прибыль]=$B$5)))
Так будет выглядеть отчет о продажах по Группам Товаров, принесших прибыль.
Выбрав в фильтре значение
Нет
(в ячейке
B
5
), сразу же получим отчет о продажах по Группам Товаров, принесших убытки.
Отчет №4 Статистика сроков сбыта Товаров
Вернемся к исходной таблице. Каждая партия Товара сбывалась определенное количество дней (см. столбец Сбыт в исходной таблице). Необходимо подготовить отчет о количестве партий, которые удалось сбыть за за период от 1 до 10 дней, 11-20 дней; 21-30 и т.д.
Вышеуказанные диапазоны сформируем нехитрыми формулами в столбце
B
.
Количество партий, сбытые за определенный период времени, будем подсчитывать с помощью формулы
ЧАСТОТА()
, которую нужно ввести как
формулу массива
:
=ЧАСТОТА(Исходная_Таблица[Сбыт, дней];A7:A12)
Для ввода формулы выделите диапазон
С6:С12
, затем в
Строке формул
введите вышеуказанную формулу и нажмите
CTRL
+
SHIFT
+
ENTER
.
Этот же результат можно получить с помощью обычной функции
СУММПРОИЗВ()
:
=СУММПРОИЗВ((Исходная_Таблица[Сбыт, дней]>A6)* (Исходная_Таблица[Сбыт, дней]<=A7))
Отчет №5 Статистика поставок Товаров
Теперь подготовим отчет о поставках Товаров за месяц. Сначала создадим перечень месяцев по годам. В исходной таблице самая ранняя дата поставки 11.07.2009. Вычислить ее можно с помощью формулы:
=МИН(Исходная_Таблица[Дата поставки])
Создадим перечень дат –
первых дней месяцев
, начиная с самой ранней даты поставки. Для этого воспользуемся формулой:
=КОНМЕСЯЦА($C$5;-1)+1
В результате получим перечень дат – первых дней месяцев:
Применив соответствующий формат ячеек, изменим отображение дат:
Формула для подсчета количества поставленных партий Товаров за месяц:
=СУММПРОИЗВ((Исходная_Таблица[Дата поставки]>=B9)* (Исходная_Таблица[Дата поставки]
Теперь добавим строки для подсчета суммарного количества партий по каждому году. Для этого немного изменим таблицу, выделив в отдельный столбец год, в который осуществлялась поставка, с помощью функции
ГОД()
.
Теперь для вывода
промежуточных итогов
по годам создадим структуру через пункт меню
:
- Выделите любую ячейку модифицированной таблицы;
-
Вызовите окно
Промежуточные итоги
через пункт меню
;
- Заполните поля как показано на рисунке:
После нажатия ОК, таблица будет изменена следующим образом:
Будут созданы промежуточные итоги по годам. Нажатием маленьких кнопочек в левом верхнем углу листа можно управлять отображением данных в таблице.
Резюме
:
Отчеты, аналогичные созданным, можно сделать, естественно, с помощью
Сводных таблиц
или с применением
Фильтра
к исходной таблице или с помощью других функций
БДСУММ()
,
БИЗВЛЕЧЬ()
,
БСЧЁТ()
и др. Выбор подхода зависит конкретной ситуации.
Сводные таблицы Excel – тема очень интересная и обширная.
В этой заметке я расскажу все, что вам нужно знать, для того чтобы начать применять сводные таблицы (в английском варианте – pivot table) в своей работе.
Сводные таблицы предоставляют очень широкие возможности для формирования нужных вам отчетов на основе каких-либо данных. При этом отчеты на базе сводных таблиц создаются буквально в несколько щелчков мыши и не требуют от пользователя создания сложнейших формул для группировки или суммирования необходимых данных.
Кроме этого, данные сводных таблиц очень просто визуализировать с помощью диаграмм или графиков, что с успехом применяется при создании так называемых дашбордов, которые сейчас очень широко применяются при анализе данных.
Постановка задачи
Итак, сводные таблицы могут быть применены к абсолютно любым данным, но чаще всего Excel применяют для анализа различных финансовых показателей компаний или проектов, поэтому давайте рассмотрим следующий пример (скачать файл).
Допустим вы трудитесь в компании, которая является поставщиком овощей и фруктов в сетевые супермаркеты, которые находятся в нескольких крупных городах страны.
По каждой поставке в базе есть информация, которую можно выгрузить в приблизительно вот такую таблицу:
Здесь в каждой строке мы видим дату заказа, название сети супермаркетов, город, в который осуществлялась поставка, категорию товара, его наименование, цену на дату поставки, количество заказов и итоговую сумму сделки.
Информации может быть намного больше. Для упрощения задачи я взял лишь минимальный набор данных. Тем не менее данных очень много и таблица состоит из нескольких тысяч строк.
Любую компанию в первую очередь интересует прибыль и поэтому может потребоваться найти ответы на ряд вопросов, например:
- Определить, в каком из городов за прошедшее время выручка была максимальной.
- Какая торговая сеть позволила получить компании наибольшую выручку.
- Определить категорию товаров и конкретный товар, принесшие наибольшую выручку.
В таблице представлены данные за два года, поэтому приблизительно такие же вопросы могут возникнуть у руководства компании по отношению к какому-то конкретному временному интервалу, например, какой товар был наиболее востребованным прошлым летом, или в прошлом году, какова была динамика продаж товаров разных категорий в течении года (по месяцам, кварталам) или какой из заказчиков в прошлом месяце был для компании наиболее значимым. Также часто возникает необходимость определить лидеров продаж, например составить ТОП5 товаров, на которые был наибольший спрос в определенное время.
Решение задачи формулами
На первый взгляд все эти задачи легко решаются несложными формулами и стандартными функциями.
Так, например, для определения города, принесшего максимальную выручку, нужно лишь сложить сумму всех поставок по каждому из городов. Сделать это можно с помощью функции СУММЕСЛИ.
То есть нам нужно будет создать отдельную таблицу, в которую с помощью функции СУММЕСЛИ свести данные по каждому из городов.
Для начала нужно создать список уникальных значений. Для этого скопируем значение столбца с названиями городов (столбец С) и вставим их на новый лист. Далее с помощью удаления дубликатов оставим лишь уникальные значения.
Ну а теперь применим функцию СУММЕСЛИ.
Сначала укажем диапазон значений, в котором будем искать условие (это столбец с городами в исходной таблице), а затем зададим само условие. Нам нужно, чтобы в этой строке суммировались итоги по конкретному городу, поэтому указываем ячейку с его названием в новой таблице. Ну а теперь задаем диапазон, значения которого нужно суммировать в случае выполнения условия – это столбец с итогами.
Получаем выручку, полученную в конкретном городе. Растягиваем формулу на весь диапазон новой таблицы и получаем результат.
В итоге по значениям суммарной выручки мы легко определим победителя и ответим на первый вопрос.
Для решения остальных задач нужно будет также создавать отдельные таблицы и с помощью формул, которые могут быть довольно сложными, рассчитывать значения.
Подобный подход к формированию нужных отчетов весьма трудоемкий и требует не только времени на создание отчета, но и внимательности со стороны пользователя, ведь допустить ошибку в формуле при обработке огромного массива данных довольно просто. Ну а об аппетитах начальства рассказывать и вовсе нет смысла. Как только на стол ляжет ответ на первый вопрос сразу же появятся дополнительные и придется вновь корпеть над формулами и стягивать нужные данные в компактную табличку…
Это не наш метод, тем более что сводные таблицы позволяют сделать ровно тоже самое, но в разы быстрее.
Ответим на первый вопрос с помощью сводной таблицы.
Создание сводной таблицы в Excel
Сводная таблица всегда строится на основе некоторого массива данных, который должен иметь строку заголовков.
В моем примере мы имеем простой диапазон значений, но первая строка диапазона содержит заголовки столбцов, а значит и такой диапазон подойдёт для создания сводной таблицы.
Установим табличный курсор в любую ячейку диапазона и на вкладке Вставка выберем Сводная таблица.
Excel автоматически выберет весь неразрывный диапазон значений и появится окно создания сводной таблицы, где будет указана абсолютная ссылка на этот диапазон.
Абсолютным и относительным ссылкам уже было посвящено отдельное подробное видео, поэтому не буду на этом останавливаться. Упомяну лишь, что в случае использования такого фиксированного диапазона в качестве источника данных сводной таблицы, мы в итоге не сможем добавлять в нее новую информацию. Точнее сказать, если в исходной таблице появятся новые строки, то для того, чтобы информация из них появилась в сводной таблице нам придется вручную корректировать в ее настройках этот диапазон.
Фактически данную проблему полностью решают умные таблицы. Поэтому перед тем, как создать новую сводную таблицу стоит преобразовать исходные данные в умную таблицу.
Делается это очень просто – также устанавливаем табличный курсор в любую ячейку диапазона и либо на вкладке Вставка выбираем Таблица, либо просто нажимаем сочетание клавиш Ctrl+T.
Так как строка с заголовками уже присутствует в диапазоне, то соответствующую галочку не убираем.
Ну и также, как и раньше создадим сводную таблицу, но уже на основе умной таблицы.
Теперь в источнике данных уже указан не фиксированный диапазон на листе, а отдельный объект Таблица1. Это нам гарантирует, что новые данные будут автоматически добавляться в сводную таблицу при ее обновлении.
Вставим сводную таблицу на новый лист.
На новом листе появится подсказка, подсказывающая нам, что это лист сводной таблицы, а также в правой части окна появится панель инструментов, позволяющая сконструировать нужный нам отчет.
Эту панель инструментов условно можно разделить на две части. В верхней находится перечень, так называемых полей. Можно легко убедиться, что названия полей соответствуют названиям заголовков исходной таблицы. То есть по названию поля мы можем легко понять, какой именно столбец с данными за ним стоит.
В нижней части расположены четыре области, которые относятся к четырём конструктивным элементам сводной таблицы. В зависимости от того, в какую область мы перетащим то или иное поле, его данные будут выводиться в той или иной части сводной таблицы.
Например, нам нужно ответить на вопрос – поставки товаров в какой город позволили получить максимальную выручку?
То есть в первую очередь нам нужно получить список городов. Для этого захватываю мышью поле Город и перетягиваем его в область Строки. Мы сразу получаем список названий всех городов из столбца Город исходной таблицы.
То есть область Строки позволят разместить данные в строках.
Если мы перетянем данное поле в область Столбцы, то получим тот же список, но уже в одной строке, то есть каждое название стало заголовком отдельного столбца.
Верну поле в Строки и завершу создание первой сводной таблицы. Нам ведь нужно узнать суммарную выручку по городам, поэтому просто перетягиваем поле Итого в Значения. Получаем точно такую же табличку, как и раньше, но буквально в несколько щелчков мыши.
Пока не будем обращать внимание на внешний вид сводной таблицы, а сосредоточимся на ее функциональности.
Обратите внимание на то, что в области Строки фигурирует имя поля, а в области Значения находится фраза «Сумма по полю Итого». Эта же фраза подставлена в заголовок соответствующего столбца сводной таблицы. Она указывает на то, что при формировании значений столбца сводной таблицы производилось суммирование значений столбца Итого умной таблицы.
Если щелкнуть мышью по маленькому черному треугольнику и в меню выбрать Параметры полей значений, то появится окно, в котором доступны все возможные операции. Чаще всего приходится применять суммирование или подсчет количества значений.
Так как в столбце Итого в исходной таблицы у нас находятся числовые значения, то Эксель автоматически выбрал суммирование при перетягивании поля в область. Если же в столбце находится текст, то по умолчанию будет выбрано количество.
Так в столбце Заказчик умной таблицы находятся наименования торговых сетей, то если я перетяну их в область Значения мы увидим количество по этому полю. Фактически это значение показывает, сколько было поставок в том или ином городе, то есть сколько сделок было совершено.
Ну а теперь давайте создадим еще одну сводную таблицу, которая ответит на второй вопрос – какая торговая сеть позволила получить компании наибольшую выручку?
Переключимся на лист с исходными данными и точно также создадим еще одну сводную таблицу на новом листе.
В первую очередь нам нужен список торговых сетей, поэтому перетянем поле Заказчик в область Строки. Ну а поле Итого в Значения. Все готово!
Ну и последняя задача – определить категорию товаров, принесшую наибольшую выручку.
Действием по аналогии.
Можно сделать отчет более информативным, если в область Строки перенести еще и Товар. Тогда мы сможем получить информацию не только по отдельным категориям товаров, но и по товарам внутри категории.
При этом важно соблюдать “вложенность” полей. То есть у нас товары принадлежат категориям, а не наоборот. Этот порядок задается последовательностью полей в области. Если сейчас изменить их очередность, то получим следующее – появится список всех товаров, а вложенной информацией станет их принадлежность к какой-либо товарной категории.
В данном случае это крайне не информативно, поэтому верну все как было.
Но не стоит забывать и про область Столбцы. Для примера перетянем в нее поле Заказчик. Мы получим подробный отчет по объемам заказов каждой категории товаров отдельными торговыми сетями. При этом каждую категорию можно раскрыть, чтобы увидеть детализацию по каждому товару и сети.
На первый взгляд формирование сводной таблицы с помощью полей может показаться довольно сложным и непредсказуемым, но небольшая практика быстро расставит все на свои места и позволит вам сходу ориентироваться в нужных областях при создании отчетов.
Итак, у нас есть отчеты, отвечающие на поставленные задачи. Осталось лишь немного отформатировать данные в таблицах, сделав их более приятными для восприятия.
Форматирования сводной таблицы
В первую очередь поговорим о заголовках. Именно их хочется сразу изменить, но тут есть один нюанс, который стоит учитывать.
Заголовок изменяется самым обычным образом – щелкаем по ячейке с ним и затем меняем текст.
Также можно выделить нужную ячейку с заголовком и нажать клавишу F2 для перехода в режим редактирования ее содержимого.
При этом важно учитывать, что в сводной таблице название заголовка не может быть таким же, как и название поля. То есть если я захочу переименовать «Сумма по полю Итого» в «Итого», то ничего не выйдет и появится ошибка.
Правда этот нюанс можно обойти. Если добавить в конце слова пробел, то для Эксель это будет уже другое значение, а пользователь разницы не увидит.
Я же просто переименую поля в «Сумма заказов» и «Количество заказов». Первый заголовок также можно изменить на «Город».
Осталось отформатировать сами значения. В первую очередь изменим числовой формат, сделав его денежным. При этом сразу же приходит на ум воспользоваться соответствующим инструментом со вкладки Главная.
Однако, если выделенным будет только одна ячейка столбца, то и форматирования затронет только ее. В данном случае правильнее будет изменить числовой формат для всего столбца и для этого достаточно из контекстного меню, вызванного щелчком правой кнопки мышки на любой из ячеек столбца, выбрать пункт Числовой формат.
Затем в появившемся окне указываем нужный формат и задаем его параметры.
Форматирование будет применено сразу ко всем столбцу.
Ну а также на контекстной вкладке Конструктор, которая появляется только при выделении сводной таблицы, можно задать стиль оформления таблицы целиком. Для этого нужно либо выбрать одну из готовых цветовых схем, либо можно создать свой вариант стилевого оформления, задав форматирования для каждого элемента сводной таблицы индивидуально.
Общие и промежуточные итоги
И уж если речь зашла о контекстной вкладке Конструктор, то стоит сразу сказать и о настройках сводной таблицы, связанных с ее макетом.
Макет определяет, в какой части сводной таблицы будет выводиться тот или иной ее элемент, то есть определяет ее структуру. Кроме данных, которые автоматически подтягиваются в сводную таблицу из исходной, сама сводная таблица формирует общие и промежуточные итоги по каждому столбцу и строке.
Расположением и видимостью общих и промежуточных итогов мы также можем управлять. Для этого есть соответствующие инструменты на контекстной вкладке Конструктор.
Промежуточные итоги в моем примере формируются суммами по каждой категории товаров и по умолчанию выводятся в строке с наименованием категории, то есть в заголовке группы.
То есть если просуммировать значения по каждому товару, то мы получим значение, указанное в промежуточных итогах.
Далеко не всегда это значение нужно выводить. Так при раскрытом списке оно скорее создает путаницу, если не знать, что именно оно означает. В таком случае можно отключить промежуточные итоги, выбрав соответствующую опцию.
Тогда промежуточные итоги будут выводиться только в случае свернутой категории, когда данные по отдельным товарам не отображаются.
Также можно выводить промежуточные итоги отдельной строкой в нижней части каждой категории товаров (второй пункт меню). Опять же, промежуточные итоги будут отображаться в свернутом виде в основной строке, а при развернутой категории смещаться отдельной строкой ниже.
Общие итоги также формируются автоматически по каждой строке и столбцу и далеко не всегда они необходимы. В соответствующем меню мы можем полностью отключить вывод общих итогов в сводной таблице, либо оставить итоги только по столбцу или только строке.
Макет сводной таблицы
Ну и выбор макета также влияет на внешний вид сводной таблицы. Есть три варианта.
Первый – сжатая форма. Этот вариант по умолчанию и мы его видим сразу после создания сводной таблицы.
При выборе второго варианта – форма структуры, в сводной таблице под каждое поле будет выделен отдельный столбец. То есть в первом столбце теперь выводится только категория товара, а сами товары отображаются во втором столбце.
Табличная форма аналогична форме структуры, но промежуточные итоги из строки с названием категории перемещаются вниз.
В этом же меню есть еще одна настройка, позволяющая повторять или не повторять подписи элементов.
Сейчас категория отображается только в одной строке и это вариант с не повторяющимися подписями. Если выбрать второй вариант, то название категории будет дублироваться в каждой строке.
Ну а теперь со знанием дела приведем отчет к нужному виду – вернем сводной таблице сжатую форму, а затем перенесем промежуточные итоги вниз каждой категории.
С помощью соответствующего инструмента вставим пустые строки после каждой категории, чтобы визуально их отделить друг от друга.
Ну а чтобы быстро свернуть или развернуть все категории можно воспользоваться контекстным меню, вызванным щелчком правой кнопки мыши на соответствующей ячейке. Здесь есть раздел, в котором выбираем нужный вариант.
Ну а если кнопки свертывания не нужны, то можно их скрыть. Для этого на контекстной вкладке Анализ отключим их отображение.
Подкорректируем заголовки, выберем подходящий стиль и наш отчет готов.
Сортировка и фильтрация
Скорее всего вы уже обратили внимание на то, что в сводной таблице есть две ячейки с кнопками.
По щелчку мыши на них появляется меню с возможностью фильтрации и сортировки данных. Эти инструменты относятся к заголовкам строк и, соответственно, столбцов.
Если нужно сформировать отчет только по какой-то одной товарной категории (например, “Зелень”), то с помощью фильтра отключаем все ненужные и получаем результат:
То же самое касается и заказчиков. То есть мы можем сократить отчет только до нужной категории товаров, заказанных определенной торговой сетью.
Если кроме категории нужно отфильтровать данные еще и по конкретным товарам, то в меню в выпадающем списке указываем соответствующее поле, а затем делаем фильтрацию по нему.
При применении сортировки или фильтрации значок на кнопке изменяется. По нему можно однозначно определить, что данные в столбце или строке отфильтрованы или отсортированы.
Чтобы удалить фильтры достаточно выбрать соответствующий пункт в меню, однако в случае с вложенными полями удаление фильтра касается только выбранного в выпадающем списке. То есть если фильтрация была произведена по нескольким полям, то для ее удаления нужно будет сначала переключиться на соответствующее поле.
Кроме стандартных возможностей фильтрации мы можем настроить фильтр по произвольному полю. Для этого есть отдельная область, которая так и называется Фильтры.
Сейчас мы построили отчет, дающий полное представление об объемах заказов со стороны торговых сетей, но вот как дела обстоят по отдельным городам?
Перетаскиваем соответствующее поле в область Фильтр и над сводной таблицей появляется соответствующий выпадающий список.
Мы можем выбрать отдельный город, чтобы получить информацию только по нему.
Что же касается сортировки, то в выпадающем меню есть стандартные инструменты, позволяющие отсортировать заголовки строк или столбцов в алфавитном порядке.
Однако намного удобнее пользоваться контекстным меню. Например одной из первых задач у нас было определить, в каком из городов за прошедшее время выручка была максимальной. Мы получили результат в виде данных по всем городам, но чтобы быстро определить нужное значение необходимо отсортировать значения по возрастанию или убыванию. Вызываем контекстное меню на любой ячейке столбца и выбираем нужный вариант.
Аналогично можно отсортировать данные по любому полю или итогам. Просто вызываем контекстное меню на соответствующей ячейке и выбираем нужное направление сортировки.
Ну и затронув тему фильтрации нельзя обойти стороной так называемые срезы.
Срезы в сводных таблицах
Срез – это тот же фильтр, но интерактивный.
При вставке среза мы также выбираем поле, по которому фильтр будет работать. Например, вставим два среза – по городам и товарам.
Если в ранее вставленном нами фильтре нужно выбирать нужные объекты из списка, то в срезе достаточно щелкнуть мышью по нужному пункту. При этом обратите внимание на то, что срез по городам и ранее вставленный вручную фильтр работают синхронно, то есть полностью дублируют друг друга.
Таким образом выбирая нужные значения в срезах в пару щелчков мыши мы можем изменять отчет, выводя в нем только нужную информацию.
Для выделения нескольких пунктов подряд достаточно выбирать их удерживая нажатой левую кнопку мыши. Если же нужно выбрать несколько несмежных значений, то в окне каждого среза есть соответствующая кнопка. Также как и для очистки фильтров.
Даты в сводных таблицах
Ну и последняя важная тема – это даты. Пока мы вообще не трогали поле Дата, но сводные таблицы позволяют очень гибко выводить информацию, связанную с датами и сейчас я это продемонстрирую.
Создадим еще одну сводную таблицу, в которой выведем выручку за все время.
В исходной таблице указывалась конкретная дата каждой сделки, а сводная таблица автоматически сгруппировала даты при этом не только по годам, но и по кварталам и месяцам. При этом в области Строки поле Дата было автоматически преобразовано в три – Годы, Кварталы и Дата.
Если такая группировка не нужна, то можно ее отменить через контекстное меню.
Также с помощью контекстного меню можно вернуть группировку (пункт Группировать), указав необходимые группы. Здесь можно выбрать сразу несколько, например, месяцы и года.
Сортировка и фильтрация по датам работает также, как и с другими данными. Например, можно отключить какой-то временной период.
Для дат существует свой формат срезов – временная шкала.
Она также в интерактивном режиме позволяет выбирать только интересующие вас временные интервалы.
Таким образом на базе сводной таблицы можно создать интерактивный отчет, в котором с помощью срезов и временной шкалы можно очень тонко фильтровать данные. Ну а преобразовав данные сводной таблицы в диаграммы или графики можно получить отличный дашборд, с помощью которого легко можно анализировать или демонстрировать информацию.
Ну а сводные таблицы – это очень обширная и увлекательная тема, которой я посвятил отдельный очень подробный видеокурс, который так и называется “Сводные таблицы“.
Нажмите на эту ссылку, чтобы перейти на страницу курса >>
________________________________________
Ссылки на мои ресурсы по Excel
★ YouTube-канал Excel Master
★ Серия видеокурсов “Microsoft Excel Шаг за Шагом”
★ Авторские книги и курсы
Таблицы – важный инструмент в работе пользователя Excel. Как в Экселе сделать таблицу и автоматизиро…
Таблицы – важный инструмент в работе пользователя Excel. Как в Экселе сделать таблицу и автоматизировать этот процесс, ответит наша статья.
Советы по структурированию информации
Перед тем, как создать таблицу в Excel, предлагаем изучить несколько общих правил:
- Сведения организуются по колонкам и рядам. Каждая строка отводится под одну запись.
- Первый ряд отводится под так называемую «шапку», где прописываются заголовки столбцов.
- Нужно придерживаться правила: один столбец – один формат данных (числовой, денежный, текстовый и т.д.).
- В таблице должен содержаться идентификатор записи, т.е. пользователь отводит один столбец под нумерацию строк.
- Структурированные записи не должны содержать пустых колонок и рядов. Допускаются нулевые значения.
Как создать таблицу в Excel вручную
Для организации рабочего процесса пользователь должен знать, как создать таблицу в Экселе. Существуют 2 метода: ручной и автоматический. Пошаговая инструкция, как нарисовать таблицу в Excel вручную:
- Открыть книгу и активировать нужный лист.
- Выделить необходимые ячейки.
- На панели инструментов найти пиктограмму «Границы» и пункт «Все границы».
- Указать в таблице имеющиеся сведения.
II способ заключается в ручном рисовании сетки таблицы. В этом случае:
- Выбрать инструмент «Сетка по границе рисунка» при нажатии на пиктограмму «Границы».
- При зажатой левой кнопке мыши (ЛКМ) перетащить указатель по обозначенным линиям, в результате чего появляется сетка. Таблица создается, пока нажата ЛКМ.
Как создать таблицу в Excel автоматически
Опытные пользователи рекомендуют прибегнуть к изучению вопроса, как создать таблицу в Excel
автоматически. С использованием встроенного инструментария процесс создания табличной формы происходит в разы быстрее.
Область таблицы
Перед тем, как составить таблицу в Excel, пользователю нужно определить, какой интервал ячеек ему понадобится:
- Выделить требуемый диапазон.
- В MS Excel 2013-2019 на вкладке «Главная» кликнуть на пиктограмму «Форматировать как таблицу».
- При раскрытии выпадающего меню выбрать понравившийся стиль.
Кнопка «Таблица» на панели быстрого доступа
На панели инструментов находится пиктограмма для создания табличного объекта. Чтобы воспользоваться функционалом табличного процессора, пользователь использует следующий алгоритм:
- Активировать интервал ячеек, необходимых для работы.
- Перейти в меню «Вставка».
- Найти пиктограмму «Таблицы»:
- В MS Excel 2007 кликнуть на пиктограмму. В появившемся диалоговом окне отметить или убрать переключатель пункта «Таблица с заголовками». Нажать ОК.
- В MS Excel 2016 нажать пиктограмму и выбрать пункт «Таблица». Указать диапазон ячеек через выделение мышкой или ручное прописывание адресов ячеек. Нажать ОК.
Примечание: для создания объекта используют сочетание клавиш CTRL + T.
4. Для изменения названия столбца перейти на строку формул или дважды кликнуть на объекте с заголовком.
Диапазон ячеек
Работа с числовой информацией подразумевает применение функций, в которых указывается интервал (диапазон ячеек). Под диапазоном справочная литература определяет множество клеток электронной таблицы, в совокупности образующих единый прямоугольник (А1:С9).
Активированная вкладка «Конструктор» раскрывает инструментарий, облегчающий процесс редактирования объекта. Для автоматизации работы пользователь устанавливает переключатели на инструментах «Строка заголовка» и «Строка итогов». Последняя опция позволяет провести расчеты по выбранной формуле. Для этого пользователь раскрывает список посредством нажатия на стрелку в итоговой ячейке и выбирает функцию.
Заполнение данными
Работа со структурированной информацией возможна, если ячейки заполнены текстовой, численной и иной информацией.
- Для заполнения необходимо активировать ячейку и начать вписывать информацию.
- Для редактирования ячейки дважды кликнуть на ней или активировать редактируемую ячейку и нажать F2.
- При раскрытии стрелок в строке заголовка структурированной информации MS Excel можно отфильтровать имеющуюся информацию.
- При выборе стиля форматирования объекта MS Excel автоматически выбрать опцию черезстрочного выделения.
- Вкладка «Конструктор» (блок «Свойства») позволяет изменить имя таблицы.
- Для увеличения диапазона рядов и колонок с последующим наполнением информацией: активировать кнопку «Изменить размер таблицы» на вкладке «Конструктор», новые ячейки автоматически приобретают заданный формат объекта, или выделить последнюю ячейку таблицы со значением перед итоговой строкой и протягивает ее вниз. Итоговая строка останется неизменной. Расчет проводится по мере заполнения объекта.
- В заголовках должны отсутствовать числовые форматы, поскольку при создании таблицы они преобразуются в текст. Если формула содержит в качестве аргумента ссылку на ячейку заголовка, где предполагалось число, то функция может не сработать.
Сводная таблица
Сводка используется для обобщения информации и проведения анализа, не вызывает трудностей при создании и оформлении. Для создания сводной таблицы:
- Структурировать объект и указать сведения.
- Перейти в меню «Вставка» и выбрать пиктограмму: в MS Excel 2007 – «Сводная таблица»; в MS Excel 2013-2019 – «Таблицы – Сводная таблица».
- При появлении окна «Создание сводной таблицы» активировать строку ввода диапазона, устанавливая курсор.
- Выбрать диапазон и нажать ОК.
Примечание: Если сводка должна находиться после создания на этом же листе, пользователь устанавливает переключатель на нужную опцию.
5. При появлении боковой панели для настройки объекта перенести категории в нужные области или включить переключатели («галочки»).
Созданная сводка автоматически подсчитывает итоги по каждому столбцу.
Рекомендуемые сводные таблицы
Поздние версии MS Excel предлагают воспользоваться опцией «Рекомендуемые сводные таблицы». Подобная вариация анализа информации применяется в случаях невозможности правильного подбора полей для строк и столбцов.
Для применения рекомендуемых сводных таблиц:
- Выделить ячейки с введенной информацией.
- При клике на пиктограмму «Таблицы» выбрать пункт «Рекомендуемые сводные таблицы».
- Табличный процессор автоматически анализирует информацию и предлагает оптимальные варианты решения задачи.
- В случае выбора подходящего пункта таблицы и подтверждения через ОК получить сводную таблицу.
Готовые шаблоны в Excel 2016
Табличный процессор MS Excel 2016 при запуске предлагает выбрать оптимальный шаблон для создания таблицы. В офисном пакете представлено ограниченное количество шаблонов. В Интернете пользователь может скачать дополнительные образцы.
Чтобы воспользоваться шаблонами:
- Выбирать понравившийся образец.
- Нажать «Создать».
- Заполнить созданный объект в соответствии с продуманной структурой.
Оформление
Экстерьер объекта – важный параметр. Поэтому пользователь изучает не только, как построить таблицу в Excel, но и как акцентировать внимание на конкретном элементе.
Создание заголовка
Дана таблица, нарисованная посредством инструмента «Границы». Для создания заголовка:
Выделить первую строку, кликнув ЛКМ по численному обозначению строки.
На вкладке «Главная» найти инструмент «Вставить».
Активировать пункт «Вставить строки на лист».
После появления пустой строки выделить интервал клеток по ширине таблицы.
Нажать на пиктограмму «Объединить» и выбрать первый пункт.
Задать название в ячейке.
Изменение высоты строки
Обычно высота строки заголовка больше первоначально заданной. Корректировка высоты строки:
- Нажать правой кнопкой мыши (ПКМ) по численному обозначению строки и активировать «Высота строки». В появившемся окне указать величину строки заголовка и нажать ОК.
- Или перевести курсор на границу между первыми двумя строками. При зажатой ЛКМ оттянуть нижнюю границу ряда вниз до определенного уровня.
Выравнивание текста
Если пользователь предполагает расположение текста в ячейке иное, нежели по умолчанию, то пользуется пиктограммами «Выравнивание» относительно горизонтали и вертикали, а также кнопкой «Ориентация». Выбор пунктов выпадающего списка позволит расположить текст по вертикали или диагонали.
Изменение стиля
Изменение размера шрифта, начертания и стиля написания осуществляется вручную. Для этого пользователь пользуется инструментами блока «Шрифт» на вкладке «Главная» или вызывает диалоговое окно «Формат ячеек» через ПКМ.
Пользователь может воспользоваться пиктограммой «Стили». Для этого выбирает диапазон ячеек и применяет понравившийся стиль.
Как вставить новую строку или столбец
Для добавления строк, столбцов и ячеек:
- выделить строку или столбец, перед которым вставляется объект;
- активировать пиктограмму «Вставить» на панели инструментов;
- выбрать конкретную опцию.
Удаление элементов
Для удаления объектов на листе MS Excel пользователь активирует аналогичную кнопку на панели инструментов, предварительно выделив строку, столбец, ячейку.
Заливка ячеек
Для задания фона ячейки, строки или столбца:
- выделить диапазон;
- найти на панели инструментов пиктограмму «Цвет заливки»;
- выбрать понравившийся цвет.
II способ
- вызвать «Формат ячеек» через ПКМ;
- перейти на вкладку «Заливка»;
- выбрать цвет, способы заливки, узор и цвет узора.
III способ
- щелкнуть на стрелочку в блоке «Шрифт»;
- перейти на вкладку «Заливка»;
- выбрать понравившийся стиль.
Формат элементов
На панели инструментов находится пиктограмма «Формат». Опция помогает задать размер ячеек, видимость, упорядочить листы и защитить лист.
Формат содержимого
Последний пункт из выпадающего списка «Формат» на панели быстрого доступа позволяет назначить тип данных или числовые форматы, задать параметры внешнего вида и границы объекта, установить фон и защитить лист.
Использование формул в таблицах
Табличный процессор пользуется успехом благодаря возможности применения математических, статистических, логических и т.п. функций.
Ознакомиться с полным списком и вписываемыми аргументами пользователь может, нажав на ссылку «Справка по этой функции».
Для задания формулы:
- активировать ячейку, где будет рассчитываться формула;
- открыть «Мастер формул»;
или
- написать формулу самостоятельно в строке формул и нажимает Enter;
или
- применить и активирует плавающие подсказки.
На панели инструментов находится пиктограмма «Автосумма», которая автоматически подсчитывает сумму столбца. Чтобы воспользоваться инструментом:
- выделить диапазон;
- активировать пиктограмму.
Использование графики
Для вставки изображения в ячейку:
- Выделить конкретную ячейку.
- Перейти в меню «Вставка – Иллюстрации – Рисунки» или «Вставка – Рисунок».
- Указать путь к изображению.
- Подтвердить выбор через нажатие на «Вставить».
Инструментарий MS Excel поможет пользователю создать и отформатировать таблицу вручную и автоматически.
