Werrej
Għandna żewġ tabelli (pereżempju, il-verżjonijiet il-qodma u l-ġodda tal-lista tal-prezzijiet), li għandna bżonn inqabblu u nsibu malajr id-differenzi:
Mill-ewwel jidher ċar li xi ħaġa ġiet miżjuda mal-lista l-ġdida tal-prezzijiet (dati, tewm …), xi ħaġa sparixxa (tut, lampun …), il-prezzijiet inbidlu għal xi oġġetti (tin, bettieħ …). Trid issib u turi malajr dawn il-bidliet kollha.
Għal kwalunkwe kompitu f'Excel, kważi dejjem hemm aktar minn soluzzjoni waħda (ġeneralment 4-5). Għall-problema tagħna, jistgħu jintużaw ħafna approċċi differenti:
- funzjoni VPR (LOOKUP) — Fittex l-ismijiet tal-prodotti mil-lista l-ġdida tal-prezzijiet fil-qadima u uri l-prezz il-qadim ħdejn il-ġdid, u mbagħad aqbad id-differenzi
- għaqqad żewġ listi f'waħda u mbagħad ibni tabella tal-pern ibbażata fuqha, fejn id-differenzi jkunu viżibbli b'mod ċar
- uża l-Power Query Add-in għal Excel
Ejja nieħduhom kollha fl-ordni.
Metodu 1. Tqabbil ta' tabelli mal-funzjoni VLOOKUP
Jekk m'intix familjari għal kollox ma 'din il-karatteristika mill-isbaħ, allura l-ewwel ħares hawn u aqra jew ara tutorja bil-vidjo dwarha - issalva lilek innifsek ftit snin ta' ħajja.
Tipikament, din il-funzjoni tintuża biex tiġbed data minn tabella għal oħra billi tqabbel xi parametru komuni. F'dan il-każ, aħna ser nużawha biex nimbuttaw il-prezzijiet qodma fil-prezz il-ġdid:
Dawk il-prodotti, li kontrihom irriżulta l-iżball #N/A, mhumiex fil-lista l-antika, jiġifieri ġew miżjuda. Il-bidliet fil-prezzijiet huma wkoll viżibbli b'mod ċar.
Prosperità dan il-metodu: sempliċi u ċar, "klassiku tal-ġeneru", kif jgħidu. Jaħdem fi kwalunkwe verżjoni ta 'Excel.
cons hemm ukoll. Biex tfittex prodotti miżjuda mal-lista l-ġdida tal-prezzijiet, ser ikollok tagħmel l-istess proċedura fid-direzzjoni opposta, jiġifieri iġbed prezzijiet ġodda għall-prezz l-antik bl-għajnuna ta 'VLOOKUP. Jekk id-daqsijiet tat-tabelli jinbidlu għada, allura l-formuli se jkollhom jiġu aġġustati. Ukoll, u fuq tabelli verament kbar (> 100 elf ringiela), dan il-ferħ kollu se jonqos b'mod deċenti.
Metodu 2: Tqabbil ta' tabelli bl-użu ta' pern
Ejja nikkopja t-tabelli tagħna waħda taħt l-oħra, billi żżid kolonna bl-isem tal-lista tal-prezzijiet, sabiex aktar tard tkun tista 'tifhem minn liema lista liema ringiela:
Issa, ibbażata fuq it-tabella maħluqa, aħna se noħolqu sommarju permezz Daħħal – PivotTable (Daħħal — Pivot Table). Ejja tarmi għalqa prodott għaż-żona tal-linji, qasam prezz għall-erja tal-kolonna u l-qasam Цena fil-firxa:
Kif tistgħu taraw, it-tabella tal-pern awtomatikament tiġġenera lista ġenerali tal-prodotti kollha mil-listi tal-prezzijiet qodma u ġodda (l-ebda repetizzjonijiet!) U ssortja l-prodotti alfabetikament. Tista' tara b'mod ċar il-prodotti miżjuda (m'għandhomx il-prezz l-antik), il-prodotti mneħħija (m'għandhomx il-prezz il-ġdid) u l-bidliet fil-prezz, jekk ikun hemm.
Totali kbar f'tali tabella ma jagħmlux sens, u jistgħu jiġu diżattivati fuq it-tab Kostruttur - Totali kbar - Iddiżattiva għal ringieli u kolonni (Disinn — Totali Grandi).
Jekk il-prezzijiet jinbidlu (iżda mhux il-kwantità tal-merkanzija!), allura huwa biżżejjed li sempliċement taġġorna s-sommarju maħluq billi tikklikkja bil-lemin fuqha - riffriskar.
Prosperità: Dan l-approċċ huwa ordni ta 'kobor aktar mgħaġġel b'tabelli kbar minn VLOOKUP.
cons: għandek bżonn tikkopja manwalment id-dejta taħt xulxin u żżid kolonna bl-isem tal-lista tal-prezzijiet. Jekk id-daqsijiet tat-tabelli jinbidlu, imbagħad trid tagħmel kollox mill-ġdid.
Metodu 3: Tqabbil ta' tabelli ma' Power Query
Power Query huwa add-in b'xejn għal Microsoft Excel li jippermettilek tagħbija data f'Excel minn kważi kull sors u mbagħad tittrasforma din id-data bi kwalunkwe mod mixtieq. F'Excel 2016, dan is-add-in diġà huwa mibni b'mod awtomatiku fuq it-tab Data (Data), u għal Excel 2010-2013 trid tniżżlu separatament mill-websajt tal-Microsoft u tinstallah – ikseb tab ġdida Mistoqsija dwar l-Enerġija.
Qabel ma tagħbija l-listi tal-prezzijiet tagħna f'Power Query, l-ewwel iridu jiġu kkonvertiti f'tabelli intelliġenti. Biex tagħmel dan, agħżel il-firxa bid-dejta u agħfas il-kombinazzjoni fuq it-tastiera Ctrl+T jew agħżel it-tab fuq iż-żigarella Home – Format bħala tabella (Id-dar — Format bħala Tabella). L-ismijiet tat-tabelli maħluqa jistgħu jiġu kkoreġuti fuq it-tab kostruttur (Se nħalli l-istandard Tabella 1 и Tabella 2, li jinkisbu awtomatikament).
Tagħbija l-prezz l-antik f'Power Query billi tuża l-buttuna Minn Tabella/Firxa (Mill-Tabella/Firxa) mit-tab Data (Data) jew mit-tab Mistoqsija dwar l-Enerġija (jiddependi fuq il-verżjoni ta 'Excel). Wara t-tagħbija, se nirritornaw lura lejn Excel minn Power Query bil-kmand Agħlaq u tagħbija – Agħlaq u tagħbija fi... (Agħlaq & Tagħbija — Agħlaq u Tagħbija Sa...):
... u fit-tieqa li tidher imbagħad agħżel Oħloq biss konnessjoni (Konnessjoni Biss).
Irrepeti l-istess mal-lista l-ġdida tal-prezzijiet.
Issa ejja noħolqu t-tielet mistoqsija li se tgħaqqad u tqabbel id-dejta mit-tnejn preċedenti. Biex tagħmel dan, agħżel f'Excel fuq it-tab Dejta – Ikseb id-Dejta – Għaqqad it-Talbiet – Għaqqad (Dejta — Ikseb id-Data — Mistoqsijiet Amalgamazzjoni — Amalgamazzjoni) jew agħfas il-buttuna Għaqqad (Għaqqad) tab Mistoqsija dwar l-Enerġija.
Fit-tieqa tal-għaqda, agħżel it-tabelli tagħna fil-listi drop-down, agħżel il-kolonni bl-ismijiet tal-oġġetti fihom, u fin-naħa t'isfel, issettja l-metodu tal-għaqda - Tlesti esterna (Sħiħ ta' Barra):
Wara li tikklikkja fuq OK għandha tidher tabella ta 'tliet kolonni, fejn fit-tielet kolonna għandek bżonn tespandi l-kontenut tat-tabelli nested billi tuża l-vleġġa doppja fl-intestatura:
Bħala riżultat, aħna niksbu l-għaqda tad-dejta miż-żewġ tabelli:
Huwa aħjar, ovvjament, li tibdel l-isem tal-kolonni fl-intestatura billi tikklikkja darbtejn fuq oħrajn li jinftiehmu:
U issa l-aktar interessanti. Mur fit-tab Żid kolonna (Żid Kolonna) u kklikkja fuq il-buttuna Kolonna kundizzjonali (Kolonna Kundizzjonali). U mbagħad fit-tieqa li tiftaħ, daħħal diversi kundizzjonijiet tat-test bil-valuri tal-output korrispondenti tagħhom:
Fadal biex tikklikkja fuq OK u ittella r-rapport li jirriżulta f'Excel billi tuża l-istess buttuna agħlaq u niżżel (Agħlaq u Tagħbija) tab home (Dar):
Sbuħija.
Barra minn hekk, jekk iseħħu xi bidliet fil-listi tal-prezzijiet fil-futur (il-linji huma miżjuda jew imħassra, il-prezzijiet jinbidlu, eċċ.), allura jkun biżżejjed li naġġornaw it-talbiet tagħna b'shortcut tat-tastiera. Ctrl+Alt+F5 jew bil-buttuna Aġġorna kollox (Aġġorna Kollha) tab Data (Data).
Prosperità: Forsi l-aktar mod sabiħ u konvenjenti ta 'kulħadd. Jaħdem b'mod intelliġenti ma 'tabelli kbar. Ma teħtieġx editjar manwali meta tbiddel id-daqs tat-tabelli.
cons: Jeħtieġ li l-add-in Power Query (f'Excel 2010-2013) jew Excel 2016 jiġi installat. L-ismijiet tal-kolonni fid-dejta tas-sors m'għandhomx jinbidlu, inkella nġibu l-iżball "Kolonna tali u tali ma nstabitx!" meta tipprova taġġorna l-mistoqsija.
- Kif tiġbor id-dejta mill-fajls Excel kollha f'folder partikolari billi tuża Power Query
- Kif issib logħbiet bejn żewġ listi f'Excel
- Għaqda ta' żewġ listi mingħajr duplikati