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

Обновлено: 05.07.2024

поиск меню

Урок 10: Работа с основными функциями

Работа с основными функциями

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

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

Основные функции

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

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

Вы можете загрузить этот пример для дополнительной практики.

Части функции

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

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

Работа с аргументами

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

Двоеточия создают ссылку на диапазон ячеек.

Чтобы создать базовую функцию в Excel:

1. Выберите ячейку, в которой появится ответ (например, F15).
2. Введите знак равенства (=), затем введите имя функции (например, SUM).

Создание функции СУММ

Добавление ячеек в аргумент функции

Результат

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

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

Команда "Автосумма" позволяет автоматически возвращать результаты для диапазона ячеек для стандартных функций, таких как СУММ и СРЗНАЧ.

1. Выберите ячейку, в которой появится ответ (например, E24).
2. Перейдите на вкладку "Главная".
3. В группе "Редактирование" нажмите стрелку раскрывающегося списка "Автосумма" и выберите нужную функцию (например, "Среднее").

Команда автосуммирования

Автосумма выбирает и отображает диапазон ячеек

Результат

Команда "Автосумма" также доступна на вкладке "Формулы".

Вы также можете использовать сочетание клавиш Alt+= вместо команды "Автосумма". Чтобы использовать это сочетание клавиш, удерживайте нажатой клавишу Alt, а затем нажмите знак равенства.

Посмотрите видео ниже, чтобы увидеть этот ярлык в действии.

Библиотека функций

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

Отличным местом для изучения функций является библиотека функций на вкладке "Формулы". Здесь вы можете искать и выбирать функции Excel на основе таких категорий, как «Финансы», «Логика», «Текст» и «Дата и время». Нажмите кнопки в интерактивном ниже, чтобы узнать больше.

Дата и время

Категория "Дата и время" содержит функции для работы с датами и временем и возвращает такие результаты, как текущая дата и время (СЕЙЧАС) или секунды (СЕКУНДЫ).

Автосумма

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

Недавно использованные

Используйте команду "Недавно использованные" для доступа к функциям, с которыми вы недавно работали.

Финансовый

Категория «Финансы» содержит функции для финансовых расчетов, таких как определение платежа (PMT) или процентной ставки по кредиту (RATE).

Дополнительные функции

Дополнительные функции содержат дополнительные функции в категориях "Статистические", "Инженерные", "Куб", "Информация" и "Совместимость".

Математика и триггеры

Категория Math & Trig включает функции для числовых аргументов. Например, можно округлить значения (ОКРУГЛ), найти число Пи (ПИ), умножить (ПРОИЗВЕД) и получить промежуточный итог (ПРОМЕЖУТОЧНЫЙ ИТОГ).

Поиск и ссылки

Категория «Поиск и ссылки» содержит функции, которые будут возвращать результаты для поиска и ссылки. Например, вы можете добавить гиперссылку (HYPERLINK) в ячейку или вернуть значение пересечения определенной строки и столбца (INDEX).

Категория "Текст" содержит функции, которые работают с текстом в аргументах, используя такие задачи, как преобразование текста в нижний регистр (НИЖНИЙ) или замена текста (ЗАМЕНИТЬ).

Логический

Функции в категории Логические проверяют аргументы на наличие значения или условия. Например, если стоимость заказа превышает 50 долларов США, добавьте 4,99 доллара США за доставку, а если сумма превышает 100 долларов США, за доставку плата не взимается (IF).

Вставить функцию

Команда "Вставить функцию" позволяет легко найти команду, введя описание того, что вы ищете.

Чтобы вставить функцию из библиотеки функций:

1. Выберите ячейку, в которой появится ответ (например, I6).
2. Перейдите на вкладку "Формулы".
3. В группе «Библиотека функций» выберите нужную категорию функций. В этом примере мы выберем дату и время.
4. Выберите нужную функцию в раскрывающемся меню «Дата и время». Мы выберем функцию ЧИСТРАБДНИ для подсчета дней между датой заказа и датой получения на нашем листе.

Категория даты и времени библиотеки функций

Выбор ячейки для поля Start_date

Выбор ячейки для поля End_date

Результат

Команда "Вставить функцию"

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

Использование команды "Вставить функцию"

В этом примере мы хотим найти функцию, которая будет подсчитывать общее количество расходных материалов, перечисленных в журнале заказов канцелярских товаров. Базовая функция COUNT считает только ячейки с числами; мы хотим подсчитать ячейки в столбце «Канцелярские товары», в котором используется текст. Поэтому нам нужно найти формулу, которая подсчитывает ячейки с текстом.

1. Выберите ячейку, в которой появится ответ (например, A27).
2. Перейдите на вкладку "Формулы", затем выберите команду "Вставить функцию".

Команда "Вставить функцию"

Поиск функции

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

Выбор диапазона ячеек для поля Value1

Выбор диапазона ячеек для поля Value2

Результат

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

Узнайте, как создавать формулы и использовать встроенные функции для выполнения вычислений и решения задач.

Важно! Расчетные результаты формул и некоторых функций листа Excel могут незначительно отличаться на ПК с Windows, использующем архитектуру x86 или x86-64, и ПК с Windows RT, использующем архитектуру ARM. Узнайте больше о различиях.

Важно! В этой статье мы обсуждаем XLOOKUP и VLOOKUP, которые похожи. Попробуйте использовать новую функцию XLOOKUP, улучшенную версию функции VLOOKUP, которая работает в любом направлении и возвращает точные совпадения по умолчанию, что делает ее более простой и удобной в использовании, чем ее предшественница.

Создайте формулу, которая ссылается на значения в других ячейках

Введите знак равенства =.

Примечание. Формулы в Excel всегда начинаются со знака равенства.

Выберите ячейку или введите ее адрес в выбранной ячейке.

Введите оператора. Например, — для вычитания.

Выберите следующую ячейку или введите ее адрес в выбранной ячейке.

Нажмите Enter. Результат расчета появится в ячейке с формулой.

Посмотреть формулу

Когда формула вводится в ячейку, она также отображается в строке формул.

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

Введите формулу, содержащую встроенную функцию

Выберите пустую ячейку.

Введите знак равенства =, а затем введите функцию. Например, =СУММ для получения общего объема продаж.

Введите открывающую скобку (.

Выделите диапазон ячеек и введите закрывающую скобку).

Нажмите Enter, чтобы получить результат.

Загрузите учебное пособие по формулам

Мы подготовили книгу "Начало работы с формулами", которую вы можете скачать. Если вы новичок в Excel или даже имеете некоторый опыт работы с ним, в этом туре вы можете ознакомиться с наиболее распространенными формулами Excel. С реальными примерами и полезными визуальными эффектами вы сможете суммировать, считать, усреднять и выполнять ВПР как профессионал.

Подробнее о формулах

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

Формула также может содержать некоторые или все из следующих элементов: функции, ссылки, операторы и константы.

Части формулы

<р>1. Функции: функция PI() возвращает значение числа пи: 3,142.

<р>2. Ссылки: A2 возвращает значение в ячейке A2.

<р>3. Константы: числа или текстовые значения, введенные непосредственно в формулу, например 2.

<р>4. Операторы: оператор ^ (вставка) возводит число в степень, а оператор * (звездочка) умножает числа.

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

Стили ссылок A1

По умолчанию Excel использует стиль ссылок A1, который ссылается на столбцы буквами (от A до XFD, всего 16 384 столбца) и ссылается на строки с цифрами (от 1 до 1 048 576). Эти буквы и цифры называются заголовками строк и столбцов. Чтобы сослаться на ячейку, введите букву столбца, а затем номер строки. Например, B2 относится к ячейке на пересечении столбца B и строки 2.

Для ссылки

Ячейка в столбце A и строке 10

Диапазон ячеек в столбце А и строках с 10 по 20

Диапазон ячеек в строке 15 и столбцах с B по E

Все ячейки в строке 5

Все ячейки в строках с 5 по 10

Все ячейки в столбце H

