Excelda ikki roʻyxatni qanday solishtirish va farqlarni topish

Ikki yuklamani solishtirish kerak: kadrlar boʻlimidagi xodimlar va ish haqi tizimidagi xodimlar, yetkazib beruvchining dalolatnomalari va sizning toʻlovlaringiz, ikki reyestr. Koʻz bilan solishtirish uzoq davom etadi va xatosiz chiqmaydi. Yuzlab qatorda bitta farqni topish esa deyarli imkonsiz.

Excelda buni formulalar bilan bir necha daqiqada qilish mumkin. Quyida ikki roʻyxatni solishtirishning ishonchli usulini koʻrsatamiz: avval tayyorgarlik, keyin formulalar, oxirida odatiy tuzoqlar.

Solishtirishdan oldin: kalitni tayyorlang

Solishtirish uchun kalit kerak — har bir yozuvni noyob aniqlaydigan qiymat: raqam, kod, pasport seriyasi, shartnoma raqami. Ism-familiya yomon kalit, chunki takrorlanadi va har xil yoziladi.

Koʻpchilik xatolar kalit notoʻgʻri yozilganidan chiqadi. Masalan, «AB-123» va «ab-123 » (oxirida boʻshliq bilan) Excel uchun ikki xil qiymat. Shuning uchun kalitni avval tozalang:

=UPPER(TRIM(A2))

TRIM ortiqcha boʻshliqlarni olib tashlaydi, UPPER esa registrni bir xil qiladi. Tozalangan kalitni alohida ustunga yozing.

Birinchi usul: COUNTIF

Roʻyxat A ning kaliti A ustunida, roʻyxat B ning kaliti alohida varaqda boʻlsin. A dagi har bir kalit B da bormi?

=IF(COUNTIF(B!A:A;A2)>0;"Ikkalasida bor";"Faqat A da")

Xuddi shuni B varagʻida teskari yoʻnalishda qiling: «Faqat B da» qatorlarni topasiz. COUNTIF tez va tushunarli, lekin faqat bor-yoʻqligini aytadi.

Ikkinchi usul: VLOOKUP bilan qiymatlarni solishtirish

Kalit mos kelganda ham, qiymatlar (masalan, summa) farq qilishi mumkin. Roʻyxat A da kalit A2, summa B2; roʻyxat B da kalit A ustunida, summa B ustunida:

=IFERROR(IF(VLOOKUP(A2;B!A:B;2;FALSE)=B2;"Mos";"Qiymat farq qiladi");"Faqat A da")

Bu bitta formula uch holatni ajratadi: kalit topilmadi, topildi va qiymat bir xil, topildi va qiymat boshqa.

Misol. Ikki roʻyxat, kalit — tabel raqami, qiymat — oylik.

Shunday qilib, 103-xodim ikkinchi tizimga kiritilmagan, 104 esa birinchisida yoʻq, 102 da esa summa mos kelmaydi. Aynan shu uch toifani tekshirish kerak.

Takrorlarni tekshiring

Kalit roʻyxatning ichida takrorlansa, VLOOKUP faqat birinchisini topadi, qolganlari esa sezilmay qoladi. Takrorlarni oldindan belgilang:

=IF(COUNTIF($A$2:$A$5000;A2)>1;"Takror";"")

Takror topilsa, avval uni tushunib oling: bu xato yozuvmi yoki bitta kalitga bir nechta qator tegishlimi (masalan, bir shartnomaga bir nechta toʻlov). Ikkinchi holda solishtirishdan oldin qiymatlarni SUMIF bilan kalit boʻyicha yigʻing.

Odatiy tuzoqlar

• Matn va son. «101» matn koʻrinishida va 101 son koʻrinishida teng emas. Ikkala roʻyxatda ham bir xil format boʻlsin.
• Ortiqcha boʻshliqlar va registr. Yuqorida TRIM va UPPER buning uchun.
• Yashirin belgilar. Boshqa tizimdan olingan maʼlumotda koʻrinmaydigan belgilar boʻlishi mumkin, ularni CLEAN olib tashlaydi.
• Qisman mos kelish. «Karimov A. O.» va «Karimov Anvar Olimovich» formulada teng emas. Shuning uchun ism emas, raqamli kalit tanlang.
• Asl faylni buzish. Asl roʻyxatlarni oʻzgartirmang: solishtirishni nusxada qiling.

Tayyor shablon: Ikki roʻyxatni solishtirish

Formulalarni har safar qayta yozishni xohlamasangiz, bepul shablonni oling:

• «A roʻyxati» va «B roʻyxati» varaqlariga kalit va ikkita qoʻshimcha qiymat qoʻyiladi, har birida 5000 qatorgacha.
• Kalit ortiqcha boʻshliqlardan va registrdan avtomatik tozalanadi.
• Har bir qator boʻyicha natija chiqadi: «Boshqa roʻyxatda bor», «Faqat shu yerda» yoki «Bor, lekin qiymatlar farq qiladi». Kalit takrorlari belgilanadi.
• «Xulosa» varagʻida jami, mos kelgani, faqat A da, faqat B da, farqlar va dublikatlar soni koʻrinadi.
• «Faqat A da», «Faqat B da» va «Farqlar» varaqlarida tayyor roʻyxatlar va qator raqamlari bor.

Shablon kalitni tozalagandan keyin toʻliq moslik boʻyicha solishtiradi, noaniq qidiruv yoʻq. Yaʼni «Karimov A. O.» va «Karimov Anvar Olimovich»ni bir kishi deb topmaydi. Fayl Excel 2007 va undan yangi versiyalarda hamda LibreOfficeʼda ishlaydi, tashqi bogʻlanishlarsiz. Fayl oʻzbek tilida.

Koʻp beriladigan savollar

Ikki ustunni bitta roʻyxat ichida qanday solishtirish mumkin?

Birinchi ustundagi qiymat ikkinchi ustunda bormi, shuni COUNTIF bilan tekshiring: =IF(COUNTIF(B:B;A2)>0;"Bor";"Yoʻq"). Yoki shartli formatlashda «Takrorlanuvchi qiymatlar» qoidasini qoʻllang.

Farq qanday topilsa, nima qilish kerak?

Avval manbada tekshiring: koʻpincha yozuv boshqa tizimga kiritilmagan yoki boshqacha yozilgan. Faqat shundan keyin tuzatish kiriting.

Katta roʻyxatlarda Excel sekinlashadimi?

COUNTIF va VLOOKUP minglab qatorda ham ishlaydi, lekin butun ustun oʻrniga aniq diapazon (masalan, A2:A5000) bering va ortiqcha formatlashni olib tashlang.

Roʻyxatlarni solishtirish uchun qaysi funksiya yaxshi?

Hozirgi Excelda XLOOKUP ham bor, ammo eski versiyalarda ishlamaydi. VLOOKUP va COUNTIF har yerda ishlaydi.