Kurs Excel: Sumy i średnie warunkowe

0:00Witam Cię w kolejnym module, w którym pochylimy się nad grupą

0:04funkcji warunkowego zliczania.

0:05I mam tutaj na myśli funkcje takie jak suma.

0:09Jeżeli suma warunków średnia.

0:11Jeżeli średnia warunków licz jeżeli i licz warunki.

0:16Bierzmy się do pracy.

0:17Pierwsza formuła, którą omówimy to formuła suma.

0:21Jeżeli formuła ta przyda Ci się w sytuacji, gdy będzie trzeba policzyć sumę

0:27wartości pod jakimś konkretnym kryterium.

0:30W naszym przypadku chcielibyśmy zobaczyć, ile sprzedało się poszczególnego rodzaju

0:35mebla ze względu na nazwę, czyli ile sprzedało nam się szafek,

0:39szaf, łóżek i komód.

0:41Jak możemy się za to zabrać?

0:43Skorzystajmy z funkcji suma. Jeżeli.

0:45Funkcja ta składa się z trzech elementów.

0:48Pierwszym elementem jest zakres, w którym będą znajdowały się nasze kryteria.

0:52U nas będzie to rodzaj mebla, czyli w tej kolumnie będą występowały

0:57kolejne rodzaje mebla.

0:59Drugim elementem formuły jest kryterium, czyli to na podstawie czego

1:04ma zostać wykonane sumowanie.

1:06U nas będą to kolejne rodzaje mebla znajdujące się w tej tabeli.

1:10Następnie suma zakres, czyli kolumna, z której chcemy uzyskiwać rezultaty.

1:15W naszym przypadku jest to kolumna ze sprzedażą.

1:18Na jakiej zasadzie będzie działała nasza funkcja?

1:20Jeżeli Excel znajdzie w tym konkretnym pierwszym przypadku szafkę, to uwzględni

1:26nasz wynik sprzedażowy do sumy.

1:28Jeżeli natomiast nie znajdzie szafki, co ma miejsce np.

1:32W drugiej linijce mamy tutaj łóżko, to cała ta linijka jest przez

1:36Excela traktowana jako fałsz.

1:38Nie jest brana pod uwagę w kalkulacji.

1:40Szafkę bierze pod uwagę, szafy nie.

1:42No i w ten oto sposób przechodzi linijka po linijce do samego końca.

1:46Na samym końcu zostaje mu tablica z wartościami i zerami.

1:51Tam gdzie nie ma słowa szafka, oczywiście pojawiają się zera.

1:54Tu możemy już do końca przekreślić nasze wartości.

1:57No i na samym końcu Excel będzie nam sumował te wartości.

2:01A więc Doda 12 1800 do 14.300 i zwróci nam tutaj prawidłowy wynik.

2:07Tyle jeżeli chodzi o to, co dzieje się pod maską formuły suma.

2:10Jeżeli teraz spróbujmy ją zbudować.

2:12Ustawiam się w komórce, w której chcę zbudować sumę.

2:14Jeżeli wpisuję równa się suma kropka, strzałka w dół.

2:18Jeżeli A więc zatwierdzam sobie tabulatorem zakres strzałka

2:22w lewo, jestem w komórce A11.

2:24Mogę zaznaczyć myszką albo użyć skrótu klawiszowego Ctrl + Shift strzałka w dół.

2:29No i teraz istotna sprawa.

2:30Będę moją formułę przeciągał w dół, a więc zależy mi na tym, aby ta formuła

2:34odwoływała się zawsze dokładnie do zakresu rodzaju mebla, a więc muszę

2:39zablokować ją FN, F4 lub F4.

2:42Wciskam sobie raz ten przycisk.

2:44No i faktycznie sprawdza.

2:45Mamy dwa dolary dla komórki startowej, dwa dolary dla komórki końcowej, a to oznacza,

2:50że właśnie mój zakres jest zablokowany Bezwzględnie.

2:54Gdy będę kopiował moją formułę, będzie to wszystko działało tak, jakbym chciał.

2:58Wciskam sobie średnik.

2:59No i teraz muszę zaznaczyć komórkę z kryterium No jak widzisz, myszą będzie

3:03ciężko to zrobić, ale możemy użyć strzałki na klawiaturze.

3:06Strzałka w lewo.

3:07Ustawiam się dokładnie w tej komórce, którą chcę mieć uwzględnioną

3:11w mojej formule.

3:12Nie muszę jej blokować, dlatego, że gdy będę przeciągał moją formułę w dół,

3:15chciałbym, aby oczywiście moje rodzaje mebla kolejno się zmieniały.

3:19No i z racji tego, że mamy odblokowaną komórkę, nie ma tutaj żadnych dolarów.

3:23Ona będzie sobie podróżowała w dół dokładnie tak, jakbym chciał.

3:26Wciskam sobie średnik. No i suma.

3:28Zakres. Tym razem znowu strzałka w lewo.

3:30Jestem w komórce C11 Ctrl+Shift strzałka w dół.

3:34Zaznaczam cały obszar i również blokuję go klawiszem F4 lub funkcyjny + F4 tak aby

3:39zawsze sumować dokładnie te kolumny.

3:42Istotną sprawą jest to, aby te zakresy były równe, to znaczy, żeby kończyły się

3:46na jednej wysokości i aby zaczynały się na jednej wysokości.

3:49Dlatego, żeby funkcja działała w sposób prawidłowy.

3:52Zwróćmy na to uwagę, bo to częsty błąd.

3:53Czasami gdzieś wkradnie się jakaś pusta komórka przy zaznaczaniu i te

3:57zakresy mogą nam się różnić.

3:59To spowoduje błędne działanie formuły zamykamy nawias, zatwierdzamy

4:03enterem, przeciągamy sobie w dół.

4:05No i tradycyjnie możemy sprawdzić.

4:06Może niekoniecznie w pierwszej komórce, bo tam formuła bardzo często działa, żeby nie

4:10powiedzieć zawsze sprawdźmy sobie wartości dla łóżka.

4:14Jak możemy to zrobić?

4:15Możemy oczywiście użyć filtra na tej tabeli, ale z racji tego, że ona jest dość

4:18mała, skorzystamy ze sprytnego triku z kontrolą.

4:20Czyli mam łóżko, zaznaczam komórkę z łóżkiem, trzymam teraz lewy ctrl

4:25i szukam w liście kolejnego łóżka.

4:26Mamy tutaj drugie łóżko, Mamy trzecie łóżko.

4:29No i w ten oto sposób zaznaczyłem sobie trzymając lewy ctrl trzy komórki.

4:33No a na dole to już wiemy z jednej z poprzednich lekcji.

4:36Mamy szybką sumę 48.900, która to suma świetnie zgadza się z tym, co zostało

4:43wyliczone przez funkcję, a więc mogę uznać tę funkcję za działającą prawidłowo.

4:47Drugim sposobem sprawdzenia, z którego też często korzystam przy tego typu funkcjach,

4:51jest zaznaczenie całego obszaru, podejrzenie sobie na dole

4:53w wyniku zapamiętania go. Mniej więcej.

4:55Czyli to jest 163, 1800 to 800 moglibyśmy nawet pominąć.

4:59Zaznaczamy obszar.

5:00No i tutaj faktycznie widzimy, że wyniki są takie same, a to oznacza, że moja

5:04funkcja obejmuje wszystkie kategorie.

5:07Więc to też będzie takie szybkie, proste sprawdzenie, które upewni nas,

5:11że funkcja działa prawidłowo.

5:12To dotyczy nie tylko funkcji sumy, ale także w innych przypadkach również

5:16możemy korzystać z tego typu triku. Przejdźmy dalej.

5:19Oprócz funkcji suma, jeżeli mamy do dyspozycji również funkcję średnia.

5:22Jeżeli różnica pomiędzy sumą jeżeli a średnią.

5:25Jeżeli będzie.

5:25To, że średnia będzie nam wyliczała po prostu średnią.

5:28Na podstawie tych konkretnych rodzajów mebli, zacznijmy wpisywać funkcję

5:32równa się średnia strzałka w dół.

5:33Jeżeli znaleźliśmy.

5:34Jeżeli zatwierdzam sobie tabulatorem.

5:36I teraz składnia bardzo, bardzo podobna.

5:39Czyli najpierw zakres, na podstawie którego mają zostać

5:41znalezione rodzaje mebli.

5:43Ctrl+Shift strzałka w dół blokuję klawiszem F4 wciskam średnik.

5:47Teraz kryterium, a więc strzałka w lewo.

5:49Wybieram sobie kryterium mojego mebla. Rodzaj mebla.

5:52Wciskam średnik.

5:53Teraz średnia zakres, czyli z której kolumny chciałbym, aby

5:56została wyliczona średnia. I tu jest ta różnica.

5:58Wcześniej mieliśmy nazwę parametru nie średnia zakres, a suma zakres.

6:02Teraz mamy średni zakres, dlatego, że będziemy wyciągali średnią

6:05właśnie z tej naszej sprzedaży.

6:07blokujemy klawiszem F4, bo zawsze chcielibyśmy, aby formuła odwoływała

6:11się właśnie do tego obszaru.

6:12Zamykamy nawias, zatwierdzamy enterem, przeciągamy sobie w dół.

6:16No i zwróćmy uwagę możemy zrobić po raz kolejny sprawdzenie.

6:20Zróbmy to jeszcze raz dla łóżka. Czyli mamy łóżko.

6:22Trzymam lewy ctrl, zaznaczam sobie kolejne łóżko i jeszcze jedno łóżko, a na dole po

6:28prawej mam włączoną średnią, więc widzę, że jest ona prawidłowa, bo wynosi

6:32dokładnie tyle samo, ile w wyniku obliczeń formuły.

6:37Gdyby na Twoim pasku nie było średniej, przypomnę prawy przycisk myszy.

6:40I tutaj możemy sobie w tym obszarze włączać interesujące

6:45nas elementy obliczeń.

6:46Ja tą średnią mam włączoną, mam włączony również licznik, a także sumę.

6:51To tyle w tej lekcji.

6:52W kolejnej pokażę Ci możliwości sumowania i wyliczania średniej w przypadku,

6:56gdy tych kryteriów jest nieco więcej.

6:58No bo co gdybyśmy chcieli na przykład wykonać sumę dla szafek w kolorze hebanu

7:02albo w jakimś innym kolorze, żeby po prostu rozróżnić na podstawie

7:05więcej niż jednego kryterium?

7:06Więcej o tym w kolejnej lekcji. Do zobaczenia!