Thực đơn
trang chủiOS

Cách nhập đúng công thức trong Excel. Hợp nhất các ô. Mảng hai chiều

Bạn phải nhập công thức bắt đầu bằng dấu bằng. Điều này là cần thiết để Excel hiểu rằng đó là một công thức chứ không phải dữ liệu đang được nhập vào ô.

Chọn một ô tùy ý, ví dụ A1. Trong thanh công thức, nhập =2+3 rồi nhấn Enter. Kết quả (5) xuất hiện trong ô. Và bản thân công thức sẽ vẫn còn trong thanh công thức.



Thử nghiệm với các toán tử số học khác nhau: cộng (+), trừ (-), nhân (*), chia (/). Để sử dụng chúng một cách chính xác, bạn cần hiểu rõ mức độ ưu tiên của chúng.

  1. Các biểu thức bên trong dấu ngoặc đơn được thực thi đầu tiên.
  2. Phép nhân và phép chia có nhiều hơn ưu tiên cao hơn phép cộng và phép trừ.
  3. Các toán tử có cùng mức ưu tiên được thực hiện từ trái sang phải.

Lời khuyên của tôi dành cho bạn là SỬ DỤNG DÂN ĐỒNG. Trong trường hợp này, bạn sẽ tự bảo vệ mình khỏi lỗi ngẫu nhiên một mặt trong các phép tính và mặt khác, dấu ngoặc đơn giản hóa rất nhiều việc đọc và phân tích các công thức. Nếu số dấu ngoặc đơn đóng và mở trong công thức không khớp nhau, Excel sẽ hiển thị thông báo lỗi và đưa ra tùy chọn để sửa lỗi đó. Ngay sau khi bạn nhập dấu ngoặc đơn đóng, Excel sẽ hiển thị in đậm(hoặc màu khác) cặp đôi cuối cùng dấu ngoặc, rất thuận tiện khi có trong công thức số lượng lớn dấu ngoặc.

Bây giờ chúng ta hãy thử sử dụng tham chiếu đến các ô khác trong công thức.

Nhập số 10 vào ô A1 và số 15 vào ô A2. Trong ô A3, nhập công thức =A1+A2. Trong ô A3, tổng của ô A1 và A2 sẽ xuất hiện - 25. Thay đổi giá trị của ô A1 và A2 (nhưng không phải A3!). Sau khi thay đổi giá trị ở ô A1 và A2, giá trị ô A3 sẽ tự động được tính lại (theo công thức).



Để tránh nhầm lẫn khi nhập địa chỉ ô, bạn có thể sử dụng chuột khi nhập liên kết. Trong trường hợp của chúng tôi, chúng tôi cần phải làm như sau:

  1. Chọn ô A3 và nhập dấu bằng vào thanh công thức.
  2. Bấm vào ô A1 và nhập dấu cộng.
  3. Bấm vào ô A2 và nhấn Enter.

Kết quả sẽ tương tự.

Tham chiếu tương đối, tuyệt đối và hỗn hợp



Để hiểu rõ hơn về sự khác biệt giữa các liên kết, chúng ta hãy thử nghiệm.

  • A1 - 20 B1 - 200
  • A2 - 30 B2 - 300

Trong ô A3, nhập công thức =A1+A2 và nhấn Enter.



Bây giờ đặt con trỏ ở góc dưới bên phải của ô A3, nhấn nút chuột phải và kéo qua ô B3 rồi thả nút chuột. Một menu ngữ cảnh sẽ xuất hiện trong đó bạn cần chọn “Sao chép ô”.



Sau đó, giá trị công thức từ ô A3 sẽ được sao chép sang ô B3. Kích hoạt ô B3 và xem bạn nhận được công thức gì - B1+B2. Tại sao điều này xảy ra? Khi chúng tôi viết công thức A1+A2 trong ô A3, Excel diễn giải mục này như sau: “Lấy các giá trị từ ô nằm cao hơn hai hàng trong cột hiện tại và cộng giá trị của ô nằm cao hơn một hàng trong cột hiện tại. cột hiện tại.” Những thứ kia. bằng cách sao chép công thức từ ô A3 chẳng hạn sang ô C43, chúng ta nhận được - C41 + C42. Đây là vẻ đẹp của các liên kết tương đối; bản thân công thức dường như thích ứng với nhiệm vụ của chúng ta.



Nhập các giá trị sau vào ô:

  • A1 - 20 B1 - 200
  • A2 - 30 B2 - 300
  • Nhập số 5 vào ô C1.

Trong ô A3, nhập công thức sau =A1+A2+$C$1. Tương tự, sao chép công thức từ A3 sang B3. Hãy nhìn xem chuyện gì đã xảy ra. Liên kết tương đối“điều chỉnh” theo giá trị mới, nhưng giá trị tuyệt đối vẫn không thay đổi.



Bây giờ hãy thử tự mình thử nghiệm các liên kết hỗn hợp và xem chúng hoạt động như thế nào. Bạn có thể tham chiếu các trang tính khác trong cùng một sổ làm việc giống như cách bạn có thể tham chiếu các ô trong trang tính hiện tại. Bạn thậm chí có thể tham khảo các tờ từ các cuốn sách khác. Trong trường hợp này, liên kết sẽ được gọi là liên kết bên ngoài.


Ví dụ, để viết link tới ô A5 (Sheet2) trong ô A1 (Sheet 1), bạn cần thực hiện như sau:

  • chọn ô A1 và nhập dấu bằng;
  • nhấp vào nhãn "Trang 2";
  • Bấm vào ô A5 và nhấn enter;
  • Sau đó, Sheet 1 sẽ được kích hoạt lại và công thức sau sẽ xuất hiện trong ô A1 = Sheet2!A5.

Chỉnh sửa công thức cũng tương tự như chỉnh sửa giá trị văn bản trong ô. Những thứ kia. bạn cần kích hoạt ô có công thức bằng cách tô sáng hoặc nhấn đúp chuột chuột, sau đó chỉnh sửa bằng cách sử dụng các phím Del, Backspace nếu cần. Những thay đổi được thực hiện bằng cách nhấn phím Enter.

Sử dụng văn bản trong công thức

Bạn có thể thực hiện các phép toán trên giá trị văn bản nếu giá trị văn bản chỉ chứa các ký tự sau:

Các số từ 0 đến 9, + - e E /

Bạn cũng có thể sử dụng năm ký tự định dạng số:

khoảng trống $%()

Văn bản phải được đặt trong dấu ngoặc kép.


Sai: =$55+$33

Phải: ="$55"+$"33"


Bằng cách làm tính toán excel chuyển đổi văn bản dạng số thành giá trị số nên kết quả của công thức trên sẽ là giá trị 88.

Để kết hợp các giá trị văn bản, hãy sử dụng toán tử văn bản & (ký hiệu). Ví dụ: nếu ô A1 chứa giá trị văn bản"Ivan" và ô A2 - "Petrov", sau đó nhập công thức sau vào ô A3 =A1&A2, chúng ta nhận được "IvanPetrov".


Để chèn khoảng trắng giữa họ và tên, hãy viết như sau: =A1&" "&A2.


Dấu và có thể được sử dụng để nối các ô với các loại khác nhau dữ liệu. Vì vậy, nếu trong ô A1 có số 10 và trong ô A2 có dòng chữ "túi", thì kết quả của công thức =A1&A2, chúng ta sẽ được "10 túi". Hơn nữa, kết quả của sự kết hợp như vậy sẽ là một giá trị văn bản.

Trong Excel, công thức là thứ làm cho chương trình trở nên “sống động”. Nhờ tính năng này, Microsoft Excel đã trở nên phổ biến và có lĩnh vực ứng dụng gần như vô tận. Hãy cùng tìm hiểu cách viết công thức chính xác và tận hưởng công việc ở cấp độ cao!

Trong các ô có công thức Ký tự đầu tiên luôn là dấu bằng "=". Vì thế Microsoft Excel hiểu rằng công thức sẽ được viết ra tiếp theo. Nếu dấu này không được đặt hoặc không ở đầu dòng, chương trình sẽ coi như văn bản thông thường đã được nhập vào ô. Khi bạn đã nhập công thức, nhấn Enter, chương trình sẽ tính kết quả và hiển thị giá trị của nó trong ô (mặc dù thực tế sẽ có công thức trong ô). Để xem và sửa công thức, hãy chọn ô mong muốn và thực hiện tất cả các thao tác trong .

Các phần của công thức có thể là:

  • Dấu hiệu của các phép toán và logic
  • Số hoặc văn bản

Nếu một công thức chỉ chứa số và văn bản thì đó là công thức tĩnh; kết quả của nó chỉ có thể thay đổi bằng cách điều chỉnh công thức. Nếu công thức chứa các liên kết đến các ô khác thì đó là công thức động. Khi bạn thay đổi giá trị trong các ô này, công thức sẽ được tính lại ngay lập tức.

Những toán tử nào được sử dụng trong công thức Excel

Microsoft Excel sử dụng các toán tử giống như chúng ta đã học ở trường, thứ tự thực hiện của chúng trong các công thức cũng giống như chúng ta đã quen. Dưới đây là bảng các toán tử được sắp xếp theo mức độ ưu tiên trong tính toán:

Tôi chắc rằng bạn rất quen thuộc với các toán tử này và mức độ ưu tiên tính toán của chúng. Giống như toán thông thường, bạn có thể thay đổi trình tự tính toán bằng dấu ngoặc đơn.

Chúng ta sẽ xem xét việc sử dụng hàm trong một bài riêng về hàm, ở đây tôi sẽ chỉ dành một vài câu cho chủ đề. Hàm Excel là các toán tử đặc biệt thực hiện tính toán phức tạp. Ví dụ: hàm AVERAGE tính giá trị trung bình của một phạm vi đã chọn.

Nhập công thức vào ô

Đã đến lúc phải hành động, chúng ta hãy học cách viết công thức. Ví dụ, trong một ô A4 bạn cần tính tổng các giá trị từ phạm vi A1:A3. Nó có thể được thực hiện hai lối:

  1. thủ công. Đặt con trỏ vào một ô A4 và nhập bằng bàn phím: =A1+A2+A3. Nhấn Enter, chương trình sẽ tính công thức và hiển thị kết quả trong ô
  2. chỉ ra. Thay vì viết địa chỉ ô theo cách thủ công, bạn có thể chỉ định chúng. Viết vào ô A4= , sau đó bấm vào ô A1(hoặc chọn bằng cách sử dụng mũi tên trên bàn phím). Thanh công thức hiển thị =A1. Sau đó, nhấn + trên bàn phím và trỏ đến ô A2. Thêm một ô theo cách tương tự A3. Nhấn Enter để thực hiện phép tính.

Chèn tên vào công thức và sử dụng chúng trong tính toán

Nếu một số ô có tên, tốt hơn là chèn tên vào công thức thay vì chèn liên kết. Điều này sẽ làm tăng thêm sự rõ ràng cho các tính toán. Để chèn một ô được đặt tên vào công thức, hãy sử dụng một trong các phương pháp sau:

  1. Nếu bạn nhớ tên muốn chèn, chỉ cần nhập tên đó vào đúng vị trí trong công thức. Khi bạn nhập, nó sẽ hoạt động, vì vậy bạn có thể nhập các chữ cái đầu tiên của tên và chọn tên từ danh sách xuất hiện.
Tự động hoàn thành khi chỉ định tên ô
  1. Đặt con trỏ vào vị trí mong muốn trong công thức và nhấn F3. Hộp thoại Insert Name sẽ mở ra, chọn tên bạn cần và nhấp đúp vào nó để chèn. Nếu không có tên nào trong sổ làm việc, nhấn F3 sẽ không có tác dụng gì

Ở phần tôi đã hướng dẫn bạn cách gán tên cho một ô hoặc một phạm vi ô. Hôm nay chúng ta sẽ mở rộng những kỹ năng này vì chúng có thể có tác dụng kỳ diệu đối với khả năng đọc công thức.

Vì vậy có thể gán tên cho hằng số. Để làm điều này, hãy làm theo các bước sau:

  1. Bấm vào lệnh ribbon Công thức – Tên được xác định – Gán tên. Cửa sổ Tạo tên sẽ mở ra.
  2. Trong trường Tên, viết tên của hằng số tương lai
  3. Trong trường Phạm vi, chọn phạm vi của hằng số - toàn bộ sổ làm việc hoặc một trang tính cụ thể
  4. Bạn có thể để lại bình luận của mình trong Ghi chú
  5. Trong trường Phạm vi, ghi lại giá trị số mà bạn sẽ đặt tên và nhấp vào OK

Hằng số được đặt tên đã sẵn sàng. Cái này Quyết định tốt nhất hơn là viết giá trị bằng số trong công thức. Đầu tiên, cái tên mang lại ý nghĩa cho ý nghĩa. Thứ hai, giá trị được đặt tên này dễ thay đổi hơn.

Sử dụng sơ đồ tương tự, bạn có thể gán tên cho công thức. Khi viết công thức vào trường Range, đừng quên đặt dấu “=” ở đầu. Hãy tính đến các công thức này vì điều này có thể ảnh hưởng đến kết quả tính toán.

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

Nếu bạn muốn thay đổi công thức (và điều này xảy ra thường xuyên), việc này rất dễ dàng. Như mọi khi, MS Excel cung cấp một số tùy chọn. Đầu tiên, đặt con trỏ vào ô có công thức, sau đó thực hiện một trong các thao tác sau:

  • Thay đổi công thức trên thanh công thức. Bạn có thể chọn các phần của công thức, thay thế chúng bằng các phần khác, chèn và xóa các phần hoặc ký tự riêng lẻ. Khi hoàn tất nhấn Enter
  • Bấm đúp chuột trái vào tế bào hoạt động . Công thức sẽ xuất hiện trong ô thay vì kết quả tính toán. Bây giờ thực hiện thay đổi theo ý muốn và nhấn Enter
  • Chọn một ô và nhấn F2, nó cũng sẽ hiển thị công thức trong ô. Chỉnh sửa và nhấn Enter.

Chế độ tính toán

Microsoft Excel có 3 chế độ tính toán, việc sử dụng khéo léo sẽ cho phép bạn quản lý tốt các công thức và tiết kiệm thời gian làm việc. Để chọn chế độ tính toán, trên ribbon, hãy thực hiện: Công thức – Phép tính – Tùy chọn tính toán. Một danh sách sẽ mở ra để chọn một trong các tham số tính toán:


Các tùy chọn tính toán trong Excel
  1. Tự động. Chế độ này được sử dụng theo mặc định trong chương trình. Công thức được tính toán ngay lập tức, khi ô ảnh hưởng thay đổi, các công thức phụ thuộc sẽ được tính toán lại ngay lập tức. Việc tính toán được thực hiện theo trình tự tự nhiên: đầu tiên là các ô ảnh hưởng, sau đó là các ô phụ thuộc. Chế độ này phù hợp với các tệp nhỏ khi Excel không đặt tải lớn lên bộ xử lý.
  2. Tự động, ngoại trừ các bảng dữ liệu. Tất cả các ô ngoại trừ phạm vi đều được tính toán tự động. Công thức dạng bảng được tính toán thủ công (sau lệnh tính toán lại của bạn). Chế độ này được sử dụng khi sách bài tập Có bàn lớn với những công thức mất nhiều thời gian để tính toán lại. Sau đó, trước tiên bạn sẽ sửa các ô ảnh hưởng bằng dữ liệu gốc, sau đó ra lệnh tính toán lại bảng (đọc cách thực hiện việc này trong đoạn tiếp theo).
  3. thủ công. Công thức không được tính toán lại cho đến khi bạn đưa ra lệnh. Để tính toán công thức, bạn có thể sử dụng các tổ hợp phím sau:
    • F9 – tính lại tất cả các công thức mở tài liệu Excel
    • Shift+F9 – tính toán lại công thức của bảng tính đang hoạt động
    • Ctrl+Shift+F9 – tính toán lại tất cả các công thức.

      Chế độ tính toán này cần thiết khi tính toán lại đồng thời số lượng lớn công thức và bất kỳ thay đổi nào đối với ô đều dẫn đến việc phải chờ đợi lâu cho đến khi mọi thứ được tính toán. Sau đó, chúng tôi bật tính toán lại thủ công và thực hiện tất cả các thay đổi cần thiết. Chỉ sau đó chúng tôi mới tính toán những thay đổi trong công thức.

Như bạn có thể thấy, việc chuyển đổi chế độ một cách chu đáo sẽ cho phép bạn tiết kiệm tài nguyên máy tính và thời gian cá nhân của mình.

Bây giờ bạn đã biết cách viết công thức, bạn có thể đi sâu vào thể dục nhịp điệu. Và trong bài viết tiếp theo tôi sẽ nói về. Trong lúc chờ đợi, hãy đặt câu hỏi của bạn về chủ đề bài viết này, tôi sẽ giải đáp cho mọi người!

