Hướng dẫn sử dụng hàm offset trong excel

1 Hàm OFFSET – cú pháp với áp dụng cơ bản:1.2 Những đối số tùy chọn:2 Công thức Excel OFFSET – hầu hết vấn đề cần nhớ:3 Làm chũm như thế nào để thực hiện hàm OFFSET trong Excel – các công thức ví dụ:3.1 Hàm OFFSET và hàm SUM:5 Hàm OFFSET & VLOOKUP:5.2 lấy ví dụ như 2. Làm nỗ lực như thế nào thực hiện Vlookup dò bên trên trong Excel6 Hàm OFFSET – tiêu giảm cùng các sàng lọc cố gắng thế6.2 Các chọn lựa sửa chữa cho câu hỏi áp dụng OFFSET trong Excel

Trong bài viết này, học excel nâng cao online vẫn lý giải về hàm OFFSET.

Bạn đang xem: Hướng dẫn sử dụng hàm offset trong excel

Hàm OFFSET – cú pháp cùng thực hiện 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à nên còn 2 dòng sau thì không. Tất cả các đối số hoàn toàn có thể là những tham chiếu mang lại những ô không giống hay những những hiệu quả được trả về do những phương pháp Excel không giống.

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

Reference– một ô hoặc một dải ô giáp mà lại tiếp đến nó hoàn toàn có thể bị thêm/giảm (Có thể nói đó là vấn đề khởi đầu/có tác dụng mốc) Rows – Số mặt hàng dịch chuyển từ điểm làm cho cội, lên hoặc xuống. Nếu những hàng là một số trong những dương, tức là bí quyết dịch rời xuống phía dưới tmê man chiếu, vào ngôi trường đúng theo một số trong những âm thì dịch rời lên bên trên tmê mẩn chiếu.Cols – Số cột bạn muốn cách làm dịch chuyển, lấy cội là tự điểm xuất phát. Cũng nhỏng rows, cols hoàn toàn có thể là số dương (ngơi nghỉ mặt bắt buộc tmê mệt chiếu) hoặc số âm (nghỉ ngơi bên trái tyêu thích chiếu).

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

height – độ cao, tính thông qua số sản phẩm, mà lại bạn muốn tsay mê chiếu trả về Width – chiều rộng lớn, tính ngay số cột, nhưng mà bạn muốn tđắm say chiếu trả về.

Cả nhị đối số height và width luôn nên là số dương. Nếu một trong nhì bị bỏ qua mất, thì height hoặc width của tmê mẩn chiếu cội được áp dụng.

Và hiện thời, tôi gồm một ví dụ đơn giản dễ dàng về bí quyết OFFSET nhằm minh hoạ kim chỉ nan trên:

lấy ví dụ như bí quyết OFFSET:

Công thức OFFSET sau trả về một tđê mê chiếu ô dựa vào một điểm làm mốc, những hàng và cột nhưng chúng ta chỉ định:

= OFFSET (A1,3,1)

Công thức đem ô A1 làm điểm làm mốc (tđắm đuối chiếu), tiếp nối dịch chuyển 3 mặt hàng xuống (đối số rows) với 1 cột phía bên trái (đối số cols). Kết trái là, phương pháp OFFSET này trả về quý hiếm trong ô B4.

Bức Ảnh sống phía trái cho biết phương thức hàm hoạt động và làm việc mặt bắt buộc trình bày cách chúng ta có thể áp dụng bí quyết OFFSET bên trên tài liệu thực tiễn. Sự khác hoàn toàn độc nhất giữa nhì công thức là bí quyết thiết bị nhị (mặt phải) gồm 1 tsay mê chiếu ô (E1) trong đối số sản phẩm (rows). Nhưng ví như ô E1 tất cả cất số 3 cùng đúng mực thuộc một trong những lộ diện trong các đối số mặt hàng của bí quyết trước tiên, thì cả nhì vẫn số đông trả về cùng một hiệu quả – giá trị trong B4.

*

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

Hàm OFFSET trong Excel ko đích thực di chuyển bất kỳ ô hoặc dải nào, nhưng mà điều nó làm chỉ với trả về một tham chiếu.Khi một công thức OFFSET trả về một dải ô, các đối số rows và cols luôn luôn luôn đề cập đến ô bên trên phía bên trái.Đối số tđắm đuối chiếu nên bao gồm một ô hoặc một dải ô cạnh bên, nếu không phương pháp của 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 tmê mệt chiếu mang lại rìa của bảng tính, thì công thức Excel OFFSET của bạn sẽ trả về #REF! lỗi.Hàm OFFSET hoàn toàn có thể được thực hiện trong bất kỳ hàm Excel nào không giống cơ mà gồm đồng ý một tđam mê chiếu ô hoặc dải trong số đối số của chính nó.

lấy ví dụ như, nếu như bạn cố gắng thực hiện phương pháp tựa như cầm này: = OFFSET (A1,3,1,1,3), thì nó đã trả về # VALUE! Lỗi, vày một dải nhằm đưa thành (1 sản phẩm, 3 cột) không phù hợp với việc đặt chỉ vào một ô nhất. Tuy nhiên, nếu như khách hàng nhúng nó vào hàm SUM, như thế này:

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

Công thức đã trả lại tổng các cực hiếm vào dải có 1 hàng bởi dải bao gồm 3 cột, bao hàm 3 hàng bên dưới với 1 cột mặt buộc phải ô A1, Có nghĩa là tổng mức vốn gồm trong số ô B4: D4.

*

Sử dụng OFFSET vào trường vừa lòng nào:

Bây tiếng bạn đang biết hàm OFFSET có tác dụng được phần nhiều gì, vậy nên bạn có thể từ bỏ hỏi mình “Tại sao lại nên dùng nó?” Tại sao không chỉ dễ dàng là viết một tmê mệt chiếu trực tiếp nlỗi B4: D4?

Công thức OFFSET vô cùng có lợi nếu khách hàng muốn:

Tạo các dải động: Các tđắm đuối chiếu nhỏng B1: C4 là tĩnh, có nghĩa là chúng luôn luôn tmê say chiếu đến một dải nhất mực. Nhưng cùng với một số trong những công việc thì đã dễ thực hiện hơn Lúc làm cho với dải đụng. Đây là ngôi trường hòa hợp đặc biệt quan trọng khi chúng ta làm việc với Việc biến hóa dữ liệu, ví dụ: Quý khách hàng tất cả một bảng tính, và mỗi tuần sẽ có một mặt hàng hoặc cột new được cung cấp trong những số đó.

Lấy dải từ ô gốc: thường thì, chúng ta có thể đắn đo liên can thực của cả dải, tuy vậy chúng ta tất cả hiểu được nó bước đầu từ bỏ một vài ô. Trong các trường hợp điều đó, áp dụng OFFSET trong Excel là một cách làm đúng.

Làm cố kỉnh nào để sử dụng hàm OFFSET vào Excel – các phương pháp ví dụ:

Tôi mong muốn chúng ta đã không Cảm Xúc nản với gò kim chỉ nan sinh hoạt trên. Dù sao, hiện nay họ sẽ triển khai phần độc đáo tuyệt nhất – áp dụng hàm OFFSET trong tình huống thực tiễn.

Hàm OFFSET và hàm SUM:

Ví dụ bọn họ vẫn bàn thảo cách đó một phút trình bày phương pháp thực hiện OFFSET với SUM vào Excel dễ dàng tuyệt nhất. Bây giờ đồng hồ, chúng ta hãy chú ý vào những hàm này ở một khía cạnh không giống và xem những gì chúng rất có thể có tác dụng.

ví dụ như 1. Một phương pháp hàm SUM / OFFSET thay đổi:

Vấn đề nữa là khi làm việc cùng với bảng tính update tiếp tục, bạn cũng có thể mong có một phương pháp SUM tự động lựa chọn tất cả những hàng mới đạt thêm vào?

Giả sử chúng ta có dữ liệu mối cung cấp tựa như như các gì chúng ta thấy vào hình bên dưới. Mỗi tháng sẽ có một loại new được cung cấp ngay phía trên công thức SUM, với thoải mái và tự nhiên, bạn muốn nó được bao hàm vào tổng thể. Trên toàn thể, có nhị chọn lựa – hoặc cập nhật các dải trong công thức SUM các lần đổi khác bằng tay hoặc các bạn sẽ sử dụng hàm OFFSET làm vấn đề này cho bạn.

*

Lúc ô trước tiên của dải được xem tổng (SUM) mà lại được tsi mê chiếu trực tiếp trong phương pháp SUM, bạn chỉ yêu cầu đưa ra các tmê mệt số cho hàm OFFSET, và kế tiếp nó sẽ tham chiếu cho tới ô sau cuối của dải:

Reference – ô đựng tổng cộng, trong ví dụ ở đó là ô B9. Rows – ô trên ô cất tổng số với nằm sát cần, cần một số âm: -1.

Xem thêm: Hướng Dẫn Kê Khai Bảo Hiểm Xã Hội Điện Tử, Hướng Dẫn Kê Khai Bảo Hiểm Xã Hội Qua Mạng 2020

Cols – là 0 bởi các bạn không thích chuyển đổi cột.

Từ kia, ta bao gồm chủng loại bí quyết SUM / OFFSET:

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

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

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

Và nó đích thực hoạt động tốt như vào hình được minc họa bên dưới đây:

*

lấy ví dụ 2. Công thức OFFSET  để tổng vừa lòng N các chiếc cuối danh sách:

Trong ví dụ trên, giả sử bạn muốn biết số tiền thưởng (Bonus) mang lại N mon sớm nhất chứ đọng chưa hẳn là toàn bộ. Và các bạn vẫn muốn phương pháp chạy tự động hóa (không quyên tâm cho liệu có ngẫu nhiên sản phẩm bắt đầu được cấp dưỡng trang tính tốt không).

Với trách nhiệm này, chúng ta đang thực hiện Excel OFFSET kết hợp với những 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 thông tin dưới đây hoàn toàn có thể giúp đỡ bạn nắm rõ những phương pháp giỏi hơn:

Reference – tiêu đề của cột bao gồm các quý hiếm nhưng bạn có nhu cầu tính tổng, vào ví dụ này là ô B1. Rows – để tính số hàng để tính offmix, cùng bạn sử dụng thêm hàm COUNT hoặc COUNTA.

COUNTA trả về số ô vào cột B bao gồm cất số, từ đó bạn trừ đi các N tháng ở đầu cuối (quý giá làm việc trong ô E1) và thêm 1.

Nếu bạn muốn lựa chọn hàm COUNTA, thì lúc ấy bạn không cần phải thêm 1, do hàm này tính toàn bộ những ô ko cất quý hiếm trống rỗng, cùng mặt hàng title có giá trị văn uống phiên bản được thêm một ô thêm mới mà phương pháp đề xuất đến. Xin xem xét rằng công thức này đã chỉ vận động đúng đắn trên một cấu tạo bảng tính tương tự như – cùng loại tiêu đề tiếp theo sau là mặt hàng tất cả số. Đối với những cấu trúc bảng khác nhau, bạn cũng có thể phải thực hiện một vài điều chỉnh trong công thức OFFSET / COUNTA.

Cols – số cột kiểm soát và điều chỉnh là 0. Chiều cao – số sản phẩm được tổng thích hợp được chỉ định vào E1. Chiều rộng – 1 cột.

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

Theo biện pháp tựa như như Shop chúng tôi hoàn toàn có thể tính chi phí ttận hưởng mang lại N mon qua, chúng ta cũng có thể nhấn giá tốt trị vừa phải mang đến N ngày, tuần hoặc năm cuối cùng cũng giống như kiếm tìm các quý giá về tối đa hoặc tối thiểu. Sự khác biệt tốt nhất giữa những bí quyết 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 chính mà lại những công thức này hơn hàm AVERAGE(B5: B8) hoặc MAX (B5: B8) là bạn sẽ chưa hẳn update cách làm mỗi khi bảng cội của người sử dụng được update. Quý Khách sẽ không nhất thiết phải quan tâm rằng bao nhiêu chiếc mới được cung cấp hoặc xóa vào bảng tính Excel của khách hàng, nhưng mà sẽ có những phương pháp OFFSET sẽ luôn luôn luôn luôn tmê say chiếu mang đến tận những ô sau cùng (vẫn qui định trước giới hạn nhỏ tốt nhất với phệ nhất) vào cột.

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

Được áp dụng với COUNTA, hàm OFFSET hoàn toàn có thể khiến cho bạn chế tạo một dải động có ích trong nhiều tình huống, ví dụ như Lúc bạn muốn tạo nên danh sách theo mục hoàn toàn có thể tự động hóa cập nhật.

Công thức OFFSET cho một 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 cùng theo sau là lốt chnóng than, cùng liên can của ô bao gồm chứa mục đầu tiên xuất hiện trong vào dải (-đã có được đặt tên). Xin xem xét câu hỏi thực hiện các tmê man chiếu ô tuyệt vời và hoàn hảo nhất ($), ví dụ Sheet1! $A$1.Các tham mê số Rows & Cols những là 0, vì không tồn tại những cột hoặc những hàng được tiếp tế. Height là vấn đề chủ đạo tại chỗ này. Tình huống là các bạn áp dụng hàm COUNTA nhằm tính tân oán số ô ko trống rỗng trong cột tất cả chứa những mục của dải được đặt tên. Với đó, hãy lưu ý rằng trong tđắm đuối số của COUNTA, bạn nên chỉ có thể định tên bảng kề bên thương hiệu cột, ví dụ: COUNTA (Sheet_Name!$A: $A). Width là một trong cột.

Một khi bạn vẫn tạo thành một dải đụng (đang đặt tên) với bí quyết OFFSET sinh sống bên trên, chúng ta có thể sử dụng Excel Data Validation để sản xuất list theo mục – luôn luôn auto update ngay khi bạn thêm hoặc xoá các mục tự Danh sách mối cung cấp (bảng tính gốc).

*

Hàm OFFSET & VLOOKUP:

Nhỏng chúng ta đã biết, những tra cứu vớt dễ dàng theo chiều dọc củ và ngang vào Excel được triển khai với hàm VLOOKUP hoặc hàm HLOOKUP. Tuy nhiên, những hàm này còn có quá nhiều giảm bớt cùng hay là băn khoăn to trong Việc tạo nên cách làm tra cứu vãn táo bạo hơn và phức hợp rộng. Vì vậy, nhằm thực hiện tra cứu vãn “một phương pháp tinh vi hơn” trong các bảng Excel, bạn nên tra cứu kiếm các tuyển lựa thay thế sửa chữa nhỏng hàm INDEX, MATCH với OFFSET.

Xem thêm: Hướng Dẫn Cách Sửa Máy Hàn Điện Tử Báo Lỗi Oc, Cách Sửa Máy Hàn Điện Tử

Ví dụ 1. Công thức OFFSET cho 1 Vlookup dò bên trái vào Excel

trong số những tinh giảm đáng chú ý độc nhất vô nhị của hàm Excel VLOOKUP. là không có tác dụng dò giá tốt trị bên trái của chính nó, có nghĩa là VLOOKUPhường chỉ rất có thể trả lại một quý giá ngơi nghỉ bên đề nghị cột tra cứu giúp.

Trong bảng tra cứu vớt chủng loại của Shop chúng tôi, bao gồm nhì cột – tên tháng (month) (cột A) và tiền ttận hưởng (Bonus) (cột B). Nếu bạn có nhu cầu nhận ra chi phí thưởng trọn cho một tháng nhất định, phương pháp VLOOKUP. đơn giản dễ dàng này vẫn vận động mà không gặp gỡ trở ngại:

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

Tuy nhiên, tức thì khi chúng ta tráo đổi những cột trong bảng tra cứu, điều đó đã ngay lập tức dẫn cho lỗi #N/A:

*

Để xử trí một tra cứu vãn phía trái, bạn phải một hàm linc hoạt hơn nhưng không thực thụ quan tâm cho địa chỉ của cột kia nơi đâu. Một trong những chiến thuật có thể sử dụng chính là phối hợp các hàm INDEX cùng MATCH. Một biện pháp làm khác đó là thực hiện OFFSET, MATCH cùng ROWS:

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

Trong ví dụ của Cửa Hàng chúng tôi, bảng tra cứu vớt là A5: B9 với quý giá tra cứu bên trong ô B1, do vậy công thức bên trên vẫn xúc tiế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ách làm trông hơi dềnh dàng về, mà lại nó thực sự được Việc
Chuyên mục: Hướng Dẫn - Hỏi Đáp