Excel 解析中高级函数3文档格式.docx
《Excel 解析中高级函数3文档格式.docx》由会员分享，可在线阅读，更多相关《Excel 解析中高级函数3文档格式.docx（19页珍藏版）》请在冰点文库上搜索。
K"
IF（A1=12,"
L"
IF（A1=13,"
M"
IF（A1=14,"
N"
IF（A1=15,"
O"
IF（A1=16,"
P"
D1））））））））
D1
IF（A1=17,"
Q"
IF（A1=18,"
R"
IF（A1=19,"
S"
IF（A1=20,"
T"
IF（A1=21,"
U"
IF（A1=22,"
V"
IF（A1=23,"
W"
IF（A1=24,"
X"
E1））））））））
E1
IF（A1=25,"
Y"
IF（A1=26,"
Z"
"
超出范围"
））
根据情况，可以将
C、D、E
这些从事辅助运算的单元格放在其它任何地方
3.
一个单元格也可以实现
=IF（A1=1,"
"
））））））））&
））（数组形式输入）。
4.
以一例：
a1=1,2,3,4,5,6,7,8,9,10
b=if（a1=1,"
一"
if（a1=2,"
二"
if（a3=3，"
三"
.......if（a1=9,"
九"
if（a1=10,"
十"
））））））,if超过7层不起作用，我该如何办
解答：
1、b=if（a1>
5,if（a1=6,"
六"
。
）），明白意思？
就是截为两段再做判断，这样可以不超过7重。
2、可以用自定义数字格式。
也可以用=CHOOSE（A1+1,"
一二三四五六七八九十"
）
问：
实际上我的要求是现行高一成绩统计中:
b=if（a1="
语文"
if（a1="
数学"
英语"
物理"
化学"
历史"
政治"
政治"
生物"
生物“，if（a1="
地理"
））））））））,这样超过了7层。
我不知如何处理。
因为下面的公式要引用语、数、英、等。
答：
新建一表，取名Data，找一区域设置名称为SubjectTable：
语文Chinese
英语English
..
=vlookup（SubjectTable,a1,2,false）可以有65536个，够了吧。
其实，稍加改进，理论上，可以有达到你硬盘空间的个数。
或用if和or的组合可以解决15个。
再举个例子：
=IF（A16="
IF（B16="
样办尚未交"
IF（OR（B16="
内部检查中"
B16="
数据查询中"
）,CONCATENATE（IF（B16="
品质检测中"
）,IF（B16="
图纸未确认"
为不合格"
需要修正"
））,CONCATENATE（IF（C16="
客户检查中"
待客回复"
）,IF（C16="
合格"
待P/O生产"
取消"
客户取消"
为客户设变中"
客户设变中"
不合格"
）））））
一个单元格内格式问题
如果我做了一个表某一列是表示重量的，数值很多在1--------------152********44之间的数不等。
这些表示重量的数。
如果我想次给他们加上单位，但要求是单位是>
999999吨，之下>
999是千克，其余的是克。
如何办
[>
9999]###.00,"
吨"
;
*,*.00"
千克"
怎样用函数求出一定范围内的数值的偶数的个数
1设你的数据区域为A1：
A30
{=COUNT（IF（A1:
A30/2/2=INT（A1:
A30/2）,A1:
A30））}
如果你的数据区域内还包括空白格你不想计算在内的话，{=COUNT（IF（A1:
A30））-COUNTBLANK（A1:
A30）}
或：
{=sum（（even（a1:
a30）=a1:
a30）*1）}
如何使某列的数据不重复
1、
我做了个宏，可惜在数据量大时（超过1000时）速度狂忙，各位帮我修改修改
Sub检查重复项（）
DimiAsInteger
DimjAsInteger
num=Selection.Cells.Count
Fori=1Tonum
Forj=i+1Tonum
If（Selection.Cells（i）=Selection.Cells（j））Then
MsgBoxSelection.Cells（i）.Value
EndIf
Nextj
Nexti
MsgBox"
检索完毕"
EndSub
2、
假设数据在B列
选中B列，[数据]-[数据有效性]-[自定义]
在“公式”输入框中键入：
=COUNTIF（B:
B,B1）=1。
请问如何能使随机数不重复
我在30个单元格里使用了randbetween（1,500）,随机抽取了1-500之间的任意数,但问题是这30个数中仍有机会出现重复请问如何才能使这些随机数不重复呢?
1、勾选迭代计算，A1输入如下公式：
=IF（COUNTIF（$A$1:
$E$6,A1）=1,A1,RANDBETWEEN（1,500））
如果某格出现0值，点选旁边单元格拖一下即可。
2、用上面的方法得到的随机数不会变化，如何做到想变就变？
增加一个条件语句，即可达到此效果，公式如下：
$E$6,A1）=1,IF（$G$1=1,RANDBETWEEN（1,500）,A1）,RANDBETWEEN（1,500））G1为控制格，当在G1输入数字1，就开始变。
变化后的数据有可能不惟一了，怎么办？
把1清除即可！
日期型编号
想在单元格里输入1，产生0207121这样一个数字，02是2002年，07是7月份，12是12日，1是输入的数值
如a1输入1，要在b1生成你要的数字，你可以在b1设公式=text（today（）,"
yymmdd"
）&
a1
如何将数字改变为字符串
=TEXT（A1,"
@"
在Excel中如何自定义数字格式
虽然Excel为用户提供了大量的数字格式，但还是有许多用户因为工作、学习方面的特殊要求，需要使用一些Excel未提供的数字格式，这时我们就需要利用Excel的自定义数字格式功能来帮助实现用户的这些特殊要求。
一、在Excel中创建自定义数字格式的方法
1、选择要设置格式的单元格或单元格区域。
2、单击“格式”菜单中的“单元格”命令，然后单击“数字”选项卡。
3、在“分类”列表中，单击“自定义”选项。
4、在“类型”框中，编辑数字格式代码以创建所需的格式。
在Excel自定义数字格式使用如下的表达方式：
正数的格式 负数的格式 零的格式 文本的格式
#,##0.00 [Red]-#,##0.00 0.00 "
TEXT"
@
在Excel自定义数字格式的格式代码中，用户最多可以指定四个节；
每个节之间用分号进行分隔，这四个节顺序定义了格式中的正数、负数、零和文本。
如果用户在表达方式中只指定两个节，则第一部分用于表示正数和零，第二部分用于表示负数。
如果用户在表达方式中只指定了一个节，那么所有数字都会使用该格式。
如果在表达方式中要跳过某一节，则对该节仅使用分号即可。
二、自定义数字格式实例
（一）自动添加文本
在日常财务工作中，常常需要在金额数字后加单位“元”，这时就可以使用“0.00元”的自定义数字格式，当用户在单元格中录入数字后，Excel就会自动在数字后加上单位“元”。
如果需要将单元格中录入的数字“32”自动转换成“高三十二班”格式，只要将单元格的数字格式定义为“高[DBNum1]G/通用格式班”即可。
（二）在自定义数字格式中使用颜色
要设置格式中某一部分的颜色，只要在该部分对应位置用方括号键入颜色名称或颜色编号即可。
Excel中可以使用的颜色名称有[黑色]、[蓝色]、[青色]、[绿色]、[洋红]、[红色]、[白色]、[黄色]八种不同的颜色，此外Excel还可以使用[颜色X]的方式来设置颜色，其中X为1-56之间的数字，代表了56种不同的颜色。
例如：
当用户需要将单元格中的负数数字用蓝色来表示，只要使用“#,##0.00;
[蓝色]-#,##0.00”自定义数字格式，用户在单元格中录入负数时，Excel就会将数字以蓝色显示。
（三）在自定义数字格式中使用条件格式
在Excel自定义数字格式中用户可以进行条件格式的设置。
当单元格中数字满足指定的条件时，Excel可以自动将条件格式应用于单元格。
Excel自定义数字格式中可以使用如下六种标准的比较运算符：
运算符 含义
= 等于
>
大于
<
小于
= 大于等于
= 小于等于
>
不等于
在Excel中要想设置满足指定条件数字的格式，在自定义数字格式代码中必须加入带中括号的条件，条件由比较运算符和数值两部分组成。
在学生成绩工作表中，当我们想以红色字体显示大于等于90分的成绩，以蓝色字体显示小于60分的成绩时，其余的成绩则以黑色字体显示，这时只需将自定义数字格式设置为“[红色][>
=90];
[蓝色][<
60];
[黑色]”即可。
值得注意的是，当你在以后需要继续使用刚才所创建的成绩条件自定义数字格式时，你会发现在“单元格格式”的“自定义”分类类型中找不到“[红色][>
[黑色]”格式，这是因为Excel自动将你所创建的“[红色][>
60]”格式修改成“[[红色][>
=90]G/通用格式;
60]G/通用格式;
[黑色]G/通用格式”，你只需选择此格式即可达到同样的使用效果。
（四）隐藏单元格中的数值
在Excel工作表中，有时为了表格的美观或者别的因素，我们希望将单元格中的数值隐藏起来，这时我们使用“;
”（三个分号）的自定义数字格式就可达到此目的。
这样单元格中的值只会在编辑栏出现，并且被隐藏单元格中的数值还不会被打印出来,但是该单元格中的数值可以被其它单元格正常引用。
在单元格显示负数为红字后又紧跟汉字
1、这个使用宏可以解决，但单元格开始请不要带单位。
单个单元格宏代码如下：
Sub宏1（）
'
宏1Macro
Dent记录的宏2002-2-16
Dimstr1AsString
str1=Str（ActiveCell.Value）
IfVal（str1）<
0Then
str1=Abs（Val（str1））&
"
文字"
ActiveCell.Value=str1
ActiveCell.Font.Color=vbRed
EndIf
2、用单元格格式解决：
#,##0.00;
[红色]-#,##0.00"
文字"
检索问题
在一个工作表中，有客户名称、收款日期等字段名及相应数据。
现在想为了检验收款日期是否到期，要求在工作表的某一空白单元格中键入某一日期，则此单元格以下的单元格可以显示收款日期字段中所有在此日期前的日期清单，请问如何实现？
并要求不使用筛选功能。
Sheet2!
A1命名为X,键入日期
Sheet1:
客户名称、收款日期等字段名及相应数据
插入名称定义：
XX=OFFSET（Sheet1!
$A$2,,,MATCH（X,Sheet1!
$B$2:
$B$65536）-1,2）
XX即是您所需要之范围
vba写在ThisWorkbook
PrivateSubWorkbook_SheetChange（ByValShAsObject,ByValTargetAsExcel.Range）
OnErrorGoTo1:
Target.Select
Range（Target.Value）.Copy
ActiveSheet.PasteLink:
=False
Application.CutCopyMode=False
1
**********************************************************************************
Sheet2任意储存格键入xx按ENTER
[比较一下并没有比筛选功能快],反而复杂
SHEET2随便贴张图
键入公式=XX
微软到底对我们隐藏了多少
NumberString
这个函数是否一直存在但没有解释？
NumberString（1234567890,1）就像自定格式[DBNum1]一样，它还有2的选择。
其实这些特别用途的函数是否中文板设计时特别隐藏。
测试结果：
NumberString（1234567890,1）＝一十二亿三千四百五十六万七千八百九十
NumberString（1234567890,2）＝壹拾贰亿叁仟肆佰伍拾陆万柒仟捌佰玖拾
NumberString（1234567890,3）＝一二三四五六七八九○
DateString（）相信亦是一特别Lotus函数。
DATESTRING（"
23-Sep-2002"
）=2002年09月23日
23Sep2002"
9/23/2002"
9-23-2002"
求一组数中第一个出现的正数
单元格A1—G1中存放一组数，如何使A2的值等于这组数中第一个出现的正数（按A1—G1的顺序），若无正数则A2＝0.
=IF（A1<
=0,IF（B1<
=0,IF（C1<
=0,IF（D1<
=0,IF（E1<
=0,IF（F1<
=0,IF（G1<
=0,,G1）,F1）,E1）,D1）,C1）,B1）,A1）
{=IF（COUNTIF（A1:
G1,"
0"
）=0,0,INDIRECT（ADDRESS（1,SMALL（IF（A1:
G1>
0,COLUMN（A1:
G1））,1））））}
能否使表格不能打印
PrivateSubWorkbook_BeforePrint（CancelAsBoolean）
Cancel=True
（用法：
打开Excel,按Alt+F11,调出VBE后,按Ctrl+r显示出工程资源管理器（默认此窗口应已显示）,双击ThisWorkbook,在模块中粘入代码即可!
要想打印你表,用截图软件即可.无最保险办法）。
有无这样的函数
A1为一定范围内的随机值，B1—B20为一组数，有无这样的函数来确定A2的值？
当A1=1时，A2=B1
当A1=2时，A2=B2
当A1=3时，A2=B3
…………
=INDIRECT（ADDRESS（A1,2））（绝）
能实现两栏数据中按特定条件进行比较并做出相应记号吗
具体意思如下：
有四栏数据。
A栏B栏C栏D栏F栏
对A栏及C栏数据进行查找对比，如果发现存在A=C，且B=D，则在符合条件的F栏显示已找到，对于没有查到的的显示未找到。
如果是一一对应查找则f2=if（and（A2=C2,B2=D2）,"
OK"
No"
）,复制.
如果整体查找：
=IF（AND（COUNTIF（$C$2:
$C$5,A2）>
0,COUNTIF（$D$2:
$D$5,B2）>
0）,"
找到"
没有"
如何对文本格式的数字进行筛选
如80/24；
73/56……
要对A列进行筛选，条件1:
符号"
/"
左边的数字>
=50;
条件2:
右边的数字>
=80,达到任一条件的即被选中。
试试这个公式:
B1=IF（OR（LEFT（A1,FIND（"
A1）-1）*1>
=50,MID（A1,FIND（"
A1）+1,LEN（A1）-FIND（"
A1））*1>
=80）,A1,"
我想再请教一个问题:
在自动筛选中有"
包含、不包含"
之类的选项,这类选项在高级筛选中能否实现?
提供以往一个函数公式,请你研究看是否有参考:
={SUM（（ISNUMBER（SEARCH（"
广东"
$B$3:
$B$13）））*（$A$3:
$A$13="
股份制"
）*（$C$3:
$C$13））}
这是对B列单位名称中包含"
并且A列企业类型为"
的,按C列汇总.
如何筛选出特定行
一份工作表，有几万行数据，现在想每隔50行打印一行，如何进行筛选
插入一空列,输入=MOD（ROW（）,50）并复制到全数据列,再用筛选法把有“0”的单元格选出.
Word加启动密码
在Word模板中录制如下宏文件保存即可:
Subautoexec（）
Dimuser
user=InputBox（"
请输入软件启动密码"
）
Ifuser="
password"
Then
Else
MsgBox（"
非法用户,将退出本程序.Wu"
Application.Quit
有关大小写的转换
在EXCEL单元格中有的单元格是小写字母，有的是大小写混在一起现在我想全部转为大写、或全部转为小写，有哪些方法呢？
利用upper（）函数或还有LOWER。
如：
a1=aBcD，upper（a1）=ABCD
在Excel中打造自己的“公式保护”菜单
工作中经常用到Excel的计算功能，有时一张工作表中需要设置很多公式，为了防止误操作就将工作表保护起来，但是如果将含有公式的单元格保护，而其它单元格不锁定，设置时需要一个个单元格选定，很麻烦而且容易出错，就想到建立一个菜单项，将这项工作变成菜单。
有幸的是这一切Excel都能完成，具体操作如下：
先将工作表保护，不必输入密码。
再录制一个宏。
方法是：
打开“工具”栏，单击“宏”-“录制新宏”；
将其命名为“公式保护”，单击“保存在”的下拉箭头，将新宏保存到“个人宏工作簿”。
单击“确定”录制开始。
（注意此后的每一动作都将被录制，直至“停止录制”。
将工作表取消保护；
选定a1单元格，输入="
测试"
（注：
黑体为实际输入内容，标点符号均为英文输入法状态,下同）；
单击工作表左上角的方框选定整个工作表；
单击“格式”-“单元格”-“保护”项，将“锁定”和“隐藏”前的方框里的对号去掉；
5.
单击“插入”-“名称”-“定义”，当前工作表名称输入：
公式保护，引用位置输入：
=get.cell（4,indirect（"
rc"
false））,单击“添加”后“确定”；
6.
单击“格式”-“条件格式”，在“单元格数值”下拉框选定“公式”，后面的框内输入=公式保护，单击“格式…”，在图案标签内选浅绿色或者其它颜色“确定”，再“确定”；
7.
单击“编辑”-“定位”-“定位条件…”，选定“公式”项“确定”再“确定”；
8.
重复步骤4，将“锁定”和“隐藏”前的方框里的对号选定；
9.
选定a1单元格，取消其内容，并重复步骤4，取消a1的锁定和隐藏属性；
10.
单击工具-保护-保护工作表，将工作表保护（也不必设密码）。
再单击“工具”-“宏”-“停止录制”停止录制。
再打开“工具”-“自定义”项，再“命令”标签栏内“类别”栏中找到“新菜单”并指定，在其右侧的命令框中的新菜单用鼠标按住拖到“工具栏”的“保护”项中的“保护工作表”下面放开，并单击右键将其命名为“公式保护”，将刚才录制名为“公式保护“的宏指定给它。
至此，一个很有用的菜单项就作成了。
此后，只要你将鼠标移动到“工具”-“保护”-“公式保护”的位置，工作表将执行其所指定的宏，只要你工作表中输入了公式（以=开头），含有公式的单元格将自动变为浅绿色，提醒你和别人此处有公式，小心编辑，十分醒目。
编辑完公式后再次运行该命令就可以此保护工作表并锁定公式，禁止改动。
如何让EXCEL自动从头统计到当前单元格
情况如下：
C列要根据A列的内容来统计B列的数据，范围从A1：
An，即当A列中An有数据时，Cn自动根据An的值，统计B1：
Bn的数据。
=SUM（INDIRECT（"
B1:
&
LARGE（（A1:
A65535<
）*（ROW（A1:
A65535））,1））），按Ctrl+Shift+Enter。
请问想要取当前单元格的列号，用什么函数
1、=CHAR（64+COLUMN（））
2、PrivateSubWorksheet_SelectionChange（ByValTargetAsExcel.Range）
IfSelection.Columns.Column>
26Then
tt=Mid（ActiveCell.Address,2,2）
Else
tt=Mid（ActiveCell.Address,2,1）
MsgBox（tt）
3、1的公式就变化一下：
=IF（COLUMN（）>
26,CHAR（64+INT（COLUMN（）/26））&
CHAR（64+MOD（COLUMN（）,26））,CHAR（64+COLUMN（）））
SUMIF函数
a1至A4是10,24,30,12.B1至B4是8,15,25,35.D1至D4是25,4,3,5.用SUMIF第一参数选取A1:
B4,第二参数是>
20,第三参数选取D1:
D4.它得出是7。
它判
升级会员 