Czasami chcemy skopiować naprzemienne komórki z zakresu lub co trzecią wartość lub co czwartą wartość i tak dalej. We wszystkich tych przypadkach ręczne kopiowanie będzie wyczerpującym zadaniem. Więc jakie jest rozwiązanie? Jak skopiować wartość z każdego n-tego wiersza? Czy jest na to formuła?
Tak jest. Możemy użyć funkcji OFFSET programu Excel, aby uzyskać wartości z n-tych wierszy. Jak? Zobaczmy.
=PRZESUNIĘCIE(Pierwsza_komórka_na_liście,(WIERSZ(A1)*n)-1,0)
First_Cell_in_Range: Jest to bezwzględne odwołanie do pierwszej komórki w zakresie listy, z którego chcesz skopiować. Na przykład, jeśli Twoja lista jest w A5:A100, to First_Cell_in_Range jest $5. Nie dołączaj nagłówka kolumny.
n: jest to liczba, której otrzymasz wartość wielokrotności. Na przykład, jeśli chcesz uzyskać co drugą wartość z listy, to N wynosi 2. Dla każdej trzeciej wartości jest to 3 i tak dalej.
WIERSZ(A1): to dla uzyskania wielokrotności N.
Zobaczmy teraz przykład, a następnie wyjaśnię, jak to działa.
Przykład Kopiuj co drugą wartość z listy w programie Excel
Tutaj mamy listę przedmiotów. Co drugą wartość z listy musimy skopiować w nowym zakresie.
Więc jak to robimy?
Ponieważ na liście mamy 11 pozycji, otrzymamy pięć alternatywnych pozycji (2, 4, 5, 6, 10).
Napisz poniższą formułę w E4 i skopiuj komórki.
=PRZESUNIĘCIE($B$4(WIERSZ(A1)*2)-1,0)
Tutaj $ B $ 4 jest pierwszą komórką w zakresie listy, a ponieważ chcemy skopiować co drugą (alternatywną) wartość z listy, mamy nasze N jako 2.
Jeśli chcesz skopiować co trzeci element, użyj tej formuły.
=PRZESUNIĘCIE($B$4(WIERSZ(A1)*3)-1,0)
Za co czwarte:
=PRZESUNIĘCIE($B$4(WIERSZ(A1)*4)-1,0)
I tak dalej. Możesz skopiować dowolną N-tą wartość z za pomocą tej formuły programu Excel.
Jak to działa
Formuła realizuje to zadanie za pomocą dwóch funkcji programu Excel, PRZESUNIĘCIE i WIERSZ.
Funkcja PRZESUNIĘCIE zwraca wartość z przesunięcia wiersza i kolumny z danej komórki.
Funkcja WIERSZ zwraca numer wiersza danego odwołania.
Zaczynając od środka, WIERSZ(A1) zwróci 1. Ponieważ A1 należy do pierwszego rzędu.
(WIERSZ(A1)*2 stanie się 2.
(WIERSZ(A1)*2)-1, stanie się (1*2)-1=1.
Gdy skopiujemy formułę, odwołanie w ROW zmieni się na A2, A3, A4 i tak dalej. Dla A2 otrzymamy wartość końcową (2*2)-1=3. Dla A3 otrzymamy (3*3)-1=5 i tak dalej.
Teraz nasza formuła w E4 została rozwiązana do = OFFSET ($ B $ 4,1,0). Spowoduje to zwrócenie wartości 1 wiersz poniżej z B4, która jest drugą wartością z B4.
W E4 formuła będzie miała postać = PRZESUNIĘCIE ($ B 4, 3,0), co jest czwartą wartością z B4. Podobnie otrzymujemy wszystkie koszty przemienne.
Teraz wiesz, jak skopiować każdą N-tą wartość z listy w programie Excel. To jest technika, którą znam. Jeśli znasz inną sztuczkę, aby skopiować n-tą komórkę z zakresu, możesz podzielić się nimi w sekcji komentarzy poniżej. Jeśli masz pytania dotyczące tej lub innej funkcji programu Excel 2019, 2016, 2013 i 2010, skorzystaj z sekcji komentarzy.
Jak pobrać najnowszą cenę w Excelu?
Pobieranie pierwszej wartości z listy, która jest większa/mniejsza niż określona wartość
Jak pobrać cały wiersz o dopasowanej wartości
Popularne artykuły:
Funkcja WYSZUKAJ.PIONOWO w programie Excel
LICZ.JEŻELI w Excelu 2016
Jak korzystać z funkcji SUMIF w programie Excel?