Trong Phần 2 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 công thức toán học, thêm hàng và cột vào rồi bàn đã hoàn thành, tìm hiểu về tính 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 cho người mới bắt đầu”, chúng ta đã làm quen với những kiến ​​thức cơ bản nhất chương trình 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 bảng tính là các ô riêng lẻ có dữ liệu 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 bảng thông thường, Excel còn có thể được sử dụng để thực hiện các phép tính toán học 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 hoạt động ngược lại- tăng số chữ số 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.

TRÊN khoảnh khắc này nó ghi lại hàng tháng chi tiêu cá nhânđối với các mặt hàng 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. Cái này rất tâm điểm, 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 đặc biệt“AutoSum”, cho phép bạn tính tổng của nhiều ô 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 ô trống ở bên cạnh dòng mong muốn(trong trường hợp của chúng tôi đây 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 khoảng các ô có giá trị cần được tính tổng. Cùng lúc đó, khung đánh dấu dạng chấm 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"TỔNG" - trước công thức cụ thể, sẽ thêm 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ọ đã làm điều đó với hai những cách khác- trước tiên 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 vào một trình tự nhất định, chẳng hạn như ngày trong tuần, ngày tháng hoặc số dò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. Kẹp nút trái chuột và kéo nó sang bên 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 số chẵn Giá trị kiểu số: 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ụ.

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

Bây giờ hãy áp dụng điều này cơ hội hữu ích chương trình vào bảng của chúng ta, để không phải nhập công thức 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 một số dòng bổ sung. Như mọi khi, điều 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 danh mục 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 cuối cùng hàng hoặc cột sau khi chèn kết thúc trên ở một nơi không cần thiết, chúng có thể được gỡ bỏ dễ dàng. 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 vào phần trên cùng 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 sẽ xuất hiện một menu với 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 việc 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ẽ bao gồm sự khác biệt 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 ta có thể sử dụng tự động điền, giống như chúng ta đã làm trong trường hợp số tiền.

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ị sự khác biệt giữa tổng cộ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 tờ khác nhau 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ề điều này trong lần xuất bản 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 tiếp theo chúng ta sẽ nói chi tiết hơn về hàm số, hiểu khái niệm quan hệ và liên kết tuyệt đối, chúng ta chắc chắn sẽ nắm vững thêm một vài điều nữa yếu tố hữu ích bảng chỉnh sửa và nhiều hơn nữa.

Không phải tự nhiên mà Excel được hàng triệu người dùng yêu thích: từ sinh viên đến các chuyên gia hàng đầu và doanh nhân. Khả năng làm việc với dữ liệu số của anh ấy đơn giản là rất lớn. Hầu hết các khả năng của nó đều dựa trên việc sử dụng các công thức và hàm dùng để tính toán. Vậy công thức Excel viết thế nào?

Việc nhập công thức luôn bắt đầu bằng dấu bằng =

Sau đó, bạn viết công thức của mình bằng cách sử dụng:

  • địa chỉ ô,
  • dấu + (cộng), - (trừ), * (nhân), / (chia),
  • dấu ngoặc,
  • dấu phẩy
  • dấu hai chấm.

Ví dụ: bạn nói với Excel rằng bạn cần cộng các số trong ô A1 và C1, sau đó trừ số đó khỏi ô H1 từ tổng này. Để thực hiện việc này, bạn nhập vào ô mà bạn cần kết quả =A1+C1-H1 và nhấn enter.

Hoặc bạn đặt dấu = rồi không nhập địa chỉ ô mà chỉ cần chọn chúng và Excel sẽ tự thay thế địa chỉ đó vào công thức.

Nghĩa là, nếu chúng ta nói về ví dụ trên, hãy đặt dấu “=”, nhấp vào ô A1, đặt dấu “+”, nhấp vào ô C1, đặt dấu “-”, nhấp vào ô H1, nhấn enter.

Nếu bạn thực hiện các thay đổi đối với bảng, chẳng hạn như thay đổi số liên quan đến phép tính, công thức sẽ được tính toán lại!

Những lỗi thường gặp khi viết công thức trong Excel:

  • nhập số sai phần phân đoạn (tùy thuộc vào phiên bản Excel, dấu phân cách giữa phần số nguyên và phần phân số có thể là dấu phẩy hoặc dấu chấm! Theo quy định, trong hầu hết các trường hợp, dấu phẩy được sử dụng, hãy kiểm tra bằng cách đặt định dạng số hoặc tiền tệ có chữ số thập phân)
  • nhập địa chỉ ô- CHỈ MỘT bằng chữ Latinh, chuyển ngôn ngữ và nhập các ký tự Latinh - hoặc chỉ cần nhấp vào các ô và Excel sẽ nhập địa chỉ chính xác riêng tôi
  • dấu ngoặc đơn không đóng
  • bạn không thấy kết quả mà chỉ thấy các ký hiệu băm - ##### - kết quả đơn giản là không vừa với ô) Chỉ cần mở rộng cột và bạn sẽ hài lòng :)
  • Nếu bạn có thắc mắc hoặc không biết lỗi xuất phát từ đâu, hãy viết bình luận, tôi sẽ luôn hỗ trợ!

Ví dụ đơn giản nhất về công thức: tổng của hai ô (A2 và B2)

Xin lưu ý rằng trong chính ô chúng ta thấy kết quả (số) và trong thanh công thức chúng ta có một công thức còn lại, nó có thể được chỉnh sửa hoặc sao chép.

Công thức trong Excel - Ví dụ

Trong hình, bạn có thể thấy nhiều ví dụ khác nhau về cách viết nhiều nhất công thức đơn giản trong máy tính bảng

Và một video ngắn hướng dẫn cách tạo công thức trong Excel nhanh chóng và dễ dàng

Trong bài học này, chúng ta sẽ xem xét cách tạo một công thức phức tạp trong Excel và cũng xem xét những lỗi điển hình, xảy ra do sự thiếu chú ý của những người mới sử dụng. Nếu bạn mới làm việc trong Excel gần đây, chúng tôi khuyên bạn trước tiên nên xem lại bài học mà chúng ta đã thảo luận về cách tạo các công thức đơn giản.

Cách tạo công thức phức tạp trong Excel

Trong ví dụ bên dưới, chúng tôi sẽ trình bày cách Excel tính toán các công thức phức tạp dựa trên thứ tự thực hiện các phép tính. TRONG trong ví dụ này Chúng tôi muốn tính thuế doanh thu cho dịch vụ ăn uống. Để thực hiện việc này, hãy viết biểu thức sau vào ô D4: =(D2+D3)*0,075. Công thức này sẽ cộng chi phí của tất cả các mục hóa đơn rồi nhân với số tiền thuế bán hàng 7,5% (viết là 0,075).

Excel tuân theo thứ tự này và cộng các giá trị trong ngoặc đơn trước: (44.85+39.90)=$84.75 . Sau đó nhân số này với thuế suất: $84.75*0.075 . Kết quả tính toán cho thấy thuế bán hàng sẽ được $6.36 .

Điều cực kỳ quan trọng là nhập các công thức phức tạp theo đúng thứ tự. Nếu không thì tính toán excel có thể trở nên không chính xác. Trong trường hợp của chúng tôi, nếu không có dấu ngoặc đơn, phép nhân được thực hiện trước và kết quả sẽ không chính xác. Các dấu ngoặc đơn là cách tốt nhất xác định thứ tự tính toán trong Excel.

Tạo các công thức phức tạp bằng quy trình làm việc

Trong ví dụ bên dưới, chúng tôi sẽ sử dụng các liên kết cùng với dữ liệu định lượng để tạo ra một công thức phức tạp giúp tính toán tổng chi phí của hóa đơn thực phẩm. Công thức sẽ tính giá thành của từng món trong menu rồi cộng tất cả các giá trị lại với nhau.


Bạn có thể thêm dấu ngoặc đơn vào bất kỳ công thức nào để dễ hiểu hơn. Mặc dù điều này sẽ không thay đổi kết quả tính toán trong ví dụ này nhưng chúng ta vẫn có thể đặt phép nhân trong ngoặc đơn. Điều này sẽ làm rõ rằng nó được thực hiện trước khi bổ sung.

Excel không phải lúc nào cũng cảnh báo bạn về các lỗi trong một công thức, vì vậy bạn cần phải tự mình kiểm tra tất cả các công thức của mình. Để tìm hiểu cách thực hiện việc này, hãy xem bài học Kiểm tra công thức.