Обычно, когда я использую слово «диапазон» в своих руководствах по Excel, это ссылка на ячейку или набор ячеек на листе.
Но этот урок не об этом диапазоне.
«Диапазон» также является математическим термином, который относится к разбросу в наборе данных (то есть к диапазону между минимальным и максимальным значением в данном наборе данных).
В этом уроке я покажу вам действительно простые способы рассчитать диапазон в Excel.
Что такое диапазон?
В данном наборе данных диапазон этого набора данных будет разбросом значений в нём.
Простой пример
Если у вас есть набор данных об успеваемости учащихся, где минимальный балл составляет 15, а максимальный балл — 98, то разброс этого набора данных (диапазон) будет равен 73.
Диапазон = 98 - 15
«Диапазон» — это не что иное, как разница между максимальным и минимальным значением этого набора данных.
Как рассчитать диапазон в Excel
Если у вас есть список отсортированных значений, вам просто нужно вычесть первое значение из последнего значения (при условии, что сортировка выполняется по возрастанию).
Но в большинстве случаев у вас будет случайный, ещё не отсортированный набор данных.
Найти диапазон в таком наборе данных также довольно просто. В Excel есть функции для определения максимального и минимального значения из диапазона — функции МАКС и МИН.
Предположим, у вас есть набор данных, и вы хотите вычислить диапазон для данных в столбце B.
Ниже приведена формула для расчёта диапазона для этого набора данных:
=МАКС(B2:B11)-МИН(B2:B11)
Приведённая выше формула находит максимальное и минимальное значение и даёт нам разницу.
Довольно просто, не правда ли?
Вычислить условный диапазон в Excel
В большинстве практических случаев найти диапазон не так просто, как просто вычесть минимальное значение из максимального.
В реальных сценариях вам также может потребоваться учесть некоторые условия или выбросы.
К счастью, в Excel есть множество условных формул, которые могут помочь разобраться с такими аномалиями.
Предположим, у вас есть набор данных о продажах в столбце B, и вы замечаете, что есть два магазина, где значения довольно низкие (возможно, это новые магазины, или на продажи повлияли какие-то внешние факторы).
При вычислении диапазона для этого набора данных может иметь смысл исключить эти новые магазины и рассматривать только те, где есть существенные продажи.
Скажем, вы хотите игнорировать все те магазины, где стоимость продажи меньше 20 000.
Ниже приведена формула, по которой можно найти диапазон с учётом условия:
=МАКС(B2:B11)-МИНЕСЛИ(B2:B11;B2:B11;">20000")
Как это работает
Вместо функции МИН здесь используется функция МИНЕСЛИ (доступна в Excel 2021-2022 и Microsoft 365). Она находит минимальное значение, если соблюдены указанные критерии — в данном случае учитывается только значение больше 20 000.
Таким образом, функция МИНЕСЛИ просматривает весь набор данных, но при вычислении минимального значения учитывает только те значения, которые больше 20 000. Это гарантирует, что значения ниже 20 000 игнорируются, а минимальное значение всегда больше 20 000 (следовательно, выбросы игнорируются).
=МАКС(B2:B11)-МИН(ЕСЛИ(B2:B11>20000;B2:B11)) (вводится как формула массива).
Так же, как использовалась условная функция МИНЕСЛИ, вы также можете использовать функцию МАКСЕСЛИ, если хотите избежать точек данных, которые являются выбросами в другом направлении (то есть пары больших значений, искажающих данные).
Итак, вот как вы можете быстро найти диапазон в Excel, используя пару простых формул.
Надеюсь, вы нашли этот урок полезным.
Хотите освоить Excel глубже?
Больше полезных формул, приёмов и готовых решений — на Vip Excel