教你怎么样零基本入门Excel表格编辑，快速把握Excel
<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><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;">Excel表格不止能用在工作中，在业余<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>说你想更加深入的<span style="color: black;">认识</span>Excel其实你都<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>的Excel知识，为你带来<span style="color: black;">各样</span>便利。<span style="color: black;">那样</span>让我<span style="color: black;">循序渐进</span>来教<span style="color: black;">大众</span>：</p>
<h1 style="color: black; text-align: left; margin-bottom: 10px;"><span style="color: black;">第1</span>步：<span style="color: black;">熟练</span>架构</h1>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;">首要</span>我拿的是2016版本的OFFICE Excel 来做例子，你一打开Excel最好要先把文件、<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>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/0c63b013927d42d687fb1b840363f230~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=brUyvOnnBM8V0d5rHSt98zGFrOk%3D" style="width: 50%; margin-bottom: 20px;"></div>
<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>只要在正常界面按下Alt键就<span style="color: black;">能够</span>弹出工具栏的快捷键，他会以灰色的字体标注出来，后面<span style="color: black;">咱们</span><span style="color: black;">能够</span>按住Alt+任意灰色快捷键，<span style="color: black;">转</span>到需要的项目上面。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/51672f0f1a71463196af7563a107bf25~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=PB7NXJs7VLeYBF5%2B6uSweKdLulQ%3D" style="width: 50%; margin-bottom: 20px;"></div>
<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>就可直接<span style="color: black;">经过</span>Ctrl+快捷键直接<span style="color: black;">运用</span>这个工具。为<span style="color: black;">咱们</span>的制表效率<span style="color: black;">供给</span>有效的支持。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p26-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/1241417b9b7e4ee8b6fc64fa494e3df2~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=40j8paWrK5O0jWvH105X8zTGgng%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">第二步：快捷键的<span style="color: black;">运用</span></h1>
<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>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/74c29b3f788a4f3285acaa29a8f11055~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=36oX0hP%2FYXb4%2FCKN587qAAiRLOc%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">其中ctrl+e是快速填充快捷键，这个键很好用，自Office2013版本后才有了Ctrl+E这个功能组合键，让<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;"><strong style="color: blue;">1、批量合并/提取内容，添加符号</strong></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>值，按下Ctrl+E就能<span style="color: black;">容易</span>完成。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/f861282394084fe29a60e50d40a30271~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=fG7cD8pkmrmcF29cuOqYfM0zpeE%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">2、批量替换字符</strong></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>Ctrl+H来做数据替换，<span style="color: black;">或</span>用REPLACE和SUBSTITUTE函数来完成。但<span style="color: black;">此刻</span><span style="color: black;">运用</span><span style="color: black;">有效</span>的Ctrl+E只需在首个单元格输入<span style="color: black;">目的</span>值，按下Ctrl+E，文字与符号都能一键替换哦。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/0a72c44acff247d9a89a5b50fe72b8b7~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=0AEF%2BZ%2FLF%2BoBABWl%2BDigvUSjRcY%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">第三步：函数的应用</h1>
<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> sum , sumif , mid , left , right，vlookup 这些函数学会后就能整理数据源了，<span style="color: black;">而后</span>再学习</span>数据透视表<span style="color: black;">，能做出一张简单的表格，再画个</span>曲线图<span style="color: black;">什么的，这能达到达初级水平。</span></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/73745999f543460a88d02b51b86ebd20~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=xyibDfg32q6XU0JZ%2B%2BQI%2F6Z%2FbVM%3D" style="width: 50%; margin-bottom: 20px;"></div>
<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></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">1、取绝对值</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">=ABS(数字)</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">2、取整</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">=INT(数字)</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">3、四舍五入</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">=ROUND(数字,小数位数)</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;">2、</span>判断公式</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">1、把公式产生的错误值<span style="color: black;">表示</span>为空</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">公式：C2</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">=IFERROR(A2/B2,"")</strong></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>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/4edca6605ea54735b3b5477bbb38b20e~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=L%2F%2BIwo2diPnoQsNPnINaMLyxYTE%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">2、IF多<span style="color: black;">要求</span>判断返回值</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">公式：C2</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">=IF(AND(A2<500,B2="未到期"),"补款","")</strong></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>成立用AND,任一个成立用OR函数。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/9a03dd7b66594dbf95901ff81b927f95~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=YSZNwk38Nlqi%2FAt46av2NYKf8eI%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">3、</span>统计公式</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">1、统计两个表格重复的内容</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">公式:B2</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">=COUNTIF(Sheet15!A:A,A2)</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">说明：<span style="color: black;">倘若</span>返回值大于0说明在另一个表中存在，0则不存在。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/cad4a2e3d8204d57b018dcca3ad36d33~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=qAAdbljLlopdfXK5vxoAbIKIFX4%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">2、统计不重复的总人数</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">公式：C2</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">=SUMPRODUCT(1/COUNTIF(A2:A8,A2:A8))</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">说明:用COUNTIF统计出每人的<span style="color: black;">显现</span>次数，用1除的方式把<span style="color: black;">显现</span>次数变<span style="color: black;">成份</span>母，<span style="color: black;">而后</span>相加。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/f4d6f2f7b3194a03a601e62cf9dbff2e~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=gz7UvQ7jrVa4whh62Fg6a%2B8qj4k%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">4、</span>求和公式</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">1、隔列求和</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">公式：H3</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">=SUMIF($A$2:$G$2,H$2,A3:G3)</strong></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;"><strong style="color: blue;">=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)</strong></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>规则用第2个公式</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/f479948def6b4b3298c6765dca33d790~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=dVhF0gQxbtY6dyts9cDceCqgph4%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">2、单<span style="color: black;">要求</span>求和</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">公式：F2</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">=SUMIF(A:A,E2,C:C)</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">说明：SUMIF函数的基本用法</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/abc57f16b91544eea5d6cd4f3bd0f7bb~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=TOq5td%2B2jdwNs0a72xFR19lhGtc%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">3、单<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;">倘若</span>需要进行模糊求和，就需要<span style="color: black;">把握</span>通配符的<span style="color: black;">运用</span>，其中星号是<span style="color: black;">暗示</span>任意多个字符，如"*A*"就<span style="color: black;">暗示</span>a前和后有任意多个字符，即<span style="color: black;">包括</span>A。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/f83f783d0ca34d55b8662832b1b5fc9b~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=0HZwWcd5VMWAYmFJTxcHLoLv2hU%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">4、多<span style="color: black;">要求</span>模糊求和</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">公式：C11</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">=SUMIFS(C2:C7,A2:A7,A11&"*",B2:B7,B11)</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">说明：在sumifs中<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;">5、多表相同位置求和</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">公式：b2</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">=SUM(Sheet1:Sheet19!B2)</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">说明：在表中间删除或添加表后，公式结果会自动更新。</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/0d6727181b7d414fa29cd37ee8e9295d~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=DGFpk2QMDqb69%2BSVcLDuHpK%2BXNM%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">6、按日期和<span style="color: black;">制品</span>求和</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">公式：F2</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">=SUMPRODUCT((MONTH($A$2:$A$25)=F$1)*($B$2:$B$25=$E2)*$C$2:$C$25)</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">说明：SUMPRODUCT<span style="color: black;">能够</span>完成多<span style="color: black;">要求</span>求和</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/258cd328b249411690bf7542ec0079b0~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=a7w9cRvd2AU5ogvgvzFlHs9klnc%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;"><span style="color: black;">5、</span><span style="color: black;">查询</span>与引用公式</strong></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;">公式1：C11</p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><strong style="color: blue;">=VLOOKUP(B11,B3:F7,4,FALSE)</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">说明：<span style="color: black;">查询</span>是VLOOKUP最<span style="color: black;">善于</span>的，基本用法</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/81c8e58f18b14ae998c15b3eb67a9533~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=FFCCvqyWgNO6%2BLDjeo3eUVKaWB4%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">2、双向<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;"><strong style="color: blue;">=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))</strong></p>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">说明：利用MATCH函数<span style="color: black;">查询</span>位置，用INDEX函数取值</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/61d3ba7f9df046c5be36698e60cf4188~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=61SMs7PwyX6P6KvTisLa6JDya0k%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;">3、<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;">说明：0/(<span style="color: black;">要求</span>)<span style="color: black;">能够</span>把不符合<span style="color: black;">要求</span>的变成错误值，而lookup<span style="color: black;">能够</span>忽略错误值</p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/d4767de277ab418cb0bbd3abcdb73d93~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=mkjke%2FueiKjk9P1T0pyJmd08ChM%3D" style="width: 50%; margin-bottom: 20px;"></div>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">第四步：学会<span style="color: black;">运用</span>数据透视表</h1>
<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></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></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></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 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;">2、不<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;">3、<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;">4、<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;">5、不<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><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;"><strong style="color: blue;"><span style="color: black;">2.创建数据透视表</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></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/a220ee0c0dfb43f98482f340b3bce526~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=rUAX9hjSra3HMPvNfTt2Doy1tJE%3D" style="width: 50%; margin-bottom: 20px;"></div>
<p style="font-size: 16px; color: black; line-height: 40px; text-align: left; margin-bottom: 15px;"><span style="color: black;"><span style="color: black;">第1</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>】</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></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>列、行、值字段分别拖拽到下方对应区域</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;">3.<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></p>
<div style="color: black; text-align: left; margin-bottom: 10px;"><img src="https://p3-sign.toutiaoimg.com/tos-cn-i-qvj2lq49k0/f5e11f78065e45babba5763fabddb1db~noop.image?_iz=58558&from=article.pc_detail&lk3s=953192f4&x-expires=1727967456&x-signature=JS81JhqtRO5o7s7impJ6l2Xkb2s%3D" style="width: 50%; margin-bottom: 20px;"></div>
<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>，在数据透视表内汇总的先后层次<span style="color: black;">亦</span>会<span style="color: black;">区别</span>。</p>
<h1 style="color: black; text-align: left; margin-bottom: 10px;">第五步：<span style="color: black;">办法</span>论</h1>
<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>在学习 Excel 的时候应该怎么去学习呢？</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></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;">3、系统学习，<span style="color: black;">非常多</span>看小E公众号的过程中<span style="color: black;">把握</span>了<span style="color: black;">非常多</span>小技巧，但遇到问题还是懵，问题就出在这了。</span></p>
期待楼主的下一次分享！” 感谢楼主的分享！我学到了很多。
页:
[1]