Bảng tính Excel thường bao gồm các ô thả xuống để đơn giản hóa và/hoặc tiêu chuẩn hóa việc nhập dữ liệu. Các danh sách thả xuống này được tạo bằng tính năng xác thực dữ liệu để chỉ định danh sách các mục nhập được phép.
Để thiết lập danh sách thả xuống đơn giản, hãy chọn ô nơi dữ liệu sẽ được nhập, sau đó nhấp vào Xác nhận dữ liệu (trên Dữ liệu tab), chọn Xác thực dữ liệu, chọn Danh sách (trong Cho phép:), rồi nhập các mục danh sách (cách nhau bằng dấu phẩy) vào hộp Nguồn: trường (xem Hình 1).
Trong kiểu thả xuống cơ bản này, danh sách các mục được phép được chỉ định trong chính quá trình xác thực dữ liệu; do đó, để thực hiện thay đổi trong danh sách, người dùng phải mở và chỉnh sửa xác thực dữ liệu. Tuy nhiên, điều này có thể khó khăn đối với những người dùng thiếu kinh nghiệm hoặc trong trường hợp danh sách các lựa chọn dài.
Một tùy chọn khác là đặt danh sách vào một phạm vi được đặt tên trong bảng tính, sau đó chỉ định tên phạm vi đó (bắt đầu bằng dấu bằng) trong bảng tính. Nguồn: trường xác thực dữ liệu (như trong Hình 2).
Phương pháp thứ hai này giúp việc chỉnh sửa các lựa chọn trong danh sách dễ dàng hơn nhưng việc thêm hoặc xóa các mục có thể gặp vấn đề. Vì phạm vi được đặt tên (FruitChoices, trong ví dụ của chúng tôi) đề cập đến một phạm vi ô cố định ($H$3:$H$10 như được hiển thị), nếu có nhiều lựa chọn hơn được thêm vào các ô H11 trở xuống, chúng sẽ không hiển thị trong danh sách thả xuống (vì những ô đó không thuộc phạm vi FruitChoices).
Tương tự như vậy, ví dụ: nếu các mục nhập Lê và Dâu tây bị xóa, chúng sẽ không còn xuất hiện trong danh sách thả xuống nữa mà thay vào đó, danh sách thả xuống sẽ bao gồm hai lựa chọn “trống” vì danh sách thả xuống vẫn tham chiếu toàn bộ phạm vi FruitChoices, bao gồm các ô trống H9 và H10.
Vì những lý do này, khi sử dụng một phạm vi được đặt tên thông thường làm nguồn danh sách cho danh sách thả xuống, bản thân phạm vi được đặt tên đó phải được chỉnh sửa để bao gồm nhiều hoặc ít ô hơn nếu các mục nhập được thêm hoặc xóa khỏi danh sách.
Giải pháp cho vấn đề này là sử dụng một năng động tên phạm vi làm nguồn cho các lựa chọn thả xuống. Tên phạm vi động là tên tự động mở rộng (hoặc thu gọn) để khớp chính xác với kích thước của khối dữ liệu khi các mục nhập được thêm hoặc xóa. Để làm điều này, bạn sử dụng một công thứcthay vì một dải địa chỉ ô cố định, để xác định dải ô được đặt tên.
Cách thiết lập dải động trong Excel
Tên phạm vi (tĩnh) bình thường đề cập đến một phạm vi ô được chỉ định ($H$3:$H$10 trong ví dụ của chúng tôi, xem bên dưới):
Nhưng phạm vi động được xác định bằng công thức (xem bên dưới, được lấy từ một bảng tính riêng sử dụng tên phạm vi động):
Trước khi chúng tôi bắt đầu, hãy đảm bảo bạn tải xuống tệp ví dụ Excel của chúng tôi (các macro sắp xếp đã bị tắt).
Hãy xem xét công thức này một cách chi tiết. Các lựa chọn cho Trái cây nằm trong một khối ô ngay bên dưới tiêu đề (TRÁI CÂY). Tiêu đề đó cũng được gán một tên: Trái CâyTiêu Đề:
Toàn bộ công thức được sử dụng để xác định phạm vi động cho các lựa chọn Trái cây là:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
Trái CâyTiêu Đề đề cập đến tiêu đề nằm phía trên mục đầu tiên trong danh sách một hàng. Số 20 (được sử dụng hai lần trong công thức) là kích thước (số hàng) tối đa cho danh sách (có thể điều chỉnh theo ý muốn).
Lưu ý rằng trong ví dụ này, chỉ có 8 mục trong danh sách, nhưng cũng có những ô trống bên dưới những ô này để có thể thêm các mục bổ sung. Số 20 đề cập đến toàn bộ khối nơi các mục có thể được thực hiện, không phải số lượng mục thực tế.
Bây giờ, hãy chia công thức thành từng phần (mã hóa màu cho từng phần) để hiểu cách thức hoạt động của nó:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
Phần “trong cùng” là OFFSET(Tiêu đề trái cây,1,0,20,1). Điều này tham chiếu khối gồm 20 ô (bên dưới ô FruitsHeading) nơi có thể nhập các lựa chọn. Về cơ bản, hàm OFFSET này có nội dung: Bắt đầu tại Trái CâyTiêu Đề ô, xuống 1 hàng trở lên 0 columns, sau đó chọn vùng dài 20 hàng và rộng 1 cột. Vì vậy, điều đó mang lại cho chúng ta khối 20 hàng nơi nhập các lựa chọn Trái cây.
Phần tiếp theo của công thức là ISBLANK chức năng:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)
Ở đây, hàm OFFSET (đã giải thích ở trên) đã được thay thế bằng hàm “the Above” (để dễ đọc hơn). Nhưng hàm ISBLANK đang hoạt động trên phạm vi ô 20 hàng mà hàm OFFSET xác định.
Sau đó, ISBLANK tạo một tập hợp gồm 20 giá trị TRUE và FALSE, cho biết mỗi ô riêng lẻ trong phạm vi 20 hàng được tham chiếu bởi hàm OFFSET có trống (trống) hay không. Trong ví dụ này, 8 giá trị đầu tiên trong tập hợp sẽ là FALSE vì 8 ô đầu tiên không trống và 12 giá trị cuối cùng sẽ là TRUE.
Phần tiếp theo của công thức là hàm INDEX:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
Một lần nữa, “ở trên” đề cập đến các hàm ISBLANK và OFFSET được mô tả ở trên. Hàm INDEX trả về một mảng chứa 20 giá trị TRUE/FALSE được tạo bởi hàm ISBLANK.
MỤC LỤC thường được sử dụng để chọn một giá trị nhất định (hoặc phạm vi giá trị) từ một khối dữ liệu, bằng cách chỉ định một hàng và cột nhất định (trong khối đó). Nhưng việc đặt đầu vào hàng và cột về 0 (như được thực hiện ở đây) sẽ khiến INDEX trả về một mảng chứa toàn bộ khối dữ liệu.
Phần tiếp theo của công thức là hàm MATCH:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
Các CUỘC THI ĐẤU hàm trả về vị trí của giá trị TRUE đầu tiên, trong mảng được hàm INDEX trả về. Vì 8 mục đầu tiên trong danh sách không trống nên 8 giá trị đầu tiên trong mảng sẽ là FALSE và giá trị thứ chín sẽ là TRUE (vì giá trị thứ 9quần què hàng trong phạm vi trống).
Vì vậy hàm MATCH sẽ trả về giá trị của 9. Tuy nhiên, trong trường hợp này, chúng ta thực sự muốn biết có bao nhiêu mục trong danh sách, do đó công thức trừ 1 từ giá trị MATCH (cung cấp vị trí của mục nhập cuối cùng). Vì vậy, cuối cùng, MATCH(TRUE,the Above,0)-1 trả về giá trị của số 8.
Phần tiếp theo của công thức là hàm IFERROR:
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
Hàm IFERROR trả về một giá trị thay thế nếu giá trị đầu tiên được chỉ định dẫn đến lỗi. Hàm này được đưa vào vì nếu toàn bộ khối ô (tất cả 20 hàng) chứa đầy các mục nhập thì hàm MATCH sẽ trả về lỗi.
Điều này là do chúng ta đang yêu cầu hàm MATCH tìm kiếm giá trị TRUE đầu tiên (trong mảng giá trị từ hàm ISBLANK), nhưng nếu KHÔNG có ô nào trống thì toàn bộ mảng sẽ chứa đầy các giá trị FALSE. Nếu MATCH không thể tìm thấy giá trị đích (TRUE) trong mảng mà nó đang tìm kiếm thì nó sẽ trả về lỗi.
Vì vậy, nếu toàn bộ danh sách đã đầy (và do đó, MATCH trả về lỗi), hàm IFERROR sẽ trả về giá trị 20 (biết rằng phải có 20 mục trong danh sách).
Cuối cùng, OFFSET(Tiêu đề trái cây,1,0,ở trên,1) trả về phạm vi mà chúng ta thực sự đang tìm kiếm: Bắt đầu tại ô FruitsHeading, đi xuống 1 hàng và hơn 0 columns, sau đó chọn một khu vực có chiều dài bao nhiêu hàng tùy theo số mục trong danh sách (và rộng 1 cột). Vì vậy, toàn bộ công thức sẽ trả về phạm vi chỉ chứa các mục thực tế (xuống ô trống đầu tiên).
Sử dụng công thức này để xác định phạm vi là nguồn cho danh sách thả xuống có nghĩa là bạn có thể tự do chỉnh sửa danh sách (thêm hoặc xóa các mục nhập, miễn là các mục còn lại bắt đầu ở ô trên cùng và liền kề nhau) và danh sách thả xuống sẽ luôn phản ánh hiện tại danh sách (xem Hình 6).
Tệp ví dụ (Danh sách động) được sử dụng ở đây được bao gồm và có thể tải xuống từ trang web này. Tuy nhiên, các macro không hoạt động vì WordPress không thích những cuốn sách Excel có macro trong đó.
Để thay thế cho việc chỉ định số lượng hàng trong khối danh sách, khối danh sách có thể được gán tên phạm vi riêng của nó, sau đó có thể được sử dụng trong một công thức đã sửa đổi. Trong tệp ví dụ, danh sách thứ hai (Tên) sử dụng phương pháp này. Ở đây, toàn bộ khối danh sách (bên dưới tiêu đề “NAMES”, 40 hàng trong tệp ví dụ) được gán tên dải ô của Khối tên. Công thức thay thế để xác định NamesList là:
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
Ở đâu TênKhối thay thế OFFSET(FruitsHeading,1,0,20,1) và HÀNG(Khối tên) thay thế 20 (số hàng) trong công thức trước đó.
Vì vậy, đối với danh sách thả xuống có thể dễ dàng chỉnh sửa (bao gồm cả những người dùng khác có thể thiếu kinh nghiệm), hãy thử sử dụng tên dải động! Và lưu ý rằng, mặc dù bài viết này tập trung vào danh sách thả xuống, nhưng tên phạm vi động có thể được sử dụng ở bất cứ đâu bạn cần để tham chiếu một phạm vi hoặc danh sách có thể có kích thước khác nhau. Thưởng thức!
