Excelda ikkita ustunni qanday solishtirish va dublikatlarni olib tashlash (ta'kidlash, rang berish, ko'chirish)

Ushbu maqolani o'qish uchun sizga 10 daqiqa vaqt ketadi. Keyingi 5 daqiqada siz Excelda ikkita ustunni osongina solishtirishingiz va ularda dublikatlar mavjudligini bilib olishingiz, ularni o'chirishingiz yoki rang bilan ajratib ko'rsatishingiz mumkin. Shunday qilib, vaqt keldi!

Excel katta hajmdagi ma'lumotlarni yaratish va qayta ishlash uchun juda kuchli va chindan ham ajoyib dasturdir. Agar sizda ma'lumotlarga ega bo'lgan bir nechta ish kitoblari (yoki faqat bitta katta jadval) bo'lsa, unda siz ikkita ustunni solishtirishni, takroriy qiymatlarni topishni va keyin ular bilan biror narsa qilishni, masalan, tarkibni o'chirish, ajratib ko'rsatish yoki tozalashni xohlaysiz. Ustunlar bitta jadvalda bo'lishi mumkin, qo'shni yoki qo'shni bo'lmasligi mumkin, 2 xil varaqda yoki hatto turli kitoblarda joylashgan bo'lishi mumkin.

Tasavvur qiling, bizda odamlarning ismlari bo'lgan 2 ta ustun bor - har bir ustunda 5 ta nom A va ustunda 3 ta nom B. Ushbu ikki ustundagi nomlarni solishtirib, dublikatlarni topishingiz kerak. Siz tushunganingizdek, bu faqat misol uchun olingan xayoliy ma'lumotlar. Haqiqiy jadvallarda biz minglab yoki hatto o'n minglab yozuvlar bilan ishlaymiz.

Variant A: ikkala ustun ham bir varaqda. Masalan, ustun A va ustun B.

Varian-r: Ustunlar turli varaqlarda joylashgan. Masalan, ustun A varaqda Jadval2 va ustun A varaqda Jadval3.

Excel 2013, 2010 va 2007 da o'rnatilgan vosita mavjud Dublikatlarni olib tashlang (Dublikatlarni o'chirish) lekin bu holatda u kuchsiz, chunki u 2 ustundagi ma'lumotlarni solishtira olmaydi. Bundan tashqari, u faqat dublikatlarni olib tashlashi mumkin. Ranglarni ta'kidlash yoki o'zgartirish kabi boshqa variantlar yo'q. Va nuqta!

Keyinchalik, men sizga Excelda ikkita ustunni solishtirishning mumkin bo'lgan usullarini ko'rsataman, bu sizga takroriy yozuvlarni topish va o'chirish imkonini beradi.

• 2 ta ustunni solishtiring va formulalar yordamida dublikatlarni qidiring
  
  • Variant A: ikkala ustun ham bir varaqda
  • Variant B: Ustunlar turli varaqlarda yoki turli ish kitoblarida
• Topilgan dublikatlarni qayta ishlash
  
  • A ustunida faqat takroriy satrlarni ko'rsating
  • Rangni o'zgartiring yoki topilgan dublikatlarni belgilang
  • Birinchi ustundan dublikatlarni olib tashlang

Excelda 2 ta ustunni solishtiring va formulalar yordamida takroriy yozuvlarni toping

Variant A: ikkala ustun ham bir varaqda

1. Birinchi bo'sh katakka (bizning misolimizda bu C1 katak), biz quyidagi formulani yozamiz:

   =IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")

   

   Bizning formulamizda A1 bu biz solishtirmoqchi bo'lgan birinchi ustunning birinchi katagi. $1 B$ и $10000 B$ bu ikkinchi ustunning birinchi va oxirgi kataklarining manzillari bo'lib, ular bilan taqqoslashni amalga oshiramiz. Mutlaq havolalarga e'tibor bering - ustun harflari va qator raqamlaridan oldin dollar belgisi ($) mavjud. Formulalarni nusxalashda hujayra manzillari bir xil bo'lib qolishi uchun mutlaq havolalardan foydalanaman.

   Agar siz ustunda dublikatlarni topmoqchi bo'lsangiz B, havolalarni formula quyidagicha ko'rinishi uchun o'zgartiring:

   =IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")

   Buning o'rniga "faqat"Va"ko'paytirish» Siz o'z teglaringizni yozishingiz mumkin, masalan, «Topilmadi"Va"Topilgan", yoki faqat qoldiring "ko'paytirish' va ikkinchi qiymat o'rniga bo'sh joy belgisini kiriting. Ikkinchi holda, dublikatlari topilmagan hujayralar bo'sh qoladi va menimcha, ma'lumotlarning bu ko'rinishi keyingi tahlil uchun eng qulaydir.

2. Endi formulamizni ustunning barcha kataklariga ko'chiramiz C, ustundagi ma'lumotlarni o'z ichiga olgan pastki qatorga qadar A. Buning uchun sichqoncha ko‘rsatkichini katakchaning pastki o‘ng burchagiga olib boring C1, ko'rsatgich quyidagi rasmda ko'rsatilganidek, qora chiziq shaklida bo'ladi:Sichqonchaning chap tugmachasini bosing va ushlab turing va formulani kiritmoqchi bo'lgan barcha katakchalarni ajratib ko'rsatgan holda ramka chegarasini pastga torting. Barcha kerakli katakchalar tanlanganda sichqoncha tugmasini qo'yib yuboring:

   

Maslahat: Katta jadvallarda, agar siz klaviatura yorliqlaridan foydalansangiz, formulani nusxalash tezroq bo'ladi. Hujayrani ajratib ko'rsatish C1 va matbuot Ctrl + C (formulani buferga nusxalash uchun), keyin bosing Ctrl + Shift + End (C ustunidagi barcha bo'sh bo'lmagan kataklarni tanlash uchun) va oxirida bosing Ctrl + V (formulani barcha tanlangan kataklarga kiritish uchun).

3. Ajoyib, endi barcha takroriy qiymatlar " sifatida belgilanganko'paytirish":

Variant B: ikkita ustun turli varaqlarda (turli ish kitoblarida)

1. Ish varag'idagi birinchi bo'sh ustunning birinchi katagida Jadval2 (bizning holatda bu B ustuni) quyidagi formulani kiriting:

   =IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")

   =ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")

   shu yerda Jadval3 2-ustun joylashgan varaqning nomi va $A$1:$A$10000 Bu 1-ustunda 2-dan oxirgigacha boʻlgan katak manzillari.

2. Formulani ustunning barcha kataklariga nusxalash B (A varianti bilan bir xil).
3. Biz ushbu natijani olamiz:

Topilgan dublikatlarni qayta ishlash

Ajoyib, biz birinchi ustundagi yozuvlarni topdik, ular ikkinchi ustunda ham mavjud. Endi biz ular bilan nimadir qilishimiz kerak. Jadvaldagi barcha takroriy yozuvlarni qo'lda ko'rib chiqish juda samarasiz va juda ko'p vaqtni oladi. Yaxshiroq usullar mavjud.

A ustunida faqat takroriy satrlarni ko'rsating

Agar ustunlaringiz sarlavhalari bo'lmasa, ularni qo'shishingiz kerak. Buning uchun kursorni birinchi qatorni ko'rsatadigan raqamga qo'ying va u quyidagi rasmda ko'rsatilganidek, qora o'qga aylanadi:

Sichqonchaning o'ng tugmachasini bosing va kontekst menyusidan tanlang Qo'shish (Kiritmoq):

Ustunlarga nom bering, masalan, "Ism"Va"Takroriymi?» Keyin yorliqni oching ma'lumotlar (Ma'lumotlar) va bosing filter (Filtr):

Shundan so'ng " yonidagi kichik kulrang o'qni bosing.Takroriymi?« filtr menyusini ochish uchun; tashqari ushbu ro'yxatdagi barcha elementlarni belgilang ko'paytirish-ni tanlang va tugmasini bosing OK.

Hammasi shu, endi siz faqat ustunning elementlarini ko'rasiz А, ular ustunda takrorlanadi В. Bizning o'quv stolimizda faqat ikkita hujayra mavjud, ammo siz tushunganingizdek, amalda ulardan ko'pi bo'ladi.

Ustunning barcha qatorlarini qayta ko'rsatish uchun А, ustundagi filtr belgisini bosing В, endi kichik o'q bilan huni kabi ko'rinadi va tanlang barcha tanlang (Hammasini belgilash). Yoki bosish orqali lenta orqali xuddi shunday qilishingiz mumkin ma'lumotlar (Ma'lumotlar) > Tanlang & Filtr (Saralash va filtrlash) > aniq (Tozalash) quyidagi skrinshotda ko'rsatilganidek:

Rangni o'zgartiring yoki topilgan dublikatlarni belgilang

Agar eslatmalar "ko'paytirish” sizning maqsadlaringiz uchun etarli emas va siz boshqa shrift rangi, to'ldirish rangi yoki boshqa usul bilan takroriy hujayralarni belgilashni xohlaysiz ...

Bunday holda, yuqorida ko'rsatilganidek, dublikatlarni filtrlang, barcha filtrlangan katakchalarni tanlang va bosing Ctrl + 1dialog oynasini ochish uchun Hujayralarni formatlash (hujayra formati). Misol tariqasida, dublikatli satrlardagi kataklarning to'ldirish rangini yorqin sariq rangga o'zgartiramiz. Albatta, siz asbob yordamida to'ldirish rangini o'zgartirishingiz mumkin To'ldiring (To'ldirish rangi) yorlig'i Bosh sahifa (Uy) lekin dialog oynasi afzalligi Hujayralarni formatlash (Hujayra formati) bunda siz bir vaqtning o'zida barcha formatlash opsiyalarini sozlashingiz mumkin.

Endi siz dublikatlari bo'lgan hujayralarni o'tkazib yubormaysiz:

Birinchi ustundan takroriy qiymatlarni olib tashlash

Jadvalni filtrlang, shunda faqat takroriy qiymatlari bo'lgan katakchalar ko'rsatiladi va o'sha katakchalarni tanlang.

Agar siz solishtirayotgan 2 ta ustun turli varaqlarda bo'lsa, ya'ni turli jadvallarda tanlangan diapazonni o'ng tugmasini bosing va tanlang Qatorni o'chirish (chiziqni olib tashlash):

matbuot OKExcel sizdan haqiqatan ham butun varaq qatorini o'chirishni va keyin filtrni o'chirishni xohlayotganingizni tasdiqlashingizni so'raganda. Ko'rib turganingizdek, faqat noyob qiymatlarga ega qatorlar qoladi:

Agar bitta varaqda 2 ta ustun bo'lsa, bir-biriga yaqin (qo'shni) yoki bir-biriga yaqin emas (qo'shni emas), keyin dublikatlarni olib tashlash jarayoni biroz murakkabroq bo'ladi. Ikki nusxadagi qiymatlar bilan butun qatorni olib tashlay olmaymiz, chunki bu ikkinchi ustundagi katakchalarni ham olib tashlaydi. Shunday qilib, ustunda faqat noyob yozuvlarni qoldirish uchun А, buni bajaring:

1. Faqat takroriy qiymatlarni ko'rsatish uchun jadvalni filtrlang va o'sha katakchalarni tanlang. Ularni sichqonchaning o'ng tugmasi bilan bosing va kontekst menyusidan tanlang Tarkibni tozalash (tarkibni aniq).
2. Filtrni tozalang.
3. Ustundagi barcha katakchalarni tanlang А, hujayradan boshlab A1 ma'lumotlarni o'z ichiga olgan eng pastgacha.
4. bosing ma'lumotlar (Ma'lumotlar) va bosing A dan Z gacha tartiblang (A dan Z gacha saralash). Ochilgan dialog oynasida tanlang Joriy tanlov bilan davom eting (Ko'rsatilgan tanlov doirasida saralash) va tugmani bosing qora (Tartiblash):
5. Formula bilan ustunni o'chiring, endi sizga kerak bo'lmaydi, bundan buyon sizda faqat noyob qiymatlar mavjud.
6. Mana, endi ustun А ustunda bo'lmagan faqat noyob ma'lumotlarni o'z ichiga oladi В:

Ko'rib turganingizdek, formulalar yordamida Excelda ikkita ustundan dublikatlarni olib tashlash unchalik qiyin emas.