Работа в excel с если: ЕСЛИ (функция ЕСЛИ)

Содержание

ЕСЛИ (функция ЕСЛИ)

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

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

Например, функция =ЕСЛИ(C2=”Да”;1;2) означает следующее: ЕСЛИ(С2=”Да”, то вернуть 1, в противном случае вернуть 2).

Синтаксис

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

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

Например:

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

Описание

лог_выражение    (обязательно)

Условие, которое нужно проверить.

значение_если_истина    (обязательно)

Значение, которое должно возвращаться, если

лог_выражение имеет значение ИСТИНА.

значение_если_ложь    (необязательно)

Значение, которое должно возвращаться, если лог_выражение имеет значение ЛОЖЬ.

Простые примеры функции ЕСЛИ

В примере выше ячейка D2 содержит формулу: ЕСЛИ(C2 = Да, то вернуть 1, в противном случае вернуть 2)

В этом примере ячейка D2 содержит формулу: ЕСЛИ(C2 = 1, то вернуть текст “Да”, в противном случае вернуть текст “Нет”)

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

В примере выше функция ЕСЛИ в ячейке D2 означает: ЕСЛИ(C2 больше B2, то вернуть текст “Превышение бюджета”, в противном случае вернуть текст “В пределах бюджета”)

На рисунке выше мы возвращаем не текст, а результат математического вычисления. Формула в ячейке E2 означает: ЕСЛИ(значение “Фактические” больше значения “Плановые”, то вычесть сумму “Плановые” из суммы “Фактические”, в противном случае ничего не возвращать).

В этом примере формула в ячейке F7 означает: ЕСЛИ(E7 = “Да”, то вычислить общую сумму в ячейке F5 и умножить на 8,25 %, в противном случае налога с продажи нет, поэтому вернуть 0)

Примечание: Если вы используете текст в формулах, заключайте его в кавычки (пример: “Текст”).

Единственное исключение — слова ИСТИНА и ЛОЖЬ, которые Excel распознает автоматически.

Распространенные неполадки

Проблема

Возможная причина

0 (ноль) в ячейке

Не указан аргумент значение_если_истина или значение_если_ложь

. Чтобы возвращать правильное значение, добавьте текст двух аргументов или значение ИСТИНА/ЛОЖЬ.

#ИМЯ? в ячейке

Как правило, это указывает на ошибку в формуле.

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

См. также

Операторы вычислений и их приоритеты в Excel

Использование вложенных функций в формуле

Использование функции ЕСЛИ для проверки ячейки на наличие символов

Видео: расширенное применение функции ЕСЛИ

Функция ЕСЛИМН (Microsoft 365, Excel 2016 и более поздних)

Усложненные функции ЕСЛИ: как работать с вложенными формулами и избежать ошибок

Обучающие видео: усложненные функции ЕСЛИ

Подсчет значений на основе одного условия с помощью функции СЧЁТЕСЛИ

Подсчет значений на основе нескольких условий с помощью функции СЧЁТЕСЛИМН

Суммирование значений на основе одного условия с помощью функции СУММЕСЛИ

Суммирование значений на основе нескольких условий с помощью функции СУММЕСЛИМН

Функция И

Функция ИЛИ

Функция ВПР

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Поиск ошибок в формулах

Логические функции

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Функция ЕСЛИ в Excel с примерами нескольких условий

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

Сопоставляются числа и/или текст, функции, формулы и т.д. Когда значения отвечают заданным параметрам, то появляется одна запись. Не отвечают – другая.

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

Синтаксис функции ЕСЛИ с одним условием

Синтаксис оператора в Excel – строение функции, необходимые для ее работы данные.

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

Разберем синтаксис функции:

Логическое_выражение – ЧТО оператор проверяет (текстовые либо числовые данные ячейки).

Значение_если_истина – ЧТО появится в ячейке, когда текст или число отвечают заданному условию (правдивы).

Значение,если_ложь – ЧТО появится в графе, когда текст или число НЕ отвечают заданному условию (лживы).

Пример:

Оператор проверяет ячейку А1 и сравнивает ее с 20. Это «логическое_выражение». Когда содержимое графы больше 20, появляется истинная надпись «больше 20». Нет – «меньше или равно 20».

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

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

Обратите внимание: оператор ЕСЛИ должен проверить не цифровой тип данных, а текстовый. Поэтому мы прописали в формуле В2= «зач.». В кавычки берем, чтобы программа правильно распознала текст.



Функция ЕСЛИ в Excel с несколькими условиями

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

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

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

Здесь оператор проверяет два параметра.

Если первое условие истинно, то формула возвращает первый аргумент – истину. Ложно – оператор проверяет второе условие.

Примеры несколько условий функции ЕСЛИ в Excel:

Таблица для анализа успеваемости. Ученик получил 5 баллов – «отлично». 4 – «хорошо». 3 – «удовлетворительно». Оператор ЕСЛИ проверяет 2 условия: равенство значения в ячейке 5 и 4.

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

Расширение функционала с помощью операторов «И» и «ИЛИ»

Когда нужно проверить несколько истинных условий, используется функция И. Суть такова: ЕСЛИ а = 1 И а = 2 ТОГДА значение в ИНАЧЕ значение с.

Функция ИЛИ проверяет условие 1 или условие 2. Как только хотя бы одно условие истинно, то результат будет истинным. Суть такова: ЕСЛИ а = 1 ИЛИ а = 2 ТОГДА значение в ИНАЧЕ значение с.

Функции И и ИЛИ могут проверить до 30 условий.

Пример использования оператора И:

Пример использования функции ИЛИ:

Как сравнить данные в двух таблицах

Пользователям часто приходится сравнить две таблицы в Excel на совпадения. Примеры из «жизни»: сопоставить цены на товар в разные привозы, сравнить балансы (бухгалтерские отчеты) за несколько месяцев, успеваемость учеников (студентов) разных классов, в разные четверти и т.д.

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

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

Исходные данные (таблицы, с которыми будем работать):

Выделяем первую таблицу. Условное форматирование – создать правило – использовать формулу для определения форматируемых ячеек:

В строку формул записываем: =СЧЕТЕСЛИ (сравниваемый диапазон; первая ячейка первой таблицы)=0. Сравниваемый диапазон – это вторая таблица.

Чтобы вбить в формулу диапазон, просто выделяем его первую ячейку и последнюю. «= 0» означает команду поиска точных (а не приблизительных) значений.

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

Выделяем вторую таблицу. Условное форматирование – создать правило – использовать формулу. Применяем тот же оператор (СЧЕТЕСЛИ).

Скачать все примеры функции ЕСЛИ в Excel

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

Как научиться работать в Microsoft Excel самостоятельно

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

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

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

Конечно, в первую очередь необходимо затронуть тему создания таблиц в Microsoft Excel, поскольку эти объекты являются основными и вокруг них строится остальная работа с функциями. Запустите программу и создайте пустой лист, если еще не сделали этого ранее. На экране вы видите начерченный проект со столбцами и строками. Столбцы имеют буквенное обозначение, а строки – цифренное. Ячейки образовываются из их сочетания, то есть A1 – это ячейка, располагающаяся под первым номером в столбце группы А. С пониманием этого не должно возникнуть никаких проблем.

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

Задайте для нее необходимую область, зажав левую кнопку мыши и потянув курсор на необходимое расстояние, следя за тем, какие ячейки попадают в пунктирную линию. Если вы уже разобрались с названиями ячеек, можете заполнить данные самостоятельно в поле расположения. Однако там нужно вписывать дополнительные символы, с чем новички часто незнакомы, поэтому проще пойти предложенным способом. Нажмите «‎ОК» для завершения создания таблицы.

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

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

Основные элементы редактирования

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

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

Использование функций Excel

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

  1. Если вы желаете объявить любую функцию в ячейке (результат обязательно выводится в поле), начните написание со знака «​=», после чего впишите первый символ, обозначающий название формулы. На экране появится список подходящих вариантов, а нажатие клавиши TAB выбирает одну из них и автоматически дописывает оставшиеся символы.

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

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

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

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

Вставка диаграмм

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

Подробнее: Как построить диаграмму по таблице в Excel: пошаговая инструкция

Элементы разметки страницы

Вкладка под названием «‎Разметка страницы» пригодится вам только в том случае, если создаваемый лист в будущем должен отправиться в печать. Здесь вы найдете параметры страницы, сможете изменить ее размер, ориентацию, указать область печати и выполнить другое редактирование. Большинство доступных инструментов подписаны, и с их использованием не возникнет никаких проблем. Учитывайте, что при внесении изменений вы можете нажать комбинацию клавиш Ctrl + Z, если вдруг что-то сделали не так.

Сохранение и переключение между таблицами

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

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

Глава 7 Работа с данными таблицы Excel

 

Глава 7 Работа с данными таблицы Excel

