Как посчитать количество истин в экселе
Здравствуйте!
Сразу скажу, что в Excel я слабовато разбираюсь.
Итак, есть число, из которого вычитаются другие числа из диапазона и проверяется, >=0 или нет. В зависимости от результата ячейки из диапазона закрашиваются определённым цветом. Выглядит это примерно так (прикрепил пример, там наглядней):
число-СУММ(диапазон чисел)>=0 (зелёный цвет)
число-СУММ(диапазон чисел)<0 (жёлтый цвет)
Но надо добавить ещё одно правило: если правило "число-СУММ(диапазон чисел)<0 (жёлтый цвет)" повторилось 5 раз (дало 5 истин), то ячейка закрашивается в красный. Т.е. в примере после 5-ти жёлтых ячеек должны идти красные. Как такое правило сделать?
Здравствуйте!
Сразу скажу, что в Excel я слабовато разбираюсь.
Итак, есть число, из которого вычитаются другие числа из диапазона и проверяется, >=0 или нет. В зависимости от результата ячейки из диапазона закрашиваются определённым цветом. Выглядит это примерно так (прикрепил пример, там наглядней):
число-СУММ(диапазон чисел)>=0 (зелёный цвет)
число-СУММ(диапазон чисел)<0 (жёлтый цвет)
Но надо добавить ещё одно правило: если правило "число-СУММ(диапазон чисел)<0 (жёлтый цвет)" повторилось 5 раз (дало 5 истин), то ячейка закрашивается в красный. Т.е. в примере после 5-ти жёлтых ячеек должны идти красные. Как такое правило сделать? FrameG2
число-СУММ(диапазон чисел)>=0 (зелёный цвет)
число-СУММ(диапазон чисел)<0 (жёлтый цвет)
Но надо добавить ещё одно правило: если правило "число-СУММ(диапазон чисел)<0 (жёлтый цвет)" повторилось 5 раз (дало 5 истин), то ячейка закрашивается в красный. Т.е. в примере после 5-ти жёлтых ячеек должны идти красные. Как такое правило сделать? Автор - FrameG2
Дата добавления - 28.02.2016 в 17:31
Саня, тех, которых >0, не обязательно 4
понял
Саня, тех, которых >0, не обязательно 4
понял vikttur
Саня, тех, которых >0, не обязательно 4
понял Автор - vikttur
Дата добавления - 28.02.2016 в 19:08
vikttur, Если в В2 вставить 340, то желтых получится 4 шт.
Формулу Александра Excel2003 не видит
vikttur, Если в В2 вставить 340, то желтых получится 4 шт.
Формулу Александра Excel2003 не видит AlexM
vikttur, Если в В2 вставить 340, то желтых получится 4 шт.
Формулу Александра Excel2003 не видит Автор - AlexM
Дата добавления - 28.02.2016 в 19:17
Не смертельно - вместо "<=" оставить "<" Автор - vikttur
Дата добавления - 28.02.2016 в 19:30 А можно вас попросить немного модифицировать решение?
Правило, которое закрашивает в красный цвет, смещая вправо на 5, должно теперь учитывать символ "-". Т.е. от начала жёлтых ячеек смещать вправо на 5 + на количество "-" в жёлтых ячейках.
На обновлённом примере, который я прикрепил, в 3-ей строке в жёлтых ячейках присутствует 3 символа "-", следовательно, красные ячейки должны сместиться не на 5, а на 5+3=8 (т.е. ячейки с числами 50, 60, 40 должны остаться жёлтыми, а все, что правее - красными). Поможете с решением? А можно вас попросить немного модифицировать решение?
Правило, которое закрашивает в красный цвет, смещая вправо на 5, должно теперь учитывать символ "-". Т.е. от начала жёлтых ячеек смещать вправо на 5 + на количество "-" в жёлтых ячейках.
На обновлённом примере, который я прикрепил, в 3-ей строке в жёлтых ячейках присутствует 3 символа "-", следовательно, красные ячейки должны сместиться не на 5, а на 5+3=8 (т.е. ячейки с числами 50, 60, 40 должны остаться жёлтыми, а все, что правее - красными). Поможете с решением? FrameG2 Автор - Nic70y
Дата добавления - 29.02.2016 в 08:45 Nic70y, спасибо! Вроде работает! Довольно жёсткое решение :)
Правильно ли я понял, если мне понадобится
- изменить красное "смещение" с 5 до 7, я просто заменяю цифру 5 на 7 ?
- увеличить таблицу (область с условным форматированием) вправо, то придётся вручную менять O2 на новый столбец?
- добавить столбцы слева, то придётся вручную прибавлять такое же кол-во к последней цифре 2 ?
Можно ли последние 2 пункта как-то автоматизировать? :)
И что означает 1=2 в конце? Nic70y, спасибо! Вроде работает! Довольно жёсткое решение :)
Правильно ли я понял, если мне понадобится
- изменить красное "смещение" с 5 до 7, я просто заменяю цифру 5 на 7 ?
- увеличить таблицу (область с условным форматированием) вправо, то придётся вручную менять O2 на новый столбец?
- добавить столбцы слева, то придётся вручную прибавлять такое же кол-во к последней цифре 2 ?
Можно ли последние 2 пункта как-то автоматизировать? :)
И что означает 1=2 в конце? FrameG2 FrameG2, да все верно, задайте сразу максимальный диапазон FrameG2, да все верно, задайте сразу максимальный диапазон =ЛОЖЬ Автор - Nic70y
Дата добавления - 29.02.2016 в 14:17 Nic70y, да, сразу не обратил внимания, есть небольшой минус у решения. Правило должно учитывать "-", но оно учитывает ещё и пустые ячейки, хотя не желательно. Т.е. пустые жёлтые ячейки и ячейки с числами должны обрабатываться одинаково. Например, если жёлтая ячейка начинается с числа, а затем идут 4 пустые ячейки, то дальше сразу должны идти красные. Возможно ли подкорректировать решение? Nic70y, да, сразу не обратил внимания, есть небольшой минус у решения. Правило должно учитывать "-", но оно учитывает ещё и пустые ячейки, хотя не желательно. Т.е. пустые жёлтые ячейки и ячейки с числами должны обрабатываться одинаково. Например, если жёлтая ячейка начинается с числа, а затем идут 4 пустые ячейки, то дальше сразу должны идти красные. Возможно ли подкорректировать решение? FrameG2 FrameG2, а если пустые будут в зеленых, то откуда должны начаться желтые? FrameG2, а если пустые будут в зеленых, то откуда должны начаться желтые? Nic70y
Nic70y, а жёлтые вычисляются по формуле, которая в правилах указана. Первая ячейка, в которой "число-СУММ(диапазон чисел)<0", становится жёлтой. Зелёные и жёлтые работают как надо (можно проследить, как они работают, заменив ячейки с числами на пустые). А вот красные должны появиться от начала жёлтых через 5 ячеек, если в них только ячейки с числами или пустые ячейки (помогло решение, предложенное во 2-ом посте), или на 5+x, где x - кол-во ячеек с символом "-" в жёлтой области (модифицированное задание).
Пустые ячейки могут быть и в зелёных, и в жёлтых, и в красных. И ячейки с "-" тоже могут быть и в зелёных, и в жёлтых, и в красных. Пустые ячейки каждым правилом воспринимаются как 0. Ячейки с "-" воспринимаются как 0 "зелёным" и "желтым" правилами, а для "красного" это означает сместиться ещё правее (это и есть модифицированное задание).
Извиняюсь, что сразу не указал эти моменты.
Может быть какие-то обходные варианты можно(проще) сделать? Допустим, взять решение со второго поста и добавить 4-ое правило, которое поверх красных докрашивает жёлтые, если в жёлтых имеются ячейки с "-".
Nic70y, а жёлтые вычисляются по формуле, которая в правилах указана. Первая ячейка, в которой "число-СУММ(диапазон чисел)<0", становится жёлтой. Зелёные и жёлтые работают как надо (можно проследить, как они работают, заменив ячейки с числами на пустые). А вот красные должны появиться от начала жёлтых через 5 ячеек, если в них только ячейки с числами или пустые ячейки (помогло решение, предложенное во 2-ом посте), или на 5+x, где x - кол-во ячеек с символом "-" в жёлтой области (модифицированное задание).
Пустые ячейки могут быть и в зелёных, и в жёлтых, и в красных. И ячейки с "-" тоже могут быть и в зелёных, и в жёлтых, и в красных. Пустые ячейки каждым правилом воспринимаются как 0. Ячейки с "-" воспринимаются как 0 "зелёным" и "желтым" правилами, а для "красного" это означает сместиться ещё правее (это и есть модифицированное задание).
Извиняюсь, что сразу не указал эти моменты.
Может быть какие-то обходные варианты можно(проще) сделать? Допустим, взять решение со второго поста и добавить 4-ое правило, которое поверх красных докрашивает жёлтые, если в жёлтых имеются ячейки с "-". FrameG2
Может быть какие-то обходные варианты можно(проще) сделать? Допустим, взять решение со второго поста и добавить 4-ое правило, которое поверх красных докрашивает жёлтые, если в жёлтых имеются ячейки с "-". Автор - FrameG2
Дата добавления - 01.03.2016 в 12:03
[vba] Я формулами не умею, вот макрос нарисовался. Можно повесить на событие листа Change или Calculate, в зависимости от реальной задачи
[vba] [/vba] Автор - МВТ
Дата добавления - 01.03.2016 в 21:30 МВТ, спасибо, только с макросами я плохо дружу :)
Как повесить на событие листа я пока не разобрался (если подскажете, как это сделать, буду благодарен), разобрался только как вручную макрос запускать.
И есть вопросы по коду:
- почему-то он красные ячейки закрашивает через 4 жёлтых, а не через 5 (хотя в коде >=5). Если поставить >=6, то вроде нормально становится (странно).
- что надо изменить, чтобы код работал не на одну строку, а на несколько?
- как можно ограничить кол-во закрашиваемых столбцов? Т.е. чтобы закрашивало не до бесконечности, а до определённого предела? МВТ, спасибо, только с макросами я плохо дружу :)
Как повесить на событие листа я пока не разобрался (если подскажете, как это сделать, буду благодарен), разобрался только как вручную макрос запускать.
И есть вопросы по коду:
- почему-то он красные ячейки закрашивает через 4 жёлтых, а не через 5 (хотя в коде >=5). Если поставить >=6, то вроде нормально становится (странно).
- что надо изменить, чтобы код работал не на одну строку, а на несколько?
- как можно ограничить кол-во закрашиваемых столбцов? Т.е. чтобы закрашивало не до бесконечности, а до определённого предела? FrameG2 FrameG2, так приведите адекватный пример: каким числом ограничить количество колонок, откуда берутся данные (вводятся вручную или получаются в результате работы формул), какие строки будут задействованы в "процессе"? А пока это - "пойди туда, не знаю куда. " FrameG2, так приведите адекватный пример: каким числом ограничить количество колонок, откуда берутся данные (вводятся вручную или получаются в результате работы формул), какие строки будут задействованы в "процессе"? А пока это - "пойди туда, не знаю куда. " МВТ
МВТ, виноват!
Итак, опишу ещё раз и максимально подробно своё задание :)
Имеется файл, который я прикрепил. В нём простая таблица, которую условно можно разделить на 2 части: столбец B (B2 и ниже) и всё остальное (C2 и правее-ниже). Таблица в дальнейшем будет расширяться вправо-вниз. Ячейки, которые расположены правее столбца B, закрашиваются в 3 цвета (зелёный, жёлтый, красный) в зависимости от результата вычислений по формуле и от одного дополнительного условия. Красный цвет в прикреплённом примере работает не совсем правильно, т.к. не учитывает дополнительное условие (ячейки с символом "-"). Именно его нужно учесть! В остальном же всё верно.
На данный момент верно работают только зелёный и жёлтый цвета (реализовано через Условное Форматирование). Поэтому задание такое: реализовать правильную работу всех 3-х цветов.
В дальнейшем слева и сверху от таблицы будут добавлены столбцы и строки, а смещение 5 будет изменено (например, на 7), поэтому в дополнение к решению хотелось бы увидеть комментарий, что для этого нужно изменить в формуле, чтобы всё работало. Также было бы хорошо, чтобы строки закрашивались не бесконечно вправо, а до определённого предела, который можно отредактировать.
В идеале хотелось бы увидеть решение без макросов, но если это сделать проблематично, то пускай будут макросы.
В дополнение скажу, что каждое число в таблице может быть отредактировано, и тогда должен происходить пересчёт строки (это относится к макросам).
upd: если что, в столбце B могут быть числа от 0 и выше, а также пустые ячейки.
МВТ, виноват!
Итак, опишу ещё раз и максимально подробно своё задание :)
Имеется файл, который я прикрепил. В нём простая таблица, которую условно можно разделить на 2 части: столбец B (B2 и ниже) и всё остальное (C2 и правее-ниже). Таблица в дальнейшем будет расширяться вправо-вниз. Ячейки, которые расположены правее столбца B, закрашиваются в 3 цвета (зелёный, жёлтый, красный) в зависимости от результата вычислений по формуле и от одного дополнительного условия. Красный цвет в прикреплённом примере работает не совсем правильно, т.к. не учитывает дополнительное условие (ячейки с символом "-"). Именно его нужно учесть! В остальном же всё верно.
На данный момент верно работают только зелёный и жёлтый цвета (реализовано через Условное Форматирование). Поэтому задание такое: реализовать правильную работу всех 3-х цветов.
В дальнейшем слева и сверху от таблицы будут добавлены столбцы и строки, а смещение 5 будет изменено (например, на 7), поэтому в дополнение к решению хотелось бы увидеть комментарий, что для этого нужно изменить в формуле, чтобы всё работало. Также было бы хорошо, чтобы строки закрашивались не бесконечно вправо, а до определённого предела, который можно отредактировать.
В идеале хотелось бы увидеть решение без макросов, но если это сделать проблематично, то пускай будут макросы.
В дополнение скажу, что каждое число в таблице может быть отредактировано, и тогда должен происходить пересчёт строки (это относится к макросам).
upd: если что, в столбце B могут быть числа от 0 и выше, а также пустые ячейки. FrameG2
На данный момент верно работают только зелёный и жёлтый цвета (реализовано через Условное Форматирование). Поэтому задание такое: реализовать правильную работу всех 3-х цветов.
В дальнейшем слева и сверху от таблицы будут добавлены столбцы и строки, а смещение 5 будет изменено (например, на 7), поэтому в дополнение к решению хотелось бы увидеть комментарий, что для этого нужно изменить в формуле, чтобы всё работало. Также было бы хорошо, чтобы строки закрашивались не бесконечно вправо, а до определённого предела, который можно отредактировать.
В идеале хотелось бы увидеть решение без макросов, но если это сделать проблематично, то пускай будут макросы.
В дополнение скажу, что каждое число в таблице может быть отредактировано, и тогда должен происходить пересчёт строки (это относится к макросам).
upd: если что, в столбце B могут быть числа от 0 и выше, а также пустые ячейки. Автор - FrameG2
Дата добавления - 02.03.2016 в 23:39
Функция ИСТИНА в Excel предназначена для указания логического истинного значения и возвращает его в результате вычислений.
Функция ЛОЖЬ в Excel используется для указания логического ложного значения и возвращает его соответственно.
Функция НЕ в Excel возвращает противоположное указанному логическому значению. Например, запись =НЕ(ИСТИНА) вернет результат ЛОЖЬ.
Примеры использования логических функций ИСТИНА, ЛОЖЬ и НЕ в Excel
Пример 1. В таблице Excel хранятся телефонные номера различных организаций. Звонки на некоторые из них являются бесплатными (с кодом 8800), на остальные – платные по тарифу 1,5 руб/мин. Определить стоимость совершенных звонков.
В столбце «Бесплатный» отобразим логические значения ИСТИНА или ЛОЖЬ по следующему условию: является ли код номера телефона равным «8800»? Введем в ячейку C3 формулу:
- ЛЕВСИМВ(B3;4)="8800" – условие проверки равенства первых четырех символов строки указанному значению («8800»).
- Если условие выполняется, функция ИСТИНА() вернет истинное логическое значение;
- Если условие не выполнено, функция ЛОЖЬ() вернет ложное логическое значение.
Аналогично определим является ли звонок бесплатным для остальных номеров. Результат:
Для расчета стоимости используем следующую формулу:
- C3=ИСТИНА() – проверка условия «является ли значение, хранящееся в ячейке C3 равным значению, возвращаемым функцией (логическое истина)?».
- 0- стоимость звонка, если условие выполнено.
- D3*1,5 – стоимость звонка, если условие не выполнено.
Мы получили суммарную стоимость вех совершенных звонков по всем организациям.
Как посчитать среднее значение по условию в Excel
Пример 2. Определить средний балл за экзамен для группы студентов, в составе которой есть студенты, которые его провалили. Так же необходимо получить среднюю оценку успеваемости только лишь для тех студентов, которые сдали экзамен. Оценка студента, не сдавшего экзамен, должна учитываться как 0 (нуль) в формуле для расчета.
Для заполнения столбца «Сдал» используем формулу:
Создадим новый столбец, в который перезапишем оценки при условии, что оценка 2 интерпретируется как 0 с использованием формулы:
Определим средний балл по формуле:
Теперь получим средний балл успеваемости, для студентов, которые допущены к следующим экзаменам. Для этого воспользуемся еще одной логической функцией СРЗНАЧЕСЛИ:
Как получить значение по модулю числа без использования функции ABS
Пример 3. Реализовать алгоритм определения значения модуля числа (абсолютную величину), то есть альтернативный вариант для функции ABS.
Для решения используем формулу массива:
- НЕ(A3:A10<0) – проверка условия «принадлежит ли число к диапазону положительных значений или является 0 (нулем)?». Без использования функции не потребовалась бы более длинный вариант записи ИЛИ(A3:A10=0;A3:A10>0).
- A3:A10 – возвращаемое число (соответствующий элемент из диапазона), если условие выполняется;
- A3:A10*(-1) – возвращаемое число, если условие не выполняется (то есть, исходное значение принадлежит к диапазону отрицательных чисел, для получения модуля производится умножение на -1).
Примечание: как правило, логические значения и сами функции (ИСТИНА(), ЛОЖЬ()) в выражениях явно не указываются, как это сделано в примерах 1 и 2. Например, во избежание промежуточных расчетов в Примере 2 можно было использовать формулу =ЕСЛИ(B3=2;0;B3), а так же =B3<>2.
При этом Excel автоматически определяет результат вычислений выражения B3<>2 или B3=2;0;B3 в аргументах функции ЕСЛИ (логическое сравнение) и на его основании выполняет соответствующее действие, предписанное вторым или третьим аргументами функции ЕСЛИ.
Особенности использования функций ИСТИНА, ЛОЖЬ, НЕ в Excel
В функциях ИСТИНА и ЛОЖЬ Аргументы отсутствуют.
Функция НЕ имеет следующий вид синтаксической записи:
- логическое_значение – обязательный аргумент, характеризующий одно из двух возможных значений: ИСТИНА или ЛОЖЬ.
Среди множества различных выражений, которые применяются при работе с Microsoft Excel, следует выделить логические функции. Их применяют для указания выполнения различных условий в формулах. При этом, если сами условия могут быть довольно разнообразными, то результат логических функций может принимать всего два значения: условие выполнено (ИСТИНА) и условие не выполнено (ЛОЖЬ). Давайте подробнее разберемся, что представляют собой логические функции в Экселе.
Основные операторы
Существует несколько операторов логических функций. Среди основных следует выделить такие:
Существуют и менее распространенные логические функции.
У каждого из вышеуказанных операторов, кроме первых двух, имеются аргументы. Аргументами могут выступать, как конкретные числа или текст, так и ссылки, указывающие адрес ячеек с данными.
Функции ИСТИНА и ЛОЖЬ
Оператор ИСТИНА принимает только определенное заданное значение. У данной функции отсутствуют аргументы, и, как правило, она практически всегда является составной частью более сложных выражений.
Оператор ЛОЖЬ, наоборот, принимает любое значение, которое не является истиной. Точно так же эта функция не имеет аргументов и входит в более сложные выражения.
Функции И и ИЛИ
Функция И является связующим звеном между несколькими условиями. Только при выполнении всех условий, которые связывает данная функция, она возвращает значение ИСТИНА. Если хотя бы один аргумент сообщает значение ЛОЖЬ, то и оператор И в целом возвращает это же значение. Общий вид данной функции: =И(лог_значение1;лог_значение2;…) . Функция может включать в себя от 1 до 255 аргументов.
Функция ИЛИ, наоборот, возвращает значение ИСТИНА даже в том случае, если только один из аргументов отвечает условиям, а все остальные ложные. Её шаблон имеет следующий вид: =И(лог_значение1;лог_значение2;…) . Как и предыдущая функция, оператор ИЛИ может включать в себя от 1 до 255 условий.
Функция НЕ
В отличие от двух предыдущих операторов, функция НЕ имеет всего лишь один аргумент. Она меняет значение выражения с ИСТИНА на ЛОЖЬ в пространстве указанного аргумента. Общий синтаксис формулы выглядит следующим образом: =НЕ(лог_значение) .
Функции ЕСЛИ и ЕСЛИОШИБКА
Для более сложных конструкций используется функция ЕСЛИ. Данный оператор указывает, какое именно значение является ИСТИНА, а какое ЛОЖЬ. Его общий шаблон выглядит следующим образом: =ЕСЛИ(логическое_выражение;значение_если_истина;значение_если-ложь) . Таким образом, если условие соблюдается, то в ячейку, содержащую данную функцию, заполняют заранее указанные данные. Если условие не соблюдается, то ячейка заполняется другими данными, указанными в третьем по счету аргументе функции.
Оператор ЕСЛИОШИБКА, в случае если аргумент является истиной, возвращает в ячейку его собственное значение. Но, если аргумент ошибочный, тогда в ячейку возвращается то значение, которое указывает пользователь. Синтаксис данной функции, содержащей всего два аргумента, выглядит следующем образом: =ЕСЛИОШИБКА(значение;значение_если_ошибка) .
Функции ЕОШИБКА и ЕПУСТО
Функция ЕОШИБКА проверяет, не содержит ли определенная ячейка или диапазон ячеек ошибочные значения. Под ошибочными значениями понимаются следующие:
В зависимости от того ошибочный аргумент или нет, оператор сообщает значение ИСТИНА или ЛОЖЬ. Синтаксис данной функции следующий: = ЕОШИБКА(значение) . В роли аргумента выступает исключительно ссылка на ячейку или на массив ячеек.
Оператор ЕПУСТО делает проверку ячейки на то, пустая ли она или содержит значения. Если ячейка пустая, функция сообщает значение ИСТИНА, если ячейка содержит данные – ЛОЖЬ. Синтаксис этого оператора имеет такой вид: =ЕПУСТО(значение) . Так же, как и в предыдущем случае, аргументом выступает ссылка на ячейку или массив.
Пример применения функций
Теперь давайте рассмотрим применение некоторых из вышеперечисленных функций на конкретном примере.
Имеем список работников предприятия с положенными им заработными платами. Но, кроме того, всем работникам положена премия. Обычная премия составляет 700 рублей. Но пенсионерам и женщинам положена повышенная премия в размере 1000 рублей. Исключение составляют работники, по различным причинам проработавшие в данном месяце менее 18 дней. Им в любом случае положена только обычная премия в размере 700 рублей.
Попробуем составить формулу. Итак, у нас существует два условия, при исполнении которых положена премия в 1000 рублей – это достижение пенсионного возраста или принадлежность работника к женскому полу. При этом, к пенсионерам отнесем всех тех, кто родился ранее 1957 года. В нашем случае для первой строчки таблицы формула примет такой вид: =ЕСЛИ(ИЛИ(C4<1957;D4="жен.");"1000";"700") . Но, не забываем, что обязательным условием получения повышенной премии является отработка 18 дней и более. Чтобы внедрить данное условие в нашу формулу, применим функцию НЕ: =ЕСЛИ(ИЛИ(C4<1957;D4="жен.")*(НЕ(E4<18));"1000";"700") .
Для того, чтобы скопировать данную функцию в ячейки столбца таблицы, где указана величина премии, становимся курсором в нижний правый угол ячейки, в которой уже имеется формула. Появляется маркер заполнения. Просто перетягиваем его вниз до конца таблицы.
Таким образом, мы получили таблицу с информацией о величине премии для каждого работника предприятия в отдельности.
Как видим, логические функции являются очень удобным инструментом для проведения расчетов в программе Microsoft Excel. Используя сложные функции, можно задавать несколько условий одновременно и получать выводимый результат в зависимости от того, выполнены эти условия или нет. Применение подобных формул способно автоматизировать целый ряд действий, что способствует экономии времени пользователя.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Функция СЧЁТЕСЛИ в MS Excel, позволяет посчитать количество ячеек, которые отвечают заданному критерию поиска, в указанном диапазоне. Рассмотрим, как это работает на примерах.
Как вызвать функцию СЧЁТЕСЛИ СЧЁТЕСЛИ в MS Excel.
Нажимаем на кнопу Вставить функцию и в открывшемся диалоговом окне Вставка функции, в поле поиск, пишем СЧЁТЕСЛИ. Нажимаем найти. Обращаю внимание, что в поле поиска, писать СЧЁТЕЛСИ, необходимо именно с буквой Ё. По-другому поиск не найдет интересующую нас функцию. По результатам поиска, выбираем нужную нам функцию.
Аргументы функции СЧЁТЕСЛИ в MS Excel.
После того, как была выбрана функция СЧЁТЕСЛИ, появляется диалоговое окно Аргументы функции. В данном диалоговом окне есть два поля для заполнения:
- Диапазон, здесь необходимо указать диапазон ячеек, в которых будет осуществляться поиск ячеек с значениями, которые отвечают критериям поиска. Выбрать необходимый диапазон можно сначала поставив курсор на поле Диапазон, а потом выделив необходимые ячейки.
- Критерий, здесь необходимо указать условие (критерий поиска), согласно которому функция будет искать и подсчитывать количество ячеек в заданном Диапазоне.
Какие варианты критериев поиска существуют.
В данном пункте, на примерах рассмотрим, какие варианты критериев для поиска можно использовать в функции СЧЁТЕСЛИ. Предположим, что у нас есть простая таблица, с текстовыми данными и числами.
Больше (>), меньше(<), больше или равно (>=), меньше или равно (<=), равно (=), не равно (<>).
Больше.
Найдем, сколько ячеек в столбце Числа, содержат в себе значение больше числа 50. Вызываем функцию СЧЁТЕСЛИ в ячейку Е2. В диалоговом окне Аргументы функции, в поле Диапазон, указываем диапазон С3:С17. Это ячейки столбца Числа, в которых мы будем осуществлять поиск. В поле Критерий, пишем знак больше «>», и число 50.
Формула функции будет выглядеть вот так:
Кавычки появятся автоматически, после того, как нажать ОК.
По такому же принципу работаю остальные варианты. Если продолжать рассматривать на нашем примере, Диапазон для всех остальных случаев остаётся без изменения. Меняется Критерий.
Меньше.
Используем знак меньше «<».
В поле Критерий, диалогового окна Аргументы функции, пишем: <50.
Формула функции будет выглядеть вот так:
В результате получим количество ячеек, числа в которых меньше 50.
Больше или равно.
В поле Критерий, диалогового окна Аргументы функции, пишем: >=50.
Формула функции будет выглядеть вот так:
В результате получим количество ячеек, числа в которых больше или равны 50.
Меньше или равно.
В поле Критерий, диалогового окна Аргументы функции, пишем: <=50.
Формула функции будет выглядеть вот так:
В результате получим количество ячеек, числа в которых меньше или равны 50.
Равно.
В поле Критерий, диалогового окна Аргументы функции, пишем: =50.
Формула функции будет выглядеть вот так:
В результате получим количество ячеек, числа в которых равны 50.
Не равно.
В поле Критерий, диалогового окна Аргументы функции, пишем: <>50.
Формула функции будет выглядеть вот так:
В результате получим количество ячеек, числа в которых не равны 50.
Ссылка на ячейку в качестве критерия поиска функции СЧЁТЕСЛИ в MS Excel.
Возможно использовать ссылку на ячейку в качестве критерия поиска. В нашем примере найдем сколько ячеек в столбце Числа содержат в себе число 50. Значения в поле Диапазон, диалогового окна Аргументы функции, остаётся без изменений. В поле Критерий указываем любую ячейку из указанного диапазона, которая отвечает нашему критерию. В нашем пример выберем ячейку С11.
Формула функции будет выглядеть вот так:
Кавычки в таком варианте не нужны.
Нажимаем ОК. Получаем результат. Количество ячеек, которые содержат то же значение, что и ячейка С11. Три ячейки. Они залиты желтым цветом для наглядности.
При использование в качестве критерия поиска ссылку на ячейку, использовать знаки: <, >, =, <=, >=, <>, необходимо с знаком амперсанда (&), между этими знаками с самой ссылкой на ячейку.
Важный момент, в данном случае, кавычки возле знака больше (>) ставить нужно вручную.
Для примера, формулы с знаком больше будет выглядеть вот так:
Текстовые значения в качестве критерия поиска функции СЧЁТЕСЛИ в MS Excel.
В качестве критерия поиска в поле Критерий, в диалоговом окне Аргументы функции, можно использовать текстовое значения. Например, можно найти в столбце Значения, количество ячеек, которые не содержат в себе Значение 1. Меняем Диапазон поиска.
Формула функции будет выглядеть вот так:
Кавычки возле знака не равно (<>) ставить вручную.
Если использовать в качестве критерия не ссылку на ячейку, в которой есть текст, а сам текст, его необходимо заключить в кавычки вручную.
Формула функции будет выглядеть вот так:
Возможно осуществлять поиск указав в критерии только часть слова или одну букву. Например, у нас есть столбец, в котором указано название мебели.
Найдем количество ячеек, со словом Стол, указав в критерии поиска часть букв из этого слова, которые стоят в начале: Ст
Формула функции будет выглядеть вот так:
Теперь укажем в критерии поиска конец слова. Например, букву ф, из слова шкаф.
Формула функции будет выглядеть вот так:
Кавычки возле Ст* и *ф, ставятся автоматически.
Варианты использования функции СЧЁТЕСЛИ с двумя (несколькими) критериями поиска описаны в статье: Функция СЧЁТЕСЛИ с использованием двух (нескольких) критериев поиска. Описание и примеры.
Читайте также: