Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Home : その他)
非表示列の計算について
投稿日時: 21/11/09 09:27:32
投稿者: TI

おはようございます。
 
SUBTOTALを利用して、列データの集計をしています。
 
グループ化をして、集計データがある部分を非表示
にする場合があります。
 
その際に、集計データ部分を非表示にすると、計算が
されないという現象が起きています。
 
作成した数式は以下の通りです。
=SUBTOTAL(9,計算範囲)
(第一引数を「109」にすると非表示行は集計されないと
 いうことはヘルプで確認しました。
 ただ、同じヘルプ内のSUBTOTAL 関数では、集計方法の値に
 かかわらず、フィルターの結果に含まれていない行はすべて
 無視されます。」の部分とつながらないような気がして、
 この部分は確認中なのですが)
 
作成はExcel2013で行い、非表示にしても、計算が正しく
されます。
しかし、こちらのファイルをOffice365のExcelで開くと
非表示にした状態では集計されず、計算結果が「0」と
表示されます。
 
SUBTOTALはExcelのバージョンにより挙動が異なることが
あるのでしょうか。

回答
投稿日時: 21/11/09 10:49:23
投稿者: Suzu

引用:
作成はExcel2013で行い、非表示にしても、計算が正しく
されます。
しかし、こちらのファイルをOffice365のExcelで開くと
非表示にした状態では集計されず、計算結果が「0」と
表示されます。

 
=SUBTOTAL(9,計算範囲)
の数式で
                    本来         現状
2013 -- 非表示の部分は      集計対象となる    集計対象となる
 365 -- 非表示の部分は      集計対象となる    集計対象とならない 
という現象が起きていると言うことでしょうか?
 
再計算を行っても同じでしょうか?
 
 
ヘルプの文の整合性についてですが、
引用:
SUBTOTAL 関数では、集計方法の値にかかわらず、フィルターの結果に含まれていない行はすべて無視されます。

の記載の部分ですよね。
 
ヘルプの内容が間違っている事もありますが、
「集計の方法」と言うのが、SUMや、AVAREGE等の事を言っているのではないでしょうか?
英語版のヘルプを確認すると何か判るかもですが、当方では細かいニュアンスまでは判りませんので ^^;

回答
投稿日時: 21/11/09 11:11:45
投稿者: めいぷる

こんにちは。
 
集計方法の9(非表示の値も含める)と109(非表示の値を無視する)の違いですが、
ここでいう"非表示"とは手操作で非表示にした場合のことです。
フィルタで非表示にしている場合は該当しないので、9を指定しても109と同じ結果になります。
ですので、下記記述は正しいです。
 

引用:
SUBTOTAL 関数では、集計方法の値にかかわらず、フィルターの結果に含まれていない行はすべて無視されます。

回答
投稿日時: 21/11/09 14:34:53
投稿者: WinArrow
投稿者のウェブサイトに移動

SUBTOTAL関数の機能強化した「AGGREGATE」の検討をお勧めします。
私は、まだ、実際には、使ってことがないので、詳しく説明できないが
検討してみる価値はあるものと思います。

投稿日時: 21/11/09 14:51:54
投稿者: TI

皆様、ご返信ありがとうございます。
 

Suzu さんの引用:

 
=SUBTOTAL(9,計算範囲)
の数式で
                    本来         現状
2013 -- 非表示の部分は      集計対象となる    集計対象となる
 365 -- 非表示の部分は      集計対象となる    集計対象とならない 
という現象が起きていると言うことでしょうか?
 
再計算を行っても同じでしょうか?
 

 
その通りです。
再計算をしても、結果は同じでした。
 
また、別のPCで使用しているExcel2019でも、2013と同様に計算範囲となっている
行を非表示にしてもSUBTOTALで計算結果が希望通りの表示がされました。
 
WinArrow さんの引用:

SUBTOTAL関数の機能強化した「AGGREGATE」の検討をお勧めします。
 

 
以前、エラーを除いた集計をしたいと思って使用したっきり、この
関数のことをすっかり忘れていました。
 
早速試してみます。
が、自身の環境では確認ができないので、確認結果のご報告はしばらく
お時間をいただければ、と思います。
 

