當中間有空行時如何複製公式而不丟失連結工作表的順序？

Question

我有一個 Excel 文件，其中有兩張紙。

• 概括
• 原始數據

我的摘要表有這樣的數據，其中每個下一個數據行都有幾行的間隙

現在的問題是，如果我有一個包含1000 多行的工作表，並且我想更改其中的任何內容，我必須手動編輯每個單元格引用，因為它不會繼續RawData 工作表的引用，而是獲取單元格編號作為摘要表。

在所附的範例圖片中，如果我複製並貼上公式，我會得到單元格編號為 2、5、8、11 等，具體取決於行間隙，因為我需要複製公式，如手動公式列中所示像是B2、B3、B4、B5 順序，不論目前工作表行號。嘗試了很多事情但根本找不到任何解決方案。有什麼幫助嗎？

為了避免混淆，請從我的工作表中建立更真實的範例，並使用 RawData 標籤的螢幕截圖。

原始資料表：- 原始資料表

這是連結表，我在其中連結 RawData 表中的數據 產品概要表

如您所見，如果您複製貼上公式，則會顯示測試產品 1、測試產品 6 和測試產品 11，而這應該是測試產品 1、2 和 3。

這是範例 Excel 文件鏈接https://we.tl/t-3F3mJra7pe

Answer 1

首先我來描述這個問題背後的原因，

• 在總結表在儲存格中，F3公式=Rawdata!A2傳回值Test Product 1。
• 一旦你複製總結表資料來自 B3:F7at B8，Excel 讀取6 行之間並從 Cell ofF3:F8返回 Test Product 7B8原始數據床單。

這破壞了整個練習。

解決方案：

• 你需要輔助欄在原始數據床單。

• 重寫儲存B3格概括片材與 簡單產品1。
• F3在儲存格中輸入此公式概括床單。

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

你明白了。

• 現在，複製範圍B3:F7和貼上在單元格上B8。

你明白了。

• 重寫儲存B8格簡單產品2，現在您得到了正確的序列。

• 重複複製貼上與糾正簡單產品序列，你會得到想要的結果。

您可以根據需要調整公式中的儲存格參考。

Answer 2

在這些類型的場景中，我認為更簡單但更強大的解決方案是使用 OFFSET() 函數。 Offset 傳回單一儲存格或儲存格區域的參考。考慮到您的情況，如果第一個公式複製和貼上應引用 RawData Product1，第二次複製和貼上將引用 Product2，無論這些複製和貼上會跳過多少行，我都會按如下方式實現此 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() 將計算自列開始以來「簡單產品」出現的次數，並將使用此內部偏移量來引用順序位置中的對應產品。所有這一切都不需要建立輔助列。結果如下：

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