Вредоносное ПО (malware) - это назойливые или опасные программы,...
Excel - мощное офисное приложение, позволяющее проводить сложнейшие вычислительные расчёты, систематизировать информацию, создавать аналитические расчёты. К сожалению, многие пользователи, не разбираясь в тонкостях Excel , используют её только в качестве простого счётного инструмента вроде калькулятора.
Проанализировать в рекордно короткое время представленную информацию, сопровождающуюся числовыми показателями, принять на основе них эффективные управленческие решения, помогает сводная таблица в Excel, а как сделать её, можно легко понять, ознакомившись с рекомендациями продвинутых пользователей.
Сводная таблица создаётся, приняв за основу уже имеющуюся таблицу, которая может быть продуктом Excel или другой базы данных.
Если пользователь решил пройти начальный курс, как создать сводную таблицу в Excel,то ему лучше потренироваться на табличном варианте, созданном этим офисным приложением. После отличного усвоения основ, как делать сводные таблицы в Excel , можно переходить к более усложнённым вариантам, когда показатели получают путём загрузки из документов, созданных совершенно иными приложениями.
Основные требования
Существует ряд требований, обязательных для выполнения, в противном случае сводная таблица не будет выполнять задачи, на которые так рассчитывает пользователь.
Главным требованием является то, что все данные должны быть занесены в виде таблицы, в верхней части которой, безусловно, оформляется шапка (с указанием названий столбцов).
Кроме этого, должны быть полностью исключены пустые строки и столбцы. За пределами таблицы не должны находиться никакие примечания и посторонние записи, занесённые в строки или столбцы, примыкающие к используемой области.
Желательно, чтобы во всех табличных ячейках были тоже занесены данные, по возможности следует избегать пустых ячеек, поскольку это может негативно отразиться на систематизации и последующем анализе информации.
Часто в Excel пользователи объединяют несколько ячеек. Если необходимо создать сводные отчёты, то придётся избегать такого объединения. Недопустимо скрывать строки или столбцы, которые, по мнению пользователя, в этой ситуации не являются важными.
Инструкция по созданию
После того как пользователем были соблюдены все вышеописанные требования, можно приступать к дальнейшим действиям, позволяющим понять, как сделать сводную таблицу в Эксель.
Сначала нужно найти первую ячейку, которая расположена сразу под табличной шапкой, и сделать её активной. Для этого потребуется установить курсор мышки на эту ячейку и кликнуть левой клавишей.
Затем в основном меню следует перейти во вкладку «Вставка», после чего отобразится его подменю, среди которого будет пиктограмма с названием пункта «Сводная таблица». Кликнув по нему, активизируется мастер создания сводных отчётов, воспользовавшись которым, будет значительно проще делать табличный аналитический отчёт.
В появившемся диалоговом окне табличный редактор запросит указать диапазон, который будет подлежать анализу. Чаще всего, мастер самостоятельно правильно определяет его, поэтому вносить изменения не потребуется.
Также необходимо указать, где пользователь желает создать сводный отчёт:
- на том же самом листе, где имеются основные показатели;
- на новом листе.
Перейдя на новый лист, пользователь обнаружит там область для таблицы, а также список допустимых полей. Определившись с тем, какие конкретно показатели должны подлежать анализу, пользователь должен перетянуть мышкой выбранные поля в области «Фильтр», «Колонна». Таким способом удастся создать столбики сводного отчёта.
Далее, точно таким же способом оформляются строки, перетаскивая мышкой нужные поля. Если строк несколько, то важно определить, какая должна находиться вверху, а какая внизу. Можно расположить сначала строки в одном порядке и пронаблюдать, насколько это будет удобно, насколько корректно будет отображаться сводная информация. Если будет сложно анализировать данные при таком расположении, то всё легко исправляется – достаточно зажать определённое поле мышкой и переместить его вверх или вниз.
В завершение остаётся сделать последнюю область сводного отчёта «Значения». В неё переносится поле, являющееся ключевым для анализа, например, общая масса, общее количество.
Если всё делать строго по инструкции, то уже на этом этапе отобразится аналитический отчёт. При выявлении ошибок, достаточно поменять местами поля, обновить данные, и таблица автоматически перестроится.
Использование сводных отчётов
Пользователь принимает решение делать сводную таблицу, если возникает необходимость часто прибегать к аналитической деятельности. В связи с этим полезно знать и то, что таблица оснащена фильтром, позволяющим систематизировать данные по указанным критериям.
Применение фильтра
Начинающим пользователям полезно знать, как обновить сводную таблицу в Excel, и как осуществлять запрос на основе группировки данных.
Обновить данные в сводной таблице важно тогда, когда пользователем были внесены любые изменения в основной таблице. Для того чтобы обновить данные, следует кликнуть мышкой в пределах табличной области, вызывая дополнительное меню. В нём легко найти пункт «Обновить», а выбрав его, автоматически произойдёт обновление.
Применение фильтра в сводных отчётах также является достаточно важным действием. Очень часто заносятся ежедневные данные работников, а сотруднику, ответственному за аналитические отчёты, необходимо проанализировать данные за квартал, полугодие.
Как раз решить такую задачу помогает фильтр. Делать опять ничего сложного не понадобится. Достаточно кликнуть по любой дате мышкой (правой клавишей), в контекстном меню выбрать «Группировать», вслед за этим откроется диалоговое окно, в котором можно определить приемлемые для себя способы группировки. После этого таблица автоматически перестроится, значительно уменьшится в размерах, в связи с этим анализировать данные будет значительно проще.
Итак, создавать сводные таблицы несложно, если предварительно потренироваться на простой таблице , соблюдая все требования и вводя правильные данные. Овладев такими навыками, пользователь очень быстро ощутит преимущества, поскольку анализ данных будет осуществляться за короткий промежуток времени, сопровождаясь абсолютной точностью.
Привет всем! Сегодняшний материал для тех, кто продолжает осваивать работу с программами-приложениями, и не знает, как сделать сводную таблицу в excel.
Создав общую таблицу, в каком либо из текстовых документов, можно осуществить её анализ, сделав в Excel сводные таблицы.
Создание сводной Эксель таблицы требует соблюдения определенных условий:
- Данные вписываются в таблицу, где есть столбцы и списки с названиями.
- Отсутствие незаполненных форм.
- Отсутствие скрытых объектов.
Как сделать сводную таблицу в excel: пошаговая инструкция
Для создания сводной таблицы необходимо:
Создался пустой лист, где видно списки областей и полей. Заголовки стали полями в нашей новой таблице. Сводная таблица будет формироваться путем перетаскивания полей.
Помечаться они будут галочкой, и для удобства анализа вы будете их менять местами в табличных областях.
Я решил, что анализ данных буду делать через фильтр по продавцам, чтобы было видно кем и на какую сумму каждый месяц было продано, и какой именно товар.
Выбираем конкретного продавца. Зажимаем мышку и переносим поле «Продавец» в «Фильтр отчета». Новое поле отмечается галочкой, и вид таблицы немного изменяется.
Категорию «Товары» мы поставим в виде строк. В «Названия строк» мы переносим необходимое нам поле.
Для отображения выпадающего списка имеет значение, в какой последовательности мы указываем наименование. Если изначально в строках делаем выбор в пользу товара, а потом указываем цену, то товары как раз и будут выпадающими списками, и наоборот.
Столбец «Единицы», будучи в главной таблице, отображал количество товара проданного определенным продавцом по конкретной цене.
Для отображения продаж, например, по каждому месяцу, нужно поле «Дата» поставить на место «Названия столбцов». Выберите команду «Группировать», нажав на дату.
Указываем периоды даты и шаг. Подтверждаем выбор.
Видим такую таблицу.
Сделаем перенос поля «Сумма» к области «Значения».
Стало видно отображение чисел, а нам необходим именно числовой формат
Для исправления, выделим ячейки, вызвав окно мышкой, выберем «Числовой формат».
Числовой формат мы выбираем для следующего окна и отмечаем «Разделитель групп разрядов». Подтверждаем кнопкой «ОК».
Оформление сводной таблицы
Если мы поставим галочку, которая подтверждает выделение сразу нескольких объектов, то сможем обрабатывать данные сразу по нескольким продавцам.
Применение фильтра возможно для столбцов и строк. Поставив галочку на одной из разновидностей товара, можно узнать, сколько его реализовано одним или несколькими продавцами.
Отдельно настраиваются и параметры поля. На примере мы видим, что определенный продавец Рома в конкретном месяце продал рубашек на конкретную сумму. Нажатием мышки мы в строке «Сумма по полю…» вызываем меню и выбираем «Параметры полей значений».
Далее для сведения данных в поле выбираем «Количество». Подтверждаем выбор.
Посмотрите на таблицу. По ней четко видно, что в один из месяцев продавец продал рубашки в количестве 2-х штук.
Теперь меняем таблицу и делаем так, чтобы фильтр срабатывал по месяцам. Поле «Дата» мы переносим в «Фильтр отчета», а там где «Названия столбцов», будет «Продавец». Таблица отображает весь период продаж или за конкретный месяц.
Выделение ячеек в сводной таблице приведет к появлению такой вкладки как «Работа со сводными таблицами», а в ней будут еще две вкладки «Параметры» и «Конструктор».
На самом деле рассказывать о настройках сводных таблиц можно еще очень долго. Проводите изменения под свой вкус, добиваясь удобного для вас пользования. Не бойтесь нажимать и экспериментировать. Любое действие вы всегда сможете изменить нажатием сочетания клавиш Ctrl+Z.
Надеюсь, вы усвоили весь материал, и теперь знаете, как сделать сводную таблицу в excel.
Для анализа больших и сложных таблиц обычно используют . С помощью формул также можно осуществить группировку и анализ имеющихся данных. Создадим несложные отчеты с помощью формул.
В качестве исходной будем использовать (Вставка/ Таблицы/ Таблица ), содержащую информацию о продажах партий продуктов. В строках таблицы приведены данные о поставке партии продукта и его сбыте. Аналогичная таблица использовалась в статье .
В таблице имеются столбцы:
- Товар – наименование партии товара, например, «Апельсины »;
- Группа – группа товара, например, «Апельсины » входят в группу «Фрукты »;
- Дата поставки – Дата поставки Товара Поставщиком;
- Регион продажи – Регион, в котором была реализована партия Товара;
- Продажи – Стоимость, по которой удалось реализовать партию Товара;
- Сбыт – срок фактической реализации Товара в Регионе (в днях);
- Прибыль – отметка о том, была ли получена прибыль от реализованной партии Товара.
Через Диспетчер имен откорректируем таблицы на «Исходная_таблица » (см. файл примера ).
С помощью формул создадим 5 несложных отчетов, которые разместим на отдельных листах.
Отчет №1 Суммарные продажи Товаров
Найдем суммарные продажи каждого Товара.
Задача решается достаточно просто с помощью функции СУММЕСЛИ()
, однако само построение отчета требует определенных навыков работы с некоторыми средствами EXCEL.
Итак, приступим. Для начала нам необходимо сформировать перечень названий Товаров. Т.к. в столбце Товар исходной таблицы названия повторяются, то нам нужно из него выбрать только значения. Это можно сделать несколькими способами: формулами (см. статью ), через меню Данные/ Работа с данными/ Удалить дубликаты или с помощью . Если воспользоваться первым способом, то при добавлении новых Товаров в исходную таблицу, новые названия будут включаться в список автоматически. Но, здесь для простоты воспользуемся вторым способом. Для этого:
- Перейдите на лист с исходной таблицей;
- Вызовите (Данные/ Сортировка и фильтр/ Дополнительно );
- Заполните поля как показано на рисунке ниже: переключатель установите в позицию Скопировать результат в другое место ; в поле Исходный диапазон введите $A$4:$A$530; Поставьте флажок Только уникальные записи .
- Скопируйте полученный список на лист, в котором будет размещен отчет;
- Отсортируйте перечень товаров (Данные/ Сортировка и фильтр/ Сортировка от А до Я ).
Должен получиться следующий список.
В ячейке B6 введем нижеследующую формулу, затем скопируем ее вниз до конца списка:
СУММЕСЛИ(Исходная_Таблица[Товар];A6;Исходная_Таблица[Продажи])
СЧЁТЕСЛИ(Исходная_Таблица[Товар];A6)
Отчет №2 Продажи Товаров по Регионам
Найдем суммарные продажи каждого Товара в Регионах.
Воспользуемся перечнем Товаров, созданного для Отчета №1. Аналогичным образом получим перечень названий Регионов (в поле Исходный диапазон введите $D$4:$D$530).
Скопируйте полученный вертикальный диапазон в Буфер обмена
и его в горизонтальный. Полученный диапазон, содержащий названия Регионов, разместите в заголовке отчета.
В ячейке B 8 введем нижеследующую формулу:
СУММЕСЛИМН(Исходная_Таблица[Продажи];
Исходная_Таблица[Товар];$A8;
Исходная_Таблица[Регион продажи];B$7)
Формула вернет суммарные продажи Товара, название которого размещено в ячейке А8 , в Регионе из ячейки В7 . Обратите внимание на использование (ссылки $A8 и B$7), она понадобится при копировании формулы для остальных незаполненных ячеек таблицы.
Скопировать вышеуказанную формулу в ячейки справа с помощью не получится (это было сделано для Отчета №1), т.к. в этом случае в ячейке С8 формула будет выглядеть так:
СУММЕСЛИМН(Исходная_Таблица[Сбыт, дней];
Исходная_Таблица[Группа];$A8;
Исходная_Таблица[Продажи];C$7)
Отчет №3 Фильтрация Товаров по прибыльности
Вернемся к исходной таблице. Каждая партия Товара либо принесла прибыль, либо не принесла (см. столбец Прибыль в исходной таблице). Подсчитаем продажи по Группам Товаров в зависимости от прибыльности. Для этого будем фильтровать с помощью формул записи исходной таблицы по полю Прибыль.
ЧАСТОТА(Исходная_Таблица[Сбыт, дней];A7:A12)
Для ввода формулы выделите диапазон С6:С12 , затем в Строке формул введите вышеуказанную формулу и нажмите CTRL + SHIFT + ENTER .
Этот же результат можно получить с помощью обычной функции СУММПРОИЗВ()
:
=СУММПРОИЗВ((Исходная_Таблица[Сбыт, дней]>A6)*
(Исходная_Таблица[Сбыт, дней]<=A7))
Отчет №5 Статистика поставок Товаров
Теперь подготовим отчет о поставках Товаров за месяц.
Сначала создадим перечень месяцев по годам. В исходной таблице самая ранняя дата поставки 11.07.2009. Вычислить ее можно с помощью формулы:
=МИН(Исходная_Таблица[Дата поставки])
Создадим перечень дат - , начиная с самой ранней даты поставки. Для этого воспользуемся формулой:
=КОНМЕСЯЦА($C$5;-1)+1
В результате получим перечень дат - первых дней месяцев:
Применив соответствующий формат ячеек, изменим отображение дат:
Формула для подсчета количества поставленных партий Товаров за месяц:
СУММПРОИЗВ((Исходная_Таблица[Дата поставки]>=B9)* Теперь добавим строки для подсчета суммарного количества партий по каждому году. Для этого немного изменим таблицу, выделив в отдельный столбец год, в который осуществлялась поставка, с помощью функции ГОД()
. Теперь для вывода по годам создадим структуру через пункт меню : После нажатия ОК, таблица будет изменена следующим образом: Будут созданы промежуточные итоги по годам. Резюме
:
Отчеты, аналогичные созданным, можно сделать, естественно, с помощью или с применением Фильтра
к исходной таблице или с помощью других функций БДСУММ()
, БИЗВЛЕЧЬ()
, БСЧЁТ()
и др. Выбор подхода зависит конкретной ситуации. Сводная таблица - это эффективный инструмент для вычисления, сведения и анализа данных, который упрощает поиск сравнений, закономерностей и тенденций. Подготовка Если у вас недостаточно опыта работы со сводными таблицами или вы не знаете, с чего начать, лучше воспользоваться рекомендуемой сводной таблицей
. При использовании этой функции Excel определяет подходящий макет, сопоставляя данные с наиболее подходящими областями в сводной таблице. Это позволяет получить отправную точку для дальнейших экспериментов. После создания рекомендуемой сводной таблицы вы можете изучить различные ориентации и изменить порядок полей для получения нужных результатов. В области Имя поля
строк
столбцов
значений
Значения в сводной таблице Обновление сводных таблиц При добавлении новых данных в источник необходимо обновить все сводные таблицы, созданные на его основе. Чтобы обновить одну сводную таблицу, можно щелкнуть правой кнопкой мыши
в любом месте ее диапазона и выбрать команду Обновить
. При наличии нескольких сводных таблиц сначала выберите любую ячейку в любой сводной таблице, а затем на ленте
откройте вкладку Анализ сводной таблицы
, щелкните стрелку под кнопкой Обновить
и выберите команду Обновить все
. Удаление сводной таблицы Если вам больше не нужна сводная таблица, просто выделите ее и нажмите клавишу DELETE
. Это не повлияет на другие данные и сводные таблицы или диаграммы. Если сводная таблица находится на отдельном листе, где больше нет нужных данных, вы можете просто удалить этот лист. Так проще всего избавиться от сводной таблицы. Теперь сводную таблицу можно вставить в электронную таблицу в Excel Online. Работа со списком полей сводной таблицы В области Поля сводной таблицы
вверху установите флажки для полей, которые вы хотите добавить в сводную таблицу. По умолчанию нечисловые поля добавляются в область строк
, поля значений даты и времени - в область столбцов
, а числовые поля - в область значений
. Вы также можете вручную перетаскивать элементы в любые поля сводной таблицы. Если элемент больше не нужен, просто перетащите его за пределы списка полей или снимите его флажок. Возможность перестановки элементов - одна из особенностей сводной таблицы, благодаря которой можно быстро и просто изменять ее вид. Работа с значениями сводной таблицы Обновление сводных таблиц При добавлении новых данных в источник необходимо обновить все основанные на нем сводные таблицы. Чтобы обновить сводную таблицу, можно щелкнуть правой кнопкой мыши в любом месте ее диапазона и выбрать команду Обновить
Удаление сводной таблицы Если вам больше не нужна сводная таблица, просто выделите ее и нажмите клавишу DELETE. Это не повлияет на другие данные и сводные таблицы или диаграммы. Если сводная таблица находится на отдельном листе, где больше нет нужных данных, вы можете просто удалить этот лист. Так проще всего избавиться от сводной таблицы. Вы всегда можете задать вопрос специалисту Excel Tech Community , попросить помощи в сообществе Answers community , а также предложить новую функцию или улучшение на веб-сайте Работать со сводными таблицами Excel приходится в разных сферах. Можно быстро обрабатывать большие объемы информации, сравнивать, группировать данные. Это значительно облегчает труд менеджеров, продавцов, руководителей, маркетологов, социологов и т.д. Сводные таблицы позволяют быстро сформировать различные отчеты по одним и тем же данным. Кроме того, эти отчеты можно гибко настраивать, изменять, обновлять и детализировать. У нас есть тренировочная таблица с данными: Каждая строка дает нам исчерпывающую информацию об одной сделке: Если это огромная сеть магазинов и продажи идут, то в течение одного квартала размер таблицы станет ужасающим. Проанализировать данные в сотне строк будет очень сложно. А на составление отчета уйдет не один день. В такой ситуации сводная таблица просто необходима. Создам отчет с помощью мастера сводных таблиц. В новых версиях Excel он почему-то спрятано глубоко в настройках: Теперь инструмент находится в панели быстрого доступа, а значит всегда под рукой. Готовый отчет можно форматировать, изменять. Это можно сделать вручную и автоматически. Настройка автоматического обновления при изменении данных: Теперь каждый раз при открытии файла с измененными данными будет происходить автоматическое обновление сводной таблицы. Когда мы сводим в отчет большой объем данных, для выводов и принятия каких-то решения может понадобиться группировка. Допустим, нам нужно увидеть итоги за месяц или квартал. Группировка по дате в сводной таблице Excel: Получаем отчет, в котором четко видны суммы продаж по месяцам. Поэкспериментируем и установим шаг – «Кварталы». Результат – сводная таблица вида: Если фамилия продавцов для анализа деятельности сети магазинов не важна, можем сформировать отчет с поквартальной прибылью. Чтобы убрать результаты группировки, необходимо щелкнуть по ячейке с данными правой кнопкой мыши и нажать разгруппировать. Либо выбрать данный параметр в меню «Структура». У нас есть сводный отчет такого вида: Видны итоги по месяцам (сделано «Группировкой») и по наименованиям товаров. Сделаем отчет более удобным для изучения. Как в сводной таблице сделать итоги сверху: Уже нет той перегруженности, которая затрудняла восприятие информации. Как удалить промежуточные итоги? Просто на вкладке макет выбираем «Не показывать промежуточные суммы»: Получим отчет без дополнительных сумм: Огромные сводные таблицы, которые составляются на основании «чужих» таблиц, периодически нуждаются в детализации. Мы не знаем, откуда взялась сумма в конкретной ячейке Excel. Но можно это выяснить, если разбить сводную таблицу на несколько листов. Мы можем переместить всю сводную таблицу на новый лист, выбрав на вкладке «Действия» кнопку «Переместить». По умолчанию в сводную таблицу помещается абсолютно вся информация из того столбца, который мы добавляем в отчет. В нашем примере – ВСЕ товары, ВСЕ даты, ВСЕ суммы и магазины. Возможно, пользователю не нужны некоторые элементы. Они просто загромождают отчет и мешают сосредоточиться на главном. Уберем ненужные элементы. Жмем ОК – сводная таблица меняется.
(Исходная_Таблица[Дата поставки]
Нажатием маленьких кнопочек в левом верхнем углу листа можно управлять отображением данных в таблице.
Настройка сводной таблицы
Дополнительные сведения
Создание отчета с помощью мастера сводных таблиц
Как обновить данные в сводной таблице Excel?
Некоторые секреты форматирования
Работа с итогами
Детализация информации
