Làm thế nào bạn có thể tạo một danh sách thả xuống phụ thuộc vào những gì được chọn trong một danh sách khác? Giả sử chúng ta có 4 chi nhánh, 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. Cách chọn 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ể là ví dụ sau:
Lời yêu cầu:
- Thống kê tổng số người theo từng ngành tại dòng 12
- Tạo danh sách chọn nhánh trong ô G2
- 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:
Thống kê tổng số người của từng chi nhánh
Để đếm số người trong mỗi nhánh, chúng ta sẽ cần đếm số ô chứa nội dung trong mỗi cột.
Để làm điều này, trong Excel, chúng tôi sử dụng chức năng COUNTA. Đây là cách thực hiện:
A12=COUNTA(A2:A11)&”người”
Trong đó:
- Hàm COUNTA(A2:A11) Kết quả là một con số đại diện cho tổng số người
- Nhân vật & để nối chuỗi
- Mọi người là một chuỗi ký tự, để có thể nối với hàm ta phải đặt chuỗi đó trong dấu ngoặc 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 hàng 12 và đượ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 nhánh, chúng ta sẽ sử dụng chức năng Data Validation trong tab Data.
Khi mở cửa sổ Data Validation, ta chọn tiếp:
- Cho phép mục: Chọn Danh sách
- Nguồn: Chọn vùng A1:D1 làm danh sách nhánh
Kết quả ta có một danh sách chọn tại ô G2 là danh sách các nhánh từ Nhánh 01 đến Nhánh 04
Xem thêm:
Hướng dẫn sử dụng Data Validation để nhập dữ liệu nhanh từ ô chọn
Làm sao để thiết lập chỉ nhập dữ liệu ngày tháng trong ô tính trên Excel?
Tạo một danh sách lựa chọn các 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 một ô, đó là sử dụng Xác thực dữ liệu. Nhưng điều quan trọng trong trường hợp này là xác định đúng Source cần nạp trong List.
Định vị cột
Việc thay đổi tên nhánh sẽ giúp chúng ta định vị được cột cần tham chiếu: Sử dụng hàm Match để tra cứu tên nhánh đã chọn trong danh sách các nhánh. Kết quả là số cột được tính từ vị trí bắt giữ của vùng lookup_array của Match. chức năng
=MATCH(G2,A1:D1,0)
Trong đó:
- G2: Tên nhánh đã chọn
- A1:D1: Vùng danh sách nhánh
- 0: Tham chiếu phương thức đúng theo tên nhánh
Ví dụ tại G2 chọn Nhánh 02 kết quả trả về là số 2
Xác định tọa độ điểm đầu của vùng danh sách
Dựa vào bảng danh sách ta thấy danh sách các nhánh đều bắt đầu từ dòng 2. Như vậy ta có thể xác định điểm bắt đầu của danh sách bằng hàm ADDRESS.
Hàm Địa chỉ giúp xác định địa chỉ của một ô bằng cách chỉ định số hàng và số cột.
Trong trường hợp này chúng ta có:
- Số dòng = 2
- Số cột = kết quả của hàm Match đã xác định ở trên (ô I7)
I8=ĐỊA CHỈ(2,I7)
Xác định tọa độ điểm cuối của vùng danh sách
Trong ví dụ về bảng danh sách ở trên, chúng ta có thể thấy đến hàng thứ 11. Nếu coi điểm cuối là dòng 11 thì ta có ô cuối cùng là
I9=ĐỊA CHỈ(11,I7)
Tuy nhiên, để giúp danh sách thay đổi linh hoạt tùy thuộc vào số lượng nhân viên trong mỗi danh sách, chúng ta sẽ cần tùy chỉnh điểm cuối này.
Như trên chúng ta đã biết cách sử dụng hàm COUNTA để đếm trong khoảng từ dòng 2 đến dòng 11 trong mỗi danh sách để xác định số lượng nhân viên trong mỗi danh sách. Áp dụng trong trường hợp này ta sẽ tính trong khoảng I8:I9
Làm thế nào để viết một chức năng như thế này:
J9=COUNTA( GIÁN TIẾP(I8&”:”&I9))
Trong đó:
- I8 là tọa độ điểm đầu
- I9 là tọa độ điểm cuối (đếm toàn bộ số dòng)
- I8&”:”&I9 được biểu diễn dưới dạng vùng từ đầu đến cuối
- gián tiếp là chức năng giúp xác định nội dung trong vùng trên
- ĐếmA để đếm tất cả nội dung trong vùng do Indirect tạo ra, kết quả trả về là số ô chứa ký tự (cells with content)
Sau đó, điểm cuối chứa dữ liệu trong phạm vi từ dòng 2 đến dòng 11 là:
I10=ĐỊA CHỈ(J9+1,I7)
Nối điểm đầu và điểm cuối để tạo thành một vùng dữ liệu, sau đó chúng ta sử dụng:
I11=I8&”:”&I10
Để chắc chắn hơn chúng ta có thể thêm tên Sheet nơi chứa bảng danh sách nhánh (ví dụ sheet DeBai):
I11=”DeBai!”&I8&”:”&I10
Tạo danh sách lựa chọn nhân viên
Trong ô G3, chúng tôi sử dụng Xác thực dữ liệu như sau:
Như vậy là chúng ta đã hoàn thành việc tạo danh sách lựa chọn nhân viên tùy theo tên chi nhánh đã chọn. Chúc may mắn!
Để tải file mẫu trong bài viết này, bạn có thể tải tại địa chỉ: http://bit.ly/2nKY8h7
Xem thêm:
Hướng dẫn cách tạo danh sách phụ thuộc trong Excel bằng VBA
Việc sử dụng linh hoạt các hàm và công cụ trong Excel tạo nên hiệu quả tuyệt vời phải không? Nếu bạn gặp khó khăn trong việc sử dụng các hàm và công cụ trong Excel thì hãy tham gia ngay khóa học EX101 – Excel từ cơ bản đến chuyên gia của Học Excel Online. Khóa học này giúp bạn nắm vững kiến thức về hàm, từ hàm cơ bản đến hàm nâng cao, hiểu cách sử dụng các công cụ trong Excel như Định dạng có điều kiện, Xác thực dữ liệu, Bộ lọc, Bảng tổng hợp, v.v.. rất cần thiết trong công việc hàng ngày trên Excel.
Hiện hệ thống đang có nhiều ưu đãi hấp dẫn khi đăng ký khóa học này. Tham gia ngay bây giờ để tận dụng ưu đãi này. Xem chi tiết tại:
Nguồn : Blog hocexcel