Chèn hộp kiểm/ checkbox vào Excel

Bài viết này sẽ hướng dẫn bạn cách tạo hộp đánh dấu trong Excel rồi sử dụng kết quả hộp đánh dấu vào công thức để tạo bảng liệt kê những mục cần kiểm tra, bảng liệt kê những việc cần làm, bảng báo cáo hay biểu đồ mang tính tương tác.

Tôi tin rằng ai cũng biết hộp đánh dấu là gì, chắc chắn bạn đã nhìn thấy rất nhiều hộp đánh dấu với các hình dạng khác nhau trên mạng. Tuy vậy, để rõ ràng, hãy để tôi đưa cho bạn một định nghĩa ngắn gọn.

Hộp đánh dấu, hay còn gọi là hộp lựa chọn, là một hộp hình vuông nhỏ nơi bạn có thể chọn hay bỏ chọn các lựa chọn có sẵn.

Chèn hộp đánh dấu vào Excel nghe có vẻ bình thường, nhưng nó tạo ra rất nhiều khả năng cho trang tính của bạn, việc này sẽ giúp bạn luôn hướng tới mục tiêu, làm việc theo thời khóa biểu, hoàn thành bài tập,…

CÁCH CHÈN HỘP ĐÁNH DẤU VÀO EXCEL

Giống như các quyền kiểm soát hình thức khác, quyền kiểm soát Hộp đánh dấu nằm trên tab Người phát triển, tab này không có trên thanh ribbon Excel theo mặc định. Vì thế, bạn cần phải bật nó lên trước đã.

HIỂN THỊ TAB DEVELOPER TRÊN RIBBON

Để thêm tab Developer vào ribbon trên Excel, hãy thực hiện các bước sau:

Nhấp chuột phải vào bất cứ đâu trên ribbon, rồi nhấp Customize the Ribbon…hay nhấp File>Options>Customize Ribbon.

Bên dưới Customize the Ribbon, hãy chọn Main Tabs (thông thường thì nó được chọn một cách mặc định), hãy kiểm tra hộp Developer, rồi nhấp OK.

Bây giờ, với tab Developer trên thanh ribbon, bạn có quyền truy cập vô số các quyền kiểm soát tương tác, kể cả Hộp Đánh dấu.

Sắp xếp dữ liệu

Nếu bạn đang tạo bảng liệt kê những mục cần kiểm tra hay bảng liệt kê những việc cần làm, thì bước đầu tiên là lập danh sách các nhiệm vụ hay các mục khác – các mục được chèn vào hộp đánh dấu.

Đối với ví dụ này, tôi đã tạo ra bảng liệt kê những kế hoạch cần làm cho bữa tiệc:

THÊM HỘP ĐÁNH DẤU

Các bước chuẩn bị đã xong, và bây giờ chúng ta đi vào phần chính thôi – thêm hộp đánh dấu cho danh sách những việc cần làm cho bữa tiệc.

Để chèn hộp đánh dấu vào Excel, hãy thực hiện các bước sau:

Trên tab Developer, trong nhóm Controls, nhấp Insert, rồi chọn hộp đánh dấu bên dưới Form Controls.

Nhấp vào ô mà bạn muốn chèn hộp đánh dấu đầu tiên (trong ví dụ này là ô B2). Quyền kiểm soát hộp đánh dấu sẽ xuất hiện gần đó, không cần phải chính xác là ở trong ô đó:

Để đặt hộp đánh dấu một cách thích hợp, hãy di chuyển chuột sao cho chữ hộp đánh dấu được đóng khung và ngay khi con trỏ đổi thành 4 mũi tên, thì hãy kéo hộp đánh dấu đến vị trí mong muốn.

Để xóa chữ ‘Hộp đánh dấu 1’, nhấp chuột phải vào hộp đánh dấu, chọn ký tự rồi chọn xóa. Hay, hãy nhấp chuột phải vào hộp đánh dấu, chọn Chỉnh sửa Ký tự trong bảng chọn ký tự, rồi xóa ký tự.

Hộp đánh dấu đầu tiên trong Excel đã sẵn sàng, bạn chỉ cần sao chép nó vào các ô khác.

