Jak w Excelu cofać się o zmienną ilość kolumn?
Pobieranie wartości z komórki oddalonej o wybraną ilość kolumn — porada #46
Miałem potrzebę pobierać wartość z wcześniejszych kolumn. Tylko ilość kolumn o które się przesuwałem była zmienna. Załóżmy, że jednego dnia chciałem, żeby była pobierana wartość z kolumny sąsiadującej, drugiego z 2 kolumny po lewej, a trzeciego z 4 kolumny na prawo, albo nawet więcej.
Dane te były wprowadzana, jako część składników do tabeli, która rozrastała się w prawą stronę. Czyli dodatkowo chciałem stworzyć taką formułę, którą będzie można kopiować i będzie dalej działała.
Funkcja INDEKS
Najprostszy sposób jaki znalazłem wykorzystywał funkcję INDEKS.
Funkcja INDEKS zwraca wybraną pozycję (wartość komórki) z zaznaczonego obszaru (wprowadzanego jako pierwszą zmienną do funkcji).
W filmie podany jest przykład:
INDEKS($B$4:$E$7;2;3)
Excel dla takich parametrów zwróci wartość przechowywaną w 2 wierszu i 3 kolumnie wybranego obszaru ($B$4:$E$7), czyli wartość komórki D5.
Co wybiera funkcja INDEKS
Przesuwanie o wybraną ilość kolumn
Jak to wykorzystać do pobierania wartości z komórek przesuniętych o zmienną ilość kolumn? Przede wszystkim potrzebowałem zaznaczyć odpowiedni obszar. Ten wprowadzony jako pierwszy miał jedną wadę, nie zmieniał się wraz z przechodzeniem na prawo w arkuszu Excela. dlatego usunąłem znak $ przy E, czyli przykładowy obszar wyglądał tak $B$4:E$7, dzięki temu zawsze zaczynałem od 1 komórki ustawionej bezwzględnie, a przesuwałem kolumny tabeli (wierszy nie) wraz z przesuwaniem się na prawo.
Następnie trzeba było dopasować odpowiedni wiersz. Ponieważ pierwszym wierszem dla wybranego obszaru jest wiersz w arkuszu oznaczony numerem 4 trzeba było to odpowiednio obliczyć. Zastosowałem funkcję WIERSZ. Zwraca ona numer wiersza komórki, do której się odwołujesz. Odwoływałem się względnie do komórki, w której była zapisywana formuła. Następnie trzeba było odjąć ilość wierszy w arkuszu nad zaznaczonym obszarem. Dlatego odwoływałem się do komórki o 1 wiersz wyżej nad górną granicą obszaru. Na przykładzie wyglądało to tak:
WIERSZ(F4)-WIERSZ($F$3)
pierwszy wiersz się zmienia wraz z budowaniem tabeli w dół i prawo, a wiersz nad tabelą ma się nie przesuwać, żeby zawsze była odejmowana jednakowa wartość.
Teraz został element przesuwania się o wybraną ilość kolumn. Nie można w funkcji INDEKS wpisać po prostu ilości kolumn, o które chce się cofnąć, ponieważ funkcja INDEKS zwraca konkretny numer kolumny od początku, czyli trzeba było odjąć tą wartość od całej szerokości obszaru. Uzyskałem ją dzięki funkcji LICZBA.KOLUMN. Jednak po odjęciu okazało się, że formuła zwraca mi wartość z kolumny o 1 wcześniej niż chciałem. Mogłem to rozwiązać na 2 sposoby, albo uwzględnić w obszarze kolumnę komórki, w której wpisuję formułę, albo dodać 1. Wolałem mieć mniej składników sumy.
Ewentualnie jeśli chcesz skrócić długość ten formuły, możesz powiększyć obszar, żeby zaczynał się od 1 wiersza arkusza, wtedy nie trzeba odejmować numeru wiersza tuż nad obszarem. Wtedy formuła wygląda tak:
INDEKS($B$1:F$7;WIERSZ(F4);LICZBA.KOLUMN($B$1:F$7)-$F10)
Końcowa wersja formuły odwołująca się do różnych kolumn
P.S.
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.
Bezpośredni odnośnik do filmu na youtube — Pobieranie wartości z komórki oddalonej o wybraną ilość kolumn — porada #46
