Forum
Quản trị Excel Quản trị Office

Cách tìm, lọc và xoá dữ liệu trùng lặp trong Excel cực nhanh với Conditional Formatting và Remove Duplicates

Advertisement

Cách tìm, lọc và xoá dữ liệu trùng lặp trong Excel cực nhanh với Conditional Formatting và Remove Duplicates

Đối với dữ liệu trong Excel có số mẫu tin lớn, việc dữ liệu bị trùng lặp rất dễ xảy ra, để tìm kiếm dữ liệu trùng lặp này sẽ rất khó khăn và mất thời gian nếu chúng ta không sử dụng công cụ Conditional Formatting và Remove Duplicates trong Excel.
Conditional Formatting, Remove Duplicates và Auto Filter là những công cụ không khó để sử dụng lại cực kỳ hiệu quả trong việc tìm kiếm, trích lọc và xoá dữ liệu trùng lặp trong Excel. Trong bài viết này sẽ hướng dẫn các bạn những nội dung sau:
  • Tìm kiếm, đánh dấu dữ liệu trùng sử dụng Conditional Formatting
  • Trích lọc và xoá dữ liệu trùng trong Excel cực nhanh với Auto Filter và Remove Duplicates
  • Quy định và kiểm tra dữ liệu nhập với Data validtion để tránh sai sót khi nhập dữ liệu
Lưu ý: Để làm theo hướng dẫn các bạn có thể tải file mẫu Tại Đây
1. Cách tìm và đánh dấu dữ liệu bị trùng bằng Conditional Formatting
Cần lưu ý rằng để tìm dữ liệu bị trùng theo hướng dẫn này bạn phải dùng bản Excel từ 2007 trở lên, đối với bản excel 2003 bạn phải sử dụng thêm công thức.
Bước 1: Bạn bôi đen vùng dữ liệu chọn, sau đó trong thẻ Home chọn Conditional Formatting -> Highlight Rules Cells -> Duplicate values như hướng dẫn ở hình sau:
tìm và xoá dữ liệu bị trùng lặp trong Excel

