Auditportal.ru

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

Как найти и получить наибольшее значение по нескольким критериям в Excel?

Excel SUMIFS: как суммировать только значения, отвечающие нескольким критериям

Функция SUMIFS расширяет возможности функции SUMIF, позволяя указать от 2 до 127 критериев вместо одного. Например, SUMIF может установить критерии для продавцов, которые продали более 250 единиц товара. SUMIFS может установить критерии для продавцов в данном регионе, которые продали более 250 единиц товара.

Примечание Эти инструкции распространяются на Excel 2019, 2016, 2013, 2010, Excel 2019 для Mac, Excel 2016 для Mac, Excel для Mac 2011, Excel для Office 365 и Excel Online.

Как работает функция SUMIFS

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

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

В пошаговом руководстве SUMIF мы рассмотрим уникальные критерии торговых агентов, которые продали более 250 заказов за один год.

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

Вы можете задать несколько условий, указав дополнительные аргументы Criteria_range и Criteria в SUMIFS.

Ввод данных учебника

Первый шаг к использованию функции SUMIFS в Excel – это ввод данных.

На рисунке 1 показаны места ввода данных на листе Excel.

Функция SUMIFS и критерии поиска (по 275 заказам и торговым представителям из восточного торгового района) находятся в строке 12 в разделе «Данные».

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

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

В обобщенном виде синтаксис функции Excel относится к схеме функции и включает имя функции, круглые скобки и аргументы.

Синтаксис для функции SUMIFS:

В функции можно указать до 127 пар критерий_диапазон/критерия.

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

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

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

Sum_range: данные в этом диапазоне ячеек суммируются, когда все указанные критерии совпадают с соответствующими им аргументами Criteria_range.

Criteria_range: группа ячеек, в которых функция должна искать совпадения с соответствующим аргументом Criteria.

Сравните это значение с данными соответствующей таблицы.

Диапазон_критериев: фактические данные или ссылка на ячейку для данных аргументов.

Запуск функции SUMIFS

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

  1. Щелкните ячейку F12, чтобы сделать ее активной ячейкой; F12 – это место, где вы войдете в функцию SUMIFS.
  2. Откройте вкладку Формулы .
  3. Нажмите Математика и триггеры в группе «Библиотека функций».
  4. Нажмите SUMIFS в списке, чтобы открыть диалоговое окно функции SUMIFS.
Читайте так же:
Как использовать макрос для вставки строки под таблицей в Excel?

В Excel Online нет вкладки “Формулы”. Чтобы использовать SUMIFS в Excel Online, перейдите в раздел Вставить >Функция .

  1. Нажмите F12, чтобы активировать его и войти в функцию SUMIFS.
  2. Нажмите кнопку Вставить функцию. Откроется диалоговое окно Вставить функцию.
  3. Нажмите Математика и триггеры в списке категорий.
  4. Нажмите SUMIFS в списке, чтобы запустить функцию.

Данные, которые мы вводим в пустые строки диалогового окна, становятся аргументами функции SUMIFS.

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

Ввод аргумента Sum_range

Аргумент Sum_range содержит ссылки на ячейки данных, которые мы хотим агрегировать.

В этой инструкции данные для аргумента Total_Range помещаются в столбец Total Sales.

Учебное пособие

  1. Нажмите на строку Sum_range в диалоговом окне.
  2. Выберите ячейки F3 — F9 на листе, чтобы добавить ссылки на эти ячейки в строку Sum_range.

Ввод аргумента Criteria_range1

В этом уроке мы постараемся удовлетворить этим двум критериям в каждом наборе данных:

  1. Торговые агенты в Восточном регионе продаж
  2. Торговые агенты, совершившие менее 275 продаж в этом году.

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

Учебное пособие

  1. Щелкните на строке Criteria_range1 в диалоговом окне.
  2. Выберите ячейки D3 — D9 на листе, чтобы ввести ссылки на эти ячейки в качестве диапазона, в котором будет выполняться поиск.

