人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

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


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

●質問者: my_redo_log
●カテゴリ:コンピュータ インターネット
✍キーワード:Excel State データ フォーマット ボット
○ 状態 :終了
└ 回答数 : 2/2件

▽最新の回答へ

1 ● SALINGER
●35ポイント

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


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

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

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


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

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

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


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

◎質問者からの返答

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


2 ● kaiton
●35ポイント

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


↓は、その行を一意に表すキーのデータとします。(時刻と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

◎質問者からの返答

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

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

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

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