Ganska ofta har Excel-användare en uppgift att jämföra två tabeller eller listor för att identifiera skillnader i dem eller saknade föremål. Varje användare klarar av denna uppgift på sin egen väg, men oftast en ganska stor tid spenderas på att lösa den angivna frågan, eftersom inte alla tillvägagångssätt för detta problem är rationella. Samtidigt finns det flera beprövade handlingar algoritmer som tillåter jämförande listor eller bordsarrayer på ganska kort tid med minimal betydande ansträngning. Låt oss överväga detaljer dessa alternativ.
På samma sätt kan du jämföra data i tabellerna som finns på olika ark. Men i det här fallet är det önskvärt att linjerna i dem är numrerade. Resten av jämförelseförfarandet är nästan exakt detsamma som beskrivet ovan, förutom det faktum att när man gör formeln är det nödvändigt att byta mellan ark. I vårt fall kommer uttrycket att ha följande form:
= B2 = lista2! B2
Det vill säga, som vi ser, framför datakoordinaterna, som är belägna på andra ark än där jämförelsesresultatet visas, indikeras arksnumret och ett utropstecken.
Metod 2: Val av grupper av celler
Jämförelse kan göras med hjälp av verktyget för separation av grupper av celler. Med det kan du också jämföra synkroniserade och beställda listor. Dessutom måste listorna i det här fallet vara placerade bredvid varandra på ett ark.
- Välj jämfört arrays. Gå till fliken "Hem". Klicka sedan på ikonen "Sök och välj", som ligger på tejpen i redigeringsverktygsfältet. Det finns en lista där du bör välja position "urval av gruppen av celler ...".
Dessutom kan valet av gruppen av celler nås på ett annat sätt. Det här alternativet kommer särskilt att vara användbart för de användare som har versionen av programmet tidigare än Excel 2007, eftersom metoden via "Sök och Välj" -knappen inte stöder dessa program. Välj Arrays som vill jämföra och klicka på F5-tangenten.
- Ett litet övergångsfönster är aktiverat. Klicka på knappen "Highlight ..." i sitt nedre vänstra hörn.
- Därefter, oavsett två av ovanstående alternativ du har valt, lanseras valfönstret av cellgrupper. Montera omkopplaren till "Markera på rad" -läget. Klicka på "OK" -knappen.
- Som vi kan se, kommer de inkonsekventa värdena på linjerna att markeras med en annan nyans. Dessutom, hur kan man bedömas från innehållet i formellinjen, kommer programmet att göra en aktiv en av cellerna i de angivna icke-sammanföll linjerna.
Metod 3: Villkorlig formatering
Du kan göra en jämförelse genom att använda villkorlig formateringsmetod. Som i föregående metod bör jämförda områden vara på ett enda excel-arbetsblad och synkroniseras med varandra.
- Först och främst väljer vi vad Tabular-regionen kommer att betraktas som huvudet, och i vad man ska leta efter skillnaden. Senast låt oss göra i det andra bordet. Därför fördelar vi en lista över arbetare i den. Fliken Fliken "Hem" klickar du på knappen "Villkorlig formatering", som har en plats på bandet i "Styles" -blocket. Från rullgardinsmenyn, gå igenom objektet "Regler Management".
- Fönstret Rules Manager är aktiverat. Vi klickar på den till knappen "Skapa regel".
- I löpfönstret väljer vi "Använd formel" -läget. I fältet "Formatcell" skriver du en formel som innehåller adresserna till de första cellerna i intervallet jämfört med kolumner, separerade av "Ej lika" tecken (). Strax före detta uttryck kommer den här tiden att stå undertecknet "=". Dessutom måste du till alla kolumnkoordinater i den här formeln tillämpa en absolut adressering. För att göra detta tilldelar vi formeln med markören och klickar på F4-tangenten tre gånger. Som vi kan se uppträdde dollartecknet nära alla kolumnadresser, vilket innebär att du vrider länkarna till Absolut. För vårt speciella fall kommer formeln att ta följande formulär:
= $ A2 $ d2
Vi registrerar detta uttryck i ovanstående fält. Därefter klickar vi på knappen "Format ...".
- Fönstret "Cellformat" är aktiverat. Vi går till fliken "Fyll". Här i listan över färger stoppa valet på färgen, som vi vill måla de element där data inte matchar. Klicka på "OK" -knappen.
- Återgå till fönstret Formatering Regel, klicka på "OK" -knappen.
- Efter automatisk flyttning till fönstret "Regler Manager" klickar du på "OK" -knappen och i den.
- Nu i det andra bordet kommer element som har data som är inkonsekventa med motsvarande värden på det första tabellområdet att markeras i den valda färgen.
Det finns ett annat sätt att tillämpa villkorlig formatering för att utföra uppgiften. Liksom de tidigare alternativen kräver det placeringen av båda jämförbara områdena på ett ark, men i motsats till de tidigare beskrivna metoderna kommer synkroniseringsvillkoren eller sorteringsdata inte att vara obligatoriska, vilket skiljer detta alternativ från tidigare beskrivna.
- Vi producerar valet av områden du behöver jämföra.
- Vi utför övergången till fliken som heter "Home". Vi klickar på knappen "Villkorlig formatering". I den aktiverade listan väljer du positionen "Regler för allokering av celler". I nästa meny gör du positionen "Upprepande värden".
- Dubbla värdena lanseras. Om du är klar korrekt, så är det bara i det här fönstret bara att klicka på "OK" -knappen. Även om du vill, i det lämpliga fältet i det här fönstret, kan du välja en annan färg på valet.
- Efter att vi producerat den angivna åtgärden kommer alla repetitiva element att markeras i den valda färgen. De element som inte sammanfaller kommer att förbli målade i sin ursprungliga färg (som standard vit). Således kan du omedelbart se visuellt för att se vad skillnaden mellan arrays.
Om du vill, kan du tvärtom måla de inkonsekventa elementen och de indikatorer som matchar, lämnar med fyllningen av föregående färg. Samtidigt är algoritmen för åtgärd nästan densamma, men i inställningarna för dubbla värdena i det första fältet, istället för den "upprepade" parametern, välj den "unika" parametern. Därefter klickar du på "OK" -knappen.
Således är det just de indikatorer som inte sammanfaller.
Lektion: Villkorlig formatering i Excel
Metod 4: Omfattande formel
Jämför även data med hjälp av en komplex formel, vars grund är funktionen av mätaren. Med det här verktyget kan du beräkna hur mycket varje element från den valda kolumnen i det andra tabellen upprepas i den första.
Operatörsrådet hänvisar till den statistiska gruppen av funktioner. Dess uppgift är att räkna antalet celler, de värden som uppfyller det angivna tillståndet. Syntaxen för den här operatören har detta slag:
= Scheman (intervall; kriterium)
Argumentet "Range" är adressen till arrayen, som beräknar de sammanfallande värdena.
Argumentet "Kriterier" anger tillfället för tillfället. I vårt fall kommer det att vara koordinaterna för de specifika cellerna i det första tabellområdet.
- Vi fördelar det första elementet i den extra kolumnen där antalet matcher kommer att beräknas. Klicka sedan på ikonen "Infoga funktion".
- Funktionsguiden startar. Gå till kategorin "Statistiska". Hitta i listan namnet "countecles". Efter det val, klicka på "OK" -knappen.
- Fönstret Operatörsargument startade. Som vi ser motsvarar namnen på fälten i det här fönstret namnen på argumenten.
Montera markören i fältet "Range". Efter det, med hjälp av vänster musknapp, allokera alla värdena på kolumnen med namnen på det andra tabellen. Som du kan se faller koordinaterna omedelbart i det angivna fältet. Men för våra ändamål bör denna adress göras av absolut. För att göra detta, markera koordinaterna i fältet och klicka på F4-tangenten.
Som du kan se tog referensen den absoluta formen, som kännetecknas av närvaron av dollarskyltar.
Gå sedan till fältet "Kriterium" genom att installera markören. Klicka på det första elementet med de sista namnen i det första tabellområdet. Låt i det här fallet lämna länken relativ. När det är visat i fältet kan du klicka på "OK" -knappen.
- Resultatet är utmatning till arkelementet. Det är lika med numret "1". Det betyder att i listan över namn på det andra tabellen, uppstår namnet "Grinev V. P.", som är det första i listan över den första bordsuppsättningen, en gång.
- Nu måste vi skapa ett liknande uttryck och för alla andra delar av det första bordet. För att göra detta kommer jag att utföra kopiering, med tankmarkören, som vi redan har gjort tidigare. Vi sätter markören till den nedre högra delen av bladelementet, som innehåller mätarens funktion och efter att ha konvertera den till fyllnadsmarkören, kläm den vänstra musknappen och dra ned markören.
- Som vi kan se har programmet gjort en beräkning av tillfällen, vilket jämför varje cell i det första bordet med data, som finns i det andra tabellintervallet. I fyra fall var resultatet "1", och i två fall - "0". Det vill säga att programmet inte kunde hitta de två värdena i det andra tabellen som finns i den första tabellmatrisen.
Naturligtvis är detta uttryck för att jämföra tabellindikatorer, det är möjligt att ansöka i befintlig form, men det är möjligt att förbättra det.
Vi kommer att göra de värden som finns i det andra tabellen, men är inte tillgängliga i det första, visades som en separat lista.
- Först och främst återvinner vi vår formel, men vi kommer att göra det till en av operatörens argument om. För att göra detta markerar vi den första cellen där operatören är operatören. I formelsträngen framför den lägger vi till uttrycket "Om" utan citat och öppnar konsolen. Vidare, så att det var lättare för oss att arbeta, fördela formlerna i Formel Row och klicka på ikonen "Infoga funktion".
- Fönstret Funktionsargument öppnas om. Som du kan se har det första fönstret i fönstret redan fyllts med värderingen av operatörens råd. Men vi måste lägga till något annat på detta område. Vi satte där markören och det redan befintliga uttrycket lägg till "= 0" utan citat.
Därefter, gå till fältet "mening om sanning". Här kommer vi att använda en annan nestad funktionslinje. Ange ordet "String" utan citat, öppna sedan fästen och ange koordinaterna för den första cellen med efternamnet i det andra bordet, varefter vi stänger parenteser. Specifikt, i vårt fall i fältet "mening om det sanna", hände följande uttryck:
Rad (d2)
Nu kommer operatörssträngen att rapportera om det linjenummer där det specifika efternamnet är beläget och i det fall då det tillstånd som anges i det första fältet kommer att utföras, funktionen om det här numret visas. Klicka på "OK" -knappen.
- Som du kan se visas det första resultatet som "lögner". Det innebär att värdet inte uppfyller operatörens villkor om. Det vill säga det första efternamnet är närvarande i båda listorna.
- Använda fyllnadsmarkören, som redan är bekant att kopiera operatörens uttryck om du är på hela kolumnen. Som vi ser, i två positioner som är närvarande i det andra bordet, men inte i den första, raderar formeln nummer.
- Vi drar tillbaka från bordsområdet till höger och fyll i numreringskolonnen i ordning, från 1. Antalet nummer måste matcha antalet rader i det andra koncemable-tabellen. För att påskynda numreringsproceduren kan du också använda fyllmarkören.
- Därefter markerar vi den första cellen till höger om högtalaren med siffrorna och klickar på ikonen "Infoga funktion".
- Trollkarlen öppnas. Gå till kategorin "Statistisk" och producera namnet på det "minsta" namnet. Klicka på "OK" -knappen.
- Funktionen är den minsta, vars argument avslöjades, är avsett att dra tillbaka det angivna minsta värdet.
I fältet "Array" bör du ange koordinaterna för det valfria kolumnen "antalet matchningar", som vi tidigare omvandlat med hjälp av funktionen om. Vi gör alla länkar absolut.
I fältet "K" anges det som i kontot det minsta värdet ska visas. Här anger vi koordinaterna för kolonnens första cell med numreringen, som vi nyligen har lagt till. Adressen lämnar relativ. Klicka på "OK" -knappen.
- Operatören visar resultatet - nummer 3. Det är just det minsta av numreringen av de inkonsekventa linjerna i tabelluppsättningar. Använd fyllnadsmarkören, kopiera formeln till näsan själv.
- Nu, att veta antalet rader av de obegripliga elementen, kan vi infoga i cellen och deras värden med hjälp av indexfunktionen. Välj det första elementet i arket innehållande formeln är den minsta. Därefter, gå till linjens formler och innan namnet på "minsta" lägg till namnet "index" utan citat, öppna omedelbart fästet och lägg en punkt med ett komma (;). Därefter tilldelar vi Formel Name "-indexet" i raden och klickar på ikonen "Klistra in funktion".
- Därefter öppnas ett litet fönster, där det är nödvändigt att avgöra om referensvyn ska ha en indexfunktion eller utformad för att fungera med arrays. Vi behöver ett andra alternativ. Den är installerad som standard, så att i det här fönstret bara klicka på "OK" -knappen.
- Argumentfönstret kör indexfunktionen. Denna operatör är utformad för att mata ut ett värde som finns i en specifik matris i den angivna raden.
Som du kan se är fältet "radnummer" redan fyllt med betydelsen av den minsta funktionen. Från ett befintligt värde där, skillnaden mellan numreringen av Excel-arket och den interna numreringen av tabellområdet. Som vi ser har vi bara en hatt på bordsvärdena. Det betyder att skillnaden är en linje. Därför, lägg till "-1" utan citat i fältet "radnummer".
I fältet "Array" anger du adressen till värdet av de andra tabellvärdena. Samtidigt gör alla koordinater absolut, det vill säga, vi sätter dollarns tecken innan den metod som beskrivs av oss.
Klicka på "OK" -knappen.
- Efter att ha dragit ut, sträcker resultatet på skärmen funktionen med fyllmarkören till slutet av kolumnen nedåt. Som vi ser avlägsnas båda efternamnen som finns i det andra bordet, men inte tillgängliga i det första, i ett separat område.
Metod 5: Jämförelse av arrays i olika böcker
När du jämför intervallet i olika böcker kan du använda ovanstående metoder, med undantag av de alternativ där placeringen av båda tabulära områden på ett ark krävs. Det huvudsakliga villkoret för jämförelseförfarandet i det här fallet är öppnandet av fönstren i båda filerna samtidigt. För versioner av Excel 2013 och senare, såväl som för versioner till Excel 2007, det finns inga problem med genomförandet av detta tillstånd. Men i Excel 2007 och Excel 2010 för att öppna båda fönstren samtidigt krävs ytterligare manipuleringar. Hur man gör det berättar i en separat lektion.
Lektion: Hur öppnar du Excel i olika fönster
Som du kan se finns det ett antal möjligheter att jämföra borden med varandra. Vilken typ av alternativ att använda beror på var bordsdata i förhållande till varandra är belägen (på ett ark, i olika böcker, på olika ark), såväl som från användaren vill att den här jämförelsen ska visas på skärmen.