[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『該当する氏名を一覧表に抽出』（Ｋさん）
いつもお世話になります。今回も初歩的な質問で吸いませんがお助け願います。
Ａ Ｂ Ｃ
田中 １００
吉田 ９５
広瀬 ９８
黒木 １００
今岡 ７５
という１００名程度の表があります。Ｂ列にはいろいろ数字が入っています。
この表から「１００点」の人や「９０点台の人」の氏名を抽出したいのです。
ＩＮＤＥＸ関数を使うのかな、と思うのですが、私の力では思うようにはなりませんでした。
「データ｣｢フィルタ｣｢オートフィルタ｣にチェックを入れる。 マウスポインタは表の中に入れてから行ってください。 (shota)
こんばんは 例えば 90点台なら =SUMPRODUCT((B1:B5<100)*(B1:B5>90)*(B1:B5)) (oni)
C1に入れる式 =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()))) として、Shift+Ctrlキー押しながらEnterキーで確定させて配列数式にします。 あとは、下方向にコピー
（川野鮎太郎）
なぜでしょう (shota)
回答者の皆さんは、データが A1:B5 にあるものとして、回答をされていますが！ Ｋさんの実際のデータはどのにあるのでしょうか？ (pockey)
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
という具合にセル番地も変えていますが、
なぜか大きなセル番地になると反応してくれません。
何故でしょうか
(shota)
配列に関する過去ログです。 [[20050212191618]]『理解に苦しんでます』（頭いたい子）
（川野鮎太郎）
A列に名前でC列に点数で、５行目からですね！ =IF(COUNTIF($C$5:$C$9,">=90")<ROW(A1),"",INDEX($A$5:$A$9,SMALL(IF($C$5:$C$9>=90,ROW($C$5:$C$9)-ROW($C$4)),ROW(A1)))) 配列数式として確定は同じです。
★IF(COUNTIF($C$5:$C$9,">=90")<ROW(A1),"" 『C5:C9の範囲で90以上の数がROW(A1)=1より小さい』という条件に対し、 TRUEならば空白を返し、 ※""を空白と表現しましたが、何も入力しない文字列です。 何も入力していないC1に対し、D1セルに =TYPE(IF(C1="","",1)) としてC1に入力したりDeleteしたりして試してください。 結果は、TYPE関数のEXCELヘルプでご確認下さい。
FALSEならば↓
★INDEX($A$5:$A$9,SMALL(IF($C$5:$C$9>=90,ROW($C$5:$C$9)-ROW($C$4)),ROW(A1))))
INDEXの配列A5:A9に対し、
C5:C9が90以上ならばその行ナンバーからROW($C$4)=4を引いた値の中で、
一番小さい値（ROW(A1)=1)の順番にあるものを返す。
※今回、IF関数のFALSE部分の指定をしていないので、FALSEの場合はFALSEと返りますが、
実際には、最初のIF(COUNTIF($C$5:$C$9,">=90")<ROW(A1),""により、""が返されています。
INDEX関数は、配列内の上端が1、左端が1になり、この行（列）位置を指定することにより該当するものが返されます。 例えば、A1:B2の配列に対し、B1を返したい場合は、 =INDEX($A$1:$B$2,1,2) となります。 従って、範囲の一番目A5位置【ROW(C5)=5】を１にするために【ROW($C$4)=4】を引いています。単純に-4としても当然OKです。 これもINDEX関数のヘルプを一読下さい。
また、全体の式に中に''相対参照($の付いていないセル指定)''になっている ROW(A1) 部分は、 下にコピーする事により1,2,3・・・という値を得るためにあります。 これを右にコピーする場合は、COLUMN(A1) に置き換えれば、同様に1,2,3・・・を得る事が出来ます。 鮎太郎さんが当初 A1 部分を省略しているのは、 >C1に入れる式 という、条件を提示していますから、当然この場合にROW()は、ROW(C1)の意なのです。 >実際の表では、氏名がＡ５から入力されているのですが、そのことがなにか影響するでしょうか。 に関しては、大いに影響します。 実感されてますよね？
指定が無い場合、回答される皆さんは○○の範囲として但し書を入れているか、 暗黙の了解として、A1から始まる範囲での回答をされています。 必要性を判断できない場合等には、シート名やセル番地・諸条件は、必ず書き込んだ方がお互いに時間の節約になります。 ただし、必ず提示された条件通りの回答が付くとは限りません。 手取り足取りだと自分のものにしようとする努力を惜しむ方もいらっしゃる様ですから・・・
$$に関しては、修正されていますので、ご確認下さい。 (sin) 拙い説明でごめんなさいね。ペコリ〜
shotaさん、これだとどうでしょう？？？
Ａ Ｂ Ｃ a b c d
5 田中 100 4<1=FALSE TRUE→5-4=1 SMALL({1,2,3,4},1)=1 INDEX($A$5:$A$9,1)=A5
6 吉田 95 4<2=FALSE TRUE→6-4=2 SMALL({1,2,3,4},2)=2 INDEX($A$5:$A$9,2)=A6
7 広瀬 98 4<3=FALSE TRUE→7-4=3 SMALL({1,2,3,4},3)=3 INDEX($A$5:$A$9,3)=A7
8 黒木 100 4<4=FALSE TRUE→8-4=4 SMALL({1,2,3,4},4)=4 INDEX($A$5:$A$9,4)=A8
9 今岡 75 4<5=TRUE FALSE
a：IF(COUNTIF($C$5:$C$9,">=90")<ROW(A1) 部分 通常の関数式と同じです。COUNTIFの結果『4』と、表示したい上からの順番（数）とを比較しています。
b：IF($C$5:$C$9>=90,ROW($C$5:$C$9)-ROW($C$4)) 部分 IF($C$5>=90,ROW($C$5)-ROW($C$4))という式の 『範囲C5』を『範囲C5:C9』に広げて一度に計算結果を保持する(?)為に配列数式にしています。
c：SMALL(IF($C$5:$C$9>=90,ROW($C$5:$C$9)-ROW($C$4)),ROW(A1)) 部分
『 {1,2,3,4} 』は配列定数ですが、bの配列数式の結果を受けたものです。
d：INDEX($A$5:$A$9,SMALL(IF($C$5:$C$9>=90,ROW($C$5:$C$9)-ROW($C$4)),ROW(A1))) 各セルの1,2,3,4は、cの結果です。
配列数式にしない場合には、 D5セルにbの部分にも書いた、 =IF($C$5>=90,ROW($C$5)-ROW($C$4)) を入力しD9まで下方コピーすると、 表の結果と同じ 1,2,3,4,FALSE が各セルに返るはずです。その結果を利用して、 =INDEX($A$5:$A$9,SMALL($D$5:$D$9,ROW(A1))) とすれば配列数式ではない方法で求める事が出来ます。 ※FALSE部分でエラーが出ると思いますが・・・ (sin)
90以上、100未満で出してしまいました。これで応用してください。 =IF((COUNTIF($C$5:$C$9,">=90")-COUNTIF($C$5:$C$9,">=100"))<ROW(A1),"",INDEX($A$5:$A$9,SMALL(IF(($C$5:$C$9>=90)*($C$5:$C$9<100),ROW($C$5:$C$9)-ROW($C$4),10^9),ROW(A1)))) 配列数式で確定です。 90以上から100以上の数を引いてます。 AND関数と配列数式は相性が悪かったと思うので、掛けてみました。TRUE=1,FALSE=0を元に計算します。 (sin)
>理解できませんでした。 これでは、何が理解できないのか 理解できません。 理解する為の一つの方法として、上記で私が書いている表部分をご自分で実際に入力して試されては如何でしょう？ そして、数式バー上で関数にカーソルを合わせ、左にあるfx(もしくは =)をクリックして出てくる窓で確認したり、 [ツール]-[ワークシート分析]-[数式の検証]を使ってみては如何でしょうか？ (sin)
sinさん、フォローありがとうございます。(o_ _)ｏ))
（川野鮎太郎）
A B C
1 田中 70 =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
というふうにセルA1からだと出来るのに
AA AB AC
31102 田中 70 =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())))
31103 吉田 95
31104 広瀬 98
31105 黒木 100
31106 今岡 75
セル番地を変えると名前が表示できなくなるんです。
=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())))
この式をどう変えたら出来るのかわからないんです。
sinさんがこと細かくご指導してくださいましたがいまいちわからないんです。
大変あつかましいお願いですけど、式を書き換えていただけないでしょうか。
完全にお手上げの状態です。
(shota)
ところで以前Kさんからこんなのがありました 「たびたびスミマセン。この数式を利用して、例えば８０点から９０点の間の人を抽出する事はできますか。チャレンジしたのですが、サッパリでした。（Ｋ）」 私も知りたいです。どうか今一度お力をお貸しください。 お願いします。 (shota)
A B C 1 田中 70 吉田 2 吉田 85 広瀬 3 広瀬 88 4 黒木 100 5 今岡 75
C1=IF(SUMPRODUCT(($B$1:$B$5>=80)*($B$1:$B$5<=90))<ROW(),"",INDEX($A$1:$A$5,SMALL(IF(($B$1:$B$5>=80)*($B$1:$B$5<=90),ROW($A$1:$A$5)),ROW())))
AA AB AC 31102 田中 70 吉田 31103 吉田 85 広瀬 31104 広瀬 88 31105 黒木 100 31106 今岡 75
AC31102=IF(SUMPRODUCT(($AB$31102:$AB$31106>=80)*($AB$31102:$AB$31106<=90))<ROW(A1),"",INDEX($AA$1:$AA$31106,SMALL(IF(($AB$31102:$AB$31106>=80) *($AB$31102:$AB$31106<=90),ROW($AB$31102:$AB$31106)),ROW(A1)))) (Maron)
AC31102=IF(SUMPRODUCT(($AB$31102:$AB$31111>=80)*($AB$31102:$AB$31111<=90))ROW(A1),"",INDEX($AA$31102:$AA$31111,SMALL(IF(($AB$31102:$AB$31111>=80)*$AB$31102:$AB$31111<=90),ROW($AB$31102:$AB$31111)),ROW(A1)))) 色々と考える範囲で数字を書き換えたりしたんですがなぜかエラーになります。 Maronさんお手数でしょうが、今一度教えてください。 宜しくお願いします。 (shota)
式の意味（数式内の各関数の意味）を理解されていないから出来ないのです。 =IF(SUMPRODUCT(($AB$31102:$AB$31111>=80)*($AB$31102:$AB$31111<=90))<ROW(A1),"",INDEX($AA$1:$AA$31111,SMALL(IF(($AB$31102:$AB$31111>=80)*($AB$31102:$AB$31111<=90),ROW($AB$31102:$AB$31111)),ROW(A1))))
（川野鮎太郎）
◆川野鮎太郎さんの式と、shotaさんの式を比べてください！ ◆違うところがありますよね！！ (Maron)
完璧に出来ました。 式の意味を今一度ひもほどいで理解に勤めようと思っております。 どうもありがとうございます。 涙が出そうなほど嬉しいです。 今後ともご指導をお願いします。 (shota)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.