EXCEL实用操作技巧资料.docVIP

EXCEL实用操作技巧 一、如何快速选定不相邻的多个单元格区域？ 比如：要同时选定A1:A100和D1:D100两个区域。 方法一： 在单元格地址栏直接输入： A1:A100,D1:D100 回车。 方法二： 单击A1，按住SHIFT单击A100，按住CTRL（要松开SHIFT键）单击D1，按住SHIFT（要松开CTRL键）单击D100。 二、如何用函数对两个工作表进行整行比较？ 假定两个工作表（SHEET1和SHEET2）均有4列，行数和数据顺序不一样，现在要找到两个表中完全相同的记录。请在SHEET1表的E1输入公式： =IF(SUMPRODUCT((Sheet2!A$1:A$1000=A1)*(Sheet2!B$1:B$1000=B1)*(Sheet2!C$1:C$1000=C1)*(Sheet2!D$1:D$1000=D1)),有,) 将公式向下复制。 将SHEET1表按E列排序，所有E列含有“有”的行就集中在一起了。 三、如何查找某个数据在一个单元格区域中的位置，并得到其所有单元格名称？ 假定要在A1:E100区域查找“中国”这个词所在的所有单元格的名字，先将A1:E100区域定义名称为“DATA”（主要是缩短公式并增加通用性），然后在F1单元格输入公式： =IF(COUNTIF(DATA,中国),IF(ROW()COUNTIF(DATA,中国),,ADDRESS(INT(SMALL(IF(DATA=中国,ROW(DATA)*100+COLUMN(DATA)),ROW())/100),MOD(SMALL(IF(DATA=中国,ROW(DATA)*100+COLUMN(DATA)),ROW()),100),4,1)),没有) 公式以CTRL+SHIFT+回车结束。 将公式向下复制，直到出现空白。 四、用LOOKUP、CHOOSE等函数替代IF函数进行多条件判断的方法一例： A列为文本格式的数值（01、02、03……48、49），现要将其分为“左边”和“右边”两组标明在B列，分组条件是01 02 03 04 08 09 10 11 15 16 17 18 22 23 24 29 30 31 36 37 38 43 44 45为“左边”，05 06 07 12 13 14 19 20 21 25 26 27 28 32 33 34 35 39 40 41 42 46 47 48 49为“右边”。 公式一： =CHOOSE(MOD(1*A1,7)+1,右,左,左,左,IF(1*A124,左,右),右,右)边 公式二： =IF(SUMPRODUCT((1*A1={1,8,15,22,29,36,43})*(1*A1={4,11,18,24,31,38,45})),左,右)边 公式三： =IF(ISERR(FIND(A1,01 02 03 04 08 09 10 11 15 16 17 18 22 23 24 29 30 31 36 37 38 43 44 45)),右边,左边) 公式四： =LOOKUP(1*A1,{1,5,8,12,15,19,22,25,29,32,36,39,43,46;左,右,左,右,左,右,左,右,左,右,左,右,左,右})边 从公式长度来看，公式一是最短的，但是它需要数据有一定规律才能实现。公式四是最长的，但是它只用了一个函数，对于按照数值大小来分组（可以分为更多的组）的条件判断最适合，具有通用性。公式二和公式三只适合将数据分为两组的情况，如果分为多组公式会很长。另外，公式三要求原数据必须为“文本”格式，其他三个公式无此要求（既可以是文本也可以是数值格式）。 在实际工作中可以根据需要灵活使用不同的公式。 五、用星号(*)代替工作表名称快速输入公式（收集于OFFICE精英俱乐部） 比如当前工作薄中有SHEET1至SHEET100共100个工作表，现在你要在SHEET1工作表的A1单元格对后面99个工作表的B1单元格求和，可输入公式： =SUM(‘*’!B1) 回车后公式自动变为： =SUM(SHEET2:SHEET100!B1) 也就是说，公式中’*’可以代替本工作薄中除本工作表以外的所有工作表名。 六、利用公式快速制作工资条(隔行插入表头)的方法: 假定原数据在SHEET1中，第一行为表头，请在SHEET2的A1单元格输入公式： =IF(MOD(ROW(),2),Sheet1!A$1,OFFSET(Sheet1!$A$1,ROW()/2,COLUMN()-1,,)) 将公式向右向下复制就可以了。??? 如果插入表头之后还要插入一个空行，A1公式改为： =CHOOSE(MOD(ROW()-1,3)+1,SHEET1!A$1,INDEX(SHEET1!A:A,INT((ROW()+2)/3)),) 七