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

как пользоваться, создавать и изменять

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

Кому важно знать Excel и где выучить основы

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

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

На онлайн-курсе Skypro «Аналитик данных» научитесь владеть базовыми формулами Excel, работать с нестандартными данными, статистикой.

Еще используют символы сравнения:

равенство =

меньше <

больше >

меньше либо равно <=

больше либо равно >=

не равно <>

Основные виды

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

Простые

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

✅ СУММ. Складывает несколько чисел. Сумму можно посчитать для нескольких ячеек или целого диапазона.

=СУММ(А1;В1) — для соседних ячеек;

=СУММ(А1;С1;h2) — для определенных ячеек;

=СУММ(А1:Е1) — для диапазона.

Сумма всех чисел в ячейках от А1 до Е1

✅ ПРОИЗВЕД. Умножает числа в соседних, выбранных вручную ячейках или диапазоне.

=ПРОИЗВЕД(А1;В1)

=ПРОИЗВЕД(А1;С1;h2)

=ПРОИЗВЕД(А1:Е1)

Произведение всех чисел в ячейках от А1 до Е1

✅ ОКРУГЛ. Округляет дробное число до целого в большую или меньшую сторону. Укажите ячейку с нужным числом, в качестве второго значения — 0.

=ОКРУГЛВВЕРХ(А1;0) — к большему целому числу;

=ОКРУГЛВНИЗ(А1;0) — к меньшему.

Округление в меньшую сторону

✅ ВПР. Находит данные в таблице или определенном диапазоне.

=ВПР(С1;А1:В6;2)
  • С1 — ячейка, в которую выписывают известные данные. В примере это код цвета.
  • А1 по В6 — диапазон ячеек. Ищем название цвета по коду.
  • 2 — порядковый номер столбца для поиска. В нём указаны названия цвета.

Формула вычислила, какой цвет соответствует коду

✅ СЦЕПИТЬ. Объединяет данные диапазона ячеек, например текст или цифры. Между содержимым ячеек можно добавить пробел, если объединяете слова в предложения.

=СЦЕПИТЬ(А1;В1;С1) — текст без пробелов;

=СЦЕПИТЬ(А1;» «;В1;» «С1) — с пробелами.

Формула объединила три слова в одно предложение

✅ КОРЕНЬ. Вычисляет квадратный корень числа в ячейке.

=КОРЕНЬ(А1)

Квадратный корень числа в ячейке А1

✅ ПРОПИСН. Преобразует текст в верхний регистр, то есть делает буквы заглавными.

=ПРОПИСН(А1:С1)

Формула преобразовала строчные буквы в прописные

✅ СТРОЧН. Переводит текст в нижний регистр, то есть делает из больших букв маленькие.

=СТРОЧН(А2)

✅ СЧЕТ. Считает количество ячеек с числами.

=СЧЕТ(А1:В5)

Формула вычислила, что в диапазоне А1:В5 четыре ячейки с числами

✅ СЖПРОБЕЛЫ. Убирает лишние пробелы. Например, когда переносите текст из другого документа и сомневаетесь, правильно ли там стоят пробелы.

=СЖПРОБЕЛЫ(А1)

Формула удалила двойные и тройные пробелы

Сложные

✅ ПСТР. Выделяет определенное количество знаков в тексте, например одно слово.

=ПСТР(А1;9;5)
  1. Введите =ПСТР.
  2. Кликните на ячейку, где нужно выделить знаки.
  3. Укажите номер начального знака: например, с какого символа начинается слово. Пробелы тоже считайте.
  4. Поставьте количество знаков, которые нужно выделить из текста. Например, если слово состоит из пяти букв, впишите цифру 5.

В ячейке А1 формула выделила 5 символов, начиная с 9-го

✅ ЕСЛИ. Анализирует данные по условию. Например, когда нужно сравнить одно с другим.

=ЕСЛИ(A1>25;"больше 25";"меньше или равно 25")

В формуле указали:

  • А1 — ячейку с данными;
  • >25 — логическое выражение;
  • больше 25, меньше или равно 25 — истинное и ложное значения.

Первый результат возвращается, если сравнение истинно. Второй — если ложно.

