贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

網友問到:在 Excel 的公式常會出現 ROW 函數和 COLUMN 函數,其主要的用途為何?

ROW 函數會傳回儲存格的列號,COLUMN 函數會儲存格的欄號。所以:

ROW(1:1)=1、ROW(2:2)=2、ROW(3:3)=3、...。

COLUMN(A:A)=1、COLUMN(B:B)=2、COLUMN(C:C)=3、...。

使用 ROW 和 COLUMN 來產生定數,尤其在複製後可以產生有規則的定數,這是非常好用的簡單方法。

觀察下圖:

Excel-認識ROW,COLUMN函數

以左圖為例:

儲存格B2:=B$1*$A2

複製儲存格B2,貼至儲存格B2:J10。

以右圖為例:

儲存格L2:=ROW(A1)*COLUMN(A1)

複製儲存格L2,貼至儲存格L2:T10。

這兩個式子的差別在於,右圖不需仰賴第1列和第A欄的數字。

其中:ROW(A1)*COLUMN(A1)=ROW(1:1)*COLUMN(A:A)

, ,

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

網友問到:在 Excel 的工作表中一個數值清單,其中每個儲存格內有3個以『,』隔開的數字,如何計算每個儲存格內數字的總和?

參考下圖左,A欄中每個儲存格有 3 個數字,並且以 2 個『,』隔開。如果以「資料剖析」工具,手動來將一個儲存格內容,調整為 3 個儲存格,再予以加總。這也是一個不錯的做法。但是,如果想要以公式來進行運算,該如何處理?如果是一個儲存格中有 4 個數字,又該如何處理?

Excel-計算儲存格中以逗號分隔的數字總和(SUBSTITUTE,MID)

【公式設計與解析】

1. 儲存格內有 3 個數字

儲存格B2:

{=SUM(1*MID(SUBSTITUTE(A2,",",REPT(" ",20)),(ROW($1:$3)-1)*20+1,20))}

這是陣列公式,公式輸入完成,按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格B2,貼至儲存格B2:B18。

(1) SUBSTITUTE(A2,",",REPT(" ",20))

假設每個數字都在數個位數以內,參數 20 是隨意的一個很大的數。

利用 SUBSTITUE 函數,將儲存格A2內容中的『,』全都置換為 20 個空格(REPT(" ",20)),結果如下:

image

(2) MID(第(1)式,(ROW($1:$3)-1)*20+1,20)

在陣列公式中,ROW($1:$3)={1,2,3},透過 MID 函數,藉以取出儲存格中,第 1, 21, 41 個字開始的20個字元。(本例可得:18__________________、17__________________、14__________________,其中『_』表示一個空格)

(3) 1*MID(第(1)式,(ROW($1:$3)-1)*20+1,20)

將第(2)式乘以 1,結果可以將取得的 3 個 20 字元的文字轉換為數字(本例可得:18、17、14)。

(4) SUM(1*第(2)式)

在陣列公式中,透過 SUM 函數將 3 個數字(=18、17、14)予以加總(=49)。


2. 儲存格內有 4 個數字

若儲存格內改為有 4 個數字,儲存格E2:

{=SUM(1*MID(SUBSTITUTE(D2,",",REPT(" ",20)),(ROW($1:$4)-1)*20+1,20))}

這是陣列公式,公式輸入完成,按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格E2,貼至儲存格E2:E18。

公式原理同『1. 儲存格內有 3 個數字』,只是將公式中 ROW($1:$3) 改成 ROW($1:$4) 即可。


3. 儲存格內有 n 個數字

如果你不想管儲存格內有幾個數字,而修改儲存格公式,該如何處理?

Excel-計算儲存格中以逗號分隔的數字總和(SUBSTITUTE,MID)

儲存格B2:{=SUM(1*MID(SUBSTITUTE(A2,",",REPT(" ",20)),(ROW(INDIRECT
("$1:$"& (LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1)))-1)*20+1,20))}

這是陣列公式,公式輸入完成,按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格B2,貼至儲存格B2:B18。

將原公式 ROW($1:$3) 改為:

ROW(INDIRECT("$1:$"& (LEN(A2)-LEN(SUBSTITUTE(A2,",",""))+1

如果儲存格有4個『,』,則會產生 ROW($1:$5);如果儲存格有6個『,』,則會產生 ROW($1:$7);...。

, ,

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

網友在 Excel 中想要製作大量的超連結,能夠從一個工作表,經由按一下的動作,就跳到另一個工作表的某個儲存格上,有辦法做到嗎?

參考下圖,在工作表1的儲存格A9上按一下「工作表2的A9」,就會自動跳到工作表2的儲存格A9。

Excel-大量產生連結到另一個工作表的超連結(HYPERLINK)

到達到這個效果,可以使用HYPERLINK函數來完成。參考以下的做法:

若要在工作表1中的儲存格A1(顯示:工作表2的A1)上按一下,要自動跳到工作表2的儲存格A1。輸入以下公式:

儲存格A1:=HYPERLINK("#工作表2!A"&ROW(1:1),"工作表2的A"&ROW(1:1))

複製儲存格A1,貼至儲存格A1:A15。

"#工作表2!A"&ROW(1:1):超連結的位址(置)。

當公式向下複製時,會產生「#工作表2!A1、#工作表2!A2、#工作表2!A3、….」

"工作表2的A"&ROW(1:1):超連結顯示的文字。

當公式向下複製時,會產生「工作表2的A1、工作表2的A2、工作表2的A3、…」

,

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

在使用 PowerPoint 時,如果想要將圖案中的文字分欄顯示,該如何處理?

(本篇以 PowerPoint 2013 為例)

Excel、PowerPoint-將圖案中的文字分欄顯示

如果你在 Word 文件中,將文字置入一個圖案裡,除了改變文字方向之外:

Excel、PowerPoint-將圖案中的文字分欄顯示

你還可以透過文字方塊設定其他選項:(Word)

Excel、PowerPoint-將圖案中的文字分欄顯示

在 PowerPoint 中的文字方塊選項多了一個「欄」的設定:(本例適用於 Excel)

Excel、PowerPoint-將圖案中的文字分欄顯示

選取圖案後,按一「欄」按鈕,設定分欄的數值(本例:2),即可將文字分多欄顯示。

Excel、PowerPoint-將圖案中的文字分欄顯示

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

網友在 Excel 的工作表中,想要產生重覆數字的數列(如下圖),例如:1,1,2,2,3,3,4,5,5,...,該如何處理?

在下圖中,如果選取儲存格B1:B4,再利用自動填滿方式,將會產生B欄的錯誤結果。所以,得利用公式來產生數列。

Excel-產生重覆數字的數列(ROW,INT)

【公式設計與解析】

1. 產生重覆 2 次的數列: 1,1,2,2,3,3,4,4,…

儲存格D1:=INT((ROW(1:1)-1)/2)+1

複製儲存格D1,往下各列貼上。

2. 產生重覆 n 次的數列(例如 n=5): 1,1,1,1,1,2,2,2,2,2,3,3,3,3,3,4,4,4,4,4,…

儲存格F1:=INT((ROW(1:1)-1)/$G$2)+1

n 置於儲存格G2,當 n 值改變時,數列隨之改變。

複製儲存格F1,往下各列貼上。

, ,

vincent 發表在 痞客邦 PIXNET 留言(0) 人氣()

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