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 filmu i wykonaj następujące czynności:

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 klawisz [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 filmie.

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 definiowania nazw wygląda na tym etapie jak prezentujemy na filmie.

Po zdefiniowaniu 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 klawiszem [Enter], jak standardową funkcję lub formułę Excela.

Efektem zatwierdzenia zdefiniowanej 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 filmie przedstawiamy przykładową tabelę po zastosowaniu zdefiniowanej 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ę zdefiniowaną 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.

R14NUByEGPpCL

Nagranie filmowe dotyczące kontrolowania poprawności obliczeń i jednoczesnego wyświetlania formuł i wyników.

Nagranie filmowe dotyczące kontrolowania poprawności obliczeń i jednoczesnego wyświetlania formuł i wyników.

Film dostępny pod adresem /preview/resource/R14NUByEGPpCL

Nagranie filmowe dotyczące kontrolowania poprawności obliczeń i jednoczesnego wyświetlania formuł i wyników.

Pobierz przykładowe dane:

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.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

RuuVXG5KVCXQv

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnach A, B, C, D, E wprowadzono dane dotyczące wyników sprawdzianów i kartkówek poszczególnych uczniów w klasie. W arkuszu kolejno dodano opisy: w komórce A2 Numer ucznia, w komórce B2 Kartkówka 1, w komórce C2 Sprawdzian 1, w komórce D2 Kartkówka 2, w komórce E2 Sprawdzian 2, w komórce F2 Średnia ważona. W kolumnie A, w komórkach od A3 do A17 wprowadzono numer kolejnego ucznia. W kolumnie B, w komórkach od B3 do B17 wprowadzono oceny z kartkówki 1. W kolumnie C, w komórkach od C3 do C17 wprowadzono oceny ze sprawdzianu 1. W kolumnie D, w komórkach od D3 do D17 wprowadzono oceny z kartkówki 2. W kolumnie E, w komórkach od E3 do E17 wprowadzono oceny ze sprawdzianu 2. Dodatkowo komórkę A1 zatytułowano waga strzałka w prawo. W kolumnie B, w komórce B1 wprowadzono 3. W kolumnie C, w komórce C1 wprowadzono 5. W kolumnie D, w komórce D1 wpisano 2. W kolumnie E, w komórce E1 wprowadzono 5. Dodatkowo zaprezentowano sposób obliczania średniej ważonej i na jej podstawie podać ocenę na koniec semestru, z uwzględnieniem zaokrąglenia do liczby całkowitej. Formuły obliczania należy wpisać w komórkach od G3 do G17. Formuła w komórce G3 brzmi następująco: =ZAOKR(SUMA.ILOCZYNÓW(B3:E3;$B$1:$E$1)/SUMA($B$1:$E$1);0). Formuła w komórce G4 brzmi następująco: =ZAOKR(SUMA.ILOCZYNÓW(B4:E4;$B$1:$E$1)/SUMA($B$1:$E$1);0). Formuła w komórce G5 brzmi następująco: =ZAOKR(SUMA.ILOCZYNÓW(B5:E5;$B$1:$E$1)/SUMA($B$1:$E$1);0). Formuła w komórce G6 brzmi następująco: =ZAOKR(SUMA.ILOCZYNÓW(B6:E6;$B$1:$E$1)/SUMA($B$1:$E$1);0). Formuła w komórce G7 brzmi następująco: =ZAOKR(SUMA.ILOCZYNÓW(B7:E7;$B$1:$E$1)/SUMA($B$1:$E$1);0). Formuła w komórce G7 brzmi następująco: =ZAOKR(SUMA.ILOCZYNÓW(B7:E7;$B$1:$E$1)/SUMA($B$1:$E$1);0). Formuła w komórce G8 brzmi następująco: =ZAOKR(SUMA.ILOCZYNÓW(B8:E8;$B$1:$E$1)/SUMA($B$1:$E$1);0). Formuła w komórce G9 brzmi następująco: =ZAOKR(SUMA.ILOCZYNÓW(B9:E9;$B$1:$E$1)/SUMA($B$1:$E$1);0). Formuła w komórce G10 brzmi następująco: =ZAOKR(SUMA.ILOCZYNÓW(B10:E10;$B$1:$E$1)/SUMA($B$1:$E$1);0). Formuła w komórce G11 brzmi następująco: =ZAOKR(SUMA.ILOCZYNÓW(B11:E11;$B$1:$E$1)/SUMA($B$1:$E$1);0). Formuła w komórce G12 brzmi następująco: =ZAOKR(SUMA.ILOCZYNÓW(B12:E12;$B$1:$E$1)/SUMA($B$1:$E$1);0). Formuła w komórce G13 brzmi następująco: =ZAOKR(SUMA.ILOCZYNÓW(B13:E13;$B$1:$E$1)/SUMA($B$1:$E$1);0). Formuła w komórce G14 brzmi następująco: =ZAOKR(SUMA.ILOCZYNÓW(B14:E14;$B$1:$E$1)/SUMA($B$1:$E$1);0). Formuła w komórce G15 brzmi następująco: =ZAOKR(SUMA.ILOCZYNÓW(B15:E15;$B$1:$E$1)/SUMA($B$1:$E$1);0). Formuła w komórce G16 brzmi następująco: =ZAOKR(SUMA.ILOCZYNÓW(B16:E16;$B$1:$E$1)/SUMA($B$1:$E$1);0). Formuła w komórce G17 brzmi następująco: =ZAOKR(SUMA.ILOCZYNÓW(B17:E17;$B$1:$E$1)/SUMA($B$1:$E$1);0).

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 fizycznego 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ć.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

RlczwZBHa0nmh

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W kolumnie A, B i C wprowadzono dane dotyczące wyznaczenia wartości BMI każdego ucznia na podstawie wzrostu i wagi. W arkuszu kolejno dodano opisy: w komórce A2 Numer ucznia, w komórce B2 wzrost w metrach, w komórce C2 wagę w kilogramach. W kolumnie A, w komórkach od A3 do A17 wprowadzono numer kolejnego ucznia. W kolumnie B, w komórkach od B3 do B17 wprowadzono wzrost uczniów. W kolumnie C, w komórkach od C3 do C17 wprowadzono wagę uczniów. Dodatkowo zaprezentowano sposób obliczania wartości BMI z uwzględnieniem zaokrąglenia do liczby całkowitej. Formuły obliczania należy wpisać w komórkach od E3 do E17. W komórce E3 brzmi ona następująco: =ZAOKR(C3/POTĘGA(B3;2);2). W komórce E4 brzmi ona następująco: =ZAOKR(C4/POTĘGA(B4;2);2). W komórce E5 brzmi ona następująco: =ZAOKR(C5/POTĘGA(B5;2);2). W komórce E6 brzmi ona następująco: =ZAOKR(C6/POTĘGA(B6;2);2). W komórce E7 brzmi ona następująco: =ZAOKR(C7/POTĘGA(B7;2);2). W komórce E8 brzmi ona następująco: =ZAOKR(C8/POTĘGA(B8;2);2). W komórce E9 brzmi ona następująco: =ZAOKR(C9/POTĘGA(B9;2);2). W komórce E10 brzmi ona następująco: =ZAOKR(C10/POTĘGA(B10;2);2). W komórce E11 brzmi ona następująco: =ZAOKR(C11/POTĘGA(B11;2);2). W komórce E12 brzmi ona następująco: =ZAOKR(C12/POTĘGA(B12;2);2). W komórce E13 brzmi ona następująco: =ZAOKR(C13/POTĘGA(B13;2);2). W komórce E14 brzmi ona następująco: =ZAOKR(C14/POTĘGA(B14;2);2). W komórce E15 brzmi ona następująco: =ZAOKR(C15/POTĘGA(B15;2);2). W komórce E16 brzmi ona następująco: =ZAOKR(C16/POTĘGA(B16;2);2). W komórce E17 brzmi ona następująco: =ZAOKR(C17/POTĘGA(B17;2);2).