Załóżmy, że masz listę produktów z ich ceną w tabeli Excela. Teraz musisz stworzyć pakiety, które mogą zawierać różne produkty z dostępnych pozycji. Teraz na końcu chciałbyś obliczyć całkowitą cenę każdego pakietu. Czasem możesz to zrobić ręcznie, ale jeśli jest to twoje zwykłe zadanie, lepiej zautomatyzować to zadanie za pomocą eleganckich formuł. I po to jest ten artykuł. Nauczymy się obliczać ceny pakietów lub grup towarów za pomocą jednej formuły.
Wzór ogólny:
=SUMAPRODUKT(zakres_ceny,--(zakres_kontroli="y")) |
Przedział cenowy:Jest to asortyment zawierający cenę produktów.
check_range: Jest to zakres, w którym chcemy postawić nasz czek. Jeśli produkt jest częścią pakietu, wstawiamy y w przekroju pakietu i produktu.
"tak"= To jest kontrola, którą wystawiamy, aby dołączyć produkt do pakietu.
Miejmy przykład, aby wyjaśnić koncepcję.
Przykład: Utwórz formułę wyceny pakietów w programie Excel.
Bierzemy ten sam scenariusz, który omówiliśmy na początku. Przygotowaliśmy tabelę w zakresie B2:F9. Zidentyfikujmy potrzebne nam zmienne.
Przedział cenowy:Przedział cenowy to C2:C9. Ponieważ jest to ustalone, możemy albo nazwać zakres, albo użyć jego bezwzględnego odniesienia. W tym przykładzie użyję odniesienia bezwzględnego $C$2:$C$9.
Sprawdź_zakres:Są to zakresy zawierające sprawdzenia (kolumna pakietu). Są to D3:D9, E3:E9 i F3:f9.
Umieśćmy te wartości w ogólnej formule.
Napisz tę formułę w D10, aby obliczyć cenę pakietu.
=PRODUKTSUMA($C$3:$C$9,--(D3:D9="y")) |
Wciśnij Enter. Masz koszt pakietu 1 obliczony w komórce D10. Skopiuj tę formułę do sąsiednich komórek, aby obliczyć cenę wszystkich pakietów.
Jak to działa?
Formuła działa na lewą stronę. Więc najpierw…(D3:D9="y") jest rozwiązany.
Zwraca tablicę 1 i 0 jako. 1 dla każdego y i 0 czegokolwiek innego w zakresie D3:D9.
{1;1;0;1;0;1;0} |
Następnie $C$3:$C$9 jest konwertowane na tablicę zawierającą cenę każdego przedmiotu/produktu.
{100;200;20;10;12;15;25} |
Teraz funkcja SUMPRODUCT ma to w sobie.
=PROD.SUMA({100;200;20;10;12;15;25};{1;1;0;1;0;1;0}) |
Teraz, jak robi to funkcja SUMPRODUCT, mnoży każdą wartość w jednej tablicy przez tę samą indeksowaną tablicę w innej tablicy i na koniec sumuje te wartości. Oznacza to, że każda cena odpowiadająca wartości 0 w innej tablicy jest zamieniana na 0. {100;200;0;10;0;15;0}. Teraz ta tablica jest sumowana. To daje nam 325 za pakiet 1. To samo dotyczy wszystkich pakietów.
Alternatywna formuła:
Alternatywną formułą jest oczywiście funkcja SUMA.JEŻELI i SUMA.
=SUMA.JEŻELI(D3:D9;"y";$C$3:$C$9) |
oraz
=SUMA.WARUNKI($C$3:$C$9;D3:D9;"y") |
To są klasyczne odpowiedzi, ale formuła SUMPRODUCT jest również szybsza i bardziej wyszukana.
Więc tak, w ten sposób można łatwo obliczyć cenę pakietu w Excelu. Mam nadzieję, że było to wystarczająco wyjaśniające. Jeśli przegapiłem jakiś punkt lub masz jakiekolwiek wątpliwości dotyczące tego artykułu lub jakichkolwiek innych wątpliwości związanych z Excelem, zapytaj o to w sekcji komentarzy poniżej.
Policz wszystkie dopasowania w dwóch zakresach w Excelu | Dowiedz się, jak liczyć wszystkie dopasowania w dwóch zakresach za pomocą funkcji SUMPROUDCT.
SUMY z wykorzystaniem logiki AND-OR | SUMIFS może być również używany z logiką OR. Domyślna logika, której używa SUMIFS, to logika AND.
SUMPRODUCT z logiką IF | Dowiedz się, jak używać SUMPRODUCT z logiką JEŻELI bez używania funkcji JEŻELI w formule.
50 skrótów Excela zwiększających produktywność | Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Funkcja WYSZUKAJ.PIONOWO w programie Excel | Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
LICZ.JEŻELI w programie Excel 2016 | Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby policzyć konkretną wartość. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMA.JEŻELI w programie Excel | To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.