Blog

Phạm vi động trong Excel: khi nào dùng OFFSET hoặc bảng

Cập nhật tổng và lựa chọn thả xuống khi thêm hàng. Xem cách dùng OFFSET và COUNTA, tác động của ID trống và cách dùng bảng Excel.

Phạm vi tham chiếu được tô sáng mở rộng xuống dưới khi thêm hàng.

Bạn thêm sản phẩm vào danh sách Excel nhưng số tổng và các lựa chọn thả xuống không đổi. Nếu tham chiếu vẫn là A2:A10, hàng 11 nằm ngoài phạm vi. Phạm vi động cho phép Excel tự xác định điểm cuối của danh sách mà không cần sửa công thức mỗi lần cập nhật.

Dùng OFFSET để xác định số hàng và cột

Với danh sách liền mạch, đếm các tiêu đề cột và ID sản phẩm để cung cấp chiều cao và chiều rộng cho OFFSET. Tạo mẫu trên Sheet1 và nhập công thức tính tổng trên trang khác. Giữ nguyên tiêu đề tiếng Nhật để các tham chiếu có cấu trúc ở phần sau hoạt động.

Ô Cột A Cột B Cột C
Hàng 1 商品ID (ID sản phẩm) 商品名 (tên sản phẩm) 数量 (số lượng)
Hàng 2 A01 Bút 4
Hàng 3 A02 Vở 6
=SUM(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),COUNTA(Sheet1!$A$1:$Z$1)))

Bắt đầu từ A1 mà không dịch theo hàng hay cột. Số ô có dữ liệu ở cột A là chiều cao, số tiêu đề ở hàng 1 là chiều rộng. Mẫu trả về A1:C3 với ba hàng, ba cột và tổng 10. Thêm A03, Thước và 2 ở hàng 4 để được A1:C4 và tổng 12. ID có dạng văn bản, và phạm vi đếm không chứa dữ liệu khác. Tài liệu OFFSET của Microsoft

Giờ chỉ xóa ID ở A3. Ba ô được đếm trong cột A là tiêu đề A1 và ID ở A2, A4. Vì thế OFFSET trả về ba hàng đầu, A1:C3. Số lượng 6 ở hàng 3 vẫn được tính, còn 2 ở hàng 4 nằm ngoài phạm vi. Tổng trở lại 10.

A3 trống. OFFSET bao phủ A1:C3, tính 6 ở hàng 3 và bỏ 2 ở hàng 4, nên tổng là 10.
Viền xanh đánh dấu phạm vi A1:C3 do OFFSET trả về trong Excel trên Windows.

Một tiêu đề trống ở giữa cũng có thể khiến các cột cuối bị loại. COUNTA vẫn đếm ô có công thức trả về chuỗi rỗng (""). Với sổ làm việc có sẵn, hãy giữ tiêu đề và ID liền mạch, kiểm tra giới hạn đếm và các ô trống. Số lượng trống không đổi chiều cao nếu vẫn có ID. Tài liệu COUNTA của Microsoft

Khi tiêu đề bắt đầu ở hàng 2

Nếu hàng 1 dành cho tên báo cáo, bắt đầu danh sách tại A2. Đếm chiều cao và chiều rộng từ hàng 2 để tránh tính tên báo cáo. Dời mẫu xuống một hàng cho kết quả A2:C4, gồm tiêu đề và hai mặt hàng. Tổng vẫn là 10.

=SUM(OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000),COUNTA(Sheet1!$A$2:$Z$2)))

Dùng bảng tự mở rộng khi thêm hàng

Với danh sách mới, chọn vùng gồm cả tiêu đề rồi chọn Chèn → Bảng. Đặt tên bảng là Items. Công thức sau cộng cột số lượng từ một ô ngoài bảng. Phạm vi tính tổng mở rộng khi thêm hàng. Hướng dẫn tham chiếu có cấu trúc của Microsoft

=SUM(Items[数量])

Chỉ cộng các hàng có ID sản phẩm

Khi chuyển A1:C4 thành bảng Items, công thức trên cộng 4 + 6 + 2 và trả về 12 dù A3 trống. Để chỉ tính các hàng có ID, dùng SUMIF với điều kiện cột ID không trống. Mẫu cộng 4 + 2 ở hàng 2 và 4, cho kết quả 6. Tài liệu SUMIF của Microsoft

=SUMIF(Items[商品ID],"<>",Items[数量])

Đồng bộ lựa chọn thả xuống với bảng

Để chọn tên sản phẩm ở trang tính khác, mở Công thức → Trình quản lý tên trong Excel trên máy tính. Tạo tên ItemChoices ở phạm vi sổ làm việc, đặt Tham chiếu đến là =Items[商品名]. Với ô nhập liệu, chọn Danh sách trong Xác thực dữ liệu và đặt Nguồn là =ItemChoices. Tiêu đề không xuất hiện trong danh sách, còn tên ở cuối bảng được thêm làm lựa chọn mới. Hãy nhập tên cho mỗi hàng mới để tránh lựa chọn trống. Hướng dẫn tạo danh sách thả xuống của Microsoft