EXCELで社内システムのCSVデータを整形したい

質問

社内システムで次のようなCSVファイルがダウンロードできます。

名前　　チーム　道具
田中　　A 　　　1
鈴木　　　　　　2
　　　　　　　　3

このデータをEXCELの関数を多用して次のように整形したいのですが、可能ですか？

名前　　チーム　道具
田中　　A 　　　1
田中　　A 　　　2
田中　　A 　　　3
鈴木　　A 　　　1
鈴木　　A 　　　2
鈴木　　A 　　　3

回答No.3

dogs_cats · ベストアンサー

データA~D列
E~G列作業列
I~L列データ抽出

作業列１：E2式
=IF(C2="","",ROW())
A~D列データがあるまで下方へオートフィル
作業列２：F2式
=IFERROR(INDEX($A$2:$A$1000,MATCH(SMALL($E$2:$E$1000,ROW(A1)),$E$2:$E$1000,0)),"")
作業列３：G2式
=IF(F2="","",COUNTIFS($A$2:$A$1000,F2,$B$2:$B$1000,"<>")*COUNTIFS($A$2:$A$1000,F2,$D$2:$D$1000,"<>"))
F2~G2下方へオートフィル

I2式
=IF(F2="","",F2)
I3式
=IF(ROWS($2:3)>SUM($G$2:$G$100),"",IF(COUNTIF(I$2:I2,I2)<INDEX($G$2:$G$100,MATCH(I2,$F$2:$F$100,0)),I2,INDEX($F$2:$F$100,MATCH(I2,$F$2:$F$100,0)+1)))

J2式
=IFERROR(OFFSET(INDEX($B$2:$B$1000,MATCH(I2,$A$2:$A$1000,0)),INT((COUNTIF($I$2:I2,I2)-1)/COUNTIFS($A$2:$A$1000,I2,$D$2:$D$1000,"<>")),),"")

K2式
=IFERROR(VLOOKUP(I2,$A$2:$C$1000,3,FALSE),"")

L2式
=IFERROR(OFFSET(INDEX($D$2:$D$1000,MATCH(I2,$A$2:$A$1000,0)),IF(MOD(COUNTIF($I$2:I2,I2),COUNTIFS($A$2:$A$1000,I2,$D$2:$D$1000,"<>"))=0,COUNTIFS($A$2:$A$1000,I2,$D$2:$D$1000,"<>"),MOD(COUNTIF($I$2:I2,I2),COUNTIFS($A$2:$A$1000,I2,$D$2:$D$1000,"<>")))-1,),"")
J2~L2をJ3~L3までコピー
I3~L2を下方へオートフィル

お礼

やりたかったことができました。
ありがとうございました。

回答No.2

ushi2015

こんにちは

同じ様な感じですけど、
もし、名前、チームの種類、道具の数に制限が無い場合ならば、

条件
A~C列にデータ
１行目：項目名が必ず記載されてる。
２行目以降にデータ
名前、チームの種類、道具の数に制限が無い場合で、

E~F列に抽出するとした場合は
下記式を指定セルにコピー
E2に
=IF((COUNTA($A:$A)-1)*(COUNTA($B:$B)-1)*(COUNTA($C:$C)-1)>=ROW($A1),OFFSET($A$1,INT(((ROW($A1)-1)+(COUNTA($B:$B)-1)*(COUNTA($C:$C)-1))/((COUNTA($B:$B)-1)*(COUNTA($C:$C)-1))),0),"")
F2に
=IF(E2="","",OFFSET($B$1,IF(INT(((ROW($A1)-1)+(COUNTA($C:$C)-1))/(COUNTA($C:$C)-1))>(COUNTA($B:$B)-1),MOD(INT(((ROW($A1)-1)+(COUNTA($C:$C)-1))/(COUNTA($C:$C)-1))+1,(COUNTA($B:$B)-1))+1,INT(((ROW($A1)-1)+(COUNTA($C:$C)-1))/(COUNTA($C:$C)-1))),0))
G2に
=IF(E2="","",OFFSET($C$1,MOD(ROW($A2)+1,(COUNTA($C:$C)-1))+1,0))
で、E2~F2を下方へオートフィルとかで。

回答No.1

dogs_cats

条件
A~C列にデータ
１行目：項目名が必ず記載されてる。
２行目以降にデータ
名前、道具の数に制限は無いが、チームの種類は１つだけ。

E~F列に抽出
下記式を指定セルにコピー
E2式
=IF((COUNTA(A:A)-1)*(COUNTA(C:C)-1)<ROW(A1),"",INDEX(A:A,INT((ROW()-2)/(COUNTA(C:C)-1))+2))
F2式
=IF(E2="","",$B$2)
G2式
=IF(E2="","",OFFSET($C$2,MOD((ROW()-2),(COUNTA(C:C)-1)),))

E2~F2を下方へオートフィル。

数式でrow()関数で行番号を取得して繰り返しを行っていますので２行目から開始して下さい。
列を変更する場合は、F2、G2での=IF(E2=""の名前のセルのアドレスに変更下さい。

お礼

回答ありがとうございます。

私の説明不足で申し訳ないのですが、CSVファイルには案件が複数個あります。
この式では1案件しか扱えないのですが、複数案件扱えるようになりませんでしょうか。

今回は名前、チーム、道具の3項目を例としましたが、実際は100個近くの項目があります。
その中には、対象の案件を特定する項目もあり、それを含むと次のようになります。

案件ID　名前　チーム　道具
N01　　田中　Ａ　　　1
N01　　鈴木　　　　　2
N01　　　　　　　　　3
N02　　吉田　Ａ　　　1
N02　　武田　　　　　4
N02　　　　　　　　　5
N02

後付け設定で申し訳ありません。
チームは今のところ1案件につき1個しか指定されません。
名前はいくつも設定でき、道具もいくつも設定できます。
中には案件IDのみあり、項目が空白のデータもあります。(正しくは今回私がほしい項目がない行)

式で難しければVBAの使用を考えています。