Hàm trong Excel là thứ không thể thiếu được nếu như bạn muốn sử dụng tốt Excel. Nếu coi việc học Excel như học ngoại ngữ (tiếng Anh) thì hàm Excel chính là các từ mới. Biết càng nhiều từ thì bạn nói, viết càng dễ, nhưng không ai giỏi nhờ chỉ tập trung học từ mới. Vậy thì chúng ta phải học hàm như thế nào?
Cách nạp giá trị vào các thành phần của các hàm trong Excel
Cách 1: Viết trực tiếp: là khi bạn viết cụ thể 1 con số hoặc 1 đoạn ký tự. Khi viết các ký tự Text thì bạn cần đặt các ký tự này trong cặp dấu nháy kép (“ký tự text”) còn các con số thì không cần đặt trong cặp dấu nháy kép.
Cách 2: Viết tham chiếu: thay vì nhập trực tiếp, bạn sẽ tham chiếu tới tọa độ của 1 ô đang chứa giá trị mà bạn muốn nạp vào.
Cách viết trực tiếp và viết tham chiếu trong hàm Excel
Trong ví dụ trên, cách viết trực tiếp là ghi rõ các con số cần tính tổng vào trong hàm SUM. Cách viết tham chiếu là tham chiếu tới vùng ô A2:A4, là nơi chứa các số cần tính tổng.
Cách 3: Viết lồng ghép hàm Excel: Giá trị nạp vào không có sẵn mà phải qua 1 số bước tính toán bằng hàm khác mới có được.
Anh Long ký hợp đồng lao động với công ty A, ngày bắt đầu là từ 05/3/2020, hợp đồng lao động có thời hạn 1 năm 8 tháng. Hãy xác định ngày hết hạn của hợp đồng đó.
Ngày hết hạn của hợp đồng được xác định như sau:
Năm: tính từ năm ký hợp đồng + 1 năm
Tháng: tính từ tháng ký hợp đồng + 6 tháng
Ngày: tính từ ngày ký hợp đồng + 0 ngày
Kết quả cần thu được là 1 giá trị ngày tháng được tạo nên từ các tham số Năm, tháng, ngày được xác định như trên.
Như vậy chỉ cần duy nhất 1 công thức tại ô D8 chúng ta có thể tính ra được kết quả rồi. Trong ô B1 chỉ chứa 1 giá trị gồm đủ ngày tháng năm. Nhưng để có thể cộng riêng được Số năm, Số tháng vào thời điểm ngày bắt đầu, bắt buộc chúng ta phải tách được riêng các con số thể hiện số năm, số tháng, số ngày => Dùng các hàm Year, Month, Day để tách các giá trị này; Sau đó nối lại với hàm DATE để tạo ra 1 kết quả là giá trị đủ ngày tháng năm.
Xem thêm: Hướng dẫn sử dụng hàm DATE cơ bản trong Excel
Cách bỏ ký tự trong Excel ở đầu chuỗi dựa vào dấu cách
Thông thường, rất ít dữ liệu trong bảng tính có cấu trúc và số thứ tự ký tự trong một chuỗi giống nhau hoàn toàn. Đối với dạng dữ liệu này, bạn sử dụng cách xóa chữ trong Excel theo công thức sau:
=RIGHT(TRIM(ô tham chiếu chứa dữ liệu cần xóa ký tự),LEN(TRIM(ô tham chiếu chứa dữ liệu cần xóa ký tự))-FIND(" ",TRIM(ô tham chiếu chứa dữ liệu cần xóa ký tự))).
Cùng phân tích ví dụ dưới đây để hiểu hơn về công thức:
Ô chứa chuỗi dữ liệu cần xóa ký tự là ô A1 với chuỗi ký tự “Nguyễn Văn Thanh”. Bạn cần loại bỏ phần họ - tức chữ “Nguyễn”. Lúc này, công thức bạn áp dụng như sau: =RIGHT(TRIM(A1),LEN(TRIM(A1))-FIND(" ",TRIM(A1))). Sau đó, bạn nhấn Enter, kết quả chuỗi ký tự được xóa sẽ lập tức hiện ra tại ô.
Trong đó, ý nghĩa của các hàm được sử dụng kết hợp như sau:
Như vậy, công thức phía trên diễn văn như sau: Yêu cầu máy xác định vị trí của khoảng trắng đầu tiên có ở chuỗi văn bản trong ô tham chiếu, sau đó đếm số ký tự cần giữ sau dấu cách đầu tiên (từ trái qua phải). Sau khi đếm số ký tự cần giữ xong, hàm RIGHT sẽ trích xuất chuỗi ký tự này và giữ lại chúng. Phần ký tự còn lại không được trích xuất sẽ bị loại bỏ.
Để áp dụng với các dòng dữ liệu còn lại, bạn chỉ cần nhấn chuột vào góc dưới bên phải ô dữ liệu, sau đó kéo theo hàng dọc.
Khi bạn thay đổi bất kỳ ký tự nào trong ô tham chiếu, ô kết quả cũng sẽ được tự động cập nhật. Đây là một ưu điểm lớn của hàm Excel loại bỏ ký tự này.
Cách xóa ký tự trong Excel ở đầu chuỗi văn bản có số thứ tự cố định
Nếu các ký tự trong chuỗi văn bản đứng liền nhau và các chuỗi đều có số thứ tự cố định, bạn có thể sử dụng hàm RIGHT và hàm LEN theo công thức dưới đây:
=RIGHT(ô tham chiếu chứa giá trị cần bỏ ký tự,LEN(ô tham chiếu chứa ký tự cần bỏ)-số lượng ký tự sẽ loại bỏ tính từ trái sang phải).
Cùng phân tích ví dụ dưới đây để hiểu công thức hơn nhé:
Chuỗi ký tự ban đầu là ABC-123. Bạn cần loại bỏ 4 ký tự đầu tiên là ABC và dấu gạch ngang. Lúc này, công thức bạn áp dụng sẽ là:
Trong đó, hàm LEN đảm nhiệm vai trò đếm số số lượng ký tự có tại ô A1. Hàm RIGHT có trách nhiệm tách các ký tự bên phải của ô A1 với số lượng ký tự tách sẽ là tổng số ký tự trừ đi 4 để giữ lại. Các ký tự khác sẽ tự động được bỏ đi.
Sau khi nhập xong công thức, bạn nhấn Enter. Ngay lập tức kết quả sẽ được hiển thị trong ô đã nhập công thức như hình phía dưới.
Một điều bất cập là cách này chỉ có thể áp dụng nếu tất cả các chuỗi ký tự trong bảng tính đều có cấu trúc giống nhau và số lượng, số thứ tự ký tự cần loại bỏ như nhau.
Ở ví dụ phía trên, chúng ta chỉ cần loại bỏ 4 ký tự đầu tiên. Do đó, bạn có thể dùng chuột kéo theo hàng dọc để copy công thức từ ô trên xuống ô dưới. Từ đó, 4 ký tự đầu sẽ tự động được loại bỏ.
Lưu ý: bạn nhấn giữ chuột tại góc dưới bên phải ô (đến khi thấy hiện ra dấu cộng) sau đó mới thực hiện thao tác kéo chuột.
Đối với các chuỗi ký tự có cấu trúc và số thứ tự ký tự cần loại bỏ không giống nhau, bạn sử dụng cách xóa ký tự trong chuỗi được chúng tôi gọi ý ở phần dưới đây.
Cách dùng hàm bỏ ký tự trong Excel
Dưới đây là cách sử dụng hàm và lệnh xóa ký tự trong Excel vô cùng đơn giản mà bạn có thể áp dụng ngay:
Chú ý khi viết dấu toán tử vào hàm trong Excel
Các dấu toán tử là các dấu thể hiện sự so sánh hoặc thể hiện 1 khoảng giá trị (bằng, khác, lớn hơn, nhỏ hơn...). Các dấu này được viết trong hàm Excel như sau:
Nếu ô B1 nhận giá trị bằng 5 thì kết quả là Đúng, không thì là Sai
=IF(AND(B1=”CN1”, C1<>3), “Có”, “Không”)
Nếu đồng thời ô B1 là chữ CN1 và ô C1 khác 3 thì kết quả là chữ Có, còn không phải thì là chữ Không
Đếm trong vùng A1:A10 có bao nhiêu giá trị lớn hơn 5
Tính tổng trong vùng D3:D10 tương ứng theo các giá trị thỏa mãn điều kiện lớn hơn hoặc bằng 5 trong vùng C3:C10
=SUMPRODUCT((A3:A10<100)*C3:C10)
Tính tổng các giá trị trong vùng C3:C10 tương ứng theo các giá trị trong vùng A3:A10 thỏa mãn điều kiện nhỏ hơn 100
=SUMIFS(A3:A10, F3:F10, “<=”&B2)
Tính tổng các giá trị trong vùng A3:A10 tương ứng theo các giá trị trong vùng F3:F10 thỏa mãn điều kiện nhỏ hơn hoặc bằng giá trị tại ô B2
Nếu ô A1 có giá trị bằng 1 thì kết quả là Đúng, không phải thì nhận giá trị rỗng (không có nội dung gì)
Khi viết dấu toán tử kèm với 1 số cụ thể (viết số trực tiếp) thì viết bình thường, chú ý 2 dạng:
Dạng biểu thức so sánh: không sử dụng dấu nháy kép (ví dụ về dấu Bằng, Khác)
Dạng giá trị theo khoảng: đặt cả dấu toán tử và số trong dấu nháy kép (ví dụ về Lớn hơn, Lớn hơn hoặc bằng)
Khi viết dấu toán tử kèm với 1 tọa độ (viết tham chiếu) thì viết dấu toán tử trong cặp dấu nháy kép, sau đó nối với tọa độ tham chiếu bởi dấu & (trong ví dụ về dấu Nhỏ hơn hoặc bằng).
Bởi vì có rất nhiều hàm trong Excel nên để thuận tiện cho việc ghi nhớ, tra cứu và sử dụng thì Excel đã phân nhóm sẵn các hàm. Cụ thể chúng ta có thể thấy các nhóm hàm Excel trong thẻ Formulas như sau:
Các nhóm hàm trong thẻ Formulas của Excel
Nhóm hàm tính tổng tự động (AutoSum): Gồm các phép tính nhanh, đơn giản như Tính tổng (SUM), Tính trung bình (AVERAGE), Tìm giá trị lớn nhất (MAX), Tìm giá trị nhỏ nhất (MIN)
Nhóm hàm thường được sử dụng (Recently Used): là những hàm được nhiều người sử dụng và phổ biến: Tính trung bình hoặc tính tổng (AVERAGE và SUM), xét tính logic (IF), nối chuỗi văn bản (CONCAT), thống kê, đếm (COUNT)...
Hàm logic (Logical): là những hàm chuyên về xét tính logic: IF, AND, OR, NOT… Những hàm này có thể lồng với nhau chẳng hạn như hàm IF kết hợp với AND để kiểm tra một loạt điều kiện phức tạp,…
Nhóm hàm trong tài chính (Financial): Những hàm tính toán chuyên biệt về trong ngành tài chính: Tính dòng tiền hiện tại, dòng tiền tương lai, lợi nhuận gộp, lãi vay…
Hàm ngày tháng, thời gian (Nhóm Date & Time): Những hàm chuyên về xử lý dữ liệu dạng Ngày tháng, thời gian: DATE, TIME, DAY, MONTH, YEAR, HOUR, MINUTE… hoặc xác định các mốc thời gian cuối tháng (EOMONTH), khoảng cách thời gian (DATEDIF)...
Hàm tính toán (Math & Trig): Gồm các hàm tính toán chuyên sâu trong toán học như SIN, COS, LOG… các hàm tính nhân (PRODUCT, SUMPRODUCT), tính chia (MOD, QUOTIENT), làm tròn số (ROUND, FLOOR…)
Hàm dò tìm, tham chiếu (Lookup & Reference): Gồm các hàm dùng cho mục đích tìm kiếm kết quả dựa trên 1 dấu hiệu: VLOOKUP, LOOKUP, INDEX, MATCH, ROW, COLUMN, ADDRESS…
Thật khó để có thể kể tên hết các hàm Excel, cũng không thể nói hết được 1 hàm sẽ sử dụng như thế nào, bởi trong các tình huống khác nhau thì dù cùng là 1 hàm nhưng cách dùng sẽ khác.
Do vậy chúng ta nên quan tâm tới việc: Trong 1 tình huống, chúng ta đang có gì, cần đạt được gì. Mô tả cách để đạt được điều đó bằng tiếng việt. Sau đó lựa chọn hàm phù hợp với từng bước. Quá trình này giống như dịch từ tiếng Việt sang tiếng Anh vậy.
Việc sử dụng các hàm trong Excel không quá khó nhưng cũng không phải là dễ. Nếu bạn chỉ ghi nhớ một cách máy móc các sử dụng, bạn sẽ không bao giờ thành thạo được hết các hàm này. Bạn cần nắm được kiến thức Excel cơ bản và hơn hết là tư duy sử dụng hàm Excel.
Xem thêm: Top khóa học tin học văn phòng nhiều học viên tại Gitiho
