excel ２つの条件（第三弾）

質問

http://okwave.jp/qa5057168.html
の更に続きですが、
　　　Ａ　　　Ｂ　　　Ｃ 　Ｄ 　Ｅ　　　Ｆ　　Ｇ
１　　あ　　　３　　２２　　　　４　　１５　　？
２　　い　　　４　　１５　　　　４　　１５　　？
３　　う　　　２　　１０　　　　４　　１０　　？
４　　え　　　４　　１０
５　　お　　　４　　１５

想定していなかった５行目を追加しました。
Ｅ１に=LARGE(B1:B5,1)
Ｅ２に=LARGE(B1:B5,2)
Ｅ３に=LARGE(B1:B5,3)
Ｆ１とＦ２とＦ３に{=MOD(LARGE(B1:B5*(MAX(C1:C5)+1)+C1:C5,ROW(B1:B5)),MAX(C1:C5)+1)}
Ｇ１に=INDEX($A$1:$A$5,SUMPRODUCT((B$1:B$5=E1)*(C$1:C$5=F1)*ROW(A$1:A$5)))を入れましたがＢ列とＣ列が同じ組み合わせがある場合ではエラーが出てしまいます。
Ｇ１に『い』、Ｇ２に『お』を表示させる事は可能でしょうか？
重ね重ね申し訳有りませんがわかる方おりましたらよろしくお願いします。

回答No.5

某HN クロメート(Chromate)（@CoalTar）

>=10000-MOD(LARGE(D2:D6,F2:F4),10000)は、
>＃２の方の「=B1+C1/100+(10000-ROW())/10000」と同じような考え方ですか？
似たような考え方です。当方は小数点を嫌っているだけの話。

>前回回答頂いた時ＭＯＤを調べましたが、
>どうしたらこのような式が作れるのですか？
前々回の一番最初に回答したときが 一番の初期の状態です。
100で繰り上がるのではなく、B列の最大値+1で繰り上がっているだけのこと

だから、B列で最大値が決まっているならそれより大きな値で有効桁数を超えない計算ならOKです

ちなみにOFFSET関数を使うのはあまり感心できません
なぜなら、まったく関係のない値を入力・変更してもOFFSET関数を使った数式は計算を実行してしまうからです。「発揮性」で調べると良いです
後から気づいたのですが、#2の方の方法でOFFSET関数をINDEX関数にすれば当方の作業列を使った回答より、良い方法だと思いました

回答No.4

某HN クロメート(Chromate)（@CoalTar）

＃3です 先ほどの補足と作業列案
G1セルの数式も[Ctrl]+[Shft] ＋[Enter] で確定します
B,C列が負の場合は考えていません

作業列案(10000行まで想定)
D1セル =MAX($C$2:$C$6)+1
D2セル =(B2*$D$1+C2)*10000-ROW()
フィルハンドル ダブルクリック

G2:G4セル選択 =10000-MOD(LARGE(D2:D6,F2:F4),10000)
[Ctrl]+[Shft] ＋[Enter] で確定
または G2セル =10000-MOD(LARGE($D$2:$D$6,F2),10000)
H2セル =INDEX(B:B,$G2)
I2セル =INDEX(C:C,$G2)
J2セル =INDEX(A:A,$G2)
おのおの下へオートフィル
B,C列が負の場合は考えていません

お礼

別々にご用意いただきまして本当に有難うございます。
=10000-MOD(LARGE(D2:D6,F2:F4),10000)は、＃２の方の「=B1+C1/100+(10000-ROW())/10000」と同じような考え方ですか？
両方とも自分で作れる気がしません。
法則みたいなのがあるのですか？
もしここから学べ！みたいなことや参考URLなどがあれば是非教えていただければと思います。

回答No.3

某HN クロメート(Chromate)（@CoalTar）

また、作業列禁止でしょうか？
E1:E3セルを選択して =LARGE(B1:B5,ROW(A1:A3))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式です({}で囲まれる)
ふまえて
F1:F3セルを選択して
=MOD(LARGE(B1:B5*(MAX(C1:C5)+1)+C1:C5,ROW(B1:B3)),MAX(C1:C5)+1)
[Ctrl]+[Shft] ＋[Enter] で確定
G1セルのみ選択して
=INDEX($A$1:$A$5,SMALL(IF(($B$1:$B$5=E1)*($C$1:$C$5=F1),ROW($A$1:$A$5)),SUMPRODUCT(($E$1:E1=E1)*($F$1:F1=F1))))
フィルハンドルをダブルクリック

(実際は作業列を使って計算を軽くするので)参考まで

お礼

またまた回答有難うございます。
いつも驚かされます。すごいです。
作業列ですが、全然禁止ではありません。
色々なやり方で出来るようになりたいので・・・。強いて言うなら見た目がスッキリさせたいくらいです。
前回回答頂いた時ＭＯＤを調べましたが、どうしたらこのような式が作れるのですか？
回答頂いた式については全て検証して納得するようにしてるのですが、これはさっぱりわかりません。
もし、よろしければ考え方を教えてもらえますか？

回答No.2

noname#99913

(1)I1に「=B1+C1/100+(10000-ROW())/10000」と入力し、I5までコピー
(2)J1に「=RANK(I1,I:I)」と入力し、J5までコピー
(3)E1に「=OFFSET($A$1,MATCH(ROW(),$J:$J,0)-1,1)」と入力し、E3までコピー
(4)F1に「=OFFSET($A$1,MATCH(ROW(),$J:$J,0)-1,2)」と入力し、F3までコピー
(5)G1に「=OFFSET($A$1,MATCH(ROW(),$J:$J,0)-1,0)」と入力し、G3までコピー

複雑な式を使うより、作業列を使い、単純な関数を組み合わせたほうが、わかりやすいし、応用もききます。

お礼

回答有難うございます。
Ｉ列の(10000-ROW())/10000には、なるほどと思いました。

作業列を使えて更にそれを一つに出来るようになれればと思っていますが、作業列ですら満足に使えないので到底無理ですね。

回答No.1

nattocurry

削除覚悟で。

過去の質問で、回答してもらった数式をそのまま入力しているようですが、関数の理解はしていますか？

SUMPRODUCT関数の値はいくつになっているか調べてみましたか？
INDEX関数の使い方は解りますか？

理解していたら、修正方法は解らなくても、なぜエラーになるのかくらいは解ると思うんですけどねぇ。

この数式は、Ｂ列とＣ列が同じ組み合わせがない前提での数式です。
また前の質問のお礼のように「Ｂ列とＣ列が同じ組み合わせがないなんて言っていません」と言いたいのでしょうが、例が悪すぎます。
同じ組み合わせがあるなら、そういう例を提示すべきです。

お礼

まず、数式自体はそのままではありません。
多少変えてあります。その都度、関数の理解をしているつもりです。

関数の値も検証で調べています。エラーになる原因もわかっています。
しかし、自分ではそのエラーを解消出来なかったので質問しています。
関数はアイデアだと思っています。自分なりにも考えて聞いていることを理解していただきたいです。

同じ組み合わせがあることを想定していなかったと質問でも書いております。指摘されて気付いたのです。