Functions and formulas are undoubtedly one of the most fascinating features in Excel. Using functions and formulas can help users to complete various required data operations, summarization, extraction and other tasks. Functions and formulas combined with data validation can limit the input content or type of data, and can also create dynamically updated drop-down menus. Functions and formulas are combined with the conditional formatting function to display user-defined formats according to the contents of cells. In applications such as advanced charts and pivot tables, functions and formulas are also indispensable.
Although there are no shortcuts to learning functions and formulas, it is also a matter of methods. The editor summarizes the learning experience of countless Excel masters in order to teach you the correct learning methods and ideas. Today, let's learn about Excel functions.
structure of the function
Excel functions are function modules that are predefined by Excel and perform calculations according to specific algorithms. Each Excel function has a unique name and is not case-sensitive.
A function consists of a function name, an opening parenthesis, arguments separated by commas, and a closing parenthesis.
Functions vary in the number of arguments, some with only one argument, some with multiple arguments, and some with no arguments. For example, the NOW function, RAND function, and PI function have no parameters, only the function name and a pair of parentheses.
The parameters of the function can use constants, arrays, cell references or other functions according to their own characteristics. When a function is used as an argument to another function, it is called a nested function.
Figure 1-1 shows the common formulas that use the IF function to determine positive numbers, negative numbers and zero, where the second IF function is a nested function of the first IF function.
Figure 1-1 Structure of a function
Classification of commonly used functions
In Excel functions, functions can be divided into the following 4 categories according to the source.
❖Built -in functions
Functions that Excel uses by default.
❖Extension function
Functions that must be loaded in order to function properly. For example, the EUROCONVERT function must have the "Euro Conversion Tool" add-in installed and loaded to function properly. If you want to load the "Euro Conversion Tool" add-in, you can click the [Developer] → [Excel Add-In] command in turn, select the [Euro Currency Tools] check box in the pop-up [Add-In] dialog box, and then click [OK] button, as shown in Figure 1-2.
Figure 1-2 Euro Conversion Tool Add-in
❖Custom Functions
Use VBA code to compile and implement functions of specific functions, such functions are stored in "modules" of the VB editor.
❖Macro table function
This type of function is Excel version 4.0 function, which needs to be used by defining a name or in a macro sheet, and most of these functions have been gradually replaced by built-in functions and VBA functions.
The file containing custom functions or macro table functions needs to be saved as "Macro-Enabled Workbook (.xlsm)", and after opening the file for the first time, you need to click [Enable Content] in the [Macros Disabled] security warning dialog box ] button, otherwise the macro table function will not be available.
Built-in functions can be divided into the following types according to their functions and application areas:
Text functions, information functions, logical functions, lookup and reference functions, date and time functions, statistical functions, math and trigonometry functions, financial functions, engineering functions, cube functions, compatibility functions, and web functions.
Among them, compatibility functions are legacy functions that have been retained in new versions of Excel with precision improvements from earlier versions or with names changed to better reflect their usage. While these functions are still backward compatible, users are advised to use the new functions from now on, as legacy functions may no longer be supported in future versions of Excel.
In practical applications, the functions of functions are continuously developed and excavated, and the problems that different types of functions can solve are not limited to a certain type. The flexibility and variability of functions is also the joy of learning function formulas.
Recognize the volatility of functions
Sometimes Excel also pops up a "Save changes to document?" dialog when a user opens a workbook but closes it without making any changes, because of the use of "volatile functions" in the workbook.
When a volatile function is used in a workbook, the volatile function is automatically recalculated every time a cell is activated or data is entered in a cell, or simply the workbook is opened.
Volatile functions do not cause automatic recalculation in the following cases.
❖When the recalculation mode of the workbook is set to "Manual".
❖When manually setting column width and row height instead of double-clicking to adjust to proper column width, except for hiding rows or setting row height to 0.
❖When formatting cells or other settings that change display properties.
❖When activating the cell or editing the cell content but pressing the key to cancel. Common volatile functions are as follows.
❖ Use the RAND and RANDBETWEEN functions to obtain random numbers, and new random numbers will be automatically generated each time you edit.
❖Get the TODAY and NOW functions of the current date and time, and return the date and time of the current system each time.
❖ OFFSET, INDIRECT functions that return cell references, each edit will relocate the actual reference area.
❖ The CELL function and INFO function to obtain cell information will refresh the relevant information every time you edit. In addition, if the third parameter of the SUMIF function uses the shorthand form, a recalculation is also caused.
Knowledge may seem boring, but through the above introduction, I hope you can better grasp the basic knowledge of Excel functions, and lay the foundation for in-depth study and use of functions and formulas to solve problems.
" Excel 2019 Function and Formula Application Encyclopedia "
