Как в excel вписать формулу: Как быстро применить формулу ко всему столбцу или строке с / без перетаскивания в Excel?

Глава 17. Формулы Power Query

Это продолжение перевода книги Кен Пульс и Мигель Эскобар. Язык М для Power Query. Главы не являются независимыми, поэтому рекомендую читать последовательно.

Предыдущая глава    Содержание    Следующая глава

Пользовательский интерфейс Power Query позволяет выполнять огромное число операций. Но наверняка возникнут моменты, когда вам потребуется что-то сделать, что не встроено в интерфейс. Вот мы и добрались до языка программирования Power Query: M.

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

Рис. 17.1. Интерфейс создания пользовательского столбца

Скачать заметку в формате Word или pdf, примеры в формате архива

Создание пользовательских столбцов

Окно создания настраиваемого столбца содержит три важные части (см. 17.1):

1. Имя столбца
2. Доступные столбцы: здесь перечислены имена всех столбцов в запросе. Двойной щелчок любого элемента в этом поле помещает его в область формулы с правильным синтаксисом для ссылки на поле.
3. Пользовательская формула столбца – место, где вы записываете формулу.

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

• Дважды щелкните Class в списке Доступные столбцы (или выделите строку Class и нажмите кнопку Вставить)
• Введите символ &
• Дважды щелкните столбец Account #2 в списке Доступные столбцы

