Excel không chỉ là một phần mềm tính toán; đó là một công cụ mạnh mẽ giúp chúng ta quản lý dữ liệu, phân tích thông tin và trực quan hóa các kết quả. Dù bạn là một nhà quản lý, một kế toán viên hay một nhà nghiên cứu, việc hiểu và sử dụng thành thạo các công thức Excel là điều hết sức cần thiết. Cũng như một chiếc chìa khóa mở ra cánh cửa của hiệu suất và sự sáng tạo, các công thức này giúp bạn giải quyết các vấn đề phức tạp một cách nhanh chóng và hiệu quả. Dưới đây là một danh sách các công thức Excel mà mọi nhân viên văn phòng nên nắm rõ.

1. Công thức cơ bản

1.1. SUM: Tính tổng

Hàm SUM là người bạn đồng hành đầu tiên mà mọi nhân viên văn phòng cần phải biết. Giống như cách ta cộng dồn các con số vào một chiếc túi vô hình, hàm SUM giúp ta nhanh chóng tính tổng của nhiều giá trị.

• Cú pháp: =SUM(số1, số2, ...)
• Ví dụ: =SUM(C5:C10) sẽ tính tổng của các giá trị trong các ô từ C5 đến C10.

Hàm SUM không chỉ hữu ích khi tính tổng mà còn có thể kết hợp với các hàm khác như AVERAGE, MAX, MIN để thực hiện các tính toán phức tạp hơn. Chẳng hạn, khi muốn tính tổng các giá trị thỏa mãn một điều kiện nào đó, ta có thể dùng SUMIF, một hàm nâng cao hơn mà ta sẽ bàn đến sau.

1.2. AVERAGE: Tính trung bình

Khi bạn cần liên kết dữ liệu và hiểu rõ hơn bức tranh tổng thể, hàm AVERAGE sẽ ra tay giúp đỡ. Nó giống như việc chia đều từng phần trong một chiếc bánh lớn để tất cả mọi người đều nhận được một phần bằng nhau.

• Cú pháp: =AVERAGE(số1, số2, ...)
• Ví dụ: =AVERAGE(F5:F10) sẽ tính trung bình của các giá trị trong các ô từ F5 đến F10.

Hàm AVERAGE cũng rất linh hoạt khi bạn cần tính trung bình của các giá trị từ các ô không liền kề nhau, hoặc khi cần loại bỏ các giá trị 0 hay ô trống khỏi tính toán.

1.3. MAX: Tìm giá trị lớn nhất

Trong một danh sách dài các số liệu, việc tìm ra giá trị lớn nhất không cứ phải mất công tìm kiếm thủ công, hàm MAX sẽ làm điều đó cho bạn. Bạn có thể tưởng tượng hàm MAX như một chiếc kính lúp giúp bạn ngay lập tức nhận diện điểm cao nhất trong một dãy núi dữ liệu.

• Cú pháp: =MAX(số1, số2, ...)
• Ví dụ: =MAX(B2:B6) sẽ tìm giá trị lớn nhất trong các ô từ B2 đến B6.

Điểm độc đáo của hàm MAX là nó có thể kết hợp với các điều kiện khác như MAXIF để tìm ra giá trị lớn nhất trong phạm vi thỏa mãn điều kiện cụ thể. Ví dụ, hãy tưởng tượng bạn có một bảng dữ liệu bao gồm doanh thu và quốc gia. Bạn có thể dễ dàng tìm ra doanh thu cao nhất chỉ trong một quốc gia cụ thể bằng cách sử dụng MAXIF.

1.4. MIN: Tìm giá trị nhỏ nhất

Trái ngược với hàm MAX, hàm MIN giúp bạn tìm ra giá trị nhỏ nhất trong một dãy dữ liệu. Nó chẳng khác gì một chiếc đèn dò tìm kim trong đống rơm.

• Cú pháp: =MIN(số1, số2, ...)
• Ví dụ: =MIN(B2:B6) sẽ tìm giá trị nhỏ nhất trong các ô từ B2 đến B6.

Giống như MAX, hàm MIN cũng có thể được sử dụng kết hợp với các điều kiện như MINIF để tìm ra giá trị nhỏ nhất thỏa mãn điều kiện cụ thể. Đây là một công cụ tuyệt vời cho các nhà quản lý khi cần xác định các điểm yếu hoặc tình trạng kém nhất trong một phạm vi dữ liệu rộng lớn.

1.5. COUNT: Đếm số lượng ô dữ liệu

Hàm COUNT là công cụ đắc lực cho phép bạn biết được số lượng ô chứa dữ liệu trong một phạm vi nhất định, giống như việc đếm từng viên gạch trong một bức tường lớn.

• Cú pháp: =COUNT(phạm_vi)
• Ví dụ: =COUNT(B2:B10) sẽ đếm số lượng ô chứa dữ liệu trong phạm vi từ B2 đến B10.

Điểm mạnh của hàm COUNT là nó chỉ đếm các ô chứa số hay văn bản, bỏ qua các ô trống, không làm bạn mất thời gian đếm thủ công và tránh các sai sót.

1.6. COUNTA: Đếm số lượng ô có dữ liệu

Trong nhiều tình huống bạn không chỉ muốn đếm các ô chứa số mà cần đếm cả các ô chứa văn bản, hàm COUNTA sẽ là một trợ thủ đắc lực. Nó giống như một chiếc máy soi để kiểm tra từng ô và báo cáo lại tất cả những gì đã được điền vào.

• Cú pháp: =COUNTA(value1, [value2], ...)
• Ví dụ: =COUNTA(A1:A10) sẽ đếm các ô có dữ liệu trong phạm vi từ A1 đến A10.

Bất kể ô dữ liệu chứa số, chữ hay bất kỳ kiểu dữ liệu nào, hàm COUNTA đều đếm được, chỉ trừ các ô trống. Điều này rất hữu ích khi bạn cần tổng hợp thông tin từ các bảng dữ liệu lớn và muốn kiểm tra xem bảng dữ liệu đã được điền đầy đủ hay chưa.

1.7. COUNTIF: Đếm số lượng ô thỏa mãn điều kiện

Hàm COUNTIF là một bậc cao hơn của COUNT, giúp bạn không chỉ đếm các ô có dữ liệu mà còn thỏa mãn một điều kiện nhất định. Tưởng tượng bạn cần đếm số lượng nhân viên có mức lương trên 20 triệu trong một bảng dữ liệu, hàm COUNTIF sẽ là công cụ cứu cánh.

• Cú pháp: =COUNTIF(range, criteria)
• Ví dụ: =COUNTIF(A1:A10, ">20") sẽ đếm số ô có giá trị lớn hơn 20 trong phạm vi từ A1 đến A10.

Hàm COUNTIF không chỉ giới hạn ở việc đếm số mà còn có thể áp dụng với văn bản và ngày tháng. Bạn có thể dễ dàng đếm số ô chứa chữ “Yes”, các ô có ngày lớn hơn một ngày cụ thể nào đó, từ đó hỗ trợ tốt cho việc phân tích dữ liệu và đưa ra quyết định.

2. Công thức làm việc với văn bản

Văn bản trong Excel không chỉ dùng để mô tả dữ liệu mà còn là đề tài không thể thiếu trong công việc xử lý thông tin. Dưới đây là những công thức giúp bạn làm việc hiệu quả với văn bản.

2.1. CONCATENATE: Nối chuỗi

Hàm CONCATENATE là công cụ nối các chuỗi văn bản lại với nhau. Nó giống như việc bạn gộp các từ riêng lẻ lại để tạo thành một câu hoàn chỉnh.

• Cú pháp: =CONCATENATE(text1, [text2], ...)
• Ví dụ: =CONCATENATE("Hello", " ", "World") sẽ trả về kết quả “Hello World”.

Hàm CONCATENATE cực kỳ hữu ích khi cần gộp các ô chứa văn bản lại với nhau để tạo thành một thông tin thống nhất, ví dụ như gọp tên và họ lại thành tên đầy đủ của người dùng.

2.2. LEFT: Lấy ký tự bên trái

Khi cần trích xuất một số lượng ký tự nhất định từ bên trái của một chuỗi văn bản, hàm LEFT sẽ ra tay giúp đỡ.

• Cú pháp: =LEFT(text, [num_chars])
• Ví dụ: =LEFT("Hello World", 5) sẽ trả về kết quả “Hello”.

Hàm LEFT không chỉ dừng ở việc lấy ký tự bên trái trong trường hợp giai đoạn đầu của văn bản là cần thiết, nó còn đóng vai trò quan trọng trong việc tách mã số, chữ cái hoặc thông tin đặc biệt từ chuỗi văn bản lớn.

2.3. RIGHT: Lấy ký tự bên phải

Ngược lại với hàm LEFT, hàm RIGHT giúp bạn lấy các ký tự từ bên phải của chuỗi văn bản, giống như việc trích rút phần cuối của một đoạn thông điệp dài.

• Cú pháp: =RIGHT(text, [num_chars])
• Ví dụ: =RIGHT("Hello World", 5) sẽ trả về kết quả “World”.

Dùng hàm RIGHT rất hữu ích khi bạn cần nhóm lại các thông tin quan trọng tại phần kết thúc của văn bản, chẳng hạn số đuôi điện thoại hoặc mã vạch sản phẩm.

2.4. LEN: Tính độ dài chuỗi

Hàm LEN là công cụ đắc lực khi bạn cần biết chính xác độ dài của một chuỗi văn bản. Nó chẳng khác gì bạn đếm từng ký tự chữ trong một đoạn văn dài.

• Cú pháp: =LEN(text)
• Ví dụ: Nếu ô A1 chứa chuỗi “onetwothree”, công thức =LEN(A1) sẽ trả về kết quả là 11.

Hàm LEN đặc biệt hữu ích trong trường hợp bạn cần kiểm tra tính nhất quán của dữ liệu văn bản hoặc xác thực độ dài của các trường dữ liệu khi nhập vào các hệ thống.

2.5. FIND: Tìm vị trí ký tự trong chuỗi

Khi bạn cần tìm ra vị trí của một ký tự hoặc chuỗi ký tự trong một chuỗi dài, hàm FIND sẽ giúp bạn thực hiện điều này. Nó giống như một chiếc kính phóng đại để tìm ra chi tiết nhỏ trong bức tranh lớn.

• Cú pháp: =FIND(find_text, within_text, [start_num])
• Ví dụ: Nếu ô A2 chứa chuỗi “CD234”, công thức =FIND("2", A2) sẽ trả về kết quả là 3.

Hàm FIND đặc biệt hữu ích khi bạn cần xác định vị trí của một ký tự cụ thể trong chuỗi văn bản để từ đó thực hiện các thao tác tách, thay thế hoặc chỉnh sửa dữ liệu.

2.6. UPPER: Chuyển đổi chuỗi sang chữ hoa

Hàm UPPER là sự lựa chọn khi bạn cần chuyển đổi tất cả các ký tự trong một chuỗi thành chữ hoa. Điều này đặc biệt quan trọng khi bạn cần chuẩn hóa dữ liệu văn bản.

• Cú pháp: =UPPER(text)
• Ví dụ: Nếu ô A1 chứa chuỗi “Colin”, công thức =UPPER(A1) sẽ trả về kết quả “COLIN”.

Hàm UPPER rất tiện dụng khi bạn cần chuẩn hóa dữ liệu văn bản như chuyển đổi email, tên người hay các đoạn văn để đảm bảo tính nhất quán và dễ đọc.

2.7. LOWER: Chuyển đổi chuỗi sang chữ thường

Ngược lại với hàm UPPER, hàm LOWER giúp bạn chuyển đổi tất cả các ký tự trong một chuỗi thành chữ thường.

• Cú pháp: =LOWER(text)
• Ví dụ: Nếu ô A1 chứa chuỗi “COLIN”, công thức =LOWER(A1) sẽ trả về kết quả “colin”.

Hàm LOWER hữu ích trong trường hợp bạn cần đảm bảo tính đồng nhất của dữ liệu văn bản, đặc biệt khi làm việc với hệ thống yêu cầu chữ phân biệt rõ ràng giữa chữ hoa và chữ thường.

2.8. TRIM: Loại bỏ khoảng trống thừa

Hàm TRIM là công cụ tuyệt vời để loại bỏ mọi khoảng trống thừa trong chuỗi văn bản. Nó giống như việc làm sạch bàn làm việc, loại bỏ mọi thứ dư thừa.

• Cú pháp: =TRIM(text)
• Ví dụ: Nếu ô A1 chứa chuỗi ” Colin “, công thức =TRIM(A1) sẽ trả về kết quả “Colin”.

Hàm TRIM đặc biệt quan trọng khi bạn làm việc với dữ liệu đầu vào không nhất quán, loại bỏ các khoảng trống thừa giúp bạn có dữ liệu sạch hơn và dễ xử lý hơn.

3. Công thức làm việc với ngày tháng

Ngày tháng luôn có vai trò trọng yếu trong nhiều lĩnh vực công việc. Excel cung cấp nhiều công thức giúp bạn làm việc dễ dàng với ngày tháng, từ đơn giản đến phức tạp.

3.1. TODAY: Trả về ngày hiện tại

Hàm TODAY giúp bạn dễ dàng cập nhật ngày hiện tại trong bảng tính của mình. Nó như một tấm lịch tự động điều chỉnh theo từng ngày.

• Cú pháp: =TODAY()
• Ví dụ: Nếu áp dụng công thức này vào ô A1, ô A1 sẽ hiển thị ngày hiện tại.

TODAY hữu ích trong các báo cáo hàng ngày, lịch làm việc hoặc bất kỳ tài liệu nào cần cập nhật ngày tự động mà không phải chỉnh sửa thủ công.

3.2. NOW: Trả về ngày và giờ hiện tại

Khi bạn cần cập nhật không chỉ ngày mà còn cả giờ hiện tại, hàm NOW sẽ là sự lựa chọn hoàn hảo. Nó giống như một chiếc đồng hồ đang chạy liên tục để đảm bảo dữ liệu luôn thời kịp thời.

• Cú pháp: =NOW()
• Ví dụ: Nếu áp dụng công thức này vào ô B1, ô B1 sẽ hiển thị ngày và giờ hiện tại.

NOW rất hữu ích cho các công việc yêu cầu ghi nhận thời gian chính xác như hệ thống chấm công, theo dõi thời gian dự án, hoặc lập báo cáo thời gian thực.

3.3. DATE: Trả về ngày từ năm, tháng, ngày

Hàm DATE giúp bạn tạo ra một ngày từ các thành phần riêng lẻ như năm, tháng, ngày. Nó giống như việc lắp ráp từng mảnh ghép lại thành một bức tranh hoàn chỉnh.

• Cú pháp: =DATE(year, month, day)
• Ví dụ: =DATE(2024, 7, 10) sẽ trả về ngày 10 tháng 7 năm 2024.

DATE rất tiện lợi khi bạn cần tạo ra ngày từ dữ liệu riêng lẻ hoặc sửa đổi các thành phần của một ngày cụ thể.

3.4. YEAR: Trả về năm từ ngày

Hàm YEAR giúp bạn trích xuất năm từ một ngày hoàn chỉnh, chẳng khác gì việc bạn tách riêng phần năm ra từ một tờ lịch lớn.

• Cú pháp: =YEAR(date)
• Ví dụ: Nếu ô A1 chứa ngày 10/07/2024, =YEAR(A1) sẽ trả về kết quả 2024.

Hàm YEAR thường được sử dụng trong việc phân tích năm cụ thể từ ngày tháng tổng hợp, ví dụ như trong các báo cáo tài chính hoặc lịch làm việc.

3.5. MONTH: Trả về tháng từ ngày

Ngược lại với hàm YEAR, hàm MONTH giúp bạn trích xuất tháng từ một ngày. Nó giống như việc bóc tách phần tháng ra để giữ lấy thông tin quan trọng.

• Cú pháp: =MONTH(date)
• Ví dụ: Nếu ô B1 chứa ngày 10/07/2024, =MONTH(B1) sẽ trả về kết quả 7.

MONTH rất hữu ích khi bạn cần phân loại hoặc tổng hợp dữ liệu theo tháng, chẳng hạn như doanh thu hàng tháng hay các sự kiện định kỳ.

3.6. DAY: Trả về ngày từ ngày

Hàm DAY giúp bạn trích xuất ngày từ một ngày tháng hoàn chỉnh, giống như việc bạn xác định được con số ngày cụ thể từ tờ lịch tháng.

• Cú pháp: =DAY(date)
• Ví dụ: Nếu ô C1 chứa ngày 10/07/2024, =DAY(C1) sẽ trả về kết quả là 10.

DAY rất hữu ích cho việc tạo báo cáo ngày cụ thể hoặc phân tích ngày trong tháng.

3.7. DATEDIF: Tính khoảng cách giữa hai ngày

Khi cần tính khoảng cách giữa hai ngày, hàm DATEDIF sẽ giúp bạn làm điều này một cách dễ dàng và hiệu quả như một cái cân đo đếm ngày tháng.

• Cú pháp: =DATEDIF(start_date, end_date, "unit")
• Ví dụ: =DATEDIF("01/01/2020", "31/12/2020", "d") sẽ trả về số ngày giữa hai ngày là 365.

DATEDIF có thể sử dụng để tính số ngày, tháng hoặc năm giữa hai ngày, rất hữu ích cho việc tính nguyệt kế, tuổi hoặc thời gian làm việc.

3.8. WEEKDAY: Trả về ngày trong tuần

Hàm WEEKDAY trả về ngày trong tuần của một ngày cụ thể, với các giá trị từ 1 (Chủ Nhật) đến 7 (Thứ Bảy). Nó giống như việc bạn xác định ngay rằng hôm nay là thứ mấy.

• Cú pháp: =WEEKDAY(date)
• Ví dụ: =WEEKDAY("10/07/2024") sẽ trả về kết quả là 3 (Thứ Ba).

WEEKDAY rất hữu ích khi bạn cần biết ngày trong tuần của một ngày cụ thể để lên kế hoạch làm việc hoặc thực hiện các công việc khác liên quan đến lịch.

4. Công thức điều kiện

Các công thức điều kiện trong Excel giúp bạn tạo ra các câu lệnh điều kiện logic, kiểm tra và trả về kết quả dựa trên các điều kiện cụ thể. Những công thức này tương tự như các phép kiểm tra điều kiện trong lập trình, cho phép bạn thực hiện các tính toán phức tạp và tự động hóa công việc.

4.1. IF: Kiểm tra điều kiện

Hàm IF là một trong những hàm cơ bản và phổ biến nhất trong Excel giúp bạn kiểm tra điều kiện và trả về giá trị khác nhau dựa trên kết quả của điều kiện đó. Nó giống như một bài kiểm tra nhanh với hai kết quả đích thị đúng sai.

• Cú pháp: =IF(điều_kiện, giá_trị_nếu_đúng, giá_trị_nếu_sai)
• Ví dụ: =IF(B2>60, "Pass", "Fail") sẽ kiểm tra nếu giá trị trong ô B2 lớn hơn 60 thì trả về “Pass”, ngược lại trả về “Fail”.

Hàm IF rất mạnh mẽ và linh hoạt, ứng dụng trong nhiều tình huống như: kiểm tra tình trạng hàng hóa (Còn hàng hay hết hàng), phân loại học sinh (Đạt hay không đạt), hay xác định giá trị khuyến mãi sản phẩm.

4.2. AND: Kiểm tra nhiều điều kiện đồng thời

Khi bạn cần kiểm tra nhiều điều kiện đồng thời, hàm AND sẽ là một công cụ hữu ích. Nó tương tự như việc bạn đồng thời yêu cầu tất cả các điều kiện phải đúng mới trả về kết quả True.

• Cú pháp: =AND(điều_kiện1, điều_kiện2, ...)
• Ví dụ: =AND(A2>100, B2<50) sẽ kiểm tra xem giá trị trong ô A2 có lớn hơn 100 và giá trị trong ô B2 có nhỏ hơn 50 hay không.

Hàm AND thường được sử dụng cùng với hàm IF, giúp bạn tạo ra các phép thử điều kiện phức tạp. Ví dụ: =IF(AND(A2>100, B2<50), "Đúng", "Sai") sẽ trả về “Đúng” nếu cả hai điều kiện đều đúng, ngược lại trả về “Sai”.

4.3. OR: Kiểm tra nhiều điều kiện hoặc

Ngược lại với hàm AND, hàm OR giúp bạn kiểm tra một trong số nhiều điều kiện. Điều này tương tự như việc bạn chỉ cần một trong số các điều kiện đúng là kết quả đúng.

• Cú pháp: =OR(điều_kiện1, điều_kiện2, ...)
• Ví dụ: =OR(A2>100, B2<50) sẽ kiểm tra xem giá trị trong ô A2 có lớn hơn 100 hoặc giá trị trong ô B2 có nhỏ hơn 50 hay không.

Hàm OR cũng thường được sử dụng trong các phép kiểm tra phức tạp cùng với hàm IF. Ví dụ: =IF(OR(A2>100, B2<50), "Đúng", "Sai") sẽ trả về “Đúng” nếu một trong hai điều kiện là đúng, ngược lại trả về “Sai”.

4.4. VLOOKUP: Tìm kiếm giá trị trong bảng

Hàm VLOOKUP là một công cụ mạnh mẽ giúp bạn tìm kiếm một giá trị trong bảng và trả về giá trị tương ứng trong cùng hàng. Nó giống như việc bạn tìm một thông tin cụ thể trong một từ điển và nhận lại thông tin liên quan.

• Cú pháp: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
• Ví dụ: =VLOOKUP(A2, DataTable, 2, FALSE) sẽ tìm giá trị trong cột 2 của bảng DataTable dựa trên giá trị trong ô A2.

VLOOKUP rất hữu ích trong việc tra cứu thông tin khách hàng, sản phẩm hay các bản dữ liệu lớn. Tuy nhiên, điều cần lưu ý là giá trị tìm kiếm phải nằm trong cột đầu tiên của phạm vi tra cứu.

4.5. HLOOKUP: Tìm kiếm giá trị theo hàng

Hàm HLOOKUP có chức năng tương tự VLOOKUP nhưng thay vì tìm kiếm theo cột, nó tìm kiếm theo hàng. Điều này rất hữu ích khi bảng dữ liệu của bạn được sắp xếp theo hàng ngang.

• Cú pháp: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
• Ví dụ: =HLOOKUP(B1, DataTable, 3, FALSE) sẽ tìm giá trị trong hàng 3 của bảng DataTable dựa trên giá trị trong ô B1.

HLOOKUP rất hữu ích khi bạn cần tìm hiểu các thông tin như bảng chấm công theo các ngày trong tuần hoặc lịch biểu sự kiện.

4.6. INDEX: Trả về giá trị tại vị trí xác định

Hàm INDEX cho phép bạn trả về giá trị tại vị trí xác định trong một phạm vi, tương tự như việc bạn chỉ tay vào một điểm trên bản đồ và nhận lại thông tin tại đó.

• Cú pháp: =INDEX(array, row_num, [col_num])
• Ví dụ: =INDEX(DataTable, 2, 3) sẽ trả về giá trị tại hàng 2, cột 3 của bảng DataTable.

Hàm INDEX rất linh hoạt và có thể kết hợp với hàm MATCH để tạo ra các tra cứu mạnh mẽ hơn. Ví dụ: =INDEX(DataTable, MATCH("Tìm kiếm", cột_tìm_kiếm, 0), 2) sẽ trả về giá trị tại hàng thỏa mãn điều kiện tìm kiếm trong cột và cột trả về tương ứng.

4.7. MATCH: Tìm vị trí của giá trị trong danh sách

Hàm MATCH giúp bạn tìm vị trí của một giá trị trong một phạm vi, giống như việc bạn xác định vị trí của một từ trong từ điển.

• Cú pháp: =MATCH(lookup_value, lookup_array, [match_type])
• Ví dụ: =MATCH(B2, DataColumn, 0) sẽ tìm vị trí của giá trị trong ô B2 trong cột DataColumn.

Hàm MATCH có thể kết hợp với hàm INDEX để tạo ra hệ thống tra cứu động, giúp bạn linh hoạt hơn trong việc tìm kiếm và trả về giá trị.

5. Công thức nâng cao

Đối với những người muốn tối ưu hóa hiệu quả và thực hiện các phân tích phức tạp hơn, các công thức nâng cao trong Excel sẽ là những trợ thủ đắc lực.

5.1. SUMIF: Tính tổng theo điều kiện

Hàm SUMIF giúp bạn tính tổng các giá trị trong một phạm vi dựa trên một điều kiện cụ thể. Nó giống như việc bạn chỉ tính tổng các hũ đường trong một giỏ bánh kẹo lớn.

• Cú pháp: =SUMIF(criteria_range, criteria, [sum_range])
• Ví dụ: =SUMIF(A1:A10, ">10", B1:B10) sẽ tính tổng các giá trị trong cột B nếu giá trị tương ứng trong cột A lớn hơn 10.

Hàm SUMIF rất hữu ích khi bạn cần tính tổng doanh số bán hàng của một sản phẩm cụ thể trong một nhóm dữ liệu lớn.

5.2. SUMIFS: Tính tổng theo nhiều điều kiện

Hàm SUMIFS là một sự mở rộng của SUMIF, cho phép bạn tính tổng các giá trị dựa trên nhiều điều kiện. Tưởng tượng bạn cần tính tổng các hũ đường nhưng chỉ trong các giỏ bánh kẹo đỏ và xanh.

• Cú pháp: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
• Ví dụ: =SUMIFS(B1:B10, A1:A10, ">10", C1:C10, "<5") sẽ tính tổng các giá trị trong cột B nếu giá trị tương ứng trong cột A lớn hơn 10 và giá trị trong cột C nhỏ hơn 5.

Hàm SUMIFS rất tiện lợi khi bạn cần tính tổng dựa trên nhiều điều kiện khác nhau, giúp bạn có cái nhìn chi tiết hơn về dữ liệu.

5.3. AVERAGEIF: Tính trung bình theo điều kiện

Hàm AVERAGEIF giúp bạn tính trung bình của các giá trị trong một phạm vi dựa trên một điều kiện. Nó giúp bạn lọc ra và chỉ lấy các giá trị cần thiết để tính trung bình.

• Cú pháp: =AVERAGEIF(range, criteria, [average_range])
• Ví dụ: =AVERAGEIF(A1:A10, ">10", B1:B10) sẽ tính trung bình các giá trị trong cột B nếu giá trị tương ứng trong cột A lớn hơn 10.

AVERAGEIF rất hữu ích khi bạn cần tính trung bình của các điểm số đạt trên ngưỡng điểm cụ thể, hoặc trung bình của các lô hàng có số lượng trên mức qui định.

5.4. AVERAGEIFS: Tính trung bình theo nhiều điều kiện

Hàm AVERAGEIFS là một sự mở rộng của AVERAGEIF, cho phép bạn tính trung bình của các giá trị dựa trên nhiều điều kiện. Điều này giúp việc tính toán trở nên linh hoạt và chính xác hơn.

• Cú pháp: =AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
• Ví dụ: =AVERAGEIFS(B1:B10, A1:A10, ">10", C1:C10, "<5") sẽ tính trung bình các giá trị trong cột B nếu giá trị tương ứng trong cột A lớn hơn 10 và giá trị trong cột C nhỏ hơn 5.

AVERAGEIFS cho phép bạn thực hiện các phân tích chi tiết hơn, đặc biệt trong các bài toán liên quan đến dữ liệu phức tạp.

5.5. COUNTIF: Đếm theo điều kiện

Hàm COUNTIF giúp bạn đếm số lượng các giá trị trong một phạm vi dựa trên một điều kiện cụ thể.

• Cú pháp: =COUNTIF(range, criteria)
• Ví dụ: =COUNTIF(A1:A10, ">10") sẽ đếm số ô có giá trị lớn hơn 10 trong phạm vi từ A1 đến A10.

Hàm COUNTIF rất hữu ích khi bạn cần đếm số lô hàng đạt số lượng tối thiểu, hoặc số lượng học sinh đạt điểm trên mức qui định.

5.6. COUNTIFS: Đếm theo nhiều điều kiện

Hàm COUNTIFS là một sự mở rộng của COUNTIF, cho phép bạn đếm số lượng các giá trị dựa trên nhiều điều kiện khác nhau.

• Cú pháp: =COUNTIFS(range1, criteria1, [range2, criteria2], ...)
• Ví dụ: =COUNTIFS(A1:A10, ">10", B1:B10, "<5") sẽ đếm số ô có giá trị trong cột A lớn hơn 10 và giá trị trong cột B nhỏ hơn 5.

COUNTIFS rất thuận tiện khi bạn cần đếm các đối tượng thỏa mãn nhiều tiêu chí, từ đó giúp bạn có cái nhìn tổng thể và chi tiết hơn về dữ liệu.

5.7. LOOKUP: Tìm kiếm giá trị gần nhất

Hàm LOOKUP giúp bạn tìm kiếm một giá trị trong một phạm vi và trả về giá trị gần nhất. Điều này giống như việc tìm một từ trong từ điển và nhận lại từ gần nghĩa nhất.

• Cú pháp: =LOOKUP(lookup_value, lookup_vector, [result_vector])
• Ví dụ: =LOOKUP(85, {60, 70, 80, 90, 100}) sẽ trả về 90, vì đây là giá trị gần nhất với 85.

LOOKUP rất hữu ích khi bạn cần tìm kiếm giá trị gần nhất với một giá trị cho trước, thay vì phải tìm kiếm chính xác. Điều này đặc biệt quan trọng khi bạn làm việc với các bảng tra cứu hoặc dữ liệu không hoàn toàn chính xác.

6. Công thức sử dụng với Pivot Table

Pivot Table là một công cụ mạnh mẽ trong Excel giúp bạn tóm tắt, phân tích và khám phá dữ liệu từ một bảng dữ liệu lớn. Các công thức dưới đây giúp bạn tận dụng tối đa khả năng của Pivot Table.

6.1. GETPIVOTDATA: Lấy dữ liệu từ Pivot Table

Hàm GETPIVOTDATA giúp bạn trích xuất các giá trị cụ thể từ một Pivot Table. Nó giống như việc bạn lấy một phần dữ liệu từ một báo cáo tổng hợp để sử dụng trong phân tích chi tiết.

• Cú pháp: =GETPIVOTDATA(data_field, pivot_table_range, [field1, item1], [field2, item2], ...)
• Ví dụ: =GETPIVOTDATA("Tổng_Doanh_Thu", A1:E10, "Sản_Phẩm", "Sản_Phẩm_A") sẽ lấy giá trị tổng Doanh Thu của Sản Phẩm A từ bảng Pivot Table.

GETPIVOTDATA rất hữu ích khi bạn cần trích xuất dữ liệu cụ thể từ Pivot Table để thực hiện các phân tích chi tiết mà không cần phải tái tạo lại bảng.

6.2. SUMIFS: Tính tổng theo điều kiện từ Pivot Table

Hàm SUMIFS không chỉ dùng để tính tổng theo nhiều điều kiện từ dữ liệu thông thường mà còn có thể áp dụng cực kỳ hiệu quả với dữ liệu từ Pivot Table.

• Cú pháp: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
• Ví dụ: =SUMIFS(B1:B10, A1:A10, "Hà Nội", C1:C10, ">500") sẽ tính tổng các giá trị trong cột B, với điều kiện cột A là “Hà Nội” và cột C lớn hơn 500.

SUMIFS rất hữu ích khi bạn cần tính tổng dữ liệu thỏa mãn nhiều điều kiện từ Pivot Table, giúp bạn có cái nhìn chi tiết hơn về dữ liệu.

7. Công thức chuyển đổi dữ liệu

Các công thức chuyển đổi dữ liệu trong Excel giúp bạn chuyển đổi giữa các loại dữ liệu khác nhau, từ văn bản sang số, từ số sang văn bản hoặc chuyển đổi đơn vị đo lường.

7.1. VALUE: Chuyển đổi văn bản sang số

Hàm VALUE giúp bạn chuyển đổi văn bản chứa số thành số thực sự. Điều này giống như việc biến một biểu thức viết tay thành một số cụ thể.

• Cú pháp: =VALUE(text)
• Ví dụ: =VALUE("123") sẽ trả về số 123.

VALUE rất hữu ích khi bạn làm việc với dữ liệu nhập vào từ hệ thống khác, nơi mà số liệu có thể được lưu dưới dạng văn bản.

7.2. TEXT: Chuyển đổi số sang văn bản

Hàm TEXT giúp bạn chuyển đổi số thành văn bản theo một định dạng cụ thể. Nó giống như việc bạn định dạng lại một con số để hiển thị theo cách bạn muốn.

• Cú pháp: =TEXT(value, format_text)
• Ví dụ: =TEXT(123.456, "0.00") sẽ trả về “123.46”.

TEXT đặc biệt hữu ích khi bạn cần chuẩn hoá hiển thị số liệu trong báo cáo hoặc chuyển đổi số liệu thành văn bản để sử dụng trong các công cụ khác.

7.3. CONVERT: Chuyển đổi đơn vị

Hàm CONVERT giúp bạn chuyển đổi giữa các đơn vị đo lường khác nhau, từ mét sang feet, từ kilogram sang pound, v.v.

• Cú pháp: =CONVERT(number, "from_unit", "to_unit")
• Ví dụ: =CONVERT(10, "m", "ft") sẽ chuyển đổi 10 mét sang feet, trả về kết quả 32.81.

CONVERT rất cần thiết khi bạn làm việc với dữ liệu từ nhiều nguồn khác nhau với các đơn vị đo lường khác nhau.

8. Công thức xử lý lỗi

Trong quá trình làm việc với Excel, việc xảy ra lỗi là không thể tránh khỏi. Các công thức xử lý lỗi giúp bạn kiểm tra và xử lý các lỗi này một cách hiệu quả.

8.1. ISERROR: Kiểm tra lỗi

Hàm ISERROR giúp bạn kiểm tra xem một công thức có trả về lỗi hay không. Điều này tương tự như việc bạn kiểm tra từng lỗi trong một danh sách dài các công thức.

• Cú pháp: =ISERROR(value)
• Ví dụ: =ISERROR(A2/B2) sẽ trả về TRUE nếu có lỗi (ví dụ: chia cho 0) và FALSE nếu không có lỗi.

ISERROR rất hữu ích khi bạn cần xác định các vị trí lỗi trong bảng tính và xử lý chúng một cách hiệu quả.

8.2. ISNA: Kiểm tra lỗi #N/A

Hàm ISNA giúp bạn kiểm tra xem một giá trị có phải là lỗi #N/A hay không. Điều này rất quan trọng khi bạn làm việc với các công thức tìm kiếm và tra cứu trong Excel.

• Cú pháp: =ISNA(value)
• Ví dụ: =ISNA(VLOOKUP("Tìm kiếm", B2:E10, 2, FALSE)) sẽ trả về TRUE nếu không tìm thấy giá trị cần tìm.

ISNA giúp bạn nhận diện lỗi #N/A và xử lý chúng một cách hiệu quả, ví dụ như thay thế bằng giá trị khác.

8.3. IFERROR: Xử lý lỗi

Hàm IFERROR giúp bạn xử lý lỗi bằng cách trả về một giá trị thay thế nếu công thức gặp lỗi. Điều này giống như việc bạn thay thế một phần hỏng trong máy móc bằng một phần khác.

• Cú pháp: =IFERROR(value, value_if_error)
• Ví dụ: =IFERROR(A2/B2, "Lỗi chia 0") sẽ trả về “Lỗi chia 0” nếu công thức A2/B2 có lỗi.

IFERROR rất hữu ích khi bạn cần xử lý và làm sạch dữ liệu trong các báo cáo lớn, giúp tạo ra một báo cáo chuyên nghiệp hơn.

8.4. IFNA: Xử lý lỗi #N/A

Hàm IFNA giúp bạn xử lý lỗi #N/A bằng cách trả về một giá trị thay thế. Nó giống như việc bạn chắp vá lỗ thủng trong một tấm vải lớn để đảm bảo không bị rò rỉ.

• Cú pháp: =IFNA(value, value_if_na)
• Ví dụ: =IFNA(VLOOKUP("Tìm kiếm", B2:E10, 2, FALSE), "Không tìm thấy") sẽ trả về “Không tìm thấy” nếu không tìm thấy giá trị cần tìm.

IFNA giúp bạn tránh việc hiển thị các lỗi #N/A, đảm bảo tính thẩm mỹ và chính xác cho báo cáo.

Trên đây là hơn 40 công thức tính excel cơ bản, bạn là 1 nhân viên văn phòng sẽ thường xuyên cần tới vì vậy hãy lưu lại bài viết này sẽ có lúc bạn cần tới nó nhé. Chúng tôi sẽ tiếp tục cập nhật thêm các công thức cần thiết khác ở phía dưới mời bạn theo dõi nhé!