Hàm Offset và các kết hợp hàm của nó trong Excel - P2

CHÀO MỪNG BẠN ĐẾN VỚI THƯ VIỆN HỌC TẬP & ĐÀO TẠO CƠ BẢN

Hàm Offset và các kết hợp hàm của nó trong Excel – P2

Excel

Bắt đầu sử dụng Excel

Công thức và hàm

Nhập và phân tích dữ liệu

Định dạng dữ liệu

Xử lý sự cố

Excel 2016

Chúng ta đang thực hiện phần thú vị nhất khi sử dụng hàm Offset trong Excel – áp dụng hàm Offset và các kết hợp hàm của nó trong Excel.

3. Hàm Offset và hàm COUNTA

Được sử dụng cùng với COUNTA, hàm OFFSET có thể giúp bạn tạo một dải động hữu ích trong nhiều tình huống, ví dụ như khi bạn muốn tạo danh sách theo mục có thể tự động cập nhật.

Công thức OFFSET cho một dải động như sau:

= OFFSET (Sheet_Name! $A$1, 0, 0, COUNTA (Sheet_Name! $A:$A), 1)

Trong đó:

  • Reference: được biểu diễn dưới dạng tên bảng và theo sau là dấu chấm than, và địa chỉ của ô có chứa mục đầu tiên có mặt trong trong dải (-đã được đặt tên). Xin lưu ý việc sử dụng các tham chiếu ô tuyệt đối ($), ví dụ Sheet1! $A$1.
  • Các tham số Rows and Cols đều là 0, vì không có các cột hoặc các hàng được thêm vào.
  • Height là điểm mấu chốt ở đây. Tình huống là bạn sử dụng hàm COUNTA để tính toán số ô không rỗng trong cột có chứa các mục của dải được đặt tên. Với đó, hãy lưu ý rằng trong tham số của COUNTA, bạn nên chỉ định tên bảng bên cạnh tên cột, ví dụ: COUNTA (Sheet_Name!$A: $A).
  • Width là 1 cột.

Một khi bạn đã tạo một dải động (đã đặt tên) với hàm Offset trong Excel ở trên, bạn có thể sử dụng Excel Data Validation để tạo danh sách theo mục – luôn tự động cập nhật ngay khi bạn thêm hoặc xoá các mục từ Danh sách nguồn (bảng tính gốc).

Hàm Offset và hàm COUNTA

Hàm Offset và hàm COUNTA

4. Hàm Offset & Vlookup

Các tra cứu đơn giản theo chiều dọc và ngang trong Excel được thực hiện với hàm VLOOKUP hoặc hàm HLOOKUP. Tuy nhiên, các hàm này có quá nhiều hạn chế và thường là rắc rối lớn trong việc tạo công thức tra cứu mạnh hơn và phức tạp hơn. Vì vậy, để thực hiện tra cứu “một cách tinh vi hơn” trong các bảng Excel, bạn phải tìm kiếm các lựa chọn thay thế như hàm INDEX, MATCH và OFFSET.

Công thức Offset cho một Vlookup dò bên trái trong Excel

Một trong những hạn chế đáng kể nhất của hàm Excel VLOOKUP là không có khả năng dò được giá trị bên trái của nó, có nghĩa là VLOOKUP chỉ có thể trả lại một giá trị ở bên phải cột tra cứu.

Trong bảng tra cứu mẫu của chúng tôi, có hai cột – tên tháng (month) (cột A) và tiền thưởng (Bonus) (cột B). Nếu bạn muốn nhận được tiền thưởng cho một tháng nhất định, công thức VLOOKUP đơn giản này sẽ hoạt động mà không gặp trở ngại:

= VLOOKUP (B1, A5: B11, 2, FALSE)

Tuy nhiên, ngay khi bạn tráo đổi các cột trong bảng tra cứu, điều này sẽ ngay lập tức dẫn đến lỗi #N/A:

Để xử lý một tra cứu bên trái, bạn cần một hàm linh hoạt hơn mà không thực sự quan tâm đến vị trí của cột đó ở đâu. Một trong những giải pháp có thể sử dụng chính là kết hợp các hàm INDEX và MATCH. Một cách làm khác chính là sử dụng OFFSET, MATCH và ROWS:

=OFFSET(lookup table, MATCH(lookup value, OFFSET(lookup table, 0, 1, ROWS(lookup table), 1) ,0) -1, 0, 1, 1)

Trong ví dụ của chúng tôi, bảng tra cứu là A5: B9 và giá trị tra cứu nằm trong ô B1, vì vậy công thức trên sẽ triển khai thành:

= OFFSET (A5: B9, MATCH (B1, OFFSET (A5: B9, 0, 1, ROWS (A5: B9), 1), 0) -1, 0, 1, 1)

Công thức trông hơi vụng về, nhưng nó thực sự được việc.

Bạn có thể thành thạo ngay kỹ năng tin học văn phòng từ giảng viên FPT- Arena chỉ với 280,000Đ