【エクセル関数】特定のフラグのデータだけ抜き出す方法について

エクセル2010を使用しています。

「定義」というページの中のA1からC501までに「取引先分類」という名前を付けています。
A列に分類フラグ、B列に取引先番号、C列に取引先名称が記入してあるのですが、A列のフラグの中の特定のフラグのみ抽出して、別のページに表示させたいと思っています。

フラグの種類は4種類あり、1.2.3.9を使っていますが、その中で「2」と「3」と書かれているフラグのB列・C列のみ、別シートのA列・B列に抽出したいと思っています。

これを関数で、と思っているのですがなぜか何度やってもうまくいきません…
どなたか教えていただけないでしょうか？
よろしくお願いします。

No.1 ベストアンサー 回答者： tom04 回答日時： 2015/05/29 20:28

こんばんは！

せっかく名前定義されているようですが、名前定義されている範囲は使いません。

↓の画像のように元データがSheet1にあり、Sheet2に表示するとします。
Sheet2のA2セルに
=IF(COUNTIF(Sheet1!$A:$A,2)+COUNTIF(Sheet1!$A:$A,3)<ROW(A1),"",INDEX(Sheet1!B$1:B$1000,SMALL(IF(ISNUMBER(FIND(Sheet1!$A$1:$A$1000,"2,3")),ROW($A$1:$A$1000)),ROW(A1))))
これは配列数式になりますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は上記数式をドラッグ＆コピー　→　Sheet2のA2セルを選択
→　数式バー内に貼り付け　→　そのまま（編集可能なまま）Ctrl＋Shift＋Enterで確定！
数式の前後に{ }マークが入り配列数式になります。
このA2セルのフィルハンドルで列・行方向にコピーすると
画像のような感じになります。m(_ _)m

この回答へのお礼

ドンピシャの回答で本当に助かりました。
同じような方法で試していたのですが、何時間やってもエラー続きで混乱していました。
おかげさまで何年も使えるシートが完成しそうです！
ありがとうございました。

お礼日時：2015/06/03 15:49

No.3 回答者： esupuresso 回答日時： 2015/06/01 12:00

＞フラグの種類は4種類あり、1.2.3.9を使っていますが、その中で「2」と「3」と書かれているフラグのB列・C列のみ、別シートのA列・B列に抽出したい。

添付画像を参照して戴きまして、若干式をコンパクトにしてみました。
「Sheet2のセルA2」には次の式を入れて、「Ctrl」+「Shift」+「Enter」を押すと、式の前後に｛｝（左右の中括弧）が自動で付きます。
後はB列へ式をコピー、下方向へ式をコピーします。

｛=IFERROR(INDEX(Sheet1!B:B,SMALL(IF((Sheet1!$A$2:$A$1000=2)+(Sheet1!$A$2:$A$1000=3),ROW($A$2:$A$1000)),ROW(A1))),"")｝

抽出は本来オートフィルタ機能を使い、「2」と「3」をフィルタリングして抽出。
データ内のどこでもいいのでクリックしておいて「Ctrl」+「Shift」+「＊（アスタリスク」を押すと抽出したデータ範囲が勝手に選択されますので、「Ctrl」+「C」でコピーしてSheet2に、「Ctrl」+「V」で張り付けた方が格段に早いです。

何でもかんす～♫と言わず、便利なコマンドもあるのですから、多用するのも実務や実践では結構役に立ちますよ。　＾＾

この回答へのお礼

関数以外の方法もいくつかありましたが保護された状態で他の人が見るだけのシートにしたかったので関数でお願いしました(´・ω・`)毎回呼ばれて説明するのが面倒で…(笑)
コンパクトにしてくださってありがとうございます、参考になりました^^回答ありがとうございました。

お礼日時：2015/06/03 15:41

No.2 回答者： hallo-2007 回答日時： 2015/05/30 14:30

＞フラグの種類は4種類あり、1.2.3.9を使っていますが、その中で「2」と「3

一案です。
別シートに
=INDEX(Sheet1!B:B,SMALL(INDEX((Sheet1!$A$2:$A$1000=1)*10000+(Sheet1!$A$2:$A$1000=9)*1000+ROW($A$2:$A$1000),),ROW(A1)))&""
と入れて右へひとつコピー、下へコピーしてみてください。

考え方ですが、１と９のフラグを表示しない様にしてあります。
式の意味が不明でしたら
https://oshiete.goo.ne.jp/qa/8983086.html
を参考にしてみてください。

添付の表をみますと、オートフィルターの設定があるようですが、
色々と条件が変わるようであれば、フィルターの機能で操作した方が
自由度があって便利かと思います。

この回答へのお礼

今回はこれをもとに他にもやりたいことがあったので、1と9を表示しない方法ではなく2と3をどうしても抽出したくて…でも逆の発想はとても参考になりました。またコレ系の関数を使うことがあったら参考にさせていただきます。ありがとうございました。

お礼日時：2015/06/03 15:44