Các hàm Excel với các ví dụ cần thiết cho công việc. Các hàm trong Excel. Mô tả chung, mục đích. Hàm văn bản Microsoft Excel

Trong Excel, hàm là thành phần chính của toàn bộ chương trình. Tuy nhiên, đại đa số người dùng thực tế không biết gì về chúng. Lúc đầu, khi bạn mới học, bạn nên sử dụng một trình hướng dẫn đặc biệt, với sự trợ giúp của nó, việc làm việc với các chức năng sẽ dễ dàng hơn nhiều lần. Trong bài viết này chúng ta sẽ xem xét chi tiết quá trình tạo công thức và các hàm cơ bản nhất được sử dụng trong nhiều ngành nghề.

Để làm điều này, bạn cần thực hiện các bước sau.

1. Chọn bất kỳ ô nào. Nhấp vào biểu tượng để mở cửa sổ “Chèn chức năng”. Nhấp vào danh sách thả xuống và chọn danh mục mong muốn.

1. Sau đó chọn chức năng mong muốn. Hãy lấy "COUNTIF" làm ví dụ. Ngay sau đó bạn sẽ thấy thông tin ngắn gọn về mục đã chọn. Để được trợ giúp chi tiết, bạn sẽ cần nhấp vào chức năng được chỉ định. Để tiếp tục, hãy nhấp vào “OK”.

1. Sau đó, bạn sẽ được yêu cầu cung cấp "Đối số chức năng". Các trường sẽ luôn thay đổi tùy thuộc vào công thức đã chọn. Trong trường hợp này, bạn cần chỉ định phạm vi ô và tiêu chí đếm.

1. Đi đến trường đầu tiên. Chọn số lượng ô cần thiết.

1. Trong trường thứ hai, làm ví dụ, bạn có thể chỉ định hai dấu ngoặc kép trống. Mục này có nghĩa là văn bản trống. Nghĩa là, cái lồng không được chứa bất cứ thứ gì.

Xin lưu ý: tất cả các đối số phải được điền đầy đủ. Công thức sẽ không hoạt động nếu một trong các trường trống.

Sau đó, nhấp vào “OK”.

1. Nhờ điều này, bạn sẽ thấy một con số. Giá trị này sẽ tương ứng với số lượng ô đáp ứng tiêu chí của bạn. Trong trường hợp này, chúng tôi đã chọn 14 ô trống.

1. Nếu bạn thực hiện bất kỳ thay đổi nào, kết quả của hàm sẽ thay đổi ngay lập tức.

Công thức này có thể được chỉnh sửa bằng tay. Bạn không cần phải sử dụng cửa sổ Insert Function để thực hiện việc này. Chỉ cần nhấp vào ô mong muốn và đi đến thanh công thức.

Nếu dòng này có vẻ nhỏ và bất tiện với bạn, bạn cần nhấn phím nóng Ctrl + Shift + U. Nhờ đó, chiều cao của nó sẽ tăng lên gấp nhiều lần.

Để quay lại chế độ trước, bạn cần lặp lại tổ hợp phím Ctrl + Shift + U.

Kích thước của thanh công thức sẽ không thay đổi ngay cả sau khi đóng Excel. Cài đặt sẽ hoạt động mỗi khi bạn khởi động nó và hơn thế nữa. Nó chỉ có thể được thay đổi bằng tay.

Điều đáng chú ý là bạn không cần sử dụng trình hướng dẫn đặc biệt để tạo công thức. Bạn chỉ cần sao chép mã này hoặc tự viết nó.

Bạn có thể, nếu muốn, chỉ định bất kỳ phạm vi và tiêu chí lựa chọn nào bạn cần.

Hàm toán học và lượng giác

Phần công thức này rất lớn. Nó có thể được chia thành nhiều nhóm. Điều này sẽ giúp bạn điều hướng dễ dàng hơn.

Hầu hết những người mới bắt đầu nghĩ rằng chỉ có một cách duy nhất để làm tròn số. Nhưng điều này không đúng chút nào. Có khoảng 15 phương pháp cho việc này. Chúng ta hãy nhìn vào từng người trong số họ.

Đầu tiên, hãy tạo bảng trực quan này. Ở đây chúng ta có thể làm tròn cùng một số thành một cột bằng cách sử dụng một số hàm. Chế độ xem dạng bảng sẽ đơn giản hóa quá trình so sánh.

1. Đi tới ô đầu tiên trong bảng này. Mở cửa sổ “Chèn chức năng”. Chọn danh mục "Toán học". Tìm mục “ROUND” ở đó và nhấp vào “OK”.

1. Nhập địa chỉ của ô chứa số của bạn. Sau đó điền vào trường “Số chữ số”. Nó xác định số vị trí thập phân sau dấu thập phân. Để lưu, hãy nhấp vào “OK”.

1. Nhờ điều này bạn sẽ thấy kết quả sau đây.

1. Sau đó di chuyển con trỏ đến góc dưới bên phải của ô đó. Sự xuất hiện của con trỏ chuột của bạn sẽ thay đổi. Nhấp chuột trái và không thả ngón tay ra, vuốt đến cuối bảng.

1. Kết quả là bạn sẽ thấy như sau.

1. Lặp lại các bước trên cho các chức năng còn lại.

Các chức năng này có mục đích sau:

• ROUND – làm tròn số được chỉ định đến một số chữ số thập phân nhất định. Nguyên lý hoạt động giống hệt như người ta dạy bạn đi vòng ở trường;
• ROUNDED – làm tròn đến giá trị nhỏ hơn (modulo) gần nhất. Trong trường hợp này, tất cả các ký tự khác sau độ chính xác đã chỉ định sẽ bị loại bỏ. Trong trường hợp của chúng tôi, từ 1,598 nó trở thành 1,59. Mặc dù theo quy luật toán học thì nó phải là 1,6;
• ROUNDUP – làm tròn đến giá trị lớn hơn (modulo) gần nhất. Nguyên lý hoạt động hoàn toàn giống với “ĐÁY TRÒN”;
• ROUND – Làm tròn một số lên bội số gần nhất của số được chỉ định trong trường chính xác. Trong bảng của chúng tôi, tất cả các kết quả đều là bội số của 2. Chính con số này đã được chỉ ra trong tham số thứ hai;
• ROUND UP – nguyên tắc hoạt động hoàn toàn giống với chức năng “ROUND”. Chỉ trong trường hợp này, việc làm tròn xảy ra ở mức cao hơn gần nhất chứ không phải ở bất kỳ bội số nào;
• OKRVNIZ - điều tương tự, chỉ ở mức độ thấp hơn;
• DROP – hàm này loại bỏ toàn bộ phần phân số đến số chữ số đã chỉ định;
• INTEGER – làm tròn đến số nhỏ nhất gần nhất. Trong trường hợp này, chỉ còn lại toàn bộ phần;
• EVEN – hàm trả về số nguyên chẵn gần nhất;
• ODD - hàm trả về số nguyên lẻ gần nhất.

Như bạn có thể thấy, nhiều công thức tương tự nhau.

Các hàm kết thúc bằng "MAT" có tham số bổ sung thứ ba. Nếu nó không được chỉ định thì nguyên tắc hoạt động hoàn toàn giống như đối với các hàm không có tiền tố “MAT”.

Để minh họa thư viện hàm này, hãy tạo một bảng hơi khác một chút. Chúng tôi sẽ điền chính xác theo nguyên tắc tương tự.

Các hàm này cho phép (cú pháp được đưa ra riêng cho bảng của chúng tôi):

• SUM – tính tổng của tất cả các ô có trong phạm vi đã chỉ định;

• SUMIF – tính tổng của tất cả các ô nằm trong phạm vi đã chỉ định và đáp ứng một điều kiện nhất định;

• SUMILIMS – tính tổng của tất cả các ô nằm trong phạm vi đã chỉ định và đáp ứng một số điều kiện nhất định;

• TỔNG HỢP – tính tích của các ô từ mỗi hàng của phạm vi được chỉ định.

• SUMMKV – tính tổng bình phương của các đối số đã chỉ định. Bạn có thể sử dụng cả số nguyên tố và mảng lớn.

• SUMMDISC – tính tổng chênh lệch giữa các bình phương của các mảng đã chỉ định. Công thức sau đây được sử dụng.

• SUMMSUMMQ – tính tổng các tổng bình phương của các mảng đã chỉ định. Công thức sau đây được sử dụng.

• SUMMKVRAZN – tính tổng bình phương của các mảng dữ liệu được chỉ định. Công thức sau đây được sử dụng để tính toán.

Xin lưu ý: trong tất cả các trường hợp trên, số phần tử mảng phải giống nhau.

Kết quả sẽ như sau.

Nhờ những công thức này, ngay cả một ấm trà cũng có thể đạt được kết quả mong muốn hoặc lập báo cáo tài chính. Bạn không cần phải là nhà kinh tế hay lập trình viên mới có thể sử dụng những công cụ đó. Chỉ cần biết tiếng Nga là đủ. Tiếng Anh là không cần thiết. Tất cả các chức năng đều tự nói lên điều đó.

Chức năng này phức tạp hơn - đây là nhiệm vụ của chương trình viện. Vì vậy, chúng tôi sẽ xem xét nó một cách riêng biệt. Nó trả về tổng của một chuỗi lũy thừa phức tạp, thay vì một mảng thông thường. Trong trường hợp này, công thức sử dụng một số lượng lớn đối số. Nhìn thì đáng sợ quá nhưng lại dễ hiểu hơn phương trình Laplace.

Việc tính toán một chuỗi số tiền theo cách thủ công thậm chí còn khó khăn hơn. Đây là lý do tại sao trình soạn thảo Excel lại được yêu cầu vì sử dụng nó dễ dàng hơn nhiều so với việc suy nghĩ bằng chính bộ não của bạn. Đồng thời, giải pháp sẽ xảy ra ngay lập tức, ngay cả khi bạn thay đổi điều gì đó trong điều kiện.

Ví dụ: lấy 1 phần tử của mảng thứ 1 làm đối số X. Độ N sẽ bắt đầu từ 1 với bước tiếp theo là 1. Hãy lấy tất cả các ô của mảng thứ hai làm hệ số.

Kết quả sẽ rất lớn vì chúng ta đã chọn nhiều giá trị cho đối số cuối cùng. Nếu có ít thì con số sẽ giảm nhưng vẫn lớn nhất trong bảng.

Trong trường hợp này, mọi thứ đơn giản hơn nhiều. Các công thức lượng giác trường học tiêu chuẩn được sử dụng ở đây. Và chúng sẽ được gọi gần như giống hệt nhau. Bao gồm các:

• COS – cosin của góc;
• COSH – cosin hyperbol của góc;
• COT - cotang góc;
• COTH – cotang hyperbol của góc;
• CSC – cosec góc;
• CSCH – cosec hyperbol của góc;
• SEC – cát tuyến góc;
• SECH – sec hyperbol của một góc;
• SIN – sin của góc;
• SINH – sin hyperbol của góc;
• TAN – tiếp tuyến của góc;
• TANH là tang hyperbol của góc.

Xin lưu ý rằng khi tính toán, bạn cần chỉ ra góc bằng radian chứ không chỉ là số. Để chuyển đổi một số thành radian, hãy sử dụng hàm cùng tên. Chúng ta sẽ nói về nó chi tiết hơn một chút.

Ví dụ, công thức cosine trông như thế này.

Sử dụng các chức năng được mô tả ở trên, bạn có thể xây dựng bảng sau.

Bạn có thể kiểm tra tính nhất quán của bảng và thấy rằng tất cả các giá trị đều được nhập chính xác. Và điểm trừ là ở chỗ đáng ra phải có, nhưng vẫn có một tỷ lệ lỗi nhỏ như vậy.

Trường hợp có số lượng rất lớn hoặc sai số chia cho 0 thì giá trị bảng bị thiếu. Chính xác hơn, giá trị như vậy không tồn tại. Nhưng người soạn thảo vẫn cố gắng biến đổi công thức theo thiết kế.

Đây là bảng trông giống như trong sách giáo khoa toán. Excel không hiển thị gốc - nó ngay lập tức tính giá trị số cuối cùng.

Ngoài ra còn có hàm nghịch đảo. Cụ thể là:

• ACOS – cung cosin của số;
• ACOSH – cung cosin hyperbol của một số;
• ACOT – cotang cung của một số (hoạt động từ năm 2013);
• ACOTH – arccotang hyperbol của một số (hoạt động từ năm 2013);
• ASIN – arcsine của số;
• ASINH – arcsin hyperbol của một số;
• ATAN – cung tiếp tuyến của số;
• ATANH là arctang hyperbol của một số.

Để hiển thị kết quả chính xác, bạn cần sử dụng chức năng “DEGREES”. Kết quả sẽ như sau. Bạn cũng có thể kiểm tra tính nhất quán của nó với các giá trị trong bảng. Việc kiểm tra sẽ cho thấy rằng việc tự động điền dữ liệu đã được hoàn thành chính xác.

Trong một số trường hợp, chúng tôi thấy lỗi "#NUMBER". Đây là hệ quả của việc những giá trị này không tồn tại. Ví dụ: công thức ACOSH có thể sử dụng một số lớn hơn hoặc bằng 1. Nhưng trong bảng của chúng tôi, các giá trị được phân tích cú pháp bắt đầu từ -1 - điều này không được chấp nhận trong hàm này. Ngược lại ở đâu đó - phạm vi giá trị có thể từ -1 đến 1 và không nhiều hơn. Nghĩa là, tất cả các kết quả thực nghiệm của chúng tôi đều được coi là có tính đến các quy tắc toán học.

Theo quy luật, những công thức như vậy thường được học sinh sử dụng để soạn bài thuyết trình hoặc khi thực hiện các công việc khác nhau trong phòng thí nghiệm khi các em được yêu cầu lập bảng một hàm số. Điều này có nghĩa là xây dựng một biểu đồ từ dữ liệu bảng dựa trên một số biểu thức toán học.

Ví dụ: hãy thử vẽ biểu đồ tăng trưởng theo cấp số nhân dựa trên các giá trị đã cho.

1. Chúng tôi xây dựng một bảng và chỉ ra các dữ liệu cần thiết. Bạn có thể điền nó với bất kỳ số nào. Điều này sẽ không có nghĩa gì quan trọng.

1. Chèn công thức sau vào ô đầu tiên của cột thứ hai.

Sau đó chúng ta nhân đôi vào các ô còn lại (kéo góc kết quả đầu tiên).

1. Sau đó đánh dấu tất cả các giá trị của bạn trong bảng (cho phép đánh dấu tiêu đề). Chuyển đến tab “Chèn” và nhấp vào biểu tượng “Biểu đồ”. Chọn bất kỳ tùy chọn bố trí được đề xuất.

1. Kết quả sẽ không hoàn toàn chính xác vì chúng ta không có những con số này. Trong trường hợp này, các giá trị được phân phối tự động.

1. Nhấp chuột phải vào sơ đồ. Trong menu xuất hiện, nhấp vào mục “Chọn dữ liệu”.

1. Nhấp vào nút “Thay đổi”.

1. Trong cửa sổ xuất hiện, bạn sẽ cần chỉ định phạm vi ô chúng ta cần. Để tiếp tục, hãy nhấp vào “OK”.

1. Sau đó, điều tương tự.

1. Bây giờ việc phân phối đang diễn ra như bình thường. Tất cả các giá trị đều ở đúng vị trí của chúng. Trong những thời điểm như vậy, việc người dùng kiểm soát những gì đang diễn ra là rất quan trọng vì Excel không hoàn hảo.

Như bạn có thể thấy, sự tăng trưởng của hàm hoàn toàn không tuyến tính - nó tăng rất nhanh. Một sự gia tăng nhỏ chỉ được quan sát thấy khi bắt đầu. Xây dựng một biểu đồ như vậy khá đơn giản.

Để minh họa các công thức này, bạn cần thêm một bảng khác.

Bằng cách sử dụng hàm "ROMAN", chúng ta có thể viết những số này dưới dạng chữ số La Mã. Sau đó, bằng cách sử dụng “ARAB”, chúng tôi đã có thể đưa giao diện của mình trở lại bình thường. Hơn nữa, sự thay thế diễn ra từ sự chuyển đổi trước đó. Đối với Excel, ô này sẽ chứa gì không quan trọng - công thức, văn bản tĩnh hoặc biến. Đây là lý do tại sao tiềm năng thực tế của các chức năng này là không thể tin được.

Trong lượng giác, các hàm sau được sử dụng để chuyển đổi (ví dụ được trình bày ở trên):

• ĐỘ – chuyển đổi radian sang độ;
• RADIANS – chuyển đổi độ sang radian.

Ngoài ra, còn có một công cụ thú vị và hữu ích khác để chuyển đổi các số khác nhau từ một số hệ thống số sang số thập phân. Để thể hiện chức năng này, bạn cần thêm một bảng khác.

Sau đó, bạn sẽ cần phải nhập các công thức sau.

Đầu tiên, số ô được chỉ định, sau đó là hệ thống số ban đầu. Kết quả sẽ như thế này.

Hàm số học

Những công thức này bao gồm:

• SẢN PHẨM – phép nhân hai số;
• CÒN LẠI – loại bỏ phần nguyên khỏi phép chia;
• POWER – nâng con số được chỉ định lên mức công suất cần thiết;
• QUANTIATE – lấy phần nguyên từ phép chia một số;
• EXP – nâng số mũ lên lũy thừa quy định;
• ROOT – lấy căn dương của một số được chỉ định.

Những chức năng này được dạy ở trường trung học. Bạn không cần phải tìm kiếm bất kỳ ghi chú nào cho việc này – tất cả đều phải ở trong đầu bạn. Ngay cả khi bạn có lỗ hổng kiến ​​thức, thông tin này vẫn nên được giữ lại.

Để trừ hai giá trị một cách đơn giản, chỉ cần sử dụng ký hiệu dấu trừ thông thường (“-”). Không có chức năng đặc biệt cho việc này.

Điều khoản khác

• Ngoài ra, còn rất nhiều hàm toán học khác khó có thể kết hợp thành một nhóm điều kiện.
• ABS - mô-đun có giá trị được chỉ định;
• TỔNG HỢP – biểu thức danh sách tổng hợp (để biết thêm chi tiết, xem trợ giúp chính thức);
• SỰ THẬT – tính giai thừa của số đã chỉ định;
• GCD – tìm ước chung lớn nhất;
• LCM – tìm bội số chung nhỏ nhất;
• MOPRED – cho phép bạn tìm định thức của ma trận của một mảng dữ liệu;
• MUMULT – tích ma trận các số của hai mảng;
• PI – nhập số “pi” vào công thức;
• RAND – chọn ngẫu nhiên một số từ 0 đến 1;
• RANDBETWEEN – giá trị ngẫu nhiên giữa các số được chỉ định;
• SIGN – cho phép bạn xác định dấu của giá trị được chỉ định;
• SUBTOTAL – hợp nhất các giá trị và tổng hợp trong danh sách hoặc cơ sở dữ liệu.

Chức năng thông tin

Những công thức này chủ yếu là một công cụ để phân tích dữ liệu. Thật dễ dàng để viết chúng ra. Mục đích của họ là như sau:

• EMPTY – kiểm tra một ô xem có giá trị nào đó không;
• UND – kiểm tra ô để tìm lỗi #N/A;
• ENUMBER – kiểm tra giá trị xem có tuân thủ định dạng số không;
• LỖI – kiểm tra xem có lỗi nào không;
• ETEXT – hàm trả về true nếu đối số chứa giá trị văn bản;
• ENETEXT – một kiểm tra tương tự, chỉ ngược lại;
• EOS – hàm sẽ trả về kết quả đúng nếu ô chứa bất kỳ lỗi nào khác ngoài #N/A;
• để kiểm tra xem một giá trị là chẵn hay lẻ, các công thức EVEN và UNEVEN được sử dụng;
• CÔNG THỨC – kiểm tra sự hiện diện của công thức trong ô được chỉ định.

Nhưng có một chức năng phức tạp hơn cần được nói riêng.

Sử dụng công cụ này, bạn có thể tìm hiểu tất cả thông tin cần thiết về ô được chỉ định. Trong trường hợp này, bạn phải sử dụng tham số bắt buộc – “Loại thông tin”, bạn có thể tìm hiểu:

• màu sắc;
• Địa chỉ;
• cột;
• và nhiều hơn nữa.

Thông tin chi tiết có thể được tìm thấy trên trang web của Microsoft.

Những công trình này được sử dụng để xây dựng các công thức lớn và phức tạp.

• Và – đúng, nếu tất cả các điều kiện đều đúng;
• HOẶC – đúng nếu ít nhất một điều kiện đúng;

Các chức năng sau đây được sử dụng để phân tích các điều kiện khác nhau:

• IF – để kiểm tra một sự kiện;
• ĐIỀU KIỆN – điều tương tự, chỉ có một số lượng lớn các điều kiện.

Phần cuối cùng ở trên chỉ xuất hiện trong trình chỉnh sửa Excel 2016. Trước đây, tùy chọn “IFS” đã được sử dụng.

Bảng sau đây có thể được sử dụng làm ví dụ.

Trong trường hợp này, hai hàm được sử dụng cùng một lúc: “IF” và “OR”.

Để kiểm tra hoạt động của công thức, bạn có thể sử dụng cấu trúc với “IFERROR”. Nếu mọi thứ được thực hiện chính xác, bạn sẽ thấy kết quả tính toán. Nếu không, bạn sẽ thấy giá trị đã nhập ở dạng văn bản.

Chức năng liên kết và tìm kiếm

• CHỌN – chọn một giá trị từ danh sách (mảng) dữ liệu;
• COLUMN – hiển thị số cột của ô được chỉ định;
• GET.PIVORTABLE.DATA – hiển thị dữ liệu được lưu trữ trong báo cáo PivotTable;
• SPR – tìm kiếm trong mảng dữ liệu;
• CHỈ SỐ – chọn giá trị theo chỉ mục bổ sung trong phạm vi ô được chỉ định;
• GIÁN TIẾP – nhận một liên kết ban đầu được cung cấp một giá trị văn bản;
• VIEW – tìm kiếm các giá trị trong một mảng;
• MATCH – tìm kiếm vị trí của văn bản hoặc giá trị được chỉ định trong một phạm vi ô cụ thể;
• OFFSET – độ lệch của liên kết so với liên kết đã chỉ định;
• ROW – trả về số dòng trong liên kết đã chỉ định;
• TRANSPOSE – chuyển vị của một mảng dữ liệu;
• VLOOKUP - tìm kiếm một giá trị trong một mảng và lấy dữ liệu từ một ô trong hàng tìm thấy trong danh sách dữ liệu cụ thể.

Các hàm cơ sở dữ liệu

Có khá nhiều công thức trong phần này. Chúng ta hãy xem xét một số điều cơ bản nhất và phổ biến nhất. Bao gồm các:

• BIZRETCH – tìm kiếm bản ghi trong cơ sở dữ liệu phù hợp với điều kiện lựa chọn đã chỉ định;
• BDSUMM – tổng của tất cả các số nằm trong trường được chỉ định và đáp ứng các điều kiện nhất định;
• DMIN – tìm kiếm giá trị tối thiểu trong toàn bộ mẫu dữ liệu từ cơ sở dữ liệu;
• DMAX – tìm kiếm giá trị lớn nhất trong toàn bộ mẫu dữ liệu từ cơ sở dữ liệu;

Phần công thức này rất thú vị và hữu ích. Sử dụng chúng, bạn có thể nhanh chóng phân tích một số thông tin hoặc trích xuất dữ liệu nhất định từ một ngày cụ thể.

• DAY – ngày trong ngày đã chỉ định được xác định;
• THÁNG – xác định tháng của ngày được chỉ định;
• NĂM – năm trong ngày được chỉ định được xác định;
• HÔM NAY – hiển thị ngày hiện tại;
• SỐ TUẦN – hiển thị số tuần dựa trên ngày đã chỉ định;
• TDATE – hiển thị ngày và giờ hiện tại;
• GIỜ – xác định giờ nào được chỉ định vào một ngày nhất định;
• PHÚT – xác định số phút được chỉ định trong một ngày nhất định;
• GIÂY – xác định số giây được chỉ định trong một ngày nhất định;
• NGÀY TUẦN – số sê-ri của ngày trong tuần được tính (việc tính bắt đầu từ Chủ Nhật, không phải Thứ Hai).

Ngoài ra còn có các công thức phức tạp hơn. Bao gồm các:

• RAZNDAT – tính số năm, tháng và ngày giữa các ngày đã chỉ định;
• NGÀY – số ngày giữa các ngày đã chỉ định được tính toán (hàm xuất hiện vào năm 2013);
• NGÀY MẠNG – tính số ngày làm việc giữa các ngày đã chỉ định;
• NGÀY TUẦN – chuyển đổi ngày thông thường ở định dạng số thành số sê-ri trong tuần;
• NGÀY LÀM VIỆC – hiển thị một ngày ở sau hoặc trước số ngày được chỉ định.

Bạn có thể đọc thêm về công thức mới nhất trên trang web chính thức của Microsoft.

Cú pháp của hàm này như sau.

Và các ví dụ khá đơn giản.

Có rất nhiều chức năng khác nhau để làm việc với thông tin văn bản trong trình soạn thảo Excel. Phổ biến nhất bao gồm:

• KẾT NỐI – trong trường hợp này, nhiều phần khác nhau được nối thành một văn bản hoàn chỉnh;
• COICH – kiểm tra hai giá trị để tuân thủ hoàn toàn với nhau;
• TÌM, TÌM – tìm kiếm một đoạn trong văn bản khác (chức năng tìm kiếm sử dụng cách viết hoa chữ cái);
• TÌM KIẾM, POISKB – tìm kiếm tương tự, chỉ phân biệt chữ hoa chữ thường;
• LEVSIMB, LEVB – sao chép các ký tự đầu tiên của một dòng (trong một trường hợp, phép tính xảy ra theo từng ký tự và trong trường hợp khác - theo byte);
• PRAVSIMV, PRAVB - cùng nghĩa, chỉ tính từ phía bên phải;
• LENGTH, LENGTH – số ký tự trong một dòng;
• PSTR, PSTRB – sao chép một đoạn có số lượng ký tự được yêu cầu từ vị trí được chỉ định cho báo cáo;
• REPLACE, REPLACE – thay thế các ký tự nhất định trong chuỗi văn bản;
• SUBSTITUTE – thay thế văn bản này bằng văn bản khác;
• TEXT – chuyển đổi một số thành định dạng văn bản;
• KẾT HỢP – kết hợp các đoạn văn bản khác nhau thành một tổng thể (trong trường hợp này, một loại con trỏ nào đó được chèn vào).

Công thức được đề cập cuối cùng đã xuất hiện trong phiên bản mới nhất của Microsoft Excel 2016. Cú pháp của nó như sau.

Phần công thức này phức tạp hơn nhiều so với những phần được mô tả trước đó. Để không phải bận tâm đến việc lựa chọn dữ liệu, chúng tôi sẽ sử dụng trợ giúp chính thức của Microsoft làm ví dụ.

PLT

Video hướng dẫn

Một số người dùng cảm thấy khó hiểu hướng dẫn ở dạng văn bản. Đặc biệt đối với họ, chúng tôi đã chuẩn bị một video hướng dẫn với mô tả từng bước về tất cả các hành động được thảo luận ở trên.

Giới thiệu

1: Microsoft Excel

1.1 Khái niệm và khả năng của MS Excel

1.2 Các thành phần cơ bản của cửa sổ MS Excel

1.4 Những lỗi có thể xảy ra khi sử dụng hàm trong công thức

2: Phân tích dữ liệu. Sử dụng tập lệnh

2.1 Phân tích dữ liệu trong MS Excel

2.2 Kịch bản

2.3 Ví dụ tính tỷ lệ doanh thu đầu tư nội bộ

Phần kết luận

Thư mục

Giới thiệu

Microsoft Office, dòng sản phẩm phần mềm văn phòng phổ biến nhất, bao gồm các phiên bản mới của các ứng dụng quen thuộc hỗ trợ công nghệ Internet, cho phép bạn tạo các giải pháp Internet linh hoạt

Microsoft Office là dòng sản phẩm phần mềm của Microsoft kết hợp các ứng dụng phổ biến nhất thế giới vào một môi trường duy nhất lý tưởng để làm việc với thông tin. Microsoft Office bao gồm bộ xử lý văn bản Microsoft Word, bảng tính Microsoft Excel, công cụ trình bày Microsoft PowerPoint và ứng dụng Microsoft Outlook mới. Tất cả các ứng dụng này tạo nên Phiên bản Tiêu chuẩn của Microsoft Office. Phiên bản Professional cũng bao gồm Microsoft Access DBMS.

Microsoft Excel– một chương trình được thiết kế để sắp xếp dữ liệu trong một bảng để ghi lại và trình bày thông tin bằng đồ họa.

Chương trình MSExcel được sử dụng để tạo các tài liệu phức tạp cần thiết:

· sử dụng cùng một dữ liệu trong các bảng tính khác nhau;

· thay đổi và khôi phục các kết nối.

Ưu điểm của MSExcel là chương trình giúp thao tác với khối lượng thông tin lớn. Sổ làm việc MSExcel cung cấp khả năng lưu trữ và sắp xếp dữ liệu cũng như tính tổng các giá trị trong ô. MsExcel cung cấp nhiều phương pháp giúp thông tin trở nên dễ hiểu.

Ngày nay, điều quan trọng là mọi người phải biết và có kỹ năng làm việc với các ứng dụng Microsoft Office, vì thế giới hiện đại đã bão hòa với một lượng thông tin khổng lồ mà bạn chỉ cần có thể làm việc được.

Khóa học này sẽ trình bày chi tiết hơn về ứng dụng MSExcel, các chức năng và khả năng của nó. Cũng như việc sử dụng các kịch bản với ứng dụng thực tế của chúng.

1. Microsoft Excel

1.1 . Microsoft Excel . Khái niệm và tính năng

Bộ xử lý bảng tính MS Excel (bảng tính) là một trong những ứng dụng được sử dụng thường xuyên nhất trong bộ MS Office, một công cụ mạnh mẽ trong tay những người có khả năng giúp đơn giản hóa đáng kể công việc thường ngày hàng ngày. Mục đích chính của MS Excel là giải quyết hầu hết mọi vấn đề tính toán, dữ liệu đầu vào có thể được trình bày dưới dạng bảng. Việc sử dụng bảng tính giúp đơn giản hóa việc làm việc với dữ liệu và cho phép bạn thu được kết quả mà không cần tính toán lập trình. Kết hợp với ngôn ngữ lập trình Visual Basic for Application (VBA), bộ xử lý bảng tính MS Excel trở nên phổ biến và cho phép bạn giải quyết mọi vấn đề, bất kể bản chất của nó.

Tính năng đặc biệt của bảng tính là khả năng sử dụng công thức để mô tả mối quan hệ giữa giá trị của các ô khác nhau. Tính toán sử dụng các công thức cụ thể được thực hiện tự động. Việc thay đổi nội dung của một ô dẫn đến việc tính toán lại giá trị của tất cả các ô được kết nối với nó bằng quan hệ công thức và do đó, cập nhật toàn bộ bảng theo dữ liệu đã thay đổi.

Các tính năng chính của bảng tính:

1. thực hiện các phép tính phức tạp cùng loại trên các tập dữ liệu lớn;

2. tự động hóa các tính toán cuối cùng;

3. Giải bài toán bằng cách chọn giá trị tham số;

4. Xử lý (phân tích thống kê) kết quả thực nghiệm;

5. tìm kiếm giá trị tham số tối ưu (giải bài toán tối ưu hóa);

6. chuẩn bị các tài liệu dạng bảng;

7. Xây dựng sơ đồ (kể cả sơ đồ tóm tắt) dựa trên số liệu sẵn có;

8. tạo và phân tích cơ sở dữ liệu (danh sách).

1.2. Các thành phần cơ bản của cửa sổ MS Excel

Các thành phần chính của cửa sổ làm việc là:

1. Thanh tiêu đề (cho biết tên chương trình) với các nút điều khiển cửa sổ chương trình và cửa sổ tài liệu (Thu gọn, Thu nhỏ thành cửa sổ hoặc Phóng to toàn màn hình, Đóng);

2. Thanh menu chính (mỗi mục menu là một tập hợp các lệnh được thống nhất bởi một trọng tâm chức năng chung) cộng với một cửa sổ tìm kiếm thông tin trợ giúp.

3. Thanh công cụ (Tiêu chuẩn, Định dạng, v.v.).

4. Thanh công thức, chứa các thành phần là trường Tên và nút Chèn Hàm (fx), dùng để nhập và chỉnh sửa các giá trị hoặc công thức trong ô. Trường Tên hiển thị địa chỉ của ô hiện tại.

5. Khu vực làm việc (bảng tính hoạt động).

6. Thanh cuộn (dọc và ngang).

7. Bộ phím tắt (phím tắt trang tính) để di chuyển giữa các bảng tính.

8. Thanh trạng thái.

1.3 Cấu trúc bảng tính

Một tệp được tạo bằng MS Excel thường được gọi là sổ làm việc. Bạn có thể tạo bao nhiêu sổ làm việc tùy theo khả năng sẵn có của bộ nhớ trống trên thiết bị bộ nhớ thích hợp. Bạn có thể mở bao nhiêu sổ làm việc mà bạn đã tạo. Tuy nhiên, chỉ có một sổ làm việc hiện tại (mở) có thể là sổ làm việc hiện hoạt.

Sổ làm việc là một tập hợp các trang tính, mỗi trang tính có cấu trúc dạng bảng. Cửa sổ tài liệu chỉ hiển thị trang tính hiện tại (đang hoạt động) mà bạn đang làm việc. Mỗi trang tính có một tiêu đề xuất hiện trên tab trang tính ở cuối cửa sổ. Bằng cách sử dụng các phím tắt, bạn có thể chuyển sang các trang tính khác có trong cùng một sổ làm việc. Để đổi tên một bảng tính, bạn cần nhấp đúp vào tab của nó và thay thế tên cũ bằng tên mới hoặc bằng cách thực hiện các lệnh sau: Menu định dạng, Dòng trang tính trong danh sách menu, Đổi tên. Hoặc bạn có thể, bằng cách đặt con trỏ chuột lên phím tắt của trang tính đang hoạt động, nhấp chuột phải, sau đó trong menu ngữ cảnh xuất hiện, nhấp vào dòng Đổi tên và thực hiện đổi tên. Bạn có thể thêm (chèn) trang tính mới vào sổ làm việc hoặc xóa những trang tính không cần thiết. Việc chèn một trang tính có thể được thực hiện bằng cách thực hiện lệnh menu Insert, dòng Sheet trong danh sách các mục menu. Trang tính sẽ được chèn trước trang tính đang hoạt động. Các hành động trên cũng có thể được thực hiện bằng menu ngữ cảnh, được kích hoạt bằng cách nhấp vào nút chuột phải, con trỏ của menu này sẽ được đặt trên tab của trang tính tương ứng. Để hoán đổi bảng tính, bạn cần đặt con trỏ chuột vào tab của bảng tính đang di chuyển, nhấn chuột trái và kéo tab đến vị trí mong muốn.

Một trang tính (bảng) bao gồm các hàng và cột. Các cột có tiêu đề bằng các chữ cái Latinh viết hoa và hơn nữa là sự kết hợp của hai chữ cái. Bảng tính chứa tổng cộng 256 cột, được đặt tên từ A đến IV. Các dòng được đánh số tuần tự từ 1 đến 65536.

Các ô trong bảng được hình thành tại giao điểm của các cột và hàng. Chúng là những yếu tố tối thiểu được thiết kế để lưu trữ dữ liệu. Mỗi ô có địa chỉ riêng. Địa chỉ ô bao gồm tên cột và số hàng tại giao điểm của ô đó, ví dụ: A1, B5, DE324. Địa chỉ ô được sử dụng khi viết công thức xác định mối quan hệ giữa các giá trị nằm trong các ô khác nhau. Tại thời điểm hiện tại, chỉ có một ô có thể hoạt động, ô này được kích hoạt bằng cách nhấp vào ô đó và được đánh dấu bằng khung. Khung này đóng vai trò như một con trỏ trong Excel. Các thao tác nhập và chỉnh sửa dữ liệu luôn chỉ được thực hiện trong ô hiện hoạt.

Dữ liệu nằm trong các ô liền kề tạo thành một khu vực hình chữ nhật có thể được tham chiếu dưới dạng một đơn vị trong công thức. Một nhóm các ô được bao quanh bởi một vùng hình chữ nhật được gọi là một phạm vi. Các phạm vi hình chữ nhật được sử dụng phổ biến nhất được hình thành tại giao điểm của một nhóm các hàng tuần tự và một nhóm các cột tuần tự. Một phạm vi ô được biểu thị bằng cách chỉ định địa chỉ của ô đầu tiên và địa chỉ của ô cuối cùng trong phạm vi, được phân tách bằng dấu hai chấm, ví dụ: B5:F15. Việc chọn một phạm vi ô có thể được thực hiện bằng cách kéo con trỏ chuột từ ô góc này sang ô đối diện theo đường chéo. Khung của ô hiện tại (đang hoạt động) sẽ mở rộng, bao phủ toàn bộ phạm vi đã chọn.

Để tăng tốc và đơn giản hóa công việc tính toán, Excel cung cấp cho người dùng một bộ máy mạnh mẽ gồm các hàm bảng tính cho phép thực hiện hầu hết tất cả các phép tính có thể.

Tổng cộng, MS Excel chứa hơn 400 hàm trang tính (hàm tích hợp sẵn). Tất cả chúng, theo mục đích của chúng, được chia thành 11 nhóm (loại):

1. chức năng tài chính;

2. chức năng ngày và giờ;

3. các hàm số học và lượng giác (toán học);

4. chức năng thống kê;

5. chức năng liên kết và thay thế;

6. chức năng cơ sở dữ liệu (phân tích danh sách);

7. chức năng văn bản;

8. chức năng logic;

9. Chức năng thông tin (kiểm tra thuộc tính và giá trị);

10.chức năng kỹ thuật;

11. chức năng bên ngoài.

Viết bất kỳ hàm nào vào ô trang tính phải bắt đầu bằng ký hiệu bằng (=). Nếu một hàm được sử dụng như một phần của bất kỳ hàm phức tạp nào khác hoặc trong một công thức (công thức lớn), thì ký hiệu bằng (=) được viết trước hàm (công thức) này. Bất kỳ hàm nào cũng được truy cập bằng cách chỉ định tên của nó, theo sau là một đối số (tham số) hoặc danh sách các tham số trong ngoặc đơn. Sự hiện diện của dấu ngoặc đơn là bắt buộc; chúng đóng vai trò là dấu hiệu cho thấy tên được sử dụng là tên của hàm. Các tham số danh sách (đối số hàm) được phân tách bằng dấu chấm phẩy (;). Số lượng của chúng không được vượt quá 30 và độ dài của công thức chứa số lệnh gọi hàm như mong muốn không được vượt quá 1024 ký tự. Khi viết (nhập) công thức, nên gõ tất cả các tên bằng chữ thường, khi đó những tên nhập đúng sẽ hiển thị bằng chữ in hoa.

1.4 Những lỗi có thể xảy ra khi sử dụng hàm trong công thức

Hàm là một công thức đặc biệt, được tạo sẵn để thực hiện các phép tính phức tạp, trong đó người dùng chỉ cần nhập các đối số cần thiết. Nó có tên mô tả mục đích của nó (ví dụ: SUM là phép cộng của một số số) và các đối số. Có thể có một số cái sau, nhưng chúng luôn được đặt trong dấu ngoặc đơn. Tiếp theo, chúng ta sẽ xem xét các chức năng chính của Excel và mô tả ngắn gọn mục đích của chúng.

Tính năng phổ biến nhất

1. SUM – dùng trong Excel để cộng giá trị trong các trường.
2. IF – cho phép bạn trả về các giá trị khác nhau tùy theo điều kiện đáp ứng.
3. XEM – cần thiết để tìm kiếm các giá trị nằm ở vị trí giống hệt nhau nhưng ở một cột hoặc hàng khác.
4. VLOOKUP - được sử dụng để tìm kiếm thông tin trong toàn bộ bảng Excel hoặc các phạm vi riêng lẻ của nó. Với sự trợ giúp của nó, bạn có thể dễ dàng tìm thấy số điện thoại theo họ hoặc ngược lại (tương tự như danh bạ điện thoại).
5. MATCH – được sử dụng để tìm kiếm một phần tử nhất định và trả về vị trí tương đối của nó trong phạm vi.
6. CHỌN – nhằm mục đích chọn các giá trị từ danh sách.
7. NGÀY – có thể trả lại số sê-ri của nó cho một khoảng thời gian nhất định.
8. NGÀY – cho phép bạn trả về số ngày giữa các ngày được chỉ định.
9. TÌM, TÌM - phát hiện sự xuất hiện của văn bản trong chuỗi văn bản khác.
10. CHỈ SỐ – tìm một giá trị hoặc liên kết đến giá trị đó trong một dải ô hoặc bảng Excel và trả về giá trị đó.

Cách nhập hàm vào bảng

Việc thêm giá trị cần thiết vào bảng tính có thể được thực hiện trực tiếp từ bàn phím hoặc thông qua lệnh Excel đặc biệt nằm trong menu “Chèn”. Sau khi chọn ô và chọn “Chèn/Chức năng”, hộp thoại Wizard sẽ xuất hiện trên màn hình.

Trước tiên, bạn nên chọn một danh mục, sau đó là hàm Excel từ danh sách theo thứ tự bảng chữ cái. Chương trình sẽ nhập dấu bằng, tiêu đề, dấu ngoặc đơn vào ô và mở hộp thoại sau. Nó phải chứa các đối số (tọa độ của các ô chứa dữ liệu cần thiết). Sau khi nhấn nút “Ok” hoặc Enter trên bàn phím, hàm thu được sẽ xuất hiện ở dòng có công thức.

Các bạn, chúng tôi đã đặt cả tâm hồn vào trang web. cảm ơn vì điều đó
rằng bạn đang khám phá vẻ đẹp này. Cảm ơn vì nguồn cảm hứng và sự nổi da gà.
Tham gia cùng chúng tôi Facebook Và Liên hệ với

Ngày nay, Microsoft Excel đơn giản là không thể thay thế được, đặc biệt là khi xử lý lượng lớn dữ liệu. Tuy nhiên, chương trình này có quá nhiều chức năng nên không dễ để tìm ra chức năng nào thực sự cần thiết và hữu ích.

Và đó là lý do tại sao hôm nay trang mạng sẽ cho bạn biết những cách nào bạn có thể hệ thống hóa thông tin một cách hiệu quả và sắp xếp mọi thứ theo thứ tự.

Bảng tổng hợp

Bảng tổng hợp giúp bạn dễ dàng sắp xếp, tính tổng hoặc giá trị trung bình từ dữ liệu bảng tính mà không cần phải viết bất kỳ công thức nào.

Cách sử dụng:

1. Lựa chọn Chèn >.
2. Trong hộp thoại PivotTable được đề xuất Bấm vào bất kỳ bố cục PivotTable nào để xem bản xem trước của nó, rồi chọn bố cục hiển thị dữ liệu theo cách bạn muốn. Nhấn vào nút ĐƯỢC RỒI.
3. Excel thêm PivotTable vào trang tính mới và hiển thị danh sách các trường mà bạn có thể sử dụng để sắp xếp dữ liệu trong bảng.

Lựa chọn tham số

Nếu bạn biết mình muốn kết quả nào từ một công thức nhưng không thể xác định giá trị đầu vào sẽ tạo ra kết quả đó, hãy sử dụng Parameter Finder.

Cách sử dụng:

1. Lựa chọn Dữ liệu > Làm việc với dữ liệu > Phân tích giả định > Lựa chọn tham số.
2. Trong lĩnh vực Đặt thành ô Nhập liên kết đến ô chứa công thức mong muốn.
3. Trong lĩnh vực Nghĩa Nhập kết quả công thức mong muốn.
4. Trong lĩnh vực Thay đổi giá trị tế bào nhập một liên kết đến ô chứa giá trị cần điều chỉnh và nhấp vào nút ĐƯỢC RỒI.

Định dạng có điều kiện

Định dạng có điều kiện cho phép bạn nhanh chóng đánh dấu thông tin quan trọng trên trang tính.

Cách sử dụng:

Trên tab trang chủ trong nhóm Kiểu dáng bấm vào mũi tên bên cạnh nút Định dạng có điều kiện và chọn công thức bạn sẽ cần.

Ví dụ: nếu bạn cần làm nổi bật tất cả các giá trị nhỏ hơn 100, hãy chọn Quy tắc chọn ô > Nhỏ hơn, rồi quay số 100. Trước khi nhấn ĐƯỢC RỒI, bạn có thể chọn định dạng sẽ được áp dụng cho các giá trị thích hợp.

CHỈ SỐ và MATCH

Nếu VLOOKUP giúp bạn chỉ tìm thấy dữ liệu cần thiết trong cột đầu tiên, thì nhờ các hàm INDEX và TÌM KIẾM, bạn có thể tìm kiếm thông tin bên trong bảng.

Cách sử dụng:

1. Đảm bảo các ô dữ liệu tạo thành một lưới có tiêu đề và tiêu đề hàng.
2. Sử dụng chức năng TÌM KIẾM: Đầu tiên hãy tìm cột chứa mục bạn đang tìm kiếm, sau đó lại di chuyển đến hàng có câu trả lời.
3. Dán câu trả lời vào MỤC LỤC, và Excel có thể trỏ đến ô nơi các giá trị đó giao nhau.