EXCEL関数でフィルタの様にデータ抽出したい

質問

以下のサイトで
http://okwave.jp/qa/q6674262.html
ベストアンサーに選ばれている方法だと古いものから順に抽出されるのですが、新しいものから古い方へ抽出させるにはどうすればよいのでしょうか？
small関数をlarge関数に変えたり、×1000を1／1000にしたりしたのですが、上手く行きません。
ご教示下さい。

回答No.5

bunjii · ベストアンサー

>ベストアンサーに選ばれている方法だと古いものから順に抽出されるのですが、新しいものから古い方へ抽出させるにはどうすればよいのでしょうか？
提示のリンクでは下記の数式で行番号の小さい順に抽出していますので最小の修正ではSMALL関数をLARGE関数に代えて演算式を整理すると次の数式になります。
=INDEX(データ!A:A,SMALL(INDEX((データ!$B$1:$B$100<>$B$2)*1000+ROW(A$1:A$100),),ROW(A1)))&""
　　　　　　　↓
=INDEX(データ!A:A,LARGE(INDEX((データ!$B$1:$B$100=$B$2)*ROW(A$1:A$100),,1),ROW(A1)),1)

解説
小さい順に抽出するときでも次のような考え方をしたいのですが、除外すべき行番号が0になりますのでSMALL関数で抽出するには不都合が起り正しい抽出ができません。
=INDEX(データ!A:A,SMALL(INDEX((データ!$B$1:$B$100=$B$2)*ROW(A$1:A$100),,1),ROW(A1)),1)
それを避けるために除外すべき行番号に大きな値を加算して小さい値から順に抽出する方法を考えたものと思われます。
=INDEX(データ!A:A,SMALL(INDEX((データ!$B$1:$B$100<>$B$2)*1000+ROW(A$1:A$100),),ROW(A1)))&""

お礼

明快なご回答ありがとうございました。smallをlargeにするだけではいけないのですね。ばっちり抽出できました。なぜだめだったのか、独学してみます。ありがとうございました。

回答No.4

tom04

No.2・3です。
たびたびごめんなさい。

今までの回答は無視してください。
単にデータを下から上に並び替えていただけです。

もう一度画像をアップします。
今回は作業用の列を設けていますので、配列数式にしなくて大丈夫です。

↓の画像でSheet1のE列を作業列とし、E2セルに
=IF(A2="","",A2+1/ROW())
という数式を入れずぃ～～～！っと下へコピーしておきます。

そして、Sheet2のA2セルに
=IFERROR(INDEX(Sheet1!A:A,MATCH(LARGE(Sheet1!$E:$E,ROW(A1)),Sheet1!$E:$E,0)),"")
という数式を入れ、列・行方向にフィルハンドルでコピー！
（A列の表示形式は「日付」とし）
これで画像のような感じになります。

何度も失礼しました。m(_ _)m

お礼

ご回答ありがとうございます。私には難解な数式で、少しずつ勉強させていただきます。何度もありがとうございました。

回答No.3

tom04

No.2です。
投稿後に気づきました。
配列数式の行合わせ部分がおかしいですね。

↓の式に訂正してください。

=IFERROR(INDEX(Sheet1!A$2:A$1000,LARGE(IF(Sheet1!$A$2:$A$1000<>"",ROW($A$2:$A$1000)-1),ROW(A1))),"")

どうも失礼しました。m(_ _)m

回答No.2

tom04

こんばんは！

No.1さんが回答されているようにA列の降順で並び替えをすれば一発で解決だと思いますが・・・
どうしても関数での方法をご希望というコトであれば一例です。

↓の画像のようにSheet1のデータをSheet2に表示するようにしてみました。
お示しのURLの方法とは少し異なりますが・・・

画像ではSheet2のA2セルに
=IFERROR(INDEX(Sheet1!A$2:A$1000,LARGE(IF(Sheet1!$A$2:$A$1000<>"",ROW($A$2:$A$994)-1),ROW(A1))),"")
これは配列数式になりますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　Sheet2のA2セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。
このA2セルをD2セルまでフィルハンドルでコピー！
A列がシリアル値になりますので、好みの表示形式にしておきます。

最後にA2～D2セルを選択　→　D2セルのフィルハンドルで下へコピー！
これで画像のような感じになります。m(_ _)m

回答No.1

shintaro-2

何故、関数でなければならないのですか？

関数は手段の一つであって、関数を使うことが目的ではありません。
フィルタやソートで簡単にできるのであれば、それを使うのがベストです。

もしやるのであれば、RANK関数で順位を出して、matchで行番号を出して、それを基に表示するということでしょう。

お礼

ご回答ありがとうございます。
なぜフィルターを使わず関数なのか？という点ですが、
私の場合は、およそ1,000社から目的の会社をフィルタで選ぶのが手間だからです。
フィルタータブ？で目的の会社を探すだけで苦労しています。それも1社でなく複数行わなければいけないのです。なので特定のセルに目的の会社名を入れると関数で抽出してくれる方法がほしいのです。
「目的の会社名を入れるのが面倒だろう」と思われるかもしれませんが、そこは、この分について抽出せよとの指示が来るので問題はないのです。