Tính NPV trong Excel (ví dụ). Chương trình Microsoft Excel: tính số tiền

Xin chào!

Nhiều người không sử dụng Excel thậm chí còn không tưởng tượng được những cơ hội mà chương trình này mang lại! Hãy nghĩ xem: tự động thêm các giá trị từ công thức này sang công thức khác, tìm kiếm các dòng cần thiết trong văn bản, thêm theo điều kiện, v.v. - nói chung, về cơ bản, về cơ bản là một ngôn ngữ lập trình mini để giải các bài toán “hẹp” (thành thật mà nói, bản thân tôi đã lâu không coi Excel là một chương trình và gần như chưa bao giờ sử dụng nó)...

Trong bài viết này, tôi muốn đưa ra một số ví dụ về cách bạn có thể nhanh chóng giải quyết các công việc văn phòng hàng ngày: thêm một cái gì đó, trừ một cái gì đó, tính tổng (bao gồm cả một điều kiện), thay thế các giá trị từ bảng này sang bảng khác, v.v. Nghĩa là, bài viết này sẽ giống như một hướng dẫn nhỏ về việc tìm hiểu những gì bạn cần cho công việc (chính xác hơn là bắt đầu sử dụng Excel và cảm nhận toàn bộ sức mạnh của sản phẩm này!).

Có thể là nếu tôi đọc một bài viết tương tự cách đây 15-17 năm, thì tôi đã bắt đầu sử dụng Excel nhanh hơn nhiều (và sẽ tiết kiệm được rất nhiều thời gian để giải quyết những vấn đề “đơn giản”) (lưu ý: theo tôi hiểu bây giờ) nhiệm vụ)...

Lưu ý: Tất cả ảnh chụp màn hình bên dưới là từ Excel 2016 (là ảnh mới nhất hiện nay).

Nhiều người dùng mới làm quen, sau khi khởi chạy Excel, đã hỏi một câu hỏi kỳ lạ: "Ồ, bảng ở đâu?" Trong khi đó, tất cả các ô bạn nhìn thấy sau khi bắt đầu chương trình đều là một bảng lớn!

Bây giờ đến vấn đề chính: bất kỳ ô nào cũng có thể chứa văn bản, một số số hoặc công thức. Ví dụ: ảnh chụp màn hình bên dưới hiển thị một ví dụ minh họa:

• bên trái: ô (A1) chứa số nguyên tố “6”. Xin lưu ý rằng khi bạn chọn ô này, thanh công thức (Fx) chỉ hiển thị số "6".
• bên phải: trong ô (C1) cũng có số đơn giản là “6”, nhưng nếu chọn ô này sẽ thấy công thức "=3+3" - đây là một tính năng quan trọng trong Excel!

Chỉ một số (ở bên trái) và một công thức tính toán (ở bên phải)

Vấn đề là Excel có thể tính toán giống như một máy tính nếu bạn chọn một ô nào đó rồi viết công thức, ví dụ "=3+5+8" (không có dấu ngoặc kép). Bạn không cần phải viết kết quả - Excel sẽ tự tính toán và hiển thị trong ô (như ô C1 trong ví dụ trên)!

Nhưng bạn có thể viết công thức và thêm không chỉ các số mà còn cả các số đã được tính trong các ô khác. Trong ảnh chụp màn hình bên dưới, trong ô A1 và B1 lần lượt có số 5 và 6. Trong ô D1 tôi muốn lấy tổng của chúng - bạn có thể viết công thức theo hai cách:

• đầu tiên: “=5+6” (không thuận tiện lắm, hãy tưởng tượng rằng trong ô A1 - số của chúng tôi cũng được tính theo một số công thức khác và nó thay đổi. Bạn sẽ không thay thế một số mới thay vì 5 mỗi lần?!);
• thứ hai: “=A1+B1” - nhưng đây là tùy chọn lý tưởng, chúng tôi chỉ cần thêm giá trị của các ô A1 và B1 (bất chấp số trong đó là bao nhiêu!)

Thêm các ô đã chứa số

Lan truyền công thức sang các ô khác

Trong ví dụ trên, chúng tôi đã cộng hai số ở cột A và B ở hàng đầu tiên. Nhưng chúng ta có 6 dòng và hầu hết trong các bài toán thực tế, bạn cần thêm số vào mỗi dòng! Để làm điều này, bạn có thể:

1. ở dòng 2 viết công thức "=A2+B2", ở dòng 3 - "=A3+B3", v.v. (việc này dài và tẻ nhạt, tùy chọn này không bao giờ được sử dụng);
2. chọn ô D1 (đã có công thức), sau đó di chuyển con trỏ chuột đến góc bên phải của ô để xuất hiện dấu thập màu đen (xem ảnh chụp màn hình bên dưới). Sau đó giữ nút bên trái và kéo dài công thức ra toàn bộ cột. Thuận tiện và nhanh chóng! (Lưu ý: bạn cũng có thể sử dụng tổ hợp Ctrl+C và Ctrl+V cho các công thức (sao chép và dán tương ứng)).

Nhân tiện, hãy chú ý đến việc Excel tự chèn công thức vào từng dòng. Nghĩa là, nếu bây giờ bạn chọn một ô, chẳng hạn như D2, bạn sẽ thấy công thức "=A2+B2" (tức là Excel tự động thay thế công thức và đưa ra kết quả ngay lập tức) .

Cách đặt hằng số (một ô sẽ không thay đổi khi bạn sao chép công thức)

Thông thường, trong các công thức (khi bạn sao chép chúng) yêu cầu một số giá trị không thay đổi. Giả sử một nhiệm vụ đơn giản: chuyển đổi giá bằng đô la sang rúp. Giá trị của đồng rúp được chỉ định trong một ô, trong ví dụ bên dưới của tôi là G2.

Tiếp theo, tại ô E2, viết công thức “=D2*G2” và nhận kết quả. Nhưng nếu kéo dài công thức như đã làm trước đây, chúng ta sẽ không thấy kết quả ở các dòng khác, bởi vì Excel sẽ đưa công thức “D3*G3” vào dòng 3, “D4*G4” ở dòng 4, v.v. Chúng ta cần G2 vẫn là G2 ở mọi nơi...

Để thực hiện việc này, chỉ cần thay đổi ô E2 - công thức sẽ có dạng "=D2*$G$2". Những thứ kia. ký hiệu đô la $ - cho phép bạn chỉ định một ô sẽ không thay đổi khi bạn sao chép công thức (tức là chúng ta nhận được một hằng số, ví dụ bên dưới)...

Cách tính số tiền (công thức SUM và SUMIFS)

Tất nhiên, bạn có thể soạn công thức theo cách thủ công bằng cách nhập “=A1+B1+C1”, v.v. Nhưng Excel có các công cụ nhanh hơn và tiện lợi hơn.

Một trong những cách dễ nhất để thêm tất cả các ô đã chọn là sử dụng tùy chọn tự động tính (Excel sẽ tự viết công thức và chèn vào ô).

1. Đầu tiên, chọn các ô (xem ảnh chụp màn hình bên dưới);
2. Tiếp theo, mở phần “Công thức”;
3. Bước tiếp theo là nhấp vào nút "AutoSum". Kết quả của phép cộng sẽ xuất hiện bên dưới các ô bạn đã chọn;
4. nếu bạn chọn ô có kết quả (trong trường hợp của tôi đó là ô E8) - sau đó bạn sẽ thấy công thức "=SUM(E2:E7)" .
5. do đó viết công thức "=TỔNG(xx)", thay vào đó ở đâu xxđặt (hoặc chọn) bất kỳ ô nào, bạn có thể đọc nhiều phạm vi ô, cột, hàng...

Rất thường xuyên khi làm việc, bạn không chỉ cần tổng của toàn bộ cột mà còn cần tổng của một số hàng nhất định (tức là có chọn lọc). Hãy giả sử một nhiệm vụ đơn giản: bạn cần nhận được số tiền lãi từ một số công nhân (tất nhiên là phóng đại, nhưng ví dụ này còn hơn cả thực tế).

Tôi sẽ chỉ sử dụng 7 hàng trong bảng của mình (để rõ ràng), nhưng một bảng thực có thể lớn hơn nhiều. Giả sử chúng ta cần tính toán tất cả lợi nhuận mà “Sasha” kiếm được. Công thức sẽ trông như thế nào:

1. "=SUMIFS(F2:F7 ;A2:A7 ;"Sasha") " - (lưu ý: chú ý đến dấu ngoặc kép cho điều kiện - chúng phải giống như trong ảnh chụp màn hình bên dưới chứ không phải như tôi đã viết trên blog của mình bây giờ). Cũng lưu ý rằng Excel, khi nhập phần đầu của một công thức (ví dụ: “SUM…”), chính nó sẽ gợi ý và thay thế các tùy chọn có thể có - và có hàng trăm công thức trong Excel!;
2. F2:F7 là phạm vi mà các số từ các ô sẽ được cộng vào (tổng hợp);
3. A2:A7 là cột mà điều kiện của chúng ta sẽ được kiểm tra;
4. “Sasha” là một điều kiện, những hàng có “Sasha” ở cột A sẽ được thêm vào (chú ý đến ảnh chụp màn hình biểu thị bên dưới).

Lưu ý: có thể có một số điều kiện và chúng có thể được kiểm tra bằng các cột khác nhau.

Cách đếm số dòng (có 1, 2 điều kiện trở lên)

Một nhiệm vụ khá điển hình: đếm không phải tổng trong các ô mà là số hàng thỏa mãn một số điều kiện. Chà, ví dụ: tên “Sasha” xuất hiện bao nhiêu lần trong bảng bên dưới (xem ảnh chụp màn hình). Hiển nhiên là 2 lần (nhưng điều này là do bảng quá nhỏ và được lấy làm ví dụ minh họa). Làm thế nào để tính toán điều này bằng một công thức? Công thức:

"=COUNTIF(A2:A7,A2)" - Ở đâu:

• A2:A7- phạm vi các hàng sẽ được kiểm tra và đếm;
• A2- một điều kiện đã được đặt (lưu ý rằng bạn có thể viết một điều kiện như “Sasha” hoặc bạn có thể chỉ định một ô).

Kết quả được hiển thị ở phía bên phải của màn hình bên dưới.

Bây giờ hãy tưởng tượng một nhiệm vụ nâng cao hơn: bạn cần đếm các dòng nơi tên “Sasha” xuất hiện và vị trí trong cột AND số “6” sẽ xuất hiện. Nhìn về phía trước, tôi sẽ nói rằng chỉ có một dòng như vậy (ảnh chụp màn hình có ví dụ bên dưới).

Công thức sẽ trông như sau:

=COUNTIFS(A2:A7 ;A2 ;B2:B7 ;"6") (lưu ý: chú ý đến dấu ngoặc kép - chúng phải giống như trong ảnh chụp màn hình bên dưới chứ không giống như của tôi), Ở đâu:

A2:A7 ;A2- phạm vi đầu tiên và điều kiện tìm kiếm (tương tự như ví dụ trên);

B2:B7 ;"6"- phạm vi thứ hai và điều kiện tìm kiếm (lưu ý rằng điều kiện có thể được đặt theo nhiều cách khác nhau: chỉ định một ô hoặc chỉ cần viết văn bản/số trong dấu ngoặc kép).

Cách tính tỷ lệ phần trăm của số tiền

Đây cũng là một câu hỏi khá phổ biến mà tôi thường gặp phải. Nói chung, theo như tôi có thể tưởng tượng, nó phát sinh thường xuyên nhất - do mọi người bối rối và không biết họ đang tìm kiếm tỷ lệ phần trăm (và nói chung, họ không hiểu rõ chủ đề về tỷ lệ phần trăm ( mặc dù bản thân tôi không phải là một nhà toán học vĩ đại, và vẫn ... )).

Cách đơn giản nhất, đơn giản là không thể bị nhầm lẫn, là sử dụng quy tắc hoặc tỷ lệ “vuông”. Toàn bộ vấn đề được hiển thị trong ảnh chụp màn hình bên dưới: nếu bạn có tổng số tiền, giả sử trong ví dụ của tôi, con số này là 3060 - ô F8 (tức là nó là 100% lợi nhuận và một phần trong số đó được tạo ra bởi “Sasha”), bạn cần tìm cái nào... ).

Theo tỷ lệ, công thức sẽ như thế này: =F10*G8/F8(tức là chéo: đầu tiên chúng ta nhân hai số đã biết theo đường chéo, sau đó chia cho số thứ ba còn lại). Về nguyên tắc, sử dụng quy tắc này, hầu như không thể nhầm lẫn về tỷ lệ phần trăm.

Trên thực tế, đây là nơi tôi kết thúc bài viết này. Tôi không ngại nói rằng khi đã thành thạo mọi thứ được viết ở trên (và chỉ có “năm” công thức được đưa ra ở đây), bạn sẽ có thể tự học Excel, xem qua trợ giúp, xem, thử nghiệm và phân tích. Tôi thậm chí còn nói nhiều hơn, mọi thứ mà tôi mô tả ở trên sẽ bao gồm nhiều nhiệm vụ và sẽ cho phép bạn giải quyết những vấn đề phổ biến nhất mà bạn thường bối rối (nếu bạn không biết các khả năng của Excel) và không biết cách làm nhanh hơn...

Công thức là một biểu thức toán học được tạo ra để tính toán kết quả và có thể phụ thuộc vào nội dung của các ô khác. Công thức trong một ô có thể chứa dữ liệu, liên kết đến các ô khác và cũng là dấu hiệu cho thấy các hành động cần được thực hiện.

Việc sử dụng tham chiếu ô cho phép tính toán lại kết quả của công thức khi nội dung của các ô có trong công thức thay đổi.

Trong Excel, công thức bắt đầu bằng dấu =. Dấu ngoặc đơn () có thể được sử dụng để xác định thứ tự của các phép toán.

Excel hỗ trợ các toán tử sau:

• Các phép tính toán học:
  • phép cộng (+);
  • phép nhân (*);
  • tìm phần trăm (%);
  • phép trừ(-);
  • phân công(/);
  • số mũ (^).
• Toán tử so sánh:
  • = bằng nhau;
  • < меньше;
  • > hơn nữa;
  • <= меньше или равно;
  • >= lớn hơn hoặc bằng;
  • <>không công bằng.
• Các nhà khai thác viễn thông:
  • : phạm vi;
  • ; Liên hiệp;
  • & toán tử để nối văn bản.

Bảng 22. Ví dụ về công thức

Bài tập

Chèn công thức -25-A1+AZ

Nhập trước bất kỳ số nào vào ô A1 và A3.

1. Chọn ô cần thiết, ví dụ B1.
2. Bắt đầu nhập công thức bằng dấu =.
3. Nhập số 25 rồi đến toán tử (dấu -).
4. Nhập tham chiếu đến toán hạng đầu tiên, ví dụ bằng cách nhấp vào ô A1 mong muốn.
5. Nhập toán tử sau (+).
6. Bấm vào ô là toán hạng thứ hai trong công thức.
7. Hoàn thành công thức bằng cách nhấn phím Đi vào. Tại ô B1 bạn sẽ nhận được kết quả.

Tự động tính tổng

Cái nút Tự động tính tổng- ∑ có thể dùng để tự động tạo công thức tính tổng diện tích các ô liền kề bên trái trong dòng này và trực tiếp cao hơn trong cột này.

1. Chọn ô để đặt kết quả tính tổng.
2. Nhấn nút AutoSum - ∑ hoặc nhấn tổ hợp phím Alt+=. Excel sẽ quyết định vùng nào sẽ được đưa vào phạm vi tổng và sẽ đánh dấu vùng đó bằng khung chuyển động có chấm chấm được gọi là đường viền.
3. Nhấp chuột Đi vàođể chấp nhận vùng mà Excel đã chọn hoặc dùng chuột chọn vùng mới rồi nhấn Enter.

Hàm AutoSum tự động chuyển đổi khi các ô được thêm hoặc xóa trong một vùng.

Bài tập

Tạo bảng và tính toán bằng công thức

1. Nhập dữ liệu số vào các ô như trong bảng. 23.

Bảng 23. Bảng dữ liệu gốc

1. Chọn ô B7 để tính tổng theo chiều dọc.
2. Nhấp vào nút Tự tính tổng - ∑ hoặc nhấp vào Alt+=.
3. Lặp lại bước 2 và 3 cho ô C7 và D7.

Tính số lượng nhân viên không có trình độ học vấn cao hơn (sử dụng công thức B7-VZ).

1. Chọn ô B8 và gõ dấu (=).
2. Bấm vào ô B7, đây là toán hạng đầu tiên trong công thức.
3. Nhập dấu (-) trên bàn phím và nhấp vào ô V3, đây là toán hạng thứ hai trong công thức (công thức sẽ được nhập).
4. Nhấp chuột Đi vào(kết quả sẽ được tính ở ô B8).
5. Lặp lại các bước 5-8 để tính toán bằng cách sử dụng các công thức thích hợp trong ô C8 và 08.
6. Lưu file với tên Education_employees.x1s.

Bảng 24.Kết quả tính toán

Công thức trùng lặp bằng cách sử dụng dấu điền

Diện tích ô (ô) có thể được nhân lên bằng cách sử dụng điền vào điểm đánh dấu. Như được hiển thị trong phần trước, núm điều khiển điền là điểm điều khiển ở góc dưới bên phải của ô đã chọn.

Thường cần phải sao chép không chỉ dữ liệu mà còn cả các công thức chứa các liên kết địa chỉ. Quá trình sao chép công thức bằng cách sử dụng núm điều khiển điền cho phép bạn sao chép công thức đồng thời thay đổi các tham chiếu địa chỉ trong công thức.

1. Chọn ô chứa công thức để sao chép.
2. Lôi kéo đánh dấu điềnđi đúng hướng. Công thức sẽ được sao chép trong tất cả các ô.

Quá trình này thường được sử dụng khi sao chép công thức trong các hàng hoặc cột chứa cùng loại dữ liệu. Khi sao chép công thức bằng cách sử dụng điểm đánh dấu điền, cái gọi là địa chỉ tương đối của các ô trong công thức sẽ thay đổi (các liên kết tương đối và tuyệt đối sẽ được mô tả chi tiết bên dưới).

Bài tập

Sao chép công thức

1.Mở file Staff_Education.x1s.

1. Nhập công thức tính tổng ô tự động =SUM(VZ:03) vào ô E3.
2. Sao chép công thức bằng cách kéo chốt điền vào các ô E4:E8.
3. Hãy xem địa chỉ tương đối của các ô thay đổi như thế nào trong công thức kết quả (Bảng 25) và lưu tệp.

Bảng 25. Thay đổi địa chỉ ô khi sao chép công thức

Tham chiếu tương đối và tuyệt đối

Các công thức thực hiện các phép tính trong bảng sử dụng cái gọi là tham chiếu để đánh địa chỉ các ô. Tham chiếu ô có thể là liên quan đến hoặc tuyệt đối.

Sử dụng tham chiếu tương đối cũng tương tự như chỉ hướng di chuyển trên đường - "đi ba dãy nhà về phía bắc, sau đó hai dãy nhà về phía tây". Làm theo những hướng dẫn này từ những nơi xuất phát khác nhau sẽ dẫn đến những đích đến khác nhau.

Ví dụ: công thức tính tổng các số trong một cột hoặc hàng thường được sao chép cho các số hàng hoặc cột khác. Những công thức như vậy sử dụng tham chiếu tương đối (xem ví dụ trước trong Bảng 25).

Tham chiếu tuyệt đối đến một ô. hoặc vùng ô sẽ luôn tham chiếu đến cùng một địa chỉ hàng và cột. Khi so sánh với chỉ đường trên đường phố, nó sẽ giống như thế này: “Đi tới giao lộ Arbat và Boulevard Ring”. Bất kể bạn bắt đầu từ đâu, nó sẽ dẫn đến cùng một nơi. Nếu một công thức yêu cầu địa chỉ ô không thay đổi khi sao chép thì phải sử dụng tham chiếu tuyệt đối (định dạng bản ghi $A$1). Ví dụ: khi công thức tính các phân số của tổng số tiền, tham chiếu đến ô chứa tổng số tiền sẽ không thay đổi khi sao chép.

Ký hiệu đô la ($) sẽ xuất hiện trước cả tham chiếu cột và tham chiếu hàng (ví dụ: $C$2). Nhấn F4 liên tiếp sẽ thêm hoặc xóa ký hiệu trước số cột hoặc hàng trong tham chiếu (C$2 hoặc $C2). - cái gọi là liên kết hỗn hợp).

1. Tạo một bảng tương tự như bảng dưới đây.

Bảng 26. Tính lương

1. Trong ô СЗ nhập công thức tính lương của Ivanov =В1*ВЗ.

Khi sao chép công thức của ví dụ này bằng tham chiếu tương đối, thông báo lỗi (#VALUE!) sẽ xuất hiện trong ô C4, vì địa chỉ tương đối của ô B1 sẽ thay đổi và công thức =B2*B4 sẽ được sao chép sang ô C4;

1. Đặt tham chiếu tuyệt đối cho ô B1 bằng cách đặt con trỏ vào thanh công thức trên B1 và ​​nhấn phím F4. Công thức trong ô C3 sẽ có dạng =$B$1*BZ.
2. Sao chép công thức vào ô C4 và C5.
3. Lưu tệp (Bảng 27) dưới tên Lương.xls.

Bảng 27. Kết quả tính lương

Tên trong công thức

Tên trong công thức dễ nhớ hơn địa chỉ ô, vì vậy bạn có thể sử dụng phạm vi được đặt tên (một hoặc nhiều ô) thay vì tham chiếu tuyệt đối. Các quy tắc sau phải được tuân theo khi tạo tên:

• tên có thể chứa không quá 255 ký tự;
• tên phải bắt đầu bằng một chữ cái và có thể chứa bất kỳ ký tự nào ngoại trừ dấu cách;
• tên không được giống với tài liệu tham khảo, chẳng hạn như VZ, C4;
• tên không nên sử dụng các chức năng Excel như TỔNG NẾU và như thế.

Trên thực đơn Điền tên Có hai lệnh khác nhau để tạo các vùng được đặt tên: Tạo và Gán.

Đội Tạo cho phép bạn chỉ định (nhập) tên được yêu cầu ( chỉ một), Gán lệnh sử dụng các nhãn được đặt trên trang tính làm tên vùng (cho phép bạn tạo nhiều tên cùng một lúc).

Tạo tên

1. Chọn ô B1 (Bảng 26).
2. Chọn từ trình đơn Lệnh Insert, Name (Chèn, Tên) Gán (Xác định).
3. Nhập tên của bạn Đánh giá theo giờ và nhấn OK.
4. Chọn ô B1 và ​​đảm bảo rằng trường tên cho biết Tỷ lệ hàng giờ.

Tạo nhiều tên

1. Chọn ô VZ:C5 (Bảng 27).
2. Chọn từ trình đơn Lệnh Insert, Name (Chèn, Tên) Tạo (Create), một hộp thoại sẽ xuất hiện Tạo tên(Hình 88).
3. Đảm bảo nút radio ở cột bên trái được chọn và nhấp vào ĐƯỢC RỒI.
4. Chọn các ô VZ:NZ và đảm bảo rằng trường tên có nội dung Ivanov.

Cơm. 88. Hộp thoại Tạo tên

Bạn có thể chèn tên vào công thức thay vì liên kết tuyệt đối.

1. Trong thanh công thức, đặt con trỏ vào nơi bạn muốn thêm tên.
2. Chọn từ trình đơn Lệnh Insert, Name (Chèn, Tên) Dán (Paste), Hộp thoại Chèn Tên xuất hiện.

1. Chọn tên mong muốn từ danh sách và nhấp vào OK.

Lỗi trong công thức

Nếu xảy ra lỗi khi nhập công thức hoặc dữ liệu, thông báo lỗi sẽ xuất hiện trong ô kết quả. Ký tự đầu tiên của tất cả các giá trị lỗi là ký tự #. Các giá trị lỗi phụ thuộc vào loại lỗi được tạo ra.

Excel không thể nhận ra tất cả các lỗi, nhưng những lỗi được phát hiện thì phải sửa được.

Lỗi # # # # xuất hiện khi số đã nhập không vừa trong ô. Trong trường hợp này, bạn nên tăng chiều rộng cột.

Lỗi #DIV/0! xuất hiện khi một công thức cố gắng chia cho 0. Điều này thường xảy ra nhất khi số chia là tham chiếu ô chứa giá trị null hoặc giá trị trống.

Lỗi #N/A! là viết tắt của thuật ngữ "dữ liệu không xác định". Lỗi này cho biết rằng công thức đang sử dụng tham chiếu ô trống.

Lỗi #NAME? xuất hiện khi tên được sử dụng trong công thức đã bị xóa hoặc chưa được xác định trước đó. Để sửa, xác định hoặc sửa tên vùng dữ liệu, tên hàm, v.v.

Lỗi #EMPTY! xuất hiện khi có sự giao nhau giữa hai vùng không thực sự có các ô chung. Thông thường, lỗi chỉ ra rằng đã xảy ra lỗi khi nhập tham chiếu đến phạm vi ô.

Số lỗi! xuất hiện khi một hàm có đối số là số sử dụng định dạng hoặc giá trị đối số không chính xác.

Lỗi #VALUE! xuất hiện khi một công thức sử dụng đối số hoặc loại toán hạng không hợp lệ. Ví dụ: văn bản được nhập thay vì giá trị số hoặc logic cho toán tử hoặc hàm.

Ngoài các lỗi được liệt kê, một liên kết tròn có thể xuất hiện khi nhập công thức.

Tham chiếu vòng xảy ra khi một công thức trực tiếp hoặc gián tiếp bao gồm các tham chiếu đến ô riêng của nó. Tham chiếu vòng có thể gây ra biến dạng trong tính toán bảng tính và do đó được coi là lỗi trong hầu hết các ứng dụng. Khi bạn nhập tham chiếu vòng, một thông báo cảnh báo sẽ xuất hiện (Hình 89).

Để sửa lỗi, hãy xóa ô gây ra tham chiếu vòng, chỉnh sửa hoặc nhập lại công thức.

Các hàm trong Excel

Các phép tính phức tạp hơn trong bảng Excel được thực hiện bằng các hàm đặc biệt (Hình 90). Một danh sách các loại chức năng có sẵn khi bạn chọn một lệnh Chức năng trong menu Chèn (Chèn, Chức năng).

Các chức năng tài chính thực hiện các phép tính như tính toán số tiền thanh toán cho khoản vay, số tiền thanh toán lợi nhuận cho các khoản đầu tư, v.v.

Hàm Ngày và Giờ cho phép bạn làm việc với các giá trị ngày và giờ trong công thức. Ví dụ: bạn có thể sử dụng ngày hiện tại trong công thức bằng hàm HÔM NAY.

Cơm. 90. Trình hướng dẫn chức năng

Các hàm toán học thực hiện đơn giản và các phép tính toán học phức tạp như tính tổng của một phạm vi ô, giá trị tuyệt đối của một số, làm tròn số, v.v.

Hàm thống kê cho phép bạn thực hiện phân tích dữ liệu thống kê. Ví dụ: bạn có thể xác định giá trị trung bình và phương sai của mẫu và hơn thế nữa.

Các hàm cơ sở dữ liệu có thể được sử dụng để thực hiện các phép tính và chọn bản ghi dựa trên các điều kiện.

Hàm văn bản cung cấp cho người dùng khả năng xử lý văn bản. Ví dụ: bạn có thể nối nhiều chuỗi bằng hàm KẾT NỐI.

Các hàm logic được thiết kế để kiểm tra một hoặc nhiều điều kiện. Ví dụ: hàm IF cho phép bạn xác định xem một điều kiện đã chỉ định có đúng hay không và trả về một giá trị nếu điều kiện đó đúng và giá trị khác nếu điều kiện đó sai.

Chức năng Kiểm tra thuộc tính và giá trị nhằm mục đích xác định dữ liệu được lưu trữ trong một ô. Các hàm này kiểm tra giá trị trong ô dựa trên điều kiện và trả về giá trị tùy theo kết quả Đúng hay sai.

Để thực hiện tính toán bảng bằng các hàm dựng sẵn, chúng tôi khuyên bạn nên sử dụng Trình hướng dẫn hàm. Hộp thoại Function Wizard xuất hiện khi bạn chọn lệnh Chức năng trong menu Chèn hoặc nhấn nút , trên thanh công cụ chuẩn. Trong quá trình đối thoại với trình hướng dẫn, bạn cần chỉ định các đối số của hàm đã chọn; để thực hiện việc này, bạn cần điền vào các trường trong hộp thoại các giá trị hoặc địa chỉ tương ứng của các ô trong bảng.

Bài tập

Tính giá trị trung bình mỗi dòng trong file Education.xls.

1. Chọn ô F3 và nhấp vào nút Thuật sĩ chức năng.
2. Trong cửa sổ hộp thoại đầu tiên của Trình hướng dẫn Hàm, từ danh mục Thống kê, chọn hàm TRUNG BÌNH, nhấn nút Hơn nữa.
3. Hộp thoại thứ hai của Function Wizard phải cung cấp các đối số. Con trỏ đầu vào nằm trong trường đầu vào của đối số đầu tiên. Trong trường này là một số đối số! nhập địa chỉ phạm vi B3:D3 (Hình 91).
4. Nhấp chuột ĐƯỢC RỒI.
5. Sao chép công thức kết quả vào các ô F4:F6 và lưu tệp (Bảng 28).

Cơm. 91. Nhập một đối số trong trình hướng dẫn hàm

Bảng 28. Bảng kết quả tính toán sử dụng hàm Wizard

Để nhập một phạm vi ô vào cửa sổ Trình hướng dẫn Chức năng, bạn có thể sử dụng chuột để khoanh tròn phạm vi này trên bảng tính (trong ví dụ: B3:D3). Nếu cửa sổ Function Wizard bao phủ các ô mong muốn, bạn có thể di chuyển hộp thoại. Sau khi chọn một phạm vi ô (B3:D3), một khung chấm đang chạy sẽ xuất hiện xung quanh nó và địa chỉ của phạm vi ô đã chọn sẽ tự động xuất hiện trong trường đối số.

Và công thức nào được sử dụng để tính toán chỉ báo này, nhưng nó yêu cầu có sẵn các công cụ đơn giản cho phép bạn tính NPV nhanh hơn so với cách tính thủ công hoặc sử dụng máy tính thông thường.

Một môi trường đa chức năng sẽ hỗ trợ họ, cho phép họ tính toán NPV bằng cách sử dụng dữ liệu dạng bảng hoặc sử dụng các hàm đặc biệt.

Hãy xem một ví dụ giả định mà chúng ta sẽ giải quyết bằng cách áp dụng công thức tính NPV mà chúng ta đã biết, sau đó chúng ta sẽ lặp lại các phép tính của mình bằng khả năng của Excel.

Bài toán tìm NPV

Ví dụ. Những cái đầu tiên ở A là 10.000 rúp. Hàng năm – 10%. Diễn biến doanh thu từ năm 1 đến năm 10 được trình bày trong bảng dưới đây:

Để rõ ràng, dữ liệu tương ứng có thể được trình bày bằng đồ họa:

Hình 1. Biểu diễn đồ họa của dữ liệu ban đầu để tính NPV

Giải pháp chuẩn.Để giải bài toán, chúng ta sẽ sử dụng công thức NPV đã biết:

Chúng tôi chỉ cần thay thế các giá trị đã biết vào đó, sau đó chúng tôi tính tổng. Đối với những tính toán này, chúng ta sẽ cần một máy tính:

NPV = -10000/1,1 0 + 1100/1,1 1 + 1200/1,1 2 + 1300/1,1 3 + 1450/1,1 4 + 1600/1,1 5 + 1720/1,1 6 + 1860/1,1 7 + 2200/1,1 8 + 2500/1,1 9 + 3600/1,1 10 = 352,1738 rúp.

Tính NPV trong Excel (ví dụ dạng bảng)

Chúng ta có thể giải quyết ví dụ tương tự này bằng cách sắp xếp dữ liệu liên quan dưới dạng bảng Excel.

Nó sẽ trông giống như thế này:

Hình 2. Bố cục dữ liệu mẫu trên trang tính Excel

Để có được kết quả như mong muốn, chúng ta phải điền vào các ô tương ứng các công thức cần thiết.

Kết quả là trong ô F15 chúng ta nhận được giá trị NPV mong muốn bằng 352,1738.

Phải mất 3-4 phút để tạo một bảng như vậy. Excel cho phép bạn tìm giá trị NPV mong muốn nhanh hơn.

Tính NPV trong Excel (hàm NPV)

Đặt công thức vào ô B17 (hoặc bất kỳ ô nào khác):

NPV(F2/100;C5:C14)-D14

Chúng tôi sẽ ngay lập tức nhận được giá trị NPV chính xác bằng rúp (352,1738 rúp).

Hình 3. Tính NPV bằng công thức NPV trên Excel

Công thức của chúng tôi đề cập đến các ô F2 (ở đó chúng tôi có lãi suất 10%; để sử dụng nó trong hàm NPV, chúng tôi cần chia nó cho 100), phạm vi giá trị C5:C14, nơi chứa dữ liệu về dòng tiền vào và ô D14, chứa kích thước của ô ban đầu

Microsoft Excel không chỉ là một bảng tính lớn mà còn là một máy tính cực kỳ hiện đại với nhiều chức năng và khả năng. Trong bài học này chúng ta sẽ học cách sử dụng nó cho mục đích đã định.

Tất cả các phép tính trong Excel đều được gọi là công thức và chúng đều bắt đầu bằng dấu bằng (=).

Ví dụ: tôi muốn tính tổng của 3+2. Nếu tôi nhấp vào bất kỳ ô nào và nhập 3+2 vào bên trong, sau đó nhấn nút Enter trên bàn phím thì sẽ không có gì được tính - 3+2 sẽ được ghi vào ô. Nhưng nếu tôi gõ =3+2 và nhấn Enter thì mọi thứ sẽ được tính toán và kết quả sẽ được hiển thị.

Hãy nhớ hai quy tắc:

Mọi phép tính trong Excel đều bắt đầu bằng dấu =

Sau khi nhập công thức xong bạn cần nhấn nút Enter trên bàn phím

Và bây giờ về các dấu hiệu mà chúng ta sẽ đếm. Chúng còn được gọi là toán tử số học:

Phép cộng

Phép trừ

* phép nhân

/ phân công. Ngoài ra còn có một cây gậy nghiêng về hướng khác. Vì vậy, nó không phù hợp với chúng tôi.

^ lũy thừa. Ví dụ: 3^2 được đọc là ba bình phương ( lũy thừa bậc hai).

% phần trăm. Nếu chúng ta đặt dấu này sau một số thì số đó chia hết cho 100. Ví dụ: 5% sẽ là 0,05.
Sử dụng dấu hiệu này bạn có thể tính lãi. Nếu chúng ta cần tính 5% trong số 20 thì công thức sẽ như sau: =20*5%

Tất cả các ký tự này đều nằm trên bàn phím ở trên cùng (phía trên các chữ cái, cùng với số) hoặc ở bên phải (trong một khối nút riêng biệt).

Để in các ký tự ở đầu bàn phím, bạn cần nhấn và giữ nút có nhãn Shift, cùng với đó, nhấn nút có ký tự mong muốn.

Bây giờ chúng ta thử đếm nhé. Giả sử chúng ta cần cộng số 122596 với số 14830. Để thực hiện việc này, hãy nhấp chuột trái vào bất kỳ ô nào. Như tôi đã nói, tất cả các phép tính trong Excel đều bắt đầu bằng dấu “=”. Điều này có nghĩa là trong ô bạn cần in =122596+14830

Và để có được câu trả lời, bạn cần nhấn nút Enter trên bàn phím. Sau đó, ô sẽ không còn chứa công thức mà là kết quả.

Bây giờ hãy chú ý đến trường trên cùng này trong Excel:

Đây là Thanh công thức. Chúng tôi cần nó để kiểm tra và thay đổi công thức của chúng tôi.

Ví dụ: nhấp vào ô mà chúng tôi vừa tính số tiền.

Và nhìn vào thanh công thức. Nó sẽ hiển thị chính xác cách chúng tôi có được giá trị này.

Nghĩa là, trong thanh công thức, chúng ta không thấy chính con số đó mà là công thức tạo ra con số này.

Hãy thử gõ số 5 vào một số ô khác và nhấn Enter trên bàn phím. Sau đó bấm vào ô đó và nhìn vào thanh công thức.

Vì chúng tôi chỉ in số này và không tính toán bằng công thức nên nó sẽ chỉ nằm trong thanh công thức.

Cách đếm chính xác

Tuy nhiên, theo quy luật, phương pháp “đếm” này không được sử dụng thường xuyên. Có một tùy chọn nâng cao hơn.

Giả sử chúng ta có một bảng như thế này:

Tôi sẽ bắt đầu với vị trí đầu tiên "Pho mát". Tôi bấm vào ô D2 và gõ bằng.

Sau đó, tôi nhấp vào ô B2 vì tôi cần nhân giá trị của nó với C2.

Tôi gõ dấu nhân *.

Bây giờ tôi bấm vào ô C2.

Và cuối cùng, tôi nhấn nút Enter trên bàn phím. Tất cả! Ô D2 cho kết quả mong muốn.

Bằng cách nhấp vào ô này (D2) và nhìn vào thanh công thức, bạn có thể thấy giá trị này được lấy như thế nào.

Tôi sẽ giải thích bằng cách sử dụng bảng tương tự làm ví dụ. Bây giờ số 213 được nhập vào ô B2, tôi xóa nó, gõ số khác và nhấn Enter.

Hãy nhìn vào ô có tổng D2.

Kết quả đã thay đổi. Điều này xảy ra vì giá trị trong B2 đã thay đổi. Rốt cuộc, công thức của chúng ta như sau: =B2*C2

Điều này có nghĩa là Microsoft Excel nhân nội dung của ô B2 với nội dung của ô C2, bất kể giá trị đó là gì. Hãy rút ra kết luận của riêng bạn :)

Cố gắng tạo bảng tương tự và tính tổng ở các ô còn lại (D3, D4, D5).

Bài thực hành số 20

Thực hiện các phép tính trongExcel

Mục tiêu: học cách thực hiện các phép tính trong Excel

Thông tin từ lý thuyết

Tất cả các phép tính trong MS Excel đều được thực hiện bằng công thức. Nhập công thức luôn bắt đầu bằng dấu bằng “=”, sau đó đặt các phần tử được tính toán, giữa đó có dấu hiệu của các thao tác đang được thực hiện:

+ phép cộng/ phân công* phép nhân

− phép trừ^ lũy thừa

Trong các công thức, không phải số thường được sử dụng làm toán hạng mà là địa chỉ của các ô chứa những số này. Trong trường hợp này, khi bạn thay đổi số trong ô được sử dụng trong công thức, kết quả sẽ được tính toán lại tự động

Ví dụ về viết công thức:

=(A5+B5)/2

=C6^2* (D6- D7)

Các công thức sử dụng tài liệu tham khảo tương đối, tuyệt đối và hỗn hợp tới các địa chỉ ô. Tham chiếu tương đối thay đổi khi bạn sao chép công thức. Khi sao chép một công thức, ký hiệu $ cố định: số hàng (A$2 là tham chiếu hỗn hợp), số cột ($F25 là tham chiếu hỗn hợp) hoặc cả hai ($A$2 là tham chiếu tuyệt đối).

