大家最近都在分享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 推 yenii:這篇初學者可能會看不懂 但超實用!! 讚!!! 118.168.165.100 02/10 13:48 ※ 編輯: olivia720410 來自: 111.248.66.185 (02/10 13:57)