=OFFSET(A1,3,2) trả về ô cách A1 3 hàng xuống dưới và 2 cột sang phải, tức là C4. Khi bạn đưa thêm chiều cao và chiều rộng, hàm trả về cả một vùng, và đó là công dụng chính của OFFSET: tính tổng và trung bình trên một vùng có thể dịch chuyển hoặc mở rộng.
| A | B | C | D | E | F | G | |
|---|---|---|---|---|---|---|---|
| 1 | Product | Category | Price | Rows | Cols | Result | |
| 2 | Apple | Fruit | $1.20 | 3 | 2 | $0.80 | |
| 3 | Pear | Fruit | $1.50 | ||||
| 4 | Carrot | Vegetable | $0.80 | ||||
| 5 | Bread | Bakery | $2.40 | ||||
| 6 | Milk | Dairy | $1.10 |
Đi xuống 3 hàng và sang 2 cột từ A1 sẽ tới C4, giá của Carrot, $0.80. Đặt Cols bằng 0 để lấy tên Carrot, hoặc Rows bằng 5 để tới hàng của Milk. Số hàng và số cột có thể âm để đi lên hoặc lùi lại, còn dịch ra ngoài mép trên hay mép trái của trang tính sẽ báo #REF!.
Cú pháp OFFSET
=OFFSET(reference, rows, cols, [height], [width])
reference: ô gốc (hoặc vùng gốc).rows,cols: dịch đi bao xa. 0 nghĩa là giữ nguyên.height,width: kích thước của vùng cần trả về, tính từ ô đã dịch tới. Nếu bỏ trống, kích thước bằng kích thước củareference.
Khi đứng một mình trong ô, một hàm OFFSET trả về nhiều ô sẽ tràn (spill) ra trong Excel 365; các phiên bản cũ hơn thường hiện #VALUE!. Đặt trong SUM, AVERAGE, COUNT hay MAX thì nó hoạt động như một vùng.
Tính tổng N hàng cuối
Việc kinh điển của OFFSET: một tổng luôn bao trùm các hàng mới nhất, dù đã thêm bao nhiêu hàng. COUNT tìm xem có bao nhiêu giá trị, OFFSET dịch xuống giá trị đầu tiên trong N giá trị cuối, và chiều cao lấy N hàng.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | Last N | Total | ||
| 2 | Jan | 4,200 | 3 | 14,900 | ||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
Có 7 giá trị, nên OFFSET bắt đầu cách B1 7-3+1, tức 5 hàng, tại B6, và lấy 3 hàng: tháng 5 đến tháng 7, tổng 14.900. Gõ 4900 vào B9 (tháng 8) thì tổng chuyển sang tháng 6, 7 và 8, vì giờ COUNT đếm được 8. Vùng B2:B13 chừa chỗ cho các tháng còn lại trong năm. Cột không được có ô trống xen giữa, nếu không COUNT sẽ đếm thiếu và cửa sổ rơi sai chỗ.
Trung bình trượt
Khi kéo xuống một cột, OFFSET với số hàng âm cho mỗi hàng một cửa sổ gồm các hàng phía trên: ở đây là trung bình của tháng hiện tại và hai tháng trước đó.
| A | B | C | |
|---|---|---|---|
| 1 | Month | Sales | 3-month average |
| 2 | Jan | 4,200 | |
| 3 | Feb | 3,900 | |
| 4 | Mar | 4,800 | 4,300 |
| 5 | Apr | 5,100 | 4,600 |
| 6 | May | 4,600 | 4,833 |
| 7 | Jun | 5,300 | 5,000 |
| 8 | Jul | 5,000 | 4,967 |
C4 tính trung bình B2:B4 (tháng 1 đến tháng 3), 4.300. Mỗi hàng bên dưới dịch cửa sổ xuống một hàng. Đổi 3 thành 6 và -2 thành -5 để có trung bình sáu tháng (khi đó bắt đầu công thức từ hàng 7). Riêng trường hợp này thì không cần OFFSET: =AVERAGE(B2:B4) kéo xuống từ C4 cũng cho kết quả tương tự, vì tham chiếu tương đối vốn đã tự dịch chuyển. OFFSET chỉ thật sự đáng dùng khi kích thước cửa sổ lấy từ một ô.
Vì sao INDEX thường là lựa chọn tốt hơn
OFFSET là hàm volatile: Excel tính lại mọi hàm OFFSET sau bất kỳ chỉnh sửa nào ở bất kỳ đâu trong workbook, vì không thể biết trước hàm sẽ trỏ tới ô nào. Một trang tính có hàng nghìn hàm như vậy sẽ chậm đi. INDEX cũng trả về tham chiếu, và một vùng viết dạng start:INDEX(...) mở rộng theo cùng cách mà không volatile:
=SUM(OFFSET(B2, 0, 0, E2, 1)) first E2 rows, volatile
=SUM(B2:INDEX(B2:B13, E2)) same rows, not volatile
Cả hai đều đọc E2 hàng đầu tiên của cột. OFFSET cũng khó kiểm tra hơn: Trace Precedents và các đường viền màu Excel vẽ khi bạn sửa công thức chỉ cho thấy ô gốc và các đối số, chứ không cho thấy vùng mà OFFSET thực sự trả về. Dùng OFFSET cho mô hình nhanh hoặc vùng dữ liệu của biểu đồ; với workbook lớn, ưu tiên INDEX. Trang INDEX nói thêm về cách trả về vùng, còn INDIRECT là hàm tham chiếu volatile còn lại.
Luyện tập: tổng N tháng đầu tiên
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Month | Sales | First N | Total | ||
| 2 | Jan | 4,200 | 4 | |||
| 3 | Feb | 3,900 | ||||
| 4 | Mar | 4,800 | ||||
| 5 | Apr | 5,100 | ||||
| 6 | May | 4,600 | ||||
| 7 | Jun | 5,300 | ||||
| 8 | Jul | 5,000 |
Đến lượt bạn: Trong F2, dùng OFFSET bên trong SUM để tính tổng N tháng đầu tiên, với N nằm ở E2.
Câu hỏi thường gặp
Hàm OFFSET trong Excel dùng để làm gì?
Hàm trả về một tham chiếu cách ô gốc một số hàng và số cột cho trước, có thể kèm thay đổi kích thước. =OFFSET(A1,3,2) là ô cách A1 3 hàng xuống dưới và 2 cột sang phải, tức là C4.
Làm sao tính tổng N hàng cuối trong Excel?
Bắt đầu từ ô tiêu đề và dịch xuống tới giá trị đầu tiên trong N giá trị cuối: =SUM(OFFSET(B1,COUNT(B2:B100)-N+1,0,N,1)). COUNT tìm xem có bao nhiêu giá trị, và chiều cao N lấy đúng chừng đó hàng. Cách này chỉ đúng khi cột không có ô trống xen giữa.
Vì sao OFFSET là hàm volatile?
Excel tính lại mọi hàm OFFSET sau bất kỳ thay đổi nào trong workbook, vì chỉ sau khi hàm chạy mới biết được nó trỏ tới ô nào. Với workbook lớn, điều này làm mọi thứ chậm đi. Một vùng dựng bằng INDEX, như B2:INDEX(B2:B100,N), làm được việc tương tự mà không volatile.
Các đối số của hàm OFFSET là gì?
OFFSET(reference, rows, cols, [height], [width]): ô gốc, dịch xuống bao nhiêu hàng (số âm là đi lên), dịch sang bao nhiêu cột (số âm là lùi lại), và tùy chọn kích thước của vùng cần trả về.