回答
投稿日時: 21/11/09 15:20:39
投稿者: んなっと

Office365(Microsoft365)です。
 
●縦方向に並ぶデータ
 
C11
=SUBTOTAL(109,C2:C10)
 
   A   B  C
 1   名前  値
 2 ┌  あ  10
 3 |  あ  20
 4 |  あ  30
 5 □  あ  40 ←□をクリックして非表示に
 6    い  50
 7    い  60
 8    い  70
 9 ┌  う  80
10 □  う  90
11   合計 450 ★
 
    ↓
 
   A   B  C
 1   名前  値
 6    い  50
 7    い  60
 8    い  70
 9 ┌  う  80
10 □  う  90
11   合計 350 ★
 
 
●横方向に並ぶデータ
 
K3
=SUBTOTAL(109,B3:J3)
 
           ↓□をクリックして非表示に
   A  B  C  D  E  F  G  H  I  J   K
1    ┌ ― ― □       ┌ □   
2 名前 あ あ あ あ い い い う う 合計
3  値 10 20 30 40 50 60 70 80 90  450 ★
 
      ↓
 
   A   F  G  H  I  J   K
1           ┌ □   
2 名前  い い い う う 合計
3  値  50 60 70 80 90  450 ★こちらは変化なし
 
どちらも全く問題ありませんね。従来通りの結果です。
 
そちらの状況がわからないので、上のような表形式の具体例を添えて質問してください。

投稿日時: 21/11/14 17:30:51
投稿者: TI

んなっと様
ご返信ありがとうございます。
 

んなっと さんの引用:
Office365(Microsoft365)です。
 
●縦方向に並ぶデータ
 
C11
=SUBTOTAL(109,C2:C10)
 
   A   B  C
 1   名前  値
 2 ┌  あ  10
 3 |  あ  20
 4 |  あ  30
 5 □  あ  40 ←□をクリックして非表示に
 6    い  50
 7    い  60
 8    い  70
 9 ┌  う  80
10 □  う  90
11   合計 450 ★
 
    ↓
 
   A   B  C
 1   名前  値
 6    い  50
 7    い  60
 8    い  70
 9 ┌  う  80
10 □  う  90
11   合計 350 ★
 
 

 
上記のようなデータの状態で、
=SUBTOTAL(9,C2:C10)
としております。
 
しかし、結果が上記のように「=SUBTOTAL(109,C2:C10)」
とした場合と同じく、非表示行が集計されない結果と
なります。
 
WinArrow様
 
AGGREGATE関数を利用して、修正をしたところ、SUBTOTALを
使用した場合と同じ結果を得ることができました。
こちらのバージョンをOffice365を利用している方に確認
依頼中ですので、結果がわかりましたら、ご報告します。
 
宜しくお願い申し上げます。
 
 
 
 
 

回答
投稿日時: 21/11/15 10:13:31
投稿者: んなっと

Office365です。
C11
=SUBTOTAL(9,C2:C10)
  
   A   B  C
 1   名前  値
 2 ┌  あ  10
 3 |  あ  20
 4 |  あ  30
 5 □  あ  40 ←□をクリックして非表示に
 6    い  50
 7    い  60
 8    い  70
 9 ┌  う  80
10 □  う  90
11   合計 450 ★
  
    ↓
  
   A   B  C
 1   名前  値
 6    い  50
 7    い  60
 8    い  70
 9 ┌  う  80
10 □  う  90
11   合計 450 ★←問題なし。

投稿日時: 21/11/20 08:11:39
投稿者: TI

んなっと様
 
度々、ご検証いただき、ありがとうございます。
 
私の環境でもんなっと様と同じ結果となっておりますが、
やはり、私に相談があった方の環境で再度確認をしましたが、
非表示列が集計されない結果となっていました。
 
どうにも、原因がつかめない状況です。
 
WinArrow様
ご教授いただいた「Aggregate関数」を使用したところ、
希望通り、非表示列も集計された結果を得ることができました。
 
同にもすっきりしない状況ですが、一旦は解決とさせて
いただきます。
 
皆様、貴重なアドバイスをありがとうございます。