Excelで以下のフォーマットのデータから

時間  ID   state
------- ------- ---------
10:10  1    idle
10:10  2    wait
10:11  1    idle
10:11  2    wait
10:12  1    wait
10:12  2    wait

の形式に変換するための方法を教えてください。

  |時間
ID |10:10 10:11 10:12
---+------------------
1 | idle idle wait
2 | wait wait wait


ピボットテーブルを試しましたが、データ欄は基本的に数値限定のようで、うまくいきませんでした。どうぞよろしくお願いいたします。

回答の条件
  • 1人2回まで
  • 登録:2008/11/19 19:31:22
  • 終了:2008/11/26 19:35:03

回答(2件)

id:SALINGER No.1

SALINGER回答回数3454ベストアンサー獲得回数9692008/11/19 21:18:16

ポイント35pt

VBAなら一括して変換するコードを書けそうですがExcelの機能と関数だけでやってみます。


まず最初の表の4列目に見出し「作業列」などにして2行目以降の数式を

=IF(C2="idle",1,IF(C2="wait",2,0))

にして、無理やり数値にしてしまいピボットテーブルでデータ領域を作業列として、フィールドを合計にする。


できた、ピボットテーブルを選択してコピー(右クリックのメニューではでコピーできないので

Ctrl+Cでコピー)して、別の場所に貼り付け。

貼り付けたものに「1」を「idle」。「2」を「wait」に置換。


無理やりピボットテーブルでやりましたが、できないこともないということで。

id:my_redo_log

なるほど、一時的に数値にしておき、ピボットテーブル集計後に文字列に直すということですね。

2008/11/20 12:21:48
id:kaiton No.2

kaiton回答回数260ベストアンサー獲得回数342008/11/20 14:11:44

ポイント35pt

一時的な作業セルを使って関数で行う方法です。


↓は、その行を一意に表すキーのデータとします。(時刻とIDの両方が重なるデータはないとしています。)

D2=TEXT(A2,"hh:mm")&"_"&B2 を下にコピー


時刻のうち同一のものを判断

F2=COUNTIF($A2:$A$7,$A2) を下にコピー

G2=IF(F2=1,A2,"") を下にコピー


IDが同一のものを判断

I2=COUNTIF($B2:$B$7,B2) を下にコピー

J2=IF(I2=1,B2,"") を下にコピー


L2=IF(COUNT($J:$J)>=ROW()-1,SMALL($J$2:$J$7,ROW()-1),"") を下にコピー

M2=IF(COUNT($G:$G)>=COLUMN()-12,SMALL($G$2:$G$7,COLUMN()-12),"") を右にコピー


M3=IF(AND(M$1<>"",$L2<>""),INDEX($C$2:$C$7,MATCH(TEXT(M$1,"hh:mm")&"_"&$L2,$D$2:$D$7,0)),"")

を右や下にコピー


F2は IF(COUNTIF($A2:$A$7,$A2)=1,A2,"") でもOKです。

I2も同様にできると思います。

ただし、列が変わるとM2等で使っている COLUMN()-12の-12の部分は調整が必要です(行も同じくROW()の部分)


もし、データの行数が変わるなら、

http://www2.odn.ne.jp/excel/waza/name.html#SEC20

http://office.microsoft.com/ja-jp/excel/HA011261151041.aspx

の例を参考に、可変範囲の名前を付けて、式を変えると楽になると思います。

もし、間違いや不明な点があれば、コメントで補足します。


f:id:kaiton:20081120134050j:image

id:my_redo_log

うーんすごい。セル参照と関数を駆使したExcelならではの方法ですね。

ただ、ちょっと難しすぎて私の実力では、いざ必要となったときに実行できない恐れが高いです・・・。

ピボットテーブルを応用する形など、もう少し簡単な方法は無いでしょうか・・・。

2008/11/20 17:29:37
  • id:Gay_Yahng
    「時間」 はどのくらいあるのでしょうか?また、ID1,2で必ず同じ時間のデータがありますか?
    「ID」は1と2だけですか?
  • id:my_redo_log
    「時間」は数百程度です。
    ID1,2に必ず同じ時間があるとは限りませんが、基本的にはあります。

    「ID」は1,2以外にも存在する場合でも適用可能な方法を教えていただきたいです。
  • id:kaiton
    エラー処理やデータ量が変わったときの対処をしているので、難しくなっていますが

    最終的な表を、基本的な式だけにすると

    L2=SMALL($J$2:$J$7,ROW()-1)
    J列の小さい方から順番にL列に上から表示
     ROW()は何行目かを表す関数 L2ではROW()-1=1のこと
     L2にJ列の1番目に小さい値を表示ということ L3には2番目に小さい値を表示

    M2=SMALL($G$2:$G$7,COLUMN()-12)
    G列の小さい方から順番にM2~横方向へ表示
     COLUMN()は何列目かを表す関数 M2ではCOLUMN()-12=1のこと
     M2にG列の1番目に小さい値を表示ということ N2に2番目 O2に3番目に小さい値を表示

    M3=INDEX($C$2:$C$7,MATCH(TEXT(M$1,"hh:mm")&"_"&$L2,$D$2:$D$7,0) を右や下にコピー
     M3では
      TEXT(M$1,"hh:mm")&"_"&$L2 は 10:10_1
      MATCH(TEXT(M$1,"hh:mm")&"_"&$L2,$D$2:$D$7,0) はD2:D7から 10:10_1 と一致する行数を求め
      INDEX($C$2:$C$7,MATCH(TEXT(M$1,"hh:mm")&"_"&$L2,$D$2:$D$7,0) はC2:C7の表から、上(MATCH関数)で検索した行の値を表示
    になります。


    ピボットに、SQL(アクセスのクエリ)のように、先頭とかがあればできるのでしょうが、
    最大値、最小値ではだめでしたね。
    それは、あいにく知りません。

    MSアクセスのクエリなら、テーブル:t1として
    TRANSFORM First(t1.state) AS stateの先頭
    SELECT t1.id
    FROM t1
    GROUP BY t1.id
    PIVOT t1.time;
    クロス集計でOKでしたけれど...
  • id:kaiton
    ★訂正★
    上の回答やコメントで
     M2= は M1= の
     M3= は M2= の間違いです。
    M1の式を、N1,O1へコピーしてください。
    同じように、M2の式を右や下へコピーしてください。
  • id:my_redo_log
    kaiton様
    フォローコメントありがとうございます。
    たしかにピボットの値フィールド算出方法は、値の種別が数値を想定したものしかなさそうですね・・・。
    残念。

    個人的には、SALINGERさまのやり方をアレンジして、より省力化する方向で検討しようかと考えております。

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません