Эксель все формулы: Функции Excel (по категориям)

Введение в формулах (Intro to Formulas)

Lesson 4: Введение в формулах (Intro to Formulas)

/en/tr_ru-misc/-cell-basics/content/

Введение.

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

По желанию: загрузите наше практическое учебное пособие .

В приведенном ниже видео вы узнаете больше о создании формул в Excel.

Математические операторы

Excel использует стандартные операторы для формул, такие как: знак плюс для сложения (+), знак минус для вычитания (-), звездочка для умножения (*), косая черта для деления (/) и циркумфлекс (^) для возведения в степень.

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

Понятие ссылки на ячейки

Несмотря на то, что вы можете создавать простые формулы в Excel с помощью чисел (например, = 2 + 2 или = 5 * 5), в большинстве случаев для создания формул вы будете использовать адреса ячеек. Это называется созданием ссылки на ячейку. Использование ссылок на ячейки будет гарантировать точность ваших формул, так как вы сможете менять значение ссылочных ячеек без редактирования всей формулы.

В приведенной ниже формуле в ячейке A3 складывается значение ячеек А1 и А2, создавая ссылку на ячейки:

При нажатии кнопки Ввод, формула вычисляет и отображает результат равенства в ячейке A3:

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

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

— Разрыв страницы

Создаем формулу в Excel:

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

1. Выделите ячейку, которая будет содержать формулу. В нашем примере мы выделили D12.
2. Введите знак равенства (=). Обратите внимание на то, что он появляется как в самой ячейке, так и в строке формул.
3. Введите в формуле первую ссылку на ячейку: в нашем примере это ячейка D10. Вокруг ссылки на ячейку отобразится рамка синего цвета.
4. Введите математический оператор, который вы хотите применить. В нашем примере, мы введем знак сложения (+).
5. Введите в формуле вторую ссылку на ячейку: в нашем примере это ячейка D11. Вокруг ссылки на ячейку отобразится рамка красного цвета.
6. Нажмите Ввод на клавиатуре. Формула будет рассчитана, а значение отобразится в ячейке. Если вы снова выделите ячейку, то вы заметите, что ячейка показывает результат, в то время как в строке формул отображается формула.

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

Изменение значений в ссылках на ячейки

Главным преимуществом ссылок является возможность обновлять данные на листе Excel без необходимости переписывать формулы. В приведенном ниже примере мы изменили значение ячейки B1 с $ 1200 на $ 1800. Формула в ячейке B3 будет автоматически пересчитана и новый результат отобразится в ячейке B3.

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

— Разрыв страницы

Создаем формулу в Excel с выделением курсором мыши:

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

1. Выделите ячейку , которая будет содержать формулу. В нашем примере мы выделили ячейку D4.
2. Введите знак равенства (=).
3. Введите в формуле первую ссылку на ячейку: в нашем примере это ячейка B4. Ссылка на ячейку отобразится в формуле.
4. Введите математический оператор , который вы хотите применить. В нашем примере, мы будем вводить знак умножения (*).
5. Введите в формуле вторую ссылку на ячейку: в нашем примере это ячейка С4. Ссылка на ячейку отобразится в формуле.
6. Нажмите Ввод на клавиатуре. Формула будет рассчитана, а значение отобразится в ячейке.

Копирование формул с помощью маркера автозаполнения

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

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

Как изменить формулу в Excel:

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

1. Выделите ячейку с формулой, которую вы хотите изменить. В нашем примере мы выделили ячейки D12.
2. Для того, чтобы изменить формулу в ячейке, нажмите на строку формул. Вы можете также дважды щелкнуть по ячейке, чтобы посмотреть и отредактировать формулу непосредственно в самой ячейке.
3. Все ячейки, на которые ссылается формула, будут обведены рамкой. В нашем примере мы изменим первую часть формулы, чтобы ссылка вела на ячейку D10, вместо ячейки D9.
4. Когда вы закончите, нажмите Ввод на клавиатуре или нажмите на команду Ввод в строке формул.
5. Формула будет обновлена и новое значение отобразится в ячейке.

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

Чтобы показать все формулы в электронной таблице, нажмите сочетание клавиш Ctrl и `(апостроф). Ключ гравис обычно находится в верхнем левом углу клавиатуры. Чтобы вернуться в обычный режим просмотра, нажмите повторно Ctrl + `.

— Разрыв страницы

Практическое занятие!

1. Откройте наше практическое учебное пособие .
2. Нажмите на вкладку Вызовы в левом нижнем углу учебного пособия.
3. Создайте формулу в ячейке D4 , которая умножит количество товара в ячейке В4 на цену за одну единицу товара в ячейке C4.
4. С помощью маркера заполнения скопируйте формулу из ячейки D4 в ячейки D5:D7.
5. Измените цену за одну единицу жаренного плода в ячейке С6 и укажите $2,25. Обратите внимание на то, что колонка общей стоимости также будет автоматически изменена.
6. Измените формулу для итогового значения в ячейке D8 с прибавлением ячейки D7.
7. Когда вы закончите, ваш результат должен выглядеть следующим образом:

Back to Tutorial

Previous: Понятие ячеек (Cell Basics)

Next:Return to Playlist: русский

Преобразование формул в значения

8946
10.01.2015
Скачать пример

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

• Вы хотите зафиксировать цифры в вашем отчете на текущую дату.
• Вы не хотите, чтобы клиент увидел формулы, по которым вы рассчитывали для него стоимость проекта (а то поймет, что вы заложили 300% маржи на всякий случай).
• Ваш файл содержит такое больше количество формул, что Excel начал жутко тормозить при любых, даже самых простых изменениях в нем, т.к. постоянно их пересчитывает (хотя, честности ради, надо сказать, что это можно решить временным отключением автоматических вычислений на вкладке Формулы – Параметры вычислений).
• Вы хотите скопировать диапазон с данными из одного места в другое, но при копировании «сползут» все ссылки в формулах.

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

Способ 1. Классический

Этот способ прост, известен большинству пользователей и заключается в использовании специальной вставки:

1. Выделите диапазон с формулами, которые нужно заменить на значения.
2. Скопируйте его правой кнопкой мыши – Копировать (Copy).
3. Щелкните правой кнопкой мыши по выделенным ячейкам и выберите либо значок Значения (Values):

   
   либо наведитесь мышью на команду Специальная вставка (Paste Special), чтобы увидеть подменю:

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

   В старых версиях Excel таких удобных желтых кнопочек нет, но можно просто выбрать команду Специальная вставка и затем опцию Значения (Paste Special — Values) в открывшемся диалоговом окне:

Способ 2.

Только клавишами без мыши

При некотором навыке, можно проделать всё вышеперечисленное вообще на касаясь мыши:

1. Копируем выделенный диапазон Ctrl+C
2. Тут же вставляем обратно сочетанием Ctrl+V
3. Жмём Ctrl, чтобы вызвать меню вариантов вставки
4. Нажимаем клавишу с русской буквой З или используем стрелки, чтобы выбрать вариант Значения и подтверждаем выбор клавишей Enter:

Способ 3. Только мышью без клавиш или Ловкость Рук

Этот способ требует определенной сноровки, но будет заметно быстрее предыдущего. Делаем следующее:

1. Выделяем диапазон с формулами на листе
2. Хватаем за край выделенной области (толстая черная линия по периметру) и, удерживая ПРАВУЮ клавишу мыши, перетаскиваем на пару сантиметров в любую сторону, а потом возвращаем на то же место
3. В появившемся контекстном меню после перетаскивания выбираем Копировать только значения (Copy As Values Only).

После небольшой тренировки делается такое действие очень легко и быстро. Главное, чтобы сосед под локоть не толкал и руки не дрожали ;)

Способ 4. Кнопка для вставки значений на Панели быстрого доступа

Ускорить специальную вставку можно, если добавить на панель быстрого доступа в левый верхний угол окна кнопку Вставить как значения. Для этого выберите Файл — Параметры — Панель быстрого доступа (File — Options — Customize Quick Access Toolbar). В открывшемся окне выберите Все команды (All commands) в выпадающем списке, найдите кнопку Вставить значения (Paste Values) и добавьте ее на панель:

