Elektroda.pl
Elektroda.pl
X
Proszę, dodaj wyjątek dla www.elektroda.pl do Adblock.
Dzięki temu, że oglądasz reklamy, wspierasz portal i użytkowników.

Excel 2007, sumowanie danych w zależności od dat

11 Kwi 2011 18:58 10881 11
  • Poziom 10  
    Witam. Mam problem z wyszukaniem danych oraz ich zsumowaniem w zależności od dat pod jakimi zostały zapisane. Arkusz wygląda tak:

    numer identyfikacyjny; data w formacie (rrrr-mm-dd); kwota1; kwota2;
    123; 2009-05-30; 100; 10;
    123; 2008-04-19; 200; 20;
    123; 2008-04-13; 300; 30;
    123; 2009-06-11; 400; 40;

    Dane są powiedzmy z okresu 5 lat.

    Teraz muszę zsumować dla danego miesiąca w odpowiednim mu roku wartości kwota1, oraz oddzielnie kwota 2.
    Tj. w kwietniu 2008, numer identyfikacyjny 123 ma sumę kwota1=500 oraz sumę kwota2=50.

    Znalazłem taki temat: https://www.elektroda.pl/rtvforum/topic1639467.html jednakże niestety przy moim problemie podane w nim rozwiąznie nie skutkuje. Próbowałem zmodyfikować formułę wzbogacając ją o rok, ale bez powodzenia, formuła =SUMA(JEŻELI()) zwracała wartości 0 - czyli dla fałszu.

    Czy ktoś mógłby wspomóc radą jak rozwiązać ten "wydaje się" prosty problem?
  • Pomocny post
    Moderator Programowanie
    Np taką funkcją tablicową (nie wiem dlaczego nie działa ORAZ):

    Excel 2007, sumowanie danych w zależności od dat
  • Pomocny post
    Poziom 22  
    Zamiast ORAZ można użyć iloczynu warunków i wtedy nie trzeba zagnieżdżać JEŻELI:

    Excel 2007, sumowanie danych w zależności od dat
  • Poziom 10  
    Dziękuję za odpowiedzi.
    Faktycznie już "potrafię" zsumować odpowiednio po kryterium daty. Natomiast pojawił się kolejny problem. Otóż być może powinienem o tym napisać od razu, że to sumowanie jest tylko jednym z kilku elementów, które składają się na większą całość operacji potrzebnych do uzyskania żądanego efektu.

    Mianowicie dążę do tego by maksymalnie wykorzystać możliwości excela a przy tym się nie narobić. Wiadomo - człowiek leniwy - jeśli może coś zrobić za nas maszyna to czemu nie?...

    I teraz sytuacja wygląda tak: dane o których pisałem w pierwszym poście nie są przeze mnie wpisywane ręcznie do tego arkusza (którego nazwijmy zbiorczym) a kopiuję je z innych plików excela korzystając z funkcji:
    ="='ścieżka_do_pliku_oraz_adres_komórki'" i poprzez operację kopiuj -> wklej specjalnie -> wartości a potem tekst jako kolumny pobieram dane z innych plików. Struktura tych plików jest taka sama więc przygotowałem sobie "na zaś" te formuły, dla plików których jeszcze fizycznie nie ma. Więc teraz część arkusza wygląda tak, że mam dane pobrane z plików, które już są utworzone oraz wiersze gdzie są w komórkach #ADR!. Jak utworzę plik to błędne odwołanie zniknie a pojawią się konkretne wartości. I teraz kończąc ten przydługawy wywód pojawia się następujący problem. Korzystając z Waszych podpowiedzi chcę, zbudować funkcję =Suma(Jeżeli(....)) taką by uwzględniać od razu te komórki, w których jest teraz #ADR!, czyli danego pliku nie ma. W chwili obecnej jeśli wezmę cały zakres danych do tablicy to niestety ale w wyniku sumowania po datach nie mam sumy na podstawie dostępnych danych tylko #ADR! - jak sądzę, ze względu na to, że excel "się gubi" przy próbie odczytania wartości z komórek gdzie jest #ADR!. Teraz pytanie jak ten problem obwarować? Próbowałem zrobić jeszcze jedną funkcję jeżeli przed sumą na zasadzie =Jeżeli((a1:a100)="#ADR!");"";Sumowanie które zaproponowaliście wyżej;). Ale bez rezultatu. Macie jakiś pomysł?
  • Pomocny post
    Moderator Programowanie
    Wykorzystaj funkcję (E2007): JEŻELI.BŁĄD(wartość;wartość jeśli błąd). Pierwszym argumentem będzie formuła, którą masz już gotową. Drugim zero...
  • Poziom 10  
    adamas_nt napisał:
    Wykorzystaj funkcję (E2007): JEŻELI.BŁĄD(wartość;wartość jeśli błąd). Pierwszym argumentem będzie formuła, którą masz już gotową. Drugim zero...


    Z tym, że jeżeli program napotka podczas realizacji funkcji na błąd (#ADR!) to wtedy od razu wypisze "0", mimo tego, że w części komórek są poprawne wartości, które powinien zliczyć. To nie jest poprawne rozwiązanie.
    Wydaje się, że powinna być poprawna taka funkcja, która po prostu nie bierze pod uwagę komórki jeśli jej wartością jest błąd (#ADR!), ale zlicza resztę komórek poprawnie. Da się zmajstrować taką funkcję?

    EDIT:
    Dla jasności sytuacji podrzucam zrzut jak wygląda mój arkusz. Wierszy jest w sumie powiedzmy 311. Do wiersza 21. są pobrane dane z plików, które zostały już utworzone. Wiersze 21-311 są jak widać #ADR! ze względu na brak takiego pliku. Po prawo są sumy dla lat 2010 i 2011. W komórce K3 utworzyłem funkcję:
    =JEŻELI.BŁĄD(SUMA(JEŻELI((MIESIĄC($B$3:$B$311)=$J3)*(ROK($B$3:$B$311)=$I$3);C$3:C$311;""));0)

    Potem przeciągnąłem ją dla całego roku 2010 dla ~oraz #.

    Excel 2007, sumowanie danych w zależności od dat
  • Poziom 30  
    Da się, wykorzystując funkcję JEŻELI.BŁĄD w komórkach w których jest szansa na wynik (#ADR!).
  • Poziom 10  
    No właśnie jak widać wyżej na screenie nie do końca. Chyba, że źle buduję funkcję?
  • Poziom 30  
    Chodzi o to żebyś wykorzystał tą funkcję w każdej z komórek w kolumnach B, C i D
  • Poziom 10  
    Byłem nieobecny trochę, teraz odświeżam temat.
    Nie wiem do końca w jaki sposób mam użyć funkcji Jeżeli.Błąd w kolumnach B,C,D.
    W tej chwili moja formuła dla komórek kolumn B wygląda tak:
    ="='ścieżka\["&A3&.xlsm]Arkusz1'!$I$1"
    w kolumnie A, poczynając od komórki A3 mam nazwy plików w formacie tekstowym. Kolumny B i C wyglądają analogicznie, zmiana jest tylko w $I$1 - są odpowiednio inne komórki. Następnie robię tak, że zaznaczam całą kolumnę, kopiuj -> wklej specjalnie -> wartości, potem 'dane -> tekst jako kolumny -> zakończ' i jest. Te pliki które już istnieją to są odczytywane i wartości wskakują do komórek w kolumnach B, C i D. W przypadku gdy nie ma danego pliku pojawia się właśnie #ADR!.
    Próbowałem sklecić funkcję jako:
    =JEŻELI.BŁĄD("='ścieżka\["&A3&".xlsm]Arkusz1'!$I$1";0)
    ale niestety bez powodzenia, po zrobieniu operacji wklej specjalnie ucinany jest fragment funkcji Jeżeli.Błąd i dalej excel działa tak jak w przypadku opisanym na początku postu.
  • Pomocny post
    Poziom 30  
    Ciężko się trochę połapać w tych twoich powiązaniach między arkuszami. Nie bardzo rozumiem ten opis odnośnie wklejania itd. Przydałby się twój arkusz do analizy. Jeżeli jednak z jakiegoś powodu nie możesz zastosować funkcji JEŻELI.BŁĄD w kolumnach B, C i D, to przed sumowaniem skopiuj ich zawartość wpierw do kolumn E, F i G przy pomocy tej funkcji takich jak =JEŻELI.BŁĄD(B1;0) powielonych we wszystkich komórkach tych kolumn i sumuj dopiero kolumny E,F i G.
  • Poziom 10  
    O!
    Działa, że też sam na to nie wpadłem. Ale to zazwyczaj tak jest, że z tyłu sali lepiej widać błąd niż gdy się stoi przy tablicy z kredą w ręku.

    Wykonałem kopiowanie na zasadzie =Jeżeli.błąd(b1;0) i już mam poprawne sumowania. Dziękuję serdecznie za poradę. Przyznaję punkty.