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

Hàm thống kê trong Excel sang Python: STDEV.S, T.TEST

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

Bài 3/8 · Chương 14

Trang phụ trợ

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

Hàm thống kê trong Excel như AVERAGE, MEDIAN, STDEV.S, PERCENTILE.INC, NORM.DIST hay T.TEST đều có lệnh tương ứng trong Python, và trang này đặt chúng cạnh nhau thành năm bảng, mỗi dòng kèm kết quả thật để bạn đối chiếu với file Excel của mình. Dùng trang khi bạn đã quen làm thống kê trên bảng tính và muốn chuyển một báo cáo sang Python mà không lệch con số nào.

Cách đọc các bảng Excel sang Python

Mỗi nhóm mở đầu bằng một đoạn code tạo dữ liệu, giống một cột trong Excel: mảng diem là tám ô A2:A9, mảng diem_b là B2:B9. Cột Excel ghi công thức gõ vào ô; cột Cú pháp là lệnh Python cho cùng con số, và cột Kết quả là thứ Python in ra. Các dòng chạy lần lượt sau đoạn code đầu nhóm.

Excel hiển thị số đã làm tròn theo định dạng ô; Python in đủ chữ số. Các dòng dưới đây làm tròn bằng round() để dễ so; khi so với Excel, tăng số chữ số thập phân của ô cho tới khi hai bên khớp. Đọc file Excel vào pandas, lọc, VLOOKUP và PivotTable có ở trang pandas cho người dùng Excel.

Trung bình, trung vị, mốt

Điểm của tám bạn, đặt ở A2:A9.

python
import statistics
import numpy as np

diem = np.array([6.5, 7.0, 8.5, 5.0, 9.0, 7.5, 6.0, 8.0])

Cần làm

Excel

Cú pháp

Kết quả

Bài

Trung bình cộng

=AVERAGE(A2:A9)

float(np.mean(diem))

7.1875

trung bình

Trung bình có trọng số

=SUMPRODUCT(A2:A4,B2:B4)/SUM(B2:B4)

float(np.average([7, 8, 9], weights=[1, 2, 3]))

8.333333333333334

trung bình có trọng số

Trung vị

=MEDIAN(A2:A9)

float(np.median(diem))

7.25

trung vị

Mốt (một giá trị)

=MODE.SNGL(A2:A6)

statistics.mode([7, 8, 8, 9, 9])

8

mốt

Mọi mốt

=MODE.MULT(A2:A6)

statistics.multimode([7, 8, 8, 9, 9])

[8, 9]

mốt

Trung bình nhân

=GEOMEAN(A2:A4)

round(statistics.geometric_mean([1.1, 1.2, 0.9]), 4)

1.0591

trung bình nhân, điều hòa

Lớn nhất trừ nhỏ nhất

=MAX(A2:A9)-MIN(A2:A9)

float(np.ptp(diem))

4.0

khoảng biến thiên

MODE.SNGL của Excel và statistics.mode() cùng trả mốt gặp trước khi có hai mốt; muốn thấy cả hai, dùng MODE.MULT hay statistics.multimode().

Phương sai, độ lệch chuẩn, phân vị

Đây là nhóm hay lệch nhất giữa hai bên: NumPy mặc định chia cho n (như STDEV.P), Excel có hai hàm riêng cho n và n − 1.

python
import numpy as np
from scipy import stats

diem = np.array([6.5, 7.0, 8.5, 5.0, 9.0, 7.5, 6.0, 8.0])

Cần làm

Excel

Cú pháp

Kết quả

Bài

Phương sai mẫu (chia n − 1)

=VAR.S(A2:A9)

round(float(np.var(diem, ddof=1)), 4)

1.7812

phương sai mẫu chia n − 1

Phương sai tổng thể (chia n)

=VAR.P(A2:A9)

round(float(np.var(diem, ddof=0)), 4)

1.5586

phương sai

Độ lệch chuẩn mẫu

=STDEV.S(A2:A9)

round(float(np.std(diem, ddof=1)), 4)

1.3346

độ lệch chuẩn

Độ lệch chuẩn tổng thể

=STDEV.P(A2:A9)

round(float(np.std(diem, ddof=0)), 4)

1.2484

độ lệch chuẩn

Phân vị 90, có hai đầu mút

=PERCENTILE.INC(A2:A9,0.9)

round(float(np.percentile(diem, 90)), 4)

8.65

tứ phân vị

Tứ phân vị Q1

=QUARTILE.INC(A2:A9,1)

float(np.percentile(diem, 25))

6.375

tứ phân vị

Tứ phân vị Q1, kiểu EXC

=QUARTILE.EXC(A2:A9,1)

float(np.percentile(diem, 25, method="weibull"))

6.125

tứ phân vị

Điểm chuẩn hóa z

=STANDARDIZE(9,AVERAGE(A2:A9),STDEV.S(A2:A9))

round(float((9 - diem.mean()) / diem.std(ddof=1)), 4)

1.358

z-score

Độ lệch (skewness) mẫu

=SKEW(A2:A9)

round(float(stats.skew(diem, bias=False)), 4)

-0.2958

độ lệch và độ nhọn

Sai số chuẩn

=STDEV.S(A2:A9)/SQRT(COUNT(A2:A9))

round(float(stats.sem(diem)), 4)

0.4719

sai số chuẩn

STDEV là tên cũ của STDEV.S trong Excel. Trong Python, viết ddof=1 mỗi lần tính độ lệch chuẩn mẫu: np.std(diem) không có ddof cho kết quả của STDEV.P. SKEW của Excel là bản hiệu chỉnh cho mẫu, nên Python cần bias=False.

Phân phối và giá trị tới hạn

Hàm phân phối của Excel có tham số cuối TRUE (tích lũy) hay FALSE (xác suất tại một điểm); scipy tách thành hai phương thức.

python
from scipy import stats

Cần làm

Excel

Cú pháp

Kết quả

Bài

P(X ≤ 495), X chuẩn μ = 500, σ = 4

=NORM.DIST(495,500,4,TRUE)

round(float(stats.norm.cdf(495, loc=500, scale=4)), 4)

0.1056

phân phối chuẩn

Φ(z) của chuẩn tắc

=NORM.S.DIST(1.25,TRUE)

round(float(stats.norm.cdf(1.25)), 4)

0.8944

phân phối chuẩn tắc

z tới hạn 97,5%

=NORM.S.INV(0.975)

round(float(stats.norm.ppf(0.975)), 4)

1.96

phân phối chuẩn tắc

t tới hạn hai phía, α = 0,05

=T.INV.2T(0.05,7)

round(float(stats.t.ppf(0.975, df=7)), 4)

2.3646

phân phối Student t

P(X = 3), nhị thức n = 10, p = 0,3

=BINOM.DIST(3,10,0.3,FALSE)

round(float(stats.binom.pmf(3, 10, 0.3)), 4)

0.2668

phân phối nhị thức

P(X ≤ 3), nhị thức

=BINOM.DIST(3,10,0.3,TRUE)

round(float(stats.binom.cdf(3, 10, 0.3)), 4)

0.6496

phân phối nhị thức

P(X = 2), Poisson λ = 4

=POISSON.DIST(2,4,FALSE)

round(float(stats.poisson.pmf(2, 4)), 4)

0.1465

phân phối Poisson

χ² tới hạn mức 5%, df = 2

=CHISQ.INV.RT(0.05,2)

round(float(stats.chi2.ppf(0.95, df=2)), 4)

5.9915

phân phối chi bình phương và F

Khoảng tin cậy và kiểm định

Hai ca đóng gói cà phê, mỗi ca cân sáu gói (A2:A7 và B2:B7).

python
import numpy as np
from scipy import stats

ca_sang = np.array([501.2, 498.7, 503.1, 499.5, 502.4, 500.8])
ca_chieu = np.array([497.9, 499.1, 496.4, 498.8, 500.2, 497.5])

Cần làm

Excel

Cú pháp

Kết quả

Bài

Nửa độ rộng khoảng tin cậy 95%

=CONFIDENCE.T(0.05,STDEV.S(A2:A7),6)

round(float(stats.t.ppf(0.975, df=5) * stats.sem(ca_sang)), 4)

1.7545

khoảng tin cậy cho trung bình

T.TEST kiểu 1: cặp, hai phía

=T.TEST(A2:A7,B2:B7,2,1)

round(float(stats.ttest_rel(ca_sang, ca_chieu).pvalue), 4)

0.0477

kiểm định t cặp

T.TEST kiểu 2: phương sai bằng nhau

=T.TEST(A2:A7,B2:B7,2,2)

round(float(stats.ttest_ind(ca_sang, ca_chieu).pvalue), 4)

0.013

kiểm định t hai mẫu

T.TEST kiểu 3: Welch

=T.TEST(A2:A7,B2:B7,2,3)

round(float(stats.ttest_ind(ca_sang, ca_chieu, equal_var=False).pvalue), 4)

0.0137

kiểm định t hai mẫu

T.TEST một phía (tails = 1)

=T.TEST(A2:A7,B2:B7,1,3)

round(float(stats.ttest_ind(ca_sang, ca_chieu, equal_var=False, alternative="greater").pvalue), 4)

0.0068

mức ý nghĩa

Chi bình phương: thực tế và mong đợi

=CHISQ.TEST(A2:B3,D2:E3)

round(float(stats.chi2_contingency([[30, 20], [18, 32]], correction=False).pvalue), 4)

0.0163

kiểm định chi bình phương

T.TEST của Excel chỉ trả p-value; scipy trả thêm thống kê t và bậc tự do. Excel tails = 1 cho một phía theo hướng của dữ liệu, còn scipy cần bạn nói hướng bằng alternative=. CHISQ.TEST cần bạn tự dựng bảng mong đợi và không hiệu chỉnh Yates, nên dòng Python ghi correction=False để ra cùng số với bảng 2 × 2.

Tương quan và hồi quy

Giờ tự học (A2:A7) và điểm (B2:B7) của sáu bạn.

python
import numpy as np
from scipy import stats

gio = np.array([1, 2, 3, 4, 5, 6])
diem = np.array([5.2, 6.1, 6.4, 7.3, 7.9, 8.6])

Cần làm

Excel

Cú pháp

Kết quả

Bài

Hệ số tương quan

=CORREL(A2:A7,B2:B7)

round(float(np.corrcoef(gio, diem)[0, 1]), 4)

0.9953

kiểm định hệ số tương quan

Hiệp phương sai mẫu

=COVARIANCE.S(A2:A7,B2:B7)

round(float(np.cov(gio, diem)[0, 1]), 4)

2.33

hiệp phương sai

Hệ số góc

=SLOPE(B2:B7,A2:A7)

round(float(stats.linregress(gio, diem).slope), 4)

0.6657

hồi quy đơn

Hệ số chặn

=INTERCEPT(B2:B7,A2:A7)

round(float(stats.linregress(gio, diem).intercept), 4)

4.5867

hồi quy đơn

R²

=RSQ(B2:B7,A2:A7)

round(float(stats.linregress(gio, diem).rvalue ** 2), 4)

0.9907

hồi quy đơn

SLOPE và INTERCEPT của Excel nhận cột y trước, cột x sau; linregress() nhận x trước. Đảo thứ tự là lỗi hay gặp nhất khi chép công thức sang.

Câu hỏi thường gặp

STDEV trong Excel là STDEV.S hay STDEV.P?

STDEV (tên cũ) là STDEV.S: độ lệch chuẩn mẫu, chia n − 1. Trong Python đó là np.std(x, ddof=1) hay df["cot"].std() của pandas (mặc định ddof=1). Bài phương sai mẫu chia n − 1 giải thích vì sao có hai cách chia.

Hàm AVERAGE và MEDIAN bỏ qua ô trống, Python có vậy không?

AVERAGE và MEDIAN của Excel bỏ qua ô trống. np.mean() gặp NaN thì trả nan; dùng np.nanmean(), np.nanmedian(), hay df["cot"].mean() của pandas (tự bỏ ô trống). Bài NaN trong NumPy nói thêm.

T test Excel và scipy cho p khác nhau thì kiểm gì?

Kiểm ba chỗ: kiểu (1 cặp, 2 phương sai bằng nhau, 3 Welch) có khớp ttest_rel(), ttest_ind(), equal_var=False không; số phía (tails) có khớp alternative= không; và hai vùng ô có cùng số dòng, không lẫn ô trống không. Bài kiểm định t hai mẫu so Student và Welch.

Chủ đề

hàm thống kê trong 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