Excel основные функции для аналитика

Самые полезные формулы Excel

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

Наверняка, 99% (если не все 100%) из вас знакомы с ним. Поэтому, давай пробежимся по основным функциям MS Excel. Для удобства работы будем использовать Google Sheets, аналог Excel, который позволяет работать с таблицами в браузере, но имеет почти идентичный функционал.

Операции с ячейками

Объединение значений ячеек

Бывает такое, что нам необходимо объединить значения двух ячеек в одну. Когда строк в таблице очень много — вручную физически это сделать очень затратно. Да что уж там, даже при наличии 10 строк в таблице.

Для сцепки значений ячеек используется амперсанд & либо формула СЦЕПИТЬ , то есть итоговая формула ячейки может быть такой:

Получение значений из других файлов Google Sheets

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

Поиск наибольшего и наименьшего значения

Функция МИН возвращает минимальное значение из указанного диапазона. А функция МАКС — наибольшее значение. Форма записи этих функций достаточно проста.

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

Простейшие функции

Суммирование

Чтобы узнать, сколько всего сеансов было за период, можно воспользоваться функцией СУММ .

Среднее значение

Чтобы узнать среднее количество транзакций в день, воспользуемся формулой СРЗНАЧ .

Функции с условиями

Сравнение значений

Чтобы выполнить логические сравнения значений, используют функцию ЕСЛИ . Например, план продаж в день составляет 100 транзакций, и необходимо в дополнительном столбце указать был ли выполнен план продаж или нет. По ходу проверки ставим «нет», если не выполнен и «да» — при успешном выполнении плана.

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

К сожалению отдельной формулы, которая поможет быстренько перечислить сразу несколько условий подряд в Excel нет, но это не мешает нам использовать формулу ЕСЛИ немного по другому. Например, нам нужно учитывать, был ли выполнен план транзакций из предыдущего пункта, но при условии, что количество сеансов было не менее 10000 в день.
Для этого в третью переменную функции ЕСЛИ мы прописываем еще одну функцию ЕСЛИ . То есть, если первое условие не выполняется (в данном случае план продаж ниже указанного), то Excel поставит «нет», если план выполняется, Excel перейдет к следующему условию.

Сравнение и суммирование

Далее, нам хочется посчитать, сколько всего транзакций в декабре было в дни с выполненным планом, но дополнительный столбец использовать не хочется. Воспользуемся функцией СУММЕСЛИ . Здесь мы суммируем количество транзакций при условии, что выполнение плана = «да».

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

Если нам необходимо добавить еще одно условие при суммировании, например, сеансов не менее 10000, используют функцию СУММЕСЛИМН .

Сравнение и подсчет

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

Сопоставление данных

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

Дополнительная таблица выглядит следующим образом:

Далее в нашей таблице создаем столбец «Новые пользователи», заносим в него формулу ВПР , которая содержит:

• Искомую ячейку;
• Таблицу с данными которые нужно перенести (первый столбец в таблице должен начинаться со столбца с искомыми значениями);
• Номер столбца в этой таблице, значения которого нужно перенести;
• Тип совпадений значений, нам нужно точное совпадение, поэтому ставим «0» или «Ложь».

Источник: thisisdata.ru

4 техники анализа данных в Microsoft Excel

Если вам по работе или учёбе приходится погружаться в океан цифр и искать в них подтверждение своих гипотез, вам определённо пригодятся эти техники работы в Microsoft Excel. Как их применять — показываем с помощью гифок.

Тренер Учебного центра Softline с 2008 года.

1. Сводные таблицы

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

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

Как работать

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

1. Откройте файл с таблицей, данные которой надо проанализировать.
2. Выделите диапазон данных для анализа.
3. Перейдите на вкладку «Вставка» → «Таблица» → «Сводная таблица» (для macOS на вкладке «Данные» в группе «Анализ»).
4. Должно появиться диалоговое окно «Создание сводной таблицы».
5. Настройте отображение данных, которые есть у вас в таблице.

Перед нами таблица с неструктурированными данными. Мы можем их систематизировать и настроить отображение тех данных, которые есть у нас в таблице. «Сумму заказов» отправляем в «Значения», а «Продавцов», «Дату продажи» — в «Строки». По данным разных продавцов за разные годы тут же посчитались суммы. При необходимости можно развернуть каждый год, квартал или месяц — получим более детальную информацию за конкретный период.

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

Можно её детализировать, например, по странам. Переносим «Страны».

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

2. 3D-карты

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

Полезное дополнение. Координаты нигде прописывать не нужно — достаточно лишь корректно указать географическое название в таблице.

Как работать

1. Откройте файл с таблицей, данные которой нужно визуализировать. Например, с информацией по разным городам и странам.
2. Подготовьте данные для отображения на карте: «Главная» → «Форматировать как таблицу».
3. Выделите диапазон данных для анализа.
4. На вкладке «Вставка» есть кнопка 3D-карта.

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

Также достаточно информативной является круговая диаграмма по годам. Размер круга задаётся суммой.

3. Лист прогнозов

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

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

Как работать

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

В примере ниже у нас есть данные за 2011, 2012 и 2013 годы. Важно указывать не числа, а именно временные периоды (то есть не 5 марта 2013 года, а март 2013-го).

Для прогноза на 2014 год вам потребуются два ряда данных: даты и соответствующие им значения показателей. Выделяем оба ряда данных.

На вкладке «Данные» в группе «Прогноз» нажимаем на «Лист прогноза». В появившемся окне «Создание листа прогноза» выбираем формат представления прогноза — график или гистограмму. В поле «Завершение прогноза» выбираем дату окончания, а затем нажимаем кнопку «Создать». Оранжевая линия — это и есть прогноз.

4. Быстрый анализ

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

Полезное дополнение. Мгновенно можно создавать различные типы диаграмм или спарклайны (микрографики прямо в ячейке).

Как работать

1. Откройте таблицу с данными для анализа.
2. Выделите нужный для анализа диапазон.
3. При выделении диапазона внизу всегда появляется кнопка «Быстрый анализ». Она сразу предлагает совершить с данными несколько возможных действий. Например, найти итоги. Мы можем узнать суммы, они проставляются внизу.

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

Также можно проставить в ячейках разноцветные значки: зелёные — наибольшие значения, красные — наименьшие.

Надеемся, что эти приёмы помогут ускорить работу с анализом данных в Microsoft Excel и быстрее покорить вершины этого сложного, но такого полезного с точки зрения работы с цифрами приложения.

Источник: lifehacker.ru

Функции Excel 2016: 10 самых важных формул

Опубликовано Михаил Непомнящий в 24.10.2016 24.10.2016

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

Объединение текстовых значений

Для объединения ячеек с текстовым значением можно использовать разные формулы, однако они имеют свои нюансы. Например, команда =СЦЕПИТЬ(D4;E4) успешно объединит две ячейки, равно как и более простая функция =D4&E4, однако никакого разделителя между словами добавлено не будет – они отобразятся слитно.

Избежать данного недочета можно добавляя пробелы, либо в конце текста каждой ячейки, что вряд ли можно назвать оптимальным решением, либо непосредственно в самой формуле, куда в любое место можно вставить набор символов в кавычках, в том числе и пробел. В нашем случае формула =СЦЕПИТЬ(D4;E4) получит вид =СЦЕПИТЬ(D4;” “;E4). Впрочем, если вы объединяете большое количество текстовых ячеек, то аналогичным образом пробел вручную придется прописывать после адреса каждой ячейки.

Другой типовой формулой для склеивания ячеек с текстом является команда ОБЪЕДИНИТЬ. По своему синтаксису она по умолчанию содержит два дополнительных параметра – сначала идет конкретный символ разделения, затем команда ИСТИНА или ЛОЖЬ (в первом случае пустые ячейки из указанного интервала будут игнорироваться, во втором – нет), и потом уже список или интервал ячеек. Между ячейками также можно использовать и обычные текстовые значения в кавычках. Например, формула =ОБЪЕДИНИТЬ(” “;ИСТИНА;D4:F4) склеит три ячейки, пропустив пустые, если таковые имеется, и добавит между словами по пробелу.

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

Выполнение условия ИЛИ

Простой оператор ИЛИ определяет выполнение заданного в скобках условия и на выходе возвращает одно из значений ИСТИНА или ЛОЖЬ. В дальнейшем данная формула может использоваться в качестве составного элемента более сложных условий, когда в зависимости от того, что выдаст значение ИЛИ будет выполняться то или иное действие.

При этом сравниваться могут как численные показатели, применяя знаки >, B2; “Превышение бюджета”; “В пределах бюджета”).

Кроме того, в качестве условия может использоваться другая функция, например, условие ИЛИ и даже еще одно условие ЕСЛИ. При этом у воженных функций ЕСЛИ может быть от 3 до 64 возможных результатов). Как пример, =ЕСЛИ(D4=1; “ДА”;ЕСЛИ(D4=2; “Нет”; “Возможно”)).

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

Формула ранжирования

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

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

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

Максимум из выбранных значений

Простая, но очень полезная формула МАКС выдает наибольшее значение из списка значений. Сам список может состоять как из ячеек и/или их диапазона, так и вручную введенных чисел. Всего максимальное значение можно искать среди списка из 255 чисел.

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

Минимум из выбранных значений

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

Среднее из выбранных значений

Для получения среднего арифметического из выбранного списка значений также есть своя формула. Однако написание ее в русском языке не столь очевидно. Звучит она как СРЗНАЧ, после чего в скобках указываются либо конкретные значения, либо ссылки на ячейки.

Сумма выбранных значений

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

Куда более интересным вариантом является суммирование ячеек, отвечающих конкретным критериям. Для этого используется оператор СУММЕСЛИ с аргументами диапазон, условие, диапазон суммирования.

Применение: Например, есть список школьников, согласившихся поехать на экскурсию. У каждого есть статус – оплатил он мероприятие или нет. Таким образом, в зависимости от содержимого столбца «Оплатил» значение из столбца «Стоимость» будет считаться или нет. =СУММЕСЛИ(E5:E9; “Да”; F5:F9)

Примечание: Подробную информацию об использовании каждой функции Excel можно найти на официальном сайте Microsoft Office.

Источник: pcgramota.ru

MS Excel для аналитиков

Ограниченное предложение

Кому идеально подойдет этот курс?

Потребность в специалистах по анализу данных и бизнес-аналитике растёт ежегодно, а кандидатов на соответствующие вакансии найти всё труднее. Уровень конкуренции на рынке труда за последние 2 года снизился в 1,5 раза. Денежное вознаграждение специалистов растёт на 10% в год. Это создаёт отличные условия для начала активной работы в этой области.

• 75 Пульс нашего выпускника после скачивания
  материалов excel 2019
• Выпускной проект
• 100 % бесплатно скачайте
  программу Excel
• + 10 000 к вашим доходам
• Стоимость 18000 руб.
• 120 + довольных выпускников

Что вы изучите?

Занятие 1. Основы работы с таблицами
Создание таблиц, форматирование ячеек: текст, дата, число, деньги, проценты. Фильтрация. Поиск. Замена. Сортировка. Разделение текста по столбцам, сцепление. Закрепление строк и столбцов. Именованные ячейки. Основные горячие клавиши.
Работа с Google Sheets. Google таблица. Дублирование функционала, панель инструментов.

Занятие 2. Формулы и базовые функции
Запись формулы. Абсолютные и относительные ссылки на ячейки. Копирование и закрепление формул. Создание формул с данными различных листов, книг.
Стандартные встроенные функции (сумма, среднее, минимум, максимум, число). Поиск функций и использование справки. Логические функции (ЕСЛИ, И, ИЛИ, ЕСЛИОШИБКА).

