Excel tips : การกำหนดชื่อข้อมูลแบบคงที่ใน excel (Static data)

วันนี้ขอนำเสนอบทความ tips การใช้ excel เพื่อเป็นพื้นฐานในการใช้ excel หรือใช้สูตร excel ในงานอื่นๆต่อไปครับ การกำหนดชื่อข้อมูลใน excel มีประโยชน์อย่างมาก ในกรณีที่เราต้องเรียกใช้งานข้อมูลนั้นบ่อยๆ การพิมพ์ช่วงของข้อมูลหรือตำแหน่งเซลล์ของข้อมูลมีโอกาสเกิดข้อผิดพลาดได้เช่น สูตร excel สำหรับการคำนวณภาษีมูลค่าเพิ่ม หรือ การกำหนดตารางข้อมูลเพื่อใช้ในสูตร excel : vlookup เป็นต้น ผมจะแบ่งชื่อข้อมูลแบบคงที่ใน excel ออกเป็น 3 รูปแบบดังนี้ครับ

1. ข้อมูลประเภท ค่าคงที่ เช่น ภาษีมูลค่าเพิ่ม อัตราแลกเปลี่ยน
2. ข้อมูลประเภทเป็นช่วงข้อมูลแบบรายการ (อาจจะเป็นแบบแถวเดียวหรือหลักเดียว) ผมจะเรียกว่าข้อมูลแบบเวกเตอร์ เราจะพบการใช้งานแสดง List รายการต่างๆให้ผู้ใช้เลือก
3. ข้อมูลประเภทตาราง จะเรียกว่าข้อมูลแบบ Matrix เราจะพบในการใช้งานสูตร excel : vlookup เป็นส่วนใหญ่

ข้อดีของการกำหนดชื่อข้อมูล

1. ลดความผิดพลาดในการใช้สูตร excel
2. ง่ายต่อการเรียกใช้ข้อมูลเนื่องจากชื่อข้อมูลที่กำหนดมีความหมายในตัวเองเช่น vat , customer_name , Table เป็นต้น
3. ง่ายต่อการพิมพ์สูตร excel

ข้อเสีย

1. ใช้ได้อย่างถูกต้องกับข้อมูลที่ไม่มีการเปลี่ยนแปลงขณะใช้งาน

วิธีการกำหนดชื่อใน excel 2007 ขึ้นไป

1. เลือกเมนู สูตร
2. เลือกทูลบาร์ กำหนดชื่อ จะปรากฎหน้าต่าง ชื่อใหม่ ขึ้นมาดังรูปที่ 1
3. กำหนดชื่อข้อมูลในช่อง ชื่อ
4. เลือกขอบเขตการใช้ข้อมูล ในหัวข้อนี้เราสามารถกำหนดให้ข้อมูลที่เรากำหนดสามารถเรียกใช้ได้ตามขอบเขตที่กำหนด อาจจะทั้งหมด (สมุดงาน) หรือว่า เจาะจงใช้ได้เฉพาะ WorkSheet
5. กำหนดตำแหน่งของ cells หรือช่วงของ Cells ที่ต้องการกำหนดชื่อ ในช่องอ้างอิงไปยัง
1. ตัวอย่างเช่น =Sheet1!$F$12 จะเป็นข้อมูลแบบ ค่าคงที่
2. ตัวอย่างเช่น =Sheet1!$F$12:$F$15 จะเป็นข้อมูลแบบเวกเตอร์
3. ตัวอย่างเช่น =Sheet1!$F$12:$G$15 จะเป็นข้อมูลแบบ Matrix
6. คลิกปุ่มตกลง

ภาพที่ 1 ตัวอย่างการกำหนดชื่อข้อมูลใน excel

ตัวอย่างการใช้งาน excel
ข้อมูลแบบค่าคงที่
พิมพ์ค่า 7% ใน Cell A1 กำหนดชื่อข้อมูลแบบค่าคงที่ ชื่อ vat โดยอ้างถึง Cell A1 หากต้องการคำนวณภาษีมูลค่าเพิ่มจากราคาสินค้าใน cells B3จะเขียนสูตร excel ใน Cell C3 ได้ดังนี้
สูตร excel :  C3= vat*B3
ข้อมูลแบบเวกเตอร์
พิมพ์ข้อมูลชื่อลูกค้าลงใน Cells A1 – A5
กำหนดชื่อข้อมูลแบบเวกเตอร์ ชื่อ Customer โดยอ้างถึง $A$1:$S$5
สามารถนำมาใช้เป็น List data ในการตรวจสอบความถูกต้องของข้อมูล โดยกำหนดแหล่งข้อมูล = Customer
ข้อมูลแบบ Matrix
พิมพ์ข้อมูลลงใน Cells A1 – C5
กำหนดชื่อข้อมูลแบบ Matrix ชื่อ data โดยอ้างถึง $A$1:$C$5
สามารถนำมาใช้ในสูตร excel : vlookup เช่น
=vlookup(B4,data,2,false) เป็นต้น

ก่อนจบบทความนี้อยากให้ท่านผู้อ่านคิดต่อนะครับว่า หากต้องการเพิ่มข้อมูลในแบบเวกเตอร์หรือแบบ Matrix เช่น มีลูกค้าเพิ่มขึ้นมาอีก 2 ราย เราจะทำอย่างไร หากเป็นการกำหนดข้อมูลแบบเดิมก็ต้องกลับไปแก้ไขข้อมูลในช่องอ้างอิงไปยังเพื่อให้ครอบคุลมช่วงของข้อมูลที่เพิ่มขึ้นมา  ซึ่งการกระทำดังกล่าวจะทำให้ไม่สะดวกในการใช้งาน ซึ่งเป็นจุดด้อยของการกำหนดชื่อข้อมูลแบบคงที่ ดังนั้นในบทความต่อไปผมจะนำเสนอวิธีการกำหนดชื่อข้อมูลแบบพลวัตร (Dynamic Data) เพื่อให้ข้อมูลมีการอัพเดตและครอบคลุมข้อมูลที่เพิ่มขึ้นมาได้อย่างอัตโนมัติ สวัสดีครับ

VLOOKUP in Excel กับการค้นหาข้อมูลชนิด Text Format

เมื่ออาทิตย์ที่ผ่านมาสุดที่รักให้การบ้านมาหนึ่งชิ้นเกี่ยวกับการใช้ excel ค้นหาข้อมูลสินค้าตามรหัสและเมื่อหาเจอแล้วให้นำข้อมูลของปริมาณของสินค้าต่อกล่องไปคำนวณหาจำนวนกล่องซึ่งใช้บรรจุสินค้าใน Work Sheet ถัดไป เป็นไงครับเห็นโจทย์แล้วหลายท่านบอกว่าการใช้ excel แบบหมูๆ Search ด้วย VLOOKUP in Excel ซิ ผมก็คิดเช่นนั้นครับ ลองมาดูปัญหากัน ผมขอยกตารางมาให้ทุกท่านได้เข้าใจตรงกันดีกว่าครับ

สมมุติว่าผมมีตารางข้อมูลใน 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 แก้ปัญหาการค้นหาหรือลบช่องว่างภายในข้อความไม่ได้ดูนะครับ