V tomto článku se naučíme Jak používat funkci OFFSET v Excelu.
Co je Funkce OFFSET?
Offsetová funkce je funkce pro získávání informací. Pokud máte obrovskou tabulku obsahující určitá data a chcete z ní data načíst, nejlépe to provedete tak, že do tabulky nebo jiného listu ve stejném sešitu vložíte ofsetový vzorec pro tabulku. Jakmile tedy vytvoříte tabulku načítání ofsetů, stačí zadat data například „měsíc“ a z této tabulky získáte „tržby“ nebo „příjem“ daného měsíce.
Procházení tabulkou je další možností, ale zvažovali byste tuto možnost, pokud máte listy a listy dat, které obsahují tabulku dat 1000 sloupců? Zde je offset kombinován s dalšími funkcemi.
Koncept kontingenční tabulky pochází z offsetu. Kontingenční tabulka je obecná tabulka a z této konkrétní kombinace dat a grafů se získává podle potřeby. Stejným způsobem lze také vytvářet dynamické tabulky. Pro tyto tabulky budete muset vytvořit vzorec ofsetu aplikace Excel
Syntaxe OFFSET
| = OFFSET (reference, řádky, sloupce, [výška], [šířka]) |
Řádky - požádáte Excel, aby přesunul počet řádků, aby získal informace. V tomto případě se musí pohybovat po celém stole, a tak jednoduše ponechte prázdné místo označené čárkou (,). V opačném případě pro přesunutí sloupce vpřed zadejte hodnotu jako 1 a pro přesun sloupce před zadejte hodnotu -1.
Sloupce - to má řídit funkci na počet sloupců. Systém hodnot je stejný jako pro řádek.
Výška - výška stolu, v tomto případě A11.
Šířka - šířka stolu, v tomto případě D11.
Poté, co nastavíte funkci ofsetu, je spuštění zkušební verze důležité pro odstranění všech chyb.
Vezměme si tabulku, která obsahuje jméno, e -mailovou adresu, datum narození a věk. Tabulka začíná buňkou A1, která obsahuje nadpis „Název“. Data běží řekněme A11. A řádky běží až do D1. Celá tabulka běží A1: D11. Chcete systém načítání, který vám zadá jméno, když zadáte ID e -mailu, nebo ID e -mailu, když zadáte jméno spolu s dalšími podrobnostmi. Vzorec vytvoříte splněním 5 argumentů.
Referenční bod je buňka, ze které by mělo vyhledávání začít. V tomto případě by to mělo být A2. To je obecný standard.
Příklad:
Například uvedete odkaz na B4 a offsetový řádek 0 a offsetový sloupec 1, pak bude vrácena hodnota z C4. Matoucí? Uveďme několik dalších příkladů. Poslední příklad funkce OFFSET říká, jak dynamicky SUMOVAT.
Funkce OFFSET pro získání hodnoty zprava a zleva od buňky
Máme tu tabulku.
Nyní, pokud chci získat hodnotu od 2 buňky doprava po B2 (což znamená D2). Napíšu tento vzorec OFFSET:
| = OFFSET (B2,0,2) |
Funkce OFFSET přesune 2 buňky doprava od buňky B4 a vrátí její hodnotu. Viz obrázek níže
Pokud chci získat hodnotu z 1 buňky vlevo na B2 (což znamená A2). Napíšu tento vzorec OFFSET:
| = OFFSET (B2,0, -1) |
Znaménko minus (-) označuje posun posunutý opačně.
Funkce OFFSET pro získání hodnoty zespodu a shora od buňky
Takže znovu ve výše uvedené tabulce, pokud chci získat hodnotu od 2 buněk belove po B3 (což znamená B5). Napíšu tento vzorec OFFSET:
| = OFFSET (B2,2,0) |
Pokud chci získat hodnotu z 1 buňky vlevo na B2 (což znamená A2). Napíšu tento vzorec OFFSET:
| = OFFSET (B2, -2,0) |
- Profesionální tip: Považujte OFFSET za ukazatel grafu, kde Reference je počátek a řádek a sloupec jsou osy x a y.
Funkce OFFSET k dynamickému součtu rozsahu
Podívejme se na tento příklad.
Ve výše uvedené tabulce Celkový řádek na konci tabulky. Časem budete do této tabulky přidávat řádky. Poté budete muset ve vzorci změnit rozsah součtů. Zde můžeme využít funkci OFFSET k dynamickému součtu. Aktuálně v buňce C11 máme = SUM (C2: C10). Nahraďte to níže uvedeným vzorcem.
| = SUM (C2: OFFSET (C11, -1,0)) |
Tento vzorec pouze vezme první datový řádek a poté sečte rozsah nad řádkem celkem.
Offset pro získání pole
Funkce OFFSET má dva volitelné argumenty, [výška] a [šířka]. Neberte je jako samozřejmost. Když je k dispozici funkce OFFSET [argumenty [výška] a [šířka], vrátí dvojrozměrné pole. Pokud je jako argument předán pouze jeden z nich, vrátí se na dimenzionální pole hodnot.
Pokud do libovolné buňky zadáte tento vzorec:
| = SUM (OFFSET (C1,1,0,9,3)) |
Bude součet hodnot v rozsahu od C2 do 9 řádků níže a 3 sloupců vpravo.
OFFSET (C1,1,0,9,3): To se převede na {8,8,7; 6,1,2; 8,5,8; 5,1,5; 8,3,5; 8,3,9; 8,9,2; 3,5 , 4; 6,6,5}.
Na závěr je tedy offset velmi užitečnou funkcí Excelu, která vám může pomoci vyřešit mnoho zaškrcených vláken. Dejte mi vědět, jak ve svém vzorci používáte funkci OFFSET a kde vám to nejvíce pomohlo.
Funkce vyhledávání je stejná jako offset, ale využívá funkce jako Match, Counta a IF k práci s dynamickou tabulkou.
Použití ofsetu v Excelu vyžaduje zručnost a dobrou znalost funkcí Excelu a jejich vzájemného působení. Vytáhnout to bez chyby je největší překážka.
Zde jsou všechny pozorovací poznámky týkající se používání vzorce.
Poznámky:
- Tento vzorec funguje jak s textem, tak s čísly.
- Abyste získali bezchybné informace, je třeba splnit pět argumentů ofsetu. Pokud napíšete nesprávný vzorec, dojde k chybě Ref.
Doufám, že jste pochopili, co je funkce Excel Offset a jak ji používat v aplikaci Excel. Prozkoumejte další články o vyhledávání a shodách hodnot v Excelu pomocí vzorců zde. Pokud se vám naše blogy líbily, sdílejte je se svými přáteli na Facebooku. A také nás můžete sledovat na Twitteru a Facebooku. Rádi bychom od vás slyšeli, dejte nám vědět, jak můžeme zlepšit, doplnit nebo inovovat naši práci a zlepšit ji pro vás. Napište nám na e -mail.
Funkce VLOOKUP pro výpočet známky v aplikaci Excel : Pro výpočet známek nejsou IF a IFS jediné funkce, které můžete použít. VLOOKUP je pro takové podmíněné výpočty efektivnější a dynamičtější. Pro výpočet známek pomocí VLOOKUP můžeme použít tento vzorec.
17 Věcí o VYHLEDÁVÁNÍ Excelu : VLOOKUP se nejčastěji používá k načítání odpovídajících hodnot, ale VLOOKUP dokáže mnohem více. Zde je 17 věcí o VLOOKUP, které byste měli vědět, jak je efektivně využívat.
VYHLEDEJTE první text ze seznamu v aplikaci Excel : Funkce VLOOKUP funguje dobře se zástupnými znaky. Můžeme to použít k extrahování první textové hodnoty z daného seznamu v Excelu. Zde je obecný vzorec.
LOOKUP datum s poslední hodnotou v seznamu : K načtení data, které obsahuje poslední hodnotu, použijeme funkci LOOKUP. Tato funkce zkontroluje buňku, která obsahuje poslední hodnotu ve vektoru, a poté použije tento odkaz k vrácení data.
Jak načíst nejnovější cenu v aplikaci Excel : Je běžné aktualizovat ceny v jakémkoli podnikání a použití nejnovějších cen pro jakýkoli nákup nebo prodej je nutností. K získání nejnovější ceny ze seznamu v aplikaci Excel používáme funkci LOOKUP. Funkce LOOKUP načte nejnovější cenu.
Oblíbené články:
50 Zkratka aplikace Excel pro zvýšení produktivity : Získejte rychleji svůj úkol. Těchto 50 klávesových zkratek vám umožní pracovat v Excelu ještě rychleji.
Jak používat funkci IF v aplikaci Excel : Příkaz IF v aplikaci Excel zkontroluje podmínku a vrátí konkrétní hodnotu, pokud je podmínka PRAVDA, nebo vrátí jinou konkrétní hodnotu, pokud NEPRAVDA.
Jak používat funkci VLOOKUP v Excelu : Toto je jedna z nejpoužívanějších a nejoblíbenějších funkcí Excelu, která se používá k vyhledávání hodnot z různých rozsahů a listů.
Jak používat funkci COUNTIF v aplikaci Excel : Pomocí této úžasné funkce spočítejte hodnoty s podmínkami. K počítání konkrétních hodnot nemusíte data filtrovat. Funkce Countif je nezbytná pro přípravu řídicího panelu.
Jak používat funkci SUMIF v aplikaci Excel : Toto je další základní funkce řídicího panelu. To vám pomůže shrnout hodnoty za konkrétních podmínek.