Bạn sẽ làm thế nào để tạo ra được 1 danh sách chọn (dropdown List) phụ thuộc vào nội dung được chọn ở danh sách khác? Giả sử như chúng ta có 4 chi nhánh, tại mỗi chi nhánh có số lượng nhân viên khác nhau, tên nhân viên khác nhau. Làm thế nào để khi chọn 1 chi nhánh trong danh sách chi nhánh thì danh sách nhân viên sẽ thay đổi tương ứng với chi nhánh đó. Cụ thể như ví dụ sau:

Yêu cầu:

1. Thống kê tổng số người trong mỗi chi nhánh tại dòng 12
2. Tạo danh sách chọn chi nhánh tại ô G2
3. Tạo danh sách chọn nhân viên phụ thuộc chi nhánh tại ô G3

Sau đây chúng ta cùng tìm hiểu cách tạo danh sách nhân viên phụ thuộc theo chi nhánh trên Excel nhé:

Thống kê tổng số người trong mỗi chi nhánh

Để thống kê số người trong mỗi chi nhánh, chúng ta sẽ cần phải đếm số ô có chứa nội dung trong mỗi cột.

Để làm việc này, trong Excel chúng ta sử dụng hàm COUNTA. Cách làm như sau:

A12=COUNTA(A2:A11)&” người”

Trong đó:

• Hàm COUNTA(A2:A11) ra kết quả là con số thể hiện tổng số người
• Ký tự & để nối chuỗi
• người là 1 chuỗi ký tự, để có thể ghép với 1 hàm thì chúng ta phải đặt chuỗi đó trong dấu nháy kép

Sau khi tính được kết quả tại A12, chúng ta sẽ copy công thức sang các cột bên phải, trên cùng dòng 12 và thu được kết quả như sau:

Tạo danh sách chọn chi nhánh

Để tạo danh sách chọn cho các chi nhánh, chúng ta sẽ sử dụng chức năng Data Validation trong thẻ Data.

Khi mở cửa sổ Data Validation, chúng ta chọn tiếp:

• Mục Allow: Chọn List
• Source: Chọn vùng A1:D1 là danh sách các chi nhánh

Kết quả thu được là chúng ta có danh sách chọn tại ô G2 là danh sách các chi nhánh từ Chi nhánh 01 đến chi nhánh 04

Xem thêm:

Hướng dẫn sử dụng Data Validation để nhập nhanh dữ liệu từ select box

Cách thiết lập chỉ nhập dữ liệu dạng ngày tháng trong ô trên Excel

Tạo danh sách chọn nhân viên phụ thuộc chi nhánh

Chúng ta đã biết cách để tạo danh sách chọn trong 1 ô, đó là sử dụng Data Validation. Nhưng điều quan trọng trong trường hợp này là xác định đúng được Source để nạp vào trong List.

Xác định vị trí cột

Việc thay đổi tên chi nhánh sẽ giúp chúng ta xác định được vị trí cột cần tham chiếu: Dùng hàm Match để tra cứu tên chi nhánh được chọn trong danh sách chi nhánh. Kết quả là số cột tính từ vị trí bắt của vùng lookup_array của hàm Match

=MATCH(G2,A1:D1,0)

Trong đó:

• G2: Tên chi nhánh được chọn
• A1:D1: Vùng danh sách chi nhánh
• 0: Phương thức tham chiếu là chính xác theo tên chi nhánh

Ví dụ tại G2 chọn Chi nhánh 02 thì ta có kết quả là số 2

Xác định tọa độ điểm đầu vùng danh sách

Dựa vào bảng danh sách, chúng ta có thể thấy danh sách các chi nhánh đều bắt đầu từ dòng 2. Do đó chúng ta có thể xác định điểm bắt đầu của danh sách bằng hàm ADDRESS.

Hàm Address giúp xác định địa chỉ của 1 ô bằng cách chỉ ra số dòng, số cột.

Trong trường hợp này ta có:

• Số dòng = 2
• Số cột = kết quả của hàm Match đã xác định ở trên (ô I7)

I8=ADDRESS(2,I7)

Xác định tọa độ điểm cuối vùng danh sách

Trong ví dụ về bảng danh sách ở trên, chúng ta có thể thấy tối đa tới dòng thứ 11. Nếu coi điểm cuối là dòng 11 thì ta có ô cuối là

I9=ADDRESS(11,I7)

Tuy nhiên để giúp danh sách thay đổi linh động tùy theo số nhân viên trong mỗi danh sách thì chúng ta sẽ cần tùy biến lại điểm cuối này.

Như ở trên chúng ta đã biết cách dùng hàm COUNTA để đếm trong vùng từ dòng 2 tới dòng 11 trong mỗi danh sách để xác định số nhân viên trong mỗi danh sách. Áp dụng trong trường hợp này chúng ta sẽ đếm trong phạm vi I8:I9

Cách viết hàm như sau:

J9=COUNTA(INDIRECT(I8&”:”&I9))

Trong đó:

• I8 là tọa độ điểm đầu
• I9 là tọa độ điểm cuối (tính toàn bộ số dòng)
• I8&”:”&I9 là thể hiện dưới dạng vùng từ điểm đầu tới điểm cuối
• Indirect là hàm giúp xác định nội dung trong vùng trên
• CountA để đếm toàn bộ các nội dung trong vùng tạo bởi Indirect, kết quả là số ô có chứa ký tự (ô có nội dung)

Khi đó điểm cuối có chứa dữ liệu trong vùng từ dòng 2 tới dòng 11 là:

I10=ADDRESS(J9+1,I7)

Ghép lại điểm đầu và điểm cuối để tạo thành 1 vùng dữ liệu thì chúng ta dùng:

I11=I8&”:”&I10

Để cho chắc chắn chúng ta có thể thêm tên Sheet nơi chứa bảng danh sách chi nhánh (ví dụ sheet DeBai):

I11=”DeBai!”&I8&”:”&I10

Tạo danh sách chọn nhân viên

Tại ô G3 chúng ta sử dụng Data Validation như sau:

Như vậy chúng ta đã hoàn thành viện tạo danh sách chọn nhân viên phụ thuộc vào tên chi nhánh được chọn. Chúc các bạn thành công!

Để tải về file mẫu trong bài viết này, các bạn có thể tải về theo địa chỉ: http://bit.ly/2nKY8h7

Xem thêm:

Hướng dẫn cách tạo danh sách phụ thuộc nhau trong Excel bằng VBA

Việc sử dụng linh hoạt các hàm và các công cụ trong Excel tạo ra hiệu quả rất tuyệt vời phải không nào. Nếu bạn gặp khó khăn trong việc sử dụng các hàm, các công cụ trong Excel thì hãy tham gia ngay khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Khóa học này giúp bạn nắm vững các kiến thức về các hàm, từ hàm cơ bản tới hàm nâng cao, hiểu rõ cách sử dụng các công cụ trong Excel như Conditional formatting, Data Validation, Filter, Pivot Table… Những kiến thức này đều rất cần thiết trong công việc hàng ngày trên Excel.

Hiện nay hệ thống đang có nhiều ưu đãi hấp dẫn khi đăng ký khóa học này. Hãy tham gia ngay để nhận được ưu đãi này nhé. Chi tiết xem tại: