Функция СЧЕТЕСЛИ входит в группу статистических функций. Позволяет найти число ячеек по определенному критерию. Работает с числовыми и текстовыми значениями, датами.
Сначала рассмотрим аргументы функции:
- Диапазон – группа значений для анализа и подсчета (обязательный).
- Критерий – условие, по которому нужно подсчитать ячейки (обязательный).
В диапазоне ячеек могут находиться текстовые, числовые значения, даты, массивы, ссылки на числа. Пустые ячейки функция игнорирует.
В качестве критерия может быть ссылка, число, текстовая строка, выражение. Функция СЧЕТЕСЛИ работает только с одним условием (по умолчанию). Но можно ее «заставить» проанализировать 2 критерия одновременно.
Рекомендации для правильной работы функции:
- Если функция СЧЕТЕСЛИ ссылается на диапазон в другой книге, то необходимо, чтобы эта книга была открыта.
- Аргумент «Критерий» нужно заключать в кавычки (кроме ссылок).
- Функция не учитывает регистр текстовых значений.
- При формулировании условия подсчета можно использовать подстановочные знаки. «?» — любой символ. «*» — любая последовательность символов. Чтобы формула искала непосредственно эти знаки, ставим перед ними знак тильды (~).
- Для нормального функционирования формулы в ячейках с текстовыми значениями не должно пробелов или непечатаемых знаков.
Посчитаем числовые значения в одном диапазоне. Условие подсчета – один критерий.
У нас есть такая таблица:
Посчитаем количество ячеек с числами больше 100. Формула: =СЧЁТЕСЛИ(B1:B11;»>100″). Диапазон – В1:В11. Критерий подсчета – «>100». Результат:
Если условие подсчета внести в отдельную ячейку, можно в качестве критерия использовать ссылку:
Посчитаем текстовые значения в одном диапазоне. Условие поиска – один критерий.
Формула: =СЧЁТЕСЛИ(A1:A11;»табуреты»). Или:
- Во втором случае в качестве критерия использовали ссылку на ячейку.
- Формула с применением знака подстановки: =СЧЁТЕСЛИ(A1:A11;»таб*»).
- Для расчета количества значений, оканчивающихся на «и», в которых содержится любое число знаков: =СЧЁТЕСЛИ(A1:A11;»*и»). Получаем:
Формула посчитала «кровати» и «банкетки».
Используем в функции СЧЕТЕСЛИ условие поиска «не равно».
Формула: =СЧЁТЕСЛИ(A1:A11;»»&»стулья»). Оператор «» означает «не равно». Знак амперсанда (&) объединяет данный оператор и значение «стулья».
При применении ссылки формула будет выглядеть так:
Часто требуется выполнять функцию СЧЕТЕСЛИ в Excel по двум критериям. Таким способом можно существенно расширить ее возможности. Рассмотрим специальные случаи применения СЧЕТЕСЛИ в Excel и примеры с двумя условиями.
- Посчитаем, сколько ячеек содержат текст «столы» и «стулья». Формула: =СЧЁТЕСЛИ(A1:A11;»столы»)+СЧЁТЕСЛИ(A1:A11;»стулья»). Для указания нескольких условий используется несколько выражений СЧЕТЕСЛИ. Они объединены между собой оператором «+».
- Условия – ссылки на ячейки. Формула: =СЧЁТЕСЛИ(A1:A11;A1)+СЧЁТЕСЛИ(A1:A11;A2). Текст «столы» функция ищет в ячейке А1. Текст «стулья» — на базе критерия в ячейке А2.
- Посчитаем число ячеек в диапазоне В1:В11 со значением большим или равным 100 и меньшим или равным 200. Формула: =СЧЁТЕСЛИ(B1:B11;»>=100″)-СЧЁТЕСЛИ(B1:B11;»>200″).
- Применим в формуле СЧЕТЕСЛИ несколько диапазонов. Это возможно, если диапазоны являются смежными. Формула: =СЧЁТЕСЛИ(A1:B11;»>=100″)-СЧЁТЕСЛИ(A1:B11;»>200″). Ищет значения по двум критериям сразу в двух столбцах. Если диапазоны несмежные, то применяется функция СЧЕТЕСЛИМН.
- Когда в качестве критерия указывается ссылка на диапазон ячеек с условиями, функция возвращает массив. Для ввода формулы нужно выделить такое количество ячеек, как в диапазоне с критериями. После введения аргументов нажать одновременно сочетание клавиш Shift + Ctrl + Enter. Excel распознает формулу массива.
СЧЕТЕСЛИ с двумя условиями в Excel очень часто используется для автоматизированной и эффективной работы с данными. Поэтому продвинутому пользователю настоятельно рекомендуется внимательно изучить все приведенные выше примеры.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ
Посчитаем количество реализованных товаров по группам.
- Сначала отсортируем таблицу так, чтобы одинаковые значения оказались рядом.
- Первый аргумент формулы «ПРОМЕЖУТОЧНЫЕ.ИТОГИ» — «Номер функции». Это числа от 1 до 11, указывающие статистическую функцию для расчета промежуточного результата. Подсчет количества ячеек осуществляется под цифрой «2» (функция «СЧЕТ»).
Скачать примеры функции СЧЕТЕСЛИ в Excel
Формула нашла количество значений для группы «Стулья». При большом числе строк (больше тысячи) подобное сочетание функций может оказаться полезным.
Источник: https://exceltable.com/funkcii-excel/funkciya-schetesli-primery
Използване на критерии в Microsoft Excel
Microsoft Excel не е само редактор на електронни таблици, но и мощно приложение за различни изчисления. Не на последно място, тази функция се дължи на вградените функции. С помощта на определени функции (оператори) е възможно да се определят дори и условията за изчисление, които се наричат критерии. Нека научим по-подробно как да ги използваме, когато работите в Excel.
Прилагане на критериите
Критериите са условията, при които програмата изпълнява определени действия. Те се използват в редица вградени функции. В тяхното име най-често присъства изразът «IF» .
За тази група оператори, на първо място, е необходимо да се включат СТРАНИ , СМЕТКИ , SUM , SUMMES . В допълнение към вградените оператори критериите в Excel се използват и за условно форматиране.
Нека разгледаме тяхното приложение при работа с различни инструменти на даден процесор за таблици в повече подробности.
COUNTIF
Основната задача на оператора COUNTIF , която се отнася до статистическа група, е броят на клетките, заети от различни стойности, които отговарят на определено предварително определено условие. Синтаксисът му е както следва:
=СЧЁТЕСЛИ(диапазон;критерий)
Както можете да видите, този оператор има два аргумента. «Обхват» е адресът на масив от елементи в листа, в който да се брои.
«Критерий» е аргумент, който определя условието, че трябва да съдържа клетките на определената област, които да бъдат включени в броя.
Като параметър можете да използвате цифров израз, текст или позоваване на клетката, в която се съдържа критерият.
В този случай можете да използвате следните знаци, за да посочите критерия: «» ( «more» ), «=» ( «equal» ), «» . Например, ако посочите израза » 15000″ .
След като направите всички описани по-горе манипулации, кликнете върху бутона «OK» .
Урокът: Съветник за функциите в програмата Excel
Следващата функция, която работи с критериите, е COUNTRY . Той също така се позовава на статистическата група от оператори. Задачата на COUNTERMAN е преброяването на клетките в посочения масив, които отговарят на определен набор от условия. Това е фактът, че не можете да посочите нито един, а няколко параметъра, и да различава този оператор от предишния. Синтаксисът е:
=СЧЁТЕСЛИМН(диапазон_условия1;условие1;диапазон_условия2;условие2;…)
«Обхватът на условия» е идентичен на първия аргумент от предходното изявление. Това означава, че става дума за зоната, в която клетките, които отговарят на определените условия, ще бъдат преброени. Този оператор ви позволява да посочите няколко такива зони наведнъж.
«Условие» е критерий, който определя кои елементи от съответния масив от данни ще се включат в изчислението и кои от тях няма. Всяка дадена област за данни трябва да бъде определена отделно, дори и да съответства. Необходимо е всички маси, използвани като условни зони, да имат еднакъв брой редове и колони.
За да определите няколко параметъра на една и съща зона за данни, например, за да преброите броя на клетките, в които стойностите са по-големи от определен брой, но по-малки от друг номер, трябва да посочите същия масив няколко пъти, както аргумента «Обхват на условието» , Въпреки това, трябва да се използват различни критерии като съответните аргументи «Състояние» .
На пример за една и съща маса с седмични приходи от магазини ще видим как работи. Необходимо е да знаем броя на дните от седмицата, когато доходите във всички посочени обекти достигнаха установения за тях процент. Тарифите на приходите са, както следва:
- Магазин 1 — 14000 рубли;
- Магазин 2 — 15 000 рубли;
- Магазин 3 — 24000 рубли;
- Магазин 4 — 11 000 рубли;
- Магазин 5 — 32000 рубли.
- За да изпълните горната задача, маркирайте курсора с елемента на работния лист, към който ще се изведе резултата от обработката на данните за COUNTRY . Кликваме върху иконата «Вмъкване на функция» .
- Преминавайки към помощника за функциите , върнете се към «статистическия» блок. В списъка е необходимо да се намери името на COUNTRY FORM и да се направи изборът. След извършване на това действие, трябва да кликнете върху бутона «OK» .
- След изпълнението на горепосочения алгоритъм от действия се отваря прозорецът на параметрите COUNTLINES .
В полето «Обхват на условие1» въведете адреса на реда, в който се намират данните за приходите на магазин 1 на седмица. За да направите това, поставете курсора в полето и изберете съответния ред в таблицата. Координатите се показват в прозореца.
Като се има предвид, че за дневния размер на Store 1 е 14 000 рубли, тогава в полето «Условие 1» въвеждаме израза «> 14000» .
В полето «Обхват на условията2 (3,4,5)» трябва да въведете координатите на линиите със седмични постъпления в съответствие със Съхранение 2, Съхранение 3, Съхранение 4 и Съхранение 5. Действието се изпълнява по същия алгоритъм като първия аргумент от тази група.
В полетата «Състояние2» , «Състояние3» , «Състояние4» и «Състояние5» влизаме съответно стойностите «> 15000 «, «> 24000» , «> 11000» и «> 32000» . Както може да предположите, тези стойности съответстват на интервала на приходите над нормата за съответния магазин.
След въвеждане на всички необходими данни (общо 10 полета) кликнете върху бутона «OK» .
- Програмата изчислява и показва резултата на екрана. Както можете да видите, то е равно на числото 3. Това означава, че в три дни от анализираната седмица приходите във всички пазари надвишават установения за тях процент.
Сега нека да променим проблема донякъде. Трябва да изчислим броя дни, в които магазин 1 получи приходи над 14 000 рубли, но по-малко от 17 000 рубли.
- Поставяме курсора в елемента, в който ще се извежда резултатът от изчисленията. Кликваме върху иконата «Вмъкване на функция» над работната площ на листа.
- Тъй като наскоро приложихме формулата COUNCILS , сега не е необходимо да отидете в групата на «статистическите» магьосници на функциите . Името на този оператор може да бъде намерено в категорията «10 наскоро използвана» . Изберете го и кликнете върху бутона «OK» .
- Отваря прозореца на аргументите на оператора СЧиТЕСЛИМН, който вече ни е познат. Поставяме курсора в полето «Обхват на състояние1» и след като сте натиснали левия бутон на мишката, изберете всички клетки, които съдържат приходите от дните на Store 1. Те се намират в реда, който се нарича «Магазин 1» . След това координатите на посочената област ще бъдат отразени в прозореца.
След това поставете курсора в полето «Състояние1» . Тук трябва да посочим долната граница на стойностите в клетките, които ще участват в изчислението. Посочете израза «> 14000» .
В полето «Обхват на условията2» въвеждаме същия адрес по същия начин, както е въведен в полето «Обхват на състояние1» , т.е. отново въвеждаме координатите на клетките с стойностите на приходите в първата точка на продажба.
В полето «Условие2″ посочваме горната граница на селекцията: » 10.03.2017″ .
В полето «Обхват на сумиране» трябва да посочите област, чиито стойности, съответстващи на зададените критерии, ще бъдат сумирани. В нашия случай това са стойностите на приходите на ред «Shop1» . Изберете подходящия масив от елементи на листа.
След като въведете всички зададени данни, кликнете върху бутона «OK» .
- След това резултатът от обработката на данните чрез функцията SUMMER ще се покаже в предварително зададения елемент на работния лист. В нашия случай тя е 47921.53. Това означава, че от 11.03.2017 г. до края на анализирания период общите приходи за магазин 1 са 47 921.53 рубли.
SUMIFS
Ние завършихме проучването на операторите, които използват критериите, спирайки на функцията на РЕЗЮМЕ . Задачата на тази математическа функция е сумирането на стойностите на посочените области на таблицата, избрани чрез няколко параметъра. Синтаксисът на конкретния оператор е:
- =СУММЕСЛИМН(диапазон_суммирования;диапазон_условия1;условие1;диапазон_условия2;условие2;…)
- «Обхват на сумиране» е аргумент, който е адреса на този масив, клетките, в които, съответстващи на определен критерий, ще бъдат добавени.
- «Обхват на състояние» е аргумент, представляващ масив от данни, проверявани за съответствие със състоянието;
- «Условие» е аргумент, който е критерий за подбор.
- Тази функция включва операции с няколко групи подобни оператори.
Нека видим как този оператор е приложим за решаване на проблеми в контекста на нашата таблица с приходите от продажби в търговските обекти. Ще трябва да изчислим дохода, който Shop 1 донесе за периода от 09 до 13 март 2017 г. В същото време при сумирането на приходите трябва да се вземат предвид само тези дни с приходи над 14 000 рубли.
- Отново изберете клетката, за да видите общата сума и кликнете върху иконата «Вмъкване на функция» .
- В помощника за функции първо се преместваме в «Математически» блок и там избираме елемент, наречен «SUMMESLIMN» . Кликнете върху бутона «OK» .
- Прозорецът на аргументите на оператора, чието име е споменато по-горе, се стартира.
Задайте курсора в полето «Обхват на сумиране» . За разлика от последващите аргументи, този по рода си и насочва към масива от стойности, където ще бъде направено сумиране на данните, отговарящи на определените критерии. След това изберете района на линията «Shop1» , който публикува стойностите на приходите за съответния изход.
След като адресът се покаже в прозореца, отворете полето «Обхват на състояние1» . Тук ще трябва да покажем координатите на реда с датите. Захващаме левия бутон на мишката и избираме всички дати в таблицата.
Поставяме курсора в полето «Състояние1» . Първото условие е, че ще обобщим данните не по-рано от 9 март. Затова въвеждаме стойността «> 08.03.2017» .
Преминаваме към аргумента «Обхват на състояние2» . Тук трябва да въведете същите координати, които са били записани в полето «Обхват на състояние1» . Правим това по същия начин, т.е. като подчертаваме линията с датите.
Задайте курсора в полето «Състояние2» . Второто условие е, че дните, за които ще бъдат добавени приходите, трябва да бъдат не по-късно от 13 март. Затова написваме следния израз: «
Источник: https://soringpcrepair.com/criteria-in-excel/
Выборочные вычисления по одному или нескольким критериям
16069 20.02.2013 Скачать пример
Постановка задачи
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина «Копейка».
Способ 1. Функция СУММЕСЛИ, когда одно условие
Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в «Копейку», например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig). Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке:
Жмем ОК и вводим ее аргументы:
- Диапазон — это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае — это диапазон с фамилиями менеджеров продаж.
- Критерий — это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак — один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий ?????. А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву «П», а заканчивается на «В» — критерий П*В. Строчные и прописные буквы не различаются.
- Диапазон_суммирования — это те ячейки, значения которых мы хотим сложить, т.е. нашем случае — стоимости заказов.
Способ 2. Функция СУММЕСЛИМН, когда условий много
Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для «Копейки»), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия.
Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) — в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:
При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3—Условие3), и четвертую, и т.д. — при необходимости.
Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться — см. следующие способы.
Способ 3. Столбец-индикатор
Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в «Копейку» и от Григорьева, то в ячейке этого столбца будет значение 1, иначе — 0. Формула, которую надо ввести в этот столбец очень простая:
=(A2=»Копейка»)*(B2=»Григорьев»)
Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:
Способ 4. Волшебная формула массива
Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:
=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)
После ввода этой формулы необходимо нажать не Enter, как обычно, а Ctrl + Shift + Enter — тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.
Способ 4. Функция баз данных БДСУММ
В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM), которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев — ячейки, содержащие условия отбора — и указать затем этот диапазон функции как аргумент:
=БДСУММ(A1:D26;D1;F1:G2)
Источник: https://www.planetaexcel.ru/techniques/2/167/
Функция СЧЕТЕСЛИ в Excel
В этой статье мы сосредоточимся на функции СЧЕТЕСЛИ в Excel, которая предназначена для подсчета ячеек с указанным вами условием. Во-первых, мы кратко рассмотрим синтаксис и общее использование, а затем приведем ряд примеров функции СЧЕТЕСЛИ.
По сути, функция СЧЕТЕСЛИ на английском COUNTIF, идентична во всех версиях Excel, поэтому вы можете использовать примеры из этого руководства в Excel 2016, 2013, 2010 и 2007.
Синтаксис и использование функции СЧЕТЕСЛИ в Excel
Функция СЧЕТЕСЛИ в Excel используется для подсчета ячеек в пределах заданного диапазона, которые соответствуют определенному критерию или условию.
Например, вы можете использовать функцию СЧЕТЕСЛИ, чтобы узнать, сколько ячеек на вашем листе содержит число больше или меньше указанного вами числа. Другое типичное использование функции СЧЕТЕСЛИ в Excel — подсчет ячеек с определенным словом или началом с конкретной буквы (букв).
Синтаксис функции СЧЕТЕСЛИ очень прост:
=СЧЕТЕСЛИ(диапазон; критерий)
Как видите, есть только 2 аргумента функции СЧЕТЕСЛИ, оба из которых обязательны:
- диапазон – определяет одну или несколько ячеек для подсчета. Вы помещаете диапазон в формулу, как обычно, в Excel, например. A1:A20.
- критерии – определяет условие, которое сообщает функции, которую подсчитывают ячейки. Это может быть число, текстовая строка, ссылка на ячейку или выражение (например, «10», A2, «>=10»).
Вот простейший пример функции СЧЕТЕСЛИ в Excel. Формула =СЧЁТЕСЛИ(C2:C7;»Иванов Иван») подсчитывает, сколько заявок поступало от Иванова Ивана:
Функция СЧЕТЕСЛИ в Excel – Пример использования функции СЧЕТЕСЛИ в Excel
Примечание: Критерий нечувствителен к регистру, что означает, что если вы наберете «иванов иван» в качестве критерия в приведенной выше формуле СЧЕТЕСЛИ, это приведет к такому же результату.
Функция СЧЕТЕСЛИ в Excel – примеры
Синтаксис функции СЧЕТЕСЛИ очень прост, однако он допускает множество возможных вариантов критериев, включая подстановочные знаки, значения других ячеек и даже другие функции Excel.
Функция СЧЕТЕСЛИ в Excel для текста и чисел (точное совпадение)
Выше мы рассмотрели пример функции СЧЕТЕСЛИ, которая подсчитывает текстовые значения, соответствующие определенному критерию.
Вместо ввода текста вы можете использовать ссылку на любую ячейку, содержащую это слово или слова, и получить абсолютно одинаковые результаты, например: =СЧЕТЕСЛИ(С1:С7; С2).
Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ со ссылкой на ячейку
Аналогичные формулы СЧЕТЕСЛИ работают для чисел, также как для текстовых значений.
Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ для чисел
- На изображении выше формула =СЧЁТЕСЛИ(B2:B7;10) учитывает ячейки с количеством 10 в столбце D.
- Чтобы подсчитать ячейки со значениями больше, меньше или равными указанному вами числу, вы просто добавляете соответствующий логический оператор к критериям.
- Обратите внимание, что в функции СЧЕТЕСЛИ оператор с числом всегда заключен в кавычки, например, =СЧЕТЕСЛИ(B2:B7; «>=10»).
Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ для чисел с логическим оператором
Функция СЧЕТЕСЛИ с подстановочными знаками (частичное совпадение)
Если данные Excel содержат несколько вариантов ключевых слов, которые вы хотите подсчитать, вы можете использовать подстановочный знак в функции СЧЕТЕСЛИ для подсчета всех ячеек, содержащих определенное слово, фразу или буквы, как часть содержимого ячейки.
Предположим, у вас есть список цветов, и вы хотите узнать количество цветов, в названии которых содержится слово «синий». Поскольку эти цвета можно написать несколькими разными способами, мы вводим «*синий*» в качестве критериев поиска =СЧЕТЕСЛИ(B2:B8;»*синий*»).
Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ с частичным совпадением
Звездочка (*) используется в функции СЧЕТЕСЛИ для поиска ячеек с любой последовательностью ведущих и конечных символов, как показано в приведенном выше примере. Если вам нужно сопоставить какой-либо один символ, введите вместо него знак вопроса (?), например, =СЧЁТЕСЛИ(A2:A7;»ст?л»).
Функция СЧЕТЕСЛИ в Excel – Пример функции СЧЕТЕСЛИ с подстановочным знаком
В данном случае функция СЧЕТЕСЛИ вернет значение 2, так как найдет «стол» и «стул».
Источник: https://naprimerax.org/posts/86/funktciia-schetesli-v-excel
Excel. Подсчет и суммирование ячеек, отвечающих критерию условного форматирования
Ранее я описал, как с помощью пользовательской функции найти сумму значений в ячейках, выделенных цветом. К сожалению, эта функция не работает, если ячейки раскрашены с помощью условного форматирования. Я обещал «доработать» функцию.
Но за два года, прошедшие с публикации той заметки, я не смог ни самостоятельно, ни с помощью информации из Интернета написать удобоваримый код… (Дополнение от 29 марта 2017 г. Спустя еще пять лет, код мне всё же удалось написать; см. заключительную часть заметки). И вот недавно я наткнулся на идею, содержащуюся в книге Д.
Холи, Р. Холи «Excel 2007. Трюки», которая позволяет обойтись вовсе без кода.
Пусть есть список чисел от 1 до 100, размещенных в диапазоне А1:А100 (рис. 1; см. также лист «СУММЕСЛИ» Excel-файла) [1]. На диапазон наложено условное форматирование, помечающее ячейки, содержащие числа больше 10 и меньше или равно 20.
Рис. 1. Диапазон чисел; условным форматированием выделены ячейки, содержащие значения от 10 до 20
Скачать заметку в формате Word, примеры в формате Excel
Теперь необходимо сложить значения в ячейках, отвечающих только что установленному критерию. Неважно, какое именно форматирование применяется к этим ячейкам, однако необходимо знать критерий, согласно которому ячейки выделяются.
Чтобы сложить диапазон ячеек, отвечающих одному критерию, можно использовать функцию СУММЕСЛИ (рис. 2).
Рис. 2. Суммирование ячеек, отвечающих одному условию
Если у вас несколько условий, можно использовать функцию СУММЕСЛИМН (рис. 3).
Рис. 3. Суммирование ячеек, отвечающих нескольким условиям
Для подсчета числа ячеек, отвечающих одному критерию, можно использовать функцию СЧЁТЕСЛИ.
Для подсчета числа ячеек, отвечающих нескольким критериям, можно использовать функцию СЧЁТЕСЛИМН.
В Excel предусмотрена еще одна функция, которая позволяет указать несколько условий. Эта функция входит в набор функций баз данных Excel и называется БДСУММ. Чтобы проверить ее, используйте тот же набор чисел в диапазоне А2:А100 (рис. 4; см. также лист «БДСУММ» Excel-файла).
Рис. 4. Использование функций баз данных
Выделите ячейки C1:D2 и присвойте этому диапазону имя Критерий, введя его в поле имени слева от строки формул. Теперь выделите ячейку С1 и введите =$А$1, то есть ссылку на первую ячейку на листе, содержащую имя базы данных. Введите =$А$1 в ячейку D1 и вы получите две копии заголовка столбца А.
Эти копии будут использоваться как заголовки для условий БДСУММ (C1:D2), который вы назвали Критерий. В ячейке С2 введите >10. В ячейке D2 введите Макросы -> Макросы; в окне Макрос, выделите строку СумЦветУсл, и нажмите Выполнить. Запуститься макрос, выберите диапазон суммирования и критерий.
Ответ появится в окне.
Код процедуры
Sub СумЦветУсл()
Application.Volatile True
Dim SumColor As Double
Dim i As Range
Dim UserRange As Range
Dim CriterionRange As Range
SumColor = 0
' Запрос диапазона
Set UserRange = Application.
InputBox( _
Prompt:=»Выберите диапазон суммирования», _
Title:=»Выбор диапазона», _
Default:=ActiveCell.Address, _
Type:=8)
' Запрос критерия
Set CriterionRange = Application.
InputBox( _
Prompt:=»Выберите критерий суммирования», _
Title:=»Выбор критерия», _
Default:=ActiveCell.Address, _
Type:=8)
' Суммирование «правильных» ячеек
For Each i In UserRange
If i.DisplayFormat.Interior.
Color = _
CriterionRange.DisplayFormat.Interior.Color Then
SumColor = SumColor + i
End If
Next
MsgBox SumColor
End Sub
|
If i.DisplayFormat.Interior.Color = _ CriterionRange.DisplayFormat.Interior.Color Then
|
Хотя пользовательская функция и дает ошибку, но в процессе ее вызова можно «подсмотреть» ответ. В ячейке начните набирать формулу =su…
Воспользуйтесь подсказкой, кликнув на нее, а затем нажмите знак функции в строке формул:
Введите аргументы, и увидите ответ. К сожалению, нажав, ОК, получите в ячейке значение ошибки.
[1] Массив я создал с помощью функции =СЛУЧМЕЖДУ(1;100)
Источник: http://baguzin.ru/wp/excel-podschet-i-summirovanie-yacheek-otvecha/
Счетесли в excel по двум критериям
Смотрите также только если оба была добавлена функция ячейки, которые мыКОДСИМВ(«андрей»)*(A1:A10=»андрей»)*(B$1:B$10) одной формуле - Начиная с 2007 неправильность моей формулы будет отобрано 7 строк предварительного создания таблицы но позволяет понять тут. по заполненным строкам,Указали диапазон столбца В.
- (с помощью функции
- критерия указать ссылкуПодсчитывает количество ячеек, содержащих
- Для подсчета ЧИСЛОвых значений, условия выполняются. ТеперьСУММЕСЛИМН (SUMIFS) проверяем на выполнение)
ни в жизнь есть функция СЧЁТЕСЛИМН, :-) (см. строку состояния с условиями. Заголовки работу функции СУММПРОИЗВ(),В Excel можно не считает пустые «Критерий» — поставили РАНГ()). Этот подход на целый диапазон числа равных или Дат и Текстовых
Подсчет числовых значений с одним критерием
стоимости продаж осталось- в нейКритерия(т.е. сравнение (кода
| бы не додумалась. | которая позволяет задать | Марина | в нижней части |
| этой таблицы должны | которая может быть | из данных разных | ячейки. Формат ячейки «>300» — это применен в статьях Динамическая ячеек с критериями, |
| меньших 10. Критерий | значений, удовлетворяющих определенному | умножить на значения | количество условий проверки. В нашем случае первого символа ячейки) Спасибо |
|
полезна для подсчета | ячеек составить предложение. | ставим «процентный». значит, посчитать все сортировка таблицы в то функция вернет указан через ссылку |
критерию, существует простая получившегося столбца и увеличено аж до — это диапазон с (Для полноты картиныAndrey770 вариантов, но когдаПримечание с заголовками исходной с множественными критериями Эта функция удобна
Подсчет Текстовых значений с одним критерием
Если нужно, чтобы ячейки в столбце MS EXCEL и Отбор уникальных массив. В файле>=
Подсчет дат с одним критерием
и эффективная функция просуммировать отобранное в 127! Функция находится с фамилиями менеджеровпроизведением ещё один способ: 2003 excel применяю к таблице: подсчет значений с
таблицы. Размещение условий в других случаях. для заполнения бланков, расчет производился с В, где цифры значений с сортировкой
Подсчет с несколькими условиями
примера формула =СЧЁТЕСЛИ(A16:A25;C16:C18)(ячейка С4) СЧЁТЕСЛИ(), английская версия зеленой ячейке: в той же продаж.кода первого символа (только для первойAndrey770 исправленную формулу или множественными критерями также в одной строкеРазберем подробнее применение функции
учетом пустых ячеек, больше 300. в MS EXCEL. возвращает массив {3:2:5}.11 COUNTIF(). Подсчитаем значенияЕсли вы раньше не категорииКритерий искомой строки и
буквы слов):: А если так СУММПРОИЗВ и т.д.
Специальные случаи использования функции
рассмотрен в статьях соответствует Условию И. СУММПРОИЗВ(): об этой возможности
формула будет такая.Получилось такая формула.В Excel функция «СЧЕТЕСЛИ» (ячейка С5) в диапазоне в
- сталкивались с такойМатематические- это то, преобразованных результатов логическихКод =СУММПРОИЗВ(—(КОДСИМВ(A1:A10)=КОДСИМВ(«андрей»)*(A1:A10=»андрей»)*(B$1:B$10curious dare=СУММПРОИЗВ((C3:C31=»андрей»)*(I3:I31 ничего не получается, Подсчет значений сЗдесь есть один трюк:Результатом вычисления A2:A13=D2 является читайте в статье=СЧЁТЕСЛИ(B15:B19;»да»)/ЧСТРОК(B15:B19)Формула посчитала таксчитает данные ячеек,
- Для ввода формулы выделите=СЧЁТЕСЛИ($A$2:$A$12; C4&C5) случае одного критерия, замечательной возможностью Excelи работает похожим что мы ищем операций)) I ask: andSerge 007 пишет — о множественными критериями (Часть в качестве второго массив {ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ИСТИНА:ИСТИНА:ИСТИНА:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ:ЛОЖЬ} Значение «Функция «Сцепить» вФункцией «СЧЁТЕСЛИ» в Excel
- excel2.ru
Функция «СЧЁТЕСЛИ» в Excel
— в двух только, когда выполнятся диапазон ячеек такого3 а также покажем как формулы массива, образом, но имеет в предыдущем указанномвариант немного странный what are the: К сожалению не :-((( 2. Условие ИЛИ), аргумента функции БСЧЁТА() ИСТИНА соответствует персики. Excel».
можно одновременно подсчитать ячейках стоят цифры условия, которые мы же размера какПодсчитывает количество ячеек, содержащих как ее использовать то советую почитать больше аргументов: диапазоне. Разрешается использовать (на мой взгляд), minuses for?see answer работаетне могу понять Часть3, Часть4.
(поле) нужно ввести
Результат можно увидеть,Произведем подсчет строк, удовлетворяющих для подсчета неповторяющихся предварительно про нихПри помощи полосы прокрутки символы * (звездочка) но в таком See thisBuckminster в чем проблема… критерии. В Строке более 11. Критерий значений и вычисления много хорошего здесь. в правой части и ? (вопросительный
случае дополнительное преобразованиеНе по теме:: Посмотрите файл, уМарина: Подскажите в чем столбца с текстовыми затем нажав клавишу которые образуют Условие их сумму, произведение,Другие примерыкак посчитать количество ячеек формул введите формулу указан через ссылку
ранга. Ну, а в окна можно задать знак) как маски типов, действительно, необходимо…да, но
меня работает, или: Отзовитесь,пожалуйста, хоть кто-нибудь… ошибка, может вообще значениями, т.к. БСЧЁТА()F9 И. т.д.применения функции «СЧЁТЕСЛИ».
в Excel и нажмите и параметрСинтаксис нашем случае задача и третью пару или символы подстановки.p.s.
anyway thank you свой выкладывайтеSh_Alex неправильная формула:-): =СЧЁТ(ЕСЛИ($C$4:$C$16=»да»;ЕСЛИ($B$4:$B$16=$G4;0);0)) подсчитывает текстовые значения.;В качестве исходной таблицыНапример, в ячейкахВ некоторых примерах в
.CTRL+SHIFT+ENTERПримечаниеСЧЁТЕСЛИдиапазонкритерий решается одной формулой:
( Звездочка подменяет собойв любом случае for the informationпояснения…ЗЫ У меня: Отозвался, но свою
Задача: посчитать по В случае использования БСЧЁТ() нужноДвойное отрицание (—) преобразует возьмем таблицу с B1:Е1 стоят числа формулах стоит знакНапример, нужно посчитать, т.е. введите ее. О подсчете значений,)=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)Диапазон_условия3 любое количество любых присоединяюсь к благодарности действительно, то, что андрей с маленькой
- задачу решать придется
- знак. Ддя чего определеную цифру, слово,Это свойство функции СЧЁТЕСЛИ() читайте в статье — диапазон, в котором необходимо нажать неУсловие3 — один любойза привлечение внимания как более очевидное
- excel-office.ru
двум критериям. записать другую формулу вышеуказанный массив в двумя столбцами: текстовым — 4 и «звездочка». Это подстановочный ячейки, которые содержат как формулу массива. удовлетворяющих нескольким критериямДиапазонПосле ввода этой формулы- символов, вопросительный знакPelena я вначале воспринял буквы, а у Вам самой. ЯСпасибо =БСЧЁТ(A1:B13;B1;D14:E15). Табличка с числовой: {0:0:0:0:1:1:1:0:0:0:0:0} «Фрукты» и числовым 7. Нам нужно
Подсчет значений с множественными критериями (Часть 1. Условие И) в MS EXCEL
Вас? только все наличиеGuest критериями не изменится.
Источник: https://my-excel.ru/excel/schetesli-v-excel-po-dvum-kriterijam.html
3 способа подсчитать итоги по условию в Excel
Те, кто много работает с разными данными, рано или поздно сталкиваются с необходимостью подсчитать итоговые суммы по одному или нескольким условиям. Например, из базы подневных расходов вытащить помесячные суммы. Или из базы продаж собрать итоги по городу, году и менеджеру и т.д.
https://www.youtube.com/watch?v=7FF-FIJlVac
Рассмотрим небольшой пример. На рисунке ниже Вы видите базу в виде таблицы и итоговый свод, который нужно заполнить данными. В базе есть расходы по датам и по статьям (причем в одну дату может быть несколько строк расходов по любой статье). Задача — свести данные по месяцам и по статьям.
Исходные данные и свод для заполнения
- Сегодня мы рассмотрим 3 способа это сделать:
- 1) Функция СУММЕСЛИМН
- 2) Функция СУММПРОИЗВ
- 3) Сводная таблица
Но сначала поделимся с Вами небольшой хитростью. В итоговом своде в ячейках с названиями месяцев на самом деле введен не текст («Январь», «Февраль» и т.д.), а даты первого дня каждого месяца (01.01.2017, 01.02.2017 и т.д.), а уже после к этим ячейкам применен пользовательский формат «ММММ».
Чтобы применить такой формат, выделите нужные ячейки, нажмите Ctrl+1, выберите вкладку Число, найдите пункт «(все форматы)» и в строку Тип введите четыре прописные буквы М. Применение пользовательского формата
Этот небольшой трюк впоследствии нам очень поможет при подсчете итоговых сумм.
Способ 1. Функция СУММЕСЛИМН
Один из очевидных способов решения задачи — использование специальной функции суммирования по нескольким условиям. Это умеет делать функция СУММЕСЛИМН. Она суммирует значения заданного диапазона только в тех строках/столбцах, в которых выполняются заданные условия.
К сожалению, она умеет воспринимать диапазоны условий только в том виде, в котором они представлены на листе, и не может «на лету» обработать их. Это значит, что если нам нужно свести данные по месяцам, то функция СУММЕСЛИМН требует наличия дополнительной колонки с месяцем. Добавим колонку «Номер месяца», в которой пропишем формулу
=МЕСЯЦ(A2)
и протянем ее вниз на всю высоту таблицы. Получим столбец с порядковым номером месяца (определяется по дате в первом столбце).
Дополнительный столбец
Теперь в ячейку для Статьи 1 месяца Январь (в примере это ячейка H3) введем формулу:
=СУММЕСЛИМН($C:$C;$D:$D;МЕСЯЦ(H$2);$B:$B;$G3)
- где:
- $C:$C — столбец «Сумма затрат» (первым указывается тот диапазон, итоги по которому нужно подсчитать).
- $D:$D — столбец проверки первого условия («Номер месяца»).
МЕСЯЦ(H$2) — первое условие. Ячейка H2 это «Январь». Так как мы вначале ввели туда 01.01.2017, а потом просто применили числовой формат, мы можем обработать эту ячейку функцией МЕСЯЦ и узнать порядковый номер месяца (и тогда функция сможет сравнить порядковый номер месяца в H2 и порядковые номера в столбце «Номер месяца»).
$B:$B — столбец проверки второго условия («Статья»).
$G3 — второе условие. Ячейка с названием статьи затрат, по которой подводим итог.
Обратите внимание на закрепление ссылок. Это сделано для того, чтобы формулу можно было копировать. После ввода формулы, в ячейке H3 будет подсчитан итог по Январю и статье 1. Скопируйте формулу в другие ячейки и получите нужный результат (при копировании в другие кварталы, не забудьте перетянуть ссылку на строку месяцев, как показано на гифке ниже).
Копирование формулы
Способ 2. Функция СУММПРОИЗВ
Избежать создания доп.столбца (как в первом способе) можно путем применения функции СУММПРОИЗВ. Ее особенность в том, что она может обрабатывать внутри себя массивы данных, но при этом не требует ввода через Ctrl+Shift+Enter, то есть формально не является формулой массива.
В ячейку для Статьи 1 месяца Январь (в примере это ячейка H3) введем формулу:
=СУММПРОИЗВ((МЕСЯЦ($A$2:$A$1000)=МЕСЯЦ(H$2))*($B$2:$B$1000=$G3)*$C$2:$C$1000)
Закрепления ячеек, как и в первом примере, настроены для дальнейшего копирования. Только при переходе на другой квартал, не забудьте перетянуть строку с месяцем.
Разберем пошагово, как эта формула работает:
- Первая часть формулы (МЕСЯЦ($A$2:$A$1000)=МЕСЯЦ($H2)) делает следующее. Она берет диапазон А2:A1000 и к каждой ячейке применяет функцию МЕСЯЦ, то есть из каждой даты получает номер месяца. Затем каждый из полученных номеров сравнивается с номером месяца в ячейке H2 (это наш Январь в итоговом своде). Результат такого сравнения — столбец из значений ИСТИНА (если номера совпали) и ЛОЖЬ (если не совпали). Все эти вычисления происходят внутри формулы и не выносятся на лист. Обратите внимание, что сравнение нужно обязательно заключать в скобки!
Работа первой части формулы
- Вторая часть формулы ($B$2:$B$1000=$G3) делает то же самое, но для колонки Статьи (здесь нам не требуется применение функции МЕСЯЦ, названия статей сравниваются напрямую). Сравнение также берется в скобки и на выходе так же дает столбец значений ИСТИНА и ЛОЖЬ.
- На третьем этапе столбцы перемножаются между собой. В Excel при умножении логических значений ИСТИНА и ЛОЖЬ на выходе получается ноль или единица.
Перемножение столбцов сравнения
Полученный столбец нулей и единиц умножается на столбец, сумму по которому нужно свести. В итоге в тех строках, где оба условия совпали, сумма умножится на 1, то есть не изменится. А в тех строках, где хотя бы одно условие неверно, сумма умножится на 0 и станет равна 0.
- На завершающем этапе функция складывает значения полученного столбца и выдает итоговую сумму.
Правила работы с функцией при подобных расчетах:
- не указывать целые столбцы и строки в качестве аргументов;
- перемножаемые диапазоны-аргументы должны быть равны (А2:А1000 и B2:B1000, например);
- все выражения сравнения нужно заключать в скобки.
Способ 3. Сводная таблица
Третий способ — подсчет итогов через сводную таблицу. Поместите курсор в любую ячейку исходной базы, далее нажмите «Вставка» — «Сводная таблица». Для удобства, можно вставить таблицу на лист с базой и итоговым сводом.
Выбор места вставки таблицы
Поместите поле «Дата» в область строк, поле «Статьи» в область столбцов, а поле «Сумма затрат» в область значений, как показано на рисунке ниже.
Настройка сводной
Если Excel сразу не сгруппировал даты помесячно, то кликните на столбце с датами в сводной таблице правой кнопкой мыши и выберите «Группировать» с шагом «Месяцы». Для красоты можете применить к таблице встроенный или собственный стиль. В результате получится примерно такая таблица.
Сводная таблица
Собственно, итоги подсчитаны. Проблема лишь в том, что настроить макет сводной так, чтобы она выглядела как итоговый свод, не получится. Нужно перенести данные из сводной в нашу таблицу. Есть следующие способы:
- простое копирование и вставка. Подходит, если сводная не будет обновляться и менять размеры (не будут добавляться новые статьи);
- использование функции ИНДЕКС, для извлечения данных из сводной. Про эту функцию мы недавно подробно рассказывали. Этот способ подойдет, если сводная будет обновляться, но новые статьи не будут появляться;
- использование функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ. Самый интересный способ, который позволяет гибко менять сводную и всё равно подтягивать данные. Разберем его подробнее.
Встаньте в ячейку Января и Статьи 1 итогового свода и попробуйте сослаться на соответствующую ячейку сводной таблицы. Скорее всего, Excel вместо простой ссылки, вроде =А15, вставит огромную формулу
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Сумма затрат»;$M$2;»Дата»;1;»Статья»;»Статья 1″)
Она то нам и нужна. Если формула не появилась, Вы можете ввести ее вручную или включить в настройках. Кликните на сводной таблице, найдите на ленте вкладку «Анализ», нажмите маленькую стрелочку рядом с кнопкой «Параметры» и поставьте галочку «Создать GetPivotData»:
Включение функции ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ
Вернемся к функции. Она имеет 2 обязательных аргумента и дополнительные.
1) Поле сводной таблицы, которое находится в области значений. В нашем случае это поле «Сумма затрат». Можете оставить в аргументе этот текст, а можете сослаться на шапку соответствующего столбца базы, чтобы сократить формулу;
2) Адрес любой ячейки сводной таблицы. Указывается на случай, если на листе их несколько и Excel должен понять, с какой именно работать;
3) Дополнительные аргументы парные. Они состоят из названия поля, по которому задается условие отбора, и самого условия (похоже на функцию СУММЕСЛИМН).
В нашем случае нужно указать поле «Дата», в качестве условия для него — месяц. Так как в сводной месяц представлен в виде текста «янв», «фев» и т.д., нам нужно превратить наш заголовок в такой же текст. Так как мы вводили туда даты (делали это в начале статьи), получить нужное сочетание поможет формула
=ТЕКСТ(H2;»МММ»), где H2 — ячейка с месяцем в итоговом своде
Вторая пара условий — поле «Статья» и ссылка на название статьи в итоговом своде. В результате для ячейки Января и Статьи 1 получим формулу:
=ПОЛУЧИТЬ.ДАННЫЕ.СВОДНОЙ.ТАБЛИЦЫ(«Сумма затрат»;$M$2;»Дата»;ТЕКСТ(H$2;»МММ»);»Статья»;$G3)
В данном примере сводная начинается в ячейке $M$2. Формулу можно копировать, как и все предыдущие.
Итоговый результат
Файл-пример, в котором реализованы все 3 способа подсчета, Вы можете скачать по этой ссылке.
Для закрепления материала, можете посмотреть ролик по данному уроку на нашем YouTube канале.
- Поддержать наш проект и его дальнейшее развитие можно вот здесь.
- Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot
- С уважением, команда tDots.ru
Источник: https://zen.yandex.ru/media/id/59affb7afd96b11e8eadd771/5a74034d8c8be30f0667f4bf
Загрузка...
