Hàm offset dùng để làm gì

Trong những hàm tham chiếu vào Excel, hàm OFFSET là 1 trong những hàm cực kì lợi hại do nó rất có thể tạo đề nghị những màn phối hợp ăn ý với những hàm tham chiếu cũng như các hàm giám sát khác. Vậy làm nạm nào để thực hiện hàm OFFSET hiệu quả? Hãy cùng đi tìm hiểu với basissport.store nhé.

Bạn đang xem: Hàm offset dùng để làm gì

Tuyệt đỉnh Excel - biến chuyển bậc thầy Excel vào 16 giờ

Giới thiệu về hàm OFFSET

Công thức hàm OFFSET

Công thức hàm OFFSET vào vai trò hàm tham chiếu vào Excel như sau:


=OFFSET(reference, rows, cols, , )

Trong công thức bọn họ nhìn thấy 3 đối số reference, rows cùng cols là 3 đối số bắt buộc người dùng phải nhập. 2 đối số còn lại, height cùng width, là 2 đối số tùy chọn, nghĩa là fan dùng hoàn toàn có thể nhập hoặc bỏ qua.

Các đối số phải trong cách làm hàm OFFSETreference:Là một ô hoặc một dải ô gần cạnh mà kế tiếp nó có thể bị thêm/bớt. Nói biện pháp khác, đối số này khẳng định điểm có tác dụng gốc của công thức hàm OFFSET.rows: Là số hàng di chuyển lên hoặc xuống trường đoản cú điểm làm gốc (trong đối số reference). Nếu điền số dương vào trong đối số này, bí quyết sẽ dịch chuyển xuống bên dưới tham chiếu. Ngược lại, vào trường hòa hợp điền một số âm vào đối số này thì cách làm sẽ di chuyển lên phía trên tham chiếu.cols: Là số cột bạn có nhu cầu công thức dịch chuyển từ điểm làm cho gốc (trong đối số reference). Tương tự như như cùng với đối số rows, cols có thể là số dương nhằm công thức dịch rời sang bên yêu cầu tham chiếu, hoặc số âm nhằm công thức dịch rời sang phía bên trái tham chiếu.Các đối số tùy chọn trong công thức hàm OFFSETheight: Làchiều cao tính bằng số hàng mà bạn có nhu cầu hàm tham chiếu trong Excel trả về kết quảwidth: Làchiều rộng, tính thông qua số cột, mà bạn muốn tham chiếu trả về.

Lưu ý: Cả nhì đối số height và width luôn phải là số dương. Nếu 1 trong những hai bị vứt qua, thì Excel sẽ tự động hóa dùng height hoặc width của tham chiếu gốc.

Để vận dụng được bí quyết hàm OFFSET được reviews phía trên, chúng ta hãy cùng khám phá một ví dụ rõ ràng nhé.

Hình bên dưới đây miêu tả cách OFFSET Excel trả về kết quả từ công thức được nhập.

*

Hàm OFFSET được nhập theo công thức:

=OFFSET(A1,3,1)

Công thức OFFSET Excel này được hiểu là lấy ô tham chiếu ban đầu từ điểm gốc A1, dịch chuyển ô tham chiếu3 mặt hàng xuống dưới, sau đó dịch ô tham chiếu 1 cột sang phải. Như vậy, ô tham chiếu của chúng ta là ô B4.

Cùng mày mò một ví dụ khác về hàm OFFSET nhé.

*

Về phiên bản chất, chúng ta vẫn sử dụng công thức OFFSET trong Excel như tại phần trên. Mặc dù nhiên, trên đối số rows, thay vì điền số 3, họ điền ô E1. Excel sẽ auto hiểu bọn họ nhập dữ liệu trong ô E1 vào đối số rows trong cách làm hàm OFFSET. Bởi vì ô E1 tất cả chứa số 3 nên hoàn toàn có thể nói, chúng ta đã gián tiếp nhập số 3 vào đối số rows.

Những điều cần ghi ghi nhớ về hàm OFFSET

HàmOFFSETtrong Excel ko di chuyển ngẫu nhiên ô hoặc dải nào, nhưng nó chỉ trả về một tham chiếu vào phạm vi dữ liệu.Đối với công thức hàmOFFSETtrả về kết quả là một dải ô, các đối số rows cùng cols luôn luôn luôn đề cập mang lại ô phía trên bên trái.Hàm OFFSET hoàn toàn có thể được phối kết hợp lồng trong ngẫu nhiên hàm Excel nào đồng ý một tham chiếu ô hoặc dải trong những đối số của nó.

Để minh họa cho các lỗi có thể xảy ra trong vượt trình bọn họ sử dụng hàm OFFSET làm hàm tham chiếu vào Excel, bọn chúng mình bao gồm một ví dụ như sau: nếu khách hàng sử dụng bí quyết =OFFSET(A1,3,1,1,3) thì sẽ thấy thông tin lỗi #VALUE!. Lí do nguyên nhân là Excel cần thiết trả một dải đựng 1 hàng 3 cột về 1 ô duy nhất.

Tuy nhiên, công thức hàm trên sẽ vận động mượt mà khi bạn lồng phương pháp trên vào trong hàm SUM. áp dụng công thức:

=SUM(OFFSET(A1,3,1,1,3))

Như vậy, bọn họ thu được kêt quả chính là tổng dải ô tham chiếu gần kề được khẳng định bởi hàm OFFSET.

*

Các trường hợp áp dụng hàm OFFSET

Tạo những dải động bằng hàm OFFSET

Các tham chiếu trực tiếp ví dụ như A1:B4 là tham chiếu tĩnh, tức là nó luôn luôn tham chiếu mang lại phạm vi cầm định. Bởi vì đó, bạn không thể đổi khác phạm vi tham chiếu.

Nếu bạn phải thường xuyên update dữ liệu trên bảng Excel nhưng không muốn làm ảnh hưởng đến kết quả hàm tham chiếu trong Excel, hàm OFFSET là sự việc lựa chọn tuyệt vời. Nó chất nhận được bạn tạo các dải động, nên khi bạn biến đổi dữ liệu bảng tính xuất xắc thêm những hàng, cột bắt đầu vào bảng, các bạn vẫn sẽ kiếm được dữ liệu bản thân cần.

Lấy dải từ ô gốc bởi hàm OFFSET trong Excel

Với các bảng Excel có nhiều dữ liệu, bạn cũng có thể quên mất địa chỉ thực của cả dải ô đề nghị tìm tìm thông tin. Toàn bộ những gì bạn hãy nhớ là phạm vi search kiếm bước đầu từ một số ô cố kỉnh thể. Trong tình huống này, hàm OFFSET sẽ cứu bạn khỏi việc mỏi đôi mắt tra cứu tài liệu đấy.

Cách thực hiện hàm OFFSET

Sử dụng hàm OFFSET lồng trong hàm SUM

Ở những phần trên, chúng ta đã nói tới một ví dụ đơn giản và dễ dàng về cách áp dụng hàm OFFSET lồng trong hàm SUM rồi. Vậy cụ thể hơn cách phối hợp này làm được gì? thuộc nhau mày mò nhé.

Sử dụng bí quyết hàm SUM phối kết hợp hàm OFFSET rứa đổi

Công thức hàm SUM kết hợp hàm OFFSET thay đổi có công dụng tính tổng những ô tham chiếu vào OFFSET Excel trong cả khi bảng tính được biến hóa và update dữ liệu liên tục. Như vậy, chúng ta không phải phải thay thế hàm SUM thủ công mỗi lần thêm mặt hàng hoặc cột vào trang tính nữa.

Giả sử, họ có bảng tính Excel phía bên dưới được update hàng tháng. Điều này nghĩa là bảng tính của chúng ta biến hóa liên tục. Để tính tổng tiền thưởng qua các tháng, chúng tacần một hàm SUM thích hợp ứng với những đổi khác trong bảng. Thay vị dùng hàm SUM cơ bạn dạng cần cập nhật hàm thủ công, phương pháp hàm SUM kết hợp hàm OFFSET thuận tiện hơn khôn xiết nhiều.

*

*

Bạn nhập thẳng ô trước tiên của dải đề xuất tính tổng vào đối số trong phương pháp hàm SUM, kế tiếp việc bắt buộc làm chỉ nên đưa ra những tham số sót lại cho hàmOFFSET. Hàm tham chiếu trong Excelsẽ tự động tham chiếu cho đến ô ở đầu cuối của dải. Hãy nhập các tham số sau đây vào đối số trong cách làm hàm OFFSET:

reference: ÔB9 đựng giá trị tổng thể liệu.rows:Ô phía bên trên ô cất giá trị tổng và nằm cạnh phải, bắt buộc phải là số âm -1.Cols:Nhập 0 vì chúng ta không muốn thay đổi cột.

Từ công thứcmẫu hàm SUM phối hợp hàm OFFSET:

=SUM(first cell:OFFSET(cell with total, -1,0))

Áp dụng vào vào ví dụ, ta sẽ có công thức hàm OFFSET như sau:

=SUM(B2:OFFSET(B9, -1,0))

Và họ thu được kết quả mĩ mãn.

*

Sử dụng hàm OFFSET tổng phù hợp N dòng cuối của một danh sách

Tiếp tục với ví dụ ở phần trên, mang sử thay vày tính tổng tiền thưởng toàn bộ các tháng,bạn chỉ ước ao chạy một công thức tự động tính tổng số chi phí thưởng mang đến N tháng ngay gần nhất.

Để có tác dụng được điều này, chúng ta sẽ thực hiện hàmOFFSETkết hợp với các hàmSUMvà hàmCOUNT/ COUNTA với phương pháp như sau:

=SUM(OFFSET(B1,COUNT(B:B)-E2+1,0, E2,1))

hoặc

=SUM(OFFSET(B1,COUNTA(B:B)-E2,0,E2,1)))

*

Trong cách làm hàm OFFSET:

reference: Nhập title của cột chứa những giá trị cần tính tổng, trong lấy ví dụ như này là ô B1.rows: thực hiện thêm hàm COUNT hoặc COUNTA để tính số hàng.cols: Nhập số cột điều chỉnh là 0.height: Nhập ô E1 cất số sản phẩm được tổng hợpwidth: Nhập 1 tương xứng với số cột.

Hàm COUNTtrả về số ô trong cột B có chứa cực hiếm số, vì thế bạn trừ đi N tháng cuối cùng (giá trị nghỉ ngơi trong ô E1) và thêm vào đó 1.

Xem thêm: Cách Chuyển Tài Khoản Coin Master Trên Android, Ios, Cách Chuyển Tài Khoản Coin Master

HàmCOUNTAtính toàn bộ các ô không đựng giá trị rỗng, với hàng tiêu đề cất giá trị văn bạn dạng là một ô thêm new mà công thức yêu cầu đến.Vậy nên những khi sử dụng hàm này, bạn không cần phải thêm 1. Lưu ý rằng bí quyết này đã chỉ hoạt động đúng chuẩn trên một cấu tạo bảngtương tự. Vị đó, đối với các cấu trúc bảng không giống nhau, chúng ta có thể cần thực hiện một số điều chỉnh trong cách làm OFFSET Excel / COUNTA nhằm kết phù hợp với các hàm tính toán và hàm tham chiếu vào Excel.

Sử dụng hàm OFFSET lồng vào hàm AVERAGE, MAX, MIN

Tương từ với cách tính tiền thưởng mang lại N tháng trong phần trên, bạn cũng có thể tính cực hiếm trung bình mang đến N ngày, N tuần hoặc N năm cuối cùng. Lân cận đó, bọn họ cũng có thể tìm các giá trị lớn nhất hoặc bé dại nhất trong bảng. Bằng phương pháp sử dụng hàm OFFSET, sự khác hoàn toàn duy nhất giữa các công thức để tính các yêu cầu trên chỉ là tên gọi của hàm đầu tiên:

Công thức hàm OFFSET lồng vào hàm AVERAGE để tính cực hiếm trung bình:=AVERAGE(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))Công thức hàm OFFSET lồng trong hàm MAX nhằm tìm giá trị lớn số 1 trong bảng:=MAX(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))Công thức hàm OFFSET lồng vào hàm MAX nhằm tìm giá bán trị bé dại nhất vào bảng:=MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))

*

Công thức này không cần phải được cập nhật thủ công bằng tay nếu bạn thêm các biến đổi vào bảng tính gốc. Bạn sẽ không nên lo về việc Excel trả sai hiệu quả hay trả lỗi mỗi lần cập nhật dữ liệu nữa, bởi phương pháp hàm OFFSET luôn luôn luôn tham chiếu cho ô sau cùng trong cột.

Sử dụng hàm OFFSET phối hợp hàm COUNTA để tạo thành dải động

Hàm OFFSET kết hòa hợp hàm COUNTA có thể giúp bạn tạo một dải cồn vô thuộc hữu ích trong những trường hợp bạn có nhu cầu tạo danh sách auto cập nhật. Việc bạn phải làm là để tên đến dải, sau đó tạo một list theo mục để tiện lợi thao tác.

Đầu tiên, họ cần xác minh và để tên cho dải. Chúng ta vào tab Formula > Defined Names > Define Name cùng nhập công thức hàm OFFSET phối hợp hàm COUNTA như sau:

=OFFSET(Sheet_Name!$A$1,0,0,COUNTA(Sheet_Name!$A:$A),1)

Trong phương pháp hàm OFFSET:

reference:Được biểu diễn dưới dạng tên trang tính cùng theo sau là vết chấm than kèm theo add ô chứa mục đầu tiên có mặt trong vào dải (-đã được để tên). Bạn cần sử dụng các tham chiếu tuyệt đối hoàn hảo ($) đến tên trang tính, ví dụ như Sheet1!$A$1.Các thông số rowsvà colsđều là 0, vì không tồn tại cột hoặc mặt hàng nào có thêm vào.height: Đây làđiểm cơ bản trong công thức. Do họ sử dụng hàm COUNTA để đo lường số ô không rỗng vào cột tất cả chứa những mục của dải được đặt tên. Lưu ý điều này, bạn nên chỉ có thể định thương hiệu bảng kề bên tên cột, ví như COUNTA(Sheet_Name!$A:$A).width: là 1 cột.

*

Sau khi sẽ đặt tên mang lại dải, chúng tasử dụngExcel Data Validationđể tạo list theo mục, bảo đảm Excel luôn tự động hóa cập nhật ngay khi bạn thêm hoặc xoá các mục từ list nguồn (bảng tính gốc). Các bạn hãy vào tab Data > Data Tools > Data Validation hoặc thừa nhận mũi tên ở kề bên mục Data Validation > Data Validation...

*

Hộp thoại Data Validation hiện lên. Các bạn chọn List ở trong phần Allow và điền dải ô đề nghị tham chiếu vào phần Source, tại lấy ví dụ là dải A2:A8 đặt trong tham chiếu tuyệt đối ($).

*

Vậy là chúng ta đã bao gồm một dải rượu cồn hiển thị bên dưới dạng danh sách theo mục như sau đây rồi.

*

Sử dụng hàm OFFSET sửa chữa thay thế các hàm LOOKUP trong Excel

Sử dụng hàm OFFSET chũm hàm VLOOKUP để tra cứu phía bên trái trong Excel

Tại sao chúng ta lại thực hiện hàm OFFSET ráng hàm VLOOKUP? Mình để giúp đỡ bạn giải thích lý do nhé.

Hàm VLOOKUP vào Excel chỉ có công dụng trả lại một quý hiếm ở bên buộc phải cột tra cứu, đồng nghĩa với việc trong số những hạn chế lớn số 1 của hàm VLOOKUP là không có khả năng dò giá tốt trị phía bên trái của nó.

Chúng ta thường xuyên áp dụng ví dụ về bảng chi phí thưởng theo mon ở những phần trên. Ví như bạn chỉ cần tìm tìm tiền thưởng của một tháng nhất định, bí quyết hàm VLOOKUP cơ bạn dạng không hề gặp gỡ khó khăn gì:

=VLOOKUP(B10,A5:B11,2,FALSE)

*

Tuy nhiên, một khi bạn tráo đổi các cột vào bảng với nhau, hàm VLOOKUP sẽ chạm mặt lỗi #N/A như vậy này:

*

Đây là lúc chúng ta cần tìm về một hàm tham chiếu trong Excel khác linh hoạt hơn hoàn toàn như hàm INDEX phối kết hợp hàm MATCH. Mặc dù nhiên, chiến thuật chúng mình đề xuất đó là sử dụng hàmOFFSET kết hòa hợp hàm MATCH cùng hàm ROWS với cách làm như sau:

=OFFSET(lookup table, MATCH(lookup value,OFFSET(lookup table,0,1, ROWS(lookup table),1),0)-1,0,1,1)

Áp dụng cách làm vào lấy một ví dụ của chúng ta, phạm vi bảng tra cứu là A5: B11, giá bán trị đề nghị tìm phía trong ô B1. Ta bao gồm công thức ví dụ của hàm OFFSET như sau:

=OFFSET(A5:B11,MATCH(B1,OFFSET(A5:B11,0,1,ROWS(A5:B11),1),0)-1,0,1,1)

Nhìn qua công thức trên, chúng ta sẽ nghĩ về nó thiệt phức tạp. Tuy vậy vậy, chính vì sự phức tạp ấy sẽ đem lại tác dụng tuyệt vời cho quá trình tra cứu của chúng ta đấy.

*

Sử dụng hàm OFFSET cụ hàm HLOOKUP tra cứu trên trong Excel

Tương tự như hàm VLOOKUPkhông thể dò tìm giá bán trị mặt trái, hàm HLOOKUP cấp thiết dò giá chỉ trị trên hàng cất giá trị tham chiếu trong phạm vi tài liệu để xuất ra một giá bán trị.

Do vậy, hàng đựng giá trị cần tìm nằm ở phía trên hàng đựng tên giá chỉ trị cần tìm, chúng ta hãy áp dụng công thức hàm OFFSET phối kết hợp hàm MATCH với hàm ROWS tương tự như phần trên. Mặc dù nhiên, đối với trường phù hợp này, bạn cần thêm hàm COLUMNS để bí quyết dò kiếm tìm như sau:

=OFFSET(lookup table,0,MATCH(lookup value,OFFSET(lookup table,ROWS(lookup table)-1,0,1,COLUMNS(lookup table)),0)-1,1,1)

Giả sử rằng bảng tra cứu là E4:K5 và cực hiếm tra cứu là trong ô E1, bí quyết đi như sau:

=OFFSET(E4:K5,0,MATCH(E1,OFFSET(E4:K5,ROWS(E4:K5)-1,0,1,COLUMNS(E4:K5)),0)-1,1,1)

Một lần nữa, hàm OFFSET lại phối hợp mượt nhưng với những hàm tham chiếu không giống để làm cho công thức công dụng khắc phục điểm yếu kém của hàm LOOKUP.

*

Sử dụng hàm OFFSET thay các hàm LOOKUP tra cứu giúp hai chiều vào Excel

Tra cứu vãn hai chiều trong Excel nghĩa là chúng ta tìm kiếm tài liệu tại vị trí giao điểm các hàng cùng cột. Chúng ta cũng có thể sử dụng bí quyết hàm OFFSET với mảng tra cứu 2 chiều như sau:

=OFFSET(lookup table,MATCH(row lookup value,OFFSET(lookup table, 0,0,ROWS(lookup table),1),0)-1,MATCH(column lookup value, OFFSET(lookup table,0,0,1,COLUMNS(lookup table)),0)-1)

Công thức trên quá dài cái và cực nhọc hiểu nếu họ không gồm một lấy ví dụ thực tế. Vị vậy, hãy cùng áp dụng kiến thức và kỹ năng vào trường hợp dưới đây: bảng chi phí thưởng của các nhân viên vào một cỗ phận.

*

Để áp dụng công thức trên vào ví dụ này, bọn họ xác định những yếu tố sau:

Bảng tra cứu (lookup table) là A5:G9Giá trị tương xứng trên những hàng là B2Giá trị cân xứng trên các cột là B1

Như vậy, họ có viết được công thức tra cứu hai chiều sau đây:

=OFFSET(A5:G9,MATCH(B2,OFFSET(A5:G9,0,0,ROWS(A5:G9),1),0)-1,MATCH(B1,OFFSET(A5:G9,0,0,1,COLUMNS(A5:G9)),0)-1)

Lưu ý: Đây là một trong công thức mảng, vị đó họ phải ấn tổng hợp phím Ctrl + Shift + Enter để Excel trả về kết quả chính xác.

*

Những yếu điểm của hàm OFFSET và biện pháp khắc phục

Nhược điểm của hàm OFFSET

Bên cạnh hầu hết lợi thế tuyệt đối của mình, hàm OFFSET trong Excel cũng có một số nhược điểm mà họ cần xem xét khi sử dụng.

Hàm OFFSET rất có thể khiến Excel chạy chậm

Hàm OFFSETlà một hàm tham chiếu vào Excel luôn luôn luôn “khát dữ liệu”, nghĩa là chỉ việc một cầm đổi nhỏ tại bất kể ô làm sao trong phạm vi tham chiếu, các công thức hàm tham chiếu của người tiêu dùng sẽ tự động hóa chạy lại để update kết quả. Điều này rất có thể khiến Excel tốn thêm thời gian nếu khách hàng có rất nhiều công thức hàm OFFSET trong trang tính của mình.

Hàm OFFSET hoàn toàn có thể gây trở ngại cho việc sửa lỗi hàm

Công thức hàmOFFSETrất nặng nề để chúng ta kiểm tra lại. Lí do chính đến từ những việc các tham chiếu được trả lại vày hàm OFFSET là động. Đối với những công thức phức tạp, nguy hại cao là họ sẽ tốn rất nhiều thời gian nhằm tìm và sửa lỗi đấy.

Các chiến thuật khắc phục điểm yếu của hàm OFFSET vào Excel

Sử dụng qui định bảng trong Excel

Bạn chỉ cần nhập một cách làm hàm OFFSET vào một ô trong bảng, sau đó xào nấu ô xuống những ô phía dưới. Vậy là bạn đã có thể tạo thành một bảng Excel với 1 cột cách làm giống nhau nhưng có sự thay đổi dữ liệu cân xứng cho mỗi hàng.

Tuyệt vời hơn, bất kỳ công thức như thế nào tham chiếu đến tài liệu của bảng mà có điều chỉnh tự động sẽ bao gồm tất cả các hàng new nào chúng ta thêm vào bảng hoặc thải trừ các hàng đã được xóa. Về phương diện kỹ thuật, các công thức như vậy chuyển động trên những cột hoặc mặt hàng của bảng, gọi là phần nhiều dải động.

Mỗi bảng vào trang tính Excel gồm một tên độc nhất vô nhị mặc định là Table1, Table2,… nhưng các bạn hoàn toàn hoàn toàn có thể đổi tên bảng của mình bằng cách vào tabDesign> Properties group> Table Name.

*

Hình hình ảnh dưới phía trên thể hiện bí quyết SUM gồm tham chiếu cho cột Thưởng vào Bảng 3. Hãy chăm chú rằng cách làm này có bao gồm tên cột của bảng thay bởi vì một dải ô.

*

Sử dụng hàm INDEX trong Excel

Mặc dù tính năng không tương đồng hàmOFFSET, nhưnghàm INDEXcũng rất có thể được cần sử dụng làm hàm tham chiếu trong Excel đôi với những dải động. Điểm mạnh mẽ của hàm INDEX đối với hàm OFFSET là hàm INDEX ko tự thế đổi, vày vậy nó sẽ không làm chậm rì rì Excel của bạn.

Sử dụng hàm INDIRECT vào ExcelHàmINDIRECTgiúp chúng ta tạo tham chiếu mang lại dải động từ nhiều nguồn như các ô giá chỉ trị, quý hiếm và văn bạn dạng của ô, và các dải ô được đặt tên. Xung quanh ra, nó cũng có thể có thể tự động tham chiếu một bảng tính hoặc một trang tính Excel.

Tổng kết

Có thể nói hàm OFFSET là hàm tham chiếu vào Excel lợi hại bậc nhất khi kết hợp với các hàm khác. Vị vậy, nhằm sử dụng hiệu quả hàm OFFSET, các bạn cần nắm vững công thức của những hàm tham chiếu với hàm đo lường và tính toán trong Excel. Để làm cho được điều này, hãy xem thêm các bài viết về hàm Excel trên blog basissport.store và nhanh tay đăng kí khóa đào tạo Tuyệt đỉnh Excel cùng bọn chúng mình nhé.