Excel power query счетесли

Обновлено: 07.01.2025

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

Конструирование таблиц

Функция	Описание
ItemExpression.From	Возвращает AST для тела функции.
ItemExpression.Item	Узел AST, представляющий элемент в выражении элемента.
RowExpression.Column	Возвращает AST, представляющий доступ к столбцу в выражении строки.
RowExpression.From	Возвращает AST для тела функции.
RowExpression.Row	Узел AST, представляющий строку в выражении строки.
Table.FromColumns	Возвращает таблицу из списка, содержащего вложенные списки с именами и значениями столбцов.
Table.FromList	Преобразует список в таблицу путем применения заданной функции разбиения к каждому элементу в списке.
Table.FromRecords	Возвращает таблицу из списка записей.
Table.FromRows	Создает таблицу из списка, в которой каждый элемент списка является списком, содержащим значения столбца для одной строки.
Table.FromValue	Возвращает таблицу со столбцом, содержащим указанное значение или список значений.
Table.FuzzyGroup	На основе нечеткого соответствия группирует строки таблицы со значениями строк указанного столбца.
Table.FuzzyJoin	Соединяет строки из двух таблиц, обладающие нечетким соответствием по заданным ключам.
Table.FuzzyNestedJoin	Выполняет нечеткое соединение таблиц по указанным столбцам и выводит результат соединения в новом столбце.
Table.Split	Разделяет указанную таблицу на список таблиц с заданным размером страницы.
Table.View	Создает или расширяет таблицу, используя определенные пользователем обработчики операций запросов и действий.
Table.ViewFunction	Создает функцию, которая может быть перехвачена обработчиком, определенным в представлении (через Table.View ).

Преобразования

Функция	Описание
Table.ToColumns	Возвращает список вложенных списков, каждый из которых представляет столбец значений во входной таблице.
Table.ToList	Возвращает таблицу в список путем применения заданной функции объединения к каждой строке значений в таблице.
Table.ToRecords	Возвращает список записей из входной таблицы.
Table.ToRows	Возвращает вложенный список значений из входной таблицы.

Сведения

Функция	Описание
Table.ApproximateRowCount	Возвращает приблизительное количество строк в таблице.
Table.ColumnCount	Возвращает количество столбцов в таблице.
Table.IsEmpty	Возвращает значение true, если таблица не содержит строк.
Table.Profile	Возвращает профиль столбцов таблицы.
Table.RowCount	Возвращает количество строк в таблице.
Table.Schema	Возвращает таблицу, содержащую описание столбцов (например, схему) указанной таблицы.
Tables.GetRelationships	Возвращает связи между набором таблиц.

Операции со строками

Операции со столбцами

Функция	Описание
Table.Column	Возвращает значения столбца в таблице.
Table.ColumnNames	Возвращает имя столбцов в таблице.
Table.ColumnsOfType	Возвращает список с именами столбцов, соответствующих указанным типам.
Table.DemoteHeaders	Понижает уровень строки заголовка до первой строки таблицы.
Table.DuplicateColumn	Дублирует столбец с указанным именем. Значения и тип копируются из исходного столбца.
Table.HasColumns	Возвращает значение true, если таблица содержит указанный столбец или столбцы.
Table.Pivot	При наличии таблицы и столбца атрибутов, содержащих pivotValues, создает новые столбцы для каждого из значений сведения, и назначает им значения из valueColumn. Можно предоставить необязательную функцию aggregationFunction для обработки нескольких вхождений одного и того же значения ключа в столбце атрибутов.
Table.PrefixColumns	Возвращает таблицу, где для всех столбцов задано текстовое значение в качестве префикса.
Table.PromoteHeaders	Продвигает первую строку таблицы в заголовок или имена столбцов.
Table.RemoveColumns	Возвращает таблицу, не имеющую определенного столбца или столбцов.
Table.ReorderColumns	Возвращает таблицу с конкретными столбцами в заданном порядке относительно друг друга.
Table.RenameColumns	Возвращает таблицу со столбцами, переименованными, как указано.
Table.SelectColumns	Возвращает таблицу, содержащую только определенные столбцы.
Table.TransformColumnNames	Преобразует имена столбцов с помощью предоставленной функции.
Table.Unpivot	При наличии списка столбцов таблицы преобразует эти столбцы в пары "атрибут-значение".
Table.UnpivotOtherColumns	Преобразует все столбцы, отличные от заданного набора, в пары "атрибут-значение", объединенные с остальными значениями в каждой строке.

Параметры

Преобразование

Параметры для параметров группы

Параметры для типов соединения

Алгоритм соединения

Следующие значения JoinAlgorithm можно указать для Table.Join

Значения параметров	Описание
JoinSide.Left	Указывает левую таблицу соединения.
JoinSide.Right	Указывает правую таблицу соединения.

Пример данных

В примерах этого раздела используются следующие таблицы.

Таблица Customers

Таблица Orders

Членство

Параметры для проверок членства

Спецификация вхождения

Компонент	Описание
Table.Contains	Определяет, появляется ли запись в виде строки в таблице.
Table.ContainsAll	Определяет, появляются ли все указанные записи в виде строк в таблице.
Table.ContainsAny	Определяет, появляются ли какие-либо указанные записи в виде строк в таблице.
Table.Distinct	Удаляет повторяющиеся строки из таблицы, обеспечивая уникальность всех оставшихся строк.
Table.IsDistinct	Определяет, содержит ли таблица только уникальные строки.
Table.PositionOf	Определяет позицию или позиции строки в таблице.
Table.PositionOfAny	Определяет позицию или позиции любой из указанных строк в таблице.
Table.RemoveMatchingRows	Удаляет все вхождения строк из таблицы.
Table.ReplaceMatchingRows	Заменяет определенные строки из таблицы новыми строками.

Упорядочение

Демонстрационные данные

В примерах этого раздела используются следующие таблицы.

Таблица Employees

Компонент	Описание
Table.Max	Возвращает самую большую строку или строки из таблицы с помощью comparisonCriteria.
Table.MaxN	Возвращает из таблицы количество N самых длинных строк. После сортировки строк необходимо указать параметр countOrCondition для дальнейшей фильтрации результатов.
Table.Min	Возвращает наименьшую строку или строки из таблицы с помощью comparisonCriteria.
Table.MinN	Возвращает количество N наименьших строк в заданной таблице. После сортировки строк необходимо указать параметр countOrCondition для дальнейшей фильтрации результатов.
Table.Sort	Сортирует строки в таблице с помощью comparisonCriteria или порядка по умолчанию, если он не указан.

Другое

Компонент	Описание
Table.Buffer	Помещает таблицу в буфер памяти, изолируя ее от внешних изменений во время оценки.

Значения параметров

Именование выходных столбцов

Этот параметр представляет собой список текстовых значений, указывающих имена столбцов результирующей таблицы. Этот параметр обычно используется в функциях построения таблиц, таких как Table.FromRows и Table.FromList.

Критерии сравнения

Критерий сравнения может быть предоставлен как одно из следующих значений:

Числовое значение для указания порядка сортировки. См. раздел о порядке сортировки в разделе о значениях параметров.

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

Чтобы выбрать ключ и порядок, критерием сравнения может быть список, содержащий ключ и порядок.

Примеры см. в описании Table.Sort.

Критерий количества или условия

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

Число указывает, сколько значений следует вернуть, а также соответствующую функцию.

Если указано условие, возвращается строка, содержащая значения, которые изначально соответствуют условию. Если значение не соответствует условию, дальнейшие значения не учитываются.

Обработка дополнительных значений

Используется для указания того, как функция должна управлять дополнительными значениями в строке. Этот параметр указывается в виде числа, которое сопоставляется с параметрами ниже.

Дополнительные сведения см. в разделе Table.FromList.

Обработка отсутствующих столбцов

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

Используется в преобразовании или операциях со столбцами. Примеры см. в разделе Table.TransformColumns.

Порядок сортировки

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

Критерии равенства

Критерий равенства для таблиц можно указать как:

значение функции, которое является:

селектором ключа, определяющим столбец в таблице для применения условий равенства;

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

Термины "Power Query", "Power Pivot", "Power BI" и прочие "пауэры" все чаще всплывают в статьях и материалах о Microsoft Excel. По моему опыту, далеко не все ясно представляют себе что скрывается за этими понятиями, как они между собой взаимосвязаны и как могут помочь простому пользователю Excel.

Давайте проясним ситуацию.

Power Query

Еще в 2013 году специально созданная группа разработчиков внутри Microsoft выпустила для Excel бесплатную надстройку Power Query (другие названия - Data Explorer, Get&Transform), которая умеет массу полезных для повседневной работы вещей:

