Phân tích dữ liệu Excel - Xác thực dữ liệu

Xác thực dữ liệu là một công cụ rất hữu ích và dễ sử dụng trong Excel mà bạn có thể thiết lập xác thực dữ liệu trên dữ liệu được nhập được nhập vào Trang tính của bạn.

Đối với bất kỳ ô nào trên trang tính, bạn có thể

Hiển thị thông báo đầu vào về những gì cần nhập vào đó.
Hạn chế các giá trị được nhập.
Cung cấp danh sách các giá trị để lựa chọn.
Hiển thị thông báo lỗi và từ chối mục nhập dữ liệu không hợp lệ.

Hãy xem xét Trình theo dõi rủi ro sau đây có thể được sử dụng để nhập và theo dõi thông tin về Rủi ro đã xác định.

Trong trình theo dõi này, dữ liệu được nhập vào các cột sau được xác thực với các ràng buộc dữ liệu đặt trước và dữ liệu đã nhập chỉ được chấp nhận khi đáp ứng các tiêu chí xác nhận. Nếu không, bạn sẽ nhận được thông báo lỗi.

Probability
Impact
Loại rủi ro
Nguồn rủi ro
Status

Cột Rủi ro Phơi nhiễm sẽ có các giá trị được tính toán và bạn không thể nhập bất kỳ dữ liệu nào. Ngay cả cộtS. No. được đặt để có các giá trị tính toán được điều chỉnh ngay cả khi bạn xóa một hàng.

Bây giờ, bạn sẽ học cách thiết lập một trang tính như vậy.

Chuẩn bị cấu trúc cho bảng tính

Để chuẩn bị cấu trúc cho trang tính -

Bắt đầu với một trang tính trống.
Đặt tiêu đề vào Hàng 2.
Đặt tiêu đề cột vào Hàng 3.
Đối với các tiêu đề cột Xác suất, Tác động và Rủi ro -
- Nhấp chuột phải vào ô.
- Nhấp vào Định dạng ô từ trình đơn thả xuống.
- Trong hộp thoại Định dạng Ô, bấm vào Căn chỉnh tab.
- Nhập 90 dưới Định hướng.
Hợp nhất và Căn giữa các ô trong Hàng 3, 4 và 5 cho mỗi tiêu đề cột.
Định dạng Đường viền cho các ô trong Hàng 2 - 5.
Điều chỉnh độ rộng của hàng và cột.

Trang tính của bạn sẽ trông như sau:

Đặt giá trị hợp lệ cho danh mục rủi ro

Trong các ô M5 - M13 nhập các giá trị sau (M5 là tiêu đề và M6 - M13 là các giá trị)

Giá trị danh mục
Người dùng cuối
khách hàng
Sự quản lý
Lên lịch
Lên lịch
Môi trường
Sản phẩm
Dự án

Bấm vào ô đầu tiên trong cột Phân loại rủi ro (H6).
Nhấp vào tab DATA trên Ribbon.
Bấm Xác thực Dữ liệu trong nhóm Công cụ Dữ liệu.
Chọn Xác thực dữ liệu… từ danh sách thả xuống.

Hộp thoại Xác thực Dữ liệu xuất hiện.

Nhấp vào tab Cài đặt.
Dưới tiêu chí Xác thực, trong Allow: danh sách thả xuống, Chọn tùy chọn List.

Chọn phạm vi M6: M13 trong hộp Nguồn: xuất hiện.
Chọn các hộp Bỏ qua trống và thả xuống Trong ô xuất hiện.

Đặt thông báo đầu vào cho danh mục rủi ro

Bấm vào tab Thông báo đầu vào trong hộp thoại Xác thực Dữ liệu.
Kiểm tra hộp Show input message khi ô được chọn.
Trong hộp bên dưới Tiêu đề :, nhập Loại rủi ro:
Trong hộp bên dưới Thông báo đầu vào: Chọn Danh mục Rủi ro từ Danh sách.

Đặt cảnh báo lỗi cho danh mục rủi ro

Để đặt cảnh báo lỗi -

Nhấp vào tab Cảnh báo Lỗi trong hộp thoại Xác thực dữ liệu.
Chọn hộp Hiển thị cảnh báo lỗi sau khi nhập dữ liệu không hợp lệ.
Chọn Dừng dưới Kiểu: thả xuống
Trong hộp bên dưới Tiêu đề :, nhập Mục nhập không hợp lệ:
Trong hộp bên dưới Thông báo lỗi: nhập Chọn giá trị từ danh sách thả xuống.
Bấm OK.