Ввод аргумента Критерий1

Первый критерий, которому мы хотим соответствовать, – это если данные в диапазоне D3: D9 равны Восток .

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

Учебное пособие

  1. Нажмите на строку Критерии1 в диалоговом окне.
  2. Щелкните на ячейке D12, чтобы ввести ссылку на эту ячейку. Функция ищет в диапазоне, выбранном на предыдущем шаге, данные, соответствующие этим критериям.

Как ссылки на ячейки увеличивают универсальность SUMIFS

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

Поэтому легко найти те же данные для другого региона продаж, просто изменив East на North или West в ячейке D12 после определения восточного региона. Функция автоматически обновится и отобразит новый результат.

Ввод аргумента Criteria_range2

Аргумент Criteria_range2 указывает диапазон ячеек, которые SUMIFS должен искать при попытке удовлетворить второй критерий: торговые агенты, которые продали менее 275 заказов в этом году.

  1. Нажмите на строку Criteria_Range2 в диалоговом окне.
  2. Выберите ячейки с E3 по E9 в рабочем листе, чтобы ввести ссылки на эти ячейки в качестве второго диапазона, в котором функция будет искать.

Ввод критерия2 критерия

Второй стандарт, к которому мы стремимся соответствовать, – это если данные в диапазоне E3: E9 меньше 275 заказов на продажу.

Критерии2 — это то же самое, что и Критерии1, только в диалоговом окне вводится ссылка на ячейку, а не данные.

  1. Нажмите на строку Критерии2 в диалоговом окне.
  2. Щелкните на ячейке E12 , чтобы ввести ссылку на эту ячейку. Функция выполняет поиск данных, соответствующих критериям, в диапазоне, выбранном на предыдущем этапе.
  3. Нажмите OK, чтобы завершить работу функции SUMIFS и закрыть диалоговое окно.
Читайте так же:
Как найти и заменить в верхнем или нижнем колонтитуле в Excel?

Ответ ноль (0) появится в ячейке F12 (ячейка, где мы ввели функцию), потому что мы еще не добавили данные в поля Критерии1 и Критерии2 (C12 и D12). Мы не сможем добавить значение, пока это не будет сделано, поэтому результат будет нулевым.

Добавление критериев поиска и завершение учебника

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

ITGuides.ru

Вопросы и ответы в сфере it технологий и настройке ПК

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

Функция поиска решения пригодится при необходимости определить неизвестную величину

Функция поиска решений очень полезна при определении неизвестной величины.

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

Видео пример поиска решения в Excel

Функция «Подбор параметра»

Подбор параметра в Excel позволяет подобрать какой-то определенный параметр, значение которого неизвестно. Чтобы было понятней, можно привести такой пример. Допустим, есть прямоугольник со сторонами A и B. Известно, что общая площадь этой фигуры составляет 400 квадратных метров, а сторона B — 40 метров. Сторона A неизвестна и, соответственно, нужно ее найти. Для решения такой задачи необходимо заполнить рабочий лист программы теми данными, которые уже известны. Для этого нужно создать таблицу с 2 колонками и 3 строками (диапазон ячеек A1:B3).

Первый столбец содержит названия сторон прямоугольника и букву, обозначающую его площадь (например, A, B и S). А второй столбец должен содержать известные значения:

  • в соседней ячейке для стороны B (ячейка B2) написать — 40 (значение для стороны А остается пустым);
  • а в соседнем поле для площади прямоугольника (поле B3) написать следующую формулу: = B1*B2 (т.е. формула для расчета площади).

Если все было сделано правильно, то в поле B3 должно быть значение 0. Затем надо выделить эту ячейку и выбрать в панели меню пункты: «Сервис — Подбор параметра». В появившемся окне нужно указать то значение, которое должно быть получено в результате, т.е. 400. В строке «Установить в ячейке» будет указано поле «B3»: менять его не нужно, так и должно быть (сюда будет выведен результат). А в строке «Изменяя значение» необходимо выбрать неизвестный параметр, т.е. поле B1. После нажатия кнопки «ОК» программа выдаст результат: сторона А — 10 метров, а в поле общей площади прямоугольника будет указано число 400.

