日付の

質問

WindowsXP Office2003 です。
エクセルで20シート位に同じ形式の表があります。
各シートのE3:E14には、日付を入れ
J3:J14には、=IF(E3="","",TEXT(E3,"m"))
　〈E3　⇒　E14　と相対値です〉
と入れてあります。(月を数値として表示させたかった為)
この表は必ずE3が4月○日、E4が5月○日になるとは限りません。
シートによっては、E3が5月○日や6月○日になったり
E3が8月○日、E4が8月△日と
同じ月が２回入力される場合もあります。

それを集計シートのC3～C14に
全部のシートの4（月）は○個　5（月）は○個・・・　という集計を出したいのです。

集計シートのA3～A14に検索値として
4
5
6
…省略…
12
1
2
3
と（標準書式で）入れてあります。
C3セルに
=COUNTIF(sheet1!$J$3:$J$14,A3)
と入れるとヒトツのシートだけですが4（月分）の個数が出ました。
しかし、
=COUNTIF(sheet1:sheet20!$J$3:$J$14,A3)
と入れると#VALUE!　となってしまいます。
3D集計のように全シートの集計はできませんか？
又、
各シートのJ3:J14にTEXT関数を作る必要がない方法はありますか？
（日付が入力してあるセルからダイレクトに
COUNTIF等で月の個数を数えられる方法）　

よろしくお願いします。

回答No.3

zenjee · ベストアンサー

Sheet1～Sheet20の各シートに作業列を設けるのはイヤですか。

もしイヤでなければ、Sheet1～Sheet20を全選択して空いているセル（例えばL3:L14)に集計シートのA3:A14をコピー、L列を検索値としてM3:M14にCOUNTIF関数で各シートの数値を一旦出しておき、集計シートのC3を選択して
=SUM(Sheet1:Sheet20!M3)
で串刺し集計した方が作業としては簡単なように思いますが……。

なお、後段のTEXT関数を作らない方法はNo.2 mshr1962さんの下の式
=SUMPRODUCT((MONTH(INDIRECT(B1&"!$E$3:$E$14"))=A3)*1)
でＯＫですが、Ｂ1の定義がなかったので、COUNTIFでなくこの式を使うならSheet名が表示されるよう、Ｂ1に次の式の入力が必要となります。
=MID(CELL("filename"),FIND("]",CELL("filename"))+1,31)

お礼

この方法で作らせていただきます。
ありがとうございました！m(_ _)m

補足

お返事ありがとうございました！

この方法は、とてもわかりやすいし
私のレベルにはベストです！

これならSheetを間に挿入しても
自動的に集計できますね。

しかし、Sheet名を参照する関数は
今まで知らなかった関数だらけです。
MID　関数とは・・・
文字列　CELL("filename"),FIND("]",
開始位置　CELL("filename"))+1,
文字数　31
という意味ですか？
CELL("filename"),
FIND("]",
これらも関数のヘルプを見てみたのですが
？？？意味がよくわかりません。
すみませんが詳しく教えていただけませんか？
又は、参考サイトを教えていただけませんか？
使えればとても便利そうですよね。

回答No.5

mshr1962

#2,4のmshr1962です。
残念ながらCELL関数で表示されるのは関数のあるシートです。
各シートで表示したセルをリンクする形ですね。

もう一つの方法としてマクロを使う方法があります。
参考になるサイトを載せておきます。
http://www.nurs.or.jp/~ppoy/access/index.html
ここでExcelの15行目の「シート名一覧と各シートへのハイパーリンクを新規シートに作成する方法 」

お礼

ご丁寧にありがとうございます！

こちらのサイトは　まだちらり　と見ただけですが
ワクワクするような宝箱のようなサイトですね！
これからも参考にさせていただきます。

今回の書類はNo.3zenjeeさんが回答して下さった方法で作成させていただこうと思いますが
他の事に使えそうな沢山の事を
わかりやすく教えて下さり
本当にありがとうございました！m(_ _)m

回答No.4

mshr1962

#2のmshr1962です。
CELL関数は情報関数です。
CELL("filename")の場合は「ファイルの絶対パス[ブック名]シート名」を表示します。
例　今のシートがCドライブのEXCELフォルダのEXCELブックのEXCELシートなら
CELL("filename")="C:\EXCEL[EXCEL.XLS]EXCEL"
ですね。

FIND関数は文字列内の特定の文字の位置を探します。
これで"]"を見つければ、上記からMID関数でシート名を抽出できます。
最後の31は文字の長さの指定ですが、シート名の文字数以上であれば別の数値でもOKです。

補足

ありがとうございます！
CELL関数はエクセルのヘルプを見ても
？？？でしたが、
意味がよくわかりました。
同様にFIND関数、MID関数の意味も
ありがとうございました。

これは、そのシート自身のシート名を
表示してくれる訳ですね？
では、次の、その次の・・・シート名を表示させる
という事には応用できるのでしょうか？

回答No.2

mshr1962

=SUMPRODUCT((MONTH(sheet1!$E$3:$E$14)=A3)*1)
で出来ませんか？
Sheet名を別のセルで参照するなら
=SUMPRODUCT((MONTH(INDIRECT(B1&"!$E$3:$E$14"))=A3)*1)

補足

早速お返事いただき　ありがとうございました！
日付の月だけをダイレクトに参照するには
この方法があるのですね！
これで数式の列を一行削除する事ができます。（J列）

Sheet名を別のセルで参照する　というのは
この場合はB1にSheet1の名前を入れておく　
という意味ですか？
20シート位ある場合はシート名の列をどこかに作って（この場合はB列ですね？）INDIRECT関数で
参照させる　という意味ですね？

あと、20シート位の"個数を数える3D集計"については何かご存知ありませんか？

回答No.1

aroi-aroi

=COUNTIF(sheet1:sheet20!$J$3:$J$14,A3)
が間違いですね。
=COUNTIF(sheet1!$J$3:$J$14,A3)+COUNTIF(sheet2!$J$3:$J$14,A3)+…+COUNTIF(sheet20!$J$3:$J$14,A3)
としなければいけません。
「sheet1:sheet20!$J$3:$J$14」の部分の範囲指定が
よくないです。

補足

早速お返事頂きありがとうございます。
部分の範囲指定がエラーの原因だというのが
わかりました。
そして、この方法でできました！
しかし、シートが増えたら補修が必要ですね？
SUMの3D集計の場合、最初のシート：最後のシート
と指定しておくと
間に新規シートを挿入しても
そのシートが自動的に加算されるので
そのような作り方ができないかな　と
思ったのですが、どうでしょうか？
（最初から多めに作っておけば良いのですが
参考までに　教えていただけたら　嬉しいです）