[[20060302144720]]　『９０より以上の人の名前を抽出したい』（shota）　ページの最後に飛ぶ

『９０より以上の人の名前を抽出したい』（shota）

     　AA　　　　AB 　　AC 
 31102 田中　　  100    =IF(COUNTIF($AB$31102:$AB$31106,">=90")<ROW(),"",INDEX($AA$31102:$AA$31106,SMALL(IF($AB$31102:$AB$31106>=90,ROW($AA$31102:$AA$31106)),ROW())))
 31103 吉田　　　 95 
 31104 広瀬　　　 98 
 31105 黒木　　  100 
 31106 今岡　　 　75 
 という具合にセル番地も変えていますが、
 なぜか大きなセル番地になると反応してくれません。
 何故でしょうか ?
 おんぶに抱っこで済みませんが式を全部書いていただけないでしょうか。
 Excel2002
 WindowsXP

 部分部分でどんな値が計算されているか、じっくり考えましょう。
　
31102行目の任意のセルで、=ROW()　は「31102」と計算されます。　
したがって、
COUNTIF($AB$31102:$AB$31106,">=90")<ROW()
はROW()の部分だけ数字にすると
COUNTIF($AB$31102:$AB$31106,">=90")<31102　と計算されます。
　
つまり、
$AB$31102:$AB$31106の範囲に">=90"に当てはまるものが31102件以上ないと、""。
当然そんなに件数はないでしょうから、「反応してくれません。」
　
31102行目では　COUNTIF($AB$31102:$AB$31106,">=90")<1　としたいところですよね？
ということは　ROW()　がまずいということになります。
ちなみに
=ROW(A1)　とすると　「1」と計算されます。・・・とここまでがヒント。
　
(みやほりん)(-_∂)b

みやほりんさん　早速のご指導ありがとうございます。考えて見ます。

 横への移動はセル番地の書き換えでできました。
 問題はたてへの移動です。もう少しヒントをいただけないでしょうか。
 ROW()の次に何かを書くんですか　それとも()のなかに何かを書くんでしょうか
 宜しくお願いします。　
 (shota)

 どこかで見た式だと思ったら、こちらの疑問を別スレに立てたのですね。
[[20060301183031]] 『該当する氏名を一覧表に抽出』（Ｋさん） 
　
長くなります。
　
ROW関数をなぜ使っているか、という暗号を解かないとこの式は理解できません。　
一行目にある状態から考えて見ましょう。
次の数式はなぜROW関数を使っているのか？
　
=IF(COUNTIF($B$1:$B$5,">=90")<ROW(),"",INDEX($A$1:$A$5,SMALL(IF($B$1:$B$5>=90,ROW($A$1:$A$5)),ROW())))
　
日本語訳します。いきなり全部日本語訳するとややこしいので、便宜上、
$A$1:$A$5を「Ａ範囲」、
$B$1:$B$5を「Ｂ範囲」、
INDEX($A$1:$A$5,SMALL(IF($B$1:$B$5>=90,ROW($A$1:$A$5)),ROW()))　を「INDEX関数部分」
と呼びましょう。

もし、Ｂ範囲で90点以上の数値の数がROW()に満たなければ　空白としなさい。そうでなければINDEX関数部分を計算しなさい。

    A　　　　B 　　  C 
 1 田中　　  100    =IF(COUNTIF($B$1:$B$5,">=90")<ROW(),"",INDEX($A$1:$A$5,SMALL(IF($B$1:$B$5>=90,ROW($A$1:$A$5)),ROW())))
 2 吉田　　　 95 
 3 広瀬　　　 98 
 4 黒木　　  100 
 5 今岡　　 　75 
　
上記の表で　C1　へ入力した式では・・・
Ｂ範囲に90点以上が「ひとつもなかったら」空白、「ひとつ以上あったら」INDEX関数部分を計算。
同じく　C2　へコピーした式では・・・
Ｂ範囲に90点以上が「ふたつなかったら」空白、「ふたつ以上あったら」INDEX関数部分を計算。
つまり、90点以上の数値の数によって空白にするかINDEX関数部分を計算するかを分岐したいのです。
　
ということは、本来は次のような入力になります。
    A　　　　B 　　  C 
 1 田中　　  100    =IF(COUNTIF($B$1:$B$5,">=90")<1,"",INDEX($A$1:$A$5,SMALL(IF($B$1:$B$5>=90,ROW($A$1:$A$5)),1)))
 2 吉田　　　 95    =IF(COUNTIF($B$1:$B$5,">=90")<2,"",INDEX($A$1:$A$5,SMALL(IF($B$1:$B$5>=90,ROW($A$1:$A$5)),2)))
 3 広瀬　　　 98    =IF(COUNTIF($B$1:$B$5,">=90")<3,"",INDEX($A$1:$A$5,SMALL(IF($B$1:$B$5>=90,ROW($A$1:$A$5)),3)))
 4 黒木　　  100    =IF(COUNTIF($B$1:$B$5,">=90")<4,"",INDEX($A$1:$A$5,SMALL(IF($B$1:$B$5>=90,ROW($A$1:$A$5)),4)))
 5 今岡　　 　75    =IF(COUNTIF($B$1:$B$5,">=90")<5,"",INDEX($A$1:$A$5,SMALL(IF($B$1:$B$5>=90,ROW($A$1:$A$5)),5)))
　
こんな入力、したいですか？行が増えると、数値を１づつ増やす部分が2箇所あります。
一行目をコピーしても修正しなきゃならない。５行ぐらいならいいですけど、何十行も
やってられないでしょう？
　
一行目で作った数式をコピーするだけで修正しなくてもよい方法はないか？
ここで　ROW関数　の出番です。
　
ROW関数は引数（()の中身）を省略すると入力した行の行番号を計算します。
A1の　=ROW()　は　　　　　　1　
C2に　=ROW()　を入力すれば　2　
AB3に =ROW()　ならば　　　　3　というふうに。
（この関数はどの列へ入力するかは関係ないのです）
即ち、ROW関数はこの1、2、3・・・を修正する手間を省くためだけに使用しています。
　
C1の数式を切り取ってAC31102に貼り付けたときはどうなるか。
　
=IF(COUNTIF($AB$31102:$AB$31106,">=90")<ROW(),"",INDEX($AA$31102:$AA$31106,SMALL(IF($AB$31102:$AB$31106>=90,ROW($AA$31102:$AA$31106)),ROW())))
　
日本語訳します。
もし、Ｂ範囲で90点以上の数値の数がROW()に満たなければ　空白としなさい。そうでなければINDEX関数部分を計算しなさい。

 最初の日本語訳と同じです。ただ先ほど書いたように、
『ROW関数は引数（()の中身）を省略すると入力した行の行番号を計算します。』
これを考慮して日本語訳します。
もし、Ｂ範囲で90点以上の数値の数が　31102　に満たなければ　空白としなさい。そうでなければINDEX関数部分を計算しなさい。
実際には　AC31102　に貼り付けたときでも　ROW()　の部分は　1　であってほしいのです。
なぜなら、移動する前の数式に期待される処理は・・・
Ｂ範囲に90点以上が「ひとつもなかったら」空白、「ひとつ以上あったら」INDEX関数部分を計算。
移動してもこの処理が変わってしまっては同じ計算結果を得られません。
　
ではどうするか。
ROW()のままでは　31102　になってしまう。
ROW関数は引数を省略した場合は入力セルの行番号が返る。
では、どうしたらよいのか。　　先ほど　ヒント　で書きました。
　
＞ちなみに
＞=ROW(A1)　とすると　「1」と計算されます。
　
１という数字がほしかったら、一行目のセルを参照すればよいのです。
ROW関数は引数にセル参照を与えると参照したセルの行番号を返します。
ということで、正解は　
　
=IF(COUNTIF($AB$31102:$AB$31106,">=90")<ROW(A1),"",INDEX($AA$31102:$AA$31106,SMALL(IF($AB$31102:$AB$31106>=90,ROW($A$1:$A$5)),ROW(A1))))
　
この数式をCtrl+Shift+Enter。
　
ここで新たな疑問が湧くはずです。
なぜ、INDEX関数部分で　ROW($A$1:$A$5)　はROW($AA$31102:$AA$31106)ではないのか？
これはINDEX関数を働かせる部分だから、上で述べたROW関数とは違う理由です。
AC31102で単純にＡ範囲から「田中」をINDEX関数で取り出す場合。
　
     　AA　　　　AB 　　AC 
 31102 田中　　  100    =INDEX($AA$31102:$AA$31106,1)
 31103 吉田　　　 95 
 31104 広瀬　　　 98 
 31105 黒木　　  100 
 31106 今岡　　 　75 
　
「1」の所を、「吉田」なら「２」、「今岡」なら「５」と書き換えれば表示できます。
INDEX関数はＡ範囲が何行目にあろうと、そのＡ範囲の１番目、２番目、とリスト
（配列）の順番を指定して検索を行う関数です。31102行目から始まるリストだから
といって、そのリストの１番目の「田中」を検索しようと思ったら、31102を指定
してはだめなのです。
INDEX関数の第一引数のＡ範囲は５行しかないので、第二引数は1～5でなければなり
ません。Ａ範囲の行数よりも大きい値を指定すると　#REF!（参照でけまへん）エラー。
リストが6行以上ないのだから、当然。
　
と、これが前置きで、いよいよ、一番小難しい部分です。
　
INDEX($AA$31102:$AA$31106,SMALL(IF($AB$31102:$AB$31106>=90,ROW($A$1:$A$5)),ROW(A1)))
　
この部分が配列数式です。内側の部分から解説していきます。
　
IF($AB$31102:$AB$31106>=90,ROW($A$1:$A$5))
　
この数式はひとつで次の５つの数式の計算をしています。
IF($AB$31102>=90,ROW($A$1))　 $AB$31102は　90以上なので　　　　1
IF($AB$31103>=90,ROW($A$2)) 　$AB$31103は　90以上なので　　　　2 
IF($AB$31104>=90,ROW($A$3)) 　$AB$31104は　90以上なので　　　　3
IF($AB$31105>=90,ROW($A$4)) 　$AB$31105は　90以上なので　　　　4
IF($AB$31106>=90,ROW($A$5)) 　$AB$31106は　90以上ではないので　FALSE（偽）
　
これが配列数式という形で計算されると{1;2;3;4;FALSE}という配列が計算結果に
なります。配列数式とは複数の計算をひとつの関数の中で計算してしまうもの、と
とりあえず思っておいてください。
　
次にその外側。
SMALL(IF($AB$31102:$AB$31106>=90,ROW($A$1:$A$5)),ROW(A1))
IF($AB$31102:$AB$31106>=90,ROW($A$1:$A$5))　の部分へ先ほどの計算結果代入してみます。
　
SMALL({1;2;3;4;FALSE},ROW(A1))
　
{1;2;3;4;FALSE}　という配列の中からROW(A1)=1 番目に小さい値は　なぁに？
答えは　「1」です。
　
・・・ということで
INDEX($AA$31102:$AA$31106,SMALL(IF($AB$31102:$AB$31106>=90,ROW($A$1:$A$5)),ROW(A1)))
　
この計算でSMALLまでの計算は「1」なので、
INDEX($AA$31102:$AA$31106,1)
Ａ範囲の一番目、「田中」が結果として計算されます。
　
　
　
　
うーん、一番目が表示されると面白みがないですね。
では、田中さんを奮発して　0　にしてしまいましょう。
　
     　AA　　　　AB 　　AC 
 31102 田中　　    0    
 31103 吉田　　　 95 
 31104 広瀬　　　 98 
 31105 黒木　　  100 
 31106 今岡　　 　75 
　
