คุกกี้ช่วยให้เราสามารถนำเสนอบริการของเรา โดยการใช้บริการของเราคุณยินยอมที่จะใช้งานของเราคุกกี้
เคล็ดลับ: ภาษาอื่น ๆ ได้รับการแปลโดย Google คุณสามารถเยี่ยมชม English รุ่นของลิงก์นี้
เข้าสู่ระบบ
x
or
x
x
สมัครสมาชิก
x

or

วิธีการนับและรวมเซลล์ตามสีพื้นหลังใน Excel?

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

นับและผลรวมของเซลล์ตามสีเติมที่เฉพาะเจาะจงโดยตัวกรองและ SUBTOTAL

นับและผลรวมของเซลล์ตามสีเติมที่ระบุโดยฟังก์ชัน GET.CELL

นับและรวมเซลล์ตามสีเติมที่เฉพาะเจาะจงด้วย User Defined Function

นับและผลรวมของเซลล์ตามสีเติมเฉพาะ (หรือสีการจัดรูปแบบตามเงื่อนไข) ด้วย Kutools สำหรับ Excel

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

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


นับและผลรวมเซลล์สีด้วยตัวกรองและ SUBTOTAL

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

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

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


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

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

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

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

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


นับหรือรวมเซลล์สีด้วยฟังก์ชัน GET.CELL

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

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

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

3. ตอนนี้เพิ่มคอลัมน์สีใหม่ลงในตารางต้นฉบับ ถัดไปพิมพ์สูตร = NumColor และลากจุดจับการป้อนอัตโนมัติเพื่อใช้สูตรกับเซลล์อื่นในคอลัมน์สี ดูภาพหน้าจอ:
หมายเหตุ: ในสูตร, 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 คือคอลัมน์สี NumColor เป็นช่วงที่ระบุชื่อ $ E $ 2: $ E $ 20 คือคอลัมน์จำนวนเงินและคุณสามารถเปลี่ยนแปลงได้ตามต้องการ

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


นับและรวมเซลล์ตามสีเติมที่เฉพาะเจาะจงด้วย User Defined Function


หากว่าเซลล์สีกระจายในช่วงดังภาพด้านล่างที่แสดงวิธีการทั้งสองข้างต้นไม่สามารถนับหรือหาผลรวมของเซลล์สี ที่นี่วิธีนี้จะแนะนำ 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. นับจำนวนเซลล์สี: = colorfunction (A, B: C, FALSE)
B. รวมเซลล์สี: = colorfunction (A, B: C, TRUE)

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

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

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


นับและบวกเซลล์ตามสีเติมเฉพาะด้วย Kutools for Excel

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

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

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

Kutools สำหรับ Excel - รวมเครื่องมือ Excel ที่มีประโยชน์มากกว่า 300 ฟีเจอร์ทดลองใช้ฟรี 60 เต็มวันไม่ต้องใช้บัตรเครดิต! รับทันที!

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

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


บทความที่เกี่ยวข้อง:

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


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

ในวิดีโอนี้ Kutools แท็บและ Kutools More มีการเพิ่มโดย Kutools สำหรับ Excel. หากต้องการโปรดคลิก ได้ที่นี่ ทดลองใช้ฟรีเป็นเวลา 60 วันโดยไม่มีข้อ จำกัด !



เครื่องมือเพิ่มประสิทธิภาพที่แนะนำ

แท็บ Office

gold star1 นำแท็บที่มีประโยชน์ไปยัง Excel และซอฟต์แวร์ Office อื่น ๆ เช่นเดียวกับ Chrome, Firefox และ Internet Explorer ใหม่

Kutools สำหรับ Excel

gold star1 น่าทึ่ง! เพิ่มประสิทธิภาพการทำงานของคุณในอีก 5 นาที ไม่จำเป็นต้องมีทักษะพิเศษประหยัดสองชั่วโมงทุกวัน!

gold star1 300 คุณสมบัติใหม่สำหรับ Excel, Make Excel ง่ายมากและมีประสิทธิภาพ:

  • ผสานเซลล์ / แถว / คอลัมน์โดยไม่สูญเสียข้อมูล
  • รวมและรวมแผ่นงานหลายแผ่นและสมุดงาน
  • เปรียบเทียบช่วง, คัดลอกหลายช่วง, แปลงข้อความเป็นวันที่, หน่วยและการแปลงสกุลเงิน
  • นับตามสี, เพจย่อยย่อย, การเรียงลำดับขั้นสูงและตัวกรอง Super,
  • เพิ่มเติมเลือก / แทรก / ลบ / ข้อความ / รูปแบบ / ลิงก์ / ความคิดเห็น / เวิร์คบุ๊ค / แผ่นงานเครื่องมือ ...

ภาพหน้าจอของ Kutools for Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Mori · 2 days ago
    I copied and paste but calculation result is "0" why?? I am using Office 2016.
  • To post as a guest, your comment is unpublished.
    Troy Havard · 1 months ago
    I've copied and pasted as stated and have used this formula for over a year but recently saved the workbook to a new name and now the function doesn't work! I can't figure out what the deal is and I'm losing hair and sleep over it! Loading the original workbook, the formula works like a charm but going back to the new one, it doesn't! I've loaded VBA and tried re-creating the function but it doesn't work. Using Office 2019 - any help appreciated.
    • To post as a guest, your comment is unpublished.
      Jku · 13 days ago
      #NAME clearly indicates some keyword is not used in 2019, say, Interior.ColorIndex. Try to figure out changing the code from minimum lines to the full by adding one by one or search for the keywords in Excel/VBA in 2019
    • To post as a guest, your comment is unpublished.
      Troy Havard · 1 months ago
      I should probably have also noted that I get a #NAME? error in the cell in which I try to use the function.
  • To post as a guest, your comment is unpublished.
    Karolin · 2 months ago
    works fine, except it does not update the value in the totals when i change a cell to a diffrent colour. i have to click in the totals cell again to trigger a recount. Any Ideas how to fix this?
    • To post as a guest, your comment is unpublished.
      Arnier Hamz · 1 months ago
      Same here, i tried Ctrl+Alt+F9 or Ctrl+Shift+Alt+F9 to recalculate all formula and it works
  • To post as a guest, your comment is unpublished.
    Aaron · 3 months ago
    This doesn't work at all. It's total BS.
  • To post as a guest, your comment is unpublished.
    Schmingy · 5 months ago
    I was experiencing issues when attempting to use this function as-is to count/sum the lighter (pastel?) colors, at least in Excel 2016. Some debugging led me to realize that the range of "ColorIndex" values is quite limited, and the function above fails to distinguish between light yellow and light orange, reading both as ColorIndex = 19.

    Using the "Color" attribute, instead (as in "lCol = rColor.Interior.Color" and the two instances of "If rCell.Interior.Color = lCol Then", per the sample code above), resolved the issue.