Условия excel: Функция УСЛОВИЯ — Служба поддержки Майкрософт

Содержание

Функция УСЛОВИЯ — Служба поддержки Майкрософт

Excel

Формулы и функции

Логические

Функция УСЛОВИЯ

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Еще…Меньше

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

Примечание: Эта функция доступна в Windows или Mac, если у вас есть Office 2019 или у вас есть подписка На Microsoft 365. Если вы являетесь подписчикомMicrosoft 365, убедитесь, что у вас установлена последняя версия.

Простой синтаксис

Как правило, синтаксис функции ЕСЛИМН выглядит следующим образом:

=ЕСЛИМН(условие1; значение1; [условие2; значение2]; [условиe3; значение3])

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

Синтаксис

ЕСЛИМН(лог_выражение1;значение_если_истина1,[лог_выражение2;значение_если_истина2],[лог_выражение3;значение_если_истина3],…)

Аргумент

Описание

лог_выражение1 (обязательно)

Условие, принимающее значение ИСТИНА или ЛОЖЬ.

значение_если_истина1 (обязательно)

Результат, возвращаемый, если условие лог_выражение1 принимает значение ИСТИНА. Может не указываться.

лог_выражение2…лог_выражение127 (необязательно)

Условие, принимающее значение ИСТИНА или ЛОЖЬ.

значение_если_истина2…значение_если_истина127 (необязательно)

Результат, возвращаемый, если условие лог_выражениеN принимает значение ИСТИНА. Каждый аргумент значение_если_истинаN соответствует условию лог_выражениеN. Может не указываться.

Пример 1

Формула для ячеек A2:A6:

Расшифровка: ЕСЛИ(значение A2 больше 89, возвращается «A», если значение A2 больше 79, возвращается «B», и т. д. Для всех значений меньше 59 возвращается «F»).

Пример 2

В ячейке G7 находится следующая формула:

Расшифровка: ЕСЛИ(значение в ячейке F2 равно 1, возвращаемое значение в ячейке D2, если значение в ячейке F2 равно 2, возвращается значение в ячейке D3, и т. д. Если ни одно из других условий не выполняется, возвращается значение в ячейке D8.

Замечания

Чтобы задать результат по умолчанию, введите значение ИСТИНА для последнего аргумента лог_выражение. Если ни одно из других условий не выполняется, возвращается соответствующее значение. Это показано в строках 6 и 7 примера 1 (с 58 баллами).

 Если условие лог_выражение указывается без соответствующего аргумента значение_если_истина, появляется сообщение «Указано недостаточно аргументов».

 Если аргумент лог_выражение принимает значение, отличное от ИСТИНА или ЛОЖЬ, функция возвращает ошибку #ЗНАЧ!.

 Если условия со значением ИСТИНА не найдены, возвращается ошибка #Н/Д!

Дополнительные сведения

Вы всегда можете задать вопрос специалисту Excel Tech Community или попросить помощи в сообществе Answers community.

Статьи по теме

Функция ЕСЛИ

Усложненные функции ЕСЛИ: как работать с вложенными формулами и избежать ошибок

Обучающие видео: усложненные функции ЕСЛИ

Функция СЧЁТЕСЛИ подсчитывает значения с учетом одного условия

Функция СЧЁТЕСЛИМН подсчитывает значения с учетом нескольких условий

Функция СУММЕСЛИ суммирует значения с учетом одного условия

Функция СУММЕСЛИМН суммирует значения с учетом нескольких условий

Функция И

Функция ИЛИ

Функция ВПР

Полные сведения о формулах в Excel

Рекомендации, позволяющие избежать появления неработающих формул

Поиск ошибок в формулах

Логические функции

Функции Excel (по алфавиту)

Функции Excel (по категориям)

Расширенный фильтр Excel – великолепная альтернатива регулярным выражениям

Текст представляет собой адаптированный перевод статьи Annie Cushing (Энни Кашинг), оригинал — Advanced Filters: Excel’s Amazing Alternative To Regex. Курсивом выделяются названия пунктов меню для Excel 2013 и Mac 2011 или термины.

Всегда казалось странным, что Excel не поддерживает регулярные выражения (кои в среде программистов зовут регулярками или регэкспами). С их помощью делают сложную сортировку и фильтрацию. Инструмент SeoTools plugin for Excel поддерживает регулярки, но, как и большинство дополнительных ресурсов для Excel, работает только на платформе ПК. Эта статья предназначена, в основном, для пользователей Mac (мы не рекламируем SeoTools).

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

Немного теории

Подготовка к работе с расширенным фильтром

Для дальнейшего нам нужен «подопытный» — база данных, над которой будем ставить эксперименты. Мы её оставили отформатированной под таблицу. Но это лишь для «красоты», поскольку применение расширенного фильтра удалит стандартные табличные фильтры. Последние можно включить повторно по окончании получения наборов данных.

Расширенный фильтр находится:

Для ПК: Данные > Сортировка и фильтр >Дополнительно.

Для Мак: Данные > Сортировка и фильтр > Фильтр > Расширенный фильтр

Пользователи Mac могут щёлкнуть ПКМ по таблице и выбрать нужный пункт из контекстного меню. На ПК такой возможности нет.

Ещё один момент — база данных должна иметь заголовки столбцов, как показано ниже.

Небольшой совет

Подробная информация о фильтрах приведена ниже в пунктах Операторы, Заголовки и Множественные условия.

Базовые операторы

В дополнение к вышеупомянутым подстановочным знакам приведём некоторые операторы, необходимые при работе с фильтром:

= равенство, т.е. левая и правая часть должна совпасть: =билет [включит все ячейки, содержащие билет]
<> не равно, левая и правая часть не должны совпасть: <> https://www. aviasales.ru/mag* [исключаем данный адрес (блог)]
‘ апостроф преобразует формулу в текст, если поставить его первым в ячейке: ‘=купить авиабилет
> больше чем: >500
>= больше или равно: B4-C4>=3
< меньше чем: C6<D6
<= меньше или равно: <=3

Заголовки

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

Набор условий

Вот три базовых конструкции для множественных условий:

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

Фильтр отберёт строки, для которых выполняется хотя бы одно из условий, т.е. либо Позиция меньше 11, либо Количество больше 1000. Оператор ИЛИ можно использовать и для одного столбца. В таком случае, все критерии располагаются друг под другом в целевом столбце.

И: Если необходимо одновременное соблюдение всех критериев, применяется логический оператор И. Для этого критерии располагают на одной строке.

Данный пример отбирает строки, где в столбце Ключевые слова встречается слово «купить» и нет слова «самолёт», также значение в Позиция больше 7, а значение в Среднемесячное количество больше 1000. Помним, что звёздочка обозначает любое количество знаков. В нашем примере будут отобраны строки, в которых встречается «купить» на любой позиции внутри ячейки. Обратите внимание, что отсев идёт по слову «самолёт», «самолет» вполне проходит.

ОБА ОПЕРАТОРА: Можно блеснуть своим умением, применив оба оператора для установки критериев.

Такая таблица условий слегка обескураживает. Но этот фильтр всего лишь отбирает строки, в которых присутствует слово «купить» или «аэрофлот», при этом нет слов «самолет» или «самолёт», а значения последнего столбца больше 1000.

Примечание: Чтобы получить текст вместо формул, достаточно поставить апостроф перед операторами сравнения: ‘=купить или ‘<>самолёт. Майкрософт на своём сайте советует излишне сложный способ, предлагая заключить в двойные кавычки всё выражение, а перед ним ставить знак равенства: =»=купить«.

В файле справки самого Excel написано, что преобразовать формулу в текст можно, поставив первым знаком апостроф. Но на сайте предлагают весьма замысловатый путь.

Промежуток: Если необходимо получить данные, лежащие в некотором промежутке, можно применить следующий приём:

Эти условия отберут ключевые слова, для которых Среднемесячное количество находится в промежутке от 3700 до 10000.

Формулы: Это действительно мощный инструмент. Вместо таблицы критериев можно использовать формулы.

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

Пара моментов, на которые необходимо обращать внимание при использовании формул:

Формула должна возвращать логическое значение ЛОЖЬ или ИСТИНА;
Заголовки над формулами должны отличаться от заголовков базы данных, можно вообще использовать пустую строку;
Формула должна ссылаться на ячейки первой строки под заголовками базы данных;
Ссылки на проверяемые ячейки базы должны быть относительными: вида С4, при абсолютных, таких как $C$4 — значение формул будет статичным, т. е. всегда ЛОЖЬ или всегда ИСТИНА.

Общий алгоритм

Пошаговый алгоритм работы с расширенным фильтром (полагаем, что данные и табличка условий уже подготовлена):

Шаг 1: Как упоминалось ранее, щёлкните любую ячейку внутри подготовленного набора данных и перейдите:

Данные > Сортировка и фильтр >Дополнительно для ПК. Для Мак: Данные > Сортировка и фильтр > Фильтр > Расширенный фильтр.

Откроется меню. Здесь приведён скриншот Excel 2013, но все пункты совпадают с Mac.

Шаг 2: Выберите, где будут размещаться отобранные данные. В большинстве случаев разумнее определить «другое место», это оставит неизменным исходный массив данных. Но выбор за вами.

Шаг 3: Для исходного диапазона Excel по умолчанию отбирает всю «таблицу». Это можно изменить, исключив ненужные столбцы. Но Excel позволит выбрать только прямоугольный диапазон. Чтобы отобрать столбцы с пропусками, введите названия целевых столбцов в свободных ячейках и укажите их адрес для диапазона вывода. В Дополнительных примерах приводится подробное пояснение.

Шаг 4: Для установки диапазона условий щёлкните внутри поля и выделите соответствующий диапазон на листе. Если нужно свернуть меню, нажмите на значок выбора диапазона справа от поля.

Шаг 5: Если выбран пункт Cкопировать результат в другое место, укажите адрес в поле Поместить результат в диапазон. Можно указать единичную ячейку, тогда она станет верхней левой выводимого диапазона, или выбрать ячейки с введёнными названиями нужных столбцов.

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

Шаг 7: Жмём OK, смотрим на результат.

Шаг 8: Если список фильтровался на месте, можно сбросить фильтр, нажав Очистить, но это верно для ПК. Данный пункт расположен выше пункта Дополнительно. На Маке присутствует аналог — Очистить фильтр, но он недоступен. Ещё один довод в пользу фильтрации на новое место. Чтобы сбросить фильтр нужно отменить действие — нажать Command-Z, или подключить стандартный фильтр.

Дополнительные примеры

1. Исключить слова

<> В данном контексте значит не содержит

2. Получить ключевые слова первой десятки

Заголовки двух столбцов скопированы для диапазона вывода

3. Вывести ключевые слова, для которых Cреднемесячное количество лежит в промежутке [5000, 10000]

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

4. Содержит хотя бы одно из слов: «купить», «аэрофлот». Не содержит слово «туту», и значение в столбце Среднемесячное количество больше 500

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

5. Ключевые слова содержат «аэрофлот» и Среднемесячное количество больше 500

Как и в предыдущем примере, первый знак в ячейке — апостроф.

6. Ключевые слова, для которых Позиция меньше 3, или Среднемесячное количество больше 1000

Для диапазона условий ИЛИ наличие пустого столбца — не помеха.

7. Ключевые слова содержат, по крайней мере, 1 знак перед словом «аэрофлот»

Сочетание ?* требует наличия хотя бы 1 знака, но может содержать больше (эквивалент .+ в регулярках)

8. Ключевые слова, для которых Среднемесячное количество больше 8000

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

9. Ключевые слова, для которых Среднемесячное количество больше 10000, а Конкурентность меньше 0,75

Используем функцию И в формуле.

10. Ключевые слова первой десятки по критерию Среднемесячное количество

Формула отбирает ячейки, значение которых больше или равно 10-му по величине числу. Само число получаем функцией НАИБОЛЬШИЙ. Обратите внимание, сравниваемая ячейка получена относительной ссылкой, а диапазон поиска для функции — абсолютной.

Заключительные рекомендации

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

Если случайно включить в диапазон условий пустые ячейки, расположенные под заполненными строками, то Excel воспримет их как условия, включающие всё.
Если вывод результатов идёт на другой лист, то запускать фильтр нужно с этого листа. Excel разрешает вывод результатов только на тот лист, с которого запущен фильтр.
Если фильтрации идёт на месте, то не получится последовательно применить несколько фильтров. Применение нового аннулирует предыдущие результаты.
Как уже писалось выше, пункт Очистить фильтр не работает в Excel 2011. Для сброса фильтра используйте отмену действия (Command-Z) или подключите обычный фильтр. В 2016 это исправили (прим. пер).
При выводе результатов в другое место полезно совместное применение оператора <>. Кроме того, можно транспонировать полученные данные: выделить, Копировать > Специальная вставка > флажок транспонировать.

Совет для продвинутых пользователей Excel

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

Однако существует хороший выход — макрос. Достаточно подключить несложный макрос и назначить его запуск по сочетанию клавиш или по щелчку на кнопке. Вместо кнопки можно использовать картинку. Например, такую:

Если вы затратите немного времени на изучение расширенного фильтра, то поймёте простоту и мощь данного инструмента.

8.3 Условные функции — Excel для принятия решений

Мы использовали функции, начиная с главы 2, для возврата значений на основе математических и статистических функций, таких как СУММ, СРЗНАЧ и СЧЁТ. В главе 3 мы узнали, как настроить логические тесты, заставить Excel оценить наши условия для всех элементов в наших диапазонах, а затем обработать все значения для нас. В главе 5 мы изучили, как интерпретировать направления, настраивать параметры и фильтровать наши данные в зависимости от множества критериев, используя таблицы Excel, фильтры и слайсеры.

В этой главе мы продолжим нашу работу с набором данных системы показателей колледжа, с которым мы познакомились в главе 6. Здесь мы использовали сводные таблицы и сводные диаграммы, чтобы обобщить наш набор данных, чтобы получить представление о параметрах, которые описывают стоимость образования и его отдачу, тенденции зачисления в различных типах программ, разбивка студенческого состава и многое другое. В этой главе мы рассмотрим альтернативные способы получения тех же ответов, используя комбинацию функций и формул Excel, которые мы изучали в предыдущих главах.

В таблице 1 ниже представлен обзор функций, которые мы будем использовать для возврата значений в Excel. Обратите внимание на шаблон от СУММ до СУММЕСЛИ и СУММЕСЛИМН, комбинацию СУММ + ЕСЛИ или комбинацию СУММ + ЕСЛИ, ЕСЛИ во множественном числе, если хотите. Структура этих функций подразумевает, что мы СУММИМ все, ЕСЛИ они удовлетворяют одному условию или критерию. В качестве альтернативы, мы будем суммировать вещи, используя окончание IFS, когда они должны соответствовать множественным условиям или критериям. Шаблон в составе функций одинаков для наших основных математических и статистических функций. AVERAGE и COUNT имеют так называемые условные версии, которые позволяют нам устанавливать параметры для усреднения или подсчета наших значений на основе.

СУММА	Добавляет значения, которые вы вводите в формулу.
СУММФ	Добавляет значения, соответствующие одному критерию.
СУММЕСЛИМН	Добавляет все значения, соответствующие нескольким критериям.
СРЕДНЕЕ	Вычисляет среднее арифметическое диапазона значений.
СРЕДНЕЕСЛИ	Возвращает среднее значение всех ячеек, соответствующих одному критерию.
СРЕДНИЕ	Возвращает среднее значение всех ячеек, соответствующих нескольким критериям.
СЧЕТ	Подсчитывает количество ячеек, содержащих числа.
СЧЁТЕСЛИ	Подсчитывает ячейки по одному критерию.
СЧЁТЕСЛИ	Подсчитывает ячейки по нескольким критериям.

Таблица 1: Математические и статистические функции.

=СУММЕСЛИ(диапазон, критерии, [сумма_диапазон])

СУММЕСЛИ по существу спрашивает: что вы хотите сложить, исходя из каких критериев. Синтаксис СУММЕСЛИ начинается с нашей функции, затем в круглых скобках мы должны сообщить Excel, что такое диапазон значений (текст или числа, пробелы будут игнорироваться), мы хотим, чтобы он складывался на основе каких критериев . Наши критерии могут быть одним значением, например числом 42 или «> 42», ссылкой на ячейку, где находится наш критерий. Если вы используете текст или операторы =, >, >= и т. д., убедитесь, что они заключены между ” “, иначе Excel вернет ошибку. Аргумент [SUM_RANGE] в квадратных скобках указывает, что эта часть аргумента является необязательной, мы можем использовать ее, если хотим сложить что-то кроме диапазон мы указали ранее.

Например, мы хотим знать, сколько студентов в штате Техас. В предыдущих главах мы изучили различные способы ответа на этот вопрос. Теперь мы хотим использовать нашу условную функцию SUM, чтобы вернуть это значение.

Откройте файл Excel с данными оценочной карты колледжа. (Вы можете скачать новую копию отсюда.)
Вставьте несколько дополнительных строк над таблицей данных. Подойдет три или четыре.
Учитывая, что в этом наборе данных более 120 столбцов, вы можете выбрать, щелкнуть правой кнопкой мыши и скрыть столбцы, которые в данный момент не используете.
Преобразуйте свой диапазон в таблицу Excel, чтобы в формуле использовались ваши имена полей (заголовки столбцов)
Щелкните любую из ячеек над первыми несколькими столбцами и начните ввод формулы, введя знак =.
Теперь вам нужно указать Excel, что вы хотите, чтобы он просматривал диапазон сокращений штатов (STABBR), используйте TX в качестве критерии , затем СУММУЛИРУЙТЕ значения в столбце, показывающем количество студентов (UGDS). Попробуйте сделать это самостоятельно или воспользуйтесь рисунком ниже. Обратите внимание, как структура таблицы Excel позволяет легко увидеть, на какой столбец вы ссылаетесь, в отличие от использования ссылок на диапазоны.
Рис. 1. Функция СУММЕСЛИ в использовании.
Обратите внимание на цветные границы вокруг каждого активного диапазона в формуле.
Теперь продолжайте и проверьте ответ, используя другие способы получения правильного ответа. Таким образом, вы можете подтвердить, что ваша формула выполнила свою работу. Вы можете создать таблицу Excel и отфильтровать ее по Техасу, а затем использовать итоговую строку, чтобы СУММИТЬ количество студентов. Вы также можете создать сводную таблицу, используя поля «Сокращение штата» и «Количество студентов», чтобы вернуть выходные данные. Совпадают ли ваши ответы?
Сделайте еще несколько вариантов ответа на этот вопрос в зависимости от городов, штатов, регионов, типов МЕСТНОСТИ и многого другого!

Функция СУММЕСЛИМН суммирует все аргументы, соответствующие нескольким критериям (support. office.com).

Синтаксис для СУММЕСЛИМН начинается с диапазона , который мы хотим сложить, затем мы должны указать каждый диапазон с соответствующими критериями.

=СУММЕСЛИМН(сумма_диапазон, критерий_диапазон1, критерий1, [критерий_диапазон2, критерий2], …)

Используйте набор данных College Scorecard, чтобы узнать количество студентов в городе Хьюстон, штат Техас, зачисленных на двухгодичные программы обучения. Обратитесь к своему словарю данных, чтобы выбрать поля, которые необходимо включить в расчет. Перечислите их на листе бумаги или в простом файле на своем компьютере, который вы можете поместить рядом с файлом данных вашей оценочной карты колледжа.

Если вы застряли, вы можете обратиться к этому снимку экрана. (Номера таблиц [Таблица 1, Таблица 2] будут отражены, если вы преобразуете обратно в диапазон, чтобы свести данные, а затем заново создадите таблицу…) Когда закончите, используйте таблицу Excel или сводную таблицу, чтобы получить тот же ответ.

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

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

Синтаксис COUNTIF начинается со знака равенства, за которым следует наша функция, а затем в круглых скобках мы должны указать Excel, какой диапазон значений мы хотим, чтобы он подсчитывал, на основе каких критериев . Как и в случае СУММЕСЛИ, наши критерии могут быть одним значением, например, числом 42 или «> 42», ссылкой на ячейку, где находится наш критерий. Если вы используете текст или операторы =, >, >= и т. д., убедитесь, что они заключены между ” “, иначе Excel вернет ошибку. У нас нет необязательных аргументов, как в случае с СУММЕСЛИ.

Мы хотим узнать, сколько высших учебных заведений в штате Техас. Давайте воспользуемся некоторыми пустыми ячейками, которые мы ранее вставили в наш лист над нашей таблицей Excel, чтобы выяснить это!

Щелкните любую из ячеек над первыми несколькими столбцами и начните ввод формулы, введя знак =.
Теперь вам нужно указать Excel, что вы хотите, чтобы он СЧИТАЛ. Используйте диапазон сокращений штатов (STABBR) и TX в качестве критериев . Попробуйте сделать это самостоятельно или используйте рисунок ниже. Обратите внимание, как структура таблицы Excel позволяет легко увидеть, на какой столбец вы ссылаетесь, в отличие от использования ссылок на диапазоны.
Рис. 2. Функция СЧЁТЕСЛИ в использовании.
Обратите внимание на цветные границы вокруг каждого активного диапазона в формуле.
Еще раз проверьте ответ, используя другие способы получения правильного ответа. Таким образом, вы можете подтвердить, что ваша формула выполнила свою работу. Вы можете отфильтровать свою таблицу Excel для Техаса, а затем использовать итоговую строку, чтобы СЧИТАТЬ названия учреждений или аббревиатуры штатов. Вы также можете создать сводную таблицу, используя поля «Сокращение штата» и «Названия учреждений», чтобы получить результат. Совпадают ли ваши ответы?
Сделайте еще несколько вариантов ответа на этот вопрос в зависимости от городов, штатов, регионов, типов МЕСТНОСТИ и многого другого!

Функция СЧЁТЕСЛИМН применяет критерии к ячейкам в нескольких диапазонах и подсчитывает количество совпадений всех критериев (support. office.com).

=СЧЕТЧИСЛ(диапазон_критериев1, диапазон_критериев1, [диапазон_критериев2, критерии2]…)

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

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

Еще упражнения СЧЁТЕСЛИМН:

Сколько в Аризоне онлайновых учебных заведений?
Сколько учебных заведений, в которых более 50% студентов принимают федеральный кредит и более 50% получают грант PELL в штате Калифорния?
Сколько учебных заведений находится в субрегионе восток-север-центр в крупных городах?

=СРЗНАЧЕСЛИ(диапазон, критерии, [средний_диапазон])

Синтаксис СРЗНАЧЕСЛИ будет вести себя аналогично СУММЕСЛИ. В скобках мы должны указать Excel, что такое диапазон значений, которые соответствуют нашим критериям , затем в необязательном аргументе в скобках мы указываем, какой диапазон мы хотим усреднить, если это необходимо. Например, мы хотим знать, каков средний % студентов, обучающихся по бизнес-программам в Техасе. Давайте используем некоторые из оставшихся пустых ячеек, которые мы вставили в наш лист над нашей таблицей Excel ранее, чтобы выяснить это!

Щелкните любую из ячеек над первыми несколькими столбцами и начните ввод формулы, введя знак =.
Теперь вам нужно указать Excel, что вы хотите использовать СРЕДНЕЕ значение. Используйте диапазон сокращений штатов (STABBR) и TX в качестве критериев . Попробуйте сделать это самостоятельно или воспользуйтесь рисунком ниже. Обратите внимание, как структура таблицы Excel позволяет легко увидеть, на какой столбец вы ссылаетесь, в отличие от использования ссылок на диапазоны.
Рисунок 3. AVERAGEIF в использовании.
Обратите внимание на цветные границы вокруг каждого активного диапазона в формуле.
Еще раз проверьте ответ, используя другие способы получения правильного ответа. Таким образом, вы можете подтвердить, что ваша формула выполнила свою работу. Вы можете отфильтровать свою таблицу Excel для Техаса, а затем использовать итоговую строку, чтобы СЧИТАТЬ названия учреждений или аббревиатуры штатов. Вы также можете создать сводную таблицу, используя поля «Сокращение штата» и «Названия учреждений», чтобы получить результат. Совпадают ли ваши ответы?
Сделайте еще несколько вариантов ответа на этот вопрос в зависимости от городов, штатов, регионов, типов МЕСТНОСТИ и многого другого!

Функция СРЗНАЧЕСЛИМН возвращает среднее (среднее арифметическое) всех ячеек, соответствующих нескольким критериям (источник: support. office.com).

=СРЗНАЧIFS(средний_диапазон, критерий_диапазон1, критерий1, [критерий_диапазон2, критерий2], …)

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

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

Больше упражнений AVERAGEIFS:

Каков средний процент студентов, изучающих психологию в штате Калифорния?
Какой средний балл ACT(SAT) в учебных заведениях с 4-летним обучением в штате Нью-Йорк?
Каков средний процент студентов, которые зарабатывают 25 тысяч или более через 6 лет после выпуска в Аризоне в онлайн-университетах?

Глава 8. 3 от Emese Felvegi лицензируется в соответствии с CC BY 4.0

Media Attributions

СУММЕСЛИ
СЧЁТЕСЛИ
СРЗНАЧСЛИ

Как использовать функцию ЕСЛИ с 3 условиями в Excel

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

Значение функции ЕСЛИ будет следующим:

=ЕСЛИ(логическая_проверка, [значение_если_истина], [значение_если_ложь])

У нас есть 3 аргумента для функции ЕСЛИ:

Логическая проверка : В этом аргументе , мы узнаем, выполнено ли условие, и сравниваем данные.

Значение, если оно истинно : Если условие соответствует приведенному выше аргументу, Excel должен вернуть определенное значение.

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

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

Метод 1: вложенная функция ЕСЛИ

=ЕСЛИ(C2<15, «Плохо», ЕСЛИ(C2<20, «ОК », ЕСЛИ(C2<25, «Хорошо», «Отлично»))

Метод 2: функция ЕСЛИ с логикой И

=ЕСЛИ(И(A2=»Mavs», B2=»Guard», C2> 25), «Да», «Нет»)

Метод 3: функция ЕСЛИ с логикой ИЛИ

= ЕСЛИ (ИЛИ (A2 = «Mavs», B2 = «Guard», C2> 25), «Да», «Нет»)

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

Пример 1. Вложенная функция ЕСЛИ

Если в столбце «Очки» ввести следующую формулу в ячейку D2, она вернет значение на основе значения для каждого игрока.

Формула: =ЕСЛИ(С2<15, «Плохо», ЕСЛИ(С2<20, «ОК», ЕСЛИ(С2<25, «Хорошо», «Отлично»))))

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

Эта формула может выполнять следующие действия:

Если в столбце Баллы значение не превышает 15, будет возвращено значение Плохо.

С другой стороны, если значение в столбце Points не превышает 20, будет возвращено OK.

Принимая во внимание, что если значение в столбце «Очки» не превышает 25, будет возвращено «Хорошо».

В противном случае он вернет Отлично.

Пример 2: функция ЕСЛИ с логикой И

Если для определенного игрока выполняются 3 условия, ввод формулы в ячейку D2 вернет «Да», и будет «Нет», если хотя бы одно условие не выполнено :

=ЕСЛИ(И(A2=»Mavs», B2=»Guard», C2>25), «Да», «Нет»)

Теперь можно заполнить формулу для каждой оставшейся ячейки в столбце D:

Эта формула способна выполнять следующие действия:

В колонке Команда, если значение было «Mavs», а в колонке «Позиция» было значение «Охрана» и в колонке «Очки» значение было не менее 25 , он вернет Да.