Countifs ที่มีตรรกะ OR สำหรับหลายเกณฑ์ใน Excel
โดยปกติ คุณสามารถใช้ฟังก์ชัน COUNTIFS เพื่อนับเซลล์ตามเงื่อนไขอย่างน้อยหนึ่งเงื่อนไขด้วยตรรกะ AND ใน Excel คุณเคยประสบกับสถานการณ์ที่คุณต้องนับค่ามากกว่าหนึ่งค่าจากคอลัมน์เดียวหรือช่วงของเซลล์หรือไม่? นี่หมายถึงการนับด้วยหลายเงื่อนไขและตรรกะ OR ในกรณีนี้ คุณสามารถใช้ฟังก์ชัน SUM และ COUNTIFS ร่วมกัน หรือใช้ฟังก์ชัน SUMPRODUCT
นับเซลล์ที่มีเงื่อนไข OR ใน Excel
ตัวอย่างเช่น ฉันมีช่วงของข้อมูลตามที่แสดงไว้ด้านล่างนี้ ตอนนี้ ฉันต้องการนับจำนวนผลิตภัณฑ์ที่เป็น "ดินสอ" หรือ "ไม้บรรทัด" ในที่นี้ ฉันจะพูดถึงสองสูตรสำหรับการแก้ปัญหานี้ใน Excel
นับเซลล์ที่มีเงื่อนไข OR โดยใช้ฟังก์ชัน SUM และ COUNTIFS
ใน Excel ในการนับด้วยเงื่อนไข OR หลายรายการ คุณสามารถใช้ฟังก์ชัน SUM และ COUNTIFS ที่มีค่าคงที่อาร์เรย์ได้ ไวยากรณ์ทั่วไปคือ:
- range: ช่วงข้อมูลประกอบด้วยเกณฑ์ที่คุณนับเซลล์
- criterion1, criterion2, criterion3…: เงื่อนไขที่คุณต้องการนับเซลล์ตาม
การนับจำนวนสินค้าที่เป็น “ดินสอ” หรือ “ไม้บรรทัด” ให้คัดลอกหรือใส่สูตรด้านล่างลงในช่องว่าง แล้วกด เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์:
คำอธิบายของสูตร:
=SUM(COUNTIFS(B2:B13,{"ดินสอ","ไม้บรรทัด"}))
- {"ดินสอ","ไม้บรรทัด"}: อันดับแรก คุณควรจัดแพ็กเกจเงื่อนไขทั้งหมดในค่าคงที่อาร์เรย์ดังนี้: {"Pencil","Ruler"} คั่นรายการด้วยเครื่องหมายจุลภาค
- COUNTIFS(B2:B13,{"ดินสอ","ไม้บรรทัด"}): ฟังก์ชัน COUNTIFS นี้จะนับจำนวนเฉพาะสำหรับ "ดินสอ" และ "ไม้บรรทัด" และคุณจะได้ผลลัพธ์ดังนี้: {2,3}
- SUM(COUNTIFS(B2:B13,{"Pencil","Ruler"}))=SUM({2,3}): สุดท้ายนี้ ฟังก์ชัน SUM จะรวมรายการทั้งหมดในอาร์เรย์และส่งกลับผลลัพธ์: 5
เคล็ดลับ: คุณสามารถใช้การอ้างอิงเซลล์สำหรับเกณฑ์ได้ โปรดใช้สูตรอาร์เรย์ด้านล่าง จากนั้นกด Ctrl + Shift + Enter คีย์ร่วมกันเพื่อให้ได้ผลลัพธ์ที่ถูกต้อง:
นับเซลล์ที่มีเงื่อนไข OR โดยใช้ฟังก์ชัน SUMPRODUCT
นี่คือสูตรอื่นที่สร้างขึ้นโดยฟังก์ชัน SUMPRODUCT ที่สามารถช่วยในการนับเซลล์ด้วยตรรกะ OR ได้เช่นกัน ไวยากรณ์ทั่วไปคือ:
- range: ช่วงข้อมูลประกอบด้วยเกณฑ์ที่คุณนับเซลล์
- criterion1, criterion2, criterion3…: เงื่อนไขที่คุณต้องการนับเซลล์ตาม
โปรดคัดลอกหรือป้อนสูตรต่อไปนี้ลงในเซลล์ว่าง แล้วกด เข้าสู่ คีย์เพื่อส่งคืนผลลัพธ์:
คำอธิบายของสูตร:
=SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))
- B2:B13={"ดินสอ","ไม้บรรทัด"}: นิพจน์นี้เปรียบเทียบแต่ละเกณฑ์ "ดินสอ" "ไม้บรรทัด" กับช่วงเซลล์ B2:B13 หากตรงตามเกณฑ์ จะส่งกลับ TRUE ไม่เช่นนั้น FALSE จะแสดงขึ้น คุณจะได้ผลลัพธ์ดังนี้: {TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE;TRUE,FALSE ;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE;FALSE,FALSE;FALSE,TRUE;FALSE,FALSE}
- 1*(B2:B13={"ดินสอ","ไม้บรรทัด"}): การคูณแปลงค่าตรรกะ – TRUE และ FALSE เป็น 1 และ 0 ดังนั้นจะได้ผลลัพธ์ดังนี้: {1,0;0,0;0,0;0,1;0,0 ,1,0;0,0;0,1;0,0}.
- SUMPRODUCT(1*(B2:B13={"Pencil","Ruler"}))= SUMPRODUCT({1,0;0,0;0,0;0,1;0,0;1,0;0,0;0,1;0,0;0,0;0,1;0,0}): ในที่สุด ฟังก์ชัน SUMPRODUCT จะรวมตัวเลขทั้งหมดในอาร์เรย์เพื่อให้ได้ผลลัพธ์: 5.
นับเซลล์ที่มีเงื่อนไข OR หลายชุดใน Excel
บางครั้ง คุณอาจต้องนับเซลล์ที่มีเงื่อนไข OR ตั้งแต่สองชุดขึ้นไป ในกรณีนี้ คุณสามารถใช้ SUM และ COUNTIFS กับค่าคงที่อาร์เรย์ หรือ SUMPRODUCT ที่มีฟังก์ชัน ISNUMBER MATCH
นับเซลล์ที่มีเงื่อนไข OR สองชุดโดยใช้ฟังก์ชัน SUM และ COUNTIFS
ในการจัดการกับเกณฑ์ OR เพียงสองชุด คุณเพียงแค่เพิ่มค่าคงที่อาร์เรย์อื่นลงในสูตร COUNTIFS
ตัวอย่างเช่น ฉันมีช่วงข้อมูลตามภาพหน้าจอด้านล่าง ตอนนี้ฉันต้องการนับคนที่สั่ง "ดินสอ" หรือ "ไม้บรรทัด" และจำนวนเงินคือ <100 หรือ >200
โปรดป้อนหรือคัดลอกสูตรต่อไปนี้ลงในเซลล์ จากนั้นกด เข้าสู่ กุญแจสำคัญในการรับผลลัพธ์ดูภาพหน้าจอ:
หมายเหตุ: ในสูตร คุณควรใช้เครื่องหมายอัฒภาคสำหรับค่าคงที่อาร์เรย์ที่สอง ซึ่งจะสร้างอาร์เรย์แนวตั้ง
นับเซลล์ที่มีเงื่อนไข OR หลายชุดโดยใช้ฟังก์ชัน SUMPRODUCT
สูตรข้างต้นใช้ได้กับเกณฑ์ OR สองชุดเท่านั้น ถ้าคุณต้องการนับด้วยเกณฑ์เพิ่มเติม สูตร SUMPRODUCT ที่ซับซ้อนพร้อมฟังก์ชัน ISNUMBER MATCH สามารถช่วยคุณได้
นำข้อมูลด้านล่างเช่นนับผู้ที่สั่ง "ดินสอ" หรือ "ไม้บรรทัด" และสถานะเป็น "จัดส่ง" หรือ "ระหว่างทาง" และลงนามโดย "บ๊อบ" หรือ "Eko" คุณควรใช้สูตรที่ซับซ้อน .
โปรดคัดลอกหรือป้อนสูตรด้านล่างลงในเซลล์ว่าง จากนั้นกด เข้าสู่ กุญแจสำคัญในการคำนวณ ดูภาพหน้าจอ:
คำอธิบายของสูตร:
=SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0)))
ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0)):
- MATCH(B2:B13,{"ดินสอ","ไม้บรรทัด"},0): ฟังก์ชัน MATCH นี้ใช้เพื่อเปรียบเทียบแต่ละเซลล์ในช่วง B2:B13 กับค่าคงที่อาร์เรย์ที่สอดคล้องกัน หากพบการจับคู่ จะส่งกลับตำแหน่งสัมพัทธ์ของค่าในอาร์เรย์ มิฉะนั้น จะแสดงค่าความผิดพลาด ดังนั้น คุณจะได้รับรายการอาร์เรย์ดังนี้: {1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;# ไม่มี}.
- ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))= ISNUMBER({1;#N/A;#N/A;2;#N/A;1;#N/A;2;1;#N/A;2;#N/A}):ฟังก์ชัน ISNUMBER จะแปลงตัวเลขเป็น TRUE และค่าความผิดพลาดเป็น FALSE ดังนี้: {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE;FALSE}
ตรรกะข้างต้นยังสามารถนำไปใช้กับนิพจน์ ISNUMBER ที่สองและสามได้อีกด้วย
SUMPRODUCT(ISNUMBER(MATCH(B2:B13,{"Pencil","Ruler"},0))*ISNUMBER(MATCH(C2:C13,{"Delivered","In transit"},0))*ISNUMBER(MATCH(D2:D13,{"Bob","Eko"},0))):
- จากนั้น อาร์เรย์ทั้งสามนี้จะถูกคูณเข้าด้วยกันภายใน SUMPRODUCT ซึ่งจะแปลงค่า TRUE และ FALSE เป็น 1 และ 0 โดยอัตโนมัติ โดยเป็นส่วนหนึ่งของการดำเนินการทางคณิตศาสตร์ดังนี้: SUMPRODUCT({1;0;0;1;0;1;0;1; 1;0;1;0}*{1;1;0;0;1;1;0;1;1;0;1;1} *{1;0;0;0;0;0;0; 1;0;0;1;0})=SUMPRODUCT({1;0;0;0;0;0;0;1;0;0;1;0}).
- ในที่สุด ฟังก์ชัน SUMPRODUCT จะรวมตัวเลขทั้งหมดในอาร์เรย์เพื่อให้ได้ผลลัพธ์: 3
ฟังก์ชันสัมพัทธ์ที่ใช้:
- ผลรวม:
- ฟังก์ชัน Excel SUM ส่งกลับผลรวมของค่าที่ให้มา
- นับ:
- ฟังก์ชัน COUNTIF เป็นฟังก์ชันทางสถิติใน Excel ซึ่งใช้เพื่อนับจำนวนเซลล์ที่ตรงตามเกณฑ์
- ผลรวม:
- ฟังก์ชัน SUMPRODUCT สามารถใช้เพื่อคูณคอลัมน์หรืออาร์เรย์ตั้งแต่สองคอลัมน์ขึ้นไปเข้าด้วยกันแล้วรับผลรวมของผลิตภัณฑ์
- ISNUMBER:
- ฟังก์ชัน Excel ISNUMBER จะส่งกลับ TRUE เมื่อเซลล์มีตัวเลข และ FALSE หากไม่มี
- จับคู่:
- ฟังก์ชัน Microsoft Excel MATCH จะค้นหาค่าเฉพาะในช่วงของเซลล์และส่งกลับตำแหน่งสัมพัทธ์ของค่านี้
บทความเพิ่มเติม:
- นับค่าตัวเลขที่ไม่ซ้ำตามเกณฑ์
- ในเวิร์กชีต Excel คุณอาจประสบปัญหาที่นับจำนวนค่าตัวเลขที่ไม่ซ้ำตามเงื่อนไขเฉพาะ ตัวอย่างเช่น ฉันจะนับค่า Qty เฉพาะของผลิตภัณฑ์ "เสื้อยืด" จากรายงานที่แสดงภาพหน้าจอด้านล่างได้อย่างไร ในบทความนี้ ผมจะแสดงสูตรบางอย่างเพื่อให้บรรลุงานนี้ใน Excel
- นับเซลล์ที่ตรงกับเกณฑ์ตั้งแต่สองเกณฑ์ขึ้นไป
- ใน Excel ในการนับจำนวนเซลล์ตามเกณฑ์สองหรือหลายเกณฑ์ ฟังก์ชัน COUNTIFS อาจช่วยให้คุณแก้ปัญหานี้ได้อย่างรวดเร็วและง่ายดาย
- นับจำนวนแถวด้วยหลายหรือเกณฑ์
- ในการนับจำนวนแถวที่มีหลายเกณฑ์ในคอลัมน์ต่างๆ ด้วยตรรกะ OR ฟังก์ชัน SUMPRODUCT สามารถช่วยคุณได้ ตัวอย่างเช่น ฉันมีรายงานผลิตภัณฑ์ตามภาพหน้าจอด้านล่าง ตอนนี้ ฉันต้องการนับแถวที่ผลิตภัณฑ์คือ "เสื้อยืด" หรือสีคือ "สีดำ" วิธีจัดการกับงานนี้ใน Excel?
เครื่องมือเพิ่มประสิทธิภาพการทำงานในสำนักงานที่ดีที่สุด
Kutools สำหรับ Excel - ช่วยให้คุณโดดเด่นจากฝูงชน
Kutools สำหรับ Excel มีคุณสมบัติมากกว่า 300 รายการ รับรองว่าสิ่งที่คุณต้องการเพียงแค่คลิกเดียว...
แท็บ Office - เปิดใช้งานการอ่านแบบแท็บและการแก้ไขใน Microsoft Office (รวม Excel)
- หนึ่งวินาทีเพื่อสลับไปมาระหว่างเอกสารที่เปิดอยู่มากมาย!
- ลดการคลิกเมาส์หลายร้อยครั้งสำหรับคุณทุกวันบอกลามือเมาส์
- เพิ่มประสิทธิภาพการทำงานของคุณได้ถึง 50% เมื่อดูและแก้ไขเอกสารหลายฉบับ
- นำแท็บที่มีประสิทธิภาพมาสู่ Office (รวมถึง Excel) เช่นเดียวกับ Chrome, Edge และ Firefox
