Sumowanie narastająco z pominięciem błędów

Załóżmy, że swój plan oszczędzania na nowy komputer chcesz rozpisać w Excelu. W kolumnie B wpisujesz kwoty, które udało ci się odłożyć, a w przypadku gdy w danym miesiącu nic nie udało się odłożyć, wpisujesz funkcję BRAK(). Powoduje ona, że na wykresie znajdującym się w innym arkuszu i opartym na tych danych nie ma przerw w ciągłości serii. Zadanie polega jednak na obliczeniu w kolumnie C sumy kwot z kolumny B narastająco (sumowanie komórek zawierających funkcję BRAK() powoduje zwracanie błędu). Dodatkowo obok komórek zawierających funkcję BRAK() w kolumnie C ma nie pojawiać się żadna wartość.

W kolumnie C możesz zastosować formułę tablicową, ale dodatkowo trzeba wstawić pusty wiersz.

Aby to zrobić:

1. Zaznacz wiersz 2. i na karcie Narzędzia główne, w grupie poleceń Komórki wybierz Wstaw/Wstaw wiersze arkusza, aby wstawić pusty wiersz przed wierszem 2.

2. Ponownie podświetl wiersz 2. i na karcie Narzędzia główne, w grupie poleceń Komórki wybierz Format/Ukryj i Odkryj/Ukryj wiersze, aby ukryć wstawiony pusty wiersz.

3. W komórce C3 wstaw formułę tablicową:

=JEŻELI(CZY.LICZBA(B3);B3+ADR.POŚR(ADRES(MAX(JEŻELI(CZY.LICZBA($C$2:C2);WIERSZ($C$2:C2);2));3));"")

Pamiętaj, że formuły tablicowe zatwierdza się, jednocześnie naciskając klawisze [Ctrl]+[Shift]+[Enter], a nie sam [Enter] jak przy zwykłych formułach. Formuły tablicowe na pasku formuły są wyświetlane w nawiasach klamrowych.

Formuła sprawdza, czy w komórce obok w kolumnie B wpisana jest liczba – jeśli nie, to wynikiem jest pusty ciąg znaków. W przypadku gdy w komórce obok znajduje się liczba, to formuła sprawdza, które komórki zawierają liczby, począwszy od komórki C2, a skończywszy na komórce znajdującej się wiersz wyżej. Następnie formuła spośród sprawdzanych komórek pobiera tę liczbę, która znajduje się w najniżej położonym wierszu i dodaje ją do wartości komórki znajdującej się obok w kolumnie B.

2. Zaznacz komórkę C3 i na karcie Narzędzia główne, w grupie poleceń Schowek wybierz Kopiuj, aby skopiować komórkę.

3. Zaznacz komórki C4:C33 i na karcie Narzędzia główne, w grupie poleceń Schowek wybierz Wklej, aby wkleić komórkę z formułą tablicową do wszystkich wierszy zestawienia.

Kopiować komórki możesz także na inne sposoby, np. zaznaczyć komórkę C3 i przeciągnąć na dół czarny krzyżyk znajdujący się w prawym dolnym rogu komórki.

Po tych operacjach w kolumnie C zostanie wstawiona suma narastająco, ale te wartości będą wstawiane tylko wtedy, gdy w kolumnie B znajduje się liczba.

Pobierz przykładowe dane:

Ćwiczenie 1

Należysz do szkolnego koła Przyjaciół Natury. Planujecie z okazji Światowego Dnia Zwierząt przeprowadzić zbiórkę charytatywną, a za zebraną kwotę kupić karmę i przekazać ją do wybranych schronisk w waszym mieście. Przez cały wrzesień postanowiliście chodzić po klasach i zbierać pieniądze od nauczycieli i uczniów. Podczas całej akcji twoim obowiązkiem było skrupulatne notowanie i obliczanie kwoty, jaką do tej pory udało wam się zebrać. Ile kilogramów karmy będziecie w stanie kupić, jeśli kilogram kosztuje 5,50 zł? Otrzymaną wartość zaokrąglij w dół do części setnych.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

R2WbZ8YdTSPk9

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A i B wprowadzono dane dotyczące zbiórki pieniędzy na zakup karmy dla zwierząt ze schroniska w poszczególnych dniach. W arkuszu kolejno dodano opisy: w komórce A1 Data, w komórce B1 Kwota. W kolumnie A, w komórkach od A3 do A11 wpisano kolejne daty. W kolumnie B, w komórkach od B3 do B6 oraz B9 do B11 wpisano wartości kwot. W komórkach B7 i B8 wpisano #N/D. Dodatkowo zaprezentowano sposób obliczania kwoty zebranej. Formułę obliczania należy wpisać w odpowiedniej komórce. W tym przypadku w komórce C3. Brzmi ona następująco: =JEŻELI(CZY.LICZBA(B3);B3+ADR.POŚR(ADRES(MAX(JEŻELI(CZY.LICZBA($C$2:C2);WIERSZ($C$2:C2);2));));""). Na arkuszu jest podany wynik: suma zebranej kwoty wynosi 8938. Udało się za nią kupić 1625,09 kilogramów karmy.

Ćwiczenie 2

Jedna z twoich koleżanek z kółka wpadła na pomysł, żeby poza zbieraniem pieniędzy organizować również zbiórkę karmy. Zmodyfikuj arkusz z poprzedniego zadania i wylicz ilość karmy, jaką będzie można przekazać schroniskom.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

RXAEIWta0pLNE

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A, B, C i D wprowadzono dane dotyczące zbiórki pieniędzy na zakup karmy dla zwierząt ze schroniska w poszczególnych dniach oraz ilości karmy. W arkuszu kolejno dodano opisy: w komórce A1 Data, w komórce B1 Kwota, w komórce C1 Kwota zebrana, w komórce D1 Karma, w komórce E1 Karma zebrana. W kolumnie A, w komórkach od A3 do A13 wpisano kolejne daty. W kolumnie B, w komórkach od B3 do B6 oraz B9 do B13 wpisano wartości kwot. W komórkach B7 i B8 wpisano #N/D. W kolumnie C, w komórkach od C3 do C13 wpisano wartości kwot. W kolumnie D, w komórkach, D3, D4, D9, D11, D12 wpisano wartości karmy. W komórkach D5, D6, D7, D8, D10 i D13 wpisano #N/D. Dodatkowo zaprezentowano sposób obliczania karmy zebranej. Formułę obliczania należy wpisać w odpowiedniej komórce. W tym przypadku w komórce E3. Formuła brzmi następująco: =JEŻELI(CZY.LICZBA(D3);D3+ADR.POŚR(ADRES(MAX(JEŻELI(CZY.LICZBA($E$2:E2);WIERSZ($E$2:E2);2));5));""). Na arkuszu jest podany wynik: suma pieniędzy wynosi 8938. Udało się za nią kupić 2358,09 kilogramów karmy.