Это была очень простая задача на уровне 3-го класса, но с помощью этой функции можно решать и более сложные задачи. Допустим, вы решили профинансировать себе автомобиль. Нет сомнений, что вы сможете вносить ежемесячный платеж в размере $1000 (но не больше), а банк предлагает вам автокредит под 7,5%. По сути, эта проблема заключается в следующем: «Какую максимальную сумму на автомобиль вы можете взять в кредит при таких условиях?»?»? По существу, программа теперь рассчитывает стоимость автомобиля, исходя из того, что ежемесячный платеж не должен быть больше 1000 долларов. Как видите, этот пример уже сложнее и удобнее, чем вычисление площади прямоугольника.

Читайте так же:
Как найти адрес ячейки, содержащей определенный текст в Excel?

Надстройка «Поиск решения»

Параметры инструмента поиск решения

Параметры инструмента для решения

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

Поиск оптимального решения в Excel

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

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

Примеры использования условий в формулах Excel

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

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

Условная функция ЕСЛИ()

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

операции отношения в логических выражениях

Операции с отношениями в логических выражениях

Простое условие

Для чего используется функция IF()? Посмотрите на диаграмму. Вот простой пример того, как эта функция работает для определения знака a.

операции отношения в логических выражениях

Простая блок-схема с простым состоянием. Определение отрицательных и неотрицательных чисел

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

Формулы и числа пишутся без кавычек; текст в формуле заключен в кавычки.

Если вы хотите, чтобы результатом были данные, полученные в результате вычисления, рассмотрите следующий пример. Давайте увеличим неотрицательное число на 10, а отрицательное оставим без изменений.

Читайте так же:
Как использовать функцию ЕСЛИ с И, ИЛИ и НЕ в Excel?

формула если простое условие в эксель

Блок-схема «Простое условие». Расчет данных

Исходя из диаграммы, если условие выполняется, то число увеличивается на десять, и формула Excel записывает вычисленное выражение A1+10 (выделено зеленым цветом). Тем не менее, само число не меняется, и это выражение состоит только из обозначения самого числа A1 (выделено красным).

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

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

Решение :.

Решение этой проблемы можно увидеть на следующем рисунке. Но давайте внесем ясность в эту иллюстрацию. Основные записи для этой проблемы находятся в колонках A и B. В ячейке A5 показано пороговое значение дохода, при котором изменяется ставка налога. Соответствующие ставки приведены в ячейках B5 и B6. Доход предприятий приведен в диапазоне ячеек B9:B14. Формула для расчета налога записана в ячейке C9: =If(B9>A$5;B9*B$6;B9*B$5). Эту формулу следует скопировать в ячейки ниже (выделены желтым цветом).

прогрессивный налог в эксель

В формуле расчета адреса ячеек записываются как A$5, B$6, B$5. При использовании знака доллара часть адреса, которой он предшествует, становится фиксированной. Запрещается включать в адрес ячейки номер строки.

Составное условие

Составное условие состоит из простого условия в сочетании с логическими операциями И() и ИЛИ().

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

Логическая операция И()

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

Составное условие в Excel

В этом примере функция IF() использует составное условие, связанное логической операцией AND(). Примечание: кандидат Петров не зачислен, хотя сумма его баллов равна проходному баллу.

Почему это произошло? Давайте рассмотрим условие в нашей формуле = IF(AND(E6>=D2;B6>70); «спасен»; «не спасен»). Логическая операция AND() требует, чтобы все условия были выполнены, но выполняется только одно условие. Второе условие B6>70 не выполняется, поэтому составное условие устанавливается на false. И появляется сообщение «не вошел в систему». (помните схему — ветвь «нет»).

Задание:
В торговой фирме перед Новым Годом устроена праздничная распродажа. Рассчитать сумму продаж с учетом скидки, назначаемой в период распродажи.

