Separacja liczb wpisanych w jedną komórkę Excela

by Marcin

Separacja liczba w komórce 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.

Suma liczb wpisanych w jedna komórke Excela

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:

Separacja liczb w komórce Excela

Komórki pomocnicze zdefiniowane są następująco:

Separacja liczba w komórce Excela

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 ?

Download

Marcin

{ 9 comments… read them below or add one }


Fatal error: Cannot assign by reference to overloaded object in /home/klient.dhosting.pl/antiquus/excelblog.pl/public_html/wp-content/themes/thesis_182/lib/classes/comments.php on line 176