Bảng tính Excel thường bao gồm danh sách thả xuống (dropdown) ô để đơ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 tạo bằng tính năng kiểm tra dữ liệu (data validation) chỉ định danh sách mục cho phép.
Để thiết lập danh sách thả xuống đơn giản, chọn ô nhập dữ liệu, nhấp Data Validation (Kiểm tra dữ liệu) (tab Data), chọn Data Validation, chọn List (Danh sách) (dưới Allow:), nhập các mục danh sách (cách nhau dấu phẩy) trường Source (Nguồn): (xem Hình 1).
Loại dropdown cơ bản này, danh sách mục cho phép chỉ định trong chính data validation; do đó, thay đổi danh sách, người dùng phải mở chỉnh sửa data validation. Điều này khó khăn cho người ít kinh nghiệm, hoặc khi danh sách lựa chọn dài.
Tùy chọn khác: đặt danh sách trong phạm vi đặt tên (named range) trong bảng tính, sau chỉ định tên phạm vi đó (đầu bằng dấu bằng) trường Source của data validation (như Hình 2).
Phương pháp hai dễ chỉnh sửa lựa chọn danh sách, nhưng thêm/xóa mục có vấn đề. Vì phạm vi đặt tên (FruitChoices, ví dụ) tham chiếu phạm vi ô cố định ($H$3:$H$10 như hình), nếu thêm lựa chọn ô H11 trở xuống, chúng không hiện dropdown (vì ô đó không trong phạm vi FruitChoices).
Tương tự, nếu xóa mục Pears và Strawberries, chúng không còn hiện dropdown, nhưng dropdown sẽ bao gồm hai lựa chọn "trống" vì dropdown vẫn tham chiếu toàn bộ phạm vi FruitChoices, bao gồm ô trống H9, H10.
Vì vậy, dùng phạm vi đặt tên bình thường làm nguồn danh sách dropdown, phạm vi đặt tên phải chỉnh sửa để bao gồm nhiều/ít ô hơn nếu mục thêm/xóa từ danh sách.
Giải pháp vấn đề này: dùng dynamic range name (tên phạm vi động) làm nguồn lựa chọn dropdown. Phạm vi động tự động mở rộng (thu hẹp) khớp chính xác kích thước khối dữ liệu khi mục thêm/xóa. Để làm này, dùng công thức (formula), thay vì phạm vi địa chỉ ô cố định, định nghĩa phạm vi đặt tên.
Cách Thiết Lập Phạm Vi Động Trong Excel
Phạm vi đặt tên bình thường (tĩnh) tham chiếu phạm vi ô chỉ định ($H$3:$H$10 ví dụ, xem dưới):
Nhưng phạm vi động định nghĩa dùng công thức (xem dưới, từ bảng tính riêng dùng dynamic range names):
Trước bắt đầu, tải file ví dụ Excel (macro sắp xếp đã tắt).
Xem chi tiết công thức. Lựa chọn Fruits (Trái cây) ở khối ô ngay dưới tiêu đề (FRUITS). Tiêu đề cũng đặt tên: FruitsHeading:
Toàn bộ công thức định nghĩa dynamic range cho lựa chọn Fruits:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
FruitsHeading tham chiếu tiêu đề hàng trên mục đầu danh sách. Số 20 (dùng hai lần công thức) là kích thước tối đa (số hàng) danh sách (có thể điều chỉnh).
Lưu ý ví dụ chỉ có 8 mục danh sách, nhưng cũng có ô trống dưới nơi thêm mục. Số 20 tham chiếu toàn bộ khối nơi nhập mục, không phải số mục thực tế.
Bây giờ phân tích công thức thành mảnh (mã màu mỗi mảnh), hiểu cách hoạt động:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)
Mảnh "bên trong nhất" là OFFSET(FruitsHeading,1,0,20,1). Tham chiếu khối 20 ô (dưới FruitsHeading) nơi nhập lựa chọn. Hàm OFFSET cơ bản nói: Bắt đầu ô FruitsHeading, xuống 1 hàng, qua 0 cột, chọn khu vực 20 hàng dài 1 cột rộng. Cho khối 20 hàng nơi nhập lựa chọn Fruits.
Mảnh tiếp theo hàm ISBLANK:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)
Lại, hàm OFFSET (giải thích trên) thay bằng "the above" (để dễ đọc). Nhưng ISBLANK hoạt động trên phạm vi 20 ô OFFSET định nghĩa.
ISBLANK tạo tập 20 giá trị TRUE/FALSE chỉ mỗi ô riêng trong phạm vi 20 hàng OFFSET tham chiếu trống (rỗng) hay không. Ví dụ, 8 giá trị đầu tập là FALSE (8 ô đầu không rỗng) 12 giá trị cuối TRUE.
Mảnh tiếp theo hàm INDEX:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
Lại, "the above" tham chiếu ISBLANK và OFFSET trên. INDEX trả mảng chứa 20 TRUE/FALSE ISBLANK tạo.
INDEX thường chọn giá trị (hoặc phạm vi giá trị) khỏi khối dữ liệu, chỉ định hàng/cột (trong khối). Nhưng đặt đầu vào hàng/cột bằng 0 (như đây) khiến INDEX trả mảng chứa toàn bộ khối dữ liệu.
Mảnh tiếp theo hàm MATCH:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
Hàm MATCH trả vị trí giá trị TRUE đầu tiên, trong mảng INDEX trả. Vì 8 mục đầu không trống, 8 giá trị đầu mảng FALSE, giá trị thứ 9 TRUE (hàng 9 trong phạm vi rỗng).
Vậy MATCH trả 9. Trong trường hợp này, chúng ta thực sự muốn biết số mục danh sách, công thức trừ 1 từ giá trị MATCH (cho vị trí mục cuối). Cuối cùng, MATCH(TRUE,the above,0)-1 trả 8.
Mảnh tiếp theo hàm IFERROR:
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
IFERROR trả giá trị thay thế, nếu giá trị đầu dẫn đến lỗi. Hàm này đưa vào vì, nếu toàn bộ khối ô (tất cả 20 hàng) điền mục, MATCH trả lỗi.
Điều này vì chúng ta bảo MATCH tìm giá trị TRUE đầu (trong mảng ISBLANK), nhưng nếu KHÔNG ô nào rỗng, toàn bộ mảng FALSE. Nếu MATCH không tìm giá trị mục tiêu (TRUE) mảng tìm, trả lỗi.
Vì vậy, nếu toàn bộ danh sách đầy (MATCH lỗi), IFERROR trả 20 (biết chắc 20 mục).
Cuối cùng, OFFSET(FruitsHeading,1,0,the above,1) trả phạm vi thực sự tìm: Bắt đầu FruitsHeading, xuống 1 hàng, qua 0 cột, chọn khu vực dài bao nhiêu hàng bằng số mục danh sách (1 cột rộng). Toàn bộ công thức cùng trả phạm vi chỉ chứa mục thực tế (xuống ô trống đầu).
Dùng công thức này định nghĩa phạm vi nguồn dropdown nghĩa là bạn tự do chỉnh sửa danh sách (thêm/xóa mục, miễn các mục còn lại bắt đầu ô đầu và liền kề) và dropdown luôn phản ánh danh sách hiện tại (xem Hình 6).
File ví dụ (Dynamic Lists) dùng ở đây có sẵn tải từ trang web. Macro không hoạt động, vì WordPress không thích file Excel có macro.
Thay thế chỉ định số hàng khối danh sách, khối danh sách có thể đặt tên phạm vi riêng, dùng trong công thức sửa đổi. File ví dụ, danh sách thứ hai (Names) dùng phương pháp này. Ở đây, toàn bộ khối danh sách (dưới tiêu đề "NAMES", 40 hàng file ví dụ) đặt tên phạm vi NameBlock. Công thức thay thế định nghĩa NamesList:
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
nơi NamesBlock thay OFFSET(FruitsHeading,1,0,20,1) và ROWS(NamesBlock) thay 20 (số hàng) công thức trước.
Vì vậy, cho danh sách thả xuống dễ chỉnh sửa (kể cả người khác ít kinh nghiệm), hãy thử dynamic range names! Và lưu ý, dù bài tập trung dropdown, dynamic range names dùng đâu cần tham chiếu phạm vi/danh sách thay đổi kích thước. Thưởng thức!