Các công thức rất linh hoạt và kết quả sẽ tự động thay đổi khi số trong các ô đã chọn thay đổi.

Nếu có một số thao tác trong một công thức thì cần phải tính đến thứ tự thực hiện các phép toán.

Tự động điền các ô bằng công thức:

nhập công thức vào ô đầu tiên và nhấn Enter;

chọn ô có công thức;

Đặt con trỏ chuột vào góc dưới bên phải của ô và kéo theo hướng mong muốn (bạn có thể tự động điền các ô có công thức xuống, lên, phải, trái).

Sử dụng chức năng:

đặt con trỏ vào ô nơi phép tính sẽ được thực hiện;

mở rộng danh sách lệnh của nút Sum (Hình 20.1) và chọn chức năng mong muốn. Khi lựa chọn Cac chưc năng khac gọi điện Trình hướng dẫn chức năng.

Hình 20.1 – Chọn chức năng

Nhiệm vụ cần hoàn thành

Nhiệm vụ 1 Tính tỷ giá đô la

Trong thư mục có tên nhóm của bạn, hãy tạo một tài liệu MS Excel, đặt tên theo số bài tập thực tế.

Bảng tên 1 theo mã số nhiệm vụ;

Tạo bảng tính tỷ giá đô la (Hình 20.2), cột ngàyđiền bằng cách sử dụng tự động hoàn thành, cột Tỷ lệ mua và và Tỷ giá bánđịnh dạng dưới dạng số có hai chữ số thập phân.

Hình 20.2 – Bảng mẫu (Bảng 1)

Thực hiện tính toán thu nhập trong ô D4. Công thức tính toán: Thu nhập = Tỷ giá bán – Tỷ giá mua

Tự động điền các ô D5:D23 bằng công thức.

Trong cột "Thu nhập", nhập Tiền mặt (r)định dạng số;

Định dạng bảng.

Lưu cuốn sách.

Nhiệm vụ 2 Tính tổng doanh thu

Tờ 2.

Tạo bảng tính tổng doanh thu bằng mẫu và thực hiện tính toán (Hình 20.3).

Trong phạm vi B3:E9, chỉ định định dạng tiền tệ đến hai chữ số thập phân.

Công thức tính toán:

Tổng số ngày = Phường 1 + Phường 2 + Phường 3;

Tổng số hàng tuần = Tổng giá trị cho mỗi cột

Định dạng bảng. Lưu cuốn sách.

Hình 20.3 – Bảng mẫu (Bảng 2)

Nhiệm vụ 3 Tính trợ cấp

Trong cùng một tài liệu, đi tới Tờ 3.Đặt tên nó theo mã số công việc.

Tạo bảng tính phí bảo hiểm theo mẫu (Hình 20.4). Cột Phần trăm phụ phí thiết lập định dạng Tỷ lệ phần trăm, hãy tự đặt định dạng dữ liệu chính xác cho các cột Số tiền lương Và Số tiền bổ sung.

Hình 20.4 - Bảng mẫu (Bảng 3)

Thực hiện các phép tính.

Công thức tính toán

Số tiền thưởng = Tỷ lệ phần trăm tiền thưởng * Số tiền lương

Sau cột Tổng phụ phí thêm một cột khác Tổng cộng. In tổng lương cộng thưởng.

Nhập: vào ô E10 Số tiền trợ cấp tối đa tới ô E11 − Số tiền bảo hiểm tối thiểu.Đến ô E12 - Số tiền bảo hiểm trung bình.

Trong các ô F10:F12, nhập công thức tính toán thích hợp.

Lưu cuốn sách.

Bài 4 Sử dụng địa chỉ tuyệt đối và tương đối trong công thức

Trong cùng một tài liệu, đi tới Tờ 4.Đặt tên nó theo mã số công việc.

Nhập bảng theo ví dụ (Hình 20.5), đặt font chữ Bodoni MT, kích thước 12, cài đặt định dạng khác - theo mẫu. Đối với các ô C4:C9, đặt định dạng thành Tiền tệ, ký hiệu “r.”, đối với các ô D4:D9, đặt định dạng thành Tiền tệ, ký hiệu “$”.

Hình 20.5 - Bảng mẫu (Bảng 4)

Tính giá trị của các ô có dấu “?”

Lưu cuốn sách.

Câu hỏi kiểm soát

Làm thế nào để bạn nhập công thức trong Excel?

Những thao tác nào có thể được sử dụng trong công thức, chúng được chỉ định như thế nào?

Làm cách nào để tự động điền các ô bằng công thức?

Làm cách nào để gọi Trình hướng dẫn chức năng?

Bạn đã sử dụng những tính năng nào trong phòng thí nghiệm này?

Hãy cho chúng tôi biết cách bạn tính toán các giá trị Tối đa, tối thiểu Và Số tiền bảo hiểm trung bình.

Địa chỉ ô được hình thành như thế nào?

Bạn đã sử dụng địa chỉ tuyệt đối ở đâu trong công việc và bạn sử dụng địa chỉ tương đối ở đâu?

Bạn đã sử dụng định dạng dữ liệu nào trong các bảng trong tác phẩm này?