Занятие 3. Функции
Математические функции (СУММЕСЛИ, СЧЕТЕСЛИ). Тестовые функции (СЦЕПИТЬ, ЛЕВСИМВ, ПРОПНАЧ). Функции массивов (ГПР, ВПР). Обработка ассортиментной матрицы.
Поиск указанного элемента в диапазоне ячеек (функция ПОИСКПОЗ). Текстовые функции (ПОИСК, ПОИСКБ и другие). Строковые функции (ПСТР, ЗНАЧЕН). Динамический диапазон (ИНДЕКС, ДВССЫЛ, АДРЕС). Функция ВПР.

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

Занятие 5. Анализ данных с помощью Excel
Создание и преобразование сводных таблиц. Построение прогнозов и статистик. Анализ динамики продаж по товарной группе за несколько лет: тренд, сезонность. ABC и XYZ – анализ.
Анализ ассортимента: расчёт по магазинам объемов по потребностям. Формирование календаря заказов. Распределение потребностей на запланированные артикулы.

Занятие 6. Работа с отчетностью
Создание отчетов в Excel: формирование и их автоматизация. Работа с Google Sheets. Взаимодействие с Google Forms. Защита данных от изменения.

Занятие 7. Автоматизация решения задач посредством записи макросов
Макросы. Записи макросов. Работа с макросами. Запуск и изменение параметров. Редактор Visual Basic. Примеры практического использования.

Занятие 8. Технология подключения к данным Power Query
Источники данных (файл Excel, CSV-файл, база данных SQL). Подключение. Подготовка данных. Моделирование данных Power Pivot. Визуализация данных Power View.

Занятие 9. Получение информации из баз данных
Базы данных. Язык SQL и его использование. Простые операции с таблицей: выборка данных по SELECT, условия в запросе и фильтрация данных с помощью WHERE. Использование логических операций: AND, OR, NOT.

Занятие 10. Информационные SQL-запросы
Оператор группировки GROUP BY. Агрегирование данных: COUNT, MAX, MIN, SUM и AVG. Сортировка результатов запроса. Оператор диапазона значений BETWEEN. Оператор поиска похожих значений LIKE.

Занятие 11. Интеграция баз данных с Excel и Python
Разбор типовых вариантов запросов. Работа с несколькими таблицами: запросы к ним и соединения. Подключение и обращение к данным c использованием синтаксиса SQL в Excel.

Также в курсе предусмотрены практические занятия по отработке запросов на SQL-тренажере с экспертом в режиме онлайн.

Занятие 12. Решение прикладных задач с помощью Python
Возможности научного стека Python. Основы использования системы Google Colaboratory для решения аналитических задач. Скриптинг в Python. Замена Excel библиотеками Python. Основные элементы библиотеки Pandas.

Занятие 13. Автоматизация процессов с помощью Python
Методы визуализации данных Matplotlib и создание отчетов. Работа с таблицами в Pandas, как с базой данных. Написание запросов к БД в Python.

Занятие 14. Анализ данных на Python
Применение библиотек Python для сбора статистик, извлечения информации и обработки данных и проведения анализа основных свойств данных, нахождения в них общих закономерностей, распределений и аномалий. Разбор задачи кредитного скоринга.

Занятие 15. Перспективные технологии решения аналитических задач
Технологии машинного и глубокого обучения. Использование глубоких нейросетевых моделей для прогнозирования и обработки табличных данных. Обзор практических кейсов и обсуждение перспектив использования технологий в области торговли.

Источник: brainskills.ru

Бизнес-аналитика: как отказаться от Excel, не отказываясь от него

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

Наш сегодняшний рассказ про то, как компания федерального уровня с большим документооборотом переходила с реестра в виде громоздких Excel-файлов на нормальный, вменяемый BI-инструмент. Ну хорошо – про наше видение, как компания должна переходить (клиент пока ещё не принял окончательного решения).

Проблематика вопроса

