您所在位置： &
&nbsp&&nbsp&nbsp&&nbsp
EXCEL函数教学课件精品.ppt 80页
本文档一共被下载：
次 ,您可全文免费在线阅读后下载本文档。
下载提示
1.本站不保证该用户上传的文档完整性，不预览、不比对内容而直接下载产生的反悔问题本站不予受理。
2.该文档所得收入(下载+内容+预览三)归上传者、原创者。
3.登录后可充值，立即自动返金币，充值渠道很便利
EXCEL函数教学课件精品
你可能关注的文档：
··········
··········
* * * * * * * * * * COUNTBLANK（1：1）=256 COUNTBLANK（A：A）=65536 * 提示日期型态的字段值，需取年、月、日时，不要用mid * 在EXCEL中,进行数据统计的步长是一样的,而有时我们需要有更灵活的统计区间, FREQUENCY可以解决这一问题 ABS：求绝对值 用途：返回某一参数的绝对值。 语法：ABS(number) 参数：number 是需要计算其绝对值的一个实数。 实例：如果A1=-16，则公式“=ABS(A1)”返回16。 ROUND：对数值进取四舍五入。 用途：按指定位数四舍五入某个数字。 语法：ROUND(需四舍五入的数字，保留的小数位) 注意：如果保留的小数位大于0，则四舍五入到指定的小数位；如果等于0，则四舍五入到最接近的整数；如果小于0，则在小数点左侧按指定位数四舍五入。 实例：如果A1=65.25，则公式“=ROUND(A1，1)”返回65.3；=ROUND(82.149，2)返回82.15；=ROUND(21.5，-1)返回20；Round(A1,0)返回65。 Countblank 用途：计算某个单元格区域中空白单元格的数目。 语法：COUNTBLANK(range) 参数：Range 为需要计算其中空白单元格数目的区域。 Countblank的思考案例 1、在一个空白的excel表中： Countblank(1:1)=?(相当于EXCEL的列数)
Countblank(a:a)=?(相当于EXCEL的行数)
2、在一个非空白的excel表中： Counta(a:a)+countblank(a:a)=? INT:无条件取整 用途：将任意实数向下取整为最接近的整数。 语法：INT(实数) 参数：Number 为需要处理的任意一个实数。 实例：如果A1=16.24、A2=-28.389，则公式“=INT(A1)”返回16，=INT(A2)返回-29。 Trunc 用途：将数字的小数部分依需截去（不作四舍五入），返回实数。 语法：TRUNC(需要截去小数部分的数字，保留小数的位数) 注意：TRUNC 函数可以按需要截取数字的小数部分，而INT函数则将数字向下舍入到最接近的整数。INT 和TRUNC 函数在处理负数时有所不同：TRUNC(-4.3)返回-4，而INT(-4.3)返回-5。 实例：如果A1=78.652，则公式“=TRUNC(A1，1)”返回78.6，=TRUNC(A1，2)返回78.65，=TRUNC(-8.963，2)返回–8.96。 EXACT:比较两个字符串是否相同 用途：测试两个字符串是否完全相同。如果它们完全相同，则返回TRUE；否则返回FALSE。EXACT 函数能区分大小写，但忽略格式上的差异。 语法：EXACT(text1，text2)。 参数：Text1 是待比较的第一个字符串，Text2 是待比较的第二个字符串。 实例：如果A1=得利卡、A2=富利卡、A3=戈蓝，则公式“=EXACT(A1，A2)”返回FALSE，=EXACT(A1，A3)返回FALSE，=EXACT(&word&，&word&)返回TRUE。
ISEVEN 用途：测试参数的奇偶性，如果参数为偶数返回TRUE，否则返回FALSE。 语法：ISEVEN(number),Number 待测试的数值。如果参数值不是整数，则自动截去小数部分取整。 注意：该函数必须加载“分析工具库”方能使用。如果参数number 不是数值，ISEVEN 函数返回错误值#VALUE!。 实例：公式“=ISEVEN(11)返回FALSE”，=ISEVEN(6)返回TRUE。 ISERROR 用途：它们可以检验是否发生错误值。发生错误返回TRUE ，否则返回FALSE。 语法：ISERROR(value)
参数：Value 是需要进行检验的参数。 实例：用VLOOKUP函数找不到相应的值时，显示空值。
=IF(ISSEROR(vlookup(A2,A2:G5,2,0)), 〝〞, vlookup(A2,A2:G5,2,0)) CONCATENATE 用途：将若干文字串合并到一个文字串中，其功能与&&&运算符相同。 语法：CONCATENATE(text1，text2，...) 参数：Text1，text2，...为1 到30 个将要合并成单个文本的文本项，这些文本项可以是文字串、数字或对单个单元格的引用。 实例：如果A1=98、A2=千米，则公式“=CONCATENATE(A1，A2)”返回“98 千米”，与公式“=A1&A2”等价。
YEAR\MONTH\DAY 用途：返回某日期的年份/月份
正在加载中，请稍后...excel里怎么用VLOOKUP函数取同时满足两个条件的值！_百度知道
excel里怎么用VLOOKUP函数取同时满足两个条件的值！
我有更好的答案
输入公式：=VLOOKUP(条件一&条件二，IF({1,0},条件一区域&条件二区域，返回列）,2,0）最后同时按ctrl shift 回车 三键结束解释：1、公式中的E2&F2，它表示将两个单元格连在一起当做一个整体进行查找。如图中即为：A许六2、A2:A7&B2:B7表示的意思与上面基本一致，就是班级和姓名作为一个整体。如图中即为：A李一，D王二，B张三……3、IF({1,0},A2:A7&B2:B7,C2:C7)表示将班级和姓名作为一个整体，然后与得分列进行组合成两列，即班级姓名整体一列，得分一列4、最后用VLOOKUP来从“班级姓名整体”一列，“得分”一列这两列中，查找“班级姓名整体”说对应的“得分”
采纳率：59%
来自团队：
取同时满足两个条件的值可以用函数SUMPRODUCT()完成，而不要用vlookup。
本回答被提问者采纳
为您推荐：
其他类似问题
vlookup函数的相关知识
换一换
回答问题，赢新手礼包
个人、企业类
违法有害信息,请在下方选择后提交
色情、暴力
我们会通过消息、邮箱等方式尽快将举报结果通知您。当前位置： >>
常用EXCEL函数详解及应用实例(分类汇总版6)――查找与引用函数
常用Excel函数详解及应用实例七、查找与引用函数序号1 2 3 4 5 6 7 8 9 10 11 12 13 14 15函数Areas Choose Column Columns Hlookup Index Indirect Lookup Match Offset Row Rows Vlookup函数定义计算指定范围的区域个数 从参数列表中选择特定的值 返回给定引用的列标 返回给定数组或引用的列数 在数据区域首行查找指定的数值,返回数据区域当前列中指定行处的数值页码1 1 1 1 2 2 3 5 6 7 8 9 9 10 10Hyperlink 创建快捷方式或跳转，打开存储在网络服务器、Intranet或其上的文档返回行和列交叉的位置的值（引用和数组两种形式） 返回由文本字符串指定的引用，并对引用进行计算及显示其内容 返回查找向量的单行区域或单列区域或数组中的数值的值 返回在指定方式下与指定数值匹配的数组中元素的相应位置 以指定的引用为参照系,给定偏移量,返回单元格或单元格区域 返回给定引用的行号。 返回引用或数组的行数Transpose 返回区域的转置(行和列的转置)在数据区域首列查找指定的数值,返回数据区域当前行中指定列处的数值整理日期：2013年6月Zylkfxy常用Excel函数详解及应用实例A 1 B C D E F G H I七、查找与引用函数1.AREAS 引用中包含的区域个数 一、日期与时间函数函数定义： 返回引用中包含的区域个数.区域表示连续的单元格区域或某个单元格. 使用格式： AREAS(reference) 格式简义： AREAS(一个或多个区域) 参数定义： Reference 对某个单元格或单元格区域的引用,也可以引用多个区域. 注意事项： 如果需要将几个引用指定为一个参数,则必须用括号括起来,以免MicrosoftExcel将逗号作为参 数间的分隔符. 结果 公式显示 1 =AREAS(B10:C11) 2 =AREAS((B10:C11,B13:C13)) 3 =AREAS((B10:C11,C12,B13:C13))2 3 4 5 6 7 8 9 10 例1 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 例2 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 例3 52 532.CHOOSE 选择特定的值使用index_num返回数值参数列表中的数值.使用函数CHOOSE可以基于索引号返回多达29个基于 函数定义： indexnumber待选数值中的任一数值.例如,如果数值1到7表示一个星期的7天,当用1到7之间的 数字作index_num时,函数CHOOSE返回其中的某一天.使用格式： CHOOSE(index_num,value1,value2,...) 格式简义： CHOOSE(指定区域的第几个,指定区域1,指定区域2,??) 参数定义： Index_num 用以指明待选参数序号的参数值.Index_num必须为1到29之间的数字、或者是包 valuel?含数字1到29的公式或单元格引用. 为 1到29个数值参数，函数CHOOSE基于index_num，从中选择一个数值或执行相 应的操作。参数可以为数字、单元格引用、已定义的名称、公式、函数或文本注意事项： 1.若index_num为1,函数CHOOSE返回value1；若为2,函数CHOOSE返回value2,以此类推; 2.若index_num小于1或大于列表中最后一个值的序号,函数CHOOSE返回错误值#VALUE!; 3.若index_num为小数,则在使用前将被截尾取整;4.Value1,value2,??为1到29个数值参数,函数CHOOSE基于index_num,从中选择一个数值或 执行相应的操作.参数可以为数字、单元格引用、已定义的名称、公式、函数或文本; 5.若index_num为一个数组,则在函数CHOOSE计算时,每一个值都将计算; 6.函数CHOOSE的数值参数不仅可以为单个数值,也可以为区域引用.3 5 excel 11 134 文本 11 15 6 12 1713 文本 30=CHOOSE(B32,B33,B34,B35) =CHOOSE(1,D32,C33,B34,D35) =SUM(CHOOSE(2,B32:B35,C32:C35,D32:D35))3.COLUMN[COUMNS] 引用的列标[列数]函数定义： column返回给定引用的列标;columns返回给定数组或引用的列数 使用格式： COLUMN(reference);COLUMNS(reference) 格式简义： COLUMN(目标单元格);COLUMNS(单元格或一个单元格区域) 参数定义： reference 指定单元格引用或单元格区域的引用.当指定了单元格区域的引用时,位于左 上角的单元格的列序号构成返回值. 要点： 如果省略区域,输入&=COLUMN()&时,输入COLUMN函数的单元格的列序号就是函数的返回值 注意事项： COLUMN:1.如果reference为一个单元格区域,并且函数COLUMN作为水平数组输入,则函数 COLUMN将reference中的列标以水平数组的形式返回; 2.Reference不能引用多个区域. COLUMNS:1.如果省略reference,则假定为是对函数COLUMN所在单元格的引用. 2.如果reference为一个单元格区域,并且函数COLUMN作为水平数组输入,则函数COLUMN将 reference中的列标以水平数组的形式返回. 3.Reference不能引用多个区域.& A B C E F G I J K 2 =COLUMN(B51)(计算出在第几列开始(B列)) 3 =COLUMN(C51:D51)(计算出在第几列开始(C列)) 5 =COLUMN()(计算公式所在的列(E列)) 第 2 页，共 13 页常用Excel函数详解及应用实例A 54 55 56 57 例4 利用函数COLUMN在行上分别标出系列奇数和偶数 58 1 3 5 7 奇数： =COLUMN(A1)*2-1 59 2 4 6 8 偶数： =COLUMN(A1)*2 60 61 例5 利用函数ROW.COLUMN来组成有规律的数据 1 2 3 4 公式 62 5 6 7 8 =ROW(A1)*4+COLUMN(A1)-4 63 9 10 11 12 =COLUMN()-1+(ROW()-62)*4 64 B D M C H N D L O E F G H 1 =COLUMNS(B51:B55)(计算只有1列) 3 =COLUMNS(B51:D55)(计算出3列) I4.HLOOKUP 在表格或数值数组的首行查找指定的数值 65 按照水平方向在表格或数值数组的首行查找指定的数值,并由此返回表格或数组当前列中指定行 66 函数定义： 处的数值.当比较值位于数据表的首行,并且要查找下面给定行中的数据时,请使用函数 HLOOKUP. 67 当比较值位于要查找的数据左边的一列时,请使用函数 VLOOKUP. 68 69 使用格式： HLOOKUP(lookup_value,table_array,row_index_num,range_lookup) 70 格式简义： HLOOKUP((要查找的内容,搜索的区域,从查找区域首行开始到要找的内容的行数,指定是 71 近似匹配还是精确匹配查找方式) Lookup_value Lookup_value为需要在数据表第一行中进行查找的数值.Lookup_value可以 72 参数定义： 73 为数值、引用或文本字符串.文本不区分大小写 Table_array 为需要在其中查找数据的数据表.可以使用对区域或区域名称的引用.Table_array 74 75 的第一行的数值可以为文本、数字或逻辑值. 为table_array中待返回的匹配值的行序号.Row_index_num为1时,返回table_array 76 Row _index_num 第一行的数值,row_index_num为2时,返回table_array第二行的数值,以此类推.如果 77 row_index_num小于1,函数HLOOKUP返回错误值#VALUE!；如果row_index_num大于 78 table-array的行数,函数HLOOKUP返回错误值#REF!. 79 Range 为一逻辑值,指明函数HLOOKUP查找时是精确匹配,还是近似匹配.如果为TRUE或省略, 80 _lookup 则返回近似匹配值.也就是说,如果找不到精确匹配值,则返回小于lookup_value的 81 最大数值.如果range_value为FALSE,函数HLOOKUP将查找精确匹配值,如果找不到, 82 则返回错误值#N/A!. 83 如果range_lookup为TRUE,则table_array的第一行的数值必须按升序排列:?-2、-1、0、1、2、 84 要点： ?、A-Z、FALSE、TRUE；否则,函数HLOOKUP将不能给出正确的数值.如果range_lookup为FALSE, 85 则table_array不必进行排序. 86 87 88 例6 建立一个查询表，查询指定科目的成绩 数学 姓名 数学 英语 物理 89 成绩查询： 数学 90 85 赵一 85 100 83 赵一 91 钱二 69 58 91 =HLOOKUP($G$88,$C$89:$E$95,ROW(A1),0) 92 孙三 78 78 96 93 李四 89 57 78 94 周五 80 68 85 95 吴六 98 98 28 96 97 例7 根据不同的值班类别所对应的工资标准统计表中的值班类别自动返回应计工资标准 98 值班日期
1月2―3日 5月1―2日 5月3―7日 双休日 99 日期类别 长假开始初 长假后期 长假开始初 长假后期 双休日 100 每日工资 300 220 300 220 180 姓名 值班日期 值班天数 日期类别 工资标准 工资总额 101
102 李四 300 1 长假开始初 300 ―3 103 钱二 220 1 长假后期 220 ―2 104 孙三 600 2 长假开始初 300 ―7 105 王八 880 4 长假后期 220
106 吴六 180 1 双休日 180 107 =HLOOKUP(E102,$C$99:$G$100,2,0)(查找工资标准)第 3 页，共 13 页常用Excel函数详解及应用实例A B C D E F G H I5.HYPERLINK 创建超链接 108 创建一个快捷方式超链接,用以打开存储在网络服务器、Intranet或Internet中的文件.当单击函 109 函数定义： 数HYPERLINK所在的单元格时,MicrosoftExecl将打开存储在link_location中的文件. 110 111 使用格式： HYPERLINK(link_location,friendly_name) 112 格式简义： HYPERLINK(目标地址或单元格引用,显示说明或单元格引用) 可以为括在引号中的文本字符串,或是包含文本字符串链接的单元格.为文档的路径 113 参数定义： Link_location 114 和文件名,此文档可以作为文本打开.Link_location还可以指向文档中的某个更为 115 具体的位置,如Execl工作表或工作簿中特定的单元格或命名区域,或是指向 116 MicrosoftWord文档中的书签.路径可以是存储在硬盘驱动器上的文件,或是服务器 117 (在MicrosoftExcelforWindows中)上的“通用命名规范”(UNC)路径,或是在 118 Internet或Intranet上的“统一资源定位符”(URL)路径. Friendly_name Friendly_name为单元格中显示的跳转文本值或数字值.单元格的内容为蓝色并带有 119 120 下划线.如果省略Friendly_name,单元格将link_location显示为跳转文本. 1.如果在link_location中指定的跳转不存在或不能访问,则当单击单元格时将出现错误信息 121 注意事项： 2.Friendly_name可以为数值、文本字符串、名称或包含跳转文本或数值的单元格. 122 3.如果Friendly_name返回错误值（例如,#VALUE!）,单元格将显示错误值以替代跳转文本. 123 4.若要选定一个包含超链接的单元格并且不跳往超链接的目标文件,请单击单元格区域并按住鼠 124 125 标按钮直到光标变成一个十字,然后释放鼠标按钮. 126 127 例8 链接网页 链接结果 公式显示 ××的博客 =HYPERLINK(&http://blog.sina.com.cn/excelbd&,A1) 128 ××的博客 129 130 链接工作表和单元格链接 链接到查找与引用函数 =HYPERLINK(&#查找函数!A1&,&链接到查找与引用函数&) 131 目录 =HYPERLINK(&#A1&,&本页A1&) 132 本页页首 本页A1 说明:#为数字占位符 133 134 例9 根据查找结果建立超链接（根据查找代号来建立销售部名称及其价格的链接） 135 代号 名称 最新价 代号 A11015 老洲销售部 136 A11011 枞阳销售部 13.26 名称 26.02 137 A11012 其林销售部 32.92 最新价 138 A11013 破罡销售部 46.01 139 A11014 汤沟销售部 24.80 =HYPERLINK(&#hyperlink!c&&MATCH($F$135,$B$136:$B$145,0)+135, 140 A11015 老洲销售部 26.02 VLOOKUP($F$135,$B$136:$C$145,2,0))(链接名称) 141 A11016 横埠销售部 10.82 142 A11017 白云销售部 21.83 =HYPERLINK(&#hyperlink!d&&MATCH($F$135,$B$136:$B$145,0)+135, 143 A11018 义津销售部 10.98 VLOOKUP(F135,B136:D145,3,0))(链接最新价) 144 A11019 官桥销售部 49.13 145 A11020 钱桥销售部 2.82146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 1626.INDEX 查找行和列交叉位置的值函数定义： (数组形式)返回列表或数组中的元素值,此元素由行序号和列序号的索引值给定； (单元格引用方式)返回行和列交差位置的单元格引用，返回指定的行与列交叉处的单元格 引用.数组形式通常返回数值或数值数组；引用形式通常返回引用.如果引用由不连续的选 定区域组成,可以选择某一连续区域 使用格式： 数组形式INDEX(array,row_num,column_num)； 引用形式INDEX(reference,row_num,column_num,area_num) 格式简义： 数组形式INDEX(单元格区域或数组常量,行序号,列序号) 参数定义： Array；引用形式INDEX(对一个或多个单元格区域的引用,行序号,列序号,选择引用中的一个区域) 为单元格区域或数组常量.如果数组只包含一行或一列,则相对应的参数row_num或 column_num为可选.如果数组有多行和多列,但只使用row_num或column_num,函数 INDEX返回数组中的整行或整列,且返回值也为数组. Reference 对一个或多个单元格区域的引用,如果为引用输入一个不连续的区域,必须用括号括 起来.如果引用中的每个区域只包含一行或一列,则相应参数row_num或column_num 分别为可选项.如,对于单行的引用,可以使用函数INDEX(reference,,column_num). Row_num 某行的行序号,函数从该行返回数值.如果省略row_num,则必须有column_num. Column_num 某列的列序号,函数从该列返回数值.如果省略column_num,则必须有row_num.第 4 页，共 13 页常用Excel函数详解及应用实例A B C D E F G H I 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 例10 业务员 不重复业务员 不重复人员名单： 标注 181 何不求 何不求 重复 {=INDEX($B$181:$B$194, 182 许锡文 许锡文 不重复 MATCH(0,COUNTIF($C$180:C180,$B$181:$B$194),0))&&&} 183 何不求 朱英杰 重复 标注重复与不重复: 184 朱英杰 刘召 重复 =IF(COUNTIF($B$181:$B$194,$B$181:$B$194)&1, 185 何不求 刘招 重复 重复,&不重复&) 186 刘召 吴小冰 不重复 统计不重复人员数量: 187 刘招 梁俊浩 重复 9 188 刘招 蒋进才 重复 {=SUM(1/COUNTIF($B$181:$B$194,$B$181:$B$194))} 189 吴小冰 于震威 重复 9 190 梁俊浩 不重复 =SUMPRODUCT(1/COUNTIF($B$181:$B$194,$B$181:$B$194)) 191 蒋进才 不重复 192 朱英杰 重复 193 于震威 不重复 194 吴小冰 重复 195 196 例11 一次性提取工资大于4000元的人的姓名 197 姓名 性别 工资 提取姓名 198 2008 张三 王五 男 199 1920 赵六 李四 女 200 5560 郑八 张三 女 201 7160 周九 赵六 男 202 2980 吴七 男 203 4120 {=INDEX($B$198:$B$204,MATCH(1,($D$198:$D$204&4000) 郑八 女 204 9999 *(COUNTIF($E$197:E197,$B$198:$B$204)=0),0))} 周九 女 205 206 例12 (1)将单列表转换成多列表 207 甲 A1 转置（A列） 甲 乙 丙 208 乙 A2 丁 戊 己 209 丙 A3 庚 辛 壬 210 丁 A4 癸 甲 乙 211 戊 A5 =INDEX($B$207:$B$218,(ROW()-207)*3+COLUMN()-4,) 212 己 A6 213 庚 A7 转置（B列） A1 A2 A3 214 辛 A8 A4 A5 A6 215 壬 A9 A7 A8 A9 216 癸 A10 A10 A11 A12 217 甲 A11 =INDEX($C$207:$C$218,(ROW()-213)*3+COLUMN()-4) 218 乙 A12 219 (2)将两个表头相同的并列的表转换成一个表头相同的表 第 5 页，共 13 页选择引用中的一个区域,并返回该区域中row_num和column_num的交叉区域.选中或 Area_num 输入的第一个区域序号为1,第二个为2,以此类推.如果省略area_num,函数INDEX使 用区域1. 1.INDEX函数有两种语法形式：数组和引用.数组形式通常返回数值或数值数组,引用形式通常返回 要点： 引用.当函数INDEX的第一个参数为数组常数时,使用数组形式. 2.此处的行序号参数(row_num)和列序号参数(column_num)是相对于所引用的单元格区域而言的, 不是Excel工作表中的行或列序号. 1.如果同时使用row_num和column_num,函数INDEX返回row_num和column_num交叉处的单元格的数 注意事项： 值；&在通过reference和area_num选择了特定的区域后,row_num和column_num将进一步选择指定 的单元格：row_num1为区域的首行,column_num1为首列,以此类推.函数INDEX返回的引用即为 row_num和column_num的交叉区域..Row_num、column_num和area_num必须指向reference中的单 元格；否则,函数INDEX返回错误值#REF& 2.如果将row_num或column_num设置为0,函数INDEX则分别返回整个列或行的数组数值.若要使用以 数组形式返回的值,请将INDEX函数以数组公式形式输入,对于行以水平单元格区域的形式输入,对 于列以垂直单元格区域的形式输入.若要输入数组公式,请按Ctrl+Shift+Enter. 3.Row_num和column_num必须指向array中的某一单元格；否则,函数INDEX返回错误值#REF!.常用Excel函数详解及应用实例A 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 例13 用函数方法制作工资条 241 姓名 岗位 薪级 地差 职岗 物补 节编 242 顾** 748 325 0
243 王* 750 774 6
244 汤** 760 459 3.5
246 总合计 .5
姓名 岗位 薪级 地差 职岗 物补 节编 247 248 顾** 748 325 0
249 250 姓名 岗位 薪级 地差 职岗 物补 节编 251 王* 750 774 6
252 253 姓名 岗位 薪级 地差 职岗 物补 节编 254 汤** 760 459 3.5
255 256 姓名 岗位 薪级 地差 职岗 物补 节编 257 龚**
258 =IF(MOD(ROW(),3)=1,INDEX($B$241:$H$245,1,COLUMN()-1),IF(MOD(ROW(),3)=0,&&, 259 INDEX($B$241:$H$245,(ROW()-241)/3,COLUMN()-1))) 260 261 例14 根据客户姓名和商家来查找销售额 262 客户 商家 销售额 客户 商家 查找销售额 263 赵一 商家A 120 周四 商家B 165 264 165 钱二 商家B 135 265 孙三 商家C 150 266 周四 商家B 165 267 吴五 商家C 180 268 {=INDEX($D$263:$D$267,MATCH(E263&&|&&F263,B263:B267&&|&&C263:C267,0))} 269 {=IF(ISNA(INDEX($D$263:$D$267,MATCH(E263&&|&&F263,B263:B267&&|&&C263:C267,0))),&&, 270 INDEX($D$263:$D$267,MATCH(E263&&|&&F263,B263:B267&&|&&C263:C267,0)))} 271 272 273 274 275 B 序号 1 2 3 4 5 6 1 2 3 4 5 6 7 8 9 10 11 12 C 姓名 赵一 钱二 孙三 李四 周五 吴六 赵一 钱二 孙三 李四 周五 吴六 郑七 王八 冯九 陈十 褚十一 卫十二 D 成绩 89 73 62 61 68 72 89 73 62 61 68 72 78 73 60 88 75 65 E F G H 序号 姓名 成绩 7 郑七 78 8 王八 73 9 冯九 60 10 陈十 88 11 褚十一 75 12 卫十二 65 =INDEX($B$221:$G$226,MOD(ROW(A1)-1,6)+1, INT((ROW(A1)-1)/6)*3+COLUMN(A1)) I7.INDIRECT 间接引用单元格的内容函数定义： 返回由文本字符串指定的引用.此函数立即对引用进行计算,并显示其内容 使用格式： INDIRECT(ref_text,a1) 格式简义： INDIRECT(目标单元格,a1) 参数定义： Ref_text 为对单元格的引用,此单元格可以包含A1-样式的引用、R1C1-样式的引用、 第 6 页，共 13 页常用Excel函数详解及应用实例A 276 277 278 279 280 281 282 283 284 ×的博客http://blog.sina.com.cn/ex =INDIRECT(&B285&) 285 例15 ×的博客http://blog.sina.com.cn/ex 286 287 例16 (1)隔行提取数据 (2)转置 288 中 原排序 隔1行提取 隔2行提取 隔3行提取 中华人民共和国 289 华 甲 甲 甲 甲 290 人 乙 丙 丁 戊 291 民 丙 戊 庚 壬 292 共 丁 庚 癸 293 和 戊 壬 294 国 己 {=MID($F$288,ROW(INDIRECT(&1:&&LEN(F288))),1)} 295 庚 296 辛 =INDIRECT(&b&&(ROW(A1)*2+287))&&&(隔1行提取) 297 壬 =INDIRECT(&b&&ROW(A1)*3+286)&&&(隔2行提取) 298 癸 =INDIRECT(&b&&ROW(B1)*4+285)&&&(隔3行提取) 299 (3)先将单列转为多列，再将多列转为单列 再三列转为一列显示 300 姓名 成绩 赵一 301 4 301 赵一 90 赵一 钱二 孙三 钱二 301 5 302 钱二 88 李四 周五 吴六 孙三 301 6 303 孙三 60 郑七 王八 冯九 李四 302 4 304 李四 89 陈十 褚十一 卫十二 周五 302 5 =INDIRECT(&b&&ROW()*3+COLUMN()-606) 305 周五 71 吴六 302 6 注：606=ROW()*3-300+3 306 吴六 85 郑七 303 4 307 1.(转置所在行的3倍减去数据源上一行 郑七 64 王八 303 5 308 的行号加上转置所在列左边的列数) 王八 79 冯九 309 2.201=源数据所在的行号减去 冯九 63 陈十 310 int(row()/3来试判，配成3个一组 陈十 67 褚十一 311 需要转置的源数据的行和列号 褚十一 72 卫十二 312 卫十二 65 =INDIRECT(&r&&INT((ROW())/3+201)&&c&&MOD((ROW()-3),3)+4,) 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 D E F G H I 定义为引用的名称或对文本字符串单元格的引用. A1 为一逻辑值,指明包含在单元格ref_text中的引用的类型. 要点： 当需要更改公式中单元格的引用,而不更改公式本身,请使用函数INDIRECT. 注意事项： 1.如果ref_text不是合法的单元格的引用,函数INDIRECT返回错误值#REF!. 2.如果ref_text是对另一个工作簿的引用(外部引用),则那个工作簿必须被打开.如果源 工作簿没有打开,函数INDIRECT返回错误值#REF!. 3.如果a1为FALSE,ref_text被解释为R1C1-样式的引用. 4.如果a1为TRUE或省略,ref_text被解释为A1-样式的引用. B C8.LOOKUP 搜索单行、单列、区域、查找对应值函数定义： (向量形式)(数组形式)搜索单行、单列、区域、查找对应值 使用格式： LOOKUP(lookup_value,lookup_vector,result_vector)(向量形式) LOOKUP(lookup_value,array)(数组形式) 格式简义： 向量形式LOOKUP(条件,含条件的搜索区域,对应的搜索区域) 数组形式LOOKUP(条件,搜索的区域) 参数定义: Lookup_value Lookup_value为函数LOOKUP在第一个向量中所要查找的数值.Lookup_value可以为数字、文本、逻辑值或包含数值的名称或引用 Lookup_vector为只包含一行或一列的区域.Lookup_vector的数值可以为文本、数字 或逻辑值 Result_vector Result_vector只包含一行或一列的区域,其大小必须与lookup_vector相同. 参数定义: Lookup_value Lookup_value为函数LOOKUP在数组中所要查找的数值.Lookup_value可以为数字、文 本、逻辑值或包含数值的名称或引用. Array 数组 为包含文本、数字或逻辑值的单元格区域,它的值用于与lookup_value进行比较. 1.向量为只包含一行或一列的区域.函数LOOKUP的向量形式是在单行区域或单列区域(向量)中查找 要点： (向量形式) 数值,然后返回第二个单行区域或单列区域中相同位置的数值.若需要指定包含待查找数值的区域, 则可以使用函数LOOKUP的这种形式.函数LOOKUP的另一种形式为自动在第一列或第一行中查找数值. 2.函数LOOKUP的数组形式是在数组的第一行或第一列中查找指定数值,然后返回最后一行或最后一 列中相同位置处的数值.如果需要查找的数值在数组第一行或第一列,就可以使用函数LOOKUP的这 种形式.当需要指定列或行的位置时,,可以使用函数LOOKUP的其他形式.向量Lookup_vector第 7 页，共 13 页常用Excel函数详解及应用实例A B C D E F G H I 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 种形式主要用于与其他电子表格兼容. 350 351 例17 姓名 销量 姓名 销量1 352 李四 451 吴六 862 353 钱二 456 =LOOKUP(D352,$B352:$B359,C352:C359)(姓名必须排序，否则出错） 354 孙三 231 销量 姓名 355 王八 265 265 王八 356 吴六 862 =LOOKUP(D355,C352:C359,B352:B359)（销量没排序，出错） 357 赵一 562 姓名 销售 358 郑七 308 吴六 862 359 周五 362 =LOOKUP(D358,B352:C359)(数组型） 360 361 例18 根据姓名和省份查找人的民族（多条件查找） 362 姓名 省 民族 查找条件 结果 363 赵一 江苏 回族 赵一 江苏 回族 364 赵一 江西 汉族 郑七 浙江 汉族 365 钱二 南京 藏族 赵一 江苏 回族 366 赵一 山东 藏族 孙三 山东 满族 367 孙三 山东 满族 吴六 上海 高山族 368 吴六 上海 高山族 赵一 山东 藏族 369 郑七 浙江 汉族 赵一 江西 汉族 370 =LOOKUP(1,0/(($B$363:$B$369=E363)*($C$363:$C$369=F363)),$D$363:$D$369) 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 3873.Lookup_vector的数值必须按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否 则,函数LOOKUP不能返回正确结果.文本不区分大小写.4.如果lookup_value小于lookup_vector中 的最小值,函数LOOKUP返回错误值#N/A. 4.若lookup_value小于lookup_vector中的最小值,函数LOOKUP返回错误值#N/A. 5.若函数LOOKUP找不到lookup_value,则查找lookup_vector中小于或等于lookup_value最大数值. 1.若函数LOOKUP找不到lookup_value,则使用数组中小于或等于lookup_value的最大数值. 要点： (数组形式) 2.若lookup_value小于第一行或第一列(取决于数组的维数)的最小值,函数LOOKUP返回错误值#N/A. 3.函数LOOKUP的数组形式与函数HLOOKUP和函数VLOOKUP非常相似.不同之处在于函数HLOOKUP在第一 行查找lookup_value,函数VLOOKUP在第一列查找,而函数LOOKUP则按照数组的维数查找. 4.如果数组为正方形,或者所包含的区域高度大,宽度小(即行数多于列数),函数LOOKUP在第一列查 找lookup_value. 5.函数HLOOKUP和函数VLOOKUP允许按行或按列索引,而函数LOOKUP总是选择行或列的最后一个数值. 6.数组中的数值必须按升序排序：-2、-1、0、1、2、...、A-Z、FALSE、TRUE；否则,函数LOOKUP 不能返回正确的结果.文本不区分大小写. 1.若有多个符合条件的情况:vlookup返回的是第一个满足条件的值,lookup返回的是最后一个满足 注意事项： 条件的值. 2.通常情况下,最好使用函数HLOOKUP或函数VLOOKUP来替代函数LOOKUP的数组形式.函数LOOKUP的这9.MATCH 搜索值的相对位置函数定义： 返回在指定方式下与指定数值匹配的数组中元素的相应位置.如果需要找出匹配元素的位 置而不是匹配元素本身,则应该使用MATCH函数而不是 LOOKUP函数 使用格式： MATCH(lookup_value,lookup_array,match_type) 格式简义： MATCH(查找条件,查找区域,查找方式) 参数定义： Lookup_value 为需要在数据表中查找的数值.为需要在Look_array中查找的数值.可以为数值(数字、文本或逻辑值)或对数字、文本或逻辑值的单元格引用.Lookup_array 可能包含所要查找的数值的连续单元格区域.Lookup_array应为数组或数组引用. Match_type 为数字-1、0或1.Match-type指明MicrosoftExcel如何在lookup_array中 查找lookup_value. 要点：函数MATCH返回lookup_array中目标值的位置,而不是数值本身.例如,MATCH(&b&,{&a&,&b&,&c&},0) 返回2,即“b”在数组{&a&,&b&,&c&}中的相应位置. 1.如果match_type为1,函数MATCH查找小于或等于lookup_value的最大数值.Lookup_array必须按升 注意事项： 序排列：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE. 2.如果match_type为0,函数MATCH查找等于lookup_value的第一个数值.Lookup_array可以按任 何顺序排列. 3.如果match_type为-1,函数MATCH查找大于或等于lookup_value的最小数值.Lookup_array必须按第 8 页，共 13 页常用Excel函数详解及应用实例A B C D E F G H I 388 389 390 例19 根据姓名查找其位置 391 公式显示 姓名 性别 对应位置 条件 位置 392 赵一 孙三 女 1 3 =MATCH(E392,B392:B397,0) 393 钱二 男 2 394 孙三 男 3 395 李四 女 4 396 周五 女 5 397 吴六 女 6 398 399 例20 用函数进行身份证真假判断 400 18位身份证最后一位是验证码位，用来验证身份证号码的真假。最后一位是通过前面17位计算得 401 出来的。所以当使用前17位数字进行运算时，如果计算结果与身份证的第18位一致，则为真身份证 402 号码，反之则为假身份证号码。 403 计算方法：将身份证前17位分别乘以7、9、10、5、8、4、2、1、6、3、7、9、10、5、8、4、2 404 这17个数字，再将这17个乘积相加在一起除以11求余数，最终得到余数n，再到辅助运算表对应值中 405 查找n，找到后显示对应的数字就是身份证的第18位数字，找不到则为假。 406 辅助运算表 407 如某人身份证号码 290018 随意编的号码，如有雷同，请谅解 408 查找对应值 查找显示值 乘数 对应值 显示值 身份证单排 乘后之和 余数 409 290 4 第5行 真 7 0 1 3 410 9 1 0 4 411 1.身份证单列排： 10 2 x 2 412 5 3 9 8 {=--MID(D407,ROW($1:$17),1))} 413 2.身份证号各数与乘数乘积之和： 8 4 8 2 =SUMPRODUCT(--MID(D407,ROW($1:$17),1)*B409:B425) 414 4 5 7 3 415 3.乘积之和除以11之余数： 2 6 6 1 416 1 7 5 9 =MOD(SUMPRODUCT(--MID(D407,ROW($1:$17),1) 417 6 8 4 8 *B409:B425),11) 418 4.查找余数在对应值栏中的所在的行数： 3 9 3 1 =&第&&MATCH(MOD(SUMPRODUCT(MID(D407,ROW($1:$17), 419 7 10 2 1 420 9 1 1)*$B$409:$B$425),11),$C$409:$C$419)&&行& 421 5.查找余数（对应值）的显示值 10 2 422 5 9 =IF(VLOOKUP(G409,C409:D419,2,0)= 423 8 0 --RIGHT(D407,1),&真&,&假&) 424 4 0 425 2 1降序排列：TRUE、FALSE426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 44310.OFFSET 计算指定位置的单元格引用函数定义： 以指定的引用为参照系,通过给定偏移量得到新的引用.返回的引用可以为一个单元格或单 元格区域.并可以指定返回的行数或列数 使用格式： OFFSET(reference,rows,cols,height,width) 格式简义： OFFSET(起始坐标,从坐标开始算的行数,从坐标开始算的列数,高,宽) 参数定义： Reference 作为偏移量参照系的引用区域.Reference必须为对单元格或相连单元格区域 的引用；否则,函数OFFSET返回错误值#VALUE!. 相对于偏移量参照系的左上角单元格,上(下)偏移的行数.如果使用5作为参数Rows, Rows则说明目标引用区域的左上角单元格比reference低5行.行数可为正数(代表在起始 引用的下方)或负数(代表在起始引用的上方). 相对于偏移量参照系的左上角单元格,左(右)偏移的列数.如果使用5作为参数Cols, 则说明目标引用区域的左上角的单元格比reference靠右5列.列数可为正数(代表在 起始引用的右边)或负数(代表在起始引用的左边).ColsHeight 高度,即所要返回的引用区域的行数.Height必须为正数. Width 宽度,即所要返回的引用区域的列数.Width必须为正数. 注意事项： 1.如果行数和列数偏移量超出工作表边缘,函数OFFSET返回错误值#REF!. 2.如果省略height或width,则假设其高度或宽度与reference相同. 3.函数OFFSET实际上并不移动任何单元格或更改选定区域,它只是返回一个引用.函数 第 9 页，共 13 页常用Excel函数详解及应用实例A B 444 445 446 例21 姓名 1日销售量 2日销售量 1日销售 447 李四 231 188 329 =OFFSET(B446,3,2)(孙三2日销售量) 448 钱二 456 372 741 =SUM(OFFSET(B446,3,2,1,2))(孙三1.2日销售量) 449 孙三 412 329 根据查找姓名来计算1、2两天的销售(综合运用） 450 赵一 562 366 周五 807 451 周五 451 356 =SUM(OFFSET(B445,MATCH(E449,B446:B450),1,1,2)) 452 453 例22 (1)将单列表转换成多列表 454 甲 A1 转置（A列） 甲 乙 丙 455 乙 A2 丁 戊 己 456 丙 A3 庚 辛 壬 457 丁 A4 癸 甲 乙 458 戊 A5 =OFFSET($B$453,(ROW()-454)*3+COLUMN()-4,0,1) 459 己 A6 460 庚 A7 转置（B列） A1 A2 A3 461 辛 A8 A4 A5 A6 462 壬 A9 A7 A8 A9 463 癸 A10 A10 A11 A12 464 甲 A11 =OFFSET($B$453,(ROW()-454)*3+COLUMN()-4,0,1) 465 乙 A12 466 (2)将两个表头相同的并列的表转换成一个表头相同的表 467 序号 姓名 成绩 序号 姓名 成绩 468 1 赵一 70 7 郑七 60 469 2 钱二 88 8 王八 80 470 3 孙三 77 9 冯九 75 471 4 李四 81 10 陈十 74 472 5 周五 62 11 褚十一 65 473 6 吴六 63 12 卫十二 67 474 1 赵一 70 475 2 钱二 88 =OFFSET($B$467,MOD(ROW(A1)-1,6)+1, 476 3 孙三 77 INT((ROW(B1)-1)/6)*3+COLUMN(A1)-1) 477 4 李四 81 478 5 周五 62 479 6 吴六 63 480 7 郑七 60 481 8 王八 80 482 9 冯九 75 483 10 陈十 74 484 11 褚十一 65 485 12 卫十二 67 486 487 例23 动态取出数据表列中最后5行数据（本题取最后5个数据） 488 数据1 数据2 数据1结果 数据2结果 后5求和 489 A01 237 A05 0 A02 756 A06 631 491 A03 920 A07 72 492 A04 275 A08
494 A06 631 495 A07 72 {=OFFSET($B$488,COUNTA($B$489:$B$497)-4,0,5,1)}(数据1) 496 A08 1022 {=OFFSET($B$488,COUNTA($C$489:$C$497)-4,1,5,1)}( 数据2) 497 A09 787 =SUM(OFFSET($B$488,COUNTA($C$489:$C$497)-4,1,5,1))(数据2求和) 498 499 C D E F OFFSET可用于任何需要将引用作为参数的函数. G H I11.ROW [ROWS]查找第几行[计算多少行]函数定义： ROW查找在第几行；ROWS计算多少行 第 10 页，共 13 页常用Excel函数详解及应用实例A 500 501 502 503 504 505 506 507 508 例24 姓名 509 赵一 510 钱二 511 孙三 512 李四 513 周五 514 515 516 517 518 519 520 521 522 523 524 例25 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 B C D E F G H I 使用格式： ROW(reference)；ROWS(array) 格式简义： ROW(目标单元格);ROWS(单元格或一个单元格区域） 参数定义： Reference Reference为需要得到其行号的单元格或单元格区域.array为需要得到其行 数的数组、数组公式或对单元格区域的引用 如果省略reference,则假定是对函数ROW所在单元格的引用.如果reference为一个单元格区域,并 注意事项：且函数ROW作为垂直数组输入,则函数ROW将reference的行号以垂直数组的形式返回.但Reference 不能引用多个区域.性别 女 男 男 女 女row 509 510 511 512 513rows 5 5 5 5 5row公式：=ROW(B509:B513)(结果始终是公式所在单元格的行号)rows公式：=ROWS(B509:B513)(结果是从起始行到结束行共有几行)12.TRANSPOSE 行和列的转置函数定义： 返回转置单元格区域，即将一行单元格区域转置成一列单元格区域，反之亦然 使用格式： TRANSPOSE(array) 格式简义： TRANSPOSE(需要进行转置单元格区域) 为需要进行转置的数组或工作表中的单元格区域.所谓数组的转置就是,将数组的第 参数定义： Array 要点：一行作为新数组的第一列,数组的第二行作为新数组的第二列,以此类推. 返回转置单元格区域,即将一行单元格区域转置成一列单元格区域,反之亦然.在行列数分别与数组 的行列数相同的区域中,必须将TRANSPOSE输入为数组公式.使用TRANSPOSE可在工作表中转置数组的 垂直和水平方向.姓名 赵一 钱二 孙三 李四性别 女 男 男 女转置后 姓名 性别赵一 女钱二 男孙三 男李四 女{=TRANSPOSE($B$524:$C$528)}13.VLOOKUP 指定条件在指定区域直方向查找函数定义： 在表格或数值数组的首列查找指定的数值,并由此返回表格或数组当前行中指定列处的数值. 当比较值位于数据表首列时,可以使用函数VLOOKUP代替函数HLOOKUP. 使用格式： VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) 格式简义： VLOOKUP(要查找的内容,搜索的区域,从查找区域首列开始到要找的内容的列数,指定是近 似匹配还是精确匹配查找方式) Lookup_value为需要在数组第一列中查找的数值.Lookup_value可以为数值、引用或文本字符串. 参数定义：Table_array 为需要在其中查找数据的数据表.可以使用对区域或区域名称的引用Col_index_num Col_index_num为table_array中待返回的匹配值的列序号.Col_index_num为1时,返回 table_array第一列中的数值；col_index_num为2,返回table_array第二列中的数值, 以此类推.如果col_index_num小于1,函数VLOOKUP返回错误值值#VALUE!；如果 col_index_num大于table_array的列数,函数VLOOKUP返回错误值#REF!. Range_lookup为一逻辑值,指明函数VLOOKUP返回时是精确匹配还是近似匹配.如果为TRUE（可用1 代替）或省略,则返回近似匹配值.也就是说.如果找不到精确匹配值,则返回小于 lookup_value的最大数值；如果range_value为FALSE（可用0代替）,函数VLOOKUP将 返回精确匹配值.如果找不到,则返回错误值#N/A.精确查找适用于文本，也适用于数 值；但对数值查找时必须注意格式一致，否则会出错 如果range_lookup为TRUE,则table_array的第一列中的数值必须按升序排列：?、-2、-1、0、1、 要点： 2、?、-Z、FALSE、TRUE；否则,函数VLOOKUP不能返回正确的数值.如果range_lookup为FALSE, table_array不必进行排序. 1.Table_array的第一列中的数值可以为文本、数字或逻辑值.文本不区分大小写. 注意事项： 2.如果函数VLOOKUP找不到lookup_value,且range_lookup为TRUE,则使用小于等于lookup_value的 最大值.如果lookup_value小于table_array第一列中的最小数值,函数VLOOKUP返回错误值#N/A. 如果函数VLOOKUP找不到lookup_value且range_lookup为FALSE,函数VLOOKUP返回错误值#N/A.若有多个符合条件的情况:vlookup返回的是第一个满足条件的值,lookup返回的是最后一个满 足条件的值. 第 11 页，共 13 页常用Excel函数详解及应用实例A 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 例26 B 品名 A产品 B产品 C产品 D产品 E产品 C 数量 139 35 186 99 358 D E F G H I 营业额 (1)查找左表品名的数量 2363 B产品 品名 595 35 =VLOOKUP(F557,B557:C561,2,0) 数量 3162 (2)反向查找（根据营业额查找品名） 1683 数量 品名 =VLOOKUP(E561,
C产品 IF({1,0},D557:D561,B557:B561),2,0)例27 根据个人工资收入计算个人工资所得税 姓名 工资 个税 级数 应税额 税率 扣除数 .00 0 0.03 0 马二 1 .00
105 张三 2
555 李四 3
1005 王五 4 .00
2755 赵六 5 00.00
5505 吴七 6
0.45 13505 郑八 7
=IF(C565&3500,&&,(C565-3500)*VLOOKUP((C565-3500), 冯十
$F$565:$G$571,2)-VLOOKUP((C565-3500),$F$565:$H$571,3)) 周九 例28 根据左边的合并单元格用Vlookup填充右边单元格 季度 vlookup填充 一季度 =VLOOKUP(&座&,$B$577:B577,1,1) 一季度 一季度 一季度 二季度 二季度 二季度 二季度 三季度 三季度 三季度 三季度 四季度 四季度 四季度 四季度 例29 合并两张表的数据(两张表格姓名顺序不同，把表1的年龄和表2的工资合并到1张表) 姓名 姓名 年龄 姓名 工资 年龄 工资 1850 赵一 34 孙三 950 李四 32 3840 钱二 35 李四 1850 周五 23 12000 孙三 24 周五 3840 吴六 46 24800 李四 32 吴六 12000 郑七 51 15890 周五 23 郑七 24800 赵一 34 5580 吴六 46 赵一 15890 钱二 35 5580 冯九 1280 郑七 51 钱二 47 1280 陈十 3760 王八 38 冯九 22 3760 王八 8550 冯九 47 陈十 38 8550 孙三 950 陈十 22 王八 24 =VLOOKUP(F592,$B$592:$C$601,2,0)(年龄) =VLOOKUP(F592,$D$592:$E$601,2,0)(工资) 例30 根据上边的表用Vlookup与Match综合查找并填充下边的表 姓名 性别 年龄 工龄 工资 张三 男 28 8 2010.00 李四 女 37 17 2200.00 王五 女 49 25 3200.00 赵六 男 55 31 4180.00 吴七 男 42 23 3170.00 第 12 页，共 13 页奖金 500.00 400.00 500.00 600.00 700.00收入合计 0.00 0.00 4330.00常用Excel函数详解及应用实例A 611 612 613 614 615 616 617 618 619 620 621 622 623 例31 根据员工姓名、性别来查找工资（多条件查找方法） 624 条件 姓名 性别 工资 625 张三 男 2008 姓名 张三 626 性别 李四 女 1920 女 627 vlookup: 5560 张三 女
赵六 男 7160 {=VLOOKUP(F625&F626, 629 吴七 男 2980 IF({1,0},B625:B631&C625:C631,D625:D631),2,0)} 630 郑八 女 4120 =LOOKUP(1,0/(B625:B631=F625)*(C625:C631=F626),D625:D631) 631 周九 女
例32 合并单元格的查找与计算（根据单位成本表的单价来计算成本统计表的成本金额） 单位成本表 634 成本统计表 635 品种 销售公司 销售数量 成本金额1 成本金额2 品种 单价 636 安阳公司 39 702 #VALUE! A产品 20 637 白水公司 50 900 #VALUE! B产品 15 D产品 638 天仓公司 43 774 #VALUE! C产品 18 639 小计 132 2376 #VALUE! D产品 30 640 白水公司 20 360 #VALUE! B产品 641 天仓公司 29 522 #VALUE! =VLOOKUP(LOOKUP(&座&,$B$636:$B$649), 642 小计 49 882 #VALUE! $G$636:$H$639,2,0)*$D636 643 安阳公司 36 648 #VALUE! 644 白水公司 31 558 #VALUE! =VLOOKUP(LOOKUP(CHAR(65535), A产品 645 天仓公司 22 396 #VALUE! $B$636:$B$649),$G$636:$H$639,2,0) 646 小计 89 1602 #VALUE! *$D636 647 安阳公司 24 432 #VALUE! C产品 648 天仓公司 48 864 #VALUE! 649 小计 72 1296 #VALUE! 650 总计 342 6156 #VALUE! 651 652 例33 设置产品销售表的销售数量&或=产品库存表的库存数量，并且销售数量必须大于0的数据有效控制 653 产品库存表 产品销售表 654 产品名称 库存数量 产品名称 销售数量 第一步：将&库存&工作表中的B655:C661定义名称为&KC& 655 A产品 55 A产品 51 第二步:在销售表中，选定区域E655:E661 656 B产品 66 B产品 65 第三步：单击菜单&数据&-&有效性&,&设置&&自定义&项 657 C产品 77 C产品 22 第四步:在&公式&输入： 658 D产品 99 D产品 99 =AND(E655&=VLOOKUP(D655,kc,2,0),E655&0) 659 E产品 44 E产品 44 第五步：勾选&忽略空值&复选框，单击&确定&按钮 660 F产品 88 F产品 销售表中的销售数量如输入&产品库存表中的库存数量 661 G产品 99 G产品 或&0的数就不能通过 B C D E F G 郑八 女 31 14 .00 周九 女 66 38 .00 姓名 工龄 性别 年龄 奖金 工资 25 女 49 500 3200 王五 8 男 28 500 2010 张三 17 女 37 400 2200 李四 38 女 66 890 5618 周九 23 男 42 700 3170 吴七 14 女 31 500 2960 郑八 31 男 55 600 4180 赵六 =VLOOKUP($B614,$B$606:$H$612,MATCH(C$613,$B$605:$H$605,0),0) H 8.00 收入合计 0.00 8.00 0.00 5400.00 I第 13 页，共 13 页
更多搜索：
All rights reserved Powered by
文档资料库内容来自网络，如有侵犯请联系客服。}