EXCEL基础公式合集2018.2.2.doc

一、Vlookup1、数值查找文本公式 =VLOOKUP(F4&,$A$2:$C$6,3,0)2、文本查找数值公式 =VLOOKUP(F12*1,$A$10:$C$14,3,0)3、文本数值相互查找公式 =IF(ISNA(VLOOKUP(F20*1,$A$18:$C$22,3,0), VLOOKUP(F20&,$A$18:$C$22,3,0),VLOOKUP(F20*1,$A$18:$C$22,3,0)4、通配符查找=VLOOKUP(A2&*,数据源!B:E,4,0)5、批量查找和原数据顺序一致的数据VLOOKUP+COLUMN(列计数公式)=VLOOKUP($D4,数据源!$A:$K,COLUMN()-3,0)6、批量查找和原数据顺序不一致的数据使用MATCH查找表头在原数据是在第几个=VLOOKUP($A3,数据源!$A:$K,MATCH(B$2,数据源!$A$1:$K$1,0),0)二、Lookup1、个税计算公式，数组计算 =MAX(F7-3500)*$C$6:$C$12-$D$6:$D$12) 公式输入完成后计算需同时按ctrl+shift+enter2、用数组查找值 =LOOKUP(1,0/($B$3:$B$7=G3),$E$3:$E$7)3、数组精确匹配=LOOKUP(1,0/($A$2:$A$13=K6)*($B$2:B$13=L6),$D$2:$D$13) 三、INDEX&MATCH1、反向查找类似VLOOKUP =INDEX(数据源!B:B,MATCH(查询!A2,数据源!A:A,0)2、利用MATCH&INDEX查找提取图片1、自定义名称，引用位置：查找照片位置的公式，公式名称设置为：照片2、使用照相机，点击照相机，随意点击单元格，将框拖拽到需显示照片的单元格3、点击图片框，修为改=照片四、INDIRECT1、 跨表提取位置相同的数值=INDIRECT(A4&!g2)2、 跨表提取位置不相同的数值= =VLOOKUP(张三,INDIRECT(A4&!A:G),7,0)3、Vlookup批量查找绝对值应用 =VLOOKUP(B$2,INDIRECT($A3&!$A:$G),7,0)五、SUMIFS1、在多数据中，求和一段时间的数据=SUMIFS(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&”在哪个城市的？”八、SUMIF&SUMIFS1、查找对象数字大于15位，查找对象需加上&”*” =SUMIF(A:A,F4&*,B:B)2、多行数值相同求和，最后参数为求和项的第一个目录。 =SUMIF($A:$J,$L3,$B$1)3、 SUMIF使用辅助列求和=SUMIF(A:A,H5&I5,G:G)4、 SUMIFS多条件求和=SUMIFS($F:$F,$D:$D,$G5,$E:$E,$H5)5、 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($G$2:$G$14,A2) =IF(COUNTIF($G$2:$G$14,A2)=1,是,否)4、条件格式=COUNTIF($G$2:$G$14,A1)=0=COUNTIF($G$2:$G$14,A2)=05、数据有效性 =COUNTIF(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、分钟转变为小时=D4+E4/24/602、小时转变为分钟=(E9-D9)*60*243、DATE函数=DATE(YEAR(B5),MONTH(B5)+C5,DAY(B5)4、本月天数=DAY(DATE(YEAR(B21),MONTH(B21)+1,0)6、 工龄，日期与日期之间相减=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)=TEXT(B10,0000-00-00)*1 十四、条件格式将数量大于100的项目日期标记为红颜色背景*动态图标的制作1、开发工具-插入控件-定义控件2、定义公式名称:=IF(Sheet8!$J$11,Sheet8!$B$2:$B$7,Sheet8!$N$2:$N$7)=各区域名称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(当前单元格,FIND(C