Nội dung
Phương pháp 1. Công thức
Hãy bắt đầu khởi động với lựa chọn đơn giản nhất – công thức. Nếu chúng ta có một bảng nhỏ được sắp xếp theo ngày làm đầu vào thì để tính tổng số chạy trong một cột riêng biệt, chúng ta cần một công thức cơ bản:
Tính năng chính ở đây là việc sửa phạm vi phức tạp bên trong hàm SUM – tham chiếu đến phần đầu của phạm vi được đặt ở mức tuyệt đối (với ký hiệu đô la) và đến phần cuối – tương đối (không có đô la). Theo đó, khi sao chép công thức xuống toàn bộ cột, chúng ta sẽ nhận được một phạm vi mở rộng, tổng của chúng sẽ được tính toán.
Những nhược điểm của phương pháp này là rõ ràng:
- Bảng phải được sắp xếp theo ngày.
- Khi thêm hàng mới có dữ liệu, công thức sẽ phải được mở rộng theo cách thủ công.
Phương pháp 2. Bảng tổng hợp
Phương pháp này phức tạp hơn một chút, nhưng dễ chịu hơn nhiều. Và để làm trầm trọng thêm, hãy xem xét một vấn đề nghiêm trọng hơn – một bảng gồm 2000 hàng dữ liệu, trong đó không có sự sắp xếp theo cột ngày tháng, nhưng có sự lặp lại (tức là chúng ta có thể bán nhiều lần trong cùng một ngày):
Chúng tôi chuyển đổi bảng ban đầu của mình thành phím tắt “thông minh” (động) Ctrl+T hoặc đội Trang chủ - Định dạng dưới dạng bảng (Trang chủ - Định dạng dưới dạng Bảng), sau đó chúng ta xây dựng một bảng tổng hợp trên đó bằng lệnh Chèn - PivotTable (Chèn - Bảng tổng hợp). Chúng tôi đặt ngày trong khu vực hàng trong bản tóm tắt và số lượng hàng hóa đã bán trong khu vực giá trị:
Xin lưu ý rằng nếu bạn có phiên bản Excel không quá cũ thì ngày sẽ tự động được nhóm theo năm, quý và tháng. Nếu bạn cần một nhóm khác (hoặc hoàn toàn không cần đến nó), thì bạn có thể sửa nó bằng cách nhấp chuột phải vào bất kỳ ngày nào và chọn lệnh Nhóm / Tách nhóm (Nhóm / Tách nhóm).
Nếu bạn muốn xem cả tổng kết quả theo từng giai đoạn và tổng hiện có trong một cột riêng biệt thì việc đưa trường vào vùng giá trị là điều hợp lý. Bán một lần nữa để lấy một bản sao của trường – trong đó chúng ta sẽ bật hiển thị tổng số đang chạy. Để thực hiện việc này, nhấp chuột phải vào trường và chọn lệnh Các phép tính bổ sung – Tổng tích lũy (Hiển thị các giá trị dưới dạng - Tổng số đang chạy):
Ở đó, bạn cũng có thể chọn tùy chọn tăng tổng số theo phần trăm và trong cửa sổ tiếp theo, bạn cần chọn trường sẽ tích lũy – trong trường hợp của chúng tôi, đây là trường ngày:
Ưu điểm của phương pháp này:
- Một lượng lớn dữ liệu được đọc nhanh chóng.
- Không cần phải nhập công thức bằng tay.
- Khi thay đổi dữ liệu nguồn, chỉ cần cập nhật tóm tắt bằng nút chuột phải hoặc bằng lệnh Dữ liệu – Làm mới tất cả là đủ.
Nhược điểm xuất phát từ thực tế đây là bản tóm tắt, có nghĩa là bạn không thể làm bất cứ điều gì bạn muốn trong đó (chèn dòng, viết công thức, xây dựng bất kỳ sơ đồ nào, v.v.) sẽ không còn hiệu quả.
Phương pháp 3: Truy vấn nguồn
Hãy tải bảng “thông minh” của chúng ta với dữ liệu nguồn vào trình soạn thảo truy vấn Power Query bằng lệnh Dữ liệu - Từ Bảng / Phạm vi (Dữ liệu - Từ Bảng / Phạm vi). Nhân tiện, trong các phiên bản Excel mới nhất, nó đã được đổi tên – bây giờ nó được gọi là Với những chiếc lá (Từ Trang tính):
Sau đó chúng ta sẽ thực hiện các bước sau:
1. Sắp xếp bảng theo thứ tự tăng dần theo cột ngày tháng bằng lệnh Sắp xếp tăng dần trong danh sách thả xuống bộ lọc trong tiêu đề bảng.
2. Một lát sau, để tính tổng chạy, chúng ta cần một cột phụ có số hàng thứ tự. Hãy thêm nó bằng lệnh Thêm cột - Cột chỉ mục - Từ 1 (Thêm cột - Cột chỉ mục - Từ 1).
3. Ngoài ra, để tính tổng chạy, chúng ta cần tham chiếu đến cột Bán, nơi chứa dữ liệu tóm tắt của chúng tôi. Trong Power Query, các cột còn được gọi là danh sách (danh sách) và để lấy liên kết đến nó, hãy bấm chuột phải vào tiêu đề cột rồi chọn lệnh Chi tiết (Hiển thị chi tiết). Biểu thức chúng ta cần sẽ xuất hiện trên thanh công thức, bao gồm tên của bước trước đó # ”Đã thêm chỉ mục”, từ đó chúng tôi lấy bảng và tên cột [Việc bán hàng] từ bảng này trong ngoặc vuông:
Sao chép biểu thức này vào clipboard để sử dụng tiếp.
4. Xóa bước cuối cùng không cần thiết Bán và thay vào đó thêm một cột được tính toán để tính tổng chạy bằng lệnh Thêm một cột - Cột tùy chỉnh (Thêm cột - Cột tùy chỉnh). Công thức chúng ta cần sẽ trông như thế này:
Đây là chức năng Danh sách.Range lấy danh sách ban đầu (cột [Việc bán hàng]) và trích xuất các phần tử từ nó, bắt đầu từ phần tử đầu tiên (trong công thức, giá trị này là 0, vì việc đánh số trong Power Query bắt đầu từ XNUMX). Số phần tử cần lấy chính là số hàng ta lấy từ cột [Mục lục]. Vì vậy, hàm này cho hàng đầu tiên chỉ trả về một ô đầu tiên của cột Bán. Đối với dòng thứ hai – đã là hai ô đầu tiên, đối với dòng thứ ba – ba ô đầu tiên, v.v.
Vâng, sau đó chức năng Danh sách.Sum tính tổng các giá trị được trích xuất và ở mỗi hàng chúng ta nhận được tổng của tất cả các phần tử trước đó, tức là tổng tích lũy:
Việc còn lại là xóa cột Chỉ mục mà chúng ta không còn cần nữa và tải kết quả trở lại Excel bằng lệnh Trang chủ – Đóng & Tải để.
Vấn đề đã được giải quyết.
Nhanh và nguy hiểm
Về nguyên tắc, điều này có thể đã được dừng lại, nhưng có một con ruồi nhỏ trong thuốc mỡ – yêu cầu mà chúng tôi tạo ra hoạt động với tốc độ của một con rùa. Ví dụ: trên chiếc PC không phải yếu nhất của tôi, một bảng chỉ có 2000 hàng được xử lý trong 17 giây. Nếu có nhiều dữ liệu hơn thì sao?
Để tăng tốc, bạn có thể sử dụng tính năng đệm bằng hàm List.Buffer đặc biệt, chức năng này tải danh sách (danh sách) được cung cấp cho nó dưới dạng đối số vào RAM, giúp tăng tốc đáng kể khả năng truy cập vào nó trong tương lai. Trong trường hợp của chúng tôi, việc đệm danh sách #”Chỉ mục đã thêm” [Đã bán] mà Power Query phải truy cập khi tính toán tổng số đang chạy trong mỗi hàng của bảng 2000 hàng của chúng tôi là điều hợp lý.
Để thực hiện việc này, trong trình soạn thảo Power Query trên tab Chính, hãy bấm vào nút Trình chỉnh sửa nâng cao (Trang chủ – Trình chỉnh sửa nâng cao) để mở mã nguồn truy vấn của chúng tôi bằng ngôn ngữ M được tích hợp trong Power Query:
Và sau đó thêm một dòng với một biến ở đó Danh sách của tôi, giá trị của nó được hàm đệm trả về và ở bước tiếp theo, chúng ta thay thế lệnh gọi danh sách bằng biến này:
Sau khi thực hiện những thay đổi này, truy vấn của chúng tôi sẽ nhanh hơn đáng kể và sẽ xử lý bảng 2000 hàng chỉ trong 0.3 giây!
Một điều khác, phải không? 🙂
- Biểu đồ Pareto (80/20) và cách xây dựng nó trong Excel
- Tìm kiếm từ khóa trong văn bản và đệm truy vấn trong Power Query