Excel основные функции для аналитика – Все про Эксель

Excel основные функции для аналитика - Все про Эксель Аналитика

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

Занятие 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. Перспективные технологии решения аналитических задачТехнологии машинного и глубокого обучения. Использование глубоких нейросетевых моделей для прогнозирования и обработки табличных данных. Обзор практических кейсов и обсуждение перспектив использования технологий в области торговли.

«умная» таблица

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

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

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

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

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

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

Бизнес-аналитика в excel: формируем и визуализируем данные

Функции табличного редактора Excel, позволяющие формировать данные для анализа результатов работы компании

Средства Excel для визуализации данных бизнес-анализа

Понятие бизнес-аналитики достаточно обширно и нередко трактуется по-разному.

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

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

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

Бизнес-аналитика и продвинутая работа с данными в microsoft excel

Код курса: Т-ЭКСЕЛЬБ-Г

09.01.03 Мастер по обработке цифровой информации

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

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

Программа состоит из 5 курсов:

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

Второй курс программы посвящен обработке данных перед анализом. Эта часть работы часто занимает большую часть времени и сил. Освоив на курсе расширенные возможности Power Query (Get & Transform), вы сможете быстро и легко загружать данные из любого источника в любом формате и преобразовывать их в тот вид и формат, который вам требуется.

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

Преимущества комплексной программы:

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

Используйте продвинутые возможности Эксель для эффективной бизнес-аналитики! Записывайтесь на программу!

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

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

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

Дополнительный анализ:  Работа: Hr аналитик аналитик по кадрам в Москве — Июль 2021 - 50 вакансий |

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

Все документы Центра

§

§

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

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

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

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

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

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

Поэтому для хорошего аналитика очень актуальна задача — держать под контролем количество источников данных. В идеале иметь ОДИН. В этой статье будет использоваться один источник данных — лист DATA и будет продемонстрирован довольно продвинутый и удобный способ извлечения данных из него.

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

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

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

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

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

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

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

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

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

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

Как работать

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

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

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

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

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

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

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

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

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

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

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

Подбор параметра

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

Откроется окно подбора параметра. В поле «Установить в ячейке» вы должны указать ссылку на ячейку, которая содержит нужную формулу. В поле «Значение» должен быть указан конечный результат, который вы хотите получить. В поле «Изменяя значения ячейки» вставьте координаты ячейки с корректируемым значением.

Работа: аналитик excel в москве — июнь 2021 – 6554 вакансии |

Собираем когортный анализ/анализ потоков на примере excel

В прошлой статье я описал использование когортного анализа для выяснения причин динамики клиентской базы. Сегодня пришло время поговорить про трюки подготовки данных для когортного анализа.

Легко рисовать картинки, но для того, чтобы они считались и отображались правильно “под капотом” нужно проделать немало работы. В этой статье мы поговорим о том, как реализовать когортный анализ. Я расскажу про реализацию при помощи Excel, а в другой статье при помощи R.

Хотим мы этого или нет, но по факту Excel это инструмент анализа данных. Более “высокомерные” аналитики будут считать, что это слабый и не удобный инструмент. С другой стороны по факту сотни тысяч людей делают анализ данных в Excel и в этом отношении он легко побьет R / python. Конечно, когда мы говорим о advances analytics и машинном обучении, мы будем работать на R / python. И я был бы за то, чтобы большая часть аналитики делалась именно этими инструментами. Но стоит признать факты, в Excel обрабатывают и представляют данные подавляющее большинство компаний и именно этим инструментом пользуются обычные аналитики, менеджеры и product owners. Вдобавок Excel трудно победить в части простоты и наглядности процесса, т.к. вы мастерите свои расчеты и модельки буквально руками.

И так, как же нам сделать когортный анализ в Excel? Для того, чтобы решать подобные задачи нужно определить 2 вещи:

  1. Какие данные у нас в начале процесса

  2. Как должны выглядеть наши данные в конце процесса.

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

  3. Календарная дата

  4. Id клиента

  5. Дата регистрации клиента

  6. Объем продаж этого клиента в эту календарную дату

Первая сложность, которую предстоит преодолеть — это получить эти данные. Если у вас правильное хранилище, то они уже должны быть у вас. С другой стороны, если пока реализовали только запись данных о совокупных продажах по дням, то данные по клиентам у вас есть только на “проде”. Для когортного анализа вам придется реализовать ETL и сложить в ваше хранилище данные в разрезе клиентов, иначе у вас ничего не выйдет. И лучше всего если вы разделите “прод” и аналитику в разные базы, т.к. У аналитических задач и задач функционирования вашего продукта разные цели конкуренция за ресурсы. Аналитикам нужны быстрые агрегаты и расчеты на по многим пользователям, продукту нужно быстро обслужить конкретного пользователя. Об организации хранилища я напишу отдельную статью.

Итак, вы имеете стартовые данные:

Excel основные функции для аналитика - Все про Эксель

Первое, что нам нужно сделать это преобразовать их в “лесенки”. Для этого нужно над этой таблицей построить сводную таблицу, по строкам — дата регистрации, по столбцам — календарная дата, в качестве значений — кол-во id клиентов. Если вы верно извлекли данные, то у вас должен получится вот такой треугольник/лесенка:

Excel основные функции для аналитика - Все про Эксель

В целом лесенка это наш когортный график, в котором каждая строка отображает динамику отдельной когорты. Клиенты во времени в этой отображении двигаются только внутри одной строки. Таким образом динамика когорты отображает развитие отношений с группой клиентов пришедший в один период времени. Часто для удобства и без потери качества, можно объединить когорты в “блоки” строк. Например, вы можете сгруппировать их по неделям и месяцам. Точно так же вы можете сгруппировать и колонку, т.к. Возможно ваш темп развития продукта не требует детализации до дней.

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

Excel основные функции для аналитика - Все про Эксель

Это график с накопительными областями, где каждый ряд — это строка, по горизонтали даты.

Чуть сложнее логика для реализации графика “потоков”. Для потоков мы должны сделать некоторые дополнительные вычисления. В логике потоков каждый клиент прибывает в различных состояниях:

  1. Новый — любой клиент, у кого разница между датой регистрации и календарной дате <7 дней
  2. Реактивированный — любой клиент, кто уже не новый, но в прошлом календарном месяце не генерировал выручку
  3. Действующий — любой клиент, кто не новый, но в в календарном месяце генерировал выручку
  4. Ушедший — любой клиент, кто не генерирует выручку 2 месяца подряд

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

В Excel вам нужно создать дополнительную колонку, куда вписать описанную выше логику. В нашей случае нам придется “попотеть”. У нас есть 2 типа критериев:

  • Разница между датой регистрацией и календарной датой — эти данные есть у каждой строки и тут просто нужно ее посчитать (вычитание дат в Excel просто дает разницу в днях)
  • Данные о выручке в текущем и прошлом месяце. Эти данные нам не доступны в строке. Более того, с учетом того, что в нашей таблице не гарантирован порядок, то вы не можете точно сказать, где у вас данные по другим дням месяца для этого клиента.

Решить проблему 2 типа критериев можно 2 способами:

  1. Попросите сделать это в базе данных. SQL позволяет при помощи аналитической функции вычислить для каждого клиента сумму выручки за текущий и прошлый месяц (для текущего месяца SUM(revenue) OVER (PARTITION BY client_id, calendar_month, а потом LAG, чтобы получить смещение по прошлому месяцу):
  2. В экселе вам придется реализовать это так:
    • Для текущего месяца: СУММЕСЛИ(), критериями будет id клиента и месяц ячейки календарного дня
    • Для прошлого месяца: СУММЕСЛИ(), критериями будет id клиента и месяц ячейки календарного дня минус ровно 1 календарный месяц. При этом обращу внимание, что вы должны вычесть именно календарный месяц, а не 30 дней. Иначе вы рискуете получить смазанную картину из-за неодинакового числа дней в месяцах. Также используйте функцию ЕСЛИОШИБКА, чтобы заменить ошибочные значения для клиентов у кого не было прошлого месяца.

Добавив колонки выручки текущего месяца, прошлого месяца вы можете построить вложенное условие ЕСЛИ, учитывающие все факторы (разницу дат и суммы выручки в текущем/прошлом месяце):
ЕСЛИ( разница дат <7; “новый”;
ЕСЛИ( И (выручка прошлого месяца = 0; выручка текущего месяца > 0); “реактивация”;
ЕСЛИ( И (выручка прошлого месяца > 0; выручка текущего месяца > 0); “действующий”
ЕСЛИ( И (выручка прошлого месяца = 0; выручка текущего месяца = 0); “ушедший”; “ошибка”))))

“Ошибка” нужна тут только для контроля, что вы не ошиблись в записи. Логика критериев состояний MECE (https://en.wikipedia.org/wiki/MECE_principle), т.е. Если все сделано правильно, то каждому будет проставлено одно состояние из 4-х

У вас должно получится вот так:

Excel основные функции для аналитика - Все про Эксель

Теперь эту таблицу можно пересобрать при помощи сводной таблицы в таблицу для построения графика. Вам нужно трансформировать ее в таблицу:

Календарная дата (колонки)
Состояние (строки)
Кол-во id клиентов (значения в ячейках)

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

Excel основные функции для аналитика - Все про Эксель

Теперь мы можем приступать к интерпретации и анализу.

Создание диаграмм

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

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

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

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

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

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

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

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

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

Суммеслимн (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 элементарны.

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

Параметр в моделиАнглоязычный термин, единицы измеренияРусскоязычный аналог, формулы
VOLVOLUME, unitsОбъем продаж. Единицы измерения, физические единицы продукции: ящики, упаковки, штуки.
GRREVGROSS REVENUE, РУБСуммарный доход от продаж без учета скидок
DEDUCDEDUCTIONS, РУБРазличного рода скидки: скидки, отраженные в счетах, скидки по контракту, за выполнение обязательств клиентом, и прочее
NREVNET REVENUE, РУБЧистый доход. Вычисляется, как NREV = GRREV – DEDUC
PRDPRODUCTION COST, РУБЗатраты на производство (приобретение) реализуемой продукции: сырье, затраты на производство, затраты на транспортировку внутри компании
GRPRFGROSS PROFIT, РУБВаловая прибыль, вычисляется как: GRPRF = NREV – PRD
OPEXOPERATING EXPENSES, РУБОперационные затраты, не включаемые в прямую себестоимость продукции
EBITEARNINGS BEFORE INTEREST AND TAXES, РУБОперационная прибыль, вычисляется как: EBIT = GRPRF – OPEX
DAAMMORTIZATION, РУБАммортизация, переоценка активов
EBITDAEARNINGS BEFORE INTEREST, TAXES, DEPRECIATION AND AMORTIZATION, РУБОперационная прибыль плюс аммортизационные отчисления, EBITDA = EBIT DA

Топ 5 лучших функций excel для экономиста

Excel основные функции для аналитика - Все про Эксель

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

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

Я сомневаюсь что многие будут со мной спорить в том вопросе что знать экономический эффект от любого действия на предприятии или рассчитать прибыль для любого продукта это «архиважно» товарищи. А вот для этого и важны те функции, которые мы будем рассматривать. Рассмотрим 5 ТОП функций для экономиста, это:

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

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

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

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

Формулы в excel

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

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

Excel также удобно использовать и в качестве обычного калькулятора. Для этого в строке формул или в любой ячейке просто вводятся математические выражения после знака «=».

Функция «впр»

Одной из самых востребованных функций в Microsoft Excel является «ВПР» («VLOOKUP)». Задействовав ее, можно перетягивать значения одной или нескольких таблиц в другую. При этом поиск производится только в первом столбце таблицы, тем самым при изменении данных в таблице-источнике автоматически формируются данные и в производной таблице, в которой могут выполняться отдельные расчеты.

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

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

Функция впр вexcel

Excel основные функции для аналитика - Все про Эксель«Массивы и ссылки». Я думаю, что нового не скажу и откровением не станет тот факт, что в работе каждого экономиста встречаются большие объемы информации, громадные таблицы, которые нужно перелопатить, анализировать для получения нужных данных, вот такие данные и называются «массивы».

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

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

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

Функция если вexcel

Excel основные функции для аналитика - Все про Эксель

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

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

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

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

Функция суммесли вexcel

Excel основные функции для аналитика - Все про Эксель

Функция СУММЕСЛИ в Excel хороша еще тем что, спокойно работает с поименованными диапазонами значений, что значительно упрощает рутинные вычисления. Но стоит помнить, что функция чувствительна к точности написания критериев и даже ошибка в один знак не даст вам правильный результат.

Детальнее о том, как работает СУММЕСЛИ в Excel вы можете ознакомиться и посмотреть видео здесь.

Функция суммеслимн вexcel

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

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

В целом при работе с большими массивами данных функция СУММЕСЛИМН будет являться для вас неоценимым помощником.

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

Функция суммпроизв вexcel

Excel основные функции для аналитика - Все про Эксельфункции ЕСЛИ, СУММЕСЛИ, СУММЕСЛИМН, а также производить свои вычисление в 255 массивах, а это, я вам скажу, ох как много.

Функция СУММПРОИЗВ в Excel позволит вам справится практически с любой экономической задачей, где фигурируют массивы. Подобрав правильные критерии или условия, с помощью формул или иным способом, любые задачи смогут капитулировать перед легкостью, с которой эта функция будет их решать.

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

Детально о том, как работает функция СУММПРОИЗВ в Excel вы можете ознакомиться здесь.

Оцените статью
Аналитик-эксперт
Добавить комментарий

Adblock
detector