Я хочу увеличить ссылку на ячейку формулы.

На чтение 3 мин Опубликовано 29.10.2024

Содержание

Вопрос или проблема
Ответ или решение
Шаги для реализации:
Заключение:

Вопрос или проблема

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

“Лист 1”

B2 - =Sheet2!B2
B3 - =Sheet2!B21

и я хочу перетаскивать формулу в B2 вниз столько раз, и она будет в итоге такой,

B29 - =Sheet2!B517

Я приложил образец таблицы для справки.

https://docs.google.com/spreadsheets/d/1tyJfjO5tErGSPBQaLswagHCy8mhJf7m9QM7ZJyU2I68/edit?usp=sharing

Есть ли какие-нибудь формулы, которые я могу использовать, или комбинации клавиш, чтобы сократить время, не добавляя вручную 19?

Спасибо

Используйте функцию OFFSET.

Для вашего примера вы можете использовать

=OFFSET(Sheet2!$B$2,(ROW()-2)*19,0)

Это приведет к необходимым вам ссылкам на ячейки с caveat, что формулы не будут столь прозрачными, как просто ссылки на ячейки.

Если вы хотите адаптировать это для других целей, просто знайте, что функция ROW() возвращает номер строки ячейки, в которой находится формула. Поскольку ваша первая формула находится в строке 2, я использовал (ROW()-2) в качестве множителя 19, чтобы убедиться, что нет смещения в первой формуле (просто Sheet2!$B$2). Для следующей строки (3) формула вернет Sheet2!B2 с смещением на 1*19 строк. Следующая будет смещена на 2*19 строк и т.д.

Ответ или решение

Для решения задачи инкрементации ячейков с помощью формулы в Google Sheets, воспользуемся функцией OFFSET. Эта функция позволяет динамически ссылаться на определённые ячейки, смещая их на заданное количество строк или столбцов от исходной позиции.

Шаги для реализации:

Изначальное понимание функции OFFSET:
Функция OFFSET используется для получения ссылок на диапазоны, которые находятся на определённом расстоянии от заданной ячейки. Синтаксис функции следующий:
```
OFFSET(reference, rows, cols, [height], [width])
```
где:
- reference — исходная ячейка или диапазон,
- rows — смещение по строкам,
- cols — смещение по столбцам,
- height и width — необязательные параметры, определяющие размер возвращаемого диапазона.
Создание базовой формулы:
В вашем случае, начнем с формулы в ячейке B2. Чтобы получить нужную ссылку на ячейку в Sheet2, сформулируем следующую формулу:
```
=OFFSET(Sheet2!$B$2, (ROW()-2)*19, 0)
```
- Объяснение формулы:
  - Sheet2!$B$2 — это наша исходная ячейка.
  - (ROW()-2)*19 — эта часть формулы вычисляет количество строк, на которое необходимо сместиться от базовой ячейки. ROW() возвращает текущий номер строки, и если мы вычтем 2 (поскольку формула начинается с строки 2), мы получим нужный коэффициент для смещения на 19 строк на каждую последующую строку (то есть для каждой следующей строки инкремент будет на 19).
  - 0 указывает на отсутствие смещения по столбцам.
Применение формулы в других ячейках:
После ввода этой формулы в ячейку B2, вы можете просто перетащить уголок ячейки вниз, чтобы автоматически продублировать формулу в ячейках ниже. Это приведёт к тому, что для каждой новой ячейки будет добавлено смещение на 19 строк, создавая нужные ссылки.
Проверка результатов:
Убедитесь, что в ячейках B2, B3, B4 и так далее ссылки ведут на корректные ячейки в Sheet2, как вы и планировали:
- B2: =Sheet2!B2
- B3: =Sheet2!B21
- …
- B29: =Sheet2!B517

Заключение:

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

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