Evolcom.ru

Бытовая техника
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Как подсчитать, если ячейки не пустые и не равны конкретному слову в Excel?

Как подсчитать, если ячейки не пустые и не равны конкретному слову в Excel?

В этой статье описывается синтаксис формулы и применение C AVERAGEIF в Microsoft Excel.

Описание

Возвращает среднее значение (среднее арифметическое) всех ячеек в диапазоне, удовлетворяющих данному условию.

Синтаксис

DETERMINE(диапазон, условия, [среднее значение диапазона])

Аргументы функции SRZNACELY приведены ниже

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

Необходимые условия. Требуется. Условие в виде числа, выражения, ссылки на ячейку или текста, которое определяет ячейки, используемые при расчете среднего значения. Например, условие может быть выражено следующим образом: 32, «32», «>32», «яблоки» или B4.

Диапазон_усреднения. Необязательно. Для вычисления среднего значения требуется набор ячеек. Если этот параметр не указан, используется диапазон.

Замечания

Значения T RUE и FALSE в диапазоне игнорируются.

S RZNACCHESLY игнорирует пустые ячейки в «диапазон_усреднения».

# DIV0, если диапазон пуст или текстовое значение! значение ошибки # DIV0!

Если ячейка в условии пуста, «SRZNACHELY» рассматривает ее как ячейку со значением 0.

Если ни одна из ячеек диапазона не удовлетворяет условиям, значение ошибки #DIV/0! будет #DEL/0!

В этом аргументе можно использовать символы подстановки: вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому одиночному символу, звездочка — любой последовательности символов. Если необходимо найти вопросительный знак или звездочку, то перед ними должна стоять тильда (

Значение «range_averaging» не обязательно должно быть того же размера и формы, что и диапазон. При определении фактических ячеек, для которых рассчитывается среднее значение, левая верхняя ячейка в «range_averaging» используется в качестве начальной ячейки, а затем добавляются ячейки того же размера и формы. Например

Читайте так же:
Как посчитать сетевые дни между двумя датами, включая субботу в Excel?

Если на пляже

I «диапазон_усреднения»

Клетки для лечения

S RSNACHELY используется для вычисления среднего значения, или центра, статистического распределения. Вычисление среднего значения может быть выполнено тремя различными способами:

Среднее значение — это среднее арифметическое, которое рассчитывается путем сложения набора чисел и деления полученной суммы на их количество. Например, среднее значение чисел 2, 3, 5, 7 и 10 будет равно 5, что является результатом деления их суммы, равной 30, на их количество, равное 6.

Медиана — это число, которое лежит в середине набора чисел, т.е. половина чисел имеет значения выше медианы, а половина чисел — ниже медианы. Например, медиана чисел 2, 3, 3, 5, 7 и 10 будет равна 4.

Режим — это число, которое чаще всего встречается в наборе чисел. Например, для чисел 2, 3, 5, 7 и 10 мода равна трем.

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

Примеры

Скопируйте примерные данные из приведенной ниже таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите F2, а затем Enter. При необходимости измените ширину столбцов, чтобы увидеть все данные.

Функции Excel ЕСЛИ (IF) и ЕСЛИМН (IFS) для нескольких условий

Логическая функция ЕСЛИ в Excel является одной из самых популярных функций. Он возвращает результат (значение или другую формулу) в зависимости от условия.

Функция ЕСЛИ в Excel

Данная функция имеет следующий синтаксис.

I F(log_выражение; истинное_значение; [ложное_значение]).

Выражение_записи — это условие, которое необходимо проверить. Например, A2<100. Если значение в ячейке A2 действительно меньше 100, в памяти Exel генерируется ответ TRUE, и функция возвращает то, что находится в следующем поле. В противном случае в памяти генерируется FALSE и возвращается значение последнего поля.

Читайте так же:
Как перевести килограммы в камни и фунты в Excel?

Value_if_truth — значение или формула, которая возвращается, если произошло событие, указанное в первом параметре.

Value_if_false — это альтернативное значение или формула, которая возвращается, если условие не выполняется. Заполнение этого поля не требуется. В этом случае функция вернет FALSE при наступлении альтернативного события.

Очень простой пример. Проверьте, превышают ли продажи каждого товара 30 единиц или нет. Если они выше, формула должна вернуть «Ok», в противном случае она должна вернуть «Delete». Расчет и результат показаны ниже.

Функция Excel ЕСЛИ с одним условием

Продажи первого элемента равны 75, т.е. условие, что они больше 30, выполняется. Следовательно, функция возвращает то, что указано в следующем поле — «Ok». Продажи второго товара меньше 30, поэтому условие (>30) не выполняется и возвращается альтернативное значение, указанное в третьем поле. Для этого служит функция ЕСЛИ. Результат для каждого продукта получается путем вытягивания вычисления вниз.

Это был просто демонстрационный пример. Более сложные проверки обычно выполняются с помощью формулы Excel ЕСЛИ. На данный момент предположим, что есть средненедельные продажи товаров и их остатки. Покупатель должен предоставить свой прогноз остатка через две недели. Вычтите из среднего недельного объема продаж двукратный объем текущих запасов.

Прогноз остатков

Пока все логично, но смущают минусы. Разве бывают отрицательные остатки? Нет, конечно. Запасы не могут быть ниже нуля. Чтобы прогноз был корректным, нужно отрицательные значения заменить нулями. Здесь отлично поможет формула ЕСЛИ. Она будет проверять полученное по прогнозу значение и если оно окажется меньше нуля, то принудительно выдаст ответ 0, в противном случае — результат расчета, т.е. некоторое положительное число. В общем, та же логика, только вместо значений используем формулу в качестве условия.

Функция ЕСЛИ для задания условия в формуле

В прогнозах акций больше нет отрицательных значений, что в целом очень хорошо.

Читайте так же:
Как повторно печатать заголовки на нескольких листах?

Формулы массива также обычно основаны на формулах IF в Excel. Здесь мы не будем углубляться. Для тех, кому интересно, рекомендую прочитать статью о том, как вычислить максимальное и минимальное значение по условию. Расчет в той статье уже не актуален, так как в Excel 2016 добавлены функции MINESLY и MAXESLY. Но посмотреть пример очень полезно — он пригодится в другой ситуации.

Формула ЕСЛИ в Excel – примеры нескольких условий

Часто количество возможных условий не 2 (проверенное и альтернативное), а 3, 4 и более. В этом случае можно также использовать функцию IF, но тогда вам придется вложить друг в друга, указывая все условия по очереди. Рассмотрим следующий пример.

Нескольким менеджерам по продажам нужно начислить премию в зависимости от выполнения плана продаж. Система мотивации следующая. Если план выполнен менее, чем на 90%, то премия не полагается, если от 90% до 95% — премия 10%, от 95% до 100% — премия 20% и если план перевыполнен, то 30%. Как видно здесь 4 варианта. Чтобы их указать в одной формуле потребуется следующая логическая структура. Если выполняется первое условие, то наступает первый вариант, в противном случае, если выполняется второе условие, то наступает второй вариант, в противном случае если… и т.д. Количество условий может быть довольно большим. В конце формулы указывается последний альтернативный вариант, для которого не выполняется ни одно из перечисленных ранее условий (как третье поле в обычной формуле ЕСЛИ). В итоге формула имеет следующий вид.

Несколько условий в функции ЕСЛИ

Комбинация функций IF работает таким образом, что если одно из заданных условий выполнено, то последующие уже не проверяются. Поэтому важно указать их в правильном порядке. Если бы мы начали с B2<1, Excel просто "не заметил" бы условия B2<0,9 и B2<0,95, потому что они включены в интервал B2<1, который проверяется первым (если значение меньше 0,9, то, конечно, оно также меньше 1). И тогда у нас будет только два возможных варианта: меньше 1 и альтернатива, т.е. 1 или больше.

Читайте так же:
Как показать стрелку выпадающего списка в Excel?

При написании формулы легко запутаться, поэтому рекомендуется смотреть на подсказку.

Подсказка функции

Не забудьте закрыть все круглые скобки в конце, иначе Excel выдаст ошибку

Ошибка из-за нехватки скобки

Функция Excel ЕСЛИМН

Функция ЕСЛИ в Excel в целом способна справиться с большинством задач. Но вариант, когда нужно написать длинную цепочку условий, не очень приятен, потому что, во-первых, написать с первого раза не всегда получается (если условие неверное, то скобка не закрывается); во-вторых, разобраться при необходимости в такой формуле бывает сложно, особенно когда условий много, а сами вычисления сложные.

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

Она работает следующим образом. Возьмите приведенный выше пример и используйте новую формулу Excel ELIMIN.

Функция Excel ЕСЛИМН

Как видите, обозначение формулы выглядит намного проще и понятнее.

Следует обратить внимание на следующие моменты. Условия всегда перечисляются в правильном порядке, чтобы не было ненужного дублирования диапазонов. Последнее альтернативное условие, в отличие от обычного условия IF, также должно быть указано. В IF указывается только альтернативное значение, которое возникает, если ни одно из условий не выполняется. Здесь необходимо указать само условие, которое в нашем случае будет B2>=1. Однако этого можно избежать, записав TRUE в поле условия, тем самым указав, что если перечисленные выше условия не выполняются, то это TRUE и возвращается последнее альтернативное значение.

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

голоса
Рейтинг статьи
Читайте так же:
Как очистить фильтры при открытии, сохранении или закрытии книги в Excel?
Ссылка на основную публикацию
Adblock
detector