Hàm OFFSET và INDIRECT trong Excel: tham chiếu động nâng cao

Tham chiếu động là gì? Vì sao cần OFFSET và INDIRECT?

Tham chiếu bình thường trong Excel (như A2:A10) là tham chiếu cố định — vùng tính toán luôn nằm nguyên một chỗ, dù dữ liệu có thêm dòng hay không. Vấn đề phát sinh khi bảng dữ liệu thay đổi kích thước liên tục: mỗi tháng thêm vài chục dòng doanh số mới, nhưng công thức SUM hay biểu đồ vẫn chỉ tính đến dòng cũ vì vùng tham chiếu không tự giãn ra.

OFFSET và INDIRECT là hai hàm giải quyết đúng vấn đề này — chúng tạo ra vùng tham chiếu tự thay đổi dựa trên công thức, thay vì gõ chết một địa chỉ ô. Đây là hai hàm nâng cao, nên bài này giả định bạn đã quen các hàm cơ bản như SUM, VLOOKUP.

Ví dụ dữ liệu dùng xuyên suốt bài — bảng doanh số theo tháng, sẽ tiếp tục được thêm dòng mới mỗi tháng:

	A	B
1	Tháng	Doanh số
2	Tháng 1	42,000,000
3	Tháng 2	38,500,000
4	Tháng 3	51,000,000
5	Tháng 4	47,200,000

Hàm OFFSET: dịch chuyển vùng tham chiếu theo tọa độ

Cú pháp

=OFFSET(vùng_gốc, số_dòng_dịch, số_cột_dịch, [chiều_cao], [chiều_rộng])

Trong đó:

• vùng_gốc: ô hoặc vùng bắt đầu để tính dịch chuyển từ đó.
• số_dòng_dịch: số dòng cần dịch xuống (số dương) hoặc lên (số âm) so với vùng gốc.
• số_cột_dịch: số cột cần dịch sang phải (số dương) hoặc sang trái (số âm).
• chiều_cao, chiều_rộng: (tùy chọn) kích thước vùng kết quả — bỏ trống thì mặc định bằng kích thước vùng gốc.

OFFSET không tự "chứa" dữ liệu, nó chỉ trả về một vùng tham chiếu mới dựa trên vùng gốc đã dịch chuyển — nên luôn phải bọc trong một hàm khác (SUM, COUNT, AVERAGE...) để lấy được kết quả tính toán thật sự.

Ví dụ 1: Lấy giá trị của ô cách vị trí gốc một khoảng

=OFFSET(A1, 2, 1)

Bắt đầu từ ô A1, dịch xuống 2 dòng, sang phải 1 cột → trỏ đến ô B3, kết quả trả về 38,500,000. Cách này ít dùng trực tiếp để lấy 1 ô, nhưng là nền tảng để hiểu cơ chế dịch chuyển trước khi ghép với vùng động.

Ví dụ 2: SUM một vùng tự động giãn theo dữ liệu mới

Đây là ứng dụng thực tế nhất của OFFSET — tính tổng doanh số mà không cần sửa công thức mỗi khi thêm dòng:

=SUM(OFFSET($B$2, 0, 0, COUNTA($A:$A)-1, 1))

Cách đọc: OFFSET($B$2, 0, 0, ...) bắt đầu từ B2, không dịch dòng hay cột nào, nhưng chiều cao vùng được tính động bằng COUNTA($A:$A)-1 — tức đếm số ô có dữ liệu trong cột A (trừ đi 1 dòng tiêu đề). Khi thêm "Tháng 5" vào A6 và doanh số vào B6, COUNTA($A:$A) tự tăng lên 6, chiều cao vùng OFFSET tự giãn thành 5 dòng (B2:B6), SUM tính đúng luôn mà không cần sửa lại vùng tham chiếu.

Ví dụ 3: Lấy giá trị dòng cuối cùng của cột (kể cả khi thêm dòng mới)

=OFFSET($B$1, COUNTA($B:$B)-1, 0)

Bắt đầu từ B1 (dòng tiêu đề), dịch xuống theo đúng số dòng có dữ liệu trong cột B trừ 1 (vì COUNTA đếm luôn cả tiêu đề) → luôn trỏ đúng ô chứa doanh số của tháng mới nhất, dù bảng có bao nhiêu dòng đi nữa. Công thức này hữu ích khi cần hiển thị "doanh số tháng gần nhất" trên một ô báo cáo riêng.

Hàm INDIRECT: biến chuỗi văn bản thành tham chiếu thật

Cú pháp

=INDIRECT(chuỗi_địa_chỉ, [kiểu_tham_chiếu])

Trong đó:

• chuỗi_địa_chỉ: một chuỗi văn bản (hoặc ô chứa chuỗi văn bản) viết đúng dạng địa chỉ ô, ví dụ "B2" hoặc "Thang1!B2".
• kiểu_tham_chiếu: TRUE (mặc định, kiểu A1 quen thuộc) hoặc FALSE (kiểu R1C1) — hầu như luôn bỏ trống để dùng kiểu A1.

Khác với OFFSET (dịch chuyển theo tọa độ số), INDIRECT xây tham chiếu từ chuỗi chữ, nên rất mạnh khi cần ghép tên sheet hoặc địa chỉ ô lại từ nhiều phần.

Ví dụ 1: Đổi vùng tính theo nội dung một ô

=INDIRECT("B" & 3)

Excel ghép chuỗi "B" với số 3 thành "B3", rồi INDIRECT hiểu đây là tham chiếu đến ô B3 → kết quả 51,000,000. Nếu thay số 3 bằng một ô chứa số (ví dụ ô D1 đang gõ số 4), công thức =INDIRECT("B" & D1) sẽ tự đổi sang lấy giá trị B4 khi bạn đổi D1 từ 3 thành 4 — không cần sửa công thức gốc.

Ví dụ 2: Lấy dữ liệu từ nhiều sheet có tên đặt theo quy luật

Giả sử có các sheet đặt tên "Thang1", "Thang2", "Thang3"... mỗi sheet đều có doanh số ở ô B2. Muốn lấy nhanh doanh số của "tháng số mấy" dựa vào một ô chọn:

=INDIRECT("Thang" & C1 & "!B2")

Nếu ô C1 đang gõ số 2, công thức ghép thành chuỗi "Thang2!B2", INDIRECT hiểu đây là tham chiếu sang sheet "Thang2", ô B2 → trả về đúng doanh số tháng 2. Đổi C1 thành 3, công thức tự chuyển sang lấy dữ liệu từ sheet "Thang3" mà không cần sửa gì trong công thức — rất hữu ích khi có hàng chục sheet theo tháng hoặc theo chi nhánh.

Ví dụ 3: Tạo danh sách chọn (Data Validation) phụ thuộc lẫn nhau

INDIRECT thường được dùng để làm dropdown 2 cấp — chọn "Phòng ban" ở ô A1 thì dropdown "Nhân viên" ở ô A2 chỉ hiện đúng nhân viên phòng đó. Cách làm: đặt tên vùng (Name Range) cho từng danh sách nhân viên theo đúng tên phòng ban (ví dụ vùng tên "KinhDoanh", "KyThuat"), sau đó ở Data Validation của ô A2, chọn List và nhập:

=INDIRECT($A$1)

Khi A1 đang chọn "KinhDoanh", INDIRECT trả về đúng vùng đã đặt tên "KinhDoanh" để làm nguồn cho dropdown A2. Đây là kỹ thuật hay dùng trong các file quản lý nhân sự hoặc đơn hàng có danh mục phân cấp.

So sánh nhanh OFFSET và INDIRECT

	OFFSET	INDIRECT
Cách xây tham chiếu	Dịch chuyển theo tọa độ số (dòng, cột)	Ghép chuỗi văn bản thành địa chỉ
Phù hợp nhất khi	Vùng dữ liệu tự giãn theo số dòng/cột	Cần đổi sheet hoặc địa chỉ theo lựa chọn người dùng
Đổi tên sheet trong công thức	Không hỗ trợ trực tiếp	Làm được, ghép tên sheet vào chuỗi
Ảnh hưởng khi chèn/xóa dòng, cột	Excel không tự cập nhật công thức chứa OFFSET	Tương tự, vì tham chiếu là chuỗi văn bản, không phải ô thật
Tốc độ tính với file lớn	Là hàm "dễ bay hơi" (volatile), tính lại liên tục	Cũng là hàm volatile, tính lại liên tục

Vì sao OFFSET và INDIRECT làm file Excel chạy chậm?

Cả hai hàm này thuộc nhóm hàm dễ bay hơi (volatile function) — nghĩa là Excel tính toán lại kết quả của chúng mỗi khi có bất kỳ thay đổi nào trên toàn bộ file, kể cả khi thay đổi đó không liên quan gì đến vùng dữ liệu của công thức. Với file nhỏ vài trăm dòng thì không đáng lo, nhưng nếu dùng OFFSET/INDIRECT lặp lại ở hàng nghìn ô trong file lớn, file sẽ giật, lag rõ rệt mỗi lần gõ hoặc sửa bất kỳ ô nào.

Cách hạn chế: chỉ dùng OFFSET/INDIRECT ở những công thức thực sự cần tính động (ví dụ vùng SUM tổng hợp, dropdown phụ thuộc), còn lại dùng tham chiếu thường hoặc chuyển bảng dữ liệu sang dạng Table (Ctrl+T) — Table có vùng tự giãn sẵn mà không cần OFFSET, vừa gọn vừa không làm chậm file.

Lỗi thường gặp

Lỗi #REF! khi dùng OFFSET Xảy ra khi số dòng/cột dịch chuyển vượt ra ngoài phạm vi bảng tính (ví dụ dịch lên trên hàng 1, hoặc dịch ra ngoài cột A). Kiểm tra lại dấu và giá trị của số_dòng_dịch, số_cột_dịch.

Lỗi #REF! khi dùng INDIRECT Do chuỗi văn bản ghép ra không đúng định dạng địa chỉ ô hoặc tên sheet không tồn tại — ví dụ gõ nhầm dấu cách, thiếu dấu ! giữa tên sheet và địa chỉ ô. Có thể kiểm tra bằng cách tách riêng phần ghép chuỗi ra một ô khác (không bọc INDIRECT) để xem chuỗi thật sự ra là gì trước khi debug tiếp.

Tên sheet có khoảng trắng nhưng quên bọc dấu nháy đơn Nếu tên sheet có dấu cách (ví dụ "Thang 1"), phải viết 'Thang 1'!B2 chứ không phải Thang 1!B2 — khi ghép chuỗi trong INDIRECT cũng phải giữ đúng cặp dấu nháy đơn này, nếu không sẽ báo lỗi #REF!.

File chạy chậm bất thường sau khi thêm nhiều công thức OFFSET/INDIRECT Đây không phải lỗi công thức mà là hệ quả của tính chất volatile đã nói ở trên. Cân nhắc thay bằng Table (Ctrl+T) hoặc INDEX (không volatile) nếu chỉ cần lấy vùng dữ liệu cố định theo kích thước, không cần đổi sheet linh hoạt.

Khi nào nên dùng OFFSET, khi nào nên dùng INDIRECT?

• Cần vùng tính (SUM, COUNT, biểu đồ) tự giãn theo số dòng dữ liệu mới thêm vào → dùng OFFSET, hoặc cân nhắc chuyển sang Table nếu không bắt buộc phải dùng OFFSET.
• Cần đổi tên sheet hoặc ghép địa chỉ ô từ lựa chọn của người dùng (dropdown, ô nhập liệu) → dùng INDIRECT.
• Cần dropdown 2 cấp phụ thuộc lẫn nhau → dùng INDIRECT kết hợp Name Range.
• File có rất nhiều dòng dữ liệu và cần tối ưu tốc độ → hạn chế cả hai hàm, ưu tiên Table hoặc INDEX/MATCH.

Học Excel thực chiến tại TP Vinh

OFFSET và INDIRECT là hai hàm nâng cao giúp công thức tự thích ứng khi dữ liệu thay đổi, nhưng cần dùng đúng lúc để tránh làm chậm file. Nếu bạn muốn học Excel bài bản từ các hàm cơ bản đến kỹ thuật xử lý dữ liệu nâng cao, tham khảo khóa học Excel tại Điền Nguyễn AZ.

📞 Hotline / Zalo: 0362 465 361

👉 Xem chi tiết: Khóa học Excel thực tế tại TP Vinh