Excel合併儲存格編號:Ctrl+E自動填滿,MAX及Countif函數

Excel表格使用合併儲存格雖然整潔美觀,很多時候不是很方便,本文介紹Ctrl+Enter鍵盤快速鍵用法,如何自動填滿,進而設計MAX等函數公式為合併儲存格連續編號。

一、合併儲存格表格

範例是一個課程大綱表格資料。可以看到在章的部分由合併儲存格組成,每一章有各個小節課程名稱,在最左側是序號欄位,同樣是合併儲存格。

關於Excel表格如何利用合併儲存格,可以參考贊贊小屋相關文章

Excel合併儲存格編號:Ctrl+E自動填滿,MAX及Countif函數 1

二、Ctrl+Enter輸入

首先介紹如何在多個儲存格統一輸入相同的文字,例如在表格右邊新增一欄,選擇第3列到第14列的範圍,接著在資料編輯裡輸入文字,例如「成本會計」,然後鍵盤按住Ctrl+Enter快速組合鍵,如此就會在所選取的範圍同時都輸入「成本會計」,並且因為原來的範圍已經設置好了合併儲存格,所以同樣會是在三個合併儲存格輸入「成本會計」的文字內容。

Excel合併儲存格編號:Ctrl+E自動填滿,MAX及Countif函數 3

三、範圍寫入公式

除了輸入純粹的文字資料,也可以輸入函數公式,例如簡單的「=ROW()」,如此會在三個合併儲存格得到相對應的列號,從輸入結果很容易可以得知在Excel所謂的合併儲存格輸入資料,其實是把資料輸入在合併範圍內的最左上角那一個儲存格裡面。

關於Excel ROW函數進階用法,可以參考贊贊小屋相關文章

Excel合併儲存格編號:Ctrl+E自動填滿,MAX及Countif函數 5

四、分散儲存格輸入

除了一整個相鄰範圍進行統一輸入之外,其實也可以先按住「Ctrl」,然後在工作表上面任意選取分散的儲存格,接著再輸入公式,例如「=COLUMN()」,資料編輯裡輸入完公式之後,同樣鍵盤按住Ctrl+Enter快速組合鍵,便能在分散儲存格同時輸入相同公式。上個步驟ROW函數是取得列號,這裡的COLUMN函數則是取得欄號。

關於Excel如何輸入函數公式的技巧,可以參考贊贊小屋相關文章

一張含有 文字 的圖片

自動產生的描述

五、自動填滿資料

先前步驟瞭解了合併儲存格的機制還有如何同時輸入資料之後,在此想要將在合併儲存格輸入連續編號,利用自動填滿的技巧,在第一個合併儲存格輸入「1」,之後將游標移到儲存格的右下角,將游標往下拉,會發現到沒有辦法執行,提示「若要這麼做,所有合併儲存格的大小都必須相同。」

既然它是說相同大小的合併儲存格,可以簡單做一個測試,建立三個具有相同大小的合併儲存格,同樣在第一個合併儲存格輸入「1」,之後將游標移到儲存格右下角,滑鼠游標往下拉,果然已經自動填滿了「1」、「2」、「3」,所以不同大小的合併儲存格沒有辦法,但是只要合併儲存格具有相同大小,還是能自動填滿。

關於合併儲存格自動填滿的操作,也許靜態文章圖片比較無法生動的說明,讀者有興趣請參考文章下方所附的YouTube影片。

Excel合併儲存格編號:Ctrl+E自動填滿,MAX及Countif函數 8

六、MAX函數用法

想要在合併儲存格建立連續編號,首先可以設計函數公式「 MAX($D$2:D2)+1」,這裡由於D2到D2的範圍沒有任何數值,只有文字,因此它的最大值會是0,再加1就是1,而且注意到這裡前面的D2是用錢字號「$」固定起來,沒有「$」則是會在公式自動填滿時自動跳號,因此當我在選取D3到D14的範圍,然後同時輸入公式的時候,它跟先前第三步驟的 Row函數公式一樣,其實會在合併儲存格最左上角的儲存格輸入公式,也就是儲存格D3、D6、D10,而在D6儲存格輸入時,公式會自動變成是D2到D5的範圍,這個時候範圍內一個是數值、一個是文字,然後數值部分是在D3儲存格所計算的合併儲存值「1」,因此D6公式中的MAX最大值為是1,1+1=2,所以D6公式計算結果為「2」。至於D10儲存格的「3」,也是相同原理函數公式計算出來的結果。

關於Excel MAX函數其他用法,可以參考贊贊小屋相關文章

Excel合併儲存格編號:Ctrl+E自動填滿,MAX及Countif函數 10

七、COUNTIF函數

除了MAX函數,相同公式結構使用COUNT函數、COUNTA函數、COUNTIF函數都可以達到相同的結果,以COUNTIF函數為例,公式為「=COUNTIF($A$2:A9,”>0″)+1」,雖然是不同函數、不同的計算機制,原理大致上都是先從一個固定的儲存格位置開始,公式自動填滿延伸到目前儲存格,再利用合併儲存格特性,從上到下範圍進行函數計算,最後得到連續的編號。

由於原理相同,其他函數在文章這裡不再一一說明,而是將公式列在下方,讀者有需要可以參考本篇文章所附的YouTube影片,配合實際操作測試或贊贊小屋練習題會有更為具體的瞭解。

關於Excel Countif函數其他用法,可以參考贊贊小屋相關文章

=MAX($D$2:D2)+1

=COUNT($E$2:E2)+1

=COUNTA($A$3:A3)

=COUNTIF($A$2:A2,”>0″)+1

Excel合併儲存格編號:Ctrl+E自動填滿,MAX及Countif函數 12

合併儲存格連續編號

本篇文章的範例是課程大綱,不過在瞭解Ctrl+E多儲存格輸入、合併儲存格機制、函數公式自動填滿的用法及原理之後,可以想見在其他實務工作的場合,只要有用到Excel合併儲存格,表格資料是有明細項目以及大分類小分類的結構,同樣都可以使用本文所介紹到的指令函數技巧。

每天學習,每天充電:Excel函數文章合集

Excel合併儲存格編號:Ctrl+E自動填滿,MAX及Countif函數 14
加入Line社群,口袋裡的Excel小教室!