fbpx

Wyodrębnianie gramatury produktów z ich nazwy, czyli liczba z tekstu

01.08.2017 | Analizy sprzedaży, ECP2, ECP3, Makra VBA

Czyli wyodrębniania liczby z tekstu za pomocą funkcji w VBA

W dzisiejszym wpisie sytuacja, której nie życzę nikomu.Sytuacja, która jednak się zdarza i to jak się okazuje często… Najlepiej zobrazuje to screen poniżej:

Formatka

I chodzi oczywiście o wyciągnięcie gramatury z nazwy produktu. Jest wiele fajnych metod na wyciąganie liczb z tekstu, jednak ta sytuacja jest wyjątkowo wredna, ponieważ te liczby są różnej długości, w różnych miejscach w tekście, są często dziesiętne, a nawet jak są całkowite, to zapisywane np. tak: 1,0 zamiast po prostu 1. Próbowałam wielu sposobów na ten przypadek, ale po wielu nieprzespanych nocach stwierdziłam, że jednak VBA będzie tutaj najlepszym wyjściem. Zawsze staram się unikać funkcji tworzonych w VBA, ponieważ są wolniejsze, muszą być przechowywane w pliku z rozszerzeniem .xlsm lub .xlsb itp. Jednak tutaj stwierdziłam, że to najlepsze wyjście.

Logika funkcji

Zadanie funkcji jest takie, że po kolei sprawdza każdy znak w nazwie produktu. Jeśli będzie on liczbą lub przecinkiem – zostanie zapamiętany w swoistym kontenerze (będzie to po prostu zmienna tekstowa). Na koniec zawartość tego kontenera zostanie wyświetlona jako wynik funkcji. Proste, prawda? Technicznie użyję do tego kilku zmiennych i oczywiście pętli (For). W VBA to kilka linijek, a z formułą trzeba byłoby się nabiedzić, zwłaszcza, że jest tyle wyjątków…

Tworzenie makra

1. Stwórz nowy plik Excela, który zapisz jako Skoroszyt programu Excel z obsługą makr .xlsm

2. Wejdź do edytora Visual Basica (Alt + F11)

3. Stwórz nowy moduł, czyli menu Insert / Module.

4. Do białego okienka po prawej strony wklej następujący kod:

Function GramaturaTekst(Produkt As String) As String
   Dim Dlugosc As Long, Znak As String, Licznik As Long
 
   Dlugosc = Len(Produkt)
 
   For Licznik = 1 To Dlugosc
      Znak = Mid(Produkt, Licznik, 1)
      If IsNumeric(Znak) Or Znak = "," Then GramaturaTekst = GramaturaTekst & Znak
   Next

End Function

Tworzysz w ten sposób funkcję o nazwie GramaturaTekst, którą od tej pory możesz wykorzystać w arkuszu, jak normalną funkcję Excela. Funkcja ta wymaga tylko jednego argumentu, czyli tekstu (nasz produkt), z którego ma wyciągnąć liczbę oznaczającą gramaturę.

Użyłam kilku zmiennych:

  1. Dlugosc – długość nazwy produktu
  2. Znak – pojedynczy znak wyodrębniany do sprawdzenia
  3. Licznik – zmienna techniczna dla pętli

Postanowiłam wyświetlać wynik funkcji, czyli liczbę, jako tekst, ze względu na to, że w nazwach produktów występują wartości typu 1,0l. Oczywiście jeśli tekst 1,0 przekonwertujemy na liczbę otrzymamy po prostu 1. Będzie to uciążliwe ze względu na późniejsze wyciąganie jednostki z tej samej nazwy produktu.

Jeśli jednak nie masz takich „kwiatków” w swoich danych, spokojnie możesz użyć takiej funkcji:

Function Gramatura(Produkt As String) As Double
 Dim Dlugosc As Long, Znak As String, Licznik As Long, Kontener As String
 
 Dlugosc = Len(Produkt)
 
 For Licznik = 1 To Dlugosc
    Znak = Mid(Produkt, Licznik, 1)
    If IsNumeric(Znak) Or Znak = "," Then Kontener = Kontener & Znak
 Next
 
 Gramatura = Kontener
End Function

Zauważ jednak, że dochodzi tutaj dodatkowa zmienna Kontener, która przechowuje w sobie wyodrębniane znaki. I sama w sobie funkcja jest typu Double, czyli zwraca liczbę, a nie tekst. Tak oczywiście tygryski lubią najbardziej :). Można oczywiście użyć wcześniejszej funkcji GramaturaTekst i już w samym Excelu przekonwertować ją na liczbę (np. =–GramaturaTekst(A1)). Obojętne, byle o tym pamiętać.

Efekt funkcji GramaturaTekst  jest następujący:

Wynik funkcji VBA

Wynik funkcji VBA

Jak widać, gramatura jest tekstem (jest wyrównana do lewej strony) i to jest tutaj ok, ponieważ będzie potrzebne do kolejnego kroku, czyli ustalania jednostki…

Ustalanie jednostki

To już zrobimy zwykłymi wbudowanymi funkcjami Excela. Damy radę! (jak Bob Budowniczy – jeden z ulubieńców moich dzieciaków :)).

Zaczniemy od funkcji FRAGMENT.TEKSTU, ponieważ potrzebujemy wyciągnąć pewien określony tekst z nazwy produktu. Oczywiście ten tekst jest różny i ciągle w innym miejscu, więc będzie sporo kombinacji.

Znowu logika jest taka, że ponieważ już znamy wartość liczbową, to możemy ustalić na której pozycji w tekście się ona znajduje (funkcją SZUKAJ.TEKST). Jednostka jest zawsze „przyklejona” do niej. Po jednostce jest zazwyczaj spacja. Zazwyczaj, ponieważ są sytuacje, gdzie jednostka jest po prostu ostatnim znakiem w tekście. I na to też trzeba będzie się zabezpieczyć (JEŻELI.BŁĄD).

W każdym razie, jak już poznamy pozycję liczby, będziemy mogli też poznać pozycję spacji zaraz za nią (znowu funkcją SZUKAJ.TEKST). Po odjęciu jednego od drugiego uzyskamy długość całej gramatury (DŁ), łącznie z jednostką. A z tego łatwo otrzymamy liczbę znaków jednostki – po prostu odejmiemy długość naszej liczby.

Formuła, która to wszystko robi jest dość długa, ale działa:

=FRAGMENT.TEKSTU(B4;SZUKAJ.TEKST(C4;B4)+DŁ(C4);JEŻELI.BŁĄD(SZUKAJ.TEKST(" ";B4;SZUKAJ.TEKST(C4;B4));DŁ(B4)+1)-SZUKAJ.TEKST(C4;B4)-DŁ(C4))

Oczywiście łatwiej byłoby, gdyby nasze jednostki zawsze były w środku tekstu – wtedy nie byłoby konieczności funkcji JEŻELI.BŁĄD, ale tak jednak nie jest 🙂

Wynik wszystkiego wygląda tak:

Wynik

Wynik

Powiązane produkty:

  • WEBINAR: Konwersja tekstu na liczbę odpowiadający na pytanie dlaczego w ogóle Excel czasem traktuje liczby/ daty jako tekst oraz prezentujący szereg metod na radzenie sobie z takimi sytuacjami. Dodatkowo pokazana jest też metoda na sytuację odwrotną: gdy chcemy z liczby stworzyć tekst.

 

 Poniżej plik z gotowcem do pobrania (zawiera makra oczywiście):

MalinowyExcel Gramatura w nazwach produktów dw.xlsm

I wersje wideo dla funkcji GramaturaTekst w VBA:

I dla wbudowanych funkcji tekstowych:

 

Jeżeli chcesz lepiej poznać Excela na poziomie średniozaawansowanym zapisz się na listę zainteresowanych kursem Excel w codziennej pracy cz. 2! Podczas oczekiwania na kurs będę Ci wysyłała informacje, ciekawostki i excelowe triki. Bezpłatnie! 

Pomogłam Ci? Postaw mi wirtualną kawę! →

Przeczytaj podobne wpisy

Kategorie