Теперь после копирования ячеек с формулами будет достаточно нажать на эту кнопку на панели быстрого доступа:

Кроме того, по умолчанию всем кнопкам на этой панели присваивается сочетание клавиш Alt + цифра (нажимать последовательно). Если нажать на клавишу Alt, то Excel подскажет цифру, которая за это отвечает:

Способ 5. Макросы для выделенного диапазона, целого листа или всей книги сразу

Если вас не пугает слово «макросы», то это будет, пожалуй, самый быстрый способ.

Макрос для превращения всех формул в значения в выделенном диапазоне (или нескольких диапазонах, выделенных одновременно с Ctrl) выглядит так:

Sub Formulas_To_Values_Selection()
'преобразование формул в значения в выделенном диапазоне(ах)
    Dim smallrng As Range
    For Each smallrng In Selection.Areas
        smallrng.Value = smallrng.Value
    Next smallrng
End Sub

Если вам нужно преобразовать в значения текущий лист, то макрос будет таким:

Sub Formulas_To_Values_Sheet()
'преобразование формул в значения на текущем листе
    ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
End Sub
 

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

Sub Formulas_To_Values_Book()
'преобразование формул в значения во всей книге
    For Each ws In ActiveWorkbook. Worksheets
        ws.UsedRange.Value = ws.UsedRange.Value
    Next ws
End Sub
 

Код нужных макросов можно скопировать в новый модуль вашего файла (жмем Alt+F11 чтобы попасть в Visual Basic, далее Insert — Module). Запускать их потом можно через вкладку Разработчик — Макросы (Developer — Macros) или сочетанием клавиш Alt+F8. Макросы будут работать в любой книге, пока открыт файл, где они хранятся. И помните, пожалуйста, о том, что действия выполненные макросом невозможно отменить — применяйте их с осторожностью. 

Способ 6. Для ленивых

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

В этом случае:

• всё будет максимально быстро и просто
• можно откатить ошибочную конвертацию отменой последнего действия или сочетанием Ctrl+Z как обычно
• в отличие от предыдущего способа, этот макрос корректно работает, если на листе есть скрытые строки/столбцы или включены фильтры
• любой из этих команд можно назначить любое удобное вам сочетание клавиш в Диспетчере горячих клавиш PLEX

Ссылки по теме

• Что такое макросы, как их использовать, копировать и запускать
• Как скопировать формулы без сдвига ссылок
• Как считать в Excel без формул

Расширенные функции Excel [Единственное руководство по Excel на 2022 год]

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

Поиск цели

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

Давайте рассмотрим пример, чтобы лучше понять его.

Пример

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

5000 долларов в месяц для погашения суммы кредита.

Функция

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

Давайте рассмотрим эту задачу пошагово, чтобы увидеть, как мы можем рассчитать процентную ставку, которая позволит погасить кредит в размере 400 000 долларов США при ежемесячном платеже в размере 5 000 долларов США.

• Формула PMT теперь должна быть введена в ячейку, которая находится рядом с платежной ячейкой. В настоящее время в ячейке процентной ставки нет значения, Excel дает нам платеж в размере 3 333,33 доллара США, поскольку предполагается, что процентная ставка равна 0%. Игнорируй это.

• Перейдите в раздел Данные > Анализ «что если» > Поиск цели

• Нажмите OK. Вы увидите, что функция поиска цели автоматически дает процентную ставку, необходимую для выплаты суммы кредита.

Перейдите в раздел «Главная» > «Число» и измените значение на «Процент».

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

Анализ «что если» с помощью Solver

Анализ «что, если» — это метод изменения значений для опробования различных сценариев для формул в Advanced Excel.

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

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

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

Читайте также: Лучшее руководство по созданию информационной панели Excel

Активация надстройки Solver

• На вкладке «Файл» щелкните «Параметры».
• Перейдите в надстройки, выберите надстройку Solver и нажмите кнопку «Перейти».

• Проверьте надстройку Solver и нажмите OK.

•  На вкладке «Данные» в группе «Анализ» вы можете увидеть добавление параметра «Решатель».

Как использовать решатель в Excel

В этом примере мы попытаемся найти решение простой задачи оптимизации.

Проблема: Предположим, вы владелец бизнеса и хотите, чтобы ваш доход составлял 8000 долларов.

Цель: Рассчитать количество продаваемых единиц и цену за единицу для достижения цели.

Например, мы создали следующую модель:

• На вкладке Данные в группе Анализ нажмите кнопку Решатель.
• В установленной цели выберите ячейку дохода и установите для нее значение 8000 долларов.
• Чтобы изменить ячейку переменной, выберите ячейки C5, C6 и C10.

• Нажмите «Решить».

Ваша модель данных изменится в соответствии с условиями.

Если-иначе

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

Синтаксис: =ЕСЛИ(тест, истинный результат, ложный результат)

Если-Ошибка

Функция Excel ЕСЛИОШИБКА возвращает альтернативный результат, если формула выдает ошибку, и ожидаемый результат, если ошибки не обнаружено.

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

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

С помощью функции ЕСЛИОШИБКА можно добавить сообщение, если формула дает ошибку.

Сводные таблицы

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

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

Пример данных

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

Вставка сводных таблиц

Чтобы вставить сводную таблицу на лист, выполните следующие действия:

• Щелкните любую ячейку в наборе данных.
• На вкладке Вставка в группе Таблицы щелкните Сводная таблица.

Появится диалоговое окно. Excel автоматически выберет ваш набор данных. Он также создаст новый рабочий лист для вашей сводной таблицы.

• Нажмите «ОК». Затем он создаст рабочий лист сводной таблицы.

Поле для перетаскивания

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

• Поле покупателя в область строк.
• Поле элементов в область значений.

Эксель VBA

VBA расшифровывается как Visual Basic Analysis. Excel VBA — это управляемый событиями язык программирования Microsoft для приложений Office. Макросы — это то, что использует большинство людей, пишущих код VBA.

Включить параметр разработчика в Excel

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

• Щелкните правой кнопкой мыши в любом месте ленты, а затем выберите параметр «Настроить ленту».

• Перейдите в раздел «Настройка ленты» и установите флажок «Разработчик».

Интерфейс редактора VBA

Вы можете открыть интерфейс VBA с помощью сочетания клавиш ALT + F11 или перейти на вкладку «Разработчик» и щелкнуть Visual Basic.

Создание командной кнопки и назначение макроса командной кнопке

После включения вкладки разработчика и ознакомления с редактором VBA приступим к созданию макроса с помощью командной кнопки.

Чтобы разместить командную кнопку на рабочем листе, с которым вы работаете, вам необходимо выполнить шаги, указанные ниже:

• Перейдите на вкладку «Разработчик» > «Вставка» > «Элементы управления ActiveX» > «Командная кнопка».

• Перетащите командную кнопку на рабочий лист.

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

• Щелкните правой кнопкой мыши командные кнопки и выберите «Просмотреть код».

• Добавьте следующие строки кода, показанные ниже.

• Закройте редактор VBA и нажмите кнопку команды на рабочем листе. Не забудьте отменить выбор режима дизайна.

Индекс

и соответствие

Это расширенная функция Excel. Функция ПОИСКПОЗ предназначена для возврата позиции значения в указанном диапазоне, а функция ИНДЕКС возвращает конкретное значение, присутствующее в одномерном диапазоне.

Функция ПОИСКПОЗ возвращает позицию искомого идентификатора. Функция ИНДЕКС вернет значение зарплаты, соответствующее должности.

Функция смещения

Функция СМЕЩ возвращает ссылку на диапазон ячеек, который представляет собой заданное количество строк и столбцов из ячейки или диапазона ячеек.

Синтаксис: OFFSET(ссылка, строки, столбцы, [высота], [ширина])

Пример:

Рассмотрим следующие данные: 

 Для ссылки на C4, начинающейся с A1, ссылка – это A1, строки – 3, а столбцы – 2:

