Excel диапазон по условию
Обновлено: 07.07.2024
Для создания раскрывающихся списков будем использовать именованные динамические диапазоны, которые позволяют добавлять и удалять элементы в исходных наборах без внесения изменений в формулы.
Для динамических диапазонов создаем новый рабочий лист Excel и переименовываем его в «Списки». Заполняем столбцы наборами элементов:
На вкладке «Формулы» ленты инструментов нажимаем ссылку «Присвоить имя»:
В поле «Имя» пишем «Группа», в поле «Диапазон» вставляем формулу =СМЕЩ(Списки!$A$2;0;0;СЧЁТЗ(Списки!$A:$A)-1;1) и нажимаем кнопку «OK». Первый именованный динамический диапазон создан.
Повторяем эти действия для создания остальных именованных диапазонов, формулы для которых перечислены в таблице:
| Имя | Диапазон |
|---|---|
| Группа | =СМЕЩ(Списки!$A$2;0;0;СЧЁТЗ(Списки!$A:$A)-1;1) |
| Грибы | =СМЕЩ(Списки!$B$2;0;0;СЧЁТЗ(Списки!$B:$B)-1;1) |
| Насекомые | =СМЕЩ(Списки!$C$2;0;0;СЧЁТЗ(Списки!$C:$C)-1;1) |
| Рыбы | =СМЕЩ(Списки!$D$2;0;0;СЧЁТЗ(Списки!$D:$D)-1;1) |
Все имена диапазонов можно просмотреть в диспетчере имен («Формулы» — «Диспетчер имен»):
В диспетчере имен доступно создание, изменение и удаление имен диапазонов.
Создание раскрывающегося списка
Создаем первый раскрывающийся список, от выбора значения в котором будет зависеть набор элементов второго выпадающего списка. Привяжем его к динамическому диапазону «Группа».
На листе Excel, где будет таблица с раскрывающимися списками, называем первые два столбца «Группа» и «Вид». Выбираем ячейку под названием столбца «Группа» и нажимаем ссылку «Проверка данных» на вкладке «Данные» ленты инструментов.
В открывшемся окне «Проверка вводимых значений» выбираем тип данных «Список», указываем источник «=Группа» и нажимаем кнопку «OK»:
Первый выпадающий список создан. Скопировать его в другие ячейки столбца «Группа» можно простым протягиванием. Также, при присвоении источника данных диапазону ячеек, раскрывающийся список будет добавлен во все ячейки.
Раскрывающийся список по условию
Для создания выпадающего списка по условию выбираем ячейку под названием столбца «Вид» и нажимаем ссылку «Проверка данных» на вкладке «Данные» ленты инструментов. В открывшемся окне «Проверка вводимых значений» выбираем тип данных «Список», указываем источник =ЕСЛИ(A2="Грибы";Грибы;ЕСЛИ(A2="Насекомые";Насекомые;ЕСЛИ(A2="Рыбы";Рыбы;Списки!$E$1))) и нажимаем кнопку «OK».
Если в первом раскрывающемся списке (Группа) не будет выбрана ни одна позиция, во втором выпадающем списке (Вид) будет отображаться значение ячейки по адресу Списки!$E$1 (Выберите группу).
Смотрите, как для раскрывающегося списка можно использовать динамический диапазон на основе умной таблицы.
В данной статье рассмотрим функцию MS Excel, которая называется ЕСЛИ. Данная функция позволяет проверить, соответствует ли содержащиеся в указанной ячейки данные заданному критерию или нет. В зависимости от результата проверки функция выводит заданное значение. Чтобы понять суть и возможности функции ЕСЛИ, рассмотрим примеры с ее использованием.
Функция ЕСЛИ в MS Excel с одним условием.
Функцией ЕСЛИ в Excel, первый пример.
Рассмотрим простой пример применения функции ЕСЛИ в MS Excel. У нас есть столбец №1, в котором содержаться числа от 1 до 10.
Необходимо проверить, соответствует ли числа находящиеся в столбце №1 нашему критерию. Критерий: является ли числа находящиеся в столбце №1 числами, значения которых больше числа 4.
Взываем функцию ЕСЛИ в ячейку С3.
В диалоговом окне Аргументы функции видим три поля для заполнения:
Нажимаем ОК в диалоговом окне Аргументы функции.
Как видим, число содержащиеся в ячейки В3 не является числом, которое больше числа 4. Функция ЕСЛИ вернула значение_если_ложь (неправда). Протянем функцию ЕСЛИ вниз по столбцу, чтобы проверить значения находящиеся в остальных ячейках столбца №1, на соответствие нашему критерию (логическое_выражение).
Как видно из результата, ячейки в диапазоне В7:В12 содержат в себе числа, которые больше числа 4. Функция ЕСЛИ вернула значение_если_истина (правда).
Функция ЕСЛИ в Excel, второй пример.
Рассмотрим еще один пример. Предположим, что у нас есть таблица. В ней фамилии студентов, которые сдавали зачет. Часть студентов сдали зачет, а часть не сдали.
Теперь добавим в нашу таблицу столбец: Допуск к экзамену. Используем функцию ЕСЛИ для того, чтобы определить допущен студент к экзамену или нет. Диалоговое окно Аргументы функции будет выглядеть вот так:
Важно: текстовый критерий в поле логическое_выражение должен быть заключен в кавычки. Иначе функция выдаст ошибку.
Если студен сдал зачет, он допущен к экзамену, если нет, то не допущен. Нажимаем ОК в диалоговом окне Аргументы функции и протягиваем функцию ЕСЛИ вниз, по всему столбцу Допуск к экзамену. Получаем:
Видим, что в зависимости от результатов сдачи зачета, студент допущен к экзамену, либо не допущен.
Обращаю внимание, что при работе с функцией ЕСЛИ, можно использовать разные варианты равенств: больше (>), меньше(<), больше или равно (>=), меньше или равно (<=), равно (=), не равно (<>).
Функция ЕСЛИ в MS Excel с несколькими условиями.
Предположим, что у нас есть таблица с результатами сдачи контрольной работы студентами ВУЗа. Результаты контрольной работы оцениваются в баллах. От 0 до 100. Где все, что выше 90 баллов, отлично. Выше 80 баллов, это хорошо. Выше 70 баллов, это удовлетворительно. Ниже 70 баллов, это плохо.
Формула функции ЕСЛИ будет выглядеть вот так:
Где ячейка С3, первая из проверяемых ячеек в столбце Оценка. Далее функция протянута вниз по столбцу Результат, до конца таблицы.
Внимание: в данном случае формула функции ЕСЛИ прописана в ручную, прямо в строе формулы.
Таблица выглядит вот так:
В столбце Результат, в зависимости от количества баллов, функция ЕСЛИ вернула значение, согласно заданному критерию в формуле функции.
Использование операторов «И» и «ИЛИ» в функции ЕСЛИ в MS 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. Меняем Диапазон поиска.
Формула функции будет выглядеть вот так:
Кавычки возле знака не равно (<>) ставить вручную.
Если использовать в качестве критерия не ссылку на ячейку, в которой есть текст, а сам текст, его необходимо заключить в кавычки вручную.
Формула функции будет выглядеть вот так:
Возможно осуществлять поиск указав в критерии только часть слова или одну букву. Например, у нас есть столбец, в котором указано название мебели.
Найдем количество ячеек, со словом Стол, указав в критерии поиска часть букв из этого слова, которые стоят в начале: Ст
Формула функции будет выглядеть вот так:
Теперь укажем в критерии поиска конец слова. Например, букву ф, из слова шкаф.
Формула функции будет выглядеть вот так:
Кавычки возле Ст* и *ф, ставятся автоматически.
Варианты использования функции СЧЁТЕСЛИ с двумя (несколькими) критериями поиска описаны в статье: Функция СЧЁТЕСЛИ с использованием двух (нескольких) критериев поиска. Описание и примеры.
В данной статье, на примерах, рассмотрим как работает функция ПОИСКПОЗ в Excel. Данная функция осуществляет поиск искомого значения в указном диапазоне ячеек. Как результат, функция возвращает позицию искомого значения в указанном диапазоне ячеек. При этом поиск с помощью функции ПОИСКПОЗ может осуществляться только в одном столбце или в одной строке.
Как вызвать функцию ПОИСКПОЗ в Excel.
Перед тем как вызвать функцию ПОИСКПОЗ необходимо определить ячейку, в которой она будет располагаться. Это можно сделать щелкнув левой клавишей мыши по выбранной ячейки. Далее нажимаем на кнопку Вставить функцию. Открывается диалоговое окно Вставка функции. Здесь в поле Выберите функцию: выбираем нужную нам функцию. Если в предложенном перечни функций интересующей нас функции нет, необходимо воспользоваться поиском.
Использование функции ПОИСКПОЗ для поиска позиции ячейки с текстовым значением.
У нас есть список имен. Данные имена расположены в одном столбце. Наша задача найти позицию (номер по порядку) определенного имени в этом списке. После того, как мы выбрали необходимую нам функцию в диалоговом окне Вставка функции, откроется диалоговое окно Аргументы функции.
В данном диалогом окне три поля:
ВАЖНО: позиция (порядковый номер) искомого значения в анализируемом диапазоне является относительным, так как функция рассчитывает позицию, отсчитывая порядковый номер от начала анализируемого диапазона ячеек.
Нажимаем ОК.
Функция вернула значение 5. Это значит, что имя Петр находиться в пятой по счету ячейки, в столбце В2:В13. При этом отсчёт видеться от ячейки В2.
Данный список имен можно разместить в одной строке и применив функцию ПОИСКПОЗ, по такому же алгоритму, найти позицию (номер по порядку) того или иного значения.
Использование функции ПОИСКПОЗ для поиска позиции ячейки с числовым значением.
Тип_сопоставления: 0. Функция ПОИСКПОЗ в Excel.
Нажимаем ОК.
Функция вернула значение 8. Это значит, что число 55 находиться в восьмой по счету ячейки.
Тип_сопоставления: 1. Функция ПОИСКПОЗ в Excel.
Нажимаем ОК.
Функция вернула значение 6. Это позиция числа 32. Поскольку в анализируемом диапазоне нет числа 40, ПОИСКПОЗ вернула позицию числа, которое меньше числа 40 (Искомое_значение). При этом оно наибольшее среди чисел, которые меньше числа 40.
Нажимаем ОК.
Функция вернула значение 6. Это позиция числа 45. Поскольку в анализируемом диапазоне нет числа 40, ПОИСКПОЗ вернула позицию числа, которое больше числа 40 (Искомое_значение). При этом оно наименьшее среди чисел, которые больше числа 40.
Использование функции ПОИСКПОЗ с ссылкой на ячейку.
Вернемся к списку имен. В диалоговом окне Аргументы функции, в поле Искомое_значение, укажем не само значение, а ссылку на ячейку. Например, ячейку: С2. Просматриваемый _массив: В2:В13. Тип_сопоставления: 0.
Нажимаем ОК.
Теперь в ячейку С2 вводим значение (имя): Глеб.
Функция вернула нам позицию имени Глеб в анализируемом диапазоне ячеек. Таким образом можно менять Искомое_значение, при этом не меняя его в самой формуле функции ПОИСКПОЗ.
Читайте также: