如何使用IF来允许我在视图之间进行选择? [英] How to use an IF to allow me to select between the views?
问题描述
我目前正在Google表格中创建一个信息中心。
- 在单元格
A2
中,可以从两个视图中选择一个
- 在
A5
中,他们可以选择一个开始日期,并在A8
code>可以填写结束日期。 - 在单元格
A10:C17
中,我有一些模型数据见下面)。
现在我想要在两个日期之间显示数据并基于视图(在表中称为选择器用户可以选择测试和测试2)。我可以使用Filter()来过滤日期,但是,我无法选择一个视图。因此,我只能在日期中使用它,而不是视图。
这是允许我过滤日期的公式:
= arrayformula(ifs($ A $ 2 =test; 1)* FILTER(A11:C17; A11:A17> = A5; A11:A17 <= A8))
任何有关如何使用IF(或其他可能工作的函数)的想法都可以让我在视图之间进行选择? / p>
实体模型数据:
pre $选择器
测试
开始日期
01-01-2018
结束日期
03- 01-2018
日期用户浏览量
04-01-2018 350 400
03-01-2018 300 350
02- 01-2018 250 300
01-01-2018 200 250
31-12-2017 150 200
30-12-2017 100 150
29-12-2017 50 100
如果您想要,您可以在这里查看电子表格: https://docs.google.com/spreadsheets/d/e/2PACX-1vRjwsm49p48sP2dZXWjdMacw9vLe0GoADLKc_J4E2Sggo5hDpp2a_2zpCsZM4jlW21CfLrG_TSthhV7/pubhtml
在A10:C17可以看到我想要展示的数据,然后在A20:A27中可以看到我想为test2展示的数据。解决方案这适用于组合查看下拉菜单。如果您想分开视图选项以进行并排比较,那应该没有问题。
过滤器
= IF ,IF(A2 =test2,FILTER(A11:C,A11:A> = B5,A11:A <= B8),Sorry。No matches found))
所选日期减1年
= DATE(年(A5)-1,月(A5),日(A5))
请参阅此 Google表格
根据电子表格数据,下拉菜单中的日期范围仅为
数据验证
。I am currently creating a dashboard in Google Sheets.
- In cell
A2
, one can select from two views (let's say "test" and "test2"). - In
A5
they can pick a starting date and inA8
one can fill in the end day. - In cells
A10:C17
I have some mock-up data (see below).
Now what I want is to display the data between two dates and based on the views (in the table called selector. Users can select test and test2). I am able to use Filter() to filter the dates, however, I am unable to select a view. Thus I can only use it for the dates, not for the views.
This is the formula that allows me to filter the dates:
=arrayformula(ifs($A$2="test";1)*FILTER(A11:C17;A11:A17>=A5;A11:A17<=A8))
Anyone any idea on how to use an IF (or any other function that might work) to allow me to select between the views?
Mock-up data:
Selector Test Start date 01-01-2018 End date 03-01-2018 Date Users Pageviews 04-01-2018 350 400 03-01-2018 300 350 02-01-2018 250 300 01-01-2018 200 250 31-12-2017 150 200 30-12-2017 100 150 29-12-2017 50 100
If you want you can view the spreadsheet here: https://docs.google.com/spreadsheets/d/e/2PACX-1vRjwsm49p48sP2dZXWjdMacw9vLe0GoADLKc_J4E2Sggo5hDpp2a_2zpCsZM4jlW21CfLrG_TSthhV7/pubhtml In A10:C17 you can see the data I want to show for test and in A20:A27 you can see the data I want to show for test2.
解决方案This works for a combined "view" drop down. If you want to separate the view options to get a side-by-side comparison, that should be no problem.
Filter
=IF(A2="test",FILTER(A11:C,A11:A>=A5,A11:A<=A8),IF(A2="test2",FILTER(A11:C,A11:A>=B5,A11:A<=B8),"Sorry. No matches found"))
Selected dates minus 1 year
=DATE(YEAR(A5)-1,MONTH(A5),DAY(A5))
See this Google Sheet
The dates ranges in the drop downs are just
data validation
based on the spreadsheet data.这篇关于如何使用IF来允许我在视图之间进行选择?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
- In cell