Что такое критерий в эксель

Excel счетеслимн

​Смотрите также​ моём примере используется​ поскольку СЧЁТЕСЛИМН (),​ автоматизированной и эффективной​ можно в качестве​ 31.12.2010 включительно (3).​9​Как видим, с помощью​ Он выполняет указанный​ область строки​ функцией​«Диапазон суммирования»​ выручка по дням​ Магазина 4 и​«Диапазон условия»​.​),​

​Программа Microsoft Excel является​ формула СЧЕТЕСЛИМН и​

Применение критериев

​ то все-таки в​ работы с данными.​ критерия использовать ссылку:​=СУММПРОИЗВ((B14:B17>=ДАТАЗНАЧ(«01.01.2010»))*(B14:B17​10​ инструментов, использующих при​ вид форматирования ячеек,​«Магазин1»​​СУММЕСЛИ​​— это необязательный​ Магазина 1. Они​ Магазина 5. Действие​​несколько раз указать​​После того, как все​​«<>»​​ не просто табличным​​ сохранение в Excel​​ формате 2007. (хоть​​ Поэтому продвинутому пользователю​​Посчитаем текстовые значения в​Подсчитывает число ячеек в​11​ своей работе критерии,​ которые отвечают заданным​, в которой размещены​. В нашем случае​ аргумент. Он указывает​

СЧЁТЕСЛИ

​ расположены в строке,​​ выполняем по тому​​ один и тот​ вышеуказанные манипуляции произведены,​(​ редактором, а ещё​ 1997-2003 невозможно! :о(((​ и не люблю​

​ настоятельно рекомендуется внимательно​

​ одном диапазоне. Условие​ диапазоне B14:B17 с​​12​​ в Экселе можно​ условиям. Взглянем на​ значения выручки по​ он равен 47921,53.​

​ на конкретную область​​ которая так и​ же алгоритму, что​ же массив. Но​ клацаем по кнопке​«не равно»​ и мощнейшим приложением​Задача стоИт такая:​ я сохранять у​ изучить все приведенные​ поиска – один​ датами в интервале​13​ решать довольно разноплановые​ пример работы с​ соответствующей торговой точке.​​ Это означает, что​​ массива, в которой​​ называется​​ и для первого​​ при этом в​​«OK»​​). Например, если задать​​ для различных вычислений.​​Необходимо подсчитать кол-во​​ себя файлы. )​​ выше примеры.​​ критерий.​​ от 01.01.2010 до​​14​ задачи. Это может​​ условным форматированием.​После того, как адрес​ начиная с 11.03.2017,​​ будет производиться суммирование.​​«Магазин 1»​ аргумента данной группы.​ качестве соответствующих аргументов​.​ выражение​ Не в последнюю​ ячеек, удовлетворяющих следующим​Hugo​Посчитаем количество реализованных товаров​Формула: =СЧЁТЕСЛИ(A1:A11;»табуреты»). Или:​

​ 31.12.2010 включительно (3).​15​ быть, как подсчет​Выделим те ячейки таблицы​

​ отобразился в окне,​ и до конца​ Если его опустить​. После этого координаты​В поля​«Условие»​Программа производит подсчет и​«, то при подсчете​ очередь такая возможность​ условиям:​

​: так разве не​ по группам.​Во втором случае в​ В данном примере​16​​ сумм и значений,​​ синим цветом, где​

​ превышают 14000 рублей.​​. Тут нам нужно​​ 1 составила 47921,53​ считается, что он​Далее устанавливаем курсор в​«Условие3»​На примере все той​ был выделен перед​«Диапазон»​ некоторых функций (операторов)​ быть значением подраздела,​dydyka​​ оказались рядом.​​Формула с применением знака​

​ которая впервые появилась​B​ многих других задач.​​Выделяем весь массив элементов​​ будет отобразить координаты​

​«Условие4»​ недельной выручкой магазинов​

СЧЁТЕСЛИМН

​Мастера функций​ числовые значения менее​​ условия вычисления, которые​​ целое, а с​ думал проще будет,​ — «Номер функции».​​Для расчета количества значений,​​ Функция ДАТАЗНАЧ преобразует​Счет​ в данной программе​ котором указана выручка​ Производим зажим левой​ используют критерии, остановившись​«Диапазон»​. Тут нам нужно​и​ посмотрим, как это​

​ 50. Использование данных​​ принято называть критериями.​ десятыми 3.1, 3.2,​ но этот вариант​ Это числа от​ оканчивающихся на «и»,​ даты в числовые​Егоров​ с критериями, то​ торговых точек по​ кнопки мыши и​ на функции​

​.​​ указать нижнюю границу​«Условие5»​ работает. Нам нужно​ данном случае результат​ знаков для указания​ Давайте подробнее узнаем,​ 2.1, 1.5 и​ тоже отлично подошел​ 1 до 11,​ в которых содержится​ значения, с которыми​15 000​ есть, с определенными​ дням.​ выделяем все даты​СУММЕСЛИМН​Теперь, как всегда, рассмотрим​

​ значений в ячейках,​вносим соответственно значения​ узнать количество дней​ равен числу 5.​ параметров будут актуальными​ каким образом можно​ т.д.​lapink2000​ указывающие статистическую функцию​ любое число знаков:​ может работать функция​​Егоров​​ условиями, при выполнении​Передвигаемся во вкладку​ в таблице.​. Задачей данной математической​ применение данного оператора​​ которые будут принимать​​«>15000​ недели, когда доход​

​ Это означает, что​ и для всех​ их использовать при​2. Значение в​: или так:​ для расчета промежуточного​ =СЧЁТЕСЛИ(A1:A11;»*и»). Получаем:​ СУММПРОИЗВ.​9 000​ которых активируется указанное​«Главная»​Ставим курсор в поле​

• ​ функции является суммирование​ на практике. На​
• ​ участие в подсчете.​»,​
• ​ во всех указанных​ в выделенном массиве​
• ​ других вариантов, о​ работе в Экселе.​
• ​ столбце С должно​=СУММА(СЧЁТЕСЛИ(A1:A13;<"*замена*";"*установка*">))​

​ находятся значения превышающие​​ в данном уроке​​ Excel​3. Все считаемые​: Всем доброго дня!​ цифрой «2» (функция​Используем в функции СЧЕТЕСЛИ​ Позволяет найти число​Гладких​ также условное форматирование.​, размещенной в блоке​ то, что нами​

​ нескольким параметрам. Синтаксис​ стоит задача подсчитать​.​«>11000»​ нормы. Нормы выручки​​ 15000. То есть,​​ ниже.​​Критерии представляют собой условия,​​ ячейки должны относиться​

​ Нужна помощь. Необходимо​​ «СЧЕТ»).​​ условие поиска «не​ ячеек по определенному​20 000​Автор: Максим Тютюшев​«Стили»​ будут суммироваться данные​ указанного оператора таков:​ сумму выручки в​В поле​и​

​ следующие:​​ можно сделать вывод,​​А теперь давайте на​​ при которых программа​​ к Разделу 1,​​ подсчитат колычество совпадений​​Скачать примеры функции СЧЕТЕСЛИ​​ равно».​​ критерию. Работает с​​Егоров​​Обзор ​​на ленте. Открывается​​ не ранее 09​​=СУММЕСЛИМН(диапазон_суммирования;диапазон_условия1;условие1;диапазон_условия2;условие2;…)​​ Магазине 1 за​​«Диапазон условия2»​​«>32000»​Магазин 1 – 14000​ что в Магазине​ конкретном примере посмотрим,​ выполняет определенные действия.​

​ т.е. номера строк​ при выполнении двух​ в Excel​Формула: =СЧЁТЕСЛИ(A1:A11;»<>»&»стулья»). Оператор «<>»​ числовыми и текстовыми​​5 000​​Функция СЧЁТЕСЛИМН применяет условия​

​ условий. Как пример:​Формула нашла количество значений​ означает «не равно».​ значениями, датами.​Туманов​ к ячейкам в​ в нём по​ значение​

​— это аргумент,​ 11.03.2017.​ адрес тем же​ эти значения соответствуют​Магазин 2 – 15000​​ днях из анализируемых​​ оператор на практике.​ целом ряде встроенных​

​dydyka​ строк (больше тысячи)​​ и значение «стулья».​​ для анализа и​Описание (результат)​ Функция СУММЕСЛИМН вычисляет​.​Перемещаемся к аргументу​ котором, отвечающие определенному​​ результата. Щелкаем по​​«Диапазон условия1»​

​ магазина.​​ рублей;​​Урок: Мастер функций в​ пяти магазинам за​ присутствует выражение​Первые два условия​​: есть столбец в​​ подобное сочетание функций​При применении ссылки формула​ подсчета (обязательный).​=СЧЁТЕСЛИ(B2:B7;»>9000″)​ сумму значений ячеек​

​Активируется окошко генерации правила​​«Диапазон условия2»​​ критерию, будут складываться.​ пиктограмме​​, то есть, опять​

​После того, как был​Магазин 4 – 11000​ программе Эксель​​ неделю. Нам нужно​​«ЕСЛИ»​

СУММЕСЛИ

​«Вставить функцию»​ вносим координаты ячеек​​ произведен ввод всех​​ рублей;​Следующей функцией, которая оперирует​ узнать количество дней​. К данной группе​ задать, но вот​ найти 2 разных​light26​Часто требуется выполнять функцию​

​ которому нужно подсчитать​

​ ячеек в диапазоне​​ нескольким условиям.​​ выбора типа правила​ те же координаты,​— аргумент, представляющий​.​ со значениями выручки​ необходимых данных (всего​Магазин 5 – 32000​ критериями, является​​ за этот период,​​ операторов, прежде всего,​

​ с третьим –​​ слова, ни как​: Ничего не понимаю.​ СЧЕТЕСЛИ в Excel​ ячейки (обязательный).​ B2:B7, значения которых​Вложение функции в функцию​ выделяем наименование​ которые были записаны​ собой массив данных,​Перейдя в​ по первой торговой​

​ 10 полей), жмем​​ рублей.​СЧЁТЕСЛИМН​ в которых в​ нужно отнести​ беда. Помогите, пжл.​ не могу в​ Почему формула отказывается​ по двум критериям.​В диапазоне ячеек могут​ больше 9000 (4).​Функция ЕСЛИ​​«Форматировать только ячейки, которые​​ в поле​

​ проверяемый на соответствие​Мастер функций​ точке.​ на кнопку​Для выполнения вышеуказанной задачи,​. Она также относится​ Магазине 2 доход​СЧЁТЕСЛИ​Если у кого-то​ условие их вставить​

​ считать, если вместо​ Таким способом можно​ находиться текстовые, числовые​=СЧЁТЕСЛИ(B2:B7;»​​Функция СУММЕСЛИМН​​ содержат»​

​Функция СЧЁТЕСЛИМН​​. В первом поле​​. Делаем это тем​«Условие»​«Математические»​«Условие2»​.​ рабочего листа, куда​ операторов. Задачей​ 15000 рублей.​СЧЁТЕСЛИМН​ 10-ый офис будет​ соответственно не работает​

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

​указываем верхнюю границу​​Программа производит подсчет и​​ будет выводиться итог​СЧЁТЕСЛИМН​Выделяем элемент листа, в​,​ мешать, я постараюсь​(нужно посчитать общее​​Задача — подсчитать​​ случаи применения СЧЕТЕСЛИ​ Пустые ячейки функция​

​ B2:B7, значения которых​Функция ЕСЛИОШИБКА​ списка возможных вариантов​ есть, путем выделения​​ собой критерий отбора​​ наименование​

СУММЕСЛИМН

​ для сложения.​«СУММЕСЛИ»​«.​​ экран. Как видим,​​СЧЁТЕСЛИМН​ в указанном массиве,​ выводить результат вычисления.​,​ 97-ой.​или одной формулой​

​ заданном диапазоне F1:F28,​

​ примеры с двумя​​В качестве критерия может​=СЧЁТЕСЛИМН(B2:B7;»>=9000″;B2:B7;»​Мы стараемся как​«Значение ячейки»​Устанавливаем курсор в поле​

​Данная функция подразумевает операции​​. Клацаем по кнопке​После того, как все​ он равен числу​. Клацаем по иконке​

​ которые удовлетворяют определенному​​ После этого щелкаем​СУММЕСЛИМН​Спасибо!​

​ тут не обойтись?​ которые больше С1,​ условиями.​

​ быть ссылка, число,​Функция СЧЁТЕСЛИМН (доступна в​ можно оперативнее обеспечивать​. В следующем поле​«Условие2»​ сразу с несколькими​«OK»​ указанные действия произведены,​ 3. Это означает,​«Вставить функцию»​ набору условий. Именно​ по пиктограмме​. Кроме встроенных операторов,​iba2004​Юрий М​ но меньше D1​Посчитаем, сколько ячеек содержат​

​ текстовая строка, выражение.​ Excel 2007 и​ вас актуальными справочными​​ выбираем позицию​​. Вторым условием является​

​«Больше»​​ то, что дни,​​Посмотрим, как данный оператор​Запускается окно аргументов функции​«OK»​ днях из анализируемой​Перейдя в​ можно задать не​.​ используются также при​ хотел помочь. ​Ещё на этой странице​​:​​ «стулья». Формула: =СЧЁТЕСЛИ(A1:A11;»столы»)+СЧЁТЕСЛИ(A1:A11;»стулья»).​ только с одним​ приложения) подсчитывает число​

​ языке. Эта страница​. В последнем —​ за которые будет​​ применим для решения​​СУММЕСЛИ​.​ недели выручка во​Мастер функций​ один, а несколько​Производится запуск​ условном форматировании. Рассмотрим​

​Я нашёл выход.​​ чуть ниже «​​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=СЧЁТЕСЛИМН(F1:F28;»>»&C1;F1:F28;»​ Для указания нескольких​ условием (по умолчанию).​ ячеек в диапазоне​ переведена автоматически, поэтому​ указываем само значение,​​ суммироваться выручка, должны​​ задач в контексте​

​. В нём имеется​​Программа выдает результат расчета.​​ всех торговых точках​, снова перемещаемся в​ параметров, и отличает​Мастера функций​​ их применение при​​Для моего примера:​»вопрос по операции​light26​ условий используется несколько​

​ Но можно ее​​ B2:B7, значения которых​​ ее текст может​ больше которого требуется​ быть не позже​ нашей таблицы выручки​ три поля, соответствующих​ Как видим, итоговое​ превышала установленную для​​ блок​

​ этого оператора от​​. Совершаем перемещение в​​ работе с различными​=СЧЁТЕСЛИМН(ДВССЫЛ(«A1:A»&ПОИСКПОЗ(«Раздел 2.*»;A:A;0));»>»&A4;ДВССЫЛ(«A1:A»&ПОИСКПОЗ(«Раздел 2.*»;A:A;0));»0″)​ СУММЕСЛИ» не то?​: А, так вот​ выражений СЧЕТЕСЛИ. Они​ «заставить» проанализировать 2​

​ больше или равны​ содержать неточности и​ отформатировать элементы таблицы.​ 13 марта. Поэтому​​ от реализации в​​ аргументам указанного оператора.​ значение равно 5.​ них норму.​«Статистические»​ предыдущего. Синтаксис следующий:​ блок​​ инструментами данного табличного​​но м.б. у​

​dydyka​ где собака порылась. ​​ объединены между собой​​ критерия одновременно.​

Условное форматирование

​ кого-то родится более​: нет​Только странно, что​ оператором «+».​Рекомендации для правильной работы​ или равны 22500​ нас важно, чтобы​ 14000. Чтобы выбрать​«.​ нужно будет подсчитать​

​«Диапазон»​ в 5 днях​ Нам следует посчитать​ отыскать наименование​

​«Диапазон условия»​. Там находим и​Главной задачей оператора​ короткая формула?​конечный этап вычисления​

​:​ А1. Текст «стулья»​ необходимо, чтобы эта​

​ B2:B7, значения которых​ помогла ли она​

​Активируется окно форматирования. Передвигаемся​ тот самый массив,​ 2017 года. При​ В нашем случае​ до 17000 рублей.​ меньшую, чем 17000​ произвести нажатие на​ на область, в​«OK»​ ячеек, которые удовлетворяют​ДВССЫЛ заменить на​ или не работают​200?’200px’:»+(this.scrollHeight+5)+’px’);»>=SUMPRODUCT((F1:F28>C1)-(F1:F28>D1))​ — на базе​ книга была открыта.​ больше или равны​ вам, с помощью​ во вкладку​ адрес которого был​ этом при суммировании​

​ это будет строка​

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

​ рублей.​ кнопку​ которой будет производиться​.​ определенному заданному условию.​ ИНДЕКС​ :(​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=SUMPRODUCT((F1:F28>2)-(F1:F28>3))​

Хотите узнать больше?

​ критерия в ячейке​

​Аргумент «Критерий» нужно заключать​

​ кнопок внизу страницы.​

​ внесен, как массив​

Подсчет числа или даты, по условию

​ дохода должны учитываться​​ дат. Ставим курсор​ использует критерии, является​Ставим курсор в элемент,​«OK»​ подсчет ячеек, удовлетворяющих​Происходит активация окна аргументов​ Его синтаксис следующий:​Михаил С.​Михаил С.​Код200?’200px’:»+(this.scrollHeight+5)+’px’);»>=SUMPRODUCT(—(F1:F28>3))​ А2.​ в кавычки (кроме​ или равны 22500​ Для удобства также​. Из предложенных вариантов​ суммирования.​ только те дни,​ в данное поле​СУММЕСЛИ​ где будет произведен​.​

​ указанным условиям. Данный​ вышеуказанного оператора. В​=СЧЁТЕСЛИ(диапазон;критерий)​: Формула массива​: Файлик бы. (небольшой,​dydyka​Посчитаем число ячеек в​ ссылок).​​ (4). Эту функцию​​ приводим ссылку на​ цветов заливки выбираем​После того, как адрес​ выручка в которых​ и выделяем все​. В отличие от​ вывод на лист​Вслед за выполнением вышеуказанного​ оператор позволяет задать​​ поле​​Как видим, у данного​=СЧЁТ(ПОИСК(«,»;A1:ИНДЕКС(A1:A1000;ПОИСКПОЗ(«Раздел 2.»;A1:A1000;))))​ xls)​

Пример

​: есть столбец в​

​ диапазоне В1:В11 со​

​Функция не учитывает регистр​

​ можно использовать в​

​ оригинал (на английском​

​ синий, щелкая по​

​ указанного массива отобразился​

​ превысила 14000 рублей.​

​ ячейки, в которых​

​ предыдущих функций, он​

​ результатов подсчета. Клацаем​

​ алгоритма действий открывается​

​ сразу несколько таких​

​ оператора два аргумента.​

​не массивная​

​ котором мне необходимо​

​ значением большим или​

​ текстовых значений.​

​ Excel 2003 и​

​ нему левой кнопкой​

​ в окне, переходим​

​Снова выделяем ячейку для​

​ относится к математическому​

​следует указать область​

​: На счёт .xls​

​ найти 2 разных​

​При формулировании условия подсчета​ более ранних версиях,​Подсчет числа или даты,​ мыши. После того,​

​ вывода итога и​Так как нам нужно​ блоку операторов. Его​«Вставить функцию»​

​«Условие»​ ячеек, среди которых​представляет собой адрес​зы. Не могу​ поскольку СЧЁТЕСЛИМН (),​ слова, ни как​ меньшим или равным​ можно использовать подстановочные​ в которых функция​ которые соответствуют одного​

​ как выбранный цвет​

​«Условие3»​ клацаем по пиктограмме​ сложить только суммы​ задачей является суммирование​над рабочей площадью​.​представляет собой критерий,​ будет производиться подсчет.​ массива элементов на​ понять, почему не​ то все-таки в​ не могу в​

​ 200. Формула: =СЧЁТЕСЛИ(B1:B11;»>=100″)-СЧЁТЕСЛИ(B1:B11;»>200″).​

​ СЧЁТЕСЛИМН не поддерживается.​

​ условия (например, равно,​

​ отобразился в области​

​. Учитывая, что в​

​«Вставить функцию»​

​ выручки, начиная с​

​ данных в ячейках,​ листа.​В поле​ который определяет, какие​

​ листе, в которых​ работает =СЧЁТЕСЛИ(A2:A9;»*,*»)?​ формате 2007. (хоть​ условие их вставить​Применим в формуле СЧЕТЕСЛИ​ любой символ. «*»​Дата​ больше, чем меньше,​«Образец»​

​ суммировании будут принимать​

​.​ 11 марта, то​ которые соответствуют определенному​Так как мы совсем​«Диапазон условия1»​

​ элементы из соответствующего​

​ следует выделить содержимое​ следует произвести подсчет.​Михаил С.​ и не люблю​ =СЧЁТЕСЛИМН(Работы!$I:$I;B3;Работы!$J:$J;»*замена» И «установка»)-​ несколько диапазонов. Это​ — любая последовательность​11.03.2011​ чем больше или​, клацаем по кнопке​ участие только значения,​В​ в поле​ условию. Синтаксис таков:​ недавно применяли формулу​

Функция СЧЕТЕСЛИ в Excel и примеры ее использования

​следует ввести адрес​ массива данных войдут​ строки​«Критерий»​: забыл про условие​ я сохранять у​ соответственно не работает​

Синтаксис и особенности функции

​ возможно, если диапазоны​

• ​ символов. Чтобы формула​01.01.2010​ равно или меньше​
• ​«OK»​ величина которых превышает​Мастере функций​

​«Критерий»​=СУММЕСЛИ(диапазон;критерий;[диапазон_суммирования])​СЧЁТЕСЛИМН​ строки, в которой​ в подсчет, а​«Магазин 2»​

​— это аргумент,​ столбца «С»​ себя файлы. )​(нужно посчитать общее​ являются смежными. Формула:​ искала непосредственно эти​31.12.2010​ или равно), используйте​.​

​ 14000 рублей, вносим​, прежде всего, выполняем​

• ​вбиваем значение​Аргумент​, то теперь не​ расположены данные по​ какие не войдут.​
• ​, в которой расположены​ который задаёт условие,​=СУММПРОИЗВ(ЕЧИСЛО(ПОИСК(«,»;A1:ИНДЕКС(A:A;ПОИСКПОЗ(«Раздел 2.»;A:A;))))*(C1:ИНДЕКС(C:C;ПОИСКПОЗ(«Раздел 2.»;A:A;))>0))​
• ​Hugo​ количество: замена, установка​
• ​ =СЧЁТЕСЛИ(A1:B11;»>=100″)-СЧЁТЕСЛИ(A1:B11;»>200″). Ищет значения​ знаки, ставим перед​30.06.2010​ функцию​Автоматически происходит возврат к​ запись следующего характера:​ перемещение в блок​«>10.03.2017»​«Диапазон»​ обязательно переходить в​
• ​ выручке Магазина 1​ Каждой заданной области​ значения выручки по​ что именно должны​kim​

Функция СЧЕТЕСЛИ в Excel: примеры

​или одной формулой​ по двум критериям​ ними знак тильды​Формула​

​СЧЁТЕСЛИ​ окну генерации правила​

​«>14000»​«Математические»​.​указывает на область​ группу​ за неделю. Для​

​ данных нужно указывать​ дням. Ставим курсор​ содержать ячейки указанной​: Не может найти​

​ подходит (диапазон замените):​ тут не обойтись?​ сразу в двух​ (

​. Для подсчета​ форматирования. В нём​.​

​, а там выделяем​В поле​

​ ячеек, которые будут​«Статистические»​ этого ставим курсор​ условие отдельно, даже​ в указанное поле​

​ области, чтобы быть​ текст в числах,​

​=СЧЁТЕСЛИ(A1:A13;»*замена*»)+СЧЁТЕСЛИ(A1:A13;»*установка*»)​Юрий М​ столбцах. Если диапазоны​

​Для нормального функционирования формулы​=СЧЁТЕСЛИ(B14:B17;»>01.03.2010″)​ числа или даты,​ также в области​После выполнения последнего действия​

​ пункт под названием​«Диапазон суммирования»​

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

1. ​ в ячейках с​Подсчитывает число ячеек в​ которые входят в​«Образец»​ клацаем по кнопке​«СУММЕСЛИМН»​нужно указать область,​ условия. По сути,​
2. ​. Наименование данного оператора​ выделяем соответствующую строку​ если оно совпадает.​ кнопку мыши, выделяем​ В качестве параметра​ столбце А числа​: СПАСИБО, если честно​Ещё на этой странице​
3. ​ функция СЧЕТЕСЛИМН.​ текстовыми значениями не​ диапазоне B14:B17, в​ определенный диапазон (например,​отображается синий цвет.​«OK»​
4. ​. Производим клик по​ значения которой, отвечающие​ он задается по​ можно найти в​ в таблице. Координаты​ Обязательно требуется, чтобы​ соответствующий массив в​ может быть использовано​ в виде текста​ думал проще будет,​
5. ​ чуть ниже «​Когда в качестве критерия​ должно пробелов или​ которых указана дата​ больше чем 9000​ Тут нам нужно​.​ кнопке​ указанным критериям, будут​ тому же принципу,​ категории​ отображаются в окне.​ все массивы, используемые​ таблице. Адрес выделенного​

​ числовое выражение, текст​ — тады ой.​ но этот вариант​»вопрос по операции​ указывается ссылка на​ непечатаемых знаков.​ после 01.03.2010 (3).​ на том же​ произвести одно единственное​

ПРОМЕЖУТОЧНЫЕ.ИТОГИ и СЧЕТЕСЛИ

​Программа выводит результат на​«OK»​

1. ​ суммироваться. В нашем​ что и одноименный​«10 недавно использовавшихся»​
2. ​Учитывая, что для Магазина​ в качестве областей​ массива отобразится в​ или ссылка на​iba2004​ тоже отлично подошел​ СУММЕСЛИ» не то?​ диапазон ячеек с​​=СЧЁТЕСЛИ(B14:B17;»31.12.2010″)​

​ время и меньше,​ действие: клацнуть по​

​ лист. Он равен​.​ случае это значения​ аргумент функции​. Выделяем его и​ 1 дневная норма​

СЧЕТЕСЛИМН не выполняет условие при ссылке на ячейку (Формулы/Formulas)

​ условия, имели одинаковое​​ окне.​ ячейку, в которой​: 2 Михаил С.​lapink2000​dydyka​
​ условиями, функция возвращает​Посчитаем числовые значения в​Подсчитывает число ячеек в​ чем 22500), можно​ кнопке​

​ 62491,38. Это означает,​​Производится запуск окошка аргументов​ ​ выручки строки​

​СЧЁТЕСЛИ​​ щелкаем по кнопке​ выручки составляет 14000​​ количество строк и​В следующем поле​ критерий содержится. При​
​2 kim​

​: или так:​​: нет​ ​ массив. Для ввода​
​ одном диапазоне. Условие​
​ диапазоне B14:B17 с​

СЧЁТЕСЛИМН в одном условии два критерия?

​ использовать функцию​​«OK»​ что за период​ оператора, наименование которого​«Магазин1»​.​«OK»​ рублей, то в​ столбцов.​
​«Критерий»​ этом, для указания​

​конечный этап вычисления​​ формулы нужно выделить​

​ подсчета – один​ датой 31.12.2010 (1).​
​СЧЁТЕСЛИМН​.​

​ с 09 по​​ было указано выше.​
​. Выделяем соответствующий массив​«Критерий»​.​ поле​Для того, чтобы задать​как раз нужно​ критерия можно использовать​

​ ребят!​​malder​ счетеслимн два разных​

​ такое количество ячеек,​​ критерий.​ Знак равенства в​. Кроме того​После выполнения последнего действия,​ 13 марта 2017​Устанавливаем курсор в поле​ элементов листа.​

​— является обязательным​​Открывается уже знакомое нам​«Условие 1»​
​ несколько параметров одной​

​ задать непосредственный параметр​​ следующие знаки:​Да, Вы правы,​: Всем доброго дня!​ слова, но ввести​

​ как в диапазоне​​У нас есть такая​

​ условии не обязателен​

​ можно использовать функцию​​ все ячейки выделенного​ года сумма выручки​«Диапазон суммирования»​После того, как произведено​ аргументом, задающим параметр​ окошко аргументов оператора​вписываем выражение​

СЧЁТЕСЛИМН в одном условии два критерия?

​ и той же​​ отбора. В нашем​« (​ я не указал,​ Нужна помощь. Необходимо​ можно только одно​ с критериями. После​ таблица:​ и поэтому не​
​ СУММПРОИЗВ слишком.​ массива, где содержится​

​ при сложении её​. В отличие от​

​ введение всех указанных​​ отбора ячеек из​

​СЧЁТЕСЛИМН​«>14000»​
​ области данных, например,​ случае нужно подсчитать​

​«меньше»),​​ что в столбце​
​ подсчитат колычество совпадений​ * + и​ введения аргументов нажать​Посчитаем количество ячеек с​ указывается (формула будет​1​ число большее, чем​

​ за дни, в​​ последующих аргументов, этот​ данных, жмем на​

​ указанной области данных,​​. Ставим курсор в​.​ чтобы подсчитать количество​ только те элементы​«>»​ А м.б. что​ при выполнении двух​

​ или не работают​​ одновременно сочетание клавиш​ числами больше 100.​
​ работать и со​

​2​​ 14000, будут залиты​ которых она превышает​ единственный в своем​ кнопку​

​ которые будут суммироваться.​​ поле​

​ ячеек, в которых​​ таблицы, в которых​(​ угодно: и числа,​ условий. Как пример:​ :(​ Shift + Ctrl​ Формула: =СЧЁТЕСЛИ(B1:B11;»>100″). Диапазон​

СЧЕТЕСЛИМН

​ знаком равенства («=31.12.2010»).​​3​
​ синим цветом.​ 14000 рублей, составила​ роде и указывает​«OK»​ Принципы указания те​
​«Диапазон условия1»​
​«Диапазон условия2 (3,4,5)»​ расположены величины больше​ значение превышает 15000.​

​«больше»​ и слова и​ количество однофамильцев в​Михаил С.​ + Enter. Excel​ – В1:В11. Критерий​=СЧЁТЕСЛИМН(B14:B17;»>=01.01.2010″;B14:B17;»​4​

​Более подробно о возможностях​ 62491,38 рубля.​ на тот массив​

​.​ же, что и​и, произведя зажим​следует внести координаты​ определенного числа, но​ Поэтому с помощью​),​ т.д.​

​ организации​: Файлик бы. (небольшой,​ распознает формулу массива.​ подсчета – «>100».​Подсчитывает число ячеек в​
​5​ условного форматирования рассказывается​Последним, описанным нами, инструментом,​ значений, где будет​После этого в предварительно​ у аналогичных аргументов​
​ левой кнопки мыши,​

​ строк с недельной​​ меньше другого числа,​ клавиатуры вбиваем в​
​«=»​

​ xls)​СЧЕТЕСЛИ с двумя условиями​ Результат:​
​ диапазоне B14:B17 с​
​6​

​ в отдельной статье.​​ при работе с​
​ производиться суммирование подошедших​ указанный элемент рабочего​

​ предыдущих операторов, которые​​ выделяем все ячейки,​
​ выручкой соответственно Магазина​
​ следует в качестве​
​ указанное поле выражение​

​(​Но, я Вас​: Здравствуйте!​

​Михаил С.​​ в Excel очень​Если условие подсчета внести​
​ датами в интервале​

​7​​Урок: Условное форматирование в​ которым используются критерии,​ под указанные критерии​ листа будет выведен​ были рассмотрены нами​ в которых содержится​

​ 2, Магазина 3,​​ аргумента​
​«>15000»​
​«равно»​ благодарю, спасибо!​
​К сожалению, в​: На счёт .xls​ часто используется для​ в отдельную ячейку,​ от 01.01.2010 до​8​ программе Эксель​
​ является условное форматирование.​
​ данных. Затем выделяем​ результат обработки данных​

Использование критериев в Microsoft Excel

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

Применение критериев

Критерии представляют собой условия, при которых программа выполняет определенные действия. Они применяются в целом ряде встроенных функций. В их названии чаще всего присутствует выражение «ЕСЛИ». К данной группе операторов, прежде всего, нужно отнести СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СУММЕСЛИ, СУММЕСЛИМН. Кроме встроенных операторов, критерии в Excel используются также при условном форматировании. Рассмотрим их применение при работе с различными инструментами данного табличного процессора более подробно.

СЧЁТЕСЛИ

Главной задачей оператора СЧЁТЕСЛИ, относящегося к статистической группе, является подсчет занятых различными значениями ячеек, которые удовлетворяют определенному заданному условию. Его синтаксис следующий:

Как видим, у данного оператора два аргумента. «Диапазон» представляет собой адрес массива элементов на листе, в которых следует произвести подсчет.

«Критерий» — это аргумент, который задаёт условие, что именно должны содержать ячейки указанной области, чтобы быть включенными в подсчет. В качестве параметра может быть использовано числовое выражение, текст или ссылка на ячейку, в которой критерий содержится. При этом, для указания критерия можно использовать следующие знаки: «<» («меньше»), «>» («больше»), «=» («равно»), «<>» («не равно»). Например, если задать выражение «<50», то при подсчете будут учитываться только элементы, заданные аргументом «Диапазон», в которых находятся числовые значения менее 50. Использование данных знаков для указания параметров будут актуальными и для всех других вариантов, о которых пойдет речь в данном уроке ниже.

А теперь давайте на конкретном примере посмотрим, как работает данный оператор на практике.

Итак, имеется таблица, где представлена выручка по пяти магазинам за неделю. Нам нужно узнать количество дней за этот период, в которых в Магазине 2 доход от реализации превысил 15000 рублей.

1. Выделяем элемент листа, в который оператор будет выводить результат вычисления. После этого щелкаем по пиктограмме «Вставить функцию».
2. Производится запуск Мастера функций. Совершаем перемещение в блок «Статистические». Там находим и выделяем наименование «СЧЁТЕСЛИ». Затем следует клацнуть по кнопке «OK».

В следующем поле «Критерий» как раз нужно задать непосредственный параметр отбора. В нашем случае нужно подсчитать только те элементы таблицы, в которых значение превышает 15000. Поэтому с помощью клавиатуры вбиваем в указанное поле выражение «>15000».

СЧЁТЕСЛИМН

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

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

«Условие» представляет собой критерий, который определяет, какие элементы из соответствующего массива данных войдут в подсчет, а какие не войдут. Каждой заданной области данных нужно указывать условие отдельно, даже в том случае, если оно совпадает. Обязательно требуется, чтобы все массивы, используемые в качестве областей условия, имели одинаковое количество строк и столбцов.

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

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

1. Для выполнения вышеуказанной задачи, выделяем курсором элемент рабочего листа, куда будет выводиться итог обработки данных СЧЁТЕСЛИМН. Клацаем по иконке «Вставить функцию».
2. Перейдя в Мастер функций, снова перемещаемся в блок «Статистические». В перечне следует отыскать наименование СЧЁТЕСЛИМН и произвести его выделение. После выполнения указанного действия требуется произвести нажатие на кнопку «OK».
3. Вслед за выполнением вышеуказанного алгоритма действий открывается окно аргументов СЧЁТЕСЛИМН.

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

Учитывая, что для Магазина 1 дневная норма выручки составляет 14000 рублей, то в поле «Условие 1» вписываем выражение «>14000».

В поля «Диапазон условия2 (3,4,5)» следует внести координаты строк с недельной выручкой соответственно Магазина 2, Магазина 3, Магазина 4 и Магазина 5. Действие выполняем по тому же алгоритму, что и для первого аргумента данной группы.

В поля «Условие2», «Условие3», «Условие4» и «Условие5» вносим соответственно значения «>15000», «>24000», «>11000» и «>32000». Как нетрудно догадаться, эти значения соответствуют интервалу выручки, превышающую норму для соответствующего магазина.

Теперь несколько изменим задачу. Нам следует посчитать количество дней, в которых Магазин 1 получил выручку, превышающую 14000 рублей, но меньшую, чем 17000 рублей.

1. Ставим курсор в элемент, где будет произведен вывод на лист результатов подсчета. Клацаем по пиктограмме «Вставить функцию» над рабочей площадью листа.
2. Так как мы совсем недавно применяли формулу СЧЁТЕСЛИМН, то теперь не обязательно переходить в группу «Статистические»Мастера функций. Наименование данного оператора можно найти в категории «10 недавно использовавшихся». Выделяем его и щелкаем по кнопке «OK».
3. Открывается уже знакомое нам окошко аргументов оператора СЧЁТЕСЛИМН. Ставим курсор в поле «Диапазон условия1» и, произведя зажим левой кнопки мыши, выделяем все ячейки, в которых содержится выручка по дням Магазина 1. Они расположены в строке, которая так и называется «Магазин 1». После этого координаты указанной области будут отражены в окне.

Далее устанавливаем курсор в поле «Условие1». Тут нам нужно указать нижнюю границу значений в ячейках, которые будут принимать участие в подсчете. Указываем выражение «>14000».

В поле «Диапазон условия2» вводим тот же адрес тем же способом, который вводили в поле «Диапазон условия1», то есть, опять вносим координаты ячеек со значениями выручки по первой торговой точке.

В поле «Условие2» указываем верхнюю границу отбора: «<17000».

СУММЕСЛИ

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

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

«Критерий» — является обязательным аргументом, задающим параметр отбора ячеек из указанной области данных, которые будут суммироваться. Принципы указания те же, что и у аналогичных аргументов предыдущих операторов, которые были рассмотрены нами выше.

«Диапазон суммирования» — это необязательный аргумент. Он указывает на конкретную область массива, в которой будет производиться суммирование. Если его опустить и не указывать, то по умолчанию считается, что он равен значению обязательного аргумента «Диапазон».

Теперь, как всегда, рассмотрим применение данного оператора на практике. На основе той же таблицы перед нами стоит задача подсчитать сумму выручки в Магазине 1 за период, начиная с 11.03.2017.

1. Выделяем ячейку, в которой будет производиться вывод результата. Щелкаем по пиктограмме «Вставить функцию».
2. Перейдя в Мастер функций в блоке «Математические» находим и выделяем наименование «СУММЕСЛИ». Клацаем по кнопке «OK».
3. Запускается окно аргументов функции СУММЕСЛИ. В нём имеется три поля, соответствующих аргументам указанного оператора.

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

Так как нам нужно сложить только суммы выручки, начиная с 11 марта, то в поле «Критерий» вбиваем значение «>10.03.2017».

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

СУММЕСЛИМН

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

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

«Диапазон условия» — аргумент, представляющий собой массив данных, проверяемый на соответствие условию;

«Условие» — аргумент, представляющий собой критерий отбора для сложения.

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

Посмотрим, как данный оператор применим для решения задач в контексте нашей таблицы выручки от реализации в торговых точках. Нам нужно будет подсчитать доход, который принес Магазин 1 за период с 09 по 13 марта 2017 года. При этом при суммировании дохода должны учитываться только те дни, выручка в которых превысила 14000 рублей.

1. Снова выделяем ячейку для вывода итога и клацаем по пиктограмме «Вставить функцию».
2. В Мастере функций, прежде всего, выполняем перемещение в блок «Математические», а там выделяем пункт под названием «СУММЕСЛИМН». Производим клик по кнопке «OK».
3. Производится запуск окошка аргументов оператора, наименование которого было указано выше.

Устанавливаем курсор в поле «Диапазон суммирования». В отличие от последующих аргументов, этот единственный в своем роде и указывает на тот массив значений, где будет производиться суммирование подошедших под указанные критерии данных. Затем выделяем область строки «Магазин1», в которой размещены значения выручки по соответствующей торговой точке.

После того, как адрес отобразился в окне, переходим к полю «Диапазон условия1». Тут нам нужно будет отобразить координаты строки с датами. Производим зажим левой кнопки мыши и выделяем все даты в таблице.

Ставим курсор в поле «Условие1». Первым условием является то, что нами будут суммироваться данные не ранее 09 марта. Поэтому вводим значение «>08.03.2017».

Перемещаемся к аргументу «Диапазон условия2». Тут нужно внести те же координаты, которые были записаны в поле «Диапазон условия1». Делаем это тем же способом, то есть, путем выделения строчки с датами.

Устанавливаем курсор в поле «Условие2». Вторым условием является то, что дни, за которые будет суммироваться выручка, должны быть не позже 13 марта. Поэтому записываем следующее выражение: «<14.03.2017».

Переходим в поле «Диапазон условия2». В данном случае нам нужно выделить тот самый массив, адрес которого был внесен, как массив суммирования.

После того, как адрес указанного массива отобразился в окне, переходим к полю «Условие3». Учитывая, что в суммировании будут принимать участие только значения, величина которых превышает 14000 рублей, вносим запись следующего характера: «>14000».

Условное форматирование

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

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

1. Выделяем весь массив элементов в таблице, в котором указана выручка торговых точек по дням.
2. Передвигаемся во вкладку «Главная». Клацаем по пиктограмме «Условное форматирование», размещенной в блоке «Стили» на ленте. Открывается список действий. Клацаем в нём по позиции «Создать правило…».
3. Активируется окошко генерации правила форматирования. В области выбора типа правила выделяем наименование «Форматировать только ячейки, которые содержат». В первом поле блока условий из списка возможных вариантов выбираем «Значение ячейки». В следующем поле выбираем позицию «Больше». В последнем — указываем само значение, больше которого требуется отформатировать элементы таблицы. У нас это 14000. Чтобы выбрать тип форматирования, клацаем по кнопке «Формат…».
4. Активируется окно форматирования. Передвигаемся во вкладку «Заливка». Из предложенных вариантов цветов заливки выбираем синий, щелкая по нему левой кнопкой мыши. После того, как выбранный цвет отобразился в области «Образец», клацаем по кнопке «OK».
5. Автоматически происходит возврат к окну генерации правила форматирования. В нём также в области «Образец» отображается синий цвет. Тут нам нужно произвести одно единственное действие: клацнуть по кнопке «OK».
6. После выполнения последнего действия, все ячейки выделенного массива, где содержится число большее, чем 14000, будут залиты синим цветом.

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

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

Что такое критерий в эксель

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

Самая простая функция СЧЁТЕСЛИ означает следующее:

=СЧЁТЕСЛИ(где нужно искать;что нужно найти)

СЧЁТЕСЛИ(диапазон;критерий)

Имя аргумента

диапазон (обязательный)

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

критерий (обязательный)

Число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать.

Например, критерий может быть выражен как 32, «>32», В4, «яблоки» или «32».

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

Примеры

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

Количество ячеек, содержащих текст «яблоки» в ячейках А2–А5. Результат — 2.

Количество ячеек, содержащих текст «персики» (значение ячейки A4) в ячейках А2–А5. Результат — 1.

Количество ячеек, содержащих текст «яблоки» (значение ячейки A2) и «апельсины» (значение ячейки A3) в ячейках А2–А5. Результат — 3. В этой формуле для указания нескольких критериев, по одному критерию на выражение, функция СЧЁТЕСЛИ используется дважды. Также можно использовать функцию СЧЁТЕСЛИМН.

Количество ячеек со значением больше 55 в ячейках В2–В5. Результат — 2.

Количество ячеек со значением, не равным 75, в ячейках В2–В5. Знак амперсанда (&) объединяет оператор сравнения «<>» (не равно) и значение в ячейке B4, в результате чего получается формула =СЧЁТЕСЛИ(B2:B5;»<>75″). Результат — 3.

Количество ячеек со значением, большим или равным 32 и меньшим или равным 85, в ячейках В2–В5. Результат — 1.

Количество ячеек, содержащих любой текст, в ячейках А2–А5. Подстановочный знак «*» обозначает любое количество любых символов. Результат — 4.

Количество ячеек, строка в которых содержит ровно 7 знаков и заканчивается буквами «ки», в диапазоне A2–A5. Подставочный знак «?» обозначает отдельный символ. Результат — 2.

Распространенные неполадки

Возможная причина

Для длинных строк возвращается неправильное значение.

Функция СЧЁТЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 символов.

Для работы с такими строками используйте функцию СЦЕПИТЬ или оператор сцепления &. Пример: =СЧЁТЕСЛИ(A2:A5;»длинная строка»&»еще одна длинная строка»).

Функция должна вернуть значение, но ничего не возвращает.

Аргумент критерий должен быть заключен в кавычки.

Формула СЧЁТЕСЛИ получает #VALUE! ошибка при ссылке на другой лист.

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

Рекомендации

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

Критерий не чувствителен к регистру. Например, строкам «яблоки» и «ЯБЛОКИ» будут соответствовать одни и те же ячейки.

Использование подстановочных знаков

В критериях можно использовать подстановочные знаки — вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (

) перед искомым символом.

Например, =СЧЁТЕСЛИ(A2:A5;»яблок?») возвращает все вхождения слова «яблок» с любой буквой в конце.

Убедитесь, что данные не содержат ошибочных символов.

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

Для удобства используйте именованные диапазоны.

ФУНКЦИЯ СЧЁТЕСЛИ поддерживает именованные диапазоны в формуле (например, =COUNTIF(fruit;»>=32″)-COUNTIF(fruit;»>85″). Именованный диапазон может располагаться на текущем листе, другом листе этой же книги или листе другой книги. Чтобы одна книга могла ссылаться на другую, они обе должны быть открыты.

Примечание: С помощью функции СЧЁТЕСЛИ нельзя подсчитать количество ячеек с определенным фоном или цветом шрифта. Однако Excel поддерживает пользовательские функции, в которых используются операции VBA (Visual Basic для приложений) над ячейками, выполняемые в зависимости от фона или цвета шрифта. Вот пример подсчета количества ячеек определенного цвета с использованием VBA.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Создание сложных формул с помощью встроенных логических функций Excel

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

Ниже представлен список операторов сравнения Excel:

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

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

Результатом логического выражения является логическое значение ИСТИНА (1) или логическое значение ЛОЖЬ (0).

Функция ЕСЛИ

Функция ЕСЛИ – это функция, которая позволяет вывести в ячейку одно значение, если заданное пользователем условие выполняется и другое – если условие не выполняется.

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

Рассчитать стоимость товара с учетом того, что если количество превысило 5 шт., то магазин дает скидку 10%. Исходные данные приведены на рис. 26.

Для того чтобы рассчитать стоимость товара с учетом скидки необходимо:

Переместить курсор в ячейку D2. С помощью мастера функций выбрать из категории Логические функцию ЕСЛИ (рис. 27), а затем щелкнуть на кнопке ОК.

Диалоговое окно Аргументы функции (рис. 28) содержит три поля ввода. В поле Лог_выражение необходимо ввести условие, которое определяет, превышает ли проданное кол-во товара 5 шт., следовательно введем в это поле С2>5. В поле Значение_если_истина необходимо ввести формулу, которая вычисляет стоимость товара с учетом скидки, тогда введем в это поле B2*C2-B2*C2*0,1. В поле Значение_если_ложь необходимо ввести формулу, которая вычисляет стоимость товара без учета скидки (условие С2>5 — Ложь), в тогда введем в это поле B2*C2 (см. рис. 29). Теперь щелкнем на кнопке OK.

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

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

На рис. 31 приведена таблица с данными. Необходимо:

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

Определить, на какую сумму продано товаров каждого вида с учетом скидки (сумма с учетом скидки = сумма — сумма * скидка). Скидка начисляется по следующему принципу: если продано товара на сумму более 2500 грн., то скидка составит 5%, если продано товара на сумму менее 1100 грн., то скидка составит 0%, в остальных случаях скидка составит 2%.

Для того чтобы выполнить первый пункт задания необходимо в ячейку D2 ввести формулу =C2*B2.

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

Переместим курсор в ячейку E2 и с помощью мастера функций введем следующую формулу (рис. 33 – 34). Результаты решения приведены на рис. 35.

Рис. 35

Функции ИЛИ, И

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

ИЛИ(логическое_значение1;логическое_значение2; . )

И(логическое_значение1; логическое_значение2; . )

Аргументы функций И, ИЛИ могут быть логическими выражениями, массивами или ссылками на ячейки, содержащие логические значения.

Функция ИЛИ возвращает логическое значение ИСТИНА, если хотя бы одно из логических выражений истинно, а функция И возвращает логическое значение ИСТИНА, только если все логические выражения истинны.

Предположим, что нужно вывести сообщение «Светофор», если содержимое ячейки B4 или «красный», или «зеленый», или «желтый». Если же она содержит любую другую информацию, то необходимо вывести сообщение «Это не светофор. ».

ЕСЛИ(ИЛИ(B4=«зеленый»;B4=«красный»;B4=«желтый»);»Светофор»; «Это не светофор. » )

Предположим, что нужно вывести на экран содержимое ячейки B4, если она содержит число строго между 1 и 100 (1<=B4<=100), и сообщение «Значение вне интервала» в противном случае.

ЕСЛИ(И(B4>=1; B4<=100); B4; «Значение вне интервала»)

Есть список дат, необходимо определить, является ли выходным днем введенная дата. Решения задачи на рис. 36

Формулу в ячейке В2 можно вводить с клавиатуры (необходимо помнить синтаксис используемых вложенных функций) или с помощью мастера функции. Рассмотрим, как вводить формулу с помощью мастера функций:

Переместим курсор в ячейку В2, щелкнем на кнопке вставка функции и в категории логические выберем функцию Если.

Щелкнем в поле Лог_выражение функции Если, а затем щелкнем на стрелке вниз в строке формул и выберем Другие функции (см. рис. 37). Теперь в категории Логические можно выбрать функцию ИЛИ.

Щелкнем в поле Логическое_выражение_1 функции ИЛИ, а затем щелкнем на стрелке вниз в строке формул и выберем Другие функции. В категории Дата и время выберем функцию ДЕНЬНЕД и в поле Дата_в_числом_формате введем А2, а в поле Тип введем 2.

В строке формул щелкнем мышкой внутри слова ИЛИ и таким образом, сможем вернуться в диалоговое окно функции ИЛИ, для того чтобы закончить ввод условия в первое поле этой функции (набираем = 6).

Щелкнем в поле Логическое_выражение_2 функции ИЛИ и повторим действия пункта 3 и 4, чтобы ввести второе условие ДЕНЬНЕД(А2;2)=7.

В строке формул щелкнем мышкой внутри слова ЕСЛИ и таким образом, сможем вернуться в диалоговое окно функции ЕСЛИ, для того чтобы закончить ввод формулы (в поле Значение_если_истина вводим Выходной день, в поле Значение_если_ложь вводим пробел).

Информация о результатах первого модульного контроля и посещаемости студентами занятий выведена в таблице см. рис. 38. Необходимо вывести следующую информацию: Если средний бал студента меньше или равен 3,5 и он пропустил более 49 часов занятий по неуважительной причине, то необходимо вызвать родителей в деканат; если средний бал студента больше или 4,5 и он пропустил не более 10 часов занятий по неуважительной причине, то необходимо отправить родителям письмо благодарность. Решение задачи представлено на рис. 39.

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

Эти функции надо искать не в категории Логические, а в категориях Статистические и Математические, соответственно (или в полном алфавитном перечне).

СЧЁТЕСЛИ — подсчитывает количество ячеек в диапазоне, удовлетворяющих заданному условию, а СУММЕСЛИ — суммирует значения ячеек, удовлетворяющих заданному условию. Функция СУММЕСЛИ используется в тех случаях, когда необходимо суммировать не весь диапазон, а только ячейки, отвечающие некоторым условиям (критериям).

СЧЁТЕСЛИ (диапазон; критерий)

диапазон — диапазон, в котором нужно подсчитать ячейки.

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

Функция СЧЁТЕСЛИ работает следующим образом: вычисляется количество ячеек диапазона, значение которых удовлетворяют условию (критерию).

В задаче, рассмотренной в примере 10 необходимо определить количество студентов, у которых средний балл >=4.5.

Тогда в ячейку С15 необходимо ввести формулу: =СЧЁТЕСЛИ(B11:B13;»>=4,5″). Результат приведен на рис. 40

СУММЕСЛИ (диапазон; критерий; диапазон_суммирования)

диапазон — диапазон ячеек проверяемых на критерий (условие).

критерий — критерий (условие) в форме числа, выражения или текста, определяющего суммируемые ячейки.

диапазон_суммирования — фактические ячейки для суммирования.

Функция СУММЕСЛИ работает следующим образом: ячейки из «диапазон_суммирования» суммируются, только если соответствующие им ячейки в аргументе «диапазон» удовлетворяют условию (критерию). В тех случаях, когда диапазон вычисляемых ячеек (где проверяется условие) и диапазон фактических ячеек для суммирования совпадают, аргумент «диапазон_суммирования» можно не указывать.

В таблице на рис. 41 приведены объемы партий товара, полученные магазином. Необходимо просуммировать только объемы тех партий, значения которых превышают 20. Решение на рис. 42