.

СУММ Функция со смещением

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

Функция OFFSET возвращает диапазон 1×2, 8 строк ниже ячейки A2 и 1 столбец справа от ячейки A2. Затем функция СУММ вычисляет сумму этого диапазона.

Получите опыт работы с новейшими инструментами и методами бизнес-аналитики с помощью магистерской программы бизнес-аналитика. Зарегистрируйтесь сейчас!

Заключение

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

Ускорьте свою карьеру аналитика с помощью востребованных сегодня и мощных навыков Microsoft Excel, включая обучение использованию Power BI с курсом Simplilearn Business Analytics with Excel.

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

У вас есть к нам вопросы? Не стесняйтесь задавать их в разделе комментариев «Расширенного учебника по Excel», и наши специалисты оперативно ответят на них!

Как использовать Excel If Function: подробное руководство

MS Excel — это мощный инструмент, помогающий повысить производительность пользователя при работе с данными. Это связано с широким набором функций, присутствующих в Excel. Функция ЕСЛИ в Excel — одна из таких функций, которая позволяет легко классифицировать данные в соответствии с заданным условием. Существует пять вариантов функции ЕСЛИ в Excel, которые мы будем обсуждать.

Как использовать функцию Excel If?

Функция ЕСЛИ — это встроенная функция Excel, которая проверяет условие. Если условие выполнено, возвращается истинное значение; в противном случае возвращается ложное значение. Синтаксис следующий:

Давайте рассмотрим пример, чтобы лучше понять это.

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

Формула  ЕСЛИ(G2=»В ожидании»,»ДА»,»НЕТ»)   проверяет, находится ли заказ в состоянии ожидания. Если это так, выполняется ИСТИННАЯ часть оператора (т. е. «ДА»), в противном случае выполняется значение ЛОЖЬ, отображающее «НЕТ».

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

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

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

• ЕСЛИ(С2>20, С2*0,05, С2*0,07)

Формула проверяет, превышает ли значение в ячейке C2 20. Если значение TRUE, значение в ячейке C2 умножается на 0,05 (или пять процентов). Если FALSE, оно умножается на 0,07 (или семь процентов).

2. Условием внутри ЕСЛИ может быть любая функция. Например, ЕПУСТО, ЕЧИСЛО, СРЗНАЧ, СУММА и т. д.

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

• ЕСЛИ(ПУСТО(C2), «ИСТИНА», «ЛОЖЬ»)

 Формула возвращает ИСТИНА, если ячейка C2 пуста, и ЛОЖЬ в противном случае.

Что такое функция IFNA?

Функция IFNA относится к группе функций проверки ошибок в Excel. Эта функция помогает обрабатывать ошибки #N/A.

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

Сначала выполняется формула, и функция IFNA проверяет, является ли результат ошибкой #N/A. Это ошибка «нет доступного значения», которая возникает, когда значение не может быть найдено.

В этом случае возвращается значение_если_ошибка. Если ошибка #Н/Д не возникает, будет возвращен результат формулы.

Рассмотрим следующий пример:

• Чтобы понять функцию IFNA, давайте сначала разберемся с функциями, которые генерируют ошибку #N/A. Эти функции включают ВПР, ГПР и т. д. 
• ВПР — это функция, помогающая искать значение в таблице по вертикали. Синтаксис этой функции: ВПР (значение, таблица, индекс_столбца, [диапазон_просмотра])

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

Мы будем использовать следующую формулу: IFNA(VLOOKUP(B9,A2:F5,6,TRUE),» NOT FOUND»)

• Функция IFNA сначала выполняет функцию ВПР, которая генерирует ошибку #Н/Д, если значение не найдено. Поэтому мы будем использовать функцию IFNA вместе с VLOOKUP.
• В случае ошибки #Н/Д выполняется и отображается альтернативное значение (значение_если_ошибка).

Поскольку значение в этом примере недоступно, мы получим сообщение «НЕ НАЙДЕНО».

Что такое функция ЕСЛИОШИБКА?

