จะดีแค่ไหนถ้าเซลล์ถูกเติมโดยอัตโนมัติ? ส่วนใหญ่เราจะรักสิ่งนั้น วันนี้เราจะแสดงวิธีการเติมเซลล์อัตโนมัติใน excel ตามค่าจากเซลล์อื่น สำหรับเซสชันนี้ เราจะใช้ Excel 2019 โปรดใช้เวอร์ชันที่คุณต้องการได้ตามใจชอบ
อย่างแรกเลย เรามาทำความรู้จักกับชุดข้อมูลที่เป็นฐานของตัวอย่างในวันนี้กันก่อน
ที่นี่เรามีตารางที่มีข้อมูลของพนักงาน เช่น ชื่อ รหัส ที่อยู่ แผนกที่เกี่ยวข้อง และวันที่เข้าร่วม เมื่อใช้ข้อมูลนี้ เราจะเห็นวิธีการเติมเซลล์โดยอัตโนมัติ
โปรดทราบว่านี่เป็นชุดข้อมูลพื้นฐานที่มีข้อมูลจำลอง ในสถานการณ์จริง คุณอาจพบชุดข้อมูลขนาดใหญ่และซับซ้อนกว่ามาก
สมุดแบบฝึกหัด
คุณสามารถดาวน์โหลดคู่มือการฝึกปฏิบัติได้จากลิงค์ด้านล่าง
เติมเซลล์อัตโนมัติตามเซลล์อื่น
ในที่นี้ เราได้วางตัวอย่างในลักษณะที่การระบุชื่อพนักงาน เราจะค้นหาข้อมูลของเขาโดยอัตโนมัติ
ที่นี่เราได้แนะนำเขตข้อมูลที่แยกจากตารางเดิม สมมติว่าเราตั้งค่า ชื่อ โรเบิร์ต .
เราก็จะได้รายละเอียดของ Robert . มาดูกันว่าเราจะทำอย่างนั้นได้อย่างไร
1. การใช้ฟังก์ชัน VLOOKUP
ลืมไปสักครู่เกี่ยวกับ "การเติมข้อมูลอัตโนมัติ" และคิดถึงการดึงข้อมูลที่ตรงกับเกณฑ์ ฟังก์ชันใดที่เข้ามาในหัวคุณ ค่อนข้างชัดเจน VLOOKUP เป็นหนึ่งในนั้น
VLOOKUP ค้นหาข้อมูลโดยจัดเรียงตามแนวตั้ง สำหรับข้อมูลเพิ่มเติม โปรดดูบทความ VLOOKUP นี้
ตอนนี้เราจะเขียนสูตรโดยใช้ VLOOKUP ฟังก์ชันที่จะดึงข้อมูลที่แน่นอนที่เราต้องการในเซลล์
มาเขียนสูตรหา id ของพนักงานกัน
=IFERROR(VLOOKUP($I$4,$B$4:$F$9,2,0),"")
ภายใน VLOOKUP ฟังก์ชัน เราได้ใส่ชื่อ (I4) เป็น lookup_value . จากนั้นช่วงตารางทั้งหมดเป็น lookup_array .
รหัสพนักงาน เป็นคอลัมน์ที่ 2 ดังนั้นเราจึงกำหนดให้ 2 เป็น column_num .
เราใช้ IFERROR ฟังก์ชันเพื่อสรุป VLOOKUP สูตร. การดำเนินการนี้จะขจัดข้อผิดพลาดที่เกิดขึ้นจากสูตร (หากต้องการทราบเกี่ยวกับฟังก์ชัน โปรดไปที่บทความ:IFERROR)
เพื่อให้ได้ชื่อแผนก เราต้องแก้ไขสูตร
=IFERROR(VLOOKUP($I$4,$B$4:$F$9,3,0),"")
ที่นี่เราได้เปลี่ยน column_num ตามตำแหน่งในตารางเดิม กรม เป็นคอลัมน์ที่ 3 เราจึงใช้ 3
สำหรับ วันที่เข้าร่วม และ ที่อยู่ สูตรจะเป็น
=IFERROR(VLOOKUP($I$4,$B$4:$F$9,4,0),"")
และ
=IFERROR(VLOOKUP($I$4,$B$4:$F$9,5,0),””)
เราพบรายละเอียดของพนักงานแล้ว ตอนนี้เปลี่ยนชื่อและเซลล์จะได้รับการอัปเดตอัตโนมัติ
VLOOKUP พร้อมรายการแบบเลื่อนลง
ก่อนหน้านี้เราได้ระบุชื่อด้วยตนเอง บางครั้งอาจดูใช้เวลานานและสับสน
เพื่อแก้ปัญหานี้ เราสามารถจัดทำรายการแบบหล่นลงสำหรับชื่อพนักงาน ตรวจสอบบทความเพื่อทราบเกี่ยวกับการทำรายการแบบเลื่อนลง
ใน การตรวจสอบข้อมูล กล่องโต้ตอบ เลือก รายการ และแทรกการอ้างอิงเซลล์ของชื่อ
B4:B9 คือช่วงที่มีชื่อ
ตอนนี้เราจะพบรายการแบบเลื่อนลง
เราสามารถเลือกชื่อได้อย่างมีประสิทธิภาพและรวดเร็วยิ่งขึ้นในขณะนี้
เซลล์อื่นๆ จะถูกเติมโดยอัตโนมัติในขณะที่เราใช้ VLOOKUP .
2. การใช้ฟังก์ชัน INDEX – MATCH
การดำเนินการที่เราดำเนินการผ่าน VLOOKUP สามารถทำได้อีกทางหนึ่ง เราสามารถใช้ INDEX-MATCH . ร่วมกันได้ เพื่อเติมเซลล์โดยอัตโนมัติ
MATCH ระบุตำแหน่งของค่าการค้นหาในแถว คอลัมน์ หรือตาราง ดัชนี ส่งคืนค่าที่ตำแหน่งที่กำหนดในช่วง หากต้องการทราบข้อมูลเพิ่มเติม โปรดไปที่บทความ:INDEX, MATCH
สูตรจะเป็นดังนี้
=IFERROR(INDEX($C$4:$C$9,MATCH($I$4,$B$4:$B$9,0)),"")
ที่นี่สูตรของเราได้หมายเลขรหัสเนื่องจากเราได้ระบุช่วงรหัสภายใน ดัชนี และ MATCH ฟังก์ชันระบุหมายเลขแถวที่ตรงกับค่าเกณฑ์ในตาราง (B4:B9 )
เพื่อให้ได้มาซึ่ง กรม เราจะเปลี่ยนช่วงใน INDEX และสูตรจะเป็นดังนี้
=IFERROR(INDEX($D$4:$D$9,MATCH($I$4,$B$4:$B$9,0)),"")
หน่วยงานอยู่ในช่วง D4 ถึง D9 .
สูตรสำหรับ วันที่เข้าร่วม จะ
=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
ชื่อ ฟิลด์จะถูกตั้งค่าจากรายการดรอปดาวน์ และส่วนที่เหลือของฟิลด์จะถูกเติมโดยอัตโนมัติ
ในการรับ id เราจะใช้สูตรต่อไปนี้
=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),"")
กรม เป็นแถวที่ 3 ดังนั้น row_num คือ 3 ที่นี่
มาเขียนสูตรวันเข้าร่วมกันเถอะ
=IFERROR(HLOOKUP($C$11,$C$3:$H$7,4,0),"")
วันที่เข้าร่วม เป็นแถวที่ 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 ใน ดัชนี ฟังก์ชัน ซึ่งก็คือ รหัสพนักงาน แถว.
เปลี่ยนช่วงแถวเพื่อค้นหาแผนก
=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 คือ วันที่เข้าร่วม แถว.
และ C7:H7 คือ ที่อยู่ แถว ดังนั้นสูตรการหาที่อยู่จะเหมือนกับที่ระบุไว้ด้านล่าง
=IFERROR(INDEX($C$7:$H$7,MATCH($C$11,$C$3:$H$3,0)),””)
บทสรุป
นั่นคือทั้งหมดสำหรับวันนี้ เราได้ระบุวิธีการเติมเซลล์อัตโนมัติตามเซลล์อื่นหลายวิธี หวังว่าคุณจะพบว่าสิ่งนี้มีประโยชน์ รู้สึกอิสระที่จะแสดงความคิดเห็นหากมีอะไรที่ดูเหมือนเข้าใจยาก แจ้งให้เราทราบวิธีการอื่นๆ ที่เราพลาดไปที่นี่
อ่านเพิ่มเติม
- วิธีใช้สูตรป้อนอัตโนมัติใน Excel (6 วิธี)
- ป้อนเซลล์อัตโนมัติโดยยึดตามเซลล์อื่นใน Excel (5 วิธี)
- การใส่หมายเลขอัตโนมัติใน Excel (9 วิธี)
- วิธีการป้อนตัวเลขอัตโนมัติใน Excel (12 วิธี)
- แก้ไข:การป้อนอัตโนมัติของ Excel ไม่ทำงาน (7 ปัญหา)
- วิธีการป้อนวันที่ตามลำดับในหลายชีตใน Excel
