Чтобы выделить строку по результату формулы в другой ячейке в Numbers для Mac, используйте вспомогательные ячейки и правила выделения на основе ссылок на ячейки. В этой инструкции показано, как строка с количеством объектов меняет цвет, когда вычисленный статус становится Ready.

Как правила выделения в Numbers влияют на решение

Apple описывает условное выделение как проверку значения самой выделяемой ячейки, в том числе сравнение с другой ячейкой. В руководстве не предлагается произвольное правило на основе формулы для окрашивания всей строки по несвязанной ячейке со статусом. Использование условий для ячеек в Numbers на Mac.

В приведённом ниже обходном решении задокументированные сравнения сочетаются с формулами. Каждая вспомогательная ячейка возвращает значение соответствующей видимой ячейки, если статус — Ready, и другое значение в противном случае. Затем правило равенства позволяет каждой видимой ячейке следовать за статусом. Это производный метод, а не опубликованная Apple инструкция по выделению строк.

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

Создание таблицы примера и формулы статуса

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

Строка A: Объект B: Количество C: Статус D: Вспом. ячейка объекта E: Вспом. ячейка количества
2 Кубы 8 Формула Формула Формула
3 Диски 12 Формула Формула Формула
4 Конусы 15 Формула Формула Формула

Задача для примера — определить группы объектов, содержащие не менее 12 единиц. Нажмите C2, введите формулу ниже и нажмите Return. Ввод = открывает редактор формул; Apple указывает, что >= — поддерживаемый оператор сравнения. Вычисление значений с использованием данных из ячеек таблицы в Numbers на Mac.

=IF(B2>=12,"Ready","Pending")

Функция IF возвращает одно значение, если условие истинно, и другое, если оно ложно. Здесь количество 12 или больше даёт результат Ready, а меньшее количество — Pending. Оба варианта указаны явно, поэтому результат не зависит от пропущенного аргумента. IF.

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

Прежде чем продолжить, проверьте результаты: в C2 должно отображаться Pending, а в C3 и C4 — Ready. В скопированных формулах ссылки на количество должны быть B3 и B4 соответственно. Apple объясняет, как относительные ссылки изменяются при копировании формул. Ссылки на ячейки в формулах.

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

Создание вспомогательных ячеек, совпадающих только для готовых строк

Введите эту формулу в D2:

=IF($C2="Ready",A2,A2&" pending")

Если статус — Ready, вспомогательная ячейка возвращает название объекта без изменений. В противном случае она добавляет pending, благодаря чему значение отличается от исходного названия. Apple указывает, что оператор & используется для объединения текста или содержимого ячеек, на которые есть ссылки. Использование текстовых операторов и подстановочных знаков.

Введите эту формулу в E2:

=IF($C2="Ready",B2,-1)

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

В обеих формулах $C2 фиксирует столбец статуса, оставляя номер строки относительным. Запись $C$2 зафиксировала бы обе части ссылки, и скопированные вспомогательные формулы продолжали бы считывать статус первой строки данных. Ссылки на ячейки в формулах.

Автозаполните диапазон D2:E2 вниз до строки 4. Вспомогательные ячейки должны содержать следующие значения. Это расчётные ожидаемые результаты для синтетических данных, а не наблюдения за работающим сеансом Numbers.

Строка Статус Вспом. ячейка объекта Вспом. ячейка количества Ожидаемое выделение
2 Pending Кубы pending -1 Нет
3 Ready Диски 12 A3:C3
4 Ready Конусы 15 A4:C4

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

Применение правил выделения к видимым ячейкам

Для каждой целевой ячейки откройте Формат → Ячейка → Условное выделение → Добавить правило. Выберите подходящее условие равенства для текста или чисел. Для сравнения со вспомогательной ячейкой нажмите кнопку ссылки на ячейку и выберите нужную вспомогательную ячейку; задайте для всех трёх видимых ячеек одинаковый цвет заливки, затем нажмите Готово. Использование условий для ячеек в Numbers на Mac.

Целевая ячейка Настраиваемое сравнение
A2 Текст равен значению в указанной ячейке D2
B2 Число равно значению в указанной ячейке E2
C2 Текст равен введённому значению Ready

Для названия объекта используйте условие точного равенства текста. Условие «содержит» нарушит принцип решения, поскольку вспомогательное значение для ожидающей строки всё равно содержит исходное название.

В этом небольшом примере повторите эти три сравнения для строк 3 и 4, заменив каждую ссылку на вспомогательную ячейку ссылкой на ячейку в той же строке. Таким образом, A3 сравнивается с D3, B3 — с E3, а все ячейки статуса сравниваются с Ready. Явная настройка девяти ячеек позволяет не полагаться на непроверенные изменения ссылок при распределении правил.

Не указывайте для всех ячеек с объектами D2, а для всех ячеек с количеством — E2: у каждой строки свои вспомогательные ячейки. Сравнение всех трёх видимых ячеек непосредственно с Ready тоже не сработает: названия объектов и количества содержат другие значения.

Когда сравнения заработают, выберите вспомогательные столбцы D и E, откройте меню со стрелкой вниз у буквы выбранного столбца и выберите Скрыть выбранные столбцы. Apple прямо указывает, что можно скрывать столбцы, данные которых по-прежнему нужны формулам. В результате в таблице останутся три видимых столбца, и заливка будет охватывать всю видимую строку данных. Добавление и удаление строк и столбцов в Numbers на Mac.

Проверка порога и поиск причин неожиданных результатов

Проверьте изменение в обоих направлениях. Измените B2 с 8 на 12: статус должен стать Ready, значения вспомогательных ячеек должны совпасть, а для A2:C2 должна примениться выбранная заливка. Измените значение на 11: статус должен стать Pending, а строка должна вернуться к исходному форматированию. При изменении входных данных, на которые есть ссылки, формулы обновляют результаты. Обзор формул.

Если результат отличается, сначала проверьте вычисления, а затем внешний вид:

  1. Проверьте статус. Убедитесь, что количество введено как число, а формула возвращает нужный текст. Если Numbers отображает значок ошибки формулы, нажмите его, чтобы прочитать сообщение об ошибке. Вычисление значений с использованием данных из ячеек таблицы в Numbers на Mac.
  2. Проверьте равенство вспомогательных значений. Для готовой строки каждое вспомогательное значение должно совпадать с соответствующей видимой ячейкой. Для ожидающей строки оба значения должны отличаться.
  3. Проверьте ссылку в правиле. Убедитесь, что каждая целевая ячейка сравнивается со вспомогательной ячейкой своей строки.
  4. Проверьте конкурирующие правила. Numbers применяет первое подходящее правило выделения; при необходимости переместите нужное правило выше конкурирующего. Использование условий для ячеек в Numbers на Mac.

Адаптация метода с сохранением его исходных условий

Для другого условия по количеству объектов измените формулу в столбце C, сохранив те же результаты Ready и Pending. Тогда вспомогательные ячейки продолжат следовать за этим статусом. Для дополнительного видимого столбца создайте ещё одну вспомогательную ячейку, ветви которой подходят для типа данных этого столбца.

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

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

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

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

Источники