Power Query построит формулу: =[Class]&[#"Account #2"]

Самое замечательное в использовании интерфейса двойного щелчка заключается в том, что для вас не имеет значения, что синтаксис Class и Account #2 должен обрабатываться по-разному. Этот специфический синтаксис объясняется в главе 19 и главе 20. Вы также можете выполнить все четыре арифметических действия: –, + ,  /, *. В то же время для возведения в степень нужно применить формулу: =Number.Power([Column1],[Column2]).

К сожалению, Power Query не имеет подсказок, чтобы выяснить, какие функции можно использовать. Но… в окне Настраиваемый столбец есть гиперссылка Сведения о формулах Power Query (см. рис. 17.1). Щелкнув на нее, вы попадете на страницу с подробным каталогом функций, правда, на английском языке. В отличие от Excel, но аналогично Power Pivot, функции Power Query не русифицированы.

Подводные камни формул на языке М

Power Query и Excel существенно различаются в том, как они обрабатывают входные данные.

Рис. 17.2. Различия Power Query и Excel в обработке данных

Чувствительность к регистру. Запомните, что в 99% случаев первая буква каждого слова в формуле на языке М – заглавная, а остальные – строчные. В то время как Excel не заботится, какие буквы вы используете и преобразует формулы в верхний регистр по умолчанию, Power Query просто возвращает ошибку.

База 0 против базы 1. Если бы вас спросили о номере позиции буквы x в слове Excel, вы сказали бы 2. Это логично, и так считает программа MS Excel. Но Power Query скажет, что буква x в слове Excel занимает позицию 1.

Преобразование типов данных. В Excel вы можете добавить единицу к дате, что изменит дату на один день. В Power Query, если дата отформатирована с типом Даты, необходимо использовать специальную формулу чтобы добавить к ней день. Если вы попытаетесь использовать ту же формулу для добавления дней к числу, Power Query вернет ошибку. Это означает, что перед использованием столбцов в формулах необходимо явно задавать в них тип данных.

В Excel вы можете объединить две ячейки вместе с помощью функции конкатенации &. Содержат ли ячейки текст или числа, не важно. Excel неявно преобразует их в текст, а затем объединяет вместе:

Рис. 17.3. Неявное преобразование данных в Excel: число и текст, преобразованные в текст

Создайте на основе двух первых столбцов Таблицы Excel запрос, а затем внутри Power Query создаете пользовательский столбец, используя формулу: =[Column1]&[Column2]:

Рис. 17.4. Power Query не может соединить число и текст вместе

Чтобы устранить эту проблему, необходимо сначала преобразовать тип данных Столбец1 в текст, а уже затем создать пользовательский столбец:

Рис. 17.5. Два текстовых столбца объединить можно

При явном преобразовании данных в столбце 1 в текстовое значение конкатенация будет работать так, как вы изначально предполагали:

На самом деле существует два способа работы с типами данных в Power Query:

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

Функции преобразования типов данных

Существует несколько функций преобразования типов данных.

Преобразование в текст. Если вам нужно преобразовать значения в столбце в текст, можно использовать универсальную функцию Text.From(). Если же вы хотите подчеркнуть тип преобразуемых данных, также в вашем распоряжении есть: Date.ToText(), Time.ToText(), Number.ToText(). Имейте в виду, что Text.From() преобразует любой тип данных в текст, в то время как Date.ToText() не преобразует число в текст.

Даты. Данные, похожие на даты, могут поступать в формате чисел или текста. Для их преобразования есть две функции: Date.From() и Date.FromText(). Опять же, Date.From() справится с преобразованием в формат даты, как чисел, так и текста.

Время. Значения времени могут поступать как в виде чисел, так и в виде текста. Опять же, есть две функции для них: Time.From() и Time.FromText().

Длительность – это разница между двумя значениями даты/времени: Duration. From() и Duration.FromText().

Числа. Имеется универсальная функция Number.From() и несколько специальных. Для чисел из текста Number.FromText(), для десятичных чисел Decimal.From(), целых чисел Int64.From(), валюты Currency.From().

Сравнение текстовых функций Excel и Power Query

Если вы работали с текстовыми функциями Excel, то привыкли использовать их для извлечения элементов текста из данных. В Power Query текстовые функции работают иначе. Рассмотрим пять наиболее часто используемых текстовых функций Excel, и их аналоги в Power Query. Откройте файл 5 Useful Text Functions.xlsx. Каждый из примеров в этом разделе начинается с набора данных:

Рис. 17.6. Пример данных

В августе 2015 года команда Power Query добавила возможность извлечения первого, последнего и ряда символов на вкладку Преобразование. Несмотря на это, ниже рассматривается процесс извлечения текста с помощью языка M, что позволит глубже познакомиться с языком, и создавать более надежные решения, чем те, что могут быть созданы с помощью команд пользовательского интерфейса.

Итак, что поместить данные, представленные на рис. 17.6, в Power Query, кликните на любой ячейке в диапазоне А1:В8 –> Данные –> Из таблицы/диапазоне. Подтвердите создание Таблицы с заголовком. В окне редактора Power Query переименуйте запрос pqLeft. Перейдите на вкладку Добавление столбца –> Настраиваемый столбец. Назовите новый столбец pqLeft(x,4). Введите формулу: =LEFT([Слово],4). Вроде бы, это должно сработать:

Рис. 17.7. Power Query не находит синтаксических ошибок

Однако, после нажатия Ok, появляется ошибка:

Рис. 17.8. Формула =LEFT([Слово],4) не работает

В Power Query используется иной синтаксис =Text.Start(text,num_chars). Отредактируйте формулу. В области ПРИМЕНЕННЫЕ ШАГИ кликните на шестеренку справа от строки Добавлен пользовательский столбец, и в окне Настраиваемый столбец введите формулу: =Text.Start([Слово],4). Не забывайте, что формулы в Power Query чувствительны к регистру: Text. start и TEXT.START вернут ошибку. Нажмите Ok:

Рис. 17.9. Функции ЛЕВСИМВ() в Excel соответствует Text.Start() в Power Query

Теперь вы можете завершить запрос: Главная –> стрелочка вниз возле кнопки Закрыть и загрузить –> Закрыть и загрузить в… –> Только создать подключение.

Никогда не используйте имя функции Excel в качестве имени запроса Power Query. Если бы вы назвали запрос ЛЕВСИМВ, вы бы получили ошибки в формулах Excel исходной Таблицы. Имена таблиц обрабатываются перед функциями.

Рис. 17.10. Не давайте запросам имена, совпадающие с именами функций Excel; чтобы увеличить изображение кликните на нем правой кнопкой мыши и выберите Открыть картинку в новой вкладке

Рис. 17.11. Соответствие текстовых функций Excel и Power Query

Первые две функции из таблицы аналогичны только что рассмотренной Text.Start(). Использование двух последних функций требует небольшой коррекции в связи с тем, что Power Query за точку начала отсчета берет ноль. Также обратите внимание на следующее различие: аргумент искомый текст является первым в функции НАЙТИ(), и – вторым в функции Text.PositionOf().

Аналог функции НАЙТИ

В файле 5 Useful Text Functions.xlsx перейдите на лист FIND. Кликните на одной из ячеек таблицы, пройдите по меню Данные –> Из таблицы/диапазона. В окне редактора Power Query переименуйте запрос pqFind. Перейдите на вкладку Добавление столбца –> Настраиваемый столбец. Назовите новый столбец pqFind(x,"o"). Введите формулу: =Text.PositionOf([Word],"o"). Нажмите Ok.

Рис. 17.12. Результат не вполне согласуются с Excel

Возвращаемые значения, следуют базовому правилу. В первой строке буква F идет под номером 0. Измените формулу, добавив 1: =Text.PositionOf([Word],"o")+1.

Рис. 17.13. Есть совпадение с Excel, а вместо ошибок выводится значение ноль

Аналог функции ПСТР

В файле 5 Useful Text Functions. xlsx перейдите на лист MID. Кликните на одной из ячеек таблицы, пройдите по меню Данные –> Из таблицы/диапазона. В окне редактора Power Query переименуйте запрос pqMid. Перейдите на вкладку Добавление столбца –> Настраиваемый столбец. Назовите новый столбец pqMid(x,5,4). Введите формулу: =Text.Range([Word],5,4). Нажмите Ok. Результат не соответствует ожиданиям:

Рис. 17.14. Несколько результатов адекватно, но почему не все?

Это немного удивляет. Вы ожидали, что результат не будет соответствовать Excel. Но что число ошибок будет таким большим!? Для начала исправим положение начального символа. На примере Bookkeeper вы ожидали увидеть keep, а появился eepe. Поскольку первый символ в слове имеет номер ноль, вам нужно исправить формулу на =Text.Range([Word],5-1,4).

Рис. 17.15. Уже лучше, но всё еще ошибки в двух последних строках

Одна из замечательных особенностей функции Mid (ПСТР) Excel заключается в том, что вас не волнует, сколько символов осталось в текстовой строке. Если конечный параметр больше, чем количество оставшихся символов, он просто вернет все оставшиеся символы. Не таков Power Query. Вам нужно дополнить формулу проверкой: вы хотите вернуть четыре символа или меньше, до конца текстовой строки. Для этих целей подойдет функция List.Min (подробнее о ней вы узнаете из главы 20). Вместо того, чтобы пытаться встроить эту функцию в формулу столбца pqMid(x,5,4), создайте еще один пользовательский столбец с формулой =List.Min({Text.Length([Word])-(5-1),4}).

Рис. 17.16. В отдельном столбце определено количество оставшихся символов

Несколько слов о том, как работает формула:

• Text.Length([Word])-(5-1) подсчитывает длину слова в столбце Word и вычитает начальную позицию. Вы использовали выражение (5-1), чтобы подчеркнуть, что хотели взять пятый символ, но исправили формулу для базы 0 (можно использовать и 4).
• Последняя четверка в формуле – максимальное количество символов, которые вы хотите вернуть
• Для того, чтобы использовать их в функции List. Min() они должны быть окружены фигурными скобками и разделены запятыми.

Теперь вы можете отредактировать формулу в столбце pqMid(x,5,4) =Text.Range([Word],5-1, List.Min({Text.Length([Word])-(5-1),4}))

Рис. 17.17. Всё верно, кроме последней строки

Теперь вы можете удалить вспомогательный столбце Пользовательская и загрузить запрос в Таблицу на лист Excel. А как же ошибка в последней строке. Не страшно. Потому что ошибки в Power Query будут показываться в Excel, как пустые ячейки:

Рис. 17.18. Ошибки исчезают при загрузке в Таблицу

Очень жаль, что не все функции в Power Query эквивалентны функциям в Excel. Особенно учитывая, что Power Query – это инструмент для пользователей Excel. Надеемся, что это изменится в будущих версиях. Мы хотели бы видеть новую библиотеку функций Power Query, которые позволяют переносить существующие знания из Excel в Power Query без необходимости изучать новые функции и новый синтаксис.

Использование вложенной функции If в Excel для создания категорий

Вероятно, у вас есть много данных о населении, которые вы собрали с помощью оценок и опросов. И, скорее всего, вы используете Excel (иногда или постоянно) для анализа всех этих данных. Функция ЕСЛИ в Excel представляет собой логическое выражение, состоящее из «логического теста» и результата «истина/ложь». И вы можете использовать функцию Excel Nested If для применения категорий к данным о населении.

В каких случаях следует использовать вложенную функцию Excel?

Вот три примера, когда вы можете захотеть использовать функцию Excel Nested If:

• Создать возрастные категории для отдельных лиц, когда вы собрали только возрастные данные
• Используйте формулу (например, расчет уязвимости) только для определенных респондентов опроса
• Применить диапазоны доходов к результатам отдельных домохозяйств, когда вы собрали только данные о доходах

Синтаксис базовой функции

IF

=IF(логическая_проверка, [значение_если_истина], [значение_если_ложь])

logical_test чаще всего является сравнением двух значений. Value_if_true — это значение, которое будет возвращено в результате формулы, если logical_test  равно TRUE . Value_if_false — это значение, которое будет возвращено, если logical_test равно FALSE .

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

Создание вложенного файла Excel  IF функция, если logical_test равно FALSE , мы хотим применить секунд logical_test. В этом случае мы поместим новую функцию ЕСЛИ вместо параметра [ значение_если_ложь] .

Синтаксис скучный. Давайте посмотрим на реальный пример. Возрастные категории снимок возрастных данных:

Если бы мы представили эти данные в виде гистограммы, вот как это выглядело бы (ни для кого не очень полезно) – просто много столбцов:

определенные возрастные категории по показателям вашей организации или по показателям ваших доноров. Например, давайте использовать следующие возрастные категории:

• Менее 18 лет (<18)
• 18-39 лет
• 40-59 лет
• 60 лет и старше (>60)

Если вы примените вложенную функцию if Excel к данным о возрасте, используя эти категории, вместо этого вы получите эту диаграмму (гораздо удобнее поместить в отчет):

1. Напишите свою первую функцию If

Сначала добавьте в таблицу новый столбец и назовите его «Возрастные категории». Начните с добавления одной функции «Если» в верхнюю ячейку этого столбца. Сделайте эту функциональную проверку: «если возраст меньше 18 лет, то применяется возрастная категория «<18 лет"". Вы пишете функцию так:

• Сначала напишите знак «равно», затем напишите «если» и открывающую скобку.
• Затем щелкните ячейку, в которой хранятся данные о вашем возрасте (в моем примере данные о моем возрасте находятся в ячейке E2).
• Затем поставьте знак «меньше», укажите возраст 18 лет и запятую, чтобы отметить конец логического теста.
• Затем поместите двойные кавычки и текст, который вы хотите применить, и снова двойные кавычки.
• Наконец, поставьте еще одну скобку для завершения функции:

=if(E2<18,"менее 18 лет")

• И нажмите Enter = если ваши возрастные данные больше или равны 18, ячейка теперь будет видеть "ЛОЖЬ"

2. Добавьте вторую функцию «если»

Прямо сейчас мы проверяем только одну из наших возрастных категорий (мы проверяем, не меньше ли данных в ячейке E2 18). Итак, вы собираетесь добавить в формулу вторую функцию ЕСЛИ, чтобы проверить, относится ли она к следующей возрастной категории.

• Возьмите формулу, которую вы уже написали. После второго набора двойных кавычек добавьте запятую.
• Если вы нажмете «Alt+Enter» на клавиатуре, следующая часть формулы будет помещена на следующую строку, что упростит отслеживание всех операторов if в вашей формуле.
• Затем снова напишите «if» и еще одну открытую скобку и проверьте, меньше ли возраст 40 лет, написав E2<40. Затем поставьте еще одну запятую, чтобы закончить новый логический тест.
• Далее введите «18-39 лет».
• А затем поместите две закрывающие скобки, чтобы закрыть оба оператора if:

=if(E2<18,"младше 18 лет",
if(E2<40,"18-39 лет"))

• Когда закончите, нажмите Enter.

3. Добавьте третью функцию «если»

Теперь вы проверяете, соответствует ли возраст одной из двух возрастных категорий. Во-первых, вы проверяете, меньше ли данных в ячейке E2 18. Затем, если данные не равно меньше 18, вы используете вложенную функцию if, чтобы проверить, соответствует ли оно ). Теперь вы собираетесь добавить в формулу третью функцию ЕСЛИ, чтобы проверить, относится ли она к следующей возрастной категории (40–59 лет).

