エクセル関数を教えてください.3

先日教えていただいたエクセル関数ですが応用して試してみましたができませんでした。
以前質問した表は
グループ　名前　　作成月
A 　　　　浅井　　１
A 　　　　伊藤　　３
Ａ　　　　小林　　１
Ｂ　　　　久野　　３
Ｂ　　　　酒井　　１
Ｃ　　　　服部　　３
Ｃ　　　　林　　　１
ですが・・・
グループ　日付　名前　　作成月
A 　　　　9/10　浅井　　１
A 　　　　9/25　伊藤　　３
Ａ　　　　6/8　　小林　　１
Ｂ　　　　2/7　　久野　　３
Ｂ　　　　2/28　酒井　　１
Ｃ　　　　4/10　服部　　３
Ｃ　　　　4/15　林　　　１
と間に日付の列がある状態でも
グループＡかつ作成月１を抽出
浅井
小林　
は可能でしょうか？（※この場合日付は抽出しません。実際の表は抽出したい列以外の内容の列がたくさんあります）

また、
グループ　名前　　作成月
A 　　　　浅井　　１
A 　　　　伊藤　　
Ａ　　　　小林　　１
Ｂ　　　　久野　　３
Ｂ　　　　酒井　　１
Ｃ　　　　服部　　３
Ｃ　　　　林　　　
のように作成月など空欄がある場合でも数式はかわりないでしょうか？
質問の意図が分かりにくいかもしれませんがよろしくお願いします。

No.1 回答者： tom04 回答日時： 2017/09/27 08:29

こんにちは！

https://oshiete.goo.ne.jp/qa/9954043.html

の関連質問ですね。
あちらの方法は最初から配列数式を使うようにしていましたが、条件がどんどん増えてきていますので
一気に！となると配列数式そのものがやたらと長くなり、数式の修正も難しくなります。
そこで作業用の列を設ける方法をおススメします。

↓の画像のような配置でSheet2の1行目に表示したい項目名を入れておき、そのデータのみを抽出するとします。
Sheet1のF列に作業列を設けます。
作業列F2セルに
=IF(AND(COUNTIF(Sheet2!E:E,A2),COUNTIF(Sheet2!F:F,D2)),ROW(),"")
という数式を入れこれ以上データは増えない！という位まで下へフィル＆コピーしておきます。

そしてSheet2のA2セルに
=IFERROR(INDEX(Sheet1!$A:$D,SMALL(Sheet1!$F:$F,ROW(A1)),MATCH(A$1,Sheet1!$A$1:$D$1,0)),"")

という数式を入れ、フィルハンドルで列・行方向にコピー！
これで画像のような感じになります。

※　今回、配列数式は一切ありません。
※　Sheet2の1行目項目数が増えても対応できます。
※　「グループ」・「作成月」の検索条件が増えても大丈夫です。
※　作業列が目障りであれば
遠く離れた列にするか、非表示にしてください。m(_ _)m

この回答へのお礼

何度も回答いただきありがとうございます。
まず以下のように抽出したいと考えています。
グループ　Ａ　　Ｂ　　Ｃ　　Ａ　　Ｂ　　Ｃ
作成月　　１　　１　　１　　３　　３　　３
　　　　浅井　酒井　　林　伊藤　久野　服部
　　　　小林
その後上記のようなこともやりたいのですが・・・・

お礼日時：2017/09/27 22:53

No.2 回答者： tom04 回答日時： 2017/09/28 08:25

No.1です。

結局一覧表のような形で表示したい！というコトでしょうか？
そうなると作業列を票の数だけ作るのも大変なので、やっぱり配列数式ですかね。

↓の画像ではSheet2のB2セルに
=IF(COUNTBLANK(B$1:B$2),"",IFERROR(INDEX(Sheet1!$C$1:$C$1000,SMALL(IF((Sheet1!$A$1:$A$1000=B$1)*(Sheet1!$D$1:$D$1000=B$2),ROW($A$1:$A$1000)),ROW(A1))),""))

