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

Софт Microsoft Office

12.07.20195357

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

Немного терминологии

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

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

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

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

А теперь давайте все это проверим на практике.

Как включить автоматические выключения формул в Excel?

Включить вычисления можно двумя способами:

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

1. Автоматически – автоматическое вычисление;
2. Автоматически, кроме данных таблиц;
3. Вручную – ручная активация расчетов.

• Открыть меню «Файл» => «Параметры»;
• В появившемся окне перейти во вкладку «Формулы»;
• Поставить флажок напротив пункта «Автоматически» в меню «Вычисления в книге»;
• Нажать кнопку «Ок».

Как видите все несложно.

Удачи в изучении Excel и успехов в работе.

Вопросы можете задавать в комментариях.

Похожее:

1. Умножение столбцов таблицы Excel на выбранное число.Как умножить столбец таблицы Excel на одно.
2. Как увеличить производительность труда или Таблица: Сочетание горячих клавиш в «Эксель»:Как увеличить производительность труда подчиненных в программе.
3. Сумма или среднее значение одной и той же ячейки с разных листов документа Excel.Добрый день. Часто возникают ситуации, когда нужно.

Формула 1: ВПР

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

Синтаксис

Это довольно сложная формула, имеющая 4 аргумента, а ее использование имеет множество особенностей.

Синтаксис такой:

=ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Давайте более подробно рассмотрим все аргументы:

1. Значение, которое ищется.
2. Таблица. Необходимо, чтобы было искомое значение, находящееся в первой колонке, а также значение, которое возвращается. Последнее располагается где-угодно. Пользователь может самостоятельно определиться, куда вставлять результат формулы.
3. Номер столбца.
4. Интервальный просмотр. Если в этом нет необходимости, то можно не приводить значение этого аргумента. Оно являет собой логическое выражение, обозначающее степень точности совпадения, которое должно быть обнаружено функцией. Если указан параметр «Истина», то тогда Excel будет искать самое близкое значение к указанному в качестве искомого. Если же указан параметр «Ложь», то функция будет искать только те значения, которые находятся в первой колонке.

На этом скриншоте мы пробуем с помощью формулы понять, сколько просмотров было осуществлено по запросу «купить планшет».

Отличие в версиях MS Excel

Всё описанное выше подходит для современных программ 2007, 2010, 2013 и 2020 года. Старый редактор Эксель значительно уступает в плане возможностей, количества функций и инструментов. Если откроете официальную справку от Microsoft, то увидите, что они дополнительно указывают, в какой именно версии программы появилась данная функция.

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

1. Указать какие-нибудь данные для вычисления. Кликните на любую клетку. Нажмите на иконку «Fx».

1. Выбираем категорию «Математические». Находим функцию «СУММ» и нажимаем на «OK».

1. Указываем данные в нужном диапазоне. Для того чтобы отобразить результат, нужно нажать на «OK».

1. Можете попробовать пересчитать в любом другом редакторе. Процесс будет происходить точно так же.

Формула 2: Если

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

Синтаксис

В формулу этой функции входит три основных аргумента, и она выглядит так:

=ЕСЛИ(логическое_выражение;«значение_если_истина»;«значение_если_ложь»).

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

Есть еще один вариант, как можно применять функцию ЕСЛИ

– вложенные функции. Здесь может быть намного больше условий, вплоть до 64. Пример рассуждения, соответствующего формуле, приведенной на скриншоте, такой. Если ячейка А2 равна двум, то нужно вывести значение «Да». Если же она имеет другое значение, то необходимо проверить, равна ли ячейка D2 двум. Если да, то нужно выдать значение «нет», если и здесь условие оказывается ложным, то формула должна вернуть значение «возможно».

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

Функция ЕСЛИ

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

Тут синтаксис следующий:

=ЕСЛИ(ЕПУСТО(номер ячейки);«Пустая»;«Не пустая»).

Кроме этого, есть возможность применять вместо функции ЕПУСТО

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

ЕСЛИ –

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

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

Видеоинструкция

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

Добрый день.

Когда-то, написать самостоятельно формулу в Excel — для меня было чем-то невероятным. И даже, несмотря на то, что часто приходилось работать в этой программе, ничего кроме текста не набивал…

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

И так, начнем…

Формула 3: СУММЕСЛИ

Функция СУММЕСЛИ

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

Синтаксис

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

=СУММЕСЛИ(диапазон;условие;[диапазон_суммирования])

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

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

Так, в данной ситуации Excel суммировал данные о тех запросах, где количество переходов превышает 100000.

Формула 5: СЧЕТЕСЛИ и СЧЕТЕСЛИМН

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

Синтаксис

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

=СЧЕТЕСЛИ(диапазон;критерий)

Что означают приведенные аргументы?

1. Диапазон – это совокупность ячеек, среди которой должен осуществляться подсчет.
2. Критерий – условие, учитываемое при выборе ячеек.

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

Если говорить о родственной функции
СЧЕТЕСЛИМН, то она, аналогично предыдущему примеру, предоставляет возможность использовать сразу несколько критериев.
Ее формула следующая:

=СЧЁТЕСЛИМН(диапазон_условия1;условие1;[диапазон_условия2;условие2];…)

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

Формула 6: ЕСЛИОШИБКА

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

Синтаксис

У этой функции два аргумента. Синтаксис следующий:

=ЕСЛИОШИБКА(значение;значение_если_ошибка)

Описание аргументов:

1. Значение – непосредственно формула, проверяемая на предмет багов.
2. Значение если ошибка – результат, появляющийся после того, как ошибка будет обнаружена.

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

Формула 7: ЛЕВСИМВ

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

Ее синтаксис следующий:

=ЛЕВСИМВ(текст;[число_знаков])

Возможные аргументы:

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

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

Формула 8: ПСТР

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

Ее синтаксис следующий:

=ПСТР(текст;начальная_позиция;число_знаков).

Расшифровка аргументов:

1. Текст – это строка, в которой содержатся необходимые данные.
2. Начальная позиция – это непосредственно положение того символа, который и служит началом для извлечения текста.
3. Число знаков – количество символов, которые формула должна вытащить из текста.

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

БЛОГ

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

Отсюда вытекает вопрос: Сколько нужно знать функций Excel, чтобы решать практически любую задачу в Excel?

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

Представляю Вам ТОП-50 самых главных функций в Microsoft Excel с примерами их использования

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

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

1.
СУММ / СРЗНАЧ / СЧЁТ / МАКС / МИН (SUM / AVERAGE / COUNT / MAX / MIN) — [Базовые формулы Excel] 2. ВПР (VLOOKUP) — [Ищет значение в первом столбце массива и выдает значение из ячейки в найденной строке и указанном столбце] 3. ИНДЕКС (INDEX) — [По индексу получает значение из ссылки или массива] 4. ПОИСКПОЗ (MATCH) — [Ищет значения в ссылке или массиве] 5. СУММПРОИЗВ (SUMPRODUCT) — [Вычисляет сумму произведений соответствующих элементов массивов (позволяет работать с массивами без формул массива)] 6. АГРЕГАТ / ПРОМЕЖУТОЧНЫЕ.ИТОГИ (AGGREGATE / SUBTOTALS) — [Возвращает общий итог или промежуточный итог в списке или базе данных с учетом фильтров или без учета фильтров] 7. ЕСЛИ (IF) — [Выполняет проверку условия] 8. И / ИЛИ / НЕ (AND / OR / NOT) — [Логические условия, как правило для функции ЕСЛИ] 9. ЕСЛИОШИБКА (IFERROR) — [Если формула возвращает ошибку то что] 10. СУММЕСЛИМН (SUMIFS) — [Суммирует ячейки, удовлетворяющие заданным критериям. Допускается указывать более одного условия] 11. СРЗНАЧЕСЛИМН (AVERAGEIFS) — [Возвращает среднее арифметическое значение всех ячеек, которые соответствуют нескольким условиям] 12. СЧЁТЕСЛИМН (COUNTIFS) — [Подсчитывает количество ячеек, которые соответствуют нескольким условиям] 13. МИНЕСЛИ / МАКСЕСЛИ (MINIFS / MAXIFS) — [Возвращает минимальное/максимальное значение всех ячеек, которые соответствуют нескольким условиям] 14. НАИБОЛЬШИЙ / НАИМЕНЬШИЙ (LARGE / SMALL) — [Возвращает k-ое наибольшее/наименьшее значение в множестве данных] 15. ДВССЫЛ (INDIRECT) — [Определяет ссылку, заданную текстовым значением] 16. ВЫБОР (CHOOSE) — [Выбирает значение из списка значений по индексу] 17. ПРОСМОТР (LOOKUP) — [Ищет значения в массиве] 18. СМЕЩ (OFFSET) — [Определяет смещение ссылки относительно заданной ссылки] 19. СТРОКА / СТОЛБЕЦ (ROW / COLUMN) — [Возвращает номер строки/столбца, на который указывает ссылка] 20. ЧИСЛСТОЛБ / ЧСТРОК (COLUMNS / ROWS) — [Возвращает количество столбцов/строк в ссылке] 21. ОКРУГЛ / ОКРУГЛТ / ОКРУГЛВНИЗ / ОКРУГЛВВЕРХ (ROUND / MROUND / ROUNDDOWN / ROUNDUP) — [Округляет число до указанного количества десятичных разрядов] 22. СЛЧИС / СЛУЧМЕЖДУ / РАНГ (RAND / RANDBETWEEN / RANK) — [Возвращает случайное число] 23. (N) — [Возвращает значение, преобразованное в число] 24. ЧАСТОТА (FREQUENCY) — [Находит распределение частот в виде вертикального массива] 25. СЦЕПИТЬ / СЦЕП / ОБЪЕДИНИТЬ / & (CONCATENATE / CONCAT / TEXTJOIN / &) — [Объединения двух или нескольких текстовых строк в одну] 26. ПСТР (MID) — [Выдает определенное число знаков из строки текста, начиная с указанной позиции] 27. ЛЕВСИМВ / ПРАВСИМВ (LEFT / RIGHT) — [Возвращает заданное количество символов текстовой строки слева / права] 28. ДЛСТР (LEN) — [Определяет количество знаков в текстовой строке] 29. НАЙТИ / ПОИСК (FIND / SEARCH) — [Поиск текста в ячейке с учетом / без учета регистр] 30. ПОДСТАВИТЬ / ЗАМЕНИТЬ (SUBSTITUTE / REPLACE) — [Заменяет в текстовой строке старый текст новым] 31. СТРОЧН / ПРОПИСН / ПРОПНАЧ (LOWER / UPPER) — [Преобразует все буквы текста в строчные/прописные/ или первую букву в каждом слове текста в прописную] 32. ГИПЕРССЫЛКА (HYPERLINK) — [Создает ссылку, открывающую документ, находящийся на жестком диске, сервере сети или в Интернете] 33. СЖПРОБЕЛЫ (TRIM) — [Удаляет из текста все пробелы, за исключением одиночных пробелов между словами] 34. ПЕЧСИМВ (CLEAN) — [Удаляет все непечатаемые знаки из текста] 35. СОВПАД (EXACT) — [Проверяет идентичность двух текстов] 36. СИМВОЛ / ПОВТОР (CHAR / REPT) — [Возвращает знак с заданным кодом/Повторяет текст заданное число раз] 37. СЕГОДНЯ / ТДАТА (TODAY / NOW) — [Возвращает текущую дату в числовом формате / Возвращает текущую дату и время в числовом формате] 38. МЕСЯЦ / ГОД (MONTH / YEAR) — [Вычисляет год / месяц от заданной даты] 39. НОМНЕДЕЛИ (WEEKNUM) — [Преобразует дату в числовом формате в число, которое указывает, на какую неделю года приходится дата] 40. ДАТАЗНАЧ (DATEVALUE) — [Преобразует дату из текстового формата в числовой] 41. РАЗНДАТ (DATEDIF) — [Вычисляет количество дней, месяцев или лет между двумя датами] 42. РАБДЕНЬ (WORKDAY) — [Возвращает дату в числовом формате, отстоящую вперед или назад на заданное количество рабочих дней] 43. ЯЧЕЙКА (CELL) — [Возвращает сведения о формате, расположении или содержимом ячейки] 44. ТРАНСП (TRANSPOSE) — [Выдает транспонированный массив] 45. ПРЕОБР (CONVERT) — [Преобразует число из одной системы мер в другую] 46. ПРЕДСКАЗ (FORECAST) — [Вычисляет или предсказывает будущее значение по существующим значениям линейным трендом] 47. ТИП.ОШИБКИ (ERROR.TYPE) — [Возвращает числовой код, соответствующий типу ошибки] 48. ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ (GETPIVOTDATA) — [Возвращает данные, хранящиеся в сводной таблице] 49. БДСУММ (DSUM) — [Суммирует числа в поле (столбце) записей списка или базы данных, которые удовлетворяют заданным условиям] 50. В качестве бонуса рекомендую изучить Пользовательские форматы в Excel. После освоения данных функций, следующим этапом рекомендую осваивать инструменты Бизнес- аналитики Business Intelligence (BI)

