Сумма по условию
Формула суммы по условию критерия выборки значений в Excel
Простые логические функции такие как ЕСЛИ обычно предназначены для работы с одним столбцом или одной ячейкой. Excel также предлагает несколько других логических функций служащих для агрегирования данных. Например, функция СУММЕСЛИ для выборочного суммирования диапазона значений по условию.
Примеры формулы для суммы диапазонов с условием отбора в Excel
Ниже на рисунке представлен в таблице список счетов вместе с состоянием по каждому счету в виде положительных или отрицательных чисел. Допустим нам необходимо посчитать сумму всех отрицательных чисел для расчета суммарного расхода по движению финансовых средств. Этот результат будет позже сравниваться вместе с сумой положительных чисел с целью верификации и вывода балансового сальдо. Узнаем одинаковые ли суммы доходов и расходов – сойдется ли у нас дебит с кредитом. Для суммирования числовых значений по условию в Excel применяется логическая функция =СУММЕСЛИ():
Функция СУММЕСЛИ анализирует каждое значение ячейки в диапазоне B2:B12 и проверяет соответствует ли оно заданному условию (указанному во втором аргументе функции). Если значение меньше чем 0, тогда условие выполнено и данное число учитывается в общей итоговой сумме. Числовые значения больше или равно нулю игнорируются функцией. Проигнорированы также текстовые значения и пустые ячейки.
В приведенном примере сначала проверяется значения ячейки B2 и так как оно больше чем 0 – будет проигнорировано. Далее проверяется ячейка B3. В ней числовое значение меньше нуля, значит условие выполнено, поэтому оно добавляется к общей сумме. Данный процесс повторяется для каждой ячейки. В результате его выполнения суммированы значения ячеек B3, B6, B7, B8 и B10, а остальные ячейки не учитываются в итоговой сумме.
Обратите внимание что ниже результата суммирования отрицательных чисел находится формула суммирования положительных чисел. Единственное отличие между ними — это обратный оператор сравнения во втором аргументе где указывается условие для суммирования – вместо строки "<0" (меньше чем ноль) используется строка ">0" (больше чем ноль). Теперь мы можем убедиться в том, что дебет с кредитом сходится балансовое сальдо будет равно нулю если сложить арифметически в ячейке B16 формулой =B15+B14.
Пример логического выражения в формуле для суммы с условием
Другой пример, когда нам нужно отдельно суммировать цены на группы товаров стоимости до 1000 и отдельно со стоимостью больше 1000. В таком случае одного оператора сравнения нам недостаточно (<1000) придется использовать знак «меньше или равно» (<=1000) иначе цены со значением ровно 1000 не будут включены в расчеты. Более того в таком случае мы уже не можем использовать в другой формуле «больше или равно» (>=1000) иначе мы просуммируем сумму ровно в 1000 – 2 раза, что приведет к ошибочным итоговым результатам:
Это очень распространенная ошибка пользователей Excel при работе с логическими функциями!
Внимание! В первом примере нулевые значения нам необязательно было учитывать, так как на балансовое сальдо это никак не повлияло бы, но во втором случаи нужно составлять критерий условия суммирования иначе, чтобы не допустить ошибочных просчетов.
Второй аргумент функции СУММЕСЛИ, то есть условие, которое должно быть выполнено, записывается между двойными кавычками. В данном примере используется символ сравнения – «меньше» (<). По синтаксису функции необходимо представлять такие записи как строка, то есть в двойных кавычках. Иначе Excel выдаст предупреждение об ошибке в формуле.
Синтаксис формулы суммирования по условию в Excel
В синтаксисе функции СУММЕСЛИ присутствует необязательный для заполнения третий аргумент – диапазон суммирования. В предыдущем примере условие проверялось по тому же диапазону, который нужно было суммировать. Благодаря третьему аргументу появляется возможность проверять условия по одному диапазону, а суммировать другой.
Ниже на рисунке представлена таблица отчета продаж по клиентам за месяц. Напротив, каждого клиента указана сумма, на которую совершил покупки клиент на протяжении месяца. Некоторые клиенты за месяц совершили по несколько покупок, поэтому их наименование дублируется в списке.
Необходимо узнать на какую общую сумму купил товара «Клиент3» в данном месяце. Здесь весьма уместно использовать формулу с заполнением третьего аргумента функции СУММЕСЛИ:
Как видно на всех выше приведенных примерах во втором аргументе указывается строка текста с условием, а не логическое выражение как в функции ЕСЛИ. На практике использования функции СУММЕСЛИ этот второй логический аргумент с условием может содержать:
- сроки с текстовым значением;
- числовые значения;
- логические выражения;
- формулы с функциями;
- ссылки на ячейки.
Далее рассмотрим больше примеров составления условий для второго логического аргумента функции СУММЕСЛИ более детально.
Создание критериев условий для функции СУММЕСЛИ
Второй аргумент функции называется «Критерий». Данный логический аргумент используется и в других подобных логических функциях: СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИ и СРЗНАЧЕСЛИМН. В каждом случаи аргумент заполняется согласно одних и тех же правил составления логических условий.
Другими словами, для всех этих функций второй аргумент с критерием условий является логическим выражением возвращающим результат ИСТИНА или ЛОЖЬ. Это значит, что выражение должно содержать оператор сравнения, например: больше (>) меньше (<) равно (=) неравно (<>), больше или равно (>=), меньше или равно (<=). За исключением можно не указывать оператор равно (=), если должно быть проверено точное совпадение значений.
Создание сложных критериев условий может быть запутанным. Однако если придерживаться нескольких простых правил описанных в ниже приведенной таблице, не будет возникать никаких проблем.
Таблица правил составления критериев условий:
| Чтобы создать условие | Примените правило | Пример |
| Значение равно заданному числу или ячейке с данным адресом. | Не используйте знак равенства и двойных кавычек. | =СУММЕСЛИ(B1:B10;3) |
| Значение равно текстовой строке. | Не используйте знак равенства, но используйте двойные кавычки по краям. | =СУММЕСЛИ(B1:B10;"Клиент5") |
| Значение отличается от заданного числа. | Поместите оператор и число в двойные кавычки. | =СУММЕСЛИ(B1:B10;">=50") |
| Значение отличается от текстовой строки. | Поместите оператор и число в двойные кавычки. | =СУММЕСЛИ(B1:B10;"<>выплата") |
| Значение отличается от ячейки по указанному адресу или от результата вычисления формулы. | Поместите оператор сравнения в двойные кавычки и соедините его символом амперсант (&) вместе со ссылкой на ячейку или с формулой. | =СУММЕСЛИ(A1:A10;"<"&C1) или =СУММЕСЛИ(B1:B10;"<>"&СЕГОДНЯ()) |
| Значение содержит фрагмент строки | Используйте операторы многозначных символов и поместите их в двойные кавычки | =СУММЕСЛИ(A1:A10;"*кг*";B1:B10) |
Во втором аргументе критериев условий можно использовать разные функции и формулы. Ниже на рисунке изображен список дат и присвоенных им значений.
Важно отметить что сегодня на момент написания статьи дата – «03.11.2018». Чтобы суммировать числовые значения только по сегодняшней дате используйте формулу:
Чтобы суммировать только значения от сегодняшнего дня включительно и до конца периода времени воспользуйтесь оператором «больше или равно» (>=) вместе с соответственной функцией =СЕГОДНЯ(). Формула c операторам (>=):
Суммирование по неточному совпадению в условии критерия отбора
Во втором логическом аргументе критериев условий функции СУММЕСЛИ можно применять многозначные символы – (?)и(*) для составления относительных неточных запросов. Знак вопроса (?) – следует читать как любой символ, а звездочка (*) – это строка из любого количества любых символов или пустая строка. Например, нам необходимо просуммировать только защитные краски-лаки с кодом 3 английские буквы в начале наименования:
Суммируются все значения ячеек в диапазоне B2:B16 в соответствии со значениями в ячейках диапазона A2:A16, в которых после третьего символа фрагмент строки «-защита».
Таким образом удалось суммировать только определенную группу товаров в общем списке отчета по складу. Данный фрагмент наименования товара должен встречаться в определенном месте – 3 символа от начала строки. Нет необходимости использовать сложные формулы с функцией =ЛЕВСИМВ() и т.д. Достаточно лишь воспользоваться операторами многозначных символов чтобы сформулировать простой и лаконичный запрос к базе данных с минимальными нагрузками на системные ресурсы.
Функция СУММЕСЛИ - Служба поддержки Майкрософт
Функция СУММЕСЛИ используется, если необходимо просуммировать значения диапазон, соответствующие указанному критерию. Предположим, например, что в столбце с числами необходимо просуммировать только значения, превышающие 5. Для этого можно использовать следующую формулу: =СУММЕСЛИ(B2:B25;"> 5")
Это видео — часть учебного курса Сложение чисел в Excel.
Советы:
-
При необходимости условия можно применить к одному диапазону, а просуммировать соответствующие значения из другого диапазона.
Например, формула =СУММЕСЛИ(B2:B5; "Иван"; C2:C5) суммирует только те значения из диапазона C2:C5, для которых соответствующие значения из диапазона B2:B5 равны "Иван". -
Если необходимо выполнить суммирование ячеек в соответствии с несколькими условиями, используйте функцию СУММЕСЛИМН.
Важно: Функция СУММЕСЛИ возвращает неверные результаты при использовании для сопоставления строк длиной более 255 символов или строкового #VALUE!.
Синтаксис
СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])
Аргументы функции СУММЕСЛИ описаны ниже.
-
Диапазон — обязательный аргумент. Диапазон ячеек, оцениваемых на соответствие условиям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки на числа.
Пустые и текстовые значения игнорируются. Выбранный диапазон может содержать даты в стандартном формате Excel (см. примеры ниже). -
Условие .Обязательный аргумент. Условие в форме числа, выражения, ссылки на ячейку, текста или функции, определяющее, какие ячейки необходимо суммировать. Можно включить подстановочные знаки: вопросительный знак (?) для сопоставления с любым одним символом, звездочка (*) для сопоставления любой последовательности символов. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак "тильда" (~).
Например, критерии можно выразить как 32, ">32", B5, "3?", "apple*", "*~?" или TODAY().
Важно: Все текстовые условия и условия с логическими и математическими знаками необходимо заключать в двойные кавычки (").
Если условием является число, использовать кавычки не требуется. -
Диапазон_суммирования .Необязательный аргумент. Ячейки, значения из которых суммируются, если они отличаются от ячеек, указанных в качестве диапазона. Если аргумент диапазон_суммирования опущен, Excel суммирует ячейки, указанные в аргументе диапазон (те же ячейки, к которым применяется условие).
Sum_range должны иметь тот же размер и форму, что и диапазон. Если это не так, производительность может снизиться, и формула суммирует диапазон ячеек, который начинается с первой ячейки в sum_range но имеет те же размеры, что и диапазон. Например:
диапазон
Диапазон_суммирования.
Фактические суммированные ячейки
A1:A5
B1:B5
B1:B5
A1:A5
B1:K5
B1:B5
Примеры
Пример 1
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel.
Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу Enter. При необходимости измените ширину столбцов, чтобы видеть все данные.
|
Стоимость имущества |
Комиссионные |
Данные |
|---|---|---|
|
1 000 000 ₽ |
70 000 ₽ |
2 500 000 ₽ |
|
2 000 000 ₽ |
140 000 ₽ |
|
|
3 000 000 ₽ |
210 000 ₽ |
|
|
4 000 000 ₽ |
280 000 ₽ |
|
|
Формула |
Описание |
Результат |
|
=СУММЕСЛИ(A2:A5;">160000";B2:B5) |
Сумма комиссионных за имущество стоимостью больше 1 600 000 ₽. |
630 000 ₽ |
|
=СУММЕСЛИ(A2:A5; ">160000") |
Сумма по имуществу стоимостью больше 1 600 000 ₽. |
9 000 000 ₽ |
|
=СУММЕСЛИ(A2:A5;300000;B2:B5) |
Сумма комиссионных за имущество стоимостью 3 000 000 ₽. |
210 000 ₽ |
|
=СУММЕСЛИ(A2:A5;">" &C2;B2:B5) |
Сумма комиссионных за имущество, стоимость которого превышает значение в ячейке C2. |
490 000 ₽ |
Пример 2
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу Enter. Кроме того, вы можете настроить ширину столбцов в соответствии с содержащимися в них данными.
|
Категория |
Продукты |
Объем продаж |
|---|---|---|
|
Овощи |
Помидоры |
23 000 ₽ |
|
Овощи |
Сельдерей |
55 000 ₽ |
|
Фрукты |
Апельсины |
8 000 ₽ |
|
Масло |
4 000 ₽ |
|
|
Овощи |
Морковь |
42 000 ₽ |
|
Фрукты |
Яблоки |
12 000 ₽ |
|
Формула |
Описание |
Результат |
|
=СУММЕСЛИ(A2:A7;"Фрукты";C2:C7) |
Объем продаж всех продуктов категории "Фрукты". |
20 000 ₽ |
|
=СУММЕСЛИ(A2:A7;"Овощи";C2:C7) |
Объем продаж всех продуктов категории "Овощи". |
120 000 ₽ |
|
=СУММЕСЛИ(B2:B7;"*ы";C2:C7) |
Объем продаж всех продуктов, названия которых заканчиваются на "ы" ("Помидоры" и "Апельсины"). |
43 000 ₽ |
|
=СУММЕСЛИ(A2:A7;"";C2:C7) |
Объем продаж всех продуктов, категория для которых не указана. |
4 000 ₽ |
К началу страницы
Дополнительные сведения
См. также
Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.
Функция СУММЕСЛИМН суммирует все аргументы, которые соответствуют нескольким условиям
Функция СУММКВ суммирует несколько значений, предварительно возведя каждое из них в квадрат
Функция СЧЁТЕСЛИ подсчитывает только те значения, которые соответствуют одному условию
Функция СЧЁТЕСЛИМН подсчитывает только те значения, которые соответствуют нескольким условиям
Функция ЕСЛИМН (Microsoft 365, Excel 2016 и более поздние версии)
Полные сведения о формулах в Excel
Рекомендации, позволяющие избежать появления неработающих формул
Поиск ошибок в формулах
Математические и тригонометрические функции
Функции Excel (по алфавиту)
Функции Excel (по категориям)
Информация об условном платеже | CMS
В соответствии с законом Medicare о вторичных плательщиках (42 U.
S.C. § 1395y(b)), Medicare не оплачивает товары или услуги в той степени, в которой оплата была произведена или может обоснованно ожидаться в результате отсутствия вины или ответственности. страховщика или через организацию по компенсации работникам. Medicare может произвести условный платеж, если есть доказательства того, что основной план не производит своевременных выплат при условии возмещения расходов, когда основной план действительно выплачивает. Центр координации и возмещения льгот (BCRC) отвечает за возмещение условных платежей при урегулировании, судебном решении, присуждении или другом платеже, произведенном получателю Medicare. Когда BCRC получит информацию о потенциальной ситуации восстановления, он выявит затронутые претензии и начнет действия по восстановлению. Бенефициары и их адвокаты должны признавать обязательство по возмещению Medicare во время любых переговоров об урегулировании.
Если Medicare добивается возмещения ущерба непосредственно от страховщика/органа, занимающегося компенсацией работникам, бенефициар и его адвокат или другой представитель получат копию корреспонденции о возмещении, отправленную страховщику/органу, компенсирующему работникам.
Получателю не нужно предпринимать никаких действий в отношении этой корреспонденции. Однако, если программа Medicare добивается возмещения ущерба от бенефициара, BCRC направит бенефициару корреспонденцию о возмещении ущерба.
Письмо об условном платеже (CPL)
CPL предоставляет информацию о предметах или услугах, которые Medicare оплатила условно, и которые были определены BCRC как относящиеся к ожидающей рассмотрения претензии. В случаях, когда Medicare добивается возмещения у бенефициара, CPL автоматически отправляется бенефициару в течение 65 дней после выдачи письма о правах и обязанностях (копию письма о правах и обязанностях можно получить, перейдя по ссылке Процесс восстановления Medicare ). . Все организации, у которых есть подтвержденное свидетельство о представлении или согласие на выдачу разрешений, хранящиеся в BCRC по делу, получат копию CPL. См. Доказательство представления и согласие на выпуск 9.0009 страницу для получения дополнительной информации по этим темам.
CPL включает в себя Сводную форму платежа, в которой перечислены все товары или услуги, которые BCRC идентифицировал как относящиеся к ожидающей рассмотрения претензии. В письме указана промежуточная общая сумма условного платежа и разъясняется, как оспорить любые несвязанные претензии. Общая сумма условного платежа считается промежуточной, поскольку Medicare может производить дополнительные платежи, пока претензия бенефициара находится на рассмотрении.
Вы можете получить текущую сумму условного платежа и копии CPL в BCRC или на портале Medicare Secondary Payer Recovery Portal (MSPRP). Чтобы получить информацию об условном платеже от BCRC, позвоните по номеру 1-855-79.8-2627. Чтобы получить информацию об условных платежах из MSPRP, см. раздел «Портал восстановления вторичного плательщика Medicare (MSPRP)» ниже. Если происходит урегулирование, судебное решение, присуждение или другой платеж, об этом следует сообщить в BCRC как можно скорее, чтобы BCRC мог определить любые новые связанные претензии, которые были выплачены с момента последней выдачи CPL.
Для получения дополнительной информации о CPL см. документ под названием Письма об условном платеже (бенефициар) . Доступ к этому документу можно получить, щелкнув Процесс восстановления Medicare ссылка.
Контактную информацию для BCRC можно получить, нажав ссылку «Контакты».
Уведомление об условном платеже (CPN)
CPN выдается бенефициару вместо CPL, когда расчет, судебное решение, присуждение или другой платеж уже осуществлен. CPN предоставляет информацию об условном платеже и советует, какие действия необходимо предпринять, поскольку расчет, судебное решение, присуждение или другой платеж уже произошел. После выдачи CPN получателю предоставляется 30 дней для ответа. Если CPN получен, любые из перечисленных ниже элементов должны быть перенаправлены в BCRC, если они не были отправлены ранее:
- Документация, подтверждающая представительство.
- Подтверждение любых товаров и/или услуг, не имеющих отношения к делу, если применимо.
- Вся расчетная документация, если вы предоставляете доказательства каких-либо предметов и/или услуг, не связанных с делом.
- Расходы на закупку и сборы, оплачиваемые бенефициаром.
- Документация по любым дополнительным или ожидающим расчетам, судебным решениям, вознаграждениям или другим платежам, связанным с тем же инцидентом.
При получении ответа в течение 30 календарных дней корреспонденция будет рассмотрена, и будет выдано письмо-требование. Если ответ не будет получен в течение 30 календарных дней, будет автоматически отправлено письмо-требование с требованием возмещения всех условных платежей, связанных с делом, без пропорционального уменьшения сборов или затрат.
Для получения дополнительной информации о CPN см. документ под названием Уведомление об условном платеже (бенефициар) , доступ к которому можно получить, перейдя по ссылке «Процесс восстановления» Medicare.
Оспаривание претензий по CPL или CPN
Если бенефициар, его или ее адвокат или другой представитель считает, что какие-либо претензии, включенные в CPL или CPN, должны быть исключены из суммы условного платежа Medicare, документация, подтверждающая эту позицию, должна быть отправлена в BCRC.
. Предоставленная документация должна установить, что требования не связаны с тем, что было заявлено или было выпущено бенефициаром. Этот процесс может осуществляться по почте, факсу или через MSPRP. Дополнительную информацию см. в разделе «Портал восстановления вторичного плательщика Medicare (MSPRP)» ниже. BCRC скорректирует сумму условного платежа, чтобы учесть любые претензии, которые, по его мнению, не связаны с тем, что было заявлено или раскрыто. По завершении процесса рассмотрения спора BCRC уведомит все уполномоченные стороны о разрешении спора.
Вариант расчета требования
Если бенефициар урегулирует дело об ответственности, он или она может иметь право на получение суммы требования Medicare до урегулирования или на выплату Medicare фиксированного процента от общей суммы урегулирования. Нажмите на ссылку Параметры расчета спроса , чтобы определить, соответствует ли дело бенефициара требуемым правилам.
Портал Medicare Secondary Payer Recovery Portal (MSPRP)
MSPRP — это онлайн-инструмент самообслуживания, который может использоваться адвокатами для сообщения о случаях и управления делами, о которых было сообщено в BCRC.
MSPRP позволяет пользователям получать обновленные суммы условного платежа, запрашивать копию текущего письма об условном платеже или осуществлять электронный платеж. Нажмите на ссылку MSPRP, чтобы получить дополнительные сведения.
Создание условных формул — Служба поддержки Майкрософт
Проверка истинности или ложности условий и выполнение логических сравнений между выражениями характерны для многих задач. Вы можете использовать функции И , ИЛИ , НЕ и ЕСЛИ для создания условных формул.
Например, функция IF использует следующие аргументы.
Формула, использующая функцию ЕСЛИ
logical_test: условие, которое вы хотите проверить.
value_if_true: возвращаемое значение, если условие истинно.
value_if_false: возвращаемое значение, если условие имеет значение False.
Дополнительные сведения о создании формул см.
в разделе Создание или удаление формулы.
Что ты хочешь сделать?
-
Создайте условную формулу, результатом которой является логическое значение (ИСТИНА или ЛОЖЬ).
-
Создайте условную формулу, которая приводит к другому вычислению или значениям, отличным от ИСТИНА или ЛОЖЬ
Создать условную формулу, результатом которой является логическое значение (ИСТИНА или ЛОЖЬ)
Для выполнения этой задачи используйте функции и операторы И , ИЛИ и НЕ , как показано в следующем примере.
Пример
Пример будет легче понять, если вы скопируете его на пустой лист.
Как скопировать пример?
-
Выберите пример из этой статьи.
Выбор примера из справки
-
Нажмите CTRL+C.
-
В Excel создайте пустую книгу или лист.
-
На листе выберите ячейку A1 и нажмите CTRL+V.
Важно: Чтобы пример работал правильно, его необходимо вставить в ячейку A1 рабочего листа.
-
Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих результаты, нажмите CTRL+` (большое ударение) или на вкладке Формулы в группе Аудит формул щелкните значок Кнопка Показать формулы .
После того, как вы скопируете пример на пустой лист, вы можете адаптировать его под свои нужды.
|
(ЛОЖЬ) 
(ЛОЖЬ) Дополнительные сведения об использовании этих функций см. в разделе Функция И, Функция ИЛИ и Функция НЕ.
Верх страницы
Создать условную формулу, которая приводит к другому вычислению или значениям, отличным от ИСТИНА или ЛОЖЬ
Для выполнения этой задачи используйте функции и операторы IF , И и ИЛИ , как показано в следующем примере.
Пример
Пример будет легче понять, если вы скопируете его на пустой лист.
Как скопировать пример?
-
Выберите пример из этой статьи.
Важно: Не выделяйте заголовки строк или столбцов.
Выбор примера из справки
-
Нажмите CTRL+C.
-
В Excel создайте пустую книгу или лист.
-
На листе выберите ячейку A1 и нажмите CTRL+V.
Важно: Чтобы пример работал правильно, его необходимо вставить в ячейку A1 рабочего листа.
-
Чтобы переключиться между просмотром результатов и просмотром формул, возвращающих результаты, нажмите CTRL+` (большое ударение) или на вкладке Формулы в группе Аудит формул нажмите кнопку Показать формулы .
После того, как вы скопируете пример на пустой лист, вы можете адаптировать его под свои нужды.
|
В противном случае верните «Не в порядке». (ОК) 
В противном случае верните «Не в порядке». (Не в порядке) 
