Либо отключить отображение общих итогов на вкладке «Конструктор», если вы хотите видеть и вычисляемый объект, и те значения, что в него входят, но не видеть общих задвоенных итогов.
В вычисляемых объектах можно использовать не только математические операторы, но и функции, — например, рассчитать среднее с помощью СРЗНАЧ / 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.
Файлы с примерами:
2 Загружаем данные из внешних источников.xlsx
Продажи. txt
Для начала импорта необходимо нажать на «Получить данные» (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, в котором можно будет изменить данные — убрать некоторые столбцы, изменить значения, отсортировать или отфильтровать их и проделать многие другие действия.
Импорт папки с файлами
Пример импорта данных из другого источника — папки с несколькими файлами, например несколькими книгами Excel.
Файлы с примерами:
• Сводная из папки.xlsx
• Папка «Филиалы»
Если данные хранятся в разных книгах Excel, их не обязательно предварительно собирать вручную — можно воспользоваться надстройкой Power Query.
В появившемся окне выберите папку с файлами для загрузки.
Таблицы в файлах должны быть с одинаковой структурой (по столбцам), хотя число строк может отличаться. Конечно, данные должны подходить для создания сводной, ведь наша конечная цель именно в этом!
Далее нужно выбрать элементы, из которых будут загружаться данные. Power Query видит таблицы и листы, они помечены своими иконками (важно, чтобы в разных книгах при загрузке целой папки они назывались одинаково).
Если вам нужно сначала посмотреть список всех объектов в файлах, а не выбирать сразу листы или таблицы, то нажмите на «Параметр1» на этом шаге
После выбора объекта (таблицы или листа) откроется окно редактора Power Query. В нем можно преобразовать данные, почистить их, отфильтровать, поменять форматы — все, что вы сделаете здесь, будет каждый раз происходить с данными при их обновлении.
Чтобы перенести данные отсюда на лист Excel в виде таблицы или сводной, выбирайте «Закрыть и загрузить в» (Close & Load To…) — далее можно будет выбрать, в каком виде данные можно загрузить
После чего будет построена обычная сводная, но источником для нее будет выступать подключение к четырем файлам.
При обновлении сводной будет обновляться подключение: будут загружены данные из всех файлов-источников и над ними будут проделаны те операции, которые записаны в редакторе Power Query (например, замена какого-то текста на другой, изменение форматов, фильтрация данных и так далее).
Power Query позволяет не только загружать данные из внешних источников, но и преобразовывать их, подготавливая к дальнейшему анализу с помощью сводных таблиц Excel или использованию как одного из источников в модели данных Power Pivot.
Если при импорте вы выберете «Преобразовать» (Transform Data), а не «Загрузить» (Load), откроется отдельное окно редактора Power Query, в котором можно производить манипуляции с данными.
Вот так выглядит окно Power Query:
Манипуляции с данными можно осуществлять как через ленту, так и с помощью контекстного меню.
В пункте «Преобразование» есть ряд операций, позволяющих изменять числа или текст.
Многие преобразования имеют аналоги в Excel — в виде инструментов («Найти и заменить» в Excel, «Замена значений» в Power Query) или функций (как ПРОПИСН / UPPER — здесь ВЕРХНИЙ РЕГИСТР). Но преимущество здесь и в быстродействии, и в том, что все преобразования будут осуществляться каждый раз при обновлении связи с источником. Не нужно вводить функции, делать что-то вручную, эти действия будут происходить автоматически.
Что можно делать с помощью Power Query с импортируемыми данными:
• удалять столбцы;
• переименовывать столбцы;
• заполнять столбцы по образцу (по аналогии с мгновенным заполнением Excel);
• сортировать и фильтровать данные (например, убирать пустые строки);
• изменять регистр текстовых значений;
• заменять один текст/символ на другой;
• очищать данные от лишних пробелов;
• округлять числа и производить с ними другие операции;
• группировать данные по значениям из какого-то столбца (по аналогии со сводными таблицами);