Përmbajtje
Ne kemi dy tabela (për shembull, versionet e vjetra dhe të reja të listës së çmimeve), të cilat duhet t'i krahasojmë dhe të gjejmë shpejt ndryshimet:
Menjëherë është e qartë se diçka është shtuar në listën e re të çmimeve (hurma, hudhra…), diçka është zhdukur (manaferra, mjedra…), çmimet kanë ndryshuar për disa mallra (fiq, pjepër…). Ju duhet të gjeni dhe shfaqni shpejt të gjitha këto ndryshime.
Për çdo detyrë në Excel, pothuajse gjithmonë ka më shumë se një zgjidhje (zakonisht 4-5). Për problemin tonë, mund të përdoren shumë qasje të ndryshme:
- funksion VPR (VLOOKUP) — kërkoni emrat e produkteve nga lista e re e çmimeve në atë të vjetër dhe shfaqni çmimin e vjetër pranë atij të ri dhe më pas kapni dallimet
- bashkoni dy lista në një dhe më pas ndërtoni një tabelë kryesore bazuar në të, ku dallimet do të jenë qartë të dukshme
- përdorni shtesën Power Query për Excel
Le t'i marrim të gjitha me radhë.
Metoda 1. Krahasimi i tabelave me funksionin VLOOKUP
Nëse nuk jeni plotësisht të panjohur me këtë veçori të mrekullueshme, atëherë së pari shikoni këtu dhe lexoni ose shikoni një video tutorial për të - kurseni vetes disa vite jetë.
Në mënyrë tipike, ky funksion përdoret për të tërhequr të dhëna nga një tabelë në tjetrën duke përputhur disa parametra të zakonshëm. Në këtë rast, ne do ta përdorim atë për të shtyrë çmimet e vjetra në çmimin e ri:
Ato produkte, ndaj të cilave ka rezultuar gabimi #N/A, nuk janë në listën e vjetër, pra janë shtuar. Ndryshimet e çmimeve janë gjithashtu të dukshme.
rekuizitë kjo metodë: e thjeshtë dhe e qartë, "klasike e zhanrit", siç thonë ata. Punon në çdo version të Excel.
Cons është gjithashtu aty. Për të kërkuar produkte të shtuara në listën e re të çmimeve, do të duhet të bëni të njëjtën procedurë në drejtim të kundërt, dmth. me ndihmën e VLOOKUP-it, të ngrini çmime të reja në çmimin e vjetër. Nëse madhësitë e tabelave ndryshojnë nesër, atëherë formulat do të duhet të rregullohen. Epo, dhe në tavolina vërtet të mëdha (> 100 mijë rreshta), e gjithë kjo lumturi do të ngadalësohet mirë.
Metoda 2: Krahasimi i tabelave duke përdorur një pivot
Le të kopjojmë tabelat tona njëra nën tjetrën, duke shtuar një kolonë me emrin e listës së çmimeve, në mënyrë që më vonë të kuptoni se nga cila listë cili rresht:
Tani, bazuar në tabelën e krijuar, ne do të krijojmë një përmbledhje përmes Insert – PivotTable (Fut - Tabela kryesore). Le të hedhim një fushë Produkt në zonën e linjave, fushë Çmimi në zonën dhe fushën e kolonës Цena në diapazonin:
Siç mund ta shihni, tabela kryesore do të gjenerojë automatikisht një listë të përgjithshme të të gjitha produkteve nga listat e çmimeve të vjetra dhe të reja (pa përsëritje!) dhe do t'i renditë produktet sipas alfabetit. Mund të shihni qartë produktet e shtuara (nuk kanë çmimin e vjetër), produktet e hequra (nuk kanë çmimin e ri) dhe ndryshimet e çmimeve, nëse ka.
Shumat e mëdha në një tabelë të tillë nuk kanë kuptim dhe ato mund të çaktivizohen në skedë Konstruktori – Totalet e mëdha – Çaktivizo për rreshtat dhe kolonat (Dizajni - Totali i Madh).
Nëse çmimet ndryshojnë (por jo sasia e mallrave!), atëherë mjafton thjesht të përditësoni përmbledhjen e krijuar duke klikuar me të djathtën mbi të - freskoj.
rekuizitë: Kjo qasje është një renditje e madhësisë më e shpejtë me tabela të mëdha sesa VLOOKUP.
Cons: duhet të kopjoni manualisht të dhënat nën njëra-tjetrën dhe të shtoni një kolonë me emrin e listës së çmimeve. Nëse madhësitë e tabelave ndryshojnë, atëherë duhet të bëni gjithçka përsëri.
Metoda 3: Krahasimi i tabelave me Power Query
Power Query është një shtesë falas për Microsoft Excel që ju lejon të ngarkoni të dhëna në Excel nga pothuajse çdo burim dhe më pas t'i transformoni këto të dhëna në çdo mënyrë të dëshiruar. Në Excel 2016, kjo shtesë është tashmë e integruar si parazgjedhje në skedë Data (Të dhënat), dhe për Excel 2010-2013 duhet ta shkarkoni veçmas nga faqja e internetit e Microsoft dhe ta instaloni - merrni një skedë të re Kërkesa e energjisë.
Përpara se të ngarkojmë listat tona të çmimeve në Power Query, ato duhet së pari të konvertohen në tabela inteligjente. Për ta bërë këtë, zgjidhni diapazonin me të dhëna dhe shtypni kombinimin në tastierë Ctrl+T ose zgjidhni skedën në shirit Faqja kryesore – Formatoni si tabelë (Shtëpia - Formatoni si tabelë). Emrat e tabelave të krijuara mund të korrigjohen në skedë konstruktor (Unë do të lë standardin Tabela 1 и Tabela 2, të cilat merren si parazgjedhje).
Ngarko çmimin e vjetër në Power Query duke përdorur butonin Nga tabela/gama (Nga tabela/gama) nga skeda Data (Data) ose nga skeda Kërkesa e energjisë (në varësi të versionit të Excel). Pas ngarkimit, ne do të kthehemi në Excel nga Power Query me komandën Mbyll dhe ngarko – Mbyll dhe ngarko në… (Mbyll & Ngarko - Mbylle & Ngarko për…):
… dhe në dritaren që shfaqet, zgjidhni Thjesht krijoni një lidhje (Vetëm lidhje).
Përsëriteni të njëjtën gjë me listën e re të çmimeve.
Tani le të krijojmë një pyetje të tretë që do të kombinojë dhe krahasojë të dhënat nga dy të mëparshmet. Për ta bërë këtë, zgjidhni në Excel në skedën Të dhënat – Merr të dhëna – Kombinoje Kërkesat – Kombinoje (Të dhënat - Merr të dhëna - Pyetje për bashkimin - Bashkim) ose shtypni butonin Kombinoj (Shkrihet) tab Kërkesa e energjisë.
Në dritaren e bashkimit, zgjidhni tabelat tona në listat rënëse, zgjidhni kolonat me emrat e mallrave në to dhe në fund vendosni metodën e bashkimit - E jashtme e plotë (E jashtme e plotë):
Pas klikimit OK duhet të shfaqet një tabelë me tre kolona, ku në kolonën e tretë duhet të zgjeroni përmbajtjen e tabelave të mbivendosura duke përdorur shigjetën e dyfishtë në kokë:
Si rezultat, marrim bashkimin e të dhënave nga të dy tabelat:
Është më mirë, natyrisht, të riemërtoni emrat e kolonave në kokë duke klikuar dy herë mbi ato më të kuptueshme:
Dhe tani më interesante. Shkoni te skeda Shtoni kolonën (Shto kolonë) dhe klikoni në butonin Kolona e kushtëzuar (Kollona e kushtëzuar). Dhe më pas në dritaren që hapet, futni disa kushte testimi me vlerat e tyre përkatëse të daljes:
Mbetet për të klikuar OK dhe ngarkoni raportin që rezulton në Excel duke përdorur të njëjtin buton mbyllni dhe shkarkoni (Mbyll & Ngarko) tab Fillimi (Shtëpi):
Beauty.
Për më tepër, nëse në të ardhmen ndodh ndonjë ndryshim në listat e çmimeve (shtohen ose fshihen rreshtat, ndryshojnë çmimet, etj.), atëherë do të mjaftojë vetëm të përditësojmë kërkesat tona me një shkurtore të tastierës Ctrl+Ndalet+F5 ose me buton Rifresko të gjitha (Rifresko të gjitha) tab Data (Data).
rekuizitë: Ndoshta mënyra më e bukur dhe më e përshtatshme nga të gjitha. Punon me zgjuarsi me tavolina të mëdha. Nuk kërkon modifikime manuale kur ndryshoni madhësinë e tabelave.
Cons: Kërkon që të instalohet shtesa Power Query (në Excel 2010-2013) ose Excel 2016. Emrat e kolonave në të dhënat e burimit nuk duhet të ndryshohen, përndryshe do të marrim gabimin "Kollona e tillë dhe ajo nuk u gjet!" kur përpiqeni të përditësoni pyetjen.
- Si të mblidhni të dhëna nga të gjithë skedarët Excel në një dosje të caktuar duke përdorur Power Query
- Si të gjeni përputhjet midis dy listave në Excel
- Bashkimi i dy listave pa dublikatë