Quản Lý Chi Phí Bằng Excel - Học Excel Siêu Nhanh

Bạn cũng có thể tải file thực hành tại đây.

Ví dụ như ở đây mình có 4 bảng dữ liệu được đặt trong 4 Sheet khác nhau. Bây giờ mình sẽ cùng các bạn đi tổng hợp chúng lại và quản lý chúng. 

Tạo Table

Đầu tiên các bạn sẽ tiến hành tạo Table cho các vùng dữ liệu cần tổng hợp bằng cách bôi đen vùng dữ liệu sau đó vào thẻ Insert rồi chọn Table, các bạn nhớ tích vào My table has headers tiếp đến vào mục Table Name để đặt tên rồi bấm Enter là được.

Các bạn làm tương tự với những bảng còn lại.

Gộp dữ liệu

Bước tiếp theo mình sẽ gộp toàn bộ 4 loại chi phí này vào 1 Sheet duy nhất bằng cách vào thẻ Data. Chọn Get data tiếp đến From Other Sources rồi chọn  Blank Query thì nó hiện chương trình Power Query cho các bạn.

Sau đó trên thanh Text các bạn nhập công thức = Excel.CurrentWorkbook() rồi nhấn Enter. Đó là lệnh để kết hợp tất cả dữ liệu của file này 1 Sheet mới. Sau khi các bạn Enter nó sẽ hiện lên tất của các Table có trong file.

Các bạn có thể bỏ cột Name đi bằng cách nhấn chuột phải vào Name rồi chọn Remove.

Sau đó trong cột Content các bạn chọn biểu tượng mở rộng sau đó ấn bỏ chọn Use original column rồi chọn Ok. Việc này giúp các bạn không phải chèn thêm các ký tự cho tiêu đề.

Như vậy là tất cả dữ liệu đã được gộp lại. Các bạn có thể đổi lại định dạng ngày bằng cách vào biểu tượng ABC 123 rồi chọn kiểu Date.

Các bạn có thể chỉnh sửa dữ liệu trực tiếp trong bảng đó. Sau khi đã làm xong mọi thứ các bạn chọn Close & Load nhé.

Vậy là tất cả các dữ liệu trong File đã được cập nhật vào 1 Sheet mới có tên là Query1 hoặc Sheet1. Các bạn có thể đổi tên bằng cách ấn vô Query1 bên cửa sổ Queries rồi vào mục Name để đổi tên rồi chọn Close & Load.

Sau khi các bạn đã có bảng dữ liệu tổng hợp mới mà các bạn muốn thay đổi dữ liệu ở bảng gốc thì các bạn sẽ tiến hành thay đổi đến Sheet tổng hợp nhấn phải chuột chọn Refresh.

Các bạn có thể thấy số hàng đã tăng nên do khi các bạn tạo mới lại dữ liệu nó sẽ cập nhật lại thêm bảng mới xuống bên dưới mà không xóa bảng cũ đi. Để xóa bảng cũ đi các bạn làm như sau. Các bạn quay lại cửa sổ Power Query rồi chọn phần Source tiếp đó vào phần Name chọn Text Filters rồi chọn Does not equal cuối cùng chọn Insert.

Trong mục Does not equal các bạn nhập vào tên các bạn đặt cho bảng tổng hợp rồi chọn Ok. Đó là lệnh để xóa cái Table đó đi mỗi khi nó Refresh lại.

Rồi sau đó các bạn Close & Load lại là nó sẽ tự động cập nhật lại bảng dữ liệu tổng hợp mới.

Dữ liệu dạng Pivot table

Ngoài biểu diễn dưới dạng trên các bạn có thể biểu diễn dưới dạng Pivot table bằng cách nhấn phải chuột vào bảng mới tạo trong cửa sổ Queries rồi chọn Load to. Tiếp đó chọn Pivot table report rồi chọn Ok.

Thì nó đã thay phần dữ liệu cũ thành dạng Pivot table. Các bạn sẽ biểu diễn dữ liệu bằng cách kéo những tiêu chí các bạn muốn biểu diễn xuống 2 ô Rows và Values thì nó sẽ tự cập nhật bảng Pivot cho các bạn. Ví dụ bài của mình sẽ lấy Loại chi phí và Số tiền. Mình sẽ tạo thêm 1 cái Sheet nữa để lát nữa mình để những biểu đồ vào.

Biểu đồ tròn

Sau đó các bạn có thể chọn biểu đồ biểu diễn bằng cách ấn vào vùng dữ liệu trước. Sau đó vào thẻ PivotTable Analyze tiếp đó chọn Pivot Chart. Mình sẽ hướng dẫn biểu đồ vòng tròn trước nên các bạn chọn Pie rồi ấn Ok.

Sau đó các bạn có thể dấu hoặc xóa các chi tiết không cần đến trong đồ thị đi rồi nhấn phải chuột vào đồ thị chọn Add data Lables.

Sau đó các bạn tích vào các giá trị mới hiện lên trong đồ thị rồi nhấn phải chuột chọn Format Data Lables.

Các bạn có thể tích vô Category Name để hiện tên nội dung và Percentage để hiện phần trăm. Ngoài ra các bạn có thể chọn vị trí đặt số liệu trong mục Lable Position.

Tiếp theo chúng ta sẽ chỉnh khung biểu đồ bằng cách nhấn phải chuột vào biểu đồ chọn Format Chart Area.

Sau đó các bạn vào phần Border chọn Solid line để hiện khung rồi vào Color chọn màu khung rồi tới Width chọn độ rộng của khung và có thể chọn Rounded corners để bo viền lại.

Biểu đồ cột

Mình sẽ Cut cái đồ thị đó sang Sheet Đồ Thị mình mới tạo để thực hành tiếp. Các bạn phải Copy cái table cũ rồi Paste ra thì mới tạo biểu đồ mới được nha. Sau đó các bạn nhấn chọn vào table mới paste rồi vào thẻ Analyze chọn Field List.

Ở ví dụ này mình sẽ lấy theo ngày nên mình kéo Ngày xuống Rows thay thế cho Loại Chi Phí.

Sau đó các bạn chọn Pivot Chart rồi chọn biểu đồ cột rồi bấm OK.

Các bạn cũng xóa và dấu những chi tiết không cần thiết bằng cách nhấn phải chuột chọn Delete hoặc Hide. Sau đó các bạn nhấn vào biểu tượng dấu cộng chọn Data Labels thì nó sẽ đẹp hơn và tự nhiên hơn.

Sau đó các bạn cũng nháy phải chuột vào biểu đồ chọn Format Chart Area để chỉnh khung như trên.

Rồi mình sẽ nháy phải chuột vào biểu đồ chọn Cut rồi Paste sang Sheet Biểu Đồ như biểu đồ tròn để thực hành tiếp.

Biểu đồ xếp hạng

Bây giờ mình sẽ hướng dẫn các bạn cách lấy Top của 3 chi phí cao nhất. Các bạn cũng Copy rồi Paste table ra để lấy dữ liệu mới thực hành nha. Các bạn sẽ tích vào table mới vào thẻ Analyze chọn Field List.

Rồi các bạn sẽ bỏ phần Ngày đi rồi kéo phần Chi tiết vô.

Sau đó các bạn tick vô biểu tượng filter của Row rồi chọn Value filter rồi chọn Top 10 rồi các bạn có thể sửa số 10 thành số thứ tự các bạn muốn, mình lấy top 3 nên chọn 3, bôi đen là lấy theo tiêu chí đó rồi chọn Ok.

Các bạn có thể kéo thêm Loại Chi Phí vào hàm Row để làm rõ hơn dữ liệu. Bây giờ các bạn vào thẻ Design chọn Report layout tiếp đó chọn Show in Tabular. Rồi vào Subtotals chọn Do not Show để thiết kế lại table cho đẹp.

Nếu các bạn không thích Grand Total thì các bạn có thể vào Grand Total chọn OFF nó đi.

Bây giờ chúng ta sẽ vẽ biểu đồ Top cho table mới đó bằng cách chọn vào table rồi chọn Pivot Chart rồi chọn Bar rồi OK.

Tương tự thì các bạn cũng xóa và dấu những cái thừa đi. Rồi các bạn cũng căn chỉnh cũng như chỉnh khung tương tự như đồ thị vòng tròn trên kia.

Tạo Slicer

Bây giờ chúng ta sẽ cùng nhau tạo Slicer. Các bạn nhẽ nhấn chọn vào đồ thị sau đó vào thẻ Analyze chọn Insert Slicer sau đó các bạn sẽ chọn tùy theo những cái bạn muốn chọn ra.

Các bạn có thể sửa Slicer bằng cách vào thẻ Slicer chọn Slicer setting để chỉnh sửa.

Các bạn có thể chỉnh số cột trong Slicer bằng cách vào Columns và áp dụng Slicer đó cho tất cả các biểu đồ bằng cách nhấn phải chuột chọn Report Connections rồi tích chọn vào hết tất cả các Pivottable trong bảng rồi chọn OK.

Trong đồ thị thì thứ tự thì nó sẽ để là tăng dần, các bạn có thể đổi về giảm dần bằng cách nhấn chuột phải vào phần chi phí của đồ thị sau đó chọn Format Axis rồi chọn Categories in reverse orded.

Khi các bạn muốn thêm dữ liệu vào dữ liệu gốc thì các bạn cứ thêm bình thường sau đó vào bảng Sliver nhấn phải chuột chọn Refresh thì dữ liệu sẽ được tự động cập nhật lại.

Giả sử bây giờ các bạn muốn thêm 1 table nữa vào bảng tổng hợp cũng như biểu đồ thì các bạn sẽ nhấn phải chuột vào 1 bảng bất kỳ chọn Move or Copy.

Sau đó các bạn chọn Create a copy và chọn move to end.

Rồi các bạn chỉ cần sang Sheet tổng hợp Refresh lại là nó cũng sẽ tự động cập nhật cho các bạn.

Chúc các bạn thành công.

Video hướng dẫn