Trong excel có không ít hàm, nhưng chúng ta chỉ có tác dụng được một vài hàm đối chọi giản. Cùng với hàm OFFSET cũng vậy, vậy chúng ta đã rõ hàm Offset là gìvàcách dùng của hàm này cụ thể như nuốm nào chưa?
Bài viết này để giúp đỡ giải quyết có các bạn 5 vụ việc về thực hiện hàm OFFSET Excel hay chạm chán phải. Mời độc giả cùng theo dõi.
Bạn đang xem: Hàm offset trong excel là gì
1. Hàm OFFSET vào Excel là gì?
Hàm OFFSET vào excel trả về một ô hoặc phạm vi ô là con số hàng với cột được chỉ định từ 1 ô hoặc một phạm vi ô.
Trong Excel, hàm OFFSET được sử dụng với mục đích:
- máu kiệm thời hạn trong việc giám sát dữ liệu của một ô.
- Hàm này kết phù hợp với hàm khác giúp bạn nâng cao hiệu suất làm cho việc.
2. Cú pháp của hàm OFFSET
Công thức Offset trong Excel=OFFSET (tham chiếu, hàng, cols, chiều cao, chiều rộng lớn )
Trong đó:
+ Tham chiếu: là khu vực mà bạn có nhu cầu tham chiếu mang lại phần như thế nào đó
+ Hàng: hoàn toàn có thể là tích cực và lành mạnh hoặc tiêu cực.
+Cols: số lượng cột, ở phía trái hoặc mặt phải, mà bạn có nhu cầu ô trên phía trái của tham chiếu được trả về nhằm tham chiếu. Ví dụ, sử dụng cols bởi 2 hướng dẫn và chỉ định rằng ô trên phía trái của tham chiếu được trả về đã là nhì cột ngơi nghỉ bên phải của ô tham chiếu bên trên cùng phía bên trái . Cols có thể là lành mạnh và tích cực hoặc tiêu cực.
+ Chiều cao: tùy chọn, theo con số hàng, mà bạn có nhu cầu tham chiếu được trả về. độ cao phải là một vài dương. Nếu chiều cao bị bỏ qua, tham chiếu được trả về sẽ sở hữu cùng độ cao với tham chiếu.
+ Chiều rộng: tùy chọn, theo số lượng cột, mà bạn có nhu cầu tham chiếu được trả về. Chiều rộng đề xuất là một số trong những dương. Giả dụ chiều rộng bị bỏ qua, tham chiếu được trả về sẽ có được cùng chiều rộng lớn với tham chiếu.
Công thức hàm OFFSET vào Excel
3. Ví dụ về cách dùng hàm OFFSET
Ví dụ 1
- vào ô B8 trả về quý giá của ô E6 thông qua ô tham chiếu các đại lý B5.
- tại ô B8 các bạn nhập công thức:OFFSET(B5,1,3,1,1)
Ví dụ 2
- Hàm OFFSET dưới trả về ô tất cả 3 hàng bên dưới và 2 cột bên cần ô A2. Hàm OFFSET trả về một ô vì cả chiều cao và chiều rộng gần như được đặt thành 1.
-Ta được tác dụng như hình dưới:
4. Hàm OFFSET thường xuyên kết hợp với hàm nào?
Nhìn phần lý thuyết trên thì rất nhiều người còn không biết và vướng mắc tại sao. Tuy nhiên, tôi đã minh họa lấy ví dụ như kỹ rộng phía dưới cho các bạn:
Hàm OFFSET kết hợp với hàm SUM
- Ví dụ: Tính tổng lương và thưởng của nhân viên Nguyễn Văn B bắt đầu từ ô B6 (lương của nhân viên cấp dưới đầu tiên).
- Áp dụng hàm: =SUM(OFFSET(B6,3,0,1,2) các các bạn sẽ được công dụng như sau:
Hàm OFFSET kết phù hợp với hàm AVERAGE, MAX, MIN
- bước 1: Cho ví như mô tả dưới ảnh, nhằm tính giá trị trung bình, các bạn nhập phương pháp sau:=AVERAGE(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).
Hình minh họa
- cách 2: nhận Enter để hiển thị kết quả.
Hình minh họa
- cách 3: Để tính số lớn số 1 trong dải, các bạn nhập cách làm sau:=MAX(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).
Hình minh họa
- bước 4: Nhấn Enter để nhận kết quả.
Hình minh họa
- bước 5: Để search số nhỏ dại nhất trong dải, chúng ta nhập công thức sau:=MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).
Hình minh họa
- cách 6: thừa nhận Enter để nhận kết quả.
Hình minh họa
Hàm OFFSET kết phù hợp với hàm VLOOKUP
- bước 1: Trong ô tham chiếu mong mỏi hiển thị kết quả, bạn nhập công thức=VLOOKUP(B1,A5:B11,2,FALSE).
Hình minh họa
- cách 2: dìm Enter để nhận kết quả.
Hình minh họa
Ngoài ra, hàm OFFSET hoàn toàn có thể kết phù hợp với nhiều hàm không giống để thống kê giám sát và xử lý dữ liệu trong excel. Hàm để giúp ích các bạn rất nhiều nếu các bạn biết vận dụng và phối kết hợp hàm một cách hiệu quả.
Để cải thiện kiến thức cũng giống như kỹ năng áp dụng vào thao tác và tiếp thu kiến thức Unica còn đem đến bạn đọc những kiến thức xoay xung quanh lĩnh vực tin học tập văn phòng với những khoá học tập Powerpoint, học Word, học Excel từ cơ phiên bản đến nâng cao,... đang được không ít người quan tiền tâm.
Trong Excel hàm OFFSET được áp dụng để tham chiếu hay đo lường và thống kê dữ liệu một ô, một dãy trong bảng tính. Hàm này sẽ giám sát dựa bên trên vùng tham chiếu được mang đến sẵn. Người dùng cũng có thể kết hòa hợp hàm OFFSET với các hàm khác liên quan trong Excel nhằm hỗ trợ quá trình trên bảng tính được thuận lợi và huyết kiệm thời gian hơn. Nếu như bạn không biết về cú pháp với cách thực hiện hàm OFFSET trong Excel hãy tham khảo ngay nội dung bài viết dưới phía trên từ mamnongautruc.edu.vn.
Hàm OFFSET là hàm gì?Cú pháp và cách áp dụng hàm OFSET?
Những chú ý khi sử dụng hàm OFFSET vào excel
Hàm OFFSET rất có thể kết hợp với những hàm nào?
Công thức và cách sử dụng hàm OFFSET trong Excel đối chọi giản, dễ thực hiện!
I. Hàm OFFSET là hàm gì? phương pháp hàm OFFSET vào Excel
Hàm OFFSET là hàm được dùng làm tham chiếu hay đo lường và thống kê dữ liệu một ô, một hàng trong bảng tính phụ thuộc một vùng tham chiếu được mang đến sẵn trước đó.
Hàm OFFSET vào excel được sử dụng để giám sát dữ liệu của một ô hay 1 dãy vào bảng tính nhanh và thuận tiện hơn. Ngoại trừ ra, hàm OFFSET còn được áp dụng kết phù hợp với các hàm không giống để cung cấp hoàn thành các bước nhanh chóng và hiệu 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 chi phí được thời gian đáng kể khi thành thạo một số loại hàm này.
Hàm này được tiến hành trên hệ quản lý Windows 10 với 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 đại lý cho hàm để sinh sản 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ừ ô thứ nhất (ô ở góc trên bên trái) của reference.
+ Cols: Là số cột phía bên trái hoặc bên phải reference, tính trường đoản cú ô đầu tiên (ô ở góc trên mặt trái) của reference.
+ Height: Là số cái của vùng tham chiếu đề nghị trả về. Height đề nghị là số dương.
+ Width: Là số cột của vùng tham chiếu phải trả về. Width đề xuất là số dương.
II. Những chú ý khi thực hiện hàm OFFSET trong excel
Khi vận dụng công thức hàm OFFSET các bạn cần chăm chú một số điểm sau:
Nếu chúng ta không nhập những đối số height và width thì tham chiếu trả về mang định bao gồm cùng chiều 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 chuyển hay làm cố gắng đổi ngẫu nhiên phần như thế nào được chọn bởi vì hàm chỉ trả về một tham chiếu. Các chúng ta có thể sử dụng hàm OFFSET kết phù hợp với các hàm đề xuất đến một đối số tham chiếu.
III. Cách áp dụng hàm OFFSET vào excel
Ví dụ 1: Sử dụng hàm OFFSET để đưa ra quý hiếm ô ở đoạn cột 2 loại 3 vào bảng sau.
Bước 1: Trong bảng dữ liệu tính Excel, thực hiện nhập hàm =OFFSET(B3,3,2) vào ô tham chiếu muốn hiển thị kết quả.
Nhập phương pháp như trên
Nhấn Enter để bảng tính hiển thị kết quả 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 cùng hàm SUM nhằm tính tổng của cột D nghỉ ngơi bảng bên 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 ước ao hiển thị kết quả.
Sử dụng hàm OFFSET với hàm SUM nhằm 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 bắt đầu từ ô tham chiếu A3.3: Là số cột được dịch rời 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 số cột lấy tài liệu tính kết quả.
Nhấn Enter tổng vẫn hiển thị như hình dưới.
Kết quả 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 nhằm hiển thị công dụng như hình dưới.
Với cách kết hợp này cũng áp 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 nhằm hiển thị tác dụng như hình
Bước 3: Để tìm số nhỏ tuổi nhất vào dải, các bạn nhập =MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)).
Hàm OFFSET kết phù hợp với hàm MIN
Nhấn Enter để hiển thị tác dụng như hình dưới.
250000 là kết quả thấp nhất
Ví dụ 4: Hàm OFFSET kết phù hợp với hàm VLOOKUP
Trong rất nhiều cách áp dụng hàm offfset bạn có thể kết hợp hàm VLOOKUP cùng 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 hy vọng 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ị kết quả như hình
Ví dụ 5: Hàm OFFSET kết phù hợp với hàm MATCH
Cho bảng dữ liệu gồm: MÃ SP, TÊN SP, HÃNG, GIÁ BÁN. Hãy phối hợp MATCH cùng OFFSET nhằm tìm giá cả 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 hợp với hàm MATCH
Trong đó:
+ $A$1: Ô cố định và thắt chặt để khẳng định hàng với cột bù trừ.
+ MATCH(F2;$B$2:$B$10;0): khẳng định số hàng phụ thuộc vào vị trí của tên sản phẩm trong cột TÊN SP.
+ 3: từ bỏ ô A1 mang lại cột D (GIÁ BÁN) là 3.
Kết đúng như hình trên
Ví dụ 6: áp dụng hàm OFFSET phối kết hợp hàm COUNTA để chế tạo ra dải động
Hàm OFFSET kết đúng theo hàm COUNTA có thể giúp bạn tạo một dải động vô cùng hữu ích trong những trường hợp bạn có nhu cầu tạo danh sách tự động cập nhật. Việc bạn cần làm là đặt tên cho dải, tiếp nối tạo một list theo mục để dễ ợt thao tác.
Đầu tiên, họ cần xác minh và đặt tên đến dải. Vào tab Formula > Defined Names > Define Name
Thực hiện như hình trên
Sau kia 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 kết hợp hàm COUNTA
Trong bí quyết hàm OFFSET:
reference:được màn biểu diễn dưới dạng thương hiệu trang tính với theo sau là dấu chấm than kèm theo add ô cất mục đầu tiên xuất hiện trong vào dải. Bạn phải sử dụng các tham chiếu tuyệt vời và hoàn hảo nhất ($) mang lại tên trang tính, ví dụ Sheet1!$A$1.Các thông số rowsvà cols đều là 0, vì không tồn tại cột hoặc hàng nào nhận thêm vào.height: Do họ sử dụng hàm COUNTA để giám sát và đo lường số ô không 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 chỉ có thể định thương hiệu bảng bên cạnh tên cột, ví như COUNTA(Sheet_Name!$A:$A).width: là một trong cộtSau khi đã đặt tên mang đến dải, các bạn sử dụng Excel Data Validation để tạo list theo mục, bảo đảm an toàn Excel luôn tự động cập nhật ngay khi chúng ta thêm hoặc xoá những mục từ list nguồn (bảng tính gốc). Bạn hãy vào tab Data > Data Tools > Data Validation hoặc thừa nhận mũi tên cạnh bên mục Data Validation > Data Validation.
Chọn Data Validation
Tại phần Allow kích chọn menu và cố định vùng tại phần Source
Kết quả bọn họ sẽ gồm một dải cồn hiển thị dưới dạng list theo mục như hình dưới.
Xem thêm:
IV. Tổng kết
Bài viết trên lí giải cách thực hiện hàm OFFSET trong Excel để tham chiếu và đo lường và tính toán dữ liệu. Nếu chúng ta hiểu và thành thạo hàm này sẽ hỗ trợ trong công việc một cách hối hả và thuận tiện hơn. Hy vọng nội dung bài viết sẽ hữu dụng với bạn, chúc bạn thực hiện thành công!
Đừng quên truy vấn website mamnongautruc.edu.vn để update các nội dung bài viết mới nhất liên quan đến thủ thuật excel cũng tương tự tin học tập văn phòng các bạn nhé!