1. Các hàm tính toán cơ bản (SUM, AVERAGE, MAX, MIN)
Các hàm tính toán cơ bản là nền tảng để tự động hóa việc xử lý dữ liệu số trong Excel. Thay vì thực hiện các phép tính thủ công, việc sử dụng hàm giúp tiết kiệm thời gian, tăng độ chính xác và dễ dàng quản lý các bảng tính lớn.
Cấu trúc chung của một hàm trong Excel
Một hàm là một công thức được định nghĩa trước, thực hiện một phép tính cụ thể. Cấu trúc của hàm luôn tuân thủ các quy tắc sau:
- Bắt đầu bằng dấu bằng (
=). - Tiếp theo là Tên hàm (ví dụ: SUM, AVERAGE).
- Cuối cùng là một cặp dấu ngoặc đơn
(). Bên trong dấu ngoặc là các Đối số (Arguments), đây là các giá trị đầu vào cho hàm. Các đối số có thể là số, văn bản, tham chiếu ô (ví dụ: A1), hoặc một vùng dữ liệu (ví dụ: A1:B10).
Quy trình thực hiện: Chèn hàm vào ô tính
- Nhập dấu = vào ô muốn chứa kết quả.
- Gõ tên hàm, ví dụ SUM. Excel sẽ hiển thị một danh sách gợi ý (IntelliSense).
- Khi hàm mong muốn được tô sáng, nhấn phím Tab để Excel tự động hoàn thành tên hàm và mở dấu ngoặc đơn.
- Nhập hoặc chọn các đối số cho hàm.
- Đóng dấu ngoặc đơn ) và nhấn Enter.
Lưu ý:
Có thể sử dụng Function Library (Thư viện Hàm) trên tab Formulas để tìm kiếm và chèn hàm theo danh mục. Khi chọn một hàm, Excel sẽ khởi chạy một hộp thoại hướng dẫn (Function Arguments) để hỗ trợ nhập các đối số.
Hàm SUM: Tính tổng
Hàm SUM được sử dụng để cộng tất cả các giá trị số trong một phạm vi ô được chỉ định. Đây là một trong những hàm được sử dụng thường xuyên nhất.
- Cú pháp: =SUM(number1, [number2], …)
- Chức năng: Cộng các giá trị trong các đối số. Đối số có thể là các số, tham chiếu ô hoặc vùng dữ liệu.
Hàm AVERAGE: Tính trung bình cộng
Hàm AVERAGE trả về giá trị trung bình cộng của các đối số. Hàm sẽ tự động cộng tất cả các giá trị số và chia cho số lượng các giá trị đó.
- Cú pháp: =AVERAGE(number1, [number2], …)
- Chức năng: Tính trung bình của một dãy số. Hàm này bỏ qua các ô chứa văn bản hoặc ô trống.
Hàm MAX: Tìm giá trị lớn nhất
Hàm MAX được dùng để tìm và trả về giá trị số lớn nhất trong một tập hợp các giá trị.
- Cú pháp: =MAX(number1, [number2], …)
- Chức năng: Quét qua các đối số và trả về số có giá trị cao nhất. Hàm này bỏ qua các ô trống, văn bản, và các giá trị logic (TRUE/FALSE).
Hàm MIN: Tìm giá trị nhỏ nhất
Ngược lại với hàm MAX, hàm MIN trả về giá trị số nhỏ nhất trong một tập hợp các giá trị.
- Cú pháp: =MIN(number1, [number2], …)
- Chức năng: Quét qua các đối số và trả về số có giá trị thấp nhất. Tương tự hàm MAX, hàm này cũng bỏ qua các ô trống, văn bản và giá trị logic.
2. Các hàm đếm dữ liệu (COUNT, COUNTA, COUNTBLANK)
Trong phân tích dữ liệu, việc xác định số lượng mục dữ liệu là một yêu cầu phổ biến. Excel cung cấp một nhóm các hàm đếm chuyên dụng, mỗi hàm phục vụ một mục đích khác nhau.
Hàm COUNT: Đếm các ô chứa số
Hàm COUNT được sử dụng để đếm số lượng ô chứa dữ liệu dạng số trong một vùng được chỉ định.
- Cú pháp: =COUNT(value1, [value2], …)
- Chức năng: Đếm các ô chứa số, bao gồm số âm, phần trăm, ngày, giờ, và phân số.
- Lưu ý: Hàm COUNT sẽ bỏ qua các ô trống, ô chứa văn bản, và các giá trị lỗi.
Hàm COUNTA: Đếm các ô không rỗng
Hàm COUNTA (viết tắt của “count all”) đếm tất cả các ô không trống trong một vùng dữ liệu, bất kể nội dung của chúng là gì.
- Cú pháp: =COUNTA(value1, [value2], …)
- Chức năng: Đếm các ô có chứa bất kỳ loại thông tin nào, bao gồm số, văn bản, giá trị logic, giá trị lỗi, và chuỗi trống (“”).
- Lưu ý: Hàm COUNTA chỉ không đếm các ô hoàn toàn trống.
Hàm COUNTBLANK: Đếm các ô rỗng
Hàm COUNTBLANK thực hiện nhiệm vụ ngược lại với COUNTA, nó chỉ đếm các ô trống trong một vùng dữ liệu.
- Cú pháp: =COUNTBLANK(range)
- Chức năng: Đếm số lượng ô không chứa bất kỳ giá trị nào.
- Lưu ý: Một ô chứa công thức trả về chuỗi trống (“”) vẫn được tính là ô trống bởi hàm COUNTBLANK. Tuy nhiên, một ô chứa số không (0) sẽ không được tính là ô trống.
3. Hàm logic IF và các toán tử so sánh
Hàm IF là một trong những hàm mạnh mẽ và phổ biến nhất trong Excel. Nó cho phép thực hiện một phép so sánh logic giữa các giá trị và trả về một kết quả nếu điều kiện đó đúng, và một kết quả khác nếu điều kiện đó sai.
Cấu trúc của hàm IF
Hàm IF yêu cầu ba đối số: một bài kiểm tra logic và hai kết quả trả về có thể có.
- Cú pháp:
=IF(logical_test, value_if_true, value_if_false) - Giải thích các đối số:
- logical_test (bắt buộc): Là một giá trị hoặc biểu thức có thể được đánh giá là TRUE (Đúng) hoặc FALSE (Sai). Đây chính là điều kiện cần kiểm tra.
- value_if_true (tùy chọn): Là giá trị được trả về nếu logical_test là TRUE.
- value_if_false (tùy chọn): Là giá trị được trả về nếu logical_test là FALSE.
Ví dụ: Công thức =IF(A1>100, "Tốt", "Chưa đạt") sẽ kiểm tra xem giá trị trong ô A1 có lớn hơn 100 không. Nếu có, nó trả về chữ “Tốt”; nếu không, nó trả về “Chưa đạt”.
Sử dụng các toán tử so sánh
Để xây dựng đối số logical_test trong hàm IF, các toán tử so sánh được sử dụng để thiết lập điều kiện.
| Toán tử | Mô tả | Ví dụ |
|---|---|---|
| = | Bằng | A1 = B1 |
| > | Lớn hơn | A1 > 100 |
| < | Nhỏ hơn | A1 < 50 |
| >= | Lớn hơn hoặc bằng | A1 >= 65 |
| <= | Nhỏ hơn hoặc bằng | A1 <= B1 |
| <> | Không bằng (khác) | A1 <> “Hoàn thành” |
Bài tập ứng dụng và củng cố kiến thức
Bài tập 1: Sử dụng hàm SUM để tính tổng
- Tệp dữ liệu thực hành: L3_T1_try1_sum_function_starter.xlsx
| Yêu cầu (Tiếng Anh) | Yêu cầu (Tiếng Việt) |
|---|---|
| 1. In cell F2, input the column header Total. | 1. Trong ô F2, nhập tiêu đề cột là Total. |
| 2. In cell A9, input the column header Total. | 2. Trong ô A9, nhập tiêu đề cột là Total. |
| 3. In cell F3, use the Function Library to enter a function that sums B3 to E3. | 3. Trong ô F3, sử dụng Thư viện Hàm để nhập một hàm tính tổng từ B3 đến E3. |
| 4. Copy the formula from cell F3 to cell F4 through to cell F8. | 4. Sao chép công thức từ ô F3 xuống các ô từ F4 đến F8. |
| 5. In cell B9, use the Insert Function wizard to enter a function that sums B3 to B8. | 5. Trong ô B9, sử dụng trình hướng dẫn Chèn Hàm để nhập một hàm tính tổng từ B3 đến B8. |
| 6. Copy the formula from cell B9 to cell C9 through to cell F9. | 6. Sao chép công thức từ ô B9 sang các ô từ C9 đến F9. |
| 7. Select cells B9 to F9 and format a Top and Double Bottom Border for the range of cells. | 7. Chọn các ô từ B9 đến F9 và định dạng một đường viền trên và viền kép dưới cho vùng ô này. |
| 8. Save and close the file. | 8. Lưu và đóng tệp. |
Hướng dẫn giải chi tiết:
- Mở tệp L3_T1_try1_sum_function_starter.xlsx.
- Nhập “Total” vào các ô F2 và A9.
- Tại ô F3, nhập công thức =SUM(B3:E3) và nhấn Enter.
- Chọn ô F3, di chuột đến góc dưới bên phải của ô cho đến khi con trỏ chuyển thành dấu cộng màu đen, sau đó kéo xuống đến ô F8.
- Tại ô B9, nhập công thức =SUM(B3:B8) và nhấn Enter.
- Chọn ô B9 và kéo ngang sang phải đến ô F9.
- Bôi đen vùng B9:F9, vào tab Home, nhóm Font, chọn biểu tượng Borders và chọn Top and Double Bottom Border.
- Lưu lại tệp.
Bài tập 2: Sử dụng hàm AVERAGE để tính trung bình
- Tệp dữ liệu thực hành: L3_T1_try2_average_function_starter.xlsx
| Yêu cầu (Tiếng Anh) | Yêu cầu (Tiếng Việt) |
|---|---|
| 1. In cell G3, use the Insert Function wizard to enter a function that averages cells B3 to E3. | 1. Trong ô G3, sử dụng trình hướng dẫn Chèn Hàm để nhập một hàm tính trung bình các ô từ B3 đến E3. |
| 2. Copy the formula from cell G3 to cell G4 through to cell G9. | 2. Sao chép công thức từ ô G3 xuống các ô từ G4 đến G9. |
| 3. Save and close the file. | 3. Lưu và đóng tệp. |
Hướng dẫn giải chi tiết:
- Mở tệp L3_T1_try2_average_function_starter.xlsx.
- Tại ô G3, nhập công thức =AVERAGE(B3:E3) và nhấn Enter.
- Chọn ô G3 và kéo công thức xuống đến ô G9.
- Lưu lại tệp.
Bài tập 3: Sử dụng hàm MAX và MIN
- Tệp dữ liệu thực hành (Hàm MAX): L3_T2_try1_max_monthly_usage_starter.xlsx
- Tệp dữ liệu thực hành (Hàm MIN): L3_T2_try2_min_monthly_usage_starter.xlsx
| Yêu cầu (Tiếng Anh) | Yêu cầu (Tiếng Việt) |
|---|---|
| Hàm MAX | Hàm MAX |
| 1. In cell A16, input the row header Max Usage. | 1. Tại ô A16, nhập tiêu đề hàng là Max Usage. |
| 2. In cell B16, use the Function Library to enter a function that finds the maximum value for energy usage in 2012. | 2. Tại ô B16, sử dụng Thư viện Hàm để nhập một hàm tìm giá trị lớn nhất cho mức sử dụng năng lượng trong năm 2012. |
| 3. Copy the formula from cell B16 to cell C16 through to cell I16. | 3. Sao chép công thức từ ô B16 sang các ô từ C16 đến I16. |
| Hàm MIN | Hàm MIN |
| 1. In cell A17, input the row header Min Usage. | 1. Tại ô A17, nhập tiêu đề hàng là Min Usage. |
| 2. In cell B17, use the Function Library to enter a function that finds the minimum value for energy usage in 2012. | 2. Tại ô B17, sử dụng Thư viện Hàm để nhập một hàm tìm giá trị nhỏ nhất cho mức sử dụng năng lượng trong năm 2012. |
| 3. Copy the formula from cell B17 to cell C17 through to cell I17. | 3. Sao chép công thức từ ô B17 sang các ô từ C17 đến I17. |
Hướng dẫn giải chi tiết:
- Với hàm MAX:
- Mở tệp L3_T2_try1_max_monthly_usage_starter.xlsx.
- Tại ô A16, nhập “Max Usage”.
- Giả sử dữ liệu năm 2012 nằm trong vùng B4:B15. Tại ô B16, nhập công thức =MAX(B4:B15).
- Kéo công thức từ B16 sang phải đến I16.
- Với hàm MIN:
- Mở tệp L3_T2_try2_min_monthly_usage_starter.xlsx.
- Tại ô A17, nhập “Min Usage”.
- Giả sử dữ liệu năm 2012 nằm trong vùng B4:B15. Tại ô B17, nhập công thức =MIN(B4:B15).
- Kéo công thức từ B17 sang phải đến I17.
Bài tập 4: Sử dụng hàm COUNT và COUNTA
- Tệp dữ liệu thực hành (Hàm COUNT): L4_T1_try1_count_producebox_starter.xlsx
- Tệp dữ liệu thực hành (Hàm COUNTA): L4_T1_try2_counta_producebox_starter.xlsx
| Yêu cầu (Tiếng Anh) | Yêu cầu (Tiếng Việt) |
|---|---|
| Hàm COUNT | Hàm COUNT |
| 1. On the Sales tab, in cell E10, use the Function Library to enter a function that counts how many Produce Box types have been sold to shareholders. | 1. Trên tab Sales, tại ô E10, sử dụng Thư viện Hàm để nhập một hàm đếm có bao nhiêu loại Hộp Sản phẩm đã được bán cho cổ đông. |
| 2. In cell B5, enter 150. Does the count change? | 2. Trong ô B5, nhập 150. Kết quả đếm có thay đổi không? |
| 3. In cell E7, enter the text Yes. Does the count change? | 3. Trong ô E7, nhập văn bản “Yes”. Kết quả đếm có thay đổi không? |
| Hàm COUNTA | Hàm COUNTA |
| 1. On the Allocation tab, in cell G9, use the Function Library to enter a function that counts how many Produce Box types have been allocated to shareholders. | 1. Trên tab Allocation, tại ô G9, sử dụng Thư viện Hàm để nhập một hàm đếm có bao nhiêu loại Hộp Sản phẩm đã được phân bổ cho cổ đông. |
| 2. In cells D5 and H4 enter Yes. Does the count change? | 2. Trong các ô D5 và H4 nhập “Yes”. Kết quả đếm có thay đổi không? |
| 3. In cells B5 and G6 enter 100. Does the count change? | 3. Trong các ô B5 và G6 nhập 100. Kết quả đếm có thay đổi không? |
Hướng dẫn giải chi tiết:
- Với hàm COUNT:
- Mở tệp, chuyển đến tab Sales.
- Tại ô E10, nhập công thức =COUNT(…) với vùng dữ liệu bán hàng cho cổ đông.
- Khi nhập 150 vào B5, kết quả đếm sẽ tăng lên vì COUNT đếm số.
- Khi nhập Yes vào E7, kết quả đếm không thay đổi vì COUNT bỏ qua văn bản.
- Với hàm COUNTA:
- Mở tệp, chuyển đến tab Allocation.
- Tại ô G9, nhập công thức =COUNTA(…) với vùng dữ liệu phân bổ.
- Khi nhập Yes, kết quả đếm sẽ tăng lên vì COUNTA đếm cả văn bản.
- Khi nhập 100, kết quả đếm cũng sẽ tăng lên vì COUNTA đếm cả số.
Bài tập 5: Sử dụng hàm COUNTBLANK
- Tệp dữ liệu thực hành: L4_T2_try_summary_starter.xlsx
| Yêu cầu (Tiếng Anh) | Yêu cầu (Tiếng Việt) |
|---|---|
| 1. On the Membership Summary tab, in cell F35, use the Function Library to enter a function that will determine how many entries are missing in the New Membership section of the table. | 1. Trên tab Membership Summary, tại ô F35, sử dụng Thư viện Hàm để nhập một hàm xác định có bao nhiêu mục bị thiếu trong phần New Membership của bảng. |
| 2. In cell F37, use the Function Library to enter a function that will determine how many entries are missing in the Renewed Membership section of the table. | 2. Tại ô F37, sử dụng Thư viện Hàm để nhập một hàm xác định có bao nhiêu mục bị thiếu trong phần Renewed Membership của bảng. |
Hướng dẫn giải chi tiết:
- Mở tệp và chuyển đến tab Membership Summary.
- Xác định vùng dữ liệu của phần New Membership. Tại ô F35, nhập công thức =COUNTBLANK(vùng_dữ_liệu_new_membership).
- Xác định vùng dữ liệu của phần Renewed Membership. Tại ô F37, nhập công thức =COUNTBLANK(vùng_dữ_liệu_renewed_membership).
Bài tập 6: Xây dựng và chỉnh sửa hàm IF
- Tệp dữ liệu thực hành (Bài 1): L5_T2_try1_matching_if_statement_starter.xlsx
- Tệp dữ liệu thực hành (Bài 2): L5_T2_try2_edit_if_statement_starter.xlsx
| Yêu cầu (Tiếng Anh) | Yêu cầu (Tiếng Việt) |
|---|---|
| Bài 1 | Bài 1 |
| 1. In the Company Match column, in cell G3, create a formula that evaluates the following: If the donation amount is over $150, display the word “Match.” If the donation amount is not over $150, display the word “No Match.” | 1. Trong cột Company Match, tại ô G3, tạo một công thức đánh giá như sau: Nếu số tiền quyên góp lớn hơn $150, hiển thị chữ “Match”. Nếu không, hiển thị “No Match”. |
| 2. Copy the formula created in cell G3 to cells G4 through to G8. | 2. Sao chép công thức đã tạo ở ô G3 xuống các ô từ G4 đến G8. |
| 5. In the Match Amount column, in cell H3, create a formula that evaluates the following: If the Company Match entry says Match, then multiply the donation amount by 2… If … No Match, then display 0. | 5. Trong cột Match Amount, tại ô H3, tạo công thức: Nếu mục Company Match là “Match”, nhân số tiền quyên góp với 2… Nếu là “No Match”, hiển thị số 0. |
| Bài 2 | Bài 2 |
| 1. In the Company Match column, in cell G3, edit the If statement as follows: If the donation amount is equal to or over $150, then multiply the donation amount by 2… If … not over $150, then display a blank cell. | 1. Trong cột Company Match, tại ô G3, chỉnh sửa câu lệnh IF như sau: Nếu số tiền quyên góp lớn hơn hoặc bằng $150, nhân số tiền đó với 2… Nếu không, hiển thị một ô trống. |
Hướng dẫn giải chi tiết:
- Bài 1:
- Mở tệp …if_statement_starter.xlsx.
- Giả sử cột số tiền quyên góp là cột F. Tại ô G3, nhập công thức: =IF(F3>150, “Match”, “No Match”).
- Kéo công thức từ G3 xuống G8.
- Tại ô H3, nhập công thức: =IF(G3=”Match”, F3*2, 0).
- Bài 2:
- Mở tệp …edit_if_statement_starter.xlsx.
- Giả sử cột số tiền quyên góp là cột F. Tại ô G3, nhập công thức: =IF(F3>=150, F3*2, “”).
- Dấu “” sẽ tạo ra một ô trống nếu điều kiện sai.
