จะดีแค่ไหนถ้าเซลล์เติมข้อมูลอัตโนมัติ? ส่วนใหญ่เวลาเราจะชอบ นวันนี้เราจะแสดงวิธี auto-populate เซลล์ใน Excel ตามค่าจากอีกเซลล์ สำหรับเซสชันนี้ เราจะใช้ Excel 2019 โปรดใช้เวอร์ชันที่คุณชอบ
ก่อนอื่น มารู้จักชุดข้อมูลที่เป็นพื้นฐานของตัวอย่างวันนี้
ที่นี่เรามีตารางที่มีข้อมูลพนักงาน เช่น ชื่อ, ไอดี, ที่อยู่, แผนก, และวันที่เข้าทำงาน ใช้ข้อมูลนี้ เราจะดูวิธีเติมเซลล์อัตโนมัติ
โปรดสังเกตว่าเป็นชุดข้อมูลพื้นฐานพร้อมข้อมูลจำลอง ในสถานการณ์จริง อาจพบชุดข้อมูลใหญ่และซับซ้อนกว่านี้มาก
สมุดงานฝึกฝน
คุณสามารถดาวน์โหลดสมุดงานฝึกฝนจากลิงก์ด้านล่าง
Auto Populate เซลล์ ตามอีกเซลล์
ที่นี่ เราตั้งตัวอย่างไว้ว่า ด้วยการให้ชื่อพนักงาน เราจะหาข้อมูลของเขาอัตโนมัติ
ที่นี่เราแยกฟิลด์ข้อมูลออกจากตารางต้นฉบับ สมมติเราตั้ง Name, Robert
จากนั้นเราควรได้รายละเอียดของ Robert มาสำรวจว่าทำได้อย่างไร
1. ใช้ฟังก์ชัน VLOOKUP
เพื่อชั่วคราวลืม “auto-populate” และคิดถึงการดึงข้อมูลจับคู่เกณฑ์ ฟังก์ชันไหนมาในใจ? ค่อนข้างชัดเจน VLOOKUP เป็นหนึ่งในนั้น
VLOOKUP ค้นหาข้อมูลที่จัดเรียงแนวตั้ง สำหรับข้อมูลเพิ่มเติม อ่านบทความ VLOOKUP
ตอนนี้เราจะเขียนสูตรใช้ฟังก์ชัน VLOOKUP ที่จะดึงข้อมูลที่ต้องการในเซลล์
มาเขียนสูตรสำหรับดึงไอดีพนักงาน
=IFERROR(VLOOKUP($I$4,$B$4:$F$9,2,0),"") 
ภายในฟังก์ชัน VLOOKUP เราใส่ชื่อ (I4) เป็น lookup_value จากนั้นช่วงตารางทั้งหมดเป็น lookup_array
Employee ID เป็นคอลัมน์ที่ 2 ดังนั้นเราตั้ง 2 เป็น column_num
เราใช้ฟังก์ชัน IFERROR ครอบสูตร VLOOKUP นี้ จะกำจัดข้อผิดพลาดที่เกิดจากสูตร (เพื่อรู้จักฟังก์ชัน อ่านบทความ: IFERROR)
สำหรับดึงชื่อแผนก เราต้องปรับสูตร
=IFERROR(VLOOKUP($I$4,$B$4:$F$9,3,0),"")
ที่นี่เราเปลี่ยน column_num ตามตำแหน่งในตารางต้นฉบับ Department เป็นคอลัมน์ที่ 3 ดังนั้นเราใช้ 3
สำหรับ Joining Date และ Address สูตรจะเป็น
=IFERROR(VLOOKUP($I$4,$B$4:$F$9,4,0),"") 
และ
=IFERROR(VLOOKUP($I$4,$B$4:$F$9,5,0),””)
เราพบรายละเอียดพนักงานแล้ว ตอนนี้เปลี่ยนชื่อ และเซลล์จะอัปเดตอัตโนมัติ
VLOOKUP พร้อม Drop-Down List
ก่อนหน้านี้ เราให้ชื่อด้วยตนเอง บางครั้งอาจดูใช้เวลาและสับสน
เพื่อแก้ปัญหา เราสามารถสร้างรายการหล่นสำหรับชื่อพนักงาน อ่านบทความเพื่อรู้จักการสร้างรายการหล่น
ในกล่องโต้ตอบ Data Validation เลือก List และใส่การอ้างอิงเซลล์ของชื่อ
B4:B9 คือช่วงที่มีชื่อ
ตอนนี้เราจะพบรายการหล่น
เราสามารถเลือกชื่อได้มีประสิทธิภาพและรวดเร็วขึ้น
เซลล์อื่นๆ กำลังถูกเติมอัตโนมัติ เนื่องจากเราใช้ VLOOKUP
2. ใช้ฟังก์ชัน INDEX – MATCH
การดำเนินการที่เราทำผ่าน VLOOKUP สามารถทำได้อีกทางหนึ่ง เราสามารถใช้การรวม INDEX-MATCH เพื่อเติมเซลล์อัตโนมัติ
MATCH ค้นหาตำแหน่งของค่าค้นหาในแถว คอลัมน์ หรือตาราง INDEX คืนค่าที่ตำแหน่งที่กำหนดในช่วง เพื่อรู้เพิ่มเติม อ่านบทความ: INDEX, MATCH
สูตรจะเป็นดังนี้
=IFERROR(INDEX($C$4:$C$9,MATCH($I$4,$B$4:$B$9,0)),"")
ที่นี่ สูตรของเราดึงหมายเลขไอดี เพราะเราให้ช่วงไอดีใน INDEX และฟังก์ชัน MATCH ให้หมายเลขแถว จับคู่ค่าเกณฑ์ในตาราง (B4:B9)
เพื่อดึง Department เราจะเปลี่ยนช่วงใน INDEX และสูตรจะเป็นดังนี้
=IFERROR(INDEX($D$4:$D$9,MATCH($I$4,$B$4:$B$9,0)),"")
แผนกอยู่ในช่วง D4 ถึง D9
สูตรสำหรับ Joining Date จะเป็น
=IFERROR(INDEX($E$4:$E$9,MATCH($I$4,$B$4:$B$9,0)),"")
และสำหรับที่อยู่
=IFERROR(INDEX($F$4:$F$9,MATCH($I$4,$B$4:$B$9,0)),"")
ตอนนี้เพื่อยืนยัน ให้ล้างการเลือก และเลือกชื่อใดก็ได้
คุณจะพบเซลล์อื่นๆ ถูกเติมอัตโนมัติ
3. ใช้ฟังก์ชัน HLOOKUP
หากข้อมูลของคุณจัดเรียงแนวนอน คุณต้องใช้ฟังก์ชัน HLOOKUP เพื่อรู้จักฟังก์ชัน อ่านบทความ: HLOOKUP
The Name ฟิลด์จะตั้งจากรายการหล่น และฟิลด์อื่นๆ จะเติมอัตโนมัติ
สำหรับดึงไอดี เราจะใช้สูตรต่อไปนี้
=IFERROR(HLOOKUP($C$11,$C$3:$H$7,2,0),"")
การดำเนินการคล้ายกับสูตร VLOOKUP ภายในฟังก์ชัน HLOOKUP เราให้ชื่อเป็น lookup_value และตารางเป็น lookup_array ไอดีอยู่ที่แถวที่ 2 ดังนั้น row_num คือ 2 และ 0 สำหรับจับคู่ที่ตรง
ตอนนี้ สำหรับแผนก สูตรจะเป็น
=IFERROR(HLOOKUP($C$11,$C$3:$H$7,3,0),"") 
Department เป็นแถวที่ 3 ดังนั้น row_num คือ 3 ที่นี่
มาเขียนสูตรสำหรับวันที่เข้าทำงาน
=IFERROR(HLOOKUP($C$11,$C$3:$H$7,4,0),"")
Joining Date เป็นแถวที่ 4 ดังนั้น row_num คือ 4 ที่นี่ สำหรับที่อยู่ เปลี่ยนหมายเลขแถวเป็น 5
=IFERROR(HLOOKUP($C$11,$C$3:$H$7,5,0),"")
ล้างเซลล์ และเลือกชื่อจากรายการหล่น
หลังเลือกชื่อ คุณจะพบเซลล์อื่นๆ กำลังเติมอัตโนมัติ
4. INDEX-MATCH สำหรับแถว
เราสามารถใช้การรวม INDEX MATCH สำหรับแถวได้เช่นกัน สูตรจะเป็นดังนี้
=IFERROR(INDEX($C$4:$H$4,MATCH($C$11,$C$3:$H$3,0)),"") นี่สำหรับดึงไอดี ดังนั้นเราใช้ C4:H4 ในฟังก์ชัน INDEX ซึ่งเป็นแถว Employee ID
เปลี่ยนช่วงแถวเพื่อหาแผนก
=IFERROR(INDEX($C$5:$H$5,MATCH($C$11,$C$3:$H$3,0)),"")
เช่นเดียวกัน เปลี่ยนหมายเลขแถวสำหรับวันที่เข้าทำงานและที่อยู่
=IFERROR(INDEX($C$6:$H$6,MATCH($C$11,$C$3:$H$3,0)),"")ที่นี่ C6:H6 เป็นแถว Joining Date
และ C7:H7 เป็นแถว Address ดังนั้นสูตรสำหรับดึงที่อยู่จะเป็นเหมือนด้านล่าง
=IFERROR(INDEX($C$7:$H$7,MATCH($C$11,$C$3:$H$3,0)),””)
สรุป
จบบทความวันนี้ เราแสดงหลายวิธี auto-populate เซลล์ตามอีกเซลล์ หวังว่าจะเป็นประโยชน์ โปรดคอมเมนต์หากมีอะไรยากที่จะเข้าใจ แจ้งให้เรารู้วิธีอื่นที่เราพลาดไป
การอ่านเพิ่มเติม
- วิธีใช้ Autofill Formula ใน Excel (6 วิธี)
- AutoFill Cell Based on Another Cell in Excel (5 Methods)
- Automatic Numbering in Excel (9 Approaches)
- วิธี Autofill ตัวเลขใน Excel (12 วิธี)
- Fix: Excel Autofill Not Working (7 Issues)
- วิธีป้อนวันที่ลำดับข้ามหลายชีตใน Excel
