Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

В появившемся окне задаем имя элемента и формулу. Для написания формулы можно выбирать элементы из списка внизу и щелкать по ним дважды либо нажимать «Добавить элемент». Конечно, можно вводить названия элементов и вручную, но смысла в этом нет (обратите внимание, что названия с пробелами/дефисами нужно брать в апострофы — это происходит автоматически при добавлении поля из списка).

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

Обратите внимание на строку «Общий итог». Там суммируются три канала продаж и добавленный нами вычисляемый элемент тоже. Получается задвоение. Можно убрать промежуточные значения фильтром (если вам не нужно смотреть на составляющие розничных продаж отдельно).

Либо отключить отображение общих итогов на вкладке «Конструктор», если вы хотите видеть и вычисляемый объект, и те значения, что в него входят, но не видеть общих задвоенных итогов.

В вычисляемых объектах можно использовать не только математические операторы, но и функции, — например, рассчитать среднее с помощью СРЗНАЧ / AVERAGE. Но ссылаться на ячейки и диапазоны не получится, увы.

Power Query и Power Pivot: несколько слов

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

Кратко про обе надстройки

Power Query. Загружаем данные из внешних источников (баз данных, корпоративных систем, облачных хранилищ, книг Excel, сайтов и Google Таблиц, папок с несколькими файлами и других) с последующей обработкой (очищением, заменой, изменением структуры, фильтрацией, сортировкой и т. д.) и загрузкой в рабочую книгу Excel (в виде сводной или Таблицы) или в модель данных (то есть в Power Pivot) для связи с другими данными и — опять же — последующего анализа в виде сводной.

Power Pivot. Устанавливаем связи между разными источниками данных (загруженными с помощью самой Power Pivot или Power Query), в том числе превышающими по объему миллион строк (максимальный в рабочих листах Excel) для последующего анализа в виде сводной. У каждой книги Excel есть одна модель данных (если версия Excel поддерживает Power Pivot), в которую можно загрузить данные, связать несколько источников (не используя много функций ВПР и подобных функций поиска) и загрузить в сводную таблицу в книге Excel. Power Pivot позволяет обрабатывать десятки и сотни миллионов (не тысяч!) строк и делать это быстро.

^{Microsoft: Power Pivot https://mif.to/rfIJH}

Сценарии применения надстроек

1. Только Power Query: импортируем извне и преобразуем данные — загружаем в таблицу или сводную таблицу Excel

Загрузили из внешнего источника (например, с сайта или из папки с файлами) данные с помощью Power Query, опционально — обработали (например, удалили некоторые столбцы или поменяли формат данных) и выгрузили в Excel как таблицу или сводную таблицу.

2. Только Power Pivot: импортируем данные из тех источников, что поддерживает Power Pivot (базы данных, Excel, текстовые файлы), связываем в модели данных, загружаем в сводную или 3D-карту.

Загрузили с помощью Power Pivot данные из нескольких источников, связали их в модели данных, выгрузили в сводную таблицу и/или 3D-карту (если в данных есть географические метки, например города).

3. Обе надстройки. Импортируем данные с помощью Power Pivot и/или с помощью Power Query (в Power Query также преобразуем при необходимости).

Загрузили с помощью Power Pivot данные из нескольких источников, загрузили и — опционально — обработали данные из других источников (импорт которых не поддерживается в Power Pivot) с помощью Power Query, связали их в модели данных, выгрузили в сводную таблицу и/или 3D-карту.

Power Query

Power Query встроена в Excel 2016, 2019, Excel в Microsoft 365 (для Mac — только в Microsoft 365) и находится прямо на ленте инструментов на вкладке «Данные» (Data) — в группе «Получить и преобразовать» (Get & Transform Data).