Máme tabulku, ve kterém jsou objemy prodeje určitého zboží zaznamenány v různých měsících. V tabulce je nutné najít data a kritérium hledání bude titulky řádků a sloupců. Vyhledávání by mělo být provedeno samostatně rozsahem linky nebo sloupce. To znamená, že bude použita pouze jedna z kritérií. Proto nelze použít funkci indexu a je zapotřebí speciální vzorec.

Vyhledávání hodnot v tabulce Excel

Pro vyřešení tohoto problému ilustrujeme příklad na schematické tabulce, která odpovídá výše popsaným podmínkám.

List s tabulkou pro vyhledávání hodnot vertikálně a horizontální:

Nad samotným stolem je řetězec s výsledky. V buňce B1 řídit kritérium pro vyhledávací dotaz, tj. Záhlaví sloupce nebo název řetězce. A v buňce D1 musí vyhledávací vzorec vrátit výsledek výpočtu odpovídající hodnoty. Poté bude druhý vzorec fungovat ve F1 buňce, která již bude používat hodnoty buněk B1 a D1 jako kritéria pro vyhledávání příslušného měsíce.

Hodnoty vyhledávání v řetězci Excel

Nyní se učíme, jaký je maximální objem a v kterém měsíci byl maximální prodej zboží 4.

Následuje vyhledávání sloupců:

V tomto měsíci a jaký byl největší prodej zboží 4 pro dva čtvrtiny.

Princip akce Vyhledávání vzorců v řádku Excel:

V prvním argumentu, funkce PRD (vertikální zobrazení) označuje odkaz na buňku, kde je vyhledávací kritéria. Druhý argument označuje rozsah buněk pro prohlížení během procesu vyhledávání. Ve třetím argumentu by měla být funkce PRD indikována číslem sloupce, ze kterého by měla být hodnota přijata proti řádku s názvem zboží 4. Ale protože toto číslo nepoznáme předem. Používáme funkce sloupce. Vytvořit pole čísel sloupců pro rozsah B4: G15.

To umožňuje funkce VDC shromažďovat celou řadu hodnot. V důsledku toho jsou všechny odpovídající hodnoty každého sloupce na řádkové položce 4 uloženy v paměti (konkrétně: 360; 958; 201; 605; 462; 832). Po tom, že Max funkce zůstávají pouze pro maximální počet z tohoto pole a vrátí se jako hodnota buňky D1, jako výsledek výpočtu vzorce.

Jak vidíte design vzorce jednoduché a lakonické. Na jeho základě je možné podobným způsobem najít pro konkrétní produkt a další ukazatele. Například minimální nebo průměrná hodnota prodeje použití pro tyto funkce nebo CPNAV. Nemusíte vám bránit, že tato kostra vzorce se aplikuje pomocí složitějších funkcí pro implementaci nejpohodlnější analýzy zprávy o prodeji.

Jak získat sloupce titulky na inspiritu jedné buňky?

Například jako velkolepě jsme zobrazili měsíc, ve kterém došlo k maximálnímu prodeji pomocí druhého vzorce. Není těžké si všimnout, že ve druhém vzorci jsme použili kostru prvního vzorce bez funkce MAX. Hlavní struktura vzorce: PRD (B1; A5: G14; kolona (B5: G14); 0). Nahradili jsme funkci MAX na vyhledávání, která v prvním argumentu používá hodnotu získanou předchozí vzorec. Nyní působí jako kritérium pro vyhledávání měsíce. A v důsledku toho je funkce vyhledávání se vrátí podle čísla sloupce 2, kde je umístěna maximální hodnota prodeje zboží 4. Poté funkce obsahuje funkci indexu, která vrátí hodnotu číslem a sloupcem z rozsah definovaný ve svých argumentech. Vzhledem k tomu, máme číslo sloupce 2, a číslo řádku v rozsahu, kde jsou názvy měsíců uloženy v jakýchkoli případech, bude 1. Potom zůstane funkcí indexu pro získání odpovídající hodnoty z rozsahu B4: G4 - Únor (druhý měsíc).



Hodnoty vyhledávání v sloupci Excel

Druhým úkolem je vyhledat tabulku pomocí názvu měsíce jako kritéria. V takových případech musíme změnit kostru naší vzorce: Funkce UPR je nahrazena GRP a funkce sloupce je nahrazena řetězcem.

To nám umožní zjistit, jaký objem a který produkt byl maximální prodej v určitém měsíci.

Chcete-li zjistit, jaký výrobek měl maximální prodej v určitém měsíci, následuje:

Princip akce Vyhledávání vzorců v sloupci Excel:

V prvním argumentu, funkce GPR (horizontální pohled) indikuje odkaz na buňku s kritériem vyhledávání. Ve druhém argumentu je indikován odkaz na zobrazený rozsah tabulky. Třetí argument generuje řetězec funkci, která vytvoří pole řádků od 10 prvků v paměti. Vzhledem k tomu, že v tabulkové části máme 10 řádků.

Dále funkce GRP střídavě pomocí každého čísla řádku vytvoří pole vhodných prodejních hodnot z tabulky v určitém měsíci (červen). Dále, funkce MAX zůstávají pouze pro výběr maximální hodnoty z tohoto pole.

POZORNOST! Při použití kostry vzorců pro jiné úkoly vždy věnujte pozornost druhému a třetímu argumentu vyhledávací funkce GRP. Počet řádků uvedených v rozsahu argumentu uvedeného v argumentu by se měl shodovat s počtem řádků v tabulce. Také, číslování by mělo začít s druhým řádkem!

Podmíněné formátování (5)
Seznamy a rozsahy (5)
Makra (procedury VBA) (63) \\ t
Různé (39)
Excel chyby a závady (3)

Jak najít hodnotu v jiné tabulce nebo PRP

Ve skutečnosti chci říct o příležitosti nejen v tomto článku. funkce rukouale také chcete ovlivnit a Vyhledávací deskaJako velmi související funkce s rozhodčím řízením. Každá z těchto funkcí má jak jeho výhody a nevýhody. Pokud je v kostce, pak UPR hledá nějakou hodnotu určenou USA mezi mnoha hodnotami umístěnými v jednom sloupci. Snad nejčastěji potřebu rozhodčího řízení dochází, když je nutné porovnat data, najít data v jiné tabulce, ze stejné tabulky přidávají data do druhého, na základě jakéhokoli kritéria atd.
Trochu lépe porozumět principu provozu UPR, je lepší začít s určitým praktickým příkladem. Tam je tabulka tohoto typu:
obr. 1

a z první tabulky musí být nahrazeny do druhého data pro každé jméno. Pro tři nahrávky to není problém a dělat ruce - všechno je zřejmé. Ale v životě se jedná o tabulky na tisíce záznamů a vyhledávání s nahrazením dat ručně může trvat ne jednu hodinu. Plus další pár létání v cíli: nejen skutečnost, že název je umístěn zcela jinak v obou tabulkách a počet záznamů v tabulkách se liší, takže více tabulek se nachází na různých listech / knihách. Předpokládám, že jsem vás přesvědčil, že náhrada těchto rukou není možností vůbec. Ale Vlookup) Bude zde nepostradatelná. Zároveň nebude muset prakticky nic dělat - pouze psát v první buňce sloupce z druhé tabulky (Kde je nutné nahradit data z první tabulky) Takový vzorec:
\u003d UPR ($ A2; List1! $ A $ 2: $ C $ 4; 3; 0)
Formulář můžete napsat buď přímo do buňky nebo pomocí funkčního dispečera výběrem v kategorii Odkazy a pole A odděleně určující nezbytná kritéria. Nyní kopírovat ( Ctrl.+C.) Buňku se vzorcem, který přiděluje všechny buňky kolony s koncem dat a vložky ( Ctrl.+PROTI.).

Za prvé, hlavní princip operace: UPR hledá hodnotu tabulky určená argumentem v prvním sloupci argumentu SKEKSING_NEW. . Když najdete požadovanou hodnotu, funkce vrátí hodnotu naproti nalezené hodnoty, ale z sloupce zadaného argumentem Number_stolbets. . S intervalovým pohledem, budeme to o něco později přijít. VDP může vrátit pouze jedno hodnoty - první vhodný pro kritérium. Pokud požadovaná hodnota není nalezena (chybí v tabulce)Poté bude výsledek funkcí # N / d . Nebojte se tohoto - je to dokonce užitečné. Budete vědět přesně, jaké záznamy nejsou, a tak můžete porovnat dva stoly mezi sebou. Někdy se ukáže, že vidíte: data jsou v obou tabulkách, ale problémy VD # n / d. Data ve vašich tabulkách nejsou tedy totožná. V některých z nich jsou extra nenápadné mezery (obvykle před hodnotou nebo po), nebo známky cyrilice jsou smíšeny s latinovými značkami. Stejný # N / d budou, pokud kritéria čísla a v požadované tabulce jsou zaznamenána jako text (Zpravidla se v levém horním rohu takové buňky objeví zelené trojúhelníky)a ve finále - jako čísla. Nebo naopak.

Popis argumentů argumentu
$ A2 - argument SKEKSING_NEW. (Zavolejme tomu Kritérium být krátký). To je to, co hledáme. Ty. Pro první záznam druhé tabulky bude Petrov S.A. Zde můžete určit buď text kritéria (v tomto případě by mělo být v uvozovkách - \u003d VD ("Petrov SA"; list1! $ A $ 2: $ C $ 4; 3; 0), nebo odkaz na buňka, s tímto textem (jako v příkladu). Existuje malý nuance: můžete také aplikovat Substituční symboly: "*" a "?" Je velmi výhodné, pokud potřebujete najít hodnoty pouze po části řádku. Například, nemůžete vstoupit zcela "Petrov S.A.", ale představit pouze příjmení a znamení hvězdičky - "Petrov *". Pak bude stažen libovolný záznam, který začíná v Petrovovi. Pokud potřebujete najít záznam, ve kterém je název "Petrov" nalézt kdekoli v řádku, pak můžete určit: "* Petrov *." Pokud chcete najít příjmení Petrov a bez ohledu na to, co iniciály budou na jméno-osobní jméno (pokud je název zaznamenáno ve formě Ivanova I.I.), pak zde je nejlepší: "Ivanov?" . Často je nutné určit svou hodnotu pro každý řádek (ve sloupci a příjmení a musíte je najít). V tomto případě odkazy na buňky sloupce A. Například v buňce A1 zaznamenané: Ivanov. Je také známo, že Ivanov je v jiném stole, ale po příjmení může být zaznamenán jméno a patronymické (nebo něco jiného). Ale musíme najít pouze řetězec, který začíná na příjmení. Pak musíte nahrávat následovně: A1 & "*". Tento záznam bude ekvivalentní "Ivanov *". V A1 je Ivanov zaznamenán, Ampersand (&) slouží ke sloučení jedné řady dvou textových hodnot. Asterisk v uvozovkách (jako by měl být text uvnitř vzorce). Takže dostaneme:
A1 & "*" \u003d\u003e
"Ivanov" & "*" \u003d\u003e
"Ivanov *"
Velmi pohodlné, pokud existuje mnoho znaků pro vyhledávání.
Pokud potřebujete zjistit, zda je slovo někde v řetězci, pak hvězdy dali na obou stranách: "*" & A1 & "*"

Sheet1! $ A $ 2: $ C $ 4 - argument Stůl. Určuje rozsah buněk. Pouze rozsah musí obsahovat data z první buňky s daty k nejlépe. To nemusí být uvedeno v rozsahu příkladu. Pokud řetězce 100, pak seznam1! $ A $ 2: $ C $ 100. Je důležité si pamatovat tři věci: Za prvé, to Stůl by měl vždy začít s tímto sloupcem, ve kterém hledáme Kritérium . A nic jiného. Jinak není nic nalezeno, nebude nebo výsledek nebude přesně ten, který očekává. Za druhé: argument Stůl musí být "pevné" . Co to znamená. Podívejte se na znaky dolaru - $? To je fixace (Přesněji se nazývá absolutní odkaz s rozsahem). Jak to je hotovo. Přidělit text odkaz (Pouze jeden rozsah je jedním z kritéria) A Zhmete. F4. Dokud nevidíme, že dolar se nezobrazí před názvem označení názvu názvu sloupce a před číslem řádku. Pokud to není provedeno, pak při kopírování vzorce bude tabulka argumentu "přesunout" a výsledek bude opět nesprávný. A poslední - tabulka musí obsahovat sloupce z prvního (ve kterém hledáte) k druhé (z nichž je nutné vrátit hodnoty). V příkladu Seznam1! $ A $ 2: $ C $ 4 - takže nebude možné vrátit hodnotu ze sloupce D (4), protože Tabulka je pouze tři sloupce.

3 - Number_stolbets. . Zde jednoduše zadejte číslo sloupce v argumentu Stůl, Hodnoty, z nichž musíme v důsledku toho nahradit. V příkladu je to datum přijetí - tj. Číslo sloupce 3. Kdybych potřeboval oddělení, uvázal bych 2, a kdybychom jsme potřebovali jednoduše porovnávat, pokud existuje jedna tabulková jména v jiném, pak by bylo možné označit a 1. Důležité: Argument Number_stolbets. by neměly překročit počet sloupců v argumentu Stůl . V opačném případě bude výsledek vzorce chyba #ODKAZ!. Například, pokud je uveden rozsah $ B $ 2: $ C $ 4 je zadáno a je nutné vrátit data z sloupce C, pak správně označte 2. Protože argument Stůl($ B $ 2: $ C $ 4) obsahuje pouze dvě sloupce - v a C. Pokud se pokusíte určit číslo sloupce 3 (jaký účet je na listu), dostaneme chybu #ODKAZ!protože Třetí sloupec v určeném rozsahu není prostě ne.

Praktický tip: Pokud má tabulka argumentů příliš velký počet sloupců a musíte vrátit výsledek z posledního sloupce, pak není nutné je vypočítat s počtem. To může být specifikováno takto: \u003d VD ($ A2; seznam1! $ A $ 2: $ C $ 4; numeric (Sheet1! $ A $ 2: $ C $ 4); 0). Mimochodem v tomto případě list1! Můžete také odstranit jako zbytečné: \u003d VD ($ A2; list1! $ A $ 2: $ C $ 4; Nutstolba ($ A $ 2: $ C $ 4); 0).

0 - Interval_prising - Velmi zajímavý argument. Může se rovnat buď pravdivé nebo nepravdivé. Okamžitě otázka vzniká: Proč v mém vzorci tam 0? Všechno je velmi jednoduché - Excel ve vzorcích může vnímat 0 jako lež a 1 jako pravdu. Pokud je v UPR určete tento parametr je 0 nebo lež, pak se vyskytne vyhledávání přesné dodržování zadaného kritéria. Nemá nic společného s náhradním znaménkem ("*" a "?"). Pokud používáte 1 nebo pravdu (nebo nezadáte poslední argument vůbec, protože ve výchozím nastavení je to pravda), pak ... velmi dlouhý příběh. Stručně řečeno - EPR bude hledat nejpodobnější hodnotu vhodný pro Kritérium . Někdy velmi užitečné. Pravda, pokud použijete tento parametr, je nutné, aby byl seznam v tabulce argumentů tříděna vzestupně. Upozorňuji na skutečnost, že třídění je nezbytné pouze v případě, že interval intervalu argumentu jste roven pravdě nebo 1. pokud 0 nebo lež - třídění není nutné.

Mnozí si to zřejmě všimli, že v mém obrázku jsem měl apartmány pro celé jméno. Toto není chybová chyba. V příkladu připojeném k článku je zobrazen, jak je to možné, aby je nahradilo a date, aniž by se změnila ruční argument Number_stolbets.. Zdálo se mi, že takový příklad by mohl dobře přijít.

Jak se vyhnout chybám # n / d (# n / a) v UPR?
Dosud Častý problém - Mnozí nechtějí vidět výsledek # n / E, pokud není nalezena náhoda. Je snadné se dostat kolem:
\u003d Jestliže (und (EGR ($ A2; list1! $ A $ 2: $ C $ 4; 3; 0)); ""; paže ($ A2; list1! $ A $ 2: $ C $ 4; 3; 0))
Pokud by arbitrita nenajde náhoda, buňka bude prázdná.
A uživatelé verzí aplikace Excel 2007 lze použít, pokud můžete použít, pokud:
\u003d Pokud nástroj (rameno A2; seznam1! $ A $ 2: $ C $ 4; 3; 0); "")

Slíbené vyhledávací deska

Tato funkce hledá hodnotu určenou parametrem SKEKSING_NEW. v argumentu Visible_massive. . A výsledek funkce je číslo polohy nalezené hodnoty Viewed_massive.. Je to číslo pozice, nikoli samotná hodnota. V zásadě to nebudu malovat stejným způsobem, protože hlavní body jsou přesně stejné. Kdybychom ji chtěli aplikovat na výše uvedenou tabulku, bylo by to takto:
\u003d Vyhledávací společnost ($ A2; List1! $ A $ 2: $ A $ 4; 0)
$ A2 je požadovaný. Zde je vše přesně stejné jako u UPR. Symboly substituce a přesně ve stejném výkonu jsou také povoleny.

Leaf1! $ A $ 2: $ A $ 4 - VIEWED_MASSIVE. Hlavní rozdíl od UPR je dovoleno určit pole pouze s jedním sloupcem. Mělo by to být ten sloupec, ve kterém se podíváme SKEKSING_NEW. . Pokud se pokusíte zadat více než jeden sloupec, funkce vrátí chybu.

Typ_spections (0) - stejně jako v UPR Interval_prising . Se stejnými funkcemi. Vyznačuje se příležitostí hledat nejmenší z požadovaného nebo největšího. Ale na tom nebudu zastavit v tomto článku.

V podstatě řešeny. Ale musíme vrátit počet pozic, ale samotná hodnota. Pokus o vyhledávání proto není pro nás vhodný. Alespoň jeden sám. Ale pokud se používá společně s funkčním indexem - pak to je to, co potřebujeme a ještě více.
\u003d Index (list1! $ A $ 2: $ c $ 4; vyhledávací deska ($ A2; list1! $ A $ 2: $ A $ 4; 0); 2)
Takový vzorec vrátí stejně jako ARD.

Index funkce argumenty
Leaf1! $ A $ 2: $ C $ 4 - pole. Jako tento argument naznačujeme rozsah, ze kterého chceme získat hodnoty. Může to být jak jeden sloupec, tak několik. Pokud je sloupec jeden, poslední argument nemusí určit funkci. Mimochodem, tento argument nemůže absolvovat s tím, že poukazujeme v argumentu vnímané společností By_Massive funkci vyhledávací desky.

Dále přichází číslo_stroke a number_stall. Je to jako číslo, které nahrazujeme vyhledávací desku, která se vrací do čísla pozice USA v poli. To je postaveno na něm. Index vrátí hodnotu z pole, která je v zadaném řádku (číslo serveru) pole a zadaného sloupce (number_number), pokud jsou sloupce více než jeden. Je důležité vědět, že v tomto svazku počtu řádků v argumentu řada funkčního indexu a počet řetězců v argumentu, který je vnímaný společností By_Massive, funkce vyhledávací desky by se měly shodovat. A začít se stejným řádkem. To je v běžných případech, pokud nebudete sledovat různé účely.
Také jako v případě HDP index v případě nenajde požadovanou hodnotu vrací # H / D. A dostat se kolem podobné chyby Můžete také:
Pro všechny verze aplikace Excel (včetně roku 2003):
\u003d Pokud (Und ($ A2; list1! $ A $ 2: $ A $ 4; 0)); "" "; Index (Sheet1! $ A $ 2: $ C $ 4; Vyhledávací společnost ($ A2; List1! $ A $ 2: $ A $ 4; 0); 2))
Pro verze 2007 a výše:
\u003d Pokud nástroj (index (list1! $! $ 2 $: $ C $ 4; vyhledávací deska ($ A2; list1! $ A $ 2: $ A $ 4; 0); 2); "")

Práce s kritérii je delší než 255 znaků
Existuje vyhledávací index a ještě další výhodu před PRD. Skutečnost je, že EPR nemůže hledat význam délka řetězce, která obsahuje více než 255 znaků. Stává se to jen zřídka, ale to se stane. Můžete samozřejmě klamat arbitráž a snížit kritérium:
\u003d PRP (PRST (PRT ($ A2; 1; 255), PRST (Sheet1! $ A $ 2: $ C $ 4; 1; 255); 3; 0)
Ale to je vzorec pole. A kromě toho ne vždy takový vzorec vrátí požadovaný výsledek. Pokud jsou první 255 znaků identické s prvním 255 znaky v tabulce, a pak se značky liší - vzorec to nevidí. Ano, a vrátí vzorec mimořádně textové hodnoty, které v případech, kdy by se čísla měla být vrácena, není příliš vhodná.

Proto je lepší použít takový mazaný vzorec:
\u003d Index (Sheet1! $ A $ 2: $ C $ 4; SUMPERED (pravda; list1! $ A $ 2: $ A $ 4 \u003d $ A2; 0)); 2)
Zde jsem použil stejné rozsahy pro čitelnost, ale v příkladu ke stažení se liší od těch, které jsou zde uvedeny.
Samotný vzorec je postaven na schopnostech funkce transformace Sumpera na masivní výpočty některých funkcí v něm. V tomto případě hledání hledání hledá pozici řádku, ve které se kritérium rovná hodnotě řádku. Zástrčné znaky zde nelze použít.

V příkladném provedení naleznete příklady použití všech popsaných případů a příkladem toho, proč jsou index a vyhledávací deska někdy vhodnější pro UPR.

Stáhnout příklad

(26.0 KIB, 14 191 ke stažení)

Článek pomohl? Sdílejte odkaz s přáteli! Video lekce

("Dolní lišty": ("TextStyle": "Static", "TextositionStatic": "Dno", "Textautohide": true, "TEXTOSITIONMARGINSTATIC": 0, "TEXTOSITIONMITIONDYNACIC": "Dottomleft", "Textositionmarginleft": 24 " TEXTOSITIONMARGINGRIGHT ": 24," TEXTOSITIONMARGINTOP ": 24," TEXTOSITIONMARGINGINTOP ": 24," TEXTOSITIONMARGINGINBOTTOM ": 24," TextEffect ":" Slide "," TextEffecisting ":" Symboly SylynaTcubic "," TexteffectDuation ": 600," TextEffectedLiredirection ": 600," Texteffectedledirection ":" Vlevo "" : 30, "TexteffectDelay": 500, "TexteffectedSeparate": false, "Texteffect1": "Slide", "texteffectsLidirection1": "vpravo", "texteffectsLidistance1": 120, "texteffectment1": "Texteffectmentment1": "usmírněno", "usmírněno", "texteferefectd)": 600 \\ t , "TexteffectDelay1": 1000, "texteffect2": "Slide", "texteffectedledirection2": "vpravo", "texteffectslidedistance2": 120, "texteffectmentment2": "usmírněno", "usmírněno", "texteffectDuation2": 600, "texteffectdelay2": 1500, "\\ t Textcsssss ":" Displej: Blok; Polstrování: 12px; Zarovnání textu: vlevo; "," TextbGCSS ":" Displej: Blok; Poloha: Absolutní; Top: 0px; vlevo: 0px; šířka: 100%; Výška: 100% Barva pozadí: # 333333; Opacity: 0,6; filtr: A LPHA (Opacity \u003d 60); "," TitleCss ":" Displej: Blok; Pozice: Relativní; Font: Bold 14px "Lucida Sans Unicode", "Lucida Grande", Sans-Serif, Arial; Barva: #fff; "," popiscss ":" Display: blok; Pozice: Relativní; Písmo: 12px "Lucida Sans Unicode", "Lucida Grande", Sans-Serif, Arial; Barva: #fff; Margin-top: 8px; "," buttocksss ":" Displej: blok; Pozice: Relativní; Margin-top: 8px; "," TexteffectResponsive ": TRUE," TITEGEFTRESSIONIZE ": 640," TitlecSSSSPONSIVE ":" velikost písma: 12px; "," PopiscSSSSSSSPONSIVE ":" Displej: Žádný! Důležité; "," ButtonCsSSSSSPONSIVE ": "" "," addGoooGrooglefonts ": false," GoogleFonts ":" "," textleftrightpercentForsatic ": 40))

V mnoha vyhledávacích vzorcích je velmi často možné použít vyhledávací funkci jako pomocný v kombinaci s jinými funkcemi jako: index, průmyslový komplex, GRP atd. Ale jaký přínos může být tato funkce generována. Z samého jména vyhledávací funkce je zřejmé, že jeho hlavní úkol je určení polohy zdrojové hodnoty, která obsahuje rozsah buněk nebo tabulky. Použijte tuto funkci je velmi jednoduchá pro pásma nebo tabulky s jedním sloupcem nebo jedním řádkem. Proto okamžitě komplikovat úkol a na konkrétním příkladu ilustrujeme, jak použít funkci vyhledávání pro tabulku se dvěma sloupcemi a dalšími.

Příklad hledání dvou sloupců Excel

Například vezměte si seznam automobilů ze flotily střední firmy, jak je uvedeno níže na obrázku:

V obou sloupcích se opakují jména automobilů a oddělení, ale nejsou zde žádné spárované duplikáty. Například v seznamu 2 vozů značky FORD, ale oba z různých oddělení. Pokud chceme znát číslo pozice tohoto automobilu, pak v důsledku toho, v důsledku toho se funkce Popisos vrátí do polohy v rozsahu, kde je nalezena první hodnota - 3. To je Ford z obchodní oddělení:

Co dělat, pokud se zajímáme o FORD z marketingového oddělení? Kromě toho chceme používat pouze funkci POSPOST, aniž by se uchýlila k vzorci s kombinacemi indexu jiných funkcí atd. Cesta z této situace je určování nastavení argumentu a provedení funkce v poli. Pro tohle:

1. V buňce B16 zadejte hodnotu FORD a v C16 buňce jméno úrokového oddělení je marketing.
2. V buňce C17 zadejte funkci s následujícími argumenty:
3. Po zadání potvrzení funkce stiskněte klávesu CTRL + SHIFT + Zadejte kombinaci klávesové zkratky, protože by měla být provedena v poli. Pokud se vše provádí správně v řádcích, zobrazí se vzorce bracovní.

Jak můžete vidět funkci, nezávisle vyrovnaný s řešením úkolu.

Popis příkladu, jak funguje funkce vyhledávání:

Aby byla funkce vyhledávání pracovat s tabulkou se dvěma sloupcemi jako u jednoho, které jsme použili argumenty operátora ve svých argumentech. Vzhledem k tomuto operátorovi je první argument pro funkci nyní hodnotu fordmarketingu. Z tohoto důvodu první Ford z obchodního oddělení není zohledněn, protože nyní dva Ford pro funkci jsou různé hodnoty (Ford Sales a fordmarketing). Rozsah rozsahu se nyní rozšiřuje na 2 sloupce, stejně jako s operátorem &, který aplikujeme ve druhém argumentu k lepicím hodnotám dvou sousedních rozsahů. Hodnoty jsou tedy prováděny současně ze dvou sloupců automobilu a oddělení.

Přečtěte si také: Funkce Index a vyhledávání Excel a příklady jejich použití

Hodnoty vyhledávání v rozsahu tabulky Excel na sloupcích a řadách

Předpokládejme, že vaše zpráva obsahuje tabulku s velkým množstvím dat na množství sloupců. Vizuální analýza těchto tabulek je velmi obtížná. A jeden z úkolů pro práci se zprávou je analyzovat data na záhlaví řádků a sloupců týkajících se určitého měsíce. Na první pohled to je velmi jednoduchý úkol, ale nelze jej vyřešit pomocí jedné standardní funkce.

Vyhledejte polohu položky v seznamu s vyhledávací deskou (shoda)

Ano, samozřejmě můžete použít nástroj: "Domů" - "Editace" - "Najít" Ctrl + F vyvolat vyhledávací pole na listu Excel. Nebo vytvořit podmíněné formátování pro tabulku. Ale pak nebude dále počítač s dosaženými výsledky. Proto je nutné vytvořit a správně aplikovat odpovídající vzorec.

Hodnoty vyhledávání v poli Excel

Schéma řešení úkolů se dívá na tímto způsobem:

• v B1 buňce zadáme data, o kterou máte zájem;
• v buňce B2 se zobrazí záhlaví sloupce, která obsahuje hodnotu B1 buňky
• v buňce B3 se zobrazí název řádku, který obsahuje hodnotu B1 buňky.

Ve skutečnosti musíte hledat souřadnice v aplikaci Excel. K čemu to je? Poměrně často musíme dostat souřadnice tabulky podle hodnoty. Trochu se podobá návratové analýze matrice. Specifický příklad V kostce vypadá takhle. Nastavený cíl v číslech je zdrojová hodnota, musíte určit, kdo a kdy nejblíže tomuto účelu. Například používáme jednoduchou datovou matici se zprávou počtu prodaného zboží ve třech čtvrtletích, jak je uvedeno níže na obrázku. Je důležité, aby se všechny numerické ukazatele shodovaly. Pokud není žádná touha ručně vytvořit a vyplnit excel tabulka Z prázdného listu, pak na konci výrobku si můžete stáhnout s připraveným příkladem.

Důsledně zvažujeme možnosti řešení jiné složitosti a na konci článku - konečný výsledek.

Hodnoty vyhledávání v sloupci Excel

Nejprve se naučte dostat titulky sloupců tabulky podle hodnoty. Chcete-li to provést, postupujte takto:

1. V buňce B1 zadejte hodnotu převzatou z tabulky 5277 a zvýrazněte jeho pozadí modře pro čitelnost vstupního pole (dále bude zavedena do buňky B1 Ostatní čísla experimentovat s novými hodnotami).
2. V buňce C2 zadáváme vzorec pro získání záhlaví sloupce, která obsahuje tuto hodnotu:
3. Po zadání vzorce pro potvrzení stiskněte kombinaci horkých kláves Ctrl + Shift + Enter, protože vzorec musí být proveden v poli. Pokud se vše provádí správně v řádku vzorců kolem okrajů, objeví se kudrnaté rovnátka ().

V buňce C2 se vzorec vrátil písmeno D odpovídající hlavičku sloupce listu. Jak vidíte vše s konverge, hodnota 5277 je obsažena v buňce sloupce D. Doporučujeme se podívat na vzorec, abyste získali celou adresu aktuální buňky.

Hodnoty vyhledávání v řetězci Excel

Nyní získáme číslo řádku pro stejnou hodnotu (5277). Chcete-li to provést, zadejte následující vzorec v C3 Cell:

Po zadání vzorce znovu potvrďte, stiskněte kombinaci kláves CTRL + SHIFT + ENTER a získejte výsledek:

Vzorec se vrátil číslo 9 - našel záhlaví řetězce listů podle odpovídající hodnoty tabulky. V důsledku toho máme celá adresa Hodnoty D9.

Jak získat záhlaví sloupce a název tabulky

Nyní se naučte, jak přijímat souřadnice není celý list, ale aktuální tabulka. Stručně řečeno, musíme najít hodnotu 5277 místo D9 Získejte titulky:

• pro sloupec tabulku - březen;
• pro řetězec - Product4.

Vyřešit tento úkol Vzorec použijeme již získanými hodnotami v buňkách C2 a C3. Chcete-li to udělat, proveďte to:

V důsledku toho jsou vnitřní souřadnice tabulky získány penězi - březen; Produkt 4:

Na první pohled se vše funguje dobře, ale co když tabulka obsahuje 2 identické hodnoty? Pak problémy mohou vzniknout s chybami! Doporučujeme také vidět alternativní řešení Hledat sloupce a řádky podle hodnoty.

Vyhledejte identické hodnoty v rozsahu Excel

Pro řízení přítomnosti duplikátů mezi hodnotami tabulek vytvořte vzorec, který nás může informovat o dostupnosti duplikátů a spočítat jejich množství. K tomu představujeme vzorec do buňky E2:

Kromě toho, pro rozsah tabulkové části vytvoříme pravidlo podmíněného formátování:

Jak je vidět v přítomnosti duplikátů vzorce pro titulky, vezme titul z prvního duplikátu horizontálně (zleva doprava). A vzorec pro získání názvu (číslo) řádku vezme číslo z prvního duplikátu svisle (shora dolů). Pro opravu toto řešení Existují 2 způsoby:

V tomto případě změníme vzorce nebo jeden nebo druhý, ale ne dva najednou. Stojí za to připomenout, že starý vzorec by měl zůstat v C3 Cell:

Zde jsou správně zobrazeny souřadnice prvního duplicitního vertikálního (shora dolů) - I7 pro list a srpen; Product2 pro tabulku. Nechme tuto možnost pro další konečný příklad.

Vyhledejte nejbližší hodnotu v rozsahu Excel

Tato tabulka stále není dokonalá.

Koneckonců, když analyzujete, musíte přesně znát všechny své hodnoty. Pokud generované číslo v buňce B1 není nalezen v tabulce, pak se chyba vrátí - # význam! Ideální by to bylo, že vzorec v nepřítomnosti v tabulce zdrojových čísel vybral nejbližší hodnotu, která obsahuje tabulku. Chcete-li vytvořit takový program pro analýzu tabulek v buňce F1, zadejte nový vzorec:

Poté, ve všech ostatních vzorcích změňte odkaz namísto B1 by měl být F1! Musíte také změnit odkaz v podmíněném formátování. Zvolte: "Domů" - "Styly" - "Podmíněné formátování" - "Řízení pravidel" - "Změnit pravidlo". A zde v parametrech určete F1 místo B1. Chcete-li zkontrolovat provoz programu, zadejte číslo, které není v tabulce do buňky B1, například: 8000. To bude mít za následek konečný výsledek:

Nyní můžete zadat libovolnou počáteční hodnotu a samotný program vybere nejbližší číslo, které obsahuje tabulku. Poté zobrazuje záhlaví sloupce a název řetězce pro aktuální hodnotu. Pokud například zadáte číslo 5000, dostaneme nový výsledek:

Stáhněte si příklad nalezení hodnoty v pásmu Excel

Náš program v aplikaci Excel nalezl nejbližší hodnotu 4965 pro originál - 5000. Takový program může být užitečný pro automatické vyřešení různých analytických úkolů v oblasti obchodního plánování, stanovení cílů, vyhledávání racionálního řešení atd. A získané linie a sloupce umožňují dále rozšiřovat výpočetní schopnosti tohoto druhu zpráv pomocí nových vzorců Excel.

Úloha 1. Vytvoření rozevíracího seznamu

1. Otevřete soubor Ex03_1.xlsx..

2. On List. Cena K dispozici je tabulka zboží s cenami.

3. Nastavte názvy do rozsahu s názvem zboží ( Produkt) a tabulka s ceníkem ( Stůl) (obrázek 1). Jména rozsahů jsou stanoveny pro jejich další použití ve vzorcích a usnadňují práci s rozsahy. Název rozsahu lze nastavit pole Název buňky nebo v kontextová nabídka Vyberte příkaz Přidělit jméno (Samozřejmě, že v jednom případě musí být rozsah buněk předem vybrán, jak je znázorněno na obrázku 1).

Obrázek 1.

Na prodejní list ve sloupci Název produktu Vytvořte rozevírací seznam a vyplňte jej datem.

Chcete-li vytvořit rozevírací seznam, vyberte buňky (můžete okamžitě vybrat několik), ve kterém chcete rozevírací seznam a vybrat na kartě Data knoflík Kontrola dat (Obrázek 2).

Obrázek 2.

Na první záložce Parametry Z rozevíracího seznamu Datový typ Vyberte možnost Seznam a zadejte řádek Zdroj Značka se rovná názvu rozsahu s požadovanými daty (tj. \u003d Produkt.) (Obrázek 3). Kliknutí OK.

Výsledek vytvoření rozevíracího seznamu je zobrazen na obrázku 4.

Pokud se sada hodnot ve zdroji může lišit, je lepší vložit nebo mazat data ve středu seznamu.

Obrázek 3.

Obrázek 4.

Seznámení s funkcemi vyhledávací desky a indexu

Funkce vyhledávání a indexu se používají hlavně pro automatické nahradit data do tabulky ze zadaného rozsahu.

Syntaxe Funkce

Dodatek (požadovaný_station, Array, typ_denzace)

Array.- Jedná se o blok sestávající z jednoho sloupce nebo jednoho řetězce.

Type_specifikace- Je to číslo -1, 0 nebo 1.

Pokud type_specifikacerovna 1, pak funkce Vyhledávací deskanajde největší hodnotu, která je menší než sKEKSING_NEW.nebo stejně jako k němu. nachází se array.musí být objednat vzestupně.

Pokud type_specifikacehavran 0, že funkce vyhledávací deskynajde první hodnotu, která přesně se rovná argumentu sHEKEPING.nachází se array.mohou být porušeny.

Pokud type_specifikacese rovná -1, pak funkce Vyhledávací deskanajde nejmenší hodnotu, která je větší než požadovanýnebo stejně jako k němu. Visible_massive.musí být objednán sestupně.

Pokud type_specifikacevynechán, pak se předpokládá, že je to stejné 1 .

Funkce Vyhledávací deskavrátí polohu požadované hodnoty v poli a ne samotnou hodnotu.

Funkce INDEX Má dvě formy. Budeme zvažovat pouze jeden.

Index (tabulka; number_name; number_number)

Tato funkce vybere prvek zadaný čísly řádku a sloupců z obdélníkového bloku (tabulka) a tato čísla se počítají z levého horního bloku prvku.

Podívejme se na práci těchto funkcí na konkrétních příkladech.

Úloha 2. Seznámení s funkcí vyhledávání

Pomocí vyhledávací funkce () určete polohu zboží s názvem "Jogurt" v rozsahu Produkt. Pro tohle:

1. B. elektronická kniha Ex03_1.xlsx. Jít na list Cena a nainstalujte kurzor listu do libovolné volné buňky;

2. Zadejte vzorec k této buňce \u003d vyhledávací deska ("jogurt"; produkt; 0), tj. Specifikujeme používat tuto funkci, kterou musíme najít prvek "jogurt" (hodnota textu v funkcích je vždy označena v citacích) v rozsahu Produkt, typ porovnání 0 - přesná náhoda;

stiskněte Enter. V důsledku toho byste měli dostat číslo 7 (Obrázek 5).

Obrázek 5.

Opravdu, pokud se podíváte na rozsah ProduktPoloha jogurtu odpovídá tomuto obrázku. Je třeba si pamatovat, že pozice hodnoty v poli, tj. Zadaný rozsah se počítá od začátku rozsahu. Ukazuje se, že pokud přidáme nový řetězec na začátku tabulky, hodnota "jogurtu" se posune na lince s číslem 8, ale rozsah Produkt Začíná hodnotou "mléka", takže od začátku rozsahu bude poloha jogurtu v každém případě odpovídající obr. 7 (obr. 6).

Obrázek 6.

Všimněte si, že jako hodnota funkce vyhledávací funkce () můžete zadat jak samotnou hodnotu a název buňky, ve které je tato hodnota. Například nahrávání \u003d vyhledávací deska (A7; produkt; 0) umožní získat podobný výsledek jako při použití záznamu \u003d vyhledávací deska ("jogurt"; produkt; 0).

Úkol 3. Splňte funkci indexu

Připomeňme si, že funkce indexu () vybere prvek zadaný čísly řádků a sloupcem z obdélníkového bloku (tabulka) a tato čísla se počítají z levého horního bloku prvku.

Použití funkce indexu () Najít v rozsahu Produkt Prvek umístěný na páté poloze. Pro tohle:

1. V e-knize Ex03_1.xlsx. Na listu Cena Namontujte kurzor listu do libovolné volné buňky;

2. Do této buňky \u003d index zadejte následující funkci (5). Stiskněte Enter. Výsledek je znázorněn na obrázku 7.

Obrázek 7.

Upozorňujeme, že index ukazovali pouze pro hodnoty rozsahu a číslo řádku. Číslo sloupce, které jsme neuvedli.

Tento parametr není povinný, pokud hledáme hodnotu v tabulce (rozsah) sestávající z jednoho sloupce. Pokud potřebujeme najít hodnotu v tabulce (rozsah) sestávající z několika sloupců, pak ve vzorci, pro přesnější vyhledávání hodnoty, musíte zadat číslo sloupce.

V nové buňce zadejte vzorec \u003d index (tabulka; 8; 2). V důsledku toho najdete náklady na šunku (obrázek 8).

Postavení 8.

Automatická substituce dat

Při práci s rozevíracím seznamem můžete automatizovat zadání dat do tabulky. Existuje například ceník obsahující názvy zboží a jejich cenu. Můžete zorganizovat výběr názvu zboží ze seznamu a automatickou náhradu ceny zboží ve finálovém stole.

Automatická náhrada dat z referenční tabulky je založena na kombinovaném použití funkcí indexu a vyhledávací desky.

Úloha 4. Kombinace funkcí vyhledávací desky a indexu

V elektronické knize Ex03_1.xlsx. Na listu Odbyt na šermu CenaPomocí funkce MS Excel poskytují automatickou náhradu nákladů na zboží z listu Cena, v závislosti na hodnotě na listu vybraném z rozevíracího seznamu Odbyt.

1. Nainstalujte kurzor listu do buňky C2 a do něj zadejte následující vzorec:

2. Stiskněte ENTER. V důsledku toho obdržíte v C2 CuBu náklady na tento produkt, jehož název je uveden v sousedním sloupci (obrázek 9). Při výběru ze rozevíracího seznamu jiného jména produktu bude odpovídajícím způsobem změněna cenová hodnota.

Obrázek 9.

3. Pomocí značky autokomprese duplikujte tento vzorec na konec tabulky (obrázek 10).

Obrázek 10.

Rozhodněte se tento vzorec. Tento vzorec začíná pracovat s funkcí vyhledávací desky, která nám umožňuje najít hodnotu hodnoty z B2 Cell na listu Odbyt v dosahu Produkt S listem Cenaa typ přesnosti 0. Například pokud používáte seznam zboží, které je uvedeno na obrázku 10, pak funkce vyhledávací desky hledá pozici bochníku, jehož název je uveden v buňce B2 z sloupce. Název produktu v dosahu Produkt Na listu Cena. Tato pozice bude rovna 3. V důsledku toho se vzorec zavedl USA v paměti počítače

\u003d Index (tabulka; vyhledávací deska (b2; produkt; 0); 2)

zobrazit se

\u003d Index (tabulka; 3; 2)

Parametr "3" je výsledkem pracovní funkce vyhledávací desky.

Dále funkce vstupuje do funkce, která bude hledat v rozsahu Stůl Na listu Cena Hodnotu na křižovatce třetího řádku a druhého sloupce. (Připomínáme, že rozsah Stůl Se skládá ze dvou sloupců). Tato hodnota bude cena zboží, v tomto případě chleba \u003d 27.

Úloha 5. Registrace závěrečné tabulky

Na listu Odbyt na šermu číslo Zadejte libovolné hodnoty.

Vypočítejte hodnoty ve sloupci CELKOVÝ.

Úloha 6. Nezávislý úkol

Otevřít soubor Rámce.xls. . Je nutné automatizovat změnu v platu na listu. Rámce .

Například, to trvá někoho od manažerů, aby přeložit a vedoucí manažery nebo konzultant, aby přeložili do pohybů. Je nutné provést tuto operaci pro čtyři zaměstnance. Spolu s úřadem by se mělo změnit a plat.

Vytvořte nový list a pojmenujte jej. Stát .

Kopírovat na list Stát Sloupce Pozice a Plat.

Odstraňte opakované zaměstnance.

Pro tohle:

1. Zvýrazněte sloupci poštovních a platů.

2. Na kartě Data Klikněte na tlačítko Odstranit duplikát .

Funkce indexu a expozice v aplikaci Excel - nejlepší alternativa pro VDP

Vyjměte zaškrtnutí sloupce Plat.

4. Klikněte na tlačítko OK..

Aby bylo možné změnit pozice na listu Rámce V sloupci pošta vytvořte rozevírací seznam. Bude to nutné změnit a oficiální plat, pro tento proces automatizovat tento proces tak, aby při změně příspěvku, Excel změní samotný plat. Chcete-li to provést, použijte funkce: vyhledávání a index.

Metodické pokyny

1. Zvýrazněte na list Státrozsah příspěvků. Jméno vybraného názvu rozsahu pozice.

2. Zvýrazněte na list Státplatový rozsah. Jméno vybraného názvu rozsahu plat.

3. Na listu Personálvložka Platzadejte vzorec, který by hledal list Státv dosahu pozicepozice relevantních pracovních míst a pak z rozsahu platprostěradlo Státvložený plat odpovídající nalezené poloze.

4. Po vstupu do vzorce se ujistěte, že získaný plat skutečně odpovídá platu pozice zaměstnance z listu Stát.

5. Změňte plat z CH. Účetní na listu Stát. Zkontrolujte, zda se stala změna v odpovídajícím platu na listu Personál.

Vyhledávání na stránkách:

Funkce PRD s více kritérii vyhledávání v aplikaci Excel

Funkce VRP (vertikální zobrazení) je vyhledána tabulku s daty a na základě kritérií vyhledávacích dotazů vrátí odpovídající hodnotu ze specifického sloupce. Velmi často je nutné použít několik podmínek na základě vyhledávacího dotazu. Ale ve výchozím nastavení tato funkce nemůže zpracovat více než jednu podmínku. Proto je nutné použít velmi jednoduchý vzorec, který vám umožní rozšířit možnosti funkce FPR v několika sloupcích současně.

Práce funkce zbraní pro několik kritérií

Pro jasnost budeme analyzovat vzorec PRD s příkladem několika podmínek. Například budeme používat schematickou zprávu o příjmech obchodních zástupců na čtvrtletí:

Tato zpráva musí najít rychlost příjmů pro konkrétní obchodní zástupce v určitém datu. Vzhledem k vyhledávacím výrazu musí naše žádost obsahovat 2 podmínky:

1. - Datum doručení příjmů do pokladny.
2. - Příjmení obchodního zástupce.

Chcete-li tento problém vyřešit, použijeme funkci PRD v několika podmínkách a tvoří následující vzorec:

1. V C1 buňce zadejte první hodnotu pro první kritérium vyhledávacího dotazu. Datum: 03/22/2017.
2. V buňce C2 zadejte název obchodního zástupce (například Novikov). Tato hodnota bude použita jako druhý argument vyhledávacího dotazu.
3. V C3 buňce obdržíme výsledek hledání, pro to by mělo být zavedeno tam:
4. Po zadání vzorce pro potvrzení stiskněte kombinaci horkých kláves Ctrl + Shift + Enter, protože vzorec musí být proveden v poli.

Výsledkem hledání v tabulce pro dvě podmínky:

Zjistil výši příjmů konkrétního obchodního zástupce v určitém datu.

Analýza principu vzorce pro funkci FMP s několika podmínkami:

První argument funkce \u003d VD () je první podmínkou pro nalezení hodnoty zprávy o zprávě o příjmech obchodních zástupců. Ve druhém argumentu je virtuální tabulka vytvořena v důsledku masivního výpočtu logické funkce \u003d if (). Každé příjmení v rozsahu buněk B6: B12 se porovnává s hodnotou v C2 buňce. Tak, konvenční datová řada dat s prvky hodnot pravdy a lži je vytvořena v paměti.

Poté kvůli vzorci, v paměti programu, každý skutečný prvek je nahrazen sadou dat 3 elementů:

1. prvek - datum.
2. prvek - příjmení.
3. prvek - příjmy.

A každý falešný prvek v paměti je nahrazen 3-prvkový sadou prázdných textových hodnot ("").

V důsledku toho je v paměti programu vytvořena nová tabulka, s jakou funkcí PRD již funguje. Ignoruje všechny prázdné sady datových prvků.

Funkce Vyhledávání aplikace Microsoft Excel

A neprázdné prvky jsou porovnány s hodnotou C1 buněk použité jako první kritérium vyhledávacího dotazu (datum). V aplikaci Word je tabulka v paměti kontrolována funkcí FPR s jedním podmínkou vyhledávání. S pozitivním výsledkem srovnání vrátí funkce hodnotu prvku ze třetího sloupce (příjmy) konvenční tabulky. Je to proto, že třetí argument indikuje číslo kolony, jehož jsou hodnoty přijaty. Stojí za zmínku, že pro prohlížení argumentů funkcí je určena celá tabulka (ve druhém argumentu), ale hledání samotné vždy přichází na první sloupec v zadané tabulce.

Stáhněte si příklad funkce funkce s více podmínkami v aplikaci Excel

A ze kterého sloupec převzít návratovou hodnotu je již uvedena ve třetím argumentu.

Číslo 0 v posledním argumentu funkce označuje, že náhoda musí být naprosto přesná.

Manipulační pole v MS Excel

Účel práce: nákup dovedností k práci s poli; Studovat vlastnosti vstupního vzorce pole.

Metodické pokyny

Pole je přilehlý obdélníkový rozsah vzorců, které MS Excel procesy jako celek.

Výsledek výpočtů může být jak pole a jedno číslo. Použití vzorce pole umožňuje získat kompaktní řešení pro dostatečně složité úkoly a v některých případech je bez nich nemožné. Výměna opakovaných vzorců pole pole umožňuje ušetřit paměť, protože MS Excel udržuje řadu vzorců jako jeden vzorec.

Příklad 1. Je nutné vypočítat náklady na každý typ výrobku (obr. 5.1).

Obr. 5.1. Zdrojová data například 1

Rozhodnutí.Provádění výpočtů pomocí vzorců pole:

1. Přidělte rozsah, který vstoupí do vzorce pole.

2. Zadejte vzorec . Tento záznam Zahrnuje násobení odpovídajících prvků polí. Je třeba poznamenat, že namísto specifikace rozsahů buněk je přípustné používat svá vlastní jména (například :)).

3. Dokončete vstup vzorce současným stisknutím Shift-Ctrl-Enter. V tomto případě se automaticky objevují kariové závorky, rámování vzorce. Massif vzorec bude zaznamenán ve všech buňkách přiděleného rozsahu.

Po vytvoření pole obsahující vzorec, nemůžete vložit buňky v rozsahu pole, odstranit část rozsahu nebo upravit samostatnou buňku uvnitř rozsahu. Pole lze změnit pouze jako celek. Tak například změnit řešit vzorec, vyberte rozsah pole, upravte vzorec a dokončete změny stisknutím Shift-Ctrl-Enter.

Chcete-li opravit vzorec pole směrem k poklesu nebo zvýšení velikosti jednotky, můžete také nabídnout následující algoritmus.

1. Přidělte rozsah s hmotným vzorcem masivu a přidejte symbol apostrofy " ’ " Vzorec se změní na text.

2. Zadejte tento text do všech buněk vyhrazeného rozsahu ( Ctrl-Enter.).

3. Vyberte nový rozsah pro vzorec pole, upravte potřebné adresy, vyjměte symbol apostrofy a dokončete změny stisknutím tlačítka Shift-Ctrl-Enter.

Předpokládejme, že potřebujete získat obecný výsledek bez výpočtu nákladů pro každý výrobek. K tomu může být v buňce zaznamenána řešitelným vzorcem. Všimnásobíme opět, že kudrnaté rovnátka by neměly být napsány, protože jsou automaticky vloženy jako znamení práce s řadou (explicitní úloha úložných nástrojů povede k tomu, že vzorec bude vnímán jako text).

Pokud výpočet nepodléhá celkové výši výdajů, ale pouze náklady v určité fázi zadávání zakázek. Chcete-li to udělat, v buňce zaznamenává číslo fáze. Požadovaný odhadovaný vzorec v buňce bude mít formulář :. \\ T

V MS Excel je řada funkcí, které vrací výsledek ve formě pole hodnot. Přirozeně obsahující vzorce obsahující (například atd.) Měly být podávány jako vzorce polí a vytváření rozsahu buněk požadované velikosti.

V MS Excel je přípustné používat konstantní pole. Tak například nahrávání označuje vektorovou čáru; - Vektorový sloupec, matrice.

Funkce vyhledávací desky

V tomto případě je vyžadováno zavedení figurálních závorek.

Příklad 2. Vypočítejte součet dvou matricových konstantů může být příkaz. Externí kudrnaté šle by měly být vytvořeny automaticky při vytváření dimenze 2'2.

Příklad 3.Společnost vyrábí produkty dvou typů a využívá suroviny dvou typů. Náklady na náklady na suroviny na jednotku výrobků z každého druhu jsou stanoveny matricí, ve které je množství surovin spotřebovaných na výrobu výrobků typu 1 a 2 indikovány na lince.

Náklady na jednotku surovin každého typu jsou nastaveny matricí. Jaké jsou celkové náklady podniku na výrobu 100 kusů výroby prvního typu a 150 jednotek druhého typu?

Rozhodnutí. Za účelem stanovení hodnoty surovin pro výrobu výrobků každého typu vynásobte matrici hodnoty hodnoty jednotky surovin B. O matrici nákladů na suroviny (obr. 5.2):

Obr. 5.2. Vzorek nalezení nákladů na suroviny pro výrobu

jednotky výrobků každého typu

Objemy výroby Pojďme nastavit sloupec Matrix, pak celkové náklady na výrobu výrobků se rovnající produktu matrice řetězce Z na sloupci matrice Q.(Obr. 5.3):

Obr. 5.3. Výpočet vzorku celkových nákladů podniků

Příklad 4.Vyřešit systém lineárních rovnic, kde.

Rozhodnutí.Řešení tohoto systému je vektor . Chcete-li najít vektor, představujeme prvky matrice v rozsahu buněk a prvky vektoru v rozsahu buněk.

Potom je třeba vybrat rozsah, kde jsou umístěny prvky vektoru roztoku. V tomto rozsahu představujeme vzorec Shift-Ctrl-Enter. V důsledku akcí akcí v rozsahu buněk se zobrazí systém rovnic (obr. 5.4).

Obr. 5.4. Roztok vzorku příkladu 4

Příklad 5.

Vypočítejte kvadratický tvar, zatímco.

Rozhodnutí.Chcete-li najít hodnotu této kvadratické formy, představujeme prvky matric v rozsahu buněk a prvky vektoru v rozsahu buněk. Pak musíte vybrat buňku, kde umístit hodnotu kvadratického formuláře. V této buňce představujeme vzorec \u003d Mumznom (MOBUM (A2: B3; A2: B3)); D2: D3) a dokončili vstup vzorce stisknutím kombinace klíčů Shift-Ctrl-Enter. V důsledku akcí akcí v rozsahu buněk se objeví systém rovnic (obr. 5.5).

Obr. 5.5. Roztok vzorku příkladu 5

⇐ Předchozí45678910111213Další ⇒

Podobné informace:

Vyhledávání na stránkách:

Excel Odkazy a funkce pole

Tento článek popisuje některé funkce práce s odkazy a polí:

1. ČÁRA;
2. SLOUPEC;
3. ADRESA;
4. Dwarl;
5. Hřeben;
6. Číslice;
7. Posuny;
8. STRANA.

PRP Funkce

Vertikální první rovnost. Hledáte náhodu na klíč v prvním sloupci určitého rozsahu a vrátí hodnotu ze zadaného sloupce tohoto rozsahu v řádcích zápasů.

Syntaxe: \u003d. Prsa(klíč; rozsah; number_stall; [interval_view]), kde

• klíčem je povinným argumentem. Požadovanou hodnotu, pro kterou chcete vrátit hodnotu.
• rozsah je povinným argumentem. Tabulka, ve které je nutné najít hodnotu pro klíč. První sloupec tabulky (rozsahu) musí obsahovat hodnotu, která se shoduje s klíčem, jinak bude vrácena chyba # n / d.
• number_libes - povinný argument. Číslo sekvence sloupce v zadaném rozsahu, ze kterého je nutné vrátit hodnotu v případě klíče shody.
• interval_prising - volitelný argument. Logická hodnota označující typ zobrazení:
  • FALSE - funkce hledá přesnou shodu v prvním sloupci tabulky. Pokud je to možné několik shody, pak se nejprve vrátí. Pokud není náhoda nalezena, funkce vrátí chybu # n / d.
  • Pravda - funkce hledá přibližnou náhodu. Je výchozí hodnota. Přibližný zápas znamená, že nebyla nalezena žádná shoda okolností, funkce vrátí hodnotu předchozího klíče. Současně se předchozí bude považován za klíč, který jde před požadovaným způsobem podle třídění od méně do více nebo z A do Ya. Před použitím funkce s tímto intervalem prohlížením, předem druhu prvního sloupce Stůl vzestupně, protože pokud není provedeno, funkce může vrátit nesprávný výsledek. Když bylo zjištěno několik shody, poslední z nich se vrátí.

Je důležité, abychom nebyli zmateni, že číslo sloupce není indikováno indexem na listu, ale v pořadí v určeném rozsahu.

Příklad použití:

Obsah obsahuje 3 tabulky. První a druhý tabulky mají zdrojová data. Třetí tabulka se shromažďuje z prvních dvou.
První tabulka zobrazuje kategorie zboží a umístění každé kategorie.
Ve druhé kategorii existuje seznam všech položek označujících ceny.
Třetí tabulka obsahuje část zboží, pro které je nutné určit cenu a místo.

Za cenu musíte použít funkci FPR s přesnou shodou okolností (interval lži), protože tento parametr je definován pro všechny zboží a neposkytuje použití ceny jiného produktu, pokud dosud není definován náhodou.

Pro určení umístění zboží se používá HDP s přibližnou shodou okolností (interval pohled na pravdu), neboť distribuce zboží provádí kategorii. Vzhledem k tomu, že název zboží se skládá z názvu kategorie plus dalšího textu, podle podmínek třídění z A na I, jméno zboží půjde ihned po názvu kategorie, tedy Tabulka nezjistí klíč k tlačítku zpřísněno první hodnotu shora.

Tento přístup by v zásadě zařídil, pokud pro zboží, pro které není kategorie, místo nebylo utaženo. Věnujte pozornost produktu "cibule moskevské oblasti". Pro něj je umístění "stelleg č. 2" definováno, i když v první tabulce neexistuje "cibule". To se děje vše ze stejných důvodů, kdy jsou funkce detekovány funkce, pak určuje hodnotu hodnoty menšího klíče k třídění klíče a pro "Loko Moskevské oblasti" je kategorie "brambory ".

Lze jej odstranit určením kategorie z názvu produktu pomocí textových funkcí Levsmv (C11; Vyhledávání (""; C11) -1), které bude vráceno všem znakům do prvního prostoru, a také změnit interval prohlížení na přesný.

Kromě toho popsané funkce PRD umožňuje textové hodnoty zástupných znaků - * (hvězdiček - libovolný počet libovolných znaků) a? (jeden znak). Například pro požadovanou hodnotu "*" & "ivan" & "*" lze oslovit Ivan, Ivanov, pohovky, atd.

Také tato funkce může vyhledat hodnoty v polích - \u003d VD (1; (2; (2; "dva": 1; "jeden"); 2; false) - výsledek provedení linky "dva".

Funkce GPR

Horizontální první rovnost. Hledáte náhodu na klíč v prvním řetězci konkrétního rozsahu a vrátí hodnotu ze zadané linie tohoto rozsahu v shodném sloupci s klíčem.

Syntaxe: \u003d GPR (klíč; rozsah; number_name; [interval_prising]).

Vzhledem k tomu, že funkce je podobná funkce PRD, s výjimkou, že používá horizontální tabulky namísto vertikálních, pak popisy a příklady použití jsou také vhodné pro GPR, s přihlédnutím k uvedeným rozdílům.

Funkční řetězec

Určuje a vrátí číslo řádku zadaného odkazu buňky.

Příklad použití:

=ČÁRA(D4) - výsledek 4.
=ČÁRA() - Funkce vrátí číslo řetězce, ve kterém je umístěn.

Funkce sloupce

Vrátí počet buněčných sloupců označených odkazem.

Příklad použití:

=SLOUPEC(C4) - Vzorec vrátí hodnotu 3.
=SLOUPEC() - Funkce vrátí číslo sloupce, do kterého se nachází.

Funkční adresa

Vrátí text představující adresu buňky určené číslem řádku a sloupce.

Syntaxe: \u003d. ADRESA(řetězec; sloupec; [typ typu]; [Style_links]; [list_name]), kde:

• Řetězec je povinným argumentem. Číslo představující číslo řádku, pro které je nutné vrátit adresu;
• Sloupec - povinný argument. Číslo představující číslo cílového buněčného sloupce.
• požadavky typu - volitelný argument. Číslo od 1 do 4, označující referenční indexy:
  • 1 - Výchozí hodnota, když jsou všechny indexy pevné;
  • 2 - Upevnění indexu linky;
  • 3 - Upevnění indexu sloupce;
  • 4 - Adresa bez upevnění.
• styl propojení - volitelný argument. Logická hodnota:
  • Pravda je formát odkazu "A1";
  • False je formát odkazu "R1C1".
• link_name je volitelný argument. Řetězec reprezentující název listu přidat do textu adresy buňky.

Příklad použití:

=ADRESA(1; 1) - vrátí $ 1 $ 1.
=ADRESA(1; 1; 4) - vrátí A1.
=ADRESA(1; 1; 4; false) - výsledek RC.
=ADRESA(1; 1; 4; False; »List1") - výsledek funkce seznamu1! RC.

Funkce DVSSL.

• adresa_Links - povinný argument. Řetězec reprezentující adresu odkazu na buňku nebo rozsah. Například "C3", "R3C3" nebo "D8: D9".
• styl propojení - volitelný argument. Logická hodnota Definování stylu odkazu:
  • Pravda - styl A1. Je výchozí hodnota;
  • Lež - styl r1c1.

Příklad použití:

=DVSSL.("A3") - Vrátí odkaz na buňku A3.
=DVSSL.("R3C3") - Vrátí chybu # LINK!, Od textu pro odkazy v formátu R1C1 a druhý argument je výchozí.
=DVSSL.("R3C3"; False) - Vrátí odkaz na C3 Cell.
=DVSSL.(Adresa (řetězec (C3); sloupec (C3))) - Funkce vrátí výsledek podobný příkladu.
Připojení funkce DVSSL s odkazem na rozsah:

Funkce CEMS

Vrátí počet řádků v určeném rozsahu nebo pole.

Příklad použití:
=Cems.(D1: D8) - Funkce vrátí výsledek 8.
=Cems.(1: 2: 3: 4: 5)) - Funkce určí, že v řadě 5 řádků.

Funkce nizhnyolb.

Vrátí počet sloupců v zadaném rozsahu nebo poli.
Syntaxe: \u003d. Numeric.(odkaz), kde "reference" je povinným argumentem, který je odkazem na buňku, rozsah nebo pole.
Příklad použití:
=Numeric.(A5: D5) - výsledek funkce 4.
=Numeric.((1; 2; 3; 4; 5)) - Funkce určí, že v řadě 5 sloupců.

Funkce posunutí funkce

• odkaz - povinný argument.
• displacement_p_strokes - povinný argument. Negativní buď pozitivní číslo označující, kolik řádků musí být posunuty.
• likvidace_ppost_stolbans - povinný argument. Negativní nebo pozitivní číslo označující, kolik sloupců musí být posunuty.
• výška je volitelný argument. Přirozené číslo (kromě nuly), který definuje počet řádků v novém rozsahu (offset).
• Šířka - volitelný argument. Přirozené číslo (kromě nula), které určuje počet sloupců v novém rozsahu.

Pokud v důsledku offsetu, rozsah začal jít do limitů listů, funkce vrátí chybu # odkaz!.

Příklad použití:

Například použijeme připevnění přílohy k funkčním součtům.
Zpočátku se obraťte na rozsah 10 řádků a 1 kolony, kde všechny buňky mají hodnotu 2, čímž se získá výsledek vzorce - 20.

Nyní uložte rozsah na jeden sloupec doleva, tj na -1.

Funkce vyhledávací desky

Vrátí polohu položky zadané hodnotou v rozsahu nebo poli.

Syntaxe: \u003d. Vyhledávací deska(SKEKSOSING_NAME; pole; [typ_denzace]), kde:

• požadovanou objednávku je povinným argumentem. Hodnota prvku, který se nachází v poli.
• Poloha je povinným argumentem. Jednorozměrný rozsah nebo pole pro vyhledávání položky.
• type_name je volitelný argument. Číslo 1, 0 nebo -1, který definuje způsob vyhledávání položky:
  • 1 - Výchozí hodnota. Pokud nejsou nalezeny shody, pak se vrátí poloha nejbližší menší hodnoty na požadovaný prvek. Pole nebo rozsah musí být rozdělen z menších na více nebo z A na Ya.
  • 0 - Funkce hledá přesnou náhodu. Pokud nebyl nalezen, je vrácena chyba # n / d.
  • -1 - Pokud nejsou nalezeny shody, pak se poloha nejbližší větší hodnoty vrátí do požadovaného prvku. Pole nebo rozsah musí být tříděno pomocí sestupně.

Příklad použití:
=Vyhledávací deska("G"; ("a"; "b"; "v"; "g"; "d")) - Funkce vrátí výsledek 4.

Funkce pro vyhledávání aplikace Excel

Zároveň se nezohledňuje registr.
=Vyhledávací deska("E"; ("A"; "B"; "v"; "G"; "D"); 1) - výsledek 5, protože Prvek není nalezen, takže se vrátí nejbližší menší prvek. Prvky pole jsou zaznamenány vzestupně.
=Vyhledávací deska("E"; ("a"; "b"; "v"; "g"; "d"); 0) - chyba je vrácena, protože Prvek není nalezen a typ porovnání je indikován na přesné náhody.
=Vyhledávací deska("B"; ("D"; "G"; "B"; "B"; "A"); -1) - výsledek 3.
=Vyhledávací deska("D"; ("A"; "B"; "B"; "G"; "D"; "D"); -1) - Prvek není nalezen, i když je přítomen v poli. Funkce vrátí nesprávný výsledek, protože poslední argument vezme hodnotu -1 a prvky nejsou uspořádány jako sestupně.

Pro textové hodnoty umožňuje funkce použití zástupných znaků "*" a "?"

Dobré odpoledne, drahý hobrovers!

Čas od času se některé (a možná více než některé) musejí vypořádat s úkoly zpracování malých datových polí, od přípravy a analýzy domovského rozpočtu a končící s jakýmikoli výpočty pro práci, studie atd. Snad nejvhodnější nástroj pro to je Microsoft Excel. (nebo možné jiné než jeho analogy, ale jsou méně časté).

Vyhledávání mi dalo jen jeden článek na Habré podle podobných tématech - "Talmud podle vzorců v tabulce Google". Dává dobrý popis základních věcí, které pracují v aplikaci Excel (i když to není 100% o aplikaci Excel).

Tak, akumulaci určitého fondu žádostí / úkolů, myšlenka na ně psát a navrhnout možné řešení (Nechte to možné, ale rychle dejte výsledek).

Bude to o řešení nejčastějších úkolů, kterým čelí uživatelé.

Popis řešení je založen následujícím způsobem - je uveden případ, který obsahuje zdrojový úkol, který se postupně stává komplikovaným, každý krok je dán podrobné řešení s vysvětlením. Jména funkcí budou uvedeny v ruštině, ale v závorkách na první zmínce budou původním názvem anglický jazyk (Vzhledem k tomu, že má zkušenost, převážná většina uživatelů má rusko-jazykovou verzi).

Case_1: logické funkce a náhodné funkce
"Mám sadu hodnot v desce a je nutné, aby při provádění konkrétního stavu / sady podmínek byla zobrazena určitá hodnota" (c) uživatel

Data jsou obvykle prezentována v tabulkové formě:

Stav:

• pokud je hodnota v sloupci "Množství" větší než 5,
• pak musíte stáhnout ve sloupci "Výsledek" hodnotu "objednávka není vyžadována",

To nám pomůže vzorec "jestliže" (pokud), který se týká logických vzorců a může vydat všechny hodnoty při řešení, že jsme zaznamenáni předem ve vzorci. Upozorňujeme, že všechny hodnoty textu jsou napsány pomocí nabídek.

Syntaxe vzorce je následující:
Pokud (Log_Production, [Value_Initina], [Value_if_nu]))

• Log_eximacy - výraz, který má za následek hodnotu pravdy nebo false.
• Value_initina - hodnota, která se zobrazí, pokud je logický výraz pravdivý
• Value_fire je hodnota, která se zobrazí, pokud je logický výraz false

Syntaxe vzorce pro řešení:

\u003d Pokud (C5\u003e 5; "objednávka není vyžadována"; "vyžaduje objednávku")

Na výstupu získáme výsledek:

Stává se, že stav je složitější, například provádění 2 nebo více podmínek:

• pokud je hodnota v sloupci "Množství" větší než 5, a hodnota ve sloupci typu se rovná "A"

V tomto případě se již nemůžeme omezit na používání jednoho vzorce "IF", je nutné přidat další vzorec k jeho syntaxi. A bude to další logický vzorec "a" (a).
Syntaxe vzorce je následující:
A (Logical_dation1, [Logical_dation2], ...)

• Logical_dation1-2 atd. - platný stav, jehož výpočet dává hodnotu pravdy nebo nepravdivé

Zobrazí výsledek v buňce D2:

\u003d Jestliže (a (a (a (c2\u003e 5; b2 \u003d "A"); 1; 0)

Tak, pomocí kombinace 2 vzorců nalezneme řešení našeho úkolu a získání výsledku:

Pokusme se komplikovat úkol - nový stav:

• pokud je hodnota v sloupci "Množství" 10 a hodnota v sloupci typu se rovná "A"
• nebo hodnota ve sloupci "Číslo" je větší nebo rovna 5 a hodnota "typ" je "B"
• musíte stáhnout ve sloupci "výsledek" hodnota "1", v reverzním případě "0".

Řešení syntaxe bude následující:
Zobrazí výsledek v buňce D2:

\u003d Jestliže (nebo ((a (a (a (a (c2 \u003d 10; b2 \u003d "A"); a (C2\u003e \u003d 5; B2 \u003d "B")); 1; 0)

Jak je vidět ze záznamu, ve vzorci ", pokud" jeden stav "nebo" (nebo) a dvě podmínky používající "a" vzorec jsou zahrnuty do ní, jsou zahrnuty. Pokud je alespoň jeden z podmínek druhé úrovně "pravda", zobrazí se výsledek "1" v sloupci "výsledku", jinak bude "0".
Výsledek:

Nyní se obrátíme na další situaci:
Představte si, že v závislosti na hodnotě v sloupci "podmínka" je třeba zobrazit určitý stav ve sloupci "výsledku", hodnoty a výsledky jsou dodržování.
Stav:

• 1 \u003d A.
• 2 \u003d B.
• 3 \u003d B.
• 4 \u003d G.

Při řešení úkolu pomocí funkce "IF" bude syntaxe následující:

\u003d If (A2 \u003d 1; "A";, pokud (A2 \u003d 2; "B"; pokud (A2 \u003d 3; "B"; pokud (A2 \u003d 4; "G"; 0)))

Výsledek:

Jak je vidět, psaní takového vzorce není nejen příliš pohodlný a těžkopádný, ale může nějakou dobu trvat na jeho editaci v nezkušeném uživateli v případě chyby.
Minus tohoto přístupu je, že je použitelný na malý počet podmínek, protože všechny budou muset být vytočeny ručně a "nafouknutí" naší vzorec na velké velikosti, ale přístup je odlišen úplným "všudypřítomným" hodnoty a všestrannost použití.

Alternativní řešení_1:
Pomocí vzorce "Choice" (vybrat),
Funkce syntaxe:
Výběr (číslo_intex, hodnota1, [Value2], ...)

• Číslo indexu je číslo zvolené hodnoty argumentu. Číslo indexu musí být číslo od 1 do 254, vzorec nebo odkaz na buňku obsahující číslo v rozsahu od 1 do 254.
• Value1, hodnota2, ... - hodnota od 1 do 254 argumentů hodnot, z nichž funkce "select" pomocí čísla indexu vybere hodnotu nebo provedenou akci. Argumenty mohou být čísly odkazovány na buňky, konkrétní názvy, vzorce, funkce nebo text.

Při použití, okamžitě zadáváme výsledky podmínek v závislosti na určených hodnotách.
Stav:

• 1 \u003d A.
• 2 \u003d B.
• 3 \u003d B.
• 4 \u003d G.

Syntaxe vzorce:

\u003d Volba (A2; "A"; "B"; "B"; "G")

Výsledek je podobný roztoku s řetězcem funkcí "IF" výše ".
Při použití tohoto vzorce existují následující omezení:
Pouze čísla mohou být indikována v buňce A2 (indexové číslo) a hodnoty výsledků budou výstupy ve vzestupném pořadí od 1 do 254 hodnot.
Jinými slovy, funkce bude fungovat pouze v případě, že v buňce "A2" jsou indikována od 1 do 254 ve vzestupném pořadí a při použití tohoto vzorce ukládá určitá omezení.
Ty. Pokud chceme být hodnota "G" byla zobrazena při určování čísla 5,

• 1 \u003d A.
• 2 \u003d B.
• 3 \u003d B.
• 5 \u003d G.

Tento vzorec bude mít následující syntaxi:
Zobrazuje výsledek v buňce B2:

\u003d Volba (A31; "A"; "B"; "B" ;; "g")

Jak je vidět, hodnota "4" ve vzorci musíme opustit prázdný a přenášet výsledek "g" na číslo sekvence "5".

Alternativní řešení_2:
Takže jsme se přiblížili jedné z nejoblíbenějších funkcí aplikace Excel, jehož zvládnutí automaticky otočí veškerý úředník v aplikaci Excel "Excel zkušený" / sarkasmus /.
Syntaxe vzorce:
PRD (žádoucí_dation, tabulka, číslo_libes, [interval_view])

• Požadovaný je hodnota, hledání, které je prováděno funkcí.
• Tabulka - rozsah buněk obsahujících data. Je v těchto buňkách, že se vyhledávání dojde. Hodnoty mohou být text, číselné nebo logické.
• Číslo skladby je číslo sloupce v argumentu "tabulka", ze kterého bude hodnota zobrazena v případě náhody. Je důležité pochopit, že odpočítávání sloupců se nevyskytuje na celkovém listu plechu (A.B, C, D atd.), A uvnitř pole uvedené v argumentu "tabulky".
• Interval_prising - určuje, která shoda okolností by měla najít funkci - přesné nebo přibližné.

Důležité: Funkce "PRD" hledá náhodu pouze na prvním jedinečném vstupu, pokud je požadovaný_dace přítomen v argumentu "tabulka" a má různé hodnoty, pak funkce "VDP" najde pouze první náhodu, Výsledky pro všechny ostatní náhody nejsou k dispozici pro vzorec UPR. (Vlookup) je spojen s jiným přístupem při práci s daty, a to s tvorbou "referenčních knih".
Podstatou přístupu při vytváření "referenční knihy" shody argumentu "požadovaný_name" určitého výsledku, odděleně od hlavního pole, ve kterém jsou předepsány podmínky a odpovídající hodnoty:

Poté je vzorec již předepsán v pracovní části tabulky s odkazem na adresář vyplněný dříve. Ty. V adresáři ve sloupci "D", hodnota hodnoty ze sloupce "A" a při vyhovování, je odvozena ze sloupce "E" v sloupci "B".
Syntaxe vzorce:
Zobrazuje výsledek v buňce B2:

Výsledek:

Nyní si představte situaci, kdy potřebujete utáhnout data do jedné tabulky z druhé, zatímco tabulky nejsou identické. Viz příklad níže

Je vidět, že řádky ve sloupcích "produktu" obou tabulek se neshodují, ale to není překážka pro použití funkce "VDP".
Zobrazuje výsledek v buňce B2:

Ale při řešení, čelíme novým problémům - s "protahováním" vzorec, který jsme napsali do našeho práva od sloupce "v" do sloupce "E", budeme muset ručně nahradit argument "number_stolbet". Případ je časově náročný a nevděčný, protože jde o pomoc jinou funkci - "sloupec" (sloupec).
Funkce syntaxe:
Sloupec ([LINK])

• Link - buňka nebo rozsah buněk, pro které chcete vrátit číslo sloupce.

Pokud používáte typ typu:

Funkce zobrazí počet aktuálního sloupce (ve kterém vzorec je napsán v buňce).
V důsledku toho ukazuje číslo, které lze použít v funkci "PRD", než používáme a získáme následující registraci vzorce:
Zobrazuje výsledek v buňce B2:

\u003d VD ($ A3; $ H $ 3: $ M $ 6; sloupec (); 0)

Funkce "Sloupec" určí počet aktuálního sloupce, který bude použit argumentem "Lent-Drive Number", který určí číslo sloupce vyhledávání v adresáři.
Kromě toho můžete použít design:

Namísto čísla "1" můžete použít libovolné číslo (stejně jako nejen to odečíst, ale také přidat do výsledné hodnoty), abyste získali požadovaný výsledek, pokud není žádná touha odkazovat na konkrétní buňku Ve sloupci s číslem potřebujeme.
Výsledný výsledek:

Pokračujeme ve vývoji tématu a komplikovat podmínku: Představujeme si, že máme dvě referenční knihy s různými daty dat a je nutné zobrazit hodnotu v tabulce v závislosti na tom, jaký typ referenčního knihy je uveden v sloupci "adresáře"
Stav:

• Pokud je číslo 1 zadáno v sloupci "Příručka", data by měla být čerpána z tabulky "Directory_1", pokud číslo 2, pak z tabulky "Directory_2" v souladu se zadaným měsícem

Možnost řešení, která okamžitě přijde na mysl, následující:

\u003d If ($ B3 \u003d 1; VD ($ A3; $ G $ 3: $ I $ 6; sloupec () - 1; 0); VD ($ A3; $ K $ 3: $ M $ 6; sloupec () - 1; 0))

profesionálové: Název referenční knihy může být libovolný (text, čísla a jejich kombinace), minusy - špatně vhodné, pokud jsou možnosti více než 3.
Pokud čísla adresářů vždy představují čísla, má smysl použít následující řešení:
Zobrazovací výstup v buňce C3:

\u003d VD ($ A3; volba ($ B3; $ G $ 3: $ I $ 6; $ K $ 3: $ M $ 6); sloupec () - 1; 0)

profesionálové: Vzorec může zahrnovat až 254 názvů referenčních knih, minusy - jejich jméno musí být přísně numerické.
Výsledek pro vzorec pomocí funkce "Výběr":

Bonus: UPR na dvou nebo více známkách v argumentu "požadované_station".
Stav:

• Představte si, že my, jako vždy, mít řadu dat v tabulkové formě (pokud ne, pak poskytujeme data), z pole pro určité funkce je nutné získat hodnoty a umístit je do jiné tabulkové formy.

Oba tabulky jsou uvedeny níže:

Jak je vidět z tabulek formulářů, každá pozice má nejen název (který není jedinečný), ale také odkazuje na konkrétní třídu a má vlastní možnost balení.
Pomocí kombinace jména a třídy a balení můžeme vytvořit novou funkci, v tabulce s daty vytvoříme další sloupec "Další prognónu", který vyplní následující vzorec:

Použití symbolu "&", kombinujeme tři známky do jednoho (oddělovač mezi slovy může být jakýkoliv, tak, aby nebylo úplně, hlavní věc je použít podobné pravidlo a pro vyhledávání)
Analogu vzorce může být funkce "mince" (zřetězení), v tomto případě bude vypadat takto:

\u003d Úlovek (h3; "_"; i3; "_"; j3)

Po vytvoření další funkce pro každou položku v tabulce s daty přejděte k zápisu funkce vyhledávání na této funkci, která bude vypadat:
Zobrazuje výsledek v buňce D3:

\u003d Pokud nástroj (APP (APP (A2 & "_" & B2 & "_" $ G $ 2: $ k $ 6; 5; 0); 0)

Ve funkci "VDP" jako argument "požadovaná_station" používáme všechny stejné vazby ze tří značek (název_class_faska), ale vezmeme ji již v tabulce pro vyplnění a zadávání přímo do argumentu (jako volba, to je Bylo by možné zvýraznit hodnotu argumentu na další sloupec v tabulce pro vyplnění, ale tato akce bude zbytečná).
Připomínám vám, že pomocí funkce "Pokud je užitečnost" (IfError), je nutné, pokud není požadovaná hodnota nalezena, a funkce "PRD" přinese hodnotu "# h / d" (o níže níže).
Výsledek je na obrázku níže:

Tato technika může být použita pro více Znamení, jediný stav je jedinečnost získaných kombinací, pokud není respektována, výsledek bude nesprávný.

Keys_3 Vyhledávání hodnoty v poli nebo když nám není schopna pomoci

Zvažte situaci, kdy je nutné pochopit, zda v řadě buněk potřebujeme hodnoty.
Úkol:

• v sloupci "Hledat stav" je hodnota indikována a je nutná určit, zda je přítomen v sloupci "Search Array"

Vizuálně se vše dívá v následujícím formuláři:

Jak vidíme, funkce "prd" zde je bezmocná, protože Hledáme přesnou náhodu, konkrétně přítomnost hodnot v buňce.
Pro vyřešení problému je nutné použít kombinaci několika funkcí, a to:
"POKUD"
"Je-li utility"
"Promno"
"NAJÍT"

V pořádku "Jestliže" jsme již dříve demontovali dříve, proto budeme pokračovat do funkce "Je-li internet" (IfError)

Pokud je chyba (hodnota, hodnota_no_r.)

• Hodnota je argumentem zaškrtnuté pro chyby.
• Value_No_name - hodnota vrácená v případě výpočtu vzorce. Jsou možné následující typy chyb: # n / d, # znamená!, # LINK!, # Del / 0!, # Číslo!, # Jméno? A # je prázdný!.

DŮLEŽITÉ: Tento vzorec je téměř vždy povinný při práci s informačními poli a referenčními knihami, protože Často se stává, že požadovaná hodnota není v adresáři a v tomto případě funkce vrátí chybu. Pokud se v buňce zobrazí chyba a buňka, například v výpočtu se také stane s chybou. Plus všem, buňky, kde vzorec vrátil chybu, můžete přiřadit různé hodnoty, které usnadňují jejich statistické zpracování. Také v případě chyby lze provést další funkce, což je velmi pohodlné při práci s maticemi a umožňuje nám stavět vzorce s přihlédnutím do spíše rozvětvených podmínek.

"Dolní" (nižší)

• Text - text kabriolet na nižší registr.

Důležité: Funkce "Stitch" nenahrazuje značky, které nejsou písmena.
Úloha ve vzorci: Vzhledem k tomu, že funkce "Najít" (Najít) vyhledává text textu, pak je nutné přivést veškerý text do jednoho registru, jinak "čaj" nebude "čaj", atd. To je relevantní, pokud hodnota registru není podmínkou pro vyhledávání a výběr hodnot, jinak nelze použít vzorec "Strachkin", takže vyhledávání bude přesnější.

Nyní více o funkci syntaxe "Najít" (Najít).

Najít (požadovaný_text, zobrazení_text, [nach_position])

• Požadovaný_text je text, který chcete najít.
• Hledáte přes through_text - text, ve kterém potřebujete najít požadovaný text.
• Nach_position - znaménko, ze kterého chcete spustit vyhledávání. První znak v textu "Watching_Text" má číslo 1. Pokud není číslo zadáno, považuje se za rovný 1.

Syntaxe řešení vzorce se bude podívat na:
Zobrazuje výsledek v buňce B2:

\u003d Jestliže (pokud je nástroj (vyhledání (nálet (nálep); Strochene (E2); 1); 0) \u003d 0; "FAIL"; "Bingo!")

Budeme analyzovat logiku vzorců podle akce:

1. Stricken (A2) - Převede argument "požadovaný_text" v buňce v A2 na text s nižším rejstříkem
2. Funkce "Najít" začíná vyhledávat převedený argument "požadovaný_text" v poli "Listing_text", který je převeden funkcí "Strach" (E2), také do textu s nižším registrem.
3. V případě, funkce najde náhodu, tj. Vrátí pořadové číslo prvního symbolu odpovídajícího slova / hodnoty, stav pravdy v "pokud" vzorec je spuštěn. Výsledná hodnota není nula. V důsledku toho se hodnota "Bingo!" Zobrazí v sloupci "Výsledek".
4. Pokud funkce nenajde náhodu I. Sekvenční číslo prvního symbolu odpovídajícího slova / hodnoty není zadáno a chyba je vrácena namísto hodnoty, podmínka je spuštěna ve vzorci ", pokud nástroj" a vrátí hodnotu rovnou "0", která odpovídá hodnotě "0" lež ve vzorci "jestliže", protože Získaná hodnota je "0". V důsledku toho se v sloupci "Výsledek" zobrazí hodnota "Fail".

Jak je vidět z obrázku výše, díky funkcím "Strach" a "Najít" najdeme požadované hodnoty bez ohledu na registraci symbolu a umístění v buňce, ale musíte věnovat pozornost řetězci 5.
Stav vyhledávání je nastaven jako "111", ale ve vyhledávacím poli je indikováno hodnotou "1111111 cookie", nicméně, vzorec vydává výsledek "bingo!". Je to proto, že hodnota "111" je zahrnuta v počtu hodnot "11111111", v důsledku toho je shoda okolností. V opačném případě nebude tato podmínka fungovat.

Case_4 hledat hodnotu v poli v několika podmínkách, nebo když nám není schopna pomoci nám

Představte si situaci, kdy je nutné najít hodnotu z tabulky "s výsledkem" v dvourozměrném poli "adresář" v několika podmínkách, a to hodnotu "název" a "měsíc".
Formulář tabulky bude následující:

Stav:

• V tabulce s výsledkem musíte data utáhnout v souladu s náhodou "názvu" a "Měsíc" podmínek.

Chcete-li takový úkol vyřešit, bude vhodná kombinace funkcí "index" a "vyhledávací firmy"

Funkce syntaxe "Index" (index)

Index (Array, Number_stroke, [number_stolbits])

• Pole je rozsah buněk, ze kterých budou hodnoty zobrazeny v případě náhody jejich hledání.
• Pokud pole obsahuje pouze jeden řetězec nebo jeden sloupec, argument "number_stroke" nebo "number_stolbet", respektive, není povinný.
• Pokud pole trvá více než jeden řádek a jeden sloupec, a pouze jeden je nastaven z argumentů "number_stroke", pak funkce "Index" vrátí pole sestávající z celého řádku nebo celý sloupec argumentu "Array".
• Číslo_stroke - číslo řádku v poli, ze kterého chcete hodnotu vrátit.
• Číslo skladby je číslo sloupce v poli, ze kterého chcete vrátit hodnotu.

Jinými slovy, funkce se vrátí ze zadaného pole v argumentu "Array", která je na křižovatce souřadnic zadaných v argumentech "number_stroke" a "number_stolbet".

Funkce syntaxe "Vyhledávací společnost" (zápas)

Hledat společnost (Search_Name, zobrazení by_massive, [typ softwaru]))

• Nejdůležitější_station je hodnota, která je uzavřena s hodnotami v argumentu vnímané by_massive. Argument požadované_dace může být hodnota (číslo, text nebo logická hodnota) nebo odkaz na buňku obsahující takovou hodnotu.
• Visible_massive - rozsah buněk, ve kterých je vyhledávání provedeno.
• Type_name je volitelný argument. Číslo -1, 0 nebo 1.

Funkce vyhledávací služby vyhledává zadaný prvek v rozsahu buněk a vrátí relativní polohu tohoto prvku v pásmu.
Podstatou použití kombinace funkcí "index" a "vyhledávače" je to, že vyrábíme souřadnice hodnot pro jejich název podle "souřadnicových os".
Osa Y bude sloupec "název" a osa X je řádek "měsíce".

Část vzorce:

Vyhledávací společnost ($ A4; $ I $ 4: $ I $ 7; 0)

Vrátí číslo podél osy Y, v tomto případě bude roven 1, protože Hodnota "A" je přítomna v hledaném rozsahu a má relativní polohu "1" v tomto rozsahu.
Část vzorce:

Vyhledávací společnost (B $ 3; $ J $ 3: $ L $ 3; 0)

Vrátí hodnotu # n / d, protože V zobrazení pásma chybí hodnota "1".

Získali jsme tak souřadnice bodu (1; # n / d), které funkce "index" používá "pole" pro vyhledávání v argumentu.
Plně písemná funkce pro buňku B4 bude mít následující formulář:

\u003d Index ($ j $ 4: $ l $ 7; vyhledávací deska ($ a4; $ i $ 4: $ i $ 7; 0); vyhledávací deska (B-$ 3; $ j $ 3 $ 3: $ l $ 3; 0 ))

Ve skutečnosti, kdybychom věděli souřadnice hodnot, které potřebujeme, funkce by vypadala takto:

\u003d Index ($ j $ 4: $ l $ 7; 1; # n / d))

Vzhledem k tomu, že argument "number_stolb" je "# h / d", výsledek pro buňku B4 bude vhodný.
Jak je vidět z výsledného výsledku, ne všechny hodnoty v tabulce se shodují s referenčním knihou a v důsledku toho vidíme, že část hodnot v tabulce se zobrazí jako "# h / d", která Je obtížné použít data pro další výpočty.
Výsledek:

Chcete-li tento negativní efekt neutralizovat, použijte funkci "IF Utility", kterou jsme četli dříve, a nahradit hodnotu, která se vrátí během chyby na "0", pak se vzorec bude podívat na:

\u003d Pokud UtyLum (index ($ j $ 4: $ l $ 7; vyhledávací deska ($ A4; $ I $ 4: $ i $ 7; 0); vyhledávací deska (B-$ 3; $ J $ 3: $ L $ 3; 0)); 0)

Demonstrace výsledku:

Jak je vidět na obrázku, hodnoty "# h / d" neinterferují s námi v následných výpočtech pomocí hodnot v tabulce s výsledkem.

Case_5 Hodnoty vyhledávání v rozsahu čísel

Představte si, že musíme určit určitou funkci čísel obsažených v konkrétním rozsahu.
Stav:
V závislosti na ceně výrobku by mělo být přiděleno určitou kategorii
Pokud je hodnota v rozsahu

• Od 0 do 1000 \u003d a
• Od 1001 do 1500 \u003d b
• Od 1501 do roku 2000 \u003d v
• Od roku 2001 do 2500 \u003d g
• Více než 2501 \u003d D

Zobrazení funkce (vyhledávání) vrátí hodnotu z řádku, sloupce nebo pole. Funkce má dvě syntaktické tvary: vektor a forma pole.

Zobrazit (požadavek_station; viewed_tector; [vector_teults])

• Požadovaná objednávka je hodnota, kterou zobrazí funkce zobrazení v prvním vektoru. Požadovaná objednávka může být číslo, text, logická hodnota, název nebo odkaz na hodnotu.
• Visible_extector - rozsah sestávající z jednoho řádku nebo jednoho sloupce. Hodnoty v názvu argumentu_tector může být text, čísla nebo logické hodnoty.
• Hodnoty v argumentu viewed_tector by měly být uspořádány ve vzestupném pořadí: ..., -2, -1, 0, 1, 2, ..., A-Z, lež, pravda; V opačném případě může funkce zobrazení vrátit nesprávný výsledek. Text v dolních a horních registrech je považován za ekvivalentní.
• Vector_teults - rozsah sestávající z jednoho řádku nebo sloupce. Vector_teults by měl mít stejnou velikost jako viewed_tector.

\u003d Zobrazit (E3; $ A $ 3: $ A $ 7; $ B $ 3: $ B $ 7)

Argumenty "Watching_tector" a "Vector_TEULT" mohou být zaznamenány ve formě pole - v tomto případě je nemusíte stáhnout do samostatné tabulky na listu Excel.
V tomto případě bude funkce vypadat takto:
Zobrazuje výsledek v buňce B3:
\u003d Pohled (E3; (0; 1001; 1501; 2001; 2501; ("A"; "B"; "B"; "G"; "D"))

Case_6 Summace čísel na funkce

Chcete-li shrnout čísla, mohou být pro určité funkce použity tři různé funkce:
SUMIF - shrnuje pouze jeden znak
Sumilimn (SUMIFS) - shrnuje mnoho značek
SumProduct - shrnuje soubor značek
K dispozici je také možnost používající "součet" (součet) a funkce vzorce polí, když je vzorec "součty" zabudována do pole:
((\u003d Částky (() * ())))
Ale tento přístup je poměrně nepohodlný a zcela překrývá na funkčnosti vzorce "Sumpot"
Nyní více na Syntaxe "Summip -":

Summion (Array1, [Array2], [Array3], ...)

• Array1 je první pole, jehož součásti musí násobit a pak složit výsledky.
• Array2, Array 3 ... - od 2 do 255 polí, jejichž součásti musí znásobit a poté složit výsledky.

Stav:

• Najděte celkovou částku při nákladech na dodávky pro každou z produktů na určité období:

Jak je vidět z tabulky s údaji pro výpočet nákladů, musíte vynásobat cenu podle množství a hodnota získaná použitím cen výběru k přenosu do tabulky s výsledkem.
Formula "Sumpariz" však umožňuje takové výpočty uvnitř vzorce.
Zobrazení výsledku v buňce B4:

\u003d SHUMPAZACA (($ A4 \u003d $ H $ 3: $ H $ 11) * ($ K $ 3: $ K $ 11\u003e \u003d B $ 3) * ($ K $ $ 3: $ K $ $ 11
Budeme analyzovat vzorec v částech:
- Nastavte podmínku pro výběr v sloupci "Název" tabulky s daty na sloupci "Název" v tabulce s výsledkem

($ K $ 3: $ K $ 11\u003e \u003d B $ 3) * ($ K $ 3: $ K $ 11 - Zeptáme se podmínku podle časového rámce, datum je větší nebo rovno prvnímu počtu aktuálního měsíce, ale méně než první číslo měsíce následujícího. Stejně tak stav v tabulce s výsledkem, pole - v tabulce s daty.
- Snižte sloupce "Číslo" a "Cena" v tabulce s daty.
Nepochopený plus této funkce je volný postup pro rekordní podmínky, mohou být zaznamenány v libovolném pořadí, to nebude mít vliv na výsledek.
Výsledek:

Nyní komplikují stav a přidat požadavek, aby výběr podle názvu "cookies" došlo pouze ve třídách "Malé" a "Big", a na jméno "mísy", s výjimkou třídy "s JAM":

Zobrazení výsledku v buňce B4:

\u003d Sumpacy (($ A4 \u003d $ H $ 3: $ H $ 11) * ($ J $ 3: $ J $ 11\u003e \u003d B $ 3) * ($ J $ 3: $ J $ 3: $ J $ 11
Vzorec pro výběr pro soubory cookie Přidá nový stav:

(($ I $ 3: $ i $ 11 \u003d "malý") + ($ 1 £ 3: $ i $ 11 \u003d "velký")

- Jak lze vidět, dvě nebo více podmínek na jednom sloupci jsou přiděleny do samostatné skupiny pomocí symbolu "+" a uzavřením podmínek do dalších závorek.
Formule pro výběr nohou byl také přidán nový stav:

\u003d Sumpapers (($ A5 \u003d $ H $ 3: $ H $ 11) * ($ J $ 3: $ J $ 11\u003e \u003d B $ 3) * ($ J $ 3: $ J $ 3: $ J $ 11 "S JAMEM"); ($ L $ 3: $ L $ 11) * ($ K $ 3: $ K $ 11))

tohle je:

($ I $ 3: $ i $ 11<>"S JAMEM")

- Ve skutečnosti, v tomto vzorci bylo možné napsat stav výběru, stejně jako při výběru na cookies, ale pak by muselo uvést tři podmínky ve vzorci, v tomto případě je snazší napsat výjimku - ne přesně " S JAMEM "pro to používáme hodnotu"<>».
Obecně platí, že pokud jsou známky / třídy známy předem, je lepší je kombinovat je do těchto skupin, vytváření referenčních knih než zaznamenávat všechny podmínky ve funkci, foukání.
Výsledek:

Takže jsme přišli na konec našeho stručného manuálu, který by mohl být ve skutečnosti mnohem více, ale cílem bylo ještě učinit rozhodnutí nejvíce setkávaných situací a nepopsat rozhodnutí soukromého (ale mnohem zajímavějších případů) .
Doufám, že manuál pomůže někomu při řešení úkolů s Excelem, protože to bude znamenat, že moje práce nezískala marně!

Děkuji vám za Váš čas!

Mezi tisíc řádků a desítky datových sloupců ručně v tabulce Excel najít něco téměř nemožného. Jedinou možností je použít nějaký druh vyhledávací funkce, a pak se podíváme na to, jak se vyhledávání provádí v tabulce Excel.

Chcete-li vyhledat data v tabulce Excel, musíte použít položku nabídky "Najít a zvýraznit" Na kartě "Hlavní"Ve kterém potřebujete vybrat možnost "Najít" Nebo použít k volání kombinace klíčů "Ctrl + F".

Snažte se například najít požadované číslo mezi údaji naší tabulky, protože je při hledání čísel, je nutné zvážit některé vyhledávání subtleties. Budeme hledat v tabulce aplikace Excel "10".

Po výběru požadované položky menu do pole, které se zobrazí, zadáme požadovanou hodnotu. Máme dvě možnosti pro nalezení hodnot v tabulce Excel, je najít všechny shody najednou kliknutím na tlačítko. "Najít vše" Nebo okamžitě procházet každou nalezenou buňku, stisknutí tlačítka "Najděte dále". Při použití tlačítka "Najděte dále" Je třeba také zohlednit aktuální umístění aktivní buňky, protože vyhledávání začne s touto pozicí.

Pokusme se najít všechny hodnoty najednou, se všemi zjištěnými bude uvedeno v okně pod nastavením vyhledávání. Pokud necháte všechny výchozí nastavení, výsledek vyhledávání nebude přesně tak, jak jsme očekávali.

Chcete-li správně vyhledat data v tabulce Excel, stiskněte tlačítko "Parametry" A nastavte vyhledávací oblast. Nyní je požadovaná hodnota navržena i ve vzorcích používaných v buňkách pro výpočty. Musíme zadat hledání pouze v hodnotách a pokud chcete, můžete stále určit formát požadovaných dat.

Při hledání slov v tabulce aplikace Excel byste měli také vzít v úvahu všechny tyto bubívy a například můžete dokonce zvážit případ písmen.

No, nakonec zvážit, jak vyhledat data v Excelu pouze v požadované oblasti listu. Jak je vidět z našeho příkladu požadovanou hodnotu "10" Vyskytuje se ihned ve všech datových sloupcích. Pokud je nutné najít tuto hodnotu, povolte pouze v prvním sloupci, musíte vybrat tento sloupec nebo libovolnou oblast hodnot, ve kterých chcete vyhledat, a potom spustit vyhledávání.