ไปยังเนื้อหาหลัก

สำรวจสิ่งใหม่ รุ่นฟรี ของ Kutools for Outlook วันนี้!

วิธีสร้างไดนามิก 10 หรือ n รายการใน Excel?

ผู้เขียน: Xiaoyang แก้ไขล่าสุด: 2024-11-08

รายการยอดนิยมใช้เพื่อจัดอันดับบริษัทหรือบุคคลตามค่านิยม สมมติว่า คุณมีรายการเกรดของนักเรียนในชั้นเรียน ตอนนี้ คุณต้องการสร้างรายชื่อนักเรียน 10 อันดับแรกแบบไดนามิกดังที่แสดงภาพหน้าจอด้านล่าง ในบทความนี้ ผมจะแนะนำสูตรบางอย่างสำหรับการสร้างรายการ 10 หรือ n อันดับแรกในเวิร์กชีต Excel

สร้างรายการ 10 อันดับแรกแบบไดนามิกใน Excel

ใน Excel 2019 และเวอร์ชันก่อนหน้า หากต้องการแยกรายการ 10 อันดับแรกหรือรายการ 10 อันดับแรกที่มีเกณฑ์ คุณควรใช้สูตรต่อไปนี้:

สูตรสร้างไดนามิก 10 อันดับแรก

1. ขั้นแรก คุณควรแยกค่า 10 อันดับแรกออกจากช่วงข้อมูล โปรดใช้สูตรด้านล่างลงในเซลล์ว่าง - G2 แล้วลากที่จับเติมลงไปเพื่อให้ได้ค่า 10 อันดับแรก ดูภาพหน้าจอ:

=LARGE($B$2:$B$20,ROWS(B$2:B2))
หมายเหตุ: ในสูตรนี้ B2: B20 เป็นรายการข้อมูลที่คุณต้องการรับค่า 10 อันดับแรก และ B2 เป็นเซลล์แรกของรายการข้อมูล

ใช้สูตรในการแยกค่า 10 อันดับแรก

2. จากนั้นใช้สูตรต่อไปนี้ในเซลล์ - F2 แล้วกด Ctrl + Shift + Enter คีย์เข้าด้วยกันเพื่อให้ได้ผลลัพธ์แรกดูภาพหน้าจอ:

=INDEX($A$2:$A$20,SMALL(IF($B$2:$B$20=G2,ROW($B$2:$B$20)-ROW($B$1)),COUNTIF($G$2:G2,G2)))
หมายเหตุ: ในสูตรข้างต้น A2: A20 เป็นรายการข้อมูลที่คุณต้องการดึงชื่อค่า 10 อันดับแรกจาก B2: B20 เป็นรายการข้อมูลที่มีค่าทั้งหมด G2 เป็นเซลล์ที่มีค่ามากที่สุดที่ดึงมาจากคอลัมน์ B และ B1 เป็นเซลล์ส่วนหัวของรายการค่า

ใช้สูตรในการหาค่าสัมพันธ์

3. หลังจากได้รับผลลัพธ์แรกแล้ว ให้เลือกเซลล์สูตรแล้วลากที่จับเติมลงไปเพื่อรับชื่ออื่นๆ พร้อมกัน ดูภาพหน้าจอ:

ลากและเติมสูตรลงในเซลล์อื่น

ภาพหน้าจอของ kutools สำหรับ excel ai

ปลดล็อก Excel Magic ด้วย Kutools AI

  • การดำเนินการอย่างชาญฉลาด: ดำเนินการกับเซลล์ วิเคราะห์ข้อมูล และสร้างแผนภูมิ ทั้งหมดนี้ขับเคลื่อนด้วยคำสั่งง่ายๆ
  • สูตรที่กำหนดเอง: สร้างสูตรที่ปรับแต่งโดยเฉพาะเพื่อปรับปรุงขั้นตอนการทำงานของคุณ
  • การเข้ารหัส VBA: เขียนและใช้งานโค้ด VBA ได้อย่างง่ายดาย
  • การตีความสูตร: เข้าใจสูตรที่ซับซ้อนได้อย่างง่ายดาย
  • การแปลข้อความ: ทลายกำแพงด้านภาษาภายในสเปรดชีตของคุณ
ปรับปรุงความสามารถ Excel ของคุณด้วยเครื่องมือที่ขับเคลื่อนด้วย AI ดาวน์โหลดเดี๋ยวนี้ และสัมผัสประสิทธิภาพอย่างที่ไม่เคยมีมาก่อน!
สูตรสร้างไดนามิก 10 อันดับแรกพร้อมเกณฑ์

บางครั้ง คุณอาจต้องได้รับรายชื่อ 10 อันดับแรกตามเกณฑ์ ตัวอย่างเช่นเพื่อให้ได้ชื่อและคะแนน 10 อันดับแรกของ Class1 ตามภาพหน้าจอด้านล่าง

สร้างรายชื่อ 10 อันดับแรกแบบไดนามิกพร้อมเกณฑ์

