按日期重新排序和重新组合多列 [英] Reorder and regroup multiple columns by date
本文介绍了按日期重新排序和重新组合多列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我对这个有点难,希望能得到一些帮助:)
I'm a bit stumped on this one, would love some help on this :)
这是我的数据:
+--- Col A --+- Col B --+-- Col C ---+--- Col D --+- Col E --+-- Col F ---+
| FRANCE | ITALY |
| DATE | Installs | Uninstalls | DATE | Installs | Uninstalls |
+---+----------------+----------------------------------------------------+
| 14/09/2020 | 1 | 2 | 14/09/2020 | 1 | 2 |
| 15/09/2020 | 3 | 1 | 17/09/2020 | 4 | 1 |
| 16/09/2020 | 1 | 2 | 22/09/2020 | 1 | 2 |
| 22/09/2020 | 3 | 1 | 26/09/2020 | 4 | 1 |
+------------+----------+------------+------------------------------------+
我希望像这样按日期对结果进行分组(一个日期 = 一行):
I am looking to group the results by date like this (one date = one line) :
+--- Col A --+- Col B --+-- Col C ---+--- Col D --+- Col E --+-- Col F ---+
| FRANCE | ITALY |
| DATE | Installs | Uninstalls | DATE | Installs | Uninstalls |
+---+----------------+----------------------------------------------------+
| 14/09/2020 | 1 | 2 | 14/09/2020 | 1 | 2 |
| 15/09/2020 | 3 | 1 | | 0 | 0 |
| 16/09/2020 | 1 | 2 | | 0 | 0 |
| | 0 | 0 | 17/09/2020 | 0 | 0 |
| 22/09/2020 | 3 | 1 | 22/09/2020 | 1 | 2 |
| | 0 | 0 | 26/09/2020 | 4 | 1 |
+------------+----------+------------+------------------------------------+
我熟悉查询,但很难找到正确的公式:(
I am familiar with query, but having difficulties finding the right formula :(
提前谢谢你!!
最佳
推荐答案
在 你的评论你提到
我在真实数据集中有更多国家/地区,所有国家/地区都采用相同的格式 (DATE | INSTALLS | UNINSTALLS)
I have more countries in the real data set, all on the same format (DATE | INSTALLS | UNINSTALLS)
您可以使用单个公式
={ArrayFormula(IFERROR(VLOOKUP(UNIQUE(SORT({A3:A;D3:D})),A3:C,{1,2,3},FALSE))),
ArrayFormula(IFERROR(VLOOKUP(UNIQUE(SORT({A3:A;D3:D})),D3:F,{1,2,3},FALSE)))}
您可以通过更改为下一组 G3:I
并使用逻辑添加 arrayformula 部分来添加下一个国家:
You can add the next country by changing to the next set G3:I
and adding the arrayformula part using the logic:
={ArrayFormula(...),
ArrayFormula(...),
ArrayFormula(...),
ArrayFormula(...),
ArrayFormula(...)}
这篇关于按日期重新排序和重新组合多列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文