エクセルで、選手名の管理表を作成する上で、

質問

エクセルで、選手名の管理表を作成する上で、
「出場予定月」と「出場可能の有無」の２条件に該当する選手名を、
自動で表示したいと考えています。
（マクロではなく、通常のエクセル計算を希望）

下記の
＜ファイル内容＞にある、シート2の方法を実現する上で、
＜質問事項＞にご回答できる方がいましたらお教え下さい。

＜ファイル内容＞

■シート１
A列：出場可能の有無（有：●、なし：空欄）
B列：選手名（山田　太郎、佐々木　太郎、・・・）
C列：出場予定月(2010/6、2010/4、・・・)

■シート２
A1:出場予定月（2010/6）
B列：該当月（A1）に出場できる選手名
C列：該当月（A1）に出場できない選手名

＜質問事項＞

１、（最重要）
現状、シート２で、
「出場予定月」に該当する選手名を、
下記の数式で算出しています。

（シート２の計算式）
=IF(COUNTIF(シート1!$C$1:$C$10,$A$1)<ROW(シート1!C1),"",INDEX(シート1!C$1:C$10,SMALL(IF(シート1!$C$1:$C$10=$A$1,ROW(シート1!$C$1:$C$10)),ROW(シート1!C1))))
※上記式を下方向にオートフィル

この「出場予定月」に「出場可能の有無」の条件を加えて、
計算・表示したいのですが、どのようにすればいいでしょうか。

２、（もしわかれば）
シート１の「出場予定月」が、2010/6/3、2010/6/10など、
出場日になっている場合、これを2010/6など、月にまとめる計算をして、
シート２で同様の事を実現できるといいのですが、その方法はありますでしょうか。

＜補足＞
可能であれば、複数のセルを使わずに、
一つのセル内で、一回の計算式から算出したいです。

回答No.1

kybo · ベストアンサー

そもそもなのですが、
=IF(COUNTIF(シート1!$C$1:$C$10,$A$1)<ROW(シート1!C1),"",INDEX(シート1!C$1:C$10,SMALL(IF(シート1!$C$1:$C$10=$A$1,ROW(シート1!$C$1:$C$10)),ROW(シート1!C1))))
の式は質問分とあってません。INDEX(の次の部分の範囲が、C列を参照しているので、出場予定月を返す式になってます。これが間違いなのだとして。

=IF(SUMPRODUCT(((シート1!$C$1:$C$10-DAY(シート1!$C$1:$C$10)+1)=A1)*(シート1!$A$1:$A$10="●"))<ROW(シート1!C1),"",INDEX(シート1!B$1:B$10,SMALL(IF(((シート1!$C$1:$C$10-DAY(シート1!$C$1:$C$10)+1)=A1)*(シート1!$A$1:$A$10="●"),ROW(シート1!$C$1:$C$10)),ROW(シート1!C1))))

という式をいれれば出来るかと思います。

出場できない選手名は「"●"」を「""」として下さい。

お礼

今回は、貴重なアドバイスを頂き、
本当にありがとうございました。

自己解決できそうなので、回答を締切ります。
ご協力頂き、ありがとうございました。

補足

kybo 様

迅速なご回答ありがとうございます。
（最初に私が書いた式は、ご指摘の通り間違いです）

一点追加のご質問になり恐縮ですが、「出場予定月」であるC列が、計算式だった場合、
「#Value!」のエラーになるのですが、対策はありますでしょうか？

＜解決したい内容＞

■シート１
（C列）
=DATE(YEAR(D2),MONTH(D2),1)、=DATE(YEAR(D3),MONTH(D3),1)、・・・・

（D列）
2010/6/6、2010/6/20、・・・・

■シート２
下記の式だと「#Value!」になるため、解決したい。

=IF(SUMPRODUCT(((シート1!$C$2:$C$10-DAY(シート1!$C$2:$C$10)+1)=$A$1)*(シート1!$A$2:$A$10="●"))<ROW(シート1!C2)-1,"",INDEX(シート1!B$2:B$10,SMALL(IF(((シート1!$C$2:$C$10-DAY(シート1!$C$2:$C$10)+1)=$A$1)*(シート1!$A$2:$A$10="●"),ROW(シート1!$C$2:$C$10)-1),ROW(シート1!C2)-1)))

※シート1の1行目にタイトルを入れたため、ご記載頂いた式を少し変えています。

回答No.5

某HN クロメート(Chromate)（@CoalTar）

一部 修正。
さらに条件が付加されるので
J1セルに
=IF(($A$1:$A$10="●")*($C$1:$C$10=$H$1),ROW(A1))
=TRUE*1 の計算結果は 1になること
論理式が数値の場合、0はFALSE、0以外がTRUEとなる

上記数式は完全な間違いではないし、ほしい結果が求められるが
わかりにくいのと説明にそぐわないので
J1セルは
=IF((A1="●")*(C1=$H$1),ROW(A1))
下へオートフィルとします。

お礼

今回は、貴重なアドバイスを頂き、
本当にありがとうございました。

ベストアンサーには、他の方を選びましたが、
ご回答頂いた内容は、大変参考になり、
アドバイスの優劣的な意味で、
ベストアンサーにしなかったわけではありません。
ご了承下さい。

ご協力頂き、ありがとうございました。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

>可能であれば、複数のセルを使わずに、
>一つのセル内で、一回の計算式から算出したいです。
を実現する。および、条件を加えるためには、数式の理解が必要です。
そのためのステップです。

わかりやすくするためには同一シートで作業列を使います。
条件
H1セルに 文字列で 2010/6 と入力するとします。

一致する条件に行番号を振る
D1セルに =IF(C1=$H$1,ROW(A1))
下へオートフィル

行番号を小さい順にする
E1セルに =SMALL($D$1:$D$10,ROW(a1))
下へオートフィル

行番号に対する氏名を返す
F1セルに =INDEX($B$1:$B$10,E1)
下へオートフィル

エラー処理
G1セルに
=IF(COUNTIF($C$1:$C$10,$H$1)<ROW(A1),"",F1)
下へオートフィル

普通に作業列を使うならこのような面倒な方法はとりませんが、
配列数式を理解するうえで必要なので提示しました。

さらに条件が付加されるので
J1セルに
=IF(($A$1:$A$10="●")*($C$1:$C$10=$H$1),ROW(A1))
=TRUE*1 の計算結果は 1になること
論理式が数値の場合、0はFALSE、0以外がTRUEとなる

上記、すべて完全に理解した上で I1セル
=IF(SUM(($A$1:$A$10="●")*($C$1:$C$10=$H$1))<ROW(A1),"",
INDEX($B$1:$B$10,SMALL(IF(($A$1:$A$10="●")*($C$1:$C$10=$H$1),ROW($A$1:$A$10)),ROW(A1))))
[Ctrl]+[Shft] ＋[Enter] で確定、配列数式です({}で囲まれる)

お礼

今回は、貴重なアドバイスを頂き、
本当にありがとうございました。

ベストアンサーには、他の方を選びましたが、
ご回答頂いた内容は、大変参考になり、
アドバイスの優劣的な意味で、
ベストアンサーにしなかったわけではありません。
ご了承下さい。

ご協力頂き、ありがとうございました。

回答No.3

nattocurry

#2 です。
ご記入があったので、訂正します。

「出場予定月」列で、
　　「2010/5/1」「以上」
　　　AND
　　「2010/6/1」「より小さい」
と指定します。

↓

「出場予定月」列で、ドロップダウンリストの中から「オプション」を選択し、
　　「2010/5/1」「以上」
　　　AND
　　「2010/6/1」「より小さい」
と指定します。

お礼

今回は、貴重なアドバイスを頂き、
本当にありがとうございました。

この方法でも、実現できそうですね！

ベストアンサーには、他の方を選びましたが、
ご回答頂いた内容は、大変参考になり、
アドバイスの優劣的な意味で、
ベストアンサーにしなかったわけではありません。
ご了承下さい。

ご協力頂き、ありがとうございました。

回答No.2

nattocurry

お望みの回答ではありませんが・・・

私なら、オートフィルタを利用して、簡単に済ませます。

1行目には適当な項目名を記入します。

列A～列Cの全体を選択して、
データ　－　フィルタ　－　オートフィルタ
を設定します。

「出場可能」列で、ドロップダウンリストの中から「●」を選択。

「出場予定月」列で、
　　「2010/5/1」「以上」
　　　AND
　　「2010/6/1」「より小さい」
と指定します。

すると、出場可能で、出場予定月が2010年5月の人のデータだけが抽出されます。

このやりかたでは、ご不満でしょうか？