Вычисляем диапазон в Excel

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

Итак, начнём!

Что такое диапазон?

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

К примеру, у вас есть какое-то количество данных, максимальное значение 100, а минимальное 1, разброс в вашем случае — 99.

Вычисляем диапазон

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

Но все-таки вычислить этот разброс не сложно даже без сортировки.

Для этого мы будем использовать функции МАКС и МИН.

Допустим, у нас есть такая табличка:

Наша задача, вычислить разброс по продажам. Давайте сделаем это!

Формула примет такой вид:

=МАКС(B2:B11)-МИН(B2:B11)

Готово! Таким образом, мы вычислили разброс.

Более сложное вычисление

В ситуациях, похожих на рассмотренную выше, все довольно просто. Мы вычисляем разброс простым вычитанием из максимального значения, минимального.

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

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

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

Что же делать?

Сейчас разберемся.

Допустим, у нас есть такая табличка:

В ней все значения довольно большие, кроме двух (2 и 4 строки).

Конечно, это не обязательно ошибка, а может быть из-за того что магазины были закрыты (например, из-за ремонта) или по еще каким-то причинам.

Как же правильно рассчитать разброс в таком случае?

Довольно просто, мы можем просто исключить и не использовать в расчетах их продажи.

Формула принимает такой вид:

=МАКС(B2:B11)-МИНЕСЛИ(B2:B11;B2:B11;">20000")

Итак, мы вызывали функцию МИНЕСЛИ. Что она делает?

Результатом её выполнения будет минимальное значение, которое удовлетворяет указанному критерию (в нашем случае >20000). То есть она найдет самое минимальное значение в табличке, которое больше 20000.

Но, эта функция доступна только в новых версиях Excel. Что делать если вы пользуетесь старой версией?

Ну, во-первых, рекомендую обновиться. Если это все-таки не вариант, то можно использовать следующий вариант:

=МАКС(B2:B11)-МИН(ЕСЛИ(B2:B11>20000;B2:B11))

Если вам нужно исключить какие-либо большие данные, используйте функцию МАКСЕСЛИ по аналогии с МИНЕСЛИ.

Итак, вы узнали как можно находить разброс в различных ситуациях.

Надеюсь, эта статья была полезна для вас!