📘 Excel Function Summary – Tin học đại
cương (NEU)
VLOOKUP
➤ Purpose: Finds a value in the first column of a range and returns a value in the same row
from another column.
➤ Syntax: =VLOOKUP(lookup_value, table_array, col_index, [range_lookup])
➤ Example: =VLOOKUP("B01", A2:C10, 2, FALSE) → Returns value in column 2 for B01
➤ When to Use:
- Lookup student names/scores using IDs
- Search inventory items
HLOOKUP
➤ Purpose: Looks for a value in the first row of a table and returns a value from a specified
row.
➤ Syntax: =HLOOKUP(lookup_value, table_array, row_index, [range_lookup])
➤ Example: =HLOOKUP("Math", A1:E3, 2, FALSE)
➤ When to Use:
- Horizontal search across subjects or headers
IF
➤ Purpose: Checks a condition and returns one value if TRUE, another if FALSE.
➤ Syntax: =IF(condition, value_if_true, value_if_false)
➤ Example: =IF(B2>=5, "Pass", "Fail")
➤ When to Use:
- Mark pass/fail, bonuses, conditions
COUNTA
➤ Purpose: Counts the number of non-empty cells in a range.
➤ Syntax: =COUNTA(range)
➤ Example: =COUNTA(A2:A20) → Counts cells with any value
➤ When to Use:
- Count filled answers or submissions
COUNT
➤ Purpose: Counts the number of numeric values in a range.
➤ Syntax: =COUNT(range)
➤ Example: =COUNT(B2:B10)
➤ When to Use:
- Count only numbers, ignore text
COUNTIF
➤ Purpose: Counts cells in a range that meet a single condition.
➤ Syntax: =COUNTIF(range, criteria)
➤ Example: =COUNTIF(B2:B10, ">=8")
➤ When to Use:
- Count scores above 8, number of males/females
SUM
➤ Purpose: Adds up numbers in a range.
➤ Syntax: =SUM(range)
➤ Example: =SUM(B2:B10)
➤ When to Use:
- Total scores, total sales, total items
SUMIF
➤ Purpose: Adds numbers in a range that meet a single condition.
➤ Syntax: =SUMIF(range, criteria, sum_range)
➤ Example: =SUMIF(A2:A10, "Math", B2:B10)
➤ When to Use:
- Total scores for subject Math
AVERAGE
➤ Purpose: Calculates the average (mean) of numbers.
➤ Syntax: =AVERAGE(range)
➤ Example: =AVERAGE(B2:B10)
➤ When to Use:
- Find average score, average salary
ROUND
➤ Purpose: Rounds a number to a specified number of digits.
➤ Syntax: =ROUND(number, num_digits)
➤ Example: =ROUND(8.678, 1) → 8.7
➤ When to Use:
- Round scores or results
CONCATENATE / &
➤ Purpose: Joins several text items into one.
➤ Syntax: =CONCATENATE(text1, text2, ...) or =A1 & " " & B1
➤ Example: =CONCATENATE(A1, " ", B1) → Joins full name
➤ When to Use:
- Create full names, labels, codes
LEFT, RIGHT, MID
➤ Purpose: Extracts part of a text string.
➤ Syntax: =LEFT(text, num_chars), =RIGHT(text, num_chars), =MID(text, start_num,
num_chars)
➤ Example: =LEFT("NEU2025", 3) → "NEU"
➤ When to Use:
- Extract codes, year, initials
LEN
➤ Purpose: Counts number of characters in a string.
➤ Syntax: =LEN(text)
➤ Example: =LEN("Hanoi") → 5
➤ When to Use:
- Validate length, passwords, codes