Xác minh xác thực dữ liệu cho loại rủi ro

Đối với ô đầu tiên đã chọn trong Danh mục rủi ro,

Tiêu chí xác thực dữ liệu được đặt
Thông báo đầu vào được thiết lập
Đã đặt cảnh báo lỗi

Bây giờ, bạn có thể xác minh cài đặt của mình.

Bấm vào ô mà bạn đã đặt tiêu chí Xác thực Dữ liệu. Thông báo Đầu vào xuất hiện. Nút thả xuống xuất hiện ở phía bên phải của ô.

Thông báo đầu vào được hiển thị chính xác.

Nhấp vào nút thả xuống ở phía bên phải của ô. Danh sách thả xuống xuất hiện với các giá trị có thể được chọn.
Kiểm tra chéo các giá trị trong danh sách thả xuống với những giá trị được sử dụng để tạo danh sách thả xuống.

Cả hai bộ giá trị đều khớp. Lưu ý rằng nếu số lượng giá trị nhiều hơn, bạn sẽ nhận được thanh cuộn xuống ở phía bên phải của danh sách thả xuống.

Chọn một giá trị từ danh sách thả xuống. Nó xuất hiện trong ô.

Bạn có thể thấy rằng việc lựa chọn các giá trị hợp lệ đang hoạt động tốt.

Cuối cùng, hãy thử nhập một mục không hợp lệ và xác minh cảnh báo Lỗi.

Nhập Mọi người vào ô và nhấn Enter. Thông báo lỗi mà bạn đã đặt cho ô sẽ được hiển thị.

Xác minh thông báo Lỗi.
Bạn có một tùy chọn để Thử lại hoặc Hủy. Xác minh cả hai tùy chọn.

Bạn đã đặt thành công Xác thực Dữ liệu cho ô.

Note - Việc kiểm tra chính tả và ngữ pháp của tin nhắn là rất quan trọng.

Đặt tiêu chí hợp lệ cho cột loại rủi ro

Bây giờ, bạn đã sẵn sàng áp dụng tiêu chí Xác thực Dữ liệu cho tất cả các ô trong cột Danh mục Rủi ro.

Tại thời điểm này, bạn cần nhớ hai điều:

Bạn cần đặt tiêu chí cho số lượng ô tối đa có thể được sử dụng. Trong ví dụ của chúng tôi, nó có thể thay đổi từ 10 - 100 tùy theo nơi trang tính sẽ được sử dụng.
Bạn không nên đặt tiêu chí cho phạm vi ô không mong muốn hoặc cho toàn bộ cột. Điều này sẽ làm tăng kích thước tệp một cách không cần thiết. Nó được gọi là định dạng dư thừa. Nếu bạn nhận được trang tính từ một nguồn bên ngoài, bạn phải loại bỏ định dạng thừa, bạn sẽ tìm hiểu trong chương Yêu cầu trong hướng dẫn này.

Làm theo các bước dưới đây -

Đặt tiêu chí xác nhận cho 10 ô trong Danh mục rủi ro.
Bạn có thể dễ dàng thực hiện việc này bằng cách nhấp vào góc dưới cùng bên phải của ô đầu tiên.
Giữ biểu tượng + xuất hiện và kéo nó xuống.

Xác thực Dữ liệu được đặt cho tất cả các ô đã chọn.

Nhấp vào cột cuối cùng được chọn và xác minh.

Xác thực dữ liệu cho cột Danh mục rủi ro đã hoàn tất.

Đặt giá trị xác thực cho nguồn rủi ro

Trong trường hợp này, chúng ta chỉ có hai giá trị - Bên trong và Bên ngoài.

Nhấp vào ô đầu tiên trong cột Nguồn rủi ro (I6)
Bấm vào tab DATA trên Ribbon
Nhấp vào Xác thực dữ liệu trong nhóm Công cụ dữ liệu
Chọn Xác thực dữ liệu… từ danh sách thả xuống.

Hộp thoại Xác thực Dữ liệu xuất hiện.

Nhấp vào tab Cài đặt.
Dưới tiêu chí Xác thực, trong danh sách thả xuống Cho phép:, hãy chọn Danh sách tùy chọn.
Nhập Nội bộ, Bên ngoài vào hộp Nguồn: xuất hiện.
Chọn các hộp Bỏ qua trống và thả xuống Trong ô xuất hiện.

Đặt thông báo đầu vào cho nguồn rủi ro.

Đặt cảnh báo lỗi cho nguồn rủi ro.

Đối với ô đầu tiên đã chọn trong Nguồn rủi ro -

Tiêu chí xác thực dữ liệu được đặt
Thông báo đầu vào được thiết lập
Đã đặt cảnh báo lỗi

Bây giờ, bạn có thể xác minh cài đặt của mình.

Bấm vào ô mà bạn đã đặt tiêu chí Xác thực Dữ liệu. Thông báo nhập xuất hiện. Nút thả xuống xuất hiện ở phía bên phải của ô.

Thông báo đầu vào được hiển thị chính xác.

Nhấp vào nút mũi tên thả xuống ở phía bên phải của ô. Một danh sách thả xuống xuất hiện với các giá trị có thể được chọn.
Kiểm tra xem các giá trị có giống như bạn đã nhập - Bên trong và Bên ngoài.

Cả hai bộ giá trị đều khớp. Chọn một giá trị từ danh sách thả xuống. Nó xuất hiện trong ô.

Bạn có thể thấy rằng việc lựa chọn các giá trị hợp lệ đang hoạt động tốt. Cuối cùng, hãy thử nhập một mục không hợp lệ và xác minh cảnh báo Lỗi.

Nhập Tài chính vào ô và nhấn Enter. Thông báo lỗi mà bạn đã đặt cho ô sẽ được hiển thị.

Xác minh thông báo Lỗi. Bạn đã đặt thành công Xác thực Dữ liệu cho ô.
Đặt tiêu chí hợp lệ cho Cột Nguồn Rủi ro
Áp dụng tiêu chí Xác thực Dữ liệu cho các ô I6 - I15 trong cột Nguồn Rủi ro (tức là cùng phạm vi với cột Danh mục Rủi ro).

Xác thực Dữ liệu được đặt cho tất cả các ô đã chọn. Xác thực dữ liệu cho cột Nguồn rủi ro đã hoàn tất.

Đặt giá trị xác thực cho trạng thái

Lặp lại các bước tương tự mà bạn đã sử dụng để đặt giá trị Xác thực cho Nguồn rủi ro.
Đặt các giá trị Danh sách là Mở, Đóng.
Áp dụng tiêu chí Xác thực Dữ liệu cho các ô K6 - K15 trong cột Trạng thái (tức là cùng phạm vi với cột Danh mục rủi ro).

Xác thực Dữ liệu được đặt cho tất cả các ô đã chọn. Xác thực dữ liệu cho trạng thái cột đã hoàn tất.

Đặt giá trị xác thực cho xác suất

Giá trị Điểm xác suất rủi ro nằm trong khoảng 1-5, 1 là thấp và 5 là cao. Giá trị có thể là bất kỳ số nguyên nào từ 1 đến 5, bao gồm cả hai.

Bấm vào ô đầu tiên trong cột Nguồn rủi ro (I6).
Nhấp vào tab DATA trên Ribbon.
Bấm Xác thực Dữ liệu trong nhóm Công cụ Dữ liệu.
Chọn Xác thực dữ liệu… từ danh sách thả xuống.

Hộp thoại Xác thực Dữ liệu xuất hiện.

Nhấp vào tab Cài đặt.
Trong Tiêu chí xác thực, trong danh sách thả xuống Cho phép:, hãy chọn Số nguyên.

Chọn giữa trong Dữ liệu:
Nhập 1 vào hộp bên dưới Tối thiểu:
Nhập 5 vào hộp bên dưới Tối đa:

Đặt thông báo đầu vào cho xác suất

Đặt Thông báo Lỗi cho Xác suất và bấm OK.

Đối với ô đầu tiên đã chọn trong Xác suất,

Tiêu chí xác thực dữ liệu được đặt.
Thông báo đầu vào được thiết lập.
Đã đặt cảnh báo lỗi.

Bây giờ, bạn có thể xác minh cài đặt của mình.

Bấm vào ô mà bạn đã đặt tiêu chí Xác thực Dữ liệu. Thông báo nhập xuất hiện. Trong trường hợp này, sẽ không có nút thả xuống vì các giá trị đầu vào được đặt trong một dải ô chứ không phải từ danh sách.

Thông báo đầu vào được hiển thị chính xác.

Nhập một số nguyên từ 1 đến 5 vào ô. Nó xuất hiện trong ô.

Việc lựa chọn các giá trị hợp lệ đang hoạt động tốt. Cuối cùng, hãy thử nhập một mục không hợp lệ và xác minh cảnh báo Lỗi.

Nhập 6 vào ô và nhấn Enter. Thông báo Lỗi mà bạn đã đặt cho ô sẽ được hiển thị.

Bạn đã đặt thành công Xác thực Dữ liệu cho ô.

Đặt tiêu chí hợp lệ cho Cột xác suất.
Áp dụng tiêu chí Xác thực Dữ liệu cho các ô E6 - E15 trong cột Xác suất (tức là cùng phạm vi với cột Danh mục Rủi ro).

Xác thực Dữ liệu được đặt cho tất cả các ô đã chọn. Xác thực dữ liệu cho cột Xác suất đã hoàn tất.

Đặt giá trị xác thực cho tác động

Để đặt giá trị xác thực cho Impact, hãy lặp lại các bước tương tự mà bạn đã sử dụng để đặt giá trị xác thực cho xác suất.

Áp dụng tiêu chí Xác thực Dữ liệu cho các ô F6 - F15 trong cột Tác động (tức là cùng phạm vi với cột Danh mục rủi ro).

Xác thực Dữ liệu được đặt cho tất cả các ô đã chọn. Xác thực dữ liệu cho cột Impact đã hoàn tất.

Đặt mức độ rủi ro của cột với các giá trị được tính toán

Phơi nhiễm rủi ro được tính toán như một sản phẩm của Xác suất rủi ro và Tác động của rủi ro.

Mức độ rủi ro = Khả năng xảy ra * Tác động

Gõ = E6 * F6 vào ô G6 và nhấn Enter.

0 sẽ được hiển thị trong ô G6 vì E6 và F6 trống.

Sao chép công thức trong các ô G6 - G15. 0 sẽ được hiển thị trong các ô G6 - G15.

Vì cột Phơi nhiễm Rủi ro dành cho các giá trị được tính toán, bạn không nên cho phép nhập dữ liệu vào cột đó.

Chọn ô G6-G15
Nhấp chuột phải và trong danh sách thả xuống xuất hiện, hãy chọn Định dạng ô. Hộp thoại Định dạng Ô xuất hiện.
Nhấp vào tab Bảo vệ.
Kiểm tra tùy chọn Locked.

Điều này là để đảm bảo rằng không được phép nhập dữ liệu trong các ô đó. Tuy nhiên, điều này sẽ chỉ có hiệu lực khi trang tính được bảo vệ, bạn sẽ thực hiện bước cuối cùng sau khi trang tính sẵn sàng.

Bấm OK.
Tô bóng các ô G6-G15 để cho biết chúng là các giá trị được tính toán.

Định dạng giá trị số sê-ri

Bạn có thể để người dùng điền vào Cột S. Không. Tuy nhiên, nếu bạn định dạng các giá trị S. Không, trang tính sẽ hiển thị hơn. Ngoài ra, nó còn hiển thị số hàng mà trang tính được định dạng.

Nhập = row () - 5 vào ô B6 và nhấn Enter.

1 sẽ xuất hiện trong ô B6. Sao chép công thức trong các ô B6-B15. Giá trị 1-10 xuất hiện.

Che bóng các ô B6-B15.

Gói (lại

Bạn gần như đã hoàn thành dự án của mình.

Ẩn Cột M có chứa các giá trị Hạng mục Dữ liệu.
Định dạng Đường viền cho các ô B6-K16.

Bấm chuột phải vào tab trang tính.
Chọn Bảo vệ Trang tính từ menu.

Hộp thoại Bảo vệ Trang tính xuất hiện.

Chọn tùy chọn Bảo vệ trang tính và nội dung của các ô bị khóa.
Nhập mật khẩu trong Mật khẩu để bỏ bảo vệ trang tính -
- Mật khẩu phân biệt chữ hoa chữ thường
- Không thể khôi phục trang tính được bảo vệ nếu quên mật khẩu
- Một phương pháp hay là giữ một danh sách tên trang tính và mật khẩu ở đâu đó
Trong Cho phép tất cả người dùng trang tính này: chọn hộp Chọn các ô đã mở khóa.

Bạn đã bảo vệ các ô bị khóa trong cột Rủi ro Phơi nhiễm khỏi mục nhập dữ liệu và giữ cho phần còn lại của các ô đã mở khóa có thể chỉnh sửa được. Bấm OK.

Các Confirm Password hộp thoại xuất hiện.

Nhập lại mật khẩu.
Bấm OK.

Trang tính của bạn với bộ Xác thực Dữ liệu cho các ô đã chọn đã sẵn sàng để sử dụng.