Bill đã hỏi câu hỏi của tuần này về dữ liệu Excel dư thừa.
Tôi xây dựng danh sách giao dịch hàng tháng trong Excel. Cuối tháng, tôi cần loại bỏ các dữ liệu thừa và đưa ra tổng số theo mã tài khoản. Mỗi mã tài khoản có thể xảy ra nhiều lần. Bill sau đó mô tả phương pháp Excel hiện tại của mình tương tự như phương pháp 1 bên dưới để đưa ra danh sách mã tài khoản duy nhất, với kế hoạch sử dụng ma trận công thức CSE để lấy tổng. Anh ấy hỏi, có cách nào dễ dàng hơn để có được danh sách mã tài khoản duy nhất với tổng số cho mỗi tài khoản không?
Đây là một câu hỏi kỳ nghỉ hoàn hảo. Là một người dùng Lotus trong 15 năm, tôi nhận ra phương pháp của Bill là phương pháp cổ điển để thao tác dữ liệu "nhanh chóng và bẩn thỉu" từ những ngày xưa tốt đẹp của bản phát hành Lotus 2.1. Đây là một mùa để đếm các phước lành của chúng tôi. Khi nghĩ về câu hỏi này, bạn nhận ra rằng những người ở Microsoft đã thực sự ban tặng cho chúng tôi rất nhiều công cụ trong những năm qua. Nếu bạn đang sử dụng Excel 97, có ít nhất năm phương pháp để thực hiện tác vụ này, tất cả đều dễ dàng hơn nhiều so với phương pháp cổ điển được Bill mô tả. Tôi sẽ cung cấp một hướng dẫn về năm phương pháp trong tuần này.
Tập dữ liệu đơn giản của tôi có số tài khoản trong cột A và số tiền trong cột B. Dữ liệu chạy từ A2: B100. Nó không được sắp xếp ngay từ đầu.
Phương pháp 1
Sử dụng câu lệnh If sáng tạo kết hợp với Dán Giá trị Đặc biệt để tìm câu trả lời.
Với các công cụ mới hơn được cung cấp bởi Excel, tôi không còn khuyến nghị phương pháp này nữa. Tôi đã từng sử dụng điều này rất nhiều trước khi những thứ tốt hơn xuất hiện và vẫn có những trường hợp nó trở nên hữu ích. Tên thay thế của tôi cho phương pháp này là "The-Lotus-123-When-You-Were-Not-In-The-Mood-To-Use- @ DSUM". Đây là các bước.
- Sắp xếp dữ liệu theo cột A.
- Phát minh ra một công thức trong cột C sẽ giữ tổng số chạy theo tài khoản. Ô C2 là
=IF(A2=A1,C1+B2,B2). - Phát minh ra một công thức trong D sẽ xác định mục nhập cuối cùng cho một tài khoản cụ thể. Ô D2 là
=IF(A2=A3,FALSE,TRUE). - Sao chép C2: D2 xuống tất cả các hàng của bạn.
- Sao chép C2: D100. Thực hiện Chỉnh sửa - Dán Đặc biệt - Giá trị trở lại C2: D100 để thay đổi công thức thành giá trị.
- Sắp xếp theo cột D giảm dần.
- Đối với các hàng có TRUE trong cột D, bạn có một danh sách duy nhất các số tài khoản trong A và tổng số tài khoản đang chạy cuối cùng trong C.
Ưu điểm: Nó là nhanh chóng. Tất cả những gì bạn cần là một ý thức nhạy bén trong việc viết các câu lệnh IF.
Nhược điểm: Có nhiều cách tốt hơn.
Phương pháp 2
Sử dụng Bộ lọc Dữ liệu - Bộ lọc Nâng cao để lấy danh sách các tài khoản duy nhất.
Câu hỏi của Bill thực sự là làm thế nào để có được danh sách số tài khoản duy nhất để anh ta có thể sử dụng Công thức CSE để lấy tổng số. Đây là một phương pháp để lấy danh sách các số tài khoản duy nhất.
- Đánh dấu A1: A100
- Từ Menu, chọn Dữ liệu, Bộ lọc, Bộ lọc Nâng cao
- Nhấp vào nút radio để "Sao chép sang vị trí khác".
- Nhấp vào hộp kiểm cho "Chỉ Bản ghi Duy nhất".
- Chọn một phần trống của trang tính nơi bạn muốn danh sách duy nhất xuất hiện. Nhập thông tin này vào trường "Sao chép vào:". (Lưu ý rằng trường này chuyển sang màu xám cho đến khi bạn chọn "Sao chép sang vị trí khác".
- Bấm OK. Các số tài khoản duy nhất sẽ xuất hiện trong F1.
- Nhập bất kỳ thao tác tuyến dưới, công thức mảng, v.v. để nhận kết quả của bạn.
Ưu điểm: Nhanh hơn Phương pháp 1. Không cần phân loại.
Nhược điểm: Các công thức CSE được yêu cầu sau đó sẽ khiến bạn quay cuồng.
Phương pháp 3
Sử dụng Hợp nhất Dữ liệu.
Chất lượng cuộc sống của tôi được cải thiện khi Excel cung cấp Hợp nhất dữ liệu. Điều này thật LỚN! Phải mất 30 giây để thiết lập nó, nhưng nó đã đánh vần cái chết đối với DSUM và các phương thức khác. Số tài khoản của bạn phải ở bên trái của các trường số mà bạn muốn tổng cộng. Bạn phải có tiêu đề phía trên mỗi cột. Bạn cần chỉ định tên phạm vi cho khối ô hình chữ nhật bao gồm số tài khoản dọc theo cột bên trái và tiêu đề ở trên cùng. Trong trường hợp này, phạm vi đó là A1: B100.
- Đánh dấu A1: B100
- Gán tên phạm vi cho khu vực này bằng cách nhấp vào hộp tên (ở bên trái thanh công thức) và nhập tên chẳng hạn như "TotalMe". (Hoặc, sử dụng Chèn - Tên).
- Đặt con trỏ ô vào phần trống của trang tính.
- Chọn dữ liệu - Hợp nhất
- Trong trường tham chiếu, nhập tên phạm vi (TotalMe).
- Trong phần Sử dụng nhãn Trong, hãy kiểm tra cả Hàng trên cùng và Cột Bên trái.
- Bấm OK
Ưu điểm: Đây là phương pháp yêu thích của tôi. Không cần phân loại. Phím tắt là alt-D N (tên dải ô) alt-T alt-L enter. Nó có thể dễ dàng mở rộng. Nếu phạm vi của bạn bao gồm 12 cột hàng tháng, câu trả lời sẽ có tổng số cho mỗi tháng.
Nhược điểm: Nếu bạn thực hiện một Hợp nhất dữ liệu khác trên cùng một trang tính, bạn cần xóa tên phạm vi cũ ra khỏi trường Tất cả tham chiếu bằng cách sử dụng nút Xóa. Số tài khoản phải ở bên trái dữ liệu số của bạn. Nó hơi chậm hơn so với bảng tổng hợp, điều này trở nên đáng chú ý đối với bộ dữ liệu có hơn 10.000 bản ghi.
Phương pháp 4
Sử dụng Tổng phụ dữ liệu.
Đây là một tính năng thú vị. Bởi vì dữ liệu kết quả là lạ để làm việc với, tôi ít sử dụng nó hơn Dữ liệu hợp nhất.
- Sắp xếp theo cột A tăng dần.
- Chọn bất kỳ ô nào trong phạm vi dữ liệu.
- Chọn Dữ liệu - Tổng phụ từ menu.
- Theo mặc định, Excel đề nghị tính tổng phụ của cột cuối cùng trong dữ liệu của bạn. Điều này hoạt động trong ví dụ này, nhưng bạn thường phải cuộn qua danh sách "Thêm tổng vào:" để chọn các trường chính xác.
- Bấm OK. Excel sẽ chèn một hàng mới vào mỗi lần thay đổi số tài khoản với tổng số.
Sau khi có tổng phụ, bạn sẽ thấy 123 nhỏ xuất hiện bên dưới hộp tên. Nhấp vào 2 để xem chỉ một dòng cho mỗi tài khoản với tổng số. Đọc Sao chép Tổng phụ Excel để biết giải thích về các bước đặc biệt cần thiết để sao chép chúng vào vị trí mới. Bấm vào 3 để xem tất cả các dòng. Ưu điểm: Tính năng mát mẻ. Tuyệt vời để in các báo cáo với tổng số và ngắt trang sau mỗi phần.
Nhược điểm: Dữ liệu phải được sắp xếp trước. Chậm đối với nhiều dữ liệu. Bạn phải sử dụng Goto-Special-VisbileCellsOnly để lấy tổng ở những nơi khác. Bạn phải sử dụng Data-Subtotals-RemoveAll để lấy lại dữ liệu ban đầu của mình.
Phương pháp 5
Sử dụng Bảng tổng hợp.
Bảng tổng hợp là bảng linh hoạt nhất. Dữ liệu của bạn không cần phải được sắp xếp. Các cột số có thể ở bên trái hoặc bên phải số tài khoản. Bạn có thể dễ dàng đưa số tài khoản xuống hoặc trên toàn trang.
- Chọn bất kỳ ô nào trong phạm vi dữ liệu.
- Chọn Dữ liệu - PivotTable từ menu.
- Chấp nhận các giá trị mặc định ở Bước 1
- Đảm bảo rằng phạm vi dữ liệu trong bước 2 là chính xác (thường là như vậy)
- Nếu bạn đang sử dụng Excel 2000, hãy nhấp vào nút Bố cục ở bước 3. Người dùng Excel 95 & 97 sẽ tự động chuyển đến bố cục như bước 3.
- Trong hộp thoại bố cục, hãy kéo nút Tài khoản từ phía bên phải của hộp thoại và thả nó vào vùng Hàng.
- Kéo nút Số lượng từ phía bên phải của hộp thoại và thả nó vào vùng Dữ liệu.
- Người dùng Excel 2000 bấm OK, người dùng Excel 95/97 bấm Tiếp theo.
- Chỉ định xem bạn muốn kết quả trong trang tính mới hay trong một phần cụ thể của trang tính hiện có. Đọc thêm về bảng tổng hợp trong Excel Thủ thuật nâng cao bảng tổng hợp.
- Bảng tổng hợp cung cấp chức năng đáng kinh ngạc và thực hiện nhiệm vụ này nhanh chóng. Để sao chép kết quả bảng tổng hợp, bạn cần thực hiện Chỉnh sửa-Dán Đặc biệt-Giá trị, nếu không Excel sẽ không cho phép bạn chèn hàng, v.v.
Ưu điểm: Nhanh chóng, linh hoạt, mạnh mẽ. Nhanh chóng, ngay cả đối với nhiều dữ liệu.
Nhược điểm: Hơi đáng sợ.
Bill hiện có bốn phương pháp mới để loại bỏ dữ liệu dư thừa. Mặc dù những phương pháp này không có sẵn kể từ đầu, cả Lotus và Excel đều là những nhà cải tiến tuyệt vời để mang lại cho chúng ta những cách nhanh hơn để hoàn thành công việc đơn giản này.
