OFFSET địa chỉ Excel

Hàm OFFSET trong excel trả về giá trị của một ô hoặc một phạm vi (của các ô liền kề) là một số hàng và cột cụ thể từ điểm tham chiếu. Điểm tham chiếu này là ô bắt đầu được cung cấp làm đối số cho hàm. Bắt đầu từ điểm tham chiếu, hàm tìm ra điểm kết thúc (ô hoặc phạm vi kết thúc) có giá trị được trả về

Ví dụ: một tập dữ liệu được đưa ra như sau

• Cột A gồm 2, 4, 3 trong dãy A1. A3
• Cột B gồm 1, 5, 6 trong dãy B1. B3

Công thức “=OFFSET(A1,2,1)” trả về 6. Hàm excel OFFSET bắt đầu tính toán tham chiếu ô từ ô A1 (điểm bắt đầu). Tiếp theo, nó di chuyển xuống 2 hàng (đến ô A3) và 1 cột sang phải (đến ô B3)

Điểm cuối là ô B3. Vì vậy, hàm OFFSET trả về giá trị của ô B3, i. e. , 6

Mục đích của việc sử dụng hàm OFFSET là tìm giá trị của ô biết điểm đầu. Chức năng này đặc biệt hữu ích trong trường hợp tập dữ liệu được cập nhật thường xuyên

Hàm OFFSET là một phần của hàm Tra cứu và Tham chiếu của Excel

Mục lục

Cú pháp của Hàm OFFSET Excel

Cú pháp của hàm được hiển thị trong hình ảnh sau

Hàm chấp nhận các đối số bắt buộc sau

• Thẩm quyền giải quyết. Đây là điểm bắt đầu tính toán địa chỉ ô (hoặc phạm vi) kết thúc. Nó có thể được nhập dưới dạng một ô hoặc một dải các ô liền kề
• hàng. Đây là số hàng mà hàm cần di chuyển. Chuyển động có thể ở trên hoặc dưới điểm tham chiếu (điểm bắt đầu). Nếu đối số này là dương, hàm sẽ di chuyển xuống dưới điểm tham chiếu. Nếu đối số này là âm, hàm sẽ di chuyển trên điểm tham chiếu
• Cols. Đây là số cột mà hàm cần di chuyển. Chuyển động có thể ở bên phải hoặc bên trái của điểm tham chiếu (điểm bắt đầu). Nếu đối số này là dương, hàm sẽ di chuyển sang bên phải của điểm tham chiếu. Nếu đối số này là âm, hàm sẽ di chuyển sang bên trái của điểm tham chiếu

Hàm chấp nhận các đối số tùy chọn sau

• Chiều cao. Đây là chiều cao (số hàng) của phạm vi sẽ được trả về
• Chiều rộng. Đây là chiều rộng (số cột) của phạm vi sẽ được trả về

Các đối số "chiều cao" và "chiều rộng" phải luôn là số dương. Nếu một trong hai đối số này bị bỏ qua, thì chiều cao và chiều rộng của tham chiếu (điểm bắt đầu) được giả định

lưu ý 1. Các đối số “hàng” và “cột” được chỉ định bằng cách sử dụng điểm tham chiếu làm cơ sở

Lưu ý 2. Nếu cả hai đối số “chiều cao” và “chiều rộng” là 1, thì hàm excel OFFSET trả về giá trị của một ô. Điều này là do phạm vi 1 hàng x 1 cột là thứ nguyên (kích thước) của một ô

lưu ý 3. Hàm OFFSET không di chuyển bất kỳ ô hoặc phạm vi nào. Nó chỉ trả về giá trị của địa chỉ ô (hoặc phạm vi) mà nó tính toán

Cách sử dụng Hàm OFFSET trong Excel?

Hàm OFFSET là một hàm có sẵn trong Excel. Hãy để chúng tôi hiểu nó hoạt động với sự trợ giúp của một vài ví dụ

Mỗi ví dụ bao gồm một trường hợp khác nhau của hàm OFFSET excel, bao gồm cả phần giải thích giống nhau. Trong hai ví dụ trước (ví dụ #4 và #5), hàm OFFSET đã được kết hợp với các hàm số học

Bạn có thể tải xuống Mẫu Excel Hàm OFFSET này tại đây –

Ví dụ #1–Điểm cuối là Ô dữ liệu

Bảng sau đây cho thấy tên của năm tay đua và thứ hạng tương ứng mà họ đạt được. Chúng tôi muốn tìm tên của tay đua thứ ba với sự trợ giúp của hàm OFFSET

Bước 1. Nhập công thức OFFSET sau vào ô F4

“=OFFSET(B3,2,1)”

Bước 2. Nhấn phím “Enter”. Đầu ra xuất hiện trong ô F4. Như vậy, “Nadal” là tay đua về thứ ba của chặng đua

Giải trình. Đối số đầu tiên của hàm OFFSET excel là ô B3, là điểm tham chiếu (điểm bắt đầu)

Đối số "hàng" là 2. Vì vậy, hàm di chuyển hai hàng bên dưới ô B3, là ô B5. Đối số “cols” là 1. Vậy hàm di chuyển một cột sang bên phải ô B3 là ô C5

Giá trị của ô kết quả (điểm cuối) C5 là “Nadal. ” Do đó, đầu ra trong ô F4 là “Nadal. ” Điểm kết thúc được hiển thị trong hình ảnh sau

Ví dụ #2–Điểm kết thúc là một ô trống

Làm việc với dữ liệu của ví dụ #1, chúng ta muốn hàm OFFSET trả về giá trị của ô trống D5

Bước 1. Nhập công thức sau vào ô F5

“=OFFSET(B3,2,2)”

Bước 2. Nhấn phím “Enter”. Đầu ra 0 xuất hiện trong ô F5. Do đó, hàm OFFSET trả về giá trị của một ô trống là 0

Giải trình. Trong công thức excel OFFSET đã cho, điểm tham chiếu (điểm bắt đầu) là ô B3

Vì đối số “hàng” là 2 nên hàm sẽ di chuyển hai hàng xuống ô B5. Đối số “cols” cũng là 2. Vậy hàm di chuyển hai cột sang bên phải ô B3 là ô D5

Ô kết quả (điểm cuối) là D5. Vì ô này trống nên đầu ra được trả về bởi hàm OFFSET là 0. Điểm cuối được hiển thị trong hình ảnh sau

Ví dụ #3–Điểm kết thúc là một ô không tồn tại

Làm việc với dữ liệu của ví dụ #1, chúng ta muốn hàm OFFSET trả về giá trị của ô (ngay bên trái ô A1) không tồn tại

Bước 1. Nhập công thức excel OFFSET sau vào ô F6

“=OFFSET(B3,-2,-2)”

Bước 2. Nhấn phím “Enter”. Đầu ra xuất hiện trong ô F6. Do đó, giá trị của ô không tồn tại là “#REF. " lỗi

Giải trình. Trong ví dụ này, ô B3 là điểm tham chiếu

Đối số "hàng" là -2. Vì vậy, hàm di chuyển hai hàng trên ô B3, đó là ô B1. Đối số “cols” cũng là -2. Vì vậy, hàm di chuyển hai cột sang bên trái của ô B3. Tuy nhiên, không có cột nào ở bên trái cột A

Do đó, không có ô kết quả (điểm cuối). Vì vậy, đầu ra là “#REF. ” lỗi, như thể hiện trong hình ảnh sau. Biểu tượng màu vàng hiển thị “lỗi tham chiếu ô không hợp lệ. ”

Ghi chú. Nếu điểm cuối của hàm OFFSET là một ô nằm ngoài phạm vi hoặc không tồn tại, nó sẽ trả về lỗi “#REF”

Ví dụ #4–Tổng giá trị của địa chỉ phạm vi được trả về

Bảng sau đây cho thấy năm cổ phiếu có lợi nhuận tối đa và tối thiểu (tính bằng đô la). Chúng tôi muốn tính tổng lợi nhuận tối đa của các cổ phiếu này. Sử dụng hàm OFFSET excel kết hợp với Hàm SUM Hàm SUMHàm SUM trong excel cộng các giá trị số trong một . Được phân loại theo hàm Toán học và Lượng giác, nó được nhập bằng cách nhập “=SUM” theo sau là các giá trị cần tính tổng. Các giá trị được cung cấp cho hàm có thể là số, tham chiếu ô hoặc phạm vi. đọc thêm .

Bước 1. Nhập công thức sau vào ô F3

“=SUM(OFFSET(C2,0,0,5,1))”

Bước 2. Nhấn phím “Enter”. Đầu ra xuất hiện trong ô F3. Do đó, tổng lợi nhuận tối đa của các cổ phiếu đã cho là $449

Giải trình. Ô C2 là điểm tham chiếu

Cả hai đối số “hàng” và “cột” đều bằng không. Điều này ngụ ý rằng chức năng OFFSET sẽ không di chuyển lên xuống cũng như sang trái và phải

Đối số “chiều cao” là 5, ngụ ý rằng hàm OFFSET sẽ tính tổng năm hàng bắt đầu từ ô C2. Đây là phạm vi C2. C 6

Đối số "chiều rộng" là 1, ngụ ý rằng hàm OFFSET sẽ tính tổng các giá trị của một cột (cột C)

Với các đối số "chiều cao" và "chiều rộng" đã cho, công thức trước đó (được nhập ở bước 1) trả về tổng giá trị của phạm vi 5 hàng x 1 cột. Nó tính tổng các giá trị của phạm vi được trả về (5 hàng x 1 cột) vì OFFSET được đặt trong hàm SUM

Do đó, tổng tất cả các giá trị của cột C được tính là 98+92+89+88+82=449

Ví dụ #5–Giá trị trung bình của địa chỉ phạm vi được trả về

Làm việc trên dữ liệu của ví dụ #4, chúng tôi muốn tính lợi nhuận trung bình của cổ phiếu “S1. ” Sử dụng hàm OFFSET excle kết hợp với Hàm AVERAGE Hàm AVERAGEHàm AVERAGE trong Excel cho trung bình cộng của . Công thức này được phân loại là Hàm thống kê. Công thức tính trung bình là =AVERAGE(đọc thêm .

Bước 1. Nhập công thức sau vào ô F5

“=AVERAGE(OFFSET(C2,0,0,1,2))”

Bước 2. Nhấn phím “Enter”. Đầu ra xuất hiện trong ô F5. Do đó, lợi nhuận trung bình của cổ phiếu “S1” là $74

Giải trình. Ô C2 là điểm tham chiếu

Cả hai đối số “hàng” và “cột” đều bằng không. Điều này ngụ ý rằng hàm OFFSET sẽ không di chuyển theo bất kỳ hướng nào

Đối số "chiều cao" là 1. Điều này ngụ ý rằng hàm OFFSET sẽ chỉ xem xét một hàng (hàng 2) để tính giá trị trung bình

Đối số "chiều rộng" là 2. Điều này ngụ ý rằng hàm OFFSET excel sẽ tính trung bình cộng của hai cột (cột C và D)

Với các đối số "chiều cao" và "chiều rộng" đã cho, công thức trước đó (được nhập ở bước 1) trả về giá trị trung bình của phạm vi 1 hàng x 2 cột

Trong công thức trước, OFFSET được đặt trong hàm AVERAGE. Vì vậy, các giá trị của dải ô được trả về (1 hàng x 2 cột) được sử dụng để tính giá trị trung bình. Do đó, trung bình cộng của các giá trị trong các ô C2 và D2 ​​được tính là (98+50)/2=74

Phạm vi được Trả về bởi Hàm OFFSET Excel

Hàm OFFSET cũng có thể trả về các giá trị của một dải các ô liền kề. Đối với điều này, các bước được thực hiện được liệt kê như sau

• Chọn một phạm vi trống có kích thước giống như được chỉ định bởi các đối số “chiều cao” và “chiều rộng” của hàm OFFSET trong excel
• Chèn công thức OFFSET vào ô trên cùng bên trái của phạm vi trống
• Nhấn “Ctrl + Shift + Enter. ” Dấu ngoặc nhọn xuất hiện ở đầu và cuối công thức

Các giá trị của phạm vi ô được lấy dưới dạng đầu ra của hàm excel OFFSET

Ghi chú. Để có được các giá trị của một phạm vi ô, điều cần thiết là cung cấp các đối số "chiều cao" (số hàng) và "chiều rộng" (số cột)

Các câu hỏi thường gặp

1. Định nghĩa hàm OFFSET của Excel

Hàm OFFSET trong excel trả về giá trị của một ô hoặc một dãy ô liền kề. Địa chỉ của ô này (hoặc phạm vi) được tính từ một điểm tham chiếu (ô bắt đầu) được cung cấp dưới dạng đối số. Điểm tham chiếu này được lấy làm cơ sở để chỉ định các đối số “hàng” và “cols”.

Cú pháp hàm OFFSET excel như sau.

“=OFFSET(reference,rows,cols,[height],[width])”

Hàm OFFSET chấp nhận các đối số sau.

Tham khảo. Đây là điểm bắt đầu hoặc cơ sở mà từ đó bắt đầu tính toán địa chỉ ô (hoặc phạm vi). Nó có thể là một ô đơn lẻ hoặc một dãy các ô liền kề.

Hàng. Đây là số hàng mà hàm cần di chuyển. Nó được tính từ điểm bắt đầu. Số dương ngụ ý chuyển động đi xuống, trong khi số âm ngụ ý chuyển động đi lên.

Cols. Đây là số cột mà hàm cần di chuyển. Điều này cũng được tính từ điểm xuất phát. Số dương ngụ ý chuyển động sang phải, trong khi số âm ngụ ý chuyển động sang trái.

Chiều cao. Đây là chiều cao (số hàng) của phạm vi sẽ được trả về.

Chiều rộng. Đây là chiều rộng (số cột) của phạm vi sẽ được trả về.

Ba đối số đầu tiên là bắt buộc, trong khi hai đối số cuối cùng là tùy chọn.

Lưu ý. Để lấy các giá trị của một dải ô, hãy chọn một dải ô trống có cùng chiều cao và chiều rộng như được chỉ định trong đối số. Tiếp theo, chèn công thức OFFSET và nhấn “Ctrl+Shift+Enter. ”

2. Công thức SUM OFFSET trong Excel là gì?

Trong công thức SUM OFFSET, OFFSET được đặt trong hàm SUM theo cách sau.

“=SUM(ô đầu tiên. (OFFSET(ô chứa tổng,-1,0)))”

“Ô đầu tiên” là địa chỉ ô đầu tiên có giá trị được thêm vào. “Ô chứa tổng” là ô xuất (chứa tổng) ở cuối các ô số. Các số -1 và 0 lần lượt là các đối số “hàng” và “cột”.

Công thức đã cho giúp tìm tổng của một dải ô liền kề. Hàm OFFSET tính toán địa chỉ của dãy. Hàm SUM tính tổng các giá trị của dãy.

Ví dụ: chúng ta hãy tính tổng các giá trị của cột B bao gồm các giá trị 20, 35, 42 và 11 trong phạm vi B1. B 4. Các bước được liệt kê như sau.

• Nhập công thức sau vào ô B5.
“=SUM(B1. (OFFSET(B5,-1,0)))”

• Nhấn phím “Enter”.

Kết quả ở ô B5 là 108.

3. Tại sao nên sử dụng hàm OFFSET và khi nào nên sử dụng hàm này trong Excel?

Lý do sử dụng hàm OFFSET được liệt kê như sau.

• Nó giúp tìm giá trị của một ô (hoặc phạm vi) có địa chỉ không xác định, nhưng đã biết điểm bắt đầu.
• Nó hoạt động với dữ liệu trong đó hàng hoặc cột được thêm thường xuyên.
• Nó giúp tạo phạm vi động cho PivotTable và PivotChart.
• Nó có thể được kết hợp với các chức năng khác yêu cầu địa chỉ ô hoạt động.

Hàm OFFSET excel được sử dụng trong các trường hợp sau.

• Để bao gồm giá trị của một ô mới được chèn vào tổng của một dải ô, hàm OFFSET được sử dụng với hàm SUM.
• Để tìm giá trị trung bình, giá trị lớn nhất hoặc giá trị nhỏ nhất bằng cách thêm hoặc bớt các hàng, hàm OFFSET được sử dụng với các hàm AVERAGE, MAX và MIN.
• Để tạo danh sách thả xuống động (cập nhật các thay đổi trong danh sách nguồn), OFFSET được sử dụng với hàm COUNTA.
• Để khắc phục hạn chế của VLOOKUP (không tra trái được) và HLOOKUP (không tra lên trên được), hàm OFFSET được sử dụng với hàm MATCH.

Bài viết được đề xuất

Đây là bài hướng dẫn hàm OFFSET trong Excel. Ở đây chúng tôi thảo luận về cách sử dụng công thức excel OFFSET cùng với các ví dụ từng bước. Bạn cũng có thể xem các chức năng hữu ích này của Excel–

• OFFSET trong VBA OFFSET Trong VBAChức năng OFFSET trong VBA hỗ trợ người dùng di chuyển xuống hoặc quay lại một ô cụ thể ở một phạm vi cụ thể, i. e. , cách ô hiện tại một số hàng và cột nhất định. đọc thêm
• Hàm FIND trong Excel Hàm FIND Trong ExcelHàm Find trong excel tìm vị trí của một ký tự hoặc một chuỗi con trong một chuỗi văn bản. Nói cách khác, nó tìm thấy sự xuất hiện của một văn bản trong một văn bản khác, vì nó cho chúng ta vị trí, đầu ra mà hàm này trả về là một số nguyên. đọc thêm
• Biểu đồ bong bóng trong Excel Biểu đồ bong bóng trong ExcelTrong Excel, biểu đồ bong bóng là một loại biểu đồ phân tán sử dụng bong bóng để hiển thị giá trị và . Giống như biểu đồ phân tán, biểu đồ bong bóng so sánh dữ liệu trên cả trục ngang và trục dọc. đọc thêm
• Biểu đồ kiểm đếm trong Excel Biểu đồ kiểm đếm trong ExcelBiểu đồ kiểm đếm là một trong những kỹ thuật thu thập dữ liệu có giá trị thông qua các dấu kiểm đếm từ tập dữ liệu. Dấu kiểm đếm là số, lần xuất hiện hoặc tổng tần suất được đo cho một danh mục từ các quan sát được nhóm.

  Làm thế nào để bù đắp trong Excel?

  = OFFSET(tham chiếu, hàng, cột, [chiều cao], [chiều rộng]) . Tham chiếu (đối số bắt buộc) – Đây là phạm vi ô sẽ được bù trừ. Nó có thể là một ô hoặc nhiều ô

  Công thức offset hoạt động như thế nào trong Excel?

  Hàm OFFSET trong Excel trả về một ô hoặc dải ô có số lượng hàng và cột nhất định từ một ô hoặc dải ô nhất định . 3 đối số đầu tiên là bắt buộc và 2 đối số cuối cùng là tùy chọn. Tất cả các đối số có thể là tham chiếu đến các ô khác hoặc kết quả được trả về bởi các công thức khác.

  Làm thế nào bù đắp trong Excel với ví dụ?

  OFFSET có thể được sử dụng với bất kỳ chức năng nào cần đối số tham chiếu. Ví dụ: công thức SUM(OFFSET(C2,1,2,3,1)) tính tổng giá trị của một hàng 3 nhân với 1- .

  Công thức địa chỉ trong Excel là gì?

  Bạn có thể sử dụng hàm ADDRESS để lấy địa chỉ của một ô trong trang tính, cho số hàng và cột được chỉ định . Ví dụ: ADDRESS(2,3) trả về $C$2. Một ví dụ khác, ADDRESS(77,300) trả về $KN$77.