excel疑难问题.docx
《excel疑难问题.docx》由会员分享，可在线阅读，更多相关《excel疑难问题.docx（23页珍藏版）》请在冰点文库上搜索。
excel疑难问题
巧妙将数字转换成为货币大写luFDEMSoTE
假设F5单元格数值为18.21Y__'_VUGM
按照以下步骤你可以成功将它转化为货币金额大写;sI"_@（L]_
在单元格E6输入公式"=int（F5）"p9_^V_^LZ
设置单元格E6,在“数字”下选择“特殊”－“中文大写数字”g<4_,/6Q$t
设置单元格E6,在“数字”下选择“自定义”－[DBNum2][$-804]G/通用格式，在后加“圆”，即：
[DBNum2][$-804]G/通用格式圆，上方显示“壹拾捌圆”_KE,K1P1X_
在F6单元格录入“＝（F5-E6）*100”,得到值21，gw@hos_@v_
设置单元格F6,第一步设置同E6,hMf_*a_O?
设置单元格F6,在“数字”下选择“自定义”－修改为：
[DBNum2][$-804]0角0分，上方显示贰角壹分。
VLOOKUP函数（财务人员非学不可的函数） XV]_N_T_Qn
在表格数组的首列查找指定列的值，并由此返回表格数组满足行条件的指定列的值。
（非常不好描述，即使看不懂也没关系）FE[?
=N2_*
P_cea___N
假设有一张成绩表，第一列为学号，第二列为姓名，第三列之后为各科成绩，第二行开始有学生的成绩信息，信息区域为A2:
A41 我需要找出指定学号（放置在一个新工作表的A2:
A10）的几个人的数学成绩（假设在第5列）h~ce{2xHp
3_�^_.rJ_z
语法__,_Sh'_
VLOOKUP（A2,成绩表的数据区域,5,0）_kd,__&23q
sIj_fittvr
在新工作薄b2录入“＝vlookup（A2,成绩表$A$2:
$A$41,5,0）”,假设A2学号为S008,函数会将成绩表里的S008找到并将第5列的数学成绩返回
每年离高考还有不足二百天的时候，为了烘托气氛，学校都会要求各班制作倒计时日历，如图1所示，用以提醒学生们当天的日期，同时提醒距离高考的时间。
如果每天手工书写一份这样的纸的话，不仅有些费事儿，更关键的是，就咱写的那字，呵呵，还真有点拿不出手。
想来想去，咱还是发挥咱的强项，借助Word2007的邮件合并功能来完成吧。
经过试验，呵呵，效果良好。
图1倒计时日历
一、Excel相关表格的建立
首先启动Excel2007，在A1单元格输入“日期”，然后在A2单元格输入“2008-1-17”。
选中此单元格，按下右键，向下拖动该单元格的填充句柄至A144单元格，松开右键，在弹出的快捷菜单中点击“以天数填充”命令，如图2所示，则可以得到自1月17日至6月7日的所有日期。
图2Excel2007执行以天数填充
在B1单元格输入“日”，在B2单元格输入公式“=TEXT（A2,"dd日"）”，17回车后就可以得到相应的日期（17日）。
在C1单元格输入“月”，并在C2单元格输入公式“=TEXT（A2,"mm月"）”，回车后即可得到用两位数表示的相应的月份（01月）。
那么怎样得到这天是星期几呢？
在D1单元格输入“周”，然后在D2单元格输入公式“=TEXT（A2,"[DBNum1]aaaa"）”，回车后就可以得到用中文表示的星期几了。
最后，在E1单元格输入“倒计时”，然后在E2单元格输入公式“="2008-6-7"-A2”，回车后就可以得到今天距高考的天数了。
所得的结果如图3所示。
图3Excel2007键入倒计时公式
至此，在Excel中的工作就可以宣告结束了，将其保存备用。
需要说明的是上述公式中的TEXT函数是为了指定相应单元格的格式，并将数字转化成文本。
二、Word中的邮件合并
启动Word2007，新建一个文件。
首先应点击功能区“页面布局”选项卡中的相应按钮，设置页面及页边距等。
我们可以根据需要进行相关设置，这里就不罗嗦了。
然后点击功能区“插入”选项卡“表格”功能组中“表格”按钮，插入一个三行一列的表格。
在第一行中输入文字“今天是”，在第三行中输入文字“距离高考还有天”。
分别设置好字体、字号等。
将鼠标定位于第二行，点击功能区“邮件”选项卡“开始邮件合并”功能组中的“选择收件人”按钮，在弹出的菜单中点击“使用现有列表”命令，然后在弹出的对话框中找到我们准备好的Excel文件，双击。
在打开的“选择表格”对话框中选择数据所在的工作表，并确认下方的“数据首行包含列标题”复选项处于选中状态，如图4所示。
点击“确定”按钮关闭对话框。
图4Word2007选择表格
再点击功能区“邮件”选项卡“编写和插入域”功能组中“插入合并域”按钮右下角的小三角形，在弹出的列表中依次点击“月”、“日”、“周”，插入相应的域。
再将鼠标定位于第三行中“距离高考还有”和“天”之间，再次点击“邮件”选项卡“编写和插入域”功能组中“插入合并域”按钮右下角的小三角形，在弹出菜单中点击“倒计时”，插入相应的域。
选中相应的域符号，并进行相应的格式设置，即可得到如图5所示的结果。
图5Word2007编写和插入域
点击“邮件”选项卡“预览结果”功能组中“预览结果”按钮，可以看到制作的效果。
感觉满意的话，那么点击“邮件”选项卡“完成”功能组的“完成并合并”按钮右下角小三角形，在弹出菜单中点击“编辑单个文档”命令，在打开的“合并到新文档”对话框中选择“全部”单选项，如图6所示。
图6Word2007邮件合并
当然，我们也可以在对话框的输入框中指定合并的记录起止数。
点击“确定”按钮后就可以了，图7所示为所得到的前六页。
如何显示多行文本
一般情况下，在单元格中输入的文本是不换行的，这样看起来会很别扭。
如果要在同一单元格中显示多行文本，可以这样做：
选中要换行的单元格，单击“格式”菜单，选择“单元格”命令，然后选择“对齐”选项卡，选中“文本控制”标题下的“自动换行”复选框。
这时界面就变成了下面的样子。
如果单元格中还有段落，就要在里面输入硬回车，这时Enter键的功能不是在单元格内换行，而是在单元格间移动。
这时我们可以在编辑栏（或正编辑的单元格）中要换行的地方单击鼠标，按ALT+ENTER键来换行。
换行后的效果如下图，在编辑区文字是换行的。
分列功能的妙用
上一个例子中，我们用left函数将姓名中的姓和名分开了，说实话，这个方法比较笨，因为EXCEL中提供了分列功能。
我们可以看下面这个例子，原始数据中含有编号，而且随着行数的增加，编号位数也在增加，这时就不能用Left函数来解决问题了。
我们可以分列功能将编号和文本分开。
选中此列，单击“数据”菜单中的“分列”命令，弹出“文本分列向导”对话框。
在对话框中提供了两类数据分割方法，一是按固定宽度，一是按分隔符号。
将姓与名分开的问题就可以通过选择“固定宽度”项来解决，当前这个例子可以选择“分隔符号”项，单击“下一步”。
这里要选择分隔符号，因为例子中的编号和文本中间都有“.”，所以我们可以选择“其他”复选框，并在后面的文本框中输入“.”，再单击“下一步”。
单击“完成”按钮，编号和文本就被分开了。
如何将姓与名分开？
有这样一个问题，一个数据表中，A列是由姓和名组成了，如何将姓和名分开呢？
一个简单的方法是应用Left函数，此函数可以返回文本串中的第一个或前几个字符。
Left函数的语法形式为：
LEFT（text,num_chars）
其中：
Text：
是包含要提取字符的文本串。
Num_chars：
要提取的字符数量，这个值必须大于或等于0；
如果num_chars大于文本长度，则返回所有文本；
如果忽略num_chars，则假定其值为1。
下面我们看一下这个例子，要将姓分开，且例子中都是单姓的，所以我们可以在B1中添入“=left（A1）”，然后利用自动填充功能，完成下面单元格公式的复制。
我们再看一个例子，某些会计程序在显示负数时会在数值的右边添加负号（–）。
如果导入的文件是在按此方法保存负数的程序中创建的，则MicrosoftExcel会将这些数值作为文本导入。
要将此文本串转换为数值，必须返回除最右边字符（即负号）以外的所有文本串字符，然后乘以–1。
对于num_chars参数，可使用LEN函数计算文本串中字符的个数，然后减去1。
例如，如果单元格A1中的值为“234–”，我们可以在B1中输入“LEFT（A1,LEN（A1）–1）*–1”。
按回车后B1中就显示为“–234”。
同样，我们可以利用自动填充的功能完成其余数据的转换。
好，Left函数的功能就讲这些，与之相对的还有个“right”函数，可以返回文本串中后一个或几个字符，你可以放手一用了！
Excel技巧：
快速录数据让单位自动补全
用Excel做表格，经常遇到需要在某一列使用同一单位的情况。
如果先在第一个单元格输入对应单位的名称，然后使用“拖拽”的方法使其它行复制这一单位，达到的效果不尽人意，要么需要再改单位前的数值，要么单位与数值分在两列。
我们通过预先设置某一列的格式可以让这些单位“自动补全”。
这里以将某一列的单位设置为厘米（CM），最终的效果是在该列任一单元格中输入数字后，按下回车键或将光标移至其它单元格，在数字后会自动补齐“CM”。
第一步：
运行Excel，单击选中需要设置格式的列。
右键单击选中的列，在弹出的快捷菜单中选择“设置单元格格式”（如图1）。
第二步：
在打开的“单元格格式”对话框中，在“数字”标签中，选中“自定义”项，在右侧“类型（T）:
”下方的文本框中输入“0.00"CM"”（如图2,不含外侧引号），最后单击“确定”按钮。
经过这样设置之后，在该列单元格中输入数据时，不用再考虑单位问题了，直接输入数字即可。
小提示：
其中“0.00”表示该列单元格保留两位小数，小数点后面几个“0”就代表几位小数，具体多少可根据需要自行设置。
如果是其它单位符号，只要更改双引号中的CM为相应的字符即可。
利用函数实现Excel表格自动隔行着色
我们在Excel中浏览一个非常大的工作簿中的数据时，有时会出现看错行的现象发生。
如果能隔行填充上不同的颜色，这种现象就应该不会发生了。
我们利用条件格式和函数来实现这一需求。
1、启动Excel，打开相应的工作簿文档。
2、选中数据区域（或用Ctrl+A组合键选中整个工作表）。
3、执行“格式→条件格式”命令，打开“条件格式”对话框（如图1）。
4、单击“条件1”下面左侧方框右边的下拉按钮，在随后出现的下拉列表中，选择“公式”选项，并在右侧的方框中输入公式：
=MOD（ROW（）,2）=0。
5、单击其中的“格式”按钮，打开“单元格格式”对话框（如图2）。
6、切换到“图案”标签下，选中一种颜色，确定返回到“条件格式”对话框，按下“确定”按钮退出。
7、隔行填充不同颜色的效果得以实现（如图3）。
Excel单元格中多行与一行并存的处理办法
在EXCEL中，经常会碰到在一个单元格中多行与一行同时并存的情况（见图1），该如何处理呢？
在图1的红圈处，左侧的“合计人民币（大写）”分成两行，而与之在同一格的“仟佰拾元角分”却是一行而已。
我们可以利用文本框来处理。
首先，单击绘图工具栏的文本框按钮，见图2红圈处:
然后在单元格的左上角单击一下，输入“合计人民”。
注意是单击一下，而不是拖动鼠标，如果拖动鼠标，就会出现边框。
效果见下图:
采取同样的办法做出“币（大写）”文本框，将鼠标移到文本框的边缘，按住鼠标即可拖动文本框到合适的位置。
效果见下图:
采取同样的办法做出“仟佰拾元角分”，将鼠标移到文本框的边缘，按住鼠标左键拖动该文本框到单元格右边合适的位置。
效果见下图:
利用高级筛选功能巧妙删除Excel的重复记录
Excel有一个小小的缺陷，那就是无法自动识别重复的记录。
为了清除这些重复记录，有的朋友是一个一个手工删，既费时又费力。
虽说Excel中并没有提供给我们清除重复记录这样的功能，但我们还可以利用它的高级筛选功能来达到相同的目的。
今天，笔者就来向大家介绍一个如何利用Excel的“高级筛选”巧妙删除重复记录的小技巧。
（注:
本文所述技巧已于微软Excel2003环境下测试通过）
具体操作步骤如下:
1.打开一份带有重复记录的Excel文档。
如图1所示（注:
本图已用Photoshop处理，其中彩色部分为重复的记录）
图1
2.选中图表中的所有记录（注意，此时应将每列的标题行也选择上，否则筛选完的数据表中将不再包含有该标题行），执行“数据”菜单→“筛选→高级筛选”命令
3.在弹出的“高级筛选”对话框中选择“将筛选结果复制到其他位置”一项，并点击“复制到”选择框后面的范围按钮来选择一块区域以存放筛选后的数据（注意:
此处千万不要与原数据所处的单元格相重合，否则数据表将会混乱），最后，勾选“选择不重复的记录”复选框后，点击“确定”按钮即可。
如图2所示
图2
4.此时，Excel便会将所有的重复记录自动删除掉，确认无误后，您就可以把新生成的数据清单拷贝到新的工作表中继续使用了。
如图3所示
图3
怎么样，是不是很方便，那就赶快动起您的鼠标试试吧!
巧用Excel计算分期付款
你买房子了吗？
你是贷款买房吗？
你是不是被银行贷款搞糊涂了，算不清每月应该付银行多少银子？
那么下面这个Excel例子正可以帮你的忙！
设计思路
本文以房地产公司为前来咨询的顾客设计一个“分期付款购买房屋自动查询系统”为例，用Excel完成。
如图1所示，房屋总售价（百元）=每平方米的房屋价款（百元）×房屋面积（m2），待偿还金额（百元）=房屋总售价（百元）-首付金额（百元），每月需要支付金额（百元）则是根据待偿还金额（百元）和偿还月份数通过Excel中的一个函数PMT计算出来的。
顾客通过输入所要购买的不同标准的房屋（每平方米的房屋价款和房屋面积的组合）以及首付金额和偿还月份数后就可立即知晓每月需要支付的金额数。
读者朋友可照此思路设计出解决类似实际问题的方案，比如汽车、设备以及其他商品。
图1
设计步骤
设置单元格式
1、新建“分期付款购买房屋自动查询系统”工作簿。
2、在B2、B3、B4、B5、B6、B7、B8单元格中分别输入：
每平方米的房屋价款（百元）、房屋面积（m2）、房屋总售价（百元）、首付金额（百元）、待偿还金额（百元）、偿还月份数、每月需要支付金额（百元）。
3、设置D8单元格的“货币”格式的小数位为“2”，“货币符号”为“￥”，“负数”为“￥-1234.10”（如图2）。
图2
设计微调控制按钮
1、点击［视图］→［工具栏］→［窗体］，打开“窗体”工具栏。
2、单击“窗体”工具栏中的“微调项”按钮，光标变成十字，画一个矩形框（如图3）。
图3
3、用鼠标右击微调项按钮，选择［设置控件格式］→［控制］标签，将最小值设为“8”（计量单位为百元，这里假设房屋的最低售价为每平方米800元），最大值定义为“30000”，步长为“1”，单元格链接栏中录入“$D$2”，启用“三维阴影”（如图4）。
图4
4、用同样的方法在C3、C5、C7单元格中分别设计一个“微调项”按钮，C3的控件格式：
最小值定义为“46”（计量单位是平方米，这里假设最小房屋的面积是46），最大值定义为“30000”，步长为“1”，单元格链接栏中输入“$D$3”；C5的控件格式：
最小值定义为“100”（计量单位是百元，这里假设首次付款的最低限额是100百元，即10000元），最大值定义为“30000”，步长为“10”，单元格链接栏中输入“$D$5”；C7的控件格式：
最小值定义为“1”，最大值定义为“180”（这里假设还款的最长期限是180个月），步长为“1”，单元格链接栏中输入“$D$7”。
定义公式
1、房屋总售价（D4）=每平方米的房屋价款（D2）*房屋面积（D3）。
2、待偿还金额（D6）=房屋总售价（D4）-首付金额（D5）。
3、每月需要支付金额（D8）=-PMT（0.4%，D7，D6）。
这里，0.4%表示月利率，D7代表偿还的月份数，D6代表偿还金额的现值。
PMT是Excel中的一个函数，其功能是计算在固定利率下的贷款（或投资或欠款）的等额分期偿还额。
升级会员 