Contents [show]
Ons het twee tabelle (byvoorbeeld die ou en nuwe weergawes van die pryslys), wat ons moet vergelyk en vinnig die verskille vind:
Dit is onmiddellik duidelik dat iets by die nuwe pryslys gevoeg is (dadels, knoffel …), iets het verdwyn (braambessies, frambose …), pryse het verander vir sommige goedere (vye, spanspekke …). U moet al hierdie veranderinge vinnig vind en vertoon.
Vir enige taak in Excel is daar byna altyd meer as een oplossing (gewoonlik 4-5). Vir ons probleem kan baie verskillende benaderings gebruik word:
- funksie VPR (VERSOEKING) — soek produkname uit die nuwe pryslys in die ou een en vertoon die ou prys langs die nuwe een, en vang dan die verskille op
- voeg twee lyste saam in een en bou dan 'n spiltabel op grond daarvan, waar die verskille duidelik sigbaar sal wees
- gebruik die Power Query-byvoeging vir Excel
Kom ons neem hulle almal in volgorde.
Metode 1. Vergelyk tabelle met die VLOOKUP-funksie
As jy heeltemal onbekend is met hierdie wonderlike kenmerk, kyk dan eers hier en lees of kyk 'n video-tutoriaal daaroor - spaar jouself 'n paar jaar van die lewe.
Tipies word hierdie funksie gebruik om data van een tabel na 'n ander te trek deur een of ander algemene parameter te pas. In hierdie geval sal ons dit gebruik om die ou pryse in die nuwe prys in te druk:
Daardie produkte waarteen die #N/A-fout geblyk het, is nie in die ou lys nie, maw is bygevoeg. Prysveranderinge is ook duidelik sigbaar.
Pros hierdie metode: eenvoudig en duidelik, "klassiek van die genre", soos hulle sê. Werk in enige weergawe van Excel.
Nadele is ook daar. Om te soek na produkte wat by die nuwe pryslys gevoeg is, sal jy dieselfde prosedure in die teenoorgestelde rigting moet doen, dws nuwe pryse optrek na die ou prys met behulp van VLOOKUP. As die groottes van die tabelle môre verander, sal die formules aangepas moet word. Wel, en op werklik groot tafels (> 100 duisend rye), sal al hierdie geluk ordentlik vertraag.
Metode 2: Vergelyk tabelle deur 'n spilpunt te gebruik
Kom ons kopieer ons tabelle een onder mekaar en voeg 'n kolom by met die naam van die pryslys, sodat jy later kan verstaan uit watter lys watter ry:
Nou, gebaseer op die geskep tabel, sal ons 'n opsomming deur te skep Voeg in – draaitabel (Voeg in – Spiltabel). Kom ons gooi 'n veld Produk na die gebied van lyne, veld Prys na kolomarea en veld Цena in die reeks:
Soos u kan sien, sal die spiltabel outomaties 'n algemene lys van alle produkte vanaf die ou en nuwe pryslyste genereer (geen herhalings!) en die produkte alfabeties sorteer. Jy kan die bygevoegde produkte duidelik sien (hulle het nie die ou prys nie), die verwyderde produkte (hulle het nie die nuwe prys nie) en prysveranderings, indien enige.
Groottotale in so 'n tabel maak nie sin nie, en hulle kan op die oortjie gedeaktiveer word Konstrukteur – Groottotale – Deaktiveer vir rye en kolomme (Ontwerp – Groottotale).
As pryse verander (maar nie die hoeveelheid goedere nie!), dan is dit genoeg om eenvoudig die geskepte opsomming by te werk deur daarop te regsklik – Verfris.
Pros: Hierdie benadering is 'n orde van grootte vinniger met groot tabelle as VLOOKUP.
Nadele: jy moet die data handmatig onder mekaar kopieer en 'n kolom byvoeg met die naam van die pryslys. As die groottes van die tafels verander, dan moet jy alles weer van voor af doen.
Metode 3: Vergelyk tabelle met Power Query
Power Query is 'n gratis byvoeging vir Microsoft Excel wat jou in staat stel om data vanaf byna enige bron in Excel te laai en dan hierdie data op enige gewenste manier te transformeer. In Excel 2016 is hierdie byvoeging reeds by verstek op die oortjie ingebou data (Data), en vir Excel 2010-2013 moet jy dit apart van die Microsoft-webwerf aflaai en installeer – kry 'n nuwe oortjie Kragnavraag.
Voordat ons pryslyste in Power Query gelaai word, moet dit eers in slim tabelle omgeskakel word. Om dit te doen, kies die reeks met data en druk die kombinasie op die sleutelbord Ctrl+T of kies die oortjie op die lint Tuis – Formateer as 'n tabel (Tuis — Formateer as tabel). Die name van die geskepde tabelle kan op die blad reggestel word konstruktor (Ek sal die standaard verlaat Tabel 1 и Tabel 2, wat by verstek verkry word).
Laai die ou prys in Power Query deur die knoppie te gebruik Van tabel/reeks (Vanaf tabel/reeks) vanaf die blad data (Datum) of vanaf die blad Kragnavraag (afhangende van die weergawe van Excel). Nadat ons gelaai het, sal ons terugkeer na Excel vanaf Power Query met die opdrag Maak toe en laai – Maak toe en laai in... (Maak toe en laai — Maak toe en laai na...):
… en kies dan in die venster wat verskyn Skep net 'n verbinding (Slegs verbinding).
Herhaal dieselfde met die nuwe pryslys.
Kom ons skep nou 'n derde navraag wat die data van die vorige twee sal kombineer en vergelyk. Om dit te doen, kies in Excel op die blad Data – Kry Data – Kombineer Versoeke – Kombineer (Data – Kry data – Voeg navrae saam – voeg saam) of druk die knoppie Kombineer (Voeg saam) tab Kragnavraag.
In die aansluitingsvenster, kies ons tabelle in die aftreklyste, kies die kolomme met die name van die goedere daarin, en stel onderaan die aansluitingsmetode in – Voltooi ekstern (Volledige buitenste):
Na kliek op OK 'n tabel van drie kolomme moet verskyn, waar in die derde kolom jy die inhoud van geneste tabelle moet uitbrei deur die dubbelpyltjie in die kopskrif te gebruik:
As gevolg hiervan kry ons die samevoeging van data uit beide tabelle:
Dit is natuurlik beter om die kolomname in die kopskrif te hernoem deur op meer verstaanbare te dubbelklik:
En nou die interessantste. Gaan na oortjie Voeg kolom by (Voeg kolom by) en klik op die knoppie Voorwaardelike kolom (Voorwaardelike kolom). En voer dan in die venster wat oopmaak verskeie toetstoestande in met hul ooreenstemmende uitsetwaardes:
Dit bly om op te klik OK en laai die resulterende verslag op na Excel met dieselfde knoppie maak toe en laai af (Maak toe en laai) tab What is This (Tuis):
Skoonheid.
Verder, as enige veranderinge in die pryslyste in die toekoms plaasvind (lyne word bygevoeg of uitgevee, pryse verander, ens.), Dan sal dit genoeg wees om net ons versoeke op te dateer met 'n sleutelbordkortpad Ctrl+alt+F5 of per knoppie Verfris alles (Verfris alles) tab data (Datum).
Pros: Miskien die mooiste en gerieflikste manier van almal. Werk slim met groot tafels. Vereis nie handmatige wysigings wanneer die grootte van tabelle verander word nie.
Nadele: Vereis dat die Power Query-byvoeging (in Excel 2010-2013) of Excel 2016 geïnstalleer moet word. Die kolomname in die brondata moet nie verander word nie, anders sal ons die fout kry "Kol die en die is nie gevind nie!" wanneer jy probeer om die navraag op te dateer.
- Hoe om data van alle Excel-lêers in 'n gegewe vouer te versamel met behulp van Power Query
- Hoe om passings tussen twee lyste in Excel te vind
- Voeg twee lyste saam sonder duplikate