Temat: suma.jeżeli w oparciu o dwa parametry

Nie, LICZ.WARUNKI nie ma swojego odpowiednika w Excelu 2003 - można sobie oczywiście poradzić, za pomocą SUMA i JEŻELI.

Błąd u Pana polega na tym, że w komórce C2 znajduje się po prostu inna wartość niż w komórkach B2:B7.

Myślę, w C2 jest "A ", a w B2:B7 jest {"A";"B";"A";"A";"B";"A"}.

Pozdrawiam!Tomasz Gryzio edytował(a) ten post dnia 12.01.13 o godzinie 12:26

Temat: suma.jeżeli w oparciu o dwa parametry

Marek Zadęcki:
Czy funkcja LICZ.WARUNKI ma swój odpowiednik w Excelu 2003?

Nie. Ta funkcja pojawiła się w późniejszych wersjach.

Temat: suma.jeżeli w oparciu o dwa parametry

Marek Zadęcki:
Czy funkcja LICZ.WARUNKI ma swój odpowiednik w Excelu 2003?

Funkcję LICZ.WARUNKI, która nie jest tablicową można w excelu 2003 zastąpić funkcją SUMA.ILOCZYNÓW, np. dla danych:
1 A A
2 B
3 A
4 B
5 A
gdybysmy chcieli policzyć ile rekordów ma w kolumnie A liczbę mniejszą niż 3, a w kolumnie B wartość z komórki C1 zapisalibyśmy:

=LICZ.WARUNKI($A$1:$A$5;"<3";$B$1:$B$5;C1)

a w Excelu 2003 zastąpilibyśmy formułą:

=SUMA.ILOCZYNÓW(($A$1:$A$5<3)*($B$1:$B$5=C1))

Jednak jeśli funkcja LICZ.WARUNKI pojawia się tablicowo, tak jak w tym przypadku, to trzeba by było sprawić, żeby i SUMA.ILOCZYNÓW dała wynik tablicowy (a chyba nie jest to możliwe).

> Prawdopodobnie to ona jest przyczyną błędnego mojego wyniku.
Tak, przyczyną błędnego wyniku jest użycie w Excelu funkcji LICZ.WARUNKI, która pojawiła się w Excelu 2007, gdyż ona zwróci błąd nazwy, a dalej używa Pan funkcji CZY.BŁĄD i w rezultacie funkcja JEŻELI zwróci tablicę "", gdzie zsumowana tablicowo da wynik zero. To tak jakby tablicowo zastosować formułę:

=SUMA(JEŻELI(A1:A2=A1:A2;"";"cokolwiek"))

też wyjdzie zero.

Jak powinna wyglądać prawidłowa formuła dla Excela 2003, aby uzyskać właściwy wynik? Mam dane (identyczne jak w pierwotnym przykładzie) w zakresie A2:C7
Moja formuła tablicowa (używam Excela 2003) wyszła mi tak:

=SUMA(JEŻELI(CZY.BŁĄD(--($B$2:$B$7=C2)*1/LICZ.WARUNKI($A$2:$A$7;$A$2:$A$7;$B$2:$B$7;C2));"";(--($B$2:$B$7=C2)*1/LICZ.WARUNKI($A$2:$A$7;$A$2:$A$7;$B$2:$B$7;C2))))

Jeśli kolumna A jest kolumną liczb, to wcześniejsza formuła z CZĘSTOŚCIĄ zatwierdzona tablicowo zadziała:

=SUMA(JEŻELI(CZĘSTOŚĆ(JEŻELI($B$2:$B$7=C2;$A$2:$A$7;"");JEŻELI($B$2:$B$7=C2;$A$2:$A$7;""))>0;1))

jeśli byłaby to kolumna tekstowa, to chyba pozostaje dodać jedną kolumnę pomocniczą.

Temat: suma.jeżeli w oparciu o dwa parametry

Bardzo dziękuję Pani Barbaro za wyjaśnienie. Pani rozwiązanie kończy mój problem.

Barbara Bobikau:

Jeśli kolumna A jest kolumną liczb, to wcześniejsza formuła z CZĘSTOŚCIĄ zatwierdzona tablicowo zadziała:

=SUMA(JEŻELI(CZĘSTOŚĆ(JEŻELI($B$2:$B$7=C2;$A$2:$A$7;"");JEŻELI($B$2:$B$7=C2;$A$2:$A$7;""))>0;1))

Temat: suma.jeżeli w oparciu o dwa parametry

Witam,

Runda 3 z zadaniem.

Tak jak napisała Pani Barbara z funkcji suma.iloczynów nie dostaniemy w sposób łatwy w Excelu tablicy sum, a więc w dużym stopniu należy polegać na funkcji częstość, która sama z siebie daje nam w wyniku swojego działania tablicę sum - prawie to samo co wyrażenie

licz.warunki(zakres1;zakres2;zakres3;zakres4) 

dla Excela >= 2007
oraz
to co wyrażenia

suma.iloczynów(zakres1=zakres2;zakres3=zakres4), licz.jeżeli(zakres;zakres), suma(jezeli(zakres1=zakres2*zakres3=zakres4;1;0))

dla Excela < 2007 (gdyby była możliwość z nich skorzystać),

czyli rozkład liczby wystąpień danej wartości dla danej danej, a suma z odwrotności (1/powyższeWyrażenia) liczebności w takim rozkładzie da nam liczbę unikatowych danych, dla których rozkład był budowany, jednakże w przypadku funkcji częstość mamy do czynienia z rozkładem budowanym dla przedziałów podawanych jako drugi parametr jako tablica granic przedziałów, a więc jesteśmy uzależnieni od sumowania kombinacji funkcji jeżeli i częstość w postaci tablicy