В Excel к инструментам бизнес-аналитики уровня Self-Service BI относятся бесплатные надстройки «Power»:

• Power Query — это технология подключения к данным, с помощью которой можно обнаруживать, подключать, объединять и уточнять данные из различных источников для последующего анализа.
• Power Pivot — это технология моделирования данных, которая позволяет создавать аналитические модели данных, устанавливать отношения и добавлять аналитические вычисления.
• Power View — это технология визуализации данных, с помощью которой можно создавать интерактивные диаграммы, графики, карты и другие наглядные элементы, позволяющие визуализировать различную информацию.

Ну и если Вы со временем поймете, что возможностей Excel для решения ваших аналитических задач недостаточно, то вам пора переходить к изучению промышленных решений уровня Business Intelligence (BI)

Июль 19, 2017

18 884

Формула 11: ПОИСКПОЗ

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

Шаблон этой формулы такой:

=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;тип_сопоставления)

Первые два аргумента обязательные, последний – нет.

Существует три способа сопоставления:

1. Меньше или равно – 1.
2. Точное – 0.
3. Самое маленькое значение, равно искомому или большего него -1.

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

Выводы

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

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

Создание простой пользовательской функции в VBA

Давайте создадим простую пользовательскую функцию на VBA и посмотрим как там все работает.

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

Function Цифры(Текст As String) As Long Dim i As Long Dim result As String For i = 1 To Len(Текст) If IsNumeric(Mid(Текст, i, 1)) Then result = result & Mid(Текст, i, 1) Next Цифры = CLng(result) End Function

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

Теперь посмотрим как функция работает, попробуем использовать ее на листе:

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

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