Ennek a cikknek az elolvasása körülbelül 10 percet vesz igénybe. A következő 5 percben könnyedén összehasonlíthat két oszlopot az Excelben, és megtudhatja, hogy vannak-e ismétlődések, törölheti vagy színnel kiemelheti. Tehát eljött az idő!

Az Excel egy nagyon hatékony és nagyon klassz alkalmazás nagy mennyiségű adat létrehozására és kezelésére. Ha több adatot tartalmazó munkafüzete (vagy csak egy hatalmas táblázat) van, akkor valószínűleg két oszlopot szeretne összehasonlítani, megkeresni az ismétlődő értékeket, majd végezni velük valamilyen műveletet, például törölni, kiemelni vagy törölni a tartalmat. ... Az oszlopok lehetnek ugyanabban a táblázatban, lehetnek szomszédosak vagy nem szomszédosak, elhelyezhetők 2 különböző lapon vagy akár különböző könyvekben is.

Képzeld el, hogy van 2 oszlopban az emberek neve – 5 név egy oszlopban Aés 3 név egy oszlopban B... Össze kell hasonlítani a két oszlopban található neveket, és ismétlődőket kell keresni. Amint Ön is tudja, ezek fiktív adatok, kizárólag példaként. Valódi táblázatokban több ezer, ha nem több tízezer rekorddal van dolgunk.

A lehetőség: mindkét oszlop ugyanazon a lapon van. Például az oszlop Aés oszlop B.

B lehetőség: Az oszlopok különböző lapokon helyezkednek el. Például az oszlop A a lapon 2. lapés oszlop A a lapon 3. lap.

Az Excel 2013, 2010 és 2007 beépített eszközzel rendelkezik Az ismétlődés eltávolítása(Duplikátumok eltávolítása), de ilyen helyzetben tehetetlen, mivel nem tudja 2 oszlopban összehasonlítani az adatokat. Sőt, csak a duplikációkat tudja eltávolítani. Nincs más lehetőség, mint például a kiemelés vagy a színek megváltoztatása. És a lényeg!

Hasonlítson össze két oszlopot az Excelben, és keresse meg az ismétlődő rekordokat képletek segítségével

A lehetőség: mindkét oszlop ugyanazon a lapon található

Gyors: A nagy táblázatokban a képlet másolása gyorsabb lesz, ha billentyűparancsokat használ. Válasszon ki egy cellát C1és nyomja meg Ctrl + C(a képlet vágólapra másolásához), majd nyomja meg a gombot Ctrl + Shift + End(az összes nem üres cella kijelöléséhez a C oszlopban), és végül nyomja meg a gombot Ctrl+V(a képlet beszúrásához az összes kijelölt cellába).

B lehetőség: két oszlop különböző lapokon van (különböző könyvekben)

Talált ismétlődések feldolgozása

Remek, az első oszlopban olyan bejegyzéseket találtunk, amelyek a második oszlopban is jelen vannak. Most tennünk kell velük valamit. A tábla összes ismétlődő rekordjának manuális megtekintése meglehetősen nem hatékony és időigényes. Vannak jobb módszerek is.

Csak ismétlődő sorok jelenjenek meg az A oszlopban

Ha az oszlopoknak nincs fejléce, akkor fel kell vennie őket. Ehhez helyezze a kurzort az első sort jelölő számra, és az fekete nyíllá változik, ahogy az alábbi ábrán látható:

Kattintson a jobb gombbal, és válassza ki Beszúrás(Beszúrás):

Adjon oszlopneveket, például: " Név"és" Másolat?"Akkor nyissa meg a lapot Adat(Adat) és nyomja meg Szűrő(Szűrő):

Ezután kattintson a kis szürke nyílra a „ Másolat?„A szűrőmenü bővítéséhez; törölje az összes elem jelölését a listában, kivéve Másolat, és nyomja meg rendben.

Ez minden, most csak az oszlop azon elemei láthatók A amelyek ismétlődnek az oszlopban V... Tanulmányi táblázatunkban csak két ilyen cella található, de amint érti, a gyakorlatban sokkal több van belőlük.

Egy oszlop összes sorának ismételt megjelenítéséhez A, kattintson a szűrő szimbólumra az oszlopban V amely most úgy néz ki, mint egy tölcsér egy kis nyíllal és válassza ki Mindet kiválaszt(Mindet kiválaszt). Alternatív megoldásként ugyanezt megteheti a hírfolyamon keresztül is, ha rákattint Adat(Adatok)> Kijelölés és szűrés(Rendezés és szűrés)> Egyértelmű(Törlés) az alábbi képernyőképen látható módon:

Módosítsa a színt, vagy jelölje ki a talált ismétlődéseket

Ha a jelek „ Másolat"Nem elég a céljaidhoz, és az ismétlődő cellákat más betűszínnel, kitöltőszínnel vagy más módon szeretnéd megjelölni...