1) Рассчитайте полную себестоимость продаж. Итого = Стоимость * Количество.

Рассчитайте ставку скидки (в процентах) с помощью функции IF(). Если дата продажи выпадает на праздничный день, применяется скидка, в противном случае скидка не применяется. При задании условий используйте функцию AND().

Рассчитайте сумму продаж с учетом скидки. (Скидка%) = Итого* (1- Скидка%).

Решение:

Читайте так же:
Как импортировать текстовые файлы в Excel в виде отдельных листов с оригинальными именами в Excel?

примел логической функции И()

Для проведения расчетов необходимо ввести следующие формулы:

  • В ячейке Е7: =B7*C7
  • В ячейке F7: =ЕСЛИ(И(D7>=D$4;D7<=E$4);B$4;0)
  • В ячейке G7: =E7*(1-F7)

И скопируйте соответствующие столбцы до строки 16 включительно.

Логическая операция ИЛИ()

Рассмотрим пример с логической операцией OR() в той же таблице. Немного изменим условие задачи. Для поступления не обязательно иметь менее 60 баллов по математике или проходной балл. Рисунок ниже.

Пример Если несколько условий в excel

Несмотря на то, что Сидоров не набрал проходной балл, он зачислен на программу. ЕСЛИ (E7>D2;B7>60); зачислен; не зачислен). Это условие удовлетворяется одним или несколькими условиями, так как здесь используется OR(). Это означает, что первое условие, B7>60, было выполнено. Это породило сообщение о зачислении заявителя.

Задание:
В торговой фирме установлены дни распродаж — последние числа месяца. Рассчитать сумму продаж с учетом скидки, назначаемой в дни распродажи.

1. Оцените общую стоимость продаж. Итого = Стоимость * Количество.

Определите скидку (в процентах) с помощью функции IF(). Если дата продажи совпадает с датами распродаж, назначается скидка, в противном случае скидка не применяется. Задайте условия логически, используя функцию OR().

3 Определите сумму дисконтированных продаж. Дисконтированные продажи = Итого* (1-% скидки)

Решение : .

пример логической функции ИЛИ()

Для расчета введите следующие формулы:

  • В ячейке Е7: =B7*C7
  • В ячейке F7: =ЕСЛИ(ИЛИ(D7=D$4;D7=E$4;D7=F$4);B$4;0)
  • В ячейке G7: =E7*(1-F7)

И копировать по соответствующим графам до строки 15 включительно

Вложенные условия

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

вложенные условия формула если

Когда первая функция IF() не удовлетворяет условию, вторая IF() вложена в нее и вызывается вместо нее. В последних версиях Excel вложенные функции допускаются на глубину до 64 уровней.

Задание:
С целью уменьшения текучести кадров администрация решила выплачивать надбавку за непрерывный стаж работы на своем предприятии. Рассчитайте надбавку за непрерывный стаж работы в соответствии с таблицей Надбавка.

1. Определите стаж работы сотрудников. Стаж = 2018- Год приема на работу.

2. Рассчитайте размер надбавки для сотрудников с помощью вложенных функций If. Пособие (руб.) = Пособие(%)*Зарплата

Решение:

пример вложенной функции ЕСЛИ()

Для расчета необходимы следующие формулы:

  • В ячейке D9: =2018-B9
  • В ячейке E9: =ЕСЛИ(D9>=B$6;C9*C$6;ЕСЛИ(D9>=B$5;C9*C$5;0))

И скопируйте соответствующие столбцы до строки 19.

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

Постскриптум: Удивительные факты

Дорогой читатель! Вы посмотрели статью до конца.Получили вы ответ на свой вопрос? Напишите в комментариях пару слов. Если ответа не нашли, укажите что искали или откройте содержание блога.

ОЧЕНЬ ВАЖНО! Оцени лайком или дизлайком статью!Блог твой компьютер лайкБлог твой компьютер дизлайк

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector