如何从现有表制作新表并在电子表格中添加新列 [英] how to make a new table from an existing table and add new column in spreadsheet

问题描述

我想问一下电子表格或Google表格的公式.我有一个看起来像这样的数据:

I want to Ask about spreadsheet or google-sheets formula. I have a data that looks like this:

   A       B           C          D
   ------------------------------------------------
1 | UserId  fruitType   media     PriceStatus
2 | 3       Apple       Bag       Paid
3 | 7       Banana      Bag       Paid
4 | 7       Apple       Bag       Paid
5 | 43      Banana      Bag       Paid
6 | 43      Apple       Bag       FREE
7 | 43      Apple       Cart      Credit

注意:

1. 我的数据仅包含两种类型的水果:苹果和香蕉
2. 2种媒体类型:袋和购物车
3. 3价格状态的类型:已付费，信用和免费

1. My data only consist of 2 type of fruit : Apple and banana
2. 2 type of media : Bag and Cart
3. 3 Type of PriceStatus : Paid, Credit, & Free

正如您所看到的，该列与电子表格中我赋予值 AD 的列相同，而行则为 1-7 ，我的计划是表格看起来像这样:

As you can see the column is the same as the one in spreadsheet that I give value A-D and the row as 1-7, My plan is to make the sheets look like this:

UserId  Apple   Banana
3          1       0
7          1       1 
43         2       1

是否可以在电子表格中创建一个像这样的新表?我尝试使用 VLOOKUP ，但仍然无法实现，有人可以帮我吗?

is it possible to make a new table like this in spreadsheet? I tried using VLOOKUP but still failed to implement it, can someone help me with this?

推荐答案

尝试:

=QUERY(A1:D, "select A,count(A) where A is not null group by A pivot B", 1)

这篇关于如何从现有表制作新表并在电子表格中添加新列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！