Все ячейки в столбцах с H по J

Диапазон ячеек в столбцах от A до E и строках с 10 по 20

Создание ссылки на ячейку или диапазон ячеек на другом листе той же книги

В следующем примере функция СРЗНАЧ вычисляет среднее значение для диапазона B1:B10 на листе Marketing в той же книге.

<р>1. Относится к рабочему листу под названием "Маркетинг"

<р>2. Относится к диапазону ячеек от B1 до B10

<р>3. Восклицательный знак (!) отделяет ссылку на рабочий лист от ссылки на диапазон ячеек

Примечание. Если на указанном листе есть пробелы или числа, необходимо добавить апострофы (') до и после имени листа, например ='123'!A1 или ='Доход за январь'!A1.

Разница между абсолютными, относительными и смешанными ссылками

Относительные ссылки Относительная ссылка на ячейку в формуле, например A1, основана на относительном положении ячейки, содержащей формулу, и ячейки, на которую ссылается ссылка. Если положение ячейки, содержащей формулу, изменяется, ссылка изменяется. Если вы скопируете или заполните формулу между строками или столбцами, ссылка будет автоматически скорректирована. По умолчанию в новых формулах используются относительные ссылки. Например, если вы скопируете или заполните относительную ссылку из ячейки B2 в ячейку B3, она автоматически изменится с =A1 на =A2.

Скопирована формула с относительной ссылкой

Абсолютные ссылки Абсолютная ссылка на ячейку в формуле, например $A$1, всегда указывает на ячейку в определенном месте. Если положение ячейки, содержащей формулу, изменяется, абсолютная ссылка остается прежней. Если вы скопируете или заполните формулу между строками или столбцами, абсолютная ссылка не изменится. По умолчанию в новых формулах используются относительные ссылки, поэтому вам может потребоваться переключить их на абсолютные ссылки. Например, если вы скопируете или заполните абсолютную ссылку из ячейки B2 в ячейку B3, она останется одинаковой в обеих ячейках: =$A$1.

Скопирована формула с абсолютной ссылкой

Смешанные ссылки Смешанная ссылка имеет либо абсолютный столбец и относительную строку, либо абсолютную строку и относительный столбец. Абсолютная ссылка на столбец имеет вид $A1, $B1 и т. д. Абсолютная ссылка на строку принимает форму A$1, B$1 и т. д. Если положение ячейки, содержащей формулу, изменяется, относительная ссылка изменяется, а абсолютная ссылка не изменяется.Если вы копируете или заполняете формулу по строкам или столбцам, относительная ссылка корректируется автоматически, а абсолютная ссылка не корректируется. Например, если вы скопируете или заполните смешанную ссылку из ячейки A2 в ячейку B3, она изменится с =A$1 на =B$1.

Скопирована формула со смешанной ссылкой

Трехмерный эталонный стиль

Удобные ссылки на несколько листов Если вы хотите анализировать данные в одной и той же ячейке или диапазоне ячеек на нескольких листах в книге, используйте трехмерную ссылку. Трехмерная ссылка включает в себя ссылку на ячейку или диапазон, которому предшествует диапазон имен рабочих листов. Excel использует все рабочие листы, хранящиеся между начальным и конечным именами ссылки. Например, =СУММ(Лист2:Лист13!B5) суммирует все значения, содержащиеся в ячейке B5, на всех листах между листами 2 и 13 включительно.

Трехмерные ссылки можно использовать для ссылки на ячейки на других листах, для определения имен и создания формул с помощью следующих функций: СУММ, СРЗНАЧ, СРЗНАЧ, СЧЕТ, СЧЕТ, МАКС, МАКС, МИН, МИН, PRODUCT, STDEV.P, STDEV.S, STDEVA, STDEVPA, VAR.P, VAR.S, VARA и VARPA.

Объемные ссылки нельзя использовать в формулах массива.

Трехмерные ссылки нельзя использовать с оператором пересечения (один пробел) или в формулах, использующих неявное пересечение.

Что происходит при перемещении, копировании, вставке или удалении листов В следующих примерах показано, что происходит при перемещении, копировании, вставке или удалении листов, включенных в трехмерную ссылку. В примерах используется формула =СУММ(Лист2:Лист6!A2:A5) для добавления ячеек с A2 по A5 на листах со 2 по 6.

Вставка или копирование Если вы вставляете или копируете листы между Листами2 и Лист6 (конечными точками в этом примере), Excel включает в расчеты все значения в ячейках с A2 по A5 из добавленных листов.

Удалить Если вы удалите листы между Листами2 и Лист6, Excel удалит их значения из расчета.

Переместить. Если вы перемещаете листы между Листами2 и Лист6 за пределы указанного диапазона листов, Excel удаляет их значения из расчета.

Перемещение конечной точки. Если вы перемещаете Лист2 или Лист6 в другое место в той же книге, Excel корректирует расчет, чтобы учесть новый диапазон листов между ними.

Удалить конечную точку. Если вы удаляете Лист2 или Лист6, Excel корректирует расчет, чтобы учесть диапазон листов между ними.

Стиль ссылок R1C1

Вы также можете использовать справочный стиль, в котором и строки, и столбцы на листе пронумерованы. Справочный стиль R1C1 полезен для вычисления позиций строк и столбцов в макросах. В стиле R1C1 Excel указывает расположение ячейки буквой "R", за которой следует номер строки, и буквой "C", за которой следует номер столбца.

Относительная ссылка на ячейку двумя строками выше и в том же столбце

Относительная ссылка на ячейку на две строки вниз и на два столбца вправо

Абсолютная ссылка на ячейку во второй строке и во втором столбце

Относительная ссылка на всю строку над активной ячейкой

Абсолютная ссылка на текущую строку

При записи макроса Excel записывает некоторые команды, используя стиль ссылок R1C1. Например, если вы записываете команду, такую ​​как нажатие кнопки "Автосумма", чтобы вставить формулу, которая добавляет диапазон ячеек, Excel записывает формулу, используя стиль R1C1, а не стиль A1, ссылки.

Вы можете включить или отключить стиль ссылки R1C1, установив или сняв флажок Стиль ссылки R1C1 в разделе Работа с формулами в категории Формулы диалогового окна Параметры. Чтобы отобразить это диалоговое окно, щелкните вкладку "Файл".

Нужна дополнительная помощь?

Вы всегда можете обратиться к эксперту в техническом сообществе Excel или получить поддержку в сообществе ответов.

Формула – это выражение, которое вычисляет значение ячейки. Функции представляют собой предопределенные формулы и уже доступны в Excel.

Например, ячейка A3 ниже содержит формулу, которая складывает значение ячейки A2 со значением ячейки A1.

Например, ячейка A3 ниже содержит функцию СУММ, которая вычисляет сумму диапазона A1:A2.

Введите формулу

Чтобы ввести формулу, выполните следующие действия.

<р>1. Выберите ячейку.

<р>2. Чтобы Excel знал, что вы хотите ввести формулу, введите знак равенства (=).

<р>3. Например, введите формулу A1+A2.

Совет. Вместо ввода A1 и A2 просто выберите ячейку A1 и ячейку A2.

<р>4. Измените значение ячейки A1 на 3.

Excel автоматически пересчитывает значение ячейки A3. Это одна из самых мощных функций Excel!

Редактировать формулу

При выборе ячейки Excel показывает значение или формулу ячейки в строке формул.

<р>1. Чтобы изменить формулу, нажмите в строке формул и измените формулу.

Приоритет оператора

Excel использует порядок вычислений по умолчанию. Если часть формулы заключена в круглые скобки, эта часть будет вычислена первой. Затем он выполняет вычисления умножения или деления. Как только это будет завершено, Excel добавит и вычтет остаток вашей формулы. См. пример ниже.

Сначала Excel выполняет умножение (A1 * A2). Затем Excel добавляет к этому результату значение ячейки A3.

Сначала Excel вычисляет часть в скобках (A2+A3). Затем он умножает этот результат на значение ячейки A1.

Копировать/вставить формулу

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

<р>1. Введите приведенную ниже формулу в ячейку A4.

2а. Выберите ячейку A4, щелкните правой кнопкой мыши и выберите "Копировать" (или нажмите CTRL + C).

<р>. затем выберите ячейку B4, щелкните правой кнопкой мыши и выберите "Вставить" в разделе "Параметры вставки:" (или нажмите CTRL + v).

2б. Вы также можете перетащить формулу в ячейку B4. Выберите ячейку A4, щелкните в правом нижнем углу ячейки A4 и перетащите ее в ячейку B4. Это намного проще и дает точно такой же результат!

Результат. Формула в ячейке B4 ссылается на значения в столбце B.

Вставить функцию

Все функции имеют одинаковую структуру. Например, СУММ(A1:A4). Имя этой функции SUM. Часть в скобках (аргументы) означает, что мы даем Excel диапазон A1: A4 в качестве входных данных. Эта функция складывает значения в ячейках A1, A2, A3 и A4. Нелегко запомнить, какую функцию и какие аргументы использовать для каждой задачи. К счастью, функция «Вставить функцию» в Excel поможет вам в этом.

Чтобы вставить функцию, выполните следующие шаги.

<р>1. Выберите ячейку.

<р>2. Нажмите кнопку "Вставить функцию".

Появится диалоговое окно "Вставить функцию".

<р>3. Найдите функцию или выберите функцию из категории. Например, выберите СЧЁТЕСЛИ из категории Статистические данные.

Откроется диалоговое окно "Аргументы функции".

<р>5. Щелкните поле "Диапазон" и выберите диапазон A1:C2.

<р>6. Нажмите в поле Критерии и введите >5.

Результат. Функция СЧЁТЕСЛИ подсчитывает количество ячеек больше 5.

Примечание: вместо использования функции "Вставить функцию" просто введите =СЧЁТЕСЛИ(A1:C2,">5"). Когда вы дойдете до: =СЧЁТЕСЛИ( вместо ввода A1:C2, просто выберите диапазон A1:C2.

Excel позволяет создавать пользовательские функции с помощью VBA, называемые «Определяемые пользователем функции» (UDF), которые можно использовать так же, как SUM() или другие встроенные функции Excel. Они могут быть особенно полезны для продвинутой математики или специальных операций с текстом или вычисления дат до 1900 года. Многие надстройки Excel предоставляют большие наборы специализированных функций.

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

Эта страница (содержание):

ПРИМЕЧАНИЕ. Новая функция LAMBDA, доступная в разделе "Production: Current Channel builds of Excel", изменит способ использования пользовательских функций в Excel без VBA.

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

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

1. Откройте новую книгу Excel.
2. Войдите в VBA (нажмите Alt+F11)
3. Вставьте новый модуль (Вставить > Модуль)
4. Скопируйте и вставьте примеры пользовательских функций Excel
5. Выйти из VBA (нажмите Alt+Q)
6. Использование функций. Они появятся в диалоговом окне "Вставить функцию" (Shift+F3) в категории "Определено пользователем".

Если вы хотите использовать пользовательскую функцию более чем в одной рабочей книге, вы можете сохранить свои функции в рабочей книге personal.xlsb или в собственной пользовательской надстройке. Чтобы создать надстройку, сохраните файл Excel, содержащий функции VBA, как файл надстройки (.xla для Excel 2003 или .xlam для Excel 2007+). Затем загрузите надстройку (Инструменты > Надстройки для Excel 2003 или Разработчик > Надстройки Excel для Excel 2010+).

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

Преимущества пользовательских функций Excel

• Создайте сложную или пользовательскую математическую функцию.
• Упрощение формул, которые в противном случае были бы очень длинными "мегаформулами".
• Диагностика, например проверка форматов ячеек.
• Пользовательские операции с текстом.
• Расширенные формулы массивов и матричные функции.
• Вычисление даты до 1900 года с использованием встроенных функций даты VBA.

Ограничения пользовательских функций

• Невозможно "записать" пользовательскую функцию Excel, как макрос Excel.
• Более ограничены, чем обычные макросы VBA. Пользовательские функции не могут изменить структуру или формат рабочего листа или ячейки.
• Если вы вызываете другую функцию или макрос из пользовательской функции, на другой макрос распространяются те же ограничения, что и на пользовательскую функцию.
• Невозможно поместить значение в ячейку, отличную от ячейки (или диапазона), содержащей формулу. Другими словами, пользовательские функции предназначены для использования в качестве «формул», а не обязательно «макросов».
• Определенные пользователем функции Excel в VBA обычно намного медленнее, чем функции, скомпилированные в C++ или FORTRAN.
• Часто трудно отследить ошибки.
• Если вы создаете надстройку, содержащую ваши пользовательские функции, вы можете забыть, что использовали пользовательскую функцию, что сделает файл менее доступным для совместного использования.
• Добавление пользовательских функций в вашу книгу активирует флажок "Макрос" (проблема безопасности: "Инструменты" > "Макросы" > "Безопасность").

Примеры пользовательских функций

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

Следующий пример может быть полезен при извлечении гиперссылок из таблиц ссылок, скопированных в Excel, при постобработке веб-запросов Excel или при получении адреса электронной почты из списка гиперссылок mailto:.

Эта функция также является примером использования необязательного аргумента пользовательской функции Excel. Синтаксис этой пользовательской функции Excel:

Чтобы увидеть пример работы с необязательными аргументами, найдите команду IsMissing в файлах справки Excel VBA (F1).

В этом примере показано, как воспользоваться преимуществами некоторых функций, доступных в VBA, для выполнения некоторых манипуляций с текстом. Что, если у вас есть куча телефонных номеров в следующем формате: 1-800-999-9999, и вы хотите получить только трехзначный префикс?

Эта UDF принимает в качестве аргументов текстовую строку, номер элемента, который вы хотите захватить (n), и разделитель в виде строки (например, "-"). Синтаксис для этого примера определяемой пользователем функции в Excel:

Пример. Если ячейка B3 содержит "1-800-333-4444", а ячейка C3 содержит формулу =GetElement(B3,3,"-"), тогда C3 будет равна "333". Чтобы превратить "333" в число, вы должны использовать =ЗНАЧ(GetElement(B3,3,"-")).

Следующая функция основана на встроенной визуальной базовой функции MonthName() и возвращает полное название месяца с учетом номера месяца. Если второй аргумент равен TRUE, будет возвращено сокращение.

Пример: =VBA_MonthName(3) вернет «март», а =VBA_MonthName(3,TRUE) вернет «март».

Функция Excel РАЗНДАТ(дата_начала,дата_окончания,"y") – это очень простой способ рассчитать возраст человека, если даты датируются после 1 января 1900 года. Функции даты VBA, такие как Year(), Month(), Day(), DateSerial(), DateValue(), могут обрабатывать все даты по григорианскому календарю, поэтому пользовательские функции, основанные на функциях VBA, могут позволить вам работать с датами до 1900. Приведенная ниже функция работает как DATEDIF(start_date,end_date,"y"), если end_date >= start_date.

Пример: =AgeInYears("10-Oct-1850","5-Jan-1910") возвращает значение 59.

Другие примеры пользовательских функций Excel

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

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

Комментарии

здравствуйте,
Я создал пользовательскую формулу, но когда я делюсь файлом со своими коллегами, он всегда возвращает ошибку, когда они открывают его, даже если у них также установлена ​​​​пользовательская формула в их Excel.
Как я могу это решить? Ссылка на расположение формулы постоянно меняется

Хмммм… Если вся пользовательская формула включена в код VBA файла, которым вы делитесь, то им нужно будет только включить макросы после открытия файла. Если формула является частью надстройки, необходимо активировать надстройку. Таким образом, код VBA должен находиться либо в файле, которым вы делитесь, либо в надстройке, которую они также активировали, либо в их собственной книге personal.xlsb.

можно ли использовать ФОРМУЛУ внутри UDF
(например)
Функция Age(AgeingDate) As Date
Age= "=today()" – AgeingDate
Завершить функцию.

Мне нужно изменить дату, просто щелкнув столбец даты закрытия.

я попробовал новую функцию... не могли бы вы мне помочь

Функция Решение(число)
Если (Решение > 0,4) То Решение = сохранить
Если (Решение > 0,2) Тогда Решение = пересмотреть
Иначе:
Решение() = отклонить
Конец, если
Завершить функцию

Функция Решение(число)
Если (число > 0,4) Тогда
Решение = «сохранить»
ИначеЕсли (число > 0,2) Тогда
Решение = «пересмотреть»
Иначе: Решение = «отклонить»
Конец, если
Завершить функцию

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

Функция CJEXTRTHK(plate_profile As Variant) как строка

CJEXTRTHK = IfError(Left(plate_profile, Search(“X”, plate_profile) – 1), “”)

Формула, из которой я делаю функцию, состоит в том, чтобы взять профиль стального листа, такой как PL3/8″X5 3/16″, и дать мне PL3/8″ (любой до X). Когда я пытаюсь использовать функцию, она продолжает выдавать ошибку «Подпрограмма, функция или свойство не определены: ошибка 35». Я перепроверил эту формулу и не могу понять, что в ней не так.

Создать уникальное значение против дубликатов для vlookup

Пример ,
A B C D
1 Xx-100 Xx 100

Я пишу формулу ниже в ячейке a1.

<р>1. ХХ-100-1
2. ХХ-100-2

Но я хочу написать ниже udf
= MyUnque(b1)
Вместо =b1&”-“&countif($b$1:b1,b1)

@Tomal … Ваша формула, вероятно, будет работать быстрее и эффективнее, чем пользовательская пользовательская функция. Однако вы можете получить предложение от консультанта по Excel, который поможет вам.

пожалуйста, напишите коды vba формулы –if(and(date1=date2,(time_sec1+duration)>time_sec2),"con","")

Я пытаюсь создать функцию, которая суммирует все числа в столбце рядом с чьим-то именем. То есть, если «Джон Доу» находится в столбце «имя» в строках 3, 7, 18 и 25, я суммирую значения столбца времени в строках 3, 7, 18 и 25. Когда я использую это код в макросе для пробного прогона с ручным вводом «repname», работает отлично, но этот код всегда выводит ноль. Где я ошибаюсь?

Function RepTime(repname As String) As Integer

Тусклый repminutes как целое число

Dim repminutesarry() как целое
ReDim repminutesarray(1 для размера массива) как целое число

Для i = 2 На звонки по номеру

j = i – 1
Если repname = Cells(i, 4).Value Then repminutesarray(j) = Cells(i, 3).Value

@Josh… Я бы использовал либо СУММЕСЛИ, либо СУММЕСЛИМН вместо пользовательской функции.

Я пытаюсь выяснить, возможно ли создать формулу или код, чтобы я мог применить ко всем строкам, когда я набираю число 1 для автоматического преобразования в 14 %, 2 в 28 %… до 7 в 100 %?

Я предполагаю, что вам не нужно более очевидное решение деления на 7. Итак, если вы хотите, чтобы введенное вами значение изменялось в ячейке, в которую вы его вводите, у вас есть два варианта: (а) форматирование числа или (б) вба. Я не знаю числового формата для преобразования чисел 1-7 в 14%-100%. Для решения VBA вам нужно будет получить предложение от консультанта, если вы хотите, чтобы что-то было создано для вас. Существует третий вариант, который не совсем то, что вы просили, но вы можете использовать проверку данных для создания раскрывающегося списка, чтобы вы могли выбрать 14%, 28%, … 100% из раскрывающегося списка.

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

  
• Форматирование даты в Excel
  
• Как перейти к последней строке в Excel
  
• Как удалить аккаунт Google из списка аккаунтов в браузере
  
• Какие браузеры поддерживают единство
  
• Как установить программу без прав администратора windows 7