EXCEL: Znalezienie numeru kolumny pierwszego i ostatniego wystąpienia znaku w ciągu
Proszę o pomoc w napisaniu funkcji (nie makra) znajdowania pierwszej i ostatniej kolumny ciągów danych (tu liter B).
Przykład wygląda tak
A B C D E F G H I J K L M N O P Q R
1| A A _ B B B B D _ Z _ B B B A 8 _ B
Podkreślenia to puste komórki, wynik działania kolejnych funkcji w kolejnych komórkach ma wyglądać: 4 7 12 14 18 18
W wyniku pierwszej funkcji chcę uzyskać numer kolumny pierwszego wystąpienia B (proste: =JEŻELI.BŁĄD(PODAJ.POZYCJĘ("B";$A1:R1;0);""), w wyniku drugiej funkcji ostatniego wystąpienia B w pierwszym ciągu (tu G1 czyli 7 kolumna).
(Kolejnym zadaniem będzie znalezienie pierwszej i ostatniej kolumny drugiego ciągu liter B, czyli kolumny 12 i 14, itd...3 ciąg...)
Pozdrawiam, Borys
Odpowiedzi: 1
Hej.
Makro (funkcja użytkownika) jest prostsza nie tylko w opracowaniu ale i w stosowaniu w arkuszu.
Przy pomocy samych formuł.
- w Twoim przykladzie potrzebuję wolnej kolumny A więc ją wstawiam a dane są w kolumnach B:S,
- w formułach nie uwzględniam wystąpienie błędu 'nie znaleziono' lub '99' - to już sam opracujesz,
- formuły są tablicowe - trzeba je zatwierdzać klawiszem Enter podczas trzymania wciśniętych klawiszy Ctrl i Shift.
- literę do znalezienia (tu B) umieściłem w komorce B3
1-sza para
=PODAJ.POZYCJĘ($B$3;$B$1:$S$1;0)
=MIN.K(JEŻELI(($B$1:$S$1=$B$3)*($C$1:$T$1<>$B$3); NR.KOLUMNY($B$1:$S$1)-1;99);1)
2-ga
=MIN.K(JEŻELI(($B$1:$S$1=$B$3)*($A$1:$R$1<>$B$3); NR.KOLUMNY($A$1:$R$1);99);2)
=MIN.K(JEŻELI(($B$1:$S$1=$B$3)*($C$1:$T$1<>$B$3); NR.KOLUMNY($B$1:$S$1)-1;99);2)
3-cia
=MIN.K(JEŻELI(($B$1:$S$1=$B$3)*($A$1:$R$1<>$B$3); NR.KOLUMNY($A$1:$R$1);99);3)
=MIN.K(JEŻELI(($B$1:$S$1=$B$3)*($C$1:$T$1<>$B$3); NR.KOLUMNY($B$1:$S$1)-1;99);3)
Parami można je kopiować zmieniając jedynie ostatni parametr funkcji MIN.K
Rozwiązanie przy pomocy funkcji użytkownika.
Do modułu ogólnego Module1 wstaw kod:
Function PodajPozycjęZnaku(znak As String, zakres As Range)
Dim T() 'tablica na wyniki
Dim p As Integer 'pozycja w w/w tablicy
Dim bIN As Boolean 'flaga 'w ciągu'
Dim i As Integer
bIN = False
'przygotowanie tablicy wyników
p = zakres.Count - 1
ReDim T(p)
For i = 0 To p
T(i) = "" 'pusty ciąg by nie zwracać błędu ND
Next i
p = 0
For i = 1 To zakres.Count
If UCase(zakres(i)) = UCase(znak) Then
If Not bIN Then
'początek ciągu
bIN = True
T(p) = i
p = p + 1
End If
Else
If bIN Then
'koniec ciągu
bIN = False
T(p) = i - 1
p = p + 1
End If
End If
Next i
'czy kompletna ostatnia para?
If bIN Then
T(p) = i - 1
End If
PodajPozycjęZnaku = T
End Function
Funkcja ta zwraca tablcę wyników i musi być użyta w arkuszu jako tablicowa wprowadzona do tablicy (jedna funkcja w zkresie komorek).
Dla szukanej litery B w B3 (jak poprzednio),
- zaznacz zakres np. B4:S4
- wpisz formułę
=PodajPozycjęZnaku(B3;B1:S1)
- zatwierdź ją klawiszami Ctrl + Alt + Enter
Powodzenia
