Bài 2: Essential Excel Formulas

1. Formula Fundamentals

1.1 Cấu trúc Formula

Mọi formula trong Excel đều bắt đầu bằng =:

Text

1=FUNCTION_NAME(argument1, argument2, ...)

Ví dụ:

Text

1=SUM(A1:A10)           → Cộng từ A1 đến A10
2=IF(A1>100, "High", "Low")  → Điều kiện logic
3=VLOOKUP(A1, B:C, 2, FALSE) → Tìm kiếm dữ liệu

1.2 Cell References

Type	Example	Behavior
Relative	A1	Changes when copied
Absolute	$A$ 1	Never changes
Mixed	$A1 or A$ 1	Partially fixed

Pro Tip: Nhấn F4 để cycle qua các reference types:

Text

1A1 → $A$1 → A$1 → $A1 → A1

1.3 Reference Examples

Text

1Original formula in B2: =A2*$C$1
2 
3Copy to B3:
4  =A3*$C$1   (A2 changes to A3, $C$1 stays fixed)
5 
6Copy to C2:
7  =B2*$C$1   (A2 changes to B2, $C$1 stays fixed)

2. Math Functions

2.1 SUM Family

excel

1=SUM(A1:A10)           # Cộng tất cả
2=SUM(A1:A10, C1:C10)   # Cộng nhiều ranges
3=SUMIF(A:A, ">100")    # Cộng có điều kiện
4=SUMIFS(C:C, A:A, "North", B:B, ">1000")  # Nhiều điều kiện

SUMIF Syntax:

Text

1=SUMIF(range, criteria, [sum_range])
2 
3Ví dụ: Tổng revenue của region "North"
4=SUMIF(B:B, "North", D:D)

SUMIFS Syntax (Multiple Criteria):

Text

1=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2, ...)
2 
3Ví dụ: Tổng revenue của "North" và Product = "Laptop"
4=SUMIFS(D:D, B:B, "North", A:A, "Laptop")

2.2 AVERAGE, COUNT, MAX, MIN

excel

1=AVERAGE(A1:A100)      # Trung bình
2=AVERAGEIF(B:B, "North", C:C)  # TB có điều kiện
3 
4=COUNT(A1:A100)        # Đếm số (numbers only)
5=COUNTA(A1:A100)       # Đếm non-empty cells
6=COUNTIF(A:A, ">100")  # Đếm có điều kiện
7=COUNTIFS(A:A, ">100", B:B, "North")  # Nhiều điều kiện
8 
9=MAX(A1:A100)          # Giá trị lớn nhất
10=MIN(A1:A100)          # Giá trị nhỏ nhất

2.3 Practice Exercise

Sample Data:

Product	Region	Revenue	Units
Laptop	North	15000	10
Phone	South	8000	20
Tablet	North	6000	15
Laptop	South	12000	8
Phone	North	4000	10

Tasks:

Tổng Revenue: =SUM(C2:C6) → 45000
Revenue North: =SUMIF(B:B,"North",C:C) → 25000
Đếm North: =COUNTIF(B:B,"North") → 3
TB Units: =AVERAGE(D2:D6) → 12.6

3. Logical Functions

3.1 IF Function

Syntax:

Text

1=IF(logical_test, value_if_true, value_if_false)

Examples:

excel

1=IF(A1>=60, "Pass", "Fail")
2=IF(B1="North", "Zone A", "Zone B")
3=IF(C1>1000, C1*0.1, 0)  # 10% bonus if >1000

3.2 Nested IF

excel

1# Grade classification
2=IF(A1>=90, "A", IF(A1>=80, "B", IF(A1>=70, "C", IF(A1>=60, "D", "F"))))

Better Alternative - IFS (Excel 2019+):

excel

1=IFS(A1>=90, "A", A1>=80, "B", A1>=70, "C", A1>=60, "D", TRUE, "F")

3.3 AND, OR, NOT

excel

1# AND - Tất cả điều kiện đúng
2=IF(AND(A1>100, B1="North"), "Priority", "Normal")
3 
4# OR - Ít nhất 1 điều kiện đúng  
5=IF(OR(A1>1000, B1="VIP"), "Important", "Regular")
6 
7# NOT - Đảo ngược
8=IF(NOT(A1="Cancelled"), "Active", "Inactive")
9 
10# Combined
11=IF(AND(A1>100, OR(B1="North", B1="South")), "Target", "Other")

3.4 IFERROR

Xử lý lỗi gracefully:

excel

1# Thay vì hiển thị #DIV/0!
2=IFERROR(A1/B1, 0)
3 
4# Thay vì #N/A từ VLOOKUP
5=IFERROR(VLOOKUP(A1, Data, 2, FALSE), "Not Found")

4. Lookup Functions

4.1 VLOOKUP

The Most Used Lookup Function

Text

1=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Parameters:

lookup_value: Giá trị cần tìm
table_array: Bảng dữ liệu (lookup value phải ở cột đầu tiên)
col_index_num: Số thứ tự cột kết quả (1, 2, 3...)
range_lookup: FALSE = exact match, TRUE = approximate

Example:

Text

1Product Table (A1:C5):
2   A          B         C
31  ProductID  Name      Price
42  P001       Laptop    1500
53  P002       Phone     800
64  P003       Tablet    600
7 
8Lookup:
9=VLOOKUP("P002", A2:C5, 2, FALSE)  → "Phone"
10=VLOOKUP("P002", A2:C5, 3, FALSE)  → 800

4.2 XLOOKUP (Excel 365)

Modern replacement for VLOOKUP:

Text

1=XLOOKUP(lookup_value, lookup_array, return_array, [not_found], [match_mode])

Advantages over VLOOKUP:

✅ Can look left (VLOOKUP only looks right)
✅ Returns array directly (no column index needed)
✅ Built-in error handling
✅ More intuitive syntax

Example:

excel

1=XLOOKUP("P002", A:A, C:C, "Not Found")  → 800
2=XLOOKUP("P002", A:A, B:C)  → Returns both Name and Price

4.3 INDEX + MATCH (Universal Solution)

Most flexible lookup method:

Text

1=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Example:

excel

1# Find Price of "Phone"
2=INDEX(C:C, MATCH("Phone", B:B, 0))  → 800
3 
4# Two-way lookup (row and column)
5=INDEX(Data, MATCH("Phone", RowHeaders, 0), MATCH("Q2", ColHeaders, 0))

Why INDEX+MATCH is powerful:

✅ Lookup column can be anywhere
✅ Two-dimensional lookups
✅ More flexible than VLOOKUP
✅ Works in all Excel versions

4.4 Lookup Comparison

Feature	VLOOKUP	XLOOKUP	INDEX+MATCH
Look Left	❌	✅	✅
Multiple Results	❌	✅	Manual
Error Handling	IFERROR	Built-in	IFERROR
Compatibility	All versions	365 only	All versions
Performance	Good	Best	Good

5. Text Functions

5.1 Text Extraction

excel

1=LEFT(A1, 3)           # First 3 characters
2=RIGHT(A1, 4)          # Last 4 characters
3=MID(A1, 2, 5)         # 5 chars starting from position 2
4=LEN(A1)               # Length of text

Example:

Text

1A1 = "LAPTOP-001"
2=LEFT(A1, 6)    → "LAPTOP"
3=RIGHT(A1, 3)   → "001"
4=MID(A1, 1, 6)  → "LAPTOP"

5.2 Text Transformation

excel

1=UPPER(A1)             # "HELLO"
2=LOWER(A1)             # "hello"
3=PROPER(A1)            # "Hello World"
4=TRIM(A1)              # Remove extra spaces
5=CLEAN(A1)             # Remove non-printable chars

5.3 Text Combination

excel

1=CONCAT(A1, " ", B1)   # Join text
2=TEXTJOIN(", ", TRUE, A1:A10)  # Join with delimiter
3=A1 & " " & B1         # Concatenate operator

5.4 FIND and SUBSTITUTE

excel

1=FIND("@", A1)         # Position of @ (case-sensitive)
2=SEARCH("@", A1)       # Position of @ (case-insensitive)
3=SUBSTITUTE(A1, "old", "new")  # Replace text
4=REPLACE(A1, 1, 3, "NEW")      # Replace by position

Practical Example - Extract Domain from Email:

excel

1Email: john@company.com
2=MID(A1, FIND("@", A1)+1, 100)  → "company.com"

6. Date & Time Functions

6.1 Current Date/Time

excel

1=TODAY()               # Current date (updates daily)
2=NOW()                 # Current date + time

6.2 Date Extraction

excel

1=YEAR(A1)              # Year from date
2=MONTH(A1)             # Month (1-12)
3=DAY(A1)               # Day (1-31)
4=WEEKDAY(A1)           # Day of week (1=Sunday)
5=WEEKNUM(A1)           # Week number

6.3 Date Calculations

excel

1=DATEDIF(A1, B1, "d")  # Days between dates
2=DATEDIF(A1, B1, "m")  # Months between dates
3=DATEDIF(A1, B1, "y")  # Years between dates
4 
5=EDATE(A1, 3)          # Add 3 months
6=EOMONTH(A1, 0)        # End of current month
7=EOMONTH(A1, 1)        # End of next month
8 
9=NETWORKDAYS(A1, B1)   # Working days (excl. weekends)

6.4 Date Creation

excel

1=DATE(2026, 1, 15)     # Create date: 1/15/2026
2=DATEVALUE("1/15/2026") # Convert text to date

7. Practical Exercises

Exercise 1: Sales Analysis

Data Setup:

OrderID	Date	Product	Region	Revenue	Cost
001	1/5/2026	Laptop	North	15000	10000
002	1/8/2026	Phone	South	8000	5000
...	...	...	...	...	...

Tasks:

Calculate Profit: =E2-F2
Profit Margin %: =G2/E2
Total Revenue North: =SUMIF(D:D,"North",E:E)
Count Laptops: =COUNTIF(C:C,"Laptop")
Avg Profit South: =AVERAGEIF(D:D,"South",G:G)

Exercise 2: Grade Calculator

Data:

Student	Midterm	Final	Attendance
John	75	80	95%
Jane	85	90	100%
Bob	60	55	80%

Formulas:

excel

1# Weighted Average (40% mid, 50% final, 10% attendance)
2=B2*0.4 + C2*0.5 + D2*10
3 
4# Letter Grade
5=IFS(E2>=90,"A", E2>=80,"B", E2>=70,"C", E2>=60,"D", TRUE,"F")
6 
7# Pass/Fail
8=IF(AND(E2>=60, D2>=75%), "Pass", "Fail")

Exercise 3: Product Lookup System

Product Table:

Code	Name	Price	Category
P001	Laptop	1500	Electronics
P002	Phone	800	Electronics
P003	Desk	300	Furniture

Order Entry:

excel

1# User enters Code in A1
2# Auto-fill other columns:
3 
4Name: =VLOOKUP(A1, Products, 2, FALSE)
5Price: =VLOOKUP(A1, Products, 3, FALSE)
6Category: =VLOOKUP(A1, Products, 4, FALSE)

8. Formula Debugging

8.1 Common Errors

Error	Meaning	Solution
`#VALUE!`	Wrong data type	Check inputs
`#REF!`	Invalid reference	Fix deleted cells
`#DIV/0!`	Division by zero	Use IFERROR
`#N/A`	Lookup not found	Check lookup value
`#NAME?`	Unknown function	Check spelling
`#NUM!`	Invalid number	Check calculation

8.2 Debugging Tools

excel

1# Show all formulas
2Ctrl + `
3 
4# Evaluate Formula step by step
5Formulas tab → Evaluate Formula
6 
7# Trace Precedents/Dependents
8Formulas tab → Trace Precedents

8.3 Error Handling Best Practice

excel

1# Always wrap lookups with IFERROR
2=IFERROR(VLOOKUP(...), "Not Found")
3 
4# Handle division by zero
5=IF(B1=0, 0, A1/B1)
6# Or: =IFERROR(A1/B1, 0)

9. Bài tập về nhà

Bài 1: Formula Practice

Tạo file với 50 rows sales data và calculate:

Total Revenue by Region (SUMIF)
Count of products > $1000 (COUNTIF)
Profit Margin với conditional formatting

Bài 2: Lookup Challenge

Tạo Invoice system với:

Product lookup (VLOOKUP or XLOOKUP)
Auto-calculate line totals
Grand total with tax

Bài 3: Date Analysis

Với sales data có dates:

Extract Month, Year
Calculate days since order
Group by Week number

Summary

Trong bài này bạn đã học:

✅ Cell references (Relative, Absolute, Mixed)
✅ Math functions (SUM, AVERAGE, COUNT families)
✅ Logical functions (IF, AND, OR, IFERROR)
✅ Lookup functions (VLOOKUP, XLOOKUP, INDEX+MATCH)
✅ Text functions (LEFT, RIGHT, MID, CONCAT)
✅ Date functions (TODAY, YEAR, DATEDIF)
✅ Error handling và debugging

Next: Bài 3 - PivotTables Mastery - Dynamic data analysis