この表で計算すると、
IF($AB$31102>=90,ROW($A$1))　 $AB$31102は　90以上ではないので　FALSE（偽）
IF($AB$31103>=90,ROW($A$2))　 $AB$31103は　90以上なので　　　　2 
IF($AB$31104>=90,ROW($A$3))　 $AB$31104は　90以上なので　　　　3
IF($AB$31105>=90,ROW($A$4))　 $AB$31105は　90以上なので　　　　4
IF($AB$31106>=90,ROW($A$5))　 $AB$31106は　90以上ではないので　FALSE（偽）
　
IF($AB$31102:$AB$31106>=90,ROW($A$1:$A$5))　の計算結果は　{FALSE;2;3;4;FALSE}
　
この状態で
SMALL(IF($AB$31102:$AB$31106>=90,ROW($A$1:$A$5)),ROW(A1))　は
SMALL({FALSE;2;3;4;FALSE},ROW(A1))　という配列を計算。
　
{FALSE;2;3;4;FALSE}　という配列の中から ROW(A1) = 1 番目に小さい値は　なぁに？
答えは「2」です。
（FALSEはどの数値よりも大きいと判断される。IFの部分で偽の処理を敢えて省略する
のはこの効果をねらっている）
　
次に
INDEX($AA$31102:$AA$31106,SMALL(IF($AB$31102:$AB$31106>=90,ROW($A$1:$A$5)),ROW(A1)))
SMALL関数までの計算結果を代入すると、
INDEX($AA$31102:$AA$31106,2)
　
Ａ範囲の2番目は「吉田」なのでそれが表示されます。
　
つまり、ROW($A$1:$A$5)は、表示すべき値がＡ範囲の「何番目」にあるかを特定する
ために使われているのです。
ふう～っ！
(みやほりん)(-_∂)b

みやほりんさん　ごめんなさい。こんなに詳しく書いていただいても理解できない私です。まったく情けないです。(shota)

 みやほりんさんが、回答してからまだそんなに時間がたってませんが？
理解できないのではなく、自分のパソコンで操作を行なったのでしょうか？
もし行なったのでしたら、どこがわからないのかの説明や何がわかったのかが、言えるのではないでしょうか？
前にも、言いましたがここは学校ですよ？
勉強をする場であって、回答のみを求める場とは違います。
一緒に勉強をする場ではないでしょうか？

 （キリキ）(〃⌒o⌒)b

思いつくままテストはしました。

 =IF(COUNTIF($AB$31102:$AB$31106,">=90")<ROW(),"",INDEX($AA$31102:$AA$31106,SMALL(IF($AB$31102:$AB$31106>=90,ROW($AA$31102:$AA$31106)),ROW())))
 これをベースにしてから教えられたことをいろいろと推測しながら式を変えて実験をやりました。
 それも思いつくすべてをやってからの結果だめだったのです。
 何もやらなかったわけではありません。
 結局別シートに氏名点数とかの項目も入れられないまま表だけを写しました。
 私のQ＆Aファイルを取られた方がご自分で解決されることを期待してます。
 (shota)

 私は・・・
・書き込み、検証、推敲を含め前後併せて１６０分あまり
・配列数式の存在を知り、理解、自分で数式の組み立てできるまでに２年あまり
・ExcelVBAマクロで思うようなコードを組めるようになるまでに約３年半
・喧嘩別れした友人と和解するまで５年と２ヶ月
　
などなどという時間を費やしております。
気が長いんでしょうか、短いのでしょうか。
　
自宅のノートPCのバッテリ持続時間は３０秒弱です。
正解の式はすでに書き込んであります。
(みやほりん)

 衝突しました・・。

 みやほりんさんがとっても詳しく説明されておられるのに・・・・。
[[20060301183031]] 『該当する氏名を一覧表に抽出』（Ｋさん）
 ↑で私が貼り付けたリンク先のTyさんのコメントを見られましたか・・・？
 ほんの1～2時間お試しになっただけで、
＞思いつくすべてをやってからの結果だめだったのです。
 は、少し納得できませんね・・・。
 shotaさんがお試しになった時間より、みやほりんさんがコメントを掛かれる時間に費やした時間が長いかも・・・(^_^A;

 私もこの学校に来たての頃は、配列数式を解くのに数時間掛かったものです。＿/￣|○ il||li
 もう一度、今までみなさんがコメントされたことを、少しずつ理解しながらお試しになってはどうでしょうか。

 とりあえず、セルの移動にも対応できる式にしてみました。
 =IF(COUNTIF($AB$31102:$AB$31106,">=90")<ROWS($AA$31102:$AA31102),"",INDEX($AA$31102:$AA$31106,SMALL(IF($AB$31102:$AB$31106>=90,ROW($AA$31102:$AA$31105)-(ROW($AA$31102)-1)),ROWS($AA$31102:$AA31102))))

 （川野鮎太郎）

Ｋさん oniさん　川野鮎太郎さん　sinさん　みやほりんキリキさんどうもありがとうございました。
いい方法が見つかりました。
あの表はセルA1からにして置いてマクロで内容だけをシート２にコピーするというのはどうでしょうか。
もちろんシート２には一行目には各項目を書いておくんです。
(shota)

川野さん　ありがとうございました。完璧になりました。
どうもありがとうございました。何度お礼を言っても言い尽くせません。
本当にありがとうございました。
私事なんですが　脳梗塞で右半身不随となった私を職場の人は助けてくれ6年間も勤めさせてくれました。その恩返しにヤフーで「エクセル初心者の方勉強しませんか。」という部屋を開いています。
「最初に」というファイルと15個の圧縮フォルダの中に50個のファイルがあります。
Q＆A方式にして毎日集めてホームページのブリーフケースは更新しています。
少しでもお役に立てればと思い毎日少しづつQ＆Aを集めています。喜んでもらえるとうれしいです。
(shota)

 違うんですよ！shotaさん！
厳しいことを言うようですが、今shotaさんが一生懸命にやっておられることは、「初心者のため」ではなく「自分が満足できるかどうか」だと思いますよ？
σ(^o^;)はshotaさんの所にもお邪魔してます。

会社のPCで、Q&Aも先日ダウンロードしてます。
はたして、あれは本当の意味でQ&Aになるんでしょうか？
初心者のエクセル画面が見れない以上、shotaさんが理解して説明できないとQ&Aにならないと思うんです。
今のshotaさんの行なっていることはQ&Aの収集でしか無いと思うのです。
σ(^o^;)は、この学校でエクセルの関数を知りマクロを知りました。
まだ二年程度です。
σ(^o^;)にも出来るんです！
あんなに素晴らしいマクロを組めるshotaさんならちょっと時間を掛ければ出来るはずです！
時間を惜しまず、理解し初心者を助けてあげてください。

 携帯からの書き込みで、コメントを利用しないと書き込みできません。
コメント機能は文字数の関係がありスレが二度にまたがる無礼をお許しください。
 （キリキ）(〃⌒o⌒)b

確かにキリキさんのおっしゃるとおりだと思います。
今も今までかかってあるところからメールで送ってくる
Q＆Aを整理していました。
今の状態では整理と収集のほうばかりで何も理解し切れていません。
反省して今後は理解に勤めます。
(shota)

 こっちにお邪魔します。　今後の理解を深める方法の一案を・・・
最後に提示されてる鮎太郎さんの式で出来たのでしたら、
その右隣に
 =INDEX($AA$31102:$AA$31106,SMALL(IF($AB$31102:$AB$31106>=90,ROW($AA$31102:$AA$31105)-(ROW($AA$31102)-1)),ROWS($AA$31102:$AA31102)))
で配列確定し、下方コピー。　この右隣に
 =IF(COUNTIF($AB$31102:$AB$31106,">=90")<ROWS($AA$31102:$AA31102),"",ROW(A1))
として、下方コピー。
式を分けて表示結果を見比べ、元のデータの値を変えてみたり、式を色々弄ってみるのも、結構楽しいものですよ。
(sin)

sinさん　色々と気を使ってくれてありがとうございます。
教えられた式を勉強させていただきます。
脳梗塞の後遺症から来る右半身の痛み　それに加え今は歯医者にまで通っています。
今日も傷みから逃れるのに４時まで寝ていました。
夜は痛み止めどめの座薬を入れられるので何とかしのげます。
今日も頑張って勉強したいと思っております。
(shota)

sinさん　判りましたよ。

 =INDEX($AA$31102:$AA$31106,SMALL(IF($AB$31102:$AB$31106>=90,ROW($AA$31102:$AA$31105)-(ROW($AA$31102)-1)),ROWS($AA$31102:$AA31102)))
上記の式でも順位氏名が出ると言うことですよね。
=IF(COUNTIF($AB$31102:$AB$31106,">=90")<ROWS($AA$31102:$AA31102),"",ROW(A1))
上記の式は何名いるかが出る式ですよね。
本当に温かいご指導に感謝しております。
ありがとうございます。
キリキさん　川野鮎太郎さん　みやほりんさん　Ｋさん
本当にありがとうございました。
今後とも宜しくお願いします。
(shota)

今、みやほりんさんが書いてくれたのを勉強中です。
(shota)

 頑張れ～♪
（キリキ）(〃⌒o⌒)b

ただなんとなく

        AA         AB        AC       AD        AE       AF                AG      AH
 31102 田中	99	田中	吉田	広瀬	飯田		田中	1
 31103 吉田	79	今岡	黒木	広島			今岡	2
 31104 広瀬	52	但馬	黒下				但馬	3
 31105 黒木	70	今永					今永	4
 31106 今岡	80							
 31107 但馬	89							
 31108 飯田	21							
 31109 広島	41							
 31110 黒下	61							
 31111 今永	99

 AC31102には{=IF(COUNTIF($AB$31102:$AB$31111,">=80")<ROWS($AA$31102:$AA31102),"",INDEX($AA$31102:$AA$31111,SMALL(IF($AB$31102:$AB$31111>=80,ROW($AA$31102:$AA$31111)-(ROW($AA$31102)-1)),ROWS($AA$31102:$AA31102))))}
 AD31102には{=IF(SUMPRODUCT(($AB$31102:$AB$31111>=60)*($AB$31102:$AB$31111<=79))<ROW(A1),"",INDEX($AA$1:$AA$31111,SMALL(IF(($AB$31102:$AB$31111>=60)*($AB$31102:$AB$31111<=79),ROW($AB$31102:$AB$31111)),ROW(A1))))}
 AD31102には{=IF(SUMPRODUCT(($AB$31102:$AB$31111>=60)*($AB$31102:$AB$31111<=79))<ROW(A1),"",INDEX($AA$1:$AA$31111,SMALL(IF(($AB$31102:$AB$31111>=60)*($AB$31102:$AB$31111<=79),ROW($AB$31102:$AB$31111)),ROW(A1))))}
 AE31102には{=IF(SUMPRODUCT(($AB$31102:$AB$31111>=40)*($AB$31102:$AB$31111<=59))<ROW(B2),"",INDEX($AA$1:$AA$31111,SMALL(IF(($AB$31102:$AB$31111>=40)*($AB$31102:$AB$31111<=59),ROW($AB$31102:$AB$31111)),ROW(B2))))}
 AF31102には{=IF(SUMPRODUCT(($AB$31102:$AB$31111>=20)*($AB$31102:$AB$31111<=39))<ROW(C1),"",INDEX($AA$1:$AA$31111,SMALL(IF(($AB$31102:$AB$31111>=20)*($AB$31102:$AB$31111<=39),ROW($AB$31102:$AB$31111)),ROW(C1))))}
 AG31102には{=IF(SUMPRODUCT(($AB$31102:$AB$31111>=0)*($AB$31102:$AB$31111<=19))<ROW(D1),"",INDEX($AA$1:$AA$31111,SMALL(IF(($AB$31102:$AB$31111>=0)*($AB$31102:$AB$31111<=19),ROW($AB$31102:$AB$31111)),ROW(D1))))}
 AH31102には{=IF(AC31102="","",INDEX($AA$31102:$AA$31111,SMALL(IF($AB$31102:$AB$31111>=80,ROW($AA$31102:$AA$31111)-(ROW($AA$31102)-1)),ROWS($AA$31102:$AA31102))))}
 AI31102には{=IF(COUNTIF($AB$31102:$AB$31111,">=80")<ROWS($AA$31102:$AA31102),"",ROW(A1))}
 教えられた式を色々と書き換えていたらいつの間にやらできてしまったんです。
 今、みやほりんさんから教えてもらった説明に取り組んでいます。
 今後とも宜しくご指導お願いします。
 これを読まれた方、http://www.realintegrity.net/~yancha/ブリーフケースからファイルを取ってくださいね。
 きっとお役に立てると思いますよ。
 ご指導してくださった皆さん、本当にありがとうございました。
 (shota)

コメント返信：

[ 一覧(最新更新順) ]

[[20060302144720]] 『９０より以上の人の名前を抽出したい』（shota） ページの最後に飛ぶ

[[20060302144720]]　『９０より以上の人の名前を抽出したい』（shota）　ページの最後に飛ぶ