エクセル 表のなかから条件に合うものを詰めて一覧にする

質問

ある表のなかから条件に合うものリストを
詰めて一覧にするにはどういった関数などを
つかってどういう風に組めばよいでしょうか？

・ＶＢ等プログラム使用不可
・手動でのソートや抽出はしない
・「詰めて」が最も大きなポイント

例

シート１

　　|　　A　　　B　　　C　　　D　
---------------------------------
01　|　　あ　　○　　　　　　58
02　|　　あ　　×　　　u　　　4
03　|　　あ　　☆　　　o　　　2
04　|　　　　　×　　　　　　87
05　|　　う　　　　　　j　　　7
06　|　　あ　　○　　　k
07　|　　い　　　　　　n　　　9
08　|　　い　　×　　　d　　223
09　|　　あ　　○　　yua　　　0
10　|　　　　　　　　　　　　66
11　|　　い　　☆　　　j　
12　|　　う　　×　　　o　　　2
13　|　　う　　　　　　t　　　2
14　|　　あ　　○　　ykd　　651
15　|　　い　　☆　　　h　　　6
16　|
17　|
18　|
19　|
20　|

上記の中から　
A列が「あ」かつ
B列が「○」かつ
D列が空白でないものを
詰めて別シートで下記のような
リストに自動的にしたい。

自動的・・・
・シート１の16以降の行に追加しても
　自動的にシート２に詰めて反映される。
・シート１いずれかの行を削除しても
　条件にあったリストがシート２に
　反映される。

シート２　　（完成）

　　|　　A　　　B　　　C　　　D　
---------------------------------
01　|　　あ　　○　　　　　　58
02　|　　あ　　○　　yua　　　0
03　|　　あ　　○　　ykd　　651
04　|
05　|
06　|
07　|
08　|
09　|
10　|
11　|
12　|
13　|
14　|
15　|

もちろん、計算のための隠し列や隠しシートを儲けるなどは可。

エクセルの関数の駆使だけではムリなのでしょうか？

どうかご教授くださいませ。

回答No.6

maron--5 · ベストアンサー

◆参考◆配列数式｛｝付の回答
=IF(ROW(A1)>SUMPRODUCT((Sheet1!$A$1:$A$20="あ")*(Sheet1!$B$1:$B$20="○")*(Sheet1!$D$1:$D$20<>"")),"",INDEX(Sheet1!A$1:A$20,SMALL(IF((Sheet1!$A$1:$A$20="あ")*(Sheet1!$B$1:$B$20="○")*(Sheet1!$D$1:$D$20<>""),ROW(Sheet1!$A$1:$A$20),""),ROW(A1)))&"")
★この式は「配列数式」です。式を入力後、Ctrl＋Shift＋Enter　をおして、式を確定させてください。
★確定すると、式の両端に｛　｝がつきます。

◆前回の回答は、Ctrl＋Shift＋Enter　で式を確定させるのを、Enter　だけで式を確定させるために変形した式です
★それが、INDEX　であり、SUBSTITUTE　です

◆なお、式の最後の　)&""　は、データが無いときに、「0」表示されることを回避するためです
◆本来は、結果が文字列の場合は良いのですが、数値の場合は文字列になるので、この結果から再度計算するようなケースではおすすめではありません

◆配列数式の解説されたURLを添付しておきます

参考URL：

http://pc.nikkeibp.co.jp/pc21/special/hr/index.shtml

回答No.5

maron--5

>どうしてもわからない一点が、２つめのINDEXは　ＩＮＤＥＸ（○，）と言う形式になっているので○の値そのものを指し、
>一見 要らないような気がするのですがなぜ必要なのでしょうか？

SUMPRODUCT((Sheet1!$A$1:$A$20="あ")*(Sheet1!$B$1:$B$20="○")*(Sheet1!$D$1:$D$20<>""))
◆上の部分は、対象の個数を計算しています、その個数を上回ると、""　にするようにしています

◆zenyasaiさんのEXCELが、2002？か2003以上であれば、その数式のセルを指定して、メニューバーの［ツール］－［ワークシート分析］－［数式の検証］をためしてください

補足

再度回答いただきありがとうございます。

>どうしてもわからない一点が、２つめのINDEXは　ＩＮＤＥＸ（○，）と言う形式になっているので○の値そのものを指し、
>一見 要らないような気がするのですがなぜ必要なのでしょうか？

ですが、少し、私の質問のしかたがまずかったようです。

ＩＮＤＥＸ文は２回出現していますが、２つ目（内側の）の方は
INDEX(SUBSTITUTE((Sheet1!$A$1:$A$20="あ")*(Sheet1!$B$1:$B$20="○")*(Sheet1!$D$1:$D$20<>""),0,10^5)*ROW(Sheet1!$A$1:$A$20),)
であり
つまり
INDEX(SUBSTITUTE(yyy)*row(zzz)、) の形式で
さらにつまり
INDEX(ｘｘｘ、)の形式なので
この値はＸＸＸそのものの値であり
INDEX(SUBSTITUTE(yyy)*row(zzz)、) 　＝　SUBSTITUTE(yyy)*row(zzz)
のような気がする・・・
（実際この式だけを行きだし別セルで入れると同じ値になる。
　しかし、当初のＩＦから始まる全文でこの２つめのＩＮＤＥＸ
　だけをカットすると、確かにエラーにはなってしまう。）

と言った意味でした。

検証についても試してみます。

またよろしくお願いします。

回答No.4

noname#204879

Sheet2 において、
１．セル A1 に次の配列数式を入力
　　{=INDEX(Sheet1!A$1:A$20,SMALL(IF((Sheet1!$A$1:$A$20="あ")*(Sheet1!$B$1:$B$20="○")*(Sheet1!$D$1:$D$20<>""),ROW($1:$20),""),ROW()),1)}
２．セル A1 を選択して、次の[条件付き書式]を設定
　　　　数式が　　　　=ISERROR(A1)
　　　　フォント色　　白
３．セル A1 を範囲 B1:D1 に複写
４．セル C1 を選択して F2キーを一発叩く
５．末尾に「&""」を追加入力した後、Ctrlキーおよび Shiftキーを抑えたまま
　　Enterキーを「エイヤッ！」と叩き付けて、結果的に次の配列数式を入力
　　C1: {=INDEX(Sheet1!C$1:C$20,SMALL(IF((Sheet1!$A$1:$A$20="あ")*(Sheet1!$B$1:$B$20="○")*(Sheet1!$D$1:$D$20<>""),ROW($1:$20),""),ROW()),1)&""}
４．範囲 A1:D1 を下方にズズーッと複写

補足

回答ありがとうございます。

教えていただいた式の仕組みと意味を理解し
応用が利くように解析中です♪

理解できたのち、あらためてお礼返信させていただきます♪

追伸
何も考えずにとりあえず実行してみましたところ
うまくいきませんでした。

回答No.3

maron--5

◆説明が漏れていました
◆先程の式を、右と下にコピーです

回答No.2

maron--5

◆別のシートならば
Sheet2のA1の式
A1=IF(ROW(A1)>SUMPRODUCT((Sheet1!$A$1:$A$20="あ")*(Sheet1!$B$1:$B$20="○")*(Sheet1!$D$1:$D$20<>"")),"",INDEX(Sheet1!A$1:A$20,SMALL(INDEX(SUBSTITUTE((Sheet1!$A$1:$A$20="あ")*(Sheet1!$B$1:$B$20="○")*(Sheet1!$D$1:$D$20<>""),0,10^5)*ROW(Sheet1!$A$1:$A$20),),ROW(A1)))&"")

★少し式が長いですが

お礼

おかげさまでなんとか式の意味・理屈が解りました。

どうしてもわからない一点が、
２つめのINDEXは　ＩＮＤＥＸ（○，）と言う形式になっているので
○の値そのものを指し、一見 要らないような気がするのですが
なぜ必要なのでしょうか？
（とはいえ実際にその２つ目のＩＮＤＥＸを外すとエラーになる事実は
　試しています。でもなぜだかわかりません。）

これについては、「何について」を勉強すればよいのでしょうか？

よろしければ引き続きご教授願います♪

補足

回答ありがとうございます。

教えていただいた式の仕組みと意味を理解し
応用が利くように解析中です♪

理解できたのち、あらためてお礼返信させていただきます♪

回答No.1

chukenkenkou

>エクセルの関数の駆使だけではムリなのでしょうか？

無理です。
関数とは何か分かっていませんね。

関数とは、関数を入力したセルの値を、与えられた引数で決めるものです。したがって、行の追加削除、列の追加削除など、他の行や列に対して作用させるものではありません。

お礼

早い回答ありがとうございます♪

関数とは何か・・・
すみません、十分に理解しております。
ただ関数について全てを知るわけではないので

指定範囲内の指定列で条件に合ったもののうち
ｎ番目を抽出・・・

なんてことが出来る関数がないかな・・・
あるいは、関数をいくつか組み合わせて
計算エリアも設けて、結果的にそのようなことが
できないかな・・
あるいはまた
VLOOKUPやMATCH、CHOOSEなんていう結構なものも
あるぐらいなのでそのようなものが
あるかな・・・・
と思いまして。

やはりむりですか？