Phân tích dữ liệu sau 30 phút với Excel

Chào các bạn, trong group trước đây mình đã post rất nhiều bài lý thuyết chia sẻ về tổng quan nghành nghề phân tích dữ liệu, quy trình phân tích dữ liệu rồi các lý thuyết vĩ mô,…. 😀 nghe rất kinh khủng phải không ạ. Hôm nay mình sẽ demo cho các bạn bài hướng dẫn phân tích dữ liệu với Excel sau 30 phút, đây cũng là yêu cầu của bạn Trần Văn Cường

Nội dung yêu cầu: Nếu có thể thì em mong muốn được xem các bài viết về: Các bước triển khai cụ thể từ xác định yêu cầu phân tích—> lựa chọn dữ liệu đầu vào—> làm sạch, xử lý dữ liệu—> mô hình hoá, tạo dashboard —> đưa ra các insight hay data storytelling ạ.

• Data này mình lấy dữ liệu từ nguồn dữ liệu chia sẻ của Sở KH&ĐT TPHCM về quản lý dự án đầu tư công.

Đây là hình dữ liệu gốc

Mình sẽ sửa lại thông tin trường(tiêu đề cột) thành bản tiếng việt cho dễ đọc ạ.

Theo bài về quy trình phân tích dữ liệu hôm trước ta có 5 bước 

Bước 1: Xác định yêu cầu phân tích

• Mình giả sử yêu cầu phân tích dữ liệu ban đầu là mình sẽ phân tích tỷ trọng vốn đầu tư liên doanh so với vốn đầu tư nước ngoài, từ đó có được kết quả phân tích và ra được đề xuất phù hợp. Điều này thì tùy thuộc vào mục đích của từng bạn và từng doanh nghiệp

Bước 2: Khám phá dữ liệu – Data Exploretation

• Ở bước này, nếu chúng ta dùng excel theo cách thủ công, thông thường chúng ta sẽ dùng các hàm Count để đếm số dự án, Hàm Sum để đếm tổng số tiền vốn sau đó vẽ biểu đồ hoặc các hàm counif, sumif, các hàm như hàm lenth, left,…. để tách và lấy các giá trị trong chuỗi mà chúng ta cần phân tích.
•  Nếu bạn nào biết qua Pivot table thì việc tổng hợp và khám dữ liệu sẽ nhanh gọn và chính xác hơn đến 90% so với cách làm báo cáo theo kiểu thủ công bằng hàm.

Ta đặt trỏ chuột vào ô A1  rồi chọn Insert -> Chọn Pivot Table phần Table Range (là vùng cần tổng hợp), nếu chọn dữ liệu hiển thị trong sheet mới phần Choose Where you want,…. Ở phần này do dữ liệu trên bảng hiện tại đã hiển thị chiếm chọn chỗ nên ta sẽ chọn New Worik Sheet (Tạo bảng tính mới) Sau đó ấn Ok.

sau khi ấn ok, Pivot Table sẽ tạo ra 1 sheet. Ở góc phải chỗ vùng PivotTable Fields (Trường tổng hợp) Ta sẽ khám phá các dữ liệu muốn phân tích như mục tiêu.

• Phía dưới là cột (trường dữ liệu để đưa vào tổng hợp).
• Vùng fillter là các giá trị cần lọc.
• Vùng Collumn là cột cần tổng hợp.
• Vùng Rows là hàng cần tổng hợp.
• Vùng Vallues là giá trị cần tổng hợp.

Theo yêu cầu ở bước 1: Xác định tỷ trọng vốn đầu tư liên doanh so với vốn đầu tư trong nước.

Ta sẽ chọn ở vùng Rows ta kéo chọn ngày, còn vùng Values ta sẽ chọn Tên dự án đầu tư, Tổng vốn đầu tư VNĐ và tổng vốn đầu tư USD ta được hình như dưới đây:

Nhìn sau cách làm này mình đã thấy được sự lợi hại của Pivot Table rồi phải ko ạ, Nhìn vào biểu đồ này ta có thể làm ngay 1 biểu đồ khám phá rồi đó, giả sử mình vẽ biểu đồ như hình dưới:

Ta thấy ngay là dữ liệu biểu đồ đọc rất khó, vì giá trị vốn đầu tư VNĐ và USD chênh lệch nhau rất nhiều – muốn hiển thị điều này ta có thể quy đổi giá trị USD sang VNĐ. Tuy nhiên ta thấy, nếu sử dụng dữ liệu này để làm biểu đồ và phân tích thì chưa được. Do đó ta phải có công đoạn làm sạch dữ liệu và chúng ta có thể cập nhật chi tiết lại bước 1 phần yêu cầu nghiệp vụ thành như sau: 

• Phân tích tỷ trọng đầu tư qua các năm về tổng số vốn đầu tư trong nước, nước ngoài qua các năm.
• Phân tích loại hình đầu tư .
• Phân tích tiến độ thực hiện của dự án.
• Phân tích quy mô dự án.
• Phân tích tỷ trọng đơn vị cấp phép.

Bước 3: Làm sạch dữ liệu – ETL

• Vấn đề: Nhìn vào dữ liệu gốc ban đầu ta thấy, các giá trị tại các trường thông tin vẫn đang rất khó để phân tích và làm báo cáo, Ví dụ ở cột ngày có thêm giá trị (13-12-2019  12:00:00 AM), các cột mục tiêu dự án, các ưu đãi bỏ cần phải loại bỏ (Do là trường text rất dài, nếu cần làm bài toán phân tích về text mình cần phải làm sâu hơn theo yêu cầu phân tích). Ở cột tiến độ mình cần phân tích theo số nhưng lại chứa cả text điều này cần phải tách giá trị trong cột ạ.
• Giải pháp: Nếu làm theo phương pháp cũ của excel ta sẽ tiến hành xóa bỏ 2 cột Mục tiêu dự án và Các ưu đãi sau đó tiến hành tách giá trị trong cột ngày và cột tiến độ thực hiện bằng các hàm len và left, right,… tuy nhiên ta sẽ khám phá công cụ ETL rất mạnh hiện nay là Power Query (Công cụ có sẵn tích hợp trên excel 2016).

Đầu tiên ta quay lại sheet ban đầu DS_vốn_ĐT_nn -> Chọn Data – Chọn From Table – sau đó chọn ok,

Sau khi ấn ok giao diện màm hình power query sẽ hiển thị ra bảng thao tác dữ liệu trên power query

Đầu tiên là làm sạch dữ liệu:

• Loại bỏ cột: Chọn vào cột Mục tiêu dự án sau đó ấn Remove, tiếp tục chọn cột Các ưu đãi Rồi ấn Remove
• Bước thứ 2: Ta thấy cột ngày chứng nhận đầu tư ở đây chưa phải là định dạng ngày và có thêm giá trị giờ phía sau nữa, nên ta phải tiến hành phân tách giá trị để loại bỏ giờ đi ta dùng chức năng split trong power query -> Chọn By Dilemeter, ở phần chọn thông số chia tác sẽ là space (tách bởi khoảng trống) ta được như hình dưới đây:

• Sau đó ta tiến hành xóa nốt cột giờ sau khi đã tách ra bằng Remove Column và cột text AM sau khi bị tách ra. 
• Tiếp theo ở cột quy mô dự án như ban đầu ta có định phân tích nhưng như ở đây cột này là các giá trị text kết hợp với số và nhiều dòng nên ta sẽ không phân tích phần này (Do thông tin bị khác nhau về đại lượng tính toán), vậy ở cột này ta tiến hành xóa bỏ.

Phần này nằm trong bước 4 – Data Validation của quy trình phân tích dữ liệu (kiểm tra tính hợp lệ của dữ liệu sau đó quay trở lại quá trình xử lý dữ liệu).

• Tiếp tục ở cột tiến độ thực hiện có dấu | ở giữa số và ngày nên ta sẽ thực hiện phân tách by custom chọn dấu | để ra được dữ liệu chuẩn sau đó xóa bỏ cột dư thừa ở tiến độ thực hiện hình như dưới đây:

Tiếp tục: Ta cần quy đổi tỷ giá từ USD sang VND để lúc làm biểu đồ ta thấy cân bằng hơn -> chọn phần Add column sau đó chọn Custom Column (Phần này mình chọn cột tổng vốn đầu tư USD rồi * 23500. Tuy nhiên do đại lượng và tỷ giá có thể thay đổi nên phải sử dụng các bước lập trình hàm theo năm hoặc theo dự án để làm chính xác hơn nhé.

Như vậy sau quá trình ETL chúng ta đã có được bộ dữ liệu cần phân tích:

Thứ 2: Load dữ liệu vào kho làm báo cáo: Ta chọn Close Load To

Sau đó chọn Table và chọn New Work Sheet , Quay trở lại bảng tính ta thấy Power Query đã tạo giúp 1 sheet thông tin mới để làm báo cáo rồi.

Bước 5: Làm báo cáo phân tích, Data visualization, Report,…

• Báo cáo tỷ trọng vốn đầu tư
• Chọn Ở bảng Data sau khi ETL ta thực hiện thao tác Insert/ Pivot chart – Phần tỷ trọng đầu tư mình sẽ vẽ biểu đồ hình tròn để tính tổng giá trị số vốn đầu tư USD và tổng vốn đầu tư VNĐ – cái này mình đã quy đổi đơn vị tiền tệ USD ra VNĐ.

• Báo cáo loại hình đầu tư

• Ta thự hiện thao tác giống như phần trên

Các báo cáo khác ta làm tương tự chúng ta sẽ có Dashboard đẹp đẽ dưới đây:

Trong phần trang Dashboard bạn có thể thêm phần Insert/ Slicer để thêm các bộ lọc năm, quý hoặc tên đơn vị cấp phép các loại hình đầu tư bạn muốn.

Như vậy là bạn đã hoàn thành 1 bộ phân tích dữ liệu với Excel sau 30 phút đó ạ. Bạn có thể xem thêm các case study phân tích dữ liệu khác tại khóa học data analysis online Cole.

Phần tiếp theo ta sẽ đọc thông tin insight:

1. Tỷ lệ đầu tư tại TPHCM là 40% vốn trong nước và 60% vốn nước ngoài.
2. Thời hạn cấp phép trung bình thấp nhất là hình thức đầu tư hợp đồng hợp tác kinh doanh trung bình là 14 năm.
3. Đơn vị cấp phép, do ủy ban nhân dân thành phố cấp chiếm tỷ trọng lên đến 70%.

Ngoài ra chúng ta có thể thêm 1 số biểu đồ để thể hiện rõ hơn tỷ lệ phân bổ vốn theo từng năm,….

Tạm kết: Có thể nói trong phân tích dữ liệu công đoạn ETL là công đoạn mất nhiều thời gian nhất, ngoài ra bạn phải xác định được yêu cầu và nghiệp vụ cụ thể, để báo cáo đầu ra được chuẩn hơn.

Trên đây là toàn bộ bài hướng dẫn làm phân tích dữ liệu theo chuẩn quy trình bằng Excel trong 30 phút.

>> Xem thêm Các loại hình chuyển đổi dữ liệu ETL