Якҷоя кардани ду рӯйхат бе такрорӣ

Вазъияти классикӣ: шумо ду рӯйхат доред, ки бояд ба як якҷоя карда шаванд. Ғайр аз он, дар рӯйхатҳои ибтидоӣ метавонанд ҳам унсурҳои беназир ва ҳам унсурҳои мувофиқ бошанд (ҳам дар байни рӯйхатҳо ва ҳам дар дохили), аммо дар натиҷа шумо бояд рӯйхатро бидуни такрор (такрор) гиред:

Биёед ба таври анъанавӣ якчанд роҳҳои ҳалли чунин як масъалаи умумӣ - аз ибтидоӣ "дар пешонӣ" то мураккабтар, вале шево назар андозем.

Усули 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+Shift+даромадан ва сипас ба ячейкаҳои кӯдак бо маржа нусхабардорӣ кунед (кашед).

Дар версияи англисии Excel ин формула чунин менамояд:

=ХАТО(IFERROR(INDEX(List1, MATCH(0, COUNTIF($E$1:E1, List1), 0)), INDEX(List2, MATCH(0, COUNTIF($E$1:E1, List2), 0)) ), "") 

Камбудии ин равиш дар он аст, ки формулаҳои массив корро бо файл ба таври назаррас суст мекунанд, агар ҷадвалҳои манбаъ шумораи зиёди (якчандсад ё бештар) элементҳо дошта бошанд. 

Усули 3. Дархости барқ

Агар рӯйхати сарчашмаҳои шумо миқдори зиёди унсурҳо дошта бошанд, масалан, якчанд садҳо ё ҳазорҳо, пас ба ҷои формулаи массиви суст, беҳтар аст, ки равиши куллан дигар, яъне абзорҳои иловагии 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
• Истихроҷи ашёи нодир аз рӯйхат
• Чӣ тавр ду рӯйхатро бо ҳамдигар барои мувофиқат ва фарқиятҳо муқоиса кардан мумкин аст