Sumowanie po zakresie miesiąca gdy miesiąc jest tekstem — 2 rozwiązania — porada #94

Jak obliczyć sumę sprzedaży w okresie 1 miesiąca zapisanego jako tekst?


Sumowanie po zakresie miesiąca gdy miesiąc jest tekstem — 2 rozwiązania — porada #94

Sumowanie po zakresie miesiąca gdy miesiąc jest tekstem - 2 rozwiązania - porada #94

W tym filmie czeka nas trudne zadanie ponieważ chcemy sumować wartości sprzedaży po zakresie miesiąca, który jest podany w formie tekstu, czyli czeka nas najpierw zadanie przekształcenia miesiąca w formie tekstu na zrozumiałą dla Excela datę.

Pokarzemy tu 2 możliwe rozwiązania.

Pierwsze wykorzystuje funkcję SUMA.WARUNKÓW, a najtrudniejsze w nim jest zamiana miesiąca tekstowego na datę. Przede wszystkim potrzebujemy 1 danego miesiąca konkretnego roku. Ponieważ są to wartości niezmienne możemy je wpisać na stałe i połączyć ampersandami z miesiącem. Otrzymamy po oszacowaniu formę daty, która wygląda tak:

“1cze2013”

Nie jest to może najelegantszy format daty, ale Excel go rozumie i to nam wystarczy.

Ostatni dzień miesiąca uzyskamy dzięki wstawieniu tego połączenia do odpowiedniej funkcji:

NR.SER.OST.DN.MIES(1&G$10&2013;0)

Łączymy wszystkie warunki w funkcji SUMA.WARUNKÓW i otrzymujemy następującą formułę:

=SUMA.WARUNKÓW($D$2:$D$325;$B$2:$B$325;$F11;$A$2:$A$325;”>=1″&G$10&2013″;$A$2:$A$325;”<=”&NR.SER.OST.DN.MIES(1&G$10&2013;0))

Drugie rozwiązanie wykorzysta funkcję SUMA.ILOCZYNÓW. Jako jeden z argumentów wstawimy cały zakres sprzedaży ($D$2:$D$325), kolejnym będzie sprawdzenie miesiąca, ale podejdziemy do tego inaczej niż w pierwszym rozwiązaniu.

Ponieważ mamy tylko jeden rok możemy sprawdzić, czy zgadza się miesiąc i to wystarczy. Żeby to zrobić wykorzystamy funkcję TEKST i zapis formatowania niestandardowego dla skróconej nazwy miesiąca “mmm”.

Musimy to zrobić na całym obszarze i przyrównać do wartości miesiąca z nagłówka.

TEKST($A$2:$A$325;“mmm”)=G$1

taka formuła zwróci nam tablicę PRAWDA i FAŁSZ , których niestety funkcja SUMA.ILOCZYNÓW nie rozumie dlatego potrzebujemy zamienić je na 1 i 0. Najszybciej uzyskamy to dzięki podwójnemu znakowi minus (–). Musimy pamiętać o jednym, że zmiana znaków jako operacja matematyczna odbywa się przed porównaniem, więc porównanie musimy wziąć w nawiasy i dopiero przed nimi postawić znaki minus:

–(TEKST($A$2:$A$325;“mmm”)=G$1)

Analogicznie robimy dla warunku sprawdzania sprzedawcy:

–($B$2:$B$325=$F2)

łącząc wszystkie argumenty uzyskujemy formułę:

=SUMA.ILOCZYNÓW($D$2:$D$325;–($B$2:$B$325=$F2);–(TEKST($A$2:$A$325;“mmm”)=G$1))

P.S.

Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić napisz do mnie o tym w komentarzu pod spodem albo bezpośrednio. W miarę możliwości odpowiem na Twoje pytanie.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Sumowanie po zakresie miesiąca gdy miesiąc jest tekstem — 2 rozwiązania — porada #94

Do tego artykułu dotarto przez wyszukiwarkę Google m.in. poprzez poniższe frazy kluczowe:

  • excel suma iloczynów
  • suma iloczynow w excelu

Sumowanie po zakresie miesiąca na podstawie 1 daty SUMA.WARUNKÓW — porada #93

Jak obliczyć sumę sprzedaży w okresie 1 miesiąca mając tylko 1 datę w Excelu?


Sumowanie po zakresie miesiąca na podstawie 1 daty SUMA.WARUNKÓW — porada #93

Sumowanie po zakresie miesiąca na podstawie 1 daty SUMA.WARUNKÓW - porada #93

Co w sytuacji kiedy chcemy dokonać sumowania po zakresie jednego miesiąca i mamy tylko 1 komórkę, która jak zdaje się zawiera tylko nazwę miesiąca?

W tym filmie omówimy prosty przypadek, gdy wyświetlająca się nazwa miesiąca wynika z formatowania niestandardowego “mmm”, a w komórce jest w rzeczywistości data.

W takiej sytuacji sytuacja jest prosta bo potrzeba tylko na podstawie tej daty (pierwszego dnia miesiąca) stworzyć warunek większe lub równe tej wartości oraz drugi warunek mówiący, że mniejsze lub równe wartości ostatniego dnia miesiąca. Do tego drugiego warunku wykorzystamy funkcję NR.SER.OST.DN.MIES

Dodatkowo jeszcze dokładamy jeszcze 1 warunek sprawdzający do naszego przykładu — kryterium sprzedawcy. Wszystkie warunki sprowadzają się do jednej formuły:

=SUMA.WARUNKÓW($D$2:$D$325;$A$2:$A$325;”>=”&G$1;$A$2:$A$325;”<=”&NR.SER.OST.DN.MIES(G$1;0);$B$2:$B$325;$F2)

P.S.

Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić napisz do mnie o tym w komentarzu pod spodem albo bezpośrednio. W miarę możliwości odpowiem na Twoje pytanie.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Sumowanie po zakresie miesiąca na podstawie 1 daty SUMA.WARUNKÓW — porada #93

Sumowanie sprzedaży w wybranym zakresie dat — porada #92

Jak dokonać sumy sprzedaży w określonym zakresie dat w Excelu?


Sumowanie sprzedaży w wybranym zakresie dat — porada #92

Sumowanie sprzedaży w wybranym zakresie dat - porada #92

Zakładamy, że chcemy z sumować wszystkie wartości sprzedaży, które miały miejsce w określonym przedziale czasowym, czyli od daty początkowej do daty końcowej. Wynika z tego, że potrzebujemy 2 warunków i sumowania, czyli wykorzystamy funkcję SUMA.WARUNKÓW.

Na początek sprawdzimy sobie jaka jest najstarsza data (minimalna) w naszym zakresie i najmłodsza (maksymalna), dzięki odpowiednio funkcji MIN i MAX po całym zakresie dat.

Dzięki temu mamy datę od której chcemy sumować. Dokładnie chcemy sumować po kolejnych miesiącach, więc potrzebujemy stworzyć tabelę początków i końców naszych miesięcy.

Zaczynamy od daty wyznaczonej przez funkcję MIN i na jej podstawie wyznaczamy ostatni dzień miesiąca dzięki funkcji:

=NR.SER.OST.DN.MIES(G5;0)

parametr zero informuje nas, że chcemy mieć ostatni dzień z tego miesiąca. Gdybyśmy wstawili np: –1 (minus jeden) to uzyskalibyśmy ostatni dzień poprzedniego miesiąca. Jeśli wstawimy tam 1 (jedynkę) to otrzymamy ostatni dzień z następnego miesiąca.

Kolejnym krokiem jest stworzenie 1 dnia kolejnego miesiąca. Najprościej zrobić to dodają 1 do ostatniego dnia pierwszego miesiąca.

Analogicznie wypełniamy dane do końca dat w naszym zakresie. Końcową datę możemy zostawić taką jaka wynika z naszych obliczeń albo przypisać wartości z funkcji MAX.

Teraz możemy już wstawiać funkcję SUMA.WARUNKÓW z odpowiednimi warunkami mniejsze i równe oraz większe i równe w nawiązaniu do dat.

=SUMA.WARUNKÓW($E$2:$E$325;$A$2:$A$325;”>=”&G5;$A$2:$A$325;”<=”&H5)

Uzyskaliśmy sumę sprzedaży w zakresie 2 dat.
Nasze daty możemy dowolnie modyfikować jeśliby zaszła taka potrzeba.

P.S.

Jeśli chcesz dowiedzieć się więcej na temat Excela lub nie wiesz jak coś zrobić napisz do mnie o tym w komentarzu pod spodem albo bezpośrednio. W miarę możliwości odpowiem na Twoje pytanie.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Sumowanie sprzedaży w wybranym zakresie dat — porada #92

SUMA.JEŻELI i SUMA.WARUNKÓW mieszane odwołania i zakresy w tabelach — sztuczki #48

Jak wykorzystać SUMA.JEŻELI i SUMA.WARUNKÓW aby zsumować z 1 lub więcej kryteriów?


SUMA JEŻELI i SUMA WARUNKÓW mieszane odwołania i zakresy w tabelach — sztuczki #48

Dodawanie z jednym lub więcej kryteriów:

      1. Dowiedz się, jak dodawać z jednym lub więcej kryterium wykorzystanie funkcji SUMA.JEŻELI i SUMA.WARUNKÓW.
      2. Opcja Excela do tworzenia tabel po to by uzyskać dynamiczne zakresy, które będą rosnąć przy dodawania nowych rekordów.
      3. Zobacz jak korzystać z regularnych zakresów zamiast odwołań do nazewnictwa w tabelach, tak aby można skopiować formułę w bok, bez przesunięcia się nazw tabeli do innych kolumn.
      4. Dowiedz się, jak stworzyć skrzyżowaną tabelę aby dodać na podstawie 2 kryteriów.
      5. Dowiedz się, jak korzystać z mieszanych odwołań do komórek, aby przyspieszyć czas tworzenia formuł.

P.S.

Wpis na podstawie Excel Magic Trick 1024

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.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — SUMA JEŻELI i SUMA WARUNKÓW mieszane odwołania i zakresy w tabelach — sztuczki #48

Do tego artykułu dotarto przez wyszukiwarkę Google m.in. poprzez poniższe frazy kluczowe:

  • yhs-ddc_bd
  • suma jeżeli
  • suma jeżeli excel
  • dodawanie pod jednym warunkiem excel
  • suma jeżeli data

Sumowanie po wielu kryteriach SUMA.WARUNKÓW — pytania widzów #4

Jak sumować uwzględniając wiele kryteriów?


Sumowanie po wielu kryteriach SUMA.WARUNKÓW — pytania widzów #4

Roman miał tabelę z hurtowniami i ich zarobkami uporządkowanymi według dat. Robił sumę zysków dla poszczególnym hurtowni wykorzystując funkcję SUMA.JEŻELI. Jego funkcja miała taką strukturę:

SUMA.JEŻELI(Zakres z nazwami hurtowni; Nazwa hurtowni po której chce sumować; Zakres po którym będzie sumowane po sprawdzeniu warunków)

Ta struktura przekłada się na filmie w odwołania do odpowiednich komórek.

SUMA.JEŻELI(D3:D14;I2;E3:E14)

Tabela hurtowni i funkcja SUMA.JEŻELI

Tabela hurtowni i funkcja SUMA.JEŻELI

Ta funkcja sprawdzała się, gdy chciał sumować po jednym warunku (nazwie hurtowni), ale gdy zaszła potrzeba do tego dołożyć jeszcze daty, ta formuła nie dawała rady. Należało wykorzystać funkcję SUMA.WARUNKÓW. Ta funkcja pozwala na ustawienie wielu warunków jednocześnie.

Struktura funkcji SUMA.WARUNKÓW pozwala na ustawienie wielu warunków, zawsze ustawianych w pary z zakresami, to znaczy, że 1 parametrem funkcji jest zakres, po którym będzie liczona suma, a następnie podajesz parę:

  • zakres po którym sprawdzany ma być warunek,
  • warunek.

Te dwa parametry zawsze muszą być podawane w parze, a takich par możesz dodać bardzo dużo.

Przykładowy zapis dla sprawdzenia nazwy hurtowni i ustawienia daty pomiędzy 2012-11-01, a 2012-10-01 wygląda tak
=SUMA.WARUNKÓW(E3:E14;D3:D14;I2;B3:B14;“<2012-11-01";B3:B14;">2012–10-01″)

SUMA.WARUNKÓW daty wpisane ręcznie

SUMA.WARUNKÓW daty wpisane ręcznie

      1 parametr (E3:E14) to zakres, po którym będzie robiona suma,
      2 parametr (D3:D14) to zakres, po którym będzie sprawdzany 1 warunek (nazwa hurtowni),
      3 parametr (I2) to nazwa hurtowni, po którym będzie sprawdzany 1 zakres,
      4 parametr (B3:B14) to zakres, po którym będzie sprawdzany 2 warunek (mniejsze od daty, czyli wcześniej niż data),
      5 parametr (“<2012-11-01") to odpowiednio znak mniejszości i data w cudzysłowie, po której będzie przeszukiwany 2 zakres,
      6 parametr (B3:B14) to zakres, po którym będzie sprawdzany 3 warunek (mniejsze od daty, czyli wcześniej niż data),
      7 parametr (">2012–10-01″) to odpowiednio znak mniejszości i data w cudzysłowie, po której będzie przeszukiwany 3 zakres.

W podobny sposób możesz dodawać kolejne warunki — zawsze w parach — zakres_sprawdzania_warunku i warunek

Możesz zauważyć, że w przykładzie powyżej 3 parametr jest zapisany jako odwołanie do komórki, a 5 i 7 parametr są wpisane z cudzysłowach. Jeżeli chciałbyś, żeby były one pokazane jako odwołania do komórek powinny wyglądać odpowiednio tak:
”<”&J6 i “>”&J7
czyli połączenie (&) znaku mniejsze lub większe w cudzysłowie z odwołaniem do komórki

Cała formuła wtedy wygląda odpowiednio:

=SUMA.WARUNKÓW(E3:E14;D3:D14;I2;B3:B14;”<”&J6;B3:B14;”>”&J7)

SUMA.WARUNKÓW daty pobierane z komórek

SUMA.WARUNKÓW daty pobierane z komórek

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.

Excel i Adam - kontakt

Bezpośredni odnośnik do filmu na youtube — Sumowanie po wielu kryteriach SUMA.WARUNKÓW — pytania widzów #4

Do tego artykułu dotarto przez wyszukiwarkę Google m.in. poprzez poniższe frazy kluczowe:

  • excel suma warunki pionowe i poziome
  • excel wyszukaj pionowo w wielu zakresach
  • sql sumowanie wiele sumowań
  • suma warunków dla dat