数组的一些特殊用途.xls
《数组的一些特殊用途.xls》由会员分享,可在线阅读,更多相关《数组的一些特殊用途.xls(32页珍藏版)》请在咨信网上搜索。
1、数数组组的的一一些些特特殊殊用用途途(ChenJun 青青牛牛在在线线)一、扩充区域二、交叉区域三、隐含的交叉区域四、合并区域(多单元格数组公式)五、数组的运算 数组运算的说明(NEW)六、简单的查找(返回右或左列的数据)七、多条件的查找(返回右或左列的数据)八、二维查找九、在两列中求出和为指定数的值(打破EXCEL工作表函数中数组最多为256列的限制)(新增一个方法NEW)十、随心所欲的转换区域(NEW)十一、从区域返回不重复值(兼论数组公式的运算效率)(NEW)十二、判别一个数是否为质数的数组公式(NEW)一一、扩扩充充区区域域将将一一列列扩扩充充为为多多列列(是是多多单单元元格格数数组组
2、公公式式,选选中中区区域域后后输输入入公公式式,按按Ctrl+Shfit+Enter)区域为数值时1121 转换公式为=A4:A8*1,2,122423363448455105区域为文本时AAA2A3转换公式为=A11:A14&,2,3BBB2B3CCC2C3DDD2D3将将一一行行扩扩充充为为多多行行(是是多多单单元元格格数数组组公公式式,选选中中区区域域后后输输入入公公式式,按按Ctrl+Shfit+Enter)区域为数值时区域为文本时123ABC公式为=A18:C18*1;2;3公式为=E18:G18&1;2;3,注意;号123A1B1C1246A2B2C2369A3B3C3二二、交交叉
3、叉区区域域110220公式为=A28:D29 B26:C30,注意公式中两个区域间的空格AB30 DB30C440 E440550三三、隐隐含含的的交交叉叉区区域域1隐含行交叉,公式为=A33:A382相当于=A33:A38 35:35公式为=A33:A38 35:3533比较公式345612342 隐含列交叉,公式为=A40:D40,相当于=A40:D40 B:B比较公式2 公式为=A40:D40 B:B四四、合合并并区区域域(多多单单元元格格数数组组公公式式)合合并并列列区区域域为数值时23合并公式=B49:B53*1,0+D48:D52*0,11241232252243263254274
4、265527为文本时A合并公式=IF(COLUMN(F56:G59)=COLUMN(F56:F59),B55:B58,D56:D59)BWAWCXBXDYCYZDZ合合并并行行区区域域为数值时123合并公式=A62:C62*1;0+B64:D64*0;1,注意“;”号123456456为文本时ABC合并公式=IF(ROW(F66:H68)=ROW(F66:H66),A66:C66,B68:D68)ABCEFGEFG五五、数数组组的的运运算算同同行行列列区区域域的的运运算算,就就是是将将数数组组中中的的对对应应元元素素进进行行运运算算并并返返回回数数组组101002001102020200300
5、2203030300400330404040050044050990180 运算公式=A72:C75-E72:G75181802702727036036360450矩矩阵阵相相乘乘运运算算(第第一一个个数数组组的的列列数数要要和和第第二二个个数数组组的的行行数数相相同同)1223101334452229563445不不同同行行列列数数的的数数组组运运算算区域1区域2区域311011211112021221313214142区域1区域2,公式为=A87:A91*C87:E87区域1区域2区域3,公式为=A87:A91*C87:E87*G87:I91101101011100000010131010
6、1410六六、简简单单的的查查找找(返返回回右右或或左左列列的的数数据据)最最简简单单的的查查找找,按按左左列列返返回回右右边边的的列列的的数数据据,直直接接用用VLOOKUP函函数数查找列返回列AData1查找CBData2返回Data3公式=VLOOKUP($E$102,$A$102:$B$106,2,0)CData3DData4EData5简简单单的的查查找找,按按右右列列查查找找返返回回左左边边列列的的数数据据返回列查找列Data1A用INDEX和MATCH函数的方法Data2B查找CData3C返回Data3公式=INDEX($A$109:$A$113,MATCH($E$110,$B
7、$109:$B$113,0)Data4DData5E有有人人说说非非要要用用VLOOKUP函函数数怎怎么么办办?见见下下例例用用数数组组公公式式查找C返回Data3公式=VLOOKUP($B$115,IF(COLUMN($B$109:$C$113)=COLUMN($B$109:$B$113),$B$109:$B$113,$A$109:$A$113),2,0)按行查找并返回其他行的数据可参考上述方法,用HLOOKUP函数,COLUMN函数换为ROW函数七七、多多条条件件的的查查找找(返返回回右右或或左左列列的的数数据据)返返回回右右边边列列的的数数据据查找列1查找列2返回列用INDEX和MATC
8、H函数的方法(数组公式)A121 Data1查找列1A12A122 Data2查找列21B112 Data3返回Data4A121 Data4公式=INDEX($C$123:$C$127,MATCH($F$123&xyz&$F$124,$A$123:$A$127&xyz&$B$123:$B$127,0)A11 Data5xyz是为了区分A1&21和A12&1出现相同的值,可换为其他的特殊字符组合用VLOOKUP函数的数组公式方法查找列1A12查找列21返回Data4公式=VLOOKUP($B$129&xyz&$B$130,IF(COLUMN($A$123:$B$127)=COLUMN($A$1
9、23:$A$127),$A$123:$A$127&xyz&$B$123:$B$127,$C$123:$C$127),2,0)返返回回的的列列在在其其他他位位置置查找列1返回列查找列2用INDEX和MATCH函数的方法(数组公式)A1Data111查找列1A1A12Data21查找列221A12Data311返回Data5A1Data41公式=INDEX($B$135:$B$140,MATCH($F$135&xyz&$F$136,$A$135:$A$140&xyz&$C$135:$C$140,0)A1Data521A1Data6211用VLOOKUP函数的数组公式方法查找列1A1公式=VLOOK
10、UP($B$142&xyz&$B$143,IF(COLUMN($A$135:$B$140)=COLUMN($A$135:$A$140),$A$135:$A$140&xyz&$C$135:$C$140,$B$135:$B$140),2,0)查找列221返回Data5八八、二二维维查查找找普普通通的的二二维维查查找找条件二B1B2B3B4用INDEX和MATCH函数的公式,返回区域可为任何类型数据条件一A11234条件一A25678条件二A39101112返回A413141516公式=INDEX($C$150:$F$155,MATCH($I$150,$B$150:$B$155,0),MATCH($
11、I$151,$C$149:$F$149,0)A517181920A621222324用数组公式,返回区域是数值类型,找不到返回0条件一A3公式=SUM($B$150:$B$155=$B$157)*($C$149:$F$149=$B$158)*$C$150:$F$155)条件二B3注意公式中$B$150:$B$155、$C$149:$F$149、$C$150:$F$155为不同行列数的区域返回11如返回区域为文本,用自定义文本连接函数contxt的数组公式,找不到返回空条件二B1B2B3B4条件一A1Data1Data2Data3Data4A2Data5Data6Data7Data8A3Data
12、9Data10Data11Data12A4Data13Data14Data15Data16A5Data17Data18Data19Data20A6Data21Data22Data23Data24条件一A3条件二B3返回#NAME?公式=contxt(IF($B$163:$B$168=$C$169)*($C$162:$F$162=$C$170),$C$163:$F$168,)上例中用VLOOKUP和MATCH函数进行查找的公式返回Data11公式=VLOOKUP($C$169,$B$163:$F$168,MATCH($C$170,$C$162:$F$162,0)+1,0)复复杂杂的的二二维维查查
13、找找条件三条件一条件二C1C2C3C4注意条件一所在列有合并单元格A1B11234B25678A2B29101112A3B213141516B117181920A4B121222324B325262728用数组公式,返回区域是数值类型,找不到返回0条件一A3公式=SUM(IF($A$177:$A$183=,$A$176:$A$182,$A$177:$A$183)=$C$185)*($B$177:$B$183=$C$186)*($C$176:$F$176=$C$187)*($C$177:$F$183)注意区域$A$177:$A$183和$A$176:$A$182的行错开一行条件二B1条件三C3返
14、回19如返回区域为文本,用自定义文本连接函数contxt的数组公式,找不到返回空返回#NAME?公式=contxt(IF(IF($A$177:$A$183=,$A$176:$A$182,$A$177:$A$183)=$C$185)*($B$177:$B$183=$C$186)*($C$176:$F$176=$C$187),$C$177:$F$183,)十十、随随心心所所欲欲的的转转换换区区域域(NEW)定义为Data的区域(在“插入名称定义”中定义)1231A2A3A1B2B3B1C2C3C1D2D3D1E2E3E1F2F3F112131新老区域的单元格总数相同,见下例123 1A2A3A1B
15、2B3B1C2C3C1D2D3D1E2E3E1F2F3F112131如新区域的单元格数多于原区域,多出的部分单元格值为#REF!,见下例123 1A2A13A1B2B3B1C52C3C1D2D3D91E2E3E1F2F133F112131#REF!17#REF!#REF!#REF!#REF!#REF!21如新区域的单元格数少于原区域,忽略原区域中后面的单元格123 1A2A3A1B2B3B1C2C3C1D2D3D此两例不作详解了,提示:按红色区域表示的内存数组进行的索引有没有超出Data区域按按先先列列后后行行的的顺顺序序排排列列至至新新区区域域(多多单单元元格格数数组组公公式式,选选择择区区
16、域域后后输输入入公公式式,按按ctrl+shift+enter)1 1F2D3B1A11 2E3C1B2 2F3D1C2A21 3E1D2B3 3F1E2C3A31原理同上,看一下区域中的公式就可明白按按先先行行后后列列的的顺顺序序排排列列至至单单列列区区域域(多多单单元元格格数数组组公公式式,选选择择区区域域后后输输入入公公式式,按按ctrl+shift+enter)可可简简化化公公式式,见见右右例例看看完完这这一一章章,可可以以体体会会到到随随心心所所欲欲转转换换区区域域的的乐乐趣趣了了吧吧快快试试一一试试!十十一一、从从区区域域返返回回不不重重复复值值(兼兼论论数数组组公公式式的的运运算
17、算效效率率)(NEW)从从一一列列数数据据中中返返回回不不重重复复值值的的多多单单元元格格和和单单个个单单元元格格数数组组公公式式的的比比较较定义为Data1的单列区域AAABBBCCC111B22A#NUM!#NUM!2#NUM!#NUM!1#NUM!#NUM!上面的两个公式均存在缺点,1.多于不重复值数量的单元格中会出现错误#NUM!;2.如黄色的Data1区域中有空白单元格,就全部出错#N/A(你可试着删除区域中一个单元格看看)改进后的公式如下AABBCC1122再试着清空Data1区域的某一格,是不是均不出现错误了。那么单个和多个单元格数组公式有什么差别?1.单个单元格数组公式使用比较
18、灵活,需要多少行就拖动多少行;2.单个单元格数组公式好理解,修改方便,可像普通公式那样修改及移动位置,只是不要忘了用ctrl+shift+enter三个键输入;3.多单元格数组公式理解困难,修改也难,要全部选中原来的公式输入区域(按ctrl+/),按F2键或点公式编辑栏进行修改;4.多单元格数组公式的输入区域如要变化,必须先选中原输入区域(按ctrl+/),再按ctrl+enter转化为普通公式,注意不要管出现的错误,然后重新选择区域,输入多单元格数组公式;5.多单元格数组公式运算效率比单个单元格数组公式高,如上例,两种方法均先在内存中生成一个数组,多单元格数组公式将这个数组的各元素依次放入每
19、个单元格,实际是算了一遍就返回了所有单元格的数据,而单个单元格数组公式只在内存数组中按位置挑了一个数据返回到一个单元格中,对其他的单元格还要从头算一遍,可以近似地看为多单元格数组公式算完全部的时间和单个单元格数组公式算出一个单元格的时间相同。因因此此用用数数组组公公式式的的单单元元格格越越多多就就越越体体现现多多单单元元格格数数组组公公式式的的速速度度。大家可在两个新工作薄中按上述两种方法分别做一个在1000个单元格区域中返回不重复值的数组公式试试111 公式为=A4:A81,2,3248392741664525125点点击击返返回回目目录录点点击击返返回回目目录录将将一一列列扩扩充充为为多多
20、列列(是是多多单单元元格格数数组组公公式式,选选中中区区域域后后输输入入公公式式,按按Ctrl+Shfit+Enter)转换公式为=A11:A14&,2,3将将一一行行扩扩充充为为多多行行(是是多多单单元元格格数数组组公公式式,选选中中区区域域后后输输入入公公式式,按按Ctrl+Shfit+Enter)公式为=E18:G18&1;2;3,注意;号公式为=A28:D29 B26:C30,注意公式中两个区域间的空格公式为=A33:A38 35:35点点击击返返回回目目录录点点击击返返回回目目录录1.可用于数组的运算基本与单个数据间可用的运算一样多,如+、-、*、/、运算、文本的连接运算&、逻辑比较
21、运算等,并可用于函数中。2.两个同行列数的数组运算,前面已讲过是对应元素间进行运算,并返回同样大小的数组;3.一个数组与一个单一的数据(可为单个单元格的引用或一个常数)进行运算,是将数组的每一元素均与那个单一数据进行运算,并返回同样大小的数组;4.一个单行的数组与一个单列的数组的运算返回一个多行列的数组(行数同单列数组、列数同单行数组),数组中第N行第M列的元素是单列数组的第N个元素和单行数组的第M个元素运算的结果;5.一个单列的数组与一个多行列的数组的运算比较复杂,如果单列数组的行数与多行列数组的行数相同,就返回与多行列数组同样大小的数组,单列数组的每一元素分别与多行列数组中的每一列的对应元
22、素进行运算,见下例:(用多单元格数组公式的方法输入)11-1公式=MMULT(A82:B84,D82:E83)2*2-2运算方式可查阅线性代数书33-344-4如果单列数组的行数与多行列数组的行数不同,就返回与多行列数组同样列数、行数为两个数组中行数多的那个返回数组的大于小行数数组行数的行的元素为#N/A,应用于公式时,要适当考虑对错误的处理,数据的计算同上例,合并公式=B49:B53*1,0+D48:D52*0,1合并公式=IF(COLUMN(F56:G59)=COLUMN(F56:F59),B55:B58,D56:D59)合并公式=A62:C62*1;0+B64:D64*0;1,注意“;”
23、号合并公式=IF(ROW(F66:H68)=ROW(F66:H66),A66:C66,B68:D68)3见下例:1311-1232*2-23333-3434313单行数组与多行列数组的运算规律也是一样的,不再多说。06.两个不同行列数的多行列数组的运算,按两个数组的最大行和列数返回数组,超出两个数组中最小行列数的33元素为#N/A,有效值的元素为两个数组的对应位置元素的计算结果,见下例:43121点点击击返返回回目目录录2-1*20234再举一个例:将第本例中的运算后的数组按条件求和(条件为=4)没有错误处理的公式结果#N/A#N/A经过错误处理的公式结果10107.提示:很多书中说到数组运算
24、时均提示要保证行列数相同,其实是怕大家没了解清楚数组运算规律而经常出错,了解清楚后可以有意识的尝试不同行列数组的运算。如还没看清楚规律,那就是我的表达的问题了,只有继续按常规应用了。8.AND和OR函数用于逻辑数组时只返回一个值,NOT函数可返回数组,其他的如ROWS、COLUMNS、MIN、MAX等函数只返回一个值。点点击击返返回回目目录录区域1区域2区域3,公式为=A87:A91*C87:E87*G87:I91公式=VLOOKUP($E$102,$A$102:$B$106,2,0)公式=INDEX($A$109:$A$113,MATCH($E$110,$B$109:$B$113,0)公式=
25、VLOOKUP($B$115,IF(COLUMN($B$109:$C$113)=COLUMN($B$109:$B$113),$B$109:$B$113,$A$109:$A$113),2,0)按行查找并返回其他行的数据可参考上述方法,用HLOOKUP函数,COLUMN函数换为ROW函数用INDEX和MATCH函数的方法(数组公式)公式=INDEX($C$123:$C$127,MATCH($F$123&xyz&$F$124,$A$123:$A$127&xyz&$B$123:$B$127,0)xyz是为了区分A1&21和A12&1出现相同的值,可换为其他的特殊字符组合点点击击返返回回目目录录A3B3
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数组 一些 特殊 用途
1、咨信平台为文档C2C交易模式,即用户上传的文档直接被用户下载,收益归上传人(含作者)所有;本站仅是提供信息存储空间和展示预览,仅对用户上传内容的表现方式做保护处理,对上载内容不做任何修改或编辑。所展示的作品文档包括内容和图片全部来源于网络用户和作者上传投稿,我们不确定上传用户享有完全著作权,根据《信息网络传播权保护条例》,如果侵犯了您的版权、权益或隐私,请联系我们,核实后会尽快下架及时删除,并可随时和客服了解处理情况,尊重保护知识产权我们共同努力。
2、文档的总页数、文档格式和文档大小以系统显示为准(内容中显示的页数不一定正确),网站客服只以系统显示的页数、文件格式、文档大小作为仲裁依据,平台无法对文档的真实性、完整性、权威性、准确性、专业性及其观点立场做任何保证或承诺,下载前须认真查看,确认无误后再购买,务必慎重购买;若有违法违纪将进行移交司法处理,若涉侵权平台将进行基本处罚并下架。
3、本站所有内容均由用户上传,付费前请自行鉴别,如您付费,意味着您已接受本站规则且自行承担风险,本站不进行额外附加服务,虚拟产品一经售出概不退款(未进行购买下载可退充值款),文档一经付费(服务费)、不意味着购买了该文档的版权,仅供个人/单位学习、研究之用,不得用于商业用途,未经授权,严禁复制、发行、汇编、翻译或者网络传播等,侵权必究。
4、如你看到网页展示的文档有www.zixin.com.cn水印,是因预览和防盗链等技术需要对页面进行转换压缩成图而已,我们并不对上传的文档进行任何编辑或修改,文档下载后都不会有水印标识(原文档上传前个别存留的除外),下载后原文更清晰;试题试卷类文档,如果标题没有明确说明有答案则都视为没有答案,请知晓;PPT和DOC文档可被视为“模板”,允许上传人保留章节、目录结构的情况下删减部份的内容;PDF文档不管是原文档转换或图片扫描而得,本站不作要求视为允许,下载前自行私信或留言给上传者【fq****56】。
5、本文档所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用;网站提供的党政主题相关内容(国旗、国徽、党徽--等)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。
6、文档遇到问题,请及时私信或留言给本站上传会员【fq****56】,需本站解决可联系【 微信客服】、【 QQ客服】,若有其他问题请点击或扫码反馈【 服务填表】;文档侵犯商业秘密、侵犯著作权、侵犯人身权等,请点击“【 版权申诉】”(推荐),意见反馈和侵权处理邮箱:1219186828@qq.com;也可以拔打客服电话:4008-655-100;投诉/维权电话:4009-655-100。