Note: The other languages of the website are Google-translated. Back to English

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

รายการยอดนิยมใช้เพื่อจัดอันดับบริษัทหรือบุคคลตามค่านิยม สมมติว่า คุณมีรายการเกรดของนักเรียนในชั้นเรียน ตอนนี้ คุณต้องการสร้างรายชื่อนักเรียน 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 เป็นเซลล์แรกของรายการข้อมูล

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


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

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

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

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

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

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 อันดับแรกจะแสดงพร้อมกัน ดูภาพหน้าจอ:

เคล็ดลับ:

ฟังก์ชัน SORT:

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

  • แถว: ช่วงของเซลล์ที่คุณต้องการจัดเรียง
  • [sort_index]: หมายเลขคอลัมน์หรือแถวที่จะจัดเรียงตามอาร์เรย์ ตัวอย่างเช่น หากต้องการจัดเรียงตามคอลัมน์ที่สองของช่วงข้อมูล ดัชนีการจัดเรียงจะเป็น 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 อันดับแรกตามเกณฑ์ที่กำหนดจะแสดงพร้อมกัน ดูภาพหน้าจอ:

เคล็ดลับ:

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

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

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

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


เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด

Kutools สำหรับ Excel แก้ปัญหาส่วนใหญ่ของคุณและเพิ่มผลผลิตของคุณได้ถึง 80%

  • นำมาใช้ใหม่: ใส่อย่างรวดเร็ว สูตรที่ซับซ้อนแผนภูมิ และสิ่งที่คุณเคยใช้มาก่อน เข้ารหัสเซลล์ ด้วยรหัสผ่าน; สร้างรายชื่อผู้รับจดหมาย และส่งอีเมล ...
  • ซุปเปอร์ฟอร์มูล่าบาร์ (แก้ไขข้อความและสูตรหลายบรรทัดได้อย่างง่ายดาย); การอ่านเค้าโครง (อ่านและแก้ไขเซลล์จำนวนมากได้อย่างง่ายดาย); วางลงในช่วงที่กรองแล้ว...
  • ผสานเซลล์ / แถว / คอลัมน์ โดยไม่สูญเสียข้อมูล แยกเนื้อหาของเซลล์ รวมแถว / คอลัมน์ที่ซ้ำกัน... ป้องกันเซลล์ซ้ำ; เปรียบเทียบช่วง...
  • เลือกซ้ำหรือไม่ซ้ำ แถว; เลือกแถวว่าง (เซลล์ทั้งหมดว่างเปล่า); Super Find และ Fuzzy Find ในสมุดงานจำนวนมาก สุ่มเลือก ...
  • สำเนาถูกต้อง หลายเซลล์โดยไม่เปลี่ยนการอ้างอิงสูตร สร้างการอ้างอิงอัตโนมัติ ถึงหลายแผ่น ใส่สัญลักษณ์แสดงหัวข้อย่อย, กล่องกาเครื่องหมายและอื่น ๆ ...
  • แยกข้อความ, เพิ่มข้อความ, ลบตามตำแหน่ง, ลบ Space; สร้างและพิมพ์ผลรวมย่อยของเพจ แปลงระหว่างเนื้อหาของเซลล์และความคิดเห็น...
  • ซุปเปอร์ฟิลเตอร์ (บันทึกและใช้โครงร่างตัวกรองกับแผ่นงานอื่น ๆ ); การเรียงลำดับขั้นสูง ตามเดือน / สัปดาห์ / วันความถี่และอื่น ๆ ตัวกรองพิเศษ โดยตัวหนาตัวเอียง ...
  • รวมสมุดงานและแผ่นงาน; ผสานตารางตามคอลัมน์สำคัญ แยกข้อมูลออกเป็นหลายแผ่น; Batch แปลง xls, xlsx และ PDF...
  • คุณสมบัติที่ทรงพลังมากกว่า 300 รายการ. รองรับ Office/Excel 2007-2021 และ 365 รองรับทุกภาษา ง่ายต่อการปรับใช้ในองค์กรหรือองค์กรของคุณ คุณสมบัติเต็มรูปแบบ ทดลองใช้ฟรี 30 วัน รับประกันคืนเงินภายใน 60 วัน
kte แท็บ 201905

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

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

ช่องทางอื่นๆ

ลิขสิทธิ์© 2009 - wwwextendoffice.com | สงวนลิขสิทธิ์. ขับเคลื่อนโดย ExtendOffice. | แผนผังเว็บไซต์
Microsoft และโลโก้ Office เป็นเครื่องหมายการค้าหรือเครื่องหมายการค้าจดทะเบียนของ Microsoft Corporation ในสหรัฐอเมริกาและ / หรือประเทศอื่น ๆ
ได้รับการปกป้องโดย Sectigo SSL