如何根据一列中的不同值填充多个列

I have an excel file as:

我有一个excel文件:

+----+------------+
| ID | Order date |
+----+------------+
| A  | 1/1/2011   |
| A  | 1/3/2012   |
| A  | 1/8/2013   |
| A  | 1/20/2014  |
| A  | 1/23/2015  |
| B  | 1/1/2011   |
| B  | 1/20/2014  |
| B  | 1/23/2015  |
+----+------------+

I want to convert it in a report that informs me what all years did the person bought the product in the below format:

我想在一份报告中对其进行转换,该报告告诉我该人以下列格式购买产品的所有年份:

+----+------------+-----------+------+------+------+-----+
| ID | Order date | 2011 2012 | 2013 | 2014 | 2015 |     |
+----+------------+-----------+------+------+------+-----+
| A  | 1/1/2011   | Yes       | No   | No   | No   | No  |
| A  | 1/3/2012   | No        | Yes  | No   | No   | No  |
| A  | 1/8/2013   | No        | No   | Yes  | No   | No  |
| A  | 1/20/2014  | No        | No   | No   | Yes  | No  |
| A  | 1/23/2015  | No        | No   | No   | No   | Yes |
| B  | 1/1/2011   | Yes       | No   | No   | No   | No  |
| B  | 1/20/2014  | No        | No   | No   | Yes  | No  |
| B  | 1/23/2015  | No        | No   | No   | No   | Yes |
+----+------------+-----------+------+------+------+-----+

any help is much appreciated! Thanks!

任何帮助深表感谢!谢谢!

1 个解决方案

#1

Assuming the data is in column A and B, with a header row, and you have the years going across the top, you can use this (place in C2):

假设数据位于A列和B列,带有标题行,并且您有多年的时间跨越顶部,您可以使用它(放在C2中):

=IF(YEAR($B2)=C$1,"Yes","No") and drag over and down.

= IF(年($ B2)= C $ 1,“是”,“否”)并上下拖动。

Pretty straightforward. It takes the YEAR from column B, checks against the year in the current column's header row, and puts "Yes" if they match.

很简单。从B列开始,需要YEAR,检查当前列标题行中的年份,如果匹配,则选择“Yes”。

A note, we didn't do anything with the IDs. Perhaps that's okay, but I'm thinking you included them in your sample for some reason (maybe not though), so if you want to whittle this down further, update your OP.

注意,我们没有对ID做任何事情。也许这没关系,但我认为你出于某种原因(可能不是)将它们包含在你的样本中,所以如果你想进一步减少它,请更新你的OP。

edit: For funsies, in case you just want to drag willy-nilly, add a statement to leave it blank if there's no data in B: =If($B2<>"",IF(YEAR($B2)=C$1,"Yes","No"),"")

编辑:对于funsies,如果您只是想无所事事地拖动,请添加一个语句,如果B中没有数据则将其留空:= If($ B2 <>“”,IF(YEAR($ B2)= C $ 1 , “是”, “否”), “”)

#1