Microsoft Excel -ning ko'pgina imkoniyatlaridan biri bu ikkita ma'lumot ro'yxatini solishtirish, ro'yxatlar orasidagi moslikni aniqlash va qaysi elementlar faqat bitta ro'yxatda topilganligini aniqlashdir. Bu moliyaviy yozuvlarni taqqoslashda yoki ma'lum bir nomning ma'lumotlar bazasida mavjudligini tekshirishda foydalidir. Siz mos keladigan yoki mos kelmaydigan yozuvlarni aniqlash va belgilash uchun MATCH funktsiyasidan foydalanishingiz yoki COUNTIF funktsiyasi bilan konditsioner formatlashdan foydalanishingiz mumkin. Quyidagi qadamlar sizning ma'lumotlaringizga mos kelishi uchun har biridan qanday foydalanish kerakligini aytadi.
Qadamlar
2 -usul 1: MATCH funktsiyasi bilan yozuvlarni aniqlash
Qadam 1. Ma'lumotlar ro'yxatini bitta ishchi varaqqa nusxalash
Excel bitta ishchi daftarchasida yoki bir nechta ishchi daftarchasida bir nechta ishchi varaqlari bilan ishlashi mumkin, lekin ularning ma'lumotlarini bitta ishchi varaqqa ko'chirsangiz, ro'yxatlarni solishtirish osonroq bo'ladi.
Qadam 2. Har bir ro'yxat elementiga o'ziga xos identifikator bering
Agar sizning ikkita ro'yxatingiz ularni aniqlashning umumiy usulini baham ko'rmasa, har bir ma'lumotlar ro'yxatiga ushbu elementni Excelga aniqlaydigan qo'shimcha ustun qo'shishingiz kerak bo'ladi, shunda u berilgan ro'yxatdagi element element bilan bog'liqligini ko'radi. boshqa ro'yxatda. Ushbu identifikatorning tabiati siz mos keladigan ma'lumot turiga bog'liq bo'ladi. Har bir ustun ro'yxati uchun identifikator kerak bo'ladi.
- Ma'lum bir davr bilan bog'liq moliyaviy ma'lumotlar uchun, masalan, soliq yozuvlari, bu aktivning tavsifi, aktivning sotib olingan sanasi yoki ikkalasi bo'lishi mumkin. Ba'zi hollarda yozuv kod raqami bilan aniqlanishi mumkin; ammo, agar ikkala tizim uchun ham xuddi shu tizim ishlatilmasa, bu identifikator mos bo'lmagan o'yinlarni yaratishi yoki bajarilishi lozim bo'lgan moslamalarni e'tiborsiz qoldirishi mumkin.
- Ba'zi hollarda, siz ob'ektlarni bitta ro'yxatdan olib, ularni boshqa ro'yxatdagi elementlar bilan birlashtira olasiz, masalan, jismoniy aktivlarning tavsifi va sotib olingan yili. Bunday identifikatorni yaratish uchun siz ampersand (&) yordamida ikki yoki undan ko'p hujayradan ma'lumotlarni birlashtirasiz (qo'shasiz, birlashtirasiz). F3 katakchasidagi element tavsifini G3 katakchasidagi sana bilan bo'sh joy bilan birlashtirish uchun siz "= F3 &" "& G3" formulasini o'sha qatorning boshqa katagiga kiritasiz, masalan, E3. Agar siz identifikatorga faqat yilni kiritishni xohlasangiz (chunki bitta ro'yxatda to'liq sanalar, ikkinchisida esa faqat yillar ishlatilgan bo'lsa), siz E3 katakchasiga '= F3 & "" & YEAR (G3)' ni kiritib YIL funktsiyasini kiritgan bo'lar edingiz. (Bitta tirnoqni kiritmang, ular faqat misolni ko'rsatish uchun mavjud.)
- Formulani yaratganingizdan so'ng, formulali katakchani tanlab, formulani ko'chirmoqchi bo'lgan ustunning boshqa katakchalari ustidan sudrab, identifikator ustunining boshqa barcha hujayralariga ko'chirishingiz mumkin. Sichqoncha tugmachasini qo'yib yuborganingizda, siz sudrab o'tgan har bir katakka formulalar to'ldiriladi va kataklarga mos yozuvlar bir qatorda mos katakchalarga o'rnatiladi.
Qadam 3. Iloji boricha ma'lumotlarni standartlashtirish
Aql buni "Inc" deb tan oladi. va "Birlashtirilgan" xuddi shu narsani anglatadi, Excel sizda bir yoki boshqa so'zni qayta formatlamaguningizcha emas. Xuddi shunday, siz $ 11, 950 va $ 11, 999.95 kabi qiymatlarni bir -biriga mos keladigan darajada yaqin deb hisoblashingiz mumkin, lekin Excel buni aytmaguningizcha bo'lmaydi.
- Siz ba'zi qisqartmalar bilan shug'ullanishingiz mumkin, masalan, "Kompaniya" uchun "Co" va "Inc" uchun "Qo'shilgan belgilarni qisqartirish uchun LEFT string funktsiyasidan foydalanib." Assn "kabi boshqa qisqartmalar bo'lishi mumkin. Ma'lumotlarni kiritish uslubi bo'yicha ko'rsatma tuzish va keyin noto'g'ri formatlarni qidirish va tuzatish uchun dastur yozish orqali hal qilindi.
- Pochta indekslari kabi, ba'zi yozuvlar ZIP+4 qo'shimchasini o'z ichiga olgan, boshqalari kiritmagan bo'lsa, siz faqat LEFT mag'lubiyat funktsiyasidan foydalanib, faqat birlamchi pochta kodlarini moslashtirishingiz mumkin. Excelga yaqin, lekin bir xil bo'lmagan raqamli qiymatlarni tanib olish uchun ROUND funktsiyasidan foydalanib, yopilish qiymatlarini bir xil raqamga yaxlitlash va ularga mos kelish mumkin.
- Qo'shimcha bo'shliqlar, masalan, so'zlar orasidagi ikkita bo'shliqni yozish o'rniga, TRIM funktsiyasi yordamida olib tashlash mumkin.
Qadam 4. Taqqoslash formulasi uchun ustunlar yarating
Ro'yxat identifikatorlari uchun ustunlar yaratishingiz kerak bo'lganidek, siz uchun taqqoslaydigan formulalar uchun ustunlar yaratishingiz kerak bo'ladi. Har bir ro'yxat uchun sizga bitta ustun kerak bo'ladi.
Siz ushbu ustunlarni "Yo'qolganmi?"
Qadam 5. Har bir yacheykaga taqqoslash formulasini kiriting
Taqqoslash formulasi uchun siz boshqa Excel funktsiyasiga joylashtirilgan MATCH funktsiyasidan foydalanasiz, ISNA.
- Formula "= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))" shaklini oladi, bu erda birinchi ro'yxatning identifikator ustunining katakchasi ikkinchi ro'yxatdagi har bir identifikator bilan solishtiriladi. qarang, u ulardan biriga mos keladimi? Agar u mos kelmasa, yozuv yo'qoladi va "HAQIQAT" so'zi o'sha katakchada ko'rsatiladi. Agar u mos keladigan bo'lsa, yozuv mavjud va "FALSE" so'zi ko'rsatiladi. (Formulani kiritishda, tirnoqli tirnoqlarni kiritmang.)
- Formulani hujayra identifikatori formulasidan nusxa ko'chirganingizdek, ustunning qolgan hujayralariga ham ko'chirishingiz mumkin. Bu holda, identifikator yacheykasi uchun faqat uyali mos yozuvlar o'zgaradi, chunki dollar belgilarini ikkinchi hujayra identifikatorlari ro'yxatidagi birinchi va oxirgi yacheykalar uchun satr va ustun moslamalari oldiga qo'yib, ularni mutlaq havolaga aylantiradi.
- Birinchi ro'yxatning taqqoslash formulasini ikkinchi ro'yxatning ustuni birinchi katagiga ko'chirishingiz mumkin. Keyin "G3" ikkinchi ro'yxatning birinchi identifikator yacheykasiga havola bilan almashtirilishi va "$ L $ 3: $ L $ 14" ning birinchi va oxirgi identifikator katakchasi bilan almashtirilishi uchun siz hujayra havolalarini tahrir qilishingiz kerak bo'ladi. ikkinchi ro'yxat. (Dollar belgilarini va yo'g'on ichakni yolg'iz qoldiring.) Keyin bu tahrirlangan formulani ikkinchi ro'yxatning taqqoslash qatoridagi qolgan katakchalarga ko'chirishingiz mumkin.
Qadam 6. Agar kerak bo'lsa, mos kelmaydigan qiymatlarni ko'rish uchun ro'yxatlarni tartiblang
Agar sizning ro'yxatlaringiz katta bo'lsa, ularga mos kelmaydigan barcha qiymatlarni birlashtirish uchun ularni saralashingiz kerak bo'ladi. Pastki bosqichdagi ko'rsatmalar qayta hisoblash xatolariga yo'l qo'ymaslik uchun formulalarni qiymatlarga aylantiradi va agar sizning ro'yxatlaringiz katta bo'lsa, qayta hisoblashning uzoq vaqtini oldini oladi.
- Uni tanlash uchun sichqonchani ro'yxatdagi barcha katakchalarga suring.
- Excel 2003 -dagi Tartibga solish menyusidan yoki Excel 2007 yoki 2010 -dagi Bosh tasma clipboardining Nusxalash -ni tanlang.
- Excel 2003 -dagi Tartibga solish menyusidan yoki Excel 2007 yoki 2010 -ning Bosh sahifa tasmasining clipboard -dagi Paste -ni ochish tugmachasidan Maxsus joylashtirish -ni tanlang.
- Maxsus joylashtirish oynasida "Turli xil joylashtirish" ro'yxatidan "Qiymatlar" ni tanlang. Muloqot oynasini yopish uchun OK ni bosing.
- Excel 2003 -dagi ma'lumotlar menyusidan yoki Excel 2007 yoki 2010 -dagi ma'lumotlar tasmasining saralash va filtrlash guruhidan Saralash -ni tanlang.
- Saralash bo'yicha muloqot oynasidagi "Ma'lumotlar diapazonim bor" ro'yxatidan "Sarlavha qatori" ni tanlang, "Yo'qolganmi?" (yoki taqqoslash ustunining sarlavhasini aslida bergan ismingiz) va OK ni bosing.
- Boshqa ro'yxat uchun ushbu amallarni takrorlang.
7-qadam. Mos kelmaydigan narsalarni vizual tarzda solishtiring va nima uchun ular mos kelmasligini bilib oling
Yuqorida ta'kidlab o'tilganidek, Excel taxminiy ma'lumotlarni qidirish uchun o'rnatmagan bo'lsangiz, aniq ma'lumotlar mosligini qidirishga mo'ljallangan. Sizning mos kelmasligingiz tasodifan harflar yoki raqamlarni ko'chirish kabi oddiy bo'lishi mumkin. Bu mustaqil tekshirishni talab qiladigan narsa bo'lishi mumkin, masalan, ro'yxatga olingan aktivlar birinchi navbatda hisobot berilishi kerakligini tekshirish.
2 -usul 2: COUNTIF bilan shartli formatlash
Qadam 1. Ma'lumotlar ro'yxatini bitta ishchi varaqqa nusxalash
Qadam 2. Qaysi ro'yxatda mos yoki mos bo'lmagan yozuvlarni ajratib ko'rsatish kerakligini hal qiling
Agar siz faqat bitta ro'yxatda yozuvlarni ajratib ko'rsatmoqchi bo'lsangiz, ehtimol siz ushbu ro'yxatga xos bo'lgan yozuvlarni ajratib ko'rsatishni xohlaysiz; ya'ni boshqa ro'yxatdagi yozuvlarga mos kelmaydigan yozuvlar. Agar siz ikkala ro'yxatdagi yozuvlarni ajratmoqchi bo'lsangiz, siz bir -biriga mos yozuvlarni ajratib ko'rsatishni xohlaysiz. Ushbu misol uchun, biz birinchi ro'yxat G3 dan G14 gacha bo'lgan hujayralarni, ikkinchi ro'yxat esa L3 dan L14 gacha bo'lgan hujayralarni o'z ichiga oladi deb taxmin qilamiz.
Qadam 3. Ro'yxatdagi noyob yoki mos keladigan elementlarni ajratib ko'rsatmoqchi bo'lgan narsalarni tanlang
Agar siz ikkala ro'yxatda ham mos keladigan elementlarni ajratib ko'rsatishni xohlasangiz, siz ro'yxatlarni birma -bir tanlab, har bir ro'yxatga taqqoslash formulasini (keyingi bosqichda tasvirlangan) qo'llashingiz kerak bo'ladi.
Qadam 4. Tegishli taqqoslash formulasini qo'llang
Buni amalga oshirish uchun siz Excel versiyasidagi shartli formatlash dialogiga kirishingiz kerak bo'ladi. Excel 2003 -da siz buni "Format" menyusidan shartli formatlashni tanlash orqali amalga oshirasiz, Excel 2007 va 2010 -da "Uy" tasmasi "Uslublar" guruhidagi shartli formatlash tugmasini bosasiz. Qoidalar turini "Formula" sifatida tanlang va formulani "Qoidalar tavsifini tahrirlash" maydoniga kiriting.
- Agar siz birinchi ro'yxatga xos bo'lgan yozuvlarni ajratib ko'rsatishni istasangiz, formula "= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)" bo'ladi, ikkinchi ro'yxatning yacheykalari diapazoni mutlaq qiymatlar va mos yozuvlar sifatida ko'rsatiladi. nisbiy qiymat sifatida birinchi ro'yxatning birinchi katagiga. (Yaqin tirnoqlarni kiritmang.)
- Agar siz ikkinchi ro'yxatga xos bo'lgan yozuvlarni ajratib ko'rsatmoqchi bo'lsangiz, formula "= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)" bo'ladi, birinchi ro'yxatning kataklari diapazoni mutlaq qiymatlar va mos yozuvlar sifatida ko'rsatiladi. nisbiy qiymat sifatida ikkinchi ro'yxatning birinchi katagiga. (Yaqin tirnoqlarni kiritmang.)
- Agar siz boshqa ro'yxatdagi har bir ro'yxatdagi yozuvlarni ajratib ko'rsatishni xohlasangiz, sizga ikkita formulalar kerak bo'ladi, biri birinchi ro'yxat uchun, ikkinchisi. Birinchi ro'yxatning formulasi "= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)", ikkinchi ro'yxatning formulasi esa COUNTIF ($ G $ 3: $ G $ 14, L3> 0) "dir. Yuqorida aytib o'tilganidek, siz formulani qo'llash uchun birinchi ro'yxatni tanlaysiz va keyin formulani qo'llash uchun ikkinchi ro'yxatni tanlang.
- Belgilangan yozuvlarni ta'kidlashni xohlagan formatni qo'llang. Muloqot oynasini yopish uchun OK ni bosing.
Video - bu xizmatdan foydalanib, ba'zi ma'lumotlar YouTube bilan bo'lishishi mumkin
Maslahatlar
- COUNTIF shartli formatlash usuli bilan hujayra havolasini ishlatishning o'rniga, siz qidiriladigan qiymatni kiritishingiz va bu misollar uchun bir yoki bir nechta ro'yxatni belgilashingiz mumkin.
- Taqqoslash shakllarini soddalashtirish uchun siz ro'yxatingiz uchun "List1" va "List2" kabi nomlar yaratishingiz mumkin. Keyin, formulalar yozilganda, bu ro'yxat nomlari yuqoridagi misollarda ishlatilgan mutlaq hujayra diapazonlarini almashtirishi mumkin.
