Excel新手必须技巧53个
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">前言</span>：这是很早的技巧教程，适用于2003版，为了让<span style="color: black;">更加多</span>高版本的<span style="color: black;">朋友</span><span style="color: black;">亦</span>能学习，兰色对技巧进行重新整理，<span style="color: black;">期盼</span>能对<span style="color: black;">朋友</span>们有用。</strong></span></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">1</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>在已有的单元格中批量加入一段固定字符？</span></strong><span style="color: black;">例如：在单位的人事资料，在</span>excel<span style="color: black;">中输入后，<span style="color: black;">因为</span>上级<span style="color: black;">需求</span>在原来的职<span style="color: black;">叫作</span>证书的号码<span style="color: black;">所有</span>再加两位，即要在<span style="color: black;">每一个</span>人的证书号码前再添上两位数</span>13<span style="color: black;">，<span style="color: black;">倘若</span>一个一个改的话实在太麻烦了，<span style="color: black;">那样</span><span style="color: black;">咱们</span><span style="color: black;">能够</span>用下面的办法，省时又省力：</span>1<span style="color: black;">）假设证书号在</span>A<span style="color: black;">列，在</span>A<span style="color: black;">列后点击鼠标右键，<span style="color: black;">插进</span>一列，为</span>B<span style="color: black;">列</span> <span style="color: black;">；</span>2<span style="color: black;">）在</span>B2<span style="color: black;">单元格写入：</span> ="13" & A2 <span style="color: black;">后回车；</span>3<span style="color: black;">）看到结果为</span> 13xxxxxxxxxxxxx <span style="color: black;">了吗？鼠标放到</span>B2<span style="color: black;">位置，单元格的下方不是有一个小方点吗，按着鼠标左键往下拖动直到结束。当你放开鼠标左键时就<span style="color: black;">所有</span>都改好了。</span><span style="color: black;">若是在原证书号后面加</span>13 <span style="color: black;">则在</span>B2<span style="color: black;">单元格中写入：</span>=A2 & <span style="color: black;">“</span>13<span style="color: black;">”</span> <span style="color: black;">后回车。</span><strong style="color: blue;"><span style="color: black;">2</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>设置文件下拉窗口的最下面的<span style="color: black;">近期</span>运行的文件名个数？</span></strong><span style="color: black;">打开“工具”，选“选项”，再选“常规”，在“<span style="color: black;">近期</span><span style="color: black;">运用</span>的文件<span style="color: black;">名单</span>”下面的文件个数输入框中改变文件数目<span style="color: black;">就可</span>。若不在菜单中<span style="color: black;">表示</span><span style="color: black;">近期</span><span style="color: black;">运用</span>的文件名，则将“<span style="color: black;">近期</span><span style="color: black;">运用</span>的文件<span style="color: black;">名单</span>”前的复选框去掉<span style="color: black;">就可</span>。</span><strong style="color: blue;"><span style="color: black;">3</span></strong><strong style="color: blue;"><span style="color: black;">、在</span></strong><strong style="color: blue;"><span style="color: black;">EXCEL</span></strong><strong style="color: blue;"><span style="color: black;">中输入如“１</span></strong><strong style="color: blue;"><span style="color: black;">-</span></strong><strong style="color: blue;"><span style="color: black;">１”</span></strong><strong style="color: blue;"><span style="color: black;">、“１</span></strong><strong style="color: blue;"><span style="color: black;">-</span></strong><strong style="color: blue;"><span style="color: black;">２”</span></strong><strong style="color: blue;"><span style="color: black;">之类的格式后它即变成１月１日，１月２日等日期形式，怎么办？</span></strong><span style="color: black;">这是<span style="color: black;">因为</span></span>EXCEL<span style="color: black;">自动识别为日期格式所<span style="color: black;">导致</span>，你只要点击主菜单的“格式”菜单，选“单元格”，再在“数字”菜单标签下把该单元格的格式设成文本格式就行了。</span><strong style="color: blue;"><span style="color: black;">4</span></strong><strong style="color: blue;"><span style="color: black;">、在</span></strong><strong style="color: blue;"><span style="color: black;">EXCEL</span></strong><strong style="color: blue;"><span style="color: black;">中<span style="color: black;">怎样</span>使它象</span></strong><strong style="color: blue;"><span style="color: black;">WORD</span></strong><strong style="color: blue;"><span style="color: black;"><span style="color: black;">同样</span>的自动<span style="color: black;">按时</span><span style="color: black;">保留</span>文件？</span></strong><span style="color: black;">点击“工具”菜单“自动<span style="color: black;">保留</span>”项，设置自动<span style="color: black;">保留</span>文件夹的间隔时间。<span style="color: black;">倘若</span>在“工具”菜单下<span style="color: black;">无</span>“自动<span style="color: black;">保留</span>”菜单项，<span style="color: black;">那样</span>执行“工具”菜单下“加载宏</span>...<span style="color: black;">”选上“自动<span style="color: black;">保留</span>”，“确定”。<span style="color: black;">而后</span>进行设置<span style="color: black;">就可</span>。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">excel2010版：文件选项卡 - excel选项 - <span style="color: black;">保留</span> - ...</span><strong style="color: blue;"><span style="color: black;">5</span></strong><strong style="color: blue;"><span style="color: black;">、用</span></strong><strong style="color: blue;"><span style="color: black;">Excel</span></strong><strong style="color: blue;"><span style="color: black;">做多页的表格时，<span style="color: black;">怎么样</span>像</span></strong><strong style="color: blue;"><span style="color: black;">Word</span></strong><strong style="color: blue;"><span style="color: black;">的表格那样做一个标题，即每页的<span style="color: black;">第1</span>行（或几行）是<span style="color: black;">同样</span>的。<span style="color: black;">然则</span>不是用页眉来完成？</span></strong><span style="color: black;">在</span>EXCEL<span style="color: black;">的文件菜单（<span style="color: black;">excel2010是页面布局 - 打印标题</span>）－页面设置－工作表－打印标题；可进行顶端或左端标题设置，<span style="color: black;">经过</span>按下折叠对话框按钮后，用鼠标划定范围<span style="color: black;">就可</span>。<span style="color: black;">这般</span></span>Excel<span style="color: black;">就会自动在各页上加上你划定的部分<span style="color: black;">做为</span>表头。</span><strong style="color: blue;"><span style="color: black;">6</span></strong><strong style="color: blue;"><span style="color: black;">、在</span></strong><strong style="color: blue;"><span style="color: black;">Excel</span></strong><strong style="color: blue;"><span style="color: black;">中<span style="color: black;">怎样</span>设置加权平均</span></strong><strong style="color: blue;"><span style="color: black;">？</span></strong><span style="color: black;">加权平均在财务核算和统计工作中经常用到，并不是一项很<span style="color: black;">繁杂</span>的计算，关键是要理解加权平均值其实<span style="color: black;">便是</span>总量值（如金额）除以总数量得出的单位平均值，而不是简单的将各个单位值（如单价）平均后得到的那个单位值。在</span>Excel<span style="color: black;">中可设置公式<span style="color: black;">处理</span>（其实<span style="color: black;">便是</span>一个除法算式），分母是各个量值之和，分子是相应的各个数量之和，它的结果<span style="color: black;">便是</span>这些量值的加权平均值。</span><strong style="color: blue;"><span style="color: black;">7</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">倘若</span>在一个</span></strong><strong style="color: blue;"><span style="color: black;">Excel</span></strong><strong style="color: blue;"><span style="color: black;">文件中含有多个工作表，<span style="color: black;">怎样</span>将多个工作表一次设置成<span style="color: black;">一样</span>的页眉和页脚？<span style="color: black;">怎样</span><span style="color: black;">才可</span>一次打印多个工作表？</span></strong><span style="color: black;">把鼠标移到工作表的名<span style="color: black;">叫作</span>处（若你<span style="color: black;">无</span><span style="color: black;">尤其</span>设置的话，</span>Excel<span style="color: black;">自动设置的名<span style="color: black;">叫作</span>是“</span>sheet1<span style="color: black;">、</span>sheet2<span style="color: black;">、</span>sheet3.......<span style="color: black;">”），<span style="color: black;">而后</span>点右键，在弹出的菜单中<span style="color: black;">选取</span>“<span style="color: black;">选取</span><span style="color: black;">所有</span>工作表”的菜单项，<span style="color: black;">此时</span>你的所有操作都是针对<span style="color: black;">所有</span>工作表了，不管是设置页眉和页脚还是打印你工作表。</span><strong style="color: blue;"><span style="color: black;">8</span></strong><strong style="color: blue;"><span style="color: black;">、</span></strong><strong style="color: blue;"><span style="color: black;">EXCEL</span></strong><strong style="color: blue;"><span style="color: black;">中有序号一栏，<span style="color: black;">因为</span>对表格进行<span style="color: black;">调节</span>，序号全乱了</span></strong><strong style="color: blue;"><span style="color: black;">,</span></strong><strong style="color: blue;"><span style="color: black;">可要是手动一个一个改序号实在太慢太麻烦，用什么<span style="color: black;">办法</span><span style="color: black;">能够</span>快速<span style="color: black;">处理</span>？</span></strong><span style="color: black;"><span style="color: black;">倘若</span>序号是<span style="color: black;">不该</span>随着表格其他内容的<span style="color: black;">调节</span>而<span style="color: black;">出现</span>变化的话，<span style="color: black;">那样</span>在制作</span>EXCEL<span style="color: black;">表格时就应将序号这一字段与其他字段<span style="color: black;">掰开</span>，如在“总分”与“排名”之间空开一列，为了不影响<span style="color: black;">表示</span>美观，可将这一空的列字段设为<span style="color: black;">隐匿</span>，<span style="color: black;">这般</span>在<span style="color: black;">调节</span>表格（数据<span style="color: black;">名单</span>）的内容时就不会影响序号了。</span><strong style="color: blue;"><span style="color: black;">9</span></strong><strong style="color: blue;"><span style="color: black;">、用</span></strong><strong style="color: blue;"><span style="color: black;">Excel2000</span></strong><strong style="color: blue;"><span style="color: black;">做成的工资表，<span style="color: black;">仅有</span><span style="color: black;">第1</span>个人有工资条的条头（如编号、姓名、岗位工资</span></strong><strong style="color: blue;"><span style="color: black;">.......)</span></strong><strong style="color: blue;"><span style="color: black;">，想输出成工资条的形式。怎么做？</span></strong><span style="color: black;">这个问题应该<span style="color: black;">这般</span><span style="color: black;">处理</span>：先复制一张工资表，<span style="color: black;">而后</span>在页面设置中选中工作表选项，设置打印工作表行标题，选好工资条的条头，<span style="color: black;">而后</span>在每一个人之间<span style="color: black;">插进</span>行分页符，再把页长设置成工资条的高度<span style="color: black;">就可</span></span><span style="color: black;">。</span><strong style="color: blue;"><span style="color: black;">10</span></strong><strong style="color: blue;"><span style="color: black;">、在Ｅ</span></strong><strong style="color: blue;"><span style="color: black;">xcel</span></strong><strong style="color: blue;"><span style="color: black;">中小数点<span style="color: black;">没法</span>输入，按小数点，<span style="color: black;">表示</span>的却是逗号，无论<span style="color: black;">怎么样</span>设置选项都无济于事，该怎么办？</span></strong><span style="color: black;">这是一个比较特殊的问题，我曾为此花了十几个小时的时间，但说白了很简单。在Ｗ</span>indows<span style="color: black;">的<span style="color: black;">掌控</span>面板中，点击“区域设置”图标，在弹出的“区域设置属性”对话面板上在“区域设置”里<span style="color: black;">选取</span>“中文（中国）”，在“区域设置属性”对话面板上在“数字”属性里把小数点改为“</span>.<span style="color: black;">”（未改前是“，”），按“确定”按钮结束。<span style="color: black;">这般</span>再打开</span>Excel<span style="color: black;">就一切都正常了。</span><strong style="color: blue;"><span style="color: black;">11</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>快速<span style="color: black;">选择</span>特定区域？</span></strong><span style="color: black;"><span style="color: black;">运用</span></span>F5<span style="color: black;">键<span style="color: black;">能够</span>快速<span style="color: black;">选择</span>特定区域。例如，要<span style="color: black;">选择</span></span>A2<span style="color: black;">：</span>A1000<span style="color: black;">，最简便的<span style="color: black;">办法</span>是按</span>F5<span style="color: black;">键，<span style="color: black;">显现</span>“定位”窗口，在“引用”栏内输入需<span style="color: black;">选择</span>的区域</span>A2<span style="color: black;">：</span>A1000<span style="color: black;">。</span><strong style="color: blue;"><span style="color: black;">12</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>快速返回选中区域？</span></strong><span style="color: black;">按</span>Ctr+BacksPae<span style="color: black;">（即退格键）。</span><strong style="color: blue;"><span style="color: black;">13</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>快速定位到单元格？</span></strong><span style="color: black;"><span style="color: black;">办法</span>一：按</span>F5<span style="color: black;">键，<span style="color: black;">显现</span>“定位”对话框，在引用栏中输入欲跳到的单元格<span style="color: black;">位置</span>，单市“确定”按钮<span style="color: black;">就可</span>。</span><span style="color: black;"><span style="color: black;">办法</span>二：单击编辑栏左侧单元格<span style="color: black;">位置</span>框，输入单元格<span style="color: black;">位置</span><span style="color: black;">就可</span>。</span><strong style="color: blue;"><span style="color: black;">14</span></strong><strong style="color: blue;"><span style="color: black;">、“</span></strong><strong style="color: blue;"><span style="color: black;">Ctrl</span></strong><strong style="color: blue;"><span style="color: black;">＋</span></strong><strong style="color: blue;"><span style="color: black;">*</span></strong><strong style="color: blue;"><span style="color: black;">”的特殊功用？</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">通常</span><span style="color: black;">来讲</span>，当处理一个工作表中有<span style="color: black;">非常多</span>数据的表格时，<span style="color: black;">经过</span>选定表格中某个单元格，<span style="color: black;">而后</span>按下</span>Ctrl<span style="color: black;">＋</span>* <span style="color: black;">键可选定<span style="color: black;">全部</span>表格。</span>Ctfl<span style="color: black;">＋</span>* <span style="color: black;">选定的区域是<span style="color: black;">这般</span>决定的：<span style="color: black;">按照</span>选定单元格向四周辐射所<span style="color: black;">触及</span>到的有数据单元格的最大区域。</span><strong style="color: blue;"><span style="color: black;">15</span></strong><strong style="color: blue;"><span style="color: black;">．<span style="color: black;">怎样</span>快速<span style="color: black;">选择</span>工作表中所有<span style="color: black;">包括</span>公式的单元格？</span></strong><span style="color: black;">有时，需要对工作表中所有<span style="color: black;">包括</span>公式的单元格加以<span style="color: black;">守护</span>，或填入与其他单元格<span style="color: black;">区别</span>的颜色，以提醒用户<span style="color: black;">重视</span><span style="color: black;">不可</span>在有此颜色的区域内输入数据。以下<span style="color: black;">办法</span><span style="color: black;">能够</span><span style="color: black;">帮忙</span>快速<span style="color: black;">选择</span>所有<span style="color: black;">包括</span>公式的单元格：CTRL+G，单击“定位<span style="color: black;">要求</span>”按钮，在“定位<span style="color: black;">要求</span>”对话框中<span style="color: black;">选取</span>“公式”项，按“确定”按钮<span style="color: black;">就可</span>。</span><strong style="color: blue;"><span style="color: black;">16</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>在<span style="color: black;">区别</span>单元格中快速输入同一数内容？</span></strong><span style="color: black;">选定单元格区域，输入值，<span style="color: black;">而后</span>按</span> Ctrl<span style="color: black;">＋</span> Ener<span style="color: black;">键，<span style="color: black;">就可</span>实<span style="color: black;">此刻</span>选定的单元格区域中一次性输入相同的值。</span><strong style="color: blue;"><span style="color: black;">17</span></strong><strong style="color: blue;"><span style="color: black;">、只记得函数的名<span style="color: black;">叫作</span>，但记不清函数的参数了，怎么办？</span></strong><span style="color: black;"><span style="color: black;">倘若</span>你<span style="color: black;">晓得</span>所要<span style="color: black;">运用</span>函数的名字，但又记不清它的所有参数格式，<span style="color: black;">那样</span><span style="color: black;">能够</span>用键盘快捷键把参数粘贴到编辑栏内。</span><span style="color: black;"><span style="color: black;">详细</span><span style="color: black;">办法</span>是：在编辑栏中输入一个等号其后接函数名，<span style="color: black;">而后</span>按</span> <span style="color: black;">Ctr<span style="color: black;">＋</span>A</span><span style="color: black;">键，</span>Excel<span style="color: black;">则自动进入“函数指南——<span style="color: black;">过程</span></span> 2<span style="color: black;">之</span>2<span style="color: black;">”。当<span style="color: black;">运用</span>易于记忆的名字且<span style="color: black;">拥有</span>很长一串参数的函数时，<span style="color: black;">以上</span><span style="color: black;">办法</span><span style="color: black;">显出</span><span style="color: black;">尤其</span>有用。</span><strong style="color: blue;"><span style="color: black;">18</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>把选定的一个或多个单元格拖放至新的位置？</span></strong><span style="color: black;">按住</span>Shift<span style="color: black;">键<span style="color: black;">能够</span>快速修改单元格内容的<span style="color: black;">秩序</span>。<span style="color: black;">详细</span><span style="color: black;">办法</span>是：</span><span style="color: black;">选定单元格，按下</span>Shift<span style="color: black;">键，移动鼠标指针至单元格边缘，直至<span style="color: black;">显现</span>拖放指针箭头（空心箭头），<span style="color: black;">而后</span>按住鼠标左键进行拖放操作。上下拖拉时鼠标在单元格间边界处会变为一个水平“工”状标志，<span style="color: black;">上下</span>拖拉时会变为垂直“工”状标志，释放鼠标按钮完成操作后，选定的一个或多个单元格就被拖放至新的位置。</span><strong style="color: blue;"><span style="color: black;">19</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>让屏幕上的工作空间变大？</span></strong><span style="color: black;"><span style="color: black;">能够</span>将<span style="color: black;">不消</span>的工具栏<span style="color: black;">隐匿</span>，<span style="color: black;">亦</span><span style="color: black;">能够</span><span style="color: black;">极重</span>化</span>Excel<span style="color: black;">窗口，<span style="color: black;">或</span>在“视图”菜单中<span style="color: black;">选取</span>“全屏<span style="color: black;">表示</span>”命令。</span><strong style="color: blue;"><span style="color: black;">20</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span><span style="color: black;">运用</span>快显菜单？</span></strong><span style="color: black;">快显菜单中<span style="color: black;">包含</span>了<span style="color: black;">有些</span>操作中最常用的命令，利用它们<span style="color: black;">能够</span>大大<span style="color: black;">加强</span>操作效率。<span style="color: black;">首要</span>选定一个区域，<span style="color: black;">而后</span>单击鼠标右健<span style="color: black;">就可</span>调出快显菜单，<span style="color: black;">按照</span>操作需要<span style="color: black;">选取</span><span style="color: black;">区别</span>命令。</span><strong style="color: blue;"><span style="color: black;">（二）</span></strong><strong style="color: blue;"><span style="color: black;"> 21</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span><span style="color: black;">运用</span>快显菜单？</span></strong><span style="color: black;">快显菜单中<span style="color: black;">包含</span>了<span style="color: black;">有些</span>操作中最常用的命令，利用它们<span style="color: black;">能够</span>大大<span style="color: black;">加强</span>操作效率。<span style="color: black;">首要</span>选定一个区域，<span style="color: black;">而后</span>单击鼠标右健<span style="color: black;">就可</span>调出快显菜单，<span style="color: black;">按照</span>操作需要<span style="color: black;">选取</span><span style="color: black;">区别</span>命令。</span><strong style="color: blue;"><span style="color: black;">22</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>防止</span></strong><strong style="color: blue;"><span style="color: black;">Excel</span></strong><strong style="color: blue;"><span style="color: black;">自动打开太多文件？</span></strong><span style="color: black;">当</span>Excel<span style="color: black;"><span style="color: black;">起步</span>时，它会自动打开</span>Xlstart<span style="color: black;">目录下的所有文件。当该目录下的文件<span style="color: black;">太多</span>时，</span>Excel<span style="color: black;">加载太多文件<span style="color: black;">不仅</span>费时<span style="color: black;">况且</span>还有可能出错。<span style="color: black;">处理</span><span style="color: black;">办法</span>是将<span style="color: black;">不应</span><span style="color: black;">位置于</span></span>Xlstart<span style="color: black;">目录下的文件移走。<span style="color: black;">另一</span>，还要防止</span>EXcel<span style="color: black;">打开替补<span style="color: black;">起步</span>目录下的文件：<span style="color: black;">选取</span>“工具”＼“选项”＼“普通”，将“替补<span style="color: black;">起步</span>目录”一栏中的所有内容删除。</span><strong style="color: blue;"><span style="color: black;">23</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>去掉网格线？</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><strong style="color: blue;"><span style="color: black;">excel2010版：视图 - 去掉网格线的勾选</span></strong></span>1<span style="color: black;">）除去编辑窗口中的表格线，单击“工具”菜单中的“选项”，再选中“视图”，找到“网格线”，使之失效；</span>2<span style="color: black;">）除去打印时的未定义表格线</span><span style="color: black;">有时会<span style="color: black;">显现</span><span style="color: black;">这般</span>的<span style="color: black;">状况</span>：你在编辑时未定义的表格线（在编辑窗中看到的<span style="color: black;">亦</span>是淡灰色表格线），<span style="color: black;">通常</span><span style="color: black;">状况</span>下在打印时是不会打印出来的，可有时却偏偏不听使唤给打印出来了，<span style="color: black;">尤其</span>是<span style="color: black;">有些</span><span style="color: black;">所说</span>的“电脑”</span>VCD<span style="color: black;">中编辑的</span>Excel<span style="color: black;">表格<span style="color: black;">更加是</span><span style="color: black;">这般</span>。要除去这些表格线，只要在单击“文件”、“页面设置”、“工作表”菜单，点击一下“网格线”左边的<span style="color: black;">选取</span>框，取消<span style="color: black;">选取</span>“网格线”就行了。</span><strong style="color: blue;"><span style="color: black;">24</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>快速格式化报表？</span></strong><span style="color: black;">为了制作出美观的报表，需要对报表进行格式化。有快捷<span style="color: black;">办法</span>，即自动套用</span>Excel<span style="color: black;">预设的表格样式。<span style="color: black;">办法</span>是：</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">excel2010版：<span style="color: black;">起始</span> - 套用表格格式</span><span style="color: black;">选定操作区域，<span style="color: black;">选择</span>“格式”菜单中的“自动套用格式”命令，在格式列表框中<span style="color: black;">选择</span>一款你满意的格式样式，按“确定”按钮<span style="color: black;">就可</span>。要<span style="color: black;">重视</span>的是，格式列表框下面有<span style="color: black;">包含</span>“数字”、“边框线”、“字体”等</span>6<span style="color: black;">个“应用格式种类”选项，若某项前面的“</span>x<span style="color: black;">”不<span style="color: black;">显现</span>，则在套用表格样式时就不会用该项。</span><strong style="color: blue;"><span style="color: black;">25</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>快速地复制单元格的格式？</span></strong><span style="color: black;">要将某一格式化操作复制到另一部分数据上，可<span style="color: black;">运用</span>“格式刷”按钮。<span style="color: black;">选取</span>含有所需源格式的单元格，单击工具条上的“格式刷”按钮，此时鼠标变<span style="color: black;">成为了</span>刷子形状，<span style="color: black;">而后</span>单击要格式化的单元格<span style="color: black;">就可</span>将格式拷贝过去。</span><strong style="color: blue;"><span style="color: black;">26</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>为表格添加斜线？</span></strong><span style="color: black;"><span style="color: black;">通常</span><span style="color: black;">咱们</span>习惯表格上有斜线，而工作表本身并<span style="color: black;">无</span><span style="color: black;">供给</span>该功能。其实，<span style="color: black;">咱们</span><span style="color: black;">能够</span><span style="color: black;">运用</span>绘图工具来实现：</span><span style="color: black;">单击“绘图”按钮，<span style="color: black;">选择</span>“直线”，鼠标变成十字型．将其移至要添加斜线的<span style="color: black;">起始</span>位置，按住鼠标左键拖动至终止位置，释放鼠标，斜线就画出来了。<span style="color: black;">另一</span>，<span style="color: black;">运用</span>“文字框”按钮<span style="color: black;">能够</span>方便地在斜线上下方添加文字，但文字<span style="color: black;">周边</span>有边框，<span style="color: black;">想要</span>取消它，可选中文字框，调出快显菜单，<span style="color: black;">选取</span>“对象格式”＼“图案”，<span style="color: black;">选取</span>“无边框”项<span style="color: black;">就可</span>。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">excel2010版</span>：<span style="color: black;"><span style="color: black;">插进</span>选项卡 - 形状 - 直线</span><strong style="color: blue;"><span style="color: black;">27</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>快速地将数字<span style="color: black;">做为</span>文本输入？</span></strong><span style="color: black;">在 输入数字前加一个单引号" "，<span style="color: black;">能够</span>强制地将数字<span style="color: black;">做为</span>文本输入。</span><strong style="color: blue;"><span style="color: black;">28</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>定义自己的函数？</span></strong><span style="color: black;">用户在</span>Excel<span style="color: black;">中<span style="color: black;">能够</span>自定义函数。切换至</span> Visual Basic<span style="color: black;">模块，或<span style="color: black;">插进</span>一页新的模块表（</span>Module<span style="color: black;">），在<span style="color: black;">显现</span>的空白程序窗口中键入自定义函数</span>VBA<span style="color: black;">程序，按</span>Enter<span style="color: black;">确认后完成编</span><span style="color: black;">写工作，</span>Excel<span style="color: black;">将自动<span style="color: black;">检测</span>其正确性。此后，在同一工作薄内，你就<span style="color: black;">能够</span>与<span style="color: black;">运用</span></span>Exed<span style="color: black;">内部函数<span style="color: black;">同样</span>在工作表中<span style="color: black;">运用</span>自定义函数，如：</span>
</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">Function Zm(a) </p>If a<span style="color: black;">＜</span> 60 Then im<span style="color: black;">＝‘不及格”</span>Else Zm=<span style="color: black;">“及格”</span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">End If </p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">End Function </p><strong style="color: blue;"><span style="color: black;">29</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>在一个与自定义函数驻留工作簿<span style="color: black;">区别</span>的工作簿内的工作表公式中调用自定义</span></strong><strong style="color: blue;"><span style="color: black;">函数？</span></strong><span style="color: black;">可在<span style="color: black;">包括</span>自定义函数的工作薄打开的前提下，采用链接的<span style="color: black;">办法</span></span>(<span style="color: black;"><span style="color: black;">亦</span><span style="color: black;">便是</span>在调用函数时加上该函数所在的工作簿名</span>)<span style="color: black;">。假设上例中的自定义函数</span>Zm<span style="color: black;">所在工作薄为</span>MYUDF.XLS<span style="color: black;">，现要在另一<span style="color: black;">区别</span>工作簿中的工作表公式中调用</span>Zm<span style="color: black;">函数，应<span style="color: black;">首要</span><span style="color: black;">保证</span></span>MYUDF.XLS<span style="color: black;">被打开，<span style="color: black;">而后</span><span style="color: black;">运用</span>下述链接的<span style="color: black;">办法</span>：</span>=MYUDF.XLS<span style="color: black;">！</span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"> ZM(b2) </p><strong style="color: blue;"><span style="color: black;">30</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>快速输入数据序列？</span></strong><span style="color: black;"><span style="color: black;">倘若</span>你需要输入诸如表格中的项目序号、日期序列等<span style="color: black;">有些</span>特殊的数据系列，千万别逐条输入，<span style="color: black;">为么</span>不让</span>Excel<span style="color: black;">自动填充呢？在<span style="color: black;">第1</span>个单元格内输入<span style="color: black;">初始</span>数据，在下一个单元格内输入第二个数据，选定这两个单元格，将光标指向单元格右下方的填充柄，沿着要填充的方向拖动填充柄，拖过的单元格中会自动按</span>Excel<span style="color: black;">内部规定的序列进行填充。<span style="color: black;">倘若</span>能将自己经常要用到的某些有规律的数据（如办公室人员名单），定义成序列，以备日后自动填充，岂不一劳永逸！<span style="color: black;">选取</span>“工具”菜单中的“选项”命令，再<span style="color: black;">选取</span>“自定义序列”标签，在输入框中输入新序列，<span style="color: black;">重视</span>在新序列各项</span>2<span style="color: black;">间要输入半角符号的逗号加以分隔（例如：张三，李四，王二……），单击“<span style="color: black;">增多</span>”按钮将输入的序列<span style="color: black;">保留</span>起来。</span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">excel2010版自定义序列：文件 - excel选项 - 高级 - 翻到最下面找到"编辑自定义列表"</span><strong style="color: blue;"><span style="color: black;">31</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">运用</span>鼠标右键拖动单元格填充柄？</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">上例<span style="color: black;">中间商</span>绍了<span style="color: black;">运用</span>鼠标左键拖动单元格填充柄自动填充数据序列的<span style="color: black;">办法</span>。其实，<span style="color: black;">运用</span>鼠标右键拖动单元格填充柄则更具灵活性。在某单元格内输入数据，按住鼠标右键沿着要填充序列的方向拖动填充柄，将会<span style="color: black;">显现</span><span style="color: black;">包括</span>下列各项的菜单：复制单元格、以序列方式填充、以格式填充、以值填充；以天数填充、以工作日该充、以月该充、以年填充；序列……此时，你<span style="color: black;">能够</span><span style="color: black;">按照</span>需要<span style="color: black;">选取</span>一种填充方式。</span><strong style="color: blue;"><span style="color: black;">32</span></strong><strong style="color: blue;"><span style="color: black;">．<span style="color: black;">倘若</span>你的工作表中已有某个序列项，想把它定义成自动填充序列以备后用，<span style="color: black;">是不是</span>需要<span style="color: black;">根据</span>上面介绍的自定义序列的<span style="color: black;">办法</span>重新输入这些序列项？</span></strong><span style="color: black;">不需要。有快捷<span style="color: black;">办法</span>：选定<span style="color: black;">包括</span>序列项的单元格区域，<span style="color: black;">选取</span>“工具”＼“选项”＼“自定义序列”，单击“引入”按钮将选定区域的序列项添加至“自定义序列”对话框，按“确定”按钮返回工作表，下次就<span style="color: black;">能够</span>用这个序列项了。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">excel2010操作同32打开自定义序列操作界面</span><strong style="color: blue;"><span style="color: black;">33</span></strong><strong style="color: blue;"><span style="color: black;">、上例中，<span style="color: black;">倘若</span>你已拥育的序列项中含有许多重复项，应<span style="color: black;">怎样</span>处理使其<span style="color: black;">无</span>重复项，以便<span style="color: black;">运用</span>“引入”的<span style="color: black;">办法</span>快速创建所需的自定义序列？</span></strong><span style="color: black;">选定单元格区域，<span style="color: black;">选取</span>“数据”＼“筛选”＼“高级筛选”，选定“不选重复的记录”选项，按“确定”按钮<span style="color: black;">就可</span>。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">excel2010中：选中区域 - 数据 - 删除重复项</span><strong style="color: blue;"><span style="color: black;">34</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>对工作簿进行安全<span style="color: black;">守护</span>？</span></strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;"><strong style="color: blue;">excel2010版：审阅 - <span style="color: black;">守护</span>工作簿; 工作表</strong></span></span><span style="color: black;"><span style="color: black;">倘若</span>你不想别人打开或修改你的工作簿，<span style="color: black;">那样</span>想法加个<span style="color: black;">秘码</span>吧。打开工作薄，<span style="color: black;">选取</span>“文件”菜单中的“另存为”命令，<span style="color: black;">选择</span>“选项”，<span style="color: black;">按照</span>用户的需要分别输入“打开文件口令”或“修改文件</span>D<span style="color: black;">令”，按“确定”退出。工作簿（表）被<span style="color: black;">守护</span>之后，还可对工作表中某些单元格区域的重要数据进行<span style="color: black;">守护</span>，起到双重<span style="color: black;">守护</span>的功能，此时你<span style="color: black;">能够</span><span style="color: black;">这般</span>做：<span style="color: black;">首要</span>，选定需<span style="color: black;">守护</span>的单元格区域，<span style="color: black;">选择</span>“格式”菜单中的“单元格”命令，<span style="color: black;">选择</span>“<span style="color: black;">守护</span>”，从对话框中<span style="color: black;">选择</span>“锁定”，单由“确定”按钮退出。<span style="color: black;">而后</span><span style="color: black;">选择</span>“工具”菜单中的“<span style="color: black;">守护</span>”命令，<span style="color: black;">选择</span>“<span style="color: black;">守护</span>工作表”，<span style="color: black;">按照</span>提示两次输入口令后退出。</span><span style="color: black;"><span style="color: black;">重视</span>：不要忘记你设置有“口令”。</span><strong style="color: blue;"><span style="color: black;">35</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>使单元格中的颜色和底纹不打印出来？</span></strong><span style="color: black;">对<span style="color: black;">哪些</span>加了<span style="color: black;">守护</span>的单元格，还<span style="color: black;">能够</span>设置颜色和底纹，以便让用户一目了然，从颜色上看出<span style="color: black;">哪些</span>单元格加了<span style="color: black;">守护</span><span style="color: black;">不可</span>修改，从而可<span style="color: black;">增多</span>数据输入时的直观感觉。但却带来了问题，即在黑白打印时<span style="color: black;">倘若</span>连颜色和底纹都打出来，表格的可视性就大打折扣。<span style="color: black;">处理</span>办法是：<span style="color: black;">选取</span>“文件”＼“页面设置”＼“工作表”，在“打印”栏内<span style="color: black;">选取</span>“单元格单色打印”选项。之后，打印出来的表格就面目如初了。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">excel2010版：页面布局 - 打开标题，打开打印设置窗口</span><strong style="color: blue;"><span style="color: black;">36</span></strong><strong style="color: blue;"><span style="color: black;">、工作表<span style="color: black;">守护</span>的口令忘记了怎么办？</span></strong><span style="color: black;"><span style="color: black;">倘若</span>你想<span style="color: black;">运用</span>一个<span style="color: black;">守护</span>了的工作表，但口令又忘记了，有办法吗？有。选定工作表，<span style="color: black;">选取</span>“编辑”＼“复制”、“粘贴”，将其拷贝到一个新的工作薄中（<span style="color: black;">重视</span>：<span style="color: black;">必定</span>要是新工作簿），<span style="color: black;">就可</span>超越工作表<span style="color: black;">守护</span>。当然，提醒你最好<span style="color: black;">不消</span>这种<span style="color: black;">办法</span>盗用他人的工作表。</span><strong style="color: blue;"><span style="color: black;">37</span></strong><strong style="color: blue;"><span style="color: black;">、“</span></strong><strong style="color: blue;"><span style="color: black;">$</span></strong><strong style="color: blue;"><span style="color: black;">”的功用：</span></strong>Excel<span style="color: black;"><span style="color: black;">通常</span><span style="color: black;">运用</span>相对<span style="color: black;">位置</span>来引用单元格的位置，当把一个含有单元格<span style="color: black;">位置</span>的公式拷贝到一个新的位置，公式中的单元格<span style="color: black;">位置</span>会随着改变。你<span style="color: black;">能够</span>在列号或行号前添加符号“</span>$<span style="color: black;">”来冻结单元格<span style="color: black;">位置</span>，使之在拷贝时保持固定不变。</span><strong style="color: blue;"><span style="color: black;">38</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>用汉字名<span style="color: black;">叫作</span>代替单元格<span style="color: black;">位置</span>？</span></strong><span style="color: black;"><span style="color: black;">倘若</span>你不想<span style="color: black;">运用</span>单元格<span style="color: black;">位置</span>，<span style="color: black;">能够</span>将其定义成一个名字。</span><span style="color: black;">定义名字的<span style="color: black;">办法</span>有两种：一种是选定单元格区域后在“名字框”直接输入名字，另一种是选定想要命名的单元格区域，再<span style="color: black;">选取</span>“<span style="color: black;">插进</span>”＼“名字”＼“定义”（<span style="color: black;">excel2010版：公式 - 定义名<span style="color: black;">叫作</span></span>），在“当前工作簿中名字”对话框内键人名字<span style="color: black;">就可</span>。<span style="color: black;">运用</span>名字的公式比<span style="color: black;">运用</span>单元格<span style="color: black;">位置</span>引用的公式更易于记忆和阅读，<span style="color: black;">例如</span>公式“</span>=SUM<span style="color: black;">（实发工资）”显然比用单元格<span style="color: black;">位置</span>简单直观，<span style="color: black;">况且</span><span style="color: black;">很难</span>出错。</span><strong style="color: blue;"><span style="color: black;">39</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>在公式中快速输入不连续的单元格<span style="color: black;">位置</span>？</span></strong><span style="color: black;">在</span>SUM<span style="color: black;">函数中输入比较长的单元格区域字符串很麻烦，尤其是当区域为许多不连续单元格区域<span style="color: black;">构成</span>时。<span style="color: black;">此时</span>可按住</span>Ctrl<span style="color: black;">键，进行不连续区域的<span style="color: black;">选择</span>。区域选定后<span style="color: black;">选取</span>“<span style="color: black;">插进</span>”＼“名字”＼“定义”（<span style="color: black;">excel2010版：公式 - 定义名<span style="color: black;">叫作</span></span>），将此区域命名，如</span>Group1<span style="color: black;">，<span style="color: black;">而后</span>在公式中<span style="color: black;">运用</span>这个区域名，如“＝</span>SUM<span style="color: black;">（</span>Group1<span style="color: black;">）”。</span><strong style="color: blue;"><span style="color: black;">40</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>定义局部名字？</span></strong><span style="color: black;">在默认<span style="color: black;">状况</span>下，工作薄中的所有名字都是全局的。其实，<span style="color: black;">能够</span>定义局部名字，使之只对某个工作表有效，<span style="color: black;">办法</span>是将名字命名为“工作表名！名字”的形式<span style="color: black;">就可</span>。</span><strong style="color: blue;"><span style="color: black;">41</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>命名常数？</span></strong><span style="color: black;">有时，为常数指定一个名字<span style="color: black;">能够</span>节省在<span style="color: black;">全部</span>工作簿中修改替换此常数的时间。例如，在某个工作表中经常需用利率</span>4.9<span style="color: black;">％来计算利息，<span style="color: black;">能够</span><span style="color: black;">选取</span>“<span style="color: black;">插进</span>”＼“名字”＼“定</span><span style="color: black;">义”（<span style="color: black;">excel2010版：公式 - 定义名<span style="color: black;">叫作</span></span>），在“当前工作薄的名字”框内输入“利率”，在“引用位置”框中输入“＝</span> 0.04.9<span style="color: black;">”，按“确定”按钮。</span><strong style="color: blue;"><span style="color: black;">42</span></strong><strong style="color: blue;"><span style="color: black;">、工作表名<span style="color: black;">叫作</span>中能含有空格吗？</span></strong><span style="color: black;">能。例如，你<span style="color: black;">能够</span>将某工作表命名为“</span>Zhu Meng<span style="color: black;">”。有一点结<span style="color: black;">重视</span>的是，当你在其他工作表中调用该工作表中的数据时，<span style="color: black;">不可</span><span style="color: black;">运用</span>类似“＝</span> ZhU Meng<span style="color: black;">！</span>A2<span style="color: black;">”的公式，否则</span>Excel<span style="color: black;">将提示错误信息“找不到文件</span>Meng<span style="color: black;">”。<span style="color: black;">处理</span>的<span style="color: black;">办法</span>是，将调用公式改为“＝</span>Zhu Mg! A2<span style="color: black;">”就行了。当然，输入公式时，你最好养成<span style="color: black;">这般</span>的习惯，即在输入“＝”号以后，用鼠标单由</span>Zhu Meng<span style="color: black;">工作表，再输入余下的内容。</span><strong style="color: blue;"><span style="color: black;">43</span></strong><strong style="color: blue;"><span style="color: black;">、给工作表命名应<span style="color: black;">重视</span>的问题？</span></strong><span style="color: black;">有时为了直观，<span style="color: black;">常常</span>要给工作表重命名（</span>Excel<span style="color: black;">默认的荼表名是</span>sheet1<span style="color: black;">、</span>sheet2.....<span style="color: black;">），在重命名时应<span style="color: black;">重视</span>最好不要用已存在的函数名来作荼表名，否则在下述<span style="color: black;">状况</span>下将产征收岂义。<span style="color: black;">咱们</span><span style="color: black;">晓得</span>，在工作薄中复制工作表的<span style="color: black;">办法</span>是，按住</span>Ctrl<span style="color: black;">健并沿着标签行拖动选中的工作表到达新的位置，复制成的工作表以“源工作表的名字</span>+<span style="color: black;">（</span>2<span style="color: black;">）”形式命名。例如，源表为</span>ZM<span style="color: black;">，则其“克隆”表为</span>ZM<span style="color: black;">（</span>2<span style="color: black;">）。在公式中</span>Excel<span style="color: black;">会把</span>ZM<span style="color: black;">（</span>2<span style="color: black;">）<span style="color: black;">做为</span>函数来处理，从而出错。因而应给</span>ZM<span style="color: black;">（</span>2<span style="color: black;">）工作表重起个名字。</span><strong style="color: blue;"><span style="color: black;">44</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>拆分或取消拆分窗口？</span></strong><span style="color: black;">当<span style="color: black;">咱们</span>给一个工作表输入数据时，在向下滚动过程中，尤其是当标题行消失后，有时会记错各列标题的相对位置。<span style="color: black;">此时</span><span style="color: black;">能够</span>将窗口拆分为几部分，<span style="color: black;">而后</span>将标题部分<span style="color: black;">保存</span>在屏幕上不动，只滚动数据部分。其<span style="color: black;">办法</span>是在主菜单上单击“窗口”＼“拆分窗口”（<span style="color: black;">excel2010版：视图 - 拆分</span>）。取消拆分窗口时除了<span style="color: black;">运用</span>“窗口”＼“撒消拆分窗口”命令外，有捷径：将鼠标指针置于水平拆分或垂直拆分线或双拆分钱交点上，双击鼠标<span style="color: black;">就可</span>取消已拆分的窗口。</span><strong style="color: blue;"><span style="color: black;">45</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>给工作簿扩容？</span></strong><span style="color: black;"><span style="color: black;">选择</span>“工具”＼“选项”命令（<span style="color: black;">excel2010版：文件 - excel选项 - 常规</span> ），<span style="color: black;">选取</span>“常规”项，在“新工作薄内的工作表数”对话栏用上下箭头改变打开新工作表数。一个工作薄最多<span style="color: black;">能够</span>有</span>255<span style="color: black;">张工作表，系统默认值为</span>6<span style="color: black;">。</span><strong style="color: blue;"><span style="color: black;">46</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>减少重复劳动？</span></strong><span style="color: black;"><span style="color: black;">咱们</span>在<span style="color: black;">实质</span>应用</span>Excel<span style="color: black;">时，经常遇到有些操作重复应用（如定义上下标等）。为了减少重复劳动，<span style="color: black;">咱们</span><span style="color: black;">能够</span>把<span style="color: black;">有些</span>常用到的操作定义成宏。其<span style="color: black;">办法</span>是：<span style="color: black;">选择</span>“工具”菜单中的“宏”命令，执行“记录新宏”（<span style="color: black;">excel2010版：左下角 录制宏按钮，在就续的右邻</span>），记录好后按“停止”按钮<span style="color: black;">就可</span>。<span style="color: black;">亦</span><span style="color: black;">能够</span>用</span>VBA<span style="color: black;">编程定义宏。</span><strong style="color: blue;"><span style="color: black;">47</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>快速地批量修改数据？</span></strong><span style="color: black;">假如有一份</span> Excel<span style="color: black;">工作簿，里面有所有职工工资表。<span style="color: black;">此刻</span>想将所有职工的<span style="color: black;">补助</span><span style="color: black;">增多</span></span>50<span style="color: black;">（元），当然你<span style="color: black;">能够</span>用公式进行计算，但除此之外还有更简单的批量修改的<span style="color: black;">办法</span>，即<span style="color: black;">运用</span>“<span style="color: black;">选取</span>性粘贴”功能：</span><span style="color: black;"><span style="color: black;">首要</span>在某个空白单元格中输入</span>50<span style="color: black;">，选定此单元格，<span style="color: black;">选取</span>“编辑”＼“复制”。<span style="color: black;">选择</span>想修改的单元格区域，例如从</span>E2<span style="color: black;">到</span>E150<span style="color: black;">。<span style="color: black;">而后</span><span style="color: black;">选取</span>“编辑”＼“<span style="color: black;">选取</span>性粘贴”，在“<span style="color: black;">选取</span>性粘贴”对话框“运算”栏中选中“加”运算，按“确定”健<span style="color: black;">就可</span>。最后，要删除<span style="color: black;">起始</span>时在某个空白单元格中输入的</span>50<span style="color: black;">。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">excel2010版，复制 - 右键菜单中即有<span style="color: black;">选取</span>粘贴选项。</span></span><strong style="color: blue;"><span style="color: black;">48</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>快速删除特定的数据？</span></strong><span style="color: black;">假如有一份</span>Excel<span style="color: black;">工作薄，其中有<span style="color: black;">海量</span>的<span style="color: black;">制品</span>单价、数量和金额。<span style="color: black;">倘若</span>想将所有数量为</span>0<span style="color: black;">的行删除，<span style="color: black;">首要</span>选定区域（<span style="color: black;">包含</span>标题行），<span style="color: black;">而后</span><span style="color: black;">选取</span>“数据”＼“筛选”＼“自动筛选”。在“数量”列下拉列表中<span style="color: black;">选取</span>“</span>0<span style="color: black;">”，<span style="color: black;">那样</span>将列出所有数量为</span>0<span style="color: black;">的行。此时在所有行都被选中的<span style="color: black;">状况</span>下，<span style="color: black;">选取</span>“编辑”＼“删除行”，<span style="color: black;">而后</span>按“确定”<span style="color: black;">就可</span>删除所有数量为</span>0<span style="color: black;">的行。最后，取消自动筛选。</span><strong style="color: blue;"><span style="color: black;">49</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>快速删除工作表中的空行？</span></strong><span style="color: black;">以下几种<span style="color: black;">办法</span><span style="color: black;">能够</span>快速删除空行：</span><span style="color: black;"><span style="color: black;">办法</span>一：<span style="color: black;">倘若</span>行的<span style="color: black;">次序</span>无关紧要，则<span style="color: black;">能够</span><span style="color: black;">按照</span>某一列排序，<span style="color: black;">而后</span><span style="color: black;">能够</span>方便地删掉空行。</span><span style="color: black;"><span style="color: black;">办法</span>二：<span style="color: black;">倘若</span>行的<span style="color: black;">次序</span>不可改变，<span style="color: black;">插进</span>新的一列，在</span>A<span style="color: black;">列中<span style="color: black;">次序</span>填入整数。<span style="color: black;">而后</span><span style="color: black;">按照</span>其他任何一列将表中的行排序，使所有空行都集中到表的底部，删去所有空行。最后以</span>A<span style="color: black;">列重新排序，再删去</span>A<span style="color: black;">列，恢复工作表各行原来的<span style="color: black;">次序</span>。</span><span style="color: black;"><span style="color: black;">办法</span>三：<span style="color: black;">运用</span>上例“<span style="color: black;">怎样</span>快速删除特定的数据”的<span style="color: black;">办法</span>，只<span style="color: black;">不外</span>在所有列的下拉列表中都<span style="color: black;">选取</span>“空白”。</span><strong style="color: blue;"><span style="color: black;">50</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span><span style="color: black;">运用</span>数组公式？</span></strong>Excel<span style="color: black;">中数组公式非常有用，它可<span style="color: black;">创立</span>产生多值或对一组值而不是单个值进行操作的公式。要输入数组公式，<span style="color: black;">首要</span>必须<span style="color: black;">选取</span>用来存放结果的单元格区域，在编辑栏输入公式，<span style="color: black;">而后</span>按</span>ctrl<span style="color: black;">＋</span>Shift<span style="color: black;">＋</span>Enter<span style="color: black;">组合键锁定数组公式，</span>Excel<span style="color: black;">将在公式两边自动加上括号“</span>{}<span style="color: black;">”。不要自己键入花括号，否则，</span>Excel<span style="color: black;">认为输入的是一个正文标签。要编辑或清除数组公式．需<span style="color: black;">选取</span>数组区域并且激活编辑栏，公式两边的括号将消失，<span style="color: black;">而后</span>编辑或清除公式，最后按</span>Ctrl<span style="color: black;">＋</span>shift<span style="color: black;">＋</span>Enter<span style="color: black;">键。</span><strong style="color: blue;"><span style="color: black;">51</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>不使<span style="color: black;">表示</span>或打印出来的表格中<span style="color: black;">包括</span>有</span></strong><strong style="color: blue;"><span style="color: black;">0</span></strong><strong style="color: blue;"><span style="color: black;">值？</span></strong><span style="color: black;"><span style="color: black;">一般</span><span style="color: black;">状况</span>下，<span style="color: black;">咱们</span>不<span style="color: black;">期盼</span><span style="color: black;">表示</span>或打印出来的表格中<span style="color: black;">包括</span>有</span>0<span style="color: black;">值，而是将其内容置为空。例如，图</span>1<span style="color: black;">合计列中<span style="color: black;">倘若</span><span style="color: black;">运用</span>“</span>=b2+c2+d2<span style="color: black;">”公式，将有可能<span style="color: black;">显现</span></span>0<span style="color: black;">值的<span style="color: black;">状况</span>，<span style="color: black;">怎样</span>让</span>0<span style="color: black;">值不<span style="color: black;">表示</span>？</span><span style="color: black;"><span style="color: black;">办法</span>一；<span style="color: black;">运用</span>加上</span>If<span style="color: black;">函数判断值<span style="color: black;">是不是</span>为</span>0<span style="color: black;">的公式，即：</span> =if<span style="color: black;">（</span>b2<span style="color: black;">＋</span>c2<span style="color: black;">＋</span>d2<span style="color: black;">＝</span>0<span style="color: black;">，“”，</span>b2<span style="color: black;">＋</span>c2<span style="color: black;">＋</span>d2<span style="color: black;">）</span><span style="color: black;"><span style="color: black;">办法</span>二：<span style="color: black;">选取</span>“工具”＼“选项”＼“视窗”，在“窗口选项”中去掉“零值”选项。（<span style="color: black;">excel2010版，文件 - excel选项 - 高级 - 在<span style="color: black;">拥有</span>零值的单元格<span style="color: black;">表示</span>零去掉勾选</span>）</span><span style="color: black;"><span style="color: black;">办法</span>三：<span style="color: black;">运用</span>自定义格式。</span> <span style="color: black;">选中</span> E2<span style="color: black;">：</span>E5<span style="color: black;">区域，右键菜单中设置单元格格式 \\ 单元格”＼“数字”，从“<span style="color: black;">归类</span>”列表框中<span style="color: black;">选取</span>“自定义”，在“格式”框中输入“</span>G/<span style="color: black;">通用格式；</span>G/<span style="color: black;">通用格式；；”，按“确定”按钮<span style="color: black;">就可</span>。</span><strong style="color: blue;"><span style="color: black;">52</span></strong><strong style="color: blue;"><span style="color: black;">、在</span></strong><strong style="color: blue;"><span style="color: black;">Excel</span></strong><strong style="color: blue;"><span style="color: black;">中用</span></strong><strong style="color: blue;"><span style="color: black;">Average</span></strong><strong style="color: blue;"><span style="color: black;">函数计算单元格的平均值的，值为</span></strong><strong style="color: blue;"><span style="color: black;">0</span></strong><strong style="color: blue;"><span style="color: black;">的单元格<span style="color: black;">亦</span><span style="color: black;">包括</span>在内。有<span style="color: black;">无</span>办法在计算平均值时排除值为</span></strong><strong style="color: blue;"><span style="color: black;">0</span></strong><strong style="color: blue;"><span style="color: black;">的单元格？</span></strong><span style="color: black;"><span style="color: black;">办法</span>一：<span style="color: black;">倘若</span>单元格中的值为</span>0<span style="color: black;">，可用上例“</span>0<span style="color: black;">值不<span style="color: black;">表示</span>的<span style="color: black;">办法</span>”将其内容置为空，此时空单元格处理成文本，<span style="color: black;">这般</span>就<span style="color: black;">能够</span>直接用</span>Average<span style="color: black;">函数计算了。</span><span style="color: black;"><span style="color: black;">办法</span>二：巧用</span>Countif<span style="color: black;">函数</span> <span style="color: black;">例如，下面的公式可计算出</span>b2:B10<span style="color: black;">区域中非</span>0<span style="color: black;">单元格的平均值：</span>=sum<span style="color: black;">（</span>b2: b10<span style="color: black;">）</span>/countif<span style="color: black;">（</span>b2:b1o<span style="color: black;">，</span>"<>0"<span style="color: black;">）</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">excel2010中可以<span style="color: black;">运用</span>averageif函数</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">=AVERAGEIF(N8:N10,">0")</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">53</span></strong><strong style="color: blue;"><span style="color: black;">、<span style="color: black;">怎样</span>在</span></strong><strong style="color: blue;"><span style="color: black;">Excel</span></strong><strong style="color: blue;"><span style="color: black;">中实现“自动更正”功能？</span></strong>excel2003版，工具 - 自动更正</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">excel2010版，文件 - excel选项 - 校对 - 自动更正选项</span></p><span style="color: black;"><strong style="color: blue;"><span style="color: black;">excel精英培训</span></strong><span style="color: black;">ID:excelpx-tete</span></span>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">每日</span>一篇原创<span style="color: black;">Excel</span>教程，伴你学习Excel每一天！</strong><span style="color: black;">长按</span><span style="color: black;">下面二维码<span style="color: black;">照片</span>，点上面”</span><span style="color: black;">识别图中二维码</span><span style="color: black;">“<span style="color: black;">而后</span>再点关注，<span style="color: black;">每日</span><span style="color: black;">能够</span>收到一篇兰色最新写的excel教程。</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="http://mmbiz.qpic.cn/mmbiz/4jUToJBtQvSebUj6fyjNHbIktD1ls6xopyj5VIDU9waiaHa55s7G4Ric1cM2fAZxneEFsPLguWneYQQdbqZ2iawHg/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
你的见解独到，让我受益匪浅，非常感谢。 论坛外链网http://www.fok120.com/ 楼主听话，多发外链好处多，快到碗里来！外链论坛 http://www.fok120.com/ 感谢楼主分享，祝愿外链论坛越办越好！
页:
[1]