【エクセル2003】入力規則のリストから土日のセルを取り除きたい。

質問

仕事でエクセルを使って、「各営業所の最大繁忙日」データを収集しているのですが、
各営業所の担当者に毎月一番忙しかった日付をエクセルへ入力してもらおうとすると、
「4月13日」や「４／１３」など、ばらばらの書き方になってしまうので、
入力規則のリストを使って、日付を選択する方法にしようと思いました。

既に作成してあるカレンダーを使い、リストを作ることは問題なくできたのですが、
通常のカレンダーなので土日や休業日も入ってしまっています。
せっかくなので、リストから関係のない 営業日以外の日付 を抜き取りたいのですが、
特定のセルだけ抜き取ったリストを作ることは可能なのでしょうか？

いろいろ考えたのですが、作業列を使って、
営業日以外の日付を「－」にしたリストをつくり、
視覚的に土日を選ばないようにするのが限界でした。

【参考】

Q列にカレンダーがあり、Q1は年、Q2は月、Q3～Q33に日付が入っています。
※ Q3には、=DATE($Q$1,$Q$2,1)と入力してあり、Q4にはQ3+1、Q5にはQ4+1・・・と入っていて、
Q1、Q2の数値だけを変えるだけでカレンダー全体が変わるようになっています。
これが上記文章中で言う「通常のカレンダー」になります。

このカレンダーを使い、X3～X33をリスト用カレンダーにするため、
X3以降に下記のように入力し、営業日以外を「－」となるようにしました。

=IF(AND(COUNTIF($V$2:$W$33,$Q3)=0,MOD($Q3,7)<>0,MOD($Q3,7)<>1,MONTH($Q3)=$Q$2),$Q3,"-")

「$V$2:$W$33（祝日リスト）になく土曜でも日曜でもない」＝営業日 で、
かつ、Q2と同じ月の日付を返し、それ以外を「－」と返すことで、
視覚的に該当月の営業日のみを選べるようなリストを作りました。

ドロップダウンリスト例）

4/1
4/2
-
-
4/5
4/6
・
・
・

この「－」の部分を空欄（""）にして、入力規則の「空白を無視する」にチェックを入れれば、
自動的にリストが詰まるのかと思ったのですが、そういったチェックではないんですね・・・。

そのようなリストを作ることは可能なのでしょうか？
分かりにくい説明で申し訳ございませんが、ご教授お願い致します。

回答No.3

mt2008 · ベストアンサー

ANo.1です。
さっきの回答は後からのメンテナンスを考えると面倒でしたね。

営業日のリストを作る方法も……。
W3に　=IF(ISNUMBER(X3),MAX(W$2:W2)+1,"")　と、入れてW33までオートフィル。
次にAA3　に　=IF((ROW()-2)<=MAX(W3:W33),VLOOKUP((ROW()-2),W3:X33,2,FALSE),"")　と入れてAA33までオートフィル。
これで営業日だけのリストが出来ました。

回答No.4

KURUMITO

次のようにしてはどうでしょう。
通常の日付カレンダーがＱ列に有るのですね。そこでリストを作成するためにもう一つ作業列を設けてＸ３セルには次の式を入力して下方にオートフィルドラッグします。

=IF(OR(COUNTIF(V$1:W$100,Q3)>0,WEEKDAY(Q3)=1,WEEKDAY(Q3)=7),"",MAX(X$2:X2)+1)

もう一つ作業列を設け、Ｙ1セルは空白のままでＹ2セルには次の式を入力して下方にオートフィルドラッグします。

=IF(COUNTIF(X:X,ROW(A1))=0,"",INDEX(Q:Q,MATCH(ROW(A1),X:X,0)))

この列がリスト作成の基になります。表示形式を日付で望みの表示形式にします。

リスト作成の際の元の値には=Y:Yと入力すればよいでしょう。

回答No.2

keithin

できるだけ簡単な方法で。

１．ツールメニューのアドインで分析ツールのチェックを入れる(全員）

２．X3セルに
=IF(MONTH(WORKDAY(DATE($Q$1,$Q$2,0),ROW(X1),$V$2:$W$33))<>$Q$2,"",WORKDAY(DATE($Q$1,$Q$2,0),ROW(X1),$V$2:$W$33))
と入れて，X33セルまでコピーする

３．挿入メニューの名前の定義を開始し
　名前　稼働日
　参照範囲　=OFFSET(Sheet1!$X$3,0,NOW()*0,COUNT(Sheet1!$X$3:$X$33),1)
と入れて追加する

４．入力規則を開始し
　種類　リスト
　元の値　=稼働日
　とする

以上です。

回答No.1

mt2008

リストではないですが、入力値の種類を日付にして以下の様な設定を行う事で営業日のみ入力可能に出来ます。
以下の例では、AA1のセルに入力規則を設定しています。

入力地の種類：日付
データ：次の値に等しくない
日付：
=IF(OR(MIN(X3:X33)>AA1,MAX(X3:X33)<AA1,COUNTIF(X3:X33,AA1)<1),AA1,0)

日付に入れる式では、X3:X33の最大値最小値の範囲に収まっていない場合と、入力した日付がX3:X33に無い場合、AA1の日付をそのまま返しています。