別シートにあるその月の最大値を取得したい。

質問

次のような処理を考えています。
　　Ａ　　　　Ｂ　　　　　（シート１）
2001/1/1　10000
2001/1/2　13000
　　：　　　：
2001/1/31　25000
2001/2/1　28000
　　：　　　：
Ｂ列は累計値です。（つまり、月末日がその月の最大値です。）
ここで、シート２からシート１の毎月末の値を取得したいと思っています。
以下は例です。
　　Ａ　　　Ｂ　　　Ｃ
2001年　　1月　　　2月　　・・・　　　（シート２）
最大値　25000　　???
つまり、Ａ１の年とＢ１の月と合致するシート１の月末日の値を、Ｂ２に取得
したいのです。（この例では2001/1/31の値が取得したい。）

VLOOKUPとMAXを組み合わせてなんとか、と思いましたが、いい方法が
思い浮かびません。何か方法はあるでしょうか？
（ワークシート関数だけで解決できるでしょうか？）

回答No.5

mshr1962 · ベストアンサー

#4です。
＞やはりあらかじめ大きな数値を指定しておくしかありませんか？
SUMPRODUCT関数は列全体・行全体は指定できません。
$A$2:$A$65535 のような全体から1行だけ除いた範囲にするしかありません。

※Office2003までは同じです。来年発売予定の2007では確認してません。

お礼

よくわかりました。勉強になりました。

ありがとうございました。

回答No.4

mshr1962

月末固定でいいなら
=VLOOKUP(DATE(2001,1+1,0),シート１!$A$2:$B$100,2,FALSE)
※DATE(2001,2,0)=DATE(2001,2,1)-1="2001/1/31"です。
年と月の数値を別セルの参照にすればいいでしょう。
サンプルの場合表示形式をユーザー定義で「0000"年"」「0"月"」とすれば見かけは同じで出来ます。　
最大値でいいならこんな方法もあります。
=SUMPRODUCT(MAX((TEXT(シート１!$A$2:$A$100,"yyyymm")="200101")*(シート１!$B$2:$B$100)))

お礼

確かにできました。ありがとうございます。

もう一点だけご教示頂きたいのですが・・、
月末固定ではないのでSUMPRODUCTを使った方式でやってみましたが、
「シート１!$A$2:$A$100」のように範囲を明確に指定しないと#NUM!に
なるようです。シート１のデータ件数はACCESSからEXPORTするため
流動的（正確には増える一方）なのですが、件数を指定しなくても
済む方法はないでしょうか？（やはりあらかじめ大きな数値を指定
しておくしかありませんか？）

回答No.3

noname#204879

「シート２からシート１の毎月末の値を」は「シート１からシート２の毎月末の値を」の間違いですね。

Sheet2!B2: =VLOOKUP(DATE($A1,B1+1,0),Sheet1!$A:$B,2,FALSE)

回答No.2

sydneyh

sheet1にある表に、新たに「年」「月」の列を2列増やせば、ピボッドテーブルで簡単に出来ると思います。

ピボッドテーブルはお分りでしょうか。
これは、列ごとに蓄積された数値や値を、自分の好きなようにレイアウトしたり、集計したりできる表のことです。

表形式でいうと、年別を縦の見出し、月別を横の見出しにして、あとは中の集計を「最大値」にします。
そして、sheet2に作成場所を指定してあげれば、出来上がりです。

ピボッドについては、Excelヘルプでもきちんと載ってますので、まずはお試しを。
これを覚えるといろいろと使えますよ。

お礼

ご回答ありがとうございます。

ピボッドの有用性は認識しているつもりですが、まだまだ勉強不足です・・・。(^^;)
これからも勉強していきます。ありがとうございました。

回答No.1

teg354

ピボットはどうですか？