Xác định và tạo công thức

Trong bài học này, chúng tôi giới thiệu cho bạn các quy tắc cơ bản để tạo công thức và sử dụng các hàm. Chúng tôi cảm thấy một trong những cách tốt nhất để học là thông qua thực hành, vì vậy chúng tôi cung cấp một số ví dụ và giải thích chúng một cách chi tiết. Các chủ đề chúng tôi sẽ đề cập bao gồm:

CHUYỂN ĐỔI TRƯỜNG

Tại sao bạn cần công thức và hàm?
Xác định và tạo công thức
Tham chiếu ô tương đối và tuyệt đối và định dạng
Các chức năng hữu ích bạn nên biết
Tra cứu, Biểu đồ, Thống kê và Bảng Pivot

hàng và cột
hàm toán học ví dụ: SUM ()
khai thác
ưu tiên điều hành
ví dụ chức năng tài chính: PMT (), thanh toán khoản vay
bằng cách sử dụng một hàm chuỗi của Wikipedia (chuỗi String, là viết tắt cho chuỗi văn bản của phạm vi) trong một công thức và các hàm lồng nhau

Các công thức là một hỗn hợp của các hàm, các toán tử, dữ liệu và các hàm toán tử, và trước đó, chúng ta cần tạo một hàm nhưng trước khi có thể tạo một hàm, trước tiên chúng ta cần hiểu ký hiệu hàng và cột.

Hàng và cột

Để hiểu cách viết công thức và hàm, bạn cần biết về hàng và cột.

Hàng chạy theo chiều ngang và cột chạy dọc. Để nhớ đó là cái gì, hãy nghĩ về một cột giữ một mái nhà - các cột đi lên xuống và do đó các hàng đi sang trái sang phải.

Cột được dán nhãn bằng chữ cái; hàng theo số. Ô đầu tiên trong bảng tính là A1 có nghĩa là cột A, hàng 1. Các cột được gắn nhãn A-Z. Khi bảng chữ cái chạy ra, Excel sẽ đặt một chữ cái khác ở phía trước: AA, AB, AC mộc AZ, BA, BC, BC, v.v..

Ví dụ: Hàm Sum ()

Bây giờ hãy trình bày cách sử dụng hàm.

Bạn sử dụng các chức năng bằng cách nhập chúng trực tiếp vào hoặc sử dụng trình hướng dẫn chức năng. Trình hướng dẫn chức năng sẽ mở khi bạn chọn một chức năng từ menu Công thức của chế độ ăn kiêng từ Thư viện chức năng..

Trình hướng dẫn cho bạn biết những đối số bạn cần cung cấp cho mỗi chức năng. Nó cũng cung cấp một liên kết đến các hướng dẫn trực tuyến nếu bạn cần trợ giúp để hiểu chức năng này làm gì và làm thế nào để sử dụng nó. Ví dụ: nếu bạn nhập = sum vào một ô, trình hướng dẫn nội dòng sẽ hiển thị cho bạn những đối số nào được yêu cầu cho hàm SUM.

Khi bạn nhập một chức năng, trình hướng dẫn là nội tuyến hoặc ngay trong ngón tay của bạn. Khi bạn chọn một chức năng từ menu Công thức của chế độ ăn kiêng, Trình hướng dẫn là một hộp bật lên. Dưới đây là trình hướng dẫn bật lên cho hàm SUM ().

Đối với hàm đầu tiên của chúng tôi, hãy sử dụng SUM (), để thêm danh sách các số.

Giả sử chúng tôi có bảng tính này để chứa các kế hoạch ngân sách cho kỳ nghỉ của gia đình bạn:

Để tính tổng chi phí bạn có thể viết = b2 + b3 + b4 + b5 nhưng sử dụng hàm SUM () dễ dàng hơn.

Trong Excel, hãy tìm biểu tượng ở góc trên cùng bên trái của màn hình Excel để tìm nút AutoSum (các nhà toán học sử dụng chữ cái Hy Lạp để thêm một chuỗi số).

Nếu con trỏ ở dưới số ngân sách gia đình, Excel đủ thông minh để biết rằng bạn muốn tổng hợp danh sách các số ở trên nơi bạn đặt con trỏ, để nó làm nổi bật các số.

Nhấn vào Enter enter để chấp nhận phạm vi được chọn bởi Excel hoặc sử dụng con trỏ để thay đổi các ô được chọn.

Nếu bạn nhìn vào những gì Excel đưa vào bảng tính, bạn có thể thấy rằng nó đã viết hàm này:

Trong công thức này, Excel tính tổng các số từ B2 đến B9. Lưu ý, chúng tôi đã để lại một số phòng bên dưới hàng 5 để bạn có thể thêm vào ngân sách kỳ nghỉ gia đình - chi phí chắc chắn sẽ tăng lên khi danh sách trẻ em về những gì chúng muốn làm và nơi chúng muốn đi dài hơn!

Các hàm toán học không hoạt động với các chữ cái, vì vậy nếu bạn đặt các chữ cái vào cột, kết quả sẽ được hiển thị dưới dạng Số #NAME?.

#TÊN? chỉ ra rằng có một số loại lỗi. Nó có thể là bất kỳ số lượng bao gồm:

tham chiếu tế bào xấu
sử dụng các chữ cái trong các hàm toán học
bỏ qua các đối số cần thiết
đánh vần sai tên hàm
hoạt động toán học bất hợp pháp như chia cho 0

Cách dễ nhất để chọn các đối số trong phép tính là sử dụng chuột. Bạn có thể thêm hoặc xóa khỏi danh sách các đối số cho hàm bằng cách phóng to hoặc thu nhỏ hộp mà Excel vẽ khi bạn di chuyển chuột hoặc nhấp vào một ô khác.

Chúng tôi đã nhấp vào trên cùng của hình vuông được vẽ bởi Excel để lấy vé máy bay của hãng ra khỏi ngân sách. Bạn có thể thấy biểu tượng tóc chéo mà bạn có thể vẽ để làm cho phạm vi được chọn lớn hơn hoặc nhỏ hơn.

Nhấn vào Enter để xác nhận kết quả.

Toán tử tính toán

Có hai loại toán tử: toán và so sánh.

Toán tử toán học	Định nghĩa
+	thêm vào
-	phép trừ hoặc phủ định, ví dụ: 6 * -1 = -6
*	phép nhân
/	bộ phận
%	phần trăm
^	số mũ, ví dụ 2⁴= 2 ^ 4 = 2 * 2 * 2 * 2 = 16

Có những toán tử khác không liên quan đến toán học như Hồi giáo & Có nghĩa là nối hai chuỗi (nối từ đầu đến cuối). Ví dụ: = Excel Excel & & là trò chơi Fun bằng với Excel Excel là trò vui.

Bây giờ chúng ta nhìn vào các toán tử so sánh.

Toán tử so sánh	Định nghĩa
= =	bằng, ví dụ: 2 = 4 hoặc phạm lỗi
>	lớn hơn, ví dụ: 4> 2 hoặc ở đây
<	ít hơn, ví dụ: 2 < 4 or “a” < “b”
> =	lớn hơn hoặc bằng - một cách khác để nghĩ về điều này là> = có nghĩa là hoặc > hoặc là = =.
<=	ít hơn hoặc bằng.
	không bằng, ví dụ: 46

Như bạn có thể thấy ở trên, các toán tử so sánh làm việc với các số và văn bản.

Lưu ý, nếu bạn nhập = một cách nhanh chóng> Một số người khác vào một ô, nó sẽ nói là FALSE, vì một số tiền không lớn hơn một chút, đó là sau khi một người trong bảng chữ cái, vì vậy, một > Quảng cáo hoặc là Ăn trưa

Ưu tiên đặt hàng khai thác

Thứ tự ưu tiên là một ý tưởng từ toán học. Excel phải tuân theo các quy tắc giống như toán học. Chủ đề này phức tạp hơn, vì vậy hãy hít thở và hãy đi sâu vào.

Thứ tự ưu tiên có nghĩa là thứ tự mà máy tính tính toán câu trả lời. Như chúng tôi đã giải thích trong Bài 1, diện tích của một hình tròn là πr², tương tự như π * r * r. Nó là không phải (πr)².

Vì vậy, bạn phải hiểu thứ tự ưu tiên khi bạn viết một công thức.

Nói chung, bạn có thể nói điều này:

Đầu tiên Excel đánh giá các mục trong ngoặc đơn làm việc từ trong ra ngoài.
Sau đó, nó sử dụng các quy tắc ưu tiên thứ tự của toán học.
Khi hai mục có cùng mức ưu tiên, Excel sẽ hoạt động từ trái sang phải.

Ưu tiên của toán tử toán học được hiển thị dưới đây, theo thứ tự giảm dần.

(và)	Khi sử dụng dấu ngoặc đơn, chúng sẽ ghi đè lên các quy tắc ưu tiên thông thường. Điều này có nghĩa là Excel sẽ thực hiện tính toán này trước. Chúng tôi giải thích điều này dưới đây.
-	Phủ định, ví dụ: -1. Điều này giống như nhân một số với -1. -4 = 4 * (-1)
%	Phần trăm, có nghĩa là nhân với 100. Ví dụ: 0,003 = 0,3%.
^	Số mũ, ví dụ: 10 ^ 2 = 100
* và /	Nhân và chia. Làm thế nào hai nhà khai thác có thể có cùng một ưu tiên? Điều đó chỉ có nghĩa là nếu một công thức có thêm hai toán tử có cùng mức ưu tiên, thì phép tính được thực hiện từ trái sang phải.
+ và -	Cộng và trừ.

Có các quy tắc ưu tiên khác liên quan đến chuỗi và toán tử tham chiếu. Hiện tại, chúng tôi sẽ chỉ gắn bó với những gì chúng tôi vừa trình bày. Bây giờ, hãy xem xét một số ví dụ.

Ví dụ: Tính diện tích hình tròn

Diện tích hình tròn là= PI () * bán kính ^ 2.

Nhìn vào bảng trên chúng ta thấy rằng số mũ xuất hiện trước khi nhân. Vì vậy, máy tính trước tiên tính bán kính ^ 2 và sau đó nhân với kết quả đó bằng Pi.

Ví dụ: Tính tăng lương

Giả sử sếp của bạn quyết định bạn đang làm một công việc tuyệt vời và anh ấy hoặc cô ấy sẽ tăng lương cho bạn 10%! Làm thế nào bạn sẽ tính lương mới của bạn?

Đầu tiên, hãy nhớ rằng phép nhân xuất hiện trước khi cộng.

Là nó = lương + lương * 10% hay là = lương + (lương * 10%)?

Giả sử mức lương của bạn là 100 đô la. Với mức tăng 10%, mức lương mới của bạn sẽ là:

= 100 + 100 * 10% = 100 + 10 = 110

Bạn cũng có thể viết nó như thế này:

= 100 + (100 * 10%) = 100 + 10 = 110

Trong trường hợp thứ hai, chúng tôi đã làm cho thứ tự ưu tiên rõ ràng bằng cách sử dụng dấu ngoặc đơn. Hãy nhớ rằng dấu ngoặc đơn được đánh giá trước bất kỳ thao tác nào khác.

Nhân tiện, cách dễ dàng hơn để viết này là = tiền lương * 110%

Dấu ngoặc đơn có thể được lồng vào nhau. Vì vậy, khi chúng ta viết (3 + (4 * 2)), làm việc từ trong ra ngoài, trước tiên hãy tính 4 * 2 = 8, sau đó thêm 3 + 8 để nhận 11.

Một vài ví dụ nữa

Đây là một ví dụ khác: = 4 * 3 / 2. Câu trả lời là gì?

Chúng tôi thấy từ các quy tắc trong bảng trên rằng * và / có quyền ưu tiên như nhau. Vì vậy, Excel hoạt động từ trái sang phải, 4 * 3 = 12 trước, sau đó chia cho 2 để nhận 6.

