Kompletność wprowadzanych informacji

Jeżeli wraca do nas zestawienie uzupełniane przez kolegę i zawiera niewypełnione komórki, mamy co najmniej dwa wyjścia z takiej sytuacji: uzupełnić luki samodzielnie lub odesłać do uzupełnienia. Niestety w obu przypadkach wydłuży się czas sporządzenia wykazu, a może się zdarzyć, że potrzebujemy go pilnie. Aby uniknąć takich sytuacji, na etapie projektowania arkusza nałóż odpowiednią regułę sprawdzania poprawności. Excel będzie wówczas sam kontrolował, czy wszystkie rubryki zostały wypełnione. Pobierz plik z przykładowymi danymi i wykonaj poniższe kroki:

1. Zaznaczamy zakres komórek, na które ma być nałożona reguła sprawdzania poprawności, np. A4:D15.

2. Wywołujemy okno dialogowe Sprawdzanie poprawności i przechodzimy do zakładki Ustawienia.

3. Z pierwszej rozwijanej listy wybieramy pozycję Niestandardowe.

4. W pole poniżej wprowadzamy następującą formułę:

=ILE.NIEPUSTYCH($A$3:$D3)=(ILE.WIERSZY($A$3:$D3)*LICZBA.KOLUMN($A3:$D3))

5. Klikamy przycisk OK, aby uaktywnić sprawdzanie poprawności.

Teraz zweryfikujmy, jak działa arkusz. Wpisujemy do komórki A10 nazwę kolejnego miasta. Excel nie dopuści do wprowadzenia tego tekstu i wyświetli komunikat ostrzegawczy. Wpis w wierszu 10. będzie możliwy dopiero wówczas, gdy wszystkie komórki w wierszu 9. zostaną wypełnione.

Wyjaśnienie działania formuły:

• Lewa strona równania [ILE.NIEPUSTYCH($A$3:$D3)] ma za zadanie sprawdzić, ile niepustych komórek znajduje się w zakresie A3:D3. Zauważmy, że w odwołaniu zastosowano zarówno odwołanie bezwzględne ($A$3), jak i mieszane ($D3). Dzięki temu składnia formuły dostosowuje się dla każdej komórki sprawdzanego zakresu. Przykładowo dla komórki B5 lewa strona równania będzie wyglądała następująco: ILE. NIEPUSTYCH($A$3:$D4). Oznacza to, że niezależnie od położenia komórki formuła będzie zawsze zliczała liczbę wartości w części obszaru zestawienia znajdującego się nad wierszem zawierającym daną komórkę. Dla komórki A4 lewa strona równania zwróci wartość 4, ponieważ w zakresie komórek A3:D3 znajdują się 4 wypełnione komórki, zawierające nagłówki kolumn.

• Prawa strona równania [(ILE.WIERSZY($A$3:$D3)*LICZBA.KOLUMN($A3:$D3))] wygląda na bardziej skomplikowaną, ale tylko pozornie. Funkcja ILE.WIERSZY, jak sama nazwa wskazuje, zwraca liczbę wierszy w podanym zakresie (tablicy). Funkcja LICZBA.KOLUMN analogicznie do poprzedniej zwraca liczbę kolumn. Iloczyn tych funkcji zwraca zatem całkowitą liczbę komórek z zakresu odpowiadającemu dokładnie zakresowi sprawdzanemu przez lewą stronę równania.

• A zatem jeżeli liczba wypełnionych komórek (lewa strona równania) jest równa liczbie wszystkich komórek (prawa strona równania), formuła zwraca wartość PRAWDA i wpis jest dozwolony.

R1NdaO1i96uMG

Nagranie filmowe dotyczące kompletności wprowadzanych informacji.

Nagranie filmowe dotyczące kompletności wprowadzanych informacji.

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

Nagranie filmowe dotyczące kompletności wprowadzanych informacji.

Pobierz przykładowe dane:

Ćwiczenie 1

Codziennie poza lekcjami uczęszczasz na informatyczne koło naukowe. Nauczyciel prowadzący poprosił cię o pomoc przy sprawdzaniu obecności. Przygotuj arkusz, w którym w kolejnych kolumnach wpiszesz dni przyszłego tygodnia rozpoczynając od poniedziałku. Arkusz będzie dostępny przy pierwszym stanowisku komputerowym w sali i każdy uczeń, który przyjdzie na zajęcia, będzie proszony o wpisanie swojego imienia i nazwiska. Wykorzystując nowo poznane metody sprawdzania kompletności wprowadzanych informacji, zabezpiecz arkusz, by nie było pustych wierszy w konkretnym dniu między wpisującymi się uczniami.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

Wykorzystana formuła sprawdzająca poprawność w pierwszej kolumnie (poniedziałek):

Linia 1. znak równości ILE kropka NIEPUSTYCH otwórz nawias okrągły $A$1 dwukropek $A1 zamknij nawias okrągły znak równości ILE kropka WIERSZY otwórz nawias okrągły $A$1 dwukropek $A1 zamknij nawias okrągły.

=ILE.NIEPUSTYCH($A$1:$A1)=ILE.WIERSZY($A$1:$A1)

R12PNk6oUsVlc

Na zrzucie ekranu widoczny jest fragment arkusza Excel. Komórkę A1 zatytułowano Poniedziałek, komórkę B1 Wtorek, komórkę C1 Środa, komórkę D1 Czwartek, komórkę E1 Piątek. W kolumnie A w komórkach od A2 do A4 oraz A6 wpisano imiona i nazwiska. Komórkę A6 otoczono zieloną ramką. Pozostałe komórki są puste. Dodatkowo na arkuszu wyświetla się okno z następującą informacją: "Alert. Ta wartość jest niezgodna z ograniczeniami sprawdzania dostępności poprawności danych, które są zdefiniowane dla tej komórki". Do wyboru są przyciski Anuluj lub Ponów próbę.

Ćwiczenie 2

Razem z rodzeństwem planujecie utworzyć arkusz kalkulacyjny, w którym będziecie zapisywać wszelkie wydatki związane z przygotowaniem do rozpoczęcia roku szkolnego. Arkusz powinien zawierać cztery kolumny: datę wydatku, osobę, która wydatek poniosła, przedmiot, jaki został zakupiony oraz jego cenę. Wykorzystaj sprawdzanie poprawności danych, aby uniemożliwić wpisanie kolejnego wydatku, jeżeli poprzedni wiersz nie zawiera wpisanych wszystkich informacji.

Pokaż rozwiązanie

Przykładowe rozwiązanie zadania:

Wykorzystana formuła sprawdzająca poprawność:

Linia 1. znak równości ILE kropka NIEPUSTYCH otwórz nawias okrągły $A$1 dwukropek $D1 zamknij nawias okrągły znak równości otwórz nawias okrągły ILE kropka WIERSZY otwórz nawias okrągły $A$1 dwukropek $D1 zamknij nawias okrągły asterysk LICZBA kropka KOLUMN otwórz nawias okrągły $A1 dwukropek $D1 zamknij nawias okrągły zamknij nawias okrągły.

=ILE.NIEPUSTYCH($A$1:$D1)=(ILE.WIERSZY($A$1:$D1)*LICZBA.KOLUMN($A1:$D1))

RAdf7QzyJShzz

Na zrzucie ekranu widoczny jest fragment arkusza Excel. W komórce A1 wpisano tytuł Data wydatku. W komórce B1 wpisano tytuł Osoba. W komórce C1 wpisano tytuł Przedmiot. W komórce D1 wpisano tytuł Cena. W kolumnie A w komórkach od A2 do A6 wpisano daty wydatków. Komórkę A6 zaznaczono ramką. W kolumnie B w komórkach od B2 do B5 wpisano imiona. W kolumnie C w komórkach od C2 do C4 wpisano przedmioty szkolne. W kolumnie D w komórkach od D2 do D5 wpisano ceny. Dodatkowo na arkuszu wyświetla się okno z następującą informacją: "Alert. Ta wartość jest niezgodna z ograniczeniami sprawdzania dostępności poprawności danych, które są zdefiniowane dla tej komórki". Do wyboru są przyciski Anuluj lub Ponów próbę.