Excel。DELTA関数を使って、振分伝票入力の借方と貸方の金額チェックする技を紹介

Excel。DELTA関数を使って、
振分伝票入力の借方と貸方の金額チェックする技を紹介

DELTA関数

前回、ご紹介したDELTA関数。
この関数は、２つの数値を比べてチェックすることができる関数でした。
使い方の詳細は、前回を参照して頂くとして、

今回はこのDELTA関数を使用して、振分伝票の借方貸方の金額が合致しているのか？
というチェックで使用してみたいと思います。

この内容は、仕事でつかえるExcel講座や、関数講座などでも紹介したことがありますし、
会計担当者さんにマンツーマンで講義した時にも紹介したものです。

では、下記のような振分伝票のように作った、Excelファイルがあります。

借方金額の合計が貸方金額と合致している必要がありますね。
双方の合計を出してそれをチェックしてもいいのですが、
今回は、F3に合致しているかいないかの判定をさせたいと思います。

そこで登場するのが、前回ご紹介したDELTA関数という訳です。

それでは、F3をクリックして、関数の挿入ダイアログボックスを表示させて、
DELTA関数のダイアログボックスを表示させましょう。

数値1には、貸方金額の合計を求めたいので、SUM関数をネストしていきますので、
数値1のボックスにカーソルがあることを確認したら、名前ボックス▼をクリックして、
SUM関数のダイアログボックスを表示していきます。

SUM関数のダイアログボックスが表示されてきましたら、合計範囲が違っていますので、
数値1の範囲を修正していきましょう。

今回は、借方金額の合計を算出させたい訳なので、範囲は、C3:C6となりますね。
そして、ここがポイントになるのですが、
このSUM関数のダイアログボックスのOKボタンをクリックしてしまうと、
まだDELTA関数そのものは、作成中なので、エラーになってしまいますね。

ですので、必ず数式バーのDELTAという文字をクリックして、
DELTA関数のダイアログボックスに戻りましょう。

続いて、数値2のボックスをクリックしましょう。
数値2には、数値1の値と比べる値。すなわち、E3ですね。

そして、OKボタンをクリックすると、F3には0（ゼロ）すなわち、
合致していないということが判定されました。

しかし、合致する・しないが、０か１かで表示されてもわかりませんので、
前回同様にIF関数を使用して、
0だったら合致しない。1ならば合致するという文字を表示できるように修正してみましょう。

前回は、一度削除してIF関数から作成していきましたが、
今回は、このF3の数式をコピーして、
直接IF関数のダイアログボックスに貼り付けていきたいと思います。

まず、F3をクリックして、数式バーにカーソルを表示させて、イコールを除いた文字

DELTA(SUM(C3:C6),E3)

を範囲選択して、Ctrl+C。あるいは、コピーボタンを使ってもいいので、コピーします。

その後、F3を何もない状態にしますので、DELなどで削除しておきましょう。
F3に今度は、IF関数のダイアログボックスを表示させましょう。

表示されましたら、論理式のボックスをクリックして、Ctrl+V。
あるいは、貼り付けボタンで
DELTA(SUM(C3:C6),E3)を挿入していきます。

DELTA(SUM(C3:C6),E3)に=1を追加して、
論理式を、DELTA(SUM(C3:C6),E3)=1　とします。
真の場合は、合致
偽の場合は、合致してない
と入力をして、OKボタンをクリックしましょう。

そうすると、F3には、合致しないという文字が表示されましたね。

確かに、合致していませんね。それでは、貸方金額を、35000に変えてみましょう。
すると、今度は、合致と表示されましたね。

このように、2つの数値が同じかどうかを、チェックするのに使うとなかなか、
面白い関数ですので、

DELTA関数

を使ってみてはどうでしょうか？

Excel。数値の入力チェックする技で、DELTA関数を使ってみる。

Excel。数値の入力チェックする技で、
DELTA関数を使ってみる。

DELTA関数

仕事でつかるExcel講座や初心者の方の講座などのご質問で、
大量なデータ入力作業があるのだけど、
正しい数値が入力されているかどうかのチェックがなかなか、大変だとか、
振分伝票で、借方と貸方が合致しているのか？というチェックもしたい。

というご質問があったりします。

このように、２つの数値を比べてチェックするという関数があるので、それをご紹介しております。
その関数は、

DELTA関数。

今回ご紹介するDELTA関数は、
2つの数値を比べて異なっていたら0を合致していたら1を返してくれる関数です。

まずは、使い方の説明からしましょう。次のような表があります。

B2には、基準となる数値が、C3には、入力するセルだとして、
C3にはB2と同じ数値を入力しないといけないとします。

D3にDELTA関数を作っていきましょう。

関数挿入ボタンfxをクリックして、関数の挿入ダイアログボックスから、
DELTA関数のダイアログボックスを表示していきましょう。

数値1には、基準となる数値を入力しますので、今回はC3。
数値2には、入力した数値を入力しますので、今回は、D3。
そして、OKボタンをクリックしましょう。

D3には、0と表示されました。合致しないときは0と表示されます。

では、C3をB3と同じ数値にしてみましょう。
すると、今度は1と表示されましたね。

ただ、0と1が表示されても、さっぱりわからないので、ここはIF関数を使って、
IF+DELTA関数にアレンジしていきましょう。

1の時は、空白で、違っている時は、すなわち0の時は、入力ミスと表示するようにしてみます。

それでは、D3を削除して、IF関数のダイアログボックスを表示しましょう。

論理式から作っていきましょう。

論理式のボックスをクリックしてカーソルが表示されているのを確認して、

名前ボックス▼をクリックして、DELTA関数をクリックしましょう。

そうすると、DELTA関数のダイアログボックスが表示されてきますね。

数値1と数値2には、先ほどと同じ、C3とD3を入力します。
ここで、ポイントになるのが、このあと、OKボタンをクリックしてしまうと、
先ほど作っていたIF関数が途中のために、エラーになってしまいます。

ですので、OKボタンをクリックしないで、数式バーのIFという文字をクリックすると、

ダイアログボックスがIF関数のダイアログボックスに戻ります。
論理式には、DELTA(B3,C3)と表示されていますので、=1を追加して、
DELTA(B3,C3)=1
とします。
あとは、真の場合には、””を偽の場合には、”入力ミス”と入力してOKボタンをクリックしましょう。

すると、今度は、空白になりましたね。これは、数値が合致しているからです。

確認のためにC3を違う数値に変えてみましょう。D3に入力ミスと表示されたのが確認できます。

これが基本的な使い方ですね。
ちなみに、文字では使えませんのであしからず…

次回は、
このDELTA関数を使って振分伝票の借方と貸方が合致しているのかを
確認するときに使う方法をご紹介しようと思います。

Excel。0（ゼロ）を表示させないようにするIF関数を使った公式

Excel。0（ゼロ）を表示させないようにするIF関数を使った公式

IF関数

職業訓練や、仕事とで使えるExcel講座をはじめとする、Excelの様々な講座で、
必ず紹介することなのですが、請求書や見積書なので、単価×数量の計算式をつくると、
商品名がないところでも、数式があると、0（ゼロ）を表示してしまって、
カッコ悪いというか、出来る事なら、ゼロを表示させたくない訳ですね。

そこで、IF関数を使うことによって、0（ゼロ）を表示させることを防ぐことが出来ますので、
今回は、IF関数を使った公式ではありませんが、0（ゼロ）を表示しない、

IF関数のスタンダードスキルをご紹介しましょう。

まず、次のような計算表があるとします。

E3に数量×金額の計算式を作ってみましょう。=C3*D3ですね。当然50と表示されます。

さて、見積書や請求書の場合は、このレコードが複数あるわけでして、
オーダー件数が少ない場合は、当然数量が空白のところが出るわけですね。

またオーダーしてない訳なので、商品名すら空白でしょう。

それにもかかわらず、合計欄に0（ゼロ）が表示されているのは、いかがなものでしょうか？

これじゃ、お客様に対しても印象がよく映らないでしょう。
そこで、計算式をイチイチ消去して、オーダーが入ったら、また計算式を作って、
あるいは、コピーしての繰り返しでは、作業効率が悪すぎます。

では、早速IF関数を使って、E3の合計を修正していきましょう。
面倒かもしれませんが、関数になれない方は、一度数式を削除してから、
作り直したほうが楽だと思います。

