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)

Bạn đang gõ vào ô G4 công thức =OFFSET(A1,G2,G3), thanh công thức hiện cùng nội dung
Ô gốc là A1, số dòng lấy ở ô G2 và số cột lấy ở ô G3
Ô G4 đang chọn, thanh công thức hiện =OFFSET(A1,G2,G3), kết quả là 214
Excel đi từ A1 xuống 4 dòng tới A5, rồi sang phải 2 cột tới C5

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))

Bạn đang gõ vào ô G4 công thức =OFFSET(A1,MATCH(G2,A2:A7,0),MATCH(G3,B1:D1,0))
Hai hàm MATCH thay cho số dòng và số cột gõ tay
Ô G4 đang chọn, kết quả doanh thu tháng 5 của TP.HCM là 731
Tháng 5 nằm ở vị trí 5 trong cột A, TP.HCM nằm ở vị trí 3 trong dòng tiêu đề

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))

Bạn đang gõ vào ô E2 công thức =SUM(OFFSET(B2,COUNT(B2:B13)-3,0,3,1))
Hàm COUNT đếm số tháng đã có số liệu trong cột B
Ô E2 đang chọn, tổng 3 tháng gần nhất là 4,567
Công thức cộng tháng 6, 7 và 8: 1.510 + 1.482 + 1.575 = 4.567

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)-3 ra 5, nên Excel đi từ ô B2 xuống 5 dòng, tới ô B7 của tháng 6.

  • Hai tham số cuối 3,1 lấy vùng cao 3 dòng, rộng 1 cột tính từ ô B7, tức là vùng B7:B9.

  • Hàm SUM cộng vùng B7:B9 và ra 4.567.

Tiếp theo, bạn gõ 1608 vào ô B10 của tháng 9 rồi nhấn Enter.

Ô E2 đang chọn, thanh công thức vẫn là công thức cũ, tháng 9 ở ô B10 là 1,608 và tổng đổi thành 4,665
Vùng cộng tự dời xuống B8:B10, tức là tháng 7, 8 và 9

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))

Bạn đang gõ vào ô E3 công thức =SUM(OFFSET(B2,0,0,MATCH(E2,A2:A13,0),1))
MATCH trả về 5 cho Tháng 5, nên vùng cộng cao 5 dòng
Ô E3 đang chọn, doanh thu lũy kế đến tháng 5 là 6,500
Công thức cộng vùng B2:B6, từ tháng 1 tới tháng 5

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))

Bạn đang gõ vào ô B7 công thức =SUM(B2:OFFSET(B7,-1,0))
OFFSET(B7,-1,0) trả về ô B6, ngay trên ô tổng
Ô B7 đang chọn, tổng 5 khoản chi là 5,140,000
Vùng cộng lúc này là B2:B6

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.

Ô B8 đang chọn, thanh công thức hiện =SUM(B2:OFFSET(B8,-1,0)), tổng mới là 6,040,000
Ô tổng dời xuống B8, OFFSET(B8,-1,0) trả về B7 nên dòng mới được cộng

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)

Bạn đang gõ vào ô C2 công thức =OFFSET(A2,0,0,COUNTA(A:A)-1,1)
COUNTA(A:A) đếm cả ô tiêu đề A1, nên công thức trừ đi 1
Ô C2 đang chọn, vùng C2:C6 hiện đủ 5 sản phẩm của cột A
Excel 365 trải kết quả xuống các ô bên dưới, nên bạn thấy ngay vùng OFFSET trả về

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.

Ô A7 vừa thêm Webcam HD, vùng ở cột C tự dài thành 6 sản phẩm
COUNTA đếm được 7 ô, nên vùng OFFSET cao 6 dòng

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.

Hộp thoại New Name có ô Name là DanhSachSP và ô Refers to là công thức OFFSET, con trỏ chuột chỉ vào nút OK
Tên DanhSachSP thay cho công thức OFFSET, nên bạn dùng tên này ở nhiều nơi mà không gõ lại 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.

Hộp thoại Data Validation chọn List, ô Source là =DanhSachSP, con trỏ chuột chỉ vào nút OK
Ô Source dùng tên vùng thay cho địa chỉ cố định
Danh sách thả xuống ở ô B2 hiện đủ 6 sản phẩm, Webcam HD nằm ở cuối
Sản phẩm vừa thêm ở dòng 7 đã có trong danh sách

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ụ ở ô C2 củ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 ở ô E2

=SUM(OFFSET(B2,COUNT(B2:B13)-3,0,3,1))

=SUM(INDEX(B2:B13,COUNT(B2:B13)-2):INDEX(B2:B13,COUNT(B2:B13)))

Bạn cộng lũy kế đến tháng chọn ở ô E3

=SUM(OFFSET(B2,0,0,MATCH(E2,A2:A13,0),1))

=SUM(B2:INDEX(B2:B13,MATCH(E2,A2:A13,0)))

Bạn tạo vùng tự mở rộng cho tên DanhSachSP

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

=$A$2:INDEX($A:$A,COUNTA($A:$A))

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 #REF!

Vùng trả về vượt ra ngoài sheet, ví dụ =OFFSET(A1,-1,0) đi lên trên dòng 1.

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 #REF!

Cột B mới có 1 tháng số liệu, nên COUNT(B2:B13)-3 ra -2 và vùng bắt đầu ở trên dòng 1.

Bạn sửa công thức ở ô E2 thành =SUM(OFFSET(B2,MAX(0,COUNT(B2:B13)-3),0,3,1)). Hàm MAX giữ số dòng dịch chuyển không nhỏ hơn 0.

Tên vùng báo #REF! khi danh sách trống

Cột A chỉ còn ô tiêu đề, nên COUNTA($A:$A)-1 ra 0. Tham số height bằng 0 làm OFFSET báo lỗi.

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 -1 sau COUNTA, nên vùng tính cả ô tiêu đề vào chiều cao.

Bạn trừ 1 cho mỗi ô tiêu đề nằm trong cột A.

Ô công thức báo #SPILL!

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 #N/A

MATCH không tìm thấy tên tháng hoặc tên chi nhánh, ví dụ ô G2 có dấu cách thừa ở cuối "Tháng 5 ".

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.