google-sheets - 创建一个数据透视表,其中结果列是原始列和行的乘积?
问题描述
如果没有大量昂贵的脚本,这可能是不可能的,但我想由专家运行它,以防我遗漏了什么。很难解释(因为它很荒谬.. 即不是我的选择),所以我只举一个非常简化的例子。
我的源数据表是这样的......
+----------+-------+------+--------+
| Date | Time | Cars | Trucks |
+----------+-------+------+--------+
| 01/01/19 | 08:00 | 2 | 12 |
| 01/01/19 | 12:00 | 4 | 10 |
| 01/01/19 | 20:00 | 6 | 8 |
| 01/02/19 | 08:00 | 8 | 6 |
| 01/02/19 | 12:00 | 10 | 4 |
| 01/02/19 | 20:00 | 12 | 2 |
+----------+-------+------+--------+
..我想让另一张工作表像...一样动态显示它
+----------+---------------+---------------+---------------+
| | 08:00 | 12:00 | 20:00 |
+----------+------+--------+------+--------+------+--------+
| | Cars | Trucks | Cars | Trucks | Cars | Trucks |
+----------+------+--------+------+--------+------+--------+
| 01/01/19 | 2 | 12 | 4 | 10 | 6 | 8 |
| 01/02/19 | 8 | 6 | 10 | 4 | 12 | 2 |
+----------+------+--------+------+--------+------+--------+
换句话说,一个列每次在类别组合。请记住,实际上,这是一个大型数据集。此外,我对标题有一点灵活性,因为输出中的两个标题行可能是一个。像“汽车 8:00”、“卡车 8:00”、“汽车 12:00”……等等
有谁知道如何使用数据透视表来做到这一点?还是其他一些简单的方法?
这是同一示例的实时版本... https://docs.google.com/spreadsheets/d/1npQikx3Zwa2QZwDAk8IxyawYw2hkeYpPe9Nh4ImkZAE/edit?usp=sharing
解决方案
尝试:
=ARRAYFORMULA({{TEXT(SUBSTITUTE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(
QUERY({Source!A2:B, TRANSPOSE(QUERY(TRANSPOSE(Source!C2:D),,999^99))},
"select Col1,max(Col3) where Col1 is not null group by Col1 pivot Col2"),
"limit 0", 1)),,999^99)), " "), "1899-12-30", ), "hh:mm"), ""}; {"",
SPLIT(REPT(Source!C1&" "&Source!D1&" ", COUNTUNIQUE(Source!B2:B)), " ")};
TRANSPOSE(QUERY(TRANSPOSE(SPLIT(TRANSPOSE(QUERY(TRANSPOSE(QUERY(QUERY({Source!A2:B,
TRANSPOSE(QUERY(TRANSPOSE(Source!C2:D),,999^99))},
"select Col1,max(Col3) where Col1 is not null group by Col1 pivot Col2"),
"offset 1", 0))&" ",,999^99)), " ", 1, 0)), "where "&JOIN(" or ", "Col"&
ROW(INDIRECT("A1:A"&COUNTUNIQUE(Source!A2:A)))&" is not null"), 0))})
电子表格演示
推荐阅读
- swift - Local and Network Data Manager
- azure - 如何使用帕洛阿尔托网络 ngf 保护 azure web 应用程序
- oracle - Using Trigger to maintain referential integrity
- javascript - 事件侦听器在 ajax 操作后消失并在刷新后返回
- php - 用于从文件服务器中删除数据的 PHP 脚本
- javascript - 当我有唯一键时,为什么会收到唯一键道具警告?
- azure - ARM resource iteration failing with an empty array
- javascript - Use let variable in different functions
- java - Android Studio 3.2.1 虚拟设备创建错误
- python - How to save the results in sdf file format after simulation through python Api in Dymola