Число в А1 больше 25. Поэтому формула показывает первый результат — больше 25.

✅ СУММЕСЛИ. Складывает числа, которые соответствуют критерию. Обычно критерий — числовой промежуток или предел. 

=СУММЕСЛИ(В2:В5;">10")

В формуле указали:

  • В2:В5 — диапазон ячеек;
  • >10 — критерий, то есть числа меньше 10 не будут суммироваться.

Число 8 меньше указанного в условии, то есть 10. Поэтому оно не вошло в сумму.

✅ СУММЕСЛИМН. Складывает числа, когда условий несколько. В формуле указывают диапазоны — ячейки, которые нужно учитывать. И условия — содержание подходящих ячеек. Например:

=СУММЕСЛИМН(D2:D6;C2:C6;"сувениры";B2:B6;"ООО ХY")
  • D2:D6 — диапазон, из которого суммируем числа;
  • C2:C6 — диапазон ячеек для категории;
  • сувениры — условие, то есть числа другой категории учитываться не будут;
  • B2:B6 — диапазон ячеек для компании;
  • ООО XY — условие, то есть числа другой компании учитываться не будут.

Под условия подошли только ячейки D3 и D6: их сумму и вывела формула

Комбинированные

В Excel можно комбинировать несколько функций: сложение, умножение, сравнение и другие. Например, вам нужно найти сумму двух чисел. Если значение больше 65, сумму нужно умножить на 1,5. Если меньше — на 2.

=ЕСЛИ(СУММ(A1;B1)<65;СУММ(A1;B1)*1,5;(СУММ(A1;B1)*2))

То есть если сумма двух чисел в А1 и В1 окажется меньше 65, программа посчитает первое условие — СУММ(А1;В1)*1,5. Больше 65 — Excel задействует второе условие — СУММ(А1;В1)*2.

Сумма в А1 и В1 больше 65, поэтому формула посчитала по второму условию: умножила на 2

Встроенные

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

  1. Поместите курсор в нужную ячейку.
  2. Откройте диалоговое окно мастера: нажмите клавиши Shift + F3. Откроется список функций.
  3. Выберите нужную формулу. Нажмите на нее, затем на «ОК». Откроется окно «Аргументы функций».
  4. Внесите нужные данные. Например, числа, которые нужно сложить.

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

Как скопировать

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

  1. Впишите функцию в ячейку и кликните на нее.
  2. Наведите курсор на правый нижний угол — курсор примет форму креста.
  3. Нажмите левую кнопку мыши, удерживайте ее и тяните до нужной ячейки.
  4. Отпустите кнопку. Появится итог.

Посчитали сумму ячеек в трех строках

Как обозначить постоянную ячейку

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

  1. Нажмите на ячейку с формулой.
  2. Поместите курсор в нужную ячейку и нажмите F4.
  3. В формуле фрагмент с описанием ячейки приобретет вид $A$1. Если вы протянете формулу, то ссылка на ячейку $A$1 останется на месте.

Как поставить «плюс», «равно» без формулы

Когда нужна не формула, а данные, например +10 °С:

  1. Кликните правой кнопкой по ячейке.
  2. Выберите «Формат ячеек».
  3. Отметьте «Текстовый», нажмите «ОК».
  4. Поставьте = или +, затем нужное число.
  5. Нажмите Enter.

Вебинары

Главное о формулах в Excel

  • Формула состоит из математических знаков. Чтобы ее вписать, используют символы = ( ) ; : .
  • С помощью простых формул числа складывают, умножают, округляют, извлекают из них квадратный корень. Чтобы отредактировать текст, используют формулы поиска, изменения регистра, удаления лишних пробелов.
  • Сложные и комбинированные формулы помогают делать объемные вычисления, когда нужно соблюдать несколько условий.

