一、如何快速选定不相邻的多个单元格区域?
比如:要同时选定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*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精英俱乐部)
比如当前工作薄中有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)),"")
七、利用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+回车结束
(二)、用公式排序(中国式排名):
假定成绩在E列,请在F2输入公式:
=SUM(IF(E$2:E$1000>E2,1/COUNTIF(E$2:E$1000,E$2:E$1000)))+1
公式以Ctrl+Shift+Enter三键结束。
联系客服