Как создать функцию в excel: Как создать пользовательскую функцию в Excel на VBA

Как создать пользовательскую функцию в Excel на VBA

ExcelVBSФункции Excel

Олег Толочко
  |  
19/12/2022
  |  
184 просмотров
  |  
0 комментариев

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

Что такое пользовательские функции

Пользовательские функции помогают нам расширить функционал вычислений в Excel. Они похожи на встроенные в Excel, однако логику вычислений мы можем задавать сами. Пользовательские функции принимают определенные значения из параметров при вызове, производят с ними определенные вычисления и возвращают результат. Задать эти параметры можно и ссылкой на ячейку, и статичным значением.

Создаются они так же, как и макросы — в окне редактора VBA Excel. Разница в том, что макрос — это алгоритм определенных действий, который сам по себе не возвращает никакого результата и может манипулировать листом, книгой, файлами, практически как реальный пользователь. Функции таких возможностей лишены, и используются только для вычислений или преобразований отправленных в них данных.

Использовать их можно:

  • непосредственно на листе Эксель
  • как часть макроса или другой функции в редакторе VBA
  • как правило условного форматирования

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

Как создать

Давайте попробуем составить простую функцию Excel для сложения двух чисел. Откройте редактор кода сочетанием клавиш «Alt+F11». Затем создайте дополнительный модуль к книге и вставьте приведенный ниже код.

Function СЛОЖИТЬ(number1 As Long, number2 As Long) As Long
    Dim result As Long
    result = number1 + number2
    СЛОЖИТЬ = result
End Function

Сохраните книгу сочетанием клавиш Ctrl+S. Теперь функцию СЛОЖИТЬ() можно использовать на листе. Чтобы проверить ее работу, вернитесь на лист и попробуйте написать =СЛОЖИТЬ(1;2) в ячейку.

=СЛОЖИТЬ(1;2)

Как видите, все работает.

Синтаксис

Давайте разберем из чего синтаксически состоит код.

Вместо команды Sub у макроса, функция определяется командой Function.

Далее идет имя. Оно может быть задано цифрами, кириллическими или латинскими буквами, символом подчеркивания. Нельзя использовать пробелы. Также имя пользовательской функции не должно дублировать имя стандартной, поскольку в противном случае она просто не будет работать.

После имени объявляются аргументы. У аргументов при необходимости можно задать тип данных. Типы аргументов соответствуют стандартным типам данных VBA. При этом функция может быть вообще без аргументов. Также аргументы могут быть обязательными и необязательными. Эти примеры мы разберем позже.

После аргументов можно объявить тип данных возвращаемого значения.

Далее идет непосредственно блок вычислений — в нем мы можем объявлять переменные, проводить операции и вычисления с аргументами. Перед завершающей командой End Function нужно обязательно указать возвращаемое значение:

Function ИМЯФУНКЦИИ()
    ' тут наши вычисления
    ИМЯФУНКЦИИ = 2
End Function

Аргументы

Как правило, задается несколько аргументов, однако их может не быть совсем — Вы наверняка знаете, что в Эксель есть и стандартные функции без аргументов, типа СЕГОДНЯ() или СЛЧИС(). Например, мы можем получить имя текущего листа — для этого не требуется никаких аргументов.

Function ИМЯЛИСТА()
    ИМЯЛИСТА = Application.ActiveSheet.Name
End Function

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

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

Function СЛОЖИТЬЕД(number1 As Long, number2 As Long, Optional ed As String)
    Dim count As Long
    count = number1 + number2 ' Складываем
    If Len(ed) > 0 Then
        result = count & ed ' Если единицы заданы, добавляем их
    Else
        result = count ' Иначе возвращаем просто число
    End If
  СЛОЖИТЬЕД = result
End Function

Обратите внимание, как задан необязательный аргумент — перед именем указано ключевое слово Optional и расположен он после обязательных, это важно.

Необязательный аргумент также позволяет установить значение по умолчанию, и не указывать его при вызове. Для примера, напишем функцию расчета суммы с НДС, а НДС по умолчанию зададим 20%:

Function СНДС(price As Long, Optional nds As Long = 20) As Long
    Dim result As Long
    result = price + price * nds / 100
    СНДС = result
