สมมติว่าเรามีตารางการขายผลไม้ตามภาพด้านล่างนี้และเราจะนับหรือรวมเซลล์สีในคอลัมน์จำนวน ในสถานการณ์นี้เราสามารถกรองคอลัมน์จำนวนตามสีจากนั้นนับหรือรวมเซลล์สีที่กรองแล้วด้วยฟังก์ชัน SUBTOTAL ใน Excel ได้อย่างง่ายดาย

1. เลือกเซลล์ว่างเพื่อเข้าสู่ฟังก์ชัน SUBTOTAL

1. หากต้องการนับเซลล์ทั้งหมดที่มีสีพื้นหลังเดียวกันโปรดป้อนสูตร = SUBTOTAL (102, E2: E20);
2. หากต้องการรวมเซลล์ทั้งหมดที่มีสีพื้นหลังเดียวกันโปรดป้อนสูตร = SUBTOTAL (109, E2: E20);

หมายเหตุ: ในทั้งสองสูตร E2: E20 คือคอลัมน์จำนวนที่มีเซลล์สีและคุณสามารถเปลี่ยนแปลงได้ตามต้องการ

2. เลือกส่วนหัวของตารางแล้วคลิก ข้อมูล > กรอง. ดูภาพหน้าจอ:

3. คลิกไอคอนตัวกรอง  ในเซลล์ส่วนหัวของคอลัมน์จำนวนเงินแล้วคลิก กรองตามสี และสีที่ระบุคุณจะนับตามลำดับ ดูภาพหน้าจอ:

หลังจากกรองแล้วสูตร SUBTOTAL ทั้งสองจะนับและรวมเซลล์สีที่กรองทั้งหมดในคอลัมน์จำนวนโดยอัตโนมัติ ดูภาพหน้าจอ:

หมายเหตุ: วิธีนี้ต้องใช้เซลล์สีที่คุณจะนับหรือผลรวมอยู่ในคอลัมน์เดียวกัน

คลิกเพียงครั้งเดียวเพื่อนับรวมและค่าเฉลี่ยเซลล์สีใน Excel

ด้วยความยอดเยี่ยม นับตามสี คุณลักษณะของ Kutools สำหรับ Excelคุณสามารถนับรวมและเฉลี่ยเซลล์ได้อย่างรวดเร็วด้วยสีเติมหรือสีฟอนต์ที่ระบุด้วยการคลิกเพียงครั้งเดียวใน Excel นอกจากนี้คุณสมบัตินี้ยังค้นหาค่าสูงสุดและต่ำสุดของเซลล์ด้วยสีเติมหรือสีแบบอักษร

ในวิธีนี้เราจะสร้างช่วงที่ตั้งชื่อด้วยฟังก์ชัน GET.CELL รับรหัสสีของเซลล์แล้วนับหรือรวมด้วยรหัสสีใน Excel ได้อย่างง่ายดาย โปรดดำเนินการดังนี้:

1. คลิก สูตร > กำหนดชื่อ. ดูภาพหน้าจอ:

2. ในกล่องโต้ตอบชื่อใหม่โปรดทำตามภาพด้านล่างที่แสดง:
(1) พิมพ์ชื่อในกล่องชื่อ;
(2) ใส่สูตร = GET.CELL (38, Sheet4! $ E2) ในกล่องอ้างอิงถึง (หมายเหตุ: ในสูตร 38 หมายถึงส่งคืนรหัสเซลล์และ Sheet4! $ E2 เป็นเซลล์แรกในคอลัมน์จำนวนยกเว้นส่วนหัวของคอลัมน์ที่คุณต้องเปลี่ยนตามข้อมูลตารางของคุณ)
(3) คลิกไฟล์ OK ปุ่ม

3. ตอนนี้เพิ่มคอลัมน์สีใหม่ลงในตารางเดิม พิมพ์สูตรถัดไป = NumColor แล้วลากจุดจับการป้อนอัตโนมัติเพื่อใช้สูตรกับเซลล์อื่นในคอลัมน์สี ดูภาพหน้าจอ:
หมายเหตุ: ในสูตร จำนวนสี คือช่วงที่ตั้งชื่อที่เราระบุไว้ใน 2 ขั้นตอนแรก คุณต้องเปลี่ยนเป็นชื่อที่คุณตั้งไว้

ตอนนี้รหัสสีของแต่ละเซลล์ในคอลัมน์จำนวนจะส่งกลับในคอลัมน์สี ดูภาพหน้าจอ:

4. คัดลอกและแสดงรายการสีเติมในช่วงว่างในแผ่นงานที่ใช้งานอยู่และพิมพ์สูตรที่อยู่ถัดจากภาพด้านล่างที่แสดง:
A. หากต้องการนับเซลล์ตามสีโปรดป้อนสูตร = COUNTIF ($ F $ 2: $ F $ 20, NumColor);
B. หากต้องการรวมเซลล์ตามสีโปรดป้อนสูตร = SUMIF ($ F $ 2: $ F $ 20, NumColor, $ E $ 2: $ E $ 20).

หมายเหตุ: ในทั้งสองสูตร $ F $ 2: $ F $ 20 คือคอลัมน์สี จำนวนสี คือช่วงที่ระบุชื่อ $ E $ 2: $ E $ 20 คือคอลัมน์จำนวนเงินและคุณสามารถเปลี่ยนแปลงได้ตามต้องการ

ตอนนี้คุณจะเห็นเซลล์ในคอลัมน์จำนวนเงินถูกนับและรวมด้วยสีเติม

สมมติว่าเซลล์สีกระจายอยู่ในช่วงตามภาพด้านล่างที่แสดงทั้งสองวิธีข้างต้นไม่สามารถนับหรือรวมเซลล์สีได้ ที่นี่วิธีการนี้จะแนะนำ VBA เพื่อแก้ปัญหา

1. กด ALT + F11 และจะเปิดไฟล์ Microsoft Visual Basic สำหรับแอปพลิเคชัน หน้าต่าง

2. คลิก สิ่งที่ใส่เข้าไป > โมดูลและวางรหัสต่อไปนี้ในหน้าต่างโมดูล

VBA: นับและรวมเซลล์ตามสีพื้นหลัง:

Function ColorFunction(rColor As Range, rRange As Range, Optional SUM As Boolean)
Dim rCell As Range
Dim lCol As Long
Dim vResult
lCol = rColor.Interior.ColorIndex
If SUM = True Then
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = WorksheetFunction.SUM(rCell, vResult)
End If
Next rCell
Else
For Each rCell In rRange
If rCell.Interior.ColorIndex = lCol Then
vResult = 1 + vResult
End If
Next rCell
End If
ColorFunction = vResult
End Function

3. จากนั้นบันทึกรหัสและใช้สูตรต่อไปนี้:
A. นับเซลล์สี: = ฟังก์ชันสี (A, B: C, FALSE)
B. รวมเซลล์สี: = ฟังก์ชันสี (A, B: C, TRUE)

หมายเหตุ: ในสูตรข้างต้น A คือเซลล์ที่มีสีพื้นหลังเฉพาะที่คุณต้องการคำนวณจำนวนและผลรวมและ B: ค คือช่วงเซลล์ที่คุณต้องการคำนวณจำนวนและผลรวม

4. จับภาพหน้าจอต่อไปนี้เช่นป้อนสูตร= ฟังก์ชันสี (A1, A1: D11, FALSE) เพื่อนับเซลล์สีเหลือง และใช้สูตร = ฟังก์ชันสี (A1, A1: D11, TRUE) เพื่อรวมเซลล์สีเหลือง ดูภาพหน้าจอ:

5. หากคุณต้องการนับและรวมเซลล์สีอื่น ๆ โปรดทำซ้ำขั้นตอนที่ 4 จากนั้นคุณจะได้ผลลัพธ์ดังต่อไปนี้:

Kutools for Excel ยังรองรับฟังก์ชันที่มีประโยชน์บางอย่างเพื่อช่วยให้ผู้ใช้ Excel ทำการคำนวณพิเศษกล่าวว่านับตามสีพื้นหลังของเซลล์ผลรวมตามสีแบบอักษรเป็นต้น

1. เลือกเซลล์ว่างที่คุณวางผลการนับแล้วคลิก Kutools > ฟังก์ชั่น Kutools > สถิติและคณิตศาสตร์ > COUNTBYCELLCOLOR. ดูภาพหน้าจอ:

