根据选定的值过滤数据，在Google表格中按日期进行排序和计数 [英] Filter data according to a selected value, sorting and counted by date in Google Sheets

问题描述

在第一列中，我有一个列表，该列表按几个学生的日期及其要求的主题排序.可以看出，如果问题已解决，则该表将按数据，名称，主题和复选框进行排列.

在第二列中，我有一个下拉列表，我要在其中选择学生并在其最近15天的请求下方进行查询，以检查其是否完成.

每次选择该学生时，A4行将显示他/她最近15天排序的所有主题，单元格F3和F4将根据总数和已解决的问题进行计数.

可以通过公式应用它吗?

以下是您需要的文件:

---

F3:

  = COUNTA(B4:B) 

F4:

  = COUNTIF(C4:C; TRUE) 

In the first column I have a list sorted by the date of several students and its topics requested. As can be seen, the table is arranged by data, name, topic and a checkbox if the question has been solved.

In the second column, I have a drop down list where I want to select the student and query below his last 15 days requests, checking whether it is completed or not.

Every time the student is selected, the row A4 will display all his/her topics sorted from the latest 15 days, and the cells F3 and F4 will count according to total and solveds.

Is that possible to apply it via formula?

Here is the file if you need:

https://docs.google.com/spreadsheets/d/1w7beVyUr0pEUoRSgqQYYOi0y0Sbd8scC3B825GKA3sQ/edit?usp=sharing

解决方案

your column A on Control sheet contains a mixture of dates and text strings (invalid dates) so the formula needs to be:

=ARRAYFORMULA(QUERY(SORT({IFNA(IF(N(Control!A2:A)<>0; Control!A2:A; DATE(
 REGEXEXTRACT(Control!A2:A; "/(\d+) "); 
 REGEXEXTRACT(Control!A2:A; "/(\d+)/"); 
 REGEXEXTRACT(Control!A2:A; "^\d+"))+
 INDEX(SPLIT(Control!A2:A; " ");;2)))\ Control!B2:D}; 1; 0); 
 "select Col2,Col3,Col4 where Col2 = '"&A1&"' limit 15"; 0))

---

F3:

=COUNTA(B4:B)

F4:

=COUNTIF(C4:C; TRUE)

这篇关于根据选定的值过滤数据，在Google表格中按日期进行排序和计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！