Excel – cái tên không còn quá xa lạ đối với cuộc sống của chúng ta nữa rồi, khi mà từ công việc, học hành hằng ngày chúng ta đều phải sử dụng nó. Vậy bạn đã hiểu rõ hết tần tật về Excel chưa nhỉ? Hôm nay, chúng ta sẽ cùng nhau bàn luận về hàm OFFSET là gì, hàm OFFSET trong Excel là gì và nó có ứng dụng, lợi ích như thế nào nhé!
Hàm OFFSET là gì?
Hàm OFFSET là hàm được sử dụng để xem, tính toán hay phân tích dữ liệu của một ô hay một dãy trong bảng tính Excel dựa vào một vùng tham chiếu đã có sẵn trước đó.
Hàm OFFSET được ứng dụng trong Excel để:
- Nhanh chóng và thuận lợi hơn khi tính toán, phân tích dữ liệu của một ô hay một dãy trong bảng tính.
- Một công dụng khác nữa đó là, kết hợp hàm OFFSET với các hàm khác trong Excel để hỗ trợ hoàn thành công việc nhanh hơn.
- Bạn sẽ trở nên chuyên nghiệp hơn khi làm các công việc liên quan đến hàm Excel, là cơ hội giúp bạn thăng tiến trong công việc, một điểm cộng trong mắt sếp.
Cú pháp của hàm OFFSET
OFFSET(reference, rows, cols, [height], [width])
Trong đó:
- Reference: đối số bắt buộc, là vùng tham chiếu làm cơ sở cho hàm tạo vùng tham chiếu mới.
- Rows: đối số bắt buộc, là số dòng bên trên hoặc bên dưới reference, tính từ ô đầu tiên (góc bên trái) của reference. Rows có thể số âm, có số thể dương, nếu số âm thì các dòng trả về nằm bên trên reference, nếu số dương thì các dòng trả về nằm bên dưới reference.
- Cols: đối số bắt buộc, là số cột bên trái hoặc bên phải reference, tính từ ô đầu tiên (góc bên trái) của reference. Cols có thể là số âm, có thể là số dương, nếu số âm thì các cột trả về nằm bên trái reference, nếu số dương thì các cột nằm bên phải reference.
- [height]: là đối số tùy chọn biểu thị cho số dòng cần trả về của vùng tham chiếu.
- [width]: là đối số tùy chọn biểu thị cho số cột cần trả về của vùng tham chiếu.
>>> Tham khảo thêm: Hàm Offset trong Excel
Các lưu ý khi sử dụng hàm OFFSET
- Khi báo lỗi #REF thì các đối số Rows và Cols đã làm cho vùng tham chiếu trả về vượt khỏi phạm vi của trang tính.
- Hai đố số height và width thì luôn phải là số dương.
- Nếu bỏ qua hai đối số height và width thì tham chiếu trả về mặc định có cùng chiều cao và độ rộng với vùng tham chiếu.
- Nếu các đối số height và width >1 thì có nghĩa là hàm phải trả về nhiều hơn 1 ô, để không nhận lỗi #VALUE thì các bạn phải sử dụng công thức mảng hàm OFFSET.
- Hàm OFFSET chỉ trả về một tham chiếu nên sẽ không di chuyển hay làm thay đổi bất kỳ phần nào được chọn. Chúng ta có thể sử dụng hàm OFFSET kết hợp với các hàm cần đến một đối số tham chiếu.
Ví dụ cơ bản của hàm OFFSET
Nói sơ qua lý thuyết về hàm OFFSET là gì nghe có vẻ trừu tượng, dưới đây là ví dụ cơ bản minh họa. Cho quá trình sử dụng hàm OFFSET để các bạn hình dung rõ hơn. Cách thực hiện hầu như là giống nhau trên các phiên bản Excel 2007, 2010, 2013, 2016, 2019 và MacOS.
Đề bài: Hãy sử dụng hàm OFFSET để tính giá trị ô ở vị trí cột 2, dòng 3 trong bảng dữ liệu sau:
- Bước 1: Trong bảng tính Excel, bạn nhập hàm =OFFSET(B3,3,2). Vào tham chiếu mà chúng ta muốn hiển thị kết quả.
- Bước 2: Nhấn phím Enter và xem kết quả cần tìm.
Sự kết hợp của hàm OFFSET với các hàm khác trong Excel
Ngoài sử dụng để tính độc lập, thì lựa chọn kết hợp hàm OFFSET là gì với hàm khác trong Excel. Cũng tạo ra các phép tính mới sẽ giúp giải quyết các vấn đề nhanh chóng và tiện lợi. Ví dụ như kết hợp với hàm SUM, AVERAGE, MIN, MAX,..
Kết hợp OFFSET với hàm SUM để tính tổng phạm vi được trả về
Ví dụ 1: Tính tổng tiền hàng của tất cả sản phẩm theo dữ liệu sau đây.
Chúng ta có thể tham chiếu bắt đầu từ ô A1 sau đó phải di chuyển xuống 1 hàng, sang phải 5 hàng. Sau đó lấy 10 dòng và một cột.
Từ đó kết hợp lại ta có công thức: =SUM(OFFSET(A1;1;5;10;1)).
Hoặc cách khác, tham chiếu bắt đầu từ ô C3. Sau đó di chuyển lên 1 hàng và sang phải 3 dòng, sau đó 10 dòng và 1 cột.
Suy ra ta có công thức : =SUM(OFFSET(C3;-1;3;10;1)). Và kết quả thu được là như nhau.
Kết hợp hàm OFFSET với hàm AVERAGE để tính giá trị trung bình của hàm.
Ví dụ 2: Hãy tính giá trị trung bình của bảng dữ liệu sau.
Giá trị trung bình của bảng là, tương tự với cách tính tiền thưởng cho n tháng, chúng ta cũng có thể áp dụng để tính giá trị trung bình cho n tuần, n ngày hay n năm.
Ta có công thức là: =AVERAGE(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).
Nhấn Enter và nhận kết quả.
Kết hợp với hàm MAX để tìm số lớn nhất
Ví dụ 3: Tìm giá trị lớn nhất trong bảng tính:
Giá trị lớn nhất là:
Ta có công thức: =MAX(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).
Nhấn Enter và nhận được kết quả.
Kết hợp với hàm MIN để tìm số nhỏ nhất
Ví dụ 4: Tìm giá trị nhỏ nhất trong bảng tính:
Giá trị nhỏ nhất là:
Ta có công thức: MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).
Nhấn Enter và nhận được kết quả.
Hàm OFFSET kết hợp với hàm VLOOKUP để tra cứu thông tin
Hàm VLOOKUP trong Excel có một hạn chế đó là chỉ có khả năng trả lại một giá trị nhất định. Ở bên phải cột tra cứu nên ảnh hưởng rất nhiều đến tiến độ làm việc. Vì thế có thể sử dụng hàm OFFSET kết hợp hàm VLOOKUP để tra cứu dữ liệu bên trái trong file Excel.
Ví dụ 5: Dùng hàm OFFSET kết hợp với hàm VLOOKUP để hiển thị giá trị lương tháng 3 trong bảng sau:
Ta có công thức: =VLOOKUP(B1,A5:B11,2,FALSE).
Nhấn Enter và nhận kết quả cần tìm.
Trên đây là một số ứng dụng mà hàm OFFSET mang lại. Chúng ta có thể sử dụng hàm OFFSET trong bảng tính để nhận lại kết quả nhanh, chính xác nhất.
Trong cuộc sống hiện nay, hầu như các công việc liên quan đến giấy tờ đều phải dùng đến Excel. Ngay cả khi đang là học sinh các bạn cũng được biết về tầm quan trọng của bảng tính này rồi. Vì thế chúng ta cần phải học các thủ thuật, phím tắt để hoàn thành công việc nhanh chính hơn. Vậy là hôm nay chúng ta đã cùng tìm hiểu hàm OFFSET là gì, hàm OFFSET trong Excel là gì và các ứng dụng cần thiết của hàm. Đừng chần chờ gì nữa mà không lưu lại để khi cần có cái mà dùng.
>>> Tham khảo thêm: Tách chữ trong excel
