E11

YouTubeで開く

Excel基礎
IF関数 / SUMIF(S)関数 / COUNTIF(S)関数

17:35 不等号の向きが間違っていました。後に修正しております。

▼準備（完成）ファイル▼

ダウンロード

こちらではExcelの計算の仕組みや注意点、対策について解説しています。

・計算結果の確認

まず最初に、次の計算結果を確認します。

これはB列の値からC列の値を引いた答えをD列に数式により結果を表示させています。
画像ではすべてが「0.1」と表示されています。

では、上記の内容を踏まえ、「引いた答えが0.1の場合に○」という条件式を書いてみます。

そうすると、ところどころ「○」ではない箇所が見つかります。
ここで多くの人は「え？」となります。

ただ、これはExcelの不具合ではなく、コンピュータで計算する上でどうしようもない問題になります。
最初の引き算の答え「0.1」の表示桁数を増やしてみます。

ところどころ、「0.1」ではなくなってしまっています。

それ以外でも、次の場合も正しく計算できません。

・Excelの計算の仕組み

まずコンピュータがどのように数字を扱っているのかについて確認します。

→コンピュータの数字

通常、私たちが生活で目にする数字は0から9まで数えたら桁が上がり10,11,12,13となる10進数と呼ばれているものになります。
0から9の10種類の数字で成り立っているため10進数と呼ばれます。

しかし、コンピュータでは10進数でデータを扱うことができません。

基本的にコンピュータは電気で動いています。
電気の流れから取得できる情報は「電圧がある値よりも大きいのか小さいのか」といった情報になります。
そのため、送られてくる電気から取得できる情報は1回あたり2種類になります。

これを「0~0.9Vを0、1~1.9Vを1、…」のように10種類にすれば10進数でできるように感じますが、電圧は安定しません。
そのため、多少の誤差を考慮できるように、ある値（閾値）よりも大きいのか小さいのかで表現されています。

2種類の情報しかないため、10進数では表現できず2進数で数字を表現することになります。
2進数は0と1の2種類の数字で成り立っています。
そのため、0から1まで数えたら桁が上がり10,11,100,101,110となっていきます。

2進数から10進数を求める方法は2のn乗で掛けて計算します。
例えば、2進数「1001」の場合は、「1×2^3 + 0×2^2 + 0×2^1 + 1×2^0」となり10進数「9」になります。

2進数では小数も扱うことができます。
例えば、2進数「10.1」の場合は、「1×2^1 + 0×2^0 + 1×2^-1」となり10進数「2.5」になります。

つまり2進数で表現できる数は必ず2のn乗の合計になります。
そのため、10進数「0.1」など2進数では正しく表現ができない数が存在します。

10進数「0.1」を2進数で表現すると「0.000110011001100…」という感じに循環小数になります。
これは10進数「10」を3で割り切れないのと似ています。

このように2進数では10進数よりも表現できる数の種類が少ないため、誤差が生じます。

→Excelの数字

ここまでの内容を踏まえた上で「でも、Excelで0.1と計算できているのもあるじゃん」と思われた方もいるかと思います。

これは、ExcelがIEEE754の仕様に基づいて作成されているためです。
IEEE754というのは、IEEE（米国電気電子学会）という学会が決めたルールになります。
→コンピュータを全世界で連携するためにはルールを共通化する必要があります。

その仕様上、10進数で表現できる精度が15桁になるため、15桁より大きい数字は切り捨てられてしまいます。

そのため、上記の計算結果をさらに細かく書くと以下のようになります。
※Excelでは表現することができないため文字列で直接入力しています。

実際は0.1ではないのですが、15桁で切り捨てられた結果0.1になったというのが答えになります。

・小数点以下の計算をする上での注意点と対策

今までの内容を考慮すると、「小数点以下を含む計算の場合は、誤差が生じる可能性がある」ということを考慮する必要があります。
では、どのように対策すればいいのか3通りの方法を解説します。

→方法①

「ROUND関数を用いて指定した桁数で四捨五入する」という方法があります。

ROUND関数の使い方は以下のようになります。

=ROUND(数値,桁数)

桁数に小数点の位置を指定します。
桁数が「1」の場合は小数点第一位まで表現できるように四捨五入、
桁数が「2」の場合は小数点第二位まで表現できるように四捨五入、
桁数が「0」の場合は一の位まで表現できるように四捨五入、
桁数が「-1」の場合は十の位まで表現できるように四捨五入、
というように表現できます。

計算結果にROUND関数を使うことで以下のように正しく判定することができます。

→方法②

「元の値を10のn乗して整数にして計算する」という方法があります。

この方法を使った場合は以下のようになります。
この場合は、数字に対して単位などを書いておくとより分かりやすくなるかと思います。

→方法③

「表示桁数で計算」という方法があります。

この方法では、先ほどのIF文のように直接0.1と比較する場合には使うことができません。
ただ以下のように計算結果を表示させる場合には活用することができます。

これは、見た目上は0.1だけど実際は異なるものになります。
そのため、この値と0.1と比較すると以下のようになってしまいます。

これに対し、以下の設定を行います。

[ファイル]→[オプション]→[詳細設定]→[次のブックを計算するとき]→[表示桁数で計算する]

この時に「データの正確さが失われます。元に戻すことはできません。」と表示されます。
これは「実際に見た目で確認ができる数字を正として計算する」という設定です。
そのため、正確さが場合によっては失われるので、そのような警告になります。

上記の設定を行うと以下のように反映されます。

この設定はブック全体に反映されます。
そのため、最終手段として使用を考えてみるといいかと思います。
※私的には推奨しません。

というような感じで以上になります。
Excelの奥は深いですね…

色んな日付を求める方法について解説していきます。
給料日の算出や年齢の算出などに役立ちます。

・はじめに

はじめに、日付はどのようにExcelで管理されているのかを理解する必要があります。
日付をセルに入力すると、同時に表示形式が”日付”や”ユーザー定義”などに変わるかと思います。

ちなみに、”2022/1/1(土)”などと直接入力されているのを見たことがありますが、編集時に曜日まで編集が出来てしまうのであれば、日付として認識されていません。
これでは日付の計算ができないため、修正する必要があります。
もしセル内に曜日も表示したい場合は、そのような表示形式で”yyyy/m/d(aaa)”などを設定する必要があります。
表示形式の設定方法についてはこちらを参考にご確認お願いします。

日付で管理されている場合は、表示形式を”標準”などにすることで、日付の実体であるシリアル値を表示させることができます。

シリアル値は「ある日を基準にした通し番号」になります。
Excelでは、基本1900年1月1日を「1」として数えられています。

ちなみに時間は、24分の1で計算します。
つまり、”44562″が2022年1月1日の場合、”44562.25″は2022年1月1日の6:00を表していることになります。

・N日後やN日前を求める方法

日付がシリアル値で管理されていると解説しました。
日付の実体が数字であることから、通常の計算式が使えることが分かります。

そのため、1日後や1日前を求める場合は、足し算と引き算で求めることができます。

ちなみに、今日の日付を基準に計算したい時は、今日の日付のシリアル値を返すTODAY関数が使えます。
※「返す」という表現は関数を実行した結果を受け取ることを言います。

明日の日付を求めたい時は、「=TODAY()+1」で求めることができます。

・日付の差を求める方法（期限や年齢など）

単純な日付の差を求める場合は、引き算で求めることができます。
この時、正しく表示されない場合は、表示形式が影響している可能性があります。
以下の例では、黄色のセルの表示形式は”標準”にしています。

上記の方法は、日にちの場合には便利ですが、月や年で求める際には計算が複雑になってしまいます。

そんな時に使えるのがDATEDIF関数になります。
※DATEDIF関数は入力候補に表示されない特殊な関数になります。直接入力することで使えます。

使い方は、「=DATEDIF(開始日,終了日,単位)」になります。
便利な単位のみ紹介しますと以下になります。

※DATEDIF関数は特殊な関数で、いくつか注意する点があります。
　詳しくはこちらの公式サイトをご確認ください。

年齢を求める場合に関しては以下のように求めることができます。

他に、DATEDIF関数を使わずに年を求める方法も紹介します。

日付の年月日は、YEAR関数、MONTH関数、DAY関数で抽出することができます。
つまり、YEAR関数で年のみを抽出して引き算するという方法です。
ただ、単純な引き算ですと日にちなどが考慮されていないので注意する必要があります。

・特定の日付を求める方法（翌月５日など）

年月日をそれぞれ設定してシリアル値に変換するDATE関数を使います。
使い方は、「=DATE(年,月,日)」です。

基準日の翌月の5日を求める場合は以下のようになります。
※年月を別々で設定し、月に関してのみ+1して計算しています。

ちなみに、このDATE関数の月に「13」や「-1」など通常では存在しない値を設定したとしても、正しく計算されます。
日に関しても同様で大きすぎた分や小さすぎた分は年月に反映されます。
→つまり翌年は月に+12しても求めることができます。

・月末を求める方法

月末を求めるにはEOMONTH関数を使います。
使い方は、「=EOMONTH(開始日,月)」です。

ちなみに、先ほど解説したDATE関数でも求めることができます。
※日の「0」は1日より1日前として計算されます。

・N日後やN日前の営業日を求める方法

営業日（週末や休日を除いた日付）を求めるには、WORKDAY関数やWORKDAY.INTL関数を使います。
WORKDAY関数の使い方は「=WORKDAY(開始日,日数,[祭日])」です。
こちらの関数は週末が土日で計算されます。祭日（休日）を設定しない場合は省略することができます。
WORKDAY.INTL関数の使い方は「=WORKDAY.INTL(開始日,日数,[週末],[祭日])」です。
こちらの関数はWORKDAY関数に加え、週末を自由に設定することができます。
週末の設定値は以下になります。
※省略時は土日として設定されます。

WORKDAY.INTL関数の方がWORKDAY関数に比べ汎用性があるため、こちらでは、WORKDAY.INTL関数で解説していきます。

例えば土・日を週末とし、土・日・休日を除いた3日後を求める場合、以下のようになります。
休日に関しては、引数の[祭日]に”L5:L9″と休日一覧の範囲を指定します。

次に、同じ条件で3日前を求めていきます。
引数の[日数]を負の値にすることで簡単に求めることができます。

次に週末を金・土・日にした場合の3日前を求めていきます。
週末の候補に金・土・日がないため、”0000111″と記述して求めていきます。

・特定の日付の直近の営業日を求める方法（翌月５日以降の直近の営業日など）

給料日などを求める際に、このような計算が必要になってくるかと思います。
指定日の直近の営業日を求めるにはDATE関数とWORKDAY(WORKDAY.INTL)関数を組み合わせることで実現できます。

まず、土・日・休日に含まれる翌月の7日（2023/1/7）を求めます。

この求めた日付を元に、直近の営業日を求めます。
営業日を求める際に、WORKDAY(WORKDAY.INTL)関数を使います。
ただ、WORKDAY(WORKDAY.INTL)関数では0日後や0日前を指定することができません。
そのため、次の営業日を求める場合は、1日前から1日後を求めます。

先ほどのDATE関数の引数の[日]を”6″にして、DATE関数をそのままWORKDAY(WORKDAY.INTL)関数の引数の[開始日]に設定します。
そうすると、以下のように求めることができます。

では、前の営業を求める場合についても確認していきます。
先ほどの逆で求めることができるので、1日後から1日前というようにしていきます。

基準日が今日で来月の給料日（例えば、25日、土・日・休日の場合は、次の営業日）を求める場合は、以下のようになります。

以上が、色んな日付の計算方法になります。