Excel - podstawy

SAVE OFFLINE

Excel – odwołania bezwzględne Do zrobienia samodzielnego trzeba było obliczyć BMI wszystkich osób (arkusz BMI) korzystając ze wzoru, że BMI to waga podzielona przez wzrost do kwadratu – Jasiowi powinno wyjść 32,9. Mamy dwie możliwości.  

=C2/(B2*B2) – czyli zwykłe mnożenie z nawiasem dla ustalenia kolejności działań =C2/B2^2 – czyli użycie potęgowania – znak „^” oznacza „do potęgi”

Oczywiście liczymy tylko pierwszemu, pozostałe formuły kopiujemy „w dół”. Przypominam – robimy to klikając LPM (lewy przycisk myszy) w prawym dolnym narożniku wybranej komórki (lub zaznaczonego obszaru) i przeciągamy do wybranego miejsca. Drugą rzeczą było obliczenie długości życia kotów. Dość prosta formuła czyli =C2-B2. Kopiujemy „w dół” Uwaga – na arkuszu „kotki” jest błąd – proszę w komórce E1 poprawić „miał” na „miałby”. Chcemy obliczyć teraz ile kotki miałyby lat gdyby dzisiaj żyły. Teoretycznie formuła jest prosta bo =B9-B2. Wynik poprawny czyli 9 (Pierwszy z kotków urodził się w 2010, dzisiaj jest 2020). Ale po skopiowaniu w dół dzieje się coś dziwnego. Zamiast wyników pojawiają się liczby ujemne.

Żeby zrozumieć się się stało, klikamy dwukrotnie na komórkę E5 i widzimy, że formuła się zmieniła. W poprzednim przypadku, formuła „aktualizowała się” zgodnie z jej przesunięciem. Dla Puszka – od roku zejścia Puszka odejmowała rok urodzenia Puszka, dla Mruczka od roku zejścia Mruczka odejmowała rok urodzenia Mruczka itd. I fajnie. Ale teraz mamy sytuację inną – komórka B9 (czyli 2020) powinna być stała. A jak widzimy, aktualizuje się i w formule dla Mruczka zamiast być =B9-B5 jest =B12-B5. Jeżeli chcemy, żeby jakaś komórka w formule była stała, musimy wpisać jej adres (nazwę) w sposób bezwzględny. Czyli przed elementem, który ma się nie zmieniać wstawiamy symbol $. A zatem wracamy do komórki E2 i poprawiamy formułę na =B$9-B2. Następnie kopiujemy w dół. Prawdopodobnie w każdej z nowopowstałych formuł część B$9 jest stała, natomiast B2 zmienia się w B3, B4, B5 itd. Oczywiście można by „oszukać” i zamiast B$9 wpisać 2020 ale wtedy zmiana roku w komórce B9 nie spowoduje aktualizacji danych w formułach.

Spróbujmy teraz obliczyć ile miałby każdy z kotków dzisiaj lat, gdyby jeszcze żył. Wyniki dla całego zadania powinny być takie jak poniżej:

Podstawowe funkcje Czasami, niestety zwykłe formuły nie wystarczają i trzeba użyć czegoś bardziej zaawansowanego czyli funkcji. Mają one specyficzną budowę i wyglądają zawsze tak =NAZWA(PARAMETRY). Przejdźmy na arkusz 2 (klikając dwukrotnie LPM na jego nazwie możemy ją zmienić, np. na Uzupełnianie – będzie łatwiej się orientować. Na arkuszu mieliśmy dwie kolumny, w każdej liczby 1,3 i 5. W pierwszej z nich uzupełniliśmy ciąg do komórki A20, w drugiej skopiowaliśmy (patrz poprzednie materiały). Następnie w komórce C1 wpisaliśmy =A1+B1 i skopiowaliśmy formułę w dół. Kontynuujemy – zaznaczmy komórki C1:C20 i skopiujmy je w prawo o trzy kolumny. Całość powinna teraz wyglądać tak:

Zaznaczmy teraz komórki A1:F20 i przesuńmy je o jeden wiersz w dół i jedną kolumnę w prawo. Robimy to „łapiąc” zaznaczony obszar za zewnętrzną ramkę (nie za narożnik). W komórkach A22 i H1 wpisujemy „Suma”, w A23 i I1 wpisujemy „Średnia” W komórce B22 chcielibyśmy obliczyć sumę wszystkich liczb powyżej. Możemy zrobić to na dwa sposoby. Pierwszym byłoby posłużenie się zwykłą formułą czyli =B2+B3+B4+…+B21 i jest to poprawne ale bezsensownie pracochłonne. Zrobimy więc inaczej i posłużymy się funkcją. Zaznaczamy komórki B2:B21 i klikamy napis „Autosumowanie” na karcie „narzędzia główne” (po prawej). I oto wynik się pojawia. Teraz wystarczy na niego kliknąć i skopiować w prawo do G22. Robimy to samo zaznaczając komórki B2:G2 – tym razem wynik pojawi się w H2. Kopiujemy w dół do H21

W następnym kroku chcemy obliczyć średnie – zaznaczamy ten sam obszar (B2:B21) ale tym razem nie tyle klikamy na Autosumowanie co rozwijamy listę i wybieramy Średnia. Jak widać, Excel omija sumy i wstawia średnią w następnej komórce. Kopiujemy w prawo, robimy to samo z średnia dla B2:G2, kopiujemy w dół. Klikając na którąś z sum lub średnich, w pasku formuły zobaczymy jej prawdziwy zapis. Wygląda on np. =SUMA(B2:B21) albo =ŚREDNIA(B2:G2). Możemy taki zapis wprowadzić ręcznie. Robimy to wtedy gdy chcemy wynik wprowadzić w innym miejscu niż tylko pod kolumną / za wierszem. Kliknijmy w H22. Chcemy tutaj mieć sumę wszystkich komórek od B2 do G21. A zatem piszemy =SUMA(B2:G21) i Enter. W komóre I23 analogicznie liczymy ich średnią. Uwaga- nie musimy wpisywać komórek, możemy je zaznaczyć. Czyli piszemy np. =ŚREDNIA( i zaznaczamy komórki od B1 do G21 a następnie wstawiamy Enter. Nie trzeba zamykać nawiasu. Jeżeli chcemy zaokrąglić wynik (często robimy to dla średnich) to zaznaczamy komórki to zaokrąglenia i klikamy na odpowiednie ikonki na karcie narzędzie główne .

=SUMA i =ŚREDNIA to podstawowe i dość proste funkcje. Podobnie działają =MAX i =MIN, które wyświetlają największą i najmniejszą liczbę z wybranego obszaru. Zadanie – proszę na arkuszu BMI i Kotki wpisać (to co na zielono) i obliczyć (to co na czerwono, wyniki wyświetlam, żebyście państwo sprawdzili czy wychodzi tak samo) następujące rzeczy: Dla kotków – ile wynosiła najdłuższa długość życia kota, ile najkrótsza i ile średnio koty żyły (a następnie kopiujemy wyniki na „Miałby” i „Nie żyje już”). Dla BMI – ile wynosi najmniejszy, najniższy, łączny i średni wzrost (a następnie kopiujemy wyniki na Wagę i BMI.