ЕСЛИОШИБКА также относится к группе функций проверки ошибок в Excel. Это разновидность функции ЕСЛИ. Функция ЕСЛИОШИБКА помогает распознавать ошибки и эффективно их обрабатывать.

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

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

Демонстрация, показанная ниже, дает более четкое представление о функции ЕСЛИОШИБКА.

В этом примере используется формула ЕСЛИОШИБКА(C1/C2, «Ошибка в расчете»)

Сначала выполняется C1/C2. Если это приведет к какой-либо ошибке, будет отображено альтернативное значение. (т. е. «Ошибка в расчете»).

Как использовать вложенные ЕСЛИ в Excel

Вложенную функцию ЕСЛИ можно использовать для проверки нескольких условий. Он состоит из одной функции ЕСЛИ внутри другой функции ЕСЛИ. Синтаксис:

• Выполняется первая функция ЕСЛИ и оценивается условие1
• Если условие1 истинно, то выполняется истинное_значение1.
• Значение false_value1 встроено в следующую функцию ЕСЛИ. Следовательно, если условие1 ложно, следующая функция ЕСЛИ выполнит
• Условие2 проверено, и true_value2 возвращается, если условие2 истинно. В противном случае возвращается false_value2.

При необходимости можно продолжить вложение до семи функций ЕСЛИ.

В следующей демонстрации показано, как работает вложенный оператор IF:

IF(B4=»Красота»,»7%», IF(B4=»Электроника», IF(B4=»Спорт»,»4%»,»Не применимо»)))

• В таблице справа показан процент налога для каждой линейки продуктов. Мы формулируем это с помощью вложенной функции ЕСЛИ.
• Мы проверяем, является ли значение ячейки «Красота», которое возвращает «7%», если TRUE.
•  Если значение FALSE, значение ячейки проверяется на «Электроника», в котором отображается «5%», если TRUE.
• В противном случае он снова проверит, является ли значение ячейки «Спорт». На этот раз отображается «4%», если ИСТИНА, и «Неприменимо», если ЛОЖЬ.

Функция IFS

Функция IFS устраняет сложность использования вложенных ЕСЛИ в Excel. Он использует более короткий и простой синтаксис, ориентированный на несколько критериев.

Эта функция доступна только в Excel 2019 и Excel для Office 365.

Синтаксис для IFS:

Функция IFS состоит из пары условие-значение. Он проверяет условия по порядку и возвращает значение, соответствующее первому результату TRUE. Эта функция может проверять до 127 условий.

Например:

=IFS(C2=»Бакалея»,»5%»,C2=»Электроника»,»12%»,C2=»Автомобили»,»28%»)

• В этом примере проверяется значение C2.
• Если C2 содержит значение «Бакалея», ставка налога будет отображаться как «5%».
• В противном случае снова проверяется значение в C2, чтобы убедиться, что оно содержит «Электроника». Если TRUE, налоговая ставка будет «12%».
• В противном случае значение C2 окончательно проверяется с «Автомобили». Если это условие выполнено, отображается «18%».
• Если ни одно из условий не выполняется, эта функция вернет ошибку #Н/Д.

Подробнее об Excel IF

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

Вот несколько примеров, которые показывают, как комбинировать функцию ЕСЛИ Excel с другими:

Функция ЕСЛИ с И

Синтаксис для этого будет:

Эта функция проверит, выполнены ли все \условия (условие1, условие2 и т. д.), и только после этого будет выполнено значение true_value. Даже если одно условие не выполнено, false_value будет выполнено.

Например:

ЕСЛИ(И(B2>5,C2<5),"Отлично","Удовлетворительно")

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

Функция ЕСЛИ с ИЛИ

Синтаксис для этого будет следующим:

Если выполняется хотя бы одно условие из всех условий (условие1, условие2 и т. д.), то будет выполнено значение true_value. Если все условия FALSE, то выполняется false_value.

Например:

ЕСЛИ(ИЛИ(B2=»Фрукты»,B2=»Овощи»),»Бакалея»,»Другое»)

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

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

Заключение

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