Критерии в Excel
Если необходимо выполнить операцию, выходящую за рамки автофильтра, придется прибегнуть к помощи средств расширенной фильтрации. Расширенный фильтр гораздо более гибкий, чем автофильтр, однако при его использовании требуется выполнять больше подготовительных действий. Расширенный фильтр позволяет выполнить следующее:
Прежде чем использовать средство расширенной фильтрации, необходимо задать диапазон критериев. Диапазон критериев — это специально отведенная область рабочего листа, отвечающая определенным требованиям. Диапазон критериев используется Excel для фильтрации списка и должен отвечать следующим требованиям:
Чтобы выполнить фильтрацию, выберите команду Данные | Фильтр | Расширенный фильтр . Появится диалоговое окно Расширенный фильтр (рис. 2).
Определите диапазон списка и диапазон критериев и убедитесь, что установлен переключатель Фильтровать список на месте . Щелкните на кнопке ОК , и список будет отфильтрован по заданным критериям.
Если в диапазоне критериев используется несколько строк, критерии в каждой строке соединены оператором ИЛИ . На рисунке 3 показан диапазон критериев A1:D3 с двумя строками критериев.
Рис.3. В этом диапазоне критериев содержится два набора критериев
В этом примере строки отфильтрованного списка отвечают следующим требованиям:
Это пример фильтрации, которая не может быть выполнена с помощью автофильтра. Диапазон критериев может иметь любое количество строк, каждая из которых соединена одна с другой оператором ИЛИ.
Элементы, которые указываются в диапазоне критериев, можно разделить на следующие типы:
В таблице 1 представлен список операторов сравнения, которые можно использовать в текстовых или числовых критериях.
В таблице 2 показаны примеры строковых (текстовых) критериев.
Вычисляемый критерий может сделать операцию фильтрации более гибкой. Вычисляемый критерий основывается на одном или нескольких вычислениях. На рисунке 4 показан пример простого списка, состоящего из кодов проектов, даты начала, даты окончания и количества привлеченных к проекту ресурсов.
Рис.4. Этот список будет отфильтрован с использованием вычисляемого критерия
Над списком в диапазоне А1:А2 помещен диапазон критериев. Однако обратите внимание на то, что в диапазоне критериев используются не названия полей списка, а название нового поля. Вычисляемый критерий, по существу, вычисляет для списка новое поле. Поэтому следует помещать название поля в первую строку диапазона критериев.
В ячейке А2 содержится следующая формула: = С5 — В5 + 1 >= 30
Если необходимо отфильтровать список так, чтобы были показаны только проекты, которые используют ресурсы больше среднего, примените следующую формулу: = D5 > СРЗНАЧ(D:D)
Отфильтрованный в результате список будет содержать только те строки, в которых значение в поле Ресурсы превосходит среднее значение этого же поля.
При использовании вычисляемых критериев рекомендуется помнить некоторые правила:
В диалоговом окне Расширенный список представлены еще две опции:
Если Вы выберите переключатель Скопировать результат в другое место в диалоговом окне Расширенный фильтр , то отобранные строки будут скопированы в другое место активного рабочего листа или на другой лист. Место определяется в поле Поместить результат в диапазон . Обратите внимание, что при использовании этой опции сам список не фильтруется.
При выборе опции Только уникальные записи все одинаковые строки, отвечающие определенному критерию, будут скрыты. Если Вы не установили диапазон критериев, то в списке будут скрыты все одинаковые строки.
На следующем шаге мы рассмотрим использование в списках функций баз данных.
Excel: Функции СЧЕТЕСЛИ и СЧЕТЕСЛИМН
В случаях, когда при работе в Excel требуется подсчитать количество ячеек, удовлетворяющих заданному условию, можно использовать функцию Счётесли .
Функция Счётесли
Диапазон – группа ячеек, для которых нужно выполнить подсчет.
Диапазон может содержать числа, массивы или ссылки на числа.
Обязательный аргумент.
Критерий – число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать.
Обязательный аргумент.
Критерий не чувствителен к регистру. К примеру, функция не увидит разницы между словами «налог» и «НАЛОГ».
Примеры использования функции Счётесли.
Вопросительный знак соответствует одному любому символу,
звездочка— любому количеству произвольных символов.
Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак
Например, чтобы подсчитать количество ячеек, содержащих текст, который начинается с буквы Т (без учета регистра), можно воспользоваться следующей формулой:
Если необходимо подсчитать количество ячеек, которые содержат ровно четыре символа, можно использовать формулу:
В функции Счётесли используется только один критерий.
Чтобы провести подсчет по нескольким условиям, необходимо воспользоваться функцией Счётеслимн.
Функция Счётеслимн
Функция аналогична функции Счётеслимн, за исключением того, что может содержать до 127 диапазонов и условий, где первое является обязательным, а последующие – нет.
Каждый дополнительный диапазон должен состоять из такого же количества строк и столбцов, что и диапазон условия 1. Эти диапазоны могут не находиться рядом друг с другом.
Пример использования:
Функция СЧЁТЕСЛИ в MS Excel. Описание и примеры
Функция СЧЁТЕСЛИ в MS Excel, позволяет посчитать количество ячеек, которые отвечают заданному критерию поиска, в указанном диапазоне. Рассмотрим, как это работает на примерах.
Как вызвать функцию СЧЁТЕСЛИ СЧЁТЕСЛИ в MS Excel.
Нажимаем на кнопу Вставить функцию и в открывшемся диалоговом окне Вставка функции, в поле поиск, пишем СЧЁТЕСЛИ. Нажимаем найти. Обращаю внимание, что в поле поиска, писать СЧЁТЕЛСИ, необходимо именно с буквой Ё. По-другому поиск не найдет интересующую нас функцию. По результатам поиска, выбираем нужную нам функцию.
Аргументы функции СЧЁТЕСЛИ в MS Excel.
После того, как была выбрана функция СЧЁТЕСЛИ, появляется диалоговое окно Аргументы функции. В данном диалоговом окне есть два поля для заполнения:
- Диапазон, здесь необходимо указать диапазон ячеек, в которых будет осуществляться поиск ячеек с значениями, которые отвечают критериям поиска. Выбрать необходимый диапазон можно сначала поставив курсор на поле Диапазон, а потом выделив необходимые ячейки.
- Критерий, здесь необходимо указать условие (критерий поиска), согласно которому функция будет искать и подсчитывать количество ячеек в заданном Диапазоне.
Какие варианты критериев поиска существуют.
В данном пункте, на примерах рассмотрим, какие варианты критериев для поиска можно использовать в функции СЧЁТЕСЛИ. Предположим, что у нас есть простая таблица, с текстовыми данными и числами.
Больше (>), меньше(=), меньше или равно (<=), равно (=), не равно ().
Больше.
Найдем, сколько ячеек в столбце Числа, содержат в себе значение больше числа 50. Вызываем функцию СЧЁТЕСЛИ в ячейку Е2. В диалоговом окне Аргументы функции, в поле Диапазон, указываем диапазон С3:С17. Это ячейки столбца Числа, в которых мы будем осуществлять поиск. В поле Критерий, пишем знак больше «>», и число 50.
Кавычки появятся автоматически, после того, как нажать ОК.
Получаем результат — пять ячеек. Сами ячейки, с значением больше пятидесяти, залиты желтым цветом для наглядности.
По такому же принципу работаю остальные варианты. Если продолжать рассматривать на нашем примере, Диапазон для всех остальных случаев остаётся без изменения. Меняется Критерий.
Меньше.
В поле Критерий, диалогового окна Аргументы функции, пишем:
В результате получим количество ячеек, числа в которых меньше 50.
Больше или равно.
В поле Критерий, диалогового окна Аргументы функции, пишем: >=50.
В результате получим количество ячеек, числа в которых больше или равны 50.
Меньше или равно.
В поле Критерий, диалогового окна Аргументы функции, пишем:
В результате получим количество ячеек, числа в которых меньше или равны 50.
Равно.
В поле Критерий, диалогового окна Аргументы функции, пишем: =50.
В результате получим количество ячеек, числа в которых равны 50.
Не равно.
В поле Критерий, диалогового окна Аргументы функции, пишем: 50.
В результате получим количество ячеек, числа в которых не равны 50.
Ссылка на ячейку в качестве критерия поиска функции СЧЁТЕСЛИ в MS Excel.
Нажимаем ОК. Получаем результат. Количество ячеек, которые содержат то же значение, что и ячейка С11. Три ячейки. Они залиты желтым цветом для наглядности.
При использование в качестве критерия поиска ссылку на ячейку, использовать знаки: , =, =, , необходимо с знаком амперсанда (&), между этими знаками с самой ссылкой на ячейку.
Важный момент, в данном случае, кавычки возле знака больше (>) ставить нужно вручную.
Для примера, формулы с знаком больше будет выглядеть вот так:
Текстовые значения в качестве критерия поиска функции СЧЁТЕСЛИ в MS Excel.
В качестве критерия поиска в поле Критерий, в диалоговом окне Аргументы функции, можно использовать текстовое значения. Например, можно найти в столбце Значения, количество ячеек, которые не содержат в себе Значение 1. Меняем Диапазон поиска.
Если использовать в качестве критерия не ссылку на ячейку, в которой есть текст, а сам текст, его необходимо заключить в кавычки вручную.
Возможно осуществлять поиск указав в критерии только часть слова или одну букву. Например, у нас есть столбец, в котором указано название мебели.
Найдем количество ячеек, со словом Стол, указав в критерии поиска часть букв из этого слова, которые стоят в начале: Ст
В поле Критерий, в диалоговом окне Аргументы функции, указываем: «Ст*».
Теперь укажем в критерии поиска конец слова. Например, букву ф, из слова шкаф.
В поле Критерий, в диалоговом окне Аргументы функции, указываем: «*ф».
Варианты использования функции СЧЁТЕСЛИ с двумя (несколькими) критериями поиска описаны в статье: Функция СЧЁТЕСЛИ с использованием двух (нескольких) критериев поиска. Описание и примеры.
Выборочные вычисления по одному или нескольким критериям
Имеем таблицу по продажам, например, следующего вида:
Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина «Копейка».
Способ 1. Функция СУММЕСЛИ, когда одно условие
- Диапазон — это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае — это диапазон с фамилиями менеджеров продаж.
- Критерий — это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак — один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву «П», а заканчивается на «В» — критерий П*В. Строчные и прописные буквы не различаются.
- Диапазон_суммирования — это те ячейки, значения которых мы хотим сложить, т.е. нашем случае — стоимости заказов.
Способ 2. Функция СУММЕСЛИМН, когда условий много
При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3—Условие3), и четвертую, и т.д. — при необходимости.
Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться — см. следующие способы.
Способ 3. Столбец-индикатор
Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в «Копейку» и от Григорьева, то в ячейке этого столбца будет значение 1, иначе — 0. Формула, которую надо ввести в этот столбец очень простая:
Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:
Способ 4. Волшебная формула массива
Если вы раньше не сталкивались с такой замечательной возможностью Excel как формулы массива, то советую почитать предварительно про них много хорошего здесь. Ну, а в нашем случае задача решается одной формулой:
После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter — тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.
Способ 4. Функция баз данных БДСУММ
В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев — ячейки, содержащие условия отбора — и указать затем этот диапазон функции как аргумент:
[expert_bq id=»1570″]Метод использования условного оператора ЕСЛИ отличается тем, что для сравнения двух столбцов используется только необходимая для сравнения часть, а не весь массив целиком. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] Использование макросов для сравнения двух столбцов позволяет унифицировать процесс и сократить время на подготовку данных. Решение о том, какой результат сравнения необходимо отобразить, полностью зависит от вашей фантазии и навыков владения макросами. Ниже представлена методика, опубликованная на официальном сайте Микрософт.
Критерий Сравнения в Excel Где Находится
- Диапазон, здесь необходимо указать диапазон ячеек, в которых будет осуществляться поиск ячеек с значениями, которые отвечают критериям поиска. Выбрать необходимый диапазон можно сначала поставив курсор на поле Диапазон, а потом выделив необходимые ячейки.
- Критерий, здесь необходимо указать условие (критерий поиска), согласно которому функция будет искать и подсчитывать количество ячеек в заданном Диапазоне.
Надстройка «Сопоставить столбцы» имеет другое назначение. Её главная задача — выяснить, насколько, в целом, наборы данных (столбцы) схожи или отличны. Надстройка помогает с анализом большого объёма данных, когда вам нужно посмотреть шире, на макро-уровне, напр. ответить на такие вопросы:
Excel: как сделать критерий Счетесли Суммесли (Эксель формула вычисляемый)
Эксель: Счетесли критерий формула. Есть возможность задать определенные условия, называемые критериями. Это происходит благодаря встроенным функциям. Самые распространенные из них СЧЁТЕСЛИ, СЧЁТЕСЛИМН, СУММЕСЛИ, СУММЕСЛИМН. Можно заметить, что их объединяет слово Если. Как они работают, рассмотрим далее.
СЧЕТЕСЛИ.
Данный критерий применяется, когда необходимо подсчитать значения занятые несколькими ячейками, объединенное некоторым условием, то есть критерием. Пишется это так:
4. Программа производит подсчет и выводит результат в элемент листа, который был выделен перед активацией Мастера функций. Как видим, в данном случае результат равен числу 5. Это означает, что в выделенном массиве в пяти ячейках находятся значения превышающие 15000. То есть, можно сделать вывод, что в Магазине 2 в пяти днях из анализируемых семи выручка превысила 15000 рублей.
СЧЁТЕСЛИМН
Следующей функцией, которая оперирует критериями, является СЧЁТЕСЛИМН.
Она также относится к статистической группе операторов. Задачей СЧЁТЕСЛИМН является подсчет ячеек в указанном массиве, которые удовлетворяют определенному набору условий. Именно тот факт, что можно задать не один, а несколько параметров, и отличает этого оператора от предыдущего. Синтаксис следующий:
=СЧЁТЕСЛИМН(диапазон_условия1;условие1;диапазон_условия2;условие2;…)
«Диапазон условия» является идентичным первому аргументу предыдущего оператора. То есть, он представляет собой ссылку на область, в которой будет производиться подсчет ячеек, удовлетворяющих указанным условиям. Данный оператор позволяет задать сразу несколько таких областей.
«Условие» представляет собой критерий, который определяет, какие элементы из соответствующего массива данных войдут в подсчет, а какие не войдут. Каждой заданной области данных нужно указывать условие отдельно, даже в том случае, если оно совпадает. Обязательно требуется, чтобы все массивы, используемые в качестве областей условия, имели одинаковое количество строк и столбцов.
Для того, чтобы задать несколько параметров одной и той же области данных, например, чтобы подсчитать количество ячеек, в которых расположены величины больше определенного числа, но меньше другого числа, следует в качестве аргумента «Диапазон условия» несколько раз указать один и тот же массив.
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». Как нетрудно догадаться, эти значения соответствуют интервалу выручки, превышающую норму для соответствующего магазина.
После того, как был произведен ввод всех необходимых данных (всего 10 полей), жмем на кнопку «OK».
4. Программа производит подсчет и выводит результат на экран. Как видим, он равен числу 3.
Это означает, что в трех днях из анализируемой недели выручка во всех торговых точках превышала установленную для них норму.
Теперь несколько изменим задачу. Нам следует посчитать количество дней, в которых Магазин 1 получил выручку, превышающую 14000 рублей, но меньшую, чем 17000 рублей.
1. Ставим курсор в элемент, где будет произведен вывод на лист результатов подсчета. Нажимаем на пиктограмму «Вставить функцию» над рабочей площадью листа.
2. Так как мы совсем недавно применяли формулу СЧЁТЕСЛИМН, то теперь не обязательно переходить в группу «Статистические» Мастера функций. Наименование данного оператора можно найти в категории «10 недавно использовавшихся». Выделяем его и щелкаем по кнопке «OK».
3. Открывается уже знакомое нам окошко аргументов оператора СЧЁТЕСЛИМН. Ставим курсор в поле «Диапазон условия1» и, произведя зажим левой кнопки мыши, выделяем все ячейки, в которых содержится выручка по дням Магазина 1.
Они расположены в строке, которая так и называется «Магазин 1».
После этого координаты указанной области будут отражены в окне.
Далее устанавливаем курсор в поле «Условие1». Тут нам нужно указать нижнюю границу значений в ячейках, которые будут принимать участие в подсчете. Указываем выражение «>14000».
В поле «Диапазон условия2» вводим тот же адрес тем же способом, который вводили в поле «Диапазон условия1», то есть, опять вносим координаты ячеек со значениями выручки по первой торговой точке.
В поле «Условие2» указываем верхнюю границу отбора: «10.03.2017».
В поле «Диапазон суммирования» нужно указать область, значения которой, отвечающие указанным критериям, будут суммироваться. В нашем случае это значения выручки строки «Магазин1». Выделяем соответствующий массив элементов листа.
После того, как произведено введение всех указанных данных, жмем на кнопку «OK».
4. После этого в предварительно указанный элемент рабочего листа будет выведен результат обработки данных функцией СУММЕСЛИ. В нашем случае он равен 47921,53. Это означает, что начиная с 11.03.2017, и до конца анализируемого периода, общая выручка по Магазину 1 составила 47921,53 рубля.
2. В Мастере функций, прежде всего, выполняем перемещение в блок «Математические», а там выделяем пункт под названием «СУММЕСЛИМН». Производим клик по кнопке «OK».
3. Производится запуск окошка аргументов оператора, наименование которого было указано выше.
Устанавливаем курсор в поле «Диапазон суммирования». В отличие от последующих аргументов, этот единственный в своем роде и указывает на тот массив значений, где будет производиться суммирование подошедших под указанные критерии данных.
Затем выделяем область строки «Магазин1», в которой размещены значения выручки по соответствующей торговой точке.
После того, как адрес отобразился в окне, переходим к полю «Диапазон условия1». Тут нам нужно будет отобразить координаты строки с датами. Производим зажим левой кнопки мыши и выделяем все даты в таблице.
Ставим курсор в поле «Условие1». Первым условием является то, что нами будут суммироваться данные не ранее 09 марта. Поэтому вводим значение «>08.03.2017».
Перемещаемся к аргументу «Диапазон условия2».
Тут нужно внести те же координаты, которые были записаны в поле «Диапазон условия1». Делаем это тем же способом, то есть, путем выделения строчки с датами.
Устанавливаем курсор в поле «Условие2».
Вторым условием является то, что дни, за которые будет суммироваться выручка, должны быть не позже 13 марта. Поэтому записываем следующее выражение: «14000».
После выполнения последнего действия нажимаем на кнопку «OK».
4. Программа выводит результат на лист. Он равен 62491,38. Это означает, что за период с 09 по 13 марта 2017 года сумма выручки при
сложении её за дни, в которых она превышает 14000 рублей, составила 62491,38 рубля.
П.12. Решение прикладных задач средствами EXCEL. УМК Математ основы пс
В ячейке С2 введите следующую формулу =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A2;$B500:$B$11;0));»»;A2) и протяните ее до ячейки С11. Данная формула последовательно просматривает наличие каждого элемента из столбца A в столбце B и возвращает значение элемента, если оно было найдено в столбце B.