Một lần nữa, bạn có thể làm cho điều đó rõ ràng bằng cách viết = (4 * 3) / 2

Thế còn = 4 + 3 * 2?

Máy tính nhìn thấy cả toán tử * và +. Vì vậy, theo các quy tắc ưu tiên (nhân lên trước khi cộng), nó tính 3 * 2 = 6 trước, sau đó thêm 4 để nhận 10.

Nếu bạn muốn thay đổi thứ tự ưu tiên, bạn sẽ viết = (4 + 3) * 2 = 14.

Cái này thì sao = -1 ^ 3?

Sau đó trả lời là -3 vì máy tính đã tính = (-1) ^ 3 = -1 * -1 * -1 = -1.

Hãy nhớ rằng lần tiêu cực tiêu cực là tích cực và lần tiêu cực một lần tích cực là tiêu cực. Bạn có thể thấy điều này như thế này (-1 * -1) * -1 = 1 * -1 = -1.

Vì vậy, có một vài ví dụ về thứ tự và ưu tiên toán học, chúng tôi hy vọng điều đó sẽ giúp làm rõ một số điều về cách Excel thực hiện các phép tính (và đó có lẽ là toán học đủ để tồn tại suốt đời đối với một số bạn).

Ví dụ: Chức năng thanh toán khoản vay (PMT)

Hãy xem xét một ví dụ để tính toán khoản vay.

Bắt đầu bằng cách tạo một bảng tính mới.

Định dạng các số có ký hiệu đô la và sử dụng số thập phân bằng 0 vì chúng tôi không quan tâm đến xu ngay bây giờ vì chúng không quan trọng lắm khi bạn nói về đô la (trong chương tiếp theo chúng tôi sẽ tìm hiểu cách định dạng chi tiết số). Ví dụ: để định dạng lãi suất, nhấp chuột phải vào ô và nhấp vào các ô định dạng của chế độ ăn uống. Chọn phần trăm và sử dụng 2 chữ số thập phân.

Tương tự, định dạng các ô khác cho loại tiền tệ thay vì tỷ lệ phần trăm và chọn số lượng số bù cho thời hạn cho vay.

Bây giờ chúng tôi có:

Thêm hàm SUM () vào tổng chi phí hàng tháng.

Lưu ý, thế chấp tế bào không được bao gồm trong tổng số. Excel không biết rằng bạn muốn bao gồm số đó, vì không có giá trị ở đó. Vì vậy, hãy cẩn thận để mở rộng hàm SUM () lên trên cùng bằng cách sử dụng con trỏ hoặc nhập E2 trong đó có ghi E3 để bao gồm cả thế chấp trong tổng.

Đặt con trỏ vào ô thanh toán (B4).

Trên menu Công thức, chọn trình đơn thả xuống Financial Financial, sau đó chọn chức năng PMT. Trình hướng dẫn bật lên:

Sử dụng con trỏ để chọn tỷ lệ, .viết, thời gian cho vay (thời hạn cho vay), trò chơi Pv Pv (giá trị hiện tại của người khác hoặc số tiền cho vay). Lưu ý rằng bạn phải chia lãi suất cho 12 vì tiền lãi được tính hàng tháng. Ngoài ra, bạn cần nhân số thời hạn cho vay trong năm với 12 để có được thời hạn cho vay theo tháng. Nhấn nút OK OK để lưu kết quả trong bảng tính.

Lưu ý rằng khoản thanh toán được hiển thị dưới dạng số âm: -1013.37062. Để làm cho nó tích cực và thêm nó vào chi phí hàng tháng, hãy trỏ đến ô thế chấp (E2). Nhập loại = - Sau đó sử dụng con trỏ để trỏ đến trường thanh toán. Công thức kết quả là = -B4.

Bây giờ bảng tính trông như thế này:

Chi phí hàng tháng của bạn là $ 1,863 - Ouch!

Ví dụ: Hàm văn bản

Ở đây chúng tôi trình bày cách sử dụng các hàm bên trong công thức và hàm văn bản.

Giả sử bạn có một danh sách các sinh viên như hình dưới đây. Tên và họ nằm trong một trường được phân tách bằng dấu phẩy. Chúng ta cần đặt tên cuối cùng và công ty vào các ô riêng biệt. Chung ta se lam như thê nao?

Để giải quyết vấn đề này, bạn cần sử dụng một thuật toán - tức là, quy trình từng bước để thực hiện việc này.

Ví dụ, hãy nhìn vào Washington Washington, George. Quy trình phân chia thành hai từ sẽ là:

Tính độ dài của chuỗi.
Tìm vị trí của dấu phẩy (phần này hiển thị nơi một từ kết thúc và từ còn lại bắt đầu).
Sao chép phía bên trái của chuỗi lên cho đến dấu phẩy.
Sao chép phía bên phải của chuỗi từ dấu phẩy đến cuối.

Chúng ta hãy thảo luận về cách thực hiện điều này với từng bước của George George Washington trong Excel.

Tính độ dài của chuỗi với hàm = LEN (A3) - kết quả là 18.
Bây giờ hãy tìm vị trí của dấu phẩy bằng cách nhập hàm này = TÌM (phạm lỗi, phạm lỗi, A3,) - kết quả là 11.
Bây giờ, hãy đặt phía bên trái của chuỗi lên cho đến dấu phẩy và tạo công thức lồng nhau này bằng cách sử dụng kết quả từ Bước 1: = LEFT (A3, FIND (lỗi, lỗi, A3) -1). Lưu ý, chúng ta phải trừ 1 từ độ dài vì TÌM đưa ra vị trí của dấu phẩy.

Đây là những gì tất cả trông giống như khi tất cả các chức năng được đặt cùng nhau trong một công thức. Trong ô B3, bạn có thể thấy công thức này lấy tất cả thông tin từ ô A3 và nhập vào Washington Washington..

Vậy là chúng ta đã có được Washington Washington, ngay bây giờ chúng ta cần có được George George.?

Lưu ý rằng chúng ta có thể đã lưu kết quả từ Bước 1 trong một ô, giả sử, B6, sau đó viết một công thức đơn giản hơn = LEFT (A3, B6-1). Nhưng điều đó sử dụng hết một ô cho bước không liên tục.

Ghi nhớ vị trí của dấu phẩy hoặc tính lại.
Tính độ dài của chuỗi.
Đếm các ký tự từ cuối chuỗi đến dấu phẩy.

Lấy số lượng ký tự từ Bước 3 và trừ đi một ký tự để bỏ dấu phẩy và dấu cách.

Hãy làm điều này từng bước một.

Từ trên xuống, đây là = TÌM
Độ dài của chuỗi là = LEN (A3)
Bạn sẽ cần sử dụng một số phép toán để tìm số lượng ký tự cần thực hiện: = LEN (A3) - TÌM (TUYẾN, Điên, A3) - 1
Phía bên phải của chuỗi mà chúng ta muốn là = RIGHT (A3, LEN (A3) - TÌM (Rời, Lần, A3) - 1)

Bảng tính của bạn bây giờ trông giống như ảnh chụp màn hình bên dưới. Chúng tôi đã sao chép các công thức dưới dạng văn bản vào dưới cùng của bảng tính để dễ đọc và xem hơn.

Đó là một chút khó khăn nhưng bạn chỉ cần viết các công thức này một lần.

Sắp tới tiếp theo

Điều này kết thúc bài học của chúng tôi cho ngày hôm nay. Bây giờ bạn cần có một sự hiểu biết khá vững chắc về các công thức và hàm, hàng và cột và cách tất cả những thứ này có thể được sử dụng thông qua một số ví dụ xác định.

Tiếp theo trong Bài 3, chúng tôi sẽ thảo luận về tham chiếu và định dạng ô cũng như di chuyển và sao chép các công thức để bạn không phải viết lại từng công thức nhiều lần!