Powiązania i komentarze w kontroli poprawności obliczeń w arkuszu kalkulacyjnym. Wprowadzenie Samouczek I Samouczek II Samouczek III Dla nauczyciela

(1)

Powiązania i komentarze w kontroli poprawności obliczeń w arkuszu kalkulacyjnym

Wprowadzenie Samouczek I Samouczek II Samouczek III Dla nauczyciela

(2)

W jaki sposób wyświetlić jednocześnie zarówno formuły, jak i wyniki ich działania? Kiedy warto

zastosować narzędzie tryb inspekcji formuł? Jak dzięki komentarzom czytelniej zaprezentować formuły w arkuszu? Poznaj sposoby na kontrolowanie poprawności obliczeń.

Twoje cele

Dowiedz się, co trzeba zrobić, aby jednocześnie wyświetlać formuły i wyniki.

Poznasz narzędzie, które pozwoli Ci analizować poprawność lub sposób działania formuł.

Będziesz kontrolować poprawność obliczeń wykorzystując komentarze.

Powiązania i komentarze w kontroli poprawności obliczeń w

arkuszu kalkulacyjnym

Źródło: licencja: CC 0.

(3)

Samouczek I

Kontrolowanie poprawności obliczeń – jednoczesne wyświetlenie formuł i wyników

Zdarza się, że potrzebne jest zastosowanie takiego trybu widoku arkusza, w którym jednocześnie

wyświetlone będą zarówno formuły, jak i wyniki ich działania. Excel nie posiada wbudowanego narzędzia pozwalającego na szybkie zastosowanie takiego wariantu widoku, ale istnieje sposób by osiągnąć ten efekt. Pobierz plik z danymi umieszczony poniżej ﬁlmu i wykonaj następujące kroki.

W tym celu:

1. Obok wszystkich (lub wybranych) kolumn zawierających formuły wstaw dodatkowe kolumny pomocnicze.

2. Następnie zaznacz pierwszą komórkę zawierającą formułę i przejdź do trybu jej edycji, przez wciśnięcie klawisza [F2].

3. Teraz zaznacz zawartość komórki (formułę) i za pomocą skrótu klawiaturowego [Ctrl]+[C] skopiuj ją do schowka.

4. Wciśnij teraz [Esc], aby wyjść z trybu edycji komórki.

5. Przejdź teraz do komórki w sąsiedniej kolumnie, którą przygotowałeś do wyświetlenia formuł.

Wprowadź znak apostrofu (‘), a następnie wklej zawartość schowka kombinacją klawiszy [Ctrl]+[V].

Użycie znaku apostrofu (górnego przecinka) jako pierwszego znaku w komórce powoduje, że cała zawartość komórki traktowana jest przez Excela jako tekst. Wstawiony znak apostrofu nie jest

bezpośrednio wyświetlany w komórce; zobaczyć go można dopiero w trybie edycji lub w pasku formuły.

Fragment arkusza ilustrujący opisane działania przedstawiamy na ﬁlmie.

Kopiowanie kolejnych formuł i wklejanie ich do sąsiednich komórek po wprowadzeniu znaku apostrofu okaże się – nie ma co ukrywać – czynnością czasochłonną. Niestety, próba zastosowania

autowypełnienia (skopiowanie zawartości komórki w dół) nie da oczekiwanego efektu, ponieważ

zawartością komórek jest tekst. Dlatego warto zastosować inne rozwiązanie: połączenie zalet płynących z zastosowania nazw oraz makr.

W tym celu:

1. Zaznacz komórkę w kolumnie pomocniczej znajdującej się bezpośrednio z prawej strony komórki zawierającej formułę; w przykładzie jest to komórka E5.

2. Na karcie Formuły, w grupie poleceń Nazwy zdefiniowane kliknij przycisk Definiuj nazwę.

3. W polu Nazwy w skoroszycie wprowadź nazwę, np. Formuła.

4. W polu Odwołuje się do wprowadź formułę:

=O.KOMÓRCE(6;D5)

i zatwierdź ustawienia nazwy przyciskiem Dodaj, a następnie OK.

Okno dialogowe deﬁniowania nazw wygląda na tym etapie jak prezentujemy na ﬁlmie.

Po zdeﬁniowaniu nazwy zgodnie z opisem możesz teraz szybko wypełnić komórki kolumn pomocniczych odpowiednimi danymi. Wystarczy, że w komórce E5 wprowadzisz formułę: =Formuła i zatwierdzisz

(4)

klawiszem [Enter], jak standardową funkcję lub formułę Excela.

Efektem zatwierdzenia zdeﬁniowanej formuły jest wyświetlenie w komórce kodu formuły wprowadzonej w sąsiedniej komórce. Teraz możesz skopiować tę formułę do komórek kolumn pomocniczych, w których powinny być wyświetlone formuły. Na ﬁlmie przedstawiamy przykładową tabelę po zastosowaniu

zdeﬁniowanej formuły Formuła.

Wyjaśnienie działania funkcji:

Zasada działania opisanej metody polega na wykorzystaniu funkcji O.KOMÓRCE(), wchodzącej w skład makr Excela 4. Dzięki zachowaniu wstecznej kompatybilności kolejnych wersji Excela możliwe jest ciągłe wykorzystywanie leciwych już funkcji. Za wyświetlenie tekstu w postaci wzoru formuły odpowiada pierwszy parametr funkcji, tj. 6.

Parametr ten może przyjmować również inne wartości. Na przykład:

7 - formuła zwróci format użyty w komórce,

17 - wysokość wiersza (w punktach) w którym znajduje się komórka, 18 - nazwa czcionki użytej w komórce.

Poprzez zmianę w nieznacznym stopniu odniesienia w trakcie definiowania nazwy możesz wpływać na sposób funkcjonowania zdefiniowanej formuły. W przykładzie zastosowano odniesienie do komórki znajdującej się z lewej strony zaznaczenia, stąd też uzyskana formuła powoduje wyświetlenie zawartości (formuł) komórek znajdujących się z lewej strony komórki bieżącej. Jeśli przy definiowaniu nazwy wprowadzisz adres komórki znajdujący się nad zaznaczeniem, pod nim lub z prawej strony zaznaczenia, Twoja formuła będzie generować wyniki zgodne z zastosowaną zasadą.

Mimo że nie używasz Edytora VBA i w module arkusza nie znajdziesz żadnych makropoleceń, przy otwieraniu arkusza zawierającego nazwę zdeﬁniowaną w opisany sposób Excel wyświetli ostrzeżenie o ewentualnych niebezpieczeństwach związanych z istniejącymi w arkuszu makrami. Wynika to właśnie z zastosowania funkcji O.KOMÓRCE().

Innym sposobem jednoczesnego wyświetlenia formuł oraz wyników ich działania jest wykorzystanie wbudowanej funkcjonalności Excela, tj. możliwości wyświetlenia kilku okien tego samego arkusza.

W tym celu:

1. Przejdź do karty Widok i w grupie poleceń Okno wybierz przycisk Nowe okno.

2. Aby wyświetlić obok siebie oba okna arkusza, na karcie Widok, w grupie poleceń Okno po wciśnij przycisk Wyświetlaj obok siebie.

3. Oryginalne i powielone okna Twojego arkusza są teraz wyświetlone obok siebie. Wybierz w jednym z okien tryb wyświetlania formuł (skrót klawiszowy: CTRL + ~), dzięki czemu uzyskasz pożądany efekt:

w jednym oknie wyświetlany jest standardowy arkusz z wynikami formuł, natomiast w drugim Excel pokazuje formuły wprowadzone w komórkach.

Oczywiście możesz w wybranym oknie wykonywać dowolne działania, obserwując jednocześnie ich efekt w drugim oknie arkusza.

(5)

Film dostępny na portalu epodreczniki.pl

Źródło: Contentplus.pl Sp. z o.o., licencja: CC BY-SA 3.0.

Film nawiązujący do treści materiału

Pobierz przykładowe dane:

Plik o rozmiarze 13.03 KB w języku polskim Polecenie 1

Nauczyciel informatyki poprosił cię, abyś na dodatkową ocenę przygotował opracowanie wyników sprawdzianów i kartkówek w twojej klasie. Twoim zadaniem jest na podstawie danych ocen (z dwóch kartkówek o wadze 2 i 3 i dwóch sprawdzianów o wadze 5) obliczyć średnią ważoną i na jej podstawie podać ocenę na koniec semestru, zaokrągloną do liczby całkowitej. Pokaż, że znasz się na rzeczy i dodaj kolumnę, w której nauczyciel będzie mógł bez uruchamiania edycji komórki podejrzeć Twoje rozwiązanie. W tym celu użyj poznanych metod.

Polecenie 2

Nauczyciel informatyki był bardzo zadowolony z wykonanego przez ciebie zadania. Opowiedział nawet w pokoju nauczycielskim, jak twoje rozwiązanie pomogło mu w wystawianiu ocen. Nie trzeba było długo czekać aż inny nauczyciel zgłosi się do ciebie po pomoc. Nauczyciel wychowania

ﬁzycznego miał dość ciągłego obliczania BMI uczniów za pomocą kalkulatora. Poprosił cię

o wyznaczenie wartości BMI uczniów w twojej klasie, a w dodatku, w kolumnie obok zapisanie formuł, z których można skorzystać.

(6)

Samouczek II

Kontrolowanie poprawności obliczeń – wyświetlanie powiązań pomiędzy komórkami

Analiza poprawności lub sposobu działania formuł w arkuszu nie jest zadaniem ani łatwym, ani

wygodnym. Dlatego Excel oferuje narzędzie, dzięki któremu zależności pomiędzy komórkami w arkuszu będą czytelniejsze. Narzędziem tym jest tryb inspekcji formuł. Aby wykorzystać zalety tego trybu, użyjemy poleceń na karcie Formuły w grupie poleceń Inspekcja formuł.

Korzystanie z narzędzi z grupy Inspekcja formuł jest intuicyjne. Aby sprawdzić, z jakimi komórkami powiązana jest wartość w komórce zawierającej formułę, zaznacz ją, a następnie skorzystaj z przycisku Śledź poprzedniki. Excel wyświetli wówczas strzałki do komórki, z której pobierane są dane do formuły z komórki aktywnej. Kolejne użycie przycisku Śledź poprzedniki spowoduje sięgnięcie (jeśli to możliwe) do kolejnych komórek wstecz, związanych z bieżącą. Efekt zastosowania poleceń z grupy inspekcji formuł prezentuje ﬁlm.

Możliwa będzie również analiza w drugą stronę, tj. sprawdzenie, w której formule wykorzystana jest wartość z komórki zaznaczonej. Sprawdzenie takie wywołasz przez użycie przycisku

Śledź zależności. Podobnie jak w przypadku śledzenia poprzedników kolejne użycie tego przycisku spowoduje wyświetlenie się strzałek rysujących powiązania z bardziej odległymi komórkami.

Aby wyczyścić arkusz ze strzałek obrazujących zależności pomiędzy komórkami, skorzystaj z przycisku Usuń strzałki.

Plik o rozmiarze 13.02 KB w języku polskim

(7)

Polecenie 1

Od nowego roku postanowiłeś, że zaczniesz zapisywać wpłacone środki na twoje konto

oszczędnościowe. W pierwszej kolumnie zapisujesz miesiąc, w którym przekazałeś środki, a w drugiej kwotę. Wpłat na konto mogło być kilka w tym samym miesiącu. Żeby obliczyć sumaryczną kwotę wpłaconą w danym miesiącu na konto użyj funkcji SUMA.JEŻELI() i wykonaj dla każdego miesiąca zestawienie wpłaconych środków. Skontroluj twoje obliczenia opcją Śledź zależności, pozwoli ci to na łatwiejsze wykrycie nieoczekiwanych błędów.

Polecenie 2

W tym ćwiczeniu masz za zadanie zsumować wpłacone oszczędności i skontrolować za pomocą opcji Śledź poprzedniki, czy podany przez ciebie sumowany zakres jest prawidłowy.

(8)

Samouczek III

Kontrolowanie poprawności obliczeń – formuły arkusza w notatkach

Kolejnym sposobem na czytelne zaprezentowanie formuł w arkuszu Excela jest wykorzystanie notatek.