• Возьмите формулу, которую вы уже написали. После «18-39 лет» поставьте запятую и нажмите «Alt+Enter» на клавиатуре — это перенесет следующую часть формулы на следующую строку.
• Затем снова напишите «if» и еще одну открытую скобку и проверьте, меньше ли возраст 60 лет, написав E2<60. Затем поставьте еще одну запятую, чтобы закончить новый логический тест.
• Далее введите «40-59 лет».
• А затем поместите в три закрывающие скобки, чтобы закрыть все три оператора if:

=if(E2<18,"младше 18 лет",
if(E2<40,"18-39 лет",
if(E2<60,"40-59 лет")))

• Нажмите Enter, когда закончите.

4. Добавьте последнюю возрастную категорию

Для вашей последней возрастной категории вам не нужно помещать еще один вложенный оператор if. Вы просто хотите заявить, что «если логический тест по-прежнему ЛОЖЬ — то просто присвойте ему эту оставшуюся возрастную категорию». Мы хотим, чтобы в последней категории было просто написано «60+ лет». Неважно, возраст 60 или 600 лет, он все равно будет применять эту конечную возрастную категорию.

• Возьмите формулу, которую вы уже написали. После «40-59 лет» поставьте запятую и нажмите «Alt+Enter» на клавиатуре — это перенесет следующую часть формулы на следующую строку.
• Затем напишите «60+ years» и оставьте три закрытых скобки, чтобы закрыть все три оператора if:

=if(E2<18,"моложе 18 лет",
if(E2<40,"18-39 лет",
if(E2<60,"40-59 лет",
"60 + лет")))

• Нажмите Enter, когда закончите.

5. Скопируйте формулу вниз.

Скопируйте и вставьте (или «заполните») формулу во весь столбец «Возрастные категории». Вот частичный снимок экрана:

6. Сделайте сводную таблицу столбца «Возрастная категория»

• Выделите столбец возрастной категории
• Нажмите Вставка -> Сводная таблица
• Нажмите «ОК», чтобы вставить сводную таблицу в новую электронную таблицу
• Перетащите «Возрастные категории» в сводную таблицу «Строки»
• Также перетащите «Возрастные категории» в сводную таблицу «Значения», чтобы она отображала «Количество возрастных категорий»

7.

Составьте гистограмму итоговых возрастных категорий

• Выделите данные из сводной таблицы
• Скопируйте и «вставьте как значения», чтобы данные больше не были связаны со сводной таблицей
• Превратить «абсолютные» числа в «проценты»
• Нажмите «Вставка» -> «Гистограмма»
• Отформатируйте гистограмму по своему вкусу

Как вычислить первые 10 с помощью формул в Excel

Списки первых 10 — это распространенная форма для отображения информации, особенно на информационных панелях и сводных отчетах. Легко создать топ-10 в Excel при работе с отсортированными данными; просто дайте ссылку на 10 лучших элементов в списке… легко! Это также относительно просто при использовании автофильтра, таблиц и сводных таблиц, так как это настройка фильтра по умолчанию в этих функциях. Однако создание топ-10 с формулами на несортированном наборе данных становится немного сложнее.

Но формулы являются наиболее гибким методом создания отчетов в Excel. Поэтому в этом посте я покажу вам, как точно рассчитать список 10 лучших. С помощью этих методов вы не ограничены топ-10; вы можете создать топ-5, топ-8 или любой номер по вашему выбору.

С момента появления в Excel вычислений динамических массивов у нас появился новый и более простой способ создания списка 10 лучших. Поэтому мы начнем с рассмотрения динамических массивов. Однако не беспокойтесь, если у вас Excel 2019.или ранее, мы предоставим решение и для этого.

Загрузите файл примера : Щелкните ссылку ниже, чтобы загрузить файл примера, использованный для этого сообщения:

0015 Top 10 с использованием формул.xlsx Download

Примечание: Если у вас нет версии с поддержкой Dynamic Array Excel, на вкладках, содержащих эти примеры, будут отображаться ошибки.

Посмотреть видео:

Примечание: Сообщение в блоге было обновлено, чтобы включить последние формулы динамического массива; они не включены в видео.

Посмотреть видео на YouTube

Содержание

Функции динамического массива

Если у вас есть версия Excel с поддержкой динамического массива (Excel 365 и Excel 2021), это отличная новость, так как вам не нужно полагаться на сложные формулы . Вместо этого динамические массивы упрощают работу.

Комбинация функций ИНДЕКС / СОРТИРОВКА / ПОСЛЕДОВАТЕЛЬНОСТЬ (Excel 2021 и Excel 365)

Начните с просмотра вкладки Top 10 — DA в файле примера.

В нашем примере файла есть 26 клиентов с их соответствующими местоположениями и значениями.

В ячейке F4 формула:

 =ИНДЕКС(СОРТИРОВКА(A2:C27,3,-1),ПОСЛЕДОВАТЕЛЬНОСТЬ(10),{1,3}) 

Вот и все! Это простое решение. Нет необходимости в сложных формулах, не нужно нажимать Ctrl+Shift+Enter и не нужно копировать формулы вниз.

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

СОРТИРОВКА

СОРТИРОВКА имеет четыре аргумента:

 =СОРТИРОВКА(массив, [индекс_сортировки], [порядок_сортировки], [по_столбцу]) 

• массив: диапазон ячеек или массив значений для сортировки.
• [sort_index]: n-й столбец или строка, к которым применяется сортировка. Например, для сортировки по 3-му столбцу индекс сортировки будет равен 3.
• [порядок_сортировки]: 1 = сортировка по возрастанию; -1 = сортировать по убыванию (если исключено, аргумент по умолчанию равен 1).
• [by_col]: TRUE = сортировать по столбцам; ЛОЖЬ = сортировать по строкам (если исключено, аргумент по умолчанию имеет значение ЛОЖЬ).

В нашей формуле мы использовали следующую СОРТИРОВКУ.

 SORT(A2:C27,3,-1) 

Сортирует ячейки от A2 до C27 в 3-м столбце в порядке убывания.

Узнайте больше о функции СОРТИРОВКА в этой статье: Функция СОРТИРОВКИ в Excel.

ПОСЛЕДОВАТЕЛЬНОСТЬ

Функция ПОСЛЕДОВАТЕЛЬНОСТЬ имеет четыре аргумента:

 =ПОСЛЕДОВАТЕЛЬНОСТЬ(строки, [столбцы], [начало], [шаг]) 

• строки: число возвращаемых строк
• [столбцы]: число столбцов для возврата. Если он исключен, он возвращает один столбец.
• [начало]: первое число в последовательности. Если опущено, оно начинается с 1.
• [шаг]: сумма увеличения каждого последующего значения. Если исключить, каждое приращение равно 1.

В нашем примере функция ПОСЛЕДОВАТЕЛЬНОСТЬ создает список от 1 до 10. Нам нужен только первый аргумент; мы можем использовать параметры по умолчанию для остальных аргументов.

 ПОСЛЕДОВАТЕЛЬНОСТЬ(10) 

Результатом этой формулы является массив {1;2;3;4;5;6;7;8;9;10}

. Узнайте больше о функции ПОСЛЕДОВАТЕЛЬНОСТЬ в этой статье: Функция ПОСЛЕДОВАТЕЛЬНОСТЬ в Экселе.

ИНДЕКС

Давайте используем СОРТИРОВКУ и ПОСЛЕДОВАТЕЛЬНОСТЬ в традиционной функции ИНДЕКС:

 =ИНДЕКС(СОРТИРОВКА(A2:C27,3,-1),ПОСЛЕДОВАТЕЛЬНОСТЬ(10),{1,3}) 

Формула вычисляет первые 10 результатов СОРТИРОВКИ и возвращает столбцы 1 и 3.

Это было так просто… правильно.

Комбинация функций ВЫБОР / ВЫБОР / СОРТИРОВКА / ПОСЛЕДОВАТЕЛЬНОСТЬ (только Excel 365)

Если у вас есть Excel 365, у нас есть доступ к еще большему количеству динамических формул массива. Две из них — это ВЫБОР и ВЫБОР, которые мы можем использовать вместо функции ИНДЕКС.

Формула в ячейке K4 :

 =ВЫБРАТЬСТРОК(ВЫБОРСКОРОСТ(СОРТИРОВКА(A2:C27,3,-1),1,3),ПОСЛЕДОВАТЕЛЬНОСТЬ(10)) 

Формула может быть длиннее, чем альтернатива ИНДЕКС, но она более гибкая. Давайте взглянем на функции CHOOSEROWS и CHOOSECOLS.

CHOOSEROWS и CHOOSECOLS

CHOOSEROWS и CHOOSECOLS возвращают только указанные строки или столбцы из массива. У них похожий синтаксис и только 3 аргумента:

 =ВЫБОР(массив,номер_строки1, [номер_строки2],…) 

 =ВЫБОРСКОРОСТ(массив,номер_столбца1, [номер_столбца2],…) 

• массив: массив, содержащий строки или столбцы, которые нужно вернуть
• row_num1 / col_num1: первая возвращаемая строка/столбец
• [row_num2] / [col_num2] : возвращаемые дополнительные номера строк/столбцов

В нашей формуле мы использовали следующее:

 CHOOSECOLS(SORT( A2:C27,3,-1),1,3) 

Берет отсортированные значения из ячеек A2:C27 (отсортированные по столбцу 3 в порядке убывания) и возвращает только столбцы 1 и 3.

Затем мы обертываем результат внутри функции CHOOSEROWS.

 =ВЫБОР РЯДОВ(ВЫБОРЦВЕТ(СОРТИРОВКА(A2:C27,3,-1),1,3),ПОСЛЕДОВАТЕЛЬНОСТЬ(10)) 

Берет предыдущий результат из CHOOSECOLS и возвращает только первые 10 строк.

Нижние 10

Чтобы получить последние 10, нам нужно всего лишь изменить 3-й аргумент функции СОРТИРОВКИ с -1 на 1. Посмотрите на ячейки F18 и K18 в файле примера, чтобы увидеть это в действии.

Динамические массивы с критериями

Даже если у нас есть критерии выбора для применения, с динамическими массивами все еще просто. Ячейка J4 содержит название города; Лондон или Бирмингем. Мы хотим, чтобы в первую десятку вошли клиенты только из этого города.

Посмотрите на вкладку Top 10 — DA с критерием в файле примера.

Формула в ячейке F4:

 =ИНДЕКС(СОРТИРОВКА(  ФИЛЬТР(A2:C27,B2:B27=J4)  ,3,-1),ПОСЛЕДОВАТЕЛЬНОСТЬ(10),{1,3}) 

Единственное отличие от предыдущего примера заключается в том, что мы используем функцию ФИЛЬТР для включения совпадающих элементов до того, как они будут переданы в функцию СОРТИРОВКА.

