Ofte har Excel-brugere en opgave at sammenligne to tabeller eller lister for at identificere forskelle i dem eller manglende elementer. Hver bruger klare denne opgave på sin egen måde, men oftest er en ret stor mængde tid brugt til at løse det specificerede spørgsmål, da ikke alle tilgange til dette problem er rationelt. Samtidig er der flere dokumenterede handlinger algoritmer, der gør det muligt at sammenligne lister eller bordarrayer på temmelig kort tid med minimal stor indsats. Lad os overveje detaljer disse muligheder.
På samme måde kan du sammenligne dataene i de tabeller, der er placeret på forskellige ark. Men i dette tilfælde er det ønskeligt, at linjerne i dem er nummereret. Resten af sammenligningsproceduren er næsten nøjagtig den samme som beskrevet ovenfor, udover det faktum, at når den foretager formlen, er det nødvendigt at skifte mellem ark. I vores tilfælde vil udtrykket have følgende formular:
= B2 = LIST2! B2
Det vil sige, som vi ser foran datakoordinaterne, som er placeret på andre plader, bortset fra, hvor sammenligningsresultatet vises, er arkantalet og et udråbstegn angivet.
Metode 2: Udvælgelse af grupper af celler
Sammenligning kan laves ved hjælp af værktøjet til adskillelse af grupper af celler. Med det kan du også sammenligne kun synkroniserede og bestilte lister. Derudover skal lister i dette tilfælde være placeret ved siden af hinanden på et ark.
- Vælg sammenlignede arrays. Gå til fanen "Home". Klik derefter på ikonet "Find og vælg", som er placeret på båndet i redigeringsværktøjslinjen. Der er en liste, hvor du skal vælge positionen "Valg af gruppen af celler ...".
Derudover kan udvælgelsen af gruppen af celler nås på en anden måde. Denne mulighed vil især være nyttig for de brugere, der har den version af programmet tidligere end Excel 2007, da metoden via knappen "Find og Select ikke understøtter disse applikationer. Vælg Arrays, der ønsker at sammenligne, og klik på F5-tasten.
- Et lille overgangsvindue er aktiveret. Klik på knappen "Highlight ..." i nederste venstre hjørne.
- Derefter lanceres udvælgelsesvinduet for cellegrupper, uanset hvor to af de ovennævnte muligheder du har valgt. Installer kontakten til "Highlight on Line" -positionen. Klik på knappen "OK".
- Da vi kan se, vil de inkonsekvente værdier af linjerne blive fremhævet med en anden farvetone. Hertil kommer, hvordan kan bedømmes fra indholdet af formellinjen, vil programmet lave en aktiv en af cellerne i de indikerede ikke-faldende linjer.
Metode 3: Betinget formatering
Du kan foretage en sammenligning ved at anvende betinget formateringsmetode. Som i den foregående metode bør sammenlignede områder være på et enkelt Excel-arbejdsark og synkroniseres med hinanden.
- Først og fremmest vælger vi, hvad tabelregionen vil blive betragtet som hovedet, og i hvad man skal kigge efter forskellen. Sidst lad os gøre i anden tabel. Derfor tildeler vi en liste over arbejdstagere i den. Flyt til fanen "Home", klik på knappen "Conditional Formatering", som har en placering på båndet i "Styles" -blokken. Fra rullelisten, gå gennem "Regler Management" -genstanden.
- Vinduet Regel Manager Dispatcher er aktiveret. Vi klikker på det til knappen "Opret regel".
- I vinduet Running vælger vi positionen "Brug Formula". I feltet "Formatcelle" skal du skrive en formel, der indeholder adresserne på de første celler i intervallerne af sammenlignet kolonner, adskilt af "ikke lige" tegnet (). Lige før dette udtryk vil denne gang stå tegnet "=". Hertil kommer, at for alle kolonne koordinater i denne formel skal du anvende en absolut adressation. For at gøre dette tildeler vi formlen med markøren og klik på F4-tasten tre gange. Som vi kan se, dukkede Dollar-tegnet i nærheden af alle kolonneadresserne, hvilket betyder at dreje forbindelserne til absolutte. For vores særlige tilfælde vil formlen tage følgende formular:
= $ A2 $ D2
Vi registrerer dette udtryk på ovenstående felt. Derefter klikker vi på knappen "Format ...".
- Vinduet "Celleformat" er aktiveret. Vi går til fanen "Fyld". Her i listen over farver stopper valget på farven, som vi vil male de elementer, hvor data ikke vil matche. Klik på knappen "OK".
- Vejledning til formateringsregelvinduet, klik på knappen "OK".
- Efter automatisk flytning til vinduet "Rules Manager", skal du klikke på knappen "OK" og i den.
- Nu i den anden tabel vil elementer, der har data, der er inkonsekvente med de tilsvarende værdier af det første tabelområde, blive fremhævet i den valgte farve.
Der er en anden måde at anvende betinget formatering til at udføre opgaven. Ligesom de tidligere muligheder kræver det placeringen af begge sammenlignede områder på et ark, men i modsætning til de tidligere beskrevne metoder vil synkroniseringsbetingelsen eller sorteringsdataene ikke være obligatoriske, hvilket adskiller denne valgmulighed fra tidligere beskrevet.
- Vi producerer udvælgelsen af områder, du skal sammenligne.
- Vi udfører overgangen til fanen kaldet "Home". Vi klikker på knappen "Conditional Formatering". I den aktiverede liste skal du vælge positionen "regler for tildeling af celler". I den næste menu skal du gøre "gentagende værdier" -positionen.
- De dobbelte værdier lanceres. Hvis du er færdig korrekt, så er det kun i dette vindue kun at klikke på knappen "OK". Selvom du ønsker, i det relevante område i dette vindue, kan du vælge en anden farve på valget.
- Når vi producerer den angivne handling, vil alle gentagne elementer blive fremhævet i den valgte farve. De elementer, der ikke falder sammen, vil forblive malet i deres oprindelige farve (som standard hvid). Således kan du straks se visuelt for at se, hvad forskellen mellem arrays.
Hvis du ønsker det, kan du tværtimod male de inkonsekvente elementer, og de indikatorer, der matcher, forlader med fylden af den foregående farve. Samtidig er algoritmen for handling næsten det samme, men i indstillingerne for de dobbelte værdier i det første felt, i stedet for den "gentagne" parameter, vælg den "unikke" parameter. Derefter klik på knappen "OK".
Således er det netop de indikatorer, der ikke falder sammen.
Lektion: Betinget formatering i Excel
Metode 4: Omfattende formel
Sammenlign også data ved hjælp af en kompleks formel, hvis grundlag er målerens funktion. Ved hjælp af dette værktøj kan du beregne, hvor meget hvert element fra den valgte kolonne i den anden tabel gentages i den første.
Operatørrådene refererer til den statistiske gruppe af funktioner. Dens opgave er at tælle antallet af celler, de værdier, som opfylder den angivne tilstand. Denne operatørs syntaks har denne art:
= Skemaer (rækkevidde; kriterium)
Argumentet "Range" er adressen til arrayet, som beregner de sammenfaldende værdier.
Argumentet "Kriterier" specificerer betingelsen for tilfældigheden. I vores tilfælde vil det være koordinaterne for de specifikke celler i det første tabelområde.
- Vi tildeler det første element i den ekstra kolonne, hvor antallet af kampe beregnes. Klik derefter på ikonet "Indsæt funktion".
- Guiden fungerer. Gå til kategorien "Statistisk". Find i listen navnet "Countecles". Efter dets valg, klik på knappen "OK".
- Operatørens argumenter vindue begyndte at køre. Som vi ser, svarer navnene på felterne i dette vindue til navnene på argumenterne.
Installer markøren i feltet "RANGE". Derefter tildele ved at holde venstre museknap allokér alle værdierne i kolonnen med navnene på den anden tabel. Som du kan se, falder koordinaterne straks i det angivne felt. Men til vores formål skal denne adresse foretages af absolutte. For at gøre dette skal du fremhæve koordinaterne i feltet og klikke på F4-tasten.
Som du kan se, tog referencen den absolutte form, som er karakteriseret ved tilstedeværelsen af dollar tegn.
Gå derefter til feltet "Kriterium" ved at installere markøren. Klik på det første element med de sidste navne i det første tabelområde. I dette tilfælde skal du forlade linket relativ. Når det vises i feltet, kan du klikke på knappen "OK".
- Resultatet udskrives på arkelementet. Det er lig med nummeret "1". Det betyder, at navnet på listen over navne på den anden tabel, som er den første i listen over den første tabel array, forekommer en gang i listen over den første tabel array, en gang.
- Nu skal vi skabe et lignende udtryk og for alle andre elementer i den første tabel. For at gøre dette vil jeg udføre kopiering, ved hjælp af påfyldningsmarkøren, som vi allerede har gjort før. Vi sætter markøren til den nederste højre del af bladelementet, som indeholder målerens funktion, og efter at have konverteret den til påfyldningsmarkøren, klem den venstre museknap og træk markøren ned.
- Som vi kan se, har programmet lavet en beregning af sammenfald, der sammenligner hver celle i den første tabel med data, som er placeret i det andet bordområde. I fire tilfælde var resultatet "1" og i to tilfælde - "0". Det vil sige, at programmet ikke kunne finde de to værdier i den anden tabel, der er tilgængelige i den første tabel array.
Selvfølgelig er dette udtryk for at sammenligne bordindikatorer, det er muligt at anvende i den eksisterende form, men det er muligt at forbedre det.
Vi vil gøre, at de værdier, der findes i anden tabel, men ikke er tilgængelige i den første, blev vist som en separat liste.
- Først og fremmest genbruger vi vores formel, men vi vil gøre det til en af operatørens argumenter, hvis. For at gøre dette fremhæver vi den første celle, hvor operatøren er operatøren. I formlen Strengen foran den tilføjer vi udtrykket "hvis" uden citater og åbner beslaget. Endvidere, så det var lettere for os at arbejde, tildele formlerne i Formel Row og klikke på ikonet "Indsæt funktion".
- Vinduet Funktionsargumenter åbnes, hvis. Som du kan se, er vindues første vindue allerede blevet fyldt med værdiansættelsen af operatørens råd. Men vi skal tilføje noget andet på dette område. Vi sætter der markøren og det allerede eksisterende udtryk Tilføj "= 0" uden citater.
Derefter gå til feltet "Betydning, hvis sandheden". Her vil vi bruge en anden indlejret funktionslinje. Indtast ordet "streng" uden citater, og åbn derefter parentes og angiv koordinaterne for den første celle med efternavnet i den anden tabel, hvorefter vi lukker parentes. Specielt i vores tilfælde i "Betying Hvis TRUE" -feltet, skete følgende udtryk:
Række (D2)
Nu vil operatørstrengen rapportere funktioner, hvis linjenummeret, hvor det specifikke efternavn er placeret, og i det tilfælde, hvor den tilstand, der er angivet i det første felt, udføres, vises funktionen, hvis dette nummer vises. Klik på knappen "OK".
- Som du kan se, vises det første resultat som "løgne". Det betyder, at værdien ikke opfylder operatørens betingelser, hvis. Det vil sige, det første efternavn er til stede i begge lister.
- Brug af påfyldningsmarkøren, som allerede er kendt for at kopiere operatørens udtryk, hvis den er på hele kolonnen. Som vi ser, i to positioner, der er til stede i anden tabel, men ikke i den første, beskriver formlen rækker numre.
- Vi trækker mig tilbage fra bordområdet til højre og udfylder nummerkolonnen i rækkefølge, fra 1. Antallet af numre skal svare til antallet af rækker i det andet sammensatte bord. For at fremskynde nummereringsproceduren kan du også bruge fyldmarkøren.
- Derefter fremhæver vi den første celle til højre for højttaleren med tallene og klik på ikonet "Indsæt funktion".
- Guiden åbnes. Gå til kategorien "Statistisk" og lav navnet på det "mindste" navn. Klik på knappen "OK".
- Funktionen er den mindste, hvis argumenter blev beskrevet, er beregnet til at trække den angivne mindste værdi tilbage.
I feltet "Array" skal du angive koordinaterne for intervallet af den valgfrie kolonne "antallet af kampe", som vi tidligere har forvandlet ved hjælp af funktionen, hvis. Vi gør alle links absolutte.
I feltet "K" er det angivet, som i kontoen den mindste værdi skal vises. Her angiver vi koordinaterne for den første celle i kolonnen med nummereringen, som vi for nylig tilføjede. Adressorlov relativ. Klik på knappen "OK".
- Operatøren viser resultatet - nummer 3. Det er netop det mindste af nummereringen af de inkonsekvente linjer af bord arrays. Ved hjælp af påfyldningsmarkøren skal du kopiere formlen til selve næsen.
- Nu ved at kende antallet af rækkerne af de uforståelige elementer, kan vi indsætte i cellen og deres værdier ved hjælp af indeksfunktionen. Vælg det første element i arket, der indeholder formlen, er den mindste. Derefter skal du gå til linjeformlerne og før navnet på det "mindste" tilføj navnet "indeks" uden citater, straks åbne beslaget og sæt et punkt med et komma (;). Derefter tildeler vi formelnavnet "Indeks" i linjen og klik på ikonet "Indsætningsfunktion".
- Derefter åbnes et lille vindue, hvor det er nødvendigt at afgøre, om referencevisningen skal have en indeksfunktion eller designet til at arbejde med arrays. Vi har brug for en anden mulighed. Den er som standard installeret, så det i dette vindue blot klikker på knappen "OK".
- Argumentvinduet kører indeksfunktionen. Denne operatør er designet til at udgive en værdi, der er placeret i et bestemt array i den angivne linje.
Som du kan se, er "ROW-nummer" allerede fyldt med betydningsværdierne for den mindste funktion. Fra en eksisterende værdi der, forskellen mellem nummereringen af Excel-arkene og internt nummerering af tabelområdet. Som vi ser, har vi kun en hat på bordværdierne. Det betyder, at forskellen er en linje. Derfor tilføj "-1" uden citater i feltet "ROW NUMBER".
I feltet "Array" angive adressen på værdien af den anden tabelværdier. På samme tid gør alle koordinaterne absolut, det vil sige, at vi sætter dollarskiltet før den metode, der er beskrevet af os.
Klik på knappen "OK".
- Efter tilbagetrækning strækker resultatet på skærmen funktionen ved hjælp af fyldmarkøren til slutningen af søjlen ned. Som vi ser, er begge efternavne, der er til stede i anden tabel, men ikke er tilgængelige i det første, fjernes i et særskilt område.
Metode 5: Sammenligning af arrays i forskellige bøger
Når du sammenligner området i forskellige bøger, kan du bruge ovenstående metoder, undtagen de muligheder, hvor placeringen af begge tabelområder på et ark er påkrævet. Hovedbetingelsen for sammenligningsproceduren i dette tilfælde er åbningen af vinduerne i begge filer på samme tid. For versioner af Excel 2013 og senere, såvel som for versioner til Excel 2007, er der ingen problemer med implementeringen af denne tilstand. Men i Excel 2007 og Excel 2010 for at åbne begge vinduer på samme tid kræves der yderligere manipulationer. Sådan gør du det, fortæller i en separat lektion.
Lektion: Sådan åbnes Excel i forskellige vinduer
Som du kan se, er der en række muligheder for at sammenligne tabeller med hinanden. Hvilken slags mulighed afhænger af, hvor borddataene i forhold til hinanden er placeret (på et ark, i forskellige bøger, på forskellige ark), såvel som fra brugeren ønsker, at denne sammenligning vises på skærmen.