Aby wprowadzić formułę do notatki, należy ją tam wkleić.

W tym celu:

1. Zaznacz komórkę zawierającą formułę, którą chcesz pokazać w notatce.

2. Aktywuj tryb edycji formuły, przez użycie klawisza [F2].

3. Zaznacz całą formułę i skopiuj ją do pamięci systemowej za pomocą kombinacji [Ctrl]+[C].

Zauważ, że po uruchomieniu trybu edycji komórki Excela kursor zawsze znajduje się na końcu formuły wpisanej do komórki. Za pomocą kombinacji klawiszy [Shift]+[Home] spowodujesz zaznaczenie całej zawartości komórki, co pozwoli Ci wygodnie ją skopiować.

4. Wciśnij klawisz [Esc], aby wyjść z trybu edycji.

5. Kliknij prawym przyciskiem myszy bieżącą komórkę i wybierz z menu kontekstowego polecenie Nowa notatka.

6. Skasuj domyślnie wprowadzoną w notatce nazwę użytkownika i wklej formułę do treści notatki.

W celu zatwierdzenia/zapisania zmian w notatce wystarczy kliknąć dowolną inną komórkę w arkuszu.

Dzięki przedstawionym czynnościom kliknięcie skomentowanej komórki lub najechanie nad nią kursorem myszki spowoduje wyświetlenie notatki zawierającej treść zawartej w niej formuły.

Plik o rozmiarze 13.03 KB w języku polskim

(9)

Polecenie 1

Załóżmy, że podczas wakacji postanowiłeś zadbać o swoją sprawność ﬁzyczną. Codziennie przed pójściem spać ze sportowego zegarka odczytujesz ilość kroków, które przeszedłeś danego dnia i wpisujesz do tabeli w Excelu. Po kilkunastu dniach chcesz sprawdzić jaka była średnia liczba przebytych kroków. Użyj do tego odpowiedniej funkcji, a dodatkowo zapisz wykorzystywaną formułę w notatce.

Polecenie 2

W tym ćwiczeniu Twoim zadaniem jest sprawdzenie, w którym dniu zrobiłeś najwięcej kroków. W tym celu oblicz, ile najwięcej kroków zrobiłeś, następnie wykorzystując tę informację oblicz, którego dnia miało to miejsce. Na koniec wykorzystaną formułę zapisz do notatki i dodaj śledzenie poprzedników, aby sprawdzić poprawność obliczeń.

(10)

Dla nauczyciela

Autor: Karolina Biegus Przedmiot: Informatyka

Temat: Powiązania i komentarze w kontroli poprawności obliczeń w arkuszu kalkulacyjnym Grupa docelowa:

III etap edukacyjny, liceum ogólnokształcące, technikum, zakres rozszerzony Podstawa programowa:

Zakres podstawowy i rozszerzony Cele kształcenia – wymagania ogólne

2) Programowanie i rozwiązywanie problemów z wykorzystaniem komputera oraz innych urządzeń cyfrowych:

układanie i programowanie algorytmów, organizowanie, wyszukiwanie i udostępnianie informacji, posługiwanie się aplikacjami komputerowymi.

Zakres rozszerzony

II. Programowanie i rozwiązywanie problemów z wykorzystaniem komputera i innych urządzeń cyfrowych.

Uczeń spełnia wymagania określone dla zakresu podstawowego, a ponadto:

4. przygotowując opracowania rozwiązań złożonych problemów, posługuje się wybranymi aplikacjami w stopniu zaawansowanym:

3) stosuje zaawansowane funkcje arkusza kalkulacyjnego w zależności od rodzaju danych, deﬁniuje makropolecenia, zna możliwości wbudowanego języka programowania,

Kształtowane kompetencje kluczowe:

kompetencje obywatelskie;

kompetencje cyfrowe;

kompetencje osobiste, społeczne i w zakresie umiejętności uczenia się;

kompetencje matematyczne oraz kompetencje w zakresie nauk przyrodniczych, technologii i inżynierii.

Cele operacyjne (językiem ucznia):

Dowiedz się, co trzeba zrobić, aby jednocześnie wyświetlać formuły i wyniki.

