Power Query, Pivot Tables для финансистов

Программа курса

I ABC-анализ в разрезе номенклатуры

Работа в Power Query
1. Приведение выгрузки в состояние плоской таблицы с помощью Power Query
• Создание умной таблицы
• Создание запроса Power Query
• Внесение корректировок в автоформат данных столбцов
• Функция «Сократить строки» - Удаление пустых строк
• Функция «Заполнить вверх»
• Функция «Сократить строки» - Удаление верхних строк
• Функция «Сократить строки» - Удаление чередующихся строк
• Функция «Удалить столбец»
2. Выгрузка запроса в отдельную закладку в формате плоской таблицы
3. Объединение нескольких выгрузок с помощью запросов Power Query
• Создание подключения без выгрузки данных в отдельную закладку
• Быстрое дублирование подключения и изменение ссылки на умную таблицу с помощью расширенного редактора
• Объединение нескольких запросов в один
• Функция «Разделение столбца по переходу»
• Функция «Замена значений»
• Доработка дополнительных аналитических полей

Работа со сводными таблицами (pivot tables)
4. Создание сводной таблицы
• Создание сводной таблицы
• Настройка параметров сводной таблицы

ABC-анализ в разрезе номенклатуры
5. Проведение ABC-анализа
• Создание таблицы для ABC-анализа
• ABC-анализ по выручке с помощью формулы =ЕСЛИМН()
• ABC-анализ по валовой прибыли с помощью формулы =ЕСЛИМН()
6. Определение товаров с низкой оборачиваемостью и высокой, с низкой рентабельностью и высокой

II ABC-анализ в разрезе контрагентов

Работа в Power Query
7. Приведение выгрузки в состояние плоской таблицы с помощью Power Query
• Функция «Добавление настраиваемого столбца» if then
• Исключение пустых ячеек в столбце
• Функция «Заполнить вниз»
• Функция «Разделить столбец по разделителю»
8. Выверка данных запроса с выгрузкой из 1С

Работа со сводными таблицами (pivot tables)
9. Подготовка таблицы к ABC-анализу
• Создание сводной таблицы
• Настройка параметров сводной таблицы
• Добавление вычисляемого поля
• Расчёт доли от общей суммы
• Расчет доли накопительным итогом
• Группировка контрагентов по номенклатурным подгруппам
• Сортировка данных внутри таблицы

ABC-анализ в разрезе контрагентов
10. Проведение ABC-анализа
• Создание таблицы для ABC-анализа
• ABC-анализ по выручке с помощью формулы =ЕСЛИМН()
• ABC-анализ по валовой прибыли с помощью формулы =ЕСЛИМН()

Работа со сводными таблицами (pivot tables)
11. Доработка сводной таблицы для дальнейшего анализа
• Расчет средневзвешенной цены с помощью вычисляемого поля по номенклатуре в разрезе контрагентов
• Отличие средней цены от средневзвешенной
• Вставка срезов в сводную таблицу
• Настройка срезов в сводную таблицу

III Получение данных по взаиморасчетам с контрагентами, расчёт оборачиваемости в днях

Работа в Power Query
12. Приведение выгрузки в состояние плоской таблицы с помощью Power Query
• Подготовка таблицы перед созданием запроса
• Создание умной таблицы
• Функция «Использовать первую строку в качестве заголовка»
• Функция «Сократить строки» - Удаление пустых строк

Работа со сводными таблицами (pivot tables)
13. Подготовка таблицы к ABC-анализу
• Создание сводной таблицы
• Добавление вычисляемого поля для расчёта оборачиваемости в днях

IV Анализ чистой валовой прибыли

Работа в Power Query
14. Создание таблицы для анализа чистой валовой прибыли
15. Доработка плоской таблицы для анализа
• Создание запросов
• Объединение запросов
• Слияние запросов на примере нечеткого соответствия
• Добавление настраиваемого столбца try otherwise

Анализ чистой валовой прибыли
16. Заполнение таблицы данными на основе сводной
17. Структурный анализ в абсолютном и относительном выражении по ЦФО