วิธีใช้ฟังก์ชันอันดับและ SUMPRODUCT ใน Excel ด้วย COUNTIF

การจัดลำดับรายการจากน้อยไปมากหรือมากไปหาน้อยโดยใช้ฟังก์ชัน COUNTIF, RANK.EQ และ SUMPRODUCT ร่วมกันนั้นค่อนข้างง่าย คุณสามารถเรียงลำดับรายการโดยมีหรือไม่มีการทำซ้ำ และคงการควบคุมไว้เพื่อข้ามหรือไม่ข้ามลำดับใดๆ ในลำดับ

ด้วยการกำหนดอันดับเดียวกันให้กับรายการที่เหมือนกันหลายรายการ ฟังก์ชัน RANK.EQ จะข้ามตัวเลขในลำดับโดยรวม เพื่อหลีกเลี่ยงการข้ามนี้ ให้ใช้ฟังก์ชัน SUMPRODUCT กับฟังก์ชัน COUNTIF ชุดค่าผสมนี้จัดอันดับค่าที่ซ้ำกันโดยไม่ข้ามตัวเลข

---

---

ฟังก์ชันอันดับต่างๆใน Excel

ก่อนที่เราจะพิจารณาการใช้งานให้ละเอียดยิ่งขึ้น เรามาทบทวนสั้น ๆ อีกสองรูปแบบที่พบใน Excel; RANK.AVG และ RANK

ใน Excel เวอร์ชันก่อนหน้า มีเพียงฟังก์ชัน RANK เท่านั้นที่พร้อมใช้งาน อย่างไรก็ตาม ตั้งแต่ Excel 2010 ได้มีการเพิ่มฟังก์ชันอันดับเพิ่มเติมสองฟังก์ชัน ได้แก่ RANK.AVG และ RANK.EQ มาสำรวจความแตกต่างกัน เนื่องจากคุณน่าจะเห็นพวกเขาทั้งหมดใน Excel

ฟังก์ชัน RANK.EQ: RANK.EQ เป็นฟังก์ชัน RANK เวอร์ชันใหม่ที่ได้รับการปรับปรุง และได้รับการสนับสนุนใน Excel เวอร์ชันล่าสุด อย่างไรก็ตาม ฟังก์ชันทั้งสองเหมือนกันและมีจุดประสงค์เดียวกัน ใช้อะไรก็ให้ผลเหมือนกัน

ฟังก์ชันอันดับ: ในทางกลับกัน ฟังก์ชัน RANK สามารถใช้งานได้กับ Excel 2007 และเวอร์ชันก่อนหน้า และอาจถูกเอาออกใน Excel เวอร์ชันใหม่ ทุกวันนี้ ทุกคนใช้ Excel เวอร์ชันล่าสุด ดังนั้นให้ใช้ฟังก์ชัน RANK.EQ เสมอเมื่อคุณต้องแชร์ชีตกับผู้อื่น

อันดับ เฉลี่ย: เมื่อลำดับอันดับใดๆ มีรายการที่ซ้ำกัน ฟังก์ชัน RANK.AVG จะกำหนดอันดับเฉลี่ยให้กับชุดของรายการที่ซ้ำกันแต่ละชุด ดังนั้นจึงมีจุดประสงค์เดียวกันในการจัดอันดับรายการ แต่เป้าหมายหลักคือการจับรายการที่ซ้ำกัน

---

---

เนื่องจากบทความนี้เกี่ยวข้องกับการคำนวณอันดับสำหรับรายการ เรามาดูกันว่าฟังก์ชัน RANK.EQ จะจัดอันดับอย่างไร นอกจากนี้ คุณจะเห็นการใช้งานร่วมกับฟังก์ชัน COUNTIF

ก่อนนำสูตรไปใช้ มาดูภาพรวมคร่าวๆ ของไวยากรณ์ของฟังก์ชัน RANK.EQ

---

---

ภาพรวมของฟังก์ชันอันดับของ Excel

ดังนั้น มีสามอาร์กิวเมนต์ในฟังก์ชัน RANK.EQ; หมายเลขอ้างอิงและการสั่งซื้อ จำเป็นต้องมีอาร์กิวเมนต์สองตัวแรก ในขณะที่อาร์กิวเมนต์ที่สามเป็นทางเลือก ในการดำเนินการ RANK.EQ ในอาร์เรย์ของตัวเลข คุณต้องระบุสองอาร์กิวเมนต์แรกเสมอ

มาดูหน้าที่ของอาร์กิวเมนต์แต่ละตัวกัน:

1. ตัวเลข: อาร์กิวเมนต์นี้หมายถึงหมายเลขที่คุณต้องการทราบอันดับ
2. อ้างอิง: อาร์กิวเมนต์อ้างอิงหมายถึงอาร์เรย์ของรายการตัวเลขในลำดับ โปรดทราบว่าผู้อ้างอิงจะละเว้นค่าที่ไม่ใช่ตัวเลขที่พบในอาร์เรย์
3. คำสั่ง: แม้ว่าจะเป็นอาร์กิวเมนต์ที่ไม่บังคับ แต่คุณสามารถระบุฟังก์ชัน RANK เพื่อจัดอันดับรายการได้ทั้งจากมากไปหาน้อยหรือจากน้อยไปหามากโดยเลือก 0 หรือ 1 ในอาร์กิวเมนต์นี้ โดยค่าเริ่มต้น หากคุณไม่ใช้งาน รายการจะเรียงลำดับจากมากไปหาน้อย

ตัวอย่างฟังก์ชันอันดับของ Excel

ดังนั้น เมื่อคุณเข้าใจอาร์กิวเมนต์ของฟังก์ชัน RANK.EQ แล้ว มาลองใช้กับชุดข้อมูลเพื่อดูว่ามันทำงานอย่างไร

ดูตัวอย่างข้อมูลตัวอย่างที่มีเปอร์เซ็นต์ของนักเรียนสิบคน คุณสามารถใช้ฟังก์ชัน Rank.EQ เพื่อคำนวณอันดับอย่างรวดเร็วตามเปอร์เซ็นต์ของนักเรียนเหล่านี้ ทำให้ไม่จำเป็นต้องคำนวณด้วยตนเอง

อย่างไรก็ตาม มันจะข้ามตัวเลขเมื่อมีการซ้ำซ้อน ดังนั้นคุณอาจต้องใช้ฟังก์ชันอื่นๆ ที่กล่าวถึงในบทความต่อไป ด้านล่างนี้คือรายชื่อนักเรียนที่มีเปอร์เซ็นต์สุดท้าย

ทำไมตอนนี้การ์ดจอถึงแพงจัง

อาร์กิวเมนต์แรกคือตัวเลขที่คุณต้องการจัดอันดับ ซึ่งเป็นเปอร์เซ็นต์ของ James Walker และนักเรียนคนอื่นๆ ในแถวสำหรับตัวอย่างนี้ ดังนั้น เลือกเซลล์ B2 เป็นอาร์กิวเมนต์แรก

ช่วงอ้างอิง ในกรณีนี้ จะมาจากเซลล์ B2 ถึง B11 และเลือกจากน้อยไปหามากในอาร์กิวเมนต์ของลำดับ ด้านล่างนี้ คุณสามารถดูการใช้งานฟังก์ชัน RANK.EQ สูตรมีลักษณะดังนี้:

=RANK.EQ(B2,$B:$B,0)

กด เข้า จะดำเนินการตามสูตรและคุณจะได้รับอันดับสำหรับ James Walker

ในตัวอย่างนี้ James Walker อยู่ในอันดับ 5^NSในชั้นเรียนตามเปอร์เซ็นต์ของเขา ซึ่งหมายความว่าเปอร์เซ็นต์ของเขาคือ 5^NSสูงสุดเมื่อเทียบกับตัวเลขที่เหลือในอาร์เรย์

การใช้ฟังก์ชัน RANK ร่วมกับฟังก์ชัน COUNTIF

เป็นไปได้ที่จะใช้ฟังก์ชัน RANK.EQ ร่วมกับฟังก์ชัน COUNTIF เพื่อหยุดการข้ามตัวเลข แต่จะละเว้นการซ้ำซ้อนของอันดับด้วย แม้ว่าจะไม่มีเลขใดซ้ำสอง แต่นักเรียนสองคนที่มีหมายเลขเดียวกันอาจได้ยศต่างกัน

เพื่อให้เข้าใจได้ดีขึ้น มาดูกันว่า RANK.EQ ทำงานร่วมกับ COUNTIF อย่างไร สูตรมีลักษณะดังนี้:

=RANK.EQ(B2,$B:$B,0)+COUNTIF($B:B2,B2)-1

การใช้สูตรนี้จะช่วยแก้ปัญหาการข้ามตัวเลข

ไม่มีการซ้ำซ้อนในอันดับข้างต้น แต่เจมส์ วอล์กเกอร์และกิลเลียน ทิลล์แมน ซึ่งทั้งคู่ควรจะมียศเท่ากัน ตอนนี้อันดับต่างกัน

ดังนั้น การใช้ RANK.EQ กับ COUNTIF สามารถแก้ปัญหาได้ครึ่งหนึ่ง แต่ไม่สามารถให้ผลลัพธ์ที่ต้องการได้

ซอฟต์แวร์ผลิตเพลงฟรีที่ดีที่สุดสำหรับผู้เริ่มต้น

ที่เกี่ยวข้อง: ฟีเจอร์ใหม่บน Excel สำหรับเว็บเพื่อเพิ่มประสิทธิภาพการทำงานของคุณ

การใช้ฟังก์ชัน SUMPRODUCT กับฟังก์ชัน COUNTIF

ในการจัดอันดับนักเรียนในรายการโดยกำหนดอันดับเดียวกันสำหรับเปอร์เซ็นต์ที่เท่ากันโดยไม่ข้ามตัวเลขใดๆ คุณสามารถใช้ฟังก์ชัน SUMPRODUCT กับ COUNTIF ได้

ลองดูสูตรด้านล่าง:

สูตรอาจดูซับซ้อน แต่เป็นวิธีที่ดีที่สุดในการจัดอันดับรายการอย่างถูกต้อง ด้วยวิธีนี้ คุณสามารถบรรลุผลลัพธ์ที่ต้องการด้วยอันดับที่ซ้ำกันและไม่มีการข้ามหมายเลข

ในขณะที่สร้างผลลัพธ์ให้กับนักเรียนของคุณ คุณสามารถใช้สูตร SUMPRODUCT แทนฟังก์ชัน RANK ได้โดยตรง สำหรับการคำนวณอันดับที่ไม่ซ้ำ คุณสามารถใช้ฟังก์ชัน RANK.EQ อย่างเดียวหรือกับฟังก์ชัน COUNTIF ก็ได้

การเปลี่ยนลำดับผลลัพธ์สุดท้าย

บน แท็บข้อมูล , คลิก จัดเรียงและกรอง และเลือกลำดับจากน้อยไปมากเพื่อจัดลำดับ

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

ที่เกี่ยวข้อง: วิธีที่ตารางข้อมูลใน Microsoft Excel ให้คุณเปรียบเทียบผลลัพธ์

จัดอันดับรายการอย่างง่ายดายโดยใช้ฟังก์ชัน RANK.EQ และ SUMPRODUCT

นี่คือวิธีที่คุณสามารถใช้ฟังก์ชัน RANK และ SUMPRODUCT ร่วมกับฟังก์ชัน COUNTIF เพื่อจัดอันดับรายการ โดยมีหรือไม่มีการซ้ำซ้อน คุณยังสามารถตัดสินใจได้ว่ามันจะข้ามตัวเลขใด ๆ ในลำดับหรือไม่

คุณสามารถใช้วิธีต่างๆ ในการจัดอันดับตัวเลขตามเกณฑ์ที่กำหนดได้ ทั้งนี้ขึ้นอยู่กับสถานการณ์ นี่คือตัวอย่างวิธีที่ฟังก์ชันของ Excel สามารถช่วยให้คุณประหยัดเวลาและความพยายามอย่างมากในการคำนวณด้วยตนเอง มีอะไรอีกมากมายให้สำรวจเพื่อออกแบบสเปรดชีต excel ที่ดูเป็นมืออาชีพ

แบ่งปัน แบ่งปัน ทวีต อีเมล วิธีสร้างสเปรดชีต Excel ที่ดูเป็นมืออาชีพ

การออกแบบสเปรดชีตมักถูกมองข้าม แต่วิธีการนี้ค่อนข้างง่าย เรียนรู้วิธีสร้างรูปลักษณ์ระดับมืออาชีพใน Excel

อ่านต่อไป หัวข้อที่เกี่ยวข้อง

• ผลผลิต
• สเปรดชีต
• เครื่องคิดเลข
• Microsoft Excel
• คณิตศาสตร์
• การวิเคราะห์ข้อมูล

เกี่ยวกับผู้เขียน ฉาน อับดุล |(เผยแพร่บทความ 46 บทความ)

Shan Abdul เป็นบัณฑิตวิศวกรรมเครื่องกล หลังจากสำเร็จการศึกษา เขาได้เริ่มต้นอาชีพการเป็นนักเขียนอิสระ เขาเขียนเกี่ยวกับการใช้เครื่องมือและซอฟต์แวร์ต่างๆ เพื่อช่วยให้ผู้คนมีประสิทธิผลมากขึ้นในฐานะนักเรียนหรือมืออาชีพ ในเวลาว่าง เขาชอบดูวิดีโอ Youtube เกี่ยวกับประสิทธิภาพการทำงาน

เพิ่มเติมจาก Shan Abdul

สมัครรับจดหมายข่าวของเรา

เข้าร่วมจดหมายข่าวของเราสำหรับเคล็ดลับทางเทคนิค บทวิจารณ์ eBook ฟรี และดีลพิเศษ!

คลิกที่นี่เพื่อสมัครสมาชิก