Turinys
Turime dvi lenteles (pavyzdžiui, seną ir naują kainoraščio versijas), kurias turime palyginti ir greitai rasti skirtumus:
Iš karto aišku, kad kažkas papildė naują kainoraštį (datos, česnakai...), kažkas dingo (gervuogės, avietės...), pasikeitė kai kurių prekių kainos (figos, melionai...). Turite greitai rasti ir parodyti visus šiuos pakeitimus.
Bet kuriai „Excel“ užduočiai beveik visada yra daugiau nei vienas sprendimas (dažniausiai 4–5). Mūsų problemai spręsti galima naudoti daugybę skirtingų metodų:
- funkcija VPR (ŽIŪRĖTI) - ieškoti produktų pavadinimų iš naujojo kainoraščio senajame ir rodyti seną kainą šalia naujos, o tada pastebėti skirtumus
- sujunkite du sąrašus į vieną ir pagal jį sukurkite suvestinę lentelę, kurioje bus aiškiai matomi skirtumai
- naudokite „Excel“ skirtą „Power Query“ priedą
Paimkime juos visus iš eilės.
1 būdas. Lentelių palyginimas su funkcija VLOOKUP
Jei jums visiškai nepažįstama ši nuostabi savybė, pirmiausia pažiūrėkite čia ir perskaitykite arba žiūrėkite vaizdo įrašą apie tai – sutaupykite porą metų gyvenimo.
Paprastai ši funkcija naudojama duomenims perkelti iš vienos lentelės į kitą, suderinant tam tikrą bendrą parametrą. Tokiu atveju naudosime ją, kad pakeistume senas kainas į naują:
Tie produktai, kuriems pasirodė klaida #N/A, senajame sąraše nėra, ty buvo įtraukti. Kainų pokyčiai taip pat aiškiai matomi.
Argumentai "už" šis metodas: paprastas ir aiškus, „žanro klasika“, kaip sakoma. Veikia bet kurioje „Excel“ versijoje.
Trūkumai taip pat yra. Norėdami ieškoti produktų, įtrauktų į naują kainoraštį, tą pačią procedūrą turėsite atlikti priešinga kryptimi, ty VLOOKUP pagalba pakelti naujas kainas iki senos kainos. Jeigu rytoj keisis lentelių dydžiai, tuomet teks koreguoti formules. Na, o ant tikrai didelių stalų (> 100 tūkst. eilučių) visa ši laimė padoriai sulėtės.
2 būdas. Lentelių palyginimas naudojant suvestinę
Nukopijuokime lenteles vieną po kita, pridėdami stulpelį su kainoraščio pavadinimu, kad vėliau suprastumėte, iš kurio sąrašo kuri eilutė:
Dabar, remdamiesi sukurta lentele, sukursime santrauką Įterpimas – PivotTable (Įterpti – Suvestinė lentelė). Užmeskime lauką Produktas į linijų sritį, lauką Kaina į stulpelio sritį ir lauką ЦENA į diapazoną:
Kaip matote, suvestinė lentelė automatiškai sugeneruos bendrą visų prekių sąrašą iš senojo ir naujojo kainoraščio (be pasikartojimų!) ir surūšiuos produktus abėcėlės tvarka. Aiškiai matosi pridėtos prekės (jie neturi senos kainos), pašalintos prekės (naujos kainos neturi) ir kainų pokyčiai, jei tokių yra.
Pagrindinės sumos tokioje lentelėje nėra prasmės ir jas galima išjungti skirtuke Konstruktorius – bendrosios sumos – išjungti eilučių ir stulpelių atveju (Dizainas – „Grand Totals“).
Pasikeitus kainoms (bet ne prekių kiekiui!), tuomet pakanka tiesiog atnaujinti sukurtą suvestinę spustelėjus ją dešiniuoju pelės klavišu – atnaujinti.
Argumentai "už": naudojant dideles lenteles šis metodas yra daug greitesnis nei VLOOKUP.
Trūkumai: reikia rankiniu būdu kopijuoti duomenis po vieną ir pridėti stulpelį su kainoraščio pavadinimu. Jeigu keičiasi lentelių dydžiai, tuomet tenka viską daryti iš naujo.
3 būdas: lentelių palyginimas su Power Query
„Power Query“ yra nemokamas „Microsoft Excel“ priedas, leidžiantis įkelti duomenis į „Excel“ iš beveik bet kokio šaltinio ir transformuoti šiuos duomenis bet kokiu norimu būdu. Programoje „Excel 2016“ šis priedas jau yra pagal numatytuosius nustatymus skirtuke Duomenys (duomenys), o Excel 2010-2013 reikia atsisiųsti atskirai iš Microsoft svetainės ir įdiegti – gauti naują skirtuką „Power Query“.
Prieš įkeldami mūsų kainoraščius į „Power Query“, pirmiausia juos reikia konvertuoti į išmaniąsias lenteles. Norėdami tai padaryti, pasirinkite diapazoną su duomenimis ir paspauskite klaviatūros kombinaciją "Ctrl"+T arba pasirinkite juostelės skirtuką Pagrindinis – formatuoti kaip lentelę (Pagrindinis – formatuoti kaip lentelę). Sukurtų lentelių pavadinimus galima koreguoti skirtuke Konstruktorius (Paliksiu standartą Lentelė 1 и Lentelė 2, kurie gaunami pagal numatytuosius nustatymus).
Įkelkite seną kainą į Power Query naudodami mygtuką Iš lentelės/diapazono (Iš lentelės / diapazono) iš skirtuko Duomenys (Data) arba iš skirtuko „Power Query“ (priklausomai nuo „Excel“ versijos). Po įkėlimo grįšime į Excel iš Power Query su komanda Uždaryti ir įkelti – uždaryti ir įkelti… (Uždaryti ir įkelti – uždaryti ir įkelti į...):
… ir pasirodžiusiame lange pasirinkite Tiesiog sukurkite ryšį (Tik ryšys).
Pakartokite tą patį su naujuoju kainoraščiu.
Dabar sukurkime trečią užklausą, kuri sujungs ir palygins ankstesnių dviejų duomenis. Norėdami tai padaryti, skirtuke Excel pasirinkite Duomenys – Gauti duomenis – Sujungti užklausas – Sujungti (Duomenys – gauti duomenis – sujungti užklausas – sujungti) arba paspauskite mygtuką Derinti (Sujungti) kortelė „Power Query“.
Sujungimo lange išskleidžiamuose sąrašuose pasirinkite mūsų lenteles, pažymėkite stulpelius su prekių pavadinimais ir apačioje nustatykite sujungimo būdą – Pilnas išorinis (Visas išorinis):
Spustelėję OK turėtų pasirodyti trijų stulpelių lentelė, kurioje trečiame stulpelyje reikia išplėsti įdėtųjų lentelių turinį, naudojant dvigubą rodyklę antraštėje:
Dėl to gauname abiejų lentelių duomenų sujungimą:
Žinoma, geriau pervardyti stulpelių pavadinimus antraštėje dukart spustelėjus suprantamesnius:
O dabar įdomiausia. Eikite į skirtuką Pridėti stulpelį (Pridėti stulpelį) ir spustelėkite mygtuką Sąlyginis stulpelis (Sąlyginis stulpelis). Tada atsidariusiame lange įveskite kelias bandymo sąlygas su atitinkamomis išvesties reikšmėmis:
Belieka spustelėti OK ir įkelkite gautą ataskaitą į „Excel“ naudodami tą patį mygtuką uždaryti ir atsisiųsti (Uždaryti ir įkelti) kortelė Pagrindinis (Namai):
Grožis.
Be to, jei ateityje kainoraščiuose įvyks kokių nors pasikeitimų (papildomos arba ištrinamos eilutės, pasikeis kainos ir pan.), užteks tiesiog atnaujinti užklausas sparčiuoju klavišu "Ctrl"+Kitas+F5 arba mygtuku Atnaujinti viską (Atnaujinti viską) kortelė Duomenys (Data).
Argumentai "už": Galbūt pats gražiausias ir patogiausias būdas. Protingai dirba su dideliais stalais. Keičiant lentelių dydį nereikia redaguoti rankiniu būdu.
Trūkumai: reikia įdiegti „Power Query“ papildinį („Excel 2010–2013“) arba „Excel 2016“. Stulpelių pavadinimų šaltinio duomenyse keisti negalima, antraip gausime klaidą „Tokio ir tokio stulpelis nerastas! bandant atnaujinti užklausą.
- Kaip rinkti duomenis iš visų „Excel“ failų tam tikrame aplanke naudojant „Power Query“.
- Kaip rasti atitikmenis tarp dviejų sąrašų programoje „Excel“.
- Dviejų sąrašų sujungimas be dublikatų