Bảng tính Excel thường bao gồm các ô thả xuống để đơn giản hóa và / hoặc chuẩn hóa 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, 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 tab Dữ liệu), chọn Xác thực dữ liệu, chọn Danh sách(bên dưới Cho phép :), sau đó nhập các mục danh sách (được phân tách bằng dấu phẩy) trong Nguồn: trường (xem Hình 1).
Trong loại 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 xác thực dữ liệu; do đó, để thay đổ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 các lựa chọn dài.
Một tùy chọn khác là đặt danh sách trong phạm vi được đặt tên trong bảng tính, sau đó chỉ định tên phạm vi đó (được đặt trước bằng một dấu bằng) trong trường Nguồn: của 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 có thêm lựa chọn được thêm vào các ô H11 hoặc bên dưới, chúng sẽ không hiển thị trong danh sách thả xuống (vì các ô đó không phải là một phần của phạm vi FruitChoices). Các lựa chọn trống rỗng khi lựa chọn 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 phạm vi được đặt tên bình thường làm nguồn danh sách cho danh sách thả xuống, phạm vi được đặt tên chính 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.
Một giải pháp cho vấn đề này là sử dụng độngtê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 một khối dữ liệu khi các mục nhập được thêm hoặc xóa. Để thực hiện việc này, bạn sử dụng công thức, thay vì một phạm vi địa chỉ ô cố định, để xác định phạm vi được đặt tên.
Cách thiết lập động Phạm vi 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 (macro sắp xếp đã bị vô hiệu hóa).
Hãy để chi tiết kiểm tra công thức này. Các lựa chọn cho Trái cây nằm trong một khối các ô ngay bên dưới một tiêu đề (TRÁI CÂY). Tiêu đề đó cũng được gán tên: FruitsHead:
Toàn bộ công thức được sử dụng để xác định phạm vi động cho 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)
FruitsHeadđề cập đến tiêu đề nằm trên một hàng phía trên mục đầ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 những ô này có thể thêm các mục nhập 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, chứ không phải số lượng mục thực tế.
Bây giờ, hãy chia công thức thành các phần (mã hóa từng màu), để 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)
Đoạn trong cùng của nhóm là OFFSET (FruitsHead, 1,0,20,1). Phần này tham chiếu khối 20 ô (bên dưới ô FruitsHead) nơi có thể nhập các lựa chọn. Hàm OFFSET này về cơ bản cho biết: Bắt đầu tại ô FruitsHead, đi xuống 1 hàng và trên 0 cột, sau đó chọn một khu vực dài 20 hàng và rộng 1 cột. Vì vậy, nó cung cấp cho chúng tôi 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à hàm ISBLANK:
=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 trên, trên đây (để làm cho mọi thứ 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.
ISBLANK sau đó tạo một bộ gồm 20 giá trị TRUE và FALSE, cho biết liệu mỗi ô riêng lẻ trong 20- phạm vi 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 do 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, Tiếng nói 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.
INDEXthường được sử dụng để chọn một giá trị (hoặc phạm vi giá trị) nhất định 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 các đầ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)
Hàm MATCHtrả 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, 8 giá trị đầu tiên trong mảng sẽ là FALSE và giá trị thứ chín sẽ là TRUE (vì hàng 9 thtrong 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 trong danh sách, vì vậy công thức trừ 1 từ giá trị MATCH (cung cấp vị trí của mục 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) chứa đầy các mục, hàm MATCH sẽ trả về lỗi.
Điều này là do chúng tôi đang nói với hàm MATCH cần tìm giá trị TRUE đầu tiên (trong mảng các 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 điền với 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ẽ thay vì trả về giá trị 20 (biết rằng phải có 20 mục trong danh sách).
Cuối cùng, OFFSET (FruitsHead, 1,0, ở trên, 1)trả về phạm vi chúng tôi thực sự đang tìm kiếm: Bắt đầu tại ô FruitsHead, đi xuống 1 hàng và hơn 0 cột, sau đó chọn một khu vực có nhiều hàng miễn là có các mục 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 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 trình đơn 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, 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 danh sách hiện tại (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 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 chỉ định tên phạm vi riêng, 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 thức này. Tại đây, toàn bộ khối danh sách (bên dưới tiêu đề của NÊN Cảnh, 40 hàng trong tệp ví dụ) được gán tên phạm vi của NameBlock. Công thức thay thế để xác định Danh sách tên là:
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
trong đó TênBlockthay thế OFFSET (FruitsHead, 1,0,20,1) và ROWS (Tên khóa)thay thế 20 (số hàng) trong công thức trước đó.
Vì vậy, đối với các 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 phạm vi động! Và lưu ý rằng, mặc dù bài viết này đã được tập trung vào danh sách thả xuố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ể thay đổi kích thước. Tận hưởng!