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 we will learn about some methods of function input in Excel.
As shown in Figure 1-1, there are [AutoSum] buttons under the [Formula] tab and [Start] tab. Using this button, you can quickly insert sum, count, average, maximum and minimum values, etc. formula.
By default, clicking the AutoSum button or pressing a key combination will insert the SUM function for summation. Click the drop-down button on the right side of the [Auto-Sum] button, and the drop-down list includes 6 options of [Sum], [Average], [Count], [Maximum], [Minimum], and [Other Functions], as shown in Figure 1. -2 shown.
Figure 1-1 Autosum button
Figure 1-2 AutoSum button options
When you click the [Other Function] button in the drop-down list, the [Insert Function] dialog box will open, as shown in Figure 1-3.
After selecting one of the calculation methods [Sum], [Average], [Count], [Maximum], [Minimum], Excel will intelligently select the cells for the formula statistics based on the selected cell range and the surrounding data distribution. range for quick input. As shown in Figure 1-4, select the cell range B2:H8, click the [AutoSum] button under the [Formula] tab, and Excel will sum each column and row of the range separately.
Figure 1-3 Insert Function Dialog Box
Figure 1-4 Summing multiple rows and columns at the same time
Normally, Excel automatically sums the data above the row where the formula is located or to the left of the column where the formula is located. If there are blank cells above and to the left of the cell where the autosum formula is inserted, the user is required to specify the summation range.
If the table area to be calculated is in the filtered state, clicking the [Auto Summation] button will use the SUBTOTAL function to perform summation, average, count, maximum, minimum and other summary calculations in the filtered state.
In the [Function Library] command group under the [Formula] tab, the Excel classification provides [Finance] [Logic] [Text] [Date and Time] [Find and Reference] [Math and Trigonometric Functions] [Other Functions], etc. There are multiple drop-down buttons, and the [Other Functions] drop-down button also provides extended menus such as [Statistics] [Project] [Cube] [Information] [Compatibility] and [Web Functions]. Users can insert a built-in function (except database functions) as needed, or click the [Recently used function] drop-down button to select the 10 most recently used functions in the drop-down menu (list), as shown in Figure 1-5. Show.
Figure 1-5 Inserting functions of known classes using the function library
Use the [Insert Function] dialog wizard to select or search for the desired function, and the optional categories will be more abundant. The following methods can open the [Insert Function] dialog box.
❖Click the [Insert Function] button on the [Formula] tab.
❖Switch to the [Formula] tab, click the [Insert Function] command in each drop-down menu of the [Function Library] command group, or click the [AutoSum] drop-down button, and click [Other Functions] in the extended menu. .
❖Press <Shift+F3> key combination.
As shown in Figure 1-6, enter the keyword "Average" in the [Search Function] edit box, click the [Go] button, the dialog box will display a list of "recommended" functions, after selecting the desired function, click Click the [OK] button to insert the function and switch to the [Function Parameters] dialog box.
Figure 1-6 Insert function
In the [Function Parameters] dialog box, it is mainly composed of function name, parameter edit box, function introduction, parameter description and calculation result from top to bottom. Among them, the parameter edit box allows to input parameters directly or click the insert function collapse button in Figure 1-6 above to select the cell area, as shown in Figure 1-7.
Figure 1-7 Function parameter dialog box
Excel opens the "Formula Autotype" function by default. As long as you enter the first part of the letter, a list of all related functions and names will appear for selection.
When editing a formula, press the key combination <alt+ <="" span="">↓ > to switch whether to enable the "Formula Autotype" function, or click [File] → [Options], in [Excel Options] In the [Formula] tab of the dialog box, select the [Formula AutoComplete] check box in the [Use formula] area, and then click the [OK] button to close the dialog box.
For example, when the input method is switched to the English input state, and after entering "=SU", Excel will automatically display the extended drop-down menu of all functions, names or "tables" beginning with "=SU". Different functions can be selected with the help of the up and down arrow keys or the mouse, and the function prompt will be displayed on the right side. Double-click the mouse or press the <Tab> key to add this function to the current editing position, which not only improves the input efficiency, but also ensures Enter the accuracy of the function name.
As more characters are entered, the candidates in the expand drop-down menu will gradually narrow, as shown in Figure 1-8.
Figure 1-8 Formula autotype
What is your usual way of entering functions? By being familiar with a variety of input methods, it is convenient for us to have a more systematic study of Excel functions and formulas.
" Excel 2019 Function and Formula Application Encyclopedia "
