Кайталанбаган эки тизмени бириктирүү

Классикалык кырдаал: сизде эки тизме бар, аларды бири-бирине бириктирүү керек. Андан тышкары, баштапкы тизмелерде уникалдуу элементтер да, дал келгендер да болушу мүмкүн (тизмелер арасында да, ичинде да), бирок чыгарууда сиз кайталанбаган тизмени (кайталануулар) алышыңыз керек:

Келгиле, салттуу түрдө мындай жалпы көйгөйдү чечүүнүн бир нече жолдорун карап көрөлү - примитивдүү "маңдайга" чейин татаал, бирок көрктүү.

Метод 1: Дубликаттарды алып салуу

Сиз көйгөйдү эң жөнөкөй жол менен чече аласыз - эки тизменин элементтерин кол менен бирине көчүрүп, андан кийин куралды пайда болгон топтомго колдонуңуз. Дубликаттарды алып салуу өтмөктөн маалыматтар (Маалымат — Дубликаттарды алып салуу):

Албетте, бул ыкма булак тизмелериндеги маалыматтар тез-тез өзгөрүп турса иштебейт – ар бир өзгөртүүдөн кийин процедураны толугу менен кайталашыңыз керек болот. 

Метод 1a. айланма үстөл

Бул ыкма, чынында, мурунку логикалык уландысы болуп саналат. Эгерде тизмелер анча чоң болбосо жана алардагы элементтердин максималдуу саны алдын ала белгилүү болсо (мисалы, 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, МАТЧ(0, COUNTIF($E$1:E1, Тизме2), 0)) ), "") 

Бул ыкманын терс жагы, эгерде баштапкы таблицаларда элементтердин саны көп (бир нече жүз же андан көп) болсо, массив формулалары файл менен иштөөнү байкалаарлык жайлатат. 

Метод 3. Power Query

Эгерде сиздин булак тизмелериңизде элементтердин саны көп болсо, мисалы, бир нече жүздөгөн же миңдеген элементтер болсо, анда жай массив формуласынын ордуна, принципиалдуу түрдө башкача ыкманы, атап айтканда, Power Query кошумча куралдарын колдонуу жакшы. Бул кошумча демейки боюнча Excel 2016га орнотулган. Эгер сизде Excel 2010 же 2013 болсо, аны өзүнчө жүктөп алып, орното аласыз (акысыз).

Иш -аракеттердин алгоритми төмөнкүчө:

1. Орнотулган кошумча кошумча өтмөктү ачыңыз күч суроо (эгер сизде Excel 2010-2013 болсо) же жөн гана өтмөккө өтүңүз маалыматтар (эгерде сизде Excel 2016 болсо).
2. Биринчи тизмени тандап, баскычты басыңыз Таблицадан/Арападан (Диапазондон/таблицадан). Биздин тизмеден "акылдуу үстөл" түзүү жөнүндө сураганда, биз макулбуз:

   

3. Суроо редакторунун терезеси ачылат, анда сиз жүктөлгөн маалыматтарды жана суроонун атын көрө аласыз стол 1 (кааласаңыз, аны өзүңүзгө өзгөртсөңүз болот).
4. Таблицанын башын эки жолу чыкылдатыңыз (word тизме 1) жана аны башкасына өзгөртүү (мисалы эл). Эмнени так атоо маанилүү эмес, бирок ойлоп табылган ысымды эстен чыгарбоо керек, анткени. экинчи таблицаны импорттоодо кийинчерээк кайра колдонууга туура келет. Келечекте эки таблицаны бириктирүү алардын тилкелеринин аталыштары дал келген учурда гана иштейт.
5. Жогорку сол бурчтагы ачылуучу тизмени кеңейтиңиз жабуу жана жүктөп алуу тандап Жабуу жана жүктөө… (Жабуу&Жүктөө…):

   

6. Кийинки диалог терезесинде (ал бир аз башкача көрүнүшү мүмкүн – кооптонбоңуз), тандаңыз Жөн гана байланыш түзүңүз (Байланыш түзүү гана):

   

7. Экинчи тизме үчүн процедураны (2-6-пункттар) кайталайбыз. Мамычанын аталышын өзгөртүүдө мурунку суроодогудай эле аталышты (Адамдар) колдонуу маанилүү.
8. Өтмөктөгү Excel терезесинде маалыматтар же өтмөктө күч суроо тандап алуу Маалыматтарды алуу - Сурамдарды бириктирүү - кошуу (Маалыматтарды алуу — Суроолорду бириктирүү — Тиркетүү):

   

9. Пайда болгон диалог терезесинде ачылуучу тизмелерден биздин суроо-талаптарды тандаңыз:

   

10. Натыйжада, биз жаңы суроону алабыз, анда эки тизме бири-биринин астына кошулат. Бул баскыч менен дубликаттарды алып салуу бойдон калууда Саптарды жок кылуу - Дубликаттарды жок кылуу (Сатарларды жок кылуу — Дубликаттарды жок кылуу):

    

11. Аякталган сурамдын атын параметрлер панелинин оң жагына өзгөртүүгө болот, ага акылга сыярлык ат берип (чындыгында бул жыйынтык таблицанын аталышы болот) жана бардыгын буйрук менен баракка жүктөсө болот. жабуу жана жүктөп алуу (Жабуу жана жүктөө):

    

Келечекте, баштапкы тизмелерге кандайдыр бир өзгөртүүлөр же толуктоолор менен, натыйжалар таблицасын жаңыртуу үчүн жөн гана оң баскыч менен чыкылдатуу жетиштүү болот.

• Power Query аркылуу ар кандай файлдардан бир нече таблицаларды кантип чогултса болот
• Тизмеден уникалдуу элементтерди чыгаруу
• Дал келүү жана айырмачылыктар үчүн эки тизмени бири-бири менен кантип салыштырса болот