EXCEL技巧收集
1、两列数据查找相同值对应的位置=MATCH(B1,A:A，0)2、已知公式得结果定义名称=EVALUATE(Sheet1!C1)已知结果得公式定义名称=GET.CELL(6,Sheet1!C1)3、强制换行用Alt+Enter4、超过15位数字输入这个问题问的人太多了，也收起来吧。一、单元格设置为文本；二、在输入数字前先输入'5、在EXCEL2000中，我们可以设置工作簿的共享来加快数据的录入速度，而且在工作过程中还可以随时查看各自所做的改动。当多人一起在共享工作簿上工作时，Excel会自动保持信息不断更新。在一个共享工作簿中，各个用户可以输入数据、插入行和列以及更改公式等，甚至还可以筛选出自己关心的数据，保留自己的视窗。一、设置共享工作簿我们可以先举个例子：假设有甲乙丙丁四个用户分工合作共同完成录入操作，四人分别在001、002、003、004号机器上同时操作，这四台机器都已经与000号电脑通过网络连接了起来，学生学籍资料信息2002.xls存放在000号机器的硬盘E:\上。1、在“工具”菜单中单击“共享工作簿”命令，然后单击“编辑” 标签，如图所示。2、选择“允许多用户同时编辑，同时允许工作簿合并”复选框，在“高级”标签中可以对“自动更新间隔”、“用户间修订冲突解决方法”以及“个人视图属性”等进行设置，在通常情况下，“高级”标签中的各个参数可直接使用系统默认值，然后单击[确定]即可。3、出现提示时，保存工作簿。4、在“文件”菜单中单击“另存为”，然后将共享工作簿保存在其他用户可以访问到的一个网络资源上（如000号机器的E:\）。二、使用共享工作簿1、查看冲突日志冲突日志在共享工作簿中，是为以前编辑时所作的更改保存的信息，信息包括每个更改的作者、进行更改的时间和被更改的数据内容等。查看冲突日志有两种方法：一是在工作表上将鼠标箭头停留在被修订过的单元格上，突出显示详细的修改信息；二是在单独的冲突日志工作表上罗列出每一处冲突，方法如下：（1）在“工具”菜单中，选择“修订”子菜单，再单击“突出显示修订”命令。（2）选中“编辑时跟踪修订信息，同时共享工作簿”复选框，该复选框将开启工作簿共享和冲突日志。（3）选中“在屏幕上突出显示修订”复选框，这样在工作表上进行需要的修改后，Excel会以突出显示的颜色标记修改、插入或删除的单元格。（4）选中“在新工作表中显示修订”选项，将启动冲突日志工作表。（5）单击[确定]按钮，当弹出对话框提示保存工作簿时，请再次单击[确定]按钮，保存工作簿。二、合并工作簿当合并用户需要获得各自更改的共享工作簿的若干备份时，Excel需要共享工作簿的每个备份都保留有从创建备份之日到合并之日的所有冲突日志，同时如果天数超过指定的保留时间，就不能再合并备份。保险的办法是将冲突日志保留足够长的时间，或是输入一个极大的天数，例如1000天。合并工作簿具体操作如下：（1）在欲合并的另一个工作簿文件之中，打开共享工作簿的备份。（2）单击“工具”菜单中的“合并工作簿”命令。（3）如果有提示，请保存共享工作簿。（4）在“将选定文件合并到当前工作簿”对话框中，单击包含有合并更改内容的共享工作簿的某一个备份，然后单击[确定]按钮。（5）重复第（2）步到第（4）步，直到共享工作簿的所有备份都被合并。做好以上设置后，其他用户都可以在自己的电脑中同时编辑、浏览约定的工作簿，同时000号操作员可以在自己的窗口中看到并管理所有用户共享编辑这个工作簿。为方便实际操作，应将每位用户姓名更改为真名实姓，方法是：单击“工具→选项→常规→用户姓名→输入姓名→确定”，这样在打开“共享工作簿”弹出的对话框（如前图所示）中便可正确显示正在使用本工作薄的所有用户姓名和开始使用时间。为了避免几个用户修改同一单元格的内容，可以估计一下数据中的记录数量，让几位用户每人分配不同的录入部分。比如让甲从工作薄的第1条开始录入，让乙从工作薄的第200行开始……但应注意在“共享工作簿”状态下不能同时编辑批注和建立超链接，这两项只能在不共享状态下进行。?提示?1、如果需要取消共享工作簿，只需要在图1的“编辑”标签中，清除“允许多用户同时编辑，同时允许工作簿合并”项即可，不过这将使其他用户丢失所有未保存的修改。2、对工作簿进行共享设置后，可以由网络上的多位用户同时访问和修改，为避免丢失修订记录，可以为工作簿指定一个密码来保护共享，或保护其他工作表及工作簿特性，可作如下操作：（1）单击“工具”菜单的“保护”命令，从其子菜单中选择“保护并共享工作簿”命令，弹出“保护共享工作簿”对话框（如图）。（2）选中“以追踪修订方式共享”复选框，用这种方式就可以共享工作簿且避免丢失修订记录。如果希望其他用户在关闭冲突日志或撤消工作簿共享状态时输入密码，请在“密码”框中键入密码，然后在出现提示时再次输入以便确认。为工作簿提供共享保护以后，其他用户就不能撤消工作簿共享状态或者关闭冲突日志了，除非知道正确的密码。6、EXCEL中行列互换复制，选择性粘贴，选中转置，确定即可7、Excel 是怎么加密的1、保存时可以的另存为&&右上角的&工具&&&常规&&设置2、工具&&选项&&安全性8、关于COUNTIFCOUNTIF函数只能有一个条件，如大于90，为=COUNTIF(A1:A10,&&=90&)介于80与90之间需用减，为 =COUNTIF(A1:A10,&&80&)-COUNTIF(A1:A10,&&90&)9、根据身份证号提取出生日期1、=IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),IF(LEN(A1)=15,DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2)),&错误身份证号&))2、=TEXT(MID(A2,7,6+(LEN(A2)=18)*2),&#-00-00&)*110、想在SHEET2中完全引用SHEET1输入的数据工作组，按住Shift或Ctrl键，同时选定Sheet1、Sheet2。11、一列中不输入重复数字[数据]--[有效性]--[自定义]--[公式]输入=COUNTIF(A:A,A1)=1如果要查找重复输入的数字条件格式》公式》=COUNTIF(A:A,A5)&1》格式选红色12、直接打开一个电子表格文件的时候打不开“文件夹选项”－“文件类型”中找到.XLS文件，并在“高级”中确认是否有参数1%,如果没有，请手工加上13、excel下拉菜单的实现[数据]-[有效性]-[序列]14、 10列数据合计成一列=SUM(OFFSET($A$1,(ROW()-2)*10+1,,10,1))15、查找数据公式两个(基本查找函数为VLOOKUP,MATCH)1、根据符合行列两个条件查找对应结果=VLOOKUP（H1，A1：E7，MATCH（I1，A1：E1，0），FALSE）2、根据符合两列数据查找对应结果(为数组公式)=INDEX（C1：C7，MATCH（H1&I1,A1:A7&B1:B7,0））16、如何隐藏单元格中的0单元格格式自定义0;-0;;@ 或 选项》视图》零值去勾。呵呵，如果用公式就要看情况了。17、多个工作表的单元格合并计算=Sheet1!D4+Sheet2!D4+Sheet3!D4，更好的=SUM(Sheet1:Sheet3!D4)18、获得工作表名称1、定义名称：Name=GET.DOCUMENT(88)2、定义名称：Path=GET.DOCUMENT(2)3、在A1中输入=CELL(&filename&)得到路径级文件名在需要得到文件名的单元格输入=MID(A1,FIND(&*&,SUBSTITUTE(A1,&\&,&*&,LEN(A1)-LEN(SUBSTITUTE(A1,&\&,&&))))+1,LEN(A1))4、自定义函数Public Function name()Dim filename As Stringfilename = ActiveWorkbook.namename = filenameEnd Function19、这个在一个帖子中的我就整搬一下（自已的帖^_^）如何获取一个月的最大天数:&=DAY(DATE()-1)&或&=DAY(B1-1)&,B1为&数据区包含某一字符的项的总和,该用什么公式=sumif(a:a,&*&&&某一字符&&&*&,数据区)最后一行为文本：=offset($b$1,MATCH(CHAR(65535),b:b)-1,)最后一行为数字：=offset($b$1,MATCH(9.,b:b)-1,)或者：=lookup(2,1/(b1:b1000&&&&),b1:b1000)评委打分中，如何去掉两个以上最高分，两个以上最底分，求剩余人员的平均分？同时显示出被去掉的分数。看看trimmean()函数帮助。被去掉的分数：最大两个：=large(data,{1;2})最小两个：=small(data,{1;2})怎样很简单的判断最后一位是字母right(a1)*1出错的字母=IF(ISNUMBER(--RIGHT(A1,1)),&数字&,&字母&)=IF(ISERR(RIGHT(A1)*1),&字母&,&数字&)如何 设置单元格，令其不接受包含空格的字符选定A列数据——有效性——自定义——公式=iserror(find(& &,a1))数据－－有效性－－自定义－－公式=len(a1)=len(trim(a1))原来的函数是=a1+a4+a7+a10+a13+a16+a19+a22.....现在是=sum(n(offset(a1,(row(1:10)-1)*3,)))在一个工作表中引用其他工作表中的数据,但是被引用的工作表不是固定的,根据我输入的工作表名自动选择相应的工作表中的数据,请问在公式里怎样引用?=INDIRECT(&A1&&&!&&&E1&) A1为工作表名奇数行求和 =SUMPRODUCT((A1:A1000)*MOD(ROW(A1:A1000),2))偶数行求和 =SUMPRODUCT((A1:A1000)*NOT(MOD(ROW(A1:A1000),2)))查看字符串字数=LEN(A1)求非空单元格数量公式计算出来的数据，COUNTA不能用的（否则空字符也计算进去了）=COUNTIF($E$3:$E$65536,&?*&)动态求和公式,自A列A1单元格到当前行前面一行的单元格求和.=SUM(INDIRECT(&A1:A&&ROW()-1))20、比较好用的EXCEL文档修复工具ExcelRecovery21、EXCEL开方运算：将8开3次方，可以用这个公式，在单元格中输入=8^(1/3)22、单元格中的数据分散对齐文本格式》全角输入23、查找工作表中的链接Ctrl+~ 或编辑》链接24、如何让空单元格自动填为0选中需更改的区域》查找》空》替换》025、把Word里的数字转换到Excel方法有多种，选中》复制》设置输入单元格为文本》选择性粘贴》值选中》表格转换为文本》粘贴》分列》对分列选项设置为文本另存为文本文件》EXCEL中打开文本文件》对导入文本对话框进行对应设置26、如果隐藏了B列，如果让它显示出来？选中A到C列，点击右键，取消隐藏选中A到C列，双击选中任一列宽线或改变任一列宽将鼠标移到到AC列之间，等鼠标变为双竖线时拖动之。
来自：&&&《》
更多精彩，关注微信号：360doc
馆友评论（0）
您好，请&&或者&&后再进行评论
合作登录：
(window.slotbydup = window.slotbydup || []).push({
container: s,
id: 'u1442766',
scale: '20.3',
display: 'inlay-fix'求excel函数问题.用于股票手续费的计算？
用于股票的手续费的计算.x为单价Y为股数.手续费计算公式分以下三部份:1 2 3(x*y*0.0021)+(x*y*0.001)+(y*0.001)其中第一部份.如不足5元 则要求补足5元.超出5则照实算第三部份.如不足1元 则要求补足1元.超出1则照实算.要如何操作.用什么公式?
09-07-04 & 发布
假设你把单价x的数值放在A2单元格内，把股数Y的数值放在B2单元格内，在C2单元格内放你的股票手续费的计算值，则根据你的公式，C2单元格的内容如下：= IF(A2*B2*0.*B2*0.0021,5) + A2*B2*0.001 + IF(B2*0.001&=1,B2*0.001,1)只是用了一个IF函数。IF函数中第一个参数是判断条件，如果满足，就采用第二个参数（计算公式）为值，如果不满足，就采用第三个参数（计算公式）为值。
请登录后再发表评论!
非要使用公式的话，可以试一下ExcelHome上一位达人写的这个公式，个人感觉精练多了：=IF(LEN(A2)=15,REPLACE(A2,7,,19)&MID(&10X&,MOD(SUMPRODUCT(MID(REPLACE(A2,7,,19),ROW(INDIRECT(&1:17&)),1)*2^(18-ROW(INDIRECT(&1:17&)))),11)+1,1),A2)用我做的自定义函数吧：首先，按“Alt+F11”进入VBE；然后，点菜单“插入”－》“模块”；接着，复制如下代码至右侧编辑窗口：Option ExplicitFunction CheckID(IdStr) As String '身份证号码校验On Error GoTo ErrorHandle '设置错误处理Application.Volatile (False) '将函数标记为非易失性函数Dim wi As Variant, ji As Variant, sum%, i%, intMsg%, datBirthday As Datewi = Array(7, 9, 10, 5, 8, 4, 2, 1, 6, 3, 7, 9, 10, 5, 8, 4, 2)ji = Array(&1&, &0&, &X&, &9&, &8&, &7&, &6&, &5&, &4&, &3&, &2&)If Len(IdStr) = 15 Then If Not IsNumeric(IdStr) Then  Err.Raise vbObjectError + 1000, , &号码中有非法字符& End If IdStr = Left(IdStr, 6) & &19& & Right(IdStr, 9)ElseIf Len(IdStr) = 18 Then If Not (IsNumeric(Mid(IdStr, 1, 17)) And (IsNumeric(Right(IdStr, 1)) Or Right(IdStr, 1) = &X& Or Right(IdStr, 1) = &x&)) Then  Err.Raise vbObjectError + 1001, , &号码中有非法字符& End IfElseIf Len(IdStr) = 0 Then Exit FunctionElse  Err.Raise vbObjectError + 1002, , &号码不是15位或18位&End IfdatBirthday = DateValue(Mid(IdStr, 7, 4) & &-& & Mid(IdStr, 11, 2) & &-& & Mid(IdStr, 13, 2))sum = 0For i = 0 To UBound(wi) sum = sum + Mid(IdStr, i + 1, 1) * wi(i)Next iIf Len(IdStr) = 17 Then CheckID = IdStr & ji(sum Mod 11) '将15位的身份证转化成18位的号码 'CheckID = Left(IdStr, 6) & Right(IdStr, 9) '不对15位的号码升位Else If ji(sum Mod 11) && Right(IdStr, 1) Then  intMsg = MsgBox(&18位身份证号码中的校验码错误！& & vbCrLf & &您要输入的是：& & Left(IdStr, 17) & ji(sum Mod 11) & &吗？&, vbYesNo)  If intMsg = vbYes Then   CheckID = Left(IdStr, 17) & ji(sum Mod 11)  Else   Err.Raise vbObjectError + 1003, , &末位校验码有误&  End If Else  CheckID = IdStr End IfEnd IfExit FunctionErrorHandle: If Err.Number = 13 Then  CheckID = &号码中出生日期非法& Else  CheckID = Err.Description End If Exit FunctionEnd Function然后在文件中像使用普通函数一样调用这个函数就可以了，具体语法为： A B1 412 =CheckID(A1)详见附件：&BR/&附件：&a href=&/browse/download.php?path=/59/36/50/..xls&filename=身份证号码升位和验证.xls& target=&_blank&&身份证号码升位和验证.xls&/a&
请登录后再发表评论!SUMPRODUCT函数用法详解
扫扫二维码，随身浏览文档
手机或平板扫扫即可继续访问
SUMPRODUCT函数用法详解
举报该文档为侵权文档。
举报该文档含有违规或不良信息。
反馈该文档无法正常浏览。
举报该文档为重复文档。
推荐理由：
将文档分享至：
分享完整地址
文档地址：
粘贴到BBS或博客
flash地址：
支持嵌入FLASH地址的网站使用
html代码：
&embed src='/DocinViewer-4.swf' width='100%' height='600' type=application/x-shockwave-flash ALLOWFULLSCREEN='true' ALLOWSCRIPTACCESS='always'&&/embed&
450px*300px480px*400px650px*490px
支持嵌入HTML代码的网站使用
您的内容已经提交成功
您所提交的内容需要审核后才能发布，请您等待！
3秒自动关闭窗口问题补充&&
本页链接：
SUMPRODUCT(((A2;))*(B2;高级主任&)+(A2:A11=&主任":A11=":B11>
wu_zhenjiu &7-16 16:07
•回答
•回答
•回答
•回答
•回答
A11="=SUMPRODUCT(or((A2;))*(B2;高级主任&):A11=&主任":B11>,(A2
hbpaoxiao2&7-16 16:05
猜你感兴趣
服务声明： 信息来源于互联网，不保证内容的可靠性、真实性及准确性，仅供参考，版权归原作者所有！Copyright &
Powered byExcel2007函数公式实例集43-第5页
上亿文档资料，等你来发现
Excel2007函数公式实例集43-5
在具有合并单元格的A列产生自然数编号:=1+CO；引用合并区域时防止产生0值:=IF(A1&；计算10届运动会中有几次破纪；录:=SUMPRODUCT(N(SUBTOTAL；计第奎续三天之总产量大于等于25万元的次数:=S；进、出库合计查询:=SUM(OFFSET(A1,；根据人数自动调整表格大；小:{=IFERROR(OFFSET($E$1,；累计数
在具有合并单元格的A列产生自然数编号:=1+COUNT(OFFSET($A$2,,,ROW()-2,)) 引用合并区域时防止产生0值:=IF(A1&&&&,A1,OFFSET(B1,-1,)) 计算10届运动会中有几次破纪录:=SUMPRODUCT(N(SUBTOTAL(5,OFFSET(B2,,,ROW(2:10)))&SUBTOTAL(5,OFFSET($B$2,,,ROW(1:9))))) 计第奎续三天之总产量大于等于25万元的次数:=SUMPRODUCT(N(SUBTOTAL(9,OFFSET($B$1,ROW(1:10)-1,,3))&=25)) 进、出库合计查询:=SUM(OFFSET(A1,E2,MATCH(G2&&总计&,B1:C1,0),F2-E2+1)) 根据人数自动调整表格大小:{=IFERROR(OFFSET($E$1,SMALL(IF(F$2:F$5&=TRANSPOSE(ROW(INDIRECT(&1:&&MAX(F$2:F$5)))),ROW($2:$5)-1),ROW(1:1)),),&&)}累计数据:{=SUM(OFFSET(B$2,,,ROW()-1))} 计算至少两科不及格的学生人数:{=SUM(--(COUNTIF(OFFSET($B$1,ROW(2:11)-1,,,4),&&60&)&=2))} 列出成绩最好的科目:{=OFFSET(A2,,SUM((MAX(SUBTOTAL(9,OFFSET(A2,1,ROW(1:4),4)))=SUBTOTAL(9,OFFSET(A2,1,COLUMN(A:D),4)))*COLUMN(B:E))-1)} 计算及格率不超过50％的科目数:{=SUM(N(COUNTIF(OFFSET(A1,1,COLUMN(A:D),10,1),&&60&)&=ROWS(2:11)/2))} 罗列两次未打卡人员:{=IFERROR(OFFSET(A$1,LARGE((COUNTIF(OFFSET(A$1,ROW($2:$11)-1,1,,4),&×&)&=2)*ROW($2:$11),ROW(A1))-1,),&&)} 计算语文、英语、化学、政治哪科总分最高:=CHOOSE(MATCH(MAX(SUBTOTAL(9,OFFSET(A1,1,MATCH({&语文&,&英语&,&化学&,&政治&},$B$1:$G$1,0),10,))),SUBTOTAL(9,OFFSET(A1,1,MATCH({&语文&,&英语&,&化学&,&政治&},$B$1:$G$1,0),10,)),0),&语文&,&英语&,&化学&,&政治&) 连续三届达到100的次数:=SUMPRODUCT(N(COUNTIF(OFFSET(B1,ROW(2:9)-1,,3,1),&&=100&)=3)) 罗列及格率最高的学生姓名:{=INDEX(A:A,SMALL(IF(MAX(COUNTIF(OFFSET(A$1,ROW($2:$11)-1,1,1,COLUMNS(B:G)),&&=60&))=COUNTIF(OFFSET(A1,ROW($2:$11)-1,1,1,COLUMNS(B:G)),&&=60&),ROW($2:$11),12),ROW(A1)))&&&} 计算Excel类图书最多进货量及书名:{=MAX(SUMIF(OFFSET(B1,ROW(2:11)-1,1,1,6),&&=100&)*(B2:B11=&excel&))} 计算Excel类图书进货最多的是哪一个月:{=INDEX(C1:H1,MATCH(MAX(SUMIF(B2:B11,&excel&,OFFSET(C2,,COLUMN(C:H)-3,ROWS(2:11),1))),SUMIF(B2:B11,&excel&,OFFSET(C2,,COLUMN(C:H)-3,ROWS(2:11),1)),0))} 根据下拉列表中的时间和产品名计算销量冠军:{=INDEX(A2:A11,MATCH(MAX(OFFSET(C2,,MATCH(J2,C1:H1,0)-1,ROWS(2:11),)*(B2:B11=K2)),OFFSET(C2,,MATCH(J2,C1:H1,0)-1,ROWS(2:11),)*(B2:B11=K2),0))} 根据下拉列表中的产品提取姓名与销量:{=IFERROR(1/MOD(SMALL(IF(B2:B11=K1,1/SUBTOTAL(9,OFFSET(C2,ROW(2:11)-2,0,1,COLUMNS(C:H)))+ROW(2:11)),ROW(1:10)),1),&&)} 计算产量最高的季度:=TEXT(MATCH(MAX(SUBTOTAL(9,OFFSET(A1,{0,3,6,9},1,3))),SUBTOTAL(9,OFFSET(A1,{0,3,6,9},1,3)),0),&[DBNum1]0季度&)分栏打印:=IF(ROW()=1,CHOOSE(MOD(COLUMN()-1,3)+1,资料!$A$1,资料!$B$1,&&),IF(MOD(COLUMN(),3)=0,&&,OFFSET(资料!$A$1,INT(COLUMN()/3)*9+ROW()-1,MOD(COLUMN(),3)-1,))) 分类汇总:=IF(SUMIF(B$2:B$11,E2,C$2:C$11)=0,&&,SUMIF(B$2:B$11,E2,C$2:C$11)) 分类汇总并排序:{=OFFSET(B$1,RIGHT(LARGE(IF(MATCH(B$2:B$11,B$2:B$11,)=ROW($2:$11)-1,SUMIF(B$2:B$11,B$2:B$11,C$2:C$11)*1000+ROW($2:$11),ROWS($1:$11)+1),ROW(1:1)),3)-1,)&&&} 工资查询:{=IFERROR(OFFSET(D1,MATCH(F2&G2&H2,A2:A11&B2:B11&C2:C11,0),),G2&&无此人&)} 多表成绩查询:{=SUBTOTAL(9,OFFSET(INDIRECT(ADDRESS(1,MATCH(H1,1:1,0),1,1,{&一班&;&二班&;&三班&})),1,,ROWS(2:11),))}计算每个学生总分是否高于本班平均成绩:{=SUM(C2:E2)&AVERAGE(IF((A2=A$2:A$11),SUBTOTAL(9,OFFSET(B$1,ROW($2:$11)-1,1,,COLUMNS(C:E)))))} 计算每个学生进入前三名的科目总数:{=SUM(N((RANK(N(OFFSET($B$2,ROW()-2,COLUMN(B:F)-2,1,1)),OFFSET($B$2,0,COLUMN(B:F)-2,ROWS($2:$11),1)))&=3))}计算高于单科平均值的科目总数:{=SUM(N(N(OFFSET($B$2,ROW()-2,COLUMN(B:F)-2,1,1))&SUBTOTAL(1,OFFSET($B$2,0,COLUMN(B:F)-2,ROWS($2:$11),1))))}罗列平均成绩倒数三名的班级:{=OFFSET(A1,MATCH(SMALL(SUBTOTAL(1,OFFSET(A1,ROW($2:$9)-1,1,1,COLUMNS(B:F)))*1000+ROW(2:9),ROW(1:3)),SUBTOTAL(1,OFFSET(A1,ROW($2:$9)-1,1,1,COLUMNS(B:F)))*1000+ROW(2:9),),)} 将姓名重复三次:{=T(OFFSET(A$1,ROUNDUP(ROW(INDIRECT(&1:&&ROWS(A$2:A$5)*3))/3,0),))} 多表汇总金额:{=SUM(SUBTOTAL(6,OFFSET(INDIRECT({&华南区&,&华东区&,&华北区&}&&!B1:C1&),ROW(2:10)-1,)))} 从单价表引用单价并汇总金额:{=SUM((N(OFFSET(G1,MATCH(A2:A7,F2:F13,),)))*B2:B7)} 从单价表引用最新单价并汇总金额:{=SUM((N(OFFSET(F1,MATCH(A2:A7,D2:D13,)+(COUNTIF(D2:D13,A2:A7)-1),)))*B2:B7)}根据完工状况汇总工程款:{=SUM(SUBTOTAL(9,OFFSET(C1,ROW(2:11)-1,,1,2))*(E2:E11=G2))} 统计最后三天的平均销量:{=SUBTOTAL(1,OFFSET(INDIRECT(&B&&MAX((A:A&&&&)*ROW(1:1048576))),,,-3,1))} 重组培训科目表:姓名=LOOKUP(ROW()-1,COUNTIF(OFFSET(B$1:G$1,,,ROW($1:$7)),&&&&),A$2:A$8)&&&;科目=IFERROR(OFFSET(B$2,MATCH(H2,$A$2:$A$7,)-1,COUNTIF($H$2:H2,H2)-1),&&) 从多个产品相同单价的单价表中引用单价:=SUMPRODUCT(COUNTIF(OFFSET(A$2,ROW($2:$4)-2,0,1,4),G2)*E$2:E$4)*H2统计所有业务员销售利润并罗列排列榜:{=OFFSET(A1,MOD(LARGE(INT(SUBTOTAL(6,OFFSET(C2,ROW(C2:C11)-2,,,3)))*1000+ROW(2:11),ROW(2:11)-1),1000)-1,)}按季度引用不同价格并统计金额与累计:{=IF(A2&&&累计&,LOOKUP(COUNTIF(OFFSET(A$1,1,0,ROWS($2:2),),&合计&)+1,ROW($2:$5)-1,F$2:F$5)*B2,SUM(C1:C$2*(A1:A$2&&&累计&)))} 计算10个月中的销售利润并排名:{=OFFSET(A1,MOD(LARGE(INT(MMULT(SUBTOTAL(6,OFFSET(INDIRECT({&华东区&,&华南区&,&华北区&,&华中区&,&西南区&}&&!A1&),ROW(2:11)-1,1,1,3)),{1;1;1;1;1}))*1000+ROW(2:11),ROW(1:10)),1000)-1,)} 计算五个地区销售利润:{=TRANSPOSE(MMULT({1,1,1,1,1,1,1,1,1,1},SUBTOTAL(6,OFFSET(INDIRECT({&华东区&,&华南区&,&华北区&,&华中区&,&西南区&}&&!A1&),ROW(西南区!$2:$11)-1,1,1,3)))*1000+ROW(2:11))} 计算第几轮销量最高以及售货员姓名:{=OFFSET(A1,RIGHT(MAX(SUBTOTAL(9,OFFSET(D1,5*(ROW(INDIRECT(&1:&&CEILING(COUNTA(C:C)/5,1)))-1),,5))*10+ROW(INDIRECT(&1:&&CEILING(COUNTA(C:C)/5,1)))))*5-1,)} 提取组名及计算每组平均达标率:{=TEXT(SUBTOTAL(1,OFFSET(B1,((ROW(1:4))*2-1),,,8)),&0.00%&)} 判断是否超过一半人达标率在90%以上:{=COUNTIF(OFFSET(B1,((ROW(1:4))*2-1),,,8),&&=0.9&)&COLUMNS(B:I)/2} 分别计算每个班第一名的成绩和姓名:名次{=MAX(SUBTOTAL(9,OFFSET(B$1,ROW($2:$31)-1,1,,COLUMNS(C:I)))*(B$2:B$31=K2))};名{=OFFSET(A$1,MOD(MAX((SUBTOTAL(9,OFFSET(B$1,ROW($2:$31)-1,1,,COLUMNS(C:I)))*1000+ROW($2:$31))*(B$2:B$31=K2)),1000)-1,)} 计算哪一个月完成目标:=OFFSET(A1,LOOKUP(,1*(SUBTOTAL(9,OFFSET(B1,1,0,ROW(2:12)-1))&=200),ROW(2:12)),) 有几次连续三个月的平均值低于整体平均值:{=SUM(N((SUBTOTAL(9,OFFSET(B4,ROW(2:11)-2,,3,2))/3&AVERAGE(SUBTOTAL(9,OFFSET(B4,ROW(2:11)-2,,,2))))))} 计算10个月中的销售利润并排名:{=OFFSET(A1,MOD(LARGE(INT(MMULT(SUBTOTAL(6,OFFSET(INDIRECT({&华东区&,&华南区&,&华北区&,&华中区&,&西南区&}&&!A1&),ROW(2:11)-1,1,1,3)),{1;1;1;1;1}))*1000+ROW(2:11),ROW(1:10)),1000)-1,)} 将表格转置方向:{=TRANSPOSE(A1:E5)} 对组数进行排名:{=MMULT(N(B2:B11*(IF(LEFT(C2:C11)=&万&,10000,1))&TRANSPOSE(B2:B11*(IF(LEFT(C2:C11)=&万&,10000,1)))),ROW(2:11)^0)+1} 区分大小写提取产品单价:{=MMULT((EXACT(B2:B11,TRANSPOSE(单价表!A2:A5)))*TRANSPOSE(单价表!B2:B5),{1;1;1;1})}区分大小写查单价且统计三组总金额:{=MMULT(TRANSPOSE(SUBTOTAL(9,OFFSET(B1,ROW(2:11)-1,1,,5))*MMULT((EXACT(B2:B11,TRANSPOSE(单价表!A2:A5)))*TRANSPOSE(单价表!B2:B5),{1;1;1;1})),1*(A2:A11={&A组&,&B组&,&C组&}))} 引用销售金额高于200次数最多者:{=INDEX(A:A,RIGHT(MAX(MMULT((B2:H9&200)*1,TRANSPOSE(COLUMN(B:H)^0))*10+ROW(2:9))))} 根据评委评分和权重分配统计最后得分:{=SUM(B2:F8*(A2:A8=B10)*TRANSPOSE(I2:I6))} 罗列选手得分前三名的姓名:{=OFFSET($A1,RIGHT(LARGE(MMULT($B2:$F8*TRANSPOSE($I2:$I6),TRANSPOSE(COLUMN($B:$F)^0))*10^6+ROW(2:8),COLUMN(A1)),2)-1,,)} 根据字母评语转换得分:{=MMULT(TRANSPOSE(评语换算得分!A$2:A$11=TRANSPOSE(E2:E11))*1,评语换算得分!B$2:B$11)+SUBTOTAL(9,OFFSET(B2,ROW(2:11)-2,,,COLUMNS(B:D)))} 多列、隔行数据汇总:{=SUM(MMULT(D2:G11,TRANSPOSE(COLUMN(D:G)^0))*(A2:A11=&赵还珠&))} 计算犯规低于3次的人数:{=SUM(N(MMULT(--(B2:B21=TRANSPOSE(B2:B21)),ROW(2:21)^0)={1,2})/{1,2})} 提取姓名:=INDEX(B:B,ROW()*2)&&& 从电话簿中选择性引用数据:=INDEX($A:$B,ROW(A1)*3-2,COLUMN(A:A)) 消除厂牌打印资料照片行:{=INDEX(A:A,SMALL(IF(MOD(ROW($1:$12),3)&0,ROW($1:$12),1048576),ROW(A1)))&&&} 罗列优秀员工:{=INDEX(A:A,MOD(SMALL(B$2:B$11*100+ROW($2:$11),ROW(8:8)),100))} 插入空行分割数据:=IF(MOD(ROW(),3)&0,INDEX(A:A,ROW(A2)*2/3),&&) 仅仅提取通讯录中四分之三信息:=INDEX(A:B,ROW(A2)*2/3,(MOD(ROW(A3),3)+1)/3+1) 罗列12月中产量倒数第一名次数最多者名单:{=INDEX(B:B,SMALL(IF((COUNTIF(B$2:B$13,B$2:B$13)=MAX(COUNTIF($B$2:$B$13,$B$2:$B$13)))*(MATCH($B$2:$B$13,$B$2:$B$13,0)=ROW($2:$13)-1),ROW($2:$13),1048576),ROW(A1)))&&&} 按投诉次数升序排列客服姓名:{=INDEX(B:B,MOD(SMALL(IF(MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1,COUNTIF(B$2:B$12,B$2:B$12)*10^5+IF(MATCH(B$2:B$12,B$2:B$12,)=ROW($2:$12)-1,ROW($2:$12),99999),ROW(A1)),10^5))&&&} 计算60分到95分之间的人员个数:=INDEX(FREQUENCY(B2:B11,{60,95}),2) 罗列导致产品不良的主因:{=IFERROR(T(INDEX($A:$A,SMALL(IF($B$2:$B$11=LARGE(IF(FREQUENCY($B$2:$B$11,$B$2:$B$11),$B$2:$B$11),ROW(A1)),ROW($2:$11)),COLUMN(A1)))),&&)} 按身高对学生排列座次表:{=INDEX($A:$A,MOD(SMALL($C$2:$C$49*1000+ROW($2:$49),(ROW(A1)-1)*6+MOD(COLUMN(A1)-1,6)+1),1000))} 重组教师授课表:{=INDEX(班级!$A:$A,SMALL(IF(班级!$B$2:$D$11=$A3,ROW($2:$11),1048576),COLUMN(C$1)))&&&} 提取三个不规则区域的交集:{=INDEX($B:$B,SMALL(IF(COUNTIF(C组!$B$2:$I$2,$B$2:$B$9)*COUNTIF(B组!$C$2:$D$4,$B$2:$B$9),ROW($B$2:$B$9),10),ROW(A4)))&&&} 不区分大小写查找单价:=VLOOKUP(B2,单价表!A$2:C$11,3,0)*C2 乱序资料表中查找多个项目:=VLOOKUP($B2,单价表!$A$2:$E$11,MATCH(C$1,单价表!$A$1:$E$1,0),0) 将得分转换成等级:=VLOOKUP(B2,{0,&D&;60,&C&;80,&B&;90,&A&},2) 查找美元与人民币报价:=VLOOKUP(B2,INDIRECT(E2&&报价!A2:B9&),2,0) 多条件查找:{=VLOOKUP(A2&B2&C2,IF({1,0},资料表!A2:A11&资料表!B2:B11&资料表!D2:D11,资料表!C2:C11),2,0)} 查找最后更新单价:{=VLOOKUP(10^16,--LEFT(VLOOKUP(B2,单价表!A:Z,COUNTA(INDIRECT(&单价表!A&&MATCH(B2,单价表!A:A,0)&&:Z&&MATCH(B2,单价表!A:A,0))),0),ROW($1:$16)),1)} 查找双列信息:{=VLOOKUP(A9,CHOOSE({3,2,1},A1:A6&B1:B6,C1:C6&D1:D6,E1:E6&F1:F6),{2,3},)} 提取姓名拼音的首字母:=VLOOKUP(LEFT(A2),拼音,2)&VLOOKUP(MID(A2,2,1),拼音,2)&VLOOKUP(MID(A2,3,1),拼音,2) 用不确定条件查找:{=VLOOKUP(A2&&&,IF({1,0},IF(COUNTIF(资料表!A2:A10,A2)=0,资料表!B2:B10,资料表!A2:A10),资料表!E2:E10),2,0)} 按学历对姓名排序:{=VLOOKUP(MOD(SMALL(MATCH(B$2:B$10,{&大学&;&高中&;&初中&;&小学&},0)*1000+ROW($2:$10),ROW(A1)),1000),IF({1,0},ROW($2:$10),A$2:A$10),2,0)} 使用通配符进行查找:{=VLOOKUP(&*&&A2&&*&,IF({1,0},资料表!B$2:B$9,资料表!A$2:A$9),2,0)} 多工作表查找最大值:{=TEXT(VLOOKUP(MAX(SUBTOTAL(9,INDIRECT(TEXT(ROW(1:6),&[DBNum1]&)&&年级!B&&MATCH(D2,A:A,0)))),IF({1,0},SUBTOTAL(9,INDIRECT(TEXT(ROW(1:6),&[DBNum1]&)&&年级!B&&MATCH(D2,A:A,0))),ROW(1:6)),2,0),&[DBNum1]&)} 对带有合并单元格的区域查找年假天数:=VLOOKUP(F2,OFFSET(B2,MATCH(E2,A2:A13,0)-1,,4,2),2) 查找某业务员在某季度的销量:=HLOOKUP(G2,A1:E9,MATCH(H2,A:A,0),0) 在同一行查找数据:{=HLOOKUP(MAX(A2:H2),IF({1;0},B2:H2,A2:G2),2,FALSE)} 计算两个产品不同时期的单价:=HLOOKUP(MONTH(A2),IF(B2=&塑胶机&,{0,3,8;25,19,18},{0,5,10;12.5,10,11}),2) 多条件计算加班费:=TEXT(HOUR(B2)+HLOOKUP(MINUTE(B2),{0,20.1;0,0.5,1},2),&[&2]6;5&)*HOUR(B2)+HLOOKUP(MINUTE(B2),{0,20.1;0,0.5,1},2) 根据进厂日期计算有薪假天数:=HLOOKUP(DATEDIF(B2,TODAY(),&y&),{0,1,3,5,7,10;0,2,3,5,7,10},2) 制作准考证:=HLOOKUP(B2,学生档案库!$1:$11,ROUNDUP(COLUMN()/5,0)+1+INT(ROW()/7)*2,FALSE) 不区分大小写判断两列相同数据个数:{=COUNT(MATCH(A2:A11,B2:B11,0))} 按汉字评语进行排序:{=INDEX(A:B,MOD(SMALL(MATCH($B$2:$B$12,排名标准!$A$2:$A$9,)*100+ROW($B$2:$B$12),ROW(2:12)-1),100),{1,2})} 提取A列最后一个数据:{=INDIRECT(&A&&(MATCH(1,0/(A:A&&&&))))} 提取字符串中的汉字:{=MID(A2,MATCH(1,1/(MID(A2,ROW($1:$99),1)&=&啊&),),SUM(MATCH({1,2},1/(MID(A2,ROW($1:$99),1)&=&啊&),{0,1})*{-1,1})+1)} 将文件号中的中文大写转小写:{=&第&&TEXT(SUM((MATCH(MID(A2,{2,3,4},1),TEXT(ROW($1:$10)-1,&[DBNum2]&),0)-1)*{100,10,1}),&000&)&&号文件&} 计算补课科目总数:{=COUNT(0/(MATCH(B2:B8,B2:B8,0)=ROW(2:8)-1))} 产生混合编号:=TEXT(COUNTIF(C$1:C1,&*&),&[DBNum2]&)&TEXT(ROW()-MATCH(&々&,C$1:C1),&(000);;&) 提取迟到次数最多者姓名:=INDEX(B2:B11,MODE(MATCH(B$2:B$11,B$2:B$11,0))) 罗列多次迟到者姓名:{=IFERROR(INDEX(B$2:B$11,MODE(IF(COUNTIF(D$1:D1,B$2:B$11)=0,MATCH(B$2:B$11,B$2:B$11,0)))),&&)}区分、不区分大小写统计字符个数:{=COUNT(0/(MATCH(MID(A2,ROW($1:$100),1),MID(A2,ROW($1:$100),1),0)=ROW($1:$100)))-1}按金、银、铜牌排名次:{=MATCH(B2:B11+C2:C11%+D2:D11%%,LARGE(B2:B11+C2:C11%+D2:D11%%,ROW(2:11)-1),0)}包含各类专业文献、文学作品欣赏、中学教育、幼儿教育、小学教育、各类资格考试、外语学习资料、Excel2007函数公式实例集43等内容。
Excel2007函数公式实例集Excel2007函数公式实例集隐藏&& Excel2007 函数公式收集了 688 个实例,涉及到 137 个函数、7 个行业、41 类用途,为大家 提供一个参考,拓... Excel2007函数公式实例集_PPT制作技巧_PPT专区。Excel2007函数公式实例集Excel2007 函数公式实例集.txt 我是天使,回不去天堂是因为体重的原因。别人装处,我只 好装... Excel2007 函数公式收集了 688 个实例,涉及到 137 个函数、7 个行业、41 类用途,为大家提供一个参考,拓展思路的机会。 公式由{}包括的为数组公式,在复制粘贴... Excel2007 函数公式收集了 688 个实例,涉及到 137 个函数、7 个行业、41 类用途,为大家提供一个参考,拓展思路的机会。公式由{}包括的为数组公式, 在复制粘贴... Excel 函数公式实例集 Excel2007 函数公式收集了 688 个实例,涉及到 137 个函数、7 个行业、 41 类用途,为大家提供一个参考,拓展思路的机会。公式由{}包括的... Excel 2007公式与函数实例详解_IT/计算机_专业资料。很实用的Excel函数公式,留着备用Excel 2007 公式与函数实例详解 1、使用 IF 函数判断员工是否完成任务: 、 函数... 例如,函数“AND(8=4*2,9&6,3*6&4^2)”先计算出算术运算的结果值,再进...Excel2007函数公式实例集... 80页 免费 excel2007技巧之5公式和... 38页 免费... 3 目录编辑本段|回到顶部版权信息 书名: Excel2007 函数与公式应用大全 作者:...Excel2007函数公式实例集... 44页 免费 EXCEL2007实战技巧(公式... 55页 免费... Excel2007 公式和函数中的运算符运算符是对于元素进行运算而规定的特殊符号。 一...Excel2007函数公式实例集... 44页 免费 Excel2007函数公式689个... 32页 免费...
