Манипуляция автозаполнения формулы

Я пытаюсь создать большую таблицу (10 000 строк формул), которая берет информацию из двух других листов. Основной макет, который я хочу:

Row1 =Sheet1!A7 =Sheet2!M7 =Sheet1!A8 =Sheet2!M8 =Sheet1!A9 =Sheet2!M9 ...etc. 

Когда я пытаюсь использовать формульную автозаполнение, excel выбирает неправильный шаблон, и я получаю что-то вроде этого:

 =Sheet1!A7 =Sheet2!M7 ... =Sheet1!A11 =Sheet2!M11 =Sheet1!A17 =Sheet2!M17 

Я дал excel 10 ячеек, чтобы основать шаблон, и не смог заставить его работать. Может ли кто-нибудь помочь мне разобраться, как это сделать (надеюсь, без VBA)?

Старайтесь избегать функции volatile¹ OFFSET или пары функций INDIRECT / ADDRESS в пользу функции INDEX и небольшой математики.

В первых двух клетках используют эти две формулы.

 =INDEX(Sheet1!A:A, 7+ROW(1:1)/2) =INDEX(Sheet2!M:M, 7+ROW(1:1)/2) 

Выберите две ячейки и перетащите ручку заливки.


¹ Летучие функции пересчитываются всякий раз, когда что-либо во всей книге изменяется, а не только когда что-то, что влияет на их результат, изменяется. Примерами летучих функций являются КОСВЕННЫЕ , СМЕЩЕНИЕ , СЕГОДНЯ , ТЕПЕРЬ , RAND и RANDBETWEEN . Некоторые подфункции функций рабочего листа CELL и INFO также сделают их неустойчивыми.

Вот простой макрос VBA, который устанавливает ваши ссылки. Используйте его, пока не будет представлено решение без VBA :

 Sub propagator() Dim i As Long, k As Long k = 1 For i = 7 To 99 Cells(k, 1).Formula = "=Sheet1!A" & i k = k + 1 Cells(k, 1).Formula = "=Sheet2!M" & i k = k + 1 Next i End Sub 

Просто выберите рабочий лист и запустите макрос.

Отрегулируйте 99 для удовлетворения ваших потребностей.

Вы можете решить эту проблему без VBA с помощью некорректного трюка = INDIRECT – в «Sheet3» находится следующее:

Скриншот Excel

Вы можете напечатать Sheet1!A и Sheet2!M в ячейках A1 и A2 соответственно и заполнить. Затем введите 7 в B1 и формулу в B2 – снова заполните. Эта первая формула эффективно увеличивает счет на два. Наконец, вы можете ввести формулу в C1 и заполнить ее.

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

=INDIRECT("Sheet1!A" & 7+(ROUNDDOWN(ROW()/2,0)-1))

=INDIRECT("Sheet2!M" & 7+(ROUNDDOWN(ROW()/2,0)-1))

ROW() возвращает текущую строку, которая затем делится на 2 (поскольку вы только увеличиваете ссылку на одну строку для каждых двух ячеек), а затем вычитаете 1 (так как мы хотим, чтобы первая настройка была 0).

Попробуйте в строке 2:

 =INDIRECT("Sheet1!A" & 7 +QUOTIENT(ROW()-2,2)) 

И в строке 3:

 =INDIRECT("Sheet2!M" & 7 +QUOTIENT(ROW()-2,2)) 

Выделите оба и скопируйте их.

  • Найти формулу Excel для шаблона номера
  • Формула столбца Excel с условным значением
  • Использование значения в ячейке в качестве ссылки на ячейку в формуле?
  • Удалите все до определенного символа, если найдено в Excel
  • Как рассчитать разницу между двумя номерами в Excel (сложнее, чем вы могли ожидать ;-)
  • Как использовать COUNTIFS или ANOTHER METHOD для подсчета дубликатов заданного диапазона на другом листе
  • Формула Excel для проверки повторяющихся слов
  • Поиск последнего появления дублированного элемента в массиве
  • Excel, возвращает текст из столбца A, где столбец E имеет самое высокое значение, а значения столбца B равны значению в cel G1
  • Несогласованный модуль (= MOD ()) Поведение в Excel
  • SUMPRODUCT игнорировать строку, если она пуста?
  • Давайте будем гением компьютера.