Сводные таблицы Excel – тема очень интересная и обширная.
В этой заметке я расскажу все, что вам нужно знать, для того чтобы начать применять сводные таблицы (в английском варианте – pivot table) в своей работе.
Сводные таблицы предоставляют очень широкие возможности для формирования нужных вам отчетов на основе каких-либо данных. При этом отчеты на базе сводных таблиц создаются буквально в несколько щелчков мыши и не требуют от пользователя создания сложнейших формул для группировки или суммирования необходимых данных.
Кроме этого, данные сводных таблиц очень просто визуализировать с помощью диаграмм или графиков, что с успехом применяется при создании так называемых дашбордов, которые сейчас очень широко применяются при анализе данных.
Постановка задачи
Итак, сводные таблицы могут быть применены к абсолютно любым данным, но чаще всего Excel применяют для анализа различных финансовых показателей компаний или проектов, поэтому давайте рассмотрим следующий пример (скачать файл).
Допустим вы трудитесь в компании, которая является поставщиком овощей и фруктов в сетевые супермаркеты, которые находятся в нескольких крупных городах страны.
По каждой поставке в базе есть информация, которую можно выгрузить в приблизительно вот такую таблицу:
Здесь в каждой строке мы видим дату заказа, название сети супермаркетов, город, в который осуществлялась поставка, категорию товара, его наименование, цену на дату поставки, количество заказов и итоговую сумму сделки.
Информации может быть намного больше. Для упрощения задачи я взял лишь минимальный набор данных. Тем не менее данных очень много и таблица состоит из нескольких тысяч строк.
Любую компанию в первую очередь интересует прибыль и поэтому может потребоваться найти ответы на ряд вопросов, например:
- Определить, в каком из городов за прошедшее время выручка была максимальной.
- Какая торговая сеть позволила получить компании наибольшую выручку.
- Определить категорию товаров и конкретный товар, принесшие наибольшую выручку.
В таблице представлены данные за два года, поэтому приблизительно такие же вопросы могут возникнуть у руководства компании по отношению к какому-то конкретному временному интервалу, например, какой товар был наиболее востребованным прошлым летом, или в прошлом году, какова была динамика продаж товаров разных категорий в течении года (по месяцам, кварталам) или какой из заказчиков в прошлом месяце был для компании наиболее значимым. Также часто возникает необходимость определить лидеров продаж, например составить ТОП5 товаров, на которые был наибольший спрос в определенное время.
Решение задачи формулами
На первый взгляд все эти задачи легко решаются несложными формулами и стандартными функциями.
Так, например, для определения города, принесшего максимальную выручку, нужно лишь сложить сумму всех поставок по каждому из городов. Сделать это можно с помощью функции СУММЕСЛИ.
То есть нам нужно будет создать отдельную таблицу, в которую с помощью функции СУММЕСЛИ свести данные по каждому из городов.
Для начала нужно создать список уникальных значений. Для этого скопируем значение столбца с названиями городов (столбец С) и вставим их на новый лист. Далее с помощью удаления дубликатов оставим лишь уникальные значения.
Ну а теперь применим функцию СУММЕСЛИ.
Сначала укажем диапазон значений, в котором будем искать условие (это столбец с городами в исходной таблице), а затем зададим само условие. Нам нужно, чтобы в этой строке суммировались итоги по конкретному городу, поэтому указываем ячейку с его названием в новой таблице. Ну а теперь задаем диапазон, значения которого нужно суммировать в случае выполнения условия – это столбец с итогами.
Получаем выручку, полученную в конкретном городе. Растягиваем формулу на весь диапазон новой таблицы и получаем результат.
В итоге по значениям суммарной выручки мы легко определим победителя и ответим на первый вопрос.
Для решения остальных задач нужно будет также создавать отдельные таблицы и с помощью формул, которые могут быть довольно сложными, рассчитывать значения.
Подобный подход к формированию нужных отчетов весьма трудоемкий и требует не только времени на создание отчета, но и внимательности со стороны пользователя, ведь допустить ошибку в формуле при обработке огромного массива данных довольно просто. Ну а об аппетитах начальства рассказывать и вовсе нет смысла. Как только на стол ляжет ответ на первый вопрос сразу же появятся дополнительные и придется вновь корпеть над формулами и стягивать нужные данные в компактную табличку…
Это не наш метод, тем более что сводные таблицы позволяют сделать ровно тоже самое, но в разы быстрее.
Ответим на первый вопрос с помощью сводной таблицы.
Создание сводной таблицы в Excel
Сводная таблица всегда строится на основе некоторого массива данных, который должен иметь строку заголовков.
В моем примере мы имеем простой диапазон значений, но первая строка диапазона содержит заголовки столбцов, а значит и такой диапазон подойдёт для создания сводной таблицы.
Установим табличный курсор в любую ячейку диапазона и на вкладке Вставка выберем Сводная таблица.
Excel автоматически выберет весь неразрывный диапазон значений и появится окно создания сводной таблицы, где будет указана абсолютная ссылка на этот диапазон.
Абсолютным и относительным ссылкам уже было посвящено отдельное подробное видео, поэтому не буду на этом останавливаться. Упомяну лишь, что в случае использования такого фиксированного диапазона в качестве источника данных сводной таблицы, мы в итоге не сможем добавлять в нее новую информацию. Точнее сказать, если в исходной таблице появятся новые строки, то для того, чтобы информация из них появилась в сводной таблице нам придется вручную корректировать в ее настройках этот диапазон.
Фактически данную проблему полностью решают умные таблицы. Поэтому перед тем, как создать новую сводную таблицу стоит преобразовать исходные данные в умную таблицу.
Делается это очень просто – также устанавливаем табличный курсор в любую ячейку диапазона и либо на вкладке Вставка выбираем Таблица, либо просто нажимаем сочетание клавиш Ctrl+T.
Так как строка с заголовками уже присутствует в диапазоне, то соответствующую галочку не убираем.
Ну и также, как и раньше создадим сводную таблицу, но уже на основе умной таблицы.
Теперь в источнике данных уже указан не фиксированный диапазон на листе, а отдельный объект Таблица1. Это нам гарантирует, что новые данные будут автоматически добавляться в сводную таблицу при ее обновлении.
Вставим сводную таблицу на новый лист.
На новом листе появится подсказка, подсказывающая нам, что это лист сводной таблицы, а также в правой части окна появится панель инструментов, позволяющая сконструировать нужный нам отчет.
Эту панель инструментов условно можно разделить на две части. В верхней находится перечень, так называемых полей. Можно легко убедиться, что названия полей соответствуют названиям заголовков исходной таблицы. То есть по названию поля мы можем легко понять, какой именно столбец с данными за ним стоит.
В нижней части расположены четыре области, которые относятся к четырём конструктивным элементам сводной таблицы. В зависимости от того, в какую область мы перетащим то или иное поле, его данные будут выводиться в той или иной части сводной таблицы.
Например, нам нужно ответить на вопрос – поставки товаров в какой город позволили получить максимальную выручку?
То есть в первую очередь нам нужно получить список городов. Для этого захватываю мышью поле Город и перетягиваем его в область Строки. Мы сразу получаем список названий всех городов из столбца Город исходной таблицы.
То есть область Строки позволят разместить данные в строках.
Если мы перетянем данное поле в область Столбцы, то получим тот же список, но уже в одной строке, то есть каждое название стало заголовком отдельного столбца.
Верну поле в Строки и завершу создание первой сводной таблицы. Нам ведь нужно узнать суммарную выручку по городам, поэтому просто перетягиваем поле Итого в Значения. Получаем точно такую же табличку, как и раньше, но буквально в несколько щелчков мыши.
Пока не будем обращать внимание на внешний вид сводной таблицы, а сосредоточимся на ее функциональности.
Обратите внимание на то, что в области Строки фигурирует имя поля, а в области Значения находится фраза «Сумма по полю Итого». Эта же фраза подставлена в заголовок соответствующего столбца сводной таблицы. Она указывает на то, что при формировании значений столбца сводной таблицы производилось суммирование значений столбца Итого умной таблицы.
Если щелкнуть мышью по маленькому черному треугольнику и в меню выбрать Параметры полей значений, то появится окно, в котором доступны все возможные операции. Чаще всего приходится применять суммирование или подсчет количества значений.
Так как в столбце Итого в исходной таблицы у нас находятся числовые значения, то Эксель автоматически выбрал суммирование при перетягивании поля в область. Если же в столбце находится текст, то по умолчанию будет выбрано количество.
Так в столбце Заказчик умной таблицы находятся наименования торговых сетей, то если я перетяну их в область Значения мы увидим количество по этому полю. Фактически это значение показывает, сколько было поставок в том или ином городе, то есть сколько сделок было совершено.
Ну а теперь давайте создадим еще одну сводную таблицу, которая ответит на второй вопрос – какая торговая сеть позволила получить компании наибольшую выручку?
Переключимся на лист с исходными данными и точно также создадим еще одну сводную таблицу на новом листе.
В первую очередь нам нужен список торговых сетей, поэтому перетянем поле Заказчик в область Строки. Ну а поле Итого в Значения. Все готово!
Ну и последняя задача – определить категорию товаров, принесшую наибольшую выручку.
Действием по аналогии.
Можно сделать отчет более информативным, если в область Строки перенести еще и Товар. Тогда мы сможем получить информацию не только по отдельным категориям товаров, но и по товарам внутри категории.
При этом важно соблюдать “вложенность” полей. То есть у нас товары принадлежат категориям, а не наоборот. Этот порядок задается последовательностью полей в области. Если сейчас изменить их очередность, то получим следующее – появится список всех товаров, а вложенной информацией станет их принадлежность к какой-либо товарной категории.
В данном случае это крайне не информативно, поэтому верну все как было.
Но не стоит забывать и про область Столбцы. Для примера перетянем в нее поле Заказчик. Мы получим подробный отчет по объемам заказов каждой категории товаров отдельными торговыми сетями. При этом каждую категорию можно раскрыть, чтобы увидеть детализацию по каждому товару и сети.
На первый взгляд формирование сводной таблицы с помощью полей может показаться довольно сложным и непредсказуемым, но небольшая практика быстро расставит все на свои места и позволит вам сходу ориентироваться в нужных областях при создании отчетов.
Итак, у нас есть отчеты, отвечающие на поставленные задачи. Осталось лишь немного отформатировать данные в таблицах, сделав их более приятными для восприятия.
Форматирования сводной таблицы
В первую очередь поговорим о заголовках. Именно их хочется сразу изменить, но тут есть один нюанс, который стоит учитывать.
Заголовок изменяется самым обычным образом – щелкаем по ячейке с ним и затем меняем текст.
Также можно выделить нужную ячейку с заголовком и нажать клавишу F2 для перехода в режим редактирования ее содержимого.
При этом важно учитывать, что в сводной таблице название заголовка не может быть таким же, как и название поля. То есть если я захочу переименовать «Сумма по полю Итого» в «Итого», то ничего не выйдет и появится ошибка.
Правда этот нюанс можно обойти. Если добавить в конце слова пробел, то для Эксель это будет уже другое значение, а пользователь разницы не увидит.
Я же просто переименую поля в «Сумма заказов» и «Количество заказов». Первый заголовок также можно изменить на «Город».
Осталось отформатировать сами значения. В первую очередь изменим числовой формат, сделав его денежным. При этом сразу же приходит на ум воспользоваться соответствующим инструментом со вкладки Главная.
Однако, если выделенным будет только одна ячейка столбца, то и форматирования затронет только ее. В данном случае правильнее будет изменить числовой формат для всего столбца и для этого достаточно из контекстного меню, вызванного щелчком правой кнопки мышки на любой из ячеек столбца, выбрать пункт Числовой формат.
Затем в появившемся окне указываем нужный формат и задаем его параметры.
Форматирование будет применено сразу ко всем столбцу.
Ну а также на контекстной вкладке Конструктор, которая появляется только при выделении сводной таблицы, можно задать стиль оформления таблицы целиком. Для этого нужно либо выбрать одну из готовых цветовых схем, либо можно создать свой вариант стилевого оформления, задав форматирования для каждого элемента сводной таблицы индивидуально.
Общие и промежуточные итоги
И уж если речь зашла о контекстной вкладке Конструктор, то стоит сразу сказать и о настройках сводной таблицы, связанных с ее макетом.
Макет определяет, в какой части сводной таблицы будет выводиться тот или иной ее элемент, то есть определяет ее структуру. Кроме данных, которые автоматически подтягиваются в сводную таблицу из исходной, сама сводная таблица формирует общие и промежуточные итоги по каждому столбцу и строке.
Расположением и видимостью общих и промежуточных итогов мы также можем управлять. Для этого есть соответствующие инструменты на контекстной вкладке Конструктор.
Промежуточные итоги в моем примере формируются суммами по каждой категории товаров и по умолчанию выводятся в строке с наименованием категории, то есть в заголовке группы.
То есть если просуммировать значения по каждому товару, то мы получим значение, указанное в промежуточных итогах.
Далеко не всегда это значение нужно выводить. Так при раскрытом списке оно скорее создает путаницу, если не знать, что именно оно означает. В таком случае можно отключить промежуточные итоги, выбрав соответствующую опцию.
Тогда промежуточные итоги будут выводиться только в случае свернутой категории, когда данные по отдельным товарам не отображаются.
Также можно выводить промежуточные итоги отдельной строкой в нижней части каждой категории товаров (второй пункт меню). Опять же, промежуточные итоги будут отображаться в свернутом виде в основной строке, а при развернутой категории смещаться отдельной строкой ниже.
Общие итоги также формируются автоматически по каждой строке и столбцу и далеко не всегда они необходимы. В соответствующем меню мы можем полностью отключить вывод общих итогов в сводной таблице, либо оставить итоги только по столбцу или только строке.
Макет сводной таблицы
Ну и выбор макета также влияет на внешний вид сводной таблицы. Есть три варианта.
Первый – сжатая форма. Этот вариант по умолчанию и мы его видим сразу после создания сводной таблицы.
При выборе второго варианта – форма структуры, в сводной таблице под каждое поле будет выделен отдельный столбец. То есть в первом столбце теперь выводится только категория товара, а сами товары отображаются во втором столбце.
Табличная форма аналогична форме структуры, но промежуточные итоги из строки с названием категории перемещаются вниз.
В этом же меню есть еще одна настройка, позволяющая повторять или не повторять подписи элементов.
Сейчас категория отображается только в одной строке и это вариант с не повторяющимися подписями. Если выбрать второй вариант, то название категории будет дублироваться в каждой строке.
Ну а теперь со знанием дела приведем отчет к нужному виду – вернем сводной таблице сжатую форму, а затем перенесем промежуточные итоги вниз каждой категории.
С помощью соответствующего инструмента вставим пустые строки после каждой категории, чтобы визуально их отделить друг от друга.
Ну а чтобы быстро свернуть или развернуть все категории можно воспользоваться контекстным меню, вызванным щелчком правой кнопки мыши на соответствующей ячейке. Здесь есть раздел, в котором выбираем нужный вариант.
Ну а если кнопки свертывания не нужны, то можно их скрыть. Для этого на контекстной вкладке Анализ отключим их отображение.
Подкорректируем заголовки, выберем подходящий стиль и наш отчет готов.
Сортировка и фильтрация
Скорее всего вы уже обратили внимание на то, что в сводной таблице есть две ячейки с кнопками.
По щелчку мыши на них появляется меню с возможностью фильтрации и сортировки данных. Эти инструменты относятся к заголовкам строк и, соответственно, столбцов.
Если нужно сформировать отчет только по какой-то одной товарной категории (например, “Зелень”), то с помощью фильтра отключаем все ненужные и получаем результат:
То же самое касается и заказчиков. То есть мы можем сократить отчет только до нужной категории товаров, заказанных определенной торговой сетью.
Если кроме категории нужно отфильтровать данные еще и по конкретным товарам, то в меню в выпадающем списке указываем соответствующее поле, а затем делаем фильтрацию по нему.
При применении сортировки или фильтрации значок на кнопке изменяется. По нему можно однозначно определить, что данные в столбце или строке отфильтрованы или отсортированы.
Чтобы удалить фильтры достаточно выбрать соответствующий пункт в меню, однако в случае с вложенными полями удаление фильтра касается только выбранного в выпадающем списке. То есть если фильтрация была произведена по нескольким полям, то для ее удаления нужно будет сначала переключиться на соответствующее поле.
Кроме стандартных возможностей фильтрации мы можем настроить фильтр по произвольному полю. Для этого есть отдельная область, которая так и называется Фильтры.
Сейчас мы построили отчет, дающий полное представление об объемах заказов со стороны торговых сетей, но вот как дела обстоят по отдельным городам?
Перетаскиваем соответствующее поле в область Фильтр и над сводной таблицей появляется соответствующий выпадающий список.
Мы можем выбрать отдельный город, чтобы получить информацию только по нему.
Что же касается сортировки, то в выпадающем меню есть стандартные инструменты, позволяющие отсортировать заголовки строк или столбцов в алфавитном порядке.
Однако намного удобнее пользоваться контекстным меню. Например одной из первых задач у нас было определить, в каком из городов за прошедшее время выручка была максимальной. Мы получили результат в виде данных по всем городам, но чтобы быстро определить нужное значение необходимо отсортировать значения по возрастанию или убыванию. Вызываем контекстное меню на любой ячейке столбца и выбираем нужный вариант.
Аналогично можно отсортировать данные по любому полю или итогам. Просто вызываем контекстное меню на соответствующей ячейке и выбираем нужное направление сортировки.
Ну и затронув тему фильтрации нельзя обойти стороной так называемые срезы.
Срезы в сводных таблицах
Срез – это тот же фильтр, но интерактивный.
При вставке среза мы также выбираем поле, по которому фильтр будет работать. Например, вставим два среза – по городам и товарам.
Если в ранее вставленном нами фильтре нужно выбирать нужные объекты из списка, то в срезе достаточно щелкнуть мышью по нужному пункту. При этом обратите внимание на то, что срез по городам и ранее вставленный вручную фильтр работают синхронно, то есть полностью дублируют друг друга.
Таким образом выбирая нужные значения в срезах в пару щелчков мыши мы можем изменять отчет, выводя в нем только нужную информацию.
Для выделения нескольких пунктов подряд достаточно выбирать их удерживая нажатой левую кнопку мыши. Если же нужно выбрать несколько несмежных значений, то в окне каждого среза есть соответствующая кнопка. Также как и для очистки фильтров.
Даты в сводных таблицах
Ну и последняя важная тема – это даты. Пока мы вообще не трогали поле Дата, но сводные таблицы позволяют очень гибко выводить информацию, связанную с датами и сейчас я это продемонстрирую.
Создадим еще одну сводную таблицу, в которой выведем выручку за все время.
В исходной таблице указывалась конкретная дата каждой сделки, а сводная таблица автоматически сгруппировала даты при этом не только по годам, но и по кварталам и месяцам. При этом в области Строки поле Дата было автоматически преобразовано в три – Годы, Кварталы и Дата.
Если такая группировка не нужна, то можно ее отменить через контекстное меню.
Также с помощью контекстного меню можно вернуть группировку (пункт Группировать), указав необходимые группы. Здесь можно выбрать сразу несколько, например, месяцы и года.
Сортировка и фильтрация по датам работает также, как и с другими данными. Например, можно отключить какой-то временной период.
Для дат существует свой формат срезов – временная шкала.
Она также в интерактивном режиме позволяет выбирать только интересующие вас временные интервалы.
Таким образом на базе сводной таблицы можно создать интерактивный отчет, в котором с помощью срезов и временной шкалы можно очень тонко фильтровать данные. Ну а преобразовав данные сводной таблицы в диаграммы или графики можно получить отличный дашборд, с помощью которого легко можно анализировать или демонстрировать информацию.
Ну а сводные таблицы – это очень обширная и увлекательная тема, которой я посвятил отдельный очень подробный видеокурс, который так и называется “Сводные таблицы“.
Нажмите на эту ссылку, чтобы перейти на страницу курса >>
________________________________________
Ссылки на мои ресурсы по Excel
★ YouTube-канал Excel Master
★ Серия видеокурсов “Microsoft Excel Шаг за Шагом”
★ Авторские книги и курсы
Пример. Простой перечневой статистической таблицы
Таблица 6.1 ‑ Котировка облигаций государственного в одном из межбанковских объединений на 19.11.2018 г. (цифры условны (млн руб.))
| Облигации по номерам серии | Объем покупки | Объем продажи |
|
1 2 3 4 |
122,50 112,60 123,20 124,40 |
123,40 113,50 124,40 108,35 |
| Всего | 482,70 | 469,65 |
*Подлежащее – облигации
Пример. Простой монографической таблицы
Таблица 6.2 – Котировка облигаций государственного сберегательного займа в одном из межбанковских объединений на 19.11.2018 г. (цифры условные)(млн руб.)
| Облигации государственного сберегательного займа | Объем покупки | Объем продаж |
| 482,70 | 469,65 |
Таблица 6.3 – Цены на основные биржевые товары в России на 19.11.2018 г.
| Наименование товара | Средневзвешенная цена, руб. | Суммарный объем предложения, т | Минимальный объем партии, т |
| Бензин А-92 | |||
| Бензин-95 | |||
|
Дизельное топливо |
*Подлежащее – наименование товара
Пример. Простая перечневая таблица по территориальному принципу
Таблица 6.4 ‑ Средние цены на продовольственные товары на 01.03.2018 г. (руб./кг)
| Город | Говядина | Свинина | Масло сливочное | |||
| оптовая | розничная | оптовая | розничная | оптовая | розничная | |
| Москва | ||||||
| С-Петербург | ||||||
| Екатеринбург | ||||||
| Новосибирск | ||||||
| Омск | ||||||
| Казань |
*Подлежащее – перечень городов России
Аналогично строится простая перечневая таблица по временному принципу.
Пример. Групповая таблица
Таблица 6.5 – Распределение несовершеннолетних, совершивших правонарушения и преступления в 1918г.
(по возрасту)
| № группы | Группы несовершеннолетних по возрасту, лет | Всего | В том числе | ||
| имели привод в милицию |
состоят в милиции на учете |
совершили преступления | |||
| 1 | До 13 | ||||
| 2 | 14-15 | ||||
| 3 | 16-17 | ||||
| Итого | – |
*Подлежащее – Группы несовершеннолетних по возрасту
Пример. Сложная комбинационная таблица
Таблица 6.6 – Распределение эмитентов фондового рынка (цифры условные)
| Группы эмитентов по величине котировки банковского долга, млн руб. | Подгруппы эмитентов по размеру средневзвешенной ставки | Число эмитентов |
| 97-1745 |
50-75 75-100 |
6 9 |
| Итого по группе | 15 | |
| 1745-3393 |
50-75 75-100 |
2 2 |
| Итого по группе | 4 |
*Подлежащее ‑ Группы эмитентов по величине котировки банковского долга
При простой разработке сказуемого показатель, определяющий его, не подразделяется на подгруппы и итоговые значения получаются путем простого суммирования значений по каждому признаку отдельно, независимо друг от друга.
Пример простой разработки сказуемого.
Таблица 6.7– Характеристика студентов заочников
| Курс | Численность студентов, чел | ||||
| Всего | мужчин | женщин | омичей | иногородних | |
|
1 2 3 4 5 6 |
|||||
| Всего |
Пример простой разработки сказуемого может служить следующий фрагмент статистической таблицы:
Таблица 6.8 ‑ Распределение строительных организаций различных форм собственности по объему работ, выполненных по договорам строительного подряда в 2018 г.
| Строительные организации |
Объем работ, выполненных по договорам строительного подряда – всего |
в том числе по формам собственности | ||
|
государственная |
муниципальная | частная | смешанная российская |
прочие |
После заполнения данного фрагмента таблицы получается подробная характеристика строительных организаций по структуре объема работ по формам собственности. По каждой строительной организации можно получить информацию об объеме работ, выполненных по договорам строительного подряда, как в целом, так и в разрезе форм собственности.
Сложная разработка сказуемого в статистических таблицах предполагает деление признака, его формирующего, на группы, т.е. показатели сказуемого, сочетаются друг с другом, пример табл. 6.9, 6.10).
Таблица 6.9 – Характеристика студентов заочников
| Курс | Численность студентов, чел | ||||||||
| омичей | иногородних | Всего | |||||||
| Всего | мужчин | женщин | Всего | мужчин | женщин | Всего | мужчин | женщин | |
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |
|
1 2 3 4 5 6 |
|||||||||
Сложная разработка сказуемого предполагает деление признака, формирующего его, на подгруппы:
Таблица 6.10 – Распределение предприятий по видам акций
| Предприятия | Приобретено акций (всего) | в том числе | |||
| На льготных условиях |
По цене определенной Госкомимуществом |
||||
| привилегированные типа А. | обыкновенные | привилегированные типа А. | обыкновенные | ||
При этом получается более полная и подробная характеристика объекта.
Здесь оба признака сказуемого (ценовой и видовой) тесно связаны друг с другом. Можно проанализировать не только количество приобретенных акций по видам и условиям приобретения их сотрудниками предприятий, но и определить число привилегированных и обыкновенных акций, приобретенных на разных ценовых условиях. То есть, при сложной разработке сказуемого явление или объект могут быть охарактеризованы различной комбинацией признаков, формирующих их.
Исследователь при построении статистических таблиц должен руководствоваться оптимальным соотношением показателей сказуемого.
6. Статистическая таблица и ее элементы см. по ссылке
6.1. Примеры статистических таблиц см. по ссылке
6.2. Основные правила построения и анализа статистических таблиц см. по ссылке
6.3. Анализ и чтение статистических таблиц см. по ссылке
СТАТИСТИЧЕСКИЕ
ТАБЛИЦЫ
После
того как данные наблюдения собраны и
даже сгруппированы, их трудно воспринимать
и анализировать без определенной,
наглядной систематизации. Одним из
важнейших средств систематизации и
обобщения результатов сводки и обработки
статистических материалов является
статистическая
таблица. Сведенные
в таблицу данные приобретают компактность,
наглядность, доходчивость, и статистик
получает возможность делать на
основании их те или иные выводы. Этим
объясняется широкое применение табличной
формы изложения статистических
данных в практике экономической работы.
Статистические формы отчетности – это,
как правило, таблицы.
Использованне
таблиц как
средства
систематизации данных можно найти в
трудах основоположников политической
арифметики Дж.Граунта. В. Петти, Г. Кинга.
Эти таблицы носят характер самой простой
сводки статистических данных и не дают
возможности установить закономерности
изучаемого явления. Цель же всякого
научного познания действительности
состоит именно в раскрытии закономерностей,
выражающих те или иные связи между
явлениями. Поэтому особую ценность,
познавательный интерес имеют статистические
таблицы, которые способствуют решению
важных задач исследования. Следовательно,
важно, какую именно таблицу составляет
исследователь: таблицу, являющуюся
простой сводкой данных, или же таблицу,
которая открывает возможности научного
анализа данных.
Отличительная черта
статистической таблицы состоит в том,
что она дает сводную количественную
характеристику исследуемой совокупности.
Все одноименные показатели располагаются
в таблице в одной и той же строке либо
в графе. затем по общим элементам
объединяются в разделы, имеющие общий
заголовок. Материал приобретает не
только удобную для обозрения форму, но
и позволяет определить значение
каждого признака, их совпадение,
последовательность, различие, взаимосвязь.
Появляется возможность сравнения,
сопоставления и анализа числовых
характеристик явления, т. е. возможность
сделать научные выводы.
Таким
образом, статистическая
таблица представляет собой наиболее
рациональную форму изложения результатов
сводки и обработки статистических
материалов, позволяющую
решить конкретные задачи количественного
анализа исследуемого явления.
Статистические
таблицы, которые можно рассматривать
как вполне научное представление
статистического материала, впервые
применены в работах русского академика
Л. Ю. Крафта (80-е – 90-е годы XVIII столетия).
Попытки
составлять сложные аналитические
таблицы, предназначенные для раскрытия
связей между изучаемыми явлениями,
можно найти у А. Кетле. Но А. Кетле не
ставил еще вопрос о методологии
составления статистических таблиц и
об их значении как средства раскрытия
взаимосвязей между явлениями.
Этот вопрос был поставлен немецкими
статистиками во второй половине XIX
столетия.
Заслугой земских
статистиков является широкое применение
табличного метода в практической
деятельности. Земская статистика сама
не разработала теоретических принципов
табличного метода, но создала для этого
благоприятные условия. Именно практика
земских статистиков дала «заряд»
для построения теории табличной
разработки и анализа статистических
материалов.
Большой вклад в
разработку теории табличного метода
внесли известные русские статистики
А. А. Чупров и А. А. Кауфман, которые на
первое место выдвигали аналитическое
значение таблицы как инструмента
научного анализа изучаемых явлений
В экономической
работе таблицы играют важнейшую роль.
Их ценность становится вполне ощутимой
в том случае, если всесторонне исследуется
сложное общественное явление. Грамотный
экономист должен уметь правильно
составлять таблицы, правильно читать
разработанные таблицы и делать на их
основе верные выводы.
Сtатистические
таблицы внешне представляют определенного
рода пересечения вертикальных граф и
горизонтальных строк, которые образуют
клетки, предназначенные для вписывания
в них статистических данных.
Каждая графа (колонка,
столбец) в верхней ее части обязательно
имеет пояснение в виде краткого заголовка.
Наименования строк помещаются в
левой части таблицы. При нанесении
только строк и граф без их наименований
и статистических данных получается
графленая сетка, которая именуется
скелетом таблицы. Если скелет таблицы
заполнить наименованиями строк и
граф, то получится макет таблицы.
Каждая
таблица имеет общее подробное название,
которое раскрывает читателю ее содержание
и назначение,
указывает, к какому месту и времени они
относятся.
Статистическую
таблицу можно рассматривать как форму
логического
предложения, имеющего
статистическое
подлежащее и
сказуемое.
Идея уподобления
статистической таблицы грамматическому
предложению принадлежит А. А. Кауфману.
Подлежащее
таблицы представляет
ту статистическую совокупность, о
которой идет речь в таблице, т. е. перечень
отдельных или всех единиц совокупности
либо их групп. Чаще всего подлежащее
помещается в левой части таблицы и
содержит перечень строк. Сказуемое
таблицы- это
цифровая характеристика изучаемой
совокупности (подлежащего таблицы),
представленная перечнем различных
признаков в соответствующих графах
таблицы.
Подлежащее и сказуемое
таблицы могут располагаться по-разному.
Это технический вопрос, главное, чтобы
таблица была легко обозримой, компактной
и легко воспринималась.
В
статистической практике и в исследовательских
работах используются таблицы различной
сложности. Это зависит от характера
изучаемой совокупности, объема имеющейся
информации, задач анализа. Существуют
разные принципы классификации таблиц.
Наиболее распространенной в советской
экономической литературе является
классификация, определяемая разработкой
статистического подлежащего или
группировкой единиц в подлежащем.
Если в подлежащем содержится простой
перечень каких-либо объектов или
территориальных единиц, таблица
называется
простой. Простые
таблицы имеют самое широкое применение
в статистической практике. Характеристика
предприятий отрасли по технико-экономическим
показателям за отчетный квартал,
полугодие, год приводится. В
простой перечневой
таблице, в подлежащем которой перечисляются
все предприятия отрасли. Характеристика
городов страны по численности
населения, размеру жилищного фонда и
т. п. также представляется в простой
таблице. Подлежащее простой таблицы
может содержать перечень территорий,
например областей, республик, экономических
районов. В этом случае таблица называется
территориальной.
Простая таблица
содержит лишь описательные сведения,
ее аналитические возможности ограничены.
Глубокий .анализ исследуемой совокупности,
взаимосвязей признаков предполагает
построение более сложных таблиц –
групповых и комбинационных.
Групповые
таблицы в
отличие от простых содержат в подлежащем
не простой перечень единиц объекта
наблюдения, а их группировку по одному
существенному признаку. Простейшим
видом групповой таблицы являются
таблицы, в которых представлены ряды
распределения. Групповая таблица
может быть более сложной, если в сказуемом
приводится не только число единиц в
каждой группе, но и ряд других важных
показателей, количественно и качественно
характеризующих группы подлежащего
(см. табл. 6.5).
Такие таблицы
часто используются в целях сопоставления
обобщающих показателей по группам, что
позволяет сделать определенные
практические выводы.’ Более широкими
аналитическими возможностями
располагают комбинационные таблицы.
Комбинационными
называются
статистические таблицы,
в подлежащем
которых группы единиц, образованные по
одному признаку, подразделяются на
подгруппы по одному или нескольким
другим признакам. В отличие от простых
и групповых таблиц комбинационные
позволяют проследить зависимость
показателей сказуемого от нескольких
признаков, которые легли в основу
комбинационной группировки в подлежащем.
Предположим, задача
заключается в том, чтобы по данным сорока
предприятий отрасли показать взаимосвязь
между объемом производства,
электровооруженностью труда, с одной
стороны, и средней выработкой продукции
на одного рабочего, с другой стороны.
Предварительное изучение характера
взаимосвязей показывает, что на выработку
рабочего влияет объем производства
предприятий и электровооруженность
труда. Следовательно, результативным
будет первый признак, а два последних
– факторные. Подлежащее таблицы будет
представлять комбинацию двух группировочных
признаков, т. е. первоначально
выделяются группы предприятий по объему
произведенной продукции, а затем
последние подразделяются на подгруппы
по электровооруженности труда. Затем
по группам и подгруппам производится
подсчет данных по показателям сказуемого
и все результаты оформляются в виде
комбинационной таблицы.
В
табл. 7.1 отражена не только структура
предприятий с точки зрения выпуска
продукции и электровооруженности труда,
но и взаимосвязи между признаками. Так,
весьма четко прослеживается зависимость
выработки рабочего как от объема
произведенной продукции, так и от
электровооруженности труда. Если на
предприятиях с выпуском продукции до
10 млн. руб. в год производительность
труда на одного рабочего за год составляет
3,8 тыс. руб., то на более крупных
предприятиях с выпуском продукции
свыше 20 млн. руб. выработка достигает
7,1 тыс. руб. Аналогично во всех трех
группах предприятий с ростом
электровооруженности труда увеличивается
выработка: в первой группе она возрастает
с 3,6 до 4,1 тыс. руб.; во второй – с 5,0 до 5,7
тыс. руб.; в третьей – с 6,8 до 7,4 тыс. руб.
При исследовании
зависимости результативных признаков
от трех и более факторов комбинационные
таблицы усложняются, становятся
громоздкими и трудно обозримыми. В этих
случаях рекомендуется строить не одну
таблицу, а систему взаимосвязанных
групповых и комбинационных таблиц.
Построение
статистических таблиц начинается
с разработки макета будущей таблицы.
Это – основной и ответственный этап,
требующий определенных знаний в области
изучаемой проблемы, четкого
представления целей и задач исследования
в целом и назначения конкретно
разрабатываемой таблицы.
Макет
таблицы включает
следующие элементы: общий заголовок
таблицы, ее скелет, полное наименование
подлежащего и всех его составляющих
(перечень единиц, групп, подгрупп),
наименование всех граф сказуемого,
итоговые строки и графы. После построения
макета таблицы следует техническая
операция – разнесение статистических
данных в соответствующие клетки и
подсчет итогов. Не следует увлекаться
слишком громоздкими макетами таблиц,
их будет сложно заполнять данными,
они потеряют обозримость, возникнут
сложности при анализе.
Как
правило, макет таблицы строится после
того, как четко определены границы
исследуемой совокупности, произведена
ее группировка по одному или нескольким
признакам, т. е. известна структура
подлежащего таблицы, необходимо решить
вопрос о его удобном размещении. Сложнее
решаются вопросы разработки сказуемого
таблицы.
При детальном
изучении сложного явления может быть
большое число признаков сказуемого,
которые могут быть представлены в
свернутом либо развернутом виде.
Развернутое сказуемое содержит
перечень показателей, которые при
необходимости подразделяются на
составляющие. Свернутые сказуемые
содержат, например, табл. 6.4, 6.5, 6.7, 6.8.
Развернутое сказуемое может иметь
простую и сложную разработку. Если
составные части показателя в сказуемом
не подразделяются на части, разработка
сказуемого считается простой.
Если
в таблице студентов стационарной,
заочной и вечерней форм обучения
подразделить в свою очередь по полу,
разработка сказуемого будет считаться
сложной. Сложная разработка сказуемого
дает более полную характеристику
исследуемого объекта, но приводит к
увеличению размеров таблицы и осложняет
ее восприятие. Это обстоятельство надо
учитывать при разработке макета таблицы
и выделять следует лишь те составляющие
показателей, которые действительно
необходимы в процессе исследования, а
другие объединить в одну графу под
рубрикой «прочие». Однако группа «прочие»
не должна охватывать более десятой
части общего итога. Если таблица
получается крайне громоздкая и
труднодоступная для обозрения, ее
рекомендуется разделить на несколько
таблиц. Комбинированная разработка
сказуемого позволяет одну сложную
таблицу представить в виде системы
таблиц с простой разработкой сказуемого.
Показатели сказуемого могут давать
количественную оценку изучаемого
объекта на какой-либо, один момент
времени или за определенный промежуток
времени. Такие таблицы называются
статическими.
Если же
показатели сказуемого характеризуют
изменение объекта во времени, то таблицы
называются динамическими.
Оформление таблиц
не должно быть произвольным. Существуют
определенные правила, которыми необходимо
руководствоваться при оформлении
таблиц:
Каждая таблица
должна иметь название (заголовок),
которое в лаконичной форме должно
раскрывать ее содержание. В названии
четко и ясно следует указать границы
статистической совокупности, период
или момент времени, к которому относятся
данные. Если единица измерения для всех
данных таблицы одинакова, ее целесообразно
вынести в Общий заголовок. При
сопоставлении данных сказуемого по
всем элементам подлежащего с одним
и тем же годом в заголовке таблицы
указать год сопоставления, например по
сравнению с 1913 г. или по сравнению с 1980
г. В общем заголовке должны быть
сделаны оговорки о полноте приводимых
данных и их характере (плановые,
фактические, расчетные).
Кроме общего заголовка
таблицы, ее графы и строки тоже должны
иметь свое название. Слова в заголовках
подлежащего и сказуемого желательно
писать полностью и ставить единицы
измерения, если они неодинаковые. Если
каждая строка имеет свою особую единицу
измерения, то для их обозначения
целесообразно отводить специальную
графу.
Графам таблицы, если
их много, желательно давать нумерацию.
Это облегчает пользование таблицей и,
кроме того, дает возможность показать
способ расчета некоторых показателей.
Первая графа, если она предназначена
для подлежащего, обозначается буквой
«А», графы сказуемого нумеруются
арабскими цифрами.
Обязательным
атрибутом статистической таблицы
являются итоговые строки и графы.
Без итогов ряд таблиц нельзя признать
законченными. В сложных таблицах следует
различать «итого» и «всего». «Итого»
– это характеристика, относящаяся к
определенной части совокупности, а
«всего» – это итог в целом для всей
изучаемой совокупности (см. табл. 7.1).
Часто в таблицах
приводятся данные, связанные друг с
другом. В таких случаях эти данные
следует располагать в рядом стоящих
графах. Например, абсолютные данные и
соответствующие им относительные
показатели, данные о плановом задании
и о выполнении плана, абсолютные приросты
и темпы роста.
Большое
значение в статистических таблицах
имеет запись цифр в графах с соблюдением
определенных правил. Статистика
часто оперирует многозначными числами,
которые для простоты восприятия
приходится округлять. Основное правило
округления, как известно, заключается
в том, что, если заменяемая цифра
больше или равна 5, стоящую от нее слева
цифру увеличивают на единицу. При
округлении надо помнить о назначении
таблицы. Чрезмерное округление может
скрыть существующие связи и закономерности,
исказить действительную картину.
Крайне нежелательно, чтобы после
округления оставалась лишь одна цифра,
а иногда и две. Если данные приводятся
в процентах, округление следует
производить до долей процентов.
Многозначные числа, состоящие из четырех
и более цифр, необходимо записывать,
отделяя каждые три цифры друг от друга,
т. е. выделяя классы миллионов, тысяч,
единиц. В такой записи многозначные
числа легче читаются и сопоставляются.
Например, число 73872458 надо записать 73
872 458, а при округлении – 73,9 млн.
В
статистической таблице каждая клетка
должна быть заполнена. Однако в ряде
случаев числа в клетках могут
отсутствовать. Причины отсутствия
должны быть определенным образом
показаны в таблице. Если сведения о
данном факте отсутствуют, но сам факт
имеет место, рекомендуется ставить три
точки (…). в
случае если
отсутствует само явление, ставится
тире (-). Если клетка не подлежит заполнению,
ста вится знак Х. Число 0,0 ставится в тех
случаях, когда изучаемое явление
наблюдается, но в очень малых размерах
и составляет менее половины последней
значащей цифры в условиях принятой
точности.
Числа
в табличных клетках могут сопровождаться
определенными знаками. Так, если
число получено на основании условных
расчетов, т. е. является не совсем точным,
его рекомендуется брать в скобки.
Сомнительные числа должны сопровождаться
вопросительным знаком, а предварительные
знаком *.
В ряде случаев
статистические таблицы сопровождаются
сносками и примечаниями. Сноски относятся
либо к отдельным строчкам, либо к
графам, иногда и к отдельным клеткам.
Сносками пользуются для того, чтобы
указать на ограниченные обстоятельства,
которые надо принять во внимание при
чтении таблицы. Примечания, которые
относятся ко всей таблице, обычно
даются сразу же после таблицы. Они дают
пояснения методам расчета показателей,
указывают источники получения данных,
определенные ограничения, принятые при
составлении таблицы.
Соседние файлы в папке 14-05-2013_10-41-11
- #
- #
- #
- #
- #
- #
- #
Содержание
- 1 Инструменты анализа Excel
- 2 Сводные таблицы в анализе данных
- 3 Анализ «Что-если» в Excel: «Таблица данных»
- 4 Анализ предприятия в Excel: примеры
- 5 Начало работы
- 6 Создание сводных таблиц
- 7 Использование рекомендуемых сводных таблиц
- 8 Анализ
- 8.1 Сводная таблица
- 8.2 Активное поле
- 8.3 Группировать
- 8.4 Вставить срез
- 8.5 Вставить временную шкалу
- 8.6 Обновить
- 8.7 Источник данных
- 8.8 Действия
- 8.9 Вычисления
- 8.10 Сервис
- 8.11 Показать
- 9 Конструктор
- 10 Сортировка значений
- 11 Сводные таблицы в Excel 2003
- 12 Заключение
- 13 Видеоинструкция
- 14 1. Сводные таблицы
- 14.1 Как работать
- 15 2. 3D-карты
- 15.1 Как работать
- 16 3. Лист прогнозов
- 16.1 Как работать
- 17 4. Быстрый анализ
- 17.1 Как работать
Анализ данных в Excel предполагает сама конструкция табличного процессора. Очень многие средства программы подходят для реализации этой задачи.
Excel позиционирует себя как лучший универсальный программный продукт в мире по обработке аналитической информации. От маленького предприятия до крупных корпораций, руководители тратят значительную часть своего рабочего времени для анализа жизнедеятельности их бизнеса. Рассмотрим основные аналитические инструменты в Excel и примеры применения их в практике.
Одним из самых привлекательных анализов данных является «Что-если». Он находится: «Данные»-«Работа с данными»-«Что-если».
Средства анализа «Что-если»:
- «Подбор параметра». Применяется, когда пользователю известен результат формулы, но неизвестны входные данные для этого результата.
- «Таблица данных». Используется в ситуациях, когда нужно показать в виде таблицы влияние переменных значений на формулы.
- «Диспетчер сценариев». Применяется для формирования, изменения и сохранения разных наборов входных данных и итогов вычислений по группе формул.
- «Поиск решения». Это надстройка программы Excel. Помогает найти наилучшее решение определенной задачи.
Практический пример использования «Что-если» для поиска оптимальных скидок по таблице данных.
Другие инструменты для анализа данных:
Анализировать данные в Excel можно с помощью встроенных функций (математических, финансовых, логических, статистических и т.д.).
Сводные таблицы в анализе данных
Чтобы упростить просмотр, обработку и обобщение данных, в Excel применяются сводные таблицы.
Программа будет воспринимать введенную/вводимую информацию как таблицу, а не простой набор данных, если списки со значениями отформатировать соответствующим образом:
- Перейти на вкладку «Вставка» и щелкнуть по кнопке «Таблица».
- Откроется диалоговое окно «Создание таблицы».
- Указать диапазон данных (если они уже внесены) или предполагаемый диапазон (в какие ячейки будет помещена таблица). Установить флажок напротив «Таблица с заголовками». Нажать Enter.
К указанному диапазону применится заданный по умолчанию стиль форматирования. Станет активным инструмент «Работа с таблицами» (вкладка «Конструктор»).
Составить отчет можно с помощью «Сводной таблицы».
- Активизируем любую из ячеек диапазона данных. Щелкаем кнопку «Сводная таблица» («Вставка» — «Таблицы» — «Сводная таблица»).
- В диалоговом окне прописываем диапазон и место, куда поместить сводный отчет (новый лист).
- Открывается «Мастер сводных таблиц». Левая часть листа – изображение отчета, правая часть – инструменты создания сводного отчета.
- Выбираем необходимые поля из списка. Определяемся со значениями для названий строк и столбцов. В левой части листа будет «строиться» отчет.
Создание сводной таблицы – это уже способ анализа данных. Более того, пользователь выбирает нужную ему в конкретный момент информацию для отображения. Он может в дальнейшем применять другие инструменты.
Анализ «Что-если» в Excel: «Таблица данных»
Мощное средство анализа данных. Рассмотрим организацию информации с помощью инструмента «Что-если» — «Таблица данных».
Важные условия:
- данные должны находиться в одном столбце или одной строке;
- формула ссылается на одну входную ячейку.
Процедура создания «Таблицы данных»:
- Заносим входные значения в столбец, а формулу – в соседний столбец на одну строку выше.
- Выделяем диапазон значений, включающий столбец с входными данными и формулой. Переходим на вкладку «Данные». Открываем инструмент «Что-если». Щелкаем кнопку «Таблица данных».
- В открывшемся диалоговом окне есть два поля. Так как мы создаем таблицу с одним входом, то вводим адрес только в поле «Подставлять значения по строкам в». Если входные значения располагаются в строках (а не в столбцах), то адрес будем вписывать в поле «Подставлять значения по столбцам в» и нажимаем ОК.
Анализ предприятия в Excel: примеры
Для анализа деятельности предприятия берутся данные из бухгалтерского баланса, отчета о прибылях и убытках. Каждый пользователь создает свою форму, в которой отражаются особенности фирмы, важная для принятия решений информация.
- скачать систему анализа предприятий;
- скачать аналитическую таблицу финансов;
- таблица рентабельности бизнеса;
- отчет по движению денежных средств;
- пример балльного метода в финансово-экономической аналитике.
Для примера предлагаем скачать финансовый анализ предприятий в таблицах и графиках составленные профессиональными специалистами в области финансово-экономической аналитике. Здесь используются формы бухгалтерской отчетности, формулы и таблицы для расчета и анализа платежеспособности, финансового состояния, рентабельности, деловой активности и т.д.
Сводные таблицы в Excel – мощный инструмент для создания отчетов. Он особенно полезен в тех случаях, когда пользователь плохо работает с формулами и ему сложно самостоятельно сделать анализ данных. В данной статье мы рассмотрим, как правильно создавать подобные таблицы и какие для этого существуют возможности в редакторе Эксель. Для этого никаких файлов скачивать не нужно. Обучение доступно в режиме онлайн.
Начало работы
Первым делом нужно создать какую-нибудь таблицу. Желательно, чтобы там было несколько столбцов. При этом какая-то информация должна повторяться, поскольку только в этом случае можно будет сделать какой-нибудь анализ введенной информации.
Например, рассмотрим одни и те же финансовые расходы в разных месяцах.
Создание сводных таблиц
Для того чтобы построить подобную таблицу, необходимо сделать следующие действия.
- Для начала ее необходимо полностью выделить.
- Затем перейдите на вкладку «Вставка». Нажмите на иконку «Таблица». В появившемся меню выберите пункт «Сводная таблица».
- В результате этого появится окно, в котором вам нужно указать несколько основных параметров для построения сводной таблицы. Первым делом необходимо выбрать область данных, на основе которых будет проводиться анализ. Если вы предварительно выделили таблицу, то ссылка на нее подставится автоматически. В ином случае ее нужно будет выделить.
- Затем вас попросят указать, где именно будет происходить построение. Лучше выбрать пункт «На существующий лист», поскольку будет неудобно проводить анализ информации, когда всё разбросано на несколько листов. Затем необходимо указать диапазон. Для этого нужно кликнуть на иконку около поля для ввода.
- Сразу после этого мастер создания сводных таблиц свернется до маленького размера. Помимо этого, изменится и внешний вид курсора. Вам нужно будет сделать левый клик мыши в любое удобное для вас место.
- В результате этого ссылка на указанную ячейку подставится автоматически. Затем нужно нажать на иконку в правой части окна, чтобы восстановить его до исходного размера.
- Для завершения настроек нужно нажать на кнопку «OK».
- В результате этого вы увидите пустой шаблон, для работы со сводными таблицами.
- На этом этапе необходимо указать, какое поле будет:
- столбцом;
- строкой;
- значением для анализа.
Вы можете выбрать что угодно. Всё зависит от того, какую именно информацию вы хотите получить.
- Для того чтобы добавить любое поле, по нему нужно сделать левый клик мыши и, не отпуская пальца, перетащить в нужную область. При этом курсор изменит свой внешний вид.
- Отпустить палец можно только тогда, когда исчезнет перечеркнутый круг. Подобным образом, нужно перетащить все поля, которые есть в вашей таблице.
- Для того чтобы увидеть результат целиком, можно закрыть боковую панель настроек. Для этого достаточно кликнуть на крестик.
- В результате этого вы увидите следующее. При помощи этого инструмента вы сможете свести сумму расходов в каждом месяце по каждой позиции. Кроме того, доступна информация об общем итоге.
- Если таблица вам не понравилась, можно попробовать построить ее немного по-другому. Для этого нужно поменять поля в областях построения.
- Снова закрываем помощник для построения.
- На этот раз мы видим, что сводная таблица стала намного больше, поскольку сейчас в качестве столбцов выступают не месяцы, а категории расходов.
Использование рекомендуемых сводных таблиц
Если у вас не получается самостоятельно построить таблицу, вы всегда можете рассчитывать на помощь редактора. В Экселе существует возможность создания подобных объектов в автоматическом режиме.
Для этого необходимо сделать следующие действия, но предварительно выделите всю информацию целиком.
- Перейдите на вкладку «Вставка». Затем нажмите на иконку «Таблица». В появившемся меню выберите второй пункт.
- Сразу после этого появится окно, в котором будут различные примеры для построения. Подобные варианты предлагаются на основе нескольких столбцов. От их количества напрямую зависит число шаблонов.
- При наведении на каждый пункт будет доступен предварительный просмотр результата. Так работать намного удобнее.
- Можно выбрать то, что нравится больше всего.
- Для вставки выбранного варианта достаточно нажать на кнопку «OK».
- В итоге вы получите следующий результат.
Обратите внимание: таблица создалась на новом листе. Это будет происходить каждый раз при использовании конструктора.
Анализ
Как только вы добавите (неважно как) сводную таблицу, вы увидите на панели инструментов новую вкладку «Анализ». На ней расположено огромное количество различных инструментов и функций.
Рассмотрим каждую из них более детально.
Сводная таблица
Нажав на кнопку, отмеченную на скриншоте, вы сможете сделать следующие действия:
- изменить имя;
- вызвать окно настроек.
В окне параметров вы увидите много чего интересного.
Активное поле
При помощи этого инструмента можно сделать следующее:
- Для начала нужно выделить какую-нибудь ячейку. Затем нажмите на кнопку «Активное поле». В появившемся меню кликните на пункт «Параметры поля».
- Сразу после этого вы увидите следующее окно. Здесь можно указать тип операции, которую следует использовать для сведения данных в выбранном поле.
- Помимо этого, можно настроить числовой формат. Для этого нужно нажать на соответствующую кнопку.
- В результате появится окно «Формат ячеек».
Здесь вы сможете указать, в каком именно виде нужно выводить результат анализа информации.
Группировать
Благодаря этому инструменту вы можете настроить группировку по выделенным значениям.
Вставить срез
Редактор Microsoft Excel позволяет создавать интерактивные сводные таблицы. При этом ничего сложного делать не нужно.
- Выделите какой-нибудь столбец. Затем нажмите на кнопку «Вставить срез».
- В появившемся окне, в качестве примера, выберите одно из предложенных полей (в будущем вы можете выделять их в неограниченном количестве). После того как что-нибудь будет выбрано, сразу же активируется кнопка «OK». Нажмите на неё.
- В результате появится небольшое окошко, которое можно перемещать куда угодно. В нем будут предложены все возможные уникальные значения, которые есть в данном поле. Благодаря этому инструменту вы сможете выводить сумму лишь за определенные месяцы (в данном случае). По умолчанию выводится информация за всё время.
- Можно кликнуть на любой из пунктов. Сразу после этого в поле сумма изменятся все значения.
- Таким образом получится выбрать любой промежуток времени.
- В любой момент всё можно вернуть в исходный вид. Для этого нужно кликнуть на иконку в правом верхнем углу этого окошка.
В данном случае мы смогли сортировать отчет по месяцам, поскольку у нас существовало соответствующее поле. Но для работы с датами есть более мощный инструмент.
Вставить временную шкалу
Если вы кликните на соответствующую кнопку на панели инструментов, то, скорее всего, увидите вот такую ошибку. Дело в том, что в нашей таблице нет ячеек, у которых будет формат данных «Дата» в явном виде.
В качестве примера создадим небольшую таблицу с различными датами.
Затем нужно будет построить сводную таблицу.
Снова переходим на вкладку «Вставка». Кликаем на иконку «Таблица». В появившемся подменю выбираем нужный нам вариант.
- Затем нас попросят выбрать диапазон значений.
- Для этого достаточно выделить всю таблицу целиком.
- Сразу после этого адрес подставится автоматически. Здесь всё очень просто, поскольку рассчитано для чайников. Для завершения построения нажмите на кнопку «OK».
- Редактор Excel предложит нам всего один вариант, поскольку таблица очень простая (для примера больше и не нужно).
- Попробуйте снова нажать на иконку «Вставить временную шкалу» (она расположена на вкладке «Анализ»).
- На этот раз никаких ошибок не будет. Вам предложат выбрать поле для сортировки. Поставьте галочку и нажмите на кнопку «OK».
- Благодаря этому появится окошко, в котором можно будет выбирать нужную дату при помощи бегунка.
- Выбираем другой месяц и данных нет, поскольку все расходы в таблице указаны только за март.
Обновить
Если вы внесли какие-нибудь изменения в исходные данные и по каким-то причинам это не отобразилось в сводной таблице, вы всегда можете обновить её вручную. Для этого достаточно нажать на соответствующую кнопку на панели инструментов.
Источник данных
Если вы решили изменить поля, на основе которых должно происходить построение, то намного проще сделать это в настройках, а не удалять таблицу и создавать её заново с учетом новых предпочтений.
Для этого нужно нажать на иконку «Источник данных». Затем выбрать одноименный пункт меню.
В результате этого появится окно, в котором можно заново выделить нужное количество информации.
Действия
При помощи этого инструмента вы сможете:
- очистить таблицу;
- выделить;
- переместить её.
Вычисления
Если расчетов в таблице недостаточно или они не отвечают вашим потребностям, вы всегда можете внести свои изменения. Нажав на иконку этого инструмента, вы увидите следующие варианты.
К ним относятся:
- вычисляемое поле;
- вычисляемый объект;
- порядок вычислений (в списке отображаются добавленные формулы);
- вывести формулы (информации нет, так как нет добавленных формул).
Сервис
Здесь вы сможете создать сводную диаграмму либо изменить тип рекомендуемой таблицы.
Показать
При помощи этого инструмента можно настроить внешний вид рабочего пространства редактора.
Благодаря этому вы сможете:
- настроить отображение боковой панели со списком полей;
- включить или выключить кнопки «плюс/мину»с;
- настроить отображение заголовков полей.
Конструктор
При работе со сводными таблицами помимо вкладки «Анализ» также появится еще одна – «Конструктор». Здесь вы сможете изменить внешний вид вашего объекта вплоть до неузнаваемости по сравнению с вариантом по умолчанию.
Можно настроить:
- промежуточные итоги:
- не показывать;
- показывать все итоги в нижней части;
- показывать все итоги в заголовке.
- общие итоги:
- отключить для строк и столбцов;
- включить для строк и столбцов;
- включить только для строк;
- включить только для столбцов.
- макет отчета:
- показать в сжатой форме;
- показать в форме структуры;
- показать в табличной форме;
- повторять все подписи элементов;
- не повторять подписи элементов.
- пустые строки:
- вставить пустую строку после каждого элемента;
- удалить пустую строку после каждого элемента.
- параметры стилей сводной таблицы (здесь можно включить/выключить каждый пункт):
- заголовки строк;
- заголовки столбцов;
- чередующиеся строки;
- чередующиеся столбцы.
- настроить стиль оформления элементов.
Для того чтобы увидеть больше различных вариантов, нужно кликнуть на треугольник в правом нижнем углу этого инструмента.
Сразу после этого появится огромный список. Можете выбрать что угодно. При наведении на каждый из шаблонов ваша таблица будет меняться (это сделано для предварительного просмотра). Изменения не вступят в силу, пока вы не кликните на что-нибудь из предложенных вариантов.
Помимо этого, при желании, вы можете создать свой собственный стиль оформления.
Сортировка значений
Также тут можно изменить порядок отображения строк. Иногда это нужно для удобства анализа расходов. Особенно, если список очень большой, поскольку необходимую позицию проще найти по алфавиту, чем листать список по несколько раз.
Для этого нужно сделать следующее.
- Кликните на треугольник около нужного поля.
- В результате этого вы увидите следующее меню. Здесь вы можете выбрать нужный вариант сортировки («от А до Я» или «от Я до А»).
Если стандартного варианта недостаточно, вы можете в этом же меню кликнуть на пункт «Дополнительные параметры сортировки».
В результате этого вы увидите следующее окно. Для более детальной настройки нужно нажать на кнопку «Дополнительно».
Здесь всё настроено в автоматическом режиме. Если вы уберете эту галочку, то сможете указать необходимый вам ключ.
Сводные таблицы в Excel 2003
Описанные выше действия подходят для современных редакторов (2007, 2010, 2013 и 2016 года). В старой версии всё выглядит иначе. Возможностей, разумеется, там намного меньше.
Для того чтобы создать сводную таблицу в Экселе 2003 года, нужно сделать следующее.
- Перейти в раздел меню «Данные» и выбрать соответствующий пункт.
- В результате этого появится мастер для созданий подобных объектов.
- После нажатия на кнопку «Далее» откроется окно, в котором нужно указать диапазон ячеек. Затем снова нажимаем на «Далее».
- Для завершения настроек жмем на «Готово».
- В результате этого вы увидите следующее. Здесь нужно перетащить поля в соответствующие области.
- К примеру, может получиться вот такой результат.
Становится очевидно, что создавать подобные отчеты намного лучше в современных редакторах.
Заключение
В данной статье были рассмотрены все тонкости работы со сводными таблицами в редакторе Excel. Если у вас что-то не получается, возможно, вы выделяете не те поля или же их очень мало – для создания такого объекта необходимо несколько столбцов с повторяющимися значениями.
Если данного самоучителя вам недостаточно, дополнительную информацию можно найти в онлайн справке компании Microsoft.
Видеоинструкция
Для тех, у кого всё равно остались вопросы без ответов, ниже прилагается видеоролик с комментариями к описанной выше инструкции.
Если вам по работе или учёбе приходится погружаться в океан цифр и искать в них подтверждение своих гипотез, вам определённо пригодятся эти техники работы в Microsoft Excel. Как их применять — показываем с помощью гифок.
Юлия Перминова
Тренер Учебного центра Softline с 2008 года.
1. Сводные таблицы
Базовый инструмент для работы с огромным количеством неструктурированных данных, из которых можно быстро сделать выводы и не возиться с фильтрацией и сортировкой вручную. Сводные таблицы можно создать с помощью нескольких действий и быстро настроить в зависимости от того, как именно вы хотите отобразить результаты.
Полезное дополнение. Вы также можете создавать сводные диаграммы на основе сводных таблиц, которые будут автоматически обновляться при их изменении. Это полезно, если вам, например, нужно регулярно создавать отчёты по одним и тем же параметрам.
Как работать
Исходные данные могут быть любыми: данные по продажам, отгрузкам, доставкам и так далее.
- Откройте файл с таблицей, данные которой надо проанализировать.
- Выделите диапазон данных для анализа.
- Перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица» (для macOS на вкладке «Данные» в группе «Анализ»).
- Должно появиться диалоговое окно «Создание сводной таблицы».
- Настройте отображение данных, которые есть у вас в таблице.
Перед нами таблица с неструктурированными данными. Мы можем их систематизировать и настроить отображение тех данных, которые есть у нас в таблице. «Сумму заказов» отправляем в «Значения», а «Продавцов», «Дату продажи» — в «Строки». По данным разных продавцов за разные годы тут же посчитались суммы. При необходимости можно развернуть каждый год, квартал или месяц — получим более детальную информацию за конкретный период.
Набор опций будет зависеть от количества столбцов. Например, у нас пять столбцов. Их нужно просто правильно расположить и выбрать, что мы хотим показать. Скажем, сумму.
Можно её детализировать, например, по странам. Переносим «Страны».
Можно посмотреть результаты по продавцам. Меняем «Страну» на «Продавцов». По продавцам результаты будут такие.
2. 3D-карты
Этот способ визуализации данных с географической привязкой позволяет анализировать данные, находить закономерности, имеющие региональное происхождение.
Полезное дополнение. Координаты нигде прописывать не нужно — достаточно лишь корректно указать географическое название в таблице.
Как работать
- Откройте файл с таблицей, данные которой нужно визуализировать. Например, с информацией по разным городам и странам.
- Подготовьте данные для отображения на карте: «Главная» → «Форматировать как таблицу».
- Выделите диапазон данных для анализа.
- На вкладке «Вставка» есть кнопка 3D-карта.
Точки на карте — это наши города. Но просто города нам не очень интересны — интересно увидеть информацию, привязанную к этим городам. Например, суммы, которые можно отобразить через высоту столбика. При наведении курсора на столбик показывается сумма.
Также достаточно информативной является круговая диаграмма по годам. Размер круга задаётся суммой.
3. Лист прогнозов
Зачастую в бизнес-процессах наблюдаются сезонные закономерности, которые необходимо учитывать при планировании. Лист прогноза — наиболее точный инструмент для прогнозирования в Excel, чем все функции, которые были до этого и есть сейчас. Его можно использовать для планирования деятельности коммерческих, финансовых, маркетинговых и других служб.
Полезное дополнение. Для расчёта прогноза потребуются данные за более ранние периоды. Точность прогнозирования зависит от количества данных по периодам — лучше не меньше, чем за год. Вам требуются одинаковые интервалы между точками данных (например, месяц или равное количество дней).
Как работать
- Откройте таблицу с данными за период и соответствующими ему показателями, например, от года.
- Выделите два ряда данных.
- На вкладке «Данные» в группе нажмите кнопку «Лист прогноза».
- В окне «Создание листа прогноза» выберите график или гистограмму для визуального представления прогноза.
- Выберите дату окончания прогноза.
В примере ниже у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не числа, а именно временные периоды (то есть не 5 марта 2013 года, а март 2013-го).
Для прогноза на 2014 год вам потребуются два ряда данных: даты и соответствующие им значения показателей. Выделяем оба ряда данных.
На вкладке «Данные» в группе «Прогноз» нажимаем на «Лист прогноза». В появившемся окне «Создание листа прогноза» выбираем формат представления прогноза — график или гистограмму. В поле «Завершение прогноза» выбираем дату окончания, а затем нажимаем кнопку «Создать». Оранжевая линия — это и есть прогноз.
4. Быстрый анализ
Эта функциональность, пожалуй, первый шаг к тому, что можно назвать бизнес-анализом. Приятно, что эта функциональность реализована наиболее дружественным по отношению к пользователю способом: желаемый результат достигается буквально в несколько кликов. Ничего не нужно считать, не надо записывать никаких формул. Достаточно выделить нужный диапазон и выбрать, какой результат вы хотите получить.
Полезное дополнение. Мгновенно можно создавать различные типы диаграмм или спарклайны (микрографики прямо в ячейке).
Как работать
- Откройте таблицу с данными для анализа.
- Выделите нужный для анализа диапазон.
- При выделении диапазона внизу всегда появляется кнопка «Быстрый анализ». Она сразу предлагает совершить с данными несколько возможных действий. Например, найти итоги. Мы можем узнать суммы, они проставляются внизу.
В быстром анализе также есть несколько вариантов форматирования. Посмотреть, какие значения больше, а какие меньше, можно в самих ячейках гистограммы.
Также можно проставить в ячейках разноцветные значки: зелёные — наибольшие значения, красные — наименьшие.
Надеемся, что эти приёмы помогут ускорить работу с анализом данных в Microsoft Excel и быстрее покорить вершины этого сложного, но такого полезного с точки зрения работы с цифрами приложения.
Читайте также:
- 10 быстрых трюков с Excel →
- 20 секретов Excel, которые помогут упростить работу →
- 10 шаблонов Excel, которые будут полезны в повседневной жизни →
#Руководства
- 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
Узнать больше
