Hướng dẫn dùng hàm COLOR trong Excel để đếm và tính tổng ô theo màu

làm thế nào để vận dụng màu sắc trong trang tính để tăng phần sinh động cho file excel? nếu bạn đang băn khoăn câu hỏi này thì hàm color trong excel chính là câu trả lời bạn đang tìm kiếm. hãy cùng gitiho học cách sử dụng hàm color qua bài viết ngày hôm nay nhé.

xem thêm: hướng dẫn cách viết các hàm trong excel chi tiết dễ hiểu nhất

Bạn đang xem: Hướng dẫn dùng hàm COLOR trong Excel để đếm và tính tổng ô theo màu

tuyệt đỉnh excel – trở thành bậc thầy excel trong 16 giờ

cách đếm và tính tổng các ô màu bằng hàm color trong excel

chúng ta hãy đi vào một ví dụ cụ thể về hàm color trong excel để xác định các bước cần làm. dưới đây là trang tính thể hiện trạng thái giao hàng của một công ty vận tải, trong đó “Đang giao hàng trong x ngày” được tô màu đá quý, “giao hàng thành công” được tô màu xanh, “quá hạn giao hàng” được tô màu đỏ. hãy cùng xem cách sử dụng hàm color trong excel để đếm và tính tổng các ô theo màu tương ứng nhé.

cách đếm và tính tổng các ô theo màu ô bằng hàm color trong excel

cách đếm số ô theo màu ô bằng hàm color trong excel

  • bước 1: Ấn tổ hợp phím alt+f11 để mở giao diện visual basic editor (vbe). sau đó nhấn chuột phải ở tên trang tính trong mục project – vba project, chọn insert > module.

hướng dẫn dùng hàm color trong excel để đếm và tính tổng ô theo màu

  • bước 2: một bảng tính hiện ra. các bạn nhập dòng code sau vào bảng tính:
function getcellcolor(xlrange as range)
dim indrow, indcolumn as long
dim arresults()
 
application.volatile
 
if xlrange is nothing then
set xlrange = application.thiscell
end if
 
if xlrange.count > 1 then
redim arresults(1 to xlrange.rows.count, 1 to xlrange.columns.count)
for indrow = 1 to xlrange.rows.count
for indcolumn = 1 to xlrange.columns.count
arresults(indrow, indcolumn) = xlrange(indrow, indcolumn).interior.color
next
next
getcellcolor = arresults
else
getcellcolor = xlrange.interior.color
end if
end function
 
function getcellfontcolor(xlrange as range)
dim indrow, indcolumn as long
dim arresults()
 
application.volatile
 
if xlrange is nothing then
set xlrange = application.thiscell
end if
 
if xlrange.count > 1 then
redim arresults(1 to xlrange.rows.count, 1 to xlrange.columns.count)
for indrow = 1 to xlrange.rows.count
for indcolumn = 1 to xlrange.columns.count
arresults(indrow, indcolumn) = xlrange(indrow, indcolumn).font.color
next
next
getcellfontcolor = arresults
else
getcellfontcolor = xlrange.font.color
end if
 
end function
 
function countcellsbycolor(rdata as range, cellrefcolor as range) as long
dim indrefcolor as long
dim cellcurrent as range
dim cntres as long
 
application.volatile
cntres = 0
indrefcolor = cellrefcolor.cells(1, 1).interior.color
for each cellcurrent in rdata
if indrefcolor = cellcurrent.interior.color then
cntres = cntres + 1
end if
next cellcurrent
 
countcellsbycolor = cntres
end function
 
function sumcellsbycolor(rdata as range, cellrefcolor as range)
dim indrefcolor as long
dim cellcurrent as range
dim sumres
 
application.volatile
sumres = 0
indrefcolor = cellrefcolor.cells(1, 1).interior.color
for each cellcurrent in rdata
if indrefcolor = cellcurrent.interior.color then
sumres = worksheetfunction.sum(cellcurrent, sumres)
end if
next cellcurrent
 
sumcellsbycolor = sumres
end function
 
function countcellsbyfontcolor(rdata as range, cellrefcolor as range) as long
dim indrefcolor as long
dim cellcurrent as range
dim cntres as long
 
application.volatile
cntres = 0
indrefcolor = cellrefcolor.cells(1, 1).font.color
for each cellcurrent in rdata
if indrefcolor = cellcurrent.font.color then
cntres = cntres + 1
end if
next cellcurrent
 
countcellsbyfontcolor = cntres
end function
 
function sumcellsbyfontcolor(rdata as range, cellrefcolor as range)
dim indrefcolor as long
dim cellcurrent as range
dim sumres
 
application.volatile
sumres = 0
indrefcolor = cellrefcolor.cells(1, 1).font.color
for each cellcurrent in rdata
if indrefcolor = cellcurrent.font.color then
sumres = worksheetfunction.sum(cellcurrent, sumres)
end if
next cellcurrent
 
sumcellsbyfontcolor = sumres
end function

lưu bảng tính vừa nhập code dưới tên “excel macro-enabled workbook (.xlsm)”. như vậy, chúng ta đã xử lí xong phần code vba.

  • bước 3: quay lại trang tính trạng thái giao hàng, ta tạo một bảng tổng hợp kết quả đếm ô theo màu. Để tiến hành phân loại và đếm số ô có màu tương ứng, ta dùng hàm color trong excel theo công thức: 

=countcellsbycolor(range, color code)

trong đó:

  • range: là phạm vi đếm
  • color code: là code màu tương ứng

như vậy, ta sẽ nhập vào ô b15 công thức: =countcellsbycolor($e$2:$e$12,a15)

hướng dẫn dùng hàm color trong excel để đếm và tính tổng ô theo màu

lưu ý: chúng ta có thể sử dụng tham chiếu tuyệt đối để khóa phạm vi đếm.

cách tính tổng theo màu ô bằng hàm color trong excel

tương tự như công thức hàm color phía trên, để tính tổng số liệu các ô theo màu tương ứng, chúng ta dùng hàm sau:

=sumcellsbycolor(range, color code)

Xem thêm: Các thủ thuật thêm bớt nhanh cột trong Excel, hay và lạ

trong ví dụ trên, giả sử chúng ta cần tính tổng số lượng hàng theo từng trạng thái giao hàng. ta điền vào ô b15 công thức: =sumcellsbycolor($c$2:$c$12,a15)

hướng dẫn dùng hàm color trong excel để đếm và tính tổng ô theo màu

cách đếm và tính tổng các ô theo màu chữ bằng hàm color trong excel

cách đếm số ô theo màu chữ bằng hàm color trong excel

thay vì sử dụng màu ô để đếm số ô tương ứng, chúng ta còn có thể đếm số ô theo màu chữ bằng cách dùng hàm:

=countcellsbyfontcolor(range, color code)

hướng dẫn dùng hàm color trong excel để đếm và tính tổng ô theo màu

cách tính tổng các ô theo màu chữ bằng hàm color trong excel

Để tính tổng các ô dựa vào màu chữ, chúng ta chỉ cần nhập công thức đơn giản như sau:

=sumcellsbyfontcolor(range, color code)

hướng dẫn dùng hàm color trong excel để đếm và tính tổng ô theo màu

lưu ý: khi sử dụng code vba phía trên, trong trường hợp chúng ta cần thêm các ô dữ liệu mới, chúng ta sẽ phải tô màu ô hoặc màu chữ thủ công. kết quả số ô và tổng các ô đã tính sẽ không tự động cập nhật thêm những ô dữ liệu mới. thay vào đó, các bạn cần di con trỏ chuột vào một ô bất kì trong phạm vi, ấn f2 rồi enter. lúc đó excel mới cập nhật kết quả sau cùng.

cách đếm và tính tổng số ô theo màu trên toàn bộ bảng tính

nếu các bạn muốn đếm và tính tổng số ô theo một màu nhất định thì hãy nhập code sau vào vba:

function wbkcountcellsbycolor(cellrefcolor as range)
dim vwbkres
dim wshcurrent as worksheet
 
application.screenupdating = false
application.calculation = xlcalculationmanual
 
vwbkres = 0
for each wshcurrent in worksheets
wshcurrent.activate
vwbkres = vwbkres + countcellsbycolor(wshcurrent.usedrange, cellrefcolor)
next
application.screenupdating = true
application.calculation = xlcalculationautomatic
 
wbkcountcellsbycolor = vwbkres
end function
 
function wbksumcellsbycolor(cellrefcolor as range)
dim vwbkres
dim wshcurrent as worksheet
 
application.screenupdating = false
application.calculation = xlcalculationmanual
 
vwbkres = 0
for each wshcurrent in worksheets
wshcurrent.activate
vwbkres = vwbkres + sumcellsbycolor(wshcurrent.usedrange, cellrefcolor)
next
application.screenupdating = true
application.calculation = xlcalculationautomatic
 
wbksumcellsbycolor = vwbkres
end function

các bạn có thể sử dụng mã macro phía trên cùng. công thức =wbkcountcellscolor() và =wbksumcellsbycolor(). bằng cách nhập công thức vào một ô trống bất kì bên ngoài vùng lựa chọn, xác định vị trí ô chứa màu lựa chọn và nhập vị trí ô vào ngoặc đơn (), các bạn sẽ nhận được kết quả tổng các ô cùng màu trong trang tính.

tổng hợp các chức năng tùy chỉnh màu sắc ô, màu chữ và lấy mã màu bằng hàm color trong excel:

  • công thức đếm số ô bằng màu sắc:
    • countcellsbycolor (range,color_code) – đếm các ô có màu nền được chỉ định.
    • countcellsbyfontcolor (range,color_code) – đếm các ô với màu chữ được chỉ định.

  • công thức tính tổng bằng màu sắc:
    • sumcellsbycolor (range, color code) – tính tổng của các ô có cùng màu nền.
    • sumcellsbyfontcolor (range, color code) – tính tổng của các ô có cùng màu chữ.

  • công thức lấy mã màu:
    • getcellfontcolor(cell) – trả về mã màu chữ của một ô xác định.
    • getcellcolor(cell) – trả về mã màu nền của một ô xác định.

lưu ý: tất cả các công thức chỉ hoạt động khi các bạn thêm chức năng được định nghĩa vào trang tính excel.

xem thêm: hướng dẫn những mã vba cơ bản và thường dùng nhất trong excel

cách đếm và tính tổng các ô màu định dạng có điều kiện bằng hàm color trong excel

Để đếm và tính tổng các ô màu ở bất kì định dạng có điều kiện nào, chúng ta cần nhập code vba dưới đây:

sub sumcountbyconditionalformat()
dim indrefcolor as long
dim cellcurrent as range
dim cntres as long
dim sumres
dim cntcells as long
dim indcurcell as long
 
cntres = 0
sumres = 0
 
cntcells = selection.countlarge
indrefcolor = activecell.displayformat.interior.color
 
for indcurcell = 1 to (cntcells – 1)
if indrefcolor = selection(indcurcell).displayformat.interior.color then
cntres = cntres + 1
sumres = worksheetfunction.sum(selection(indcurcell), sumres)
end if
next
msgbox “count=” & cntres & vbcrlf & “sum= ” & sumres & vbcrlf & vbcrlf & _
“color=” & left(“000000”, 6 – len(hex(indrefcolor))) & _
hex(indrefcolor) & vbcrlf, , “count & sum by conditional format color”
end sub

chúng ta làm theo các bước sau:

  • bước 1: chọn vùng dữ liệu bằng số muốn thao tác.
  • bước 2: nhấn giữ phím ctrl, chọn một ô có màu muốn thao tác trong vùng dữ liệu, thả phím ctrl.
  • bước 3: nhấn tổ hợp alt+f8 để mở danh sách macros.
  • bước 4: chọn sumcountbyconditionalformat và nhấn run.

hướng dẫn dùng hàm color trong excel để đếm và tính tổng ô theo màu

xem thêm: hướng dẫn hiện thẻ developer để ghi macro hoặc viết code vba trong excel

sau khi hoàn thành, kết quả được hiển thị như trong ảnh:

Xem thêm: Hướng dẫn copy dữ liệu từ Excel sang Word giữ nguyên định dạng

hướng dẫn dùng hàm color trong excel để đếm và tính tổng ô theo màu

hộp thoại trên cho thấy: có 3 ô nền màu đỏ thể hiện 3 đơn hàng đang ở trạng thái quá hạn giao hàng, tổng số lượng hàng bị quá hạn giao là 29, và mã của màu đỏ được dùng trong ô là 7578ff.

tổng kết

vậy là chúng ta đã học xong cách đếm và tính tổng các ô theo màu bằng hàm color trong excel rồi. bằng cách sử dụng yếu tố màu sắc, các bạn có thể phân loại dữ liệu rõ ràng hơn, đồng thời tăng sự sinh động cho file excel của mình. nếu bạn muốn học thêm những kiến thức hữu ích tương tự về excel và code vba excel, hãy đọc các bài viết trên blog gitiho và đăng kí các khóa học của gitiho nhé.

chúc các bạn áp dụng thật thành công kiến thức trong bài viết!

tham khảo thêm hướng dẫn các công cụ trong excel:

hướng dẫn cách dùng hàm index trong excel chi tiết nhất

hướng dẫn cách copy chỉ những giá trị hiển thị sau khi lọc trong excel

hướng dẫn cách sắp xếp ngày trong excel

hướng dẫn cách dùng hàm vlookup qua các ví dụ từ cơ bản đến nâng cao

hướng dẫn cách sử dụng hàm if với nhiều điều kiện: and, or, hàm if lồng nhau và hơn thế

gitiho.com  – nền tảng học online duy nhất tại việt nam tập trung vào phát triển kỹ năng làm việc dành cho người đi làm

với sứ mệnh: mang cơ hội phát triển kỹ năng, phát triển nghề nghiệp tới hàng triệu người”, đội ngũ phát triển gitiho.com  đã và đang làm việc với những học viện, trung tâm đào tạo, các chuyên gia đầu ngành để nghiên cứu và xây dựng lên các chương trình đào tạo từ cơ bản đến chuyên sâu xung quanh các lĩnh vực: tin học văn phòng, phân tích dữ liệu, thiết kế, công nghệ thông tin, kinh doanh, marketing, quản lý dự án…

gitiho tự hào khi được đồng hành cùng:

  • 50+ khách hàng doanh nghiệp lớn trong nhiều lĩnh vực như: vietinbank, vietcombank, bidv, vp bank, th true milk, vnpt, fpt software, samsung sdiv, ajinomoto việt nam, messer,…
  • 100.000+ học viên trên khắp việt nam

tìm hiểu ngay các khóa học của gitiho tẠi ĐÂy

hướng dẫn dùng hàm color trong excel để đếm và tính tổng ô theo màu

Nguồn: https://lava.com.vn
Danh mục: Excel