Hàm OFFSET vào Excel được sử dụng trong trường hợp mong xem hoặc tính toán, phân tích tài liệu của một ô hay như là 1 dãy trong bảng tính phụ thuộc vào một vùng tham chiếu gồm sẵn trước đó.
Bạn đang xem: Hàm offset trong excel là gì
Cú pháp
=OFFSET(reference, rows, cols, height, width)Trong đó:
reference: là vùng tham chiếu làm đại lý cho hàm (làm điểm xuất phát) để tạo nên vùng tham chiếu mới.rows: là số dòng trên hoặc dưới reference, tính trường đoản cú ô trước tiên (ô ở góc trên mặt trái) của reference. Ví dụ ví như rows là 3, sẽ sở hữu 3 cái trả về và nằm dưới reference. Lúc rows là số dương thì những dòng trả về nằm dưới reference, khi rows là số âm thì những dòng trả về nằm bên trên reference.cols: là số cột phía trái hoặc bên bắt buộc reference, tính từ bỏ ô đầu tiên (ô ở góc cạnh trên mặt trái) của reference. Ví dụ nếu như cols là 4 sẽ sở hữu 4 cột trả về và nằm bên phải của reference. Khi cols là số dương thì những cột trả về nằm sát phải reference, lúc cols là số âm thì những cột trả về nằm bên cạnh trái reference.height: là số chiếc của vùng tham chiếu đề nghị trả về. Height yêu cầu là số dương.width: là số cột của vùng tham chiếu nên trả về. Width cần là số dương.Chú ý:
Nếu rows với cols khiến cho vùng tham chiếu vượt thoát ra khỏi phạm vi của bảng tính thì hàm OFFSET() đã báo lỗi #REF!.Hàm OFFSET() không dịch chuyển hay làm gắng đổi bất kỳ phần như thế nào được chọn bởi vì hàm chỉ trả về một tham chiếu. Các bạn có thể sử dụng hàm OFFSET() kết phù hợp với các hàm nên đến một đối số tham chiếu.Ví dụ:
– tại ô B7, trả về giá trị của ô D5 thông qua ô tham chiếu cửa hàng B4, dùng công thức: =OFFSET(B4;1;2;1;1)
=>Kết quả:
– Kết hợp hàm OFFSET() với hàm SUM() nhằm tính tổng vùng bôi kim cương C3-E5, cùng với vùng tham chiếu là bảng C4-E7 ta cần sử dụng công thức: =SUM(OFFSET(C4:E7;-1;0;3;3))
=> hiệu quả thu được:
Học Word Excel cơ bản
Ngoài ra các bạn cũng có thể tham khảo đoạn clip sau phía trên để hoàn toàn có thể thấy rõ rộng cách áp dụng hàm OFFSET vào Excel:
https://www.facebook.com/photo.php?fbid=10154749371733241&l=32c34cb1a6
https://www.facebook.com/photo.php?fbid=10154749371733241&l=32c34cb1a6
Để có thể ứng dụng tốt Excel vào vào công việc, chúng ta không chỉ nắm vững được những hàm mà lại còn đề nghị sử dụng tốt cả những công nạm của Excel. Phần đông hàm nâng cao giúp áp dụng giỏi vào công việc như SUMIFS, COUNTIFS, SUMPRODUCT, INDEX + MATCH … Những giải pháp thường áp dụng là Data validation, Conditional formatting, Pivot table …
Toàn cỗ những kiến thức này các bạn đều hoàn toàn có thể học được trong khóa học EX101 – Excel tự cơ bạn dạng tới siêng gia của Học Excel Online. Hiện nay hệ thống đang có ưu đãi siêu lớn cho chính mình khi đăng ký tham gia khóa học.
Hàm Offset là trong số những hàm được áp dụng để tính toán hoặc tham chiếu tài liệu của một dãy hay một ô trong bảng tính phụ thuộc vào một vùng tham chiếu đã gồm sẵn. Trong nội dung bài viết này, hãy thuộc emcanbaove.edu.vn tìm hiểu hàm offset vào excel và những áp dụng của hàm offset. Mời chúng ta tham khảo nội dung bài viết dưới phía trên nhé.
Nội dung
1 Hàm OFFSET dùng để gia công gì? 2 Cú pháp cùng một số để ý của hàm OFFSET trong excel?4 Hàm OFFSET hay được kết hợp với hàm nào?Hàm OFFSET dùng để làm gì?
Định nghĩa hàm offset là gì?
Hàm offset vào excel là hàm chất nhận được trả về phạm vi ô hoặc một ô là số lượng cột và số lượng hàng được chỉ định xuất phát điểm từ 1 phạm vi ô hoặc một ô.
Mục đích
Hàm offset excel được thực hiện với mục đích.
Tiết kiệm thời gian cho những người dùng trong việc thống kê giám sát bảng tài liệu của một ô.Nâng cao hiệu suất thao tác làm việc khi phối kết hợp hàm offset với các hàm khác.Giúp bạn trở nên bài bản hơn, biết thực hiện hàm offset dành riêng và các hàm excel không giống nói chung, khiến cho bạn có thời cơ thăng tiến vào công việc.Cú pháp với một số chú ý của hàm OFFSET vào excel?
Cú pháp hàm OFFSET vào excel?
=OFFSET(reference, rows, cols,
Hay: =OFFSET (tham chiếu, hàng, cols, chiều cao, chiều rộng).
Trong đó:
Reference: Được coi là vùng cửa hàng tham chiếu để tạo ra vùng tham chiếu mới.Rows: Số dòng dưới bên bên trên hoặc reference, tính từ bỏ ô đầu tiên của reference ở góc trên bên trái.Cols: Là số cột bên đề nghị hoặc bên trái của reference, tính tự ô trước tiên của reference ở góc trên bên trái.Height: Số lượng loại của vùng tham chiếu cần phải trả về.Width: Số cột của vùng tham chiếu cần được trả về (Width phải luôn luôn là số dương).Lưu ý: 3 đối số đầu tiên là cần phải có, còn 2 cái sau thì không bắt buộc. Tất cả các đối số rất có thể là các tác dụng được trả về bởi những công thức Excel không giống hoặc là những tham chiếu đến những ô khác.
Một số chú ý khi dùng hàm Offset vào excel
Nếu đối số cột với số hàng làm mang lại vùng tham chiếu trả về, vượt vượt ngoài đường biên của trang tính thời điểm này, hàm OFFSET vẫn trả về giá trị lỗi #REF! .Nếu đối số độ rộng cùng chiều cao được bỏ qua, thì nó được giả định có cùng độ rộng và chiều cao với vùng tham chiếu.Nếu những đối số chiều cao với độ rộng >1 tức thị hàm nên trả về nhiều hơn thế nữa 1 ô thì lúc này, bạn phải thực hiện công thức mảng hàm OFFSET còn nếu như không sẽ trả về giá trị lỗi #VALUE!.Hàm OFFSET ko làm đổi khác hay dịch rời hay ngẫu nhiên phần làm sao được chọn cũng chính vì hàm OFFSET chỉ trả về một tham chiếu. Các chúng ta cũng có thể sử dụng hàm OFFSET kết hợp với các một vài hàm khác đề xuất đến một đối số tham chiếu.Ví dụ về kiểu cách dùng hàm offset trong excel
Hướng dẫn được triển khai trên hệ điều hành Windows với phiên bản Excel 2007, 2010, 2013, 2016,2019 bạn có thể thực hiện tương tự như trên hệ quản lý và điều hành Mac
OS.
Ví dụ
Tính lệch giá bán được của nhân viên Lê Thị Ngọc Thảo, phụ thuộc vào bảng dữ liệu đã cho.
Bước 1: dựa vào bảng Excel, họ có công thức như sau =OFFSET(B3,3,2) vào 1 ô tham chiếu mà bạn muốn hiển thị kết quả.
Bước 2: sau khi nhập công thức, chúng ta nhấn phím Enter nhằm hiển thị kết quả.
Hướng dẫn sử dụng hàm offset vào excel
Hàm OFFSET hay được kết phù hợp với hàm nào?
Hàm offset còn rất có thể kết hợp với nhiều hàm khác. Chẳng hạn như hàm Sum, Average, Min, Max, … mục đích để đưa dữ liệu nhưng mà bạn ý muốn muốn.
Hàm offset kết hợp với hàm Sum
Ví dụ: Tính tổng lợi nhuận bán được của nhân viên cấp dưới Lê Cao bởi của cột D theo bảng bên dưới đây.
Bước 1: vào bảng tài liệu tính Excel, chúng ta nhập hàm =SUM(OFFSET(A3,1,3,5,1)) trên ô tham chiếu mà họ muốn hiển thị kết quả.
Hướng dẫn áp dụng hàm offset vào excel
Giải thích:
SUM với OFFSET: Là những lệnh hàm.A3: Ô tham chiếu.1: Số hàng di chuyển xuống, tính bắt đầu từ ô tham chiếu A3.3: Là số cột được dịch rời sang phải, tính bước đầu từ ô tham chiếu A3.5: Là tổng thể hàng để mang dữ liệu tính kết quả.1: Là tổng cộng cột để lấy dữ liệu tính kết quả.Bước 2: sau khoản thời gian nhập công thức, các bạn nhấn phím Enter để hiển thị kết quả.
Hướng dẫn sử dụng hàm offset trong excel
Hàm offset kết phù hợp với hàm Average, Min, Max
Ví dụ: Kết thích hợp hàm Average với offset để tính quý giá trung bình với dữ liệu trong bảng.
Bước 1: tại ô E5, chúng ta nhập hàm =AVERAGE(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).
Hướng dẫn thực hiện hàm offset trong excel
Hướng dẫn sử dụng hàm offset vào excel
Tương tự với cách tính tiền thưởng cho N mon trong bảng tính trên, chúng ta cũng có thể tính quý hiếm trung bình cho N tuần, N ngày hoặc N năm cuối cùng. Cạnh bên đó, các chúng ta cũng có thể tìm các nhỏ nhất hoặc giá chỉ trị lớn nhất lồng ghép hàm offset vào bảng.
Để tìm giá chỉ trị lớn nhất trong bảng: =MAX(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).
Để tìm giá chỉ trị bé dại nhất vào bảng: =MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))
Sử dụng hàm Offset thay thế sửa chữa các hàm Vlookup vào excel
Ví dụ: Kết thích hợp hàm Vlookup với offset để tính giá trị lớn số 1 với tài liệu trong bảng.
Bên cạnh đó, chúng ta cũng có thể kết vừa lòng hàm offset cùng với hàm Vlookup.
Bước 1: tại ô B2, các bạn hãy nhập hàm =VLOOKUP(B1,A5:B11,2,FALSE) vào ô tham chiếu mong muốn hiển thị kết quả.
Sử dụng hàm offset vắt hàm Vlookup nhằm tra cứu tài liệu bên trái trong file Excel. Như các bạn đã biết, hàm Vlookup chỉ có chức năng trả lại một giá bán trị cố định ở bên phải cột tra cứu, đó là một trong những hạn chế lớn số 1 của hàm Vlookup, nó không có công dụng dò giá tốt trị phía bên trái của nó.
Chúng ta liên tiếp áp dụng ví dụ như trên cho bảng tiền thưởng theo tháng. Ví như bạn chỉ cần tìm kiếm số chi phí thưởng của một tháng làm sao đó, áp dụng công thức hàm Vlookup cơ bản rất đối kháng giản.
=VLOOKUP(B10,A5:B11,2,FALSE)
Bước 2: sau khi nhập công thức, chúng ta nhấn phím Enter nhằm hiển thị kết quả.
Xem thêm: Công Thức Tính Điểm Trung Bình Trong Excel Đơn Giản Nhất, Hướng Dẫn Cách Tính Điểm Trung Bình Trong Excel
Hy vọng cùng với nội mà emcanbaove.edu.vn đã share và trả lời về hàm offset vào excel. Giúp chúng ta hiểu rõ rộng về hàm này, đồng thời vận dụng trong quá trình cũng như trong học hành một cách. Chúc chúng ta ngày càng thành thục excel hơn, để giao hàng cho quá trình tốt rộng nhé.