- ベストアンサー
エクセル 表のなかから条件に合うものを詰めて一覧にする
ある表のなかから条件に合うものリストを 詰めて一覧にするにはどういった関数などを つかってどういう風に組めばよいでしょうか？ ・ＶＢ等プログラム使用不可 ・手動でのソートや抽出はしない ・「詰めて」が最も大きなポイント 例 シート１ | 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 | もちろん、計算のための隠し列や隠しシートを儲けるなどは可。 エクセルの関数の駆使だけではムリなのでしょうか？ どうかご教授くださいませ。
- みんなの回答 （6）
- 専門家の回答
質問者が選んだベストアンサー
◆参考◆配列数式｛｝付の回答 =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を添付しておきます
その他の回答 (5)
- maron--5
- ベストアンサー率36% (321/877)
>どうしてもわからない一点が、２つめのINDEXは ＩＮＤＥＸ（○，）と言う形式になっているので○の値そのものを指し、 >一見 要らないような気がするのですがなぜ必要なのでしょうか？ SUMPRODUCT((Sheet1!$A$1:$A$20="あ")*(Sheet1!$B$1:$B$20="○")*(Sheet1!$D$1:$D$20<>"")) ◆上の部分は、対象の個数を計算しています、その個数を上回ると、"" にするようにしています ◆zenyasaiさんのEXCELが、2002？か2003以上であれば、その数式のセルを指定して、メニューバーの［ツール］－［ワークシート分析］－［数式の検証］をためしてください
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 を下方にズズーッと複写
補足
回答ありがとうございます。 教えていただいた式の仕組みと意味を理解し 応用が利くように解析中です♪ 理解できたのち、あらためてお礼返信させていただきます♪ 追伸 何も考えずにとりあえず実行してみましたところ うまくいきませんでした。
- maron--5
- ベストアンサー率36% (321/877)
◆説明が漏れていました ◆先程の式を、右と下にコピーです
- maron--5
- ベストアンサー率36% (321/877)
◆別のシートならば 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は ＩＮＤＥＸ（○，）と言う形式になっているので ○の値そのものを指し、一見 要らないような気がするのですが なぜ必要なのでしょうか？ （とはいえ実際にその２つ目のＩＮＤＥＸを外すとエラーになる事実は 試しています。でもなぜだかわかりません。） これについては、「何について」を勉強すればよいのでしょうか？ よろしければ引き続きご教授願います♪
補足
回答ありがとうございます。 教えていただいた式の仕組みと意味を理解し 応用が利くように解析中です♪ 理解できたのち、あらためてお礼返信させていただきます♪
- chukenkenkou
- ベストアンサー率43% (833/1926)
>エクセルの関数の駆使だけではムリなのでしょうか？ 無理です。 関数とは何か分かっていませんね。 関数とは、関数を入力したセルの値を、与えられた引数で決めるものです。したがって、行の追加削除、列の追加削除など、他の行や列に対して作用させるものではありません。
お礼
早い回答ありがとうございます♪ 関数とは何か・・・ すみません、十分に理解しております。 ただ関数について全てを知るわけではないので 指定範囲内の指定列で条件に合ったもののうち ｎ番目を抽出・・・ なんてことが出来る関数がないかな・・・ あるいは、関数をいくつか組み合わせて 計算エリアも設けて、結果的にそのようなことが できないかな・・ あるいはまた VLOOKUPやMATCH、CHOOSEなんていう結構なものも あるぐらいなのでそのようなものが あるかな・・・・ と思いまして。 やはりむりですか？
補足
再度回答いただきありがとうございます。 >どうしてもわからない一点が、２つめの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) のような気がする・・・ （実際この式だけを行きだし別セルで入れると同じ値になる。 しかし、当初のＩＦから始まる全文でこの２つめのＩＮＤＥＸ だけをカットすると、確かにエラーにはなってしまう。） と言った意味でした。 検証についても試してみます。 またよろしくお願いします。