EXCEL基础公式合集

1、一、Vlookup1、数值查找文本公式 =VLOOKUP(F4&，A2：$C$6,3,0）2、文本查找数值公式 =VLOOKUP（F121，A$10:C14,3,0）3、文本数值相互查找公式 =IF（ISNA(VLOOKUP(F201,A18：C22，3，0）, VLOOKUP（F20”,$A18：$C$22,3，0）,VLOOKUP（F201，$A18:$C22,3，0）)4、通配符查找=VLOOKUP（A2&”，数据源！B：E,4,0）5、批量查找和原数据顺序一致的数据VLOOKUP+COLUMN(列计数公式）=VLOOKUP（D4，数据源!$A：K,COLUMN(）3,0）6、批量查找和

2、原数据顺序不一致的数据使用MATCH查找表头在原数据是在第几个=VLOOKUP（A3,数据源！$A：$K，MATCH(B2,数据源！$A$1:K1,0),0)二、Lookup1、个税计算公式，数组计算 =MAX(（F7-3500）*C$6:$C$12D6：D12) 公式输入完成后计算需同时按ctrl+shift+enter2、用数组查找值 =LOOKUP(1，0/（B$3:B7=G3），E3：E7）3、数组精确匹配=LOOKUP(1，0/($A2:$A13=K6）($B2:B$13=L6),$D2：D$13) 三、INDEX&MATCH1、反向查找类似VLOOKUP =INDEX(数据源！B：

3、B,MATCH(查询!A2,数据源!A:A,0）)2、利用MATCH&INDEX查找提取图片1、自定义名称，引用位置：查找照片位置的公式,公式名称设置为:照片2、使用照相机，点击照相机，随意点击单元格，将框拖拽到需显示照片的单元格3、点击图片框，修为改=照片四、INDIRECT跨表提取位置相同的数值=INDIRECT（A4!g2”）跨表提取位置不相同的数值= =VLOOKUP(”张三”,INDIRECT（A4&！A：G),7，0)3、Vlookup批量查找绝对值应用 =VLOOKUP（B2，INDIRECT（A3&!$A：$G”），7，0）五、SUMIFS1、在多数据中,求和一段时间的数据=S

4、UMIFS(11月总流水!$I:$I,11月总流水！D:$D,商户每周总流水!$C2,11月总流水!$C：C，”=2017/11/13”，11月总流水!C:C,=2017/11/19”，11月总流水！$V：$V,”代理”)六、高级筛选功能需要把A表的数据筛选复制到B表，需要在B表执行高级筛选的命令。七、&符号的使用1、文字或链接与单元格链接=”你好，请问”&B2在哪个城市的？”八、SUMIFSUMIFS1、查找对象数字大于15位，查找对象需加上&” =SUMIF（A:A,F4&”，B：B）2、多行数值相同求和，最后参数为求和项的第一个目录。 =SUMIF（$A:$J,L3，B$1）SUMIF使

5、用辅助列求和=SUMIF（A:A，H5I5,G：G）SUMIFS多条件求和=SUMIFS(F：$F，$D：$D，$G5，E:$E，H5）SUMIF求数值和可替代VLOOKUP=SUMIF(A:A,M4,J：J）1、SUMIF数据有效性=SUMIF(F：$F，F3，$G：G)=60)2、数据超过15位数，使用&*” =COUNTIF(A2:A3，A2&*”)3、 =COUNTIF（$G2：$G$14，A2) =IF（COUNTIF（G2:$G14,A2)=1，”是”，否”)4、条件格式=COUNTIF（G$2：$G$14,A1)=0=COUNTIF（G$2:G14,A2）=05、数据有效性 =C

6、OUNTIF(C：C,C1）=60）,AND(A20=”女,B20=40）,1000,0）十二、LEFT、MID、FIND1、时间计算为分钟数公式：=LEFT（C2,FIND（”小,C2)1)*60+MID（C2,FIND（时”，C2）+1,FIND(分”，C2）FIND（时”,C2)-1）时=LEFT(C2，FIND（”小”,C2）-1) 分=MID(C2，FIND（”时,C2）+1，FIND(分”,C2)-FIND(时,C2）1） 秒=MID(C3，FIND（”分,C3）+1，FIND(”秒”,C3)-FIND（分，C3）-1)100参数。对提取数据没有影响十三、时间函数：date1、分钟

7、转变为小时=D4+E4/24/602、小时转变为分钟=（E9D9）60243、DATE函数=DATE（YEAR(B5），MONTH（B5）+C5,DAY（B5)）4、本月天数=DAY（DATE(YEAR（B21），MONTH（B21)+1，0）工龄，日期与日期之间相减=DATEDIF（B5，C5+1,”Y）7、计算年、月、天=DATEDIF(B13，C13,y）&年DATEDIF(B13,C13，ym） &月”DATEDIF(B13，C13,”md”）日”8、计算日期是第几周 =WEEKNUM(B3，2)9、计算日期是周几=WEEKDAY（B8，2)10、=TEXT(B3,”aaaa”）=TE

8、XT(B10，000000-00）1 十四、条件格式将数量大于100的项目日期标记为红颜色背景*动态图标的制作1、开发工具-插入控件-定义控件2、定义公式名称：=IF(Sheet8！J11,Sheet8！B$2:$B7，Sheet8！N$2:N7)=各区域名称3、图表选择数据：数据=各区域名称十五、宏表函数1、63=计算单元格的颜色=GET。CELL(63,A2)2、提取单元格公式3、写超链接=4、快速建立超链接目录1。公式定义名称=GET。WORKBOOK(1）2超链接=HYPERLINK（INDEX(工作名称,ROW（）1)”！a1”)5、计算文本=公式定义名称=EVALUATE（B2)6、SUBSTITUTE:将字符串中的部分字符串以新字符替换1、=EVALUATE（SUBSTITUTE(A9,”,”+”)2、=EVALUATE（”&B9) 把数字变成数组3、数字无符号提取某一个数字=EVALUATE（”SUBSTITUTE(A16， ”，”;”)”）=数据3=INDEX（数据3，4）4、点击一个单元格，同列同行会变成一个,颜色设置好格式后按住F9后点击单元格1、公式定义名称当前单元格=REFTEXT(ACTIVE。CELL(）)&T(NOW()2、条件格式列=COLUMN（)=MID（当前