Logic của hàm OFFSET

Euler

Mod
Thành viên BQT
Hàm OFFSET là một trong những hàm khó hiểu. Tuy nhiên, nếu hiểu được logic của nó, chúng ta sẽ thấy nó là hàm có ích đấy.
Đầu tiên, chúng ta cùng nhau lý giải hàm OFFSET là nó làm cái gì nhé?

Cấu trúc code:
OFFSET(Gốc tọa độ, số dòng, số cột, chiều cao, chiều rộng)
Chúng ta có thể phân loại các tham số của hàm OFFSET làm 3 loại.
1. Tham số chỉ định cell làm gốc -> Ta gọi là Gốc tọa độ.
2. Tham số chỉ định vị trí di chuyển từ cell gốc -> [số dòng], [số cột]
3. Tham số chỉ định độ lớn của vùng kết quả -> [chiều cao], [chiều rộng]
Tham số [Gốc tọa độ] chỉ ra địa chỉ của cell nào làm mốc.
Cái này không khó phải không nào. Ví dụ bạn hỏi đường, người ta nói bắt đầu từ nhà số 32, rẽ trái 3 nhà nữa, rồi rẽ phải 2 nhà nữa...

Đầu tiên, tôi sẽ giải thích về cách sử dụng [số dòng], [số cột] để di chuyển từ cell gốc.
Hãy nhìn vào hình vẽ dưới.
Bạn cần đăng nhập để thấy đính kèm


Trong vùng Range A1:C5 tôi nhập các số từ 1 đến 15.
Hàm OFFSET ở trên tôi lấy cell A1 làm gốc.

Tại ô E2 tôi nhập công thức = OFFSET(A1,2,1,1,1).
số dòng = 2, số cột = 1 có nghĩa là từ cell gốc A1 tôi di chuyển xuống dưới 2 dòng và di chuyển sang phải 1 cột, tôi sẽ được vị trí cell mà tôi muốn tham chiếu tới, cụ thể là ô B3. Đây chính là ý nghĩa của nó.

Về tham số chỉ định [chiều cao], [chiều rộng]: cả hai tham số này tôi đã chỉ định đều là 1, Có nghĩa là cell gốc lấy độ lớn là :
1 dòng x 1 cột. Đó chính là ý nghĩa của nó. Sau đây tôi sẽ giải thích kỹ hơn.

Đến đây, các bạn đã hiểu chưa ạ? Tiếp theo, tôi sẽ giải thích rõ hơn về [chiều cao], [chiều rộng] mà ta chỉ định độ lớn cho cell gốc.
Đầu tiên các bạn hãy hiểu rằng 1 dòng x 1 cột tức là 1 cell bình thường.
Ở hình vẽ trên bạn thấy rằng chúng ta có vùng Range A1:C5 tức là 5 dòng x 3 cột.
Giống như vậy, [chiều cao] x [chiều rộng] chính là biểu thị một vùng Range có số dòng = chiều cao, số cột = chiều rộng.
Như vậy hai tham số [chiều cao], [chiều rộng] chính là chỉ định độ lớn cho một vùng Range.

Ta hãy xem ví dụ sau:
Bạn cần đăng nhập để thấy đính kèm


Ở hình trên, tôi đã nhập công thức vào ô E2 như sau: OFFSET(A1,2,1,3,2). So với công thức ban nãy, thì cell gốc, số dòng, số cột không hề thay đổi.
OFFSET(A1,2,1,1,1) là từ cell gốc A1 đi xuống dưới 2 dòng, đi sang phải 1 cột ta được kết quả là ô B3.
Bây giờ ta phóng to kích thước ô B3 lên là 3 dòng (chiều cao = 3), và 2 cột (chiều rộng = 2) ta sẽ được kết quả là vùng Range B3:C5 được tô màu xanh ở trên. Khác với kết quả ở ví dụ ban đầu, bây giờ kết quả của OFFSET là một vùng Range, do đó hàm SUM sẽ cho kết quả là tổng các số trong vùng Range đó = 8+9+10+13+14+15 = 69.

Tuy nhiên cách chúng ta hay dùng là chỉ định cell gốc, và số dòng, số cột di chuyển. Chúng ta thường lược bỏ tham số chiều caochiều rộng. Tất nhiên thì chỉ định cũng không sao.
Bạn cần đăng nhập để thấy đính kèm

Như hình minh họa trên, tôi chỉ định cell gốc là A1, số dòng = 3, số cột = 0, tôi không chỉ định độ lớn chiều caochiều rộng.
Ta có kết quả: A1 di chuyển xuống dưới 3 dòng, và không di chuyển cột nào, ta có kết quả là ô A4, vậy công thức ở G1 sẽ cho kết quả = A4 = 2.

Bây giờ câu hỏi các bạn quan tâm là OFFSET ứng dụng vào việc gì?
Ta có ví dụ như sau:
Bạn cần đăng nhập để thấy đính kèm


Ở trên, ta có list data là vùng Range A1:C5 phải không nào? Tôi nhập số hiệu vào ô F2, và ở ô F3 tôi thực hiện tìm kiếm bằng VLOOKUP để cho hiển thị tên người đảm nhiệm.
Việc trên chẳng phải là khó, chúng ta vẫn thường gặp đấy hay sao. Ấy thế nhưng mà vấn đề là list data của ta ngày càng nhiều hơn thì sẽ như thế nào đây?
Ngày hôm nay dữ liệu là A2:C5 và công thức của các bạn là =VLOOKUP(F2,A2:C5,2), mọi thứ đều suôn sẻ. Nhưng ngày mai, dữ liệu nhiều hơn, công thức phải sửa lại là = VLOOKUP(F2,A2:C7,2).

Giống như câu chuyện trên, list dữ liệu có thể thay đổi độ lớn, và chúng ta có thể chỉ định địa chỉ bằng hàm OFFSET.
Dù mai mốt data có nhiều hơn, chúng ta cũng không phải thay đổi công thức.
Tóm lại là:
=VLOOKUP(F2,Độ lớn dữ liệu bây giờ,2)

=VLOOKUP(F2,Độ lớn dữ liệu bây giờ× chiều rộng,2) //chiều rộng: số cột

=VLOOKUP(F2,Số dòng của vùng dữ liệu được sử dụng× chiều rộng,2) // số dòng x số cột

Cell gốc của chúng ta là ô A2. Cell gốc sẽ không di chuyển gì cả, mặc định số dòng = số cột = 0.
Bây giờ phạm vi được mở rộng A2:C5 tức là tham số OFFSET là: [chiều cao] = số dòng = 4. [chiều rộng] = số cột = 3.
Chiều cao (số dòng) nếu mà được chỉ định thì coi như là xong. Giả thiết data của ta có số cột cố định, chỉ phát sinh thêm số dòng.
Vậy thì ta có thể sử dụng hàm COUNTA. Đây chính là mấu chốt của vấn đề.
Để đếm cột A có dữ liệu tới đâu ta dùng COUNTA(A:A) là xong. Tuy nhiên chú ý dòng 1 là dòng tiêu đề, không sử dụng vào việc tính toán. Cho nên ta phải lấy kết quả đó trừ đi 1.
=VLOOKUP(F2,OFFSET(A2,0,0,Số dòng của vùng dữ liệu được sử dụng,3),2)

=VLOOKUP(F2,OFFSET(A2,0,0,Số lượng data có trên cột A -1 ,3),2)

=VLOOKUP(F2,OFFSET(A2,0,0,COUNTA(A:A)-1,3),2)

Giờ thì nhìn vào ô F3, công thức đã được thay đổi:
Bạn cần đăng nhập để thấy đính kèm


Bài viết được tham khảo và dịch từ
 
T

thanhphuongvip

Guest
=VLOOKUP(F2,OFFSET(A2,0,0,Số dòng của vùng dữ liệu được sử dụng,3),2)

=VLOOKUP(F2,OFFSET(A2,0,0,Số lượng data có trên cột A -1 ,3),2)

=VLOOKUP(F2,OFFSET(A2,0,0,COUNTA(A:A)-1,3),2)
Một lưu ý khi sử dụng phương pháp CountA là dữ liệu ở bảng dò phải liên tục không được có dòng trống thì dùng countA mới chính xác, nếu có chèn dòng trống ở giữa thì Bảng tra sẽ bị thiếu, giống ví dụ bên dưới, bảng tra người ta thêm vào dòng trống vì một lý do nào đó, ở đây là phân theo nhóm hàng cho dễ nhìn:

Bạn cần đăng nhập để thấy hình ảnh


Trong trường này mình sẽ cộng thêm vài trăm dòng trừ hao để lúc người ta chèn vào dòng trống, ví dụ như mình trừ hao khoảng 300 dòng thì mình sẽ làm công thức thành:

=VLOOKUP(F2,OFFSET(A2,0,0,COUNTA(A:A)+300,3),2)

Với Excel thêm 300 dòng sẽ ko hao tổn tài nguyên bao nhiêu nên cách này chấp nhận được!
 

ndhuu

Thành viên mới
bài viết rất bổ ích và dễ hiểu,cảm ơn anh chị em
 
Top