Tag Archives: funkcja

Loading
loading..

Tutaj możecie ściągnąć plik ze stworzoną dwuetapową listą wybieralną.

Lista jest stworzona tak, że w 7 wierszu (zaznaczony na jasno zielony kolor) możemy dodawać kolejne regiony, a w polu zaznaczonym szarym kolorem możemy dodawać kolejne wydziały w tych regionach.
W zależności od tego ile dodamy pól, to lista wybieralna będzie odpowiednio dostosowana.

Lista wybieralna opiera się na poprawności danych. Klikamy na komórkę E2, wchodzimy w Dane/Poprawność danych i na karcie Ustawienia wybieramy opcję Lista. Różnica między standardową listą wybieralną polega na tym, że jako źródło nie podajemy zwykłej listy a formułę.

karta developer

Jako źródło listy wybieralnej regionu nie jest podany po prostu zakres $A$7:$D$7 tylko odpowiednia formuła z wykorzystaniem funkcji PRZESUNIĘCIE – kliknij tutaj aby przejść do wpisu gdzie opisujemy funkcję.

W komórce E2 (gdzie wybieramy region), w poprawności danych jako źródło listy wybieralnej mamy wpisaną formułę:

=PRZESUNIĘCIE($A$7:$I$7;0;0;;ILE.NIEPUSTYCH($A$7:$I$7))



karta developer

Taka formuła powoduje, że gdy dopiszemy kolejne regiony w zakresie A7:I7 to pojawią się one na liście wybieralnej. Gdyby jako źródło był podany bezpośrednio zakres $A$7:$I$7, to na liście pojawiłyby się również puste pozycje. Co więcej, dzięki powyższej formule zakres ma tyle kolumn ile jest niepustych komórek w zakresie $A$7:$I$7. W funkcji PRZESUNIĘCIE jako piąty argument jest wpisana formuła ILE.NIEPUSTYCH($A$7:$I$7), dzięki czemu szerokość zakresu jest zmieniona w naszym przykładzie ma on 4 kolumny (mamy wpisane 4 regiony). Czyli zwrócony przez funkcję zakres to: $A$7:$D$7 i takie jest źródło naszej listy wybieralnej.

W kolejnej komórce, gdzie mamy wydziały (komórka E3) jako źródło listy wybieralnej wpisaliśmy formułę:

=PRZESUNIĘCIE($A$8:$A$102;0;PODAJ.POZYCJĘ(E2;$A$7:$I$7;0)-1;ILE.NIEPUSTYCH(PRZESUNIĘCIE($A$8:$A$102;0;PODAJ.POZYCJĘ(E2;$A$7:$I$7;0)-1)))



karta developer

Funkcja PODAJ.POZYCJĘ(E2;$A$7:$I$7;0) zwraca pozycję wybranego przez nas wyżej regionu w zakresie a7:I7. I tak np. jeśli wybierzemy Południe, to podaj pozycje zwróci nam wartość 4. Dzięki temu wiemy, że interesująca nas lista wydziałów znajduje się w kolumnie 4, czyli musimy przesunąć zakres $A$8:$A$102 o 3 kolumny (czyli pozycja Południe – 1) w prawo. Właśnie w tym celu zastosowaliśmy funkcję PRZESUNIĘCIE. Powoduje ona, że zakres $A$8:$A$102 jest przesunięty o 0 wierszy w dół i odpowiednią ilość kolumn w prawo (w przypadku południe 3 kolumny). Znów formuła ILE.NIEPUSTYCH zapewnia nam, że na liście nie będą widoczne puste elementy, czyli wielkość zakresu będzie miała tyle wierszy ile jest niepustych komórek. Ostatecznie funkcja PRZESUNIĘCIE powoduje, że w źródle listy wybieralnej odwołujemy się do zakresu $D$8:$D$11.

Mam nadzieję, że to Wam pomogło. Zapraszamy za tydzień po kolejny wpis. 🙂

Czasami pojawiały się pytania o funkcję „oczyszczającą”, która w tekście zostawiałaby wskazane przez nas znaki a pozostałe usuwała. Po tych pytaniach postanowiliśmy umieścić taką funkcję na blogu, aby ułatwić Wam pracę.:)

Przedstawiona poniżej funkcja pozostawia w tekście znaki, które są wskazane w drugim argumencie funkcji. Jako argument możemy podać ciąg znaków i tak np. jeśli wpiszemy poniższą formułę:

=Pozostaw(„Ala ma kota!”; „ma „)

to funkcja usunie wszystkie znaki poza m, a, oraz spacją. Wynikiem działania będzie „Aa ma a”


Function Pozostaw(PrzeszukiwanyTekst As String, Znak As String) As String
    'Funkcja pozostawia w tekście znaki, które są podane w drugim argumencie
    Dim Z As String
    Dim i As Long
    Dim NumerZnaku As Long
    For i = 1 To Len(PrzeszukiwanyTekst)
        NumerZnaku = NumerZnaku + 1
        Z = Mid(PrzeszukiwanyTekst, NumerZnaku, 1)
        If Not UCase(Znak) Like "*" & UCase(Z) & "*" And Z <> "" Then
            PrzeszukiwanyTekst = Replace(PrzeszukiwanyTekst, Z, "")
            NumerZnaku = NumerZnaku - 1
        End If
    
    Next
    
    Pozostaw = PrzeszukiwanyTekst
End Function

Jeśli nie wiesz jak wkleić powyższą funkcję to zapraszamy do wpisu jak zacząć pierwsze makro.
Wystarczy skopiować i wkleić powyższy kod a już będziemy mogli korzystać z formuły Podstaw.


Inne przykłady wykorzystania funkcji Pozostaw

Funkcja pozostaw może nam się np. przydać jeśli mamy podany serię i numer dowodu, a chcemy je oddzielić.
Przykładowo: ABS123456

Chcemy jednak wyciągnąć tylko cyfry z podanego ciągu. Wystarczy, że wywołamy funkcję

Pozostaw(„ABS123456″;”0123456789”)

Oczywiście jako argumenty funkcji możemy podawać odwołania do komórek.

Layout mode
Predefined Skins
Custom Colors
Choose your skin color
Patterns Background
Images Background

Zapisz się na nasz newsletter dotyczący nauki Excela

Wysyłaj mi newsletter (możesz się wypisać w każdym dowolnym momencie).

FreshMail e-mail marketing free online marketing software