Магия таблиц. 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).

Для версий Excel 2010 и 2013 ее можно скачать бесплатно на сайте Microsoft (https://www.microsoft.com/ru-RU/download/details.aspx?id=39379) в качестве отдельной надстройки (она будет отображаться как отдельная вкладка на ленте инструментов с названием Power Query, а не внутри вкладки «Данные», как в новых версиях).

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

Импорт данных с помощью Power Query

Файлы с примерами:

Для начала импорта необходимо нажать на «Получить данные» (Get Data) и выбрать необходимый тип источника в одном из списков.

Импорт данных из интернета

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

Данные → Получить данные → Из других источников → Из интернета

(Data → Get Data → From Other Sources → From Web).

В случае отдельной вкладки с надстройкой Power Query на ленте (в старых версиях):

Power Query → Из других источников → Из интернета

(Power Query → From Other Sources → From Web).

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

После нажатия на кнопку «ОК» появится диалоговое окно «Навигатор» (Navigator). Слева в нем будет список источников данных (таблиц) на странице. Методом перебора можно найти необходимую — содержимое таблицы будет отображаться справа, в окне предварительного просмотра.

После этого у нас есть несколько возможных действий.

• «Загрузить» (Load). Данные загрузятся на новый лист Excel в виде таблицы без изменений.

• «Загрузить в» (Load To) — кнопка справа от «Загрузить». Откроется новое окно, в котором можно будет выбрать не таблицу, а сводную таблицу, — тогда данные не будут отображаться на листе Excel как есть, а будет построена сводная таблица на их основе. Если в этом окне выбрать «Добавить в модель данных», то данные сразу попадут в Power Pivot (модель данных) как один из источников.

• «Преобразовать данные» (Transform Data). Откроется редактор Power Query, в котором можно будет изменить данные — убрать некоторые столбцы, изменить значения, отсортировать или отфильтровать их и проделать многие другие действия.

Импорт папки с файлами