Увеличьте относительную ссылку на строку более чем на 1 строку при копировании формулы вниз на следующую строку.

Содержание

Вопрос или проблема
Excel 2010
Excel 365
Ответ или решение
Увеличение относительной ссылки на ячейку при копировании формулы в Excel 2010
Введение
Исходная Проблема
Решение через использование функции OFFSET
Пример формулы с использованием OFFSET
Альтернативное решение с использованием INDIRECT
Заключение

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

У меня есть рабочий лист в Excel 2010, где результаты побед и поражений представлены 1/0 в столбце AC, начиная с AC46 и затем на каждой 21-й строке, которая следует.

Я использую следующую формулу для возврата “W” за 1 или “L” за 0, если строка имеет значение.

=IF(AC46="","",
   IF(AC46=1,"W",
     IF(AC46=0,"L","")))

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

Возможно ли перетаскивать формулу вниз по строкам, но чтобы ссылка на ячейку увеличивалась более чем на 1 строку?

Я хочу скопировать её вниз по столбцу, но чтобы ссылки на строки увеличивались на 21 строку вместо 1.

Например, я хочу, чтобы формула следующей строки выглядела так:

=IF(AC67="","", 
   IF(AC67=1,"W",
     IF(AC67=0,"L","")))

А следующая:

=IF(AC88="","", 
   IF(AC88=1,"W",
     IF(AC88=0,"L","")))

И так далее.

Введите то, что ниже, в A2,
=OFFSET($A$10,ROWS($A$2:A2)*$B$2,0,1,1)
и число 1 в B2
Затем в A11 также число 1,
A12: =A11+1,
заполните A12 вниз на любое количество ячеек, которые вы хотите
Заполните A2 вниз до A10.

Измените число в B2, чтобы увидеть, как изменяются значения в A2 до A10.

Успешный тест в LibreOffice

B2 определяет
какое из значений в A11 и ниже
показывать в A2 до A10.

Замените A46 в вашей первой формуле на формулу в шаге 1 (удалите = очевидно)
Либо замените $B$2 на 67 напрямую,
либо поместите 67 в любую ячейку и замените $B$2 на фиксированную ссылку
(абсолютную, с $, как показано для B2) на эту ячейку.

Попробуйте эту формулу:
Введите формулу в A1:

=IF(AC1="","",IF(AC1=1,"W",IF(AC1=0,"L","")))

Затем введите формулу в A2:

=IF(INDIRECT("AC"&ROW(A1)+20)="","",IF(INDIRECT("AC"&ROW(A1)+20)=1,"W",IF(INDIRECT("AC"&ROW(A1)+20)=0,"L","")))

Теперь вы можете перетаскивать формулу в A2.

Excel 2010

=IF(OFFSET(AC$46, 21*(ROW(AC46)-ROW(A$46)),)=1, "W", 
   IF(OFFSET(AC$46, 21*(ROW(AC46)-ROW(AC$46)),)&""="0", "L",
     ""))

Формула использует n*21 в качестве аргумента строки функции OFFSET, где
```
n=ROW(AC$46)-ROW(AC[46+n])
```
Чтобы проверить на 0, значение преобразуется в строку, чтобы не нужно было также проверять на пустое значение, так как формула уже достаточно длинная:
```
true=AND(ISBLANK(x), x=0)
false=AND(ISBLANK(x), ""&x="0")
  true=(1-1)&""="0")  
```

Excel 365

=LET(
   val,OFFSET(AC$46, 21*(ROW(AC46)-ROW(AC$46)),),
   IF(val=1, "W",
     IF(val&""="0", "L",
      "")))

LET можно использовать для уменьшения размера формулы.

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

Увеличение относительной ссылки на ячейку при копировании формулы в Excel 2010

Введение

Работа с формулами в Microsoft Excel может иногда вызывать трудности, особенно когда необходимо использовать относительные ссылки, которые не увеличиваются на 1 строку, а требуют специфического шага. В данном случае вы имеете дело с формулой, которая должна ссылаться на ячейки с результатами "Win/Loss" (1/0) в столбце AC, начиная с AC46 и далее на каждом 21-м ряду.

Исходная Проблема

Ваше исходное выражение:

=IF(AC46="","", IF(AC46=1,"W", IF(AC46=0,"L","")))

При копировании формулы вниз, ссылка на AC46 автоматически изменяется на AC47, AC48 и так далее, что не соответствует вашему требованию извлечь данные из AC67, AC88 и т.д. Вам необходимо сделать так, чтобы ссылки увеличивались на 21 строку.

Решение через использование функции OFFSET

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

Пример формулы с использованием OFFSET

Вы можете использовать следующую формулу:

=IF(OFFSET(AC$46, (ROW(A1)-1)*21, 0)="","", 
   IF(OFFSET(AC$46, (ROW(A1)-1)*21, 0)=1,"W", 
   IF(OFFSET(AC$46, (ROW(A1)-1)*21, 0)=0,"L","")))

Разбор формулы:

OFFSET(AC$46, (ROW(A1)-1)*21, 0):
- AC$46 — это базовая ячейка, с которой мы начинаем отсчет.
- (ROW(A1)-1)*21 — здесь мы используем функцию ROW для определения текущей строки, из которой будем черпать данные. Начинаем с A1, поэтому вычитаем 1, чтобы корректно задать смещение. При копировании вниз ROW(A1) будет заменяться на ROW(A2), ROW(A3) и т.д., что даст нужное смещение на 21 строку.

Альтернативное решение с использованием INDIRECT

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

=IF(INDIRECT("AC" & (46 + (ROW(A1)-1)*21))="","", 
   IF(INDIRECT("AC" & (46 + (ROW(A1)-1)*21))=1,"W", 
   IF(INDIRECT("AC" & (46 + (ROW(A1)-1)*21))=0,"L","")))

Пояснения:

INDIRECT("AC" & (46 + (ROW(A1)-1)*21)) формирует строку, представляющую адрес ячейки, начиная с AC46 и прибавляя смещение в 21 строку при каждых новых копиях.

Заключение

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

Если у вас возникнут дополнительные вопросы или потребуется помощь в других аспектах работы с Excel, не стесняйтесь обращаться!