Hướng dẫn cách dùng hàm OFFSET để tạo danh sách động trong Excel

Hàm OFFSET là một trong những hàm có nhiều tính năng rất thú vị. Một trong những tính năng đó là chúng ta có thể dùng hàm OFFSET tạo ra danh sách động trong Excel. Trong bài viết này, bạn hãy cùng Gitiho.com tìm hiểu cách tạo danh sách động trong Excel với hàm OFFSET nhé.

Hướng dẫn cách dùng hàm OFFSET để tạo danh sách động trong Excel

Hàm OFFSET tạo danh sách không có dòng trống thừa ở cuối trong Excel

Ví dụ bạn có 1 danh sách nhân viên, bạn dự tính danh sách đó sẽ gồm tối đa là 20 người. Thế nhưng tại thời điểm tháng 1, bạn mới chỉ có 5 người trong danh sách, còn lại 15 dòng trống. Và trong những tháng tiếp theo thì số người có thể tăng thêm, giảm đi.

Vậy làm cách nào để danh sách nhân viên đó tự động thay đổi theo số nhân viên hiện có, và những dòng trống sẽ bị loại ra khỏi danh sách?

Hình 1: danh sách nhân viên khi có 5 người

Hình 2: danh sách tự động mở rộng khi có thêm 2 người

Để danh sách có thể tự thay đổi được, chúng ta phải sử dụng hàm OFFSET như sau:

Tìm điểm bắt đầu

Danh sách bắt đầu từ ô B3, vì vậy trong hàm OFFSET chúng ta có thể:

• Bắt đầu với tham chiếu B3, không thay đổi về dòng, cột
• Bắt đầu với tham chiếu B2, xuống 1 dòng, không đổi về cột
• Bắt đầu với tham chiếu A2, xuống 1 dòng, sang phải 1 cột

Việc chọn vị trí bắt đầu chỉ đơn giản là căn cứ để chúng ta tìm được điểm đầu của danh sách. Tùy theo vị trí tham chiếu mà chúng ta có thực hiện di chuyển số dòng, số cột không. Nếu có thì di chuyển bao nhiêu dòng, bao nhiêu cột.

Ví dụ công thức bắt đầu với ô B3

=OFFSET(B3,0,0,độ rộng theo sốdòng, độ rộng theo số cột)

Xác định độ rộng của danh sách

Tiếp đến là xác định danh sách đó có bao nhiêu ô. Để biết điều này, chúng ta dùng hàm COUNTA. Hàm COUNTA có tác dụng đếm những ô có chứa nội dung (không phải ô trống)

Chúng ta đếm trong cột B, từ dòng 3 tới dòng 22 (tương ứng với tối đa 20 nhân viên trong danh sách):

=COUNTA(B3:B20)

• Với 5 nhân viên, kết quả hàm COUNTA = 5
• Với 7 nhân viên, kết quả hàm COUNTA=7

Khi đặt giá trị này vào tham số Weight của hàm OFFSET, chúng ta sẽ có độ rộng của danh sách là 5 hay 7 ô, tính từ ô B3.

=OFFST(B3,0,0,COUNTA(B3:B22))

Không cần nhập tham số độ rộng theo số cột, vì ở đây chúng ta chỉ áp dụng trên 1 cột B

Tạo danh sách chọn với Data Validation

Sau khi đã viết đúng được hàm OFFSET thì chúng ta chỉ việc copy công thức đó để đưa vào trong mục tạo danh sách chọn trong Data Validation:

Tạo danh sách phụ thuộc nhau, danh sách theo nhóm với hàm OFFSET trong Excel

Ví dụ về danh sách phụ thuộc nhau, danh sách theo nhóm như sau:

Trong danh sách nhân viên ở trên, chúng ta có thêm cột Bộ phận. Tại đó chia làm 2 bộ phận là Kinh doanh và Kế toán. Khi chọn bộ phận Kinh doanh, chúng ta sẽ có 1 danh sách chỉ có những nhân viên thuộc bộ phận kinh doanh:

Còn khi chọn bộ phận Kế toán, danh sách nhân viên sẽ thay đổi chỉ có nhân viên thuộc bộ phận kế toán:

Để làm được việc này, chúng ta sẽ sử dụng hàm OFFSET như sau:

Xác định điểm bắt đầu làm tham chiếu gốc

Bởi vì bộ phận có thể thay đổi, nên chúng ta sẽ xác định điểm bắt đầu là 1 vị trí cố định. Ví dụ như ô B2

=OFFSET(B2, ….)

Xác định hướng thay đổi điểm bắt đầu theo dòng

Tùy vào bộ phận được chọn ở ô F2 mà chúng ta sẽ xác định từ vị trí B2 sẽ di chuyển bao nhiêu dòng. Việc này xác định một cách khá đơn giản với hàm MATCH như sau:

=MATCH(F2,B3:B22,0)

Dò tìm giá trị F2 ở trong vùng B3:B22. Kết quả sẽ là vị trí dòng thứ mấy trong vùng B3:B22 ứng với giá trị tại F2, nếu có nhiều hơn 1 giá trị đúng thì sẽ trả về vị trí của giá trị đầu tiên.

• Nếu chọn Kinh doanh, ta sẽ có vị trí đầu tiên tìm được là ô B3, ứng với dòng 1 => kết quả là 1
• Nếu chọn Kế toán, ta sẽ có vị trí đầu tiên tìm được là ô B6, ứng với dòng 4 => kết quả là 4

=OFFSET(B2,MATCH(F2,B3:B22,0), ….)

Xác định hướng thay đổi điểm bắt đầu theo cột

Do danh sách cần tìm là Họ tên nhân viên, cách vị trí ô B2 là 1 cột, nên sẽ thay đổi 1 cột. Chúng ta chọn số 1

=OFFSET(B2,MATCH(F2,B3:B22,0),1, …)

Xác định độ rộng theo dòng

Để xác định độ rộng theo dòng, chúng ta cần đếm xem có bao nhiêu dòng trong cột Bộ phận có giá trị giống với Bộ phận được chọn ở ô F2. Đây là việc đếm theo điều kiện, do đó chúng ta dùng hàm COUNTIF như sau:

=COUNTIF(B3:B22,F2)

• Nếu chọn Kinh doanh, hàm COUNTIF đếm ra 3 kết quả => Tương ứng độ rộng của vùng tạo bởi hàm OFFSET có 3 dòng, tính từ dòng 3 đến dòng 5
• Nếu chọn Kế toán, hàm COUNTIF đếm ra 4 kết quả => Tương ứng độ rộng của vùng tạo bởi hàm OFFSET có 4 dòng, tính từ dòng 6 đến dòng 9

=OFFSET(B2,MATCH(F2,B3:B22,0),1,COUNTIF(B3:B22,F2), …)

Xác định độ rộng theo cột

Vì danh sách này chúng ta chỉ lấy trên 1 cột, do đó không cần tới điều chỉnh về độ rộng số cột. Có thể không cần nhập tham số này. Chúng ta kết thúc hàm OFFSET với nội dung

=OFFSET(B2,MATCH(F2,B3:B22,0),1,COUNTIF(B3:B22,F2))

Tạo danh sách chọn với Data Validation

Cuối cùng chúng ta chỉ việc copy công thức trên và dán vào danh sách chọn Data Validation cho ô F3

Để cho chắc chắn dữ liệu không bị thay đổi, khi đưa công thức vào Data Validation thì chúng ta nên cố định lại tọa độ dữ liệu như sau:

=OFFSET($B$2,MATCH($F$2,$B$3:$B$22,0),1,COUNTIF($B$3:B$22,$F$2))

Kết luận

Như vậy là chúng ta đã có thể nắm được các áp dụng hàm OFFSET vào việc tạo danh sách động trong Excel rồi phải không nào. Chỉ với một số hàm cơ bản trong Excel như hàm MATCH, COUNTA, COUNTIF khi kết hợp với hàm OFFSET đã tạo ra những hiệu quả rất ấn tượng. Các bạn hãy tìm hiểu kỹ hơn về các hàm trong Excel để có thêm nhiều kiến thức bổ ích hơn nữa nhé.