Загружать данные в Excel из почти 40 различных источников, среди которых базы данных (SQL, Oracle, Access, Teradata. ), корпоративные ERP-системы (SAP, Microsoft Dynamics, 1C. ), интернет-сервисы (Facebook, Google Analytics, почти любые сайты).
Собирать данные из файлов всех основных типов данных (XLSX, TXT, CSV, JSON, HTML, XML. ), как поодиночке, так и сразу оптом - из всех файлов указанной папки. Из книг Excel можно автоматически загружать данные сразу со всех листов.
Зачищать полученные данные от "мусора": лишних столбцов или строк, повторов, служебной информации в "шапке", лишних пробелов или непечатаемых символов и т.п.
Приводить данные в порядок: исправлять регистр, числа-как-текст, заполнять пробелы, добавлять правильную "шапку" таблицы, разбирать "слипшийся" текст на столбцы и склеивать обратно, делить дату на составляющие и т.д.
Всячески трансформировать таблицы, приводя их в желаемый вид (фильтровать, сортировать, менять порядок столбцов, транспонировать, добавлять итоги, разворачивать кросс-таблицы в плоские и сворачивать обратно).
Подставлять данные из одной таблицы в другую по совпадению одного или нескольких параметров, т.е. прекрасно заменяет функцию ВПР (VLOOKUP) и ее аналоги.

Power Query встречается в двух вариантах: как отдельная надстройка для Excel 2010-2013, которую можно скачать с официального сайта Microsoft и как часть Excel 2016. В первом случае после установки в Excel появляется отдельная вкладка:

В Excel 2016 весь функционал Power Query уже встроен по умолчанию и находится на вкладке Данные (Data) в виде группы Получить и преобразовать (Get & Transform) :

Возможности этих вариантов совершенно идентичны.

Принципиальной особоенностью Power Query является то, что все действия по импорту и трансформации данных запоминаются в виде запроса - последовательности шагов на внутреннем языке программирования Power Query, который лаконично называется "М". Шаги можно всегда отредактировать и воспроизвести повторно любое количество раз (обновить запрос).

Основное окно Power Query обычно выглядит примерно так:

По моему мнению, это самая полезная для широкого круга пользователей надстройка из всех перечисленных в этой статье. Очень много задач, для которых раньше приходилось либо жутко извращаться с формулами, либо писать макросы - теперь легко и красиво делаются в Power Query. Да еще и с последующим автоматическим обновлением результатов. А учитывая бесплатность, по соотношению "цена-качество" Power Query просто вне конкуренции и абсолютный must have для любого средне-продвинутого пользователя Excel в наши дни.

Power Pivot

Power Pivot - это тоже надстройка для Microsoft Excel, но предназначенная немного для других задач. Если Power Query сосредоточена на импорте и обработке, то Power Pivot нужен, в основном, для сложного анализа больших объемов данных. В первом приближении, можно думать о Power Pivot как о прокачанных сводных таблицах.

Общие принципы работы в Power Pivot следующие:

Сначала мы загружаем данные в Power Pivot - поддерживается 15 различных источников: распространенные БД (SQL, Oracle, Access. ), файлы Excel, текстовые файлы, веб-каналы данных. Кроме того, можно использовать Power Query как источник данных, что делает анализ почти всеядным.
Затем между загруженными таблицами настраиваются связи или, как еще говорят, создается Модель Данных. Это позволит в будущем строить отчеты по любым полям из имеющихся таблиц так, будто это одна таблица. И никаких ВПР опять же.
При необходимости, в Модель Данных добавляют дополнительные вычисления с помощью вычисляемых столбцов (аналог столбца с формулами в "умной таблице") и мер (аналог вычисляемого поля в сводной). Всё это пишется на специальном внутреннем языке Power Pivot, который называется DAX (Data Analysis eXpressions).
На листе Excel по Модели Данных строятся интересующие нас отчеты в виде сводных таблиц и диаграмм.

Главное окно Power Pivot выглядит примерно так:

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

У Power Pivot есть ряд особенностей, делающих её уникальным инструментом для некоторых задач:

В Power Pivot нет предела по количеству строк (как в Excel). Можно грузить таблицы любого размера и спокойно работать с ними.
Power Pivot очень хорошо умеет сжимать данные при загрузке их в Модель. 50 Мб исходный текстовый файл может легко превратиться в 3-5 Мб после загрузки.
Поскольку "под капотом" у Power Pivot, по сути, полноценный движок базы данных, то с большими объемами информации он справляется очень быстро. Нужно проанализировать 10-15 млн. записей и построить сводную? И все это на стареньком ноутбуке? Без проблем!

К сожалению, пока что Power Pivot входит не во все версии Excel. Если у вас Excel 2010, то скачать её можно бесплатно с сайта Microsoft. А вот если у вас Excel 2013-2016, то всё зависит от вашей лицензии, т.к. в некоторых вариантах она включена (Office Pro Plus, например), а в некоторых нет (Office 365 Home, Office 365 Personal и т.д.) Подробнее об этом можно почитать тут.

