Někdy chceme zkopírovat střídající se buňky z rozsahu nebo každé třetí hodnoty nebo každé 4. hodnoty atd. Ve všech těchto případech bude ruční kopírování vyčerpávajícím úkolem. Jaké je tedy řešení? Jak zkopírujete hodnotu z každého n -tého řádku? Existuje na to vzorec?
Ano, tam je. K získání hodnot z n -tých řádků můžeme použít funkci OFFSET v Excelu. Jak? Pojďme se na to podívat.
= OFFSET (First_Cell_in_List, (ŘADA (A1)*N.)-1,0)
First_Cell_in_Range: Jedná se o absolutní odkaz na první buňku v rozsahu seznamu, ze kterého chcete kopírovat. Pokud je váš seznam například v formátu A5: A100, pak First_Cell_in_Range je $ A $ 5. Nezahrnujte záhlaví sloupců.
N.: je to číslo, které získáte násobky. Pokud například chcete získat každou 2. hodnotu ze seznamu, pak N je 2. Pro každou 3. hodnotu jsou 3 a tak dále.
ŘADA (A1): to pro získání násobků N.
Nyní se podívejme na příklad a poté vysvětlím, jak to funguje.
Příklad Zkopírujte každou druhou hodnotu ze seznamu v aplikaci Excel
Zde máme seznam položek. Musíme zkopírovat každou druhou hodnotu ze seznamu v novém rozsahu.
Jak to tedy uděláme?
Protože máme na seznamu 11 položek, dostaneme pět alternativních položek (2, 4, 5, 6, 10).
Napište níže uvedený vzorec do E4 a zkopírujte buňky dolů.
= OFFSET ($ B $ 4, (ŘADA (A1)*)2)-1,0)
Zde je $ B $ 4 první buňkou v rozsahu seznamu, a protože chceme zkopírovat každou druhou (alternativní) hodnotu ze seznamu, máme naše N jako 2.
Pokud chcete zkopírovat každou třetí položku, použijte tento vzorec.
= OFFSET ($ B $ 4, (ŘADA (A1)*)3)-1,0)
Za každé 4.
= OFFSET ($ B $ 4, (ŘADA (A1)*)4)-1,0)
A tak dále. Z tohoto excelového vzorce můžete zkopírovat libovolnou N -tu hodnotu.
Jak to funguje
Vzorec splňuje tento úkol pomocí dvou funkcí aplikace Excel, OFFSET a ROW.
Funkce OFFSET vrací hodnotu z posunu řádků a sloupců z dané buňky.
Funkce ROW vrací číslo řádku dané reference.
Počínaje zevnitř se ROW (A1) vrátí 1. Protože A1 patří do 1. řady.
(ŘADA (A1)*2 stane se 2.
(ŘADA (A1)*2)-1, stane se (1*2) -1 = 1.
Když zkopírujeme vzorec, odkaz v ROW se změní jako A2, A3, A4 atd. Pro A2 dostaneme konečnou hodnotu (2*2) -1 = 3. Pro A3 dostaneme (3*3) -1 = 5 a tak dále.
Nyní jsme náš vzorec v E4 vyřešili na = OFFSET ($ B $ 4,1, 0). Tím se vrátí hodnota 1 řádek níže z B4, což je druhá hodnota z B4.
V E4 bude vzorec = OFFSET ($ B $ 4,3,0), což je čtvrtá hodnota z B4. Podobně získáme všechny střídavé náklady.
Nyní víte, jak zkopírovat každou N -tu hodnotu ze seznamu v aplikaci Excel. To je technika, kterou znám. Pokud znáte nějaký jiný trik, jak zkopírovat n -tu buňku z určitého rozsahu, neváhejte se podělit v sekci komentáře níže. Pokud máte nějaké dotazy týkající se této nebo jakékoli jiné funkce aplikace Excel 2019, 2016, 2013 a 2010, použijte sekci komentáře.
Jak načíst nejnovější cenu v aplikaci Excel
Získání první hodnoty v seznamu, který je větší / menší než zadaná hodnota
Jak načíst celý řádek odpovídající hodnoty
Oblíbené články:
Funkce VLOOKUP v Excelu
COUNTIF v aplikaci Excel 2016
Jak používat funkci SUMIF v aplikaci Excel