また、手入力で、IF関数を入力するのも悪くないですが、
関数の挿入ダイアログボックスを使用した方法が、
関数が苦手な人、初心者だなぁ～と思っている方には、いいかと思いますので、

今回もダイアログボックスを使って紹介していきます。

E3をクリックして、数式を削除してから、数式バーのfxボタンをクリックして、
関数の挿入ダイアログボックスを表示しましょう。

IF関数は、だいたい、関数の分類が、最近使用した関数の中にあると思いますので、
それを使ってもいいですし、すべてに変更してもOKなので、
IF関数のダイアログボックスを表示しましょう。

論理式。これは、質問文ですね。
日頃論理式なんてコトバを使うことは滅多にないでしょうから、
質問文というイメージで置き換えたほうが、分かりやすいと思います。

今回は、数量が空白ですか？という質問でいきましょう。

ですので、

論理式は、C3=""

日本語にすると、C3は空白ですか？という意味になります。

“”（ダブルコーテーションが連続）は、空白という意味です。
空白の文字の意味ではありません。

セル内にデータは空っぽですか？というのが本当の意味ですね。

つづいて、
真の場合ですが、
これも日常会話で使いませんね。
これは、YES。という意味だと考えましょう。
つまり、C3は空白ですか？という質問に対して、

そうだよ。空白だよという事でしたら、どうするのか？

という訳ですね。

今回は、C3が空白だったら、空白にしたいわけですので、

真の場合は、””

となります。

最後に偽の場合ですが、これまた、日常会話では使いませんね。
これは、NO。という意味だと考えるといいですね。

C3は空白ですか？という質問に対して、ちがうよ。空白じゃないよ。だったらどうするの？

という訳ですね。

今回は、数値が入っている訳ですから、数量×金額をさせたいわけですので、

C3*D3となります。

あとは、OKボタンをクリックすればOKですね。
数式バーには、=IF(C3="","",C3*D3)という数式が出来ていますね。
これで、0（ゼロ）を表示させないですむようになりました。

今回のIF関数の使い方は、0（ゼロ）を表示させない場合や、
エラーを表示させない場合で使う、スタンダードな公式のようなものなので、
是非この形を覚えておくといいでしょう。

Excel。シートの保護を使って、編集されないように保護しよう。

Excel。シートの保護を使って、編集されないように保護しよう。

シートの保護

仕事で使えるExcel講座でも、必ずご紹介している、シート保護を今回はご紹介しようと思います。
このシートの保護は、VLOOKUP関数を使った請求書などを作成するノウハウの一環として、
いつもはご紹介しております。

ただ、このシートの保護。簡単ですが、
ちょっと、分かりにくい所もありますので、注意が必要です。

では、今回は、下記の表を使って、紹介していきましょう。

このような表を使いまわす場合、見出しと氏名とF列と6行目の計算式は、
特に修正することがなく、
逆に青色に網掛けしてあるC3:E5のデータだけ入力し直す場合を考えてみましょう。

青色の所以外は、入力できないで、青色の所は、
入力できればOKとするには、シートの保護をどのようにして設定していけばいいのでしょうか？

それでは、早速ご紹介していきましょう。
まず、シートの保護をこのままやってみましょう。

まず、Excel2010の場合、主なやり方は２通りあります。
最初は、ホームタブのセルの書式をクリックしてその中の保護にあるシートの保護を
クリックする方法

もう一つは、校閲タブの変更にあるシートの保護。

どちらでも、大丈夫です。
どちらとも、クリックしますと、

シートの保護ダイアログボックスが表示されてきます。

取りあえず、このままOKボタンをクリックしましょう。
そうしましたら、どこでもいいので、セルをクリックしてみると、

と表示されましたので、確認後OKボタンをクリックしましょう。
これは、シートに保護が設定されたため表示されたわけです。

また、リボンを見てみましょう。

あれれ？？ほとんどのボタンが、グレーになっていて使えませんね。

改めて、シートの保護ダイアログボックスを見てみると、
ロックされた　とか　ロックされてない　というように、ロックという言葉がありますね。
このセルのロックを解除してからシートの保護を実行すると、シートが保護されても、
解除したセルには文字が入力することが出来るようになります。

また、このダイアログボックスのチェックを付けると、
シートの保護をしてもその処理が可能になります。

それでは、今度は、ロックの解除をしてからシートの保護してみましょう。
と、その前に、シートの保護を解除しておきましょう。
シートの保護の解除方法は、

最初は、ホームタブのセルの書式をクリックしてその中の保護にあるシート保護の解除を
クリックする方法

もう一つは、校閲タブの変更にあるシート保護の解除。

をクリックしましょう。


ロックの解除方法ですがこちらも二通りありますので、両方ともご紹介していきます。
シートの上に、【サランラップのような保護シート】をかぶせるイメージで、
そのかぶせるシート状のものに、”穴”をあけておくという感じです。

保護されたくない、つまりロックを解除してシートの保護をするわけです。

保護したくないセル。今回は、C3:E5の青色のセルを範囲選択をしましょう。

ホームタブのセルの書式にある、セルのロックがONになっていますので、これをクリックして、
OFFにする方法と、

セルの書式設定ダイアログボックスを表示させて、
保護タブのロックにチェックがついていますので、このチェックを外します。

そして、OKボタンをクリックしましょう。

どちらでもOKです。

見た目、何も変わっておりませんが、先程と同様の手順でシートの保護を実行してみましょう。

C3をクリックして、別の任意の数字を入力してみましょう。今度は入力することが出来ましたね。
これで、ロックを解除したところは入力できるけど、
他のセルは修正することは出来なくなっています。

余談ですが、

シートの保護を解除するためのパスワードですが、これ、パスワードを忘れてしますと、
マイクロソフトさんでもリカバリーが出来ませんので、設定する場合は十分注意してくださいね。

今回は、平均を使っているので、もしExcelに詳しくない人が使って、誤って削除したとしても、
リカバリーはしやすいですが、もっと高度な関数や、
ネストの関数が入っていたのを削除してしまう。

なんてことが発生したら、気を悪くすること間違いなしですし、入力するところ以外は、
触れないようにしておく方が作業効率の面からみても、
シートの保護を設定しておくといいでしょう。

Excel。AVERAGE関数は平均を求める関数だけど注意が必要です。

Excel。AVERAGE関数は平均を求める関数だけど
注意が必要です。

Excel。ホームタブにある、Σ▼ボタン。
すなわち、オートSUMボタンの▼をクリックすると中に平均という文字がありますよね。
これを使うと平均が算出されるわけですが、職業訓練や、初心者さん向け講座の時に、
注意してねと申し上げていることがありますので、今回は、それを紹介しましょう。

今回は、注意点として２つあげてみたいと思います。
その前に、平均とは、どう計算するのかというと、全体のデータを足して、
そのデータの件数分で割ると算出されますね。

一応、念のため確認しました。

さて、下記のような表があります。

まず、オートSUMボタンを使って、7行目の合計を算出しましょう。
そして、仮に３月の大崎店が改装で売上高が0（ゼロ）だったとして、
91を0に修正しておきましょう。

そうすると、表は、このようになります。これで、準備終了。

いよいよ、平均を求めていきましょう。C8をクリックして、
オートSUMボタンの▼をクリックして、平均をクリックしましょう。

そうすると、C8に、数式が登場しますね。
=AVERAGE(C4:C7)と表示されていますが、これでいいのでしょうか？

注意の一つ目はココです。
つまり、このような表計算の時にだいたい平均は、合計のとなりにあることが多いと思われます。
合計ときのように、オートSUMボタンをクリックして、
範囲を確認しても大体、そのままでEnterキーを押せば、合計値が算出されるのですが、

平均は、そのままEnterキーを押してしまうと、余計なデータ。

今回は合計値まで含めて、平均を算出してしまいます。
引数、（）の中の範囲を確認するとC4:C7で合計値が含まれていますね。

ですので、平均の時には、必ず範囲を確認して、違っていたら、
改めて範囲選択をし直して、Enterキーで確定するようにしましょう。

Excelのベテランさんからすれば、当たり前だろう…と思われるかもしれませんが、
初心者の方は、結構このトラップに引っかかる人が多いんですね。

では、範囲選択をし直して、算出してみましょう。
なお、範囲選択をするときには、
必ずセルの中央で白い十字のマウスカーソルにして範囲選択をするようにしましょう。

よくやってしまうのが、線の上をクリックして、カラーリファレンス（青い線）を移動させてしまって、
うまく範囲選択が出来ないという方も多く見受けますので、注意してください。

C8に算出された平均値をオートフィルを使って、E8まで連続コピーをしておきましょう。

さて、E8ですか、この数値でOKでしょうか？
大崎店の売上は、改装のため０円でしたね。
品川と五反田の数値からみて、平均値が低くなっていると思いませんか？

気づいた方も多いと思いますが、
実は、大崎の0も足して、データの件数の３で割ってしまっているんです。

要するに、０と空白は別という事なんですね。

これが注意点のその２。
ゼロと空白&文字は意味が違うという事です。
今回様なケースの場合は、セルを空白あるいは、”―”などを使用するといいでしょう。
では、今回は、０を”―”に置き換えてみましょう。

こうすることで、数値も変わってきますので、ただ算出するのではなく、
やりたいこととマッチしているのかを考える必要がありますね。

Excel。HLOOKUPを使うと判定分岐は楽にできますね。

Excel。HLOOKUPを使うと判定分岐は楽にできますね。

HLOOKUP

以前、紹介しました、判定分岐が多い場合は、IF+IF関数のネストよりも、
Choose関数を使うのも一つの方法ですよ。

とご紹介しましたが、判定表を作っておくことが出来るならば、

IF+IF関数やChoose関数を使うよりも、HLOOKUP関数を使うほうが、分かりやすくて、
便利なので、今回は、HLOOKUP関数を使って判定分岐を行ってみようと思います。

今回のHLOOKUP関数。VLOOKUP関数の陰に隠れて、日の目を見ませんが、
MOS Excel2010のExpert試験では、試験範囲になっていますし、
覚えておくと色々とアイディアいかんで使えますので、覚えておきましょう。

さて、今回は、仕事で使えるExcel講座で話したことがある内容を元にして、ご紹介していきます。

下記の表があります。

F2:F10までの評価には、
300点なら満点。
200～299点なら、良。
100～199点なら、可。
0～99点なら、不可。
という風に判断分岐をしたいわけです。
通常ならIF+IF+IF関数か、Choose関数を使うのですが、
今回はHLOOKUP関数を使用してやってみようという訳です。

その為に、12行目と13行目に判定表を作っておきます。

では、早速作っていきましょう。
F2をクリックして、関数挿入ダイアログボックスから、
HLOOKUP関数ダイアログボックスを表示していきましょう。

まずは、検索値。VLOOKUP関数の説明の時もいいましたが、
これは、コンビニのレジで考えると、商品のバーコードに当たります。

ですから、山田さんの合計点は？ということになりますので、
検索値は、E2。

つづいて、範囲。これは、判定表になりますので、範囲は、A12:E13となりますが、
このHLOOKUP関数で算出したセルをオートフィルを使って、数式の連続コピーをすると、
当然、セル番地がずれてしまうので、絶対参照を設定する必要があります。

範囲は、$A$12:$E$1

行番号ですが、これは、先程の範囲の中で、何行目のデータをピックアップするのか？
ということになりますので、ここは2行目の評価を検索しますので、

行番号は、2。

最後の検索方法ですが、VLOOKUP関数だと、
だいたいここは、falseあるいは、0（ゼロ）を入力すると思います。

falseは、完全一致。

すなわち、検索値が範囲のデータの中でビンゴのものしか、検索しないわけです。
ここが、今回のポイントでして、0～99点という幅があるわけですね。
ということは、完全一致ではマズいわけですね。

ですので、ここをTRUEにすることで、近似値を含めて検索することが出来るわけです。
よって、
検索方法は、TRUE。

これで、OKボタンをクリックしましょう。
F2には、良と表示されましたね。判定表で確認しても大丈夫ですね。
この数式をオートフィルを使って、数式を連続コピーしてみましょう。

関数からみても、ネストや複雑な事をしなくても簡単に判別することが出来ますので、
もっと、条件が多岐にわたってしまう時などには、判定表が作れるようでしたら、
HLOOKUP関数を使うという方法もある。

ということを覚えておくといいでしょう。