Jak nauczyć arkusz czytać sumy. Na przykład taki zapis: 3,20 zł za piwo + 0,50 zł butelka.

Chodzi oczywiście o sytuację, gdy nie możemy sami wziąć i rozmontować arkusza, dodać kolumn, etc, a musimy wyciągnąć tą wartość 3,70 zł. Bo przecież tyle to razem kosztuje.

Operację dzielę na 2 etapy:

Najpierw rozbijam zawartość komórki B5 na poziomą tabelę. Funkcja split traktuje w tym wypadku każdą spację jako seperator kolejnych komórek. Mam więc wydzielone liczby i inne znaki. Wszystko możesz prześledzić w pliku roboczym.

Składnia:

SPLIT(tekst;separator;czy każdy znak wymieniony w polu separator;czy skasować puste komórki).

Ostatnie dwa parametry są opcjonalne i domyślnie ustawione jako prawda (true).

Teraz pozostaje tą tabelę zsumować. Ale nie musimy dokładać tu żadnych kolumn, wystarczy, że zagnieździmy funkcje i dodamy funkcję SUM.

Co zrobić jeśli jest trudniej i mamy zapis 3,20 zł + 50 gr, a gdzie indziej 3,20 zł + 0,50 zł, albo okaże się że składniki są 3 lub więcej?

Tu się zaczynają schody, bo chcemy żeby arkusz działał w każdej sytuacji. Tworzymy więc przykład i się z nim siłujemy.

Zaczynamy od tego, że skoro mamy same sumowanie, to możemy podzielić komórki nie po spacji, a po znaku + i funkcja split stworzy nam następującą tabelę:

Teraz musimy zsumować tabelę, biorąc pod uwagę, że te liczby przy których stoi ” gr” mają wartość 100 razy mniejszą niż te bez ” gr”. Posłużymy się tu kilkoma funkcjami.

IF – klasyczna funkcja warunkowa mówi co zrobić gdy trafimy na komórkę z groszami, a co jak na komórkę z wartością zł.

REGEXMATCH posłuży nam do sprawdzenia, w których komórkach jest ” gr”, – szerzej o wyrażeniach regularnych mówię w osobnym wpisie.

VALUE przekształci format tekstowy na liczbę (potrzebne po usunięciu ” gr” z komórki)

TO_TEXT pozwoli zastosować funkcje na wyrażeń regularnych na wszystkich komórkach (bez TO_TEXT nie poradzą sobie na komórkach z formatem liczbowym).

REGEXREPLACE – usunie ” gr” z komórek, tak by można było ich użyć do sumowania.

ARRAYFORMULA (opisana w osobnym wpisie) pozwoli, żeby funkcje zadziałały po kolei na wszystkich komórkach tabeli stworzonej przez SPLIT

SUM – zsumuje to wszystko razem.

ZACZYNAMY!

Zaczynamy na piechotę. Pod każdą komórką wygenerowaną przez funkcję split, umieściłem funkcję IF, która analizuje zawartość komórki:

Jak widzicie, trzeba było wygenerować masę komórek – w wierszu 6 jest tabela, a poniżej, w wierszu 7 skopiowane funkcje IF.

ŁĄCZYMY!

Na koniec pozostaje zsumować wszystkie otrzymane wartości i zapakować wszystko z powrotem w jedną komórkę, gdzie będzie wynik.

Jeśli skopiujecie ostatnią formułę do wszystkich wcześniejszych przykładów to zadziała również.

Specjalnie dla Olka – co zrobić z zapisem treningu biegowego

Chodzi o zapis zawierający kilka etapów treningu wraz z seriami. Dla przykładu wziąłem taki trening:

2 km rozgrzewki + 3x 400 m szybko + 2 km schłodzenia

Oczywiście wszystko w jednej komórce. I zadaniem jest zsumowanie tego dystansu. Zadziałałem podobnie jak w poprzednich przykładach, tylko do usuwania niepotrzebnych ogonów używałem funkcji SUBSTITUTE, a nie REGEXREPLACE. No i musiałem sobie poradzić tak by funkcja ignorowała pojawiające się dodatkowe słowa jak “rozgrzewki”, “schłodzenia”.

Jak widzicie, trochę się tu dzieje (najpierw wyciągam dystanse, potem ustalam czy są to metry czy kilometry, potem wyciągam ilość powtórzeń danego dystansu i mnożę powtórzenia przez dystans). ARRAYFORMULA pozwala na mnożenie poszczególnych elementów powstałych tabel (w praktyce jest to 1x 2 km + 2×0,4km +1x 2 km) przez siebie, a obejmująca wszystko funkcja SUM, sumuje wyniki.