エクセルの関数で

質問

また皆様のお知恵を拝借したく
先週に数値の大きい順番に項目を並べたいという質問をさせていただきましたが、そこに条件を付け足して、同じように求めたいのです。
シート１には
A列　　　　　B列　　　　　　C列
１８０１　　りんご　　　　５００
１８０１　　みかん　　　　３５０
１８０１　　なし　　　　　７００
　　　　　・
　　　　　・
１８０２　　みかん　　　　２５０
１８０２　　ぶどう　　　　６００
・
・
１８１２　　かき　　　　　１０００
B列、C列は以下に項目と数字が並んでいきます。
A列は年月が入ります。
月の入力は毎月２０項目程度になります。
そしてシート２のＤ列1行目から5行目に１８０１における上位金額の5項目
　　　　　　　　Ｅ列1行目から5行目に１８０２における上位金額の5項目
　　　　　　　　　　　　　　・
　　　　　　　　　　　　　　・
　　　　　　　　　　　　Ｏ列1行目から5行目に１８１２における上位5項目
というように取り込みたいのです。
集計シートに関数を入れて自動的に上位項目を表示させるにはどのような関
数を使えばよろしいのでしょうか。
入力されていない場合は表示はしないような形で行いたいのです。
よろしくお願いします。

回答No.5

rin01 · ベストアンサー

Rinです。。。
見る時間がなくて、遅くなりました。
>年ごとに関数を書き換える手間を省く為に、０１～１２という月だけ
>を取り出して行いたい場合は関数式はどのようになるのでしょうか

>列の挿入でＢ列に ＭＩＤ（Ａ１，３，２）という関数でＡ列の
>下２桁（０１～１２）を取り出そうと考えています。

せっかく、ここまでやってきましたので
列を増やさないでやってみませんか？

すこし式が、変わります。

集計シートの
D1: =IF(SUMPRODUCT((RIGHT(Sheet1!$A$1:$A$300,2)=TEXT(COLUMN(A1),"00"))*1)<ROW(A1),"",INDEX(Sheet1!$B:$B,MATCH(LARGE(INDEX((RIGHT(Sheet1!$A$1:$A$300,2)=TEXT(COLUMN(A1),"00"))*(Sheet1!$C$1:$C$300)+(100-ROW($1:$300))/100,),ROW(A1)),INDEX((RIGHT(Sheet1!$A$1:$A$300,2)=TEXT(COLUMN(A1),"00"))*(Sheet1!$C$1:$C$300)+(100-ROW($1:$300))/100,),0)))

で、右と下へコピーです。

★式が長くなりましたので
少しでも、式が分かりやすくなるように
式の一部を名前定義した式と方法も書いておきます。

(順序その１)
名前定義を２つ作ります。
集計シートから
挿入→名前→定義
1つ目
名前欄に　　　→　A列
参照範囲欄に　→　=Sheet1!$A$1:$A$300

追加で、2つ目
名前欄に　　　→　C列
参照範囲欄に　→　=Sheet1!$C$1:$C$300

で、OKです。

(順序その２)
★集計シートの
D1: =IF(SUMPRODUCT((RIGHT(A列,2)=TEXT(COLUMN(A1),"00"))*1)<ROW(A1),"",INDEX(Sheet1!$B:$B,MATCH(LARGE(INDEX((RIGHT(A列,2)=TEXT(COLUMN(A1),"00"))*(C列)+(100-ROW($1:$300))/100,),ROW(A1)),INDEX((RIGHT(A列,2)=TEXT(COLUMN(A1),"00"))*(C列)+(100-ROW($1:$300))/100,),0)))

右と下にコピーです。

(注意)A列　C列　の名前は行範囲が300行になっています。
行数を変更する時は、参照範囲を変えてください。
その時、式の中の
ROW($1:$300)　の部分も同じ行数に変えてください。

★最後に
RIGHT(Sheet1!$A$1:$A$300,2)=TEXT(COLUMN(A1),"00")
この部分で、A列の　1801 等の下2桁をRIGHT関数で
抜き出して比較しています。

例えば、
=RIGHT(Sheet1!$A$1,2) は、01　です。
RIGHT関数で、抜き出しますと数値ではなく
文字列になります。
そこで、COLUMN(A1)　→数値の 1 ですが、
RIGHT関数で抜き出した文字列の　01 と
同じにするために、
TEXT関数で、 1　を　文字列の　01　にします。
=TEXT(COLUMN(A1),"00")
(1月)

ですから
RIGHT(Sheet1!$A$1:$A$300,2)=TEXT(COLUMN(A1),"00")
この場合は、

A1～A300の範囲で、下2桁　＝　01 (1月)
で、　TURE　FALSE　で　判定しています。

説明がヘタでゴメンナサイ！！

分からない場合は、再レスくださいませ～。。。
時々、スレを見ますので。。

。。。Ms.Rinでした～♪♪

お礼

Ms.Rin様
ばっちりできました。大変ありがとうございました。
いろいろな関数が含まれており、理解しきれないところもありますが、がんばって勉強してみます。
また、何かわからないことが出てきましたら、質問をさせていただきます。
その時はまたよろしくお願いします。

回答No.4

rin01

Rinで～す。。。♪

>1800+COLUMN(A1)という個所は何をさしているのでしょうか。

どこかのセルに
=1800+COLUMN(A1)
と入れて右にコピーしてみてください。
1801 1802 1803 ....に、なると思います。

No2さんもNo3さんも書かれていますが

D1セルから右へ、1801 1802 ・・・
と入れて、式の中でセル参照しているのと
同じになります。

その方が、解りやすかったですね。ゴメンナサイ！！

その場合は、式の中の
1800+COLUMN(A1)
の部分をすべて
$D1
に、変更すればいいで～す。
そして、式は　D2に入れて右と下へコピーします。

でした。。。Ms.Rin～♪♪

補足

Ms.Rin様
確認し納得しました。ありがとうございました。
No2さんNo3さんが言われていることと同じなのですね。
今回作製しているファイルは行を挿入することは難しいためRinさんの式で考えていこうと考えています。
もう一つ教えていただきたいのですが、
最初の質問文では１８０１～１８１２という形で年月を取り込むとしましたが、集計シートは原紙として用いようと考えており、年ごとに関数を書き換える手間を省く為に、０１～１２という月だけを取り出して行いたい場合は関数式はどのようになるのでしょうか。
列の挿入でＢ列に ＭＩＤ（Ａ１，３，２）という関数でＡ列の下２桁（０１～１２）を取り出そうと考えています。
何度も申し訳ありませんが、よろしくお願いいたします。

回答No.3

mu2011

次の方法は如何でしょうか。
シート２の１行目を見出しとして、D列にシート１のA列日付を入力する事とします。
シート２のＤ２セルに次の数式を設定して下方向に５行、右方向に必要分コピーします。
尚、配列数式を使用していますので、入力完了時にshift＋ctrl＋enterキーを同時押下して下さい。
D2に=IF(ROW($A1)-1<COUNTIF(Sheet1!$A$1:$A$200,D$1),INDEX(Sheet1!$B$1:$B$200,MATCH(LARGE(IF(Sheet1!$A$1:$A$200=D$1,Sheet1!$C$1:$C$200,0),ROW($A1)),Sheet1!$C$1:$C$200,0)),"")
尚、D1行から表示したいの場合、数式のD$1部分をそれぞれの日付に変更して設定して下さい。

回答No.2

mshr1962

とりあえず同じ金額がなければですが
D1=1801,E1=1802としてそれぞれ2行目から上位5項目で
D2=IF(COUNTIF($A:$A,D$1)>ROW()-2,INDEX($B:$B,SUMPRODUCT(($A$1:$A$100=D$1)*1,($C$1:$C$100=SUMPRODUCT(LARGE(($A$1:$A$100=D$1)*$C$1:$C$100,ROW()-1)))*1,ROW($B$1:$B$100)),1),"")

この式をD2:E6にドラッグして配置してみてください。
簡略化できるか試してみます。
まあ設定がワンパターンなんでマクロOKならユーザー定義関数を作った方が楽かもしれませんが．．．

回答No.1

rin01

ZEBRRAさん
こんにちは～♪

前回より、条件が、増えたんですね～。。。

Ａ列の年月が、シリアル値でなく
数値の場合です。

集計シートの

D1: =IF(COUNTIF(Sheet1!$A$1:$A$300,1800+COLUMN(A1))<ROW
(A1),"",INDEX(Sheet1!$B:$B,INDEX(MATCH(LARGE(((Sheet1!
$A$1:$A$300=1800+COLUMN(A1))*(Sheet1!$C$1:$C$300))+(100-ROW
($1:$300))/100,ROW(A1)),((Sheet1!$A$1:$A$300=1800+COLUMN(A1))*
(Sheet1!$C$1:$C$300))+(100-ROW($1:$300))/100,0),)))

右　O1セルまでコピーと
下　5行目までコピーします。

★sheet1のデータ行は、300行に増やしてあります。

で、いかがでしょうか？

。。。。。Ms.Rinでした～♪♪

補足

Ms.Rinさま
関数の内容について教えてください。
1800+COLUMN(A1)という個所は何をさしているのでしょうか。
実際の集計シートと質問文内容で多少違いがあるものですから。
よろしくお願いいたします。