Строки в списке можно сортировать по значениям ячеек одного или нескольких столбцов. Строки, столбцы или отдельные ячейки в процессе сортировки переупорядочиваются в соответствии с заданным пользователем порядком сортировки. Списки можно сортировать в возрастающем (от 1 до 9, от А до Я) или убывающем (от 9 до 1, от Я до А) порядке.

Сортировка данных:

1. Выделить данные в таблице.

2. Меню Данные Сортировка

3. В окне Сортировка диапазона указать столбец, по которому будет производиться сортировка, затем ее способ (по возрастанию или по убыванию).

 

Фильтрация данных:

Фильтрация это скрытие ненужных данных и показ нужных.

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

1. Укажите ячейки в фильтруемом списке.

2. Меню Данные Фильтр Автофильтр.

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

4. Выберите значение в списке.

5. Повторите шаги 3 и 4 для введения дополнительных ограничений значений в других столбцах.

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

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

Анализ данных

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

Если команда Анализ данных отсутствует в меню Сервис, то необходимо запустить программу установки Microsoft Excel. После установки пакета анализа его необходимо выбрать и активизировать с помощью команды Настройки. Для успешного применения процедур анализа необходимы начальные знания в области статистических и инженерных расчетов, для которых эти инструменты были разработаны.

Чтобы запустить пакет анализа:

1.  В меню Сервис выберите команду Анализ данных.

2. В списке Инструменты анализа выберите нужную строку.

3. Введите входной и выходной диапазоны, затем выберите необходимые параметры.

Другие инструменты позволяют представить результаты анализа в графическом виде.

Создание диаграммы

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

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

Можно создать либо внедренную диаграмму (то есть поместить её на лист рядом с таблицей), либо лист диаграммы.

С

оздание диаграммы:

1. Выделите ячейки, содержащие данные, которые должны быть отражены на диаграмме. 

2. Если необходимо, чтобы в диаграмме были отражены и названия строк или столбцов, выделите также содержащие их ячейки.

3. Меню Вставка Диаграмма.

4. Следуйте инструкциям Мастера.

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

 

Изменение диаграммы:

1.  Выберите изменяемую диаграмму.

2. В меню Диаграмма выберите нужную строку, например Диапазон данных.

3. В окне редактирования внесите нужные изменения.

Вычисления

Автосуммирование

Сумму значений выделенных ячеек можно ввести автоматически с помощью кнопки      S  Автосуммирование

При нажатии кнопки Автосуммирование автоматически предлагается формула вычисления, например = СУММ(E5:E10). Чтобы принять формулу, нажмите клавишу Enter.

Синтаксис формулы

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

 

Ссылки на ячейку

В формуле может быть указана ссылка на ячейку. Если необходимо, чтобы в ячейке содержалось значение другой ячейки, введите знак равенства, после которого укажите ссылку на эту ячейку. Ее значение зависит от значения другой ячейки. Формула может вернуть другое значение, если изменить ячейку, на которую формула ссылается. Следующая формула умножает значение ячейки B15 на число 5. Формула будет пересчитываться при изменении значения ячейки B15.

 

 

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

Функции

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

1. Меню Вставка Функции f(x).

2. Следуйте за Мастером функций.

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

 

Сводная таблица

Сводная таблица создается с помощью Мастера сводных таблиц, используемого для размещения и объединения анализируемых данных:

1.  Меню Данные Сводная таблица.

2. Идите по шагам Мастера.

Подведение итогов в сводной таблице производится с помощью итоговой функции (например, “Сумма”, “Кол-во значений” или “Среднее”). 

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

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

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

 

Продолжим                                                                Назад

 

Как в Excel применять функцию ЕСЛИ() — Трюки и приемы в Microsoft Excel

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

Давайте начнем с простейшей возможности использования формулы. Синтаксис: ЕСЛИ (лог_выражение; значение_если истина), где лог_выражение — логическое выражение, то есть выражение возвращающее значение ИСТИНА или ЛОЖЬ (или эквивалентные численные значения: 0, если ЛОЖЬ, и любое положительное значение — ИСТИНА, и где значение_если истина — значение, возвращаемое функцией, в случае если лог_выражение имеет значение ИСТИНА.

Например, разберем следующую формулу: =ЕСЛИ(A1>1000;"много"). Логическое выражение А1>1000 используется для проверки. Допустим, вы добавите данную формулу в ячейку В1. Если логическое выражение является истинным (оно является таковым, когда число в A1 больше 1000), функция возвращает значение «много», и именно это вы увидите в ячейке В1 (в случае если А1 меньше 1000, вы увидите значение ЛОЖЬ).

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

Здесь ячейка – адрес ячейки для проверки, а отметка – определенного рода текст для вывода на экран для указания на отрицательное значение. Вот пример: =ЕСЛИ(В1 .

Немного более улучшенная версия отметки, в которой она будет зависеть от величины отрицательного значения, выглядит так: =ПОВТОР(". Это выражение умножает процентное значение на 100 (минус указан, для того чтобы сделать значение положительным) и затем использует результат для указания количества знаков для вывода. Вот итоговая формула: =ЕСЛИ(В1. Рис. 4.15 показывает, как это выглядит на практике.

Рис. 4.15. Пример использования функции ЕСЛИ()

Обработка ложного результата

Как вы можете видеть на рис. 4.15, если в функции ЕСЛИ() логическое выражение принимает отрицательное значение, функция возвращает ЛОЖЬ в качестве результата. Это не является критическим недостатком, однако делает рабочие листы как бы «тяжелее», чем если бы в результате возвращалась, например, пустая строка.

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

Например, рассмотрим следующую формулу: =ЕСЛИ(A1>1000;"много";"мало"). Если на этот раз ячейка A1 содержит число, меньшее или равное 1000, формула выведет строку «мало». Для примера с отметкой отрицательных объемов продаж (см. выше рис. 4.15) необходимо использовать следующую формулу: =ЕСЛИ(B1.

Рис. 4.16. Обработка ложного результата

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

Предотвращение деления на ноль

Как мы уже указывали ранее, функция возвращает ошибку #ДЕЛ/0!, если формула пытается поделить какое-то значение на 0. Для устранения даже возможности совершения такой ошибки вы можете воспользоваться функцией ЕСЛИ() для проверки деления.

Например, простейшее вычисление процента валовой прибыли по формуле (Доходы – Расходы)/Доходы. Для уверенности в том, что значение Доходы не равно нулю, используйте следующую формулу (предполагается, что вместо слов Доходы и Расходы будут подставлены необходимые значения): =ЕСЛИ(Доходы 0; (Доходы - Расходы)/Доходы; "Доходы равны 0!"). Если логическое выражение Доходы 0 верно, значит, произойдет вычисление по формуле. В противном случае функция вернет предупреждающее сообщение вместо ошибки.

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

Функция работы с временем (ВРЕМЯ)

         Добрый день уважаемый пользователь!

      В этой статье я научу вас применять функцию работы с временем (ВРЕМЯ). Эта функция очень удобно использовать специалистам, которые тесно связанные с временными расчётами, например, экономист нормировщик. Из личного опыта скажу, что работа с этой функцией помогла мне, когда на заводе вводили новую линию по упаковке продукции, и пришлось нормировать затраченное время на нее персоналом. Что помогло в дальнейшем для корректного расчёта времени, которое нужно что бы выполнить заказ. 

        Данная функция переводит значение секунд, минут и часов в формат времени, но если в ячейке установлен формат «Общий», то результат получится в формате дата.

       При числовом формате времени, которое вернет функция ВРЕМЯ, мы получаем десятичное число, составляющее интервал от 0 (00:00:00) до 0,99988426 (23:59:59). То есть результат формулы =ВРЕМЯ(9;15;0) мы получим как 9:15 утра или 0,3854167.

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

       = ВРЕМЯ(_часы_;_минуты_;_секунды_), где:

  • Часы – является обязательным аргументом и указывается число от 0 до 32767, которое и задает часы. В случаях, когда значение этого аргумента больше чем 23 оно разделяется на отрезки в 24 часа и остаток от произведенного деления будет аналогичен значению часов. Пример: ВРЕМЯ(33;0;0)= ВРЕМЯ(9;0;0)=0,375, то есть 09:00 утра;
  • Минуты — является обязательным аргументом и указывается число от 0 до 32767, которое и задает минуты. В случаях, когда значение этого аргумента больше чем 59, то произведется пересчёт в минуты и часы. Пример: ВРЕМЯ(0;270;0) = ВРЕМЯ(04;30;0) = 0,1875, то есть 04:30 утра;
  • Секунды — является обязательным аргументом и указывается число от 0 до 32767, которым задаются секунды. В случаях, когда значение этого аргумента больше чем 59, то произведется пересчёт в минуты, часы и секунды. Пример: ВРЕМЯ(0;0;14000) = ВРЕМЯ(03;53;20) = 0,1620370, то есть 03:53:20 ночи.

        Пример №1: Использование ссылок для формирования времени.

       При использовании формулы =ВРЕМЯ(B2;C2;D2) получим время в числовом формате 0,925833 вместо 22:13:12. Для получения значения в правильном формате, вызываем контекстное меню правой кнопкой мыши, выбираем пункт «Формат ячеек», указываем формат «Время» и в окне «Тип» нужный формат.        Пример №2: Расчёт количества секунд.

      У нас есть время 00:07:12, то есть 7 мин и 12 секунд. Узнаем сколько же секунд, содержится в этом времени.

      Для начала определим числовое значение нашего времени, оно соответствует числу 0,005.

      Следующий шаг, это определить числовое значение 1 секунды. Поможет в этом формула = 1/24/60/60.

     Теперь для получения результата нам нужна формула =F8/(1/24/60/60) которая и сосчитает, сколько же секунд и состоит в 7 мин и 12 сек, результат получим 432 секунды.       Пример №3: добавляем минуты к времени.

    У нас есть определенное время, к примеру, время отбытия автобуса происходит в 10:25 утра, длина маршрута составляет 85 мин. Необходимо узнать, во сколько произойдет прибытие автобуса?

    Для получения результата воспользуемся формулой =E10+ВРЕМЯ(;F10;), где, E10 – время отбытия, а F10 – время проезда. Результат получим 11:50:00 – время расчётного прибытия автобуса на конечную остановку.      Я надеюсь, что описание как применять функцию работы с временем (ВРЕМЯ) в Excel, вам стало более понятно, в чем рассматриваемые примеры вам должны помочь. Замечания и предложения жду от вас в комментариях, если понравилось, поделитесь с другими в соц.сетях.

      С другими функциями MS Excel вы можете ознакомиться в «Справочнике функций».

     До встречи на страницах TopExcel.ru!


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

Статья помогла? Поделись ссылкой с друзьями, твитни или лайкни!

10 формул Excel, которые пригодятся каждому / TeachMePlease

Не нужно тратить время на подсчёты в Microsoft Excel вручную, ведь существует множество формул, которые помогут быстро справиться с поставленными задачами и повысить точность ваших отчётов. Мы собрали 10 наиболее полезных формул, которые вы сможете выучить за один день.

СУММ

Формула:

=СУММ(число1; число2)

=СУММ(адрес_ячейки1; адрес_ячейки2)

=СУММ(адрес_ячейки1:адрес_ячейки6)

Англоязычный вариант: =SUM(5; 5) или =SUM(A1; B1) или =SUM(A1:B5)

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

С помощью формулы вы можете:

  • посчитать сумму двух чисел c помощью формулы: =СУММ(5; 5)
  • посчитать сумму содержимого ячеек, сссылаясь на их названия: =СУММ(A1; B1)
  • посчитать сумму в указанном диапазоне ячеек, в примере во всех ячейках с A1 по B6: =СУММ(A1:B6)

СЧЁТ

Формула: =СЧЁТ(адрес_ячейки1:адрес_ячейки2)

Англоязычный вариант: =COUNT(A1:A10)

Данная формула подсчитывает количество ячеек с числами в одном ряду. Если вам необходимо узнать, сколько ячеек с числами находятся в диапазоне c A1 по A30, нужно использовать следующую формулу: =СЧЁТ(A1:A30).

СЧЁТЗ

Формула: =СЧЁТЗ(адрес_ячейки1:адрес_ячейки2)

Англоязычный вариант: =COUNTA(A1:A10)

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

ДЛСТР

Формула: =ДЛСТР(адрес_ячейки)

Англоязычный вариант: =LEN(A1)

Функция ДЛСТР подсчитывает количество знаков в ячейке. Однако, будьте внимательны – пробел также учитывается как знак.

СЖПРОБЕЛЫ

Формула: =СЖПРОБЕЛЫ(адрес_ячейки)

Англоязычный вариант: =TRIM(A1)

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

Мы добавили лишний пробел после фразы “Я люблю Excel”. Формула СЖПРОБЕЛЫ убрала его, в этом вы можете убедиться, взглянув на количество знаков с использованием формулы и без.

ЛЕВСИМВ, ПСТР и ПРАВСИМВ

Формула:

=ЛЕВСИМВ(адрес_ячейки; количество знаков)

=ПРАВСИМВ(адрес_ячейки; количество знаков)

=ПСТР(адрес_ячейки; начальное число; число знаков)

Англоязычный вариант: =RIGHT(адрес_ячейки; число знаков), =LEFT(адрес_ячейки; число знаков), =MID(адрес_ячейки; начальное число; число знаков).

Эти формулы возвращают заданное количество знаков текстовой строки. ЛЕВСИМВ возвращает заданное количество знаков из указанной строки слева, ПРАВСИМВ возвращает заданное количество знаков из указанной строки справа, а ПСТР возвращает заданное число знаков из текстовой строки, начиная с указанной позиции.

Мы использовали ЛЕВСИМВ, чтобы получить первое слово. Для этого мы ввели A1 и число 1 – таким образом, мы получили «Я».

Мы использовали ПСТР, чтобы получить слово посередине. Для этого мы ввели А1, поставили 3 как начальное число и затем ввели число 6 – таким образом, мы получили «люблю» из фразы «Я люблю Excel».

Мы использовали ПРАВСИМВ, чтобы получить последнее слово. Для этого мы ввели А1 и число 6 – таким образом, мы получили слово «Excel» из фразы «Я люблю Excel».

ВПР

Формула: =ВПР(искомое_значение; таблица; номер_столбца; тип_совпадения)

Англоязычный вариант: =VLOOKUP (искомое_значение; таблица; номер_столбца; тип_совпадения)

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

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

  1. В первом списке данные записаны с А1 по В13, во втором – с D1 по Е13.
  2. В ячейке B17 поставим формулу: =ВПР(B16; A1:B13; 2; ЛОЖЬ)
  • B16 = искомое значение, то есть паспортные данные. Они имеются в обоих списках.
  • A1:B13 = таблица, в которой находится искомое значение.
  • 2 – номер столбца, где находится искомое значение.
  • ЛОЖЬ – логическое значение, которое означает то, что вам требуется точное совпадение возвращаемого значения. Если вам достаточно приблизительного совпадения, указываете ИСТИНА, оно также является значением по умолчанию.

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

ЕСЛИ

Формула: =ЕСЛИ(логическое_выражение; “текст, если логическое выражение истинно; “текст, если логическое выражение ложно”)

Англоязычный вариант: =IF(логическое_выражение; “текст, если логическое выражение истинно; “текст, если логическое выражение ложно”)

Когда вы проводите анализ большого объёма данных в Excel, есть множество сценариев для взаимодействия с ними. В зависимости от каждого из них появляется необходимость по‑разному воздействовать на данные. Функция «ЕСЛИ» позволяет выполнять логические сравнения значений: если что‑то истинно, то необходимо сделать это, в противном случае сделать что‑то ещё.

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

В примере с ВПР у нас был доход в столбце B и имя человека в столбце E. Мы можем поместить квоту в столбце C, а следующую формулу – в ячейку D1:

=ЕСЛИ(B1>C1; “Норма выполнена”; “Норма не выполнена”)

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

СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ

Формула: =СУММЕСЛИ(диапазон; условие; диапазон_суммирования) =СЧЁТЕСЛИ(диапазон; условие)

=СРЗНАЧЕСЛИ(диапазон; условие; диапазон_усреднения)

Англоязычный вариант: =SUMIF(диапазон; условие; диапазон_суммирования), =COUNTIF(диапазон; условие), =AVERAGEIF(диапазон; условие; диапазон_усреднения)

Эти формулы выполняют соответствующие функции – СУММ, СЧЁТ, СРЗНАЧ, если выполнено заданное условие.

Формулы с несколькими условиями – СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИМН – выполняют соответствующие функции, если все указанные критерии соответствуют истине.

Используя функции на предыдущем примере, мы можем узнать:

Формула «СУММЕСЛИ»

СУММЕСЛИ – общий доход только для продавцов, выполнивших норму.

Формула «СРЗНАЧЕСЛИ»

СРЗНАЧЕСЛИ – средний доход продавца, если он выполнил норму.

Формула «СЧЁТЕСЛИ»

СЧЁТЕСЛИ – количество продавцов, выполнивших норму.

Конкатенация

Формула: =(ячейка1&” “&ячейка2)

=ОБЪЕДИНИТЬ(ячейка1;” “;ячейка2)

За этим причудливым словом скрывается объединение данных из двух и более ячеек в одной. Сделать объединение можно с помощью формулы конкатенации или просто вставив символ & между адресами двух ячеек. Если в ячейке A1 находится имя «Иван», в ячейке B1 – фамилия «Петров», их можно объединить с помощью формулы =A1&” “&B1. Результат – «Иван Петров» в ячейке, где была введена формула. Обязательно оставьте пробел между ” “, чтобы между объединёнными данными появился пробел.

Формула конкатенации даёт аналогичный эффект и выглядит так: =ОБЪЕДИНИТЬ(A1;” “; B1) или в англоязычном варианте =concatenate(A1;” “; B1).

Кстати, все перечисленные формулы можно применять и в Google‑таблицах.

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

Функция ЕСЛИ

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

Таким образом, оператор IF может иметь два результата. Первый результат – если ваше сравнение – Истина, второй – если ваше сравнение – Ложь.

Например, = ЕСЛИ (C2 = «Да», 1,2) говорит ЕСЛИ (C2 = Да, тогда вернуть 1, иначе вернуть 2).

Синтаксис

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

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

Например:

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

Описание

logic_test (обязательно)

Состояние, которое вы хотите проверить.

value_if_true (обязательно)

Значение, которое вы хотите вернуть, если результатом logic_test является ИСТИНА.

value_if_false (необязательно)

Значение, которое вы хотите вернуть, если результатом logic_test является ЛОЖЬ.

Примеры простых IF

В приведенном выше примере в ячейке D2 написано: IF (C2 = Да, затем вернуть 1, в противном случае вернуть 2)

В этом примере формула в ячейке D2 говорит: ЕСЛИ (C2 = 1, затем вернуть Да, в противном случае вернуть Нет) Как видите, функцию ЕСЛИ можно использовать для оценки как текста, так и значений. Его также можно использовать для оценки ошибок.Вы не ограничены только проверкой того, совпадает ли одно с другим и возвращением единственного результата, вы также можете использовать математические операторы и выполнять дополнительные вычисления в зависимости от ваших критериев. Вы также можете вложить несколько функций ЕСЛИ вместе, чтобы выполнить несколько сравнений.

В приведенном выше примере функция ЕСЛИ в D2 говорит ЕСЛИ (C2 больше B2, затем возвращается «Превышение бюджета», в противном случае возвращается «В пределах бюджета»)

На приведенной выше иллюстрации, вместо того, чтобы возвращать текстовый результат, мы собираемся вернуть математическое вычисление.Таким образом, формула в E2 говорит IF (Фактическое значение больше, чем заложенное в бюджете, затем вычтите запланированную сумму из фактической суммы, в противном случае ничего не верните).

В этом примере формула в F7 говорит IF (E7 = «Да», затем рассчитайте общую сумму в F5 * 8,25%, в противном случае налог с продаж не взимается, поэтому возвращайте 0)

Примечание: Если вы собираетесь использовать текст в формулах, вам необходимо заключить текст в кавычки (например, «Текст»).Единственное исключение из этого правила – ИСТИНА или ЛОЖЬ, которые Excel распознает автоматически.

Общие проблемы

Задача

Что пошло не так

0 (ноль) в ячейке

Не было аргументов ни для значение_если_ истинно , ни для аргументов значение_если_False .Чтобы увидеть верное возвращаемое значение, добавьте текст аргумента к двум аргументам или добавьте к аргументу ИСТИНА или ЛОЖЬ.

# ИМЯ? в ячейке

Обычно это означает, что формула написана неправильно.

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

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

См. Также

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

Используйте вложенные функции в формуле

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

Видео: Расширенные функции ЕСЛИ

Функция IFS (Microsoft 365, Excel 2016 и более поздние версии)

Расширенные функции ЕСЛИ – работа с вложенными формулами и предотвращение ошибок

Обучающие видеоролики: Расширенные функции ЕСЛИ

Функция СЧЁТЕСЛИ будет подсчитывать значения на основе одного критерия.

Функция СЧЁТЕСЛИМН будет подсчитывать значения на основе нескольких критериев.

Функция СУММЕСЛИ суммирует значения на основе одного критерия.

Функция СУММЕСЛИМН суммирует значения на основе нескольких критериев.

И функция

Функция ИЛИ

Функция ВПР

Обзор формул в Excel

Как избежать неправильных формул

Обнаруживать ошибки в формулах

Логические функции

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Как составить отчет ЕСЛИ в Excel

Что такое оператор ЕСЛИ в Excel?

Оператор Excel IF проверяет данное условие и возвращает одно значение для ИСТИННОГО результата и другое значение для ЛОЖНОГО результата.Например, если общая сумма продаж превышает 5000 долларов, то для бонуса вы должны вернуть «Да» – в противном случае – «Нет» для бонуса. Мы также можем использовать функцию ЕСЛИ для оценки одной функции или включить несколько функций ЕСЛИ в одну формулу. Несколько операторов ЕСЛИ в Excel известны как вложенные операторы ЕСЛИ.

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

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

IF Formula

= IF (logic_test, value_if_true, value_if_false)

Функция использует следующие аргументы:

  1. Логический аргумент _ test (это обязательный аргумент). быть протестированным и оцененным как ИСТИНА или ЛОЖЬ.
  2. Значение _ if_true (необязательный аргумент) – значение, которое будет возвращено, если логический_тест оценивается как ИСТИНА.
  3. Value_if_false (необязательный аргумент) – значение, которое будет возвращено, если логический_тест оценивается как FALSE.

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

  • = (равно)
  • > (больше)
  • > = (больше или равно)
  • < (меньше)
  • <= (меньше или равно)
  • <> (не равно)

Как использовать функцию ЕСЛИ в Excel

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

Пример 1. Простой оператор IF Excel

Предположим, мы хотим провести очень простой тест.Мы хотим проверить, больше ли значение в ячейке C2 или равно значению в ячейке D2. Если аргумент верен, мы хотим вернуть текст, в котором говорится «Да, это так», а если это не так, мы хотим отобразить «Нет, это не так».

Вы можете точно увидеть, как работает оператор IF в Excel, в простом примере ниже.

Результат, когда истина:

Результат, когда ложь:

Загрузите простой шаблон XLS.

Пример 2 – Заявление Excel IF

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

В таблице выше мы перечислили задачи, связанные с AGM, в столбце A. Примечания содержат дату завершения. В столбце B мы будем использовать формулу, чтобы проверить, пусты ли ячейки в столбце C. Если ячейка пуста, формуле будет присвоен статус «открыта».Однако, если ячейка содержит дату, формуле будет присвоен статус «закрыто». Используемая формула:

Мы получаем следующие результаты:

Пример 3 – Excel IF Statement

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

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

Мы получаем результат ниже:

Что нужно помнить о функции ЕСЛИ

  1. Функция ЕСЛИ в Excel будет работать, если логический_тест возвращает числовое значение. В таком случае любое ненулевое значение трактуется как ИСТИНА, а ноль – как ЛОЖЬ.
  2. # ЗНАЧЕНИЕ! error – Возникает, когда данный аргумент logic_test не может быть оценен как ИСТИНА или ЛОЖЬ.
  3. Когда любой из аргументов предоставляется функции в виде массивов, функция ЕСЛИ оценивает каждый элемент массива.
  4. Если мы хотим подсчитать условия, мы должны использовать функции СЧЁТЕСЛИ и СЧЁТЕСЛИМН.
  5. Если мы хотим сложить условия, мы должны использовать функции СУММЕСЛИ и СУММЕСЛИМН.

Причины использования инструкции ЕСЛИ в Excel

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

Общие примеры включают:

  • Чтобы проверить, является ли аргумент истинным или ложным
  • Для вывода ЧИСЛА
  • Для вывода некоторого ТЕКСТА
  • Для создания условной формулы (например, результат C3 + B4, если истина и N9 -E5, если ложно)
  • Создание сценариев Анализ сценария Анализ сценария – это процесс изучения и оценки возможных событий или сценариев, которые могут произойти в будущем, и прогнозирования, которые будут использоваться в финансовом моделировании. Что такое Финансовое моделирование. Финансовое моделирование выполняется в Excel для прогнозирования финансовые показатели компании.Обзор того, что такое финансовое моделирование, как и зачем его создавать.
  • Для расчета графика долга График долга График долга включает в себя весь долг, имеющийся у предприятия, на основе его срока погашения и процентной ставки. В финансовом моделировании потоки процентных расходов или график амортизации основных средств График амортизации График амортизации требуется в финансовом моделировании для связи трех финансовых отчетов (доход, баланс, денежный поток) в Excel. в бухгалтерском учете

Щелкните здесь, чтобы загрузить образец файла Excel

Дополнительные ресурсы

Спасибо за то, что прочитали руководство CFI о том, как использовать отчет IF Excel.Потратив время на изучение и освоение этих функций, вы значительно ускорите свой финансовый анализ. Чтобы узнать больше, ознакомьтесь с этими дополнительными ресурсами CFI:

  • Расширенный курс Excel
  • Расширенное руководство по формулам Excel Расширенные формулы Excel, которые необходимо знать Эти расширенные формулы Excel очень важно знать и выведут ваши навыки финансового анализа на новый уровень. Загрузите нашу бесплатную электронную книгу Excel!
  • Excel Shortcuts ListExcel Shortcuts PC MacExcel Shortcuts – Список наиболее важных и распространенных сочетаний клавиш MS Excel для пользователей ПК и Mac, финансов и бухгалтеров.Сочетания клавиш ускоряют ваши навыки моделирования и экономят время. Изучите редактирование, форматирование, навигацию, ленту, специальную вставку, манипулирование данными, редактирование формул и ячеек и другие краткие сведения
  • Программа финансового моделирования Станьте сертифицированным аналитиком финансового моделирования и оценки (FMVA) ® Сертификация CFI по финансовому моделированию и оценке (FMVA) ® поможет вам обрести уверенность в своей финансовой карьере. Запишитесь сегодня!

Как использовать вложенные функции ЕСЛИ (WS)

В этом руководстве Excel объясняется, как вложить функцию ЕСЛИ в Excel с синтаксисом и примерами.

Описание

Функция ЕСЛИ – это встроенная функция в Excel, относящаяся к категории логической функции . Его можно использовать как функцию рабочего листа (WS) в Excel. Как функцию рабочего листа, функцию ЕСЛИ можно ввести как часть формулы в ячейку рабочего листа.

В одну формулу Excel можно вложить несколько функций ЕСЛИ. Вы можете вложить до 7 функций IF для создания сложного оператора IF THEN ELSE.

Синтаксис

Синтаксис вложенности функции ЕСЛИ:

 ЕСЛИ (условие1, значение_если_ истинно1, ЕСЛИ (условие2, значение_если_ истинное2, значение_если_ ложь2)) 

Это было бы эквивалентно следующему утверждению IF THEN ELSE:

 ЕСЛИ условие1 ТО
   value_if_true1
ELSEIF условие2 THEN
   value_if_true2
ЕЩЕ
   value_if_false2
КОНЕЦ IF 

Параметры или аргументы

состояние
Значение, которое вы хотите проверить.
value_if_true
Значение, которое возвращается, если условие оценивается как ИСТИНА.
значение_if_false
Значение, которое возвращается, если условие оценивается как ЛОЖЬ.

Пример (как функция рабочего листа)

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

На основе приведенной выше таблицы Excel будут возвращены следующие примеры вложенных IF:

 = ЕСЛИ (A1 = «10x12», 120, IF (A1 = «8x8», 64, IF (A1 = «6x6», 36)))
  Результат: 120

= ЕСЛИ (A2 = «10x12», 120; IF (A2 = «8x8», 64, IF (A2 = «6x6», 36)))
  Результат:  64

= ЕСЛИ (A3 = «10x12»; 120; ЕСЛИ (A3 = «8x8», 64; ЕСЛИ (A3 = «6x6», 36)))
  Результат:  36 

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

Неправильная формула:
= ЕСЛИ (A1 = 2, «Привет», = ЕСЛИ (A1 = 3, «До свидания», 0))

Правильная формула
= ЕСЛИ (A1 = 2, «Привет», ЕСЛИ (A1 = 3, «До свидания», 0))

Часто задаваемые вопросы

Вопрос: В Microsoft Excel мне нужно написать формулу, которая работает следующим образом:

Если (ячейка A1) меньше 20, умножьте на 1,
Если оно больше или равно 20, но меньше 50, то умножьте на 2
Если оно больше или равно 50 и меньше 100, затем умножьте на 3
А если оно больше или равно 100, то умножьте на 4

Ответ: Вы можете написать вложенный оператор IF, чтобы справиться с этим.Например:

 = ЕСЛИ (A1 <20, A1 * 1, ЕСЛИ (A1 <50, A1 * 2, ЕСЛИ (A1 <100, A1 * 3, A1 * 4))) 

Вопрос: В Excel мне нужна формула в ячейке C5, которая выполняет следующие действия:

IF A1 + B1 <= 4, вернуть 20 долларов
IF A1 + B1> 4, но <= 9, вернуть 35 долларов
IF A1 + B1> 9, но <= 14, вернуть 50 долларов
IF A1 + B1> 15, вернуть 75 долларов

Ответ: В ячейке C5 вы можете написать вложенный оператор IF, который использует функцию AND следующим образом:

 = ЕСЛИ ((A1 + B1) <= 4,20, ЕСЛИ (И ((A1 + B1)> 4, (A1 + B1) <= 9), 35, ЕСЛИ (И ((A1 + B1)> 9 , (A1 + B1) <= 14), 50,75))) 

Вопрос: В Microsoft Excel мне нужна формула для следующего:

ЕСЛИ ячейка A1 = PRADIP, тогда значение будет 100
ЕСЛИ ячейка A1 = PRAVIN тогда значение будет 200
ЕСЛИ ячейка A1 = PARTHA тогда значение будет 300
ЕСЛИ ячейка A1 = PAVAN тогда значение будет 400

Ответ: Вы можете написать оператор IF следующим образом:

 = ЕСЛИ (A1 = "ПРАДИП"; 100; ЕСЛИ (A1 = "ПРАВИН"; 200; ЕСЛИ (A1 = "ПАРТА"; 300; ЕСЛИ (A1 = "ПАВАН"; 400; "")))) 

Вопрос: В Microsoft Excel я хочу вычислить следующее, используя формулу «если»:

, если A1 <100 000, то A1 *.1%, но минимум 25
, и если A1> 1000000, то A1 * 0,01%, но максимум 5000

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

 = ЕСЛИ (A1 <100000, МАКС (25, A1 * 0,1%), ЕСЛИ (A1> 1000000, МИН (5000, A1 * 0,01%), "")) 

Вопрос: У меня Excel 2000. Если ячейка A2 больше или равна 0, добавьте к C1. Если ячейка B2 больше или равна 0, вычтите из C1. Если и A2, и B2 пусты, то равно C1.Вы можете помочь мне с функцией ЕСЛИ на этом?

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

 = ЕСЛИ (И (ISBLANK (A2) = FALSE, A2> = 0), C1 + A2, IF (AND (ISBLANK (B2) = FALSE, B2> = 0), C1-B2), IF (AND (ISBLANK ( A2) = TRUE, ISBLANK (B2) = TRUE), C1, ""))) 

Вопрос: Как мне написать это уравнение в Excel? Если D12 <= 0, то D12 * L12, Если D12> 0, но <= 600, то D12 * F12, Если D12> 600, то ((600 * F12) + ((D12-600) * E12))

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

 = ЕСЛИ (D12 <= 0, D12 * L12, IF (D12> 600, ((600 * F12) + ((D12-600) * E12)), D12 * F12)) 

Вопрос: Я прочитал вашу статью о вложенных IF в Excel, но я все еще не могу понять, что не так с моей формулой, пожалуйста, не могли бы вы помочь? Вот что у меня:

 = ЕСЛИ (63 <= A2 <80,1; ЕСЛИ (80 <= A2 <95,2; ЕСЛИ (A2 => 95,3,0))) 

Ответ: Самый простой способ написать вложенный оператор IF на основе описанной выше логики:

 = ЕСЛИ (A2> = 95,3; ЕСЛИ (A2> = 80,2; ЕСЛИ (A2> = 63,1,0))) 

Эта формула будет делать следующее:

Если A2> = 95, формула вернет 3 (первая функция ЕСЛИ)
Если A2 <95 и A2> = 80, формула вернет 2 (вторая функция ЕСЛИ)
Если A2 <80 и A2> = 63, формула вернет 1 (третья функция ЕСЛИ)
Если A2 <63, формула вернет 0


Вопрос: Я новичок в мире Excel и пытаюсь понять, как настроить правильную формулу для ячейки If / then.

Я пытаюсь:

Если значение B2 равно от 1 до 5, то умножьте E2 на 0,77
Если значение B2 равно от 6 до 10, затем умножьте E2 на 0,735
Если значение B2 от 11 до 19, то умножьте E2 на 0,7
Если значение B2 равно От 20 до 29, затем умножьте E2 на 0,675
Если значение B2 равно от 30 до 39, умножьте E2 на 0,65

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

Ответ: Чтобы написать формулу ЕСЛИ, вам нужно вложить несколько функций ЕСЛИ вместе в комбинацию с функцией И.

Следующая формула должна работать для того, что вы пытаетесь сделать:

 = ЕСЛИ (И (B2> = 1, B2 <= 5), E2 * 0,77, ЕСЛИ (И (B2> = 6, B2 <= 10), E2 * 0,735, ЕСЛИ (И (B2> = 11, B2 <= 19), E2 * 0,7, ЕСЛИ (И (B2> = 20, B2 <= 29), E2 * 0,675, ЕСЛИ (И (B2> = 30, B2 <= 39), E2 * 0,65, "") )))) 

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


Вопрос: У меня проблема с функцией вложенности ИЛИ:

Моя нерабочая формула:

 = ЕСЛИ (C9 = 1, K9 / J7, IF (C9 = 2, K9 / J7, IF (C9 = 3, K9 / L7, IF (C9 = 4,0, K9 / N7)))) 

В ячейке C9 я могу ввести 1, 2, 3, 4 или 0. Проблема заключается в том, как записать условие «или», когда «4 или 0» существует в столбце C. Если «4 или 0 "в столбце C Я хочу, чтобы столбец K был разделен на столбец N, а ответ был помещен в столбец M и связанную строку

.

Ответ: Вы должны иметь возможность использовать функцию ИЛИ в своей функции ЕСЛИ для проверки C9 = 4 ИЛИ C9 = 0 следующим образом:

 = ЕСЛИ (C9 = 1, K9 / J7, IF (C9 = 2, K9 / J7, IF (C9 = 3, K9 / L7, IF (OR (C9 = 4, C9 = 0), K9 / N7)) )) 

Эта формула вернет K9 / N7, если ячейка C9 равна 4 или 0.


Вопрос: В Excel я пытаюсь создать формулу, которая покажет следующее:

Если столбец B = Ross и столбец C = 8, то в ячейке AB этой строки я хочу, чтобы он отображал 2013 год. Если столбец B = Block и столбец C = 9, то в ячейке AB этой строки я хочу показать 2012 год.

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

 = ЕСЛИ (И (B1 = "Росс", C1 = 8), 2013, ЕСЛИ (И (B1 = "Блок", C1 = 9), 2012, "")) 

Эта формула вернет 2013 в виде числового значения, если B1 - «Росс», а C1 - 8, или 2012 в виде числового значения, если B1 - «Блок», а C1 - 9.В противном случае он вернет пустое значение, что обозначено "".


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

Если B1 = 0, C1 равно A1 / 2
Если B1 = 1, C1 равно A1 / 2 умножить на 20%
Если D1 = 1, C1 равно A1 / 2-5

Я пытался найти на вашем сайте такие же выражения. Пожалуйста, помогите мне это исправить.

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

 = ЕСЛИ (B1 = 0, A1 / 2, ЕСЛИ (B1 = 1, (A1 / 2) * 0.2, ЕСЛИ (D1 = 1, (A1 / 2) -5, ""))) 

Обратите внимание, что если ни одно из условий не выполнено, формула Excel вернет в качестве результата "".


Вопрос: Что я сделал не так с этой формулой в Excel?

 = ЕСЛИ (ИЛИ (ISBLANK (C9), ISBLANK (B9)), «», ЕСЛИ (ISBLANK (C9), D9-СЕГОДНЯ (), «Активировано»)) 

Я хочу сделать событие, что если B9 и C9 пусты, значение будет пустым. Если только C9 пуст, то выводом будут оставшиеся дни между двумя датами, а если две ячейки не пусты, выводом должна быть строка «Reactivated».

Проблема с этим кодом заключается в том, что IF (ISBLANK (C9), D9-TODAY () не работает.

Ответ: Прежде всего, вы можете заменить функцию ИЛИ на функцию И, чтобы ваша формула ЕСЛИ в Excel выглядела так:

 = ЕСЛИ (И (ISBLANK (C9), ISBLANK (B9)), «», ЕСЛИ (ISBLANK (C9), D9-СЕГОДНЯ (), «Активировано»)) 

Затем убедитесь, что у вас нет аномального форматирования в ячейке, содержащей результаты. На всякий случай щелкните правой кнопкой мыши ячейку, содержащую формулу, и выберите Формат ячеек во всплывающем меню.Когда появится окно Формат ячеек , выберите вкладку Число . Выберите General в качестве формата и нажмите кнопку OK.


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

0-125000 = 0%
125001-250000 = 2%
250001-975000 = 5%
975001-1500000 = 10%
> 1500000 = 12%

Я понимаю, что это, вероятно, функция «ЕСЛИ (И)», но мне, кажется, требуется слишком много аргументов.Вы можете помочь?

Ответ: Вы можете создать эту формулу, используя вложенные функции ЕСЛИ. Предположим, что ваш номер n находится в ячейке B1. Вы можете создать свою формулу следующим образом:

 = ЕСЛИ (B1> 1500000, B1 * 0,12, IF (B1> = 975001, B1 * 0,1, IF (B1> = 250001, B1 * 0,05, IF (B1> = 125001, B1 * 0,02,0)))) 

Поскольку ваши условия IF будут охватывать все числа в диапазоне от 0 до> 1500000, проще всего работать в обратном направлении, начиная с условия> 1500000. Excel оценит каждое условие и остановится, когда условие будет ИСТИНА.Вот почему мы можем упростить формулы во вложенных функциях ЕСЛИ вместо тестирования диапазонов с помощью двух сравнений, таких как AND (B1> = 125001, B1 <= 250000).


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

0-125000 = 0%
125001-250000 = 2%
250001-975000 = 5%
975001-1500000 = 10%
> 1500000 = 12%

Допустим, я ввел 1 000 000 в B1.Первые 125 000 привлекают 0%, следующие 125 000 - 250 000 привлекают 2% и так далее.

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

Мы можем создать это решение по следующей формуле:

 = ЕСЛИ (B1 <= 125000,0, ЕСЛИ (B1 <= 250000, (B1-125000) * 0,02), ЕСЛИ (B1 <= 975000, (125000 * 0,02) + ((B1-250000) * 0,05), ЕСЛИ (B1 <= 1500000, (125000 * 0,02) + (725000 * 0,05) + ((B1-975000) * 0,1), (125000 * 0.02) + (725000 * 0,05) + (525000 * 0,1) + ((B1-1500000) * 0,12))))) 

Если значение меньше 125 000, формула вернет 0.

Если значение находится между 125 001 и 250 000, будет вычислено 0% для первых 125 000 и 2% для остатка.

Если значение находится между 250 001 и 250 001, будет вычислено 0% для первых 125 000, 2% для следующих 125 000 и 5% для остатка.

И так далее ....

Excel IF Function - Учебное пособие и примеры

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

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

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

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

Диалоговое окно функции IF PinBlank с пустым результатом формулы

Аргументы функции IF

Поле Определение
Logical_test Проверка значения ячейки, которое имеет значение ИСТИНА или ЛОЖЬ.
Value_if_true Значение, которое Excel поместит в ячейку, если проверка верна.
Value_if_false Значение, которое Excel поместит в ячейку, если тест не пройден.

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

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

Настройка функции IF

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

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

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

Примеры тестов PinLogical

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

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

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

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

Чтобы ввести аргументы функции IF,
  1. Щелкните ячейку таблицы, в которой вы хотите использовать формулу Excel.
  2. На вкладке «Формулы» щелкните «Вставить функцию…».
  3. В текстовом поле диалогового окна «Вставить функцию» введите «если».
  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] ниже).
Pin Пример заполненной формулы ЕСЛИ и результата
  1. Щелкните OK .
  2. Скопируйте формулу в другие ячейки столбца.

Совет : Несмотря на то, что поле Value_if_false является необязательным, лучше указать значение. В противном случае Excel будет использовать FALSE в значении ячейки.

Excel IF, пример 2

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

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

  = ЕСЛИ (B2> 0,5, B2 * C2, -C2)  

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

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

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

Связанные ресурсы Excel

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

10 причин, по которым формулы Excel не работают [и как их исправить]

«Боже мой» формулы Excel не работают в моем отчете.

Звучит знакомо, не так ли?

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

Подумайте об этом.

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

[формулы IF с 64 условиями - шучу ]

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

Здесь могут произойти две вещи:

Один , возможно, вы захотите что-то сказать громко [ Я воздерживаюсь от использования этого слова ].

Второй , вы приложите дополнительные усилия и исправите все формулы.

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

И как их исправить.


Формулы Excel не работают: почему и как их исправить

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

Оригинальное исследование, проведенное Journal Genome Biology, показало, что из года в год наблюдается тенденция к увеличению количества ошибок.

Вы можете загрузить полные исследовательские материалы с веб-сайта SourceForge (с открытым исходным кодом) для дальнейшего чтения.

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

Очень хорошо разработанное и протестированное программное обеспечение, такое как MS Excel, может пойти не так с точки зрения понимания наших данных что насчет вас и меня?

Итак, вот 10 основных причин [пусть вечеринка началась]


№1.ВАРИАНТЫ РАСЧЕТОВ [НАИБОЛЕЕ ПРИЧИНЫ, ПО КОТОРЫМ НЕ ОБНОВЛЯЮТСЯ ФОРМУЛЫ EXCEL]

Изменение параметра расчета на «ручной» - это лучший способ, но при его использовании нужно быть очень осторожным.

По умолчанию опция расчета будет установлена ​​на автоматически в Excel.

Но есть случаи, когда вы могли бы использовать это значение в ‘m anual ‘, чтобы ускорить процесс.

Если вы включите эту опцию « ВКЛ. », ваши формулы не будут обновляться автоматически , это, в свою очередь, увеличит скорость обработки электронных таблиц.

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

Как решить эту проблему:

Перейдите в Формулы → Расчет → Нажмите Рассчитать сейчас / Рассчитать лист.

Вы также можете использовать сочетание F9 , чтобы обновить книгу.

Вот наше видео-руководство по параметрам расчета в Excel.

Еще несколько советов:

  • Сделайте привычкой нажимать Ctrl + S [это приведет к принудительному вычислению]
  • Не используйте этот параметр для каждой книги, кроме случаев, когда у вас есть огромный файл с большим количеством формул.
  • Этот параметр предназначен для конкретной книги, поэтому вам нужно чтобы включить / выключить его в каждой книге, которую вы хотите использовать

# 2. ФОРМУЛА EXCEL, НЕ ВЫЧИСЛЯЮЩАЯ ТОЛЬКО ПОКАЗАННАЯ ФОРМУЛА [ПОКАЗАТЬ ФОРМУЛЫ]


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

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

Сказав это, нам нужно быть немного осторожными при использовании опции th e show formulas , иначе вы можете получить что-то вроде ниже.

Вы видите, что формула «Сегодня» отображается как формула вместо отображения фактической даты?

То же самое и с формулой Randbetween.

Как решить эту проблему:

Очень просто:

Перейдите в Формулы → Под аудитом формул → Нажмите «Показать формулы» (при первом щелчке формулы появятся, а при втором щелчке они скроются за ячейками и начнут работать)

[См. Скриншот выше]

Также прочтите: 51 лучший совет и хитрость по работе с Excel


№3.ДОПОЛНИТЕЛЬНОЕ ПРОСТРАНСТВО - ДОПОЛНИТЕЛЬНАЯ ГОЛОВНАЯ БОЛЬ


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

Знаете ли вы, что лишние пробелы вызывают много проблем при использовании формулы ВПР?

Иногда эти места довольно сложно определить.

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

Теперь, если я возьму длину каждого имени, я вижу, что в первой строке 12, а во второй 13.

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

Это не соответствует критериям точного соответствия в формуле ВПР, поэтому приводит к ошибке # Н / Д .

Как решить эту проблему:

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

Еще несколько советов:

  • Всегда рекомендуется использовать формулу обрезки , прежде чем выполнять Vlookup в обеих таблицах (поиск и таблица данных)
  • Функция обрезки игнорирует пробелы между буквами (работает только со словами)

№4.ИЗБАВЬТЕСЬ ОТ НЕПЕЧАТНЫХ / СКРЫТЫХ ПЕРСОНАЖЕЙ


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

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

Как решить эту проблему:

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

Совет: Рекомендуется использовать вместе формулы Trim и Clean


№ 5.ФОРМАТИРОВАНИЕ EXCEL - НЕ ПЫТАЙТЕСЬ СРАВНИТЬ ЯБЛОКИ С АПЕЛЬСИНАМИ


Очень важно иметь правильное форматирование перед использованием таких формул, как Vlookup, Hlookup и Match & Index.

Числа в текстовом формате - одна из частых причин, по которой формулы не работают в Excel.

Хотя они выглядят как числа, но это не так.

Например, слева вы видите идентификатор клиента, похожий на числа.

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

Это, в свою очередь, вызовет ошибку # Н / Д при добавлении формулы ВПР.

Хуже всего то, что это немного сложно идентифицировать.

Как решить эту проблему:

Убедитесь, что все ваши числа отформатированы как числа, а не как текст. Перед использованием формулы Vlookup стоит переформатировать столбец подстановки.

Еще несколько советов:

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

№ 6.ЦИРКУЛЯРНЫЕ ССЫЛКИ (ОШИБКИ ИЗ-ЗА БЕСКОНЕЧНОГО КРУГА)


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

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

Лично я много раз боролся с такими ошибками.

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

Итак, мы рассчитали сумму разницы в строке 13, и теперь нам просто нужно поместить их в строку 9.

Один простой способ - связать ячейки строки 13 в строке 9.

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

[см. Ниже]

В целом должно работать.

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

Как решить эту проблему:

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

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

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


№ 7. НЕПРАВИЛЬНОЕ ИСПОЛЬЗОВАНИЕ ДВОЙНЫХ ЦИТАТ - ОШИБКА ЧЕЛОВЕКА


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

Но очень важно понимать, когда использовать, а когда нет.

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

= 1234 и 5678 = 12345678

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

= John & Smith: Неправильный способ использования [, скорее всего, увидит ошибку #NAME]

= «Джон» и «Смит» = Это правильный путь.

То же самое и с условными формулами, такими как ЕСЛИ, И, ИЛИ. Очень важно заключать текст в двойные кавычки.

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


№8. БОДМА - ОСНОВНОЕ ПРАВИЛО КАЖДОГО РАСЧЕТА


Знаете ли вы, что все вычисления в Excel основаны на правиле «BODMAS»?

Ой, погоди .. что такое правило BODMAS?

Порядок операций определяется математическим правилом.

Хорошо, давайте еще больше упростим.

Можете ли вы угадать ответ на нижеприведенный?

10 + 10 * 2 =?

Это 40, поздравляю, вы ошиблись.

Можете ли вы попробовать такой же расчет в Excel?

Скорее всего, вы увидите 30, действительно, это правильный ответ ... но как?

Согласно правилу BODMAS, вычисление будет происходить на основе последовательности, а именно → B- скобки, O- порядок, D- деление, M- умножение, A- сложение и S- вычитание.

Итак, последовательность приведенной выше формулы: → 10 * 2 = 20 → 20 + 10 = 30.

Ваши формулы могут ошибаться, если вы не учитываете BODMAS.

Как решить эту проблему:

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

= (30 + 40) * 2

Итак, все, что указано в скобках, будет сначала вычислено, а затем перейдет к следующей операции.


№ 9. НЕПРАВИЛЬНОЕ ИСПОЛЬЗОВАНИЕ «АБСОЛЮТНОЙ» ССЫЛКИ


Каждый, кто использует Excel, в основном знает, что такое абсолютные ссылки.

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

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

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

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

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

Но мы ошиблись.

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

Это действительно привело к неверным результатам.

Итак, правильным способом должно было быть использование абсолютной ссылки для единственных строк, подобных этой = СУММ (D $ 3: D $ 6).

Как решить эту проблему:

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

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

Пожалуйста, дайте мне знать ваши обходные пути на этом этапе.

Еще несколько советов:

  • Не используйте сочетание клавиш F4 для частичного замораживания
  • Попробуйте вручную добавить знак доллара ($) для частичного диапазона

# 10. НЕПРАВИЛЬНЫЕ АРГУМЕНТЫ ФОРМУЛЫ


Неправильные аргументы формулы могут отрицательно повлиять на результаты формулы.

Чтобы дать вам некоторое представление, давайте разберемся с синтаксисом vlookup ниже , особенно с последним [поиск диапазона].

Аргумент поиска диапазона имеет два варианта выбора.

  1. Верно - Примерное совпадение
  2. Ложно - Точное совпадение

Лично я считаю, что использование варианта «Примерное совпадение» похоже на самоубийство.

Потому что Приблизительное совпадение никогда не даст точных результатов.

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

Если вы внимательно присмотритесь, сумма дохода 25 тыс. Для идентификатора клиента 6378993 неверна. Вместо этого должно было быть 65к.

Итак, что пошло не так?

Как я упоминал ранее, я использовал «Приблизительное совпадение» под аргументом поиска диапазона .

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

Как решить эту проблему:

Никогда не используйте « Приблизительное совпадение» в формулах ВПР, если вы не знаете, что делаете.


Теперь твоя очередь


Как вы думаете, у вас есть другие причины, по которым формулы Excel не работают?

Или, может быть, у вас есть вопрос.

В любом случае, оставьте комментарий ниже, я отвечу как можно скорее.

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

[Не волнуйтесь, мы ненавидим спам и уважаем вашу конфиденциальность] .

Подпишитесь на нашу рассылку новостей

Оператор ЕСЛИ ИЛИ в Excel с примерами формул

В руководстве показано, как написать оператор IF OR в Excel для проверки различных условий «это ИЛИ».

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

  • Оператор ЕСЛИ ИЛИ в Excel
  • Примеры формул ЕСЛИ ИЛИ в Excel

Оператор ЕСЛИ ИЛИ в Excel

Чтобы оценить два или более условий и вернуть один результат, если любое из условий ИСТИНА, и другой результат, если все условия ЛОЖЬ, вставьте функцию ИЛИ в логический тест IF:

IF (ИЛИ ( условие1 , условие2 , ...), значение_if_true, значение_if_false)

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

Вот пример формулы ЕСЛИ ИЛИ в простейшей форме:

= ЕСЛИ (ИЛИ (B2 = «доставлено», B2 = «оплачено»), «Закрыто», «Открыто»)

Формула говорит следующее: если ячейка B2 содержит «доставлено» или «оплачено», отметьте заказ как «закрытый», иначе как «открытый».

Если вы хотите, чтобы ничего не возвращал. , если логический тест оценивается как ЛОЖЬ , включите пустую строку ("") в последний аргумент:

= ЕСЛИ (ИЛИ (B2 = "доставлено", B2 = "оплачено"), "Закрыто", "")

Эту же формулу можно записать в более компактной форме с помощью константы массива:

= ЕСЛИ (ИЛИ (B2 = {"доставлено", "оплачено"}), "Закрыто", "")

Если последний аргумент опущен, формула будет отображать ЛОЖЬ, если ни одно из условий не выполняется.

Примечание. Обратите внимание, что формула ЕСЛИ ИЛИ ИЛИ в Excel не делает различий между строчными и прописными буквами, потому что функция ИЛИ нечувствительна к регистру . В нашем случае «доставлен», «доставлен» и «доставлен» считаются одним и тем же словом. Если вы хотите различать регистр текста, оберните каждый аргумент функции ИЛИ в ТОЧНО, как показано в этом примере.

Примеры формул ЕСЛИ ИЛИ в Excel

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

Формула 1. ЕСЛИ с несколькими условиями ИЛИ

Не существует конкретного ограничения на количество условий ИЛИ, встроенных в формулу ЕСЛИ, если это соответствует общим ограничениям Excel:

  • В формулах Excel 2016–2007 допускается до 255 аргументов, общая длина которых не превышает 8192 символа.
  • В Excel 2003 и ниже вы можете использовать до 30 аргументов, а общая длина не должна превышать 1024 символа.

В качестве примера давайте проверим столбцы A, B и C на наличие пустых ячеек и вернем «Incomplete», если хотя бы одна из 3 ячеек пуста.Задача может быть решена с помощью следующей функции ЕСЛИ ИЛИ:

= ЕСЛИ (ИЛИ (A2 = "", B2 = "", C2 = ""), "Неполно", "")

Результат будет выглядеть примерно так:

Формула 2. Если это ИЛИ ячейка, вычислите

Ищете формулу, которая может сделать что-то более сложное, чем возвращать предварительно определенный текст? Просто вложите другую функцию или арифметическое уравнение в аргументы value_if_true и / или value_if_false IF.

Допустим, вы рассчитываете общую сумму заказа ( кол-во, , умноженное на Цена за единицу ) и хотите применить скидку 10%, если выполняется одно из этих условий:

  • в B2 больше или равно 10, или
  • Цена за единицу в C2 больше или равна 5 долларам.

Итак, вы используете функцию ИЛИ, чтобы проверить оба условия, и, если результат ИСТИНА, уменьшите общую сумму на 10% (B2 * C2 * 0,9), в противном случае верните полную цену (B2 * C2):

= ЕСЛИ (ИЛИ (B2> = 10, C2> = 5), B2 * C2 * 0.9, B2 * C2)

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

= ЕСЛИ (ИЛИ (B2> = 10, C2> = 5), «Да», «Нет»)

На снимке экрана ниже показаны обе формулы в действии:

Формула 3. Формула ЕСЛИ ИЛИ с учетом регистра

Как уже упоминалось, функция ИЛИ в Excel по своей природе нечувствительна к регистру. Однако ваши данные могут быть чувствительны к регистру, поэтому вы захотите выполнить с учетом регистра ИЛИ тесты .В этом случае выполните каждый отдельный логический тест внутри функции EXACT и вложите эти функции в оператор OR.

IF (OR (EXACT ( cell, " condition1 "), EXACT ( cell, " condition2 ")), value_if_true, value_if_false)

В этом примере давайте найдем и отметим идентификаторы заказов «AA-1» и «BB-1»:

= ЕСЛИ (ИЛИ (ТОЧНЫЙ (A2; «AA-1»); ТОЧНЫЙ (A2; «BB-1»)), «x», «»)

В результате только два идентификатора заказов, в которых все буквы написаны заглавными буквами, помечены знаком «x»; похожие идентификаторы, такие как «aa-1» или «Bb-1», не помечаются:

Формула 4.Вложенные операторы IF OR в Excel

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

Чтобы продемонстрировать концепцию, давайте проверим названия элементов в столбце A и вернем «Фрукты» для Яблоко или Апельсин и «Овощ» для Помидор или Огурец :

= ЕСЛИ (ИЛИ (A2 = «яблоко», A2 = «апельсин»), «Фрукты», ЕСЛИ (ИЛИ (A2 = «помидор», A2 = «огурец»), «Овощ», «»))

Для получения дополнительной информации см. Вложенное ЕСЛИ с условиями ИЛИ / И.

Формула 5. Оператор IF AND OR

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

В качестве примера мы собираемся отметить строки, в которых элемент в столбце A - это либо Apple , либо Orange , а количество в столбце B больше 10:

= ЕСЛИ (И (ИЛИ (A2 = «яблоко», A2 = «апельсин»), B2> 10), «x», «»)

Для получения дополнительной информации см. Excel ЕСЛИ с несколькими условиями И / ИЛИ.

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

Вас также может заинтересовать

Как объединить условное форматирование с оператором IF

Объединить условное форматирование с оператором IF

Синтаксис

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

Но при условном форматировании синтаксис IF / THEN / ELSE не может применяться в одном правиле.Условное форматирование применяется только с использованием логического теста IF / THEN . Для применения условного форматирования он должен возвращать ИСТИНА.

Например, если вы хотите применить условное форматирование, используя условие: «Если значение ячейки больше установленного значения, скажем 100, то отформатируйте ячейку как КРАСНУЮ, иначе отформатируйте ячейку как ЗЕЛЕНУЮ». Итак, вы можете видеть, что для выполнения условного форматирования требуется два правила: одно для значения больше 100 и одно для значения меньше 100.

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

Примеры использования условного форматирования с условиями IF / THEN

Давайте рассмотрим сценарии, чтобы увидеть, как использовать логический тест IF / THEN в условном форматировании для выделения целевых значений. Если вы хотите выделить счета-фактуры в диапазоне данных A2: C13 , которые должны быть оплачены в апреле, вам необходимо проверить логическое условие IF / THEN для диапазона дат в столбце B, если месяц равен апрелю. , используя следующую настраиваемую формулу в условном форматировании. = МЕСЯЦ ($ B2) = 4

Сначала выберите диапазон данных A2: C13, затем перейдите к:

Условное форматирование (на вкладке «Главная»)> Новое правило > Используйте формулу для определения …> Введите указанную выше формулу в окно « Редактировать описание правила» > Выберите Format Fill для предварительного просмотра и нажмите OK

Обратите внимание, что вам нужно исправить столбец, сделав его абсолютным, используя для него знак $ , и оставить номер строки свободным или относительным для изменения.Следовательно, формула проверит каждую строку указанного столбца в выбранном диапазоне, проверит логическое условие ЕСЛИ / ТО и вернет ИСТИНА и ЛОЖЬ.

Это правило будет оцениваться во всех активных ячейках заблокированного столбца B одну за другой, игнорируя другие ячейки в столбцах A и C. Когда функция МЕСЯЦ в ячейке столбца B возвращает число 4 (апрель), правило вернет ИСТИНА для всех активные ячейки в этой строке и условное форматирование будут применены ко всей этой строке, как показано ниже.

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

= И (критерии, $ C2> = БОЛЬШОЙ (ЕСЛИ (критерии, значения), 2)) = И (МЕСЯЦ ($ B2) = критерии, $ C2> = БОЛЬШОЙ (ЕСЛИ (МЕСЯЦ (дата) = критерии , сумма), 2)) = И (МЕСЯЦ ($ B2) = 4, $ C2> = БОЛЬШОЙ (ЕСЛИ (МЕСЯЦ ($ B $ 2: $ B $ 13) = 4, $ C $ 2: $ C $ 13), 2))

Применяя эту настраиваемую формулу, вы можете выделить два счета-фактуры с наибольшей суммой в апреле. Функция НАИБОЛЬШИЙ с ЕСЛИ генерирует серию значений и сравнивает их с каждым значением в столбце C. Функция AND проверяет логические условия в каждой ячейке столбца B и C по одному и вернет ИСТИНА, если будут выполнены оба условия.Посмотрите ниже.

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

Вам все еще нужна помощь по функции «Среднее»? Ознакомьтесь с нашим исчерпывающим обзором руководств по функциям Average здесь.

.

Оставить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *