Excel Median适用于多种条件 [英] Excel Median for multiple conditions

问题描述

基本上在excel中，我想要一张表格，如右下图所示(我的数据规模比给定的示例大得多)，该表格具有每个科目的每个条件(例如TADA， TADP，TPDA，TPDP).理想情况下，我将使用数据透视表，但是excel不会在数据透视表中执行中位数"操作.我想知道是否有一个公式可以节省我必须手动进行计算并计算出中位数的问题，我已经尝试了一些(沿着"median(if etc.")行，但是我在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 Median适用于多种条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！