Có thể bạn quan tâm: Hướng Dẫn Chi Tiết Cách Tính Hệ Số Tương Quan (correlation Coefficient) Trong Excel
Mở đầu: Tại sao lại cần tính IQR?
Khoảng tứ phân, hay còn gọi là Interquartile Range (IQR), là một trong những chỉ số thống kê quan trọng giúp chúng ta hiểu rõ hơn về độ phân tán của dữ liệu. IQR được định nghĩa là khoảng cách giữa phân vị thứ ba (Q3) và phân vị thứ nhất (Q1), tức là:
\text{IQR} = Q3 – Q1
Vai trò của IQR trong phân tích dữ liệu
- Đánh giá độ biến thiên: IQR cho biết phần dữ liệu nằm trong trung vị (50% trung tâm). So với độ lệch chuẩn, IQR không bị ảnh hưởng mạnh bởi những giá trị ngoại lệ (outliers), vì vậy nó là thước đo “bền vững” (robust).
- Phát hiện outliers: Một quy tắc phổ biến là bất kỳ giá trị nào nhỏ hơn (Q1 – 1.5 \times IQR) hoặc lớn hơn (Q3 + 1.5 \times IQR) đều được xem là ngoại lệ.
- So sánh nhóm: Khi so sánh hai hoặc nhiều nhóm dữ liệu, IQR giúp chúng ta hiểu nhóm nào có độ biến thiên lớn hơn, từ đó đưa ra quyết định kinh doanh, y tế, giáo dục, v.v.
Với tầm quan trọng này, việc tính IQR nhanh chóng và chính xác trong Excel là kỹ năng cần thiết cho mọi nhà phân tích dữ liệu, sinh viên, nhà nghiên cứu và doanh nghiệp.
Có thể bạn quan tâm: Cài Đặt Excel Miễn Phí: Hướng Dẫn Chi Tiết Từ A Đến Z
Phần 1: Kiến thức nền tảng – Phân vị và IQR
1.1. Phân vị là gì?
Phân vị (percentile) là giá trị chia dữ liệu thành 100 phần bằng nhau. Các phân vị thường được dùng:
- Q1 (25th percentile) – giá trị dưới đó có 25% dữ liệu.
- Median (Q2, 50th percentile) – giá trị trung vị.
- Q3 (75th percentile) – giá trị dưới đó có 75% dữ liệu.
1.2. Công thức tính IQR
\text{IQR} = Q3 – Q1
Nếu bạn có một tập hợp dữ liệu, việc xác định Q1 và Q3 là bước đầu tiên, sau đó trừ Q1 khỏi Q3 để có IQR.
Có thể bạn quan tâm: Cách Chỉnh Ngay Trên Excel: Hướng Dẫn Chi Tiết Từ A Đến Z Để Làm Việc Hiệu Quả
Phần 2: Chuẩn bị dữ liệu trong Excel
2.1. Định dạng dữ liệu
- Cột dữ liệu: Đặt dữ liệu số trong một cột (ví dụ: cột A từ A2 tới A101). Đảm bảo không có ô trống hoặc dữ liệu không phải số trong dải.
- Tiêu đề: Đặt tiêu đề ở hàng đầu (A1), ví dụ “Doanh thu (nghìn $)”.
2.2. Kiểm tra dữ liệu sạch
Sử dụng Data → Filter để lọc và loại bỏ giá trị lỗi, hoặc dùng công thức =ISNUMBER(A2) để kiểm tra. Bạn cũng có thể dùng Conditional Formatting để tô màu các giá trị không hợp lệ.
Phần 3: Cách tính Q1, Q3 và IQR bằng các hàm Excel
Excel cung cấp hai nhóm hàm để tính phân vị:
| Nhóm hàm | Hàm | Mô tả |
|---|---|---|
| Phiên bản Excel 2026 trở lên | PERCENTILE.INC(array, k) | Tính phân vị theo phương pháp “inclusive” (bao gồm cả giá trị cực biên). |
PERCENTILE.EXC(array, k) | Tính phân vị theo phương pháp “exclusive” (bỏ qua các giá trị cực biên). | |
| Phiên bản cũ | PERCENTILE(array, k) | Tương đương PERCENTILE.INC. |
QUARTILE(array, quart) | Trả về Q1, Q2, Q3 (quart = 1,2,3). | |
QUARTILE.INC(array, quart) | Tương tự QUARTILE. | |
QUARTILE.EXC(array, quart) | Tương tự QUARTILE nhưng theo phương pháp exclusive. |
Lưu ý: Khi làm việc với dữ liệu thực tế, hầu hết người dùng nên dùng
PERCENTILE.INChoặcQUARTILE.INCvì chúng tuân thủ tiêu chuẩn thống kê phổ biến hơn.
3.1. Tính Q1 và Q3 bằng QUARTILE.INC
Giả sử dữ liệu nằm trong A2:A101.
- Q1 (25th percentile):
=QUARTILE.INC(A2:A101, 1)
- Q3 (75th percentile):
=QUARTILE.INC(A2:A101, 3)
3.2. Tính IQR bằng cách trừ
=QUARTILE.INC(A2:A101, 3) - QUARTILE.INC(A2:A101, 1)
Bạn có thể đặt công thức này vào ô B2 (hoặc bất kỳ ô nào) và đặt tiêu đề “IQR”.
3.3. Sử dụng PERCENTILE.INC (cách khác)
- Q1:
=PERCENTILE.INC(A2:A101, 0.25)
- Q3:
=PERCENTILE.INC(A2:A101, 0.75)
- IQR:
=PERCENTILE.INC(A2:A101, 0.75) - PERCENTILE.INC(A2:A101, 0.25)
3.4. Khi nào dùng EXC?
Nếu bạn muốn bỏ qua các giá trị cực biên (đôi khi dùng trong một số lĩnh vực chuyên sâu), hãy thay INC bằng EXC. Công thức sẽ hơi khác:
=QUARTILE.EXC(A2:A101, 1) // Q1
=QUARTILE.EXC(A2:A101, 3) // Q3
Và IQR:
=QUARTILE.EXC(A2:A101, 3) - QUARTILE.EXC(A2:A101, 1)
Phần 4: Tự động hoá – Tạo bảng tính IQR cho nhiều cột
Trong thực tế, bạn thường phải tính IQR cho nhiều biến (cột) cùng lúc. Dưới đây là cách thiết lập một bảng tổng hợp tự động.
4.1. Bố trí bảng
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Biến | Q1 | Q3 | IQR | Giới hạn dưới (Lower Fence) |
| 2 | Doanh thu | ||||
| 3 | Chi phí | ||||
| 4 | Lợi nhuận |
Giả sử dữ liệu:
- Doanh thu nằm ở Sheet1!A2:A101
- Chi phí ở Sheet1!B2:B101
- Lợi nhuận ở Sheet1!C2:C101
4.2. Công thức động với INDEX
Trong ô B2 (Q1 cho Doanh thu):
=QUARTILE.INC(INDEX(Sheet1!$A$2:$C$101,0,1),1)
Giải thích:
INDEX(range,0,1)trả về toàn bộ cột 1 của dải (cột A).- Thay
1bằng2hoặc3để lấy cột Chi phí, Lợi nhuận.
Sao chép công thức sang B3, B4 và thay 1 thành 2, 3 tương ứng.
4.3. Công thức Q3 và IQR
- C2 (Q3):
=QUARTILE.INC(INDEX(Sheet1!$A$2:$C$101,0,1),3)
- D2 (IQR):
=C2-B2
4.4. Giới hạn dưới và trên (outlier fences)
Có thể bạn quan tâm: Cách Tính Khoảng Cách Ngày Trong Excel: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao
- Lower Fence (giới hạn dưới) = Q1 – 1.5 IQR
- Upper Fence (giới hạn trên) = Q3 + 1.5 IQR
Trong ô E2 (Lower Fence):
=B2 - 1.5D2
Trong ô F2 (Upper Fence):
=C2 + 1.5D2
Kéo công thức xuống cho các biến còn lại. Như vậy bạn có một bảng đầy đủ: Q1, Q3, IQR và các ngưỡng để phát hiện outliers cho mọi cột dữ liệu.
Phần 5: Visualize – Vẽ boxplot (hộp) trong Excel để xem IQR
Excel không có biểu đồ boxplot truyền thống cho tới Excel 2026 trở lên (được gọi là “Box and Whisker”). Nếu bạn dùng phiên bản cũ hơn, có thể tạo thủ công.
5.1. Boxplot trong Excel 2026+ (đơn giản)
- Chọn dữ liệu: Chọn toàn bộ cột dữ liệu (ví dụ A2:A101).
- Insert → Insert Statistic Chart → Box and Whisker.
- Excel sẽ tự động tính Q1, Q3, median, và outliers, hiển thị IQR dưới dạng “box”.
Bạn có thể tùy chỉnh màu sắc, thêm tiêu đề, và hiển thị giá trị trung vị.
5.2. Tạo boxplot thủ công (Excel 2026 hoặc cũ)
- Tính các tham số: Q1, Q3, Median, Lower Fence, Upper Fence (theo công thức ở phần 4).
- Tạo một bảng tóm tắt:
| Giá trị | |
|---|---|
| Min (không tính outlier) | =MIN(IF(A2:A101>=E2, A2:A101)) |
| Q1 | =B2 |
| Median | =MEDIAN(A2:A101) |
| Q3 | =C2 |
| Max (không tính outlier) | =MAX(IF(A2:A101<=F2, A2:A101)) |
- Chèn một biểu đồ Stacked Column và điều chỉnh sao cho “box” hiển thị IQR, “whiskers” hiển thị khoảng min-max không tính outlier. Các bước chi tiết có thể tham khảo các tutorial trên YouTube (từ “Excel manual boxplot”).
Phần 6: Ứng dụng thực tế – Ví dụ thực hành
6.1. Dữ liệu bán hàng
Giả sử bạn có dữ liệu Doanh thu của 100 cửa hàng trong một tháng. Dữ liệu (đơn vị nghìn $) được nhập ở cột A.
| A (Doanh thu) |
|---|
| 12 |
| 15 |
| 18 |
| … |
| 250 |
Bước 1: Tính Q1, Q3, IQR
- Q1:
=QUARTILE.INC(A2:A101,1)→ 45 - Q3:
=QUARTILE.INC(A2:A101,3)→ 180 - IQR:
=C2-B2→ 135
Bước 2: Xác định outliers
- Lower Fence = 45 – 1.5135 = -157 (không có giá trị âm, nên không có outlier dưới)
- Upper Fence = 180 + 1.5135 = 382.5
Nếu có cửa hàng nào có doanh thu > 382.5, nó sẽ được đánh dấu là ngoại lệ (có thể là lỗi nhập liệu hoặc một cửa hàng “siêu sao”).
6.2. Dữ liệu y tế – Đo huyết áp
Bạn có 200 bệnh nhân, đo huyết áp tâm thu (mmHg) trong cột B.
- Q1 = 112
- Q3 = 138
- IQR = 26
Upper Fence = 138 + 1.526 = 177 → Bệnh nhân > 177 mmHg có thể được xem là huyết áp cao nghiêm trọng, cần can thiệp.
6.3. So sánh IQR giữa hai nhóm
Bạn muốn so sánh độ biến thiên của thu nhập giữa nam và nữ.
| Gender | Income |
|---|---|
| Nam | 45 |
| Nữ | 38 |
| … | … |
- Tạo hai dải riêng:
Nam!C2:C101,Nữ!D2:D101. - Tính IQR cho mỗi nhóm:
- Nam:
=QUARTILE.INC(Nam!C2:C101,3)-QUARTILE.INC(Nam!C2:C101,1)→ 22 - Nữ:
=QUARTILE.INC(Nữ!D2:D101,3)-QUARTILE.INC(Nữ!D2:D101,1)→ 18 - Kết luận: Nhóm nam có độ biến thiên thu nhập lớn hơn, có thể do ngành nghề đa dạng hơn.
Phần 7: Những lỗi thường gặp và cách khắc phục
| Lỗi | Nguyên nhân | Cách khắc phục |
|---|---|---|
| #NUM! | Dải dữ liệu rỗng hoặc chứa lỗi (text, lỗi #DIV/0!). | Kiểm tra và lọc dữ liệu, dùng =IFERROR(..., ""). |
| Kết quả không khớp với phần mềm thống kê | Sử dụng QUARTILE.EXC trong khi phần mềm dùng INC. | Đảm bảo cùng phương pháp (INC) hoặc chuyển đổi công thức. |
| Outlier không được phát hiện | Giới hạn quá lớn vì IQR quá lớn (do dữ liệu có outlier cực đoan). | Loại bỏ outlier ban đầu, rồi tính lại IQR. |
| Không có boxplot trong Excel 2026 | Phiên bản không hỗ trợ. | Tạo thủ công (phần 5.2) hoặc nâng cấp lên phiên bản mới hơn. |
| Công thức trả về giá trị sai khi có ô trống | Hàm QUARTILE tính cả ô trống như 0. | Loại bỏ ô trống bằng cách lọc, hoặc dùng =QUARTILE.INC(IF(ISNUMBER(A2:A101),A2:A101),k) dưới dạng array formula (Ctrl+Shift+Enter). |
Mẹo nâng cao
- Array formula để bỏ qua ô trống:
=QUARTILE.INC(IF(ISNUMBER(A2:A101),A2:A101),0.25)
Nhấn Ctrl+Shift+Enter để tạo công thức mảng (đối với Excel 2026 hoặc cũ hơn).
Sử dụng Power Query: Nhập dữ liệu, lọc, và tính IQR trực tiếp trong Power Query, sau đó tải kết quả về bảng tính.
Đặt tên dải (named range): Đặt tên
DoanhThucho dải A2:A101, sau đó công thức ngắn gọn hơn:=QUARTILE.INC(DoanhThu,1).
Phần 8: Tự động hoá với VBA (Macro)
Nếu bạn thường xuyên phải tính IQR cho nhiều bảng dữ liệu, một macro VBA sẽ tiết kiệm thời gian.
8.1. Mã VBA mẫu
Sub CalculateIQR() Dim ws As Worksheet Dim dataRng As Range Dim q1 As Double, q3 As Double, iqr As Double Set ws = ActiveSheet Set dataRng = ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) q1 = Application.WorksheetFunction.Quartile_Inc(dataRng, 1) q3 = Application.WorksheetFunction.Quartile_Inc(dataRng, 3) iqr = q3 - q1 ws.Range("B1").Value = "Q1" ws.Range("C1").Value = "Q3" ws.Range("D1").Value = "IQR" ws.Range("B2").Value = q1 ws.Range("C2").Value = q3 ws.Range("D2").Value = iqr MsgBox "IQR đã được tính: " & iqr, vbInformation
End Sub
- Cách dùng: Mở Alt+F11, chèn Module, dán code, đóng VBA, quay lại Excel và chạy macro
CalculateIQR. Kết quả sẽ xuất hiện ở cột B, C, D.
8.2. Mở rộng cho nhiều cột
Bạn có thể lặp qua các cột trong một vòng For Each col In ws.UsedRange.Columns và lưu kết quả vào một bảng tổng hợp, giống như trong phần 4.
Phần 9: Tóm tắt và những lưu ý cuối cùng
- IQR = Q3 – Q1 là thước đo độ biến thiên bền vững, ít bị ảnh hưởng bởi outliers.
- Excel cung cấp
QUARTILE.INC,QUARTILE.EXC,PERCENTILE.INC,PERCENTILE.EXC. Đối với hầu hết trường hợp, dùng INC. - Công thức cơ bản:
excel =QUARTILE.INC(range,3) - QUARTILE.INC(range,1) - Xác định outliers bằng công thức:
- Lower Fence = Q1 – 1.5IQR
- Upper Fence = Q3 + 1.5IQR
- Visualize: Dùng Boxplot (Excel 2026+) hoặc tạo thủ công để trực quan hoá IQR và outliers.
- Tự động hoá: Sử dụng bảng tổng hợp, công thức
INDEX, hoặc VBA để tính nhanh cho nhiều biến. - Kiểm tra dữ liệu: Loại bỏ ô trống, giá trị không phải số, và dùng công thức mảng nếu cần.
Kết luận
Việc tính khoảng tứ phân (IQR) trong Excel không chỉ giúp bạn nắm bắt được mức độ biến thiên của dữ liệu mà còn là nền tảng để phát hiện ngoại lệ, so sánh nhóm, và đưa ra các quyết định dựa trên dữ liệu một cách thông minh. Với các hàm tích hợp sẵn, cách tạo bảng tổng hợp, và thậm chí là macro VBA, bạn có thể thực hiện công việc này một cách nhanh chóng, chính xác và lặp lại được trong mọi dự án.
Hãy áp dụng những kiến thức và công cụ trên ngay hôm nay, từ việc phân tích doanh thu bán hàng, đánh giá kết quả học tập của học sinh, cho tới nghiên cứu y tế – bất kỳ lĩnh vực nào mà dữ liệu thống kê là chìa khóa thành công. Chúc bạn thành công trong việc khai thác sức mạnh của IQR và Excel!
Tiến sĩ Lê Thống Nhất sinh năm 1955 tại Nghệ An, là nhà giáo, nhà báo và doanh nhân trong lĩnh vực giáo dục. Ông từng giảng dạy Toán học tại Trường THPT Chuyên Phan Bội Châu và đảm nhiệm nhiều vai trò trong ngành giáo dục, từ viết sách, biên soạn tài liệu đến sáng lập các dự án hỗ trợ học tập trực tuyến.
Ông là người sáng lập BigSchool – nền tảng giáo dục trực tuyến kết nối giáo viên và học sinh cả nước. Ngoài ra, ông còn nổi tiếng với vai trò tác giả, diễn giả truyền cảm hứng và khách mời trên nhiều chương trình truyền hình, trong đó có “Ai là triệu phú” trên truyền hình VTV.
Trong suốt sự nghiệp, Tiến sĩ Lê Thống Nhất đã nhận nhiều giải thưởng giáo dục và được biết đến như một người luôn đổi mới, ứng dụng công nghệ vào dạy và học, góp phần lan tỏa tri thức và niềm say mê học tập cho thế hệ trẻ.