24个Excel表格制作技巧，让你工作效率翻一倍
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">技巧</span><span style="color: black;">1</span><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>一批注，<span style="color: black;">而后</span>复制多列数据，再<span style="color: black;">选择</span>批注按ctrl+v粘贴，<span style="color: black;">而后</span>再复制批注文字粘贴到表格中<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;">重视</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;">动图</span><img src="https://mmbiz.qpic.cn/mmbiz/4jUToJBtQvSIYaMpZhGPHThG3Nh8GYXNjsSJcy2PVuyn20r7oPCDicqrk46wGkmxUEQCHSOjiaRf8ZU1ciaxwDSrA/640?wx_fmt=gif&tp=webp&wxfrom=5&wx_lazy=1" style="width: 50%; margin-bottom: 20px;"></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;">2</span>、筛选<span style="color: black;">以1<span style="color: black;">起始</span></span>和<span style="color: black;">以8<span style="color: black;">起始</span></span>的数字</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">在搜索框中输入<span style="color: black;">1</span><span style="color: black;">*</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/4jUToJBtQvQobzic9ibOD1AMdbfxCqZ9WQPlIyyb3sh3oyIbZM0A8C3xsZsldrutIV8RMcFn13gpPFniaPiaIx5EfA/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">再打开搜索窗口，在搜索框中输入<span style="color: black;">8</span><span style="color: black;">*</span>，<span style="color: black;">选择</span>“<span style="color: black;">将当前所选内容添加到筛选器</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/4jUToJBtQvQobzic9ibOD1AMdbfxCqZ9WQSoDBKSAgRwbOrozS875q7q5Ic4GNPRZN1xk6ZweUgXibJoWOMtIKtFQ/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">筛选后的结果：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="http://mmbiz.qpic.cn/mmbiz/4jUToJBtQvQobzic9ibOD1AMdbfxCqZ9WQEzb3OqX0FS2Bt7sptT1CmDeNvfuaWo1Qav7ZnfxdhsgaFcT8RLBHxQ/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></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;">3</span>、一列数字前批量加汉字</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">选择</span>数字列，按ctrl+数字1打开单元格格式设置窗口 - 数字 - 自定义 ，右而框中输入代码：<span style="color: black;">"编号:"000</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz/4jUToJBtQvSicv91ArAWpPnl3AW4eiaib2v1gib84qSI16UGNVBnk2F3IVGfuFVXd58Rwf6dqXjYbHlic4To21I1Few/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"><span style="color: black;">完成后效果：</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="https://mmbiz.qpic.cn/mmbiz_jpg/3iaGPbSibqshmicjB81dPK2N9LmMGWfAEXuFtxJ2Qkjib1YIzfqBcSJQhAQus5iaF7DficM4DmgN0BEUEwSUNK037ic1g/640?wx_fmt=jpeg&tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></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;">4</span>、单元格内强制换行</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">在单元格中某个字符后按alt+回车键，<span style="color: black;">就可</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/4jUToJBtQvSbjRkDRdELODiakzVJic9sRylSOwEArsRvFV7jtuqqToj06neVNCEL2HMOtBnuZugu90ZqRO6rCVgg/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></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;">5</span>、锁定标题行</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">选择</span>第2行，视图 - 冻结窗格 - 冻结首行（或<span style="color: black;">选择</span>第2行 - 冻结窗格）冻结后再向下翻看时标题行始终<span style="color: black;">表示</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/4jUToJBtQvSbjRkDRdELODiakzVJic9sRydHFeBe9fnQLjtpbTydia2gBeTX0zcI36W6Q6ZtRN95hJMy2aHc1Mpqw/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></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;">6</span>、打印标题行</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">倘若</span>想在打印时每一页都<span style="color: black;">表示</span>标题，页面布局 - 打印标题 - 首端标题行：<span style="color: black;">选择</span>要<span style="color: black;">表示</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/4jUToJBtQvSbjRkDRdELODiakzVJic9sRy3W8vysbaSq3YocUxu2Z2TVc8vib0njKlVuMZc2G0QIRJGPnamIL6erQ/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></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;">7</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;">选择</span>数据区域 - <span style="color: black;">起始</span> - <span style="color: black;">要求</span>格式 - <span style="color: black;">明显</span><span style="color: black;">表示</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/4jUToJBtQvSbjRkDRdELODiakzVJic9sRyJmQ20EtT7pibHWa6zmn1CzI4iaEOo8EtMQicmCng7MKRzJFZOU7yIdyCw/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"><span style="color: black;">表示</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/4jUToJBtQvSbjRkDRdELODiakzVJic9sRy5icsOo5fNlrMhrwSUoVnQwrxHmnibA8raxEvjN2h8zIJeR0D5ulDb0ng/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"><span style="color: black;">技巧<span style="color: black;">8</span>、删除重复值</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">选择</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/4jUToJBtQvSbjRkDRdELODiakzVJic9sRyEKPbDib5A3Os10Kg6hSicch9TGfCKEbooTjJ8ia8c4rAwTLmJYJVcyw4w/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></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;">9</span>、快速输入对号√</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">在excel中输入符号最快的方式<span style="color: black;">便是</span>利用 alt+数字 的方式，<span style="color: black;">例如</span>输入<span style="color: black;">√</span>，你<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;">alt</span>不松，<span style="color: black;">而后</span>按小键盘的数字键： <span style="color: black;">41420 </span></span></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;">10</span>、万元<span style="color: black;">表示</span></strong></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">在一个空单元格输入10000（<span style="color: black;"><span style="color: black;">意见</span>设置数字格式和边框</span>） - 复制它 - <span style="color: black;">选取</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/4jUToJBtQvSbjRkDRdELODiakzVJic9sRy8wcYkh2tUhApoibCeOUdMZP9tYMzpvVI6sfghMxZ3vvgxwU02IcgmnQ/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">转换后</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="http://mmbiz.qpic.cn/mmbiz/4jUToJBtQvSbjRkDRdELODiakzVJic9sRykTMqvOWC6TmM5JIMAGdNMHictNQYlI7Yl0jyia84yTFSYNr16IU6YYUA/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></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;">11</span>、<span style="color: black;">隐匿</span>0值</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">表格中的0值<span style="color: black;">倘若</span>不想<span style="color: black;">表示</span>，<span style="color: black;">能够</span><span style="color: black;">经过</span>：文件 - excel选项 - 高级 - 在<span style="color: black;">拥有</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/4jUToJBtQvSbjRkDRdELODiakzVJic9sRyOX7TSFd4aEiab7o5LZfXsGLCcJmtKFho6CTP8jVvE9HSRNf4eSVfkYg/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></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;">12</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;">倘若</span>要<span style="color: black;">隐匿</span>单元格的值，<span style="color: black;">选择</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/4jUToJBtQvSbjRkDRdELODiakzVJic9sRyN2hwnzayQWc35r2SxJ1aDMuyLgPu4R6mVmct7iab47S3v8hThG9gfkA/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></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;">13</span>、单元格中输入00001</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;">倘若</span>在单元格中输入以0开头的数字，<span style="color: black;">能够</span>输入前把格式设置成文本格式，<span style="color: black;">倘若</span>想固定位数（如5位）不足用0补齐，<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;">选择</span>该区域，右键 - 设置单元格格式 - 数字 - 自定义 - 右侧文本框中输入 <span style="color: black;">00000</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">输入1<span style="color: black;">就可</span><span style="color: black;">表示</span>00001</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="http://mmbiz.qpic.cn/mmbiz/4jUToJBtQvSbjRkDRdELODiakzVJic9sRysl5Ozl9n7CUVHqKDG9NeBb0TsCnRRyoQxwsJvk14IfKXBIkUj2qaEQ/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></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;">14</span>、按月填充日期</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">日期所在单元格向下拖动复制后，打开粘贴列表，<span style="color: black;">选择</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/4jUToJBtQvSbjRkDRdELODiakzVJic9sRy7YkFIZvtPniboo6cTy2kT9saC2Cwhz7ejbGqqODPRFA8hYF9466UU4Q/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></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;">15</span>、合并多个单元格内容</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">把列宽<span style="color: black;">调节</span>成能容易合并后字符，<span style="color: black;">而后</span><span style="color: black;">选择</span>合并的区域 - <span style="color: black;">起始</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/4jUToJBtQvSbjRkDRdELODiakzVJic9sRye8QGcmUmojzf6SWDiax4WXfn6QQzZqLlVjdeR8E1X4bMgmWYf8PicRPg/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;">合并后：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="http://mmbiz.qpic.cn/mmbiz/4jUToJBtQvSbjRkDRdELODiakzVJic9sRymqib4n6K4SZMrRYqoiaePBkRk54Rzibwh3pZbyZ3JictOHIsJTWeJDqPEg/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"><span style="color: black;">技巧<span style="color: black;">16</span>、防止重复录入</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">选择</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/4jUToJBtQvSbjRkDRdELODiakzVJic9sRyI20TsDUk0uctBZkKQ8xUmleQb1T0JPPYZvl7vqEpemDCJNibnDpfRrQ/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"><span style="color: black;">倘若</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/4jUToJBtQvSbjRkDRdELODiakzVJic9sRyfIejDnIXMiaACbz7bMZs4RFK7hibicXqvooewFExnQz7XnN1XNhdhka8Q/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></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;">17</span>、公式转数值</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">选择</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/4jUToJBtQvSbjRkDRdELODiakzVJic9sRyKOLlR7yERyKBFzoQeUO57XZiby92W8bw8u937icZesia2Zhx9KAuU5NxQ/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></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;">18</span>：<span style="color: black;">同期</span>修改多个工作表</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">按shift或ctrl键<span style="color: black;">选择</span>多个工作表，<span style="color: black;">而后</span>在一个表中输入内容或修改格式，所有选中的表都会同步输入或修改。<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;">19</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;">C:UsersAdministratorAppDataRoamingMicrosoftExcel ，</span>在文件夹内会找到的<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;">所在的文件夹，如下图所示。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="http://mmbiz.qpic.cn/mmbiz/4jUToJBtQvRnGgibx4LfHvibKT0e0iax6iafCD3ibCMsL65ca4FGfjAicopzYP19d991ciasb3yb5C3MKEYRtEejdglGA/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">打开文件夹，未<span style="color: black;">保留</span>的文件就找到了。打开后另存就OK！</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="http://mmbiz.qpic.cn/mmbiz/4jUToJBtQvRnGgibx4LfHvibKT0e0iax6iafdCl5eI2mTOF7oBdUWibywx4sfU9jzMupwjiaqFibbXRVicz9xCaJEAsduA/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">为何</span>我测试<span style="color: black;">无</span>恢复成功？你是怎么<span style="color: black;">晓得</span>恢复文件的路径的？</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">先看一个界面，看过你就明白了。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">文件 - excel选项 - <span style="color: black;">保留</span></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/4jUToJBtQvRnGgibx4LfHvibKT0e0iax6iaf14Bb6ce8db9e0Dpb0pbPSjp8CSD8xxhIeEiaq5SEz9uWMbtKU5lu1tg/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></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;">20</span>、给excel文件添加打开<span style="color: black;">秘码</span></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">excel文件 - 信息 - <span style="color: black;">守护</span>工作簿 - 用<span style="color: black;">秘码</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/4jUToJBtQvSbjRkDRdELODiakzVJic9sRy2GnSdOruswhcMvVMptQgzO5I4b6T2rPeIEibNnydRvhylBe7PIOkUyw/640?tp=webp&wxfrom=5&wx_lazy=1&wx_co=1" style="width: 50%; margin-bottom: 20px;"></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;">21</span>、快速关闭所有excel文件</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">按shift键不松，再点右上角关闭按钮，<span style="color: black;">能够</span>关键所有打开的excel文件。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;"></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;">22</span>、制作下拉菜单</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><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>的下拉菜单。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">分析</span>：在excel里制作下拉菜单有好几种<span style="color: black;">办法</span>，<span style="color: black;">咱们</span><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>：</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>1</span>：<span style="color: black;">选择</span><span style="color: black;">营销</span>员一列需要设置下拉菜单的单元格区域（<span style="color: black;">这一步<span style="color: black;">不可</span>少</span>），打开数据有效性窗口（excel2003版数据菜单 - 有效性，excel2007和2010版本 数据选项卡 - 数据有效性 - 数据有效性），在窗口中的“设置”选项卡里选“序列”。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;"></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>2</span>：在<span style="color: black;">源自</span>输入框里<span style="color: black;">咱们</span>需要设置下拉菜单里要<span style="color: black;">表示</span>的内容，有两种设置<span style="color: black;">办法</span>。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">1 <span style="color: black;">直接输入法</span>。在<span style="color: black;">源自</span>后的框里输入用“,”（英文逗号）连接的字符串：张一，吴汉青，刘能，将文胜，李大民</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">2 <span style="color: black;">引用单元格的内容法</span>。<span style="color: black;">倘若</span><span style="color: black;">营销</span>员在单元格B4:B8区域里，在“<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>就<span style="color: black;">能够</span>在<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;">23</span>、二级联动下拉</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><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>所有三星的型号。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">手机列选苹果：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">手机列<span style="color: black;">选择</span>三星</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">朋友</span>们应该明白什么是二级下拉联动菜单了，还等什么，<span style="color: black;">咱们</span><span style="color: black;">一块</span><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;">过程</span>：</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;">过程</span>1</span>：设置数据源区域。<span style="color: black;">便是</span>把手机名<span style="color: black;">叫作</span>和型号整理成如下图格式备用，存放的位置随意。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;"></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>2</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;">excel2003</span>版里，<span style="color: black;">插进</span>菜单 - 名<span style="color: black;">叫作</span> - <span style="color: black;">指定</span>，<span style="color: black;">07</span>和<span style="color: black;">10</span>版 公式选项卡 - 定义的名<span style="color: black;">叫作</span>组 -<span style="color: black;"><span style="color: black;">按照</span>所选内容创建</span>），<span style="color: black;">选择</span>窗口上的“<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;"><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;"></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>3</span>：设置数据有效性。<span style="color: black;">选择</span>型号列，打开数据有效性窗口（打开<span style="color: black;">办法</span>见昨天的教程），在<span style="color: black;">源自</span>中输入<span style="color: black;"><strong style="color: blue;">=indirect(D5)</strong></span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">进行如下设置后，二级联动菜单设置完成。</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;">24</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;">插进</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>。</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">下面的演示分为两部分：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">1 隔行<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;">2 <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;"><span style="color: black;">注：演示过程中打开定位窗口的组合键是</span>ctrl + g</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">关联</span>表格：</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><a style="color: black;">免费分享一套Excel进销存仓库管理表格</a></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><a style="color: black;">出入库管理表格，可<span style="color: black;">按照</span>物品名<span style="color: black;">叫作</span><span style="color: black;">查找</span>库存量</a></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><a style="color: black;">仓库盘点库存<span style="color: black;">再也不</span>愁，Excel数据</a>分析带你飞</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">你想要的表格，这都有！</span></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><img src="data:image/svg+xml,%3C%3Fxml version=1.0 encoding=UTF-8%3F%3E%3Csvg width=1px height=1px viewBox=0 0 1 1 version=1.1 xmlns=http://www.w3.org/2000/svg xmlns:xlink=http://www.w3.org/1999/xlink%3E%3Ctitle%3E%3C/title%3E%3Cg stroke=none stroke-width=1 fill=none fill-rule=evenodd fill-opacity=0%3E%3Cg transform=translate(-249.000000, -126.000000) fill=%23FFFFFF%3E%3Crect x=249 y=126 width=1 height=1%3E%3C/rect%3E%3C/g%3E%3C/g%3E%3C/svg%3E" style="width: 50%; margin-bottom: 20px;"></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">酒水每日库存盘点表格，盘点库存表格怎么做，厨房库存盘点表格，仓库库存盘点表模板</span></p>
页:
[1]