เปรียบเทียบค่าขนส่งพัสดุด้วย Google Sheets แบบไม่ต้องใช้ IF หลายชั้น

นอกจาก Google Sheets จะสามารถสร้างเอกสารได้อย่างหลากหลายแล้ว แต่ความสามารถที่ดีอีกอย่างเลยก็คือการคำนวณ บทความนี้จะพาคุณใช้คำสั่ง =QUERY() ในการหาค่าส่งพัสดุเพื่อใช้เปรียบเทียบกันหลายๆบริษัท หากคุณเป็นพ่อค้าแม่ค้าออนไลน์ไม่ควรพลาดครับ

ทำความเข้าใจการคำนวณราคากันก่อนนะ #

ก่อนที่เราจะเปิด Google Sheets แล้วมาเริ่มลงมือกัน ผมอยากอธิบายกันก่อนว่าวิธีคิดราคาค่าขนส่งในตลาดทุกวันนี้เป็นอย่างไรกันบ้างครับ ไม่ใช่ว่าทุกเจ้าจะมองที่น้ำหนักอย่างเดียว บางเจ้าดูตั้งแต่ขนาดกล่อง, น้ำหนัก, รวมไปถึงจังหวัดปลายทางที่จะส่งไปด้วย เพื่อไม่ให้งงตอนที่ผมพาใช้เครื่องมือ งั้นเรามาดูกันว่าแต่ละที่คิดยังไง ซึ่งวันนี้ผมจะขอยกตัวอย่างเพียง 2 เจ้านะครับ ซึ่งอนาคตวิธีคิดอาจจะมีการเปลี่ยน แต่สำหรับบทความนี้จะอิงตามปัจจุบันนะครับและนี่เป็นการส่งแบบภายในประเทศเท่านั้นนะครับ

1. EMS Thailand Post #

ไปรษณีย์ไทยของเรานั่นเอง รายนี้สนใจแค่ น้ำหนัก ของพัสดุที่จะส่งไปเท่านั้นเอง แต่เนื่องจากช่วงนี้ไปจนถึง 15 กันยายน 2562 มีโปรโมชั่นลดพิเศษ ผมก็จะคิดทั้ง 2 แบบเลยแล้วกัน

2. Kerry Express #

รายนี้จะมีปัจจัยที่ต้องเช็คกันหลายอย่าง ดังนี้ครับ

• ขนาดของกล่องบรรจุ จะใช้ผลรวมของด้านกว้าง ยาว และสูงของกล่องมาคิดนะครับ
• น้ำหนัก
• จังหวัดปลายทาง เนื่องจากมีการแบ่งโซนเป็น 3 โซนครับ คือ ภายในภาค, ภาคใกล้ และภาคไกล
• พื้นที่ห่างไกลหรือเปล่า? ตรงนี้ส่วนมากจะเป็นพวกเกาะหรือบนดอยครับ

เริ่มสร้างตารางค่าขนส่ง #

วิธีของผมจะเริ่มจากการเอาค่าขนส่งของแต่ละบริษัทมาสร้างเป็นตารางของใครของมันก่อนนะครับ ซึ่งแต่ละที่ก็จะแตกต่างกันไปครับ

1. EMS Thailand Post #

อันนี้ผมดึงมาจากตารางที่ Google มาได้นะครับ ผมไม่ยืนยันว่าข้อมูลนี้ถูกต้องนะครับ แต่ทุกท่านสามารถหาได้จากไปรษณีย์สาขาใกล้บ้านได้ครับ ตารางนี้ผมจะให้ column A คือ น้ำหนัก นะครับ ส่วนอีก 2 columns จะเป็นราคาของแบบปกติและแบบโปรโมชั่น ส่วนแถวสุดท้ายผมจะเพิ่มเข้าไปเพื่อเอาไว้กันขอบเขตของข้อมูลแล้วก็จะมีประโยชน์ในการ query ด้วยครับ

ในหน้า template จะมีให้เราเลือกใช้อยู่หลายอันครับ แต่วันนี้เราจะมาทำ Invoice ดังนั้นเราก็ต้องเลือก Invoice

2. Kerry Express #

จากที่ Google มาผมก็ได้ตารางหน้าตาแบบนี้มาครับ

แต่ตัวนี้ผมจะปรับนิดหน่อยไม่ได้ลอกตามที่ kerry ให้มาเพราะว่าราคาถูกแบ่งเป็น 3 columns ตามโซนครับ เพื่อให้ง่ายต่อการ Query ผมจะปรับมันเป็นแบบนี้ครับ

ผมเพิ่มข้อมูลเอาไว้สำหรับกำหนดขอบเขตข้อมูลเช่นกันนะครับ ที่มีค่าเป็น 999 นั่นเอง

อย่าลืมกล่อง #

เนื่องจาก Kerry Express ต้องการขนาดของกล่องด้วย ผมจึงจำเป็นต้องใส่ข้อมูลความยาวและน้ำหนักของกล่องเข้ามาด้วย เพราะกล่องใบละไม่กี่ขีดอาจจะทำให้ราคาค่าขนส่งได้เหมือนกัน โดย column F ที่เป็น ขนาดรวม ผมใช้ =ARRAYFORMULA() มาช่วย เราจะได้ไม่ต้องมาคอยบวกเองในอนาคตถ้ามีกล่องขนาดใหม่เข้ามา สูตรก็จะหน้าตาแบบนี้ครับ

=ARRAYFORMULA(IF(A2:A<>"",B2:B+C2:C+D2:D, ""))

อธิบายสูตรก่อนเพื่อความเข้าใจ

• =IF(A2:A<>"",X,Y) คำสั่งนี้ก็คือ IF เป็นการกำหนดว่า ถ้า column A ตั้งแต่แถว 2 เป็นต้นไปจนแถวสุดท้ายของตารางมันไม่ว่างเปล่า (Z<>"" แปลว่า Z ต้องไม่ว่างเปล่า) ให้เข้าไปทำคำสั่ง X แต่ถ้าว่างเปล่าก็คือไปทำคำสั่งใน Y ซึ่งในข้อนี้ตัวอย่างของผม X ก็คือการเอา กว้าง + ยาว + สูง และ Y ก็คือความว่างเปล่าไม่ต้องแสดงอะไรในช่องนั้น
• =ARRAYFORMULA() คำสั่งนี้เอาไว้ทำซ้ำคำสั่งที่อยู่ในวงเล็บ โดยจะทำตาม range ที่กำหนดไว้อยู่แล้ว เช่น A2:A<>"" แทนที่ปกติเราจะใช้แค่ A<>"" อย่างนั้นแหละครับ

ฟอร์มข้อมูลพัสดุและเปรียบเทียบราคา #

มาถึง sheet สุดท้ายที่เราจะต้องทำแล้วครับ ตารางนี้เราก็จะมีการรับ input จากผู้ใช้ดังนี้ครับ

• น้ำหนัก
• ขนาดกล่อง จาก sheet ที่เราทำไว้
• โซนของจังหวัดปลายทาง (วันนี้กรอกตรงๆก่อนนะครับ อนาคตจะพาทำแบบที่หาจากจังหวัดได้เลย)
• อยู่พื้นที่ห่างไกลหรือเปล่า

ออกมาได้ก็ประมาณนี้นะครับ สำหรับใครที่สงสัยว่าเส้นตารางหายไปไหน ปิดได้ที่เมนู View > Gridlines

แต่ละช่องก็จะมีรายละเอียดนิดหน่อยนะครับตามนี้นะครับ

น้ำหนักสินค้า #

กำหนดค่าให้อยู่ระหว่าง 0.5 ถึง 25 นะครับ เพราะเป็นค่าต่ำสุด สูงสุดเท่าที่มีในตารางครับ

ขนาดกล่อง #

แสดง column A จาก sheet กล่อง มาเป็นตัวเลือกครับ

ความยาวรวมกับน้ำหนักกล่อง #

เผื่อใครคิดถึง VLOOKUP ตอนนี้ได้ใช้แล้วนะครับ ผมใช้ดึงค่าความยาวรวมของกล่องมาจากอีก sheet นะครับ ส่วนคำสั่ง =IFERROR() คือคำสั่งที่จะให้เราแสดงค่าอะไรก็ได้ถ้าเจอ Error จากคำสั่งในวงเล็บครับ ซึ่งตรงนี้ผมจะไม่แสดงค่าอะไรครับ

• ความยาวรวม =IFERROR(VLOOKUP(C3,'กล่อง'!A2:F5,6,false),"")
• น้ำหนักกล่อง =IFERROR(VLOOKUP(C3,'กล่อง'!A2:F5,5,false),"")
• น้ำหนักรวมของพัสดุและกล่อง =C5+C2

โซน #

กำหนด list เองตามนี้ครับ “ภายในภาค”, “ภาคใกล้”, “ภาคไกล”

พื้นที่ห่างไกล #

กำหนด list เองตามนี้ครับ “ใช่”, “ไม่ใช่”

ส่วนเปรียบเทียบราคา #

ส่วนนี้เราจะใช้คำสั่ง Query หาราคาของแต่ละบริษัทครับผม

Kerry Express #

=QUERY(kerry!A1:E31, "select min(E) where B = '"&C7&"' and (D >= "&C6&" and C >= "&C4&") label min(E) ''") + IF(C8="ไม่ใช่",0,50)

แทนที่จะใช้ IF ซ้อนกันหลายๆชั้น ผมเปลี่ยนมาใช้ QUERY หาค่าน้อยที่สุด min(E) ซึ่งก็คือราคาครับ โดยเป็นราคาที่ถูกกำหนดด้วยโซน B7 และน้ำหนักรวมกับขนาดกล่องด้วยครับ ผมใช้เงื่อนไขเป็น “มากกว่าหรือเท่ากับ (>=)” ทั้งสองค่าเลยนะครับ เช่น ผมส่งในภาคเดียวกัน มีขนาดกล่อง 95 ซม. พัสดุหนัก 7 กก. ลองกลับไปดูตารางของ kerry แล้วจะเห็นว่าเงื่อนไขนี้จะตรงกับแถวที่ 7 - 11 เลยและเมื่อเราหาช่องที่ราคาต่ำสุด min(E) เราก็จะได้ราคา 130 บาทนั่นเอง แล้วสุดท้ายจะมีการบวกเพิ่ม 50 บาทในกรณีที่เป็นพื้นที่ห่างไกลด้วยครับ

EMS Thailand Post #

=QUERY(ems!A2:C21, "select min(B) where A >= "&C6&" label min(B) ''")

คล้ายๆกันกับ Kerry ครับแต่ง่ายกว่าหน่อยตรงที่ไม่ต้องเอาขนาดหรือโซนมาคิดด้วย คำสั่งก็เลยสั้นลง

EMS Thailand Post Promotion #

=QUERY(ems!A2:C21, "select min(C) where A >= "&C6&" label min(C) ''")

เหมือนข้างบนเด๊ะเลยครับแต่เลื่อนมาอีก column เท่านั้นเอง

เพื่อความเนียนราคาที่เกินขอบเขตการให้บริการที่เป็น 999 ผมจะทำให้มันหายไป ด้วยการเลือก C10:C12 แล้วคลิกขวา เลือกที่ Conditional formatting แล้วเปลี่ยนค่า 999 ให้เป็นสีขาวทั้งพื้นหลังทั้งตัวอักษรครับ

ตารางเปรียบเทียบราคาของเราก็จะประมาณนี้ครับ

ถ้ามีการใส่ค่าที่เกินขอบเขตบริการก็จะเป็นช่องว่างแบบนี้ครับ

เรียบร้อยแล้วครับเพียงเท่านี้ คุณก็สามารถเลือกใช้บริการขนส่งที่ถูกที่สุดได้อย่างง่ายๆ ถ้าใครอยากจะเพิ่มข้อมูลของบริษัทอื่นๆอีก ก็เพียงแค่เพิ่ม sheet แล้วทำตารางตามตัวอย่างที่ผมทำให้ดูเป็นตัวอย่างครับ อะไรที่เป็น input ใหม่ที่เราไม่เคยมีเราก็เอามาเพิ่มในฟอร์มคำนวณด้วยเช่นกัน

สรุปสูตรที่เราเรียนรู้วันนี้กันครับ #

• =QUERY(range, query) คำสั่งดึงข้อมูลที่เราต้องการ เพียงแค่กำหนด range และเขียน query select xxx where xxx เราก็จะได้สิ่งที่ต้องการ อ่านง่ายและเข้าใจกว่า VLOOKUP แน่นอน
• =VLOOKUP(search, range, column, sort) คำสั่งค้นหาค่าจาก range และลำดับ column ที่ระบุ
• =ARRAYFORMULA() คำสั่งที่จะช่วยทำซ้ำคำสั่งที่อยู่ด้านในอีกทีตาม range ที่กำหนด
• =IF() คำสั่งกำหนดเงื่อนไขในการใส่ค่าต่างๆของ cell นั้นๆ

Google Sheets หรือ Excel ไม่ได้จำเป็นต้องเรียนรู้กันทุกคนหรอกครับ แต่ผมเชื่อว่าทุกสาขาอาชีพสามารถเอามาใช้เป็นเครื่องมือที่ช่วยผ่อนแรงกาย แรงสมองได้อย่างแน่นอนครับ รู้มากกว่าย่อมดีกว่า ถ้าอยากรู้มากกว่านี้ก็ตามอ่านได้ที่นี่นะครับ :)