นับจำนวนเซลล์ไม่เท่ากับหลายค่าใน Excel
ใน Excel คุณอาจได้รับจำนวนเซลล์ที่ไม่เท่ากับค่าใดค่าหนึ่งได้ง่ายๆ โดยใช้ฟังก์ชัน COUNTIF แต่คุณเคยลองนับเซลล์ที่ไม่เท่ากับหลายค่าหรือไม่ ตัวอย่างเช่น ฉันต้องการรับจำนวนรวมของผลิตภัณฑ์ในคอลัมน์ A แต่ไม่รวมรายการเฉพาะใน C4:C6 ตามภาพหน้าจอด้านล่าง บทความนี้ผมจะแนะนำสูตรบางอย่างสำหรับการแก้ปัญหานี้ใน Excel
นับจำนวนเซลล์ไม่เท่ากับหลายค่าด้วยสูตร1
ในการนับเซลล์ที่ไม่เท่ากับค่าที่ระบุจำนวนมาก คุณสามารถใช้ฟังก์ชัน MATCH, ISNA และ SUMPRODUCT ร่วมกัน ไวยากรณ์ทั่วไปคือ:
= SUMPRODUCT ( -- ( ISNA ( MATCH ( range, exclude_values, 0)))
- range: รายการเซลล์ที่คุณต้องการนับ
- exclude_values: ค่าที่จะละเว้นจากรายการช่วง
1. ป้อนหรือคัดลอกสูตรด้านล่างลงในเซลล์ว่างที่คุณต้องการให้ผลลัพธ์ออกมา:
=SUMPRODUCT(--(ISNA(MATCH(A2:A13,C2:C4,0))))
2. จากนั้นกด เข้าสู่ คีย์เพื่อให้ได้ผลลัพธ์ที่ถูกต้องดูภาพหน้าจอ:
คำอธิบายของสูตร:
- ตรง(A2:A13,C2:C4,0): ฟังก์ชัน MATCH นี้ส่งคืนตำแหน่งของแต่ละค่าที่ตรงกันเป็นตัวเลข และส่งกลับค่าความผิดพลาด #N/A โดยที่ไม่พบ หมายความว่า เมื่อค่าใน A2:A13 เท่ากับค่าใน C2:C4 จะส่งกลับตัวเลข มิฉะนั้น จะส่งกลับข้อผิดพลาด #NA ผลลัพธ์ที่ได้คือ: {1;#N/A;#N/A;1;3;2;#N/A; #N/A;3;#N/A; #ไม่มี; #ไม่มี;}.
- ISNA(MATCH(A2:A13,C2:C4,0))= ISNA({1;#N/A;#N/A;1;3;2;#N/A; #N/A;3;#N/A; #N/A; #N/A;}): ฟังก์ชัน ISNA นี้บังคับให้ค่าความผิดพลาดกลายเป็น TRUE และตัวเลขจะกลายเป็น FASLE ดังนั้นคุณจะได้รับสิ่งนี้: {FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE} .
- --(ISNA(MATCH(A2:A13,C2:C4,0))))=--({FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE}): ตัวดำเนินการลบคู่ -- แปลง TRUE ทั้งหมดเป็น 1s และ FALSE เป็น 0s ดังนี้: SUMPRODUCT(--(ISNA(MATCH(A2:A13,C2:C4,0))))=SUMPRODUCT({0;1;1 ;0;0;0;1;1;0;1;1;1;}).
- จากนั้นฟังก์ชัน SUMPRODUCT จะใช้เพื่อรวมตัวเลขในอาร์เรย์และจะได้ผลลัพธ์:7
นับจำนวนเซลล์ไม่เท่ากับหลายค่าด้วยสูตร2
ในการแก้ปัญหานี้ใน Excel คุณสามารถใช้การรวมกันของฟังก์ชัน COUNTA, SUMPRODUCT และ COUNTIF ได้ ไวยากรณ์ทั่วไปคือ:
=COUNTA(range)-SUMPRODUCT(COUNTIF(range,exclude_values))
- range: รายการเซลล์ที่คุณต้องการนับ
- exclude_values: ค่าที่จะละเว้นจากรายการช่วง
1. โปรดป้อนหรือคัดลอกสูตรด้านล่างลงในเซลล์ว่างเพื่อใส่ผลลัพธ์:
=COUNTA(A2:A13)-SUMPRODUCT(COUNTIF(A2:A13,C2:C4))
2. จากนั้นกด เข้าสู่ คีย์เพื่อให้ได้ผลลัพธ์ดังภาพต่อไปนี้:
คำอธิบายของสูตร:
- นับ(A2:A13): ฟังก์ชัน COUNTA นี้จะคืนค่าจำนวนเซลล์ที่ไม่ว่างทั้งหมดในช่วง A2:A13 และจะได้ผลลัพธ์:12
- นับ(A2:A13,C2:C4): ฟังก์ชัน COUNTIF นี้นับเซลล์ในช่วง A2:A13 ที่เท่ากับเกณฑ์ใน C2:C4 ดังนั้น คุณจะได้ผลลัพธ์นี้: {2;1;2} ซึ่งหมายความว่า Apple ปรากฏขึ้นสองครั้ง องุ่นปรากฏขึ้นหนึ่งครั้ง และมะนาวปรากฏขึ้น สองครั้ง.
- SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=SUMPRODUCT({2;1;2}): ฟังก์ชัน SUMPRODUCT นี้จะรวมรายการทั้งหมดในอาร์เรย์และส่งกลับผลลัพธ์สุดท้าย:5
- COUNTA(A2:A13)-SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=12-5 :ผลลัพธ์ (5) ของฟังก์ชัน SUMPRODUCT จะถูกลบออกจากจำนวนเซลล์ที่ไม่ว่างเปล่าทั้งหมด (12) เพื่อให้ได้ผลลัพธ์สุดท้ายที่คุณต้องการ
หมายเหตุ: สูตรทั้งสองข้างต้นไม่คำนึงถึงขนาดตัวพิมพ์
ฟังก์ชันสัมพัทธ์ที่ใช้:
- MATCH:
- ฟังก์ชัน MATCH จะค้นหาค่าเฉพาะในช่วงของเซลล์ และส่งกลับตำแหน่งสัมพัทธ์ของค่านี้
- อิสนา:
- ฟังก์ชัน ISNA ใช้เพื่อตรวจสอบว่าสูตรส่งกลับข้อผิดพลาด #N/A หรือไม่
- SUMPRODUCT:
- ฟังก์ชัน SUMPRODUCT สามารถใช้เพื่อคูณคอลัมน์หรืออาร์เรย์ตั้งแต่สองคอลัมน์ขึ้นไปเข้าด้วยกันแล้วรับผลรวมของผลิตภัณฑ์
- COUNTIF:
- ฟังก์ชัน COUNTIF เป็นฟังก์ชันทางสถิติใน Excel ซึ่งใช้เพื่อนับจำนวนเซลล์ที่ตรงตามเกณฑ์
- นับ:
- ฟังก์ชัน COUNTA ส่งกลับจำนวนเซลล์ยกเว้นเซลล์ว่าง
บทความเพิ่มเติม:
- นับจำนวนเซลล์เท่ากับหนึ่งในหลายค่าใน Excel
- สมมติว่าฉันมีรายการผลิตภัณฑ์ในคอลัมน์ A ตอนนี้ฉันต้องการรับจำนวนรวมของผลิตภัณฑ์เฉพาะ Apple, Grape และ Lemon ซึ่งระบุไว้ในช่วง C4:C6 จากคอลัมน์ A ดังที่แสดงด้านล่าง โดยปกติ ใน Excel ฟังก์ชัน COUNTIF และ COUNTIFS แบบธรรมดาจะไม่ทำงานในสถานการณ์นี้ บทความนี้ผมจะพูดถึงวิธีแก้ปัญหานี้อย่างรวดเร็วและง่ายดายด้วยการรวมกันของฟังก์ชัน SUMPRODUCT และ COUNTIF
- นับจำนวนเซลล์ระหว่างสองค่า / วันที่ใน Excel
- คุณเคยพยายามรับหรือนับจำนวนเซลล์ระหว่างสองตัวเลขหรือวันที่ใน Excel ดังที่แสดงไว้ด้านล่างนี้หรือไม่? บทความนี้จะพูดถึงสูตรที่มีประโยชน์สำหรับจัดการกับมัน
- นับจำนวนเซลล์ที่ไม่อยู่ระหว่างสองตัวเลขที่ระบุใน Excel
- การนับจำนวนเซลล์ระหว่างตัวเลขสองตัวนั้นเป็นงานทั่วไปสำหรับเราใน Excel แต่ในบางกรณี คุณอาจต้องการนับเซลล์ที่ไม่ใช่ระหว่างตัวเลขสองตัวที่กำหนด ตัวอย่างเช่น ฉันมีรายการสินค้าที่มียอดขายตั้งแต่วันจันทร์ถึงวันอาทิตย์ ตอนนี้ ฉันต้องได้รับจำนวนเซลล์ซึ่งไม่ได้อยู่ระหว่างตัวเลขที่ต่ำและสูงตามที่แสดงด้านล่าง บทความนี้ผมจะแนะนำสูตรบางอย่างสำหรับจัดการกับงานนี้ใน Excel
เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด
Kutools สำหรับ Excel - ช่วยให้คุณโดดเด่นจากฝูงชน
Kutools สำหรับ Excel มีคุณสมบัติมากกว่า 300 รายการ รับรองว่าสิ่งที่คุณต้องการเพียงแค่คลิกเดียว...
แท็บ Office - เปิดใช้งานการอ่านแบบแท็บและการแก้ไขใน Microsoft Office (รวม Excel)
- หนึ่งวินาทีเพื่อสลับไปมาระหว่างเอกสารที่เปิดอยู่มากมาย!
- ลดการคลิกเมาส์หลายร้อยครั้งสำหรับคุณทุกวันบอกลามือเมาส์
- เพิ่มประสิทธิภาพการทำงานของคุณได้ถึง 50% เมื่อดูและแก้ไขเอกสารหลายฉบับ
- นำแท็บที่มีประสิทธิภาพมาสู่ Office (รวมถึง Excel) เช่นเดียวกับ Chrome, Edge และ Firefox