1. เพื่อให้ได้รายชื่อ 10 อันดับแรก คุณควรแยกคะแนน 10 อันดับแรกด้วยสูตรนี้:

=LARGE(IF($B$2:$B$25=$F$2,$C$2:$C$25),ROW(I2)-ROW(I$1))

2. จากนั้นกด Ctrl + Shift + Enter คีย์ร่วมกันเพื่อให้ได้ผลลัพธ์แรก จากนั้นลากสูตรนี้ลงเพื่อแสดงค่าอื่นๆ ดูภาพหน้าจอ:

ใช้สูตรในการแยกค่า 10 อันดับแรกตามเกณฑ์

3. จากนั้น โปรดคัดลอกและวางสูตรต่อไปนี้ลงในเซลล์ - I2 แล้วกด Ctrl + Shift + Enter กดปุ่มพร้อมกันเพื่อแยกผลลัพธ์แรก จากนั้นลากสูตรนี้เพื่อเติมลงในเซลล์อื่น และแสดงชื่อ 10 อันดับแรก

=INDEX($A$2:$A$25,SMALL(IF(($C$2:$C$25=J2)*($B$2:$B$25=$F$2),ROW($C$2:$C$25)-ROW($C$1)),COUNTIF(J2:$J$2,J2)))

สร้างรายการ 10 อันดับแรกแบบไดนามิกใน Office 365

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

สูตรสร้างไดนามิก 10 อันดับแรก

ในการรับรายการข้อมูล 10 รายการ โปรดใช้สูตรด้านล่าง:

=INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2})

แล้วกด เข้าสู่ สำคัญ ข้อมูลทั้งหมดของ 10 อันดับแรกจะแสดงพร้อมกัน ดูภาพหน้าจอ:

ใช้สูตรเพื่อสร้างรายการ 10 อันดับแรกแบบไดนามิกใน Office 365

เคล็ดลับ:

ฟังก์ชัน SORT:

=SORT(อาร์เรย์, [sort_index], [sort_order], [by_col])

  • แถว: ช่วงของเซลล์ที่คุณต้องการจัดเรียง
  • [เรียงลำดับ_ดัชนี]: หมายเลขคอลัมน์หรือแถวที่จะจัดเรียงตามอาร์เรย์ ตัวอย่างเช่น หากต้องการจัดเรียงตามคอลัมน์ที่สองของช่วงข้อมูล ดัชนีการจัดเรียงจะเป็น 2;
  • [จัดเรียง_ลำดับ]: หมายเลข 1 (หรือละเว้น) หมายถึงการเรียงลำดับจากน้อยไปมาก; หมายเลข -1 เรียงลำดับจากมากไปน้อย
  • [by_col]: ทิศทางการเรียงลำดับ TRUE เรียงตามคอลัมน์ FALSE หรือละเว้น เรียงตามแถว

ในสูตร เราใช้ฟังก์ชัน SORT ดังนี้

เรียงลำดับ(A2:B20,2,-1): หมายถึงการเรียงลำดับช่วงเซลล์ A2:A20 ในคอลัมน์ที่สองโดยเรียงลำดับจากมากไปหาน้อย

ฟังก์ชันลำดับ:

=SEQUENCE(แถว, [คอลัมน์], [เริ่ม], [ขั้นตอน])

  • แถว: จำนวนแถวที่จะส่งคืน
  • [คอลัมน์]: จำนวนคอลัมน์ที่จะส่งคืน หากละเว้น จะแสดงคอลัมน์เดียว
  • [เริ่ม]: ตัวเลขแรกในลำดับ หากละเว้น จะเริ่มที่ 1
  • [ขั้นตอน]: การเพิ่มระหว่างแต่ละตัวเลข หากไม่รวม การเพิ่มแต่ละครั้งจะเป็น 1

ในสูตรนี้ เราใช้ SEQUENCE(10) เพื่อสร้างรายการตั้งแต่ 1 ถึง 10

สุดท้าย ใส่ทั้งฟังก์ชัน SORT และ SEQUENCE ลงในฟังก์ชัน INDEX: =INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2})ซึ่งจะส่งคืน 10 ระเบียนแรกจากช่วงข้อมูลและส่งคืนคอลัมน์ 1 และ 2

สูตรสร้างไดนามิก 10 อันดับแรกพร้อมเกณฑ์

หากต้องการแสดงรายการ 10 อันดับแรกพร้อมเกณฑ์ คุณควรฝังฟังก์ชัน FILTER ลงในฟังก์ชัน SORT ดังนี้:

=INDEX(SORT(FILTER(A2:C25,B2:B25=F2),3,-1),SEQUENCE(10),{1,3})

แล้วกด เข้าสู่ สำคัญ ข้อมูลทั้งหมดของรายการ 10 อันดับแรกตามเกณฑ์ที่กำหนดจะแสดงพร้อมกัน ดูภาพหน้าจอ:

สูตรอื่นในการสร้างรายชื่อ 10 อันดับแรกแบบไดนามิกพร้อมเกณฑ์ใน Office 365

เคล็ดลับ:

ฟังก์ชันตัวกรอง:

=FILTER(อาร์เรย์ รวม [if_empty])

  • แถว: ช่วงของเซลล์ที่จะกรอง
  • ประกอบด้วย: เงื่อนไขที่คุณใช้กรองอาร์เรย์เพื่อรับอาร์เรย์ของผลลัพธ์ TRUE หรือ FALSE เพื่อให้ค่า TRUE ยังคงอยู่ในตัวกรอง
  • [ถ้า_ว่างเปล่า]: ค่าที่จะแสดงหากไม่มีผลลัพธ์ที่ตรงกัน

ในสูตรนี้: =FILTER(A2:C25,B2:B25=F2) ใช้เพื่อกรองในช่วง A2:C25 โดยที่ค่าจาก B2:B25 เท่ากับเซลล์ F2 ที่ระบุ

สุดยอดเครื่องมือเพิ่มผลผลิตในสำนักงาน

🤖 Kutools AI ผู้ช่วย: ปฏิวัติการวิเคราะห์ข้อมูลโดยยึดตาม: การดำเนินการที่ชาญฉลาด   |  สร้างรหัส  |  สร้างสูตรที่กำหนดเอง  |  วิเคราะห์ข้อมูลและสร้างแผนภูมิ  |  เรียกใช้ฟังก์ชัน Kutools...
คุณสมบัติยอดนิยม: ค้นหา เน้น หรือระบุรายการที่ซ้ำกัน   |  ลบแถวว่าง   |  รวมคอลัมน์หรือเซลล์โดยไม่สูญเสียข้อมูล   |   รอบโดยไม่มีสูตร ...
การค้นหาขั้นสูง: VLookup หลายเกณฑ์  |   VLookup หลายค่า  |   VLookup ข้ามหลายแผ่น   |   การค้นหาที่ไม่ชัดเจน ....
รายการแบบเลื่อนลงขั้นสูง: สร้างรายการแบบหล่นลงอย่างรวดเร็ว   |  รายการแบบหล่นลงขึ้นอยู่กับ   |  เลือกหลายรายการแบบหล่นลง ....
ผู้จัดการคอลัมน์: เพิ่มจำนวนคอลัมน์เฉพาะ  |  ย้ายคอลัมน์  |  สลับสถานะการมองเห็นของคอลัมน์ที่ซ่อนอยู่  |  เปรียบเทียบช่วงและคอลัมน์ ...
คุณสมบัติเด่น: กริดโฟกัส   |  มุมมองการออกแบบ   |   บาร์สูตรใหญ่   |  สมุดงานและตัวจัดการชีต   |  ห้องสมุดทรัพยากร (ข้อความอัตโนมัติ)   |  เลือกวันที่   |  รวมแผ่นงาน   |  เข้ารหัส/ถอดรหัสเซลล์   |  ส่งอีเมลตามรายการ   |  ซุปเปอร์ฟิลเตอร์   |   ตัวกรองพิเศษ (กรองตัวหนา/ตัวเอียง/ขีดทับ...) ...
ชุดเครื่องมือ 15 อันดับแรก:  12 ข้อความ เครื่องมือ (เพิ่มข้อความ, ลบอักขระ, ... )   |   50 + แผนภูมิ ประเภท (แผนภูมิ Gantt, ... )   |   40+ ใช้งานได้จริง สูตร (คำนวณอายุตามวันเกิด, ... )   |   19 การแทรก เครื่องมือ (ใส่ QR Code, แทรกรูปภาพจากเส้นทาง, ... )   |   12 การแปลง เครื่องมือ (ตัวเลขเป็นคำ, การแปลงสกุลเงิน, ... )   |   7 ผสานและแยก เครื่องมือ (แถวรวมขั้นสูง, แยกเซลล์, ... )   |   ... และอื่น ๆ

เพิ่มพูนทักษะ Excel ของคุณด้วย Kutools สำหรับ Excel และสัมผัสประสิทธิภาพอย่างที่ไม่เคยมีมาก่อน Kutools สำหรับ Excel เสนอคุณสมบัติขั้นสูงมากกว่า 300 รายการเพื่อเพิ่มประสิทธิภาพและประหยัดเวลา  คลิกที่นี่เพื่อรับคุณสมบัติที่คุณต้องการมากที่สุด...

แท็บ Office นำอินเทอร์เฟซแบบแท็บมาที่ Office และทำให้งานของคุณง่ายขึ้นมาก

  • เปิดใช้งานการแก้ไขและอ่านแบบแท็บใน Word, Excel, PowerPoint, ผู้จัดพิมพ์, Access, Visio และโครงการ
  • เปิดและสร้างเอกสารหลายรายการในแท็บใหม่ของหน้าต่างเดียวกันแทนที่จะเป็นในหน้าต่างใหม่
  • เพิ่มประสิทธิภาพการทำงานของคุณ 50% และลดการคลิกเมาส์หลายร้อยครั้งให้คุณทุกวัน!