Mỗi khi bảng doanh thu có thêm một tháng, bạn phải sửa lại vùng cộng trong công thức tổng 3 tháng gần nhất. Hàm OFFSET trong Excel tạo một vùng ô dịch chuyển theo số dòng, số cột do công thức tính ra, nên vùng cộng tự đổi khi bảng có thêm số liệu.
Để cộng 3 tháng gần nhất ở cột B, bạn gõ vào ô E2 công thức =SUM(OFFSET(B2,COUNT(B2:B13)-3,0,3,1)). Với bảng có số liệu từ tháng 1 tới tháng 8, công thức cộng tháng 6, 7, 8 và ra 4.567. Khi bạn điền thêm tháng 9, kết quả tự đổi thành 4.665 mà bạn không phải sửa công thức.
Ảnh trong bài được chụp trên Excel 365 giao diện tiếng Anh. Các ô màu vàng nhạt là nơi bạn gõ công thức. Số doanh thu trong bài tính theo đơn vị triệu đồng. Ảnh dùng định dạng số tiếng Anh, nên máy của bạn có thể hiện 4.567 thay vì 4,567. Nếu Excel của bạn báo lỗi khi gõ công thức có dấu phẩy, bạn thay dấu phẩy ngăn tham số bằng dấu chấm phẩy.
Hàm OFFSET có cú pháp như thế nào?
Hàm OFFSET trả về một ô hoặc một vùng ô nằm cách ô gốc một số dòng và một số cột. Hàm này không chép hay di chuyển dữ liệu. Hàm chỉ trả về vùng ô để công thức khác dùng tiếp.
=OFFSET(reference, rows, cols, [height], [width])
Tham số | Ý nghĩa |
|---|---|
reference | Tham số này là ô gốc, nơi Excel bắt đầu đếm. |
rows | Tham số này là số dòng dịch chuyển. Số dương đi xuống dưới, số âm đi lên trên, số 0 giữ nguyên dòng. |
cols | Tham số này là số cột dịch chuyển. Số dương đi sang phải, số âm đi sang trái, số 0 giữ nguyên cột. |
height | Tham số này là số dòng của vùng trả về. Nếu bạn bỏ trống, vùng trả về cao bằng ô gốc. |
width | Tham số này là số cột của vùng trả về. Nếu bạn bỏ trống, vùng trả về rộng bằng ô gốc. |
Bạn có thể nhớ hàm theo thứ tự sau: Excel đứng ở ô gốc, đi xuống rows dòng, đi sang cols cột, rồi lấy một vùng cao height dòng và rộng width cột tính từ ô vừa tới.
Bạn lấy ô cách ô gốc n dòng m cột như thế nào?
Sheet đầu tiên có bảng doanh thu 6 tháng của 3 chi nhánh ở vùng A1:D7. Ô G2 là số dòng dịch xuống, ô G3 là số cột dịch sang phải.
Bạn chọn ô G4, gõ công thức sau rồi nhấn Enter:
=OFFSET(A1,G2,G3)


Kết quả 214 là doanh thu tháng 4 của chi nhánh Đà Nẵng ở ô C5. Khi bạn đổi ô G2 thành 6 và ô G3 thành 3, công thức trả về 762, là doanh thu tháng 6 của TP.HCM. Khi cả hai ô bằng 0, công thức trả về chữ "Tháng" của chính ô A1.
Bạn kết hợp OFFSET với MATCH để lấy số theo tên tháng như thế nào?
Người xem báo cáo thường chọn tên tháng và tên chi nhánh, chứ không gõ số dòng, số cột. Hàm MATCH đổi tên thành vị trí, rồi hàm OFFSET dùng vị trí đó để dịch chuyển. Ở sheet thứ hai, ô G2 chứa tên tháng và ô G3 chứa tên chi nhánh.
Bạn chọn ô G4, gõ công thức sau rồi nhấn Enter:
=OFFSET(A1,MATCH(G2,A2:A7,0),MATCH(G3,B1:D1,0))


Phần MATCH(G2,A2:A7,0) tìm "Tháng 5" trong vùng A2:A7 và trả về 5. Phần MATCH(G3,B1:D1,0) tìm "TP.HCM" trong dòng tiêu đề và trả về 3. Công thức đi từ A1 xuống 5 dòng, sang phải 3 cột, tới ô D6 có giá trị 731.
Số 0 ở cuối hàm MATCH bắt Excel tìm khớp chính xác. Hàm MATCH không phân biệt chữ hoa và chữ thường, nên khi bạn gõ "tháng 5", công thức vẫn ra 731.
Bạn cộng 3 tháng gần nhất bằng SUM và OFFSET như thế nào?
Sheet thứ ba có doanh thu từ tháng 1 tới tháng 8 ở cột B, các tháng sau còn trống. Bạn cần ô E2 luôn cộng 3 tháng có số liệu mới nhất.
Bạn chọn ô E2, gõ công thức sau rồi nhấn Enter:
=SUM(OFFSET(B2,COUNT(B2:B13)-3,0,3,1))


Công thức này chạy theo 4 bước:
Hàm
COUNT(B2:B13)đếm các ô có số trong cột B, ở đây là 8 tháng.Phần
COUNT(B2:B13)-3ra 5, nên Excel đi từ ôB2xuống 5 dòng, tới ôB7của tháng 6.Hai tham số cuối
3,1lấy vùng cao 3 dòng, rộng 1 cột tính từ ôB7, tức là vùngB7:B9.Hàm SUM cộng vùng
B7:B9và ra 4.567.
Tiếp theo, bạn gõ 1608 vào ô B10 của tháng 9 rồi nhấn Enter.

Công thức trong ô E2 giữ nguyên, nhưng hàm COUNT đếm được 9 tháng, nên vùng cộng dời xuống B8:B10. Tổng mới bằng 1.482 + 1.575 + 1.608 = 4.665.
Công thức này cần cột B được điền liền từ tháng 1, không bỏ trống tháng nào ở giữa. Phần lỗi hay gặp ở cuối bài giải thích lý do. Nếu bạn muốn hiểu rõ COUNT và COUNTA đếm những ô nào, bạn có thể đọc bài hàm đếm số lượng trong Excel.
Bạn cộng lũy kế từ đầu năm đến tháng chọn như thế nào?
Lũy kế đến tháng 5 là tổng doanh thu từ tháng 1 tới tháng 5. Vùng cộng luôn bắt đầu ở tháng 1, và chỉ chiều cao của vùng thay đổi theo tháng chọn. Vì vậy, bạn để rows và cols bằng 0, rồi đưa hàm MATCH vào tham số height. Ở sheet thứ tư, ô E2 chứa tháng chọn.
Bạn chọn ô E3, gõ công thức sau rồi nhấn Enter:
=SUM(OFFSET(B2,0,0,MATCH(E2,A2:A13,0),1))


Kết quả 6.500 bằng 1.215 + 1.151 + 1.324 + 1.364 + 1.446. Khi bạn đổi ô E2 thành Tháng 12, công thức cộng cả năm và ra 17.918.
Bạn dùng số âm để lấy ô phía trên như thế nào?
Số âm ở tham số rows đưa Excel đi lên trên, và số âm ở tham số cols đưa Excel sang trái. Ví dụ, khi bạn gõ vào ô G6 của sheet đầu tiên công thức =OFFSET(D7,-2,-1), công thức đi từ D7 lên 2 dòng, sang trái 1 cột, và trả về giá trị 214 của ô C5.
Ô tổng ở cuối bảng là chỗ hay dùng số âm. Khi bạn chèn dòng mới ngay trên dòng tổng rồi gõ tay số vào dòng đó, Excel thường tự đổi =SUM(B2:B6) thành =SUM(B2:B7). Việc tự đổi này chỉ xảy ra khi tùy chọn Extend data range formats and formulas trong File > Options > Advanced đang bật. Khi tùy chọn này tắt, hoặc khi dữ liệu được ghi bằng macro, công thức =SUM(B2:B6) giữ nguyên và bỏ sót dòng mới. Bạn tránh phụ thuộc vào tùy chọn này bằng cách cho điểm cuối của vùng cộng là ô nằm ngay trên ô tổng.
Sheet thứ năm có 5 khoản chi ở vùng A2:B6 và dòng tổng ở dòng 7. Bạn chọn ô B7, gõ công thức sau rồi nhấn Enter:
=SUM(B2:OFFSET(B7,-1,0))


Tiếp theo, bạn bấm chuột phải vào số 7 ở đầu dòng 7 rồi chọn Insert để chèn một dòng trống. Bạn gõ Phí gửi xe vào ô A7, gõ 900000 vào ô B7 rồi nhấn Enter.

Sau khi bạn chèn dòng, Excel tự đổi B7 trong công thức thành B8, nên điểm cuối của vùng cộng là B7, đúng dòng vừa thêm. Tổng mới bằng 5.140.000 + 900.000 = 6.040.000. Công thức OFFSET cho kết quả này dù tùy chọn tự mở rộng đang bật hay tắt. Nếu ô tổng dùng =SUM(B2:B6) và tùy chọn đang tắt, tổng vẫn là 5.140.000 vì vùng cộng không chứa dòng 7.
Bạn tạo vùng tự mở rộng bằng OFFSET và COUNTA như thế nào?
Danh sách sản phẩm thường dài thêm theo thời gian. Nếu danh sách thả xuống lấy cố định vùng A2:A6, sản phẩm thứ sáu không hiện trong danh sách. Bạn dùng hàm COUNTA để đếm số ô có dữ liệu, rồi đưa kết quả vào tham số height để vùng tự dài theo danh sách.
Bước 1: Bạn kiểm tra vùng mà OFFSET trả về
Sheet Sản phẩm có tiêu đề ở ô A1 và 5 sản phẩm ở vùng A2:A6. Bạn chọn ô C2, gõ công thức sau rồi nhấn Enter:
=OFFSET(A2,0,0,COUNTA(A:A)-1,1)


Cột A có 6 ô có dữ liệu, gồm ô tiêu đề và 5 sản phẩm. Công thức lấy vùng bắt đầu ở A2, cao 6 - 1 = 5 dòng, tức là vùng A2:A6. Excel 365 tự trải 5 giá trị này xuống vùng C2:C6. Ô C2 chỉ dùng để kiểm tra, nên bạn có thể xóa ô này sau khi xem xong.
Tiếp theo, bạn gõ Webcam HD vào ô A7 rồi nhấn Enter.

Bước 2: Bạn đặt tên cho vùng tự mở rộng
Bạn mở thẻ Formulas, bấm Define Name. Trên màn hình nhỏ, Excel gom các nút đặt tên vào nút Defined Names, khi đó bạn bấm Defined Names rồi chọn Define Name. Trong hộp thoại New Name, bạn xóa chữ có sẵn trong ô Name nếu có, rồi gõ DanhSachSP. Excel hay tự điền vào ô này nội dung của ô đang chọn, ví dụ Webcam_HD. Bạn xóa hết nội dung có sẵn trong ô Refers to, rồi gõ công thức sau:
=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)
Bạn cần gõ dấu $ để khóa vùng. Nếu thiếu dấu $, Excel hiểu vùng theo ô đang chọn lúc bạn tạo tên, và tên trỏ lệch khi bạn dùng ở ô khác. Khi bạn bấm OK, Excel tự thêm tên sheet Sản phẩm vào công thức.

Bạn bấm OK để lưu tên. Khi cần sửa hoặc xóa tên này, bạn mở Formulas, chọn Name Manager.
Bước 3: Bạn dùng tên vùng cho danh sách thả xuống
Ở sheet Đơn hàng, bạn quét chọn vùng B2:B6, mở thẻ Data rồi bấm Data Validation. Ở ô Allow, bạn chọn List. Ở ô Source, bạn gõ =DanhSachSP rồi bấm OK.


Từ nay, khi bạn thêm sản phẩm vào cuối cột A của sheet Sản phẩm, danh sách thả xuống tự có thêm sản phẩm đó. Bạn có thể xem thêm các cách tạo danh sách khác trong bài tạo danh sách thả xuống trong Excel.
Cột A cần được điền liền nhau từ ô A2. Nếu bạn để trống một ô ở giữa, COUNTA đếm thiếu một ô, và sản phẩm cuối cùng bị mất khỏi danh sách.
Vì sao OFFSET không đặt trong SUM lại ra nhiều giá trị hoặc lỗi #VALUE!?
Hàm OFFSET trả về một vùng ô, không trả về một con số. Khi vùng có nhiều ô và bạn không bọc OFFSET trong một hàm như SUM, kết quả phụ thuộc vào phiên bản Excel:
Excel 365, Excel 2021 và Excel 2024 trải các giá trị của vùng xuống các ô bên dưới, như ví dụ ở ô
C2của sheet Sản phẩm. Nếu các ô bên dưới đã có dữ liệu, ô công thức báo#SPILL!.Excel 2019 trở về trước thường báo
#VALUE!. Với vùng một cột, Excel chỉ lấy được một giá trị khi ô công thức nằm cùng dòng với một ô trong vùng.
Vì vậy, khi cần một con số, bạn bọc OFFSET trong hàm SUM, AVERAGE, COUNT, MAX hoặc một hàm khác nhận vùng ô.
Khi nào bạn nên dùng INDEX thay cho OFFSET?
OFFSET là hàm volatile. Excel tính lại mọi công thức chứa hàm volatile mỗi khi bạn sửa bất kỳ ô nào trong file, kể cả ô không liên quan. Với file nhỏ, bạn không thấy khác biệt. Với file có hàng nghìn công thức OFFSET, Excel phải tính lại hết các công thức này sau mỗi lần nhập liệu, nên file chạy chậm.
Hàm INDEX cũng trả về được vùng ô, nhưng INDEX không phải hàm volatile. Bảng dưới đây có hai cách viết cho cùng một kết quả với dữ liệu trong bài:
Việc cần làm | Công thức OFFSET | Công thức INDEX |
|---|---|---|
Bạn cộng 3 tháng gần nhất ở ô |
|
|
Bạn cộng lũy kế đến tháng chọn ở ô |
|
|
Bạn tạo vùng tự mở rộng cho tên DanhSachSP |
|
|
Hai cách cho kết quả khác nhau ở hai trường hợp biên. Khi cột B mới có 1 tháng số liệu, công thức INDEX báo #VALUE! thay vì #REF!, và cách sửa bằng hàm MAX ở bảng lỗi cuối bài chỉ dùng cho công thức OFFSET. Khi danh sách sản phẩm trống, tên vùng dạng INDEX trả về vùng A1:A2 có cả ô tiêu đề, còn tên vùng dạng OFFSET báo #REF!.
Bạn nên dùng INDEX khi file lớn hoặc khi công thức được kéo xuống hàng nghìn dòng. Với file nhỏ, bạn có thể giữ OFFSET, vì công thức OFFSET đọc theo đúng thứ tự ô gốc, số dòng, số cột, chiều cao và chiều rộng.
Hàm INDIRECT cũng là hàm volatile. INDIRECT tạo vùng ô từ một đoạn chữ, ví dụ tên sheet nằm trong một ô, còn OFFSET tạo vùng ô bằng cách dịch chuyển từ ô gốc. Bạn có thể đọc cách dùng INDIRECT trong bài hàm INDIRECT trong Excel.
Bạn hay gặp lỗi gì khi dùng hàm OFFSET?
Lỗi | Nguyên nhân | Cách sửa |
|---|---|---|
Công thức báo | Vùng trả về vượt ra ngoài sheet, ví dụ | Bạn kiểm tra lại số dòng và số cột, nhất là khi chúng là kết quả của một công thức khác. |
Ô tổng 3 tháng báo | Cột B mới có 1 tháng số liệu, nên | Bạn sửa công thức ở ô |
Tên vùng báo | Cột A chỉ còn ô tiêu đề, nên | Bạn giữ ít nhất một sản phẩm trong danh sách. |
Tổng 3 tháng thiếu tháng mới nhất | Cột B bỏ trống một tháng ở giữa, hoặc một tháng được gõ dạng chữ. COUNT đếm thiếu, nên vùng cộng dừng sớm một dòng. Khi tháng 4 bỏ trống, tổng ra 4.438 thay vì 4.567. | Bạn điền 0 cho tháng không có doanh thu, và đổi số dạng chữ thành số. |
Danh sách thả xuống thiếu sản phẩm cuối | Cột A có ô trống ở giữa danh sách, nên COUNTA đếm thiếu. | Bạn xóa dòng trống hoặc điền đủ ô trống. |
Danh sách thả xuống có một dòng trống ở cuối | Công thức thiếu phần | Bạn trừ 1 cho mỗi ô tiêu đề nằm trong cột A. |
Ô công thức báo | OFFSET trả về nhiều ô, nhưng các ô bên dưới đã có dữ liệu. | Bạn bọc OFFSET trong SUM nếu cần một con số, hoặc xóa dữ liệu ở vùng bên dưới. |
Công thức có MATCH báo | MATCH không tìm thấy tên tháng hoặc tên chi nhánh, ví dụ ô | Bạn xóa dấu cách thừa, hoặc tạo danh sách thả xuống cho ô chọn tháng. |
File chạy chậm khi nhập liệu | File có nhiều công thức OFFSET, và Excel tính lại tất cả sau mỗi lần sửa ô. | Bạn đổi sang công thức INDEX như bảng ở mục trên. |
Bạn muốn học Excel bài bản hơn?
Nếu muốn học bài bản cùng người hướng dẫn, bạn có thể tham khảo khóa 30 ngày học và thực hành Excel của Nghề Excel, với 30 buổi luyện tập, hơn 500 bài tập thực tế lấy từ doanh nghiệp và coach đồng hành cùng bạn trong từng buổi.
Ảnh chụp màn hình sản phẩm Microsoft: Used with permission from Microsoft.