Чтобы была понятней суть проблемы: сейчас в компании подготовка и консолидация данных через Excel занимает порядка 80 % рабочего времени сотрудников, причем 79 % — это ожидание реакции самой программы. То есть человек выполнил какие-то манипуляции, написал формулу или значение, нажал клавишу Enter и идёт курить, пить кофе или обедать: некоторые операции консолидации и обновления данных занимают от 40 минут до 2 часов.

Главной проблемой является заложенный в стародавние времена механизм консолидации данных — куча сложных перекрёстных формул, в которых чёрт ногу сломит. Когда-то этот механизм придумал какой-то «гуру» Excel, но он явно не предполагал, что будет обрабатываться такой объём данных. Раньше всё работало без тормозов, и файлы с прописанными формулами просто копировались из проекта в проект. И когда через несколько лет начались проблемы, никто уже точно не знал, как работает консолидация. А найти специалиста, который смог бы её переделать, не удалось, что не удивительно: разработчики обычно не изучают формульный язык Excel да и не пользуются им.

Трудоёмкость и длительность расчётов в Excel привела к тому, что по мере увеличения объёмов данных приходилось постепенно нанимать в отдел всё больше сотрудников, так как уже имеющиеся работники переставали справляться с обработкой документов.

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

1. подготовка данных из разных источников, в основном из бумажных документов,
2. внесение в реестр в виде Excel-файлов и консолидация.

Технологический процесс у заказчика выглядит так:

Второй нюанс: при введении данных в реестр сотрудники проверяют, не вводился ли этот документ раньше. Для этого выполняют поиск по совокупности реквизитов. Мы предложили автоматизировать эту процедуру и выявлять дубликаты уже на этапе ввода. То есть приходит новый документ, его начинают вбивать без всяких фильтров. Если система находит дубликат по каким-либо параметрам (к примеру, по назначению и дате платежа), эта запись подсвечивается красным цветом, чтобы пользователь выяснил: нет ли ошибки во вносимых или уже внесённых данных. Таким образом реализуется контроль дублирования записей и корректности ввода средствами Excel.

Мы решили не создавать зоопарк из разных вендоров. Решение Microsoft было в данном случае лучше не потому, что эта компания разработала и Excel, и Power BI. И даже не потому, что они хорошо интегрированы друг с другом. Просто Power BI позволяет использовать в качестве источника данных целую папку, в которую пользователь может сложить разные файлы по своему усмотрению (в том числе и текстовые). И обработать их буквально одним щелчком мыши. Ну и добавить другие источники информации, если понадобится (1С, автоматизированные учётные системы других подразделений и т. п.).

Power BI и Power Query буквально в сотни раз ускоряют работу и позволяют обойти ограничение на количество данных, которыми оперирует Excel. Сначала это было 64 тыс. строк, теперь счёт идёт на миллионы. Так что если вы упёрлись в возможности Excel, то следующим логическим шагом может быть переход или на Power Pivot или на Power BI: они используют те же самые подходы и механизмы. Если для обработки данных вы применяли какие-то формулы на языке М (в Power Query) или DAX в Power Pivot, то всё это будет практически без изменений работать и в Power BI.

Power BI, как единый интегрированный продукт, позволяет соединить в себе все те технологии, которые годами «навешивались» на Excel, как игрушки на елку. Пусть в него нельзя вводить данные (ну почти), нельзя считать А + Б, как это делается в Excel, но зато его можно использовать в качестве обработчика больших объёмов данных. Например, если нужно произвести какие-то вычисления над колоссальным количеством столбцов или строк. Excel тоже справится, но за часы, а Power BI — за секунды.

Есть два–три конкурирующих продукта, которые, гипотетически, могут выполнять те же вычисления столь же быстро, но они будут не так хорошо интегрироваться с Excel и, что не менее важно, потребуется развернуть дополнительную ИТ-инфраструктуру.

Кроме того, нельзя сбрасывать со счётов и финансовую составляющую: Microsoft предоставляет Power Pivot Desktop бесплатно, если вы хотите использовать этот механизм только для интеграции своих собственных Excel-файлов и будете готовить отчеты или анализировать данные в одиночку или в составе небольшой группы.

