Как копировать формулы, если между ними есть пустые строки, не теряя при этом последовательность связанных листов?

Question

У меня есть файл Excel, состоящий из двух листов.

• Краткое содержание
• Необработанные данные

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

Теперь проблема в том, что если у меня есть лист с 1000+ строками и я хочу что-то в нем изменить, мне придется вручную редактировать ссылку на каждую ячейку, поскольку она не продолжает ссылку листа RawData, а получает номер ячейки как ссылку листа Summary.

На приложенном примере, если я копирую и вставляю формулу, я получаю номера ячеек 2, 5, 8, 11 и так далее в зависимости от зазора между строками, тогда как мне нужно копировать формулы, как показано в столбце ручной формулы, например, последовательность B2, B3, B4, B5 независимо от текущего номера строки листа. Пробовал много вещей, но не смог найти никакого решения. Есть ли помощь?

Чтобы избежать путаницы, создам более реалистичный пример из моей таблицы со снимком экрана вкладки RawData.

Лист исходных данных:- Лист данных RawData

Вот связанный лист, куда я привязываю данные из листа RawData. Краткий лист продукта

Как вы видите, если вы скопируете и вставите формулу, то увидите Тестовый продукт 1, Тестовый продукт 6 и Тестовый продукт 11, тогда как должны быть Тестовые продукты 1, 2 и 3.

Вот пример ссылки на файл Excelhttps://we.tl/t-3F3mJra7pe

Answer 1

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

• НаРезюме листв ячейке F3Формула =Rawdata!A2возвращает значение Test Product 1.
• Как только вы скопируетеСводные листыданные из B3:F7, B8Excel читает6 рядовмежду F3:F8и возвращается Test Product 7из B8ячейкиНеобработанные данныеЛист.

Что портило все мероприятие.

Решение:

• Тебе нужноВспомогательная колонкавНеобработанные данныеЛист.

• Переписать ячейку B3вКраткое содержаниеЛист с Простой продукт 1.
• Введите эту формулу в F3ячейкуКраткое содержаниеЛист.

=VLOOKUP(B3,RawData!A$2:E$12,2,FALSE)

Вы поняли.

• Сейчас,КопироватьДиапазон B3:F7иВставитьна сотовом B8.

Вы поняли.

• Перепишите ячейку B8с помощьюПростой продукт 2, теперь вы получили правильную последовательность.

• ПовторитеКопировать вставитьс исправлениемПростые продуктыпоследовательности, вы получите желаемый результат.

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

Answer 2

В таких сценариях, я думаю, более простым, но надежным решением было бы использование функции OFFSET(). Offset возвращает ссылку на одну ячейку или диапазон ячеек. Учитывая ваш сценарий, если первая формула Copy&Paste должна ссылаться на RawData Product1, а вторая Copy&Paste будет ссылаться на Product2, независимо от того, сколько строк будет пропущено при этих Copy&Paste, я бы реализовал эту OFFSET следующим образом:

A B               C      D        E       F
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B1);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B6);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B11);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             =OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B16);0)
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large

Обратите внимание, что при копировании формулы "=OFFSET(RawData!$A$1;COUNTA(Summary!$B$1:B1);0)" ссылка на "Summary!$B$1:B1" автоматически изменится с тем же поведением, которое вам НЕ НУЖНО; но как только эта ссылка будет использована внутри формулы COUNTA(), она будет работать, и вам не придется изменять тысячи ссылок самостоятельно. Сделав это, COUNTA() подсчитает, сколько раз "Simple Product" появляется с начала столбца, и будет использовать это внутреннее смещение для ссылки на соответствующий продукт в порядковом положении. Все это без необходимости создания вспомогательных столбцов. Результат будет следующим:

A B               C      D        E       F
  Simple Product  FALSE  TRUE             TestProduct1
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct2
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct3
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large
  Simple Product  FALSE  TRUE             TestProduct4
                  AttrId AttrName AttrVal
                  1      Size     Small
                  1      Size     Medium
                  1      Size     Large