Bước 2: Sau khi hoàn thành bước 1, cửa sổ Duplicate values xuất hiện cho phép bạn chọn phương thức đánh dấu các dữ liệu trùng như hình sau:
cách tìm và xoá dữ liệu bị trùng lặp trong excel
Ở thao tác (5):  Cho phép bạn chọn loại giá trị muốn đánh dấu, có 2 lựa chọn:
1. Duplicate: Chỉ đánh dấu các giá trị bị trùng
2. Unique: Chỉ đánh dấu các giá trị duy nhất
Ở thao tác (6): dựa theo lựa chọn ở thao tác (5), Excel cho bạn lựa chọn 1 trong 7 kiểu đánh dấu giá trị bị trùng lần lượt như sau
1. Light red Fill with dark red text: Bôi nền đỏ với chữ đỏ đậm
2. Yellow Fill with dark yellow text: Bôi nền vàng với chữ vàng đậm
3. Green Fill with dark green text: Bôi nền xanh với chữ xanh đậm
4. Light red Fill: Chỉ bôi nền đỏ
5. Red text: Bôi màu chữ đỏ cho tất cả các giá trị trùng hoặc duy nhất
6. Red border: Kẻ khung màu đỏ cho tất các ô chứa giá trị trùng hoặc duy nhất
7. Custom format: Thiết lập đánh dấu theo chỉ định của bạn (chúng ta nên lựa chọn từ 1-6)
sau các thao tác lựa chọn trên bạn chỉ cần nhấn OK là ta được kết quả như bảng sau:
cách tìm và xoá dữ liệu trong Excel
Sau khi tìm được dữ liệu bị trùng, các bạn có thể lọc dữ liệu bị trùng như hướng dẫn sau
2. Cách trích lọc, xoá dữ liệu trùng với Auto Filter và Remove Duplicates
2.1 Cách trích lọc dữ liệu với Auto Filter
Bước 1: Click chọn vào 1 ô bất kỳ trong vùng vừa chọn, Click chuột phải và chọn Filter trong danh sách menu hiện ra.
Bước 2: Chọn Filter by Selected  Cell’s Color (do ở phần trước sử dụng Light Red Fill – tô nền màu đỏ nhạt – màu hồng)
cách tìm và xoá dữ liệu trùng lặp trong Excel
Sau đó các bạn sẽ thu được bảng hiển thị các giá trị bị trùng, bạn có thể sử dụng trực tiếp lệnh Data →  Auto Filter như sau: Bôi đen dòng tiêu đề, chọn thẻ data → Filter →  chọn button ở cột Mã hàng →  chọn Sort by color → chọn màu như hình sau:
tìm và lọc dữ liệu với auto filter
Để bỏ lọc dữ liệu: nhấn chuột vào Filter (hình chiếc phễu ở thao tác 3); Để bỏ đánh dấu bạn thực hiện như sau: Bôi đen vùng dữ liệu, thẻ Home chọn Conditional Formatting -> Clear Rules -> Clear Rules from Selected Cells như hình sau:
Nếu muốn xoá các giá trị bị trùng lặp và chỉ giữ lại duy nhất 1 giá trị, chúng ta sẽ sử dụng chức năng Remove Duplicates.
2.2  Các xoá dữ liệu bị trùng trong Excel cực nhanh với Remove Duplicates
Để xoá dữ liệu bị trùng lặp trong Excel ta thực hiện như sau: Đầu tiên, bôi đen toàn bộ dữ liệu muốn lọc, vào thẻ Data → Remove Duplicate, một hộp thoại mới xuất hiện và làm theo hướng dẫn ở hình sau:tìm và xoá dữ liệu trùng trong Excel
Như lựa chọn ở hình trên dữ liệu trùng cả cột Mã hàng và cột Số lượng mới bị xóa, chỉ giữ lại 1 giá trị duy nhất trong các dòng dữ liệu bị trùng này (để ý là có 1 dòng dữ liệu có mã hàng là 1230011 và số lượng 300 bị xóa).
3. Quy định và kiểm tra dữ liệu nhập với Data validtion để tránh sai sót khi nhập dữ liệu trong Excel
Với trường hợp nhập dữ liệu mới, để dữ liệu là duy nhất và tránh trùng lặp, các bạn hoàn toàn có thể dùng Data validation để quy định giữ liệu nhập.
Bài viết hướng dẫn chi tiết cách sử dụng Data validation đã được giới thiệu trên Hay học hỏi, các bạn có thể tham khảo ở liên kết sau:
Bước 1: Bôi đen cột mã hàng, chọn thẻ Data -> Data validation chọn tiếp Data validationBây giờ, chúng ta tiếp tục với phần này sẽ hướng dẫn sử dụng data validation để tránh nhập trùng dữ  liệu, các bạn thực hiện như sau:
Bước 2: Hộp thoại data validation xuất hiện, ở thẻ Settings, vùng Allow chọn Custom, vùng Fomula nhập công thức: =COUNTIF($D$4:$D$11,D7)=1 nhấn OK như hướng dẫn ở hình sau:
data validation kiểm tra dữ liệu trùng
Bước 3: Ở thẻ Error Alert, vùng style chọn warning, vùng Title nhập Thông báo, vùng Error Message nhập: Dữ liệu bị trùng, vui lòng nhập giá trị khác.
data validation kiểm tra dữ liệu trùng
Tiến hành nhập thử lại dữ liệu ở cột mã hàng, khi nhập tới giá trị thứ 4 có mã hàng trùng với dữ liệu ô mã hàng đầu tiên sẽ có thông báo như hình sau:
Như vậy, với phần hướng dẫn cách tìm dữ liệu trùng, lọc và xóa dữ liệu trùng đồng thời sử dụng data validation kiểm tra dữ liệu trùng khi nhập ở trên giúp ích cho các bạn. Nếu thấy bài viết có ích, hãy chia sẻ và comment ý kiến dưới bài viết nha các bạn.

?

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.

167 Comments

  1. I happen to be writing to let you be aware of of the perfect encounter our girl undergone studying your web site. She noticed so many pieces, most notably what it’s like to have a wonderful giving mood to get many others without hassle gain knowledge of a number of specialized topics. You actually exceeded my desires. Thanks for coming up with these invaluable, trusted, explanatory not to mention unique tips on your topic to Emily.

    Reply
  2. I would like to thank you for the efforts you’ve put in writing this web site. I am hoping the same high-grade website post from you in the upcoming also. Actually your creative writing skills has inspired me to get my own web site now. Really the blogging is spreading its wings fast. Your write up is a great example of it.

    Reply
  3. Have you ever considered creating an ebook or guest authoring on other websites? I have a blog centered on the same information you discuss and would love to have you share some stories/information. I know my readers would value your work. If you are even remotely interested, feel free to send me an email.

    Reply
  4. I’m extremely inspired with your writing abilities as well as with the structure to your weblog. Is that this a paid topic or did you modify it yourself? Either way keep up the nice high quality writing, it’s rare to look a great weblog like this one these days..

    Reply
  5. Good V I should definitely pronounce, impressed with your website. I had no trouble navigating through all the tabs and related info ended up being truly simple to do to access. I recently found what I hoped for before you know it at all. Quite unusual. Is likely to appreciate it for those who add forums or anything, web site theme . a tones way for your customer to communicate. Nice task..

    Reply
  6. You could certainly see your enthusiasm within the work you write. The sector hopes for even more passionate writers such as you who aren’t afraid to mention how they believe. Always go after your heart. “Everyone has his day and some days last longer than others.” by Sir Winston Leonard Spenser Churchill.

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

    Reply
  8. Thank you for the sensible critique. Me & my neighbor were just preparing to do some research about this. We got a grab a book from our area library but I think I learned more from this post. I’m very glad to see such magnificent information being shared freely out there.

    Reply
  9. Great post. I was checking constantly this blog and I am impressed! Very helpful information specially the last part 🙂 I care for such information a lot. I was looking for this particular information for a long time. Thank you and best of luck.

    Reply
  10. Admiring the hard work you put into your website and detailed information you present. It’s great to come across a blog every once in a while that isn’t the same old rehashed material. Great read! I’ve saved your site and I’m adding your RSS feeds to my Google account.

    Reply
  11. Attractive section of content. I just stumbled upon your blog and in accession capital to assert that I acquire actually enjoyed account your blog posts. Any way I will be subscribing to your augment and even I achievement you access consistently rapidly.

    Reply
  12. I’ve recently started a site, the information you provide on this site has helped me tremendously. Thank you for all of your time & work. “Her grandmother, as she gets older, is not fading but rather becoming more concentrated.” by Paulette Bates Alden.

    Reply
  13. I have been browsing online more than 3 hours today, yet I never found any interesting article like yours. It is pretty worth enough for me. In my view, if all website owners and bloggers made good content as you did, the net will be a lot more useful than ever before.

    Reply
  14. I was wondering if you ever thought of changing the page layout of your website? Its very well written; I love what youve got to say. But maybe you could a little more in the way of content so people could connect with it better. Youve got an awful lot of text for only having one or two images. Maybe you could space it out better?

    Reply
  15. Hey very nice web site!! Man .. Beautiful .. Amazing .. I will bookmark your site and take the feeds also…I am happy to find a lot of useful info here in the post, we need work out more techniques in this regard, thanks for sharing. . . . . .

    Reply
  16. My spouse and i have been quite fulfilled Chris could complete his studies through the precious recommendations he had while using the site. It is now and again perplexing just to be giving freely tricks the rest might have been making money from. So we know we need the writer to appreciate for this. Those illustrations you made, the simple blog navigation, the relationships you give support to instill – it is most astonishing, and it’s assisting our son in addition to our family reason why that concept is excellent, which is certainly really mandatory. Many thanks for the whole thing!

    Reply
  17. I simply wanted to type a remark to thank you for those magnificent tricks you are giving out on this website. My particularly long internet look up has finally been honored with pleasant details to talk about with my two friends. I would assume that many of us website visitors are very much lucky to live in a great site with very many outstanding people with insightful opinions. I feel somewhat fortunate to have encountered your entire web page and look forward to some more fabulous minutes reading here. Thanks again for a lot of things.

    Reply
  18. Good V I should definitely pronounce, impressed with your website. I had no trouble navigating through all the 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
  19. Good day! I know this is kind of 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 fantastic if you could point me in the direction of a good platform.

    Reply
  20. I think other web-site proprietors should take this web site as an model, very clean and fantastic user friendly style and design, as well as the content. You are an expert in this topic!

    Reply
  21. Thanx for the effort, keep up the good work Great work, I am going to start a small Blog Engine course work using your site I hope you enjoy blogging with the popular BlogEngine.net.Thethoughts you express are really awesome. Hope you will right some more posts.

    Reply
  22. Great – I should certainly pronounce, impressed with your web site. I had no trouble navigating through all the tabs and related info 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, web site theme . a tones way for your client to communicate. Nice task..

    Reply
  23. Do you mind if I quote a couple of your articles as long as I provide credit and sources back to your site? My website is in the exact same niche as yours and my visitors would definitely benefit from a lot of the information you present here. Please let me know if this ok with you. Regards!

    Reply
  24. Its like you read my thoughts! You seem to understand a lot about this, such as you wrote the guide in it or something. I feel that you can do with a few to drive the message home a little bit, but other than that, this is wonderful blog. A great read. I will certainly be back.

    Reply
  25. I’m really enjoying the design and layout of your website. It’s a very easy on the eyes which makes it much more enjoyable for me to come here and visit more often. Did you hire out a developer to create your theme? Outstanding work!

    Reply
  26. What i do not understood is actually how you are not actually much more well-liked than you may be right now. You’re very intelligent. You realize therefore considerably relating to this subject, made me personally consider it from a lot of varied angles. Its like women and men aren’t fascinated unless it is one thing to accomplish with Lady gaga! Your own stuffs outstanding. Always maintain it up!

    Reply
  27. 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
  28. Hello very nice site!! Guy .. Excellent .. Amazing .. I will bookmark your web site and take the feeds additionally…I am satisfied to search out a lot of helpful info here in the publish, we want work out extra techniques in this regard, thanks for sharing.

    Reply
  29. I’ve been exploring for a bit for any high quality articles or blog posts on this kind of area . Exploring in Yahoo I finally stumbled upon this web site. Reading this info So i’m glad to exhibit that I have a very good uncanny feeling I came upon exactly what I needed. I most surely will make certain to don’t fail to remember this web site and give it a glance on a relentless basis.

    Reply
  30. I wish to convey my passion for your generosity giving support to men and women that really need assistance with in this issue. Your real commitment to getting the message throughout appears to be astonishingly functional and has specifically empowered regular people just like me to arrive at their pursuits. Your own warm and friendly tutorial entails a whole lot a person like me and much more to my colleagues. Regards; from each one of us.

    Reply
  31. Along with every thing that seems to be building throughout this specific subject material, your points of view are actually quite radical. Even so, I beg your pardon, because I do not give credence to your whole plan, all be it exciting none the less. It would seem to everybody that your opinions are actually not entirely validated and in reality you are generally yourself not really thoroughly convinced of your assertion. In any event I did enjoy looking at it.

    Reply
  32. Hi there just wanted to give you a quick heads up. The words in your post seem to be running off the screen in Internet explorer. I’m not sure if this is a formatting issue or something to do with web browser compatibility but I figured I’d post to let you know. The style and design look great though! Hope you get the issue solved soon. Cheers

    Reply
  33. I together with my pals were actually examining the excellent tactics found on your site while quickly I had an awful suspicion I never expressed respect to the blog owner for those secrets. Most of the boys appeared to be totally excited to read through them and have without a doubt been making the most of them. I appreciate you for genuinely simply accommodating and for making a choice on this kind of amazing subjects most people are really wanting to discover. Our honest regret for not saying thanks to you earlier.

    Reply
  34. What i do not realize is actually how you are not really much more well-liked than you may be now. You’re so intelligent. You realize thus considerably relating to this subject, produced me personally consider it from numerous varied angles. Its like women and men aren’t fascinated unless it’s one thing to do with Lady gaga! Your own stuffs nice. Always maintain it up!

    Reply
  35. hello!,I like your writing very so much! share we keep up a correspondence more approximately your article on AOL? I require a specialist in this area to unravel my problem. May be that’s you! Looking forward to peer you.

    Reply
  36. Thanks for sharing excellent informations. Your site is very cool. I’m impressed by the details that you have 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 simply the info I already searched all over the place and simply could not come across. What an ideal web-site.

    Reply
  37. I would like to thank you for the efforts you’ve put in writing this site. I am hoping the same high-grade web site post from you in the upcoming also. Actually your creative writing abilities has inspired me to get my own web site now. Really the blogging is spreading its wings quickly. Your write up is a good example of it.

    Reply
  38. I simply wished to thank you so much yet again. I’m not certain the things that I would’ve implemented without these advice shown by you regarding such area. Certainly was a very daunting dilemma in my view, nevertheless observing the skilled strategy you processed it took me to weep for joy. I will be happy for your advice and thus pray you are aware of an amazing job that you are undertaking instructing men and women with the aid of your website. I am sure you have never encountered all of us.

    Reply
  39. Howdy very nice site!! Guy .. Beautiful .. Amazing .. I will bookmark your website and take the feeds additionallyKI’m happy to seek out numerous useful info right here in the publish, we want develop more strategies on this regard, thanks for sharing. . . . . .

    Reply
  40. Can I just say what a relief to find someone who actually knows what theyre talking about on the internet. You definitely know how to bring an issue to light and make it important. More people need to read this and understand this side of the story. I cant believe youre not more popular because you definitely have the gift.

    Reply
  41. I found your blog web site on google and verify a number of of your early posts. Continue to maintain up the superb operate. I just additional up your RSS feed to my MSN Information Reader. Searching for forward to reading more from you in a while!…

    Reply
  42. 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
  43. Great V I should certainly pronounce, impressed with your site. I had no trouble navigating through all the tabs as well as related info 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 anything, website theme . a tones way for your client to communicate. Nice task..

    Reply
  44. What i don’t understood is actually how you’re no longer actually much more well-favored than you may be right now. You’re very intelligent. You recognize therefore significantly relating to this matter, made me individually believe it from a lot of numerous angles. Its like women and men are not interested except it’s one thing to accomplish with Lady gaga! Your personal stuffs outstanding. Always take care of it up!

    Reply
  45. Vanzari si montaj parbrize, lunete, geamuri laterale Citroen
    Livram si montam la domiciliul clientului, orice tip
    de parbriz Citroen, geam Citroen si luneta Citroen pentru masina dumneavoastra in Bucuresti si zona limitrofa.

    Parbriz Citroen,Parbrize Citroen,Parbriz
    Auto Citroen,Parbrize Auto Citroen,Parbriz Auto Citroen Bucuresti,Parbrize Auto Citroen Bucuresti,Parbriz Citroen La
    Domiciliu,Parbrize Citroen La Domiciliu,Montaj Parbriz Citroen,Inlocuire Parbriz
    Citroen,Parbriz Citroen Pret,Parbriz Citroen Ieftin,Parbriz Citroen Bucuresti,
    Parbriz Citroen Ilfov,Parbriz Citroen Berlingo,Parbriz Citroen C Crosser,Parbriz
    Citroen C Elysee,Parbriz Citroen C Zero,Parbriz Citroen C1,Parbriz Citroen C2,Parbriz
    Citroen C3,Parbriz Citroen C4,Parbriz Citroen C5,
    Parbriz Citroen C6,Parbriz Citroen C8,Parbriz Citroen Jumper,Parbriz Citroen Jumpy,Parbriz Citroen Xantia,Parbriz Citroen XM,Parbriz Citroen Xsara,Parbriz Citroen Spacetourer
    Ai nevoie de un parbriz nou? O luneta sau un geam?
    Ai parbrizul ciobit? Nimic mai simplu!Cere oferta
    acum!
    Parbrize, lunete si geamuri laterale pentru o gama larga de autoturisme, autoutilitare si camioane.

    La comanda si pentru modele mai putin populare.
    Echipele noastre de montaj folosesc materiale de cea mai buna calitate.

    Pentru noi cea mai buna reclama o reprezinta un client multumit.

    Deoarece stim ca timpul dvs. este important, de comun acord
    (in baza unei programari) se stabileste data
    si ora la care se va face montajul. Parbrize, lunete, geamuri laterale
    de calitate.Parbriz FUYAO, Parbriz PILKINGTON, Parbriz SAINT-GOBAIN.

    Parbriz Alfa Romeo , Parbriz Audi , Parbriz Bmw ,
    Parbriz Chevrolet , Parbriz Chrysler , Parbriz Citroen , Parbriz Dacia
    , Parbriz Daewoo , Parbriz Fiat , Parbriz Ford , Parbriz Honda , Parbriz Hyundai , Parbriz
    Jeep , Parbriz Kia , Parbriz Lada , Parbriz Lancia , Parbriz Land Rover ,
    Parbriz Mazda , Parbriz Mercedes , Parbriz Mitsubishi , Parbriz Nissan ,
    Parbriz Opel , Parbriz Peugeot , Parbriz Porsche , Parbriz Renault
    , Parbriz Rover , Parbriz Saab , Parbriz Seat , Parbriz Skoda , Parbriz Smart
    , Parbriz SsangYong , Parbriz Subaru , Parbriz Suzuki , Parbriz Toyota , Parbriz Volkswagen , Parbriz Volvo ,

    https://parbriz.net/parbriz.php?marca=Citroen&step1=marca_auto https://parbriz.net/parbriz.php?marca=Citroen&step1=marca_auto

    Reply
  46. Hey! Quick question that’s entirely off topic. Do you know how to make your site mobile friendly? My web site looks weird when browsing from my iphone. I’m trying to find a theme or plugin that might be able to fix this problem. If you have any recommendations, please share. Cheers!

    Reply
  47. Great V I should certainly pronounce, impressed with your web site. I had no trouble navigating through all the tabs as well as related info 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. Excellent task..

    Reply
  48. Write more, thats all I have to say. Literally, it seems as though you relied on the video to make your point. You obviously know what youre talking about, why throw away your intelligence on just posting videos to your site when you could be giving us something enlightening to read?

    Reply

Đăng bình luận

(+84) (85) 39.67890