Like

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

Funkcja split rozbija nam zapis na kilka komórek. Funkcja jest też wprowadzona w komórkach, c3 i c4, ale tam nic nie zmienia.

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

Funkcja split dzieli źródłową komórkę, a następnie dodaje wszystkie liczby znalezione w tabeli stworzonej przez split.
Funkcja split dzieli źródłową komórkę, a następnie wszystkie liczby znalezione w tabeli stworzonej przez split są do siebie dodawane przez 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.

Jak widać udało się w komórce C6 wyliczyć poprawny wynik. Co tam się kryje, opowiem w kilku etapach.

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ę:

Formuła SPLIT stworzyła tabelę. Liczby przy których stało “zł” zostały automatycznie zinterpretowane jako format numeryczny, natomiast te z wartością gr pozostawione jako tekst.

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:

Tłumacząc “na polskie”, funkcja If sprawdza, czy komórka C6 przekształcona w tekst, zawiera wyrażenie ” gr”. Jeśli tak, to wywala to ” gr” przy pomocy funkcji regextreplace, a następnie przekształca resztę zawartości w liczbę (funkcja value) i dzieli to przez 100, by uzyskać wartość w złotówkach. Jeśli wyrażenie ” gr” nie zostanie znalezione, to podawana jest zawartość komórki C6.

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

ŁĄCZYMY!

Tym razem wpisaliśmy funkcję tylko w jedną komórkę i obudowaliśmy to ARRAYFORMULA. Zamiast przepisywania funkcji do kolejnych komórek, wstawiliśmy funkcję SPLIT, która tworzy nam zakres. I funkcja if przejdzie przez każdy element tego zarkesu.

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

Funkcja SUM bierze całą wyprodukowaną tabelę i sumuje ją, zostawiając tylko jedną komórkę z wynikiem.

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.

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany.