Hàm OFFSET – cú pháp và thực hiện cơ bản:Những đối số tùy chọn:Công thức Excel OFFSET – những điều cần nhớ:Làm vậy nào để áp dụng hàm OFFSET trong Excel – các công thức ví dụ:Hàm OFFSET với hàm SUM:Hàm OFFSET & VLOOKUP:Ví dụ 2. Làm rứa nào thực hiện Vlookup dò bên trên trong ExcelHàm OFFSET – hạn chế và các lựa chọn thế thếCác lựa chọn thay thế sửa chữa cho việc sử dụng OFFSET vào Excel

Hàm OFFSET – cú pháp và áp dụng cơ bản:

Cú pháp của hàm OFFSET như sau:

OFFSET(reference, rows, cols, , )

Trong đó, 3 đối số đầu tiên là bắt buộc còn 2 loại sau thì không. Tất cả các đối số có thể là các tham chiếu đến các ô khác hoặc là các hiệu quả được trả về bởi các công thức Excel khác.

Bạn đang xem: Cách dùng hàm offset trong excel

Những đối số bắt buộc:

Reference– một ô hoặc một dải ô gần kề mà tiếp nối nó rất có thể bị thêm/bớt (Có thể nói đó là điểm khởi đầu/làm mốc) Rows – Số hàng di chuyển từ điểm có tác dụng gốc, lên hoặc xuống. Nếu các hàng là một số trong những dương, tức thị công thức di chuyển xuống bên dưới tham chiếu, trong trường hợp một trong những âm thì dịch chuyển lên phía bên trên tham chiếu.Cols – Số cột bạn muốn công thức di chuyển, lấy cội là tự điểm xuất phát. Cũng giống như rows, cols có thể là số dương (ở bên bắt buộc tham chiếu) hoặc số âm (ở bên trái tham chiếu).

Những đối số tùy chọn:

height – chiều cao, tính thông qua số hàng, mà bạn muốn tham chiếu trả về Width – chiều rộng, tính bằng số cột, mà bạn muốn tham chiếu trả về.

Cả nhì đối số height cùng width luôn luôn phải là số dương. Nếu một trong những hai bị quăng quật qua, thì height hoặc width của tham chiếu gốc được sử dụng.

Và bây giờ, tôi bao gồm một ví dụ đơn giản và dễ dàng về bí quyết OFFSET nhằm minh hoạ triết lý trên:

Ví dụ công thức OFFSET:

Công thức OFFSET sau trả về một tham chiếu ô dựa vào một điểm làm mốc, các hàng cùng cột mà các bạn chỉ định:


*

*

= OFFSET (A1,3,1)

Công thức rước ô A1 làm cho điểm có tác dụng mốc (tham chiếu), sau đó dịch chuyển 3 sản phẩm xuống (đối số rows) cùng 1 cột phía trái (đối số cols). Công dụng là, cách làm OFFSET này trả về quý hiếm trong ô B4.

Hình ảnh ở phía bên trái cho thấy cách thức hàm vận động và ngơi nghỉ bên phải thể hiện nay cách bạn có thể sử dụng bí quyết OFFSET trên dữ liệu thực tế. Sự khác hoàn toàn duy tốt nhất giữa hai bí quyết là công thức thứ nhị (bên phải) gồm 1 tham chiếu ô (E1) trong đối số sản phẩm (rows). Nhưng lại nếu ô E1 tất cả chứa số 3 và đúng đắn cùng một số xuất hiện trong những đối số hàng của cách làm đầu tiên, thì cả nhì sẽ phần đông trả về cùng một công dụng – giá trị trong B4.

*

Công thức Excel OFFSET – những điều cần nhớ:

Hàm OFFSET vào Excel không thực sự di chuyển bất kỳ ô hoặc dải nào, mà lại điều nó làm chỉ là trả về một tham chiếu.Khi một công thức OFFSET trả về một dải ô, những đối số rows với cols luôn luôn đề cập cho ô bên trên bên trái.Đối số tham chiếu phải gồm 1 ô hoặc một dải ô liền kề, còn nếu không công thức của các bạn sẽ trả về #VALUE! lỗi.Nếu những hàng (rows) được hướng dẫn và chỉ định và/hoặc cột (cols) dịch rời một tham chiếu cho rìa của bảng tính, thì phương pháp Excel OFFSET của bạn sẽ trả về #REF! lỗi.Hàm OFFSET có thể được áp dụng trong bất kỳ hàm Excel nào khác nhưng có đồng ý một tham chiếu ô hoặc dải trong những đối số của nó.

Ví dụ, giả dụ bạn nỗ lực sử dụng công thức tương tự thế này: = OFFSET (A1,3,1,1,3), thì nó sẽ trả về # VALUE! Lỗi, bởi một dải để đưa thành (1 hàng, 3 cột) không cân xứng với việc đặt chỉ vào một ô duy nhất. Mặc dù nhiên, nếu như bạn nhúng nó vào hàm SUM, như cố gắng này:

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

Công thức đang trả lại tổng các giá trị trong dải có 1 hàng bằng dải có 3 cột, bao gồm 3 hàng dưới và 1 cột bên buộc phải ô A1, tức là tổng quý giá có trong số ô B4: D4.

*

Sử dụng OFFSET vào trường đúng theo nào:

Bây giờ bạn đã biết hàm OFFSET làm được rất nhiều gì, vậy nên chúng ta cũng có thể tự hỏi bản thân “Tại sao lại phải dùng nó?” vì sao không chỉ đơn giản là viết một tham chiếu thẳng như B4: D4?

Công thức OFFSET vô cùng hữu ích nếu như khách hàng muốn:

Tạo những dải động: những tham chiếu như B1: C4 là tĩnh, tức là chúng luôn tham chiếu mang lại một dải tốt nhất định. Nhưng với một số các bước thì đang dễ thực hiện hơn khi làm cho với dải động. Đây là trường hợp đặc biệt khi bạn làm việc với việc thay đổi dữ liệu, ví dụ: bạn có một bảng tính, cùng mỗi tuần sẽ sở hữu được một sản phẩm hoặc cột new được chế tạo trong đó.

Lấy dải từ ô gốc: Đôi khi, bạn có thể không biết showroom thực của tất cả dải, mặc dù bạn bao gồm biết rằng nó bước đầu từ một trong những ô. Trong các trường hợp như vậy, áp dụng OFFSET trong Excel là một cách làm đúng.

Làm cố gắng nào để sử dụng hàm OFFSET vào Excel – các công thức ví dụ:

Tôi hi vọng bạn đã không cảm thấy chán nản với đống kim chỉ nan ở trên. Mặc dù sao, hiện nay chúng ta đang thực hiện phần thú vui nhất – áp dụng hàm OFFSET trong trường hợp thực tế.

Hàm OFFSET cùng hàm SUM:

Ví dụ bọn họ đã thảo luận cách trên đây một phút trình bày cách sử dụng OFFSET với SUM trong Excel đơn giản nhất. Bây giờ, bọn họ hãy chú ý vào gần như hàm này nghỉ ngơi một khía cạnh khác cùng xem đều gì chúng rất có thể làm.

Ví dụ 1. Một bí quyết hàm SUM / OFFSET thay đổi:

Vấn đề nữa là khi thao tác làm việc với bảng tính update liên tục, chúng ta có thể muốn gồm một cách làm SUM tự động chọn tất cả các hàng mới được thêm vào?

Giả sử các bạn có dữ liệu nguồn tương tự như đông đảo gì chúng ta thấy vào hình mặt dưới. Từng tháng sẽ sở hữu một dòng new được cung cấp ngay bên trên công thức SUM, và tự nhiên, bạn có nhu cầu nó được bao gồm trong tổng số. Bên trên toàn bộ, tất cả hai chọn lọc – hoặc update các dải trong phương pháp SUM mỗi lần thay đổi thủ công bằng tay hoặc các bạn sẽ dùng hàm OFFSET làm vấn đề này cho bạn.

*

Khi ô trước tiên của dải được xem tổng (SUM) nhưng mà được tham chiếu trực tiếp trong cách làm SUM, các bạn chỉ cần đưa ra những tham số mang đến hàm OFFSET, và sau đó nó đã tham chiếu cho đến ô cuối cùng của dải:

Reference – ô chứa tổng số, trong lấy một ví dụ ở đấy là ô B9. Rows – ô trên ô cất tổng số và nằm bên phải, bắt buộc một số ít âm: -1. Cols – là 0 vì chúng ta không muốn chuyển đổi cột.

Xem thêm: Phong Cách Vintage Là Gì - Phối Đồ Sao Cho Chuẩn Gu

Từ đó, ta có mẫu cách làm SUM / OFFSET:

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

Rút gọn gàng ví dụ trên, ta sẽ sở hữu được công thức trông như sau:

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

Và nó thực sự vận động tốt như trong hình được minh họa dưới đây:

*

Ví dụ 2. Phương pháp OFFSET  để tổng đúng theo N các dòng cuối danh sách:

Trong lấy ví dụ như trên, đưa sử bạn có nhu cầu biết số tiền thưởng (Bonus) đến N tháng sớm nhất chứ chưa phải là tất cả. Và bạn cũng muốn công thức chạy auto (không suy xét liệu có ngẫu nhiên hàng bắt đầu được tiếp tế trang tính tuyệt không).

Với trọng trách này, bọn họ sẽ thực hiện Excel OFFSET kết hợp với các hàm SUM với hàm COUNT / COUNTA:

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

hoặc là

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

*

Các tin tức sau đây có thể giúp bạn làm rõ các công thức xuất sắc hơn:

Reference – title của cột có các giá trị mà bạn có nhu cầu tính tổng, trong lấy ví dụ này là ô B1. Rows – để tính số hàng để tính offset, và bạn sử dụng thêm hàm COUNT hoặc COUNTA.

COUNTA trả về số ô vào cột B gồm chứa số, từ đó chúng ta trừ đi các N tháng ở đầu cuối (giá trị ngơi nghỉ trong ô E1) cùng thêm 1.

Nếu bạn muốn chọn hàm COUNTA, thì lúc đó bạn không nhất thiết phải thêm 1, do hàm này tính tất cả các ô không đựng giá trị rỗng, cùng hàng tiêu đề có giá trị văn bạn dạng được thêm 1 ô thêm new mà công thức nên đến. Xin chú ý rằng công thức này vẫn chỉ hoạt động đúng chuẩn trên một cấu tạo bảng tính tương tự như – và mẫu tiêu đề tiếp theo là hàng bao gồm số. Đối cùng với các kết cấu bảng không giống nhau, chúng ta cũng có thể cần thực hiện một trong những điều chỉnh trong bí quyết OFFSET / COUNTA.

Cols – số cột kiểm soát và điều chỉnh là 0. Chiều cao – số hàng được tổng vừa lòng được chỉ định và hướng dẫn trong E1. Chiều rộng – 1 cột.

Sử dụng hàm OFFSET cùng với AVERAGE, MAX, MIN

Theo cách tương tự như như cửa hàng chúng tôi có thể tính tiền thưởng mang đến N mon qua, bạn cũng có thể nhận giá tốt trị trung bình cho N ngày, tuần hoặc năm cuối cùng cũng giống như tìm các giá trị tối đa hoặc tối thiểu. Sự khác biệt duy duy nhất giữa các công thức là tên của hàm đầu tiên:

= AVERAGE (OFFSET (B1, COUNT (B: B) -E1 + 1,0, E1,1))

= MAX (OFFSET (B1, COUNT (B: B) -E1 + 1,0, E1,1))

= MIN (OFFSET (B1, COUNT (B: B) -E1 + 1,0, E1,1))

 

*

Lợi ích thiết yếu mà các công thức này hơn hàm AVERAGE(B5: B8) hoặc MAX (B5: B8) là bạn sẽ không phải cập nhật công thức mọi khi bảng gốc của người tiêu dùng được cập nhật. Bạn sẽ không rất cần phải quan trung ương rằng bao nhiêu dòng mới được sản xuất hoặc xóa trong bảng tính Excel của bạn, mà sẽ sở hữu các bí quyết OFFSET sẽ luôn luôn luôn tham chiếu mang đến tận đều ô ở đầu cuối (đã phương tiện trước giới hạn nhỏ tuổi nhất và khủng nhất) vào cột.

Công thức OFFSET để tạo ra một dải động

Được sử dụng cùng cùng với COUNTA, hàm OFFSET có thể giúp chúng ta tạo một dải cồn hữu ích trong không ít tình huống, ví như khi bạn có nhu cầu tạo danh sách theo mục gồm thể auto cập nhật.

Công thức OFFSET cho 1 dải đụng như sau:

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

Trong đó:

Reference: được trình diễn dưới dạng thương hiệu bảng với theo sau là dấu chấm than, và showroom của ô có chứa mục đầu tiên có mặt trong vào dải (-đã được để tên). Xin lưu ý việc sử dụng những tham chiếu ô hoàn hảo và tuyệt vời nhất ($), lấy một ví dụ Sheet1! $A$1.Các thông số Rows & Cols phần lớn là 0, vì không có các cột hoặc những hàng có thêm vào. Height là vấn đề mấu chốt ngơi nghỉ đây. Trường hợp là bạn thực hiện hàm COUNTA để đo lường và thống kê số ô ko rỗng vào cột tất cả chứa các mục của dải được để tên. Cùng với đó, hãy lưu ý rằng trong tham số của COUNTA, bạn nên có thể định tên bảng sát bên tên cột, ví dụ: COUNTA (Sheet_Name!$A: $A). Width là một trong những cột.

Một khi bạn đã chế tạo ra một dải rượu cồn (đã để tên) với cách làm OFFSET nghỉ ngơi trên, bạn cũng có thể sử dụng Excel Data Validation để tạo list theo mục – luôn tự động hóa cập nhật ngay khi chúng ta thêm hoặc xoá các mục từ danh sách nguồn (bảng tính gốc).

*

Hàm OFFSET và VLOOKUP:

Như chúng ta đã biết, những tra cứu đơn giản và dễ dàng theo chiều dọc và ngang trong Excel được thực hiện với hàm VLOOKUP hoặc hàm HLOOKUP. Mặc dù nhiên, những hàm này có quá nhiều hạn chế và thường là vấn đề lớn trong việc tạo công thức tra cứu bạo phổi hơn và phức hợp hơn. Bởi vì vậy, để thực hiện tra cứu vớt “một cách tinh vi hơn” trong số bảng Excel, chúng ta phải tìm kiếm các lựa chọn thay thế sửa chữa như hàm INDEX, MATCH với OFFSET.

Ví dụ 1. Công thức OFFSET cho 1 Vlookup dò phía bên trái trong Excel

Một giữa những hạn chế đáng chú ý nhất của hàm Excel VLOOKUP là không có tác dụng dò giá tốt trị phía bên trái của nó, tức là VLOOKUP chỉ rất có thể trả lại một quý hiếm ở bên đề xuất cột tra cứu.

Trong bảng tra cứu chủng loại của bọn chúng tôi, gồm hai cột – thương hiệu tháng (month) (cột A) cùng tiền thưởng (Bonus) (cột B). Nếu bạn muốn nhận được tiền thưởng cho 1 tháng tuyệt nhất định, bí quyết VLOOKUP đơn giản dễ dàng này sẽ vận động mà không chạm chán trở ngại:

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

Tuy nhiên, ngay khi chúng ta tráo đổi các cột trong bảng tra cứu, vấn đề này sẽ ngay mau lẹ dẫn cho lỗi #N/A:

*

Để cách xử lý một tra cứu bên trái, bạn cần một hàm hoạt bát hơn cơ mà không thực sự suy xét vị trí của cột đó ở đâu. Một trong các những chiến thuật có thể sử dụng đó là kết hợp các hàm INDEX và MATCH. Một bí quyết làm khác chính là sử dụng OFFSET, MATCH với ROWS:

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

Trong lấy ví dụ của bọn chúng tôi, bảng tra cứu là A5: B9 và quý hiếm tra cứu phía bên trong ô B1, do vậy bí quyết trên sẽ thực hiện thành:

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

Tôi biết công thức trông hơi vụng về về, nhưng mà nó thực thụ được vấn đề