jeżeli(częstość(zakres1;zakres2)>0;1;0)

,
gdzie oba zakresy muszą być zakresami liczbowymi, co nie pozostawia złudzeń, że chcąc użyć tej funkcji - a chcemy będąc w Excelu 2003 - musimy podać granice przedziałów - a więc liczby [po raz pierwszy] oraz zakres liczbowy - a więc liczby [po raz drugi] - co wymusza na nas dodanie kolumny dodatkowej lub użycie funkcji, która takiej [teksty na liczby] zamiany dokona bez ingerencji w dane, której wykorzystanie pozwala na użycie jej wyniku jako parametr funkcji częstość, a więc i obliczenie liczby unikatowych rekordów w wielokolumnowym zakresie przy założeniu, że wartości z jednej z kolumn są równe znanej stałej - taką funkcją jest tablicowo wywołana funkcja podaj.pozycję.

Rozwiązaniem zadania dla tekstów oraz liczb dla Excela 2003 będzie formuła wprowadzona tablicowo (potwierdzenie przez Ctrl+Shift+Enter) do komórki D1 i przeciągnięta w dół:

=SUMA(JEŻELI(CZĘSTOŚĆ(JEŻELI($B$1:$B$6=C1;PODAJ.POZYCJĘ($A$1:$A$6&$B$1:$B$6;$A$1:$A$6&$B$1:$B$6;0));WIERSZ($A$1:$A$6)-WIERSZ($A$1)+1)>0;1))

Pozdrawiam!

Temat: suma.jeżeli w oparciu o dwa parametry

To jeszcze dorzucę swoje spostrzeżenie formuła Pana Tomasza:

Tomasz Gryzio:

Rozwiązaniem zadania dla tekstów oraz liczb dla Excela 2003 będzie formuła wprowadzona tablicowo (potwierdzenie przez Ctrl+Shift+Enter) do komórki D1 i przeciągnięta w dół:

 =SUMA(JEŻELI(CZĘSTOŚĆ(JEŻELI($B$1:$B$6=C1;PODAJ.POZYCJĘ($A$1:$A$6&$B$1:$B$6;$A$1:$A$6&$B$1:$B$6;0));WIERSZ($A$1:$A$6)-WIERSZ($A$1)+1)>0;1))

nie rozróżnia wielkich i małych liter.
Proszę o przeanalizowanie takiego przykładu:

Znacznik litera warunek Rozwiązanie
1 A A 2
2 a B 1
3 B REF 1
4 C a 2
a D
b REF

Tymczasem powinno (tak sądzę) być: 1 1 1 2 (a może się mylę?)

Temat: suma.jeżeli w oparciu o dwa parametry

Witam,

Przykład:

 =SUMA(JEŻELI(CZĘSTOŚĆ(JEŻELI($B$1:$B$6=C1;PODAJ.POZYCJĘ($A$1:$A$6&$B$1:$B$6;$A$1:$A$6&$B$1:$B$6;0));WIERSZ($A$1:$A$6)-WIERSZ($A$1)+1)>0;1))

nie rozróżnia wielkich i małych liter.
Proszę o przeanalizowanie takiego przykładu:

Znacznik litera warunek Rozwiązanie
1 A A 2
2 a B 1
3 B REF 1
4 C a 2
a D
b REF

Tymczasem powinno (tak sądzę) być: 1 1 1 2 (a może się mylę?)

przeanalizowany.

Tak, oczywiście formuła nie rozróżnia wielkości liter, niemniej jednak podane Przez Pana rozwiązanie jest nieprawidłowe - rozróżniając wielkość liter komórki warunku i kolumny litera w przypadku warunku "a" prawidłową odpowiedzią jest 1 ("a" występuje jednokrotnie w kolumnie litera - a więc i tyle jest wartości występujących jednokrotnie dla tego warunku).

Jeżeli zależy nam na rozróżnieniu wielkości liter odpowiednią formułą będzie wprowadzona tablicowo (potwierdzenie przez Ctrl+Shift+Enter) do komórki D1 i przeciągnięta w dół::

=SUMA(JEŻELI(CZĘSTOŚĆ(JEŻELI(PORÓWNAJ($B$1:$B$6;C1);PODAJ.POZYCJĘ($A$1:$A$6&$B$1:$B$6;$A$1:$A$6&$B$1:$B$6;0));WIERSZ($A$1:$A$6)-WIERSZ($A$1)+1)>0;1))

Pozdrawiam!

Temat: suma.jeżeli w oparciu o dwa parametry

Tomasz Gryzio:

Tak, oczywiście formuła nie rozróżnia wielkości liter, niemniej jednak podane Przez Pana rozwiązanie jest nieprawidłowe - rozróżniając wielkość liter komórki warunku i kolumny litera w przypadku warunku "a" prawidłową odpowiedzią jest 1 ("a" występuje jednokrotnie w kolumnie litera - a więc i tyle jest wartości występujących jednokrotnie dla tego warunku).

Jeżeli zależy nam na rozróżnieniu wielkości liter odpowiednią formułą będzie wprowadzona tablicowo (potwierdzenie przez Ctrl+Shift+Enter) do komórki D1 i przeciągnięta w dół::

=SUMA(JEŻELI(CZĘSTOŚĆ(JEŻELI(PORÓWNAJ($B$1:$B$6;C1);PODAJ.POZYCJĘ($A$1:$A$6&$B$1:$B$6;$A$1:$A$6&$B$1:$B$6;0));WIERSZ($A$1:$A$6)-WIERSZ($A$1)+1)>0;1))

Pozdrawiam!

Faktycznie podana przez Pana ww. formuła zwraca prawidłowe wyniki.
Serdecznie dziękuję.