Microsoft Excel -дің көптеген мүмкіндіктерінің бірі - екі тізімдегі деректерді салыстыру, тізімдер арасындағы сәйкестікті анықтау және тек бір тізімнен табылған элементтерді анықтау. Бұл қаржылық есептерді салыстыру немесе белгілі бір атаудың дерекқорда бар -жоғын тексеру кезінде пайдалы. Сәйкес келетін немесе сәйкес келмейтін жазбаларды анықтау және белгілеу үшін MATCH функциясын қолдануға болады немесе COUNTIF функциясымен кондиционерлеу пішімін қолдануға болады. Келесі қадамдар деректерді сәйкестендіру үшін әрқайсысын қалай пайдалану керектігін айтады.
Қадамдар
2 -ші әдіс 1: MATCH функциясымен жазбаларды анықтау
Қадам 1. Деректер тізімін бір жұмыс парағына көшіріңіз
Excel бір жұмыс кітабында немесе бірнеше жұмыс кітабында бірнеше жұмыс парақтарымен жұмыс жасай алады, бірақ олардың ақпаратын бір жұмыс парағына көшірсеңіз, тізімдерді салыстыруды жеңілдетесіз.
Қадам 2. Әрбір тізім элементіне бірегей идентификатор беріңіз
Егер сіздің екі тізіміңіз оларды анықтаудың ортақ әдісін бөліспесе, сізге берілген тізімдегі элементтің бір элементке қатысы бар -жоғын білу үшін Excel -ге сол элементті анықтайтын әрбір деректер тізіміне қосымша баған қосу қажет болуы мүмкін. басқа тізімде. Бұл идентификатордың сипаты сәйкес келетін деректердің түріне байланысты болады. Әрбір баған тізімі үшін идентификатор қажет болады.
- Белгілі бір кезеңмен байланысты қаржылық деректер үшін, мысалы, салықтық есепке алу, бұл активтің сипаттамасы, активтің сатып алынған күні немесе екеуі де болуы мүмкін. Кейбір жағдайларда жазба код нөмірімен анықталуы мүмкін; алайда, егер бір жүйе екі тізімге де қолданылмаса, бұл сәйкестендіргіш сәйкестіктер жоқ жерде жасалуы мүмкін немесе сәйкес келмеуі мүмкін.
- Кейбір жағдайларда сіз бір тізімнен элементтерді алып, оларды басқа тізімдегі элементтермен біріктіре аласыз, мысалы, физикалық активтердің сипаттамасы мен сатып алынған жылы сияқты идентификатор жасау. Мұндай идентификаторды құру үшін сіз екі және одан да көп ұяшықтардағы деректерді амперсанд (&) көмегімен біріктіресіз (қосасыз, біріктіресіз). F3 ұяшығындағы элементтің сипаттамасын бос орынмен бөлінген G3 ұяшығындағы күнмен біріктіру үшін сіз сол жолдағы басқа ұяшыққа '= F3 & «» & G3' формуласын енгізесіз, мысалы E3. Егер сіз идентификаторға тек қана жыл енгізгіңіз келсе (бір тізімде толық күндер пайдаланылады, ал екіншісінде тек жылдар ғана пайдаланылады), сіз Е3 ұяшығына '= F3 & «» & YEAR (G3)' енгізу арқылы ЖЫЛ функциясын қосар едіңіз. (Жалғыз тырнақшаларды қоспаңыз, олар тек мысалды көрсету үшін ғана бар.)
- Формуланы жасағаннан кейін, формуласы бар ұяшықты таңдап, формуланы көшіргіңіз келетін бағанның басқа ұяшықтарына апару арқылы оны идентификатор бағанының барлық басқа ұяшықтарына көшіруге болады. Тінтуір түймесін босатқан кезде, сіз апарған әрбір ұяшық формуламен толтырылады, ұяшық сілтемелері сол қатардағы сәйкес ұяшықтарға реттеледі.
Қадам 3. Мүмкіндігінше деректерді стандарттау
Ақыл «Инк.» Екенін мойындайды. және «кіріктірілген» бір нәрсені білдіреді, Excel сізде бір сөзді немесе басқа сөзді қайта пішімдемейінше, олай етпейді. Дәл осылай сіз $ 11, 950 және $ 11, 999,95 сияқты мәндерді сәйкестендіре алатындай жақын деп есептей аласыз, бірақ Excel сіз айтпасаңыз болмайды.
- Қосымша таңбаларды қысқарту үшін LEFT жолының функциясын қолдана отырып, «Компания» және «Инк» үшін «Co» сияқты кейбір аббревиатуралармен айналысуға болады. Басқа ассоциациялар, мысалы, «Ассоциация» үшін «Assn» сияқты болуы мүмкін. Деректерді енгізу стилі бойынша нұсқаулық құру, содан кейін дұрыс емес форматтарды іздеу және түзету үшін бағдарлама жазу арқылы шешілді.
- Пост индексі сияқты, кейбір жазбаларда ZIP+4 жұрнағы бар, ал басқаларында жоқ - сандар тізбегі үшін, тек бастапқы пошталық индекстерді тану және сәйкестендіру үшін LEFT жол функциясын қайтадан пайдалануға болады. Excel -ге жақын, бірақ бірдей емес сандық мәндерді тану үшін, ROUND функциясын қолданып, жақын мәндерді сол санға дөңгелектеп, оларға сәйкестендіруге болады.
- Сөздердің арасына бір емес, екі бос орын енгізу сияқты қосымша бос орындарды TRIM функциясы арқылы жоюға болады.
Қадам 4. Салыстыру формуласы үшін бағандар жасаңыз
Тізім идентификаторлары үшін бағандарды жасау қажет болған сияқты, сіз үшін салыстыруды жасайтын формула үшін бағандар жасау қажет болады. Әр тізім үшін сізге бір баған қажет.
Сіз бұл бағандарды «Жетіспедіңіз бе?» Деген сияқты белгілермен белгілегіңіз келеді
Қадам 5. Әр ұяшыққа салыстыру формуласын енгізіңіз
Салыстыру формуласы үшін сіз басқа Excel функциясына кірістірілген MATCH функциясын қолданасыз, ISNA.
- Формула «= ISNA (MATCH (G3, $ L $ 3: $ L $ 14, FALSE))» формасын алады, мұнда бірінші тізімдегі идентификатор бағанының ұяшығы екінші тізімдегі әрбір идентификатормен салыстырылады. олардың біреуіне сәйкес келетінін қараңыз. Егер ол сәйкес келмесе, онда жазба жоқ болады және «TRUE» сөзі сол ұяшықта көрсетіледі. Егер ол сәйкес келсе, жазба бар және «ЖАЛҒАН» сөзі көрсетіледі. (Формуланы енгізген кезде қосымша тырнақшаларды қоспаңыз.)
- Сіз формуланы бағанның қалған ұяшықтарына ұяшық идентификаторының формуласын көшіргендей көшіре аласыз. Бұл жағдайда идентификатор ұяшығына тек ұяшық сілтемесі өзгереді, себебі доллар белгілерін екінші ұяшық идентификаторларының тізіміндегі бірінші және соңғы ұяшықтар үшін жолдың және бағанның сілтемелерінің алдына қою оларды абсолютті сілтеме етеді.
- Бірінші тізімдегі салыстыру формуласын екінші тізімдегі бағанның бірінші ұяшығына көшіруге болады. «G3» екінші тізімдегі бірінші идентификатор ұяшығына сілтеме мен «$ L $ 3: $ L $ 14» орнына бірінші және соңғы идентификатор ұяшығына ауыстырылатын етіп ұяшық сілтемелерін өңдеу қажет болады. екінші тізім. (Доллар белгісі мен қос нүктені жалғыз қалдырыңыз.) Содан кейін осы өңделген формуланы екінші тізімдегі салыстыру жолындағы қалған ұяшықтарға көшіруге болады.
Қадам 6. Қажет болса, сәйкес келмейтін мәндерді оңай көру үшін тізімдерді сұрыптаңыз
Егер сіздің тізімдеріңіз үлкен болса, сәйкес келмейтін барлық мәндерді біріктіру үшін оларды сұрыптау қажет болуы мүмкін. Төмендегі ішкі қадамдардағы нұсқаулар формулаларды мәндерді қайта есептеуге қателіктер жібермеу үшін түрлендіреді, ал егер сіздің тізімдеріңіз үлкен болса, ұзақ қайта есептеуден аулақ болады.
- Оны таңдау үшін тінтуірді тізімдегі барлық ұяшықтардың үстіне апарыңыз.
- Excel 2003 бағдарламасындағы Өңдеу мәзірінен немесе Excel 2007 немесе 2010 бағдарламасындағы Басты таспаның Алмасу буферінің тобынан Көшіру таңдаңыз.
- Excel 2003 бағдарламасындағы Өңдеу мәзірінен немесе Excel 2007 немесе 2010s Басты таспасының Алмасу буферіндегі Қою ашылмалы түймесінен Арнайы қоюды таңдаңыз.
- Арнайы қою диалогтық терезесіндегі Қою тізімінен «Мәндерді» таңдаңыз. Диалогты жабу үшін OK түймесін басыңыз.
- Excel 2003 бағдарламасындағы Деректер мәзірінен Сұрыптауды немесе Excel 2007 немесе 2010 бағдарламасындағы Деректер таспасының Сұрыптау және Сүзу тобын таңдаңыз.
- Сұрыптау тілқатысу терезесіндегі «Менің деректер ауқымы бар» тізімінен «Тақырып жолын» таңдаңыз, «Жетіспеді ме?» (немесе салыстыру бағанының тақырыбын нақты берген атыңыз) және OK түймесін басыңыз.
- Басқа тізім үшін осы қадамдарды қайталаңыз.
Қадам 7. Сәйкес келмейтін элементтерді неге сәйкес келмейтінін көру үшін салыстырыңыз
Бұрын айтылғандай, Excel деректердің сәйкестігін іздеуге арналған, егер сіз оны жақындарын іздеу үшін орнатпасаңыз. Сіздің сәйкес келмеуіңіз әріптерді немесе цифрларды кездейсоқ ауыстыру сияқты қарапайым болуы мүмкін. Бұл сондай -ақ тәуелсіз тексеруді қажет ететін нәрсе болуы мүмкін, мысалы, тізімделген активтерді бірінші кезекте есепке алу қажет пе екенін тексеру.
2 -ші әдіс 2: COUNTIF көмегімен шартты форматтау
Қадам 1. Деректер тізімін бір жұмыс парағына көшіріңіз
Қадам 2. Сәйкес келетін немесе сәйкес келмейтін жазбаларды қай тізімнен бөлектегіңіз келетінін шешіңіз
Егер сіз жазбаларды тек бір тізімге бөлектегіңіз келсе, сіз сол тізімге ғана тән жазбаларды бөлектеуді қалайтын шығарсыз; яғни басқа тізімдегі жазбаларға сәйкес келмейтін жазбалар. Егер сіз екі тізімдегі жазбаларды бөлектегіңіз келсе, бір -біріне сәйкес келетін жазбаларды бөлектегіңіз келеді. Бұл мысал үшін біз бірінші тізім G3 - G14 ұяшықтарын, ал екінші тізім L3 - L14 ұяшықтарын алады деп есептейміз.
Қадам 3. Бірегей немесе сәйкес келетін элементтерді бөлектегіңіз келетін тізімдегі элементтерді таңдаңыз
Егер сіз екі тізімдегі сәйкес элементтерді бөлектегіңіз келсе, сіз тізімдерді бір -бірлеп таңдап, әр тізімге салыстыру формуласын (келесі қадамда сипатталған) қолдануыңыз керек.
Қадам 4. Сәйкес салыстыру формуласын қолданыңыз
Мұны істеу үшін сізге Excel нұсқасындағы шартты пішімдеу тілқатысу терезесіне кіру қажет. Excel 2003 -те мұны Пішім мәзірінен Шартты форматтауды таңдау арқылы жасайсыз, ал Excel 2007 және 2010 -де Үй таспасының Стильдер тобындағы Шартты пішімдеу түймешігін басасыз. Ереже түрін «Формула» ретінде таңдаңыз және ережені сипаттауды өңдеу өрісіне формулаңызды енгізіңіз.
- Егер сіз бірінші тізімге ғана тән жазбаларды бөлектегіңіз келсе, формула «= COUNTIF ($ L $ 3: $ L $ 14, G3 = 0)» болады, екінші тізімдегі ұяшықтар диапазоны абсолютті мәндер мен сілтеме ретінде көрсетіледі салыстырмалы мән ретінде бірінші тізімнің бірінші ұяшығына. (Жақын тырнақшаларды енгізбеңіз.)
- Егер сіз екінші тізімге ғана тән жазбаларды бөлектегіңіз келсе, формула «= COUNTIF ($ G $ 3: $ G $ 14, L3 = 0)» болады, бірінші тізімдегі ұяшықтар диапазоны абсолютті мәндер мен сілтеме ретінде көрсетіледі салыстырмалы мән ретінде екінші тізімнің бірінші ұяшығына. (Жақын тырнақшаларды енгізбеңіз.)
- Егер сіз басқа тізімнен табылған әр тізімдегі жазбаларды бөлектегіңіз келсе, сізге бірінші және екіншісіне арналған екі формула қажет болады. Бірінші тізімнің формуласы «= COUNTIF ($ L $ 3: $ L $ 14, G3> 0)», ал екінші тізімдегі формула COUNTIF ($ G $ 3: $ G $ 14, L3> 0) «. Жоғарыда айтылғандай, сіз формуланы қолдану үшін бірінші тізімді таңдайсыз, содан кейін формуланы қолдану үшін екінші тізімді таңдайсыз.
- Белгіленген жазбаларды бөлектегіңіз келетін кез келген пішімді қолданыңыз. Диалогты жабу үшін OK түймесін басыңыз.
Бейне - бұл қызметті пайдалану арқылы кейбір ақпарат YouTube -пен бөлісілуі мүмкін
Кеңестер
- COUNTIF шартты пішімдеу әдісімен ұяшық сілтемесін қолданудың орнына, сіз ізделетін мәнді енгізе аласыз және сол мәннің даналары үшін бір немесе бірнеше тізімді белгілей аласыз.
- Салыстыру формаларын жеңілдету үшін сіз өзіңіздің тізіміңізге «List1» және «List2» сияқты аттар жасай аласыз. Содан кейін, формулаларды жазу кезінде, бұл тізім атаулары жоғарыда келтірілген мысалдарда қолданылған ұяшықтардың абсолюттік диапазондарын алмастыра алады.
