Nilalaman
Mayroon kaming dalawang talahanayan (halimbawa, ang luma at bagong bersyon ng listahan ng presyo), na kailangan naming ihambing at mabilis na mahanap ang mga pagkakaiba:
Kaagad na malinaw na may naidagdag sa bagong listahan ng presyo (mga petsa, bawang ...), may nawala (mga blackberry, raspberry ...), nagbago ang mga presyo para sa ilang mga kalakal (mga igos, melon ...). Kailangan mong mabilis na mahanap at ipakita ang lahat ng mga pagbabagong ito.
Para sa anumang gawain sa Excel, halos palaging mayroong higit sa isang solusyon (karaniwan ay 4-5). Para sa aming problema, maraming iba't ibang mga diskarte ang maaaring gamitin:
- tungkulin VPR (VLOOKUP) — maghanap ng mga pangalan ng produkto mula sa bagong listahan ng presyo sa luma at ipakita ang lumang presyo sa tabi ng bago, at pagkatapos ay mahuli ang mga pagkakaiba
- pagsamahin ang dalawang listahan sa isa at pagkatapos ay bumuo ng pivot table batay dito, kung saan ang mga pagkakaiba ay malinaw na makikita
- gamitin ang Power Query Add-in para sa Excel
Ayusin natin silang lahat.
Paraan 1. Paghahambing ng mga talahanayan sa VLOOKUP function
Kung ikaw ay ganap na hindi pamilyar sa kahanga-hangang tampok na ito, pagkatapos ay tumingin muna dito at basahin o manood ng isang video tutorial tungkol dito - iligtas ang iyong sarili ng ilang taon ng buhay.
Karaniwan, ang function na ito ay ginagamit upang hilahin ang data mula sa isang talahanayan patungo sa isa pa sa pamamagitan ng pagtutugma ng ilang karaniwang parameter. Sa kasong ito, gagamitin namin ito upang itulak ang mga lumang presyo sa bagong presyo:
Ang mga produktong iyon, kung saan lumabas ang #N/A error, ay wala sa lumang listahan, ibig sabihin, idinagdag. Ang mga pagbabago sa presyo ay malinaw ding nakikita.
Mga kalamangan ang pamamaraang ito: simple at malinaw, "klasiko ng genre", gaya ng sinasabi nila. Gumagana sa anumang bersyon ng Excel.
Kahinaan nandoon din. Upang maghanap ng mga produktong idinagdag sa bagong listahan ng presyo, kakailanganin mong gawin ang parehong pamamaraan sa kabaligtaran na direksyon, ie pull up ng mga bagong presyo sa lumang presyo sa tulong ng VLOOKUP. Kung magbabago ang mga sukat ng mga talahanayan bukas, kailangang ayusin ang mga formula. Buweno, at sa talagang malalaking mesa (> 100 libong mga hanay), ang lahat ng kaligayahang ito ay disenteng bumagal.
Paraan 2: Paghahambing ng mga talahanayan gamit ang isang pivot
Kopyahin natin ang aming mga talahanayan sa ilalim ng isa, pagdaragdag ng isang column na may pangalan ng listahan ng presyo, upang sa ibang pagkakataon ay maunawaan mo mula sa kung aling listahan kung aling row:
Ngayon, batay sa ginawang talahanayan, gagawa kami ng buod sa pamamagitan ng Ipasok – PivotTable (Ipasok — Pivot Table). Magtapon tayo ng field produkto sa lugar ng mga linya, field presyo sa column area at field Цena sa saklaw:
Gaya ng nakikita mo, awtomatikong bubuo ang pivot table ng pangkalahatang listahan ng lahat ng produkto mula sa luma at bagong mga listahan ng presyo (walang pag-uulit!) at pag-uuri-uriin ang mga produkto ayon sa alpabeto. Malinaw mong makikita ang mga idinagdag na produkto (wala silang lumang presyo), ang mga inalis na produkto (wala silang bagong presyo) at mga pagbabago sa presyo, kung mayroon man.
Ang mga malalaking kabuuan sa naturang talahanayan ay walang saysay, at maaari silang i-disable sa tab Constructor – Grand totals – I-disable para sa mga row at column (Disenyo — Mga Grand Total).
Kung nagbabago ang mga presyo (ngunit hindi ang dami ng mga kalakal!), Sapat na lamang na i-update ang ginawang buod sa pamamagitan ng pag-right-click dito – Papanariwain.
Mga kalamangan: Ang diskarte na ito ay isang order ng magnitude na mas mabilis na may malalaking talahanayan kaysa sa VLOOKUP.
Kahinaan: kailangan mong manu-manong kopyahin ang data sa ilalim ng bawat isa at magdagdag ng column na may pangalan ng listahan ng presyo. Kung nagbabago ang mga sukat ng mga talahanayan, kailangan mong gawin muli ang lahat.
Paraan 3: Paghahambing ng mga talahanayan sa Power Query
Ang Power Query ay isang libreng add-in para sa Microsoft Excel na nagbibigay-daan sa iyong mag-load ng data sa Excel mula sa halos anumang pinagmulan at pagkatapos ay baguhin ang data na ito sa anumang gustong paraan. Sa Excel 2016, ang add-in na ito ay naka-built in na bilang default sa tab data (Data), at para sa Excel 2010-2013 kailangan mong i-download ito nang hiwalay mula sa website ng Microsoft at i-install ito - kumuha ng bagong tab kapangyarihan query.
Bago i-load ang aming mga listahan ng presyo sa Power Query, dapat munang i-convert ang mga ito sa mga smart table. Upang gawin ito, piliin ang hanay na may data at pindutin ang kumbinasyon sa keyboard Ctrl+T o piliin ang tab sa ribbon Tahanan – I-format bilang isang talahanayan (Home — Format bilang Talahanayan). Ang mga pangalan ng ginawang mga talahanayan ay maaaring itama sa tab Tagagawa (Iiwan ko ang pamantayan Table 1 и Table 2, na nakukuha bilang default).
I-load ang lumang presyo sa Power Query gamit ang button Mula sa Table/Range (Mula sa Talahanayan/Hanay) mula sa tab data (Petsa) o mula sa tab kapangyarihan query (depende sa bersyon ng Excel). Pagkatapos mag-load, babalik kami sa Excel mula sa Power Query kasama ang command Isara at i-load – Isara at i-load sa… (Isara at I-load — Isara at I-load Sa…):
… at sa lalabas na window pagkatapos ay piliin Gumawa lang ng koneksyon (Koneksyon Lang).
Ulitin ang parehong sa bagong listahan ng presyo.
Ngayon, gumawa tayo ng pangatlong query na magsasama-sama at maghahambing ng data mula sa naunang dalawa. Upang gawin ito, piliin sa Excel sa tab Data – Kumuha ng Data – Pagsamahin ang Mga Kahilingan – Pagsamahin (Data — Kumuha ng Data — Pagsamahin ang Mga Query — Pagsamahin) o pindutin ang pindutan Pagsamahin (Pagsamahin) tab kapangyarihan query.
Sa window ng pagsali, piliin ang aming mga talahanayan sa mga drop-down na listahan, piliin ang mga column na may mga pangalan ng mga kalakal sa mga ito, at sa ibaba, itakda ang paraan ng pagsali - Kumpletuhin ang panlabas (Buong Panlabas):
Pagkatapos ng pag-click sa OK dapat lumitaw ang isang talahanayan ng tatlong hanay, kung saan sa ikatlong hanay kailangan mong palawakin ang mga nilalaman ng mga nested na talahanayan gamit ang dobleng arrow sa header:
Bilang resulta, nakukuha namin ang pagsasama ng data mula sa parehong mga talahanayan:
Mas mainam, siyempre, na palitan ang pangalan ng mga pangalan ng column sa header sa pamamagitan ng pag-double click sa mga mas naiintindihan:
At ngayon ang pinaka-kawili-wili. Pumunta sa tab Magdagdag ng haligi (Magdagdag ng Column) at mag-click sa pindutan Kolumn na may kondisyon (Conditional Column). At pagkatapos ay sa window na bubukas, magpasok ng ilang mga kondisyon ng pagsubok kasama ang kanilang mga katumbas na halaga ng output:
Ito ay nananatiling mag-click sa OK at i-upload ang resultang ulat sa Excel gamit ang parehong button isara at i-download (Isara at I-load) tab Tahanan (Tahanan):
Kagandahan.
Bukod dito, kung may anumang mga pagbabago na magaganap sa mga listahan ng presyo sa hinaharap (mga linya ay idinagdag o tinanggal, nagbabago ang mga presyo, atbp.), kung gayon ito ay sapat lamang na i-update ang aming mga kahilingan gamit ang isang keyboard shortcut Ctrl+Alt+F5 o sa pamamagitan ng pindutan I-refresh lahat (I-refresh Lahat) tab data (Petsa).
Mga kalamangan: Marahil ang pinakamaganda at maginhawang paraan sa lahat. Gumagana nang matalino sa malalaking mesa. Hindi nangangailangan ng mga manu-manong pag-edit kapag binabago ang laki ng mga talahanayan.
Kahinaan: Nangangailangan ng Power Query add-in (sa Excel 2010-2013) o Excel 2016 upang mai-install. Ang mga pangalan ng column sa source data ay hindi dapat palitan, kung hindi, makakakuha tayo ng error na "Hindi nahanap ang column na ganito at ganoon!" kapag sinusubukang i-update ang query.
- Paano mangolekta ng data mula sa lahat ng Excel file sa isang partikular na folder gamit ang Power Query
- Paano makahanap ng mga tugma sa pagitan ng dalawang listahan sa Excel
- Pinagsasama ang dalawang listahan nang walang mga duplicate