エクセルでデータ抽出→並べ替え

質問

エクセルで 　　Ａ列 　 Ｂ列　 　Ｃ列 　　　Ｄ列　 Ｅ列
　　　　　　　４月１日 9:00 山田太郎 　 9:15 　 佐藤仁
　４月１日 10:05 佐藤聡 10:00 鈴木正夫
　４月１日 11:15 高橋二郎 11:00 山田勇
　　　　　　　４月２日 9：05 渡邊正志 9:15 佐々木正夫
　　　　　　　４月２日 10:00 山田太郎 　10:15 佐藤仁　
　　　　　　　４月２日 11:02 佐藤仁　　 11:00　高橋二郎

という複数の営業マンの訪問予定表があります。この「山田太郎」さんに
月間の訪問予定表を発行したいと思っています。
※発行対象は全員です。

関数でもマクロでも結構ですが、ご教授いただけると助かります。

回答No.8

kagakusuki · ベストアンサー

抽出条件となる年と月を指定すれば、全員分の訪問予定表が、自動的に表示されるという方法は如何でしょうか？
（但し、未記入の訪問予定表のフォーマットのみのページも表示されてしまいますので、印刷する際には、何ページ目までを印刷するのかを指定する必要はあります）

　今仮に、御質問文にある様な元データの表があるシートがSheet1であり、Sheet3のA列～D列を作業列として使用して、Sheet2に、その月に訪問する予定のある、各顧客向けの訪問予定表を、縦に並べて表示するものとします。
　又、訪問予定表のレイアウトが不明なため、仮に、各訪問予定表のA列の各印刷ページ毎の1行目が年月欄で、同じくA列の各印刷ページ毎の5行目に、各顧客の氏名を表示し、A列の各印刷ページ毎の10行目以下に、各顧客毎のその月の訪問予定日時を表示するものとします。

　まず、Sheet3のB2セルに次の関数を入力して下さい。

=IF(AND(ISNUMBER(1/DAY(INDEX(Sheet1!$A:$A,INT((ROW()-ROW(B$2))/2)+ROW(Sheet1!$A$1)+1))),ISNUMBER(INDEX(Sheet1!$B:$E,INT((ROW()-ROW(B$2))/2)+ROW(Sheet1!$A$1)+1,MOD(ROW()-ROW(B$2),2)*3+1))),INT(INDEX(Sheet1!$A:$A,INT((ROW()-ROW(B$2))/2)+ROW(Sheet1!$A$1)+1))+MOD(INDEX(Sheet1!$B:$E,INT((ROW()-ROW(B$2))/2)+ROW(Sheet1!$A$1)+1,MOD(ROW()-ROW(B$2),2)*3+1),1),"")

　次に、Sheet3のA2セルに次の関数を入力して下さい。

=IF(ISNUMBER($B2),COUNTIF($B:$B,"<"&$B2)+COUNTIF($B$1:$B2,$B2),"")

　次に、Sheet3のC2セルに次の関数を入力して下さい。

=IF(ISERROR(1/(INDEX(Sheet1!$C:$F,INT((MATCH(ROWS($2:2),$A:$A,0)-ROW($A$2))/2)+ROW(Sheet1!$A$1)+1,MOD(MATCH(ROWS($2:2),$A:$A,0)-ROW($A$2),2)*3+1)<>"")/(TEXT(SMALL($B:$B,ROWS($2:2)),"yyyy/m")=TEXT(Sheet2!$A$1,"yyyy/m"))),"",INDEX(Sheet1!$C:$F,INT((MATCH(ROWS($2:2),$A:$A,0)-ROW($A$2))/2)+ROW(Sheet1!$A$1)+1,MOD(MATCH(ROWS($2:2),$A:$A,0)-ROW($A$2),2)*3+1)&"◆"&COUNTIF(C$1:C1,INDEX(Sheet1!$C:$F,INT((MATCH(ROWS($2:2),$A:$A,0)-ROW($A$2))/2)+ROW(Sheet1!$A$1)+1,MOD(MATCH(ROWS($2:2),$A:$A,0)-ROW($A$2),2)*3+1)&"◆*")+1)

　次に、Sheet3のD2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNTIF($C:$C,"*?◆1"),"",SUBSTITUTE(VLOOKUP("*?◆1",IF(ROWS($2:2)=1,$C$1,INDEX($C:$C,MATCH(D1&"◆1",$C:$C,0)+1)):INDEX($C:$C,ROWS($C:$C)),1,FALSE),"◆1",))

　次に、Sheet3のA2～C2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。
　次に、Sheet3のD2セルをコピーして、Sheet3のD3以下に、（顧客の人数を上回るのに十分な行数となるまで）貼り付けて下さい。

　次に、Sheet2のA1セルの書式設定の表示形式を[日付]の

2001年3月

として下さい。
　次に、Sheet2のB2セルに「訪問予定表」、B5セルに「様」（鉤括弧は無要）、A9セルに「訪問予定日時」と入力して下さい。
　次に、Sheet2の印刷時における1ページ目に、各種の定型文等を入力して、1ページ目のレイアウトを作成して下さい。（顧客名や訪問日時は除く）
　次に、Sheet2のA5セルに、次の関数を入力して下さい。

=IF(COUNTIF($B$1:INDEX($B:$B,ROW()),"様")>COUNTIF(Sheet3!$D:$D,"*?"),"",INDEX(Sheet3!$D:$D,COUNTIF($B$1:INDEX($B:$B,ROW()),"様")+ROW(Sheet3!$D$1)))

　次に、Sheet2の印刷時における1ページ目の行範囲である5行目（定型文ではないセルが含まれている最初の行）～15行目の範囲をコピーして、　Sheet2の印刷時における2ページ目の先頭行に貼り付けて下さい。
　次に、Sheet2のA10セルに、次の関数を入力して下さい。（これは「訪問予定日時」と入力されているのが、A9セルとA20セルの場合の関数です）

=IF(MOD(ROW()-ROW($A$9),ROW($A$20)-ROW($A$9))>COUNTIF(Sheet3!$C:$C,INDEX(Sheet3!$D:$D,COUNTIF($B$1:INDEX($B:$B,ROW()-1),"様")+ROW(Sheet3!$D$1))&"◆*?"),"",SMALL(Sheet3!$B:$B,MATCH(INDEX(Sheet3!$D:$D,COUNTIF($B$1:INDEX($B:$B,ROW()-1),"様")+ROW(Sheet3!$D$1))&"◆"&MOD(ROW()-ROW($A$9),ROW($A$20)-ROW($A$9)),Sheet3!$C:$C,0)-ROW(Sheet3!$C$1)))

　次に、Sheet2のA10セルの書式設定の表示形式を[ユーザー定義]の

yyyy"年"m"月"d"日 "aaaa h"時"mm"分"

として下さい。
　次に、Sheet2のA10セルをコピーして、Sheet2のA10以下と、A21以下のそれぞれに、訪問予定日時欄の行数（空欄も含む）の分だけ貼り付けて下さい。

　次に、Sheet2の印刷時における2ページ目の行範囲をコピーして、　Sheet2の印刷時における3ページ目の先頭行以下に貼り付けて下さい。
　次に、Sheet2の印刷時における各ページの末尾に、改ページを挿入して下さい。
　次に、印刷の「ページ設定」の[シート]タブを開いて、1行目～4行目を印刷時の「タイトル行」に設定して下さい。

　以上で準備は完了で、後はSheet2のA1セルに、

2013年11月

等と入力する事で、データを抽出する月（要年数）を指定しますと、全員分の月間の訪問予定表が表示されます。（印刷時には、1～4行目のタイトル行の内容が、全てのページの冒頭部分に挿入されます）

お礼

素晴らしいです。希望していたことができました。本当にありがとうございました。

回答No.9

KURUMITO

元のデータがシート1に有るとしてシート2には入力されている営業マンのすべての方について一気に訪問予定を表にすることができます。配列数式などを使用しないでデータの数が多くなっても計算に負担のかからない方法で、しかもマクロなどではデータの新たな入力のたびにマクロの操作が必要ですがその必要もなく一気に処理できる方法です。
シート1では後で気が付いたのですが本文とお示しの図では様の列が有るかないかで違っていますね。ここではお示しの図のようにシート1にデータが入力されているとします。
Ｉ，Ｊ，Ｋ列に作業列を作って対応します。
Ｉ2セルには次の式を入力します。

=IF(MOD(ROW(A1)+2,2)=1,IF(INDEX(C:C,ROUNDUP((ROW(A1)+2)/2,0))="","",INDEX(C:C,ROUNDUP((ROW(A1)+2)/2,0))),IF(INDEX(F:F,ROUNDUP((ROW(A1)+2)/2,0))="","",INDEX(F:F,ROUNDUP((ROW(A1)+2)/2,0))))

Ｊ2セルには次の式を入力します。

=IF(I2="","",IF(COUNTIF(I$2:I2,I2)=1,ROUNDDOWN(MAX(J$1:J1),-3)+1000,INDEX(J:J,MATCH(I2,I$1:I1,0))+COUNTIF(I$1:I1,I2)))

Ｋ2セルには次の式を入力します。

=IF(MOD(ROW(A1)+2,2)=1,IF(INDEX(C:C,ROUNDUP((ROW(A1)+2)/2,0))="","",INDEX(A:A,ROUNDUP((ROW(A1)+2)/2,0))+INDEX(B:B,ROUNDUP((ROW(A1)+2)/2,0))),IF(INDEX(F:F,ROUNDUP((ROW(A1)+2)/2,0))="","",INDEX(A:A,ROUNDUP((ROW(A1)+2)/2,0))+INDEX(E:E,ROUNDUP((ROW(A1)+2)/2,0))))

Ｉ2セルからＫ2セルまでを範囲として選択してそれらの式を下方にドラッグコピーします。
作業列が目障りでしたらそれらの列を選択して右クリックし、「非表示」を選択すればよいでしょう。
次にシート2での作業に移ります。
例えばＡ2セルには次の式を入力して右横方向にドラッグコピーします。

=IF(COLUMN(A1)>INT(MAX(Sheet1!$J:$J)/1000)*2,"",IF(MOD(COLUMN(A1),2)=1,INDEX(Sheet1!$I:$I,MATCH(ROUNDUP(COLUMN(A1)/2,0)*1000,Sheet1!$J:$J,0)),"様訪問予定"))

Ａ3セルには次の式を入力して右横方向にドラッグコピーします。

=IF(A2="","",IF(MOD(COLUMN(A1),2)=1,"日付","時刻"))

最後にＡ4セルには次の式を入力して右横方向にドラッグコピーしたのちに下方にもドラッグコピーします。

=IFERROR(IF(MOD(COLUMN(A1),2)=1,TEXT(ROUNDDOWN(INDEX(Sheet1!$K:$K,MATCH(ROUNDUP(COLUMN(A1)/2,0)*1000+ROW(A1)-1,Sheet1!$J:$J,0)),0),"m/d"),TEXT(MOD(INDEX(Sheet1!$K:$K,MATCH(ROUNDUP(COLUMN(A1)/2,0)*1000+ROW(A1)-1,Sheet1!$J:$J,0)),1),"h:mm")),"")

これで完成ですが氏名ごとに間に列を入れることもできます。例えばＣ列を選択してから右クリックして「挿入」をクリックすればよいでしょう。勿論、ある方について2つの列を選択してからコピーし、他のシートでは「形式を選択して貼り付け」で「値」にチェックして貼り付けして利用することもできますね。

お礼

ありがとうございます。ひとつひとつ試してみます。助かります

回答No.7

MarcoRossiItaly

既に幾つか回答が寄せられているとおり、フィルタ（あるいはフィルタオプション（詳細設定）など）によって、「山田太郎」さんのみに絞り込むのがお手軽だと思います。

ただ、もしも、「山田太郎」さんはお客さんなので、多少は気の利いた書式にしてから手渡したいといった事情があるなら、データ貼り付け用シートと、印刷用シートを別に用意しておく方式をお勧めします。つまり、

(1)フィルタで「山田太郎」さんのみ抽出
(2)抽出された表をコピーして、データ貼り付け用シートの特定の位置に貼り付け
(3)元々数式が入力されている印刷用シートにデータが自動的に表示されているので、それを紙、PDF などに印刷

というふうにします。

印刷用シートのどこかのセルに、「=データ貼り付け用シート!a1」といった数式を始めから入力しておきます。そうして、データ貼り付け用シートにあるいろいろなセルから参照させます。つまり表示する項目を選べるということになりますね。同じ印刷用シートの中から参照する箇所については、単に「=a1」という具合です。必要な場合は、簡単な計算を行った上で印刷用シートに表示させるということもできます。

セルの配置とか書式といった文書のデザイン関係も、印刷用シートに対してのみ、好きなだけ加工を施しておけばいいでしょう。例えば「様」といった文字も印刷用シートで表示させればいいだけなので、元データの表にまで「様」を載せておく必要はないですね。

あとはいろんな人のデータに貼り替えて、その度に印刷していくだけ。

パッと見、質問文の図は、C 列がお客さんで、E 列が担当者さんとかなのかな？とも思ったのですが、実際は、E 列もお客さんなんですかね？そうであれば、できればお客さんはお客さんで 1 列にまとめたほうが、フィルタでもピボットテーブルでも、効率的だと思います。

その際、同じ日時のレコード（行）が複数存在することになっても、同一人物が 2 行にはならなさそうなので、不都合は特に起きないと思います。心配なら、通し番号の列を追加してもいいです。

また、担当者さんの列が別に存在する場合は、その列で絞り込むことによって、お客さん用ではなく担当者さん用の予定表を作ることも、上と同様にしてできますね。

お礼

ありがとうございます。できればフィルタ以外でできたらと思っておりました。

回答No.6

tom04

こんばんは！
VBAでの一例です。

Sheet1には1月分のデータだけあるとします。

Alt+F11キー　→　メニュー　→　挿入　→　「標準モジュール」を選択　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt+F8キー　→　マクロ　→　マクロ実行です）

Sub 表示() 'この行から
Dim i As Long, j As Long, str As String
Dim wS1 As Worksheet, wS2 As Worksheet
Set wS1 = Worksheets("Sheet1")
Set wS2 = Worksheets("Sheet2")

str = Application.InputBox("営業マン名を入力")
wS2.Cells.ClearContents
With wS2.Cells(1, 1)
.Value = str
.Offset(1) = "日付"
.Offset(1, 1) = "時刻"
End With

For i = 2 To wS1.Cells(Rows.Count, 1).End(xlUp).Row
For j = 3 To wS1.Cells(i, Columns.Count).End(xlToLeft).Column Step 2
If wS1.Cells(i, j) <> "" And wS1.Cells(i, j) = str Then
With wS2.Cells(Rows.Count, 1).End(xlUp).Offset(1)
.Value = wS1.Cells(i, 1)
.NumberFormatLocal = "m/d aaa"
With .Offset(, 1)
.Value = wS1.Cells(i, j - 1)
.NumberFormatLocal = "h:mm"
End With
End With
End If
Next j
Next i
End Sub 'この行まで

こんな感じではどうでしょうか？m(_ _)m

お礼

ありがとうございます。先ずは関数でやってみて、次にＶＢＡで試してみたいと思います。

回答No.5

Prome_Lin

すみません。
追伸です。

「～.vbs」として保存したファイルと、「Test.xls」を同じフォルダに入れて「～.vbs」ファイルをダブルクリックすれば、実行できます。

回答No.4

Prome_Lin

「Test.xls」というファイルの第１シートに

４月１日 9:00 山田太郎 9:15 佐藤仁
４月１日 10:05 佐藤聡 10:00 鈴木正夫
４月１日 11:15 高橋二郎 11:00 山田勇
４月２日 9：05 渡邊正志 9:15 佐々木正夫
４月２日 10:00 山田太郎 10:15 佐藤仁
４月２日 11:02 佐藤仁 11:00 高橋二郎

というデータがあります。

このファイルの第２シートには、「A」列に

佐々木正夫
佐藤聡
佐藤仁
鈴木正夫
高橋二郎
山田勇
山田太郎
渡邊正志

があります。
この条件下で、以下のテキストを「～.vbs」として保存してください。

第２シートのお名前の横に、それぞれの方の予定が記入され上書き保存されます。

Option Explicit
Dim b, c, i, j, k, m, n(), p(), u, v, w, x, y, z

'ファイルを扱えたり、フォルダを取得するためのSet
Set u = CreateObject("Scripting.FileSystemObject")

'自分自身のいるフォルダの取得
Set v = u.GetFolder(".")

'Excelを使えるようにする
Set w = CreateObject("Excel.Application")

'ファイルを開く
Set x = w.Workbooks.Open(v & "\Test.xls")

'Sheetを読み込む
Set y = x.Worksheets(1)
Set z = x.Worksheets(2)

'最後の保存時に、「OK」を押さなくても自動的に保存
w.Application.DisplayAlerts = False

'「Sheet2」の人数をチェック
m = z.Range("A1").End(-4121).Row -1

ReDim n(m), p(m)
For i = 0 to m
p(i) = CInt(0)
Next

'お名前を配列変数に取り込む
For i = 0 to m
n(i) = Trim(z.Cells(i + 1, 1).Value)
Next

'「Sheet1」の方の最終行を調べる
c = y.Range("A1").End(-4121).Row

'「Sheet2」で取得したお名前と一致する方を調べ、それに対する処理
For i = 0 to m
For j = 1 to c
For k = 1 to 2
b = Trim(y.Cells(j, k * 2 + 1).Value)
If b = n(i) Then
p(i) = p(i) + 1
z.Cells(i + 1, p(i) * 2).Value = y.Cells(j, 1).Value
z.Cells(i + 1, p(i) * 2 + 1).Value = y.Cells(j, k * 2).Value
End If
Next
Next
Next

'保存やクローズ、終了処理
x.SaveAs(v & "\Test.xls")
x.Close
w.Quit
Set z = Nothing
Set y = Nothing
Set x = Nothing
Set w = Nothing
Set v = Nothing
Set u = Nothing

MsgBox("Finished")

回答No.3

Nouble

A列は日付、
B列は開始時間、
C列は担当者、
D列は終了時間、
E列は面談者、
ですよね？

C列を基準に抽出し、
不要な列を非表示にすればいい
のではないですか？

お礼

ありがとうございます。関数やＶＢＡをつかってワンクリックで行いたいと思いました

回答No.2

masaruyone

範囲を指定してから、バーのデータのオートフイルをクリックし
矢印のマークから抽出する名前をクリックすればその方だけの
表が出ます。
ファイルに項目名（時間・名前・・・）があった方がよいかもしれません。

回答No.1

keithin

シート１にご質問に例示された通りのレイアウトで記入されているとして。

シート２のＢ１に「山田　太郎」を記入
シート２のＢ２に
=IF(ROW(B1)>COUNTIF(Sheet1!$C:$F,B$1),"",SMALL(IF(Sheet1!$C$2:$F$999=B$1,Sheet1!$A$2:$A$999+Sheet1!$B$2:$E$999),ROW(B1)))
と記入、必ずコントロールキーとシフトキーを押しながらＥｎｔｅｒで入力、セルの書式設定の表示形式のユーザー定義で
yyyy/mm/dd hh:mm
の設定を付けておき、下向けにコピーすると山田太郎の年月日時分を一覧します。

必要に応じてＢ列をコピー、例えばＣ列に貼り付けてＣ１に「浅田　朝子」を記入すると、浅田朝子の予定一覧が現れます。

＞発行対象は全員です。

顧客一覧は、別途顧客データから作成します。
そういうのも用意できてないときは、予定の名前列を別にコピーして縦に並べ、「フィルタオプションの設定」で一意のリストを作成します。
http://www.relief.jp/itnote/archives/001525.php

＃ご利用のエクセルのバージョンがご相談に書かれていませんが、Excel2007以降が使えれば「重複の削除」で簡単に顧客一覧を作成できます。