简单-聪明 / 我的图书馆 / EXCEL实用操作技巧 - postzsh的日志 - 网...

   

EXCEL实用操作技巧 - postzsh的日志 - 网易博客

2011-04-21  简单-聪明

EXCEL实用操作技巧

电脑资料 2008-11-19 22:58:47 阅读967 评论0   字号: 订阅

一、如何快速选定不相邻的多个单元格区域?

比如:要同时选定A1:A100D1:D100两个区域。

方法一:

在单元格地址栏直接输入:

A1:A100,D1:D100

回车。

方法二:

单击A1,按住SHIFT单击A100,按住CTRL(要松开SHIFT键)单击D1,按住SHIFT(要松开CTRL键)单击D100

二、如何用函数对两个工作表进行整行比较?

假定两个工作表(SHEET1SHEET2)均有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+回车结束。

将公式向下复制,直到出现空白。

四、用LOOKUPCHOOSE等函数替代IF函数进行多条件判断的方法一例:

A列为文本格式的数值(010203……4849),现要将其分为“左边”和“右边”两组标明在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*A1<24,"",""),"","")&""

公式二:

=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精英俱乐部)

比如当前工作薄中有SHEET1SHEET100100个工作表,现在你要在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)),"")

七、利用Ctrl+*快速选定整个工作表区域(收集):

  如果一个工作表中有很多数据表格时,可以通过选定表格中某个单元格,然后按下Ctrl+*键可选定整个表格。Ctrl+*选定的区域为:根据选定单元格向四周辐射所涉及到的有数据单元格的最大区域。这样我们可以方便准确地选取数据表格,并能有效避免使用拖动鼠标方法选取较大单元格区域时屏幕的乱滚现象。


八、在EXCEL中快速输入上标形式的平方、立方
(收集):

  按ALT+178输入平方,按ALT+179输入立方。并且用这种方法输入的上标“平方”和“立方”还可以用在函数和公式中。

九、VLOOKUP函数查找右边列的值后得到同一行左边列的值:

  比如在SHEET2表的B1单元格通过公式在SHEET1表的B列查找SHEET2表中A1的值,找到后返回SHEET1表中同一行中A列的值,公式如下:

=VLOOKUP(A1,IF({1,0},SHEET1!B$1:B$1000,SHEET1!A$1:A$1000),2,0)

公式可向下复制.

要点:公式中IF函数中的两个区域不能引用整列。否则公式会出错。

当然,这种公式其实可以用INDEX和MATCH两个函数代替。

十、如何将A1单元格中输入的数值累加到B2单元格中?

  先点"工具"->"选项"->"重新计算",选中"自动重算"和"迭代计算",将"最多迭代次数"设为1,然后在B2单元格输入公式:

=IF((CELL("ROW")=ROW()-1)*(CELL("COL")=1),A1+B2,B2)

这样设置好后,在A1输入的数值会自动累加的B2单元格中,而在其他单元格输入数值时,B2单元格的数值不会变化。

十一、快速输入数值尾部多个零的简便方法:

  在单元格中直接输入:

        =125**6

       单元格值自动会变为125000000.

       如果先将单元格格式设为自定义类型"0",就可以不用输入等号,直接输入125**6即可.

EXCEL实用操作技巧(二)

一、如何改变Excel中起始页的页号?

假如起始页页号要为7,点"视图"->"页眉页脚","自定义页脚," &[页码] "改为" &[页码]+6 ",点两次"确定"

二、Excel中当被引用单元格发生剪切操作时,如何保持引用单元格的内容仍然引用原位置的内容?

例如,A1等于B5,B5的内容剪切到C8,A1将等于C8的内容。如果要求不管B5作什么操作, A1始终要等于B5的内容,A1单元格公式如下:

=INDIRECT("B5")

三、如何在公式单元格前面插入或删除列时始终引用当前单元格左边相邻单元格的值?

比如C1单元格,目前引用的是B1单元格的值。要求,当在C列前面插入一列时,公式单元格(变为D1)仍然引用相邻左边单元格C1的值。而当在C列前面删除一列时,公式单元格(变为B1)仍然引用相邻左边单元格A1的值。

公式:

=OFFSET(A1,0,COLUMN()-2,1,1)

四、如何只显示(筛选)奇数行或偶数行?

