Больше функций в Excel

Обновлено: 21.11.2024

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 Web App Excel 2010 Excel 2007 Excel для Mac 2011 Excel для Windows Phone 10 Excel Starter 2010 Еще. Меньше

Функция ЕСЛИ позволяет провести логическое сравнение между значением и тем, что вы ожидаете, проверяя условие и возвращая результат, если он истинен или неверен.

=IF(Что-то верно, то сделайте что-нибудь, иначе сделайте что-нибудь другое)

Поэтому оператор IF может иметь два результата. Первый результат — если сравнение истинно, второй — если сравнение ложно.

Операции IF невероятно надежны и составляют основу многих моделей электронных таблиц, но они также являются основной причиной многих проблем с электронными таблицами. В идеале оператор ЕСЛИ должен применяться к минимальным условиям, таким как Мужчина/Женщина, Да/Нет/Возможно, и это лишь некоторые из них, но иногда вам может понадобиться оценить более сложные сценарии, требующие вложения* более 3 функций ЕСЛИ вместе.< /p>

* "Вложение" — это практика объединения нескольких функций в одну формулу.

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

ЕСЛИ(логическая_проверка, значение_если_истина, [значение_если_ложь])

Имя аргумента

Описание

логический_тест

Условие, которое вы хотите проверить.

значение_если_истина

Значение, которое вы хотите вернуть, если результат logical_test равен TRUE.

значение_если_ложь

Значение, которое вы хотите вернуть, если результат logical_test равен FALSE.

Примечания

Хотя Excel позволяет вкладывать до 64 различных функций ЕСЛИ, делать это совсем не рекомендуется. Почему?

Множественные операторы IF требуют тщательного обдумывания, чтобы правильно построить и убедиться, что их логика может правильно вычислять каждое условие до конца. Если вы не вложите свою формулу на 100 % точно, она может работать в 75 % случаев, но возвращать неожиданные результаты в 25 % случаев. К сожалению, шансы на то, что вы попадете в 25 %, невелики.

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

Если вы обнаружите, что оператор ЕСЛИ продолжает расти без конца, пора отложить мышь и пересмотреть свою стратегию.

Давайте посмотрим, как правильно создать сложный вложенный оператор ЕСЛИ, используя несколько операторов ЕСЛИ, и когда понять, что пришло время использовать другой инструмент в вашем арсенале Excel.

Примеры

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

Этот сложный вложенный оператор IF следует простой логике:

Если результат теста (в ячейке D2) больше 89, учащийся получает пятерку

Если результат теста выше 79, учащийся получает B

Если результат теста выше 69, учащийся получает оценку C

Если результат теста выше 59, учащийся получает оценку D

В противном случае учащийся получает F

Этот конкретный пример относительно безопасен, потому что маловероятно, что корреляция между результатами тестов и буквенными оценками изменится, поэтому он не потребует особого обслуживания. Но вот мысль — что, если вам нужно сегментировать оценки между A+, A и A- (и так далее)? Теперь нужно переписать оператор IF с четырьмя условиями, чтобы он содержал 12 условий! Вот как теперь будет выглядеть ваша формула:

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

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

Дополнительные примеры

Ниже приведен очень распространенный пример расчета комиссии с продаж на основе уровня дохода.

Эта формула гласит: ЕСЛИ(C9 больше 15 000, возвращается 20 %, ЕСЛИ(C9 больше 12 500, возвращается 17,5 % и т. д.

Несмотря на то, что эта формула очень похожа на предыдущий пример с оценками, она является отличным примером того, насколько сложно поддерживать большие операторы ЕСЛИ. Что вам нужно будет сделать, если ваша организация решит добавить новые уровни вознаграждения и, возможно, даже изменить существующие долларовые или процентные значения? У вас будет много работы!

Совет. Чтобы упростить чтение длинных формул, в строку формул можно вставлять разрывы строк. Просто нажмите ALT+ENTER перед текстом, который вы хотите перенести на новую строку.

Вот пример сценария комиссии с нарушенной логикой:

Вы видите, что не так? Сравните порядок сравнения Revenue с предыдущим примером. В какую сторону идет этот? Правильно, она идет снизу вверх (от 5000 до 15000 долларов), а не наоборот. Но почему это должно быть таким большим делом? Это большое дело, потому что формула не может пройти первую оценку для любого значения, превышающего 5000 долларов. Допустим, у вас есть доход в размере 12 500 долларов – оператор IF вернет 10 %, потому что он больше 5 000 долларов, и на этом он остановится. Это может быть невероятно проблематично, потому что во многих ситуациях эти типы ошибок остаются незамеченными, пока они не окажут негативного влияния. Итак, зная, что сложные вложенные операторы IF сопряжены с некоторыми серьезными ловушками, что вы можете сделать? В большинстве случаев можно использовать функцию ВПР вместо построения сложной формулы с помощью функции ЕСЛИ. Используя функцию ВПР, сначала необходимо создать справочную таблицу:

Эта формула говорит, что нужно искать значение в ячейке C2 в диапазоне C5:C17. Если значение найдено, вернуть соответствующее значение из той же строки в столбце D.

Аналогично эта формула ищет значение в ячейке B9 в диапазоне B2:B22. Если значение найдено, вернуть соответствующее значение из той же строки в столбце C.

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

ВПР рассматривается здесь более подробно, но это намного проще, чем 12-уровневый сложный вложенный оператор ЕСЛИ! Есть и другие менее очевидные преимущества:

Справочные таблицы ВПР находятся в открытом доступе, и их легко увидеть.

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

Если вы не хотите, чтобы люди видели вашу справочную таблицу или мешали ей, просто поместите ее на другой рабочий лист.

Знаете ли вы?

Появилась функция IFS, которая может заменить несколько вложенных операторов IF одной функцией. Поэтому вместо нашего примера с начальными оценками, в котором есть 4 вложенные функции ЕСЛИ:

Это можно сделать намного проще с помощью одной функции IFS:

Функция IFS великолепна, потому что вам не нужно беспокоиться обо всех этих операторах IF и скобках.

Примечание. Эта функция доступна только при наличии подписки на Microsoft 365. Если вы являетесь подписчиком Microsoft 365, убедитесь, что у вас установлена последняя версия Office.

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

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

Использование функции в качестве одного из аргументов в формуле, использующей функцию, называется вложением, и мы будем называть эту функцию вложенной функцией. Например, при вложении функций СРЗНАЧ и СУММ в аргументы функции ЕСЛИ следующая формула суммирует набор чисел (G2:G5), только если среднее значение другого набора чисел (F2:F5) больше 50. В противном случае возвращается 0.

Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.

В формулу можно вложить до 64 уровней функций.

Нажмите на ячейку, в которую хотите ввести формулу.

Чтобы начать формулу с функцией, нажмите "Вставить функцию" в строке формул.

Excel вставит за вас знак равенства (=).

В поле Или выберите категорию выберите Все.

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

Если вы не знаете, какую функцию использовать, вы можете ввести вопрос, описывающий, что вы хотите сделать, в поле "Найти функцию" (например, "добавить числа" возвращает функцию СУММ).

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

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

Если вы нажали ЕСЛИ, в диалоговом окне Аргументы функции отобразятся аргументы функции ЕСЛИ. Чтобы вложить другую функцию, вы можете ввести ее в поле аргумента. Например, можно ввести СУММ(G2:G5) в поле Значение_если_истина функции ЕСЛИ.

Введите любые дополнительные аргументы, необходимые для завершения формулы.

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

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

После ввода аргументов формулы нажмите кнопку "ОК".

Нажмите на ячейку, в которую хотите ввести формулу.

Чтобы начать формулу с функцией, нажмите "Вставить функцию" в строке формул.

В диалоговом окне "Вставить функцию" в поле "Выбрать категорию" выберите "Все".

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

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

Введите любые дополнительные аргументы, необходимые для завершения формулы.

После ввода аргументов формулы нажмите клавишу ВВОД.

Примеры

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

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

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

Синтаксис функции Excel

Следующий пример функции ОКРУГЛ, округляющей число в ячейке A10, иллюстрирует синтаксис функции.

<р>1. Структура. Структура функции начинается со знака равенства (=), за которым следует имя функции, открывающая скобка, аргументы функции, разделенные запятыми, и закрывающая скобка.

<р>2. Имя функции. Чтобы просмотреть список доступных функций, щелкните ячейку и нажмите SHIFT+F3, чтобы открыть диалоговое окно "Вставить функцию".

<р>4. Подсказка аргумента. При вводе функции появляется всплывающая подсказка с синтаксисом и аргументами. Например, введите =ROUND( и появится всплывающая подсказка. Подсказки появляются только для встроенных функций.

Ввод функций Excel

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

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

Вложенные функции Excel

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

<р>1. Функции СРЗНАЧ и СУММ вложены в функцию ЕСЛИ.

Ограничения уровня вложенности Формула может содержать до семи уровней вложенности функций. Когда одна функция (назовем ее Функцией Б) используется в качестве аргумента в другой функции (назовем ее Функцией А), Функция Б действует как функция второго уровня. Например, функция СРЗНАЧ и функция СУММ являются функциями второго уровня, если они используются в качестве аргументов функции ЕСЛИ. Функция, вложенная во вложенную функцию СРЗНАЧ, становится функцией третьего уровня и т. д.

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), который открывается в новом окне отдельно от Excel.

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

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

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

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

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

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

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

=СКИДКА(D7,E7)

Excel вычисляет 10-процентную скидку на 200 единиц по цене 47,5 долларов США за единицу и возвращает 950,00 долларов США.

В первой строке вашего кода VBA, функция СКИДКА(количество, цена), вы указали, что функция СКИДКА требует два аргумента: количество и цена. Когда вы вызываете функцию в ячейке рабочего листа, вы должны включить эти два аргумента. В формуле =СКИДКА(D7,E7) D7 – это аргумент количества, а E7 – аргумент цены. Теперь вы можете скопировать формулу СКИДКИ в G8:G13, чтобы получить результаты, показанные ниже.

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

Инструкция If в следующем блоке кода проверяет аргумент quantity и определяет, больше или равно ли количество проданных товаров 100:

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

Скидка = количество * цена * 0,1

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

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

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

Скидка = Application.Round(Скидка, 2)

В VBA нет функции ОКРУГЛ, но в Excel она есть. Поэтому, чтобы использовать ROUND в этом операторе, вы указываете VBA искать метод (функцию) Round в объекте Application (Excel).Вы делаете это, добавляя слово Application перед словом Round. Используйте этот синтаксис всякий раз, когда вам нужно получить доступ к функции Excel из модуля VBA.

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

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

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

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

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

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

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

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

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

После того как вы создали нужные функции, нажмите «Файл» > «Сохранить как».

В Excel 2007 нажмите кнопку Microsoft Office и выберите Сохранить как

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

После сохранения книги нажмите "Файл" > "Параметры Excel".

В Excel 2007 нажмите кнопку Microsoft Office и выберите Параметры Excel.

В диалоговом окне "Параметры Excel" выберите категорию "Надстройки".

В раскрывающемся списке Управление выберите Надстройки Excel. Затем нажмите кнопку "Перейти".

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

После того как вы создали нужные функции, нажмите «Файл» > «Сохранить как».

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

После сохранения книги выберите Инструменты > Надстройки Excel.

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

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

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

Этот контент изначально был создан Марком Доджем и Крейгом Стинсоном как часть их книги Microsoft Office Excel 2007 Inside Out. С тех пор он был обновлен, чтобы применяться и к более новым версиям Excel.

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

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