Как таблицы экономят компаниям сотни рабочих часов

Табличными  редакторами и офисными приложениями пользуются практически в каждой компании/государственной структуре/некоммерческой организации, независимо от масштаба и сферы деятельности (например, шеф-редактор в издательстве, управляющий портфелем книг — казалось бы, очень творческая работа; но без таблиц в ней вообще никуда). Как настроить таблицы так, чтобы работа с ними была не в тягость, а, наоборот, экономила время? Рассказывает Ренат Шагабутдинов.

Даже если в вашей компании установлена не самая последняя версия Excel, а та, которую купили когда-то (например, это Excel 2013 или Excel 2016) — есть много возможностей для оптимизации и ускорения работы, о которых многие пользователи просто не знают и продолжают работать в ручном режиме, тратя часы и дни на создание отчетов, которые можно готовить за 10-20 минут. Многие задачи можно решать формулами и обходиться без программирования, и хотя в Microsoft 365 и Google Таблицах появляются чудесные новые функции, позволяющие решать сложные задачи быстро, почти всегда можно найти аналоги и для более старых версий.

Главное правило: если в табличном редакторе, будь то Excel или Google Таблицы или рсосийский Р7-Офис что-то делается долго, то почти наверняка это делается неправильно. Хороший вопрос для самопроверки: если данных будет в 10, 100 раз больше (по числу строк или значений, например), сработает ли мой способ, буду ли я тратить столько же времени или в 10 раз больше? Если скорость работы зависит от объема данных, похоже, решение неоптимальное.

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

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

Собираем данные с десятков датчиков мониторинга качества воздуха в один отчет

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

В итоге получаются десятки таблиц с тысячами строк в каждой — анализировать это вручную почти невозможно. А если нужен один сводный отчет? Ранее сотрудникам приходилось вручную собирать все таблицы в один источник данных (копируя и вставляя), чтобы потом построить сводную таблицу.

Что мы сделали? Воспользовались надстройкой Power Query — которая уже довольно давно не надстройка, а встроенный функционал Excel — начиная с версии Excel 2016. А для версий 2010-2013 ее можно установить бесплатно.

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

Создаем пустой запрос в Power Query:

• Данные — Получить данные — Из других источников — Пустой запрос.
• Data — Get Data — From Other Sources — Blank Query.

Откроется редактор Power Query, в котором и будет создан этот самый пустой запросы. Введем функцию Excel.CurrentWorkbook(). Как и в Excel, можно начать вводить название функции и потом щелкнуть на нее в списке, чтобы название было введено до конца автоматически.

Эта функция позволяет получить все объекты текущей книги Excel. Нас интересуют все таблицы — «Table» — можно выбрать все или только нужные имена в фильтре и затем развернуть содержимое всех таблиц (Content).

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

Останется только убрать ненужные столбцы, если они есть, произвести другие манипуляции с данными (возможностей для этого в Power Query очень много) и загрузить это в виде сводной таблицы Excel (привычного большинству пользователей инструмента).

• Закрыть и загрузить — Закрыть и загрузить в… — Отчет сводной таблицы.
• Close & Load — Close & Load To… — PivotTable Report.

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

Собираем сотню Excel-файлов с выгрузками/отчетами в один файл или сводную таблицу

Схожая ситуация, только таблицы не на отдельных листах в рамках одной книги Excel, а в десятках или сотнях отдельных файлов (книг Excel) внутри одной папки.

И здесь нам поможет Power Query.

В папке лежит сотня книг Excel с однотипными данными. Нам нужно собрать все в один отчет. 

Создаем пустую рабочую книгу Excel и в ней — запрос к папке. Это можно сделать прямо на ленте инструментов Excel:

• Данные — Получить данные — Из файла — Из папки.
• Data — Get Data — From File — From Folder.

И выбрать в появившемся окне нужную папку.

Будет отображен список всех файлов, которые лежат в этой папке и ее подпапках. Можно попробовать сразу их объединить, нажав одноименную кнопку (Combine), но лучше сначала зайти в редактор Power Query, нажав «Преобразовать данные» (Transform Data).

После чего откроется список всех файлов в Power Query — тут можно отфильтровать его, выбрав не все файлы, а только некоторые, или убрав файлы ненужных расширений (это столбец Extension) — на случай если, допустим, в папке будут встречаться и презентации Power Point (.pptx).

После этого можно щелкать по кнопке Content  и разворачивать данные:

После чего будет предложено выбрать (1 на скриншоте) на примере одного из файлов (по умолчанию — первого; но можно выбрать любой — впрочем, файлы в такой ситуации обычно однотипные и это не будет иметь значения, можно остановиться на первом), какие данные (лист, таблицу) будем (2 на скриншоте) из каждого файла объединять (в нашем примере везде один лист с данными, его и выберем.:

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

Здесь алгоритм такой же, как и в прошлом примере: 

• Закрыть и загрузить — Закрыть и загрузить в… — Отчет сводной таблицы.
• Close & Load — Close & Load To… — PivotTable Report.

И готово! Мы получим сводную таблицу, в которой источником выступает папка с сотней (десятком или тысячей — не важно) файлов (книг Excel)! А если туда добавятся новые файлы? Достаточно будет обновить сводную таблицу (Alt + F5) — и новые данные тут же в нее попадут.

Исправляем выгрузку неудобного формата

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

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

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

Как получить такой результат автоматически? Можно отправить выгрузку в Power Query и преобразовать там. Но на самом деле проблему можно решить одними формулами и функциями, которые есть в старых версиях Excel — даже 2007, там, где нет Power Query. Соответственно, аналогично можно поступить и в Google Таблицах, и в Р7. 

Какие варианты в такой ситуации возможны

1. Смотреть на первое слово / символы в ячейке. Например, проверять, что первые 6 символов (ЛЕВСИМВ / LEFT) — это «филиал».

=ЕСЛИ(ЛЕВСИМВ(A2;6)=«филиал»; A2; значение сверху).

2. А если не везде начало со слова «филиал», как в реальной жизни? Там было то «Филиал», то «Центр», то сразу город.

Тогда можно смотреть на следующую строку — если там подзаголовок другого уровня, значит, в текущей строке заголовок по городу. Это решение на скриншоте. 

3. Можно смотреть на какой-то из соседних столбцов (B, C и так далее), но не конкретно в этом случае, т.к. здесь эти столбцы пустые и в строках с городами, и в строках с категориями услуг (Основные, дополнительные). Но в каких-то случаях это подойдет, можно будет проверять столбец на пустоту функцией ЕПУСТО / ISBLANK.

Так или иначе, практически в любой ситуации можно найти решение и формулой привести данные к нужному виду, который позволит построить отчет через сводные таблицы или другие формулы, а не искать каждый город и услугу через поиск (Ctrl + F — ввели город — нашли услугу; это ручная работа).

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