ใน MS Excel บางครั้งเราอาจต้องกำหนดหรือหาค่าตามเกณฑ์หรือเงื่อนไขจากรายการ สมมติว่าเรามีแผนงานพร้อมชื่อบุคคลที่ดูแลแต่ละงาน และตอนนี้เราต้องรายการชื่องานทั้งหมดในแถว ตามบุคคลที่กำหนด ดרךนี้ Excel มีหลายวิธีสร้างรายการขึ้นจากค่าเซลล์ ในบทความนี้ เราจะเห็นว่าสามารถสร้างรายการขึ้นจากค่าเซลล์ใน Excel ได้อย่างไร
ดาวน์โหลดสมุดงานฝึกฝน
6 วิธี สร้างรายการขึ้นจากค่าเซลล์ใน Excel
1. AutoFill รายการตามค่าเซลล์
มาสร้างรายการพร้อมชื่อพนักงานโครงการ ในแต่ละโครงการ ชื่อพนักงานถูกกำหนดในรูปแบบ “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)
คำอธิบายสูตร
- ในฟังก์ชัน FILTER B4:B16 นี่คือช่วงที่เราจะดึงข้อมูล
- G5 ในเซลล์นี้จะให้ชื่อป้อนเข้า และจะเปรียบเทียบกับช่วงชื่อ C4:C16
- เพื่อสำรวจเพิ่มเติมเกี่ยวกับฟังก์ชัน FILTER คุณสามารถตรวจสอบลิงก์นี้
ขั้นตอนที่ 2: พิมพ์ชื่อใดๆ ในเซลล์ G5 และกด Enter
3. บล็อกการเปลี่ยนแปลงใน Drop Down แรก
สมมติว่าเรามีหลายรายการอาหารที่ต่างกัน แต่ละรายการต่างจากกัน และรายการอาหารเฉพาะควรอยู่ในรายการที่ถูกต้อง
ในชีตงานอื่น เราจะเลือกอาหารตามประเภท
กังวลของเราคือ หากเราเลือกประเภทอาหารในคอลัมน์ B แล้ว ในคอลัมน์ C (Items) จะมีเฉพาะรายการที่อยู่ภายใต้ประเภทอาหารหรือรายการนั้น
ขั้นตอนที่ 1: เลือกเซลล์ Food Items และเปิด Data Validation
ขั้นตอนที่ 2: ใน Source ใส่สูตรต่อไปนี้
=IF(B4="",Foods, INDIRECT("FakeRange"))
ขั้นตอนที่ 3: คำเตือนจะขึ้น คลิกปุ่ม Yes
ขั้นตอนที่ 4: เลือก Food Type แล้วเลือก Items
ขั้นตอนที่ 5: เมื่อคุณป้อน Food Type และ Items แล้ว คุณไม่สามารถเปลี่ยนรายการอาหารได้ ดังนั้น ไม่มีโอกาสจับคู่ผิด
4. สร้างรายการเฉพาะเจาะจงใน Excel ตามเกณฑ์
ในแง่ของการหารายการค่าที่ไม่ซ้ำ Excel มีหลายวิธี มาพิจารณาชุดข้อมูลเดียวกับวิธี 2 ที่มีค่าซ้ำ ตอนนี้เป้าหมายคือ หารายการเฉพาะเจาะจงด้วยสูตร
ขั้นตอนที่ 1: ใส่สูตรต่อไปนี้ในเซลล์ G6
=UNIQUE(FILTER(B4:B22,C4:C22=G5))
- FILTER(B4:B22, C4:C22=G5) ฟังก์ชันนี้เหมือนวิธี 2 ดึงชื่อที่จับคู่ทั้งหมดจากชุดข้อมูล หากมีการจับคู่ซ้ำ ฟังก์ชัน FILTER ก็นับรวม
- เพื่อลบค่าซ้ำที่คืนโดยฟังก์ชัน FILTER เราใช้ฟังก์ชัน UNIQUE ฟังก์ชันนี้จะลบค่าซ้ำทั้งหมดจากข้อมูลที่จับคู่ เพื่อสำรวจเพิ่มเติมเกี่ยวกับฟังก์ชัน คุณสามารถเยี่ยมชมลิงก์นี้
ขั้นตอนที่ 2: ป้อน Name ใดๆ ในเซลล์ G5 และกด Enter
5. ดึงแถวทั้งหมดจากรายการที่ตรงเกณฑ์ในคอลัมน์เดียว ด้วยสูตรอาร์เรย์
มามีชุดข้อมูลสินค้าพร้อม ID, Brand, Model, และ Unit Price ตอนนี้ภารกิจคือ หาแถวเหล่านี้ ที่ชื่อ Brand จะตรงกับ Brand Names ที่เราป้อนในเซลล์ 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. สร้าง Dependent Drop-Down List ใน Excel
ใน MS Excel รายการหล่นเป็นคุณสมบัติที่มีประโยชน์ เมื่อเราทำฟอร์มป้อนข้อมูลหรือ Excel Dashboards
มันแสดงรายการรายการเป็นรายการหล่นในเซลล์ และผู้ใช้สามารถเลือกจากรายการหล่น นี่อาจมีประโยชน์เมื่อคุณมีรายการชื่อ สินค้า หรือภูมิภาค ที่เราต้องป้อนในชุดเซลล์บ่อยครั้ง
สมมติว่าเรามีรายการอาหาร 3 รายการที่ต่างกัน ตอนนี้เราจะสร้าง Dependent Drop-Down List ใน Excel โดยใช้รายการเหล่านั้น
ขั้นตอนที่ 1: เปิดตัวเลือก Data Validation
ขั้นตอนที่ 2: ในหน้าต่าง Data Validation เลือก Allow เป็น List และเลือก Source ตามด้านล่าง
ขั้นตอนที่ 3: คุณจะพบ Drop-Down List ในคอลัมน์ Food Types
ขั้นตอนที่ 4: เลือกชุดข้อมูลทั้งหมด และคลิกที่ตัวเลือก Create from Selection ภายใต้แท็บ Formulas
ขั้นตอนที่ 5: ป๊อปอัปจะขึ้น คลิกที่ Top row แล้วกดปุ่ม Ok
ขั้นตอนที่ 6: ไปที่เซลล์ D14 และเปิด Data Validation ตรวจสอบว่า Allow ตั้งเป็น List แล้ว ใส่สูตรต่อไปนี้ใน Source สุดท้าย กดปุ่ม OK
=INDIRECT(B14)
ขั้นตอนที่ 7: คำเตือนจะขึ้น กดปุ่ม Yes
ขั้นตอนที่ 8: เลือก Food Types ใดๆ จาก Drop Down List แรก และหารายการ Items ที่เกี่ยวข้องใน Drop-Down List อีกอัน
ขั้นตอนที่ 8: ผลลัพธ์สุดท้ายจะเป็นแบบนี้:
สิ่งที่ควรจำ
| ข้อผิดพลาดทั่วไป | เมื่อเกิดขึ้น |
|---|---|
| #VALUE! Error ใน FILTER | อาร์กิวเมนต์ include ต้องมีมิติที่เข้ากันได้กับอาร์กิวเมนต์ array มิฉะนั้น FILTER จะคืน #VALUE! |
| #NA! Error | หากสูตรไม่พบข้อมูลจากชุดข้อมูล จะคืนข้อผิดพลาดนี้ เราต้องใช้ฟังก์ชัน IFERROR จัดการข้อผิดพลาดนี้ |
| การตั้งชื่อในรายการ | ในแง่ของการตั้งชื่อรายการ เราไม่สามารถใช้ช่องว่างได้ หากมีช่องว่างในชื่อ เราสามารถใช้ “_” |
สรุป
นี่คือบางวิธีสร้างรายการขึ้นจากค่าเซลล์ใน Excel ผมแสดงทุกวิธีพร้อมตัวอย่าง แต่ยังมีการทำซ้ำอื่นๆ อาจมากกว่านี้ ผมยังอภิปรายพื้นฐานของฟังก์ชันที่ใช้ หากคุณมีวิธีอื่นในการทำสิ่งนี้ โปรดแบ่งปันกับเราได้เลย
การอ่านเพิ่มเติม
- Transfer Data from One Excel Worksheet to Another Automatically
- How to Auto Populate from Another Worksheet in Excel
- How to Fill Down to Last Row with Data in Excel (3 Quick Methods)
