Урок 8. Простые инструменты анализа данных

В уроке 5, когда мы учились делать таблицы эксель, мы уже начали говорить о работе с большими массивами данных и о том, как упростить управление таблицами. Мы освоили такие инструменты, как сортировка и фильтрация, фиксация строк и столбцов, группировка данных, работа с макросами и функция SUMIF/SUMIFS.

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

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

Содержание:

• Условное форматирование: как выделить важные данные автоматически
• Введение в сводные таблицы: что это такое и как их использовать для анализа
• Простые методы сортировки и фильтрации данных для быстрой работы с таблицами
• Проверочный тест

В подготовке урока мы опирались на материал «Создание сводной таблицы для анализа данных листа» [Microsoft, 2023]. И на материал «Использование условного форматирования для выделения сведений в Excel» [Microsoft, 2022]. Обратите внимание, что под текстом материала есть ссылки на статьи, которые в перспективе помогут вам расширить ваши знания об условном форматировании, когда вы пройдете базовый уровень.

Условное форматирование: как выделить важные данные автоматически

Условное форматирование – это инструмент в Excel, позволяющий автоматически изменять формат ячеек (например, цвет текста, фона или шрифта) в зависимости от содержания этих ячеек или заданных условий.

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

Преимущества условного форматирования:

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

Как выполнить условное форматирование:

• Выделите данные.
• Зайдите на вкладку «Главная» на ленте инструментов Excel.
• Зайдите в группу «Стили» и нажмите на кнопку «Условное форматирование».
• Определитесь с одним из предложенных вариантов.

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

• Правила выделения ячеек – позволяет задать условия (больше, меньше, равно).
• Правила для верхних и нижних значений – выделяет верхние или нижние N значений.
• Цветовые шкалы – применяет цветовую градацию для значений в диапазоне.
• Наборы значков – отображает значки рядом с числами.

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

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

Таким же образом можно сделать форматирование ячеек, содержащих числовые значения, выделив, допустим, те, где число в эксель больше 150:

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

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

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

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

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

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

Введение в сводные таблицы: что это такое и как их использовать для анализа

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

Зачем нужны сводные таблицы:

• Быстрый анализ больших данных – сводные таблицы позволяют легко агрегировать и фильтровать данные, что значительно ускоряет анализ.
• Гибкость в настройке – можно быстро менять поля для отображения различных комбинаций данных без необходимости вручную перестраивать таблицы.
• Автоматические вычисления – сводные таблицы автоматически подсчитывают суммы, средние значения, проценты, минимумы, максимумы и другие статистические данные.
• Визуализация данных – сводные таблицы могут быть связаны с диаграммами для облегчения восприятия данных.
• Фильтрация и детализация – с помощью фильтров сводные таблицы позволяют избирательно анализировать данные. Например, можно выбрать анализ только одного товара или периода.
• Группировка данных (объединение в группу по какому-либо признаку) – сводные таблицы позволяют группировать даты (например, по месяцам, кварталам или годам), а также числовые значения (например, группировать диапазоны доходов).
• Анализ по нескольким измерениям – можно легко увидеть зависимость продаж от региона, времени и продукта одновременно, сравнивая данные в разных разрезах.

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

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

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

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

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

Чем полезна сводная таблица для анализа продаж:

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

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

Чем полезна сводная таблица для анализа расходов:

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

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

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

Простые методы сортировки и фильтрации данных для быстрой работы с таблицами

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

Сортировка по цвету ячеек и шрифтов:

• Выделите столбец, который хотите отсортировать.
• На вкладке «Данные» нажмите на кнопку «Сортировка».
• В диалоговом окне выберите Сортировать по > Цвет ячейки или Цвет шрифта.
• Укажите, какой цвет должен отображаться вверху или внизу списка.

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

Быстрая фильтрация по выделению:

• Щелкните правой кнопкой мышки по ячейке с данными, по которым нужно сделать фильтр.
• В контекстном меню выберите Фильтр > Фильтровать по значению выделенной ячейки.

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