SAO CHÉP HỘP ĐÁNH DẤU VÀO CÁC Ô KHÁC

Chọn ô có hộp đánh dấu bằng cách sử dụng các phím mũi tên trên bàn phím rồi đặt con trỏ ở góc phải bên dưới ô. Khi con trỏ thay đổi thành dấu cộng màu đen đậm, hãy kéo nó đến ô cuối cùng nơi mà bạn muốn sao chép hộp đánh dấu.

Xong rồi! Hộp đánh dấu đã có đầy đủ danh sách các mục cần kiểm tra:

Như bạn có thể thấy trong ảnh chụp màn hình trên, danh sách các mục cần kiểm tra trong Excel gần như sắp xong rồi. Tại sao lại là gần như? Mặc dù các hộp đánh dấu đã được chèn và bạn có thể chọn hay bỏ chọn chúng, nhưng Microsoft Excel không có khả năng nhận biết các thay đổi này bởi vì các ô vẫn chưa được liên kết với các hộp đánh dấu.

Phần tiếp theo trong bài hướng dẫn hộp đánh dấu trong Excel sẽ chỉ bạn cách lưu giữ người dùng – người đã chọn hay bỏ chọn một hộp đánh dấu và cách dùng thông tin đó trong công thức của bạn.

VÍ DỤ VỀ VIỆC SỬ DỤNG HỘP ĐÁNH DẤU TRONG EXCEL

Bên dưới, bạn sẽ tìm thấy vài ví dụ về cách sử dụng hộp đánh dấu trong Excel để tạo danh sách các mục cần kiểm tra, danh sách các việc cần làm, bảng báo cáo hay biểu đồ mang tính tương tác. Nhưng trước hết, hãy học cách liên kết các hộp đánh dấu với các ô. Kỹ thuật rất đơn giản, nhưng nó lại là nền tảng của việc sử dụng kết quả từ hộp đánh dấu trong công thức của bạn.

CÁCH LIÊN KẾT HỘP ĐÁNH DẤU VỚI Ô

Như đã đề cập từ trước, để có khả năng lưu giữ tình trạng hộp đánh dấu (được chọn hay bỏ chọn) bạn cần phải liên kết hộp đánh dấu với một ô nhất định. Để làm điều này, hãy làm theo các bước sau:

Nhấp chuột phải vào ô đánh dấu, rồi nhấp Format Control.

Trong hộp hội thoại Format Control, hãy đổi thành Control tab, nhấp vào hộp Cell link rồi chọn một ô trống trên trang tính – ô mà bạn muốn liên kết với hộp đánh dấu, hay nhập tham chiếu ô một cách thủ công:

Lặp lại các bước trên đối với các hộp đánh dấu khác.

Mẹo. Để xác định các ô được liên kết một cách dễ dàng, hãy chọn chúng ở cột kế bên – cột không chứa dữ liệu nào. Bằng cách này, bạn có thể ẩn các ô được liên kết một cách an toàn để chúng không cản trở trang tính của bạn.

Cuối cùng, hãy nhấp vào mỗi hộp đánh dấu đã được liên kết. Trong các ô được liên kết, TRUE xuất hiện khi các hộp đánh dấu được chọn, và FALSE xuất hiện khi các hộp đánh dấu còn trống:

Ở thời điểm này, các ô được liên kết có lẽ chẳng còn tác dụng gì nữa, nhưng hãy kiên nhẫn thêm chút nữa nhé và bạn sẽ thấy nó sẽ tạo cho bạn rất nhiều cơ hội.

CÁCH TẠO DANH SÁCH NHỮNG VIỆC CẦN KIỂM TRA TRONG EXCEL VỚI BẢNG TÓM TẮT DỮ LIỆU

Thật ra, chúng ta đã làm xong phần chính của nhiệm vụ bằng cách thêm các hộp đánh dấu và liên kết chúng với các ô. Bây giờ, chúng ta chỉ lập vài công thức để tạo bảng tóm tắt dữ liệu cho danh sách các việc cần kiểm tra trong Excel.

CÔNG THỨC TÍNH TỔNG NHIỆM VỤ

Đây là cái dễ nhất – hãy sử dụng hàm COUNTA để đếm số ô trống trong danh sách các việc cần làm:

=COUNTA(A2:A12)

Trong đó, A2:A12 là các mục trong danh sách các việc cần kiểm tra.

CÔNG THỨC TÍNH SỐ NHIỆM VỤ ĐÃ HOÀN THÀNH

Một nhiệm vụ đã hoàn thành có nghĩa là hộp đánh dấu được chọn, có nghĩa là giá trị TRUE trong ô liên kết. Vì thế, hãy đếm tất cả giá trị TRUE bằng cách sử dụng công thức COUNTIF này:

=COUNTIF(C2:C12,TRUE)

Trong đó, C2:C12 là các ô được liên kết.

Để khiến công thức trông thử thách hơn một chút, bạn sử dụng hàm COUNTIFS thay vì hàm COUNTIF để kiểm tra ô trống trong danh sách (cột A):

=COUNTIFS(A2:A12, “<>”, C2:C12, TRUE)

Trong trường hợp này, nếu bạn xóa các mục có liên quan ra khỏi danh sách kiểm tra của Excel, nhưng lại không bỏ chọn hộp tương ứng, những hộp được chọn đó sẽ không được tính.

CÔNG THỨC TÍNH PHẦN TRĂM CÁC NHIỆM VỤ ĐÃ HOÀN THÀNH

Để tính phần trăm các nhiệm vụ đã hoàn thành, hãy sử dụng công thức phần trăm thông dụng này:

Một phần/Tổng = Phần trăm

Trong trường hợp của chúng ta, hãy chia số nhiệm vụ đã hoàn thành cho tổng số nhiệm vụ, giống như thế này:

=COUNTIF(C2:C12,TRUE)/COUNTA(A2:A12)

Ảnh chụp màn hình dưới đây cho thấy tất cả các công thức trên khi được sử dụng sẽ hoạt động  như thế nào:

Như bạn có thể thấy từ ảnh chụp màn hình trên, chúng ta đã chèn thêm một công thức vào ô B18. Công thức này được dựa trên hàm IF – công thức trả về “Yes” nếu số nhiệm vụ hoàn thành bằng tổng số nhiệm vụ, nếu không thì sẽ trả về “No”:

=IF(B14=B15, “Yep!”, “Nope :(“)

Để thêm thắt cho danh sách các việc cần kiểm tra, bạn có thể tạo ra một vài quy luật định dạng có điều kiện – những quy luật này sẽ thay đổi màu của ô B18 tùy vào giá trị của nó.

Khi xong rồi, hãy ẩn cột này cùng với các ô liên kết, và danh sách các việc cần kiểm tra trong Excel đã hoàn tất rồi đấy!

THÊM NHIỀU MẪU DANH SÁCH NHỮNG VIỆC CẦN HOÀN THÀNH

Để nhanh chóng chọn các mẫu danh sách trong Excel, hãy chọn File>New, gõ “danh sách cần kiểm tra” vào ô tìm kiếm, rồi nhấp Enter.

Nếu không có mẫu nào trong Excel đáp ứng nhu cầu của bạn, thì có lẽ bạn sẽ tìm thấy cái bạn muốn trong các nguồn hữu ích bên dưới:

CÁCH TẠO DANH SÁCH NHỮNG VIỆC CẦN LÀM CÙNG VỚI ĐỊNH DẠNG CÓ ĐIỀU KIỆN

Về cơ bản, bạn có thể thêm hộp đánh dấu và công thức cho danh sách những việc cần làm theo cách mà bạn đã thực hiện với danh sách cần kiểm tra. Bạn có thể hỏi tôi, “Vậy viết phần này để làm gì?” À thì, trong một danh sách những việc cần làm thông thường, những nhiệm vụ đã hoàn thành sẽ được gạch ngang như thế này:

Bạn có thể có được hiệu ứng này một cách dễ dàng bằng cách tạo quy luật định dạng có điều kiện. Dưới đây là các bước chi tiết.

Để bắt đầu, hãy viết danh sách các nhiệm vụ, chèn hộp đánh dấu rồi liên kết chúng với các ô:

Và bây giờ, hãy áp dụng định dạng có điều kiện – cái mà sẽ cho bạn định dạng gạch ngang và, tùy ý, màu hình nền hay phông chữ khác đối với các mục được chọn.

Chọn danh sách các nhiệm vụ (trong trường hợp này là A2:A11).

Đi đến tab Home>nhóm Styles, rồi chọn Conditional Formatting>New Rule…

Trong hộp hội thoại New Formatting Rule, hãy chọn Use a formula to determine which cells to format.

Trong hộp Format values where this formula is true, hãy nhập công thức sau:

=$C2=TRUE

Trong đó, C2 là ô được liên kết trên cùng.

Nhấn nút Format, cài đặt phong cách định dạng mong muốn, rồi nhấp OK. Trong ví dụ này, chúng ta chọn hiệu ứng Strikethrough và màu phông chữ là xám nhạt:

Bây giờ, khi một ô nhất định được chọn, mục tương ứng được định dạng thành màu xám nhạt và được gạch ngang.

Và sau đây, thêm một ý tưởng mới về việc định dạng danh sách cần kiểm tra trong Excel. Thay vì gạch ngang các nhiệm vụ đã hoàn thành, bạn có thể chèn thêm một cột cùng với hàm IF:

=IF(E2=TRUE, “Đã xong”, “Chưa xong”)

Trong đó, E2 là ô liên kết nằm trên cùng.

Như ảnh chụp màn hình dưới đây, công thức sẽ trả về kết quả “Đã xong” nếu ô liên kết chứa giá trị TRUE, kết quả “Chưa xong” nếu chứa giá trị FALSE:

Sau đó, hãy sử dụng định dạng có điều kiện mong muốn cho cột Tình trạng dựa trên công thức này:

=$C2=”Đã xong”

Kết quả sẽ trông như thếnày:

Cuối cùng, hãy thêm vài công thức tính các nhiệm vụ đã hoàn thành (giống như những gì ta đã thực hiện đối với danh sách cần kiểm tra), hãy ẩn các ô liên kết, và danh sách những việc cần làm trong Excel của bạn đã sẵn sàng!

Biểu đồ cột nằm trên cùng danh sách những việc cần làm được dựa trên công thức phần trăm trong ô B2.

Hướng dẫn học Excel 2010

CÁCH DÙNG HỘP ĐÁNH DẤU TẠO BẢNG BÁO CÁO MANG TÍNH TƯƠNG TÁC

Công dụng hữu ích khác của hộp đánh dấu trong Excel đó là tạo bảng báo cáo mang tính tương tác.

Giả sử bạn có bảng báo cáo doanh thu của 4 vùng: Bắc, Nam, Đông và Tây. Mục đích của bạn là tính tổng của một hay nhiều vùng được chọn. Tất nhiên, việc này có thể hoàn thành bằng cách sử dụng tính năng Slicer của bảng Excel hay PivotTable, hay bằng cách thêm tổng của từng vùng. Nhưng tại sao chúng ta lại không tạo bảng báo cáo mang tính tương tác bằng cách chèn 4 hộp đánh dấu lên trên cùng?

Trông đẹp mắt, đúng không nào? Để tạo ra bảng báo cáo tương tự trên trang tính, hãy làm theo các bước sau:

Thêm 4 hộp đánh dấu vào đầu trang tính, cho các khu vực miền Bắc, Nam, Đông và Tây.

Tạo vùng điều kiện trên bất kỳ phần nào chưa được sử dụng của trang tính, rồi liên kết hộp đánh dấu với các ô trống:

Trong ảnh chụp màn hình trên, I2:I5 là các ô được liên kết và H2:H5 chính là tên các vùng cũng giống như tên của chúng trong bảng báo cáo.

Thêm một cột nữa cho vùng điều kiện có công thức IF – công thức này sẽ trả về tên vùng nếu ô liên kết có giá trị TRUE, nếu không thì là dấu gạch ngang (“-“):

=IF(I2=TRUE, H2, “-“)

Gõ tiêu đề cho cột công thức – tiêu đề phải khớp với tiêu đề của cột tương ứng trong bảng báo cáo (trong ví dụ này là cột Khu vực). Sự trùng khớp rất quan trọng và ở bước tiếp theo, bạn sẽ hiểu lý do.

Tiếp theo, hãy lập công thức tính tổng các vùng được chọn. Về việc này, chúng ta sẽ sử dụng hàm DSUM để tính tổng các giá trị trong cơ sở dữ liệu – các giá trị đáp ứng điều kiện xác định:

DSUM(database, field, criteria)

Trong đó:

Database là bảng hay dải ô của bạn bao gồm tiêu đề của cột (trong ví dụ này là A5:F48).

Field là cột mà bạn muốn tính tổng. Bạn có thể nhập nó hoặc dưới dạng tiêu đề cột đóng khung trong dấu ngoặc kép, hoặc một con số nói lên vị trí của cột trong cơ sở dữ liệu. Trong ví dụ này, chúng ta tính tổng trong cột Tổng từng khu vực, nên câu lệnh thứ hai của chúng ta là “Tổng từng khu vực”.

Criteria là dải ô chứa điều kiện, bao gồm tiêu đề cột (J1:J5). Đây là lý do tại sao tiêu đề của cột công thức trong vùng điều kiện nên khớp với tiêu đề cột trong bảng báo cáo.

Kết hợp các câu lệnh trên, và công thức DSUM của bạn có dạng như sau:

=DSUM(A5:F48, “tổng từng vùng”, J1:J5)

và chạy một cách hoàn hảo!

Nếu bên cạnh việc tính tổng, bảng báo cáo của bạn còn tính giá trị trung bình mỗi hàng, thì bạn có thể sử dụng hàm DAVERAGE (database, field, criteria) để tính doanh thu trung bình của các vùng được chọn.

Cuối cùng, hãy ẩn, có lẽ là khóa vùng điều kiện để tránh các thay đổi ngẫu nhiên, và bảng báo cáo mang tính tương tác của bạn đã xong rồi đấy!

TẠO BIỂU ĐỒ SINH ĐỘNG DỰA TRÊN TÌNH TRẠNG HỘP ĐÁNH DẤU

Ví dụ này sẽ chỉ bạn cách tạo biểu đồ sinh động trong Excel – biểu đồ sẽ thay đổi theo tình trạng của hộp đánh dấu (chọn hay bỏ chọn):

Dữ liệu nguồn cho ví dụ này đơn giản như sau:

Để biến nó thành một biểu đồ Excel sinh động, hãy thực hiện các bước sau:

Tạo hộp đánh dấu và liên kết chúng với các ô trống.

Cụ thể, hãy chèn 2 hộp đánh dấu cho năm 2013 và năm 2014, rồi lần lượt liên kết chúng với các ô G2 và G3:

Tạo chuỗi dữ liệu cho biểu đồ dựa trên dữ liệu nguồn và các ô được liên kết (hãy nhìn hình ảnh bên dưới):

Đối với năm 2013 (J4:J7), hãy sử dụng công thức sau:

=IF($G$2=TRUE, B4, NA())

Nếu hộp đánh dấu 2013 được chọn (ô G2 có giá trị TRUE), thì công thức sẽ lấy giá trị gốc từ ô B4, nếu không thì công thức sẽ trả về lỗi #N/A.

Đối với năm 2014 (K4:K7), hãy nhập công thức tương tự để lấy các giá trị từ cột C nếu hộp đánh dấu 2014 được chọn:

=IF($G$2=TRUE, C4, NA())

Ở ô L4, hãy nhập công thức =$D4, rồi sao chép vào ô L7. Bởi vì dữ liệu cho năm 2015 nên luôn được hiển thị trong biểu đồ,và công thức IF không cần thiết cho cột này.

Hãy tạo biểu đồ kết hợp dựa trên chuỗi dữ liệu phụ thuộc (I3:L7). Vì chúng ta đã liên kết tất cả các ô trong bảng phụ thuộc với dữ liệu nguồn, nên biểu đồ sẽ tự động cập nhật ngay khi chuỗi dữ liệu gốc có bất kỳ sự thay đổi nào.

Đây là cách bạn tạo và sử dụng hộp đánh dấu trong Excel. Cảm ơn các bạn vì đã đọc bài viết này. Hẹn gặp lại các bạn trên blog này vào tuần sau.

Đừng vội bỏ qua: Khóa học Excel kế toán ở Hà Nội