Рекомендуем, 2019

Выбор редакции

Логические формулы Excel: 8 простых операторов IF для начала работы

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

Оператор IF также известен как логическая формула: IF, then else . Если что-то истинно, , затем сделайте это, else / иначе сделайте это. Например, , если идет дождь, , а затем закрыть окна, else / иначе оставить окна открытыми.

Microsoft Office Home and Business 2016See iton Amazon

Синтаксис (или структура предложения, то есть способ, которым команды организованы в формуле) оператора Excel IF: = IF (logic_test, value_if true, value_if_false). Операторы IF используются во всех языках программирования и, хотя синтаксис может незначительно отличаться, функция дает те же результаты.

[Дальнейшее чтение: вашему новому компьютеру нужны эти 15 бесплатных, отличных программ]

Запомнить: Изучение функций Excel / формулы и то, как они работают, являются первыми шагами по использованию Visual Basic, языка программирования Microsoft, управляемого событиями. Ниже приведены пять простых инструкций IF, которые помогут вам начать работу.

Прошлые уведомления

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

A. Введите формулу: = СЕГОДНЯ () в ячейке A1, которая отображается как текущая дата.

B. Введите формулу: = IF (A4-TODAY ()> 30, «Прошлое», «ОК») в ячейке B4.

На английском языке эта формула означает: Если дата в ячейке A4 минус сегодняшняя дата больше 30 дней, затем введите слова «Прошлое» в ячейке B4, еще / иначе введите слово «ОК». Скопируйте эту формулу с B4 на B5 на B13.

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

Тест на защиту от переполнения / неудачи

Сертификация LEGAPARD Oregon - это пропуск / Fail test, который требует от участников соблюдения минимального количества квалификаций для прохождения. Десятки менее 70 процентов терпят неудачу, и эти баллы, превышающие это, проходят. В столбце A указаны имена участников; столбец B показывает их оценки; и столбец C показывает, прошли ли они или не прошли курс. Информация в столбце C достигается с помощью оператора IF.

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

A. Введите эту формулу в ячейку C4: = IF (B4 <70, "FAIL", "PASS") . Это означает, что если оценка в B4 меньше 70, , то введите слово FAIL в ячейку B4, else / иначе введите слово PASS . Скопируйте эту формулу с C4 до C5 по C13.

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

Комиссионные по продажам и бонусам

Wilcox Industries выплачивает своим сотрудникам по продажам 10-процентную комиссию за все продажи превышают 10 000 долларов США. Продажи ниже этой суммы не получают бонуса. Имена сотрудников по продажам перечислены в столбце A. Введите ежемесячные продажи каждого человека в столбце B. Колонка C умножает общий объем продаж на 10 процентов, а в столбце D отображается сумма комиссии или слова «Без бонуса». Теперь вы можете добавить подходящие комиссии в колонку D и узнать, сколько денег было выплачено в бонусах за август.

A. Введите эту формулу в ячейку C4: = SUM (B4 * 10%) , затем скопируйте с C4 на C5 на C13. Эта формула вычисляет 10 процентов продаж каждого человека.

B. Введите эту формулу в ячейку D4: = IF (B4> 10000, C4, «Без бонуса») , затем скопируйте с D4 на D5 на D13. Эта формула копирует процент от столбца C для продаж более 10 000 долларов США или слова «Без бонуса» для продажи менее 10 000 долларов США в столбце D.

C. Введите эту формулу в ячейку D15: = SUM (D4: D13) . Эта формула суммирует суммарные бонусные доллары за текущий месяц.

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

Дальнейшее обучение Excel

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

Coursera - Навыки Excel для бизнеса: Essentials

EDX - Анализ и визуализация данных с помощью Excel

Lynda.com - Excel: сценарий Планирование и анализ

Udacity - Основы Excel

Преобразование оценок в оценки с помощью вложенных операторов IF

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

Имена учеников перечислены в столбце A; числовые оценки в столбце B; и графы букв в столбце C, которые вычисляются вложенным оператором IF.

A. Введите эту формулу в ячейку C4: = IF (B4> 89, «A», IF (B4> 79, «B», IF (B4> 69, «C», IF (B4> 59, «D») , «F»)))) , затем скопируйте с C4 на C5 на C13.

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

Используйте вложенный оператор IF для преобразования числовых оценок в классы букв.

Определите скользящие торговые шкалы продаж с вложенными операторами IF

В этом последнем примере используется другой вложенный оператор IF для расчета нескольких комиссионных процентов на основе скользящей шкалы, а затем составляет комиссионные за месяц. Синтаксис для вложенного оператора IF таков: данные IF являются истинными, а затем делают это; IF данные истинны, тогда сделайте это; IF данные истинны, тогда сделайте это; иначе / иначе это сделать. Имена продавцов указаны в колонке А; ежемесячные продажи каждого человека указаны в столбце B; и комиссии находятся в столбце C, которые вычисляются вложенным оператором IF, а затем суммируются в нижней части этого столбца (в ячейке C15).

