Ako v exterele hľadať číslo. Rozhodovanie Vyhľadávanie v programe Excel - príklad používania služby Hľadať riešenia v programe Excel

Komplexný proces môže byť optimalizácia hodnôt tabuľky programu Excel, ktoré spĺňajú určité kritériá. Našťastie Microsoft ponúka doplnok Riešenie problémovpre numerickú optimalizáciu. Hoci táto služba Nemôžem vyriešiť všetky problémy, môže byť užitočné ako nástroj čo ak.Tento príspevok je určený na doplnok Riešenie problémovv programe Excel.

Nadstavba Riešenie problémovk dispozícii vo všetkých verziách programu Excel. Upozorňujeme, že screenshoty nemusia zodpovedať vašej verzii. Napriek tomu, že niektoré funkcie môžu zmeniť svoju polohu v závislosti od verzie nadstavby, funkčnosť zostáva takmer nezmenená.

Aké je vyhľadávanie riešení

Hľadanie rozhodnutí - Excel doplnok, ktorý pomáha nájsť riešenie zmenou hodnôt cieľových buniek. Cieľ môže minimalizovať, maximalizovať alebo dosiahnuť určitú cieľovú hodnotu. Problém je vyriešený úpravou vstupných kritérií alebo obmedzení definovaných užívateľom.

Kde v Excel Hľadať riešenia

Nadstavba Hľadanie rozhodnutídodáva sa s Excel, ale predvolené je zakázané. Ak chcete povoliť, prejdite na kartu. Spisskupina Parametre.V dialógovom okne, ktoré sa zobrazí dialógové okno Parametre,vyberať Add-in -\u003e Control: Add-InExcel -\u003e Go.V okne Nadstavbanainštalujte kliešte oproti poľa Riešenie roztokuzhmem. Ok.

Teraz na karte Dátaobjavil sa nová skupina Analýzas tlačidlom Vyhľadávacie riešenia.

Príkladom vyhľadávania riešení

Tento príspevok je založený na príkladu použitia. . Súbor je kompatibilný so všetkými verziami programu Excel.

Definovanie problému

Predpokladajme, že máme súbor údajov pozostávajúci z 8 bodov, z ktorých každý zodpovedá jeho hodnote.

... a musíme kombinovať hodnoty do dvoch skupín, aby sa sumy hodnôt týchto skupín približne zhodovali.

Najprv chcete definovať každú položku nejakej skupine.

Ak chcete uviesť prílohu odseku do skupiny, budeme oženiť s nimi (1), inak nula (0).

V nasledujúcom stĺpci budeme zhrnúť hodnoty každej položky v skupine a potom zhrnúť na konci stĺpca.

Musíme tiež zvládnuť hodnotu každej položky v každej skupine, pre to vynásobíme hodnotu položky na hodnotu skupiny zodpovedajúcej tejto položke.

Nakoniec musíme znížiť množstvo skupín a pracovať s rozdielom medzi nimi.

Naša úloha minimalizuje rozdiel medzi sumami skupín.

Teraz môžeme priradiť každej skupinovej položke, pre to manuálne dajte jednotky v stĺpcoch C a D. Excel zobrazí rozdiel v sumách skupín v bunke G11.

Problém je, že počet možných kombinácií 2 8, t.j. 256 Pravdepodobné odpovede na otázku. Ak strávite 5 sekúnd pre každého z nich, bude to trvať od 21,3 minút, za predpokladu, že môžeme odolať tempom a zapamätať si najlepšiu kombináciu.

To je kde Hľadať riešenianájdite aplikáciu.

Vyhľadajte optimálne riešenie v programe Excel

Aplikovať službu Riešenie roztoku Musíme identifikovať niekoľko požiadaviek, pravidiel a obmedzení, ktoré umožnia nadstavbu nájsť správnu odpoveď.

Naše pravidlá

Našou základnou požiadavkou je minimalizovať rozdiel medzi dvoma skupinami. V našom príklade sa nachádza v skupine G11 Cell - Group B Mines A. Potrebujeme hodnotu v bunke G11 čo najviac, ale viac ako alebo rovné 0.

Vieme tiež, že položka môže byť buď v skupine A, alebo v skupine B, nemôže byť frakcionálne. Preto máme dva obmedzenia pre každý prvok:

Najprv: hodnota prvku v stĺpci Výsledokmusí byť rovná jednej.

Po druhé: Hodnoty prvkov v skupinách musia byť celé číslo.

Tiež vieme, že celkový počet prvkov 8 je ďalším obmedzením. Ako používať tieto obmedzenia budeme diskutovať v nasledujúcej časti.

Dialógové okno Solution Search

Táto časť popisuje doplnkové okno. Hľadať riešeniaa jeho použitie na určenie problému.

Prázdne riešenie hovoria okno

Vyplnené okno vyhľadávania riešení

Optimalizujte cieľovú funkciu

Toto je cieľová bunka, v ktorej sa snažíme problém vyriešiť. Naša cieľová bunka G11 je rozdiel v skupinách.

Predtým

Uvádzame, aké výsledky chceme dosiahnuť z cieľovej funkcie.

Chceme, aby sa sumy oboch skupín zhodovali, t.j. Aby bolo množstvo sumy, ktoré majú byť 0. Môže sa zdať zvláštne, ale nemusíme minimalizovať rozdiel, pretože všetky prvky budú umiestnené v skupine A, čo povedie k hodnote g11 bunky.

Ďalším spôsobom, ako uložiť obmedzenia, je zmena G11 \u003d Abs (G10-F10).Zároveň môžeme nainštalovať značku Minimum,v dôsledku dosiahnutia cieľovej funkcie.

Ale teraz sa zameriame na vzorec \u003d G10-F10a nastavte značku na hodnotu rovnú 0.

Zmena buniek premenných

Premeniteľné bunky - bunky, ktoré nadstavba sa pokúsi zmeniť na vyriešenie úlohy. V našom prípade ide o väzbový prvok pre konkrétnu skupinu: $ C $ 2: $D $ 9.

V súlade s obmedzeniami

Obmedzenia sú pravidlá, ktoré obmedzujú možné riešenia Problémy.

Musíme pridať niekoľko obmedzení na našom zozname:

V Coll CELKOMkaždý prvok by mal byť rovný 1
Skupinové prvky musia byť celé číslo
Súčet hodnôt stĺpcov CELKOMmusí byť 8.

Ak chcete upevniť obmedzenia, kliknite na tlačidlo Pridať

Môžeš Zmenaalebo Vymazaťobmedzenie, ak ste vykonali chybu výberom špecifického obmedzenia a stlačením príslušných tlačidiel v dialógovom okne.

Stiahnite si / uložiť možnosti vyhľadávania riešení

Solutions Search Service vám umožňuje uložiť a nahrať nadstavby. Ak to chcete urobiť, v okne je tlačidlo Stiahnuť / Uložiť.Parametre sa ukladajú do rozsahu, ktorý ste predtým označili. Tento prístup vám umožňuje rýchlo konfigurovať a zmeniť parametre. Nájsť riešenie.

Spustiť vyhľadávanie optimálneho riešenia v programe Excel

Varovanie!!! Add-in Solution Search je komplexná výpočtová nadstavba, takže pred spustením, uložte zošit.

Pred spustením modelu musíte nastaviť niekoľko ďalších parametrov, aby ste sa uistili, že služba bude fungovať správne. V hlavnom dialógovom okne sa uistite, že marker je oproti poľa Aby sa premenné bez obmedzenia negatívne.V tom istom okne Parametre.

Z času na čas sa budú musieť zmeniť dva parametre:

Obmedzenia presnosti:hodnota od 0 do 1, kde je väčšia hodnota, tým väčšie je obmedzenie

Optimalita:ukazuje, ako ďaleko od celočíselné obmedzenie má právo byť.

Spustený model

Ak chcete spustiť kliknutím na tlačidlo Nájsť riešeniezákladné okno.

V stavovom riadku uvidíte niekoľko statických údajov, ktoré zobrazia vnútornú prácu nadstavby. Spravidla sa rýchlo menia a je ťažké ich prečítať. Ak je model komplikovaný, potom sa práca môže na chvíľu zastaviť, doplnok sa zvyčajne obnovuje z týchto problémov.

Po Hľadať riešeniadokončí svoju prácu, Excel zobrazí dialógové okno Výsledky vyhľadávania výsledkov S niektorými informáciami. Prvá vec, ktorú treba venovať pozornosti, je nápis Riešenie sa nachádza v prípustnej odchýlke.Ak sa riešenie nájde, bunky pracovnej knižnice sa zmenia s navrhovaným rozhodnutím.

Teraz máte 4 možnosti na výber:

- Obnovte zdrojové hodnoty

Spustiť správu

Správa môžete vytvoriť výberom prehľadov dostupných zo zoznamu. Vytvorí sa nový list. Správa o výsledkoch1.

Upozorňujeme, že v závislosti od nainštalovaných obmedzení budú k dispozícii rôzne správy.

Uložiť skript

Kde chcete zadať názov modelového skriptu a kliknite na tlačidlo Ok.

Všetky scenáre sú k dispozícii v Skriptový dispečerktorý je na karte Dátav skupine Práca s údajmi -\u003e Analýza, čo-If -\u003e Script Manager.

Predpokladajme, že vaša správa obsahuje tabuľku s veľkým množstvom údajov o množstve stĺpcov. Vizuálna analýza takýchto tabuliek je mimoriadne náročná. A jednou z úloh na prácu s prehľadom je analyzovať údaje o hlavičkách riadkov a stĺpcov týkajúcich sa určitého mesiaca. Na prvý pohľad je to veľmi jednoduchá úloha, ale nemôže byť vyriešená pomocou jednej štandardnej funkcie. Áno, samozrejme, môžete použiť nástroj: "HOME" - "Úprava" - "Nájsť" Ctrl + F na vyvolanie vyhľadávacieho poľa pre hodnoty strana v Exceli. Alebo vytvorte podmienené formátovanie stola. Ale potom to nebude ďalej vypočítať s získanými výsledkami. Preto je potrebné vytvoriť a správne aplikovať zodpovedajúci vzorec.

Hodnoty vyhľadávania v programe Excel

Týmto spôsobom vyzerá schéma riešenia úlohy:

v bunke B1 zadáme údaje, ktoré máte záujem;
v bunke B2 sa zobrazí hlavička stĺpca, ktorá obsahuje hodnotu bunky B1
v bunke B3 sa zobrazí názov linky, ktorý obsahuje hodnotu B1 bunky.

V skutočnosti musíte hľadať súradnice v programe Excel. Načo to je? Pomerne často potrebujeme získať súradnice tabuľky podľa hodnoty. Malý sa podobá návratnosti matrice. Špecifický príklad v dvoch slovách vyzerá takto. Nastavený cieľ v číslach je zdrojová hodnota, musíte určiť, kto a kedy sa na tento účel najviac približuje. Napríklad používame jednoduchú dátovú matricu so správou počtu tovaru predávaného v troch štvrťrokoch, ako je uvedené nižšie na obrázku. Je dôležité, aby sa všetky číselné indikátory zhodovali. Ak nie je žiadna túžba manuálne vytvárať a vyplniť tabuľka programu Excel Z prázdneho listu je možné na konci článku prevziať s pripraveným príkladom.

Dôsledne zvažujeme možnosti riešenia inej zložitosti a na konci článku - konečný výsledok.

Hodnoty vyhľadávania v stĺpci programu Excel

Najprv sa naučte získať titulky stĺpcov tabuľky podľa hodnoty. Ak to chcete urobiť, postupujte nasledovne:

V bunke B1 zadajte hodnotu zhotovenú z tabuľky 5277 a zvýraznite jeho pozadie v modrej farbe pre čitateľnosť vstupného poľa (ďalej bude zavedený do ďalších buniek B1 na experimentovanie s novými hodnotami).
V C2 bunke zastupujeme vzor, \u200b\u200baby ste získali záhlavie stĺpca, ktorá obsahuje túto hodnotu:

Po zadaní vzorca pre potvrdenie stlačte kombináciu klávesových tlačidiel CTRL + SHIFT + ENTER + ENTER, pretože vzorec sa musí vykonať v poli. Ak sa všetko vykonáva správne v riadku vzorcov okolo okrajov, objavia sa kučeravé rovnátka ().

Hodnoty vyhľadávania v reťazci Excel

Teraz získame číslo riadku pre rovnakú hodnotu (5277). Ak to chcete urobiť, zadajte nasledujúci vzorec v C3 bunke:

Po opätovnom zadaní formulára na potvrdenie stlačte kláves CTRL + SHIFT + ENTER ENTER CHCEPTION a získajte výsledok:

Vzorec vrátený číslo 9 - našiel hlavičku reťazca plechu podľa zodpovedajúcej hodnoty tabuľky. V dôsledku toho máme celá adresa Hodnoty D9.

Ako získať hlavičku stĺpca a názov tabuľky

Teraz sa dozviete, ako dostávať súradnice nie je celý list, ale aktuálna tabuľka. Stručne povedané, musíme nájsť hodnotu 5277 namiesto D9 Get Titulky:

pre tabuľku stĺpcov - marec;
pre reťazec - produkt4.

Ak chcete vyriešiť tento problém, použijeme vzorca s už získanými hodnotami v bunkách C2 a C3. Urobte to:

V dôsledku toho sa vnútorné súradnice tabuľky získavajú peniaze - marec; Produkt 4:

Vyhľadajte identické hodnoty v rozsahu programu Excel

Na ovládanie prítomnosti duplikátov medzi hodnotou tabuľky vytvorte vzorec, ktorý nás môže informovať o dostupnosti duplikátov a počítať ich množstvo. Na to uvedieme vzorec do bunky E2:

Okrem toho, pre rozsah tabuľky, vytvoríme pravidlo podmienených formátovania:

Ako je možné vidieť v prítomnosti duplikátov vzorca pre titulky, podáva titul z prvého duplicitu vodorovne (zľava doprava). A vzorec na získanie názvu (číslo) čiary berie číslo z prvého duplikátu vertikálne (zhora nadol). Na opravu toto riešenie Existujú 2 spôsoby:

V tomto prípade meníme vzorce alebo jeden alebo iný, ale nie dva naraz. Stojí za to pripomenúť, že starý vzorec by mal zostať v C3 bunke:

Tu sú súradnice prvého duplicitného vertikálu správne zobrazené (zhora nadol) - i7 pre list a august; Produkt2 pre stôl. Nechajte túto možnosť ponechať na nasledujúci konečný príklad.

Vyhľadajte najbližšiu hodnotu v rozsahu programu Excel

Táto tabuľka stále nie je dokonalá. Koniec koncov, pri analýze musíte presne vedieť všetky svoje hodnoty. Ak sa generované číslo v bunke B1 nenachádza v tabuľke, potom sa vráti chyby - # znamená! Ideálne bolo, že vzorec v neprítomnosti v samotnej tabuľke zdrojov vybral najbližšiu hodnotu, ktorá obsahuje stôl. Ak chcete vytvoriť takýto program na analýzu tabuliek v bunke F1, zadajte nový vzorec:

Potom nasleduje vo všetkých ostatných vzorcoch zmeňte odkaz namiesto B1 musí byť F1! Musíte tiež zmeniť odkaz podmienené formátovanie. Vyberte: "Home" - "štýly" - "Podmienené formátovanie" - "Riadenie pravidiel" - "Zmeniť pravidlo". A tu v parametroch, špecifikujte F1 namiesto B1. Ak chcete skontrolovať prevádzku programu, zadajte počet, ktorý nie je v tabuľke do bunky B1, napríklad: 8000. To bude mať za následok konečný výsledok:

Teraz môžete zadať ľubovoľnú počiatočnú hodnotu a samotný program vyberie najbližšie číslo, ktoré obsahuje tabuľku. Potom zobrazí hlavičku stĺpca a názov reťazca pre aktuálnu hodnotu. Ak napríklad zadáte číslo 5000, dostaneme nový výsledok:

Náš program v programe Excel našiel najbližšiu hodnotu 4965 pre zdroj - 5000. Takýto program môže byť užitočný automatické riešenie Rôzne analytické úlohy pre podnikateľské plánovanie, stanovenie cieľov, hľadanie racionálneho riešenia atď. A získané čiary a stĺpce vám umožňujú ďalej rozšíriť výpočtové schopnosti tohto druhu správ pomocou nových výborných vzorcov.

Excel online pre systém Windows je možné úspešne ovládať pomocou externej klávesnice. Pre používateľov s obmedzenou pohyblivosťou alebo zlá víziou môže byť kombinácia kľúčov pohodlnejšia ako práca dotyková obrazovka alebo myš. Tento článok popisuje kombinácie kľúčových kombinácií pri práci aplikácia Excel Online pre Windows.

