Trong những hàm tham chiếu vào Excel, hàm OFFSET là một trong những hàm rất là lợi hại bởi vì nó rất có thể tạo nên những màn kết hợp ăn ý với những hàm tham chiếu cũng như các hàm đo lường khác. Vậy làm vậy nào để áp dụng hàm OFFSET hiệu quả? Hãy cùng đi kiếm hiểu cùng với daihiepkhach.vn nhé.

Bạn đang xem: Hàm offset và match trong excel

Tuyệt đỉnh Excel - phát triển thành bậc thầy Excel trong 16 giờ

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

Công thức hàm OFFSET

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


=OFFSET(reference, rows, cols, , )

Trong công thức chúng ta nhìn thấy 3 đối số reference, rows cùng 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 cùng width, là 2 đối số tùy chọn, nghĩa là fan dùng rất có thể nhập hoặc quăng quật qua.

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

Lưu ý: Cả nhị đối số height cùng width luôn luôn phải là số dương. Nếu một trong hai bị quăng quậ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 bí quyết hàm OFFSET được ra mắt phía trên, chúng ta hãy cùng tìm hiểu một ví dụ ví dụ nhé.

Hình dưới đây diễn tả cách OFFSET Excel trả về hiệu quả từ bí quyết đượ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 gọi là rước ô tham chiếu bước đầu từ điểm gốc 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 sang phải. Như vậy, ô tham chiếu của họ là ô B4.

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

*

Về bạn dạng chất, họ vẫn thực hiện công thức OFFSET vào Excel như tại đoạn trên. Mặc dù nhiên, trên đối số rows, thay bởi điền số 3, họ điền ô E1. Excel sẽ tự động hóa hiểu chúng ta nhập dữ liệu trong ô E1 vào đối số rows trong công thức hàm OFFSET. Vị ô E1 tất cả chứa số 3 nên hoàn toà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 ghi nhớ về hàm OFFSET

HàmOFFSETtrong Excel không di chuyển bất kỳ ô 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ề công dụng là một dải ô, các đối số rows với cols luôn luôn luôn đề cập mang lại ô bên trên bên trái.Hàm OFFSET 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 những 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 có một ví như sau: nếu bạn sử dụng bí quyết =OFFSET(A1,3,1,1,3) thì sẽ thấy thông báo lỗi #VALUE!. Lí do bởi vì Excel quan trọng trả một dải chứa 1 sản phẩm 3 cột về 1 ô duy nhất.

Tuy nhiên, phương pháp hàm bên trên sẽ vận động mượt mà khi chúng ta lồng cách làm trên vào trong hàm SUM. Thực hiện công thức:

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

Như vậy, bọn họ thu được kêt quả đó là tổng dải ô tham chiếu liền kề được xác minh bởi hàm OFFSET.

*

Các ngôi 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, nghĩa là nó luôn luôn tham chiếu cho phạm vi cố kỉnh định. Vì chưng đó, bạn không thể thay đổi phạm vi tham chiếu.

Nếu bạn cần thường xuyên update dữ liệu nằm trong bảng Excel nhưng không thích làm ảnh hưởng đến tác dụng hàm tham chiếu vào Excel, hàm OFFSET là sự việc lựa lựa chọn tuyệt vời. Nó cho phép bạn tạo các dải động, nên lúc bạn chuyển đổi dữ liệu bảng tính giỏi 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 từ ô gốc bằng hàm OFFSET trong Excel

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

Cách áp dụng hàm OFFSET

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

Ở những phần trên, bọn họ đã nhắc đến một ví dụ đơn giản về cách thực hiện hàm OFFSET lồng trong hàm SUM rồi. Vậy cụ thể hơn cách kết hợp này làm cho được gì? thuộc nhau mày mò nhé.

Sử dụng công thức hàm SUM phối hợp hàm OFFSET cầm đổ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 bắt buộc phải thay thế sửa chữa hàm SUM bằng tay 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ử, bọn họ có bảng tính Excel phía bên dưới được cập nhật hàng tháng. Điều này nghĩa là bảng tính của chúng ta biến đổi liên tục. Để tính tổng chi phí thưởng qua các tháng, bọn chúng tacần một hàm SUM ưa thích ứng cùng với những biến đổi trong bảng. Thay bởi vì dùng hàm SUM cơ phiên bản cần cập nhật hàm thủ công, bí quyết hàm SUM phối kết hợp hàm OFFSET tiện lợi hơn hết sức nhiều.

*

*

Bạn nhập thẳng ô trước tiên của dải đề nghị tính tổng vào đối số trong công thức hàm SUM, sau đó việc đề nghị làm chỉ cần đưa ra những 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 cuối của dải. Hãy nhập các tham số sau đây vào đối số trong phương pháp hàm OFFSET:

reference: ÔB9 cất giá trị tổng thể liệu.rows:Ô phía 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 chuyển đổi 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 trong ví dụ, ta sẽ sở hữu được công thức hàm OFFSET như sau:

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

Và bọn họ thu được công dụng mĩ mãn.

*

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

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

Để làm cho được điều này, họ sẽ sử dụng 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 phương pháp hàm OFFSET:

reference: Nhập title của cột chứa các giá trị buộc phải tính tổng, trong lấy ví dụ như này là ô B1.rows: áp dụng 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 chứa số sản phẩm được tổng hợpwidth: Nhập 1 khớp ứng với số cột.

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

Xem thêm:

HàmCOUNTAtính toàn bộ các ô không cất 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 đề nghị đến.Vậy nên khi sử dụng hàm này, bạn không cần thiết phải thêm 1. Lưu ý rằng bí quyết này đang chỉ hoạt động đúng chuẩn trên một cấu trúc bảngtương tự. Vì đó, so với các kết cấu bảng khác nhau, chúng ta có thể cần thực hiện một trong những đ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 trong Excel.

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

Tương trường đoản cú với phương pháp 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 quý hiếm trung bình cho N ngày, N tuần hoặc N năm cuối cùng. Cạnh bên đó, họ cũng có thể tìm các giá trị lớn số 1 hoặc nhỏ nhất vào bảng. Bằng cách sử dụng hàm OFFSET, sự khác biệt duy tuyệt nhất giữa các công thức để tính những yêu mong trên chỉ là tên gọi của hàm đầu tiên:

Công thức hàm OFFSET lồng trong hàm AVERAGE nhằm tính quý giá 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á bán 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á chỉ trị bé dạ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 cần phải được cập nhật thủ công nếu chúng ta thêm các đổi khác vào bảng tính gốc. Bạn sẽ không đề nghị lo về việc Excel trả sai công dụng hay trả lỗi từng lần update dữ liệu nữa, bởi phương pháp hàm OFFSET 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 ra dải động

Hàm OFFSET kết hợp hàm COUNTA có thể giúp 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 tự động cập nhật. Việc bạn cần làm là đặt tên mang đến dải, tiếp nối tạo một danh sách theo mục để dễ ợt thao tác.

Đầu tiên, họ cần xác minh và để tên mang đến dải. Bọn họ vào tab Formula > Defined Names > Define Name với 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 bí quyết hàm OFFSET:

reference:Được màn trình diễn dưới dạng tên trang tính cùng theo sau là lốt chấm than kèm theo địa chỉ cửa hàng ô đựng mục đầu tiên xuất hiện trong trong dải (-đã được để tên). Bạn cần sử dụng những tham chiếu hoàn hảo và tuyệt vời nhất ($) đến tên trang tính, ví dụ Sheet1!$A$1.Các tham số rowsvà colsđều là 0, vì không tồn tại cột hoặc sản phẩm nào có thêm vào.height: Đây làđiểm chủ yếu trong công thức. Do chúng ta sử dụng hàm COUNTA để thống kê giám sát số ô ko rỗng vào cột gồm chứa những mục của dải được đặt tên. Xem xét điều này, bạn nên 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 trong những cột.

*

Sau khi sẽ đặt tên đến dải, bọn chúng tasử dụngExcel Data Validationđể tạo danh sách theo mục, bảo đảm an toàn Excel luôn auto cập nhật ngay khi bạn thêm hoặc xoá những 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 ở 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 ở đoạn Allow cùng điền dải ô bắt buộc tham hấp thụ vào phần Source, tại lấy một ví dụ là dải A2:A8 đặt trong tham chiếu tuyệt vời và hoàn hảo nhất ($).

*

Vậy là họ đã tất cả một dải hễ hiển thị dưới dạng danh sách theo mục như sau đây rồi.

*

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

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

Tại sao bọn họ lại áp dụng hàm OFFSET gắng hàm VLOOKUP? Mình để giúp đỡ bạn phân tích và lý giải lý bởi nhé.

Hàm VLOOKUP trong Excel chỉ có tác dụng trả lại một quý giá ở bên bắt buộc cột tra cứu, đồng nghĩa tương quan 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ò được giá trị bên trái của nó.

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

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

*

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

*

Đây là lúc chúng ta cần tìm tới một hàm tham chiếu vào Excel khác linh hoạt hơn như là hàm INDEX phối hợp hàm MATCH. Tuy 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 phương pháp vào ví dụ như của chúng ta, phạm vi bảng tra cứu là A5: B11, giá bán trị nên tìm phía trong ô B1. Ta gồm công thức rõ ràng 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 sự phức tạp ấy sẽn mang lại tác 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 nuốm hàm HLOOKUP tra cứu bên 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ần yếu dò giá bán trị trên hàng cất giá trị tham chiếu vào phạm vi dữ liệu để xuất ra một giá chỉ trị.

Do vậy, hàng đựng giá trị yêu cầu tìm ở ở phía bên trên hàng chứa tên giá chỉ trị buộc phải 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 cùng hàm ROWS tựa như như phần trên. Mặc dù nhiên, so với trường thích hợp này, bạn phải thêm hàm COLUMNS để công thức 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 vãn là E4:K5 và quý hiếm tra cứu là trong ô E1, cách làm đ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 kết hợp mượt nhưng mà với các hàm tham chiếu không giống để 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 giúp hai chiều trong Excel

Tra cứu giúp hai chiều vào Excel nghĩa là chúng ta tìm kiếm tài liệu tại vị trí giao điểm những hàng với cột. Chúng ta cũng có thể sử dụng phương pháp 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 thừa dài mẫu và nặng nề hiểu nếu bọn họ không gồm 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òa hợp dưới đây: bảng chi phí thưởng của các nhân viên vào một bộ phận.

*

Để áp dụng công thức bên trên vào lấy 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ị tương xứng trên những cột là B1

Như vậy, chúng ta có viết được cách làm 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à 1 trong những công thức mảng, vày đó chúng ta phải ấn tổ hợp phím Ctrl + Shift + Enter nhằm Excel trả về kết quả chính xác.

*

Những điểm yếu kém của hàm OFFSET và bí quyết khắc phục

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

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

Hàm OFFSET 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 ráng đổi bé dại tại bất kể ô nào trong phạm vi tham chiếu, những công thức hàm tham chiếu của chúng ta sẽ tự động hóa 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 bạn có rất nhiều công thức hàm OFFSET vào trang tính của mình.

Hàm OFFSET hoàn toàn có thể gây khó khăn cho vấn đề sửa lỗi hàm

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

Các chiến thuật khắc phục nhược điểm của hàm OFFSET trong Excel

Sử dụng phép tắc bảng vào Excel

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

Tuyệt vời hơn, ngẫu nhiên công thức nào tham chiếu đến dữ liệu của bảng mà gồm điều chỉnh auto sẽ bao hàm tất cả các hàng mới nào bạn 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, những công thức như vậy hoạt động trên những cột hoặc hàng của bảng, hotline là đều dải động.

Mỗi bảng vào trang tính Excel tất cả một tên độc nhất vô nhị mặc định là Table1, Table2,… nhưng chúng ta hoàn toàn có thể đổi thương hiệu 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 tất cả tham chiếu mang lại cột Thưởng trong Bảng 3. Hãy chú ý rằng cách làm này có bao gồm tên cột của bảng thay do một dải ô.

*

Sử dụng hàm INDEX trong Excel

Mặc dù tính năng không đồng nhất hàmOFFSET, nhưnghàm INDEXcũng rất có thể được cần sử dụng làm hàm tham chiếu vào Excel song 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ự vắt đổi, bởi vì vậy nó sẽ không còn làm chậm trễ Excel của bạn.

Sử dụng hàm INDIRECT vào ExcelHàmINDIRECTgiúp bọn họ tạo tham chiếu mang đến dải động từ khá nhiều nguồn như các ô giá chỉ trị, quý hiếm và văn bản của ô, và những dải ô được để tên. 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 trong Excel lợi hại số 1 khi kết hợp với các hàm khác. Bởi vậy, để 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. Để có tác dụng được điều này, hãy đọc thêm các bài viết về hàm Excel bên trên blog daihiepkhach.vn và nhanh tay đăng kí khóa học Tuyệt đỉnh Excel cùng chúng mình nhé.