Excel счетесли двум столбцам

Функция ЕСЛИ в Excel (общие сведения)

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

1. Проверить всю колонку А на предмет наличия четных чисел.
2. Если четное число обнаружено, сложить такие-то значения.
3. Если четное число не обнаружено, то выдать надпись «не обнаружено».
4. Проверить получившееся число, является ли оно четным. 
5. Если да, то сложить его со всеми четными числами, отобранными в пункте 1.

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

Синтаксис функции ЕСЛИ с одним условием

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

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

Давайте рассмотрим синтаксис более подробно:

1. Логическое выражение. Это непосредственно условие, соответствие или несоответствие которому проверяет Excel. Проверяться могут как числовые, так и текстовая информация.
2. Значение_если_истина. Результат, который отобразится в ячейке в случае, если проверяемые данные соответствуют заданному критерию.
3. Значение_если_ложь. Результат, который выводится в ячейку, если проверяемые данные не соответствуют условию.

Вот пример для наглядности.

1

Здесь функция осуществляет сравнение ячейки А1 с числом 20. Это первый пункт синтаксиса. Если содержимое оказывается больше этого значения, в ячейку, где формула была прописана, выводится значение «больше 20». Если же этому условию ситуация не соответствует – «меньше или равно 20».

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

2

Поскольку нам нужно проверить текст, а не число, в первом аргументе указывается B2=”зач.”.

Синтаксис функции ЕСЛИ с несколькими условиями

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

Чтобы было более наглядно, вот синтаксис.

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

В этом случае функция проверит сразу два критерия. Если первое условие оказывается истинным возвращается значение, полученное в результате совершения операции в первом аргументе. Если нет – осуществляется проверка на соответствие второму критерию.

Вот пример.

3

А с помощью такой формулы (показана на скриншоте ниже) можно сделать анализ успеваемости каждого студента.

4

Как видим, здесь было добавлено еще одно условие, но принцип не изменился. Так можно сразу несколько критериев проверять.

Часто задаваемые вопросы и проблемы.

Я надеюсь, что эти примеры помогли вам почувствовать функцию Excel СЧЕТЕСЛИ. Если вы попробовали какую-либо из приведенных выше формул в своих данных и не смогли заставить их работать или у вас возникла проблема, взгляните на следующие 5 наиболее распространенных проблем. Есть большая вероятность, что вы найдете там ответ или же полезный совет.

1. Возможен ли подсчет в несмежном диапазоне клеток?

Вопрос: Как я могу использовать СЧЕТЕСЛИ для несмежного диапазона или ячеек?

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

Неправильно: =СЧЕТЕСЛИ(A2;B3;C4;»>0″)

Правильно: = СЧЕТЕСЛИ (A2;»>0″) + СЧЕТЕСЛИ (B3;»>0″) + СЧЕТЕСЛИ (C4;»>0″)

Альтернативный способ — использовать функцию ДВССЫЛ (INDIRECT) для создания массива из несмежных клеток. Например, оба приведенных ниже варианта дают одинаковый результат, который вы видите на картинке:

=СУММ(СЧЁТЕСЛИ(ДВССЫЛ({«B2:B11″;»D2:D11″});»=0»))

Или же

=СЧЕТЕСЛИ($B2:$B11;0) + СЧЕТЕСЛИ($D2:$D11;0)

1. Амперсанд и кавычки в формулах СЧЕТЕСЛИ

Вопрос: когда мне нужно использовать амперсанд?

Ответ: Это, пожалуй, самая сложная часть функции СЧЕТЕСЛИ, что лично меня тоже смущает. Хотя, если вы подумаете об этом, вы увидите — амперсанд и кавычки необходимы для построения текстовой строки для аргумента.

Итак, вы можете придерживаться этих правил:

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

= СЧЕТЕСЛИ(A1:A10;10) или = СЧЕТЕСЛИ(A1:A10;C1)

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

= СЧЕТЕСЛИ(A2:A10;»яблоко») или = СЧЕТЕСЛИ(A2:A10;»*») или = СЧЕТЕСЛИ(A2:A10;»>5″)

Если ваши критерии — это выражение со ссылкой или же какая-то другая функция Excel, вы должны использовать кавычки («») для начала текстовой строки и амперсанд (&) для конкатенации (объединения) и завершения строки. Например:

= СЧЕТЕСЛИ(A2:A10;»>»&D2) или = СЧЕТЕСЛИ(A2:A10;»<=»&СЕГОДНЯ())

Если вы сомневаетесь, нужен ли амперсанд или нет, попробуйте оба способа. В большинстве случаев амперсанд работает просто отлично.

Например, = СЧЕТЕСЛИ(C2: C8;»<=5″) и = СЧЕТЕСЛИ(C2: C8;»<=»&5) работают одинаково хорошо.

1. Как сосчитать ячейки по цвету?

Вопрос: Как подсчитать клетки по цвету заливки или шрифта, а не по значениям?

Ответ: К сожалению, синтаксис функции не позволяет использовать форматы в качестве условия. Единственный возможный способ суммирования ячеек на основе их цвета — использование макроса или, точнее, пользовательской функции Excel VBA.

1. Ошибка #ИМЯ?

Проблема: все время получаю ошибку #ИМЯ? Как я могу это исправить?

Ответ: Скорее всего, вы указали неверный диапазон. Пожалуйста, проверьте пункт 1 выше.

1. Формула не работает

Проблема: моя формула не работает! Что я сделал не так?

Ответ: Если вы написали формулу, которая на первый взгляд верна, но она не работает или дает неправильный результат, начните с проверки наиболее очевидных вещей, таких как диапазон, условия, ссылки, использование амперсанда и кавычек.

Будьте очень осторожны с использованием пробелов. При создании одной из формул для этой статьи я был уже готов рвать волосы, потому что правильная конструкция (я точно знал, что это правильно!) не срабатывала. Как оказалось, проблема была на самом виду… Например, посмотрите на это: =СЧЁТЕСЛИ(A4:A13;» Лимонад»). На первый взгляд, нет ничего плохого, кроме дополнительного пробела после открывающей кавычки. Программа отлично проглотит всё без сообщения об ошибке, предупреждения или каких-либо других указаний. Но если вы действительно хотите посчитать товары, содержащие слово «Лимонад» и начальный пробел, то будете очень разочарованы….

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

И это все на сегодня. В следующей статье мы рассмотрим несколько способов подсчитывания ячеек в Excel с несколькими условиями.

Ещё примеры расчета суммы:

5
1
голос

Рейтинг статьи

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

В этом курсе:

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

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

Для удобства также приводим ссылку на оригинал (на английском языке).

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

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

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

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

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

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

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

Функция СЧЕТЕСЛИ в 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, так как найдет «стол» и «стул».

Как перевернуть текст в excel

= Мир MS Excel/Статьи об Excel

• 1
• 2
• 3

Приёмы работы с книгами, листами, диапазонами, ячейками

Приёмы работы с формулами

Настройки Excel

Инструменты Excel

Интеграция Excel с другими приложениями

Форматирование

Выпадающие списки

Примечания

Сводные таблицы

Гиперссылки

Excel и интернет

Excel для Windows и Excel для Mac OS

Инструмент камера возвращает динамический снимок указанного диапазона. Динамический — это значит он изменяется при изменении диапазона, который мы сфотографировали. По умолчанию камера недоступна во всех версиях Excel. Для того что бы ей воспользоваться необходимо сначала вывести её на панель меню (в Excel версий до 2007) или на панель быстрого доступа (в Excel версий после 2003). Как это делается?

Для Excel версий до 2007:

Меню Сервис — пункт Настройка — вкладка Команды — категории Сервис — команды Камера — левой кнопкой мыши перетаскиваем иконку камеры на удобное место в меню.

Для Excel версий после 2003:

Кликаем правой кнопкой мыши по панели быстрого доступа — Настройка панели быстрого доступа — Выбрать команды из Все команды — Камера — Добавить — ОК.

Теперь, когда инструмент камера добавлен в меню/на панель быстрого доступа можно приступить к решению вопроса, озвученного в названии этой статьи: Как перевернуть текст в ячейке?Как я уже писал выше — это невозможно. Формулы и функции не работают с форматированием, ориентацию текста в диалоговом окне Формат ячеек можно изменять только в пределах от 90 до -90 градусов, в VBA нет функций, позволяющих это сделать. Остаётся только перевернуть монитор

Выделяем ячейку, которую надо скопировать. Левой кнопкой мыши кликаем на иконку инструмента камера. Окантовка выделенной ячейки станет активной (как при обычном копировании). Левой кнопкой мыши указываем место на листе, куда необходимо вставить снимок и он автоматически вставляется. Всё, мы получили динамический снимок ячейки. Остаётся только перевернуть его на 180 градусов и готово. На рисунке, в ячейке В2, Вы видите снимок ячейки А1:

Все изменения производимые в ячейке А1 будут отображаться в В2.

ПРИМЕЧАНИЕ: Можно так же «фотографировать» и один диапазон. Одновременно несколько ячеек/диапазонов или несмежные диапазоны инструмент камера не обрабатывает, о чём появится соответствующее сообщение, если Вы попытаетесь применить камеру к ним. С получившимся снимком можно работать как с обычным рисунком, внедрённым на лист, то есть, изменять его размеры, свойства, форму, устанавливать прозрачность, рамки и прочее.

Замечания

Подсчитываются аргументы, которые являются числами, датами или текстовыми представлениями чисел (например, число, заключенное в кавычки, например «1″).

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

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

Если аргумент является массивом или ссылкой, учитываются только числа. Пустые ячейки, логические значения, текст и значения ошибок в массиве или ссылке игнорируются.

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

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

Вариант 2: Счет числовых условий

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

1. После объявления «СЧЁТЕСЛИ» в круглых скобках задайте диапазон чисел «A1:A25», где вместо указанных ячеек подставьте необходимые.

Сразу за диапазоном без пробела добавьте знак «;», который объявляет его закрытие.

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

Примеры подсчёта с единственным условием

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

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

1. Выберите любую свободную ячейку для результата (например, H10), затем щелкните fx в строке формул Excel, чтобы ввести описанную функцию.

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

1. В раскрывающемся списке «Категория» выберите один из перечисленных списков и нажмите ОК.

1. В новом списке выберите нашу функцию и нажмите ОК.

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

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

1. В новом компактном окне выделите интересующий диапазон (полный столбец B) и щелкните красную стрелку вниз, чтобы вернуться в предыдущее окно.

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

В поле «Условие 1» введите соответствующее значение в том формате, в котором оно отображается в таблице (обычное число), и нажмите «ОК»

(Обратите внимание, что в окне аргументов появилась третья строка для второго условия. Она нам пока не нужна, поэтому мы не будем обращать на нее внимание.)

После ввода правильного условия найденное значение (2) сразу появляется в том же окне темы. После нажатия на ОК вы вернетесь в главное рабочее окно (в ячейке H10).

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

Следующий пример — критерий неравенства. Реализовано это так, как показано на скриншоте

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

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

​Если условий больше одного​ для результата, жмем​

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

​ применения формул с​4. Формула массива =СУММ((A2:A13=D2)*(B2:B13>=E2))​). Результат очевиден: 2.​ смотрите в статье​ Получилось три слова​ в столбце B.​ позволяет подсчитать количество​ дат, принадлежащих определенному​Примечание​ Нюанс состоит в​​ столбец очень простая:​​ (например, нужно найти​​ кнопку​​* (A1:A10=»андрей») *​: Конечно​1​​Марина​​ помощью стандартного Автофильтра.​ эквивалентна вышеупомянутой формуле​​ Для наглядности, строки​​ «Как объединить данные​

​ «да».​​ В ячейке В7​​ неповторяющихся значений формулой​

• ​ месяцу, то нужно​​>=10​ том, что для​=(A2=»Копейка»)*(B2=»Григорьев»)​​ сумму всех заказов​​fx​ (B$1:B$10)​Код =СУММПРОИЗВ(ЕЧИСЛО(НАЙТИ(«андрей»;A1:A10))*(B$1:B$10​Код =СЧЁТЕСЛИ(Лист2!C3:C31;»Андрtй») 2​
• ​: Темы с одноименным​​Установите автофильтр к столбцу​ =СУММПРОИЗВ((A2:A13=D2)*(B2:B13>=E2)) Единственное, после​ в таблице, удовлетворяющие​ теста в ячейках​Если нужно посчитать​ пишем формулу.​ =СУММПРОИЗВ(—(СЧЁТЕСЛИ(A6:A14;A6:A14)=1)). ​ создать дополнительный столбец​=СЧЁТЕСЛИ($A$2:$A$12; «>=10»)​ работы этой функции​Логические равенства в скобках​ Григорьева для «Копейки»),​в строке формул,​на самом деле​Buckminster​Код =СЧЁТЕСЛИ(Лист2!I3:I31;» а​ названием и смыслом​ Количество ящиков на​​ ее ввода нужно​​ критериям, выделяются Условным​ в Excel» здесь.​ процентное отношение ответов​На закладке «Формулы»​Формула =СЧЁТЕСЛИ(A6:A14;»А6:А15. В этом можно​ для вычисления месяца,​​4​​ необходимо создать на​ дают значения ИСТИНА​
• ​ то функция​​ находим функцию​ было (дополнительные зелёные​: можно стандартной функцией:​ как их объеденить??​ появляются на форуме​

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

​ складе, выделив заголовок​ вместо​ форматированием с правилом =И($A2=$D$2;$B2>=$E$2)​В Excel много​ «да» по отношению​​ в разделе «Библиотека​​ убедиться, выделив формулу​ затем записать формулу =СЧЁТЕСЛИ(B20:B30;2)​Подсчитывает количество ячеек, содержащих​ листе специальный диапазон​ или ЛОЖЬ, что​СУММЕСЛИ (SUMIF)​СУММЕСЛИ​​ скобки мои):​​Buckminster​мне нужно посчитать​ еженедельно и даже​ столбца и нажав​ENTER​Подсчет можно реализовать множеством​​ разных способов провести​​ ко всем ответам,​ функций» нажимаем кнопку​ в Строке формул​

​Обычно, в качестве аргумента​ числа равных или​ критериев — ячейки, содержащие​ для Excel равносильно​не поможет, т.к.​​в списке:​​=СУММПРОИЗВ​​: Сергей, да Вы​​ всех Андреев и​ чаще. Пользуйтесь поиском​CTRL+SHIFT+L. ​

​нажать​ формул, приведем несколько:​ анализ данных в​ то формула будет​ «Другие функции» и,​ и нажав клавишу​критерий​ более 10. Критерий​

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

​ условия отбора — и​ 1 и 0.​ не умеет проверять​Жмем​(​ просто волшебник. Я​ +, что бы​ по форуму.​Выберите числовой фильтр Между.​CTRL+SHIFT+ENTER​1. =СЧЁТЕСЛИМН(A2:A13;D2;B2:B13;»>=»&E2) Это решение является​ таблице. О разных​ сложнее. Смотрите формулу​

​ в разделе «Статистические»,​

​ F9. Значения совпадут​у функции СЧЁТЕСЛИ()​ указан в формуле​ указать затем этот​ Таким образом, поскольку​ больше одного критерия.​ОК​—​ вроде все эти​ у них был​Спасибо, обязательно воспользуюсь​Введите критерии​5. Формула массива =СЧЁТ(ЕСЛИ((A2:A13=D2)*(B2:B13>=E2);B2:B13)) представляет​ самым простым и​ способах анализа, смотрите​ в строке 22​

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

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

​ рейтинг меньше 17​

​ :-)​Убедитесь, что результат такой​ еще один вариант​ понятным.​​ в статье «Анализ​ на изображении. Здесь​​ Заполняем диалоговое окно​ в столбце​ значение. Например, =СЧЁТЕСЛИ(H2:H11;I2).​=СЧЁТЕСЛИ( $A$2:$A$12;C3)​ аргумент:​ выражения, единица в​ версии Excel 2007​Диапазон​КОДСИМВ(A1:A10) =​ чтобы вот так​Andrey770​Мне просто было​ же как в​

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

​ многокритериального подсчета значений.​​2. =СУММПРОИЗВ(—(A2:A13=D2);—(B2:B13>=E2)) Это решение сложнее,​​ данных в Excel»​​ идет расчет только​​ так.​В​ Если в качестве​8​=БДСУММ(A1:D26;D1;F1:G2)​ конечном счете получится​ в набор функций​- это те​(​ их использовать в​: А офис какой?​ интересно, в чем​

​6. Формула =БСЧЁТА(A1:B13;A1;D14:E15) требует​

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

Сначала рассмотрим аргументы функции:

• Диапазон – группа значений для анализа и подсчета (обязательный).
• Критерий – условие, по которому нужно подсчитать ячейки (обязательный).

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

В качестве критерия может быть ссылка, число, текстовая строка, выражение. Функция СЧЕТЕСЛИ работает только с одним условием (по умолчанию). Но можно ее «заставить» проанализировать 2 критерия одновременно.

Рекомендации для правильной работы функции:

• Если функция СЧЕТЕСЛИ ссылается на диапазон в другой книге, то необходимо, чтобы эта книга была открыта.
• Аргумент «Критерий» нужно заключать в кавычки (кроме ссылок).
• Функция не учитывает регистр текстовых значений.
• При формулировании условия подсчета можно использовать подстановочные знаки. «?» — любой символ. «*» — любая последовательность символов. Чтобы формула искала непосредственно эти знаки, ставим перед ними знак тильды (

).

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



Особенности использования функции СЧЁТЕСЛИ в Excel

Функция имеет следующую синтаксическую запись:

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

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

1. При подсчете числа вхождений в диапазон в соответствии с двумя различными условиями, диапазон ячеек можно рассматривать как множество, содержащее два и более непересекающихся подмножеств. Например, в таблице «Мебель» необходимо найти количество столов и стульев. Для вычислений используем выражение =СЧЁТЕСЛИ(B3:B200;»*стол*»)+СЧЁТЕСЛИ(B3:B200;»*стул*»).
2. Если в качестве критерия указана текстовая строка, следует учитывать, что регистр символов не имеет значения. Например, функция СЧЁТЕСЛИ(A1:A2;»Петров») вернет значение 2, если в ячейках A1 и A2 записаны строки «петров» и «Петров» соответственно.
3. Если в качестве аргумента критерий передана ссылка на пустую ячейку или пустая строка «», результат вычисления для любого диапазона ячеек будет числовое значение 0 (нуль).
4. Функция может быть использована в качестве формулы массива, если требуется выполнить расчет числа ячеек с данными, удовлетворяющим сразу нескольким критериям. Данная особенность будет рассмотрена в одном из примеров.
5. Рассматриваемая функция может быть использована для определения количества совпадений как по одному, так и сразу по нескольким критериям поиска. В последнем случае используют две и более функции СЧЁТЕСЛИ, возвращаемые результаты которых складывают или вычитают. Например, в ячейках A1:A10 хранится последовательность значений от 1 до 10. Для расчета количества ячеек с числами больше 3 и менее 8 необходимо выполнить следующие действия:

• записать первую функцию СЧЁТЕСЛИ с критерием «>3»;
• записать вторую функцию с критерием «>=8»;
• определить разницу между возвращаемыми значениями =СЧЁТЕСЛИ(A1:10;»>3″)-СЧЁТЕСЛИ(A1:A10;»>=8″). То есть, вычесть из множества (3;+∞) подмножество [8;+∞).

Функция СЧЕТЕСЛИ в 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, так как найдет «стол» и «стул».

СУММЕСЛИМН

Функция СУММЕСЛИМН позволяет суммировать значения по определенным условиям. Условий может быть несколько. В Excel также есть функция СУММЕСЛИ, которая позволяет суммировать по одному критерию. Призываю вас использовать более универсальную формулу.

— Диапазон суммирования — область листа Эксель, из которой мы суммируем данные- Диапазон условия 1 — Диапазон ячеек, которые мы проверяем на соответствие условию- Условие 1 — Условие, которое проверяется на соответствие в Диапазоне 1.Обратите внимание, что диапазонов условий и соответственно условий может быть столько, сколько вам нужно. Для примера выше мы хотим получит выручку, которую принес нам Петров в городе Москва

Формула имеет вид СУММЕСЛИМН(C2:C13;A2:A13;E2;B2:B13;F2), где C2:C13 — диапазон со значениями выручки, которые необходимо просуммировать; А2:А13 — диапазон с фамилиями, которые мы будем проверять; Е2 — ссылка на конкретную фамилию; B2:B13 — ссылка на диапазон с городами; F2 — ссылка на конкретный город

Для примера выше мы хотим получит выручку, которую принес нам Петров в городе Москва. Формула имеет вид СУММЕСЛИМН(C2:C13;A2:A13;E2;B2:B13;F2), где C2:C13 — диапазон со значениями выручки, которые необходимо просуммировать; А2:А13 — диапазон с фамилиями, которые мы будем проверять; Е2 — ссылка на конкретную фамилию; B2:B13 — ссылка на диапазон с городами; F2 — ссылка на конкретный город.

Более подробно о функциях СУММЕСЛИМН и СУММЕСЛИ рассказано в статье «СУММЕСЛИ и СУММЕСЛИМН в Excel».

Как использовать СЧЁТЕСЛИМН в Excel

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

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

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

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

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

Мы расширили пример из предыдущего раздела, и теперь, помимо цвета яблок, есть столбец с описанием их размера. Конечная цель в этом примере – подсчитать количество больших красных яблок.

1. Выберите ячейку, в которой вы хотите отобразить результаты. (В этом примере мы собираемся отобразить количество больших красных яблок в ячейке E3 .)
2. Перейдите к строке формул и введите формулу ниже:

   При этом формула проверяет ячейки от A3 до A11 на состояние «Красный» . Клетки, прошедшие тест, затем дополнительно тестируются в диапазоне от B3 до B11 на предмет состояния «Большой» .

3. Нажмите Enter .
4. Теперь Excel посчитает количество больших красных яблок.

Обратите внимание, как в формуле подсчитываются ячейки, имеющие как красный, так и большой атрибут. Формула берет ячейки от A3 до A11 и проверяет их на красный цвет

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

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

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

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

3. Нажмите Enter .
4. Excel теперь подсчитает и отобразит количество больших или красных яблок.

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

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

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

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

Рассмотрим главные аргументы более подробно:

1. Диапазон суммирования. Ячейки, которые будут суммироваться.
2. Диапазон условия 1 – диапазон, который будет проверяться на предмет соответствия определенному критерию. 
3. Условие 1 – непосредственно условие.
4. Диапазон условия 2 – второй диапазон, который будет проверяться на предмет соответствия критерию.
5. Условие 2 – второе условие.

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

13