Cách sử dụng hàm OFFSET trong Excel

Trong bài viết này, chúng ta sẽ cùng nhau tìm hiểu Cách sử dụng hàm OFFSET trong Excel.

Hàm OFFSET là gì ?

Hàm offset là hàm truy xuất thông tin. Nếu bạn có một bảng lớn chứa dữ liệu nhất định và bạn muốn truy xuất dữ liệu từ bảng đó, cách tốt nhất để thực hiện là chèn một công thức offset cho bảng trong trang tính đó hoặc trang tính khác trong cùng một sổ làm việc. Vì vậy, khi bạn đã tạo bảng truy xuất bù trừ, bạn chỉ cần nhập dữ liệu, chẳng hạn như ‘tháng’ và bạn sẽ nhận được ‘doanh thu’ hoặc ‘thu nhập’ của tháng đó từ bảng đó.

Xem qua bảng là một tùy chọn khác, nhưng bạn có cân nhắc tùy chọn này không nếu bạn có các trang tính và trang tính dữ liệu đều chứa bảng dữ liệu 1000 cột? Đây là nơi offset được kết hợp với các chức năng khác.

Khái niệm về bảng tổng hợp xuất phát từ phần bù. Bảng tổng hợp là một bảng chung và từ sự kết hợp dữ liệu và biểu đồ cụ thể đó được thu được khi cần thiết. Các bảng động cũng có thể được tạo theo cách tương tự. Đối với các bảng này, bạn sẽ phải tạo công thức offset excel

Cú pháp của OFFSET

=OFFSET(tham chiếu, hàng, cột, [chiều cao], [chiều rộng])

Hàng – bạn yêu cầu excel di chuyển số hàng để lấy thông tin. Trong trường hợp này, nó phải di chuyển xung quanh bảng và vì vậy chỉ cần để trống được biểu thị bằng dấu phẩy (,). Mặt khác, để di chuyển một cột về phía trước, hãy đặt giá trị là 1 và để di chuyển cột trước, hãy đặt giá trị -1.

Cột – điều này là để điều khiển chức năng đến số lượng cột. Hệ thống giá trị giống như hệ thống cho hàng.

Chiều cao – chiều cao của bảng, trong trường hợp này là A11.

Chiều rộng – chiều rộng của bảng, trong trường hợp này là D11.

Sau khi bạn thiết lập chức năng offset, việc chạy thử là rất quan trọng để loại bỏ bất kỳ lỗi nào.

Hãy để chúng tôi lấy một bảng chứa Tên, id email, ngày sinh và tuổi. Bảng bắt đầu tại ô A1, chứa tiêu đề ‘Tên’. Dữ liệu chạy cho A11. Và các hàng chạy đến D1. Toàn bộ bảng chạy A1:D11. Bạn muốn một hệ thống truy xuất lấy tên cho bạn khi bạn nhập id email hoặc id email khi bạn nhập tên cùng với các chi tiết khác. Bạn tạo một công thức bằng cách đáp ứng 5 đối số.

Điểm tham chiếu là ô mà việc tra cứu sẽ bắt đầu từ đó. Trong trường hợp này, nó phải là A2. Đó là tiêu chuẩn chung.

Ví dụ:

Ví dụ: bạn cung cấp tham chiếu của B4 và hàng bù 0 và cột bù 1 thì giá trị từ C4 sẽ được trả về. Gây nhầm lẫn? Hãy có thêm một số ví dụ. Ví dụ cuối cùng về hàm OFFSET cho biết cách SUM động.

Hàm OFFSET để lấy giá trị từ bên phải và bên trái của một ô

Chúng tôi có bảng này.

Bây giờ nếu tôi muốn lấy giá trị từ 2 ô bên phải đến B2 (có nghĩa là D2). Tôi sẽ viết công thức OFFSET này:

=OFFSET(B2,0,2)

Hàm OFFSET sẽ di chuyển 2 ô sang bên phải của ô B4 và trả về giá trị của nó. Xem hình ảnh bên dưới

Nếu tôi muốn lấy giá trị từ 1 ô còn lại đến B2 (có nghĩa là A2). Tôi sẽ viết công thức OFFSET này:

=OFFSET(B2,0,-1)

Dấu trừ (-) biểu thị độ lệch để di chuyển ngược lại.

Hàm OFFSET để lấy giá trị từ bên dưới và bên trên ô

Vì vậy, một lần nữa trong bảng trên, nếu tôi muốn lấy giá trị từ 2 ô yêu thích đến B3 (có nghĩa là B5). Tôi sẽ viết công thức OFFSET này:

=OFFSET(B2,2,0)

Nếu tôi muốn lấy giá trị từ 1 ô còn lại đến B2 (có nghĩa là A2). Tôi sẽ viết công thức OFFSET này:

=OFFSET(B2,-2,0)

• Mẹo chuyên nghiệp: Hãy coi OFFSET là con trỏ biểu đồ trong đó Tham chiếu là gốc và hàng và cột là trục x và y.

Chức năng OFFSET để tính tổng phạm vi động

Hãy xem ví dụ này.

Trong bảng trên Tổng số hàng ở cuối bảng. Theo thời gian, bạn sẽ thêm hàng vào bảng này. Sau đó, bạn sẽ cần thay đổi phạm vi tổng trong công thức. Ở đây chúng ta có thể nhờ sự trợ giúp của hàm OFFSET để tính tổng động. Hiện tại trong ô C11 chúng ta có =SUM(C2:C10). Thay thế điều này bằng công thức dưới đây.

=SUM(C2:OFFSET(C11,-1,0))

Công thức này chỉ lấy hàng dữ liệu đầu tiên và sau đó tính tổng phạm vi trên hàng tổng.

Offset để lấy mảng

Hàm OFFSET có hai đối số tùy chọn, [chiều cao] và [chiều rộng]. Đừng lấy chúng làm bằng cấp. Khi hàm OFFSET được cung cấp các đối số [chiều cao] và [chiều rộng], nó sẽ trả về một mảng hai chiều. Nếu chỉ một trong số chúng được truyền dưới dạng đối số thì nó sẽ trả về mảng giá trị thứ nguyên.

Nếu bạn gõ công thức này vào bất kỳ ô nào:

=SUM(OFFSET(C1,1,0,9,3))

Nó sẽ tính tổng các giá trị trong phạm vi bắt đầu từ C2 đến 9 hàng bên dưới và 3 cột bên phải.

OFFSET(C1,1,0,9,3) :  Điều này sẽ chuyển thành {8,8,7;6,1,2;8,5,8;5,1,5;8,3,5;8, 3,9;8,9,2;3,5,4;6,6,5}.

Tóm lại, offset là hàm rất hữu ích của excel có thể giúp bạn giải quyết nhiều vấn đề bị bóp nghẹt. Hãy cho tôi biết cách bạn sử dụng hàm OFFSET trong công thức của mình và nó giúp bạn nhiều nhất ở đâu.

Hàm tra cứu giống như hàm offset nhưng sử dụng các hàm như Match, Counta và IF để làm việc với bảng động.

Sử dụng phần bù trong Excel đòi hỏi kỹ năng và kiến ​​thức tốt về các hàm của Excel cũng như cách chúng phối hợp với nhau. Kéo nó ra mà không có bất kỳ lỗi nào là trở ngại lớn nhất.

Dưới đây là tất cả các ghi chú quan sát liên quan đến việc sử dụng công thức.

Ghi chú:

1. Công thức này hoạt động với cả văn bản và số.
2. Có năm đối số của phần bù cần được thỏa mãn để có được thông tin không có lỗi. Nếu bạn viết sai công thức, lỗi Tham chiếu sẽ xảy ra.