查询引用方法全集公式说明.xls
《查询引用方法全集公式说明.xls》由会员分享,可在线阅读,更多相关《查询引用方法全集公式说明.xls(22页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、文文本本型型数数值值型型姓姓名名部部门门籍籍贯贯编编码码部部门门数数值值型型文文本本型型 A0011高连兴销售部广东10010A部门100A001 A0022郎会坚销售部四川10010B部门300A005 A0033李 珂生产部安徽10010C部门500A009 A0044李 新销售部四川10011A部门150A002 A00518廉欢财务部成都10011B部门350A006 A0065刘德瑞生产部贵州10011C部门550A010 A0076刘恩树生产部黑龙江10012A部门200A003 A0087王峻松采购部江苏10012B部门400A007 A0098王志为生产部浙江10012C部门6
2、00A011 A0109胥和平销售部四川10013A部门250A004 A01110徐 凯财务部新疆10013B部门450A008 A01211许丽萍生产部四川10013C部门650A012 A01312薛滨峰生产部贵州 A01816杨彬财务部天津多条件引用数据源 A01517杨帆财务部湖南 A01613尹 静采购部北京 A01714张 勇销售部吉林 A01415张国顺生产部广西 A01919朱体高生产部四川 BIN_YANG168BIN_YANG168整整理理 单条件引用数据源wangjguo44补充简介 单条件引用数值型数据 姓姓名名1 12 23 34 45 56 67 78 89 91
3、010111112121313141415151616 杨彬16161616161616161616161616161616 胥和平9999999999999999 刘德瑞5555555555555555 郎会坚2222222222222222 李 珂3333333333333333 刘恩树6666666666666666 王峻松7777777777777777 王志为8888888888888888 李 新4444444444444444 徐 凯10101010101010101010101010101010 高连兴1111111111111111 朱体高191919191919191919
4、19191919191919 许丽萍11111111111111111111111111111111 薛滨峰12121212121212121212121212121212 杨帆17171717171717171717171717171717 廉欢18181818181818181818181818181818 尹 静13131313131313131313131313131313 张 勇14141414141414141414141414141414 张国顺15151515151515151515151515151515 说说明明: 1.1.下下面面简简介介若若无无特特别别指指出出,均均以以
5、第第三三行行公公式式为为例例; 2.2.作作为为公公式式,为为简简练练起起见见,对对本本工工作作表表单单元元格格的的引引用用完完全全不不必必再再输输入入本本工工作作表表名名(本本表表B21:AJ21B21:AJ21张张国国顺顺行行已已做做修修改改,可可参参考考)但但 下下面面简简介介中中仍仍保保留留原原公公式式; 3.3.简简介介中中左左侧侧(即即A23:A57A23:A57)中中数数字字与与第第二二行行(B2:AJ2B2:AJ2)中中对对应应,简简介介区区域域中中的的颜颜色色也也与与上上面面表表格格对对应应,以以便便于于对对照照; 4.4.文文本本型型数数据据第第1 1至至第第2323简简介
6、介与与数数值值型型一一样样,第第2424条条见见其其旁旁边边。 1 VLOOKUP查找(列查找):先由“IF(1,0,数据源!$C$2:$C$20,数据源!$B$2:$B$20”,将数据源C列数据置于第一列,B列数据置于第二列,实现VLOOKUP 查找。(IF(1,0,B,A)并非一般意义上的判断函数,它将A、B两组数据按1(true真,在前)、0(false假,在后)排列,有了它,VLOOKUP可以逆向查找 ,即从右向左查找) 2 VLOOKUP查找(列查找):由“CHOOSE(1,2,数据源!$C$2:$C$20,数据源!$B$2:$B$20)”实现数据列位置置换,实行VLOOKUP查找。
7、(CHOOSE函数按指定 数值返回参数列表中的数据,若choose参数增加,数据列也同步增加,则可扩大查找范围,如本例C3中若为=VLOOKUP(A3,CHOOSE(1,2,3,数据源 !$C$2:$C$20,数据源!$B$2:$B$20,数据源!E2:E20),3,0),便可查到杨斌的籍贯“天津”。 3 数组公式、HLOOKUP查找(行查找):TRANSPOSE转置函数将数组的列数据转化为行数据,行数据转化为列数据。本例通过“IF(1;0,TRANSPOSE(数据 源!$C$2:$C$20),TRANSPOSE(数据源!$B$2:$B$20)”将C列数据转置并排于第一行,B列数据转置并排于第
8、二行,最后由HLOOKUP实行查找。(数组公式要 在输入或编辑公式后按三键:Ctrl+Shift+Enter。) 4 LOOKUP查找:由“1/(数据源!$C$2:$C$20=单条件引用!A3)”“甄别筛选”出TRUE并定为“1”,其余FALSE皆为错误值“#DIV/0!”(除数为0),再由 LOOKUP的查找值2找到“1/(数据源!$C$2:$C$20=单条件引用!A3)”中“1”的位置,并返回“数据源!$B$2:$B$20”对应位置的数据。(LOOKUP要查找的值 必须大于等于查找范围值,否则出错,如本题LOOKUP的第一个参数可以是1、2、3等,但不能小于1) 5LOOKUP查找:查找原
9、理同4,只是用0/(数据源!$C$2:$C$20=单条件引用!A3)把TRUE定为“0”,用大于0 的1来查找。 6 LOOKUP向量形式查找:直接用要查找的值(A3中“杨彬”)找到在查找范围(数据源!$C$2:$C$20)的区域里的位置,返回所要区域(数据源!$B$2:$B$20)里 对应位置的数据。 7 INDEX函数:返回指定行与列交叉处的单元格数据。MATCH函数:返回指定方式下与指定数值匹配的元素位置。先由MATCH函数返回要查找数据在数据源中相 应列中的位置值(如H3要查找的A3的杨彬是数据源C列中第15位),再由INDEX在查找范围数据源A列第15个数据。(MATCH函数查找范围
10、只能是一列或一行的 数据,而INDEX查找范围可以是多列、多行矩形区域或多区域) 8 数组公式:先由MATC返回逻辑判断true值在逻辑判断结果列数组中的位置,再由INDEX返回数据源中查找范围(也是列数组)对应位置数据。本例逻辑判断结 果列数组中除第14位判断“数据源!$C$2:$C$20=单条件引用!A3”为true外,其余皆为false。 9数组公式:先用“-”将上面公式中逻辑判断结果中true转换成1、false转换成0,MATCH查1的位置,其余同上。 10数组公式:先用“(逻辑判断结果*1”把true转换成1、false转换成0,其余同上。 11 数组公式:MAX取最大值,ROW返
11、回所在单元格行数。先由判断函数IF按判断条件为true的返回行数,为false返回0,再由MAX提取最大值(本例中 除符合条件的是杨彬所在的行数15外,其与皆为0,最大值即为符合条件的是行数值15),最后由INDEX引用返回。 12 CHOOSE:将数据源要查找区域中各单元格的数据构成数据列表(1,2,3,4,18,5),再按由MATCH函数返回序号14(指定数值)返回第14个数据“16”。 (CHOOSE函数的数据列表可多达254个(Excel2003版只有129个),但是如果不采取简化措施而罗列254的数据,公式则很长很长) 13 偏移函数OFFSET引用:对指定单元格或单元格区域通过给定
12、偏移量(行偏移和列偏移)得到新的引用。本例根据MATCH得到的数值14,对数据源单元格$B$1实 行向下偏移14行得到B15的数据引用。偏移函数可以向上(负值行偏移)向左(负值列偏移)向下(正值行偏移)向右(正值列偏移),还可以有高度(行数 )宽度(列数)得到一个矩形单元格区域。(本例由于数据是在一列之中,引用的数据也只是一个单元格,所以本例只作行偏移引用,且没有高度和宽度,公 式最后也就只能是,以0(省略输入)表示) 14数组公式、偏移函数OFFSET引用:行偏移量的取得同上面第八条。 15数组公式、偏移函数OFFSET引用:行偏移量的取得同上面第九条。 16数组公式、偏移函数OFFSET引
13、用:行偏移量的取得同上面第十条。 17 引用函数INDIRECT:返回由文本字符串指定的引用。以单元格R3为例,数据源!Afslse;false;true;false;”)与数组“数据源 !$B$2:$B$20”(“1;2;12;16;17;”)相乘后相加。前一个数组中“false”即为0,“true”即为1。 25 数组公式、求和函数SUM,用判断函数if将“数据源!$B$2:$B$20”中满足“数据源!$C$2:$C$20=单条件引用!A3”的数据相加。本例只有一个数据“16”满 足条件。 26 条件求和函数SUMIF:在需要条件判断的单元格区域“数据源!$C$2:$C$20”里,确定哪些
14、单元格将满足被相加求和的条件(=单条件引用!A3),然后将在要 相加的实际单元格区域“数据源!$B$2”相应数据相加。 27求乘积和函数SUMPRODUCT:与24条相似,将数组“数据源!$C$2:$C$20=单条件引用!A3”与数组“数据源!$B$2:$B$20”相乘后相加。 28 数组公式,求乘积和函数SUMPRODUCT:用判断函数if将符合条件和不符合条件的数据构成数组“false;16;false;”。本例只有一列数据,求和 表面上成了直接引用提取。 29 数组公式,求积函数PRODUCT:由于求积函数的参数为数组或引用时,只对其中数字进行计算,其余空白单元格、逻辑值、文本、错误都将
15、忽视,因此本例中 由if函数形成的数组只有16被计算,其余均被忽视。 30 数组公式,最大值函数MAX。数组“数据源!$C$2:$C$20=单条件引用!A3”与数组“数据源!$B$2:$B$20”相乘后,除符合条件的“16”外,其余皆为“0” 。 31 数组公式,最大值函数MAX。与29条相仿,由于函数MAX的参数为数组或引用时,也只对其中数字进行计算,其余空白单元格、逻辑值、文本、错误都将忽视 ,因此本例中由if函数形成的数组只有16跟自己比较大小,当然就是16自己。 32 数组公式,最大值函数MAXA。与30条相同,数组“数据源!$C$2:$C$20=单条件引用!A3”与数组“数据源!$B
16、$2:$B$20”相乘后,除符合条件的“16”外, 其余皆为“0”。函数MAXA与MAX的区别在于它将逻辑值和键入到参数列表中代表数字的文本也被进行计算,但数组中则只使用其中的数值。 33数组公式,最大值函数MAXA。MAXA函数对数组中则只使用其中的数值,因此本例与31条一样,由if函数形成的数组只有16跟自己比较大小。 34 数组公式,最小值函数MIN。与31条相仿,由于函数MIN的参数为数组或引用时,也只对其中数字进行计算,其余空白单元格、逻辑值、文本、错误都将忽视 ,因此本例中由if函数形成的数组只有16跟自己比较大小,当然就是16自己。 35 数组公式,最小值函数MINA。函数MIN
17、A与MIN的区别在于它将逻辑值和键入到参数列表中代表数字的文本也被进行计算,但数组中则只使用其中的数值,因此 本例与33条一样,由if函数形成的数组只有16跟自己比较大小。 单条件引用文本型数据 姓姓名名1 12 23 34 45 56 67 78 89 91010111112121313141415151616 杨彬A018A018A018A018A018A018A018A018A018A018A018A018A018A018A018A018 胥和平A010A010A010A010A010A010A010A010A010A010A010A010A010A010A010A010 刘德瑞A006
18、A006A006A006A006A006A006A006A006A006A006A006A006A006A006A006 郎会坚A002A002A002A002A002A002A002A002A002A002A002A002A002A002A002A002 李 珂A003A003A003A003A003A003A003A003A003A003A003A003A003A003A003A003 刘恩树A007A007A007A007A007A007A007A007A007A007A007A007A007A007A007A007 王峻松A008A008A008A008A008A008A008A008
19、A008A008A008A008A008A008A008A008 王志为A009A009A009A009A009A009A009A009A009A009A009A009A009A009A009A009 李 新A004A004A004A004A004A004A004A004A004A004A004A004A004A004A004A004 徐 凯A011A011A011A011A011A011A011A011A011A011A011A011A011A011A011A011 高连兴A001A001A001A001A001A001A001A001A001A001A001A001A001A001A001
20、A001 朱体高A019A019A019A019A019A019A019A019A019A019A019A019A019A019A019A019 许丽萍A012A012A012A012A012A012A012A012A012A012A012A012A012A012A012A012 薛滨峰A013A013A013A013A013A013A013A013A013A013A013A013A013A013A013A013 杨帆A015A015A015A015A015A015A015A015A015A015A015A015A015A015A015A015 廉欢A005A005A005A005A005A0
21、05A005A005A005A005A005A005A005A005A005A005 尹 静A016A016A016A016A016A016A016A016A016A016A016A016A016A016A016A016 张 勇A017A017A017A017A017A017A017A017A017A017A017A017A017A017A017A017 张国顺A014A014A014A014A014A014A014A014A014A014A014A014A014A014A014A014 17171818191920202121222223232424252526262727282829293
22、030313132323333 1616161616161616161616161616161616 99999999999999999 55555555555555555 22222222222222222 33333333333333333 66666666666666666 77777777777777777 88888888888888888 44444444444444444 1010101010101010101010101010101010 11111111111111111 1919191919191919191919191919191919 11111111111111111
23、11111111111111111 1212121212121212121212121212121212 1717171717171717171717171717171717 1818181818181818181818181818181818 1313131313131313131313131313131313 1414141414141414141414141414141414 1515151515151515151515151515151515 说说明明: 1.1.下下面面简简介介若若无无特特别别指指出出,均均以以第第三三行行公公式式为为例例; 2.2.作作为为公公式式,为为简简练练起起
24、见见,对对本本工工作作表表单单元元格格的的引引用用完完全全不不必必再再输输入入本本工工作作表表名名(本本表表B21:AJ21B21:AJ21张张国国顺顺行行已已做做修修改改,可可参参考考)但但 下下面面简简介介中中仍仍保保留留原原公公式式; 3.3.简简介介中中左左侧侧(即即A23:A57A23:A57)中中数数字字与与第第二二行行(B2:AJ2B2:AJ2)中中对对应应,简简介介区区域域中中的的颜颜色色也也与与上上面面表表格格对对应应,以以便便于于对对照照; 4.4.文文本本型型数数据据第第1 1至至第第2323简简介介与与数数值值型型一一样样,第第2424条条见见其其旁旁边边。 VLOOK
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 国家行业标准规范 水利工程、行业标准、国家标准、工程建设、规程规范、施工规范、设计规范。
限制150内