การใช้ VLOOKUP เพื่อแสดงข้อมูลจากฐานข้อมูล

มีหลายท่านที่ได้สอบถามเข้ามา และให้ช่วยอธิบายการใช้ฟังก์ชัน VLOOKUP เพื่อแสดงข้อมูลจากฐานข้อมูลให้ด้วย วันนี้พอจะมีเวลา ก็ขอเขียนเรื่องนี้ก็แล้วกันครับ

VLOOKUP เป็นอีกฟังก์ชันหนึ่งของ Excel ที่มีการนำไปใช้งานค่อนข้างมาก โดยเฉพาะการแสดงข้อมูลจากฐานข้อมูล ที่มีความสะดวกและรวดเร็ว เพียงแค่เราป้อนรหัส หรือโค๊ดที่ใช้ในการอ้างอิงถึงข้อมูลนั้นๆ เช่น แค่ป้อนรหัสพนักงาน ก็สามารถแสดงข้อมูลต่างๆ ของพนักงานคนนั้นๆ ออกมาได้ในทันที รวมถึงแสดงรูปภาพของพนักงานได้ด้วย (ซึ่งจะกล่าวถึงวิธีการแสดงรูปในโอกาสต่อไป)



โครงสร้างของฟังก์ชัน VLOOKUP

VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

lookup_value หรือค่าที่จะใช้ค้นหา โดยค่าดังกล่าวนี้ ต้องอยู่ในคอลัมน์แรกของตารางฐานข้อมูล เช่น รหัสพนักงาน รหัสสินค้า เป็นต้น

table_array หรือช่วงตารางฐานข้อมูล ที่มีข้อมูลอย่างน้อยสองคอลัมน์หรือมากกว่า ดังรูปที่ 1

col_index_num หรือหมายเลขระบุคอลัมน์ในตารางฐานข้อมูล ที่คุณต้องการจะนำข้อมูลในคอลัมน์นั้นออกมาแสดง

range_lookup เป็นค่าตรรกะที่ระบุว่า ต้องการให้ VLOOKUP ค้นหารหัสที่ตรงกันกับที่ระบุเท่านั้นหรือไม่ โดยที่

• ถ้ากำหนดเป็น TRUE หรือ 1 หรือไม่ใส่ค่าอะไรเลย ในกรณีที่ไม่พบรหัสที่ตรงกัน จะแสดงข้อมูลของรหัสที่ใกล้เคียงที่สุด ซึ่งน้อยกว่าค่า lookup_value (ทั้งนี้ค่าในคอลัมน์แรกของช่วงตารางฐานข้อมูล จะต้องเรียงตามลำดับจากน้อยไปหามาก)
• แต่ถ้ากำหนดเป็น FALSE หรือ 0 (ศูนย์) VLOOKUP จะทำการค้นหาเฉพาะรหัสที่ระบุเท่านั้น ซึ่งถ้าไม่พบก็จะแสดงค่าผิดพลาด#N/A (ในกรณีนี้ ค่าในคอลัมน์แรกของช่วงตารางฐานข้อมูลไม่จำเป็นต้องเรียงลำดับ) แต่ถ้าหากพบรหัสที่ระบุที่มีค่าเหมือนกัน มากกว่าหนึ่งรายการในคอลัมน์แรกของช่วงตารางฐานข้อมูล VLOOKUP จะใช้ค่าแรกที่พบก่อน

รูปที่ 1 ตัวอย่างตารางฐานข้อมูล



สร้างตารางฐานข้อมูล

ในการใช้ VLOOKUP เราจำเป็นต้องมีตารางฐานข้อมูล อย่างน้อย 1 ตาราง (อาจจะมีมากกว่า 1 ตารางที่สัมพันธ์กันก็ได้ ซึ่งจะกล่าวถึงต่อไป) ซึ่งมีลักษณะดังรูปที่ 1 คุณต้องการเก็บข้อมูลอะไร จะมีกี่คอลัมน์ ก็สุดแล้วแต่ มีข้อแม้เพียงว่า ต้องอยู่ในรายละเอียดและเงื่อนไขของ การออกแบบตารางฐานข้อมูลใน Excel (หาอ่านได้จากหนังสือ Excel for HR ในบทที่ 21 ครับ)

จากรูปที่ 1 ผมจงใจให้ข้อมูลเริ่มต้นที่ คอลัมน์ B โดยตารางฐานข้อมูลนี้มี 5 คอลัมน์ คือ รหัส, คำนำหน้า, ชื่อ, นามสกุล และหน่วยงาน ดังนั้น ถ้าเราค้นหาเจอรหัสของใคร และต้องการนำชื่อคนนั้นมาแสดง ก็หมายถึง คอลัมน์ที่ 3 (แม้ว่าข้อมูลจะอยู่ในคอลัมน์ D ก็ตาม)



สร้างรูปแบบข้อมูลผลลัพธ์ (Output)

เราสามารถออกแบบหน้าตาของผลลัพธ์ หรือ Output ที่ต้องการได้ โดยการจัดวางองค์ประกอบ หรือโครงสร้างของผลลัพธ์นั้นตามความเหมาะสม โดยพิจารณาจากข้อมูลในตารางฐานข้อมูล

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



รูปที่ 2 ตัวอย่างข้อมูลผลลัพธ์ หรือ Output

1. จากรูปที่ 2 ผมทำการออกแบบข้อมูลผลลัพธ์แบบง่ายๆ โดยการใช้เมาท์เลือกช่วงข้อมูล B2:F2 แล้วคลิกขวาเลือก Copy
2. ผมคลิกเมาท์ปุ่มขวาที่ตำแหน่ง J4 แล้วเลือก Paste special...
3. ติ๊กเลือก Transpose แล้วคลิก OK
4. ตกแต่งรูปแบบสักหน่อย เช่น จัดข้อความชิดขวา และเพิ่มข้อความด้านบน ดังที่แสดงในรูปที่ 2

รูปที่ 3 ติ๊กเลือก Transpose แล้วคลิก OK



การแสดงข้อมูลจากฐานข้อมูล

จากรูปแบบผลลัพธ์ในรูปที่ 2 ถ้าเราป้อนรหัสพนักงานที่ K4 เราก็ต้องการให้คำนำหน้า, ชื่อ, นามสกุล และแผนก มาโชว์ใน K5 ถึง K8 ตามลำดับ โดยอัตโนมัติ เรามาทดลองทำตามไปพร้อมๆ กันเลยดีกว่าครับ

1. ป้อนรหัสพนักงานคนใดคนหนึ่งที่เซล K4
2. ที่เซล K5 พิมพ์ =VLOOKUP(K$4, $B$3:$F$10, 2, 0)
3. ทำการคัดลอกสูตรจาก K5 มาที่ K6:K8 (ซึ่งจะเห็นว่าแสดงผลเป็นคำนำหน้าเหมือนกันหมด)
4. ดับเบิลคลิกที่ K6 (หรือเลื่อนเซลมาที่ K6 แล้วกด F2) เปลี่ยนเลข 2 เป็นเลข 3
5. ดับเบิลคลิกที่ K7 เปลี่ยนเลข 2 เป็นเลข 4
6. ดับเบิลคลิกที่ K8 เปลี่ยนเลข 2 เป็นเลข 5 จะได้ผลลัพธ์ดังรูปที่ 4

รูปที่ 4 ตัวอย่างผลลัพธ์ที่ได้หลังจากใช้ VLOOKUP



การเชื่อมโยงข้อมูลจากฐานข้อมูลมากกว่า 1 ตาราง

จากตัวอย่างที่กล่าวมา เป็นการแสดงข้อมูลที่มาจากฐานข้อมูลเพียงตารางเดียว ดังนั้นจากผลลัพธ์ที่ได้ หากเราสงสัยว่า เราจะรู้ได้อย่างไรว่า รหัสหน่วยงานที่แสดงออกมานั้น จริงๆ แล้วมีชื่อเต็มว่าอะไร เราจึงอยากที่จะให้แสดงชื่อเต็ม เพราะเข้าใจง่ายกว่า

ในที่นี้เราจะทำการสร้างฐานข้อมูลขึ้นมาอีก 1 ตาราง เป็นรายชื่อของหน่วยงานต่างๆ โดยมีรหัสหน่วยงานเป็นตัวเชื่อมโยง ดังนั้น ผมจึงเพิ่มข้อมูลอีกตารางหนึ่ง ดังรูปที่ 5




รูปที่ 5 ตัวอย่างตารางข้อมูลหน่วยงาน


หลังจากที่เรามีตารางฐานข้อมูลดังรูปที่ 5 แล้ว เราจะต้องทำการแก้ไขสูตรในเซล K8 เสียใหม่ เพื่อให้แสดงชื่อเต็มของหน่วยงาน ดังนี้

=VLOOKUP(VLOOKUP(K$4,$B$3:$F$10,5,0),B16:C21,2,0)

เป็นการทำ VLOOKUP ซ้อน VLOOKUP โดยการนำผลลัพธ์ที่ได้จาก VLOOKUP ครั้งแรก มาเป็น lookup_value ของการ VLOOKUP ครั้งที่สอง ที่ไปค้นหาจากตารางฐานข้อมูลอันใหม่ ทำให้ได้ผลลัพธ์เป็นชื่อหน่วยงาน ดังรูปที่ 6



รูปที่ 6 ตัวอย่างผลลัพธ์ที่แสดงชื่อเต็มหน่วยงาน



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


1. พิมพ์สูตรครั้งเดียว สามารถคัดลอกไปใช้ได้ทุกเซล

จากตัวอย่างในครั้งที่แล้ว เมื่อเราพิมพ์สูตรที่ K5 ว่า =VLOOKUP(K$4,$B$3:$F$10,2,0) และทำการคัดลอกสูตรลงมายัง K6 ถึง K8 นั้น จะเห็นว่า เราต้องมาแก้ไขสูตรในแต่ละบรรทัดใหม่ โดยเปลี่ยนเลข 2 เป็น 3, 4 และ 5 ตามลำดับ ถ้าหากจำนวนเซลมีมากกว่านี้ล่ะ ก็ต้องแก้ไขสูตรอีกหลายครั้ง


รูปที่ 7 ตัวอย่างผลลัพธ์ที่เรียงต่อเนื่องกันลงมา


ดังนั้น ถ้าข้อมูลเป็นลักษณะเรียงต่อเนื่องกันลงมา ขอย้ำนะครับว่า "เรียงต่อเนื่องกันลงมา" ดังในรูปที่ 7 เราสามารถแก้ไขสูตรที่ K5 เสียใหม่ เพื่อให้สามารถคัดลอกไป K6 ถึง K8 ได้ โดยไม่ต้องไปแก้ไขสูตรอีก ดังนี้

1.1) เปลี่ยนสูตรที่ K5 เป็น =VLOOKUP(K$4,$B$3:$F$10,ROW()-3,0) เสร็จแล้วลองคัดลอกสูตรลงมายัง K6 ถึง K8 จะเห็นว่า ได้ผลลัพธ์ออกมาอัตโนมัติ โดยไม่ต้องมาแก้ไขสูตรอีก (ยกเว้น K8 เพราะเราใช้ VLOOKUP ซ้อน VLOOKUP ซึ่งสูตรไม่เหมือนกับเซลอื่น)

ทั้งนี้เพราะ ฟังก์ชัน ROW() จะส่งค่าเลขบรรทัดนั้นๆ ออกมา ซึ่งถ้าเรากำลังทำงานในบรรทัดที่ 5 ผลลัพธ์ที่ได้จากฟังก์ชัน ROW() ก็คือ 5 แต่ในเซล K5 นั้น เราต้องการเลข 2 ไปใส่ในสูตร ผมจึงใช้ ROW()-3

ดังนั้น เมื่อสูตรถูกคัดลอกไปยังบรรทัดถัดลงมา เลขที่แสดงบรรทัดที่ได้จาก ROW() ก็จะเปลี่ยนไปอัตโนมัติ พอนำ ROW()-3 ผลลัพธ์จึงเปลี่ยนเป็น 3, 4 และ 5 ตามลำดับ โดยอัตโนมัติ

หมายเหตุ : วิธีการนี้ใช้ได้เฉพาะกรณีที่ ต้องการแสดงข้อมูลเรียงต่อเนื่องกันลงมาเท่านั้น


1.2) เนื่องจากหัวข้อใน J5:J8 เป็นข้อความที่เราคัดลอกมาจาก B2:F2 ข้อความจึงเหมือนกันทุกประการ เราจึงสามารถใช้วิธีเปรียบเทียบ ว่าหัวข้อในคอลัมน์ J นี้ ไปตรงกับลำดับที่เท่าไรในช่วง B2:F2 โดยใช้ฟังก์ชัน MATCH ดังนั้นที่ K5 จึงเขียนสูตรใหม่ได้ ดังนี้
=VLOOKUP($K$4,$B$3:$F$10,MATCH(J5,$B$2:$F$2,0),0) 
เสร็จแล้วลองคัดลอกสูตรลงมายัง K6 ถึง K8 จะเห็นว่า ได้ผลลัพธ์ออกมาอัตโนมัติเช่นกัน


รูปที่ 8 ตัวอย่างการวางข้อมูลผลลัพธ์รูปแบบอื่น


วิธีการนี้มีข้อดีกว่าการใช้ ROW() ตรงที่ แม้ข้อมูลจะไม่เรียงต่อเนื่องกันลงมา จะเว้นบรรทัด หรือจะวางข้อมูลในแนวอื่น ดังในรูปที่ 8 สูตรนี้ก็ยังใช้งานได้ แต่มีข้อแม้ว่า ชื่อหัวข้อของผลลัพธ์ จะต้องเหมือนกันกับหัวตารางฐานข้อมูล


รูปที่ 9 ชื่อหัวข้อของผลลัพธ์จะต้องเหมือนกับหัวตารางฐานข้อมูล


2. การนำผลลัพธ์ที่ได้จาก VLOOKUP มาเชื่อมต่อกัน

ในบางกรณี ที่เราไม่ต้องการแสดงข้อมูล ในลักษณะที่เป็นหัวข้อใครหัวข้อมัน แต่ต้องการให้ข้อมูลเชื่อมต่อกันไปเลย เช่น คำนำหน้า+ชื่อ+เว้นวรรคสัก 2 เคาะ+นามสกุล อย่างนี้เป็นต้น ก็สามารถเขียนสูตรได้ดังนี้
=VLOOKUP(K$4,$B$3:$F$10,2,0)&VLOOKUP(K$4,$B$3:$F$10,3,0) &" "&VLOOKUP(K$4,$B$3:$F$10,4,0)


รูปที่ 10 ตัวอย่างการนำผลลัพธ์ที่ได้จาก VLOOKUP มาเชื่อมต่อกัน



3. ถ้ายังไม่ใส่รหัส ก็ให้แสดงแบบฟอร์มเปล่าๆ

จากทุกสูตรที่แนะนำมาแล้วในเบื้องต้น หากคุณลองลบรหัสที่ป้อนใน K4 ออกไป จะเห็นว่าในช่องต่างๆ ที่เราเรียกใช้สูตร VLOOKUP จะแสดงค่าผิดพลาด #N/A ออกมาทุกเซล ทำให้ดูเกะกะสายตา และทำให้แบบฟอร์มดังกล่าวไม่สวยงาม


รูปที่ 11 ตัวอย่างค่าผิดพลาดในกรณีที่ยังไม่ใส่รหัสที่ K4


หากไม่ต้องการให้แสดงค่าผิดพลาดดังกล่าว ให้เอาฟังก์ชัน IF เข้ามาตรวจสอบก่อนว่า มีการป้อนรหัสที่ K4 หรือไม่ ถ้ายังไม่ป้อน ก็ไม่ต้องแสดงค่า แต่ถ้าป้อนแล้ว จึงค่อยแสดงค่าตาม VLOOKUP โดยเปลี่ยนสูตรในแต่ละเซล ดังนี้
=IF(ISBLANK($K$4),"",ตามด้วยสูตรเดิม) หรือ
=IF($K$4="","",ตามด้วยสูตรเดิม)

--------------------


ที่แนะนำมาทั้งหมดนี้ ก็เป็นเกร็ดเล็กๆ น้อยๆ เกี่ยวกับการประยุกต์ใช้ VLOOKUP เพื่อแสดงข้อมูลจากตารางฐานข้อมูล หวังว่าคงจะเป็นประโยชน์กับผู้สนใจ ไม่มากก็น้อยนะครับ