按日期对多列进行重新排序和重新分组 [英] 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屋！