Hàm Offset trong Excel là gì và Cách sử dụng nó

Posted on: 2020年11月12日 2020年11月13日
Tags: tips

Chức năng OFFSET là gì?

Chức năng bù đắp là một chức năng truy xuất thông tin. Nếu bạn có một bảng lớn chứa một số dữ liệu nhất định và bạn muốn truy xuất dữ liệu từ nó, cách tốt nhất để làm điều đó là chèn một công thức bù trừ cho bảng trong trang tính đó hoặc một trang tính khác trong cùng một sổ làm việc. Do đó, khi bạn đã tạo bảng truy xuất bù đắp, bạn chỉ cần nhập dữ liệu, ví dụ: ‘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 nếu bạn có các trang tính và trang dữ liệu đều chứa bảng dữ liệu 1000 cột không? Đây là nơi bù đắp được kết hợp với các chức năng khác.

Khái niệm bảng tổng hợp xuất phát từ bù đắp. Bảng tổng hợp là một bảng tổng quát và từ đó kết hợp dữ liệu và đồ thị cụ thể sẽ thu được khi có yêu cầu. Cũng có thể tạo bảng động 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 trong excel

Cú pháp của OFFSET

=OFFSET(reference,rows, columns, [height], [width])

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

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

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

Width – 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 bù đắp, việc chạy thử là rất quan trọng để loại bỏ bất kỳ lỗi nào.

Chúng ta hãy lấy một bảng chứa Tên, id email, ngày sinh và tuổi.

Bảng bắt đầu từ ô A1, chứa tiêu đề ‘Tên’. Dữ liệu chạy cho giả sử A11. Và các hàng chạy cho đế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 công thức bằng cách thỏa mãn 5 đối số.

Điểm tham chiếu là ô mà từ đó bắt đầu tra cứu. 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? Cho phép 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.

Chức năng OFFSET để lấy giá trị từ bên phải và bên trái của một ô Chúng ta có bảng này.

Bây giờ nếu tôi muốn nhận 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 phải ô 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 ô sang trái B2 (nghĩa là A2). Tôi sẽ viết công thức OFFSET này:

=OFFSET(B2,0,-1)

Dấu trừ (-) cho biết độ 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 của một ô 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 ô belove đế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 ô sang trái 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: * Coi OFFSET như con trỏ đồ thị trong đó Tham chiếu là điểm gốc và hàng và cột là trục x và y.

Hàm OFFSET để tính tổng phạm vi động Cho phép 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 các 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ể sử dụng 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 khoảng trên tổng hàng.

Offset For Get Array 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ó 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ố, nó sẽ trả về mảng giá trị theo chiều.

Nếu bạn nhập 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ẽ dịch 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}.

Vì vậy, kết luận, offset là một chức năng rất hữu ích của excel có thể giúp bạn giải quyết nhiều chủ đề khó hiểu. 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 ích cho bạn ở đâu.

Chức năng tra cứu giống như offset nhưng sử dụng các chức năng như Match, Counta và IF để làm việc một bảng động.

Sử dụng offset trong Excel đòi hỏi kỹ năng và kiến thức tốt về các hàm Excels và cách chúng hoạt động cùng 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 lưu ý quan sát liên quan đến việc sử dụng công thức.

Ghi chú:

Công thức này hoạt động với cả văn bản và số.
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.

Hy vọng bạn đã hiểu Hàm Offset trong Excel là gì và Cách sử dụng nó trong Excel. Khám phá thêm các bài viết về tra cứu Excel và so khớp các giá trị bằng công thức tại đây. Nếu bạn thích blog của chúng tôi, hãy chia sẻ nó với bạn bè của bạn trên Facebook. Và bạn cũng có thể theo dõi chúng tôi trên Twitter và Facebook. Chúng tôi rất muốn nghe ý kiến của bạn, hãy cho chúng tôi biết cách chúng tôi có thể cải thiện, bổ sung hoặc đổi mới công việc của mình và làm cho nó tốt hơn cho bạn. Viết thư cho chúng tôi tại [email protected].