Analiza danych programu Excel - walidacja danych

Sprawdzanie poprawności danych jest bardzo przydatnym i łatwym w użyciu narzędziem w programie Excel, za pomocą którego można ustawić walidację danych wprowadzanych do arkusza roboczego.

Możesz to zrobić dla dowolnej komórki w arkuszu

Wyświetla komunikat wejściowy o tym, co należy do niego wprowadzić.
Ogranicz wprowadzane wartości.
Podaj listę wartości do wyboru.
Wyświetl komunikat o błędzie i odrzuć nieprawidłowy wpis danych.

Weź pod uwagę następujący moduł śledzenia ryzyka, którego można użyć do wprowadzania i śledzenia informacji o zidentyfikowanych zagrożeniach.

W tym module śledzącym dane wprowadzane do następujących kolumn są sprawdzane za pomocą wstępnie ustawionych ograniczeń danych, a wprowadzone dane są akceptowane tylko wtedy, gdy spełniają kryteria walidacji. W przeciwnym razie zostanie wyświetlony komunikat o błędzie.

Probability
Impact
Kategoria ryzyka
Źródło ryzyka
Status

Kolumna Ekspozycja na ryzyko będzie zawierała obliczone wartości i nie możesz wprowadzić żadnych danych. Nawet kolumnaS. No. jest ustawiony tak, aby mieć obliczone wartości, które są korygowane nawet po usunięciu wiersza.

Teraz dowiesz się, jak skonfigurować taki arkusz.

Przygotuj strukturę arkusza roboczego

Aby przygotować strukturę arkusza roboczego -

Zacznij od pustego arkusza.
Umieść nagłówek w wierszu 2.
Umieść nagłówki kolumn w rzędzie 3.
W przypadku nagłówków kolumn Prawdopodobieństwo, wpływ i narażenie na ryzyko -
- Kliknij komórkę prawym przyciskiem myszy.
- Kliknij opcję Formatuj komórki z listy rozwijanej.
- W oknie dialogowym Formatowanie komórek kliknij kartę Wyrównanie.
- Wpisz 90 pod Orientacja.
Scal i wyśrodkuj komórki w wierszach 3, 4 i 5 dla każdego z nagłówków kolumn.
Sformatuj obramowanie komórek w wierszach 2 - 5.
Dostosuj szerokość wierszy i kolumn.

Twój arkusz będzie wyglądał następująco -

Ustaw prawidłowe wartości dla kategorii ryzyka

W komórkach M5 - M13 wprowadź następujące wartości (M5 to kurs, a M6 - M13 to wartości)

Wartości kategorii
Użytkownicy końcowi
Klient
Zarządzanie
Harmonogram
Harmonogram
Środowisko
Produkt
Projekt

Kliknij pierwszą komórkę w kolumnie Kategoria ryzyka (H6).
Kliknij kartę DANE na Wstążce.
Kliknij opcję Sprawdzanie danych w grupie Narzędzia danych.
Wybierz opcję Data Validation… z listy rozwijanej.

Pojawi się okno dialogowe Data Validation.

Kliknij kartę Ustawienia.
W obszarze Kryteria walidacji w Allow: rozwijana lista, Wybierz opcję List.

Wybierz zakres M6: M13 w wyświetlonym oknie Source:.
Zaznacz pola Ignoruj puste i menu rozwijane w komórce, które się pojawiają.

Ustaw komunikat wejściowy dla kategorii ryzyka

Kliknij kartę Komunikat wejściowy w oknie dialogowym Sprawdzanie poprawności danych.
Sprawdź pudełko Show input message gdy komórka jest zaznaczona.
W polu pod tytułem: wpisz Kategoria ryzyka:
W polu pod Komunikatem wejściowym: Wybierz kategorię ryzyka z listy.

Ustaw alert o błędzie dla kategorii ryzyka

Aby ustawić alert o błędzie -

Kliknij kartę Alert błędu w oknie dialogowym Sprawdzanie poprawności danych.
Zaznacz pole Pokaż alert o błędzie po wprowadzeniu nieprawidłowych danych.
Wybierz Zatrzymaj w menu Styl:
W polu pod tytułem: wpisz Invalid Entry:
W polu pod Komunikatem o błędzie: wpisz Wybierz wartość z listy rozwijanej.
Kliknij OK.

Sprawdź walidację danych dla kategorii ryzyka

Dla wybranej pierwszej komórki w kategorii ryzyka,

Kryteria walidacji danych są ustawione
Komunikat wejściowy jest ustawiony
Ustawiono alert o błędzie

Teraz możesz zweryfikować swoje ustawienia.

Kliknij komórkę, dla której ustawiłeś kryteria sprawdzania poprawności danych. Pojawi się komunikat Input. Przycisk rozwijania pojawia się po prawej stronie komórki.

Komunikat wejściowy jest wyświetlany poprawnie.

Kliknij przycisk listy rozwijanej po prawej stronie komórki. Zostanie wyświetlona lista rozwijana z wartościami, które można wybrać.
Sprawdź wartości na liście rozwijanej z tymi, które są używane do tworzenia listy rozwijanej.

Oba zestawy wartości są zgodne. Zwróć uwagę, że jeśli liczba wartości jest większa, po prawej stronie listy rozwijanej pojawi się pasek przewijania w dół.

Wybierz wartość z listy rozwijanej. Pojawia się w celi.

Widać, że wybór prawidłowych wartości działa prawidłowo.

Na koniec spróbuj wprowadzić nieprawidłowy wpis i sprawdź alert o błędzie.

Wpisz People w komórce i naciśnij Enter. Zostanie wyświetlony komunikat o błędzie ustawiony dla komórki.

Sprawdź komunikat o błędzie.
Możesz spróbować ponownie lub anulować. Sprawdź obie opcje.

Pomyślnie ustawiłeś walidację danych dla komórki.

Note - Sprawdzanie pisowni i gramatyki wiadomości jest bardzo ważne.

Ustaw prawidłowe kryteria dla kolumny kategorii ryzyka

Teraz możesz zastosować kryteria walidacji danych do wszystkich komórek w kolumnie Kategoria ryzyka.

W tym momencie musisz pamiętać o dwóch rzeczach -

Musisz ustawić kryteria maksymalnej liczby komórek, które można wykorzystać. W naszym przykładzie może wynosić od 10 do 100 w zależności od tego, gdzie będzie używany arkusz.
Nie należy ustawiać kryteriów dla niepożądanego zakresu komórek ani całej kolumny. Spowoduje to niepotrzebne zwiększenie rozmiaru pliku. Nazywa się to nadmiernym formatowaniem. Jeśli otrzymasz arkusz roboczy z zewnętrznego źródła, musisz usunąć nadmierne formatowanie, o czym dowiesz się w rozdziale dotyczącym zapytania w tym samouczku.

Wykonaj czynności podane poniżej -

Ustaw kryteria walidacji dla 10 komórek w kategorii ryzyka.
Możesz to łatwo zrobić, klikając w prawym dolnym rogu pierwszej komórki.
Przytrzymaj symbol +, który się pojawi, i pociągnij go w dół.

Sprawdzanie poprawności danych jest ustawione dla wszystkich wybranych komórek.

Kliknij ostatnią wybraną kolumnę i zweryfikuj.

Weryfikacja danych w kolumnie Kategoria ryzyka została zakończona.

Ustaw wartości weryfikacji dla źródła ryzyka

W tym przypadku mamy tylko dwie wartości - wewnętrzną i zewnętrzną.

Kliknij pierwszą komórkę w kolumnie Źródło ryzyka (I6)
Kliknij kartę DANE na Wstążce
Kliknij opcję Sprawdzanie danych w grupie Narzędzia danych
Wybierz opcję Data Validation… z listy rozwijanej.

Pojawi się okno dialogowe Data Validation.

Kliknij kartę Ustawienia.
W obszarze Kryteria walidacji na liście rozwijanej Zezwalaj: wybierz opcję Lista.
Wpisz Internal, External w polu Source:, które się pojawi.
Zaznacz pola Ignoruj puste i menu rozwijane w komórce, które się pojawiają.

Ustaw komunikat wejściowy dla źródła ryzyka.

Ustaw alert o błędzie dla źródła ryzyka.

Dla wybranej pierwszej komórki w obszarze Źródło ryzyka -

Kryteria walidacji danych są ustawione
Komunikat wejściowy jest ustawiony
Ustawiono alert o błędzie

Teraz możesz zweryfikować swoje ustawienia.

Kliknij komórkę, dla której ustawiłeś kryteria sprawdzania poprawności danych. Pojawia się komunikat wejściowy. Przycisk rozwijany pojawi się po prawej stronie komórki.

Komunikat wejściowy jest wyświetlany poprawnie.

Kliknij przycisk strzałki rozwijanej po prawej stronie komórki. Zostanie wyświetlona lista rozwijana z wartościami, które można wybrać.
Sprawdź, czy wartości są takie same, jak wpisałeś - wewnętrzne i zewnętrzne.

Oba zestawy wartości są zgodne. Wybierz wartość z listy rozwijanej. Pojawia się w celi.

Widać, że wybór prawidłowych wartości działa prawidłowo. Na koniec spróbuj wprowadzić nieprawidłowy wpis i sprawdź alert o błędzie.

Wpisz Financial w komórce i naciśnij Enter. Zostanie wyświetlony komunikat o błędzie ustawiony dla komórki.

Sprawdź komunikat o błędzie. Pomyślnie ustawiłeś walidację danych dla komórki.
Ustaw prawidłowe kryteria dla kolumny źródła ryzyka
Zastosuj kryteria walidacji danych do komórek I6 - I15 w kolumnie Źródło ryzyka (tj. Taki sam zakres jak w kolumnie Kategoria ryzyka).

Sprawdzanie poprawności danych jest ustawione dla wszystkich wybranych komórek. Weryfikacja danych w kolumnie Źródło ryzyka została zakończona.

Ustaw wartości walidacji dla statusu

Powtórz te same kroki, które były używane do ustawiania wartości walidacji dla źródła ryzyka.
Ustaw wartości listy jako otwarte, zamknięte.
Zastosuj kryteria walidacji danych do komórek K6 - K15 w kolumnie Status (tj. Taki sam zakres jak w kolumnie Kategoria ryzyka).

Sprawdzanie poprawności danych jest ustawione dla wszystkich wybranych komórek. Sprawdzanie poprawności danych dla stanu kolumny zostało zakończone.

Ustaw wartości sprawdzania prawdopodobieństwa

Wartości wskaźnika prawdopodobieństwa ryzyka mieszczą się w zakresie 1-5, przy czym 1 oznacza niski, a 5 wysoki. Wartość może być dowolną liczbą całkowitą od 1 do 5, obie włącznie.

Kliknij pierwszą komórkę w kolumnie Źródło ryzyka (I6).
Kliknij kartę DANE na Wstążce.
Kliknij opcję Sprawdzanie danych w grupie Narzędzia danych.
Wybierz opcję Data Validation… z listy rozwijanej.

Pojawi się okno dialogowe Data Validation.

Kliknij kartę Ustawienia.
W obszarze Kryteria walidacji na liście rozwijanej Zezwalaj: wybierz opcję Cała liczba.

Wybierz jedną z opcji w obszarze Dane:
Wpisz 1 w polu pod Minimum:
Wpisz 5 w polu pod Maksimum:

Ustaw komunikat wejściowy dla prawdopodobieństwa

Ustaw alert o błędzie dotyczący prawdopodobieństwa i kliknij przycisk OK.

Dla wybranej pierwszej komórki w obszarze Prawdopodobieństwo,

Kryteria walidacji danych są ustawione.
Komunikat wejściowy jest ustawiony.
Ustawiono alert o błędzie.

Teraz możesz zweryfikować swoje ustawienia.

Kliknij komórkę, dla której ustawiłeś kryteria sprawdzania poprawności danych. Pojawia się komunikat wejściowy. W takim przypadku nie będzie przycisku rozwijania, ponieważ wartości wejściowe są ustawione w zakresie, a nie z listy.

Komunikat wejściowy jest wyświetlany poprawnie.

Wprowadź do komórki liczbę całkowitą od 1 do 5. Pojawia się w celi.

Wybór prawidłowych wartości działa prawidłowo. Na koniec spróbuj wprowadzić nieprawidłowy wpis i sprawdź alert o błędzie.

Wpisz 6 w komórce i naciśnij Enter. Zostanie wyświetlony komunikat o błędzie ustawiony dla komórki.

Pomyślnie ustawiłeś walidację danych dla komórki.

Ustaw prawidłowe kryteria dla kolumny Prawdopodobieństwo.
Zastosuj kryteria walidacji danych do komórek E6 - E15 w kolumnie Prawdopodobieństwo (tj. Taki sam zakres, jak w kolumnie Kategoria ryzyka).

Sprawdzanie poprawności danych jest ustawione dla wszystkich wybranych komórek. Weryfikacja danych w kolumnie Prawdopodobieństwo została zakończona.

Ustaw wartości walidacji dla wpływu

Aby ustawić wartości sprawdzania poprawności dla Impact, powtórz te same kroki, które były używane do ustawiania wartości sprawdzania dla prawdopodobieństwa.

Zastosuj kryteria walidacji danych do komórek F6 - F15 w kolumnie Wpływ (tj. Taki sam zakres, jak w kolumnie Kategoria ryzyka).

Sprawdzanie poprawności danych jest ustawione dla wszystkich wybranych komórek. Weryfikacja danych dla kolumny Impact została zakończona.

Ustaw kolumnę Ekspozycja na ryzyko z obliczonymi wartościami

Ekspozycja na ryzyko jest obliczana jako iloczyn prawdopodobieństwa ryzyka i wpływu ryzyka.

Ekspozycja na ryzyko = prawdopodobieństwo * wpływ

Wpisz = E6 * F6 w komórce G6 i naciśnij Enter.

0 zostanie wyświetlone w komórce G6, ponieważ E6 i F6 są puste.

Skopiuj formułę z komórek G6 - G15. 0 zostanie wyświetlone w komórkach G6 - G15.

Ponieważ kolumna Ekspozycja na ryzyko jest przeznaczona do obliczania wartości, nie należy zezwalać na wprowadzanie danych w tej kolumnie.

Wybierz komórki G6-G15
Kliknij prawym przyciskiem myszy i na wyświetlonej liście rozwijanej wybierz opcję Formatuj komórki. Zostanie wyświetlone okno dialogowe Formatowanie komórek.
Kliknij zakładkę Ochrona.
Sprawdź opcję Locked.

Ma to na celu zapewnienie, że wprowadzanie danych nie będzie dozwolone w tych komórkach. Jednak wejdzie to w życie tylko wtedy, gdy arkusz jest chroniony, co zrobisz jako ostatni krok po przygotowaniu arkusza.

Kliknij OK.
Zacieniuj komórki G6-G15, aby wskazać, że są to obliczone wartości.

Sformatuj wartości numerów seryjnych

Możesz pozostawić użytkownikowi wypełnienie kolumny S. No. Jeśli jednak sformatujesz wartości S. No., arkusz będzie wyglądał lepiej. Ponadto pokazuje, dla ilu wierszy arkusz jest sformatowany.

Wpisz = row () - 5 w komórce B6 i naciśnij Enter.

1 pojawi się w komórce B6. Skopiuj formułę z komórek B6-B15. Pojawiają się wartości 1-10.

Zacieniuj komórki B6-B15.

Zakończyć

Projekt jest prawie gotowy.

Ukryj kolumnę M, która zawiera wartości kategorii danych.
Formatuj obramowanie komórek B6-K16.

Kliknij prawym przyciskiem myszy kartę arkusza.
Wybierz z menu opcję Chroń arkusz.

Pojawi się okno dialogowe Chroń arkusz.

Zaznacz opcję Chroń arkusz i zawartość zablokowanych komórek.
Wpisz hasło w polu Hasło, aby wyłączyć ochronę arkusza -
- W haśle rozróżniana jest wielkość liter
- Chronionego arkusza nie można odzyskać, jeśli zapomnisz hasła
- Dobrą praktyką jest przechowywanie gdzieś listy nazw arkuszy i haseł
W obszarze Zezwalaj wszystkim użytkownikom tego arkusza na: zaznacz pole wyboru Wybierz odblokowane komórki.