Ebben az esetben szűrje ki a duplikációkat a fent látható módon, jelölje ki az összes szűrt cellát, és kattintson a gombra Ctrl+1 a párbeszédpanel megnyitásához Cellák formázása(Cellaformátum). Példaként változtassuk meg az ismétlődő sorok celláinak kitöltési színét élénksárgára. Természetesen az eszközzel megváltoztathatja a kitöltési színt Tölt(Kitöltés színe) a fülön itthon(Otthon), hanem a párbeszédpanel előnye Cellák formázása(Cellaformátum) az összes formázási beállítást egyszerre konfigurálhatja.

Most már biztosan nem fog hiányozni egyetlen cella sem a másolatokkal:

Az ismétlődő értékek eltávolítása az első oszlopból

Szűrje le a táblázatot úgy, hogy csak az ismétlődő értékkel rendelkező cellák jelenjenek meg, és jelölje ki azokat.

Ha a két összehasonlított oszlop különböző lapokon van, azaz a különböző táblázatokban kattintson a jobb gombbal a kiválasztott tartományra, és válassza ki Sor törlése(Sor törlése):

Kattintson rendben amikor az Excel megerősítést kér, hogy valóban törölni szeretné-e a munkalap teljes sorát, majd törölje a szűrőt. Amint láthatja, már csak egyedi értékkel rendelkező sorok vannak hátra:

Ha 2 oszlop van ugyanazon a lapon, közel egymáshoz (szomszédos) vagy nem közel egymáshoz (nem szomszédos), akkor a duplikátumok eltávolításának folyamata kissé nehezebb lesz. A duplikált értékeket tartalmazó teljes sort nem tudjuk törölni, mivel ezzel a második oszlop cellái is törlődnek. Tehát csak egyedi bejegyzések maradjanak az oszlopban A, csináld ezt:

Amint láthatja, az ismétlődések eltávolítása két oszlopból az Excelben képletekkel nem olyan nehéz.

Az Excel-felhasználók gyakran szembesülnek azzal a feladattal, hogy összehasonlítsanak két táblázatot vagy listát, hogy azonosítsák a különbségeket vagy a hiányzó elemeket. Minden felhasználó a maga módján megbirkózik ezzel a feladattal, de leggyakrabban meglehetősen sok időt töltenek a probléma megoldásával, mivel nem minden megközelítés racionális. Ugyanakkor számos bevált algoritmus létezik olyan műveletekre, amelyek lehetővé teszik a listák vagy táblázattömbök viszonylag rövid időn belüli, minimális erőfeszítéssel történő összehasonlítását. Nézzük meg közelebbről ezeket a lehetőségeket.

Számos módja van a táblázatterületek összehasonlításának az Excelben, de mindegyik három nagy csoportra osztható:

ugyanazon a lapon található listák összehasonlítása;

a különböző lapokon elhelyezett táblázatok összehasonlítása;

táblázat tartományok összehasonlítása különböző fájlokban.

Ezen osztályozás alapján mindenekelőtt az összehasonlítási módszereket választják ki, és meghatározzák a feladat végrehajtásához szükséges konkrét műveleteket és algoritmusokat. Például, amikor különböző munkafüzetekben végez összehasonlítást, egyszerre két Excel-fájlt kell megnyitnia.

Ezenkívül azt kell mondani, hogy a táblaterületek összehasonlításának csak akkor van értelme, ha hasonló szerkezetűek.

1. módszer: egyszerű képlet

A két táblázat adatainak összehasonlításának legegyszerűbb módja egy egyszerű egyenlőségi képlet. Ha az adatok egyeznek, akkor az IGAZ jelzőt adja vissza, ha pedig nem, akkor - FALSE. Összehasonlíthatja a számadatokat és a szöveget is. Ennek a módszernek az a hátránya, hogy csak akkor használható, ha a táblázatban az adatok azonos sorrendben vagy rendezettségben vannak, szinkronizáltak és azonos sorszámúak. Nézzük meg, hogyan lehet ezt a módszert a gyakorlatban alkalmazni egy lapon elhelyezett két táblázat példáján.

Tehát van két egyszerű táblázatunk a vállalkozás alkalmazottainak listáival és fizetésükkel. Össze kell hasonlítani az alkalmazottak listáját, és azonosítani kell a következetlenségeket a neveket tartalmazó oszlopok között.

1. Ehhez szükségünk van egy további oszlopra a lapon. Ott beírunk egy táblát «=» ... Ezután az első lista első összehasonlítandó elemére kattintunk. Helyezze vissza a szimbólumot «=» a billentyűzetről. Ezután kattintson a második táblázatban az összehasonlított oszlop első cellájára. Az eredmény a következő típusú kifejezés:

   

   Bár természetesen minden konkrét esetben eltérőek lesznek a koordináták, de a lényeg ugyanaz marad.

