[Excel] Suma iloczynów dla wyfiltrowanych danych.

magdaskowska · Post autor: **magdaskowska** » 9 kwie 2009, o 01:08

Pytanie dotyczy Excela (MSOffice 2007)

Mam dwie kolumny danych liczbowych dot. pewnych artykułów: kolumna1 to liczba sztuk, kolumna2: cena. Jeżeli chciałabym obliczyć łączną cenę zakupów, korzystam sobie z funkcji SUMA.ILOCZYNÓW(kolumna1;kolumna2). Ale teraz stosuje filtr, np. artykułów które chce kupić. wyswietlaja sie tylko niektóre. I teraz pytanie: jesli chciałabym policzyć sume iloczynów tylko dla wierszy wyswietlonych (tzn zeby funkcja ignorowała pozostałe), z czego powinnam skorzystac?

Do tej pory udalo mi sie rozgryźć ten problem ale dla zwykłej sumy, a nie sumy iloczynów (tzn. komedna: SUMY.CZĘŚCIOWE(109,kolumna)). 109 to funkcja jaka ma byc uzyta (tutaj to po prostu suma ignorująca ukryte), a dla sumy iloczynów? Nie moge nigdzie znaleźć :/ Byłabym wdzieczna na wskazówki.

Undre · Post autor: **Undre** » 14 kwie 2009, o 12:40

imho tak się nie da. sumailoczynów jako parametry przyjmuje dwie lub więcej tablic, te podajesz zakresem komórek. Jeżeli miałabyś funkcję, która z zakresu zwróci odfiltrowany zakres, to luz, ale takiej funkcji nie kojarzę. Sama opcja filtrowania jak zauważyłaś opiera się na ukrywaniu komórek, z tego co wiem nie ma funkcji excela rozpoznającej czy dana znajduje się w wierszu ukrytym.

... filter.htm

Ten sam problem, rozwiązanie sugerowane - zaznaczyć odfiltrowane kolumny ręcznie, skopiować obok, i na takich danych wykonywać dalsze manewry.

Zostaje Ci również VBA, tam z grubsza zrobisz co chcesz, to w końcu durny ale jednak język programowania.

naolin · Post autor: **naolin** » 18 lut 2010, o 11:13

Powracam do tematu, bo mam ten sam problem. Na VBA to się nie znam .
Magdaskowska rozwiązałaś może wtedy ten problem? Jeżeli tak to jak?

kraf101 · Post autor: **kraf101** » 23 lut 2010, o 00:01

Takie rzeczy najlepiej jest chyba rozwiązywać obliczeniami macierzowymi / wektorowymi czy jak ja zwał.
Dzięki temu możemy wprowadzić do formuł wyrażenia logiczne i zastąpić nimi np upierdliwe funkcje bazodanowe itede. Jeżeli mam dwie kolumny z danymi np A-nazwa, B-liczba, to możemy napisać: =SUM($B$1:$B$21*($A$1:$A$21="kret")) i wcisnąć Ctrl+Shift+Enter.
Taka formuła zsumuje ilość wszystkich kretów w bazie danych, co więcej łatwo tworzyć większe zestawienia dla innych zwierzaków poprzez copy-paste (dlatego warto używać adresów bezwzględnych dla zakresów danych).

Tyle że ich efekt działania tych obliczeń może być zaskakujący i chyba dlatego nie są dobrze udokumentowane. Jeżeli jednk zachować odpowiednią czujność, to mogą być nieocenione.

naolin · Post autor: **naolin** » 23 lut 2010, o 21:28

Tylko, że ja nie chce wyszukiwać jakiejś danej w tabeli.
Mam tabele z danymi, które chce pomnożyć i zsumować.

Kod: Zaznacz cały

   A  B  C
1 a  2   4
2 a  3   5
3 b  2   3
4 b  8   2
      =suma.iloczynów(B1:B4;C1:C4)

Chce sumę wszystkich iloczynów i wszystko jest ok. Natomiast jak chce wyfiltrować wartości tylko dla danej "a" chciałabym sumę iloczynów tylko dla tej danej, natomiast przy tej funkcji pojawia się suma iloczynów wszystkich wartości.

kraf101 · Post autor: **kraf101** » 24 lut 2010, o 00:03

naolin pisze:Tylko, że ja nie chce...
Chce sumę wszystkich...
chce wyfiltrować wartości tylko dla danej "a" ....

Chcesz to masz

Kod: Zaznacz cały

   A  B  C
1 a  2   4
2 a  3   5
3 b  2   3
4 b  8   2
      =SUMA(B1:B4*C1:C4*(A1:A4="a"))

Tylko na litość boską po napisaniu tej formuły naciśnij Ctrl+Shift+Enter, a nie zwykłe Enter.
Taki jest warunek zliczania macierzowego, a formuła będzie widoczna w nawiasach {}, czyli: {=SUMA(B1:B4*C1:C4*(A1:A4="a"))}

mkb · Post autor: **mkb** » 24 lut 2010, o 09:10

Formuły macierzowe przeliczają się przy każdej zmianie w arkuszu, co przy większych obliczeniach mocno spowalnia pracę. W excelu jest prosty trick zamiany wartości logicznej na liczbę, po którym działa zwykła SUMA.ILOCZYNÓW:

Kod: Zaznacz cały

=SUMA.ILOCZYNÓW(B1:B4;C1:C4;--(A1:A4="a"))

kraf101 · Post autor: **kraf101** » 24 lut 2010, o 11:05

mkb pisze:W excelu jest prosty trick zamiany wartości logicznej na liczbę, po którym działa zwykła SUMA.ILOCZYNÓW:
Kod: Zaznacz cały
=SUMA.ILOCZYNÓW(B1:B4;C1:C4;--(A1:A4="a"))

to sprytny cfancyś Ale zwykłej sumy już źle zlicza.

Kod: Zaznacz cały

=SUMA($B$1:$B$4;--($A$1:$A$4="a")) -- TO NIE DZIAŁA

A przy większych obliczeniach, i tak warto włączyć tryb manualny.

Pozdro

mkb · Post autor: **mkb** » 24 lut 2010, o 11:45

Działa, tylko że sumujesz B1:b4 (15) i 2xPRAWDA (2), a chyba nie o to chodzi.
Pojedynczą kolumnę można zsumować z dodatkowym warunkiem inną funkcją:

Kod: Zaznacz cały

=SUMA.JEŻELI(A1:A4;"a";B1:B4)

albo z konwersją:

Kod: Zaznacz cały

=SUMA.ILOCZYNÓW(--(A1:A4="a");B1:B4)

kraf101 · Post autor: **kraf101** » 24 lut 2010, o 11:51

mkb pisze:Działa, tylko że ...
Kod: Zaznacz cały
=SUMA.JEŻELI(A1:A4;"a";B1:B4)
albo z konwersją:
Kod: Zaznacz cały
=SUMA.ILOCZYNÓW(--(A1:A4="a");B1:B4)

To już dla mnie wyższa szkoła spamiętywania tego i owego i co, gdzie , jak
Tak czy siak problem chyba [SOLVED]

Pozdro

Insynuator · Post autor: **Insynuator** » 28 lut 2010, o 22:12

Witam Serdecznie. Mam takie pytanko?? Mianowicie mam 3 kolumny pierwsza jest nazwą produktu druga ilością zaś trzecia pozycją. Jak policzyć srednią pozcyje gdy ilość jest większa od zera, oraz jak policzyć średnia ilość ale tylko dla komórek większych od zera??

Z góry dzięki za odpowiedz

Pozdrawiam

mkb · Post autor: **mkb** » 1 mar 2010, o 09:09

Możesz wykorzystać liczenie średniej z definicji, wykorzystując funkcje LICZ.JEŻELI, SUMA.JEŻELI, szczegóły w pomocy.
Prościej chyba dodać pomocnicze kolumny w tabeli zwracające 1 lub 0, w zależności czy jakaś wielkość w innej kolumnie ma być uwzględniana (w przypadku pierwszej średniej jedna dla ilość>0, formuła JEŻELI w każdej komórce). Dalej już tabela przestawna, nazwa w obszarze wierszy, ilość w obszarze danych, funkcja agregacji: średnia, pomocnicza kolumna jako pole strony (z odpowiednim wyborem).

Insynuator · Post autor: **Insynuator** » 29 mar 2010, o 22:49

itam. Mam taki banalny zapewne problem do rozgryzienia. Kolumna A,B,C zawiera pewne dane, w kolumnie D, mam zapisać sumę tych trzech kolumn - można to zrobić na kilka sposobów albo dając A&B&C, albo używając funkcji złącz tekst lub też usuń przerwy. Po zastosowaniu któregoś z wyżej wymienionych sposobów w kolumnie d w wierszu 2 skopiowałem formułę do pozostałych wierszy. Jednak pomimo że w pasku formuła jest prawidłowo napisana wartości przypisane są tylko dla 2 wierszu (od razu wspomnę nie zastosowałem $). Dopiero po kliknięciu enter na każdej z formuł zmienia swoją postać dla prawidłowej. Dlaczego nie dzieje się tak po skopiowaniu formuły pomimo właściwej ścieżki ?? Może przykład lepiej to zobrazuje

a b c d ścieżka formuły

1. 12 klient 1 qp1 12klient 1qp1 =A1&B1&C1
2. 18 klient 2 qp2 12klient 1qp1 =A2&B2&C2
3. 16 klient 3 qp3 12klient 1qp1 =A3&B3&C3
...

Z góry dziękuje za odpowiedź