[已解决]筛选问题

sundonghai

=COUNTIF('9'!$Q$3:$Q$999,"<59.5")
=SUM(COUNTIF('9'!$Q$3:$Q$999,">"&{59.5,79.5})*{1,-1})
=SUM(COUNTIF('9'!$Q$3:$Q$999,">"&{79.5,89.5})*{1,-1})
=COUNTIF('9'!$Q$3:$Q$999,">=89.5")
上面函数对一组数据筛选时，发现一个数据被多筛选了一次，应该是89.5这个数，请大家帮忙看看我这个公式有无错误，谢谢！

最佳答案

月排行榜 / 总排行榜

qh8600

2014-6-10 10:21

sundonghai 发表于 2014-6-10 10:12
我想对一组数据进行筛选，小于59.5以下数的个数；59.5-79.4数的个数；79.5－89.4数的个数；89.5数以上的个 ...

=COUNTIF('9'!$Q$3:$Q$999,"<59.5")
=COUNTIF('9'!$Q$3:$Q$999,"<79.5") - COUNTIF('9'!$Q$3:$Q$999,"<59.5")
=COUNTIF('9'!$Q$3:$Q$999,"<89.5") - COUNTIF('9'!$Q$3:$Q$999,"<79.5")  
=COUNTIF('9'!$Q$3:$Q$999,">=89.5")

跳转到最佳答案楼层

qh8600

=COUNTIF('9'!$Q$3:$Q$999,">=89.5") 把等于去掉改成=COUNTIF('9'!$Q$3:$Q$999,">89.5")

sundonghai

qh8600 发表于 2014-6-10 09:35
=COUNTIF('9'!$Q$3:$Q$999,">=89.5") 把等于去掉改成=COUNTIF('9'!$Q$3:$Q$999,">89.5")

=COUNTIF('9'!$Q$3:$Q$999,">=89.5") 把等于去掉改成=COUNTIF('9'!$Q$3:$Q$999,">89.5")
能给解释一下吗错误的原因？

sundonghai

sundonghai 发表于 2014-6-10 10:03
=COUNTIF('9'!$Q$3:$Q$999,">=89.5") 把等于去掉改成=COUNTIF('9'!$Q$3:$Q$999,">89.5")

对89.5和大于89.5进行筛选比实际数少了一个

sundonghai

sundonghai 发表于 2014-6-10 10:07
改成=COUNTIF('9'!$Q$3:$Q$999,">89.5")是错误的，在原来的基础上又少了一个数（共少了两个数）

我想对一组数据进行筛选，小于59.5以下数的个数；59.5-79.4数的个数；79.5－89.4数的个数；89.5数以上的个数。
怎么能完成？
我用=COUNTIF('9'!$Q$3:$Q$999,"<59.5")
=SUM(COUNTIF('9'!$Q$3:$Q$999,">"&{59.5,79.5})*{1,-1})
=SUM(COUNTIF('9'!$Q$3:$Q$999,">"&{79.5,89.5})*{1,-1})
=COUNTIF('9'!$Q$3:$Q$999,">=89.5")进行筛选多了一个数。请帮忙看看这个公式错在哪？或是能给个正确的公式？

qh8600

sundonghai 发表于 2014-6-10 10:12
我想对一组数据进行筛选，小于59.5以下数的个数；59.5-79.4数的个数；79.5－89.4数的个数；89.5数以上的个 ...

=COUNTIF('9'!$Q$3:$Q$999,"<59.5")
=COUNTIF('9'!$Q$3:$Q$999,"<79.5") - COUNTIF('9'!$Q$3:$Q$999,"<59.5")
=COUNTIF('9'!$Q$3:$Q$999,"<89.5") - COUNTIF('9'!$Q$3:$Q$999,"<79.5")  
=COUNTIF('9'!$Q$3:$Q$999,">=89.5")