在后面空白列(假定为F)的第2行输入筛选条件:
=MOD(ROW(A2),2)=0
选定该表所有数据列,"数据"->"筛选"->"高级筛选",条件区域选择:
=$F$1:$F$2
"确定"。这样就只显示偶数行。
如果要只显示奇数行,将公式改为:
=MOD(ROW(A2),2)

五、如何仅通过自定义单元格格式设置让数据缩小1000倍显示,并且千分位后的内容不显示?

比如让333000111显示成333,000

输入数字后,将单元格格式设为"自定义","类型"框中输入:
#,##0,""
"确定"

六、在excel表格中的涉密内容如何不让其显示?

exce记录合同信息,有些合同涉密,不希望显示出来,或者用*号显示,比如:在一个单元格里输入“合同名称”四个字,但我希望别人打开这个表格时看到的这个单元格里显示的是“****”,而我通过某种途径还可以知道这个“****”后面的内容是“合同名称”。

先在那个单元格输入公式:
=IF(IV1=123,"
合同名称","****")
然后选定全表,"格式->"单元格"->"保护",取消"锁定"前面的对勾。
再选定公式单元格,"格式->"单元格"->"保护",选中"锁定""隐藏"
"工具"->"保护"->"保护工作表",输入两次密码,"确定"
这样设置好后别人看到的就是星号,也不能对这个单元格删除和修改。
你自己要看的时候,点"工具"->"保护"->"撤消工作表保护",输入密码,这时点击该单元格时编辑栏可以看到公式内容,但单元格显示的还是星号。你再在IV1单元格输入123,该单元格就显示为“合同名称”了。

七、如何将excel表中所有的空单元格填上某个数字?

选定表格区域范围,"编辑"->"定位","条件定位",选中"空值","确定",输入某个数字,Ctrl+回车。

八、如何根据A列的身份证号码在B列判断女的是否大于40岁,男的是否大于50岁?

B1输入公式:
=IF(YEAR(TODAY())-1*TEXT((LEN(A1)=15)*19&MID(A1,7,2+(LEN(A1)=18)*2),"0000")>IF(MOD(MID(A1,15,3),2),50,40),"
","")
将公式向下复制。

九、如何计算单元格中数学表达式的值?

假定A1内容为21*17+5/4,要在B1单元格计算其结果。

单击B1,然后点“插入”菜单选择“名称”命令中的“定义”子命令,出现“定义名称”对话框。
在“在当前工作表中的名称”中输入定义的名称“X”,在下方的“引用位置”编辑栏中输入:
=EVALUATE(A1)
单击[确认]按钮退出。
B1中输入“=X (:不含引号)

公式可向下复制。

十、如何将EXCEL中的信息导入Outlook Express的联系人中?

EXCEL中点"文件"->"另存为","保存类型"中选择"CSV(逗号分隔)(*.csv)",文件名任起。 Outlook Express中点"文件"->"导入"->"其他通讯薄",选中"文本文件(以逗号分隔)","导入",找到你保存的*.csv文件,按提示分别导入各项内容即可。
Outlook 2003可以直接导入EXCEL文件(需要安装转换插件)。

十一、如何求单元格中任意数字的所有各个位数之和?

5126各位数的和5+1+2+6=14,公式如下:

=SUMPRODUCT(1*MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))

注:A1可以为任意位数字。

十二、如何让用户在C1单元格选择的月份后,A列中自动显示当年当月所有日期,而在B列自动填充对应的星期几?

假定C1单元格用数据有效性设置一个选择(1-12)月份的下拉菜单,A1单元格输入公式:
=IF(C$1="","",IF(C$1<>MONTH(DATE(YEAR(TODAY()),C$1,ROW())),"",DATE(YEAR(TODAY()),C$1,ROW())))
B1
单元格输入公式:
=IF(A1="","",TEXT(WEEKDAY(A1),"AAAA"))
将两个公式向下复制到第31行。

A列单元格格式设置为你要的日期格式。

十三、如何在单元格中引用工作表名、工作簿名、工作簿所在文件夹名?

在工作簿已经保存的情况下,CELL("filename")可以得到带完整路径的工作表名。用字符函数截取相应的部分即可。

引用工作表名:

=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

引用工作簿名:

=MID(CELL("filename"),FIND("[",CELL("filename"))+1,FIND("]",CELL("filename"))-FIND("[",CELL("filename"))-5)

引用工作簿所在文件夹名:

=TRIM(LEFT(RIGHT(SUBSTITUTE(CELL("filename"),"\",REPT(" ",100)),200),100))

十四、如何填充特殊序列(如第一期、第二期……,第壹号、第贰号……)?

有两种方法:

一是设置“自定义”单元格格式,“类型”分别为:

[DBNum1]""G/通用格式""

[DBNum2]""G/通用格式""

然后输入阿拉伯数字进行填充。

二是利用公式直接生成,公式分别为:

=""&TEXT(ROW(A1),"[DBNum1]G/通用格式")&""

=""&TEXT(ROW(A1),"[DBNum2]G/通用格式")&""

十五、EXCEL中如何使用自定义函数?

打开EXCEL,按Alt+F11调出VBA编辑器,点“插入”->“模块”,将自定义的函数模块内容加入进去。

比如“将单元格字符串反序输出”的函数模块:

Public Function MyStrReverse(ByVal sString As Variant) As String
       Application.Volatile
       Dim strResult As String
       strResult = StrReverse(sString)
       MyStrReverse = strResult
End Function

如果你要对软件保密的话,在模块上按右键,[VBAproject属性]→[保护中设置密码],别人就看不到你的源程序了。

退出VBA编辑器,返回到EXCEL界面,点“文件”->“另存为”,“保存类型”选择“Microsoft Office Excel加载宏”,取名为“字符串反序输出”,会自动加上扩展名.xla

使用时,点“工具”->“加载宏”,点“浏览”找到你保存的“字符串反序输出.xla”文件,点“确定”。

如果“可用加载宏”里面已经有此项,选中后点“确定”即可。

假定A1为“ijdf152中国人id897”,在B1直接输入自定义的函数:

= MyStrReverse(A1)

即可得到结果“798di人国中251fdji”。

十六、EXCEL中如何设置二级关联下拉菜单?
          将你的一级菜单的内容输入到SHEET1工作表的第一行(假定为A1:G1),选定A1:G1,将该区域命名为"一级"(注:不含引号),将一级菜单对应的二级菜单内容分别输入到A至G列第二行以下的各行中(假定为第2至10行)。
          在SHEET2中,第一行为表头,选定A2:A10(可根据你的需要改变范围),点"数据"->"有效性"->"设置",允许下面选择"序列",在来源框中输入
=一级
          点"确定"。
          用鼠标选定B2:B10,点"数据"->"有效性"->"设置",允许下面选择"序列",在来源框中输入下面的公式:
=INDIRECT("sheet1!"&CHAR(64+MATCH(A2,一级,0))&"2:"&CHAR(64+MATCH(A2,一级,0))&"10")
          点"确定"。
          这样SHEET2工作表中A2:A10有一级下拉菜单的内容供选择,B2:B10有对应的二级下拉菜单内容供选择。

十七、EXCEL中如何提取汉字的拼音首字母?(收集)
=IF(A1="","",LOOKUP(CODE(A1),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},{"A","B","C","D","E","F","G","H","J","K","L","M","N","O","P","Q","R","S","T","W","X","Y","Z"}))
或者:
=LOOKUP(CODE(A1),45217+{0,36,544,1101,1609,1793,2080,2560,2902,3845,4107,4679,5154,5397,5405,5689,6170,6229,7001,7481,7763,8472,9264},MID("ABCDEFGHJKLMNOPQRSTWXYZ",ROW($1:$23),1))

注:公式只对常用汉字有效,部分生辟汉字未按拼音排列,无法用公式找到。

十八、EXCEL中如何禁止输入完全相同的行

      假定你准备将数据输入到A1:C100区域(三列100行),先用鼠标选定该区域,点"数据"->"有效性"->"设置","允许"下面选择"自定义",在"公式"下面的框中输入:
=SUMPRODUCT(($A$1:$A$100=$A1)*($B$1:$B$100=$B1)*($C$1:$C$100=$C1))=1
点"确定"。

      这样就不能输入完全相同的行了,必须重新输入该行直到没有重复的行它才会接受你输入的数据。

如果你的列数超过三列,将公式作相应修改即可。

十九、如何查找某列中不重复的数据并连续存放在另一列中?

     假定原数据在A1:A100区域中,其中有若干数据重复,先将A1数据复制到B1,然后在B2单元格输入数组公式:

=IF(SUMPRODUCT(1/COUNTIF(A$1:A$100,A$1:A$100))<ROW(),"",INDEX(A:A,MIN(IF(COUNTIF(B$1:B1,A$1:A$100)=0,ROW(A$1:A$100)))))

   将公式向下复制,直到出现空白为止。
   
    补充:下面这个数组公式可以从B1单元格直接输入:
     =INDEX(A:A,SMALL(IF(MATCH(A$1:A$100,A$1:A$100,)=ROW(A$1:A$100),ROW(A$1:A$100),65536),ROW()))&""
       公式以CTRL+SHIFT+回车结束。

二十、如何将一个单元格中的数字去掉重复的后从小到大排序输出在另一个单元格中?

假定A1为209537233,要在B1得到023579,公式如下:
=IF(ISERROR(FIND(0,A1)),"","0")&SUBSTITUTE(SUM(IF(FIND(ROW($1:$9),A1&"123456789")<=LEN(A1),ROW($1:$9)*10^(9-ROW($1:$9)))),0,"")
公式以CTRL+SHIFT+回车结束

一、求字符串中某字符出现的次数:
例:求A1单元格中字符"a"出现的次数:
=LEN(A1)-LEN(SUBSTITUTE(A1,"a",""))
二、如何在不同工作薄之间复制宏:
1、打开含有宏的工作薄,点“工具/(M)…”,选中你的宏,点“编辑”,这样就调出了VB编辑器界面。
2、点“文件/导出文件”,在“文件名”框中输入一个文件名(也可用默认的文件名),注意扩展名为“.bas”,点“保存”。
3、将扩展名为“.bas”的文件拷贝到另一台电脑,打开EXCEL,点“工具//VB编辑器”,调出VB编辑器界面,点“文件/导入文件”,找到你拷贝过来的文件,点“打开”,退出VB编辑器,你的宏已经复制过来了。
三、如何在EXCEL中设置单元格编辑权限(保护部分单元格)
1、先选定所有单元格,"格式"->"单元格"->"保护",取消"锁定"前面的""
2、再选定你要保护的单元格,"格式"->"单元格"->"保护","锁定"前面打上""
3、点"工具"->"保护"->"保护工作表",输入两次密码,点两次"确定"即可。
四、excel中当某一单元格符合特定条件,如何在另一单元格显示特定的颜色
比如:
A11时,C1显示红色
0<A1<1时,C1显示绿色
A1<0时,C1显示黄色
方法如下:
1、单元击C1单元格,点“格式”>“条件格式”,条件1设为:
公式 =A1=1
2、点“格式”->“字体”->“颜色”,点击红色后点“确定”。
条件2设为:
公式 =AND(A1>0,A1<1)
3、点“格式”->“字体”->“颜色”,点击绿色后点“确定”。
条件3设为:
公式 =A1<0
点“格式”->“字体”->“颜色”,点击黄色后点“确定”。
4、三个条件设定好后,点“确定”即出。
五、EXCEL中如何控制每列数据的长度并避免重复录入
1、用数据有效性定义数据长度。
用鼠标选定你要输入的数据范围,点"数据"->"有效性"->"设置""有效性条件"设成"允许""文本长度""等于""5"(具体条件可根据你的需要改变)。
还可以定义一些提示信息、出错警告信息和是否打开中文输入法等,定义好后点"确定"
2、用条件格式避免重复。
选定A列,点"格式"->"条件格式",将条件设成“公式=COUNTIF($A:$A,$A1)>1”,点"格式"->"字体"->"颜色",选定红色后点两次"确定"
这样设定好后你输入数据如果长度不对会有提示,如果数据重复字体将会变成红色。
六、在EXCEL中如何把B列与A列不同之处标识出来?
(一)、如果是要求AB两列的同一行数据相比较:
假定第一行为表头,单击A2单元格,点“格式”->“条件格式”,将条件设为:
“单元格数值”“不等于”=B2
点“格式”->“字体”->“颜色”,选中红色,点两次“确定”。
用格式刷将A2单元格的条件格式向下复制。
B列可参照此方法设置。
(二)、如果是A列与B列整体比较(即相同数据不在同一行):
假定第一行为表头,单击A2单元格,点“格式”->“条件格式”,将条件设为:
“公式”=COUNTIF($B:$B,$A2)=0
点“格式”->“字体”->“颜色”,选中红色,点两次“确定”。
用格式刷将A2单元格的条件格式向下复制。
B列可参照此方法设置。
按以上方法设置后,AB列均有的数据不着色,A列有B列无或者B列有A列无的数据标记为红色字体。
七、在EXCEL中建立下拉列表按钮
选定你要设置下拉列表的单元格,点“数据”->“有效性”->“设置”,在“允许”下面选择“序列”,在“来源”框中输入你的下拉列表内容,各项之间用半角逗号隔开,如:
A,B,C,D
选中“提供下拉前头”,点“确定”。
八、阿拉伯数字转换为大写金额(最新收集)
假定你要在A1输入阿拉佰数字,B1转换成中文大写金额(含元角分),请在B1单元格输入如下公式:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(A1>-0.5%,,"负")&TEXT(INT(FIXED(ABS(A1))),"[dbnum2]")&TEXT(RIGHT(FIXED(A1),2),"[dbnum2]元0角0分;;元"&IF(ABS(A1)>1%,"整",)),"零角",IF(ABS(A1)<1,,"零")),"零元",),"零分","整")
九、EXCEL中怎样批量地处理按行排序
假定有大量的数据(数值),需要将每一行按从大到小排序,如何操作?
由于按行排序与按列排序都是只能有一个主关键字,主关键字相同时才能按次关键字排序。所以,这一问题不能用排序来解决。解决方法如下:
               1
、假定你的数据在AE列,请在F1单元格输入公式:
=LARGE($A1:$E1,COLUMN(A1))
用填充柄将公式向右向下复制到相应范围。
你原有数据将按行从大到小排序出现在FJ列。如有需要可用“选择性粘贴/数值”复制到其他地方。
注:第1步的公式可根据你的实际情况(数据范围)作相应的修改。如果要从小到大排序,公式改为:=SMALL($A1:$E1,COLUMN(A1))
十、巧用函数组合进行多条件的计数统计
例:第一行为表头,A列是“姓名”,B列是“班级”,C列是“语文成绩”,D列是“录取结果”,现在要统计“班级”为“二”,“语文成绩”大于等于104,“录取结果”为“重本”的人数。统计结果存放在本工作表的其他列。
公式如下:
=SUM(IF((B2:B9999="二")*(C2:C9999>=104)*(D2:D9999="重本"),1,0))
输入完公式后按Ctrl+Shift+Enter,让它自动加上数组公式符号"{}"
十一、EXCEL中某个单元格内文字行间距调整方法。
当某个单元格内有大量文字时,很多人都觉得很难将其行间距按自己的要求进行调整。现介绍一种方法可以让你任意调整单元格内文字的行间距:
右击单元格,"设置单元格格式"->"对齐","水平对齐"选择"靠左","垂直对齐"选择"分散对齐",选中"自动换行",点“确定”。你再用鼠标将行高根据你要求的行距调整到适当高度即可。
注:绿色内容为关键点,很多人就是这一点设置不对而无法调整行间距。
十二、如何在EXCEL中引用当前工作表名
如果你的工作薄已经保存,下面公式可以得到单元格所在工作表名:
=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))
十三、相同格式多工作表汇总求和方法
假定同一工作薄有SHEET1至SHEET100共100个相同格式的工作表需要汇总求和,结果放在SHEET101工作表中,请在SHEET101的A1单元格输入:
=SUM(
单击SHEET1标签,按住Shift键并单击SHEET100标签,单击A1单元格,再输入:
此时公式看上去内容如下:
=SUM('SHEET1:SHEET100'!A1)
按回车后公式变为               
=SUM(SHEET1:SHEET100!A1)
所以,最简单快捷的方法就是在SHEET101的A1单元格直接输入公式:
=SUM('SHEET1:SHEET100'!A1)
然后按回车。
十四、如何判断单元格里是否包含指定文本?
假定对A1单元格进行判断有无"指定文本",以下任一公式均可:
=IF(COUNTIF(A1,"*"&"指定文本"&"*")=1,"有","无")
=IF(ISERROR(FIND("指定文本",A1,1)),"无","有")
十五、如何替换EXCEL中的通配符“?”和“*”?
在EXECL中查找和替换时,?代表任意单个字符,*代表任意多个字符。如果要将工作表中的"?"和"*"替换成其他字符,就只能在查找框中输入~?~和~*~才能正确替换。另外如果要替换~本身,在查找框中要输入~~才行。
十六、EXCEL中排名次的两种方法:
(一)、用RANK()函数:
假定E列为成绩,F列为名次,F2单元格公式如下:
=RANK(E2,E:E)
这种方法,分数相同时名次相同,随后的名次将空缺。
例如:两个人99分,并列第2名,则第3名空缺,接下来是第4名。

(二)、用公式排序(中国式排名):
假定成绩在E列,请在F2输入公式:
=SUM(IF(E$2:E$1000>E2,1/COUNTIF(E$2:E$1000,E$2:E$1000)))+1
公式以Ctrl+Shift+Enter三键结束。

第二种方法分数相同的名次也相同,不过随后的名次不会空缺。
十七、什么是单元格的相对引用、绝对引用和混合引用?
相对引用、绝对引用和混合引用是指在公式中使用单元格或单元格区域的地址时,当将公式向旁边复制时,地址是如何变化的。
具体情况举例说明:
1、相对引用,复制公式时地址跟着发生变化,如C1单元格有公式:=A1+B1
当将公式复制到C2单元格时变为:=A2+B2
当将公式复制到D1单元格时变为:=B1+C1
      
2、绝对引用,复制公式时地址不会跟着发生变化,如C1单元格有公式:=$A$1+$B$1
当将公式复制到C2单元格时仍为:=$A$1+$B$1
当将公式复制到D1单元格时仍为:=$A$1+$B$1
       
3、混合引用,复制公式时地址的部分内容跟着发生变化,如C1单元格有公式:=$A1+B$1
当将公式复制到C2单元格时变为:=$A2+B$1
当将公式复制到D1单元格时变为:=$A1+C$1
       
规律:加上了绝对地址符“$”的列标和行号为绝对地址,在公式向旁边复制时不会发生变化,没有加上绝对地址符号的列标和行号为相对地址,在公式向旁边复制时会跟着发生变化。混合引用时部分地址发生变化。
注意:工作薄和工作表都是绝对引用,没有相对引用。
技巧:在输入单元格地址后可以按F4键切换“绝对引用”、“混合引用”和“相对引用”状态。
十八、求某一区域内不重复的数据个数
例如求A1:A100范围内不重复数据的个数,某个数重复多次出现只算一个。有两种计算方法:
一是利用数组公式:
=SUM(1/COUNTIF(A1:A100,A1:A100))
输入完公式后按Ctrl+Shift+Enter,让它自动加上数组公式符号"{}"
二是利用乘积求和函数:
=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))
十九、EXCEL中如何动态地引用某列的最后一个单元格?
在SHEET2中的A1单元格中引用表SHEET1中的A列的最后一个单元格中的数值(SHEET1中A列的最后一个单元格的数值不确定,随时会增加行数):
=OFFSET(Sheet1!A1,COUNTA(Sheet1!A:A)-1,0,1,1)
或者:
               =INDIRECT("sheet1!A"&COUNTA(Sheet1!A:A))
注:要确保你SHEET1的A列中间没有空格。
二十、如何在一个工作薄中建立几千个工作表
右击某个工作表标签,点"插入",选择"工作表",点"确定",然后按住Alt+Enter键不放,你要多少个你就按住多久不放,你会看到工作表数量在不断增加,几千个都没有问题。
二十一、如何知道一个工作薄中有多少个工作表
方法一:
点"工具"->"宏"->"VB编辑器"->"插入"->"模块",输入如下内容:
Sub sheetcount()
Dim num As Integer
num = ThisWorkbook.Sheets.Count
Sheets(1).Select
Cells(1, 1) = num
End Sub
运行该宏,在第一个(排在最左边的)工作表的A1单元格中的数字就是sheet的个数。

方法二:
按Ctrl+F3(或者点"插入"->"名称"->"定义"),打开"定义名称"对话框
定义一个X
"引用位置"输入:
=get.workbook(4)
点"确定"。
然后你在任意单元格输入=X
出来的结果就是sheet的个数。
二十二、一个工作薄中有许多工作表如何快速整理出一个目录工作表
            1、用宏3.0取出各工作表的名称,方法:
            Ctrl+F3出现自定义名称对话框,取名为X,在“引用位置”框中输入:
=MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1))+1,100)
确定

             2、用HYPERLINK函数批量插入连接,方法:
             在目录工作表(一般为第一个sheet)的A2单元格输入公式:
=HYPERLINK("#'"&INDEX(X,ROW())&"'!A1",INDEX(X,ROW()))
将公式向下填充,直到出错为止,目录就生成了。

    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。如发现有害或侵权内容,请点击这里 或 拨打24小时举报电话:4000070609 与我们联系。

    0条评论

    发表

    请遵守用户 评论公约

    类似文章
    喜欢该文的人也喜欢 更多

    ×
    ×

    ¥.00

    微信或支付宝扫码支付:

    《个图VIP服务协议》

    全部>>