15 komentarzy

  1. Przydatna sprawa, ale co jeśli w nazwie produktu znajdą się jeszcze jakieś inne liczby niż te , które dotyczą wagi/pojemności? Np. Szampon 2w1 200ml. Albo co jeśli pomiędzy wagą a określeniem jednostki jest spacja : szampon 200 ml ?
    Da się to jakoś prosto zmodyfikować?

    Reply
    • Hmmm… myślę, że najprościej byłoby wypisać sobie takie wyjątki jak np. 2w1 czy 3w1 i je pomijać (czyli jak pętla je napotka – idzie do kolejnego znaku, a najlepiej do +2 kolejnego). Z takim słownikiem byłoby łatwiej. Dla spacji po liczbie można też przejechać najpierw narzędziem „zamień”, tak aby zamienić ” ml ” –> „ml ” itp.

      Reply
      • Dziękuje bardzo za bardzo użyteczny kod.
        Potrzebuje pomocy żeby go troszkę udoskonalić.

        W sytuacji gdy w wersie jest kilka liczb, jak zrobić, żeby je wszystkie wyszczególniło i oddzieliło spacją?

        z góry dziękuję za pomoc.

        PS. jestem początkującym w VBA

        Reply
        • Hej,
          tutaj bym zastosowała już inny kod:

          Function GramaturaTekstKilka(Produkt As String) As String
          Dim Dlugosc As Long, Licznik As Long, Znak As String
          Dim Slowa() As String, Elementow As Long

          Dlugosc = Len(Produkt)
          Slowa = Split(Produkt, ” „)
          Elementow = UBound(Slowa)
          'UBound zwraca najwyższy indeks

          For Licznik = 0 To Elementow
          Znak = Mid(Slowa(Licznik), 1, 1)
          If IsNumeric(Znak) Then GramaturaTekstKilka = GramaturaTekstKilka & ” ” & Slowa(Licznik)
          Next

          GramaturaTekstKilka = Trim(GramaturaTekstKilka)

          End Function

          🙂

          Reply
  2. Do takich rzeczy najlepszy jest RegEx.

    Function GramaturaTekstRegex(Produkt As String, opcja As String) As String

    Dim strPattern As String: strPattern = „(.*\s)([0-9,]+)([g|kg|l|ml])(\s?.*)”
    Dim regEx As New RegExp

    With regEx
    .IgnoreCase = False
    .Pattern = strPattern
    End With

    If regEx.Test(Produkt) Then
    If opcja = „1” Then
    GramaturaTekstRegex = regEx.Replace(Produkt, „$2”)
    ElseIf opcja = „0” Then
    GramaturaTekstRegex = regEx.Replace(Produkt, „$3”)
    Else
    GramaturaTekstRegex = „zły argument: 1-gramatura, 0-jednostka”
    End If
    Else
    GramaturaTekstRegex = „”
    End If
    End Function

    Reply
    • Super! Wyrażenia regularne – geniusz 🙂

      Reply
    • Dziękuję za kod.
      Z początku nie działał (jestem laikiem) przez brak referencji vba microsoft vbscript regular expressions 5.5.
      Umieszczam alternatywę, która nie wymaga dodawania ww. referencji.
      Zamiast:
      Dim regEx As New RegExp
      Można użyć:
      Dim regEx As Object
      Set regEx= CreateObject(„VBScript.RegExp”)

      Do moich potrzeb zmodyfikowałem też lekko pattern

      (.*\D\s?)(\d+,?\d*)\s?(kg|g|l|ml)(.*)”

      Zmieniłem spację przed gramaturą na opcjonalną (często ograniczenia znaków nazw zmuszają do ich skracania kosztem spacji)
      Aby uniknąć błędnego wyniku Produkt2 200g (zły wynik: 2200g) dodałem \D co oznacza, że ostatni znak przed spacją nie może być cyfrą.
      Tu mam problem z wynikiem gdyby nazwa była faktycznie bez spacji i kończyła się liczbą: Produkt2200g (może jakieś pomysły???)
      W ostatnim ciągu pozbyłem się granicy słowa ponieważ (niestety) w bazie danych mam różne zapisy gramatur (tabs tab tablets itp).

      Reply
      • Dziękuję 🙂

        Reply
  3. Nie wyobrażam sobie pracy z tekstem bez wyrażeń regularnych. Podstawowa wiedza może być przyswojona dosyć łatwo a korzyści z ich zastosowania przeogromne. Proponuję osobną lekcję na ten temat 🙂
    Napisany przeze mnie na szybko pattern nie do końca działa. Poświęciłem więcej czasu na przetestowanie rozmaitych przypadków i ulepszona wersja powinna wyglądać tak:
    (.*\s)(\d+,?\d*)\s?(kg|g|l|ml)(\b.*)

    Jak to czytać? Sprawdzamy czy nasz tekst pasuje do następującego wzorca:
    – dowolny początek tekstu: .* aż do spacji \s
    – pierwsza grupa którą chcemy zwrócić jako gramaturę:
    jedna lub więcej cyfr, opcjonalny przecinek, opcjonalne kolejne cyfry (jeśli pojawi się drugi przecinek to już nie traktujemy jako liczbę, nie zostanie to zwrócone)
    – opcjonalna spacja, może być ale nie musi
    – druga grupa którą chcemy zwrócić jako jednostkę:
    dopuszczamy tylko jednostki wymienione w nawiasie i rozdzielone znakiem | który oznacza logiczny LUB
    – po jednostce obowiązkowo granica słowa – po to aby ciąg 1mleko nie został złapany jako 1 ml
    – po jednostce może być jeszcze dowolny ciąg znaków

    Jeśli w tekście będzie więcej niż jedno dopasowanie to zwrócona zostanie wartość ostatnia z prawej. W tej sytuacji należy ponownie zmodyfikować wzorzec i zdecydować co ma być zwrócone.
    Skuteczność tego kodu sprawdziłem na różnych nietypowych przypadkach:

    Piwo2x2 perla 0,5ml mocna pusz.
    Zupa kucharek barszcz biały 40gr
    Napoj mirinda ananas 1,0l pet
    Piwo karpackie 0,5l mocne but.zwr.
    Jajka z mlecznej czekol.polonia 10,5g
    Cukier 2×2 drobny 5 kg diamant
    Ciastka alpino z jab.i miodem 150,1,0g
    Ciastka alpino z rodzynkami 150g
    szampon 2w1 40ml 1st class
    Posti herbata 1kg lisciasta ziolowa mieta 40g,
    Mlekpol mleko 3,2% 500ml karton12x12
    Posti herbata lisciasta ziolowa pokrzywa 40g
    Mlekpol 10mleko
    Iga markizy 140,00g z kremem orzechowym

    Reply
    • Hej, dziękuję za tak obszerne informacje! Zdecydowanie temat ten zasługuje na oddzielny artykuł. Widzę, że Ty wiesz na ten temat bardzo dużo, a z pewnością więcej niż ja. Może chciałbyś napsiać gościnny artykuł na moim blogu na ten temat? Co myślisz?

      Reply
      • A jak zmodyfikować ten kod aby móc wyciągnać z nazwy jeden konkretny wyraz np. herbata?

        Reply
  4. Szczere dzięki ale nie będę Ci wchodził w kompetencje, zresztą twój blog to nie tylko artykuł ale i wideo 😉 Ale jeśli tylko będziesz chciała zrobić o tym wpis i będziesz potrzebować pomocy to chętnie pomogę. Masz mojego maila więc jakby co to pisz śmiało.

    Reply
    • A co jeśli chciałabym wyciągnąć konkretny wyraz z nazwy?
      Np. nazwa „Napoj mirinda ananas 1,0l pet” jest w kolumnie A, ja chce wyciągnać słowo „mirinda” do kolumny L.
      Oczywiście chodzi o masowe wyciągnie ściśle określonych wyrazów.

      Reply
      • Hej,
        a powiedz co jest celem takiego wyodrębniania? Bo na pewno trzeba byłoby zdefiniować te słowa, które mają być wyodrębnione, chyba że istnieje jakaś reguła, że np. słowo występuje zawsze jako drugie we wpisie. Na pewno to będzie kombinowanie…

        Reply

Leave a Reply to robik Anuluj pisanie odpowiedzi

Twój adres e-mail nie zostanie opublikowany. Wymagane pola są oznaczone *

Pin It on Pinterest