Stiahnite si ukážkový zošit
Tento návod ukazuje, ako používať Funkcia Excel OFFSET v Exceli na vytvorenie referenčného posunu z počiatočnej bunky.
OFFSET Prehľad funkcií
Funkcia OFFSET sa začína definovanou referenciou bunky a vracia referencii bunky špecifikovaný počet riadkov a stĺpcov posunutý oproti pôvodnému rozhodnutiu. Referencie môžu byť jedna bunka alebo rozsah buniek. Offset vám tiež umožňuje zmeniť veľkosť odkazu na daný počet riadkov/stĺpcov.
(Všimnite si, ako sa zobrazujú vstupy vzorcov)
Syntax a vstupy funkcie IFERROR:
| 1 | = OFFSET (referencia, riadky, stĺpce, výška, šírka) |
odkaz - Počiatočná referencia bunky, od ktorej chcete odsadiť.
riadky - Počet riadkov, ktoré sa majú odsadiť.
cols - Počet stĺpcov na kompenzáciu.
výška - VOLITEĽNÉ: Upravte počet riadkov v referencii.
šírka - VOLITEĽNÉ: Upravte počet stĺpcov v referencii.
Čo je to funkcia OFFSET?
Funkcia OFFSET je jednou z výkonnejších tabuľkových funkcií, pretože môže byť veľmi univerzálna v tom, čo vytvára. Užívateľovi to dáva možnosť definovať bunku alebo rozsah v rôznych polohách a veľkostiach.
UPOZORNENIE: Funkcia OFFSET je jednou z volatilných funkcií. Väčšinu času, keď pracujete so svojou tabuľkou, počítač prepočíta vzorec iba vtedy, ak vstupy zmenili svoje hodnoty. Prchavá funkcia sa však prepočítava každý čas, kedy vykonáte zmenu v akejkoľvek bunke. Je potrebné postupovať opatrne, aby ste zaistili, že nespôsobíte veľký čas prepočtu v dôsledku nadmerného používania volatilnej funkcie alebo veľkého počtu buniek závislých od výsledku volatilnej funkcie.
Príklady základných riadkov
Pri každom použití funkcie OFFSET musíte uviesť počiatočný bod alebo ukotvenie. Pozrime sa na túto tabuľku, aby sme tomu porozumeli:
Ako kotviaci bod použijeme „Bob“ v bunke B3. Ak by sme chceli chytiť hodnotu tesne pod (Charlie), povedali by sme, že chceme posunúť riadok o 1. Náš vzorec by vyzeral takto
| 1 | = OFFSET (B3, 1) |
Ak by sme chceli preradiť nahor, bol by to negatívny posun. Môžete na to myslieť, pretože počet riadkov klesá, takže musíme odpočítať. Na získanie vyššie uvedenej hodnoty (Adam) by sme teda písali
| 1 | = OFFSET (B2, -1) |
Príklady základných stĺpcov
Pokračujeme v myšlienke z predchádzajúceho príkladu a do našej tabuľky pridáme ďalší stĺpec.
Ak by sme chceli Boba chytiť učiteľa, mohli by sme použiť vzorec
| 1 | = OFFSET (B2, 0, 1) |
V tomto prípade sme povedali, že chceme odsadiť nulové riadky (aka zostať v rovnakom riadku), ale chceme odsadiť 1 stĺpec. V stĺpcoch kladné číslo znamená posun doprava a záporné číslo vľavo.
OFFSET a ZHODA
Predpokladajme, že ste mali niekoľko stĺpcov údajov a chceli ste dať používateľovi možnosť vybrať si, z ktorého stĺpca má načítať výsledky. Môžete použiť funkciu INDEX alebo môžete použiť OFFSET. Pretože MATCH vráti relatívnu polohu hodnoty, budeme sa musieť uistiť, že kotviaci bod je vľavo od našej prvej možnej hodnoty. Zvážte nasledujúce rozloženie:
V B2 napíšeme tento vzorec:
| 1 | = OFFSET (B2, 0, MATCH (A2, $ C $ 1: $ F $ 1, 0)) |
Zápas bude vyzerať „Február“ v rozmedzí C1: F1 a nájdete ho v 2nd bunka. OFFSET potom posunie 1 stĺpec vpravo od B2 a chytí požadovanú hodnotu 9. Všimnite si, že OFFSET nemá problém použiť rovnakú bunku, ktorá obsahuje vzorec ako kotviaci bod.
POZNÁMKA: Túto techniku je možné použiť ako náhradu za funkciu VLOOKUP alebo HLOOKUP, ak chcete vrátiť hodnotu zľava/nad rozsah vyhľadávania. Dôvodom je, že OFFSET môže vytvárať negatívne offsety.
OFFSET na získanie dosahu
Môžete použiť 4th a 5th argumenty vo funkcii OFFSET na vrátenie rozsahu a nie iba jednej bunky. Predpokladajme, že ste chceli v tejto tabuľke zhrnúť 3 stĺpce.
| 1 | = AVERAGE (OFFSET (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
V F2 sme vybrali meno študenta, pre ktorého chceme načítať jeho priemerné skóre z testov. Na tento účel použijeme vzorec
| 1 | = AVERAGE (OFFSET (A1, MATCH (F2, A2: A5,0), 1,1,3)) |
MATCH hľadá v stĺpci A naše meno a vráti relatívnu pozíciu, ktorá je v našom prípade 3. Pozrime sa, ako sa to vyhodnotí. Najprv pôjde OFFSET dole 3 riadky od A1 a 1 stĺpec po správny z A1. Tým sme zaradení do bunky B3.
| 1 | = AVERAGE (OFFSET (A1, 3, 1, 1, 3)) |
Ďalej zmeníme veľkosť rozsahu. Nový rad bude mať B3 ako ľavú hornú bunku. Bude mať výšku 1 riadok a 3 stĺpce, čo nám poskytne rozsah B4: D4.
| 1 | = AVERAGE (OFFSET (A1,3, 1, 1, 3)) |
Všimnite si toho, že aj keď môžete oprávnene vložiť záporné hodnoty do offsetových argumentov, v argumentoch pre veľkosť môžete použiť iba nezáporné hodnoty.
Na konci, naša funkcia AVERAGE vidí:
| 1 | = PRIEMERNÉ (B4: D4) |
Tak dostaneme naše riešenie 86,67
OFFSET s dynamickým SÚČETOM
Pretože OFFSET slúži na nájdenie referencie, a nie na priamy odkaz na bunku, je veľmi nápomocný pri práci s údajmi, do ktorých boli pridané alebo odstránené riadky. Pozrite sa na nasledujúcu tabuľku s celkom v spodnej časti
| 1 | = SUM (B2: B4) |
Ak by sme tu použili základný vzorec SUM „= SUM (B2: B4)“ a potom vložili nový riadok na pridanie záznamu pre Billa, dostali by sme nesprávnu odpoveď
Namiesto toho sa pozrime na to, ako to vyriešiť z pohľadu Total. Naozaj chceme uchopiť všetko od bunky B2 do bunky tesne nad naším súčtom. Spôsob, akým to môžeme napísať do vzorca, je urobiť posunutie riadka o -1. Preto to používame ako vzorec pre náš celkový súčet v bunke B5:
| 1 | = SUM (B2: OFFSET (B5, -1,0)) |
Tento vzorec robí to, čo sme práve popísali: začnite na B2 a choďte do 1 bunky nad našu celkovú bunku. Môžete vidieť, ako sa po pridaní Billových údajov náš celkový súčet aktualizuje správne.
OFFSET na získanie posledných N položiek
Povedzme, že zaznamenávate mesačné tržby, ale chcete sa pozrieť na posledné 3 mesiace. Namiesto toho, aby ste museli vzorce pri pridávaní nových údajov ručne aktualizovať, aby ste ich mohli naďalej upravovať, môžete použiť funkciu OFFSET s funkciou COUNT.
Už sme ukázali, ako môžete pomocou OFFSETU uchopiť celý rad buniek. Aby sme určili, koľko buniek musíme posunúť, použijeme COUNT, aby sme zistili, koľko čísla sú v stĺpci B. Pozrime sa na našu ukážkovú tabuľku.
| 1 | = SÚČET (OFFSET ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Ak by sme začali na B1 a odsadili 4 riadky (počet čísel v stĺpci B), skončili by sme na konci nášho rozsahu, B5. Pretože však OFFSET nemôže zmeniť veľkosť so zápornou hodnotou, musíme urobiť niekoľko úprav, aby sme skončili v B3. Na to bude stačiť všeobecná rovnica
| 1 | COUNT (…) - N + 1 |
Zoberieme počet celého stĺpca, odpočítame, koľko ich chceme vrátiť (pretože zmeníme veľkosť, aby sme ich chytili), a potom pridáme 1 (pretože v podstate začíname náš offset na pozícii nula).
Tu vidíte, že sme nastavili rozsah na získanie súčtu, priemeru a maxima za posledných N mesiacov. V E1 sme zadali hodnotu 3. V E2 je náš vzorec
| 1 | = SÚČET (OFFSET ($ B $ 1, COUNT (B: B)-$ E $ 1+1,0, $ E $ 1,1)) |
Zvýraznená časť je naša všeobecná rovnica, o ktorej sme práve diskutovali. Nepotrebujeme kompenzovať žiadne stĺpce. Potom zmeníme veľkosť rozsahu na 3 bunky (určené hodnotou v E1) a široký 1 stĺpec. Náš SUM potom vezme tento rozsah a poskytne nám výsledok 1 850 dolárov. Tiež sme ukázali, že priemer maxima z rovnakého rozsahu môžete vypočítať jednoduchým prepnutím vonkajšej funkcie z SUM na čokoľvek, čo si situácia vyžaduje.
OFFSET dynamické validačné zoznamy
Pomocou techniky uvedenej v poslednom príklade môžeme tiež vytvoriť pomenované rozsahy, ktoré je možné použiť pri validácii údajov alebo grafoch. To môže byť užitočné, keď chcete nastaviť tabuľku, ale očakávate, že naše zoznamy/údaje zmenia veľkosť. Povedzme, že náš obchod začína predávať ovocie a v súčasnosti máme 3 možnosti.
Aby bola rozbaľovacia ponuka Overenie údajov, ktorú môžeme použiť inde, definujeme pomenovaný rozsah MyFruit ako
| 1 | = $ A $ 2: OFFSET ($ A $ 1, COUNTA ($ A: $ A) -1, 0) |
Namiesto COUNT používame COUNTA, pretože pracujeme s textovými hodnotami. Z tohto dôvodu však bude naša COUNTA o jednu vyššiu, pretože bude počítať bunku hlavičky v A1 a poskytne hodnotu 4. Ak by sme však posunuli o 4 riadky, skončili by sme v bunke A5, ktorá je prázdna. Aby sme to potom upravili, odpočítame 1.
Teraz, keď máme nastavenie pomenovaného rozsahu, môžeme v bunke C4 nastaviť určité overenie údajov pomocou typu zoznamu so zdrojom:
| 1 | = MyFruit |
Upozorňujeme, že rozbaľovacia ponuka zobrazuje iba naše tri aktuálne položky. Ak potom do nášho zoznamu pridáme ďalšie položky a vrátime sa späť do rozbaľovacieho zoznamu, zoznam zobrazí všetky nové položky bez toho, aby sme museli meniť niektoré zo vzorcov.
Upozornenia pri použití OFFSETU
Ako už bolo spomenuté na začiatku tohto článku, OFFSET je volatilná funkcia. To si nevšimnete, ak ho používate iba v niekoľkých bunkách, ale ak ho začnete používať v stovkách výpočtov a rýchlo si všimnete, že váš počítač strávi znateľné množstvo času prepočítavaním pri každom vykonaní akýchkoľvek zmien. .
Navyše, pretože OFFSET „nepomenuje priamo bunky, na ktoré sa pozerá, je pre ostatných používateľov ťažšie prísť neskôr a v prípade potreby zmeniť vzorce.
Namiesto toho by bolo vhodné použiť tabuľky (zavedené v balíku Office 2007), ktoré umožňujú štrukturálne odkazy. To pomohlo používateľom byť schopní poskytnúť jednu referenciu, ktorá sa automaticky prispôsobila veľkosti pri pridávaní alebo odstraňovaní nových údajov.
Ďalšou možnosťou, ktorú použiť namiesto OFFSET, je výkonná funkcia INDEX. INDEX vám umožňuje vybudovať všetky dynamické rozsahy, ktoré sme videli v tomto článku, bez toho, aby išlo o volatilnú funkciu.
Doplňujúce Poznámky
Funkciu OFFSET použite na vrátenie hodnoty bunky (alebo rozsahu buniek) posunutím daného počtu riadkov a stĺpcov od počiatočnej referencie. Keď hľadáte iba jednu bunku, vzorce OFFSET dosahujú rovnaký účel ako vzorce INDEX pomocou mierne odlišnej techniky. Skutočná sila funkcie OFFSET spočíva v schopnosti vybrať rozsah buniek, ktoré sa majú použiť v inom vzorci.
Pri použití funkcie OFFSET definujete počiatočnú počiatočnú bunku alebo rozsah buniek. Potom určíte počet riadkov a stĺpcov, ktoré sa majú odsadiť od tejto počiatočnej bunky. Môžete tiež zmeniť veľkosť rozsahu; pridať alebo odpočítať riadky alebo stĺpce.
Vráťte sa do zoznamu všetkých funkcií v programe Excel
OFFSET v Tabuľkách Google
Funkcia OFFSET funguje v Tabuľkách Google úplne rovnako ako v programe Excel:
