EXCELのデータ抽出の方法を教えて下さい。

質問

EXCELのデータ抽出の方法がわからず困っています。

やりたい事としては、添付画像の黄色の箇所に、
指定した月の、日曜日と祝日以外（祝日は祝日一覧のデータを基にする）の
日付を一覧で表示させたく思っています。

使用方法としては、月毎にシートをコピーして使いまわすため、
使用する人が、シートの決められたセルに（添付画像ではB1とD1）、
その月の値を入力すると（例：平成26年9月もしくは2014年9月のような形）、
その下に日付が一覧で表示される形にしたいです。
（添付画像では黄色の箇所に一覧表示）

EXCEL初心者で、悩みに悩んで、
やっとWORKDAY関数まで辿りついたのですが、
これだと土曜日も含まれてしまうのですよね…。。。
また、アドインの設定が必要な場合もある等々、
大変恥ずかしながら難しくてよくわからず。。。

また、今回作成するファイルは会社で使用するものなのですが、
社内のEXCELのバージョンは使用するパソコンによって異なり、
2000～2010まであります。
ですので、出来ればどのバージョンでも問題なく使用できるファイルを
作成したいと考えています。

初歩的な質問かもしれず大変恐縮ですが、
ご教示いただきたく思います。
どうぞ宜しくお願い致します。

回答No.1

tom04 · ベストアンサー

こんばんは！
一例です。
作業用の列を設ける方法が判りやすいと思います。

尚、お示しの画像では文字が左寄せになっているので、セルの表示形式が「文字列」なのでしょうか？
もしそうであれば面倒なので、シリアル値で処理します。

F列すべてを選択　→　右クリック　→　セルの書式設定　→　表示形式タブ　→　ユーザー定義　→　「種類」の欄がG/標準となっていれば
それを消して
ggge年m月d日　と手入力しOK
これで　2014/1/1　のように入力してもお示しの画像のような表示になります。
ggge年m月d日(aaa)　とすれば曜日まで表示されます。

そして作業列G1セル（←セルの表示形式に手を付ける必要はありません）に
=IF(MONTH(DATE(B$1+1988,D$1,ROW(A1)))=D$1,IF(OR(WEEKDAY(DATE(B$1+1988,D$1,ROW(A1)))=1,COUNTIF(F:F,DATE(B$1+1988,D$1,ROW(A1)))),"",DATE(B$1+1988,D$1,ROW(A1))),"")
という数式を入れ、月末（31日分）までのG31セルまでフィルハンドルでコピーしておきます。

そしてA4セル（セルの表示形式は好みで）に
=IF(COUNT(H:H)<ROW(A1),"",SMALL(H:H,ROW(A1)))
という数式を入れフィルハンドルでずぃ～～～！っと下へコピーすると
画像のような感じになります。

※　作業列が目障りであれば非表示にしておいてください。m(_ _)m

お礼

tom04 様

こんにちは。
早速のお返事をいただき、どうもありがとうございます。
教えていただいた方法で実際に試してみたところ、
まさに私が思い描いていた通りの事が出来ました！

こちらで質問する前に、
休日に土曜日を含んではいけない事をうっかり忘れ、
一度WORKDAY関数でファイルを仕上げていたのですが、
その際、翌月の日付が表示されてしまうのを、
条件付書式で非表示にする形で対応していました。
ですので、今回教えていただいた方法だと、
それすらも不要で、本当にスッキリ致しました。

また、和暦表示に1988を用いることも全く知りませんでした。
実はこれも自分で作成した際は、和暦にしたいけれど計算がうまくいかず、
西暦で対応していましたので、とても助かりました！

これならば、Excelのバージョンを気にすることなく、
社内のどのパソコンでも使用出来ます！

困り果てていたため、
本当に助かりました！！

大変お世話になりました。
どうもありがとうございます！！

回答No.5

hallo-2007

初心者と云うことでわかりやすく
Ｂ１セルに　２０１４　と年を西暦で入れるとして
Ｆ４セルに　数値で　０　とでも入れて
Ｆ５セルから下に　祝日を入れておきます。
空いているセル　仮に　Ｄ４セルに　=MATCH(DATE(B1,D1,0.5),F:F,TRUE)
と入れて始まりの行番号を取得
Ｄ５セルに　=MATCH(DATE(B1,D1+1,0.5),F:F,TRUE)
で終わりの行番号を取得
Ｄ６セルに　=D5-D4　で該当する日付の数
あとは
Ｂ４セルに
=IF(ROW(A1)>D$6,"",INDEX(F:F,D$4+ROW(A1)))
と入れて下へコピーしてみてください。

数式の意味は理解してくださいね。

お礼

hallo-2007 様

早速お返事をいただき、どうもありがとうございます。

教えていただいた方法で、
自分なりに数式の意味を理解しながらやってみました。
これは祝日一覧から、
指定した月に該当する祝日を抽出する方法だったのですね。

今回は逆にそれらを除いた日付を抽出したかったため、
せっかく教えていただいたのですが、
初心者の私にはこれを基にどう応用していけば良いのかまた悩むところでして…
ですので、今回はこの方法で出来ず残念ですが、
とても勉強になりました！

これからもExcelはやっていかなければいけないため、
教えていただいた方法をまた今後に繋げれるよう
日々やってきたいと思います。

大変お世話になりました。
どうもありがとうございます！！

回答No.4

nishi6

＞出来ればどのバージョンでも問題なく使用できるファイルを作成したいと考えています
やはり、エラー対応の違いで算式の長さが倍近くになります。（当方Excel2010です）

質問にある添付図で、セルA4に、下記式をコピーし、CTRL+SHIFT+ENTERで登録します。配列数式になります。後は下に（配列数式になったA4を）コピーします。F列は日付シリアル値としています。平成26年は7月と10月が抽出される日が多いようです。

=IF(COUNT(IF(MONTH(DATE($B$1+1988,$D$1,ROW($A$1:$A$31)))=$D$1,IF(WEEKDAY(DATE($B$1+1988,$D$1,ROW($A$1:$A$31)))>1,IF(COUNTIF(F:F,DATE($B$1+1988,$D$1,ROW($A$1:$A$31)))=0,ROW($A$1:$A$31),""),""),""))<ROW()-3,"",DATE($B$1+1988,$D$1,SMALL(IF(MONTH(DATE($B$1+1988,$D$1,ROW($A$1:$A$31)))=$D$1,IF(WEEKDAY(DATE($B$1+1988,$D$1,ROW($A$1:$A$31)))>1,IF(COUNTIF(F:F,DATE($B$1+1988,$D$1,ROW($A$1:$A$31)))=0,ROW($A$1:$A$31),""),""),""),ROW()-3)))

少し説明を。（下式は上と同じですが見やすいように改行しています。DATE内部は年月日で代用）
=IF(COUNT(
　　　IF(MONTH(DATE(年月日))=$D$1,　　'月が一致
　　　IF(WEEKDAY(DATE(年月日))>1, 　　'日曜以外
　　　IF(COUNTIF(F:F,DATE(年月日))=0, '祝日以外
　　ROW($A$1:$A$31),""),""),""))<ROW()-3,"",　'指定月外
　DATE(年月,
　　SMALL(
　　　IF(MONTH(DATE(年月日))=$D$1,　　'月が一致
　　　IF(WEEKDAY(DATE(年月日))>1, 　　'日曜以外
　　　IF(COUNTIF(F:F,DATE(年月日))=0, '祝日以外
　　ROW($A$1:$A$31),""),""),""),ROW()-3)))　'日

Excel2000等を意識すると、IFERROR関数が使えないので冗長になります。

お礼

nishi6 様

早速お返事をいただき、どうもありがとうございます。

配列数式…と聞いた瞬間、初心者の私にはクラッ…
ときそうになりましたが(笑)、
そんな私にもわかるよう
とても詳しく数式の解説を書いて下さったおかげで、
長い数式の意味が理解出来ました。

nishi6 様の解説のおかげで、
今回ご回答をいただいた皆様にも教えていただいた数式の解説にもなり、
とても勉強になりました！

やりたかった事が出来るようになりました！

大変お世話になりました。
どうもありがとうございます！！

回答No.3

MarcoRossiItaly

本当は、お勧めできる手法は、易しい数式のみを使って、月ごとなどのカレンダーを Excel シート上に自作することです（アドイン不要）。たまたま土日祝日に該当した欄のみ、条件付き書式により、セルの塗りつぶしや文字の色を変えたりすることもできます。毎月、必ず 31 日以内しかないわけですから、それほどカレンダーの面積が大きくなることもありませんね。日付を縦 1 列に並べてもいいし、長方形型に配置してもいいです。

カレンダーを作りたくなった場合は、この質問内には追加せず、別の質問として投稿してください。

しかし下のとおり、難解な数式を使えば、ご要望どおり条件に合う日付のみを上に詰めて表示することも不可能ではありません。非該当の日を除いて表示しているため、経過日数などは分かりづらくなるかもしれませんが、一応できます。

F 列の祝日などについては、「2014/1/1」などを記入し、セルの書式により「平成26年1月1日」などと表示させます。「セルの書式を『文字列』に設定した上でそこに『平成26年1月1日』などを記入する」という方法は、トラブルのもとなので、本件に限らず基本的に使わないようにします。

F1　=date(b1+1988,d1,1)　　……非表示の書式を設定してもオッケー
A4　=small(index(F$1-1+row($1:$31)+9^9*((month(F$1-1+row($1:$31))<>D$1)+(weekday(F$1-1+row($1:$31))=1)+(weekday(F$1-1+row($1:$31))=7)+(countif(F$4:F$100,F$1-1+row($1:$31)))),),rows(A$4:A4))

A4 セルから下方向にドラッグし、A26 までオートフィル。このうち下のほうのセルには「######」というエラーが表示されるかもしれませんが、気にしない。A4:A26 のセル範囲の書式を「[<3000000]d"（"aaa"）";」などに設定すれば（*）、このエラー表示は消えます。

* セル範囲を選択して右クリックし「セルの書式設定 > 表示形式タブ > ユーザー定義 > 種類ボックス」にこの書式記号を入力

質問文にもあるように、アドインを組み込む必要はありますが、WORKDAY 関数を使っても構わないということであれば、上式に比べればまだ簡単というくらいの数式にはなります。

F1　上式と同じ
A4　=workday(F$1,rows(A$4:a4)-1+((weekday(F$1)=1)+(weekday(F$1)=7)+countif(F$4:F$100,F$1)>0),F$4:F$100)

ただ、これだと、A 列の途中で月が変わっても非表示にならず、翌月の日付を表示してしまいます。D1 セルの月と比較するなどして、条件付き書式により翌月を非表示にします。具体的には、条件付き書式ダイアログの「数式を使用して、…」にて、「=month(a4)<>D$1」という条件下、セルの書式「;;」を指定するなど。

お礼

MarcoRossiItaly 様

早速お返事をいただき、どうもありがとうございます。
そうなんです、私もカレンダー形式に出来れば、
初心者の私でも作成出来るのでは…と思ったのですが、
業務で使用するフォーマットの都合上、
カレンダー形式には出来ませんでした。。

2通りの方法を教えていただきとても助かります！
しかも、もし1つ目のやり方をWORKDAY関数で作成した場合
どうなるのかというところまで教えていただき、
とても勉強になりました！

また、翌月の日付が表示された場合の対処の仕方ですが、
実は私も同じように条件付書式を利用していたのですが、
文字を白色にする事で対応していました。
ですので、セルの書式を「;;」とする方法は初めて知ったため、
これもとても勉強になりました！

知らないことだらけでお恥ずかしいですが、
様々な方法を教えていただけたおかけで、
やりたかった事が出来るようになりました！

大変お世話になりました。
どうもありがとうございます！！

回答No.2

tom04

No.1です。
たびたびごめんなさい。
投稿後に気づきました。

作業列はG列ではなく、H列でした。
数式そのものは問題ないと思います。

どうも失礼しました。m(_ _)m

お礼

tom04 様

何度も申し訳ありません。
どうもありがとうございます！