A. Введите эту формулу в ячейку C4: = IF (B4 <5000, B4 * 7%, IF (B4 <8000, B4 * 10%, IF (B4 <10000, B4 * 12,5%, B4 * 15%)) ) , затем скопируйте с C4 на C5 на C13.

B. Введите эту формулу в ячейку C15: = SUM (C4: C13) . Эта формула суммирует общие комиссионные доллары за текущий месяц.

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

Рассчитайте цену продукта на основе количества

Многие розничные торговцы и большинство оптовиков предоставляют клиентам цену перерыв в зависимости от количества. Например, оптовый торговец может продавать от 1 до 10 футболок за 10,50 долларов США каждая и от 11 до 25 футболок за 8,75 долларов США каждый. Используйте следующую формулу оператора IF, чтобы определить, сколько денег вы можете сэкономить, купив оптовые продукты для вашей компании навалом.

1. Введите заголовок Оптовые цены в ячейку A3. Введите заголовки Qty и Стоимость в ячейки B3 и C3.

2. Введите разрывы количества в ячейках от B4 до B9.

3. Введите ценовые разрывы в ячейках от C4 до C9.

Это матрица количества / цены для вашего оптового бизнеса.

Мир ПК / JD Sartain

Рассчитайте цены на продукцию в зависимости от количества.

4. Затем введите заголовки Qty Ordered и Qty Cost в ячейки B11 и C11.

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

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

7. И последнее, введите следующую формулу в соседние ячейки столбца C:

= B12 * IF (B12> = 101,3,95, IF (B12> = 76,5,25, IF (B12> = 51,6,3, IF (B12> = 26,7,25, IF (B12> = 11,8, IF (B12> = 1,8,5, ""))))))

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

8. Как только заказ будет завершен, создайте общую строку: в столбце A (в данном случае A20) введите TOTAL Invoice Amount . В столбце C (в данном случае C20) используйте функцию SUM для общего столбца.

Теперь, когда все формулы на месте, все, что вы должны делать каждую неделю (или месяц), вводите новые количества в столбец B, а Excel - все остальное. Если цены меняются, просто измените матрицу в верхней части таблицы, и все остальные формулы и итоговые значения будут автоматически скорректированы.

Использование других функций внутри оператора IF

В этом примере мы будем вставлять два IF и вставить всю функцию SUM в ту же формулу. Цель состоит в том, чтобы определить, являются ли цели человека меньше, равны или превышают месячную лимиту компании.

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

2. Введите следующие заголовки в ячейках от A3 до G3: Цели , 1st Qtr , 2nd Qtr , 3d Qtr , 4th Qtr , Цели или колпачок? и Бонус .

3. Введите число лучших пяти человек в количестве от 4 до 4 баллов. Введите квартальные суммы тех же самых лиц в ячейках от B4 до E8.

PC World / JD Sartain

Использование других функций INSIDE Statement If

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

5. Введите эту формулу в ячейки F4 - F8: = IF (A4B1, SUM (B4: E4,0)))

6. Введите эту формулу в ячейках от G4 до G8: = IF (F4 = B1, F4 * 25%, 0))

Используйте IF, чтобы избежать деления на ноль

В Excel, если вы попытаетесь разделить любое число на ноль, сообщение об ошибке, которое отображается в Excel: # DIV / 0!

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

1. Чтобы отслеживать собранные в месяц деньги сотрудниками фонда Фрайар Так, откройте электронную таблицу и создайте следующие заголовки:

Доллары в A1, Доллары на одного работника в B1, Доллары Влево в C1. 2. Введите сумму в долларах США в столбец A и количество сотрудников, работающих в день в столбце B. Столбец B будет содержать некоторые нули, потому что в некоторые дни никто не работает (но пожертвования по-прежнему поступают через почту).

2. Введите эту формулу в C2-C11: = IF (B20, A2 / B2,0). Используя оператор IF, чтобы Excel не разделил ни один из средств, собранных нулем, вычисляются только действительные числа.

PC World / JD Sartain

08 Используйте IF, чтобы избежать ошибок при делении на нуль

3 , Вы можете использовать другой оператор IF для определения нулей в столбце B, затем суммировать соответствующие доллары в столбце A, которые затем будут поделены поровну между всеми сотрудниками. Скопируйте эту формулу в D2 - D11: = IF (B4 = 0, A4,0).

4. Затем переместите курсор на B12 и введите эту формулу = MAX (B2: B11), чтобы найти наибольшее количество рабочих в столбце B, что равно восьми.

5. Переместите курсор на D12 и введите следующую формулу: = SUM (D2: D11) / B12. Ответ составляет $ 392.50 для каждого из восьми сотрудников, работающих в Фандрайзерах Фрайар Так.

Примечание редактора: Эта статья была обновлена ​​с ее первоначальной публикации 8 сентября 2015 года.

Top