エクセル：vlookup関数でこんなことをしたいのですが・・・

質問

よろしくお願いします。
vlookup関数のを使って次のような集計をしたいと思っています。

(1)【一月の集金】…挿入・名前の定義で「一月」とつけます。
山田太郎　3000
山田花子　2000
海山次郎　1500
・・・以下150名ほど。2月から12月までも「別シート」で同じように作られています。1月から12月の氏名は同じ人間もいれば違うのもあり、ばらばらです。氏名コード化等されていません。

(2)【集計】
　　　　　【1月】
（氏名）
　　A ...................B
1 山田太郎　＝vlokkup(A1,一月.2,false)　
2 海野五郎　・・・
3 ・・・・
合計　　（一月集金額合計）

同様に、集計欄の横に、2月、3月としていきます。
ここで、この例では、1月の山田花子が集計欄の氏名にもれています。当然、集計表で正確な合計がでません。ここでやりたいのが、「1月の表の山田花子が集計表でもれているよ」いうようなチェックです。たとえば、1月の表の山田花子に色がつくとか・・・

むずかしいことでしょうか？どうかご指導ください。
お待ちしています。人数が各月150人程度で12ヶ月のチェックですから、手作業チェックが大変なのと急いでいるので、お助けをお願いしたいのです。

どうかよろしくお願いいたします。

回答No.5

zabu-----n · ベストアンサー

>分かりやすい解説サイト
・・・は存じませんが、

月　　氏名　　金額

と、縦に伸びる形に表をまとめたら、その表内の任意のセルを
アクティブにした状態で、ツールバーの
データ ＞ ピボットテーブルと・・・
を選択します

ウイザードが表示されたら、そのままの設定で
次へ、次へと進み、完了させます

「ここにデータアイテムを・・・」

のシートが出来たら、
「ここに行のフィールドを・・・」に「氏名」を
「ここに列のフィールドを・・・」に「月」を
データアイテムを・・・のマスに金額をＤ＆Ｄします

細かなレイアウトは、この表では出来ないので、表をコピーして
別シートで編集すればいいと思います

>急いでいるので
とのことで、今回このような方法を提示しましたが

>氏名コード化等されていません。
すべきだったと思います

お礼

再度にわたってのご指導をありがとうございました。ピボットテーブルの機能をはじめて使うことになりました。お世話になりました。

補足

ありがとうございます。
＞各月の氏名と金額をコピーして別シートにまとめます
は「同シート」にまとめなさいという意味だったのでしょうか？
いずれにしてもやってみます。
他人の作った表の集計の指示を上司から受けた都合で、氏名コードのない、らばらのもので困っている状況でした＾＾：ありがとうございます。

回答No.6

mshr1962

「データ」「ピボットテーブル」を使ってみては如何ですか？
これなら１月に「山田花子」がなくても集計できます。
ただし、各表に月の列を追加する必要がありますが．．．
※表が別シートの場合でも、「複数のワークシート範囲」として追加すれば集計できます。

お礼

ありがとうございました。難しく考えすぎでした。便利な機能ピボットテーブルを使いこなせていませんでした。今回初挑戦でした。

回答No.4

imogasi

この質問の意味があいまい。
多分VBA向きの、ピヴォットテーブ向きの問題のような気がする。
しかし関数で、ということでしょうね。
（１）１－１２月の集金表は
氏名は（例山田太郎）は１月の表に２度以上出ないのか。
（２）１月と２月の集金表で、集金先の名前は１月にあり２月になし、両方にあり、２月にあり１月に無し、ががあるのでしょうね。

（３）集計表は
　　　　１月　　　２月　・・・
山田太郎　１００　１００　・・・
海野五郎　２００　　　　　・・・
河野武雄　　　　　３００　・・・
児玉花子　２００　　　　　・・・
・・・
のような感じですか。
＞1月の表の山田花子が集計表でもれているよ」いうようなチェックです
なぜ必要ですか。
むしろA列に１年の重複なき集金先名を作る質問をして、（関数では難しそう）
後は各列の各月の計数を、各月の集金表でVLOOKUPで検索し、見付からない場合は０をセットする式でよい。
各列の式が複写できるようにするのが難しそう。
ヒントとして=INDIRECT("Sheet"&COLUMN()+1 &"!A1")
のようなのがつかるように初めからシート名を工夫するとか。

お礼

ありがとうございました。今回はピボットテーブルでとのご指導を多数いただいたので、初挑戦してみました。親身になって考えていただき、心から感謝いたします。

補足

補足要求、ありがとうございます。質問の意味があいまいとのご指摘、ごもっともです。関数にこだわらず、結果がでれば、VBAでも大歓迎です。
(1)(2)ともおっしゃる通りのイメージです。

(3)のイメージもそのとおりです。但し、
「＞むしろA列に１年の重複なき集金先名を作る質問をして・・・」すみません。このあたりがあいまいでしたね。この辺の事情がすこしやっかいで説明省略していました。集計表のフォーマットが（上司により）出来上がってしまっており、A列の任意のセルに任意の氏名が入ってしまっています。つまり上司の意向でその氏名は動かせません。「漏れている人が各月これだけいました。」というようなものを別表でつくって、「その漏れているひとの別表金額の合計を元のデータに加算したら合計金額もあうでしょ」と上司にもっていき、説明した上でちゃんとした表をつくりたいのです。これでご理解いただけたでしょうか？ぜひ、引き続きのご指導をよろしくお願いいたします。

＞ヒントとして=INDIRECT("Sheet"&COLUMN()+1 &"!A1")すみません。この関数の意味はよくわかりません。あわせてお教えくださいませんでしょうか？

回答No.3

zabu-----n

単に縦横の集計が出来ればいいのであれば・・・

各月の氏名と金額をコピーして別シートにまとめます
その際、先頭列に「月」を入れておきます
あとはピボットテーブルで集計できます

補足

ご回答ありがとうございます。
よろしければ、ピボットテーブルについてもう少し教えてください。分かりやすい解説サイトはないでしょうか？

回答No.2

sero

集計表に氏名の記載漏れがないか確認したいと言う事なら、各月の集金表の氏名のセルに条件付書式で
「数式を」　で　=ISNA(MATCH(A1,集計,0))
としてパターンを設定すれば確認は可能です。
（上記式のA1は各月の氏名の一番上のセルを指します）

例の「一月」でいえば山田太郎のセルに上記の条件付書式を設定し、下方向（人数分）書式のみをコピーすれば良いでしょう。

質問の意味を取り違えていたならすみません。

お礼

ご回答ありがとうございました。
ひとえに私の理解不足によるのですが、
挑戦してみましたが、うまくできませんでした。
ごめんなさい。
またのご指導をお願いいたします。

回答No.1

papayuka

ようするに1月～12月までに出てくる人物を一覧にしたいって事かな？

同姓同名がいないなら、各シートのA列を一つのシートにコピーして、データ－フィルタ－フィルタオプションの設定で「重複するレコードは無視する」にチェックしてフィルタを掛ける。
そいつをコピーして別のシートにでも貼れば重複しないリストが出来るけど、、、

お礼

ご回答ありがとうございました。真っ先にお答えくださったのにお礼が遅くなり申し訳ありません。いろんな制約があり、重複しないリストを作成するだけでは難しい事情もありました。でも、ご回答いただいたことに心よりの感謝です。ありがとうございました。