該当する検索値が複数ある場合にすべてを出したい

質問

こんにちは、
紆余曲折を経て、とんでもないことになっているデータベースをエクセルで整理することになりました。
経緯は省いて、うまく表現できるか分かりませんが下記のようなデータを整理しなければなりません（データベースに変更は効かず唯一何とかなりそうなのがフォーマットはひどいですがExcelにデータを落としてくるというところです。）。

　　　 A　　　B 　　　 C　　　 D
1 日付　　　イベント名　　　 場所　　　 コメント（キャンセル、理由）
2 Tel # 　　　 コメント　（担当・アシスタント）
3 日付 　　　 イベント名 　　　 場所 　　　 コメント（キャンセル、理由）
4 Tel # 　　　 コメント　（担当・アシスタント）
5 日付 　　　 イベント名 　　　 場所 　　　 コメント（キャンセル、理由）
6 Tel # 　　　 コメント　（担当・アシスタント）

今、出そうとしているのは
1) 担当ごとのイベント名を日付順に

　　　 A 　　　 B 　　　 C 　　　 D
1　 担当
2
3　 日付 　　　 イベント名 　　　 場所 　　　 キャンセル（Yes/No)
4　 イベント１　　　 イベント１ 　　　 イベント１　　　 イベント１　（Yes/No)
5　 イベント２ 　　　イベント２ 　　　 イベント２　　　 イベント２　（Yes/No)
6...

2) 横長のカレンダーに担当ごとのスケジュールを１ページに

　　　 A B C D...
1　 担当 　　　 9/18 　　　 9/19 　　　 9/20...
2 　龍二 　　　 イベント 　　　イベント　　　 イベント　　　 イベント
　　　 　　　　　　イベント２ イベント２　　　 イベント２　　　 イベント２　
...
15　 竜太 　　　イベント イベント　　　 イベント　　　 イベント
16　 　　　　　　 イベント２ イベント２ 　　　イベント２ 　　　イベント２　
...

１つのイベントのデータが２段に渡って出されるので他のシートに１行に移してから作業したのですが、困っているのが担当・アシスタントの欄とキャンセルの欄はコメントなので決まったフォーマットでなく、条件を２つ、３つ適用しなければいけません。
例：　龍ちゃんメイン、こなっちアシ　や　龍二さん、龍君サポート・・・などなど（楽しそうな部署のようです。）　ちなみに龍ちゃんと龍君＝竜太、龍二さんと龍さん＝龍二と紛らわしいのもあります。キャンセル欄もキャンセル：雨、キャンセル：その他、無事終了、担当の返事待ち等々とっています。

以前回答されたMatch・Indexを使った配列関数でなんとかしようとしたのですがこの複数条件を使って担当とキャンセル状況を特定する部分がうまくいかずに困っています。 (http://okwave.jp/qa/q3345719.html / http://okwave.jp/qa/q4645331.html)

使用しているのはExcel 2010です。
どなたか力を貸してください。
よろしくお願いいたします。

回答No.4

MarcoRossiItaly · ベストアンサー

すこし分かりづらい言い方だったと思いますので、補足させていただきます。No.3の4.の説明で、「そのまま数式バーに「=$A5」を入力」と言っていますが、これは「1」などの数式バーに表示されている数値を数式で上書きするという意味です。

お礼

なるほど！
ピボットテーブルを他のシートにコピペ（非ピボットテーブル化？）する発想はなかったです。もう１つのシートに空白をつめるif関数を入れてみました。

担当ごとにコピペを繰り返すのは大変ですが今日は昨日（フィルタ機能で担当ごと＆数か月分を日付ごとにコピペ）より早く終わりそうです。
ありがとうございます。

ただ、最終的にはソースを入れたら余分なプロセスを省けるのが望ましいです。やはり、VBAを勉強すべきなのでしょうか・・・

目下、http://allabout.co.jp/gm/gc/297817/2/ にある方法をいじって該当の欄の返ってくる値をを”該当”ではなく日付にして、担当ごとの作業列と連番つきで作ってみているのですが、連番を新しい日付になるところからリセットする関数を作っているところで思考がとまっています。とりあえず、今日はコピペ作業をして帰宅します。

頭がパンクしてきたので中途半端なお礼になってしまってすみませんでした。。
大変勉強になる回答ありがとうございました。
もし、よろしかったらもう少しおつきあいしていただければ幸いです。

回答No.6

MarcoRossiItaly

お忙しいのですね。どうぞお体を大切になさってください。

＞まだまだ分からないところで質問をするかもしれませんがその際にはよろしくお願いします。

ご質問は、最初に投稿された質問文の内容が一応解決したら、締め切ってください。回答者としても、どれが解決してどれがそうでないのか、分かると嬉しいです。

新しいご質問は、（お礼／補足ではなく）別途、質問をお願いします。私に限らず、皆さん回答してくださると思います。前のご質問と関連した内容で、同じ説明を新たな投稿のたびに繰り返すのはたいへんということでしたら、このご質問のアドレスを記載して、「続きの質問です」と説明されてもよいでしょう。

お礼

ありがとうございます。
まだ、若干試行錯誤中ですがアイディアは固まりましたので一度解決させていただきます。

親切なアドバイスありがとうございました。

回答No.5

MarcoRossiItaly

＞ただ、最終的にはソースを入れたら余分なプロセスを省けるのが望ましいです。やはり、VBAを勉強すべきなのでしょうか・・・

全自動化したいということなら、覚えるなり、どっかからコードを拾ってくるなりしないといけないですね。

ただ、今後もずっと繰り返す作業なのか。また、毎回、少しずつ作業の内容が変化するということはないか。そういったことで、プログラムも価値が変わってくると思います。自動化するのにも、それなりに手間がかかりますからね。

＞……連番を新しい日付になるところからリセットする関数を作っているところで思考がとまっています。

参照されているページでは、連番を「=countif(D$3:d3,"該当")」という式で計算しているようですが、「該当」という文字列ではなく日付が入力されていたらどんな数式でできるか、ということですね。次式などでは、いかがですか。

E3　1
E4　=1+(d4="")*e3

E5セル以下は、E4をコピペするか、オートフィルします。

お礼

返事が送れて申し訳ありませんでした。
連日の残業で体調を崩して寝込んでいました。

この作業を毎日帰る間際、（終業５分前のデータで）にしなければいけないのと、常に変更がなされているので何とかしたいのです。できたら完全に全自動化をしようとしています。
連番に関して説明不足ですみませんでした。
１枚のシート（回答１＆２のsheet2)に担当１０名強とそれぞれ連番をいれる列を下のように作ってみようということです。そこから実際に欲しい表の方に余分な連番スペースを用意してということです。説明不足で申し訳ありませんでした。
でんわ9 担当4
1 担当１ 連番1 担当２ 連番2・・・
2 9/21 1
3 9/21 2
4 (空白） (空白） 9/21 1
5 9/23 1
6 (空白） (空白） 9/22 1

と、ここまで書いていて、担当の日付はすでにあるので”該当”を連番に入れ替えたほうが軽くなるのじゃ・・・簡単にsumproductやcountifsでできるじゃない（アレ私なにを考えていたのかしら・・・）
たくさんのアドバイスありがとうございます。大変勉強になりました。
仕事に戻ったら早速思いついたことをトライしてみます。

まだまだ分からないところで質問をするかもしれませんがその際にはよろしくお願いします。

回答No.3

MarcoRossiItaly

＞ピボットテーブルはよく使っていて、日付と担当ごとのイベント数を出すのに使っています。ただイベント名をすべて出せるとは思わず試しませんでした。

そうですね。確かに「横長のカレンダー」というのを一発でスパッと作ることはできなさそうです。けれど、利用の仕方はいろいろ考えられると思います。例えば、次のような手順はいかがですか。

1.
ピボットテーブル上にカーソルを置いているときに出現する、「ピボットテーブルのフィールドリスト」（ツールバー）で、「レポートフィルタ」ボックス（Excel2007以後）あるいは「ページエリア」（Excel2003以前）に「担当」をドラッグ・アンド・ドロップ。「行ラベル」ボックスあるいは「行エリア」に「イベント名」をD&D。「列ラベル」ボックスあるいは「列エリア」に「日付」をD&D。「値」ボックスあるいは「データエリア」にもう一つ「イベント名」をD&D。

2.
できた表をコピーして別シートに貼り付け。例えば次のような感じになったとします。

　　　　　　　A　　　　　　　　　 B　　　　　 C　　　　D
1　　　　　（担当）　　　　　　 龍二
2
3　データの個数 / イベント名　　列ラベル
4　行ラベル　　　　　　　　　　 日付1　　　 日付2　　総計
5　イベント名1　　　　　　　　　　1　　　　　　　　　　1
6　イベント名2　　　　　　　　　　　　　　　　1　　　　1
7　総計　　　　　　　　　　　　　 1　　　　　 1　　　　2

3.
B5:C6のセル範囲を選択し、キーボードのF5キーを押す（ジャンプ）。「セル選択」ボタンを押し（Alt+S）、「定数」を指定し（O）、OKする（Enter）。

4.
数値が入力されたセルのみ選択された状態になっているので、そのまま数式バーに「=$A5」を入力し、Ctrl+Enter。

お望みの表に少しは近いものになりましたか？

回答No.2

MarcoRossiItaly

＞特に２つ目の横長のカレンダー形式のを作るときには手作業が多く時間がかかっています。

オートフィルタを取り付けた表に対して、ピボットテーブルです。あまり使ったことがないと、とっつきにくいと感じるかもしれませんが、いじり回していると、何となくでき上がります。慣れればサッと作れます。

手作業でピボットテーブルを作るのさえ面倒という場合は、マクロですね。ボタン一つで毎回同じ作業をしてくれるので、同じフォーマットで今後もずっと同じ作業を繰り返さないといけない場合は、使ってみるのもよいでしょう。

シートの前提条件の設定と、コードの検証に時間がかかるので、どなたか親切な回答者さんが現れないかしら…（笑）まあ次のコードとかを標準モジュールに貼って実行。

sub move_cells_4_pvt()

'Sheet2をクリック後に実行

　dim i as long
　worksheets("sheet1").range("a1:d1").entirecolumn.copy
　worksheets("sheet2").paste destination:=range("a1")
　with range("a1")
　　do while .offset(i,0)<>""
　　　.offset(i+1,0).resize(1,2).cut .offset(i,4)
　　　.offset(i+1,0).entirerow.delete
　　　i=i+1
　　loop
　end with

　dim pvt as pivottable
　dim data as range
　set data=activesheet.range("a1").currentregion
　sheets.add
　set pvt=activeworkbook.pivotcaches.add(sourcetype:=xldatabase,sourcedata:=data).createpivottable(tabledestination:=range("a3"))

end sub

お礼

ありがとうございます。
ピボットテーブルはよく使っていて、日付と担当ごとのイベント数を出すのに使っています。ただイベント名をすべて出せるとは思わず試しませんでした。
現在、いろいろ試しているのですがうまくいきません。
もう少しアドバイスをいただけませんか？

回答No.1

MarcoRossiItaly

手法はいろいろあり得るでしょうけど、お手軽な方法で行きましょう。まずは日付の行と電話番号の行が交互に挟まっているので、これを1行に並べ直しましょう。

質問文の最上部の表が載っているシート名を「Sheet1」とします。別シートに、次式を入力。

A1　=offset(sheet1!A$1,2*row()-2,0)
E1　=offset(sheet1!A$1,2*row()-1,0)

A1セルとE1セルそれぞれにカーソルを置いて、右下隅の角を右方向にドラッグすることにより、A1:D1とE1:F1のセル範囲を埋めます。次いで、A1:F1の範囲を選択し、2行目以下にコピペあるいはオートフィルしてください。

並べ替えが終わったら、シート全体をコピーして値のみ貼り付けるなどしておきます。後で更に行の並べ替えとかする場合もあるでしょうから。

できた表に、リボンまたはメニューバーの「データ」にある「フィルタ」または「オートフィルタ」を取り付けると、特定の担当者さんのみ表示させたりできます。複数の条件による絞り込みも勿論できます。日付その他のデータで並べ替えたりもできます。

お礼

早速の回答ありがとうございます。
やはり、１列にしてからのオートフィルタが手軽ですね。実際昨日はそれで行いました。
ただ、やはり毎日オートフィルタを操作するのを省けるならば省きたい（特に２つ目の横長のカレンダー形式のを作るときには手作業が多く時間がかかっています。）
実際のデータは担当スタッフが10名近く、イベントも300件をゆうに超えてしまうので、Sheet１に元のデータを上書きするだけで必要なものを出せるようにできないでしょうか？

補足

すでに私もシートを１つ作ってしまっているので担当や、キャンセル状況の有無の条件もそこに入れてしまうのがいいのかなと思い、Sheet１には元のデータベースからのデータをコピペできるようにして、Sheet２には

A1 日付 B1 イベント名　C1 場所　D1 コメント（キャンセル、理由） E1 2Tel #　　F1 コメント　（担当・アシスタント） G1 担当（コメントフィルター済み） H1 キャンセル（コメントフィルター済み）

となるようにして再度挑戦中です。
１人で担当できる１日のイベント数はどんなにがんばっても１５件くらいだそうで（過去データに例外ありでしたが無視）、なんとか形にしたいと思います。
アドバイスおまちしております。