【Excel進階教學】如何快速複製多筆課程資料，並自動產生對應學號？

在處理大量學生資料時，你是否也曾遇到需要為每位學生展開所有必修課程的場景？例如，要比對全班學生的修課狀況，確認是否都達到畢業門檻。如果一個個手動複製貼上，不僅耗時，還容易出錯。

今天，我們將分享一個實用的 Excel 技巧，教你如何利用幾個簡單的函數，快速將一份課程清單複製給多位學生，並自動產生對應的學號，大幅提升你的工作效率！

情境說明：
假設我們有兩份資料：

1. 一份是所有學生都必須修的 10 門課程清單。

2. 另一份是全班 10 位學生的學號清單。

我們的目標是產生一份完整的總表，將每位學生的學號與這 10 門課程一一對應，如下圖所示：

這個技巧主要會運用到以下幾個 Excel 核心觀念：

• Excel 複製貼上的「自動重複」特性

• ROW() 函數：產生流水號

• INT() 函數：取整數

• OFFSET() 函數：根據位移傳回儲存格參照

---

操作步驟

步驟一：快速複製課程清單

這是個非常方便卻較少人知道的 Excel 隱藏技巧。當你複製的來源範圍小於貼上的目標範圍時，Excel 會自動重複填滿。

1. 複製課程清單：首先，將你的課程清單（此範例為 科目1 到 科目10）選取並複製 (Ctrl+C)。

2. 選取目標範圍：我們有 10 位學生，每人 10 門課，總共需要 100 筆課程資料。在要貼上的欄位，直接在左上角的「名稱方塊」中輸入目標範圍，例如 B2:B101，然後按下 Enter。Excel 就會幫你選取這 100 個儲存格。

3. 一次貼上：直接按下貼上 (Ctrl+V)。你會發現 Excel 自動將 10 門課程的區塊重複了 10 次，完美地填滿了 100 個儲存格。

步驟二：公式產生對應學號

課程資料準備好了，接下來的挑戰是如何讓學號「A001」重複 10 次，接著換「A002」重複 10 次，依此類推。這就要靠公式來完成了。

1. 公式核心：OFFSET 函數
   我們使用 OFFSET 函數，它可以從一個基準點開始，根據指定的「列、欄」位移量，抓取對應的資料。

   • 基準點：我們的學號清單在 工作表2 的 A2 儲存格，所以基準點就是 工作表2!$A$2（記得加上 $ 鎖定位置）。

   • 位移量：這是最關鍵的部分。我們需要產生一個序列，讓前 10 個儲存格的列位移量是 0（抓取第一個學號），第 11 到 20 個儲存格的位移量是 1（抓取第二個學號），以此類推。

2. 產生位移量的規律
   要產生 0, 0, ... (10次), 1, 1, ... (10次), 2, ... 這樣的序列，我們可以組合 ROW 和 INT 函數：

   • ROW(A1)：當公式向下拖曳時，會產生 1, 2, 3, ... 的流水號。

   • (ROW(A1)-1)：因為我們的位移是從 0 開始，所以先減 1，讓序列變成 0, 1, 2, ...。

   • (ROW(A1)-1)/10：將序列除以 10（因為每 10 筆資料換一個學號），會得到 0.0, 0.1, ..., 0.9, 1.0, 1.1...

   • INT((ROW(A1)-1)/10)：使用 INT 函數取整數，就能得到我們想要的 0, 0, ..., 1, 1, ... 位移序列！

3. 組合完整公式
   在學號欄位的第一個儲存格（A2）輸入以下公式：

   Generated excel

   =OFFSET(工作表2!$A$2,INT((ROW(A1)-1)/10),0)

   • 工作表2!$A$2：學號清單的起始位置。

   • INT((ROW(A1)-1)/10)：計算列的位移量。

   • 0：欄的位移量，因為學號都在同一欄，所以是 0。

4. 填滿公式
   輸入公式後，將滑鼠移到儲存格右下角，當游標變成黑色十字時，快點兩下，公式就會自動填滿所有對應的儲存格。

---

總結

透過這個範例，我們學會了兩個非常高效的技巧：

1. 利用 Excel 的自動重複貼上功能，可以快速處理重複性資料的建置。

2. 組合 ,  三個函數，可以根據規律性，從來源清單中動態抓取對應的資料，自動產生流水號或分類標籤。

下次再遇到類似的資料整理需求，不妨試試這個方法，告別重複的體力活，讓 Excel 成為你真正的效率神器！