Excel добавление своей формулы - Word и Excel - помощь в работе с программами

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Еще…Меньше

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

Пользовательские функции (как и макросы) записываются на языке программирования Visual Basic для приложений (VBA). Они отличаются от макросов двумя вещами. Во-первых, в них используются процедуры Function, а не Sub. Это значит, что они начинаются с оператора Function, а не Sub, и заканчиваются оператором End Function, а не End Sub. Во-вторых, они выполняют различные вычисления, а не действия. Некоторые операторы (например, предназначенные для выбора и форматирования диапазонов) исключаются из пользовательских функций. Из этой статьи вы узнаете, как создавать и применять пользовательские функции. Для создания функций и макросов используется редактор Visual Basic (VBE), который открывается в отдельном окне.

Предположим, что ваша компания предоставляет скидку в размере 10 % клиентам, заказавшим более 100 единиц товара. Ниже мы объясним, как создать функцию для расчета такой скидки.

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

Чтобы создать пользовательскую функцию DISCOUNT в этой книге, сделайте следующее:

Нажмите клавиши ALT+F11 (или FN+ALT+F11 на Mac), чтобы открыть редактор Visual Basic, а затем щелкните Insert (Вставка) > Module (Модуль). В правой части редактора Visual Basic появится окно нового модуля.

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


Function DISCOUNT(quantity, price)
   If quantity >=100 Then
     DISCOUNT = quantity * price * 0.1
   Else
     DISCOUNT = 0
   End If
 
 DISCOUNT = Application.Round(Discount, 2)
End Function

Примечание: Чтобы код было более удобно читать, можно добавлять отступы строк с помощью клавиши TAB. Отступы необязательны и не влияют на выполнение кода. Если добавить отступ, редактор Visual Basic автоматически вставит его и для следующей строки. Чтобы сдвинуть строку на один знак табуляции влево, нажмите SHIFT+TAB.

Теперь вы готовы использовать новую функцию DISCOUNT. Закройте редактор Visual Basic, выделите ячейку G7 и введите следующий код:

=DISCOUNT(D7;E7)

Excel вычислит 10%-ю скидку для 200 единиц по цене 47,50 ₽ и вернет 950,00 ₽.

В первой строке кода VBA функция DISCOUNT(quantity, price) указывает, что функции DISCOUNT требуется два аргумента: quantity (количество) и price (цена). При вызове функции в ячейке листа необходимо указать эти два аргумента. В формуле =DISCOUNT(D7;E7) аргумент quantity имеет значение D7, а аргумент price — значение E7. Если скопировать формулу в ячейки G8:G13, вы получите указанные ниже результаты.

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

Оператор If в следующем блоке кода проверяет аргумент quantity и сравнивает количество проданных товаров со значением 100:


If quantity >= 100 Then
 DISCOUNT = quantity * price * 0.1
Else
 DISCOUNT = 0
End If

Если количество проданных товаров не меньше 100, VBA выполняет следующую инструкцию, которая перемножает значения quantity и price, а затем умножает результат на 0,1:

Discount = quantity * price * 0.1

Результат хранится в виде переменной Discount. Оператор VBA, который хранит значение в переменной, называется оператором назначения, так как он вычисляет выражение справа от знака равенства и назначает результат имени переменной слева от него. Так как переменная Discount называется так же, как и процедура функции, значение, хранящееся в переменной, возвращается в формулу листа, из которой была вызвана функция DISCOUNT.

Если значение quantity меньше 100, VBA выполняет следующий оператор:

Discount = 0

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

Discount = Application.Round(Discount, 2)

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

Пользовательские функции должны начинаться с оператора Function и заканчиваться оператором End Function. Помимо названия функции, оператор Function обычно включает один или несколько аргументов. Однако вы можете создать функцию без аргументов. В Excel доступно несколько встроенных функций (например, СЛЧИС и ТДАТА), в которых нет аргументов.

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

Количество ключевых слов VBA, которые можно использовать в пользовательских функциях, меньше числа, используемого в макросах. Настраиваемые функции не могут выполнять другие задачи, кроме возврата значения в формулу на этом или в выражение, используемом в другом макросе или функции VBA. Например, пользовательские функции не могут изменять размер окна, редактировать формулу в ячейке, а также изменять шрифт, цвет или узор текста в ячейке. Если в процедуру функции включить такой код действия, функция возвращает #VALUE! ошибку «#ВЫЧИС!».

Единственное действие, которое может выполнять процедура функции (кроме вычислений), — это отображение диалогового окна. Чтобы получить значение от пользователя, выполняющего функцию, можно использовать в ней оператор InputBox. Кроме того, с помощью оператора MsgBox можно выводить сведения для пользователей. Вы также можете использовать настраиваемые диалоговые окна (UserForms), но эта тема выходит за рамки данной статьи.

Даже простые макросы и пользовательские функции может быть сложно понять. Чтобы сделать эту задачу проще, добавьте комментарии с пояснениями. Для этого нужно ввести перед текстом апостроф. Например, ниже показана функция DISCOUNT с комментариями. Благодаря подобным комментариями и вам, и другим будет впоследствии проще работать с кодом VBA. Так, код будет легче понять, если потребуется внести в него изменения.

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

Кроме того, рекомендуется присваивать макросам и пользовательским функциям описательные имена. Например, присвойте макросу название MonthLabels вместо Labels, чтобы более точно указать его назначение. Описательные имена макросов и пользовательских функций особенно полезны, если существует множество процедур с похожим назначением.

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

Для использования настраиваемой функции должна быть открыта книга, содержащая модуль, в котором она была создана. Если книга не открыта, вы получите #NAME? при попытке использования функции. Если вы ссылались на функцию в другой книге, ее имя должно предшествовать названию книги, в которой она находится. Например, при создании функции DISCOUNT в книге Personal.xlsb и вызове ее из другой книги необходимо ввести =personal.xlsb!discount(),а не просто =discount().

Чтобы вставить пользовательскую функцию быстрее (и избежать ошибок), ее можно выбрать в диалоговом окне «Вставка функции». Пользовательские функции доступны в категории «Определенные пользователем»:

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

Создав нужные функции, выберите Файл > Сохранить как.

В Excel 2007 нажмите кнопку Microsoft Office, а затем щелкните Сохранить как.
В диалоговом окне Сохранить как откройте раскрывающийся список Тип файла и выберите значение Надстройка Excel. Сохраните книгу с запоминающимся именем, таким как MyFunctions, в папке AddIns. Она будет автоматически предложена в диалоговом окне Сохранить как, поэтому вам потребуется только принять расположение, используемое по умолчанию.
Сохранив книгу, выберите Файл > Параметры Excel.

В Excel 2007 нажмите кнопку Microsoft Office и щелкните Параметры Excel.
В диалоговом окне Параметры Excel выберите категорию Надстройки.
В раскрывающемся списке Управление выберите Надстройки Excel. Затем нажмите кнопку Перейти.
В диалоговом окне Надстройки установите флажок рядом с именем книги, как показано ниже.

Создав нужные функции, выберите Файл > Сохранить как.
В диалоговом окне Сохранить как откройте раскрывающийся список Тип файла и выберите значение Надстройка Excel. Сохраните книгу с запоминающимся именем, таким как MyFunctions.
Сохранив книгу, выберите Сервис > Надстройки Excel.
В диалоговом окне Надстройки нажмите кнопку «Обзор», найдите свою надстройку, нажмите кнопку Открыть, а затем установите флажок рядом с надстройкой в поле Доступные надстройки.

После этого пользовательские функции будут доступны при каждом запуске Excel. Если вы хотите добавить его в библиотеку функций, вернимся в Visual Basic редактора. Если вы заглянуть в Visual Basic редактора Project проводника под заголовком VBAProject, вы увидите модуль с именем файла надстройки. У надстройки будет расширение XLAM.

