大量輸入含固定前綴的數據(身分證字號前 6 碼、電話區碼、地址縣市、員工編號前綴),逐字輸入極其低效。Excel 提供三種針對不同規律性的解法,讓您只輸入變動部分,固定部分自動補全。
方法一:自動填充 —— 固定前綴 + 規律遞增尾碼
適用:尾碼為等差數列(1,2,3... 或 001,002...)。
- 前 2~3 格手動輸入完整樣本:`123450001`、`123450002`、`123450003`。
- 選取這 2~3 格 → 拖曳右下角填充控制柄向下 → 選「複製儲存格」或「填充序列」。
Excel 自動識別規律,延續前綴與遞增尾碼。文字前綴同理:`一車間001`、`一車間002`...
方法二:自訂儲存格格式 —— 僅輸入尾碼,前綴自動顯示(最推薦)
適用:尾碼無固定規律、或不想佔用輔助欄。
- 選取目標儲存格區域 → Ctrl+1 開啟格式設定。
- 「數字」索引標籤 → 分類選「自訂」 → 「類型」輸入格式代碼:
- 純數字前綴(如 `0737070680` + 4 碼尾碼):
0737070680000#
(前綴 `0737070680` + 三個 `0` 強制顯示 0 + 一個 `#` 允許 1 位數字)
- 文字前綴(如 `山東省濰坊市` + 詳細地址):
"山東省濰坊市"@
(雙引號包住固定文字,`@` 代表文字佔位符)
圖:自訂格式設定介面(示意)
神奇效果:儲存格輸入 `1` → 顯示 `07370706800001`;輸入 `台南路100號` → 顯示 `山東省濰坊市台南路100號`。編輯列仍只見輸入值,公式引用也為輸入值,純顯示層面處理,不污染資料。
方法三:公式合併 —— 輔助欄分離固定/變動部分
適用:邏輯複雜、需動態組合、或需保留真實文字值供其他公式使用。
- 固定前綴放儲存格(如 A1 = `山東省濰坊市`),或直接寫在公式中。
- 變動部分輸入於輔助欄(B2:B10)。
- 目標欄(A2:A10)輸入公式:
=$A$1 & B2 → 向下填充。
- 選取結果欄 → Ctrl+C 複製 → 右鍵「選擇性貼上」→「數值」→ 確定 → 刪除輔助欄。
三法比較與選擇建議
| 方法 |
適用場景 |
資料本質 |
維護性 |
| 自動填充 |
尾碼規律遞增/遞減 |
真實完整值 |
規律中斷需重做 |
| 自訂格式 |
固定前綴、尾碼無規律、大量輸入 |
僅顯示層面、資料純淨、效能最佳 |
複製到其他軟體僅帶走輸入值 |
| 公式合併 |
邏輯複雜、需真實文字值、動態前綴 |
真實文字值、靈活組合 |
需輔助欄、貼上數值後靜態化 |
延伸:自訂格式代碼速查
- `0`:強制顯示數字位(不足補 0)
- `#`:選擇性顯示數字位(不足不補)
- `@`:文字佔位符
- `"文字"`:常值文字(需雙引號)
- 區段分隔符 `;`:正數;負數;零值;文字 四段式格式
善用自訂格式「只輸入變動部分」,大幅降低輸入負擔,資料品質與效率雙贏。