Мазмұны
Бұл мақаланы оқуға шамамен 10 минут кетеді. Келесі 5 минут ішінде сіз Excel бағдарламасындағы екі бағанды оңай салыстыра аласыз және оларда көшірмелердің бар-жоғын білуге, оларды жоюға немесе түспен бөлектеуге болады. Сонымен, уақыт келді!
Excel - бұл үлкен көлемдегі деректерді жасауға және өңдеуге арналған өте қуатты және шынымен керемет қолданба. Егер сізде деректері бар бірнеше жұмыс кітаптары (немесе бір ғана үлкен кесте) болса, онда сіз 2 бағанды салыстырып, қайталанатын мәндерді тауып, содан кейін олармен бірдеңе жасағыңыз келуі мүмкін, мысалы, мазмұнын жою, бөлектеу немесе тазалау . Бағандар бір кестеде болуы мүмкін, іргелес немесе іргелес емес, 2 түрлі парақта немесе тіпті әртүрлі кітаптарда орналасуы мүмкін.
Бізде адамдардың аты бар 2 баған бар деп елестетіңіз - әр бағанға 5 атау A және бағандағы 3 атау B. Осы екі бағандағы атауларды салыстырып, көшірмелерін табу керек. Түсінгеніңіздей, бұл тек мысал ретінде алынған жалған деректер. Нақты кестелерде біз мыңдаған, тіпті он мыңдаған жазбалармен айналысамыз.
А нұсқасы: екі баған да бір парақта. Мысалы, баған A және баған B.
В нұсқасы: Бағандар әртүрлі парақтарда. Мысалы, баған A парақта Парақ2 және баған A парақта Парақ3.
Excel 2013, 2010 және 2007 нұсқаларында кірістірілген құрал бар Көшірмелерді жою (Көшірмелерді жою), бірақ бұл жағдайда ол күшсіз, себебі ол 2 бағандағы деректерді салыстыра алмайды. Оның үстіне, ол тек көшірмелерді жоя алады. Түстерді бөлектеу немесе өзгерту сияқты басқа опциялар жоқ. Және нүкте!
Әрі қарай, мен сізге қайталанатын жазбаларды табуға және жоюға мүмкіндік беретін Excel бағдарламасындағы екі бағанды салыстырудың ықтимал жолдарын көрсетемін.
Excel бағдарламасындағы 2 бағанды салыстырыңыз және формулаларды пайдаланып қайталанатын жазбаларды табыңыз
А нұсқасы: екі баған да бір парақта
- Бірінші бос ұяшықта (біздің мысалда бұл С1 ұяшығы) келесі формуланы жазамыз:
=IF(ISERROR(MATCH(A1,$B$1:$B$10000,0)),"Unique","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;$B$1:$B$10000;0));"Unique";"Duplicate")
Біздің формулада A1 бұл біз салыстыратын бірінші бағанның бірінші ұяшығы. $B$1 и $B$10000 бұл екінші бағанның бірінші және соңғы ұяшықтарының мекенжайлары, олармен салыстыруды орындаймыз. Абсолютті сілтемелерге назар аударыңыз – баған әріптері мен жол нөмірлерінің алдында доллар белгісі ($) болады. Формулаларды көшіру кезінде ұяшық мекенжайлары өзгеріссіз қалуы үшін абсолютті сілтемелерді қолданамын.
Бағандағы көшірмелерді тапқыңыз келсе B, сілтемелерді формула келесідей болатындай етіп өзгертіңіз:
=IF(ISERROR(MATCH(B1,$A$1:$A$10000,0)),"Unique","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(B1;$A$1:$A$10000;0));"Unique";"Duplicate")Оның орнына «тек« және »көшірме» Сіз өзіңіздің белгілеріңізді жаза аласыз, мысалы, «Табылмады« және »табылды“, немесе тек қалдырыңыз”көшірме' және екінші мәннің орнына бос орын таңбасын енгізіңіз. Соңғы жағдайда көшірмелері табылмаған ұяшықтар бос қалады және менің ойымша, деректердің бұл көрінісі әрі қарай талдау үшін ең қолайлы.
- Енді формуламызды бағандағы барлық ұяшықтарға көшіріп алайық C, бағандағы деректерді қамтитын төменгі жолға дейін A. Ол үшін тінтуір меңзерін ұяшықтың төменгі оң жақ бұрышына жылжытыңыз C1, көрсеткіш төмендегі суретте көрсетілгендей қара крест түрінде болады:Тінтуірдің сол жақ батырмасын басып тұрып, формуланы кірістіргіңіз келетін барлық ұяшықтарды ерекшелеп, жақтаудың шекарасын төмен сүйреңіз. Барлық қажетті ұяшықтар таңдалғанда, тінтуір түймесін босатыңыз:
Тінтуірдің сол жақ батырмасын басып тұрып, формуланы кірістіргіңіз келетін барлық ұяшықтарды ерекшелеп, жақтаудың шекарасын төмен сүйреңіз. Барлық қажетті ұяшықтар таңдалғанда, тінтуір түймесін босатыңыз:
Кеңес: Үлкен кестелерде пернелер тіркесімін пайдалансаңыз, формуланы көшіру жылдамырақ болады. Ұяшықты бөлектеңіз C1 және баспасөз Ctrl + C (формуланы алмасу буферіне көшіру үшін), содан кейін басыңыз Ctrl + Shift + End (C бағанындағы барлық бос емес ұяшықтарды таңдау үшін) және соңында түймесін басыңыз Ctrl + V (өрнекті барлық таңдалған ұяшықтарға енгізу үшін).
- Тамаша, енді барлық қайталанатын мәндер «көшірме":
В нұсқасы: екі баған әртүрлі парақтарда (әртүрлі жұмыс кітаптарында)
- Жұмыс парағындағы бірінші бос бағанның бірінші ұяшығында Парақ2 (біздің жағдайда бұл В бағаны) келесі формуланы енгізіңіз:
=IF(ISERROR(MATCH(A1,Sheet3!$A$1:$A$10000,0)),"","Duplicate")=ЕСЛИ(ЕОШИБКА(ПОИСКПОЗ(A1;Лист3!$A$1:$A$10000;0));"";"Duplicate")осында Парақ3 - 2-баған орналасқан парақтың аты, және $A$1:$A$10000 осы 1-бағандағы 2-ден соңғыға дейінгі ұяшық мекенжайлары.
- Формуланы бағандағы барлық ұяшықтарға көшіріңіз B (А нұсқасымен бірдей).
- Біз бұл нәтижені аламыз:
Табылған көшірмелерді өңдеу
Керемет, біз бірінші бағандағы жазбаларды таптық, олар екінші бағанда да бар. Енді олармен бірдеңе істеу керек. Кестедегі барлық қайталанатын жазбаларды қолмен қарау өте тиімсіз және тым көп уақытты алады. Жақсырақ жолдар бар.
А бағанында тек қайталанатын жолдарды көрсетіңіз
Бағандарда тақырыптар болмаса, оларды қосу керек. Мұны істеу үшін курсорды бірінші жолды көрсететін санға қойыңыз, ол төмендегі суретте көрсетілгендей қара көрсеткіге айналады:
Тінтуірдің оң жақ түймешігімен басып, контекстік мәзірден таңдаңыз енгізу (Кірістіру):
Бағандарға атау беріңіз, мысалы, «Толық аты-жөніңіз« және »Көшірме?» Содан кейін қойындыны ашыңыз мәліметтер (Деректер) және түймесін басыңыз фильтр (Сүзгі):
Осыдан кейін « жанындағы кішкентай сұр көрсеткіні басыңызКөшірме?« сүзгі мәзірін ашу үшін; қоспағанда осы тізімдегі барлық элементтердің құсбелгісін алып тастаңыз көшірметүймесін басып, түймесін басыңыз OK.
Барлығы, енді сіз бағанның сол элементтерін ғана көресіз А, олар бағанда қайталанады В. Біздің оқу үстелімізде мұндай ұяшықтар тек екі ғана, бірақ сіз түсінгеніңіздей, іс жүзінде олардың саны көп болады.
Бағанның барлық жолдарын қайта көрсету үшін А, бағандағы сүзгі белгісін басыңыз В, енді шағын көрсеткі бар шұңқырға ұқсайды және таңдаңыз Барлығын таңдау (Барлығын таңдаңыз). Немесе нұқу арқылы таспа арқылы дәл осылай жасауға болады мәліметтер (Деректер) > Таңдау & Сүзгі (Сұрыптау және сүзу) > айқын (Таза) төмендегі скриншотта көрсетілгендей:
Түсті өзгертіңіз немесе табылған көшірмелерді бөлектеңіз
Егер ескертулер «көшірме” сіздің мақсаттарыңыз үшін жеткіліксіз және сіз басқа қаріп түсімен, бояу түсімен немесе басқа әдіспен қайталанатын ұяшықтарды белгілегіңіз келеді…
Бұл жағдайда көшірмелерді жоғарыда көрсетілгендей сүзіп, барлық сүзілген ұяшықтарды таңдап, басыңыз Ctrl + 1диалогты ашу үшін Ұяшықтарды форматтау (ұя форматы). Мысал ретінде көшірмелері бар жолдардағы ұяшықтардың бояу түсін ашық сарыға өзгертейік. Әрине, құралдың көмегімен бояу түсін өзгертуге болады Толтырыңыз (Түсті толтыру) қойындысы Home (Басты) бірақ диалогтық терезенің артықшылығы Ұяшықтарды форматтау (Ұяшық пішімі) бір уақытта барлық пішімдеу опцияларын конфигурациялауға болады.
Енді сіз көшірмелері бар ұяшықтарды міндетті түрде жіберіп алмайсыз:
Бірінші бағандағы қайталанатын мәндерді жою
Тек қайталанатын мәндері бар ұяшықтар көрсетілетін етіп кестені сүзіп, сол ұяшықтарды таңдаңыз.
Егер сіз салыстырып отырған 2 баған әртүрлі парақтарда болса, яғни әртүрлі кестелерде таңдалған ауқымды тінтуірдің оң жақ түймешігімен басып, таңдаңыз Жолды жою (Жолды алып тастау):
баспасөз OKExcel бағдарламасы бүкіл парақ жолын шынымен жойғыңыз келетінін растауды сұрағанда, содан кейін сүзгіні өшіріңіз. Көріп отырғаныңыздай, бірегей мәндері бар жолдар ғана қалады:
2 баған бір парақта болса, бір-біріне жақын (іргелес) немесе бір-біріне жақын емес (іргелес емес), онда көшірмелерді жою процесі сәл күрделірек болады. Қайталанатын мәндері бар жолды толығымен жоя алмаймыз, себебі бұл екінші бағандағы ұяшықтарды да жояды. Осылайша, бағанда тек бірегей жазбаларды қалдыру А, Мұны істе:
- Тек қайталанатын мәндерді көрсету үшін кестені сүзіп, сол ұяшықтарды таңдаңыз. Оларды тінтуірдің оң жақ түймешігімен басып, контекстік мәзірден таңдаңыз Мазмұнды тазалау (мазмұны анық).
- Сүзгіні тазалаңыз.
- Бағандағы барлық ұяшықтарды таңдаңыз А, ұяшықтан басталады A1 деректерді қамтитын түбіне дейін.
- басыңыз мәліметтер (Деректер) және түймесін басыңыз А-ны Z-ге дейін сұрыптаңыз (А-дан Я-ға дейін сұрыптау). Ашылған диалогтық терезеде таңдаңыз Ағымдағы таңдауды жалғастырыңыз (Көрсетілген таңдау шегінде сұрыптау) және түймешігін басыңыз қара (Сұрыптау):
- Формула бар бағанды жойыңыз, ол сізге қажет емес, енді сізде тек бірегей мәндер болады.
- Міне, енді бағана А бағанда жоқ бірегей деректерді ғана қамтиды В:
Көріп отырғаныңыздай, формулаларды пайдаланып Excel бағдарламасындағы екі бағаннан көшірмелерді жою қиын емес.