工作中最常用Excel函数公式大全

工作中最常用Excel函数公式大全

一、数字处理

1、取绝对值
　　=ABS(数字)
　　2、取整
　　=INT(数字)
　　3、四舍五入
　　=ROUND(数字,小数位数)

二、判断公式

1、把公式产生的错误值显示为空
　　公式：C2
　　=IFERROR(A2/B2,"")
　　说明：如果是错误值则显示为空，否则正常显示。



2、IF多条件判断返回值
　　公式：C2
　　=IF(AND(A2<500,B2="未到期"),"补款","")
　　说明：两个条件同时成立用AND,任一个成立用OR函数.

Microsoft Office Tips and Tricks (Word, Excel, Visio, PowerPoint)

Online PDF to Word

• Convert PDF to Word (Free!)
• SmallPDF

1. Word

1.1 Convert Texts into a Table:

 

1.2 Add separate line quickly

Excel Formula: Search a Column of Strings to Match Another Column

Once a while, I have to work on Excel sheet manually. Today I have to search exported hundreds of ip addresses in a pre-defined excel spreadsheet to see if there is a match. It seems a easy work, but it took me almost an hour to find right formula.

Column O includes all exported ip addresses. I have to manually search if those column O's ip addresses are appearing in column B's text. If yes, which row is it?

Formula is set at Column N.

=MATCH("*"&(O6)&"*",B:B,0)

As you can see from cell N6, the number is 7, which means the text in B7 includes O6's string.
N12's number is 5, which means the test in B5 includes O12's string. 

Excel Formula : Convert a text to Number

Got a Excel file from other resource witch Column A is set as text. At the tail and start of number there are some spaces filled in, for example cell A1 is '  4 '.

My challenge is to convert whole column to numbers which can be used to do sum or other math calculation.

With some research, I constructed this formula for a new column :
=VALUE(TRIM(CLEAN(SUBSTITUTE(A1,CHAR(160)," "))))

it perfectly resolved this challenge as you can see from following screen shot.

Note: trim will not work with the only space cell. Thanks reply from Hari Krishna.