Podstatou výběrového dotazu je výběr řádků ze zdrojové tabulky, které splňují určitá kritéria (podobně jako u standardní). Vybereme hodnoty ze zdrojové tabulky pomocí. Na rozdíl od použití ( CTRL + SHIFT + L nebo Data / Třídit a filtrovat / filtrovat) vybrané řádky budou umístěny do samostatné tabulky.
V tomto článku budeme uvažovat o nejběžnějších dotazech, například: výběr řádků tabulky, jejichž hodnota z číselného sloupce spadá do zadaného rozsahu (intervalu); výběr řádků, pro které je datum určité období; úkoly se 2 textovými kritérii a další. Začněme jednoduchými dotazy.
1. Jedno numerické kritérium (vyberte ty Produkty, u nichž je cena vyšší než minimální)
ukázkový soubor, list Jedním kritériem je číslo ).
V samostatné tabulce je nutné zobrazit pouze ty záznamy (řádky) ze zdrojové tabulky, které mají cenu vyšší než 25.
Tento a následné úkoly můžete snadno vyřešit pomocí. Chcete-li to provést, vyberte záhlaví tabulky Zdroj a stiskněte CTRL + SHIFT + L... Pomocí rozevíracího seznamu v záhlaví Ceny vyberte Číselné filtry ..., poté nastavte požadované podmínky filtrování a klikněte na OK.
Zobrazí se záznamy odpovídající kritériím výběru.
Dalším přístupem je použití. Na rozdíl od vybraných řádků budou umístěny v samostatné tabulce - druh, který lze například formátovat v jiném stylu než v tabulce Zdroj nebo provést jiné úpravy.
Kritérium (minimální cena) umístěte do buňky E6 , tabulka filtrovaných dat - v rozsahu D10: E19 .
Nyní vybereme rozsah D11: D19 (sloupec Produkt) a zadejte:
INDEX (A11: A19;
MALÉ (POKUD ($ E $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-LINE ($ B $ 10))
Namísto ENTER stiskněte klávesovou zkratku CTRL + SHIFT + ENTER(maticový vzorec bude).
E11: E19 (sloupec Cena), kde zadáme podobný:
INDEX (B11: B19;
MALÉ (POKUD ($ E $ 6<=B11:B19;СТРОКА(B11:B19);"");СТРОКА()-СТРОКА($B$10))
-LINE ($ B $ 10))
Ve výsledku získáme novou tabulku, která bude obsahovat pouze produkty s cenami, které nejsou nižší než ceny uvedené v buňce E6 .
Abychom ukázali dynamiku přijatého požadavku na vzorek, zavedeme do E6 hodnota 55. Do nové tabulky se dostanou pouze 2 záznamy.
Pokud do tabulky Zdroj přidáte nový produkt s cenou 80, automaticky se do nové tabulky přidá nový záznam.
Poznámka... Můžete také použít a k zobrazení filtrovaných dat. Volba konkrétního nástroje závisí na úkolu, kterému uživatel čelí.
Pokud se vám nelíbí používání maticový vzorec který vrací více hodnot, můžete použít jiný přístup, který je popsán v následujících částech: 5.a, 7, 10 a 11. V těchto případech se používají.
2. Dvě numerická kritéria (vyberte produkty, u nichž cena spadá do rozsahu)
Nechť existuje počáteční tabulka se seznamem produktů a cen (viz. ukázkový soubor, listRozsah čísel).
Umístěte kritéria (dolní a horní hranice ceny) do rozsahu E5: E6 .
Ty. pokud cena zboží spadá do stanoveného intervalu, objeví se takový záznam v nové tabulce filtrovaných dat.
Na rozdíl od předchozí úlohy vytvoříme dva: Produkty a Ceny (můžete se bez nich obejít, ale při psaní vzorců jsou pohodlné). Odpovídající vzorce by měly vypadat ve Správci jmen ( Vzorce / Definovaná jména / Správce jmen) následovně (viz obrázek níže).
Nyní vybereme rozsah D11: D19 a představujeme:
INDEX (Zboží;
NEJMÉNĚ(
IF (($ E $ 5<=Цены)*($E$6>= Ceny); LINE (Ceny); "");
Namísto ENTER stiskněte klávesovou zkratku CTRL + SHIFT + ENTER.
S rozsahem provedeme stejnou manipulaci E11: E19 kde zavedeme podobný:
INDEX (Ceny;
NEJMÉNĚ(
IF (($ E $ 5<=Цены)*($E$6>= Ceny); LINE (Ceny); "");
LINE (Ceny) -LINE ($ B $ 10)) - LINE ($ B $ 10))
Ve výsledku získáme novou tabulku, která bude obsahovat pouze produkty, u nichž ceny spadají do intervalu uvedeného v buňkách E5 a E6 .
Abychom ukázali dynamiku přijatého Reportu (Žádost o výběr), vstoupíme dovnitř E6 hodnota 65. Do nové tabulky bude přidán jeden další záznam z tabulky Zdroj, který splňuje nové kritérium.
Pokud do tabulky Zdroj přidáte nový produkt s cenou v rozmezí od 25 do 65, bude do nové tabulky přidán nový záznam.
Ukázkový soubor obsahuje také maticové vzorce, které zpracovávají chyby, když sloupec Cena obsahuje chybovou hodnotu, například # DIV / 0! (viz list Chyba při zpracování).
Následující úkoly jsou řešeny podobným způsobem, takže je nebudeme podrobně zvažovat.
3. Jedno kritérium Datum (Vyberte ty Produkty, u kterých se Datum dodání shoduje se zadaným)
ukázkový soubor, listJedno kritérium - datum).
K výběru řádků se používají maticové vzorce podobné úkolu 1 (místo kritéria<= используется =):
=INDEX (A12: A20; SMALL (IF ($ E $ 6 = B12: B20; LINE (B12: B20); ""); LINE (B12: B20) -LINE ($ B $ 11)) - LINE ($ B 11 $))
INDEX (B12: B20; MALÝ (IF ($ E $ 6 = B12: B20; LINE (B12: B20); ""); LINE (B12: B20) -LINE ($ B $ 11)) - LINE ($ B 11 $))
4. Dvě kritéria Datum (Vyberte ty Produkty, pro které Datum dodání spadá do rozsahu)
Předpokládejme, že existuje Počáteční tabulka se seznamem dat zboží a dodání (viz. ukázkový soubor, listČasové období).
Upozorňujeme, že sloupec Datum NENÍ TŘÍDĚNÝ.
Řešení1: Můžete použít k filtrování řádků.
Vstupte do buňky D12 vzorec pole:
INDEX (A $ 12: A $ 20;
VELKÉ (($ E $ 6<=$B$12:$B$20)*($E$7>= $ B $ 12: $ B $ 20) * (LINE ($ B $ 12: $ B $ 20) -LINE ($ B $ 11));
$ J $ 12-LINE (A12) + LINE ($ B $ 11) +1))
Poznámka: Po zadání vzorce namísto klávesy ENTER stiskněte kombinaci kláves CTRL + SHIFT + ENTER. Tato klávesová zkratka se používá k zadávání maticových vzorců.
Zkopírujte vzorec pole dolů na požadovaný počet buněk. Vzorec vrátí pouze ty hodnoty produktů, které byly dodány ve stanoveném časovém období. Zbytek buněk bude obsahovat chyby # ČÍSLO! Chyby v ukázkový soubor (časové období listu 4) .
Podobný vzorec je třeba zadat pro data ve sloupci E.
V cele J12 vypočítal počet řádků zdrojové tabulky, které splňují kritéria:
COUNTIFS (B12: B20; "> =" & $ E $ 6; B12: B20; "<="&$E$7)
Řádky zdrojové tabulky, které splňují kritéria, jsou.
Řešení2: Chcete-li vybrat řádky, můžete použít maticové vzorce podobné Task2 (tj.):
=INDEX (A12: A20; MALÝ (POKUD (($ E $ 6<=B12:B20)*($E$7>= B12: B20); LINE (B12: B20); ""); LINE (B12: B20) -LINE ($ B $ 11)) - LINE ($ B $ 11))
INDEX (B12: B20; MALÝ (POKUD (($ E $ 6<=B12:B20)*($E$7>= B12: B20); LINE (B12: B20); ""); LINE (B12: B20) -LINE ($ B $ 11)) - LINE ($ B $ 11))
Chcete-li zadat první vzorec, vyberte rozsah buněk G12: G20 ... Po zadání vzorce namísto stisknutí klávesy ENTER stiskněte kombinaci kláves CTRL + SHIFT + ENTER.
Řešení3: Pokud je sloupec Datum TŘÍDĚNÝ, nemusíte používat maticové vzorce.
Nejprve musíte vypočítat první a poslední pozici řádků, které splňují kritéria. Poté řádky vytiskněte.
Tento příklad opět jasně ukazuje, o kolik je snadnější psát vzorce.
5. Jedno kritérium Datum (Vyberte zboží, u kterého Datum dodání není dříve / nejpozději než zadané)
Předpokládejme, že existuje Počáteční tabulka se seznamem dat zboží a dodání (viz. ukázkový soubor, list Jedno kritérium - datum (nejpozději) ).
Chcete-li vybrat řádky, jejichž datum není starší (včetně samotného data), použije se vzorec pole:
=INDEX (A12: A20; MALÝ (POKUD ($ E $ 7<=B12:B20;СТРОКА(B12:B20);"");СТРОКА(B12:B20)-СТРОКА($B$11))-СТРОКА($B$11))
Ukázkový soubor také obsahuje vzorce pro podmínky: Ne dříve (bez); Ne později (včetně); Ne později (bez).
7. Jedno textové kritérium (vyberte produkty určitého typu)
Nechť existuje počáteční tabulka se seznamem produktů a cen (viz. ukázkový soubor, listJedno kritérium - text).
8. Dvě textová kritéria (vyberte zboží určitého typu dodané v daném měsíci)
Nechť existuje počáteční tabulka se seznamem produktů a cen (viz. ukázkový soubor, list 2 kritéria - text (I) ).
INDEX ($ A $ 11: $ A $ 19;
MALÉ (POKUD (($ F $ 6 = $ A $ 11: $ A $ 19) * ($ F $ 7 = $ B $ 11: $ B $ 19); LINE ($ A $ 11: $ A $ 19) -LINE ($ A $ 10); 30); LINE (INDIRECT ("A1: A" & ROWS ($ A $ 11: $ A $ 19)))))
Výraz ($ F $ 6 = $ A $ 11: $ A $ 19) * ($ F $ 7 = $ B $ 11: $ B $ 19) nastavuje obě podmínky (položka a měsíc).
Výraz LINE (INDIRECT ("A1: A" & ROWS ($ A $ 11: $ A $ 19))) formy (1: 2: 3: 4: 5: 6: 7: 8: 9), tj. čísla řádků v tabulce.
9. Dvě textová kritéria (vyberte produkty určitých typů)
Nechť existuje počáteční tabulka se seznamem produktů a cen (viz. ukázkový soubor, list2 kritéria - text (NEBO)).
Na rozdíl od úlohy 7 vyberte řádky se zbožím 2 typů ().
Vzorec pole se používá k výběru řádků:
INDEX (A $ 11: A $ 19;
VELKÉ ((($ E $ 6 = $ A $ 11: $ A $ 19) + ($ E $ 7 = $ A $ 11: $ A $ 19)) * * (ŘÁDEK ($ A $ 11: $ A $ 19 ) -LINE ($ A $ 10)); COUNTIF ($ A $ 11: $ A $ 19; $ E $ 6) + COUNTIF ($ A $ 11: $ A $ 19; $ E $ 7) -ROWS ($ A $ 11: A11) +1))
Stav ($ E $ 6 = $ A $ 11: $ A $ 19) + ($ E $ 7 = $ A $ 11: $ A $ 19) zaručuje, že ze žlutých buněk (Produkt2 a Produkt3) bude vybrán pouze určený druh zboží. U úkolu se používá znaménko + (přidání) (musí být splněno alespoň 1 kritérium).
Výše uvedený výraz vrátí pole (0: 0: 0: 0: 1: 1: 1: 0: 0). Vynásobte to výrazem LINE ($ A $ 11: $ A $ 19) -LINE ($ A $ 10), tj. zapnuto (1: 2: 3: 4: 5: 6: 7: 8: 9) získáme řadu pozic (čísla řádků tabulky), která splňují kritéria. V našem případě to bude pole (0: 0: 0: 0: 5: 6: 7: 0: 0).
Jako příklad uvádíme řešení následujícího problému: Vyberte Produkty, jejichž cena leží v určitém rozmezí a opakuje se zadaný početkrát nebo více.
Vezměme si tabulku zásilek zboží jako počáteční.
Předpokládejme, že nás zajímá, kolik a jaké zásilky zboží byly dodány za cenu 1 000 rublů. až 2000r. (kritérium 1). Kromě toho musí existovat alespoň 3 strany se stejnou cenou (kritérium 2).
Řešením je maticový vzorec:
MALÝ (ŘÁDEK ($ A $ 14: $ A $ 27) * * ($ C $ 14: $ C $ 27> = $ B $ 7) * ($ C $ 14: $ C $ 27<=$C$7)*($D$14:$D$27>= $ B $ 10); F14 + ($ G $ 8- $ G $ 9))
Tento vzorec vrací čísla řádků, která splňují obě kritéria.
Vzorec = SUMPRODUCT (($ C $ 14: $ C $ 27> = $ B $ 7) * ($ C $ 14: $ C $ 27<=$C$7)*($D$14:$D$27>= $ B $ 10)) spočítá počet řádků, které splňují kritéria.
11. Používáme hodnotu kritéria (Any) nebo (All)
V ukázkový soubor na listu "11. Kritérium Libovolný nebo (Vše)" tato varianta kritéria byla implementována.
Vzorec v tomto případě musí obsahovat funkci IF (). Pokud je vybrána hodnota (Vše), použije se vzorec k zobrazení hodnot bez zohlednění tohoto kritéria. Pokud je vybrána jakákoli jiná hodnota, pak kritérium funguje obvyklým způsobem.
IF ($ C $ 8 = "(Vše)";
MALÝ ((LINE ($ B $ 13: $ B $ 26) -LINE ($ B $ 12)) * * ($ D $ 13: $ D $ 26> = $ D $ 8); F13 + ($ G $ 6 - $ G $ 7));
MALÉ ((LINE ($ B $ 13: $ B $ 26) -LINE ($ B $ 12)) * * ($ D $ 13: $ D $ 26> = $ D $ 8) * ($ C $ 13: $ C $ 26 = $ C $ 8); F13 + ($ G $ 6- $ G $ 7)))
Zbytek vzorce je podobný těm, které byly diskutovány výše.
Načítání dat
Vytvořte zprávu o vzorku z listu 5 ve sloupci "Kvalitativní výkon, procenta." (z listu 8, tabulky 7)
Chcete-li vzorkovat data, musíte provést následující kroky:
Určete počet prvků nového pole podle dané podmínky zadáním proměnné pomocí operátoru InputBox
Deklarovat a znovu deklarovat nové pole
Vytvořte nové pole. Chcete-li to provést, musíte nastavit číslo prvního prvku nového pole u = 1. Poté se provede cyklus, ve kterém je zapsána podmínka výběru pro sloupec "Kvalitativní výkon, procenta." Pokud je výsledek kontroly pravdivý, pak se prvek analyzovaného pole stane prvkem nového pole.
Zobrazit nový prvek na listu 8
Sub ReportSample ()
Listy („List8“). Vybrat
Dim A () jako varianta
ReDim A (1 až n1, 1 až m)
VVOD "List5", A, n1, m, 4
C = InputBox ("Zadejte podmínku")
Listy („List8“). Buňky (5.11) = C
Pro i = 1 až n1
Pokud A (i,
8) >
d = d + 1
Listy („List8“). Buňky (5,10) = d
Dim B () jako varianta
ReDim B (1 až d, 1 až m)
Pro i = 1 až n1
Pokud A (i,
8)> Listy („List8“). Buňky (5.11)
Pro j = 1 až m
B (u, j) = A (i, j)
u = u + 1
Pro i = 1 až d
Pro j = 1 až m
Listy („List8“). Buňky (i + 4, j) = B (i, j)
Obr. Data tabulky po načtení
Vytvoření automatického makra pro výběr
Zapneme záznam makra. Nástroje> Makro> Zahájit nahrávání> OK. Na místě tlačítka pro zastavení záznamu se objeví čtverec. Na Sheet5 (report) vyberte tabulku bez záhlaví a součtů, zkopírujte ji do Sheet10 (autoselection). Vyberte tabulku bez záhlaví a v položce nabídky vyberte Data> Filtr> Automatický filtr> vyberte podmínku> OK. Označíme sloupec, podle kterého budeme třídit. Dokončujeme práci makra.
Sub Macro2Sample ()
"Makro2Nastavení makra
Listy („List5“). Vybrat
Výběr. kopírovat
Listy („List9“). Vybrat
ActiveSheet. Vložit
Rozsah („H5: H17“). Vybrat
Aplikace. CutCopyMode = False
Výběr. Automatický filtr
ActiveSheet. Rozsah („$ H $ 5: $ H $ 17“). Pole automatického filtru: = 1, Criteria1: = "> 80", _
Provozovatel: = xlAnd
Rozsah („G22“). Vybrat
Obr. Data tabulky po automatickém výběru
Stanovení maximální a minimální hodnoty
Určete maximální a minimální hodnoty pro sloupce „Celkem“, „Absolutní výkon, procenta.“, „Kvalitativní výkon“ (tabulka 9, list 10)
Chcete-li určit maximální a minimální hodnoty, musíte provést následující:
Nastavte referenční proměnnou, kterou bude aktuální minimum (maximum)
Každý prvek populace se porovnává jeden po druhém s aktuálním minimem (maximem), a pokud tento prvek nesplňuje podmínky hledání (v případě minima je větší a v případě maxima menší) , poté je referenční hodnota nahrazena hodnotou porovnávaného prvku
Po úplném skenování všech prvků v proměnné aktuálního minima (maxima) je nalezeno skutečné minimum (maximum)
Hodnota minima (maxima) se zobrazí v příslušných buňkách
Subminmax ()
Dim A () jako varianta
n1 = Listy ("List4"). Buňky (5,12)
m = Listy ("List2"). Buňky (5,12)
ReDim 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
Pro j = 3 až m
maxA = 0,00001
minA = 1 000 000
Pro i = 1 až n1
Pokud A (i, j)> maxA Potom
maxA = A (i, j)
Pokud A (i, j)< minA Then
minA = A (i, j)
Listy („List10“). Buňky (i + 4 + 2, j) = max
Listy („List10“). Buňky (i + 4 + 3, j) = minA
Makro je sled akcí, které byly zaznamenány a uloženy pro budoucí použití. Uložené makro lze přehrát pomocí speciálního příkazu. Jinými slovy, můžete zaznamenat své akce do makra, uložit je a poté umožnit ostatním uživatelům přehrát akce uložené v makru jednoduchým stiskem klávesy. To je užitečné zejména při distribuci sestav kontingenční tabulky.
Řekněme, že chcete dát svým zákazníkům možnost seskupovat sestavy kontingenčních tabulek podle měsíce, čtvrtletí a roku. Technicky může proces seskupování provést každý uživatel, ale někteří vaši klienti nemusí považovat za nutné to pochopit. V takovém případě můžete zaznamenat jedno makro pro seskupení podle měsíce, další podle čtvrtletí a třetí podle roku. Poté vytvořte tři tlačítka - jedno pro každé makro. Potom vašim zákazníkům mimo kontingenční tabulku stačí kliknout na tlačítko, abyste správně seskupili kontingenční tabulku.
Hlavní výhodou použití maker v sestavách kontingenční tabulky je umožnit zákazníkům rychlé provádění v souhrnné tabulky operace, které běžně nemohou provádět. To významně zvyšuje efektivitu analýzy poskytovaných údajů.
Stáhněte si poznámku ve formátu nebo stáhněte s příklady (uvnitř Soubor Excel s makry; zásady poskytovatele neumožňují přímé nahrání souboru tohoto formátu na web).
Makro záznam
Podívejte se na kontingenční tabulku zobrazenou na obr. 1. Tuto kontingenční tabulku můžete obnovit kliknutím pravým tlačítkem myši do ní a výběrem Obnovit... Pokud jste při aktualizaci kontingenční tabulky zaznamenali akce jako makro, můžete vy nebo jakýkoli jiný uživatel tyto akce přehrát a kontingenční tabulku aktualizovat v důsledku spuštění makra.
Obr. 1. Záznam akcí během aktualizace této kontingenční tabulky vám umožní v budoucnu aktualizovat data v důsledku spuštění makra
Prvním krokem při záznamu makra je vyvolání dialogového okna Makro záznam... Přejděte na kartu Vývojář pásu karet a klikněte na tlačítko Makro záznam... (Pokud nemůžete najít kartu na pásu karet Vývojář, vyberte kartu Soubor a klikněte na tlačítko Parametry... V zobrazeném dialogovém okně Možnosti aplikace Excel Vyberte kategorii Přizpůsobení pásu karet a v seznamu vpravo zaškrtněte políčko Vývojář... Ve výsledku se na pásu karet objeví karta Vývojář.) Alternativní způsob spusťte záznam makra - klikněte na tlačítko (obr. 2).
V dialogovém okně Makro záznam zadejte následující informace o makru (obrázek 3):
názevmakro... Název by měl popisovat akce prováděné makrem. Jméno musí začínat písmenem nebo podtržítkem; nesmí obsahovat mezery a jiné neplatné znaky; nesmí být stejný jako předdefinovaný název aplikace Excel nebo název jiného objektu v sešitu.
Kombinaceklíče... Do tohoto pole můžete zadat jakékoli písmeno. Stane se součástí klávesové zkratky, která se použije k přehrávání makra. Kombinace kláves je volitelná. Ve výchozím nastavení je jako začátek kombinace nabízen pouze Ctrl. Pokud chcete, aby kombinace zahrnovala také Shift, zadejte písmeno v okně a podržte klávesu Shift
Uložitv... Zde je uloženo makro. Pokud se chystáte distribuovat sestavu kontingenční tabulky ostatním uživatelům, vyberte tuto možnost Tentorezervovat... Excel také umožňuje uložit makro do Nová kniha nebo v Osobní makro kniha.
Popis... Do tohoto pole se zadává popis vytvořeného makra.
Obr. 3. Nastavení okna Makro záznam
Protože makro aktualizuje kontingenční tabulku, vyberte název Aktualizace dat... Můžete také přiřadit makro ke kombinaci Ctrl klávesy+ Shift + Q. Nezapomeňte, že po vytvoření makra použijete tuto klávesovou zkratku ke spuštění. Vyberte možnost jako umístění pro uložení makra Tato kniha a klikněte OK.
Po kliknutí v dialogu Makro záznam na tlačítku OK začne nahrávání makra. V tomto okamžiku budou zaznamenány všechny akce provedené v aplikaci Excel.
Klepněte pravým tlačítkem do oblasti kontingenční tabulky a vyberte Obnovit(jako na obr. 1, ale v režimu záznamu makra). Po aktualizaci kontingenční tabulky můžete zastavit proces záznamu makra pomocí tlačítka Zastavit nahrávání záložky Vývojář... Nebo znovu klikněte na tlačítko zobrazené na obr. 2.
Právě jste nahráli své první makro. Nyní můžete makro spustit pomocí klávesové zkratky Ctrl + Shift + Q.
Makro bezpečnostní varování. Je třeba poznamenat, že pokud uživatel zaznamená makra, budou spuštěna bez jakýchkoli omezení ze strany bezpečnostního subsystému. Při distribuci pracovní sešit obsahující makra, musíte poskytnout ostatním uživatelům příležitost zajistit, aby při otevírání pracovních souborů nehrozilo žádné riziko, a spuštění maker nepovede k virové infekci systému. Zejména si okamžitě všimnete, že ukázkový soubor použitý v této kapitole nebude plně fungovat, pokud na něm výslovně nedovolíte Excelu spouštět makra.
Nejjednodušší způsob, jak udržet svá makra v bezpečí, je vytvořit důvěryhodné umístění - složku, do které budou umístěny pouze „důvěryhodné“ sešity bez virů. Důvěryhodné umístění umožňuje vám i vašim klientům spouštět makra v sešitech bez jakýchkoli bezpečnostních omezení (toto chování přetrvává, pokud jsou sešity v důvěryhodné složce).
Chcete-li nastavit důvěryhodné umístění, postupujte takto.
Vyberte kartu pásu karet Vývojář a klikněte na tlačítko Makro zabezpečení... Na obrazovce se zobrazí dialogové okno. Centrum důvěryhodnosti.
Klikněte na tlačítko Přidat nové umístění.
Klikněte na tlačítko Přehled k zadání složky pro pracovní soubory, kterým důvěřujete.
Jakmile zadáte důvěryhodné umístění, ve výchozím nastavení se pro všechny sešity v něm spustí libovolná makra.
Model zabezpečení byl v aplikaci Excel 2013 vylepšen. Soubory sešitu, které byly dříve „důvěryhodné“, jsou nyní zapamatovány; po otevření Sešity aplikace Excel a klikněte na tlačítko Zahrnout obsah Excel si tento stav pamatuje. Výsledkem je, že tato kniha spadá do kategorie důvěryhodných a při jejím následném otevření nejsou kladeny zbytečné otázky.
Vytváření uživatelského rozhraní pomocí ovládacích prvků formuláře
Spuštění makra pomocí kombinace kláves Ctrl + Shift + Q pomůže, když je v sestavě kontingenční tabulky pouze jedno makro. (Plus, users need to know this combination.) Ale předpokládejme, že chcete svým klientům poskytnout více maker, která provádějí různé akce. V tomto případě musíte zákazníkům poskytnout srozumitelné a jednoduchým způsobem spusťte každé makro, aniž byste si museli pamatovat kombinace kláves. Perfektní řešení je jednoduchý uživatelské rozhraní jako kolekce ovládacích prvků, jako jsou tlačítka, posuvníky a další nástroje, které umožňují spouštění maker pomocí kliknutí myší.
Excel vám nabízí sadu nástrojů určených k vytvoření uživatelského rozhraní přímo v tabulce. Tyto nástroje se nazývají ovládací prvky formulářů. Základní myšlenkou je, že je možné vložit ovládací prvek formuláře tabulkový kalkulátor a přiřadit mu makro, které bylo zaznamenáno dříve. Po přiřazení k ovládacímu prvku se makro spustí kliknutím na tento ovládací prvek.
Ovládací prvky formulářů najdete ve skupině Ovládací prvky formuláře záložky pásu karet Vývojář... Chcete-li otevřít paletu ovládacích prvků, klikněte v této skupině na tlačítko Vložit(obr. 4).
Obr. 4. Kontrola formy Knoflík
Poznámka: kromě ovládacích prvků formuláře obsahuje paleta také Ovládací prvky ActiveX... I když jsou si podobné, programově jsou to úplně jiné objekty. Ovládací prvky formuláře s jejich postižením a jednoduché nastavení speciálně navržené pro umístění na pracovní listy. Ve stejný čas Ovládací prvky ActiveX používá se především ve vlastních formulářích. Určete pravidlo, aby se na vaše listy umisťovaly pouze ovládací prvky formulářů.
Měli byste vybrat ovládací prvky, které nejlépe vyhovují danému úkolu. V tomto příkladu musí být klienti schopni aktualizovat kontingenční tabulku kliknutím na tlačítko. Klikněte na ovládací prvek Knoflík, přesuňte ukazatel myši na místo v listu, kde chcete mít tlačítko, a klikněte.
Po umístění tlačítka do tabulky se otevře dialogové okno Přiřaďte makro objekt(obr. 5). Vyberte požadované makro (v našem případě - Aktualizace dat zaznamenané dříve) a klikněte na OK.
Obr. 5. Vyberte makro, které má být tlačítku přiřazeno, a klepněte na tlačítko OK... V tento případ makro by mělo být použito Aktualizace dat
Po umístění všech nezbytných ovládacích prvků do sestavy kontingenční tabulky můžete tabulku formátovat a vytvořit základní rozhraní. Na obr. 6 ukazuje sestavu kontingenční tabulky po formátování.
Úprava zaznamenaného makra
V důsledku záznamu makra Program Excel vytvoří modul, který uloží provedené akce. Všechny zaznamenané akce jsou reprezentovány řádky kódu VBA, které tvoří makro. Můžete přidat různé typy sestav kontingenční tabulky funkčnost přizpůsobením kódu VBA k dosažení požadovaných výsledků. Abychom lépe pochopili, jak to všechno funguje, vytvořme nové makro, které zobrazí prvních pět záznamů zákazníků. Přejděte na kartu Vývojář a klikněte na tlačítko Makro záznam... Dialogové okno zobrazené na obr. 7. Pojmenujte vytvořené makro PrvníZákazníci a zadejte umístění pro uložení Tato kniha... Klepněte na OK pro spuštění záznamu makra.
Po zahájení nahrávání klikněte na šipku vedle pole Jméno zákazníka, vyberte Filtrovat podle hodnoty a možnost Prvních 10(Obr. 8a). V zobrazeném dialogovém okně nakonfigurujte nastavení podle obr. 8b. Tato nastavení vám řeknou, abyste zobrazili data pěti nejlepších zákazníků z hlediska prodeje. Klepněte na OK.
Obr. 8. Vyberte filtr (a) a upravte parametry (b) tak, aby se zobrazilo pět nejlepších zákazníků podle prodeje
Poté, co úspěšně zaznamenáte všechny kroky potřebné k načtení nejlepších 5 prodejních zákazníků, přejděte na kartu Vývojář a klikněte na tlačítko Zastavit nahrávání.
Nyní máte makro, které filtruje kontingenční tabulku a načte prvních 5 prodejních zákazníků. Je nutné nechat makro reagovat na stav posuvníku, tj. pomocí posuvníku musíte být schopni označit makru počet zákazníků, jejichž data se zobrazí v sestavě kontingenční tabulky. Uživatel tedy bude moci pomocí posuvníku načíst prvních pět, osm nejlepších nebo třicet dva nejlepších klientů, jak uznají za vhodné.
Chcete-li do tabulky přidat posuvník, přejděte na kartu Vývojář, klikněte na tlačítko Vložit, vyberte ovládací prvek na paletě Posuvník a umístěte jej na list. Klikněte pravým tlačítkem na ovládací prvek Posuvník Formát objektu... Otevře se dialogové okno Formát ovládání(obr.9). V něm proveďte následující změny nastavení: parametr Minimální hodnota přiřadit hodnotu 1, parametr Maximální hodnota - hodnota 200 a v poli Mobilní komunikace zadejte hodnotu $ M $ 2 pro zobrazení hodnoty posuvníku v buňce M2. Klikněte na tlačítko OK použít dříve zadané nastavení.
Nyní musíte porovnat nedávno zaznamenané makro PrvníZákazníci s kontrolou Posuvník na listu. Klikněte pravým tlačítkem na ovládací prvek Posuvník a v kontextová nabídka vybrat tým Přiřaďte makro otevřete dialogové okno Přiřazení makra. Přiřaďte zaznamenané makro k posuvníku Zákazníci FirstN... Makro se spustí pokaždé, když kliknete na posuvník. Vyzkoušejte vytvořený posuvník. Po kliknutí na proužek se makro spustí Zákazníci FirstN a číslo v buňce M2 se změní, aby indikovalo stav posuvného pruhu. Číslo v buňce M2 je důležité, protože se používá k vytvoření vazby makra na posuvník.
Jediné, co zbývá udělat, je, aby makro zpracovalo číslo v buňce M2 přidružením k posuvníku. Chcete-li to provést, musíte přejít na kód VBA makra. Chcete-li to provést, přejděte na kartu Vývojář a klikněte na tlačítko Makra... Otevře se dialogové okno Makro(obr. 10). V něm můžete spustit, odstranit a upravit vybrané makro. Chcete-li zobrazit kód VBA makra na obrazovce, vyberte makro a klikněte na tlačítko Upravit.
Obr. 10. Přístup k kódu VBA makra PrvníZákazníci, vyberte makro a klikněte na tlačítko Upravit
Na obrazovce se objeví okno editoru Visual Basic s kódem VBA makra (obr. 11). Vaším cílem je nahradit napevno číslo 5, které je nastaveno během záznamu makra, hodnotou v buňce M2, která je vázána na posuvník. Zpočátku bylo zaznamenáno makro, které zobrazovalo pět nejlepších zákazníků s nejvyššími příjmy.
Odeberte z kódu číslo 5 a místo toho zadejte následující výraz:
ActiveSheet.Range ("M2"). Hodnota
Přidejte dva řádky na začátek makra a vyčistěte filtry:
Rozsah („A4“). Vyberte
ActiveSheet.PivotTables ("PivotTable1") .PivotFields ("jméno zákazníka") .ClearAllFilters
Makro kód by nyní měl vypadat jako na Obr. 12.
Zavřete editor jazyka a vraťte se do sestavy kontingenční tabulky. Otestujte posuvník přetažením jezdce na 11. Makro by se mělo spustit a odfiltrovat 11 záznamů o nejlepší klienti podle prodeje.
Synchronizujte dvě kontingenční tabulky s jednou rozbalovací nabídkou
Zpráva zobrazená na obr. 13 obsahuje dvě kontingenční tabulky. Každý z nich má pole stránky, které vám umožňuje vybrat prodejní oblast. Problém je v tom, že pokaždé, když vyberete trh v poli stránky jedné kontingenční tabulky, musíte vybrat stejný trh v poli stránky jiné kontingenční tabulky. Synchronizace filtrů mezi dvěma tabulkami během fáze analýzy dat není velkým problémem, ale existuje šance, že vy nebo vaši zákazníci na to stále zapomenete.
Obr. 13. Dvě kontingenční tabulky obsahují pole stránky, která filtrují data podle trhu. Chcete-li analyzovat data jednotného trhu, musíte synchronizovat obě kontingenční tabulky
Jedním ze způsobů, jak tyto kontingenční tabulky synchronizovat, je použít rozevírací seznam. Cílem je zaznamenat makro, které z pole vybere požadovaný trh Prodejní trh v obou tabulkách. Pak musíte vytvořit rozevírací seznam a naplnit jej jmény prodejních trhů ze dvou kontingenčních tabulek. Nakonec je třeba zaznamenané makro upravit tak, aby filtrovalo obě kontingenční tabulky pomocí hodnot z rozevíracího seznamu. Chcete-li tento problém vyřešit, musíte provést následující kroky.
1. Vytvořte nové makro a pojmenujte jej SynchMarkets... Po zahájení nahrávání vyberte do pole Prodejní trh pro oba kontingenční tabulky prodeje Kalifornie a zastavit záznam makra.
2. Zobrazte paletu ovládacích prvků formuláře a přidejte do listu rozevírací seznam.
3. Vytvořte pevně zakódovaný seznam všech trhů kontingenčních tabulek. První položka v seznamu je (Vše). Tento prvek byste měli povolit, pokud chcete z rozevíracího seznamu vybrat všechny trhy.
4. V tomto okamžiku by sestava kontingenční tabulky měla vypadat jako ta, která je uvedena na obr. čtrnáct.
Obr. 14. Máte k dispozici všechny nástroje, které potřebujete: makro, které mění pole Prodejní trh obě kontingenční tabulky, rozevírací seznam a seznam všech prodejních trhů obsažených v kontingenční tabulce
5. Klepněte pravým tlačítkem na rozevírací seznam a vyberte příkaz Formát objektu přizpůsobit ovládací prvek.
6. Nejprve nastavte počáteční rozsah hodnot použitých k naplnění rozevíracího seznamu, jak je znázorněno na obrázku 6-7. 15. V tomto případě mluvíme o seznamu prodejních trhů, který jste vytvořili v kroku 3. Poté zadejte buňku, která zobrazuje sériové číslo vybraného prvku (v tomto příkladu je to buňka H1). Parametr Počet řádků seznamu určuje, kolik řádků bude zobrazeno současně v rozevíracím seznamu. Klikněte na tlačítko OK.
Obr. 15. Nastavení rozevíracího seznamu musí ukazovat na seznam prodejních trhů jako počáteční rozsah hodnot a definovat buňku H1 jako kotevní bod
7. Nyní máte příležitost vybrat z rozevíracího seznamu prodejní trh a také definovat přidružené sériové číslo v buňce H1 (obr. 16). Vyvstává otázka: proč se místo skutečného názvu trhu používá hodnota indexu? Protože rozevírací seznam nevrací jméno, ale číslo. Pokud například vyberete Kalifornii z rozevíracího seznamu, v buňce H1 se zobrazí hodnota 5, což znamená, že Kalifornie je pátou položkou v seznamu.
Obr. 16. Rozevírací seznam je nyní vyplněn názvy trhů a sériové číslo vybraného trhu se zobrazí v buňce H1
8. Chcete-li místo názvu trhu použít sériové číslo, musíte jej předat pomocí funkce INDEX.
9. Zadejte funkci INDEX, která převádí sériové číslo z buňky H1 na smysluplnou hodnotu.
10. Funkce INDEX přijímá dva argumenty. První argument představuje rozsah hodnot v seznamu. Ve většině případů použijete stejný rozsah, který vyplňuje rozevírací nabídku. Druhým argumentem je pořadové číslo. Pokud je do buňky zadáno sériové číslo (například do buňky H1, jako na obr. 17), můžete na tuto buňku jednoduše odkazovat.
Obr. 17. Funkce INDEX v buňce 11 převádí pořadové číslo uložené v buňce H1 na hodnotu. Hodnotu v buňce I1 použijete ke změně makra
11. Upravte makro SynchMarkets pomocí hodnoty v buňce I1 namísto pevně zakódované hodnoty. Přejděte na kartu Vývojář a klikněte na tlačítko Makra... Na obrazovce se zobrazí dialogové okno, jak je znázorněno na obr. 18. Vyberte v něm makro SynchMarkets a klikněte na tlačítko Změnit.
Obr. 18. Chcete-li získat přístup k kódu VBA makra, vyberte makro SynchMarkets a klikněte Upravit
12. Při záznamu makra jste z obou kontingenčních tabulek vybrali kalifornskou prodejní oblast. Prodejní trh... Jak je patrné z obr. 19, kalifornský trh je nyní napevno zakódován v kódu makra VBA.
13. Vyměňte Kalifornii za Activesheet.Range („I1“) .Hodnota, která odkazuje na hodnotu v buňce I1. V tomto okamžiku by měl kód makra vypadat jako kód zobrazený na obr. 20. Po úpravě makra zavřete editor jazyka a vraťte se do tabulky.
Obr. 20. Nahraďte "Kalifornie" za ActiveSheet.Range ("I1") .Hodnota a zavřete editor jazyka
14. Zbývá pouze zajistit provedení makra při výběru prodejní oblasti v rozevíracím seznamu. Klikněte pravým tlačítkem na rozbalovací nabídku a vyberte požadovanou možnost Přiřaďte makro... Vyberte makro SynchMarket a klikněte na tlačítko OK.
15. Skryjte řádky a sloupce s poli stránky v kontingenčních tabulkách a seznamu vytvořených trhů a indexových vzorců.
Na obr. 21 ukazuje konečný výsledek. Nyní máte uživatelské rozhraní, které zákazníkům umožňuje vybrat svou prodejní oblast v obou kontingenčních tabulkách pomocí jediného rozevíracího seznamu.
Když vyberete novou položku z rozevíracího seznamu, změní se automaticky velikost sloupců tak, aby se do nich vešly všechny údaje zobrazené v nich. Tento druh chování programu je při formátování šablony listu docela nudný. Můžete tomu zabránit kliknutím pravým tlačítkem na kontingenční tabulku a výběrem Možnosti kontingenční tabulky... Na obrazovce se objeví dialogové okno se stejným názvem, ve kterém musíte zrušit zaškrtnutí políčka Automaticky změnit velikost sloupců při aktualizaci.
Poznámka založená na Jelenově knize Alexander. ... Kapitola 12.
Pomocí nástrojů aplikace Excel můžete vybrat konkrétní data z rozsahu v náhodném pořadí, jedné podmínce nebo několika. K řešení těchto problémů se zpravidla používají maticové vzorce nebo makra. Podívejme se na několik příkladů.
Jak provést výběr v aplikaci Excel podle podmínek
Při použití maticových vzorců se vybraná data zobrazí v samostatné tabulce. Jaká je výhoda tato metoda ve srovnání s konvenčním filtrem.
Zdrojová tabulka:
Nejprve se naučíme, jak provést výběr na základě jediného číselného kritéria. Úkolem je vybrat z tabulky produkty s cenou nad 200 rublů. Jedním z řešení je použít filtrování. Výsledkem je, že v původní tabulce zůstanou pouze ty produkty, které splňují požadavek.
Dalším řešením je použít maticový vzorec. Řádky odpovídající požadavku se vejdou do samostatné tabulky.
Nejprve vytvoříme prázdnou tabulku vedle původní: duplicitní záhlaví, počet řádků a sloupců. Nová tabulka zabírá rozsah E1: G10. Nyní vyberte E2: E10 (sloupec „Datum“) a zadejte následující vzorec: ( }.
Chcete-li získat maticový vzorec, stiskněte kombinaci kláves Ctrl + Shift + Enter. V dalším sloupci - „Produkt“ - zadáme podobný vzorec pole: ( ). Změnil se pouze první argument funkce INDEX.
Ve sloupci „Cena“ zadejte stejný vzorec pole a změňte první argument funkce INDEX.
Ve výsledku dostaneme zprávu o zboží s cenou více než 200 rublů.
Takový výběr je dynamický: když se dotaz změní nebo se ve zdrojové tabulce objeví nové produkty, zpráva se automaticky změní.
Úkol číslo 2 - vyberte z původní tabulky zboží, které bylo uvedeno do prodeje 20. 9. 2015. To znamená, že kritériem výběru je datum. Pro větší pohodlí zadáme požadované datum do samostatné buňky I2.
K vyřešení problému se používá podobný vzorec pole. Pouze místo kritéria).
Podobné vzorce se zadávají do dalších sloupců (viz princip výše).
Nyní použijeme textové kritérium. Místo data v buňce I2 zadejte text „Produkt 1“. Pojďme trochu změnit vzorec pole: ( }.
Skvělá funkce výběru v aplikaci Excel.
Výběr podle více podmínek v aplikaci Excel
Nejprve si vezmeme dvě numerická kritéria:
Úkolem je vybrat zboží, které stojí méně než 400 a více než 200 rublů. Spojme podmínky se znaménkem „*“. Vzorec pole vypadá takto: ( }.!}
Toto je pro první sloupec tabulky sestavy. Pro druhý a třetí - změníme první argument funkce INDEX. Výsledek:
Chcete-li provést výběr podle několika dat nebo číselných kritérií, používáme podobné maticové vzorce.
Náhodné vzorkování v aplikaci Excel
Když uživatel pracuje s velké množstvíúdaje mohou vyžadovat náhodný výběr vzorků pro následnou analýzu. Každému řádku můžete přiřadit náhodné číslo a poté použít výběr.
Původní datová sada:
Nejprve vložte dva prázdné sloupce vlevo. Do buňky A2 napište vzorec RAND (). Vynásobme to celým sloupcem:
Nyní zkopírujeme sloupec s náhodnými čísly a vložíme ho do sloupce B. To je nutné, aby se tato čísla nezměnila při zadávání nových údajů do dokumentu.
Chcete-li vložit hodnoty, nikoli vzorec, klikněte pravým tlačítkem na sloupec B a vyberte nástroj Vložit jinak. V okně, které se otevře, zaškrtněte před položkou „Hodnoty“:
Nyní můžete data ve sloupci B seřadit vzestupně nebo sestupně. Pořadí, ve kterém jsou uvedeny původní hodnoty, se také změní. Vybereme libovolný počet řádků shora nebo zdola - získáme náhodný vzorek.
Různé (39)
Chyby a závady aplikace Excel (3)
Jak získám seznam jedinečných (neduplicitních) hodnot?
Představte si velký seznam různých jmen, celých jmen, osobních čísel atd. A je nutné opustit seznam všech stejných jmen z tohoto seznamu, ale aby se neopakovali - tj. odstranit všechny duplicitní položky z tohoto seznamu. Jak se to jinak nazývá: vytvořte seznam jedinečných prvků, seznam neopakujících se, žádné duplikáty. Existuje několik způsobů, jak to provést: vestavěné nástroje Excel, předdefinované vzorce a nakonec použití kódu Visual Basic for Application (VBA) a kontingenční tabulky. V tomto článku se podíváme na jednotlivé možnosti.
Používání integrovaných funkcí v aplikaci Excel 2007 a novějších verzích
V aplikaci Excel 2007 a 2010 je to stejně snadné jako ostřelování hrušek - existuje speciální tým, který se nazývá -. Nachází se na kartě Data podsekce Datové nástroje
Jak používat tento příkaz. Zvýrazněte sloupec (nebo několik) s údaji, ve kterých je třeba odstranit duplicitní záznamy. Přejděte na kartu Data -Odebrat duplikáty.
Pokud vyberete jeden sloupec, ale vedle něj bude více sloupců s daty (nebo alespoň jeden sloupec), Excel nabídne výběr: rozšířit rozsah výběru o tento sloupec nebo ponechat výběr tak, jak je, a odstranit pouze data ve zvoleném rozsahu. Je důležité si uvědomit, že pokud rozsah nerozbalíte, data se změní pouze v jednom sloupci a data v sousedním sloupci zůstanou nezměněna.
Zobrazí se okno s možnostmi odstranění duplikátů 
Zaškrtněte políčka před těmito sloupci, duplikáty, které chcete odstranit, a klikněte na OK. Pokud jsou ve zvoleném rozsahu umístěny také datové hlavičky, je lepší nastavit příznak Moje data obsahují záhlaví aby nedošlo k náhodnému smazání dat v tabulce (pokud se náhle úplně shodují s hodnotou v názvu).
Metoda 1: Pokročilý filtr
V případě aplikace Excel 2003 jsou věci komplikovanější. Neexistuje žádný takový nástroj jako Odeberte duplikáty... Ale na druhou stranu existuje tak úžasný nástroj jako Pokročilý filtr... V roce 2003 lze tento nástroj nalézt v Data -Filtr -Pokročilý filtr... Krása této metody spočívá v tom, že ji můžete použít k vytvoření seznamu v jiném rozsahu, než aby zkazila původní data. V letech 2007–2010 je zde také Excel, ale trochu skrytý. Nachází se na kartě Data, Skupina Třídit a filtrovat - Pokročilý
Jak to používat: spusťte zadaný nástroj - zobrazí se dialogové okno: 
- Léčba: Vybíráme si Zkopírujte výsledek do jiného umístění.
- Rozsah seznamu: Výběr rozsahu s daty (v našem případě je to A1: A51).
- Rozsah kritérií: v tomto případě necháme prázdné.
- Zkopírovat do rozsahu: označíme první buňku pro zobrazení dat - libovolnou prázdnou (na obrázku - E2).
- Zaškrtněte políčko Pouze jedinečné záznamy.
- Lisujeme OK.
Poznámka: chcete-li výsledek umístit na jiný list, pak prostě nemůžete určit další list. Buňku budete moci určit na jiném listu, ale ... Běda a ah ... Excel dá zprávu, že nemůže kopírovat data do jiných listů. Dá se to ale také obejít, a to celkem jednoduše. Musíte jen utéct Pokročilý filtr z listu, na který chceme umístit výsledek. A jako počáteční data vybereme data z libovolného listu - to je přípustné.
Výsledek také nemůžete přenést do jiných buněk, ale filtrovat data na místě. Data nebudou nijak ovlivněna - bude to obvyklé filtrování dat.
Chcete-li to provést, stačí vybrat v části Zpracování Filtrujte seznam na místě.
Metoda 2: Vzorce
Tato metoda je pro nezkušené uživatele obtížnější pochopit, ale vytváří seznam jedinečných hodnot beze změny původních dat. Je to také dynamičtější: pokud změníte data ve zdrojové tabulce, změní se také výsledek. To je někdy užitečné. Pokusím se na prstech vysvětlit, co a co: například ve sloupci A máte seznam s daty (A1: A51, kde A1 je nadpis). Seznam zobrazíme ve sloupci C, počínaje buňkou C2. Vzorec v C2 bude následující:
(= INDEX ($ A $ 2: $ A $ 51; SMALL (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; ŘÁDEK ($ A $ 1: $ A $ 50)); 1)))
(= INDEX ($ A $ 2: $ A $ 51; SMALL (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; ŘÁDEK ($ A $ 1: $ A $ 50)); 1)))
Podrobná analýza fungování tohoto vzorce je uvedena v článku:
Je třeba poznamenat, že tento vzorec je maticový vzorec. To se dá říct rovnátka, ve kterém je tento vzorec obsažen. A takový vzorec se zadává do buňky pomocí klávesové zkratky - Ctrl+Posun+Enter... Poté, co jsme tento vzorec zadali do C2, musíme jej zkopírovat a vložit do několika řádků, abychom mohli přesně zobrazit všechny jedinečné prvky. Jakmile se vzorec ve spodních buňkách vrátí #ČÍSLO!- to znamená, že jsou zobrazeny všechny prvky a nemá smysl roztahovat vzorec níže. Chcete-li se vyhnout chybě a zajistit, aby byl vzorec univerzálnější (aniž byste se vždy natahovali, dokud se chyba neobjeví), můžete použít jednoduchou kontrolu:
pro Excel 2007 a vyšší:
(= IFERROR (INDEX ($ A $ 2: $ A $ 51; MALÉ (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; ŘÁDEK ($ A $ 1: $ A $ 50)); 1)); ""))
(= IFERROR (INDEX ($ A $ 2: $ A $ 51; MALÉ (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; ŘÁDEK ($ A $ 1: $ A $ 50)); 1)); ""))
pro Excel 2003:
(= IF (ISH (SMALL (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; ROW ($ A $ 1: $ A $ 50)); 1)); ""; INDEX ($ A $ 2: $ A $ 51; MALÉ (POKUD (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; ŘÁDEK ($ A $ 1: $ A 50 $)); 1))))
(= IF (ISERR (SMALL (IF (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; ROW ($ A $ 1: $ A $ 50)); 1)); ""; INDEX ($ A $ 2: $ A $ 51; MALÉ (POKUD (COUNTIF ($ C $ 1: C1; $ A $ 2: $ A $ 51)) = 0; ŘÁDEK ($ A $ 1: $ A 50 $)); 1))))
Pak místo chyby #ČÍSLO! (# ČÍSLO!) budete mít prázdné buňky(samozřejmě není úplně prázdný - se vzorci :-)).
Trochu podrobněji o rozdílech a nuancích vzorců ESLIOSHIBKA a IF (EOSH si můžete přečíst v tomto článku: Jak zobrazit 0 v buňce se vzorcem místo chyby
Metoda 3: Kód VBA
Tento přístup bude vyžadovat rozlišení maker a základní znalosti práce s nimi. Pokud si nejste jisti svými znalostmi, doporučuji si nejprve přečíst tyto články:
- Co je to makro a kde ho najdu? k článku je přiložen videonávod
- Co je to modul? Jaké moduly tam jsou? budou muset zjistit, kam vložit níže uvedené kódy
Oba níže uvedené kódy by měly být umístěny v standardní modul... Makra musí být povolena.
Ponechme počáteční data ve stejném pořadí - seznam s daty se nachází ve sloupci „A“ (A1: A51, kde A1 je nadpis)... Pouze zobrazíme seznam ne ve sloupci C, ale ve sloupci E, počínaje buňkou E2:
Sub Extract_Unique () Dim vItem, avArr, li As Long ReDim avArr (1 To Rows.Count, 1 To 1) With New Collection On Error Resume Next For each vItem in Range ("A2", Cells (Rows.Count, 1) .End (xlUp)). Hodnota "(! LANG: Buňky (Rows.Count, 1) .End (xlUp) - definuje poslední vyplněnou buňku ve sloupci A. Přidat vItem, CStr (vItem) Pokud Err = 0 Pak li = li + 1: avArr (li, 1) = vItem Else: Err. Clear End If Next End With If li Then .Resize (li) .Value = avArr End Sub
Pomocí tohoto kódu můžete extrahovat jedinečné hodnoty nejen z jednoho sloupce, ale z libovolného rozsahu sloupců a řádků. Pokud místo řádku
Rozsah ("A2", Cells(Rows.Count, 1).End(xlUp)).Value !}
zadejte Selection.Value, výsledkem kódu bude seznam jedinečných prvků z rozsahu vybraného na aktivním listu. Teprve potom by bylo hezké změnit výstupní buňku hodnot - místo
vložte ten, ve kterém nejsou žádná data.
Můžete také určit konkrétní rozsah:
Rozsah („C2“, buňky (Rows.Count, 3). End (xlUp)). Hodnota
Univerzální kód pro výběr jedinečných hodnot
Níže uvedený kód lze použít na jakékoli rozsahy. Stačí jej spustit, zadat rozsah s hodnotami pro výběr pouze neopakujících se (je povolen více než jeden sloupec) a buňka pro zobrazení výsledku. Budou skenovány zadané buňky, z toho pouze jedinečné hodnoty(prázdné buňky jsou přeskočeny) a výsledný seznam bude zapsán počínaje od určené buňky.
| Sub Extract_Unique () Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next "požadovat adresu buněk pro výběr jedinečných hodnot Nastavit rVals = Application.InputBox ( "Určete rozsah buněk pro vzorkování jedinečných hodnot", "Vyžádat data", "A2: A51", Typ: = 8) Pokud rVals potom nic není "pokud kliknete na tlačítko Storno Exit Sub End If "pokud je zadána pouze jedna buňka, nemá smysl vybírat If rVals.Count = 1 Then MsgBox "Chcete-li filtrovat jedinečné hodnoty, musíte zadat více než jednu buňku.", vbInformation, „www.site“ Ukončit Sub End Pokud "odříznout prázdné řádky a sloupce mimo pracovní rozsah Nastavit rVals = protínat (rVals, rVals.Parent.UsedRange) "pokud jsou zadány pouze prázdné buňky mimo pracovní rozsah Pokud rVals nic není, pak MsgBox „Nedostatek údajů k výběru hodnot“, vbInformation, "www.site" Ukončit Sub End Pokud avVals = rVals.Value "požádat buňku o zobrazení výsledku Nastavit rResultCell = Application.InputBox ( "Zadejte buňku pro vložení vybraných jedinečných hodnot", "Vyžádat data", "E2", Typ: = 8) Pokud rResultCell potom nic není "pokud kliknete na tlačítko Storno Exit Sub End If "definuje maximální možnou dimenzi pole pro výsledek ReDim avArr (1 až řádky, počet, 1 až 1) "pomocí objektu Collection "vyberte pouze jedinečné záznamy, "protože kolekce nemohou obsahovat duplicitní hodnoty S novou kolekcí při chybě Pokračujte dále pro každý x v avVals Pokud Len (CStr (x)) Pak "přeskočit prázdné buňky. Přidejte x, CStr (x) "pokud přidaný prvek již v kolekci existuje, dojde k chybě "pokud nedojde k chybě, tato hodnota ještě nebyla zadána, "přidat do výsledného pole If Err = 0 Then li = li + 1 avArr (li, 1) = x Else "nezapomeňte vymazat objekt Chyba Err.Clear End If End If Next End With "zapíše výsledek do listu, počínaje zadanou buňkou If li Then rResultCell.Cells (1, 1) .Resize (li) .Value = avArr End Sub |
Sub Extract_Unique () Dim x, avArr, li As Long Dim avVals Dim rVals As Range, rResultCell As Range On Error Resume Next "požádat o adresu buněk pro výběr jedinečných hodnot Set rVals = Application.InputBox (" Specify the range buněk k výběru jedinečných hodnot "," Žádost o data "," A2: A51 ", Typ: = 8) Pokud rVals nic není," pokud kliknete na tlačítko Storno Konec Sub Konec "Pokud" je zadána pouze jedna buňka , nemá smysl vybírat If rVals.Count = 1 Then MsgBox "Chcete-li vybrat jedinečné hodnoty, musíte zadat více než jednu buňku", vbInformation, "www.site" Exit Sub End If "odříznout prázdné řádky a sloupce mimo pracovní rozsah Nastavit rVals = Průnik (rVals, rVals.Parent.UsedRange) "pokud jsou mimo pracovní rozsah zadány pouze prázdné buňky Pokud rVals není nic Pak MsgBox" Nedostatek dat k výběru hodnot ", vbInformation," www..Value " (! LANG: vyžádat buňku k zobrazení výsledku Set rResultCell = 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!}
Metoda 4: kontingenční tabulky
Nějaký nestandardním způsobem načítání jedinečných hodnot.
- Vyberte jeden nebo více sloupců v tabulce, přejděte na kartu Vložit-Skupina Stůl -Kontingenční tabulka
- V dialogovém okně Vytvořte kontingenční tabulku zkontrolujeme správnost výběru rozsahu dat (nebo sady nový zdroj data)
- označte umístění kontingenční tabulky:
- Nový list
- Stávající list
- vytvoření potvrďte stisknutím tlačítka OK
Protože kontingenční tabulky, při zpracování dat, která jsou umístěna v oblasti řádků nebo sloupců, vyberte z nich pouze jedinečné hodnoty pro následnou analýzu, pak od nás není vyžadováno absolutně nic kromě vytvoření kontingenční tabulky a umístění dat požadovaný sloupec v oblasti řádků nebo sloupců.
Jako příklad používám soubor připojený k článku:
Jaká je v tomto případě nepříjemnost práce s kontingenčními tabulkami: pokud se změní zdrojová data, kontingenční tabulka bude muset být aktualizována ručně: Vyberte libovolnou buňku kontingenční tabulky - pravé tlačítko myši - Obnovit nebo tab Data -Obnovit vše -Obnovit... A pokud jsou zdrojová data doplňována dynamicky a ještě horší, bude nutné znovu určit rozsah zdrojových dat. A ještě jedna nevýhoda - data uvnitř kontingenční tabulky nelze změnit. Pokud tedy bude v budoucnu nutné s výsledným seznamem pracovat, tak po vytvoření požadovaný seznam pomocí souhrnu je nutné jej zkopírovat a vložit na požadovaný list.
Abyste lépe porozuměli všem krokům a naučili se používat kontingenční tabulky, důrazně vám doporučuji přečíst si článek Obecné informace o kontingenčních tabulkách - je k němu připojen videonávod, ve kterém jasně demonstruji jednoduchost a pohodlí práce s základní vlastnosti kontingenčních tabulek.
V připojeném příkladu je kromě popsaných technik napsána o něco složitější variace extrakce jedinečných prvků podle vzorce a kódu, a to: extrakce jedinečných prvků podle kritéria... O čem mluvíme: pokud v jednom sloupci příjmení a ve druhém (B) jsou nějaká data (v souboru jsou to měsíce) a chcete extrahovat jedinečné hodnoty sloupce B pouze pro vybrané příjmení. Příklady takových jedinečných extrakcí jsou uvedeny na listu Extrakce podle kritéria.
Příklad stažení:
(108,0 KiB, 14 152 stažení)
Pomohl článek? Sdílejte odkaz se svými přáteli! Video lekce
