GoldenLine
Sign in
Grzegorz Gorczynski

Grzegorz Gorczynski programista/operator
CNC

Temat: Zwracanie ostatnich wyników z kolumny

Witam,

Mam problem ze zwracaniem ostatnich danych z tabeli. Podam przykład:
W tabeli, w pierwszej kolumnie A2:A30 mam losowo rozmieszczone 3 litery A, B, C.
W drugiej kolumnie B2:B30 mam losowo rozmieszczone liczby ( np. 0-50).
Chciałbym aby po wpisaniu w komórkę np. D1 danej litery, excel zwrócił wynik w wierszu D2:D4 ostatnie 3 cyfry w kolumnie przypisane do danej litery. Chciałbym również, aby formuła działała automatycznie w przypadku dopisywania danych do tabeli.

Z góry dziękuję za pomoc
Link do wypowiedziLink
Zbigniew Szyszkowski

Zbigniew Szyszkowski sprzątacz,
Ministerstwo
Rolnictwa i Rozwoju
Wsi

Temat: Zwracanie ostatnich wyników z kolumny

Można to zrobić na wiele sposobów. W sumie najłatwiejszym byłaby Tabela Przestawna. No ale zajmijmy się formułką.
W D2 wpisz
=INDEKS(Tabela2[liczby];MAX.K(JEŻELI(Tabela2[litery]=$D$1;WIERSZ(Tabela2[litery])-WIERSZ($A$2)+1);ILE.WIERSZY(D$2:$D2)))
i zatwierdź poprzez CTRL+SHIFT+ENTER.
Skopiuj do dwóch komórek poniżej.
Jak widzisz w formule są odwołania strukturalne ( jeśli nie wiesz co to, wujek google pomoże się dowiedzieć) czyli Twój zakres z danymi zamieniasz na Tabelę. o nagłowkach..... no już chyba wiesz jakich :-))

Pozdrawiam
Link do wypowiedziLink
Artur D.

Artur D. Solution Architect,
Atos IT Services Sp.
z o.o.

Temat: Zwracanie ostatnich wyników z kolumny

Formuła tablicowa:

=INDEX($B$1:$B$21;LARGE(($A$1:$A$21=$D$1)*ROW($A$1:$A$21);{1;2;3}))

Zanaczamy zakres D2:D4, wpisujemy formułę i zatwierdzamy Ctrl + Shift + Enter

Najprostszym sposobem na samoczynne rozwijanie będzie wstawienie w interesujący zakres tabeli - wtedy formuła wygląda tak:

=INDEX(Tab_1[[#All];[value]];LARGE((Tab_1[letter]=$D$1)*ROW(Tab_1[letter]);{1;2;3}))
Link do wypowiedziLink
Zbigniew Szyszkowski

Zbigniew Szyszkowski sprzątacz,
Ministerstwo
Rolnictwa i Rozwoju
Wsi

Temat: Zwracanie ostatnich wyników z kolumny

Hej Artur,
Wsio ok ale mam zastrzeżenia co do tego fragmentu ROW($A$1:$A$21)....nieodporna na wstawianie wierszy ponad tabelą (właśnie dlatego stosuję konstrukcję jak u mnie w poście)
A już zupełnie zaciemniłoby sprawę gdyby dane tabeli zaczynały sie np w B7 ( a nie od B1).
Druga formułka jest niestety błędna .

Pozdrawiam
P.S błędna z tego samego powodu a nie że błędna w ogóle.Ten post został edytowany przez Autora dnia 11.03.15 o godzinie 15:04
Link do wypowiedziLink
Artur D.

Artur D. Solution Architect,
Atos IT Services Sp.
z o.o.

Temat: Zwracanie ostatnich wyników z kolumny

Co do samego zastrzeżenia, że jest mało odporna na wstawianie wierszy ponad tabelkę to się zgadzam, ale istnieje bardzo proste rozwiązanie - wystarczy zmienić początek w pierwszej formule na:

INDEX($B:$B

i sprawa z głowy. "Błąd" był, ale wskazałeś niewłaściwe miejsce w tej formule jako to do poprawy. W drugiej formule (odwołanie do list object) faktycznie trzeba kombinować, ale dla mnie ROW+INDIRECT+ROWS załatwiają sprawę:

=INDEX(Tab_1[[#All];[value]];LARGE((Tab_1[letter]=$D$1)*ROW(INDIRECT("2:"&ROWS(Tab_1[[#All];[value]])));{1;2;3}))Ten post został edytowany przez Autora dnia 11.03.15 o godzinie 21:37
Link do wypowiedziLink
Zbigniew Szyszkowski

Zbigniew Szyszkowski sprzątacz,
Ministerstwo
Rolnictwa i Rozwoju
Wsi

Temat: Zwracanie ostatnich wyników z kolumny

Zgadzam się... w tym przypadku całokolumnowe odwołanie załatwia sprawę... ale jest trochę niepedagogiczne bo niestety większość "użyszkodników" ;-)) nie wie kiedy takie odwołanie jest nieszkodliwe a kiedy przez nie "pada na pysk" wydajność :-)))
Wskazałem nie tyle błąd (bo jako takiego przecież go nie było) co raczej rozwiązanie bardziej "robust"...że tak powiem.
Natomiast nie zgodzę się z Tobą w przypadku drugiego rozwiązania ( z odwołaniami strukturalnymi).... użycie funkcji ulotnej (volatile) czyli w tym przypadku ADR.POŚR (INDIRECT) tam gdzie ich nie potrzebujemy to proszenie się o kłopoty. Z pewnością nie w omawianym przypadku (mały zakres) ale w pracy z duzymi zbiorami danych używanie ich nie jest wskazane o ile nie jest konieczne ( ze względu na przeliczanie przy każdej zmianie w arkuszu). W takim dużym zbiorze dołożenie paru jeszcze kolumn z róznymi innymi formułami znów może nam całkowicie połozyć efektywne korzystanie z arkusza. Dużo by mówić.....

Pozdrawiam
Link do wypowiedziLink
Artur D.

Artur D. Solution Architect,
Atos IT Services Sp.
z o.o.

Temat: Zwracanie ostatnich wyników z kolumny

Widzisz możemy się jeszcze tydzień przerzucać różnymi argumentami, a 95% użytkowników tego forum poległo na połączeniu INDEKS z MAX.K i pewnie do teraz nie wiedzą o co w tym chodzi. Według mnie nie ma eleganckiego rozwiązania tego problemu - mam na myśli ruchomy zakres, gdzie ktoś dostawia wiersze nad tabelę z danymi. Zawsze skończy się to kombinowaniem z formułą. Masz rację z ADR.POŚR, ale w wypadku jego użycia nie musisz wiedzieć jaki jest adres drugiego wiersza tabeli którą chesz sprawdzać ("it's a feature, not a bug" :D)

Podsuwmowując: zawróno Ty, jak i ja jesteśmy w stanie podać od ręki z 5 rozwiązań tego problemu i wszystkie będą działać, ale każde będzie miało specyficzne wady i zalety, więc chyba nie ma sensu sobie nawzajem udowadniać, że wiem lepiej. Tyle ode mnie.
Link do wypowiedziLink
« Wróć do tematów
Odpowiedz

Podobne tematy


Następna dyskusja:

Zmiana nazwy kolumny




Wyślij zaproszenie do
Anuluj