Automatyczne tworzenie zakresów „od-do”

by Marcin

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.

Automatyczne tworzenie zakresów „od-do”

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.

Automatyczne tworzenie zakresów „od-do”

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.

Automatyczne tworzenie zakresów „od-do”

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.

Automatyczne tworzenie zakresów „od-do”

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ł.

Automatyczne tworzenie zakresów „od-do”

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:

=ORAZ($D$7>0;$D$7<LICZ.JEŻELI($C$13:$C$28;”granica”))

Automatyczne tworzenie zakresów „od-do”

To mniej więcej tyle na dzisiaj.

Marcin

Download

{ 7 comments… read them below or add one }

climax

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

Marcin

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

oszczepek

jak zrobić aby można było użyć obliczonego zakresu (adres) w funkcji np. podaj pozycję (przeszukiwana tabela)?

piotr80

Przyłączam się do zapytania. Jak użyć obliczonego zakresu. chodzi o skopiowanie go do innego arkusza.
Pozdrawiam. Piotr

marek11

jak ma wyglądać formuła
prawidłowa odp nr 1

Michał

Cześć 🙂 Bardzo dziękuję za ten przykład. Zastosowałem go mniej więcej w takiej postaci u siebie ale nie potrafię sobie poradzić z tym zakresem zdefiniowanym w nazwie (fragment Twój: 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 ). Otóż chodzi mi o to, że ja taki zakres zdefiniowałem w jednej zakładce i chcę go skopiować do 50 kolejnych zakładek a Excel kopiuje mi zakres z nazwą zakładki poprzedniej, przykład: mam zdefiniowaną nazwę w zakładce o nazwie „24.ZTP”: =ADR.POŚR(ADRES(PODAJ.POZYCJĘ(„struktura procesu”;’24.ZTP’!$A$1:$A$300;0)+1;2;1;1)&”:”&ADRES(300;3;1;1)) i jak chcę tę formułę przekopiować do kolejnej zakładki o nazwie powiedzmy „25.ZTP” to Excel nie wpisuje automatycznie nowej zakładki, tylko wkleja z nazwą poprzedniej, czyli: =ADR.POŚR(ADRES(PODAJ.POZYCJĘ(„struktura procesu”;’24. ZTP’!$A$1:$A$300;0)+1;2;1;1)&”:”&ADRES(300;3;1;1))… Jak zrobić, żeby automat mi to zmieniał w formule po przekopiowaniu do innych zakładek ?
dziękuję
Pozdrawiam, Michał

Marcin

Witam
Próbuję skorzystać z tej formuły =JEŻELI(A1=””;0;LICZ.JEŻELI(A:A;A1))>1
ale jak chce rozszerzyć zakres działania o kolumnę D
wpisując =JEŻELI(A1=””;0;LICZ.JEŻELI(A:A;D:D;A1))>1
to wywala mi błąd „podano za dużo argumentów dla tej funkcji.
Proszę o pomoc.

Leave a Comment

Previous post:

Next post: