多条件查找公式

ex1753

准提部林 发表于 2017-10-14 09:31
稍改一下！！
（１）序号／Ｅ２：下拉到底
=(N(E1)+1)^(D2=D1)

谢谢老师，

昨天晚上弄了一晚上，我也是给H列的成绩放大10万倍，再加一个行号，这样就可以解决重名了。我用的公式是：=LOOKUP(1,0/(($A$2:$A$3000=$L2)*($B$2:$B$3000=$M2)*($C$2:$C$3000=$N2)*($J$2:$J$3000=INT(COLUMN(G1)/6))),$E$2:$E$3000)
公式全部填充后，家里的i5还是有一点点迟钝。估计单位电脑会卡顿几秒。
你这个公式好，没有明显卡顿。

ex1753

准提部林 发表于 2017-10-14 09:31
稍改一下！！
（１）序号／Ｅ２：下拉到底
=(N(E1)+1)^(D2=D1)

麻烦老师帮我在Sheet2的绿色区域在写一个公式
d3：如果701班某个项目在Sheet1有名次，则统计所有701的总分……

准提部林

ex1753 发表于 2017-10-14 13:44
麻烦老师帮我在Sheet2的绿色区域在写一个公式
d3：如果701班某个项目在Sheet1有名次，则统计所有701的总 ...

Sheet2．Ｄ４：
=SUMIF(OFFSET(Sheet1!$F$1,MATCH($B4&$C4&$A4,Sheet1!$D:$D,)-1,,30),D$3,OFFSET(Sheet1!$H$1,MATCH($B4&$C4&$A4,Sheet1!$D:$D,)-1,,30))
　
紅色３０，表示各項比賽人數不超過３０人，可自行調整，　

注意：Sheet２．Ａ列的文字須與Sheet1相同：
Xl0000026-2.rar (140.34 KB, 下载次数: 6)

2017-10-14 14:49 上传

点击文件名下载附件

　
　

ex1753

准提部林 发表于 2017-10-14 14:52
Sheet2．Ｄ４：
=SUMIF(OFFSET(Sheet1!$F$1,MATCH($B4&$C4&$A4,Sheet1!$D:$D,)-1,,30),D$3,OFFSET(Sheet ...

抱歉，可能是我表述不清楚，我稍微调整了下表格
Sheet2的D4是查找Sheet1里第二行的所有名次里面有没有701班，有的话把“计分”项加起来，不是把“成绩”加起来
Sheet2的E4是查找Sheet1里第二行的所有名次里面有没有702班，有的话把“计分”项加起来，不是把“成绩”加起来

D5则统计第三行的计分项
名次不一定取前5，有些前取前8，如果第八名重名的话，就会超过8人。

准提部林

ex1753 发表于 2017-10-14 16:58
抱歉，可能是我表述不清楚，我稍微调整了下表格
Sheet2的D4是查找Sheet1里第二行的所有名次里面有没有70 ...

=SUMPRODUCT(SUMIF(OFFSET(Sheet1!$O$1,MATCH($B4&$C4&$A4,Sheet1!$O:$O,)-1,COLUMN($A:$J)*6-4),D$3,OFFSET(Sheet1!$O$1,MATCH($B4&$C4&$A4,Sheet1!$O:$O,)-1,COLUMN($A:$J)*6)))

ex1753

准提部林 发表于 2017-10-14 17:31
=SUMPRODUCT(SUMIF(OFFSET(Sheet1!$O$1,MATCH($B4&$C4&$A4,Sheet1!$O:$O,)-1,COLUMN($A:$J)*6-4),D$3,O ...

太感谢了，一会慢慢看看。慢慢消化一下

ex1753

准提部林 发表于 2017-10-14 09:31
稍改一下！！
（１）序号／Ｅ２：下拉到底
=(N(E1)+1)^(D2=D1)

构思太巧妙了，太感谢了。花了几个小时才读懂你的公式。和大神的差距太可怕了。都怪平时少用电子表格。

大神的构思太巧妙、复杂，不利于我们这种电脑盲借鉴、引用。
还是前面几位大神的构思直截了当，便于借鉴。就是大量运算累坏了电脑。

真是难以取舍。

准提部林

ex1753 发表于 2017-10-15 18:45
构思太巧妙了，太感谢了。花了几个小时才读懂你的公式。和大神的差距太可怕了。都怪平时少用电子表格。
...

序號可用一般:=IF(D2=D1,N(E1)+1,1)
〔名次１〕那個公式就須轉幾彎才會弄懂，
改成：=IF(H2="","",MATCH(D2,O:O,)*100+I2+COUNTIF(H$1:H1,H2))　
就容易了解，但運算速度可能會稍慢！
　
資料少，可以數組解決，面對大數據，有時輔助公式也嫌不足，
公式是學一招，留一招，花時間去了解絕對值得，
至少比每次等重算的時間更划得來！
　
　

ex1753

准提部林 发表于 2017-10-15 19:33
序號可用一般:=IF(D2=D1,N(E1)+1,1)
〔名次１〕那個公式就須轉幾彎才會弄懂，
改成：=IF(H2="","",MATC ...

能够在这里瞻仰在Excel领域大师的神技，三生有幸
〔名次１〕这个公式好，不需要依赖〔序号〕E列，E列也不是重要参数，依赖越少，出错可能性越小

年轻的时候刚刚接触计算机，确实感觉到Excel在计算、统计方面不可思议的强大。可惜我的工作和Excel没什么关系，平时用Excel就是求个和什么的。

就连这个复杂的统计也是帮朋友做的，想起来脸红，没这金刚钻也去揽这瓷器活。

有个小小的感觉，Excel好像在2个以上条件查找、统计方面欠缺了点，不得不多个公式相互套用，没有一个单一的公式完成多条件统计，

准提部林

ex1753 发表于 2017-10-15 20:53
能够在这里瞻仰在Excel领域大师的神技，三生有幸
〔名次１〕这个公式好，不需要依赖〔序号〕E列，E列也 ...

〔名次１〕这个公式好，不需要依赖〔序号〕E列，E列也不是重要参数，依赖越少，出错可能性越小  

改的那個公式只是用來了解公式理路，實際並不適用你這檔案，因為檔案有分段群組，
在Ｈ１０輸入1188，就可知道錯在哪？（與Ｈ２相同，但不同分段，其累計應分開算才對）
　
所謂〔神技〕，不過是一個凡人，用得時間比別人多而已罷，
所有人都可以達到一定水準，只要多花時間在ＥＨ幫人解題及參考別人的思路！