こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

excel 2つの条件(第三弾)

http://okwave.jp/qa5057168.html
の更に続きですが、
   A   B   C  D  E   F  G
1  あ   3  22    4  15  ?
2  い   4  15    4  15  ?
3  う   2  10    4  10  ?
4  え   4  10
5  お   4  15

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

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2009-06-22 16:40:32

QNo.5065538

困ってます

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(5)

ANo.5

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

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

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

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

投稿日時 - 2009-06-23 22:07:27

ANo.4

#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列が負の場合は考えていません

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2009-06-22 18:42:04

お礼

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

投稿日時 - 2009-06-23 15:21:56

ANo.3

また、作業列禁止でしょうか?
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))))
フィルハンドルをダブルクリック

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

大変申し訳ございませんが、この投稿に添付された画像や動画などは、「BIGLOBEなんでも相談室」ではご覧いただくことができません。 OKWAVEよりご覧ください。

マルチメディア機能とは?

投稿日時 - 2009-06-22 18:23:16

お礼

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

投稿日時 - 2009-06-23 15:07:09

ANo.2

(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までコピー

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

投稿日時 - 2009-06-22 17:16:24

お礼

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

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

投稿日時 - 2009-06-23 11:53:09

ANo.1

削除覚悟で。

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

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

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

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

投稿日時 - 2009-06-22 16:59:23

お礼

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

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

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

投稿日時 - 2009-06-22 17:13:47