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

No Comments

Jak obli­czyć sumę sprze­daży w okre­sie 1 mie­siąca zapi­sa­nego jako tekst?


Sumo­wa­nie po zakre­sie mie­siąca gdy mie­siąc jest tek­stem — 2 roz­wią­za­nia — porada #94

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

W tym fil­mie czeka nas trudne zada­nie ponie­waż chcemy sumo­wać war­to­ści sprze­daży po zakre­sie mie­siąca, który jest podany w for­mie tek­stu, czyli czeka nas naj­pierw zada­nie prze­kształ­ce­nia mie­siąca w for­mie tek­stu na zro­zu­miałą dla Excela datę.

Poka­rzemy tu 2 moż­liwe rozwiązania.

Pierw­sze wyko­rzy­stuje funk­cję SUMA.WARUNKÓW, a naj­trud­niej­sze w nim jest zamiana mie­siąca tek­sto­wego na datę. Przede wszyst­kim potrze­bu­jemy 1 danego mie­siąca kon­kret­nego roku. Ponie­waż są to war­to­ści nie­zmienne możemy je wpi­sać na stałe i połą­czyć amper­san­dami z mie­sią­cem. Otrzy­mamy po osza­co­wa­niu formę daty, która wygląda tak:

“1cze2013”

Nie jest to może naje­le­gant­szy for­mat daty, ale Excel go rozu­mie i to nam wystarczy.

Ostatni dzień mie­siąca uzy­skamy dzięki wsta­wie­niu tego połą­cze­nia do odpo­wied­niej funkcji:

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

Łączymy wszyst­kie warunki w funk­cji SUMA.WARUNKÓW i otrzy­mu­jemy nastę­pu­ją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))

Dru­gie roz­wią­za­nie wyko­rzy­sta funk­cję SUMA.ILOCZYNÓW. Jako jeden z argu­men­tów wsta­wimy cały zakres sprze­daży ($D$2:$D$325), kolej­nym będzie spraw­dze­nie mie­siąca, ale podej­dziemy do tego ina­czej niż w pierw­szym rozwiązaniu.

Ponie­waż mamy tylko jeden rok możemy spraw­dzić, czy zga­dza się mie­siąc i to wystar­czy. Żeby to zro­bić wyko­rzy­stamy funk­cję TEKST i zapis for­ma­to­wa­nia nie­stan­dar­do­wego dla skró­co­nej nazwy mie­siąca “mmm”.

Musimy to zro­bić na całym obsza­rze i przy­rów­nać do war­to­ści mie­siąca z nagłówka.

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

taka for­muła zwróci nam tablicę PRAWDA i FAŁSZ , któ­rych nie­stety funk­cja SUMA.ILOCZYNÓW nie rozu­mie dla­tego potrze­bu­jemy zamie­nić je na 1 i 0. Naj­szyb­ciej uzy­skamy to dzięki podwój­nemu zna­kowi minus (–). Musimy pamię­tać o jed­nym, że zmiana zna­ków jako ope­ra­cja mate­ma­tyczna odbywa się przed porów­na­niem, więc porów­na­nie musimy wziąć w nawiasy i dopiero przed nimi posta­wić znaki minus:

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

Ana­lo­gicz­nie robimy dla warunku spraw­dza­nia sprzedawcy:

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

łącząc wszyst­kie argu­menty uzy­sku­jemy 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 dowie­dzieć się wię­cej na temat Excela lub nie wiesz jak coś zro­bić napisz do mnie o tym w komen­ta­rzu pod spodem albo bez­po­śred­nio. W miarę moż­li­wo­ści odpo­wiem na Twoje pytanie.

Excel i Adam - kontakt

Bez­po­średni odno­śnik do filmu na youtube — Sumo­wa­nie po zakre­sie mie­siąca gdy mie­siąc jest tek­stem — 2 roz­wią­za­nia — porada #94

Do tego arty­kułu dotarto przez wyszu­ki­warkę Google m.in. poprzez poniż­sze frazy kluczowe:

  • licz jeżeli data i jesz­cze jeden warunek
  • excel suma jeżeli data

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

No Comments

Jak obli­czyć sumę sprze­daży w okre­sie 1 mie­siąca mając tylko 1 datę w Excelu?


Sumo­wa­nie po zakre­sie mie­siąca na pod­sta­wie 1 daty SUMA.WARUNKÓW — porada #93

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

Co w sytu­acji kiedy chcemy doko­nać sumo­wa­nia po zakre­sie jed­nego mie­siąca i mamy tylko 1 komórkę, która jak zdaje się zawiera tylko nazwę miesiąca?

W tym fil­mie omó­wimy pro­sty przy­pa­dek, gdy wyświe­tla­jąca się nazwa mie­siąca wynika z for­ma­to­wa­nia nie­stan­dar­do­wego “mmm”, a w komórce jest w rze­czy­wi­sto­ści data.

W takiej sytu­acji sytu­acja jest pro­sta bo potrzeba tylko na pod­sta­wie tej daty (pierw­szego dnia mie­siąca) stwo­rzyć waru­nek więk­sze lub równe tej war­to­ści oraz drugi waru­nek mówiący, że mniej­sze lub równe war­to­ści ostat­niego dnia mie­siąca. Do tego dru­giego warunku wyko­rzy­stamy funk­cję NR.SER.OST.DN.MIES

Dodat­kowo jesz­cze dokła­damy jesz­cze 1 waru­nek spraw­dza­jący do naszego przy­kładu — kry­te­rium sprze­dawcy. Wszyst­kie warunki spro­wa­dzają się do jed­nej 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 dowie­dzieć się wię­cej na temat Excela lub nie wiesz jak coś zro­bić napisz do mnie o tym w komen­ta­rzu pod spodem albo bez­po­śred­nio. W miarę moż­li­wo­ści odpo­wiem na Twoje pytanie.

Excel i Adam - kontakt

Bez­po­średni odno­śnik do filmu na youtube — Sumo­wa­nie po zakre­sie mie­siąca na pod­sta­wie 1 daty SUMA.WARUNKÓW — porada #93

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

No Comments

Jak doko­nać sumy sprze­daży w okre­ślo­nym zakre­sie dat w Excelu?


Sumo­wa­nie sprze­daży w wybra­nym zakre­sie dat — porada #92

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

Zakła­damy, że chcemy z sumo­wać wszyst­kie war­to­ści sprze­daży, które miały miej­sce w okre­ślo­nym prze­dziale cza­so­wym, czyli od daty począt­ko­wej do daty koń­co­wej. Wynika z tego, że potrze­bu­jemy 2 warun­ków i sumo­wa­nia, czyli wyko­rzy­stamy funk­cję SUMA.WARUNKÓW.

Na począ­tek spraw­dzimy sobie jaka jest naj­star­sza data (mini­malna) w naszym zakre­sie i naj­młod­sza (mak­sy­malna), dzięki odpo­wied­nio funk­cji MIN i MAX po całym zakre­sie dat.

Dzięki temu mamy datę od któ­rej chcemy sumo­wać. Dokład­nie chcemy sumo­wać po kolej­nych mie­sią­cach, więc potrze­bu­jemy stwo­rzyć tabelę począt­ków i koń­ców naszych miesięcy.

Zaczy­namy od daty wyzna­czo­nej przez funk­cję MIN i na jej pod­sta­wie wyzna­czamy ostatni dzień mie­siąca dzięki funkcji:

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

para­metr zero infor­muje nas, że chcemy mieć ostatni dzień z tego mie­siąca. Gdy­by­śmy wsta­wili np: –1 (minus jeden) to uzy­ska­li­by­śmy ostatni dzień poprzed­niego mie­siąca. Jeśli wsta­wimy tam 1 (jedynkę) to otrzy­mamy ostatni dzień z następ­nego miesiąca.

Kolej­nym kro­kiem jest stwo­rze­nie 1 dnia kolej­nego mie­siąca. Naj­pro­ściej zro­bić to dodają 1 do ostat­niego dnia pierw­szego miesiąca.

Ana­lo­gicz­nie wypeł­niamy dane do końca dat w naszym zakre­sie. Koń­cową datę możemy zosta­wić taką jaka wynika z naszych obli­czeń albo przy­pi­sać war­to­ści z funk­cji MAX.

Teraz możemy już wsta­wiać funk­cję SUMA.WARUNKÓW z odpo­wied­nimi warun­kami mniej­sze i równe oraz więk­sze i równe w nawią­za­niu do dat.

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

Uzy­ska­li­śmy sumę sprze­daży w zakre­sie 2 dat.
Nasze daty możemy dowol­nie mody­fi­ko­wać jeśliby zaszła taka potrzeba.

P.S.

Jeśli chcesz dowie­dzieć się wię­cej na temat Excela lub nie wiesz jak coś zro­bić napisz do mnie o tym w komen­ta­rzu pod spodem albo bez­po­śred­nio. W miarę moż­li­wo­ści odpo­wiem na Twoje pytanie.

Excel i Adam - kontakt

Bez­po­średni odno­śnik do filmu na youtube — Sumo­wa­nie sprze­daży w wybra­nym zakre­sie dat — porada #92

Do tego arty­kułu dotarto przez wyszu­ki­warkę Google m.in. poprzez poniż­sze frazy kluczowe:

  • sumo­wa­nie wg zakresu dat

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

No Comments

Jak wyko­rzy­stać SUMA.JEŻELI i SUMA.WARUNKÓW aby zsu­mo­wać z 1 lub wię­cej kryteriów?


SUMA JEŻELI i SUMA WARUNKÓW mie­szane odwo­ła­nia i zakresy w tabe­lach — sztuczki #48

Doda­wa­nie z jed­nym lub wię­cej kryteriów:

      1. Dowiedz się, jak doda­wać z jed­nym lub wię­cej kry­te­rium wyko­rzy­sta­nie funk­cji SUMA.JEŻELI i SUMA.WARUNKÓW.
      2. Opcja Excela do two­rze­nia tabel po to by uzy­skać dyna­miczne zakresy, które będą rosnąć przy doda­wa­nia nowych rekordów.
      3. Zobacz jak korzy­stać z regu­lar­nych zakre­sów zamiast odwo­łań do nazew­nic­twa w tabe­lach, tak aby można sko­pio­wać for­mułę w bok, bez prze­su­nię­cia się nazw tabeli do innych kolumn.
      4. Dowiedz się, jak stwo­rzyć skrzy­żo­waną tabelę aby dodać na pod­sta­wie 2 kryteriów.
      5. Dowiedz się, jak korzy­stać z mie­sza­nych odwo­łań do komó­rek, aby przy­spie­szyć czas two­rze­nia formuł.

P.S.

Wpis na pod­sta­wie Excel Magic Trick 1024

Jeśli chcesz dowie­dzieć się wię­cej na temat Excela lub nie wiesz jak coś zro­bić do mnie o tym w komen­ta­rzu pod spodem albo napisz do mnie bez­po­śred­nio, ja w miarę moż­li­wo­ści odpo­wiem na Twoje pytanie.

Excel i Adam - kontakt

Bez­po­średni odno­śnik do filmu na youtube — SUMA JEŻELI i SUMA WARUNKÓW mie­szane odwo­ła­nia i zakresy w tabe­lach — sztuczki #48

Do tego arty­kułu dotarto przez wyszu­ki­warkę Google m.in. poprzez poniż­sze frazy kluczowe:

  • excel suma jeżeli
  • licz jeżeli z dwoma warunkami
  • suma jeżeli dynamiczne
  • suma jeżeli odwo­ła­nie do komórki
  • sumo­wa­nie tabel w excelu

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

No Comments

Jak sumo­wać uwzględ­nia­jąc wiele kryteriów?


Sumo­wa­nie po wielu kry­te­riach SUMA.WARUNKÓW — pyta­nia widzów #4

Roman miał tabelę z hur­tow­niami i ich zarob­kami upo­rząd­ko­wa­nymi według dat. Robił sumę zysków dla poszcze­gól­nym hur­towni wyko­rzy­stu­jąc funk­cję SUMA.JEŻELI. Jego funk­cja miała taką strukturę:

SUMA.JEŻELI(Zakres z nazwami hur­towni; Nazwa hur­towni po któ­rej chce sumo­wać; Zakres po któ­rym będzie sumo­wane po spraw­dze­niu warunków)

Ta struk­tura prze­kłada się na fil­mie w odwo­ła­nia do odpo­wied­nich komórek.

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

Tabela hurtowni i funkcja SUMA.JEŻELI

Tabela hur­towni i funk­cja SUMA.JEŻELI

Ta funk­cja spraw­dzała się, gdy chciał sumo­wać po jed­nym warunku (nazwie hur­towni), ale gdy zaszła potrzeba do tego doło­żyć jesz­cze daty, ta for­muła nie dawała rady. Nale­żało wyko­rzy­stać funk­cję SUMA.WARUNKÓW. Ta funk­cja pozwala na usta­wie­nie wielu warun­ków jednocześnie.

Struk­tura funk­cji SUMA.WARUNKÓW pozwala na usta­wie­nie wielu warun­ków, zawsze usta­wia­nych w pary z zakre­sami, to zna­czy, że 1 para­me­trem funk­cji jest zakres, po któ­rym będzie liczona suma, a następ­nie poda­jesz parę:

  • zakres po któ­rym spraw­dzany ma być warunek,
  • waru­nek.

Te dwa para­me­try zawsze muszą być poda­wane w parze, a takich par możesz dodać bar­dzo dużo.

Przy­kła­dowy zapis dla spraw­dze­nia nazwy hur­towni i usta­wie­nia 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 wpi­sane ręcznie

      1 para­metr (E3:E14) to zakres, po któ­rym będzie robiona suma,
      2 para­metr (D3:D14) to zakres, po któ­rym będzie spraw­dzany 1 waru­nek (nazwa hurtowni),
      3 para­metr (I2) to nazwa hur­towni, po któ­rym będzie spraw­dzany 1 zakres,
      4 para­metr (B3:B14) to zakres, po któ­rym będzie spraw­dzany 2 waru­nek (mniej­sze od daty, czyli wcze­śniej niż data),
      5 para­metr (“<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 odpo­wied­nio znak mniej­szo­ści i data w cudzy­sło­wie, po któ­rej będzie prze­szu­ki­wany 3 zakres.

W podobny spo­sób możesz doda­wać kolejne warunki — zawsze w parach — zakres_sprawdzania_warunku i warunek

Możesz zauwa­żyć, że w przy­kła­dzie powy­żej 3 para­metr jest zapi­sany jako odwo­ła­nie do komórki, a 5 i 7 para­metr są wpi­sane z cudzy­sło­wach. Jeżeli chciał­byś, żeby były one poka­zane jako odwo­ła­nia do komó­rek powinny wyglą­dać odpo­wied­nio tak:
”<”&J6 i “>”&J7
czyli połą­cze­nie (&) znaku mniej­sze lub więk­sze w cudzy­sło­wie z odwo­ła­niem do komórki

Cała for­muł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 pobie­rane z komórek

P.S.

Jeśli chcesz dowie­dzieć się wię­cej na temat Excela lub nie wiesz jak coś zro­bić do mnie o tym w komen­ta­rzu pod spodem albo napisz do mnie bez­po­śred­nio, ja w miarę moż­li­wo­ści odpo­wiem na Twoje pytanie.

Excel i Adam - kontakt

Bez­po­średni odno­śnik do filmu na youtube — Sumo­wa­nie po wielu kry­te­riach SUMA.WARUNKÓW — pyta­nia widzów #4

Do tego arty­kułu dotarto przez wyszu­ki­warkę Google m.in. poprzez poniż­sze frazy kluczowe:

  • excel zakres kry­te­ria you tube
  • excel sumo­wa­nie komó­rek wię­cej warunków
  • funk­cja suma
  • sumo­wa­nie w ecxcelu 2010 na youtube

Older Entries