Đề XuấT, 2020

Editor Choice

Sử dụng tên phạm vi động trong Excel để thả xuống linh hoạt

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 việc nhập dữ liệu. Những 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 được phép.

Để thiết lập danh sách thả xuống đơn giản, chọn ô nơi nhập dữ liệu, sau đó bấm 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 :), rồi nhập các mục danh sách (được phân tách bằng dấu phẩy ) trong trường Nguồn : (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. Điều này có thể khó khăn, tuy nhiê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 một phạm vi được đặt tên trong bảng tính và 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 : xác thực dữ liệu (như trong Hình 2).

Phương pháp thứ hai này giúp dễ dàng chỉnh sửa các lựa chọn trong danh sách, 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).

Tương tự như vậy, ví dụ, nếu các mục 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, mà thay vào đó, phần thả xuống sẽ bao gồm hai lựa chọn trống rỗ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 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 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 tên phạm vi động 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. Để làm điều này, bạn sử dụng một 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 Phạm vi độ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 (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 các ô ngay bên dưới một tiêu đề ( TRÁI CÂY ). Tiêu đề đó cũng được gán một tên: FruitsHead :

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 (FruitsHead, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK (OFFSET (FruitsHead, 1, 0, 20, 1)), 0, 0), 0) -1, 20), 1) 

FruitsHead đề cập đến tiêu đề là 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 trong danh sách, nhưng cũng có các ô trống bên dưới những mục 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ác mục có thể được thực hiện, không phải cho 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 từng màu), để hiểu cách thức hoạt động của nó:

 = OFFSET (FruitsHead, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK ( OFFSET (FruitsHead, 1, 0, 20, 1) ), 0, 0), 0) -1, 20), 1) 

Các mảnh trong cùng của người VikingOFFSET (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à hơ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 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à hàm ISBLANK :

 = OFFSET (FruitsHead, 1, 0, IFERROR (MATCH (TRUE, INDEX ( ISBLANK (ở trên), 0, 0), 0) -1, 20), 1) 

Ở đây, chức năng OFFSET (đã giải thích ở trên) đã được thay thế bằng cách sử dụng 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 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 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 (FruitsHead, 1, 0, IFERROR (MATCH (TRUE, INDEX (ở trên, 0, 0), 0) -1, 20), 1) 

Một lần nữa, Tiếng Việt trên đây đề 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.

INDEX thường được sử dụng để chọn một giá trị nhất định (hoặc phạm vi giá trị) trong 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 (FruitsHead, 1, 0, IFERROR ( MATCH (TRUE, ở trên, 0) -1, 20), 1) 

Hàm MATCH 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ứ chín sẽ là TRUE (vì hàng thứ 9 trong phạm vi trống).

Vì vậy, hàm MATCH sẽ trả về giá trị là 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ị 8 .

Phần tiếp theo của công thức là hàm IFERROR:

 = OFFSET (FruitsHead, 1, 0, IFERROR (ở trên, 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 điền đầy các mục, hàm MATCH sẽ trả về lỗi.

Điều này là do chúng ta đang bảo hàm MATCH 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ẽ 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, 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), thay vào đó, 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 (FruitsHead, 1, 0, ở trên, 1) trả về phạm vi chúng ta 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 dài như 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 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, miễn là các mục còn lại bắt đầu ở ô trên cùng và liền kề) 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 sách Excel có macro.

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 của chính nó, sau đó có thể được sử dụng trong một công thức đượ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. Ở đâ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 (NameHead, 1, 0, IFERROR (MATCH (TRUE, INDEX (ISBLANK ( NamesBlock ), 0, 0), 0) -1, ROWS (NameBlock) ), 1) 

trong đó NamesBlock thay thế OFFSET (FruitsHead, 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 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. Thưởng thức!

Top