Niekedy chceme skopírovať striedajúce sa bunky z rozsahu alebo z každej tretej hodnoty alebo z každej 4. hodnoty atď. Vo všetkých týchto prípadoch bude manuálne kopírovanie vyčerpávajúcou úlohou. Aké je teda riešenie? Ako skopírujete hodnotu z každého n -tého riadka? Existuje na to vzorec?
Áno, existuje. Na získanie hodnôt z n -tych riadkov môžeme použiť funkciu OFFSET v Exceli. Ako? Pozrime sa na to.
= OFFSET (First_Cell_in_List, (RIADOK (A1)*N.)-1,0)
First_Cell_in_Range: Je to absolútna referencia na prvú bunku v rozsahu zoznamu, z ktorého chcete kopírovať. Ak je váš zoznam napríklad v formáte A5: A100, potom First_Cell_in_Range je 5 A $. Neuvádzajte nadpisy stĺpcov.
N.: je to číslo, ktoré získate násobnú hodnotu. Ak napríklad chcete získať každú druhú hodnotu zo zoznamu, potom N je 2. Pre každú tretiu hodnotu sú to 3 a podobne.
RIADOK (A1): slúži na získanie násobkov N.
Teraz sa pozrime na príklad a potom vysvetlím, ako to funguje.
Príklad Skopírujte každú druhú hodnotu zo zoznamu v programe Excel
Tu nájdete zoznam položiek. Musíme skopírovať každú druhú hodnotu zo zoznamu v novom rozsahu.
Ako to teda urobíme?
Keďže v zozname máme 11 položiek, dostaneme päť alternatívnych položiek (2, 4, 5, 6, 10).
Napíšte nasledujúci vzorec do E4 a skopírujte bunky.
= OFFSET ($ B $ 4, (RIADOK (A1)*)2)-1,0)
Tu je $ B $ 4 prvá bunka v rozsahu zoznamu, a pretože chceme skopírovať každú druhú (alternatívnu) hodnotu zo zoznamu, máme naše N ako 2.
Ak chcete skopírovať každú tretiu položku, použite tento vzorec.
= OFFSET ($ B $ 4, (RIADOK (A1)*)3)-1,0)
Za každé 4.
= OFFSET ($ B $ 4, (RIADOK (A1)*)4)-1,0)
A tak ďalej. Môžete skopírovať akúkoľvek n -tú hodnotu z a pomocou tohto vzorca programu Excel.
Ako to funguje
Vzorec plní túto úlohu pomocou dvoch funkcií programu Excel, OFFSET a ROW.
Funkcia OFFSET vráti hodnotu z posunu riadkov a stĺpcov z danej bunky.
Funkcia ROW vráti číslo riadku danej referencie.
Začínajúc zvnútra, ROW (A1) vráti 1. Pretože A1 patrí do 1. radu.
(RAD (A1)*2 stane sa 2.
(RIADOK (A1)*2)-1, stane sa (1*2) -1 = 1.
Keď skopírujeme vzorec, referencia v ROW sa zmení ako A2, A3, A4 atď. Pre A2 dostaneme konečnú hodnotu (2*2) -1 = 3. Pre A3 dostaneme (3*3) -1 = 5 a tak ďalej.
Teraz bol náš vzorec v E4 vyriešený na = OFFSET ($ B $ 4,1, 0). Tým sa vráti hodnota 1 riadok nižšie z B4, čo je druhá hodnota z B4.
V E4 bude vzorec = OFFSET ($ B $ 4,3,0), čo je štvrtá hodnota z B4. Podobne dostaneme všetky striedavé náklady.
Teraz viete, ako skopírovať každú N -tú hodnotu zo zoznamu v programe Excel. Toto je technika, ktorú poznám. Ak poznáte iný trik na kopírovanie n -tej bunky z určitého rozsahu, pokojne sa podeľte v sekcii komentárov nižšie. Ak máte akékoľvek otázky týkajúce sa tejto alebo akejkoľvek inej funkcie programu Excel 2019, 2016, 2013 a 2010, použite sekciu komentárov.
Ako získať najnovšiu cenu v programe Excel
Získanie prvej hodnoty v zozname, ktorý je väčší / menší ako zadaná hodnota
Ako načítať celý riadok zodpovedajúcej hodnoty
Populárne články:
Funkcia VLOOKUP v programe Excel
COUNTIF v Exceli 2016
Ako používať funkciu SUMIF v programe Excel
