Функции excel если (if) и еслимн (ifs) для нескольких условий

Функция Excel ЕСЛИМН

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

В MS Excel 2016 появилась функция ЕСЛИМН, ради которой и написана вся эта статья. Это та же ЕСЛИ, только заточенная специально для проверки множества условий. Теперь не нужно сто раз писать ЕСЛИ и считать открытые скобки. Достаточно перечислить условия и в конце закрыть одну скобку.

Работает следующим образом. Возьмем пример выше и воспользуемся новой формулой Excel ЕСЛИМН.

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

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

В ЕСЛИ задается только альтернативное значение, которое наступает, если не выполняется ни одно из перечисленных условий. Здесь же нужно указать само условие, которое в нашем случае было бы B2>=1. Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение

Последнее альтернативное условие, в отличие от обычной ЕСЛИ, также должно быть обязательно указано. В ЕСЛИ задается только альтернативное значение, которое наступает, если не выполняется ни одно из перечисленных условий. Здесь же нужно указать само условие, которое в нашем случае было бы B2>=1. Однако этого можно избежать, если в поле с условием написать ИСТИНА, указывая тем самым, что, если не выполняются ранее перечисленные условия, наступает ИСТИНА и возвращается последнее альтернативное значение.

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

Поиск отличий в двух списках

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

Вариант 1. Синхронные списки

Если списки синхронизированы (отсортированы), то все делается весьма несложно, т.к. надо, по сути, сравнить значения в соседних ячейках каждой строки. Как самый простой вариант — используем формулу для сравнения значений, выдающую на выходе логические значения ИСТИНА (TRUE) или ЛОЖЬ (FALSE) :

Число несовпадений можно посчитать формулой:

или в английском варианте =SUMPRODUCT(—(A2:A20B2:B20))

Если в результате получаем ноль — списки идентичны. В противном случае — в них есть различия. Формулу надо вводить как формулу массива, т.е. после ввода формулы в ячейку жать не на Enter, а на Ctrl+Shift+Enter.

Если с отличающимися ячейками надо что сделать, то подойдет другой быстрый способ: выделите оба столбца и нажмите клавишу F5, затем в открывшемся окне кнопку Выделить (Special) — Отличия по строкам (Row differences) . В последних версиях Excel 2007/2010 можно также воспользоваться кнопкой Найти и выделить (Find & Select) — Выделение группы ячеек (Go to Special) на вкладке Главная (Home)

Excel выделит ячейки, отличающиеся содержанием (по строкам). Затем их можно обработать, например:

• залить цветом или как-то еще визуально отформатировать
• очистить клавишей Delete
• заполнить сразу все одинаковым значением, введя его и нажав Ctrl+Enter
• удалить все строки с выделенными ячейками, используя команду Главная — Удалить — Удалить строки с листа (Home — Delete — Delete Rows)
• и т.д.

Вариант 2. Перемешанные списки

Если списки разного размера и не отсортированы (элементы идут в разном порядке), то придется идти другим путем.

Самое простое и быстрое решение: включить цветовое выделение отличий, используя условное форматирование. Выделите оба диапазона с данными и выберите на вкладке Главная — Условное форматирование — Правила выделения ячеек — Повторяющиеся значения (Home — Conditional formatting — Highlight cell rules — Duplicate Values):

Если выбрать опцию Повторяющиеся, то Excel выделит цветом совпадения в наших списках, если опцию Уникальные — различия.

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

В качестве альтернативы можно использовать функцию СЧЁТЕСЛИ (COUNTIF) из категории Статистические, которая подсчитывает сколько раз каждый элемент из второго списка встречался в первом:

Полученный в результате ноль и говорит об отличиях.

И, наконец, «высший пилотаж» — можно вывести отличия отдельным списком. Для этого придется использовать формулу массива:

Выглядит страшновато, но свою работу выполняет отлично

Функция Подбор параметра в Эксель. Анализ что если — RUUD

• Ставка – проценты по займу.
• Кпер – число оплат (для годового кредита в случае ежемесячной оплаты это 12 раз).
• ПС – первоначальная сумма.
• БС – будущая стоимость (если вы намерены выплатить не всю сумму, а лишь ее часть, здесь указывается, какой долг должен остаться). Это необязательный аргумент, по умолчанию он равен 0.
• Тип – когда производится оплата – в начале месяца или в конце. Этот параметр не обязательно указывать, если он не заполнен, принимается равным 0, что означает оплату в конце месяца.

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

Объединяем несколько условий.

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

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

Пример 1

Функция ИЛИ возвращает ИСТИНА, если хотя бы одно из нескольких перечисленных в ней условий истинно.

= SE (OR (C2 = «Восток», C2 = «Юг»), «Экспорт», «Местный»)

Мы вставляем функцию ИЛИ как условие в функцию ЕСЛИ. В нашем случае, если регион покупателя — Восток или Юг, отгрузка считается экспортной.

Пример 2.

Давайте использовать несколько более сложных условий в функции ЕСЛИ.

Если регион продаж — Запад или Юг и количество больше 100, предоставляется скидка 10%.

= SE (И (ИЛИ (C2 = Запад, C2 = Юг), E2> 100), F2 * 0,1,0)

Функция И возвращает ИСТИНА, если все перечисленные в ней условия выполнены. Внутри функции И мы устанавливаем два условия:

1. Регион — Запад или Юг
2. Число больше 100.

Первый из них реализован так же, как и первый пример: ИЛИ (C2 = «Запад», C2 = «Юг»)

Во-вторых, здесь все очень просто: E2> 100

В строках 2, 3 и 5 выполняются оба условия. Эти покупатели получат скидку.

В строке 4 ни один из них не был выполнен. А в строке 6,7,8 выполняется только первая, но ее слишком мало. Следовательно, скидка будет нулевая.

Пример 3.

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

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

Все наши ранее написанные условия, в свою очередь, становятся первым аргументом в новой функции И:

1. Регион — Запад или Юг и число больше 100 (рассмотрено в примере 2)
2. В названии шоколада присутствует слово «черный».

В результате мы получаем формулу ЕСЛИ с несколькими условиями:

= ЕСЛИ (И (ЕЧИСЛО (НАЙТИ («Черный»; D2)),И (ИЛИ (C2 = «Запад», C2 = «Юг»)), E2> 100), F2 * 0,1,0)

Функция НАЙТИ ищет точное совпадение. Если для нас не важен регистр символов в тексте, то вместо НАЙТИ можно использовать аналогичную функцию ТОЧНЫЙ.

= ЕСЛИ (И (ЕЧИСЛО (МАТЧ («черный»; D2)),И (ИЛИ (C2 = «Запад», C2 = «Юг»)), E2> 100), F2 * 0,1,0)

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

Важно лишь строго соблюдать логическую последовательность их выполнения

Подбор параметра

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

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

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

Как использовать Подбор параметра (пример 1):

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

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

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

На изображении ниже видно, что Ваши баллы за первые два задания (тест и письменная работа) составляют 58, 70, 72 и 60. Несмотря на то, что мы не знаем, каким будет балл за последнее задание (тестирование 3), мы можем написать формулу, которая вычислит средний балл сразу за все задания. Все, что нам необходимо, это вычислить среднее арифметическое для всех пяти оценок. Для этого введите выражение =СРЗНАЧ(B2:B6)
в ячейку B7. После того как Вы примените Подбор параметра
к решению этой задачи, в ячейке B6 отобразится минимальный балл, который необходимо получить, чтобы поступить в учебное заведение.

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

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

Как использовать Подбор параметра (пример 2):

Давайте представим, что Вы планируете событие и хотите пригласить такое количество гостей, чтобы не превысить бюджет в $500. Можно воспользоваться Подбором параметра
, чтобы вычислить число гостей, которое можно пригласить. В следующем примере ячейка B4 содержит формулу =B1+B2*B3
, которая суммирует общую стоимость аренды помещения и стоимость приема всех гостей (цена за 1 гостя умножается на их количество).

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

Синтаксис функции ЕСЛИ

Вот как выглядит синтаксис этой функции Excel и её аргументы:

Логическое выражение — (обязательное)
условие, которое возвращает значение «истина» или «ложь» («да» или «нет»);

Значение если «да» — (обязательное)
действие, которое выполняется в случае положительного ответа;

Значение если «нет» — (обязательное)
действие, которое выполняется в случае отрицательного ответа;

Давайте вместе подробнее
рассмотрим эти аргументы.

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

Как правильно задать вопрос?
Для этого можно составить логическое выражение, используя знаки “=”, “>”,
“<”, “>=”, “<=”, “<>”. Давайте попробуем задать такой вопрос
вместе.

Если достаточно выполнения хотя бы одного условия (логика ИЛИ).

Как вы видели в приведенных выше примерах, подсчет ячеек, отвечающих всем вышеперечисленным критериям, прост, потому что функция СЧЁТЕСЛИМН предназначена для выполнения этой работы.

Но что, если вы хотите подсчитать значения, для которых хотя бы одно из указанных условий истинно, то есть использовать логику ИЛИ? В принципе, это можно сделать двумя способами: 1) путем добавления нескольких формул СЧЁТЕСЛИ или 2) с помощью комбинации СУММ + СЧЁТЕСЛИ с константой массива.

Способ 1. Две или более формулы СЧЕТЕСЛИ или СЧЕТЕСЛИМН.

Считаем заказы со статусом «Отменено» и «Ожидание». Для этого вы можете просто написать 2 обычные формулы СЧЁТЕСЛИ и затем сложить результаты:

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

Чтобы узнать количество «отмененных» и «ожидающих» заказов на клубнику, используйте эту опцию:

Способ 2. СУММ+СЧЁТЕСЛИМН с константой массива.

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

Введите СЧЁТЕСЛИМН в функцию СУММ, например:

СУММ (СЧЁТЕСЛИМН (диапазон; {«условие1»; «условие2»; «условие3»;…}))

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

= СУММ (СЧЁТЕСЛИМН (E2: E11; {«Отменено», «В ожидании»}))

Массив означает, что мы сначала ищем все отмененные ордера, затем отложенные ордера. В результате получается двузначная матрица итогов. А затем функция СУММ складывает их.

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

Инструменты анализа Excel

Одним из самых привлекательных анализов данных является «Что-если». Он находится: «Данные»-«Работа с данными»-«Что-если».

Средства анализа «Что-если»:

1. «Подбор параметра». Применяется, когда пользователю известен результат формулы, но неизвестны входные данные для этого результата.
2. «Таблица данных». Используется в ситуациях, когда нужно показать в виде таблицы влияние переменных значений на формулы.
3. «Диспетчер сценариев». Применяется для формирования, изменения и сохранения разных наборов входных данных и итогов вычислений по группе формул.
4. «Поиск решения». Это надстройка программы Excel. Помогает найти наилучшее решение определенной задачи.

Практический пример использования «Что-если» для поиска оптимальных скидок по таблице данных.

Другие инструменты для анализа данных:

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

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

Как задать простое логическое условие в Excel

​ с функцией​ все ли проверяемые​ в противном случае.​ Excel ячейки в​ С1 слово «Да».​ «6». Можно написать​ с помощью функции​Нужно очень крепко подумать,​ приоритеты.​=ЕСЛИ(C2>B2;»Превышение бюджета»;»В пределах бюджета»)​Имя аргумента​​ В9 – условие.​​ или продукт хранится​​ «ЛОЖЬ».​​ Как так сделать?​ значение ЛОЖЬ.​ убедиться, что одно​ЕСЛИ​

• ​ условия принимают значение​
• ​ Задавая такое условие,​ определенных строках».​
• ​ Если число будет​

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

​ любое слово в​ проверки ошибок​ чтобы выстроить последовательность​Иногда требуется проверить, пуста​В примере выше функция​Описание​ Третий аргумент –​

Как задать условие в Excel

​ дольше 10 месяцев,​=И (Лог_знач. 1; Лог_знач.​И скажить как​=ЕСЛИ(ИЛИ(НЕ(A350)); A3; «Значение вне​ значение не равно​.​

​ ИСТИНА.​​ можно сравнивать текстовые​Можно в Excel​ меньше 2, то​ этом условии, т.д.​Логические функции​ из множества операторов​ ли ячейка. Обычно​ ЕСЛИ в ячейке​лог_выражение​ $C$2:$C$7 – диапазон​ его списывают.​ 2;…)​ написать чтоб EXCEL​

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

​ D2 означает:​ (обязательно)​ усреднения; числовые значения,​Для решения используем логические​Принимает до 255 аргументов​ понял что надо​100 не меньше 0​Функция​Описание​И​

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

​ЕСЛИ(C2 больше B2, то​​Условие, которое нужно проверить.​ которые берутся для​ функции ЕСЛИ и​ в виде условий​ «Больше или равно»,»Меньше​ (ЛОЖЬ) и больше​

​НЕ​=И(A2>1;A2​​возвращает значение ИСТИНА,​​ сравнивая «ЯНВАРЬ» и​​ разным условиям. Например,​​ по столбцу С.​ мы напишем, что​Часто применяемая,​ по каждому условию​ результат при отсутствии​ вернуть текст «Превышение​значение_если_истина​ расчета среднего арифметического.​ ИЛИ: =10);»списан»;»»)’ class=’formula’>.​ или ссылок. Обязательным​ или равно», «Не​ чем 50 (ИСТИНА),​меняет значение своего​

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

​ ровняется». Так для​

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

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

В приведенной выше таблице показано, что если вы продадите 100 товаров по 5 долларов за штуку за вычетом 10% комиссии, вы заработаете 450 долларов. Вопрос в следующем: сколько товаров вам нужно продать, чтобы заработать 1000 долларов?

Давайте посмотрим, как найти ответ с помощью Goal Seek:

1. Настройте свои данные так, чтобы у вас ячейка формулы и изменение клетка зависит от ячейки формулы.
2. Перейти к Данные вкладка > Прогноз нажмите кнопку «Что, если анализ» и выберите «Поиск цели…».
3. в поиск цели диалоговое окно, определите ячейки/значения для проверки и щелкните ХОРОШО:
   • Задать ячейку — ссылка на ячейку, содержащую формулу (B5).
   • В значение — результат формулы, которого вы пытаетесь достичь (1000).
   • Изменив ячейку — ссылка на входную ячейку, которую вы хотите настроить (B3).
4. поиск цели Статус появится диалоговое окно и сообщит, найдено ли решение. Если это удалось, значение в «изменяющейся ячейке» будет заменено новым. Нажмите ХОРОШО сохранить новое значение или Отмена чтобы восстановить исходный.

   В этом примере Goal Seek обнаружил, что необходимо продать 223 товара (с округлением до следующего целого числа), чтобы получить доход в 1000 долларов.

Если вы не уверены, что сможете продать столько товаров, то, возможно, вы сможете достичь целевого дохода, изменив цену товара? Чтобы протестировать этот сценарий, выполните анализ поиска цели точно так, как описано выше, за исключением того, что вы укажете другой Изменение ячейки (БИ 2):

В результате вы обнаружите, что если вы увеличите цену за единицу до 11 долларов, вы можете достичь дохода в 1000 долларов, продав всего 100 товаров:

Советы и примечания:

• Excel Goal Seek не изменяет формулу, а только изменяет входное значение, которое вы вводите в Сменив ячейку коробка.
• Если Goal Seek не может найти решение, он отображает ближайшее найденное значение.
• Вы можете восстановить исходное входное значение, щелкнув значок Отменить или нажатием клавиши «Отменить» (Ctrl + Z).

Овладейте анализом данных

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

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

Post Views: 1 032

Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)

Примеры

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

=ЕСЛИ(D2>89;»A»;ЕСЛИ(D2>79;»B»;ЕСЛИ(D2>69;»C»;ЕСЛИ(D2>59;»D»;»F»))))

Этот сложный оператор с вложенными функциями ЕСЛИ следует простой логике:

1. Если тестовых баллов (в ячейке D2) больше 89, учащийся получает оценку A.

2. Если тестовых баллов больше 79, учащийся получает оценку B.

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

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

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

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

=ЕСЛИ(B2>97;»A+»;ЕСЛИ(B2>93;»A»;ЕСЛИ(B2>89;»A-«;ЕСЛИ(B2>87;»B+»;ЕСЛИ(B2>83;»B»;ЕСЛИ(B2>79;»B-«; ЕСЛИ(B2>77;»C+»;ЕСЛИ(B2>73;»C»;ЕСЛИ(B2>69;»C-«;ЕСЛИ(B2>57;»D+»;ЕСЛИ(B2>53;»D»;ЕСЛИ(B2>49;»D-«;»F»))))))))))))

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

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

Типичные ошибки при вводе формул

Допустим у нас есть столбец с числами и нужно сравнить каждое число с константой (пусть это будет число 30). Для каждого из чисел в столбце у нас будут два варианта результата в зависимости от сравнения:

Ячейка	Число	Формула	Результат
A5 1	10	=ЕСЛИ(A5>30;»Больше 30″;»Меньше или равно 30″)	«Меньше или равно 30»
A6	20	=ЕСЛИ(A6>30;»Больше 30″;»Меньше или равно 30″)	«Меньше или равно 30»
A7	30	=ЕСЛИ(A7>30;»Больше 30″;»Меньше или равно 30″)	«Меньше или равно 30»
A8	40	=ЕСЛИ(A8>30;»Больше 30″;»Меньше или равно 30″)	«Больше 30»
A9	50	=ЕСЛИ(A9>30;»Больше 30″;»Меньше или равно 30″)	«Больше 30»

Другие типы анализа «что если»

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

• Диспетчер сценариев позволяет подставлять значения сразу в несколько ячеек (до 32). Вы можете создать несколько сценариев, а затем сравнить их, не изменяя значений вручную. В следующем примере мы используем сценарии, чтобы сравнить несколько различных мест для проведения мероприятия.
• Таблицыданных позволяют взять одну из двух переменных в формуле и заменить ее любым количеством значений, а полученные результаты свести в таблицу. Этот инструмент обладает широчайшими возможностями, поскольку выводит сразу множество результатов, в отличие от Диспетчера сценариев или Подбора параметра. В следующем примере видно 24 возможных результата по ежемесячным платежам за кредит:

Другие типы анализа «что если»

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

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

Функции программы Microsoft Excel: подбор параметра

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

Скачать последнюю версию Excel

• Суть функции
• Применение функции на практике
• Решение уравнений

Суть функции

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

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

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

Применение функции на практике

Для того, чтобы понять, как работает данная функция, лучше всего объяснить её суть на практическом примере. Мы будем объяснять работу инструмента на примере программы Microsoft Excel 2010, но алгоритм действий практически идентичен и в более поздних версиях этой программы, и в версии 2007 года.

Имеем таблицу выплат заработной платы и премии работникам предприятия. Известны только премии работников. Например, премия одного из них — Николаева А. Д, составляет 6035,68 рублей. Также, известно, что премия рассчитывается путем умножения заработной платы на коэффициент 0,28. Нам предстоит найти заработную плату работников.

Для того, чтобы запустить функцию, находясь во вкладке «Данные», жмем на кнопку «Анализ «что если»», которая расположена в блоке инструментов «Работа с данными» на ленте. Появляется меню, в котором нужно выбрать пункт «Подбор параметра…».

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

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

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

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

Когда все данные окна параметров заполнены, жмем на кнопку «OK».

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

Решение уравнений

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

Допустим, имеем уравнение: 15x+18x=46. Записываем его левую часть, как формулу, в одну из ячеек. Как и для любой формулы в Экселе, перед уравнением ставим знак «=». Но, при этом, вместо знака x устанавливаем адрес ячейки, куда будет выводиться результат искомого значения.

В нашем случае, формулу мы запишем в C2, а искомое значение будет выводиться в B2. Таким образом, запись в ячейке C2 будет иметь следующий вид: «=15*B2+18*B2».

Запускаем функцию тем же способом, как было описано выше, то есть, нажав на кнопку «Анализ «что если»» на ленте», и перейдя по пункту «Подбор параметра…».

В открывшемся окне подбора параметра, в поле «Установить в ячейке» указываем адрес, по которому мы записали уравнение (C2).

В поле «Значение» вписываем число 45, так как мы помним, что уравнение выглядит следующим образом: 15x+18x=46.

В поле «Изменяя значения ячейки» мы указываем адрес, куда будет выводиться значение x, то есть, собственно, решение уравнения (B2). После того, как мы ввели эти данные, жмем на кнопку «OK».

Как видим, программа Microsoft Excel успешно решила уравнение. Значение x будет равно 1,39 в периоде.

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

Производим вычисления по условию.

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

В примере ниже столбец F содержит даты завершения закупок шоколада.

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

Формула в ячейке F3:

Как работает эта формула?

Функция СЧЕТЗ (английский вариант — COUNTA) подсчитывает количество значений (текстовых, числовых и логических) в диапазоне ячеек Excel. Если мы знаем количество значений в диапазоне, то легко можно составить условие. Если число значений равно числу ячеек Excel, то значит, пустых среди них нет и можно производить вычисление. Если такого равенства нет, значит есть хотя бы одна пустая ячейка, и вычислять нельзя.

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

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

Функция ЕПУСТО (английский вариант — ISBLANK) проверяет, не ссылается ли она на пустую ячейку. Если это так, то возвращает ИСТИНА.

Функция ИЛИ (английский вариант — OR) позволяет объединить условия и указать, что нам достаточно того, чтобы хотя бы одна функция ЕПУСТО обнаружила пустую ячейку. В этом случае никаких вычислений не производим и функция ЕСЛИ возвращает пустую строку. А вот если не пусто – то производим вычисления.

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

Рассмотрим теперь более универсальные решения.

В качестве аргумента условия в функции ЕСЛИ мы используем СЧИТАТЬПУСТОТЫ (английский вариант — COUNTBLANK). Она возвращает количество пустых ячеек, но любое число больше 0 Excel интерпретирует как ИСТИНА.

И, наконец, еще одна формула ЕСЛИ (IF) в Excel, которая проверит «если не пусто» и позволит производить расчет только при наличии непустых ячеек.

Функция ЕЧИСЛО (или ISNUMBER) возвращает ИСТИНА, если ссылается на число. Естественно, при ссылке на пустую ячейку возвратит ЛОЖЬ.

А теперь посмотрим, как это работает. Заполним таблицу недостающим значением.

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

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

Итак, перед нами уже знакомое выражение

Для функции СЧЕТЗ не имеет значения, число или текст используются в ячейке Excel.

То же можно сказать и о функции СЧИТАТЬПУСТОТЫ.

А вот третий вариант — к проверке условия при помощи функции ЕЧИСЛО добавляем проверку ЕТЕКСТ (ISTEXT в английском варианте). Объединяем их функцией ИЛИ.

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

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

Надеемся, этот материал был полезен. А вот еще несколько примеров работы с условиями «если – то» при помощи функции ЕСЛИ (IF) в Excel.

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