Google表格:根据另一个单元格MAX值选择单元格值 [英] Google Sheets: selecting cell values based on another cell MAX values
问题描述
我正在尝试使用Google表格建立学生数据库.它包含有关学生，团体和订单的信息；订单可以更改组中的学生身份(组中，上移到新组，毕业，请假，下达).此处是示例数据库表，
I'm trying to make a database of students using Google Sheets. It contains info about students, groups and orders; orders can change students membership in groups (taken in a group, moved up to a new group, graduated, on leave, sent down). Here are sample database sheets and here is a detailed description of my DB structure (the sheet report_Groups is slightly changed, its previous variant, described on the link, is now named old_report_Groups).
我需要一个查询，该查询将在给定的日期中选择给定的组的当前成员的列表.这意味着我必须为每个学生选择名称，给定日期之前的状态和相应的组.然后从该结果中选择学生姓名，其状态为已加入"或上移"，并且分组与给定的分组相同.
I need a query that would select a list of present members of given group on the given date. That means that for each student I have to select the name, the latter status before given date and corresponding group. And from this result select student names, where statuses are "Taken in" or "Moved Up" and group is the same as given one.
问题是选择后一种状态.它应该是MAX(status)，其自"日期≤给定日期，但是存在一个众所周知的问题，即选择多个字段以及聚合函数.此处是一个非常接近的问题，但可以从其查询最佳"答案给了我错误"QUERY:NO_COLUMN".我什至从那里复制了表格 Raw ，并尝试对报告的数据进行建议的查询(唯一的修改—根据我的语言环境限制，用分号替换逗号)错误(检查 my 数据库中的 Raw 和 report_Raw 工作表).其他变体(通过MMULT和TRANSPOSE)也可以使用，但是性能却很差.
The problem is to select the latter status. It should be MAX(status), whose "since" date ≤ given date, but there's a well-known problem of selecting more than one field together with aggregate function. Here is a question which is very close to, but query from its "best" answer gives me error "QUERY:NO_COLUMN". I've even copied the sheet Raw from there and tried to perform proposed query (with the onliest modification — replacing commas with semicolons according to my locale restrictions) on the data it was reported to work on — same error (check Raw and report_Raw sheets in my DB). Other variant (via MMULT and TRANSPOSE) works, but it's perfomance is very poor.
你能建议我什么?预先感谢.
What can you suggest me? Thanks in advance.
更新:我找到了有问题的解决方案(在我的答案中进行了描述).要解决此问题，我需要知道其他问题的答案.
Update: I've found the solution with an issue (described in my answer). To solve the issue I need to know an answer for a different question.
推荐答案
这是解决方案(问题如下所述).
Here's the solution (with an issue described below).
A.筛选 Orders_Students 以选择行，因为这些单元格具有自"单元格值≤给定日期( report_Groups ！A2):
A. Orders_Students is filtered for selecting rows, having "since" cell value ≤ given date (report_Groups!A2):
=QUERY(Orders_Students!B:E;"select E, B, C, D where E <= date '" & TEXT(report_Groups!A2;"yyyy-MM-dd") & "'";1)
此临时结果存储在 inner_report_Groups 标签中(在下一个查询中将多次引用该结果).
This interim result is stored at the inner_report_Groups tab (it will be referenced few times in the next query).
B.过滤 inner_report_Groups 以便为每个学生选择MAX("since")值和相应的行单元格值 :
B. inner_report_Groups is filtered for selecting MAX("since") values and corresponding row cell values for each student:
ARRAYFORMULA(VLOOKUP(QUERY({ROW(inner_report_Groups!A$2:A)\SORT(inner_report_Groups!A$2:D)};"select max(Col1) group by Col3 label max(Col1)''";0);{ROW(inner_report_Groups!A$2:A)\SORT(inner_report_Groups!A$2:D)};{3\4\5};0)
上面的公式用作 report_Groups ！D2中的 inner 查询(在D3，D4中也有相应的索引).
The formula above is used as inner query in report_Groups!D2 (also in D3, D4—with appropriate indeces).
C.过滤第二个查询结果，以获取状态为已加入"或上移"且相应组等于给定组( report_Groups ！B2(同样在B3，B4中-带有适当的指示)):
C. The second query result is filtered to get students whose status is either "Taken in" or "Moved Up" and corresponding group is equal to the given group (report_Groups!B2 (also in B3, B4—with appropriate indeces)):
=TRANSPOSE(IFERROR(QUERY(<here is the formula from step B>);"select Col1 where Col3 = '" & B2 & "' and (Col2='Taken in' or Col2='Moved Up')";0)))
上面的公式在 report_Groups ！D2中用作 外部 查询(在D3，D4中，也带有适当的索引).如果查询结果为#N/A，则IFERROR旨在不显示任何内容.
The formula above is used as outer query in report_Groups!D2 (also in D3, D4—with appropriate indeces). IFERROR is intended to display nothing if query result is #N/A.
该查询显示所需的结果，如您在 report_Groups 标签中所见.但是，当步骤B的查询搜索 inner_report_Groups 的整个列 时，只能有一个给定的日期分析(或其他给定日期的查询中期结果应放在 inner_report_Groups 的不同列中或在不同的标签中.是否有任何方法可以为中期提供别名结果是在单个单元格公式中引用它，而不是将其保留在其他选项卡上?
That query displays the needed results as you can see in report_Groups tab. But as the query on step B searches the whole columns of inner_report_Groups, there's only a single given date can be analysed (or the query interim results for other given dates should be placed in different columns of inner_report_Groups or at the different tab. Is there any way to give an alias for an interim result to refer it in a single cell formula instead of keeping it on different tab?
这篇关于Google表格:根据另一个单元格MAX值选择单元格值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
