【Excel】絕地改造系列(i)-對帳請款單-『取得單價計算總價(2/2)』( Lesson 6/7 )

【Excel】絕地改造系列(i)-對帳請款單-『拉皮工程』(Lesson 2/7)
【Excel】絕地改造系列(i)-對帳請款單-『儲存格格式自定』( Lesson 3/7 )
【Excel】絕地改造系列(i)-對帳請款單-『才數計算公式』( Lesson 4/7)
【Excel】絕地改造系列(i)-對帳請款單-『取得單價計算總價(1/2)』( Lesson 5/7 )

本系列堂內最後一堂課~~撐著點各位~

分析：總價的計算方式為 (才數×數量×單價)，若單價<150則以150計算

經過昨日的課程，我們已取得單價的計算方式
=IF(ISNA(VLOOKUP(B5,Price!A:B,2,FALSE)),"未設單價",VLOOKUP(B5,Price!A:B,2,FALSE))

《1》判斷有「才數」的總價計算為 (才數×數量×單價)，若沒「才數」則為(數量×單價)

同樣要使用的是IF函數來判斷，至此有沒有發現…『假如』真的很重要。
【Excel】IF，假如你不會用…小心落伍囉~( Lesson 1/1)

但在寫函數之前，先想一下，若沒有單價還需計算嗎?所以我們要從單價函數中最後一段動手腳喔!
=IF(ISNA(VLOOKUP(B5,Price!A:B,2,FALSE)),"未設單價",VLOOKUP(B5,Price!A:B,2,FALSE))
假如該品名找不到單價，則顯示"未設單價"，否則----假如有才數，則計算 (才數×數量×單價)，否則計算(數量×單價)
↑看到了嗎?有兩個假如喔~~
=IF(ISNA(VLOOKUP(B5,Price!A:B,2,FALSE)),"未設單價",IF(E5<>"",E5*F5*VLOOKUP(B5,Price!A:B,2,FALSE),F5*VLOOKUP(B5,Price!A:B,2,FALSE)))
昏頭了嗎?把單價(VLOOKUP(B5,Price!A:B,2,FALSE))那串用「@單價」來替代
=IF(ISNA(VLOOKUP(B5,Price!A:B,2,FALSE)),"未設單價",IF(E5<>"",E5*F5*@單價,F5*@單價))
這樣看得出來嗎??

《2》注意一下，有才數的總價計算，因為若<150，則必需以150做為總價。
所以…各位同學，我們該用什麼函數來判斷呢?
是的…又是IF了~~
我們在 (才數×數量×單價)的這一段裡…再加入IF判斷式~
=IF(ISNA(VLOOKUP(B5,Price!A:B,2,FALSE)),"未設單價",IF(E5<>"",E5*F5*VLOOKUP(B5,Price!A:B,2,FALSE),F5*VLOOKUP(B5,Price!A:B,2,FALSE)))
E5*F5*VLOOKUP(B5,Price!A:B,2,FALSE)   →  IF(E5*F5*VLOOKUP(B5,Price!A:B,2,FALSE)<150,150,E5*F5*VLOOKUP(B5,Price!A:B,2,FALSE))

如果看不懂…再另外問老師我吧!
把那一段判斷式，放回原來那一長串函式裡~
=IF(ISNA(VLOOKUP(B6,Price!A:B,2,FALSE)),"未設單價",IF(E6<>"",IF(E6*F6*VLOOKUP(B6,Price!A:B,2,FALSE)<150,150,E6*F6*VLOOKUP(B6,Price!A:B,2,FALSE)),F6*VLOOKUP(B6,Price!A:B,2,FALSE)))

這就是最終的答案喔!

對於這段落落長的函數…各位有啥感受!
其實這麼長的函數…不過也就是用了那3個IF、ISNA、VLOOKUP罷了…其中IF相對重要。會拉這麼長也是因為不斷的判斷、判斷、判斷。
用常理的思考角度去把函式寫出來…無需賣弄計巧或找特別的函數…

最好的程式，就是最簡單易懂的程式~~最白話的文字~

這樣過了很久之後，再回頭改函數時…相信光用看的就會知道這段在做什麼判斷了!
加油喔!各位~~

至今最長的一門課…到這裡告一段落!!明日還有番外篇喔~請期待~

【Excel】絕地改造系列(i)-對帳請款單-『取得單價計算總價(1/2)』( Lesson 5/7 )

【Excel】絕地改造系列(i)-對帳請款單-『拉皮工程』(Lesson 2/7)
【Excel】絕地改造系列(i)-對帳請款單-『儲存格格式自定』( Lesson 3/7 )
【Excel】絕地改造系列(i)-對帳請款單-『才數計算公式』( Lesson 4/7)


上完了前幾堂課，有沒有累啦!!提起精神，精華在這兒喔!
今天要教各位如何取得產品的單價，好計算總價喔!

分析：總價的計算方式為 (才數×數量×單價)

但好玩的是…單價在哪兒呢????

所以本課程上半段，將介紹如何另建單價資料表，然後利用函數取得產品單價。

《1》先另新增一個工作表〔Price〕，將品名及單價建立進去：每個品名只要建一次即可。

《2》在對帳單的工作表裡，先利用『備註』欄來測試取得單價。

〔VLOOKUP(尋找值 , 尋找範圍 , 若找到取得範圍內哪一欄的值 , 完全比對或部份比對)〕

尋找值：要找哪個產品的單價，例：B5→A37/PVC霧
尋找範圍：在單價表裡找，例：Price表 A:B的範圍裡
若找到取得範圍內哪一欄的值，例：Price裡找到帶回第2欄，也就是單價欄的值。
完全比對或部份比對：完全比對(FALSE)→品名要完全一模一樣，部份比對(TRUE)→只要品名裡有一段相同的即可。

=VLOOKUP(B5,Price!A:B,2,FALSE)
將函數複製到其它備註欄位。

《3》針對找不到單價的項目，改成文字提示。
〔ISNA(判斷值)〕
若判斷值為#N/A，則回傳TRUE，若判斷值不是#N/A，則回傳FALSE
ISNA(VLOOKUP(B5,Price!A:B,2,FALSE))=FALSE→總價H5
ISNA(VLOOKUP(B7,Price!A:B,2,FALSE))=TRUE→總價H7

再搭配IF函數，使得取不到單價的項目，回傳文字提示。
【Excel】IF，假如你不會用…小心落伍囉~( Lesson 1/1)

=IF(ISNA(VLOOKUP(B5,Price!A:B,2,FALSE)),"未設單價",VLOOKUP(B5,Price!A:B,2,FALSE))

到這裡…我們已廣泛運用了

IF

ISNA

VLOOKUP

三個函數囉~這三個都是相當好用且可經常使用到的，多花些時間練習喔!!

接下來，明日，我們將會把總價的計算方式整合起來~

敬請期待喔!