Saturs
Mums ir divas tabulas (piemēram, cenrāža vecā un jaunā versija), kuras jāsalīdzina un ātri jāatrod atšķirības:
Uzreiz ir skaidrs, ka kaut kas ir pievienots jaunajam cenrādim (dateles, ķiploki ...), kaut kas ir pazudis (kazenes, avenes ...), dažām precēm ir mainījušās cenas (vīģes, melones ...). Jums ātri jāatrod un jāparāda visas šīs izmaiņas.
Jebkuram uzdevumam programmā Excel gandrīz vienmēr ir vairāk nekā viens risinājums (parasti 4–5). Mūsu problēmai var izmantot daudzas dažādas pieejas:
- funkcija VPR (MEKLĒT) — meklēt produktu nosaukumus no jaunā cenrāža vecajā un parādīt veco cenu blakus jaunajam, un pēc tam uztvert atšķirības
- apvienojiet divus sarakstus vienā un pēc tam izveidojiet rakurstabulu, pamatojoties uz to, kur atšķirības būs skaidri redzamas
- izmantojiet Power Query pievienojumprogrammu programmai Excel
Ņemsim tos visus kārtībā.
1. metode. Tabulu salīdzināšana ar funkciju VLOOKUP
Ja šī brīnišķīgā īpašība jums ir pavisam sveša, tad vispirms ieskatieties šeit un izlasiet vai noskatieties video pamācību par to – ietaupiet sev pāris dzīves gadus.
Parasti šo funkciju izmanto, lai pārvilktu datus no vienas tabulas uz citu, saskaņojot kādu parastu parametru. Šajā gadījumā mēs to izmantosim, lai vecās cenas iekļautu jaunajā cenā:
Tie produkti, kuriem izrādījās kļūda #N/A, nav vecajā sarakstā, ti, tika pievienoti. Arī cenu izmaiņas ir skaidri redzamas.
Plusi šī metode: vienkārša un skaidra, “žanra klasika”, kā saka. Darbojas jebkurā Excel versijā.
Mīnusi ir arī tur. Lai meklētu preces, kas pievienotas jaunajam cenrādim, jums būs jāveic tā pati procedūra pretējā virzienā, proti, ar VLOOKUP palīdzību jāpaceļ jaunas cenas līdz vecajai cenai. Ja rīt mainīsies tabulu izmēri, tad būs jāpielāgo formulas. Nu un uz tiešām lieliem galdiem (> 100 tūkstoši rindu) visa šī laime pieklājīgi palēnināsies.
2. metode: tabulu salīdzināšana, izmantojot rakursu
Kopēsim savas tabulas vienu zem otras, pievienojot kolonnu ar cenrāža nosaukumu, lai vēlāk varētu saprast, no kura saraksta kura rinda:
Tagad, pamatojoties uz izveidoto tabulu, mēs izveidosim kopsavilkumu Ievietošana — PivotTable (Ievietot — rakurstabula). Uzmetīsim laukā Prece līniju laukumam, laukam Сena uz kolonnas apgabalu un lauku ЦENA diapazonā:
Kā redzat, rakurstabula automātiski ģenerēs vispārīgu visu produktu sarakstu no vecajiem un jaunajiem cenrāžiem (bez atkārtojumiem!) un sakārtos produktus alfabētiskā secībā. Var skaidri redzēt pievienotās preces (tiem nav vecās cenas), izņemtās preces (tiem nav jaunās cenas) un cenu izmaiņas, ja tādas ir.
Galvenās summas šādā tabulā nav jēgas, un tās var atspējot cilnē Konstruktors — kopējās summas — atspējot rindām un kolonnām (Dizains — Grand Totals).
Ja mainās cenas (bet ne preču daudzums!), tad pietiek vienkārši atjaunināt izveidoto kopsavilkumu, noklikšķinot uz tā ar peles labo pogu – atsvaidzināt.
Plusi: šī pieeja ir daudz ātrāka ar lielām tabulām nekā VLOOKUP.
Mīnusi: manuāli jākopē dati viens zem otra un jāpievieno kolonna ar cenrāža nosaukumu. Ja mainās galdu izmēri, tad viss ir jādara no jauna.
3. metode: tabulu salīdzināšana ar Power Query
Power Query ir bezmaksas Microsoft Excel pievienojumprogramma, kas ļauj ielādēt datus programmā Excel no gandrīz jebkura avota un pēc tam pārveidot šos datus jebkurā vēlamajā veidā. Programmā Excel 2016 šī pievienojumprogramma jau ir iebūvēta cilnē pēc noklusējuma Datums (Dati), un programmai Excel 2010-2013 tas ir jālejupielādē atsevišķi no Microsoft vietnes un jāinstalē - iegūstiet jaunu cilni Jaudas vaicājums.
Pirms mūsu cenu sarakstu ielādēšanas programmā Power Query tie vispirms ir jāpārveido viedtabulās. Lai to izdarītu, atlasiet diapazonu ar datiem un nospiediet tastatūras kombināciju Ctrl+T vai atlasiet lentes cilni Sākums — formatējiet kā tabulu (Sākums — formatēt kā tabulu). Izveidoto tabulu nosaukumus var labot cilnē Konstruktors (Es atstāšu standartu Tabula 1 и Tabula 2, kas tiek iegūti pēc noklusējuma).
Ielādējiet veco cenu programmā Power Query, izmantojot pogu No tabulas/diapazona (No tabulas/diapazona) no cilnes Datums (Datums) vai no cilnes Jaudas vaicājums (atkarībā no Excel versijas). Pēc ielādes mēs atgriezīsimies programmā Excel no Power Query ar komandu Aizvērt un ielādēt – aizvērt un ielādēt… (Aizvērt un ielādēt — aizvērt un ielādēt...):
… un parādītajā logā atlasiet Vienkārši izveidojiet savienojumu (Tikai savienojums).
Atkārtojiet to pašu ar jauno cenrādi.
Tagad izveidosim trešo vaicājumu, kas apvienos un salīdzinās datus no iepriekšējiem diviem. Lai to izdarītu, cilnē atlasiet programmu Excel Dati – Saņemt datus – Apvienot pieprasījumus – Apvienot (Dati — iegūt datus — apvienot vaicājumus — sapludināt) vai nospiediet pogu Apvienot (Apvienot) tab Jaudas vaicājums.
Savienošanas logā nolaižamajos sarakstos atlasiet mūsu tabulas, atlasiet kolonnas ar tajās esošo preču nosaukumiem un apakšā iestatiet savienošanas metodi – Pilnīga ārējā (Pilns ārējais):
Pēc noklikšķināšanas uz OK jāparādās tabulai ar trim kolonnām, kur trešajā kolonnā ir jāpaplašina ligzdoto tabulu saturs, izmantojot dubulto bultiņu galvenē:
Rezultātā mēs iegūstam datu apvienošanu no abām tabulām:
Protams, labāk ir pārdēvēt kolonnu nosaukumus galvenē, veicot dubultklikšķi uz saprotamākiem:
Un tagad pats interesantākais. Dodieties uz cilni Pievienot kolonnu (Pievienot kolonnu) un noklikšķiniet uz pogas Nosacījuma kolonna (nosacījuma kolonna). Un pēc tam atvērtajā logā ievadiet vairākus testa nosacījumus ar tiem atbilstošajām izvades vērtībām:
Atliek noklikšķināt OK un augšupielādējiet iegūto pārskatu programmā Excel, izmantojot to pašu pogu aizveriet un lejupielādējiet (Aizvērt un ielādēt) tab Sākumlapa (Mājas):
Skaistums.
Turklāt, ja cenrāžos nākotnē notiks kādas izmaiņas (rindas tiek pievienotas vai dzēstas, cenas mainās utt.), tad pietiks tikai atjaunināt mūsu pieprasījumus ar īsinājumtaustiņu Ctrl+cits+F5 vai ar pogu Atsvaidzināt visu (Atsvaidzināt visu) tab Datums (Datums).
Plusi: Varbūt visskaistākais un ērtākais veids. Gudri strādā ar lieliem galdiem. Mainot tabulu izmērus, nav nepieciešama manuāla rediģēšana.
Mīnusi: ir jāinstalē Power Query pievienojumprogramma (programmā Excel 2010–2013) vai Excel 2016. Kolonnu nosaukumus avota datos mainīt nedrīkst, pretējā gadījumā tiks parādīts kļūdas ziņojums “Tāda un tāda kolonna netika atrasta!” mēģinot atjaunināt vaicājumu.
- Kā apkopot datus no visiem Excel failiem noteiktā mapē, izmantojot Power Query
- Kā programmā Excel atrast atbilstību starp diviem sarakstiem
- Divu sarakstu sapludināšana bez dublikātiem