Forum
Excel Office

Hàm đếm các ô chứa màu trong excel

Advertisement


Với excel thì không gì là không thể, hẳn các bạn nghe qua đếm ô trống, đếm một giá trị thỏa mãn điều kiện nào đó…và ở vài viết này sẽ giới thiệu với các bạn một thủ thuật thú vị đó là đếm các ô chứa màu trong excel, bất kể màu gì miễn chúng được tô màu.
Ở bài viết này chúng ta đi tìm hiểu các ô có chứa màu, còn muốn tính tổng các giá trị có màu thì các bạn có thể tham khảo bài viết đó nhé.
Bây giờ chúng ta bắt đầu nào. Các bạn có một file excel với các ô có màu như bên dưới, việc của bạn là làm sao đếm được bao nhiêu ô có màu.


Bạn nhấn phím tắt Alt + F11 để mở cửa sổ vba lên sau đó chọn sheet mà bạn muốn đếm, ở đây là sheet 1, tiếp theo bạn chọn Insert – Module


Copy và dán đoạn code bên dưới vào
Function DemMau(Vung As Range, Mau As Range) As Long
Dim Rng As Range
For Each Rng In Vung
If Rng.Interior.Color = Mau.Interior.Color Then
DemMau = DemMau + 1
End If
Next
End Function
Như hình
Sau đó tắt đi và quay lại file excel bạn sử dụng hàm như sau:
=DEMMAU(A1:D2;$A$1)

Trong đó A1:D2 chính là vùng cần đếm, A1 là ô chứa định dạng cần đếm, chính là ô chứa màu.


Kết quả hiển thị chính xác những ô đã được tô màu.


Với vba thì không gì không thể phải không nào, nếu thấy bài viết hay hãy share cho mọi người cùng xem nhé, chúc các bạn thành công.

?

DienDan.Edu.Vn

DienDan.Edu.Vn Cám ơn bạn đã quan tâm và rất vui vì bài viết đã đem lại thông tin hữu ích cho bạn.
DienDan.Edu.Vn! là một website với tiêu chí chia sẻ thông tin,... Bạn có thể nhận xét, bổ sung hay yêu cầu hướng dẫn liên quan đến bài viết. Vậy nên đề nghị các bạn cũng không quảng cáo trong comment này ngoại trừ trong chính phần tên của bạn.
Cám ơn.

831 Comments

  1. I’ve been exploring for a little bit for any high quality articles or weblog posts in this sort of space . Exploring in Yahoo I finally stumbled upon this website. Reading this information So i am satisfied to express that I’ve a very excellent uncanny feeling I discovered just what I needed. I such a lot without a doubt will make certain to do not disregard this website and give it a glance regularly.

    Reply
  2. Thank you a bunch for sharing this with all folks you really understand
    what you are talking about! Bookmarked. Kindly also seek
    advice from my site =). We could have a hyperlink exchange arrangement among us

    Reply
  3. Woah! I’m really digging the template/theme of this website. It’s simple, yet effective. A lot of times it’s difficult to get that “perfect balance” between usability and appearance. I must say you have done a great job with this. Also, the blog loads very quick for me on Opera. Excellent Blog!

    Reply
  4. certainly like your website but you need to check the spelling on quite a few of your posts. Many of them are rife with spelling issues and I find it very troublesome to tell the truth nevertheless I will certainly come back again.

    Reply
  5. I would like to thnkx for the efforts you have put in writing this blog. I am hoping the same high-grade blog post from you in the upcoming as well. In fact your creative writing abilities has inspired me to get my own blog now. Really the blogging is spreading its wings quickly. Your write up is a good example of it.

    Reply
  6. I love your blog.. very nice colors & theme. Did you create this website yourself? Plz reply back as I’m looking to create my own blog and would like to know wheere u got this from. thanks

    Reply
  7. You really make it seem so easy with your presentation but I find this topic to be actually something that I think I would never understand. It seems too complex and extremely broad for me. I am looking forward for your next post, I will try to get the hang of it!

    Reply
  8. Youre so cool! I dont suppose Ive learn something like this before. So nice to seek out anyone with some authentic ideas on this subject. realy thank you for starting this up. this web site is something that’s wanted on the internet, somebody with just a little originality. helpful job for bringing one thing new to the internet!

    Reply
  9. There are definitely lots of details like that to take into consideration. That could be a nice point to carry up. I offer the thoughts above as general inspiration but clearly there are questions like the one you bring up the place the most important thing will likely be working in trustworthy good faith. I don?t know if best practices have emerged around things like that, but I’m sure that your job is clearly identified as a fair game. Each girls and boys really feel the impact of only a moment’s pleasure, for the rest of their lives.

    Reply
  10. I am now not sure the place you’re getting your info, but great topic. I needs to spend a while finding out much more or working out more. Thank you for great information I used to be looking for this information for my mission.

    Reply
  11. Hiya, I am really glad I’ve found this info. Today bloggers publish only about gossips and web and this is really frustrating. A good website with exciting content, that is what I need. Thanks for keeping this website, I will be visiting it. Do you do newsletters? Can’t find it.

    Reply
  12. Great – I should definitely pronounce, impressed with your site. I had no trouble navigating through all tabs and related information ended up being truly simple to do to access. I recently found what I hoped for before you know it in the least. Quite unusual. Is likely to appreciate it for those who add forums or something, site theme . a tones way for your customer to communicate. Nice task.

    Reply
  13. Great ?V I should certainly pronounce, impressed with your web site. I had no trouble navigating through all tabs as well as related information ended up being truly simple to do to access. I recently found what I hoped for before you know it at all. Reasonably unusual. Is likely to appreciate it for those who add forums or something, site theme . a tones way for your client to communicate. Excellent task..

    Reply
  14. Aw, this was a really nice post. In idea I wish to put in writing like this additionally – taking time and actual effort to make an excellent article… but what can I say… I procrastinate alot and by no means seem to get something done.

    Reply

Đăng bình luận

(+84) (918) 3369.468