excel实用操作技巧.docx

excel实用操作技巧.docx

《excel实用操作技巧.docx》由会员分享，可在线阅读，更多相关《excel实用操作技巧.docx（21页珍藏版）》请在冰豆网上搜索。

excel实用操作技巧

EXCEL实用操作技巧

（二）

2007-09-2910:

36

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列中自动显示当年当月所有日期，而在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编辑器，点“插入”->“模块”，将自定义的函数模块内容加入进去。

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

PublicFunctionMyStrReverse（ByValsStringAsVariant）AsString

Application.Volatile

DimstrResultAsString

strResult=StrReverse（sString）

MyStrReverse=strResult

EndFunction

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

退出VBA编辑器，返回到EXCEL界面，点“文件”->“另存为”，“保存类型”选择“MicrosoftOfficeExcel加载宏”，取名为“字符串反序输出”，会自动加上扩展名.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））

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+回车结束

Excel二级下拉菜单的3种方法

2009-05-2400:

24

把excelhome的论坛关于下拉菜单的贴看了不少。

发现实现2级的下拉菜单的方法好像也有好几种，我想我就做一个总结，自己也提高一下。

通过2级菜单，倒是可以比较深入熟悉INDIRECT,INDEX,OFFSET,这三个函数，是你要想学excel函数，估计你肯定过的关。

在论坛里看帖子，有一点比较累，很多都是直接用excel文件里做说明，你需要下载文件。

我这里就全部用图来说明。

不过二级菜单的第一级的做法，都是相同的，大家可以参考上面的文章创建一个一级菜单。

第一种做法：

使用函数INDIRECT

看看例子

很明显，我们希望在一级选择机房故障，那么2级就出现他下面的列表。

要实现这个目标并不复杂。

1：

建立“名称

3个一级选项，我们需要建立3个“名称”：

机房故障线路故障用户端故障。

这个时候，你就创建了3个名称

还需要建立一个名称，叫做“故障类型”这个名称包括“机房故障线路故障用户端故障”这3项。

这个时候，你就可以在“名称管理器”看到4个名称

2：

创建1级菜单

1级菜单的办法就简单了

这样就解决了一级的下拉菜单。

3：

2级菜单

这个地方你需要注意，=INDIRECT（$F2）

如果你写成=INDIRECT（$F$2），

如果是这样的话，你确定会出现一个报错

这个时候，你就实现了2级菜单

第二种做法：

使用函数INDEX

还是上面的例子，用另外一种方式来实现。

这个例子里，你只需要建立2个名称。

1：

建立名称

建立：

故障类型名称，方法和上面一样。

建立：

2级　　名称

创建上面两个名称。

2：

创建下拉菜单

第一下拉菜单和上面一样，我就不重复了，现在是演示第二级下拉菜单

=INDEX（二级,0,match（I$2,故障类型,0））

这样你就搞定2级菜单里。

第三种做法：

使用函数OFFSET

学这个offset函数半天，才搞定。

现在只是可以实现这个功能。

不过还有需要改进的地方

还是上面的例子，

=OFFSET（A2,,MATCH（L2,故障类型,）-1,6,1）

这样也是可以实现2级的下拉菜单。

以上的方式其实是不太完善的，因为我们的2级菜单刚好都是6项，那么如果是不等的，那你就只能取最多的。

完善的做法，当然是动态的，根据2级菜单的数量来显示。

需要解决的问题，就是如果动态计算出每项2级菜单的数量。

2级菜单数量=COUNTA（OFFSET（A:

A,,MATCH（L2,故障类型,0）-1））-1

那么我们的公式就变成

=OFFSET（A2,,MATCH（L2,故障类型,）-1,COUNTA（OFFSET（A:

A,,MATCH（L2,故障类型,0）-1））-1,1）

这样就更加完美。

1、两列数据查找相同值对应的位置

=MATCH（B1，A：

A，0）

2、已知公式得结果

定义名称=EVALUATE（Sheet1!

C1）

已知结果得公式

定义名称=GET.CELL（6，Sheet1!

C1）

3、强制换行

用Alt+Enter

4、超过15位数字输入

这个问题问的人太多了，也收起来吧。

一、单元格设置为文本;二、在输入数字前先输入'

5、如果隐藏了B列，如果让它显示出来?

选中A到C列，点击右键，取消隐藏

选中A到C列，双击选中任一列宽线或改变任一列宽

将鼠标移到到AC列之间，等鼠标变为双竖线时拖动之。

6excel中行列互换

复制，选择性粘贴，选中转置，确定即可

7excel是怎么加密的

（1）、保存时可以的另存为>>右上角的"工具">>常规>>设置

（2）、工具>>选项>>安全性

8、关于COUNTIF

COUNTIF函数只能有一个条件，如大于90，为=COUNTIF（A1：

A10，">=90"）

介于80与90之间需用减，为=COUNTIF（A1：

A10，">80"）-COUNTIF（A1：

A10，">90"）

9、根据身份证号提取出生日期

（1）、=IF（LEN（A1）=18，DATE（MID（A1，7，4），MID（A1，11，2），MID（A1，13，2）），IF（LEN（A1）=15，DATE（MID（A1，7，2），MID（A1，9，2），MID（A1，11，2）），"错误身份证号"））

（2）、=TEXT（MID（A2，7，6+（LEN（A2）=18）*2），"#-00-00"）*1

10、想在SHEET2中完全引用SHEET1输入的数据

工作组，按住Shift或Ctrl键，同时选定Sheet1、Sheet2。

11、一列中不输入重复数字

[数据]--[有效性]--[自定义]--[公式]

输入=COUNTIF（A：

A，A1）=1

如果要查找重复输入的数字

条件格式》公式》=COUNTIF（A：

A，A5）>1》格式选红色

12、直接打开一个电子表格文件的时候打不开

“文件夹选项”-“文件类型”中找到.XLS文件，并在“高级”中确认是否有参数1%，如果没有，请手工加上

13、excel下拉菜单的实现

[数据]-[有效性]-[序列]

14、10列数据合计成一列

=SUM（OFFSET（$A，（ROW（）-2）*10+1，，10，1））

15、查找数据公式两个（基本查找函数为VLOOKUP，MATCH）

（1）、根据符合行列两个条件查找对应结果

=VLOOKUP（H1，A1：

E7，MATCH（I1，A1：

E1，0），FALSE）

（2）、根据符合两列数据查找对应结果（为数组公式）

=INDEX（C1：

C7，MATCH（H1&I1，A1：

A7&B1：

B7，0））

16、如何隐藏单元格中的0

单元格格式自定义0;-0;;@或选项》视图》零值去勾。

呵呵，如果用公式就要看情况了。

17、多个工作表的单元格合并计算

=Sheet1!

D4+Sheet2!

D4+Sheet3!

D4，更好的=SUM（Sheet1：

Sheet3!

D4）

18、获得工作表名称

（1）、定义名称：

Name

=GET.DOCUMENT（88）

（2）、定义名称：

Path

=GET.DOCUMENT

（2）

（3）、在A1中输入=CELL（"filename"）得到路径级文件名

在需要得到文件名的单元格输入

=MID（A1，FIND（"*"，SUBSTITUTE（A1，""，"*"，LEN（A1）-LEN（SUBSTITUTE（A1，""，""））））+1，LEN（A1））

（4）、自定义函数

PublicFunctionname（）

DimfilenameAsString

filename=ActiveWorkbook.name

name=filename

EndFunction

19、如何获取一个月的最大天数

：

"=DAY（DATE（2002，3，1）-1）"或"=DAY（B1-1）"，B1为"2001-03-01

数据区包含某一字符的项的总和，该用什么公式

=sumif（a：

a，"*"&"某一字符"&"*"，数据区）

最后一行为文本：

=offset（$b，MATCH（CHAR（65535），b：

b）-1，）

最后一行为数字：

=offset（$b，MATCH（9.9999E+307，b：

b）-1，）

或者：

=lookup（2，1/（b1：

b1000<>""），b1：

b1000）

评委打分中，如何去掉两个以上最高分，两个以上最底分，求剩余人员的平均分?

同时显示出被去掉的分数。

看看trimmean（）函数帮助。

被去掉的分数：

最大两个：

=large（data，）

最小两个：

=small（data，）

怎样很简单的判断最后一位是字母

right（a1）*1

出错的字母

=IF（ISNUMBER（--RIGHT（A1，1）），"数字"，"字母"）

=IF（ISERR（RIGHT（A1）*1），"字母"，"数字"）

如何设置单元格，令其不接受包含空格的字符

选定A列

数据——有效性——自定义——公式

=iserror（find（""，a1））

数据--有效性--自定义--公式

=len（a1）=len（trim（a1））

原来的函数是=a1+a4+a7+a10+a13+a16+a19+a22……

现在是=sum（n（offset（a1，（row（1：

10）-1）*3，）））

在一个工作表中引用其他工作表中的数据，但是被引用的工作表不是固定的，根据我输入的工作表名自动选择相应的工作表中的数据，请问在公式里怎样引用?

=INDIRECT（"A1"&"!

"&"E1"）A1为工作表名

奇数行求和=SUMPRODUCT（（A1：

A1000）*MOD（ROW（A1：

A1000），2））

偶数行求和=SUMPRODUCT（（A1：

A1000）*NOT（MOD（ROW（A1：

A1000），2）））

查看字符串字数

=LEN（A1）

求非空单元格数量

公式计算出来的数据，COUNTA不能用的（否则空字符也计算进去了）

=COUNTIF（$E：

$E536，"?

*"）

动态求和公式，自A列A1单元格到当前行前面一行的单元格求和.

=SUM（INDIRECT（"A1：

A"&ROW（）-1））

20、比较好用的excel文档修复工具

excelRecovery

21、excel开方运算

将8开3次方，可以用这个公式，在单元格中输入=8^（1/3）

22、单元格中的数据分散对齐

文本格式》全角输入

23、查找工作表中的链接

Ctrl+~或编辑》链接

24、如何让空单元格自动填为0

选中需更改的区域》查找》空》替换》0

25、把word里的数字转换到excel

方法有多种，选中》复制》设置输入单元格为文本》选择性粘贴》值

选中》表格转换为文本》粘贴》分列》对分列选项设置为文本

另存为文本文件》excel中打开文本文件》对导入文本对话框进行对应设置