Załóżmy, że w komórce Excela masz ciąg liczb oddzielonych od siebie jakimś symbolem. Na przykład tak, jak na obrazku poniżej.
Oczywiście taki tekst możesz łatwo rozdzielić na poszczególne liczby przy pomocy narzędzia „Tekst jako kolumny”. Nie jest to jednak dokładnie to, o co mi chodzi, dlatego dzisiaj postanowiłem napisać o tym, w jaki sposób możesz pobrać dowolną liczbę z takiego ciągu i wykorzystać ją w swoich obliczeniach.
W kolejnym wpisie pokażę jak można obliczyć sumę wszystkich liczb w takiej komórce (jest spora szansa na to, że sam sobie to w międzyczasie wykombinujesz).
Dla uproszczenia przyjmiemy kilka założeń:
- Liczby zawsze odzielone są od siebie tylko jednym symbolem. Nie sprawdzam tego w żadnej formule i w przypadku wstawienia pomiędzy liczby większej ilości znaków oddzielających, formuły prawdopodobnie zwrócą błędy (a już na pewno drastycznie spadnie wiarygodnośc wyników).
- Chciałbym mieć możliwość wyboru znaku jakim będą rozdzielone liczby.
- W przygotowanych formułach używać będziemy znaku”@” dlatego ten znak nie może pojawić się jako znak oddzielający liczby.
Na początek przygotujemy sobie wskazany ciąg tekstowy z liczbami, usuwając z niego wszystkie „nadwymiarowe” znaki na początku i na końcu ciągu. Dodatkowo, na własne potrzeby, na końcu takiego „czystego” już ciągu dodamy wybrany przez nas znak oddzielający liczby.
=USUŃ.ZBĘDNE.ODSTĘPY(C2)&E2
Ilość liczb we wskazanej komórce łatwo policzymy używając formuły:
=DŁ(D10)-DŁ(PODSTAW(D10;E2;""))
W naszym przykładzie zwraca ona wartośc 7, co jak łatwo sie przekonać (zerkając na obrazek lub animację powyżej) jest wartością jak najbardziej prawidłową.
Formuła, którą przygotowałem i która zwraca wybraną liczbę z ciągu tekstowego wygląda następująco:
=WARTOŚĆ(JEŻELI(C5=1;FRAGMENT.TEKSTU(USUŃ.ZBĘDNE.ODSTĘPY(C3)&E3;1;1);FRAGMENT.TEKSTU(USUŃ.ZBĘDNE.ODSTĘPY(C3);SZUKAJ.TEKST("@";PODSTAW(USUŃ.ZBĘDNE.ODSTĘPY(C3)&E3;E3;"@";C5-1))+1;SZUKAJ.TEKST("@";PODSTAW(USUŃ.ZBĘDNE.ODSTĘPY(C3)&E3;E3;"@";C5))-SZUKAJ.TEKST("@";PODSTAW(USUŃ.ZBĘDNE.ODSTĘPY(C3)&E3;E3;"@";C5-1))-1)))
Wygląda na bardzo zawiłą, a to głównie dlatego, że wszystkie operacje zostały spakowane w jedna formułę. Jeżeli nie boisz się komórek pomocniczych (które nie tylko przyśpieszają pracę, ale także ułatwiają późniejsze „czytanie” i zrozumienie formuł) możesz całość podzielić na mniejsze fragmenty i wtedy mogłoby to wyglądać tak:
Komórki pomocnicze zdefiniowane są następująco:
a formuła zwracająca poszukiwaną liczbę wygląda teraz tak:
=JEŻELI(C5=1;FRAGMENT.TEKSTU(C3;1;1);FRAGMENT.TEKSTU(USUŃ.ZBĘDNE.ODSTĘPY(C3);SZUKAJ.TEKST("@";C7)+1;C10-C9-1))
O wiele prościej prawda ?
Marcin
{ 7 comments… read them below or add one }
Bardzo fajny pomysł, z tym, że jesli pierwsza liczba będzie zawierała więcej niż jeden znak, fomuła zwróci tylko pierwszy
. Po małej modyfikacji powinna wyglądać ona jakoś tak
=WARTOŚĆ(JEŻELI(C10=1;FRAGMENT.TEKSTU(USUŃ.ZBĘDNE.ODSTĘPY(C8)&E8;1;SZUKAJ.TEKST(„@”;PODSTAW(USUŃ.ZBĘDNE.ODSTĘPY(C8)&E8;E8;”@”;C10);1)-1);FRAGMENT.TEKSTU(USUŃ.ZBĘDNE.ODSTĘPY(C8);SZUKAJ.TEKST(„@”;PODSTAW(USUŃ.ZBĘDNE.ODSTĘPY(C8)&E8;E8;”@”;C10-1))+1;SZUKAJ.TEKST(„@”;PODSTAW(USUŃ.ZBĘDNE.ODSTĘPY(C8)&E8;E8;”@”;C10))-SZUKAJ.TEKST(„@”;PODSTAW(USUŃ.ZBĘDNE.ODSTĘPY(C8)&E8;E8;”@”;C10-1))-1)))
pobawiłem się tym i uważam, że to ciekawe rozwiązanie.
Osobiście bym to nieco zmodyfikował wrzucając znak @ również przed ciąg tekstowy. Odpadnie wówczas problem z funkcją warunkującą JEŻELI – stanie się zbędna.
Poza tym, dla zgrabności funkcji końcowej warto wrzucić w osobną komórkę ciąg tekstowy wraz z dodanymi na jej początku i końcu znakami @ i do tej komórki odwoływac się w formule finalnej. Ale to kosmetyka
W każdym razie dzięki za pomysł. Chwilkę musiałem pomyśleć jak to zrobić po swojemu
no nie wiem, tak jak pisał strzelec99 nie działa pierwsza liczba, i mnie to troszeczkę niepokoi, a potrzebuję to na gwałt, kod strzelca też nie działa, więc czy istniej taki kod którym mógłbym dowolne ciąg znaków rozszyfrować??? Jestem początkującą osobą i byłbym wdzięczny za każdą podpowiedz
Przypadkiem znalazłam to zadanie i rozwiązałam je swoim sposobem. Działa bezbłędnie. Zmiana dotyczy dodania gwiazdki na początku ciągu, po to żeby można było zrezygnować z funkcji jezeli, no i zakładam że nie ma niepotrzebnych odstępów. Jeżeli są wtedy nie jest problemem dodanie formuły z ich usunięciem. Cała formuła wygląda tak:
=FRAGMENT.TEKSTU(PODSTAW(ZŁĄCZ.TEKSTY(„*”;C3&E3);E3;”@”;C5);SZUKAJ.TEKST(„@”;PODSTAW(ZŁĄCZ.TEKSTY(„*”;C3&E3);E3;”@”;C5))+1;SZUKAJ.TEKST(„@”;PODSTAW(ZŁĄCZ.TEKSTY(„*”;C3&E3);E3;”@”;C5+1))-SZUKAJ.TEKST(„@”;PODSTAW(ZŁĄCZ.TEKSTY(„*”;C3&E3);E3;”@”;C5))-1)
Częściowe formuły to:
C7: PODSTAW(ZŁĄCZ.TEKSTY(„*”;C3&E3);E3;”@”;C5)
C8: PODSTAW(ZŁĄCZ.TEKSTY(„*”;C3&E3);E3;”@”;C5+1)
C9: SZUKAJ.TEKST(„@”;C7)
C10: SZUKAJ.TEKST(„@”;C8)
C12: FRAGMENT.TEKSTU(C7;C9+1;C10-C9-1)
Wygląda bardzo łatwo i przyjemnie
W vba idzie to zrobić za pomocą kilku linijek. Po co się męczyć?
Mozna tez np. komus zaplacic i ten ktos zrobi to za nas. Jeszcze mniej roboty. Chodzi jednak o to, ze jak sie czlowiek troche nameczy to moze sie czegos nauczy i wykorzysta to w przyszlosci w zupelnie innym projekcie. Swoja droga, WSZYSTKO mozna w VBA zrobic. Pytanie wiec po co w ogole Microsoft w ogole jakiekolwiek formuly udostepnia?
Marcin
Nie jest to bardzo latwe, ale dzieki!