Excel。列を非表示にしたら、合計値も連動させるには【Column hidden】

Excel。列を非表示にしたら、合計値も連動させるには

＜CELL+SUMIF関数＞

行を非表示にしたときに、見えているデータだけの合計値に連動させたい場合には、SUBTOTAL関数やAGGREGATE関数をつかうことで、求めることができます。

そこで、次のような横長の表があります。

H列には、

=SUM(B2:G2)

のように、SUM関数で合計を求めています。

やりたいことは、列を非表示にしたら、その分を除いた合計値を求めたい。

例えば、1～3月の数値を除いた合計を求めたいわけです。

当然、列を非表示にしたところで、SUM関数は、非表示に対応していないので、合計値はかわりません。

また、SUBTOTAL関数やAGGREGATE関数は、行の非表示には対応しますが、列の非表示には対応できません。

そこで、少し考え方を変えてみます。

そもそも、列の非表示とはどういうことなのか。

列幅が0ということです。

つまり、列幅がわかるようなものがあれば、いいわけです。

そこでCELL関数をつかうことで、セル情報の列幅を求めることができます。

ただ、このCELL関数。スピル機能に対応したため、今までのような数式ではうまく求めることができません。

B5にCELL関数の数式をつくります。

CELL関数の最初の引数は、検査の種類。

列幅の情報を知りたいので、 width を選択します。

2つ目の引数の参照は、見出しのB1を選択します。

=CELL("width",B1)

で、数式を確定すると…

スピル機能によって、勝手にゴーストが発生してしまいます。

これでは、オートフィルで数式をコピーすることができません。

なので、スピル機能にならないように数式を修正します。

=@CELL("width",B1)

「@（アットマーク）」をいれることで、スピル機能をとめることができます。
※@ = implicit intersection operator

あとは、横方向に、オートフィルで数式をコピーします。

B列の列幅を短くしても、8のままですが、F9キーを押すと、再計算されます。

F9キーをおさないと、算出結果は変わりませんが、列幅を求めることができました。

I列に列の非表示に連動した合計を求める数式を用意します。

I2にSUMIF関数を使った数式を設定しました。

=SUMIF($B$5:$G$5,">0",B2:G2)

B列を非表示にしたら、F9キーを押します。

これで、非表示を除いた合計を求めることができます。

では、数式を確認しておきましょう。

単一条件の合計を求めたいので、SUMIF関数をつかいます。

最初の引数は、「範囲」。

検索のデータ範囲です。

CELL関数の結果をつかいますので、$B$5:$G$5

オートフィルで数式をコピーしますので、絶対参照を忘れずに設定します。

2つ目の引数は、検索条件。

”>0”。

セルの幅が0より大きいならば、非表示じゃないという意味ですね。

最後の引数は、合計範囲。B2:G2

あまり列の非表示の合計をすることはないかもしれませんが、やり方の一つとして、紹介させていただきました。

Excel。SUMIF関数は単一条件付きで数値の合計を行います【SUMIF】

Excel。SUMIF関数は単一条件付きで数値の合計を行います

＜関数辞典：SUMIF関数＞

SUMIF関数

読み方： サムイフ

分類： 数学/三角 

SUMIF(範囲,検索条件,[合計範囲])

条件付きで数値の合計を行います

Excel。数式一つで、グループ別累計を手早く求めたい。【Cumulative total】

Excel。数式一つで、グループ別累計を手早く求めたい。

＜SUMIF関数＞

地域別などの表があります。

今回用意したいのは、人口一覧表で説明します。

A列に都道府県名があって、B列に地域。C列は人口。

そして、D列に、地域別の累計を求めたい。

まずやってしまうのが、

D2に

=C2　とセル参照させて、

D3に

=D2+C3　と数式を設定して累計を求める

この時点で数式を2つ作らなければなりません。

そこで、D2に

=SUM($C$2:C2)

という始点を絶対参照で固定した、始点留めのSUM関数をつかうことで、数式は一つだけで、オートフィルで数式をコピーすれば、累計を求めることができます。

ただ、今回の場合には、地域別で累計を求めたい。

つまり、途中で、作り直さないといけないわけですね。

これでは、面倒です。

そこで、SUMIF関数をつかうことで、対応することができます。

SUMIF関数は単一条件で合計を求めることができる関数です。

では、D2にSUMIF関数をつかった数式を設定します。

D2の数式は、

=SUMIF($B$2:B2,B2,$C$2:C2)

この数式をオートフィルでコピーします。

これで、地域別累計（グループ別累計）を求めることができました。

それでは、設定を確認しておきましょう。

最初の引数は、範囲。この範囲というのは、次の引数の検索条件が含まれている範囲のことです。

$B$2:B2

オートフィルで数式をコピーしますので、始点を止めた設定にすることで、

B2:B2

B2:B3

B2:B7というように、自動的に範囲が拡張されます。

2つ目の引数は、検索条件。

B2を設定します。

3つ目の引数は、合計範囲です。

C列の人口の地域別累計を知りたいので、

$C$2:C2

こちらも、始点留めにします。

これで、数式は完成です。

Excel。指定した文字が含まれたものだけで合計したい【Contains characters】

Excel。指定した文字が含まれたものだけで合計したい

＜SUMIF関数＞

講義に参加した人のリストがあります。

講義名に英会話という文字が含まれている参加人数の合計を求めたいのですが、どのようにしたらいいのでしょうか。

合計値を求めるには、条件がありますので、SUMIF関数をつかいます。

ただ、どのような条件を設定したらいいのでしょうか。

というのも、講義名が単純に「英会話」なれば、英会話という条件で合計値をもとめることはできますが、「英会話初級」もあれば、「実践英会話」という講座もあります。

要するに、英会話という文字が含まれているというのが条件なわけです。

このような場合には、ワイルドカードをつかった条件にする必要があります。

では、E1をクリックして、SUMIF関数をつかった数式を設定します。

=SUMIF(A2:A7,"*"&D1&"*",B2:B7)

確定すると、47と算出することができました。

引数を確認しておきましょう。

最初の引数は、「範囲」。

次の引数の検索条件が含まれている列なので、講義名のA2:A7

2つ目の引数は、「検索条件」。

ここが今回のポイントですね。

単純に「英会話」とはできません。

そこで、ワイルドカードで「*英会話*」とすることで、英会話を含むという条件にすることができます。

D1には、英会話という文字がありますので、D1をつかうとしたら、*D1*と入力すると、*D1*という文字列を条件としてしまうので、&（アンパサンド）をつかう必要があります。

よって、検索条件は「"*"&D1&"*"」とします。

3つ目の引数は、「合計範囲」。参加人数のB2:B7を設定します。

これで、求めることができました、なお、D1に英会話という文字列がなく、直接文字列を使いたい場合の数式は、次のようになります。

=SUMIF(A2:A7,"*"&"英会話"&"*",B2:B7)

Excel。関数をつかって抽出した項目ごとに集計したい【totalling】

Excel。関数をつかって抽出した項目ごとに集計したい

＜UNIQUE関数とSUMIF関数とGROUPBY関数＞

A1:D9に地域別の販売金額表があります。

地域別の販売金額合計を求めたい。

そこで、ピボットテーブルをつかってもいいのですが、今回は、関数で求めてみたいと思います。

一意の地域名。すなわち、重複していない地域名のリストがないといけません。

例えば、データタブにある重複の削除を行ってしまうと、販売金額も消えてしまうので、元表をコピーするなどしなければいけません。

UNIQUE関数をつかえば、手早く一意の地域名を求めることができます。

F2をクリックして、UNIQUE関数をつかった数式を設定します。

=UNIQUE(C2:C9)

引数には、地域フィールドのC2:C9を設定します。これだけで、一意のデータを抽出することができます。

あとは、G2に、地域別の合計をSUMIF関数で求めます。

G2に設定したSUMIF関数の数式は、

=SUMIF(C2:C9,F2#,D2:D9)

これで、地域別の販売金額合計を求めることができました。

最初の引数は、範囲なので、C2:C9と次の引数である条件が含まれている範囲を設定します。

2つ目の引数は、検索条件です。F列のF2:F5までを範囲選択すると、F2#とスピル番号とスピル範囲演算子の「#」が組み合わさった表記にかわります。

3つ目の引数は、合計範囲なので、D2:D9と設定します。

UNIQUE関数とSUMIF関数をつかって求めるのもいいのですが、2つの関数をそれぞれ設定するのは、ちょっと面倒です。

なので、GROUPBY関数をつかうことで、一発で求めることができます。

I2にGROUPBY関数の数式を設定します。

=GROUPBY(C2:C9,D2:D9,SUM,0,0)

御覧のように、GROUPBY関数だけで、一意のデータを抽出して、地域別の販売金額合計を求めることができました。

集計に関しては、いろいろな方法がExcelには、用意されています。

Excel。複数の商品の売上金額合計を手早く、求めるにはどのようにしたらいいの【TOTAL】

Excel。複数の商品の売上金額合計を手早く、求めるにはどのようにしたらいいの

＜SUM+SUMIF関数＞

商品販売の表があります。

売上金額の総合計を求めるには、SUM関数で対応することができます。

鉛筆の売上金額合計を求めるには、SUMIF関数をつかうことで対応することができます。

D6に鉛筆だけの売上金額合計を求めてみます。

設定した数式は、

=SUMIF(A2:A11,D2,B2:B11)

これで、鉛筆の合計値を算出することができました。

では、商品名が複数になった時、どのようにしたらいいのでしょうか。

鉛筆と、色鉛筆の売上金額合計を求めるとします。

複数になったので、SUMIFS関数をつかってみることにしましょう。

D7に、SUMIFS関数の数式をつくります。

 =SUMIFS(B2:B11,A2:A11,D2,A2:A11,D3)

と数式を設定してみましたが、結果は「0」になってしまいました。

SUMIFS関数は、複数条件に対応となっていますが、鉛筆または、色鉛筆のような「OR条件」には対応していません。

そのため、SUMIF関数で、鉛筆と色鉛筆の合計値を求めて、その合算にすることで、求めることができます。

F8にSUMIF関数を2つ作りその合算を求める数式を、設定してみましょう。

設定した数式は、

=SUMIF(A2:A11,D2,B2:B11)+SUMIF(A2:A11,D3,B2:B11)

これで、鉛筆と色鉛筆の合計値を求めることができました。

ただ、この数式の問題点は、対象の商品名が増えた場合です。

SUMIF関数を商品数分、つくらないといけないわけです。

そこで、SUM関数とSUMIF関数を、組み合わせる数式で対応することができます。

D9に設定した数式は、

=SUM(SUMIF(A2:A11,D2:D3,B2:B11))

これで、商品名が増えても対応することができます。

SUM関数内のネストしているSUMIF関数は、配列関数で処理されています。

ご覧のように、OR条件で合算値を求める場合には、SUM+SUMIF関数という方法もあります。

SUMIF関数をたくさんつくっていて、困った場合には有効な方法の一つかと思います。

Excel。商品名ごとに累計を手早く算出したいけど、どのようにしたらいいの。【Cumulative】

Excel。商品名ごとに累計を手早く算出したいけど、どのようにしたらいいの。

＜SUMIF関数＞

商品名ごとに並んでいる表があります。

商品名ごとに累計を算出したいのですが、どのようにしたら、手早く算出することができるのでしょうか。

累計は、SUM関数をつかって、始点を絶対参照にした数式をつくることで、算出することができます。

D2に設定した数式は、

=SUM($C$2:C2)

始点を絶対参照で留めることで、終点はオートフィルで数式をコピーすると範囲が拡張されていきます。

よって、累計を算出することができるというわけです。

そして、商品別累計を算出したいわけなので、条件が追加されています。

よって、SUMIF関数をつかい、累計と同じように、始点を絶対参照にすればいいわけです。

では、E2にSUMIF関数をつかった数式を設定します。

=SUMIF($B$2:B2,B2,$C$2:C2)

あとは、オートフィルで数式をコピーすれば、商品別累計を算出することができます。

累計のSUM関数と異なる点は、最初の引数の範囲と、三番目の引数の合計範囲の始点を絶対参照にしています。

Excelの様々な関数の読み方や引数などを紹介。今回は、SUM関数～SUMSQ関数です。【dictionary】

Excelの様々な関数の読み方や引数などを紹介。今回は、SUM関数～SUMSQ関数です。

＜Excel関数辞典：VOL.77＞

今回は、SUM関数～SUMSQ関数までをご紹介しております。

SUM関数

読み方： サム　　

分類： 数学/三角 

SUM(数値1,[数値2],…)

数値の合計します　

SUMIF関数

読み方： サムイフ　　

分類： 数学/三角 

SUMIF(範囲,検索条件,[合計範囲])

条件付きで数値の合計を行います　

SUMIFS関数

読み方： サムイフズ

読み方： サムイフエス

分類： 数学/三角 

SUMIFS(合計対象範囲,条件範囲1,条件1,…)

複数の条件付きで数値の合計を行います　

SUMPRODUCT関数

読み方： サムプロダクト　　

分類： 数学/三角 

SUMPRODUCT(配列1,[配列2],[配列3],…)

複数の数値の組を掛け合わせて合計を行います　

SUMSQ関数

読み方： サムスクウェア　　

分類： 数学/三角 

SUMSQ(数値1,[数値2],…)

数値の2乗の合計を算出します　

Excel。列を非表示にしても、手早くレコードの合計値が変わるようにしたい【Column total】

Excel。列を非表示にしても、手早くレコードの合計値が変わるようにしたい

＜CELL＋SUMIF関数＞

簡単そうに思えるのですが、次のような表があって、

店舗を非表示にしても、F列の合計値の値を連動して算出したいのですが、これが、簡単にできません。

F2の数式は、

=SUM(B3:E2)

です。

B列を非表示にしてみましょう。

F列の合計値は、変わっていません。

つまり、列の非表示に対応していないわけですね。

SUBTOTAL関数やAGGREGATE関数は、行の非表示には対応していますが、列の非表示には対応していないので、つかえません。

では、どのようにしたら、手早く、列の非表示に連動した合計値を算出できるのでしょうか？

着目点を変えてみましょう。

そもそも、非表示ということは、列幅がゼロということです。

列幅が0だったら計算範囲から除外することができればいいわけですね。

列幅を算出するならば、CELL関数をつかえば、算出することができます。

B6にCELL関数をつかった数式を設定します。

=@CELL("width",B1)

オートフィルで数式をコピーしています。

CELL関数の前に「＠（アットマーク）」をつけないと、スピル機能が使いされたことで、セルごとに算出できません。

あとは、F列の数式をSUMIF関数に変更します。

F2の数式を、

=SUMIF($B$6:$E$6,">0",B2:E2)

と設定します。

範囲には、$B$6:$E$6

列幅を算出した範囲です。オートフィルで数式をコピーしますので、絶対参照を忘れないようにします。

検索条件は、「">0"」。

比較演算子と数値を組み褪せて使うときには、「”（ダブルコーテーション）」で囲む必要があります。

合計範囲は、B2:E2

列を非表示にして確認してみます。

しかし、F列の数値が変わっていません。

ダメじゃんというわけではなくて、CELL関数は、自動的に再計算されないので、「F9」キーを押すか、数式タブの「再計算実行」をクリックします。

これで、再計算されましたので、F列の合計値が変わったことが確認できました。

このように、列を非表示にしても、合計値を連動して算出するには、簡単に算出することができないようですので、ちょっとアイディアが必要なようです。

Excel。一部の文字が含まれているデータの合計を手早く算出したい【characters】

Excel。一部の文字が含まれているデータの合計を手早く算出したい

＜SUMIF関数+ワイルドカード＞

商品名に「定食」と定食が含まれている商品の合計を算出したい場合、どのようにしたらいいのでしょか？

次の表を用意してみました。

 

条件付きで合算する場合には、単一条件ならば、SUMIF関数。

複数条件ならばSUMIFS関数をつかうわけですが、検索条件が、「完全一致」でなければ、算出対象にはなりません。

B列の商品名をみると、「定食」という文字が入っている商品は、「A定食」「B定食」「A定食コーヒー付き」の3つあります。

出来れば、「分類」のような列に「定食」と入力されていれば、SUMIF関数で簡単に算出することができますが、この表にはありません。

「定食」という文字が含まれているものを算出したい場合には、「ワイルドカード」をつかうことで、手早く算出することができます。

F1に数式を設定します。

=SUMIF(B2:B11,"*"&E1&"*",C2:C11)

この数式で算出したのが、F1です。

SUMIF関数の引数で、ポイントになるのが、「検索条件」です。

最初の引数の「範囲」は、次の引数の「検索条件」が含まれているところになりますので、「B2:B11」。

今回は、オートフィルで数式をコピーする必要がないので、絶対参照は不要です。

2つ目の引数が、ポイントの「検索条件」です。

含まれるという条件にしたいので、ワイルドカードを、部分一致する文字を前後で囲みます。

よって、「検索条件」は「"*"&E1&"*"」とします。

E1には、「定食」という文字が入力されているので、それを使用していますが、「ワイルドカード」の「*（アスタリスク）」をE1の前後につけることで、「含まれる」という条件にすることができます。

注意点は「*（ワイルドカード）」を「”（ダブルコーテーション）」で囲む必要があります。

また、文字結合しますので、「&（アンパサンド）」をつかって接続します。

最後の引数は、「合計範囲」なので、「C2:C11」を設定します。

SUMIF関数など、検索条件がある数式の引数に、ワイルドカードを合わせてつかうことで、その文字を含むというような条件にすることができます。

Excel。四半期別に集計したいので、四半期を判定するのにIFS関数をつかってみる

＜MONTH+IFS関数&SUMIF関数＞

売上データを四半期別に集計する場合、売上日がどの四半期に所属しているのかがわからないと、四半期別に集計することができません。

四半期を判定する考え方自体は、シンプルです。

4-6月だったら、「1」それ以外は…というように、IF関数のネストを繰り返す方法も悪くはありませんが、面倒です。

CHOOSE関数を使う方法もありますが、せっかくIFS関数という新しい関数が追加されたわけですから、使わないのはもったいない。

IF関数でネストを繰り返すよりもシンプルに、つくることができる、IFS関数をつかって、今回は、四半期別集計をおこなっていきます。

事前の準備として、D2:D5。第１四半期と表示されていますが、セルの値自体は「1」～「4」としています。

表示形式のユーザー定義をつかって、「第１四半期」と表示させています。

これは、どの四半期に所属しているのかを「1」～「4」の数値で算出するため、SUMIF関数をつかって集計する時に、効率よく数式を作るために行っています。

C2に次の数式を設定します。

=IFS(MONTH(A2)>=10,3,MONTH(A2)>=7,2,MONTH(A2)>=4,1,TRUE,4)

数式を説明します。

A2の月をMONTH関数で算出します。

その値が10以上。

つまり、10～12月だったら、第3四半期なので「3」と算出させています。

次の条件は、「7以上なのか」とします。

すでに、10以上は除外されますので、7～9月だったら、第２四半期なので「2」と算出させます。

最後のTRUEは、該当しないものはということなので、1～3月なので、第４四半期に当たりますから「4」と算出させることができます。

IF関数のネストで数式を作成するよりも、コンパクトで、しかも他の関数をつかうよりも、わかりやすい数式です。

この数式をオートフィルでコピーします。

C2:C11にどの四半期に分類されいるのかがわかりました。

E2にSUMIF関数をつかって、集計します。

E2に設定した数式は、

=SUMIF($C$2:$C$11,D2,$B$2:$B$11)

これで、四半期別に集計することができました。

今回は、IFS関数をつかって、四半期がどの四半期に所属しているのか判別しましたが、IFS関数が搭載されていないExcelのバージョンだと当然、IFS関数をつかうことはできませんので、他の関数をつかった方法も知っておくといいかもしれませんね。

さて、最後に、C列。

算出結果が表示されたままでは、カッコ悪いので、算出結果が見えないようにしておきましょう。

C2:C11を範囲選択して、セルの書式設定ダイアログボックスを表示します。

表示形式のユーザー定義にあわせます。

種類に「;（セミコロン）」を3個「;;;」とすることで、文字を非表示にすることができます。

これで、完成です。

Excel。同じデータごとに累計値を算出したいけど、どうやったら簡単に求めることができるのか。【Cumulative】

Excel。同じデータごとに累計値を算出したいけど、どうやったら簡単に求めることができるのか。

＜SUMIF関数＞

同じデータを見つけながら和算で累計値を算出すればいいことはわかっていても、膨大なデータから目視で探しながらというのは、面倒というか、できません。

例えば、次のようなデータ。

D列には、店舗別の累計値を算出してあります。

たった10件のデータですが、1件目の新宿店の売上高を次の新宿店の売上高と目視でみつけて、和算することを繰り返すのは中々面倒です。

データを店舗名ごとに並び替えてしまえば、データが密集するので、SUM関数と範囲の最初を絶対参照にする方法で累計値は簡単に算出することはできますが、データはそのままで店舗別の累計値を算出するには、どうしたらいいのでしょうか？

考え方としては、累計値を算出する数式の延長線上にあります。

累計値を算出するだけならば、D2の数式は次のように設定すれば、いいわけです。

=SUM($C$2:C2)

和算なのでSUM関数をつかいます。

それと、引数の範囲の始点を絶対参照にしておくことで、オートフィルで数式をコピーすると自動的に算出範囲が広がってくれます。

ちょっと工夫をすることで、累計値をSUM関数で算出することができるわけです。

本題にもどりましょう。考え方としては、「店舗名が新宿という条件がついている」ということですから、条件付き和算。

つまり、「SUMIF関数」を使用すればいいわけですね。

D2に設定する数式を、

=SUMIF($B$2:B2,B2,$C$2:C2)

と設定して、数式をオートフィルで数式をコピーしたら完成です。

目視で確認しながら和算させることを考えたら、時短で算出することが出来ましたね。

では、数式の説明をしておきましょう。

最初の引数の「範囲」には、「$B$2:B2」。

これは、次の引数の「条件」が含まれているデータのある範囲に該当しますから、B列の店舗名を設定します。

ポイントとなるのは、先程と同じで、始点となるセル番地を絶対参照にしておくことでした。

つぎの引数である「条件」は、「B2」を設定します。

引数の3つ目は、「合計範囲」。C列の売上高です。

ここも、始点を絶対参照にしますから、「$C$2:C2」と設定します。

今回は、データごとに累計値を算出するということでしたが、ちょっとしたアイディアで算出することができましたので、機会があれば、SUMIF関数をつかって累計値を算出してみてはいかがでしょうか。

Excel。年度別で4～9月を上半期それ以外は下半期として楽に合計値を算出したい【Aggregated semi-annually】

Excel。年度別で4～9月を上半期それ以外は下半期として楽に合計値を算出したい

＜IF+MONTH関数・SUMIF関数＞

日付関係の計算というのは、簡単に算出出来るイメージはするのですが、実際につくってみると厄介だったりします。

次のデータは、ある商品の販売金額を降順にしたものです。

今回は、4-9月を上半期、10-3月を下半期とします。

A1:C11までのデータから年度別上半期合計をF1に下半期合計をF2に算出しております。

今回は、単年の表なので、2020/10/1以前という条件で合算させてしまえば、上半期合計を算出することができますが、複数年のデータの場合、そう単純な数式では対応できません。

算出するにあたり、基本的な考え方としては、一度上半期か下半期かを判断させる必要があります。その後上半期と下半期ごとに合計値を算出していきます。

上半期と下半期を判断させる方法としては、

D2に次のような数式を作ることが多いかと思います。

=IF(AND(MONTH(B2)>=4,MONTH(B2)<=9),"上半期","下半期")

AND関数をつかった数式ですね。

AND関数をつかうと、期間のように、「ここ～ここまで」という条件をつくることができます。

この方法でも上半期と下半期を判断することができますが、AND関数が苦手な人向けというか、Excelならではの数式というものあります。

D2に次のような数式を作成します。

=IF((MONTH(B2)>=4)*(MONTH(B2)<=9),"上半期","下半期")

この数式でも、上半期と下半期を先程のAND関数同様に算出してくれます。

確かにAND関数は使っていませんが…

(MONTH(B2)>=4)*(MONTH(B2)<=9)　は何をしているのでしょうか？

Excelのスキルアップの一環だと思って確認していきましょう。

それぞれをバラして説明してきます。

 

H2には、(MONTH(B2)>=4)

I2には、(MONTH(B2)<=9)

という数式を設定しております。

条件を満たせば「TRUE」。

満たさないと「FALSE」を返します。

このTRUEとFALSEだとなんだかわかりませんので、H列I列の結果に「×1」してみます。

ご覧のように、TRUEだと「1」。FALSEだと「0」と算出されましたね。

そう、Excelでは、TRUEを1。FALSEを0としているアレです。

これを「×（乗算）」して「1」ならばTRUEとなり条件は成立する、つまり「上半期」と判断できるわけです。

あとは、上半期と下半期ごとに合計値を算出したいので、SUMIF関数を使えば、それぞれを算出することができます。

F1の数式は、

=SUMIF($D$2:$D$11,"上半期",$C$2:$C$11)

F2の数式は、

=SUMIF($D$2:$D$11,"下半期",$C$2:$C$11)

これで4-9月を上半期、10-3月を下半期として、それぞれの合計値を算出することができました。

ところで、算出はできたのですが、D列につくった判断するための途中計算が見えていて資料としては、カッコ悪いですね。

最後に、D列の途中計算結果を表示形式で非表示にしましょう。

D2:D11を範囲選択して、セルの書式設定ダイアログボックスを表示します。

表示形式のユーザー定義で、「;;;」（セミコロン×3）と設定してOKボタンをクリックします。

これで完成しましたね。

日付関係や期間計算は、なかなか数式一発で算出とはいかないケースが多いようですが、自分にあった方法を見つけられるといいですね。

Excel。一行おきの数値の合計を簡単に算出したいけどどうしたらいいの？【Total every other line】

Excel。一行おきの数値の合計を簡単に算出したいけどどうしたらいいの？

＜MOD&ROW関数とSUMIF関数＞

やりたいことは簡単でも、実際に算出するとなると、どうしたらいいの？と思うことって結構あります。

例えば次のような表。

1行目には販売数が2行目には金額が入力されている表。

算出したいのは、それぞれの合算値です。

今回は、わかりやすいように少ないデータにしましたが、データの件数が増えたら、一行おきに範囲選択するのは、とても面倒ですし、ミスが発生する確率も当然高くなります。

できれば、販売数の列、金額の列というように、列ごとに管理してくれていれば、困ることはなかったのですが、今から作り直すのも面倒。

Excel VBAでマクロを作成するというのもいいのですが、もっと簡単に算出する方法はどのようにしたらいいのでしょうか？

今回のポイントは、奇数行なのか？偶数行なのか？ということです。

販売数は奇数行ですから、奇数行ごとに合算すれば、販売数の数値を算出することが出来ますし、偶数行ならば、金額の合算値を求めることができます。

奇数かどうかを判断する、ISODD関数や偶数かどうかを判断するISEVEN関数などもありますが、そんな珍しくない関数でも算出することができます。

その関数は、MOD関数とROW関数。

MOD関数は、除算した結果の「あまり」を算出することができる関数です。

ROW関数は行番号を算出することができる関数です。

つまり、行番号を2で除算してあまりのあるなしで、販売数と金額とをわけて結果を算出することができます。

D列に奇数行なのか偶数行なのか、判断させる数式を作ります。

D1をクリックして、次の数式を作ります。

=MOD(ROW(),2)

算出したら、オートフィルで数式をコピーします。

この数式。

どこかで見たことがある人もいるかもしれませんね。

条件付き書式で一行おきにセルに塗りつぶしを設定するのと同じですね。

この数式の意味ですが、

ROW関数は、行番号を算出します。

MOD関数で、その行番号を、「2」で除算した結果の余りを算出します。

余りが0ならば偶数ですし、1ならば奇数というのがわかります。

最後に、それぞれの合計値を算出します。ここで登場するのはSUMIF関数。

C9にSUMIF関数の数式をつくっていきます。

範囲は、$D$1:$D$8。MOD&ROW関数で算出したところが範囲になります。

検索条件は、1。

合計範囲は、$C$1:$C$8。

範囲や合計範囲で絶対参照を設定しているのは、オートフィルで金額も算出するためです。

C9の販売数の合計値の数式は、

=SUMIF($D$1:$D$8,1,$C$1:$C$8)

C10の金額の合計値の数式は、

=SUMIF($D$1:$D$8,0,$C$1:$C$8)

これで、一行おきの値を使った、合計値を算出することができました。

現場では、関数をつかうといいのか、それとも他の方法がいいのか、アレコレ考えてみると意外な方法を見つけることが出来るかもしれませんね。

Excel。列を非表示にしたら合計値も連動して再計算させたい【Column total】

Excel。列を非表示にしたら合計値も連動して再計算させたい

＜CELL関数・SUMIF関数・スピルストップ＞

本当ならば、元データから直接ピボットテーブルで算出させたほうが楽だろうと思う資料は、概ねどの現場にもあると思います。

例えば、次のような表。

なんてことない表ですが、G列を非表示にした時に、H列の合計値も表示されているデータだけで再計算してほしいというのが、やりたいことなんですね。

なお、F2に設定されている数式は、

=SUM(B2:E2)

というお馴染みのSUM関数をつかっております。

また、念のために、G列を非表示にしても、合計値が連動しないことを確認しておきましょう。

残念ながら、連動してくれません。

SUBTOTAL関数を使えばいいのでは？と考えるかもしれませんが、SUBTOTAL関数は、『行』の非表示には対応しますが、『列』の非表示には対応してくれません。

昔から使っている帳票類なので、ピボットテーブルも使えない。

ピボットテーブルならば、オートフィルターで抽出したアイテムのみで合計値を算出することができますが、普通のExcelの表では、非表示も含めた範囲で算出してしまいます。

そこで、力技ですが、次のような方法で問題を解決することができます。

考え方ですが、「列の非表示」とはどういう状態なのかといえば、列幅が「ゼロ」ということですね。

要するに、列幅がゼロより大きければ、計算対象になるようにすればいいわけですね。

「列幅がゼロよりも大きいものを総和する」。

どうやらSUMIF関数でいけそうですね。

あと問題なのは、「列幅ゼロ」というのどうやったら算出することが出来るのでしょうか？

そこで、登場するのが、『CELL関数』です。

CELL関数は、対象のセルのステータスを確認できる関数です。

このCELL関数の引数に列幅がどのぐらいなのかを算出するものが用意されています。

B6に次の数式を設定して、列幅を算出します。

=CELL("width",B1)

ところが、上手く算出してくれません。

原因は、Excelの新機能の、「スピル」が影響したためです。

スピルがないExcelのバージョンでしたら問題ありませんが、Microsoft365のExcelだと、スピルの影響でオートフィルで数式をコピーすることができません。

もし、スピル機能があるExcelを使っている場合には、次のように式を変更すれば大丈夫です。

B6に設定した数式は、

=@CELL("width",B1)

「@」をつけることで、スピル機能を停止した数式を作ることが出来ます。

あとは、E6までオートフィルで数式をコピーします。

これで、列幅を算出することができました。

F2の数式をSUM関数から次のように変更します。

=SUMIF($B$6:$E$6,">0",B2:E2)

オートフィルで数式をF5までコピーします。

E列を非表示にしてみましょう。

あれ？合計が変わっていません。実はこのCELL関数。

再計算させないといけない関数なので、「F9キー」を押して、再計算させましょう。

これで、非表示を除いて合計値を算出することができました。

今回のように、簡単そうに見えて、なかなか面倒という場合もありますが、少しずつ改良して便利にしていけるといいですね。