Ｅｘｃｅｌの表の変換

質問

Ｅｘｃｅｌで作った、業務の分担表があります。
分担表は縦軸は日付、横軸は８項目の業務項目で、この交点のセルに担当者の氏名が記入されています。
今回、この表から、縦軸は日付で同じですが、横軸に担当者を列記して
その交点に業務内容が記入された表を作りたいのですが、効率良く作る方法があれば教えて下さい。

回答No.4

noname#52504 · ベストアンサー

#2です。
レスが大変遅くなってしまいましたが、何点か補足を。

１．
>数件データが抜けています。
ということですが、元表の氏名について
スペースの有無や字体が異なるものがあるのかもしれません。

例えば、「渡邊 太郎」「渡邉 太郎」は区別されますし、
「渡辺 太郎」「渡辺　太郎」「渡辺太郎」「渡辺太郎 」も区別されます。

条件付書式を使って[氏名の一覧にない氏名]に色をつけるようにすれば、
表記の異なる氏名を見つけやすくなります。

また毎月作成するとのことですが、
元表について入力規則を設定し、
氏名をドロップダウンリストから選んで入力するようにすれば、
表記違いを未然に防ぐことができます。

２．
もし
「その日担当する業務が無い人について#N/Aが返る」
ということでしたら…

A案：数式中で""を返すようにする
　さらに長大な数式になってしまいますが、
=IF(ISERROR(MATCH(B$1,INDEX(Sheet1!$B$2:$I$21,MATCH($A2,Sheet1!$A$2:$A$16,0),),0)),"",INDEX(Sheet1!$B$1:$I$1,MATCH(B$1,INDEX(Sheet1!$B$2:$I$21,MATCH($A2,Sheet1!$A$2:$A$16,0),),0)))
　など。
　Excel2007でしたら、IFERROR関数をかぶせる方法もあります。

B案：条件付書式で白フォントになるようにする
　書式＞条件付書式　で、
　数式が：ISERROR(B1) 書式：フォント色を白
　などとする。

C案：ページ設定で印刷しないようにする（Excel2002以降)
　ファイル＞ページ設定＞シート＞セルのエラー　で、
　<空白>を選択する

といった対処方法があります。

３．
元の表の日付の並びと変換後の表の日付の並びが「まったく同じ」であれば、
対応する日付の行を探す部分は不要です。
おそらく、単に
　=INDEX(Sheet1!$B$1:$I$1,MATCH(B$1,Sheet1!$B2:$I2,0))
としてもうまくいくはずです。

以上、ご参考まで。

お礼

丁寧なご回答ありがとうございます。
データーが移行しなかった、主な原因は貴殿がご指摘の１項がほとんどでした。
アドバイスにありますように、リスト表示にして選択するようにしました。
他のアドバイスも参考になります。
ご親切にありがとうございました。

回答No.3

imogasi

これから設計するのでなく、質問の表が、できているとすると
VBAを使わないと、難しいと思う。
ーー
関数では
=INDEX(Sheet1!範囲、行、列）の行・列を決めるとき、MATCH関数を使う場合かと思うが、
（１）２つ以上の列から探してこれない
（２）２つめ以下の出現を捉えられないので、難しくなりそうです。
ーー
VBAで
簡単な例を挙げておきます。
例データ　Sheet1　A-D列
- 清掃 陳列 補充
2007/10/1 山田 梅村 海原
2007/10/2 大木 木村 鈴木
2007/10/3 梅村 木村 大木
2007/10/4 木村 大木 山田
2007/10/5 藤原 近藤 梅村
VBAコード　標準モジュールに
Sub test01()
d = Range("A8").End(xlUp).Row
MsgBox d
For m = 2 To 4
For j = 2 To 4
For i = 2 To d

If Cells(i, j) = Cells(9, m) Then
Cells(i + 8, m) = Cells(1, j)
End If
Next i
Next j
Next m
End Sub
実行結果
Sheet1の
A9：D14に
- 山田 大木 梅村
2007/10/1 清掃 陳列
2007/10/2 清掃
2007/10/3 補充 清掃
2007/10/4 補充 陳列
2007/10/5 補充
ーー
３人分にカットしている。ｍ、ｊの４を人数分＋１に修正すれば拡張できる。
日にちも１０日できっている。
元データは増えるままでよい。
完成データの左上隅が変わるので修正要。
これらの修正箇所が判る力があれば、２００人までなら我慢できる処理時間で完成するでしょう。
上の中に都合の悪い場合があると、（例１日に２業務など）相当考え直さないといけない。

お礼

早速の回答ありがとうございます。
まず、Ｎｏ２の方の関数でやってみようと思います。
はじめのデータの表の作り方も新しく設計するとして、
シロウト考えですが、ピボットテーブルのようなものでは無理なのでしょうか。
よろしくお願いします。

回答No.2

noname#52504

元の表がSheet1にあり、
A2から下方に日付、B1から右方に業務内容が入っているとします。
同様に、変換した表を作成するシートについて、
A2から下方に日付、B1から右方に氏名があらかじめ入力されているものとします。

B2セル
　=INDEX(Sheet1!$B$1:$I$1,MATCH(B$1,INDEX(Sheet1!$B$2:$I$999,MATCH($A2,Sheet1!$A$2:$A$999,0),),0))
として下方・右方にフィルすればご要望の表ができあがります。
(Excel2003で動作確認済)

※
１．MATCH($A2,Sheet1!$A$2:$A$999,0)　で対応する日付の位置を探す。
２．INDEX(Sheet1!$B$2:$I$999,MATCH(～),)　で対応する日付の行を取得する。
３．MATCH(B$1,INDEX(～),0)　で対応する氏名の位置を探す。
４．INDEX(Sheet1!$B$1:$I$1,MATCH(～))　で対応する氏名を取得する。

ただし
「同じ日に同じ人が複数の業務を担当する」
「同じ日に同じ業務を複数の人が担当する」
「同じ日に複数の案件がある(同じ日付が複数ある)」
といったことが無い、というのが大前提です。

お礼

やってみましたら、大筋うまくできましたが、数件データが抜けています。
大部分のデータは移行できていますので、原因を探ってみます。
こんな複雑な関数は私には、とてもできません。
ありがとうございました。

回答No.1

noname#40742

いろいろ方法がありますが、１件１件いれていく方法です。

業務数が１０件くらいでしたら、縦一列に列挙します。
列挙したそのセル全体に名前をつけます。
入力したセル全体をなぞってハイライトさせ、
挿入＞名前＞定義
名前のところにgyoumuとでもつけます。

次に、業務を入力する範囲全体をなぞってハイライトさせ、
データ＞入力規則＞設定
入力値の種類：リスト
元の値：=gyoumu
とします。

するとセルを選択したら▼のプルダウンメニューがあらわれ
業務入力が軽減されます。
この方法だと別シートになっても入力可能です。

お礼

早速の回答ありがとうございます。
毎月作成するので、関数かマクロのようなものでできたら良いとのですが・・・