Trong Excel hàm OFFSET được sử dụng để tham chiếu hay giám sát dữ liệu một ô, một dãy trong bảng tính. Hàm này sẽ tính toán dựa trên vùng tham chiếu được đến sẵn. Người dùng cũng hoàn toàn có thể kết đúng theo hàm OFFSET với những hàm khác liên quan trong Excel để hỗ trợ quá trình trên bảng tính được thuận tiện và tiết kiệm thời gian hơn. Giả dụ bạn không biết về cú pháp cùng cách thực hiện hàm OFFSET vào Excel hãy tham khảo ngay nội dung bài viết dưới trên đây từ Mega.
Bạn đang xem: Hàm offset là gì
Hàm OFFSET là hàm gì?Cú pháp với cách thực hiện hàm OFSET?
Những chú ý khi thực hiện hàm OFFSET trong excel
Hàm OFFSET có thể kết hợp với những hàm nào?
Công thức và giải pháp sử dụng hàm OFFSET trong Excel đối chọi giản, dễ dàng thực hiện!
I. Hàm OFFSET là hàm gì? bí quyết hàm OFFSET vào Excel
Hàm OFFSET là hàm được dùng làm tham chiếu hay thống kê giám sát dữ liệu một ô, một dãy trong bảng tính phụ thuộc một vùng tham chiếu được cho sẵn trước đó.
Hàm OFFSET trong excel được sử dụng để thống kê giám sát dữ liệu của một ô hay 1 dãy trong bảng tính nhanh và thuận lợi hơn. Ngoại trừ ra, hàm OFFSET còn được thực hiện kết hợp với các hàm khác để cung cấp hoàn thành các bước nhanh giường và kết quả hơn. Người tiêu dùng khi làm việc trên laptop chẳng hạn sẽ tiết kiệm được thời hạn đáng kể khi thành thạo nhiều loại hàm này.
Hàm này được triển khai trên hệ quản lý và điều hành Windows 10 và phiên bản Excel 2010, 2013, Excel 2016, Excel 2019.
Cú pháp hàm OFFSET: =OFFSET(reference, rows, cols,
Trong đó:
+ Reference: Là vùng tham chiếu làm cơ sở cho hàm để sản xuất vùng tham chiếu mới.
+ Rows: Là số dòng bên trên hoặc bên dưới reference, tính từ bỏ ô đầu tiên (ô ở góc trên mặt trái) của reference.
+ Cols: Là số cột bên trái hoặc bên phải reference, tính từ bỏ ô thứ nhất (ô ở góc trên mặt trái) của reference.
+ Height: Là số loại của vùng tham chiếu cần trả về. Height đề xuất là số dương.
+ Width: Là số cột của vùng tham chiếu yêu cầu trả về. Width buộc phải là số dương.
II. Những lưu ý khi sử dụng hàm OFFSET trong excel
Khi áp dụng công thức hàm OFFSET chúng ta cần để ý một số điểm sau:
Nếu chúng ta không nhập các đối số height và width thì tham chiếu trả về mặc định có cùng độ cao và độ rộng với vùng tham chiếu.Đối số height và width phải là số dương (số ko âm).Hàm OFFSET không dịch rời hay làm vắt đổi ngẫu nhiên phần làm sao được chọn bởi vì hàm chỉ trả về một tham chiếu. Các bạn cũng 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.
III. Cách áp dụng hàm OFFSET trong excel
Ví dụ 1: Sử dụng hàm OFFSET để đưa ra cực hiếm ô ở trong phần cột 2 mẫu 3 vào bảng sau.
Bước 1: Trong bảng tài liệu tính Excel, thực hiện nhập hàm =OFFSET(B3,3,2) vào ô tham chiếu hy vọng hiển thị kết quả.
Nhập cách làm như trên
Nhấn Enter để bảng tính hiển thị tác dụng như hình dưới.
Kết trái hàm OFFSET trả về là 6000000
Ví dụ 2: áp dụng hàm OFFSET và hàm SUM nhằm tính tổng của cột D sinh sống bảng mặt dưới.
Bước 1: Trong bảng tài liệu tính Excel, thực hiện nhập hàm =SUM(OFFSET(A3,1,3,5,1)) vào ô tham chiếu ý muốn hiển thị kết quả.
Sử dụng hàm OFFSET cùng hàm SUM để tính tổng
Trong đó:
SUM và OFFSET: Là lệnh hàm.
A3: Là ô tham chiếu.1: Là số hàng dịch chuyển xuống ban đầu từ ô tham chiếu A3.3: Là số cột được dịch chuyển sang phải ban đầu từ ô tham chiếu A3.5: Là tổng số mặt hàng lấy tài 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ả.
Nhấn Enter tổng sẽ hiển thị như hình dưới.
Kết trái là 24000000
Ví dụ 3: Hàm OFFSET kết hợp với hàm AVERAGE, MAX, MIN
Bước 1: Để tính quý giá trung bình vào bảng, bạn hãy nhập hàm =AVERAGE(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))
Hàm OFFSET kết hợp với hàm AVERAGE
Nhấn Enter để hiển thị kết quả như hình dưới.
Với cách kết hợp này cũng vận dụng cho hàm MAX
Bước 2: Để tính số lớn nhất trong dải, chúng ta nhập hàm =MAX(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)) như hình trên.
Nhấn Enter để hiển thị hiệu quả như hình
Bước 3: Để search số nhỏ tuổi nhất trong dải, chúng ta nhập =MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).
Hàm OFFSET kết hợp với hàm MIN
Nhấn Enter để hiển thị tác dụng như hình dưới.
250000 là công dụng thấp nhất
Ví dụ 4: Hàm OFFSET kết hợp với hàm VLOOKUP
Trong gần như cách sử dụng hàm offfset bạn có thể kết đúng theo hàm VLOOKUP với hàm OFFSET.
Bước 1: Bạn nhập hàm =VLOOKUP(E2,A2:B8,2,FALSE) vào ô tham chiếu mong mỏi hiển thị kết quả.
Hàm OFFSET kết phù hợp với hàm VLOOKUP
Nhấn Enter nhằm hiển thị tác dụng như hình
Ví dụ 5: Hàm OFFSET kết phù hợp với hàm MATCH
Cho bảng tài liệu gồm: MÃ SP, TÊN SP, HÃNG, GIÁ BÁN. Hãy kết hợp MATCH cùng OFFSET nhằm tìm giá bán của sản phẩm.
Công thức: =OFFSET($A$1;MATCH(F2;$B$2:$B$10;0);3)
Hàm OFFSET kết phù hợp với hàm MATCH
Trong đó:
+ $A$1: Ô thắt chặt và cố định để khẳng định hàng và cột bù trừ.
+ MATCH(F2;$B$2:$B$10;0): khẳng định số hàng phụ thuộc vị trí của tên sản phẩm trong cột TÊN SP.
+ 3: trường đoản cú ô A1 mang lại cột D (GIÁ BÁN) là 3.
Kết quả như hình trên
Ví dụ 6: thực hiện hàm OFFSET phối kết hợp hàm COUNTA để chế tạo dải động
Hàm OFFSET kết hợp hàm COUNTA có thể giúp các bạn tạo một dải cồn vô cùng hữu ích trong những trường hợp bạn muốn tạo danh sách auto cập nhật. Việc bạn cần làm là để tên đến dải, kế tiếp tạo một list theo mục để tiện lợi thao tác.
Xem thêm: Phần Mềm Chuyển Ảnh Thành Văn Bản Nhanh, Chuẩn, Chuyển Đổi Hình Ảnh Sang Văn Bản
Đầu tiên, chúng ta cần xác minh và để tên mang lại dải. Vào tab Formula > Defined Names > Define Name
Thực hiện như hình trên
Sau đó nhập công thức hàm OFFSET phối kết hợp hàm COUNTA như sau:
=OFFSET(Sheet_Name!$A$1,0,0,COUNTA(Sheet_Name!$A:$A),1)
Công thức hàm OFFSET phối hợp hàm COUNTA
Trong cách làm hàm OFFSET:
reference:được màn biểu diễn dưới dạng tên trang tính cùng theo sau là dấu chấm than kèm theo địa chỉ cửa hàng ô đựng mục đầu tiên có mặt trong trong dải. Bạn cần sử dụng những tham chiếu hoàn hảo nhất ($) cho tên trang tính, ví dụ như Sheet1!$A$1.Các tham số rowsvà cols đều là 0, vì không có cột hoặc mặt hàng nào được thêm vào.height: Do chúng ta sử dụng hàm COUNTA để đo lường số ô ko rỗng trong cột bao gồm chứa những mục của dải được đặt tên. Chú ý điều này, bạn nên có thể định tên bảng bên cạnh tên cột, ví như COUNTA(Sheet_Name!$A:$A).width: là 1 trong cộtSau khi đang đặt tên đến dải, bạn sử dụng Excel Data Validation để tạo list theo mục, bảo đảm Excel luôn tự động cập nhật ngay khi chúng ta thêm hoặc xoá các mục từ danh sách nguồn (bảng tính gốc). Các bạn hãy vào tab Data > Data Tools > Data Validation hoặc nhận mũi tên bên cạnh mục Data Validation > Data Validation.
Chọn Data Validation
Tại phần Allow kích chọn danh sách và cố định vùng ở vị trí Source
Kết quả họ sẽ gồm một dải rượu cồn hiển thị bên dưới dạng list theo mục như hình dưới.
IV. Tổng kết
Bài viết trên lí giải cách sử dụng hàm OFFSET vào Excel để tham chiếu và đo lường và thống kê dữ liệu. Nếu các bạn hiểu cùng thành thành thục hàm này sẽ cung cấp trong quá trình một cách nhanh lẹ và tiện lợi hơn. Hy vọng bài viết sẽ có lợi với bạn, chúc bạn triển khai thành công!
Đừng quên truy cập website mega.com.vn để update các bài viết mới nhất tương quan đến thủ thuật excel tương tự như tin học tập văn phòng các bạn nhé!
Excel – dòng tên không thể quá không quen đối với cuộc sống đời thường của họ nữa rồi, khi mà lại từ công việc, học hành hằng ngày họ đều phải áp dụng nó. Vậy chúng ta đã nắm rõ hết tần tật về Excel không nhỉ? Hôm nay, bọn họ sẽ cùng nhau bàn bạc về hàm OFFSET là gì, hàm OFFSET trong Excel là gì cùng nó có ứng dụng, tác dụng như chũm nào nhé!
Hàm OFFSET là gì?
Hàm OFFSET là hàm được sử dụng để xem, đo lường hay phân tích tài liệu của một ô hay 1 dãy vào bảng tính Excel phụ thuộc một vùng tham chiếu đã gồm sẵn trước đó.
Hàm OFFSET được vận dụng trong Excel để:
Nhanh chóng và tiện lợi hơn khi tính toán, phân tích tài liệu của một ô hay là 1 dãy trong bảng tính.Một tính năng khác nữa nhé là, phối hợp hàm OFFSET với những hàm không giống trong Excel để cung ứng hoàn thành các bước nhanh hơn. Bạn vẫn trở nên bài bản hơn khi làm các các bước liên quan cho hàm Excel, là thời cơ giúp bạn thăng tiến vào 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,
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 trên hoặc dưới reference, tính tự ô đầu tiên (góc mặt trái) của reference. Rows có thể số âm, gồm số thể dương, giả dụ số âm thì những dòng trả về nằm trên reference, giả dụ 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 phía trái hoặc bên đề nghị reference, tính từ ô trước tiên (góc mặt trái) của reference. Cols hoàn toàn có thể là số âm, hoàn toàn có thể là số dương, nếu số âm thì các cột trả về nằm bên cạnh trái reference, nếu như số dương thì các cột nằm bên cạnh phải reference.Các xem xét khi sử dụng hàm OFFSET
Khi báo lỗi #REF thì những đối số Rows cùng 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 cùng width thì luôn luôn phải là số dương.Nếu bỏ qua hai đối số height cùng width thì tham chiếu trả về mặc định có cùng chiều cao và phạm vi với vùng tham chiếu.Nếu những đối số height cùng width >1 thì có nghĩa là hàm phải trả về nhiều hơn nữa 1 ô, để không sở hữu và nhận lỗi #VALUE thì chúng ta phải thực hiện công thức mảng hàm OFFSET.Hàm OFFSET chỉ trả về một tham chiếu bắt buộc sẽ không di chuyển hay làm cụ đổi bất kỳ phần làm sao được chọn. Bạn có thể sử dụng hàm OFFSET kết phù hợp với các hàm yêu cầu đến một đối số tham chiếu.
Ví dụ cơ bạn dạng của hàm OFFSET
Nói sơ qua định hướng về hàm OFFSET là gì nghe dường như trừu tượng, dưới đó 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. Giải pháp thực hiện phần nhiều là kiểu như nhau trên các phiên bạn dạng Excel 2007, 2010, 2013, 2016, 2019 cùng Mac
OS.
Đề bài: Hãy áp dụng hàm OFFSET nhằm tính quý giá ô tại đoạn cột 2, loại 3 vào 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: thừa nhận phím Enter cùng xem kết quả cần tìm.
Sự kết hợp của hàm OFFSET với những hàm không giống 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ì cùng với hàm khác trong Excel. Cũng tạo nên các phép tính mới để giúp giải quyết các vấn đề nhanh lẹ và luôn thể lợi. Ví như kết phù hợp với hàm SUM, AVERAGE, MIN, MAX,..
Kết phù hợp OFFSET với hàm SUM nhằm tính tổng phạm vi được trả về
Ví dụ 1: Tính tổng tiền mặt hàng của tất cả sản phẩm theo tài liệu sau đây.
Chúng ta có thể tham chiếu bước đầu từ ô A1 tiếp nối phải dịch rời xuống 1 hàng, sang yêu cầu 5 hàng. Sau đó lấy 10 chiếc và một cột.
Từ đó kết hợp lại ta gồm 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ới sang đề nghị 3 dòng, tiếp đến 10 cái và 1 cột.
Suy ra ta tất cả công thức : =SUM(OFFSET(C3;-1;3;10;1)). Và hiệu quả thu được là như nhau.
Kết vừa lòng hàm OFFSET cùng với hàm AVERAGE nhằm tính cực hiếm 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ị vừa phải của bảng là, tương tự với cách tính tiền thưởng mang đến n tháng, chúng ta cũng hoàn toàn có thể áp dụng nhằm tính quý giá trung bình mang lại n tuần, n ngày hay n năm.
Ta bao gồm công thức là: =AVERAGE(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).
Nhấn Enter cùng nhận kết quả.
Kết phù hợp với hàm MAX để tìm số béo nhất
Ví dụ 3: Tìm giá bán trị lớn số 1 trong bảng tính:
Giá trị lớn số 1 là:
Ta có công thức: =MAX(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).
Nhấn Enter và nhận ra kết quả.
Kết hợp với hàm MIN để tìm số bé dại nhất
Ví dụ 4: Tìm giá trị bé dại nhất trong bảng tính:
Giá trị nhỏ nhất là:
Ta tất cả công thức: MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).
Nhấn Enter và nhận thấy kết quả.
Hàm OFFSET kết hợp với hàm VLOOKUP nhằm tra cứu vãn thông tin
Hàm VLOOKUP vào Excel gồm một giảm bớt đó là chỉ có chức năng trả lại một cực hiếm nhất định. Ở bên đề xuất cột tra cứu vãn nên tác động rất các đến quá trình làm việc. Chính vì như thế có thể thực hiện hàm OFFSET phối hợp hàm VLOOKUP nhằm tra cứu tài liệu bên trái trong tệp tin Excel.
Ví dụ 5: sử dụng hàm OFFSET kết phù hợp với hàm VLOOKUP nhằm hiển thị quý giá lương tháng 3 trong bảng sau:
Ta tất cả công thức: =VLOOKUP(B1,A5:B11,2,FALSE).
Nhấn Enter và nhận hiệu quả cần tìm.
Trên đó 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 để thừa nhận lại kết quả nhanh, đúng mực nhất.
Trong cuộc sống thường ngày hiện nay, đa số các các bước liên quan tiền đến giấy tờ đều đề nghị dùng đến Excel. Ngay cả khi sẽ là học sinh các bạn cũng theo thông tin được biết về tầm đặc biệt quan trọng của bảng tính này rồi. Vì thế chúng ta cần buộc phải học những thủ thuật, phím tắt để hoàn thành công việc nhanh bao gồm hơn. Vậy là hôm nay chúng ta đã cùng tò mò hàm OFFSET là gì, hàm OFFSET vào Excel là gì và những ứng dụng cần thiết của hàm. Đừng do dự gì nữa mà không giữ giàng để khi cần có cái nhưng mà dùng.
