Чтобы формула в Numbers продолжала считывать фиксированный адрес верхней строки после вставки строк, используйте INDIRECT с адресом в кавычках, например =INDIRECT("B2") для первой строки данных под одной строкой заголовка. Сначала решите, нужна ли вам эта позиция или исходная ячейка.

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

Решите, что означает «сохранить ссылку»

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

Желаемый результат Способ
Считывать текущее содержимое определенного адреса, например B2 Использовать адрес в кавычках с INDIRECT
Сохранять ссылку неизменной при копировании формулы в другое место Использовать абсолютную ссылку, например $B$2
Получать значение в определенной позиции коллекции Использовать INDEX с явными индексами строки и столбца
Сохранять видимость заголовков столбцов при прокрутке Закрепить строки заголовков

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

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

Настройте фиксированную ссылку на первую строку данных

Создайте небольшую таблицу с одной строкой заголовка и без столбцов заголовка. На боковой панели Формат откройте вкладку Таблица и проверьте настройки в разделе Заголовки и нижний колонтитул. При одной строке заголовка в строке 1 находятся подписи, а строка 2 — первая строка данных. Apple отмечает, что изменение числа строк заголовка преобразует существующие строки, а не вставляет новые. Добавление и удаление строк и столбцов в Numbers на Mac.

Введите эти примерные значения. Оставьте все остальные ячейки пустыми, в том числе D2, в которой будет результат.

Строка Столбец A Столбец B Столбец C Столбец D
1 Предмет Количество Текущее верхнее количество
2 Кубики 12
3 Конусы 8
4 Сферы 5

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

=INDIRECT("B2")

Ожидаемый результат — 12. Apple определяет INDIRECT как функцию, возвращающую содержимое адреса, переданного в виде строки. Если второй аргумент опущен, по умолчанию используется формат адреса с буквами столбцов и номерами строк. Здесь "B2" — буквальный адрес, который нужно разрешить. Практический вывод этого метода заключается в том, чтобы использовать этот неизменный текст и продолжать обращаться к строке 2. INDIRECT.

Если в таблице нет строк заголовка, верхней позицией данных будет B1. Выберите адрес по видимым номерам строк. Закрепление заголовка влияет на его видимость при прокрутке, но не определяет ячейку, к которой обращается формула. Добавление и удаление строк и столбцов в Numbers на Mac.

Проверьте результат с помощью пробной вставки

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

  1. Наведите указатель на номер строки 2 и нажмите стрелку.
  2. Выберите вариант добавления строки выше.
  3. Введите Призмы в новую ячейку A2, а 20 — в новую ячейку B2.
  4. Найдите формулу результата: она переместилась вниз вместе со своей исходной строкой. Проверьте ее значение.
  5. Дважды щелкните результат, чтобы проверить текст формулы.

Apple описывает вставку строк через контекстное меню номера строки. Добавление и удаление строк и столбцов в Numbers на Mac.

В этом примере ожидаемый результат теперь — 20: новое количество в B2. Исходное 12 переместилось в B3. Прежде чем применять изменение к рабочей таблице, проверьте и отображаемое значение, и адрес в кавычках.

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

Когда использовать Preserve Row или INDEX?

Используйте Preserve Row, если проблема возникает при копировании формулы вниз по таблице. Дважды щелкните ячейку с формулой, нажмите треугольник на маркере ссылки и выберите Preserve Row. Если столбец тоже должен оставаться абсолютным, выберите также Preserve Column. Apple также описывает, как выбрать ссылку и нажать Command-K, чтобы закрепить обе части. Вычисление значений по данным в ячейках таблицы в Numbers на Mac.

INDEX отвечает на другой вопрос о позиции: какое значение находится в заданных строке и столбце указанной коллекции? Например, с исходными данными из образца эта формула вернет 12, если ввести ее в пустую ячейку за пределами указанного диапазона:

=INDEX(B2:B4, 1, 1)

Индексы задают первую строку и первый столбец диапазона B2:B4. Сами по себе они не означают строку 2 таблицы. Если структурные изменения меняют указанный диапазон, проверьте его еще раз. Apple определяет индексы относительно диапазона и требует, чтобы они находились в его пределах. Нулевой индекс может вернуть всю строку или столбец в виде массива, поэтому для получения одного значения используйте явные положительные индексы. INDEX.

Разграничивайте формулы, массивы, диаграммы и выделение

Для этой задачи достаточно формулы с одной ячейкой. Если заменить адрес диапазоном, изменится форма результата: Apple указывает, что INDIRECT может возвращать массив по строке диапазона, который затем может использовать другая функция. В актуальной справке также описаны разливающиеся массивы. Не заменяйте "B2" адресом нескольких ячеек, рассчитывая, что формула по-прежнему вернет одно количество. INDIRECT.

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

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

Устраните непредвиденный результат

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

Если формула считывает данные не из той таблицы, проверьте полную ссылку. Numbers поддерживает имена таблиц и листов, разделенные двойным двоеточием, например Objects::B2 или Sheet 2::Objects::B2. При создании ссылки между таблицами используйте реальные имена из своей таблицы и перепроверяйте буквальный адрес после переименования или изменения структуры места назначения. Ссылки на ячейки в формулах.

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

Источники