amikor Excel-ben dolgozik adatokkal, előbb vagy utóbb össze kell hasonlítania az adatokat. Ez két oszlop vagy akár különböző lapok/munkafüzetek adatainak összehasonlítása lehet.
ebben az Excel oktatóanyagban különböző módszereket mutatok be az Excel két oszlopának összehasonlítására, és egyezések vagy különbségek keresésére.
ennek többféle módja van az Excelben, és ebben az oktatóanyagban bemutatok néhányat ezek közül (például összehasonlítom a VLOOKUP képletet vagy az IF képletet vagy a feltételes formázást).
Tehát kezdjük el!
Tartalomjegyzék
- két oszlop összehasonlítása (egymás mellett)
- hasonlítsa össze egymás mellett az egyenlő jel operátorral
- összehasonlítás egymás mellett az If függvény használatával
- jelölje ki a megfelelő adatokkal (vagy különböző adatokkal) rendelkező sorokat
- hasonlítsa össze a két oszlopot a VLOOKUP használatával (megfelelő/különböző adatok keresése)
- hasonlítson össze két oszlopot a VLOOKUP használatával, és találjon egyezéseket
- hasonlítson össze két oszlopot a VLOOKUP használatával, és keresse meg a különbségeket (hiányzó adatpontok)
- gyakori lekérdezések két oszlop összehasonlításakor
két oszlop összehasonlítása (egymás mellett)
ez az összehasonlítás legalapvetőbb típusa, ahol össze kell hasonlítania egy oszlop celláját egy másik oszlop azonos sorának cellájával.
tegyük fel, hogy az alább látható adatkészlettel rendelkezik, és egyszerűen ellenőrizni szeretné, hogy egy adott cellában az A oszlopban szereplő érték megegyezik-e (vagy különbözik-e) a szomszédos cellában lévő értékkel összehasonlítva.
természetesen ezt megteheti, ha van egy kis adatkészlet ha van egy nagy, használhat egy egyszerű összehasonlító képletet, hogy ezt elvégezze. Ne feledje, hogy mindig van esély az emberi hibára, ha ezt manuálisan teszi.
Tehát hadd mutassak meg néhány egyszerű módszert erre.
hasonlítsa össze egymás mellett az egyenlő jel operátorral
tegyük fel, hogy rendelkezik az alábbi adatkészlettel, és tudni szeretné, hogy mely sorok tartalmazzák az egyező adatokat, és mely sorok eltérő adatokkal rendelkeznek.
az alábbiakban egy egyszerű képlet két oszlop összehasonlítására (egymás mellett):
=A2=B2
a fenti képlet igaz értéket ad, ha mind az értékek megegyeznek,mind hamisak, ha nem.
most, ha tudni szeretné az összes megfelelő értéket, egyszerűen alkalmazzon egy szűrőt, és csak az összes valós értéket jelenítse meg. Ha pedig meg szeretné tudni az összes eltérő értéket, szűrje le az összes HAMIS értéket (az alábbiak szerint):
ha ezt a módszert használja az oszlopok összehasonlítására az excelben, akkor mindig a legjobb, ha ellenőrzi, hogy az adatok nem tartalmaznak-e vezető vagy záró szóközöket. Ha ezek jelen vannak, annak ellenére, hogy ugyanaz az érték, az Excel másként jeleníti meg őket. Itt van egy nagyszerű útmutató arról, hogyan lehet eltávolítani a vezető és záró szóközöket az Excelben.
összehasonlítás egymás mellett az If függvény használatával
két oszlop összehasonlításához másik módszer lehet az IF függvény használata.
ez hasonló a fenti módszerhez, ahol az egyenlő ( = ) operátort használtuk, egy további előnnyel. Az IF függvény használatakor kiválaszthatja azt az értéket, amelyet meg szeretne kapni, ha egyezések vagy különbségek vannak.
például, ha van egyezés, megkaphatja a “Match” szöveget, vagy kaphat olyan értéket, mint 1. Hasonlóképpen, ha eltérés van, beprogramozhatja a képletet úgy, hogy megadja a “Mismatch” szöveget, vagy 0 vagy üres cellát adjon.
az alábbiakban látható az IF képlet, amely az ‘egyezést’ adja vissza, ha a két cellának van cellaértéke, és a ‘nem egyezést’, ha az érték eltér.
=IF(A2=B2,"Match","Not a Match")
a fenti képlet ugyanazt a feltételt használja annak ellenőrzésére, hogy a két cellának (ugyanabban a sorban) vannak-e megfelelő adatai (A2=B2). De mivel az IF függvényt használjuk, megkérhetjük, hogy adjon vissza egy adott szöveget, ha a feltétel igaz vagy hamis.
miután a képlet eredménye külön oszlopban van, gyorsan szűrheti az adatokat, és megkaphatja azokat a sorokat, amelyek az egyező adatokat tartalmazzák, vagy a nem egyező adatokat tartalmazó sorokat.
jelölje ki a megfelelő adatokkal (vagy különböző adatokkal) rendelkező sorokat
a megfelelő adatokkal (vagy eltérő adatokkal) rendelkező sorok gyors ellenőrzésének másik nagyszerű módja ezeknek a soroknak a feltételes formázással történő kiemelése.
mindkettőt megteheti – jelölje ki azokat a sorokat, amelyeknek ugyanaz az értéke egy sorban, valamint azt az esetet, amikor az érték eltérő.
tegyük fel, hogy van egy adatkészlet az alábbiak szerint, és ki szeretné emelni az összes sort, ahol a név megegyezik.
az alábbiakban bemutatjuk a feltételes formázás használatának lépéseit a megfelelő adatokkal rendelkező sorok kiemeléséhez:
- válassza ki a teljes adatkészletet (kivéve a fejléceket)
- kattintson a Kezdőlap fülre
- a stílusok csoportban kattintson a Feltételes formázás
- a megjelenő opciókban kattintson az ‘új szabály ‘elemre’
- az “Új formázási szabály” párbeszédpanelen kattintson a – “képlet segítségével határozza meg, hogy mely cellákat formázza’
- a’ Format values where this formula is true ‘ mezőbe írja be a képletet: =$A2=$B2
- kattintson a Formázás gombra
- kattintson a ‘kitöltés’ fülre, és válassza ki azt a színt, amelyben mindkét oszlopban azonos értékű sorokat szeretne kiemelni
- kattintson az OK gombra
a fenti lépések azonnal kiemelnék azokat a sorokat, ahol a név a ugyanaz mind az a, mind a B oszlopban (ugyanabban a sorban). Abban az esetben, ha a név más, ezek a sorok nem kerülnek kiemelésre.
ha két oszlopot szeretne összehasonlítani, és olyan sorokat szeretne kiemelni, ahol a nevek különböznek, használja az alábbi képletet a feltételes formázás párbeszédpanelen (a 6.lépésben).
=$A2<>$B2
hogyan működik ez?
ha feltételes formázást használunk képlettel, akkor csak azokat a cellákat emeli ki, ahol a képlet igaz.
ha $a2=$B2 értéket használunk, akkor minden cellát (mindkét oszlopban) ellenőrizni fog, és megnézi, hogy az A oszlop sorának értéke megegyezik-e a B oszlop értékével.
abban az esetben, ha pontosan egyezik, akkor a megadott színnel kiemeli, ha nem egyezik, akkor nem.
a feltételes formázás legjobb része az, hogy nem igényel képletet külön oszlopban. Továbbá, ha a szabályt egy adatkészletre alkalmazza, az dinamikus marad. Ez azt jelenti, hogy ha bármilyen nevet megváltoztat az adatkészletben, a feltételes formázás ennek megfelelően módosul.
hasonlítsa össze a két oszlopot a VLOOKUP használatával (megfelelő/különböző adatok keresése)
a fenti példákban megmutattam, hogyan lehet összehasonlítani két oszlopot (vagy listát), amikor csak egymás melletti cellákat hasonlítunk össze.
a valóságban ez ritkán lesz így.
a legtöbb esetben két oszlop lesz adatokkal, és meg kell tudnia, hogy az egyik oszlopban található adatpont létezik-e a másik oszlopban.
ilyen esetekben nem használhat egyszerű egyenlő-jelet vagy akár egy IF függvényt.
valami erősebbre van szükséged …
… valami, ami a VLOOKUP sikátorában van!
hadd mutassak két példát, ahol összehasonlítjuk az Excel két oszlopát a VLOOKUP függvény segítségével az egyezések és különbségek megtalálásához.
hasonlítson össze két oszlopot a VLOOKUP használatával, és találjon egyezéseket
tegyük fel, hogy van egy adatkészletünk az alábbiak szerint, ahol van néhány név az A és B oszlopban.
ha meg kell tudnia, hogy melyek azok a nevek, amelyek a B oszlopban vannak, amelyek szintén az A oszlopban vannak, használhatja az alábbi VLOOKUP képletet:
=IFERROR(VLOOKUP(B2,$A:$A,1,0),"No Match")
a fenti képlet összehasonlítja a két oszlopot (A és B), és megadja a nevet, ha a név a B oszlopban is szerepel, és visszaadja a “No Match” értéket, ha a név A B oszlopban van, és nem az A oszlopban.
alapértelmezés szerint a VLOOKUP függvény #n/a hibát ad vissza, ha nem talál pontos egyezést. Tehát a hiba elkerülése érdekében becsomagoltam a VLOOKUP függvényt az IFERROR függvénybe, így “nem egyezik”, ha a név nem érhető el az A oszlopban.
fordítva is megteheti összehasonlítás-annak ellenőrzésére, hogy a név szerepel-e az A oszlopban, valamint a B oszlopban. az alábbi képlet ezt tenné:
=IFERROR(VLOOKUP(A2,$B:$B,1,0),"No Match")
hasonlítson össze két oszlopot a VLOOKUP használatával, és keresse meg a különbségeket (hiányzó adatpontok)
míg a fenti példában ellenőriztük, hogy az egyik oszlopban lévő adatok egy másik oszlopban vannak-e vagy sem.
ugyanazt a koncepciót használhatja két oszlop összehasonlítására a VLOOKUP függvény segítségével, és megtalálhatja a hiányzó adatokat.
tegyük fel, hogy van egy adatkészletünk az alábbiak szerint, ahol van néhány név az A és B oszlopban.
ha meg kell találnia, hogy melyek azok a nevek, amelyek a B oszlopban vannak, amelyek nincsenek az A oszlopban, használhatja az alábbi VLOOKUP képletet:
=IF(ISERROR(VLOOKUP(B2,$A:$A,1,0)),"Not Available","Available")
a fenti képlet ellenőrzi a B oszlopban szereplő nevet az A oszlopban szereplő összes névvel. Ha talál egy pontos egyezést, akkor visszaadja ezt a nevet, és ha nem talál pontos egyezést, akkor visszaadja a #N/a hibát.
mivel érdekel a hiányzó nevek megtalálása, amelyek a B oszlopban vannak, nem pedig az A oszlopban, tudnom kell azokat a neveket, amelyek visszaadják a #N/a hibát.
ezért becsomagoltam a VLOOKUP függvényt az IF és ISERROR függvényekbe. Ez a teljes képlet adja meg az értéket – “nem elérhető”, ha a név hiányzik az A oszlopban, és “elérhető”, ha jelen van.
az összes hiányzó név megismeréséhez szűrheti az eredmény oszlopot a “nem elérhető” érték alapján.
használhatja az alábbi egyezés funkciót is, hogy ugyanazt az eredményt kapja:
=IF(ISNUMBER(MATCH(B2,$A:$A,0)),"Available","Not Available")
gyakori lekérdezések két oszlop összehasonlításakor
az alábbiakban bemutatunk néhány gyakori lekérdezést, amelyeket általában akkor kapok, amikor az emberek két oszlopban próbálják összehasonlítani az adatokat az Excel programban.
Q1. Hogyan lehet összehasonlítani több oszlopot az Excel-ben ugyanabban a sorban a mérkőzésekhez? Számolja meg a teljes másolatokat is.
év. Megadtuk az eljárást az excel két oszlopának összehasonlítására a fenti sorhoz. De ha több oszlopot szeretne összehasonlítani az Excelben ugyanabban a sorban, akkor lásd a példát
=IF(AND(A2=B2, A2=C2),"Full Match", "")
itt összehasonlítottuk az A oszlop, a B oszlop és a C oszlop adatait.
most, hogy megszámolja a másolatokat, használnia kell a Countif funkciót.
=IF(COUNTIF($A2:$E2, $A2)=5, "Full Match", "")
Q2. Melyik operátort használja a mérkőzésekhez és a különbségekhez?
év. Az alábbiakban a használandó operátorok találhatók:
- egyezések kereséséhez használja az egyenlőségjelet (=)
- különbségek (eltérések) kereséséhez használja a nem egyenlőt (<>)
3. kérdés. Hogyan lehet összehasonlítani két különböző táblázatot és lekérni az egyező adatokat?
év. Ehhez használhatja a VLOOKUP funkciót vagy az INDEX & MATCH funkciót. Hogy jobban megértsük ezt a dolgot, példát fogunk venni.
itt két táblázatot fogunk venni, és most meg akarjuk csinálni a megfelelő adatokat. Az első táblázatban van egy adatkészlet, a második táblázatban pedig vegye fel a gyümölcsök listáját, majd használja a pull matching adatokat egy másik oszlopban. A húzásillesztéshez használja a képletet
=INDEX($B:$B,MATCH($D2,$A:$A,0))
4. kérdés. Hogyan lehet eltávolítani a másolatokat Excel – ben?
év. Az ismétlődő adatok eltávolításához először meg kell találnia az ismétlődő értékeket.
a másolat megkereséséhez különböző módszereket használhat, mint például feltételes formázás, Vlookup, If utasítás és még sok más. Az Excel beépített eszközzel is rendelkezik, ahol csak kiválaszthatja az adatokat, és eltávolíthatja a másolatokat egy oszlopból vagy akár több oszlopból
Q5. Látom, hogy mindkét oszlopban van egyező érték. A fent megosztott képletek azonban ezeket nem tekintik pontos egyezésnek. Miért?
Ans: az Excel akkor tekint valamit pontos egyezésnek, ha az egyik cella minden karaktere megegyezik a másikkal. Nagy esély van arra, hogy az adatkészletben vannak vezető vagy záró terek.
bár ezek a szóközök még mindig szabad szemmel egyenlőnek tűnhetnek, az Excel esetében ezek eltérőek. Ha van ilyen adatkészlet, akkor a legjobb, ha megszabadul ezektől a terektől (ehhez használhat olyan Excel funkciókat, mint a TRIM).
Q7. Hogyan lehet összehasonlítani két oszlopot, amelyek az eredményt igaznak adják, ha az első oszlopok egész értékei nem kisebbek, mint a második oszlop egész értékei. A probléma megoldásához nincs szükség feltételes formázásra, Vlookup függvényre, If utasításra és más képletekre. Szükségem van a képletre, hogy megoldjam ezt a problémát.
év. A probléma megoldásához használhatja a tömb képletet.
a szintaxis {=és (H6:H12>I6:I12)}. Ennek eredményeként “igaz” lesz, amikor a H oszlop értéke nagyobb, mint az I oszlop értéke, különben a “hamis” lesz az eredmény.
a következő Excel oktatóanyagok is tetszhetnek:
- két oszlop összehasonlítása az Excel-ben (egyezésekhez és különbségekhez)
- hogyan lehet elrejteni az oszlopokat az Excel Cellaértéke alapján
- hogyan lehet egy oszlopot több oszlopra osztani az Excel-ben
- hogyan lehet alternatív oszlopokat kiválasztani az Excel-ben (vagy minden n-edik oszlopban)
- hogyan lehet beilleszteni egy szűrt oszlopba a rejtett cellák kihagyásával
- a legjobb Excel könyvek (hogy fog neked egy Excel Pro 2020)
hirdetés jelentése 