Jak określić kwotę w programie Excel. Excel jest lepszy niż kalkulator

Wiele osób pracujących z arkuszami kalkulacyjnymi EXCEL w pewnym momencie staje przed pytaniem „jak dodać liczby w komórkach zgodnie z określonym warunkiem w programie EXCEL”. Któregoś słonecznego dnia również natknąłem się na to pytanie, lecz nie mogłem znaleźć odpowiedzi na to pytanie w Internecie, ale byłem uparty i postanowiłem przestudiować pomoc programu, co podsunęło mi pomysł, a potem: metodą prób i błędów opracowałem formułę i jak się okazało, formuła jest elementarna.

Wszystko zaczęło się od tego, że zdecydowałam się uwzględnić swoje miesięczne wydatki i w tym celu stworzyłam tabelę, którą załączam do tego artykułu, bo może przydać się również Tobie.

Teraz postaram się szczegółowo opisać zasadę tworzenia formuły. W raporcie mam szczegółowe statystyki i raport podsumowujący. W statystykach szczegółowych wpisuję swoje dzienne wydatki, a w raporcie zbiorczym wyliczam wysokość wydatków w poszczególnych kategoriach oraz sumę wydatków.

Weźmy na przykład kategorię wydatków „Zakupy w sklepach”. Potrzebujemy EXCELA, aby znaleźć wszystkie wydatki dla danej kategorii w szczegółowych statystykach, zsumować wydatki dla tej kategorii i zapisać wynikową kwotę w komórce D10.

Najpierw zapisujemy przygotowaną formułę, którą wklejamy do komórki D10, a potem zaczniemy zagłębiać się w szczegóły. Gotowa formuła wygląda następująco (tylko na potrzeby naszego artykułu):

=SUMA JEŚLI (5 $ G $: 300 $ G $ ;(" Zakupy w sklepach");$H$5:$H$300 )

Różne warunki są wyróżnione kolorem, aby były wyraźniejsze. Ujmijmy to w porządku. Podczas procesu opisywania spójrz na powyższy obrazek, aby był jaśniejszy. Podczas robienia zdjęcia specjalnie uchwycono litery kolumn i numery wierszy. Więc zacznijmy.

• SUMA– przy tym warunku mówimy, że suma wartości niektórych komórek musi zostać wpisana do komórki, jeśli spełniają one określone warunki;
• 5 $ G $: 300 $ G $– tutaj wskazujemy EXCEL-owi, w której kolumnie mamy szukać warunku selekcji. W naszym przypadku wyszukiwanie odbywa się w kolumnie G zaczynając od linii 5 i kończąc na linii 300 ;
• (« Zakupy w sklepach») – tutaj wskazujemy wymagany warunek i zgodnie z tym warunkiem wartości komórek, które wskazujemy dalej... zostaną zsumowane;
• $H$5:$H$300– tutaj wskazujemy kolumnę, z której będą pobierane liczby do sumowania. W naszym przypadku wartości są pobierane w kolumnie H zaczynając od linii 5 i kończąc na linii 300.

Podsumowując, możemy powiedzieć, że EXCEL sumuje tylko te wartości z zakresu H5:H300, dla których odpowiednie wartości z zakresu G5:G300 są równe „Zakupy w sklepach” i zapisuje wynik do komórki D10.

W związku z tym możesz w programie EXCEL dodaj liczby w komórkach zgodnie z dowolnym warunkiem.

Podpisać $ w formule zastosowano to tak, aby podczas kopiowania formuły z komórki D10 do innych komórek nie było przesunięcia. Spójrzmy na przykład formuły bez znaku $ . Na przykład w komórce D10 Mamy formułę:

=SUMA.JEŻELI(G5:G300;("Zakupy w sklepach"),H5:H300)

Następnie chcemy wyświetlić sumę obiadów w komórce D11. Aby uniknąć przepisywania formuły, możemy skopiować komórkę D10 i wklej do komórki D11. Dzięki temu formuła zostanie wstawiona D11, ale tutaj możemy zauważyć, że formuła zmieniła wartości, zastępując 5 liczbą 6 i 300 liczbą 301:

=SUMA.JEŻELI(G6:G301,("Zakupy w sklepach"),H6:H301)

Nastąpiło przesunięcie. Jeśli skopiujemy formułę do D12, wtedy zobaczymy przesunięcie o 2 i tak dalej. Aby tego uniknąć, zapisujemy formułę ze znakiem $ . Takie są cechy EXCELA.

Tabela „Rozliczanie miesięcznych wydatków” -

W tym artykule przyjrzymy się, jak to zrobić obliczyć kwotę w Excelu za pomocą funkcji AutoSum oraz jak obliczyć kwotę w kolumnie, oblicz kwotę w wierszu lub w wybranym zakresie za pomocą funkcji SUMA. Dowiesz się także jak sumować tylko widoczne komórki, liczyć sumę, jak obliczyć sumę w Excelu na różnych arkuszach i wiele więcej.

Najszybciej oblicz kwotę w Excelu

Najpierw spójrzmy na najszybszy sposób, jak obliczyć sumę komórek w programie Excel. Jeśli chcesz szybko sprawdzić sumę określonych komórek w Excelu, możesz po prostu zaznaczyć te komórki i spojrzeć na pasek stanu w prawym dolnym rogu okna Excela:

Oblicz sumę w Excelu - Szybki sposób na sprawdzenie sumy wybranych komórek

Zacznijmy od najprostszych metod. Zatem nawet jeśli dopiero zaczynasz przygodę z Excelem, prawdopodobnie poniższe przykłady nie będą dla Ciebie trudne do zrozumienia.

Oblicz kwotę w programie Excel za pomocą prostych obliczeń arytmetycznych

Jeśli potrzebujesz obliczyć sumę komórek, możesz używać programu Excel jako minikalkulatora. Po prostu użyj operatora znaku plus (+) tak jak w przypadku normalnej operacji arytmetycznej. Na przykład:
=1+2+3
Lub
=A1+C1+D1

Oblicz sumę w Excelu - Oblicz sumę komórek za pomocą prostych obliczeń arytmetycznych

Oczywiście, jeśli trzeba obliczyć sumę kilkudziesięciu lub kilkuset wierszy, odwoływanie się do każdej komórki w formule nie jest najlepszym pomysłem. W takim przypadku powinieneś użyć funkcji SUMA, która została specjalnie zaprojektowana do dodawania określonego zestawu liczb.

Oblicz sumę w programie Excel za pomocą funkcji SUMA

Funkcja SUMA jest jedną z funkcji matematycznych i trygonometrycznych, które dodają wartości. Składnia tej funkcji jest następująca:

SUMA(liczba1;[liczba2];…)

Pierwszy argument (liczba 1) jest wymagany, pozostałe liczby są opcjonalne, a w jednej formule można określić maksymalnie 255 liczb.

W formule SUMA dowolny argument może być dodatnią lub ujemną wartością liczbową, zakresem lub odwołaniem do komórki. Na przykład:

SUMA(A1:A100)

SUMA(A1; A2; A5)

SUMA (1;5;-2)

Funkcja SUMA przydaje się, gdy trzeba obliczyć sumę wartości z różnych zakresów lub połączyć wartości liczbowe, odwołania do komórek i zakresy. Na przykład:

SUMA(A2:A4; A8:A9)

SUMA(A2:A6; A9; 10)

Poniższy obrazek przedstawia te i kilka innych przykładów formuły SUMA:

Oblicz sumę w programie Excel - Przykłady obliczenia sumy za pomocą formuły SUMA

Jak automatycznie obliczyć kwotę w programie Excel

Jeśli potrzebujesz automatycznie obliczy kwotę, zsumuj jeden zakres liczb, niezależnie od tego, czy chcesz obliczyć sumę w kolumnie, w wierszu, czy w kilku sąsiednich kolumnach lub wierszach, możesz pozwolić programowi Microsoft Excel wykonać te obliczenia za Ciebie.

Wystarczy zaznaczyć komórkę obok liczb, których sumę chcesz obliczyć w zakładce „STRONA GŁÓWNA” w grupie „Edycja”, kliknąć „AutoSum” i klawisz „Enter”, a otrzymasz formułę „ SUMA»:

Oblicz sumę w Excelu - Oblicz sumę w kolumnie za pomocą AutoSum

Jak widać, program Excel „AutoSum” nie tylko wprowadza formułę SUMA, ale także wybiera najbardziej prawdopodobny zakres komórek w celu obliczenia sumy. W tym przypadku Excel automatycznie obliczy kwotę w kolumnie. Jeśli chcesz dostosować sugerowany zakres, możesz to zrobić ręcznie, po prostu przeciągając kursor po komórkach, które chcesz policzyć, a następnie naciskając klawisz Enter.

Oprócz obliczania sumy możesz użyć funkcji AutoSum, aby automatycznie wprowadzić funkcje ŚREDNIA, LICZNIK, MAX lub MIN. Aby uzyskać więcej informacji, zapoznaj się z artykułem „”.

Jak obliczyć kwotę w kolumnie w programie Excel

SUMA(B2:B8)

Oblicz sumę w Excelu - Oblicz sumę w kolumnie

Jak obliczyć kwotę z rzędu w programie Excel

SUMA(B2:D2)

Oblicz kwotę w programie Excel - Oblicz kwotę w wierszu w programie Excel

Oblicz sumę w programie Excel — oblicz sumę w tabeli programu Excel, korzystając z wiersza sumy

Jak obliczyć kwotę w programie Excel na różnych arkuszach

Jeśli masz wiele arkuszy o tym samym układzie i tym samym typie danych, możesz to zrobić obliczyć kwotę w tej samej komórce lub w tym samym zakresie komórek w różnych arkuszach z tą samą formułą SUMA.

SUMA (styczeń: marzec! B2)

SUMA (styczeń: marzec! B2: B8)

Pierwsza formuła obliczy sumę wartości w komórkach B2, a druga formuła obliczy sumę z zakresu B2:B8 we wszystkich arkuszach znajdujących się pomiędzy określonymi dwoma arkuszami granicznymi (w tym przykładzie styczeń i marzec):

Oblicz sumę w programie Excel — użyj odniesienia 3D, aby obliczyć sumę identycznych komórek lub zakresów między określonymi arkuszami

Zatem korzystając ze wzoru =SUMA(styczeń:marzec!B2) obliczyliśmy sumę wszystkich wartości sprzedaży jabłek za trzy miesiące. I korzystając z drugiej formuły =SUMA(styczeń:marzec!B2:B8) obliczyliśmy sumę sprzedaży wszystkich produktów za trzy miesiące, których dane znajdowały się na różnych arkuszach. Więc możesz obliczyć kwotę w programie Excel na różnych arkuszach, używając formuł z odniesieniami 3D.

To wszystko. Dziękuję za przeczytanie artykułu i mam nadzieję, że ten artykuł w wystarczający sposób wyjaśnił pytanie, w jaki sposób obliczyć kwotę w Excelu.

W tym artykule omówimy, jak obliczyć kwotę w kolumnie o nieokreślonej liczbie wierszy oraz jak obliczyć kwotę w kolumnie bez nagłówka lub kilku pierwszych wierszy.

Jak obliczyć sumę kolumny za pomocą funkcji SUMA

SUMA(B2:B8)

Jak obliczyć kwotę w kolumnie w programie Excel - Oblicz kwotę w kolumnie

Jest drugi sposób, jak w Excelu automatycznie obliczyć sumę kolumn . Aby to zrobić, zaznacz ostatnią pustą komórkę w kolumnie, w której chcesz obliczyć kwotę. W zakładce „STRONA GŁÓWNA” w grupie „Edycja” kliknij „AutoSum” i naciśnij klawisz „Enter”. Następnie formuła „SUMA” zostanie automatycznie wpisana do komórki:

Jak obliczyć sumę w kolumnie w programie Excel — w tabeli oblicz sumę kolumny za pomocą funkcji AutoSum

Jak obliczyć sumę w kolumnie o nieokreślonej liczbie wierszy

Jeśli potrzebujesz obliczyć kwotę w kolumnie który ma zmienną liczbę wierszy (czyli w każdej chwili można dodawać nowe komórki, a istniejące usuwać), możesz obliczyć sumę całej kolumny określając odwołanie do kolumny, ale nie określając dolnej lub górnej granicy, na przykład:

Notatka! W żadnym wypadku nie należy umieszczać formuły „ Suma kolumn " do żądanej kolumny obliczyć kwotę ponieważ spowoduje to utworzenie okrągłego odwołania do komórki (tj. nieskończonego sumowania rekurencyjnego), a formuła SUMA zwróci 0.

Jak obliczyć kwotę w kolumnie w programie Excel - Oblicz kwotę w kolumnie, korzystając z odniesienia do kolumny

Jak obliczyć kwotę w kolumnie bez nagłówka lub z wyłączeniem kilku pierwszych wierszy

Zwykle w formule SUMA stosuje się odniesienia do kolumn obliczyć kwotę w kolumnie , ignorując nagłówek, jak pokazano na powyższym obrazku. Jednak w niektórych przypadkach nagłówek kolumny może zawierać jakąś wartość liczbową. Lub na przykład chcesz wykluczyć kilka pierwszych wierszy z liczbami, które nie mają związku z danymi.

Niestety program Microsoft Excel nie akceptuje formuł mieszanych SUMA z wyraźną dolną granicą, ale bez górnej granicy, na przykład =SUMA(B2:B). Jeśli potrzebujesz obliczyć kwotę w kolumnie Po wyeliminowaniu kilku pierwszych linii możesz skorzystać z jednego z kilku kolejnych rozwiązań.

1. Zrobić matematykę suma kolumn a następnie odejmij komórki, których nie chcesz uwzględniać w sumie (w tym przykładzie komórki B1-B3):

SUMA(B:B) - SUMA(B1:B3)

Jak obliczyć sumę w kolumnie w programie Excel - Oblicz sumę w kolumnie z wyłączeniem kilku pierwszych wierszy

1. Mając na uwadze ograniczenia rozmiaru arkusza, możesz określić górny limit formuły SUMA w oparciu o maksymalną liczbę wierszy w Twojej wersji programu Excel.

W programach Excel 2007, Excel 2010, Excel 2013 i Excel 2016:

SUMA(B2:B1048576)

SUMA(B2:B655366)

W tym artykule przyjrzeliśmy się różnym sposobom jak obliczyć kwotę w kolumnie w programie Excel, a teraz możesz wybrać dowolny z nich w zależności od sytuacji.

Wcześniej opisałem, jak używać funkcji niestandardowej do wyszukiwania plików . Niestety ta funkcja nie działa, jeśli komórki są kolorowane przy użyciu formatowania warunkowego. Obiecałem „sfinalizować” tę funkcję. Jednak w ciągu dwóch lat, które minęły od opublikowania tej notatki, ani samodzielnie, ani przy pomocy informacji z Internetu nie udało mi się napisać zrozumiałego kodu... ( Aktualizacja z 29 marca 2017 r Po kolejnych pięciu latach nadal udało mi się napisać kod; patrz ostatnia część notatki). A ostatnio natknąłem się na pomysł zawarty w książce „Excel 2007. Tricks” autorstwa D. Hawleya, R. Hawleya, który pozwala w ogóle obejść się bez kodu.

Niech będzie to lista liczb od 1 do 100, mieszcząca się w przedziale A1:A100 (rys. 1; patrz także arkusz „SUMIF” w pliku Excel). Zakres ma formatowanie warunkowe, które oznacza komórki zawierające liczby większe niż 10 i mniejsze lub równe 20.

Ryż. 1. Zakres liczb; formatowanie warunkowe zaznacza komórki zawierające wartości od 10 do 20

Pobierz notatkę w formacie, przykłady w formacie

Teraz musisz dodać wartości w komórkach, które spełniają właśnie ustawione kryteria. Nie ma znaczenia, jakie formatowanie zostanie zastosowane do tych komórek, ale musisz znać kryteria, według których komórki są wyróżniane.

Aby dodać zakres komórek odpowiadających sam kryterium można skorzystać z funkcji SUMA.JEŻELI (rys. 2).

Ryż. 2. Sumowanie komórek spełniających ten sam warunek

Jeśli masz Niektóre warunkach można skorzystać z funkcji SUMIFS (rys. 3).

Ryż. 3. Sumowanie komórek spełniających kilka warunków

Możesz użyć funkcji LICZ.JEŻELI, aby policzyć liczbę komórek spełniających jedno kryterium.

Za pomocą funkcji COUNTIFS można policzyć liczbę komórek spełniających wiele kryteriów.

Excel ma inną funkcję, która pozwala określić wiele warunków. Ta funkcja jest częścią zestawu funkcji bazy danych Excel i nosi nazwę BDSUMM. Aby to sprawdzić, użyj tego samego zestawu liczb z zakresu A2:A100 (rys. 4; patrz także arkusz „BDSUMM” w pliku Excel).

Ryż. 4. Korzystanie z funkcji baz danych

Zaznacz komórki C1:D2 i nazwij zakres Kryteria, wpisując go w polu nazwy po lewej stronie paska formuły. Teraz wybierz komórkę C1 i wprowadź =$A$1, czyli link do pierwszej komórki na arkuszu zawierającej nazwę bazy danych. Wpisz =$A$1 w komórce D1, a otrzymasz dwie kopie nagłówka kolumny A. Kopie te zostaną użyte jako nagłówki warunków BDSUMM(C1:D2), które nazwałeś Kryteria. W komórce C2 wpisz >10. W komórce D2 wpisz<=20. В ячейке, где должен быть результат, введите следующую формулу:

BDSUMM($A$1:$A$101,1,Kryteria)

Możesz użyć funkcji COUNT, aby policzyć liczbę komórek spełniających wiele kryteriów.

Czytając książkę Johna Walkenbacha dowiedziałem się, że począwszy od Excela 2010 w VBA pojawiła się nowa właściwość DisplayFormat (patrz np. Właściwość Range.DisplayFormat). Oznacza to, że VBA może odczytać format wyświetlany na ekranie. Nie ma znaczenia, w jaki sposób został uzyskany, poprzez bezpośrednie ustawienia użytkownika lub przy użyciu formatowania warunkowego. Niestety programiści MS sprawili, że właściwość DisplayFormat działa tylko w procedurach wywoływanych z VBA, a funkcje zdefiniowane przez użytkownika oparte na tej właściwości rzucają #VALUE! Sumę wartości w zakresie dla komórek o określonym kolorze można jednak uzyskać za pomocą procedury (makra, ale nie funkcji). Otwórz (zawiera kod VBA). Przejdź przez menu Pogląd -> Makra -> Makra; w oknie Makro, wybierz linię SumaKolorUsl i naciśnij Wykonać. Uruchom makro, wybierz zakres sumowania i kryterium. Odpowiedź pojawi się w oknie.

Kod procedury

Sub SumColorConv() Application.Volatile True Dim SumColor As Double Dim i As Range Dim UserRange Jako zakres Dim CriterionRange As Range SumColor = 0 " Range Request Set UserRange = Application.InputBox(_ Prompt:="Wybierz zakres sumowania", _ Tytuł : ="Wybór zakresu", _ Domyślnie:=ActiveCell.Address, _ Type:=8) " Żądanie kryterium Set CriterionRange = Application.InputBox(_ Prompt:="Wybierz kryterium sumowania", _ Title:="Wybór kryterium", _ Domyślnie:=ActiveCell.Address, _ Typ:=8) " Zsumuj „poprawne” komórki dla każdego i w UserRange If i.DisplayFormat.Interior.Color = _ CriterionRange.DisplayFormat.Interior.Color Następnie SumColor = SumColor + i End Jeśli następny MsgBox SumColor End Sub

Załóżmy, że musisz zsumować wartości z więcej niż jednego warunku, na przykład kwotę sprzedaży produktu w określonym obszarze. Jest to dobry sposób na użycie funkcji SUMIFS w formule.

Spójrz na ten przykład, w którym mamy dwa warunki: musimy znać kwotę sprzedaży mięso V południowy obszar (z kolumny A) (z kolumny C).

Oto formuła, której możesz użyć, aby to osiągnąć:

Wynik to 14,719.

Rozważ przestudiowanie wszystkich części formuły.

SUMA jest wzorem arytmetycznym. Oblicza liczby, które w tym przypadku znajdują się w kolumnie D. Pierwszą rzeczą, którą musisz zrobić, to określić lokalizację liczby:

=SUMY(D2:D11;

Innymi słowy, należy wprowadzić formułę sumującą liczby w kolumnie, jeśli spełniają one warunki. Że zakres komórek jest pierwszy argument w tym wzorze znajduje się pierwsza część danych, których funkcja wymaga jako danych wejściowych.

Następnie musisz znaleźć dane spełniające oba warunki, więc wprowadź swój pierwszy warunek, określając dla funkcji lokalizację danych (a2:a11), a także który warunek to - najlepiej „Południe”. Zwróć uwagę na przecinki pomiędzy poszczególnymi argumentami:

= SUMA(D2:D11,A2:A11,"Południe",

Cudzysłowy wokół słowa „południowe” wskazują, że są to dane tekstowe.

Na koniec wprowadź argumenty drugiego warunku — zakres komórek (C2:C11) zawierający słowo „Mięso” oraz samo słowo (ujęte w cudzysłów), aby program Excel mógł je dopasować. Uzupełnij formułę nawiasem zamykającym ) i naciśnij ENTER. Wynik, naciśnij ponownie - 14,719.

=SUMY(D2:D11,a2:a11;"Południe",C2:C11;"Mięso")

Jeśli po wejściu do funkcji SUMIFS w programie Microsoft Excel nie pamiętasz argumentów, pomoc jest pod ręką. Po wejściu = SUMY (, autouzupełnianie formuł pojawi się pod formułami na liście argumentów w prawidłowej kolejności.

Jeśli spojrzysz na ilustrację przedstawiającą użycie funkcji autouzupełniania formuły i listę argumentów, w naszym przykładzie zakres_sumy to zakres D2:D11, czyli kolumna zawierająca liczby, które chcesz zsumować; warunek_zakres1 to zakres A2.A11, czyli kolumna danych zawierająca warunek 1- "Południowy".

Po wprowadzeniu formuły pozostałe argumenty (nie pokazane tutaj) pojawią się w funkcji Autouzupełniania formuły; warunki_zakresu2 to zakres C2:C11, który jest kolumną danych zawierającą warunek 2- "Mięso".

Jeśli klikniesz SUMY podczas automatycznego uzupełniania formuły otworzy się artykuł z pomocą.

Spróbuj ćwiczyć

Jeśli chcesz poeksperymentować z funkcją SUMIFS, oto kilka przykładowych danych i formuł korzystających z tej funkcji.

Możesz pracować z przykładowymi danymi bezpośrednio w tym skoroszycie programu Excel Online. Zmień wartości i formuły lub dodaj własne, aby zobaczyć natychmiastową zmianę wyników.

Skopiuj wszystkie komórki z poniższej tabeli i wklej je do komórki A1 nowego arkusza Excel. Możesz dostosować szerokość kolumn, aby formuły wyglądały lepiej.