สมมุติว่าผมมีตารางข้อมูลใน Work Sheet ที่ชื่อ Master Product ดังแสดงในตารางที่ 1 ครับ
ตารางที่ 1 ตาราง Master Product
ในแต่ละวันแฟนผมจะได้รับออร์เดอร์จากลูกค้าในการสั่งซื้อสินค้าแต่ละชนิดโดยข้อมูลการสั่งซื้อจะถูกบันทึกไว้ในไฟล์ Excel ที่ Work Sheet ชื่อ Order
ตัวอย่างข้อมูลดังแสดงในตารางที่ 2
ตารางที่ 2 ตาราง Order
ทีนี้มาดูความต้องการแฟนผมที่ขอเพิ่มเติม
เธอบอกว่า ให้ excel คำนวณจำนวนกล่องที่จะต้องบรรจุสินค้าทุกชนิดที่ลูกค้าออเดอร์มายกเว้นสินค้าที่มีหน่วยการสั่งซื้อเป็น ถง(คงเป็นถุงแหล่ะผมว่า) และ ขด ไม่ต้องคำนวณให้เอาจำนวนในหลักของออเดอร์มาวางได้เลย เอาหล่ะครับมาดูสูตร excel กันโดยจำนวนกล่องจะถูกคำนวณและบันทึกลงในหลัก E เอาหล่ะคับมาดูกันเลย
ขั้นแรกเช็คหน่วยในหลัก D กันก่อนโดยใช้เงื่อนไข สูตร excel : OR ครับ สมมุติผมเริ่มเขียนในแถวที่สองหล่ะกัน เขียน สูตร excel ได้ดังนี้ OR(D2="ขด" , D2="ถง") เอาหล่ะคับ สูตร excel : OR จะคืนค่าจริง หากหน่วยใน D2 เป็น ขด หรือ ถง อย่างใดอย่างหนึ่ง และจะคืนค่าเท็จหากเป็นหน่วยอื่นๆ
จากนั้นเราจะเอาค่าที่คืนจาก สูตร excel : OR ไปเป็นเงื่อนไขใน สูตร excel : IF ครับโดยสามารถเขียนได้ดังนี้
IF(OR(D2="ขด" , D2="ถง") ,หากจริง, หากเท็จ)
มาดูต่อครับ หากเงื่อนไข สูตร excel : OR เป็นจริง แฟนผมบอกให้เอาค่าในหลักออเดอร์มาวางได้เลย ดังนั้นจะเขียน สูตร excel : ได้ต่อดังนี้
IF(OR(D2="ขด" , D2="ถง") ,C2, หากเท็จ)
ทีนี้หากเงื่อนไข สูตร excel : OR เป็นเท็จ เราก็ต้องหารจำนวนออเดอร์ของสินค้านั้นด้วยปริมาณบรรจุต่อถุงของสินค้าชนิดนั้น สามารถเขียน สูตร excel : ได้ต่อดังนี้
IF(OR(D2="ขด" , D2="ถง") ,C2, C2/ปริมาณบรรจุต่อถุงของสินค้าชนิดนั้น)
ปริมาณบรรจุต่อถุงของสินค้าชนิดนั้น เราสามารถหาได้จากข้อมูลในตาราง Master Product โดยค้นหาจากรหัสสินค้า ด้วยฟังก์ชัน VLOOKUP ซึ่งผมได้อธิบายไปแล้วในบทความที่ผ่านมา ขอยกมาใช้เลยครับ
ปริมาณบรรจุต่อถุงของสินค้าชนิดนั้น = VLOOKUP(A2,'Master Product'!$A$2:$C$4,3,0)
นำไปแทนในสูตรโดยรวมจะได้สูตรในหลัก E เพื่อหาจำนวนกล่องที่ต้องบรรจุสินค้าแต่ละชนิดดังนี้
IF(OR(D2="ขด" , D2="ถง") ,C2, C2/VLOOKUP(A2,'Master Product'!$A$2:$C$4,3,0))
เอาหล่ะครับ คำนวณได้เลย ผลการคำนวณแสดงได้ดังภาพที่ 1
ภาพที่ 1 ผลการคำนวณโดยใช้ excel
เป็นไงครับผลการคำนวณเป็นไปตามเงื่อนไขที่เราสั่งมั้ยครับ จะเห็นว่า excel ก็ทำงานตามคำสั่งนะครับ แล้วทำไม สองแถวแรกถึงแจ้งว่า #N/A ท่านที่ใช้ VLOOKUP อยู่บ้างแล้วคงแปลความหมายออกครับ มันบอกว่า ค้นหารหัสสินค้าที่ต้องการไม่พบครับ ตอนแรกผมงงมากบอกแฟนไปว่ารหัสสินค้าใน Master Product คงมีไม่ครบแน่ๆ มันถึงหาไม่เจอ แต่ก็ได้รับการยืนยันแบบจริงจังว่า มีครบทุกรหัส เอาแล้วไงครับ งานเข้า ทำไงดี
ก่อนแก้ปัญหาผมจึงสอบถามที่มาของข้อมูลใน Master Product เธอบอกว่านำเข้าจากโปรแกรมสำเร็จรูปตัวหนึ่ง เอาหล่ะครับเริ่มเห็นทางสว่าง จึงลองใช้ สูตร excel ที่จัดการด้าน Text มาช่วยตรวจสอบดูครับ อันดับแรก ผมลองเช็คความยาวของ รหัสสินค้า ด้วย สูตร excel : LEN(Text) ซึงจะคืนค่าความยาวของ Text ในเซลล์นั้นครับ ปรากฎว่าโชคดีมากครับ เพราะความยาวที่ สูตร excel : LEN คืนมาเท่ากับ 9 แต่หากนับตัวอักษรที่เห็นในรหัสสินค้ามันมี 7 หลัก นั่นไงครับเจอสาเหตุที่ VLOOKUP หารหัสสินค้าไม่เจอแล้ว สาเหตุคือ ข้อความของรหัสสินค้าที่นำเข้ามานอกจากมีตัวอักษร 7 ตัวแล้วยังมีช่องว่างแถมมาต่อท้ายด้วย 2 ช่อง ทำให้เมื่อนำรหัสที่ต้องการค้นหาจากตารางออเดอร์ซึ่งไม่มีช่องว่างเลย VLOOKUP เลยหาข้อมูลไม่เจอ วิธีแก้ของผมคือใช้ สูตร excel : LEFT เพื่อเอาเฉพาะข้อความที่เป็นตัวอักขระที่มองเห็นเท่านั้น โดยในที่นี้ผมทราบว่ารหัสสินค้าของแฟนผมมี 7 หลักเท่านั้น ดังนั้นผมจึงใช้ สูตร excel : ดังนี้
LEFT(text,7) เมื่อตัดช่องว่างออกไปเมื่อประมวลผลใหม่ก็ได้ภาพที่ 2 ครับ
ภาพที่ 2 ผลการคำนวณโดยใช้ excel หลังแก้ไข Text Format
จากปัญหาที่ผมได้นำเสนอจะสรุปได้ว่า
1. หากข้อมูลที่ต้องค้นหาด้วย สูตร excel : VLOOKUP เป็น Text Format เราต้องตรวจสอบข้อมูลให้แน่ใจว่าไม่มีอักขระที่มองไม่เห็นอยู่ใน Text
2. หาก Text Format ที่นำเข้ามาเป็นตัวเลขหมด ให้ใช้ สูตร excel : VALUE ใน excel แปลงเป็นตัวเลขก่อนซึ่งสามารถแก้ปัญหาในข้อที่ 1 ได้
ก็เป็นตัวอย่างประสบการณ์จริงของผมที่นำมาเสนอครับ หวังว่าคงเป็นประโยชน์กับท่านผู้อ่านครับ
ปล ข้อมูลที่นำเข้าจากโปรแกรมสำเร็จรูปอื่น จะพบปัญหาการดำเนินการกับช่องว่างภายในข้อความเนื่องจากช่องว่างดังกล่าวอาจจะไม่ใช่การเคาะ Space bar ลองเข้าไปอ่านดูในบทความ การใช้ excel แก้ปัญหาการค้นหาหรือลบช่องว่างภายในข้อความไม่ได้ดูนะครับ
1 ความคิดเห็น:
ขอบคุณมากค่ะ
เป็นประโยชน์อย่างมาก
แสดงความคิดเห็น