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

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

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

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

  1. Открыть редактор языка VBA при помощи композиции кнопок ALT+F11.
  2. В открывшемся окне избрать пункт Insert и подпункт Module, как показано на рисунке: VBA.
  3. Новейший модуль будет сотворен автоматом, при всем этом в главный части окна редактора покажется окно для ввода кода: Новый модуль.
  4. По мере необходимости можно поменять заглавие модуля.
  5. В отличие от макросов, код которых должен находиться меж операторами Sub и End Sub, пользовательские функции обозначают операторами Function и End Function соответственно. В состав пользовательской функции входят заглавие (случайное имя, отражающее ее сущность), перечень характеристик (аргументов) с объявлением их типов, если они требуются (некие могут не принимать аргументов), тип возвращаемого значения, тело функции (код, отражающий логику ее работы), также оператор End Function. Пример обычной пользовательской функции, возвращающей наименования денька недельки в зависимости от обозначенного номера, представлен на рисунке ниже: Function и End Function.
  6. Опосля ввода представленного выше кода нужно надавить комбинацию кнопок Ctrl+S либо особый значок в левом верхнем углу редактора кода для сохранения.
  7. Чтоб пользоваться сделанной функцией, нужно возвратиться к табличному редактору Excel, установить курсор в всякую ячейку и ввести заглавие пользовательской функции опосля знака «=»:

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

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

  1. Сделайте новейший макрос (нажмите комбинацию кнопок Alt+F8), в показавшемся окне введите случайное заглавие новейшего макроса, нажмите клавишу Сделать: Создайте новый макрос.
  2. В итоге будет сотворен новейший модуль с заготовкой, ограниченной операторами Sub и End Sub. Sub и End Sub.
  3. Введите код, как показано на рисунке ниже, указав требуемое количество переменных (в зависимости от числа аргументов пользовательской функции): Введите код макроса.
  4. В качестве «Macro» обязана быть передана текстовая строчка с заглавием пользовательской функции, в качестве «Description» — переменная типа String с текстом описания возвращаемого значения, в качестве «ArgumentDescriptions» — массив переменных типа String с текстами описаний аргументов пользовательской функции.
  5. Для сотворения описания пользовательской функции довольно один раз выполнить сделанный выше модуль. Сейчас при вызове пользовательской функции (либо SHIFT+F3) отображается описание возвращаемого результата и переменной: Description.

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

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

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

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

Пример 1.

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

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

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

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

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

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

Otpusknye.

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

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

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

Пример 2.

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

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

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

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

CaloriesPerDay.

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

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

Вид начальной таблицы:

Пример 3.

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

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

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

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

SquareEquation.

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

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

Модуль 3
«Функции»

Функции. Ввод функций вручную. Ввод функции при помощи клавиши «сигма». Работа с мастером функций.

Интересно почитать:  Найти функция эксель

Функции в Microsoft Excel

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

Все функции в Excel характеризуются:

  • Заглавием;
  • Назначением (что, фактически, она делает);
  • Количеством аргументов (характеристик);
  • Типом аргументов (характеристик);
  • Типом возвращаемого значения.

В качестве примера разберем функцию «СТЕПЕНЬ»

  • Заглавие: СТЕПЕНЬ;
  • Назначение: возводит обозначенное число в обозначенную степень;
  • Количество аргументов: РОВНО два (ни меньше, ни больше, по другому Excel выдаст ошибку!);
  • Тип аргументов: оба аргумента должны быть числами, либо тем, что в итоге преобразуется в число. Если заместо 1-го из их вписать текст, Excel выдаст ошибку. А если заместо одно из их написать логические значения «ЛОЖЬ» либо «ИСТИНА», ошибки не будет, поэтому что Excel считает «ЛОЖЬ» равно 0, а правду — хоть какое другое ненулевое значение, даже ?1 равно «ИСТИНА». Другими словами логические значения в итоге преобразуются в числовые;
  • Тип возвращаемого значения: число — итог возведения в степень.

Пример использования: «=СТЕПЕНЬ(2;10)». Если написать эту формулу в ячкейке и надавить Enter, в ячейке будет число 1024. Тут 2 и 10 — аргументы (характеристики), а 1024 — возвращаемое функцией значение.

Пример формулы для вычисления длины окружности, содержащую функцию ПИ():

Formula funkciya excel.jpg

Синтаксис записи функции

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

Общий синтаксис записи хоть какой функции в Excel:

имя_функции([аргумент_1; аргумент_2; … ; аргумент_N])

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

  • Некие функции совершенно не принимают аргументов. К примеру, функция ПИ() просто возвращает в ячеку значение константы «3,1415…», а функция СЕГОДНЯ() вставляет в ячейку текущую дату. Но, даже если функция не воспринимает аргументов, пустые круглые скобки писать непременно, по другому Excel выдаст ошибку!
  • Некие функции принимают РОВНО ОДИН аргумент. К примеру функции sin(число), cos(число) и т. п.
  • Некие функции принимают больше, чем один аргумент. В таком случае аргументы делятся меж собой точкой с запятой «;».

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

Ввод функций вручную

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

К недочетам набора формул вручную можно отнести:

  • Ручками ставить «=»;
  • набирать имя функции;
  • открывать/закрывать круглые скобки;
  • расставлять точки с запятой;
  • смотреть за порядком вложенности
  • заключать текст в двойные кавычки;
  • не иметь возможность поглядеть промежные расчеты;
  • и т. п.

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

Ввод функции при помощи клавиши Knopka epsilon excel.jpg«сигма»

Одно из средств облегчить и убыстрить работы с функциями — клавиша Knopka epsilon excel.jpgна панели инструментов «Обычная». В ней создатели Microsoft «упрятали» 5 нередко применяемых функций:

  • СУММ(минимум один, максимум 30 аргументов). Суммирует свои аргументы.

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

  • СРЗНАЧ(минимум один, максимум 30 аргументов). Находит среднее арифметическое аргументов;
  • СЧЁТ(минимум один, максимум 30 аргументов). Подсчитывает количество чисел в перечне аргументов (употребляется для подсчета количества ячеек с числами, пустые ячейки и текст игнорируются);
  • МАКС(минимум один, максимум 30 аргументов). Возвращает наибольший аргумент;
  • МИН(минимум один, максимум 30 аргументов). Возвращает малый аргумент.

Knopka epsilon excel1.jpgKnopka epsilon excel2.jpg

  1. активизируем ячейку, где должен быть итог (просто щелкаем);
  2. Жмем на стрелочку справа от клавиши «сигма»;
  3. Избираем подходящую функцию. Опосля выбора Excel сам вставит символ «=», имя функции, круглые скобки, и даже попробует угадать спектр, который мы ходим выделить (хотя он изредка угадывает);
  4. Выделяем связный спектр ячеек. Excel вствит в круглые скобки адресок выделенного спектра;
  5. Если необходимо, к примеру, проссуммировать числа из бессвязных диапазонов, зажимаем Ctrl, и выделяем необходимое количество диапазонов. Excel сам поставит точку с запятой, и вставит ссылку на иной спектр;
  6. Когда выделили все нужные спектры, для окончания жмем Enter;

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

Работа с мастером функций

Общие сведения. Методы пуска

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

Интересно почитать:  Функция заменить в excel

Существует 3 метода пуска мастера функций:

  1. При помощи клавиши в строке формул;
  2. При помощи команды «Остальные функции. » клавиши Knopka epsilon excel.jpg;
  3. При помощи пт меню «Вставка» —> «Функция»;

1-ый шаг

Опосля выполнения 1-го из этих действий раскроется окно мастера функций:

Okno mastera funkciy.png

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

2-ой шаг

Раскрывается последующее окно:

Master functiy 001.png

Вставка вложенной функции

В одну функцию можно вставить другую функцию. Допускается до 7-ми уровней вложения функций (в Office 2007 — до 64). Естественно, функцию можно записать вручную (писать заглавие вложенной функции, открывать скобки, ставить точки с запятой). Но это противоречит самой идеологии мастера функций, который должен облегчать написание формул, защищать юзера от ошибок и свести к минимуму ручную работу. Существует наиболее удачный метод вложить функцию — особая клавиша на панели «Строчка формул»:

Master functiy 002.png

Опосля выбора подходящей функции из выпадающего перечня Excel вставит называние фукнции и круглые скобки в обозначенное пространство в формуле (в активное текстовое поле аргумента). Опосля этого окно мастера функций для предшествующей функции (в этом примере «СУММ») сменится на окно для вставляемой функции («СТЕПЕНЬ»), и ее заглавие в формуле сделается жирным:

Master functiy 003.png

Переключение на другую функцию в формуле

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

Формулы в Excel

frml-v-exl(48)

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

Как написать формулу в Excel

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

  1. Любая начинается со знака «=».
  2. Участвовать в вычислениях могут значения из ячеек и функции.
  3. В качестве обычных нам математических символов операций употребляются операторы.
  4. При вставке записи в ячейке по дефлоту отражается итог вычислений.
  5. Поглядеть систему можно в строке над таблицей.

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

Итак, как сделать и вставить формулу в Excel? Действуйте по последующему методу:

  • Установите указатель на ячейку, где будут выполняться вычисления.
  • Нажмите на клавиатуре символ «=».
  • Мышкой щелкните по первой ячейке, которая участвует в вычислениях.
  • Поставьте символ оператора, к примеру «*».
  • Щелкните по 2-ой ячейке.
  • Нажмите ENTER.
  • Переведите указатель на ячейку с вычислениями, и вы увидите, как смотрится итог ваших действий.

Мы узнали, как поставить и посчитать формулу в Excel, а ниже приведен список операторов.

Обозначение Значение

+ Сложение
— Вычитание
/ Деление
* Умножение

Если для вас нужно указать число, а не адресок ячейки – вводите его с клавиатуры. Чтоб указать отрицательный символ в формуле Excel, нажмите «-».

Как вводить и скопировать формулы в Excel

Ввод их постоянно осуществляется опосля нажатия на «=». Но что созодать, если однотипных расчетов много? В таком случае можно указать одну, а потом ее просто скопировать. Для этого следует ввести формулу, а потом «растянуть» ее в подходящем направлении, чтоб размножить.
Установите указатель на копируемую ячейку и наведите указатель мыши на правый нижний угол (на квадратик). Он должен принять вид обычного крестика с равными сторонами.

Нажмите левую клавишу и тяните.

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

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

При копировании таковым образом номера строчки растут, если сдвиг происходит вниз, либо растут номера столбцов – если на право. Это именуется относительная адресация.
Давайте введем в таблицу значение НДС и посчитаем стоимость с налогом.

Стоимость с НДС высчитывается как стоимость*(1+НДС). Введем последовательность в первую ячейку.

Попробуем скопировать запись.

Итог вышел странноватый.

Проверим содержимое во 2-ой ячейке.

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

Нажмите F4. Адресок будет разбавлен знаком «$». Это и есть признак полностью ячейки.

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

Интересно почитать:  Excel функция поиск

Внедрение функций для вычислений

Excel дает огромное количество функций, которые разбиты по категориям. Поглядеть полный список можно, нажав на клавишу Fx около строчки формул либо открыв раздел «Формулы» на панели инструментов.

Поведаем о неких функциях.

Как задать формулы «Если» в Excel

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

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

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

Укажем условие. Для этого нужно щелкнуть в первую строчку и избрать первую ячейку «Продано». Дальше поставим символ «>» и укажем число 4.

Во 2-ой строке напишем «Закупить». Эта надпись будет появляться для тех продуктов, которые были распроданы. Последнюю строчку можно бросить пустой, потому что у нас нет действий, если условие неверно.

Нажмите ОК и скопируйте запись для всего столбца.

Чтоб в ячейке не выводилось «ЛОЖЬ» опять откроем функцию и исправим ее. Поставьте указатель на первую ячейку и нажмите Fx около строчки формул. Вставьте курсор на третью строчку и поставьте пробел в кавычках.

Потом ОК и опять скопируйте.

Сейчас мы лицезреем, какой продукт следует закупить.

Формула текст в Excel

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

В первую ячейку введем функцию (клавиша «Текстовые» в разделе «Формулы»).

В окне аргументов укажем ссылку на ячейку итоговой суммы и установим формат «#руб.».

Нажмем ОК и скопируем.

Если испытать применять эту сумму в вычислениях, то получим сообщение о ошибке.

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

Формула даты в Excel

Excel предоставляет много способностей по работе с датами. Одна из их, ДАТА, дозволяет выстроить дату из 3-х чисел. Это комфортно, если вы имеете три различных столбца – денек, месяц, год.

Поставьте указатель на первую ячейку 4-ого столбца и изберите функцию из перечня «Дата и время».

Расставьте адреса ячеек подходящим образом и нажмите ОК.

Скопируйте запись.

Автосумма в Excel

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

Перейдите на панель «Формулы» и нажмите «Автосумма».

Excel автоматом выделит ближний числовой спектр.

Вы сможете выделить иной спектр. В данном примере Excel все сделал верно. Нажмите ОК. Направьте внимание на содержимое ячейки. Функция СУММ подставилась автоматом.

При вставке спектра указывается адресок первой ячейки, двоеточие и адресок крайней ячейки. «:» значит «Взять все ячейки меж первой и крайней. Если для вас нужно перечислить несколько ячеек, разделите их адреса точкой с запятой:
СУММ (F5;F8;F11)

Работа в Excel с формулами: пример

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

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

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

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

Ссылка на основную публикацию
Adblock
detector