สูตร Excel: ค้นหาข้อความที่ใช้บ่อยที่สุดด้วยเกณฑ์
ในบางกรณีคุณอาจต้องการค้นหาข้อความที่ปรากฏบ่อยที่สุดโดยอิงตามเกณฑ์ใน Excel บทช่วยสอนนี้แนะนำสูตรอาร์เรย์เพื่อจัดการงานนี้และให้คำอธิบายเกี่ยวกับอาร์กิวเมนต์ของสูตรนี้
สูตรทั่วไป:
| =INDEX(rng_1,MODE(IF(rng_2=criteria,MATCH(rng_1,rng_1,0)))) |
ข้อโต้แย้ง
| Rng_1: the range of cells that you want to find the most frequent text. |
| Rng_2: the range of cells that contain the criteria you want to use. |
| Criteria: the condition you want to find text based on. |
ส่งคืนค่า
สูตรนี้ส่งคืนข้อความที่พบบ่อยที่สุดพร้อมเกณฑ์เฉพาะ
สูตรนี้ทำงานอย่างไร
ตัวอย่าง: มีเซลล์จำนวนหนึ่งแสดงรายการผลิตภัณฑ์เครื่องมือและผู้ใช้ตอนนี้คุณต้องการค้นหาเครื่องมือที่ใช้บ่อยที่สุดของแต่ละผลิตภัณฑ์ โปรดใช้สูตรด้านล่างในเซลล์ G3:
| =INDEX($C$3:$C$12,MODE(IF($B$3:$B$12=F3,MATCH($C$3:$C$12,$C$3:$C$12,0)))) |
ข่าวประชา Shift + Ctrl + Enter คีย์เข้าด้วยกันเพื่อให้ได้ผลลัพธ์ที่ถูกต้อง จากนั้นลากที่จับเติมลงเพื่อใช้สูตรนี้
คำอธิบาย
MATCH($C$3:$C$12,$C$3:$C$12,0): MATCH ฟังก์ชันส่งคืนตำแหน่งของ lookup_value ในแถวหรือคอลัมน์ ที่นี่สูตรจะส่งคืนผลลัพธ์อาร์เรย์ {1;2;3;4;2;1;7;8;9;7}ซึ่งจะค้นหาตำแหน่งของแต่ละข้อความในช่วง $ C $ 3: $ C $ 12
IF($B$3:$B$12=F3,MATCH($C$3:$C$12,$C$3:$C$12,0)): IF ฟังก์ชันถูกใช้เพื่อตั้งเงื่อนไข ที่นี่สูตรนี้ถูกมองว่าเป็น IF($B$3:$B$12=”KTE”,{1;2;3;4;2;1;7;8;9;7}) และผลลัพธ์ของอาร์เรย์จะส่งกลับ ={1;FALSE;3;FALSE;FALSE;1;FALSE;FALSE;9;FALSE}.
MODE(IF($B$3:$B$12=F3,MATCH($C$3:$C$12,$C$3:$C$12,0))): ฟังก์ชัน MODE ค้นหาข้อความที่พบบ่อยที่สุดในช่วง ที่นี่สูตรนี้จะค้นหาหมายเลขที่พบบ่อยที่สุดในผลลัพธ์อาร์เรย์ของฟังก์ชัน IF ซึ่งสามารถมองเห็นได้ MODE({1;FALSE;3;FALSE;FALSE;1;FALSE;FALSE;9;FALSE}) และส่งคืน 1.
INDEX function: ดัชนี ฟังก์ชันส่งคืนค่าในตารางหรืออาร์เรย์ตามตำแหน่งที่กำหนด นี่คือสูตร INDEX($C$3:$C$12,MODE(IF($B$3:$B$12=F3,MATCH($C$3:$C$12,$C$3:$C$12,0)))) จะหดลงเป็น INDEX($C$3:$C$12,1).
หมายเหตุ
หากมีข้อความที่ใช้บ่อยที่สุดสองข้อความขึ้นไปสูตรจะส่งคืนค่าที่ปรากฏในตอนแรก
ไฟล์ตัวอย่าง
คลิกเพื่อดาวน์โหลดไฟล์ตัวอย่าง
สูตรสัมพัทธ์
- ตรวจสอบว่าเซลล์มีข้อความเฉพาะหรือไม่
หากต้องการตรวจสอบว่าเซลล์มีข้อความบางข้อความในช่วง A แต่ไม่มีข้อความในช่วง B หรือไม่คุณสามารถใช้สูตรอาร์เรย์ที่รวมฟังก์ชัน COUNT, SEARCH และ AND ใน Excel - ตรวจสอบว่าเซลล์มีค่าใดค่าหนึ่งจากหลายค่า แต่ไม่รวมค่าอื่น ๆ
บทช่วยสอนนี้จะให้สูตรเพื่อจัดการงานอย่างรวดเร็วซึ่งตรวจสอบว่าเซลล์มีสิ่งใดสิ่งหนึ่ง แต่ไม่รวมค่าอื่น ๆ ใน Excel และอธิบายอาร์กิวเมนต์ของสูตร - ตรวจสอบว่าเซลล์มีสิ่งใดสิ่งหนึ่งหรือไม่
สมมติว่าใน Excel มีรายการค่าในคอลัมน์ E คุณต้องการตรวจสอบว่าเซลล์ในคอลัมน์ B มีค่าทั้งหมดในคอลัมน์ E หรือไม่และส่งคืนค่า TRUE หรือ FALSE - ตรวจสอบว่าเซลล์มีหมายเลขหรือไม่
บางครั้งคุณอาจต้องการตรวจสอบว่าเซลล์มีอักขระตัวเลขหรือไม่ บทช่วยสอนนี้มีสูตรที่จะส่งกลับค่า TRUE หากเซลล์มีตัวเลข FALSE ถ้าเซลล์ไม่มีตัวเลข
เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด
Kutools for Excel - ช่วยให้คุณโดดเด่นจากฝูงชน
Kutools for Excel มีคุณสมบัติมากกว่า 300 รายการ รับรองว่าสิ่งที่คุณต้องการเพียงแค่คลิกเดียว...
Office Tab - เปิดใช้งานการอ่านและแก้ไขแบบแท็บใน Microsoft Office (รวมถึง Excel)
- หนึ่งวินาทีเพื่อสลับไปมาระหว่างเอกสารที่เปิดอยู่มากมาย!
- ลดการคลิกเมาส์หลายร้อยครั้งสำหรับคุณทุกวันบอกลามือเมาส์
- เพิ่มประสิทธิภาพการทำงานของคุณได้ถึง 50% เมื่อดูและแก้ไขเอกสารหลายฉบับ
- นำแท็บที่มีประสิทธิภาพมาสู่ Office (รวมถึง Excel) เช่นเดียวกับ Chrome, Edge และ Firefox
