色のついたセルを合計する(色フィルターを使う)：Excel基本講座

スポンサードリンク

更新:2025/4/10;作成:2017/11/27

フィルターで色のついたセルを合計する

テーブルで色のついたセルを合計する

フィルターで色のついたセルを合計する　　　　　　　Topへ

1. 下表のデータを使って説明します。
   

   	B	C	D	E	F	G
   2	連番	氏名	クラス	国語	数学	英語
   3	1	上原嘉男	A	56	46	53
   4	2	森永彩芽	B	81	61	39
   5	3	古田恵	C	76	60	58
   6	4	太田千恵子	A	64	68	93
   7	5	豊田啓一	B	45	47	66
   8	6	新村遥奈	C	35	71	62
   9	7	坂元彩香	A	31	76	43
   10	8	坪井尚生	B	92	71	31
   11	9	西原舞	C	62	48	84
   12	10	中野野乃花	A	91	36	36
   13	11	岩渕佳代	B	68	76	74
   14	12	市村将文	C	53	88	86
   15	13	芦田公平	A	87	56	42
   16	14	高見美姫	B	77	43	86
   17	15	高山晴彦	C	39	68	54
   18	16	高島嘉子	A	63	35	73

全てのデータを計算する

1. 下図のように、オレンジ色に塗りつぶされたセルの合計と平均を求めます。
   クラス A　の女性のデータがこの色で塗りつぶされています。
   • ちなみに、クラス A の合計や平均ならAVERAGEIF関数やSUMIF関数で求めることができます。
   下図では、PIVOTBY関数で求めた例です。Excel for Microsoft365で使用できる関数です。
   =PIVOTBY(D2:D18,,HSTACK(E2:E18,F2:F18,G2:G18),SUM)
   =PIVOTBY(D2:D18,,HSTACK(E2:E18,F2:F18,G2:G18),AVERAGE)
   
2. このデータリストの1行空けた下の行に、SUBTOTAL関数 や　AGGREGATE関数 を使って合計や平均を求めることができます。
   E20セルに =SUBTOTAL(109,E3:E18) と入力します。　集計方法の109は非表示のセルを無視して合計します。
   なお、今表示されている計算結果は表示されているすべてのデータの合計値です。
   • 1行空ける理由：フィルターを実行したときに合計の行が含まれないように、データリストとを離しています。
   E21セルには =SUBTOTAL(101,E3:E18) と入力します。　集計方法の101は非表示のセルを無視して平均します。
   なお、今表示されている計算結果は表示されているすべてのデータの平均値です。
   
   • SUBTOTAL関数で計算する
     合計は　=SUBTOTAL(109,E3:E18)
     平均は　=SUBTOTAL(101,E3:E18)
     
   • AGGREGATE関数を使う場合
     合計は　=AGGREGATE(9,7,E3:E18)
     平均は　=AGGREGATE(1,7,E3:E18)
     2つ目の引数の7は「非表示の行とエラー値を無視します」
     

特定の色を指定して計算する

1. データリスト内のセルを1つ選択した状態で、[データ]タブの並べ替えとフィルター グループの[フィルター]をクリックします。
   
2. 見出し行にフィルターボタンが表示されます。
   色を付けているセルの列のフィルターボタンをクリックします。[色フィルター]を選択し、オレンジ色を選択します。
   
3. オレンジ色に塗りつぶしたセルのデータが抽出されました。他の行は非表示になりました。
   表示されているデータの合計と平均が計算されました。
   
4. AGGREGATE関数でも同様の結果が得られます。
   

テーブルで色のついたセルを合計する　　　　　Topへ

1. データリスト内のセルを1つ選択します。
   ちなみに、上項の操作を実行している場合は[データ]タブの[フィルター]をクリックして、フィルターを解除します。
   
2. [ホーム]タブの[テーブルとして書式設定]→テーブルスタイル(淡色)10　を選択しました。
   データ行を塗りつぶさないスタイルを選択しました。
   
3. テーブルとして書式設定でデータ範囲を確認して[OK]ボタンをクリックします。
   
4. テーブルに変換しました。
   セルの塗りつぶしの色はそのまま残っています。
   
5. テーブルツールの[デザイン]タブで「集計行」にチェックをいれます。
   
6. テーブルの最下行に集計行が追加されました。
   集計行では合計のほかにも平均などが使えます。
   
7. 色を付けているセルの列のフィルターボタンをクリックします。[色フィルター]を選択し、薄い黄色を選択します。
   
8. オレンジ色に塗りつぶしたセルのデータが抽出されました。他の行は非表示になりました。
   表示されているデータの合計が計算されました。
   
9. 平均を求めたいときは、合計が表示されているセルのボタンをクリックして[平均]を選択します。
   平均にしたセルのフィルハンドルを右にドラッグしてコピーします。
   ちなみに、このセルに使われているのは SUBTOTAL関数で、テーブルの列のデータを参照する数式になっています。
   
10. 平均を求めました。