Có những hàm Excel mọi người bỏ qua chỉ vì tên nghe xa lạ. Hàm SIGN là một trong số đó. Trong khi người dùng tạo công thức IF lồng nhau phức tạp để phân loại số dương âm, hàm SIGN giải quyết vấn đề trong một dòng đơn giản. Tôi đã dùng SIGN để xử lý bảng công nợ 200 dòng chỉ trong 3 phút thay vì 20 phút với IF lồng.
Hàm SIGN Là Gì
Hàm SIGN xác định dấu của một số và trả về ba giá trị có thể: 1 cho số dương, -1 cho số âm, và 0 cho số không. Đây là hàm toán học đơn giản nhưng cực kỳ hữu ích trong việc phân loại dữ liệu tài chính.
Cú pháp:
=SIGN(number)Trong đó:
number: Giá trị số cần xác định dấu, có thể là số trực tiếp, tham chiếu ô, hoặc kết quả của phép tính
Hàm này có sẵn trong tất cả phiên bản Excel từ Excel 2007 trở lên, bao gồm Excel 2013, 2016, 2019, 2021 và Microsoft 365.
Ví Dụ Cơ Bản Với Hàm SIGN
Cách đơn giản nhất để hiểu hàm SIGN là thử với các số cụ thể.
Thử nghiệm trực tiếp:
=SIGN(150)trả về 1 vì 150 là số dương=SIGN(-75)trả về -1 vì -75 là số âm=SIGN(0)trả về 0 vì đó là số không
Áp dụng với tham chiếu ô:
Giả sử bạn có dữ liệu trong cột A từ A2 đến A6:
| Ô | Giá trị | Công thức | Kết quả |
|---|---|---|---|
| A2 | 8500 | =SIGN(A2) | 1 |
| A3 | -3200 | =SIGN(A3) | -1 |
| A4 | 0 | =SIGN(A4) | 0 |
| A5 | 150.75 | =SIGN(A5) | 1 |
| A6 | -0.5 | =SIGN(A6) | -1 |
Cách thực hiện:
- Nhập công thức
=SIGN(A2)vào ô B2 - Nhấn Enter để xem kết quả
- Kéo fill handle (dấu + ở góc dưới phải ô) xuống ô B6 để sao chép công thức
Hàm SIGN chỉ hoạt động với giá trị số. Nếu ô chứa văn bản, hàm sẽ trả về lỗi #VALUE!.
Kết Hợp SIGN Với IF Để Phân Loại Tự Động
Sức mạnh thực sự của SIGN xuất hiện khi kết hợp với hàm IF để tạo nhãn mô tả.
Ví dụ quản lý công nợ:
Bạn có bảng theo dõi với cột F là “Số tiền đã trả” và cột G là “Tổng tiền phải trả”. Cần xác định tình trạng thanh toán của khách hàng.
Công thức trong cột H:
=IF(SIGN(G2-F2)<0,"Còn nợ",IF(SIGN(G2-F2)=0,"Đã trả xong","Còn dư tiền"))Phân tích công thức:
G2-F2tính chênh lệch giữa tổng tiền và số đã trảSIGN(G2-F2)trả về dấu của chênh lệch- Nếu kết quả < 0 (âm): khách hàng còn nợ
- Nếu kết quả = 0: đã thanh toán đủ
- Nếu kết quả > 0 (dương): khách hàng trả thừa
Kết quả thực tế:
| Đã trả | Phải trả | Chênh lệch | SIGN | Kết quả |
|---|---|---|---|---|
| 5,000,000 | 8,000,000 | -3,000,000 | -1 | Còn nợ |
| 8,000,000 | 8,000,000 | 0 | 0 | Đã trả xong |
| 9,000,000 | 8,000,000 | 1,000,000 | 1 | Còn dư tiền |
Bảng công nợ 200 khách hàng của tôi được xử lý hoàn toàn tự động với công thức này. Mỗi khi cập nhật số tiền đã trả, trạng thái thay đổi ngay lập tức.
So Sánh Với Phương Pháp IF Truyền Thống
Nhiều người vẫn dùng IF thuần để kiểm tra số dương âm mà không biết SIGN tối ưu hơn.
Cách cũ với IF lồng nhau:
=IF(A2>0,"Dương",IF(A2<0,"Âm","Bằng không"))Cách mới với SIGN:
=IF(SIGN(A2)=1,"Dương",IF(SIGN(A2)=-1,"Âm","Bằng không"))Lợi thế của SIGN:
- Rõ ràng hơn: SIGN trả về giá trị chuẩn (-1, 0, 1) dễ kiểm tra
- Linh hoạt hơn: Có thể dùng SIGN trong phép tính phức tạp
- Ít lỗi hơn: Không cần nhớ toán tử so sánh cho từng điều kiện
Ứng dụng nâng cao:
Tính tổng có điều kiện dựa trên dấu:
=SUMIF(B2:B100,">0") + SUMPRODUCT((SIGN(A2:A100)=1)*C2:C100)Công thức này tính tổng các giá trị dương trong cột A nhân với trọng số tương ứng trong cột C.
Xử Lý Lỗi Với Hàm SIGN
Hàm SIGN trả về lỗi #VALUE! khi đối số không phải là số.
Tình huống phổ biến:
- Ô chứa văn bản: “ABC”, “N/A”, “Pending”
- Ô trống nhưng có công thức lỗi
- Ô có khoảng trắng nhìn như trống nhưng thực chất chứa ký tự
Giải pháp phòng ngừa:
Dùng IFERROR để xử lý:
=IFERROR(SIGN(A2),"Không hợp lệ")Hoặc kiểm tra trước với ISNUMBER:
=IF(ISNUMBER(A2),SIGN(A2),"")Trường hợp đặc biệt với số 0:
Excel coi cả 0 và -0 đều trả về 0 với hàm SIGN. Trong hầu hết trường hợp thực tế, điều này không gây vấn đề. Tuy nhiên nếu cần phân biệt 0 dương và 0 âm (hiếm gặp trong kế toán), cần dùng công thức phức tạp hơn.
Ứng Dụng Thực Tế Khác
1. Phân loại xu hướng giá:
Với cột A là giá tháng trước, cột B là giá tháng này:
=IF(SIGN(B2-A2)=1,"Tăng giá",IF(SIGN(B2-A2)=-1,"Giảm giá","Không đổi"))2. Kiểm tra biên độ dao động:
Xác định hướng thay đổi lớn của dữ liệu:
=SIGN(MAX(A2:A100)-MIN(A2:A100))Luôn trả về 1 nếu có dao động, 0 nếu tất cả giá trị giống nhau.
3. Tính chỉ số tăng trưởng:
=SIGN((B2-A2)/A2)Trả về 1 nếu tăng, -1 nếu giảm, hữu ích trong báo cáo tài chính.
4. Lọc giao dịch thu chi:
Dùng với FILTER (Excel 365):
=FILTER(A2:C100,SIGN(C2:C100)=-1)Lọc tất cả dòng có giá trị âm trong cột C (chi tiêu).
Kết Hợp SIGN Với Các Hàm Khác
SIGN trở nên mạnh mẽ khi kết hợp trong công thức phức tạp.
Với SUMPRODUCT:
=SUMPRODUCT((SIGN(A2:A100)=1)*B2:B100)Tính tổng cột B chỉ khi giá trị tương ứng cột A dương.
Với COUNTIF:
=COUNTIF(B2:B100,"="&SIGN(MAX(A2:A100)))Đếm số lần xuất hiện giá trị có cùng dấu với giá trị lớn nhất.
Với AVERAGE:
=AVERAGEIF(A2:A100,"<0")/AVERAGEIF(A2:A100,">0")Kết hợp với SIGN để tính tỷ lệ trung bình âm/dương:
=SUMPRODUCT((SIGN(A2:A100)=-1)*A2:A100)/SUMPRODUCT((SIGN(A2:A100)=1)*A2:A100)Hạn Chế Và Lưu Ý
Hàm SIGN không xử lý được giá trị không phải số. Nếu cột dữ liệu có khả năng chứa văn bản hoặc lỗi, luôn bọc SIGN trong IFERROR.
Với dữ liệu ngày tháng:
Excel lưu ngày dưới dạng số (serial number), nên SIGN vẫn hoạt động nhưng kết quả luôn là 1 vì ngày sau 1900 đều dương. Không nên dùng SIGN trực tiếp với ngày tháng mà chỉ dùng với phép trừ ngày.
Tương thích:
Hàm SIGN có sẵn từ Excel 2007. Nếu file cần mở trên Excel 2003 hoặc cũ hơn, hàm sẽ không hoạt động. Trường hợp này, dùng IF thuần là lựa chọn an toàn hơn cho tương thích ngược.
Bắt Đầu Với Hàm SIGN
Thử ngay với dữ liệu đơn giản trước khi áp dụng vào bảng tính thực tế. Tạo cột test với 5-10 giá trị âm, dương, và không, sau đó thực hành công thức SIGN kết hợp IF. Sau khi quen, chuyển sang ứng dụng phức tạp hơn như công nợ hoặc phân tích xu hướng. Hàm SIGN đặc biệt hữu ích trong Excel 365 khi kết hợp với các hàm dynamic array như FILTER và SORT.
