Excel2000でオートフィルタを実行すると計算結果が0になってしまいます。

質問

シートＡの数値をもとに、シート1月、2月、3月のあるセル計算式を入力しているのですが、シートＡでオートフィルタを実行するとシート1月、2月、3月の計算結果がすべて0になってしまいます。
オートフィルタを元に戻しても0のままになっており、計算式の入っているセルを1つ1つダブルクリックするか、そのセルに1をかけないと計算結果が変わりません。
ちなみに計算方法は"自動"に設定されています。
シートＡの数値を使って計算しているシートが複数あるので1つ1つ修正するのは大変です。
シートＡではオートフィルタを頻繁に使用します。
そこで、マクロなどを使って一度に０に変わったセルを再計算させる方法なないでしょうか？

教えてください。

回答No.8

fmajin

ANo.#6について説明不足のようですので、補足させていただきます。

内容を読ませていただいた上で、恐らく「シートA」のA列には、月名と区分番号を組み合わせて「1月101」のようなキーが入力されているものと推測します。前半の「1月」が月名で後半の「101」が区分番号を想定しています。
この前提で行けば、SUMIF関数の条件値も同様に「1月101」のように指定すればよい。このとき、「1月」というのはシート名と同じなのでこれを利用し、区分番号はA1セルに入力しているので、これらを結合した文字を条件値として指定しているものを推測しました。
このような仕組みにしておけば、シートをコピーして、シート名だけを変更すれば、各月の合計を求めることができますからね。
よって、SHEETNAMEは、現シート名を取得するものと推測したので、これは先に紹介した関数で求めることができますよと紹介しました。

さてさて。
今回の場合は、ユーザー定義関数が怪しいと思うので、まずはそこを確認してみてはいかがでしょうか。
数式で「SHEETNAME()」としている部分を「1月」などの実際のシート名で置き換えてみてください。その上で、同じ現象が起こるのかどうかですね。
まずは、この結果を確認してみるといいと思います。

回答No.7

noname#9284

ANo.#6 さんのご回答を読ませていただいて思ったのですが、
SHEETNAME()という関数が、この数式が入力されているシートでしたら
=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,31)
でもいいと思います。
ANo.#6 さんのおっしゃるように別セルにシート名を表示させ、
INDIRECTで引っ張ってくる事ができます。

ただし現時点ではSHEETNAME()がどこのシート名をどう取得しているのかがわかりませんので、
残念ですが何とも言って差し上げられません。

回答No.6

fmajin

これはユーザー定義関数が原因ではないでしょうか。
どのようなコードを記述されているのかわかりませんが、ユーザー定義関数は、原則として「非自動再計算」となっているため、「Application.Volatile」の行がないと自動再計算にはなりません。「Volatile」メソッドについて、オンラインヘルプなどで調べてみるといいでしょう。

また、シート名を取得するだけでしたら、既存の関数でも求められますよ。
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,100)
のような関数を入力すれば、入力したシートのシート名が表示できるはずです。

ただし、この場合はどこか適当なセルに、シート名を入力して、SUMIF関数ではそのセルを参照するような仕組みにするのが単純だと思いますが。

回答No.5

esprei

え～。直接的な解決にはならないかもしれませんが、一つの方法を・・・。
シートAにオートフィルタをかけると計算結果が0になってしまうのなら、オートフィルタをかけないようにするという発想です。
シートAをコピーします。
Ctrl押しながらシートをドラッグすれば出来ます。
次にコピーAの表内の文字はすべて消します。
シートAの表内をすべて選択して、コピーします。
コピーAの表先頭セルをクリックして、編集メニューから形式を選択して貼り付け。
左下のリンク貼り付けをクリック。
これでコピーAにはシートAの表が反映されます。
シートAの内容が変化しても大丈夫でしょう。
そしてオートフィルタはコピーAでかけるようにします。
シートAはそのままなので、月シートに影響しないと思います。

回答No.4

noname#9284

またまたありがとうございます。

> A1には区分番号が入っています。

このA1はシート「1月」のA1ですね。

=SUMIF(A!A1:A100,SHEETNAME()&A1,D1:D100)

