Trong Excel hàm OFFSET được thực hiện để tham chiếu hay giám sát và đo lường dữ liệu một ô, một dãy trong bảng tính. Hàm này sẽ giám sát và đo lường dựa trên vùng tham chiếu được cho sẵn. Người tiêu dùng cũng rất có thể kết vừa lòng hàm OFFSET với những hàm khác liên quan trong Excel để hỗ trợ các bước trên bảng tính được dễ dàng và ngày tiết kiệm thời gian hơn. Trường hợp bạn chưa biết về cú pháp với cách áp dụng hàm OFFSET vào Excel hãy đọc ngay nội dung bài viết dưới đây từ Mega.
Bạn đang xem: Hàm offset dùng để làm gì
Cú pháp cùng cách sử dụng hàm OFSET?
Những để ý khi sử dụng hàm OFFSET trong excel
Hàm OFFSET rất có thể kết phù hợp với những hàm nào?
Công thức và cách sử dụng hàm OFFSET vào Excel đối kháng giản, dễ dàng thực hiện!
I. Hàm OFFSET là hàm gì? bí quyết hàm OFFSET trong Excel
Hàm OFFSET là hàm được dùng làm tham chiếu hay giám sát 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 đến sẵn trước đó.
Hàm OFFSET trong excel được sử dụng để đo lường và thống kê dữ liệu của một ô hay như là một dãy trong bảng tính cấp tốc và thuận lợi hơn. Ngoài 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 để hỗ trợ hoàn thành công việc nhanh nệm và tác dụng 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 ngân sách và chi phí được thời gian đáng nhắc khi thành thạo các loại hàm này.
Hàm này được triển khai trên hệ điều hành và quản lý Windows 10 cùng 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 cơ sở cho hàm để tạo ra vùng tham chiếu mới.
+ Rows: Là số dòng bên trên hoặc mặt dưới reference, tính từ ô thứ nhất (ô ở góc cạnh trên bên trái) của reference.
+ Cols: Là số cột phía bên trái hoặc mặt phải reference, tính từ bỏ ô thứ nhất (ô ở góc cạnh trên mặt trái) của reference.
+ Height: Là số chiếc của vùng tham chiếu đề xuất trả về. Height yêu cầu là số dương.
+ Width: Là số cột của vùng tham chiếu yêu cầu trả về. Width cần là số dương.
II. Những lưu ý khi sử dụng hàm OFFSET vào excel
Khi vận dụng công thức hàm OFFSET chúng ta cần chăm chú một số điểm sau:
Nếu bạn không nhập những đối số height và width thì tham chiếu trả về khoác đị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ố không âm).Hàm OFFSET không dịch rời hay làm nắm đổi bất kỳ phần nào được chọn 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 hợp với các hàm cần đến một đối số tham chiếu.
III. Cách sử dụng hàm OFFSET vào excel
Ví dụ 1: Sử dụng hàm OFFSET để mang ra quý hiếm ô tại đoạn cột 2 chiếc 3 vào bảng sau.
Bước 1: Trong bảng tài 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 công thức như trên
Nhấn Enter nhằm bảng tính hiển thị hiệu quả như hình dưới.
Kết trái hàm OFFSET trả về là 6000000
Ví dụ 2: sử dụng hàm OFFSET với hàm SUM để tính tổng của cột D làm việc bảng bên dưới.
Bước 1: Trong bảng dữ 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 hy vọng 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 di chuyển xuống bước đầu từ ô tham chiếu A3.3: Là số cột được dịch chuyển sang phải bắt đầu từ ô tham chiếu A3.5: Là tổng số sản phẩm lấy tài liệu tính kết quả.1: Là tổng số cột lấy dữ liệu tính kết quả.
Nhấn Enter tổng sẽ hiển thị như hình dưới.
Kết trái là 24000000
Ví dụ 3: Hàm OFFSET kết hợp với hàm AVERAGE, MAX, MIN
Bước 1: Để tính cực hiếm trung bình trong 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ị hiệu quả như hình dưới.
Với cách phối hợp này cũng vận dụng cho hàm MAX
Bước 2: Để tính số lớn nhất trong dải, bạn nhập hàm =MAX(OFFSET(B1,COUNT(B:B)-E2+1,0,E2,1)) như hình trên.
Nhấn Enter để hiển thị công dụng như hình
Bước 3: Để search số bé dạ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ị kết quả như hình dưới.
250000 là tác dụng thấp nhất
Ví dụ 4: Hàm OFFSET kết hợp với hàm VLOOKUP
Trong số đông cách sử dụng hàm offfset chúng ta cũng có thể kết vừa lòng 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 mong mỏi hiển thị kết quả.
Hàm OFFSET kết phù hợp với hàm VLOOKUP
Nhấn Enter để hiển thị công dụng 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 và 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 phù hợp với hàm MATCH
Trong đó:
+ $A$1: Ô cố định để khẳng định hàng cùng 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ừ ô A1 đến cột D (GIÁ BÁN) là 3.
Kết quả thật hình trên
Ví dụ 6: áp dụng hàm OFFSET phối kết hợp hàm COUNTA để tạo dải động
Hàm OFFSET kết vừa lòng hàm COUNTA hoàn toàn có thể giúp các bạn tạo một dải rượu cồn 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, tiếp nối tạo một list theo mục để tiện lợi thao tác.
Đầu tiên, bọn họ cần khẳng định và đặt tên mang đến dải. Vào tab Formula > Defined Names > Define Name
Thực hiện nay như hình trên
Sau kia 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)
Công thức hàm OFFSET phối hợp hàm COUNTA
Trong công thức hàm OFFSET:
reference:được màn biểu diễn dưới dạng tên trang tính và theo sau là vết chấm than kèm theo địa chỉ ô chứa mục đầu tiên có mặt trong trong dải. Bạn phải sử dụng những tham chiếu tuyệt vời ($) đến tên trang tính, lấy ví dụ 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 có thêm vào.height: Do chúng ta sử dụng hàm COUNTA để giám sát và đo lường số ô ko rỗng trong cột gồm chứa các mục của dải được để tên. Lưu ý điều này, bạn nên có thể định tên bảng sát bên tên cột, ví dụ như COUNTA(Sheet_Name!$A:$A).width: là một trong những cộtSau khi đã đặt tên đến dải, các bạn sử dụng Excel Data Validation để tạo list theo mục, bảo đảm Excel luôn tự động cập nhật ngay khi chúng ta 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 dìm mũi tên sát 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 đoạn Source
Kết quả bọn họ sẽ có một dải rượu cồn hiển thị bên dưới dạng list theo mục như hình dưới.
IV. Tổng kết
Bài viết trên giải đáp cách thực hiện hàm OFFSET trong Excel để tham chiếu và tính toán dữ liệu. Nếu các bạn hiểu cùng thành thạo hàm này sẽ cung cấp 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 ích với bạn, chúc bạn triển khai thành công!
Đừng quên truy vấn website mega.com.vn để cập nhật các nội dung bài viết mới nhất tương quan đến thủ thuật excel cũng giống như tin học tập văn phòng các bạn nhé!
Trong những hàm tham chiếu vào Excel, hàm OFFSET là 1 hàm cực kỳ lợi hại vì chưng nó có thể tạo đề xuất 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 cố gắng nào để sử dụng hàm OFFSET hiệu quả? Hãy cùng đi tìm hiểu với cdvhnghean.edu.vn nhé.
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 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 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 và 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 vứt qua.
Các đối số buộc phải trong phương pháp hàm OFFSETreference:Là một ô hoặc một dải ô tiếp giáp mà tiếp đến 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 cho gốc của cách làm hàm OFFSET.rows: Là số hàng di chuyển lên hoặc xuống từ điểm làm cho gốc (trong đối số reference). Giả dụ điền số dương vào vào đối số này, công thức sẽ di chuyển xuống bên dưới tham chiếu. Ngược lại, vào trường phù hợp điền một số trong những âm vào đối số này thì cách làm sẽ di chuyển 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). Giống như như với đối số rows, cols rất có thể là số dương nhằm công thức dịch chuyển sang bên bắt buộc tham chiếu, hoặc số âm nhằm công thức dịch rời sang bên trái tham chiếu.Các đối số tùy lựa chọn trong công thức hàm OFFSETheight: Làchiều cao tính bằng 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 muốn 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 1 trong các 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 phương pháp hàm OFFSET được trình làng phía trên, chúng ta hãy cùng tò mò một ví dụ cụ thể nhé.
Hình dưới đây biểu đạt cách OFFSET Excel trả về công dụng 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à đem ô tham chiếu bước đầ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 lịch sự phải. Như vậy, ô tham chiếu của chúng ta là ô B4.
Cùng khám phá một ví dụ khác về hàm OFFSET nhé.
Những điều cần ghi ghi nhớ về hàm OFFSET
HàmOFFSETtrong Excel không di chuyển ngẫu nhiên ô hoặc dải nào, mà nó chỉ trả về một tham chiếu trong phạm vi dữ liệu.Đối với cách làm hàm
OFFSETtrả về hiệu quả là một dải ô, những đối số rows và cols luôn luôn đề cập đến ô phía trên bên trái.Đối số reference phải là 1 trong ô hoặc một dải ô tức khắc kề, nếu như không Excel vẫn trả về lỗi #VALUE! lúc chạy hàm OFFSET.Nếu đối số rows với cols xác định một ô tham chiếu làm việc rìa của trang tính Excel, thì cách làm hàm tham chiếu vào Excel sẽ xuất hiện lỗi#REF!.Hàm OFFSET hoàn toàn có thể được phối kết hợp lồng trong bất kỳ hàm Excel nào chấp nhận một tham chiếu ô hoặc dải trong những đối số của nó.
Để minh họa cho những lỗi rất 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 có một ví dụ như sau: nếu bạn sử dụng phương pháp =OFFSET(A1,3,1,1,3) thì vẫn thấy thông báo lỗi #VALUE!. Lí do bởi vì Excel quan trọng trả một dải đựng 1 hàng 3 cột về 1 ô duy nhất.
Tuy nhiên, phương pháp hàm trên sẽ hoạt động mượt mà khi chúng ta lồng phương pháp 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, họ thu được kêt quả đó là tổng dải ô tham chiếu sát đượ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í như A1:B4 là tham chiếu tĩnh, nghĩa là nó luôn luôn tham chiếu mang đến phạm vi nạm định. Vì chưng đó, bạn không thể biến hóa phạm vi tham chiếu.
Nếu bạn cần thường xuyên cập nhật dữ liệu ở bảng Excel nhưng không thích làm ảnh hưởng đến hiệu quả 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ó được cho phép bạn tạo những dải động, nên khi bạn chuyển đổi dữ liệu bảng tính tốt thêm những hàng, cột new vào bảng, các bạn vẫn sẽ tìm 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ó khá nhiều dữ liệu, bạn có thể quên mất địa chỉ thực của cả dải ô yêu cầu tìm tìm thông tin. Toàn bộ những gì bạn nhớ rằng phạm vi search kiếm bắt đầu từ một vài ô thay thể. Trong tình huống này, hàm OFFSET sẽ cứu bạn khỏi bài toán mỏi đôi mắt tra cứu dữ 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 tới một ví dụ đơn giản về cách sử dụng 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 được gì? thuộc nhau tò mò nhé.
Sử dụng phương pháp hàm SUM phối hợp hàm OFFSET cầm cố đổiCông thức hàm SUM 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 đổi khác và cập nhật dữ liệu liên tục. Như vậy, chúng ta không phải 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ử, bọn họ có bảng tính Excel phía dưới được cập nhật hàng tháng. Điều này tức 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 những tháng, chúng tacần một hàm SUM thích hợp ứng với những chuyển đổi trong bảng. Thay vì chưng 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 hợp hàm OFFSET dễ dãi hơn hết sức nhiều.
OFFSET. Hàm tham chiếu vào Excelsẽ auto tham chiếu cho tới ô ở đầu cuối của dải. Hãy nhập những tham số tiếp sau đây vào đối số trong công thức hàm OFFSET:reference: ÔB9 đựng giá trị tổng thể liệu.rows:Ô phía bên trên ô chứa 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ì bạn 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à bọn họ thu được tác dụng mĩ mãn.
Tiếp tục cùng với ví dụ tại phần trên, trả sử thay bởi tính tổng tiền thưởng tất cả các tháng,bạn chỉ hy vọng chạy một công thức auto tính tổng số chi phí thưởng mang lại N tháng sát nhất.
Để làm được điều này, họ sẽ sử dụng hàm
OFFSETkết hợp với các hàm
SUMvà hàm
COUNT/ COUNTA với công thức 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 tiêu đề của cột chứa các giá trị yêu cầu tính tổng, trong lấy ví dụ này là ô B1.rows: sử dụng thêm hàm COUNT hoặc COUNTA để 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 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 gồm chứa quý giá số, vì vậy bạn trừ đi N tháng sau cuối (giá trị sống trong ô E1) và cộng thêm 1.
Hàm
COUNTAtính tất cả các ô không đựng giá trị rỗng, và hàng tiêu đề đựng giá trị văn bản là một ô thêm new mà công thức bắt buộc đế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 công thức này vẫn chỉ hoạt động đúng chuẩn trên một cấu trúc bảngtương tự. Bởi vì đó, đối với các kết cấu bảng khác nhau, bạn cũng có thể cần thực hiện một số trong những điều chỉnh trong bí quyết OFFSET Excel / COUNTA để kết 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 phương pháp tính tiền thưởng mang đến N mon trong phần trên, bạn có thể tính quý giá trung bình đến N ngày, N tuần hoặc N năm cuối cùng. Sát bên đó, bọn họ cũng rất có thể tìm những 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 độc nhất giữa các 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 vào hàm AVERAGE để 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á 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 thiết phải được cập nhật thủ công bằng tay nếu chúng ta thêm các biến đổi vào bảng tính gốc. Bạn sẽ không yêu cầu lo về việc Excel trả sai công dụng hay trả lỗi mỗi lần update dữ liệu nữa, bởi bí quyết 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 để chế tác dải động
Hàm OFFSET kết đúng theo hàm COUNTA rất có thể giúp các bạn tạo một dải hễ vô cùng hữu ích trong số trường hợp bạn muốn tạo danh sách tự động hóa cập nhật. Việc bạn phải làm là đặt tên cho dải, kế tiếp tạo một danh sách theo mục để tiện lợi thao tác.
Đầu tiên, chúng ta cần xác minh và đặt tên đến dải. Chúng ta vào tab Formula > Defined Names > Define Name và nhập bí quyết hàm OFFSET 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 biểu diễn dưới dạng thương hiệu trang tính và theo sau là dấu chấm than kèm theo add ô chứa mục đầu tiên có mặt trong vào dải (-đã được đặt tên). Bạn cần sử dụng các tham chiếu tuyệt vời ($) mang lại 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 hàng nào nhận thêm vào.height: Đây làđiểm chủ quản trong công thức. Do chúng ta sử dụng hàm COUNTA để đo lường và thống kê số ô không rỗng trong cột bao gồm chứa những mục của dải được để tên. Xem xét điều này, bạn nên chỉ định tên bảng sát bên tên cột, ví dụ như COUNTA(Sheet_Name!$A:$A).width: là một trong cột.
Excel Data Validationđể tạo danh sách theo mục, bảo vệ Excel luôn auto 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 nhấn mũi tên bên cạnh mục Data Validation > Data Validation…
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 cầm hàm VLOOKUP để tra cứu bên trái trong ExcelTại sao chúng ta lại thực hiện hàm OFFSET thế hàm VLOOKUP? Mình sẽ giúp đỡ bạn phân tích và lý giải lý do nhé.
Hàm VLOOKUP vào Excel chỉ có khả năng trả lại một giá trị ở bên bắt buộc cột tra cứu, đồng nghĩa với việc trong số 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 liên tục áp dụng ví dụ về bảng tiền thưởng theo tháng ở các phần trên. Nếu bạn chỉ cần tìm kiếm tiền thưởng của một tháng độc nhất vô nhị định, công thức 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 những cột trong bảng với nhau, hàm VLOOKUP sẽ gặp lỗi #N/A như thế này:
Đây là lúc bạn cần tìm đến một hàm tham chiếu vào Excel không giống linh hoạt hơn 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 mình đề xuất đó là sử dụng hàm
OFFSET kết thích hợp 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 ví dụ như của chúng ta, phạm vi bảng tra cứu giúp là A5: B11, giá trị cần tìm phía bên trong ô B1. Ta có 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ách làm trên, bọn họ sẽ nghĩ nó thiệt phức tạp. Tuy nhiên vậy, chính 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 chúng ta đấy.
Tương từ bỏ như hàm VLOOKUPkhông thể dò tìm giá bán trị bên trái, hàm HLOOKUP cần thiết dò giá chỉ trị bên trên hàng đựng 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 chứa giá trị nên tìm ở ở phía bên trên hàng cất tên giá bán trị phải tìm, chúng ta hãy thực hiện công thức hàm OFFSET phối hợp hàm MATCH với hàm ROWS giống như như phần trên. Tuy nhiên, đối với trường hòa hợp này, bạn phải 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 giúp 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 những hàm tham chiếu không giống để khiến cho công thức hiệu quả khắc phục điểm yếu kém của hàm LOOKUP.
Tra cứu giúp hai chiều trong Excel nghĩa là chúng ta tìm kiếm dữ liệu tại địa điểm giao điểm những hàng và cột. Chúng ta có thể sử dụng công thức hàm OFFSET 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 chiếc và khó khăn hiểu nếu bọn họ không có một ví dụ thực tế. Vị vậy, hãy cùng áp dụng kỹ năng và kiến thức vào trường hợp dưới đây: bảng tiền thưởng của những nhân viên trong một cỗ phận.
Để áp dụng công thức bên trên vào lấy một ví dụ này, bọn họ xác định những yếu tố sau:
Bảng tra cứu giúp (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à B1Như vậy, họ có viết được bí quyết tra cứu giúp 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 công thức mảng, bởi đó bọn họ phải ấn tổng hợp phím Ctrl + Shift + Enter nhằm Excel trả về tác dụng chính xác.
Những yếu điểm của hàm OFFSET và cách khắc phục
Nhược điểm của hàm OFFSET
Bên cạnh rất nhiều 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à bọn họ cần lưu ý khi sử dụng.
Hàm OFFSET rất có thể khiến Excel chạy chậmHàm OFFSETlà một hàm tham chiếu trong Excel luôn luôn “khát dữ liệu”, nghĩa là chỉ việc một cụ đổi nhỏ 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ẽ auto chạy lại để cập nhật kết quả. Điều này có thể khiến Excel tốn thêm thời gian nếu như khách hàng có quá 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 vấn đề sửa lỗi hàmCông thức hàm
OFFSETrất cực nhọc để bọn họ kiểm tra lại. Lí do bao gồm đến từ những việc các tham chiếu được trả lại bởi vì hàm OFFSET là động. Đối với những công thức phức tạp, nguy hại cao là chúng ta sẽ tốn rất nhiều 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 dụng cụ bảng trong ExcelBạn chỉ cần nhập một bí quyết hàm OFFSET vào một ô vào bảng, sau đó sao chép ô xuống các ô phía dưới. Vậy là bạn đã có thể tạo thành một bảng Excel với cùng một cột phương pháp giống nhau nhưng gồm sự chuyển đổi dữ liệu phù hợp cho mỗi hàng.
Tuyệt vời hơn, bất kỳ công thức nào tham chiếu đến dữ 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ó được xóa. Về mặt kỹ thuật, những công thức như vậy vận động trên các cột hoặc hàng của bảng, gọi là mọi dải động.
Mỗi bảng vào trang tính Excel tất cả một tên nhất mặc định là Table1, Table2,… nhưng chúng ta hoàn toàn hoàn toàn có thể đổi thương hiệu bảng của mình bằng cách vào tab
Design> Properties group> Table Name.
Hình ảnh dưới đây thể hiện bí quyết SUM tất cả tham chiếu đến cột Thưởng trong Bảng 3. Hãy để ý rằng công thức này có bao gồm tên cột của bảng thay vì một dải ô.
Mặc dù chức năng không đồng nhất hàm
OFFSET, nhưnghàm INDEXcũng hoàn toàn có thể được sử dụng làm hàm tham chiếu trong Excel đôi với các 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ự nạm đổi, bởi vậy nó sẽ không còn làm đủng đỉnh Excel của bạn.
Hàm
INDIRECTgiúp chúng ta tạo tham chiếu đến dải động từ không ít nguồn như các ô giá chỉ trị, quý giá và văn phiên bản của ô, và những dải ô được đặt tên. Ngoại trừ ra, nó cũng có thể tự động hóa tham chiếu một bảng tính hoặc một trang tính Excel.
Xem thêm:
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 phù hợp với các hàm khác. Vày vậy, để sử dụng công dụng hàm OFFSET, các bạn cần nắm vững công thức của các hàm tham chiếu và hàm tính toán trong Excel. Để làm được điều này, hãy tham khảo thêm các bài viết về hàm Excel trên blog cdvhnghean.edu.vn và nhanh tay đăng kí khóa đào tạo và huấn luyện Tuyệt đỉnh Excel cùng chúng mình nhé.