我的问题很简单，但是一位同事问我，我几乎从不使用Excel。 在Mathematica中，我可以在几秒钟内解决这个问题。 让我们假设一个非常简单的安排，包括患者ID，测量年份和值：
问题1：如何创建仅包含第一次测量的新表？ 也就是说，对于ID 1 2014行，ID 2为2015行，ID 3为2016行。
问题2：如何创建一个新表，每个ID只有一行，并且所有找到的Values都与MEDIAN()这样的函数一起使用？ 对于ID 1，中位数为12和23，对于ID 2，中位数为32和16，ID 3为4。
My question is embarrassingly simple, but a colleague asked me, and I almost never use Excel. In Mathematica, I can solve this within seconds. Let us assume a very simple arrangement consisting of a patient ID, a year of measurement, and a value:
Question 1: How can I create a new table consisting only of the first measurements? That is, for ID 1 the 2014 row, for ID 2 the 2015 row and for ID 3 the 2016 row.
Question 2: How can I create a new table that has only one line for each ID and where all found Values are used with a function like MEDIAN()? That is for ID 1 the median of 12 and 23, for ID 2 the median of 32 and 16 and for ID 3 just 4.
最满意答案
假设您的表按日期排序（它在图片中），那么可以使用VLOOKUP解决第一个问题
=VLOOKUP("1",A1:C6,3,0)将"1"替换为新表中包含ID 1单元格。
对于问题2，您可以使用以下公式，再次使用新表中包含ID 1单元格替换=1
{=MEDIAN(IF(A1:A6=1,C1:C6,""))}注意，这是一个数组公式，你不直接键入大括号{} ，但只需按CTRL + SHIFT + ENTER ，而不是输入公式时只需输入
这可以通过创建像这样的数组{12,23,"","",""}并取这些数字的中位数来实现。 MEDIAN忽略字符串，因此根据需要给出中间值12和23（ID 1的示例）
Assuming your table is ordered by date (which it is in the picture), then the first question can be solved using a VLOOKUP
=VLOOKUP("1",A1:C6,3,0)Replace "1" which whichever cell contains the ID 1 in your new table.
For question 2, you can use the following formula, again replacing =1 with whichever cell contains ID 1 in your new table
{=MEDIAN(IF(A1:A6=1,C1:C6,""))}Note, this is an array formula, you don't type the curly brackets {} directly, but simple press CTRL+SHIFT+ENTER, rather than just ENTER when entering the formula
This works by creating an array like this {12,23,"","",""} and taking the median of those numbers. MEDIAN ignores the strings so gives you the median of 12 and 23, as required (example for ID 1)
更多推荐
发布评论