Công thức Excel & Chức năng: Học với VÍ DỤ Cơ bản

Mục lục:

Anonim

Công thức và hàm là các khối xây dựng để làm việc với dữ liệu số trong Excel. Bài viết này giới thiệu cho bạn các công thức và hàm.

Trong bài viết này, chúng tôi sẽ đề cập đến các chủ đề sau.

  • Công thức trong Excel là gì?
  • Những sai lầm cần tránh khi làm việc với công thức trong Excel
  • Hàm trong Excel là gì?
  • Tầm quan trọng của các chức năng
  • Các chức năng phổ biến
  • Hàm số
  • Hàm chuỗi
  • Các chức năng ngày giờ
  • Chức năng tra cứu V

Dữ liệu hướng dẫn

Đối với hướng dẫn này, chúng tôi sẽ làm việc với các bộ dữ liệu sau.

Ngân sách cung cấp nhà

S / N MỤC QTY GIÁ BÁN TIÊU ĐỀ Có phải chăng?
1 Xoài 9 600
2 Những quả 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 dựng nhà

S / N MỤC NGÀY BẮT ĐẦU NGÀY CUỐI THỜI GIAN (NGÀY)
1 Khảo sát đất 04/02/2015 07/02/2015
2 Lay Foundation 10/02/2015 15/02/2015
3 Lợp mái 27/02/2015 03/03/2015
4 Bức vẽ 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 loạt các địa chỉ ô và toán tử. Ví dụ: = A1 + A2 + A3, tìm tổng phạm vi giá trị từ ô A1 đến ô A3. 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" tạo tham chiếu đến các địa chỉ ô A2 và D2 ​​sau đó nhân các giá trị được 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

Công thức bài tập thực hành

Chúng tôi sẽ làm việc với dữ liệu mẫu cho ngân sách gia đình để tính tổng phụ.

  • Tạo 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 thành ô 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 một 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

  1. Hãy nhớ các quy tắc về Dấu ngoặc của Phép chia, Phép nhân, Phép cộng và Phép trừ (BODMAS). Điều này có nghĩa là các biểu thức là dấu ngoặc được đánh giá đầu tiên. Đối với các toán tử số học, phép chia được đánh giá đầu tiên sau đó là phép nhân, sau đó 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 dưới dạng = (A2 * D2) / 2. Điều này sẽ đảm bảo rằng A2 và D2 ​​được đánh giá đầu tiên sau đó chia cho hai.
  2. 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 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ỉ số.
  3. Để đảm bảo rằng bạn đang làm việc với các địa chỉ ô chính xác được tham chiếu trong công thức, bạn có thể nhấn F2 trên bàn phím. Điều 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.
  4. Khi bạn đang 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ố 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 10 công thức bảng tính Excel hàng đầu

Hàm trong Excel là gì?

FUNCTION IN 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, đếm, 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.

  • SUM cho tổng của một dải số
  • AVERAGE để tính giá trị trung bình của một dãy số nhất định
  • COUNT để đếm số lượng mục trong một phạm vi nhất định

Tầm quan trọng của các chức năng

Các chức năng tăng năng suất của người dùng khi làm việc với excel . Giả sử bạn muốn nhận được tổng số tiền lớn cho ngân sách đồ dùng gia đình ở trên. Để đơn giản hơn, bạn có thể sử dụng công thức để lấy tổng số. 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 để lấy tổng của một dải ô, việc sử dụng một 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 đến rất nhiều ô.

Các chức năng phổ biến

Chúng ta hãy xem xét một số hàm thường được sử dụng nhất trong công thức ms excel. Chúng ta sẽ bắt đầu với các chức năng thống kê.

S / N CHỨC NĂNG THỂ LOẠI SỰ MIÊU TẢ SỬ DỤNG
01 TỔNG 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 dải ô = MIN (E4: E8)
03 TỐI ĐA Thống kê Tìm giá trị lớn nhất trong một dải ô = 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ố ô trong một dải ô = COUNT (E4: E8)
06 LEN Bản văn Trả về số ký tự trong văn bản chuỗi = LEN (B7)
07 SUMIF Toán & Trig Thêm tất cả các giá trị trong một phạm vi ô đáp ứng tiêu chí được chỉ định. = SUMIF (phạm vi, tiêu chí, [sum_range]) = 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 các tiêu chí đã chỉ định. = AVERAGEIF (phạm vi, tiêu chí, [phạm vi trung bình]) = 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 HIỆN NAY Ngày giờ Trả về ngày và giờ hệ thống hiện tại = NGAY BÂY GIỜ ()

Hàm số

Như tên cho thấy, các chức năng này hoạt động trên dữ liệu số. Bảng sau đây cho thấy một số hàm số phổ biến.

S / N CHỨC NĂNG THỂ LOẠI SỰ MIÊU 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à False 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 từ 0 đến 1 = RAND ()
3 TRÒN Toán & Trig Làm tròn giá trị thập phân đến số dấu thập phân được chỉ định = ROUND (3,14455,2)
4 TRUNG BÌNH Thống kê Trả về số ở giữa tập hợp các số đã cho = MEDIAN (3,4,5,2,5)
5 số Pi Toán & Trig Trả về giá trị của Hàm toán học PI (π) = PI ()
6 QUYỀN LỰC Toán & Trig Trả về kết quả của một số được nâng lên thành lũy thừa. POWER (số, công suất) = POWER (2,4)
7 MOD Toán & Trig Trả về Phần dư khi bạn chia hai số = MOD (10,3)
số 8 ROMAN Toán & Trig Chuyển 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 để thao tác trên dữ liệu văn bản. Bảng sau đây cho thấy một số hàm chuỗi phổ biến.

S / N CHỨC NĂNG THỂ LOẠI SỰ MIÊU TẢ SỬ DỤNG BÌNH LUẬN
1 TRÁI Bản văn Trả về một số ký tự được chỉ định từ đầu (phía bên trái) của một chuỗi = LEFT ("GURU99", 4) 4 nhân vật còn lại của "GURU99"
2 ĐÚNG 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 một chuỗi = RIGHT ("GURU99", 2) 2 nhân vật bên phải của "GURU99"
3 MID Bản văn Lấy một số ký tự từ giữa chuỗi từ một vị trí và độ dài bắt đầu được chỉ định. = MID (văn bản, start_num, num_chars) = MID ("GURU99", 2,3) Truy xuất các ký 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 THẤY Bản văn Trả về vị trí bắt đầu của một chuỗi văn bản trong một chuỗi văn bản khác. Hàm này phân biệt chữ hoa chữ thường. = FIND (find_text, within_text, [start_num]) = FIND ("oo", "Roofing", 1) Tìm oo trong "Roofing", 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 (old_text, start_num, num_chars, new_text) = REPLACE ("Lợp", 2,2, "xx") Thay thế "oo" bằng "xx"

Chức năng ngày giờ

Các hàm này được sử dụng để thao tác các giá trị ngày tháng. Bảng sau đây cho thấy một số hàm ngày phổ biến

S / N CHỨC NĂNG THỂ LOẠI SỰ MIÊU 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ừ một giá trị ngày = MONTH ("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 tháng = NĂM ("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 bên trái nhất và trả về một giá trị trong cùng một 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 đồ dùng gia đình có một cột số sê-ri xác định duy nhất từng mặt hàng trong ngân sách. Giả sử bạn có số sê-ri mặt hàng và bạn 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 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 trả về bởi hàm VLOOKUP
  • "FALSE," cho hàm VLOOKUP biết rằng chúng tôi đang tìm kiếm kết quả khớp chính xác của giá trị tra cứu được cung cấp

Hình ảnh động bên dưới cho thấy điều này đang hoạt động

Tải xuống mã Excel ở trên

Tóm lược

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 hoạt động hiệu quả hơn so với việc viết công thức. Cá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 tối thiểu.

Đâ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 AVERAGE = =AVERAGE(E4:E8)
  • COUNT hàm = =COUNT(E4:E8)
  • Hàm DAYS = =DAYS(D4,C4)
  • Hàm VLOOKUP = =VLOOKUP (C12, A4:B8, 2, FALSE)
  • Hàm DATE = =DATE(2020,2,4)