EXCEL ЭЛЕКТРОНДЫҚ КЕСТЕСІН САЛЫСТЫРУ

Excel-та пайдаланушылар жиі кездесетін кестелерді немесе тізімдерді айырмашылықтарды немесе олардағы жетіспейтін элементтерді анықтау үшін міндетпен бетпе-бет келуде. Әрбір пайдаланушы бұл тапсырманы өз бетімен шешеді, бірақ бұл мәселені шешуге көп уақыт жұмсалады, өйткені бұл мәселеге барлық көзқарастар ұтымды емес. Сонымен қатар тізімдерді немесе кесте массивтерін ең аз күш-жігермен салыстыруға мүмкіндік беретін бірнеше дәлелденген әрекет алгоритмдері бар. Мына опцияларды қарастырып көрейік.

Қараңыз: MS Word бағдарламасында екі құжатты салыстыру

Салыстыру әдістері

Excel бағдарламасындағы кесте кеңістіктерін салыстырудың бірнеше жолы бар, бірақ олардың барлығын үш үлкен топқа бөлуге болады:

бір парақтағы тізімдерді салыстыру;

әр түрлі парақтарда орналасқан кестелерді салыстыру;

әртүрлі файлдардағы кесте ауқымын салыстыру.

Бұл классификация негізінде, ең алдымен, салыстыру әдісі таңдалады және тапсырманы орындау үшін нақты әрекеттер мен алгоритмдер анықталады. Мысалы, әртүрлі кітаптарда салыстыру кезінде, сіз екі Excel файлын бір уақытта ашуыңыз керек.

Сонымен қатар, кестелерді салыстыру ұқсас құрылымды болғанда ғана мағынасы бар деп айтуға болады.

1-әдіс: қарапайым формула

Екі кестедегі деректерді салыстырудың қарапайым әдісі қарапайым теңдік формуласын пайдалану болып табылады. Егер деректер сәйкес келсе, онда ол TRUE мәнін береді, ал егер жоқ болса, онда - FALSE. Сандық деректерді де, мәтінді де салыстыруға болады. Бұл əдістің кемшілігі, оны кестеде келтірілген немесе реттелген болса ғана, синхрондалған жəне жолдардың тең санына ие болған жағдайда ғана пайдалануға болады. Бір парақта орналастырылған екі кестенің мысалында бұл әдісті қалай қолдануға болатынын көрейік.

Осылайша, бізде қызметкерлердің тізімдері мен олардың жалақысы бар екі қарапайым үстел бар. Қызметкерлер тізімдерін салыстырып, атаулар орналастырылған бағандар арасындағы сәйкессіздікті анықтаңыз.

Бұл үшін параққа қосымша баған қажет. Белгісін сол жерге енгізіңіз "=". Содан кейін бірінші тізіммен салыстырылатын бірінші элементті басыңыз. Біз қайтадан символды қойдық "=" пернетақтадан. Содан кейін екінші кестеде салыстыратын бағанның бірінші ұяшығын басыңыз. Өрнек мынадай түрге жатады:
= A2 = D2
Әрине, әр жағдайда координаттар әртүрлі болады, бірақ мәні өзгермейді.
Түймешікті басыңыз Кірусалыстыру нәтижелерін алу. Көріп отырғаныңыздай, екі тізбектің алғашқы ұяшықтарын салыстыру кезінде бағдарлама көрсеткішті көрсетті «TRUE»бұл деректерге сәйкес келеді.

Енді біз ұқсас операцияны біз салыстыратын бағандардағы екі кестенің қалған ұяшықтарымен орындау керек. Бірақ уақытты айтарлықтай үнемдейтін формуланы көшіруге болады. Бұл фактор, тізімдерді көптеген сызықтармен салыстырған кезде өте маңызды.
Көшіру процедурасы толтыру тұтқасын пайдалану арқылы оңай. Біз курсорды ұяшықтың төменгі оң жақ бұрышына орналастырдық, онда индикаторы алынды «TRUE». Сонымен қатар, ол қара крестке айналуы керек. Бұл толтыру белгісі. Тінтуірдің сол жақ батырмасын басып, меңзерді салыстыру кестесінің массивтеріндегі жолдар саны бойынша төмен сүйреңіз.

Көріп отырғанымыздай, енді қосымша бағанда деректерді салыстырудың барлық нәтижелері кестелік массивтердің екі бағанында көрсетіледі. Біздің жағдайда деректер тек бір жолға сәйкес келмеді. Салыстырғанда, формула нәтиже берді «FALSE». Барлық басқа жолдар үшін, көріп тұрғаныңыздай, салыстыру формуласы индикатор берді «TRUE».

Сонымен қатар, арнайы формуланы пайдаланып, сәйкессіздіктер санын есептеуге болады. Мұны орындау үшін парақтың элементін көрсетіңіз, ол көрсетілетін болады. Содан кейін белгішені басыңыз «Функцияны кірістіру».

Терезеде Функцияның шеберлері операторлар тобында «Математикалық» атты таңдаңыз SUMPRODUCT. Түймешікті басыңыз «ОК».

Функция дәлелінің терезесі белсендіріледі. SUMPRODUCTоның негізгі міндеті таңдалған ауқымның өнімдерінің сомасын есептеу болып табылады. Бірақ бұл функция біздің мақсатымыз үшін пайдаланылуы мүмкін. Оның синтаксисі өте қарапайым:
= SUMPRODUCT (array1; array2; ...)
Барлығы 255 массивтердің мекенжайларын дәлел ретінде пайдалануға болады. Бірақ біздің жағдайымызда тек екі дәлел ретінде пайдаланылады.
Курсорды өріске қойыңыз «Massive1» парақтағы бірінші аймақтағы салыстырылған деректер ауқымын таңдаңыз. Осыдан кейін біз далаға белгі қойдық. «тең емес» () және екінші аймақтың салыстырмалы ауқымын таңдаңыз. Содан кейін, нәтиже алған өрнек жақшалармен ораңыз, бұрын біз екі таңбаны қойдық "-". Біздің жағдайда, біз келесі сөздерді аламыз:
- (A2: A7D2: D7)
Түймешікті басыңыз «ОК».

Оператор нәтижені есептеп, бейнелейді. Көріп отырғанымыздай, біздің жағдайда нәтиже санға тең "1", яғни салыстырмалы тізімдерде сәйкессіздіктер табылған дегенді білдіреді. Егер тізімдер бірдей болса, нәтиже санға тең болады "0".

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

= B2 = Sheet2! B2

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

2-әдіс: Жасушалар топтарын таңдаңыз

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

Салыстырылған массивтерді таңдаңыз. Қойындыға өтіңіз «Үй». Содан кейін белгішені басыңыз «Табу және бөлектеу»ол құралдар блогында таспада орналасқан Өңдеу. Тізім ашылады, онда сіз позицияны таңдауыңыз керек. «Ұяшықтар тобын таңдау ...».
Сонымен қатар, ұяшықтар тобын таңдаған терезеде басқа жолмен қол жеткізуге болады. Бұл параметр, Excel 2007 нұсқасынан ертерек бағдарламаның нұсқасын орнатқан пайдаланушылар үшін пайдалы болады, себебі түймешік арқылы әдіс «Табу және бөлектеу» Бұл қолданбалар қолдамайды. Біз салыстырғыңыз келетін массивтерді таңдаңыз және пернені басыңыз F5.
Кішкентай көшу терезесі іске қосылады. Түймешікті басыңыз «Белгілеңіз ...» төменгі сол жақ бұрышында.

Осыдан кейін, сіз таңдаған екі нұсқаның қайсысы, ұяшықтар топтарын таңдау үшін терезе іске қосылады. Қосқышты орнына орнатыңыз «Жол бойынша таңдау». Түймешікті басыңыз «ОК».

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

3-әдіс: Шартты пішімдеу

Шартты пішімдеу әдісін пайдалану арқылы салыстыруға болады. Алдыңғы әдіс сияқты, салыстыру облыстары бір Excel жұмыс парағында болуы және бір-бірімен синхрондалуы керек.

Ең алдымен, қандай кесте кеңістігін басты деп санайтынымызды және айырмашылықтарды іздеуді таңдаймыз. Соңында біз екінші үстелде жұмыс істейміз. Сондықтан, онда орналасқан қызметкерлердің тізімін таңдаңыз. Қойындыға жылжу «Үй»батырмасын басыңыз «Шартты пішімдеу»ол блоктағы таспада орналасқан «Стильдер». Ашылмалы тізімнен қосыңыз «Ережені басқару».

Ережені басқарушы терезесі іске қосылады. Біз осы батырманы басамыз «Ережені жасау».

Бастау терезесінде позицияны таңдау «Формуланы пайдалану». Алаңда «Пішім ұяшықтары» «тең емес» белгісімен бөлінген салыстыру бағандарының ауқымдарының бірінші ұяшықтарының мекенжайларын қамтитын формуланы жазыңыз (). Бұл сөздің тек осы жолы белгісі болады. "=". Сонымен қатар, осы формуланың барлық бағандар координаттары үшін абсолюттік адрестеу қолданылуы керек. Ол үшін курсормен формуланы таңдап, кілтде үш рет басыңыз F4. Көріп тұрғаныңыздай, барлық бағандар мекен-жайларының жанында доллар белгісі пайда болды, яғни абсолюттік мәнге сілтемелерді аудару дегенді білдіреді. Біздің нақты жағдайымыз үшін формула келесі форманы алады:
= $ A2 $ D2
Жоғарыда көрсетілген өрнекпен жазамыз. Осыдан кейін түймесін басыңыз «Формат ...».

Қосылған терезе «Пішім ұяшықтары». Қойындыға өтіңіз «Толтыру». Бұл түстер тізімінде біз деректердің сәйкес келмейтін элементтерін түсіргіміз келетін түске түс таңдауды тоқтатамыз. Біз батырманы басамыз «ОК».

Пішімдеу ережесін жасау үшін терезеге оралу үшін түймесін басыңыз. «ОК».

Терезеге автоматты түрде көшкеннен кейін Ереже реттеушісі батырмасын басыңыз «ОК» және онда.

Енді екінші кестеде бірінші кесте аймағының тиісті мәндеріне сәйкес келмейтін деректер бар элементтер таңдалған түспен бөлектеледі.

Тапсырманы орындау үшін шартты пішімдеуді пайдаланудың тағы бір жолы бар. Алдыңғы нұсқалар сияқты, сол парақтағы салыстырылған аймақтардың орналасуын талап етеді, бірақ бұрын сипатталған әдістерге қарағанда, деректерді синхрондау немесе сұрыптау талап етілмейді, бұл опцияны бұрын сипатталғандардан ажыратады.

Салыстыру қажет аймақтарды таңдау.

Қойындыға өтуді орындаңыз «Үй». Түймешікті басыңыз. «Шартты пішімдеу». Белсендірілген тізімде позицияны таңдаңыз «Жасуша таңдау ережелері». Келесі мәзірде біз позицияны таңдаймыз. «Қайталанатын мәндер».

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

Көрсетілген әрекетті орындағаннан кейін, барлық қайталанатын элементтер таңдалған түспен бөлектеледі. Сәйкес келмейтін элементтер бастапқы түстерінде (әдепкі бойынша ақ түсті) қалады. Осылайша, дереу массивтер арасындағы айырмашылықты көруге болады.

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

Осылайша, сәйкес келмейтін көрсеткіштер көрсетіледі.

Сабақ: Excel бағдарламасында шартты пішімдеу

4-әдіс: күрделі формула

Сондай-ақ, деректерді функцияға негізделген күрделі формуланы пайдаланып салыстыруға болады COUNTES. Бұл құралды пайдаланып, екінші кестеде таңдалған бағаннан әрбір элементтің қаншалықты бірінші рет қайталанатынын есептеуіңізге болады.

Оператор COUNTES функциялардың статистикалық тобына жатады. Оның міндеті - мәндері берілген шартты қанағаттандыратын ұяшықтар санын санау. Бұл оператордың синтаксисі келесідей:

= COUNTERS (ауқым, критерий)

Аргумент «Ауқым» - сәйкес мәндер есептелетін массивтің мекен-жайы.

Аргумент «Критерий» матч жағдайын орнатады. Біздің жағдайда бірінші кестелік кеңістіктегі нақты жасушалардың координаттары болады.

Матчтардың саны есептелетін қосымша бағанның бірінші элементін таңдаңыз. Содан кейін белгішені басыңыз «Функцияны кірістіру».

Іске қосу орын алады Функцияның шеберлері. Санатқа өтіңіз «Статистикалық». Тізімде атауды табыңыз «COUNTES». Оны таңдағаннан кейін түймесін басыңыз. «ОК».

Оператор дәлел терезесі іске қосылды. COUNTES. Көріп отырғаныңыздай, осы терезедегі өрістердің атаулары аргументтердің аттарына сәйкес келеді.
Курсорды өріске орнатыңыз «Ауқым». Осыдан кейін, сол жақ батырманы ұстап тұрыңыз, екінші кестенің аттары бар бағанның барлық мәндерін таңдаңыз. Көріп отырғандай, координаттар дереу көрсетілген өріске түседі. Бірақ біздің мақсатымыз үшін бұл мекен-жай абсолютті болуы керек. Мұны істеу үшін, өрістегі координаттарды таңдап, кілтті басыңыз F4.
Көріп отырғаныңыздай, сілтеме доллар белгілерінің болуымен сипатталатын абсолютті пішінді қабылдады.
Содан кейін далаға барыңыз «Критерий»сонда курсорды орнатыңыз. Бірінші элементтің атын бірінші кесте ауқымында басамыз. Бұл жағдайда салыстырмалы сілтемені қалдырыңыз. Өрісте көрсетілгеннен кейін, түймені басуға болады «ОК».

Нәтиже парақ элементінде көрсетіледі. Бұл санға тең "1". Бұл дегеніміз екінші кестенің аттар тізімінде тегі «Гринев В.П.»бірінші кесте жиымының тізіміндегі бірінші болып, бір рет пайда болады.

Енді біз бірінші кестенің барлық элементтері үшін ұқсас өрнек жасауымыз керек. Мұны істеу үшін, оны бұрынғыдай орындағанымыздай, толтыру белгісі арқылы көшіріңіз. Курсорды функцияны қамтитын парақ элементінің төменгі оң жақ бөлігіне қойыңыз COUNTES, және оны толтырғышқа айналдырғаннан кейін, сол жақ тінтуірдің түймешігін басып тұрыңыз да, жүгіргіні төмен қарай сүйреңіз.

Көріп отырғаныңыздай, бағдарлама бірінші кестенің әрбір ұяшығын екінші кесте ауқымындағы деректермен салыстыру арқылы сәйкестіктерді есептеуді жасады. Төрт жағдайда, нәтиже шыққан "1", ал екі жағдайда - "0". Яғни, екінші кестеде бағдарлама бірінші кесте жиымындағы екі мәнді таба алмады.

Әрине, кестенің көрсеткіштерін салыстыру үшін бұл өрнек қолданыстағы формада қолданылуы мүмкін, бірақ оны жақсарту мүмкіндігі бар.

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

Ең алдымен, біздің формуламды қайта жасайық COUNTES, атап айтқанда оны оператордың аргументтерінің бірі етеді IF. Ол үшін оператор орналасқан бірінші ұяшықты таңдаңыз COUNTES. Формула жолында бұрын өрнек қосамыз «IF» тырнақшасыз және кронштейнді ашыңыз. Әрі қарай, біз жұмыс істеуді жеңілдету үшін, формула жолағындағы мәнді таңдаймыз. «IF» белгішені нұқыңыз «Функцияны кірістіру».

Функция дәлел терезесі ашылады. IF. Көріп отырғаныңыздай, терезенің бірінші өрісі оператордың мәнімен толтырылған. COUNTES. Бірақ біз бұл салада тағы бір нәрсе қосуымыз керек. Біз курсорды сонда орналастырдық және бұрыннан бар өрнекке қосамыз "=0" баға белгілерінсіз.
Содан кейін далаға барыңыз «Мән егер шын болса». Мұнда басқа кірістірілген функцияны қолданамыз - LINE. Сөзді енгізіңіз «LINE» тырнақшасыз, содан кейін жақшаларды ашып, екінші кестенің тегі мен бірінші ұяшықтың координаттарын көрсетіп, жақшаларды жабыңыз. Атап айтқанда, біздің жағдайда бұл салада «Мән егер шын болса» келесі өрнек алды:
LINE (D2)
Енді оператор LINE функциялар туралы есеп береді IF нақты тегі жазылған жолдың нөмірі және бірінші өрісте көрсетілген шарт орындалған жағдайда, функция IF бұл нөмірді ұяшыққа шығарады. Біз батырманы басамыз «ОК».

Көріп отырғаныңыздай, бірінші нәтиже ретінде көрсетіледі «FALSE». Бұл мән оператордың жағдайын қанағаттандырмайды дегенді білдіреді. IF. Яғни бірінші тегі екі тізімде де бар.

Толтырғышты қолдану арқылы әдеттегідей оператордың өрнегін көшіреміз IF бүкіл бағанда. Көріп отырғаныңыздай, екінші кестедегі екі позицияда, бірақ алғашқыда емес, формула жолдың нөмірін береді.

Үстел кеңістігінен оңға қарай шегініп, бағанды сандардан бастап, кезекпен толтырыңыз 1. Сандардың саны екінші салыстыру кестесіндегі жолдар санына сәйкес келуі керек. Нөмірлеу процедурасын жылдамдату үшін, сіз толтыру таңбалауышын пайдалана аласыз.

Содан кейін, бағанның оң жағындағы бірінші ұяшықты сандармен таңдап, белгішені басыңыз «Функцияны кірістіру».

Ашылады Функция шебері. Санатқа өтіңіз «Статистикалық» және атауларды таңдау «АТЫ». Түймешікті басыңыз «ОК».

Функция ЖОҒАРЫ, Аргументтер терезесі ашылғанда, есептік жазбамен көрсетілген ең төменгі мәнді көрсету үшін жасалған.
Алаңда «Массив» қосымша бағанның координаттарын көрсетіңіз «Матчтардың саны»бұрын біз функцияны қолданып түрлендірдік IF. Біз барлық сілтемелерді абсолютті түрде жасаймыз.
Алаңда «K» ең төменгі мәнді көрсету керек дегенді көрсетіңіз. Мұнда жақында қосқан бағанмен бағанның бірінші ұяшығының координаттарын көрсетеміз. Мекенжай салыстырмалы түрде қалды. Түймешікті басыңыз «ОК».

Нәтиже - нөмірі көрсетіледі 3. Бұл кесте массивтерінің сәйкес емес жолдардың ең аз нөмірленуі. Толтыру белгісін қолдану арқылы формуланы төменгі жағына көшіріңіз.

Енді сәйкес келмейтін элементтердің жол нөмірін біліп, функцияны қолданып, ұяшыққа және олардың мәндерін енгізе аламыз INDEX. Формуланы қамтитын парақтың бірінші элементін таңдаңыз ЖОҒАРЫ. Содан кейін формула жолына және атына дейін барыңыз «АТЫ» қосу аты INDEX тырнақшасыз, жақшаны ашып, нүктелі үтірді (;). Содан кейін формула жолында атауды таңдаңыз. INDEX белгішені нұқыңыз «Функцияны кірістіру».

Осыдан кейін сілтемеде функция болуы керек екенін анықтау қажет шағын терезе ашылады INDEX немесе массивтермен жұмыс істеуге арналған. Екінші нұсқа қажет. Әдепкі бойынша орнатылған, сондықтан осы терезеде батырманы басыңыз. «ОК».

Функция дәлелінің терезесі басталады. INDEX. Бұл мәлімдеме берілген жолдағы белгілі бір массивте орналасқан мәнді көрсету үшін жасалған.
Көріп тұрғаныңыздай, өріс «Жол нөмірі» функционалдық мәндермен толтырылған ЖОҒАРЫ. Алдында бар мәннен Excel парағының нөмірленуі мен кесте аумағының ішкі нөмірленуі арасындағы айырманы азайтыңыз. Көріп отырғанымыздай, кесте мәндерінің үстінде бізде қақпақ ғана бар. Бұл дегеніміз, айырмашылық бір сызық. Сондықтан біз далаға қосамыз «Жол нөмірі» мағынасы "-1" баға белгілерінсіз.
Алаңда «Массив» екінші кестенің мәндер ауқымының мекен-жайын көрсетіңіз. Сонымен қатар, біз барлық координаттарын абсолютті етіп жасаймыз, яғни біз бұрын сипаттағандай, олардың алдында долларға белгі қойдық.
Біз батырманы басамыз «ОК».

Нәтижені экранға шығарғаннан кейін, бағанды аяқтағыш арқылы бағанның соңына қарай функцияны созамыз. Көріп отырғаныңыздай, екінші кестеде болған, бірақ бірінші болып табылмайтын тегі де бөлек диапазонда көрсетіледі.

5-әдіс: Массивтерді әртүрлі кітаптармен салыстыру

Әртүрлі кітаптардағы диапазонды салыстыру кезінде, жоғарыда аталған әдістерді пайдалана аласыз, сол сияқты екі кесте кеңістігін бір парақта орналастыруды қажет ететін параметрлерді қоспағанда. Осы жағдайда салыстыру рәсімін жүргізудің негізгі шарты екі файлдың бір мезгілде терезесін ашады. Excel 2013 нұсқаларын және одан кейінгі нұсқаларын, сондай-ақ, Excel 2007 нұсқасына дейінгі нұсқалар үшін ешқандай проблемалар жоқ. Бірақ Excel 2007 және Excel 2010 бағдарламаларында бір уақытта екі терезені ашу үшін қосымша манипуляциялар қажет. Бұны қалай жасауға болады, бөлек сабақта сипатталған.

Сабақ: Excel-ды әртүрлі терезелерде қалай ашу керек

Көріп отырғаныңыздай, кестелерді бір-бірімен салыстыруға мүмкіндік беретін бірқатар мүмкіндіктер бар. Қандай параметрді кестелік деректер бір-біріне (бір парақта, әртүрлі кітаптарда, әртүрлі парақтарда), сондай-ақ, пайдаланушының осы салыстыруды экранда көрсетілуін қалайтын жеріне байланысты.