贊助廠商

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

搜尋本部落格文章資料

網友問到:在 Excel 中如何如下圖將數字金額轉換為國字金額?

例如:將 472 轉換為肆佰柒拾貳元。

Excel-將數字金額轉換為國字金額(TEXT,MID,COLUMN,INT)

【公式設計與解析】

儲存格F1:

=TEXT(MID(TEXT($C1,"000"),INT(COLUMN(A:A)/2)+1,1),"[DBNum2]")

複製儲存格F1,分別貼至儲存格H1和儲存格J1。

(1) TEXT($C1,"000")

不管儲存格C1中的數字為幾位數,全部轉換為 3 位數,前面補 0。

(2) INT(COLUMN(A:A)/2)+1,1)

COLUMN(A:A)=1,向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:CA)=3→....。

INT(COLUMN(A:A)/2)+1,1)=1,向右複製每間隔兩欄貼上,可得 2, 3, 4, …。

(3) TEXT(第(1)式, 第(2)式, "[DBNum2]")

使用參數 [DBNum2],可以將數字顯示為『一、二、三、四、五、六、七、八、九、零』。

公式自動產生,儲存格H1:

=TEXT(MID(TEXT($C1,"000"),INT(COLUMN(C:C)/2)+1,1),"[DBNum2]")

公式自動產生,儲存格J1:

=TEXT(MID(TEXT($C1,"000"),INT(COLUMN(E:E)/2)+1,1),"[DBNum2]")

 

如果你要增加數值的位元數,則仿照以上的公式即可完成。

例如:將 8615 轉換為捌千陸佰壹拾伍元。

Excel-將數字金額轉換為國字金額(TEXT,MID,COLUMN,INT)

儲存格F1:

=TEXT(MID(TEXT($C1,"0000"),INT(COLUMN(A:A)/2)+1,1),"[DBNum2]")

其實公式完全一模一樣,複製儲存格F1,分別貼至儲存格H1和儲存格J1和儲存格L1。

文章標籤

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

參考下圖,網友問到如何在 Excel 的工作表中找出間隔欄位資料的最大值?

例如:在蘋果那一列找出最大的公斤數(500),並且求得最大公斤數對應的價格(24)。

Excel-找出間隔欄位資料的最大值(COLUMN,OFFSET,MATCH,陣列公式)

 

【公式計與解析】

1. 找出最大公斤數

儲存格C8:{=MAX((MOD(COLUMN(C3:K3),2)=1)*(C3:K3))}

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

複製儲存格C8,貼至儲存格C8:C9。

(1) MOD(COLUMN(C3:K3),2)=1

利用 COLUMN 函數來取得儲存格C3:K3範圍中每一個儲存格的欄數。再利用 MOD 函數其每個欄數除以 2 的餘數,若餘數為『1』,表示為 C, E, G, I, K 欄。

(2) (MOD(COLUMN(C3:K3),2)=1)*(C3:K3))

取得 C, E, G, I, K 欄的公斤數。

(3) MAX((MOD(COLUMN(C3:K3),2)=1)*(C3:K3))

將取得 C, E, G, I, K 欄的公斤數,利用 MAX 函數求得最大值。

 

2. 找出最大公斤數對應的價格

儲存格B8:=OFFSET(B3,0,MATCH(C8,C3:K3,0)-1)

複製儲存格B8,貼至儲存格B8:BC9。

(1) MATCH(C8,C3:K3,0)

利用 MATCH 函數求得儲存格C8的內容在儲存格C3:K3範圍裡的位置,傳回一個數值。

(2) OFFSET(B3,0,MATCH(C8,C3:K3,0)-1)

利用 OFFSET 函數代入第(1)式傳回的數值,找出對應的儲存格內容。

文章標籤

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

網友問到:如何在 Excel 的工作表中,依數值區間查詢分級的作法?

分級方式如下:

正常值       男生12~18                  ,女11~15

異常D級     男生10~11.9或18.1~19 ,女生10~10.9或15.1~16

異常C級     男生8~9.9或19.1~20    ,女生8~9.9或16.1~17

網友原本想要使用巢狀 IF 函數,但是我不建議這樣做。

參考下圖,由網友提供的分級方式,先轉換成可以供 VLOOKUP 函數查詢的表格,再使用VLOOKUP 函數建立公式。

如此,會比網友使用巢狀 IF 函數來的好,因為公式比較簡捷,而且分級對照的數值如果變動,只要修改對照表,不用修改公式。

Excel-依數值區間查詢分級(VLOOKUP)

 

【公式設計與解析】

儲存格B3:=CHOOSE(MATCH(B1,A8:B8,0),VLOOKUP(B2,A9:C15,3,TRUE),
VLOOKUP(B2,B9:C15,2,TRUE))

(1) MATCH(B1,A8:B8,0)

利用 MATCH 函數依據儲存格B1查詢在儲存格A8:B8範圍中的位置。若為男生,則傳回『1』;若為女生,則傳回『2』。

(2) VLOOKUP(B2,A9:C15,3,TRUE)

利用 VLOOKUP 函數依儲存格B2查詢儲存格A9:C15範圍的資料,傳回第 3 欄的結果。

(3) VLOOKUP(B2,B9:C15,2,TRUE)

利用 VLOOKUP 函數依儲存格B2查詢儲存格B9:C15範圍的資料,傳回第 2 欄的結果。

(4) CHOOSE(第(1)式,第(2)式,第(3)式)

利用 CHOOSE 函數,根據第(1)式的傳回值,若為『1』(男生),則執第(2)式;若為『2』(女生),則執第(3)式。

文章標籤

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

讀者根據這篇:

Excel-練習(以國中教育會考成績為例)(VLOOKUP,SUMPRODUCT,樞紐分析)

問到:(參考下圖)如果要計算每個學生所獲得 A 有幾個『+』、B 有幾個『+』,該如何處理。

Excel-練習(以國中教育會考成績為例)2(SUMPRODUCT,陣列公式)

 

【公式設計與解析】

選取儲存格K1:K8,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:等級標示、數量。

儲存格H2:=SUMPRODUCT((LEFT(C2:G2,1)="A")*(等級標示=C2:G2)*數量)

複製儲存格H2,往下各列貼上,即可求得每位學生換算得的總點數。

(1) 條件一:LEFT(C2:G2,1)="A"

SUMPRODUCT 函數中,利用 LEFT 函數將儲存格C2:G2取出第 1 個字,判斷是否為『A』。

(2) 條件二:(等級標示=C2:G2)*數量

取得儲存格C2:G2中每個儲存格內容所對照的數量。

如果使用陣列公式,則公式:

儲存格I2:{=SUM((LEFT(C2:G2,1)="A")*(等級標示=C2:G2)*數量)}

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

 

同理:

儲存格I2:=SUMPRODUCT((LEFT(C2:G2,1)="B")*(等級標示=C2:G2)*數量)

複製儲存格I2,往下各列貼上,即可求得每位學生換算得的總點數。

如果使用陣列公式,則公式:

儲存格I2:{=SUM((LEFT(C2:G2,1)="B")*(等級標示=C2:G2)*數量)}

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

文章標籤

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

網友提問:如何由下圖右的資料表(或稱規則)來根據PVC管來查詢計算結果。

查詢可以使用資料表,也可以不使用資料表。

Excel-根據規則建立查詢公式,不使用資料表(SUBSTITUTE,VLOOKUP)

 

【公式設計與解析】

一般的查詢動作,只要提供資料表,透過 VLOOKUP、INDEX、OFFSET 等函數都可以輕易達成。例如:

儲存格B3:=VLOOKUP(A3,E2:F21,2)

但是,其中都需要一個查詢用的資料表。

如果,你觀察你要查詢的內容具有一定的規則,也可以設計公式,不需透過資料表即可獲取結果。例如:

儲存格B3:=SUBSTITUTE(A3,"DB","")*200

本例中,PVC管名稱和計價的對應關係都有一定的規則,所以在此是利用 SUBSTITUTE 函數先將儲存格A3中的『DB』去除(名稱規則),得到一個數字,而該數字再乘上 200(計價規則)。如此,便不需透過資料表來查詢。

文章標籤

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

找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