สร้างรายการ Top 10 แบบไดนามิกใน Excel: 8 วิธีที่พิสูจน์แล้ว

รายการ Top 10 ใช้สำหรับจัดอันดับบริษัทหรือบุคคลตามค่าต่างๆ อันดับนี้กำหนดตามประสิทธิภาพ คุณภาพ หรือเกณฑ์อื่นๆ ในบทความนี้ เราจะเรียนรู้วิธีสร้าง รายการ Top 10 แบบไดนามิก (Dynamic Top 10 List) ใน Excel

8 วิธีสร้างรายการ Top 10 แบบไดนามิกใน Excel

เราจะอภิปรายหลายวิธีในการสร้างรายการ Top 10 แบบไดนามิก ใน Excel รายการ Top 10 ให้ภาพรวมของชื่อนำใน lĩnh vựcต่างๆ

เราใช้ชุดข้อมูลที่มีคะแนนนักเรียน ตอนนี้เราจะสร้าง รายการ Top 10 แบบไดนามิก โดยใช้ชุดข้อมูลนี้

1. ใช้ฟังก์ชัน LARGE ของ Excel สร้างรายการ Top 10

ฟังก์ชัน LARGE หาค่าที่ใหญ่ที่สุดลำดับที่ k จากช่วงที่กำหนด

เราตั้ง ลำดับ 1-10 ในชุดข้อมูลด้านล่าง

ก่อนอื่น เราจะหาคะแนน Top 10 แล้วค่อยหาชื่อนักเรียน

ขั้นตอนที่ 1:

1. ใช้สูตรผสมผสานฟังก์ชัน LARGE และ ROW
2. ไปที่ เซลล์ G5 ใส่สูตรด้านล่าง

=LARGE($C$5:$C$18,ROW(F5)-ROW($F$4))

ขั้นตอนที่ 2:

1. กด Enter และลาก Fill Handle (จุดจับเติม)

เราได้คะแนน Top 10 แล้ว เรายังสามารถใช้ฟังก์ชัน ROWS สำหรับวัตถุประสงค์นี้ได้

สูตรทางเลือก:

เราสามารถใช้สูตรทางเลือกเพื่อให้ผลลัพธ์เดียวกัน

ขั้นตอน:

1. ใช้สูตรต่อไปนี้ใน เซลล์ G5:

=LARGE($C$5:$C$18,ROWS(C$5:C5))

2. กด Enter และลาก Fill Handle จนจบ

ตอนนี้ ถ้าต้องการแสดงชื่อที่ตรงกับ Top 10 ให้ทำดังนี้

ขั้นตอน:

1. ไปที่ เซลล์ F5 ใส่สูตรต่อไปนี้

=INDEX($B$5:$B$18,MATCH(G5,$C$5:$C$18,0))

2. กด Enter และลาก Fill Handle

ที่นี่ เราได้ Top 10 ชื่อพร้อมคะแนนแล้ว

2. รวมฟังก์ชัน INDEX, MATCH, และ LARGE เพื่อสร้างรายการ Top 10

ในส่วนนี้ จะใช้การผสมผสานของ INDEX, MATCH, และ LARGE เราจะได้ชื่อ Top 10 โดยตรงด้วยวิธีนี้

ขั้นตอนที่ 1:

1. ไปที่ เซลล์ F5
2. ใส่สูตรต่อไปนี้ในเซลล์นั้น

=INDEX($B$5:$B$18,MATCH(LARGE($C$5:$C$18,E5),$C$5:$C$18,0))

ขั้นตอนที่ 2:

1. กด Enter และลากไอคอน Fill handle

ที่นี่ เราได้ชื่อ Top 10 โดยตรงโดยไม่ต้องใช้คอลัมน์อื่นช่วย

การวิเคราะห์สูตร:

• LARGE($C$5:$C$18,E5) → สร้างตัวเลขที่ใหญ่ที่สุดจากช่วงตามค่าของ เซลล์ E5 → ผลลัพธ์: 96
• MATCH(LARGE($C$5:$C$18,E5),$C$5:$C$18,0) → แสดงจำนวนผลลัพธ์ที่จะแสดง → ผลลัพธ์: 10
• INDEX($B$5:$B$18,MATCH(LARGE($C$5:$C$18,E5),$C$5:$C$18,0)) → แสดงผลลัพธ์ Top 10 จากช่วง → ผลลัพธ์: {Jose, Henderson, Gerg, Josef, Aaron, Jose, Jessica, Joe, Rogers, Allisa}

3. ใช้สูตรอาร์เรย์ (Array Formula) สร้างรายการ Top 10 แบบไดนามิกใน Excel

เราจะใช้สูตรอาร์เรย์สำหรับวิธีนี้ ทั้งชื่อ Top 10 และคะแนนของนักเรียนจะแสดงหลังจากใช้สูตรนี้ สูตรนี้เป็นการผสมผสานของฟังก์ชัน INDEX, SORT, และ SEQUENCE

ขั้นตอนที่ 1:

1. ไปที่ เซลล์ F5
2. ก็อปปี้และวางสูตรด้านล่าง

=INDEX(SORT(B5:C18,2,-1),SEQUENCE(10),{1,2})

ขั้นตอนที่ 2:

1. กดปุ่ม Enter และลากไอคอน Fill Handle

เราได้ทั้งชื่อและคะแนนเพียงแค่ใช้สูตร ไม่ต้องลากสูตร

การวิเคราะห์สูตร:

• SEQUENCE(10) → ให้ลำดับตัวเลข 1 ถึง 10 → ผลลัพธ์: {1,2,3,4,5,6,7,8,9,10}
• SORT(B5:C18,2,-1) → เรียงลำดับข้อมูลตามคอลัมน์ที่ 2 → ผลลัพธ์: {Jose 96, Henderson 95, Gerg 89, Josef 84, Aaron 80, Jose 71, Jessica 70, Joe 65, Rogers 61, Allisa 60, Joe 57, Allisa 49, John 45, Mitchel 40}
• INDEX(SORT(B5:C18,2,-1),SEQUENCE(10),{1,2}) → ให้รายการ Top 10 ที่เรียงแล้ว → ผลลัพธ์: {Jose 96, Henderson 95, Gerg 89, Josef 84, Aaron 80, Jose 71, Jessica 70, Joe 65, Rogers 61, Allisa 60}

4. ใช้ฟังก์ชัน XLOOKUP ของ Excel ออกแบบรายการ Top 10

ฟังก์ชัน XLOOKUP ค้นหาวัตถุจากช่วงหรืออาร์เรย์ที่กำหนดและให้ผลลัพธ์ตามการจับคู่

เราจะใช้การผสมผสานของ XLOOKUP และ LARGE ในส่วนนี้ เราแค่อยากได้รายการชื่อ Top 10

ขั้นตอนที่ 1:

1. ไปที่ เซลล์ F5 ใส่สูตรต่อไปนี้

=XLOOKUP(LARGE($C$5:$C$18,E5),$C$5:$C$18,$B$5:$B$18)

ขั้นตอนที่ 2:

1. กด Enter
2. ลากไอคอน Fill Handle

5. การผสมผสานฟังก์ชัน SORT และ FILTER สร้างรายการ Top 10

ฟังก์ชัน FILTER อนุญาตให้กรองช่วงค่าจากเกณฑ์ที่กำหนด

ในส่วนนี้ เราจะรวม SORT และ FILTER กับ LARGE เพื่อให้ได้รายการ Top 10 ใน Excel

ขั้นตอนที่ 1:

1. ไปที่ เซลล์ F5
2. ใส่สูตรต่อไปนี้

=SORT(FILTER(B5:C18,C5:C18>=LARGE(C5:C18,10)),2,-1)

ขั้นตอนที่ 2:

1. กด Enter เพียงอย่างเดียว

เราได้ทั้งชื่อและคะแนนของนักเรียนด้วยการใช้วิธีนี้

อ่านเพิ่มเติม: วิธีสร้างรายการไดนามิกใน Excel ตามเกณฑ์ (เกณฑ์เดี่ยวและหลายเกณฑ์)

6. ออกแบบรายการ Top 10 แบบไดนามิกสำหรับข้อมูลซ้ำ (Duplicate Data)

เมื่อเรามีข้อมูลซ้ำ เราอาจใช้วิธีนี้ออกแบบรายการ Top 10 ใน Excel

ขั้นตอนที่ 1:

1. ไปที่ เซลล์ F5
2. ใส่สูตรด้านล่างในเซลล์นั้น

=INDEX($B$5:$B$18, MATCH(1, ($C$5:$C$18=LARGE($C$5:$C$18, E5)) * (COUNTIF(F$4:F4, $B$5:$B$18)=0), 0))

ขั้นตอนที่ 2:

1. กดปุ่ม Enter

ที่นี่ เราได้เฉพาะชื่อนักเรียน

7. ใช้เครื่องมือ Conditional Formatting, Filter, และ Sort สร้างรายการ Top 10 แบบไดนามิก

เราสามารถรวมเครื่องมือหลายอย่างเพื่อให้ได้รายการ Top 10 เครื่องมือเหล่านั้นคือ Conditional Formatting (การจัดรูปแบบตามเงื่อนไข), Filter (ตัวกรอง), และ Sort (การเรียงลำดับ)

ขั้นตอนที่ 1:

1. เลือกเซลล์ C5 ถึง C18
2. คลิก Conditional Formatting จากริบบอน
3. เลือก Top 10 Items จาก Top/Bottom Rules

ขั้นตอนที่ 2:

1. กล่องโต้ตอบจะปรากฏ สังเกต 10 เนื่องจากเป็น Top 10
2. กด OK

ขั้นตอนที่ 3:

1. เลือกเซลล์ C4 ถึง C18
2. กดปุ่มขวาของเมาส์
3. เลือก Filter by Selected Cell's Color จากรายการ Filter

ขั้นตอนที่ 4:

1. คลิกตัวเลือก Filter by Color

นี่คือข้อมูล Top 10 ตอนนี้เราจะเรียงลำดับ

ขั้นตอนที่ 5:

1. กดปุ่มขวาของเมาส์อีกครั้ง
2. เลือก Sort Largest to Smallest จากรายการ Sort

ขั้นตอนที่ 6:

1. เลือก Expand the selection (ขยายการเลือก)
2. กด Sort

ตอนนี้ Top 10 ของเรามีข้อมูลเรียงลำดับดีแล้ว

อ่านเพิ่มเติม: วิธีสร้างรายการตรวจสอบข้อมูลแบบไดนามิกด้วย VBA ใน Excel

8. สร้างรายการ Top 10 ใน Excel ด้วย Pivot Table

เราจะสร้างรายการ Top 10 ใน Excel โดยใช้ Pivot Table

ขั้นตอนที่ 1:

1. ไปที่แท็บ Insert (แทรก)
2. เลือก From Table/Range จากรายการ PivotTable

ขั้นตอนที่ 2:

1. เลือกช่วงจากชุดข้อมูล
2. กด OK

ขั้นตอนที่ 3:

1. ติ๊ก Name (ชื่อ) และ Mark (คะแนน) ของ PivotTable Fields

ขั้นตอนที่ 4:

1. ไปที่ Row Labels (ป้ายกำกับแถว)
2. เลือก Top 10 จาก Value Filters (ตัวกรองค่า)

ขั้นตอนที่ 5:

1. กล่องโต้ตอบจะปรากฏ คลิก OK บนกล่องนั้น

รายการ Top 10 ของเราอยู่ที่นี่ แต่ข้อมูลยังไม่ได้เรียงลำดับ

อ่านเพิ่มเติม: Excel สร้างรายการไดนามิกจากตาราง (3 วิธีง่าย)

สรุป

ในบทความนี้ เราอภิปรายวิธีสร้างรายการ Top 10 แบบไดนามิกใน Excel หวังว่าจะตอบสนองความต้องการของคุณ โปรดเยี่ยมชมเว็บไซต์ Exceldemy.com และให้คำแนะนำในกล่องความคิดเห็น

บทความที่เกี่ยวข้อง

• สร้างรายการแบบเลื่อนลงแบบไดนามิกด้วย Excel OFFSET (3 วิธี)
• วิธีสร้างรายการแบบเลื่อนลงแบบไดนามิกด้วย VBA ใน Excel