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

Настройте отдельную строку для каждого элемента запасов

В этом примере подсчитывается, для скольких типов элементов нужно пополнить запас. Каждая строка соответствует одному типу, например кубикам или дискам. Результат показывает количество строк, удовлетворяющих условию; он не рассчитывает общее число отдельных предметов, необходимых для достижения всех минимальных значений.

Используйте этот синтетический пример в таблице с одной строкой заголовков. Введите заголовки в A1:D1, а шесть строк с элементами — в строки 2–7. Сначала оставьте столбец D пустым: его значения будут подставлены формулой.

Строка A: Элемент B: Количество C: Минимум D: Статус
2 Кубики 4 6
3 Диски 8 8
4 Кольца 12 5
5 Плитки 0 3
6 Призмы 2 4
7 Сферы 7 6

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

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

Сравните каждое количество с соответствующим минимумом

Выберите D2, введите следующую формулу и нажмите Return. При вводе = откроется Formula Editor; ссылки также можно создать, щёлкнув нужные ячейки. Инструкции Apple по вводу формул

=IF(B2<C2,\"Below\",\"Meets\")

Сравнение проверяет, меньше ли количество в B2 минимума в C2. Функция IF возвращает Below, если сравнение истинно, и Meets в противном случае. Apple описывает такое поведение с двумя результатами и допускает использование текста в качестве любого из них. IF — справка по функциям Apple

Протяните формулу вниз до D7. Выберите D2, наведите указатель на границу выделения, пока не появится жёлтый маркер автозаполнения, и перетащите его вниз до D7. В качестве места вставки используйте пустые ячейки статуса, поскольку автозаполнение перезаписывает существующее содержимое. Автозаполнение ячеек в Numbers на Mac

Проверьте формулу в D3: она должна сравнивать B3 с C3. Аналогично, последняя ячейка статуса должна сравнивать B7 с C7. Эта быстрая проверка подтверждает, что для каждого элемента используется свой минимум.

Ожидаемые результаты:

Элемент Статус Причина
Кубики Below 4 меньше 6
Диски Meets 8 равно 8
Кольца Meets 12 больше 5
Плитки Below 0 меньше 3
Призмы Below 2 меньше 4
Сферы Meets 7 больше 6

Подсчитайте элементы со статусом «ниже минимального»

Разместите сводные данные за пределами строк с элементами. В этом примере введите Items below minimum в F1, а следующую формулу — в F2:

=COUNTIF(D2:D7,\"Below\")

Результат должен быть 3: кубики, плитки и призмы. Согласно определению Apple, COUNTIF подсчитывает ячейки в наборе, удовлетворяющие условию; здесь набор состоит из шести ячеек со статусами, а условие — это текст Below. COUNTIF — справка по функциям Apple

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

Почему общий порог отвечает на другой вопрос

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

=COUNTIF(B2:B7,\"<6\")

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

Выбирайте метод с учётом того, где задан порог: для единого порога используйте прямую формулу COUNTIF, а для разных порогов в строках — вспомогательный столбец. В задокументированном Apple синтаксисе COUNTIF условие применяется к проверяемым значениям. Вспомогательный метод явно выполняет сначала попарные сравнения. Синтаксис COUNTIF и условия

Не скрывайте незаполненные значения количества

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

=IF(AND(ISNUMBER(B2),ISNUMBER(C2)),IF(B2<C2,\"Below\",\"Meets\"),\"Check\")

Сначала формула проверяет, являются ли оба значения числами. ISNUMBER возвращает истину для числового значения, а AND возвращает истину, только если истинны все проверки. Строки, не прошедшие эту проверку, получают статус Check; для прошедших строк выполняется то же сравнение, что и раньше. ISNUMBER — справка по функциям Apple, AND — справка по функциям Apple

Оставьте исходную формулу сводного результата в F2. Введите Rows to check в F4, а эту формулу — в F5:

=COUNTIF(D2:D7,\"Check\")

В заполненном примере в двух сводках должно отображаться три элемента ниже минимума и ноль строк, требующих проверки. Для диагностики удалите количество кубиков. Их статус должен измениться на Check, оставив два подтверждённых элемента ниже минимума и одну неразрешённую строку. Затем восстановите количество — четыре.

Считайте подсчёт элементов ниже минимума завершённым только после проверки всех строк со статусом Check. Так незавершённый элемент не будет включён в группу элементов, которые соответствуют минимуму.

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

Проверьте результат и аккуратно расширьте таблицу

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

  1. Равенство: для дисков — восемь при минимуме восемь — должен отображаться статус Meets.
  2. Нулевое количество: для плиток — ноль при минимуме три — должен отображаться статус Below.
  3. Разные пороги: временно увеличьте минимум для колец до тринадцати; результат должен увеличиться с трёх до четырёх.
  4. Незаполненное значение: при использовании формулы с числовой проверкой удаление значения должно привести к появлению статуса Check.
  5. Последняя строка: убедитесь, что в последней строке инвентаризации есть формула статуса и она входит в диапазон сводки.

В примерах используются явные диапазоны, чтобы было понятно, какие данные они охватывают. Если вы добавите седьмой элемент в строку 8, протяните формулу вспомогательного столбца до D8 и расширьте оба диапазона сводок до D2:D8. Для новой строки нужны как результат сравнения, так и включение в подсчёт. Apple также указывает на особенность категорий в таблицах: если добавить строку внутри указанного диапазона, может потребоваться изменить ссылку, чтобы результат её учитывал. Ссылки на ячейки в формулах Numbers

Если Numbers сообщает об ошибке формулы, щёлкните значок ошибки, чтобы прочитать сообщение. Проверьте ссылки на ячейки, круглые скобки и кавычки вокруг текстовых результатов. Если в настройках используется запятая как десятичный разделитель, Apple рекомендует разделять аргументы функций точками с запятой. Например, сводная формула будет такой: =COUNTIF(D2:D7;"Below"). Обзор функций — справка по функциям Apple

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

При желании выделите ячейки статуса цветом

Выделение поможет быстрее находить строки, попавшие в подсчёт. Выберите D2:D7, откройте боковую панель Format, выберите Cell, затем нажмите Conditional Highlighting и Add a Rule. Выберите текстовое правило, соответствующее Below, задайте стиль и нажмите Done. Можно добавить ещё одно правило для Check. Apple описывает условное выделение как изменение внешнего вида ячейки в зависимости от её значения. Использование условий для ячеек в Numbers на Mac

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

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

Источники