[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『条件に合う行範囲の最大値を求めたい』（あつこ） Excel2007/Vista
A B C D E F
1 A 3 列A 記号（1行目から2000行目まですべて同じ"A")
2 A 100 9 1 1 列B 数値ア
3 A 100 1 2 1 列C ここから最大値を求めたい
4 100 4 3 1 列D 数値アが繰り返される回数+1行
5 A 5 4 1 9 列E 数値アの種類数、のつもりだったが、実際は列Dの1の回数
6 410 5 1 2 （数値アに同じ数値が重複する場合あり･･･難問）
7 8 2 2 8
8 A 6 列F 求める答え
9 A 330 9 1 3
最初、列A＝"A"で、列B<>""のとき、列Bの数値の入力範囲+1行に対応する列Cの最大値を求めようと、試行錯誤してみても、 過去ログをひっくり返しても、自力では応用しきれず、新たに列Dと列Eを追加。ちょっと答えに近づいた気がしたのですが、 結局、＃N/Aか＃VALUE!を返すことしかできませんでした。
たくさんの過去ログで勉強させていただいてますが、まだまだ応用が効きません。 すぐに頼っては自分のためにも良くないと思っているのですが、悔しいくらいできません。 どうかお知恵をお貸しください。よろしくお願いいたします。
>条件に合う行範囲の最大値 どういう条件なのですか？ F7セルが、8になる根拠が分らないのですが、、、
(半平太)
本来の最大値を求めたい範囲の条件は、『一行上の列Aに"A"が入力され、かつ列Bに数値が入力された行から 列Aの"A"が消え、列Bに数値が入力された最後の行の1行下まで』です。 列C全体から1つの最大値ではなく、数行ごとの最大値をこの条件で出したいのです。 これでは混乱してしまったので、列Dに最大値を求めたい行の最初から最後までに番号を振ってみました。 そして、それぞれの条件に合う1行目から最大行までの最大値がF5の8とF7の9です。
まずF5の9は、列D2～5(数値1～4)に対応する列C2～5の最大値です。 次のF7の8は、列D6～7(数値1～2)に対応する列C6～7の最大値です。
ご質問の答えになったでしょうか。私にはこれくらいの補助的な計算列しか思いつきませんが、 もっと他に必要でしたらそれでも結構ですので、よろしくお願いいたします。
最初に書いておきますが・・・ コメント記入の際は、その都度ご署名をお願いしますね。
質問ですが、サンプルでは 常に A列が入力された次の行からB列が入力され A列が無い次の行のB列は無いですが 実際のデータもその様になっているのですか？
(HANA)
D2以上に表示されることはないと考えられますので、こう云うことかな？ と、、(半平太)
D3セル =IF((A1="A")*ISNUMBER(B2)*(A2<>"A")*(B3=""), MAX(INDEX(C:C,IF(ISNUMBER(MATCH(9^9,D$1:D2)),MATCH(9^9,D$1:D2),2)):C3),"")
フィルダウン
<結果図>
行 _A_ _B_ _C_ _D_
1 A 3
2 A 100 9
3 A 100 1
4 100 4
5 A 5 9
6 410 5
7 8 8
8 A 6
9 A 330 9
意図するものになるかは？？？ です。 >本来の最大値を求めたい範囲の条件は、・・・・・・・最後の行の1行下まで』です。 ここの部分を理解しきれてないと思いますので、A・B列の並び方によっては、だめ！ってなると思います。
まず、前提として１行目に見出しを付けて考えていますので、実際のデータは２行目からとなります。
ご提示のものに関しては、 D2セルに =IF(COUNT(B2),IF(B1=B2,SUM(D1,1),1),IF(COUNT(B1),SUM(D1,1),"")) E2セルに =IF(OR(COUNTIF($D$1:D2,1)=0,D2=""),"",COUNTIF($D$1:D2,1)) F2セルに =IF(E2="","",IF(E2<>E3,MAX(INDEX(($E$2:E2=E2)*($C$2:C2),0)),"")) これらを下方コピー。
別に作業列を設ける考え方（G列を作業列として、G1セルに 1 を手入力）
G2セルに =IF(AND(COUNTIF(INDIRECT("A"&MAX(G$1:G1)&":A"&ROW()-1),"A")>0,B2=""),ROW(),"")
この場合のF2セルには、 =IF(G2="","",MAX(INDIRECT("C"&MAX($G$1:G1)&":C"&G2)))
同じく下方コピー。
(sin)
半平太さん、できました！ ありがとうございます。あっという間にできてしまうのですね。 MATCH(9^9,～という方法は思いもよりませんでした。さすがですね。
sinさん、2通りも考えていただき本当にありがとうございます。 特に別に作業列を設けるほうは、とても分かり易かったです。INDIRECT関数はとても便利なものを教えていただき、感謝しています。
HANAさん、ご指摘ありがとうございます。うっかりして失礼しました。 ご質問いただいた点は、実際のデータもほぼこの通りです。 > A列が入力された次の行からB列が入力され →この通りです > A列が無い次の行のB列は無いですが →B列の入力停止は別データからの指示なので、 A列は入力を続けるが、B列は空白という場合もあります (B列が途切れると、最大値を求める範囲もそこまでです） 皆様の貴重なお時間と知識をいただき、本当に感謝しています。ありがとうございました。 （あつこ）
D3セル =IF((A1="A")*ISNUMBER(B2)*(A2<>"A")*(B3=""),
MAX(INDEX(C:C,IF(ISNUMBER(MATCH(9^9,D$1:D2)),MATCH(9^9,D$1:D2)+1,2)):C3),"")
↑
ここに「+1」して下さい。
(半平太) m(__)m
半平太さま、遅くまで気にかけていただきありがとうございます！
訂正して戴いた式は、上１行分を除くということですよね？ タイトル付きの大きなシートで計算すると必要だったので助かりました。 とても責任感の強い方なのですね。良い方に教えていただき、とても嬉しいです！
実は、大きなシートで使う場合、B列とC列の間に６列あるのですが、なぜかC列～J列の間の最大値を返してしまいます。 （C2：C5の最大値を求めている(つもり)なのに、C2:J5の最大値が返ってしまいます） 応用なのだからと、ずぅーっと格闘していますがC列だけを限定できません。 面倒をお掛けしますが、もう一度教えてください。よろしくお願いします。
（あつこ）
あつこさんのご説明に忠実に従うと、前回提示した式(D列)は正しくないような気がします。
そこで、見直し後の式の結果(E列)と併記してみます。 ※F,G列を作業列にします。
(1) E2セル =IF((A1<>"A")*(B2="")*(F1=1)*(E1=""),MAX(INDEX(C:C,MATCH(1,INDEX(F:F,G2):F1,0)+G2-1):C2),"") (2) F2セル =IF(AND(F1=1,E2=""),1,IF((A1="A")*ISNUMBER(B2),1,"")) (3) G2セル =IF(ISNA(MATCH(9^9,E$1:E1)),2,MATCH(9^9,E$1:E1)+1)
※そもそも、下例の様なデータが存在し得るのかどうか分りませんが、、、
前レス 見直し後
↓ ↓
行 _A_ _B_ _C_ _D_ E _F_ _G_
1 A 3
2 A 100 9 1
3 A 100 1 1 2
4 100 4 1 2
5 A 5 9 9 2
6 410 5 1 6
7 8 8 8 6
8 A 55 8 ←C8セル(55)は集計の対象外のハズ
9 330 9 1 8
10 A 10 55 10 8
11 A 150 11 ←C11セル(150)は集計の対象外のハズ
12 430 100 1 11
13 430 13 1 11
14 14 100 11 ←ここで集計が行われるベキ？
15 430 15 15
> 訂正して戴いた式は、上１行分を除くということですよね？ より上方にある集計行のC列が次の集計行の判定範囲に入っていたので、 その値が最大値だと結果が正しくなくなるので、外したものです。
> 実は、大きなシートで使う場合、B列とC列の間に６列あるのですが、 >なぜかC列～J列の間の最大値を返してしまいます。
6列入るとしても、J列まで行くとは思われないのですけど？ 単純にB～C列の間に6列挿入すれば、式が自動的に変わってくれませんか？
(やってみると、下の様になりますが、、) 前レスの式 J3セル =IF((A1="A")*ISNUMBER(B2)*(A2<>"A")*(B3=""), MAX(INDEX(I:I,IF(ISNUMBER(MATCH(9^9,J$1:J2)),MATCH(9^9,J$1:J2)+1,2)):I3),"")
今回訂正の式 (1) K2セル =IF((A1<>"A")*(B2="")*(L1=1)*(K1=""),MAX(INDEX(I:I,MATCH(1,INDEX(L:L,M2):L1,0)+M2-1):I2),"") (2) L2セル =IF(AND(L1=1,K2=""),1,IF((A1="A")*ISNUMBER(B2),1,"")) (3) M2セル =IF(ISNA(MATCH(9^9,K$1:K1)),2,MATCH(9^9,K$1:K1)+1)
行 _A_ _B_ _C_ _D_ _E_ _F_ _G_ _H_ _I_ _J_ _K_ _L_ _M_ 1 A 3 2 A 100 9 1 2 3 A 100 1 1 2 4 100 4 1 2 5 A 5 9 9 2 6 410 5 1 6 7 8 8 8 6 8 A 55 8 9 330 9 1 8 10 A 10 55 10 8 11 A 150 11 12 430 100 1 11 13 430 13 1 11 14 14 100 11 15 430 15 15
(半平太)
質問の御回答への新たな疑問なのですが ＞> A列が入力された次の行からB列が入力され ＞ →この通りです であれば、一つ上が「A」かの確認は 要らないような気がしますが・・・。
＞B列の入力停止は別データからの指示なので、 ＞A列は入力を続けるが、B列は空白という場合もあります A列とB列は連動していないようですので （どの様なデータなのかも分かりませんし） 可能性として、例えば「A」で始まったまとまりの 途中に「B」が入って対象外になることはあるのでしょうか？
上の半平太さんのサンプルを使用させていただいて。 条件は B列数値入力開始行の一行上のA列が「A」となっている行から B列数値入力終了行の一行下の行（空欄行）迄の C列の最大値 を求めます。
D列が作業列です。 D15は、A14が空欄のため、対象外になります。 次に「B列数値入力開始行の一行上のA列が「A」となっている行」 が出現するまで、E列の値は空欄がつづきます。 [A] [B] [C] [D] [E] [1] A 3 [2] A 100 9 1 [3] A 100 1 2 [4] 100 4 3 [5] A 5 4 9 [6] 410 5 1 [7] 8 2 8 [8] A 55 0 [9] 330 9 1 [10] A 10 2 10 [11] A 150 0 [12] 430 100 1 [13] 430 13 2 [14] 14 3 100 [15] 430 15 1 D2に =IF(B2="",IF(B1="",0,D1+1),IF(B1="",1,D1+1)) E2に =IF(AND(OFFSET(A2,-D2,)="A",D2>D3),MAX(OFFSET(C2,,,-D2)),"") として、下にコピーして下さい。
D15に関してサンプルを載せておきます。 [A] [B] [C] [E] [14] 14 [15] 430 15 ←－－ [16] A 430 4 上側は [17] A 430 8 ずっと [18] 430 9 空欄が [19] 430 12 続きます [20] A 2 ←－－ [21] 430 13 13 ←ここから 範囲開始 もしもB17の値を消したら、 C18:C20の範囲で 最大を求め、E20にその値を表示します。
(HANA)
半平太さま、何度も分かりやすく解説いただき、本当にありがとうございます。 おかげで間6列分の数値を返すというのは解決しました。
さっそく新しい式も試し、小さいサンプルではできた！と思ったのですが、大きなデータでは同じ答えが並んでしまいます。 ご指摘の点によるのかもしれません。 説明が悪いせいでお手数をおかけして、どうもすいません。
> ←C8セル(55)は集計の対象外のハズ ←その通りです
> ←C11セル(150)は集計の対象外のハズ ←A10に(A)が入ることで、B11は必ず数値が入るため、
その前提により、C11(150)は認識してほしいです
(この場合、C11～C12の最大値を求めたい(=150)。また、A12に(A)がなければ、B13に数値が入ることはありません)
> ←ここで集計が行われるベキ？ ←いいえ。(B列だけに数値が入ることはありませんので）
また、応用で最小値も求めようとしているのですが、この式を同じように扱ってはダメなのでしょうか。(訂正版を使用） 小さいサンプルテストの段階からうまくできませんでした。入力セルより上を参照したいのに、なぜか下を見てしまいます。
すみませんが、(3) M2セル =IF(ISNA(MATCH(9^9,K$1:K1)),2,MATCH(9^9,K$1:K1)+1) について教えていただけませんか。私の方ではなぜか、半平太さんのサンプルのように 少しずつ増加せず、上から下まで同じ数値なのです。 サンプル通り指揮をあてはめているつもりなのですが、何度見直してもどこが違うか見つけられなくて。 本当に何度もすみませんが、よろしくお願いいたします。 (あつこ）
HANAさん、ありがとうございます。できました！ 最小値もきちんと出せました。 お願いしておきながら、私の説明の悪さで皆様に時間をとらせてしまい、申し訳ありませんでした。 でも、おかげさまで応用の幅が広がり、とても感謝しております。 次回質問させていただくときはまず、肝心の質問内容をうまく説明できるよう心掛けますので、 これに懲りず、よろしくお願いします。本当にありがとうございました。 (あつこ）
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.