Evolcom.ru

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

Как подсчитать максимальное количество последовательных положительных / отрицательных чисел в Excel?

Как найти наименьшее положительное и наибольшее отрицательное число в Excel

Положительные и отрицательные числа иногда встречаются вместе в одном диапазоне ячеек. Для того чтобы определить крайнее значение, необходимо выполнить определенные условия. Для положительных чисел мы должны получить наименьшее значение, а для отрицательных — отдельно. Эту проблему нельзя решить простым использованием функций =MIN(), =MAX() и =LOWEST(), =NOWEST(). Другими словами, если вызывается диапазон положительных и отрицательных чисел, то будет возвращено только отрицательное наименьшее значение. Поэтому с функциями следует использовать специальную формулу.

Наименьшее положительное число

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

Доходы и расходы.

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

  1. Введите следующую формулу в ячейку D2:
  2. После ввода формулы необходимо нажать комбинацию клавиш: CTRL+SHIFT+Enter для подтверждения, так как она должна быть выполнена в матрице. Если все сделано правильно, скобки будут видны в строке формул.

Обсудите принцип действия формулы для нахождения наименьшего положительного числа:

Функция =If(), которая работает в массиве формул, проверяет каждую ячейку в диапазоне C2:C13 на предмет наличия в ней числа, меньшего или равного 0 (<=0). В результате в памяти образуется условный массив логических значений:

  • FALSE для положительных чисел (в этом случае также для нуля);
  • TRUE для отрицательных.
Читайте так же:
Как показать или скрыть полевые кнопки в сводной диаграмме в Excel?

Затем в условной таблице функция IF заменяет все значения TRUE на пробелы («»). Значение FALSE заменяется положительными числами, которые находились в интервале C2:C13. Вычисление формулы завершается функцией =MIN(), которая возвращает наименьшее положительное число в условной таблице (уже отфильтрованные отрицательные значения и нули).

Наибольшее отрицательное число в диапазоне ячеек

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

  1. В ячейке E2 введите формулу: =0;””;C2:C13))’ >
  2. Так же, как и предыдущей формуле после ввода необходимо нажать комбинацию клавиш: CTRL+SHIFT+Enter, так как она должна выполняться в массиве. Если все сделано правильно в строке формул появятся фигурные скобки.

В результате мы быстро и легко подвинули самый слабый по обороту магазин — он № 4. А самый дешевый по расходам — магазин № 2.

Подсчет максимального количества подряд идущих значений в EXCEL

Предположим, что есть столбец с нечетными числами среди значений. Необходимо подсчитать количество нечетных значений в строке (выделено светло-коричневым цветом). В данном примере максимальное количество последовательных нечетных значений = 3.

Предположим, что диапазон значений находится в ячейках A8:A15, тогда появится такое вычисление (см. файл примера):

Диапазон формулы должен быть на 1 ячейку шире; это не опечатка. Напротив, если максимальное количество последовательных четных значений находится в самом конце списка, формула вернет неверное значение (на 1 меньше). Формула должна быть введена как формула массива .

Давайте разберем работу формулы более подробно. Вместо формулы массива мы используем эти дополнительные столбцы:

В качестве первого аргумента, матрицы данных, мы используем номера строк, в ячейки которых внесены исходные значения (столбец G). Для второго аргумента, интервальной матрицы, мы используем те же номера строк, но если значение нечетное, то вместо номеров строк выводим 0 (см. столбец H). Поскольку функция QUALITY() перед вычислением сортирует матрицу интервалов в порядке возрастания, для наглядности мы делаем то же самое в столбце I. Для удобства столбец J содержит список диапазонов, в которых PRIVATE() вычисляет количество значений в матрице данных, находящихся в этом диапазоне. Поскольку в столбце I нет номеров строк, содержащих нечетные значения, разность между границами диапазона даст количество нечетных значений в строке. Именно это и делает функция QUALITY(), подсчитывая количество значений (номеров строк), которые попадают в эти диапазоны. Затем вычисляем максимальное значение и вычитаем 1. Этот пример показывает нестандартное применение функции QUALITY(), поэтому весь алгоритм формулы непрозрачен и труден для понимания.

Читайте так же:
Как предотвратить дублирование записей в столбце Excel?

Еще одна формула, еще более сложная для понимания, но которая также дает правильный результат:

Используйте формулу для нахождения максимального количества последовательных значений QUANT:

Используйте формулу, чтобы найти максимальное число последовательных положительных значений:

= МАКС(ЧАСТОТА(СТРОКА( A8:A16 );( A8:A16 <=0)*СТРОКА( A8:A16 )))-1

Чтобы найти максимальное число последовательных значений =1, воспользуйтесь формулой:

= МАКС(ЧАСТОТА(СТРОКА( A8:A16 );( A8:A16 <>1)*СТРОКА( A8:A16 )))-1

Как видно из приведенных примеров, структура формулы остается прежней, меняется только выражение, отвечающее за нахождение значений, не удовлетворяющих заданному условию ( NOTHING(A8:A16)<>A8:A16 или NOTHING(A8:A16)<>A8:A16 или A 8:A16 <=0 или A 8:A16 <>1 ). Поэтому при желании вы можете адаптировать формулу к своим потребностям.

голоса
Рейтинг статьи
Ссылка на основную публикацию
Adblock
detector