ใน MS Excel บางครั้งเราอาจจำเป็นต้องกำหนดหรือค้นหาค่าตามเกณฑ์หรือเงื่อนไขบางอย่างจากรายการ สมมติว่าเรามีแผนงานที่มีชื่อบุคคลที่เกี่ยวข้องกันซึ่งดูแลงานแต่ละงาน และตอนนี้ เราต้องแสดงรายการชื่องานทั้งหมดเป็นแถวตามบุคคลที่กำหนด ด้วยวิธีนี้ Excel มีวิธีต่างๆ ในการเติมรายการตามค่าของเซลล์ ในบทความนี้ เราจะมาดูกันว่าเราสามารถเติมรายการตามค่าของเซลล์ใน Excel ได้อย่างไร
ดาวน์โหลดคู่มือการฝึกปฏิบัติ
6 วิธีในการเติมรายการตามค่าของเซลล์ใน Excel
1. ป้อนรายการอัตโนมัติตามค่าของเซลล์
มาทำรายการโครงการพร้อมชื่อผู้ปฏิบัติงานโครงการกันเถอะ ในแต่ละโครงการ ชื่อของผู้ปฏิบัติงานจะถูกกำหนดในรูปแบบ “Project_Number_Name_Serial ” ดังนั้น งานของเราคือค้นหาชื่อพนักงานทั้งหมดโดยใช้โครงการนี้
ขั้นตอนที่ 1: ป้อนสูตรต่อไปนี้ในเซลล์ D17 แล้วกด Enter
=IFERROR(INDEX($B$3:$D$11,ROW(B2:D11),MATCH($C$16,$B$3:$D$3,0)),"") คำอธิบายสูตร
- ที่นี่ MATCH($C$16,$B$3:$D$3,0) ส่วนนี้ตรงกับชื่อโปรเจ็กต์ที่ป้อนกับชุดข้อมูล และถือว่าตรงกันทุกประการเท่านั้น
- ROW(B2:D11) กำลังนับจำนวนแถวของชุดข้อมูล
- INDEX($B$3:$D$11, ROW(B2:D11), MATCH($C$16,$B$3:$D$3,0)) ส่วนนี้ของสูตรคือการค้นหาชื่อผู้ปฏิบัติงานโครงการที่ตรงกัน หากไม่พบข้อมูลในชุดข้อมูลที่กำหนด ก็จะผ่านข้อผิดพลาด #NA
- สุดท้าย IFERROR คือการจัดการกับข้อผิดพลาดใดๆ
ขั้นตอนที่ 2 :จากรายการดรอปดาวน์ ให้เลือกชื่อโครงการ
ขั้นตอนที่ 3: จะแสดงชื่อคนงานทั้งหมด
2. เติมแถวตามค่าเซลล์ที่ระบุด้วยสูตร
เรามาดูกันว่าเราจะค้นหาชื่อคนงานด้วยวิธีอื่นได้อย่างไร ในชุดข้อมูล สามารถกำหนดบุคคลหนึ่งคนให้กับหลายโครงการได้ ตอนนี้งานของเราคือค้นหาชื่อโครงการโดยใช้ชื่อของผู้ปฏิบัติงาน ชุดข้อมูลหลักจะเป็นดังนี้:
ขั้นตอนที่ 1: ป้อนสูตรต่อไปนี้ในเซลล์ G6 แล้วกด ENTER กุญแจ
=FILTER(B4:B16, G5=C4:C16) คำอธิบายสูตร
- ใน ตัวกรอง ฟังก์ชัน B4:B16 นี่คือช่วงที่เราจะดึงข้อมูล
- G5 ในเซลล์นี้จะระบุชื่ออินพุตและจะเปรียบเทียบกับช่วงชื่อ C4:C16
- หากต้องการสำรวจเพิ่มเติมเกี่ยวกับฟังก์ชัน FILTER สามารถดูได้ที่ลิงก์นี้
ขั้นตอนที่ 2: ตอนนี้พิมพ์ชื่อใดก็ได้ในเซลล์ G5 แล้วกด Enter
3. บล็อกการเปลี่ยนแปลงในรายการแบบหล่นลงแรก
สมมติว่าเรามีรายการอาหารที่แตกต่างกันหลายรายการ แต่ละรายการจะแตกต่างจากรายการอื่นๆ และรายการอาหารเฉพาะควรอยู่ในรายการที่ถูกต้อง
ในแผ่นงานอื่น เราจะเลือกรายการอาหารตามประเภท
ข้อกังวลของเราคือหากเราเลือกประเภทอาหารใดๆ ในคอลัมน์ B แล้วในคอลัมน์ C (รายการ) จะมีเฉพาะรายการซึ่งอยู่ภายใต้ประเภทหรือรายการอาหารนั้นเท่านั้น
ขั้นตอนที่ 1: เลือกเซลล์รายการอาหารและเปิดการตรวจสอบข้อมูล
ขั้นตอนที่ 2: จากนั้นใน Source ให้เขียนสูตรต่อไปนี้
=IF(B4="",Foods, INDIRECT("FakeRange"))
ขั้นตอนที่ 3: คำเตือนจะปรากฏขึ้น คลิกที่ ใช่ ปุ่ม
ขั้นตอนที่ 4: ตอนนี้เลือกประเภทอาหารแล้วเลือกรายการ
ขั้นตอนที่ 5: เมื่อคุณป้อนประเภทอาหารและรายการอาหารแล้ว คุณจะไม่สามารถเปลี่ยนแปลงรายการอาหารใดๆ ได้ จึงไม่มีโอกาสเกิดข้อผิดพลาดในการจับคู่
4. สร้างรายการที่ไม่ซ้ำใน Excel ตามเกณฑ์
ในแง่ของการค้นหารายการค่าที่ไม่ซ้ำ Excel มีหลายวิธี ลองพิจารณาชุดข้อมูลเดียวกันกับวิธีที่ 2 ด้วยค่าที่ซ้ำกัน ตอนนี้เป้าหมายของเราคือการค้นหารายการเฉพาะโดยใช้สูตร
ขั้นตอนที่ 1: ป้อนสูตรต่อไปนี้ในเซลล์ G6
=UNIQUE(FILTER(B4:B22,C4:C22=G5))
- ตัวกรอง(B4:B22, C4:C22=G5) ฟังก์ชันนี้เหมือนกับวิธีที่ 2 โดยจะแยกชื่อที่ตรงกันทั้งหมดออกจากชุดข้อมูล หากมีรายการที่ตรงกัน ตัวกรอง ฟังก์ชันก็นับด้วย
- หากต้องการลบค่าที่ซ้ำกันที่ส่งคืนโดย FILTER ฟังก์ชัน เราได้ใช้ UNIQUE การทำงาน. ฟังก์ชันนี้จะลบค่าที่ซ้ำกันทั้งหมดออกจากข้อมูลที่ตรงกัน หากต้องการสำรวจเพิ่มเติมเกี่ยวกับฟังก์ชัน โปรดไปที่ลิงก์นี้
ขั้นตอนที่ 2: ตอนนี้ป้อน ชื่อ ในเซลล์ G5 แล้วกด Enter
5. แยกแถวทั้งหมดออกจากรายการที่ตรงตามเกณฑ์ในคอลัมน์เดียวโดยใช้สูตรอาร์เรย์
มาสร้างชุดข้อมูลผลิตภัณฑ์ที่มีID , แบรนด์ , รุ่น และ ราคาต่อหน่วย . ตอนนี้งานของเราคือค้นหาแถวที่ชื่อแบรนด์จะตรงกับ ชื่อแบรนด์ ที่เราป้อน ในเซลล์ H5 และ H7 .
ขั้นตอนที่ 1 :ป้อนสูตรต่อไปนี้ในเซลล์ B19 แล้วกด CTRL + SHIFT + ENTER และคัดลอกสูตรไปทั้งตาราง
=INDEX($B$4:$E$15, SMALL(IF(COUNTIF($H$5:$H$7,$C$4:$C$15), MATCH(ROW($B$4:$E$15), ROW($B$4:$E$15)), ""), ROWS(B19:$B$19)), COLUMNS($B$3:B3
ขั้นตอนที่ 2: ตอนนี้ป้อนชื่อในเซลล์ H5 และ H7 แล้วกด Enter
6. สร้างรายการดรอปดาวน์ที่ขึ้นต่อกันใน Excel
ใน MS Excel รายการดรอปดาวน์เป็นคุณลักษณะที่มีประโยชน์เมื่อเราดำเนินการฟอร์มการป้อนข้อมูลหรือแดชบอร์ดของ Excel
มันแสดงรายการของรายการเป็นดรอปดาวน์ในเซลล์ และผู้ใช้สามารถเลือกจากดรอปดาวน์ ซึ่งอาจเป็นประโยชน์เมื่อคุณมีรายชื่อ ผลิตภัณฑ์ หรือภูมิภาคที่เรามักต้องใส่ในชุดเซลล์
สมมติว่าเรามีรายการอาหารที่แตกต่างกันสามรายการ ตอนนี้เราจะสร้างรายการดรอปดาวน์ที่ขึ้นต่อกันใน Excel โดยใช้รายการเหล่านั้น
ขั้นตอนที่ 1: เปิดการตรวจสอบข้อมูล ตัวเลือก
ขั้นตอนที่ 2: ใน การตรวจสอบข้อมูล หน้าต่าง เลือก อนุญาต เป็น รายการ และเลือก ที่มา ดังต่อไปนี้
ขั้นตอนที่ 3: คุณจะพบ รายการแบบเลื่อนลง รายการในคอลัมน์ประเภทอาหาร
ขั้นตอนที่ 4: ตอนนี้เลือกชุดข้อมูลทั้งหมดแล้วคลิก สร้างจากส่วนที่เลือก ตัวเลือกภายใต้ สูตร แถบ
ขั้นตอนที่ 5: ป๊อปอัปจะปรากฏขึ้น คลิกที่ แถวบนสุด แล้วกดปุ่ม ตกลง ปุ่ม
ขั้นตอนที่ 6: ตอนนี้ไปที่เซลล์ D14 และเปิดการตรวจสอบข้อมูล . ตรวจสอบให้แน่ใจว่า อนุญาต ถูกตั้งค่าเป็น รายการ แล้วเขียนสูตรต่อไปนี้ใน ที่มา . สุดท้าย ให้กดปุ่ม ตกลง ปุ่ม
=INDIRECT(B14)
ขั้นตอนที่ 7: คำเตือนจะปรากฏขึ้น กดปุ่ม ใช่ ปุ่ม
ขั้นตอนที่ 8: ตอนนี้เลือกประเภทอาหารใดก็ได้จากรายการแบบเลื่อนลงรายการแรกและค้นหารายการที่เกี่ยวข้องในรายการแบบเลื่อนลงอื่น ๆ
ขั้นตอนที่ 8: ผลลัพธ์สุดท้ายจะเป็นดังนี้:
สิ่งที่ควรจำ
| ข้อผิดพลาดทั่วไป | เมื่อแสดง |
|---|---|
| #VALUE! เกิดข้อผิดพลาดใน ตัวกรอง | อาร์กิวเมนต์ include ต้องมีมิติที่เข้ากันได้กับอาร์กิวเมนต์อาร์เรย์ มิฉะนั้น FILTER จะส่งกลับ #VALUE! |
| #NA! ผิดพลาด | หากสูตรไม่พบข้อมูลใด ๆ จากชุดข้อมูล ก็จะส่งคืนข้อผิดพลาดนี้ เราจำเป็นต้องได้รับความช่วยเหลือจาก IFERROR เพื่อจัดการกับข้อผิดพลาดนี้ |
| การตั้งชื่อในรายการ | ในแง่ของการให้ชื่อรายการ เราไม่สามารถใช้ช่องว่างใดๆ หากมีการเว้นวรรคในชื่อ เราก็สามารถใช้ “_ ”. |
บทสรุป
นี่คือวิธีการเติมรายการตามค่าของเซลล์ใน Excel ฉันได้แสดงวิธีการทั้งหมดพร้อมตัวอย่างที่เกี่ยวข้องแล้ว แต่สามารถทำซ้ำได้อีกมากมาย ฉันได้กล่าวถึงพื้นฐานของฟังก์ชันที่ใช้แล้ว หากคุณมีวิธีอื่นในการบรรลุเป้าหมายนี้ โปรดแชร์ให้เราทราบ
อ่านเพิ่มเติม
- ถ่ายโอนข้อมูลจากแผ่นงาน Excel หนึ่งไปยังอีกแผ่นหนึ่งโดยอัตโนมัติ
- วิธีการเติมข้อมูลอัตโนมัติจากแผ่นงานอื่นใน Excel
- วิธีการกรอกข้อมูลลงในแถวสุดท้ายด้วยข้อมูลใน Excel (วิธีด่วน 3 วิธี)