2. Kattintson a kulcsra Belép hogy megkapjuk az összehasonlítási eredményeket. Mint látható, mindkét lista első cellájának összehasonlításakor a program jelezte a jelzőt "IGAZ", ami adategyezést jelent.



3. Most hasonló műveletet kell végrehajtanunk mindkét táblázat többi cellájával az összehasonlított oszlopokban. De egyszerűen másolhatja a képletet, ami jelentősen időt takarít meg. Ez a tényező különösen fontos a nagy számú karakterláncot tartalmazó listák összehasonlításakor.

   A másolási eljárást a legegyszerűbb egy kitöltő fogantyúval végrehajtani. Vigye a kurzort a cella jobb alsó sarkára, ahol az indikátort kaptuk "IGAZ"... Ebben az esetben fekete keresztté kell alakítani. Ez a kitöltési jelző. Nyomja meg a bal egérgombot, és húzza le a kurzort az összehasonlított táblázattömbök sorainak számához.



4. Amint láthatja, most a további oszlop a táblázattömbök két oszlopában lévő adatok összehasonlításának összes eredményét jeleníti meg. Esetünkben csak egy sorban nem egyeztek az adatok. Összehasonlításukkor a képlet adta meg az eredményt "FEKVŐ"... Az összes többi sor esetében, amint látható, az összehasonlítási képlet adta a mutatót "IGAZ".



5. Ezenkívül egy speciális képlet segítségével ki lehet számítani az eltérések számát. Ehhez válassza ki a lap azon elemét, ahol megjelenik. Ezután kattintson az ikonra "Funkció beszúrása".



6. Az ablakban Funkcióvarázslók az operátorok csoportjában "Matematikai" jelölje ki a nevet SZUMTERMÉK... Kattintson a gombra "RENDBEN".



7. A függvényargumentumok ablaka aktiválva van SZUMTERMÉK, melynek fő feladata a kiválasztott tartomány szorzatainak összegének kiszámítása. De ez a funkció a mi céljainkra is használható. A szintaxisa meglehetősen egyszerű:

   SUMPRODUCT (tömb1; tömb2; ...)

   Összesen legfeljebb 255 tömb címe használható argumentumként. De a mi esetünkben csak két tömböt fogunk használni, ráadásul egy argumentumként.

   A kurzort a mezőbe helyezzük "tömb1"és válassza ki a lapon az összehasonlított adattartományt az első területen. Ezt követően a terepen kihelyezzük a táblát "nem egyenlő" (<> ), és válassza ki a második régió összehasonlított tartományát. Ezután a kapott kifejezést zárójelbe csomagoljuk, amely elé két jelet teszünk «-» ... Esetünkben a következő kifejezést kaptuk:

   - (A2: A7<>D2: D7)

   Kattintson a gombra "RENDBEN".



8. Az operátor kiszámítja és megjeleníti az eredményt. Mint látható, esetünkben az eredmény megegyezik a számmal "1" vagyis ez azt jelenti, hogy egy eltérést találtunk az összehasonlított listákban. Ha a listák teljesen azonosak lennének, akkor az eredmény egyenlő lenne a számmal «0» .

Ugyanígy összehasonlíthatja a különböző lapokon található táblázatok adatait. De ebben az esetben kívánatos, hogy a bennük lévő sorok számozottak legyenek. Egyébként az összehasonlítási eljárás szinte teljesen megegyezik a fent leírtakkal, kivéve azt a tényt, hogy képlet beírásakor váltani kell a lapok között. A mi esetünkben a kifejezés így fog kinézni:

B2 = Lap2! B2

Vagyis, amint látható, a más lapokon található adatok koordinátái előtt, az összehasonlítás eredményétől eltérően, a lapszám és egy felkiáltójel látható.

2. módszer: válasszon cellacsoportokat

Az összehasonlítást a cellacsoport-kiválasztó eszközzel lehet elvégezni. Azt is lehetővé teszi, hogy csak a szinkronizált és rendezett listákat hasonlítsa össze. Ezenkívül ebben az esetben a listáknak egymás mellett kell elhelyezkedniük ugyanazon a lapon.

3. módszer: feltételes formázás

Az összehasonlításokat a feltételes formázási módszerrel lehet elvégezni. Az előző módszerhez hasonlóan az összehasonlított területeknek ugyanazon az Excel munkalapon kell lenniük, és egymással szinkronizálva kell lenniük.

Van egy másik módja a feltételes formázás használatának a feladat végrehajtásához. Az előző opciókhoz hasonlóan ez is megköveteli mindkét összehasonlított terület elhelyezését ugyanazon a lapon, de a korábban ismertetett módszerekkel ellentétben nem lesz szükség az adatszinkronizálás vagy rendezés feltételére, ami kedvezően különbözteti meg ezt a lehetőséget a korábban leírtaktól.

