Skip to content
Очень распространенный вариант расчётов в Excel — «если — то». То есть, при выполнении определенного условия нужно выполнить какое-то вычисление. Поэтому функция ЕСЛИ в Excel (IF в английской версии) – это не только одна из самых простых функций, но и одна из самых часто используемых. Она является одной из основных и при этом она очень полезна.
Сейчас мы на примерах
рассмотрим, как можно использовать функцию ЕСЛИ в Excel, а также какие задачи мы можем решить с ее
помощью.
Что делает функция ЕСЛИ?
Она позволяет создать дерево решений, в котором при выполнении какого-то условия происходит определенное действие. А если это условие не выполняется, то совершается другое действие.
При этом аргумент функции должен быть вопросом, на который возможно 2 варианта ответа: «да» и «нет», «истина» или «ложь».
Вот как может выглядеть это дерево решений «если – то».
Итак, функция ЕСЛИ позволяет задать вопрос и указать в Excel на 2 варианта вычислений в зависимости от полученного на него ответа. Они и являются тремя аргументами функции.
Синтаксис функции ЕСЛИ
Вот как выглядит синтаксис этой функции Excel и её аргументы:
=ЕСЛИ(логическое выражение; значение если «да»; значение если «нет»)
Логическое выражение — (обязательное)
условие, которое возвращает значение «истина» или «ложь» («да» или «нет»);
Значение если «да» — (обязательное)
действие, которое выполняется в случае положительного ответа;
Значение если «нет» — (обязательное)
действие, которое выполняется в случае отрицательного ответа;
Давайте вместе подробнее
рассмотрим эти аргументы.
Первый аргумент функции ЕСЛИ – это логический вопрос. И ответ этот может быть только «да» или «нет», «истина» или «ложь».
Как правильно задать вопрос?
Для этого можно составить логическое выражение, используя знаки “=”, “>”,
“<”, “>=”, “<=”, “<>”. Давайте попробуем задать такой вопрос
вместе.
Простейший пример применения.
Предположим, вы работаете
в компании, которая занимается продажей шоколада в нескольких регионах и
работает с множеством покупателей.
Нам необходимо выделить
продажи, которые произошли в нашем регионе, и те, которые были сделаны за рубежом.
Для этого нужно добавить в таблицу ещё один признак для каждой продажи – страну,
в которой она произошла. Мы хотим, чтобы этот признак создавался автоматически
для каждой записи (то есть, строки).
В этом нам поможет функция Excel ЕСЛИ. Добавим в таблицу данных столбец “Страна”. Регион “Запад” – это местные продажи («Местные»), а остальные регионы – это продажи за рубеж («Экспорт»).
Как правильно записать условие «если – то» в Excel?
Устанавливаем курсор в
ячейку G2 и
вводим знак “=”. Для Excel это означает, что сейчас будет введена формула. Поэтому
как только далее будет нажата буква “е”, мы получим предложение выбрать
функцию, начинающуюся этой буквы. Выбираем “ЕСЛИ”.
Далее все наши действия
также будут сопровождаться подсказками.
В качестве первого аргумента ЕСЛИ записываем: С2=”Запад”. Как и в других функциях Excel, адрес ячейки можно не вводить вручную, а просто кликнуть на ней мышкой. Затем ставим “;” и указываем второй аргумент.
Второй аргумент ЕСЛИ – это значение, которое примет ячейка G2, если записанное нами условие будет выполнено. Это будет слово “Местные”.
После этого снова через запятую
указываем значение третьего аргумента. Это значение примет ячейка G2, если условие не будет
выполнено: “Экспорт”. Не забываем закончить ввод формулы, закрыв скобку и затем
нажав “Enter”.
Наша формула выглядит следующим образом:
=ЕСЛИ(C2=»Запад»;»Местные»;»Экспорт»)
В английской версии формула IF будет выглядеть так:
=IF(C2=»Запад»,»Местные»,»Экспорт»)
То есть, если значение в ячейке С2 будет «Запад», то Excel возвратит в ячейку с формулой слово «Местные». А если условие не выполнено, то – «Экспорт».
Наша ячейка G2 приняла значение «Местные».
Теперь эту формулу можно скопировать во все остальные ячейки столбца G.
А если один из параметров не заполнен?
Если вас не интересует, что будет, к примеру, если интересующее вас условие не выполняется, тогда можно не вводить второй аргумент. К примеру, мы предоставляем скидку 10% в случае, если заказано более 100 единиц товара. Не указываем в формуле ЕСЛИ никакого третьего аргумента для случая, когда условие не выполняется.
=ЕСЛИ(E2>100;F2*0.1)
Что будет в результате?
Насколько это красиво и удобно – судить вам. Думаю, в функции ЕСЛИ лучше все же использовать оба аргумента.
И в случае, если второе условие не выполняется, но делать при этом ничего не нужно, то просто вставьте в ячейку пустое значение.
=ЕСЛИ(E2>100;F2*0.1;»»)
Однако, такая конструкция
может быть использована в том случае, если значение «Истина» или «Ложь» будут
использованы другими функциями Excel в качестве логических значений.
Обратите также внимание,
что полученные логические значения в ячейке всегда выравниваются по центру. Это
видно и на скриншоте выше.
Более того, если вам
действительно нужно только проверить какое-то условие и получить «Истина» или «Ложь»
(«Да» или «Нет»), то вы можете использовать следующую конструкцию –
=ЕСЛИ(E2>100;ИСТИНА;ЛОЖЬ)
Обратите внимание, что
кавычки здесь использовать не нужно. Если вы заключите аргументы в кавычки, то
в результате выполнения функции ЕСЛИ вы получите текстовые значения, а не
логические.
Рассмотрим, как ещё можно
использовать функцию ЕСЛИ.
Использование функции ЕСЛИ с числами.
Точно так же, как мы это
делали с текстом, в аргументах функции можно использовать и числа.
Однако для нас важно то,
что функция ЕСЛИ позволяет не только заполнять ячейки определёнными числовыми значениями
в зависимости от выполнения условия, но также и производить некоторые вычисления.
К примеру, мы
предоставляем нашему покупателю скидку в зависимости от суммы покупки. Если
сумма больше 100, то он получает скидку 10%.
Назовём столбец Н “Скидка” и в ячейку H2 введём функцию ЕСЛИ, вторым аргументом которой будет формула расчёта скидки.
=ЕСЛИ(E2>100;F2*0.1;0)
Функция ЕСЛИ: примеры с несколькими условиями.
Итак, мы разобрались, как работает эта одна из самых часто применяемых функций. Обычная формула ЕСЛИ, которая проверяет одно условие, очень проста и проста в написании.
Но что, если ваши данные требуют более сложных логических проверок с несколькими условиями? В этом случае вы можете включить несколько функций ЕСЛИ в одну формулу, и это будет называться вложенными условиями, своего рода «ЕСЛИ в ЕСЛИ». Самым большим преимуществом такого подхода является то, что он позволяет проверять более одного условия и возвращать разные значения в зависимости от результатов этих проверок, и все это при помощи одной формулы.
Вот типичный пример «ЕСЛИ в ЕСЛИ». Предположим, у вас в таблице Excel есть список студентов в столбце A и их оценки по тестам в столбце B. Вы хотите классифицировать оценки по следующим условиям:
- «Отлично»: более 249 баллов
- «Хорошо»: от 249 до 200 включительно
- «Удовлетворительно»: от 199 до 150 включительно
- «Плохо»: до 150.
А теперь давайте напишем вложенную формулу ЕСЛИ на основе вышеуказанных критериев. Хорошей практикой считается начинать с самого важного условия и максимально упростить свои функции. Наша вложенная формула IF в Excel выглядит следующим образом:
=ЕСЛИ(B2>249; «Отлично»; ЕСЛИ(B2>=200; «Хорошо»; ЕСЛИ(B2>150; «Удовлетворительно»; «Плохо»)))
Многие считают, что вложенные условия слишком сложны. Попробуйте взглянуть на это под другим углом:
=ЕСЛИ(B2>249; «Отлично»;
ЕСЛИ(B2>=200; «Хорошо»;
ЕСЛИ(B2>150; «Удовлетворительно»; «Плохо»)))
На самом деле формула указывает Excel, что нужно выполнить логическую проверку первого условия и, если оно выполнено, вернуть значение, указанное в аргументе ИСТИНА . Если условие 1-й проверки не выполнено, то проверьте 2-е выражение, и так далее.
ЕСЛИ ( проверить, если B2> = 249, если ИСТИНА — вернуть «отлично», или же
ЕСЛИ ( проверить, если B2> = 200, если ИСТИНА — вернуть «хорошо», или же
ЕСЛИ ( проверить, если B2> 150, если ИСТИНА — вернуть «Удовлетворительно», если ЛОЖЬ —
вернуть «Плохо»)))
Вложенные условия с математическими выражениями.
Вот еще одна типичная задача: цена за единицу товара изменяется в зависимости от его количества. Ваша цель состоит в том, чтобы написать формулу, которая вычисляет цену для любого количества товаров, введенного в определенную ячейку. Другими словами, ваша формула должна проверить несколько условий и выполнить различные вычисления в зависимости от того, в какой диапазон суммы входит указанное количество товара.
Эта задача также может быть выполнена Excel с помощью нескольких вложенных функций ЕСЛИ. Логика та же, что и в приведенном выше примере, с той лишь разницей, что вы умножаете указанное количество на значение, возвращаемое вложенными условиями (т.е. соответствующей ценой за единицу).
Предполагая, что количество записывается в B8, формула будет такая:
=B8*ЕСЛИ(B8>=101; 12; ЕСЛИ(B8>=50; 14; ЕСЛИ(B8>=20; 16; ЕСЛИ( B8>=11; 18; ЕСЛИ(B8>=1; 22; «»)))))
И вот результат:
Как вы понимаете, этот пример демонстрирует только общий подход, и вы можете легко настроить эту вложенную функцию в зависимости от вашей конкретной задачи.
Например, вместо «жесткого кодирования» цен в самой формуле можно ссылаться на ячейки, в которых они указаны (ячейки с B2 по B6). Это позволит редактировать исходные данные без необходимости обновления самой формулы:
=B8*ЕСЛИ(B8>=101; B6; ЕСЛИ(B8>=50; B5; ЕСЛИ(B8>=20; B4; ЕСЛИ( B8>=11; B3; ЕСЛИ(B8>=1; B2; «»)))))
Объединяем несколько условий.
Для того, чтобы описать условие в первом аргументе функции ЕСЛИ, Excel позволяет использовать более сложные конструкции. В том числе можно использовать и несколько условий. При этом еще воспользуемся тем, что функции можно «вкладывать» внутрь друг друга.
Для объединения нескольких условий в одно используем логические функции ИЛИ и И. Рассмотрим простые примеры.
Пример 1
Функция ИЛИ возвращает ИСТИНА, если хотя бы одно из перечисленных в ней нескольких условий выполняется.
=ЕСЛИ(ИЛИ(C2=»Восток»;C2=»Юг»);»Экспорт»;»Местные»)
Вставляем функцию ИЛИ как условие в функцию ЕСЛИ. В нашем случае, если регион покупателя — Восток или Юг, то отгрузка считается экспортом.
Пример 2.
Используем несколько более сложных условий внутри функции ЕСЛИ.
Если регион продажи — Запад или Юг, и количество при этом больше 100, то предоставляется скидка 10%.
=ЕСЛИ(И(ИЛИ(C2=»Запад»;C2=»Юг»);E2>100);F2*0.1;0)
Функция И возвращает ИСТИНА, если выполняются все перечисленные в ней условия. Внутрь функции И мы помещаем два условия:
- Регион — или Запад или Юг
- Количество больше 100.
Первое из них реализуем так же, как это было сделано в первом примере: ИЛИ(C2=»Запад»;C2=»Юг»)
Второе — здесь всё очень просто: E2>100
В строке 2, 3 и 5 выполнены оба условия. Эти покупатели получат скидку.
В строке 4 не выполнено ни одного. А в строке 6,7,8 выполнено только первое, а вот количество слишком мало. Поэтому скидка будет равна нулю.
Пример 3.
Конечно, эти несколько условий могут быть и более сложными. Ведь логические функции можно «вкладывать» друг в друга.
Например, в дополнение к предыдущему условию, скидка предоставляется только на черный шоколад.
Все наше записанное ранее условие становится в свою очередь первым аргументом в новой функции И:
- Регион — Запад или Юг и количество больше 100 (рассмотрено в примере 2)
- В названии шоколада встречается слово «черный».
В итоге получаем формулу ЕСЛИ с несколькими условиями:
=ЕСЛИ(И(ЕЧИСЛО(НАЙТИ(«Черный»;D2)),
И(ИЛИ(C2=»Запад»;C2=»Юг»));E2>100);F2*0.1;0)
Функция НАЙТИ ищет точное совпадение. Если же регистр символов в тексте для нас не важен, то вместо НАЙТИ можно использовать аналогичную функцию СОВПАД.
=ЕСЛИ(И(ЕЧИСЛО(СОВПАД(«черный»;D2));
И(ИЛИ(C2=»Запад»;C2=»Юг»));E2>100);F2*0.1;0)
В итоге, количество вложенных друг в друга условий в Excel может быть очень большим. Важно только точно соблюдать логическую последовательность их выполнения.
Производим вычисления по условию.
Чтобы выполнить действие только тогда, когда ячейка не пуста (содержит какие-то значения), вы можете использовать формулу Excel, основанную на функции ЕСЛИ.
В примере ниже столбец F содержит даты завершения закупок шоколада.
Поскольку даты для Excel — это числа, то наша задача состоит в том, чтобы проверить в ячейке наличие числа.
Формула в ячейке F3:
=ЕСЛИ(СЧЁТЗ(D3:D9)=7;СУММ(C3:C9);»»)
Как работает эта формула?
Функция СЧЕТЗ (английский вариант — COUNTA) подсчитывает количество значений (текстовых, числовых и логических) в диапазоне ячеек Excel. Если мы знаем количество значений в диапазоне, то легко можно составить условие. Если число значений равно числу ячеек Excel, то значит, пустых среди них нет и можно производить вычисление. Если такого равенства нет, значит есть хотя бы одна пустая ячейка, и вычислять нельзя.
Согласитесь, что нельзя назвать этот способ определения наличия пустых ячеек удобным. Ведь число строк в таблице может измениться, и нужно будет менять формулу: вместо цифры 7 ставить другое число.
Давайте рассмотрим и другие варианты. В ячейке F6 записана большая формула, которая должна проверить условие «если не пусто».
=ЕСЛИ(ИЛИ(ЕПУСТО(D3);ЕПУСТО(D4);ЕПУСТО(D5);ЕПУСТО(D6);
ЕПУСТО(D7);ЕПУСТО(D8);ЕПУСТО(D9));»»;СУММ(C3:C9))
Функция ЕПУСТО (английский вариант — ISBLANK) проверяет, не ссылается ли она на пустую ячейку. Если это так, то возвращает ИСТИНА.
Функция ИЛИ (английский вариант — OR) позволяет объединить условия и указать, что нам достаточно того, чтобы хотя бы одна функция ЕПУСТО обнаружила пустую ячейку. В этом случае никаких вычислений не производим и функция ЕСЛИ возвращает пустую строку. А вот если не пусто – то производим вычисления.
Все достаточно просто, но перечислять кучу ссылок на ячейки не слишком удобно. К тому же, здесь, как и в предыдущем случае, формула не масштабируема: при изменении таблицы она нуждается в корректировке. Это не слишком удобно, да и забыть можно сделать это.
Рассмотрим теперь более универсальные решения.
=ЕСЛИ(СЧИТАТЬПУСТОТЫ(D3:D9);»»;СУММ(C3:C9))
В качестве аргумента условия в функции ЕСЛИ мы используем СЧИТАТЬПУСТОТЫ (английский вариант — COUNTBLANK). Она возвращает количество пустых ячеек, но любое число больше 0 Excel интерпретирует как ИСТИНА.
И, наконец, еще одна формула ЕСЛИ (IF) в Excel, которая проверит «если не пусто» и позволит производить расчет только при наличии непустых ячеек.
=ЕСЛИ(ЕЧИСЛО(D3:D9);СУММ(C3:C9);»»)
Функция ЕЧИСЛО (или ISNUMBER) возвращает ИСТИНА, если ссылается на число. Естественно, при ссылке на пустую ячейку возвратит ЛОЖЬ.
А теперь посмотрим, как это работает. Заполним таблицу недостающим значением.
Как видите, все наши формулы рассчитаны и возвратили одинаковые значения.
А теперь рассмотрим как проверить, что ячейки не пустые, если в них могут быть записаны не только числа, но и текст.
Итак, перед нами уже знакомое выражение
=ЕСЛИ(СЧЁТЗ(D3:D9)=7;СУММ(C3:C9);»»)
Для функции СЧЕТЗ не имеет значения, число или текст используются в ячейке Excel.
=ЕСЛИ(СЧИТАТЬПУСТОТЫ(D3:D9);»»;СУММ(C3:C9))
То же можно сказать и о функции СЧИТАТЬПУСТОТЫ.
А вот третий вариант — к проверке условия при помощи функции ЕЧИСЛО добавляем проверку ЕТЕКСТ (ISTEXT в английском варианте). Объединяем их функцией ИЛИ.
=ЕСЛИ(ИЛИ(ЕТЕКСТ(D3:D9);ЕЧИСЛО(D3:D9));СУММ(C3:C9);»»)
А теперь вставляем в ячейку D5 недостающее значение и проверяем, все ли работает.
Итак, мы с вами убедились, что простая на первый взгляд функция Excel ЕСЛИ дает нам на самом деле много возможностей для операций с данными.
Надеемся, этот материал был полезен. А вот еще несколько примеров работы с условиями «если – то» при помощи функции ЕСЛИ (IF) в Excel.
Примеры использования функции ЕСЛИ:
Функция ЕСЛИОШИБКА – примеры формул — В статье описано, как использовать функцию ЕСЛИОШИБКА в Excel для обнаружения ошибок и замены их пустой ячейкой, другим значением или определённым сообщением. Покажем примеры, как использовать функцию ЕСЛИОШИБКА с функциями визуального… 
Сравнение ячеек в Excel — Вы узнаете, как сравнивать значения в ячейках Excel на предмет точного совпадения или без учета регистра. Мы предложим вам несколько формул для сопоставления двух ячеек по их значениям, длине или количеству… 
Как проверить правильность ввода данных в Excel? — Рассмотрим несколько способов, при помощи которых мы можем подстраховать себя от случайных ошибок при вводе данных. Подтверждаем правильность ввода галочкой. Задача: При ручном вводе данных в ячейки таблицы проверять правильность… 
Функция ЕСЛИ: проверяем условия с текстом — Рассмотрим использование функции ЕСЛИ в Excel в том случае, если в ячейке находится текст. СодержаниеПроверяем условие для полного совпадения текста.ЕСЛИ + СОВПАДИспользование функции ЕСЛИ с частичным совпадением текста.ЕСЛИ + ПОИСКЕСЛИ… 
Визуализация данных при помощи функции ЕСЛИ — Функцию ЕСЛИ можно использовать для вставки в таблицу символов, которые наглядно показывают происходящие с данными изменения. К примеру, мы хотим показать в отдельной колонке таблицы, происходит рост или снижение продаж.… 
3 примера, как функция ЕСЛИ работает с датами. — На первый взгляд может показаться, что функцию ЕСЛИ для работы с датами можно применять так же, как для числовых и текстовых значений, которые мы только что обсудили. К сожалению, это… В табличном редакторе Microsoft Excel множество различных формул и функций. Они позволяют сэкономить время и избежать ошибок – достаточно правильно написать формулу и подставить нужные значения.
В этой статье мы рассмотрим функцию ВПР (или VLOOKUP, что означает «вертикальный просмотр»). Функция ВПР помогает работать с данными из двух таблиц и подтягивать значения из одной в другую. Использовать ее удобно, когда нужно посчитать выручку или прикинуть бюджет, если в одной таблице указан прайс-лист, а в другой количество проданного товара.
Допустим, есть таблица с количеством проданного товара и таблица с ценами на эти товары
Необходимо к каждому товару из таблицы слева добавить цену из прайса справа.
Как создать функцию ВПР в Excel
Необходимая последовательность значений в функции называется синтаксис. Обычно функция начинается с символа равенства «=», затем идет название функции и аргументы в скобках.
Записываем формулу в столбик цены (С2). Это можно сделать двумя способами:
-
Выделить ячейку и вписать функцию.
-
Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».
После этого открывается окно, где можно заполнить ячейки аргументов формулы.
Синтаксис функции ВПР выглядит так:
=ВПР(искомое значение;таблица;номер столбца;интервальный просмотр)
В нашем случае получится такая формула:
=ВПР(A2;$G$2:$H$11;2;0)
Аргументы функции ВПР
Сейчас разберемся что и куда писать.
Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.
Искомое значение – это название ячейки, из которой мы будем «подтягивать» данные. Формула ВПР ищет полное или частичное совпадение в другой таблице, из которой берет информацию.
В нашем случае выбираем ячейку «A2», в ней находится наименование товара. ВПР возьмет это название и будет искать аналогичную ячейку во второй таблице с прайсом.
=ВПР(A2;
Таблица – это диапазон ячеек, из которых мы будем «подтягивать» данные для искомого значения. В этом аргументе используем абсолютные ссылки. Это значит, что в формуле таблица будет выглядеть как «$G$2:$H$11» вместо «G2:H11». Знаки «$» можно поставить вручную, а можно выделить «G2:H11» внутри формулы и нажать F4. Если этого не сделать, таблица не зафиксируется в формуле и изменится при копировании.
В нашем случае – это таблица с прайсом. Формула будет искать в ней совпадение с ячейкой, которую указали в первом аргументе формулы – A2 (Кофе). Нажимаем F4 и делаем ссылку абсолютной.
=ВПР(A2;$G$2:$H$11
Номер столбца – это столбец таблицы, из которой нужно взять данные. Именно из него мы будем «подтягивать» результат.
-
Формула сканирует таблицу по вертикали.
-
Находит в самом левом столбце совпадение с искомым значением.
-
Смотрит в столбец напротив, очередность которого мы указываем в этом аргументе.
-
Передает данные в ячейку с формулой.
В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.
=ВПР(A2;$G$2:$H$11;2
Интервальный просмотр – это параметр, который может принимать 2 значения: «истина» или «ложь». Истина обозначается в формуле цифрой 1 и означает приблизительное совпадение с искомым значением. Ложь обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.
В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.
=ВПР(A2;$G$2:$H$11;2;0)
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Подписаться
Автозаполнение
В конце протягиваем формулу вниз до конца, в результате чего происходит автозаполнение.
Чтобы функция ВПР правильно сработала во время автозаполнения, искомое значение должно быть относительной ссылкой, а таблица – абсолютной.
-
В нашем случае искомое значение – A2. Это относительная ссылка на ячейку, потому что в ней нет знаков «$». Благодаря этому ссылка на искомое значение меняется относительно каждой строчки, когда происходит автозаполнение в другие ячейки: A2 → A3 → … → A11. Это удобно, когда необходимо повторить формулу на несколько строк, ведь ее не приходится писать заново.
-
Таблица зафиксирована абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменятся во время автозаполнения. Таким образом, расчет каждый раз будет корректным и опираться на таблицу.
ВПР и приблизительный интервальный просмотр
В предыдущем примере мы «подтягивали» значения из таблицы, используя точный интервальный просмотр. Он подходит для работы с наименованиями. Теперь разберем ситуацию, когда может понадобиться приблизительный интервальный просмотр.
Задача. В магазин привезли товар. Необходимо присвоить каждому товару размер партии, опираясь на его количество.
Товары такие же, как и в первом примере, но задача изменилась: нужно привязать формулу не к наименованию, а к количеству
Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.
Разница в том, что теперь искомое значение – число, а интервальный просмотр – истина, что означает приблизительный поиск. Получается вот такой результат:
Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение.
В нашем случае количество товара «Кофе» – 380. ВПР берет это число в виде искомого значения, после чего ищет ближайшее меньшее в соседней таблице – число 300. В конце функция «подтягивает» данные из столбца напротив («Крупная»). Если количество товара «Кофе» = 340 – это «Крупная партия». Важно, чтобы крайний левый столбец таблицы, которая указана в формуле, был отсортирован по возрастанию. В противном случае ВПР не сработает.
Значения и данные во второй таблице отсортированы по убыванию – ВПР не работает
Итоги
-
Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз.
-
Синтаксис функции: =ВПР(искомое значение;таблица;номер столбца;интервальный просмотр).
-
Функцию можно вписать вручную или в специальном окне (Shift + F3).
-
Искомое значение – относительная ссылка, а таблица – абсолютная.
-
Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением.
-
Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.
-
Порядок работы с функцией подходит для Гугл-таблиц.
#Руководства
-
0
Как из сотни автомобилей выбрать только те, что соответствуют запросу покупателя? Разбираемся на примере функции ЕСЛИ в Excel.
Иллюстрация: Meery Mary для Skillbox Media
Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.
ЕСЛИ — логическая функция Excel. Она проверяет, выполняются ли заданные условия в выбранном диапазоне таблицы. Пользователь указывает критерий, который нужно проверить, — функция сравнивает этот критерий с данными в ячейках таблицы и выдаёт результат.
В статье разберёмся:
- как работает и для чего нужна функция ЕСЛИ в Excel;
- как запустить функцию ЕСЛИ с одним условием;
- как запустить функцию с несколькими условиями.
Функцию ЕСЛИ используют, когда нужно сравнить данные таблицы с критериями пользователя. У функции есть два результата: ИСТИНА и ЛОЖЬ. Первый результат функция выдаёт, когда данные ячейки полностью совпадают с заданным условием, второй — когда данные ячейки условию не соответствуют.
Например, если нужно определить в таблице значения меньше 500, то значение 265 будет отмечено функцией как истинное, а значение 3426 — как ложное.
Можно задавать несколько условий одновременно. Например, найти значения меньше 500, но больше 300. В этом случае функция определит значение 265 как ложное, а 402 — как истинное. Так можно проверять не только числовые значения, но и текст.
Часто функцию ЕСЛИ используют при работе с другими функциями Excel для расширения их возможностей. Например, в случае с ВПР функция ЕСЛИ позволяет настроить поиск сразу по двум критериям.
Рассмотрим, как работает функция ЕСЛИ в классическом виде на примере.
Представим, что в автосалон обратился покупатель с просьбой подобрать ему автомобиль. Его запрос — автомобили чёрного или красного цвета, с объёмом двигателя больше 1,5 л, стоимостью до 2,5 млн рублей. Есть каталог автомобилей, но все характеристики и цены расположены в нём вразброс.
Скриншот: Excel / Skillbox Media
Нужно выяснить, какие из представленных в салоне авто подходят покупателю. Разберёмся пошагово, как это можно сделать с помощью функции ЕСЛИ в Excel.
Готовимся к запуску функции ЕСЛИ
Для начала рассмотрим, как функция ЕСЛИ работает в классическом виде — для проверки одного условия пользователя. Определим автомобили стоимостью до 2,5 млн рублей.
Принцип действия функции ЕСЛИ следующий. Алгоритм просматривает выбранный диапазон таблицы и проверяет, соответствуют ли данные его ячеек запросу пользователя. Затем возвращается в отдельную ячейку и оставляет там результат: ИСТИНА или ЛОЖЬ. О том, как настроить алгоритм функции под наши потребности, поговорим ниже. А сейчас на примере покажем, как подготовить таблицу к запуску функции и как вызвать окно для её построения.
Функция ЕСЛИ приносит результаты в отдельную ячейку, поэтому создадим отдельный столбец для них. Лучше сразу назвать его так, чтобы было понятно, о чём речь. В нашем случае сделаем столбец «До 2,5 млн руб.».
Скриншот: Excel / Skillbox Media
Выделим первую ячейку нового столбца и откроем окно для поиска функций. Это можно сделать двумя способами:
1. Перейти во вкладку «Формулы» и нажать «Вставить функцию».
Скриншот: Excel / Skillbox Media
2. Нажать на «fx» в строке ссылок на любой вкладке Excel.
Скриншот: Excel / Skillbox Media
На экране справа появится окно «Построитель формул». В нём через поиск находим функцию ЕСЛИ и нажимаем «Вставить функцию».
Скриншот: Excel / Skillbox Media
Появляется окно для заполнения аргументов функции: «Лог_выражение», «Значение_если_истина», «Значение_если_ложь». Ниже разберёмся, как их заполнить.
Скриншот: Excel / Skillbox Media
Функция ЕСЛИ предполагает, что пользователь создаёт запрос и указывает два варианта ответа на него. Этот запрос и варианты ответа — и есть три аргумента функции.
«Лог_выражение» (логическое выражение) — запрос пользователя, который функция будет проверять. В нашем примере это стоимость автомобилей.
Нужно, чтобы функция определила автомобили стоимостью менее или равной 2 500 000 рублей. Порядок действий следующий:
1. Ставим курсор в окно «Лог_выражение» в построителе формул.
2. Выбираем первое значение столбца «Цена, руб.» — ячейку Е2. Обозначение ячейки переносится в окно «Лог_выражение» построителя формул и одновременно с этим появляется в строке ссылок.
Скриншот: Excel / Skillbox Media
3. Дополняем значение E2 запросом пользователя: <=2500000. Одновременно с этим формула в строке ссылок принимает вид:fx=ЕСЛИ(E2<=2500000).
Скриншот: Excel / Skillbox Media
«Значение_если_истина» — результат, который функция принесёт в ячейку, если значение совпадёт с запросом пользователя. В случае с примером — что напишет функция, если проверяемая стоимость авто будет меньше либо равна 2 500 000 рублей.
Наша задача — определить, подходят автомобили каталога под запрос клиента или нет. Поэтому в окне аргумента истины введём значение «Подходит» — можно вводить его без кавычек, Excel добавит их самостоятельно.
Скриншот: Excel / Skillbox Media
«Значение_если_ложь» — результат, который функция принесёт в ячейку, если значение не совпадёт с запросом пользователя. В нашем примере — что напишет функция, если проверяемая стоимость авто будет больше 2 500 000 рублей.
Введём в качестве аргумента ЛОЖЬ значение «Не подходит».
Итоговая формула в строке ссылок примет вид:
fx=ЕСЛИ(E2<=2500000;»Подходит»;»Не подходит»).
Скриншот: Excel / Skillbox Media
Чтобы получить результат функции, нажимаем кнопку «Готово» в построителе формул.
Скриншот: Excel / Skillbox Media
В выбранной ячейке появится результат работы функции: «Подходит». Это значит, что первый автомобиль каталога подходит под запрос пользователя, — его цена 1 910 000 рублей.
Скриншот: Excel / Skillbox Media
Дальше нужно протянуть результат первой ячейки вниз до конца таблицы, чтобы функция проверила стоимость всех остальных автомобилей и отметила, подходят они под запрос покупателя или нет.
Скриншот: Excel / Skillbox Media
Чтобы настроить функцию ЕСЛИ с несколькими условиями, нужно добавить к ней дополнительные операторы Excel — «И», «ИЛИ».
Они связывают несколько критериев и, в зависимости от того, совпадают они с данными таблицы или нет, выдают результат. Схематично это будет выглядеть так: «ЕСЛИ условие a = 1, И/ИЛИ условие b = 2, И/ИЛИ условие c = 3, то — ИСТИНА, иначе — ЛОЖЬ».
В нашем примере у покупателя было три условия: цвет автомобиля, цена и объём двигателя. Разберёмся, как настроить функцию ЕСЛИ в этом случае.
Создадим ещё один столбец для новых результатов работы функции. Назовём его в соответствии с запросом покупателя: «До 2,5 млн руб., чёрный или красный, больше 1,5 л».
Скриншот: Excel / Skillbox Media
Вызываем окно построителя формул, как делали выше, и заполняем аргументы функции.
Отличия от классического варианта использования функции ЕСЛИ будут при заполнении первого аргумента «Лог_выражение»:
- Ставим курсор в окно «Лог_выражение» в построителе формул, вводим дополнительный аргумент «И» и открываем скобку.
- Первым условием будем вводить два цвета автомобилей, которые выбрал покупатель, поэтому вводим второй дополнительный аргумент «ИЛИ» и открываем ещё одну скобку.
- Выбираем первое значение столбца «Цвет» — ячейку B2. Ставим знак равенства и находим в этом же столбце значение из запроса пользователя: «чёрный» — ячейку B5. Ставим знак точки с запятой.
- Снова выбираем первое значение столбца «Цвет», ставим знак равенства и находим второй цвет из запроса пользователя: «красный» — ячейку B8. Закрываем скобку и ставим знак точки с запятой.
- Закрепляем выбранные ячейки с цветами. На Windows для этого поочерёдно выбираем значения ячеек в строке ссылок и нажимаем клавишу F4, на macOS — выбираем значения ячеек в строке ссылок и нажимаем клавиши Cmd + T. Закрепить эти ячейки нужно, чтобы в дальнейшем можно было протянуть формулу вниз и она сработала корректно для всех остальных строк.
- Выбираем первое значение столбца «Объём двигателя, л» — ячейку D2. Дополняем его запросом пользователя: >1,5. Также ставим знак точки с запятой.
- Выбираем первое значение столбца «Цена, руб.» — ячейку Е2. Дополняем его запросом пользователя: <=2500000, как делали в первом примере. Закрываем скобку.
Формула в строке ссылок принимает вид:
fx=ЕСЛИ(И(ИЛИ(B2=$B$5;B2=$B$8);D2>1,5;E2<=2500000)). Она объединит три запроса покупателя оператором «И», а в первом запросе предоставит возможность выбора с помощью оператора «ИЛИ».
Скриншот: Excel / Skillbox Media
Аргументы «Значение_если_истина» и «Значение_если_ложь» оставляем такими же, как в первом случае: «Подходит» и «Не подходит».
Итоговая формула в строке ссылок принимает вид:
fx=ЕСЛИ(И(ИЛИ(B2=$B$5;B2=$B$8);D2>1,5;E2<=2500000);Подходит»;»Не подходит»). Нажимаем кнопку «Готово».
В выбранной ячейке появляется результат: «Не подходит». Это значит, что первый автомобиль каталога не подходит под запрос покупателя — у него жёлтый цвет, а объём двигателя равен 1,5 л.
Скриншот: Excel / Skillbox Media
Протягиваем результат первой ячейки вниз до конца таблицы — функция проверяет остальные автомобили в каталоге и отмечает, какие из них подходят под все запросы покупателя.
Скриншот: Excel / Skillbox Media
Как пользоваться функцией ЕСЛИ в «Google Таблицах»? В них тоже есть функция ЕСЛИ, но нет окна построителя формул. Поэтому нужно прописывать её вручную, добавляя все пробелы и кавычки самостоятельно. Готовая формула будет выглядеть так:
fx=ЕСЛИ(E2<=»2500000″;»Подходит»;»Не подходит»).
- Руководство: как сделать ВПР в Excel и перенести данные из одной таблицы в другую
- Инструкция: как закреплять строки и столбцы в Excel
- Руководство по созданию выпадающих списков в Excel — как упростить заполнение таблицы повторяющимися данными
- Гайд о теории ТРИЗ, которую изучают менеджеры по всему миру
- Статья с советами эксперта, как сохранить бизнес в условиях кризиса, — о рынке, рисках, зарплатах и возможностях
Идите в бухгалтерию!
Вы с нуля научитесь вести бухучёт по российским стандартам и работать в 1С, готовить налоговую отчётность и рассчитывать зарплату. Трудоустройство гарантировано договором.
Узнать больше
#Руководства
-
0
Как перенести данные из одной таблицы в другую, если строки идут не по порядку? Разбираемся на примере каталога авто — переносим цены.
Иллюстрация: Meery Mary для Skillbox Media
Рассказывает просто о сложных вещах из мира бизнеса и управления. До редактуры — пять лет в банке и три — в оценке имущества. Разбирается в Excel, финансах и корпоративной жизни.
ВПР (Vlookup, или вертикальный просмотр) — поисковая функция в Excel. Она находит значения в одной таблице и переносит их в другую. Функция ВПР нужна, чтобы работать с большими объёмами данных — не нужно самостоятельно сопоставлять и переносить сотни наименований, функция делает это автоматически.
Разберёмся, зачем нужна функция и как её использовать. В конце материала расскажем, что делать, если нужен поиск данных сразу по двум параметрам.
Представьте, что вы продаёте автомобили. У вас есть каталог с характеристиками авто и их стоимостью. Также у вас есть таблица с данными клиентов, которые забронировали эти автомобили.
Вам нужно сообщить покупателям, сколько стоят их авто. Перед тем как обзванивать клиентов, нужно объединить данные: добавить во вторую таблицу колонку с ценами из первой.
Просто скопировать и вставить эту колонку не получится. Искать каждое авто вручную и переносить цены — долго.
ВПР автоматически сопоставит названия автомобилей в двух таблицах. Функция скопирует цены из каталога в список забронированных машин. Так напротив каждого клиента будет стоять не только марка автомобиля, но и цена.
Ниже пошагово и со скриншотами разберёмся, как сделать ВПР для этих двух таблиц с данными.
Важно!
ВПР может не работать, если таблицы расположены в разных файлах. Тогда лучше собрать данные в одном файле, на разных листах.
ВПР работает по следующему принципу. Функция просматривает выбранный диапазон первой таблицы вертикально сверху вниз до искомого значения‑идентификатора. Когда видит его, забирает значение напротив него из нужного столбца и копирует во вторую таблицу.
Подробнее о том, как определить все эти значения, поговорим ниже. А пока разберёмся на примере с продажей авто, где найти функцию ВПР в Excel и с чего начать работу.
Сначала нужно построить функцию. Для этого выделяем ячейку, куда функция перенесёт найденное значение.
В нашем случае нужно перенести цены на авто из каталога в список клиентов. Для этого добавим пустой столбец «Цена, руб.» в таблицу с клиентами и выберем ячейку напротив первого клиента.
Дальше открываем окно для построения функции ВПР. Есть два способа сделать это. Первый — перейти во вкладку «Формулы» и нажать на «Вставить функцию».
Второй способ — нажать на «fx» в строке ссылок на любой вкладке таблицы.
Справа появляется окно «Построитель формул». В нём через поисковик находим функцию ВПР и нажимаем «Вставить функцию».
Появляется окно для ввода аргументов функции. Как их заполнять — разбираемся ниже.
Последовательно разберём каждый аргумент: искомое значение, таблица, номер столбца, интервальный просмотр.
Искомое значение — название ячейки с одинаковыми данными для обеих таблиц, по которым функция будет искать данные для переноса. В нашем примере это модель авто. Функция найдёт модель в таблице с каталогом авто, возьмёт оттуда стоимость и перенесёт в таблицу с клиентами.
Порядок действий, чтобы указать значение, выглядит так:
- Ставим курсор в окно «Искомое значение» в построителе формул.
- Выбираем первое значение столбца «Марка, модель» в таблице с клиентами. Это ячейка A2.
Выбранное значение переносится в построитель формул и одновременно появляется в формуле строки ссылок: fx=ВПР(A2).
Таблица — это диапазон ячеек, из которого функция будет брать данные для искомого значения. В этот диапазон должны войти столбцы с искомым значением и со значением, которое нужно перенести в первую таблицу.
В нашем случае нужно перенести цены автомобилей. Поэтому в диапазон обязательно нужно включить столбцы «Марка, модель» (искомое значение) и «Цена, руб.» (переносимое значение).
Важно!
Для правильной работы ВПР искомое значение всегда должно находиться в первом столбце диапазона. У нас искомое значение находится в ячейке A2, поэтому диапазон должен начинаться с A.
Порядок действий для указания диапазона:
- Ставим курсор в окно «Таблица» в построителе формул.
- Переходим в таблицу «Каталог авто».
- Выбираем диапазон, в который попадают столбцы «Марка, модель» и «Цена, руб.». Это A2:E19.
- Закрепляем выбранный диапазон. На Windows для этого выбираем значение диапазона в строке ссылок и нажимаем клавишу F4, на macOS — выбираем значение диапазона в строке ссылок и нажимаем клавиши Cmd + T. Закрепить диапазон нужно, чтобы можно было протянуть функцию вниз и она сработала корректно во всех остальных строках.
Выбранный диапазон переносится в построитель формул и одновременно появляется в формуле строки ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19).
Номер столбца — порядковый номер столбца в первой таблице, в котором находится переносимое значение. Считается по принципу: номер 1 — самый левый столбец, 2 — столбец правее и так далее.
В нашем случае значение для переноса — цена — находится в пятом столбце слева.
Чтобы задать номер, установите курсор в окно «Номер столбца» в построителе формул и введите значение. В нашем примере это 5. Это значение появится в формуле в строке ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19;5).
Интервальный просмотр — условное значение, которое настроит, насколько точно сработает функция:
- Если нужно точное совпадение при поиске ВПР, вводим 0.
- Если нужно приближённое соответствие при поиске ВПР, вводим 1.
В нашем случае нужно, чтобы функция подтянула точные значения цен авто, поэтому нам подходит первый вариант.
Ставим курсор в окно «Интервальный просмотр» в построителе формул и вводим значение: 0. Одновременно это значение появляется в формуле строки ссылок: fx=ВПР(A2;’каталог авто’!$A$2:$E$19;5;0). Это окончательный вид функции.
Чтобы получить результат функции, нажимаем кнопку «Готово» в построителе формул. В выбранной ячейке появляется нужное значение. В нашем случае — цена первой модели авто.
Дальше нужно протянуть это значение вниз до конца таблицы, чтобы функция нашла и перенесла цены всех оставшихся моделей. Для этого мы закрепляли интервал, когда заполняли аргументы.
Получилась таблица с ценами — можно звонить клиентам и сообщать о стоимости авто. Данные перенесены без ошибок, а потратили мы на это несколько минут.
В нашем примере модели автомобилей в таблицах не повторяются, поэтому мы ищем только по одному критерию — названию модели. Но бывают случаи, когда такой поиск не подходит.
Например, у нас несколько одинаковых моделей с разным цветом.
И по традиции есть таблица с клиентами, которые эти модели забронировали.
Если идти по классическому пути ВПР, получится такая функция: fx=ВПР(A29;’каталог авто’!$A$29:$E$35;5;0). В таком виде ВПР найдёт первую совпавшую модель и подтянет её стоимость. Параметр цвета не будет учтён.
Соответственно, цены у всех Nissan Juke будут 1 850 000 рублей, у всех Subaru Forester — 3 190 000 рублей, у всех Toyota C-HR — 2 365 000 рублей.
Поэтому в этом варианте нужно искать стоимость авто сразу по двум критериям — модель и цвет. Для этого нужно изменить формулу вручную. В строке ссылок ставим курсор сразу после искомого значения.
Дописываем в формулу фразу ЕСЛИ(‘каталог авто’!$B$29:$B$35=B29, где:
- ‘каталог авто’!$B$29:$B$35 — закреплённый диапазон цвета автомобилей в таблице, откуда нужно перенести данные. Это весь столбец с ценами.
- B29 — искомое значение цвета автомобиля в таблице, куда мы переносим данные. Это первая ячейка в столбце с цветом — дополнительным параметром для поиска.
Итоговая функция такая: fx=ВПР(A29;ЕСЛИ(‘каталог авто’!$B$29:$B$35=B29;’каталог авто’!$A$29:$E$35);5;0). Теперь значения цен переносятся верно.
Как использовать ВПР в «Google Таблицах»? В них тоже есть функция Vlookup, но нет окна построителя формул. Поэтому придётся прописывать её вручную. Перечислите через точку с запятой все аргументы и не забудьте зафиксировать диапазон. Для фиксации поставьте перед каждым символом значок доллара. В готовой формуле это будет выглядеть так: =ВПР(A2;’Лист1′!$A$2:$C$5;3;0).
Идите в бухгалтерию!
Вы с нуля научитесь вести бухучёт по российским стандартам и работать в 1С, готовить налоговую отчётность и рассчитывать зарплату. Трудоустройство гарантировано договором.
Узнать больше
Как известно, табличный офисный редактор Excel изначально предназначен для произведения математических, алгебраических и других вычислений, для чего используется ввод специальных формул. Однако в свете того, как прописать формулу в Excel, необходимо учитывать, что большинство операций при вводе кардинально отличается от всего того, что принято использовать в обычной жизни. То есть сами формулы имеют несколько отличающийся вид и используют совсем не те операторы, которые применяются при записи стандартных вычислений.
Рассмотрим вопрос того, как прописать формулу в Excel, на нескольких простейших примерах, не затрагивая сложные операции, для понимания которых необходимо изучить программу достаточно глубоко. Но даже предварительные знания дадут любому пользователю понимание базовых принципов использования формул для разных случаев.
Как прописать формулу в Excel: начальные понятия
Итак, ввод формульных значений в программе несколько отличается от стандартных операций, использующихся символов и используемых операторов. При решении проблемы того, как прописать формулу в Excel, необходимо отталкиваться от базовых понятий, которые применяются практически во всех компьютерных системах.
Дело в том, что машина ввод сочетания вроде «2 х 2» или вынесение общего компонента за скобки («2+2)5») не понимает. Для этого предусмотрено использование нескольких типов символов, которые представлены в таблице ниже, не считая логических операторов.
|
Сумма, сложение |
+ |
|
Разность, вычитание |
— |
|
Умножение, произведение |
* |
|
Деление, частное |
/ |
|
Возведение в степень |
^ |
|
Проценты и операции с ними |
% |
|
Больше, меньше или равно, не равно |
>, <, >=, <=, <> |
При этом приоритет выполнения операций начинается от степени и заканчивается сложением и вычитанием. Кроме того, несмотря на то что Excel можно использовать, как обычный калькулятор, как правило, для вычислений необходимо указывать номера ячеек или их диапазоны. Само собой разумеется, что и формат данных в любой такой ячейке должен быть установлен соответствующий (хотя бы числовой).
Сумма и разность
Как в Excel прописать формулу суммы или разности? Итак, начнем с самого простого, когда требуется вычислить сумму. В строку формул (причем, для всех операций) сначала вводится знак равенства, после чего вписывается нужная формула. В случае с обычным калькулятором можно указать для установленной ячейки «=2+2».
Если же суммирование производится для значений, введенных непосредственно в других ячейках (например, A1 и A2), формула приобретает вид «=A1+A2». Нередко для использования дополнительных операторов сумма может заключаться в скобки. Для разности – то же самое, только с минусом вместо плюса.
Дело в том, что машина ввод сочетания вроде «2 х 2» или вынесение общего компонента за скобки («2+2)5») не понимает. Для этого предусмотрено использование нескольких типов символов, которые представлены в таблице ниже, не считая логических операторов.
|
Сумма, сложение |
+ |
|
Разность, вычитание |
— |
|
Умножение, произведение |
* |
|
Деление, частное |
/ |
|
Возведение в степень |
^ |
|
Проценты и операции с ними |
% |
|
Больше, меньше или равно, не равно |
>, <, >=, <=, <> |
При этом приоритет выполнения операций начинается от степени и заканчивается сложением и вычитанием. Кроме того, несмотря на то что Excel можно использовать, как обычный калькулятор, как правило, для вычислений необходимо указывать номера ячеек или их диапазоны. Само собой разумеется, что и формат данных в любой такой ячейке должен быть установлен соответствующий (хотя бы числовой).
Сумма и разность
Как в Excel прописать формулу суммы или разности? Итак, начнем с самого простого, когда требуется вычислить сумму. В строку формул (причем, для всех операций) сначала вводится знак равенства, после чего вписывается нужная формула. В случае с обычным калькулятором можно указать для установленной ячейки «=2+2».
Если же суммирование производится для значений, введенных непосредственно в других ячейках (например, A1 и A2), формула приобретает вид «=A1+A2». Нередко для использования дополнительных операторов сумма может заключаться в скобки. Для разности – то же самое, только с минусом вместо плюса.
Когда нужно указать номера ячеек или их диапазон, может применяться специальная команда суммы (в русском варианте «СУММ», в английском – SUM). При указании нескольких ячеек это выглядит так: «=СУММ(A1;A2)», для диапазона – «СУММ(A1:A10)» при условии того, что нужно вычислить сумму всех чисел, находящихся в ячейках с первой по десятую. В принципе, если установить активной ячейку, которая находится сразу же после последней в столбце с исходными значениями, формулу можно не вводить, а просто нажать на кнопку автоматического суммирования.
Умножение, деление и возведение в степень
Теперь посмотрим, как в Excel прописать формулу умножения или деления. Порядок тот же, что и при вводе суммы или разности, отличаются только операторы.
Для произведения используется вид «=A1*A2», для частного – «A1/A2». Это, кстати, точно такие же команды, которые можно встретить при использовании стандартного Windows-калькулятора.
Для произведения используется вид «=A1*A2», для частного – «A1/A2». Это, кстати, точно такие же команды, которые можно встретить при использовании стандартного Windows-калькулятора.
Для возведения в степень применяется символ «^». Для значения в ячейке A1, которое, например, нужно возвести в квадрат, применяется формула «=A1^2».
Процентные вычисления
С процентами, если не затрагивать сложные вычисления, тоже все просто. Как в Excel прописать формулу с процентами?
Достаточно ввести формулу вида «=A1*5%», после чего вы и получите те самые пять процентов от значения в соответствующей ячейке.
Использование ввода формул на основе выделения ячеек
Но все это относилось к ручному заданию или так называемому директ-вводу формул (прямой или непосредственный ввод). На самом деле иногда бывает полезно использовать мышь и клавишу Ctrl.
При зажатой клавише мышью можно просто выделять нужные ячейки, предварительно введя необходимое вычисление в строке формул. Ячейки будут добавляться прямо в строку формул. Но, в зависимости от типа формулы, иногда круглые скобки придется ставить вручную.
Абсолютные, относительные и смешанные типы ячеек
Отдельно стоит отметить, что в программе может использоваться несколько типов ячеек, не говоря о данных, которые они содержат.
Абсолютная ячейка является неизменной и обозначается, как $A$1, относительная представляет собой ссылку на обычное местоположение (A1), смешанная – есть комбинация ссылок и на абсолютную, и на относительную ячейку ($A1 или A$1). Обычно такие форматы применяются при создании перекрестных ссылок, когда в формулах задействованы данные, находящиеся на разных листах книги или даже в разных файлах.
При зажатой клавише мышью можно просто выделять нужные ячейки, предварительно введя необходимое вычисление в строке формул. Ячейки будут добавляться прямо в строку формул. Но, в зависимости от типа формулы, иногда круглые скобки придется ставить вручную.
Абсолютные, относительные и смешанные типы ячеек
Отдельно стоит отметить, что в программе может использоваться несколько типов ячеек, не говоря о данных, которые они содержат.
Абсолютная ячейка является неизменной и обозначается, как $A$1, относительная представляет собой ссылку на обычное местоположение (A1), смешанная – есть комбинация ссылок и на абсолютную, и на относительную ячейку ($A1 или A$1). Обычно такие форматы применяются при создании перекрестных ссылок, когда в формулах задействованы данные, находящиеся на разных листах книги или даже в разных файлах.
Формулы ВПР
Наконец, посмотрим, как в Excel прописать формулу ВПР (VLOOKUP). Такая методика позволяет вставить данные из одного диапазона в другой. В этом случае способ несколько похож на применяющийся при решении проблемы того, как в Excel прописать формулу «Условие», в котором используются символы, приведенные в таблице выше.
В общем смысле такие вычисления представляют собой нечто вроде простого фильтра, применяемого к столбцам, когда требуется отфильтровать только точные, а не приблизительные значения.
В этом варианте сначала через «Мастер функций» используется диапазон значений исходной (первой) таблицы, в поле «Таблица» указывается второй диапазон с фиксацией содержимого (F4), далее указывается номер столбца, а в поле интервального просмотра устанавливается значение «ЛОЖЬ», если действительно при фильтрации действительно нужно получить только точные, а не приблизительные значения. Как правило, такие формулы используются больше в складском или бухгалтерском учете, когда установить какие-то специализированные программные продукты не представляется возможным.
Заключение
Остается сказать, что здесь были описаны далеко не все формулы, которые можно использовать в табличном редакторе Excel. Это, так сказать, только азы. На самом деле, если копнуть еще в тригонометрию или вычисления логарифмов, матриц или даже тензорных уравнений, все выглядит гораздо сложнее. Но для того чтобы все это изучить, необходимо основательно проштудировать руководство по самому редактору. И это уже речь не идет о том, что в Excel на основе изменяющихся данных можно создавать даже простейшие логические игры. В качестве примера можно привести ту же «змейку», которая изначально к табличному редактору никакого отношения не имела, но была воспроизведена энтузиастами своего дела именно в Excel.
В остальном же следует четко понимать, что, изучив примитивные формулы или действия с данными, потом можно будет с легкостью освоить и более сложные вычисления, скажем, с созданием перекрестных ссылок, использованием разного рода сценариев или VB-скриптов и т.д. На все это нужно время, так что, если хотите изучить программу и все ее возможности по максимуму, придется попотеть над теоретической частью.