Орг_обр_ел_інф_2017-2018Практичні завдання — Excel Практична робота 2.1

  • Функция ЕСЛИ В Excel с несколькими условиями
    • Расширение функционала с помощью операторов «И» и «ИЛИ»
      • Как сравнить данные в двух таблицах

         

        file-assets/exampe_if_2_1.xlsx

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

         

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

        Операторы сравнения — равно, больше, меньше чем в Excel

        равно | Больше, чем | Менее | Больше или равно | Меньше или равно | Не равно

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

        Равно

        оператор равенства (=) возвращает ИСТИНА, если два значения равны друг другу.

        1. Например, взгляните на формулу в ячейке C1 ниже.

        Объяснение: формула возвращает ИСТИНА, поскольку значение в ячейке A1 равно значению в ячейке B1. Всегда начинайте формулу со знака равенства (=).

        2. В приведенной ниже функции ЕСЛИ используется оператор равенства.

        Объяснение: если два значения (числа или текстовые строки) равны друг другу, функция ЕСЛИ возвращает Да, в противном случае она возвращает Нет.

        Больше

        Оператор «больше» (>) возвращает значение TRUE, если первое значение на больше, чем второе значение на .

        1. Например, взгляните на формулу в ячейке C1 ниже.

        Объяснение: формула возвращает ИСТИНА, поскольку значение в ячейке A1 больше, чем значение в ячейке B1.

        2. В приведенной ниже функции ИЛИ используется оператор «больше».

        Объяснение: эта функция ИЛИ возвращает ИСТИНА, если хотя бы одно значение больше 50, в противном случае она возвращает ЛОЖЬ.

        Меньше

        Оператор меньше чем (<) возвращает значение TRUE, если первое значение на меньше второго значения на .

        1. Например, взгляните на формулу в ячейке C1 ниже.

        Объяснение: формула возвращает ИСТИНА, поскольку значение в ячейке A1 меньше значения в ячейке B1.

        2. Приведенная ниже функция И использует оператор «меньше».

        Объяснение: эта функция И возвращает ИСТИНА, если оба значения меньше 80, иначе она возвращает ЛОЖЬ.

        Больше или равно

        Оператор больше или равно (>=) возвращает TRUE, если первое значение на больше или равно второму значению.

        1. Например, взгляните на формулу в ячейке C1 ниже.

        Объяснение: формула возвращает ИСТИНА, поскольку значение в ячейке A1 больше или равно значению в ячейке B1.

        2. Функция СЧЁТЕСЛИ ниже использует оператор больше или равно.

        Объяснение: эта функция СЧЁТЕСЛИ подсчитывает количество ячеек, которые больше или равны 10.

        Меньше или равно

        Оператор меньше или равно (меньше или равно второму значению.

        1. Например, взгляните на формулу в ячейке C1 ниже:

        Объяснение: формула возвращает ИСТИНА, поскольку значение в ячейке A1 меньше или равно значению в ячейке B1

        2. СУММЕСЛИ Функция ниже использует оператор меньше или равно.0003

        Объяснение: эта функция СУММЕСЛИ суммирует значения в диапазоне A1:A5, которые меньше или равны 10.

        Не равно

        Оператор не равно () возвращает ИСТИНА, если два значения не равны друг друга.

        1. Например, взгляните на формулу в ячейке C1 ниже.

        Объяснение: формула возвращает ИСТИНА, поскольку значение в ячейке A1 не равно значению в ячейке B1.

        2. В приведенной ниже функции ЕСЛИ используется оператор «не равно».

        Объяснение: если два значения (числа или текстовые строки) не равны друг другу, функция ЕСЛИ возвращает Нет, в противном случае она возвращает Да.

        Функция СЧЁТЕСЛИ в Excel (Простое руководство)

        Числовые критерии | Текстовые трюки | Считать логические значения | Подсчет ошибок | и критерии | Или Критерии | Подробнее о Countif | Count Magic

        Мощная функция COUNTIF в Excel подсчитывает ячейки на основе одного критерия. Эта страница содержит много простых примеров COUNTIF.

        Числовые критерии

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

        1. Функция СЧЁТЕСЛИ ниже подсчитывает количество ячеек, равны 20.

        2. Следующая функция СЧЁТЕСЛИ дает точно такой же результат.

        3. Приведенная ниже функция СЧЁТЕСЛИ подсчитывает количество ячеек, которые больше или равны 10.

        4. Следующая функция СЧЁТЕСЛИ дает точно такой же результат.

        Объяснение: оператор & соединяет символ «больше или равно» и значение в ячейке C1.

        5. Приведенная ниже функция СЧЁТЕСЛИ подсчитывает количество ячеек, число которых равно , а не равно 7.

        Text Tricks

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

        1. Приведенная ниже функция СЧЁТЕСЛИ подсчитывает количество ячеек, содержащих ровно звездочку.

        2. Приведенная ниже функция СЧЁТЕСЛИ подсчитывает количество ячеек, содержащих точно звездочку + 1 символ. Знак вопроса (?) соответствует ровно одному символу.

        3. Приведенная ниже функция СЧЁТЕСЛИ подсчитывает количество ячеек, содержащих точно звездочку + последовательность из нуля или более символов. Звездочка (*) соответствует последовательности из нуля или более символов.

        4. Приведенная ниже функция СЧЁТЕСЛИ подсчитывает количество ячеек, содержащих звездочку.

        5. Приведенная ниже функция СЧЁТЕСЛИ подсчитывает количество ячеек, содержащих текст.

        Подсчет логических значений

        Используйте функцию СЧЁТЕСЛИ в Excel для подсчета логических значений (ИСТИНА или ЛОЖЬ).

        1. Приведенная ниже функция СЧЁТЕСЛИ подсчитывает количество ячеек, содержащих логическое значение ИСТИНА.

        2. Приведенная ниже функция СЧЁТЕСЛИ подсчитывает количество ячеек, содержащих логическое значение ЛОЖЬ.

        Подсчет ошибок

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

        1. Приведенная ниже функция СЧЁТЕСЛИ подсчитывает количество ячеек, содержащих #ИМЯ? ошибка.

        2. Приведенная ниже формула массива подсчитывает общее количество ошибок в диапазоне ячеек.

        Примечание: закончите формулу массива, нажав CTRL + SHIFT + ENTER. Excel добавляет фигурные скобки {}. В Excel 365 или Excel 2021 закончите, просто нажав Enter. Вы не увидите фигурных скобок. Посетите нашу страницу о подсчете ошибок для получения подробных инструкций о том, как создать эту формулу массива.

        Критерии И

        Подсчет с критериями И в Excel прост. Функция СЧЁТЕСЛИМН (с буквой S в конце) в Excel подсчитывает ячейки на основе двух или более критериев.

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

        Или Критерии

        Подсчет с помощью критерия Или в Excel может быть сложным.

        1. Приведенные ниже функции СЧЁТЕСЛИ подсчитывают количество ячеек, содержащих Google или Facebook (один столбец). Пока нет ракетостроения.

        2. Однако, если вы хотите подсчитать количество строк, содержащих Google или Stanford (два столбца), вы не можете просто дважды использовать функцию СЧЁТЕСЛИ (см. рисунок ниже).

        Примечание. Строки, содержащие Google и Stanford, учитываются дважды, но их следует учитывать только один раз. 4 — это ответ, который мы ищем.

        3. Приведенная ниже формула массива делает свое дело.

        Примечание: закончите формулу массива, нажав CTRL + SHIFT + ENTER. Excel добавляет фигурные скобки {}. В Excel 365 или Excel 2021 закончите, просто нажав Enter. Вы не увидите фигурных скобок. Посетите нашу страницу о подсчете с помощью критериев Or, чтобы узнать, как создать эту формулу массива.

        Подробнее о СЧЁТЕСЛИ

        Функция СЧЁТЕСЛИ — отличная функция. Давайте взглянем на еще несколько крутых примеров.

        1. В приведенной ниже функции СЧЁТЕСЛИ используется именованный диапазон. Именованный диапазон Ages относится к диапазону A1:A6.

        2. Приведенная ниже функция СЧЁТЕСЛИ подсчитывает количество ячеек, которые меньше среднего значения возрастов (32.2).

        3. Для подсчета ячеек между двумя числами используйте функцию СЧЁТЕСЛИМН (с буквой S в конце).

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

        Примечание: ячейка B2 содержит формулу =СЧЁТЕСЛИ(Возраст,A2), ячейка B3 =СЧЁТЕСЛИ(Возраст,A3) и т.