ФИЛЬТР

ФИЛЬТР имеет три аргумента:

 =ФИЛЬТР(массив, включить, [если_пусто]) 

• массив: Диапазон ячеек или массив значений для фильтрации.
• включает: Массив результатов ИСТИНА/ЛОЖЬ, где значения ИСТИНА сохраняются в результате.
• [if_empty]: значение для отображения, если строки не возвращаются.

В нашем примере функция ФИЛЬТР имеет вид:

 ФИЛЬТР(A2:C27,B2:B27=J4) 

Она возвращает ячейки от A2 до C27, но только в том случае, если соответствующие значения от B2 до B27 равны выбранному городу в ячейка J4.

Узнайте больше о функции ФИЛЬТР в этой статье: функция ФИЛЬТР в Excel.

Последние 10 с критериями

Посмотрите файл примера (ячейки E17:h37), чтобы увидеть, как вычисляются последние 10 с критериями.

ВЫБОР / ВЫБОР / СОРТИРОВКА / ПОСЛЕДОВАТЕЛЬНОСТЬ комбинация с критериями

В файле примера вы также найдете комбинацию ВЫБОР / ВЫБОР / СОРТИРОВКА / ПОСЛЕДОВАТЕЛЬНОСТЬ в ячейке M18.

 =ВЫБРАТЬСТРОК(ВЫБОРЦВЕТ(СОРТИРОВКА(ФИЛЬТР(A2:C27,B2:B27=J4),3,1),1,3),
SEQUENCE(10)) 

Эта комбинация описанных выше функций может использоваться только с Excel 365.

Работа с несколькими критериями

Ключом к работе с несколькими критериями является функция ФИЛЬТР.

Посмотрите на Top 10 — DA с вкладкой multi в файле примера.

Добавлен дополнительный критерий для включения только элементов со значением, равным городу ИЛИ меньше 100.

