3889370_g.zip
资源内容介绍
3889370_g.zip <link href="/image.php?url=https://csdnimg.cn/release/download_crawler_static/css/base.min.css" rel="stylesheet"/><link href="/image.php?url=https://csdnimg.cn/release/download_crawler_static/css/fancy.min.css" rel="stylesheet"/><link href="/image.php?url=https://csdnimg.cn/release/download_crawler_static/89960880/2/raw.css" rel="stylesheet"/><div id="sidebar" style="display: none"><div id="outline"></div></div><div class="pf w0 h0" data-page-no="1" id="pf1"><div class="pc pc1 w0 h0"><img alt="" class="bi x0 y0 w1 h1" src="/image.php?url=https://csdnimg.cn/release/download_crawler_static/89960880/bg1.jpg"/><div class="t m0 x1 h2 y1 ff1 fs0 fc0 sc0 ls0 ws0">在<span class="_ _0"> </span><span class="ff2">exce<span class="_ _1"></span>l200<span class="_ _1"></span>3<span class="_ _0"> </span></span>中按单元<span class="_ _1"></span>格背景颜<span class="_ _1"></span>色求和</div><div class="t m0 x1 h3 y2 ff3 fs1 fc0 sc1 ls0 ws0">2008-11-22 15:13</div><div class="c x2 y3 w2 h4"><div class="t m0 x3 h3 y4 ff4 fs1 fc0 sc1 ls0 ws0">今天应一网友要求<span class="_ _2"></span>：<span class="_ _2"></span>按颜色求和，<span class="_ _3"></span>在网上一查还真找到了这一方法，<span class="_ _3"></span>经过测试<span class="ff3">(</span>在</div><div class="t m0 x3 h3 y5 ff3 fs1 fc0 sc1 ls0 ws0">Excel2003<span class="_ _4"> </span><span class="ff4">和<span class="_ _4"> </span></span>2007<span class="_ _4"> </span><span class="ff4">中</span>)<span class="ff4">，非常好用，整理出来，与大家分享：</span></div><div class="t m0 x3 h3 y6 ff3 fs1 fc0 sc1 ls0 ws0">Excel <span class="ff4">本身没有这个功能，可用以下办法实现：</span></div><div class="t m0 x3 h3 y7 ff4 fs1 fc0 sc1 ls0 ws0">打开你的工作表<span class="_ _2"></span>：<span class="_ _2"></span>点工具<span class="ff3">--</span>宏<span class="ff3">--Visual Basic </span>编辑器，<span class="_ _5"></span>在左边最上一行点右键<span class="ff3">--</span></div><div class="t m0 x3 h3 y8 ff4 fs1 fc0 sc1 ls0 ws0">插入<span class="ff3">--</span>模块，然后在打开的编辑框中粘贴以下代码：</div><div class="t m0 x3 h3 y9 ff3 fs1 fc0 sc1 ls0 ws0">Function CountColor(col As Range, countrange As Range) As Integer</div><div class="t m0 x3 h3 ya ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> Dim icell As Range</div><div class="t m0 x3 h3 yb ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> Application.Volatile</div><div class="t m0 x3 h3 yc ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> For Each icell In countrange</div><div class="t m0 x3 h3 yd ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> If icell.Interior.ColorIndex = col.Interior.ColorIndex </div><div class="t m0 x3 h3 ye ff3 fs1 fc0 sc1 ls0 ws0">Then</div><div class="t m0 x3 h3 yf ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> CountColor = CountColor + 1</div><div class="t m0 x3 h3 y10 ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> End If</div><div class="t m0 x3 h3 y11 ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> Next icell</div><div class="t m0 x3 h3 y12 ff3 fs1 fc0 sc1 ls0 ws0">End Function</div><div class="t m0 x3 h3 y13 ff3 fs1 fc0 sc1 ls0 ws0">Function SumColor(col As Range, sumrange As Range) As Integer</div><div class="t m0 x3 h3 y14 ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> Dim icell As Range</div><div class="t m0 x3 h3 y15 ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> Application.Volatile</div><div class="t m0 x3 h3 y16 ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> For Each icell In sumrange</div><div class="t m0 x3 h3 y17 ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> If icell.Interior.ColorIndex = col.Interior.ColorIndex </div><div class="t m0 x3 h3 y18 ff3 fs1 fc0 sc1 ls0 ws0">Then</div><div class="t m0 x3 h3 y19 ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> SumColor = Application.Sum(icell) + SumColor</div><div class="t m0 x3 h3 y1a ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> End If</div><div class="t m0 x3 h3 y1b ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> Next icell</div><div class="t m0 x3 h3 y1c ff3 fs1 fc0 sc1 ls0 ws0">End Function</div><div class="t m0 x3 h3 y1d ff4 fs1 fc0 sc1 ls0 ws0">然后关闭，反回到工作表，点击<span class="ff3">--</span>工具<span class="ff3">-</span>选项<span class="ff3">--</span>安全性<span class="ff3">--</span>宏安全性<span class="ff3">--</span>安全级选<span class="ff3">--</span></div><div class="t m0 x3 h3 y1e ff4 fs1 fc0 sc1 ls0 ws0">低<span class="ff3">--</span>确定</div><div class="t m0 x3 h3 y1f ff4 fs1 fc0 sc1 ls0 ws0">保存关闭工作表，然后再打开，这时就可以用了</div><div class="t m0 x3 h3 y20 ff4 fs1 fc0 sc1 ls0 ws0">用法：</div><div class="t m0 x3 h3 y21 ff4 fs1 fc0 sc1 ls0 ws0">按颜色求和<span class="_ _3"></span>：<span class="_ _3"></span><span class="ff3">sumcolor<span class="ff4">（颜色示列格，求和区域或列）<span class="_ _3"></span>；<span class="_ _6"></span>按颜色计数<span class="_ _3"></span>：<span class="_ _3"></span><span class="ff3">countcolor</span></span></span></div><div class="t m0 x3 h3 y22 ff4 fs1 fc0 sc1 ls0 ws0">（颜色示列格，求和区域或列）</div><div class="t m0 x3 h3 y23 ff4 fs1 fc0 sc1 ls0 ws0">例如：要求和从<span class="_ _4"> </span><span class="ff3">a1<span class="_ _4"> </span></span>到<span class="_ _4"> </span><span class="ff3">a10<span class="_ _4"> </span></span>这个区域内的红色格<span class="ff3">,</span></div><div class="t m0 x3 h3 y24 ff3 fs1 fc0 sc1 ls0 ws0">=sumcolor($a$1,$a$1:$A$10)</div><div class="t m0 x3 h3 y25 ff4 fs1 fc0 sc1 ls0 ws0">计数：<span class="ff3">countcolor($a$1,$a$1:$A$10)</span></div><div class="t m0 x3 h3 y26 ff4 fs1 fc0 sc1 ls0 ws0">注意<span class="_ _3"></span>：<span class="_ _3"></span><span class="ff3">$a$1 <span class="_ _2"></span><span class="ff4">必须是红色格，这是定义颜色的，你也可以设成其它格，但必须是你</span></span></div><div class="t m0 x3 h3 y27 ff4 fs1 fc0 sc1 ls0 ws0">要求和的颜色</div><div class="t m0 x3 h3 y28 ff3 fs1 fc0 sc1 ls0 ws0">ok<span class="ff4">！有问题可以留言。</span></div></div></div><div class="pi" data-data='{"ctm":[1.611830,0.000000,0.000000,1.611830,0.000000,0.000000]}'></div></div><div id="pf2" class="pf w0 h0" data-page-no="2"><div class="pc pc2 w0 h0"><img class="bi x0 y0 w1 h1" alt="" src="/image.php?url=https://csdnimg.cn/release/download_crawler_static/89960880/bg2.jpg"><div class="c x2 y29 w2 h5"><div class="t m0 x3 h2 y2a ff1 fs0 fc0 sc0 ls0 ws0">在<span class="_ _0"> </span><span class="ff2">exce<span class="_ _1"></span>l200<span class="_ _1"></span>7<span class="_ _0"> </span></span>中按单元<span class="_ _1"></span>格背景颜<span class="_ _1"></span>色求和</div></div><div class="t m0 x4 h3 y2b ff4 fs1 fc0 sc1 ls0 ws0">一、在<span class="ff3">“</span>开发工具<span class="ff3">”</span>选项卡的<span class="ff3">“</span>代码<span class="ff3">”</span>组中，单击<span class="ff3">“</span>宏安全性<span class="ff3">”</span>。<span class="ff3"> </span></div><div class="t m0 x5 h3 y2c ff2 fs1 fc0 sc0 ls0 ws0"> <span class="_"> </span><span class="ff1">提示</span> <span class="_"> </span> <span class="_"> </span> <span class="_ _7"> </span><span class="ff1">如果未显示</span>“<span class="_ _1"></span><span class="ff1">开发工具</span>”<span class="ff1">选<span class="_ _1"></span>项卡，请单击<span class="_ _1"></span></span>“Offic<span class="_ _1"></span>e <span class="ff1">按</span></div><div class="t m0 x5 h3 y2d ff1 fs1 fc0 sc0 ls0 ws0">钮<span class="ff2">”<span class="_ _8"> </span> </span>，<span class="_ _9"></span>单击<span class="_ _9"></span><span class="ff2">“Exce<span class="_ _1"></span>l <span class="ff1">选项</span>”<span class="_ _2"></span><span class="ff1">，<span class="_ _9"></span>然后<span class="_ _1"></span>在<span class="_ _9"></span><span class="ff2">“<span class="ff1">常用</span>”<span class="_ _9"></span><span class="ff1">类别中<span class="_ _1"></span>的<span class="_ _9"></span><span class="ff2">“<span class="ff1">使用</span> Exc<span class="_ _1"></span>el </span></span></span></span></span></div><div class="t m0 x5 h3 y2e ff1 fs1 fc0 sc0 ls0 ws0">时采用的首选<span class="_ _1"></span>项<span class="ff2">”</span>下单击<span class="ff2">“<span class="_ _1"></span></span>在功能区显示<span class="_ _1"></span><span class="ff2">‘</span>开发工具<span class="ff2">’<span class="_ _1"></span></span>选项卡<span class="ff2">”</span>。</div><div class="t m0 x6 h3 y2f ff4 fs1 fc0 sc1 ls0 ws0">二、在<span class="ff3">“</span>宏设置<span class="ff3">”</span>类别中的<span class="ff3">“</span>宏设置<span class="ff3">”</span>下，单击所需的选项。<span class="ff3"> </span></div><div class="t m0 x1 h3 y30 ff4 fs1 fc0 sc1 ls0 ws0">打开要进行分背景颜色计算的工作簿，<span class="_ _a"></span>点击开发工具<span class="ff3">---VISUALBASIC—</span>插入<span class="ff3">—</span>模</div><div class="t m0 x1 h3 y31 ff4 fs1 fc0 sc1 ls0 ws0">块<span class="ff3">—</span>复制以下内容</div><div class="t m0 x1 h3 y32 ff3 fs1 fc0 sc1 ls0 ws0">Function SumColor(col As Range, sumrange As Range) As Integer</div><div class="t m0 x1 h3 y33 ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> Dim icell As Range</div><div class="t m0 x1 h3 y34 ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> Application.Volatile</div><div class="t m0 x1 h3 y35 ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> For Each icell In sumrange</div><div class="t m0 x1 h3 y36 ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> If icell.Interior.ColorIndex = col.Interior.ColorIndex </div><div class="t m0 x1 h3 y37 ff3 fs1 fc0 sc1 ls0 ws0">Then</div><div class="t m0 x1 h3 y38 ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> SumColor = Application.Sum(icell) + SumColor</div><div class="t m0 x1 h3 y39 ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> End If</div><div class="t m0 x1 h3 y3a ff3 fs1 fc0 sc1 ls0 ws0"> <span class="_"> </span> <span class="_"> </span> <span class="_"> </span> Next icell</div><div class="t m0 x1 h3 y3b ff3 fs1 fc0 sc1 ls0 ws0">End Function</div><div class="t m0 x7 h3 y3c ff4 fs1 fc0 sc1 ls0 ws0">到打开的模块范围内，即会自动产生<span class="_ _4"> </span><span class="ff3">SUMCOLOR</span>（）函数；然后点击该<span class="_ _4"> </span><span class="ff3">VB<span class="_ _4"> </span></span>的窗</div><div class="t m0 x7 h3 y3d ff4 fs1 fc0 sc1 ls0 ws0">口保存图标<span class="ff3">---</span>会显示<span class="_ _b"></span>“另存为<span class="ff3">”<span class="_ _b"></span><span class="ff4">窗，<span class="_ _b"></span>在保存类型选项选择<span class="_ _b"></span><span class="ff3">“excel<span class="_ _4"> </span><span class="ff4">启用宏的工</span></span></span></span></div><div class="t m0 x7 h3 y3e ff4 fs1 fc0 sc1 ls0 ws0">作簿<span class="ff3">”<span class="_ _2"></span><span class="ff4">，<span class="_ _3"></span>保存即可<span class="ff3">---</span>在该工作簿中建立数据表格，<span class="_ _c"></span>在点击要放汇总的单元格<span class="ff3">—</span></span></span></div><div class="t m0 x7 h3 y3f ff4 fs1 fc0 sc1 ls0 ws0">公式<span class="ff3">—f</span>（<span class="ff3">x</span>）<span class="_ _d"></span>函数<span class="ff3">—</span>选择类别<span class="ff3">--</span>全部<span class="ff3">—</span>即会在下拉中看到<span class="_ _4"> </span><span class="ff3">sumcolor</span>（）<span class="_ _d"></span>函数<span class="ff3">---</span></div><div class="t m0 x7 h3 y40 ff4 fs1 fc0 sc1 ls0 ws0">格式为<span class="_ _4"> </span><span class="ff3">sumcolor</span>（操作颜色示例单元格例如<span class="_ _4"> </span><span class="ff3">A12</span>，求和范围例<span class="_ _4"> </span><span class="ff3">B13</span>：<span class="ff3">B78</span>）<span class="ff3">---</span></div><div class="t m0 x7 h3 y41 ff4 fs1 fc0 sc1 ls0 ws0">按出现的顺序操作即可。</div><div class="t m0 x7 h3 y42 ff4 fs1 fc0 sc1 ls0 ws0">操作颜色示例单元格最好在图表旁边另建（颜色模板）<span class="_ _6"></span>，防止因数据表中颜色</div><div class="t m0 x7 h3 y43 ff4 fs1 fc0 sc1 ls0 ws0">调整而影响到计算结果；此处颜色不是字体颜色，而是背景颜色。</div><div class="t m0 x7 h3 y44 ff4 fs1 fc0 sc1 ls0 ws0">建完后要进行保存，最好也保存为“<span class="ff3">excel<span class="_ _4"> </span></span>启用宏的工作簿”。</div><div class="t m0 x1 h3 y45 ff4 fs1 fc0 sc1 ls0 ws0">另外应将宏安全级别降低或改为提醒<span class="_ _3"></span>：<span class="_ _3"></span>开发工具<span class="ff3">—</span>宏安全<span class="ff3">--</span>选择禁用所有宏，并</div><div class="t m0 x1 h3 y46 ff4 fs1 fc0 sc1 ls0 ws0">发出通知或者启用所有宏（不推荐）</div></div><div class="pi" data-data='{"ctm":[1.611830,0.000000,0.000000,1.611830,0.000000,0.000000]}'></div></div>