Классикалық жағдай: сізде бір тізімге біріктіру қажет екі тізім бар. Сонымен қатар, бастапқы тізімдерде бірегей элементтер де, сәйкес келетіндер де болуы мүмкін (тізімдер арасында да, ішінде де), бірақ нәтижеде қайталанбайтын (қайталанатын) тізімді алу керек:
Дәстүрлі түрде осындай ортақ мәселені шешудің бірнеше жолдарын қарастырайық - қарабайыр «маңдайдан» күрделірек, бірақ талғампаздыққа дейін.
1-әдіс: Көшірмелерді жою
Мәселені ең қарапайым жолмен шешуге болады – екі тізімнің элементтерін қолмен бір тізімге көшіріңіз, содан кейін құралды алынған жиынтыққа қолданыңыз. Көшірмелерді жою қойындысынан мәліметтер (Деректер — көшірмелерді жою):
Әрине, егер бастапқы тізімдердегі деректер жиі өзгерсе, бұл әдіс жұмыс істемейді - әр өзгертуден кейін бүкіл процедураны қайталау керек болады.
1а әдісі. жиынтық кесте
Бұл әдіс, шын мәнінде, алдыңғысының логикалық жалғасы болып табылады. Егер тізімдер өте үлкен болмаса және олардағы элементтердің максималды саны алдын ала белгілі болса (мысалы, 10-нан көп емес), онда сіз тікелей сілтемелер арқылы екі кестені біріне біріктіре аласыз, оң жағындағылары бар бағанды және алынған кесте негізінде жиынтық кестені құру:
Өздеріңіз білетіндей, жиынтық кесте қайталауларды елемейді, сондықтан нәтижеде біз қайталанбайтын біріктірілген тізімді аламыз. 1 бар көмекші баған қажет, себебі Excel бағдарламасы кемінде екі бағанды қамтитын жиынтық кестелерді құра алады.
Түпнұсқа тізімдер өзгертілгенде, жаңа деректер біріктірілген кестеге тікелей сілтемелер арқылы өтеді, бірақ жиынтық кестені қолмен жаңарту керек (тінтуірдің оң жақ түймешігімен басу – Жаңарту және сақтау). Егер сізге жылдам қайта есептеу қажет болмаса, басқа опцияларды қолданған дұрыс.
2-әдіс: Массив формуласы
Есепті формулалар арқылы шешуге болады. Бұл жағдайда нәтижелерді қайта есептеу және жаңарту бастапқы тізімдердегі өзгерістерден кейін бірден және автоматты түрде жүзеге асады. Ыңғайлы және қысқа болу үшін тізімдерімізге атаулар берейік. 1 тізімі и 2 тізіміпайдалана отырып, Аты менеджері қойындысы формула (Формулалар — Атау менеджері — Жасау):
Атаудан кейін бізге қажет формула келесідей болады:
Бір қарағанда, бұл қорқынышты көрінеді, бірақ, шын мәнінде, бәрі соншалықты қорқынышты емес. Бұл формуланы Alt+Enter пернелер тіркесімін пайдаланып бірнеше жолға кеңейтуге рұқсат етіңіз және біз жасағандай бос орындармен шегініңіз, мысалы, мына жерде:
Мұндағы логика келесідей:
- INDEX(List1;MATCH(0;COUNTIF($E$1:E1;List1); 0) формуласы бірінші тізімдегі барлық бірегей элементтерді таңдайды. Олар біткен бойда #N/A қатесін бере бастайды:
- INDEX(List2;MATCH(0;COUNTIF($E$1:E1;List2); 0)) формуласы екінші тізімнен бірегей элементтерді дәл осылай шығарады.
- Бір-біріне кірістірілген екі IFERROR функциясы бірінші тізім-1, содан кейін-2 тізімнен бірінен соң бірі бірегейлерінің шығысын жүзеге асырады.
Бұл массив формуласы екенін ескеріңіз, яғни тергеннен кейін оны қарапайым емес ұяшыққа енгізу керек. кіру, бірақ пернелер тіркесімі арқылы Ctrl+ауысым+кіру содан кейін шеті бар еншілес ұяшықтарға көшіріңіз (сүйреңіз).
Excel бағдарламасының ағылшын нұсқасында бұл формула келесідей көрінеді:
=ҚАУІПСІЗ(IFERROR(INDEX(1-тізім, СӘЙКЕСТІК(0, COUNTIF($E$1:E1, Тізім1), 0)), INDEX(Тізім2, SATCH(0, COUNTIF($E$1:E1, Тізім2), 0)) ), «»)
Бұл тәсілдің кемшілігі, егер бастапқы кестелерде элементтердің көп (бірнеше жүз немесе одан да көп) саны болса, массив формулалары файлмен жұмысты айтарлықтай баяулатады.
3-әдіс. Power Query
Егер бастапқы тізімдеріңізде элементтер саны көп болса, мысалы, бірнеше жүздеген немесе мыңдаған болса, онда баяу массив формуласының орнына түбегейлі басқа тәсілді, атап айтқанда Power Query қондырма құралдарын қолданған дұрыс. Бұл қондырма әдепкі бойынша Excel 2016 бағдарламасына енгізілген. Егер сізде Excel 2010 немесе 2013 болса, оны бөлек жүктеп алуға және орнатуға болады (тегін).
Әрекеттер алгоритмі келесідей:
- Орнатылған қондырманың бөлек қойындысын ашыңыз Қуат сұрауы (егер сізде Excel 2010-2013 болса) немесе жай ғана қойындыға өтіңіз мәліметтер (егер сізде Excel 2016 болса).
- Бірінші тізімді таңдап, түймесін басыңыз Кестеден/Ауқымнан (Ауқымнан/Кестеден). Біздің тізімнен «ақылды кесте» жасау туралы сұрағанда, біз келісеміз:
- Сұрау өңдегішінің терезесі ашылады, онда жүктелген деректер мен сұрау атауын көруге болады Кесте 1 (қаласаңыз, оны өзіңізге өзгертуге болады).
- Кесте тақырыбын екі рет басыңыз (word 1 тізімі) және оның атын кез келген басқаға өзгерту (мысалы адамдар). Нақты нені атау маңызды емес, бірақ ойлап тапқан атауды есте сақтау керек, өйткені. екінші кестені импорттау кезінде оны кейінірек қайта пайдалануға тура келеді. Болашақта екі кестені біріктіру олардың баған тақырыптары сәйкес келсе ғана жұмыс істейді.
- Жоғарғы сол жақ бұрыштағы ашылмалы тізімді кеңейтіңіз жабыңыз және жүктеңіз және таңдаңыз Жабыңыз және жүктеңіз… (Жабу және жүктеу…):
- Келесі тілқатысу терезесінде (ол сәл басқаша көрінуі мүмкін – үрейленбеңіз) таңдаңыз Тек қосылым жасаңыз (Тек қосылым жасау):
- Екінші тізім үшін бүкіл процедураны (2-6 тармақтар) қайталаймыз. Баған тақырыбының атын өзгерту кезінде алдыңғы сұраудағыдай атауды (Адамдар) пайдалану маңызды.
- Қойындыдағы Excel терезесінде мәліметтер немесе қойындыда Қуат сұрауы таңдау Деректерді алу - Сұрауларды біріктіру - Қосу (Деректерді алу - сұрауларды біріктіру - қосу):
- Пайда болған диалогтық терезеде ашылмалы тізімдерден сұрауларымызды таңдаңыз:
- Нәтижесінде біз жаңа сұрау аламыз, онда екі тізім бір-бірінің астына қосылады. Түйменің көмегімен көшірмелерді жою қалады Жолдарды жою – Көшірмелерді жою (Жолдарды жою — көшірмелерді жою):
- Аяқталған сұраудың атын опциялар тақтасының оң жағында өзгертуге болады, оған ақылға қонымды атау береді (бұл нәтижелер кестесінің атауы болады) және барлығын пәрмен арқылы параққа жүктеп салуға болады. жабыңыз және жүктеңіз (жабу&жүктеу):
Болашақта бастапқы тізімдерге кез келген өзгерістер немесе толықтырулар енгізілгенде, нәтижелер кестесін жаңарту үшін тінтуірдің оң жақ түймешігімен басу жеткілікті болады.
- Power Query көмегімен әртүрлі файлдардан бірнеше кестелерді қалай жинауға болады
- Тізімнен бірегей элементтерді шығару
- Сәйкестіктер мен айырмашылықтар үшін екі тізімді бір-бірімен қалай салыстыруға болады