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

[olivia720410 (最愛SHINee 泰民)]

大家最近都在分享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:好詳細!!!
推 musicya:推推!!!
推 alicejih:推 精華區能不能加開一個EXCEL專區
→ alicejih:最近的文章都很實用
推 hcw218:可以叫版主M起來嗎>////<
推 alicejih:推樓上~~ 超級希望這些可以被收錄阿
推 HOWARDSHEN:大主購~~~
→ olivia720410:小主購~~^_^ 忘記寫給集貨的分寄明細
→ olivia720410:我來補充一下吧^_^
推 Hainan:推推推!
→ Hainan:點了g單我就開始填起來了...可怕的慣性Q_Q
推 HOWARDSHEN:樓上XD 等等跟團沒匯款就.......
推 yenii:這篇初學者可能會看不懂 但超實用!! 讚!!!   118.168.165.100 02/10 13:48
※ 編輯: olivia720410    來自: 111.248.66.185       (02/10 13:57)
推 rosemary10:雖然我是初學者，但還是覺得超級實用^^
推 x3000:好強大喔!!我都只會用滑鼠拉~不過也很快速
推 boochu:看不太懂照推~~~~~ EXCEL學會真的超好用!!
推 linger411:還沒看完 先推 等等慢慢研究
→ sammy790609:太厲害了>///<
推 miriam1108:還沒仔細看完還是要先推推推!!!讚!!Y