Excel-де екі тізімді қалай салыстыруға болады

Бухгалтерия төлемдер тізімін, ал сатып алу бөлімі актілер тізімін жібереді. Бірі екіншісінен сәл өзгеше, бірақ қай жерде екені белгісіз. Қызметкерлер тізімі екі жүйеде қайшы келеді. Екі тізімді «көзбен» салыстыру жүздеген жолда сағаттарға созылады, оның үстіне нәтижеге сенім аз.

Excel мұны минуттарда істейді, егер тізімдерде бірегей кілт болса. Кілт — әр жолды анықтайтын мән: шарт нөмірі, табель нөмірі, ЖСН, артикул.

Мақалада екі тізімді салыстырудың негізгі тәсілін, формулаларды, санмен мысалды және жиі қателерді көрсетеміз. Соңында тегін дайын кесте туралы айтамыз.

1-қадам: кілтті таңдап, тазалаңыз

Салыстырудың ең көп қате беретін жері — бос орындар мен әріп регистрі. «Иванов » (соңында бос орын) мен «иванов» Excel үшін әртүрлі мән. Сондықтан кілтті алдымен тазалап алыңыз:

=UPPER(TRIM(A2))

TRIM артық бос орындарды алады, UPPER барлығын бір регистрге келтіреді. Нөмірлер мәтін ретінде де, сан ретінде де жазылса, =TEXT(A2;"0") арқылы бір пішімге келтіріңіз.

2-қадам: екінші тізімде бар ма?

Ең жеңіл тәсіл — COUNTIF:

=COUNTIF(Б!$A:$A;A2)

Нәтиже 0 болса, жазба тек бірінші тізімде. 1 болса, екеуінде де бар. 2 және одан көп болса, екінші тізімде қайталанады. Нәтижені мәтінге айналдыру үшін:

=IF(COUNTIF(Б!$A:$A;A2)=0;"Тек осында";"Екеуінде бар")

Дәл осыны екінші тізім үшін де жасаңыз, тек тізімдерді ауыстырыңыз. Сонда не «тек А-да», не «тек Б-да» болғанын көресіз.

3-қадам: кілт бірдей, мәні басқа

Кейде жазба екі тізімде де бар, бірақ сомасы немесе күні өзгеше. Бұл жағдайда VLOOKUP қолданыңыз:

=IF(ISNA(VLOOKUP(A2;Б!A:C;2;0));"Жоқ";IF(VLOOKUP(A2;Б!A:C;2;0)=B2;"Сәйкес";"Мәні өзгеше"))

Формула кілт бойынша екінші тізімнен мәнді алып, өзімен салыстырады. Үш нәтиже шығады: «Жоқ», «Сәйкес» немесе «Мәні өзгеше».

Санмен мысал

А тізімі (бухгалтерия) мен Б тізімі (акт тізімі).

Бес жолдың орнына бес мың жолда да нәтиже бірдей түсінікті. Айырмашылықтарды сүзгімен бөлек шығарып, тексеруге беріңіз: 1002 бойынша 5 000 ₸ айырмашылықты түсіндіру керек, 1003 пен 1004 бойынша құжат жоқ немесе кіргізілмеген.

Жиі кездесетін қателер

• Кілтті тазаламау. Бос орын, регистр, әріптердің нұсқасы (мысалы, Ё мен Е) жалған айырмашылық береді.
• Кілті қайталанатын тізімді салыстыру. Қайталануды алдымен белгілеңіз: =COUNTIF($A$2:$A$5000;A2)>1.
• Атау бойынша салыстыру. «Иванов И. И.» мен «Иванов Иван Иванович» бір адам екенін Excel білмейді. Кілт ретінде бірегей нөмірді алыңыз.
• Диапазонды тым қысқа қою. Деректер өссе, формула жетпей қалады, сондықтан бағандарды толық көрсетіңіз.
• Нәтижені тексермей сену. Үш-төрт жолды қолмен тексеріп көріңіз.

Нәтижемен жұмыс

Салыстыру аяқталған соң айырмашылықтарды сүзгімен бөлек алып, әр түріне өз шешімін қолданыңыз. «Тек А-да» жазбалар үшін бұл нақты жоқ екенін, әлде екінші тізімге енгізілмегенін білу керек. «Мәні өзгеше» жолдар үшін қай мәннің дұрыс екенін құжат бойынша анықтаңыз, содан кейін екі тізімді де түзетіңіз.

Тексеру кезінде жиі шығатын себептер: әртүрлі күнмен енгізу, жуықтау айырмашылығы, жылдың соңында жабылған жазба, жүйеге кеш жеткен құжат. Себепті жазып қойсаңыз, келесі салыстыру тезірек өтеді, өйткені қайталанатын мәселелер көзге түседі.

Тегін екі тізімді салыстыру үлгісі

Формулаларды өзіңіз жазбай-ақ дайын кестені қолдануға болады. Үлгі тегін.

• «А тізімі» және «Б тізімі» парақтары: кілт және екі қосымша мән, әрқайсысында 5000 жолға дейін.
• Кілтті автоматты тазалау: артық бос орындар, регистр, Ё/Е.
• Әр жол бойынша нәтиже: «Басқа тізімде бар», «Тек осында», «Бар, бірақ мәндері өзгеше», сондай-ақ кілт қайталануының белгісі.
• «Жиынтық» парағы: барлығы, сәйкес келгені, тек А-да, тек Б-да, айырмашылықтар және көшірмелер.
• «Тек А-да», «Тек Б-да», «Айырмашылықтар» парақтарында жол нөмірлері бар дайын тізімдер.

Салыстыру кілт тазаланғаннан кейін толық сәйкестік бойынша жүреді, анық емес іздеу жоқ. Кілт қайталанса, жазба белгіленеді, ал салыстыру екінші тізімдегі бірінші табылған сәйкестікпен жүреді. Файл қазақ тілінде, макростарсыз және сыртқы байланыссыз, Excel 2007 және одан жаңа нұсқаларда және LibreOffice-те ашылады.

Жиі қойылатын сұрақтар

Екі тізімді қосымша формуласыз салыстыруға бола ма?

Иә, Excel-де шартты пішімдеудегі «Қайталанатын мәндер» ережесін қолдануға болады. Бірақ ол тек бір бағанды көрсетеді және мәні өзгешеліктерді таппайды.

Мың жолдан көп тізімде формулалар баяулай ма?

Жүздеген мың жолда баяулайды. Бірнеше мың жолға Excel жеткілікті, ал одан да көп деректер болса, басқа құралды қарастырыңыз.

«Иванов И. И.» және «Иванов Иван Иванович» сәйкес келе ме?

Жоқ. Салыстыру кілттің толық сәйкестігіне құрылады. Адамдар үшін нөмірді (табель, ЖСН) кілт етіп алыңыз.

Нәтижені қайда жіберуге болады?

Айырмашылықтар тізімін жеке парақтан көшіріп, жауаптыға жіберіңіз: онда жол нөмірлері бар, сондықтан тексеру оңай.