Nội dung
Nhiều người mới sử dụng Excel thường có một câu hỏi: công thức Excel là gì và làm thế nào để nhập công thức đó vào một ô. Nhiều người thậm chí còn nghĩ tại sao nó lại cần thiết. Đối với họ, Excel là một bảng tính. Nhưng trên thực tế, đây là một máy tính đa chức năng lớn và ở một mức độ nào đó, là một môi trường lập trình.
Khái niệm về công thức và chức năng
Và tất cả công việc trong Excel đều dựa trên các công thức, trong đó có một số lượng lớn. Trung tâm của bất kỳ công thức nào là một hàm. Nó là một công cụ tính toán cơ bản trả về một giá trị tùy thuộc vào dữ liệu được truyền sau khi nó đã được xử lý trước.
Công thức là một tập hợp các toán tử logic, các phép toán số học và các hàm. Nó không phải lúc nào cũng chứa tất cả các yếu tố này. Ví dụ, phép tính có thể bao gồm chỉ các phép toán.
Trong lời nói hàng ngày, người dùng Excel thường nhầm lẫn các khái niệm này. Trên thực tế, ranh giới giữa chúng khá tùy tiện và cả hai thuật ngữ đều thường được sử dụng. Tuy nhiên, để hiểu rõ hơn về cách làm việc với Excel, cần phải biết các giá trị chính xác.
Trên thực tế, bộ máy thuật ngữ rộng hơn nhiều và bao gồm nhiều khái niệm khác cần được xem xét chi tiết hơn.
- Không thay đổi. Đây là giá trị được giữ nguyên và không thể thay đổi. Ví dụ, đây có thể là số Pi.
- Các nhà khai thác. Đây là một mô-đun cần thiết để thực hiện các hoạt động nhất định. Excel cung cấp ba loại toán tử:
- Môn số học. Cần thiết để cộng, trừ, chia và nhân nhiều số.
- Toán tử so sánh. Cần thiết để kiểm tra xem dữ liệu có đáp ứng một điều kiện nhất định hay không. Nó có thể trả về một giá trị: true hoặc false.
- Toán tử văn bản. Nó chỉ là một và cần thiết để nối dữ liệu - &.
- Liên kết. Đây là địa chỉ của ô mà từ đó dữ liệu sẽ được lấy, bên trong công thức. Có hai loại liên kết: tuyệt đối và tương đối. Đầu tiên không thay đổi nếu công thức được chuyển đến một nơi khác. Các ô tương đối, tương ứng, thay đổi ô thành ô liền kề hoặc tương ứng. Ví dụ: nếu bạn chỉ định liên kết đến ô B2 trong một số ô, rồi sao chép công thức này sang ô liền kề ở bên phải, địa chỉ sẽ tự động thay đổi thành C2. Liên kết có thể là nội bộ hoặc bên ngoài. Trong trường hợp đầu tiên, Excel truy cập vào một ô nằm trong cùng một sổ làm việc. Trong thứ hai - trong cái khác. Nghĩa là, Excel có thể sử dụng dữ liệu nằm trong tài liệu khác trong các công thức.
Cách nhập dữ liệu vào ô
Một trong những cách dễ nhất để chèn công thức có chứa hàm là sử dụng Trình hướng dẫn hàm. Để gọi nó, bạn cần nhấp vào biểu tượng fx một chút ở bên trái của thanh công thức (nó nằm phía trên bảng và nội dung của ô được sao chép trong đó nếu không có công thức trong đó hoặc công thức bị hiển thị nếu đúng. Một hộp thoại như vậy sẽ xuất hiện.
Ở đó, bạn có thể chọn danh mục chức năng và chọn trực tiếp danh mục từ danh sách mà bạn muốn sử dụng trong một ô cụ thể. Ở đó, bạn không chỉ có thể xem danh sách mà còn xem từng chức năng hoạt động như thế nào.
Cách thứ hai để nhập công thức là sử dụng tab tương ứng trên ruy-băng Excel.
Ở đây giao diện khác nhau, nhưng cơ chế thì giống nhau. Tất cả các chức năng được chia thành các danh mục, và người dùng có thể chọn một chức năng phù hợp nhất với mình. Để xem chức năng của từng chức năng, bạn cần di chuột qua chức năng đó và đợi 2 giây.
Bạn cũng có thể nhập một hàm trực tiếp vào một ô. Để thực hiện việc này, bạn cần bắt đầu viết ký hiệu nhập công thức (= =) vào đó và nhập tên của hàm theo cách thủ công. Phương pháp này phù hợp với những người dùng có kinh nghiệm hơn, những người biết nó thuộc lòng. Cho phép bạn tiết kiệm rất nhiều thời gian.
Sau khi nhập các chữ cái đầu tiên, một danh sách sẽ được hiển thị, trong đó bạn cũng có thể chọn chức năng mong muốn và chèn nó. Nếu không thể sử dụng chuột, bạn có thể điều hướng qua danh sách này bằng phím TAB. Nếu có, thì bạn chỉ cần nhấp đúp chuột vào công thức tương ứng là đủ. Khi chức năng được chọn, một lời nhắc sẽ xuất hiện cho phép bạn nhập dữ liệu theo đúng trình tự. Dữ liệu này được gọi là các đối số của hàm.
Nếu bạn vẫn đang sử dụng phiên bản Excel 2003, thì nó không cung cấp danh sách thả xuống, vì vậy bạn cần nhớ chính xác tên của hàm và nhập dữ liệu từ bộ nhớ. Điều tương tự cũng xảy ra đối với tất cả các đối số của hàm. May mắn thay, đối với một người dùng có kinh nghiệm, đây không phải là một vấn đề.
Điều quan trọng là luôn bắt đầu công thức bằng dấu bằng, nếu không Excel sẽ nghĩ rằng ô chứa văn bản.
Trong trường hợp này, dữ liệu bắt đầu bằng dấu cộng hoặc dấu trừ cũng sẽ được coi là công thức. Nếu sau đó có văn bản trong ô, thì Excel sẽ thông báo lỗi #NAME ?. Nếu các số liệu hoặc số được đưa ra, thì Excel sẽ cố gắng thực hiện các phép toán thích hợp (cộng, trừ, nhân, chia). Trong mọi trường hợp, bạn nên bắt đầu nhập công thức bằng dấu =, vì nó là thông lệ.
Tương tự, bạn có thể bắt đầu viết một hàm với dấu @, hàm này sẽ được tự động thay đổi. Phương thức nhập này được coi là lỗi thời và cần thiết để các phiên bản cũ hơn của tài liệu không bị mất một số chức năng.
Khái niệm về đối số hàm
Hầu hết tất cả các hàm đều chứa đối số, có thể là tham chiếu ô, văn bản, số và thậm chí là một hàm khác. Vì vậy, nếu bạn sử dụng hàm KIỂM TRA, bạn sẽ cần chỉ định các số sẽ được kiểm tra. Giá trị boolean sẽ được trả về. Nếu đó là một số lẻ, TRUE sẽ được trả về. Theo đó, nếu là số chẵn thì là “FALSE”. Các đối số, như bạn có thể thấy từ ảnh chụp màn hình ở trên, được nhập trong dấu ngoặc và được phân tách bằng dấu chấm phẩy. Trong trường hợp này, nếu phiên bản tiếng Anh của chương trình được sử dụng, thì dấu phẩy thông thường đóng vai trò là dấu phân cách.
Đối số đầu vào được gọi là tham số. Một số chức năng hoàn toàn không chứa chúng. Ví dụ: để lấy ngày và giờ hiện tại trong một ô, bạn cần viết công thức =TATA (). Như bạn có thể thấy, nếu hàm không yêu cầu đầu vào của các đối số, thì dấu ngoặc vẫn cần được chỉ định.
Một số tính năng của công thức và hàm
Nếu dữ liệu trong ô được tham chiếu bởi công thức được chỉnh sửa, nó sẽ tự động tính toán lại dữ liệu cho phù hợp. Giả sử chúng ta có ô A1, được ghi vào một công thức đơn giản có chứa tham chiếu ô thông thường = D1. Nếu bạn thay đổi thông tin trong đó, thì giá trị tương tự sẽ được hiển thị trong ô A1. Tương tự, đối với các công thức phức tạp hơn lấy dữ liệu từ các ô cụ thể.
Điều quan trọng là phải hiểu rằng các phương pháp Excel tiêu chuẩn không thể làm cho một ô trả về giá trị của nó cho một ô khác. Đồng thời, tác vụ này có thể đạt được bằng cách sử dụng macro - chương trình con thực hiện các hành động nhất định trong tài liệu Excel. Nhưng đây là một chủ đề hoàn toàn khác, rõ ràng không dành cho người mới bắt đầu, vì nó đòi hỏi kỹ năng lập trình.
Khái niệm về công thức mảng
Đây là một trong những biến thể của công thức, được nhập theo một cách hơi khác. Nhưng nhiều người không biết nó là gì. Vì vậy, trước tiên chúng ta hãy tìm hiểu ý nghĩa của thuật ngữ này. Sẽ dễ hiểu hơn nhiều với một ví dụ.
Giả sử chúng ta có một công thức TỔNG, trả về tổng các giá trị trong một phạm vi nhất định.
Hãy tạo một phạm vi đơn giản như vậy bằng cách viết các số từ một đến năm trong các ô A1: A5. Sau đó, chúng tôi chỉ định chức năng = SUM (A1: A5) trong ô B1. Kết quả là số 15 sẽ xuất hiện ở đó.
Đây đã là một công thức mảng chưa? Không, mặc dù nó hoạt động với một tập dữ liệu và có thể được gọi là một. Hãy thực hiện một số thay đổi. Giả sử chúng ta cần thêm một đối số vào mỗi đối số. Để làm điều này, bạn cần tạo một hàm như sau:
= SUM (A1: A5 + 1). Nó chỉ ra rằng chúng tôi muốn thêm một vào phạm vi giá trị trước khi tính tổng của chúng. Nhưng ngay cả trong biểu mẫu này, Excel sẽ không muốn làm điều này. Anh ta cần thể hiện điều này bằng cách sử dụng công thức Ctrl + Shift + Enter. Công thức mảng khác nhau về giao diện và trông giống như sau:
{= SUM (A1: A5 + 1)}
Sau đó, trong trường hợp của chúng tôi, kết quả 20 sẽ được nhập.
Không có ích gì khi nhập dấu ngoặc nhọn theo cách thủ công. Nó sẽ không làm gì cả. Ngược lại, Excel thậm chí sẽ không nghĩ rằng đây là một hàm và chỉ là văn bản thay vì công thức.
Trong khi đó, bên trong chức năng này, các hành động sau đã được thực hiện. Đầu tiên, chương trình phân tách phạm vi này thành các thành phần. Trong trường hợp của chúng tôi, nó là 1,2,3,4,5. Tiếp theo, Excel sẽ tự động tăng từng thứ một. Sau đó, các số kết quả được cộng lên.
Có một trường hợp khác trong đó công thức mảng có thể làm điều gì đó mà công thức chuẩn không thể. Ví dụ: chúng tôi có một tập dữ liệu được liệt kê trong phạm vi A1: A10. Trong trường hợp tiêu chuẩn, số không sẽ được trả về. Nhưng giả sử chúng ta có một tình huống như vậy không thể tính đến con số XNUMX.
Hãy nhập công thức kiểm tra phạm vi để xem nó có không bằng giá trị này hay không.
=МИН(ЕСЛИ(A1:A10<>0;A1:A10))
Ở đây có một cảm giác sai lầm rằng sẽ đạt được kết quả mong muốn. Nhưng đây không phải là trường hợp, bởi vì ở đây bạn cần sử dụng một công thức mảng. Trong công thức trên, chỉ phần tử đầu tiên sẽ được kiểm tra, tất nhiên, phần tử này không phù hợp với chúng ta.
Nhưng nếu bạn biến nó thành một công thức mảng, việc căn chỉnh có thể nhanh chóng thay đổi. Bây giờ giá trị nhỏ nhất sẽ là 1.
Công thức mảng cũng có ưu điểm là nó có thể trả về nhiều giá trị. Ví dụ, bạn có thể chuyển đổi một bảng.
Vì vậy, có rất nhiều loại công thức khác nhau. Một số yêu cầu đầu vào đơn giản hơn, những người khác phức tạp hơn. Công thức mảng có thể đặc biệt khó hiểu đối với người mới bắt đầu, nhưng chúng rất hữu ích.