Separacja liczb wpisanych w jedną komórkę Excela

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

Tagged as: Formuła tablicowa, Fragment.tekstu, Funkcje Tekstowe, Usuń.Zbędne.Odstępy