V tomto článku sa naučíme Ako používať funkciu OFFSET v programe Excel.
Čo je Funkcia OFFSET?
Offsetová funkcia je funkcia získavania informácií. Ak máte veľkú tabuľku obsahujúcu určité údaje a chcete z nej získať údaje, najlepšie to urobíte tak, že do tohto pracovného hárka alebo iného hárka v tom istom zošite vložíte ofsetový vzorec pre tabuľku. Akonáhle teda vytvoríte tabuľku ofsetového vyhľadávania, stačí zadať údaje napríklad „mesiac“ a z tejto tabuľky získate „predaj“ alebo „príjem“ za daný mesiac.
Prechádzanie tabuľkou je ďalšou možnosťou, ale zvážili by ste túto možnosť, ak máte všetky hárky a hárky údajov, ktoré obsahujú tabuľku s údajmi 1 000 stĺpcov? Tu je ofset kombinovaný s inými funkciami.
Koncept kontingenčnej tabuľky pochádza z offsetu. Kontingenčná tabuľka je všeobecná tabuľka a z tejto konkrétnej kombinácie údajov a grafov sa získava podľa potreby. Rovnakým spôsobom je možné vytvárať aj dynamické tabuľky. Pre tieto tabuľky budete musieť vytvoriť vzorec ofsetu programu Excel
Syntax OFFSET
| = OFFSET (referencia, riadky, stĺpce, [výška], [šírka]) |
Riadky - požiadate Excel, aby presunul počet riadkov, aby získal informácie. V tomto prípade sa musí pohybovať po celom stole, a tak nechajte prázdne miesto označené čiarkou (,). V opačnom prípade, ak chcete posunúť stĺpec dopredu, zadajte hodnotu 1 a ak chcete presunúť stĺpec predtým, zadajte hodnotu -1.
Stĺpce - toto má viesť funkciu k počtu stĺpcov. Systém hodnôt je rovnaký ako pre riadok.
Výška - výška stola, v tomto prípade A11.
Šírka - šírka stola, v tomto prípade D11.
Po nastavení funkcie posunu je spustenie skúšobnej verzie dôležité pre odstránenie všetkých chýb.
Zoberme si tabuľku, ktorá obsahuje meno, e -mailovú adresu, dátum narodenia a vek. Tabuľka začína na bunke A1 obsahujúcej nadpis „Názov“. Dáta bežia na povedzme A11. A rady bežia do D1. Celá tabuľka beží vo formáte A1: D11. Chcete vyhľadávací systém, ktorý vám zadá meno pri zadaní ID e -mailu alebo ID e -mailu pri zadaní mena spolu s ďalšími podrobnosťami. Vzorec vytvoríte splnením 5 argumentov.
Referenčný bod je bunka, z ktorej by malo začať vyhľadávanie. V tomto prípade by to mala byť A2. To je všeobecný štandard.
Príklad:
Napríklad uvediete referenciu na B4 a ofsetový riadok 0 a ofsetový stĺpec 1, potom sa vráti hodnota z C4. Mätúce? Ukážme ešte niekoľko príkladov. Posledný príklad funkcie OFFSET hovorí, ako dynamicky SUMAŤ.
OFFSET Funkcia na získanie hodnoty z pravej a ľavej strany bunky
Máme tento stôl.
Teraz, ak chcem získať hodnotu z 2 buniek vpravo na B2 (čo znamená D2). Napíšem tento vzorec OFFSET:
| = OFFSET (B2,0,2) |
Funkcia OFFSET presunie 2 bunky vpravo od bunky B4 a vráti jej hodnotu. Viď obrázok nižšie
Ak chcem získať hodnotu z 1 bunky vľavo na B2 (čo znamená A2). Napíšem tento vzorec OFFSET:
| = OFFSET (B2,0, -1) |
Znamienko mínus (-) označuje posun posunu opačne.
OFFSET Funkcia na získanie hodnoty zospodu a zhora od bunky
Takže opäť vo vyššie uvedenej tabuľke, ak chcem získať hodnotu od 2 buniek od lásky k B3 (čo znamená B5). Napíšem tento vzorec OFFSET:
| = OFFSET (B2,2,0) |
Ak chcem získať hodnotu z 1 bunky vľavo na B2 (čo znamená A2). Napíšem tento vzorec OFFSET:
| = OFFSET (B2, -2,0) |
- Pro tip: S OFFSETOM zaobchádzajte ako s ukazovateľom grafu, kde Referencia je počiatok a riadok a stĺpec sú osami x a y.
Funkcia OFFSET na dynamické sčítanie rozsahu
Pozrime sa na tento príklad.
Vo vyššie uvedenej tabuľke Riadok celkom na konci tabuľky. Časom budete do tejto tabuľky pridávať riadky. Potom budete musieť zmeniť rozsah súčtov vo vzorci. Tu môžeme využiť funkciu OFFSET na dynamické súčty. Aktuálne sa nachádzame v bunke C11 = SÚČET (C2: C10). Nahraďte to nižšie uvedeným vzorcom.
| = SUM (C2: OFFSET (C11, -1,0)) |
Tento vzorec vezme iba prvý riadok údajov a potom sčíta rozsah nad riadkom súčtu.
Ofset pre získanie poľa
Funkcia OFFSET má dva voliteľné argumenty, [výška] a [šírka]. Neberte ich ako samozrejmosť. Keď je k dispozícii funkcia OFFSET [argumenty [výška] a [šírka], vráti dvojrozmerné pole. Ak je ako argument predložený iba jeden z nich, vráti sa v dimenzionálnom poli hodnôt.
Ak do ľubovoľnej bunky zadáte tento vzorec:
| = SÚČET (OFFSET (C1,1,0,9,3)) |
Sčíta hodnoty v rozsahu od C2 do 9 riadkov nižšie a 3 stĺpce vpravo.
OFFSET (C1,1,0,9,3): To bude preložené do {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áver teda môžem povedať, že offset je veľmi užitočná funkcia Excelu, ktorá vám môže pomôcť vyriešiť mnohé zaškrtené vlákna. Dajte mi vedieť, ako vo svojom vzorci používate funkciu OFFSET a kde vám to najviac pomohlo.
Funkcia vyhľadávania je rovnaká ako offset, ale používa funkcie ako Match, Counta a IF na prácu s dynamickou tabuľkou.
Použitie ofsetu v Exceli vyžaduje zručnosť a dobré znalosti funkcií Excelu a ich vzájomnej spolupráce. Stiahnutie bez chyby je najväčšia prekážka.
Tu sú všetky pozorovacie poznámky týkajúce sa použitia vzorca.
Poznámky:
- Tento vzorec funguje s textom aj s číslami.
- Na získanie bezchybných informácií je potrebné splniť päť argumentov ofsetu. Ak napíšete nesprávny vzorec, zobrazí sa chyba Ref.
Dúfam, že ste pochopili, čo je funkcia ofsetu programu Excel a ako ju používať v programe Excel. Tu nájdete ďalšie články o vyhľadávaniach a zhodách hodnôt v Exceli pomocou vzorcov. Ak sa vám páčili naše blogy, zdieľajte ich so svojimi priateľmi na Facebooku. A tiež nás môžete sledovať na Twitteri a Facebooku. Radi by sme vás počuli, dajte nám vedieť, ako môžeme zlepšiť, doplniť alebo inovovať našu prácu a zlepšiť ju pre vás. Napíšte nám na emailovú stránku.
Funkcia VLOOKUP na výpočet známky v programe Excel : Na výpočet stupňov IF a IFS nie sú jediné funkcie, ktoré môžete použiť. VLOOKUP je pre takéto podmienené výpočty efektívnejší a dynamickejší. Na výpočet známok pomocou VLOOKUP môžeme použiť tento vzorec.
17 vecí o VLOOKUPE v Exceli : VLOOKUP sa najčastejšie používa na získavanie zhodných hodnôt, ale VLOOKUP dokáže oveľa viac. Tu je 17 vecí o VLOOKUP, ktoré by ste mali vedieť efektívne používať.
VYHĽADAJTE prvý text zo zoznamu v programe Excel : Funkcia VLOOKUP funguje dobre so zástupnými znakmi. Môžeme to použiť na extrahovanie prvej textovej hodnoty z daného zoznamu v programe Excel. Tu je generický vzorec.
HĽADAJ dátum s poslednou hodnotou v zozname : Na získanie dátumu, ktorý obsahuje poslednú hodnotu, použijeme funkciu LOOKUP. Táto funkcia vyhľadá bunku, ktorá obsahuje poslednú hodnotu vo vektore, a potom použije tento odkaz na vrátenie dátumu.
Ako získať najnovšiu cenu v programe Excel : Aktualizácia cien je bežná v každom podniku a používanie najnovších cien pri každom nákupe alebo predaji je nevyhnutné. Na získanie najnovšej ceny zo zoznamu v programe Excel používame funkciu LOOKUP. Funkcia LOOKUP načítava najnovšiu cenu.
Populárne články:
50 Skratka pre Excel, ktorá zvýši vašu produktivitu : Zrýchlite sa vo svojej úlohe. Týchto 50 skratiek vám umožní pracovať ešte rýchlejšie v Exceli.
Ako používať funkciu IF v programe Excel : Príkaz IF v programe Excel skontroluje podmienku a vráti konkrétnu hodnotu, ak je podmienka PRAVDA, alebo vráti inú konkrétnu hodnotu, ak je NEPRAVDA.
Ako používať funkciu VLOOKUP v programe Excel : Toto je jedna z najpoužívanejších a najobľúbenejších funkcií programu Excel, ktorá sa používa na vyhľadávanie hodnôt z rôznych rozsahov a hárkov.
Ako používať funkciu COUNTIF v programe Excel : Počítajte hodnoty s podmienkami pomocou tejto úžasnej funkcie. Na počítanie konkrétnych hodnôt nie je potrebné filtrovať údaje. Funkcia Countif je potrebná na prípravu palubnej dosky.
Ako používať funkciu SUMIF v programe Excel : Toto je ďalšia zásadná funkcia palubnej dosky. To vám pomôže zhrnúť hodnoty za konkrétnych podmienok.