【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

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

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

敬請期待喔!

【Excel】絕地改造系列(i)-對帳請款單-『才數計算公式』( Lesson 4/7)

完成了前兩課…外觀工程後~有沒有覺得請款單有專業的FU了?

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

今天我們將要進入函數的世界~

首先，先來介紹『才數計算公式』

分析：才數的計算方式為 (規格長×寬) ÷ 900，若有小數點無條件進位成整數。

=IF(AND(C5<>"",D5<>""),ROUNDUP(C5*D5/900,0),"")

今天我們使用三函數：

《1》  (規格長×寬) ÷ 900 ， 小數點無條件進位成整數 

〔ROUNDUP(數值，進位數)〕

ROUNDUP(12.1,0)=13

ROUNDUP(12.8,0)=13

ROUNDUP(12.666,1)=12.7

=ROUNDUP(C5*D5/900,0)

《2》有些欄位並不是才數的欄位，我們以規格是否有值在判斷是否要做才數的計算。

〔AND(條件 , 條件2 , 條件...)〕

可以有很多組條件，必需"同時成立"才行。

AND(1=1,2=2)=TRUE←值為真，代表成立

AND(1=1,2=1)=FALSE←值為假，代表不成立

=AND(C5<>"",D5<>"")

《3》利用先前有上課的IF函數來進行判斷，若該規格有值，就做才數的計算；若無值就留空白。如此，就不用每次有資料時才copy才數的計算公式過來。讓Excel自動幫你計算，這才是用此工具的目的-讓它幫你做事。

去複習一下喔! 

【Excel】IF，假如你不會用…小心落伍囉~( Lesson 1/1)

=IF(AND(C5<>"",D5<>""),ROUNDUP(C5*D5/900,0),"")

明後天有個非常重要的應用函數的課要上…大家要養足精神喔!

【Excel】IF，假如你不會用…小心落伍囉~( Lesson 1/1)

在職場上應用Excel，除了基本的加減乘除外，最常碰到一個問題，就是"判斷"~~

如果金額>多少，該怎麼處理，不是的話該怎麼處理?
如果除數是0，值應顯示什麼，不是的話就正常除即可←可以避免除數是零的錯誤產生。

如果…是的話…不是的話…
這類問題太常出現了，但若你不會用函數來處理，通常的結果就是讓錯誤在Excel表上顯現，然後再用人工的方式去修正掉。

所以我們今天來上一堂簡單的課~教會你如何應用基本的判斷式。

題目：在B1輸入章老師的年紀，如果30歲以下(含)，就說章老師青春無敵。但如果章老師大於30歲，就說章老師熟女風情。反正說好話每日一善無妨，實際年齡的話…要找也粉容易滴!

分析：這個問題就很單純，一個判斷而已…

            章老師年紀    >30    →章老師熟女風情

                                    <=30  →章老師青春無敵

解法：

=IF(B1>30,"章老師熟女風情","章老師青春無敵")
↑你可以把函式直接copy到Excel中貼上，就會看到結果囉!

作法及原理：
IF(條件 , (真)條件'成立'的顯示 , (假)條件'不成立'的顯示)

《1》我們的條件是什麼，就是章老師的年紀。至於章老師的年紀，是看B1的輸入值來決定。
我們來判定B1的值是否大於30
=B1>30

《2》當《1》的條件成立時，代表的是章老師已年過30了…所以要顯示"章老師熟女風情"。但是如果不成立呢，就必需顯示"章老師青春無敵"。所以把這兩個字串分別放入(真)與(假)的位置即可。
=IF(B1>30,"章老師熟女風情","章老師青春無敵")

IF其實非常簡單是吧!但它的應用卻可以很廣泛。
例如(真)與(假)的部份，不是只能帶"字串"喔~你也可以再加上另一個IF判斷式啊~
試一下吧!

回家作業：
請寫一個判斷式
假如章老師年紀0~20，顯示"活潑可愛"
                            21~40，顯示"小資女孩"
                            41以上，顯示"風韻猶存"
試一下喔!