1)ABC analysis

2)XYZ analysis

3)ABC/XYZ analysis

  1. Power BI intro
  2. Модель данных и DAX формулы
  3. Базовые основы визуализации
  4. Месячный отчет по продажам
  5. Месячный отчет по продажам с Time intelligence
  6. Продвинутый DAX
  7. Bookmarks
  8. Облачный Power BI
  1. Введение в Google Looker studio
  2. ABC/XYZ анализ в Looker studio
  3. Параметризация в Looker studio
  1. Продвинутые Google таблицы
  2. Google Apps script

Google Sheets

ABC анализ: часть 1

1. Необходима учетная запись Google. У вас она должна быть для доступа в Google
Classroom. Пройдемся по интерфейсу Google-таблиц
2. Создаем копию этого файла Google Sheets. Не следует запрашивать доступ на
редактирование. Необходимо именно создать дубликат
3. При помощи функции =VLOOKUP ( ), на вкладку “Продажи” доставляем
наименования из соседней вкладки “Справочник” по коду товара
4. Используем следующую формулу: = VLOOKUP(A2, ‘Справочник’!A:B, 2 ,0). Примечание
– на вашем компьютере возможно следует использовать не запятые между
аргументами формул, а точку с запятой “;”. Копируем для всех остальных строк

Здесь важно отметить, что столбцы, а, правильнее – поля, в любом наборе
данных делятся на два вида:
1) Измерения / Dimensions – это то, по чему фильтруем, “нарезаем
данные”. Качественные характеристики. В нашем случае это Код и
Наименование. Проще говоря, текстовые данные

2) Меры / Measures – это то, что мы можем просуммировать, найти
среднее и т.д. Количественные характеристики. В нашем случае это
Кол-во, Сумма (выручка) и Маржа. Проще говоря, числовые данные

5. После того, как во вкладке “Продажи” у нас будут 5 столбцов – Код,
Кол-во, Сумма, Маржа и Наименование, выбрав целиком столбцы с A по
E, создаем сводную таблицу через меню “Вставка → Создать сводную
таблицу”

 

6. В открывшимся конструкторе нужно:
● В Строки выбрать (“прокинуть”) измерения Код
и Наименование
● Убрать галочку у Показывать Итог у
Наименования, чтобы убрать промежуточные
итоги. Они в данном случае нам не нужны
● В Значения прокинуть Кол-во
● Наконец, в мини карточке “Код” нужно выбрать
порядок сортировки по убыванию, и выбрать
сортировку по Сумме параметра Кол-во

7. Переименоваем Вкладку из “Pivot Table 1” / “Сводная Таблица 1” в “ABC
Кол-во” перемещаем в кладку в конец списка

ABC анализ: часть 2

1. В столбце D Рассчитаем удельный вес / долю каждого наименования =C2/$C$2588

    ● Знак “$” означает закрепление столбца, строки, либо и того и другого

2. Это не надежный подход. Уберем Итог в сводной таблице. В этот раз убираем Итог
из Код
3. В столбце E Рассчитаем тоже самое, но через чуть более сложную формулу
    =C2/sum(C$2:C) она будет работать всегда
4. В столбце F вставляем формулу для накопительного веса =SUM(C2:$C$2)
5. В столбце G вставляем формулу для определения класса A B или C

XYZ анализ

  1. Открываем каждый свой рабочий файл и создаем сводную таблицу по наименованиям и месяцам, переименовываем новый лист на “XYZ”
  2. Убираем все промежуточные и общие итоги
  3. В ячейке F3 прописываем формулу =STDEV.P(B3:E3)  это среднеквадратичное отклонение
  4. В ячейке G3 прописываем формулу =AVERAGE(B3:E3) это среднее арифметическое
  5. В ячейке H3 делим первое на второе, =F3/G3, это и есть коэффициент вариации
  6. Наконец, в ячейке I3 прописываем формулу =IF(H3<=0.25,”X”,IF(H3<=0.75,”Y”,”Z”))
  1. Создаем в нашем рабочем файле новую вкладку.  Кнопочка “+” в нижнем левом углу
  2. Называем новую вкладку “Сводная аналитика ABC-XYZ”
  3. Задаем следующие названия полям (шапке):
    • Код
    • Наименование
    • Категория Колво
    • Категория Сумма
    • Категория Маржа
    • Категория Волатильность

  4.Копируем без шапки во вновь созданную вкладку первые два столбца из “ABC Продажи (Выручка)”: Код и             Наименование

  1. При помощи формулы =VLOOKUP ( ) доставляем из вкладки “ABC Кол-во” сегменты A, B или C

      =VLOOKUP(A2, ‘ABC Кол-во’!A:G, 6, 0)

  1. Используя условное форматирование,

закрашиваем значения (Формат → Условное

форматирование):

ABC/XYZ анализ

1. Открываем каждый свой рабочий файл и создаем новую вкладку.
Кнопочка “+” в нижнем левом углу
2. Называем новую вкладку “Сводная аналитика ABC-XYZ”
3. Задаем следующий названия полям (шапке): Код, Наименование,
Категория Колво, Категория Сумма, Категория Маржа и Категория
Волатильность
4. Копируем во вновь созданную вкладку первые два столбца из “ABC
Продажи”: Код и Наименование

5. При помощи формулы =VLOOKUP ( ) доставляем из вкладки “ABC Кол-во”
сегменты A, B или C
=VLOOKUP(A2, ‘ABC Кол-во’!A:F, 6, 0)

6. Используя условное форматирование,
закрашиваем значения:

Power BI

Power BI intro

1. Подключаем в качестве источника данных файл Excel, которые вы
получили ранее
2. Выбираем вкладку COGS
3. Чистим данные в Power Query
4. Там же делаем Unpivot-данных
5. Загружаем данные в Модель данных
6. Делаем первые и простые визуализации

Модель данных и DAX формулы

1. Для того, чтобы доставить вкладку “Sales”, необходимо:
    a. Зайти в режим Advanced Editor
    b. Поменять “cogs” на “sales” строке с командой источника данных
    c. Поменять “COGS” на “Sales” строке с командой переименования столбца
    d. Нажимаем “Close & Apply”

2. В модели данных (не только в Power BI) существуют два вида таблиц
    a. Таблицы-справочники – содержат информацию об объектах (атрибуты,
        характеристики). В основном текст, мало строк и много столбцов
    b. Таблицы фактов / реестры / регистры / журналы – содержат сведения о событиях, в
        которые вовлечены те или иные объекты. В основном числа, много строк и мало
        столбцов

Таблица-справочник – в основном текст, мало строк и много столбцов:

Таблица фактов – в основном числа, много строк и мало столбцов:

3. Задача – рассчитать маржу и маржинальность. Именно для этого
необходимы модель данных и справочники
     a. Справочник дат – Календарь. Создаем вычисляемую таблицу через DAX
         Calendar = CALENDARAUTO ()
     b. Справочник магазинов. Создаем его через Power Query
     c. Соединяем таблицы фактов со справочниками

Базовые основы визуализации

● Копируем файлы Google Sheets к себе на Google Drive
     ○ Продажи Алматы 

        https://docs.google.com/spreadsheets/d/1z2wG8QzM2raJNfx_iOqhGTkZSlkCNrja7f11YiJ5VVE/edit#gid=1744391083

     ○ Продажи Астаны

      https://docs.google.com/spreadsheets/d/1uZ2rbzTEz7w-25u6geRblb5lH4Y4MoWTr6-KQ_eB8kc/edit#gid=1484212991
     ○ Справочник товаров

      https://www.google.com/url?q=https://docs.google.com/spreadsheets/d/1k-b3OtJoa1xDoGN27pFT33MWI-                        jan38pWVqlRHe5Aj8/edit%23gid%3D1983219947&sa=D&source=apps-viewer-                                                                frontend&ust=1713261946205875&usg=AOvVaw39J56Wz2t0eoblFqgGvOG0&hl=en

