Green-sell.info

Новые технологии
1 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Excel значение ячейки несколько условий

Суммирование значений с учетом нескольких условий

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

Обратите внимание на этот пример, в котором у нас есть два условия: требуется сумма продаж мясо (из столбца C) в Южной области (из столбца a).

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

= СУММЕСЛИМН (D2: D11; a2: A11; «Юг»; C2: C11; «мясо»)

Результат — значение 14 719.

Рассмотрим более подробное представление каждой части формулы.

=СУММЕСЛИМН — это арифметическая формула. Вычисляет числа, которые в данном случае находятся в столбце D. Первый шаг — указать расположение чисел.

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

Затем нужно найти данные, отвечающие двум условиям, и ввести первое условие, указав для функции расположение данных (a2: A11), а также то, что такое условие — «Юг». Обратите внимание запятые между отдельными аргументами.

Кавычки вокруг слова «Юг» определяют, что эти текстовые данные.

Наконец, вы вводите аргументы для второго условия — диапазон ячеек (C2:C11), которые содержат слово «Мясо», а также само слово (заключенное в кавычки), чтобы приложение Excel смогло их сопоставить. Завершите формулу, закрыв закрывающая круглая скобка ) , а затем нажмите клавишу ВВОД. Результат — еще раз в 14 719.

По мере ввода функции СУММЕСЛИМН в Excel, если вы не помните эти аргументы, Справка готова. После ввода формулы = СУММЕСЛИМН (Автозаполнение формул появится под формулой, а список аргументов будет указан в нужном порядке.

Взгляните на изображение автозаполнения формул и списка аргументов в нашем примере сум_ранже— это D2: D11, столбец чисел, которые требуется суммировать. criteria_range1— a2. A11 — столбец с данными, в котором находится условие1 «Южный».

По мере того, как вы вводите формулу, в автозавершении формулы появятся остальные аргументы (здесь они не показаны); диапазон_условия2 — это диапазон C2:C11, представляющий собой столбец с данными, в котором находится условие2 — “Мясо”.

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

Попробуйте попрактиковаться

Если вы хотите поэкспериментировать с функцией СУММЕСЛИМН, вот несколько примеров данных и формула, использующая функцию.

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

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

Формула в Эксель ЕСЛИ, когда несколько условий

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

Функция ЕСЛИ

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

  • Открываем Эксель, найдя программу в меню «Пуск» или на рабочем столе.

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

  • Добавляем столбец «Премия» — в него выводим результаты функции ЕСЛИ. Курсор ставим в ячейку G4.

  • Кликаем по значку функции, расположенному слева от строки ввода оператора, которая находится над рабочим полем. Также формулу в Эксель можно вставить, обратившись к пункту меню «Формулы» и выбрав там «Логические».

  • В «Категории» находим «Логические», а в появившемся списке — функцию ЕСЛИ. Кликаем по «Ок».

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

  • Предположим, премия менеджеров составляет 30%. Заполняем аргументы, начиная с логического выражения — пишем там адрес ячейки и нужное значение. В нашем случае это выглядит следующим образом: Лог_выражение = D4=«менеджер». Затем указываем размер премии (30), если выражение истинно, и 0, если оно ложно. Нажимаем «Ок».

  • В результате видим, что премия первого сотрудника составляет 0%, так как он не является менеджером. Условие выполнено!

  • Таблица содержит множество строк, необходимо заполнить их все. Конечно, не требуется каждый раз вбивать функцию заново, ее надо просто скопировать. Сделать это очень просто — кликаем мышкой по ячейке G4 (там уже получен результат), подводим курсор к левому нижнему углу и смотрим, когда он превратится в плюс.

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

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

Читать еще:  Excel каждый файл в отдельном окне

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

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

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

  • Выделяем мышкой первую ячейку (G4) в столбце с премиями. Кликаем по значку Fx, находящемуся слева от строки ввода формул.

  • Появится окно с уже заполненными аргументами функции.

  • Изменяем логическое выражение, добавив туда еще одно условие и объединив их с помощью оператора И (условия берем в скобки). В нашем случае получится: Лог_выражение = И(D4=«менеджер»;E4=«Южный»). Нажимаем «Ок».

  • Растягиваем формулу на все ячейки, выделив первую и потянув мышкой вниз при нажатой левой клавише.

Совет: если в таблице много строк, то становится неудобно постоянно перематывать вверх-вниз, чтобы посмотреть шапку. Выход есть — закрепить строку в Excel. Тогда названия столбцов будут всегда показаны на экране.

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

В качестве примера рассмотрим, как начислить в Экселе премию в размере 40% всем сотрудникам, которые являются бухгалтерами или директорами. То есть произведем выборку по двум условиям:

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

  • Редактируем аргументы функции. Логическое выражение будет представлять собой: ИЛИ(D4=«бухгалтер»;D4=«директор»). В «Значение_если_истина» пишем 40, а в «Значение_если_ложь» — 0. Кликаем «Ок».

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

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

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

  • Добавляем строку с общей зарплатой продавцов и кликаем по ячейке, куда будет выводится результат.

  • Нажимаем на иконку Fx, которая находится слева от строки ввода функций. В открывшемся окне ищем нужную формулу через поиск — вводим в соответствующее окно «суммесли», выбираем оператор в списке, кликаем «Ок».

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

  • Вводим аргументы — первое поле «Диапазон» определяет, какие ячейки нужно проверить. В данном случае — должности работников. Кликаем мышкой в поле «Диапазон» и указываем там D4:D18. Можно поступить еще проще — просто выделить нужные ячейки.

  • В поле «Критерий» вводим «продавец». В «Диапазоне_суммирования» пишем ячейки с зарплатой сотрудников (вручную либо выделив их мышкой). Далее — «Ок».

  • Смотрим на результат — общая заработная плата всех продавцов посчитана.

Совет: сделать диаграмму в Excel просто и быстро — нужно всего лишь найти соответствующую кнопку на вкладке «Вставка» в меню.

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

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

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

  • Кликаем по значку функции. В появившемся окне в поле поиска вводим «суммеслимн», нажимаем «Найти» и выбираем нужный оператор из списка.

  • В открывшемся окне необходимо заполнить аргументы функции. В «Диапазон_суммирования» указываем ячейки с заработной платой. «Диапазон_условия1» — ячейки с должностями сотрудников. «Условие1» = «менеджер», так как мы суммируем зарплату менеджеров. Теперь нужно учесть второе условие — взять менеджеров из Южного филиала. В «Диапазон_условия2» вводим ячейки с филиалами, «Условие2» = «Южный». Все аргументы определены, нажимаем «Ок».

  • В результате будет рассчитана общая зарплата всех менеджеров, работающих в Южном филиале.

Функция СЧЁТЕСЛИ

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

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

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

  • Заполняем аргументы функции — в поле «Диапазон» указываем ячейки с должностями, в «Критерии» пишем «продавец». Далее — «Ок».

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

Функция СЧЁТЕСЛИМН

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

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

  • Кликаем по кнопке «Вставить функцию» во вкладке «Формулы». Через алфавитный перечень находим нужную функцию и нажимаем «Ок».

  • Вводим аргументы функции: «Диапазон_условия1» — это ячейки с должностями, «Условие1» = «продавец». В «Диапазон_условия2» пишем ячейки с филиалами, «Условие2» = «Северный».

  • В итоге будет определено количество продавцов Северного филиала.

Подводим итоги

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

Читать еще:  Двойное если в excel

Как выбрать в Экселе все значения по нескольким условиям

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

В этой статье мы решим такую задачу. Описываю пример. Есть ведомость школьных оценок 1-3 классов. Мы хотим выбрать класс, оценку, и увидеть учеников класса, которые получили этот балл:

Задача интересная, и достаточно простая. Однако, нам понадобится сделать дополнительный столбец. Действуем в несколько этапов.

Создание вспомогательной колонки

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

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

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

Формулу укажу на примере ячейки D3:

Функция СУММ для каждой строки сложит значение из предыдущей ячейки, а так же, результат сравнения класса в ведомости с искомым, оценки в ведомости с искомыми. Два уточнения:

  • В ячейке D2 – текст, и функция СУММ его проигнорирует. В остальных строчках будет браться число из ячейки сверху;
  • ФОРМУЛА И(A3=$L$2;C3=$L$3) вернёт ИСТИНУ (единицу), когда оба условия внутри неё выполнятся, или ЛОЖЬ (ноль), если хотя бы одно из условий не выполнится. Таким образом, когда функция И найдет совпадение класса и оценки, СУММ увеличит порядковый номер на единицу, что нам и требовалось

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

Вывод отобранных строк в таблицу

Теперь осталось вывести на экран выборку учеников класса с нужной оценкой. Для этого сопоставим номера в столбце F с номерами в технической колонке D. Удобнее всего это сделать с помощью комбинации функций ИНДЕКС и ПОИСКПОЗ:

Алгоритм работы на примере ячейки G3:

  1. Функция ПОИСКПОЗ получает числовой индекс строки из ячейки F3 и ищет такое же значение в техническом столбце D. Возвращает либо порядковый номер найденной ячейки в массиве, либо ошибку #Н/Д
  2. Функция ИНДЕКС получает из ведомости класс, находящийся в ячейке, порядковый номер которой мы получили в первом пункте
  3. Далее функция ЕСЛИОШИБКА выводит на экран класс, если он найден, или пустую строку, если ПОИСКПОЗ вернула ошибку

Такой порядок работает и для остальных столбцов: «Фамилия», «Баллы». Теперь если изменить искомый класс или оценку, формулы сами всё пересчитают и переформируют список. Можно скрыть дополнительный столбец, чтобы не мешал.

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

Мы получили полностью автоматизированный интерактивный проект, способный формировать списки «налету», сразу после того, как вы сделаете свой выбор. На этом всё, и я готов отвечать на ваши вопросы!

Выборочные вычисления по одному или нескольким критериям

Постановка задачи

Имеем таблицу по продажам, например, следующего вида:

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

Способ 1. Функция СУММЕСЛИ, когда одно условие

Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в «Копейку», например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:

Жмем ОК и вводим ее аргументы:

  • Диапазон — это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае — это диапазон с фамилиями менеджеров продаж.
  • Критерий — это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак — один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву «П», а заканчивается на «В» — критерий П*В. Строчные и прописные буквы не различаются.
  • Диапазон_суммирования — это те ячейки, значения которых мы хотим сложить, т.е. нашем случае — стоимости заказов.

Способ 2. Функция СУММЕСЛИМН, когда условий много

Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для «Копейки»), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) — в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:

Читать еще:  Вложенные условия в excel

При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3Условие3), и четвертую, и т.д. — при необходимости.

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

Способ 3. Столбец-индикатор

Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в «Копейку» и от Григорьева, то в ячейке этого столбца будет значение 1, иначе — 0. Формула, которую надо ввести в этот столбец очень простая:

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

Способ 4. Волшебная формула массива

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

После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter — тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.

Способ 4. Функция баз данных БДСУММ

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

Несколько условий ЕСЛИ в Excel

В этой статье

Вставка фиксированного значения даты или времени

Вставка обновляемых значений даты или времени

Вставка фиксированного значения даты или времени

  1. Выделите на листе ячейку, в которую требуется вставить текущую дату или время.
  2. Выполните одно из следующих действий.
    • Чтобы вставить текущую дату, нажмите клавиши CTRL + Ж.
    • Чтобы вставить текущее время, нажмите клавиши CTRL+SHIFT+; (точка с запятой).
    • Чтобы вставить текущие дату и время, нажмите клавиши CTRL + Ж, затем ПРОБЕЛ, а после этого — CTRL + SHIFT + Ж.

К началу страницы

Вставка обновляемых значений даты или времени

Для выполнения этой задачи используются функции СЕГОДНЯ и ТДАТА, как показано в приведенном ниже примере.

Пример

Чтобы этот пример проще было понять, скопируйте его на пустой лист.

Копирование примера

  1. Выделите пример, приведенный в этой статье.

Важно : Не выделяйте заголовки строк и столбцов.

Выделение примера из справки

  1. Нажмите клавиши CTRL+C.
  2. Создайте пустые книгу или лист в Excel.
  3. На листе выделите ячейку A1 и нажмите клавиши CTRL+V.

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

  1. Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих эти результаты, нажмите клавиши CTRL+` (апостроф) или на вкладке Формулы в группе Зависимости формул нажмите кнопку Показать формулы.

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

Примечание : Функции СЕГОДНЯ и ТДАТА меняют свои значения только при расчете листа или при выполнении макроса, содержащего эту функцию. Непрерывного их обновления не происходит. В качестве значений даты и времени используются показания системных часов компьютера.

Дополнительные сведения об этих функциях см. в статьях Функция СЕГОДНЯ и Функция ТДАТА.

К началу страницы

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

Несколько условий ЕСЛИ в Excel

Опубликовано 26.07.2015 Автор Ренат Лотфуллин

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

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

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

Ссылка на основную публикацию
ВсеИнструменты
Adblock
detector
×
×