Bảng tính Excel thường bao gồm danh sách thả xuống ô để đơn giản hóa và / hoặc 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 cách sử dụng tính năng xác thực dữ liệu để chỉ định danh sách các mục đượ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 thực dữ liệu (trên Dữ liệu ), 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 phân tách bằng dấu phẩy) trong Nguồn :field (xem Hình 1).
Trong loại trình đơn thả xuống cơ bản này, danh sách các mục nhập được phép được chỉ định trong chính quá trình xác thực dữ liệu; do đó, để thực hiện các thay đổi đối với 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 người dùng thiếu kinh nghiệm hoặc trong trường hợp danh sách lựa chọn dài.
Một tùy chọn khác là đặt danh sách trong 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 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 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 nhiều lựa chọn được thêm vào các ô H11 trở xuống, chúng sẽ không hiển thị trong menu 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 Pears và Strawberries bị xóa, chúng sẽ không còn xuất hiện trong menu thả xuống nữa, mà thay vào đó, menu thả xuống sẽ bao gồm hai lựa chọn "trống" vì menu thả xuống vẫn tham chiếu đến 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 dải ô được đặt tên bình thường làm nguồn danh sách cho danh sách thả xuống, bản thân dải ô đã đặ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 vào hoặc xóa khỏi danh sách.
Giải pháp cho vấn đề này là sử dụ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 hợp đồng) để 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 vào hoặc xóa. Để làm điều này, bạn sử dụng công thức , thay 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 bình thường (tĩnh) đề 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 một 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: FruitsHeading :
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)
FruitsHeading đề cập đến tiêu đề nằm trên một hàng phía trên mục nhập đầu tiên trong danh sách. Số 20 (được sử dụng hai lần trong công thức) là kích thước tối đa (số hàng) cho danh sách (điều này có thể được điều chỉnh theo ý muốn).
Lưu ý rằng trong ví dụ này, chỉ có 8 mục nhập trong danh sách, nhưng cũng có các ô trống bên dưới các ô 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ó thể thực hiện các mục nhập, không phải số lượng mục nhập thực tế.
Bây giờ, hãy chia công thức thành nhiều phần (mã hóa màu cho từng phần), để hiểu cách 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 (FruitsHeading, 1,0,20,1) . Điều này tham chiếu đến khối 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 cho biết:Bắt đầu ở FruitsHeading ô, đi xuống 1 hàng và hơn 0 cột, sau đó chọn một vùng có chiều dài 20 hàng và chiều rộng 1 cột. Vì vậy, điều đó cung cấp cho chúng tôi khối 20 hàng nơi các lựa chọn Trái cây được nhập.
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 “ở trên” (để làm cho mọi thứ dễ đọc hơn). Nhưng hàm ISBLANK đang hoạt động trên dải ô 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 liệu từng ô 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 chức năng 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.
INDEX 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 thành 0 (như được thực hiện ở đây) 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)
TRẬN ĐẤU hàm trả về vị trí của giá trị TRUE đầu tiên, trong mảng được trả về bởi hàm INDEX. Vì 8 mục đầu tiên trong danh sách không trống, 8 giá trị đầu tiên trong mảng sẽ là FALSE và giá trị thứ 9 sẽ là TRUE (kể từ thứ 9 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 tôi thực sự muốn biết có bao nhiêu mục nhập trong danh sách, vì vậy công thức trừ 1 khỏi giá trị MATCH (cho biết vị trí của mục nhập cuối cùng). Vì vậy, cuối cùng, MATCH (TRUE, ở trên, 0) -1 trả về giá trị của 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 bao gồm vì nếu toàn bộ khối ô (tất cả 20 hàng) được lấp đầy bởi các mục nhập, thì hàm MATCH sẽ trả về một lỗi.
Điều này là do chúng tôi đ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 NONE của các ô trống, thì toàn bộ mảng sẽ được lấp đầy bằng 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, 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 thay vào đó (biết rằng phải có 20 mục trong danh sách).
Cuối cùng, OFFSET (FruitsHeading, 1,0, ở trên, 1) trả về phạm vi mà chúng tôi thực sự đang tìm kiếm:Bắt đầu tại ô FruitsHeading, đi xuống 1 hàng và hơn 0 cột, sau đó chọn một khu vực có bao nhiêu hàng miễn là có các mục nhập trong danh sách (và rộng 1 cột). Vì vậy, toàn bộ công thức cùng nhau sẽ trả về phạm vi chỉ chứa các mục nhập 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 menu 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 nhập còn lại bắt đầu ở ô trên cùng và liền kề) và menu 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, macro không hoạt động vì WordPress không thích sách Excel có macro.
Để thay thế cho việc chỉ định số 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. Tại đâ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 phạm vi là NameBlock . Công thức thay thế để xác định Danh sách tên sau đó là:
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
nơi NamesBlock thay thế OFFSET (FruitsHeading, 1,0,20,1) và ROWS (NamesBlock) 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ả bởi 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 kỳ đâu bạn cần tham chiếu đến một phạm vi hoặc danh sách có thể khác nhau về kích thước. Hãy tận hưởng!