Forum
Quản trị Excel Quản trị 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.

134 Comments

  1. Definitely imagine that which you stated. Your favorite justification appeared to be on the net the simplest factor to take into accout of. I say to you, I definitely get irked while people consider worries that they plainly don’t realize about. You controlled to hit the nail upon the highest as neatly as outlined out the entire thing without having side-effects , folks can take a signal. Will probably be back to get more. Thank you

    Reply
  2. I have been exploring for a bit for any high-quality articles or weblog posts in this kind of house . Exploring in Yahoo I ultimately stumbled upon this site. Studying this information So i’m satisfied to show that I’ve a very good uncanny feeling I found out just what I needed. I so much indubitably will make sure to don’t omit this website and provides it a glance on a continuing basis.

    Reply
  3. Thank you for sharing excellent informations. Your web-site is very cool. I am impressed by the details that you have on this blog. It reveals how nicely you understand this subject. Bookmarked this web page, will come back for more articles. You, my pal, ROCK! I found just the info I already searched all over the place and simply could not come across. What a great web site.

    Reply
  4. An impressive share, I just given this onto a colleague who was doing a little analysis on this. And he in fact bought me breakfast because I found it for him.. smile. So let me reword that: Thnx for the treat! But yeah Thnkx for spending the time to discuss this, I feel strongly about it and love reading more on this topic. If possible, as you become expertise, would you mind updating your blog with more details? It is highly helpful for me. Big thumb up for this blog post!

    Reply
  5. I’m impressed, I have to say. Really hardly ever do I encounter a weblog that’s both educative and entertaining, and let me tell you, you will have hit the nail on the head. Your idea is excellent; the problem is something that not sufficient individuals are speaking intelligently about. I’m very comfortable that I stumbled throughout this in my seek for one thing relating to this.

    Reply
  6. I wanted to post a brief remark so as to thank you for all of the fabulous recommendations you are giving on this site. My extended internet search has at the end of the day been recognized with sensible information to talk about with my family and friends. I ‘d mention that many of us visitors are unquestionably endowed to exist in a great website with many outstanding individuals with valuable secrets. I feel extremely blessed to have seen your website and look forward to really more exciting minutes reading here. Thanks a lot once again for all the details.

    Reply
  7. Together with almost everything that seems to be developing inside this particular subject material, your opinions tend to be quite refreshing. Nonetheless, I am sorry, because I do not give credence to your entire idea, all be it stimulating none the less. It would seem to us that your commentary are actually not completely rationalized and in actuality you are generally your self not even totally confident of your argument. In any event I did take pleasure in looking at it.

    Reply
  8. Unquestionably believe that which you said. Your favorite justification seemed to be on the web the easiest thing to be aware of. I say to you, I certainly get annoyed while people think about worries that they just don’t know about. You managed to hit the nail upon the top as well as defined out the whole thing without having side effect , people can take a signal. Will likely be back to get more. Thanks

    Reply
  9. Simply desire to say your article is as astonishing. The clearness in your post is just excellent and i can assume you’re an expert on this subject. Well with your permission let me to grab your feed to keep up to date with forthcoming post. Thanks a million and please continue the enjoyable work.

    Reply
  10. I would like to thank you for the efforts you have put in writing this site. I’m hoping the same high-grade site post from you in the upcoming also. In fact your creative writing skills has encouraged me to get my own site now. Actually the blogging is spreading its wings rapidly. Your write up is a good example of it.

    Reply
  11. Nice post. I study one thing tougher on completely different blogs everyday. It should all the time be stimulating to read content from other writers and practice just a little one thing from their store. I’d want to make use of some with the content on my blog whether you don’t mind. Natually I’ll provide you with a hyperlink on your net blog. Thanks for sharing.

    Reply
  12. Along with everything which seems to be developing throughout this subject material, a significant percentage of perspectives are generally somewhat stimulating. Nonetheless, I appologize, but I can not give credence to your whole plan, all be it stimulating none the less. It seems to us that your comments are generally not completely validated and in fact you are your self not even totally convinced of the argument. In any event I did appreciate reading through it.

    Reply
  13. Hey! This is my first visit to your blog! We are a collection of volunteers and starting a new project in a community in the same niche. Your blog provided us beneficial information to work on. You have done a marvellous job!

    Reply
  14. Thanks for sharing excellent informations. Your web site is so cool. I am impressed by the details that you¦ve on this site. It reveals how nicely you perceive this subject. Bookmarked this web page, will come back for more articles. You, my pal, ROCK! I found just the info I already searched everywhere and just couldn’t come across. What a perfect web site.

    Reply
  15. It’s the best time to make some plans for the future and it’s time to be happy. I have read this post and if I could I desire to suggest you some interesting things or tips. Maybe you can write next articles referring to this article. I desire to read more things about it!

    Reply
  16. It is the best time to make some plans for the future and it is time to be happy. I’ve read this post and if I could I want to suggest you few interesting things or suggestions. Perhaps you can write next articles referring to this article. I wish to read even more things about it!

    Reply
  17. Hmm it seems like your blog ate my first comment (it was extremely long) so I guess I’ll just sum it up what I wrote and say, I’m thoroughly enjoying your blog. I as well am an aspiring blog writer but I’m still new to everything. Do you have any tips and hints for newbie blog writers? I’d genuinely appreciate it.

    Reply
  18. Hi, i feel that i noticed you visited my site thus i got here to “return the favor”.I am attempting to find things to enhance my site!I assume its ok to make use of some of your ideas!!

    Reply
  19. Have you ever considered about including a little bit more than just your articles? I mean, what you say is important and all. But imagine if you added some great pictures or video clips to give your posts more, “pop”! Your content is excellent but with images and video clips, this website could certainly be one of the very best in its field. Awesome blog!

    Reply
  20. Hey! I know this is kinda off topic but I was wondering which blog platform are you using for this site? I’m getting sick and tired of WordPress because I’ve had issues with hackers and I’m looking at alternatives for another platform. I would be great if you could point me in the direction of a good platform.

    Reply
  21. After I originally commented I clicked the -Notify me when new feedback are added- checkbox and now every time a comment is added I get four emails with the same comment. Is there any way you can remove me from that service? Thanks!

    Reply
  22. Thanks for sharing excellent informations. Your web-site is so cool. I’m impressed by the details that you’ve on this site. It reveals how nicely you understand this subject. Bookmarked this web page, will come back for extra articles. You, my pal, ROCK! I found simply the information I already searched all over the place and simply could not come across. What a great website.

    Reply
  23. Thanks for sharing superb informations. Your website is very cool. I am impressed by the details that you¦ve on this blog. It reveals how nicely you perceive this subject. Bookmarked this website page, will come back for extra articles. You, my friend, ROCK! I found simply the info I already searched all over the place and just couldn’t come across. What an ideal website.

    Reply
  24. Needed to put you a little note just to give many thanks yet again for all the pretty tactics you’ve discussed on this website. It has been certainly unbelievably generous of people like you to supply publicly what exactly numerous people might have marketed as an e book to help make some cash for their own end, specifically now that you could have tried it in the event you desired. These basics likewise served to become good way to know that other people have similar passion just as mine to grasp way more with regards to this problem. I am sure there are some more pleasurable periods up front for those who check out your blog.

    Reply
  25. I believe that is among the so much important information for me. And i am glad reading your article. However want to commentary on some basic issues, The site style is wonderful, the articles is in point of fact great : D. Good activity, cheers

    Reply
  26. Wonderful blog! Do you have any recommendations for aspiring writers? I’m planning to start my own blog soon but I’m a little lost on everything. Would you propose starting with a free platform like WordPress or go for a paid option? There are so many choices out there that I’m completely overwhelmed .. Any tips? Thank you!

    Reply
  27. Howdy very cool site!! Man .. Excellent .. Wonderful .. I will bookmark your blog and take the feeds also…I am glad to find numerous helpful info here within the publish, we want work out more techniques on this regard, thanks for sharing. . . . . .

    Reply
  28. Thank you so much for giving everyone an extremely special possiblity to discover important secrets from this web site. It is often so terrific and full of a great time for me personally and my office fellow workers to visit the blog minimum 3 times weekly to find out the new guides you have. Not to mention, I’m just usually astounded with the excellent tips served by you. Certain 2 ideas on this page are definitely the most suitable I’ve ever had.

    Reply
  29. I’m just commenting to make you know of the cool experience our child obtained browsing your webblog. She came to find lots of issues, which include what it is like to have an awesome teaching spirit to have a number of people effortlessly know just exactly some multifaceted topics. You really exceeded readers’ desires. I appreciate you for imparting such valuable, dependable, revealing and also easy thoughts on your topic to Janet.

    Reply
  30. I do love the manner in which you have framed this specific problem plus it does provide me a lot of fodder for thought. Nevertheless, coming from just what I have experienced, I just hope as other feedback pack on that folks continue to be on point and in no way get started on a tirade involving the news of the day. Yet, thank you for this superb piece and although I do not agree with this in totality, I value the perspective.

    Reply
  31. I do like the way you have framed this matter and it does offer me personally some fodder for thought. On the other hand, because of what precisely I have seen, I really hope as other feedback pile on that people today keep on point and don’t start upon a tirade regarding the news of the day. All the same, thank you for this superb point and whilst I do not really go along with this in totality, I respect the viewpoint.

    Reply
  32. Thanks for each of your labor on this web site. Kate takes pleasure in going through investigations and it is simple to grasp why. Most of us learn all regarding the lively manner you give both interesting and useful guidance on your web blog and as well increase response from people on that content then our child is without question studying a whole lot. Have fun with the remaining portion of the year. Your conducting a brilliant job.

    Reply
  33. Its like you read my mind! You appear to know so much about this, like you wrote the book in it or something. I think that you could do with a few pics to drive the message home a little bit, but other than that, this is wonderful blog. A fantastic read. I’ll certainly be back.

    Reply

Đăng bình luận

(+84) (85) 39.67890