Excel для анализа данных

В последнее время Excel открывает доступ к комплексному набору инструментов для анализа. Среди этих инструментов можно найти не только классические функции, но и продвинутые решения, где задействовано ИИ (Copitol) в союзе с языком Python.

Далее приведена пошаговая инструкция использования ключевых инструментов анализа данных в Excel.

Интеллектуальные и автоматизированные способы

1. Copitel в Excel открывает доступ к возможностям осуществления глубокого анализа данных через текстовые запросы на привычном всем языке. Обладает возможностями автоматической визуализации, выявления трендов и написания формул.
2. «Режим агента». Недавняя разработка, позволяющая использовать веб-поиск и возможности подвинутых моделей ИИ для того, чтобы находить решения аналитическим задачам непосредственно в окне программы.
3. Функция анализа данных, представляемая кнопкой на главной вкладке, обладает возможностью автоматического создания гистограмм, корреляционного анализа и описательной статистики в один клик.

Шаг 1. Подготовка и очистка данных

Шаг 1.1 «Получить данные»

На панели инструментов найти вкладку «Данные»

Найти кнопку «Получить данные» в группе «Получить и преобразовать данные».

Шаг 1.2 Выбрать источник

Выбор обусловлен необходимостью. К примеру, это может быть — «Из файла».

«Из базы данных».

Шаг 1.3 Для работы

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

 

 

Шаг 1.4 «Преобразовать данные»

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

Выбор необходимого источника открывает окно, в котором следует выбрать команду «Преобразовать данные».

Шаг 1.5 Редактор «Power Query»

Использование возможностей редактора «Power Query» позволяет избавиться от пустых строк, сменить тип данных, разделить текст на столбцы.

Шаг 1.6 Закрыть и загрузить

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

Шаг 2. Быстрый анализ и выводы

Шаг 2.1 Выбрать ячейку

В таблице выбрать любую ячейку.

Шаг 2.2 Анализ данных

Вкладка «Данные»

Группа «Анализ», кнопка «Анализ данных»

Шаг 2.3 Варианты готовых гистограмм

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

Кроме того, можно в строку поиска ввести интересующий вопрос. К примеру, «Какова средняя выручка по магазинам» и т.п.

Наличие активированного Copitot определяется по наличию иконки на ленте инструментов. Активация иконки позволяет задать ИИ необходимые вопросы. К примеру: «Найди аномалии в продажах…», «Добавь столбец с расчетом маржи» и т.п.

Шаг 3. Сводные таблицы для глубокого анализа

Анализ помогает сгруппировать значительную численность строк в краткие отчеты.

Шаг 3.1 Выделить таблицу

Шаг 3.2 «Сводная таблица»

Для активации кнопки «Сводная таблица» требуется войти во вкладку «Вставка», группу «Таблицы».

Упростить вариант формирования сводной таблицы позволяет кнопка «Рекомендуемые сводные таблицы», активация которой открывает окно с рекомендациями к действию.

Кнопка «Ок»

Активация кнопки позволяет открыть на панели инструментов вкладку «Анализ сводной таблицы».

Шаг 3.2.1 Работа с боковой панелью

На открывшемся пустом листе активируются подсказка по работе с боковой панелью.

Боковая панель «Поля сводной таблицы»

Шаг 3.2.2 Выбор параметров

Выбор необходимый параметров автоматически формирует сводную таблицу. К примеру, выбор двух параметров: «Магазин» и «Доходность»

Итог – сформирована сводная таблицы с общим итогом.

Шаг 3.2.3 Добавление дополнительных параметров

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

Дополнительные области

Шаг 3.2.4 «Фильтры»

На боковой панели, ниже окна с полями, которые можно добавить в отчет, располагаются поля, в которые можно перетащить необходимые критерии анализа. Вопросы может вызвать только первое окно с названием «Фильтры». Для его активации требуется на вкладке «Вставка» найти группу «Фильтры» и активировать кнопку «Срез».

Всплывающее окно позволит выбрать интересующие разделы для настройки фильтрации данных.

Шаг 4. Статистический анализ

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

Шаг 4.1 Включить надстройку

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

Шаг 4.1.1 Войти во вкладку «Файл»

Шаг 4.1.2 «Параметры»

Найти в меню пункт «Параметры» и активировать.

Шаг 4.1.3 «Надстройки»

В окне «Параметры Excel» найти пункт «Управление» и выбрать «Настройки Excel» и активировать кнопку «Перейти».

Шаг 4.1.4 «Настройки»

Из перечня доступных настроек выбрать «Пакет анализа».

Шаг 4.2 Кнопка «Анализ данных»

Во вкладке «Данные», найти кнопку «Анализ данных», при активации которой открывается окно «Анализ данных». Надо выбрать требуемый метод анализа. К примеру, это может быть метод «Регрессия» или «Описательная статистика».

Активация кнопки «Ок», открывает окно «Описательная статистика», которое требует указать диапазон данных.

Активация кнопки «Ок» позволяет Excel в автоматическом режиме сформировать новый лист и представить новые расчеты.

Шаг 5. Продвинутая аналитика

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

Шаг 5.1 Выделить ячейку и ввести формулу.

Так же можно воспользоваться вкладкой «Формулы» и выбрать команду «Вставить Python».

Шаг 5.2 Код на языке Python

Написать код на языке Python. Это позволит, к примеру, создать график через библиотеку «seaborn» или анализ через «pandas».

Шаг 5.3 Выполнить код

Для выполнения кода и визуализации в ячейке результата, требуется активировать сочетание клавиш «Ctrl+Entr».

Шаг 6. Моделирование ситуации

Для понимания процесса изменения переменных, в рамках необходимости достичь определенной цели, можно моделировать ситуацию, используя вариант анализа «Что-если».

Шаг 6.1 Кнопка «Анализ «что-если»»

Найти требуемую кнопку можно на вкладке «Данные».

В группе «Прогноз».

Шаг 6.2 Пункт «Подбор параметров»

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

Шаг 6.3 Надстройка «Поиск решений»

В случае необходимости оптимизации задачи, к примеру, минимизации расходов в рамках определенных условий, требуется выбирать инструмент «Поиск решений». Эта надстройка включается аналогично тому, как была включена надстройка «Пакет анализа», т.е. пройдя путь: «Файл» — «Параметры» — «Надстройки» — «Настройки Excel» — «Поиск решений» — «Ок».

В этом случае инструмент появится в линейке инструментов во вкладке «Данные», группы «Анализ».

Классические аналитические инструменты

Шаг 7. Сводные таблицы

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

Шаг 7.1 От «Вставка» до «Новый лист»

Вкладка «Вставка» — группа «Таблицы» — кнопка «Сводная таблица» или «Рекомендуемые сводные таблицы» — «Новый лист».

Шаг 7.2 Настройка полей

Панель справа – «Поля сводной таблицы». Распределить данные в полях внизу панели.

В окно «Строки» необходимо перетащить объект анализа «Категория товара», «Город» и т.п. Перетащить параметр можно, удержанием выбранного правой кнопкой мыши.

В окно «Значения» помещается то, что нужно посчитать, т.е. «Расходы», «Сумма» и т.п.

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

Активация строки открывает меню выбора операции, которую следует использовать для сведения данных в выбранном поле.

• Даты

При наличии в строках дат, требуется кликнуть на любую дату в таблице правой кнопкой мыши. Затем выбрать команду «Группировать» и тип группировки «Месяц» или «Кварталы».

• Срезы

Чтобы фильтровать отчет в один клик, необходимо активировать вкладку «Анализ сводной таблицы».

В группе «Фильтр» активировать кнопку «Вставить срезы».

Активация кнопки открывает окно «Вставка срезов», где требуется выделить необходимый параметр.

Выбор параметра «Доходность» открыл окно с распределением показателей по мере возрастания.

Шаг 8. Пакет анализов

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

Шаг 8.1 Активировать

Как активировать «Пакет анализов», см. Шаг 4.

Шаг 8.2 Запустить

Чтобы запустить анализ данных, требуется перейти во вкладку «Данные» и активировать кнопку «Анализ данных» в группе «Анализ».

Шаг 8.3 Выбор метода

Чтобы выбрать требуемый метод, необходимо в окне «Анализ данных», открывающийся после активации одноименной кнопки, выбрать требуемый инструмент анализа.

• Выбор метода «Описательная статистика» позволит быстро узнать не только разброс данных, но среднее, медиану, моду.

• «Корреляция» выбирается для выяснения того, как связаны две переменные.

• Метод «Регрессия» используется в случае необходимости построения математической модели прогноза.

Шаг 8.4 Настройка параметров инструментов анализа