Power Maps

Эта надстройка впервые появилась в 2013 году и первоначально называлась GeoFlow. Она предназначена для визуализации гео-данных, т.е. числовой информации на географических картах. Исходные данные для отображения берутся все из той же Модели Данных Power Pivot (см. предыдущий пункт).

Демо-версию Power Map (почти не отличающуюся от полной по возможностям, кстати) можно совершенно бесплатно загрузить опять же с сайта Microsoft. Полная же версия включена в некоторые пакеты Microsoft Office 2013-2016 вместе с Power Pivot - в виде кнопки 3D-карта на вкладке Вставка (Insert - 3D-map) :

Ключевые особенности Power Map:

Карты могут быть как плоскими, так и объемными (земной шар).
Можно использовать несколько разных типов визуализации (гистограммы, пузырьковые диаграммы, тепловые карты, заливку областями).
Можно добавлять измерение времени, т.е. анимировать процесс и смотреть на него в развитии.
Карты подгружаются из сервиса Bing Maps, т.е. для просмотра нужен весьма шустрый доступ в интернет. Иногда возникают сложности с правильным распознаванием адресов, т.к. названия в данных не всегда совпадают с Bing Maps.
В полной (не демо) версии Power Map можно использовать собственные загружаемые карты, например визуализировать посетителей торгового центра или цены на квартиры в жилом доме прямо на строительном плане.
На основе созданных гео-визуализаций можно прямо в Power Map создавать видеоролики (пример), чтобы поделиться ими потом с теми, у кого надстройка не установлена или включить в презентацию Power Point.

Power View

Эта надстройка появилась впервые в составе Excel 2013 и предназначена для "оживления" ваших данных - построения интерактивных графиков, диаграмм, карт и таблиц. Иногда для этого используют термины дашборд (dashboard) или панель показателей (scorecard) . Суть в том, что вы можете вставить в ваш файл Excel специальный лист без ячеек - слайд Power View, куда добавить текст, картинки и массу различного типа визуализаций по вашим данным из Модели Данных Power Pivot.

Выглядеть это будет примерно так:

Нюансы тут такие:

Исходные данные берутся всё оттуда же - из Модели Данных Power Pivot.
Для работы с Power View необходимо установить на вашем компьютере Silverlight - майкрософтовский аналог Flash (бесплатный).

На сайте Microsoft, кстати, есть весьма приличный обучающий курс по Power View на русском языке.

Power BI

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

В Power BI Desktop можно:

Загружать данные из более чем 70 различных источников (как в Power Query + дополнительные коннекторы).
Связывать таблицы в модель (как в Power Pivot)
Добавлять к данным дополнительные вычисления с помощью мер и вычисляемых столбцов на DAX (как в Power Pivot)
Создавать на основе данных красивейшие интерактивные отчеты с разного типа визуализациями (очень похоже на Power View, но еще лучше и мощнее).
Публиковать созданные отчеты на сайте Power BI Service (см. следующий пункт) и делиться ими с коллегами. Причем есть возможность давать разные права (чтение, редактирование) разным людям.

2. Онлайн-сервис Power BI - упрощенно говоря, это сайт, где у вас и у каждого пользователя в вашей компании будет своя "песочница" (workspace) куда можно загружать созданные в Power BI Desktop отчеты. Помимо просмотра, позволяет их даже редактировать, воспроизводя онлайн почти весь функционал Power BI Desktop. Также сюда можно заимствовать отдельные визуализации из чужих отчетов, собирая из них свои авторские дашборды.

Выглядит это примерно так:

На iPhone, например, созданный выше отчет выглядит так:

Причем всё это с сохранением интерактивностии и анимации + заточенность под тач и рисование по экрану пером. Очень удобно. Таким образом, бизнес-аналитика становится доступной всем ключевым лицам компании в любой момент и в любом месте - нужен только доступ в интернет.

Тарифные планы Power BI. Power BI Desktop и Mobile бесплатны изначально, большинство функций Power BI Service - тоже. Так что для персонального использования или применения в пределах небольшой компании за всё вышеперечисленное не нужно платить ни копейки и можно смело оставаться на плане Free. Если вы хотите делиться отчетами с коллегами и администрировать их права доступа, то придется перейти на Pro (10$ в месяц за пользователя). Есть еще Premium - для больших компаний (>500 пользователей), которым требуются для данных отдельные хранилища и серверные мощности.

С помощью статистической функции СЧЁТЕСЛИ можно подсчитать количество ячеек, отвечающих определенному условию (например, число клиентов в списке из определенного города).

Самая простая функция СЧЁТЕСЛИ означает следующее:

=СЧЁТЕСЛИ(где нужно искать;что нужно найти)

СЧЁТЕСЛИ(диапазон;критерий)

Имя аргумента

диапазон (обязательный)

Группа ячеек, для которых нужно выполнить подсчет. Диапазон может содержать числа, массивы, именованный диапазон или ссылки на числа. Пустые и текстовые значения игнорируются.

критерий (обязательный)

Число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать.

Например, критерий может быть выражен как 32, ">32", В4, "яблоки" или "32".

В функции СЧЁТЕСЛИ используется только один критерий. Чтобы провести подсчет по нескольким условиям, воспользуйтесь функцией СЧЁТЕСЛИМН.

Примеры

Чтобы использовать эти примеры в Excel, скопируйте данные из приведенной ниже таблицы и вставьте их на новый лист в ячейку A1.

Количество ячеек, содержащих текст "яблоки" в ячейках А2–А5. Результат — 2.

Количество ячеек, содержащих текст "персики" (значение ячейки A4) в ячейках А2–А5. Результат — 1.

Количество ячеек, содержащих текст "яблоки" (значение ячейки A2) и "апельсины" (значение ячейки A3) в ячейках А2–А5. Результат — 3. В этой формуле для указания нескольких критериев, по одному критерию на выражение, функция СЧЁТЕСЛИ используется дважды. Также можно использовать функцию СЧЁТЕСЛИМН.

Количество ячеек со значением больше 55 в ячейках В2–В5. Результат — 2.

Количество ячеек со значением, не равным 75, в ячейках В2–В5. Знак амперсанда (&) объединяет оператор сравнения "<>" (не равно) и значение в ячейке B4, в результате чего получается формула =СЧЁТЕСЛИ(B2:B5;"<>75"). Результат — 3.

Количество ячеек со значением, большим или равным 32 и меньшим или равным 85, в ячейках В2–В5. Результат — 3.

Количество ячеек, содержащих любой текст, в ячейках А2–А5. Подстановочный знак "*" обозначает любое количество любых символов. Результат — 4.

Количество ячеек, строка в которых содержит ровно 7 знаков и заканчивается буквами "ки", в диапазоне A2–A5. Подставочный знак "?" обозначает отдельный символ. Результат — 2.

Распространенные неполадки

Возможная причина

Для длинных строк возвращается неправильное значение.

Функция СЧЁТЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 символов.

Для работы с такими строками используйте функцию СЦЕПИТЬ или оператор сцепления &. Пример: =СЧЁТЕСЛИ(A2:A5;"длинная строка"&"еще одна длинная строка").

Функция должна вернуть значение, но ничего не возвращает.

Аргумент критерий должен быть заключен в кавычки.

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

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Самая простая функция СЧЁТЕСЛИ означает следующее:

=СЧЁТЕСЛИ(где нужно искать;что нужно найти)

СЧЁТЕСЛИ(диапазон;критерий)

Имя аргумента

диапазон (обязательный)

критерий (обязательный)

Число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать.

Например, критерий может быть выражен как 32, ">32", В4, "яблоки" или "32".

Примеры

Количество ячеек, содержащих текст "яблоки" в ячейках А2–А5. Результат — 2.

Количество ячеек, содержащих текст "персики" (значение ячейки A4) в ячейках А2–А5. Результат — 1.

Количество ячеек со значением больше 55 в ячейках В2–В5. Результат — 2.

Количество ячеек со значением, большим или равным 32 и меньшим или равным 85, в ячейках В2–В5. Результат — 3.

Распространенные неполадки

Возможная причина

Для длинных строк возвращается неправильное значение.

Функция должна вернуть значение, но ничего не возвращает.

Аргумент критерий должен быть заключен в кавычки.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Читайте также:

Excel power query счетесли

Конструирование таблиц

Преобразования

Сведения

Операции со строками

Операции со столбцами

Параметры

Преобразование

Членство

Упорядочение

Другое

Значения параметров

Именование выходных столбцов

Критерии сравнения

Критерий количества или условия

Обработка дополнительных значений

Обработка отсутствующих столбцов

Порядок сортировки

Критерии равенства

Power Query

Power Pivot

Power Maps

Power View

Power BI

Примеры

Распространенные неполадки

Рекомендации

Дополнительные сведения

Примеры

Распространенные неполадки

Рекомендации

Дополнительные сведения