При этом создаваемые Power BI файлы можно свободно перемещать между компьютерами, как Excel-отчёты. В результате пользователи получают в руки не просто какие-то рассчитываемые, фиксированные данные, Power BI предоставляет широкие возможности для построения различных интерактивных, моментально пересчитывающихся графиков и диаграмм.

После внедрения Power BI по самым пессимистичным оценкам длительность этапа расчётов уменьшилась в 200–300 раз (!). Учитывая, что в отделе на данный момент трудится 12 человек, которые тратят кучу времени на обработку документов, можно смело сокращать штат раза в 4. А это уже заметная экономия на ФОТ и налогах.

Но экономия рабочего времени и денег – не единственное преимущество этого решения. Раньше в компании никогда не анализировался весь объем данных – только для каждого бизнес-направления или проекта в отдельности. Excel просто не справился бы с такой нагрузкой. А теперь станет возможной не только перекрёстная аналитика по проектам. При дальнейшем расширении этой линейки до, например, Power BI Pro, Power BI RS или даже Power BI Premium, появится возможность готовить интерактивные отчёты для различных категорий пользователей, управлять уровнями доступа к конфиденциальным данным и предоставлять доступ через web или мобильные устройства.

То есть, практически не меняя рабочего процесса, обеспечить всех ЛПР в компании информацией, что называется, на кончиках пальцев.

Юрий Колмаков, эксперт Департамента систем консолидации и визуализации данных, «Инфосистемы Джет» (McCow)

Источник: habr.com

Excel основные функции для аналитика

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

Борьба за единственный источник данных

Одним из ключевых факторов, который ведет к тому, что подготовка отчётности занимает значительное время – это то, что в процессе подготовки данных используется несколько таблиц. Очень часто количество и структура данных становятся заложниками визуального компонента отчетности. В худшем случае под каждый вид отчёта используется свой, уникальный источник данных, а то и несколько. А когда у вас несколько источников данных, то вы начинаете их сверять между собой, чтобы убедиться, что они не потеряли целостность. Замкнутый круг. Поэтому для хорошего аналитика очень актуальна задача – держать под контролем количество источников данных. В идеале иметь ОДИН. В этой статье будет использоваться один источник данных – лист DATA и будет продемонстрирован довольно продвинутый и удобный способ извлечения данных из него.

Модельные данные

На примере довольно сложного отчёта западного бухгалтерского учёта P&L (Profit and Loses, отчёт о прибылях и убытках) я попытаюсь проиллюстрировать все идеи данного цикла. Я подготовил большой массив данных, необходимых для создания P&L отчёта, которые, как я надеюсь, вполне адекватно отражают реальный уровень сложности информации такого рода.

Таблица параметров, используемых в моделе:

Скачать пример

Структура таблицы, которая будет служить для вас источником данных исключительно важна. Основное правило, которое надо запомнить: не плодите лишних столбцов. Отметим, что мы НЕ стали делать отдельные колонки для параметров VOL , GRREV , DEDUC и т.д., а ввели их всех вместе в колонку TYPE ! Тем самым мы существенно упростили нашу таблицу и дальнейшую выборку из неё. Кстати, это же самое правило облегчает анализ данных в сводных таблицах. Тема эта, однако, сильно выходит за рамки данной статьи, тем же, кому интересно, следует гуглить ” реляционная модель + нормализация данных “.

Измерения модели данных

Извлечение данных

Я рассматривал 3 варианта:

Сводная таблица – с её помощью удобно извлекать данные в нужных разрезах, но она не может быть конечной таблицей, откуда, например, диаграмма будет забирать свои ряды данных. Нам могут потребоваться разные разрезы и тогда сводных таблиц надо иметь несколько, либо программно получать данные из сводной таблицы, последовательно меняя фильтры, забирать куда-то промежуточные данные, опять менять фильтры, опять забирать данные. Я предвижу тут значительные трудности и большой объём работы.