2. ในกล่องโต้ตอบอาร์กิวเมนต์ของฟังก์ชันโปรดระบุช่วงที่คุณจะนับเซลล์สีภายในในไฟล์ อ้างอิง เลือกเซลล์ที่เต็มไปด้วยสีพื้นหลังที่ระบุในไฟล์ Color_index_nr แล้วคลิกไฟล์ OK ปุ่ม. ดูภาพหน้าจอ:

หมายเหตุ:
(1) คุณยังสามารถพิมพ์ฟังก์ชัน Kutools ที่ระบุได้ = COUNTBYCELLCOLOR ($ A $ 1: $ E $ 20, G2)  ในเซลล์ว่างหรือแถบสูตรโดยตรงเพื่อรับผลการนับ
(2) คลิก Kutools > ฟังก์ชั่น Kutools > สถิติและคณิตศาสตร์ > ซัมบีเซลล์คัลเลอร์ หรือพิมพ์ = SUMBYCELLCOLOR ($ A $ 1: $ E $ 20, G2) ในเซลล์ว่างโดยตรงเพื่อรวมเซลล์ตามสีพื้นหลังที่ระบุ
สมัคร COUNTBYCELLCOLOR และ ซัมบีเซลล์คัลเลอร์ ฟังก์ชั่นสำหรับสีพื้นหลังแต่ละสีแยกกันและคุณจะได้ผลลัพธ์ตามภาพด้านล่างที่แสดง:

ฟังก์ชั่น Kutools มีฟังก์ชันในตัวจำนวนมากเพื่อช่วยให้ผู้ใช้ Excel คำนวณได้อย่างง่ายดายรวมถึง จำนวน / ผลรวม / เซลล์ที่มองเห็นได้โดยเฉลี่ย, นับ / ผลรวมตามสีของเซลล์, นับ / ผลรวมตามสีตัวอักษร, นับอักขระ, นับตามตัวอักษรหนาฯลฯ ทดลองใช้ฟรี!

ด้วยฟังก์ชันที่กำหนดโดยผู้ใช้ข้างต้นคุณต้องป้อนสูตรทีละรายการหากมีสีที่แตกต่างกันจำนวนมากวิธีนี้จะน่าเบื่อและใช้เวลานาน แต่ถ้าคุณมี Kutools สำหรับ Excel's นับตามสี ยูทิลิตี้คุณสามารถสร้างรายงานของเซลล์สีได้อย่างรวดเร็ว คุณไม่เพียง แต่สามารถนับและรวมเซลล์สีเท่านั้น แต่ยังสามารถรับค่าเฉลี่ยสูงสุดและต่ำสุดของช่วงสีได้อีกด้วย

1. เลือกช่วงที่คุณต้องการใช้แล้วคลิก Kutools พลัส > นับตามสีดูภาพหน้าจอ:

2. และใน นับตามสี โปรดทำตามภาพด้านล่างที่แสดง:
(1) เลือก การจัดรูปแบบมาตรฐาน จาก วิธีการสี รายการแบบหล่นลง
(2) เลือก พื้นหลัง จาก ประเภทการนับ รายการแบบเลื่อนลง
(3) คลิกปุ่มสร้างรายงาน

หมายเหตุ: หากต้องการนับและรวมเซลล์สีตามสีการจัดรูปแบบตามเงื่อนไขที่เฉพาะเจาะจงโปรดเลือก การจัดรูปแบบตามเงื่อนไข จาก วิธีการสี รายการแบบหล่นลงในกล่องโต้ตอบด้านบนหรือเลือก การจัดรูปแบบมาตรฐานและตามเงื่อนไข จากรายการแบบหล่นลงเพื่อนับเซลล์ทั้งหมดที่เติมด้วยสีที่ระบุ

ตอนนี้คุณจะได้รับสมุดงานใหม่พร้อมสถิติ ดูภาพหน้าจอ:

พื้นที่ นับตามสี คุณสมบัติคำนวณเซลล์ (จำนวน, ผลรวม, ค่าเฉลี่ย, สูงสุด ฯลฯ ) ตามสีพื้นหลังหรือสีแบบอักษร ทดลองใช้ฟรี!

วิธีการนับ / รวมเซลล์ตามสีฟอนต์ใน Excel