Hàm Offset Và Match Trong Excel

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 vì chưng nó hoàn toàn có thể tạo buộc phải những màn kết hợp ăn ý với các hàm tham chiếu cũng như các hàm giám sát khác. Vậy làm cầm nào để sử dụng hàm OFFSET hiệu quả? Hãy cùng đi kiếm hiểu cùng với annexsport.store nhé.

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

Tuyệt đỉnh Excel - vươn lên là bậc thầy Excel trong 16 giờ

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

Công thức hàm OFFSET trong Excel

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


=OFFSET(reference, rows, cols, , )

Trong công thức chúng ta nhìn thấy 3 đối số reference, rows với 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à người dùng có thể nhập hoặc vứt qua.

Các đối số nên trong cách làm hàm OFFSETrows: Là số hàng dịch chuyển lên hoặc xuống tự điểm làm gốc (trong đối số reference). Nếu như điền số dương vào vào đối số này, bí quyết sẽ dịch rời xuống phía bên dưới tham chiếu. Ngược lại, trong trường đúng theo điền một số trong những âm vào đối số này thì phương pháp sẽ dịch rời lên phía bên trên tham chiếu.cols: Là số cột bạn muốn công thức dịch rời từ điểm làm cho gốc (trong đối số reference). Tựa như như cùng với đối số rows, cols hoàn toàn có thể là số dương nhằm công thức dịch rời sang bên đề xuất tham chiếu, hoặc số âm để công thức dịch chuyển sang bên 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 muốn hàm tham chiếu vào Excel trả về kết quảwidth: Làchiều rộng, tính bằng 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 phải là số dương. Nếu một trong các hai bị quăng quật qua, thì Excel sẽ auto dùng height hoặc width của tham chiếu gốc.

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

Hình dưới đây miêu tả cách hàm OFFSET vào 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)

Hàm được gọi là đem ô tham chiếu bắt đầu từ điểm cội A1, dịch rời ô 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 bọn họ là ô B4.

Cùng khám phá một ví dụ khác nhé.

*

Về phiên bản chất, chúng ta vẫn sử dụng công thức hàm tham chiếu trong Excel như ở phần trên. Mặc dù nhiên, tại đối số rows, thay bởi điền số 3, chúng ta điền ô E1. Excel sẽ tự động hiểu 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 bao gồm chứa số 3 nên rất có thể nói, họ đã con gián tiếp nhập số 3 vào đối số rows.

Những vấn đề cần ghi nhớ về hàm OFFSET trong Excel

HàmOFFSETtrong Excel không di chuyển ngẫu nhiên ô hoặc dải nào, nhưng mà nó chỉ trả về một tham chiếu vào phạm vi dữ liệu.Đối với bí quyết hàmOFFSETtrả về tác dụng là một dải ô, các đối số rows với cols luôn luôn luôn đề cập cho ô phía trên bên trái.Hàm OFFSET rất 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 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 thừa trình chúng ta sử dụng hàm OFFSET làm hàm tham chiếu vào Excel, chúng mình tất cả một ví như sau: nếu như bạn sử dụng công thức =OFFSET(A1,3,1,1,3) thì sẽ thấy thông tin lỗi #VALUE!. Lí do bởi vì Excel cần thiết trả một dải đựng 1 sản phẩm 3 cột về 1 ô duy nhất.

Tuy nhiên, công thức hàm trên sẽ chuyển động mượt mà khi bạn lồng công thức trên vào vào hàm SUM. áp 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 cạnh bên được xác minh 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ởi hàm OFFSET vào Excel

Các tham chiếu trực tiếp ví như A1:B4 là tham chiếu tĩnh, tức thị nó luôn luôn tham chiếu cho phạm vi cố gắng định. Vày đó, chúng ta không thể đổi khác 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 ảnh hưởng đến hiệu quả hàm tham chiếu trong Excel, hàm OFFSET là sự lựa lựa chọn tuyệt vời. Nó có thể chấp nhận được bạn tạo những dải động, nên khi bạn biến hóa dữ liệu bảng tính tuyệt thêm các hàng, cột mới vào bảng, bạn vẫn sẽ tìm kiếm được dữ liệu mình cần.

Lấy dải từ bỏ ô gốc bằng hàm OFFSET vào Excel

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

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

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

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

Sử dụng cách làm hàm SUM phối hợp hàm OFFSET nắ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 các ô tham chiếu trong hàm OFFSET trong cả khi bảng tính được đổi khác và cập nhật dữ liệu liên tục. Như vậy, bọn họ không nên phải sửa chữa thay thế hàm SUM thủ công 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ử, họ có bảng tính Excel phía dưới được update hàng tháng. Điều này tức thị bảng tính của bọn chúng ta biến đổi 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 đam mê ứng với những biến đổi trong bảng. Thay vì dùng hàm SUM cơ bản cần cập nhật hàm thủ công, cách làm hàm SUM kết hợp hàm OFFSET thuận lợi hơn cực kỳ nhiều.

*

*

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

reference: ÔB9 cất giá trị tổng thể liệu.rows:Ô phía bên trên ô chứa giá trị tổng và nằm bên phải, bắt buộc phải là số âm -1.Cols:Nhập 0 vì các bạn không muốn biến hóa 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ẽ sở hữu được công thức trông như sau:

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

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

*

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

Tiếp tục cùng với ví dụ ở chỗ trên, giả sử thay bởi tính tổng tiền thưởng tất cả các tháng,bạn chỉ muốn chạy một công thức auto tính tổng số tiền thưởng mang lại N tháng sát nhất.

Để làm đượ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 phương pháp hàm OFFSET:

reference: Nhập title của cột chứa những giá trị bắt buộc 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 nhằm tính số hàng.cols: Nhập số cột điều chỉnh là 0.height: Nhập ô E1 cất số mặt hàng được tổng hợpwidth: Nhập 1 tương xứng với số cột.

Xem thêm: Cách Đo Khoảng Cách Bằng Điện Thoại Iphone, Android Chính Xác

Hàm COUNTtrả về số ô trong cột B bao gồm chứa quý hiếm số, cho nên bạn trừ đi N tháng cuối cùng (giá trị làm việc trong ô E1) và cộng thêm 1.

HàmCOUNTAtính tất cả các ô không chứa giá trị rỗng, với hàng tiêu đề chứa giá trị văn phiên bản là một ô thêm new mà công thức phải đế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 phương pháp này vẫn chỉ hoạt động đúng đắ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, bạn có thể cần thực hiện một trong những điều chỉnh trong cách làm OFFSET / COUNTA để kết phù hợp với các hàm giám sát và hàm tham chiếu trong Excel.

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

Tương từ với phương pháp tính tiền thưởng cho N tháng trong phần trên, chúng ta cũng có thể tính quý hiếm trung bình mang đến N ngày, N tuần hoặc N năm cuối cùng. ở bên cạnh đó, họ cũng hoàn toàn có thể tìm những giá trị lớn số 1 hoặc bé dại nhất vào bảng. Bằng phương pháp sử dụng hàm OFFSET, sự khác hoàn toàn duy tốt nhất giữa những công thức để tính các 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 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 trong hàm MAX nhằm tìm giá chỉ 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ị nhỏ tuổ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 bằng tay thủ công nếu chúng ta thêm các biến hóa vào bảng tính gốc. Bạn sẽ không cần lo về việc Excel trả sai kết quả hay trả lỗi từng lần update dữ liệu nữa, bởi bí quyết hàm OFFSET luôn luôn luôn tham chiếu mang lại ô ở đầu cuối trong cột.

Sử dụng hàm OFFSET kết hợp hàm COUNTA để sinh sản 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 hễ vô cùng 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 phải làm là đặt tên cho dải, sau đó tạo một danh sách theo mục để tiện lợi thao tác.

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

reference:Được màn trình diễn dưới dạng thương hiệu trang tính với theo sau là vệt chấm than kèm theo showroom ô cất 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 vời 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: Đây làđiểm chính 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 có chứa các mục của dải được đặt tên. Lưu ý điều này, bạn nên có thể định tên bảng bên cạnh tên cột, ví dụ như COUNTA(Sheet_Name!$A:$A).width: là 1 trong những cột.

*

Sau khi đã đặt tên cho dải, chúng tasử dụngExcel Data Validationđể tạo danh sách theo mục, bảo đảm an toàn 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). Các bạn hãy vào tab Data > Data Tools > Data Validation hoặc thừa nhận mũi tên bên cạnh mục Data Validation > Data Validation...

*

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

*

Vậy là bọn họ đã bao gồm một dải cồn hiển thị dưới dạng danh sách theo mục như dưới đâ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 nắm hàm VLOOKUP nhằm tra cứu phía trái trong Excel

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

Chúng ta tiếp tục áp dụng ví dụ như về bảng tiền thưởng theo tháng ở 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 độc nhất vô nhị định, phương pháp hàm VLOOKUP cơ phiên bản không hề chạm chán 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ẽ gặp lỗi #N/A như thế này:

*

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

*

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

Tương trường đoản cú như hàm VLOOKUPkhông thể dò tìm giá trị mặt trái, hàm HLOOKUP chẳng thể dò giá trị trên hàng chứa giá trị tham chiếu trong phạm vi dữ liệu để xuất ra một giá trị.

Do vậy, hàng chứa giá trị đề nghị tìm nằm ở phía trên hàng cất tên giá trị yêu cầu tìm, chúng ta hãy sử 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. Tuy nhiên, đối với trường hợp này, bạn cần thêm hàm COLUMNS để cách làm 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ớt là E4:K5 và cực 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 phối 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 tác dụng 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ãn hai chiều trong Excel

Tra cứu giúp 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 những hàng với cột. Bạn cũng có thể sử dụng phương pháp 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 thừa dài loại và cạnh tranh hiểu nếu chúng ta không có một ví dụ như thực tế. Do vậy, hãy thuộc áp dụng kiến thức vào trường phù 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.

*

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

Bảng tra cứu giúp (lookup table) là A5:G9Giá trị phù hợp trên các hàng là B2Giá trị tương xứng trên những 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ì chưng đó bọn họ phải ấn tổng hợp phím Ctrl + Shift + Enter để Excel trả về hiệu quả chính xác.

*

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

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

Bên cạnh số đông 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 lưu ý khi sử dụng.

Hàm OFFSET hoàn toàn có thể khiến Excel chạy chậm

Hàm OFFSETlà một hàm tham chiếu trong Excel luôn luôn “khát dữ liệu”, nghĩa là chỉ cần một thế đổi bé dại tại bất kể ô làm sao trong phạm vi tham chiếu, những 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 để cập nhật kết quả. Điều này rất có thể khiến Excel tốn thêm thời gian nếu bạn có quá 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 trở ngại cho vấn đề sửa lỗi hàm

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

Các giải pháp khắc phục nhược điểm của hàm OFFSET trong Excel

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

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

Tuyệt vời hơn, ngẫu nhiên 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 gồm tất cả các hàng new nào bạn thêm vào bảng hoặc đào thải các hàng đã có đượ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 mặt hàng của bảng, gọi là đều dải động.

Mỗi bảng vào trang tính Excel có 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 có thể đổi tên bảng của mình bằng phương pháp vào tabDesign> Properties group> Table Name.

*

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

*

Sử dụng hàm INDEX vào Excel

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

Sử dụng hàm INDIRECT trong ExcelHàmINDIRECTgiúp họ tạo tham chiếu cho dải động từ rất nhiều nguồn như những ô giá trị, quý giá và văn bạn dạng của ô, và những dải ô được để tên. Kế bên ra, nó cũng có thể có thể auto 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 hàng đầu khi kết hợp với các hàm khác. Vày vậy, nhằm 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 giám sát và đo lường trong Excel. Để có tác dụng được điều này, hãy xem thêm các nội dung bài viết về hàm Excel bên trên blog annexsport.store và nhanh tay đăng kí khóa đào tạo Tuyệt đỉnh Excel cùng chúng mình nhé.

Chúc các bạn áp dụng con kiến thức bài viết thành công!

Tham khảo thêm những kiến thức vềExcel:

Hướng dẫn biện pháp dùng hàm index vào Excel chi tiết nhất

Hướng dẫn phương pháp copy chỉ phần lớn giá trị hiển thị sau khi lọc vào Excel

Hướng dẫn cách sắp xếp ngày vào Excel

Hướng dẫn cách dùng hàm VLOOKUP qua những ví dụ từ cơ phiên bản đến nâng cao

Hướng dẫn cách thực hiện hàm IF với nhiều điều kiện: AND, OR, hàm IF lồng nhau cùng hơn thế