Hầu hết mọi người chỉ biết đến hàm AVERAGE khi cần tính trung bình trong Excel. Hàm HARMEAN ít được nhắc đến trong các hướng dẫn Excel cơ bản, và thậm chí không xuất hiện trong danh sách các hàm thông dụng. Nhưng khi làm việc với tỷ lệ, vận tốc, hoặc giá đơn vị, HARMEAN cho kết quả chính xác hơn AVERAGE đến 40% trong nhiều trường hợp.
HARMEAN Là Gì
Hàm HARMEAN tính trung bình điều hòa của một tập dữ liệu. Trung bình điều hòa là nghịch đảo của trung bình cộng các số nghịch đảo. Nghe phức tạp nhưng công thức đơn giản.
Cú pháp:
=HARMEAN(number1, [number2], ...)Trong đó:
- number1: Bắt buộc. Số đầu tiên hoặc vùng ô chứa số
- number2, …: Tùy chọn. Tối đa 255 đối số hoặc vùng ô
Cách hoạt động: Excel lấy nghịch đảo của từng số (1/số), tính trung bình cộng của các nghịch đảo này, rồi lấy nghịch đảo của kết quả đó.
Ví dụ tính toán: Với các số 10, 15, 20:
- Nghịch đảo: 1/10 = 0.1, 1/15 = 0.067, 1/20 = 0.05
- Trung bình các nghịch đảo: (0.1 + 0.067 + 0.05) / 3 = 0.072
- Trung bình điều hòa: 1 / 0.072 = 13.85
Công thức trong Excel:
=HARMEAN(10, 15, 20)Kết quả: 13.85
Lưu ý quan trọng: Nếu bất kỳ giá trị nào trong dãy số nhỏ hơn hoặc bằng 0, hàm sẽ trả về lỗi NUM!. HARMEAN chỉ làm việc với số dương.
Khi Nào Dùng HARMEAN Thay Vì AVERAGE
Sự khác biệt giữa HARMEAN và AVERAGE nằm ở cách chúng xử lý dữ liệu. AVERAGE phù hợp khi tính trung bình dựa trên thời gian. HARMEAN chính xác hơn khi tính trung bình dựa trên khoảng cách hoặc đơn vị.
Ví dụ minh họa rõ nhất:
Bạn lái xe từ điểm A đến điểm B cách nhau 60 km với tốc độ 60 km/h, sau đó quay lại A với tốc độ 30 km/h. Vận tốc trung bình cho toàn bộ hành trình là bao nhiêu?
Tính bằng AVERAGE:
=(60 + 30) / 2 = 45 km/hTính bằng HARMEAN:
=HARMEAN(60, 30) = 40 km/hKiểm chứng:
- Đoạn đi: 60 km với 60 km/h mất 1 giờ
- Đoạn về: 60 km với 30 km/h mất 2 giờ
- Tổng: 120 km trong 3 giờ = 40 km/h
HARMEAN cho kết quả đúng vì chuyến đi có cùng khoảng cách nhưng khác thời gian. AVERAGE sai 12.5% so với thực tế.
Quy tắc đơn giản:
- Dùng AVERAGE khi: Đi với mỗi tốc độ trong cùng một khoảng thời gian
- Dùng HARMEAN khi: Đi cùng khoảng cách với các tốc độ khác nhau
Trung bình điều hòa luôn nhỏ hơn hoặc bằng trung bình nhân, và trung bình nhân luôn nhỏ hơn hoặc bằng trung bình cộng. Công thức: HARMEAN ≤ GEOMEAN ≤ AVERAGE.
Ứng Dụng 1: Tính Vận Tốc Trung Bình
Đây là trường hợp phổ biến nhất cần dùng HARMEAN. Khi tính vận tốc trung bình cho các chặng đường có cùng khoảng cách, HARMEAN luôn chính xác hơn AVERAGE.
Tình huống thực tế: Bạn giao hàng cho 3 địa điểm, mỗi địa điểm cách kho 10 km:
- Chuyến 1: Đường trống, đi được 60 km/h
- Chuyến 2: Kẹt xe nhẹ, chỉ đi được 40 km/h
- Chuyến 3: Kẹt xe nặng, chỉ đi được 20 km/h
Bảng tính vận tốc trung bình:
| Chuyến | Khoảng cách | Tốc độ | Thời gian |
|---|---|---|---|
| 1 | 10 km | 60 km/h | 10 phút |
| 2 | 10 km | 40 km/h | 15 phút |
| 3 | 10 km | 20 km/h | 30 phút |
Công thức trong Excel:
=HARMEAN(60, 40, 20)Kết quả: 32.73 km/h
So sánh với AVERAGE:
=AVERAGE(60, 40, 20)Kết quả: 40 km/h (sai 22%)
Kiểm chứng: Tổng khoảng cách: 30 km Tổng thời gian: 55 phút = 0.917 giờ Vận tốc thực: 30 / 0.917 = 32.73 km/h
HARMEAN chính xác tuyệt đối. AVERAGE báo cao hơn 7.27 km/h so với thực tế.
Ứng Dụng 2: Hiệu Suất Nhiên Liệu
Khi tính mức tiêu thụ nhiên liệu trung bình của xe, đơn vị đo quan trọng quyết định dùng hàm nào.
Trường hợp 1: Đơn vị km/lít (dùng HARMEAN)
Xe của bạn có 3 mức tiêu thụ:
- Đường cao tốc: 20 km/lít
- Đường thành phố: 12 km/lít
- Kẹt xe: 8 km/lít
Nếu đi cùng quãng đường ở mỗi điều kiện:
=HARMEAN(20, 12, 8)Kết quả: 11.43 km/lít
Trường hợp 2: Đơn vị lít/100km (dùng AVERAGE)
Cùng xe đó với đơn vị khác:
- Đường cao tốc: 5 lít/100km
- Đường thành phố: 8.33 lít/100km
- Kẹt xe: 12.5 lít/100km
=AVERAGE(5, 8.33, 12.5)Kết quả: 8.61 lít/100km
Lý do: Hai đơn vị này là nghịch đảo của nhau. Khi một đơn vị dùng HARMEAN thì đơn vị kia dùng AVERAGE. Tiêu chuẩn CAFE của Mỹ sử dụng HARMEAN để tính tiêu chuẩn nhiên liệu của các hãng xe.
Ứng Dụng 3: Tỷ Lệ Công Việc
HARMEAN hữu ích khi tính thời gian hoàn thành công việc có nhiều người với năng suất khác nhau.
Ví dụ: Hai nhân viên làm cùng một công việc:
- Nhân viên A: Hoàn thành trong 4 giờ
- Nhân viên B: Hoàn thành trong 6 giờ
Nếu cùng làm chung, thời gian trung bình để hoàn thành là bao nhiêu?
=HARMEAN(4, 6)Kết quả: 4.8 giờ
Công thức thực tế trong Excel:
Tại ô D2:
=HARMEAN(B2:C2)Trong đó B2 và C2 chứa thời gian hoàn thành của từng người.
Ứng dụng mở rộng: Khi có 5 nhân viên hoàn thành báo cáo với thời gian lần lượt là 2, 3, 4, 5, 6 giờ:
=HARMEAN(2, 3, 4, 5, 6)Kết quả: 3.41 giờ
Đây là thời gian trung bình chính xác nếu phân công đều công việc cho cả nhóm.
Ứng Dụng 4: Tỷ Lệ Tài Chính
Trong tài chính, HARMEAN được dùng để tính tỷ lệ P/E trung bình của danh mục đầu tư.
Tại sao không dùng AVERAGE: Giả sử bạn đầu tư 1 triệu đồng vào mỗi cổ phiếu:
- Cổ phiếu A: P/E = 10
- Cổ phiếu B: P/E = 20
- Cổ phiếu C: P/E = 50
Dùng AVERAGE:
=(10 + 20 + 50) / 3 = 26.67Dùng HARMEAN:
=HARMEAN(10, 20, 50) = 17.65Kết quả thực tế: Tổng đầu tư: 3 triệu Tổng thu nhập (earnings): 100,000 + 50,000 + 20,000 = 170,000 P/E thực: 3,000,000 / 170,000 = 17.65
HARMEAN chính xác. AVERAGE sai 51%.
Các quỹ đầu tư chuyên nghiệp luôn dùng HARMEAN để tính P/E của danh mục vì nó phản ánh đúng khả năng sinh lời trên vốn đầu tư.
Khi Nào Dùng HARMEAN
HARMEAN là lựa chọn đúng khi làm việc với:
Vận tốc và tốc độ: Cùng khoảng cách, khác tốc độ Hiệu suất nhiên liệu: Đơn vị km/lít hoặc mpg Tỷ lệ công việc: Thời gian hoàn thành khác nhau cho cùng khối lượng Giá đơn vị: Đơn giá khác nhau cho cùng số lượng Tỷ lệ tài chính: P/E, P/B và các multiples khác
AVERAGE phù hợp khi dữ liệu dựa trên thời gian hoặc không liên quan đến tỷ lệ nghịch đảo.
Hàm HARMEAN có sẵn từ Excel 2007 trở đi, bao gồm tất cả phiên bản Excel 365. Nó hoạt động giống nhau trên Windows và Mac. Đối với các tập dữ liệu lớn hơn 255 giá trị, có thể sử dụng vùng ô thay vì liệt kê từng số.
