Наверняка в вакансиях вы видели похожее требование: «Знание Excel на уровне продвинутого пользователя (ВПР, сводные таблицы, формулы)». Звучит сложно, но на самом деле освоить эти функции довольно легко. Начнем со сводных таблиц: разберемся, что это такое, зачем они нужны и как их делать.
Что такое сводная таблица Excel и почему это удобно
Сводная таблица в Excel — это мощный инструмент для анализа и обработки больших объемов данных. Он помогает понять, как данные соотносятся друг с другом, выявить тенденции, закономерности, сгруппировать их по выбранным критериям и показать результат. Таблица позволяет быстро создать сводный отчет из сотен тысяч строк данных за несколько кликов.
Сводные таблицы создал разработчик Пито Салас в 1986 году. Разрабатывая программу для работы с электронными таблицами Lotus Improv, он начал замечать в данных закономерности, которые возникали при объединении разных таблиц. Он понял, что это может быть полезным инструментом для анализа данных, и создал сводные таблицы. Microsoft добавила функциональность сводных таблиц в Excel только в 1994 году. С тех пор на сводных таблицах держится многое в самых разных отраслях: финансах, маркетинге, продажах, закупках.
Например, маркетолог составляет маркетинговый план, в котором подробно описывает продвижение в разных каналах. Чтобы руководитель мог быстро и легко оценить эффективность этого плана, маркетолог создает сводную таблицу, которая объединяет информацию из всех задействованных документов. В этой таблице можно сравнить доходы, расходы и эффективность разных каналов в одном месте, без необходимости переключаться между документами.
Сводные таблицы можно использовать даже для анализа личного бюджета. Например, чтобы посмотреть, сколько денег потратили на каждую категорию товаров в течение года, вы можете создать сводную таблицу, где строки будут группироваться по категории товаров, а столбцы — по месяцам.
Как сделать простую сводную таблицу
Начинаем работу
Попробуем сделать простую сводную таблицу. В нашем примере будет небольшая IT-компания, которая занимается разработкой на заказ. Проектов много, поэтому без сводных таблиц не обойтись. В нашей таблице будут колонки «Название проекта», «Тип проекта», «Команда», которая занимается этим проектом, «Бюджет», который выделил заказчик, и «Срок (в месяцах)» — время, которое уйдет на выполнение проекта.
Чтобы создать сводную таблицу, перейдите в раздел «Вставка» и выберите «Сводная таблица».
Откроется окно создания сводной таблицы. По умолчанию оно выглядит так:
Выберем нужную нам таблицу. Чтобы это сделать, прописываем название листа (у нас «Проекты») и диапазон, который занимает таблица. Ячейки можно прописать вручную или выделить нужную область мышкой, тогда диапазон пропишется автоматически. Удобнее создать сводную таблицу на новом листе — так данные источника не будут вам мешать.
Нажимаем «Ок» и видим, что мы оказались на новом листе. Справа появился конструктор сводных таблиц. В основной области перечислены названия столбцов, которые были в нашей исходной таблице. Их можно выбирать — ставить галочки рядом или переносить мышкой. Фильтры помогут скрыть или отобразить нужные данные, столбцы и строки отвечают за внешний вид таблицы: данные будут разделены по столбцам или строкам соответственно, а значения нужны для вычисления данных.
Настраиваем сводную таблицу
Допустим, руководитель нашей компании хочет посмотреть, какие проекты ведут команды. Для этого он выбирает столбцы «Команда» и «Название проекта». По умолчанию они попадают в поле «Строки».
Теперь наша сводная таблица выглядит так:
Если выбрать сначала «Название проекта», а потом «Команда», таблица выглядит совсем по-другому. Порядок выбора столбцов важен.
Посмотрим на фильтры. Наша исходная таблица небольшая, команд немного, поэтому вывести всё и сразу довольно удобно. Если бы команд или проектов было больше, то такое отображение не подошло бы. Здесь выручат фильтры. Например, можно добавить «Команду» в поле «Фильтры».
Тогда все наши команды станут пунктами выпадающего меню, а проекты будут располагаться ниже и только для выбранной команды. Красиво, удобно, наглядно.
А что, если руководитель захочет посмотреть, сколько заработает каждая команда на проектах? Выбираем «Команда», выбираем «Бюджет». Обратите внимание, что «Команда», как обычно, оказалась в поле «Строки», а вот «Бюджет» попал в «Значения» автоматически, потому что в этом столбце были записаны числа.
Если мы перенесем «Команду» в «Столбцы», то увидим, что внешний вид таблицы изменился. Выбор внешнего вида зависит от личных предпочтений и от цели создания таблицы.
При работе с числовыми значениями часто применяются фильтры. Например, так наша IT-компания может узнать, какое из направлений ей приносит больше денег. Для этого нужно переместить «Тип проекта» в поле «Фильтры», а «Бюджет» сам попадет в «Значения». Теперь можно выбирать направление и смотреть, какое из них самое выгодное для компании.
Можно перенести «Тип проекта» в «Столбцы» и увидеть сразу все направления в одной таблице.
Так создаются простые сводные таблицы. Если необходимо выполнить более глубокий анализ данных, можно использовать более сложные варианты. Они позволяют работать с данными в различных измерениях, выполнять группировку, вычислять проценты, средние значения и многое другое.
Как обновить данные в сводной таблице
Что делать, если исходные данные изменились? Например, на какой-то проект назначили Team 2, а не Team 1? Перейдите в раздел «Анализ сводной таблицы» и нажмите «Обновить». Никаких подтверждающих окон не появится, но актуальные данные подтянутся в вашу сводную таблицу.
Если же ваша таблица изменилась существенно, например добавились новая строка или новый столбец, то есть изменился ее диапазон, нужно выбрать новый. Для этого перейдите в раздел «Анализ сводной таблицы» и нажмите «Источник данных». Вы окажетесь на листе с вашими исходными данными. Здесь появится окно, в котором вы сможете указать новый диапазон (или выделить нужную область мышкой). Нажмите «Ок» и продолжайте работу над сводной таблицей.
Как сделать сводную таблицу из нескольких листов
Сводные таблицы позволяют объединять данные из нескольких источников в одну таблицу для удобного анализа и сравнения. Например у вас есть информация, которая хранится на разных листах. В сводной таблице можно просматривать данные, не переключаясь между вкладками Excel.
Предположим, у нас есть еще одна таблица на другом листе — с тимлидами (руководителями команд). Компания у нас маленькая, поэтому руководители команд отвечают за проекты, которые ведет их команда.
Руководитель решает посмотреть, за сколько проектов сейчас отвечает каждый тимлид. Здесь пригодится сводная таблица. Но для начала нужно изменить наши таблицы — сделать их «умными». Для этого в разделе «Вставка» нужно выбрать «Таблица».
После этого — выделить нужный диапазон или прописать расположение данных вручную. Обязательно выбирайте опцию «Таблица с заголовками». Так вы сможете изменить название таблицы с системного «Таблица1» на свое.
Делаем это для обеих таблиц. Теперь они выглядят так:
После этого создаем сводную таблицу для наших исходных данных: таблицы с проектами. Обязательно ставим галочку в окошке «Добавить эти данные в модель данных». Мы видим уже привычный конструктор сводных таблиц. Но как же связать данные из наших двух таблиц? Обратимся к конструктору справа. Перейдем в раздел «Все».
Увидим обе наши таблицы! «Проекты» и «Тимлиды» — это названия, которые мы придумали.
Кликаем на каждый, раскрываем списки и видим названия столбцов. Чтобы посмотреть нагрузку на тимлидов, в списке «Проекты» мы выберем столбец «Название проекта», в списке «Тимлиды» — столбец «Тимлид». Разместим их в полях «Столбцы», чтобы придать таблице нужный вид, и в поле «Значение», чтобы автоматически посчитать количество проектов. У нас появляется желтое поле вверху.
Если выбрать «Автообнаружение», Excel сам поймет, что мы хотим сделать. Но при сложных процедурах это может не сработать. Выберем «Создать» и сделаем все вручную.
Открылось окошко создания отношений. В «Таблица» выберем основную таблицу (с проектами), в «Связанная таблица» — второй лист (с тимлидами). В «Столбец» и «Связанный столбец» должны быть одни и те же столбцы. Чтобы связать две таблицы, в них должен быть общий столбец, по которому и будет создано отношение. У нас это «Команда».
Вуаля! После нажатия «Ок» увидим желаемую таблицу и поймем, что тимлид Петров А. загружен меньше всех и мы можем дать ему еще проектов!
Полезные функции сводной таблицы
Расскажем про несколько функций, которые могут быть полезны при работе со сводными таблицами.
Вычисляемые поля
Эта функция нужна для дополнительных математических действий с данными сводной таблицы в Excel. Допустим, в компании принято, что 3% от всего бюджета команды идет на ее расходы: пятничные посиделки с пиццей, подарки на дни рождения и прочие маленькие радости. Это тоже можно посчитать в сводных таблицах. Сделаем распределение бюджетов по командам, так же как в прошлом примере.
В разделе «Анализ сводной таблицы» нужно найти выпадающее меню «Поля, элементы и наборы». Кликаем и выбираем «Вычисляемое поле».
Появляется окно, в котором можно написать имя нового столбца, у нас это «Расходы команды», и выбрать столбцы, которые понадобятся для расчета нового. Мы выбираем «Бюджет» (он сам подставится в поле «Формула») и дописываем умножение на 0,03 (3% от бюджета).
После нажатия «Ок» в нашей сводной таблице появился новый столбец. Все посчитано!
Рекомендуемые таблицы
Рядом с иконкой создания сводной таблицы есть и другая опция — «Рекомендуемые сводные таблицы». Эта функция предлагает шаблоны сводных таблиц для данных. Будет полезна тем, кто начинает работать со сводными таблицами. Можно не только сделать то, что нужно, но и посмотреть, в каких полях конструктора расположены разные столбцы, и глубже понять принцип работы сводных таблиц.
Подводя итог
В этой статье мы рассмотрели, как создать сводную таблицу в Excel. Если что-то не получилось сразу, не расстраивайтесь, попробуйте еще раз. И через некоторое время вы сможете сказать: «Я люблю Excel!».
Напоследок несколько советов, которые помогут вам использовать сводные таблицы более эффективно:
- Продумайте, какие данные вы хотите проанализировать. Что вы хотите узнать? Какая информация вам нужна для этого?
- Выберите правильные поля для вашей сводной таблицы. Они определяют, как будут группироваться ваши данные и какие вычисления будут выполняться.
- Используйте фильтры и сортировку, чтобы настроить свою сводную таблицу. Фильтры и сортировка могут помочь вам сосредоточиться на конкретных данных или увидеть данные в новом свете.
- Добавьте диаграммы и графики к своей сводке. Они помогут визуализировать данные и сделать их более понятными.
А если захотите пойти дальше в освоении Excel — можете познакомиться с курсом «Excel + Google-таблицы».
#Руководства
-
0
Как систематизировать тысячи строк и преобразовать их в наглядный отчёт за несколько минут? Разбираемся на примере с квартальными продажами автосалона
Иллюстрация: Meery Mary для Skillbox Media
Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.
Сводная таблица — инструмент для анализа данных в Excel. Она собирает информацию из обычных таблиц, обрабатывает её, группирует в блоки, проводит необходимые вычисления и показывает итог в виде наглядного отчёта. При этом все параметры этого отчёта пользователь может настроить под себя и свои потребности.
Разберёмся, для чего нужны сводные таблицы. На конкретном примере покажем, как их создать, настроить и использовать. В конце расскажем, можно ли делать сводные таблицы в «Google Таблицах».
Подписывайтесь на наш телеграм-канал «Котлер любит котиков | Школа маркетинга и управления Skillbox». Здесь мы подкидываем идеи для нестандартной рекламы, дарим книги, обсуждаем новости и важные темы для диджитал-специалистов и руководителей.
А ещё постим мемчики и вакансии, которые могут вас заинтересовать. Например, недавно выкладывали реальную вакансию директора по расслабону с зарплатой 200 000 рублей в месяц ?
Сводные таблицы удобно применять, когда нужно сформировать отчёт на основе большого объёма информации. Они суммируют значения, расположенные не по порядку, группируют данные из разных участков исходной таблицы в одном месте и сами проводят дополнительные расчёты.
Вид сводной таблицы можно настраивать под себя самостоятельно парой кликов мыши — менять расположение строк и столбцов, фильтровать итоги и переносить блоки отчёта с одного места в другое для лучшей наглядности.
Разберём на примере. Представьте небольшой автосалон, в котором работают три менеджера по продажам. В течение квартала данные об их продажах собирались в обычную таблицу: модель автомобиля, его характеристики, цена, дата продажи и Ф. И. О. продавца.
Скриншот: 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 Таблицы“ для всех»
Начать учиться
Сводная таблица помогает быстро обработать большой объем данных и найти взаимосвязи. Например, в каком месяце удалось сэкономить больше всего денег или на какие категории расходов приходятся основные траты. Расскажем, как сводные таблицы помогут с личным финансовым учетом.
Вместо Excel мы пользуемся сервисом Google Sheets, потому что он бесплатный и работает в онлайне: можно заходить с компьютера и телефона. Если Excel привычнее — никаких проблем, в нем те же функции.
Что такое сводная таблица
Это стандартный инструмент Excel или Google Sheets для анализа данных. Он облегчает жизнь тем, кто давно ведет финансовый учет и накопил сотни, а то и тысячи строк с данными. Сводная таблица собирает все эти данные, группирует их, проводит нужные вычисления и готовит компактный отчет.
Как может выглядеть такая таблица:
В ней подсчитаны расходы по дням за целый год — в процентном соотношении и абсолютных суммах. И все это за несколько кликов.
Что нужно сделать перед созданием сводной таблицы
Чтобы сводная таблица работала, нужно подготовить данные:
- Добавить заголовки. У каждого столбика таблицы должен быть заголовок, например «День недели» и «Расходы».
- Привести форматы к единообразию. В каждом столбике могут быть данные лишь одного формата, например только названия или только суммы.
- Заполнить все. В таблице не может быть пустых ячеек или строк.
Вот пример таблицы, которая отвечает всем правилам:
Допустим, вы записываете расходы посуточно и хотите узнать, в какие дни тратите больше всего денег. В примере выше это несложно сделать на калькуляторе. Но что делать, если вы вели учет целый год и таблица состоит из 365 строк? Здесь и поможет сводная таблица, давайте создадим такую за два простых шага.
Шаг 1. Создаем таблицу
Нажмите Вставка → Создать сводную таблицу. Появится окошко, в котором нужно сначала выбрать диапазон данных. Это все ячейки, в которых находятся нужные нам данные, а именно дни недели и суммы расходов. Чтобы выбрать диапазон, нажмите на изображение таблицы:
Диапазон можно ввести вручную, но проще довериться рекомендациям Google Sheets и выбрать первый вариант в списке:
Нажмите ОК → Создать, таблица появится на новом листе. Перейдем к настройкам.
Шаг 2. Настраиваем таблицу
Добавим в таблицу нужные данные. Дни недели будут строками, а суммы расходов — значениями:
Вот так за несколько кликов видим, что в течение рабочей недели и в субботу расходы равномерны, а по воскресеньям тратится на 20–25% больше, чем в остальные дни:
В сводной таблице дни недели и другие текстовые значения отображаются в алфавитном порядке.
Частые вопросы
Как обновлять сводную таблицу?
Таблица не обновляется автоматически. Если вы продолжаете вести финансовый учет и вносите дополнительные расходы, они не будут отображаться в сводной таблице, потому что на первом шаге мы ограничили диапазон. Чтобы добавить новые строки в таблицу, нужно изменить диапазон:
Как учитывать доходы от инвестиций?
При создании сводной таблицы важно, чтобы в ней не было пустых ячеек и строк, иначе ничего не сработает. В инвестициях такая ситуация невозможна, потому что доход по некоторым финансовым инструментам не поступает каждый день: например, по депозитам или сдаче недвижимости. Поскольку строки нельзя оставлять пустыми, нужно каждый раз вводить ноль в графу доходов.
Что важно запомнить
- Сводная таблица помогает быстро проанализировать большой объем данных и собрать отчет в компактном виде.
- Таблицу нужно заполнять по правилам — иначе в результате будут ошибки.
- Обновлять сводную таблицу можно только вручную.
Excel для личных финансов
В современном мире, где информация играет ключевую роль, аналитика данных становится неотъемлемой частью успешного бизнеса. Представьте, что у вас под рукой есть мощный инструмент для обработки больших объемов информации с целью извлечения ценных инсайтов. Грамотная организация и систематизация строк с данными позволяет не только упростить, но и сделать анализ более прозрачным и управляемым.
Неважно, работаете ли вы в крупной корпорации или стартапе, умение быстро и эффективно анализировать информацию может стать вашим главным конкурентным преимуществом. Для того чтобы наглядно увидеть, как ваши данные работают на вас, требуется применение детальных пошаговых методик. Каждый шаг в этом процессе важен, он помогает выявить основные тенденции и связи. В результате ваши замыслы легко трансформируются в стратегические решения.
Применение мощных формул, таких как VLOOKUP, IF, SUM и COUNT, в правильной структуре строк и колонок позволяет добиться детального и глубокого анализа, который открывает перед вами новые горизонты. В этой статье мы намерены предоставить максимально детализированную и понятную инструкцию об эффективных методах анализа, способных преобразовать ваш подход к переработке данных. Будьте готовы к путешествию в мир аналитики, где каждый шаг играет значительную роль в конечном результате.
С чего начать создание сводной таблицы
Первым делом убедитесь, что ваши данные представлены в виде списка или таблицы, где каждая строка содержит единицу информации, а каждый столбец – отдельное поле данных. Обязательно проверьте наличие заголовков столбцов, так как они будут использоваться для наименования категорий. Все дублирующиеся данные следует удалить или корректно обработать, чтобы итоговый анализ был корректным.
Грамотная структуризация информации важна для комфортной обработки. Например, если у вас есть дата в неструктурированном виде, преобразуйте её в удобный формат с помощью функции =TEXT(A1,ДД.ММ.ГГГГ), где A1 представляет ячейку с исходным значением.
Также обратите внимание на отсутствие пропущенных значений в ключевых полях. Недостающие данные могут привести к искажению анализа. Используйте механизм фильтрации для проверки на наличие пустых ячеек и заменяйте их данными, где это возможно, или удаляйте, если они не несут критической информации.
После уверенности в чистоте и правильной организации данных следующим шагом станет выбор полей для анализа. Решите, какие параметры имеют ключевую значимость и требуют детального изучения. Это поможет сконцентрировать внимание на важнейших аспектах и не распыляться на второстепенные элементы.
Соблюдая эти рекомендации, вы обеспечите себе надежную основу для эффективного анализа, избегая распространенных ошибок и экономя время на последующих этапах обработки данных. Грамотная подготовка – залог успешного и быстрого выполнения аналитической задачи.
Подготовка данных для анализа
Перед началом аналитического процесса особое внимание следует уделить подготовке данных. Этот этап поможет обеспечить надежность результатов и упростит дальнейшую работу с информацией. Структурированные и очищенные строки и столбцы данных помогут избежать ошибок и недопонимания в процессе обработки. Рассмотрим основные шаги данного процесса.
Для начала важно убедиться, что ваши данные полны и непротиворечивы. Проверьте корректность числовых значений и форматов ячеек. Например, если вы работаете с финансовыми данными, убедитесь, что все значения представлены в одном формате валюты. Обратите внимание и на даты – они должны быть в единообразном форматировании, чтобы избежать путаницы при дальнейшей обработке.
Еще один важный аспект – удаление дубликатов. Избыточная информация может значительно исказить результаты анализа. Воспользуйтесь функциональными возможностями программы для поиска и удаления повторяющихся строк. Эту задачу легко решить с помощью функции Удалить дубликаты, которая находится во вкладке «Данные».
Разделение данных на логические сегменты также может оказаться полезным. Если ваша таблица содержит сложные данные, их можно разделить на несколько разделов по ключевым характеристикам, что упростит анализ. Например, используйте формулу TEXT, чтобы извлечь части текста: =TEXT(A1, Формат).
Наведение порядка на этапах подготовки данных подготавливает удобную почву для углубленного анализа. Принимая эти меры, вы создаёте условия для эффективного и точного анализа, который позволит принимать обоснованные решения.
Использование мастера сводной таблицы
Мастер анализа данных гарантирует легкость и удобство в обработке информации, открывает двери для более глубокого изучения без необходимости погружаться в сложные методологии. Благодаря интуитивно понятному интерфейсу, пользователь может эффективно структурировать информацию, получая новые инсайты всего в несколько шагов.
Начать работу с мастером просто: сначала выберите нужный диапазон данных, выделив строки и колонки, содержащие интересующую вас информацию. После этого в меню выберите соответствующую опцию для запуска мастера. Мастер предложит несколько вариантов выбора, от которых зависит дальнейшее представление ваших данных.
После распределения данных следует выбор показателей, которые будут отображаться. Это могут быть суммы, средние значения, количество записей и другие агрегированные данные. Введите формулы, такие как SUM или COUNTA, чтобы подчеркнуть важные тенденции.
Заключительным этапом является использование фильтров и сортировки для улучшения читаемости и визуализации. Это может включать исключение нерелевантных данных или выделение ключевых метрик. Благодаря этому, итоговые таблицы становятся не только инструментом анализа, но и эффективным инструментом презентации.
Настройка полей и областей
- Размещение полей: Перетащите нужные элементы в области строк, столбцов, значений или фильтров. Каждый из этих разделов выполняет свою функцию:
- Строки: Разделение данных по строкам позволяет создать категории, которые будут отображены вертикально.
- Столбцы: Эти поля формируют горизонтальные разбивки, помогая структурировать информацию.
- Значения: В любом отчете важна числовая информация – суммирование, среднее или количество. Здесь размещаются числовые данные для агрегации.
- Фильтры: Удобный способ ограничить видимые данные только нужными параметрами. Это улучшает фокусировку анализа на ключевых аспектах.
Для более глубокой настройки используйте возможности изменения функции подсчета. Например, помимо обычной суммы – расчет среднего значения:
=AVERAGE(числовое_поле)
- Щелкните на любое числовое поле в области значений.
- Выберите нужную функцию: сумма, среднее, минимальное или максимальное значение.
- При необходимости используйте пользовательские формулы.
Для улучшения интерпретации данных добавляйте пользовательские метки и изменяйте порядок полей. Можно применить сортировку или фильтрацию для выделения ключевых моментов.
Настройка полей и областей – фундаментальный этап, определяющий, насколько адаптированы ваши данные для дальнейшего анализа. Внимательная конфигурация этих элементов обеспечит ясность и результативность вашего исследования.
Применение фильтров и сортировки
Фильтрация позволяет временно скрыть ненужные данные, чтобы сосредоточиться на элементах, имеющих приоритетное значение. Это ускоряет поиск и сравнение специфических наборов данных. Сортировка, в свою очередь, организует информацию по заданным критериям, упрощая анализ последовательностей и тенденций.
Для применения фильтра необходимо:
- Выделить диапазон данных.
- В меню выбрать функцию фильтрации.
- Установить условия для отображаемых элементов. Например, если интересуют только продажи за конкретный регион, выберите его в выпадающем списке.
Сортировка может быть настроена как в порядке возрастания, так и убывания. Чтобы наглядно представить изменения, используйте следующий подход:
| Операция | Действие |
|---|---|
| Выбор данных | Выделите нужные ячейки или столбцы. |
| Установка порядка | Воспользуйтесь панелью инструментов, выбрав восходящий или нисходящий порядок. |
| Сохранение результата | Проверьте изменения и при необходимости пересохраните рабочий файл. |
Используйте следующую формулу для определения верхних значений в наборе данных:
=НАИМЕНЬШИЙ(Диапазон; N)
Эта функция поможет выделить определенное количество наименьших элементов в заданном диапазоне. С помощью фильтров и сортировки анализ данных становится более точным и интуитивным, позволяя сосредоточиться на самом важном.
Визуализация данных с помощью диаграмм
После обработки и анализа данных наступает этап представления результатов в наглядной форме. Диаграммы позволяют быстро оценить распределение информации, выявить тенденции и связи между показателями. Они помогают визуально структурировать и упрощают восприятие информации, что делает их незаменимым инструментом для принятия обоснованных решений.
В методах оснащения диаграммами могут быть задействованы различные виды графических представлений, такие как гистограммы, круговые и линейные графики, которые способствуют эффективному отображению данных. Выбор типа диаграммы определяется характером и структурой информации, что повышает точность анализа и помогает быстрее интерпретировать результаты.
Начинайте с выделения диапазона данных, который вы планируете визуализировать. Убедитесь, что в рамках строк и столбцов закодированы необходимые метрики и категории. Затем, используя инструмент для вставки диаграмм, который обычно находится на панели, выберите наиболее подходящий тип графика. К примеру, гистограммы отлично подходят для сравнений между группами, а круговые диаграммы помогут в оценке относительных долей категорий.
Для дальнейшей настройки диаграммы воспользуйтесь параметрами редактирования. Изменяйте цвета, создавайте подписи к данным и настраивайте оси для более подробного и точного представления. Форматирование диаграммы значительно влияет на читаемость и является ключевым фактором для дальнейшего анализа.
Включение интерактивных элементов, таких как срезы и сегменты, может помочь в фильтровке необходимой информации в реальном времени, улучшая качество используемого инструмента. Например, если необходимо отобразить только данные за определённый период, воспользуйтесь инструментами временной сортировки.
Диаграммы — мощный способ сделать данные более доступными и понятными, а хорошо продуманная визуальная структура облегчает проведение качественного анализа, делая информацию выразительной и легкодоступной для дальнейшего использования на практике.
Комментарии
Сводные таблицы Excel, или так называемые Pivot Tabel, — это инструмент обобщения и изучения больших объемов данных, анализа итогов и представления сводных отчетов.
В этой статье мы подробно рассмотрим, чем могут быть полезны сводные таблицы, какие требования предъявляются к исходным данным и наконец построим свою первую сводную таблицу.
Кстати, в нашем учебном центре “РУНО” есть практический курс Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности, на котором можно узнать всё про сводные таблицы, сводные диаграммы, инструменты проверки и подготовки данных и многое другое.
Содержание:
- Зачем нужны сводные таблицы;
- Подготовка данных;
- Инструменты создания таблицы.
Зачем нужны сводные таблицы
Сводные таблицы предназначены для решения ряда задач: представление большого объема данных в удобном для анализа виде; обобщение данных по категориям и подкатегориям; обобщения данных; фильтрации, группировки, сортировки различных фрагментов данных.
Умение работать со сводной таблицей и развитые навыки в самостоятельном построении сводных таблиц — это ключевые черты, которые определяют облик Excel Pro — высококлассного специалиста, профессионала Excel.
В современном мире данных становится не просто много, а очень много. А человеку для принятия решений необходимо видеть информацию в лаконичном, сжатом виде — на одном листе.
Как сжать огромный объем данных до одного листа формата А4, в том числе — с помощью сводных таблиц? Это не так сложно , как может показаться на первый взгляд. Сводная таблица, созданная в Microsoft Excel, поможет выделить основное и сфокусироваться на наиболее ценной информации.
Тема создания сводных таблиц в Excel подробно рассматривается на курсе образовательного центра “Руно” Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности. Пройдите пробный урок на сайте.
Подготовка данных
Основа для проектирования сводной таблицы — это определенный набор значений.
Прежде чем начать работу, нужно подготовить необходимые материалы, которые можно будет впоследствии свести для анализа. Формируя информацию, важно следить за тем, чтобы данные были четко и понятно классифицированы. Например, важно цифры не смешивались с буквенным обозначением, а все столбцы имели заголовки.
Сводная таблица может динамично менять данные. То есть, когда вы вносите корректировки в базу данных (исходная таблица данных), они автоматически меняют вашу сводную таблицу.
Возникает закономерный вопрос: где же применение сводной таблицы даст наибольший эффект?
- когда анализируется база данных по разнообразным критериям (город, номенклатура, персонал, время года и пр.) систему).
- когда ведется работа с огромным количеством статистической или аналитической информации и фильтры с выборкой не могут помочь;
- когда предыдущие два варианта нужно постоянно пересчитывать, обновляя базу данных.
Единственный большой минус во всех сводных таблицах, это то, что она не может быть применена, если данные в таблице не отвечают конкретным условиям. Например:
- каждый столбец должен иметь заголовок шапки;
- все строки и столбцы нужно заполнить;
- для всех столбцов данных должны быть определенные форматы ячеек (для поля “Дата” нужен формат календарной даты, а для поля “Контрагент” — формат текста и т.п.);
- значения в ячейках должны быть “единоличными” (к примеру, “Договор № 23 от 03.09.2016 года” должен быть записан в 3 разных столбцах: “Документ”, “Номер” и “Дата”);
- если вы ведете расходно-доходную табличку, в которой, кроме суммирования, еще есть необходимость вычитания, то и в свою базу первоначальных данных вводите информацию со знаком “-”. Тогда в свёрнутом виде вы получите нужный результат;
- конструкция вашей сводной таблицы должна иметь оптимальный вид.
Если выполнены все вышеуказанные условия, то в результате можно получить настоящий чудо-инструментарий для работы с базой данных.
ВАЖНО!
В Microsoft Excel множество различных функций и команд, которые могут значительно облегчить работу даже уверенному пользователю программы.
Освоив курс Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности, вы приобретете профессиональные навыки, которые сэкономят ваше время и помогут в считанные минуты и без ошибок сформировать даже самые объемные отчеты.
Инструменты создания таблицы
В самом начале работы нам необходимо проверить, правильно ли сформирована таблица. Нужно обратить внимание, все ли столбцы имеют правильное название, имеют соответствующий формат ячеек (текст, числа и т.д.).
Если все верно, приступаем к работе над таблицей.
На панели управления документа Excel выбираем вкладку «Вставка» и получаем на выбор два варианта создания сводной таблицы:
- рекомендуемые сводные таблицы (пункт для начинающих);
- сводная таблица (используется при ручной настройке таблицы, для более опытных пользователей).
Рекомендуемые сводные таблицы
Для начала рассмотрим вариант для начинающих специалистов: когда используются готовые варианты таблиц в документе Excel.
Нам необходимо указать курсоров на таблицу и в меню «Вставка» нажать пиктограмму «Рекомендуемые сводные таблицы». В этом случае Excel сам придумает вариант представления и сделает вам сводную таблицу.
На рисунке — вариантов выбора достаточно много. Остается только подобрать один из предложенных. с этой функцией стоит поэкспериментировать, чтобы улучшить свои практические навыки создания сводных таблиц.
Сводная таблица (мастер сводных таблиц)
Начнем работу с выбора в меню «Вставка», блок «Таблицы», пиктограмма «Сводная таблица». Не забываем при этом указать курсором базу исходных данных или табличку, из которой мы будем делать сводную.
В открывшемся окне мы выбираем несколько условий создания сводной таблицы — это диапазон нужных исходных данных и где следует разместить сводную табличку. А поскольку курсор уже стоял на таблице, Excel быстро и автоматически определил диапазон таблицы и подставил в графу диапазона. Нажимаем «ОК» и получаем:
Как видите, был создан новый «Лист3» и вызван «Конструктор сводных таблиц». В конструкторе Вы указываете какие столбики исходной таблицы, вам нужно перенести в сводную и какие именно вычисление нужно будет над ними произвести и всё это происходит обыкновенным перетаскиванием в необходимую область, заголовка нужной нам таблицы.
В конструкторе Вы указываете какие столбики исходной таблицы, вам нужно перенести в сводную и какие именно вычисление нужно будет над ними произвести и всё это происходит обыкновенным перетаскиванием в необходимую область, заголовка нужной нам таблицы.
Вот мы получили и наш первый результат, но он нас не устраивает так как у нас не суммируется количество фруктов, которые были проданы, а значит, нам нужно с области «СТРОКИ» перетянуть заголовок столбца «Вес, кг» и у нас создаётся та конструкция сводной таблицы, которую мы хотим.
Профессиональный совет:
Магия Excel в том, что буквально двумя-тремя кликами можно развернуть данные, проанализировать их и принять правильное бизнес-решение.
Умея работать с формулами, вы автоматизируете свою работу. Вы тратите один раз время на конструирование формулы, дальше она работает, и вы экономите массу своего времени.
Пройдя курсы Excel дистанционно, вы сможете в короткие сроки освоить работу с программой и успешно применять полученные навыки на практике. Курс ведёт сертифицированный тренер Microsoft.
По завершению дистанционного видеокурса Microsoft Excel 2016/2019. Уровень 2. Расширенные возможности вы сможете:
-
Применять продвинутые инструменты вычисления;
-
Эффективно работать с большими табличными массивами;
-
Анализировать данные с помощью сводных таблиц;
-
Применять новые диаграммы Excel 2019;
Пройдите пробный урок на нашем сайте.
По окончании вы получите удостоверение о повышении квалификации!
Учебная программа Получить доступ
СМОТРИТЕ ВИДЕОУРОКИ ПО ТЕМЕ:
СТАТЬИ ПО ТЕМЕ:
Сортировка в Excel. Автоматизируем свою работу
Как влиться в новый коллектив? Адаптация на новом месте.
Как стать бухгалтером с нуля
КАТАЛОГ КУРСОВ ПО EXCEL:
Microsoft Excel 2016/2019. Уровень 2+3. Специалист. Профессиональные приемы работы в программе
Microsoft Excel 2016/2019. Анализ и прогнозирование данных. Уровень 3. Эксперт
Функция ВПР и сводные таблицы (Комплекс из 4-х курсов)