Фильтрация по нескольким условиям в одном столбце:

• Щелкните на стрелку фильтра в заголовке столбца.
• В списке значений снимите галочки для ненужных данных.
• Отметьте несколько нужных значений и нажмите OK.

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

Еще вы можете настроить многоуровневую сортировку данных, если нужно отсортировать их по нескольким критериям (например, сначала по фамилии, а затем по достигнутым показателям).

Сортировка по нескольким уровням:

• Выделите данные, которые хотите отсортировать.
• На вкладке «Данные» нажмите «Сортировка».
• В диалоговом окне выберите «Добавить уровень».
• Укажите параметры сортировки для каждого уровня (например, сначала по фамилии, потом по достигнутым показателям).

Многоуровневая сортировка помогает структурировать данные по нескольким критериям одновременно.

А теперь расскажем про удобный способ быстро находить и фильтровать определенные данные.

Поиск данных с помощью «Найти и выделить»:

• Перейдите на вкладку «Главная».
• Нажмите «Найти и выделить» > «Найти».
• Введите нужное значение и нажмите «Найти все».
• Выделите найденные данные и примените к ним фильтр или сортировку по необходимости.

Фильтрация по числовым условиям (больше, меньше, равно):

• Щелкните на стрелку фильтра в заголовке столбца с числами.
• Выберите «Числовые фильтры».
• Выберите нужное условие. Например, от максимального к минимальному или наоборот.

Обратите внимание, что, если ранее для сортируемых данных была создана диаграмма, она автоматически поменяет свой вид в соответствии с новой сортировкой данных:

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

Еще один полезный прием – это сортировка по пользовательскому списку. Этот метод полезен, когда нужно отсортировать данные не в алфавитном порядке, а по вашему собственному списку (например, дни недели или месяцы).

Сортировка по пользовательскому списку:

• Выделите столбец с данными.
• Перейдите на вкладку «Данные» и выберите «Сортировка».
• В поле «Сортировать по» выберите нужный столбец, а затем в разделе «Порядок» выберите «Пользовательский список».
• В появившемся окне выберите список (например, «Понедельник, Вторник, Среда...») или создайте свой.

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

И теперь расскажем о таком интересном инструменте фильтрации данных, как срезы. Этот инструмент доступен в новых версиях Excel.

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

Зачем нужны срезы:

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

Как создать срез:

• Откройте таблицу.
• Щелкните в любой ячейке эксель.
• Зайдите на вкладку «Вставка».
• В разделе «Фильтры» нажмите «Срез».
• Выберите данные, для которых нужен срез.
• Нажмите ОК.

Составим условную таблицу продаж чего-нибудь по трем крупнейшим городам России и сделаем срез:

В итоге мы получаем наглядное представление обо всех показателях продаж, которые когда-либо были в каждом из городов:

Далее мы можем щелкнуть любую строчку в любом срезе, например, 800 в Москве. Ты тут же увидим, что этот показатель продаж был достигнут в декабре, а заодно узнаем, что творилось с продажами в декабре в Екатеринбурге и Питере:

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

Срез в психологии – это метод психологических исследований, при котором эмпирический эксперимент проводится один раз и при этом одновременно изучаются группы людей разного возраста [Википедия, 2016].

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

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

Это базовые знания о срезах в эксель, а больше информации, выходящей за рамки нашего базового курса, вы можете получить в материале «Использование срезов для фильтрации данных» [Microsoft, 2023].

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

• Сортировка по цвету ячеек и шрифтов.
• Быстрая фильтрация по выделению.
• Фильтрация по нескольким условиям в одном столбце.
• Сортировка по нескольким уровням.
• Поиск данных с помощью «Найти и выделить».
• Фильтрация по числовым условиям (больше, меньше, равно).
• Сортировка по пользовательскому списку.
• Срезы в Excel.

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

Проверьте свои знания

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

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

Ольга Обломова