シートAの数値をもとに、シート1月、2月、3月のあるセル計算式を入力しているのですが、シートAでオートフィルタを実行するとシート1月、2月、3月の計算結果がすべて0になってしまいます。
オートフィルタを元に戻しても0のままになっており、計算式の入っているセルを1つ1つダブルクリックするか、そのセルに1をかけないと計算結果が変わりません。
ちなみに計算方法は"自動"に設定されています。
シートAの数値を使って計算しているシートが複数あるので1つ1つ修正するのは大変です。
シートAではオートフィルタを頻繁に使用します。
そこで、マクロなどを使って一度に0に変わったセルを再計算させる方法なないでしょうか?
教えてください。
A 回答 (8件)
- 最新から表示
- 回答順に表示
No.8
- 回答日時:
ANo.#6について説明不足のようですので、補足させていただきます。
内容を読ませていただいた上で、恐らく「シートA」のA列には、月名と区分番号を組み合わせて「1月101」のようなキーが入力されているものと推測します。前半の「1月」が月名で後半の「101」が区分番号を想定しています。
この前提で行けば、SUMIF関数の条件値も同様に「1月101」のように指定すればよい。このとき、「1月」というのはシート名と同じなのでこれを利用し、区分番号はA1セルに入力しているので、これらを結合した文字を条件値として指定しているものを推測しました。
このような仕組みにしておけば、シートをコピーして、シート名だけを変更すれば、各月の合計を求めることができますからね。
よって、SHEETNAMEは、現シート名を取得するものと推測したので、これは先に紹介した関数で求めることができますよと紹介しました。
さてさて。
今回の場合は、ユーザー定義関数が怪しいと思うので、まずはそこを確認してみてはいかがでしょうか。
数式で「SHEETNAME()」としている部分を「1月」などの実際のシート名で置き換えてみてください。その上で、同じ現象が起こるのかどうかですね。
まずは、この結果を確認してみるといいと思います。
No.7
- 回答日時:
ANo.#6 さんのご回答を読ませていただいて思ったのですが、
SHEETNAME()という関数が、この数式が入力されているシートでしたら
=MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,31)
でもいいと思います。
ANo.#6 さんのおっしゃるように別セルにシート名を表示させ、
INDIRECTで引っ張ってくる事ができます。
ただし現時点ではSHEETNAME()がどこのシート名をどう取得しているのかがわかりませんので、
残念ですが何とも言って差し上げられません。
No.6
- 回答日時:
これはユーザー定義関数が原因ではないでしょうか。
どのようなコードを記述されているのかわかりませんが、ユーザー定義関数は、原則として「非自動再計算」となっているため、「Application.Volatile」の行がないと自動再計算にはなりません。「Volatile」メソッドについて、オンラインヘルプなどで調べてみるといいでしょう。
また、シート名を取得するだけでしたら、既存の関数でも求められますよ。
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,100)
のような関数を入力すれば、入力したシートのシート名が表示できるはずです。
ただし、この場合はどこか適当なセルに、シート名を入力して、SUMIF関数ではそのセルを参照するような仕組みにするのが単純だと思いますが。
No.5
- 回答日時:
え~。
直接的な解決にはならないかもしれませんが、一つの方法を・・・。シートAにオートフィルタをかけると計算結果が0になってしまうのなら、オートフィルタをかけないようにするという発想です。
シートAをコピーします。
Ctrl押しながらシートをドラッグすれば出来ます。
次にコピーAの表内の文字はすべて消します。
シートAの表内をすべて選択して、コピーします。
コピーAの表先頭セルをクリックして、編集メニューから形式を選択して貼り付け。
左下のリンク貼り付けをクリック。
これでコピーAにはシートAの表が反映されます。
シートAの内容が変化しても大丈夫でしょう。
そしてオートフィルタはコピーAでかけるようにします。
シートAはそのままなので、月シートに影響しないと思います。
No.4
- 回答日時:
またまたありがとうございます。
> 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
- 回答日時:
たびたび恐れ入ります。
オートフィルタが設定されているシートと月のデータが入力されているシートが別々だという事がわかりました。
まだオートフィルタと計算式の戻り値が0の原因を mayself さんが関連付けられていらっしゃる理由がぴんと来ません・・・。
SUMIF関数は非表示になったセルからも値を引っ張ってきます。
SUBTOTAL関数ですと表示、非表示を区別します。
ですので、SUMIFが探している「あるセル」の値と、シートAに入力されている値の書式が違うなど
そのあたりから調べていかれた方がいいかも知れません。
文字列で数値を探している、とか、数値で文字列を探している、とか。
せっかく補足してくださったのですが、まだまだ詳細が不明ですので
補足の内容にお答えできるのはここまでとなってしまいます。
できましたら数式バーよりその数式をコピーし、
こちらに貼り付けていただけませんでしょうか。
シート名は「Sheet1」などのように置き換えてくださって結構です。
また、シートAにはどのような値がどういった書式で入力されているのか、
それも書き込んでいただきますと大変助かります。
この回答への補足
こちらこそ説明が足らず申し訳ありません。
そうですか、非表示になったセルからも値はひっぱってくるのですね。
私も関係ないと思ったのですが、シート1月にSUMIF関数(シートAの値を集計)を使って計算式を入力した直後は正しい結果が表示されるのですが、シートAにオートフィルタをかけると、たちまちシート1月の値がすべて0になってしまうのです。一度は正しい計算結果が出ているで、計算のタイミングかと思うのですが・・・。
シート1月に入力したSUMIF関数は、シート名(月)と区分番号を足したものをキーにして集計するとして、
=SUMIF(A!A1:A100,SHEETNAME()&A1,D1:D100)と式を入力しました。
・シートAのA1:A100には、月&区分を組合せたキーが入っ ています。
・SHEETNAME()はシート名を自動取得する関数としてユーザ ー定義しました。A1には区分番号が入っています。
・シートAのD1:D100には合計する為の金額が入っていま す。
以上よろしくお願いします。
No.2
- 回答日時:
うーん、宜しければ計算式を教えていただけないでしょうか。
オートフィルタでどう絞り込んでいらっしゃる、等、
もう少々お願いいたします。
この回答への補足
失礼しました。
シート1月、2月、3月にはSUMIF関数を使って、あるセルをキーにして条件にあったものだけを、シートAの表から合計するという計算式を作成しています。
シートAのオートフィルタは計算式とは関係なく、1月のものだけを表示するといった具合にいろいろな抽出用として使用しています。
フィルタを解除してすべてのデータを表示しても0のままなのです。
やはり、計算式に使用している表をオートフィルタを使って非表示にしてしまうということはやってはいけないことなのでしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) 複数セルデータを別シートの単一セルにコピーしたい。(詳細をご参照ください) 1 2022/12/14 15:08
- Excel(エクセル) エクセル関数について 2 2022/04/13 18:25
- Excel(エクセル) エクセル VBAでセル内容を別の列の最下行に転記したい 2 2022/11/29 08:47
- Excel(エクセル) シート参照を含む数式を連続コピー 3 2022/12/10 11:42
- Excel(エクセル) ユーザー定義について質問です。 2 2023/06/28 13:21
- Visual Basic(VBA) 顧客ごとに違う点検案内を作成するマクロ 4 2022/09/16 05:34
- Visual Basic(VBA) エクセルのマクロで対象ごとにシート分けしてその内容をセルに書き込みたい 9 2022/08/24 13:23
- Excel(エクセル) 前の(左隣の)シートを連続参照するように、あとから変更したい 1 2023/02/22 00:51
- Excel(エクセル) Excelで特定の文字列を判定し計算する数式を教えて下さい。 1 2022/05/01 12:04
- Excel(エクセル) エクセル 別シートの各セルそれぞれの比率を計算したい 4 2023/08/05 15:20
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
【関数】先頭だけにある、半角...
-
エクセル 白黒印刷で白線を印刷...
-
Excelのチェックボックスの使い...
-
【関数】適切な文字数の数字を...
-
Excelのpivotについて質問です
-
Excel ピボットテーブルで日付...
-
LOOKUP関数を使えばいいのでし...
-
エクセル関数を教えてください
-
エクセルのセルに同じ大きさの...
-
UNIQUE関数が使えないバージョ...
-
excelの不要な行の削除ができな...
-
エクセルで「-0.0」と表示さ...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの関数について教えて...
-
Aというブックの1というシート...
-
【マクロ】シート名を取得する...
-
VBA Private Sub Worksheet_Cha...
-
VBA、Excelのworkbook.open に...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報