英文:
find the serial number with criteria
问题
我有一个Excel数据库，他们要求我找到调查中最年长的当前吸烟者的8位pserial值。如果cigst为4，则该人是吸烟者。pserial号码在第3列，吸烟者在第N列。年龄在第N列。
这是数据库，但数据库中还有更多数据。
我认为要使用的函数是INDEX、MAX和IF，但不确定使用的顺序。
英文:
I have an Excel database, and they are asking me to find the 8-digit pserial value for the oldest current smoker in the survey. The person is a smoker if cigst is 4.
The pserial numbers are in column 3 and smokers are in column N.Age is column
this is the database, but there is more data inside the database:
I think the functions to use are INDEX, MAX and IF, but not sure on what order to use.
答案1
得分: 2
以下应该有效，假设输入数据在11-100行范围内。您可以根据问题中列出的标签适应它，也没有Excel版本限制。
=LET(f, FILTER(HSTACK(C11:C100, H11:H100), N11:N100=4), fa, INDEX(f,,1),
fb, INDEX(f,,2), TEXTJOIN(",",,FILTER(fa, fb=MAX(fb))))
您首先根据cigst列的值等于4筛选pserial和age列的子集，然后找到最大值。为了防止多个pserial值对应于最大年龄，我们使用TEXTJOIN来连接结果。如果这在您的实际情况中不符合预期，那么您可以删除这一步。
英文:
The following should work, assuming the input data are in the range of 11-100 rows. You can adapt it to your actual range. Considering also no excel version constraint based on tags listed in the question .
=LET(f, FILTER(HSTACK(C11:C100, H11:H100), N11:N100=4), fa, INDEX(f,,1),
fb, INDEX(f,,2), TEXTJOIN(",",,FILTER(fa, fb=MAX(fb))))
You filter first a subset of pserial and age columns based on cigst column values equal to 4, then just find the maximum. Preventing more than one pserial value correspond to the maximum age, we use TEXTJOIN to concatenate the result. If it is not expected for your real case, then you can remove this last step.
答案2
得分: 1
如果有多个年龄相同的人，最简单的方法是使用筛选函数：
=FILTER($C$11:$C$39, ($H$11:$H$39=MAXIFS($H$11:$H$39, $N$11:$N$39, 4)) * ($N$11:$N$39=4))
也许你需要根据你的Excel版本将分号替换为逗号。
英文:
If there are several people with the same age, it is easiest to use the filter function:
=FILTER($C$11:$C$39;($H$11:$H$39=MAXIFS($H$11:$H$39;$N$11:$N$39;4))*($N$11:$N$39=4))
Maybe you have replace semicolon with comma, depending of your Excel version.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论