A kiválasztási kérelem lényege az, hogy kiválasszon egy karakterláncot a forrás táblázatból, amely megfelel bizonyos kritériumoknak (hasonlóan a szabvány használatához). A forrás táblázatból származó értékeket választjuk ki. A használatával ellentétben ( Ctrl + Shift + L vagy Adat / rendezés és szűrő / szűrő) A kiválasztott vonalak külön táblázatba kerülnek.
Ebben a cikkben vegye figyelembe a leggyakoribb kéréseket, például: az asztali sorok kiválasztását, amelyekben a numerikus oszlopból származó érték a megadott tartományba (intervallum); A sorok kiválasztása, amelyben a dátum megad egy bizonyos időszakot; 2 szöveges kritériummal és másokkal rendelkező feladatok. Kezdjük egyszerű lekérdezésekkel.
1. Egy számmerikus kritérium (Válassza ki az árut, amely a minimum felett van)
példa fájl, lap Egy kritérium - szám ).
Meg kell jeleníteni egy külön táblázatban csak azokat a rekordokat (vonalak) a forrás táblázatból, amelyben az ár 25-nél nagyobb.
Megoldja ezt, és a későbbi feladatok könnyen lehetnek. Ehhez válassza ki a forrásasztal címsorát, majd kattintson Ctrl + Shift + L. A cím legördülő listáján, válassza ki Numerikus szűrők ..., majd állítsa be a szükséges szűrési feltételeket, majd kattintson az OK gombra.
A felvételek kielégítik a kiválasztási feltételeket.
Egy másik megközelítés a használat. A kiválasztott vonalaktól eltérően külön táblázatba kerülnek - egy különös, amely például a forrás táblázaton kívüli stílusban formázható, vagy más módosításokat eredményezhet.
Kritérium (minimális ár) kerül a sejtbe kerül E6. , Táblázat a szűrt adatokhoz - a tartományban D10: E19 .
Most válassza ki a tartományt D11: D19. (oszlop termék) és a bevezetésben:
Index (A11: A19;
A legkisebb (ha ($ e $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
Stroke ($ b $ 10))
Helyette BELÉP Nyomja meg a gombot Ctrl + Shift + Enter(A masszív formula).
E11: E19 (oszlopár), ahol és adja meg ugyanazt:
Index (B11: B19;
A legkisebb (ha ($ e $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
Stroke ($ b $ 10))
Ennek eredményeként új táblázatot kapunk, amely csak olyan termékeket tartalmaz, amelyeket az árak nem kevésbé vannak megadva a sejtben E6. .
A fogadott kiválasztási kérelem dinamizmusának megmutatása, bemutatjuk E6. Érték 55. Csak 2 bejegyzés lesz az új táblázatba.
Ha a forrás táblázat új terméket ad hozzá egy 80-as árhoz, akkor egy új bejegyzés automatikusan hozzáadódik az új táblázathoz.
jegyzet. Ezenkívül a szűrt adatok kiadására is használható. Egy adott eszköz kiválasztása a felhasználó felé néző feladattól függ.
Ha nem lehet kényelmes használni massiva FormulaAmi több értéket ad vissza, használhat egy másik megközelítést, amelyet az alábbi szakaszokban vesznek figyelembe: 5.A, 7, 10 és 11. Ezekben az esetekben.
2. Két numerikus kritérium (Válassza ki az árukat, amelyeket az ár a tartományba esik)
Hagyja, hogy van-e forrás táblázat az áruk és az árak listájával (lásd példa fájl, lapSzámválaszték).
A kritériumok (alacsonyabb és legmagasabb árhatárok) a tartományba kerülnek E5: E6. .
Azok. Ha az áruk ára a megadott intervallumba esik, akkor egy ilyen rekord jelenik meg a szűrt adatok új táblázatában.
Ellentétben az előző feladathoz, kettőt hozunk létre: áruk és árak (anélkül, hogy megtehetsz, de kényelmesek, ha képleteket írnak). A megfelelő képleteknek névkezelőnek kell lenniük ( Formulák / Különleges nevek / Névkezelő) Az alábbiak szerint (lásd az alábbi ábrát).
Most válassza ki a tartományt D11: D19. És a bevezetésben:
Index (áruk;
LEGKEVÉSBÉ(
Ha ($ E $ 5<=Цены)*($E$6>\u003d Árak); sor (árak); "");
Helyette BELÉP Nyomja meg a gombot Ctrl + Shift + Enter.
Ugyanazok a manipulációk termelnek egy tartományt E11: E19 Ahol és bevezetni hasonló:
Index (árak;
LEGKEVÉSBÉ(
Ha ($ E $ 5<=Цены)*($E$6>\u003d Árak); sor (árak); "");
Sor (árak) -strook ($ b $ 10)) - sor ($ b $ 10))
Ennek eredményeként új táblázatot kapunk, amely csak olyan termékeket tartalmaz, amelyeket az árak a sejtekben megadott intervallumba esnek E5 és E6. .
A fogadott jelentés dinamizmusa (kiválasztási kérelem) beillesztése E6. Érték 65. Egy másik bejegyzést a forrás táblából hozzá fog adni az új táblázathoz, kielégítve az új kritériumot.
Ha új terméket ad hozzá a forrás táblázatban 25-től 65-ig terjedő árral, új bejegyzést adnak az új táblázathoz.
A példafájl hibaelhárítási képleteket is tartalmaz, ha az ár oszlopban hibaértéket tartalmaz, például # esetek / 0! (lásd a levelet Hibafeldolgozás).
A következő feladatokat hasonló módon oldják meg, így részletesen nem fogjuk figyelembe venni őket.
3. Egy kritérium dátuma (Válassza ki azokat a termékeket, amelyeket a szállítási határidő meg van adva)
példa fájl, lapEgy kritérium - dátum).
A sorok kiválasztásához, a tömb formulák, hasonló a feladathoz1 (a kritérium helyett<= используется =):
=Index (A12: A20, a legkisebb (ha ($ e $ 6 \u003d b12: B20, sor (B12: B20); ""); String (B12: B20) - String ($ b $ 11) - vonal ($ B $ 11))
Index (B12: B20, a legkisebb (ha ($ e $ 6 \u003d B12: B20, sor (B12: B20); ""); String (B12: B20) - String ($ b $ 11) - sor ($ b B $ 11))
4. Két kritérium dátuma (Válassza ki az árut, hogy a szállítási dátum csökken a tartományba)
Hagyja, hogy van-e forrás táblázat az áruk és a szállítási dátumok listájával (lásd példa fájl, lapIdőintervallum).
Kérjük, vegye figyelembe, hogy a dátumok oszlopa nem rendezett.
Megoldás: A sorok kiválasztásához használható.
Belép a cellába D12. a tömb képlete:
Index ($ 12: egy 20 dollár;
A legnagyobb (($ e $ 6<=$B$12:$B$20)*($E$7>\u003d $ B $ 12: $ b $ 20) * (Line ($ b $ 12: $ b $ 20) -strokok ($ b $ 11));
$ J $ 12 sor (A12) + sor ($ b $ 11) +1)))
jegyzet: Miután belépett a képletbe, az Enter billentyű helyett, meg kell nyomnia a CTRL + SHIFT + ENTER billentyűkombinációt. Ez a kulcs kombináció a tömb formula beírására szolgál.
Másolja a masszív képletet a kívánt számú sejtszámra. A képlet csak azokat az áruk értékeit fogja visszaadni, amelyeket ezen időpontok tartományában szállítottak. A többi cella hibákat tartalmaz # szám! Hibák B. fájl példa (dátumlista) .
Hasonló képletet kell bevezetni az E. oszlopban
Cellában. J12 a kritériumokat kielégítő forrás táblázat sorai számát kiszámítják:
Kollekció (B12: B20; "\u003e \u003d" & $ e $ 6; B12: B20; "<="&$E$7)
A kritériumoknak megfelelő forrás táblázat sorai.
Megoldás2: A sorok kiválasztásához használhatja a tömb-képleteket, hasonlóan a feladathoz2 (azaz):
=Index (A12: A20; a legkisebb (ha (($ e $ 6<=B12:B20)*($E$7>\u003d B12: B20); vonal (B12: B20); ""); String (B12: B20) -Strkok ($ 11 $ 11) - sor ($ b $ 11))
Index (B12: B20, legkisebb (ha ($ e $ 6<=B12:B20)*($E$7>\u003d B12: B20); vonal (B12: B20); ""); String (B12: B20) -Strkok ($ 11 $ 11) - sor ($ b $ 11))
Az első képlet beírásához jelölje ki a sejtek tartományát G12: G20 . Miután belépett a képletbe, az Enter gomb helyett meg kell nyomnia a CTRL + SHIFT + ENTER billentyűkombinációt.
Oldat3.: Ha a dátumok oszlopát rendezi, akkor nem használhatja a tömb-képleteket.
Először ki kell számolni a kritériumoknak megfelelő sorok első és utolsó helyzetét. Ezután kimeneti karakterláncok.
Ez a példa ismét egyértelműen bemutatja, hogy milyen könnyebb írásforma.
5. Egy kritérium dátuma (válassza ki azokat a termékeket, amelyeket a szállítási határidő nem korábbi / legkésőbb a megadott)
Hagyja, hogy van-e forrás táblázat az áruk és a szállítási dátumok listájával (lásd példa fájl, lap Egy kritérium - dátum (nem később) ).
A sorok kiválasztásához a dátum, amelynek időpontja nem korábban (beleértve a dátumot is), a tömb formula használat:
=Index (A12: A20, legkisebb (ha ($ e $ 7<=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))
A példafájlban a formulák is megadhatók: nincs korábban (nem beleértve); Nem később (beleértve); Nem később (nem beleértve).
7. Egy szöveges kritérium (válasszon egy bizonyos típusú terméket)
Hagyja, hogy van-e forrás táblázat az áruk és az árak listájával (lásd példa fájl, lapEgy kritérium - szöveg).
8. Két szöveges kritérium (válassza ki az adott típusú készletet a megadott hónapban)
Hagyja, hogy van-e forrás táblázat az áruk és az árak listájával (lásd példa fájl, lap 2 kritérium - szöveg (ek) ).
Index ($ 11: $ 19 $;
A legkisebb (ha ($ 6 $ 6 \u003d $ 11: $ 11: $ 19) * ($ F $ 7 \u003d $ 11: $ b $ 19); sor ($ 11 $: $ 19 ) -Strk ($ 10), 30); sor (DVSSL ("A1: A" és ESTEXT ($ 11: $ A $ 19)))))
Kifejezés ($ F $ 6 \u003d $ 11 $: $ A $ 19) * ($ F $ 7 \u003d $ 11: $ b $ 19) Megadja mindkét feltétel (áruk és hónap).
Kifejezés Sor (DVSSL ("A1: A" & CREKOK ($ 11: $ A $ 19))))) Formák (1: 2: 3: 4: 5: 6: 7: 8: 9), vagyis Sorszámok az asztalon.
9. Két szöveges kritérium (bizonyos fajok termékei)
Hagyja, hogy van-e forrás táblázat az áruk és az árak listájával (lásd példa fájl, lap2 kritérium - szöveg (vagy)).
A feladathoz képest 7 fogja felvenni a húrokat 2 faj ().
A sorok kiválasztásához a tömb képletet használják:
Index (11 $: egy 19 dollár;
A legnagyobb (($ E $ 6 \u003d $ 11: $ 11: $ 19) + ($ E $ 7 \u003d $ 11: $ 11: $ 19)) * (sor ($ 11 $: $ A $ 19) -stris ($ 10)); számlálva ($ 11 $: $ 19, $ 19, $ e $ 6) + visszaszámlálás ($ 11 $: $ 19, $ 7) -Cons ( $ 11: A11) +1)
Feltétel ($ E $ 6 \u003d $ 11: $ 11: $ 19) + ($ E $ 7 \u003d $ 11: $ 19 $) Biztosítja, hogy csak a sárga cellákból származó meghatározott fajok (áruk2 és áruk3) termékeket választják ki. A + (kiegészítés) a feladathoz (legalább 1 kritériumot kell végrehajtani).
A fenti kifejezés visszatér egy tömböt (0: 0: 0: 0: 0: 1: 1: 1: 0: 0). Szorítva őt Sor ($ 11: $ 19 $) -strook ($ 10 $). (1: 2: 3: 4: 5: 6: 7: 8: 9), kapunk egy sor pozíciót (az asztali vonalak száma), kielégítő a kritériumokat. A mi esetünkben egy tömb lesz (0: 0: 0: 0: 0: 5: 6: 0: 0).
Például megoldást kell tenni a következő feladatra: Válassza ki azokat az árut, amelyeknek az ár egy adott tartományban van, és megismétli a megadott számú alkalommal vagy többet.
Kezdeti kezdeti, vegye fel az áruk jegyzékét.
Tegyük fel, hogy érdekelünk, hogy hány, és melyik fél árut szállítottak 1000R áron. 2000-ig (1. kritérium). Ezenkívül az azonos árú felek legalább 3 (2. kritérium).
A megoldás a Massif Formula:
A legkisebb (sor ($ 14 $ 27 $ 27) * ($ C $ 14: $ C $ 27\u003e \u003d $ b $ 7) * ($ C $ 14: $ C $ 27<=$C$7)*($D$14:$D$27>\u003d $ B $ 10); F14 + ($ G $ 8- $ 9 $ 9)
Ez a képlet mindkét kritériumot kielégítő sorszámokat ad vissza.
Képlet \u003d Equice ($ 14 $ 14: $ C $ 27\u003e $ b $ 7) * ($ C $ 14: $ 27<=$C$7)*($D$14:$D$27>\u003d $ B $ 10))) Kiszámítja a kritériumoknak megfelelő sorok számát.
11. A kritérium (bármely) vagy (minden) értékét használjuk
BAN BEN példa fájl a lapon "11. bármely vagy (minden) kritériuma" Ezt az opciót végrehajtják.
A jelen esetben a képletnek tartalmaznia kell egy funkciót, ha (). Ha az érték van kiválasztva (ALL), a képlet a kimeneti értékek, anélkül, hogy figyelembe veszi volna ezt a kritériumot. Ha bármilyen más érték van kiválasztva, a kritérium a szokásos módon működik.
Ha ($ C $ 8 \u003d "(minden)";
A legkisebb ((sor ($ b $ 13: $ 26 $ 26) -strok ($ b $ 12)) * ($ D $ 13: $ 2 $ 26\u003e $ D $ 8); F13 + ($ G $ 6- $ G $ 7)));
A legkisebb ((sor ($ b $ 13: $ 26 $) -strook ($ b $ 12)) * ($ D $ 13: $ D $ 26\u003e \u003d $ D $ 8) * ($ C $ 13: $ C $ 26 \u003d $ C $ 8); F13 + ($ G $ 6- $ 7)))))))
A többi képlet hasonló a fent tárgyalt.
Adatmintavétel
Hozzon létre egy mintajelentést egy lapról5 oszlopra "Minőségi teljesítmény, százalék". (a 8. oldalon, 7. táblázat)
Adatválasztás készítéséhez a következő lépéseket kell végrehajtania:
Határozza meg az új tömb elemeinek számát egy adott állapotban egy változó bevitelével az Inputbox operátor használatával
Írja le és frissítse az új tömböt
Új tömb létrehozásához. Ehhez adja meg az új tömb u \u003d 1 első elemének számát. Ezután a ciklust elvégzik, amely rögzíti a mintaállapotot az oszlopban "minőségi teljesítmény, százalék". Ha az igazság ellenőrzésének eredménye, az elemzett tömb eleme egy új tömb elemévé válik.
Visszatér egy új elemet a 8 lapon
Sub Defense Baby ()
Lapok ("List8"). Kiválaszt
Dim A () változatként
REDIM A (1 - N1, 1 - M)
VVOD "SHEF5", A, N1, M, 4
C \u003d inputbox ("Adja meg az állapotot")
Lapok ("List8"). Sejtek (5.11) \u003d c
I \u003d 1-től n1-ig
Ha egy (i,
8) >
d \u003d d + 1
Lapok ("List8"). Sejtek (5.10) \u003d d
Dim B () változatként
Redim B (1 - D, 1 - M)
I \u003d 1-től n1-ig
Ha egy (i,
8)\u003e Lemezek ("List8"). A sejtek (5.11)
A j \u003d 1-re m
B (u, j) \u003d a (i, j)
u \u003d U + 1
I \u003d 1-re d
A j \u003d 1-re m
Lapok ("List8"). Sejtek (i + 4, j) \u003d b (i, j)
6. ábra. A mintavétel utáni táblázatadatok
Automatikus minta makró létrehozása
Kapcsolja be a makró felvételt. Szolgáltatás\u003e Makró\u003e Record indítása\u003e OK. Úgy tűnik, hogy a STOPPING gomb megjelenik. A lapon5 (jelentés) kiemeljük a fejlécek és eredmények nélküli táblázatot, másoljuk a listát10 (automatikus kollektor). Kiemeljük az asztalt fejlécek nélkül és a menüpontban, válassza az Adatok\u003e Szűrő\u003e Automatikus szűrő\u003e Állapot\u003e OK lehetőséget. Megünnepeljük az oszlopot, amelyen rendezzük. Töltse ki a makró munkáját.
Sub Macro2 érték ()
"Macro 22 Makró makró
Lapok ("List5"). Kiválaszt
Kiválasztás. Másolat
Lapok ("List9"). Kiválaszt
ActiveSheet. Paszta.
Tartomány ("H5: H17"). Kiválaszt
Alkalmazás. Cutcopymode \u003d FALSE
Kiválasztás. Autofilter.
ActiveSheet. Tartomány ("$ h $ 5: $ h $ 17"). AutoFilter Field: \u003d 1, kritériumok1: \u003d "\u003e 80", _
Üzemeltető: \u003d xland
Tartomány ("G22"). Kiválaszt
7. ábra. Asztali adatok az automatikus mintavétel után
A maximális és a minimális érték meghatározása
Határozza meg a maximális és min értékeket az oszlopok "Teljes", "abszolút teljesítmény, százalék", "Kiváló minőségű teljesítmény" (9. táblázat, 10. lap)
A Max és Min értékek meghatározásához a következő lépéseket kell végrehajtania:
Állítsa be az aktuális minimum (maximum) szabványos változót
Korai összehasonlítja a készlet minden elemét a jelenlegi minimummal (maximum), és ha ez az elem nem felel meg a keresési feltételeknek (minimális esetekben nagyobb, nagyobb, és maximum kevesebb), majd a szabvány az értéket az összehasonlító elem értéke váltja fel
A jelenlegi minimum változójának teljes megtekintése után érvényes minimum (maximum)
A minimális érték (maximum) megjelenik a megfelelő sejtekben.
Sub Minmax ()
Dim A () változatként
n1 \u003d lapok ("Lap4"). Sejtek (5,12)
m \u003d lapok ("List2"). Sejtek (5,12)
REDIM A (1 - N1, 1 - M)
VVOD "SHEF5", A, N1, M, 4
VIVOD "LIST10", A, N1, M, 4
VVOD "List10", A, N1, M, 4
A j \u003d 3-mal m
maxa \u003d 0,00001
mINA \u003d 1000000.
I \u003d 1-től n1-ig
Ha egy (i, j)\u003e maxa thenn
maxa \u003d A (I, J)
Ha A (I, J)< minA Then
mINA \u003d A (I, J)
Lapok ("lista10"). Sejtek (i + 4 + 2, j) \u003d maxa
Lapok ("lista10"). Cellák (I + 4 + 3, J) \u003d MINA
A makró a további felhasználásra rögzített és mentett műveletek sorozata. A mentett makró egy speciális csapat reprodukálható. Más szóval, akkor a Makróban lévő műveleteket rögzítheti, mentse el, majd engedélyezze, hogy a többi felhasználó az akció makróban történő lejátszásához egyszerűen megnyomja a gombot. Ez különösen alkalmas az összefoglaló táblázatokról szóló jelentések elosztásakor.
Tegyük fel, hogy ügyfeleinknek lehetőséget nyújtanak arra, hogy hónapokig, negyedévekben és években a konszolidált táblázatok jelentéseit csoportosítsák. Technikailag a csoportosítási folyamat elvégezheti a felhasználót, de néhány ügyfele nem tekinthető szükségesnek ahhoz, hogy megértsük ezt. Hasonló esetben a harmadik hónapokig, a másik - negyedévben és a harmadik évről évre leírhat egy makrócsoportot. Ezután hozzon létre három gombot - egyet minden egyes makróra. Ezután az ügyfelek, akik nem rendelkeznek tapasztalattal a konszolidált táblázatokkal, csak a gombra kattintva az összefoglaló táblázat megfelelő csoportjához kattintva.
A makrók felhasználásának fő előnye a konszolidált táblázatokról szóló jelentésekben az, hogy az ügyfelek képesek legyenek gyorsan elvégezni az ilyen műveleteket a konszolidált táblázatokban, amelyeket nem tudnak teljesíteni a szokásos helyzetben. Ennek köszönhetően a megadott adatok elemzésének hatékonysága jelentősen növekszik.
Töltse le a formátumban, vagy töltse le a formátumban, vagy töltse le a példákkal (az Excel fájlban makrókkal; a szolgáltató házirendje nem teszi lehetővé a formátum fájljának közvetlenül feltöltését a webhelyre).
Makró rekord
Vessen egy pillantást az 1. ábrán bemutatott konszolidált táblára. 1. Ezt a konszolidált táblázatot jobbra kattintva frissítheti, és kiválaszthatja a parancsot. Frissítés. Ha a pivot tábla frissítése során makró formájában lévő műveleteket rögzítette, akkor Ön vagy bármely más felhasználó reprodukálhatja ezeket a műveleteket, és frissíti a konszolidált táblázatot a makróindítás eredményeként.
Ábra. 1. Felvételi intézkedések Az összefoglaló táblázat frissítése közben tovább frissíti az adatokat a makró elindítása eredményeként
A makró rekord első szakasza a párbeszédpanel hívása Makró rekord. Kattintson a lapra Fejlesztő Ribbons és kattintson a gombra Makró rekord. (Ha nem találja meg a szalag lapját Fejlesztő, Válassza ki a lapot Fájlés kattintson a gombra Paraméterek. A párbeszédablak párbeszédpanelen Excel beállítások Válasszon egy kategóriát Szalag beállítása És a jobb oldali listában válassza ki a jelölőnégyzetet Fejlesztő. Ennek eredményeként a szalag megjelenik a szalagon Fejlesztő.) Alternatív módja a makró rögzítésének elindításához kattintson a gombra (2. ábra).
A párbeszédpanelen Makró rekord Adja meg a következő makróinformációkat (3. ábra):
Névmakró. A névnek leírnia kell a makró által végrehajtott műveleteket. A névnek meg kell kezdenie a betűvel vagy aláhúzással; nem tartalmazhat helyet és egyéb elfogadhatatlan jeleket; Nem kell egybeesnie az Excel beágyazott nevével vagy egy másik objektum nevével a könyvben.
Kombinációkulcsok. Ezen a területen bármilyen levelet írhat be. Ez lesz része a kulcsfontosságú kombinációnak, amelyet a makró lejátszására használnak. A kulcsfontosságú kombináció opcionális. Alapértelmezés szerint csak a CTRL-t kínálják a kombináció kezdeteként. Ha azt szeretné, hogy a kombináció bekapcsolja a váltást is, tárcsázza a betűt az ablakban, miközben lenyomva tartja a Shift billentyűt
Mentésban ben. Ez jelzi a makró tárolásának helyét. Ha egy összefoglaló táblázatot fogsz terjeszteni a többi felhasználó között, válasszon ki egy paramétert Ezkönyv. Az Excel lehetővé teszi a makró mentését is Új könyv vagyon Személyes könyv makrók.
Leírás. Ez a mező leírja a létrehozott makró leírását.
Ábra. 3. Az ablak beállítása Makró rekord
Mivel a makró frissíti a konszolidált táblázatot, válassza a Név lehetőséget Frissítések. A CTRL + SHIFT + Q billentyűkombinációt is hozzárendelheti. Ne feledje, hogy a makró létrehozása után ezt a kulcskombinációt elindíthatja. Válasszon egy paramétert makró tárolási helyként Ezt a könyvet és kattintson rendben.
A párbeszédpanelen kattintva Makró rekord A gombon rendben Makró kezdődik. Ebben a szakaszban az Excel-ben végrehajtott összes művelet regisztrálva lesz.
Jobb egérgombbal kattintson a konszolidált asztalterületre, és válassza ki a parancsot. Frissítés (az 1. ábrán látható, de már a makró felvételi módban). A Pivot tábla frissítése után leállíthatja a makró felvételi folyamatot a gomb segítségével. Állítsa be a felvételt lapok Fejlesztő. Vagy kattintson az 1. ábrán látható gombra. 2.
Tehát most rögzítetted az első makrót. Most végrehajthatja a makrót a Ctrl + Shift + Q billentyűkombinációval.
Makró biztonsági figyelmeztetés. Meg kell jegyezni, hogy a felhasználók által a biztonsági makrók rögzítése esetén a biztonsági alrendszer részét képező korlátozások nélkül fogják végrehajtani. Mindazonáltal, amikor egy makrókat tartalmazó munkakönyv elosztása során meg kell adni a lehetőséget, hogy más felhasználók számára biztosítsák, hogy megbizonyosodjanak arról, hogy nincs kockázat a munkadarabok megnyitásában, és a makrók végrehajtása nem vezet a vírusrendszer fertőzéséhez. Különösen észrevehető, hogy az ebben a fejezetben használt példafájl nem fog teljes mértékben működni, ha nem konkrétan megengedi az Excel-t a makrók elvégzéséhez.
A makrók biztonságának legegyszerűbb módja, hogy megbízható helyet hozzon létre - olyan mappát, amelyhez csak "megbízható" munkafüzetek kerülnek elhelyezni, amelyek nem tartalmaznak vírusokat. A megbízható helyszín lehetővé teszi az Ön és ügyfelei számára, hogy olyan makrókat hajtsanak végre a biztonsági alrendszer részének korlátozása nélkül (az ilyen viselkedés mentésre kerül, amíg a munkakönyvek bizalmi mappában vannak).
A megbízható hely konfigurálásához kövesse az alábbi lépéseket.
Válassza ki a szalagot Fejlesztő és kattintson a gombra Biztonsági makrók. Megjelenik egy párbeszédpanel a képernyőn. Biztonsági menedzsment központ.
Kattintson a gombra Új hely hozzáadása.
Kattintson a gombra ÁttekintésA megbízható munkamódszerek mappájának megadása.
Miután meghatározta a megbízható helyet, az önkényes makrókat alapértelmezés szerint végezzük el minden munkakönyvben.
Az Excel 2013-ban javult a biztonsági modell. Most a korábban "bizalom" munkafüzetfájlokat emlékeztették, én. Az Excel könyv megnyitása után kattintson a gombra A tartalom engedélyezése Az Excel emlékszik erre az állapotra. Ennek eredményeképpen ez a könyv a megbízható, és a felesleges kérdések nem szerepelnek a későbbi megnyitása során.
Felhasználói felület létrehozása űrlapvezérlők segítségével
Makró elindítása a CTRL + SHIFT + Q gomb kombináció használatával abban az esetben, ha egy konszolidált táblázatjelentés csak egy makróval rendelkezik. (Emellett a felhasználóknak meg kell tudni, hogy ez a kombináció.) De tegyük fel, hogy szeretnénk, hogy ügyfeleink több makrók teljesítő különböző tevékenységek. Ebben az esetben az ügyfeleknek világos és egyszerű módja van az egyes makrók elindításához, anélkül, hogy a kulcskombinációkat emlékeznének. A tökéletes megoldás egy egyszerű felhasználói felület egy olyan vezérlőkészlet formájában, mint a gombok, tekercscsíkok és más eszközök, amelyek lehetővé teszik a makrók egérkattintással történő végrehajtását.
Excel Ajánlatok az Ön rendelkezésére áll egy olyan eszközkészlet, amelynek célja, hogy hozzon létre egy felhasználói felületet közvetlenül a táblázatban. Ezeket az eszközöket űrlapvezérlő elemeknek nevezik. Az alapötlet az, hogy az űrlapvezérlést a táblázatba helyezheti, és hozzárendelheti egy makrót, amelyet korábban rögzítettek. A kinevezés után a makró ezen az elemen fut.
Az űrlapvezérlők megtalálhatók a csoportban Űrlapvezérlő elemek Tapes szalagok Fejlesztő. A vezérlők paletta megnyitásához kattintson a gombra a gombra a gombra Betét (4. ábra).
Ábra. 4. Forma vezérlőelem Gomb
Megjegyzés: Az űrlap vezérlése mellett a paletta is jelen van ActiveX elemek. Bár hasonlóak, programosan különböző tárgyak. Űrlapvezérlő elemeka korlátozott funkciókkal és egyszerű beállításokkal kifejezetten a munkalapokon történő szállásra tervezték. Eközben ActiveX elemek Alkalmazzon főként egyedi formákban. Szabályt kell tenni, hogy kizárólag vezérlők formálja a munkadarabokat.
A feladat megoldásához leginkább megfelelő vezérlőket kell kiválasztania. Ebben a példában az ügyfeleknek meg kell adniuk a konszolidált táblázat frissítését a gombnyomással. Kattintson a vezérlőelemre Gomb, Mozgassa az egérmutatót a munkalap helyére, ahol a gombot kell elhelyezni, és kattintson az egérre.
Miután elhelyezte a gombot az asztalra, megnyílik a párbeszédpanel. Makró hozzárendelése tárgy (5. ábra). Válassza ki a kívánt makrót (a mi esetünkben - Frissítések, korábban rögzített), és kattintson a gombra rendben.
Ábra. 5. Válassza ki a gombhoz rendelt makrót, és kattintson a gombra. rendben. Ebben az esetben a makrót kell alkalmazni Frissítések
Miután elhelyezte az összes szükséges vezérlést a Pivot táblázatban, formázhatja az asztalt egy alapfelület létrehozásához. Ábrán. A 6. ábra a formázás után összefoglaló táblázatot mutat.
A rögzített makró módosítása
A makró felvétel eredményeként az Excel program létrehoz egy olyan modult, amely a befejezett. Minden rögzített műveletet VBA-Code-karakterláncok képviselik, amelyekből a makró áll. Különböző funkcionalitást adhat hozzá a konszolidált táblázat jelentéseihez, a VBA kód beállítása a szükséges eredmények megszerzéséhez. Annak érdekében, hogy könnyebben megértsük, hogyan működik, hozzon létre egy új makrót, visszavonja az öt első nyilvántartást az ügyfelekről. Kattintson a lapra Fejlesztő és kattintson a gombra Makró rekord. Az 1. ábrán látható párbeszédpanel 7. NEVE A létrehozott makró ElsőNsakazchikov és adja meg a mentési helyet Ezt a könyvet. Kattintás rendbenMakró felvételének elindításához.
A felvétel elindítása után kattintson a mező melletti nyílra Ügyfél neve, Válassza ki Szűrő szerint és opció Az első 10 (8a. Ábra). A megjelenő párbeszédpanelen állítsa be a beállítást az 1. ábrán látható módon. 8b. Ezek a beállítások azt jelzik, hogy megjelenítik az öt ügyfél adatait, a legjobbat az értékesítés szempontjából. Kattintás rendben.
Ábra. 8. Válassza a Szűrő (A) lehetőséget, és konfigurálja a paramétereket (B), hogy megjelenítse az első öt ügyfelet az értékesítéshez.
Miután az összes olyan művelet sikeres rekordja után, hogy az értékesítési első öt ügyfelek kivonásához szükséges, menj a fülre Fejlesztő és kattintson a gombra Állítsa be a felvételt.
Most már van egy makró, amely szűri a konszolidált táblázatot, hogy kivonja az öt legjobb ügyfelet az értékesítésre. Meg kell tenni, hogy a makró reagáljon a görgetősáv állapotára, azaz A görgetősáv használatával meg kell adnia az ügyfelek makrószámát, amelyek adatai a konszolidált táblázatjelentésben jelennek meg. Így a görgetősáv segítségével a felhasználó képes lesz kivonni az öt legjobb, nyolc legjobb vagy harminc-két legjobb ügyfelet saját belátása szerint.
Ahhoz, hogy a táblázatkezelőhöz görgethessen a táblázatkezelőhöz, lépjen a fülre. FejlesztőKattintson a gombra BetétVálassza ki a paletta vezérlését Görgetősáv És helyezze el a munkalapra. Kattintson a jobb gombbal a vezérlőelemre Görgetősáv Formátum objektum. Megnyílik egy párbeszédpanel Vezérlő formátum (9. ábra). Benne, tegye a következőket a beállítások: Paraméter Minimális érték 1. megjegyzés, paraméter Maximális érték - 200 érték, és a területen Kommunikáció a cellával Adja meg a $ M $ értéket úgy, hogy az M2 cellában megjelenítse a görgetősávot. Kattintson a gombra rendbenA korábban megadott beállítások alkalmazásához.
Most össze kell hasonlítani az újonnan rögzített makrót ElsőNsakazchikov Vezérlőelemmel Görgetősáva munkalapon található. Kattintson a jobb gombbal a vezérlőelemre Görgetősáv És a helyi menüben válassza ki a parancsot Makró hozzárendeléseA makró kinevezési párbeszédpanel megnyitása. Rögzített makró rögzítése FirstNenzakazchikov. A makró minden alkalommal kerül végrehajtásra, ha rákattint a görgetősávra. Tesztelje a létrehozott görgetősávot. A sávra kattintva a makró elindul FirstNenzakazchikov És az M2-sejtben lévő szám megváltoztatja a görgetősáv állapotát. Az M2-sejtben szereplő szám fontos szerepet játszik, mivel a makró a görgetősávhoz kötődik.
Az egyetlen dolog, amit meg kell tennie, hogy kényszerítse a makrót, hogy feldolgozza a számot az M2-sejtben, összekapcsolja egy görgetősávkal. Ehhez menjen a makró VBA-kódjához. Ehhez menjen a fülre Fejlesztő és kattintson a gombra Makrók. Megnyílik egy párbeszédpanel Makró (10. ábra). A kiválasztott makró futtatása, törlése és szerkesztése. A VBA makró kód megjelenítéséhez válassza a Makró lehetőséget, és kattintson a gombra. változás.
Ábra. 10. A makró VBA kód eléréséhez ElsőNsakazchikov, válassza ki a makrót, és kattintson a gombra változás
A Makró VBA kóddal ellátott vizuális alapszerkesztője megjelenik a képernyőn (11. ábra). A cél az, hogy a makró felvétel során telepített, az 5-ös számban meghatározott 5 számot cserélje ki, az M2-sejt értékét, amely a görgetősávhoz van kötve. Kezdetben a makrót rögzítették, úgy tervezték, hogy megjelenítse az első öt ügyfelet, akiknek a legnagyobb jövedelme van.
Távolítsa el az 5. számot a kódból, és írja be a következő kifejezést:
Activesheet.range ("M2") .érték
Adjon hozzá két húrot a makróhoz a szűrők tisztításához:
Tartomány ("A4") .Válassza ki
ActiveSheet.pivottables ("Pivottable1") .pivotfields ("ügyfélnév") .ClearalallFilders
Most a makró kódnak úgy kell kinéznie, mint az 1. ábrán látható. 12.
Zárja be a Visual Basic Editor-t, és térjen vissza a konszolidált táblázatba. Tesztelje a görgetőt, húzza a csúszkát a 11-es értékre. A makrónak meg kell kezdenie és szűrnie kell 11 rekordot a legjobb értékesítési ügyfelekről.
Két konszolidált táblázat szinkronizálása egy legördülő listával
Az 1. ábrán látható jelentés 13, két összefoglaló táblázatot tartalmaz. Mindegyikük rendelkezik olyan oldalmezővel, amely lehetővé teszi az értékesítési piac kiválasztását. A probléma az, hogy minden alkalommal, amikor egy pilonasztal oldal mezőjét választja, ugyanazt a piacot kell választania egy másik pivot asztal oldalán. A két asztal szűrőinek szinkronizálása az adatelemzési szakaszban nem jelent nagy problémát, de van esély arra, hogy Ön vagy ügyfelei még mindig elfelejtik.
Ábra. 13. Két összefoglaló táblázat tartalmazó oldal mezőket tartalmaz, amelyek az adatszűrést a piacokon végzik. A külön piac adatai elemzéséhez szinkronizálni kell mindkét összefoglaló táblázatokat.
Az összefoglaló táblázatok szinkronizálásának egyik módja a legördülő listát használja. Az ötlet a makrók rögzítése, amely kiválasztja a kívánt piacot a mezőből Értékesítési piac Mindkét asztalnál. Ezután meg kell hoznia egy legördülő listát, és töltse ki azt az értékesítési piacok nevét két összefoglaló táblázatból. Végül a rögzített makrót módosítani kell, hogy a legördülő listából az összes összefoglaló táblázatokat a legördülő listából szűrjék. A feladat megoldásához a következő lépéseket kell végrehajtania.
1. Hozzon létre egy új makrót, és hozzárendeljen egy nevet. Synchmarketek.. A bejegyzés elindításakor válassza ki a mezőt Mindkettő piaci értékesítése Összefoglaló táblázatok piaci értékesítés Kalifornia És állítsa le a makró rekordot.
2. Jelenítse meg az űrlapvezérlőket a képernyőn, és adja hozzá a képernyő legördülő listáját.
3. Hozzon létre egy konszolidált asztalpiacok mereven meghatározott listáját. Ne feledje, hogy a lista első eleme az értéket (minden) jelzi. Engedélyeznie kell ezt az elemet, ha szeretné kiválasztani az összes piacot a legördülő listában.
4. Ebben a szakaszban a konszolidált táblázat jelentése az 1. ábrán látható. tizennégy.
Ábra. 14. Az Ön rendelkezésére áll az összes szükséges pénzeszköz: egy makró a mező megváltoztatása Értékesítési piac Mindkét pivot asztal, a legördülő listát és a konszolidált táblázatban szereplő összes értékesítési piac listáját
5. Kattintson a jobb gombbal a legördülő listára, és válassza ki a parancsot a helyi menüben. Formátum objektumA vezérlés konfigurálása.
6. Először állítsa be a legördülő listák kitöltéséhez használt értéktartományt, amint azt az 1. ábra mutatja. 15. Ebben az esetben a (3) bekezdésben létrehozott értékesítési piacok listájáról beszélünk, adja meg azt a cellát, amely megjeleníti a kiválasztott elem sorszámát (ebben a példában a Cell H1). Paraméter Az ajakvonalak száma Meghatározza, hogy hány sorot fognak egyidejűleg tükrözni a legördülő listában. Kattintson a gombra rendben.
Ábra. 15. A legördülő listának beállításainak meg kell jelölniük az értékesítési piacok listáját, mint az értékek kezdeti tartományát, és kötési pontként - meghatározzuk a Cell H1
7. Most lehetősége van kiválasztani az értékesítési piacot a legördülő listában, valamint meghatározza a kapcsolódó szekvencia számot a H1 sejtben (16. ábra). A kérdés merül fel: Miért van az indexértéke a piac valódi neve helyett? Mivel a legördülő lista nem ad meg nevet, de a számot. Például, ha kiválasztja a kaliforniai név legördülő listáját a H1 cellában, megjelenik az érték 5. Ez azt jelenti, hogy a kalifornia a lista ötödik eleme.
Ábra. 16. A legördülő listát most a piacok nevével töltjük meg, és a kiválasztott piac kiválasztott száma megjelenik a H1-sejtben.
8. A szekvencia szám használata a piac nevének helyett, meg kell küldenie az index funkció használatával.
9. Adja meg az indexfunkciót, amely átalakítja a szekvencia számát a H1 cellából egyértelmű értékre.
10. Az index funkció két argumentumot vesz igénybe. Az első argumentum a lista értékek tartományát jelenti. A legtöbb esetben ugyanazt a tartalmat fogja használni, hogy a legördülő menü tele van. A második argumentum egy sorszám. Ha a szekvenciaszám be van írva a cellában (például a H1 cellában, a 17. ábrán), akkor egyszerűen utalhat erre a cellára.
Ábra. 17. Az i1 cellában szereplő függvényindex átalakítja a H1 sejtben tárolt szekvenciaszámot az értékre. Az I1 cellában értéket fog használni a makró megváltoztatásához
11. Makró szerkesztése Synchmarketek.Az I1 sejtben lévő érték használata mereven meghatározott érték helyett. Kattintson a lapra Fejlesztő és kattintson a gombra Makrók. Megjelenik a párbeszédpanel az 1. ábrán látható képernyőn. 18. Válassza ki a makrót Synchmarketek. és kattintson a Szerkesztés gombra.
Ábra. 18. A makró VBA kód eléréséhez válassza a Makró lehetőséget Synchmarketek. és kattintson változás
12. Makró felvételekor Kaliforniai értékesítési piacot választott a mezőből mindkét összefoglaló táblázatban Értékesítési piac. Amint az az 1. ábrán látható. 19, a California Market most mereven beállítja a makró VBA-kódját.
13. Helyezze vissza az ActiveSheet.Range expresszió ("i1") értékét. Valvalue, amely az I1-es sejt értékére utal. Ebben a szakaszban a makró kódnak úgy kell kinéznie, mint az 1. ábrán látható. 20. A makró megváltoztatása után zárja be a Visual Basic Editor-ot, és térjen vissza a táblázatkezelőhöz.
Ábra. 20. Helyezze vissza a "Kalifornia" értékét egy ActiveSheet.range kifejezéssel ("i1"). Valvalue és zárja be a Visual Basic Editor
14. Csak a makró végrehajtásának biztosítása a legördülő listában értékesítési piac kiválasztásakor. Kattintson a jobb gombbal a legördülő listára, és válassza ki a paramétert Makró hozzárendelése. Válassza ki a makrót Synchmarket. és kattintson a gombra rendben.
15. A konszolidált táblázatokban lévő oldal mezőkkel rendelkező sorok és oszlopok elrejtése, valamint a piacok listájának és a létrehozott index-formulák listája.
Ábrán. 21 Megmutatja a végeredményt. Olyan felhasználói felületet kapott, amely lehetővé teszi az ügyfelek számára, hogy az értékesítési piacot mindkét összefoglaló táblázatban egyetlen legördülő listával választsák ki.
Ha kiválasztja az új elemet a legördülő listában, az oszlopok mérete automatikusan megváltozik, hogy megfeleljen az összes megjelenített adat. A program hasonló viselkedése unatkozik a munkalap sablon formázásával. Megakadályozhatja, ha rákattint a jobb egérgomb összefoglalására, és válassza ki a parancsot Írta táblázatbeállítások. Megjelenik egy párbeszédpanel a képernyőn, amelyben vissza kívánja állítani a jelölőnégyzetet. Automatikusan módosítja az oszlopok szélességét frissítéskor.
A megjegyzést Jelen, Alexander könyv alapján írják. . 12. fejezet.
Az Excel eszközök használatával kiválaszthat egy bizonyos adatokat véletlenszerű tartományból, egy vagy több állapotból. Az ilyen problémák megoldásához a tömb vagy makrók képleteket vagy makrókat használnak. Fontolja meg a példákat.
Hogyan készítsünk egy mintát az Excel állapotban
A tömb forma használata esetén a kiválasztott adatok egy külön táblázatban jelennek meg. Mi a módszer előnye a szokásos szűrőhöz képest.
Forrás táblázat:
Először tanulj meg egy mintát egy számszerű kritériumon. A feladat az, hogy a 200 rubel feletti árat választja. A szűrés használata egy módja a megoldásnak. Ennek eredményeként csak azok a termékek, amelyek megfelelnek a lekérdezést, a forrás táblázatban marad.
Egy másik megoldás a tömb formula használata. A kapcsolódó lekérdezési sorokat külön jelentési táblázatba helyezik.
Először egy üres táblát hozunk létre az eredeti: duplikált fejlécek, sorok és oszlopok száma mellett. Az új táblázat az E1: G10.Tube tartományát foglalja el az E2: E10 (a "dátum" oszlop), és bemutatja a következő képletet: ( }.
A masszív képlet megszerzéséhez nyomja meg a Ctrl + Shift + Enter billentyűkombinációt. A következő oszlopban - "termék" - Hasonló, szilárd képletet vezetünk be: ( ). Csak a funkciófunkció első argumentuma megváltozott.
Az "Ár" oszlopban bemutatjuk a tömb azonos képletét az indexfunkció első argumentumának megváltoztatásával.
Ennek eredményeként jelentést kapunk az árukról, amely több mint 200 rubel.
Ez a minta dinamikus: ha megváltoztatja a kérést vagy megjelenést az új termékek forrás táblázatában, a jelentés automatikusan megváltozik.
2. feladatszám - Válasszon a forrás táblázatból Az áruk, amelyek az eladásra kerültek 09/20/2015. Ez az, hogy a kiválasztási kritérium a dátum. A kényelem érdekében bemutatjuk a kívánt dátumot egy külön cellára, I2-re.
A probléma megoldásához hasonló tömbformát használnak. Csak a kritérium helyett).
Az ilyen formulákat más oszlopokba (cm fenti) vezetik be.
Most használunk egy szövegkritériumot. Az I2-sejt dátumának helyett bemutatjuk a "1. termék" szöveget. Enyhén változtassa meg a tömb képletét: ( }.
Egy ilyen nagy mintavételi funkció az Excelben.
Mintavétel az Excel több feltételhez
Először is, vegyen két numerikus kritériumot:
A feladat az, hogy kiemeljük a 400-nál kisebb és több mint 200 rubel költségeket. Kombináljuk a "*" jelzést. A tömb formula a következő: ( }.!}
Ez a jelentés táblázat első oszlopára vonatkozik. A második és a harmadik esetében módosítsa az első argumentum funkció indexét. Eredmény:
Ahhoz, hogy több dátumban vagy numerikus kritériumban mintát készítsünk, hasonló szilárd képleteket használunk.
Véletlenszerű minta az Excelben
Amikor a felhasználó nagy mennyiségű adatmal működik, későbbi elemzés esetén véletlenszerű minta szükséges. Minden sor véletlenszerű számhoz rendelhető, majd alkalmazza a mintavételi rendezést.
Forrás adatkészlet:
Először két üres oszlopot helyezzen el a bal oldalon. Az A2-es sejtben ragasztóanyagot jelentünk (). Spin az egész oszlopon:
Most másoljon egy oszlopot véletlenszerű számokkal, és helyezze be egy V. oszlopba. Ez szükséges ahhoz, hogy ezek a számok ne változnak, amikor új adatokat készítenek a dokumentumba.
Az értékek, és nem a képlet elhelyezéséhez kattintson a jobb egérgombra az oszlopban, és válassza ki a "Speciális betét" eszközt. A megnyíló ablakban egy kullancsot helyezünk el az "Érték" elemtől:
Most meg lehet rendezni az adatokat az oszlopban növekvő vagy csökkenő. A kezdeti értékek képviseletére vonatkozó eljárás is megváltozik. Bármely számú sorot választunk felülről vagy alulról - véletlenszerű mintát kapunk.
Egyéb (39)
Excel hibák és hibák (3)
Hogyan juthat el az egyedi (nem ismételt) értékek listájának?
Képzelje el a különböző nevek nagy listáját, teljes nevét, asztalszámát stb. És meg kell hagyni az azonos nevek listáját ebből a listából, de nem ismételni - én. Törölje az összes duplikált rekordot ebből a listából. Máskülönben van: Hozzon létre egy listát az egyedi elemekről, a nem ismétlődő listáról, másolat nélkül. Ehhez többféle módon van: beágyazott excel eszközök, beépített képletek, és végül a kóddal Vizuális alapvető alkalmazás (VBA) és összefoglaló táblázatok. Ebben a cikkben fontolja meg az egyes lehetőségek mindegyikét.
Az Excel 2007 és a fenti beépített jellemzői
Az Excel 2007-ben és 2010-ben könnyebben egyszerűen csinálni - van egy speciális csapat, amelyet hívnak -. Ez a lapon található Adatok (adatok) alszakasz Adateszközök (adateszközök)
Használja ezt a parancsot. Jelölje ki az oszlopot (vagy több) Ezekkel az adatokkal, amelyekben törölni szeretné az ismétlődő bejegyzéseket. Menjen a fülre Adatok (adatok) -A duplikátumok eltávolítása (Duplikátumok eltávolítása).
Ha egy oszlopot választ, de több oszlopot (vagy legalább egy oszlopot) lehet, akkor az Excel kínálja a választást: a minta tartományának bővítése ezen az oszlopon, vagy hagyja el a választást, mivel csak a Dedikált tartomány. Fontos megjegyezni, hogy ha nem bővíti a tartományt, az adatok csak egy oszlopban megváltoznak, és a szomszédos oszlopban lévő adatok a legkisebb változások nélkül maradnak.
Az ablak megismétlődött eltávolítási paraméterekkel jelenik meg 
Tegye az oszlopokkal szembeni kullancsokat, az ismétlések, amelyekre szükség van, és kattintson az OK gombra. Ha az adatfejek a dedikált tartományban vannak, akkor jobbak a zászlót Az adatok tartalmaznak címsorokatvéletlenül nem törölheti az adatokat az asztalon (ha hirtelen teljesen egybeesnek a címértékkel).
1. módszer: Speciális szűrő
Az Excel 2003 esetében minden bonyolultabb. Nincs olyan eszköz, mint például Távolítsa el az ismétlődést. De van olyan csodálatos eszköz, mint Speciális szűrő. 2003-ban ez az eszköz megtalálható Adat -Szűrő -Speciális szűrő. Ennek a módszernek a szépsége, vele nem lehet elrontani a forrásadatokat, hanem létrehozhat egy listát egy másik tartományban. 2007-2010-ben Excel, ő is van, de egy kicsit edzett. A lapon található Adatok (adatok)Csoport Rendezés és szűrő (rendezés és szűrő) - Fejlett)
Használja ezt: Futtassa a megadott eszközt - Megjelenik a párbeszédpanel: 
- Kezelés: Választ Másolja az eredményt egy másik helyre (másoljon más helyre).
- Forrás tartomány: Válasszon egy tartományt az adatokkal (A mi esetünkben ez a1: A51).
- A kritériumok tartománya (kritériumok tartománya): Ebben az esetben üresen hagyjuk.
- Helyezze az eredményt a tartományba (másolás): Adja meg az első cellát a kimeneti adatokhoz - üres (A képen - E2).
- Tegyen egy kullancsot Csak egyedi rekordok (csak egyedi rekordok).
- Zhmem. rendben.
Jegyzet: Ha az eredményt egy másik lapra szeretné tenni, akkor csak adjon meg egy másik lapot, nem fog működni. Megadhat egy cellát egy másik lapon, de ... alas és ah ... Excel adnak üzenetet, hogy nem másolhatja az adatokat más lapokra. De ez megkerülhető, és nagyon egyszerű. Csak futni kell Speciális szűrő Attól a laptól, amelyre az eredményt szeretnénk elhelyezni. És mint forrásadatok, válasszuk az adatokat bármely lapról - megengedett.
Ön is nem eredményezheti az eredményt más sejtekbe, hanem az adatok szűrésére a helyszínen. Az ebből származó adatok nem lesznek hatással - ez lesz az adatok szokásos szűrése.
Ehhez csak ki kell választania a feldolgozási elemet Szűréslista a helyszínen (szűrjük a listát, a helyszínen).
2. módszer: Formulák
Ez a módszer nehezebb a tapasztalatlan felhasználók megértésében, de létrehozza az egyedi értékek listáját a kezdeti adatok megváltoztatása nélkül. Nos, dinamikusabb: ha megváltoztatja az adatokat a forrás táblázatban, az eredmény megváltozik. Néha hasznos. Megpróbálom megmagyarázni az ujjakról, hogy mit és mi: például az adatokkal ellátott lista az A1: A1: A51, ahol A1 egy fejléc). Lista megjelenítése A C2 cellával kezdődő oszlopban leszünk. A C2 képlet a következő:
(\u003d Index ($ 2: $ 2: $ 51, a legkisebb (ha (számlálva ($ C $ 1: C1, $ 2: $ 2: $ $ 51) \u003d 0; sor ($ 1 $: $ $ 50)); 1)))
(\u003d Index ($ 2 $: $ 51, kicsi (ha (Countif ($ C $ 1: C1, $ 2: $ 2: $ $ 51) \u003d 0; sor ($ 1 $: $ A $ 50))); 1)))
A képlet munkájának részletes elemzését a cikk tartalmazza:
Meg kell jegyezni, hogy ez a képlet egy tömb formula. Ez mondható a göndör zárójelekre, amelyekben ezt a képletet megkötötték. És ezt a képletet be kell vezetni a sejtkombinációba - Ctrl+Váltás.+BELÉP. Miután beléptünk a képlet C2-ben, át kell másolnunk, és több sorba kell másolnunk, hogy pontosan megjelenítsük az összes egyedi elemet. Amint az alsó sejtek képlete visszatér #SZÁM! - Ez azt jelenti, hogy az összes elem megjelenik, és a képlet alatt nincs értelme. Annak érdekében, hogy a hiba elkerülje és a képletet az univerzális (minden egyes alkalommal meghosszabbítás nélkül), használhat egy nem hangellenőrzést:
Az Excel 2007 és a fenti:
(\u003d Ha az index (index ($ 2 $ 2: $ 51, a legkisebb (ha (számlálva ($ C $ 1: C1, $ 2: $ 2: $ 51) \u003d 0; string ($ A $ 1: $ 50)); 1)); "")
(\u003d IFError (index ($ 2: $ 2: $ 51, kicsi (ha (Countif ($ C $ 1: C1, $ 2: $ 2: $ 51) \u003d 0; sor ($ 1: $ $ 50)); 1)); "")
Az Excel 2003 esetében:
(\u003d Ha (a legkisebb (ha (ha (ha (az ajánlat ($ c $ 1: c1, $ 2: $ 2: $ $ 51) \u003d 0; string ($ 1 $: $ 50))); 1) ; "", index ($ 2 $: $ 51, a legkisebb (ha ($ C $ 1: C1, $ 2: $ 2: $ $ 51) \u003d 0; sor ($ 1 $ 1: $ 50)); 1)))))))
(\u003d Ha (ISERR (ha (if (ha) ($ C $ 1: C1, $ 2: $ 2: $ $ 51) \u003d 0; sor ($ 1 $: $ $ 50)); 1)); ; index ($ 2: $ $ 51, kicsi (ha (Countif ($ C $ 1: C1, $ 2: $ 2: $ $ 51) \u003d 0; sor ($ 1 $: $ 50 )); 1))))
Ezután hiba helyett #SZÁM! (#Num!) Üres cellák lesz (nem teljesen üres, természetesen - a képletekkel :-)).
Egy kicsit a képlet képletének különbségeiről és árnyalatairól, és ha (Eosh elolvasható ebben a cikkben: mint egy olyan sejtben, amely a hiba show helyett egy képletet tartalmaz 0
3. módszer: VBA kód
Ez a megközelítés megköveteli a makrók engedélyét és az alapvető ismereteket. Ha nem biztos benne a tudásod, azt javaslom, hogy olvassa el ezeket a cikkeket:
- Mi a makró és hol keresni? A cikk csatolt video tutorial
- Mi a modul? Melyek a modulok? Meg kell érteni, hogy hol helyezze be az alábbi kódokat.
Mindkét kódot az alábbiakban kell elhelyezni szabvány modul. A makrókat megengedett.
A kezdeti adatok ugyanabban a sorrendben maradnak - az adatokkal rendelkező lista az "A" oszlopban található (A1: A51, ahol A1 - cím). Csak a lista visszavonása nem leszünk a C oszlopban, hanem az E2 oszlopban, az E2-cellával kezdődően:
Sub Extract_Unique () DIM VITEM, AVARR, LI LID REDIM AVARR (1-re Sors.Count, 1-től 1-ig) Új kollekció Hiba történt Továbbítás mindegyik Vitem tartományban ("A2", sejtek (sorok.Count, 1) .End (xlup)). Érték "(! Lang: cellák (sorok.Count, 1) .end (xlup) - meghatározza az utolsó töltött cellát az ADDD VITEM, CSTR oszlop (VITEM), ha err \u003d 0, akkor li \u003d li + 1: Avarr (Li, 1) \u003d Vitem mást: err.clear vég, ha a következő vége, ha Li, akkor .Resize (Li) .Value \u003d avarr végi sub
Ezzel a kóddal az egyedülálló nem csak egy oszlopból, hanem az oszlopok és vonalak bármely tartományából is kivonhat. Ha a karakterlánc helyett
Hatótávolság ("A2", Cells(Rows.Count, 1).End(xlUp)).Value !}
Adja meg a kiválasztást.Value, a kód kódjának eredménye az egyedülálló elemek listája az aktív lapon elosztott tartományból. Csak akkor jó lenne megváltoztatni a kimeneti cellát a változáshoz - Ehelyett
Tegye azt, amelybe nincs adat.
Megadhat egy adott tartományt is:
Tartomány ("C2", sejtek (sorok.Count, 3) .end (xlup)). Érték
Univerzális kód az egyedi értékek kiválasztásához
Az alábbi kód használható bármely tartományban. Elég elindítás, adja meg a csak nem ismétlődő (több oszlop kiosztása) és a cellát az eredmény kiadásához. A megadott sejtek megtekintésre kerülnek, csak az egyedi értékek kerülnek kiválasztásra (az üres cellákat kihagyják), és a kapott listát rögzítjük, a megadott cellával kezdődően.
| Sub Extract_Unique () Dim X, Avarr, Li, Li Long Dim Avvals Dim RVALS AS tartományban, RresultCell A HIBA VISSZATÉRÍTÉSE "Kérje meg a cellák címét az egyedi értékek kiválasztásához SET RVALS \u003d Application.inputbox ( "Adja meg az egyedi értékek mintavételének tartományát", "Adatkérés", "A2: A51", típusa: \u003d 8) Ha az RVALS semmi akkor "Ha megnyomja a Mégse gombot EXIT SUB VÉGE, ha "Ha csak egy cella van megadva - nincs értelme választani Ha rvals.count \u003d 1, akkor msgbox "Az egyedi értékek kiválasztásához több cellát kell megadnia", Vbinformáció, "www.syt" kilépés alvég, ha "Vágja ki az üres húrokat és oszlopokat a munkatartományon kívül Beállított rvals \u003d metszés (rvals, rvals.parent.edrange) "Ha csak a munkatartományon kívüli üres sejtek jelennek meg Ha az RVALS semmi, akkor msgbox "Nincs elég adat az értékek kiválasztásához", vbinformáció, "www.syt" kilépés alvég, ha avvalals \u003d rvals.value "Kérjen egy cellát az eredmény kiadásához SET RRESULTCELL \u003d Application.Inputbox ( "Adja meg a cellát a kiválasztott egyedi értékek beillesztéséhez", "Adatkérés", "E2", típusa: \u003d 8) Ha a rresultcell semmi akkor "Ha megnyomja a Mégse gombot EXIT SUB VÉGE, ha "Határozza meg a tömb maximális lehetséges dimenzióját az eredményhez Redim Avarr (1-hez a sors.Count, 1-től 1-ig) "A gyűjtő objektum (gyűjtemény) használata "Válasszon csak egyedi bejegyzéseket, "Mivel a gyűjtemények nem tartalmazhatnak ismételt értékeket Az új kollekció hiba esetén folytassa a következő X-t az AVVALS-ben, ha Len (CSTR (X)) "Az üres cellákat kihagyjuk .Add x, cstr (x) "Ha a hozzáadott elem már a gyűjteményben van - hiba lép fel "Ha nincs hibák - ez az érték még nem készült, - Add hozzá a kapott tömbhöz Ha err \u003d 0, akkor li \u003d li + 1 avarr (Li, 1) \u003d x mást "Győződjön meg róla, hogy tisztítja a hibaobjektumot Err.clear vég, ha véget ér, ha a következő vége van "Jegyezze fel az eredményt a lapon, kezdve a megadott cellával Ha Li, akkor a rresultcell.Cells (1, 1) .Resize (Li) .value \u003d AVARr End Sub |
Sub Extract_Unique () Dim X, AVARR, LI, LI LI hosszú dim Avvals Dim RVALS AS RANGE, RRESULTCELL A HIBA VISSZATÉRÍTÉSE A KÖVETKEZŐ "Kérje meg a cellák címét az egyedülálló készlet RVALS \u003d Application.inputbox (" Adja meg a sejtek tartományát Az egyedi értékek mintavételéhez "," Adatkérés "," A2: A51 ", Típus: \u003d 8) Ha az RVALS semmi akkor", ha a kilépési sub véget, ha a gomb megnyomása megnyomja ", ha csak egy cella van megadva - Nincs értelme választani, ha rvals.count \u003d 1, akkor msgbox "Az egyedi értékek kiválasztásához több cellát kell megadnia, mint egy cellát", vbinformáció, "www.sight" kilépési subvég, ha "üres karakterláncokat és oszlopokat énekel a működési tartományon kívül Állítsa be az rvals \u003d metersect (rvals, rvals.parent.edredrange) "Ha csak üres cellákat jeleznek, akkor az IF-es, ha az RVALS semmi, akkor msgbox" nincs elég adat az értékek kiválasztásához ", vbinformáció, www..value (! Cell a kimeneti készlet RResultcell \u003d Application.inputbox ("Укажите ячейку для вставки отобранных уникальных значений", "Запрос данных", "E2", Type:=8) If rResultCell Is Nothing Then "если нажата кнопка Отмена Exit Sub End If "определяем максимально возможную размерность массива для результата ReDim avArr(1 To Rows.Count, 1 To 1) "при помощи объекта Коллекции(Collection) "отбираем только уникальные записи, "т.к. Коллекции не могут содержать повторяющиеся значения With New Collection On Error Resume Next For Each x In avVals If Len(CStr(x)) Then "пропускаем пустые ячейки.Add x, CStr(x) "если добавляемый элемент уже есть в Коллекции - возникнет ошибка "если же ошибки нет - такое значение еще не внесено, "добавляем в результирующий массив If Err = 0 Then li = li + 1 avArr(li, 1) = x Else "обязательно очищаем объект Ошибки Err.Clear End If End If Next End With "записываем результат на лист, начиная с указанной ячейки If li Then rResultCell.Cells(1, 1).Resize(li).Value = avArr End Sub!}
4. módszer: Összefoglaló táblázatok
Számos nem szabványos módszer az egyedi értékek kivonására.
- Válasszon ki egy vagy több oszlopot az asztalon, menjen a fülre Beillesztés (betét) -Csoport Táblázat (táblázat) -Összefoglaló tábla (pivottable)
- A párbeszédpanelen CREATE PIVOTTABLE CREATE TÁBLÁZAT Ellenőrizze az adattartomány helyességét kiemelve (vagy telepít egy új adatforrást)
- adja meg a konszolidált táblázat elhelyezését:
- Új lapon (új munkalap)
- Meglévő lapon (meglévő munkalap)
- erősítse meg egy gomb létrehozását a gomb megnyomásával RENDBEN.
Mivel Összefoglaló táblázatok A sorok vagy oszlopok területén elhelyezett adatok feldolgozása során csak egyedi értékeket vesznek tőlük későbbi elemzésre, nem szükséges az USA-tól, kivéve az összefoglaló táblázatot, és tegye a a kívánt oszlop adatai a string területre vagy oszlopokra.
A cikkhez csatolt fájl példáján:
Mi a kényelmetlenség a konszolidált munkavégzés ebben az esetben: A forrásadatok megváltoztatásakor a konszolidált táblázatot manuálisan kell frissíteni: a konszolidált táblázat bármely celláját - az egérgomb alatt - Frissítés) vagy fül Adatok (adatok) -Update All (Frissítés ALL) -Frissítés). És ha a forrásadatok dinamikusan és rosszabbra frissülnek - szükség lesz a forrásadat tartomány újbóli megjelenítésére. És még egy mínusz - a konszolidált táblázatban lévő adatok nem módosíthatók. Ezért, ha a jövőben a kapott listával kell dolgoznia, akkor a kívánt lista létrehozása után másolnia kell a súgó segítségével, és helyezze be a kívánt lapot.
Annak érdekében, hogy jobban megértsem az összes cselekvést, és megtanulják, hogyan kell kezelni a konszolidált táblázatokat, határozottan javaslom a cikk olvasását az összefoglaló táblázatokról - a videó bemutatója van, amelyben vizuálisan bemutatom az egyszerűségét és kényelmét a főbb lehetőségekkel Konszolidált táblázatok.
Az alkalmazott példában a leírt technikák mellett az egyedi elemek képletének és kódjának extrakciójának enyhén összetettebb eltérése van rögzítve, nevezetesen: egyedi elemek eltávolítása kritériumok szerint. Mi a kérdés: ha az utolsó név egy oszlopában, és a második (c) bizonyos adatok (a fájlban hónapokban), és az oszlop egyedi értékeit csak a kiválasztott vezetéknévre kell kivonni . A hasonló extrakciós egyedi példák egy lapon találhatók. Kiterjesztés kritériummal.
Letöltés példa:
(108.0 Kib, 14 152 letöltés)
A cikk segített? Ossza meg egy linket a barátaival! Videó leckék
