EXCEL实用操作技巧Word格式文档下载.docx

EXCEL实用操作技巧Word格式文档下载.docx

《EXCEL实用操作技巧Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《EXCEL实用操作技巧Word格式文档下载.docx（14页珍藏版）》请在冰豆网上搜索。

有"

"

"

）

将公式向下复制。

将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列，分组条件是010203040809101115161718222324293031363738434445为“左边”，05060712131419202125262728323334353940414246474849为“右边”。

公式一：

=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,"

010203040809101115161718222324293031363738434445"

））,"

右边"

左边"

公式四：

=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!

也就是说，公式中’*’可以代替本工作薄中除本工作表以外的所有工作表名。

六、利用公式快速制作工资条（隔行插入表头）的方法:

假定原数据在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$1000,SHEET1!

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中的信息导入OutlookExpress的联系人中？

在EXCEL中点"

文件"

另存为"

保存类型"

中选择"

CSV（逗号分隔）（*.csv）"

文件名任起。

在OutlookExpress中点"

导入"

其他通讯薄"

文本文件（以逗号分隔）"

找到你保存的*.csv文件,按提示分别导入各项内容即可。

Outlook2003可以直接导入EXCEL文件（需要安装转换插件）。

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

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

=SUMPRODUCT（1*MID（A1,ROW（INDIRECT（"

1:

&

LEN（A1）））,1））

注：

A1可以为任意位数字。

十二、如何让用户在C1单元格选择的月份后，A列中自动显示当年当月所有日期，而在