配列数式です。
としてフィルハンドルで列・行方向にコピーしています。m(_ _)m

この回答へのお礼

ありがとうございます。
配列数式の確認ですが・・・・
SMALL・・・・＝B$1の「B$1」はSheet2の「Ａ」の解釈でよいでしょうか？
また、ROW(A1)・・・の「A1」はSheet2の「グループ」と解釈してよいでしょうか？
何度もすみませんがよろしくお願いします！

お礼日時：2017/09/29 23:32

No.3 回答者： tom04 回答日時： 2017/09/30 00:17

続けてお邪魔します。

少し前回の数式の説明を！

＞＝B$1の「B$1」はSheet2の「Ａ」の解釈でよいでしょうか？
はい！画像ではB1セルを参照していますので、「A」です。
余談として、次の
＞Sheet1!$D$1:$D$1000=B$2
の「B$2」は「1」であるコトは判りますよね？

すなわち両条件を掛け合わせていますので、「AND条件」になり、
「A列でTRUE」×「D列でTRUE」の行だけを表示させたいのです。

Sheet1のA1～A1000で「A」の場合が「TRUE」それ以外は「FALSE」となります。
Excel的にTRUE＝1　FALSE＝０　ですので、両方の条件に合致する行が「TRUE」となります。
前回の画像ではSheet1の「浅井」と「小林」の行だけが「TRUE」=1となり、他の行はすべて「FALSE」=0　となります。
かつ、その次の
＞ROW($A$1:$A$1000)
で行番号を掛け合わせたことになるので、Sheet1の2・4だけがIF関数の「真の場合」になります。

最後に「真の場合」の小さい順に表示させるためにSMALL関数を使い
ROW(A1)　としています。
これをフィルハンドルで下へコピーすると
ROW(A1)＝1　→　ROW(A2)=2　→　ROW(A3)=3　・・・
と変化するので
TRUEの場合のINDEX関数で範囲指定した「真の場合」の1番目の行　→　2番目の行　→　3番目の行・・・
と表示されます。

画像では3番目がないのでエラーになるのですが、
IFERROR関数を使ってエラーの場合は空白にするようにしています。

以上が前回の数式の説明になりますが
ご理解いただけたでしょうか？

一気に理解するのは難しいかもしれませんが、
前回の配列数式の意味はこんな内容です。

※　配列数式はPCにかなりの負担を掛けます。
極端に広い範囲（列全体など）すると計算速度が非常に落ちるので
とりあえず範囲を1000行程度にしています。m(_ _)m

No.4 回答者： tom04 回答日時： 2017/09/30 10:33

何度もごめんなさい。

もう一度投稿文を読み返してみて・・・
なかなか内容が理解できないと思います。
↓の画像でもう一度説明します。

結局E～I列の操作を一気にやっているのが前回の配列数式です。

E列　→　E2セルに
=A2=E$1
として下へフィル＆コピーしています。
これでA列が「A」の場合の行が「TRUE」、それ以外は「FALSE」になります。

F列　→　F2セルに
=C2=F$1
とし下へフィル＆コピー
これでC列が「1」の行が「TRUE」それ以外は「FALSE」となります。

G列　→　G2セルに
=E2*F2
としフィル＆コピー
これでA列が「A」なおかつC列が「1」の行が「1」となります。

H列　→　H2セルに
=IF(G2=1,ROW(),"")
とし、フィル＆コピー！
これで両条件が一致する行番号が表示されます。
=ROW()　とどこか使っていないセルに入れて下へフィル＆コピーしてみてください
その行の番号が表示されます。

最後にI2セルに
=INDEX(B:B,SMALL(H:H,ROW(A1)))
とし下へフィル＆コピー！
これで２・4行目のデータが表示され後はエラーになります。
このエラーはIFERRO関数で表示しないようにしていました。

前回の質問にあった
=ROW(A1)
の部分はSMALL関数の「順位」の部分に当たりますので、
=ROW(A1)　は「1番目」　→　=ROW(A2)は「2番目」・・・という意味です。

※　このA1に関しては　=ROW(B1)　とか　=ROW(Z1)　のようにどこの列にしても必ず「1」となります。

以上長々と書きましたがご理解いただけたでしょうか？

とりあえず
この画面から前回の数式をドラッグ　→　コピー　→　Sheet2のB3セルを選択　→　数式バー内に貼り付け　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定してみてください。
数式の前後に{ }マークが入り配列数式になります。m(_ _)m

この回答へのお礼

丁寧な説明ありがとうございます。
数式の意味合いは大分理解はできましたm(_ _)m
実際にやってみたのですが・・・「＃NAME?」が出てきてしまいました。
どうしたらいいでしょうか？((+_+))

お礼日時：2017/09/30 17:50

No.5 回答者： tom04 回答日時： 2017/09/30 19:45

＞「＃NAME?」が出てきてしまいました。

もしかしてお使いのバージョンはExcel2003以前なのでしょうか？
もしそうであればIFERROR関数は使えませんのでそのような表示になるコトがあります。

Sheet2のB3セルの数式を
=IF(SUMPRODUCT((Sheet1!$A$1:$A$1000=B$1)*(Sheet1!$D$1:$D$1000=B$2))<ROW(A1),"",INDEX(Sheet1!$C$1:$C$1000,SMALL(IF((Sheet1!$A$1:$A$1000=B$1)*(Sheet1!$D$1:$D$1000=B$2),ROW($A$1:$A$1000)),ROW(A1))))

やはり配列数式なのでCtrl＋Shift＋Enterで確定してください。

今度はどうでしょうか？

※　Excel2007以降のバージョンで同じエラーが表示される場合
関数のアルファベットが違ったりすると同じ表示になる事があります。m(_ _)m

この回答へのお礼

何度も回答いただきありがとうございます。
Excelのバージョンが2003でした。
上記の配列数式でやってみたのですが・・・「#NUM!」が出ました。
ちなみに、元の表でＣ列（日付）のような余分なものがない場合は
=IF(SUMPRODUCT((Sheet1!$A$1:$A$1000=B$1)*(Sheet1!$D$1:$D$1000=B$2))<ROW(A1),"",INDEX(Sheet1!$C$1:$C$1000,SMALL(IF((Sheet1!$A$1:$A$1000=B$1)*(Sheet1!$D$1:$D$1000=B$2),ROW($A$1:$A$1000)),ROW(A1))))
の中でINDEX(Sheet1!$C$1:$C$1000,を省けばいいのでしょうか？

お礼日時：2017/10/02 22:38

No.6 ベストアンサー 回答者： tom04 回答日時： 2017/10/02 23:00

No.5の件について・・・

INDEX関数を省いてはいけません。
INDEX関数で範囲指定した列（C列）の何行目を返すか？という数式なので
そのままの数式で大丈夫のはずです。
（数式そのものは日付列を一切参照していませんので。）

こちらでお礼欄に記載されている数式をそのままコピー＆ペーストし配列数式にしてみたら
ちゃんとNo.2の画像のように表示されました。

一つ気になるのですが、
＞元の表でＣ列（日付）のような余分なものがない場合は・・・
ん？日付列はB列なのでは？
（No.2の画像の配置での数式にしています）

要するに
INDEX関数の範囲に指定するのは「名前」列です。
そして必要条件は「グループ」列と「作成月」の列だけですので、
その二つの列の配列数式にする必要があります。

※　SUMPRODUCT関数で「グループ」×「作成月」が存在しない場合のエラー処理をしてるので
「#NUM!」のエラーが出るコト自体考えられないのですが、
もしかして数式を入れた2行目以降に出ていませんか？
その場合は配列数式になっていない可能性があります。

もう一度配列数式になっているかどうか確認してみてください。m(_ _)m

この回答へのお礼

回答ありがとうございます。日付はＢ列でした。
配列数式が成立しました！
何度もありがとうございました。
またご相談させていただくことがあるかもしれませんがその際はよろしくお願いします。

お礼日時：2017/10/03 22:54