W tym artykule nauczymy się zwracać SUMA tylko z formuł w programie Excel.
W prostych słowach, pracując z częściowo wypełnionymi danymi. Czasami musimy znaleźć sumę wartości dla warunku. Warunkiem jest uzyskanie sumy, przy której wartości są wyodrębniane tylko za pomocą formuł.
W tym artykule będziemy potrzebować następujących funkcji:
- Funkcja SUMA PRODUKT
- Funkcja CZY.FORMUŁA
Funkcja SUMPRODUCT to funkcja matematyczna w programie Excel. Działa na wielu zakresach. Mnoży odpowiednie tablice, a następnie je dodaje.
Razem= ( A1 * B1 * C1 *… + A2 * B2 * C2 *… +… )
Składnia:
| = PRODUKT SUMA ( tablica1 , [tablica2] ,… ) |
array : Jest to zakres lub lista wartości.
Funkcja CZY.FORMULA zwraca PRAWDA, gdy formuła istnieje w odwołanie_komórki, a w przeciwnym razie zwraca FAŁSZ.
Składnia:
| = ISFORMULA ( odniesienie ) |
odniesienie : odniesienie do komórki, gdzie sprawdzić formułę
Teraz zrobimy formułę z tych funkcji. Tutaj podamy dane i potrzebowaliśmy wyników sumarycznych, w których zastosowano formułę.
Użyj wzoru:
| = PRODUKT SUMA ( tablica * FORMUŁA ( tablica ) ) |
Wyjaśnienie:
- Funkcja CZY.FORMULA zwraca PRAWDA i FAŁSZ na podstawie sprawdzanego warunku w komórkach.
- Funkcja SUMPRODUCT uwzględnia wartość 1 dla każdej wartości TRUE i 0 dla wartości FALSE.
- Iloczyn wzięty pomiędzy odpowiadającymi sobie wartościami zignoruje wartości FALSE, ponieważ wartości są mnożone przez 0s. Tylko wartości TRUE otrzymują SUMA, ponieważ wartości są mnożone przez 1s.
Przykład:
Przetestujmy tę formułę, uruchamiając ją na przykładzie
Tutaj mamy dane, w których Cena ziaren wyodrębniona przez iloczyn Ilości do jego ceny jednostkowej, a niektóre wartości ceny są wprowadzane ręcznie. Jeśli więc potrzebuję znaleźć sumę ceny dla wartości, z której formuła wyodrębniła cenę całkowitą.
Teraz użyjemy poniższego wzoru, aby uzyskać SUMA
Formuła:
| = PRODUKT SUMA ( E2 : E15 * ISFORMULA ( E2 : E15 )) |
Wyjaśnienie:
- Funkcja CZY.FORMULA zwraca PRAWDA i FAŁSZ na podstawie sprawdzanego warunku w komórkach tablicy od E2 do E15.
- Funkcja SUMPRODUCT uwzględnia wartość 1 dla każdej otrzymanej wartości TRUE i 0 dla wartości FALSE, jak pokazano poniżej.
=PROD.SUMA(E2:E15*
{ PRAWDA; FAŁSZYWE ; FAŁSZYWE ; FAŁSZYWE ; PRAWDA ; PRAWDA ; FAŁSZYWE ; PRAWDA ; FAŁSZYWE ; PRAWDA ; PRAWDA ; PRAWDA ; PRAWDA ; PRAWDA } )
- Iloczyn wzięty między odpowiednimi wartościami zignoruje wartości FALSE, ponieważ wartości są mnożone przez 0s. Tylko wartości TRUE otrzymują SUMA, ponieważ wartości są mnożone przez 1s, jak pokazano poniżej.
= PRODUKT SUMA ( { 58,41 ; 0 ; 0 ; 0 ; 82,84 ; 95,58 ; 0 ; 90,27 ; 0 ; 37,8 ; 78,48 ; 57,97 ; 97,72 ; 77,88 }
Tutaj tablica funkcji jest podana jako cell_reference. Naciśnij Enter, aby uzyskać wynik.
Jak widać na powyższym zrzucie, suma wartości wyodrębnionych tam, gdzie istnieje formuła.
Jeśli chcesz znaleźć sumę wartości, które nie mają formuły, użyj funkcji NIE z funkcją CZY.FORMULA.
Użyj wzoru:
| = PRODUKT SUMA ( E2:E15 * NIE ( ISFORMULA ( E2:E15 )) ) |
Użyj formuły i uzyskaj wartość, jak pokazano na poniższej migawce.
Jak widać z powyższego wzoru można uzyskać wartości warunkowe.
Uwagi:
- Funkcja SUMPRODUCT traktuje wartości nieliczbowe jako 0s.
- Funkcja SUMPRODUCT traktuje wartość logiczną TRUE jako 1 i False jako 0.
- Argument tablica musi mieć tę samą długość, w przeciwnym razie funkcja zwróci błąd.
Mam nadzieję, że ten artykuł o tym, jak zwracać SUMA tylko z formuł w programie Excel, jest objaśniający. Więcej artykułów na temat funkcji SUMPRODUCT znajdziesz tutaj. Podziel się swoim zapytaniem poniżej w polu komentarza. Pomożemy Ci.
Jak korzystać z funkcji SUMA PRODUKT w programie Excel
Jak usunąć tekst w programie Excel, zaczynając od pozycji?
Walidacja wpisów tekstowych
Utwórz listę rozwijaną w programie Excel z kolorem
Usuń wiodące i końcowe spacje z tekstu w programie Excel
popularne artykuły
50 skrótów Excela, aby zwiększyć produktywność
Edytuj listę rozwijaną
Absolutne odniesienie w Excelu
Jeśli z formatowaniem warunkowym
Jeśli z symbolami wieloznacznymi
Przeglądaj według daty