Hàm Excel
Chia nhóm theo đúng bộ nhóm hàm của Microsoft. Bấm vào một hàm để xem cú pháp, từng đối số, ví dụ có kết quả và bẫy hay dính.
Thử:
Tương thích (tên hàm đời cũ)
- RANKNên biếtXếp hạng một số trong dãy. Đây là TÊN CŨ của RANK.EQ, kết quả giống hệt. Mở file cũ của công ty thấy hàm này thì cứ để nguyên, không phải sửa gì.
- CONCATENATENên biếtNối các đoạn chữ lại thành một chuỗi. Tên cũ của CONCAT. File cũ dùng hàm này vẫn chạy bình thường trên mọi bản Excel, không cần đổi.
- PERCENTILE / QUARTILENên biếtTìm mốc phân vị và tứ phân vị của một dãy số. Tên cũ của PERCENTILE.INC và QUARTILE.INC, kết quả giống hệt bản mới.
- STDEV / VARNên biếtĐộ lệch chuẩn và phương sai tính theo MẪU. Tên cũ của STDEV.S và VAR.S, công thức bên trong y hệt nhau.
- MODENên biếtTrả về giá trị xuất hiện nhiều lần nhất trong dãy. Tên cũ của MODE.SNGL, kết quả không khác.
- FORECASTNên biếtDự báo một giá trị theo đường thẳng hồi quy dựng từ dữ liệu quá khứ. Tên cũ của FORECAST.LINEAR.
- COVARHiệp phương sai giữa hai dãy số — hai đại lượng có xu hướng tăng giảm cùng chiều hay ngược chiều. Tên cũ, nay tách thành COVARIANCE.P và COVARIANCE.S.
- WEIBULL / NORMDIST / TDIST ...Cả họ hàm phân phối xác suất đời cũ: NORMDIST, NORMINV, TDIST, TINV, CHIDIST, FDIST, WEIBULL… Nay mỗi hàm có một bản tên chấm tương ứng, bản cũ vẫn chạy để file đời trước không vỡ.
Cube
- CUBEVALUELấy MỘT con số từ Data Model của file hoặc từ khối OLAP trên máy chủ. Đây không phải hàm gõ tay: nó do Excel sinh ra khi bấm PivotTable Analyze → OLAP Tools → Convert to Formulas. Dùng khi cần bố cục báo cáo TỰ DO — chèn dòng trắng, xen dòng thuyết minh, đặt hai chỉ tiêu cạnh nhau — mà số vẫn lấy từ mô hình.
- CUBEMEMBERLấy một thành phần trong khối dữ liệu — một tháng, một chi nhánh, một chỉ tiêu — để đưa vào CUBEVALUE làm lát cắt. Cũng do Convert to Formulas sinh ra chứ ít khi gõ tay.
- CUBESETDựng một TẬP thành phần theo điều kiện, ví dụ top 10 khách hàng theo doanh thu, rồi dùng CUBERANKEDMEMBER bung ra từng dòng.
- CUBESETCOUNTĐếm số phần tử trong tập do CUBESET dựng ra.
- CUBERANKEDMEMBERLấy phần tử đứng hạng thứ n trong một tập. Dùng dựng bảng xếp hạng có bố cục CỐ ĐỊNH: dòng đầu luôn là khách đứng nhất, kỳ này là ai cũng không làm xô lệch bảng.
- CUBEKPIMEMBERLấy một phần của chỉ tiêu KPI đã định nghĩa sẵn trong mô hình: giá trị thực hiện, mục tiêu, trạng thái hay xu hướng.
- CUBEMEMBERPROPERTYLấy thuộc tính đi kèm của một thành phần, ví dụ vùng miền của chi nhánh, nhóm hàng của mã hàng — thay vì phải dò sang bảng khác.
Cơ sở dữ liệu
- DSUMCộng một cột trong bảng theo bộ điều kiện viết thành một VÙNG ĐIỀU KIỆN riêng trên sheet, tức điều kiện nằm ở ô chứ không nằm trong công thức. Ngày nay SUMIFS thay được gần hết việc của nó; DSUM còn đáng dùng khi điều kiện nhiều tầng và hay đổi, vì đổi điều kiện là sửa ô chứ không phải sửa lại công thức.
- DCOUNT / DCOUNTAĐếm theo vùng điều kiện: DCOUNT đếm ô chứa SỐ, DCOUNTA đếm mọi ô không rỗng. Vai trò giống COUNTIFS, khác ở chỗ điều kiện nằm thành bảng ngoài nên sửa được mà không đụng vào công thức.
- DAVERAGETrung bình một cột trong phạm vi các dòng thoả vùng điều kiện. Tương đương AVERAGEIFS, khác ở chỗ điều kiện viết thành bảng ngoài nên đọc được bằng mắt và sửa được mà không chạm công thức.
- DMAX / DMINGiá trị lớn nhất / nhỏ nhất của một cột trong phạm vi các dòng thoả vùng điều kiện. Dùng để tìm hoá đơn lớn nhất của một khách, ngày phát sinh sớm nhất của một nhóm công nợ.
- DGETLấy ra giá trị của MỘT bản ghi duy nhất thoả điều kiện. Nét riêng không hàm tra cứu nào khác có: khớp nhiều hơn một dòng thì nó BÁO LỖI thay vì lặng lẽ lấy dòng đầu tiên. Vì thế DGET đáng dùng như công cụ phát hiện trùng hơn là một hàm tra cứu.
Ngày & giờ
- TODAY / NOWPhổ biếnTODAY() cho ra ngày hôm nay, NOW() cho ra cả ngày lẫn giờ, lấy theo đồng hồ máy tính. Đây là cách đặt mốc ĐỘNG cho cột tuổi nợ, số ngày quá hạn, hàng cận date — thay cho việc gõ tay ngày hôm nay rồi hôm sau lại phải sửa cả bảng.
- DATEPhổ biếnGhép ba số năm, tháng, ngày rời rạc thành một ngày THẬT mà Excel tính toán được. Đây là cách dựng ngày an toàn nhất: dữ liệu về ở ba cột riêng, hoặc cần một mốc cố định viết thẳng trong công thức mà không phụ thuộc định dạng ngày của máy.
- DATEVALUEĐổi một chuỗi CHỮ trông giống ngày thành ngày thật để tính toán được. Đây là hàm cứu dữ liệu dán từ web, từ PDF hoặc kết xuất từ phần mềm khác — thứ nhìn y hệt ngày nhưng Excel đang coi là văn bản.
- DAY / MONTH / YEARPhổ biếnTách một giá trị ngày ra thành ba số rời: ngày trong tháng, số tháng, số năm. Đây là bộ hàm dựng cột kỳ báo cáo để gom Pivot, lọc bảng kê thuế theo tháng, hoặc viết điều kiện cho SUMIFS.
- HOUR / MINUTE / SECONDTách phần giờ, phút, giây ra khỏi một giá trị thời gian. Dùng để đọc dữ liệu máy chấm công, log ra vào cổng, phân loại ca làm việc.
- TIMEGhép ba số giờ, phút, giây thành một giá trị thời gian thật. Dùng để dựng mốc ca làm việc ngay trong công thức, hoặc cộng thêm một khoảng thời gian vào mốc có sẵn.
- WEEKDAYNên biếtCho biết một ngày rơi vào thứ mấy trong tuần, trả về dạng SỐ. Dùng để đánh dấu cuối tuần trong bảng chấm công, xếp lịch trực, hoặc tô màu ngày nghỉ bằng định dạng có điều kiện.
- WEEKNUM / ISOWEEKNUMNên biếtCho biết một ngày thuộc tuần thứ mấy trong năm. WEEKNUM đếm theo quy ước tuần chứa ngày 1/1 là tuần 1, ISOWEEKNUM đếm theo chuẩn quốc tế ISO. Dùng cho báo cáo tuần và bảng dòng tiền 13 tuần.
- EOMONTHPhổ biếnTrả về ngày CUỐI THÁNG sau khi cộng hoặc trừ n tháng từ một mốc. Đây là hàm dựng kỳ kế toán: ngày chốt sổ, mốc cuối kỳ cho SUMIFS, hạn thanh toán vào cuối tháng.
- EDATEPhổ biếnTrả về ngày CÙNG SỐ trong tháng sau khi cộng hoặc trừ n tháng. Dùng cho lịch trả nợ hàng tháng, ngày tái tục hợp đồng, hạn thanh toán kiểu sau 3 tháng, và mốc cùng kỳ năm trước.
- DATEDIFPhổ biếnTính khoảng cách giữa hai ngày theo đơn vị tự chọn: năm, tháng hoặc ngày. Đây là hàm tính thâm niên, tính tuổi, tính số tháng còn lại của hợp đồng — những chỗ cần số kỳ TRÒN chứ không cần số ngày.
- DAYSNên biếtTrả về số ngày giữa hai mốc. Cùng kết quả với phép trừ hai ô ngày, nhưng đọc rõ nghĩa hơn khi nằm trong một công thức dài và nhận được cả ngày viết dạng chuỗi.
- NETWORKDAYSNên biếtĐếm số ngày LÀM VIỆC giữa hai mốc, tự bỏ thứ Bảy, Chủ nhật và bỏ thêm danh sách lễ nếu có khai. Dùng đo thời gian xử lý hồ sơ, tính tuổi nợ theo ngày làm việc, đo tiến độ so với cam kết.
- NETWORKDAYS.INTLNhư NETWORKDAYS nhưng cho tự chọn ngày nào trong tuần là ngày nghỉ. Cần đến khi công ty làm cả sáng thứ Bảy, nhà máy chỉ nghỉ Chủ nhật, hoặc bộ phận trực xoay ca nghỉ vào ngày khác.
- WORKDAY / WORKDAY.INTLNên biếtCộng n ngày LÀM VIỆC vào một mốc để ra ngày đến hạn: hạn giao hàng, hạn thanh toán, hạn trả hồ sơ. WORKDAY bỏ thứ Bảy và Chủ nhật; WORKDAY.INTL thêm chỗ tự chọn ngày nghỉ trong tuần.
- YEARFRACNên biếtQuy khoảng cách giữa hai ngày ra SỐ NĂM có phần lẻ. Dùng để tính lãi theo số ngày thực tế, phân bổ chi phí trả trước, và tính khấu hao cho phần năm không tròn.
Kỹ thuật
- CONVERTĐổi đơn vị đo: kg sang pound, mét sang feet, giờ sang phút, lít sang gallon. Dùng khi bảng vật tư trộn nhiều đơn vị, đỡ phải nhớ và gõ tay hệ số quy đổi.
- DEC2BIN / BIN2DECĐổi qua lại giữa hệ 10 và hệ 2. Cùng họ còn có DEC2HEX, HEX2DEC, DEC2OCT, OCT2DEC. Trong nghiệp vụ văn phòng hầu như chỉ gặp khi phải đọc cột mã trạng thái do phần mềm khác xuất ra.
- DELTATrả về 1 nếu hai số BẰNG NHAU, 0 nếu khác. Cộng cả cột DELTA lên là ra ngay số dòng khớp nhau khi đối chiếu hai bảng.
- GESTEPTrả về 1 nếu số lớn hơn hoặc bằng ngưỡng, 0 nếu nhỏ hơn. Đếm nhanh số lần vượt ngưỡng mà không phải viết IF.
- BITAND / BITOR / BITXORPhép toán trên từng bit của số nguyên. Hiếm dùng trong kế toán, hay gặp khi phần mềm khác xuất ra một cột "cờ trạng thái" gộp nhiều trạng thái vào chung một con số.
- ERF / ERFCHàm sai số và hàm sai số bù, dùng trong thống kê và toán kỹ thuật. Nghiệp vụ kế toán không có việc gì cho hai hàm này.
- IMSUM / IMPRODUCT ...Cả họ hàm số phức: IMSUM, IMSUB, IMPRODUCT, IMDIV, IMABS, IMREAL, IMAGINARY, COMPLEX… Dùng trong kỹ thuật điện và cơ học, không dùng trong nghiệp vụ văn phòng.
- BESSELI / BESSELJ ...Họ hàm Bessel (BESSELI, BESSELJ, BESSELK, BESSELY) — toán kỹ thuật chuyên sâu. Excel giữ để trao đổi số liệu với phần mềm kỹ thuật, nghiệp vụ kế toán không đụng tới.
Tài chính
- PMTPhổ biếnTính số tiền phải trả ĐỀU mỗi kỳ cho một khoản vay có lãi suất cố định. Cũng dùng ngược lại: mỗi tháng phải tích luỹ bao nhiêu thì sau n kỳ đủ một mục tiêu.
- IPMT / PPMTPhổ biếnTách khoản trả đều của một kỳ ra làm hai phần: IPMT là phần LÃI, PPMT là phần GỐC. Cần để hạch toán và để biết sau n năm đã trả được bao nhiêu gốc.
- NPERNên biếtTính SỐ KỲ cần thiết: vay từng này, trả từng này mỗi tháng thì bao lâu hết nợ; hoặc mỗi tháng để dành từng này thì bao lâu đủ mục tiêu.
- RATENên biếtTính LÃI SUẤT thật của một khoản vay hoặc khoản đầu tư khi đã biết số tiền, số kỳ và số trả mỗi kỳ. Dùng để soi lãi suất thật của các gói trả góp "0%".
- PV / FVNên biếtPV quy một dòng tiền tương lai về GIÁ TRỊ HÔM NAY; FV tính một khoản hôm nay sẽ thành bao nhiêu trong tương lai. Đây là nền của mọi phép so sánh phương án tài chính.
- NPV / IRRNên biếtNPV quy cả một dòng tiền nhiều kỳ về hôm nay theo một suất chiết khấu; IRR tìm ngược lại suất chiết khấu làm NPV bằng 0. Hai con số dùng để quyết định có làm dự án hay không.
- XNPV / XIRRNên biếtBản dùng NGÀY THẬT của NPV và IRR: mỗi dòng tiền gắn với một ngày cụ thể nên không cần các kỳ cách đều. Đây mới là cặp hàm đúng cho góp vốn và thoái vốn rải rác trong năm.
- CUMIPMT / CUMPRINCNên biếtCộng dồn phần LÃI (CUMIPMT) hoặc phần GỐC (CUMPRINC) đã trả trong một khoảng kỳ. Trả lời nhanh câu "ba năm đầu trả hết bao nhiêu tiền lãi" mà không phải dựng cả bảng.
- EFFECT / NOMINALNên biếtĐổi qua lại giữa lãi suất DANH NGHĨA (con số ghi trên hợp đồng) và lãi suất THỰC TẾ một năm sau khi tính lãi nhập gốc. Dùng để so hai gói vay có kỳ tính lãi khác nhau.
- SLN / DB / DDB / SYDPhổ biếnBốn cách tính khấu hao tài sản cố định: SLN là đường thẳng (đều mỗi kỳ), DB và DDB là số dư giảm dần (khấu hao nhanh ở kỳ đầu), SYD là tổng số thứ tự năm.
Thông tin
- ISBLANKNên biếtHỏi một ô có THẬT SỰ rỗng hay không. Rỗng ở đây nghĩa là chưa từng gõ gì vào, chứ không phải nhìn thấy trống. Đây là viên gạch đầu tiên của mọi cột soát chứng từ thiếu dữ liệu trước khi chốt sổ.
- ISERROR / ISERR / ISNANên biếtCho biết một ô có đang lỗi hay không, ba mức rộng hẹp khác nhau: ISERROR bắt mọi lỗi, ISERR bắt mọi lỗi TRỪ #N/A, ISNA chỉ bắt riêng #N/A. Dùng để ĐẾM và ĐÁNH DẤU lỗi, khác với IFERROR là để thay lỗi bằng giá trị khác.
- ISNUMBER / ISTEXT / ISLOGICALPhổ biếnCho biết nội dung trong ô là SỐ, là CHỮ hay là giá trị đúng-sai. Đây là bộ hàm chủ lực để bắt số đang bị lưu dưới dạng chữ — bệnh kinh niên của dữ liệu kết xuất từ phần mềm kế toán hoặc copy từ web về.
- ISFORMULANên biếtCho biết ô đang chứa CÔNG THỨC hay chỉ là con số gõ tay. Dùng để soát file nhiều người cùng sửa: chỗ nào lẽ ra phải tự tính mà bị gõ đè số cứng lên.
- ISODD / ISEVENCho biết một số là lẻ hay chẵn. Trong công việc văn phòng gần như chỉ dùng làm nền cho hai việc: tô màu xen kẽ dòng, và chia dữ liệu theo chu kỳ chẵn lẻ.
- NANên biếtCố tình trả về lỗi #N/A để đánh dấu chỗ chưa có dữ liệu, đừng tính. Hai chỗ dùng chính: chặn phép tính chạy trên số liệu thiếu, và cho biểu đồ đường NGẮT tại điểm chưa có số thay vì tụt xuống đáy.
- TYPE / ERROR.TYPETYPE cho biết giá trị trong ô thuộc kiểu nào, ERROR.TYPE cho biết ô đang dính LOẠI lỗi nào, cả hai trả về bằng một con số mã. Dùng khi cần phân loại lỗi để xử lý khác nhau, thay vì nuốt tất bằng IFERROR.
- CELLHỏi Excel một thông tin về ô: địa chỉ, tên file và tên sheet, mã định dạng đang áp, kiểu nội dung, độ rộng cột. Việc hay dùng nhất trong nghiệp vụ là lấy TÊN SHEET đưa lên tiêu đề báo cáo cho khỏi sửa tay mỗi tháng.
- INFOHỏi thông tin về môi trường Excel đang chạy: thư mục hiện hành, số sheet đang mở, đời Excel, hệ điều hành, và chế độ tính đang là tự động hay thủ công. Trong file nghiệp vụ chủ yếu dùng đúng một việc: cắm một ô canh chế độ tính.
- SHEET / SHEETSSHEET cho biết một sheet đứng thứ mấy trong workbook; SHEETS đếm tổng số sheet, hoặc đếm số sheet mà một tham chiếu 3 chiều đi qua. Cách dùng đáng giá nhất là làm chốt canh tham chiếu 3 chiều trong file gộp 12 tháng.
Logic
- IFPhổ biếnRẽ nhánh: điều kiện đúng thì trả về thứ này, sai thì trả về thứ kia. Là hàm nền của mọi cột phân loại, cột cảnh báo và cột kiểm tra trong một file kế toán.
- IFSNên biếtNhiều điều kiện xét nối tiếp, cái nào đúng trước thì lấy cái đó. Thay cho IF lồng IF nhiều tầng nên công thức đọc được từ trên xuống.
- AND / OR / NOTNên biếtGhép nhiều điều kiện thành một. AND đúng khi TẤT CẢ đúng, OR đúng khi CÓ MỘT đúng, NOT đảo ngược kết quả. Thường nằm bên trong IF hoặc trong quy tắc Conditional Formatting.
- XORĐúng khi số điều kiện đúng là số LẺ. Với hai điều kiện thì nghĩa là "đúng một trong hai, không phải cả hai".
- IFERRORPhổ biếnCông thức chạy ra lỗi thì trả về giá trị thay thế, không thì trả về kết quả bình thường. Dùng để bảng in ra không đầy #N/A và #DIV/0!.
- IFNANên biếtChỉ bắt riêng lỗi #N/A, các lỗi khác vẫn hiện ra để còn biết mà sửa. Đây là hàm nên dùng khi bọc VLOOKUP và XLOOKUP.
- SWITCHNên biếtSo MỘT biểu thức với nhiều giá trị cố định rồi trả về kết quả tương ứng. Gọn hơn IFS khi tất cả điều kiện đều là phép so bằng.
- LETNên biếtĐặt TÊN cho các phần tính trung gian ngay trong một công thức. Công thức dài đọc ra nghĩa thay vì một dãy ngoặc, và phần tính lặp lại chỉ chạy một lần nên nhanh hơn.
- LAMBDATự viết một hàm riêng bằng chính công thức Excel, rồi đặt tên trong Name Manager để gọi như hàm có sẵn. Cách tự viết hàm KHÔNG cần VBA, nên file vẫn là .xlsx và không vướng cảnh báo macro.
- BYROW / BYCOLChạy một phép tính cho TỪNG DÒNG (hoặc từng cột) của một vùng rồi trả về một cột (hoặc một dòng) kết quả. Thay cho việc viết công thức ở một ô rồi kéo xuống.
- MAP / REDUCE / SCANBộ ba duyệt mảng: MAP biến từng phần tử thành phần tử mới, REDUCE gộp cả mảng thành MỘT giá trị, SCAN gộp dần và trả về từng bước — chính là cột luỹ kế.
Tra cứu & tham chiếu
- VLOOKUPPhổ biếnDò một giá trị ở cột ĐẦU TIÊN của bảng rồi lấy về giá trị nằm cùng dòng, ở cột thứ mấy do mình chỉ định. Đây là hàm điền đơn giá, tên hàng, thông tin khách từ một bảng danh mục sang bảng làm việc.
- XLOOKUPPhổ biếnBản thay thế VLOOKUP và HLOOKUP: dò trong một cột rồi lấy về giá trị cùng dòng ở cột khác — cột nào cũng được, kể cả nằm bên trái. Có sẵn chỗ điền chữ khi không tìm thấy nên không cần bọc IFNA.
- HLOOKUPPhổ biếnNhư VLOOKUP nhưng dò theo DÒNG đầu tiên của bảng rồi lấy giá trị ở dòng thứ mấy bên dưới. Dùng cho bảng nằm ngang: dòng đầu là T1..T12 hoặc là các mốc năm.
- LOOKUPHàm dò đời cũ, dò trên dữ liệu ĐÃ SẮP XẾP tăng dần. Ngày nay giữ lại chủ yếu vì một mẹo: LOOKUP(2, 1/(điều_kiện), vùng) lấy được giá trị khớp CUỐI CÙNG, việc mà VLOOKUP không làm được.
- INDEXPhổ biếnTrả về ô nằm ở dòng thứ mấy, cột thứ mấy trong một vùng. Tự nó ít khi dùng một mình — ghép với MATCH thành cặp dò tìm linh hoạt hơn VLOOKUP vì không phụ thuộc số thứ tự cột.
- MATCHPhổ biếnTrả về VỊ TRÍ THỨ MẤY của một giá trị trong một cột hoặc một dòng. Con số đó thường được đưa thẳng cho INDEX, hoặc dùng để kiểm tra một mã có tồn tại trong danh mục hay không.
- XMATCHNên biếtMATCH bản mới: trả về vị trí của một giá trị, nhưng dò được từ dưới lên, khớp gần đúng theo cả hai chiều và nhận ký tự đại diện — không cần vùng phải sắp xếp trước.
- OFFSETNên biếtTrả về một ô hoặc một VÙNG nằm cách ô gốc bao nhiêu dòng, bao nhiêu cột, và rộng cao bao nhiêu. Dùng để dựng vùng tự co giãn theo số dòng dữ liệu thực tế.
- INDIRECTNên biếtBiến một chuỗi CHỮ thành tham chiếu thật. Dùng khi tên sheet hoặc địa chỉ ô được ghép từ dữ liệu trong ô khác, ví dụ gom số từ 12 sheet tháng mà không phải viết 12 công thức khác nhau.
- CHOOSENên biếtChọn phần tử thứ n trong một danh sách liệt kê thẳng trong công thức. Gọn hơn IF lồng nhiều tầng khi các lựa chọn được đánh số sẵn.
- ROW / ROWSNên biếtROW cho biết một ô nằm ở dòng thứ mấy, ROWS cho biết một vùng cao bao nhiêu dòng. Dùng để đánh số thứ tự tự chạy, và để công thức tự biết bảng dài bao nhiêu thay vì gõ số cứng.
- COLUMN / COLUMNSNên biếtCOLUMN cho biết một ô nằm ở cột thứ mấy, COLUMNS cho biết một vùng rộng bao nhiêu cột. Chủ yếu dùng để công thức tự tăng số cột khi kéo ngang, thay cho việc gõ tay 2, 3, 4 vào từng ô.
- ADDRESSGhép số dòng và số cột thành CHUỖI địa chỉ ô kiểu $B$5. Dùng khi cần chỉ cho người đọc biết số liệu nằm ở ô nào, hoặc dựng chuỗi địa chỉ cho INDIRECT gọi ra vùng.
- TRANSPOSEXoay bảng: dòng thành cột, cột thành dòng. Khác Paste Special → Transpose ở chỗ dán là chép chết một lần, còn hàm này giữ liên kết sống nên dữ liệu gốc đổi là bảng xoay đổi theo.
- HYPERLINKTạo đường dẫn bấm được bằng CÔNG THỨC, trỏ tới một ô trong chính file, tới file khác hoặc tới địa chỉ web. Dùng để dựng mục lục sheet, nút nhảy sang bảng chi tiết, hoặc cột link mở chứng từ đã scan.
- FORMULATEXTHiện CÔNG THỨC của một ô ra dưới dạng chữ thay vì hiện kết quả. Dùng để soát file người khác gửi, hoặc làm tài liệu bàn giao mà không phải chụp màn hình từng ô.
- GETPIVOTDATANên biếtLấy MỘT con số từ PivotTable theo tên chỉ tiêu và tên nhóm, thay vì trỏ vào toạ độ ô. Nhờ vậy pivot sắp lại, thêm bớt dòng thì công thức vẫn lấy đúng số.
- FILTERNên biếtLọc ra những dòng thoả điều kiện và ĐỔ KẾT QUẢ RA nhiều ô cùng lúc. Khác với nút Filter trên thanh công cụ, đây là công thức nên dữ liệu nguồn đổi là kết quả tự đổi theo.
- SORTNên biếtSắp xếp một vùng bằng CÔNG THỨC và đổ kết quả ra ô khác, giữ nguyên dữ liệu gốc. Dữ liệu nguồn đổi thì bảng đã sắp tự cập nhật, không phải bấm sắp lại.
- SORTBYNên biếtSắp một vùng theo MỘT VÙNG KHÁC, kể cả vùng đó không nằm trong kết quả. Dùng khi muốn xếp danh sách tên theo doanh số mà bảng kết quả chỉ hiện tên.
- UNIQUENên biếtLấy ra danh sách các giá trị KHÔNG TRÙNG bằng công thức. Khác nút Remove Duplicates ở chỗ dữ liệu gốc giữ nguyên và danh sách tự cập nhật khi có mã mới.
- SEQUENCENên biếtSinh một dãy số liên tiếp đổ thẳng ra nhiều ô: 1 tới 12, hay dãy ngày đầu mỗi tháng. Thay cho việc gõ tay rồi kéo fill, và dãy tự dài ra ngắn lại khi số dòng dữ liệu đổi.
- RANDARRAYSinh một BẢNG số ngẫu nhiên đổ ra nhiều ô cùng lúc, chọn được khoảng giá trị và chọn số nguyên hay số lẻ. Dùng để bốc mẫu chứng từ đi kiểm tra, hoặc dựng dữ liệu giả để thử công thức khi chưa có số thật.
- TAKE / DROPTAKE giữ lại mấy dòng hoặc mấy cột ở đầu (hoặc ở cuối) một bảng, DROP làm ngược lại là bỏ đi đúng ngần ấy. Dùng để cắt dòng tiêu đề, cắt dòng Cộng ở cuối bảng kê, hoặc lấy top mấy dòng sau khi đã sắp xếp.
- CHOOSECOLS / CHOOSEROWSRút ra đúng những cột (hoặc những dòng) mình cần từ một bảng rộng, theo thứ tự mình muốn. Dùng để dựng bảng trình bày chỉ gồm vài cột từ file gốc 30 cột, hoặc đảo thứ tự cột cho khớp mẫu phải nộp.
- HSTACK / VSTACKGhép nhiều vùng thành một bảng: VSTACK xếp CHỒNG theo chiều dọc, HSTACK nối cạnh nhau theo chiều ngang. Đây là cách gộp 12 sheet tháng thành một bảng tổng bằng công thức, không cần Power Query.
- TOCOL / TOROWDuỗi một bảng nhiều dòng nhiều cột thành MỘT cột (TOCOL) hoặc MỘT hàng (TOROW). Dùng để gom số liệu nằm rải ở nhiều cột thành một danh sách liền mạch, hoặc dựng danh mục duy nhất từ mấy cột mã rời nhau.
- GROUPBY / PIVOTBYGom nhóm và tính tổng bằng CÔNG THỨC: GROUPBY ra bảng dọc kiểu một cột nhóm một cột số, PIVOTBY ra bảng chéo có nhóm ở dòng và nhóm ở cột. Làm thay PivotTable cho những báo cáo cần tự cập nhật, không phải nhớ bấm Refresh.
Toán & lượng giác
- SUMPhổ biếnCộng tất cả các số trong một hoặc nhiều vùng. Hàm dùng nhiều nhất trong Excel, và cũng là hàm bị dùng sai nhiều nhất vì người ta tin kết quả của nó mà không kiểm lại vùng.
- SUMIFPhổ biếnCộng những dòng thoả MỘT điều kiện. Thay cho việc lọc ra rồi bấm máy tính từng nhóm: doanh thu của một chi nhánh, công nợ của một khách, chi phí của một tài khoản.
- SUMIFSPhổ biếnCộng những dòng thoả ĐỒNG THỜI nhiều điều kiện: vừa đúng chi nhánh, vừa đúng nhóm hàng, vừa nằm trong khoảng ngày. Đây là hàm dựng báo cáo doanh thu và bảng tổng hợp chi phí theo tháng, thay cho việc lọc tay rồi chép số sang.
- SUMPRODUCTPhổ biếnNhân từng cặp phần tử của các vùng rồi cộng tất cả lại. Việc thường ngày: tính thành tiền của cả bảng bằng MỘT công thức thay vì tạo cột phụ đơn giá nhân số lượng. Ngoài ra nó còn là công cụ cộng và đếm có điều kiện linh hoạt hơn hẳn SUMIFS, làm được cả điều kiện HOẶC và điều kiện tính toán ngay tại chỗ.
- PRODUCTNhân tất cả các số trong vùng với nhau. Dùng khi phải nhân dây chuyền nhiều hệ số: chiết khấu chồng nhiều bậc, quy đổi qua nhiều đơn vị, gộp tỷ lệ tăng trưởng của nhiều năm.
- ROUND / ROUNDUP / ROUNDDOWNPhổ biếnLàm tròn một số về n chữ số thập phân: ROUND theo quy tắc thông thường, ROUNDUP luôn đẩy ra xa 0, ROUNDDOWN luôn kéo về gần 0. Đây là hàm quyết định con số trên chứng từ, vì Excel tính bằng giá trị thật chứ không bằng con số đang hiện trên màn hình.
- MROUNDNên biếtLàm tròn về BỘI SỐ gần nhất của một con số do mình đặt, không phải về chữ số thập phân. Dùng để quy tiền về bội 500 đồng cho khỏi phải trả tiền lẻ, quy giờ công về bội 15 phút, quy số lượng về bội của quy cách đóng gói.
- CEILING / FLOORNên biếtÉp một số LÊN hoặc XUỐNG tới bội số chỉ định. CEILING luôn đẩy lên, FLOOR luôn kéo xuống. Khác MROUND ở chỗ chiều làm tròn do mình quyết chứ không do khoảng cách gần xa.
- INT / TRUNCNên biếtBỏ phần thập phân của một số. INT lấy phần nguyên bằng cách làm tròn xuống; TRUNC cắt cụt phần lẻ và còn cắt được tới n chữ số thập phân. Hai việc hay dùng nhất: tách phần NGÀY ra khỏi ô ngày giờ, và lấy số nguyên đơn vị khi quy đổi.
- MODNên biếtTrả về phần DƯ của phép chia. Ba việc dùng nhiều: tô màu dòng chẵn lẻ bằng định dạng có điều kiện, tách phần giờ ra khỏi ô ngày giờ, và tính số lượng lẻ còn lại sau khi đã chia hết thùng.
- ABSNên biếtBỏ dấu âm, lấy độ lớn của một số. Dùng nhiều nhất khi so sánh chênh lệch: chỉ quan tâm lệch bao nhiêu chứ không quan tâm lệch về bên nào, ví dụ đối chiếu công nợ hai bên hoặc soát số dư sổ với sao kê ngân hàng.
- POWER / SQRTPOWER nâng một số lên luỹ thừa, SQRT lấy căn bậc hai. Việc dùng nhiều nhất trong tài chính là tính tốc độ tăng trưởng bình quân năm và quy đổi lãi suất giữa các kỳ ghép lãi.
- EXP / LN / LOGBộ hàm mũ và lô-ga: EXP là e mũ n, LN là lô-ga tự nhiên, LOG là lô-ga theo cơ số tự chọn. Trong công việc kế toán tài chính chỉ dùng ở vài chỗ hẹp: tính lãi ghép liên tục, và giải ngược ra số kỳ cần thiết để một khoản tiền tăng tới mức mong muốn.
- SIGNCho biết một số là dương, âm hay bằng 0: trả về 1, -1 hoặc 0. Dùng để phân loại nhanh cột chênh lệch thành nhóm thừa, nhóm thiếu và nhóm khớp mà không phải viết IF lồng nhau.
- RAND / RANDBETWEENSinh số ngẫu nhiên: RAND cho số lẻ từ 0 tới dưới 1, RANDBETWEEN cho số nguyên trong khoảng hai đầu mút. Dùng để dựng dữ liệu mẫu cho lớp học, chọn mẫu kiểm tra chứng từ, hoặc chạy thử kịch bản.
- SUBTOTALPhổ biếnTính trên một vùng nhưng BỎ QUA những dòng đang bị Filter ẩn đi, nên con số ở dòng tổng luôn khớp với thứ đang hiện trên màn hình. Một mã số ở đối số đầu quyết định đang cộng, đếm, lấy trung bình hay lấy giá trị lớn nhất.
- AGGREGATENên biếtBản mạnh hơn của SUBTOTAL: ngoài việc bỏ qua dòng ẩn, nó còn BỎ QUA được cả ô LỖI nằm trong vùng, và làm được thêm những phép mà SUBTOTAL không có như lấy giá trị lớn thứ k, lấy trung vị, lấy phân vị. Nhờ vậy dòng tổng vẫn ra số ngay cả khi trong cột còn vài ô #N/A hoặc #DIV/0! chưa xử lý xong.
Thống kê
- COUNT / COUNTAPhổ biếnĐếm xem trong vùng có bao nhiêu ô có dữ liệu. Đây là cách kiểm tra nhanh một bảng nhập liệu đã đủ dòng chưa, thay cho việc kéo chuột chọn cả cột rồi nhìn con số dưới thanh trạng thái.
- COUNTBLANKNên biếtĐếm số ô còn để trống trong một vùng. Dùng làm chốt kiểm tra trước khi chốt sổ hoặc trước khi gửi biểu mẫu đi: còn ô nào chưa điền thì biết ngay bằng một con số, không phải rà mắt từng dòng.
- COUNTIF / COUNTIFSPhổ biếnĐếm số dòng thoả điều kiện: COUNTIF một điều kiện, COUNTIFS nhiều điều kiện cùng lúc. Thay hẳn thao tác bật Filter, chọn giá trị rồi nhìn con số dưới thanh trạng thái và chép tay sang báo cáo — số ở đây tự cập nhật khi dữ liệu đổi.
- AVERAGE / AVERAGEIF / AVERAGEIFSPhổ biếnTính trung bình cộng, có thể giới hạn trong phần dữ liệu thoả điều kiện. AVERAGEIF nhận một điều kiện, AVERAGEIFS nhận nhiều điều kiện cùng lúc. Thay việc lọc ra rồi lấy tổng chia số dòng bằng tay.
- MEDIANNên biếtTrả về con số nằm chính giữa dãy khi đem sắp theo thứ tự: một nửa số liệu nằm dưới nó, một nửa nằm trên. Thay việc sắp xếp cả cột rồi dò ngón tay xuống đúng dòng giữa bảng.
- MODE.SNGLTrả về giá trị xuất hiện nhiều lần nhất trong dãy. Dùng để biết mức giá bán nào hay áp dụng nhất, số lượng đặt hàng nào phổ biến nhất, hay mức chiết khấu nào thực tế đang chạy nhiều nhất.
- MAX / MINPhổ biếnLấy số lớn nhất hoặc nhỏ nhất trong vùng. Ngoài việc tìm giá trị đỉnh đáy, hai hàm này còn một công dụng rất hay dùng mà ít người để ý: kẹp một kết quả trong khoảng cho phép, chặn không cho ra số âm hoặc chặn trần một mức duyệt chi.
- MAXIFS / MINIFSNên biếtLấy số lớn nhất hoặc nhỏ nhất trong PHẦN dữ liệu thoả điều kiện. Thay hẳn công thức mảng MAX(IF(...)) phải bấm Ctrl+Shift+Enter mà người sau mở file không hiểu nổi.
- LARGE / SMALLNên biếtLấy số lớn thứ k hoặc nhỏ thứ k trong một dãy. Đây là hàm dựng bảng Top 10 khách hàng, Top 5 mặt hàng bán chậm, danh sách năm khoản nợ lớn nhất — làm được ngay trên báo cáo mà không phải đụng vào thứ tự của dữ liệu gốc.
- RANK.EQ / RANK.AVGNên biếtCho biết một con số đứng thứ mấy trong cả dãy. Thay việc sắp xếp bảng rồi đánh số thứ tự bằng tay — dữ liệu sửa tới đâu, hạng tự chạy lại tới đó mà thứ tự dòng trong bảng vẫn giữ nguyên.
- PERCENTILE.INC / QUARTILE.INCCắt một dãy số thành các mốc theo tỷ lệ. PERCENTILE.INC trả về mốc mà dưới nó có bao nhiêu phần trăm số liệu; QUARTILE.INC là bản rút gọn chỉ có bốn mốc 25%, 50%, 75%. Dùng để chia nhóm khách hàng, đặt ngưỡng thưởng, hoặc khoanh ra phần dữ liệu bất thường.
- STDEV.S / STDEV.PĐo mức DAO ĐỘNG của một dãy số quanh giá trị trung bình của chính nó. Số nhỏ nghĩa là các kỳ gần giống nhau, số lớn nghĩa là lúc cao lúc thấp thất thường. Dùng để trả lời những câu như doanh thu tháng có đều không, thời gian giao hàng có ổn định không.
- VAR.S / VAR.PPhương sai, cũng đo mức dao động như độ lệch chuẩn nhưng để ở dạng BÌNH PHƯƠNG nên không đọc thẳng ra ý nghĩa được. Công việc kế toán gần như luôn dùng STDEV; VAR chỉ cần khi phải điền đúng chỉ tiêu này theo một biểu mẫu hoặc một mô hình có sẵn.
- CORRELĐo xem hai dãy số có đi cùng chiều với nhau không. Kết quả nằm từ -1 tới 1: gần 1 là hai bên cùng tăng cùng giảm, gần -1 là ngược chiều nhau, quanh 0 là chẳng ăn nhập gì. Dùng để kiểm nhanh xem chi phí quảng cáo có kéo theo doanh thu, hay số nhân sự có kéo theo sản lượng.
- FORECAST.LINEAR / TRENDKéo dài xu hướng của dữ liệu quá khứ để ước tính giá trị kỳ tới. FORECAST.LINEAR cho ra MỘT con số cho một mốc; TREND cho ra cả dãy số cho nhiều mốc một lúc. Thay việc vẽ biểu đồ rồi kẻ tay đường xu hướng và ước lượng bằng mắt.
- SLOPE / INTERCEPTTách đường xu hướng thành hai con số đọc được: SLOPE là mức tăng thêm mỗi khi mốc tăng một đơn vị, INTERCEPT là mức nền khi mốc bằng 0. Dùng để trả lời kiểu chi thêm một triệu quảng cáo thì doanh thu tăng bao nhiêu, hoặc tách tổng chi phí thành phần cố định và phần biến đổi.
Văn bản
- LEFT / RIGHTPhổ biếnCắt lấy n ký tự ở đầu hoặc ở cuối một chuỗi. Đây là hàm để tách mã chi nhánh, loại chứng từ, năm tài chính ra khỏi một mã ghép, thay cho việc ngồi gõ tay lại cả cột.
- MIDPhổ biếnCắt một khúc nằm GIỮA chuỗi: bắt đầu từ ký tự thứ mấy, lấy bao nhiêu ký tự. Dùng để moi phần năm, phần tháng hay mã kho nằm kẹp giữa một mã ghép mà LEFT và RIGHT không với tới.
- LENPhổ biếnĐếm số ký tự trong ô, tính cả dấu cách. Trong thực tế ít ai cần con số này để làm gì, nó là công cụ CHẨN ĐOÁN: so LEN của ô nghi ngờ với độ dài đúng là biết ngay dữ liệu bẩn ở đâu.
- FINDNên biếtCho biết một chuỗi con nằm ở vị trí thứ mấy trong chuỗi lớn. Ít khi dùng một mình — nó là bàn đạp để LEFT, MID, RIGHT biết cắt ở đâu khi mã mỗi dòng một độ dài.
- SEARCHNên biếtTìm vị trí của một chuỗi con, không phân biệt hoa thường và chấp nhận ký tự đại diện. Trong thực tế hay dùng chung với ISNUMBER để làm cột phân loại kiểu "tên hàng có chứa chữ này thì xếp nhóm kia".
- SUBSTITUTENên biếtThay một đoạn chữ bằng đoạn chữ khác, dựa trên NỘI DUNG. Đây là hàm chủ lực khi dọn dữ liệu dán về: bỏ dấu chấm ngăn nghìn, gỡ ký tự lạ, chuẩn hoá cách viết tên công ty.
- REPLACENên biếtThay một đoạn theo VỊ TRÍ: từ ký tự thứ mấy, thay mấy ký tự, thay bằng gì. Dùng khi biết đoạn cần sửa nằm ở đâu nhưng không biết nội dung nó là gì, ví dụ che bớt số tài khoản hoặc chèn thêm dấu ngăn vào mã.
- TRIMPhổ biếnBỏ dấu cách thừa ở hai đầu chuỗi và rút gọn dấu cách đôi ở giữa còn một. Đây là thuốc chữa số một cho tình huống VLOOKUP ra #N/A trong khi mắt nhìn thấy hai ô giống hệt nhau.
- CLEANNên biếtBỏ các ký tự điều khiển không in được ra khỏi chuỗi. Dùng cho dữ liệu xuất từ phần mềm kế toán, phần mềm quản trị hoặc kết xuất máy chủ, chỗ hay lẫn ký tự hình vuông đen hoặc dấu xuống dòng ẩn.
- UPPER / LOWER / PROPERNên biếtĐổi kiểu chữ của cả chuỗi: UPPER ra chữ hoa hết, LOWER ra chữ thường hết, PROPER viết hoa chữ cái đầu mỗi từ. Dùng để chuẩn hoá danh mục khách hàng, mã hàng, email trước khi gộp hoặc đối chiếu.
- CONCATPhổ biếnNối nhiều mảnh chữ lại thành một chuỗi, nhận cả một vùng ô chứ không phải liệt kê từng ô. Dùng để dựng số chứng từ, mã ghép, hoặc gộp các cột địa chỉ rời thành một dòng.
- TEXTJOINNên biếtNối một vùng ô lại thành một chuỗi, tự chèn dấu ngăn giữa các phần và bỏ qua ô trống. Dùng để gom danh sách số hoá đơn của một khách, danh sách mã hàng trong một phiếu, vào đúng một ô để đưa vào báo cáo.
- TEXTPhổ biếnĐổi một số hoặc một ngày thành chữ theo đúng khuôn mình chỉ định. Dùng để ghép số tiền và ngày tháng vào câu chữ trong hợp đồng, công văn, hoặc để tạo khoá gom nhóm kiểu năm-tháng.
- VALUEPhổ biếnĐổi một chuỗi trông giống số thành số thật để tính toán được. Dùng cho dữ liệu kết xuất ra dạng chữ, thường nhận ra nhờ nó tự căn TRÁI trong ô và SUM cả cột ra 0.
- NUMBERVALUEĐổi chuỗi thành số nhưng CHỈ RÕ đâu là dấu thập phân, đâu là dấu ngăn nghìn, thay vì phụ thuộc cài đặt của máy. Dùng khi nhận file từ đối tác nước ngoài hoặc kết xuất từ hệ thống ghi số theo kiểu khác máy mình.
- REPTLặp lại một chuỗi n lần. Hai việc dùng thật: vẽ biểu đồ cột bằng ký tự ngay trong ô để bảng báo cáo có hình mà không cần chèn chart, và đệm số 0 vào trước mã cho đủ độ dài.
- CHAR / CODEHai hàm đi ngược chiều nhau: CHAR lấy một mã số ra ký tự tương ứng, CODE lấy một ký tự ra mã số của nó. Trong thực tế dùng để chèn ký tự gõ không được, nhất là CHAR(10) xuống dòng, và để SOI xem ký tự lạ trong dữ liệu bẩn thật ra là con gì.
- EXACTSo sánh hai chuỗi có phân biệt chữ hoa chữ thường, trả về TRUE hoặc FALSE. Cần đến khi mã hàng hoặc mã tài khoản dùng cả chữ hoa lẫn chữ thường để phân biệt hai thứ khác nhau.
- TEXTBEFORE / TEXTAFTERNên biếtLấy phần chữ nằm TRƯỚC hoặc SAU một dấu ngăn cho trước. Làm gọn đúng việc mà trước đây phải ghép LEFT với FIND, và không cần đếm ký tự nữa.
- TEXTSPLITNên biếtTách một chuỗi ra nhiều ô theo dấu ngăn, ngay bằng công thức. Là bản công thức của Text to Columns, khác ở chỗ dữ liệu nguồn thay đổi thì kết quả tự cập nhật, không phải làm lại từ đầu.
Hàm tự viết & bổ trợ
- Hàm do LAMBDA đặt tênViết một công thức bằng LAMBDA rồi vào Formulas → Name Manager đặt tên cho nó; từ đó gõ =TenHam(...) trong ô như hàm có sẵn. Đây là cách tự viết hàm KHÔNG cần VBA: file lưu .xlsx bình thường, máy nhận không phải bật macro.
- Hàm tự viết bằng VBA (UDF)Viết một Function trong Module của VBA rồi gọi thẳng trong ô. Làm được cả những việc công thức không làm nổi, đổi lại file phải lưu dạng .xlsm và máy nhận phải cho phép macro.
- EUROCONVERTQuy đổi giữa euro và các đồng tiền cũ của châu Âu theo tỷ giá CỐ ĐỊNH đã chốt khi các nước đó vào khu vực euro. Phải bật bổ trợ Euro Currency Tools mới dùng được.
- CALL / REGISTER.IDGọi hàm nằm trong thư viện DLL bên ngoài. Là di sản của Excel đời rất cũ, nay gần như không còn dùng và bị chặn ở nhiều môi trường vì lý do bảo mật.
Web
- WEBSERVICEGọi một địa chỉ web và lấy về nội dung dạng chữ, thường là XML. Hay dùng để kéo tỷ giá, giá vàng thẳng vào file mà không phải mở trình duyệt rồi chép tay.
- FILTERXMLBóc một mẩu dữ liệu ra khỏi chuỗi XML, thường là chuỗi do WEBSERVICE lấy về. Chỉ đường tới mẩu cần lấy bằng cú pháp XPath.
- ENCODEURLMã hoá một chuỗi cho an toàn khi ghép vào địa chỉ web: dấu cách, chữ tiếng Việt có dấu và ký tự đặc biệt được đổi sang dạng %XX.