多个条件的 Excel 中位数 [英] Excel Median for multiple conditions
问题描述
基本上在 excel 中,我想要一个表格,就像下面右侧给出的表格(我的数据规模比给出的示例大得多),它具有每个主题、每个条件(例如 TADA、TADP、TPDA、TPDP).理想情况下,我会使用数据透视表,但是,excel 不会在数据透视表中执行中位数".我想知道是否有一个公式可以让我不必手动完成并计算中位数,我已经尝试了一些(除了中位数(如果等......"),但我在 excel 中的编码知识是很差.有没有捷径可以做到这一点?
Basically in excel I want a table, like the one given below on the right (the scale of my data is a lot bigger than the example given),that has the median for each subject, for each condition (e.g. TADA, TADP, TPDA, TPDP). Ideally, I would use a pivot table, however, excel does not do 'median' in a pivot table. I was wondering if there was a formula I could to save me having to go through manually and working out the median, i've tried a few (along to lines of "median(if etc.." but my coding knowledge in excel is very poor. Is there a short way to do this?
Data Table
Subject RT condition Subject TADA TADP TPDA TPDP
1 23 TADA 1
1 54 TPDA 2
1 65 TADA 3
1 67 TPDP
1 76 TADA
2 72 TPDA
2 87 TADA
2 12 TPDP
2 45 TADP
2 32 TPDP
2 87 TADA
3 98 TPDA
3 12 TADA
3 53 TPDA
3 78 TADP
3 98 TPDP
推荐答案
假设数据在 A2:C100 中,然后你的结果表在 F1 中的标题和 E2 中的行标签向下你可以在 F2 中使用这样的数组公式
Assuming data in A2:C100 and then your results table with headers in F1 across and row labels in E2 down you can use an array formula like this in F2
=MEDIAN(IF($A$2:$A$100=$E2,IF($C$2:$C$100=F$1,$B$2:$B$100)))
用 CTRL+SHIFT+ENTER 确认并上下复制
confirmed with CTRL+SHIFT+ENTER and copied across and down
根据需要扩展数据范围
这篇关于多个条件的 Excel 中位数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!