End Function

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

Примеры

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

Рассчитать процент от числа

Function ПРЦН(num1 As Long, num2 As Long) As Double
    Dim result As Double
    result = num1 / num2 * 100
    ПРЦН = result
End Function

Рассчитать маржу товара

Function МАРЖА(price As Long, sebes As Long, Optional dop As Long = 0, Optional logist As Long = 0, Optional nalog As long = 0) As Double
    Dim result As Double
    result = price - sebes - dop - logist - (price * nalog / 100)
    МАРЖА = result
End Function

Рассчитать маржинальность товара

Function МАРЖИНАЛЬНОСТЬ(price As Long, sebes As Long, Optional dop As Long = 0, Optional logist As Long = 0, Optional nalog As long = 0) As Double
    Dim result As Double
    result = (price - sebes - dop - logist - (price * nalog / 100)) / price * 100
    МАРЖИНАЛЬНОСТЬ = result
End Function

Создать функцию в Excel (простая надстройка)

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

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

Итак, задача: добавить в Excel, возможность выхода вКонтакт.

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

Создадим функцию VK для выхода в Контакт:

Function vk()
Dim ie As Object
Set ie = CreateObject(«InternetExplorer.Application»)
ie.Visible = True
ie.Navigate «http://vkontaktui.ru/»
End Function

где http://vkontaktui.ru/ — анонимайзер Контакта (если на работе Контакт закрыт), можно подставить привычный http://vk.com/ или любой другой сайт.

Копируем в наш документ:

функция перехода в Контакт

Теперь переходим на любой лист Excel и пишем формулу =vk() после ввода формулы в любую ячейку и нажатия кнопки Enter будет открываться IE с указанным адресом.

Второй вариант.

Для удобства использования пропишем функцию в Макрос:

Sub vkcom()

Function vk()
Dim ie As Object
Set ie = CreateObject(«InternetExplorer.Application»)
ie.Visible = True
ie.Navigate «http://vkontaktui.ru/»
End Sub

 

 

 

 

 

 

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

 

Теперь сделаем из этого файла надстройку и разместим кнопки быстрого запуска в Ленте и На панели быстрого доступа.

Делается это очень просто. Сохраним makros.xlsm как mkrs.xlа (скачать mkrs.xla)

Откроем новый документ Excel. Перейдем в меню Файл >> Параметры >>Надстройки

появится новое окно:

 

Параметры Excel

 

В управлении выбираем «Надстройки Excel» и кликаем кнопку перейти. Появится новое окно «Надстройки» , через Обзор выбираем созданный файл mkrs.xlа:

 

 

 

 

 

 

Установка надстройки
Теперь выведем макрос на Панель быстрого доступа, сделаем это через меню Параметры :

И добавим новую вкладку на Ленту:

Лента

(Создаем вкладку, группу, называем как нам нравится и добавляем наш макрос).

теперь любой Excel на нашем компьютере будет иметь слегка измененный вид, в котором работает новая функция =VK() и две кнопки:

Excel с включенной надстройкой

 

Надеемся статья была вам полезна. Будем благодарны, если Вы нажмете +1 и/или Мне нравится или поделитесь с друзьями с помощью кнопок ниже. Если у Вас остались вопросы — задавайте!

Спасибо.

 

Советы по Excel: как использовать пользовательские функции без макросов

Краткий обзор

В своей статье за ​​декабрь 2020 г. я поделился несколькими пользовательскими функциями, созданными на языке программирования Excel, Visual Basic для приложений (VBA). Тогда это был единственный способ создавать свои собственные функции. Это недавно изменилось с введением функции LAMBDA в подписной версии Excel (Microsoft 365, ранее Office 365).

Функция LAMBDA позволяет создавать собственные пользовательские функции с использованием имен диапазонов без использования VBA (макросов). Это означает, что файл является стандартным файлом .xlsx.

Функция LAMBDA похожа на функцию LET, описанную в предыдущих статьях. Сам по себе он не выполняет никаких вычислений. Это позволяет вам захватывать ячейки и диапазоны в качестве входных данных, а затем выполнять специальные вычисления с этими ячейками или диапазонами и получать результат.

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

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

Эксклюзив GST

Начнем с простого примера. Допустим, у вас есть система, которая предоставляет вам итоговые суммы счетов, включая налог на товары и услуги, и все счета включают налог на товары и услуги. Вам нужна эксклюзивная сумма GST. На рис. 1 показана формула в ячейке C2, которая вычисляет сумму без учета налога на товары и услуги. Он основан на проценте GST в ячейке h2 и значении в столбце B. Он округляется до двух знаков после запятой.

Нам нужно использовать этот расчет во всем файле. Мы можем создать собственную функцию, которая принимает значение и возвращает исключительное значение GST. Мы создадим имя диапазона с именем GSTExclusive в качестве нашей пользовательской функции.

Шаг 1 — Создайте функцию LAMBDA в ячейке и протестируйте ее.

Функция LAMBDA не предназначена для использования в ячейке, но ее необходимо протестировать в ячейке. Он использует специальный макет, чтобы вы могли его протестировать. Рисунок 2. сравнивает функцию ОКРУГЛ с версией функции ЛЯМБДА, включающей функцию ОКРУГЛ.

В ячейке функции ОКРУГЛ B2 находится значение, которое нам нужно преобразовать. LAMBDA примет это как аргумент с именем Amount.

(B2) в конце — это специальный макет, разрешенный для тестирования функций LAMBDA. Это невозможно сделать с помощью обычных функций. Это передает значение ячейки B2 параметру Amount в начале функции LAMBDA. Вы можете определить более одного параметра. Функция ОКРУГЛ использует значение суммы в своих вычислениях. Функция LAMBDA возвращает значение, которое возвращает функция ОКРУГЛ. Помните, что LAMBA не предназначена для использования в клетках. Все станет яснее, когда вы увидите окончательное решение.

Шаг 2 — Скопируйте функцию LAMBDA в имя диапазона.

В строке формул с помощью мыши выберите и скопируйте функцию LAMBDA, включая знак =, но исключая (B2) в конце.

На вкладке ленты «Формулы» нажмите кнопку «Определить имя» в разделе «Определенные имена».

В поле Имя введите GSTExclusive. В поле Относится к вставьте функцию LAMBDA. Нажмите «ОК». См. Рисунок 3 .

Шаг 3 – Используйте новую функцию имени диапазона. Теперь имя диапазона можно использовать как функцию во всем файле. См. Рисунок 4 .

Шаг 4 – Задокументируйте имя диапазона.

Когда вы вводите стандартное имя функции Excel, Excel отображает аргументы, требуемые функцией. Вы можете имитировать эту функцию, введя сведения о функции в разделе «Комментарий» диалогового окна «Новое имя» или «Редактировать имя». Вы можете сделать это на этапе создания (Шаг 2), а можете сделать это уже после того, как создали имя.

На вкладке «Формула» нажмите «Диспетчер имен». Найдите GSTExclusive, выберите его и нажмите кнопку «Изменить». В диалоговом окне «Редактировать имя» добавьте комментарии, показанные в 9.0017 Рисунок 5 .

Текст комментария появляется, когда вы начинаете вводить имя.

Обратите внимание, что в поле Ссылается ссылка на ссылку на ячейку $H$1 (процент GST) была обновлена ​​и теперь включает имя листа, которое является примером GST. Это гарантирует, что функция работает согласованно во всем файле.

Функция имени листа

Следующий пример более сложный и расширяет статью за апрель 2021 года, в которой демонстрировалась функция LET. Функция LET позволяет вам использовать переменные в формулах и без проблем работает с функцией LAMBDA. Мы создадим функцию с именем SheetName, которая возвращает имя листа на основе ссылки на ячейку.

На рис. 6 показаны все формулы, извлекающие имя листа из текущего листа. Формулы отображаются под ячейкой, содержащей формулу. Лист называется Следующий пример.

Формула в ячейке A1 работает во всех версиях Excel.

Для функции ПУСТЬ в ячейке A4 требуется подписная версия Excel. Функция LET сокращает формулы, уменьшая дублирование с помощью переменных. Обратите внимание, что функция CELL используется три раза в формуле для ячейки A1 и только один раз в версии LET в ячейке A4.

Функция LAMBDA в ячейке A7 предоставляет ссылку на ячейку (Ref) для функции CELL в рамках функции LET.

Это будет общая структура. Параметр из функции LAMBA передается непосредственно в определение переменной в функции LET, что упрощает преобразование существующей функции LET в функцию LAMBDA.

В ячейке A10 используется функция имени диапазона, которая использует функцию LAMBDA и возвращает имя листа.

Функции общего доступа

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

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

Это введение в тему новой функции LAMBDA. Эта новая функция открывает множество возможностей для сокращения, централизации и упрощения формул в Excel.

Надстройка Advanced Formula Environment

Одновременно с выпуском функции LAMBDA появилась новая бесплатная надстройка Excel под названием Advanced Formula Environment, которая может упростить работу с функциями LAMBDA. Он предоставляет отдельную панель задач редактирования формул, которая добавляет разрывы строк и цвета синтаксиса к именам диапазонов LAMBDA. Это похоже на работу с формулами показателей DAX в Power Pivot или Power BI.

Сопутствующее видео и файл Excel более подробно продемонстрируют эти методы.

Нил Блэквуд CPA занимается бухгалтерским учетом A4, проводит обучение Excel, вебинары и консультационные услуги. Вопросы можно направлять по адресу [email protected]

Подробнее о технических навыках

Советы по работе с данными и искусственному интеллекту

6 минут чтения

Советы Excel: импорт цен акций в Excel

5 минут чтения

Эффективно Советы по написанию для бухгалтеров

4 минуты чтения

Подробнее о технических навыках

Как превратить сложные формулы в простые в использовании пользовательские функции с помощью LAMBDA() в Excel

Как превратить сложные формулы в простые в использовании пользовательские функции с помощью LAMBDA() в Excel

В электронных таблицах Excel сложно вводить сложные формулы без ошибок. Функция LAMBDA() Microsoft Excel может упростить поиск таких ошибок.

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

Изображение: Монтичелло/Adobe Stock

Функции LAMBDA являются новыми для Microsoft Excel. С помощью функций LAMBDA вы можете превратить сложный расчет в простую функцию на уровне листа. Вы должны знать сложные вычисления, но они подвержены ошибкам и их трудно поддерживать; например, если что-то изменится, вы можете изменить несколько формул на уровне листа. Почему бы вместо этого не использовать LAMBDA()? Вы вводите сложный расчет один раз, даете ему имя функции, и все.

В этом руководстве я объясню, что такое функция LAMBDA и как использовать новую функцию Excel LAMBDA(). Я предполагаю, что у вас есть хотя бы базовые навыки работы с Excel. Как только вы научитесь использовать функции LAMBDA, ожидайте, что вы будете часто использовать их в своих электронных таблицах Excel.

СМ.: Политика установки программного обеспечения (TechRepublic Premium)

Я использую Microsoft 365 в 64-разрядной системе Windows 10. Функция Excel LAMBDA() доступна только в Microsoft 365 и Excel для Интернета. Я предполагаю, что у вас есть базовые навыки работы с Excel. Для вашего удобства вы можете скачать демонстрационный файл в формате .xlsx. В этой статье предполагается, что у вас есть базовые навыки работы с Excel, но вы должны быть в состоянии следовать инструкциям для достижения успеха.

Что такое функция LAMBDA()?

Обязательная к прочтению информация о Windows

  • Виндовс 11 22х3 уже здесь

  • Контрольный список: защита систем Windows 10

  • Как очистить кеш DNS в Windows 10

  • Защитите свою сеть с помощью внешних служб безопасности Майкрософт

Функция Excel LAMBDA() аналогична определяемой пользователем функции VBA, но без VBA. Короче говоря, функция LAMBDA() в Excel позволяет создавать собственные и повторно используемые функции и давать им осмысленные имена, используя форму:

LAMBDA([параметр1, параметр2, …,] вычисление)

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

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

Прежде чем мы продолжим, есть несколько правил, которые вы должны соблюдать при создании функции LAMBDA():

  • LAMBDA() поддерживает 253 параметра, что должно быть достаточно для большинства пользователей.
  • LAMBDA() соответствует соглашениям об именах и именах параметров Excel, за одним исключением: нельзя использовать символ точки (.).
  • Как и другие функции, LAMBDA() при необходимости возвращает значение ошибки.

Как создать функцию LAMBDA() в Excel

Создать функцию LAMBDA() в Excel довольно просто. Используя LAMBDA(), вы вводите параметры и аргументы расчета, используя переменные. Используя функцию Excel «Определить имена», вы называете функцию и вводите функцию LAMBDA(), и все.

Самый простой способ понять, что функции Excel LAMBDA() могут сделать для вас, — это начать с простого. Например, Excel предлагает функцию СУММ(), но не функцию ВЫЧИТАТЬ(). Вы по-прежнему можете вычитать, но для начала это простой расчет:

  1. Введите тестовый расчет =B3-C3 в любую ячейку вне таблицы. Если он возвращает ожидаемые результаты, продолжайте. Если нет, продолжайте работать над расчетом, пока он не станет правильным.
  2. Перейдите на вкладку «Формулы» и нажмите «Определить имя».
  3. В появившемся диалоговом окне введите SUBTRACTYL в поле Name. Суффикс L идентифицирует функцию как функцию LAMBDA(), но вы можете использовать любое соглашение, которое вам нравится.
  4. Пока не изменяйте параметр Scope, но вы можете ограничить LAMBDA() листом, а не рабочей книгой.
  5. Введите комментарий, описывающий функцию LAMBDA(), например Вычитает два числа .
  6. В элементе управления Относится к введите функцию LAMBDA(), =LAMBDA(a,b,a-b) ( Рисунок A ).
  7. Нажмите кнопку ОК.

Рисунок А

Создайте LAMBDA(), дав ей имя.

Переменные a и b сначала идентифицируют оцениваемые значения, а a-b — вычисление. Поскольку переменных всего две, функция SUBTRACTL оценивает только два значения или два диапазона.

Теперь вы готовы использовать функцию LAMBDA() SUBTRACTL().

ПОСМОТРЕТЬ: Язык программирования Python: это обучение положит начало вашей карьере программиста (Академия TechRepublic)

Как вызвать функцию Excel LAMBDA()

Вы будете использовать функции LAMBDA() так же, как и функции Excel. Чтобы продемонстрировать, введите функцию и обратитесь к значениям, показанным в Рисунок B , = SUBTRACTL(B3,C3) . (Точка является грамматической и не является частью функции.) B3 и C3 удовлетворяют переменным a и b соответственно. При вычислении b вычитается из a в порядке, указанном на уровне функции.

Рисунок В

Используйте функцию ВЫЧИТАТЬ() для вычитания значений.

При работе с новой функцией LAMBDA() вы можете проверить ее, вставив исходную формулу. В данном случае это =B3-C3. Как видно из Рисунок C , проверочное выражение и LAMBDA() возвращают одинаковые результаты. Несмотря на то, что вы проверили вычисление до того, как создали SUBTRACTL(), рекомендуется проверить его еще раз. Если вы используете объект Table (как и я), Excel использует структурированные ссылки =[@Value1]-[@Value2].

Рисунок С

Проверка новой функции LAMBDA() — хорошая идея.

Стоит отметить, что вы всегда можете явно передать значения. Например, функция SUBTRACTL(182,138) возвращает 44.

Поскольку функция LAMBDA() в Excel использует язык формул Excel, ее поведение предсказуемо. Например, когда вы начинаете вводить функцию, вводя всего несколько символов, SUBTRACTL() появляется в списке автозаполнения, как показано на рис. D . Обратите внимание, что Excel также отображает описание, которое вы ввели при присвоении ему имени. Единственное, что он пока не может сделать, это показать аргументы, как это сделала бы встроенная функция.

Рисунок D

Excel реагирует на LAMBDA(), как и любая другая встроенная функция Excel.

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

Об ошибках Excel LAMBDA()

Функции LAMBDA() так же подвержены ошибкам, как и встроенные функции. Вы должны передать ожидаемые параметры, а логика вычислений должна быть правильной. В противном случае вы могли бы увидеть ошибки. Давайте посмотрим на некоторые из возможностей:

  • #ЗНАЧ!: Если вы видите это значение ошибки, проверьте переданные аргументы — вы передали неверный номер.
  • #ЧИСЛО!: Проверьте циклическую ссылку, если видите это значение ошибки.
  • #ИМЯ!: проверьте фактическое имя функции, которое вы ввели, на предмет опечатки.

Для большинства из нас #ЦЕННОСТЬ! Это наиболее вероятно, и его легко устранить. Теперь давайте рассмотрим более сложную LAMBDA().

SEE: Почему Microsoft Lists — это новый Excel (TechRepublic)

Как использовать Excel LAMBDA() для возврата верхних

n значений

Вы многому научились, и теперь пришло время использовать то, что вы узнали, для создания полезной функции LAMBDA(). Вычисление первых n значений в столбце — обычная задача, требующая некоторых специальных знаний. Раньше можно было использовать расширенный фильтр, выражение или сводную таблицу. Кроме того, вы можете использовать правило условного форматирования, чтобы выделить эти значения в источнике. В статье Как вернуть верхние или нижние n записей без фильтра или сводной таблицы в Excel не используется ни одна из них, и она опирается на функции массива Excel, СОРТИРОВКА() и ПОСЛЕДОВАТЕЛЬНОСТЬ().

Теперь мы решим эту проблему, используя функцию Excel LAMBDA(). Мы также будем использовать функцию массива SEQUENCE() в Excel в форме

.

=ЛЯМБДА(значения, n, НАИБОЛЬШИЙ(значения, ПОСЛЕДОВАТЕЛЬНОСТЬ(n)))

На рис. E показана вышеуказанная функция в G3. Вы можете сказать, что это функция массива, потому что результаты имеют синюю рамку. Давайте разберем его, чтобы вы могли увидеть, как это работает:

  • При входе в функцию выберите значения Value1 — не включая ячейку заголовка. Это удовлетворяет аргументу значений.
  • Введите 3, удовлетворяя аргументу n.
  • SEQUENCE(n) — это функция массива, которая определяет количество возвращаемых строк , но в данном случае это числа из Table1[Value1].
  • Функция НАИБОЛЬШИЙ() — это встроенная функция Excel, которая возвращает n -е наибольшее значение в диапазоне.

Рисунок Е

TOPnL() возвращает первые n значений в диапазоне чисел.

LAMBDA() передает ссылку на числа, которые вы хотите оценить (значения), и число, которое вы хотите, чтобы массив возвращал (n). Вычислительная часть LARGE(значения, ПОСЛЕДОВАТЕЛЬНОСТЬ(n)) выполняет свою работу, но LAMBDA() упрощает ее использование. Это одна из замечательных особенностей функций LAMBDA() — пользователям не нужны специальные знания, чтобы выполнять свою работу.

Теперь, когда вы знаете, как это работает, давайте создадим его:

  1. Перейдите на вкладку «Формулы» и нажмите «Определить имя».
  2. В появившемся диалоговом окне введите TOPnL в поле Имя. Суффикс L идентифицирует функцию как функцию LAMBDA(), но вы можете использовать любое соглашение, которое вам нравится.
  3. Пока не изменяйте параметр Scope, но вы можете ограничить LAMBDA() листом.
  4. Введите комментарий, описывающий функцию LAMBDA(), например, Возвращает первые n значения как LAMBDA() .
  5. В элементе управления Относится к введите функцию LAMBDA(), =LAMBDA(значения, n, НАИБОЛЬШИЙ(значения, ПОСЛЕДОВАТЕЛЬНОСТЬ(n))) . ( Рисунок F ).
  6. Нажмите кнопку ОК.

Рисунок F

Назовите функцию LAMBDA(), чтобы вы могли использовать ее на уровне листа.

После ввода TOPnL() в G3 ( Рисунок E ), скопируйте его в h4 и посмотрите, что произойдет. Ссылка является относительной, поэтому вы получаете второй массив из трех верхних значений в Value2, как вы можете видеть на рисунке 9.0017 Рисунок G .

Рисунок G

Копировать ТОПнЛ; это будет работать как большинство встроенных функций!

Дополнительные сведения о возврате верхних значений n см. в следующих статьях TechRepublic:

  • Как выделить первые n значений на листе Microsoft Excel
  • Как отобразить первые n записей в сводной таблице Excel с помощью встроенных параметров

Сьюзан Харкинс

Опубликовано:
903:50
11 мая 2022 г.

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