● Открываем Power Query и подключаем файл с продажами Алматы:
     ○ Выбираем тип источника данных (коннектор) Google Sheets
     ○ Прописываем URL (ссылку) файла. У каждого участника – свой URL
     ○ Проходим авторизацию, чтобы разрешить Power BI получать доступ к вашему файлу
     ○ Переименовываем запрос на “Almaty”
     ○ Чтобы наши продажи не смешались в будущем с продажами из Астаны, добавляем
         столбец City со значениями “Almaty” через команду “Add Custom Column”

● Подключаем файл с продажами Астаны:
     ○ Для этого выгодно дублировать алматинский запрос
     ○ Переименовываем запрос на “Astana”
     ○ Прописываем новый URL (ссылку) файла
     ○ Проходим авторизацию, чтобы разрешить Power BI иметь доступ к вашему файлу
     ○ Чтобы наши продажи не смешались c алматинскими, меняем значения в столбце City на “Astana”

● “Склеиваем” два запроса через команду Append Queries
● Деактивируем два уже ненужных
запроса с продажами Almaty и Astana

● Через фильтрацию, удаляем март 2023 г. В будущем мы его вернем обратно, чтобы
убедиться, что все отчеты пересчитаются для нового месяца автоматически
● Подключаем файл Справочник, создав новое подключение Google Sheets.
Переиспользовать ранее созданные нельзя, так как формат другой
● Создаем справочник дат, т.е. календарь в виде вычисляемой таблицы, но уже через
формулу DAX
      Календарь = CALENDAR(
       min(‘Sales'[Период] ),
       maX(‘Sales'[Период] )
       )

Месячный отчет по продажам с Time Intelligence: Часть 1

1) Explicit-мера для суммы продажи в тенге

      0 Sales = SUM(Sales[Тенге])
2) Продажи за прошедший полный месяц

      1 TYMonth = CALCULATE(
      [0 Sales],
      LASTDATE(‘Sales'[Период])
       )

3) Продажи за месяц прошлого года
      2 LYMonth = CALCULATE(
      [1 TYMonth],
      DATEADD(LASTDATE(‘Продажи'[Период]),-1,year)
       )

4) Сравнение месяц-к-месяцу 3 LYMonth, % = [1 TYMonth] / [2 LYMonth] – 1

● WTD – Week-to-Date, с понедельника по пятницу
● MTD – Month-to-Date, с 1-го по 8-е декабря включительно
● YTD – Year-to-Date, с января по ноябрь включительно
         Не говорим December YTD, но говорим Full Year

5) Накопительные продажи с начала текущего года
        4 TY YTD = TOTALYTD(
        [0 Sales],
        ‘Календарь'[Date]
         )
6) Накопительные продажи за аналогичный период прошлого года
        5 LY YTD = CALCULATE(
        [4 TY YTD],
        DATEADD(‘Календарь'[Date], -1, YEAR)
         )
7) Сравнение YTD против аналогичного периода прошлого года 6 LY YTD, % = [4 TYMonth] / [5 LYMonth] – 1

Для того, чтобы отформатировать
абсолютные значения – [2 LYMonth] и [1
TYMonth]:
1) На панели Visualizations, выбираем
метрику из раздела Values для
матрицы, как на скриншоте слева
2) Нажимаем на маленькую стрелочку
вниз
3) Выбираем Conditional Formaing,
далее – Data Bars
4) Выбираем нужный цвет

Для того, чтобы отформатировать
относительные значение – [3 LYMonth, %]
         1) На панели Visualizations, выбираем метрику из раздела Values для матрицы, как на скриншоте слева
         2) Нажимаем на маленькую стрелочку вниз
         3) Выбираем Conditional Formatting, далее – Data Icons
         4) Настраиваем в точности так, как на скриншоте на следующем слайде

Месячный отчет по продажам с Time Intelligence: Часть 2

● Создаем таблицу со списком Display Metric for Trend
             ○ Display Metric for Trend = DATATABLE(

                 “Metric”,

                  String,
                  {{“1. Тенге”},{“2. Кол-во”},{“3. Ср. цена”}}
                    )

● Добавляем фильтр для выбора метрики

● Формула для динамического отображения выбранного параметра

        0 Sales = SWITCH(
                SELECTEDVALUE(‘Display Metric for Trend'[Metric]),
                “1. Тенге”, sum(‘Продажи'[Тенге]),
                “2. Кол-во”, sum(‘Продажи'[Кол-во])
                  )

Продвинутый DAX

● Вычисляемые столбцы для дней недели
       ○ WeekDayNum = WEEKDAY(‘Calendar'[Date],2)
       ○ WeekDay Rus = SWITCH(
             ■ WEEKDAY(‘Calendar'[Date],2),
             ■ 1,”ПН”,
             ■ 2,”ВТ”,
             ■ 3,”СР”,
             ■ 4,”ЧТ”,
             ■ 5,”ПТ”,
             ■ “ВЫХ”
          )

● Новые CALCULATE-меры
        ○ Альтернативный способ расчета YTD
     11 Sales YTD ver 2 = CALCULATE(
     sum(‘Sales'[Тенге]),
     DATESYTD(‘Calendar'[Date])
     )

● Новые CALCULATE-меры
           ○ Накопительные продажи за все время
     12 SalesAmountRT =
     CALCULATE (
     SUM (‘Sales'[Тенге]),
     FILTER (
     ALL (‘Calendar’),
     ‘Calendar'[Date] <= MAX ( ‘Calendar'[Date] )
       )
    )

● Новые CALCULATE-меры
           ○ Продажи всех сегментов
      13 Sales All Segments = CALCULATE(
      [0 Sales], all(‘Products’)
      )

● Новые CALCULATE-меры
            ○ Продажи всех сегментов
       14 Sales All Segment1 = CALCULATE( //Это тестовый коммент
       [0 Sales],
       all(‘Products'[Сегмент4])
      )

● Новые CALCULATE-меры
           ○ Удельный вес для Сегмента-4
      15 Share By Segment 4 =
      [0 Sales] /
      CALCULATE(
      [0 Sales],
      ALLSELECTED(‘Products'[Сегмент4])
    )

● Новые CALCULATE-меры
            ○ Продажи только обуви
       16 SalesOnlyShoes =
       VAR S = CALCULATE(
       [0 Sales],
       FILTER(Products, ‘Products'[Сегмент4] =”Обувь”)
     )
     return S

Bookmarks

Задача – 1

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

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

● В открывшейся панели, двойным кликом переименовываем элементы
на Right Button и Left Button соответственно. А также сами
визуализации, для нашего дальнейшего удобства
● Через View раскрываем панель закладок. Мы уже работали с ней на
прошлом уроке:

● На панели выбора элементов, скрываем (нажимаем на глазик)
      ○ Left Button
      ○ Left Visual
● Этот сценарий мы программируем для левой кнопочки
● Создаем Закладку, назвав ее “For left Button”
● На выходе должно получиться следующее:

● На панели выбора элементов включаем все элементы, а затем
скрываем (нажимаем на глазик)
        ○ Right Button
        ○ Right Visual
● Этот сценарий мы программируем для правой кнопочки
● Создаем Закладку, назвав ее “For Right Button”
● На выходе должно получиться следующее:

● Наконец, осталось назначить на кнопки соответствующее действие, то
есть закладку. Нажимаем на закладку “For Left Button”, чтобы
отобразить правую кнопочку
● На правую кнопку закладку назначаем For Right Button. Это делается в
разделе Image, подразделе Action

● Включаем закладку “For Right Button”, чтобы отобразить левую кнопку
● На левую кнопку назначаем закладку – For Left Button. Это делается
аналогично предыдущему шагу

● Тестируем сценарии, которые мы запрограммировали, нажимая на
тумблер-переключатель левой клавиши мыши, удерживая кнопку
“CTRL” на клавиатуре

Облачный Power BI

● Workspace – рабочая область, подобие папок
       ○ Туда публикуются (выкладываются) отчеты Power BI
       ○ Настраивается доступ
● Существует следующие виды элементов в рабочих областях

● Через Power BI можно исследовать данные Explorer. Очень крутая вещь,
которая появилась недавно
● Можно смотреть просмотры отчетов
● Можно делать авто-отчеты
● Можно смотреть поток данных
● Microsoft Fabric

Looker studio

Looker studio intro

Составляющие Looker Studio
1. Reports (Отчеты)- дэшборды, страницы где строится
визуализация
2. Data Sources (Источники данных)- добавленные
источники данных для визуализации (Google Sheets,
Google Ads, Ga, etc)
3. Explorer (Рабочая область) – позволяет изучить данные
используя только один график на одной странице

● Скачать и сохранить файл “Sample – Superstore”

https://docs.google.com/spreadsheets/d/1SbS2HJg87IUgY9V5Q2T6LU-1G1UllOqGxK5U0HvhzrE/edit#gid=1147561053

● Загрузить ее в Google Sheets
● Подготавливаем данные для визуализации в Google Sheets.
Каждый столбец должен иметь уникальное название
● Перейти на ссылке https://lookerstudio.google.com/overview и
нажать на кнопку “USE IT FOR FREE”
● Залогиниться под вашей учетной записью Google

● При добавлении нового источника данных, в первом большом
разделе “Google Connectors”, выбираем самый последний пункт
“Google таблицы”
● В открывшемся окне выбираем ранее открытый файл “Sample –
Superstore”
● Подключаем файл, как источник данных

Список создаваемых формул
Total Sales = SUM (Total Sales)
Total Profit = SUM (Total Profit)
Total Order = COUNT_DISTINCT (Order ID)
Total Item = SUM (Total Quantity)

ABC/XYZ анализ в Looker studio

● Открываем и создаем копию следюущего файла

https://docs.google.com/spreadsheets/d/1Z_F_NlSs2GGjGK0LKzbIJ_lfgb
EOqRWToG5i7Yk5fP4/

● Вычисление и отображение удельного веса на визуализации для ABC:

● Вычисление и отображение вариативности (для XYZ):

● Настройки условного форматирования

Параметризация в Looker studio

● Создание параметров в источнике данных в Looker Studio

● Создание вычисляемых мер с параметрами
       sum(Sales B) / sum( Sales A) – 1

Advanced Google sheets

Продвинутые Google таблицы

Importrange

● Назначение
       ○ Позволяет автоматически импортировать данные из одного листа Google Sheets в другой.
● Синтаксис
       ○ =IMPORTRANGE(url; диапазон)
● Возможности
       ○ Объединение данных из разных таблиц.
● Ограничения
       ○ Разрешение на доступ, производительность.

Query

● Назначение
        ○ Позволяет выбирать, фильтровать, сортировать и агрегировать данные в Google Sheets.
● Синтаксис
        ○ =QUERY(источник_данных; текст_запроса; [число_заголовков])
● Возможности
        ○ Выборка, фильтрация, сортировка, агрегация, группировка.
● Ограничения
        ○ Производительность, некоторые функции SQL не поддерживаются полностью.

1. Необходима учетная запись Google. У вас она должна быть для доступа
в Google Classroom.
2. Создаем копию этих файлов Google Sheets к себе в отдельную папку на
Google Drive. Не следует запрашивать доступ на редактирование.
Необходимо именно создать дубликат.

https://drive.google.com/drive/folders/12ebFHD3hwKGtdanRwX7REAAFCkuKLB6z

3. C низу файла “Обучение Google Sheets – урок 1” в листах по названию
можно увидеть контент по каждой функции.

1. В первую очередь разберем функцию =IMPORTRANGE()
2. Синтаксис
       a. =IMPORTRANGE(spreadsheet_url, range_string)
3. Примеры заполнения spreadsheet_url
       a. hps://docs.google.com/spreadsheets/d/1BLBoJOwU4vPy0DJNVExHchV2MYTd5mC1OJW
           k5vcHeFI/edit#gid=273070153
       b. 1hQw_dOibrjjDKZcg80h2szpy7R8tIzp3o3oI6HXjsDA
4. Примеры заполнения Range string
       a. “Sheet1!A2:B6”
       b. “A2:B6”

1. В первую очередь разберем функцию =IMPORTRANGE()
2. Синтаксис
         a. =QUERY(data, query, [headers])
3. Примеры заполнения data
         a. Данные с одного листа:
              i. с помощью IMPORTRANGE()
              ii. с помощью ссылки на соседний лист “Sheet1!A2:B6” или на самого себя “A2:B6”
         b. В секцию data можно объединять несколько листов одновременно. Вот пример:

4. Примеры заполнения query
          a. select – “select A, B, C, D “
                   i. выборка столбцов
          b. select & where – “select A where (D<>’IT’ and M=’Chicago’) or (F =’Female’)”
                   i. фильтрация по категориям
          c. group by – “select B, MAX(J) group by B”
                   i. поможет в определении магазина с максимальным обьемом продаж по категориям
          d. pivot – “select avg(J) pivot D”
                   i. сравнение средних значений по категориям. К примеру сравнение средних цен по разным категориям
          e. order by – “select B, MAX(J) group by B order by MAX(J)”
                   i. можно упорядочить магазины по убыванию их максимальных продаж
          f. Pro Query – =QUERY( QUERY(data, query, [headers]))
                   i. вы можете сначала сгруппировать данные по одному критерию, а затем применить фильтрацию или                           другую агрегацию к этим результатам.

5. Примеры заполнения [headers]
              a. положительное число указывает на количество строк заголовков в диапазоне данных.
              b. ноль, отсутствие или негативное значения позволяет Google Sheets самостоятельно
                  определить количество строк заголовков.

Google Apps Script

 

Apps script

● Назначение
            ○ Облачная платформа для создания скриптов, которая позволяет автоматизировать и
                расширять возможности различных приложений Google, включая Google Sheets, Docs, Forms, Calendar и                    другие

● Синтаксис
            ○ Нужны знания языка программирования JavaScript.
● Возможности
            ○ API интеграции, Веб-приложения, Диалоговые окна в приложениях Google,
                автоматизация задач и прописные скрипты.

● Ограничения
            ○ Производительность, лимиты на по времени выполнения и использовании ресурсов.

Чтобы открыть панель Apps Script вам нужно перейти сюда:

У вас должно появиться вот такое окошко:

1. Первая функция

1. Вторая функция

1. Функция lesson_1_Basics в Google Apps Script используется для работы с активным
листом в Google Sheets. Она устанавливает значение 99 в ячейку D2 и затем
считывает это значение обратно в переменную, но без дальнейших действий с этой
переменной. Это базовый пример установки и получения данных из ячейки таблицы.

2. Функция on Edit(event) автоматически срабатывает при редактировании ячеек. Она
проверяет, была ли отредактирована ячейка в первых трех столбцах на
определенном листе, и если соответствующая ячейка в следующей строке пуста, то
функция записывает в ячейку шестого столбца текущую дату. Эта функция
демонстрирует использование событий редактирования для автоматической
записи данных в таблицу в зависимости от выполнения определенных условий.

 

Методология по ДДС

1. Денежный поток (cash flow) — это движение денег в компании за
определенный период: то, откуда они приходят, на что тратятся и
сколько остается.

1. ARRAYFORMULA – Эта функция позволяет применять формулу к диапазону ячеек, а не к одной ячейке.
       Она распространяет формулу на каждую ячейку в указанном диапазоне.
2. SUMIFS – Функция для суммирования ячеек, которые соответствуют нескольким условиям. Она
    позволяет задать несколько критериев, и суммирует только те ячейки, которые соответствуют всем
    критериям.
3. TODAY – Возвращает текущую дату. Формула не имеет аргументов и обновляется автоматически
    каждый день.
4. MONTH – Извлекает месяц из указанной даты. Возвращает число от 1 (январь) до 12 (декабрь).
5. YEAR – Аналогично функции MONTH, но извлекает год из даты.
6. UNIQUE – Эта функция возвращает уникальные значения из указанного диапазона или массива. Она
    помогает убрать повторяющиеся элементы.
7. Проверка данных – инструмент в Google Sheets, который позволяет ограничивать тип данных или
    значения, которые могут быть введены в ячейку. Например, можно установить правило, чтобы в
    ячейке были только числа или значения из определенного списка.