Let's talk about Excel function formulas

This article brings you relevant knowledge about excel, which mainly introduces the relevant content about function formulas. Let’s take a look at it together. I hope it will be helpful to everyone.

Relevant learning recommendations: excel tutorial

In the past few years, VBA has been in full swing, Power BI has been in full swing, and "big data" has emerged overnight It has become a buzzword for a while. If anyone uses the functions and formulas that come with Excel to process data, it will make people feel like there are shotguns on the aircraft carrier. Therefore, a popular view is called:

Function formulas are useless

It is undeniable that VBA is a good thing. VBA has powerful data interaction capabilities. Users only need to write data in Excel to complete complex report work. Many tasks that cannot be handled by function formulas, such as splitting workbooks, merging workbooks, etc., can be done using VBA code. Very easy to implement.

Small and clever is the advantage of VBA, but it is not unlimited. It is only suitable for moving and dodging on the computer. Would you like to give it to your boss who is accustomed to using his mobile phone to read reports? Are you trying it on customers who don't know how to enable macros? You are insulting VBA, you might as well just kill it.

In fact, when someone holds VBA and loudly says that function formulas are useless, function formulas are actually dismissed.

……

Power BI is also a good thing. It is especially good at data analysis and visualization, so many people have begun to promote that Power BI is 10,000 times more powerful than function formulas. After learning Power BI, you can completely There is no need to learn function formulas anymore. It just means that function formulas are useless.

In fact, to really learn Power BI well, you must use DAX functions and M functions to complete more complex data processing. Many functions that come with Excel have the same usage as DAX functions and M functions. , if you have a basic knowledge of Excel function formulas, it will not be too difficult to learn DAX functions and M functions.

……

Conclusion:

Everyone has their own advantages, but no one can replace the function formula, so don’t just say that the function formula is useless. Make trouble, but don't be afraid of it. Talk, open the door, fight, and stay with you to the end. Big Brother Function Formula has experienced more than ten years of ups and downs. What kind of battles have you never seen? On the territory of Office, no matter how violent you are, you are still a younger brother~.

The advantages of flexible and convenient function formulas are undoubted, but before introducing how powerful function formulas are, we must answer another question first. Can function formulas still be learned?

Function formulas can be said to be the most influential application in Excel. From conditional formatting and data validation to advanced charts and pivot tables, function formulas are everywhere like Lao Wang next door. If you are not familiar with function formulas, it is absolutely impossible to use Excel flexibly. And with the update of Excel version, some powerful new functions make the originally complex calculations become more and more simple. Most function formulas are common in all Excel versions, so you don't have to worry about them being eliminated due to version upgrades.

Although nothing lasts forever, it can be said with certainty that learning function formulas is not out of date now, nor will it be for a long time in the future.

Function formula is probably the easiest application to learn in Excel. After simple learning, a novice can quickly write simple function formulas to complete data statistics and summary, and once the data changes, you No additional operations are required at all to get the latest results.

Function formulas are easy to learn not only because of its ubiquitous application scenarios, but also because what it does is an Excel problem that you are very familiar with. It just replaces your manual problem-solving process with a built-in one. The calculation module, whatever you think, the function formula will do, that's all.

Well? Don't you think functions are useful?

Just snap your fingers and raise a few small chestnuts. It’s not a big thing, it’s all something that can be done with a little effort.

1. Conditional query

As shown below, you need to find the corresponding salary standard from the salary standard table according to the job number. A VLOOKUP is enough.

=VLOOKUP(A2,薪资标准!A:C,3,0)

VLOOKUP uses the job number in cell A2 as the search value to query the corresponding record in the salary standard table.

2. Conditional Count

As shown below, it is necessary to count the proportion of people whose salary standard is above 8,000.

##

=COUNTIF(E:E,">8000")/COUNT(E:E)

First use COUNTIF(E:E,”>8000″) to count the number of more than 8000, and then use COUNT(E:E) to calculate column E For all the numbers in , divide the two to get the ratio. How about it, are you holding back? ?

3. Conditional summation

As shown in the figure below, the total amount must be calculated according to departments.

=SUMIF(B:B,F2,D:D)

If the department in column B is equal to the department in cell F2, sum the corresponding amounts in column D.

4. 有道翻译

如下图，A2输入要翻译的文字，B2输入以下公式，就可以实现英汉互译，当然，要保持电脑联网才可以：

=FILTERXML(WEBSERVICE("http://fanyi.youdao.com/translate?&i="&A2&"&doctype=xml&version"),"//translation")

5. 合并单元格计数

如下图，要统计每个店铺的人数。

同时选中D2:D13单元格区域，编辑栏输入公式后按Ctrl+回车：

=COUNTA(B2:B13)-SUM(D3:D13)

6. 合并单元格添加序号

如下图，要按照部门添加序号。

同时选中A2:A15单元格区域，编辑栏输入公式按Ctrl+回车：

=COUNTA(B$2:B2)

7. 合并单元格求和

如下图，要统计每个店铺的合计销售金额。

同时选中D2:D10单元格区域，编辑栏输入公式，按Ctrl+回车：

=SUM(C2:C20)-SUM(D3:D20)

SUM(C2:C20)即当前行及以下所有C列数据的和，SUM(D3:D20)是本类别之后所有类别之和，二者相减，得到是本类别的和。

8. 限制录入重复数

1、选中要输入数据的A2:A10单元格区域，【数据】→【数据验证】

2、允许类型选择“自定义”，公式为：

=COUNTIF(A:A,A2)=1

9. 突出显示重复数据

1、选中已录入数据的A2:A10单元格区域，新建格式规则

2、使用公式：

=COUNTIF(A$2:A2,A2)>1

3、设置突出显示的格式

相关学习推荐：excel教程

The above is the detailed content of Let's talk about Excel function formulas. For more information, please follow other related articles on the PHP Chinese website!