Как установить google excel

Начинаем работу с Google Sheets на Python. От регистрации до чтения данных

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

Начав переводить ряд проектов на Python, решил, что самое время сменить (или дополнить) Excel чем-то более современным.

Когда я впервые столкнулся с необходимостью работы c таблицами Google из Python, то пребывал в иллюзии, что все это можно сделать в пару кликов. Реальность оказалась менее радужной, но другого глобуса у нас нет.

Мне очень помогли статьи:

• Генерируем красивую Google-таблицу из своей программы (используя Google Sheets API v4)
• Начинаем работать с Google Sheets API v4/
• Работаем с API Google Drive с помощью Python

Возможно, я просто шел длинным путем – буду рад, если вы меня поправите.

Все действия выполнялись на компьютере с Windows + Python 3.6.6, также использовался Jupyter Notebook.

Основные трудности у меня возникали на этапе предварительных настроек. Найти работоспособный код не представляет особого труда.

Код, использованный в статье, доступен в репозитории

Регистрация в сервисах Google и установка библиотек

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

Сначала нужно зарегистрироваться на gmail.com (это вы можете сделать самостоятельно). Потом нужно создать проект (так Google предоставляет доступ к своим сервисам).

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

Для этого зайдите на страницу console.developers.google.com/cloud-resource-manager и нажать «Создать проект»

Введите имя проекта и нажмите «Создать»

В обновленном списке проектов зайдите в меню «Права доступа»

В открывшемся окне нажмите «Добавить», внесите свой email с домена gmail.com и выберите группу «Проект» — «Владелец»

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

Выберите на своем проекте меню «Настройки»

В открывшемся окне выберите «Сервисные аккаунты», а затем «Создать сервисный аккаунт»

Введите название аккаунта и нажмите «Создать»

Выберите роль «Владелец» и нажмите «Продолжить»

В появившемся окне нажмите «Создать ключ»

Выберите тип ключа «json» и нажмите «Создать»

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

Нажмите на кнопку с тремя горизонтальными штрихами, слева от надписи «Google APIs», выберите пункт «API и сервисы», а в нем подпункт «Панель управления».

В открывшемся окне нажмите «Включить API и сервисы»

Введите в строку поиска «google drive» и кликните на сервисе «Google Drive API»

Сайт уведомит вас, что API включено и предупредит, что нужно создать учетные данные. Игнорируйте это предупреждение (ведь мы уже создали сервисный аккаунт).

Снова заходите в панель управления

В открывшемся окне нажмите «Включить API и сервисы»

Введите в строку поиска «sheet» и кликните на сервисе «Google Sheets API»

Убедитесь, что это API подключено. Оно должно включиться автоматически, при подключении Google Drive API. Если оно подключено, вы увидите кнопку «Управление API», если нет — кнопку «Включить». Включите его, при необходимости.

Выберите на своем проекте меню «Настройки»

В открывшемся окне выберите «Сервисные аккаунты», а затем скопируйте и сохраните email сервисного аккаунта. Он пригодится вам, чтобы выдавать доступ к таблицам.

Теперь переходим к установке библиотек. Выполните в консоли команду

pip3 install --upgrade google-api-python-client

pip3 install oauth2client

Возможно, что при запуске второй команды вы получите сообщение, что библиотека oauth2client уже установлена.

Нажмите правую кнопку мышки и выберите «Сохранить как»

Сохраните файл под именем quickstart.py

и запустите его командой

python quickstart.py

Откроется новая страница в браузере (возможно, он скажет, что страница небезопасная, но смело идите вперед) и вам надо будет принять условия.

На этом наш путь завершен.

Заполнение и форматирование таблицы

Создадим первую таблицу

# Подключаем библиотеки import httplib2 import apiclient.discovery from oauth2client.service_account import ServiceAccountCredentials CREDENTIALS_FILE = 'seraphic-effect-248407-7ac2c44ec709.json' # Имя файла с закрытым ключом, вы должны подставить свое # Читаем ключи из файла credentials = ServiceAccountCredentials.from_json_keyfile_name(CREDENTIALS_FILE, ['https://www.googleapis.com/auth/spreadsheets', 'https://www.googleapis.com/auth/drive']) httpAuth = credentials.authorize(httplib2.Http()) # Авторизуемся в системе service = apiclient.discovery.build('sheets', 'v4', http = httpAuth) # Выбираем работу с таблицами и 4 версию API spreadsheet = service.spreadsheets().create(body = < 'properties': , 'sheets': [>>] >).execute() spreadsheetId = spreadsheet['spreadsheetId'] # сохраняем идентификатор файла print('https://docs.google.com/spreadsheets/d/' + spreadsheetId) 

Если все прошло без ошибок — на экран будет выведена ссылка на таблицу.

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

Переходите по ней. Google сообщит вам, что у вас нет доступа

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

Но мы можем выдать себе доступ через Google Drive. Вам нужно заменить адрес my_test_address@gmail.com на свой.

driveService = apiclient.discovery.build('drive', 'v3', http = httpAuth) # Выбираем работу с Google Drive и 3 версию API access = driveService.permissions().create( fileId = spreadsheetId, body = , # Открываем доступ на редактирование fields = 'id' ).execute()

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

У каждого документа есть свой код — spreadsheetId — именно от отображается в адресной строке, когда мы открываем таблицу в браузере (в URL-е страницы с открытой таблицей он находится между «https://docs.google.com/spreadsheets/d/» и «/edit#gid=0»).

Мы сохранили его в переменной spreadsheetId и дальше будем с ним работать.

Сначала немного теории.

В каждом файле (spreadsheet) находятся листы-вкладки (sheet).

Каждый sheet имеет свой числовой код (sheetId). У первого созданного в документе листа этот Id равен 0. Остальные листы имеют сильно отличные от нуля Id (т.е. они не нумеруются подряд).

Убедимся в этом

# Добавление листа results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = < "requests": [ < "addSheet": < "properties": < "title": "Еще один лист", "gridProperties": < "rowCount": 20, "columnCount": 12 >> > > ] >).execute() # Получаем список листов, их Id и название spreadsheet = service.spreadsheets().get(spreadsheetId = spreadsheetId).execute() sheetList = spreadsheet.get('sheets') for sheet in sheetList: print(sheet['properties']['sheetId'], sheet['properties']['title']) sheetId = sheetList[0]['properties']['sheetId'] print('Мы будем использовать лист с sheetId) 

На экране появится нечто вроде:

0 Лист номер один
415832263 Еще один лист
Мы будем использовать лист с >

В самом деле, первый лист имеет Id равный нулю, а второй пронумерован иначе.

Еще один вопрос: как указывать диапазоны ячеек. Видимо, таблицы Google разрабатывали разные команды, под руководством разных менеджеров и при помощи разных архитекторов. Потому, что координаты ячеек задаются двумя разными способами.

Вариант 1: в формате текста «Лист номер один!B2:D5», т.е. имя листа, после него восклицательный знак, после — левая верхняя ячейка в формате «буква (колонка) + цифра (строка)» + правая нижняя ячейка в таком же формате.

Вариант 2: в json-формате, с указанием ID листа и координат левой верхней и правой нижней ячеек в числовом виде (номер строки и номер столбца)

Разные функции используют разные форматы.

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

results = service.spreadsheets().values().batchUpdate(spreadsheetId = spreadsheetId, body = < "valueInputOption": "USER_ENTERED", # Данные воспринимаются, как вводимые пользователем (считается значение формул) "data": [ ] >).execute()

Заполняем несколько ячеек данными. Т.к. указан параметр USER_ENTERED, таблица воспринимает эти данные так, как восприняла бы ввод руками пользователя — преобразует числовые значения в числа, а значения, начинающиеся со знака «равно» в формулы.

Посмотрите в вашу таблицу, она заполнилась данными

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

results = service.spreadsheets().batchUpdate(spreadsheetId = spreadsheetId, body = < "requests": [ # Задать ширину столбца A: 20 пикселей < "updateDimensionProperties": < "range": < "sheetId": sheetId, "dimension": "COLUMNS", # Задаем ширину колонки "startIndex": 0, # Нумерация начинается с нуля "endIndex": 1 # Со столбца номер startIndex по endIndex - 1 (endIndex не входит!) >, "properties": < "pixelSize": 20 # Ширина в пикселях >, "fields": "pixelSize" # Указываем, что нужно использовать параметр pixelSize > >, # Задать ширину столбцов B и C: 150 пикселей < "updateDimensionProperties": < "range": < "sheetId": sheetId, "dimension": "COLUMNS", "startIndex": 1, "endIndex": 3 >, "properties": < "pixelSize": 150 >, "fields": "pixelSize" > >, # Задать ширину столбца D: 200 пикселей < "updateDimensionProperties": < "range": < "sheetId": sheetId, "dimension": "COLUMNS", "startIndex": 3, "endIndex": 4 >, "properties": < "pixelSize": 200 >, "fields": "pixelSize" > > ] >).execute()

Посмотрите на таблицу, ширины колонок изменились.

Нарисуем рамку вокруг таблицы

# Рисуем рамку results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = < "requests": [ , 'bottom': < # Задаем стиль для верхней границы 'style': 'SOLID', # Сплошная линия 'width': 1, # Шириной 1 пиксель 'color': >, # Черный цвет 'top': < # Задаем стиль для нижней границы 'style': 'SOLID', 'width': 1, 'color': >, 'left': < # Задаем стиль для левой границы 'style': 'SOLID', 'width': 1, 'color': >, 'right': < # Задаем стиль для правой границы 'style': 'SOLID', 'width': 1, 'color': >, 'innerHorizontal': < # Задаем стиль для внутренних горизонтальных линий 'style': 'SOLID', 'width': 1, 'color': >, 'innerVertical': < # Задаем стиль для внутренних вертикальных линий 'style': 'SOLID', 'width': 1, 'color': > >> ] >).execute() 

Объединим ячейки над таблицей и впишем в них заголовок

# Объединяем ячейки A2:D1 results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = < "requests": [ , 'mergeType': 'MERGE_ALL'>> ] >).execute() # Добавляем заголовок таблицы results = service.spreadsheets().values().batchUpdate(spreadsheetId = spreadsheetId, body = < "valueInputOption": "USER_ENTERED", # Данные воспринимаются, как вводимые пользователем (считается значение формул) "data": [ ] >).execute() 

Установим формат у ячеек заголовка таблицы

# Установка формата ячеек results = service.spreadsheets().batchUpdate( spreadsheetId = spreadsheetId, body = < "requests": [ < "repeatCell": < "cell": < "userEnteredFormat": < "horizontalAlignment": 'CENTER', "backgroundColor": < "red": 0.8, "green": 0.8, "blue": 0.8, "alpha": 1 >, "textFormat": < "bold": True, "fontSize": 14 >> >, "range": < "sheetId": sheetId, "startRowIndex": 1, "endRowIndex": 2, "startColumnIndex": 1, "endColumnIndex": 4 >, "fields": "userEnteredFormat" > > ] >).execute() 

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

ranges = ["Лист номер один!C2:C2"] # results = service.spreadsheets().get(spreadsheetId = spreadsheetId, ranges = ranges, includeGridData = True).execute() print('Основные данные') print(results['properties']) print('\nЗначения и раскраска') print(results['sheets'][0]['data'][0]['rowData'] ) print('\nВысота ячейки') print(results['sheets'][0]['data'][0]['rowMetadata']) print('\nШирина ячейки') print(results['sheets'][0]['data'][0]['columnMetadata']) 

Значения и раскраска
[, ‘effectiveValue’: , ‘formattedValue’: ‘Ячейка C2’, ‘userEnteredFormat’: , ‘horizontalAlignment’: ‘CENTER’, ‘textFormat’: >, ‘effectiveFormat’: , ‘padding’: , ‘horizontalAlignment’: ‘CENTER’, ‘verticalAlignment’: ‘BOTTOM’, ‘wrapStrategy’: ‘OVERFLOW_CELL’, ‘textFormat’: , ‘fontFamily’: ‘Arial’, ‘fontSize’: 14, ‘bold’: True, ‘italic’: True, ‘strikethrough’: False, ‘underline’: False>, ‘hyperlinkDisplayType’: ‘PLAIN_TEXT’>>]>]

Этот код выведет свойства ячейки C2. Можно выбрать шрифт и цвет заливки вручную (в таблице), в потом увидеть, как они отражаются в json.

Чтение данных из таблицы

Чтобы особенности чтения данных проявились в полной мере, я вручную заполнил ячейки B4, C7 и D5 как показано на рисунке.

Код для чтения данных

ranges = ["Лист номер один!A2:F8"] # results = service.spreadsheets().values().batchGet(spreadsheetId = spreadsheetId, ranges = ranges, valueRenderOption = 'FORMATTED_VALUE', dateTimeRenderOption = 'FORMATTED_STRING').execute() sheet_values = results['valueRanges'][0]['values'] print(sheet_values)

[[», ‘Ячейка B2’, ‘Ячейка C2’, ‘Ячейка D2′], [», ’25’, ’36’, ‘0,9999996829’]]

Некоторые параметры функции: valueRenderOption — формат чтения числовых данных.

• FORMATTED_VALUE — чтение с учетом формата отображения. Т.е. что было видно в таблице, то и прочитается. Например, в ячейке D3 число 0,9999999, но выбран формат «два знака после запятой», поэтому отображается «1,00», именно в таком формате оно и прочитается.
• UNFORMATTED_VALUE — читается содержимое ячейки, без учета настроек форматирование (т.е. прочиталось бы 0,9999999)
• FORMULA — отображается формула (в этом случае «=sin(3,14/2)». Если в ячейке введено число, то в этом режиме оно и прочитается.

• Если ни одна ячейка в читаемой строке не заполнена — данные по строке не выводятся.
• Данные после последней заполненной ячейки не выводятся.

Создание таблиц в Google Docs

В Гугл Докс таблицы представляют из себя сетку ячеек, упорядоченных по строкам и столбцам. Их настраивают и используют для представления текстовой информации и числовых данных различных ресурсов.
Чтобы приступить к работе с таблицами в Google Docs, давайте ознакомимся с фундаментальной основой их построения.

ПОЛЕЗНЫЕ СОВЕТЫ ОТ НАШИХ РАЗРАБОТЧИКОВ

Начало работы

1. Перейдите на страницу https://docs.google.com/
Поместите курсор на место, где бы Вы хотели создать таблицу.

2. Кликните на выходящее вниз меню таблица, и нажмите мышью вставить таблицу. Появится сетка квадратов.

3. Перетащите мышь на сетку, чтобы выбрать количество строк и столбцов в таблице.

4. Кликните мышью, и таблица появится на экране. Курсор теперь будет находиться в левом верхнем углу таблицы.

Не хотите разбираться в непонятных формулах?

Запишитесь на консультацию. Мы подберем для вас решение и научим пользоваться

* Для того, чтобы удалить таблицу кликните правой кнопкой мыши на таблицу и выберете пункт удалить таблицу.

Добавляем дополнительную строку

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

2. Новая строка появилась в таблице.

Не нашли ответа на свой вопрос?
 Посмотрите другие наши решения для бизнеса

Добавляем дополнительный столбец

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

2. Новый столбец появился в таблице.

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

В Google Docs Вы можете вносить изменения в существующую таблицу, изменяя свойства, такие как размер ячеек (передвижение границ), выравнивание, размер границы и цвет.

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

2. Кликните и перетащите границу ячейки. Синяя линяя будет помогать Вам увидеть новую длину и ширину ячейки.

3. Отпустите мышь в нужном месте. Размеры ячейки изменятся.

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

В Google Docs смена выравнивания горизонтальных ячеек делается при помощи четырех кнопок на панели инструментов быстрого доступа.

1. Выделите ячейку или ячейки, которые Вы хотите выровнять.

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

В Google Docs можно изменить вертикальное выравнивание ячейки, изменив в диалоговом окне «свойства таблицы»

1. Выделите ячейку или ячейки, которые Вы хотите выровнять.

2. Кликните правой кнопкой мыши и выберете свойства таблицы в появившемся меню.

3. Появится диалоговое окно свойства таблицы. Щелкните на меню выравнивания ячейки по вертикали.

4. Выберете нужное вам выравнивание.

Кликните ОК и текст появится.

Изменение размеров границ

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

Кликните ОК и выбранный вами размер появится в документе.

Изменение цвета фона ячейки

1. Чтобы изменить цвет фона ячеек выделите ячейку или ячейки, которые вы хотите изменить.
2. Правой кнопкой мыши нажмите на свойства таблицы .
3. Появится диалоговое окно свойства таблицы . Кликните цвет фона ячеек и выберете нужный вам цвет.

Нажмите ОК и новый цвет будет установлен.

Изменение цвета границ таблицы

1.Чтобы изменить цвет границ в любом месте таблицы кликните правой кнопкой мыши и выберете свойства таблицы в предложенном меню.

2. Появилось диалоговое окно свойства таблицы . Нажмите на цвета границы таблицы и выберете нужный вам цвет.

3. Нажмите ОК и новый цвет будет установлен.

Как сделать дашборд в Гугл Таблице — шаблоны и процесс создания

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

Для чего это нужно

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

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

Как работает такой дашборд

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

Как и в случае с Excel, для dashboard google таблиц можно использовать «Срезы», которые позволят оперативно фильтровать данные по разным категориям, например:

• период отчета (год, месяц);
• отделы;
• менеджеры;
• регионы и так далее.

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

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

По сравнению с Excel, создание дашбордов в Гугл Таблицах имеет серьезное отличие. Оно заключается в возможности доступа по ссылке всем заинтересованным лицам — ознакомиться с визуализацией можно через обычный браузер.
Тем не менее разработка Microsoft более зрелая, так как находится в работе намного дольше предложения от Google и поэтому имеет несколько преимуществ:

• работает с большим количеством скриптов и макросов;
• удобнее и привычнее для многих пользователей;
• предлагает заметно большее количество вариантов визуализаций.

Конечно, сервис от Google не сможет заменить специальные инструменты для бизнес-аналитики, например, платформу Power BI. Он не приспособлен для профессиональной визуализации и имеет ограниченную функциональность. Тем не менее для небольшого массива данных и простых отчетов вполне может подойти.

Профессиональное владение Power BI

Интерактивные отчеты за 3 часа

Быстрый и удобный анализ данных

Красивые и понятные аналитические отчеты за 1 месяц

Дашборд в Power BI с нуля до профи
ОНЛАЙН-КУРС

Как создать дашборд в Гугл Таблицах

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

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

• KPI всех отделов продаж по сумме, выполнению плана, числу сделок и конверсии;
• динамику продаж по разным периодам;
• объемы продаж по всем менеджерам вместе и по каждому в отдельности;
• объемы и доли продаж по регионам;
• эффективность разных каналов реализации.

Пример дашборда в Гугл Таблице

Для начала создадим новую google таблицу. Как это делать, вы наверняка знаете, но на всякий случай покажем на скриншотах. Присваиваем ей название: в нашем случае это будет «Дашборд 2».

Заранее подготовим 2 листа файла: они пригодятся в процессе.

• «Объединенная таблица» — здесь будем «склеивать» разные Гугл Таблицы.
• «Дашборд» — для макета будущего дашборда: его мы и будем наполнять данными из сводных таблиц.

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

Создаем макет

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

• в верхней части — карточки с ключевыми показателями (KPI);
• в центре — данные для отображения динамики или сравнения показателей;
• в нижней части — анализ продаж по интересующим нас срезам для принятия управленческих решений.

Заходим в «Вставить диаграмму». На листе появляется пустой прямоугольник, в котором мы будем помещать графики и другие визуальные элементы — он называется плиткой.

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

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

Подключаем данные из разных Гугл Таблиц

Для создания отчета мы будем использовать показатели отделов продаж двух филиалов компании. Допустим, эти данные располагаются в разных таблицах: разберемся, как объединить их в одном месте.
Итак, у нас есть файлы «Таблица-источник 1» и «Таблица-источник 2» с идентичным содержимым и структурой. В столбцах располагаются:

• номера заказов;
• каналы продаж;
• продуктовые сегменты;
• фамилии менеджеров;
• наименования заказов;
• продавцы;
• города;
• факт;
• план;
• даты заключения сделок;
• даты оплат;
• статусы.

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

Лайфхаки Excel для создания дашбордов

Работа с Power Query и Power Pivot

Быстрая подготовка данных для отчета

Результат за 4 дня: из грязи — в Excel-князи

Обработка данных в Excel
ОНЛАЙН-КУРС

В созданный заранее лист «Объединенная таблица» начинаем подключать наши источники. Делаем это с помощью:

• функций QUERY и IMPORTRANGE;
• оператора консолидации < >;
• SQL запроса «select * where Col1 is not null order by Col10».

Вся формула объединения будет выглядеть так:

=QUERY(IMPORTRANGE(«1vB6WfSHNAqqAjR_ingLxPnChoZfEfmAfrCrb9wOzFHk»;»Лист1!A1:N»);
IMPORTRANGE(«1Viy0xCOcVLjiVFlNqcq0Lo1zpXTtRgH6_cVkL83hNn0″;»Лист1!A2:N»)>;
«select * where Col1 is not null order by Col10»)

1. QUERY выполняет SQL запросы. Ниже указан ее синтаксис. Эта функция позволит нам «склеить» таблицы из разных источников на одном листе, разместить их друг под другом без пропусков и пустых строк между ними.

2. Функция IMPORTRANGE перетащит выбранные нами диапазоны ячеек из каждой таблицы-источника на лист «Объединенная таблица». Происходит это при помощи «ключей» — уникальных наборов символов в ссылке на таблицу-источник.

Каждый «ключ» располагается в URL-адресе между двумя слешами: первый — после буквы «d», второй стоит последним в адресе ссылки. На примере случайной ссылки это будет выглядеть так (ключ зачеркнут красным).

https://drive.google.com/file/d/ 1wYt815zLgSPhfh5FJbQgttjueoLVMr3H /view

В функции IMPORTRANGE указываем нужный лист из файла-источника: с него и будем тянуть необходимые диапазоны ячеек в «Объединенную таблицу».

Название вкладки (у нас это «Лист1») в формуле IMPORTRANGE указываем в кавычках, а в конце ставим восклицательный знак. Получается следующее:

IMPORTRANGE(«1vB6WfSHNAqqAjR_ingLxPnChoZfEfmAfrCrb9wOzFHk»;«Лист1!A1:N«)

3. Фигурные скобки <. ; … ; . >— это оператор консолидации, который удерживает перетаскиваемые таблицы вместе. Он также участвует в формуле.

QUERY(
IMPORTRANGE(«1vB6WfSHNAqqAjR_ingLxPnChoZfEfmAfrCrb9wOzFHk»;»Лист1!A1:N»);
IMPORTRANGE(«1Viy0xCOcVLjiVFlNqcq0Lo1zpXTtRgH6_cVkL83hNn0″;»Лист1!A2:N»)>

4. Запрос SQL — это наборы команд для работы с табличными базами данных.

Сортировку проводим по столбцу 10 — в нем указаны даты оплат.

Вот каким будет процесс объединения источников.

1. На листе «Объединенная таблица» в 1-ой ячейке первого столбца пишем нашу формулу. Для удобства мы разделили ее, чтобы было лучше видно каждую функцию, но всю формулу нужно вписать в одну ячейку.

=QUERY( <
IMPORTRANGE( «1vB6WfSHNAqqAjR_ingLxPnChoZfEfmAfrCrb9wOzFHk» ; «Лист1!A1:N» );
IMPORTRANGE( «1Viy0xCOcVLjiVFlNqcq0Lo1zpXTtRgH6_cVkL83hNn0» ; «Лист1!A2:N» )>;
«select * where Col1 is not null order by Col10» )

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

Создаем сводную таблицу и делаем выборки данных

Дашборд google sheets, как и любой другой, нуждается в показателях.

На листе «Объединенная таблица» ставим курсор на свободную ячейку. Переходим в «Вставка» → «Создать сводную таблицу».

Проверяем «Диапазон данных»: как и ранее, указываем первый столбец и его 1-ую строку (без заголовка) и последний столбец (без указания последней строки). Выбираем «Новый лист» — «Создать». Назовем его «Сводная таблица 2» — сюда соберем показатели для первого визуального элемента.

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

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

Теперь в следующей ячейке записываем через знак «=» обычную формулу деления. И получаем показатель выполнения плана — остается только подписать полученный результат.

Подобным образом формируем показатель «Конверсия»: число сделок со статусом «Закрыто» делим на общее количество.

Дальше копируем первую сводную таблицу, ставим в свободное место, удаляем в ней старые настройки, выбираем «Значения» → «Продажи факт, тыс.руб.». В поле «Суммировать по» вместо «SUM» кликаем по «COUNT» в списке — это сложит в столбце количество ячеек, которые будут указаны в фильтре. В «Фильтре» отмечаем «Закрыто» и нажимаем «ОК».

Точно так же считаем общее количество сделок и делением получаем конверсию.

Добавляем карточки KPI

В подготовленный заранее макет вставляем данные для карточек KPI:

• прибыль продаж;
• выполнение плана;
• количество сделок;
• конверсия сделок.

Для этого двойным щелчком по нужной плитке открываем справа «Редактор диаграмм»:

• в поле «Тип диаграммы» выбираем столбчатую → «Сводка»;
• указываем ячейку со значением для первой карточки KPI;
• отмечаем адрес ячейки (Сводная таблица 2’!A2) и нажимаем «ОК».

Показатель появился на нужной плитке KPI. Для редактирования единиц измерения встаем на соответствующую ячейку, переходим в меню «Формат» → «Числа» и выбираем, например, российский рубль.

Для дополнительных настроек визуалов дважды щелкаем по нужной плитке — справа появляется поле «Редактор диаграмм», в котором доступны «Настройки» и «Дополнительные».

Так будет выглядеть лист с готовыми карточками KPI.

интерактив на базе сводных таблиц в Excel

грамотная визуализация любых данных

наглядные отчеты для вашего руководства

Интерактивные отчеты, которые понравятся директору

Дашборд в Excel с нуля
ОНЛАЙН-КУРС

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

Для построения дашборда в Гугл Таблицах доступны разные виды визуализаций. Мы будем использовать столбчатые и линейчатые для показателей динамики продаж, а также статистики по менеджерам и каналам — это хорошо отразит категории и сделает наглядным сравнение.

На листе «Сводная таблица 2» копируем ячейки с данными первой карточки KPI и вставляем в любое свободное место на листе. Выбираем нужную ячейку в скопированной сводной таблице. В появившемся справа редакторе удаляем значения предыдущего визуального элемента.

Выбираем для будущего графика «Динамика продаж» строки для оси X и параметр «Значения» для Y. Для корректного вывода дат по оси X сгруппируем их по году и месяцу.

«Итог» в полученной сводной таблице просто удаляем, на визуализации он нам не нужен. Все эти действия проиллюстрированы ниже — просто листайте картинки.

Переходим на лист «Дашборд», двойным щелчком выделяем нужный элемент на макете. В «Редакторе диаграмм» выбираем тип визуализации, указываем адреса ячеек нашей сводной таблицы с соответствующего листа. Выбираем значение для оси X и параметр для оси Y. Получаем диаграмму динамики продаж.

Настраиваем диаграммы

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

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

Аналогично собираем и настраиваем оставшиеся плитки о продажах:

• по менеджерам;
• по регионам;
• по каналам.

Конечно, конкретные настройки зависят от содержания вашего дашборда — мы рассказываем об оформлении на примере нашей панели.

Как настроить обновление данных

Данные в dashboard google sheets обновляются автоматически при добавлении новой информации в любую из подключенных таблиц. Покажем на примере, как это происходит.

В «Таблицу-источник 1» добавим еще одну строку. Мы ее просто скопируем, только изменим «Дату сделки», так как фильтр в формуле «QUERY» у нас написан по дате. Новая информация сразу же появится в файле с дашбордом: и на листе «Объединенная таблица», куда и подтягиваются данные из источников, и на визуализациях.

Чего избегать при разработке дашборда в Гугл Таблицах

Ошибка;Решение

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

Какие еще сервисы можно использовать для наглядных отчетов

Конечно, дашборд google таблицы — не единственный (и не самый лучший) инструмент для разработки информационной аналитической панели. Вариантов существует множество: они различаются и по своей функциональности, и по назначению, и по стоимости использования.

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

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

● Microsoft Power BI — самая известная система бизнес-аналитики, много лет входит в число лучших в мире. Считается наиболее адаптированной для работы с большими массивами данных.

● Tableau — еще одна популярная BI-платформа с широкими возможностями кастомных визуализаций. Интерфейс интуитивно понятен, разобраться с построением дашборда будет несложно. Это платный инструмент.