Для настройки параметров, требуется указать «Входные данные» и установить галочку в окошке строки «Метки в первой строке» тогда, когда заголовки выделены. Для создания листа с результатами по заданным параметрам, требуется активизировать автоматическую работу Excel посредством кнопки «Ок».

Шаг 9. Инструмент «Экспресс анализ»

Данный вид инструментов для анализа данных признается самым быстрым вариантом получения отчетов или подсчета итогов. Результат отображается сразу после ввода данных

Шаг 9.1 Выбрать диапазон

Выделить диапазон данных, не забывая заголовки столбцов.

Шаг 9.2 Найти иконку

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

Нажать на иконку, возникшую в правом нижнем углу. Аналогичная команда активируется в случае использования сочетания клавиш «Ctrl+Q». Откроется окно условного форматирования.

Шаг 9.2 Выбрать вкладку

Выбрать нужную вкладку:

1. Форматирование используется для применения цветовых шкал и ли набора значков.

1. Диаграммы используются для мгновенного внедрения рекомендуемых графиков.

1. Итоги – для добавления строк с суммой, средним или процентом в конце таблицы.

1. Таблицы используется для создания готовой сводной таблицы.

Шаг 9.2 Активация

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

Шаг 10. Условное форматирование

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

Шаг 10.1 Выделить столбец

Выбрать столбец и выделить.

Шаг 10.2 Иконка «Условное форматирование»

На линейке инструментов найти вкладку «Главная», в группе «Стили» найти иконку «Условное форматирование».

Шаг 10.3 Определить тип правила

• Гистограммы позволяют визуализировать размер значения посредством цветовых полосок внутри ячеек.

• Цветовые шкалы демонстрируют высокие и низкие показатели расходов.

• Правила выделения ячеек используются для поиска дубликатов, топ-1- элементов и т.п.

• Активация варианта предлагает не только визуализацию результатов, но и возможность форматирования визуала, настройки цвета и условий визуализации.

Форматирование данных производится в автоматическом режиме.

Шаг 11. Интерактивные срезы и временные шкалы

Использование данного инструмента позволяет превратить статистический отчет в интерактивную панель управления, у которой появляются кнопки-фильтры.

Важно!

Данный инструмент будет работать только в случае использования возможностей «Умная таблица» или «Сводная таблица».

Шаг 11.1 Создать «Умная таблица»

Чтобы из обычной таблицы создать «Умную», необходимо выделить имеющеюся таблицу.

И воспользоваться сочетанием клавиш «Ctrl+T».

Откроется окно, действия в которой требуется подтвердить. Итоговый вариант таблицы будет следующим.

Шаг 11.2 Вставка среза

1. Кликнуть внутри умной таблицы. Во всплывающем меню можно найти много интересных инструментов.

1. На линейке инструментов найти вкладку «Конструктор таблиц».

1. В группе «Инструменты найти кнопку «Вставить срез».

1. В открывшемся окне выбрать поля. По которым требуется отфильтровать данные.

Шаг 12.3 Использование срезов

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

• Выбор одной кнопки, отфильтрует один параметр.

• При необходимости выбора нескольких элементов, рекомендуется использовать клавишу «Ctrl» — удерживать клавишу и выбирать элементы.

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

Шаг 12.4 Временная шкала

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

Работая с параметрами сводной таблицы, в группе «Фильтры» можно найти инструмент, позволяющий создать временную шкалу.

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

Современные формулы и функции

Шаг 12. Динамические массивы

Шаг 12.1 Создание мини-сводных таблиц формулой

• GROUPBY. Будет работать если ввести «=GROUPBY(столбец категорий; столбец значений; функция)

Например, если задать такие параметры как: =GROUPBY(A2:A9; C2:C9; SUM), то можно создать таблицу продаж по категориям.

• PIVOTBY предлагает создать таблицу с разделением на строки и столбцы

=PIVOTBY(A2:A9; B2:B9; C2:C9; SUM)

Шаг 12.2 Цепочка анализа

Использование ниже представленных формул позволяют выжать из данных суть

• FILTER – функция, способная выделить строки, где продажи выше определенного показателя, к примеру >8000

Для этого необходимо задать следующую формулу:

=FILTER(A2:A9; D2:D9 >8000)

• UNIQUE помогает сформировать список уникальных имен клиентов без дубликатов.

В строку формул необходимо вписать следующее: UNIQUE(столбец_категория)

• SORT используется для сортировки результатов любой функции. Все что необходимо, подставить эту функцию перед другой формулой:

=SORT(UNIQUE(столбец_категория))