Trong những hàm tham chiếu trong Excel, hàm OFFSET là 1 trong những hàm cực kỳ lợi hại bởi vì nó 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 và đo lường khác. Vậy làm cố gắng nào để thực hiện hàm OFFSET hiệu quả? Hãy cùng đi tìm kiếm hiểu cùng với socialgame.vn nhé.

Bạn đang xem: Kết hợp hàm vlookup và offset

Bạn sẽ xem: phối kết hợp hàm vlookup với offset

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 họ nhìn thấy 3 đối số reference, rows và cols là 3 đối số bắt buộc người tiêu dùng phải nhập. 2 đối số còn lại, height và width, là 2 đối số tùy chọn, nghĩa là tín đồ dùng hoàn toàn có thể nhập hoặc bỏ qua.

Các đối số yêu cầu trong công thức hàm OFFSETreference:Là một ô hoặc một dải ô liền kề mà kế tiếp nó rất có thể bị thêm/bớt. Nói phương pháp khác, đối số này khẳng định điểm làm gốc của cách làm hàm OFFSET.rows: Là số hàng dịch chuyển lên hoặc xuống từ bỏ điểm có tác dụng gốc (trong đối số reference). Nếu điền số dương vào vào đối số này, cách làm sẽ di chuyển xuống bên dưới tham chiếu. Ngược lại, trong trường hợp điền một số trong những âm vào đối số này thì công thức sẽ dịch rời lên bên 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ựa như như cùng với đối số rows, cols rất có thể là số dương nhằm công thức dịch rời sang bên phải tham chiếu, hoặc số âm để công thức dịch chuyển sang phía trái tham chiếu.Các đối số tùy chọn trong phương pháp hàm OFFSETheight: Làchiều cao tính thông qua 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 ngay số cột, mà bạn muốn tham chiếu trả về.

Lưu ý: Cả hai đối số height cùng width luôn luôn phải là số dương. Nếu một trong 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.

Để áp dụng được cách làm hàm OFFSET được giới thiệu phía trên, họ hãy cùng mày mò một ví dụ ví dụ nhé.

Hình bên dưới đây diễn tả cách OFFSET Excel trả về kết quả từ phương pháp đượ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 phát âm là lấy ô tham chiếu ban đầu từ điểm nơi bắt đầu A1, dịch chuyển ô tham chiếu3 hàng xuống dưới, tiếp đến dịch ô tham chiếu 1 cột quý phái phải. Như vậy, ô tham chiếu của chúng ta là ô B4.

Cùng tò mò một ví dụ không giống về hàm OFFSET nhé.


*

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

Những điều cần ghi lưu giữ về hàm OFFSET

HàmOFFSETtrong Excel không di chuyển ngẫu nhiên ô hoặc dải nào, cơ mà nó chỉ trả về một tham chiếu trong phạm vi dữ liệu.Đối với công thức hàmOFFSETtrả về hiệu quả là một dải ô, những đối số rows với cols luôn luôn đề cập đến ô phía trên bên trái.Hàm OFFSET rất có thể được phối 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 các đối số của nó.

Để minh họa cho các lỗi hoàn toàn có thể xảy ra trong quá trình họ sử dụng hàm OFFSET làm hàm tham chiếu vào Excel, bọn chúng mình có một ví như sau: nếu như khách hàng sử dụng phương pháp =OFFSET(A1,3,1,1,3) thì sẽ thấy thông báo lỗi #VALUE!. Lí do bởi vì Excel tất yêu trả một dải chứa 1 sản phẩm 3 cột về 1 ô duy nhất.

Tuy nhiên, bí quyết hàm trên sẽ hoạt động mượt mà khi chúng ta lồng bí quyết trên vào trong hàm SUM. Sử dụng công thức:

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

Như vậy, chúng ta 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 sử 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 thị nó luôn tham chiếu mang đến phạm vi cầm định. Bởi vì đó, các bạn không thể thay đổi phạm vi tham chiếu.

Nếu bạn phải thường xuyên cập nhật dữ liệu nằm trong bảng Excel nhưng không muốn làm tác động đến công dụng hàm tham chiếu vào Excel, hàm OFFSET là việc lựa lựa chọn tuyệt vời. Nó được cho phép bạn tạo những dải động, nên khi bạn thay đổi dữ liệu bảng tính hay thêm những hàng, cột new vào bảng, chúng ta vẫn sẽ tìm được dữ liệu mình cần.

Lấy dải trường đoản cú ô gốc bởi hàm OFFSET vào Excel

Với những bảng Excel có nhiều dữ liệu, bạn cũng có thể quên mất địa chỉ cửa hàng thực của tất cả dải ô nên tìm tìm thông tin. Tất cả những gì bạn nhớ là phạm vi search kiếm ban đầu từ một số ô rứa thể. Trong trường hợp này, hàm OFFSET sẽ hỗ trợ bạn khỏi câu hỏi mỏi mắt tra cứu dữ liệu đấy.

Cách sử dụng hàm OFFSET

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

Ở các phần trên, họ đã nói đến một ví dụ 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 cách làm hàm SUM phối hợp hàm OFFSET cầm cố đổi

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

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


*

*

Bạn nhập trực tiếp ô trước tiên của dải yêu cầu tính tổng vào đối số trong cách làm hàm SUM, tiếp nối việc cần làm chỉ cần đưa ra các tham số còn sót lại cho hàmOFFSET. Hàm tham chiếu trong Excelsẽ auto tham chiếu cho tới ô sau cùng 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 chứa giá trị toàn bô liệu.rows:Ô phía trên ô đựng giá trị tổng và nằm cạnh sát phải, cần phải là số âm -1.Cols:Nhập 0 vì chúng ta không muốn đổi khác cột.

Từ công thứcmẫu hàm SUM kết 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ẽ sở hữu được công thức hàm OFFSET như sau:

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

Và chúng ta thu được công dụng mĩ mãn.


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

Tiếp tục cùng với ví dụ tại đoạn trên, trả sử thay vày tính tổng chi phí thưởng tất cả các tháng,bạn chỉ hy vọng chạy một công thức tự động tính tổng số tiền thưởng cho N tháng sát nhất.

Để làm cho được điều này, chúng ta sẽ sử dụng hàmOFFSETkết hợp với các hàmSUMvà hàmCOUNT/ COUNTA với bí quyết 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 tiêu đề của cột chứa những giá trị đề xuất tính tổng, trong ví dụ như này là ô B1.rows: áp dụng thêm hàm COUNT hoặc COUNTA nhằm tính số hàng.cols: Nhập số cột kiểm soát và điều chỉnh là 0.height: Nhập ô E1 cất số hàng được tổng hợpwidth: Nhập 1 khớp ứng với số cột.

Xem thêm: Áo Dài Cách Tân Tết 2022 Giá Siêu Tốt, Áo Dài Cách Tân Giá Tốt Tháng 5, 2022

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

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

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

Tương trường đoản cú với cách tính tiền thưởng cho N mon trong phần trên, bạn cũng có thể tính cực hiếm trung bình mang lại N ngày, N tuần hoặc N năm cuối cùng. Kề bên đó, họ cũng có thể tìm các giá trị lớn số 1 hoặc nhỏ 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 tuyệt nhất giữa những công thức để tính các yêu cầu trên chỉ là tên của hàm đầu tiên:

Công thức hàm OFFSET lồng trong hàm AVERAGE để tính giá trị trung bình:=AVERAGE(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))Công thức hàm OFFSET lồng vào hàm MAX để 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 trong hàm MAX để tìm giá bán trị nhỏ tuổi nhất trong bảng:=MIN(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1))


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

Sử dụng hàm OFFSET kết hợp hàm COUNTA để tạo ra dải động

Hàm OFFSET kết hòa hợp hàm COUNTA rất có thể giúp các bạn tạo một dải động vô thuộc 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 phải làm là để tên cho dải, sau đó tạo một danh sách theo mục để dễ dàng thao tác.

Đầu tiên, họ cần xác định và để tên mang lại dải. Họ vào tab Formula > Defined Names > Define Name cùng 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)

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

reference:Được trình diễn dưới dạng tên trang tính với theo sau là lốt chấm than kèm theo showroom ô cất mục đầu tiên xuất hiện trong vào dải (-đã được để tên). Bạn phải sử dụng những 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 có cột hoặc mặt hàng nào nhận thêm vào.height: Đây làđiểm cốt lõi trong công thức. Do họ sử dụng hàm COUNTA để đo lường và thống kê số ô không rỗng vào cột tất cả chứa các mục của dải được đặt tên. Chú ý điều này, bạn nên chỉ có thể định tên bảng lân cận tên cột, ví như COUNTA(Sheet_Name!$A:$A).width: là một trong cột.


Sau khi vẫn đặt tên mang lại dải, chúng tasử dụngExcel Data Validationđể tạo list theo mục, bảo vệ Excel luôn tự động cập nhật ngay khi bạn thêm hoặc xoá các mục từ danh sách 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 ở kề bên mục Data Validation > Data Validation...


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


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

Sử dụng hàm OFFSET nạm hàm VLOOKUP nhằm tra cứu phía trái trong Excel

Tại sao chúng ta lại sử dụng hàm OFFSET gắng hàm VLOOKUP? Mình sẽ giúp đỡ bạn lý giải lý vì nhé.

Hàm VLOOKUP trong Excel chỉ có chức năng trả lại một quý giá ở bên cần cột tra cứu, đồng nghĩa với việc giữa những hạn chế lớn nhất 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 tháng ở những phần trên. Nếu như bạn chỉ cần tìm kiếm tiền thưởng của một tháng tốt nhất định, cách làm hàm VLOOKUP cơ bạn dạng không hề chạm mặt khó khăn gì:

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


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


Đây là lúc bạn cần tìm đến một hàm tham chiếu trong Excel không giống linh hoạt hơn hẳn như hàm INDEX phối hợp hàm MATCH. Tuy nhiên, chiến thuật chúng mình đề xuất chính là sử dụng hàmOFFSET kết hòa hợp hàm MATCH cùng hàm ROWS với phương pháp 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ông thức vào lấy một ví dụ của bọn chúng ta, phạm vi bảng tra cứu vãn là A5: B11, giá bán trị phải tìm bên trong ô B1. Ta gồm công thức cụ thể 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ách làm trên, họ sẽ suy nghĩ nó thiệt phức tạp. Tuy nhiên vậy, chính vì sự phức tạp ấy sẽ đem lại công dụng tuyệt vời cho quá trình tra cứu giúp của chúng ta đấy.


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

Tương tự như hàm VLOOKUPkhông thể dò tìm giá chỉ trị mặt trái, hàm HLOOKUP cần yếu dò giá bán trị bên trên hàng chứa 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ị nên tìm nằm ở bên trên hàng chứa tên giá chỉ trị buộc phải tìm, bạn hãy sử dụng công thức hàm OFFSET phối hợp hàm MATCH và hàm ROWS tương tự như phần trên. Tuy nhiên, so với trường hòa hợp này, bạn cần thêm hàm COLUMNS để bí quyết dò 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 vớt là trong ô E1, công thức đ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 kết hợp mượt mà lại với các hàm tham chiếu khác để tạo nên công thức hiệu quả khắc phục điểm yếu của hàm LOOKUP.


Sử dụng hàm OFFSET thay các hàm LOOKUP tra cứu vớt hai chiều trong Excel

Tra cứu hai chiều trong Excel nghĩa là họ tìm kiếm dữ liệu tại địa điểm giao điểm các hàng và cột. Chúng ta cũng có thể sử dụng cách làm hàm OFFSET cùng với mảng tra cứu 2d 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 vượt dài mẫu và khó khăn hiểu nếu họ không có một ví dụ như thực tế. Vì vậy, hãy cùng áp dụng kiến thức vào trường hợp dưới đây: bảng tiền thưởng của các nhân viên trong một cỗ phận.


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

Bảng tra cứu (lookup table) là A5:G9Giá trị cân xứng trên các hàng là B2Giá trị phù hợp trên các cột là B1

Như vậy, chúng ta có viết được bí quyết tra cứu vãn 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, bởi vì đó bọn họ phải ấn tổng hợp phím Ctrl + Shift + Enter nhằm Excel trả về hiệu quả chính xác.


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

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

Bên cạnh đầy đủ lợi thế hoàn hảo của mình, hàm OFFSET trong Excel cũng có một số điểm yếu kém mà bọn họ cần để ý 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 núm đổi nhỏ tại bất kể ô nào trong phạm vi tham chiếu, các công thức hàm tham chiếu của khách hàng sẽ auto chạy lại để update kết quả. Điều này hoàn toàn có thể khiến Excel tốn thêm thời gian nếu như bạn có không ít công thức hàm OFFSET trong trang tính của mình.

Hàm OFFSET rất có thể gây khó khăn cho việc sửa lỗi hàm

Công thức hàmOFFSETrất nặng nề để họ kiểm tra lại. Lí do thiết yếu đến từ các việc các tham chiếu được trả lại vị hàm OFFSET là động. Đối với những công thức phức tạp, nguy cơ cao là chúng ta sẽ tốn tương đối nhiều thời gian để tìm cùng 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 vẻ ngoài bảng vào Excel

Bạn chỉ việc nhập một cách làm hàm OFFSET vào trong 1 ô 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 nên một bảng Excel với cùng một cột bí quyết giống nhau nhưng có sự thay đổi dữ liệu cân xứng cho từng 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à gồm điều chỉnh tự động sẽ bao hàm tất cả các hàng new nào các bạn thêm vào bảng hoặc sa thải các hàng đã có xóa. Về khía cạnh kỹ thuật, các công thức như vậy chuyển động trên các cột hoặc mặt hàng của bảng, điện thoại tư vấn là phần nhiều dải động.

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


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


Sử dụng hàm INDEX trong Excel

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

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 phù hợp với các hàm khác. Bởi vì vậy, để sử dụng công dụng hàm OFFSET, chúng ta cần nắm rõ công thức của các hàm tham chiếu với hàm thống kê giám sát trong Excel. Để có tác dụng được điều này, hãy bài viết liên quan các bài viết về hàm Excel trên blog socialgame.vn và nhanh tay đăng kí khóa học Tuyệt đỉnh Excel cùng chúng mình nhé.