Podstatou žiadosti o výber je vybrať reťazec zo zdrojovej tabuľky, ktorá spĺňa určité kritériá (podobné používaniu normy). Vyberieme hodnoty zo zdrojovej tabuľky. Na rozdiel od používania ( CTRL + SHIFT + L alebo Údaje / Zoradiť a filter / Filter) Vybrané riadky budú umiestnené v samostatnom stole.
V tomto článku zvážte najbežnejšie požiadavky, napríklad: výber riadkov tabuľky, v ktorom hodnota z numerickej kolóny spadá do špecifikovaného rozsahu (interval); Výber riadkov, v ktorých dátum zadáva určitý čas; Úlohy s 2 textovými kritériami a inými. Začnime s jednoduchými otázkami.
1. Jedno číselné kritérium (vyberte si tovar, ktorý má cenu nad minimum)
príklad Súbor, list Jedno kritérium - číslo ).
Musíte zobraziť v samostatnom stole len tie záznamy (riadky) zo zdrojovej tabuľky, v ktorom je cena vyššia ako 25.
Vyriešiť toto a následné úlohy môžu byť ľahko s. Ak to chcete urobiť, vyberte titulky zdrojovej tabuľky a kliknite na tlačidlo CTRL + SHIFT + L. Prostredníctvom rozbaľovacieho zoznamu titulu vyberte Číselné filtre ..., potom nastavte potrebné podmienky filtrovania a kliknite na tlačidlo OK.
Zobrazia sa záznamy, ktoré spĺňajú podmienky výberu.
Ďalším prístupom je použitie. Na rozdiel od vybraných línií budú umiestnené v samostatnom stole - zvláštnym spôsobom, ktorý môže byť napríklad formátovaný v inom štýle ako zdrojovej tabuľke alebo produkovať svoje ďalšie modifikácie.
Kritérium (minimálna cena) bude umiestnená v bunke E6. , Tabuľka pre filtrované dáta - v rozsahu D10: E19 .
Teraz vyberte rozsah D11: D19. (stĺpec) av úvode:
Index (A11: A19;
Najmenší (ak ($ \u200b\u200be $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
Zdvih ($ b $ 10))
Namiesto toho Zadať Stlačte klávesovú kombináciu Ctrl + Shift + ENTER(Masív vzorca bude).
E11: E19 (Cena stĺpca) Kde a zadajte to isté:
Index (B11: B19;
Najmenší (ak ($ \u200b\u200be $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
Zdvih ($ b $ 10))
V dôsledku toho získame novú tabuľku, ktorá bude obsahovať iba tovar, že ceny nie sú v bunke menej špecifikované E6. .
Ak chcete zobraziť dynamiku prijatej žiadosti o výber, zavádzame do E6. hodnota 55. Do novej tabuľky spadnú iba 2 záznamy.
Ak sa zdrojová tabuľka pridá nový produkt s cenou 80, potom sa do novej tabuľky pridá nový záznam.
Poznámka. Tiež sa môže použiť aj na výstup filtrovaných údajov. Voľba konkrétneho nástroja závisí od úlohy, ktorým čelí používateľa.
Ak nie ste vhodné používať masiva Vzorecktorý vráti niekoľko hodnôt, môžete použiť iný prístup, ktorý sa uvažuje v sekciách uvedených nižšie: 5.A, 7, 10 a 11. V týchto prípadoch sa používajú.
2. Dve číselné kritériá (vyberte si tovar, ktorý cena patrí do rozsahu)
Nech je zdrojový stôl so zoznamom tovaru a cenami (pozri príklad Súbor, listRozsah čísel).
Kritériá (nižšie a najvyššie ceny cien) budú umiestnené v rozsahu E5: E6. .
Tí. Ak cena tovaru klesne do zadaného intervalu, potom sa takýto záznam objaví v novom tabuľke filtrovaných údajov.
Na rozdiel od predchádzajúcej úlohy vytvoríme dva: tovar a ceny (bez nich môžete urobiť, ale sú pohodlné pri písaní vzorcov). Zodpovedajúce vzorce by mali vyzerať ako manažér mena ( Formuláry / Špecifické názvy / Názov Správca) Nasledovne (pozri obrázok nižšie).
Teraz vyberte rozsah D11: D19. A v úvode:
Index (tovar;
Najmenej (
Ak ((($ e $ 5<=Цены)*($E$6>\u003d Ceny); riadok (ceny); "");
Namiesto toho Zadať Stlačte klávesovú kombináciu Ctrl + Shift + ENTER.
Rovnaké manipulácie produkujú s rozsahom E11: E19 Kde a zaviesť podobné:
Index (ceny;
Najmenej (
Ak ((($ e $ 5<=Цены)*($E$6>\u003d Ceny); riadok (ceny); "");
ROW (ceny) -SPOPOK ($ B $ 10)) - Linka ($ B $ 10))
V dôsledku toho získame novú tabuľku, ktorá bude obsahovať iba tovar, ktoré ceny spadajú do intervalu uvedeného v bunkách E5 a E6. .
Ukázať dynamiku prijatej správy (žiadosť o výber) E6. hodnota 65. Ďalší záznam zo zdrojovej tabuľky bude pridaný do novej tabuľky, ktorý spĺňa nové kritérium.
Ak pridáte nový produkt s cenou v zdrojovej tabuľke z 25 až 65, do novej tabuľky sa pridá nový záznam.
Príkladový súbor obsahuje aj chybové manipulácie vzorce, keď cenový stĺpec obsahuje hodnotu chyby, napríklad # prípady / 0! (pozri list Spracovanie chýb).
Nasledujúce úlohy sú riešené podobným spôsobom, takže ich nepovažujeme tak podrobne.
3. Jeden dátum kritéria (vyberte tieto produkty, ktoré je uvedený dátum dodania)
príklad Súbor, listJeden kritérium - dátum).
Pre výber riadkov, vzorcov poľa, podobné úlohu1 (namiesto kritéria<= используется =):
=Index (A12: A20; najmenší (ak ($ \u200b\u200bE $ 6 \u003d B12: B20; riadok (B12: B20); ""); String (B12: B20) - String ($ B $ 11)) - riadok ($ B $ 11))
Index (B12: B20; najmenší (ak ($ \u200b\u200bE $ 6 \u003d B12: B20; riadok (B12: B20); ""); String (B12: B20) - String ($ B $ 11)) - riadok ($ B $ 11))
4. Dátum kritérií (vyberte tovar, ktorý dátum dodania spadá do rozsahu)
Nech je zdrojový stôl so zoznamom tovaru a dátumov dodania (pozri príklad Súbor, listRozsah dátumu).
Upozorňujeme, že stĺpec dátumov nie je triedený.
Riešenie1: Pre výber riadkov môžete použiť.
Zadajte do bunky D12. vzorec poľa:
Index ($ 12: $ 20;
Najväčší (($ e $ 6<=$B$12:$B$20)*($E$7>\u003d $ B $ 12: $ B $ 20) * (linka ($ B $ 12: $ B $ 20) -strokok ($ B $ 11));
$ J $ 12-line (A12) + linka ($ B $ 11) +1)
Poznámka: Po zadaní vzorca namiesto klávesu ENTER, musíte stlačiť kláves Ctrl + Shift + Enter Key Combiting. Táto kombinácia kľúčov sa používa na zadanie vzorca poľa.
Skopírujte vzorca MATSIF na požadovaný počet buniek. Vzorec vráti iba tie hodnoty tovaru, ktoré boli dodané v rozsahu týchto dátumov. Zvyšok buniek bude obsahovať chyby # číslo! Chyby B. príklad súboru (zoznam dátumov) .
Podobný vzorec musí byť zavedený pre dátumy v E.
V bunke J12 vypočíta sa počet riadkov zdrojovej tabuľky, ktorý spĺňa kritériá:
Kolotovateľnosť (B12: B20; "\u003e \u003d" & $ E $ 6; B12: B20;<="&$E$7)
Riadky zdrojovej tabuľky, ktoré spĺňajú kritériá.
Riešenie2: Ak chcete vybrať riadky, môžete použiť polerové vzorce, podobné úlohu2 (t.j.):
=Index (A12: A20; najmenší (ak ((((($ e $ 6)<=B12:B20)*($E$7>\u003d B12: B20); LINE (B12: B20); ""); String (B12: B20) -strkok ($ B $ 11)) - linka ($ B $ 11))
Index (B12: B20; Najmenší (ak (((($ E $ 6)<=B12:B20)*($E$7>\u003d B12: B20); LINE (B12: B20); ""); String (B12: B20) -strkok ($ B $ 11)) - linka ($ B $ 11))
Ak chcete zadať prvý vzorec, zvýraznite rozsah buniek G12: G20. . Po zadaní vzorca namiesto klávesu Enter, musíte stlačiť kláves Ctrl + Shift + Enter Key Combiting.
Riešenie3.: Ak je stĺpec dátumov triedená, potom nemôžete používať vzorce poľa.
Po prvé, je potrebné vypočítať prvú a poslednú pozíciu riadkov, ktoré spĺňajú kritériá. Potom výstupné reťazce.
Tento príklad opäť jasne ukazuje, ako ľahšie písacie vzorce.
5. Jedným dátumom kritéria (vyberte tieto produkty, ktoré dátum dodania nie je skorší / najneskôr neskôr, ako je uvedené)
Nech je zdrojový stôl so zoznamom tovaru a dátumov dodania (pozri príklad Súbor, list Jeden kritérium - dátum (nie neskôr) ).
Pre výber riadkov, dátum, ktorý nie je skôr (vrátane samotného dátumu), používa sa vzorec poľa:
=Index (A12: A20; najmenší (ak ($ \u200b\u200bE $ 7)<=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))
Aj v príklade súboru sú uvedené vzorce: nie skôr (vrátane); Neskôr (vrátane); Neskôr (vrátane).
7. Jedno kritérium textu (vyberte si určitý druh tovaru)
Nech je zdrojový stôl so zoznamom tovaru a cenami (pozri príklad Súbor, listJedno kritérium - text).
8. Dva textové kritériá (vyberte tovar určitého typu stanoveného v určenom mesiaci)
Nech je zdrojový stôl so zoznamom tovaru a cenami (pozri príklad Súbor, list 2 Kritériá - Text (y) ).
Index ($ A $ 11: $ A $ 19;
Najmenší (ak ((((($ F $ 6 \u003d $ A $ 11: $ A $ 19 $ $ 19 \u003d $ 11: $ B $ 19); riadok ($ A $ 11: $ $ 19 ) -strk ($ A $ 10); 30); riadok (DVSL ("A1: A" & ESTEXT ($ A $ 11: $ A $ 19))))
Vyjadrenie ($ F $ 6 \u003d $ A $ 11: $ A $ 19) * ($ F $ 7 \u003d $ B $ 11: $ B $ 19) Určuje obe podmienky (tovar a mesiac).
Vyjadrenie ROW (DVSL ("A1: A" & CREKOK ($ A $ A $ A $ A $ A $ 19)) Formuláre (1: 2: 3: 4: 5: 6: 7: 8: 9), t.j. Čísla riadkov v tabuľke.
9. Dva textové kritériá (vyberte si produkty určitých druhov)
Nech je zdrojový stôl so zoznamom tovaru a cenami (pozri príklad Súbor, list2 kritériá - text (alebo)).
Na rozdiel od úlohy, 7 vyzdvihne reťazce s 2 druhmi ().
Pre výber riadkov sa použije vzorec poľa:
Index ($ 11: $ 19;
Najväčšie (((($ E $ 6 \u003d $ A $ 11: $ A $ 19) + ($ E $ 7 \u003d $ A $ 11: $ A $ 19)) * (linka ($ A $ 11: $ A $ 19) -SPROK ($ A $ 10)); počítané ($ A $ 11: $ A $ 19; $ E $ 6) + odpočítavanie ($ A $ 11: $ A $ 19; $ E $ 7) -CONS $ A $ 11: A11) +1)
Stav ($ E $ 6 \u003d $ A $ 11: $ A $ 19) + ($ E $ 7 \u003d $ A $ 11: $ A $ 19) Zabezpečuje, že tovar len špecifikovaných druhov zo žltých buniek (tovar2 a tovar3) sa vyberie. A + (Dodatok) sa používa na úlohu (musí sa vykonať aspoň 1 kritérium).
Vyššie uvedená expresia vráti pole (0: 0: 0: 0: 1: 1: 1: 0: 0). Vynásobte ho ROW ($ A $ 11: $ A $ 19) -SPOK ($ A $ 10). On (1: 2: 3: 4: 5: 6: 7: 8: 9), získame rad pozícií (čísla tabuľkových liniek), ktoré spĺňajú kritériá. V našom prípade to bude pole (0: 0: 0: 0: 5: 6: 0: 0).
Ako príklad urobte riešenie nasledujúcej úlohy: Vyberte si tovar, ktorého cena leží v konkrétnom rozsahu a opakuje špecifikovaný počet časov alebo viac.
Ako počiatočná tabuľka zmluvných strán tovaru.
Predpokladajme, že máme záujem o to, koľko a ktoré strany boli dodané za cenu 1000R. do roku 2000 (Kritérium 1). Okrem toho by strany s rovnakou cenou mali byť najmenej 3 (kritérium 2).
Riešením je Masívny vzorec:
Najmenšia (linka ($ A $ 14: $ A $ 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- $ G $ 9)
Tento vzorec vráti čísla riadkov, ktoré spĺňajú oba kritériá.
Vzorec \u003d Summice (($ C $ 14: $ C $ 27\u003e \u003d $ B $ 7) * ($ C $ 14: $ C $ 27<=$C$7)*($D$14:$D$27>\u003d $ B $ 10) Vypočíta počet riadkov, ktoré spĺňajú kritériá.
11. Používame hodnotu kritéria (ktorýkoľvek) alebo (všetky)
V príklad Súbor na hárku "11. Kritérium akéhokoľvek alebo (všetky)" Táto možnosť je implementovaná.
Vzorec v tomto prípade by mal obsahovať funkciu, ak (). Ak je hodnota vybratá (všetko), vzorec sa používa na výstup hodnôt bez zohľadnenia tohto kritéria. Ak je vybratá akoukoľvek inou hodnotou, kritérium funguje obvyklým spôsobom.
Ak ($ c $ 8 \u003d "(všetko)";
Najmenší ((Line ($ B $ 13: $ B $ 26) -strok ($ B $ 12) * ($ D $ 13: $ D $ 26\u003e \u003d $ D $ 8); F13 + ($ G $ 6- $ G $ 7);
Najmenší ((LINE ($ B $ 13: $ B $ 26) -SPROW ($ B $ 12) * ($ D $ 13: $ D $ 26\u003e \u003d $ D $ 8) * ($ C $ 13: $ C $ 26 \u003d $ C $ 8); F13 + ($ G 6- $ G $ 7))
Zvyšok vzorca je podobný týmto uvedeným vyššie.
Odber vzoriek údajov
Vytvorte vzorovú správu z listu5 na stĺpci "kvalitatívny výkon, percento". (z listu 8, tabuľka 7)
Ak chcete vykonať výber údajov, musíte vykonať nasledujúce kroky:
Určite počet prvkov nového poľa v danom stave zadaním premennej pomocou operátora Inputbox
Deklarovať a obnoviť nové pole
Vytvoriť nové pole. Ak to chcete urobiť, zadajte počet prvých prvku nového poľa u \u003d 1. Potom sa vykonáva cyklus, ktorý zaznamenáva stav vzorky na stĺpci "kvalita výkonu, percentách". Ak je výsledok overovania pravdy, prvok analyzovaného poľa sa stáva prvokom nového poľa.
Stiahnuť nový prvok na list 8
Podranné dieťa ()
Listy ("List8"). Vybrať
Stlmiť () ako variant
Rediim a (1 až n1, 1 až m)
VVOD "List5", A, N1, M, 4
C \u003d vstupná schránka ("Zadajte stav")
Listy ("List8"). Bunky (5.11) \u003d c
Pre i \u003d 1 až n1
Ak á (I,
8) >
d \u003d D + 1
Listy ("List8"). Bunky (5.10) \u003d D
DIM B () ako variant
Redim b (1 až d, 1 až m)
Pre i \u003d 1 až n1
Ak á (I,
8)\u003e Listy ("LIST8"). Bunky (5.11)
Pre j \u003d 1 až m
B (U, J) \u003d A (I, J)
u \u003d u + 1
Pre i \u003d 1 až d
Pre j \u003d 1 až m
Listy ("List8"). Bunky (I + 4, J) \u003d B (I, J)
Obr. Tabuľkové údaje po odbere vzoriek
Vytvorenie automatickej vzorovej makro
Zapnite nahrávanie makra. Servis\u003e Makro\u003e Štart Record\u003e OK. Štvorcový sa objaví, kde tlačidlo zastavenia. Na hárku5 (správa), zvýrazňujeme tabuľku bez hlavičiek a výsledkov, kópiu do zoznamu 10 (Auto Collector). Zvýrazňujeme tabuľku bez hlavičiek a v položke menu, vyberte položku Údaje\u003e Filter\u003e Auto Filter\u003e Vybrať stav\u003e OK. Oslávujeme stĺpec, na ktorom budeme triediť. Dokončite prácu makra.
Subro2 hodnota ()
Makro "Makro 22 makro
Listy ("List5"). Vybrať
Výber. Kópia
Listy ("List9"). Vybrať
ActiveSheet. Prilepiť
Rozsah ("H5: H17"). Vybrať
Aplikácia. CUTCOPYMODE \u003d FALSE
Výber. AUTOFILTER.
ActiveSheet. ("$ H $ 5: $ h $ 17"). AutoFilter Field: \u003d 1, Kritériá1: \u003d "\u003e 80", _
Operátor: \u003d xland
("G22"). Vybrať
Obr. Tabuľkové údaje po automatickom odbere vzoriek
Stanovenie maximálnej a minimálnej hodnoty
Určite hodnoty max a min v stĺpcoch "Celkom", "absolútny výkon, percentá", "vysokokvalitný výkon" (tabuľka 9, list 10)
Ak chcete určiť hodnoty max a min, musíte vykonať nasledujúce kroky:
Nastavte štandardnú premennú, ktorá bude aktuálne minimum (maximálne)
Skorý porovnáva každý prvok nastavenej s aktuálnym minimálnym (maximálnym) a ak tento prvok nespĺňa podmienky vyhľadávania (v prípadoch minima je väčšie av prípade maxima - menej), potom štandard Hodnota je nahradená hodnota porovnaného prvku
Po úplnom sledovaní všetkých prvkov v premennej aktuálnej minimálnej minimálnej (maximálnej) je platné minimum (maximálne)
Hodnota minima (maximálne) sa zobrazí v zodpovedajúcich bunkách.
Sub Minmax ()
Stlmiť () ako variant
n1 \u003d listy ("hárok4"). Bunky (5,12)
m \u003d listy ("List2"). Bunky (5,12)
Rediim a (1 až n1, 1 až m)
VVOD "List5", A, N1, M, 4
Vivod "List10", A, N1, M, 4
VVOD "List10", A, N1, M, 4
Pre j \u003d 3 až m
maxa \u003d 0,00001
mina \u003d 1000000.
Pre i \u003d 1 až n1
Ak A (I, J)\u003e Maxa to
maxa \u003d A (I, J)
Ak A (I, J)< minA Then
mina \u003d A (I, J)
Listy ("LIST10). Bunky (I + 4 + 2, J) \u003d MAXA
Listy ("LIST10). Bunky (I + 4 + 3, J) \u003d MINA
Makro je postupnosť činností, ktoré sa zaznamenávajú a uložia na ďalšie použitie. Uložené makro môže byť reprodukované špeciálnym tímom. Inými slovami, môžete nahrávať vaše akcie v makro, uložiť ho a potom dovoliť ostatným používateľom hrať uloženú v akční makro jednoduchým stlačením tlačidla. To je obzvlášť vhodné pri distribúcii správ o súhrnných tabuľkách.
Predpokladajme, že chcete poskytnúť našim zákazníkom možnosť skupinového hlásenia konsolidovaných tabuliek podľa mesiacov, štvrtí a rokov. Technicky môže proces zoskupenia vykonávať každého používateľa, ale niektorí z vašich zákazníkov nebude považovaní za potrebné na to, aby ste to pochopili. V podobnom prípade môžete napísať jednu makro zoskupenia po celé mesiace, druhý - v štvrťrokoch a tretí - do roku. Potom vytvorte tri tlačidlá - jeden na každom makro. Potom budú vaši zákazníci, ktorí nemajú skúsenosti s konsolidovanými tabuľkami, budú musieť len kliknúť na tlačidlo, aby ste riadne zoskupeli tabuľku súhrnnej tabuľky.
Hlavnou výhodou použitia makier v správach o konsolidovaných tabuľkách je poskytnúť zákazníkom schopnosť rýchlo vykonávať takéto operácie v konsolidovaných tabuľkách, ktoré nemôžu plniť v obvyklej situácii. Vzhľadom na to sa účinne zvyšuje účinnosť analýzy poskytovaných údajov.
Stiahnite si poznámku vo formáte alebo, sťahovať s príkladmi (vnútri programu Excel s makrámi; Politika poskytovateľa neumožňuje priamo nahrať súbor tohto formátu na stránku).
Makro
Pozrite sa na konsolidovanú tabuľku znázornenú na obr. 1. Túto konsolidovanú tabuľku môžete aktualizovať kliknutím pravým tlačidlom myši a vyberte príkaz. Obnoviť. Ak ste zaznamenali akcie vo forme makro pri aktualizácii tabuľky otočení, potom vy alebo iný používateľ môže reprodukovať tieto akcie a aktualizovať konsolidovanú tabuľku v dôsledku štartu makra.
Obr. 1. Nahrávanie akcií Počas aktualizácie tejto súhrnnej tabuľky bude ďalej aktualizovať údaje v dôsledku spustenia makra
Výzva dialógového okna je prvý etapa v makracom zázname Makro. Kliknite na kartu Vývoja Stuhy a kliknite na tlačidlo Makro. (Ak nemôžete nájsť kartu na páske Vývoja, Vyberte kartu Spisa kliknite na tlačidlo Parametre. V dialógovom okne, ktoré sa zobrazí dialógové okno Nastavenia programu Excel vyberte kategóriu Nastavenie pásky A v pravom zozname vyberte začiarkavacie políčko Vývoja. Výsledkom je, že páska sa zobrazí na páske Vývoja,) Alternatívny spôsob, ako začať nahrávať makro - kliknite na tlačidlo (obr. 2).
V dialógovom okne Makro Zadajte nasledujúce makro informácie (obr. 3):
názovmakro. Názov musí opísať akcie vykonané makrom. Názov musí začínať písmenom alebo podčiarknutím; by nemali obsahovať priestor a iné neprijateľné príznaky; Nemalo by sa zhodovať s vstavaným názvom Excel alebo meno iného objektu v knihe.
Kombináciakľúče. V tomto poli môžete zadať ľubovoľný list. Bude to súčasťou kombinácie kľúčov, ktorá sa bude používať na prehrávanie makra. Kľúčová kombinácia je voliteľná. V predvolenom nastavení sa ako začiatok kombinácie ponúka iba Ctrl. Ak chcete, aby sa kombinácia zapne aj posun, vytočte písmeno v okne, zatiaľ čo podržaním klávesu Shift
Uložiťv. To označuje miesto skladovania makro. Ak sa medzi ostatnými užívateľmi distribuujete súhrnnú tabuľku, vyberte parameter Totokniha. Excel tiež umožňuje uložiť makro v Nová kniha alebo v Osobné knihy makrá.
Popis. Toto pole popisuje opis vytvoreného makro.
Obr. 3. Nastavenie okna Makro
Keďže makro aktualizuje konsolidovanú tabuľku, vyberte položku Názov Upgrade. Môžete tiež priradiť kombináciu klávesov CTRL + SHIFT + Q. Nezabudnite, že po vytvorení makra použijete túto kombináciu kľúčov na spustenie. Vyberte parameter ako miesto na ukladanie makro Táto kniha a kliknite V poriadku.
Po kliknutí v dialógovom okne Makro Na tlačidlo V poriadku Makro začína spustiť. V tomto štádiu budú registrované všetky akcie, ktoré vykonávate v programe Excel.
Kliknite pravým tlačidlom myši na konsolidovanú oblasť tabuľky a vyberte príkaz. Obnoviť (ako na obr. 1, ale už v režime nahrávania makra). Po aktualizácii tabuľky otáčok môžete zastaviť proces nahrávania makra pomocou tlačidla. Zastaviť nahrávanie záložky Vývoja. Alebo znova kliknite na tlačidlo zobrazené na obr. 2.
Takže ste práve zaznamenali svoje prvé makro. Teraz môžete vykonať makro pomocou kombinácie klávesov CTRL + SHIFT + Q.
Makro Bezpečnostné upozornenie. Treba poznamenať, že v prípade nahrávania makier užívateľom budú vykonané bez akýchkoľvek obmedzení zo strany subsystému bezpečnosti. Napriek tomu, že pri distribúcii pracovnej knihy obsahujúcej makrá, je potrebné poskytnúť ostatným používateľom možnosť zabezpečiť, aby neexistovali riziko pri otváraní pracovných súborov a vykonávanie makier nevedie k infekcii systému vírusov. Najmä okamžite si všimnete, že príkladný súbor použitý v tejto kapitole nebude plne funkcia, ak nie je špecificky povolený Excel vykonávať makrá v ňom.
Najjednoduchší spôsob, ako zabezpečiť bezpečnosť makier, je vytvoriť spoľahlivé umiestnenie - priečinok, ku ktorému iba "spoľahlivé" zošit, ktoré neobsahujú vírusy, budú umiestnené. Spoľahlivá lokalita umožňuje vám a vašim klientom vykonávať makrá v pracovných miestach bez akýchkoľvek obmedzení na časti bezpečnostného subsystému (takéto správanie sa uloží, kým sa pracovné knihy nebudú v priečinku Trust).
Ak chcete konfigurovať spoľahlivé miesto, postupujte podľa týchto krokov.
Vyberte kartu Stuha Vývoja a kliknite na tlačidlo Bezpečnostné makrá. Na obrazovke sa zobrazí dialógové okno. Centrum riadenia bezpečnosti.
Kliknite na tlačidlo Pridať nové miesto.
Kliknite na tlačidlo PrehľadAk chcete zadať priečinok pre pracovné súbory, ktoré dôverujete.
Po zadaní spoľahlivého umiestnenia budú ľubovoľné makrá vykonávať štandardne pre všetky pracovné knihy v ňom.
V programe Excel 2013 sa zlepšil bezpečnostný model. Teraz boli spomrené súbory zošitov, ktoré boli predtým "dôvera", t.j. Po otvorení knihy Excel a kliknite na tlačidlo Povoliť obsah Excel si tento stav pamätá. V dôsledku toho táto kniha spadá do vypúšťania dôveryhodných a zbytočné otázky nie sú špecifikované počas jeho následného otvorenia.
Vytvorenie užívateľského rozhrania pomocou ovládacích prvkov
Spustenie makro pomocou kombinácie klávesov CTRL + SHIFT + Q, keď konsolidovaná tabuľová správa má len jednu makro. (Okrem toho používatelia potrebujú poznať túto kombináciu.) Ale predpokladajme, že chcete poskytnúť našim zákazníkom niekoľko makier vykonávajúcich rôzne akcie. V tomto prípade musíte poskytnúť zákazníkom jasné a jednoduché spôsob, ako spustiť každý makro, bez toho, aby sa uchýlili k zapamätaniu kľúčových kombinácií. Perfektné riešenie je jednoduché užívateľské rozhranie vo forme sady ovládacích prvkov, ako sú tlačidlá, posúvacie pásy a iné prostriedky, ktoré vám umožňujú vykonávať makrá s kliknutiami myšou.
Excel ponúka k dispozícii súbor nástrojov určených na vytvorenie používateľského rozhrania priamo v tabuľke. Tieto nástroje sa nazývajú prvky riadiaceho formulára. Základnou myšlienkou je, že môžete položiť kontrolu formulára v tabuľke a priradiť makro, ktorý bol zaznamenaný skôr. Po vymenovaní bude makro spustený na tomto prvku.
Kontroly formulárov nájdete v skupine Kontrolné prvky formulára Pásky Vývoja. Ak chcete otvori »paletu ovládacích prvkov, kliknite na túto skupinu na tlačidlo Vložiť (Obr. 4).
Obr. 4. Form Control Prvok Tlačidlo
Poznámka: Okrem ovládacích prvkov je tiež prítomná paleta ActiveX prvky. Hoci sú podobné, sú naprogramné rôzne objekty. Kontrolné prvky formuláras jeho obmedzenými funkciami a jednoduchými nastaveniami to bolo špeciálne navrhnuté na ubytovanie na pracovných listoch. V rovnakom čase ActiveX prvky Aplikujte hlavne vo vlastných formách. Spojte pravidlo, aby ste mohli poslať na pracovných hárkoch výlučne kontrolných kontrol.
Mali by ste vybrať ovládacie prvky, ktoré sú najvhodnejšie na vyriešenie úlohy. V tomto príklade potrebujú klienti poskytnúť možnosť aktualizovať konsolidovanú tabuľku s kliknutím na tlačidlo. Kliknite na ovládací prvok Tlačidlo, Presuňte ukazovateľ myši na miesto pracovného listu, kde by ste mali byť umiestnené tlačidlo a kliknite na myš.
Po umiestnení tlačidla do tabuľky sa otvorí dialógové okno. Priradiť makro objekt (Obr. 5). Vyberte požadované makro (v našom prípade - Upgrade, Zaznamenané skôr) a kliknite na tlačidlo V poriadku.
Obr. 5. Vyberte makro, ktoré sa majú priradiť k tlačidlu a kliknite na tlačidlo. V poriadku. V tomto prípade sa má použiť makro Upgrade
Po umiestnení všetkých potrebných ovládacích prvkov v tabuľke otáčania môžete formátovať tabuľku na vytvorenie základného rozhrania. Na obr. 6 Po formátovaní ukazuje súhrnnú tabuľku.
Zmeniť nahrané makro
V dôsledku nahrávania makra vytvorí program Excel modul, ktorý ukladá, ktorý ste dokončili. Všetky zaznamenané akcie sú reprezentované reťazcami kódovania VBA, z ktorých makro pozostáva. K správam o konsolidovanej tabuľke môžete pridať rôzne funkcie, nakonfigurujte kód VBA, aby ste získali požadované výsledky. Aby bolo jednoduchšie pochopiť, ako to všetko funguje, vytvorte nové makro, stiahnutie piatich prvých záznamov o zákazníkov. Kliknite na kartu Vývoja a kliknite na tlačidlo Makro. Dialógové okno zobrazené na obr. 7. Pomenujte vytvorené makro najprvNsakazchikov a zadajte miesto úspory Táto kniha. Kliknúť V poriadkuSpustenie nahrávania makra.
Po spustení nahrávania kliknite na šípku vedľa ihriska Meno zákazníka, Vyberte Filtrovať podľa hodnoty a možnosť Prvých 10 (Obr. 8A). V dialógovom okne, ktorá sa zobrazí, nastavte nastavenia, ako je znázornené na obr. 8b. Tieto nastavenia ukazujú, že zobrazujú údaje piatich zákazníkov, najlepšie z hľadiska predaja. Kliknúť V poriadku.
Obr. 8. Vyberte Filter (A) a nakonfigurujte parametre (B) na zobrazenie popolových päť zákazníkov na predaj.
Po úspešnom zázname všetkých akcií potrebných na extrahovanie päť zákazníkov na predaj, prejdite na kartu Vývoja a kliknite na tlačidlo Zastaviť nahrávanie.
Teraz máte makro, ktoré bude filtrovať konsolidovanú tabuľku, aby extrahoval päť zákazníkov na predaj. Musí sa urobiť tak, že makro reaguje na stav posuvníka, t.j. Pomocou posúvacieho panela musíte byť schopný určiť makro počet zákazníkov, ktorých údaje sa zobrazia v konsolidovanej tabuľke. Používanie posuvníka bude teda užívateľ schopný extrahovať päť najlepších, osem najlepších alebo tridsaťdva najlepších klientov podľa vlastného uváženia.
Ak chcete pridať, prejdite na tabuľku na tabuľku, prejdite na kartu. VývojaKliknite na tlačidlo VložiťVyberte ovládací prvok na palete Posuvník A umiestnite ho na pracovný list. Kliknite pravým tlačidlom myši na riadiaci prvok Posuvník Formátovať objekt. Otvorí sa dialógové okno Riadiaci formát (Obr. 9). V ňom vykonajte nasledujúce zmeny v nastaveniach: Parameter Minimálna hodnota Poznámka 1, parameter Maximálna hodnota - hodnota 200 av poli Komunikácia s bunkou Zadajte hodnotu $ m $ 2, takže v bunke M2 zobrazí posuvník. Kliknite na tlačidlo V poriadkuAplikovať predtým zadané nastavenia.
Teraz musíte porovnať novo nahraté makro najprvNsakazchikov S riadiacim prvkom Posuvníkna pracovnom liste. Kliknite pravým tlačidlom myši na riadiaci prvok Posuvník A v kontextovom menu vyberte príkaz Priradiť makroOtvorenie dialógového okna Makro Chemen. Priradenie posuvníka zaznamenané makro FIRSTNNENZAKAZCHIKOV. Makro sa vykoná vždy po kliknutí na posúvač. Otestujte vytvorený posuvník. Po kliknutí na pruhu sa makro začne FIRSTNNENZAKAZCHIKOV A číslo v bunke M2 sa zmení na zobrazenie stavu posuvného panela. Číslo v bunke M2 hrá dôležitú úlohu, pretože sa používa na viazanie makra do posuvníka.
Jediná vec, ktorá sa má urobiť, je prinútiť makro na spracovanie čísla v bunke M2, viazanie s posuvníkom. Ak to chcete urobiť, prejdite na kód VBA-Kód makra. Ak to chcete urobiť, prejdite na kartu Vývoja a kliknite na tlačidlo Makrá. Otvorí sa dialógové okno Makro (Obr. 10). Môžete spustiť, odstrániť a upraviť vybraté makro. Ak chcete zobraziť kód Makro VBA na obrazovke, vyberte Makro a kliknite na tlačidlo. Zmena.
Obr. 10. Prístup k kódexu makro VBA najprvNsakazchikov, Vyberte makro a kliknite na tlačidlo Zmena
Na obrazovke sa zobrazí na obrazovke Visual Basic Editor s kódom VBA (obr. 11). Vaším cieľom je vymeniť číslo 5 pevne zadané v kóde, nainštalovaný počas nahrávania makra, hodnota v bunke M2, ktorá je viazaná na posúvač. Spočiatku bola zaznamenaná makro, navrhnutá na zobrazenie prvých piatich zákazníkov, ktorí majú najväčší príjem.
Odstráňte číslo 5 z kódu a namiesto toho zadajte nasledujúci výraz:
ActiveSheet.range ("m2") .Value
Pridajte do makra dva reťazce na vyčistenie filtrov:
("A4") .Vyberte
ActiveSheets.Pivottables ("pivottable1") .pivoTfields ("názov zákazníka") .CleAlllfillfilters
Teraz by mal mať kód makra, ako je znázornené na obr. 12.
Zatvorte vizuálny základný editor a vráťte sa na konsolidovanú tabuľku. Otestujte posuvník, pretiahnite posúvač na hodnotu 11. Makro musí spustiť a filtrovať 11 záznamov o najlepších predajných klientov.
Synchronizácia dvoch konsolidovaných tabuliek pomocou jedného rozbaľovacieho zoznamu
Správa zobrazená na obr. 13 obsahuje dve súhrnné tabuľky. Každý z nich má pole stránky, ktoré vám umožní vybrať si predajný trh. Problém je v tom, že zakaždým, keď si vyberiete trh v poli stránky jedného pylónového stola, musíte si vybrať ten istý trh v poli stránok iného stola otáčania. Synchronizácia filtrov dvoch tabuliek v štádiu analýzy údajov nepredstavuje veľký problém, ale je tu šanca, že ste na ňu zabudli vy alebo vaši zákazníci.
Obr. 13. Dve súhrnné tabuľky obsahujú polia stránok, ktoré vykonávajú filtrovanie údajov na trhoch. Aby ste analyzovať údaje samostatného trhu, musíte synchronizovať obidva súhrnné tabuľky.
Jeden spôsob synchronizácie týchto súhrnných tabuliek je použitie rozbaľovacieho zoznamu. Myšlienkou je nahrávať makrá, čo si vyberie požadovaný trh z poľa Predajný trh V oboch tabuľkách. Potom musíte vytvoriť rozbaľovací zoznam a vyplňte ho názvami predajných trhov z dvoch súhrnných tabuliek. Nakoniec, zaznamenané makro sa musí zmeniť na filtrovanie oboch súhrnných tabuliek pomocou hodnôt z rozbaľovacieho zoznamu. Ak chcete vyriešiť túto úlohu, musíte vykonať nasledujúce kroky.
1. Vytvorte si nový makro a priradí ho meno. Synchmarkets.. Po spustení položky vyberte v poli Trh predaj oboch Súhrnné tabuľky Trhové predaj Kalifornia A zastavte makro záznam.
2. Zobrazte ovládacie prvky formulára na obrazovke a pridajte rozbaľovací zoznam na obrazovke.
3. Vytvorte pevne zadaný zoznam všetkých konsolidovaných tabuliek tabuľkových trhov. Prvý prvok zoznamu označuje hodnotu (všetko). Tento prvok by ste mali povoliť, ak si chcete vybrať všetky trhy v rozbaľovacom zozname.
4. V tomto štádiu by mala správa konsolidovaného stola vyzerať, ako je znázornené na obr. štrnásť.
Obr. 14. K dispozícii sú všetky požadované finančné prostriedky: makro meniaca pole Predajný trh Obidve pivovace tabuľky, rozbaľovací zoznam a zoznam všetkých predajných trhov obsiahnutých v konsolidovanej tabuľke
5. Kliknite pravým tlačidlom myši na rozbaľovací zoznam a vyberte príkaz v kontextovej ponuke. Formátovať objektKonfigurovať kontrolu.
6. Najprv nastavte zdrojový rozsah hodnôt používaných na vyplnenie rozbaľovacieho zoznamu, ako je znázornené na obr. 15. V tomto prípade hovoríme o zozname predajných trhov vytvorených podľa odseku 3. Potom zadajte bunku, ktorá zobrazí poradové číslo vybranej položky (v tomto príklade je bunka H1). Parameter Počet pery Určuje, koľko riadkov bude súčasne odráža v rozbaľovacom zozname. Kliknite na tlačidlo V poriadku.
Obr. 15. Nastavenia rozbaľovacieho zoznamu musia označiť zoznam predajných trhov ako počiatočný rozsah hodnôt a ako záväzný bod - na určenie bunky H1
7. Teraz máte možnosť vybrať si predajný trh v rozbaľovacom zozname, ako aj určiť súvisiace poradové číslo v bunke H1 (obr. 16). Vzniká otázka: Prečo je jeho indexová hodnota namiesto skutočného mena trhu? Pretože rozbaľovací zoznam vráti žiadne meno, ale číslo. Napríklad, keď vyberiete rozbaľovací zoznam Kalifornia v bunke H1, zobrazí sa hodnota 5. To znamená, že Kalifornia je piatym prvkom zoznamu.
Obr. 16. Rozbaľovací zoznam je teraz naplnený menami trhov a vybraný počet vybraných trhov sa zobrazí v bunke H1.
8. Ak chcete použiť poradové číslo namiesto názvu trhu, mali by ste ho poslať pomocou funkcie indexu.
9. Zadajte indexovú funkciu, ktorá konvertuje poradové číslo z bunky H1 na jasnú hodnotu.
10. Funkcia indexu má dva argumenty. Prvý argument predstavuje rozsah hodnôt zoznamu. Vo väčšine prípadov budete používať rovnaký rozsah, ktorý je vyplnená rozbaľovacia ponuka. Druhým argumentom je poradové číslo. Ak je poradové číslo zadané v bunke (napríklad v bunke H1, ako na obr. 17), môžete jednoducho odkazovať na túto bunku.
Obr. 17. Index funkcie v bunke I1 konvertuje poradové číslo uložené v bunke H1 na hodnotu. Ak chcete zmeniť makro, použijete hodnotu v bunkovej i1
11. Upraviť makro Synchmarkets.Použitie hodnoty v bunke I1 namiesto pevne zadanej hodnoty. Kliknite na kartu Vývoja a kliknite na tlačidlo Makrá. Dialógové okno sa zobrazí na obrazovke znázornenom na obr. 18. Vyberte Makro v ňom Synchmarkets. a kliknite na tlačidlo Upraviť.
Obr. 18. Ak chcete získať prístup k kódu Makro VBA, vyberte položku Makro Synchmarkets. a kliknite Zmena
12. Pri nahrávaní makro ste si vybrali predajný trh v Kalifornii z oblasti v oboch predbežných tabuľkách Predajný trh. Ako je zrejmé z obr. 19, California Market je teraz pevne nastavený vo VBA-Code makro.
13. Vymeňte hodnotu "Kalifornia" výrazu ActiveSheet.range ("I1") .Value, ktorý odkazuje na hodnotu v bunke I1. V tomto štádiu by mal makro kód vyzerať, ako je znázornené na obr. 20. Po zmene makra zatvorte editor Visual Basic a vrátite sa do tabuľky.
Obr. 20. Vymeňte hodnotu "California" pomocou výrazu ActiveSheet.range ("i1") .Value a zatvorte editor Visual Basic
14. Zostáva len na zabezpečenie vykonávania makro pri výbere predajného trhu v rozbaľovacom zozname. Kliknite pravým tlačidlom myši na rozbaľovací zoznam a vyberte parameter Priradiť makro. Vyberte makro Synchmarket. a kliknite na tlačidlo V poriadku.
15. Skryť riadky a stĺpce s poliami stránok v konsolidovaných tabuľkách, ako aj zoznam trhoch a indexových vzorcov, ktoré ste vytvorili.
Na obr. 21 znázorňuje konečný výsledok. Dostali ste užívateľské rozhranie, ktoré umožňuje zákazníkom vybrať predajný trh v oboch súhrnných tabuľkách pomocou jedného rozbaľovacieho zoznamu.
Keď vyberiete nový prvok v rozbaľovacom zozname, veľkosti stĺpcov sa automaticky zmenia na umiestnenie všetkých údajov zobrazených v nich. Podobné správanie programu sa nudí s formátovaním šablóny pracovného listu. Môžete zabrániť tomu, ak kliknete na zhrnutie pravého tlačidla myši a vyberte príkaz Publikované nastavenia tabuľky. Na obrazovke sa zobrazí dialógové okno, v ktorom chcete resetovať začiarkavacie políčko. Pri aktualizácii automaticky zmení šírku stĺpcov.
Poznámka je napísaná na základe knihy Jelen, Alexandra. . Kapitola 12.
Pomocou nástrojov programu Excel si môžete vybrať určité údaje z náhodného rozsahu, jedného stavu alebo viac. Na vyriešenie takýchto problémov sa používajú pole alebo makrárne alebo makrá. Zvážte príklady.
Ako urobiť vzorku v programe Excel podľa stavu
Pri použití vzorec poľa sa zvolené údaje zobrazia v samostatnom stole. Aká je výhoda tejto metódy v porovnaní s obvyklým filtrom.
Zdrojová tabuľka:
Najprv sa naučte urobiť vzorku na jednom číselnom kritériu. Úlohou je vybrať si z tabuľky tovaru s cenou nad 200 rubľov. Jedným zo spôsobov, ako vyriešiť, je použitie filtrovania. V dôsledku toho zostanú len tie výrobky, ktoré spĺňajú dotaz, zostanú v zdrojovej tabuľke.
Ďalším riešením je použitie vrstvy. Súvisiace režimy dotazov budú umiestnené v tabuľke samostatnej správy.
Najprv vytvoríme prázdny stôl vedľa pôvodného: Duplicitné hlavičky, počet riadkov a stĺpcov. Nový stôl zaberá rozsah E1: G10.Tube Pridelenie E2: E10 (stĺpec "Dátum") a zaviesť nasledujúci vzorec: ( }.
Aby ste získali vzorca MATSIF, stlačte kláves Ctrl + Shift + Enter Key Combiting. V nasledujúcom stĺpci - "Product" - predstavujeme podobný tuhý vzorec: ( ). Zmenilo sa iba prvý argument funkcie funkcie.
V stĺpci "Cena" predstavujeme rovnaký vzorec poľa zmenou prvého argumentu indexovej funkcie.
V dôsledku toho dostávame správu o tovare s cenou viac ako 200 rubľov.
Táto vzorka je dynamická: Keď zmeníte požiadavku alebo vzhľad v zdrojovej tabuľke nových produktov, správa sa automaticky zmení.
Číslo úlohy 2 - Vyberte si zo zdrojovej tabuľky Tovar, ktorý išiel na predaj 09/20/2015. To znamená, že kritérium výberu je dátum. Pre pohodlie predstavujeme požadovaný dátum na samostatnú bunku, i2.
Na vyriešenie problému sa používa podobný vzorec poľa. Len namiesto kritéria).
Takéto vzorce sa zavádzajú do iných stĺpcov (cm vyššie).
Teraz používame textové kritérium. Namiesto dátumu v bunke I2 predstavujeme text "Produkt 1". Mierne zmeňte vzorec poľa: ( }.
Takáto veľká funkcia odberu vzoriek v programe Excel.
Odber vzoriek na niekoľko podmienok v programe Excel
Po prvé, užívajte dve číselné kritériá:
Úlohou je vybrať tovar, ktorý stojí menej ako 400 a viac ako 200 rubľov. Kombinovať výrazy znamenia "*". Vzorec poľa je nasledovný: ( }.!}
Toto je pre prvý stĺpec tabuľky prehľadov. Pre druhú a tretiu, zmeňte prvý index funkcie argumentu. Výsledok:
Ak chcete urobiť vzorku v niekoľkých termínoch alebo numerických kritériách, používame podobné tuhé vzorce.
Náhodná vzorka v programe Excel
Keď užívateľ pracuje s veľkým množstvom údajov, môže byť potrebná náhodná vzorka. Každý riadok je možné priradiť náhodné číslo a potom aplikovať triedenie na odber vzoriek.
Súprava zdrojových údajov:
Najprv vložte dva prázdne stĺpce vľavo. V bunke A2, naznačujeme adhézny vzorec (). Spin to na celom stĺpci:
Teraz skopírujte stĺpec s náhodnými číslami a vložte ho do stĺpca V. Toto je potrebné, aby sa tieto čísla nemenia pri vytváraní nových údajov do dokumentu.
Ak chcete, aby hodnoty a nie vzorca, kliknite na pravé tlačidlo myši na stĺpci a vyberte nástroj "Špeciálny vložka". V okne, ktoré sa otvorí, položíme tick oproti položke "Hodnota":
Teraz môžete údaje triediť v stĺpci vo vzostupnom alebo zostupnom. Zmení sa aj postup reprezentácie počiatočných hodnôt. Vyberieme si ľubovoľný počet riadkov zhora alebo nižšie - získame náhodnú vzorku.
Rôzne (39)
Excel chyby a závady (3)
Ako získať zoznam jedinečných (neopakuje) hodnôt?
Predstavte si veľký zoznam rôznych mien, celé meno, tabuľkové čísla atď. A je potrebné opustiť zoznam všetkých rovnakých mien z tohto zoznamu, ale neopakujú - t.j. Odstráňte všetky duplicitné záznamy z tohto zoznamu. Ako inak sa nazýva: Vytvorte zoznam jedinečných prvkov, zoznam non-opakujúcich sa bez duplikátov. Na tento účel existuje niekoľko spôsobov: Vložené nástroje Excel, vstavané vzorce a konečne s kódom Visual Basic for Application (VBA) a súhrnné tabuľky. V tomto článku zvážte každú z možností.
Použitie vstavaných vlastností programu Excel 2007 a vyššie
V programe Excel 2007 a 2010 je jednoduchšie jednoduché - je tu špeciálny tím, ktorý sa nazýva -. Nachádza sa na karte Údaje (údaje) pododdiel Dátové nástroje (dátové nástroje)
Ako používať tento príkaz. Zvýrazniť stĺpec (alebo viac) S týmito údajmi, v ktorých chcete vymazať duplicitné položky. Prejdite na kartu Údaje (údaje) -Odstráňte duplikáty (odstrániť duplikáty).
Ak vyberiete jeden stĺpec, ale bude existovať viac stĺpcov s údajmi (alebo aspoň jeden stĺpec), potom program Excel ponúkne výber: rozšíriť rozsah vzorky podľa tohto stĺpca alebo ponechať výber, ako je a vymazať údaje len v vyhradený rozsah. Je dôležité si uvedomiť, že ak nerozbijete rozsah, údaje sa zmenia len v jednom stĺpci a údaje v susednej stĺpci zostanú bez najmenších zmien.
Okno sa objaví s duplicitnými parametrami odstránenia 
Ticks oproti týmito stĺpcami, duplikáty, v ktorých musíte odstrániť a kliknite na tlačidlo OK. Ak sa záhlavia údajov nachádzajú aj v vyhradenom rozsahu, potom lepšie uveďte vlajku Moje údaje obsahujú titulkynáhodne neodstrániť údaje v tabuľke (Ak sa zrazu úplne zhodujú s hodnotou titulu).
Metóda 1: Rozšírené filter
V prípade programu Excel 2003 je všetko komplikovanejšie. Nie je takýto nástroj Odstrániť duplikát. Ale je tu taký nádherný nástroj ako Pokročilý filter. V roku 2003 sa tento nástroj nachádza v roku 2003 Dáta -Filter -Pokročilý filter. Krása tejto metódy je s ním, nemôžete pokaziť zdrojové údaje, ale vytvoriť zoznam v inom rozsahu. V rokoch 2007-2010 Excel má tiež, ale trochu kalené. Nachádza sa na karte Údaje (údaje)Skupina Zoradiť a filter (triedenie a filter) - Pokročilé)
Ako ho používať: Spustite zadaný nástroj - Zobrazí sa dialógové okno: 
- Liečba: Vyberať Skopírujte výsledok na iné miesto (kópia na iné miesto).
- Rozsah zdrojov: Vyberte rozsah s údajmi (V našom prípade to je A1: A51).
- Rozsah kritérií (rozsah kritérií): V tomto prípade opustíme prázdny.
- Výsledok vložte do rozsahu (kópiou): Uveďte prvú bunku na výstupné dáta - akékoľvek prázdne (na obrázku - E2).
- Zaškrtnúť Iba jedinečné záznamy (len jedinečné záznamy).
- Zhmem. V poriadku.
Poznámka: Ak chcete vložiť výsledok na iný list, potom len zadajte iný list nebude fungovať. Môžete určiť bunku na inom hárku, ale ... Alas a Ah ... Excel vám poskytne správu, že nemôže kopírovať údaje do iných listov. Ale toto môže byť obchádzané a celkom jednoduché. Musíme bežať Pokročilý filter Z tohto listu, na ktorý chceme zadať výsledok. A ako zdrojové údaje, vyberte položku Údaje z akéhokoľvek listu - je povolené.
Môžete tiež urobiť výsledok v iných bunkách, ale na filtrovanie údajov na mieste. Údaje z toho nebudú ovplyvnené - to bude obvyklé filtrovanie údajov.
Ak to chcete urobiť, stačí vybrať položku spracovania Zoznam filtrov na mieste (filter zoznam, na mieste).
Metóda 2: Formuláry
Táto metóda je ťažšia v chápaní neskúsených užívateľov, ale vytvára zoznam jedinečných hodnôt bez zmeny počiatočných údajov. Je to dynamickejšie: Ak zmeníte údaje v tabuľke Zdroj, výsledok sa zmení. Niekedy je to užitočné. Pokúsim sa vysvetliť na prstoch Čo a čo: napríklad zoznam údajov sa nachádza v stĺpci A (A1: A51, kde A1 je hlavička). Zobrazenie zoznamu Budeme v stĺpci s, počnúc C2 bunkou. Vzorec v C2 bude nasledovný:
(\u003d Index ($ A $ 2: $ A $ 51; najmenší (IF (IF (počítaný ($ C $ 1: C1; $ A $ 2: $ A $ 51) \u003d 0; riadok ($ A $ 1: $ A 50 dolárov)); 1)))
(\u003d Index ($ A $ 2: $ A $ 51; MALÉ (IF (IFNAGE (CIGNIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) \u003d 0; ROW ($ A $ 1: $ A $ A $ 50)); 1)))
Podrobná analýza práce tohto vzorca je uvedená v článku:
Treba poznamenať, že tento vzorec je vzorec poľa. Toto možno povedať, že kučeravé konzoly, v ktorých bol tento vzorec uzavretý. A tento vzorec sa zavádza do bunkovej kombinácie - Cttrl+Posunúť.+Zadať. Potom, čo sme vstúpili do tohto vzorca v C2, musíme ho skopírovať a prilepiť do niekoľkých riadkov, aby sme presne zobrazili všetky jedinečné položky. Akonáhle sa vzorec v dolných bunkách vráti #Number! - To znamená, že všetky položky sú zobrazené a pod vzorec nedáva zmysel. Aby ste sa chybovali, aby sa zabránilo a vytvorili vzorca viac univerzálny (bez rozšírenia zakaždým, keď sa vyskytne chyba), môžete použiť nehlasovú kontrolu:
Pre Excel 2007 a vyššie:
(\u003d Ak index (index ($ A $ 2: $ A $ 51; najmenší (ak (počítaný ($ C $ 1: C1; $ A $ 2: $ A $ 51) \u003d 0; reťazec ($ A $ $ 1: $ A $ 50); 1)); ""))
(\u003d Iferror (index ($ a $ 2: $ A $ 51; Malé (IF (CUTIFIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) \u003d 0; ROW ($ A $ 1: $ 50 USD)); 1)); ""))
Pre Excel 2003:
(\u003d Ak (EOSH (najmenší (ak (BID ($ C $ 1: C1; $ A $ 2: $ A $ 51) \u003d 0; reťazec ($ A $ 1: $ A $ 50)); 1)) "" "Index ($ A $ 2: $ A $ 51; najmenší (ak (IF (Countles ($ C $ 1: C1; $ A $ 2: $ A $ 51) \u003d 0; riadok ($ A $ 1: $ A $ 50); 1)))))
(\u003d Ak (ISERR (IF (ISERIF ($ C $ 1: C1; $ A $ 2: $ A $ 51) \u003d 0; ROW ($ A $ 1: $ A $ 50)); 1))); " index ($ A $ 2: $ A $ 51; Malé (IF (IF (IF (IF (IF (IF (IF (IF (IF (IF (IF (IF (IF (IF (IF (IF (IF) SMALLY (IF (IF ($ 1: C1; $ A $ 2: $ 51) \u003d 0; ROW ($ A $ 1: $ A $ 50 )); 1)))
Potom namiesto chyby #Number! (#NUM!) Budete mať prázdne bunky (Samozrejme, nie je úplne prázdne - s vzorcami :-)).
O niečo viac o rozdieloch a nuans vzorca vzorca a ak sa v tomto článku prečíta (EOSH (EOSH možno čítať v tomto článku: ako v bunke so vzorcom namiesto chybovej show 0
Metóda 3: kód VBA
Tento prístup bude vyžadovať povolenie makier a základné vedomosti o spolupráci s nimi. Ak si nie ste istí svojím vedomím, odporúčam vám, aby ste si prečítali tieto články:
- Čo je to makro a kde hľadať to? Článok je pripojený video tutorial
- Čo je modul? Aké sú moduly? Bude potrebné pochopiť, kde chcete vložiť nižšie uvedené kódy.
Obidve nižšie uvedené kódy by mali byť umiestnené Štandardný modul. Makrá musia byť povolené.
Počiatočné údaje budú ponechané v rovnakom poradí - zoznam s údajmi sa nachádza v stĺpci "A" (A1: A51, kde A1 - názov). Vyberte len zoznam, ktorý nebudeme v stĺpci C, ale v stĺpci E2, počnúc bunkou E2:
Sub Extract_unique () DIM VETEM, AVARR, LI AKO LONG REDIM AVARR (1 na ROWS.COUNT, 1 až 1) s novou kolekciou pri chybách Obnovenie ďalšieho pre každý VETEM v rozsahu ("A2", bunky (ROWS.COUNT, 1) .END (XLUP)). Hodnota "(! Lang: Bunky (ROWS.COUNT, 1) .END (XLUP) - Určuje poslednú plnú bunku v prídavnom telese, CSTR (VETEM), ak je ERR \u003d 0 potom li \u003d li + 1: Avarr (Li, 1) \u003d Vita Else: Err.Clear End, ak budúci koniec, ak je potom .res (li).,
S týmto kódom môžete extrahovať jedinečné nielen z jedného stĺpca, ale aj z ľubovoľného rozsahu stĺpcov a riadkov. Ak namiesto reťazca
Rozsah ("A2", Cells(Rows.Count, 1).End(xlUp)).Value !}
Zadajte výber. Výsledok kódu kódu bude zoznam jedinečných prvkov z rozsahu prideleného na aktívnom hárku. Až potom by bolo pekné zmeniť výstupnú bunku na zmenu - namiesto
Dajte ten, v ktorom nie sú žiadne údaje.
Môžete tiež zadať konkrétny rozsah:
Rozsah ("C2", bunky (riadky.Count, 3) .END (XLUP)).
Univerzálny kód pre výber jedinečných hodnôt
Nižšie uvedený kód je možné použiť pre všetky rozsahy. Stačí začať ho, špecifikujte rozsah s hodnotami pre výber iba neopakovania (viac ako jeden stĺpec je pridelený) a bunka na výstup výsledok. Špecifikované bunky sa zobrazia, iba jedinečné hodnoty budú vybrané z nich (prázdne bunky sú preskočené) a výsledný zoznam sa zaznamená, počnúc špecifikovanou bunkou.
| Sub Extract_unique () Dim X, Avarr, Li ako dlhé Dim AVVALS DIM RVALS ako rozsah, RRESULTCELL as rozsah pri chybách pokračovať "Žiadosť o adresu buniek vyberte jedinečné hodnoty Nastaviť RVALS \u003d Application.inputBox ( "Zadajte rozsah buniek na odber vzoriek jedinečných hodnôt", "Žiadosť o dáta", "A2: A51", typ: \u003d 8) Ak RVALS nie je potom "Ak je stlačené tlačidlo Zrušiť Exit Sub End, ak "Ak je špecifikovaná iba jedna bunka - nie je zmysel vybrať si Ak rvals.count \u003d 1 potom msgbox "Ak chcete vybrať jedinečné hodnoty, ktoré potrebujete na určenie viac ako jednej bunky", Vbinformation, "www.syt" výstupný subjek, ak "Odrežte prázdne reťazce a stĺpce mimo pracovného rozsahu Nastavte RVALS \u003d Pretieranie (RVALS, RVALS.PARENT.UsesRange) "Ak sú uvedené iba prázdne bunky mimo pracovného rozsahu Ak rvals nie je nič, potom msgbox "Nie je dostatok údajov na výber hodnôt", vbinformation, "www.syt" výstupný subjek, ak AVVALS \u003d RVALS.VALUE "Žiadať bunku na výstup výsledku Nastaviť RRESULTCELL \u003d APLIKACIE "Zadajte bunku, aby ste vložili vybrané jedinečné hodnoty", "Žiadosť o dáta", "E2", zadajte: \u003d 8) Ak RRESULTCELL nie je nič "Ak je stlačené tlačidlo Zrušiť Exit Sub End, ak "Určite maximálny možný rozmer poľa pre výsledok Redim Avarr (1 na ROWS.COUNT, 1 až 1) "Používanie objektu zberu (zbierka) "Vyberte iba jedinečné položky, "Pretože zbierky nemôžu obsahovať opakované hodnoty S novou kolekciou o chybách pokračovať v ďalšom pre každé X v AVVALS, ak je LEN (CSTR (X)) "Preskočíme prázdne bunky .Add x, cstr (x) "Ak je pridaný prvok už v kolekcii - nastane chyba "Ak nie sú žiadne chyby - táto hodnota ešte nie je vykonaná, "Pridať do výsledného poľa Ak je ERR \u003d 0 potom li \u003d li + 1 avarr (li, 1) \u003d x inak "Uistite sa, že ste čistili chybový objekt Err.Clear End If Akoniec, ak budúci koniec "Zaznamenajte výsledok na list, počnúc zadanou bunkou Ak li potom rresulcell.cells (1, 1) .resize (Li) .Value \u003d Avarr End Sub |
SUB EXTRACT_UNIQUE () DIM X, AVARR, LI Ako dlhé Dim Avvvals Dim RVALS ako rozsah, RRESULTCELL as rozsah pri chybách Obnovenie ďalšieho "Požiadajte o adresu buniek, aby ste vybrali unikátnu set RVALS \u003d Application.inputBox (" Zadajte rozsah buniek Na odber vzoriek jedinečných hodnôt "," požiadavka na údaje "," A2: A51 ", typ: \u003d 8) Ak RVALS nie je nič, potom" ak je stlačené tlačidlo výstupu, "ak je zadaná iba jedna bunka Nemá zmysel vybrať si, či rvals.count \u003d 1 potom msgbox "Ak chcete vybrať jedinečné hodnoty, musíte zadať viac ako jednu bunku", vbinformation, "www.sight" ukončiť subjek, ak "spev prázdne reťazce a stĺpce mimo prevádzkového rozsahu Nastaviť RVALS \u003d Pretieranie (RVALS, RVALS.PARENT.UsectRange) "Ak sú iba prázdne bunky označené mimo, ak Rvals nie je nič, potom Msgbox" Nie je dostatok údajov na výber hodnôt ", vbinformation, www..value (! LANG: požiadavka A Cell na výstup Set RresulCell \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!}
Metóda 4: Súhrnné tabuľky
Niekoľko neštandardných spôsobov extrakcie jedinečných hodnôt.
- Vyberte jeden alebo viac stĺpcov v tabuľke, prejdite na kartu Vložka (vložka) Hojnosť Tabuľka (tabuľka) -Súhrnná tabuľka (otvárací)
- V dialógovom okne Vytvorenie otočného stola Skontrolujte, či je zvýraznená správnosť rozsahu údajov (alebo nainštalovať nový zdroj údajov)
- zadajte umiestnenie konsolidovanej tabuľky:
- Na nový list (nový pracovný hárok)
- Na existujúcom hárku (existujúci pracovný hárok)
- potvrďte vytvorenie tlačidla stlačením tlačidla Ok.
Pretože Zhrnutie Tabuľky Pri spracovaní údajov, ktoré sú umiestnené v oblasti riadkov alebo stĺpcov, z nich sú prevzaté iba jedinečné hodnoty pre neskoršiu analýzu, nevyžaduje sa od nás Ani účet, s výnimkou vytvorenia súhrnnej tabuľky a položte Údaje požadovaného stĺpca do oblasti reťazcov alebo stĺpcov.
V príklade súboru pripojeného k článku:
Aká je nepohodlie práce s konsolidovaným v tomto prípade: pri zmene zdrojových údajov sa konsolidovaná tabuľka bude musieť byť aktualizovaná manuálne: prideliť akúkoľvek bunku konsolidovaného stola - zatiaľ čo tlačidlo myši - Obnoviť) alebo karta Údaje (údaje) -Aktualizujte všetky (Obnoviť všetko) -Obnoviť). A ak sa zdrojové údaje aktualizujú dynamicky a horšie - bude potrebné znovu označiť rozsah zdrojového dát. A ešte jeden mínus - údaje v konsolidovanej tabuľke nemožno zmeniť. Preto, ak potrebujete pracovať s výsledným zoznamom v budúcnosti, potom po vytvorení požadovaného zoznamu musíte skopírovať pomocou Pomocník a vložiť ju na požadovaný list.
Ak chcete lepšie porozumieť všetkým činnostiam a naučiť sa spracovať konsolidované tabuľky, odporúčam, aby som čítal článok Všeobecné informácie o súhrnných tabuľkách - video tutoriál je pripojený k nemu, v ktorom vidím vizuálne jednoduchosť a pohodlie práce s hlavnými možnosťami Konsolidované tabuľky.
V aplikovaným príkladom, okrem opísaných techník, mierne zložitejšie variácie extrakcie jedinečného vzorca a kódu je zaznamenané, a to: odstránenie jedinečných prvkov podľa kritérií. Aká je otázka: ak je v jednom stĺpci priezviska, a v druhej (c) niektoré údaje (v súbore je mesiace) a je potrebné vyradiť jedinečné hodnoty stĺpca len pre vybrané priezvisko . Príklady podobnej extrakcie unikátne sú umiestnené na hárku. Extrakt podľa kritéria.
Príklad sťahovania:
(108,0 kib, 14 152 stiahnutí)
Článok pomohol? Zdieľajte odkaz s priateľmi! Video lekcie
