江苏龙网

  1. 首页 > 生活 > >

[爱看]这才是老板最爱看的Excel销售报表，让数据展示一目了然！( 二 )


2、计算一组数据中第K个最大/最小值
如果想知道一个数据系列中的第k个最大值，可以使用LARGE()函数得到，相反，若想知道其中的第k个最小值，则可以使用SMALL()函数来得到，语法格式如下：
LARGE函数的语法格式为：LARGE(array,k)。
SMALL函数的语法格式为：SMALL(array,k)。
LARGE函数和SMALL函数的语法格式和参数完全相同。其中array参数表示要处理的数值序列，k参数表示返回第几大/小的值。话不多说，上例子。
现需要根据每种商品的销售数量，找出本月销量排名前3和排名后3的商品名称，以及对应的销售数量。
[爱看]这才是老板最爱看的Excel销售报表，让数据展示一目了然！
文章图片
想找出排名前3和排名后3的数值，可以分别使用LARGE函数和SMALL函数来完成，当找出这些数值后，可以使用MATCH函数来找出其在序列中的位置，然后通过INDEX函数返回其对应商品的名称，输出所需结果。具体操作步骤如下：
① 选择F3:F5单元格区域，输入公式“=LARGE($B$3:$B$16,D3)”按“Ctrl+Enter”组合键完成输入，公式用于获取B3:B16单元格区域中前三个最大的值。
[爱看]这才是老板最爱看的Excel销售报表，让数据展示一目了然！
文章图片
② 选择E3:E5单元格区域，输入公式“=INDEX($A$3:$A$16,MATCH(F3,$B$3:$B$16,0))”按“Ctrl+Enter”组合键完成输入。通过MATCH函数返回F列中的销售数量在$B$3:$B$16区域中所在的位置，最后利用INDEX函数返回销售数量对应的商品名称。
[爱看]这才是老板最爱看的Excel销售报表，让数据展示一目了然！
文章图片
③ 选择E3:E5单元格区域，输入公式“=SMALL($B$3:$B$16,H3)”按“Ctrl+Enter”组合键完成输入，公式用于获取B3:B16单元格区域中前三个最小的值。
[爱看]这才是老板最爱看的Excel销售报表，让数据展示一目了然！
文章图片
④ 选择I3:I5单元格区域，输入公式“=INDEX($A$3:$A$16,MATCH(J3,$B$3:$B$16,0))”按“Ctrl+Enter”组合键完成输入。
[爱看]这才是老板最爱看的Excel销售报表，让数据展示一目了然！
文章图片
在上述例子中，由于商品类别少，销售数量没有出现重复的情况，因此可以得到正确的结果，但如果商品类别较多，销售数量出现重复值，此时再用上面的公式返回销售数量对应的商品名称，就只会返回第一个与销售数量对应的商品名称，如下图所示。
[爱看]这才是老板最爱看的Excel销售报表，让数据展示一目了然！
文章图片
要解决此问题，就必须让INDEX函数的第二参数返回每个重复值在数据区域中的实际位置，可以通过LARGE函数、IF函数、ROW函数、以及COUNTIF函数嵌套完成，返回商品名称的公式如下：“{=INDEX($A$3:$A$16,LARGE(IF($B$3:$B$16=F3,ROW($1:$14)),COUNTIF($F$3:F3,F3)))}”，由于是数组公式，需要按“Ctrl+Shift+Enter”组合键结束公式。
[爱看]这才是老板最爱看的Excel销售报表，让数据展示一目了然！
文章图片
公式解析：用IF函数判断$B$3:$B$16区域中的值是否等于F3，等于则返回数量对应的序号，不等于则返回FALSE。于是可以得到一组由逻辑值FALSE和序号组成的数组，作为LARGE函数的第一参数。接着使用COUNTIF函数判断F3单元格在$F$3:F3区域中出现的次数，将得到的次数，作为LARGE函数的第二参数，用于确定返回这个数组中第几大的值。最后将LARGE函数的结果，作为INDEX的第二参数，返回重复数量对应的不同商品名称。
请注意该公式中COUNTIF函数对于数据区域“$F$3:F3”的引用方法，这里我们限制了计数区域的范围，这个计数范围会随着公式的下拉不断扩大，需要统计的数量就会逐渐增多，这样一来，就可以统计出该重复值是第几次重复出现。）


推荐阅读


上一篇： 全市@长春市“五一”期间社会治安秩序良好

下一篇： 『黑眼圈』怎样用最少的钱养好问题肌肤？认真阅读，一学就会！