Công thức và hàm Excel: Học với CÁC VÍ DỤ Cơ bản
Charlotte Miller
Công thức và hàm là nền tảng để làm việc với dữ liệu số trong Excel. Bài viết này giới thiệu tới bạn các công thức và hàm.
Dữ liệu hướng dẫn
Trong hướng dẫn này, chúng ta sẽ làm việc với các tập dữ liệu sau.
Ngân sách đồ dùng gia đình
| S / N | ITEM | SL | BẢNG GIÁ | SUBTOTAL | Nó có phải chăng không? |
|---|---|---|---|---|---|
| 1 | Xoài | 9 | 600 | ||
| 2 | Cam | 3 | 1200 | ||
| 3 | Cà chua | 1 | 2500 | ||
| 4 | Dầu ăn | 5 | 6500 | ||
| 5 | Nước bổ | 13 | 3900 |
Tiến độ dự án xây nhà
| S / N | ITEM | BẮT ĐẦU NGÀY | NGÀY CUỐI | THỜI GIAN (NGÀY) |
|---|---|---|---|---|
| 1 | Đất khảo sát | 04/02/2015 | 07/02/2015 | |
| 2 | Đặt nằm Foundation | 10/02/2015 | 15/02/2015 | |
| 3 | Mái ngói | 27/02/2015 | 03/03/2015 | |
| 4 | Sơn tường | 09/03/2015 | 21/03/2015 |
Công thức trong Excel là gì?
CÔNG THỨC TRONG EXCEL là một biểu thức hoạt động trên các giá trị trong một phạm vi địa chỉ ô và toán tử. Ví dụ: =A1+A2+A3, tìm tổng của phạm vi giá trị từ ô A1 đến ô A3. Một ví dụ về công thức được tạo thành từ các giá trị rời rạc như =6*3.
=A2 * D2 / 2
ĐÂY,
"="cho Excel biết rằng đây là một công thức và nó sẽ đánh giá nó."A2" * D2"tham chiếu đến địa chỉ ô A2 và D2 sau đó nhân các giá trị tìm thấy trong các địa chỉ ô này."/"là toán tử số học chia"2"là một giá trị rời rạc
Bài tập thực hành công thức
Chúng tôi sẽ làm việc với dữ liệu mẫu về ngân sách gia đình để tính tổng phụ.
- Tạo một sổ làm việc mới trong Excel
- Nhập dữ liệu được hiển thị trong ngân sách đồ dùng gia đình ở trên.
- Bảng tính của bạn sẽ trông như sau.
Bây giờ chúng ta sẽ viết công thức tính tổng phụ
Đặt tiêu điểm vào ô E4
Nhập công thức sau.
=C4*D4
ĐÂY,
"C4*D4"sử dụng phép nhân toán tử số học (*) để nhân giá trị của địa chỉ ô C4 và D4.
Nhấn phím enter
Bạn sẽ nhận được kết quả sau
Hình ảnh động sau đây cho bạn thấy cách tự động chọn địa chỉ ô và áp dụng cùng công thức cho các hàng khác.
Những sai lầm cần tránh khi làm việc với công thức trong Excel
- Hãy nhớ các quy tắc của Brackets về phép chia, phép nhân, phép cộng và phép trừ (CƠ THỂ). Điều này có nghĩa là các biểu thức trong ngoặc được đánh giá trước. Đối với các toán tử số học, phép chia được đánh giá trước, sau đó là phép nhân, rồi đến phép cộng và phép trừ là phép tính cuối cùng được đánh giá. Sử dụng quy tắc này, chúng ta có thể viết lại công thức trên thành =(A2 * D2) / 2. Điều này sẽ đảm bảo rằng A2 và D2 được đánh giá trước rồi mới chia cho hai.
- Công thức bảng tính Excel thường hoạt động với dữ liệu số; bạn có thể tận dụng tính năng xác thực dữ liệu để chỉ định loại dữ liệu sẽ được chấp nhận bởi một ô, tức là chỉ các số.
- Để đảm bảo rằng bạn đang làm việc với đúng địa chỉ ô được tham chiếu trong công thức, bạn có thể nhấn F2 trên bàn phím. Thao tác này sẽ làm nổi bật các địa chỉ ô được sử dụng trong công thức và bạn có thể kiểm tra chéo để đảm bảo chúng là địa chỉ ô mong muốn.
- Khi làm việc với nhiều hàng, bạn có thể sử dụng số sê-ri cho tất cả các hàng và có số lượng bản ghi ở cuối trang tính. Bạn nên so sánh số sê-ri với tổng số bản ghi để đảm bảo rằng công thức của bạn bao gồm tất cả các hàng.
Kiểm tra thực
10 công thức bảng tính Excel hàng đầu
Hàm trong Excel là gì?
CHỨC NĂNG TRONG EXCEL là một công thức được xác định trước được sử dụng cho các giá trị cụ thể theo một thứ tự cụ thể. Hàm được sử dụng cho các tác vụ nhanh như tìm tổng, số đếm, giá trị trung bình, giá trị lớn nhất và giá trị nhỏ nhất cho một phạm vi ô. Ví dụ: ô A3 bên dưới chứa hàm SUM tính tổng của phạm vi A1:A2.
- TÓM TẮT để tính tổng của một dãy số
- TRUNG BÌNH CỘNG để tính trung bình cộng của một dãy số cho trước
- ĐẾM để đếm số lượng mục trong một phạm vi nhất định
Tầm quan trọng của chức năng
Chức năng tăng năng suất làm việc của người dùng khi làm việc với excel. Giả sử bạn muốn có tổng số tiền cho ngân sách vật dụng gia đình ở trên. Để đơn giản hơn, bạn có thể sử dụng công thức để có tổng số tiền. Sử dụng công thức, bạn sẽ phải tham chiếu từng ô từ E4 đến E8. Bạn sẽ phải sử dụng công thức sau.
= E4 + E5 + E6 + E7 + E8
Với một hàm, bạn sẽ viết công thức trên dưới dạng
=SUM (E4:E8)
Như bạn có thể thấy từ hàm trên được sử dụng để tính tổng của một phạm vi ô, việc sử dụng hàm để lấy tổng sẽ hiệu quả hơn nhiều so với việc sử dụng công thức sẽ phải tham chiếu nhiều ô.
Các chức năng chung
Chúng ta hãy xem xét một số hàm được sử dụng phổ biến nhất trong các công thức excel ms. Chúng ta sẽ bắt đầu với các hàm thống kê.
| S / N | CHỨC NĂNG | PHÂN LOẠI | MÔ TẢ | SỬ DỤNG |
|---|---|---|---|---|
| 01 | TÓM TẮT | Toán & Trig | Thêm tất cả các giá trị trong một phạm vi ô | = SUM (E4: E8) |
| 02 | MIN | Thống kê | Tìm giá trị nhỏ nhất trong một phạm vi ô | =MIN(E4:E8) |
| 03 | MAX | Thống kê | Tìm giá trị lớn nhất trong một phạm vi ô | =MAX(E4:E8) |
| 04 | TRUNG BÌNH CỘNG | Thống kê | Tính giá trị trung bình trong một phạm vi ô | = AVERAGE (E4: E8) |
| 05 | ĐẾM | Thống kê | Đếm số lượng ô trong một phạm vi ô | = COUNT (E4: E8) |
| 06 | LEN | bản văn | Trả về số ký tự trong một chuỗi văn bản | = LEN (B7) |
| 07 | TÓM TẮT | Toán & Trig |
Thêm tất cả các giá trị trong một phạm vi ô đáp ứng tiêu chí đã chỉ định. =SUMIF(phạm vi,tiêu chí,[tổng_phạm vi]) |
=SUMIF(D4:D8,”>=1000″,C4:C8) |
| 08 | AVERAGEIF | Thống kê |
Tính giá trị trung bình trong một phạm vi ô đáp ứng tiêu chí đã chỉ định. =AVERAGEIF(phạm vi,tiêu chí,[trung bình_phạm vi]) |
=AVERAGEIF(F4:F8,”Có”,E4:E8) |
| 09 | NGÀY | Ngày giờ | Trả về số ngày giữa hai ngày | =NGÀY(D4,C4) |
| 10 | NOW | Ngày giờ | Trả về ngày giờ hiện tại của hệ thống | = NOW () |
Hàm số
Như tên gọi của nó, các hàm này hoạt động trên dữ liệu số. Bảng sau đây hiển thị một số hàm số phổ biến.
| S / N | CHỨC NĂNG | PHÂN LOẠI | MÔ TẢ | SỬ DỤNG |
|---|---|---|---|---|
| 1 | THÁNG NĂM | Thông tin | Trả về True nếu giá trị được cung cấp là số và Sai nếu nó không phải là số | = ISNUMBER (A3) |
| 2 | RAND | Toán & Trig | Tạo một số ngẫu nhiên trong khoảng từ 0 đến 1 | = RAND () |
| 3 | ROUND | Toán & Trig | Làm tròn giá trị thập phân đến số điểm thập phân được chỉ định | = ROUND (3.14455,2) |
| 4 | MEDIAN | Thống kê | Trả về số ở giữa tập hợp số đã cho | = TRUNG BÌNH(3,4,5,2,5) |
| 5 | PI | Toán & Trig | Trả về giá trị của Hàm Toán học PI(π) | = PI () |
| 6 | ĐIỆN | Toán & Trig |
Trả về kết quả của một số được lũy thừa. SỨC MẠNH (số, sức mạnh) |
= POWER (2,4) |
| 7 | MOD | Toán & Trig | Trả về số dư khi chia hai số | =MOD(10,3) |
| 8 | ROMAN | Toán & Trig | Chuyển đổi một số thành chữ số La Mã | =ROMAN(1984) |
Hàm chuỗi
Các hàm excel cơ bản này được sử dụng để xử lý dữ liệu văn bản. Bảng sau đây hiển thị một số hàm chuỗi phổ biến.
| S / N | CHỨC NĂNG | PHÂN LOẠI | MÔ TẢ | SỬ DỤNG | LÀM THẾ NÀO |
|---|---|---|---|---|---|
| 1 | LEFT | bản văn | Trả về một số ký tự được chỉ định từ đầu (phía bên trái) của chuỗi | =LEFT(“GURU99”,4) | Còn lại 4 ký tự của “GURU99” |
| 2 | QUYỀN | bản văn | Trả về một số ký tự được chỉ định từ cuối (phía bên phải) của chuỗi | =RIGHT(“GURU99”,2) | 2 ký tự bên phải của “GURU99” |
| 3 | MID | bản văn |
Truy xuất một số ký tự ở giữa chuỗi từ vị trí bắt đầu và độ dài được chỉ định. =MID (văn bản, số_bắt_đầu, số_ký tự) |
=MID(“GURU99”,2,3) | Truy xuất ký tự từ 2 đến 5 |
| 4 | ISTEXT | Thông tin | Trả về True nếu tham số được cung cấp là Văn bản | =ISTEXT(giá trị) | value – Giá trị cần kiểm tra. |
| 5 | TÌM | bản văn |
Trả về vị trí bắt đầu của một chuỗi văn bản bên trong một chuỗi văn bản khác. Chức năng này phân biệt chữ hoa chữ thường. =TÌM(tìm_văn bản, bên trong_văn bản, [số_bắt đầu]) |
=TÌM(“oo”,,”Lợp”,1) | Tìm oo trong “Tấm lợp”, Kết quả là 2 |
| 6 | Thay thế | bản văn |
Thay thế một phần của chuỗi bằng một chuỗi được chỉ định khác. =REPLACE (văn_bản_cũ,_số_bắt đầu,_số_ký tự,_văn_bản_mới) |
=REPLACE(“Tấm lợp”,2,2,”xx”) | Thay thế “oo” bằng “xx” |
Hàm ngày giờ
Các hàm này được sử dụng để thao tác các giá trị ngày. Bảng sau đây hiển thị một số hàm ngày phổ biến
| S / N | CHỨC NĂNG | PHÂN LOẠI | MÔ TẢ | SỬ DỤNG |
|---|---|---|---|---|
| 1 | NGÀY | Ngày giờ | Trả về số đại diện cho ngày trong mã Excel | = DATE (2015,2,4) |
| 2 | NGÀY | Ngày giờ | Tìm số ngày giữa hai ngày | =NGÀY(D6,C6) |
| 3 | THÁNG | Ngày giờ | Trả về tháng từ giá trị ngày | =THÁNG(“4/2/2015”) |
| 4 | PHÚT | Ngày giờ | Trả về số phút từ một giá trị thời gian | =PHÚT(“12:31”) |
| 5 | NĂM | Ngày giờ | Trả về năm từ một giá trị ngày | =YEAR(“04/02/2015”) |
Hàm VLOOKUP
Hàm VLOOKUP được sử dụng để thực hiện tra cứu theo chiều dọc ở cột ngoài cùng bên trái và trả về một giá trị trong cùng hàng từ cột mà bạn chỉ định. Hãy giải thích điều này bằng ngôn ngữ của một giáo dân. Ngân sách vật tư gia đình có một cột số sê-ri xác định duy nhất từng mục trong ngân sách. Giả sử bạn có số sê-ri của mặt hàng và muốn biết mô tả mặt hàng, bạn có thể sử dụng hàm VLOOKUP. Đây là cách hàm VLOOKUP hoạt động.
=VLOOKUP (C12, A4:B8, 2, FALSE)
ĐÂY,
"=VLOOKUP"gọi hàm tra cứu dọc"C12"chỉ định giá trị cần tra cứu ở cột ngoài cùng bên trái"A4:B8"chỉ định mảng bảng với dữ liệu"2"chỉ định số cột với giá trị hàng được hàm VLOOKUP trả về"FALSE,"cho hàm VLOOKUP biết rằng chúng ta đang tìm kiếm một kết quả khớp chính xác với giá trị tra cứu được cung cấp
Hình ảnh hoạt hình dưới đây cho thấy điều này đang hoạt động
Tổng kết
Excel cho phép bạn thao tác dữ liệu bằng công thức và/hoặc hàm. Các hàm thường có năng suất cao hơn so với việc viết công thức. Hàm cũng chính xác hơn so với công thức vì tỷ lệ mắc lỗi là rất nhỏ.
Dưới đây là danh sách các Công thức và Hàm Excel quan trọng
- Hàm SUM =
=SUM(E4:E8)
- Hàm MIN =
=MIN(E4:E8)
- Hàm MAX =
=MAX(E4:E8)
- hàm TRUNG BÌNH =
=AVERAGE(E4:E8)
- Hàm ĐẾM =
=COUNT(E4:E8)
- Hàm NGÀY =
=DAYS(D4,C4)
- Hàm VLOOKUP =
=VLOOKUP (C12, A4:B8, 2, FALSE)
- Hàm NGÀY =
=DATE(2020,2,4)
