Problem
Zadanie na dzisiaj jest następujące. W jednej z kolumn, w różnych komórkach znajdują się pewne znaczniki (w moim przypadku ciągi tekstowe „granica”). Definiują one początki i końce przedziałów. Znaczniki te wstawiane są automatycznie i mogą pojawiać się w różnych komórkach. Ich ilość także może być różna. Przykładowo, na rysunku poniżej zaznaczony jest przedział (zakres) numer 2.
Wszystko, co chciałbym teraz zrobić to mieć możliwość łatwego i szybkiego wyboru zakresu (wpisując numer zakresu w jedną z komórek), który mnie interesuje.
Rozwiązanie:
Moja propozycja rozwiązania tego problemu zakłada skorzystanie z kolumny pomocniczej. Do pierwszej komórki w kolumnie pomocniczej wpisałem formułę:
=JEŻELI($C13=”granica”;WIERSZ();”")
i skopiowałem w dół na pozostałe komórki. Wszędzie tam gdzie w wierszu obok znajduje się ciąg „granica” funkcja zwróci numer wiersza. W pozostałych przypadkach zwróci pusty ciąg.
Kolejny krok to zdefiniowanie dynamicznej nazwy, która określać będzię wybrany zakres.
Nazwę zdefiniowałem następująco:
=ADR.POŚR(ADRES(MIN.K($B$13:$B$28;$D$7);4)&”:”&ADRES(MIN.K($B$13:$B$28;$D$7+1);4))
, gdzie w komórce D7 znajduje się numer interesującego mnie przedziału.
Można to samo zrobić także przy pomocy innych funkcji, na przykład tak:
=PRZESUNIĘCIE($D$1;MIN.K($B$13:$B$28;$D$7)-1;0;MIN.K($B$13:$B$28;$D$7+1)-
MIN.K($B$13:$B$28;$D$7)+1;1)
Od tego momentu, po wpisaniu do komórki D7 numeru interesującego mnie zakresu, mogę skorzystać ze zdefiniowanej nazwy, która, jak widać na rysunku poniżej, określa dokładnie ten zakres, który mnie interesuje.
Dodam teraz kilka dodatkowych znaczników „granica” i zmienię numer interesującego mnie zakresu, aby sprawdzić czy wszystko wciąż będzie działać jak należy. Rysunek poniżej pokazuje, że tak jest w istocie. Zdefiniowana nazwa wskazuje dokładnie szósty przedział.
Tak zdefiniowanych zakresów możesz następnie użyć w innych funkcjach, na przykład poszukując Produktu 1 (w moim przypadku), ale tylko w ramach 6 przedziale.
Na koniec możesz jeszcze uodpornić całość na wypadek wprowadzenia niewłaściwego (liczba mniejsza bądź równa zero, większa niż całkowita liczba zakresów) numeru poszukiwanego zakresu . Wszystko możesz zrobić przy pomocy sprawdzania poprawności.
Odpowiednia formuła „zabezpieczająca” mogłaby wyglądać następująco:
{ 4 comments… read them below or add one }
Witam,
ciekawa metoda, robilem kiedys cos podobnego ale bardziej męczącym sposobem:)
jedno spostrzezenie, mianowicie we wzorze na koniec zakresu powinno byc chyba tak
=ADRES(MIN.K($B$13:$B$28;D7+1)-1;4) (dodałem „-1″)
w inny wypadku zakres zachacza o pierwszy element nastepnej podgrupy
pozdrawiam
Cześć,
Oczywiście można sobie odpowiednio dostosować formulę. W moim przypadku chciałem, aby zakresy zaczynały i kończyły się dokładnie w tym wierszu w którym jest sowo „granica”. Dlatego pokazałem takie rozwiązanie. Przypuszczalnie jednak bardziej interesujące dla większości z Was może być rozwiązanie uwzględniające to o czym piszesz. Całość jest w sumie dosyć elastyczna i jak pokazałeś można sobie dostosować. Dzięki za formułę.
Marcin
jak zrobić aby można było użyć obliczonego zakresu (adres) w funkcji np. podaj pozycję (przeszukiwana tabela)?
Przyłączam się do zapytania. Jak użyć obliczonego zakresu. chodzi o skopiowanie go do innego arkusza.
Pozdrawiam. Piotr