Как пользоваться если в excel: Функция ЕСЛИ — вложенные формулы и типовые ошибки

Использование анализа «что если» в Excel на примере

Анализ «Что Если» в Excel позволяет попробовать различные значения (сценарии) для формул.
Следующий пример поможет Вам освоить Анализ «что если»  быстро и легко.

Предположим, у вас есть книжный магазин и есть 100 книг на продажу. Вы продаете определенный % книг по самой высокой цене в $ 50 и определенный % книг по более низкой цене $ 20.

Если вы продаете 60% книг по самой высокой цене, ячейка D10 вычисляет общую прибыль в размере 60 * $ 50 + 40 * $ 20 = $ 3800.

Скачать рассматриваемый пример Вы можете по этой ссылке: Пример анализа «что если» в Excel.

Создание различных сценариев

Что будет, если Вы продадите 70% книг по высокой цене? А что будет, если Вы продадите 80% книг? Или 90%, или 100%? Каждый другой процент продажи книг — это различный сценарий.
Вы можете использовать «Диспетчер сценариев» для создания этих сценариев.

Примечание: Вы можете просто ввести другой процент в ячейку C4, что бы увидеть результат в ячейке C10. Однако, Анализ «что если» позволит Вам сравнить результаты различных сценариев.

Итак, приступим.

1. На вкладке Данные выберите Анализ «что если» и выберите Диспетчер сценариев из списка.
Откроется диалоговое окно Диспетчер сценариев.

 2. Добавьте сценарий, нажав на кнопку Добавить.

3. Введите имя (60% книг по высокой цене), выберите ячейку C4 (% книг, которые продаются по высокой цене) для изменяемой ячейки и нажмите на кнопку OK.

4. Введите соответствующее значение 0,6 и нажмите на кнопку OK еще раз.

5. Далее, добавьте еще 4 других сценария (70%, 80%, 90% и 100% соответсвенно).

И, наконец, ваш Диспетчер сценариев должен соответствовать картинке ниже:

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

Отчет по сценариям

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

1. Кликните по кнопке «Отчет» в Диспетчере сценариев.

2. Далее, выберите ячейку C10 (итого выручка) в качестве ячейки результата и нажмите ОК.

Результат:

Вывод: Если вы продаете 70% книг по высокой цене, то Вы получите общую выручку в размере $ 4100, если Вы продаете 80% книг по высокой цене, то Вы получаете общую прибыль в размере $ 4400 и т.д. Вот как легко можно использовать Анализ «что если» в Excel.

Подписывайтесь на нас в социальных сетях, оставляйте комментарии к статье. Надеюсь пример использования анализа «что если» в Excel Вам понравился.

Знайте эти основные формулы, чтобы научиться пользоваться Excel

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

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

Индекс

• 1 Что такое формула в Excel?
• 2 Формулы, которые нужно знать, чтобы начать изучать Excel
  • 2.1 Основные математические операции
    • 2.1.1 Сума
    • 2.1.2 вычитание
    • 2.1.3 умножение
    • 2.1.4 деление
  • 2.2 СРЕДНИЙ
  • 2.3 SI
  • 2.4 БУДЕТ СЧИТАТЬ
  • 2. 5 ГИПЕРССЫЛКА
  • 2.6 ПОИСКV

Что такое формула в Excel?

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

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

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

Формулы, которые нужно знать, чтобы начать изучать Excel

Основные математические операции

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

Сума

Чтобы выполнить сумму Excel, мы должны ввести следующую формулу:

=СУММ(A1:A2) или =СУММ(2+2)

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

вычитание

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

= А1 – А3

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

умножение

Как и вычитание, умножение в Excel основано на использовании звездочки в качестве символа. Таким образом, для умножения двух или более элементов у нас будет что-то вроде:

= А1 * А3

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

деление

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

= А1/А3

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

СРЕДНИЙ

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

Чтобы использовать его, вы должны ввести:

=СРЕДНЕЕ(A1:B3)

SI

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

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

= ЕСЛИ (Условие, Значение, если оно истинно, Значение, если оно неверно)

Таким образом, если проходной балл равен 50, у нас будет такой пример:

= ДА(B2>=50, ПРОШЕЛ, НЕ ПРОШЕЛ)

БУДЕТ СЧИТАТЬ

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

Для этого используется следующий синтаксис:

СЧЁТ (A1:B3)

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

ГИПЕРССЫЛКА

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

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

=ГИПЕРССЫЛКА(ссылка, текст)

=ГИПЕРССЫЛКА(«www.windowsnoticias.com», «Познакомьтесь с нашим сайтом»)

ПОИСКV

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

=ПРОСМОТР(ПОИСК ЗНАЧЕНИЯ, ДИАПАЗОН ЯЧЕЙ, НОМЕР СТОЛБЦА, ГДЕ НАХОДЯТСЯ Искомые данные, Упорядочено)

В этом смысле первым вводимым параметром является искомое значение, которое является не чем иным, как ссылкой, которую мы будем использовать для поиска рассматриваемых данных. Затем наступает очередь диапазона ячеек, в которых будет выполняться поиск, а затем добавляется номер столбца, в котором находятся данные, которые мы хотим найти.. Столбцы начинают нумероваться с того, который вы выбрали. Наконец, Sorted относится к тому, будет ли поиск возвращать точное или приблизительное совпадение. Для точного совпадения введите FALSE.

3.2 Функция ЕСЛИ – Excel для принятия решений

Цели обучения

• Используйте функцию ЕСЛИ для проведения логических сравнений между значением и тем, что вы ожидаете.
• Создайте вычисление ВПР для поиска информации в таблице.
• Понимать сообщения об ошибках.
• Узнайте, как вводить и форматировать функции даты/времени.

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

Функция ЕСЛИ — одна из самых популярных функций в Excel. Это позволяет вам делать логические сравнения между значением и тем, что вы ожидаете. В своей простейшей форме функция ЕСЛИ говорит что-то вроде:
Если значение в ячейке такое, как вы ожидаете (истина), сделайте это. Если нет – сделайте это.

Функция ЕСЛИ имеет три аргумента:

• Логический тест — Здесь мы можем проверить, соответствует ли значение в выбранной ячейке ожидаемому. Вы можете использовать что-то вроде «B7=14», или «B7>12», или «B7<6»

.

• Value_if_true – Если требования в логическом тесте соблюдены – если B7 равно 14 – то говорят, что это верно. Для этого аргумента вы можете ввести текст — «Верно» или «В рамках бюджета!» Или вы можете вставить вычисление, например B7*2 (если B7 действительно равно 14, умножьте его на 2). Или, если вы хотите, чтобы Excel вообще ничего не помещал в ячейку, введите «» (две кавычки).
• Value_if_false – Если требования в логическом тесте не выполняются – если B7 не равно 14 – то говорят, что он ложный. Здесь вы можете ввести те же инструкции, что и выше. Допустим, вы вводите здесь двойные кавычки. Тогда, если B7 не равно 14, в этой ячейке ничего отображаться не будет.

В столбце Q мы хотели бы, чтобы Excel сообщал нам, проходит ли учащийся класс или не проходит его. Если учащийся набирает 70% или выше, он/она считается пропущенным. Но если он/она набирает менее 70%, он/она терпит неудачу.

1. 1. Убедитесь, что Q5 является вашей активной ячейкой.
   2. На вкладке «Формулы» в библиотеке функций найдите функцию ЕСЛИ в раскрывающемся меню «Логика» (см. Рисунок 3.9 ).
      Рисунок 3.9 Функция ЕСЛИ

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

1. Щелкните в поле Логический тест . Нам нужно проверить, меньше ли баллов студента, чем .7 . Итак, в этом поле введите P5<.7
2. Щелкните в поле Value_if_true . Если оценка учащегося меньше 0,7, то он не справляется с классом. В этом поле введите Fail .
3. Щелкните в поле Value_if_false . Если балл учащегося НЕ ниже 0,7, то он считается сдавшим класс. В этом поле введите Pass .
4. Убедитесь, что ваше диалоговое окно соответствует Рисунок 3.10 .
   Рисунок 3.10 Диалоговое окно функции IF

   Пока мы здесь, давайте взглянем на диалоговое окно. Обратите внимание, что когда вы щелкаете в каждом поле, Excel дает вам краткое объяснение содержимого (посередине под полями). В нижнем левом углу вы можете увидеть результаты расчета. В этом случае DeShae проходит класс. Ниже находится ссылка на справку по этой функции . Выбрав эту ссылку, вы перейдете к справке Excel по этой функции с подробной информацией о том, как она работает.

5. После того, как вы ввели необходимые аргументы и проверили их правильность, нажмите OK. (Текст Pass должен отображаться в Q5, потому что DeShae сдает класс.)
6. Используйте маркер заполнения, чтобы скопировать функцию ЕСЛИ вниз по строке 24.
7. Нажмите Q5. Когда вы посмотрите на строку формул, вы увидите вычисление ЕСЛИ: = ЕСЛИ (P5<0,7, «Неудачно», «Пройдено») .
8. Обратите внимание на шаблон формулы в ваших строках. Сколько студентов сдали или не сдали экзамен?

Key Takeaway

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

3.2 Logical and Lookup Functions by Noreen Brown and Mary Schatz, PCC.EDU, лицензия CC BY 4.0

Media Attributions

• рисунок-3-9
• рисунок-3-10

Как использовать функцию ЕСЛИ в Excel для начинающих и практический файл

Последнее обновление 8 августа 2022 г. Anne Hennegar

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

Что такое функция ЕСЛИ?

Функция ЕСЛИ — одна из самых простых и полезных логических функций. Он может заполнить элементы ячейки для вас на основе оценки условия, такого как содержимое ячейки и логические операторы. Что привлекает в этой функции, так это то, что ее можно использовать с другими функциями для обработки более сложных сценариев. Вы можете думать об этом как о стандартном блоке формулы, и вы можете найти его в категории Logical .

В диалоговом окне мастера можно заполнить 3 Аргументы функций или элементы данных, но вы можете использовать панель формул, как только освоите ее. Это самый простой способ выучить формулу Excel, потому что вы можете видеть, возвращает ли она ожидаемый результат. Например, в левом нижнем углу диалогового окна есть строка « Результат формулы = ».

Пустое диалоговое окно функции ЕСЛИ с пустым результатом формулы

Аргументы функции ЕСЛИ

Несмотря на то, что у меня не было Microsoft Excel, мои родители регулярно использовали этот тип логики ЕСЛИ при расчете моего пособия. Их версия гласила:

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

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

Настройка функции ЕСЛИ

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

Например, я мог бы создать электронную таблицу со списком работ, необходимых для получения пособия. Если работа по дому была завершена (истинная ситуация), к надбавке будет применено значение. Если работа не была завершена (ЛОЖНАЯ ситуация), ничего не будет добавлено.

Эти примеры отмечены метками [1] и [2] на снимке экрана ниже.

Примеры логических тестов

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

ЕСЛИ ячейка B2 равна «Y» , затем используйте значение Rate из ячейки C2 (3,00 доллара США) в D2 .

ЕСЛИ ячейка B2 не равна «Y» , то поместите 0 в ячейку D2 .

Как видно из этого примера, логическое условие ЕСЛИ равно ИСТИНА или ЛОЖЬ . А мусор выносить полезно.

Операторы сравнения

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

9 2

Как ввести аргументы функции ЕСЛИ

1. Щелкните ячейку электронной таблицы, в которой вы хотите использовать формулу Excel.
2. На вкладке Формулы щелкните Вставить функцию…
3. В диалоговом окне Вставить функцию введите « if ».

Примечание : в Office 365 теперь есть кнопка Логический на вкладке Формулы . Вы можете выбрать IF из выпадающего меню.

4. Убедитесь, что курсор находится в текстовом поле Logical_test .
5. Щелкните ячейку электронной таблицы, которую вы хотите оценить. Excel заполнит ссылку на ячейку, например «B2».
6. Добавьте знак равенства = и желаемое значение в кавычках . Например = «Y».
7. В поле Value_if_true введите значение, которое вы хотели бы ввести в свою ячейку, если B2 равно «Y». В нашем примере я щелкну ячейку C3.
8. В поле Value_if_false : введите значение, которое должна иметь ячейка, если в ячейке B2 нет «Y». Я введу 0. Я мог бы оставить это поле пустым, но ячейка будет показывать «ЛОЖЬ».
9. Просмотрите диалоговое окно, чтобы убедиться, что значение Результат формулы = (метка [1] ниже) соответствует вашим ожиданиям. Если нет, проверьте, не отображаются ли какие-либо ошибки справа от полей (метка [2] ниже).

Пример заполненной формулы ЕСЛИ и результата

10. Щелкните OK .
11. Скопируйте формулу в другие ячейки столбца.

✪ Совет : Хотя поле Value_if_false является необязательным, лучше указать значение. В противном случае Excel будет использовать ЛОЖЬ в значении ячейки.

Excel IF с числовыми значениями

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

Excel является гибким, когда речь идет об операторах ЕСЛИ, и может оценивать больше, чем просто «Y» или «N». Например, если мы преобразуем наш предыдущий Done? в столбец % Done с числом, мы можем учесть эти новые требования, например: Выполняется в колонке B. Если количество выполненных работ больше 0,5, к надбавке применяется пропорциональная сумма.

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

Использование числового значения вместо значений Y/N

Советы по устранению неполадок

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

1. Текстовая строка не заключена в кавычки.