英文:
In Pivot Table (Google Sheets) filter on dates if cell contains a date or show all results
问题
我在处理这个Google Sheets文件，在其中展示住宿价格。在数据透视表中，您可以根据特定出行日期进行筛选。但是当您不使用筛选时，您会看到所有出发日期的平均价格。
我想做的是让用户可以在单元格L4中填入出发日期。如果他们使用这个单元格（从下拉菜单中选择特定日期），数据透视表中的结果将显示该特定出发日期的价格。如果此单元格为空，则数据透视表必须显示所有结果。
为此，我正在使用“start_date”作为数据透视表中的筛选器，选择“按条件筛选”、“日期为”、“精确日期”=L4。然而，当L4为空时，数据透视表不显示任何结果，但它必须在此单元格为空时显示所有结果。当L4有值（日期）时，它应该只显示该日期的结果。
当前数据透视表的操作： 当单元格L4包含日期时，它显示该特定日期的结果。当L4为空时，数据透视表不显示任何内容...
数据透视表应该做的事情： 当单元格L4为空时，它不应使用start_date筛选器，并且应显示所有结果。
英文:
I'm working on this Google Sheets where we show prices of accommodations. In the pivot you have the ability to filter on specific travel dates. But when you don't use the filter you see the average prices of all departure dates.
What I want to do is that users can fill in the departure date in cell L4. If they use this cell (choose a specific date from the dropdown) the results in the pivot will show the prices of that specific departure date. If this cell is empty the pivot has to show all the results.
For this I'm using 'start_date' as filter in the pivot where I chose 'filter by condition' 'date is' 'exact date' =L4. However, when L4 is empty the pivot is not showing any results but it has to show all results when this cell is empty. When L4 has a value (date) than it should only show the results of this date.
What the pivot does right now: When cell L4 contains a date it shows the results for that specific date. When L4 is empty the pivot shows nothing...
What the pivot should do: When cell L4 is empty it should not use the start_date filter and it should show all results.
答案1
得分: 1
The solution is to use '按条件筛选' in the Pivot Filter option and then choose Custom Formula:
=IF(ISBLANK(L4), TRUE, Start_date = L4)
英文:
If found the solution. The solution is to use 'Fliter by condition' in the Pivot Filter option and than choose Custom Formula is:
=IF(ISBLANK(L4), TRUE, Start_date = L4)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论