（视频）数学和三角函数：AGGREGATE函数
<b>视频地址1：</b>点击观看<br><br><b>视频地址2：</b>点击观看<br><br>
<b>教程难度：</b>初级<br><br>
<b>图文教程：</b><br>
<p style="margin-left: 0;text-indent: 0"><br></p><p style="margin-left: 0;text-indent: 0"><span style="font-family: 微软雅黑; letter-spacing: 0px; font-size: 16px;">返回列表或数据库中的合计。</span></p><p style="margin-left: 0;text-indent: 0"><span style="font-family: 微软雅黑; letter-spacing: 0px; font-size: 16px;">AGGREGATE 函数可将不同的聚合函数应用于列表或数据库，并提供忽略隐藏行和错误值的选项。</span></p><p style="margin-left: 0;text-indent: 0"><span style="font-family: 微软雅黑; letter-spacing: 0px; font-size: 16px;"><br></span></p><h2 style="margin-left: 0;text-indent: 0"><span style="font-size: 16px;"><strong><span style="letter-spacing: 0px; font-size: 16px; font-family: 微软雅黑;">语法</span></strong></span></h2><p style="margin-left: 0;text-indent: 0"><span style="font-family: 微软雅黑; letter-spacing: 0px; font-size: 16px;">引用形式</span></p><p style="margin-left: 0;text-indent: 0"><span style="font-family: 微软雅黑; letter-spacing: 0px; font-size: 16px;">AGGREGATE(function_num, options, ref1, , …)</span></p><p style="margin-left: 0;text-indent: 0"><span style="font-family: 微软雅黑; letter-spacing: 0px; font-size: 16px;"><br></span></p><p style="margin-left: 0;text-indent: 0"><span style="font-family: 微软雅黑; letter-spacing: 0px; font-size: 16px;">数组形式</span></p><p style="margin-left: 0;text-indent: 0"><span style="font-family: 微软雅黑; letter-spacing: 0px; font-size: 16px;">AGGREGATE(function_num, options, array, )</span></p><p style="margin-left: 0;text-indent: 0"><span style="font-family: 微软雅黑; letter-spacing: 0px; font-size: 16px;"> </span></p><p style="margin-left: 0;text-indent: 0"><span style="font-family: 微软雅黑; letter-spacing: 0px; font-size: 16px;">AGGREGATE 函数语法具有以下参数：</span></p><p><span style="font-family: 微软雅黑; letter-spacing: 0px; font-weight: bold; font-size: 13px;"> </span><strong style="font-size: 16px;"><span style="font-family: 微软雅黑; line-height: 150%; letter-spacing: 0px; font-size: 13px;">▪Function_num</span></strong><span style="font-family: 微软雅黑; line-height: 150%; letter-spacing: 0px; font-size: 13px;"> <span style="font-size: 16px;">必需</span></span><span style="font-size: 16px;">。 一个介于 1 到 19 之间的数字，指定要使用的函数。 </span></p><p><br></p><table cellspacing="2" width="243"><tbody><tr style="height:19px" class="firstRow"><td width="114" valign="center" style="padding: 1px; border-left: none; border-right-width: 1px; border-right-color: rgb(192, 192, 192); border-top: none; border-bottom: none; background: rgb(192, 192, 192);"><p style="line-height: 120%"><span style="font-size: 16px;"><strong><span style="font-family: 微软雅黑; line-height: 120%; font-size: 13px;">Function_num</span></strong></span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right-width: 1px;border-right-color: rgb(192, 192, 192);border-top: none;border-bottom: none;background: rgb(192, 192, 192)"><p style="line-height: 120%"><span style="font-size: 16px;"><strong><span style="font-family: 微软雅黑; line-height: 120%; font-size: 13px;">函数</span></strong></span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">1</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">AVERAGE</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">2</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">COUNT</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">3</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">COUNTA</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">4</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">MAX</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">5</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">MIN</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">6</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">PRODUCT</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">7</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">STDEV.S</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">8</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">STDEV.P</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">9</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">SUM</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">10</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">VAR.S</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">11</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">VAR.P</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">12</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">MEDIAN</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">13</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">MODE.SNGL</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">14</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">LARGE</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">15</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">SMALL</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">16</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">PERCENTILE.INC</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">17</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">QUARTILE.INC</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">18</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">PERCENTILE.EXC</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">19</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">QUARTILE.EXC</span></p></td></tr></tbody></table><p><br><strong style="font-size: 16px;"><span style="font-family: 微软雅黑; line-height: 150%; letter-spacing: 0px; font-size: 13px;">▪</span></strong><strong style="font-size: 16px;"><span style="font-family: 微软雅黑; line-height: 150%; letter-spacing: 0px; font-size: 13px;">Options</span></strong><span style="font-family: 微软雅黑; line-height: 150%; letter-spacing: 0px; font-size: 13px;"> <span style="font-size: 16px;">必需。</span> <span style="font-size: 16px;">一个数值，决定在函数的计算区域内要忽略哪些值。</span></span><span style="font-size: 16px; font-family: 微软雅黑; line-height: 150%; letter-spacing: 0px;"> </span></p><p><br></p><p><span style="font-size: 16px;"><span style="font-family: 微软雅黑; font-size: 13px;"> </span><span style="font-family: 思源黑体 CN Medium; font-size: 13px;"> <span style="font-size: 16px; font-family: 思源黑体 CN Medium;">注意</span><span style="font-size: 16px; font-family: Calibri;">:</span><span style="font-size: 16px; font-family: 华文仿宋;"> </span><span style="font-size: 16px; font-family: 思源黑体 CN Medium;">如果数组参数中包含计算（例如，</span><span style="font-family: Calibri; font-size: 16px;">=AGGREGATE(14,3,A1:A100*(A1:A100>0),1)</span><span style="font-size: 16px; font-family: 思源黑体 CN Medium;">），则函数不会忽略隐藏行、嵌套分类汇总或嵌套聚合。</span></span></span></p><p><br></p><table cellspacing="2" width="488"><tbody><tr style="height:19px" class="firstRow"><td width="114" valign="center" style="padding: 1px; border-left: none; border-right-width: 1px; border-right-color: rgb(192, 192, 192); border-top: none; border-bottom: none; background: rgb(192, 192, 192);"><p style="line-height: 120%"><span style="font-size: 16px;"><strong><span style="font-family: 微软雅黑; line-height: 120%; font-size: 13px;">选项</span></strong></span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right-width: 1px;border-right-color: rgb(192, 192, 192);border-top: none;border-bottom: none;background: rgb(192, 192, 192)"><p style="line-height: 120%"><span style="font-size: 16px;"><strong><span style="font-family: 微软雅黑; line-height: 120%; font-size: 13px;">行为</span></strong></span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">0 或省略</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">忽略嵌套 SUBTOTAL 和 AGGREGATE 函数</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">1</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">忽略隐藏行、嵌套 SUBTOTAL 和 AGGREGATE 函数</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">2</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">忽略错误值、嵌套 SUBTOTAL 和 AGGREGATE 函数</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">3</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">忽略隐藏行、错误值、嵌套 SUBTOTAL 和 AGGREGATE 函数</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">4</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">忽略空值</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">5</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">忽略隐藏行</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">6</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">忽略错误值</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">7</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">忽略隐藏行和错误值</span></p></td></tr></tbody></table><p><br><br><strong style="font-size: 16px;"><span style="font-family: 微软雅黑; line-height: 150%; letter-spacing: 0px; font-size: 13px;">▪Ref1</span></strong><span style="font-family: 微软雅黑; line-height: 150%; letter-spacing: 0px; font-size: 13px;"> <span style="font-size: 16px;">必需。函数的第一个数值参数，这些函数具有要计算聚合值的多个数值参数。</span> </span></p><p><br></p><p><span style="font-family: 微软雅黑; line-height: 150%; letter-spacing: 0px; font-size: 13px;"><br></span></p><p><strong style="font-size: 16px; text-indent: 27px;"><span style="font-family: 微软雅黑; line-height: 150%; letter-spacing: 0px; font-size: 13px;">▪</span></strong><strong style="font-size: 16px; text-indent: 27px;"><span style="font-family: 微软雅黑; line-height: 150%; letter-spacing: 0px; font-size: 13px;">Ref2,...</span></strong><span style="text-indent: 27px; font-family: 微软雅黑; line-height: 150%; letter-spacing: 0px; font-size: 13px;"> <span style="font-size: 16px;">可选。要计算</span></span><span style="font-size: 16px;">聚合值的 2 至 253 个数值参数。 </span></p><p><span style="font-family: 微软雅黑; font-size: 16px; letter-spacing: 0px;">对于使用数组的函数，ref1 可以是一个数组或数组公式，也可以是对要为其计算聚合值的单元格区域的引用。 ref2 是某些函数必需的第二个参数。 以下函数需要 ref2 参数： </span></p><p><span style="font-family: 微软雅黑; font-size: 16px; letter-spacing: 0px;"><br></span></p><table cellspacing="2"><tbody><tr style="height:19px" class="firstRow"><td width="114" valign="center" style="padding: 1px; border-left: none; border-right-width: 1px; border-right-color: rgb(192, 192, 192); border-top: none; border-bottom: none; background: rgb(192, 192, 192);"><p style="line-height: 120%"><span style="font-size: 16px;"><strong><span style="font-family: 微软雅黑; line-height: 120%; font-size: 13px;">函数</span></strong></span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">LARGE(array,k)</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">SMALL(array,k)</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">PERCENTILE.INC(array,k)</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">QUARTILE.INC(array,quart)</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">PERCENTILE.EXC(array,k)</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; font-size: 16px;">QUARTILE.EXC(array,quart)</span></p></td></tr></tbody></table><h2 style="margin-left: 0;text-indent: 0"><span style="font-size: 16px;"><strong><span style="letter-spacing: 0px; font-size: 16px; font-family: 微软雅黑;"><br></span></strong></span></h2><h2 style="margin-left: 0;text-indent: 0"><span style="font-size: 16px;"><strong><span style="letter-spacing: 0px; font-size: 16px; font-family: 微软雅黑;">备注</span></strong></span></h2><p style="margin-left: 0;text-indent: 0"><span style="font-size: 16px;"><strong><span style="font-family: 微软雅黑; letter-spacing: 0px;">Function_num</span></strong><span style="font-family: 微软雅黑; letter-spacing: 0px;"> </span><strong><span style="font-family: 微软雅黑; letter-spacing: 0px;">:</span></strong><span style="font-family: 微软雅黑; letter-spacing: 0px;"> </span></span></p><p style="margin-left: 0;text-indent: 0"><span style="font-size: 16px;"><span style="font-family: 微软雅黑; letter-spacing: 0px;"><br></span></span></p><p><span style="font-size: 16px;">▪在将 AGGREGATE 函数输入到工作表上的单元格中时，只要键入 function_num 参数，就会立即看到可以作为参数使用的所有函数的列表。</span></p><p><span style="font-size: 16px;"> </span></p><p><span style="font-size: 16px;">错误：</span></p><p><span style="font-size: 16px;">▪如果第二个引用参数是必需的但未提供，AGGREGATE 将返回 #VALUE! 错误。 </span></p><p><span style="font-size: 16px;">▪如果有一个或多个引用是三维引用，AGGREGATE 将返回 #VALUE! 错误值。 </span></p><p><span style="font-size: 16px;"><br></span></p><p><span style="font-size: 16px;">区域类型：</span></p><p><span style="font-size: 16px;">▪AGGREGATE 函数适用于数据列或垂直区域， 不适用于数据行或水平区域。 例如，当使用选项 1 对某个水平区域进行分类汇总时，如 AGGREGATE(1, 1, ref1)，则隐藏某一列并不会影响聚合总值。 但是，隐藏垂直区域中的某一行将对聚合总值产生影响。 </span></p><p><span style="font-size: 16px;"><br></span></p><h2><span style="font-size: 16px;">示例</span></h2><p><br></p><table cellspacing="2"><tbody><tr style="height:19px" class="firstRow"><td width="114" valign="center" style="padding: 1px; border-left: none; border-right-width: 1px; border-right-color: rgb(192, 192, 192); border-top: none; border-bottom: none; background: rgb(192, 192, 192);"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-size: 16px;"><strong><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 13px;">#DIV/0! </span></strong></span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right-width: 1px;border-right-color: rgb(192, 192, 192);border-top: none;border-bottom: none;background: rgb(192, 192, 192)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-size: 16px;"><strong><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 13px;">82</span></strong></span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">72</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">65</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">30</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">95</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">#NUM!</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">63</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">31</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">53</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">96</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">71</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">32</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">55</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">81</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">83</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">33</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">100</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">53</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">91</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">34</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">89</span></p></td></tr></tbody></table><p><span style="font-family: 微软雅黑; font-size: 16px;"> </span></p><table cellspacing="2"><tbody><tr style="height:19px" class="firstRow"><td width="114" valign="center" style="padding: 1px; border-left: none; border-right-width: 1px; border-right-color: rgb(192, 192, 192); border-top: none; border-bottom: none; background: rgb(192, 192, 192);"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-size: 16px;"><strong><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 13px;">公式 </span></strong></span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right-width: 1px;border-right-color: rgb(192, 192, 192);border-top: none;border-bottom: none;background: rgb(192, 192, 192)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-size: 16px;"><strong><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 13px;">说明</span></strong></span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right-width: 1px;border-right-color: rgb(255, 255, 255);border-top: none;border-bottom: none;background: rgb(192, 192, 192)"><p style="margin-left: 0;text-indent: 0"><span style="font-size: 16px;"><strong><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 13px;">结果</span></strong></span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">=AGGREGATE(4, 6, A1:A11)</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">计算最大值，同时忽略区域中的错误值</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">96</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">=AGGREGATE(14, 6, A1:A11, 3)</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">计算第 3 个最大值，同时忽略区域中的错误值</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">72</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">=AGGREGATE(15, 6, A1:A11)</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">将返回 错误 #NUM!。 因为函数 (SMALL) 要求具有一个第二引用参数，因而 AGGREGATE 需要第二引用参数。</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">#VALUE!</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">=AGGREGATE(12, 6, A1:A11, B1:B11)</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">计算中值，同时忽略区域中的错误值</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">68</span></p></td></tr><tr style="height:19px"><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">=MAX(A1:A2)</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">将返回错误值，因为计算区域中存在错误值。</span></p></td><td valign="center" style="padding: 1px;border-left: none;border-right: none;border-top: none;border-bottom-width: 1px;border-bottom-color: rgb(192, 192, 192);background: rgb(255, 255, 255)"><p style="margin-left: 0;text-indent: 0;line-height: 120%"><span style="font-family: 微软雅黑; line-height: 120%; letter-spacing: 0px; font-size: 16px;">#DIV/0!</span></p></td></tr></tbody></table><p><span style="font-family: 微软雅黑; font-size: 16px;"> </span></p>
頁:
[1]