Bài viết: Offset truyền kỳ (kỳ 2)

[TranThanhPhong]

Kỳ 2: Offset và Validation List

a. Có bao giờ bạn muốn tạo một Validation List mà nội dung của nó thay đổi theo sự thay đổi của một List khác (danh sách). Bài viết này sẽ hướng dẫn từng bước cách làm:

Giả sử bạn có danh sách gồm 2 cột: OS (hệ điều hành) và Versions (Phiên bản) (Xem file đính kèm). Bây giờ bạn muốn tạo một Validation List để khi tại ô G3 ta chọn một OS nào đó thì danh sách trong ô H3 sẽ chỉ hiển thị các Versions của OS mà ta đang chọn (xem hình 6).

Qui trình thực hiện:

B1. Tạo một danh sách A1:B34 (xem file đính kèm), với 2 cột OS và Versions. Lưu ý một OS có thể có nhiều Version.

B2. Đặt tên cho vùng A2:A34 với tên là "OS". Bạn quét chọn vùng A2:A34 | nhấn tổ hợp <Ctrl+F3> | Chọn New… gõ OS vào hộp Name, chọn Scope là Worksheet Offset2 hoặc Workbook đều được và tại Refers to gõ vào =A2:A34 | Nhấn nút OK (Excel 2007). Nếu dùng các phiên bản Excel trước thì sau khi nhấn <Ctrl+F3> là có thể khai báo việc đặt tên vùng và sao đó nhấn nút Add để hoàn thành.

B3. Tạo một danh sách chứa tên các hệ điều hành được trích ra từ cột OS (danh sách này không trùng lặp). Bạn quét chọn các ô D234 dùng để chứa danh sách kết quả trích lọc không trùng | nhập vào công thức mảng trích lọc sau:

=IF(ISERR(INDEX(OS,SMALL(IF(MATCH(OS,OS,0)=ROW(INDIRECT("1:"&ROWS(OS))), MATCH(OS,OS,0),""),ROW(INDIRECT("1:"&ROWS(OS)))))),"",
INDEX(OS,SMALL(IF(MATCH(OS,OS,0)=ROW(INDIRECT("1:"&ROWS(OS))), MATCH(OS,OS,0),""),ROW(INDIRECT("1:"&ROWS(OS))))))

Kết thúc công thức bằng tổ hợp phím <Ctrl+Shift+Enter>. Công thức trên sẽ trích ra các loại hệ điều hành (OS) và mỗi loại chỉ lấy một lần, các dòng trùng sẽ thay bằng "".

B4. Sau khi đã có danh sách các OS duy nhất, bạn hãy đặt tên cho danh sách này. Lưu ý, có khả năng có những loại OS khác do vậy ở đây khi đặt tên là dùng hàm Offset để tham chiếu đến vùng dữ liệu động.

Bạn nhấn <Ctrl+F3> | Chọn New… | đặt tên cho vùng là OSValList tại Name | tại Refers to nhập vào công thức sau;
=OFFSET(Offset2!$D$2,0,0,COUNTA(Offset2!$D$2:$D$34)-COUNTIF(Offset2!$D$2:$D$34,""),1)

Công thức trên giúp trả về vùng tham chiếu các OS duy nhất một cách động.

B5. Tạo bảng nhỏ như hình bên dưới. Chọn ô G3 và đặt tên là Val1Cell, chọn ô H3 đặt tên là Val2Cell (theo cách đặt tên nêu trên hoặc có thể đặt tên bằng cách gõ trực tiếp vào Name Box).

B6. Đây là bước quan trọng giúp cho danh sách chọn tại H3 sẽ thay đổi phụ thuộc vào tùy chọn tại G3. Bạn đặt tên cho vùng Versions với tham chiếu trả về thay đổi theo tùy chọn của G3 như sau:

Nhấn <Ctrl+F3> | chọn New… | tại hộp Name nhập vào tên là Versions | tại Refers to nhập vào công thức sau;

=OFFSET(INDIRECT(ADDRESS(MATCH(Val1Cell,OS,0)+1,2,,,)),0,0,COUNTIF(OS,Val1Cell),1)

Công thức này giúp chọn ra vùng Versions tương ứng với việc chọn OS tại ô G3.

B7. Áp Validation cho 2 ô G3 và H3:

Chọn ô G3 | vào Data | Data Validation | Settings | chọn List | tại Source nhập vào =OSValList | nhấn nút Ok hoàn tất.

Chọn ô H3 | vào Data | Data Validation | Settings | chọn List | tại Source nhập vào =Versions | nhấn nút Ok hoàn tất.

Các bước đã hoàn tất, các bạn hãy chọn thử một OS nào đó tại G3 và qua ô H3 xem danh sách Version tương ứng.

Các cải tiến cho công cụ này chúng ta có thể tiếp tục thảo luận trên GPE.

TP.

Một số bài viết có liên quan:
1/ Offset truyền kỳ (kỳ 1)
2/ SUMPRODUCT và Công thức mảng - Phép tính có nhiều điều kiện
3/ Sử dụng Excel để ra quyết định hằng ngày
4/ Excel nâng cao: Sử dụng sự lặp lại và các tham chiếu tuần hoàn
5/ 10 lý do để học công thức Excel
6/ Làm việc với công thức mảng trong Excel
7/ VLOOKUP với Cột động
8/ Sử dụng hàm Subtotal
9/ Cách viết hàm hiệu quả
10/ Dùng hàm "Đọc số thành chữ VND và USD" với thủ thuật Name và hàm Macro4

 

[songmai]

Công thức mảng kia dài quá, hiểu nó cũng mệt mà để nhớ thì càng mệt hơn. Tôi đề nghị dùng cách này dễ nhớ, dễ áp dụng hơn:
- Dùng thêm cột phụ là cột C nhập công thức từ C2 là =COUNTIF(OS;INDIRECT("A"&SUM($C$1:C1)+2))
- Tại D2 nhập công thức: =INDIRECT("A"&SUM($C$1:C1)+2)

Chép công thức từ C22 xuống các dòng dưới đến lúc nào hết tên Hệ điều hành thì dừng