[心得] Excel主購整單函數分享

看板Chat82gether作者olivia720410 (最愛SHINee 泰民)時間12年前 (2012/02/10 13:13)推噓13(13推 0噓 5→)

留言18則, 12人參與討論串1/1

大家最近都在分享Excel技巧，我也來獻醜吧~~扭扭~~ 希望可以對大家有幫助^_^ 下面主要是分享團員境內、國際、ezShip、團員轉帳NT、給集貨的清單明細...等主購整單需要用到的函數觀念，若有需要對帳公式or推文驗證我再寫吧︿＿︿團員G單範例：http://ppt.cc/oiV4 我通常只讓團員填寫款式、顏色、件數...之類的內容，然後用Excel算出件數、金額、運費...等數字，這樣團員比較不會轉帳錯誤金額。在打開G單公式網址前，請主購們先自我測驗一下，如果要show出全團明細給團員看，上面有哪些資料可以用副本的方式直接抓取G單呢？答案是：PTT跟團ID、方式、A款、B款這四種資料，這個概念一定要有喔～主購G單公式：http://ppt.cc/F2cj 黃底部分用='工作表1'!位址的公式直接抓取團員填寫的G單資料。本來想要畫出excel分享excel函數，但是畫了好久覺得太浪費時間了，所以請大家把這個G單公式視窗開著，可以用來對照教學文喔～分享文開始囉～～久等了orz Part I開團常見的運費計算方式有以下兩種，這兩種都可以用同樣的Excel來整單重量一樣(這個比較最常見，如：單一合購物 or 多種合購物依照數量均分運費) 重量不同(如：兩種以上款式但預估重量不同) Ａ.重量一樣：單件境內運費＝境內運費／總件數Ｂ.重量不同：單件境內運費＝境內運費＊單件重量／總重量＝＞懶得判斷上述兩種情況的人，可以全都用Ｂ的觀念直接交給Excel計算 1.建議先在Excel上方寫出款式、批價、重量、總數、總重量、境內運費、集貨運費 -->這個表格如果寫好，不管重量一樣或不同，不管境內收運費或免運都可以通用紅底：整單後填入跟賣家談好的批價&每款重量，相同重量就填入相同數字即可。　綠底：境內免運就輸入0，如果要收運費請看G單公式紫底&下面說明。 2.賣家收取境內運費時，利用每款總件數和全團總重量就可以算出單件境內運費。　函數：sum( )計算總和、round( )四捨五入到小數點第2位、$絕對位址的運用 (1)D欄位"總數"、F欄位"總重量" A款的總件數D2輸入=sum(H6:J8) 或是用拖曳的方式 B款的總件數D3輸入一樣用sum( )加總計算　全團總重量 F2輸入=C2*D2+C3*D3每款重量*總數的和 (2)E欄位"境內"、G欄位"境內運費" 在G2境內運費輸入賣家實際收取的運費（22），如果境內免運輸入（0）就可以了　　避免拖曳改變公式位置要用$G$2絕對位址表示。　　單件境內運費＝境內運費＊單件重量／總重量(絕對位址) 　　　　 $G$2 * C2 / $F$2 　　這時候用round( )將單件境內運費四捨五入到小數點第2位，比較不會產生差額　　A款的單件境內E2輸入=round($G$2*C2/$F$2,2) B款的單件境內E3輸入　　　　　邏輯同上，直接拖曳公式即可 --------------------------入門與函數運用的分隔線-------------------------- Part II版上讓人疑惑的函數跟觀念，前面有很多人都覺得很簡單吧>"<　orz 1.每人境內運費：每款總件數*單件境內運費函數：sum( )、$ D6輸入=sum(I6:K6)*$E$2+sum(L6:N6)*$E$3 單件境內用絕對位址$E$2 $E$3表示 D7、D8...輸入　　　　　邏輯同上，直接拖曳公式即可　提問：這裡不需要再用round四捨五入是為什麼呢？　答案：因為單件境內運費E2跟E3已經四捨五入到小數點第2位了，所以，每人境內運費再怎麼加也不會出現小數點第3位的值喔～ 2.國際運費算法 (1)ez取件：分Ａ、Ｂ兩階段算出以下金額Ａ.ezShip寄件50元：G5欄位"ezShip"、H5欄位"方式" 　　　　函數：if( )、文字要加上" " a.首先看到H5方式，這裡已經先抓取團員填寫Ｇ單時選擇的寄送方式 b.G5的ezShip，要用公式判斷：　　　　　如果H6用"ez",寄件收50元,不是ez就不收錢也就是 if(H6 ="ez", 50 , "") ＊這邊要注意的是：文字一律加上" "excel才能辨識，數字不用喔 G6輸入=if(H6="ez",50,"") 用ez就會顯示50，用直寄會空白無資料(0元) G7、G8...輸入邏輯同上，直接拖曳公式即可　Ｂ.國際運費ez團共同分擔：O欄位函數：if( )、文字" "、sum( )、round( )四捨五入、roundup( )、$ a.O5的ez重量：如果H6用"ez",O6寫出團員買的重量,不是ez就不用寫提問：每位團員買的重量要如何計算呢？　　　　　答案：每款總件數*單件重量(C2和C3)-->邏輯類似每人境內運費團員買的重量=sum(I6:K6)*$C$2+sum(L6:N6)*$C$3 O6輸入=if(H6="ez",sum(I6:K6)*$C$2+sum(L6:N6)*$C$3,"") 　 O7、O8...輸入直接拖曳公式即可，用ez才會顯示重量 b.O3的ez總重：O4輸入=sum(O6:O8) 算出ez團的直寄總重量 O1的ez國際：O2輸入=roundup(O4,0)*$H$2 重量無條件進位法到個位數*集貨運費提問：每位團員分擔ez團直寄運費要如何計算呢？答案：ez國際運費*個人重量/ez團總重量-->邏輯類似單件境內運費 $O$2 * O6 / $O$4 ez每人國際運費=round($O$2*O6/$O$4,2) 這個公式等一下才會用到 (2)直寄：團員買的重量無條件進位到個位數*集貨運費-->邏輯結合上方兩個提問直寄每人國際運費=roundup(sum(I6:K6)*$C$2+sum(L6:N6)*$C$3,0)*$H$2 3.每人國際運費：E5"國際"欄位函數：if( )、sum( )、roundup( )無條件進位、$絕對位址的運用　如果H6用"ez",算出ez每人國際運費,不是ez就算出直寄每人國際運費 E6輸入=if(H6="ez",ez每人國際運費,直寄每人國際運費) roundup($O$2*O6/$O$4,2),roundup(sum(I6:K6)*$C$2+sum(L6:N6)*$C$3,0)*$H$2 　按照位置把剛剛上面的兩個公式貼上，每人國際運費就可以算出來了 4.轉帳NT：無條件進位or四捨五入法 (rmb總金額+境內+國際)*匯率+雜費+ezShip 學到這邊，轉帳NT應該已經可以用上面學到的函數自己寫出來了吧^_^ P.S.Excel用上手的人幾乎都省略Part I的步驟，我都把Part I一起寫進PartII公式裡，　　怕會因此搞混的人，建議還是保留Part I的步驟。以上函數內容如果有寫錯的地方，我會盡快修正的^_^ --------------------------集貨分寄明細整單-------------------------- 提問：請問東西到集貨處，要給集貨哪些資料呢？回答：收件人姓名、電話、地址、清單明細首先要有一個觀念，給集貨的資料都在哪個工作表呢？？ Ans：工作表1，也就是團員填寫Ｇ單的原始資料表，所以請到工作表1看公式喔～給集貨的清單明細：工作表1的K欄位函數：if( )、文字" "、&連結文字資料如果沒有買A款黑色E2=0,就不用寫,有買就寫出黑色幾件黑色判斷公式=if(E2=0,"","黑"&E2) 如果沒有買A款紅色F2=0,就不用寫,有買就寫出紅色幾件紅色判斷公式=if(F2=0,"","紅"&F2) 其他顏色依此類推，最後再用&連結所有判斷公式工作表1K2輸入=if(E2=0,"","黑"&E2)&if(F2=0,"","紅"&F2)&... 公式太長，可以到G單看完整版^_^ -- 看完後請記得給原po一個鼓勵喔～歡迎分享看過之後的感想不知道推什麼的話 ^把下面亮白色的文字直接複製貼上按enter就可以了 X1洽特就溫馨分享謝謝你! -- ※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 111.248.66.185

推

hcw218

02/10 13:26, , 1^F

02/10 13:26, 1^F

推

musicya

02/10 13:28, , 2^F

02/10 13:28, 2^F

推

alicejih

02/10 13:29, , 3^F

02/10 13:29, 3^F