この数式からは「SHEETNAME()」の中身が見えてきません。
どんな風に定義されたのかはわかりませんが、中の値を確かめることはできそうです。

数式バーの「SHEETNAME()」部分をドラッグしてF9キーを押してください。
この関数が持っている実際の値が表示されます。
ご覧になりましたらEscキーで抜けてください。

さて、もしユーザー定義関数を使わないとすれば数式は次のようになって初めて機能します。

=SUMIF(A!A1:A100,INDIRECT("別なシート名!"&A1),D1:D100)

ユーザー定義関数をこれに代入しますと←ユーザー定義関数の戻り値には「!」は含まれていませんよね）
=SUMIF(A!A1:A100,INDIRECT("SHEETNAME()"&!A1),D1:D100)

となるのではないかなぁと思います。
ご確認をお願いできませんでしょうか。

回答No.3

noname#9284

たびたび恐れ入ります。

オートフィルタが設定されているシートと月のデータが入力されているシートが別々だという事がわかりました。
まだオートフィルタと計算式の戻り値が０の原因を mayself さんが関連付けられていらっしゃる理由がぴんと来ません・・・。

SUMIF関数は非表示になったセルからも値を引っ張ってきます。
SUBTOTAL関数ですと表示、非表示を区別します。

ですので、SUMIFが探している「あるセル」の値と、シートAに入力されている値の書式が違うなど
そのあたりから調べていかれた方がいいかも知れません。
文字列で数値を探している、とか、数値で文字列を探している、とか。

せっかく補足してくださったのですが、まだまだ詳細が不明ですので
補足の内容にお答えできるのはここまでとなってしまいます。

できましたら数式バーよりその数式をコピーし、
こちらに貼り付けていただけませんでしょうか。
シート名は「Sheet1」などのように置き換えてくださって結構です。
また、シートAにはどのような値がどういった書式で入力されているのか、
それも書き込んでいただきますと大変助かります。

補足

こちらこそ説明が足らず申し訳ありません。

そうですか、非表示になったセルからも値はひっぱってくるのですね。

私も関係ないと思ったのですが、シート1月にSUMIF関数（シートＡの値を集計）を使って計算式を入力した直後は正しい結果が表示されるのですが、シートＡにオートフィルタをかけると、たちまちシート1月の値がすべて0になってしまうのです。一度は正しい計算結果が出ているで、計算のタイミングかと思うのですが・・・。

シート1月に入力したSUMIF関数は、シート名（月）と区分番号を足したものをキーにして集計するとして、
=SUMIF(A!A1:A100,SHEETNAME()&A1,D1:D100)と式を入力しました。
・シートAのA1:A100には、月＆区分を組合せたキーが入っ　ています。
・SHEETNAME()はシート名を自動取得する関数としてユーザ　ー定義しました。A1には区分番号が入っています。
・シートAのD1:D100には合計する為の金額が入っていま　　す。
以上よろしくお願いします。

回答No.2

noname#9284

うーん、宜しければ計算式を教えていただけないでしょうか。
オートフィルタでどう絞り込んでいらっしゃる、等、
もう少々お願いいたします。

補足

失礼しました。
シート1月、2月、3月にはSUMIF関数を使って、あるセルをキーにして条件にあったものだけを、シートAの表から合計するという計算式を作成しています。

シートAのオートフィルタは計算式とは関係なく、1月のものだけを表示するといった具合にいろいろな抽出用として使用しています。

フィルタを解除してすべてのデータを表示しても0のままなのです。

やはり、計算式に使用している表をオートフィルタを使って非表示にしてしまうということはやってはいけないことなのでしょうか？

回答No.1

noname#9284

F9を押して様子を見ていただけないでしょうか。
それで計算されるようでしたらツール→オプションを見てください。
なお、あるタイミングによって自動に設定してあった計算方法が
知らない間に手動になるアクシデントもあるようです。

補足

早速のご回答ありがとうございます。
私もそれは思ったのですが、[ツール]-[オプション]で確認したら"自動"のままで、《F9》を押しても変更されません。何かアクションを起こしてやらないといけないようです。