Poznámky:

Skratky klávesov v tomto článku zodpovedajú klávesnici prijatej v USA. Klávesové skratky pre iné rozloženie klávesnice sa môžu líšiť od tých, ktoré sú uvedené tu.

V kombináciách, kde sú súčasne stlačené niekoľko tlačidiel, ich názvy sú rozdelené podpisom plus (+). Ak sú kľúče stlačené postupne, sú oddelené čiarkou (,).

Často používaná kombinácia kľúčov

Tu uvádza najbežnejšie skratky pre Excel Online.

Konať
Prechod na určitú bunku
Pohybujúci sa	Stránka nadol alebo šípka nadol
Posunúť sa hore	Stránka nahor alebo šípka hore

Kópia

Vysekávanie
Zrušenie
Otvorenie knihy
Zatvorenie knihy


Tučné písmo
Otvorenie obsahové menu	Windows klávesnica: Kontextový hovor menu Windows+ F10. Klávesový kľúč Contextu systému Windows je ponechaný medzi klávesmi ALT a CTRL. Ostatné klávesnice: SHIFT + F10
Asistent
	CTRL + F alebo SHIFT + F3
Opakujte vyhľadávanie (nadol)
Opakujte vyhľadávanie (hore)
Vkladacia tabuľka

Prístupové kľúče: Klávesové skratky pre prácu so stuhou

V programe Excel Online, prístupové klávesy sú teraz k dispozícii - klávesové skratky pre pohyb po páse. Používajú sa rovnakým spôsobom ako v klasickej verzii programu Excel a umožňujú vám ušetriť čas pri práci.

V programe Excel 2016 pre Windows, takéto skratky začali kľúče Alt.. V programe Excel Online sa všetky takéto kombinácie začínajú s klávesmi ALT + WIN, spolu s ktorými je stlačené tlačidlo zodpovedajúce kartu. Karta "Review" napríklad otvorí klávesy ALT + WIN + R.

Ak používate Excel online počítačový Mac.Ak chcete spustiť prácu, stlačte CTRL + OPTP.

Ak chcete prejsť na pásku, stlačte klávesy ALT + WIN alebo stlačte klávesy Ctrl + F6, kým sa nenachádzate na karte Domov.

Ak chcete ísť medzi kartami pásky, stlačte kláves Tab.

Ak chcete skryť pásku a získajte viac miesta do práce, stlačte klávesy CTRL + F1. Ak chcete znova zobraziť pásku, opakujte túto akciu.

Používanie prístupových kľúčov pre pásku

Ak chcete prejsť na kartu pásky, stlačte jeden z nasledujúcich prístupových kľúčov.

Konať
Choď na "Čo chcete urobiť?" Na páse zadajte slovo vyhľadávanie.
Prejdite na kartu "Súbor" na prácu s výhľadom na zákulisku.
Prejdite na kartu "HOME" a formátovanie textu a čísla alebo pomocou iných nástrojov (napríklad vyhľadávanie).
Prejdite na kartu "Vložiť" a vkladanie funkcií, tabuliek, diagramov, hypertextových odkazov alebo nápisov.
Prepnite na kartu "DATA" a aktualizáciu odkazov alebo pomocou údajov do práce s údajmi.
Prejdite na kartu "Review" a kontrolu čitateľnosti alebo práce s poznámkami.
Otvorte kartu Zobraziť na výber zobrazení, zabezpečenie riadkov alebo stĺpcov knihy alebo siete alebo titulkov.

Práca s kartami a páskou menu

Skratky tlačidiel z tejto tabuľky vám pomôžu ušetriť čas pri práci s kartami a páskou menu.

Klávesové skratky pre Excel Online

Poznámka: Ak sa tabuľka v programe Excel Online otvorí v režime čítania, editácia príkazov nefungujú. Ak chcete prepnúť do režimu úprav, presuňte zaostrenie mimo hárku (stlačte kláves Ctrl + F6) a potom pomocou tlačidla karty prejdete na zoznam Upravte knihu. Stlačte tlačidlo Space, potom tlačidlo so šípkou nadol a potom vyberte príkaz Zmena programu Excel Online.

Excel Online Príkazy: Úprava buniek

Konať	Kľúče
Vloženie tabuľkových riadkov zhora	Alt + win + me, výbušniny, a
Vložte stĺpce tabuľky vľavo	Alt + win + i, bb, l
Vysekávanie
Kópia

Zrušenie
Opakovanie
Ísť do nový reťazec V bunke

Vložiť tabuľku
Vložiť funkcie
Zvýšte veľkosť písma
Zníženie veľkosti písma
Použitie filtra
Opakovaný filter

Excel Online Príkazy: Zadanie údajov

Excel Online Príkazy: Úprava údajov v bunke

Konať	Kľúče
Úprava vybranej bunky
Technická podpora pre používateľov so zdravotným postihnutím Microsoft sa snaží zabezpečiť, aby všetci naši zákazníci dostali najlepšie produkty a údržba. Ak máte obmedzené možnosti alebo máte záujem o otázky týkajúce sa Špeciálne vlastnostiKontaktujte položku Microsoft Deciters Odpoveď na získanie technická podpora. Specialisti Microsoft Decility Odpoveď Specialisti sú oboznámení s mnohými obľúbenými špeciálnymi funkciami a môžu podporovať angličtinu, španielčinu, francúzštinu, ako aj na americkom geste. Prejdite na položku Microsoft Deciters Odpovedať, aby ste zistili kontaktné informácie pre váš región. Ak ste zástupcom štátnej inštitúcie alebo obchodnej organizácie, kontaktujte

Konať

Kľúče

Úprava vybranej bunky

Technická podpora pre používateľov so zdravotným postihnutím

Microsoft sa snaží zabezpečiť, aby všetci naši zákazníci dostali najlepšie produkty a údržba. Ak máte obmedzené možnosti alebo máte záujem o otázky týkajúce sa Špeciálne vlastnostiKontaktujte položku Microsoft Deciters Odpoveď na získanie technická podpora. Specialisti Microsoft Decility Odpoveď Specialisti sú oboznámení s mnohými obľúbenými špeciálnymi funkciami a môžu podporovať angličtinu, španielčinu, francúzštinu, ako aj na americkom geste. Prejdite na položku Microsoft Deciters Odpovedať, aby ste zistili kontaktné informácie pre váš región.

Ak ste zástupcom štátnej inštitúcie alebo obchodnej organizácie, kontaktujte

Táto učebnica rozpráva o hlavných výhod funkcií. Index a Výklad v programe Excel, ktoré sú v porovnaní s Priťahovať. Uvidíte niekoľko príkladov vzorcov, ktoré vám pomôžu ľahko vyrovnať s mnohými zložitými úlohami, ktoré fungujú Priťahovať bezmocný.

V niekoľkých nedávnych článkoch sme vynaložili maximálne úsilie na objasnenie nováčikov používateľov funkcie Priťahovať a zobrazia príklady zložitejších vzorcov pre pokročilých používateľov. Teraz sa pokúsime, ak vás nepoužívate Priťahovať, aspoň ukázať alternatívne metódy Implementácia vertikálneho vyhľadávania v programe Excel.

Prečo to potrebujeme? - pýtaš sa. Áno, lebo Priťahovať - Toto nie je jediná funkcia vyhľadávania v programe Excel a jeho početné obmedzenia vám môže zabrániť tomu, aby ste získali požadovaný výsledok v mnohých situáciách. Na druhej strane funkcie Index a Výklad - Flexibilnejšie a majú rad funkcií, ktoré ich robia atraktívnejším v porovnaní s Priťahovať.

Základné informácie o indexe a vyhľadávaní

Ako úloha tohto návodu - zobraziť funkcie Index a Výklad Ak chcete implementovať vertikálne vyhľadávanie v programe Excel, nebudeme pretrvávať na ich syntaxi a aplikáciu.

Tu urobíte požadované minimum na pochopenie podstaty, a potom podrobne analyzujeme príklady vzorcov, ktoré ukazujú prínosy používania Index a Výklad namiesto toho Priťahovať.

Index - syntax a aplikácia

Funkcia Index (Index) v programe Excel Vráti hodnotu z poľa podľa zadaného riadku a čísla stĺpcov. Funkcia má takúto syntax:

Každý argument má veľmi jednoduché vysvetlenie:

pole. (Array) je rozsah buniek, z ktorých je potrebné extrahovať hodnotu.
rOW_NUM (NUMBER_NAME) je číslo riadka v poli, z ktorého chcete získať hodnotu. Ak nie je uvedené, je potrebné argument. stĺpec_num (_stolbet číslo).
stĺpec_num (Number_Number) je číslo stĺpca v poli, z ktorého chcete extrahovať. Ak nie je uvedené, je potrebné argument. rOW_NUM (NUMBER_STROOKE)

Ak sú uvedené obidve argumenty, potom funkcia Index Vráti hodnotu z bunky umiestnenej na priesečníku zadaných riadkov a kolóny.

Tu najjednoduchší príklad Funkcie Index (Index):

Index (A1: C10,2,3)
\u003d Index (A1: C10; 2; 3)

Vzorec Vykonáva vyhľadávanie v rozsahu A1: C10 a vráti hodnotu bunky 2-y. Riadok I. 3m stĺpca, to znamená z bunky C2..

Veľmi jednoduché, že? Avšak, v praxi, vždy neviete, ktorý riadok a stĺpec, ktorý potrebujete, a preto potrebujete pomoc Výklad.

Vyhľadávanie syntaxe a funkcie aplikácie

Funkcia Zápas. (Vyhľadávanie) v programe Excel hľadá zadanú hodnotu v rozsahu buniek a vráti relatívnu polohu tejto hodnoty v rozsahu.

Napríklad, ak v rozsahu B1: B3. Obsahuje nové hodnoty New York, Paríž, London, potom nasledujúci vzorec vráti číslo 3 Vzhľadom k tomu, "London" je tretia položka v zozname.

Zápas ("London", B1: B3.0)
\u003d Search Board ("Londýn", B1: B3; 0)

Funkcia Zápas. (Hľadanie) má taká syntax:

Zápas (lookup_value, lookup_array,)
Umiestnenie vyhľadávania (platí_station; pozeranie_missive; [TYPE_STATION])

lookup_value. (Požadované) je číslo alebo text, ktorý hľadáte. Argument môže byť hodnota, vrátane logického alebo odkazu na bunku.
lookup_array. (Pri pohľade cez_massive) - rozsah buniek, v ktorých sa vyhľadávanie uskutočňuje.
match_type. (Typ typu) - tento argument vykazuje funkcie Výkladči chcete nájsť presný alebo približný zápas:
- 1 alebo nešpecifikované - zapadá do maximálnej hodnoty, menej alebo rovné požadovanému. Pozvaná plocha sa musí objednať vzostupne, to znamená, že z menšieho na viac.
- 0 - nájde prvú hodnotu rovnú požadovanú. Pre kombináciu Index/Výklad Vždy potrebujete presnú zhodu, takže funkcia tretieho argumentu Výklad Musia byť rovnaké 0 .
- -1 - nájde najmenšiu hodnotu, viac alebo rovnú požadovanú hodnotu. Pozvaná plocha musí byť objednaná v zostupnom poradí, to znamená z väčšieho na menšie.

Na prvý pohľad, výhody funkcie Výklad Spôsobuje pochybnosti. Kto potrebuje poznať pozíciu prvku v rozsahu? Chceme poznať význam tejto položky!

Dovoľte mi pripomenúť, že relatívna poloha požadovanej hodnoty (t.j. číslo riadku a / alebo stĺpca) je presne to, čo musíme špecifikovať pre argumenty rOW_NUM (číslo_) a / alebo stĺpec_num (Number_stall) funkcie Index (Index). Ako si spomínate na funkciu Index Môže vrátiť hodnotu v priesečníku zadaných riadkov a stĺpca, ale nemôže určiť, ktorý riadok a stĺpec sa o nás zaujíma.

Ako používať index a expcode v programe Excel

Teraz, keď poznáte základné informácie o týchto dvoch funkciách, verím, že je jasné, ako funguje Výklad a Index Môže spolupracovať. Výklad určuje relatívnu polohu požadovanej hodnoty v špecifikovanom rozsahu buniek a Index Používa toto číslo (alebo čísla) a vracia výsledok z príslušnej bunky.

Nie je to úplne jasné? Predstavte funkcie Index a Výklad V tomto formulári:

Index ((, (zápas ( sekundárna hodnota,stĺpec, v ktorom hľadáme,0))
\u003d Index ( stĺpec, z ktorého odstránite(Hlavná rada ( sekundárna hodnota;stĺpec, v ktorom hľadáme;0))

Myslím, že to bude tiež ľahšie pochopiť príklad. Predpokladajme, že máte taký zoznam štátnych metrov:

Poďme nájsť populáciu jedného z hlavných miest, napríklad Japonsko, pomocou nasledujúceho vzorca:

Index ($ D $ 2: $ D $ 10, zápas ("Japonsko", $ 2: $ B $ 10,0)
\u003d Index ($ D $ 2: $ D $ 10; Search Board ("Japonsko"; $ B $ 2: $ B $ 10; 0)

Pozrime sa teraz na to, čo robí každý prvok tohto vzorca:

Funkcia Zápas. (Vyhľadávanie) vyhľadáva "Japonsko" v stĺpci B.Konkrétne - v bunkách B2: B10a vráti číslo 3 Od "Japonska" v zozname na treťom mieste.
Funkcia Index (Index) 3 Pre argument rOW_NUM (NUMBER_STROOKE), ktorý označuje, z ktorého reťazca musíte vrátiť hodnotu. Tí. Ukazuje sa jednoduchý vzorec:
Index ($ D $ 2: $ D $ 10.3)
\u003d Index ($ D $ 2: $ D $ 10; 3)
Vzorec hovorí o nasledujúcom texte: Pozrite sa na bunky D2. predtým D10 a odstráňte hodnotu z tretieho riadku, to znamená z bunky D4.Keďže účet začína druhým riadkom.

Toto je výsledok bude v programe Excel:

DÔLEŽITÉ! Počet riadkov a stĺpcov v poli, ktoré používa funkciu Index (Index) musí spĺňať hodnoty argumentov rOW_NUM (číslo_) a stĺpec_num (Number_stall) funkcie Zápas. (Vyhľadávanie). V opačnom prípade bude výsledok vzorca chybný.

Zastavte, zastavte ... Prečo nemôžeme jednoducho používať funkciu Vlookup. (UPR)? Má zmysel tráviť čas sa snaží prísť na labyrints Výklad a Index?

VLOOKUP ("Japonsko", $ B $ 2: $ D $ 2,3)
\u003d VD ("Japonsko", $ B $ 2: $ D $ 2;

V tomto prípade nie je zmysel! Účelom tohto príkladu je výlučne demonštrácia, takže môžete pochopiť, ako funguje Výklad a Index Pracovať v pároch. Následné príklady vám ukážu skutočnú silu balíka Index a Výkladktoré sa ľahko vyrovnajú s mnohými zložitými situáciami, keď Priťahovať Ukazuje sa v slepom konci.

Prečo je index / vyhľadávacia doska lepšie ako PRD?

Riešenie, ktoré vzorec na použitie pre vertikálne vyhľadávanie, väčšina Guru Excel verí Index/Výklad oveľa lepšie ako Priťahovať. Mnohí užívatelia programu Excel však stále uchýlili k používaniu Priťahovaťpretože Táto funkcia je oveľa jednoduchšia. To sa stane, pretože veľmi málo ľudí plne chápe všetky výhody prechodu Priťahovať Na parti Index a VýkladA nikto nechce tráviť čas na učenie zložitejšieho vzorca.

4 Hlavné výhody používania vyhľadávacej rady / indexu v programe Excel:

1. Nájdenie vľavo. Ako viete, každý príslušný užívateľ programu Excel, Priťahovať Nemôže vyzerať doľava, čo znamená, že požadovaná hodnota musí byť v extrémnom ľavom stĺpci študijného rozsahu. V prípade Výklad/Index, vyhľadávací stĺpec môže byť vľavo a na pravej strane vyhľadávacieho rozsahu. Príklad: Vykazuje túto príležitosť v akcii.

2. Bezpečné pridanie alebo odstránenie stĺpcov. Formuláry s funkciou Priťahovať Prestaňte pracovať alebo vrátiť chybné hodnoty, ak vymažete alebo pridajte stĺpec do vyhľadávacej tabuľky. Pre funkciu Priťahovať Akákoľvek vložená alebo vzdialená stĺpec zmení výsledok vzoru, pretože syntax Priťahovať Vyžaduje špecifikovať celý rad a špecifické číslo stĺpca, z ktorého chcete údaje získať.

Napríklad, ak máte tabuľku A1: C10A je potrebné extrahovať údaje z stĺpca B.Potom musíte nastaviť hodnotu 2 Pre argument coll_index_num (Number_stall) funkcie Priťahovať, Páči sa ti to:

Vlookup ("Hodnota vyhľadávania", A1: C10,2)
\u003d Hodnota (hodnota "vyhľadávania"; A1: C10; 2)

Ak neskôr vložíte nový stĺpec medzi stĺpcami A. a B., hodnota argumentu sa bude musieť zmeniť 2 na 3 V opačnom prípade vzorec vráti výsledok z rovnakého vloženého kolóny.

Použitím Výklad/IndexStĺpce môžete odstrániť alebo pridať do skúšobného rozsahu bez skreslenia výsledku, pretože kolóna obsahujúca požadovanú hodnotu je definovaná. Je to veľká výhoda, najmä pri práci s veľkými objemami údajov. Stĺpce môžete pridať a odstrániť bez toho, aby ste sa starali o to, že bude potrebné napraviť každú použitú funkciu. Priťahovať.

3. Neexistuje žiadny limit veľkosti požadovanej hodnoty. Použitím PriťahovaťZapamätajte si limit na dĺžku požadovanej hodnoty v 255 znakoch, inak riskujete chybu #Value! (# Znamená!). Takže, ak tabuľka obsahuje dlhé čiary, jediným hereckým riešením je použitie Index/Výklad.

Predpokladajme, že používate tento vzorec s Priťahovaťhľadáte v bunkách B5. predtým D10 Hodnota uvedená v bunke A2.:

VLOOOKUP (A2, B5: D10,3, FALSE)
\u003d VD (A2; B5: D10; 3; LIE)

Vzorec nebude fungovať, ak je hodnota v bunke A2. Dlhých ako 255 znakov. Namiesto toho potrebujete použiť podobný vzorec Index/Výklad:

Index (D5: D10, zápas (true, index (B5: B10 \u003d A2,0), 0)
\u003d Index (D5: D10; Hľadajúca doska (pravda; index (B5: B10 \u003d A2; 0); 0))

4. Vyššia rýchlosť. Ak pracujete s malými stolmi, rozdiel v rýchlosti programu Excel bude s najväčšou pravdepodobnosťou nie je viditeľný, najmä v nedávne verzie. Ak pracujete s veľkými tabuľkami, ktoré obsahujú tisíce riadkov a stoviek vyhľadávacích vzorcov, program Excel bude pracovať oveľa rýchlejšie Výklad a Index namiesto toho Priťahovať. Všeobecne platí, že takáto náhrada zvyšuje rýchlosť excel funguje na 13% .

Vplyv Priťahovať Výkon Excel je obzvlášť viditeľný, ak zošit obsahuje stovky komplexných arvakových vzorcov, ako napr PRP + SUMS. Faktom je, že kontrola každej hodnoty v poli vyžaduje samostatný funkčný hovor Priťahovať. Preto, tým viac hodnôt obsahujú pole a tým viac vzorca pre pole obsahuje váš stôl, pomalšie funguje Excel.

Na druhej strane, vzorec s funkciami Výklad a Index Jednoducho vyhľadáva a vráti výsledok, ktorý vykonáva podobnú prácu, ktorá je viditeľná rýchlejšia.

Index a vyhľadávanie - príklady vzorcov

Teraz, keď chápete dôvody, z ktorých stojí za to študovať funkcie Výklad a IndexPoďme na najzaujímavejšie a vidieť, ako možno teoretické vedomosti aplikovať v praxi.

Ako vyhľadávať na ľavej strane pomocou vyhľadávania a indexu

Akákoľvek učebnica Priťahovať To znamená, že táto funkcia nemôže vyzerať doľava. Tí. Ak stĺpec zobrazený nie je extrémny vľavo v rozsahu vyhľadávania, potom nie je šanca dostať sa z Priťahovať Požadovaný výsledok.

Funkcie Výklad a Index Excel je oveľa flexibilnejší a sú stále rovnako ako tam je stĺpec s hodnotou, ktorú treba odstrániť. Napríklad sa vrátime do tabuľky s hlavnými hlavnými štátmi a obyvateľstvom. Tentokrát napíšeme vzorec Výklad/Indexktorý ukáže, aké miesto v obyvateľstve je obsadené hlavným mestom Ruska (Moskva).

Ako je možné vidieť na obrázku nižšie, vzorec je dokonale vyrovnať sa s touto úlohou:

Index ($ A $ 2: $ A $ 10, zápas ("Rusko", $ 2: $ B $ 10,0)

Teraz by ste nemali mať žiadne problémy s pochopením, ako tento vzorec funguje:

Po prvé, používame funkciu Zápas. (Umiestnenie), ktorá nájde pozíciu "Rusko" v zozname:
Zápas ("Rusko", $ B $ 2: $ B $ 10,0)
\u003d Search Board ("Rusko", $ B $ 2: $ B $ 10; 0))
Potom nastavte rozsah pre funkciu Index (Index), z ktorého potrebujete extrahovať. V našom prípade to A2: A10..
Potom pripojte obe časti a získajte vzorca:
Index ($ A $ 2: $ A $ 10; Match ("Rusko", $ B $ 2: $ B $ 10; 0))
\u003d Index ($ A $ 2: $ A $ 10; Hľadaná rada ("Rusko"; $ 2: $ B $ 10; 0))

Rýchla: Správne riešenie vždy bude používať absolútne odkazy Index a VýkladTakže sa rozsahy vyhľadávania nepohybujú pri kopírovaní vzorca na iné bunky.

Výpočty pomocou indexu a expozície v Excel (Srvnav, max, min)

Môžete dať ostatných excel Funkcie v Index a Výklad, napríklad, aby ste našli minimum, maximum alebo najbližšie k priemernej hodnote. Tu sú niektoré možnosti pre vzorce, ako sa aplikuje na tabuľku z:

1. Max (Max). Formula je maximálne stĺpca D. C. tých istých riadkov:

Index ($ C $ 2: $ C $ 10, zápas (max ($ D $ 2: I $ 10), $ D $ 2: D $ 10,0)
\u003d Index ($ C $ 2: $ C $ 10; Hľadaná rada (max ($ D $ 2: I $ 10); $ D $ 2: D $ 10; 0))

Výsledok: Peking

2. Min. (Min). Formula je minimum v stĺpci D. a vráti hodnotu z stĺpca C. tých istých riadkov:

Index ($ C $ 2: $ C $ 10, zápas (min (min ($ D $ 2: I $ 10), $ D $ 2: D $ 10,0)
\u003d Index ($ C $ 2: $ C $ 10; Search Board (Min ($ D $ 2: I $ 10); $ D $ 2: D $ 10; 0))

Výsledok: Lima.

3. Priemer. (Srnvov). Vzorec vypočíta priemer v rozsahu D2: D10Potom prichádza najbližšie k nemu a vráti hodnotu z stĺpca C. tých istých riadkov:

Index ($ C $ 2: $ C $ 10, zápas (priemer ($ D $ 2: D $ 10), $ D $ 2: D $ 10,1)
\u003d Index ($ C $ 2: $ C $ 10; Search Board (CPNAH ($ D $ 2: D $ 10); $ D $ 2: D $ 10; 1))

Výsledok: Moskva

Čo je potrebné pripomenúť pomocou funkcie Srvnov spolu s indexom a vyhľadávaním

Pomocou funkcie Srnzoke V kombinácii S. Index a VýkladAko argument tretej funkcie Výklad Najčastejšie bude potrebné uviesť 1 alebo -1 V prípade, že si nie ste istí, že pohľavný rozsah obsahuje hodnotu rovnajúcu sa priemeru. Ak ste si istí, že existuje taká hodnota - dať 0 Hľadať presnú zhodu.

Ak zadáte 1 Hodnoty v stĺpci vyhľadávania sa musia objednať vzostupne a vzorec vráti maximálnu hodnotu menšiu alebo rovnú priemerne.
Ak zadáte -1 Hodnoty vo vyhľadávacom stĺpci musia byť objednané v zostupnom poradí a vrátená bude minimálna hodnota, viac alebo rovná priemeru.

V našom príklade hodnota v stĺpci D. Objednané vzostupne, takže používame typ porovnania 1 . Vzorec Index/Hľadať podľaZ. Vracia "Moskva", pretože populácia mesta Moskvy je najbližšia nižšia ako priemerná hodnota (12 269 006).

Ako hľadať index a vyhľadať vyhľadávanie podľa známych riadkov a stĺpca

Tento vzorec je ekvivalentný dvojdimenzionálnym vyhľadávaním. Priťahovať a umožňuje nájsť hodnotu na križovatke konkrétneho reťazca a stĺpca.

V tomto príklade vzorca Index/Výklad Bude to veľmi podobné vzorcom, ktoré sme už diskutovali v tejto lekcii, s týmto rozdielom. Hádaj čo?

Ako si pamätáte funkciu syntaxe Index (Index) Umožňuje používať tri argumenty:

Index (Array, Row_num,)
Index (pole, číslo servera; [Number_stolbits])

A blahoželám tým z vás, ktorí ste uhádli!

Začnime s tým, že píšeme šablónu vzorca. Ak to chcete urobiť, vezmite si vzorec, ktorý nám už známy. Index/Výklad a pridajte k nemu ďalšiu funkciu Výkladktorý vráti číslo stĺpca.

Index (váš stôl (zápas (, stĺpec0)) (zápas (, reťazec, v ktorom hľadať,0))
\u003d Index (váš stôl (zápas ( vertikálna hodnota vyhľadávania,stĺpec0)), (zápas ( hodnota pre horizontálne vyhľadávanie,reťazec, v ktorom hľadať,0))

Všimnite si, že pre dvojrozmerné vyhľadávanie musíte zadať celý stôl v argumente pole. (Array) Funkcie Index (Index).

A teraz tento vzor testujeme v praxi. Nižšie nájdete zoznam najviac obývaných krajín na svete. Predpokladajme, že našej úlohy poznávať americkú populáciu v roku 2015.

No, napíšte vzorec. Keď potrebujem vytvoriť komplikovaný vzorec v programe Excel s vnorenými funkciami, potom som prvýkrát zaznamenal každý investovaný samostatne.

Začnime s dvoma funkciami Výkladktorý vráti čísla riadkov a stĺpec pre funkciu Index:

Vyhľadávanie stĺpca - Pozeráme sa na stĺpec B.alebo skôr v rozsahu B2: B11., hodnota, ktorá je špecifikovaná v bunke H2. (USA). Funkcia bude vyzerať takto:
Zápas ($ h $ 2, $ b $ 1: $ B $ 11.0)
\u003d Hľadať spoločnosť ($ h $ 2; $ 1: $ B $ 11; 0)
4 pretože "USA" je 4. prvok zoznamu v stĺpci B. (vrátane titulu).
Vyhľadávanie reťazca - Hľadáme bunkovú hodnotu H3. (2015) v reťazci 1 , to znamená v bunkách A1: E1:
Zápas ($ h $ 3, $ A $ 1: $ E $ 1.0)
\u003d Hľadať spoločnosť ($ h $ 3; $ 1: $ E $ 1; 0)

Výsledok tohto vzorca bude 5 Vzhľadom k tomu, "2015" je v 5. stĺpci.

Teraz vložte tieto vzorce k funkcii Index a voila:

Index ($ A $ 1: $ E $ 11, zápas ($ h $ 2, $ B $ 1: $ B $ 11.0), zápas ($ h $ 3, $ A $ 1: $ E $ 1,0)
\u003d Index ($ A $ 1: $ E $ 11; Hľadaná rada ($ h $ 2; $ 1: $ B $ 11; 0); Search Board ($ h $ 3; $ 1: $ E $ E $ 1; 0))

Ak nahradíte funkcie Výklad Hodnoty, ktoré sa vrátia, vzorca sa stanú jednoduchým a zrozumiteľným:

Index ($ A $ 1: $ E $ 11,4,5)
\u003d Index ($ A $ 1: $ E $ 11; 4; 5))

Tento vzorec vráti hodnotu na križovatke. 4. miesto Riadky I. 5. miesto Stĺpec v rozsahu A1: E11, to znamená bunková hodnota E4.. Jednoducho? Áno!

Vyhľadajte niekoľko kritérií s indexom a vyhľadávaním

V učebni Priťahovať Ukázali sme príklad vzorca s funkciou Priťahovať Hľadať niekoľko kritérií. Podstatné obmedzenie takéhoto roztoku však bolo pridať pomocný stĺpec. Dobrá správa: Formula Index/Výklad Môžete vyhľadávať podľa hodnôt v dvoch stĺpcoch, bez nutnosti vytvoriť pomocný stĺpec!

Predpokladajme, že máme zoznam objednávok a chceme nájsť sumu na dve kritériá - názov kupujúceho (Zákazník) a výrobok (Produkt). Prípad je komplikovaný tým, že jeden kupujúci si môže kúpiť niekoľko rôznych produktov naraz, a kupujúcich 'mená v tabuľke na hárku Vyhľadávacia tabuľka Nachádza sa v náhodnom poradí.

Tu je taký vzorec Index/Výklad Rieši úlohu:

(\u003d Index ("vyhľadávací stôl"! $ A $ 2: $ C $ 13, zápas (1, (A2 \u003d "vyhľadávací stôl"! $ A $ 2: $ A $ 13) *
(B2 \u003d "vyhľadávací stôl"! $ B $ 2: $ B $ 13), 0), 3)
(\u003d Index ("vyhľadávací stôl"! $ A $ 2: $ C $ 13, Hľadať spoločnosť (1; (A2 \u003d "vyhľadávací stôl"! $ A $ 2: $ A $ 13) *
(B2 \u003d "vyhľadávací stôl"! $ B $ 2: $ B $ 13); 0); 3)

Týmto vzorcom je zložitejšia ostatnými, o ktorých sme diskutovali skôr, ale vyzbrojení znalosťami funkcií. Index a Výklad Prekonáte to. Najťažšia časť je funkcia. VýkladMyslím si, že je potrebné najprv vysvetliť.

Zápas (1, (A2 \u003d "vyhľadávací stôl"! $ A $ 2: $ A $ 13), 0) * (B2 \u003d "Lookup Table"! $ B $ 2: $ B $ 13)
Vyhľadávacia spoločnosť (1; (A2 \u003d "vyhľadávací stôl"! $ A $ 2: $ A $ 13); 0) * (B2 \u003d "Lookup Table"! $ B $ 2: $ B $ 13)

Vo vyššie uvedenej vzorci je požadovaná hodnota 1 a vyhľadávacie pole je výsledkom násobenia. Čo by sme mali znásobiť a prečo? Analyzujme všetko v poriadku:

Vezmite prvú hodnotu v stĺpci A. (Zákazník) na hárku Hlavná tabuľka a porovnať ho so všetkými menami kupujúcich v tabuľke na hárku Vyhľadávacia tabuľka (A2: A13).
Ak sa nájde náhoda, vráti sa rovnica 1 (Pravda), a ak nie - 0 (FALSE).
Ďalej robíme to isté pre hodnoty stĺpcov. B. (Produkt).
Potom získame výsledky získané (1 a 0). Iba ak sa zhodujú zhody v oboch stĺpcoch (t.j. obidva kritériá sú pravdivé), dostanete 1 . Ak sú obe kritériá nepravdivé, alebo sa vykonáva len jeden z nich - dostanete 0 .

Teraz chápete, prečo sme sa pýtali 1 Ako požadovaná hodnota? To je správne fungovať Výklad Vrátil pozíciu len vtedy, keď sa vykonávajú obidve kritériá.

Poznámka: V tomto prípade je potrebné použiť tretí nie je povinný argument funkcie. Index. Je to potrebné, pretože V prvom argumente sme nastavili celý stôl a musíme špecifikovať funkcie, z ktorých je potrebné odstrániť stĺpec. V našom prípade je to stĺpec C. (Súčet), a preto sme zaviedli 3 .

A nakoniec, pretože Musíme kontrolovať každú bunku v poli, tento vzorec musí byť vzorec poľa. Môžete vidieť podľa zátvoriek, v ktorých je uzavretý. Preto, keď dokončíte vstup do vzorca, nezabudnite kliknúť Ctrl + Shift + ENTER.

Ak sa všetko vykoná správne, dostanete výsledok ako na obrázku nižšie:

Indexová a vyhľadávacia rada v kombinácii s IF v Excel

Ako ste pravdepodobne už si všimli (a viac ako raz), ak zadáte nesprávnu hodnotu, napríklad, ktorá nie je v zobrazenom poli, vzorec Index/Výklad Správy o chybe # N / a (# N / d) alebo #Value! (# Znamená!). Ak chcete nahradiť takúto správu na niečo pochopiteľnej, môžete vložiť vzorca Index a Výklad vo funkcii Ak je servis.

Funkcia syntaxe Ak je servis Veľmi jednoduché:

Iferror (hodnota, hodnota_IF_IRROR)
Ak je užitočná hodnota (hodnota, hodnota hodnoty_IF_)

Kde je argument hodnotu. (hodnota) je hodnota overená pre chybu (v našom prípade - výsledok vzorca Index/Výklad); Argument hodnota_if_error (Hodnota chýb) je hodnota, ktorú chcete vrátiť, ak sa vydá chyba.

Môžete napríklad vložiť do funkcie Ak je servis Takto tento spôsob:

Iferror (index ($ A $ 1: $ E $ 11, zápas ($ G $ 2, $ 1: $ B $ 11.0), zápas ($ G $ 3, $ A $ 1: $ E $ 1,0 ))
"Zhody neboli nájdené. Skúste to znova!") \u003d Ak index ($ A $ 1: $ E $ 11; Hľadať spoločnosť ($ G $ 2; $ 1: $ B $ 11; 0); ($ G $ 3; $ A $ 1: $ E $ 1; 0);
"Zhody neboli nájdené. Skúste to znova!")

A teraz, ak niekto vstúpi do chybnej hodnoty, vzorec vydá tento výsledok:

Ak uprednostňujete v prípade chyby, nechajte bunku prázdny, môžete použiť úvodzovky (""), ako hodnota druhej funkcie argumentu Ak je servis. Páči sa ti to:

Iferror (Index (pole, zápas (Požitý_station, zobrazený BY_MASSIVE, 0), "")
Ak chyba (index (pole, vyhľadávacie rada (požadované číslo, zobrazenie_massive; 0); ")

Dúfam, že aspoň jeden vzorec opísaný v tejto učebnej knihe sa vám zdalo užitočné. Ak ste čelili ostatným úlohám vyhľadávania, pre ktoré ste nemohli nájsť vhodné riešenie medzi informáciami v tejto lekcii, neváhajte popísať svoj problém v komentároch a všetci sa pokúsite vyriešiť spoločne.

Tento článok sa zaoberá syntaxou funkcie Vyhľadávanie programu Excel, ako aj ako používať funkciu Excel Hľadať príklady vzor

Aj v tomto článku sa zaoberá kombináciou vzorcov Index Excel.

V programe Microsoft Excel existuje mnoho rôznych funkcií vyhľadávania, ktoré môžu pomôcť nájsť určitú hodnotu v mnohých bunkách a Výklad - jeden z nich. Hľadať angličtinu Zápas, v podstate identifikovať relatívnu polohu prvku v rozsahu buniek. Avšak funkcia Vyhľadávanie programu Excel V kombinácii s inými funkciami môže urobiť oveľa viac.

Funkčné vyhľadanie programu Excel - Syntax a použitie

Funkcia vyhľadávania programu Excel Hľadáte zadanú hodnotu v rozsahu buniek a vráti relatívnu polohu tejto hodnoty.

Syntax formuláry vyhľadávacej rady nasledovne:

Umiestnenie vyhľadávania (platí_station; pozeranie_missive; [TYPE_STATION])

Skeping_new (Povinné argument) - hodnota, ktorú chcete nájsť. Môže to byť numerická, textová alebo logická hodnota, ako aj odkaz na bunku.

Viditeľný_massívny (Povinné argument) - Rozsah vyhľadávacích buniek.

TYPY_SPECIFIKÁCIA (Voliteľný argument) - určuje typ zhody. To môže byť jedna z nasledujúcich hodnôt: 1, 0, -1.

1 alebo chýba (štandardne) - nájde najväčšia hodnota V poli, ktoré je menšie alebo rovné vyhľadávacej hodnote. Vyhľadávacie pole musí byť zoradené vo vzostupnom poradí od najmenšieho až veľkého alebo od A do Z.
0 - nájde prvú hodnotu v poli presne Hodnota vyhľadávania. Triedenie sa nevyžaduje.
-1 - nájde najmenšia hodnota V poli, ktoré je väčšie alebo rovné vyhľadávacej hodnote. Vyhľadávacie pole musí byť zoradené v zostupnom poradí z najväčších do najmenšieho alebo z Z do A.

Ak chcete lepšie postaviť túto funkciu, napíšte jednoduchý vzorec vyhľadávacej radyNa základe nasledujúcich údajov: mená študentov v stĺpci A a ich skóre na skúškach v stĺpci B, zoradené od najvyšších až po najnižšie. Ak chcete zistiť, kde okrem iného je konkrétny študent (napríklad študent Vinogradov), použite tento jednoduchý vzorec:

Vyhľadávacia spoločnosť (E1; A2: A8; 0)

Ak si želáte, môžete umiestniť hodnotu vyhľadávania do bunky (E1 IN tento príklad) a odkazujú na túto bunku v produkte vyhľadávania Excel:

Funkcia Vyhľadávanie programu Excel - Príklad použitia funkcie vyhľadávania v programe Excel

Ako vidíte na obrázku vyššie, priezviská študentov sú zavedené v ľubovoľnom poradí, takže sme stanovili argument tYPY_SPECIFIKÁCIA Rovnaký 0 (presný zápas), pretože len tento typ zhody nevyžaduje triedenie hodnôt vo vyhľadávacom poli. Technicky, vzorec výkladnej rady vráti relatívnu polohu gradu študentov v študijnom rozsahu. Ale keďže odhady sú zoradené od najvyššieho k najnižšiemu, tiež nám hovorí, že hrozno piateho účtu medzi všetkými študentmi.

Vlastnosti vyhľadávacie funkcie programu Excel

Ako ste práve videli, použite vyhľadávaciu tabuľu v programe Excel je jednoduchá. Avšak, rovnako ako v akomkoľvek funkcii Excel, má niekoľko funkcií, o ktorých by ste mali vedieť o:

Funkčná vyhľadávacia spoločnosť sa vracia relatívna poloha Hľadať hodnoty v poli a nie veľmi význam.
Výklad nezávisí od registraČo to znamená, že pri práci s textovými hodnotami nerozlišuje malé a veľké písmená.
Ak vyhľadávacie pole obsahuje niekoľko výskytov požadovanej hodnoty, vráti sa poloha prvej hodnoty.
Ak sa v zobrazenom poli nenašla požadovaná hodnota, chyba # n / d sa vráti.

Použitie vyhľadávania programu Excel - príklady vzorcov

Teraz, keď poznáte hlavné funkcie fUNKCIE Hľadať ExcelPoďme zvážiť niekoľko ďalších príkladov vzorcov, ktoré presahujú rámec základov.

Excel Hľadať Formula s zástupnými znakmi

Rovnako ako mnoho ďalších funkcií programu Excel, Výklad Uznáva tieto zástupné znaky:

Otáznik (?) - Nahrádza akýkoľvek znak
Asterisk (*) - nahrádza akúkoľvek sekvenciu znakov

Poznámka. Zástupné znaky môžu byť použité len v vzorce vyhľadávacej rady s parametrom tYPY_SPECIFIKÁCIA Rovná 0.

Vzorec vyhľadávacej rady S zástupnými znakmi je užitočné v situáciách, keď potrebujete vyhľadávať nie všetky textové reťazec, ale len niektoré znaky alebo časť riadku. Ak chcete to zobraziť, zvážte nasledujúci príklad.

Predpokladajme, že máte zoznam spoločností a ich predajné údaje za posledný týždeň. Chcete nájsť relatívnu pozíciu určitej spoločnosti v zozname (zoradené podľa tržieb v zostupnom poradí), ale nemôžete presne spomenúť na jeho meno, hoci si pamätáte niekoľko prvých písmen.

Predpokladá sa, že mená spoločností sú v rozsahu A2: A8, a hľadáte meno začínajúce na "východe" vzorca nasledovne:

Parkovanie (východné * "; A2: A8; 0)

Urobiť naše vzorec vyhľadávacej rady Viac Universal, môžete zadať vyhľadávaciu hodnotu v akejkoľvek bunke (E1 v tomto príklade) a túto bunku s znakom zástupného znaku, napríklad:

Vyhľadávacia spoločnosť (E1 & "*"; A2: A8; 0)

Ako je znázornené na obrázku nižšie, vzorec vráti 2 pozíciu, čo zodpovedá spoločnosti "EAST-AUTO":

Funkčná funkcia v programe Excel - Príklad Excel vyhľadávacie funkcie s zástupným znakom

Ak chcete nahradiť iba jeden znak vo vyhľadávacej hodnote, použite operátor zástupného znaku "?", Napríklad:

Vyhľadávacia spoločnosť ("B? Jacker"; A2: A8; 0)

Vyššie uvedený vzor bude zodpovedať názvu "Baker" a vráti svoju relatívnu polohu, ktorá sa rovná 5.

Vyhľadávanie vyhľadávania registra

Ako sa uvádza na začiatku tohto článku, \\ t funkcia vyhľadávania programu Excel Nerozlišuje symboly horného a dolného registra. Vytvorenie zhody vzorca s registrom, používaním Výklad V kombinácii s funkciou zhody, ktorá presne porovnáva bunky, vrátane registra symbolov.

Citlivosť na vzorec vzorca, ktorý zodpovedá údajom v programe Excel, je nasledovná:

Vyhľadať spoločnosť (pravda, zhoda (platen_station; zobrazenie_massive); 0)

Logika tohto vzorca je nasledovná:

Funkcia zhody porovnáva hodnotu vyhľadávania s každým prvkom pohľadu poľa. Ak sú porovnané bunky presne rovnaké, funkcia vráti pravdu alebo nepravdivé inak.
Okrem toho funkcia searchovej rady porovnáva pravdu (ktorá je jej želaná) s každou hodnotou v poli, vrátenie zhody a vráti pozíciu prvej náhodnosti

PoznámkaJe to masifový vzorec, ktorý vyžaduje CTRL + SHIFT + ENTER Tlač pre správne spracovanie.

Nech je vyhľadávacia hodnota v bunke E1 a vyhľadávacie pole je A2: A8, potom vzorec vyzerá takto:

Vyhľadať spoločnosť (pravda, zhodná (A2: A8; E1); 0)

Nasledujúci obrázok vzorec vyhľadávacej rady Berúc do úvahy register v programe Excel:

Excel Search Funkcia - príklad funkcie vyhľadávania programu Excel

Porovnať 2 stĺpce na náhode a rozdiely (EAN a hľadanie)

Kontrola dvoch zoznamov zhody zhody a rozdielov - jeden z najbežnejších úloh v programe Excel a to možno urobiť rôzne cesty. Používanie typu und / Hľadať je jedným z nich:

Ak (udružená (prvá hodnota v zozname 1; zoznam 2; 0)); "Nie v zozname 1"; ")

Pre ľubovoľnú hodnotu zo zoznamu 2, chýba v zozname 1, vzorec vráti "Nie v zozname 1". A to je to, ako sa to stane:

Funkcia vyhľadávania vykonáva vyhľadávanie hodnoty zo zoznamu 1 v zozname 2. Ak sa nájde hodnota, vráti relatívnu polohu, inak sa chyba # n / d.
Funkcia EFD v programe Excel vykonáva len jednu vec: kontroluje prítomnosť chýb # h / d (čo znamená "nie je k dispozícii"). Ak je zadaná hodnota chyba # n / d, funkcia vráti pravdu, inak - lož. V našom prípade pravda znamená, že hodnota zo zoznamu 1 sa nenašla v zozname 2 (vyhľadávacia spoločnosť vráti chybu # N / E).
Vzhľadom k tomu, že pre ostatných používateľov bude ťažké interpretovať výsledky vo formáte pravdy / lží, potom budeme používať funkciu, ak sa zobrazí text "Nie v zozname 1" pre hodnoty, ktoré nie sú uvedené 1.

Predpokladajme, že musíme porovnať hodnoty v stĺpci B s hodnotami v stĺpci A, vzorec bude mať nasledujúci formulár (kde B2 je najviac horná bunka):

Ak (und (B2; A: A; 0)); "Nie v zozname 1"; "")

Ako si pamätáte, samotná funkcia vyhľadávania programu Excel neberie do úvahy register. Aby ste ho odlíšili medzi registrom, vložte funkciu zhody v argumente zobrazenej BY_MASSIVE a nezabudnite stlačiť kláves Ctrl + Shift + ENTER na dokončenie tohto vzorca poľa:

Ak (und (zámok (pravda; zhoduje (A: A; B2); 0)); "Nie v zozname 1"; ")

Nasledujúci obrázok ukazuje ako vzorce v akcii:

Excel Search Funkcia - porovnanie 2 stĺpcov na náhoda pomocou EAN a Hľadať

Excel Index a hľadanie

S funkciou Vyhľadávanie programu Excel Sme už oboznámení. Pozrime sa teraz, ako používať kombináciu Index vyhľadávacej rady v programe Excel. Začnime s indexom funkcie Syntax.

Syntax a Index funkcie

Funkcia Index Excel Vráti hodnotu v poli na základe riadkov a stĺpcov, ktoré ste zadali. Index funkcie Syntax je jednoduchý:

Index (pole, číslo servera; [Number_stolbits])

Tu je veľmi jednoduché vysvetlenie každého parametra:

pole je rozsah buniek, z ktorých chcete vrátiť hodnotu.
number_stoke - číslo riadku v poli z ktorého chcete vrátiť hodnotu. Ak je tento parameter vynechaný, vyžaduje sa číslo čísla_stolb.
Číslo skladby je číslo stĺpca v poli, z ktorého chcete vrátiť hodnotu. Ak je tento parameter vynechaný, vyžaduje sa číslo servera.

Ak sa obidva parametre používajú NUMBER_STREUTE A NUMBER_STALL, funkcia indexu vráti hodnotu v bunke na priesečníku zadaného riadku a stĺpca.

Tu je najjednoduchší príklad vzorca, index:

Index (A1: C10; 2; 3)

Vzorec je vyhľadávaný v bunkách z A1 až C10 a vráti bunkovú hodnotu v druhom riadku a treťom stĺpci, ktorý je v C2 bunke.

Veľmi jednoduché, že? Avšak, keď pracujete s reálnymi údajmi, nepravdepodobné, že by ste vedeli, aké riadky a stĺpce potrebujete, takže potrebujete pomoc Výklad.

Excel Index Excel

Teraz, keď poznáte syntax a základy týchto dvoch funkcií, pravdepodobne ste už vyvinuli pochopenie toho, ako Excel Search Index.

Funkcia vyhľadávacej rady Definuje relatívnu polohu vyhľadávacej hodnoty v špecifikovanom rozsahu buniek. ALE index funkcie Berie toto číslo a vráti hodnotu príslušnej bunke.

Kombinácia Index Excel môže byť zastúpený týmto spôsobom:

Index (stĺpec pre návratovú hodnotu, vyhľadávacia doska (vyhľadávacia hodnota, vyhľadávací stĺpec, 0))

Pre lepšie porozumenie zvážte vizuálny príklad. Predpokladajme, že máme zoznam krajín s populáciou, ako je toto:

Excel vyhľadávacie funkcie v programe Excel - Source Data for Formula Index Hľadať Excel

Nájdeme populáciu, napríklad Rusko pomocou nasledujúceho index vzorca vyhľadávania:

Index ($ C $ 2: $ C $ 11; Hľadať spoločnosť ("Rusko", $ 2: $ B $ 11; 0))

Pozrime sa teraz na to, čo každá zložka tohto vzorca skutočne vykonáva:

Vyhľadávanie funkcie vyhľadávania vykonáva vyhľadávanie hodnoty "Rusko" v stĺpci B, presnejšie v bunkách B2: B11 a vráti číslo 7, pretože Rusko je na siedmom mieste v zozname.
Funkcia indexu berie "7" v druhom parametri (Number_Name), ktorý označuje, ktorý riadok chcete vrátiť hodnotu a zmení sa na jednoduchý vzorec \u003d index ($ C $ 2: $ C $ 11, 7). To znamená, že vzorec vytvára vyhľadávanie v C2-C11 bunkách a vráti bunkovú hodnotu v siedmom reťazci, to znamená, že C8 bunka, pretože začneme počítanie z druhej línie.

A tu je výsledok, ktorý sa dostaneme do programu Excel:

Excel vyhľadávacie funkcie v Excel Index Formula

Poznámka! Počet riadkov a stĺpcov v indexe poľa musia spĺňať hodnoty v parametroch NUMBER_STROOKE A / ALEBO NUMBER_STROLBABKU FUNKCIU funkcie vyhľadávacej rady. V opačnom prípade vzorec vráti nesprávny výsledok.