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 анализ
Открываем каждый свой рабочий файл и создаем сводную таблицу по наименованиям и месяцам, переименовываем новый лист на “XYZ”
Убираем все промежуточные и общие итоги
В ячейке F3 прописываем формулу =STDEV.P(B3:E3) это среднеквадратичное отклонение
В ячейке G3 прописываем формулу =AVERAGE(B3:E3) это среднее арифметическое
В ячейке H3 делим первое на второе, =F3/G3, это и есть коэффициент вариации
Наконец, в ячейке I3 прописываем формулу =IF(H3<=0.25,”X”,IF(H3<=0.75,”Y”,”Z”))
Создаем в нашем рабочем файле новую вкладку. Кнопочка “+” в нижнем левом углу
Называем новую вкладку “Сводная аналитика ABC-XYZ”
Задаем следующие названия полям (шапке):
Код
Наименование
Категория Колво
Категория Сумма
Категория Маржа
Категория Волатильность
4.Копируем без шапки во вновь созданную вкладку первые два столбца из “ABC Продажи (Выручка)”: Код и Наименование
При помощи формулы =VLOOKUP ( ) доставляем из вкладки “ABC Кол-во” сегменты A, B или C
=VLOOKUP(A2, ‘ABC Кол-во’!A:G, 6, 0)
Используя условное форматирование,
закрашиваем значения (Формат → Условное
форматирование):
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 ○ Продажи Алматы
● Открываем 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) Продажи за прошедший полный месяц
● 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 Formaing, далее – 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(
● Вычисляемые столбцы для дней недели ○ 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 (Рабочая область) – позволяет изучить данные используя только один график на одной странице
● Загрузить ее в 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
● Создание параметров в источнике данных в 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. Не следует запрашивать доступ на редактирование. Необходимо именно создать дубликат.
3. C низу файла “Обучение Google Sheets – урок 1” в листах по названию можно увидеть контент по каждой функции.
1. В первую очередь разберем функцию =IMPORTRANGE() 2. Синтаксис a. =IMPORTRANGE(spreadsheet_url, range_string) 3. Примеры заполнения spreadsheet_url a. hps://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, который позволяет ограничивать тип данных или значения, которые могут быть введены в ячейку. Например, можно установить правило, чтобы в ячейке были только числа или значения из определенного списка.