《2022年excel教程及实例 .pdf》由会员分享，可在线阅读，更多相关《2022年excel教程及实例 .pdf（31页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、1 查找 Excel 工作表中的重复数据前不久，一位网友求教，要求找出Excel 工作表中的重复数据并在单元格中列出。我给出了一个数组公式供参考，但不是太符合要求，因为这个数组公式虽然找出了重复数据，但是如果将数组公式向下复制时超出了出现重复数据的数量，会在相应单元格中显示错误。不久，这位朋友获得了更好的一个公式。这个公式非常好，很好地解决了这类问题，因此，我将其转贴于此，供有兴趣的朋友参考。先看看下图：在列 A 和列 B中存在一系列数据（表中只是示例，可能数据还有很多），要求找出某人 （即列 A 中的姓名） 所对应的所有培训记录（即列B中的数据）。也就是说，在单元格E1 中输入某人的姓名后，
2、下面会自动显示这个人所有的培训记录。我们知道，Excel 的 LOOKUP 系列的函数能够很方便地实现查找，但是对于查找后返回一系列的结果，这类函数无能为力，因此只能联合其它函数来实现。这里，在方法一中使用了INDEX函数、 SMALL 函数、 IF 函数和ROW 函数，在方法二中还使用了Excel 2007中新增的 IFERROR函数。方法一：1、选择单元格E3；2、输入公式：=INDEX(B:B,SMALL(IF($A$2:$A$25=$E$1,ROW($A$2:$A$25),65536),ROW(1:1) & ，然后同时按下Ctrl+Shift+Enter键，即输入数组公式。3、选择单元
3、格E3后下拉至所有单元格。方法二：1、选择单元格F3；2、输入公式：=IFERROR(INDEX($A$2:$B$9,SMALL(IF($A$2:$A$9=$E$1,ROW($A$2:$A$9)-ROW($A$2)+1,ROW($A$9)+1),ROW(1:1),2), )，然后同时按下Ctrl+Shift+Enter键，即输入数组公式。3、选择单元格F3 后下拉至所有单元格。Excel 的条件格式技术条件格式可以在很大程度上改进电子表格的设计和可读性，允许指定多个条件来确定单元格的行为，根据单元格的内容自动地应用单元格的格式。可以设定多个条件，但Excel 只会应用一个条件所对应的格式，即按
4、顺序测试条件，如果该单元格满足某条件，则应用相应的格式规则，而忽略其他条件测试。（以下内容适合于Excel 2003及其以前版本）在使用条件格式时，首先选择要应用条件格式的单元格或单元格区域， 然后单击菜单 “格式条件格式” ，出现如图 1 所示的“条件格式”对话框。在Excel 2003 及以前的版本中，条件格式最多只能设置三个条件。图 1：选择“单元格数值”时的条件格式对话框此时，若在第一个组合框中选择“单元格数值” ，则右侧的组合框中将提供“介于” 、 “未介于”、 “等于”、 “不等于”、 “大于”、“小于”、 “大于或等于”、 “小于或等于”等选项，并且在其右侧的输入框中可以输入相应
5、的数值，也可以选择工作表中的单元格。然后，单击“格式”按钮，设置当条件为真时所应用的格式。其中，选择“介于”时，包括设置的最大值和最小值，而选择“未介于”时，不包括设置的最大值和最小值。若在第一个组合框中选择 “公式”，则“条件格式” 对话框如图2 所示。图 2：选择“公式”时的条件格式对话框此时，可在右侧的输入框中输入公式或者选择含有公式的单元格。注意， 公式的值必须返回True 或 False 。当公式返回 True时，将应用条件格式；否则，不会应用设定的格式。这也从另一个侧面可以看出，对Excel 公式与函数掌握的熟练程度，有助于灵活运用条件格式。换句话说， 正是由于公式的强大功能，才使
6、得条件格式才能发挥其真正的魅力。关于条件格式，请注意下面的几点：（1） 复制单元格并将其粘贴到包含条件格式的单元格或者单元格区域中，将会删除该单元格或单元格区域中的条件格式，Excel不会给您任何警告信息。如果非得使用粘贴，并且要保留条件格式，那么可使用“选择性粘贴”功能。（2）当复制一个包含条件格式的单元格时，将同时复制该单元格的条件格式。在包含条件格式的单元格区域中插入行或者列时，在新的单元格中将有相同的条件格式。（3）如果要删除条件格式，仅在含有条件格式的单元格中按Delete 键，不会删除条件格式。 要删除条件格式， 需要使用“编辑清除格式”命令或者“编辑清除全部”命令。还可以使用“条
7、件格式”对话框，删除其中的条件。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 31 页 - - - - - - - - - 2 （4）复制某单元格到含有条件格式的单元格中，也将清除条件格式。（5） 要快速查看所有包含条件格式的单元格，使用 Excel 的 “定位”对话框，单击“定位条件”按钮，在“定位条件”中选择“条件格式”选项，如图3 所示。图 3：利用“定位条件”中的“条件格式”选项选择所有包含条件格式的单元格下面是 Escape From Excel Hell一书
8、中关于条件格式的两个示例，供参考。示例 1：在条件格式中使用公式且公式引用另一个单元格中的内容，如图4 所示。图 4：条件公式中引用另一单元格中的内容对单元格区域C6：D20应用条件格式，三个条件公式的含义分别为，其左侧B 列相应单元格的值若大于且等于1，则单元格底纹为粉红色且字体加粗；若大于 0，则单元格底纹为靛蓝色；若小于且等于0，则单元格底纹为浅灰色且字体为斜体。示例 2：在条件中使用其他工作表或工作簿中的内容在应用条件格式时，通常不能直接引用其他工作表或工作簿，但如果为需要引用的单元格区域定义了名称，那么可以在条件中通过名称来引用其他工作表或工作簿中的内容。如下图5 所示。图 5：通过
9、定义名称来在条件格式中引用另一工作表中的数据如图 5， 在工作表 SearchAnExternalWorksheet中内容为“Trans 01”的单元格中应用了条件格式，在条件公式中引用了另一个工作表 LookupTable 中名称为 MyLookupTable 的单元格区域中的内容， 如果相应的收益 （损失） 值大于 1000 则该单元格内容使用绿底斜体字显示，若小于-1000则该单元格内容使用红底粗体字显示。还有一种方法来引用其他工作表中的单元格。例如在需要使用条件格式的工作表中的某单元格中输入公式来引用其他工作表中的单元格，然后在条件格式中引用此单元格。下面借用自已在学习中看到的关于条件
10、格式的一些示例，对条件格式作进一步详细的介绍。示例 1：标识包含文本的单元格如图 6 所示，在单元格区域B2：C4中包含数字和文本，现在要对该区域中的文本应用特定的格式，以标识包含文本的单元格。选择 B2：C4，单击“格式条件格式”，在“条件格式”对话 框 最 左 侧 的 组 合 框 中 选 择 “ 公 式 ” ， 在 右 侧 输 入 公 式“=ISTEXT(B2)” ，单击“格式”，设置字体为粗体， 底纹为灰色。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 31 页
11、- - - - - - - - - 3 图 6：对包含文本的单元格应用格式如果要对不同的单元格区域应用该条件格式，那么 ISTEXT函数的参数应该是该区域左上角的单元格。示例 2：标识两个列表中不相同的数据有时，要比较两个列表区域中的不同数据，并将该数据所在的单元格标识出来。 如图 7 所示，有两个列表区域A1：B12 和 D1 ：E12，现在要求找出区域D1：E12 中与区域 A1：B12有不同数据的单元格并在该单元格中标记为红色底纹。图 7：需要比较的两个列表区域现在，按下列步骤来完成上述要求：第 1 步：将单元格区域A2：B12 命名为 OldData 。第 2 步：选择单元格区域D2：
12、E12，单击菜单“格式条件格式”。第 3 步：在“条件格式”对话框中，在“条件”组合框中选择“公式”，在右侧框中输入公式“=COUNTIF(OldData,D2)=0 ” 。单击“格式”按钮，在弹出的对话框中选择图案为红色，如图8 所示。图 8：设置条件格式第 4 步：单击“确定”按钮，此时如图9 所示，该列表中与左侧列表含有不同的数据的单元格被标上红色底纹。图 9：条件格式应用后，标识出不同的单元格有时，可能列表在不同的单元格，如图 10 所示， 要找出工作表Sheet2 中与区域 OldData 中不同的数据单元格。与上述步骤3相同，选择工作表Sheet2 中的单元格区域A2:B12，调出
13、“条件格式”对话框，并作如图10 所示的设置。图 10：选择区域A2：B12，设置条件格式单击“确定”按钮后，效果如图11 所示，标识出了与工作表Sheet1 中区域 OldData 不同的数据。 这也充分展示了定义名称名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 31 页 - - - - - - - - - 4 的好处。图 11：应用条件格式后， 为与 OldData 区域不同的数据单元格标上底纹当然，本示例只是演示，数据很少。如果要比较大量的数据，光凭人工查找核对，
14、费时费力且易出错，通过应用条件格式，将能够很快达到要求。本文的内容：近期在进行造价工程师考试的复习，其中有许多内容涉及到关于进度图表的，联想到Excel 很方便制作这样的图表。这里，先使用条件格式来简单制作这样的图表，以后的文章将有专门使用Excel 图表技术制作进度图（甘特图）的内容。示例 3：绘制进度图表灵活使用 Excel 的条件格式，可以绘制简单的工作进度图表。例如，要绘制如图12 所示的进度图， 其中开始和结束分别代表项目开始和结束的周。图 12：完成后的进度图效果。在项目之间增加了空行，使图示更清晰。完成图 12 的步骤如下：第 1 步：在工作表中输入数据，并进行相应的格式化操作。
15、第 2 步：选择单元格区域D3：O15 ，单击“格式条件格式”。第 3 步：在“条件格式”对话框中选择“公式”并输入下列公式：=AND(D$2=$B3,D$2=$C3) 然后，单击“格式”按钮，应用相应的格式，如图13 所示。图 13：设置条件格式单击“确定”按钮后，完成最终的进度图如图12 所示。此时，如果在工作表中更改开始和结束时间，进度条会相应的更改。如果需要为不同项目的进度条应用不同的格式或颜色，则需要使用 Excel 2007 。在 Excel 2007中提供了更为丰富的条件格式选择和更优美的界面，我们将详细探讨。这里，利用Excel 2007，为图 12 所示的进度图制作更丰富的效
16、果。最终效果如图14 所示。图 14：设置具有不同颜色的进度条完成图 14 的步骤如下：第 1 步：在 Excel 2007 工作表中输入数据，并进行相应的格式化操作。第 2 步：选择单元格区域D3 ：O3 。在“开始”选项卡中选择 “条件格式管理规则”，打开“条件格式规则管理器”对话框。单击“新建规则”按钮，打开“新建格式规则”对话框。选择“使用公式确定要设置格式的单元格”，在“为符合此公式的值设置格式”中输入公式：=AND($B3=D$2) 单击“格式”按钮，在“填充”选项卡中选择红色，单击“确定”按钮关闭“设置单元格格式”对话框。单击“确定”按钮关闭“新建格式规则”对话框，此时的对话框如
17、图15 所示。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 31 页 - - - - - - - - - 5 图 15：设置规则第 3 步：对单元格区域D5：O5、D7：O7 、D9 ：O9 、D11 ：O11、D13：O13、D15：O15分别重复第2 步的操作。但是，在输入公式时要针对具体的行进行修改，并对格式填充颜色进行相应的更改。完成后的进度图如图14 所示。在 EXCL中怎样将数值转换为中文习惯的大写？问题：在 EXCL中某一个单元格中输入带小数点的数字（如：
18、123.45 ）然后将该单元格格式设置为：数字分类特殊中文大写数字，得到的结果是该单元格的小数更改为：壹佰贰拾叁.肆伍， 而非中文大写习惯的：壹佰贰拾叁元肆角伍分，如果数值没有角分位还较易解决，只须将单元格格式设置自定义在后面加上“元正”即可，但如果数值中有角位、分位就没有办法解决了！有没有解决的方式？解答：用下面的公式就可以了。=IF(ROUND(A14,2)0, 无效数值 ,IF(ROUND(A14,2)=0,零,IF(ROUND(A14,2)1,TEXT(INT(ROUND(A14,2),dbnum2)&元)&IF(INT(ROUND(A14,2)*10)-INT(ROUND(A14,2
19、)*10=0,IF(INT(ROUND(A14,2)*(INT(ROUND(A14,2)*100)-INT(ROUND(A14,2)*10)*10)=0,零),TEXT(INT(ROUND(A14,2)*10)-INT(ROUND(A14,2)*10,dbnum2)& 角)&IF(INT(ROUND(A14,2)*100)-INT(ROUND(A14,2)*10)*10)=0, 整,TEXT(INT(ROUND(A14,2)*100)-INT(ROUND(A14,2)*10)*10),dbnum2)&分) 如何从列表中找出满足多个条件的项目？问题：单元格区域A2：C7包含一系列项目以及相应的颜
20、色和价格，现在想找出价格小于$200 的第一个蓝色(Blue) 项目的名字？解答：在数组公式中使用INDEX函数和 MATCH 函数，如下所示：=INDEX(A2:A7,MATCH(1,(B2:B7=D2)*(C2:C7 D3) A B C D E (代表列标 ) 1 Item Color Price Criteria Result 2 Shoes Red $140 Blue Shoes 3 Jacket Brown $300 $200 4 Shoes Blue $199 5 T-Shirt Green $10 6 Jeans Blue $150 7 Shoes Black $99 在单元格
21、E2中的数组公式语法为: =INDEX(A2:A7,MATCH(1,(B2:B7=D2)*(C2:C7=50)-countif(a1:a9,60) 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 21 页，共 31 页 - - - - - - - - - 22 =SUM(a1:a950) 为什么 =SUM(a1:a950)可以求出符合条件的个数，按理后面应再加一待求和区域如B1:B9 等等，不知数组计算的内部机制是怎样的，不知谁能给个解释, 谢谢！这个数组公式是这样运算的： a1:a
22、9区域的数值分别与60比较。假如a1:a9 的数依次为15,25,35,45,55,65,75,85,95.那么(a1:a950) 返回false,false,false,false,true,true,true,true,true。然后(a1:a950)则返回false,false,false,false,true,false,false,false,false。 因为 * 表示 and 的意思。只有当 and 的对象都为真时结果才为真。excel 里用 1 表示 true;0表示 false。有时需要将true或 false转换成数值，可以*1 或+0。=SUMPRODUCT (a1:a9
23、50) 几个工作表如何生成一个打印文件一次打印？解答：先按 Ctrl键 ，再点击其它要一起打印的工作表标签，就会成为一个群组, 打印的页数可延续到下一个Sheet。如何自动计算应收款滞纳金？要求在给定的应收日期、滞纳金率、当前日期（自动取）的基础上自动计算出应收滞纳金。解答： =(DATEDIF(应收日期 ,NOW(),d)*滞纳金率（每天）*应收金额SUM 函数问题如何用函数计算出下面几个数据：A B 234 230 12 10 13 1、A和 B都有数据且AB的个数2、A栏在相应的B栏有数据的情况下的数据总和，例如A1对应 B1有数据， A3对应 B3有数据，则求A1+A3的和。希望都能用
24、一个函数求出所要的结果。解答：=SUM($A$1:$A$10)*($B$1:$B$10)*($A$1:$A$10$B$1:$B$10) =SUM($A$1:$A$10)*($B$1:$B$10)*($A$1:$A$10$B$1:$B$10)*($A$1:$A$10+$B$1:$B$10) 以上为数组公式，输完后要按ctrl+shift+enter键。工作表中的小写字母都变成大写：解答：请运行以下程序：( 测试通过 ) Sub ConvertToUpperCase() Dim Rng As Range Worksheets(Sheet1).UsedRange.Select For Each Rn
25、g In Selection.Cells If Rng.HasFormula = False Then Rng.value = UCase(Rng.value) End If Next Rng End Sub 如何用 COUNTIF 计算整个 B列中含有“ F”这个字符的单元格的个数。c1=COUNTIF(b1:b130,b129) ， （b129 单元格内的字符是 “ F”），问题是随后在向表格中添加新行131、132、133.之后， c1单元格中公式统计的结果还是1-129 行的， 怎么才能让c1 中实时显示添加新数据后的统计结果? 解答： c1=COUNTIF(b:b,b129) 在 E
26、XCEL 表里建立唯一索引在该列输入重复的数值后自动提示输入错误解答： 1、选定你要限制输入的列（假设是A2:A20），选菜单的“数据”“有效性”；2、在许可条件中选定“自定义”，在公式一拦中，输入你要求的限制，例如：“ =COUNTIF($A$2:$A$20,A2)=1 ”。3、你还可以在 “输入信息” 和“出错信息” 输入一些提示信息。不过，你一定要注意！这个功能只能在你从键盘上键入数据时有效！拷贝和粘贴数据的操作是无效的。统计包含某个字符的个数我想编的公式是： a/84 - （b4） 。其中 a 是一个数值，小于或等于84；b 是包含字符 C 的单元格的个数；C是一个符号。这个公式的关键
27、是要统计出包含字符C的单元格的个数。解答：方法1：=a/(84-countif(b,=c)*4) 问题：我试了一下，不能运行，我想是因为没有指定出现“c”的单元格的范围。比如说 “c”在 D2 D30中随机出现， 在上述公式中要先统计出出现 “c” 的单元格的个数。 这个公式如何做？解答： =a/(84-COUNTIF(D3:D30,c)*4) 如何用 if函数判断 , 如果 a1 单元格大于0,b1 单元格为 0 是错误, 为非 0 是正确解答： 1：if(and(a0,if(b1=0,错误 , 正确 ),条件一不满足 ) 我改进以下一个验证先进先出法公式名师资料总结 - - -精品资料欢迎
28、下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 22 页，共 31 页 - - - - - - - - - 23 =IF(B3C1,IF(SUM(B3:B4)C1,IF(SUM(B3:B5)C1,IF(SUM(B3:B6)B1,IF(SUM(B3:B7)C1,out of range,(B3*C3+B4*C4+B5*C5+B6*C6+(C1-SUM(B3:B6)*C7)/C1),(B3*C3+B4*C4+B5*C5+(C1-SUM(B3:B5)*C6)/C1),(B3*C3+B4*C4+(C1-SUM(B3:B4)*C
29、5)/C1),(B3*C3+(C1-B3)*C4)/C1),C4) B3至 B7为输入数量 ,C3 至 C7为输入单价 ,C1 为输入结余数量,E1 为输出平均单价, 即上面公式解答：公式太复杂，而且受限制。把 A列利用起来， a3=SUM(B3:$B$7)，拖至 7 行（若为 X行，则=SUM(B3:$B$X ，下拖 ) ，A列数据为先进先出的累计数量（待Match 的结存数量）， =MATCH(C1,A3:A7,-1) 找到结存批次。然后计算结存平均单价及发出平均单价。有无简结一点的公式求如:a1*b1+a2*b2+b3*b3.的和解答：在 B4中输入公式 =SUM(A1:A3*B1:B3
30、),按CTRL+SHIFT+ENTER结束. 或： =SUMPRODUCT(A1:A10,B1:B10) 工作中须将表格中大量的日期同时转换为中英文的星期几解答：转成英文： =TEXT(WEEKDAY(A1),dddd) 转成中文： =TEXT(WEEKDAY(A1),aaaa) 如何给自定义函数写上帮助信信息和参数说明解答：目前自定义函数是不能做到完全象内置函数一样的。但是可以给它添加一段说明。打开带有自定义函数的工作簿-按快捷键 Alt+F8 在光标处输入函数名字单击“选项”输入说明文字。如何在 EXCEL中统计一个数在哪一行出现？解答：=IF(ISERROR(SMALL(IF(R1C1:
31、R16C1=R1C2,ROW(R1C1:R16C1),),ROW()-1),SMALL(IF(R1C1:R16C1=R1C2,ROW(R1C1:R16C1),),ROW()-1) 工作表间的查找与复制我的工作是在一个2000 多个员工的纪录 （包括各种资料， 如身份证号，工作记录等）的excel 工作表 A中查找另一份200 多个员工名单，把员工在A表中的各项纪录复制到另一个表上去，我听说用 vba 可以很容易的简化大量的复制工作，不知哪位高手可以指点指点？解答：如下表sheet1 的内容a b c 姓名 工号部门张三 100 mm 李四 101 mm2 ,表 sheet2 a b c 姓名
32、部门李四,要在 sheet2 表中将 sheet1 表的相同人员资料复制过来可在b表 单元格 b2 中输入=vlookup(a2,sheet1!a1:gg2000,3,false) 最好将 sheet1 的数据区先定义一下怎么把等于 A列里的一个值的 , 再统计出 B列里不同数据的个数A B a 北京 /a 广州 /a 天津 /b 广州 /b 长沙 /a 北京 /b 北京/b 长沙我还想再加个条件呢?比如 : A 列是 a 的有 3 个不同的城市 ,A 列是 b 的有 3 个不同的城市 ,). 解答：1、=sum(a1:a10=a)*(b1:b10=广州 )。2、你搞错我的意思了, 我是想要不
33、同城市的值, 如: 当 A列中等于 a 的, 就统计出 B列中有多少个不同的城市数量, 其结果是3(三个不同的城市) 。3、就你这道例题来讲：设数据在A1:B8 中：=COUNT(LARGE(IF($A$1:$A$8=a,(CODE(LEFT($B$1:$B$8)&CODE(RIGHT($B$1:$B$8)*1,),ROW($A$1:$A$5)-IF(LARGE(IF($A$1:$A$8=a,(CODE(LEFT($B$1:$B$8)&CODE(RIGHT($B$1:$B$8)*1,),ROW($A$1:$A$5)-LARGE(IF($A$2:$A$9=a,(CODE(LEFT ($B$2:$
34、B$9)&CODE(RIGHT($B$2:$B$9)*1,),ROW($A$1:$A$5)=0,1,0)公式也有局限性，就是城市最多两个字，三个四个也可以，但肯能会出错，就是会漏掉如何用 IF 函数达到我想要的结果工作表数据如下：A列 B 列 C 列 D 列 E 列1 5 9 H 2 6 0 I 3 7 F J 4 8 G K 我想当 A列=1 或 2 或 3 时， E列=A列对应的值，否则CONCATENATE(A2,B2,C2,D2) /结果如下：名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - -
35、- - - 第 23 页，共 31 页 - - - - - - - - - 24 A列 B 列 C 列 D 列 E 列1 5 9 H 1 2 6 0 I 2 3 7 F J 3 4 8 G K 48GK 请问这个公式怎么写？解答： =IF(OR(A1=1,A1=2,A1=3),A1,A1&B1&C1&D1) 如何判断某个单元格包含某个字符解答：设 A1=LOVE ，查找字母 L 是否在 A1中，=IF(ISERROR(SEARCH(L,A1),NO,YES) 按条件设定数值我想定义单元格A1,在下列条件下数值不同: 1. 在 B1大于 0 小于 20 时等于 6; 2.在 B1大于 20 小于
36、 36 时等于 4; 3.在 B1大于 36 小于 56 时等于 2; 4. 大于 56 以上等于 1. 请问 :A1 公式应该如何 ? 解答：=IF(B1=0,B120,B136,B1=56),2,1) 又问：开头的 B1=0,OUT, 是起什么作用的? 又答：从数学角度来说，一个有理数的范围是从负无穷到正无穷的。在你给我的B1的数值范围内，只定义了从0 到正无穷，对于当 B10或=0 时没有定义。 而我在做 IF 函数时， 必须要考虑到当 B1处在 =0的范围时， A1需要回返的结果。 所以我自己把它定义为，当B10,B120,B136,B156或 B156，还是因为 B1=0(按你的定义
37、即为B1输入的数值有误 ) 。所以，本人还是建议要将所有的情况都考虑在内。小数的进位问题在 excel 中, 我想将小数点后所有的有效数都进为1, 请问用什么方法 ? 、如:3.25 、4.6 、5.3.等进位为 4、5、6. 、说明 : 以上数值均为公式的计算结果, 是可变的 . 解答： C3 = 3.25 、则在 D3中输入“ =ROUNDUP(C3,0) ”找出 A列的数字在B列中所在的位置，并在第三列显示单元格号解答：假设数据在A1:B10，则 C列公式为=MATCH(B1,$A$1:$A$10,0) 从 G9到 G71，需要隔行求和解答：在需要的单元格中（如G72单元格）输入：=SU
38、M(IF(MOD(ROW(G9:G71),2)=1,G9:G71,0) 按 Ctr+Shift+Enter即可。在单元格返回工作表名称解答：函数方法：=MID(CELL(filename),FIND(),CELL(filename)+1,100) 如何统计求每天不重复的值如图所示：我想统计算出每天不重复的管理员？日期行数中的值每月每天都是上下午2 个单元格分别以数字代表; 管理员行中的管理员每月每天就没有固定的取值了，但人员数是不变的。图中管理员需求的值为：A=9；B=7：C=3 如图：解答：C2=A C3=B C4=C D2:=SUM($B$2:$B$21=C2)*(MATCH($A$2:$
39、A$21&$B$2:$B$21,$A$2:$A$21&$B$2:$B$21,0)=ROW($A$1:$A$20) 拖到 D4 使用 vlookup 函数的问题当时有两千多人的考试成绩要与花名册挂接，考试成绩放在sheet km1 中，花名册放在sheet hmc 中，他们共有字段为准名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 24 页，共 31 页 - - - - - - - - - 25 考证号，我的想法是根据准考证号，用vlookup 函数查找相应的成绩并放在相应的人员下。s
40、heet km的准考证号放在第一列，考试成绩放在第二列，查找范围是 $a$2:$b$2265 ，sheet hmc 的准考证号党在第一列。公式为：vlookup(a2,km!$a$2:$b$2265,2,false) 公式应该没什么问题，但只能找到很少的纪录(35 并13 and 13)-COUNTIF(A:A,35) (35 or 14) and 0 公式=COUNTIF(A:A,35)+COUNTIF(A:A,SUMPRODUCT(-($A$2:$A$10=$G$2)*($B$2:$B$10=$G$3),ROW()-5)，然后向下复制到F7、F8, 单元格中。（2）“摘要”从数据区域中自动
41、获取，在G6单元格中输入数组公式：=IF($F6=,INDEX(C:C,SMALL(IF($A$2:$A$10=$G$2)*($B$2:$B$10=$G$3),ROW($A$2:$A$10),$F6)，然后向下拖动填充柄（3）“进货量”从数据区域中自动获取，在H6单元格中输入数组公式：=IF($F6=,INDEX(D:D,SMALL(IF($A$2:$A$10=$G$2)*($B$2:$B$10=$G$3),ROW($A$2:$A$10),$F6) 有条件查询最后一条数据记录：A 列B列C列D 列日期客户姓名进货单号进货量2006-8-8 张三N1001 10 2006-8-8 李四N1002
42、 20 2006-8-8 张三N1003 50 2006-8-8 李四N1004 40 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 26 页，共 31 页 - - - - - - - - - 27 2006-8-8 张三N1005 30 2006-8-8 李四N1006 60 2006-8-9 张三N1007 40 2006-8-9 李四N1008 20 2006-8-9 张三N1009 30 F 列G列行 2 查询日期2006-8-8 行 3 查询客户李四行 4 最后进货量60
43、 在 G4单元格输入数组公式：=LOOKUP(9E+307,IF(A2:A10=G2)*(B2:B10=G3),D2:D10) 或数组公式：=INDEX(D2:D10,MATCH(2,1/(A2:A10=G2)*(B2:B10=G3) SUM 函数实例几例：1、数组公式： =SUM(A2:A505)*(A2:A50) 求 A2：A50 区域中所有大于5 的数据之和。DSUM 函数也可以条件求和，A B C D E F G H 月份地区金额四东100 月份地区四南170 四南 3 五东200 金额四南120 290 在 G5单元格中输入公式：=DSUM(A1:D5,4,E2:F3) 或=DSUM
44、(A1:D5, “金额”, E2:F3) DSUM 的语法为： DSUM （数据清单引用，指定函数所使用的数据列的列位置数或字段名，条件区域）说明数据清单引用必须带字段在内，指定函数所使用的数据列在清单中的列位置或字段名，此例中要计算金额，那么应设定要求的数据列的在清单中的列位置为4 或字段名为“金额”，条件区域为E2：F3，同样也要带列标题在内。数组公式：=SUM(IF(B1:B100= 五月 ,C1:C100) 当 B1： B100中的数据为 “五月” 时，求 C1 ：C100区域中所有符合这个条件的数据之和。数组公式： =SUM(IF(C1:C300=C6,1,0) 求 C1： C300
45、区域中大于 C6 单元格数据的个数。此公式等效于：=COUNTIF(C109:C116,=&C118) 2、数组公式：=SUM(ISNUMBER(FIND(6,C1:C300)*ISNUMBER(FIND(8,C1:C300) 求 C1：C300中同时含有 6 与 8 的数据的个数数组公式：=SUM(IF(A1:A50= ”男”，if(B1:B50=60， 1， 0) 求满足 A1：A50区域为“男”且满足B1：B50 区域为数字60的的数据个数3、 下表是河南南阳地方两家报社三年上半年刊登广告单位数据的一部分，为了考察广告黄金月份的分布情况，现在要求对不同月份广告刊登单位数量进行统计。在 F
46、4 单元格中输入如下数组公式：=SUM(ROW(D140:D157)-139=IF(ISNA(MATCH(F140&F141&D140:D157,MONTH(B140:B157)&C140:C157&D140:D157,0),MATCH(F140&F141&D140:D157,MONTH(B140:B157)&C140:C157&D140:D157,0)*1) 编号刊登日期媒体刊登单位统计查询01 02-1-1 南阳晚报正大月份4 02 02-2-1 南阳日报科龙媒体南阳晚报03 02-3-1 南阳日报科龙刊登单位数 1 04 02-4-1 南阳日报科龙05 02-5-1 南阳晚报科龙06 0
47、2-6-1 南阳晚报志高07 03-1-1 南阳晚报科龙08 03-2-1 南阳晚报春兰09 03-3-1 南阳晚报海尔10 03-4-1 南阳日报TCL 11 03-5-1 南阳日报春兰12 03-6-1 南阳晚报春兰13 04-1-1 南阳日报海信14 04-2-1 南阳晚报志高15 04-3-1 南阳晚报海尔16 04-4-1 南阳晚报春兰17 04-5-1 南阳晚报春兰18 04-6-1 南阳晚报春兰备注： A 、这只是数据库的一部分，实际应用时，你可以根据具体情况修改公式； B、公式中的第一部分只直到一个获得自然序列数的方法，因数据存放位置的不同，所减的数字应相应改变；比如：数据如果
48、是从第2 行开始的，则应减1。至于引用第几列数据，可随便。有效性应用之多级选择性录入: A列B列C 列D列E列F列省份广东省浙江省辽宁省四川省河北省城市名广州市杭州市沈阳市成都市石家庄市深圳市宁波市大连市自贡市唐山市珠海市温州市鞍山市攀枝花市秦皇岛市汕头市嘉兴市抚顺市泸州市邯郸市韶关市湖州市本溪市德阳市邢台市名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 27 页，共 31 页 - - - - - - - - - 28 佛山市绍兴市丹东市绵阳市保定市江门市金华市锦州市广元市张家口市湛
49、江市衢州市营口市遂宁市承德市茂名市舟山市阜新市内江市沧州市肇庆市台州市辽阳市乐山市廊坊市惠州市丽水市盘锦市南充市衡水市梅州市铁岭市宜宾市汕尾市朝阳市广安市河源市葫芦岛市达州市阳江市眉山市清远市雅安市东莞市巴中市中山市资阳市潮州市阿坝州揭阳市甘孜州云浮市凉山州先建立以上数据区域作为下拉选择框的源数据，然后选定要选择性录入省份名称的单元格，执行“数据”“有效性”，在“设置” 卡的“有效性条件” 区的“允许” 列表框中选择 “序列”，再在“来源”框中拾取或输入源数据中省份名称的区域引用，如 =$B$1:$F$1 。再在要选择性录入该省份下属各城市名称的单元格中，执行同样操作，不同的是“来源”框中输入
50、的是=OFFSET($A$2,MATCH($H2,$1:$1,)-1,COUNTA(OFFSET($A$1,MATCH($H2,$1:$1,)-1,60000) 有效性应用之二级选择性录入A列B列职称专业高级高级管理高级暖通高级会计高级建筑高级经济中级中级管理中级暖通中级会计中级建筑中级经济初级初级管理初级暖通初级会计初级建筑初级经济员级员级管理员级暖通员级会计员级建筑员级经济为“职称”选择单元格设置“有效性”“序列”, “来源”框为：“ =$A$2:$A$21 ”，为“专业”选择单元格设置“有效性”“序列”，“来源”框为：“=OFFSET(A2,MATCH(D2,A2:A21,0)-1,1)