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

{ 10 comments… read them below or add one }

strzelec99

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)))

sirwitek

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 🙂

Norim

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 🙂

Pam

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 🙂

matrus

W vba idzie to zrobić za pomocą kilku linijek. Po co się męczyć?

admin

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

bbb-adventure

Nie jest to bardzo latwe, ale dzieki!

matrus

Ok. To fakt żadna nauka nie idzie w las… Zawsze warto znać wiele dróg do celu.
Pozdrawima

kkk

witam mam taki problem. Mam dane w komórkach np: L1;L2;L3;L4;L5;L6 i potrzebuję zliczyć poprzez formułę licz.jeżeli np: wszystkie L3 ale poprzez wpisanie tej formuły mam wartość 0 czy jest jakaś możliwość zeby odseparować inaczej te liczby żebym mógł zliczyć je poprawnie ?

Kamil

Witam
Niestety nie działa gdy pierwsza liczba jest dwucyfrowa

Leave a Comment

Previous post:

Next post: