Jak sumować poprawnie mimo błędów w zakresie sumy?
Poprawne sumowanie zakresów z błędami dla różnych wersji Excela — widzowie #18
Jeśli wykorzystujesz funkcję SUMA do sumowania zakresu, w którym jest błąd otrzymasz wynik z błędem. Jak tego uniknąć.
W Excelu 2010 możesz skorzystać z funkcji AGREGUJ np:
=AGREGUJ(9;6;B1:B8)
9 (1 argument) — mówi, że chcesz sumować,
6 (2 argument) — mówi, że funkcja ma ignorować błędy
Dla Excela 2007 formuła może wyglądać tak
=SUMA(JEŻELI.BŁĄD(B1:B9;0))
czyli sprawdza czy jest błąd (funkcja JEŻELI.BŁĄD), jeżeli jest to podstawiasz w jego miejsce zero, a potem sumujesz całość. Pamiętaj o zatwierdzeniu kombinacją klawiszy Ctrl + Shift + Enter, bo to formuła tablicowa.
Dla Excela 2003 jest najwięcej pisania:
=SUMA(JEŻELI(CZY.LICZBA(B1:B9);B1:B9;0))
Najpierw sprawdzasz czy wartość jest liczbą (funkcja CZY.LICZBA) jeśli tak to wstawiasz tą liczbę jeśli nie to wstawiasz zero (pomaga funkcja JEŻELI), a na koniec sumujesz. Tą formułę również zatwierdzasz kombinacją klawiszy Ctrl + Shift + Enter
Przy pliku .xls w jakim te formuły były pisane w trybie zgodności jeśli zechcesz zapisać plik pojawi Ci się komunikat o nie zgodności i możesz nawet podejrzeć raport i domyśleć się, że funkcja AGREGUJ jest dostępna od 2010, a JEŻELI.BŁĄD od 2007.
P.S.
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.
Jak zsumować komórki w tabeli spełniające kryterium 'i' w wierszu i 'lub' w kolumnach?
SUMA prostokątnej tablicy 1 warunek w wierszu i kryteria dla nagłówków kolumn — sztuczki #60
Warunkowe operacje na tablicach aby dodać z 1 kryterium z wiersza i wieloma kryteriami w nagłówkach kolumn.
Zobacz, jak dodać w prostokątnej tabeli których numery odpowiadają 1 kryterium w nagłówku wiersza i dopasowaniu lub dla kryterium nagłówków kolumn:
Użycie: funkcji PODAJ.POZYCJĘ, CZY.LICZBA, operacji porównania tabeli, mnożenia macierzy oraz funkcji SUMA.ILOCZYNÓW
Dodaj: Komórki z tabeli, które spełniają kryterium nagłówka wiersza oraz kryteria lub w nagłówkach kolumn
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.
Jak zliczyć wystąpienia 47 w komórkach, którą są i tekstem i liczbami?
Dostałem zapytanie żeby zliczyć komórki, gdzie wystąpiła liczba 47. Dane w komórkach mogą wyglądać tak:
Licz wystąpienia w komórkach, nie ważne czy tekst czy liczba — widzowie #7
Przykładowe dane
Czyli czasami 47 jest sama, czasami na początku tekstu, a czasem w środku. Jak w takim razie to policzyć, skoro funkcja LICZ.JEŻELI nie zadziała.
Postanowiłem wykorzystać funkcję SZUKAJ.TEKST. Początkowo myślałem, że będę musiał zapisać daną jako tekst, czyli "47" (47 w cudzysłowach), ale okazało się to zbędne. Funkcja SZUKAJ.TEKST odnajdywała 47 nie ważne jak była traktowana przez Excel. Czy komórka była wyrównana do prawej i oznaczała liczbę, czy do lewej i oznaczała tekst.
Teraz miałem liczby, gdy SZUKAJ.TEKST znalazło 47 i błąd #ARG! gdy nie znalazło
{1;1;#ARG!;5}
Potrzebowałem wiedzieć tylko czy liczba występuje, dlatego wykorzystałem funkcję CZY.LICZBA. Teraz miałem wartości PRAWDA i FAŁSZ :
{PRAWDA;PRAWDA;FAŁSZ;PRAWDA}
Niestety tych wartości nie da się łatwo zsumować. Trzeba je zamienić na 1 i 0 przez prostą operację matematyczną np: 2 minusy przed funkcją CZY.LICZBA.
Jak uzyskałem 1 i 0 wystarczyło je zsumować, czyli końcowa formuła wygląda tak:
=SUMA(–CZY.LICZBA(SZUKAJ.TEKST(D5;B1:B4)))
Pamiętaj ją zatwierdzić Ctrl + Shift + Enter , bo to formuła tablicowa.
Wynik działania formuły
P.S.
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.
Jak znaleźć największą wartość znajdującą się po wybranym fragmencie tekstu?
Jeśli chcesz znaleźć komórkę gdzie po wyszukiwanym fragmencie tekstu jest największa liczba potrzebujesz wykonać kilka kroków.
Znajdź największą liczbę za fragmentem tekstu — sztuczki #25
Na czerwono szukany tekst, a na zielono wynik końcowego wyszukania
Najpierw trzeba znaleźć wszystkie wystąpienia szukanego fragmentu tekstu — funkcja SZUKAJ.TEKST, niestety zwraca ona błąd #ARG! jeśli nie znajdzie fragmentu. W naszych obliczeniach będzie to stanowić problem, więc należy dodatkowo wykorzystać funkcję CZY.LICZBA, żeby zamienić wyniki na wartości logiczne PRAWDA (gdy znalazł fragment) i FAŁSZ (gdy nie znalazł)
=CZY.LICZBA(SZUKAJ.TEKST(D2;A2:A10))
W naszym przykładzie mamy zawsze liczbę 2 cyfrową po prawej stronie, więc wyciągamy ją funkcją
=PRAWY(A2:A10;2)
Wynik działania funkcji PRAWY — znajduje liczby i traktuje je jak tekst
Teraz wystarczy zwykłe mnożenie matematyczne, by zamienić wartości logiczne i liczby traktowane jako tekst, na liczby tam gdzie są fragmenty szukanego tekstu i zera gdzie nie ma.
a później szukamy jej pozycji, czyli funkcja PODAJ.POZYCJĘ wykorzystująca jeszcze raz wcześniejsze obliczenia i pamiętając, że szukamy dokładnego porównania (zero jako ostatni argument):
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.
Jak podświetlić kilka dat jednocześnie (formatowanie warunkowe)?
Chcesz, żeby formatowanie warunkowe podświetliło Ci linie, w których jest jedna z 3 dat (albo więcej)?
Formatowanie warunkowe podświetlanie 3 lub więcej dat — sztuczki #23
Możesz wykorzystać funkcję LUB i sprawdzić wartość dla każdej komórki osobno, ale jeśli tych komórek jest więcej łatwo się pomylić w odwołaniach względnych i bezwzględnych (tak mi się zdarzyło na filmie, ale akurat na komórce, która się nie sprawdzała 😉
=LUB($A4=$D$4;$A4=$D$5;$A4=$D$6;$A4=$D$7)
Sprawdzanie poprawności dat za pomocą funkcji LUB
Kiedy, jakaś wartość będzie się zgadzała funkcja LUB zwróci wartość PRAWDA, jeśli żadna wartość FAŁSZ
Całkiem innym podejściem jest wykorzystanie funkcji PODAJ.POZYCJĘ, której zadaniem będzie sprawdzenie czy data z danego wiersza znajduje się na liście dat, których szukasz. Jeśli się znajduje to zwróci pozycję, czyli liczbę, jeśli nie to błąd #N/D! , czyli brak na liście.
=PODAJ.POZYCJĘ($A4;$D$4:$D$7;0)
Sprawdzanie poprawności dat za pomocą funkcji PODAJ.POZYCJĘ
W większości operacji Excela ten błąd stanowi duży problem i trzeba sobie z nim poradzić. W tej sytuacji można wykorzystać funkcję CZY.LICZBA, która będzie zwracać wartość PRAWDA dla liczb, a dla błędu #N/D! zwróci fałsz.
=CZY.LICZBA(PODAJ.POZYCJĘ($A4;$D$4:$D$7;0))
Sprawdzanie poprawności dat za pomocą funkcji PODAJ.POZYCJĘ z obsługą błędu
Jednak dla Formatowania Warunkowego nie ma to znaczenia możesz śmiało wykorzystać samą funkcję PODAJ.POZYCJĘ. Formatowanie warunkowe, będzie formatowało komórki, gdzie będą zwracane liczby, a komórki z błędami pominie.
Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić do mnie o tym w komentarzu pod spodem albo napisz do mnie bezpośrednio, ja w miarę możliwości odpowiem na Twoje pytanie.