Tháng mới đổ thêm dữ liệu mà công thức với Pivot không phải sửa vùng
Đầu bài
Mỗi tháng dán thêm mấy nghìn dòng vào cuối bảng. Lần nào cũng phải mở từng công thức sửa $A$2:$A$5000, sửa vùng nguồn Pivot; quên một chỗ là báo cáo thiếu hẳn một tháng mà không báo lỗi.
Các bước làm
- 1
Đóng bảng nguồn thành Table và ĐẶT TÊN
Đứng trong bảng, Ctrl+T, rồi sửa ô Table Name thành tên đọc được như BanHang. Để tên mặc định Table1, Table2 thì vài tháng sau không ai nhớ bảng nào là bảng gì.
- 2
Viết công thức bằng tên cột thay cho địa chỉ
=SUMIFS(BanHang[Thành tiền], BanHang[Tháng], $A2, BanHang[Vùng], B$1) — thêm dòng vào Table thì vùng tự nới. Khoá $ ở đây chỉ dành cho ô điều kiện, không phải cho vùng.
- 3
Dán dữ liệu tháng mới đúng chỗ
Đứng ở ô đầu của dòng trống NGAY DƯỚI Table rồi dán, Table tự nuốt phần mới vào. Chừa một dòng trống rồi mới dán là Table không nhận, và đó là lỗi hay gặp nhất.
- 4
Trỏ nguồn Pivot vào tên Table
Lúc tạo Pivot thì gõ BanHang vào ô Table/Range thay vì quét vùng bằng chuột. Từ đó chỉ cần Alt+F5, hoặc Ctrl+Alt+F5 cho cả file, là Pivot có luôn tháng mới.
- 5
Cột tính toán viết một lần cho cả cột
Gõ công thức vào ô đầu của một cột trong Table rồi Enter — Table tự đổ xuống hết cột, và dòng dán thêm tháng sau cũng tự có công thức, khỏi Ctrl+D lại.
- 6
Để hai ô chốt chặn ở sheet báo cáo
=COUNTA(BanHang[Mã]) và =MAX(BanHang[Ngày]). Mở file ra liếc hai ô này là biết dữ liệu đang tới ngày nào, đủ bao nhiêu dòng — trước khi tin bất cứ con số nào phía dưới.
Phím dùng trong bài
Bẫy hay dính
Table nới theo dữ liệu NGUỒN, nhưng bảng BÁO CÁO trỏ vào Table thì vẫn phải kéo tay nếu kỳ mới sinh thêm dòng chỉ tiêu. Table lo đầu vào, không lo đầu ra — đóng Table xong không có nghĩa là hết phải nghĩ.
Cùng nhóm gộp dữ liệu từ nhiều nguồn
- Gộp nhiều file tháng thành bảng năm rồi đối chiếu không để lệchMỗi tháng một file cùng mẫu, cần bảng tổng cả năm VÀ chắc chắn không sót file, không nạp trùng, tổng khớp với số đã chốt từng tháng.
- Tổng hợp 12 sheet cùng mẫu trong một fileFile có sheet T1 đến T12 cùng bố cục, cần một sheet tổng cộng dồn ô B5 của cả 12 sheet mà không gõ 12 lần.
- Năm chi nhánh gửi năm kiểu cột, phải ghép thành một bảngCùng một biểu mẫu nhưng nơi để cột Ngày lên đầu, nơi để cuối, nơi chèn thêm cột ghi chú. Copy dán tay là lệch cột mà cả tháng sau mới phát hiện.
- Ghép bảng bán hàng với danh mục mà số dòng tự nhiên phình raGhép thêm cột Nhóm hàng, Vùng, Nhân viên phụ trách từ mấy bảng danh mục. Ghép xong doanh thu tự dưng cao hơn trước, không ai chỉ ra được sai ở đâu.