Edukiak
Bi taula ditugu (adibidez, prezioen zerrendaren bertsio zaharra eta berria), zeinak alderatu eta aldeak azkar aurkitu behar ditugu:
Berehala argi dago prezioen zerrenda berrian zerbait gehitu dela (datilak, baratxuriak...), zerbait desagertu dela (masustak, mugurdiak...), salgai batzuen prezioak aldatu direla (pikuak, meloiak...). Aldaketa horiek guztiak azkar aurkitu eta bistaratu behar dituzu.
Excel-en edozein zereginetarako, ia beti irtenbide bat baino gehiago dago (normalean 4-5). Gure arazorako, hainbat ikuspegi erabil daitezke:
- funtzioa VPR (VLOOKUP) — bilatu prezio-zerrenda berriko produktuen izenak zaharrean eta erakutsi prezio zaharra berriaren ondoan, eta ikusi desberdintasunak
- batu bi zerrenda bakarrean eta, ondoren, eraiki ezazu bertan oinarritutako taula dinamikoa, non desberdintasunak argi eta garbi ikusiko diren
- erabili Power Query gehigarria Excel-erako
Har ditzagun guztiak ordenan.
1. metodoa. Taulak alderatzea VLOOKUP funtzioarekin
Ezaugarri zoragarri hau guztiz ezagutzen ez baduzu, lehenik eta behin begiratu hemen eta irakurri edo ikusi horri buruzko bideo-tutorial bat - gorde bizitza urte pare bat.
Normalean, funtzio hau taula batetik bestera datuak ateratzeko erabiltzen da parametro arrunt batekin bat eginez. Kasu honetan, prezio zaharrak prezio berrira bultzatzeko erabiliko dugu:
#N/A akatsa gertatu zen produktu horiek ez daude zerrenda zaharrean, hau da, gehitu ziren. Prezioen aldaketak ere argi ikusten dira.
Pros metodo hau: sinplea eta argia, “generoaren klasikoa”, esaten den bezala. Excel-en edozein bertsiotan funtzionatzen du.
Cons ere hor dago. Prezio-zerrenda berrira gehitutako produktuak bilatzeko, prozedura bera egin beharko duzu kontrako noranzkoan, hau da, prezio berriak igo prezio zaharrera VLOOKUP-en laguntzaz. Taulen tamainak bihar aldatzen badira, formulak egokitu beharko dira. Beno, eta benetan mahai handietan (> 100 mila errenkada), zoriontasun hori guztia dexente motelduko da.
2. metodoa: taulak pibota erabiliz alderatzea
Kopiatu ditzagun gure taulak bata bestearen azpian, prezioen zerrendaren izena duen zutabe bat gehituz, gerora zein zerrendatatik zein errenkadatik uler dezazun:
Orain, sortutako taulan oinarrituta, laburpen bat sortuko dugu Txertatu - Taula dinamikoa (Txertatu — taula dinamikoa). Bota dezagun zelai bat Produktuen lerroen eremura, eremura Prezioa zutabearen eremura eta eremura Цena barrutian:
Ikus dezakezunez, taula dinamikoak automatikoki sortuko du prezio-zerrenda zahar eta berrietako produktu guztien zerrenda orokorra (errepikapenik gabe!) eta produktuak alfabetikoki ordenatuko ditu. Argi eta garbi ikus ditzakezu gehitutako produktuak (ez dute prezio zaharra), kendutako produktuak (ez dute prezio berria) eta prezio aldaketak, halakorik balego.
Taula horretako guztirakoek ez dute zentzurik, eta fitxan desgaitu daitezke Eraikitzailea - Guztira - Desgaitu errenkada eta zutabeetarako (Diseinua — Guztira).
Prezioak aldatzen badira (baina ez ondasunen kopurua!), nahikoa da sortutako laburpena eguneratzea gainean eskuineko botoiarekin klik eginez - Freskatu.
Pros: Ikuspegi hau VLOOKUP baino tamaina-ordena azkarragoa da taula handiekin.
Cons: eskuz kopiatu behar dituzu datuak bata bestearen azpian eta zutabe bat gehitu prezioen zerrendaren izena duen. Taulen tamainak aldatzen badira, dena berriro egin beharko duzu.
3. metodoa: Power Query-rekin taulak alderatzea
Power Query Microsoft Excel-erako doako gehigarri bat da, datuak Excel-era ia edozein iturritatik kargatzeko eta, ondoren, datu horiek nahi duzun moduan eraldatzeko. Excel 2016-n, gehigarri hau lehenetsita dago jada fitxan Data (datuak), eta Excel 2010-2013rako Microsoft-en webgunetik bereizita deskargatu eta instalatu behar duzu - fitxa berri bat lortu Power kontsulta.
Gure prezio-zerrendak Power Query-n kargatu aurretik, lehenik eta behin taula adimendunetan bihurtu behar dira. Horretarako, hautatu datuen barrutia eta sakatu teklatuko konbinazioa Ctrl+T edo hautatu zintaren fitxa Hasiera - Taula gisa formatu (Hasiera — Taula gisa formatu). Sortutako taulen izenak fitxan zuzendu daitezke Constructor (Estandarra utziko dut Table 1 и Table 2, lehenespenez lortzen direnak).
Kargatu prezio zaharra Power Query-n botoia erabiliz Taula/Barrutitik (Taula/Barrutitik) fitxatik Data (Data) edo fitxatik Power kontsulta (Excel-en bertsioaren arabera). Kargatu ondoren, Power Query-tik Excel-era itzuliko gara komandoarekin Itxi eta kargatu – Itxi eta kargatu… (Itxi eta kargatu — Itxi eta kargatu hona...):
… eta agertzen den leihoan hautatu Konexio bat sortu besterik ez dago (Konexioa soilik).
Errepikatu gauza bera prezioen zerrenda berriarekin.
Orain sor dezagun hirugarren kontsulta bat, aurreko bietako datuak konbinatuko eta alderatuko dituena. Horretarako, hautatu Excel-en fitxan Datuak – Lortu datuak – Konbinatu eskaerak – Konbinatu (Datuak — Lortu datuak — Bateratu kontsultak — Batu) edo sakatu botoia Konbinatu (Bateatu) fitxa Power kontsulta.
Elkartzeko leihoan, hautatu gure taulak goitibeherako zerrendetan, hautatu ondasunen izenak dituzten zutabeak eta behealdean, ezarri elkartzeko metodoa - Kanpo osoa (Kanpo osoa):
Klik egin ondoren OK hiru zutabeko taula agertu behar da, non hirugarren zutabean habiaratutako taulen edukia zabaldu behar duzun goiburuko gezi bikoitza erabiliz:
Ondorioz, bi tauletako datuak bateratzea lortzen dugu:
Hobe da, noski, goiburuko zutabeen izenak aldatzea, ulergarriagoak direnetan klik bikoitza eginez:
Eta orain interesgarriena. Joan fitxara Gehitu zutabea (Gehitu zutabea) eta egin klik botoian Baldintzapeko zutabea (Baldintzazko zutabea). Eta gero irekitzen den leihoan, sartu hainbat proba baldintza dagozkion irteera-balioekin:
Klik egiteko geratzen da OK eta igo emaitzaren txostena Excelera botoi bera erabiliz itxi eta deskargatu (Itxi eta kargatu) fitxa Hasiera (Hasiera):
Beauty.
Gainera, etorkizunean prezioen zerrendetan aldaketaren bat gertatzen bada (lerroak gehitzen edo ezabatzen dira, prezioak aldatzen dira, etab.), orduan nahikoa izango da gure eskaerak teklatuko lasterbide batekin eguneratzea besterik ez. Ctrl+Alt+F5 edo botoiaren bidez Freskatu guztiak (Freskatu guztiak) fitxa Data (Data).
Pros: Agian biderik ederrena eta erosoena. Mahai handiekin modu egokian funtzionatzen du. Ez du eskuzko ediziorik behar taulak tamaina aldatzean.
Cons: Power Query gehigarria (Excel 2010-2013-n) edo Excel 2016 instalatu behar du. Iturburuko datuetako zutabe-izenak ez dira aldatu behar, bestela "Ez da aurkitu zutabea ez da aurkitu!" kontsulta eguneratzen saiatzean.
- Nola bildu Excel fitxategi guztien datuak karpeta jakin batean Power Query erabiliz
- Nola aurkitu Excel-en bi zerrenden arteko partidak
- Bi zerrenda bikoiztu gabe bateratzea