นับค่าที่ไม่ซ้ำด้วยเกณฑ์ใน Excel

ในการนับเฉพาะค่าที่ไม่ซ้ำตามเกณฑ์ที่ระบุในคอลัมน์อื่น คุณสามารถใช้สูตรอาร์เรย์โดยยึดตามฟังก์ชัน SUM, FREQUENCY, MATCH และ ROW คำแนะนำทีละขั้นตอนนี้จะช่วยให้คุณใช้สูตรนี้ได้อย่างเต็มที่

วิธีการนับค่าที่ไม่ซ้ำกับเกณฑ์ใน Excel?

ตามตารางสินค้าด้านล่างที่แสดง มีสินค้าซ้ำกันบางรายการขายจากร้านเดียวกันในวันที่ต่างกัน ตอนนี้ ฉันต้องการรับจำนวนเฉพาะของผลิตภัณฑ์ที่ขายจากร้าน A คุณสามารถใช้สูตรด้านล่าง

สูตรทั่วไป

{=SUM(--(FREQUENCY(IF(range=criteria,MATCH(vals,vals,0)),ROW(vals)-ROW(vals.firstcell)+1)>0))}

ข้อโต้แย้ง

พิสัย: ช่วงของเซลล์มีค่าที่เทียบกับเกณฑ์

เกณฑ์: เกณฑ์ที่คุณต้องการนับค่าที่ไม่ซ้ำตาม;

เพลงเต้นรำ: ช่วงของเซลล์ที่คุณต้องการนับค่าที่ไม่ซ้ำ

Vals.firstcell: เซลล์แรกของช่วงที่คุณต้องการนับค่าที่ไม่ซ้ำ

หมายเหตุ: ต้องป้อนสูตรนี้เป็นสูตรอาร์เรย์ หลังจากใช้สูตรแล้ว หากมีวงเล็บปีกกาล้อมรอบสูตร สูตรอาร์เรย์จะถูกสร้างขึ้นสำเร็จ

จะใช้สูตรเหล่านี้ได้อย่างไร?

1. เลือกเซลล์ว่างเพื่อวางผลลัพธ์

2. ใส่สูตรด้านล่างลงไปจากนั้นกดปุ่ม Ctrl + เปลี่ยน + เข้าสู่ คีย์พร้อมกันเพื่อให้ได้ผลลัพธ์

=SUM(--(FREQUENCY(IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)),ROW(D3:D16)-ROW(D3)+1)>0))

หมายเหตุ: ในสูตรนี้ E3:E16 คือช่วงที่มีค่าที่เทียบกับเกณฑ์ H3 มีเกณฑ์ D3:D16 คือช่วงที่มีค่าที่ไม่ซ้ำกันที่คุณต้องการนับ และ D3 เป็นเซลล์แรกของ D3:D16 คุณสามารถเปลี่ยนได้ตามที่คุณต้องการ

สูตรนี้ทำงานอย่างไร?

{=SUM(--(FREQUENCY(IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)),ROW(D3:D16)-ROW(D3)+1)>0))}

IF(E3:E16=H3,MATCH(D3:D16,D3:D16,0)):

1) E3:E16=H3: ให้ตรวจสอบว่ามีค่า A อยู่ในช่วง E3:E16 หรือไม่ และคืนค่า TRUE หากพบ คืนค่า FALSE หากไม่ใช่ คุณจะได้รับอาร์เรย์เช่นนี้ {TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;}

2) แมตช์(D3:D16,D3:D16,0): ฟังก์ชัน MATCH รับตำแหน่งแรกของแต่ละรายการในช่วง D3:D16 และส่งคืนอาร์เรย์เช่นนี้ {1;2;3;2;1;1;3;2;1;1;1;2;3; 2}.

IF({TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;},{1;2;3;2;1;1;3;2;1;1;1;2;3;2}): ตอนนี้สำหรับค่า TRUE แต่ละค่าในอาร์เรย์ 1 เราจะได้รับตำแหน่งที่สอดคล้องกันในอาร์เรย์ 2 และสำหรับ FALSE เราจะได้รับ FALSE ที่นี่คุณจะได้รับอาร์เรย์ใหม่เป็น {1;FALSE;FALSE;2;FALSE;FALSE;3;FALSE;FALSE;1;FALSE;FALSE;3;FALSE}
แถว(D3:D16)-แถว(D3)+1: ที่นี่ ฟังก์ชัน ROW จะคืนค่าหมายเลขแถวของการอ้างอิง D3:D16 และ D3 และคุณจะได้รับ {3;4;5;6;7;8;9;10;11;12;13;14;15;16} -{3}+1.
ตัวเลขแต่ละตัวในอาร์เรย์จะลบเลข 3 จากนั้นบวก 1 และสุดท้ายจะคืนค่า {1;2;3;4;5;6;7;8;9;10;11;12;13;14}
FREQUENCY({1;FALSE;FALSE;2;FALSE;FALSE;3;FALSE;FALSE;1;FALSE;FALSE;3;FALSE},{1;2;3;4;5;6;7;8;9;10;11;12;13;14}): ที่นี่ฟังก์ชัน FREQUENCY จะคืนค่าความถี่ของแต่ละตัวเลขในอาร์เรย์ที่กำหนด: {2;1;2;0;0;0;0;0;0;0;0;0;0;0}
=SUM(--({2;1;2;0;0;0;0;0;0;0;0;0;0;0}>0)):

1) {2;1;2;0;0;0;0;0;0;0;0;0;0;0}>0: ตัวเลขแต่ละตัวในอาร์เรย์เปรียบเทียบกับ 0 และส่งกลับ TRUE ถ้ามากกว่า 0 มิฉะนั้น จะส่งกลับ FALSE และคุณจะได้อาร์เรย์ TRUE FALSE เช่นนี้ {TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE};

2) --{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}}: เครื่องหมายลบทั้งสองนี้แปลง "TRUE" เป็น 1 และ "FALSE" เป็น 0 ที่นี่คุณจะได้รับอาร์เรย์ใหม่เป็น{1;1;1;0;0;0;0;0;0;0;0;0 ;0;0}.

3) SUM{1;1;1;0;0;0;0;0;0;0;0;0;0;0}: ฟังก์ชัน SUM จะรวมตัวเลขทั้งหมดในอาร์เรย์และส่งกลับผลลัพธ์สุดท้ายเป็น 3

ฟังก์ชันที่เกี่ยวข้อง

ฟังก์ชัน Excel SUM
ฟังก์ชัน Excel SUM เพิ่มค่า

ฟังก์ชัน Excel FREQUENCY
ฟังก์ชัน Excel FREQUENCY จะคำนวณความถี่ที่ค่าต่างๆ เกิดขึ้นภายในช่วงของค่า แล้วส่งกลับอาร์เรย์แนวตั้งของตัวเลข

ฟังก์ชัน Excel IF
ฟังก์ชัน Excel IF ทำการทดสอบทางตรรกะอย่างง่ายซึ่งขึ้นอยู่กับผลการเปรียบเทียบและจะส่งกลับค่าหนึ่งค่าหากผลลัพธ์เป็น TRUE หรือค่าอื่นหากผลลัพธ์เป็น FALSE

ฟังก์ชัน Excel MATCH
ฟังก์ชัน Excel MATCH จะค้นหาค่าเฉพาะในช่วงของเซลล์ และส่งกลับตำแหน่งสัมพัทธ์ของค่านี้

ฟังก์ชัน Excel ROW
ฟังก์ชัน Excel ROW จะคืนค่าหมายเลขแถวของข้อมูลอ้างอิง

สูตรที่เกี่ยวข้อง

นับจำนวนแถวที่มองเห็นได้ในรายการที่ถูกกรอง
บทช่วยสอนนี้จะอธิบายวิธีการนับจำนวนแถวที่มองเห็นได้ในรายการที่กรองใน Excel ด้วยฟังก์ชัน SUBTOTAL

นับค่าที่ไม่ซ้ำในช่วง
บทช่วยสอนนี้จะอธิบายวิธีการนับเฉพาะค่าที่ไม่ซ้ำระหว่างรายการที่ซ้ำกันในรายการใน Excel ด้วยสูตรที่ระบุ

นับแถวที่มองเห็นได้ด้วยเกณฑ์
บทแนะนำนี้มีขั้นตอนโดยละเอียดเพื่อช่วยให้คุณนับแถวที่มองเห็นได้ด้วยเกณฑ์

ใช้ COUNTIF กับช่วงที่ไม่ต่อเนื่องกัน
คำแนะนำทีละขั้นตอนนี้สาธิตวิธีใช้ฟังก์ชัน countif บนช่วงที่ไม่ต่อเนื่องกันใน Excel

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

Kutools สำหรับ Excel - ช่วยให้คุณโดดเด่นจากฝูงชน

คุณต้องการทำงานประจำวันของคุณให้เสร็จอย่างรวดเร็วและสมบูรณ์แบบหรือไม่? Kutools สำหรับ Excel นำคุณสมบัติขั้นสูงที่มีประสิทธิภาพ 300 รายการ (รวมสมุดงาน, รวมตามสี, แยกเนื้อหาของเซลล์, แปลงวันที่และอื่น ๆ ... ) และประหยัดเวลาให้คุณ 80%

ออกแบบมาสำหรับสถานการณ์การทำงาน 1500 สถานการณ์ ช่วยคุณแก้ปัญหา Excel 80%
ลดการคลิกแป้นพิมพ์และเมาส์หลายพันครั้งต่อวันช่วยบรรเทาอาการเมื่อยล้าของดวงตาและมือ
เป็นผู้เชี่ยวชาญด้าน Excel ใน 3 นาที ไม่จำเป็นต้องจำสูตรและรหัส VBA ที่เจ็บปวดอีกต่อไป
ทดลองใช้ฟรี 30 วันไม่ จำกัด รับประกันคืนเงิน 60 วัน อัปเกรดและสนับสนุนฟรี 2 ปี

Ribbon ของ Excel (ติดตั้ง Kutools for Excel)

อ่านเพิ่มเติม ดาวน์โหลด

แท็บ Office - เปิดใช้งานการอ่านแบบแท็บและการแก้ไขใน Microsoft Office (รวม Excel)

หนึ่งวินาทีเพื่อสลับไปมาระหว่างเอกสารที่เปิดอยู่มากมาย!
ลดการคลิกเมาส์หลายร้อยครั้งสำหรับคุณทุกวันบอกลามือเมาส์
เพิ่มประสิทธิภาพการทำงานของคุณได้ถึง 50% เมื่อดูและแก้ไขเอกสารหลายฉบับ
นำแท็บที่มีประสิทธิภาพมาสู่ Office (รวมถึง Excel) เช่นเดียวกับ Chrome, Firefox และ Internet Explorer ใหม่