Not without reason, the Microsoft Excel program is extremely popular among ordinary PC users, as well as among specialists in various industries. The thing is that this application contains a huge number of integrated formulas called functions. Eight main categories allow you to create almost any equation and complex structure, up to the interaction of several separate documents.
The concept of built-in functions
What exactly are these built-in functions in Excel? These are special thematic formulas that make it possible to quickly and accurately perform any calculation.
By the way, if the simplest of functions can be performed in an alternative (manual) way, then actions such as logical or referential can only be done using the “Insert Function” menu.
There are a lot of such functions in Excel, it is impossible to know all of them. Therefore, Microsoft has developed a detailed reference that can be consulted in case of difficulty.
The function itself consists of two components:
- the name of the function (for example, SUM, IF, OR) that indicates what the operation does;
- function argument - it is enclosed in brackets and indicates in which range the formula operates and what actions will be performed.
By the way, not all basic Excel built-in functions have arguments. But in any case, the order must be followed, and the characters “()” must be present in the formula.
For built-in functions in Excel, a combination system is also provided. This means that at the same time in one cell can be used several formulas that are interconnected.
Function Arguments
It is worthwhile dwelling on the concept of a function argument in detail, since they rely on work with built-in Excel functions.
The argument of the function is the set value at which the function will be executed, and return the desired result.
Typically used as an argument:
- numbers
- text;
- Arrays
- Errors
- ranges;
- logical expressions.
How to enter functions in Excel
Creating formulas is easy enough. You can do this by entering the necessary built-in MS-Excel functions on the keyboard. This, of course, provided that the user is quite good at Excel, and knows for memory how to correctly write this or that function.
The second option is more suitable for all categories of users - use the "Function" command, which is located in the "Insert" menu.
After starting this command, the function creation wizard will open - a small window in the center of the worksheet.
There you can immediately select a function category or open a complete list of formulas. After the desired function is found, you need to click on it and click "OK". The “=” sign and the function name will appear in the selected cell.
Now we can proceed to the second stage of creating the formula - the introduction of the function argument. The function wizard kindly opens another window in which the user is prompted to select the desired cells, ranges or other options, depending on the name of the function.
Well, in the end, when the argument is already selected, the full name of the function should appear in the formula bar.
Types of main functions
Now it’s worth considering in more detail what built-in functions are in Microsoft Excel.
Only 8 categories:
- mathematical (50 formulas);
- textual (23 formulas);
- logical (6 formulas);
- date and time (14 formulas);
- statistical (80 formulas) - perform the analysis of whole arrays and ranges of values;
- financial (53 formulas) - an indispensable thing in the calculations and calculations;
- work with databases (12 formulas) - processes and performs operations with databases;
- references and arrays (17 formulas) - works on arrays and indexes.
As you can see, all categories cover a fairly wide range of possibilities.
Math functions
This category is the most common, since mathematical built-in functions in Excel can be used for any type of calculation. Moreover, these formulas can be used as an alternative to a calculator.
But this is not all the advantages of this category of functions. A large list of formulas - 50, makes it possible to create multi-level formulas for complex scientific and design calculations, and for calculating planning systems.
The most popular function from this section is SUM (amount).
It calculates the sum of several values, up to a whole range. In addition, it is very convenient to calculate the total and display the total amount in different columns of numbers.
Another category leader is INTERMEDIATE. RESULTS. Allows you to read the total amount with a running total.
The functions PRODUCT (product), DEGREE (raising numbers to a power), SIN, COS, TAN (trigonometric functions) are also very popular, and, most importantly, do not require additional knowledge to introduce them.
Functions Date and Time
This category cannot be said to be very common. But the use of Excel's built-in functions “Date and Time” make it possible to transform and manipulate data related to time parameters. If a Microsoft Excel document is structural and complex, then the use of temporary formulas must take place.
The DATE formula indicates the current time in the selected sheet cell. The TDATE function works in the same way - it indicates the current date, converting the cell to a temporary format.
Logical functions
Special attention deserves the category of functions "Logical". Working with the built-in Excel functions prescribes compliance with and fulfillment of specified conditions, the results of which will be used for calculation. Actually, absolutely any conditions can be set. But the result has only two choices - Truth or Falsehood. Well, or alternative interpretations of these functions - the cell will be displayed or not.
The most popular command - IF - prescribes the indication in the given cell of a value that is true (if this parameter is given by the formula) and false (similarly). It is very convenient for working with large arrays in which certain parameters need to be excluded.
Two other inseparable commands - TRUE and FALSE, allow you to display only those values that were selected as the source. Any data other than that will not be displayed.
Formulas And and OR - give the opportunity to create a choice of certain values, with or without tolerances. The second function allows you to choose (true or false), but allows only one value to be displayed.
Text functions
In this category of Excel built-in functions, there are a large number of formulas that allow you to work with text and numbers. It is very convenient, since Excel is a spreadsheet editor, and inserting text into it may not be performed correctly. But using the functions in this section eliminates potential inconsistencies.
So, the BATTEXT formula converts any number into a calligraphic text. DLSTR counts the number of characters in the selected text.
The REPLACE function finds and replaces part of the text with another. To do this, enter the replacement text, the desired range, the number of characters to be replaced, as well as new text.
Statistical Functions
Perhaps the most serious and necessary category of built-in functions in Microsoft Excel. After all, it is a well-known fact that analytical data processing, namely sorting, grouping, calculating general parameters and searching for data, is necessary for planning and forecasting. And statistical built-in functions in Excel fully help to implement the plan. Moreover, the ability to use these formulas more than compensates for the lack of specialized software, due to the large number of functions and the accuracy of the analysis.
So, the AVERAGE function allows you to calculate the arithmetic mean of the selected range of values. It can work even with non-contiguous ranges.
Another good feature is AVERAGE (). It calculates the arithmetic mean of only those array values that satisfy the requirements.
The MAX () and MIN () formulas display the maximum and minimum values in the range, respectively.
Financial functions
As mentioned above, the Excel editor is very popular not only among ordinary people, but also among specialists. Especially for those who use calculations very often - accountants, analysts, financiers. Due to this, the application of the built-in functions of Excel financial category allows you to perform virtually any calculation related to monetary transactions.
Excel is very popular among accountants, economists and financiers, not least due to the extensive tools for performing various financial calculations. Mainly the implementation of tasks of this orientation is assigned to a group of financial functions. Many of them can be useful not only for specialists, but also for workers in related industries, as well as for ordinary users in their household needs. Let us consider these features of the application in more detail, and also pay special attention to the most popular operators of this group.
Database Functions
Another important category that includes built-in functions in Excel for working with databases. Actually, they are very convenient for quick analysis and checks of large lists and databases with data. All formulas have a common name - DB Function, but three parameters are used as arguments:
- database;
- selection bias;
- working field.
All of them are filled in according to the needs of the user.
A database, in fact, is a range of cells that are combined into a common database. Based on the selected intervals, the rows are converted to records, and the columns are converted to fields.
Work field - it contains a column that is used by the formula to determine the desired values.
The selection criterion is the interval of the selected cells in which the conditions of the function are located. That is, if in this interval there is at least one similar value, then it fits the criterion.
The most popular in this category of functions is COUNTIF. It allows you to count cells that fit the criteria in a selected range of values.
Another popular formula is SUMMES. It calculates the sum of all the values in the selected cells that have been filtered by the criterion.
Link Functions and Arrays
But this category is popular due to the fact that it includes the built-in VBA Excel functions, i.e., formulas written in the VBA programming language. Actually, working with active links and data arrays using these formulas is very easy, as they are clear and simple.
The main task of these functions is to isolate the values of the desired element from the array of values. For this, selection criteria are set.
Thus, the set of built-in functions in Microsoft Excel makes this program very popular. Moreover, the scope of the tables of this editor is very diverse.