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

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

Создайте небольшой пример с пропусками

В актуальном онлайн-руководстве Apple для Mac указано, что оно посвящено Numbers 15.4. Чтобы проверить установленную версию, выберите Numbers → Об этой программе. Приведённые ниже формулы соответствуют актуальной справке Apple по функциям. Руководство пользователя Numbers для Mac

Используйте одну таблицу с пятью столбцами и как минимум восемью строками. Введите заголовки ниже в строку 1. Укажите примеры значений в столбцах A и B, оставив указанные ячейки B действительно пустыми. Слова «оставить пустой» поясняют настройку; не вводите их в таблицу.

Строка A: Этап B: Количество объектов C: Пусто? D: Метка результата E: Результат
1 Этап Количество объектов Пусто? Метка результата Результат
2 Планировка 12 Последнее значение
3 Сборка оставить пустой
4 Проверка 18
5 Корректировка 0
6 Выравнивание оставить пустой
7 Завершение 7
8 Расширение оставить пустой

Это пример последовательности количества объектов. Ожидаемый ответ — 7 из B7. Пустую ячейку B8 нужно пропустить, как и пропуски внутри столбца. Значение 0 в B5 — это запись, и она должна оставаться допустимой.

Поместите результат в E2, вне исходного и вспомогательного столбцов. Так ответ будет отделён от ячеек, используемых для вычисления.

Добавьте проверку на пустоту для каждой строки источника

Щёлкните C2 и введите формулу целиком:

=ISBLANK(B2)

При вводе = откроется Редактор формул; нажмите Return, чтобы применить формулу. Apple описывает эту процедуру ввода в руководстве по вычислению значений в ячейках таблицы. Вычисление значений с использованием данных в ячейках таблицы в Numbers на Mac

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

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

После заполнения проверьте C3 и C8: в них должны быть ссылки соответственно на B3 и B8. Для приведённого примера в C2:C8 должны быть следующие результаты:

Ячейка помощника Ожидаемый результат Причина
C2 FALSE В B2 содержится 12
C3 TRUE B3 пуста
C4 FALSE В B4 содержится 18
C5 FALSE В B5 содержится 0
C6 TRUE B6 пуста
C7 FALSE В B7 содержится 7
C8 TRUE B8 пуста

Верните самое нижнее заполненное значение

Щёлкните E2 и введите:

=XLOOKUP(FALSE,C2:C8,B2:B8,\"Нет записей\",0,-1)

Эта формула ищет FALSE в C2:C8 и возвращает соответствующее значение из B2:B8. Пятый аргумент, 0, задаёт точное совпадение. Последний аргумент, -1, задаёт поиск с конца к началу. \"Нет записей\" задаёт сообщение, которое выводится, если совпадений нет. Значения аргументов приведены в документации Apple по XLOOKUP.

В этом примере самое нижнее FALSE находится в C7, поэтому E2 возвращает значение из B7: 7. Обратите внимание: оно меньше 18. Задача касается расположения, поэтому наибольшее число отвечало бы на другой вопрос.

Выполните эти три временных изменения, чтобы проверить настройки, затем при желании восстановите пример:

  1. Измените B7 на 9: в E2 должно появиться 9.
  2. Очистите B7: в E2 должно появиться 0, поскольку теперь B5 — последняя заполненная ячейка в исходном столбце.
  3. Очистите все ячейки в B2:B8: в E2 должно отображаться Нет записей.

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

Определите, означает ли «пустая» пустую ячейку или ячейку, которая выглядит пустой

В основном методе используется строгое определение: пропускается только полностью пустая исходная ячейка. Ячейка, содержащая формулу, которая возвращает \"\", всё равно содержит формулу, поэтому она не соответствует этому определению. Следовательно, визуально пустой результат может быть выбранной записью. Apple определяет ISBLANK как проверку полностью пустой ячейки. ISBLANK

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

=LEN(B2)=0

Формулу поиска в E2 оставьте без изменений. Эта вспомогательная формула проверяет, имеет ли исходное значение нулевую длину текста. В документации Apple указано, что LEN подсчитывает графемы и принимает значения любого типа; подсчёт включает пробелы, числа и специальные символы. Пустая строка имеет длину ноль, а пробел — длину один. LEN

Определитесь с правилом, прежде чем использовать результат дальше. Исходная формула помощника отвечает на вопрос «Какая ячейка последняя заполненная?». Формула с проверкой длины текста отвечает на вопрос «Какое значение последним имеет ненулевую длину текста?». Ни одно из этих правил не считает пробел пустым.

Сохраняйте соответствие диапазонов при увеличении таблицы

В примере поиск намеренно ограничен строками 2–8. Если вы расширите исходные данные до строки 20, заполните вспомогательный столбец до C20 и одновременно обновите оба диапазона поиска:

=XLOOKUP(FALSE,C2:C20,B2:B20,\"Нет записей\",0,-1)

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

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

Не используйте упрощённые решения с массивами, диаграммами и подсветкой, меняющие задачу

Проверка пустоты целого столбца не заменяет вспомогательный столбец

Не заменяйте заполненный вспомогательный столбец на ISBLANK(B2:B8), предполагая, что формула выполнит семь отдельных проверок. Apple прямо указывает: если ISBLANK ссылается более чем на одну ячейку, функция возвращает FALSE, даже если все ячейки пусты. Именно поэтому этот метод проверяет каждую исходную ячейку отдельно. ISBLANK

В Numbers есть функции для работы с массивами; Apple определяет массив как последовательность значений и объясняет, что функции для работы с массивами могут передавать значения другим функциям. Однако эта общая возможность не меняет поведение аргументов, задокументированное для отдельной функции. Типы аргументов и значений

Диаграмма отображает выбранные данные

Чтобы увидеть ответ напрямую, используйте ячейку результата. Если вы также строите диаграмму количества объектов, выберите её и нажмите Редактировать ссылки на данные, чтобы проверить или изменить исходные ячейки. По умолчанию скрытые или отфильтрованные данные исключаются из диаграмм, а на больших диаграммах может отображаться представительная выборка точек. Поэтому видимую конечную точку нужно сверить с исходной таблицей. Изменение данных диаграммы в Numbers на Mac

Подсветка совпавшего значения может выделить несколько ячеек

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

Правило, сравнивающее количества с результатом, выделит совпадающие значения. Если 7 встречается в нескольких строках источника, такие совпадения не укажут однозначно на B7. Учитывайте это, если задача состоит в поиске определённой строки.

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

Источники