|
在工作中，如何设计一款适合自己工作统计的表格，既能让EXCEL帮助自己的工作，也能让统计工作变得简单，以下是我用工作统计表中，常见的几个公式以及设置方法。 1、条件格式设置 所有查询完成的结果中并非只有自己一家，那么如何区分这些呢？我们需要对单元格进行设置，并对属于自己的内容进行单元格设置。 设置规则：【菜单】——【开始】——【条件格式】——【只为包含以下内容的单元格设置格式】——【单元格值】——【等于】——【内容（自己需要的内容，比如我的是yt）】——设置格式。具体设置可以参考下图。
2、如何获取占比？ 占比的公式是：COUNTIF(C4:L4,"*yt*")/10。其中C4:L4是筛选的范围，yt加上星号是寻找在范围内的yt的数量。10是因为只查前十名，所以这里的数字是10，如果要查询前100名，那么这里的公式就是100，是需要因地制宜，根据需求进行改变的。 在设置占比时，还遇到了一个新的问题，如果这个内容暂时用不到，那么就没有一到十名了，所以需要添加前置条件，前置条件的判断使用IF函数来表示。 结合起来就是=IF(B4="","",COUNTIF(C4:L4,"*瀛台*")/10)，意思是如果这个单元格是空值，那么就是空值，否则就显示占比。 3、如何判断是否合格、良好、优秀？ 对于如何判断，是需要依靠自己建立的标准的，比如说，我这边设立的标准是小于30%就是不及格，等于30%是及格，大于等于40%是良好，大于等于50%是优秀。 根据上段话我们依次来制作表达公式，首先是， IF(M4<30%,"不及格","及格")，然后是加入40%，IF(M4>=40%,"良好",IF(M4<30%,"不及格","及格"))，之后是50%，F(M4>=50%,"优秀",IF(M4>=40%,"良好",IF(M4<30%,"不及格","及格")))。 完成之后，判断是否几个、良好、优秀的公式就制作完成了，但是还未完成，因为上一个表格中含有空值，所以这个表格也需要设置空值，即，如果单元格为空，那么值为空，否则就是上方的公式的值，用公式来表达就是=IF(B4="","",IF(M4>=50%,"优秀",IF(M4>=40%,"良好",IF(M4<30%,"不及格","及格"))))。 4、如何设置单独竖列的占比和排名 上面已经有了横向的对比，还缺少纵向的对比，与横向的占比不同，竖向的占比同样需要统计列表中有多少个包含关键词公式是一样的，唯一不一样的，就是获取序列的数量，我采用的是SUBTOTAL函数，设置的结果如下：=COUNTIF(C4:C43,"瀛台")/(SUBTOTAL(2,$A$4:$A$4746))。 排名的排序方式依旧是用公式来表达，公式为=RANK(C44,$C$44:$L$44)，意思为某个值在区域内的排名，这个公式排列出来的是按照大小排列的，与个人使用的不同，个人会将数据分为1-10，但是公式做出来的会显示几个1或者几个一样的数字。如下图：
5、如何设置第一、前三的公式 第一的公式是计算第一是否是需要的内容，如果是，那就是100%，如果不是那就是0%，公式为COUNTIF(C4,"*瀛台*")/1 前三的公式与第一相似，唯一不同的是最后要除以3，因为要计算的是前三的占比公式，公式为=IF(B4="","",COUNTIF(C4:E4,"*瀛台*")/3)。 对于合格、优秀、良好的设置在前面已经设置过了这里就不多说了，直接使用或者更改数据值就可以了。 6、整体分析表格的设置 整体占比的设置是求取前面占比的平均值，公式为=AVERAGE(M4:M43) 及格数的设置是求取合格列中的合格数，公式为=COUNTIF($N:$N,"及格") 及格占比的设置是求及格数占总数量的百分比，公式为=T6/SUBTOTAL(2,$A$4:$A$4746) 良好数、良好占比、优秀数、优秀占比、不及格数、不及格占比与及格数、及格占比的设置是一样的。
7、前三分析表格的设置 这里表格的设置与之前是一样的，原理一致，只是需要更改区域。
8、占比分析表格的设置 占比分析表格需要挑选出共有多少个不同的类型，如YT、YH等，后面的占比分析需要这些，占比的设置公式为=COUNTIF($C$4:$L$43,S29)/($A$43*10)。
到这里整个表格就结束了，除了有整体的统计表，后续还设计了很多分析类的小表格，辅助分析，是不是看起来很好用，相信大家也都可以做出来这样的表格，甚至做得更好，期待你们的作业。 我是君留香，一个致力于个人成长，社会教育的青年。关注我，与我一起成长！ |
|
|
