The power of spreadsheets lies not only in their ability to record numbers and generate reports, but also in their ability to automate calculations, intelligent analysis, and logical reasoning through “functions and formulas.”
Whether it’s budgeting, sales statistics, or performance accounting, mastering the logic and usage of functions can transform a spreadsheet from a passive “data container” into an active “calculation engine.
” For users new to WPS办公软件 Spreadsheets or Excel, functions and formulas are crucial for getting started—understanding their structure, mastering reference types, and becoming familiar with input techniques are essential to truly unleash the computational potential of spreadsheets.
What is a function and why is it so important?
A function can be thought of as a pre-set calculation formula that allows the software to automatically perform complex mathematical or logical operations.
Instead of typing in the calculation step by step, you simply tell the spreadsheet what to calculate.
For example:
- =SUM(A1:A10)It will automatically calculate the sum of all numbers from A1 to A10;
- =AVERAGE(B2:B6)The average value will be found;
- =IF(C2>100,”达标”,”未达标”)It can output different results according to the conditions.
The power of functions lies in their ability to:
- Significantly reduce the time cost of manual calculation ;
- Avoid manual input errors ;
- Realize automatic judgment of complex logic ;
- Make data analysis more systematic, intuitive and dynamic .
Simply put, mastering functions is equivalent to mastering the “brain” of the table.
How to insert a function?
WPS官网 Spreadsheet provides two main ways to insert functions, suitable for users of different levels.
Insert via the function menu
The most intuitive way is to use the menu operation:
- Click [Formulas ] in the top menu bar ;
- Select [Insert Function] ;
- When the dialog box pops up, select the required function type;
- Function descriptions and parameter hints will be displayed below to help you understand the usage;
- After filling in the corresponding parameters, click “OK” and the system will automatically generate the formula.
This method is especially suitable for beginners, because each parameter will be explained and it is not easy to make mistakes.
Enter the function directly into the cell
If you already have some understanding of the function, you can also enter it directly:
- Click on a blank cell;
- Enter the equal sign =to indicate “start entering the formula”;
- Enter a function name (e.g. SUMor AVERAGE);
- Enter the parameter range in brackets (such as A1:A10);
- Press Enter to confirm.
For example:
=SUM(A1:A10)
Indicates the sum of the contents of A1 to A10.
Tips:
If you can’t remember the full function name, just enter the first letter and the system will automatically associate related functions.
For example, if you enter , , , and other suggestions =Swill automatically pop up , making it easy to quickly select.SUMSUBTOTALSQRT
The basic structure of a function
Understanding the function structure is the first step to writing a good formula.
A complete function usually consists of three parts:
=函数名(参数1, 参数2, 参数3…)
For example:
=IF(A1>60,”合格”,”不合格”)
- =Indicates that this is a formula;
- IFis the function name;
- The brackets A1>60,”合格”,”不合格”are parameter lists;
- Parameters are separated by commas ,.
Each function has a different number and type of parameters, but their logic is consistent: a function receives input parameters and returns a calculated result .
Cell reference types in function formulas
The key to mastering functions is not just knowing how to write them, but understanding the three modes of “references.”
References determine whether parameters automatically change when you copy or drag a formula.
Relative Reference
This is the most common form, for example:
=B2+C2
When you drag this formula from row 2 to row 3, it automatically adjusts to:
=B3+C3
In other words, the reference will automatically change according to the cell location.
This feature makes batch calculations extremely convenient.
Applicable scenarios: Scenarios that require repeated calculations by row or column, such as summation, average, and multiplication.
Absolute Reference
Sometimes we don’t want the reference in the formula to change as we drag.
In this case, we need to use “absolute reference”, that is, add a symbol before the row and column $, such as:
=$A$1
The is $like a small lock that locks the position of the row and column.
No matter where you copy the formula, $A$1it always points to the same cell.
For example:
=B2*$A$1
No matter which row you copy to, the system will always use the value in A1 for calculation.
Applicable scenarios: fixed tax rate, discount ratio, exchange rate, or constant calculation, etc.
Mixed Reference
Mixed references are between relative references and absolute references, locking only a portion of a row or column.
There are two forms:
- $A1: Lock column A, the row number can be changed;
- A$1: Lock the first row, the column number can be changed.
For example:
=B$2*$A1
When dragging a formula horizontally or vertically, a more flexible reference mode can be achieved, which is often used in multiplication tables or cross calculation tables.
Tips:
Press F4 to cycle through the four reference states:
- A1→ Relative reference
- $A$1→ Absolute reference
- A$1→ Lock Row
- $A1→ Lock Column
This shortcut greatly improves editing efficiency.
Examples of function application scenarios
After understanding structures and reference types, let’s look at the typical usage of functions in actual work.
Financial Statistics
| Example | Function | Example formula |
| Total income | Calculate total sales | =SUM(B2:B50) |
| Average expenditure | Statistics average cost | =AVERAGE(C2:C50) |
| Maximum/Minimum | Find extreme values | =MAX(D2:D20) / =MIN(D2:D20) |
| growth rate | Calculating Percentage Growth | =(本期-上期)/上期 |
Conditional judgment and classification statistics
Logical functions help us automatically determine the state of data:
| Example | Function | Example formula |
| Passing grade judgment | Above 60 indicates “qualified” | =IF(A2>=60,”合格”,”不合格”) |
| Bonus Calculation | Rewards will be issued when conditions are met | =IF(B2>10000,B2*0.1,0) |
| Multiple condition judgment | Using nested or IFS functions | =IFS(A2<60,”不合格”,A2<80,”良好”,A2>=80,”优秀”) |
Date and time calculations
| Example | Function | Example formula |
| Calculate the number of days between two dates | =B2-A2 | |
| Get the current date | =TODAY() | |
| Get the current time | =NOW() | |
| Extracting the year from a date | =YEAR(A2) | |
| Extract month or day of week | =MONTH(A2)/=WEEKDAY(A2) |
Text processing and format optimization
Text functions are extremely useful for organizing data:
| Example | Function | Example formula |
| Splicing text | =CONCAT(A2,B2)or=A2&” “&B2 | |
| Extract left/right characters | =LEFT(A2,3)/=RIGHT(A2,4) | |
| Counting characters | =LEN(A2) | |
| Remove extra spaces | =TRIM(A2) |
These functions allow tables to not only calculate numbers but also flexibly process text information.
Practical tips to improve efficiency
- When you enter a function name using the formula prompt
, WPS will automatically display the parameter description. Hover to view detailed usage. - Double-click the function name to quickly complete it.
Enter the first letter, select it from the drop-down menu, and press Enter. The system will automatically fill in the brackets. - Use the formula review tool
and select “Formula Review” in the [Formula] menu to intuitively see the dependencies and calculation paths.
If errors such as #VALUE!or occur in error value processing#DIV/0! , you can combine them IFERROR()for optimization:
=IFERROR(A1/B1,”错误”)- Named ranges make formulas more intuitive
. Name a data range “Sales”, and then you can directly write:
=SUM(销售额)
Learning path recommendations for functions
Faced with hundreds of functions, beginners do not need to rush to master all of them. They can gradually deepen their understanding according to the following levels:
- Basic calculation functions : SUM, AVERAGE, MAX, MIN;
- Logical judgment functions : IF, AND, OR, IFS;
- Find reference functions : VLOOKUP, HLOOKUP, INDEX, MATCH;
- Date and time functions : TODAY, DATEDIF, NETWORKDAYS;
- Text processing functions : LEFT, RIGHT, LEN, CONCATENATE;
- Advanced statistical functions : COUNTIF, SUMIFS, AVERAGEIFS;
- Custom and nested functions : function composition and dynamic reference.
If you master the first two categories, you will be able to cope with daily office needs;
if you are proficient in the first three categories, you will be able to independently complete complex financial, sales and management analysis.
Let formulas become your thinking tools
Functions and formulas aren’t just tools for calculating data; they’re also a way of thinking.
Those who understand how to use functions often also understand how to break down problems, restructure logic, and optimize processes.
By properly setting reference types, flexibly combining function structures, and mastering shortcut techniques, you can have your spreadsheet automatically handle most repetitive tasks, freeing up time for higher-level analysis and decision-making.
Starting with basic functions and gradually deepening your application of logic and search functions, you can truly put your data to work, extending efficiency and intelligence.