Формула в ячейке F4:

 =ИНДЕКС(СОРТИРОВКА(ФИЛЬТР(A2:C27,  (B2: B27=J4)+(C2:C27 ),3,-1),
SEQUENCE(10),{1,3}) 

Критерии фильтрации выделены выше. Мы можем использовать этот подход, чтобы добавить столько критериев фильтрации, сколько нам нужно.

При использовании функции ФИЛЬТР символ плюса ( + ) создает логику ИЛИ; для создания логики И мы используем звездочку ( * ). Это показано в примере ниже.

 =ИНДЕКС(СОРТИРОВКА(ФИЛЬТР(A2:C27,  (B2:B27=J4)*(C2:C27 ),3,-1),
SEQUENCE(10),{1,3}) 

Традиционные функции

Хорошо, теперь давайте перейдем к рассмотрению традиционных функций (т. е. нединамического массива). Используя их, получение первых 10 значений не является проблемой. Но проблема заключается в том, чтобы получить имена/метки, относящиеся к этим десяти значениям.

Начните с просмотра вкладки Top 10 — non-DA в файле примера.

Использование функции НАИБОЛЬШИЙ

В нашем примере файла есть 26 клиентов с их значениями.

Мы используем функцию НАИБОЛЬШИЙ, чтобы создать топ-10 этих клиентов (без сортировки списка). Ячейка G4 содержит следующую формулу:

 =НАИБОЛЬШИЙ($C$2:$C$27,СТРОКА(F4)-СТРОКА(F$3)) 

НАИБОЛЬШИЙ имеет два аргумента:

 =НАИБОЛЬШИЙ(диапазон, k) 

• диапазон : Диапазон данных для анализа.
• k: Необходимо найти элемент n ^th .

В нашем примере диапазон данных для анализа — это ячейки с C2 по C27.

Значение k вычисляется как номер строки минус номер строки строки заголовка. Это всегда вычисляет относительную позицию строки в диапазоне ячеек. Так, например, первая строка данных вычисляется как 1, вторая строка вычисляется как 2 и так далее.

 СТРОКА(F4)-СТРОКА(F$3) 

Формула в ячейке G4 копируется вниз для отображения первых 10 значений.

Поиск меток для первых 10

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

Мы не можем использовать функцию ВПР, так как имя клиента находится слева от значения поиска. Поэтому вместо этого мы будем использовать комбинацию формул ИНДЕКС/ПОИСКПОЗ.

Примечание: Поскольку функция XLOOKUP была выпущена после динамических массивов, я исключил ее как вариант.

Ячейка F4 в нашем примере содержит следующую формулу.

 =ИНДЕКС($A$2:$A$27,ПОИСКПОЗ(G4,$C$2:$C$27,0)) 

Если эту формулу скопировать в ячейки F5:F13. Наш рабочий лист будет выглядеть следующим образом:

Вы видите проблему? Наши 10 лучших ценностей не уникальны; есть 3 значения 80 (см. скриншот выше). Базовый расчет ИНДЕКС/ПОИСКПОЗ возвращает только первое значение; поэтому имя Alfa находит 3 раза. Тем не менее, Echo и Tango , у которых тоже 80, в списке нет. Это явно ошибка, так как же нам обойти это?

Поиск меток, когда первые 10 значений не уникальны

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

 {=ИНДЕКС($A$2:$A$27,МАЛЕНЬКИЙ(ЕСЛИ($C$2:$C$27=G4,СТРОКА($C$2:$C$27)-СТРОКА($ 1 канадский доллар)),
СЧЁТЕСЛИ($G$4:G4,G4)))} 

Ого!!! Это большая и сложная формула!

Примечание: Это специальный тип формулы, известный как формула массива. Не ставьте фигурные скобки ( { } ) в начале или в конце при вводе формулы в строку формул; когда вы нажимаете Ctrl+Shift+Enter, Excel сам добавит фигурные скобки. Нажатие Ctrl+Shift+Enter позволяет Excel узнать, что это формула массива.

ПОМНИТЕ! - Если вы вернетесь к формуле массива, чтобы отредактировать ее, вам нужно нажать Ctrl + Shift + Enter, чтобы повторно ввести формулу.

Эта формула ведет себя как ИНДЕКС/ПОИСКПОЗ, но возвращает 1-е, 2-е, 3-е, 4-е… или n-е значение. Давайте копнем немного глубже, чтобы понять, как это работает.

Раздел 1. Функция ЕСЛИ

В середине формулы находится функция ЕСЛИ.

 ЕСЛИ($C$2:$C$27=G4,СТРОКА($C$2:$C$27)-СТРОКА($C$1)) 

На английском это означает:

Если C2 = G4, то вернуть количество строк между C2 и C1.

Поскольку это формула массива, она автоматически переходит к следующей строке и снова вычисляет

Если C3 = G4, то возвращает количество строк между C3 и C1.

И так продолжается.

Если C4 = G4, то вернуть количество строк между C4 и C1.

Сюда входят все ячейки от C2 до C27.

Для формулы в F4 функция ЕСЛИ рассчитывается как:

 {ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;  7  ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;
ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ;
FALSE;FALSE;FALSE;FALSE} 

7-й элемент списка соответствует ячейке G4; поэтому единственное значение, которое не является ЛОЖЬЮ, это 7.

Раздел 2 – функции МАЛЕНЬКИЙ и СЧЁТЕСЛИ

Если мы подаём результат выше в функцию МАЛЕНЬКИЙ, он будет следующим:

  МАЛЕНЬКИЙ(  {ЛОЖЬ;ЛОЖЬ ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;7;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;
ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ;
ЛОЖЬ;ЛОЖЬ;ЛОЖЬ}  ,СЧЁТЕСЛИ($G$4:G4,G4))  

МАЛЕНЬКИЙ находит n ^-е -е наименьшее значение. Он работает аналогично LARGE, но для наименьшего значения.

МАЛЕНЬКИЙ имеет только два аргумента:

 =МАЛЕНЬКИЙ(диапазон,k) 

• диапазон: Диапазон анализируемых данных.
• k: Необходимо найти элемент n ^th .

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

МАЛЕНЬКИЙ найдет первое наименьшее значение, равное 7 , так как все остальные результаты будут ЛОЖНЫ.

Наконец, он оборачивает это в функцию ИНДЕКС, чтобы найти 7-е значение в исходной таблице.

  =ИНДЕКС($A$2:$A$27,  7  )  

Это вычисляет ячейку A8, которая в нашем примере равна Golf .

Все приведенные выше действия должны были показать, как работает формула. Теперь мы можем скопировать всю формулу в ячейки F5:F13.

Проверка повторяющихся значений

Давайте проверим нашу логику повторяющихся значений с ячейкой F11. В первой десятке есть 3 значения, все из которых равны 80; они встречаются в G10, G11 и G12. Метка в F11 должна быть Echo , так как это второе значение 80.

Формула в F11:

 {=ИНДЕКС($A$2:$A$27,МАЛЕНЬКИЙ(IF($C$2:$ C$27=G11,СТРОКА($C$2:$C$27)-СТРОКА($C$1)),
СЧЁТЕСЛИ($G$4:G11,G11)))} 

Часть ЕСЛИ функции вычисляется как:

 {  1  ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;  5  ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;
ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ;  20  ;ЛОЖЬ;
FALSE;FALSE;FALSE;FALSE;FALSE} 

Строки 1, 5 и 20 исходной таблицы соответствуют значению 80. значение, которое мы ищем.

  МАЛЫЙ(  {  1  ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;  5  ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;
ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ;  20  ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;
ЛОЖЬ;ЛОЖЬ;ЛОЖЬ},  2)  

Поскольку вторым наименьшим числом является 5, функция ИНДЕКС возвращает 5-е значение в исходном списке, то есть Эхо . Следовательно, он рассчитал правильное значение.

Нижние 10 значений

Если нам нужны нижние 10 значений, единственное изменение состоит в том, что формула для получения значений использует МАЛЕНЬКИЙ, а не БОЛЬШОЙ.

Посмотрите пример файла. Формула в ячейке G18:

 =МАЛЕНЬКИЙ($C$2:$C$27,СТРОКА(F18)-СТРОКА($F$17)) 

Единственное различие между ячейкой G18 и ячейкой G4 заключается в использовании функции МАЛЕНЬКИЙ.

Формула в ячейке F18 такая же, как мы видели выше (но указывает на другие ячейки).

 =ИНДЕКС($A$2:$A$27,МАЛЕНЬКИЙ(ЕСЛИ($C$2:$C$27=G18,СТРОКА($C$2:$C$27)-СТРОКА($C$1)),
COUNTIF($G$18:G18,G18))) 

Добавление критериев

Теперь пришло время рассмотреть добавление критериев выбора. В нашем примере файла посмотрите на вкладку Top 10 — non-DA с критериями .

Как мы уже видели, ячейка J4 содержит название города; Лондон или Бирмингем. Мы хотим вернуть топ-10 только для выбранного города.

Ячейка G4 имеет следующую функцию:

 {=НАИБОЛЬШИЙ(  ЕСЛИ($B$2:$B$27=$J$4,$C$2:$C$27)  ,СТРОКА(F4)-СТРОКА(F$3) )} 

Это еще одна формула массива. Помните, что не вводите фигурные скобки, а снова нажмите Ctrl+Shift+Enter.

Здесь используется та же логика, которую мы уже видели. Функция ЕСЛИ проверяет ячейки B2:B27; если значение соответствует ячейке J4, возвращается значение в C2:C27.

Если городом является Лондон, IF рассчитывается следующим образом:

 {  80  ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;  80  ;  100  ;  120  ;ЛОЖЬ;  95  ;ЛОЖЬ;ЛОЖЬ;  55  ;ЛОЖЬ;ЛОЖЬ;
 -35  ;  30  ;ЛОЖЬ;ЛОЖЬ;ЛОЖЬ;  80  ;ЛОЖЬ; -20  ;  75  ;  -15  ;ЛОЖЬ;ЛОЖЬ} 

При использовании функции НАИБОЛЬШИЙ только значения для Лондона возвращаются в первую десятку.  Все значения, не относящиеся к Лондону, рассчитываются как ЛОЖЬ.

Аналогичную корректировку вносим в формулу расчета имени клиента (добавленный раздел выделен жирным шрифтом)

 ={ИНДЕКС($A$2:$A$27,МАЛЕНЬКИЙ(
ЕСЛИ(($C$2:$C$27=G4)  *($B$2:$B$27=$J$4)  ,СТРОКА($C$2:$C$27)-СТРОКА($C$1)),
СЧЁТЕСЛИ(G4:$G$4,G4)))} 

Это формула массива. Не вводите фигурные скобки. Нажмите Ctrl+Shift+Enter еще раз.

Работа с несколькими критериями

Посмотрите на вкладку Top 10 — non-DA с несколькими вкладками в файле примера.

Добавлен дополнительный критерий для включения только элементов со значением, равным городу ИЛИ меньше 100.

Ключом к этому являются аргументы, содержащиеся внутри функции ЕСЛИ.

Формула в ячейке G4:

 {=НАИБОЛЬШИЙ(ЕСЛИ( ($B$2:$B$27=$J$4)+($C$2:$C$27<$J$6)  ,$C$2 :$C$27),
СТРОКА(F4)-СТРОКА(F$3))} 

Формула в ячейке F4:

 {=ИНДЕКС($A$2:$A$27,МАЛЕНЬКИЙ(
ЕСЛИ(($C$2:$C$27=G4)*  (($B$2:$B$27=$J$4)+($C$2:$C$27<$J$6))  ,
СТРОКА($C$2:$C$27)-СТРОКА($C$1)),
СЧЁТЕСЛИ(G4:$G$4,G4)))} 

Это формулы массива, поэтому не забудьте нажать Ctrl+Shift+Enter.

Критерии фильтрации выделены выше. Мы можем использовать этот подход, чтобы добавить столько дополнительных критериев фильтрации, сколько захотим.

В Excel символ плюса ( + ) создает логику ИЛИ, а звездочка ( * ) создает логику И. Это показано в следующих формулах:

 {=НАИБОЛЬШИЙ(ЕСЛИ( ($B$2:$B$27=$J$4)*($C$2:$C$27<$J$6)  ,$C$2: 27 канадских долларов),
СТРОКА(F4)-СТРОКА(F$3))} 

 {=ИНДЕКС($A$2:$A$27,МАЛЕНЬКИЙ(
ЕСЛИ(($C$2:$C$27=G4)*  (($B$2:$B$27=$J$4)*($C$2:$C$27<$J$6))  ,
СТРОКА($C$2:$C$27)-СТРОКА($C$1)),
СЧЁТЕСЛИ(G4:$G$4,G4)))} 

Вот это да! Это становилось сложным.

Заключение

Как только вы освоите приемы и функции, вычисление топ-10 с использованием формул в Excel не так уж и плохо.

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

Другие ресурсы, которые могут оказаться полезными:

• Введение в динамические массивы
• Понимание основных формул массива для традиционного Excel

Об авторе

Привет, меня зовут Марк, и я запускаю Excel Off The Grid.

Мои родители рассказали мне, что в возрасте 7 лет я объявил, что стану квалифицированным бухгалтером. Либо я был экстрасенсом, либо у меня не было воображения, как это и произошло. Однако мое путешествие по-настоящему началось только в 35 лет.

В 2015 году я устроился на новую работу, на которой регулярно работал после 22:00. В результате я редко видел своих детей в течение недели. Итак, я начал искать секреты автоматизации Excel. Я обнаружил, что, создав небольшое количество простых инструментов, я могу комбинировать их по-разному, чтобы автоматизировать почти все свои обычные задачи. Это означало, что я мог работать меньше часов (и мне повысили зарплату!). Сегодня я обучаю этим техникам других специалистов в рамках нашей программы обучения, чтобы они тоже могли проводить меньше времени на работе (и больше времени со своими детьми и любимым делом).

Вам нужна помощь в адаптации этого поста к вашим потребностям?

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

Но если вы все еще испытываете трудности, вам следует:

1. Почитайте другие блоги или посмотрите видео на YouTube по той же теме. Вы получите гораздо больше пользы, открыв для себя собственные решения.
2. Спросите «Excel Ninja» в вашем офисе. Удивительно, какие вещи знают другие люди.
3. Задайте вопрос на форуме, таком как Mr Excel, или в сообществе Microsoft Answers. Помните, что люди на этих форумах обычно отдают свое время бесплатно. Поэтому постарайтесь сформулировать свой вопрос, убедитесь, что он четкий и лаконичный. Перечислите все, что вы пробовали, и предоставьте скриншоты, сегменты кода и примеры книг.
4. Используйте Excel Rescue, моего партнера-консультанта. Они помогают, предоставляя решения небольших проблем Excel.