Thực đơn
trang chủChương trình

Công thức và hàm trong Excel. Tạo công thức đơn giản trong Microsoft Excel

Các công thức trong Excel là bản chất chính của nó, đó là lý do tại sao chương trình này được Microsoft tạo ra. Công thức cho phép bạn tính toán các giá trị của ô dựa trên dữ liệu từ các ô khác và nếu dữ liệu nguồn thay đổi, kết quả tính toán trong ô nơi viết công thức sẽ được tính toán lại tự động!

Tạo công thức trong Excel

Hãy xem cách các công thức hoạt động bằng ví dụ đơn giản nhất - tổng của hai số. Nhập số 2 vào một ô Excel và nhập số 3 vào ô khác, tổng của các số này phải xuất hiện ở ô thứ ba.

Tổng của 2 và 3 tất nhiên là 5, nhưng bạn không cần nhập thủ công số 5 vào ô tiếp theo, nếu không ý nghĩa của các phép tính trong Excel sẽ bị mất. Bạn phải nhập công thức tính tổng vào ô có tổng và sau đó kết quả sẽ được chương trình tự động tính toán.

Trong ví dụ, phép tính có vẻ đơn giản, nhưng khi các số lớn hoặc phân số, bạn không thể thực hiện nếu không có công thức.

Các công thức trong Excel có thể chứa các phép tính số học (cộng +, trừ -, nhân *, chia /), tọa độ của các ô dữ liệu nguồn (cả riêng lẻ và phạm vi) và các hàm tính toán.

Hãy xem xét công thức tính tổng các số trong ví dụ trên:

TỔNG(A2;B2)

Mọi công thức đều bắt đầu bằng dấu bằng. Nếu bạn muốn thêm công thức vào một ô bằng cách viết thủ công thì ký tự này phải được viết trước.

Tiếp theo trong ví dụ là hàm SUM, có nghĩa là cần phải tổng hợp một số dữ liệu và đã có trong ngoặc đơn của hàm, cách nhau bằng dấu chấm phẩy, một số đối số được chỉ định, trong trường hợp này là tọa độ của các ô (A2 và B2), các giá trị phải được thêm vào và kết quả được đặt trong ô nơi viết công thức. Nếu cần thêm ba ô, bạn có thể viết ba đối số vào hàm SUM, phân tách chúng bằng dấu chấm phẩy, ví dụ:

TỔNG(A4;B4;C4)

Khi bạn cần thêm một số lượng lớn các ô, việc chỉ định từng ô trong công thức sẽ mất rất nhiều thời gian, vì vậy thay vì chỉ liệt kê đơn giản, bạn có thể sử dụng việc chỉ định một phạm vi ô:

TỔNG(B2:B7)

Một phạm vi ô trong Excel được chỉ định bằng cách sử dụng tọa độ của ô đầu tiên và ô cuối cùng, cách nhau bằng dấu hai chấm. Ví dụ này thêm giá trị của các ô bắt đầu từ ô B2 đến ô B7.

Các hàm trong công thức có thể được kết nối và kết hợp khi cần thiết để đạt được kết quả mong muốn. Ví dụ: nhiệm vụ là cộng ba số và tùy thuộc vào kết quả nhỏ hơn 100 hay nhiều hơn, nhân tổng với hệ số 1,2 hoặc 1,3. Công thức sau đây sẽ giúp giải quyết vấn đề:

IF(TỔNG(A2:C2)

Hãy phân tích giải pháp cho vấn đề chi tiết hơn. Hai hàm IF và SUM đã được sử dụng. Hàm IF luôn có ba đối số: đối số đầu tiên là điều kiện, đối số thứ hai là hành động nếu điều kiện đúng, đối số thứ ba là hành động nếu điều kiện sai. Chúng tôi xin nhắc bạn rằng các đối số được phân tách bằng dấu chấm phẩy.

IF(điều kiện; đúng; sai)

Điều kiện chỉ ra rằng tổng của phạm vi ô A2:C2 nhỏ hơn 100. Nếu trong quá trình tính toán, điều kiện được đáp ứng và tổng các ô trong phạm vi bằng, ví dụ: 98, thì Excel sẽ thực hiện hành động được chỉ định trong đối số thứ hai của hàm IF, tức là. TỔNG(A2:C2)*1.2. Nếu tổng vượt quá số 100 thì hành động trong đối số thứ ba của hàm IF sẽ được thực thi, tức là. TỔNG(A2:C2)*1.3.

Các hàm tích hợp trong Excel

Có một số lượng lớn các hàm trong Excel và đơn giản là không thể biết hết mọi thứ. Một số cái thường dùng thì có thể nhớ được, nhưng một số thì thỉnh thoảng bạn mới cần đến và rất khó nhớ tên và đặc biệt là hình thức ghi.

Nhưng Excel có một cách tiêu chuẩn để chèn các hàm với danh sách đầy đủ của chúng. Nếu bạn muốn thêm hàm vào ô, hãy nhấp vào ô đó và chọn chèn hàm trong menu chính. Chương trình sẽ hiển thị danh sách các chức năng và bạn có thể chọn một chức năng cần thiết để giải quyết vấn đề.

Để chèn một hàm trong Excel 2007, hãy chọn mục “Công thức” trong menu chính và nhấp vào biểu tượng “Chèn hàm” hoặc nhấn tổ hợp phím Shift + F3 trên bàn phím của bạn.

Trong Excel 2003, một hàm được chèn thông qua menu "Chèn" -> "Chức năng". Tổ hợp phím Shift+F3 hoạt động theo cách tương tự.

Một dấu bằng sẽ xuất hiện trong ô nơi con trỏ được đặt và cửa sổ "Trình hướng dẫn chức năng" sẽ xuất hiện trên đầu trang tính.

Các hàm trong Excel được chia thành các loại. Nếu bạn biết chức năng dự định của mình thuộc danh mục nào thì hãy chọn lựa theo danh mục đó. Nếu không, hãy chọn "Danh sách đầy đủ theo thứ tự bảng chữ cái". Chương trình sẽ hiển thị tất cả các chức năng có sẵn trong danh sách chức năng.

Cuộn qua danh sách và sử dụng chuột để đánh dấu tên chức năng mà bạn quan tâm. Ngay bên dưới danh sách sẽ xuất hiện biểu mẫu ghi của nó, các đối số cần thiết và mô tả ngắn gọn sẽ giải thích cho bạn mục đích của hàm. Khi bạn tìm thấy những gì bạn cần, hãy nhấp vào nút “OK” để tiến hành chỉ định các đối số.

Trong cửa sổ đối số có các trường có tên “Số 1”, “Số 2”, v.v. Chúng phải được điền tọa độ của các ô (hoặc phạm vi) mà bạn muốn lấy dữ liệu. Bạn có thể điền thủ công, nhưng sẽ thuận tiện hơn nhiều khi nhấp vào biểu tượng bảng ở cuối trường để chỉ ra ô hoặc dải ô nguồn.

Cửa sổ đối số sẽ có dạng đơn giản. Bây giờ bạn cần nhấp vào ô nguồn đầu tiên có dữ liệu, sau đó bấm lại vào biểu tượng bảng trong cửa sổ đối số.

Trường “Số 1” sẽ được điền tọa độ của ô đã chọn. Quy trình tương tự phải được thực hiện cho trường “Số 2” và cho các trường sau nếu bạn có nhiều hơn hai đối số hàm.

Sau khi điền tất cả các đối số, bạn có thể xem trước kết quả tính công thức kết quả. Để làm cho nó xuất hiện trong một ô trên bảng tính, hãy nhấp vào nút “OK”. Trong ví dụ được xem xét, ô D2 chứa tích của các số trong ô B2 và C2.

Phương pháp chèn hàm được xem xét là phổ biến và cho phép bạn thêm bất kỳ hàm nào từ danh sách chung các hàm Excel tiêu chuẩn.


Giống

Một hàm khá quan trọng trong Excel là công thức, thực hiện các phép tính số học và logic trên dữ liệu trong bảng. Với sự trợ giúp của họ, bạn có thể dễ dàng giải quyết các vấn đề toán học và kỹ thuật cũng như thực hiện các phép tính khác nhau, vì vậy người dùng nâng cao của chương trình nên biết cách thiết lập công thức trong Excel.

Tất cả các công thức đều bắt đầu bằng dấu bằng (=), điều này cho phép chương trình biết rằng đó là công thức đang được nhập vào ô. Các công thức đơn giản bao gồm các biểu thức tính tổng, hiệu, nhân, v.v. Các toán tử số học được sử dụng để viết các biểu thức như vậy:

— “*” - sản phẩm (=4*3)
— “/” - phép chia (=4/3)
— “+” - phép cộng (=4-3)
— “-” - phép trừ (=4+3)
— “^” - lũy thừa (=4^3)
- “%” - tìm tỷ lệ phần trăm (để tìm 4% của 156, sử dụng biểu thức sau “=156*4%”, nghĩa là khi thêm “%” vào một số, nó được chia cho 100 và “4%” được chuyển đổi thành “0,04”).

Ví dụ. Công thức tính số tiền trong Excel:
Đối với ô A1, biểu thức “=12+3” đã được nhập. Chương trình tự động thực hiện phép tính và chỉ ra trong ô không phải công thức đã nhập mà là giải pháp cho ví dụ. Việc thực hiện các phép tính số học khác được thực hiện tương tự.
Một số toán tử có thể được sử dụng đồng thời trong công thức: “=56 + 56*4%” - biểu thức này cộng thêm 4 phần trăm vào số 56.

Để làm việc chính xác với các toán tử, bạn nên biết các quy tắc ưu tiên:
1) các biểu thức trong ngoặc được tính;
2) sau khi tích và chia, phép cộng và phép trừ được xem xét;
3) các biểu thức được thực thi từ trái sang phải nếu chúng có cùng mức độ ưu tiên.

Các công thức “=5*4+8” và “=5*(4+8)” có ý nghĩa khác nhau, bởi vì trong trường hợp đầu tiên, phép nhân “5*4” được thực hiện ban đầu và trong trường hợp còn lại là biểu thức trong ngoặc “ 4+8” được tính "

Công thức Excel tính giá trị trung bình

Một công thức hữu ích cho phép bạn tìm giá trị trung bình của một số số đã nhập và được ký hiệu là “=AVERAGE()”. Giá trị trung bình của các số: 5, 10, 8 và 1 là kết quả của việc chia tổng của chúng cho số lượng, tức là 24 cho 4. Hàm này được triển khai trong Excel theo cách sau:

Sử dụng liên kết

Hoạt động trong chương trình không bị giới hạn ở các giá trị không đổi (hằng số), vì vậy các công thức không chỉ có thể chứa số mà còn cả số ô - liên kết. Hàm này sẽ cho phép bạn tính toán công thức ngay cả khi dữ liệu trong ô được chỉ định thay đổi và được biểu thị bằng chữ cái và số của ô đó.

Ô A1, A2 chứa các số tương đối 12, 15. Khi bạn thêm công thức “=A1+A2” vào ô A3, giá trị tổng của các số trong ô A1 và A2 sẽ xuất hiện trong đó.

Nếu bạn thay đổi số của bất kỳ ô A1 hoặc A2 nào, công thức sẽ tính số tiền dựa trên dữ liệu mới và ô A3 sẽ thay đổi giá trị của nó.

Phạm vi ô

Trong Excel, bạn có thể thao tác trên một phạm vi ô cụ thể, điều này giúp việc điền công thức trở nên dễ dàng hơn. Ví dụ: để tính tổng các ô từ A1 đến A6, không nhất thiết phải nhập dãy “=A1+A2+A3…” mà chỉ cần nhập toán tử cộng “SUM()” và giữ chuột trái và kéo từ A1 đến A6.

Kết quả của công việc sẽ là công thức “=SUM(A1:A6)”.

Văn bản trong công thức

Để sử dụng văn bản trong công thức, nó phải được đặt trong dấu ngoặc kép - “văn bản”. Để kết hợp 2 giá trị văn bản, toán tử ký hiệu “&” được sử dụng để kết nối chúng vào một ô bằng cách gán loại “giá trị văn bản”.

Để chèn dấu cách giữa các từ, bạn cần viết như sau: “=A1&” “&A2”.

Toán tử này có thể kết hợp các giá trị văn bản và số, vì vậy, chẳng hạn, bạn có thể nhanh chóng điền vào bảng như thế này bằng cách kết nối các số 10, 15, 17, 45 và 90 với văn bản “pcs”.

Trong phần thứ hai của loạt bài Excel 2010 dành cho người mới bắt đầu, bạn sẽ tìm hiểu cách liên kết các ô trong bảng với các công thức toán học, thêm hàng và cột vào bảng tạo sẵn, tìm hiểu về chức năng Tự động điền và hơn thế nữa.

Giới thiệu

Trong phần đầu tiên của loạt bài “Excel 2010 dành cho người mới bắt đầu”, chúng ta đã làm quen với những điều cơ bản nhất về Excel, học cách tạo các bảng thông thường trong đó. Nói đúng ra, đây là một vấn đề đơn giản và tất nhiên, khả năng của chương trình này rộng hơn nhiều.

Ưu điểm chính của bảng tính là các ô dữ liệu riêng lẻ có thể được liên kết với nhau bằng các công thức toán học. Nghĩa là, nếu giá trị của một trong các ô được kết nối với nhau thay đổi, dữ liệu của các ô khác sẽ tự động được tính toán lại.

Trong phần này, chúng ta sẽ tìm hiểu những lợi ích mà những cơ hội đó có thể mang lại bằng cách sử dụng ví dụ về bảng chi tiêu ngân sách mà chúng ta đã tạo, mà chúng ta sẽ phải học cách tạo các công thức đơn giản. Chúng ta cũng sẽ làm quen với chức năng tự động điền ô và tìm hiểu cách bạn có thể chèn các hàng và cột bổ sung vào bảng cũng như hợp nhất các ô trong đó.

Thực hiện các phép tính số học cơ bản

Ngoài việc tạo các bảng thông thường, Excel có thể được sử dụng để thực hiện các phép tính số học trong đó, chẳng hạn như cộng, trừ, nhân và chia.

Để thực hiện các phép tính trong bất kỳ ô bảng nào, bạn cần tạo bên trong ô đó điều đơn giản nhất công thức, luôn phải bắt đầu bằng dấu bằng (=). Để chỉ định các phép toán trong một công thức, các toán tử số học thông thường được sử dụng:

Ví dụ: hãy tưởng tượng rằng chúng ta cần cộng hai số - “12” và “7”. Đặt con trỏ chuột vào bất kỳ ô nào và gõ biểu thức sau: “=12+7”. Khi nhập xong nhấn phím “Enter” ô sẽ hiển thị kết quả tính toán - “19”.

Để biết một ô thực sự chứa gì - một công thức hoặc một số - bạn cần chọn ô đó và nhìn vào thanh công thức - khu vực nằm ngay phía trên tên cột. Trong trường hợp của chúng tôi, nó chỉ hiển thị công thức mà chúng tôi vừa nhập.

Sau khi thực hiện xong các thao tác các bạn chú ý kết quả chia số 12 cho 7, đây không phải là số nguyên (1.714286) và chứa khá nhiều chữ số sau dấu thập phân. Trong hầu hết các trường hợp, độ chính xác như vậy là không cần thiết và những con số dài như vậy sẽ chỉ làm bảng lộn xộn.

Để khắc phục điều này, hãy chọn ô có số mà bạn muốn thay đổi số vị trí thập phân sau dấu thập phân và trên tab trang chủ trong nhóm Con số chọn đội Giảm độ sâu bit. Mỗi lần bấm vào nút này sẽ loại bỏ một ký tự.

Ở bên trái của đội Giảm độ sâu bit Có một nút thực hiện thao tác ngược lại - nó tăng số vị trí thập phân để hiển thị giá trị chính xác hơn.

Vẽ các công thức

Bây giờ chúng ta hãy quay lại bảng ngân sách mà chúng ta đã tạo trong phần đầu của loạt bài này.

Hiện tại, nó ghi lại chi phí cá nhân hàng tháng cho các hạng mục cụ thể. Ví dụ: bạn có thể biết số tiền đã chi cho thực phẩm trong tháng 2 hoặc bảo dưỡng ô tô trong tháng 3. Nhưng tổng chi phí hàng tháng không được nêu ở đây, mặc dù đối với nhiều người, những chỉ số này là quan trọng nhất. Hãy khắc phục tình trạng này bằng cách thêm dòng “Chi phí hàng tháng” vào cuối bảng và tính giá trị của nó.

Để tính tổng chi phí cho tháng 1 trong ô B7, bạn có thể viết biểu thức sau: “=18250+5100+6250+2500+3300” và nhấn Enter, sau đó bạn sẽ thấy kết quả tính toán. Đây là một ví dụ về việc sử dụng một công thức đơn giản, việc biên dịch công thức này không khác gì các phép tính trên máy tính. Trừ khi dấu bằng được đặt ở đầu biểu thức chứ không phải ở cuối biểu thức.

Bây giờ hãy tưởng tượng rằng bạn đã mắc lỗi khi chỉ ra giá trị của một hoặc nhiều khoản mục chi phí. Trong trường hợp này, bạn sẽ phải điều chỉnh không chỉ dữ liệu trong các ô biểu thị chi phí mà còn cả công thức tính tổng chi phí. Tất nhiên, điều này rất bất tiện và do đó trong Excel, khi tạo công thức, người ta thường không sử dụng các giá trị số cụ thể mà địa chỉ và phạm vi ô.

Với suy nghĩ này, hãy thay đổi công thức tính tổng chi phí hàng tháng.

Tại ô B7, nhập dấu bằng (=) và... Thay vì nhập thủ công giá trị ô B2, hãy nhấp chuột trái vào ô đó. Sau đó, một khung đánh dấu có chấm sẽ xuất hiện xung quanh ô, biểu thị rằng giá trị của nó đã được bao gồm trong công thức. Bây giờ hãy nhập dấu “+” và nhấp vào ô B3. Tiếp theo, thực hiện tương tự với các ô B4, B5 và B6, sau đó nhấn phím ENTER, sau đó giá trị số lượng tương tự sẽ xuất hiện như trong trường hợp đầu tiên.

Chọn lại ô B7 và nhìn vào thanh công thức. Có thể thấy, thay vì số - giá trị ô, công thức lại chứa địa chỉ của chúng. Đây là một điểm rất quan trọng, vì chúng ta vừa xây dựng một công thức không phải từ những con số cụ thể mà từ những giá trị ô có thể thay đổi theo thời gian. Ví dụ: nếu bây giờ bạn thay đổi số tiền chi phí mua sắm trong tháng 1 thì toàn bộ tổng chi phí hàng tháng sẽ được tính toán lại tự động. Hãy thử một lần.

Bây giờ, giả sử rằng bạn cần tính tổng không phải năm giá trị, như trong ví dụ của chúng tôi, mà là một trăm hoặc hai trăm. Như bạn hiểu, việc sử dụng phương pháp xây dựng công thức trên trong trường hợp này là rất bất tiện. Trong trường hợp này, tốt hơn là sử dụng nút “AutoSum” đặc biệt, nút này cho phép bạn tính tổng của một số ô trong một cột hoặc hàng. Trong Excel, bạn không chỉ có thể tính tổng các cột mà còn cả các hàng, vì vậy chúng tôi sử dụng nó để tính toán, chẳng hạn như tổng chi phí thực phẩm trong sáu tháng.

Đặt con trỏ vào một ô trống ở phía dòng mong muốn (trong trường hợp của chúng tôi là H2). Sau đó bấm vào nút Tổng trên dấu trang trang chủ trong nhóm Chỉnh sửa. Bây giờ chúng ta hãy quay lại bàn và xem điều gì đã xảy ra.

Trong ô chúng ta đã chọn, một công thức xuất hiện với một dải ô có giá trị cần được tính tổng. Cùng lúc đó, khung chấm sáng lại xuất hiện. Chỉ lần này nó đóng khung không chỉ một ô mà là toàn bộ phạm vi ô, tổng của các ô đó cần được tính.

Bây giờ chúng ta hãy nhìn vào công thức. Như trước đây, dấu bằng đứng đầu, nhưng lần này nó được theo sau bởi chức năng“SUM” là một công thức được xác định trước sẽ cộng các giá trị của các ô được chỉ định. Ngay sau hàm có các dấu ngoặc nằm xung quanh địa chỉ các ô có giá trị cần tính tổng, gọi là đối số công thức. Xin lưu ý rằng công thức không cho biết tất cả địa chỉ của các ô được tính tổng mà chỉ cho biết địa chỉ đầu tiên và cuối cùng. Dấu hai chấm giữa chúng chỉ ra rằng phạm vi tế bào từ B2 đến G2.

Sau khi nhấn Enter, kết quả sẽ xuất hiện ở ô đã chọn, nhưng đó là tất cả những gì nút có thể làm Tổngđừng kết thúc. Nhấp vào mũi tên bên cạnh và một danh sách sẽ mở ra chứa các hàm tính giá trị trung bình (Trung bình), số lượng dữ liệu được nhập (Số), giá trị tối đa (Tối đa) và tối thiểu (Tối thiểu).

Vì vậy, trong bảng của chúng tôi, chúng tôi đã tính tổng chi phí cho tháng 1 và tổng chi phí cho thực phẩm trong sáu tháng. Đồng thời, họ thực hiện việc này theo hai cách khác nhau - đầu tiên là sử dụng địa chỉ ô trong công thức, sau đó sử dụng các hàm và dải ô. Bây giờ là lúc hoàn tất việc tính toán cho các ô còn lại, tính tổng chi phí cho các tháng còn lại và các khoản mục chi phí.

Tự động điền

Để tính số tiền còn lại, chúng ta sẽ sử dụng một tính năng đáng chú ý của Excel, đó là khả năng tự động hóa quá trình điền dữ liệu hệ thống vào các ô.

Đôi khi trong Excel, bạn phải nhập dữ liệu tương tự cùng loại theo một trình tự nhất định, ví dụ: ngày trong tuần, ngày hoặc số hàng. Hãy nhớ rằng, trong phần đầu tiên của loạt bài này, trong tiêu đề bảng, chúng ta đã nhập tên tháng vào từng cột riêng biệt? Trên thực tế, việc nhập toàn bộ danh sách này theo cách thủ công là hoàn toàn không cần thiết vì ứng dụng có thể làm điều đó cho bạn trong nhiều trường hợp.

Hãy xóa tất cả tên tháng trong tiêu đề của bảng, ngoại trừ tên đầu tiên. Bây giờ hãy chọn ô có nhãn “Tháng Một” và di chuyển con trỏ chuột đến góc dưới bên phải của nó để nó có dạng chữ thập được gọi là đánh dấu điền. Giữ nút chuột trái và kéo nó sang phải.

Một chú giải công cụ sẽ xuất hiện trên màn hình, cho bạn biết giá trị mà chương trình sắp chèn vào ô tiếp theo. Trong trường hợp của chúng tôi, đây là “Tháng Hai”. Khi bạn di chuyển điểm đánh dấu xuống, nó sẽ đổi thành tên của các tháng khác, điều này sẽ giúp bạn tìm ra điểm dừng. Khi nút được thả ra, danh sách sẽ tự động được điền.

Tất nhiên, Excel không phải lúc nào cũng “hiểu” chính xác cách điền vào các ô tiếp theo, vì các chuỗi có thể khá đa dạng. Hãy tưởng tượng rằng chúng ta cần điền vào một dòng các giá trị số chẵn: 2, 4, 6, 8, v.v. Nếu chúng ta nhập số “2” và cố gắng di chuyển điểm đánh dấu tự động điền sang bên phải, thì hóa ra chương trình sẽ đề nghị chèn lại giá trị “2” cả vào ô tiếp theo và các ô khác.

Trong trường hợp này, ứng dụng cần cung cấp thêm một chút dữ liệu. Để thực hiện việc này, trong ô tiếp theo bên phải, nhập số “4”. Bây giờ, hãy chọn cả hai ô đã điền và lại di chuyển con trỏ đến góc dưới bên phải của vùng chọn để nó có dạng điểm đánh dấu lựa chọn. Di chuyển điểm đánh dấu xuống, chúng ta thấy rằng chương trình hiện đã hiểu trình tự của chúng ta và đang hiển thị các giá trị bắt buộc trong chú giải công cụ.

Do đó, đối với các chuỗi phức tạp, trước khi sử dụng tính năng tự động điền, bạn cần tự điền vào một số ô để Excel xác định chính xác thuật toán chung tính giá trị của chúng.

Bây giờ, hãy áp dụng tính năng chương trình hữu ích này vào bảng của chúng ta để có thể nhập công thức theo cách thủ công cho các ô còn lại. Đầu tiên, chọn ô có số tiền đã được tính toán (B7).

Bây giờ “móc” con trỏ vào góc dưới bên phải của hình vuông và kéo điểm đánh dấu sang phải ô G7. Sau khi bạn nhả phím, ứng dụng sẽ tự sao chép công thức vào các ô được đánh dấu, đồng thời tự động thay đổi địa chỉ của các ô có trong biểu thức, thay thế các giá trị chính xác.

Hơn nữa, nếu điểm đánh dấu được di chuyển sang phải, như trong trường hợp của chúng tôi hoặc xuống dưới, thì các ô sẽ được điền theo thứ tự tăng dần và sang trái hoặc lên trên - theo thứ tự giảm dần.

Ngoài ra còn có một cách để điền vào một hàng bằng băng dính. Hãy sử dụng nó để tính số tiền chi phí cho tất cả các khoản mục chi phí (cột H).

Chúng tôi chọn phạm vi cần điền, bắt đầu từ ô có dữ liệu đã được nhập. Sau đó trên tab trang chủ trong nhóm Chỉnh sửa Nhấn nút Đổ đầy và chọn hướng điền.

Thêm hàng, cột và hợp nhất ô

Để thực hành nhiều hơn về cách viết công thức, hãy mở rộng bảng của chúng tôi và đồng thời tìm hiểu một số thao tác định dạng cơ bản. Ví dụ: hãy cộng các khoản thu nhập vào bên chi tiêu, sau đó tính toán mức tiết kiệm ngân sách có thể có.

Giả sử phần doanh thu của bảng sẽ nằm trên phần chi tiêu. Để làm điều này, chúng ta sẽ phải chèn thêm một vài dòng. Như thường lệ, việc này có thể được thực hiện theo hai cách: sử dụng các lệnh trên ribbon hoặc trong menu ngữ cảnh, cách này nhanh hơn và dễ dàng hơn.

Nhấp chuột phải vào bất kỳ ô nào của hàng thứ hai và chọn lệnh từ menu mở ra Chèn…, và sau đó trong cửa sổ - Thêm dòng.

Sau khi chèn một hàng, hãy chú ý đến thực tế là theo mặc định, nó được chèn phía trên hàng đã chọn và có định dạng (màu nền ô, cài đặt kích thước, màu văn bản, v.v.) của hàng nằm phía trên nó.

Nếu cần thay đổi định dạng mặc định thì ngay sau khi dán nhấn vào nút Thêm tùy chọn biểu tượng tự động xuất hiện gần góc dưới bên phải của ô đã chọn và chọn tùy chọn bạn muốn.

Sử dụng phương pháp tương tự, bạn có thể chèn các cột vào bảng sẽ được đặt ở bên trái của ô đã chọn và các ô riêng lẻ.

Nhân tiện, nếu một hàng hoặc cột ở sai vị trí sau khi chèn, bạn có thể dễ dàng xóa nó. Nhấp chuột phải vào bất kỳ ô nào thuộc đối tượng cần xóa và chọn lệnh từ menu mở ra Xóa bỏ. Cuối cùng, chỉ ra chính xác những gì bạn muốn xóa: một hàng, một cột hoặc một ô riêng lẻ.

Trên ribbon, bạn có thể sử dụng nút để thêm các thao tác Chèn nằm trong nhóm Tế bào trên dấu trang trang chủ và để xóa lệnh có cùng tên trong cùng một nhóm.

Trong trường hợp của chúng tôi, chúng tôi cần chèn năm hàng mới ở đầu bảng ngay sau tiêu đề. Để thực hiện việc này, bạn có thể lặp lại thao tác thêm nhiều lần hoặc sau khi hoàn thành thao tác này một lần, hãy sử dụng phím “F4”, thao tác này lặp lại thao tác gần đây nhất.

Kết quả sau khi chèn 5 hàng ngang vào phần trên cùng của bảng, chúng ta đưa bảng về dạng sau:

Chúng tôi để lại các hàng màu trắng không được định dạng trong bảng nhằm mục đích phân tách các phần thu nhập, chi tiêu và tổng số với nhau bằng cách viết các tiêu đề thích hợp vào đó. Nhưng trước khi làm điều đó, chúng ta sẽ tìm hiểu thêm một thao tác nữa trong Excel - hợp nhất các ô.

Khi một số ô liền kề được kết hợp, một ô sẽ được hình thành, ô này có thể chiếm nhiều cột hoặc hàng cùng một lúc. Trong trường hợp này, tên của ô đã hợp nhất sẽ trở thành địa chỉ của ô trên cùng của dải ô đã hợp nhất. Bạn có thể chia lại ô đã hợp nhất bất kỳ lúc nào, nhưng bạn không thể tách ô chưa bao giờ được hợp nhất.

Khi hợp nhất các ô, chỉ dữ liệu ở trên cùng bên trái được lưu, còn dữ liệu ở tất cả các ô đã hợp nhất khác sẽ bị xóa. Hãy nhớ điều này và thực hiện việc hợp nhất trước, sau đó mới nhập thông tin.

Hãy quay trở lại bàn của chúng tôi. Để viết tiêu đề bằng dòng trắng, chúng ta chỉ cần một ô, trong khi bây giờ chúng bao gồm tám ô. Hãy khắc phục điều này. Chọn tất cả tám ô của hàng thứ hai của bảng và trên tab trang chủ trong nhóm Căn chỉnh nhấn nút Kết hợp và đặt ở trung tâm.

Sau khi thực hiện lệnh, tất cả các ô được chọn trong hàng sẽ được gộp thành một ô lớn.

Bên cạnh nút hợp nhất có một mũi tên, nhấp vào mũi tên này sẽ hiển thị menu với các lệnh bổ sung cho phép bạn: hợp nhất các ô mà không cần căn giữa, hợp nhất toàn bộ nhóm ô theo chiều ngang và chiều dọc, đồng thời hủy hợp nhất.

Sau khi thêm tiêu đề, cũng như điền vào các dòng: tiền lương, tiền thưởng và thu nhập hàng tháng, bảng của chúng tôi bắt đầu trông như thế này:

Phần kết luận

Để kết luận, chúng ta hãy tính dòng cuối cùng trong bảng của chúng ta, sử dụng kiến ​​thức thu được trong bài viết này, các giá trị ô trong đó sẽ được tính bằng công thức sau. Trong tháng đầu tiên, số dư sẽ là chênh lệch thông thường giữa thu nhập nhận được trong tháng và tổng chi phí trong tháng. Nhưng trong tháng thứ hai, chúng tôi sẽ cộng số dư của tháng đầu tiên vào khoản chênh lệch này vì chúng tôi đang tính toán tiết kiệm. Việc tính toán cho các tháng tiếp theo sẽ được thực hiện theo cùng một sơ đồ - số tiền tiết kiệm cho kỳ trước sẽ được cộng vào số dư hàng tháng hiện tại.

Bây giờ hãy dịch những phép tính này thành các công thức mà Excel có thể hiểu được. Đối với tháng 1 (ô B14), công thức rất đơn giản và sẽ có dạng như sau: “=B5-B12”. Nhưng đối với ô C14 (Tháng 2), biểu thức có thể được viết theo hai cách khác nhau: “=(B5-B12)+(C5-C12)” hoặc “=B14+C5-C12”. Trong trường hợp đầu tiên, chúng tôi lại tính toán số dư của tháng trước và sau đó cộng số dư của tháng hiện tại vào đó, và trong trường hợp thứ hai, kết quả đã tính cho tháng trước sẽ được đưa vào công thức. Tất nhiên, sử dụng tùy chọn thứ hai để xây dựng công thức trong trường hợp của chúng tôi là tốt hơn nhiều. Rốt cuộc, nếu bạn làm theo logic của tùy chọn đầu tiên, thì trong biểu thức tính tháng 3 sẽ có 6 địa chỉ ô, vào tháng 4 - 8, vào tháng 5 - 10, v.v., và khi sử dụng tùy chọn thứ hai ở đó sẽ luôn là ba trong số họ.

Để điền vào các ô còn lại từ D14 đến G14, chúng tôi sẽ sử dụng khả năng điền chúng tự động, giống như chúng tôi đã làm trong trường hợp số lượng.

Nhân tiện, để kiểm tra giá trị của khoản tiết kiệm cuối cùng cho tháng 6, nằm trong ô G14, trong ô H14, bạn có thể hiển thị chênh lệch giữa tổng thu nhập hàng tháng (H5) và chi phí hàng tháng (H12). Như bạn hiểu, chúng phải bằng nhau.

Như có thể thấy từ các tính toán mới nhất, trong các công thức, bạn không chỉ có thể sử dụng địa chỉ của các ô liền kề mà còn có thể sử dụng bất kỳ địa chỉ nào khác, bất kể vị trí của chúng trong tài liệu hoặc thuộc một bảng cụ thể. Hơn nữa, bạn có quyền liên kết các ô nằm trên các trang khác nhau của tài liệu và thậm chí trong các cuốn sách khác nhau, nhưng chúng ta sẽ nói về vấn đề này trong ấn phẩm tiếp theo.

Và đây là bảng cuối cùng của chúng tôi với các tính toán được thực hiện:

Bây giờ, nếu muốn, bạn có thể tiếp tục tự điền vào, chèn cả mục chi phí hoặc thu nhập bổ sung (hàng) và thêm tháng mới (cột).

Trong bài viết tiếp theo, chúng ta sẽ nói chi tiết hơn về các hàm, hiểu khái niệm liên kết tương đối và tuyệt đối, đảm bảo nắm vững một số yếu tố hữu ích hơn trong việc chỉnh sửa bảng, v.v.

Công thức là một biểu thức tính giá trị của một ô. Hàm là các công thức được xác định trước và đã được tích hợp sẵn trong Excel.

Ví dụ, trong hình bên dưới ô A3 chứa công thức cộng giá trị ô A2 Và A1.

Một ví dụ nữa. Tế bào A3 chứa một chức năng TỔNG(SUM), tính tổng của phạm vi A1:A2.

TỔNG(A1:A2)
=SUM(A1:A2)

Nhập công thức

Để nhập công thức, hãy làm theo hướng dẫn dưới đây:

Khuyên bảo: Thay vì gõ thủ công A1 Và A2, chỉ cần nhấp chuột vào các ô A1 Và A2.

Chỉnh sửa công thức

Khi bạn chọn một ô, Excel sẽ hiển thị giá trị hoặc công thức trong ô đó trên Thanh Công thức.

    1. Để chỉnh sửa công thức, nhấp vào thanh công thức và thay đổi công thức.

Ưu tiên hoạt động

Excel sử dụng thứ tự có sẵn để thực hiện các phép tính. Nếu một phần của công thức nằm trong ngoặc đơn thì nó sẽ được tính trước. Sau đó nhân hoặc chia được thực hiện. Excel sau đó sẽ cộng và trừ. Xem ví dụ dưới đây:

Excel đầu tiên nhân ( A1*A2), sau đó cộng giá trị ô A3đến kết quả này.

Một vi dụ khac:

Excel đầu tiên tính giá trị trong ngoặc đơn ( A2+A3), sau đó nhân kết quả với kích thước ô A1.

Sao chép/dán công thức

Khi bạn sao chép một công thức, Excel sẽ tự động điều chỉnh các tham chiếu cho từng ô mới mà công thức được sao chép vào. Để hiểu điều này, hãy làm theo các bước sau:

Chèn một chức năng

Tất cả các chức năng đều có cấu trúc giống nhau. Ví dụ:

TỔNG(A1:A4)
TỔNG(A1:A4)

Tên của chức năng này là TỔNG(TỔNG). Biểu thức giữa các dấu ngoặc (đối số) có nghĩa là chúng ta đã chỉ định một phạm vi A1:A4 như đầu vào. Hàm này thêm giá trị vào ô A1, A2, A3 Và A4. Việc ghi nhớ những hàm và đối số nào sẽ được sử dụng cho từng tác vụ cụ thể là điều không hề dễ dàng. May mắn thay, Excel có lệnh Chèn chức năng(Chèn chức năng).

Để chèn một hàm, hãy làm như sau:

Ghi chú: Thay vì sử dụng " Chèn chức năng", chỉ cần gõ =COUNTIF(A1:C2,">5"). Khi bạn nhập “=COUNTIF(“, thay vì nhập “A1:C2”, hãy chọn phạm vi đó bằng chuột theo cách thủ công.

Công thức cho Excel biết phải làm gì với các số hoặc giá trị trong một ô hoặc nhóm ô. Nếu không có công thức thì về nguyên tắc là không cần đến bảng tính.

Cấu trúc công thức bao gồm: hằng số, toán tử, liên kết, hàm, tên phạm vi, dấu ngoặc đơn chứa đối số và các công thức khác. Bằng một ví dụ, chúng tôi sẽ phân tích ứng dụng thực tế của công thức cho người dùng mới làm quen.

Công thức trong Excel dành cho người mới bắt đầu

Để đặt công thức cho một ô, bạn cần kích hoạt nó (đặt con trỏ) và nhập bằng (=). Bạn cũng có thể nhập dấu bằng vào thanh công thức. Sau khi nhập công thức nhấn Enter. Kết quả tính toán sẽ xuất hiện trong ô.

Excel sử dụng các toán tử toán học tiêu chuẩn:

Ký hiệu “*” là bắt buộc khi nhân. Việc bỏ qua nó là không thể chấp nhận được, như thông lệ trong quá trình tính toán số học bằng văn bản. Tức là Excel sẽ không hiểu mục (2+3)5.

Excel có thể được sử dụng như một máy tính. Tức là nhập số và các toán tử tính toán vào công thức và nhận ngay kết quả.

Nhưng địa chỉ ô thường được nhập hơn. Nghĩa là, người dùng nhập một liên kết đến ô có giá trị mà công thức sẽ thực hiện.

Khi giá trị trong ô thay đổi, công thức sẽ tự động tính lại kết quả.

Toán tử nhân giá trị của ô B2 với 0,5. Để nhập tham chiếu ô vào công thức, chỉ cần nhấp vào ô đó.

Trong ví dụ của chúng tôi:

  1. Đặt con trỏ vào ô B3 và nhập =.
  2. Chúng tôi đã nhấp vào ô B2 - Excel đã “gắn nhãn” cho nó (tên ô xuất hiện trong công thức, một hình chữ nhật “nhấp nháy” được hình thành xung quanh ô).
  3. Nhập dấu *, giá trị 0,5 từ bàn phím và nhấn ENTER.

Nếu nhiều toán tử được sử dụng trong một công thức, chương trình sẽ xử lý chúng theo trình tự sau:

  • %, ^;
  • *, /;
  • +, -.

Bạn có thể thay đổi thứ tự bằng dấu ngoặc đơn: Đầu tiên, Excel sẽ tính giá trị của biểu thức trong dấu ngoặc đơn.



Cách chỉ định một ô không đổi trong công thức Excel

Có hai loại tham chiếu ô: tương đối và tuyệt đối. Khi sao chép một công thức, các liên kết này hoạt động khác nhau: các liên kết tương đối thay đổi, các liên kết tuyệt đối không đổi.

Tìm điểm đánh dấu tự động điền ở góc dưới bên phải của ô đầu tiên của cột. Bấm vào điểm này bằng nút chuột trái, giữ nó và “kéo” nó xuống cột.

Nhả nút chuột - công thức sẽ được sao chép vào các ô đã chọn có liên kết tương đối. Tức là mỗi ô sẽ có công thức riêng với các đối số riêng.