ไปยังเนื้อหาหลัก

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

ในเวิร์กชีต Excel คุณอาจประสบปัญหาที่นับจำนวนค่าตัวเลขที่ไม่ซ้ำตามเงื่อนไขเฉพาะ ตัวอย่างเช่น ฉันจะนับค่า Qty เฉพาะของผลิตภัณฑ์ "เสื้อยืด" จากรายงานที่แสดงภาพหน้าจอด้านล่างได้อย่างไร ในบทความนี้ ผมจะแสดงสูตรบางอย่างเพื่อให้บรรลุงานนี้ใน Excel

นับค่าตัวเลขที่ไม่ซ้ำตามเกณฑ์ใน Excel 2019, 2016 และรุ่นก่อนหน้า

ใน Excel 2019 และเวอร์ชันก่อนหน้า คุณสามารถรวมฟังก์ชัน SUM, FREQUENCY และ IF เพื่อสร้างสูตรสำหรับการนับค่าที่ไม่ซ้ำตามเกณฑ์ ไวยากรณ์ทั่วไปคือ:

{=SUM(--(FREQUENCY(IF(criteria_range=criteria,range),range)>0))}
Array formula, should press Ctrl + Shift + Enter keys together.
  • criteria_range: ช่วงของเซลล์ที่ตรงกับเกณฑ์ที่คุณระบุ
  • criteria: เงื่อนไขที่คุณต้องการนับค่าที่ไม่ซ้ำตาม;
  • range: ช่วงของเซลล์ที่มีค่าเฉพาะที่จะนับ

กรุณาใส่สูตรด้านล่างลงในเซลล์ว่าง แล้วกด Ctrl + Shift + Enter คีย์เพื่อให้ได้ผลลัพธ์ที่ถูกต้อง ดูภาพหน้าจอ:

=SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))

คำอธิบายของสูตร:

=SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))

  • IF(A2:A12=E2,C2:C12): ฟังก์ชัน IF นี้จะคืนค่าในคอลัมน์ C หากผลิตภัณฑ์ในคอลัมน์ A คือ "T-shirt" ผลลัพธ์จะเป็นอาร์เรย์ดังนี้: {FALSE;300;500;FALSE;400;FALSE;300;FALSE;FALSE; เท็จ;350}.
  • FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)= FREQUENCY({FALSE;300;500;FALSE;400;FALSE;300;FALSE;FALSE;FALSE;350},{200;300;500;350;400;450;300;550;200;260;350}): ฟังก์ชัน FREQUENCE ใช้เพื่อนับค่าตัวเลขแต่ละรายการในรายการอาร์เรย์ และส่งคืนผลลัพธ์ดังนี้: {0;2;1;1;1;0;0;0;0;0;0;0} .
  • --(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0)=--({0;2;1;1;1;0;0;0;0;0;0;0}>0): ทดสอบว่าแต่ละค่าในอาร์เรย์มากกว่า 0 หรือไม่ และรับผลลัพธ์ดังนี้: {FALSE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE} จากนั้น เครื่องหมายลบคู่จะแปลง TRUE และ FALSE เป็น 1 และ 0 และส่งกลับอาร์เรย์ดังนี้: {0;1;1;1;1;0;0;0;0;0;0;0}
  • SUM(--(FREQUENCY(IF(A2:A12=E2,C2:C12),C2:C12)>0))=SUM({0;1;1;1;1;0;0;0;0;0;0;0}): สุดท้าย ใช้ฟังก์ชัน SUM เพื่อเพิ่มค่าเหล่านี้ และรับจำนวนทั้งหมด: 4

ทิปส์:

ถ้าคุณต้องการนับค่าที่ไม่ซ้ำตามเงื่อนไขมากกว่าหนึ่งเงื่อนไข คุณเพียงแค่เพิ่มเกณฑ์อื่นในสูตรด้วยอักขระ *:

=SUM(--(FREQUENCY(IF((criteria,_range1=criteria1)* (criteria,_range2=criteria2)*…,range),range)>0))

นับค่าตัวเลขที่ไม่ซ้ำตามเกณฑ์ใน Excel 365

ใน Excel 365 การรวมกันของฟังก์ชัน ROWS, UNIQUE และ FILTER สามารถช่วยในการนับค่าตัวเลขที่ไม่ซ้ำตามเกณฑ์ ไวยากรณ์ทั่วไปคือ:

=ROWS(UNIQUE(FILTER(range,criteria_range=criteria)))
  • range: ช่วงของเซลล์ที่มีค่าเฉพาะที่จะนับ
  • criteria_range: ช่วงของเซลล์ที่ตรงกับเกณฑ์ที่คุณระบุ
  • criteria: เงื่อนไขที่คุณต้องการนับค่าที่ไม่ซ้ำตาม;

โปรดคัดลอกหรือป้อนสูตรต่อไปนี้ลงในเซลล์ แล้วกด เข้าสู่ กุญแจสำคัญในการส่งคืนผลลัพธ์ดูภาพหน้าจอ:

=ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))

คำอธิบายของสูตร:

=ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))

  • A2:A12=E2: นิพจน์นี้จะตรวจสอบว่าค่าในเซลล์ E2 มีอยู่ในช่วง A2:A12 หรือไม่ และรับผลลัพธ์นี้: {FALSE;TRUE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}
  • FILTER(C2:C12,A2:A12=E2): ฟังก์ชัน FREQUENCE ใช้เพื่อนับค่าตัวเลขแต่ละรายการในรายการอาร์เรย์ และส่งคืนผลลัพธ์ดังนี้: {0;2;1;1;1;0;0;0;0;0;0;0} .
  • UNIQUE(FILTER(C2:C12,A2:A12=E2))=UNIQUE({300;500;400;300;350}): ในที่นี้ ฟังก์ชัน UNIQUE ใช้เพื่อดึงค่าที่ไม่ซ้ำจากอาร์เรย์รายการเพื่อให้ได้ผลลัพธ์นี้: {300;500;400;350}
  • ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2)))=ROWS({300;500;400;350}): ฟังก์ชัน ROWS จะคืนค่าจำนวนแถวตามช่วงเซลล์หรืออาร์เรย์ ดังนั้น ผลลัพธ์ที่ได้คือ 4

ทิปส์:

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

=IFERROR(ROWS(UNIQUE(FILTER(C2:C12,A2:A12=E2))), 0)

2. ในการนับค่าที่ไม่ซ้ำตามเงื่อนไขมากกว่าหนึ่งเงื่อนไข คุณเพียงแค่เพิ่มเกณฑ์อื่นในสูตรด้วยอักขระ * ดังนี้:

=ROWS(UNIQUE(FILTER(range,(criteria_range1=criteria1)* (criteria_range2=criteria2)*…)))

ฟังก์ชันสัมพัทธ์ที่ใช้:

  • ผลรวม:
  • ฟังก์ชัน Excel SUM ส่งกลับผลรวมของค่าที่ให้มา
  • ความถี่:
  • ฟังก์ชัน FREQUENCY จะคำนวณความถี่ที่ค่าต่างๆ เกิดขึ้นภายในช่วงของค่า แล้วส่งกลับอาร์เรย์แนวตั้งของตัวเลข
  • แถว:
  • ฟังก์ชัน ROWS จะคืนค่าจำนวนแถวในการอ้างอิงหรืออาร์เรย์ที่กำหนด
  • มีเอกลักษณ์:
  • ฟังก์ชัน UNIQUE ส่งกลับรายการค่าที่ไม่ซ้ำในรายการหรือช่วง
  • กรอง:
  • ฟังก์ชัน FILTER ช่วยกรองช่วงข้อมูลตามเกณฑ์ที่คุณกำหนด

บทความเพิ่มเติม:

  • นับค่าตัวเลขหรือวันที่ที่ไม่ซ้ำในคอลัมน์
  • สมมติว่าคุณมีรายการตัวเลขที่ซ้ำกัน ตอนนี้คุณต้องการนับจำนวนค่าที่ไม่ซ้ำหรือค่าที่ปรากฏเพียงครั้งเดียวในรายการดังที่แสดงไว้ด้านล่างนี้ ในบทความนี้ เราจะพูดถึงสูตรที่มีประโยชน์สำหรับการแก้ปัญหานี้ใน Excel อย่างรวดเร็วและง่ายดาย
  • นับการแข่งขันทั้งหมด / ซ้ำระหว่างสองคอลัมน์
  • การเปรียบเทียบข้อมูลสองคอลัมน์และนับการจับคู่หรือรายการที่ซ้ำกันทั้งหมดในสองคอลัมน์อาจเป็นงานทั่วไปสำหรับพวกเราส่วนใหญ่ ตัวอย่างเช่น คุณมีชื่อสองคอลัมน์ บางชื่อปรากฏในคอลัมน์แรกและคอลัมน์ที่สอง ตอนนี้ คุณต้องการนับชื่อที่ตรงกันทั้งหมด (การจับคู่ที่ใดก็ได้ภายในสองคอลัมน์) ระหว่างสองคอลัมน์ดังภาพหน้าจอด้านล่าง บทช่วยสอนนี้ จะแนะนำสูตรบางอย่างเพื่อให้บรรลุเป้าหมายนี้ใน Excel
  • นับจำนวนเซลล์เท่ากับหนึ่งในหลายค่า
  • สมมติว่าฉันมีรายการผลิตภัณฑ์ในคอลัมน์ A ตอนนี้ฉันต้องการรับจำนวนรวมของผลิตภัณฑ์เฉพาะ Apple, Grape และ Lemon ซึ่งระบุไว้ในช่วง C4:C6 จากคอลัมน์ A ดังที่แสดงด้านล่าง โดยปกติ ใน Excel ฟังก์ชัน COUNTIF และ COUNTIFS แบบธรรมดาจะไม่ทำงานในสถานการณ์นี้ บทความนี้ผมจะพูดถึงวิธีแก้ปัญหานี้อย่างรวดเร็วและง่ายดายด้วยการรวมกันของฟังก์ชัน SUMPRODUCT และ COUNTIF

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

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

🤖 Kutools AI ผู้ช่วย: ปฏิวัติการวิเคราะห์ข้อมูลโดยยึดตาม: การดำเนินการที่ชาญฉลาด   |  สร้างรหัส  |  สร้างสูตรที่กำหนดเอง  |  วิเคราะห์ข้อมูลและสร้างแผนภูมิ  |  เรียกใช้ฟังก์ชัน Kutools...
คุณสมบัติยอดนิยม: ค้นหา เน้น หรือระบุรายการที่ซ้ำกัน  |  ลบแถวว่าง  |  รวมคอลัมน์หรือเซลล์โดยไม่สูญเสียข้อมูล  |  รอบโดยไม่มีสูตร ...
ซุปเปอร์วีลุคอัพ: หลายเกณฑ์  |  หลายค่า  |  ข้ามหลายแผ่น  |  การค้นหาที่ไม่ชัดเจน...
โฆษณา รายการแบบหล่นลง: รายการแบบหล่นลงอย่างง่าย  |  รายการแบบหล่นลงขึ้นอยู่กับ  |  เลือกหลายรายการแบบหล่นลง...
ผู้จัดการคอลัมน์: เพิ่มจำนวนคอลัมน์เฉพาะ  |  ย้ายคอลัมน์  |  สลับสถานะการมองเห็นของคอลัมน์ที่ซ่อนอยู่  |  เปรียบเทียบคอลัมน์กับ เลือกเซลล์เดียวกันและต่างกัน ...
คุณสมบัติเด่น: กริดโฟกัส  |  มุมมองการออกแบบ  |  บาร์สูตรใหญ่  |  สมุดงานและตัวจัดการชีต | ห้องสมุดทรัพยากร (ข้อความอัตโนมัติ)  |  เลือกวันที่  |  รวมแผ่นงาน  |  เข้ารหัส/ถอดรหัสเซลล์  |  ส่งอีเมลตามรายการ  |  ซุปเปอร์ฟิลเตอร์  |  ตัวกรองพิเศษ (กรองตัวหนา/ตัวเอียง/ขีดทับ...) ...
ชุดเครื่องมือ 15 อันดับแรก:  12 ข้อความ เครื่องมือ (เพิ่มข้อความ, ลบอักขระ ... )  |  50 + แผนภูมิ ประเภท (แผนภูมิ Gantt ... )  |  40+ ใช้งานได้จริง สูตร (คำนวณอายุตามวันเกิด ... )  |  19 การแทรก เครื่องมือ (ใส่ QR Code, แทรกรูปภาพจากเส้นทาง ... )  |  12 การแปลง เครื่องมือ (ตัวเลขเป็นคำ, การแปลงสกุลเงิน ... )  |  7 ผสานและแยก เครื่องมือ (แถวรวมขั้นสูง, แยกเซลล์ Excel ... )  |  ... และอื่น ๆ

Kutools สำหรับ Excel มีคุณสมบัติมากกว่า 300 รายการ รับรองว่าสิ่งที่คุณต้องการเพียงแค่คลิกเดียว...

รายละเอียด

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

  • หนึ่งวินาทีเพื่อสลับไปมาระหว่างเอกสารที่เปิดอยู่มากมาย!
  • ลดการคลิกเมาส์หลายร้อยครั้งสำหรับคุณทุกวันบอกลามือเมาส์
  • เพิ่มประสิทธิภาพการทำงานของคุณได้ถึง 50% เมื่อดูและแก้ไขเอกสารหลายฉบับ
  • นำแท็บที่มีประสิทธิภาพมาสู่ Office (รวมถึง Excel) เช่นเดียวกับ Chrome, Edge และ Firefox
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations