Bỏ qua đến nội dung chính
Mục lục

pandas vs Excel: lọc, VLOOKUP, PivotTable, SUMIF bằng pandas

AIDev Team · duyệt: Nguyễn Việt Chung

Bài 2/6 · Chương 11

Trang phụ trợ

  • 6 phút
  • Trung cấp
  • Python 3.14
  • Cập nhật

Trang pandas vs Excel này đặt các thao tác Excel quen dùng (lọc, sắp xếp, SUMIF, COUNTIF, VLOOKUP, PivotTable) cạnh câu lệnh pandas làm cùng việc, kèm kết quả thật của từng dòng. Mở trang khi bạn biết làm một việc trong Excel và cần câu lệnh pandas tương ứng.

Cách đọc bảng pandas vs Excel

Mỗi mục mở đầu bằng một bảng nhỏ dựng trong code, đóng vai trang tính Excel: bảng don gồm sáu đơn hàng với mã đơn, kênh bán, tỉnh, số tiền. Các dòng của bảng đối chiếu chạy lần lượt sau đoạn đó, như khi bạn gõ từng dòng vào cửa sổ Python. Cột Excel ghi thao tác hay công thức quen thuộc, cột Cú pháp là câu lệnh pandas, cột Kết quả là thứ câu lệnh đó cho ra. Kết quả nhiều dòng được in gọn thành list hay dict trên một dòng.

Khác biệt lớn nhất: Excel tính từng ô và tự cập nhật khi ô nguồn đổi, còn pandas tính trên cả cột một lần và trả một bảng mới. Bạn đổi dữ liệu thì chạy lại code, và dùng lại đúng quy trình đó cho file tháng sau. Bài Blog Python cho người dùng Excel nói khi nào nên chuyển; bài Excel, Power BI hay Python so ba công cụ.

Lọc dòng như AutoFilter

Lọc trong pandas là một phép so sánh trên cột, cho Series True/False, rồi giữ các dòng True.

python
import pandas as pd

don = pd.DataFrame({
    "ma_don": ["D01", "D02", "D03", "D04", "D05", "D06"],
    "kenh": ["Website", "Cửa hàng", "Website",
             "Ứng dụng", "Cửa hàng", "Website"],
    "tinh": ["Hà Nội", "Huế", "Huế", "Hà Nội", "Đà Lạt", "Hà Nội"],
    "tien": [250000, 120000, 480000, 90000, 310000, 150000],
})

Cần làm

Excel

Cú pháp

Kết quả

Bài

Lọc một giá trị

AutoFilter cột kênh, chọn Website

don[don["kenh"] == "Website"]["ma_don"].to_list()

['D01', 'D03', 'D06']

lọc dữ liệu

Lọc theo ngưỡng

Number Filters, Greater Than Or Equal To

don[don["tien"] >= 250000]["ma_don"].to_list()

['D01', 'D03', 'D05']

lọc dữ liệu

Hai điều kiện cùng lúc

Lọc hai cột

don[(don["kenh"] == "Website") & (don["tinh"] == "Hà Nội")]["ma_don"].to_list()

['D01', 'D06']

lọc dữ liệu

Một trong nhiều giá trị

Tích nhiều ô trong AutoFilter

don[don["tinh"].isin(["Huế", "Đà Lạt"])]["ma_don"].to_list()

['D02', 'D03', 'D05']

isin() và between()

Ô có chứa một chữ

Text Filters, Contains

don[don["kenh"].str.contains("hàng")]["ma_don"].to_list()

['D02', 'D05']

str.contains()

Đếm số dòng sau khi lọc

Thanh trạng thái, Count

len(don[don["kenh"] == "Cửa hàng"])

2

lọc dữ liệu

Sắp xếp

sort_values() trả bảng mới đã sắp; bảng don giữ nguyên thứ tự cũ.

python
import pandas as pd

don = pd.DataFrame({
    "ma_don": ["D01", "D02", "D03", "D04", "D05", "D06"],
    "kenh": ["Website", "Cửa hàng", "Website",
             "Ứng dụng", "Cửa hàng", "Website"],
    "tien": [250000, 120000, 480000, 90000, 310000, 150000],
})

Cần làm

Excel

Cú pháp

Kết quả

Bài

Sắp tăng dần

Sort A to Z

don.sort_values("tien")["ma_don"].to_list()

['D04', 'D02', 'D06', 'D01', 'D05', 'D03']

sort_values()

Sắp giảm dần

Sort Z to A

don.sort_values("tien", ascending=False)["ma_don"].to_list()

['D03', 'D05', 'D01', 'D06', 'D02', 'D04']

sort_values()

Sắp theo hai cột

Custom Sort, hai mức

don.sort_values(["kenh", "tien"])["ma_don"].to_list()

['D02', 'D05', 'D06', 'D01', 'D03', 'D04']

sort_values()

Ba dòng lớn nhất

Sort rồi lấy ba dòng đầu

don.nlargest(3, "tien")["ma_don"].to_list()

['D03', 'D05', 'D01']

nlargest()

SUM, COUNT, AVERAGE và SUMIF, COUNTIF

Hàm trên cả cột là phương thức của cột; phiên bản "IF" là lọc trước rồi tính, hay gom theo nhóm bằng groupby().

python
import pandas as pd

don = pd.DataFrame({
    "kenh": ["Website", "Cửa hàng", "Website",
             "Ứng dụng", "Cửa hàng", "Website"],
    "tien": [250000, 120000, 480000, 90000, 310000, 150000],
})

Cần làm

Excel

Cú pháp

Kết quả

Bài

Tổng một cột

=SUM(D2:D7)

print(don["tien"].sum())

1400000

thống kê mô tả

Trung bình, làm tròn

=ROUND(AVERAGE(D2:D7), 0)

print(round(don["tien"].mean()))

233333

thống kê mô tả

Lớn nhất, nhỏ nhất

=MAX(...), =MIN(...)

print(don["tien"].max(), don["tien"].min())

480000 90000

thống kê mô tả

SUMIF một điều kiện

=SUMIF(B2:B7, "Website", D2:D7)

print(don.loc[don["kenh"] == "Website", "tien"].sum())

880000

lọc dữ liệu

COUNTIF một điều kiện

=COUNTIF(D2:D7, ">=200000")

print((don["tien"] >= 200000).sum())

3

lọc dữ liệu

SUMIF cho mọi kênh một lần

Một SUMIF mỗi kênh

don.groupby("kenh")["tien"].sum().to_dict()

{'Cửa hàng': 430000, 'Website': 880000, 'Ứng dụng': 90000}

groupby()

COUNTIF cho mọi kênh một lần

Một COUNTIF mỗi kênh

don["kenh"].value_counts().to_dict()

{'Website': 3, 'Cửa hàng': 2, 'Ứng dụng': 1}

value_counts()

VLOOKUP: tra cột từ bảng khác

VLOOKUP tra từng ô; merge() ghép cả hai bảng theo cột khóa một lần, còn map() tra một cột theo một bảng tra.

python
import pandas as pd

don = pd.DataFrame({"ma_don": ["D01", "D02", "D03"],
                    "ma_kh": ["K2", "K1", "K9"]})
khach = pd.DataFrame({"ma_kh": ["K1", "K2", "K3"],
                      "ten": ["Lan", "Minh", "Hà"]})

Cần làm

Excel

Cú pháp

Kết quả

Bài

VLOOKUP, bỏ dòng không khớp

=VLOOKUP(B2, Khach!A:B, 2, FALSE) rồi lọc bỏ #N/A

don.merge(khach, on="ma_kh")["ten"].to_list()

['Minh', 'Lan']

merge()

VLOOKUP, giữ mọi đơn

=IFERROR(VLOOKUP(...), "")

don.merge(khach, on="ma_kh", how="left")["ten"].to_list()

['Minh', 'Lan', nan]

merge()

Tra một cột theo bảng tra

VLOOKUP một cột

don["ma_kh"].map(khach.set_index("ma_kh")["ten"]).to_list()

['Minh', 'Lan', nan]

map() và replace()

Đơn không tìm thấy khách

Lọc ô #N/A

don[~don["ma_kh"].isin(khach["ma_kh"])]["ma_don"].to_list()

['D03']

anti join

PivotTable

pivot_table() nhận đúng bốn ô của hộp PivotTable: Rows là index, Columns là columns, Values là values, phép tính là aggfunc.

python
import pandas as pd

don = pd.DataFrame({
    "kenh": ["Website", "Cửa hàng", "Website", "Cửa hàng"],
    "tinh": ["Hà Nội", "Huế", "Huế", "Hà Nội"],
    "tien": [250000, 120000, 480000, 310000],
})
bang = don.pivot_table(index="tinh", columns="kenh", values="tien",
                       aggfunc="sum", fill_value=0)

Cần làm

Excel

Cú pháp

Kết quả

Bài

Nhãn các dòng của PivotTable

Rows: tinh

bang.index.to_list()

['Huế', 'Hà Nội']

pivot_table()

Nhãn các cột của PivotTable

Columns: kenh

bang.columns.to_list()

['Cửa hàng', 'Website']

pivot_table()

Một ô của PivotTable

Đọc ô Huế × Website

print(bang.loc["Huế", "Website"])

480000

pivot_table()

Tổng mỗi dòng

Grand Total của dòng

bang.sum(axis=1).to_dict()

{'Huế': 600000, 'Hà Nội': 560000}

pivot_table()

Đếm thay cho cộng

Value Field Settings, Count

don.pivot_table(index="tinh", values="tien", aggfunc="count")["tien"].to_dict()

{'Huế': 2, 'Hà Nội': 2}

pivot_table()

Thêm cột tính và IF

Cột mới tính trên cả cột cũ một lần, không kéo công thức xuống từng ô.

python
import numpy as np
import pandas as pd

don = pd.DataFrame({"so_luong": [2, 1, 5],
                    "don_gia": [45000, 120000, 30000]})
don["thanh_tien"] = don["so_luong"] * don["don_gia"]

Cần làm

Excel

Cú pháp

Kết quả

Bài

Cột tính từ hai cột

=B2*C2 kéo xuống

don["thanh_tien"].to_list()

[90000, 120000, 150000]

thêm cột

IF hai nhánh

=IF(D2>=100000, "Lớn", "Nhỏ")

np.where(don["thanh_tien"] >= 100000, "Lớn", "Nhỏ").tolist()

['Nhỏ', 'Lớn', 'Lớn']

where trong NumPy

ROUND

=ROUND(D2/1000, 0)

(don["thanh_tien"] / 1000).round().to_list()

[90.0, 120.0, 150.0]

thêm cột

round() của pandas làm tròn số đúng giữa về số chẵn gần nhất, nên 2.5 thành 2 còn 3.5 thành 4; ROUND của Excel đẩy số đúng giữa ra xa 0 và cho 3, 4.

Mở và lưu file

Trang này không có thư viện đọc file .xlsx, nên các ví dụ chạy được dùng CSV. Trên máy của bạn, pd.read_excel("bao_cao.xlsx", sheet_name="T10") đọc một sheet và to_excel("ket_qua.xlsx", index=False) ghi ra file Excel, sau khi cài openpyxl; bài read_excel() và to_excel() dạy hai lệnh đó. Muốn Excel mở file CSV có tiếng Việt mà không lỗi font, ghi bằng to_csv("ket_qua.csv", index=False, encoding="utf-8-sig"), như bài to_csv() chỉ.

Chủ đề

pandas vs excel

Bài này có hữu ích?

Hỏi đáp

Đang tải câu hỏi…

Tham vấn chuyên môn

Nguyễn Việt Chung

Nhà sáng lập AIDev và VietPlatform · Thạc sĩ AI MSE

  • Tốt nghiệp thủ khoa xuất sắc Thạc sĩ AI MSE.
  • Đồng sáng lập, CTO E-University, SVOnline: số hóa 10+ đại học, 40.000+ sinh viên.
  • Xây hệ thống AI cho doanh nghiệp tại VietPlatform.
Biên soạn: AIDev Team · Rà soát · Quy trình biên tập