Диапазон критериев используется в ms excel при
Описание синтаксиса функции
Начать следует с описания синтаксиса самой функции «СЧЁТЕСЛИ», поскольку при работе с несколькими условиями понадобится создавать большую формулу, учитывая все особенности.
-
Для простоты понимания структуры предлагаем объявить в поле =СЧЁТЕСЛИ() и сразу перейти к меню «Аргументы функции».
Внизу под полями виден результат, что уже свидетельствует о правильном составлении функции. Сейчас добавить еще одно условие нельзя, поэтому формулу придется расширять, о чем и пойдет речь в следующих двух вариантах.
Вариант 1: Счет текстовых условий
Разберем ситуацию, когда есть два столбца с определенными значениями, которыми в нашем случае выступают месяцы. Нужно сделать выборку из них, чтобы в результате показывало значение того, сколько ячеек соответствуют заданному условию. Объединяются два условия при помощи одной простой формулы.
- Создайте первую часть функции «СЧЁТЕСЛИ», указав в качестве диапазона первый столбец. Сама функция имеет стандартный вид: =СЧЁТЕСЛИ(A2:A25;"Критерий") .
Проверьте результат, который отобразится в заданной клетке. Если вдруг возникла ошибка, удостоверьтесь в том, что вы правильно соблюли синтаксис функции, а ячейки в диапазоне имеют соответствующий формат.
Вариант 2: Счет числовых условий
С числовыми условиями дела обстоят точно так же, но на этот раз давайте рассмотрим более детальный пример ручного составления функции, учитывая каждую деталь.
-
После объявления «СЧЁТЕСЛИ» в круглых скобках задайте диапазон чисел «A1:A25», где вместо указанных ячеек подставьте необходимые.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Смотря на сухие цифры таблиц, трудно с первого взгляда уловить общую картину, которую они представляют. Но, в программе Microsoft Excel имеется инструмент графической визуализации, с помощью которого можно наглядно представить данные, содержащиеся в таблицах. Это позволяет более легко и быстро усвоить информацию. Данный инструмент называется условным форматированием. Давайте разберемся, как использовать условное форматирование в программе Microsoft Excel.
Простейшие варианты условного форматирования
Для того, чтобы произвести форматирование определенной области ячеек, нужно выделить эту область (чаще всего столбец), и находясь во вкладке «Главная», кликнуть по кнопке «Условное форматирование», которая расположена на ленте в блоке инструментов «Стили».
После этого, открывается меню условного форматирования. Тут представляется три основных вида форматирования:
- Гистограммы;
- Цифровые шкалы;
- Значки.
Для того, чтобы произвести условное форматирование в виде гистограммы, выделяем столбец с данными, и кликаем по соответствующему пункту меню. Как видим, представляется на выбор несколько видов гистограмм с градиентной и сплошной заливкой. Выберете ту, которая, на ваш взгляд, больше всего соответствует стилю и содержанию таблицы.
Как видим, гистограммы появились в выделенных ячейках столбца. Чем большее числовое значение в ячейках, тем гистограмма длиннее. Кроме того, в версиях Excel 2010, 2013 и 2016 годов, имеется возможность корректного отображения отрицательных значений в гистограмме. А вот, у версии 2007 года такой возможности нет.
При использовании вместо гистограммы цветовой шкалы, также существует возможность выбрать различные варианты данного инструмента. При этом, как правило, чем большее значение расположено в ячейке, тем насыщеннее цвет шкалы.
Наиболее интересным и сложным инструментом среди данного набора функций форматирования являются значки. Существует четыре основные группы значков: направления, фигуры, индикаторы и оценки. Каждый выбранный пользователем вариант предполагает использование разных значков при оценке содержимого ячейки. Вся выделенная область сканируется Excel, и все значения ячеек разделяются на части, согласно величинам, указанным в них. К самым большим величинам применяются значки зеленого цвета, к величинам среднего диапазона – желтого, и величины, располагающиеся в самой меньшей трети – помечаются значками красного цвета.
При выборе стрелок, в качестве значков, кроме цветового оформления, используется ещё сигнализирование в виде направлений. Так, стрелка, повернутая указателем вверх, применяется к большим величинам, влево – к средним, вниз – к малым. При использовании фигур, кругом помечаются самые большие величины, треугольником – средние, ромбом – малые.
Правила выделения ячеек
По умолчанию, используется правило, при котором все ячейки выделенного фрагмента обозначаются определенным цветом или значком, согласно расположенным в них величинам. Но, используя меню, о котором мы уже говорили выше, можно применять и другие правила обозначения.
Кликаем по пункту меню «Правила выделения ячеек». Как видим, существует семь основных правил:
- Больше;
- Меньше;
- Равно;
- Между;
- Дата;
- Повторяющиеся значения.
Рассмотрим применение этих действий на примерах. Выделим диапазон ячеек, и кликнем по пункту «Больше…».
Открывается окно, в котором нужно установить, значения больше какого числа будут выделяться. Делается это в поле «Форматировать ячейки, которые больше». По умолчанию, сюда автоматически вписывается среднее значение диапазона, но можно установить любое другое, либо же указать адрес ячейки, в которой содержится это число. Последний вариант подойдёт для динамических таблиц, данные в которых постоянно изменяются, или для ячейки, где применяется формула. Мы для примера установили значение в 20000.
В следующем поле, нужно определиться, как будут выделяться ячейки: светло-красная заливка и темно-красный цвет (по умолчанию); желтая заливка и темно-желтый текст; красный текст, и т.д. Кроме того, существует пользовательский формат.
При переходе на этот пункт, открывается окно, в котором можно редактировать выделения, практически, как угодно, применяя различные варианты шрифта, заливки, и границы.
После того, как мы определились, со значениями в окне настройки правил выделения, жмём на кнопку «OK».
Как видим, ячейки выделены, согласно установленному правилу.
По такому же принципу выделяются значения при применении правил «Меньше», «Между» и «Равно». Только в первом случае, выделяются ячейки меньше значения, установленного вами; во втором случае, устанавливается интервал чисел, ячейки с которыми будут выделяться; в третьем случае задаётся конкретное число, а выделяться будут ячейки только содержащие его.
Правило выделения «Текст содержит», главным образом, применяется к ячейкам текстового формата. В окне установки правила следует указать слово, часть слова, или последовательный набор слов, при нахождении которых, соответствующие ячейки будут выделяться, установленным вами способом.
Правило «Дата» применяется к ячейкам, которые содержат значения в формате даты. При этом, в настройках можно установить выделение ячеек по тому, когда произошло или произойдёт событие: сегодня, вчера, завтра, за последние 7 дней, и т.д.
Применив правило «Повторяющиеся значения» можно настроить выделение ячеек, согласно соответствию размещенных в них данных одному из критериев: повторяющиеся это данные или уникальные.
Правила отбора первых и последних значений
Кроме того, в меню условного форматирования имеется ещё один интересный пункт – «Правила отбора первых и последних значений». Тут можно установить выделение только самых больших или самых маленьких значений в диапазоне ячеек. При этом, можно использовать отбор, как по порядковым величинам, так и по процентным. Существуют следующие критерии отбора, которые указаны в соответствующих пунктах меню:
- Первые 10 элементов;
- Первые 10%;
- Последние 10 элементов;
- Последние 10%;
- Выше среднего;
- Ниже среднего.
Но, после того, как вы кликнули по соответствующему пункту, можно немного изменить правила. Открывается окно, в котором производится выбор типа выделения, а также, при желании, можно установить другую границу отбора. Например, мы, перейдя по пункту «Первые 10 элементов», в открывшемся окне, в поле «Форматировать первые ячейки» заменили число 10 на 7. Таким образом, после нажатия на кнопку «OK», будут выделяться не 10 самых больших значений, а только 7.
Создание правил
Выше мы говорили о правилах, которые уже установлены в программе Excel, и пользователь может просто выбрать любое из них. Но, кроме того, при желании, пользователь может создавать свои правила.
Для этого, нужно нажать в любом подразделе меню условного форматирования на пункт «Другие правила…», расположенный в самом низу списка». Или же кликнуть по пункту «Создать правило…», который расположен в нижней части основного меню условного форматирования.
Открывается окно, где нужно выбрать один из шести типов правил:
- Форматировать все ячейки на основании их значений;
- Форматировать только ячейки, которые содержат;
- Форматировать только первые и последние значения;
- Форматировать только значения, которые находятся выше или ниже среднего;
- Форматировать только уникальные или повторяющиеся значения;
- Использовать формулу для определения форматируемых ячеек.
Согласно выбранному типу правил, в нижней части окна нужно настроить изменение описания правил, установив величины, интервалы и другие значения, о которых мы уже говорили ниже. Только в данном случае, установка этих значений будет более гибкая. Тут же задаётся, при помощи изменения шрифта, границ и заливки, как именно будет выглядеть выделение. После того, как все настройки выполнены, нужно нажать на кнопку «OK», для сохранения проведенных изменений.
Управление правилами
В программе Excel можно применять сразу несколько правил к одному и тому же диапазону ячеек, но отображаться на экране будет только последнее введенное правило. Для того, чтобы регламентировать выполнение различных правил относительно определенного диапазона ячеек, нужно выделить этот диапазон, и в основном меню условного форматирования перейти по пункту управление правилами.
Открывается окно, где представлены все правила, которые относятся к выделенному диапазону ячеек. Правила применяются сверху вниз, так как они размещены в списке. Таким образом, если правила противоречат друг другу, то по факту на экране отображается выполнение только самого последнего из них.
Чтобы поменять правила местами, существуют кнопки в виде стрелок направленных вверх и вниз. Для того, чтобы правило отображалось на экране, нужно его выделить, и нажать на кнопку в виде стрелки направленной вниз, пока правило не займет самую последнюю строчу в списке.
Есть и другой вариант. Нужно установить галочку в колонке с наименованием «Остановить, если истина» напротив нужного нам правила. Таким образом, перебирая правила сверху вниз, программа остановится именно на правиле, около которого стоит данная пометка, и не будет опускаться ниже, а значит, именно это правило будет фактически выполнятся.
В этом же окне имеются кнопки создания и изменения выделенного правила. После нажатия на эти кнопки, запускаются окна создания и изменения правил, о которых мы уже вели речь выше.
Для того, чтобы удалить правило, нужно его выделить, и нажать на кнопку «Удалить правило».
Кроме того, можно удалить правила и через основное меню условного форматирования. Для этого, кликаем по пункту «Удалить правила». Открывается подменю, где можно выбрать один из вариантов удаления: либо удалить правила только на выделенном диапазоне ячеек, либо удалить абсолютно все правила, которые имеются на открытом листе Excel.
Как видим, условное форматирование является очень мощным инструментом для визуализации данных в таблице. С его помощью, можно настроить таблицу таким образом, что общая информация на ней будет усваиваться пользователем с первого взгляда. Кроме того, условное форматирование придаёт большую эстетическую привлекательность документу.
Мы рады, что смогли помочь Вам в решении проблемы.
Отблагодарите автора, поделитесь статьей в социальных сетях.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Для подсчета ЧИСЛОвых значений, Дат и Текстовых значений, удовлетворяющих определенному критерию, существует простая и эффективная функция СЧЁТЕСЛИ( ) , английская версия COUNTIF(). Подсчитаем значения в диапазоне в случае одного критерия, а также покажем как ее использовать для подсчета неповторяющихся значений и вычисления ранга .
СЧЁТЕСЛИ ( диапазон ; критерий )
Диапазон — диапазон, в котором нужно подсчитать ячейки, содержащие числа, текст или даты.
Критерий — критерий в форме числа, выражения, ссылки на ячейку или текста, который определяет, какие ячейки надо подсчитывать. Например, критерий может быть выражен следующим образом: 32, "32", ">32", "яблоки" или B4 .
Подсчет числовых значений с одним критерием
Данные будем брать из диапазона A15:A25 (см. файл примера ).
Критерий
Формула
Результат
Примечание
4
Подсчитывает количество ячеек, содержащих числа равных или более 10. Критерий указан в формуле
8
Подсчитывает количество ячеек, содержащих числа равных или меньших 10. Критерий указан через ссылку
>= (ячейка С4)11(ячейка С5)
3
Подсчитывает количество ячеек, содержащих числа равных или более 11. Критерий указан через ссылку и параметр
Примечание . О подсчете значений, удовлетворяющих нескольким критериям читайте в статье Подсчет значений со множественными критериями . О подсчете чисел с более чем 15 значащих цифр читайте статью Подсчет ТЕКСТовых значений с единственным критерием в MS EXCEL .
Подсчет Текстовых значений с одним критерием
Функция СЧЁТЕСЛИ() также годится для подсчета текстовых значений (см. Подсчет ТЕКСТовых значений с единственным критерием в MS EXCEL ).
Подсчет дат с одним критерием
Так как любой дате в MS EXCEL соответствует определенное числовое значение , то настройка функции СЧЕТЕСЛИ() для дат не отличается от рассмотренного выше примера (см. файл примера Лист Даты ).
Если необходимо подсчитать количество дат, принадлежащих определенному месяцу, то нужно создать дополнительный столбец для вычисления месяца, затем записать формулу = СЧЁТЕСЛИ(B20:B30;2)
Подсчет с несколькими условиями
Обычно, в качестве аргумента критерий у функции СЧЁТЕСЛИ() указывают только одно значение. Например, =СЧЁТЕСЛИ(H2:H11;I2) . Если в качестве критерия указать ссылку на целый диапазон ячеек с критериями, то функция вернет массив. В файле примера формула =СЧЁТЕСЛИ(A16:A25;C16:C18) возвращает массив .
Для ввода формулы выделите диапазон ячеек такого же размера как и диапазон содержащий критерии. В Строке формул введите формулу и нажмите CTRL+SHIFT+ENTER , т.е. введите ее как формулу массива .
Это свойство функции СЧЁТЕСЛИ() используется в статье Отбор уникальных значений .
Специальные случаи использования функции
Возможность задать в качестве критерия несколько значений открывает дополнительные возможности использования функции СЧЁТЕСЛИ() .
В файле примера на листе Специальное применение показано как с помощью функции СЧЁТЕСЛИ() вычислить количество повторов каждого значения в списке.
Выражение СЧЁТЕСЛИ(A6:A14;A6:A14) возвращает массив чисел , который говорит о том, что значение 1 из списка в диапазоне А6:А15 - единственное, также в диапазоне 4 значения 2, одно значение 3, три значения 4. Это позволяет подсчитать количество неповторяющихся значений формулой =СУММПРОИЗВ(--(СЧЁТЕСЛИ(A6:A14;A6:A14)=1)) .
Формула =СЧЁТЕСЛИ(A6:A14;" вычисляет ранг по убыванию для каждого числа из диапазона А6:А15. В этом можно убедиться, выделив формулу в Строке формул и нажав клавишу F9 . Значения совпадут с вычисленным рангом в столбце В (с помощью функции РАНГ() ). Этот подход применен в статьях Динамическая сортировка таблицы в MS EXCEL и Отбор уникальных значений с сортировкой в MS EXCEL .
В Электронной таблице есть список. К каждому из объектов уже присвоена оценка качества.
Требуется для каждого построить диапазон из 10 случайных значений в интервале [0,3] таким образом, чтобы если оценка качества "4", то 20% значений должна быть 0. При этом очень важно, чтобы очередность нулей была в случайном порядке.
Задано 25 случайных чисел. Подсчитать количество чисел, попавших в диапазон [a; b]
Задано 25 случайных чисел. Подсчитать количество чисел, попавших в диапазон и найти величину.
Диапазон случайных чисел
Написал программку-угадайку случайного числа. Но возник вопрос. Использую функцию rand() и она все.
Хитрый диапазон случайных чисел
Здравствуйте. Нужно в функции (Random(1000)+350); сделать так, чтобы найденные числа больше не.
Диапазон случайных вещественных чисел
помогите пожалуйста, с целочисленным то все понятно. но, а как можно заполнить массив вещественным.
20% от восьми (кол-во желых ячеек) - это 1,6 ячеек.
То есть все же 25% и две ячейки будут пустые?
4 - это максимальная оценка? А минимальная - ноль? Или задачка только для оценки 4?
Кинули бы вы пример из нескольких строк на примере. Как вы хотите чтоб было с учетом разных вариантов оценки.
Да, 25 процентов.
Все верно - минимальная 0.
Это ПРИМЕР того, как мне нужно. Вместо цифр на цветном фоне должна быть формула, но как это сделать, пока думаю и прошу поддержки.
Пример___.xlsx
Решение
Ничего не понимаю.
Во-первых, в примере при оценке 4 три ноля.
Во-вторых, в примере в ячейках значения не в интервале [0,3].
В-третьих, ячеек 10, а не 8.
В любом случае, мне в голову пришел только самый кондовый вариант. 100% можно элегантнее.
Вобщем суть в том, что на отдельном листе собрал 20 тыс. вариантов значений от 1 до 3, но с двумя нолями.
И если оценка 4 - то цифра подбирается из случайно выбранной строки. Если другая цифра - то простой рандом.
Большая Благодарность ВАМ!!
Это то, что надо! Вы помогли разобраться в формуле. Я все понял! ;-) Спасибо!
Решение
Требуется для каждого построить диапазон из 10 случайных значений в интервале [0,3] таким образом, чтобы если оценка качества "4", то 20% значений должна быть 0.
Домысливаю:
- принята 5-бальная система оценок.
- если выставлен балл 4, значит 1/5 оценок (то есть 20%) должна быть 0. Для 10 значений это 2 нуля.
аналогично, например, для 3 это 2/5 оценок (40%), то есть 4 значения из 10.
Для 1 строки задача худо-бедно решается формулами. В приложенном файле на листе 1 возможное решение приведено. Справа для контроля для выставленной оценки считается необходимое и достигнутое количество нулей. Если оно не совпадает, можно несколько раз пересчитать лист Sift/F9, чтобы получить совпадение, либо иное распределение оценок.
На листе 2 показано применение этого решения для 2 строк.
Для бОльшего количества строк задача формулами не решается - слишком много степеней свободы в таблице. На листе 3 показан пример.
Гарантировано задача решается макросрм. На листе Макрос такое решение реализуется.
Для суммирования значений по одному диапазону на основе данных другого диапазона используется функция СУММЕСЛИ() . Рассмотрим случай, когда критерий применяется к диапазону содержащему текстовые значения.
Пусть дана таблица с перечнем наименований фруктов и их количеством (см. файл примера ).
Если в качестве диапазона, к которому применяется критерий, выступает диапазон с текстовыми значениями, то можно рассмотреть несколько типов задач суммирования:
- суммирование значений, если соответствующие им ячейки в диапазоне поиска соответствуют критерию (простейший случай);
- в критерии применяются подстановочные знаки (*, ?) ;
- критерий сравнивается со значениями в диапазоне поиска с учетом РЕгиСтРА .
Рассмотрим эти задачи подробнее.
Значение соответствует критерию
Найдем количество всех значений "Яблоки" , т.е. просуммируем значения из столбца Количество, для которых соответствующее значение из столбца Фрукты в точности равно "Яблоки" (без учета РЕГИСТРА) .
Для подсчета используем формулу =СУММЕСЛИ(A3:A13;"яблоки";B3:B13)
Критерий яблоки можно поместить в ячейку D 5 , тогда формулу можно переписать следующим образом: =СУММЕСЛИ(A3:A13;D5;B3 :B13 )
В качестве диапазона суммирования можно указать лишь первую ячейку диапазона - функция СУММЕСЛИ() просуммирует все правильно: =СУММЕСЛИ(A3:A13; D5 ;B3)
В критерии применяются подстановочные знаки (*, ?)
Просуммируем значения из столбца Количество, для которых соответствующее значение из столбца Фрукты содержит слово Яблоки (без учета РЕгиСТРА) .
Для решения этой задачи используем подстановочные знаки (*, ?) . Подход заключается в том, что для отбора текстовых значений в качестве критерия задается лишь часть текстовой строки. Например, для отбора всех ячеек, содержащих слова яблоки ( свежие яблоки , яблоки местные и пр.) можно использовать критерии с подстановочным знаком * (звездочка). Для этого нужно использовать конструкцию * яблоки* .
Решение задачи выглядит следующим образом (учитываются значения содержащие слово яблоки в любом месте в диапазоне поиска): =СУММЕСЛИ($A$3:$A$13;"*яблоки*";B3)
Альтернативный вариант без использования подстановочных знаков выглядит более сложно: =СУММПРОИЗВ(B3:B13*НЕ(ЕОШ(ПОИСК("яблоки";A3:A13))))
Примеры, приведенные ниже, иллюстрируют другие применения подстановочных знаков.
Задача . Просуммировать значения, если соответствующие ячейки:
Задача
Критерий
Формула
Результат
Примечание
заканчиваются на слово яблоки , например, Свежие яблоки
11
Использован подстановочный знак * (перед значением)
начинаются на слово яблоки , например, яблоки местные
20
Использован подстановочный знак * (после значения)
начинаются с гру и содержат ровно 6 букв
= СУММЕСЛИ($A$3:$A$13; "гру. ";B3)
56
Использован подстановочный знак ?
Критерий сравнивается со значениями в диапазоне поиска с учетом РЕгиСТРА
Учет РЕгиСТра приводит к необходимости создания более сложных формул. Чаще всего используются формулы на основе функций НАЙТИ() и СОВПАД() учитывающих регистр.
Ниже приведены формулы для суммирования чисел, если соответствующие значения совпадают с критерием с учетом регистра.
Просуммировать значения, если соответствующие ячейки:
Критерий
Формула
Результат
Примечание
в точности равны Яблоки с учетом регистра
содержат значение Яблоки в любом месте текстовой строки с учетом регистра
= СУММ(ЕСЛИ( СОВПАД("Яблоки";A3:A13);1;0) *B3:B13)
СОВЕТ: Для сложения с несколькими критериями воспользуйтесь статьей Функция СУММЕСЛИМН() Сложение с несколькими критериями в MS EXCEL (Часть 2.Условие И) .
В статье Сложение по условию (один Числовой критерий) рассмотрен случай, когда критерий применяется к числовым значениям из диапазона, по которому производится суммирование.
Читайте также: