Excel là công cụ được nhiều nhà phân tích dữ liệu sử dụng vì tính đơn giản và linh hoạt của nó. Nhưng đối với các tác vụ dữ liệu lớn, lặp đi lặp lại hoặc phức tạp, Python cung cấp tốc độ, tự động hóa và phân tích nâng cao. Bằng cách tích hợp Excel với Python, bạn có thể tận dụng tối đa cả hai thế giới.
Trong hướng dẫn này, chúng tôi sẽ trình bày cách kết hợp Excel với Python để có quy trình làm việc khoa học dữ liệu mạnh mẽ.
Công cụ &thiết lập cần thiết
Trước khi kết hợp Excel với Python, hãy thiết lập môi trường. Điều này đảm bảo quy trình làm việc của bạn suôn sẻ và hiệu quả ngay từ bước đầu tiên.
Điều kiện tiên quyết:
- Microsoft Excel :Để xem xét và báo cáo dữ liệu ban đầu.
- Python 3.x :Công cụ cho quy trình làm việc về khoa học dữ liệu của bạn.
- Thư viện Python :
- gấu trúc để phân tích dữ liệu.
- matplotlib để vẽ đồ thị.
- openpyxl (tùy chọn, để ghi tệp Excel).
- khó chịu (số).
- matplotlib/seaborn để trực quan hóa.
Cài đặt thư viện Python:
pip install pandas matplotlib openpyxl
1. Đọc dữ liệu vào Python
Bạn có thể tải dữ liệu của mình vào Python bằng cách sử dụng gấu trúc, điều này giúp bạn dễ dàng thao tác và phân tích dữ liệu dạng bảng.
import pandas as pd
# Read data from Excel file
df = pd.read_excel('SalesData.xlsx')
# Preview data
print(df.head()) # Show the first 5 rows of the data
print(df.info()) # Show info about columns, datatypes, and missing values
- pd.read_csv() đọc tệp Excel vào DataFrame của gấu trúc.
- df.head() hiển thị năm hàng đầu tiên, rất tốt để kiểm tra nhanh.
- df.info() hiển thị số hàng, cột và kiểu dữ liệu.
Bạn sẽ thấy một vài hàng đầu tiên của dữ liệu bán hàng của mình cùng với phần tóm tắt như:
TransactionID Date CustomerID ProductID ProductName Category Quantity UnitPrice Region Channel SalesRep 0 100001 2024-01-02 C-100 P-101 Laptop Electronics 2.0 800.0 East Online Smith 1 100002 2024-01-02 C-101 P-102 Printer Electronics 1.0 200.0 West Retail Johnson 2 100003 2024-01-03 C-102 P-103 Mouse Electronics 5.0 25.0 North Online Lee 3 100004 2024-01-04 C-103 P-104 Desk Furniture 1.0 150.0 South Retail Brown 4 100005 2024-01-05 C-104 P-105 Monitor Electronics 3.0 175.0 NaN Online Davis <class 'pandas.core.frame.DataFrame'> RangeIndex: 63 entries, 0 to 62 Data columns (total 11 columns): # Column Non-Null Count Dtype --- ------ -------------- ----- 0 TransactionID 63 non-null int64 1 Date 62 non-null datetime64[ns] 2 CustomerID 62 non-null object 3 ProductID 61 non-null object 4 ProductName 63 non-null object 5 Category 61 non-null object 6 Quantity 61 non-null float64 7 UnitPrice 62 non-null float64 8 Region 62 non-null object 9 Channel 62 non-null object 10 SalesRep 62 non-null object dtypes: datetime64[ns](1), float64(2), int64(1), object(7) memory usage: 5.5+ KB None
2. Làm sạch và chuyển đổi dữ liệu
Dữ liệu thô hiếm khi sẵn sàng để phân tích. Trong bước này, bạn sẽ sửa các giá trị bị thiếu, chuyển đổi cột thành loại phù hợp và thêm các trường tính toán mới.
Xóa nội dung trùng lặp:
# Remove Duplicates df = df.drop_duplicates()
- Loại bỏ các giá trị trùng lặp.
Kiểm tra các giá trị bị thiếu:
# Print count of missing values per column print(df.isnull().sum())
- Nó hiển thị số lượng giá trị (NaN) bị thiếu trong mỗi cột. Nếu tìm thấy bất kỳ thứ gì, bạn có thể quyết định bỏ hoặc điền chúng.
#Output: TransactionID 0 Date 1 CustomerID 1 ProductID 2 ProductName 0 Category 2 Quantity 2 UnitPrice 1 Region 1 Channel 1 SalesRep 1 dtype: int64
Chuyển đổi kiểu dữ liệu:
# Convert 'Date' column to pandas datetime type for easier filtering/grouping df['Date'] = pd.to_datetime(df['Date'])
- Nó chuyển đổi cột Ngày từ văn bản sang định dạng ngày giờ gấu trúc để lọc và nhóm dễ dàng hơn.
Tạo cột 'Tổng doanh số':
# Add a new column: total value for each transaction df['TotalSales'] = df['Quantity'] * df['UnitPrice']
- Thêm cột mới hiển thị tổng giá trị cho mỗi giao dịch.
Trích xuất tháng để phân tích chuỗi thời gian:
df['Month'] = df['Date'].dt.to_period('M')
- Điều này tạo ra cột Tháng để nhóm và phân tích doanh số bán hàng theo tháng.
- Bây giờ hãy sử dụng print(df.head()) để xem trước dữ liệu đã được làm sạch.
#Ouput: TransactionID Date CustomerID ProductID ProductName Category ... UnitPrice Region Channel SalesRep TotalSales Month 0 100001 2024-01-02 C-100 P-101 Laptop Electronics ... 800.0 East Online Smith 1600.0 2024-01 1 100002 2024-01-02 C-101 P-102 Printer Electronics ... 200.0 West Retail Johnson 200.0 2024-01 2 100003 2024-01-03 C-102 P-103 Mouse Electronics ... 25.0 North Online Lee 125.0 2024-01 3 100004 2024-01-04 C-103 P-104 Desk Furniture ... 150.0 South Retail Brown 150.0 2024-01 4 100005 2024-01-05 C-104 P-105 Monitor Electronics ... 175.0 NaN Online Davis 525.0 2024-01
3. Phân tích dữ liệu của bạn
Với tập dữ liệu rõ ràng, giờ đây bạn có thể tạo thông tin chuyên sâu giúp nâng cao giá trị doanh nghiệp. Điều này bao gồm tổng doanh số bán hàng theo tháng, sản phẩm và khu vực.
Tổng doanh số theo tháng:
# Group by month and sum the total sales for each month
monthly_sales = df.groupby('Month')['TotalSales'].sum()
print(monthly_sales)
- Nhóm dữ liệu theo Tháng và tính tổng Tổng doanh số cho mỗi tháng.
#Output: Month 2024-01 9075.0 2024-02 9800.0 2024-03 9075.0 Freq: M, Name: TotalSales, dtype: float64
Sản phẩm bán chạy nhất:
# Group by product, sum total sales, and sort from highest to lowest
product_sales = df.groupby('ProductName')['TotalSales'].sum().sort_values(ascending=False)
print(product_sales)
- Tính tổng doanh số bán hàng trên mỗi sản phẩm, sau đó sắp xếp chúng từ phổ biến nhất đến ít phổ biến nhất.
#Output: ProductName Laptop 15200.0 Monitor 3850.0 Printer 3200.0 Desk 2550.0 Chair 2325.0 Mouse 1125.0 Name: TotalSales, dtype: float64
Doanh số theo khu vực:
# Group by region and sum total sales per region
region_sales = df.groupby('Region')['TotalSales'].sum()
print(region_sales)
- Tổng hợp tổng doanh số theo từng khu vực.
#Output: Region East 6075.0 North 5925.0 South 8225.0 West 7500.0
4. Trực quan hóa những thông tin chi tiết quan trọng
Dữ liệu mạnh mẽ hơn khi được trực quan hóa. Hãy tạo biểu đồ nhanh để giúp bạn và các bên liên quan nắm bắt nhanh các xu hướng chính.
4.1. Xu hướng bán hàng hàng tháng
import matplotlib.pyplot as plt # Import for plotting
# Create a bar chart of sales by month
monthly_sales.plot(
kind='bar',
title='Total Sales by Month',
ylabel='Sales ($)',
xlabel='Month'
)
plt.tight_layout() # Avoid label overlap
plt.savefig('monthly_sales.png') # Save the figure as a PNG file
plt.show() # Display the chart - Biểu đồ doanh số hàng tháng dưới dạng biểu đồ thanh.
- plt.savefig lưu biểu đồ cho báo cáo
- Biểu đồ thanh sẽ hiển thị sự thay đổi về doanh số bán hàng mỗi tháng.
4.2. Doanh số theo khu vực
# Pie chart of sales by region
region_sales.plot(
kind='pie',
autopct='%1.1f%%',
title='Sales Distribution by Region'
)
plt.ylabel('') # Remove default y-label
plt.tight_layout()
plt.savefig('region_sales.png')
plt.show()
- Biểu đồ hình tròn về doanh số bán hàng theo khu vực, rất phù hợp cho việc quản lý hoặc tiếp thị.
5. Phân tích &lập mô hình nâng cao
Ngoài việc nhóm và tóm tắt cơ bản, Python còn cho phép phân tích thống kê nâng cao, bảng tổng hợp và thậm chí cả học máy, tất cả chỉ với một vài dòng mã. Hãy cùng tìm hiểu sâu hơn về dữ liệu để có được nhiều thông tin chi tiết hơn nữa.
5.1. Thống kê mô tả
Thống kê mô tả cung cấp cho bạn bản tóm tắt nhanh về tập dữ liệu của bạn, hiển thị phương tiện, độ lệch chuẩn và phân vị cho các cột số.
# Show summary statistics for numeric columns (mean, std, min, max, quartiles, etc.) print(df.describe())
- df.describe() tóm tắt nhanh chóng tất cả các cột số (như Số lượng, Đơn giá, Tổng doanh số).
#Output: TransactionID Quantity UnitPrice TotalSales count 61.000000 59.000000 60.000000 59.000000 mean 100030.180328 2.542373 262.083333 478.813559 std 17.497150 1.534905 277.339497 527.085627 min 100001.000000 1.000000 25.000000 75.000000 25% 100015.000000 1.000000 75.000000 162.500000 50% 100030.000000 2.000000 175.000000 300.000000 75% 100045.000000 3.000000 200.000000 525.000000 max 100060.000000 7.000000 800.000000 2400.000000
5.2. Bảng tổng hợp trong gấu trúc
Bảng tổng hợp rất hữu ích cho báo cáo tương tác trong Excel và gấu trúc cũng có thể làm được điều đó.
# Create a pivot table: sum TotalSales for each Region pivot = df.pivot_table(index='Region', values='TotalSales', aggfunc='sum') print(pivot)
- pivot_table() tóm tắt TotalSales cho từng khu vực, tương tự như Pivot Table của Excel.
#Output TotalSales Region East 6075.0 North 5925.0 South 8225.0 West 7500.0
5.3. Ví dụ về học máy đơn giản
Hãy xem liệu chúng ta có thể dự đoán tổng doanh số bán hàng chỉ từ số lượng bán được bằng cách sử dụng mô hình hồi quy tuyến tính đơn giản (học máy) hay không.
from sklearn.linear_model import LinearRegression # Import linear regression from scikit-learn
# Prepare features and target variable
X = df[['Quantity']] # Feature: Quantity sold
y = df['TotalSales'] # Target: Total sales value
# Create and fit the regression model
model = LinearRegression()
model.fit(X, y)
# Print the regression coefficient (slope)
print('Coefficient:', model.coef_)
# Print the intercept (base value when Quantity=0)
print('Intercept:', model.intercept_) - Nhập LinearRegression từ scikit-learn.
- Sử dụng Số lượng để dự đoán Tổng doanh số.
- Phù hợp với mô hình và in ra hệ số (doanh số tăng bao nhiêu trên mỗi đơn vị bán thêm).
#Output: Coefficient: [-37.65294772] Intercept: 596.8483500185391
6. Xuất dữ liệu đã được làm sạch/phân tích trở lại Excel
Sau khi làm sạch, phân tích và lập mô hình dữ liệu, bạn có thể xuất các bảng tóm tắt và thông tin chi tiết sang tệp Excel nhiều trang. Điều này giúp tập hợp tất cả những phát hiện quan trọng của bạn và sẵn sàng để xem xét trong Excel.
# Export summary and advanced analysis tables to a multi-sheet Excel file
with pd.ExcelWriter('sales_summary.xlsx') as writer:
# Monthly summary
monthly_sales.to_frame().to_excel(writer, sheet_name='Monthly Sales')
# Product summary
product_sales.to_frame().to_excel(writer, sheet_name='Product Sales')
# Region summary
region_sales.to_frame().to_excel(writer, sheet_name='Region Sales')
# Pivot table (total sales by region)
pivot.to_excel(writer, sheet_name='Pivot Table')
# Optionally, you can export descriptive statistics
df.describe().to_excel(writer, sheet_name='Descriptive Stats')
- Trình quản lý bối cảnh (với ... là người viết): Đảm bảo tệp Excel được lưu và đóng đúng cách sau khi viết.
- .to_excel() cho mỗi bảng: Lưu từng DataFrame hoặc bản tóm tắt vào trang tính riêng để dễ dàng truy cập.
- Tên trang tính tùy chỉnh: Mỗi trang tính được đặt tên rõ ràng, phù hợp với các bước phân tích của bạn.
- Mở sale_summary.xlsx trong Excel.
- Bạn sẽ thấy các trang riêng biệt về Doanh số hàng tháng, Doanh số sản phẩm, Doanh số theo khu vực, Bảng tổng hợp và Thống kê mô tả.
7. Tự động hóa và mở rộng quy trình làm việc của bạn
Bằng cách sử dụng Python, bạn có thể tự động hóa các báo cáo hoặc phân tích định kỳ. Lần tới khi bạn nhận được tệp Excel mới, chỉ cần thay thế tệp đó và chạy lại tập lệnh của bạn; tất cả các phân tích và báo cáo đều được làm mới ngay lập tức.
- Giữ tất cả mã phân tích trong một tệp Python.
- Để cập nhật báo cáo của bạn, hãy thay thế CSV và chạy:
python Excel_to_Python.py
- Để có thêm sức mạnh, bạn có thể lên lịch việc này như một nhiệm vụ hàng tuần/hàng tháng.
Kết luận
Bằng cách kết hợp tính năng nhập và báo cáo dữ liệu trực quan của Excel với sức mạnh khoa học dữ liệu của Python, bạn có thể xử lý và phân tích các tập dữ liệu lớn, lộn xộn một cách hiệu quả. Nó tự động hóa các nhiệm vụ báo cáo lặp đi lặp lại. Mở khóa khả năng học máy và trực quan hóa nâng cao. Bạn không cần phải trở thành chuyên gia Python chỉ sau một đêm. Bắt đầu với một nhiệm vụ đơn giản; khi đã thành công, hãy thêm một bước nữa. Trước khi bạn nhận ra điều đó, bạn sẽ tự động hóa các báo cáo phức tạp.
Nhận MIỄN PHÍ Bài tập Excel nâng cao có Giải pháp!