Ehhez meg kell nyitnia a szerkesztőt Visual Basic Szerkesztő ("Alt+F11" billentyűkombináció, vagy kattintson a jobb gombbal bármelyik lap parancsikonjára, és válassza a "Forrásszöveg" lehetőséget, vagy a "Fejlesztő" menü "Kód" csoportjában kattintson a "Visual Basic" elemre) és illesszen be egy szabványos modult a projektbe. És adjunk hozzá két programkódot ehhez a modulhoz (lásd 8. ábra) - és .

Hogyan lehet optimalizálni több keresési érték kiválasztását különböző táblázatokból az Excelben

Több egyedi beszállító összegeiről és fizetési feltételeiről szóló beszámolók készítésekor gyakran jelentős mennyiségű adat (táblázat) átvizsgálása szükséges, mielőtt megtalálná a keresett információt.

Több kívánt érték (különböző beszállítók fizetési összege) kiválasztását egy paraméter szerint (például dátum szerint) optimalizálhatja kis számú táblázatból, majdnem ugyanazzal a sémával, mint azzal a különbséggel, hogy a „Könyv tartalomjegyzéke” lapon megjelenik a kiválasztásban részt vevő táblák neveinek listája és annak eredményei, valamint egy kicsit eltérő táblázatkereső képlet:

"=VLOOKUP($C$1,INDIRECT(A2),2,FALSE)", ahol:

• C1 cella (negyedszám) – beállítja a paraméter értékét;
• „INDIRECT(A2)” – szöveges hivatkozást határoz meg egy elnevezett tartományra, amelynek neve az A2 cellában van;
• A „2” a beszállítók forrástáblázatának oszlopszáma, amely tartalmazza a számunkra szükséges fizetési összegeket;
• „FALSE” (0-val helyettesíthető) – jelzi a VLOOKUP funkciónak, hogy pontos egyezésre van szükség.

A kiválasztási lekérdezés lényege, hogy a forrástáblából olyan sorokat jelölünk ki, amelyek megfelelnek bizonyos feltételeknek (hasonlóan a szabványos használatához). Válasszunk értékeket a forrástáblázatból a segítségével. A használattal ellentétben ( CTRL+SHIFT+L vagy Adatok/ Rendezés és szűrés/ Szűrés) a kijelölt sorok egy külön táblázatba kerülnek.

Ebben a cikkben a leggyakoribb lekérdezéseket tekintjük át, például: olyan táblázatsorok kiválasztása, amelyek értéke egy numerikus oszlopból egy meghatározott tartományba (intervallum) esik; olyan sorok kiválasztása, amelyek dátuma egy adott időszakhoz tartozik; feladatok 2 szöveges feltétellel és mások. Kezdjük egyszerű kérdésekkel.

1. Egy numerikus kritérium (Válassza ki azokat a termékeket, amelyek ára magasabb a minimumnál)

példafájl, lap Egy kritérium - szám ).

A Forrás táblából csak azokat a rekordokat (sorokat) szükséges külön táblázatban megjeleníteni, amelyek ára 25-nél magasabb.

Ezt és az azt követő problémákat egyszerűen megoldhatja a segítségével. Ehhez válassza ki a Forrás tábla fejléceit, és kattintson a gombra CTRL+SHIFT+L. Válassza az Árak fejléc melletti legördülő listából Numerikus szűrők..., majd állítsa be a szükséges szűrési feltételeket, és kattintson az OK gombra.

Megjelennek a kiválasztási feltételeknek megfelelő rekordok.

Egy másik megközelítés a . Ezzel szemben a kijelölt sorok egy külön táblázatba kerülnek - egy egyedibe, amely például a Forrás táblától eltérő stílusban formázható, vagy egyéb módosításokat hajthat végre.

kritérium ( minimális ár) egy cellába kerül E6 , táblázat a szűrt adatokhoz - tartományban D10:E19 .

Most válasszuk ki a tartományt D11:D19 (Termék oszlop), és írja be:

INDEX(A11:A19;
SMALL(IF($E$6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-SOR (10 USD B USD))

Ahelyett BELÉP nyomja meg a billentyűparancsot CTRL+SHIFT+ENTER(a tömbképlet a következő lesz).

E11:E19 (Ár oszlop), ahol hasonlót írunk be:

INDEX(B11:B19;
SMALL(IF($E$6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-SOR (10 USD B USD))

Ennek eredményeként egy új táblázatot kapunk, amely csak azokat a termékeket tartalmazza, amelyek ára nem alacsonyabb, mint a cellában feltüntetett E6 .

A beérkezett Mintakérés dinamizmusának bemutatására belépünk E6 érték 55. Csak 2 rekord fog szerepelni az új táblázatban.

Ha a Forrás táblához új terméket ad hozzá 80-as árral, akkor a rendszer automatikusan új rekordot vesz fel az új táblába.

jegyzet. A és a szűrt adatok megjelenítésére is használható. Egy adott eszköz kiválasztása a felhasználó előtt álló feladattól függ.

Ha nem kényelmes használni tömbképlet, amely több értéket ad vissza, akkor használhat egy másik megközelítést is, amelyet az alábbi szakaszokban tárgyalunk: 5.a, 7, 10 és 11. Ezekben az esetekben a .

2. Két numerikus kritérium (Válassza ki azokat a termékeket, amelyek ára a tartományba esik)

Legyen egy kezdeti táblázat a termékek és árak listájával (lásd. példafájl, lapSzámtartomány).

A kritériumokat (alsó és felső árhatár) a tartományba helyezzük E5:E6 .

Azok. ha a Termék ár a megadott intervallumon belülre esik, akkor az új Szűrt adatok táblában ilyen rekord jelenik meg.

Az előző feladattól eltérően kettőt fogunk létrehozni: Termékek és Árak (ezek nélkül is megtehetők, de képletek írásakor kényelmesek). A megfelelő képleteknek így kell kinézniük a Névkezelőben ( Képletek/ Meghatározott nevek/ Névkezelő) az alábbiak szerint (lásd az alábbi ábrát).

Most válasszuk ki a tartományt D11:D19 és beírjuk:

INDEX(Termékek;
LEGKEVÉSBÉ(
IF(($E$5<=Цены)*($E$6>=Árak);ROW(Árak);"");

Ahelyett BELÉP nyomja meg a billentyűparancsot CTRL+SHIFT+ENTER.

Ugyanezeket a manipulációkat hajtjuk végre a tartománnyal E11:E19 ahol bemutatunk egy hasonlót:

INDEX(árak;
LEGKEVÉSBÉ(
IF(($E$5<=Цены)*($E$6>=Árak);ROW(Árak);"");
SOR(Árak)-SORO($B$10))-SORO($B$10))

Ennek eredményeként egy új táblázatot kapunk, amely csak azokat a termékeket tartalmazza, amelyek ára a cellákban megadott intervallumon belülre esik E5 És E6 .

A beérkezett Jelentés (mintavételezési kérelem) dinamizmusának bemutatására beírjuk E6 érték 65. A Source táblából egy másik, az új feltételnek megfelelő rekord hozzáadódik az új táblához.

Ha 25 és 65 közötti árral rendelkező új terméket ad a Forrás táblához, akkor új rekord kerül hozzáadásra az új táblához.

A példafájl hibakezelésű tömbképleteket is tartalmaz, ha az Ár oszlop hibaértéket tartalmaz, például #DIV/0! (lásd a lapot Hiba a feldolgozásban).

A következő problémákat hasonló módon oldjuk meg, ezért nem foglalkozunk velük ilyen részletesen.

3. Egy feltétel Dátum (Válassza ki azokat a termékeket, amelyek szállítási dátuma megegyezik a megadott dátummal)

példafájl, lapEgy kritérium – dátum).

A sorok kiválasztásához az 1. feladathoz hasonló tömbképleteket használunk (a kritérium helyett<= используется =):

=INDEX(A12:A20,SMALL(HA($E$6=B12:B20,ROW(B12:B20),"");ROW(B12:B20)-ROW($B$11))-SORA($B$11) )

INDEX(B12:B20,KIS(HA($E$6=B12:B20,ROW(B12:B20),"");ROW(B12:B20)-SORO($B$11))-SORA($B$11) )

4. Két dátum kritérium (Válassza ki azokat a termékeket, amelyek szállítási dátuma a tartományba esik)

Legyen egy Forrás táblázat a termékek listájával és a szállítási dátumokkal (lásd. példafájl, lapIdőintervallum).

Felhívjuk figyelmét, hogy a Dátum oszlop NINCS RENDEZÉS.

Megoldás 1: Te tudod használni .

Írja be a cellába D12 tömbképlet:

INDEX(A$12:A$20;
LEGNAGYOBB((6 $E$<=$B$12:$B$20)*($E$7>=$B$12:$B$20)*(ROW($B$12:$B$20)-ROW($B$11));
$J$12-SORO(A12)+SORA($B$11)+1))

jegyzet: A képlet beírása után az ENTER billentyű helyett a CTRL+SHIFT+ENTER billentyűkombinációt kell lenyomni. Ez a billentyűparancs tömbképletek bevitelére szolgál.

Másolja le a tömbképletet a kívánt számú cellára. A képlet csak azokat az értékeket adja vissza a Termékekhez, amelyeket a megadott dátumtartományon belül szállítottak. A fennmaradó cellák #NUM! hibát tartalmaznak. Hibák benne példafájl (4. lap. Dátumtartomány) .

Hasonló képletet kell megadni a dátumokhoz az E oszlopban.

Egy cellában J12 Kiszámítja a forrástábla kritériumoknak megfelelő sorainak számát:

COUNTIFS(B12:B20;">="&$E$6;B12:B20;"<="&$E$7)

A forrástábla azon sorai, amelyek megfelelnek a feltételeknek: .

Megoldás 2: A sorok kijelöléséhez a 2. feladathoz hasonló tömbképleteket használhat (azaz):

=INDEX(A12:A20,SMALL(IF(($E$6<=B12:B20)*($E$7>=B12:B20);SORA(B12:B20);"");SORA(B12:B20)-SORA($B$11))-SORA($B$11))

INDEX(B12:B20,SMALL(IF(($E$6<=B12:B20)*($E$7>=B12:B20);SORA(B12:B20);"");SORA(B12:B20)-SORA($B$11))-SORA($B$11))

Az első képlet megadásához válassza ki a cellák tartományát G12:G20 . A képlet beírása után az ENTER billentyű helyett a CTRL+SHIFT+ENTER billentyűkombinációt kell megnyomni.

Megoldás 3: Ha a Dátum oszlop SORTED, akkor nem kell tömbképleteket használnia.

Először ki kell számítania a kritériumoknak megfelelő sorok első és utolsó pozícióját. Ezután adja ki a sorokat.

Ez a példa ismét világosan bemutatja, milyen könnyű képleteket írni.

5. Egy kritérium Dátum (Válassza ki azokat a termékeket, amelyek szállítási dátuma nem korábbi/legkésőbb a megadottnál)

Legyen egy Forrás táblázat a termékek listájával és a szállítási dátumokkal (lásd. példafájl, lap Egy feltétel – dátum (nem később) ).

Olyan sorok kiválasztásához, amelyek dátuma nem korábbi (beleértve magát a dátumot is), egy tömbképletet használunk:

=INDEX(A12:A20,SMALL(IF($E$7<=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))

Szintén a példafájlban találhatók a feltételek képletei: Nem előtte (nem beleértve); Nem később (beleértve); Nem később (nem beleértve).

7. Egy szöveges feltétel (egy bizonyos típusú termékek kiválasztása)

Legyen egy kezdeti táblázat a termékek és árak listájával (lásd. példafájl, lapEgy kritérium - Szöveg).

8. Két szöveges feltétel (Válasszon ki egy bizonyos típusú termékeket, amelyeket egy adott hónapban szállítanak ki)

Legyen egy kezdeti táblázat a termékek és árak listájával (lásd. példafájl, lap 2 kritérium - szöveg (I) ).

INDEX($11:$19;
SMALL(IF(($F$6=$11:$A$19)*($F$7=$B$11:$B$19);ROW($11:$A$19)-ROW($A$10); 30);ROW(INDIRECT("A1:A"&ROW($11:$A$19)))))

Kifejezés ($F$6=$A$11:$A$19)*($F$7=$B$11:$B$19) mindkét feltételt megadja (Termék és Hónap).

Kifejezés ROW(INDIRECT("A1:A"&ROW($A$11:$A$19))) formák (1:2:3:4:5:6:7:8:9), azaz. sorszámok a táblázatban.

9. Két szöveges kritérium (bizonyos típusú termékek)

Legyen egy kezdeti táblázat a termékek és árak listájával (lásd. példafájl, lap2 feltétel – szöveg (VAGY)).

A 7. feladattól eltérően 2 típusú árut tartalmazó sorokat fogunk kiválasztani ().

A sorok kiválasztásához tömbképletet használnak:

INDEX(A$11:A$19;
NAGY(((6 USD = 11 USD: 19 USD)+(7 USD = 11 USD: 19 USD))*(SORA (11 USD: 19 USD) - SOR(10 USD) ); COUNTIF($A$11:$A$19,$E$6)+COUNTIF($A$11:$A$19,$E$7)-SORA($11:A11)+1))

Feltétel ($E$6=$11:$A$19)+($E$7=$11:$19) garantálja, hogy a sárga cellákból (Termék2 és Termék3) csak a megadott típusú termékek kerülnek kiválasztásra. A + jel (kiegészítés) a megadásra szolgál (legalább 1 feltételnek teljesülnie kell).

A fenti kifejezés a tömböt adja vissza (0:0:0:0:1:1:1:0:0). Megszorozva a kifejezéssel SOR (11 USD: 19 USD) – SOR (10 USD), azaz -hoz (1:2:3:4:5:6:7:8:9), olyan pozíciók tömbjét kapjuk (táblasorszámok), amelyek megfelelnek a feltételeknek. Esetünkben ez egy tömb lesz (0:0:0:0:5:6:7:0:0).

Példaként a következő problémára adunk megoldásokat: Válassza ki azokat a termékeket, amelyek ára egy bizonyos tartományba esik, és meghatározott számú vagy többször ismétlődik.

Kezdőnek vegyük az áruszállítmányok táblázatát.

Tegyük fel, hogy érdekel minket, hogy hány és milyen tételeket szállítottak 1000 rubel áron. legfeljebb 2000 rubel. (1. kritérium). Ezenkívül legalább 3 azonos árú tételnek kell lennie (2. kritérium).

A megoldás egy tömbképlet:

KIS(SOR($14:$27)*($14 C$:$27>=$B$7)*($14 C$:$27<=$C$7)*($D$14:$D$27>=$B$10);F14+($G$8-$G$9))

Ez a képlet azokat a sorszámokat adja vissza, amelyek mindkét feltételnek megfelelnek.

Képlet =SZUMTERMÉK(($14:$27>=$7)*($14:$27<=$C$7)*($D$14:$D$27>= $10)) számolja a feltételeknek megfelelő sorok számát.

11. Használja a feltétel értékét (Bármelyik) vagy (Mind)

BAN BEN példafájl a "11. Kritérium Bármelyik vagy (Összes)" lapon A kritérium ezen változata megvalósult.

A képletnek ebben az esetben tartalmaznia kell az IF() függvényt. Ha az (Összes) érték van kiválasztva, akkor a rendszer egy képletet használ az értékek megjelenítésére anélkül, hogy ezt a kritériumot figyelembe vennék. Ha bármilyen más értéket választ, a feltétel a szokásos módon működik.

IF($C$8="(Mind)";
KIS((SORO($B$13:$B$26)-ROW($B$12))*($D$13:$D$26>=$D$8);F13+($G$6-$G$7));
KIS((SORO($B$13:$B$26)-SOR($B$12))*($D$13:$D$26>=$D$8)*($C$13:$C$26=$8C$) ;F13+($G$6-$G$7)))

A képlet többi része hasonló a fent leírtakhoz.

Adatmintavétel

Hozzon létre egy jelentést az 5. munkalap mintájáról a „Minőségi teljesítmény, százalék” oszlop használatával. (a 8. lap 7. táblázatából)

Az adatok mintavételéhez a következő lépéseket kell végrehajtania:

Határozza meg egy új tömb elemeinek számát egy adott feltételnek megfelelően változó bevitelével az InputBox operátor segítségével

Új tömb deklarálása és újradeklarálása

Hozzon létre egy új tömböt. Ehhez be kell állítani az új tömb első elemének számát u=1-re. Ezután egy ciklus végrehajtásra kerül, amelyben a mintavételi feltételt a „Minőségi teljesítmény, százalék” oszlopba írjuk. Ha a teszt eredménye igaz, akkor az elemzett tömb eleme az új tömb elemévé válik.

Visszavonás új elem a 8. lapon

Aljelentés kiválasztása()

Lapok ("Sheet8"). Válassza ki

Dim A() Változatként

Redim A (1 - n1, 1 - m)

VVOD "Sheet5", A, n1, m, 4

C = InputBox ("Adja meg a feltételt")

Lapok ("Sheet8"). Cellák (5, 11) = C

Ha i = 1 - n1

Ha A(i,

8) >

d = d + 1

Lapok ("Sheet8"). Sejtek (5,10) = d

Dim B() Változatként

Redim B (1-től d-ig, 1-től m-ig)

Ha i = 1 - n1

Ha A(i,

8) > Lapok („Sheet8”). Cells (5,11) Akkor

Ha j = 1 - m

B(u, j) = A(i, j)

u = u + 1

Ha i = 1-től d-ig

Ha j = 1 - m

Lapok ("Sheet8"). Cellák (i + 4, j) = B (i, j)

6. ábra. Táblázat adatok lekérés után

Automatikus makró létrehozása kijelölés alapján

Makrórögzítés engedélyezése. Eszközök>Makró>Rögzítés indítása>OK. Megjelenik egy négyzet egy gombbal a felvétel leállításához. Az 5. munkalapon (jelentés) válassza ki a táblázatot fejlécek és összegek nélkül, majd másolja a 10. munkalapra (automatikus kiválasztás). Válassza ki a fejléc nélküli táblázatot, majd a menüpontban válassza az Adat>Szűrő>Automatikus szűrés>feltétel kiválasztása>OK menüpontot. Jelöljük az oszlopot, amely szerint rendezni fogunk. Befejezzük a makrót.

Sub Macro2Selection()

" Macro2Selection makró

Lapok ("Sheet5"). Válassza ki

Kiválasztás. Másolat

Lapok ("9. lap"). Válassza ki

ActiveSheet. Paszta

Tartomány ("H5:H17"). Válassza ki

Alkalmazás. CutCopyMode = Hamis

Kiválasztás. Automatikus szűrő

ActiveSheet. Tartomány ("$H$5: $H$17"). Automatikus szűrő mező: =1, Feltétel1: =">80", _

Operátor: =xlAnd

Tartomány ("G22"). Válassza ki

7. ábra. Táblázatadatok az automatikus lekérés után

A maximális és minimális érték meghatározása

Határozza meg a maximális és minimális értékeket az „Össz”, „Abszolút teljesítmény, százalék”, „Minőségi teljesítmény” oszlopokban (9. táblázat, 10. lap)

A maximális és minimális értékek meghatározásához a következő lépéseket kell végrehajtania:

Állítson be egy referenciaváltozót, amely az aktuális minimum (maximum) lesz

A gyűjtemény minden elemét sorra összehasonlítjuk az aktuális minimummal (maximummal), és ha ez az elem nem felel meg a keresési feltételeknek (minimális esetén nagyobb, maximum esetén kisebb), akkor a szabvány értékét az összehasonlítandó elem értékére cseréljük

Az összes elem teljes áttekintése után az aktuális minimum (maximum) változó megtalálja a tényleges minimumot (maximum)

A minimális (maximális) érték a megfelelő cellákban jelenik meg

Subminmax()

Dim A() Változatként

n1 = Lapok ("Lap4"). Cellák (5.12)

m = Lapok ("Lap2"). Cellák (5.12)

Redim A (1 - n1, 1 - m)

VVOD "Sheet5", A, n1, m, 4

VIVOD "10. lap", A, n1, m, 4

VVOD "10. lap", A, n1, m, 4

j = 3 Tom esetén

maxA = 0,00001

minA = 1000000

Ha i = 1 - n1

Ha A (i, j) > maxA Akkor

maxA = A (i, j)

Ha A (i, j)< minA Then

minA = A (i, j)

Lapok ("10. lap"). Cellák (i + 4 + 2, j) = maxA

Lapok ("10. lap"). Sejtek (i + 4 + 3, j) = minA

Első módszer: Speciális automatikus szűrő használata

Tovább Excel lap ki kell választani egy területet a mintavételre szoruló adatok közül. A „Főoldal” lapon kattintson a „Rendezés és szűrés” lehetőségre (a „Szerkesztés” beállítási blokkban található). Ezután kattintson a szűrőre.

Megteheti másként is: a terület kiválasztása után lépjen az „Adatok” fülre, és kattintson a „Rendezés és szűrés” csoportban található „Szűrő” gombra.

A lépések végrehajtása után ikonoknak kell megjelenniük a táblázat fejlécében a szűrés megkezdéséhez. A cellák jobb szélén kis háromszögek formájában jelennek meg. Kattintson erre az ikonra annak az oszlopnak az elején, amelyiket kiválasztani kívánja. Indítsa el a menüt, lépjen a „Szövegszűrők” elemre, és válassza az „Egyéni szűrő...” lehetőséget.

Az Egyéni szűrés ablakot most aktiválni kell. Ebben állíthatja be azt a korlátozást, amellyel a kijelölés végrehajtásra kerül. Öt javasolt feltételtípus közül választhat: egyenlő, nem egyenlő, nagyobb, mint, nagyobb vagy egyenlő, kisebb, mint.

Szűrés után csak azok a sorok maradnak, amelyekben a bevétel összege meghaladja a 10 000-et (például).

Ugyanebben az oszlopban felvehet egy második feltételt is. Ismét vissza kell térnie az egyéni szűrési ablakhoz, és be kell állítania egy másik kiválasztási korlátot annak alsó részén. Állítsa a kapcsolót „Kevesebb” állásba, és írja be a „15000” értéket a jobb oldali mezőbe.

A táblázat csak azokat a sorokat tartalmazza, amelyekben a bevétel összege nem kevesebb, mint 10 000, de legfeljebb 15 000.

Más oszlopokban a kijelölés hasonló módon van konfigurálva. A kívánt oszlopban kattintson a szűrő ikonra, majd egymás után kattintson a „Szűrés dátum szerint” és az „Egyéni szűrő” listaelemekre.

Meg kell nyílnia az Egyéni automatikus szűrő ablaknak. Például válasszon eredményeket a táblázatban 2016. május 4. és május 6. között. Kattintson az „Utána vagy egyenlő” lehetőségre, és a jobb oldali mezőbe írja be a „2016.05.04.” értéket. Az alsó blokkban állítsa a kapcsolót „Előtte vagy egyenlő” állásba, és a jobb oldali mezőbe írja be a „2016.05.06. Hagyja a feltétel-kompatibilitás kapcsolót az alapértelmezett állásban, azaz „ÉS” állásban. A szűrés alkalmazásához kattintson az OK gombra.

A listát most még tovább kell szűkíteni, mert csak azok a sorok maradnak meg, amelyekben a bevétel összege 10 000 és 15 000 között változik, és ez a 2016. 04. 05. és 05. 06. közötti időszakra vonatkozik.

Az egyik oszlopban szükség esetén visszaállíthatja a szűrést. Ezt megteheti például a bevételi értékek esetében. Kattintson az automatikus szűrő ikonra a megfelelő oszlopban. Válassza a "Szűrő eltávolítása" lehetőséget.

A bevétel összege szerinti kiválasztás letiltásra kerül, és csak a dátum szerinti választás marad meg (2016.04.05. és 2016.06.05. között).

A táblázatban van egy másik oszlop, melynek neve „Név”. Szöveges formátumú adatokat tartalmaz. Ezen értékek felhasználásával mintát is képezhet. Az oszlop nevében kattintson a szűrő ikonra. Lépjen a „Szövegszűrők”, majd az „Egyéni szűrő...” elemre.

Ismét megnyílik az egyéni szűrőablak, amelyben kiválaszthat például „Hús” és „Krumpli” néven. Az első blokkban a kapcsolót „Egyenlő” állásba kell állítani, és a tőle jobbra lévő mezőbe be kell írni a „burgonya” kifejezést. Állítsa az alsó blokk kapcsolóját „Egyenlő” állásba, a szemközti mezőben pedig „Hús”. Most állítsa a feltételkompatibilitás kapcsolót „VAGY” állásba. Kattintson az OK gombra.

Az új mintában a korlátozások dátum szerint (2016. 04. 05-től 2016. 05. 05-ig) és név szerint (Hús és burgonya) vannak meghatározva. Csak a bevétel összegére nincs korlátozás.

A szűrőt teljesen eltávolíthatja, és ez ugyanazokkal a módszerekkel történik, mint a beállításnál. A szűrés visszaállításához az „Adatok” lapon kattintson a „Szűrő” elemre a „Rendezés és szűrés” csoportban.

A második lehetőségben lépjen a „Kezdőlap” fülre, és kattintson a „Rendezés és szűrés” elemre a „Szerkesztés” részben. Ezután kattintson a „Szűrő” gombra.

Ha ezen módszerek bármelyikét használja, a táblázat törlődik, és a kiválasztási eredmények törlődnek. Vagyis a táblázat megjeleníti az összes korábban bevitt adatot.

Második módszer: Tömbképlet használata

Ugyanazon az Excel-lapon hozzon létre egy üres táblázatot, amelynek fejlécében ugyanazok az oszlopnevek szerepelnek, mint a forrásban.

Az első oszlopban minden üres cellát ki kell jelölni új asztal. Helyezze a kurzort a képletsorba a képlet megadásához - =INDEX(A2:A29,SMALL(IF(15000

A képlet alkalmazásához nyomja meg a Ctrl+Shift+Enter billentyűket.

Jelölje ki a második oszlopot a dátumokkal, és helyezze a kurzort a képletsorba, és írja be: - =INDEX(B2:B29,SMALL(IF(15000

Ugyanígy írja be a következő képletet a bevétel oszlopba - =INDEX(C2:C29,SMALL(IF(15000

Megnyílik egy formázási ablak, amelyben ki kell választania a „Szám” lapot. A „Számformátumok” részben válassza a „Dátum” lehetőséget. Az ablak jobb oldalán kívánság szerint kiválaszthatja a megjelenített dátum típusát, majd az összes beállítás után kattintson az OK gombra.

Most minden szép lesz, és a dátum helyesen jelenik meg. Ha a cellákban a „#SZÁM!” érték jelenik meg, akkor feltételes formázást kell alkalmaznia. A táblázat összes celláját ki kell jelölni (a fejléc kivételével), és a „Kezdőlap” fülön kattintson a „Feltételes formázás” lehetőségre (a „Stílusok” eszközblokkban). Megjelenik egy lista, amelyben ki kell választania a „Szabály létrehozása...” lehetőséget.

Válassza ki a „Csak tartalmazó cellák formázása” szabályokat, és az első mezőben, amely a „Csak olyan cellák formázása, amelyeknél a következő feltétel teljesül” sor alatt található, válassza a „Hibák” lehetőséget, majd kattintson a „Formázás...” gombra.

Megnyílik egy formázási ablak, amelyben lépjen a „Betűtípus” elemre, és válassza ki a fehér színt. Kattintson az OK gombra.

A megadott korlátozásnak megfelelő kész minta jelenik meg Ön előtt, és mindez egy külön táblázatban lesz.

Harmadik módszer: Mintavétel több feltétel alapján egy képlet segítségével

A minta peremfeltételeit külön oszlopban kell megadni.

Egyenként jelölje ki az új táblázat üres oszlopait, hogy beírja a szükséges három képletet. Az első oszlopba írja be: - =INDEX(A2:A29,SMALL(IF(($D$2=C2:C29),ROW(C2:C29);"");ROW(C2:C29)-ROW($C$1 ) )-ROW($C$1)). Ezután írja be ugyanazokat a képleteket az oszlopokba, csak az INDEX operátor neve utáni koordinátákat módosítsa azokra, amelyek szükségesek és megfelelnek bizonyos oszlopoknak. Minden hasonló az előző módszerhez. Minden alkalommal, amikor belép, ne felejtse el lenyomni a Ctrl+Shift+Enter billentyűkombinációt.

Ha módosítania kell a mintavételi határokat, egyszerűen módosíthatja a határszámokat a feltételek oszlopban, és a kiválasztási eredmény automatikusan megváltozik.

Negyedik módszer: Véletlenszerű mintavétel

A táblázat bal oldalán ki kell hagyni az egyik oszlopot, és a következő cellába írjuk be a - =RAND() képletet egy véletlen szám megjelenítéséhez. Aktiválásához nyomja meg az ENTER billentyűt.

Ha véletlen számokból egy egész oszlopot kell létrehoznia, akkor helyezze a kurzort a képletet tartalmazó cella jobb alsó sarkába. Meg kell jelennie egy kitöltési markernek, amelyet az egér bal gombjának lenyomva tartásával kell lefelé húzni. Ez az adatokat tartalmazó táblázattal párhuzamosan és a végéig történik.

A cellatartomány tartalmazza a RAND képletet, de nem kell tiszta értékekkel dolgozni. Másolja a jobb oldali üres oszlopba, és válasszon ki egy cellatartományt véletlenszerű számokkal. A „Kezdőlap” lapon kattintson a „Másolás” gombra.

Válasszon ki egy üres oszlopot, és kattintson a jobb gombbal a helyi menü megjelenítéséhez. A „Beszúrási beállítások” eszközcsoportban válassza az „Értékek” lehetőséget (számokkal ellátott ikonként jelenik meg).

A „Főoldal” lapon kattintson a „Rendezés és szűrés”, majd az „Egyéni rendezés” elemre.

Jelölje be a „Az adataim fejléceket tartalmaz” lehetőség melletti négyzetet. A „Rendezés” sorban adja meg annak az oszlopnak a nevét, amelyben a másolt véletlenszám-értékek találhatók. A „Rendezés” sorban a beállítások az alapértelmezett értékeik maradnak. A „Rendelés” sorban válassza a „Növekvő” vagy „Csökkenő” opciót. Kattintson az OK gombra.

A táblázat értékeit a véletlen számok növekvő vagy csökkenő sorrendjében kell elhelyezni. A táblázatból tetszőleges számú első sor kivehető és véletlenszerű minta eredményének tekinthető.

A makró olyan műveletek sorozata, amely rögzítésre kerül, és későbbi használatra menthető. A mentett makró egy speciális paranccsal lejátszható. Más szóval, műveleteit makróban rögzítheti, elmentheti, majd lehetővé teszi a többi felhasználó számára, hogy egy egyszerű billentyűleütéssel lejátssza a makróban elmentett műveleteket. Ez különösen hasznos a kimutatás-jelentések terjesztésekor.

Tegyük fel, hogy ügyfelei számára lehetővé kívánja tenni a kimutatás-jelentések hónap, negyedév és év szerinti csoportosítását. Technikailag bárki elvégezheti a csoportosítási folyamatot, de néhány ügyfele nem érzi szükségét annak, hogy megértse. Ebben az esetben rögzíthet egy csoportosítási makrót hónaponként, egy másikat negyedévenként és egy harmadikat évenként. Ezután hozzon létre három gombot – minden makróhoz egyet. Ekkor azoknak az ügyfeleknek, akik még nem ismerik a kimutatásokat, csak egy gombra kell kattintaniuk a jelentés megfelelő csoportosításához Pivot tábla.

A makrók kimutatásban való használatának fő előnye, hogy az ügyfelek gyorsan hajthatnak végre olyan műveleteket a kimutatástáblákon, amelyeket általában nem tudnának végrehajtani. Ennek köszönhetően jelentősen megnő a szolgáltatott adatok elemzésének hatékonysága.

Töltse le a jegyzetet vagy formátumban, töltse le példákkal (belül Excel fájl makróval; A szolgáltató szabályzata nem teszi lehetővé az ilyen formátumú fájl közvetlen feltöltését az oldalra).

Makró rögzítése

Vessen egy pillantást az ábrán látható összefoglaló táblázatra. 1. Frissítheti ezt a pivot táblát, ha jobb gombbal kattint benne, és kiválasztja Frissítés. Ha a műveleteket makróként rögzítette a kimutatás frissítése során, Ön vagy bárki más újra lejátszhatja ezeket a műveleteket, és frissítheti a kimutatást a makró futtatásának eredményeként.

Rizs. 1. A pivot tábla frissítése közbeni műveletek rögzítése lehetővé teszi az adatok frissítését a jövőben a makró futtatásának eredményeként

A makró rögzítésének első lépése a párbeszédpanel meghívása Makró rögzítése. Menjen a lapra Fejlesztő szalagot, és kattintson a gombra Makró rögzítése. (Ha nem találja a fület a szalagon Fejlesztő, válassza a lapot Fájl, és kattintson a gombra Lehetőségek. A megjelenő párbeszédpanelen Excel-beállítások Válasszon egy kategóriát A szalag testreszabásaés a jobb oldali listában jelölje be a négyzetet Fejlesztő. Ennek eredményeként egy fül jelenik meg a szalagon Fejlesztő.) Alternatív mód kezdje el a makró rögzítését - kattintson a gombra (2. ábra).

A párbeszédpanelen Makró rögzítése Adja meg a következő makróinformációkat (3. ábra):

Névmakró. A névnek le kell írnia a makró által végrehajtott műveleteket. A névnek betűvel vagy aláhúzásjellel kell kezdődnie; nem tartalmazhat szóközt vagy más illegális karaktereket; nem egyezhet meg az Excel beépített nevével vagy egy másik objektum nevével a munkafüzetben.

Kombinációkulcsok. Ebbe a mezőbe bármilyen betűt beírhat. A makró lejátszásához használt billentyűparancs részévé válik. Nem szükséges billentyűkombinációt megadni. Alapértelmezés szerint csak a Ctrl billentyűt ajánljuk fel a kombináció kezdeteként. Ha azt szeretné, hogy a kombináció a Shift billentyűt is tartalmazza, írja be a betűt az ablakba, miközben lenyomva tartja a Shift billentyűt

MegmentV. Ez az a hely, ahol a makró tárolódik. Ha a kimutatás jelentést más felhasználóknak is el kívánja terjeszteni, válassza ki a lehetőséget Ezkönyv. Az Excel lehetővé teszi makró mentését is Új könyv vagy be Személyes makró könyv.

Leírás. Ebbe a mezőbe kerül a létrehozandó makró leírása.

Rizs. 3. Ablak testreszabása Makró rögzítése

Mivel a makró frissíti a pivot táblát, válassza ki a nevet Adatfrissítés. A Ctrl+Shift+Q billentyűparancsot is hozzárendelheti egy makróhoz. Ne feledje, hogy ha létrehoz egy makrót, akkor ezt a billentyűparancsot fogja használni annak futtatásához. A makró tárolási helyéhez válassza a lehetőséget Ezt a könyvetés kattintson rendben.

A párbeszédpanelen való kattintás után Makró rögzítése a gombon rendben Elkezdődik a makró rögzítése. Ezen a ponton minden, amit az Excelben csinál, rögzítésre kerül.

Kattintson a jobb gombbal a PivotTable területen, és válassza ki Frissítés(mint az 1. ábrán, de makró felvételi módban). A pivot tábla frissítése után a gombbal leállíthatja a makró rögzítési folyamatát Állítsa le a felvételt lapokat Fejlesztő. Vagy kattintson újra az ábrán látható gombra. 2.

Tehát most rögzítette az első makrót. Most már végrehajthatja a makrót a Ctrl+Shift+Q billentyűkombinációval.

Makró biztonsági figyelmeztetés. Meg kell jegyezni, hogy ha a felhasználó makrókat rögzít, akkor azok a biztonsági alrendszer korlátozása nélkül kerülnek végrehajtásra. Terítéskor azonban munkafüzet A makrókat tartalmazó makrók lehetőségét biztosítani kell a többi felhasználónak, hogy megbizonyosodjanak arról, hogy a munkafájlok megnyitásakor nincs kockázat, és a makrók végrehajtása nem vezet a rendszer vírusfertőzéséhez. Különösen azonnal észre fogja venni, hogy az ebben a fejezetben használt példafájl nem fog teljes mértékben működni, hacsak nem engedélyezi az Excel számára, hogy makrókat futtasson benne.

A makróbiztonság legegyszerűbb módja egy megbízható hely létrehozása – egy olyan mappa, amelybe csak a „megbízható” munkafüzetek kerülnek, amelyek nem tartalmaznak vírusokat. A megbízható hely lehetővé teszi Önnek és ügyfelei számára, hogy biztonsági korlátozások nélkül futtassák a makrókat a munkafüzeteken (ez a viselkedés mindaddig fennáll, amíg a munkafüzetek megbízható helyen vannak).

Megbízható hely beállításához kövesse az alábbi lépéseket:

Válasszon ki egy szalaglapot Fejlesztőés kattintson a gombra Makróbiztonság. Egy párbeszédpanel jelenik meg a képernyőn Bizalmi Központ.

Kattintson a gombra Új hely hozzáadása.

Kattintson a gombra Felülvizsgálat a megbízható munkafájlok mappájának megadásához.

Miután megadta a megbízható helyet, az adott helyen lévő összes munkafüzet alapértelmezés szerint tetszőleges makrókat fog futtatni.

Az Excel 2013-ban továbbfejlesztették a biztonsági modellt. Most a korábban „megbízható” munkafüzet-fájlok emlékeznek meg, pl. nyitás után Excel munkafüzetekés kattintson a gombra Tartalmazza Az Excel megjegyzi ezt az állapotot. Ennek eredményeként ez a könyv a megbízhatóak kategóriájába tartozik, és a következő kinyitáskor nem tesznek fel felesleges kérdéseket.

Felhasználói felület létrehozása űrlapvezérlőkkel

A makró futtatása a Ctrl+Shift+Q billentyűkombinációval segít, ha csak egy makró szerepel a kimutatásban. (Ezt a kombinációt a felhasználóknak is ismerniük kell.) De tegyük fel, hogy ügyfeleit több makróval szeretné ellátni, amelyek különböző műveleteket hajtanak végre. Ebben az esetben az ügyfelek számára érthető és egyszerű módon minden makrót futtathat anélkül, hogy emlékeznie kellene a billentyűkombinációkra. Az ideális megoldás egyszerű felhasználói felület vezérlők csoportjaként, például gombok, görgetősávok és egyéb vezérlők, amelyek lehetővé teszik a makrók egérkattintással történő végrehajtását.

Az Excel egy sor eszközt kínál felhasználói felületek létrehozására közvetlenül a táblázatban. Ezeket az eszközöket űrlapvezérlőknek nevezzük. Az alapötlet az, hogy elhelyezhet egy űrlapvezérlőt egy táblázatban, és hozzárendelhet egy korábban rögzített makrót. A vezérlőhöz való hozzárendelés után a makró az adott elemre kattintva elindul.

Az űrlapvezérlők a csoportban találhatók Űrlapvezérlők szalagfülek Fejlesztő. A vezérlőpaletta megnyitásához kattintson a csoport gombra Beszúrás(4. ábra).

Rizs. 4. Űrlapvezérlés Gomb

Figyelem: az űrlapvezérlőkön kívül a paletta tartalmaz még ActiveX vezérlők . Bár hasonlóak, programozásilag teljesen különböző objektumok. Űrlapvezérlők fogyatékosságaival és egyszerű beállítások kifejezetten munkalapokon való elhelyezésre tervezték. Eközben ActiveX vezérlők elsősorban egyedi formákban használják. Tedd szokássá, hogy csak űrlapvezérlőket helyezz el a munkalapokon.

Válassza ki azokat a vezérlőket, amelyek a legjobban megfelelnek az adott feladatnak. Ebben a példában az ügyfeleknek tudniuk kell frissíteni a kimutatástáblát egy gombra kattintva. Kattintson a vezérlőre Gomb, vigye az egérmutatót arra a helyre a munkalapon, ahol a gombot el szeretné helyezni, majd kattintson.

Miután elhelyezte a gombot a táblázatban, megnyílik egy párbeszédpanel Makró hozzárendelése tárgy(5. ábra). Válassza ki a kívánt makrót (esetünkben - Adatfrissítés, korábban rögzített) és kattintson a gombra rendben.

Rizs. 5. Válassza ki a gombhoz hozzárendelni kívánt makrót, majd kattintson a gombra rendben. Ebben az esetben a makrót kell használni Adatfrissítés

Miután elhelyezte az összes szükséges vezérlőt a kimutatásban, formázhatja a táblázatot egy alapfelület létrehozásához. ábrán. A 6. ábra a PivotTable jelentést mutatja formázás után.

Rögzített makró szerkesztése

Makró rögzítésének eredményeként Excel program létrehoz egy modult, amely tárolja az elvégzett műveleteket. Minden rögzített műveletet a VBA-kód sorai képviselnek, amelyek a makrót alkotják. Különféle típusú adatokat adhat hozzá a kimutatásokhoz. funkcionalitás, a VBA-kód testreszabása a kívánt eredmények elérése érdekében. Az egész működésének megértése érdekében hozzunk létre egy új makrót, amely megjeleníti az első öt ügyfélrekordot. Menjen a lapra Fejlesztőés kattintson a gombra Makró rögzítése. Megnyílik az ábrán látható párbeszédpanel. 7. Nevezze el a létrehozandó makrót ElsőÜgyfelekés jelezze a mentés helyét Ezt a könyvet. Kattintson rendben a makró rögzítésének megkezdéséhez.

A rögzítés megkezdése után kattintson a mező melletti nyílra Ügyfél neve, válassza ki Szűrés érték szerintés opció Első 10(8a. ábra). A megjelenő párbeszédpanelen állítsa be a beállításokat az ábra szerint. 8b. Ezek a beállítások az öt legjobb értékesítési volumenű ügyfél adatainak megjelenítését jelzik. Kattintson rendben.

Rizs. 8. Válassza ki a szűrőt (a), és állítsa be a beállításokat (b), hogy megjelenítse az öt legjobb vásárlót az eladások szerint

Miután sikeresen rögzítette az összes szükséges lépést az öt legnagyobb értékesítési potenciál kivonásához, lépjen a lapra Fejlesztőés kattintson a gombra Állítsa le a felvételt.

Most már rendelkezik egy makróval, amely kiszűri a kimutatástáblát az öt legjobb értékesítési fiók kibontásához. Szükséges, hogy a makró reagáljon a görgetősáv állapotára, pl. A görgetősáv segítségével meg kell tudnia mondani a makróval azoknak az ügyfeleknek a számát, akiknek adatai megjelennek a kimutatásban. Így a görgetősáv segítségével a felhasználó saját belátása szerint lekérheti az első öt, nyolc vagy legjobb harminckét klienst.

Ha görgetősávot szeretne hozzáadni a táblázathoz, kattintson a Fejlesztő, kattintson a gombra Beszúrás, válasszon egy vezérlőt a palettán Görgetősávés helyezd el a munkalapodra. Kattintson a jobb gombbal a vezérlőre Görgetősáv Objektum formátum. Megnyílik egy párbeszédpanel Vezérlő formátum(9. ábra). Ebben hajtsa végre a következő beállításokat: paraméter Minimális érték a paraméterhez 1 értéket rendeljen Maximális érték- értéke 200, és a mezőben SejtkommunikációÍrja be a $M$2 értéket a görgetősáv értékének megjelenítéséhez az M2 cellában. Kattintson a gombra rendben a korábban megadott beállítások alkalmazásához.

Most meg kell egyeznie a nemrég rögzített makróval ElsőÜgyfelek vezérlőelemmel Görgetősáv munkalapon található. Kattintson a jobb gombbal a vezérlőre Görgetősávés be helyi menü Válassz csapatot Makró hozzárendelése a makró-hozzárendelés párbeszédpanel megnyitásához. Rögzített makrót rendelhet a görgetősávhoz FirstN ügyfelek. A makró minden alkalommal végrehajtásra kerül, amikor a görgetősávra kattint. Tesztelje a létrehozott görgetősávot. A sávra kattintva elindul a makró FirstN ügyfelekés az M2 cellában lévő szám megváltozik, és a görgetősáv állapotát mutatja. Az M2 cellában lévő szám azért fontos, mert a makrót a görgetősávhoz köti.

Csak annyit kell tennie, hogy a makró feldolgozza az M2 cellában lévő számot úgy, hogy hozzárendeli a görgetősávhoz. Ehhez a makró VBA kódjához kell lépni. Ehhez lépjen a lapra Fejlesztőés kattintson a gombra Makrók. Megnyílik egy párbeszédpanel Makró(10. ábra). Ebben futtathatja, törölheti és szerkesztheti a kiválasztott makrót. Egy makró VBA-kódjának képernyőn történő megjelenítéséhez válassza ki a makrót, és kattintson a gombra változás.

Rizs. 10. A makró VBA kódjának elérése ElsőÜgyfelek, válassza ki a makrót, és kattintson a gombra változás

A képernyőn megjelenik egy Visual Basic szerkesztő ablak a VBA makrókóddal (11. ábra). A cél az, hogy a makró rögzítésekor beállított, kódolt 5-ös számot lecserélje az M2 cellában lévő értékre, amely a görgetősávhoz van kötve. Kezdetben egy makrót rögzítettek az első öt legmagasabb bevételű ügyfél megjelenítésére.

Távolítsa el az 5-ös számot a kódból, és írja be helyette a következő kifejezést:

ActiveSheet.Range(" M2 ").Érték

A szűrők törléséhez adjon hozzá két sort a makró elejéhez:

Tartomány(" A4 ").Válassza ki
ActiveSheet.PivotTables(" PivotTable1 ").PivotFields(" Ügyfél neve ").ClearAllFilters

Most a makrókódnak úgy kell kinéznie, mint az ábrán látható. 12.

Zárja be a Visual Basic szerkesztőt, és térjen vissza a kimutatáshoz. Tesztelje a görgetősávot a csúszka 11-re húzásával. A makrónak futnia kell, és ki kell szűrnie a legjobb 11 értékesítési fiókot.

Szinkronizáljon két pivot táblát egyetlen legördülő lista használatával

ábrán látható jelentés. A 13. két összefoglaló táblázatot tartalmaz. Mindegyikhez tartozik egy oldalmező, amely lehetővé teszi az értékesítési piac kiválasztását. A probléma az, hogy minden alkalommal, amikor kiválaszt egy piacot egy pivot tábla oldalak mezőjében, ugyanazt a piacot kell kiválasztania egy másik pivot tábla oldalak mezőjében. A szűrők szinkronizálása két tábla között az adatelemzési szakaszban nem jelent nagy problémát, de előfordulhat, hogy Ön vagy ügyfelei elfelejtik ezt megtenni.

Rizs. 13. Két pivot tábla oldalmezőket tartalmaz, amelyek piac szerint szűrik az adatokat. Egyetlen piac adatainak elemzéséhez mindkét pivot táblát szinkronizálnia kell

A pivot táblák szinkronban tartásának egyik módja egy legördülő lista használata. Az ötlet az, hogy rögzítsünk egy makrót, amely kiválasztja a kívánt piacot a mezőből Értékesítési piac mindkét táblázatban. Ezután létre kell hoznia egy legördülő listát, és meg kell töltenie a piacok nevével a két pivot táblából. Végül a rögzített makrót módosítani kell, hogy mindkét pivot táblát a legördülő listából származó értékek segítségével szűrje. A probléma megoldásához a következőket kell tennie:

1. Hozzon létre egy új makrót, és adjon neki nevet SynchMarkets. A rögzítés megkezdésekor válassza ki a mezőben Eladási piac mindkettőnekértékesítési piac összesítő táblázatai Kaliforniaés állítsa le a makró rögzítését.

2. Jelenítse meg az Űrlapvezérlők palettát, és adjon hozzá egy legördülő listát a munkalaphoz.

3. Hozzon létre egy kódolt listát az összes piacról a pivot táblában. Kérjük, vegye figyelembe, hogy a lista első eleme az érték (All). Engedélyezze ezt az elemet, ha ki szeretné választani az összes piacot a legördülő listából.

4. Ezen a ponton a kimutatásnak úgy kell kinéznie, mint az ábrán. 14.

Rizs. 14. Minden szükséges eszköz a rendelkezésére áll: egy makró, amely megváltoztatja a mezőt Értékesítési piac mindkét pivot tábla, egy legördülő lista és a pivot táblában található összes értékesítési piac listája

5. Kattintson a jobb gombbal a legördülő listára, és válassza ki a parancsot a helyi menüből Objektum formátum a vezérlés konfigurálásához.

6. Először állítsa be a legördülő lista feltöltéséhez használt kezdeti értéktartományt az ábrán látható módon. 15. Ebben az esetben a 3. lépésben létrehozott értékesítési piacok listájáról beszélünk. Ezután jelölje meg a kiválasztott elem sorozatszámát megjelenítő cellát (a ebben a példában ez a H1 cella). Paraméter Listasorok száma meghatározza, hogy a legördülő listában egyszerre hány sor jelenjen meg. Kattintson a gombra rendben.

Rizs. 15. A legördülő lista beállításainak az értékesítési piacok listájára kell mutatniuk, mint kezdeti értéktartományt, és rögzítési pontként a H1 cellát kell megadni.

7. Most lehetősége van a legördülő listából kiválasztani egy értékesítési piacot, és a H1 cellában meghatározni a hozzá tartozó sorozatszámot (16. ábra). Felmerül a kérdés: miért az indexértékét használják a piac valódi neve helyett? Mert a legördülő lista nem nevet, hanem számot ad vissza. Például, ha a legördülő listából Kaliforniát választja, a H1 cellában az 5. érték jelenik meg. Ez azt jelenti, hogy Kalifornia az ötödik elem a listában.

Rizs. 16. A legördülő lista most megtelik a piacok nevével, és a kiválasztott piac sorozatszáma megjelenik a H1 cellában.

8. Ha a piac neve helyett sorszámot szeretne használni, azt az INDEX funkcióval kell átadnia.

9. Írja be az INDEX függvényt, amely a H1 cellából származó sorszámot értelmes értékké alakítja.

10. Az INDEX függvény két argumentumot vesz fel. Az első argumentum a lista értéktartományát képviseli. A legtöbb esetben ugyanazt a tartományt fogja használni, mint a legördülő menüben. A második argumentum a sorszám. Ha a sorozatszámot egy cellába írjuk be (például a H1 cellába, mint a 17. ábrán), akkor egyszerűen hivatkozhatunk erre a cellára.

Rizs. 17. Az I1 cellában lévő INDEX függvény a H1 cellában tárolt sorszámot értékké alakítja. Az I1 cellában lévő értéket fogja használni a makró módosításához

11. Szerkessze a makrót SynchMarkets, a kódolt érték helyett az I1 cellában található értéket használja. Menjen a lapra Fejlesztőés kattintson a gombra Makrók. Az 1. ábrán látható párbeszédpanel jelenik meg a képernyőn. 18. Válasszon ki benne egy makrót SynchMarketsés kattintson a Módosítás gombra.

Rizs. 18. A makró VBA-kódjának eléréséhez válassza ki a makrót SynchMarketsés kattintson változás

12. A makró rögzítésekor mindkét pivot táblában kiválasztotta a kaliforniai értékesítési piacot a mezőből Értékesítési piac. ábrából látható. 19., a kaliforniai piac most már a VBA makrókódban van kódolva.

13. Cserélje le a "California" értéket az Activesheet.Range("I1").Value kifejezésre, amely az I1 cellában lévő értékre hivatkozik. Ebben a szakaszban a makrókódnak úgy kell kinéznie, mint az ábra. 20. A makró módosítása után zárja be a Visual Basic Editort, és térjen vissza a táblázathoz.

Rizs. 20. Cserélje ki a "California" értéket az ActiveSheet.Range("I1").Value értékre, és zárja be a Visual Basic Editort

14. Csak annyit kell tennie, hogy a makró végrehajtásra kerüljön, amikor kiválasztja az értékesítési piacot a legördülő listából. Kattintson a jobb gombbal a legördülő menüre, és válassza ki a lehetőséget Makró hozzárendelése. Válassza ki a makrót SynchMarketés kattintson a gombra rendben.

15. Rejtse el az oldalmező sorait és oszlopait a pivot táblákban, valamint a piacok listáját és az indexképleteket.

ábrán. A 21. ábra mutatja a végeredményt. Mostantól olyan felhasználói felülettel rendelkezik, amely lehetővé teszi az ügyfelek számára, hogy egyetlen legördülő lista segítségével válasszon piacot mindkét pivot táblában.

Ha új elemet választ ki a legördülő listából, az oszlopok automatikusan átméreteződnek, hogy az összes megjelenített adathoz illeszkedjenek. A program ilyen viselkedése meglehetősen bosszantó munkalapsablon formázásakor. Ezt úgy akadályozhatja meg, hogy jobb gombbal a pivot táblára kattint, és kiválasztja PivotTable beállításai. A képernyőn megjelenik egy azonos nevű párbeszédpanel, amelyben vissza kell állítania a jelölőnégyzetet Az oszlopszélesség automatikus módosítása frissítéskor.

A jegyzet Jelen, Alexander könyve alapján készült. . 12. fejezet.