[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『90より以上の人の名前を抽出したい』(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]] 『該当する氏名を一覧表に抽出』(Kさん) 長くなります。 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を「A範囲」、 $B$1:$B$5を「B範囲」、 INDEX($A$1:$A$5,SMALL(IF($B$1:$B$5>=90,ROW($A$1:$A$5)),ROW())) を「INDEX関数部分」 と呼びましょう。
もし、B範囲で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 へ入力した式では・・・ B範囲に90点以上が「ひとつもなかったら」空白、「ひとつ以上あったら」INDEX関数部分を計算。 同じく C2 へコピーした式では・・・ B範囲に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))) こんな入力、したいですか?行が増えると、数値を1づつ増やす部分が2箇所あります。 一行目をコピーしても修正しなきゃならない。5行ぐらいならいいですけど、何十行も やってられないでしょう? 一行目で作った数式をコピーするだけで修正しなくてもよい方法はないか? ここで 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()))) 日本語訳します。 もし、B範囲で90点以上の数値の数がROW()に満たなければ 空白としなさい。そうでなければINDEX関数部分を計算しなさい。
最初の日本語訳と同じです。ただ先ほど書いたように、 『ROW関数は引数(()の中身)を省略すると入力した行の行番号を計算します。』 これを考慮して日本語訳します。 もし、B範囲で90点以上の数値の数が 31102 に満たなければ 空白としなさい。そうでなければINDEX関数部分を計算しなさい。 実際には AC31102 に貼り付けたときでも ROW() の部分は 1 であってほしいのです。 なぜなら、移動する前の数式に期待される処理は・・・ B範囲に90点以上が「ひとつもなかったら」空白、「ひとつ以上あったら」INDEX関数部分を計算。 移動してもこの処理が変わってしまっては同じ計算結果を得られません。 ではどうするか。 ROW()のままでは 31102 になってしまう。 ROW関数は引数を省略した場合は入力セルの行番号が返る。 では、どうしたらよいのか。 先ほど ヒント で書きました。 >ちなみに >=ROW(A1) とすると 「1」と計算されます。 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で単純にA範囲から「田中」をINDEX関数で取り出す場合。 AA AB AC 31102 田中 100 =INDEX($AA$31102:$AA$31106,1) 31103 吉田 95 31104 広瀬 98 31105 黒木 100 31106 今岡 75 「1」の所を、「吉田」なら「2」、「今岡」なら「5」と書き換えれば表示できます。 INDEX関数はA範囲が何行目にあろうと、そのA範囲の1番目、2番目、とリスト (配列)の順番を指定して検索を行う関数です。31102行目から始まるリストだから といって、そのリストの1番目の「田中」を検索しようと思ったら、31102を指定 してはだめなのです。 INDEX関数の第一引数のA範囲は5行しかないので、第二引数は1〜5でなければなり ません。A範囲の行数よりも大きい値を指定すると #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)) この数式はひとつで次の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) A範囲の一番目、「田中」が結果として計算されます。 うーん、一番目が表示されると面白みがないですね。 では、田中さんを奮発して 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) A範囲の2番目は「吉田」なのでそれが表示されます。 つまり、ROW($A$1:$A$5)は、表示すべき値がA範囲の「何番目」にあるかを特定する ために使われているのです。 ふう〜っ! (みやほりん)(-_∂)b
みやほりんさんが、回答してからまだそんなに時間がたってませんが? 理解できないのではなく、自分のパソコンで操作を行なったのでしょうか? もし行なったのでしたら、どこがわからないのかの説明や何がわかったのかが、言えるのではないでしょうか? 前にも、言いましたがここは学校ですよ? 勉強をする場であって、回答のみを求める場とは違います。 一緒に勉強をする場ではないでしょうか?
(キリキ)(〃⌒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)
私は・・・ ・書き込み、検証、推敲を含め前後併せて160分あまり ・配列数式の存在を知り、理解、自分で数式の組み立てできるまでに2年あまり ・ExcelVBAマクロで思うようなコードを組めるようになるまでに約3年半 ・喧嘩別れした友人と和解するまで5年と2ヶ月 などなどという時間を費やしております。 気が長いんでしょうか、短いのでしょうか。 自宅のノートPCのバッテリ持続時間は30秒弱です。 正解の式はすでに書き込んであります。 (みやほりん)
衝突しました・・。
みやほりんさんがとっても詳しく説明されておられるのに・・・・。 [[20060301183031]] 『該当する氏名を一覧表に抽出』(Kさん) ↑で私が貼り付けたリンク先の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))))
(川野鮎太郎)
違うんですよ!shotaさん! 厳しいことを言うようですが、今shotaさんが一生懸命にやっておられることは、「初心者のため」ではなく「自分が満足できるかどうか」だと思いますよ? σ(^o^;)はshotaさんの所にもお邪魔してます。
携帯からの書き込みで、コメントを利用しないと書き込みできません。 コメント機能は文字数の関係がありスレが二度にまたがる無礼をお許しください。 (キリキ)(〃⌒o⌒)b
こっちにお邪魔します。 今後の理解を深める方法の一案を・・・ 最後に提示されてる鮎太郎さんの式で出来たのでしたら、 その右隣に =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)
=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)) 上記の式は何名いるかが出る式ですよね。 本当に温かいご指導に感謝しております。 ありがとうございます。 キリキさん 川野鮎太郎さん みやほりんさん Kさん 本当にありがとうございました。 今後とも宜しくお願いします。 (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)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.