Ha kívánja, ellenkezőleg, színezheti az össze nem illő elemeket, és a kitöltésnek megfelelő mutatókat ugyanazon a színűen hagyhatja. Ebben az esetben a műveletek algoritmusa gyakorlatilag ugyanaz, de a beállítások ablakban az ismétlődő értékek kiemelésére az első mezőben a paraméter helyett "Ismétlődő" a paramétert ki kell választani "Egyedi"... Ezt követően kattintson a gombra "RENDBEN".

Így kiemelésre kerülnek azok a mutatók, amelyek nem esnek egybe.

4. módszer: összetett képlet

Összehasonlíthatja az adatokat a függvényen alapuló összetett képlet segítségével is COUNTIF... Ezzel az eszközzel megszámolhatja, hogy a második táblázat kiválasztott oszlopából hány elem ismétlődik az elsőben.

Operátor COUNTIF a függvények statisztikai csoportjába tartozik. Feladata, hogy megszámolja azon cellák számát, amelyekben az értékek megfelelnek egy adott feltételnek. Ennek az operátornak a szintaxisa a következő:

COUNTIF (tartomány, kritérium)

Érv "Hatótávolság" annak a tömbnek a címe, ahol az egyező értékeket számolják.

Érv "Kritérium" egyezési feltételt ad meg. Esetünkben az első táblázatterület adott celláinak koordinátáit fogja képviselni.

Természetesen a táblázatos mutatók összehasonlításához ez a kifejezés a meglévő formájában alkalmazható, de van lehetőség a javítására.

Tegyük úgy, hogy azok az értékek, amelyek a második táblázatban vannak, de az elsőben nem, külön listában jelenjenek meg.

1. Először is nézzük át egy kicsit a képletünket. COUNTIF, nevezetesen az operátor egyik argumentumává tesszük HA... Ehhez válassza ki az első cellát, amelyben az operátor található COUNTIF... A képletsávban előtte adja hozzá a kifejezést "HA" idézőjelek nélkül, és nyissa ki a zárójelet. Továbbá, hogy megkönnyítsük a munkát, válassza ki az értéket a képletsorban "HA"és kattintson az ikonra "Funkció beszúrása".



2. Megnyitja a függvény argumentumai ablakot HA... Mint látható, az ablak első mezője már tele van az operátor értékével COUNTIF... De ehhez a területhez még valamit hozzá kell tennünk. Oda állítjuk a kurzort, és hozzáadjuk a már meglévő kifejezéshez «=0» idézőjelek nélkül.

   Utána menj a mezőre "Érték, ha igaz"... Itt még egy beágyazott függvényt fogunk használni - VONAL... Beírjuk a szót "VONAL" idézőjelek nélkül, majd nyissa meg a zárójeleket, és adja meg a második táblázatban az első cella koordinátáit a vezetéknévvel, majd zárja be a zárójeleket. Konkrétan a mi esetünkben terepen "Érték, ha igaz" a következő kifejezést kapta:

   SOR (D2)

   Most az operátor VONAL kommunikálni fogja a funkciókat HA annak a sornak a számát, amelyben az adott vezetéknév található, és abban az esetben, ha az első mezőben megadott feltétel teljesül, a függvény HA ezt a számot adja ki a cellába. Kattintson a gombra "RENDBEN".



3. Amint láthatja, az első eredmény így jelenik meg "FEKVŐ"... Ez azt jelenti, hogy az érték nem felel meg az operátor feltételeinek HA... Vagyis az első vezetéknév mindkét listában szerepel.



4. A kitöltési marker segítségével másolja ki az operátor kifejezést a szokásos módon HA az egész oszlopot. Mint látható, két olyan pozícióhoz, amelyek a második táblázatban szerepelnek, de az elsőben nem, a képlet sorszámokat ad.



5. Visszalépünk a táblázat területéről jobbra, és az oszlopot sorrendben számokkal töltjük ki, kezdve 1 ... A számok számának meg kell egyeznie a második összehasonlító táblázatban szereplő sorok számával. A számozási folyamat felgyorsításához használhatja a kitöltési jelölőt is.



6. Ezután jelölje ki a számokkal ellátott oszlop jobb oldalán lévő első cellát, és kattintson az ikonra "Funkció beszúrása".



7. Megnyílik Funkcióvarázsló... Menjen a kategóriához "Statisztikai"és válassza ki a nevet "LEGKEVÉSBÉ"... Kattintson a gombra "RENDBEN".



8. Funkció LEGKEVÉSBÉ, amelynek argumentumai ablaka megnyílt, a számlálás által meghatározott legalacsonyabb értéket kívánja megjeleníteni.

   A terepen "Sor" meg kell adnia a kiegészítő oszlop tartományának koordinátáit "A véletlenek száma" amelyet korábban a függvény segítségével transzformáltunk HA... Minden linket abszolútvá teszünk.

   A terepen "K" a legkisebb megjelenítendő értéket jelzi. Itt az oszlop első cellájának koordinátáit jelöljük számozással, amit nemrég adtunk hozzá. Hagyja meg a cím rokonát. Kattintson a gombra "RENDBEN".



9. Az operátor kiadja az eredményt - egy számot 3 ... Ez a legkisebb számozása a táblázattömbök össze nem illő sorainak. A kitöltő fogantyú segítségével másolja le a képletet a legaljára.



10. Most a nem egyező elemek sorszámának ismeretében a függvény segítségével beszúrhatjuk a cellába és azok értékeit INDEX... Válassza ki a képletet tartalmazó első lapelemet LEGKEVÉSBÉ... Ezután lépjen a képletsorra és a név elé "LEGKEVÉSBÉ" add hozzá a nevet "INDEX" idézőjelek nélkül azonnal nyissa ki a zárójelet, és tegyen egy pontosvesszőt ( ; ). Ezután válassza ki a nevet a képletsorban "INDEX"és kattintson az ikonra "Funkció beszúrása".



11. Ezt követően megnyílik egy kis ablak, amelyben meg kell határoznia, hogy a függvénynek legyen-e hivatkozási űrlapja INDEX vagy tömbökkel való együttműködésre tervezték. Szükségünk van egy második lehetőségre. Alapértelmezés szerint telepítve van, ezért ebben az ablakban egyszerűen kattintson a gombra "RENDBEN".



12. Elindul a függvényargumentumok ablaka INDEX... Ez az operátor azt az értéket hivatott megjeleníteni, amely a megadott sorban egy adott tömbben található.

    Amint látja, a mező "Vonalszám" már tele van függvényértékekkel LEGKEVÉSBÉ... Az Excel lap számozása és a táblázatterület belső számozása közötti különbséget le kell vonni az ott már meglévő értékből. Amint látja, csak egy fejléc van a táblázat értékei felett. Ez azt jelenti, hogy a különbség egy sor. Ezért adjuk hozzá a területen "Vonalszám" jelentése "-1" idézőjelek nélkül.

    A terepen "Sor" adja meg a második táblázat értéktartományának címét. Ilyenkor minden koordinátát abszolutizálunk, vagyis a már leírt módon dollárjelet teszünk elé.

    Kattintson a gombra "RENDBEN".



13. Az eredmény képernyőn történő megjelenítése után a funkciót a kitöltési jelölő segítségével az oszlop végére lefelé kiterjeszteni. Mint látható, mindkét vezetéknév, amely a második táblázatban szerepel, de az elsőben hiányzik, külön tartományban jelenik meg.

5. módszer: a különböző könyvekben található tömbök összehasonlítása

A különböző munkafüzetekben lévő tartományok összehasonlításakor használhatja a fent felsorolt ​​módszereket, kivéve azokat a beállításokat, amelyeknél mindkét táblaterületet ugyanazon a lapon kell elhelyezni. Az összehasonlítási eljárás végrehajtásának fő feltétele ebben az esetben mindkét fájl ablakának egyidejű megnyitása. Az Excel 2013 és újabb verzióinál, valamint az Excel 2007 előtti verzióinál nincs probléma ezzel a feltétellel. Az Excel 2007 és az Excel 2010 programban azonban további manipulációk szükségesek ahhoz, hogy mindkét ablakot egyszerre meg lehessen nyitni. Ennek módját egy külön leckében ismertetjük.

Mint látható, számos lehetőség van a táblázatok egymással való összehasonlítására. Az, hogy melyik lehetőséget választjuk, attól függ, hogy a táblázatos adatok pontosan hol helyezkednek el egymáshoz képest (ugyanazon lapon, különböző könyvekben, különböző lapokon), valamint attól, hogy a felhasználó hogyan szeretné, hogy ez az összehasonlítás megjelenjen a képernyőn.

Tegyük fel, hogy össze szeretné hasonlítani egy munkafüzet verzióit, elemezni szeretne egy munkafüzetet problémák vagy következetlenségek keresésére, vagy munkafüzetek vagy munkalapok közötti hivatkozásokat szeretne látni. Ha a Microsoft Office 365 vagy az Office Professional Plus 2013 telepítve van a számítógépen, a Spreadsheet Inquire bővítmény elérhető az Excelben.

Az Érdeklődés lap parancsaival végrehajthatja ezeket a feladatokat és még sok mást. Az Excel szalag Érdeklődés lapján találhatók az alább leírt parancsok gombjai.

Ha nem látja a Érdeklődik lapon az Excel menüszalagon, lásd: A Táblázatkérő bővítmény bekapcsolása.

Hasonlítson össze két munkafüzetet

Az Fájlok összehasonlítása paranccsal láthatja cellánként a különbségeket két munkafüzet között. A parancs futtatásához meg kell nyitnia két munkafüzetet az Excelben.

Az eredmények színkóddal vannak ellátva a tartalom típusa szerint, mint például a megadott értékek, képletek, elnevezett tartományok és formátumok. Van még egy ablak is, amely soronként mutatja a VBA-kód változásait. A cellák közötti különbségek egy könnyen olvasható rácselrendezésben jelennek meg, például:

Az Fájlok összehasonlítása parancs a Microsoft Spreadsheet Compare segítségével hasonlítja össze a két fájlt. A Windows 8 rendszerben a Spreadsheet Compare alkalmazást az Excelen kívül is elindíthatja, ha rákattint Táblázat összehasonlítása a Alkalmazások elemre képernyő. Windows 7 rendszerben kattintson a Windows elemre Rajt gombot, majd> Minden program > Microsoft Office 2013 > Office 2013 eszközök > Táblázat összehasonlítása 2013.

Ha többet szeretne megtudni a Táblázat-összehasonlításról és a fájlok összehasonlításáról, olvassa el a Munkafüzet két verziójának összehasonlítása című részt.

Munkafüzet elemzése

Az Munkafüzet elemzése parancs interaktív jelentést hoz létre, amely részletes információkat tartalmaz a munkafüzetről és annak szerkezetéről, képleteiről, celláiról, tartományairól és figyelmeztetéseiről. Az itt látható képen egy nagyon egyszerű munkafüzet látható, amely két képletet és adatkapcsolatokat tartalmaz egy Access adatbázishoz és egy szöveges fájlhoz.

Munkafüzet hivatkozások megjelenítése

A cellahivatkozásokon keresztül más munkafüzetekhez kapcsolódó munkafüzetek zavaróak lehetnek. A segítségével interaktív, grafikus térképet hozhat létre a fájlok közötti kapcsolatok (hivatkozások) által létrehozott munkafüzet-függőségekről. A diagramban szereplő hivatkozások típusai lehetnek más munkafüzetek, Access-adatbázisok, szövegfájlok, HTML-oldalak, SQL Server-adatbázisok és egyéb adatforrások. A kapcsolati diagramon elemeket jelölhet ki, és további információkat találhat róluk, valamint a kapcsolódási vonalak húzásával módosíthatja a diagram alakját.

Ez a diagram az aktuális munkafüzetet mutatja a bal oldalon, valamint a kapcsolatokat más munkafüzetekkel és adatforrásokkal. A munkafüzet-kapcsolatok további szintjeit is megjeleníti, így képet kaphat a munkafüzet adatforrásairól.

Munkalaphivatkozások megjelenítése

Sok munkalapja van, amelyek egymástól függenek? A segítségével interaktív, grafikus térképet hozhat létre a munkalapok közötti kapcsolatokról (hivatkozásokról) mind ugyanabban a munkafüzetben, mind más munkafüzetekben. Ez segít tisztább képet adni arról, hogy adatai hogyan függhetnek más helyeken lévő celláktól.

Ez a diagram négy különböző munkafüzet munkalapjai közötti kapcsolatokat mutatja be, az ugyanabban a munkafüzetben lévő munkalapok közötti függőséggel, valamint a különböző munkafüzetekben lévő munkalapok közötti hivatkozásokkal. Ha a mutatót a diagram egy csomópontja fölé helyezi, például a diagram "Nyugat" nevű munkalapjára, megjelenik egy információkat tartalmazó buborék.

Cellakapcsolatok megjelenítése

Ha részletes, interaktív diagramot szeretne kapni a kijelölt cellákról más munkalapokon vagy akár más munkafüzetekben lévő összes hivatkozásról, használja a Sejtkapcsolat eszköz. Ezek a kapcsolatok más cellákkal képletekben vagy elnevezett tartományokra való hivatkozásokban létezhetnek. A diagram keresztezheti a munkalapokat és a munkafüzeteket.

Ez a diagram az 5. lap A10 cellájának cellakapcsolatainak két szintjét mutatja a Book1.xlsx fájlban. Ez a cella egy másik munkafüzet, a Book2.xlsx 1. munkalap 1. lapjának C6 cellájától függ. Ez a cella precedens több cellához ugyanazon fájl más munkalapjain.

Ha többet szeretne megtudni a cellakapcsolatok megtekintéséről, olvassa el A cellák közötti hivatkozások megtekintése című részt.

Tisztítsa meg a felesleges cellaformázást

Előfordult már, hogy kinyitott egy munkafüzetet, és azt tapasztalta, hogy lassan töltődik be, vagy hatalmas lett? Előfordulhat, hogy olyan sorokra vagy oszlopokra alkalmazta a formázást, amelyekről nem tud. Használja a Tisztítsa meg a felesleges cellaformázást paranccsal eltávolíthatja a felesleges formázást és nagymértékben csökkentheti a fájlméretet. Ez segít elkerülni a "táblázat felfúvódását", ami javítja az Excel sebességét.

Jelszavak kezelése

Ha az Inquire szolgáltatást használja jelszóval védett munkafüzetek elemzésére vagy összehasonlítására, akkor hozzá kell adnia a munkafüzet jelszavát a jelszólistához, hogy az Inquire meg tudja nyitni a munkafüzet mentett példányát. Használja a Munkafüzet jelszavak parancsot a Érdeklődik fület a jelszavak hozzáadásához, amelyeket a rendszer a számítógépére ment. Ezek a jelszavak titkosítottak, és csak Ön férhet hozzá.

A cikk a következő kérdésekre ad választ:

• Hogyan lehet összehasonlítani két táblázatot az Excelben?
• Hogyan lehet összehasonlítani az összetett táblázatokat Excelben?
• Hogyan lehet összehasonlítani a táblázatokat az Excelben a VLOOKUP () függvény segítségével?
• Hogyan lehet egyedi sorazonosítókat generálni, ha azok egyediségét kezdetben több oszlopban lévő értékkészlet határozza meg?
• Hogyan lehet lefagyasztani a cellaértékeket a képletekben képletek másolásakor?

Amikor nagy mennyiségű információval dolgozik, a felhasználónak olyan feladattal kell szembenéznie, mint két táblázatos adatforrás összehasonlítása. Az adatok egyetlen könyvelési rendszerben történő tárolásakor (például 1C Enterprise alapú rendszerek, SQL adatbázisokat használó rendszerek) a rendszerbe vagy a DBMS-be épített képességek használhatók az adatok összehasonlítására. Általános szabály, hogy ehhez elegendő egy programozó bevonása, aki lekérdezést ír az adatbázisba, vagy egy szoftveres jelentési mechanizmust. Egy tapasztalt felhasználó, aki tud 1C vagy SQL lekérdezéseket írni, szintén tud kezelni egy lekérdezést.

A gondok akkor kezdődnek, amikor sürgősen el kell végezni egy adat-összehasonlítási feladatot, és a programozó felvétele és az általa történő kérés vagy programjelentés időben történő megírása túllépheti a feladat megoldására kitűzött határidőket. Egy másik, hasonlóan gyakori probléma a különböző forrásokból származó információk összehasonlításának szükségessége. Ebben az esetben a probléma megfogalmazása a programozó számára úgy hangzik, mint két rendszer integrálása. Egy ilyen feladat megoldása magasabb programozói végzettséget igényel, és több időt vesz igénybe, mint az egységes rendszerben történő fejlesztés.

A jelzett problémák megoldására az ideális technika a Microsoft Excel táblázatszerkesztő használata az adatok összehasonlítására. A legtöbb általános irányítási és szabályozási számviteli rendszer támogatja az Excel formátumba történő exportálást. Ez a feladat csak bizonyos felhasználói képesítést igényel, hogy ezzel az irodai csomaggal dolgozhasson, és nem igényel programozási ismereteket.

Tekintsük a megoldást a táblázatok Excelben való összehasonlításának problémájára egy példa segítségével. Két táblázatunkban az apartmanok listája található. A kirakodás forrásai - 1C Enterprise (építési könyvelés) és egy táblázat az Excelben (értékesítési könyvelés). A táblázatok az Excel munkafüzet első és második lapján találhatók.

A mi feladatunk ezeknek a listáknak a cím szerinti összehasonlítása. Az első táblázat a ház összes apartmanját tartalmazza. A második táblázat csak az eladott lakásokat és a vevő nevét tartalmazza. A végső cél az, hogy minden lakásnál (az eladott lakásoknál) az első táblázatban megjelenjen a vevő neve. A feladatot nehezíti, hogy az egyes táblázatokban szereplő lakáscímek konstrukciós jellegűek, és több mezőből állnak: 1) az épület (ház) címe, 2) a rész (bejárat), 3) az emelet, 4) a szám a padlón (például 1-től 4-ig) ...

Két Excel-tábla összehasonlításához gondoskodnunk kell arról, hogy mindkét tábla minden sorát egy mező azonosítsa, ne négy. Egy ilyen mezőt úgy kaphat meg, hogy a négy címmező értékét összefűzi a Concatenate () függvénnyel. Az Összefűzés () funkció célja több szöveges érték egy sorban való egyesítése. A függvény értékei a ";" szimbólummal vannak felsorolva. Az értékek lehetnek cellacímek vagy tetszőleges idézőjelben megadott szövegek.

1. lépés. Szúrjunk be egy üres "A" oszlopot az első táblázat elejére, és írjuk be a képletet ennek az oszlopnak az első adatsorral szemben lévő cellájába:
= ÖSSZEFÜGGÉS (B3; "-"; C3; "-"; D3; "-"; E3)
A vizuális érzékelés kényelme érdekében az egyesítendő cellák értékei közé a "-" szimbólumokat állítjuk be.

2. lépés. Másolja a képletet az A oszlop következő celláiba.

4. lépés. Az Excel táblázatok érték szerinti összehasonlításához használja a VLOOKUP () függvényt. A VLOOKUP () függvény célja, hogy egy értéket keressen a táblázat bal szélső oszlopában, és visszaadja az ugyanazon sor megadott oszlopában található cella értékét. Az első paraméter a kívánt érték. A második paraméter az a táblázat, amelyben az érték keresésre kerül. A harmadik paraméter annak az oszlopnak a száma, amelyből a megtalált sorban az érték visszakerül. A negyedik paraméter a keresés típusa: a false pontos egyezés, a true pedig hozzávetőleges egyezés. Mivel a kimeneti információkat az első táblában kell elhelyezni (ebbe a táblázatba kellett beírni a vevők nevét), akkor ebbe kerül kiírásra a képlet. Alkossunk egy képletet a táblázat jobb oldalán, az első adatsorral szemben lévő szabad oszlopban:
= KERESÉS (A3; Lap2! $ A $ 3: $ F $ 10; 6; HAMIS)
Képletek másolásakor a Smart Excel automatikusan megváltoztatja a cellacímzést. Esetünkben az egyes sorok kívánt értéke megváltozik: A3, A4 stb., és a keresést végző táblázat címének változatlannak kell maradnia. Ehhez rögzítse a cellákat a táblázat cím paraméterében "$" szimbólumokkal. A "Sheet2! A3: F10" helyett a "Sheet2! $ A $ 3: $ F $ 10"-et csináljuk.

A bővítmény telepítése után egy új lap jelenik meg a függvény meghívására szolgáló paranccsal. Amikor rákattint a parancsra Tartományok összehasonlítása megjelenik egy párbeszédpanel a paraméterek megadásához.

Ezzel a makróval bármilyen méretű és tetszőleges számú oszlopot tartalmazó táblázatokat összehasonlíthat. A táblázatok összehasonlítása történhet egy, két vagy három oszlopban egyszerre.

A párbeszédpanel két részre oszlik: balra az első táblázatra és jobbra a másodikra.

A táblázatok összehasonlításához kövesse az alábbi lépéseket:

• Adja meg a táblázat tartományait.
• Állítsa be a jelölőnégyzetet (jelölőnégyzet / jelölőnégyzet) a kiválasztott táblázatok tartománya alatt, ha a táblázat tartalmaz fejlécet (fejlécsor).
• Válassza ki a bal és a jobb oldali táblázat oszlopait, amelyek alapján az összehasonlítást végrehajtja (ha a táblázatok tartományai nem tartalmaznak fejlécet, az oszlopok számozásra kerülnek).
• Adja meg az összehasonlítás típusát.
• Válassza ki az eredmények megjelenítési lehetőséget.

A táblázatok összehasonlítása

A program lehetővé teszi többféle táblázat-összehasonlítás kiválasztását:

Keresse meg az egyik táblázatból azokat a sorokat, amelyek hiányoznak egy másik táblából

Ha ez a fajta összehasonlítás van kiválasztva, a program az egyik táblázatból olyan sorokat keres, amelyek hiányoznak a másikból. Ha a táblázatokat több oszloponként hasonlítja össze, akkor a munka eredménye olyan sorok lesz, amelyekben legalább az egyik oszlopban eltérés van.

Találja meg a megfelelő vonalakat

Az ilyen típusú összehasonlítás kiválasztásakor a program megkeresi az első és a második táblázatban egyező sorokat. A sorokat egybevágónak tekintjük, ha az egyik táblázat kiválasztott összehasonlító oszlopainak (1, 2, 3) értékei teljesen egybeesnek a második táblázat oszlopainak értékeivel.

A kép jobb oldalán látható egy példa a program működésére ebben a módban.

Egyeztesse a táblázatokat a kiválasztottak alapján

Ebben az összehasonlítási módban az első (főként kiválasztott) tábla minden sorával szemben a második táblázat egyező sorának adatait másolják. Ha nincsenek egyező sorok, a főtábla előtti sor üres marad.

Négy vagy több oszlop táblázatainak összehasonlítása

Ha hiányzik a program funkcionalitása, és a táblázatokat négy vagy több oszlopban kell egyeztetni, akkor a következőképpen léphet ki a helyzetből:

• Hozzon létre egy üres oszlopot a táblázatokban.
• Új oszlopokon a = képlet használatával CSATLAKOZÁS egyesítse az összehasonlítani kívánt oszlopokat.

Így 1 oszlopot kap, amely több oszlop értékeit tartalmazza. Nos, már tudja, hogyan kell egy oszlopot egyeztetni.