Дважды щелкните модуль в Project Explorer, чтобы вывести код функций. Чтобы добавить новую функцию, установите точку вставки после оператора End Function, который завершает последнюю функцию в окне кода, и начните ввод. Вы можете создать любое количество функций, и они будут всегда доступны в категории «Определенные пользователем» диалогового окна Вставка функции.

Эта статья основана на главе книги Microsoft Office Excel 2007 Inside Out, написанной Марком Доджем (Mark Dodge) и Крейгом Стинсоном (Craig Stinson). В нее были добавлены сведения, относящиеся к более поздним версиям Excel.

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

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

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

Источник

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

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

Окно вставки функции

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

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Используем вкладку с формулами

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

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

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

Ручная вставка формулы в Excel

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

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

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

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

Вставка математических формул

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

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

На экране появится редактор и блок формулы. Его используйте для перемещения, а сам редактор – для того, чтобы заносить в формулу числа и редактировать ее под себя. Учитывайте, что в этом случае не работают никакие проверки, поэтому правильность написания проверять придется собственноручно.

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

Источник

Содержание

Способ 1: Кнопка «Вставка функции»
Способ 2: Вкладка «Формулы»
Способ 3: Ручное создание формулы
Способ 4: Вставка математической формулы
Вопросы и ответы

Способ 1: Кнопка «Вставка функции»

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

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

Затем переходите к инструменту «Вставка функции» путем клика по отведенной для этого кнопке на верхней панели.

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

Посмотрите на список в блоке ниже, чтобы выбрать там ту самую функцию.

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

Для получения развернутой информации от разработчиков понадобится нажать по выделенной надписи «Справка по этой функции».

Как только произойдет выбор функции, отобразится отдельное окно, где заполняются ее аргументы. За пример мы взяли формулу МАКС, показывающую максимальное значение из всего списка аргументов. Поэтому в качестве числа здесь задается перечень ячеек, входящих в диапазон для подсчета.

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

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

После нажатия по кнопке «ОК» или клавише Enter формула вставится в выделенную ранее ячейку с уже отобразившимся результатом. При нажатии по ней на верхней панели вы увидите синтаксическую запись формулы и по необходимости сможете ее отредактировать.

Способ 2: Вкладка «Формулы»

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

Перейдите на вкладку «Формулы» через верхнюю панель.

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

Потребуется выделить все ячейки, которые должны суммироваться, а затем нажать по строке «Автосумма».

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

Способ 3: Ручное создание формулы

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

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

Напишите знак «=» в поле ввода вверху или в самой ячейке, что и будет означать начало формулы.

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

Поставьте открывающую и закрывающую скобку, внутри которых и будут написаны условия.

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

Результат формулы отобразится после нажатия по клавише Enter.

Если используется несколько рядов или аргументов, ставьте знак «;», а затем вписывайте следующие значения, что и описано в отображающихся на экране подсказках.

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

Подробнее: Полезные функции в Microsoft Excel

Способ 4: Вставка математической формулы

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

Откройте вкладку «Вставка» и разверните раздел «Символы».

Начните создание формулы, щелкнув по кнопке «Уравнение».

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

По завершении можно перемещать формулу в любое место и изменять ее внешние параметры.

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

Подробнее: Проблемы с вычислением формул в Microsoft Excel

Еще статьи по данной теме:

Помогла ли Вам статья?

Источник

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

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

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

Открыть редактор языка VBA с помощью комбинации клавиш ALT+F11.
В открывшемся окне выбрать пункт Insert и подпункт Module, как показано на рисунке:

VBA.

Новый модуль будет создан автоматически, при этом в основной части окна редактора появится окно для ввода кода:

При необходимости можно изменить название модуля.
В отличие от макросов, код которых должен находиться между операторами Sub и End Sub, пользовательские функции обозначают операторами Function и End Function соответственно. В состав пользовательской функции входят название (произвольное имя, отражающее ее суть), список параметров (аргументов) с объявлением их типов, если они требуются (некоторые могут не принимать аргументов), тип возвращаемого значения, тело функции (код, отражающий логику ее работы), а также оператор End Function. Пример простой пользовательской функции, возвращающей названия дня недели в зависимости от указанного номера, представлен на рисунке ниже:

После ввода представленного выше кода необходимо нажать комбинацию клавиш Ctrl+S или специальный значок в левом верхнем углу редактора кода для сохранения.
Чтобы воспользоваться созданной функцией, необходимо вернуться к табличному редактору Excel, установить курсор в любую ячейку и ввести название пользовательской функции после символа «=»:

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

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

Создайте новый макрос (нажмите комбинацию клавиш Alt+F8), в появившемся окне введите произвольное название нового макроса, нажмите кнопку Создать:

В результате будет создан новый модуль с заготовкой, ограниченной операторами Sub и End Sub.

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

В качестве «Macro» должна быть передана текстовая строка с названием пользовательской функции, в качестве «Description» — переменная типа String с текстом описания возвращаемого значения, в качестве «ArgumentDescriptions» — массив переменных типа String с текстами описаний аргументов пользовательской функции.
Для создания описания пользовательской функции достаточно один раз выполнить созданный выше модуль. Теперь при вызове пользовательской функции (или SHIFT+F3) отображается описание возвращаемого результата и переменной:

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

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

Пример 1. Рассчитать сумму отпускных для каждого работника, проработавшего на предприятии не менее 12 месяцев, на основе суммы общей заработной платы и числа выходных дней в году.

Вид исходной таблицы данных:

Каждому работнику полагается 24 выходных дня с выплатой S=N*24/(365-n), где:

N – суммарная зарплата за год;
n – число праздничных дней в году.

Создадим пользовательскую функцию для расчета на основе данной формулы:

Код примера:

Public Function Otpusknye(summZp As Long, holidays As Long) As Long If IsNumeric(holidays) = False Or IsNumeric(summZp) = False Then Otpusknye = "Введены нечисловые данные" Exit Function ElseIf holidays <= 0 Or summZp <= 0 Then Otpusknye = "Отрицательное число или 0" Exit Function Else Otpusknye = summZp * 24 / (365 - holidays) End If End Function

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

=Otpusknye(B3;C3)

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

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

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

Вид исходной таблицы данных:

Для расчета используем формулу Миффлина — Сан Жеора, которую запишем в коде пользовательской функции с учетом пола участника. Код примера:

Public Function CaloriesPerDay(sex As String, age As Integer, weight As Integer, height As Integer) As Integer If sex = "женский" Then CaloriesPerDay = 10 * weight + 6.25 * height - 5 * age - 161 ElseIf sex = "мужской" Then CaloriesPerDay = 10 * weight + 6.25 * height - 5 * age + 5 Else: CaloriesPerDay = 0 End If End Function

Проверки корректности введенных данных упущены для упрощения кода. Если пол не определен, функция вернет результат 0 (нуль).

Пример расчета для первого участника:

=CaloriesPerDay(B3;C3;D3;E3)

В результате использования автозаполнения получим следующие результаты:

Пользовательская функция для решения квадратных уравнений в Excel

Пример 3. Создать функцию, которая возвращает результаты решения квадратных уравнений для указанных в ячейках коэффициентах a, b и c уравнения типа ax2+bx+c=0.

Вид исходной таблицы:

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

Код примера:

Public Function SquareEquation(a As Integer, b As Integer, c As Integer) As String Dim answer1 As String Dim answer2 As String If a = 0 Then answer1 = "Единственный корень - " SquareEquation = answer1 & "(" & -c / b & ")" ElseIf c = 0 Then answer1 = "Единственный корень - " SquareEquation = answer1 & "(" & -b / a & ")" ElseIf b = 0 And c < 0 Then answer1 = "Единственный корень - " SquareEquation = answer1 & "(" & Sqr(a / c) & ")" ElseIf b ^ 2 - 4 * a * c >= 0 Then answer1 = "Первый корень - " answer2 = "Второй корень - " SquareEquation = answer1 & "(" & (-b + Sqr(b ^ 2 - 4 * a * c)) / (2 * a) & ")" & "; " & _ answer2 & "(" & (-b - Sqr(b ^ 2 - 4 * a * c)) / (2 * a) & ")" Else: SquareEquation = "Решений нет" End If End Function

Найдем корни первого уравнения:

=SquareEquation(A3;B3;C3)

Выполним расчеты для остальных уравнений. Полученные результаты:

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

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

Источник

Загрузить PDF

Хотя в Excel множество (возможно, сотни) встроенных функций, таких как SUM (СУММ), VLOOKUP (ВПР), LEFT (ЛЕВСИМВ) и других, как только вы начинаете использовать Excel для более сложных задач, вы можете обнаружить, что вам нужна такая функция, которой еще не существует. Не отчаивайтесь, вы всегда можете создать функцию сами.

Шаги

1

Создайте новую книгу Excel или откройте книгу, в которой хотите использовать пользовательскую функцию (UDF).
2

Откройте редактор Visual Basic, который встроен в Microsoft Excel, выбрав «Инструменты»->«Макросы»->«Редактор Visual Basic» (или нажав Alt+F11).
3

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

Создайте «заголовок» или «прототип» вашей функции. Он должен иметь следующую структуру:

public function TheNameOfYourFunction (param1 As type1, param2 As type2 ) As returnType У нее может быть сколько угодно параметров, а их тип должен соответствовать любому базовому типу данных Excel или типу объектов, например Range. Параметры в данном случае выступают в качестве «операндов», с которыми работает функция. Например, если вы пишете SIN(45), чтобы вычислить синус 45 градусов, 45 выступает в качестве параметра. Код вашей функции будет использовать это значение для вычислений и представления результата.
5
Добавьте код нужной функции, убедившись, что вы 1) используете значения, передаваемые в качестве параметров; 2) присваиваете результат имени функции; 3) заканчиваете код функции выражением «end function». Изучение программирования на VBA или на любом другом языке может занять некоторое время и требовать подробного изучения руководства. Однако функции обычно имеют небольшие блоки кода и используют очень мало возможностей языка. Наиболее используемые элементы языка VBA:
1. Блок If, который позволяет выполнять какую-то часть кода только в случае выполнения условия. Например:
  Public Function Course Result(grade As Integer) As String If grade >= 5 Then CourseResult = "Approved" Else CourseResult = "Rejected" End If End Function
  
  Обратите внимание на элементы внутри блока If: IF условие THEN код_1 ELSE код_2 END IF. Ключевое слово Else и вторая часть кода необязательны.
2. Блок Do, который выполняет часть кода, пока выполняется условие (While) или до тех пор (Until), пока оно не выполнится. Например:
  Public Function IsPrime(value As Integer) As Boolean Dim i As Integer i = 2 IsPrime = True Do If value / i = Int(value / i) Then IsPrime = False End If i = i + 1 Loop While i < value And IsPrime = True End Function
  
  Обратите внимание на элементы: DO код LOOP WHILE/UNTIL условие. Обратите также внимание на вторую строку, где «объявлена» переменная. В своем коде вы можете добавлять переменные и позже их использовать. Переменные служат для хранения временных значений внутри кода. И наконец, обратите внимание, что функция объявлена как BOOLEAN, что является типом данных, в котором допустимы только значения TRUE и FALSE. Этот способ определения, является ли число простым, далеко не самый оптимальный, но мы оставили его таким, чтобы сделать код более читабельным.
3. Блок For , который выполняет часть кода указанное число раз. Например:
  
  Public Function Factorial(value As Integer) As Long Dim result As Long Dim i As Integer If value = 0 Then result = 1 ElseIf value = 1 Then result = 1 Else result = 1 For i = 1 To value result = result * i Next End If Factorial = result End Function
  
  Обратите внимание на элементы:FOR переменная = начальное_значение TO конечное_значение код NEXT. Также обратите внимание на элемент ElseIf в выражении If, который позволяет добавить больше условий к коду, который нужно выполнить. И наконец, обратите внимание на объявление функции и переменной «result» как Long. Тип данных Long позволяет хранить значения, намного превышающие Integer.
  
  Ниже показан код функции, преобразующей небольшие числа в слова.
6
Перейдите обратно в рабочую книгу Excel и используйте свою функцию, набрав в какой-либо ячейке знак равно, а затем имя функции. Добавьте к имени функции открывающую скобку, параметры, разделенные запятыми, и закрывающую скобку. Например:

=NumberToLetters(A4)

Вы также можете использовать свою пользовательскую функцию, найдя ее в категории Пользовательские в мастере вставки формулы. Просто нажмите на кнопку Fx, расположенную слева от поля формул. Параметры могут быть трех типов:
1. Константные значения, непосредственно вводимые в формуле в ячейке. Текстовые строки в таком случае должны быть заключены в кавычки.
2. Ссылки на ячейки вроде B6 или ссылки на диапазоны вроде A1:C3 (параметр должен иметь тип Range).
3. Другие вложенные функции (ваша функция тоже может быть вложенной по отношению к другим функциям). Например: =Factorial(MAX(D6:D8))
7

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

Реклама

Советы

Всякий раз, когда вы пишете блок кода внутри структуры If, For, Do и так далее, убедитесь, что вы он имеет отступ, который можно сделать с помощью пробелов или знаков табуляции (стиль отступов вы выбираете сами). Это сделает ваш код более читабельным, и вам самим потом легче будет отслеживать ошибки и вносить изменения.
Используйте имя, которое еще не используется в качестве имени функции в Excel, иначе вы сможете использовать только одну из этих функций.
Excel имеет множество встроенных функций, и большинство вычислений можно сделать с помощью независимого их использования или с использованием их комбинаций. Прежде чем писать свою функцию, пройдитесь по всему списку уже существующих функций. При использовании встроенных функций выполнение может происходить быстрее.
В некоторых случаях для вычисления результата функции необязательно знать все значения параметров. В подобных случаях вы можете использовать ключевое слово Optional перед именем параметра в заголовке функции. В коде вы можете использовать функцию IsMissing(имя_параметра), чтобы определить, было ли параметру присвоено какое-то значение или нет.
Если вы не знаете, как написать код функции, прочитайте статью о том, как написать простейший макрос в Microsoft Excel.

Предупреждения

В связи с определенными мерами безопасности некоторые люди могут отключить макросы. Обязательно известите своих коллег о том, что книга Excel, которую вы им посылаете, содержит макросы, и что эти макросы не навредят их компьютерам.
Примеры функций, использованных в этой статье, — необязательно самый лучший способ решить связанные с ними проблемы. Эти функции были использованы, чтобы наглядно показать использование контрольных структур языка.
VBA, как и многие другие языки, имеет еще несколько контрольных структур кроме Do, If и For. Эти структуры были приведены здесь, чтобы объяснить, что можно делать внутри кода функций. В интернете есть множество учебников, по которым вы можете изучить VBA.

Об этой статье

Эту страницу просматривали 56 756 раз.

Была ли эта статья полезной?

Источник

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

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

Окно вставки функции

Используем вкладку с формулами

Ручная вставка формулы в Excel

Вставка математических формул

Способ 1: Кнопка «Вставка функции»

Способ 2: Вкладка «Формулы»

Способ 3: Ручное создание формулы

Способ 4: Вставка математической формулы

Еще статьи по данной теме:

Помогла ли Вам статья?

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

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

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

Пользовательская функция для решения квадратных уравнений в Excel

Шаги

Советы

Предупреждения

Об этой статье

Была ли эта статья полезной?

А вот еще интересные статьи: