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。時間の合計を、○時間○分で表示したい。【Time calculations】

Excel。時間の合計を、○時間○分で表示したい。

＜SUM関数＋表示形式＞

作業工程の予想時間の表があります。

B列の予想時間の単位は「分」です。

B2:B5の合計分数が、B6に算出されています。

B6の数式は、

=SUM(B2:B5)

ただ、結果は「分」なわけです。

このままでもいいのですが、わかりにくいので、○時間○分という表示に変えていきます。

B6をクリックして、セルの書式設定ダイアログボックスを表示します。

セルの書式設定ダイアログボックスは、Ctrl+1というショートカットキーで表示するのが便利です。

セルの書式設定ダイアログボックスが表示されたら、表示形式タブの分類を時刻。

時刻にある「13時30分」を選択してOKボタンをクリックしてみることにします。

なんと、0時間00分と表示されてしまいました。

原因は、B6の値。

日時は、シリアル値で考えなければいけません。

190分と思っていても、190という数値でしかありません。

そこで、時間に直す必要があります。

Excelは1日を1としておりますので、1時間は1/24です。

さらに、分なので、1/24した値から1/60しなければなりません。

まずは、数式を修正しましょう。

=SUM(B2:B5)/24/60

これで、3時10分　と表示されました。

あとは、3時10分を3時間10分に変更するだけです。

改めて、B6をクリックして、セルの書式設定ダイアログボックスを表示しましょう。

表示形式タブの分類「ユーザー定義」をクリックします。

種類を、

h"時間"mm"分";@

と修正します。

「;@」はなくてもOKです。

これで、完成です。

Excelは日付や時間の場合、シリアル値という数値であることを押さえておくと、いいかと思います。

Excel。最大値と最小値を除いた平均値をLET関数で算出してみる【Excluding】

Excel。最大値と最小値を除いた平均値をLET関数で算出してみる

＜SUM+MAX+MIN+COUNT関数・LET関数＞

最大値と最小値を除いた平均値は、AVERAGE関数では算出することができません。

AVERAGE関数をつかわないで、平均値を算出するには、範囲の総和を範囲の総和した件数で除算すれば、算出することができます。

C2:C6まで、売上金額が入力されています。

この範囲の最大値と最小値を除いた平均値を算出するために、D8に数式を設定しました。

=(SUM(C2:C6)-MAX(C2:C6)-MIN(C2:C6))/(COUNT(C2:C6)-2)

長いですが、シンプルな関数しかつかっていません。

だけど、C2:C6という範囲選択が何度も登場してきます。

数式を説明すると、

SUM(C2:C6)-MAX(C2:C6)-MIN(C2:C6)

SUM関数で、合計を算出します。

その値から、MAX関数で最大値をMIN関数で最小値を減算します。

この値を

COUNT(C2:C6)-2　の算出結果で除算します。

COUNT関数で数値の件数を算出することができます。

「-2」するのは、最大値と最小値のデータを除いた件数にする必要があるからです。

この数式で算出して、何ら問題はありません。

今回は、この数式をLET関数で、算出してみます。

LET関数は、数式内に「名前の定義」というか、「変数」を設定することができる関数です。

ただ、どのように使うのか、わかりにくい関数といわれています。

そこで、比較的わかりやすい　範囲選択　を名前に設定してみます。

D9にLET関数をつかって数式をつくります。

=LET(rg,C2:C6,(SUM(rg)-MAX(rg)-MIN(rg))/(COUNT(rg)-2))

数式の文字数としては、差はあまりありませんが、C2:C6を何度もつかわないので、可読性は改善されています。

では、この数式の説明をします。

LET関数の最初の引数は、名前。

プログラムでいうところに変数。あるいは、名前の定義です。

今回は、範囲選択なので、Excel VBAでお馴染みのRangeの「rg」としました。

2つ目の引数は、式。

式となっていますが、数式でもいいし、範囲でも指定できます。

繰り返し設定するところを設定するといいので、「C2:C6」としました。

この名前と式を複数設定することも出来ます。

3つ目は、計算式。

SUM(rg)-MAX(rg)-MIN(rg))/(COUNT(rg)-2

C2:C6だったところが、rgという名前を設定しましたので、rgにすることで、C2:C6と入力しなくてもいいわけです。

このように、LET関数をつかうと、複雑な数式の時に、可読性の改善と、数式もコンパクトにすることができるかもしれません。

Excel。複数列のデータから、重複を除いた件数を手早く求めたい【overlapping】

Excel。複数列のデータから、重複を除いた件数を手早く求めたい

＜SUM+COUNTIF関数＞

重複を除いた件数を算出する方法は色々あります。

今回は、複数列にあるデータから重複を除いた件数を手早く算出するには、どのようにしたらいいのでしょうか。

B2の入力されている「箱根」は、4つありますが、これを1として数えたいわけです。

つまり、重複されているものは1件として数えるわけです。

「重複を除く」という条件で数えたいわけですから、COUNTIF関数をつかうわけですが、COUNTIF関数だけは対応することができません。

そこで、D8には、次の数式をつくってみました。

=SUM(1/COUNTIF(B2:D6,B2:D6))

これで、8件と算出することができました。

数式の仕組みを確認します。

SUM関数は、いいとして、COUNTIF関数の動きがどのようになっているのか確認します。

F1を起点として、先程の表をコピーしました。

G2に、

=COUNTIF(B2:D6,B2:D6)

とSUM関数内のCOUNTIF関数の数式部分を抽出した結果を算出してみました。

箱根は4件あるということがわかります。

1のところは、1件しかないというわけです。

続いて、数式を修正します。

=1/COUNTIF(B2:D6,B2:D6)

算出された数値を全部合算することで、8と算出されるという仕組みです。

先程、4と数えられたものを1としたいわけです。

なので、1/4とすれば0.25となります。

0.25が4つあるので、1とすることができるという数式をつくってみたというわけです。

そのため、

=SUM(1/COUNTIF(B2:D6,B2:D6))

という数式で、重複を除いた件数を算出できたというわけです。

Excel。日付から、作業列（条件）をつくらないで、月別集計をしたい。【totalling】

Excel。日付から、作業列（条件）をつくらないで、月別集計をしたい。

＜SUM+IF＋MONTH関数＞

A列には、セミナー開催日が入力されている表があります。

B列には、セミナー別に参加した人数が入力されており、E列に、月別ごとの集計をしたいわけです。

そこで、1つの数式だけで月別集計を行うことはできないのでしょうか。

月別集計を行いたい場合、日付から何月なのかがわからないと集計することができません。

作業列として、MONTH関数をつかって、月を用意する必要があります。

また、期間計算でおこなうとしても、結局のところ、算出するための作業列をつくらないと算出することができません。

実のところ、合計を算出するSUM関数の引数にIF関数を組み込むことで、1つだけの数式で算出することができます。

E2に次の数式を設定しました。

=SUM(IF(MONTH($A$2:$A$10)=D2,$B$2:$B$10,0))

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

このように、1つの数式だけで、月別集計を算出することができました。

使用している関数は、SUM関数とIF関数とMONTH関数という比較的、馴染みのある関数だけで算出することができました。

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

IF関数の論理式。

MONTH($A$2:$A$10)は、配列関数ですが、スピル機能が搭載されたExcelだと、配列関数と意識することなく、数式をつくることができます。

A2＝D2だったら、B2。

そうでなければ0（ゼロ）とします。

これを、A10までおこなうわけです。

その値を、SUM関数で、和算するというわけです。

Excel。手早く曜日ごとの売上高を集計したいけど、どのようにしたらいいの。【by day of the week】

Excel。手早く曜日ごとの売上高を集計したいけど、どのようにしたらいいの。

＜SUM＋IF＋WEEKDAY関数＞

曜日ごとに集計したい場合、曜日がわかるように一度数式をつくり、それを条件として集計する方法を行うことがあります。

途中の計算式を作らなくても、SUM関数とIF関数とWEEKDAY関数を組み合わせるだけで、数式一つで、曜日ごとの集計をすることができます。

次の表を用意しましたので、確認していきます。

B1:C11にデータがあります。

曜日が表示されていれば、SUMIF関数をつかえば、算出することができますが、曜日を表示されていません。

通常、日付に基づいた曜日を算出させてから、曜日を条件として集計するわけですが、それでは、数式を複数作る必要があります。

そこで、G2には、次の数式を設定しています。

=SUM(IF(WEEKDAY($B$2:$B$11,1)=E2,$C$2:$C$11,0))

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

これで、曜日ごとの売上高の集計を算出することができました。

数式を説明すると、

WEEKDAY($B$2:$B$11,1)=E2

WEEKDAY関数は、曜日を算出する関数です。

WEEKDAY関数の2つ目の引数にある「種類」を1と設定することで、1を日曜日で7を土曜日として数値を割り振ってくれます。

E列のNOをつかって、合致することで、何曜日なのかを判断することができます。

そこで、IF関数をつかうことで、合致しなかった場合は、「0（ゼロ）」とすることができます。

最後に、SUM関数をつかって、集計します。

以前は、この数式は、配列関数で作成したのですが、Microsoft365では、普通に数式をつくるだけで、対応してれます。

本当に、合っているのか、念のため確認してみましょう。

A2に曜日を算出する数式をつくります。

=TEXT(B2,"aaa")

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

日曜日は、983+874なので、1857とG2の値と合致していることがわかります。

今回紹介した数式ですが、WEEKDAY関数をMONTH関数やYEAR関数にすることで、作業列に算出してから集計しなくても、数式一つで算出することができます。

Excel。季節変動や採点競技でお馴染みのトリム平均を算出したい【trimmed average】

Excel。季節変動や採点競技でお馴染みのトリム平均を算出したい

＜SUM＋MAX＋MIN＋COUNT関数＞

データ内から最高点と最低点を除いた平均値であるトリム平均値を算出するには、単純にAVERAGE関数では算出することはできません。

理由は簡単で、最大値と最小値を除くことができないからです。

そこで、データの総和と、その総和したデータの個数で除算した数値が平均値です。

そのデータの総和から最大値と最小値を減算してあげたものを、データの個数から「-2」した値を除算すれば、トリム平均値を算出することができるというわけです。

次のデータを使って確認しておきましょう。

E1には、AVERAGE関数の数式を設定してあります。

=AVERAGE(B2:B9)

E2には、次の数式を設定することで、トリム平均値を算出しています。

=(SUM(B2:B9)-MAX(B2:B9)-MIN(B2:B9))/COUNT(B2:B9)-2

関数が多く登場してますが、いたってシンプルです。

SUM関数は総和を算出しています。

そこ値からMAX関数の最大値とMIN関数の最小値を減算します。

その値を、COUNT関数をつかって総和した件数から最大値と最小値の2件を減算した値で除算したのが、E2の算出結果である。

トリム平均値ということになります。

トリム平均値は、外れ値を除いた平均値を算出したいときにも使用する数式です。

Excel。ABC分析の表をスピル機能で、手早くつくってみよう。【ABC analysis】

Excel。ABC分析の表をスピル機能で、手早くつくってみよう。

＜SUM関数・IFS関数＞

パレート図のもとになる、「ABC分析」。

色々な関数をつかってABC分析の表をつくるのですが、Excelに加わったスピル機能をつかってつくってみると、今までよりも、手早く、つくることができます。

C列の構成比を算出します。

C2に設定した数式は、新宿の売上高を全体の合計で除算するわけですが、合計値はありませんね。

また、合計を絶対参照にして、除算しなければいけません。

ところが、スピル機能の登場によって、C2の数式は、次のように設定することができます。

=B2:B11/SUM(B2:B11)

オートフィルで数式をコピーすることもなく、スピル機能によって、C3:C11に数式が反映（ゴースト）されます。

D列の構成比の累計値は、始点のセルを絶対参照にした、数式をつくります。

D2の数式は、

=SUM($C$2:C2)

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

C列とD列は、パーセント表示の小数点第一位という設定をすることで、視覚的にわかりやすくします。

E列は、構成比の累計からABCのランク分けを行います。

今回は、80%未満は、A。90%未満はBで、それ以外はCとします。

三分岐なので、色々な関数で算出することができますが、今回はIFS関数を採用しました。

E2のIFS関数をつかった数式は、

=IFS(D2:D11<80%,"A",D2:D11<90%,"B",TRUE,"C")

スピル機能が使えるので、セル番地を指定するのではなく、範囲選択をD2:D11とすることで、オートフィルで数式をコピーする必要がありません。

IFS関数では、「TRUE」を論理式でつかうことで、「それ以外」として設定することができます。

これで、ABCとランク分けすることができました。

今までつかっていた数式もスピル機能に対応した数式に変更することによって、可読性が向上するなど、メリットもあるかもしれませんので、見直してみるのもいいかもしれませんね。

Excel。「または」のOR条件で手早く合計を算出するにはどうしたらいい。【OR condition total】

Excel。「または」のOR条件で手早く合計を算出するにはどうしたらいい。

＜SUM＋SUMIFS関数＞

複数条件の合計を算出するならば、SUMIFS関数を使います。

このSUMIFS関数で、「または」のOR条件で算出したい時には、アイディアが必要です。

次の表をつかい、まずは対応方法から確認していきましょう。

A1:D9に表があります。

F1:F3に条件を用意してあります。

今回は、商品名が、「鉛筆」と「ボールペン」の売上高の合計値を算出します。

そこで、F6には、次の数式を用意します。

=SUM(SUMIFS(D2:D9,B2:B9,F2:F3))

これで、「鉛筆」と「ボールペン」の売上高の合計を算出することができます。

なぜ、SUMIFS関数だけだと、スピル機能によって、鉛筆とボールペンごとの合計値を算出するだけで、条件に合致した合計値は算出してくれません。

F6の数式は、

=SUMIFS(D2:D9,B2:B9,F2:F3)

そのため、SUM関数をつかってネスト構造にすることで、合計値を算出することができたというわけです。

そして、一番の問題ですがSUMIFS関数は、「または」のOR条件は、対応していないということです。

F6の数式を変更してみます。

=SUMIFS(D2:D9,B2:B9,F2,B2:B9,F3)

もう一つ条件が追加されました。

ところが、結果を見ると、「0」と表示されています。

SUMIF関数を複数使って合算させる方法もあります。

=SUMIF(B2:B9,F2,D2:D9)+SUMIF(B2:B9,F3,D2:D9)

ただ条件が増えると、可読性が悪化するので、あまりお勧めできません。

Excel。分析ツールの基本統計量を関数で算出してみよう。【Basic statistics】

Excel。分析ツールの基本統計量を関数で算出してみよう。

＜AVERAGE・STDEV.S・SQRT・COUNT・MEDIAN・MODE.SNGL・VAR.S・KURT・SKEW・MAX・MIN・SUM関数＞

アドインでデータ分析にある基本統計量をつかうことで、データのステータスを手早く確認することができます。

G列とH列に表示されているのが、C列のListeningの基本統計量です。

H列の基本統計量の値は、関数でも算出できるので、確認しておきましょう。

H2の平均は、AVERAGE関数です。

=AVERAGE(C2:C11)

標準誤差は、標準偏差をデータの件数の平方根で除算した値です。

=STDEV.S(C2:C11)/SQRT(COUNT(C2:C11))

で算出することができます。

STDEV.S関数は、「S」なので、数値をサンプルとした標準偏差を算出する関数です。

SQRT関数は、平方根を算出する関数です。

COUNT関数は、数値の件数を算出関数です。

中央値は、MEDIAN関数で算出できます。

=MEDIAN(C2:C11)

最頻値は、MODE.SNGL関数で算出できます。

最頻値は、データ内で一番多く登場した数値のことですね。

=MODE.SNGL(C2:C11)

標準偏差は、先程紹介した、STDEV.S関数で算出します。

=STDEV.S(C2:C11)

標準偏差は、データが平均値からどのぐらい外れているか（散っているのか）を表します。

分散は、VAR.S関数で算出します。

=VAR.S(C2:C11)

尖度（せんど）は、KURT関数で算出できます。

=KURT(C2:C11)

尖度は、正規分布を元に上下にどの程度偏っているかを表す値です。

歪度（わいど）は、SKEW関数で算出できます。

=SKEW(C2:C11)

歪度は、正規分布を元に左右にどの程度偏っているかを表す値です。

範囲は、最大値と最小値の範囲（レンジ）です。

最大値から最小値を減算すれば算出できます。

=MAX(C2:C11)-MIN(C2:C11)

最小は、MIN関数ですね。

=MIN(C2:C11)

最大は、MAX関数ですね。

=MAX(C2:C11)

合計は、SUM関数ですね。

=SUM(C2:C11)

最後は、データの個数なので、COUNT関数です。

=COUNT(C2:C11)

このように、基本統計量は様々な関数をつかって算出しています。

算出する項目が多いため、面倒なので、分析ツールをつかえるようならば、つかったほうが、楽なような気がします。

Excel。合計した時間を、時分表示にするには、どうしたらいいの。【total time】

Excel。合計した時間を、時分表示にするには、どうしたらいいの。

＜表示形式とSUM関数＞

時間計算は、Excelが苦手としているものの一つだといえます。

苦手というか、面倒といったところでしょうか。

次の表をつかって説明します。

B7は、B2:B6の分を合算した時間を表示してあります。

B7の数式は、

=SUM(B2:B6)

と設定してみましょう。

B7の結果は150で単位は「分」です。

150分ではわかりにくいので、単位を「時分」に変えてみることにします。

B7をクリックして、セルの書式設定ダイアログボックスを表示します。

表示形式タブの分類から「時刻」をクリックして、種類を「13時30分」の時分を選択してOKボタンをクリックします。

ところが、表示がおかしくなっています。

0時00分と表示されています。

原因は、B7の値は単位こそ「分」としていますが、実際は150という数値なわけです。

Excelは1日を1としました。

つまり1=1日=24hですから、1/24しないといけません。

そして、1時間は60分ですから、1分は1分=1/60です。

よって、B7の値を24で除算して、さらに60で除算する必要があるというわけです。

それでは、B7の数式を、修正します。

=SUM(B2:B6)/24/60

あとは、表示形式を

h"時間"mm"分"

と修正したら完成です。

Excelでは時間計算をするときなどに、1=1/24ということを、頭の中に入れておく必要がある場合がありますので、注意が必要ですね。

Excel。小計など表中の和算はSUM関数だと手早く求めることができます。【subtotal】

Excel。小計など表中の和算はSUM関数だと手早く求めることができます。

＜SUM関数＞

四半期ごとの集計を求める場合、四則演算の「＋」をつかって算出するよりも、SUM関数をつかうことで、手早く算出することができます。

次の表を用意しました。

B5の第1四半期集計を算出したい場合、3件の和算なので、「=B2+B3+B4」と数式を作りがちかもしれません。

これを第2～第4そして、年間合計を算出したい場合、「+」を繰り返し使用して、範囲選択も面倒な作業です。

ところが、SUM関数には、自動的に範囲選択を調整してくれる機能があります。

まずは、B5をクリックして、オートSUMボタンをクリックして、SUM関数を設定します。

=SUM(B2:B4)

次に、B9の小計を算出したいので、B9をクリックしたら、オートSUMボタンをクリックしてみましょう。

B5には、SUM関数の数式があると、それ以降の数値から範囲選択をしてくれます。

B9の数式は、

=SUM(B6:B8)

最後のB10の合計を算出するのもSUM関数をつかってみましょう。

B10をクリックして、オートSUMボタンをクリックしてしてみます。

SUM関数をつかって算出したセルだけを範囲選択した数式をつくってくれます。

B10の数式は、

=SUM(B9,B5)

このように、単純な和算であっても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。表内で重複しないデータ数を一発で算出する簡単な方法はないのかな。【overlapping】

Excel。表内で重複しないデータ数を一発で算出する簡単な方法はないのかな。

＜SUM＋COUNTIF関数＞

帳票や表のデータが重複していない件数を算出するには、なかなか大変なケースもあります。

例えば、次の表。

B2:D4に入力されている地域名のうち、重複を除いた件数を、D6に算出したいわけです。

データタブの「重複削除」をつかって、残った件数を数えるというのもアリですが、今回のように、ひとつの列内にデータがあるわけではないので、「重複削除」をつかうことはできません。

また、別のところで、コピーして、ひとつの列をつくってから、「重複削除」を行う必要があります。

こうなると、作業は簡単でも、面倒になってきます。

また、Excel VBAでプログラム文をつくるというのも、ちょっと面倒です。

そこで、SUM＋COUNTIF関数を組み合わせた数式で、算出することができます。

D6に設定する数式は、

=SUM(1/COUNTIF(B2:D4,B2:D4))

この数式だけで、重複を除いた件数を算出することができます。

別のセルをつかって説明します。

COUNTIF関数で範囲の中に、その文字が何件あるかを算出させています。

B9に、

=COUNTIF($B$2:$D$4,B2)

という数式を設定して、オートフィルで数式をコピーしています。

すると、ハワイは表内で3件あることが算出されるわけです。

ただ算出しただけでは、1より大きい値のデータが重複していることがわかるだけです。

そこで、この算出した値を、「1」で除算してみましょう。

 

B9の数式は、

=1/COUNTIF($B$2:$D$4,B2)

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

この値全部を合算すれば、重複を除いた件数になります。

なぜ、「1」で除算したのかというと、例えば、2件あった場合、1÷2とすれば、「0.5」になるわけです。

2件ということは、別のセルで2と算出されたところも「0.5」となります。

0.5+0.5なので、1とすることができます。

4件あれば0.25+0.25+0.25+0.25　で、1とすることができる。

あとは、合算させればいいわけですね。

このような方法を使えば、数式だけで重複を除いた件数を算出することもできます。

Excel。区切りごとに連番を簡単に設定するにはどうしたらいいの【Serial number】

Excel。区切りごとに連番を簡単に設定するにはどうしたらいいの

＜IF+SUM関数＞

カテゴリごととか、区切りごとに連番を設定したい場合、オートフィルをつかってもいいのですが、区切りを確認しながら設定するのは、面倒です。

例えば、次の表をつかって、確認していきます。

C列のカテゴリNOは、B列のカテゴリが変わると、「1」から連番を振り直すようにしてあります。

連番は、オートフィルの連続コピーをつかうことで、設定することができます。

ただ、カテゴリがわかるなど、区切りがある場合には、単純な作業でも、面倒な作業となってしまうわけです。

そこで、C2に、次のような数式を設定することで、対応することができます。

=IF(B1=B2,SUM(C1,1),1)

設定したら、オートフィルで数式をコピーするだけです。

IF関数とSUM関数をネストしただけの数式ですが、この数式で対応することができます。

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

論理式は、「B1=B2」。

上のセルと同じかどうかを確認します。

値が真の場合は、「上のセルの内容と同じ」ということですから、上の値に+1するので、「SUM(C1,1)」と設定します。

SUM関数で「,（カンマ）」をつかった引数はあまり目にしないかもしれませんが、SUM(1,1)とすれば「2」と算出されます。

つまり「1+1」と同じ意味の数式です。

わかりにくければ、SUM(C1+1)としてもOKです。

値が偽の場合。

すなわち、カテゴリが変わった場合なので、連番を「1」に戻す必要がありますので、「1」と設定します。

このように、簡単な関数をつかった数式を用意するだけでも、作業効率を改善できるかもしれませんね。

なお、カテゴリを並べ替えても、カテゴリの区切りごとに連番を設定することができます。

 

Excel。入出金表の残高を手早く算出したいので、どのような数式にしたらいいの【Balance】

Excel。入出金表の残高を手早く算出したいので、どのような数式にしたらいいの

＜IF+SUM関数＞

単純そうなんだけど、意外とアイディアが必要になる数式というのがあります。

例えば、預金通帳をはじめとした、入出金表です。

次の表で説明します。

D列の残高は、現金残高なので、当然のことながら、行ごとの減算では算出することはできません。

要するに、D3に、

=B3-C3

という数式はありえないわけですね。

ひとつ上のセルの値に、入金。

つまりB列の数値があれば、和算しなければいけませんし、C列の出金に数値がある場合には、減算しなければいけないわけです。

また、修正などのことを考慮すると、D列の数式は、すべて同じにしたほうが、都合がいいわけですね。

そこで、

=D2+(B3-C3)

という数式をD3に設定してみると、残念ながら「#VALUE!」というエラーが表示されてしまいます。

なぜ、D2を設定したのかというと、入金-出金した値を上のセルに足すためですね。

しかし、D2には、「残高」という見出し行の文字が入力されています。

文字と数値を計算することはできませんので、「#VALUE!」というエラーが表示されてしまったわけです。

考え方はいいのですが、単純な四則演算では、このようにエラーが表示されてしまいます。

そこで、SUM関数をつかうことで、解決することができます。

D3につぎの数式を設定して、オートフィルで数式をコピーしました。

=SUM(D2,(B3-C3))

このように、単なる四則演算であっても、SUM関数をつかうことで、エラー表示を回避することができます。

なおSUM関数は、範囲選択内の数値以外は対象外になるので、エラーが発生しないわけですね。

あと、プラスアルファとして、D6は入金・出金の入力が無いにも関わらず、残高が表示されているので、IF関数をつかって、入力がなければ、空白のままにするといいですね。

そこで、数式を次のように修正してみるといいですね。

=IF(A3="","",SUM(D2,(B3-C3)))

Excel。セルごとに分割された数値を合算し、その値もセルごとに分割したい【cell merging】

Excel。セルごとに分割された数値を合算し、その値もセルごとに分割したい

＜CONCAT・SUM・IFERROR・MID・LEN・COUNT関数＞

なんでそんなことしちゃったのという、Excelの帳票をみることがあります。

例えば、数値をセルごとに分割してある帳票。

そもそも、1500と分割しないでセル内にあれば、単純な和算で済むわけですが、セルごとに分割してあるわけですね。

このような場合、一の位で合算して、繰り上がったら…なんてやっていたら、桁数が増えたら大変というか、数式を考えるだけでも大変です。

最初にやることは、結合して1つのセルにまとめて数値にします。

A6に次の数式を作り、交通費を一つのセルにまとめます。

A6に設定した数式は、

=CONCAT(B2:G2)

同じように、A7に、宿泊費を一つのセルにまとめます。

A8には、合算値を算出したいので、A8に設定する数式は、

=SUM(A6:A7)

ところが、「0」と算出されてしまいました。

原因は、よくみると、A6とA7は、左揃えになっています。

これは文字型になっていることを表しています。

CONCAT関数は、文字結合をする関数なので、算出されたものは、文字になっていて数値ではないわけです。

そこで、A6とA7の数式のうしろに「*1」を追加します。

=CONCAT(D2:G2)*1

こうすると、文字型は文字型でも、数値文字型に変わるので、合算値を算出することができます。

あとは、どうやって、一つずつのセルに分割して表示させる数式を考えていきます。

合計のB4の数式は、

=IFERROR(MID($A$8,LEN($A$8)-COUNT(C1:$G$1),1)*1,"")

あとは、この数式をG4まで、オートフィルで数式をコピーすれば完成です。

数式が長くなったので、説明していきます。

B1:G1までの見出し行は、「十万・一万…一」と表示してますが、元は、普通の数値が入力してあって、表示形式をつかって、漢数字にしています。

別に直接、漢数字で入力しても問題はありません。

最初の「IFERROR関数」は、数値がない場合、#VALUEというエラーが発生するので、エラーならば空白にするようにしています。

MIDはセル内の文字列を指定の場所にある、指定した文字数分だけ抽出する関数です。

先程、合算値を算出したA8を指定します。

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

何文字目の文字なのかを指定します。

「LEN($A$8)-COUNT(C1:$G$1)」

LEN関数は、文字数を算出する関数です。これで合算値が何桁なのかを算出します。

その値から、C1:G1の数値の個数を算出した値を減算します。

桁数をずらす必要があるので、終点のみを絶対参照に設定しておきます。

今回の例としては、

LEN($A$8)は、「5」

COUNT(C1:$G$1)は、「5」

5-5=0なので、0文字目を抽出ということになるのですが、0文字目を抽出というのは、おかしいので、エラーが表示されるわけですね。

このように、ちょっと複雑な数式になっていますね。

帳票上の必要があるかもしれませんが、基本的には、数値を一つずつ、一つのセルごとに分割する場合には、アレコレ、アイディアが必要になりますね。