(MS-Excel)複数の条件にマッチする複数のセルを配列？で出力させ

質問

(MS-Excel)複数の条件にマッチする複数のセルを配列？で出力させたい。
たとえば、条件1=A列で値が数値であるセルと、条件2=条件1でマッチしたセルと同じ行でC列で値が「その他」という文字列であるセルを検索させます。
そして、A列でマッチしたセルの値をE列に出力させて、C列でマッチしたセルの値をF列に出力させたい、というような感じです。
VLOOKUP関数などで、{配列}を使うなどすれば可能だと思って試行錯誤したけどダメでした。
なお、データベース関数のように条件セルなど作業セルが必要な方法は使いたくないです。
よろしく、お願いします。

回答No.4

KURUMITO · ベストアンサー

回答Ｎｏ1です。
空白の行を作らないで表示させるのでしたら次の式をＥ1セルに入力して入力後はCtrl+Shift+Enterで確定します。その後に下方にオートフィルドラッグします。
=IF(SUM(ISNUMBER(A$1:A$100)*NOT(ISERROR(C$1:C$100="その他"))*NOT(ISBLANK(C$1:C$100))*1)<ROW(A1),"",INDEX(A:A,SMALL(IF(ISNUMBER(A$1:A$100)*(C$1:C$100="その他"),ROW(A$1:A$100),""),ROW(A1))))

回答No.3

MackyNo1

＞A列でマッチしたセルの値をE列に出力させて、C列でマッチしたセルの値をF列に出力させたい、というような感じです。

F列の値は検索条件の「その他」になるのでしょうか？

今回のご質問に対する回答は、いずれにしろ配列数式で表示するしかありません。

たとえばA列が数字でかつC列が「その他」のデータのA列の数字を列挙したいなら以下のような数式を使うことになります。

=INDEX(A:A,SMALL(IF(ISNUMBER($A$1:$A$100)*($C$1:$C$100="その他"),ROW($A$1:$A$100),""),ROW(A1)))

配列数式なので、入力後Ctrl+Shift+Enterで確定してください。

エクセルのバージョンが記載されていませんので、上記の数式はデータがない場合のエラー値を表示しない数式を提示しましたが、Excel2007なら比較的計算負荷が少ないCOUNTIFS関数などを利用することができます。

お礼

ありがとうございます。

回答No.2

hallo-2007

>複数の条件にマッチする複数のセルを配列？で出力させたい。
E列、F列に条件にあった一覧表を上から詰めて表示したい
といったことでしょうか？
作業列を使ってもダメなのでしょうか？

配列関数を利用すれば可能だと思いますが、データの量、式をコピィするセルの数でパソコンに負担をかけます。
とりあえず
A列が数値（文字、空白でない）のセルの一覧表をE列に作成します。
E1セルに
=IF(SUM(ISNUMBER(A$1:A$100)*1)<ROW(A1),"",INDEX(A:A,SMALL(IF(ISNUMBER(A$1:A$100),ROW(A$1:A$100),""),ROW(A1))))
をコピィして、Ctrl+Shift+Enterで決定して配列関数にします。
E1セルを下までコピィします。
A列の対象範囲は　A1～A100　にしています。
F列は、条件をA列が　数値　且つ　C列が　その他　で関数を組むことになりますが、かなり長くなります。
今の式でも、理解困難であれば、別方法をお勧めします。
意外と、理解しやすい方法は　作業列を使ったほうがわかりやすいし
パソコンもかるくなると思います。

お礼

ありがとうございます。
簡単ではないにしても、もっと短い数式で可能だと思ってました。
数式で済ますのは、チョー非合理というか非効率ですね。
なお、質問を間違ったというか、やりたいことはもっと複雑です。
作業セルを使う方法で、考えてみます。

回答No.1

KURUMITO

条件1と条件2を満足する行のＡ列での値をＥ列に表示させるのでしたら次の式をＥ1セルに入力し下方にオートフィルドラッグします。
=IF(AND(ISNUMBER(A1),C1="その他"),A1,"")

補足

ありがとうございます。
これって、空白のセルや行が発生しますよね。
フィルターで解決できますが、そういうのは無しで、数式だけでやりたいです。