- ベストアンサー
EXCELで社内システムのCSVデータを整形したい
社内システムで次のようなCSVファイルがダウンロードできます。 名前 チーム 道具 田中 A 1 鈴木 2 3 このデータをEXCELの関数を多用して次のように整形したいのですが、可能ですか? 名前 チーム 道具 田中 A 1 田中 A 2 田中 A 3 鈴木 A 1 鈴木 A 2 鈴木 A 3
- magi24br
- お礼率66% (10/15)
- Excel(エクセル)
- 回答数3
- ありがとう数2
- みんなの回答 (3)
- 専門家の回答
質問者が選んだベストアンサー
データA~D列 E~G列作業列 I~L列データ抽出 作業列1:E2式 =IF(C2="","",ROW()) A~D列データがあるまで下方へオートフィル 作業列2:F2式 =IFERROR(INDEX($A$2:$A$1000,MATCH(SMALL($E$2:$E$1000,ROW(A1)),$E$2:$E$1000,0)),"") 作業列3: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を下方へオートフィル
その他の回答 (2)
- ushi2015
- ベストアンサー率51% (241/468)
こんにちは 同じ様な感じですけど、 もし、名前、チームの種類、道具の数に制限が無い場合ならば、 条件 A~C列にデータ 1行目:項目名が必ず記載されてる。 2行目以降にデータ 名前、チームの種類、道具の数に制限が無い場合で、 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を下方へオートフィルとかで。
- dogs_cats
- ベストアンサー率38% (278/717)
条件 A~C列にデータ 1行目:項目名が必ず記載されてる。 2行目以降にデータ 名前、道具の数に制限は無いが、チームの種類は1つだけ。 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()関数で行番号を取得して繰り返しを行っていますので2行目から開始して下さい。 列を変更する場合は、F2、G2での=IF(E2=""の名前のセルのアドレスに変更下さい。
お礼
回答ありがとうございます。 私の説明不足で申し訳ないのですが、CSVファイルには案件が複数個あります。 この式では1案件しか扱えないのですが、複数案件扱えるようになりませんでしょうか。 今回は名前、チーム、道具の3項目を例としましたが、実際は100個近くの項目があります。 その中には、対象の案件を特定する項目もあり、それを含むと次のようになります。 案件ID 名前 チーム 道具 N01 田中 A 1 N01 鈴木 2 N01 3 N02 吉田 A 1 N02 武田 4 N02 5 N02 後付け設定で申し訳ありません。 チームは今のところ1案件につき1個しか指定されません。 名前はいくつも設定でき、道具もいくつも設定できます。 中には案件IDのみあり、項目が空白のデータもあります。(正しくは今回私がほしい項目がない行) 式で難しければVBAの使用を考えています。
関連するQ&A
- エクセルの関数について教えて下さい。
エクセルの関数について教えて下さい。 以下のような対戦表があります。 行毎の対戦です。B,Cにはそれぞれのスコアが入ります。 A B C D 1 田中 鈴木 2 目黒 井上 例えば次のように、それぞれの勝者をA3とD3に入るようにしたいのですが、どのようにすれば良いか分かりません。 A B C D 1 田中 2 5 鈴木 2 目黒 4 1 井上 3 鈴木 目黒 どなたか、お分かりになりましたら教えて下さい。 よろしくお願いいたします。
- ベストアンサー
- その他MS Office製品
- エクセル
エクセルでのカウント方法の関数がわかりません。 A列 B列 田中 福岡 田中 福岡 田中 福岡 鈴木 佐賀 鈴木 佐賀 田中 長崎 田中 長崎 このようなデータで、A列の条件でB列の種類を返すにはどのような方法がありますでしょうか? 田中2 鈴木1 わかりにくくて申しわけないですけど、よろしくお願い致します。
- 締切済み
- その他MS Office製品
- エクセル 条件つきで最小値を求めたいです
A列に200名を超える生徒の名前が入っています。 B列に計算テストの結果が入っています。 計算テストは何度も行われており、A列に何度も同じ生徒の名前が出てきます。A列に入っている生徒の名前は1000を超えます。 A B 田中 42 佐藤 48 鈴木 52 佐藤 56 田中 72 鈴木 60 鈴木 23 などです。この中で鈴木君のとった得点の最小値は23点ですが、それを求める数式を知りたいです。 『rankif』とか『minif』みたいな関数があればいいのですが、ないですよね。ちなみに、私が使用しているパソコンはexcel2000です。 他のシートやC列から右に生徒を判別する関数を入れていけば、私の知っている知識でも可能なのですが、関数を多用すると重くなりますし、避けたいです。また、もともとのデータの入力の仕方を変えたらいいのですが、それはできない状態です。 =MIN(IF(A2:A7="鈴木",B2:B7),false) みたいな感じかなと式を立てたのですが、うまくいきません。上の状態で、セルに23と表示されるには、どうすればいいか教えてください。お願い致します。m(_ _)m
- ベストアンサー
- オフィス系ソフト
- EXCELでカウント
EXCELでカウント EXCELでこのようにデータがあるとして↓ 田中 田中 鈴木 三島 鈴木 田中 田中は3個ある、鈴木は2個ある、三島は1個ある のようにカウントできる関数 または カウントする方法 を教えて下さい。
- ベストアンサー
- その他MS Office製品
- エクセル関数で困っています。
エクセル関数で困っています。 Aの表とBの表を統合したいのです。 ●A表 名前 身長 体重 田中 165 65 佐竹 167 45 伊藤 124 35 ● 名前 胴囲 座高 伊藤 70 60 佐竹 65 78 鈴木 62 42 これを名前をキーにして統合した表を作りたいのです 関数を教えてください。 よろしくお願いします。
- ベストアンサー
- その他(業務ソフトウェア)
- エクセルで検索、並び替えの方法
エクセルで以下のような並び変えはできないでしょうか? エクセルでA列とB列に名前が入っています。 A列、B列の名前は若干違います。 B列の名前をA列に揃えて並び変えることはできないでしょうか? 「処理前」 「処理後」 A B A B 伊藤 山田 伊藤 山田 田中 山田 山田 鈴木 鈴木 鈴木 鈴木 田中 伊集院 田中 田中 伊集院 宜しくお願いします。
- 締切済み
- オフィス系ソフト
- Excelで人数カウントをしたいのですが、、
Excel2002を使用しています。 まだ初心者で関数などあまりわかりません。 一生懸命考えてますが、なかなかいい案が思いつかないので どうかどうかよろしくお願いいたします! --------------------------------------- 社員の名前が1000行ぐらいに渡って入っています。 そこには同じ名前が何度か出てきます(連続ではなくとびとびで)。 その場合、たとえば田中さんだったら 一番最初に田中とでてくる行の隣のセルだけに 1とカウントしたいのです。 たとえば下記のような感じです。 最終的には人数の合計を出したいのです・・・ ------------------------------------ A B 1 名前 人数 2 田中 1 3 鈴木 1 4 田中 5 山本 1 6 鈴木 ------------------------------------ 同じ名前が連続のときであれば、IF(A2=A1,\"\",1) という式を思いついたのですが、それだと 離れてまた登場したときに、同じ名前に1が入ってしまいます。 何かいい方法、関数などございましたら、 ぜひぜひお教えください!! よろしくお願いいたします。
- ベストアンサー
- オフィス系ソフト
- エクセル VBAについて
エクセル初心者ながら質問させていただきます。 A1に田中 A2に山崎 A3に鈴木 があったとします。 B1からB3まではセルの結合を行っています。 B1に=A1&CHAR(10)&A2&CHAR(10)&A3というCHAR関数をいれてB1の表示をみてみると 田中 山崎 鈴木 と表示されました。 ここで質問なのですが、これでA2の山崎を消すとB1は 田中 鈴木 と、1行開いて表示されます。これをA2の山崎を消すとB1が 田中 鈴木 と、間を開かずに表示させたいのですが、何か良い方法はあるのでしょうか? 詳しい方いましたらよろしくお願いします。
- ベストアンサー
- その他(プログラミング・開発)
- Excelで表形式の変更をしたい
Excelで質問です。 (1)のような表形式を、(2)のように、元の1列ごとに見出しを付けた形式にしたいのですが、マクロなしで可能なのでしょうか? また、マクロの使用・非使用に限らず、以下の操作を実現するマクロや関数を教えていただけると助かります。 よろしくお願いいたします。 関数や式を使ってみましたがうまくいきませんでした。。。 (1) 番号 名前 評価 1 佐藤 A 2 鈴木 B 3 田中 C (2) 番号 1 名前 佐藤 評価 A 番号 2 名前 鈴木 評価 B 番号 3 名前 田中 評価 C
- ベストアンサー
- オフィス系ソフト
- エクセルの関数について
エクセルの関数の作り方について教えてください。 A列が曜日、、月、火、水、、、、土、日 B列が5人の名前がランダムに入っている 田中 鈴木、小泉、佐々木、、、 とします。それがずっと、、約1000行あります。 日曜日の鈴木さんの数をカウントする式は作れるでしょうか。
- ベストアンサー
- オフィス系ソフト
お礼
やりたかったことができました。 ありがとうございました。