Формулы рабочего листа – это более гибкий и надёжный вариант, если только удасться подобрать такие формулы, чтобы можно было быстро извлекать нужные ряды в нужных разрезах. И такая функция в Excel есть. Это СУММЕСЛИМН ( SUMIFS ). Самый предпочтительный вариант, на мой взгляд.

Специализированные макросы – большой объём работы. В случае смены модели – переделка программы, а если делать в общем виде, то слишком сложно. Нет, чистый VBA тут не применим, если только, как комбинация со сводной таблицей.

СУММЕСЛИМН (SUMIFS)!

Вот наиболее полезная и универсальная выборка, которая потребуется нам на первых порах. Давайте её получим из листа DATA . Обратите внимание, что выборка имеет параметры, помеченные красными кружками с номерами от 1 до 4. Соответствующие ячейки содержат выпадающие списки, при помощи которых можно выбирать тип бизнеса ( BUS ), регион ( GEO ), год ( YEAR ), сумму ( ACT_RE или BP ).

Давайте разберём формулу из ячейки B4 , которая извлекает объём продаж за январь месяц с учётом указанных в фильтре значений типа бизнеса, региона, года и типа суммы.
=СУММЕСЛИМН(ДВССЫЛ(valAMOUNT);TYPE;$A4;BUS;valBUS;GEO;valGEO;YEAR;valYEAR;MONTH;B$3) =SUMIFS(INDIRECT(valAMOUNT);TYPE;$A4;BUS;valBUS;GEO;valGEO;YEAR;valYEAR;MONTH;B$3) , тут:

• valAMOUNT , valBUS , valGEO , valYEAR – это именованные диапазоны, указывающие на ячейки с фильтрами ( E1 , G1 , I1 и K1 )
• TYPE , BUS , GEO , YEAR , MONTH – именованные диапазоны, ссылающиеся на соответствующие колонки листа DATA
• Первый параметр функции СУММЕСЛИМН указывает диапазон суммирования. Поскольку нам надо суммировать разные столбцы (столбец ACT_RE , либо столбец BP ) в зависимости от того, что выбрано в ячейке K1 (она же именованный диапазон valAMOUNT ). Именно в связи с этим использована функция ДВССЫЛ, которая возвращает именованный массив, так как в дипазаоне valAMOUNT содержится одноименная с соответствующими именованными диапазонами текстовая константа. То есть ДВССЫЛ идёт в диапазон valAMOUNT (а это K1 ), берёт из K1 текст ” ACT_RE ” и поскольку в данном файле объявлен диапазон с таким же именем, то функция и возвращает нам уже не текст, а соответствующий диапазон.
• Далее параметры идут парами: диапазон условия и условие. У нас есть 5 таких пар. Например, диапазону условия TYPE , который ссылается на колонку TYPE на листе DATA , поставлена в соответствие ячейка $A4 , где указан параметр ” VOL “.

Таким образом, СУММЕСЛИМН суммирует с учётом множественных условий. Кроме этого, если в качестве какого-то условия указана звёздочка (*), то данный фильтр фактически отключается – возвращает все значения, которые есть в данном диапазоне условия.

Все остальные формулы в строках GRREV , DEDUC , PRD , OPEX , DA – полностью идентичны рассмотренной и вводятся путём стандартного протягивания (копирования). Остальные формулы на листе SELECT элементарны.

Что у нас получилось? Что мы узнали?

• Мы смогли грамотно спланировать структуру таблицы на листе DATA
• Мы используем единственный источник внешних данных
• Мы нашли способ довольно легко и надёжно делать необходимые нам выборки для отчётов и диаграмм, о которых речь пойдёт в следующих частях

В следующих частях

Мы сделаем универсальный интерактивный отчёт P&L на одном листе, а не на 10. А в следующих выпусках будем много эксперементировать с диаграммами, иллюстрирующими данный отчёт. До встречи!

Источник: perfect-excel.ru