Poznasz narzędzie, które pozwoli Ci analizować poprawność lub sposób działania formuł.

Będziesz kontrolować poprawność obliczeń wykorzystując komentarze.

Strategie nauczania:

konstruktywizm;

konektywizm.

Metody i techniki nauczania:

dyskusja;

rozmowa nauczająca z wykorzystaniem multimedium i ćwiczeń interaktywnych.

Formy pracy:

praca indywidualna;

praca w parach;

praca w grupach;

praca całego zespołu klasowego.

Środki dydaktyczne:

(11)

komputery z głośnikami, słuchawkami i dostępem do internetu;

zasoby multimedialne zawarte w e‑materiale;

tablica interaktywna/tablica, pisak/kreda;

oprogramowanie Microsoft Excel 2019 lub wybrany odpowiednik.

Przebieg lekcji Przed lekcją:

1. Przygotowanie do zajęć. Nauczyciel loguje się na platformie i udostępnia e‑materiał: „Powiązania i komentarze w kontroli poprawności obliczeń w arkuszu kalkulacyjnym”. Uczniowie zapoznają się z treściami w sekcji „Samouczek I”.

Faza wstępna:

1. Nauczyciel wyświetla uczniom temat zajęć oraz cele. Prosi, by na ich podstawie uczniowie sformułowali kryteria sukcesu.

Faza realizacyjna:

1. Nauczyciel wyświetla zawartość sekcji „Samouczek I”. Na forum klasy uczniowie analizują zawarty w niej ﬁlm. Wybrany uczestnik zajęć czyta treść polecenia nr 1 proponuje rozwiązanie i omawia jego kolejne kroki.

2. Pozostając w sekcji „Samouczek I” uczniowie w zespołach dwuosobowych zapoznają się z treścią polecenia nr 2: „Nauczyciel informatyki był bardzo zadowolony z wykonanego przez ciebie zadania.

Opowiedział nawet w pokoju nauczycielskim, jak twoje rozwiązanie pomogło mu w wystawianiu ocen. Nie trzeba było długo czekać aż inny nauczyciel zgłosi się do ciebie o pomoc. Nauczyciel wychowania ﬁzycznego miał dość ciągłego obliczania BMI uczniów za pomocą kalkulatora. Poprosił cię o wyznaczenie wartości BMI uczniów w twojej klasie, a w dodatku, w kolumnie obok zapisanie formuł, z których można skorzystać.” i wspólnie analizują kolejne kroki rozwiązania postawionego problemu.

3. Nauczyciel wyświetla na tablicy ﬁlm zawarty w sekcji „Samouczek II”. Następnie odczytuje treść polecenia nr 2. Uczniowie pracują w parach. Wybrana grupa omawia rozwiązanie na forum klasy.

4. Uczniowie wykonują w parach kolejne ćwiczenia i po rozwiązaniu każdego z nich porównują swoje odpowiedzi z inną parą.

5. Nauczyciel przechodzi do sekcji „Samouczek III”. Zapowiada uczniom, że w kolejnym kroku będą rozwiązywać ćwiczenia, ale najpierw zapoznają się z treściami zawartymi w tej sekcji. Uczniowie wspólnie analizują udostępnione materiały.

6. Praca indywidualna – implementacja poznanej techniki do rozwiązywania problemów informatycznych – wykonywanie ćwiczeń z sekcji „Samouczek III”.

Faza podsumowująca:

1. Nauczyciel wyświetla na tablicy temat lekcji i cele zawarte w sekcji „Wprowadzenie”. W kontekście ich realizacji podsumowuje przebieg zajęć, a także wskazuje mocne i słabe strony pracy uczniów.

Praca domowa:

1. Uczniowie wykonują ostatnie ćwiczenie interaktywne i przygotowują uzasadnienie poprawnej odpowiedzi

Materiały pomocnicze:

Oﬁcjalna dokumentacja techniczna dla oprogramowania Microsoft Excel 2019 lub wybranego odpowiednika.

Wskazówki metodyczne:

Treści w sekcji „Samouczek II” można wykorzystać jako materiał, służący powtórzeniu materiału.

(12)