Dữ liệu chỉ được sử dụng khi bạn thực sự có thể làm việc trên nó và Excel là một công cụ cung cấp rất nhiều sự tiện lợi khi bạn phải lập phương trình của riêng mình hoặc sử dụng các phương trình được tích hợp sẵn. Trong bài viết này, bạn sẽ học cách thực sự có thể làm việc với các Hàm quảng cáo Công thức Excel này.
Dưới đây là một cái nhìn nhanh về các chủ đề được thảo luận ở đây:
- Công thức là gì?
- Viết công thức Excel
- Chỉnh sửa công thức
- Sao chép / Dán công thức
- Ẩn công thức trong Excel
- Toán tử ưu tiên công thức Excel
- Hàm trong Excel là gì?
- Các chức năng quan trọng nhất
Công thức là gì?
Nói chung, công thức là một cách cô đọng để biểu diễn một số thông tin dưới dạng ký hiệu. Trong Excel, công thức là biểu thức có thể được nhập vào các ô của trang tính Excel và kết quả của chúng được hiển thị.
Các công thức Excel có thể thuộc các loại sau:
Kiểu | Thí dụ | Sự miêu tả |
Toán tử toán học (+, -, *, v.v.) | Ví dụ: = A1 + B1 | Thêm các giá trị của A1 và B1 |
Giá trị hoặc văn bản | Ví dụ: 100 * 0,5 bội 100 lần 0,5 | Lấy các giá trị và trả về kết quả đầu ra đồng bộ hóa luồng trong ví dụ java |
Tham chiếu ô | VÍ DỤ: = A1 = B1 | Trả về TRUE hoặc FALSE bằng cách so sánh A1 và B1 |
Chức năng trang tính | VÍ DỤ: = SUM (A1: B1) | Trả về kết quả bằng cách thêm các giá trị có trong A1 và B1 |
Viết công thức Excel:
Để viết công thức vào một ô trang tính Excel, bạn có thể làm như sau:
- Chọn ô mà bạn muốn kết quả được hiển thị
- Nhập dấu “=” ban đầu để cho Excel biết rằng bạn sẽ nhập công thức vào ô đó
- Sau đó, bạn có thể nhập địa chỉ ô hoặc chỉ định các giá trị mà bạn định tính toán
- Ví dụ: nếu bạn muốn thêm giá trị của hai ô, bạn có thể nhập địa chỉ ô như sau:
- Bạn cũng có thể chọn các ô có tổng bạn muốn tính bằng cách chọn tất cả các ô bắt buộc trong khi nhấn phím Ctrl:
Chỉnh sửa Công thức:
Trong trường hợp bạn muốn chỉnh sửa một số công thức đã nhập trước đó, chỉ cần chọn ô có chứa công thức đích và trong thanh công thức, bạn có thể thực hiện các thay đổi mong muốn. Trong ví dụ trước, tôi đã tính Tổng của A1 và A2. Bây giờ, tôi sẽ chỉnh sửa tương tự và thay đổi công thức để tính tích các giá trị có trong hai ô này:
Sau khi hoàn tất, nhấn Enter để xem kết quả đầu ra mong muốn.
Sao chép hoặc Dán một Công thức:
Excel thực sự tiện dụng khi bạn phải sao chép / dán các công thức. Bất cứ khi nào bạn sao chép một công thức, Excel sẽ tự động xử lý các tham chiếu ô được yêu cầu tại vị trí đó. Điều này được thực hiện thông qua một hệ thống được gọi là Địa chỉ ô tương đối .
Để sao chép một công thức, hãy chọn ô chứa công thức ban đầu và sau đó kéo nó cho đến ô yêu cầu bản sao của công thức đó như sau:
Như bạn có thể thấy trong hình, công thức được viết ban đầu bằng A3 và sau đó tôi đã kéo nó xuống dọc theo B3 và C3 để tính tổng của B1, B2 và C1, C2 mà không chỉ viết ra địa chỉ ô.
Trong trường hợp tôi thay đổi giá trị của bất kỳ ô nào, kết quả đầu ra sẽ được Excel tự động cập nhật theo Địa chỉ ô tương đối , Địa chỉ ô tuyệt đối hoặc là Địa chỉ ô hỗn hợp .
Ẩn công thức trong Excel:
Trong trường hợp bạn muốn ẩn một số công thức khỏi trang tính excel, bạn có thể thực hiện như sau:
- Chọn các ô có công thức bạn định ẩn
- Mở cửa sổ Phông chữ và chọn ngăn Bảo vệ
- Kiểm tra tùy chọn Ẩn và nhấp vào OK
- Sau đó, từ tab ruy-băng, hãy chọn Xem lại
- Nhấp vào Bảo vệ trang tính (Công thức sẽ không hoạt động nếu bạn không làm điều này)
- Excel sẽ yêu cầu bạn nhập mật khẩu để hiện các công thức để sử dụng trong tương lai
Mức độ ưu tiên của toán tử của công thức Excel:
Các công thức Excel tuân theo quy tắc BODMAS (Phép chia Thứ tự Dấu ngoặc kép Phép cộng Phép trừ). Nếu bạn có một công thức chứa dấu ngoặc, biểu thức trong dấu ngoặc sẽ được giải trước bất kỳ phần nào khác của công thức hoàn chỉnh. Hãy xem hình ảnh bên dưới:
Như bạn có thể thấy trong ví dụ trên, tôi có một công thức bao gồm một dấu ngoặc. Vì vậy, theo quy tắc BODMAS, trước tiên, Excel sẽ tìm sự khác biệt giữa A2 và B1 và sau đó nó thêm kết quả với A1.
Gì là 'Hàm' trong Excel?
Nói chung, một hàm xác định một công thức được thực thi theo một số thứ tự nhất định. Excel cung cấp một số lượng lớn Chức năng tích hợp sẵn có thể được sử dụng để tính toán kết quả của các công thức khác nhau.
Công thức trong Excel được chia thành các loại sau:
| Catagory | Công thức quan trọng |
Ngày giờ | DATE, DAY, MONTH, HOUR, v.v. |
Tài chính | ACCI, accinto, Dollard, INTRAATE, v.v. |
Toán & Trig | SUM, SUMIF, PRODUCT, SIN, COS, v.v. |
Thống kê | AVERAGE, COUNT, COUNTIF, MAX, MIN, v.v. |
Tra cứu & Tham khảo | COLUMN, HLOOKUP, ROW, VLOOKUP, CHOOSE, v.v. |
Cơ sở dữ liệu | DAVERAGE, DCOUNT, DMIN, DMAX, v.v. |
Bản văn | BAHTTEXT, DOLLAR, LOWER, UPPER, v.v. |
Hợp lý | VÀ, HOẶC, KHÔNG, NẾU, TRUE, FALSE, v.v. |
Thông tin | THÔNG TIN, ERROR.TYPE, TYPE, ISERROR, v.v. |
Kỹ thuật | COMPLEX, CHUYỂN ĐỔI, DELTA, OCT2BIN, v.v. |
Khối lập phương | CUBESET, CUBENUMBER, CUBEVALUE, v.v. |
Khả năng tương thích | PERCENTILE, RANK, VAR, MODE, v.v. |
Web | ENCODEURL, FILTERXML, WEBSERVICE |
Bây giờ, hãy cùng chúng tôi kiểm tra cách sử dụng một số Công thức Excel quan trọng và được sử dụng phổ biến nhất.
Các hàm Excel quan trọng nhất:
Dưới đây là một số hàm Excel quan trọng nhất cùng với mô tả và ví dụ của chúng.
NGÀY:
Một trong những hàm Ngày quan trọng nhất và được sử dụng rộng rãi trong Excel là hàm DATE. Cú pháp của nó như sau:
DATE (năm, tháng, ngày)
Hàm này trả về một số đại diện cho ngày đã cho ở định dạng ngày-giờ trong MS Excel. Hàm DATE có thể được sử dụng như sau:
THÍ DỤ:
- = DATE (2019,11,7)
- = DATE (2019,11,7) -7 (trả về ngày hiện tại - bảy ngày)
NGÀY:
Hàm này trả về giá trị ngày của tháng (1-31). Cú pháp của nó như sau:
DAY (serial_number)
Ở đây, serial_number là ngày bạn muốn truy xuất. Nó có thể được cung cấp theo bất kỳ cách nào, chẳng hạn như kết quả của một số hàm khác, được cung cấp bởi hàm DATE hoặc tham chiếu ô.
THÍ DỤ:
- = NGÀY (A7)
- = NGÀY (HÔM NAY ())
- = DAY (DATE (2019, 11,8))
THÁNG:
Cũng giống như hàm DAY, Excel cung cấp một hàm khác, tức là hàm MONTH để lấy tháng từ một ngày cụ thể. Cú pháp như sau:
MONTH (serial_number)
THÍ DỤ:
- = THÁNG (HÔM NAY ())
- = MONTH (DATE (2019, 11,8))
Phần trăm:
Như chúng ta đã biết, Phần trăm là tỷ lệ được tính dưới dạng phân số của 100. Nó có thể được ký hiệu như sau:
Phần trăm = (Một phần / Toàn bộ) x 100
Trong Excel, bạn có thể tính toán tỷ lệ phần trăm của bất kỳ giá trị mong muốn nào. Ví dụ: nếu bạn có giá trị một phần và toàn bộ trong A1 và A2 và bạn muốn tính phần trăm, bạn có thể thực hiện như sau:
- Chọn ô mà bạn muốn hiển thị kết quả
- Gõ dấu “=”
- Sau đó, nhập công thức là A1 / A2 và nhấn Enter
- Từ nhóm số tab trang chủ, hãy chọn biểu tượng “%”
NẾU:
Câu lệnh IF là câu lệnh điều kiện trả về giá trị True khi điều kiện được chỉ định được thỏa mãn và Flase khi điều kiện không được đáp ứng. Excel cung cấp hàm “IF” cài sẵn phục vụ mục đích này. Cú pháp của nó như sau:
IF (logic_test, value_if_true, value_if_false)
Đây, kiểm tra logic là điều kiện cần được kiểm tra
THÍ DỤ:
- Nhập các giá trị được so sánh
- Chọn ô sẽ hiển thị đầu ra
- Nhập vào Thanh công thức “= IF (A1 = A2,“ Có ”,“ Không ”)” và nhấn enter
VLOOKUP:
Hàm này được sử dụng để tra cứu và tìm nạp một số dữ liệu cụ thể từ một cột từ trang tính Excel. Chữ “V” trong VLOOKUP là viết tắt của tra cứu theo chiều dọc. Đây là một trong những công thức quan trọng nhất và được sử dụng rộng rãi trong Excel và để sử dụng hàm này, bảng phải được sắp xếp theo thứ tự tăng dần. Cú pháp của hàm này như sau:
VLOOKUP (lookup_value, table_array, col_index, num_range, lookup)
Ở đâu,
lookup_value là giá trị được tìm kiếm
table_array là bảng sẽ được tìm kiếm
col_index là cột mà từ đó giá trị sẽ được truy xuất
range_lookup (tùy chọn) trả về TRUE cho khoảng. khớp và FALSE cho một kết hợp chính xác
THÍ DỤ:
Như bạn có thể thấy trong hình, giá trị mà tôi đã chỉ định là 2 và phạm vi bảng nằm giữa A1 và D4. Tôi muốn tìm nạp tên của nhân viên, do đó tôi đã đặt giá trị cột là 2 và vì tôi muốn nó là một kết quả khớp chính xác, tôi đã sử dụng False để tra cứu phạm vi.
Thuế thu nhập:
Giả sử bạn muốn tính thuế thu nhập của một người có tổng tiền lương là 300 đô la. Bạn sẽ cần phải tính thuế thu nhập như sau:
- Liệt kê Tổng số tiền lương, các khoản khấu trừ lương, Thu nhập chịu thuế và tỷ lệ phần trăm Thuế trên Thu nhập
- Chỉ định các giá trị của Tổng tiền lương, các khoản khấu trừ theo lương
- Sau đó, tính Thu nhập chịu thuế bằng cách tìm chênh lệch giữa Tổng tiền lương và các khoản khấu trừ theo lương
- Cuối cùng, hãy tính Số tiền thuế
TỔNG:
Hàm SUM trong Excel tính toán kết quả bằng cách cộng tất cả các giá trị được chỉ định trong Excel. Cú pháp của hàm này như sau:
SUM (số 1, số 2,…)
Thêm tất cả các số được chỉ định làm tham số cho nó.
THÍ DỤ:
Trong trường hợp bạn muốn tính tổng số tiền bạn đã chi để mua rau, hãy liệt kê tất cả các mức giá và sau đó sử dụng công thức SUM như sau:
Lãi kép:
Để tính lãi kép, bạn có thể sử dụng một trong các Công thức Excel được gọi là FV. Hàm này sẽ trả về giá trị tương lai của khoản đầu tư trên cơ sở lãi suất và các khoản thanh toán định kỳ, không đổi. Cú pháp của hàm này như sau:
FV (tỷ lệ, nper, pmt, pv, loại)
Để tính toán tỷ lệ, bạn sẽ cần phải chia tỷ lệ hàng năm cho số kỳ, tức là tỷ lệ hàng năm / kỳ. Số kỳ hoặc nper được tính bằng cách nhân kỳ hạn (số năm) với các khoảng thời gian tức là kỳ hạn * kỳ. pmt là viết tắt của thanh toán định kỳ và có thể là bất kỳ giá trị nào kể cả số không.
Hãy xem xét ví dụ sau:
Trong ví dụ trên, tôi đã tính Lãi gộp cho 500 đô la với tỷ lệ 10% trong 5 năm và giả định rằng giá trị thanh toán định kỳ là 0. Xin lưu ý rằng tôi đã sử dụng -B1 nghĩa là 500 đô la đã được lấy từ tôi.
Trung bình cộng:
Giá trị trung bình, như chúng ta đều biết, mô tả giá trị trung bình của một số giá trị. Trong Excel, trung bình có thể được tính dễ dàng bằng cách sử dụng hàm tích hợp có tên là “AVERAGE”. Cú pháp của hàm này như sau:
AVERAGE (số 1, số 2,…)
THÍ DỤ:
Trong trường hợp bạn muốn tính điểm trung bình của học sinh trong tất cả các kỳ thi, bạn chỉ cần tạo một bảng và sau đó sử dụng công thức AVERAGE để tính điểm trung bình của mỗi học sinh.
Trong ví dụ trên, tôi đã tính điểm trung bình của hai học sinh trong hai kỳ thi. Trong trường hợp bạn có nhiều hơn hai giá trị mà giá trị trung bình cần được xác định, bạn chỉ cần chỉ định phạm vi ô trong đó các giá trị hiện diện. Ví dụ:
ĐẾM:
Hàm count trong Excel sẽ đếm số ô chứa số trong một phạm vi nhất định. Cú pháp của hàm này như sau:
COUNT (giá trị1, giá trị2,…)
THÍ DỤ:
Trong trường hợp tôi muốn tính số ô chứa số từ bảng mà tôi đã tạo trong ví dụ trước, tôi sẽ chỉ cần chọn ô trong đó tôi muốn hiển thị kết quả và sau đó sử dụng hàm COUNT như sau:
TRÒN:
Để làm tròn các giá trị đến một số chữ số thập phân cụ thể, bạn có thể sử dụng hàm ROUND. Hàm này sẽ trả về một số bằng cách làm tròn số đó đến số chữ số thập phân được chỉ định. Cú pháp của hàm này như sau:
ROUND (số, num_digits)
THÍ DỤ:
Tìm điểm:
Để tìm điểm, bạn sẽ phải sử dụng các câu lệnh IF lồng nhau trong Excel. Ví dụ, trong ví dụ Trung bình, tôi đã tính điểm trung bình của học sinh trong các bài kiểm tra. Bây giờ, để tìm Điểm mà những sinh viên này đạt được, tôi sẽ phải tạo một hàm IF lồng nhau như sau:
Như bạn thấy, điểm trung bình có trong cột G. Để tính điểm, tôi đã sử dụng công thức IF lồng nhau. Mã như sau:
= IF (G19> 90, ”A”, (IF (G19> 75, ”B”, IF (G19> 60, ”C”, IF (G19> 40, ”D”, ”F”)))))
Sau khi làm điều này, bạn sẽ chỉ cần sao chép công thức vào tất cả các ô mà bạn muốn hiển thị điểm.
CẤP:
Trong trường hợp bạn muốn xác định Xếp hạng mà học sinh trong lớp đạt được, bạn có thể sử dụng một trong các Công thức Excel tích hợp sẵn, tức là RANK. Hàm này sẽ trả về Xếp hạng cho một phạm vi được chỉ định bằng cách so sánh một phạm vi nhất định theo thứ tự tăng dần hoặc giảm dần. Cú pháp của hàm này như sau:
RANK (giới thiệu, số lượng, đơn đặt hàng)
THÍ DỤ:
Như bạn thấy, trong ví dụ trên, tôi đã tính Xếp hạng của học sinh bằng cách sử dụng chức năng Xếp hạng. Ở đây, tham số đầu tiên là điểm trung bình mà mỗi học sinh đạt được và mảng là điểm trung bình của tất cả các học sinh khác trong lớp. Tôi chưa chỉ định bất kỳ thứ tự nào, do đó, sản lượng sẽ được xác định theo thứ tự giảm dần. Đối với xếp hạng thứ tự tăng dần, bạn sẽ phải chỉ định bất kỳ giá trị nào khác.
COUNTIF:
Để đếm các ô dựa trên một số điều kiện nhất định, bạn có thể sử dụng một trong các Công thức Excel tích hợp có tên là “COUNTIF”. Hàm này sẽ trả về số lượng ô thỏa mãn một số điều kiện trong một phạm vi nhất định. Cú pháp của hàm này như sau:
COUNTIF (phạm vi, tiêu chí)
THÍ DỤ:
Như bạn thấy, trong ví dụ trên, tôi đã tìm thấy số lượng ô có giá trị lớn hơn 80. Bạn cũng có thể cung cấp một số giá trị văn bản cho tham số tiêu chí.
MỤC LỤC:
Hàm INDEX trả về giá trị hoặc tham chiếu ô tại một số vị trí cụ thể trong một phạm vi được chỉ định. Cú pháp của hàm này như sau:
INDEX (mảng, row_num, column_num) hoặc là
INDEX (tham khảo,row_num, column_num, area_num)
Hàm chỉ mục hoạt động như sau cho Mảng hình thức:
- Nếu cả số hàng và số cột đều được cung cấp, nó sẽ trả về giá trị có ở ô giao nhau
- Nếu giá trị hàng được đặt thành 0, nó sẽ trả về các giá trị có trong toàn bộ cột trong phạm vi được chỉ định
- Nếu giá trị cột được đặt thành 0, nó sẽ trả về các giá trị có trong toàn bộ hàng trong phạm vi được chỉ định
Hàm chỉ mục hoạt động như sau cho Tài liệu tham khảo hình thức:
- Trả về tham chiếu của ô nơi các giá trị hàng và cột giao nhau
- Area_num sẽ cho biết phạm vi nào sẽ được sử dụng trong trường hợp nhiều phạm vi đã được cung cấp
THÍ DỤ:
Như bạn có thể thấy, trong ví dụ trên, tôi đã sử dụng hàm INDEX để xác định giá trị có trong hàng thứ 2 và cột thứ 4 cho phạm vi ô từ A18 đến G20.
Tương tự, bạn cũng có thể sử dụng hàm INDEX bằng cách chỉ định nhiều tham chiếu như sau:
Phần này sẽ đưa chúng ta đến phần cuối của bài viết này về Công thức và Hàm trong Excel. Tôi hy vọng bạn rõ ràng với tất cả những gì đã được chia sẻ với bạn. Đảm bảo rằng bạn luyện tập nhiều nhất có thể và hoàn nguyên trải nghiệm của mình.
Có một câu hỏi cho chúng tôi? Vui lòng đề cập đến nó trong phần nhận xét của blog “Công thức và Hàm Excel” này và chúng tôi sẽ liên hệ lại với bạn trong thời gian sớm nhất.
Để có kiến thức chuyên sâu về bất kỳ công nghệ thịnh hành nào cùng với các ứng dụng khác nhau của nó, bạn có thể đăng ký trực tiếp với hỗ trợ 24/7 và truy cập trọn đời.