Excel。わかりにくいDSUM関数をご紹介。その１ 条件が一つ DSUM関数

Excel。わかりにくいDSUM関数をご紹介。その１ 条件が一つ　

DSUM関数

データベース関数の代表格である、DSUM関数。知っていると便利なんですが、
講義をすると、みなさんから、分かりにくいという声を聞きますので、
今回は、DSUM関数をご紹介します。

下記のようなシートがあります。

DSUM関数を使うの為に、事前に準備する必要があります。それが、

F1:F2にある。条件範囲。

これを準備しておきましょう。F2にケーキが入力されておりますが、
これを和菓子とかプリンとかに変更すると、数量合計が自動的に変わるわけですね。
なお、F1の商品区分という文字は、データのフィールド名と同じにする必要があります。

それでは、作っていきましょう。
F6をクリックして、DSUM関数のダイアログボックスを表示しましょう。

データベースには、A1:D24と入力します。
見出し行も範囲に含めるのがポイントです。
VLOOKUP関数の範囲は、通常見出し行は含めないのですが、
含めたり含めなかったりと、このあたりが混乱する一つの要因ですね。
続いて、
フィールドですが、これは、フィールド名のことです。
で、
集計したいフィールド名なので、フィールド名が入っているC1をクリックするのでもOKですが、
売上数量と入力します。

フィールドは、"売上数量"

最後の条件ですが、これが、F1:F2。
入力が終わったら、OKボタンをクリックして完成。

結果は、ケーキの売上数量は、218個となりますね。

ところで、Excel2003を使っていた人は、驚きますよね。
ダイアログボックスがわかりやすくなっていますよね。

Excel2003のDSUM関数ダイアログボックス

条件がcriteria（クライテリア）となっていましたよね。
これは、Excel2007から変わっているんですよ。

Excel。ブック間での重複をチェックする方法 IF+ISNA+MATCH関数

Excel。ブック間での重複をチェックする方法 　

IF+ISNA+MATCH関数

重複をチェックするシリーズも、いよいよブック間での重複をチェックする方法をご紹介しましょう。
企業研修で、人気のある、ブック間での重複チェック。それでは、やっていきましょう。

下記のような重複マスタというブックと照合というブックをぶつけて、重複マスタの重複確認に重複という文字を表示してみようということをやっていきます。

まずは、C2をクリックして、IF関数のダイアログボックスを表示しましょう。

論理式が長くなりますので、まずは、真の場合と偽の場合を先にいれましょう。
真の場合は、””
偽の場合は、”重複”

それでは、論理式を作っていきましょう。論理式のボックスをクリックして、
#N/Aを確認したいので、ISNA関数の登場ですね。
ISNA関数のダイアログボックスを表示しましょう。

テストの対象ボックスをクリックして、MATCH関数を作っていきます。
今度は、MATCH関数のダイアログボックスを表示しましょう。

検査値は、B2
検査範囲は、[照合.xlsx]照合!$B$2:$B$5
照合の種類は、0
となります。なお、今回は、照合.xlsxが開いている状態で関数を作っております。
これで、OKボタンをクリックしましょう。オートフィルでコピーをして完成！

これで、ブック間での重複確認も出来ますね。

Excel。シート間での重複をチェックする方法 IF+ISNA+MATCH関数

Excel。シート間での重複をチェックする方法

IF+ISNA+MATCH関数

企業研修をしていると、会社ごと、担当者ごとで様々な作り方をしている訳でして、重複チェックにもいろいろリクエストがあるわけです。その中でも、助かります！と聞くことが多い、シート間での重複をチェックする方法を今回は、ご紹介しましょう。

下記のような重複マスタというシートと照合というシートをぶつけて、重複マスタの重複確認に重複という文字を表示してみようということをやっていきます。

直接、重複マスタの重複確認に結果を表示してもいいのですが、分かりにくいと思いますので、D列にMATCHというフィールドに、途中結果をまず表示していきましょう。このような場合で照合をさせる時は、MATCH関数を使っていくといいでしょう。

それでは、重複マスタのD2をクリックして、MATCH関数のダイアログボックスを表示しましょう。

検査値は、B2
検査範囲は、照合!$B$2:$B$5
照合シートに移動して、氏名のデータを範囲選択します。そして絶対参照を設定します。
絶対参照はF4キーですね。
照合の種類は、0
これで、OKボタンをクリックして、オートフィルでコピーをします。
MATCH関数を使うと、数字が入っているセルは、重複していることになります。
重複していないセルには、#N/Aというエラーが表示されていますので、このエラーを表示しないようにして、数字が入っているセルには、重複という文字を表示するようにしていきましょう。
まずは、D列が完成。

それでは、重複確認を作っていきます。いよいよ本番ですね。
要するに

もし、#N/Aエラーだったら、空白で、そうでなかったら重複

となればいいわけですので、
まずは、C2をクリックして、IF関数のダイアログボックスを表示しましょう。

論理式が長くなりますので、まずは、真の場合と偽の場合を先にいれましょう。
真の場合は、””
偽の場合は、”重複”
それでは、論理式を作っていきましょう。論理式のボックスをクリックして、
#N/Aを確認したいので、ISNA関数の登場ですね。
ISNA関数のダイアログボックスを表示しましょう。

テストの対象ボックスをクリックして、先ほど”練習”したMATCH関数を作っていきます。
今度は、MATCH関数のダイアログボックスを表示しましょう。
作る内容は、先ほどと同じです。

検査値は、B2
検査範囲は、照合!$B$2:$B$5
照合の種類は、0
でしたね。
これで、OKボタンをクリックしましょう。オートフィルでコピーをして完成！

これで、シート間での重複確認が出来ますね。

Excel。条件付き書式で簡単に重複を発見する方法

Excel。条件付き書式で簡単に重複を発見する方法

条件付き書式

今回は、企業研修さんでお馴染みの重複チェックの方法を、ご紹介します。
関数を使うのではなく、
折角、条件付き書式で簡単に出来るようになったのですから、
早速やってみましょう。

下記のような表があります。重複している氏名に色を付けてみましょう。

B2:B24を範囲選択します。

条件付き書式のセルの強調表示ルールにある重複する値をクリックします。

重複を選択して、書式は任意で設定して、
OKボタンをクリックすると、
重複に書式が付きますね。

Excel。これは簡単! COUNTIF関数を使って重複チェックする方法 IF+COUNTIF関数

Excel。
これは簡単! COUNTIF関数を使って重複チェックする方法

IF+COUNTIF関数

今回は、企業研修さんでお馴染みの重複チェックの方法を、ご紹介します。
条件付き書式ですぐにわかりますが、ここは、昔から伝わる、
IF+COUNTIF関数で重複チェックをしてみましょう。

下記のような表があり、重複確認の列に重複だったら、重複と表示したいというのが目的です。

C2をクリックして、IF関数のダイアログボックスを表示しましょう。

論理式を作る前に、先に真の場合と偽の場合を入力しておきます。
真の場合には、”重複”
偽の場合には、””
では、論理式のボックスをクリックして、COUNTIF関数のダイアログボックスを表示しましょう。

範囲には、$B$2:$B$24　と入力します。
検索条件には、B2を入力します。
IF関数が完成しておりませんので、IF関数のダイアログボックスにもどります。
数式バーのIFという文字をクリックすると、IF関数のダイアログボックスが表示されます。

論理式を加工します。
論理式が、
COUNTIF($B$2:$B$24,B2)
となっていますので、

COUNTIF($B$2:$B$24,B2)>1

と加工します。
これは、B2～B24までの間のデータでB2と同じデータがの数が
COUNTIFによって算出されています。
重複なので、その算出された値が１より大きい場合は、重複している訳ですね。

これで、OKボタンをクリックして、オートフィルでコピーして完成ですね。

Excel。WEEKDAY関数を使って土曜日に青・日曜日に赤で塗る ユーザー定義書式＋条件付き書式＋WEEKDAY関数

Excel。WEEKDAY関数を使って
土曜日に青・日曜日に赤で塗る

ユーザー定義書式＋条件付き書式＋WEEKDAY関数

Excelで、スケジュール表などを作っているときに、土曜日には青色で、日曜日には赤色で、
月がわかっても自動的にセルに色を塗ってみるというのを以前ご紹介しましたが、
表示形式を使った場合、条件付き書式では、塗ることが出来ませんでした。

そこで、今回は、WEEKDAY関数を使用することにより、
表示形式でも、条件付き書式が使えることを確認してみましょう。

下記のような表があり、まずは、表示形式の列に、ユーザー定義書式で、
曜日を表示していきましょう。

まずは、表示形式から作っていきましょう。
B3をクリックして、
=A3と入力すると、9月1日と表示されます。
これを、ユーザー定義書式で曜日に表示形式を変更していきます。
B3をクリックして、セルの書式設定ダイアログボックスを表示しましょう。
Ctrl＋1というショートカットキーでも表示できますね。

表示形式タブのユーザー定義が選択されているのを確認して、種類に
aaa
と

aを3個入力すると、曜日に表示形式が変更されます。

OKボタンをクリックした後、オートフィルでコピーしましょう。
 
これで、条件付き書式とWEEKDAY関数を使って、
土曜日に色を塗るのでもいいのですが、WEEKDAY関数の動きを見てみましょう。

C3をクリックして、WEEKDAY関数のダイアログボックスを表示しましょう。

シリアル値はA3をクリックします。
種類には、1を入力します。
OKボタンをクリックします。オートフィルでコピーします。

これで、まずは完成。
ちょっと、脱線しますが、WEEKDAY関数の種類がわかりにくいですよね。
それぞれの用途におうじて、設定を変えることが出来ますので、表にしてみましょう。

このように1～3を選択すると戻り値が変わるわけです。
今回は1を選びましたので、日曜日が1で土曜日が7を返します。
ちなみに、種類の1は省略することもできます。
ところが…
Excel2010になったら、このWEEKDAY関数の種類が増えたんですね。
それをご紹介します。
曜日ごとに、設定することがExcel2010のWEEKDAY関数では出来るようになりました。

って、ダイアログボックスに書いていない…

さて、元に戻りまして、WEEKDAY関数を使用したら、土曜日は7という数字を返すわけです。
つまり条件付き書式で、7だったら、青色を塗る設定をしてあげればいいわけですね。
それでは、早速やっていきましょう。
今回は、B3:B17を範囲選択をして、条件付き書式を設定していきます。

条件付き書式の新しいルールをクリックします。

数式を使用して、書式設定するセルを決定を選択して、ボックスに

=WEEKDAY(A3,1)=7

と入力します。あとは、書式ボタンをクリックして、青色を設定していきます。
これで完成です。

ちょっと、大変でしたが青色を付けることができました。
日曜日は赤色なので、同じようにせってしますが、

=WEEKDAY(A3,1)=1

と7を1と変更すればOKですね。

Excel。土曜日に青・日曜日に赤で塗ってみたいんだけど…ユーザー定義書式＋TEXT関数+条件付き書式

Excel。土曜日に青・日曜日に赤で塗ってみたいんだけど…

ユーザー定義書式＋TEXT関数+条件付き書式

Excelで、スケジュール表などを作っているときに、土曜日には青色で、日曜日には赤色で、月がわかっても自動的にセルに色を塗ってみるというのを今回は、やってみましょう。

Excelは、最終的に何をしたいのか？によって、途中まで同じ結果であっても、最終的に効率の良しあしが出てきてしまうのです。
それでは、下記のような表があり、表示形式の列には、ユーザー定義書式で、TEXT関数の列には、TEXT関数を使用して、それぞれ曜日を表示してみたいと思います。

まずは、表示形式から作っていきましょう。
B3をクリックして、
=A3と入力すると、9月1日と表示されます。これを、ユーザー定義書式で曜日に表示形式をへんこうしていきます。
B3をクリックして、セルの書式設定ダイアログボックスを表示しましょう。
Ctrl＋1というショートカットキーでも表示できますね。

表示形式タブのユーザー定義が選択されているのを確認して、種類に
aaa
とaを3個入力すると、曜日に表示形式が変更されます。
OKボタンをクリックした後、オートフィルでコピーしましょう。
 
次に、TEXT関数の列を作っていきましょう。

C3をクリックして、TEXT関数のダイアログボックスを表示しましょう。

値には、A3
表示形式には、”aaa”
と入力します。OKボタンをクリックして完成です。オートフィルタで連続コピーします。

まずは、完成。このあと、いよいよ、条件付き書式を使用して、土曜日に青・日曜日に赤の色を塗るように設定していきます。
まとめて設定してきますので、B3:C17までを範囲選択します。

条件付き書式→セルの強調表示ルール→文字列を選択します。
次は、色を設定します。

次の文字列を含むセルを書式設定のボックスには、土　を入力して、
書式は、ユーザー設定の書式を選択すると、セルの書式設定ダイアログボックスが表示されます。

青色を選択して、OKボタンをクリックします。

OKボタンをクリックします。コレで完成。なんですが…変ですよね。

TEXT関数で設定したC列には、条件付き書式が反映されましたが、B列の表示形式は、色がついていませんね。表示形式は、あくまでも元のデータの表示を変化させているだけなので、Excelは”土”という文字として認識していないわけですね。
条件付き書式+Weekday関数を使用すれば、表示形式の列も色を塗ることは出来ますが、効率よく作業することを考えた場合、条件付き書式+TEXT関数のほうが、良いと思います。

条件付き書式+Weekday関数は、また別でご紹介いたします。
日曜日に赤を設定するのも、同じように設定します。
これで、完成します。

Excel。SUM/SUMIF/SUMIFSを再確認してみよう。SUM関数 SUMIF関数 SUMIFS関数

Excel。SUM/SUMIF/SUMIFSを再確認してみよう。

SUM関数　SUMIF関数　SUMIFS関数

Excelの講義で、分かりにくい！といわれる方もいる。合計関係関数のSUM関数・SUMIF関数・SUMIFS関数を今回は、まとめて再確認してみましょう。
下記のような表があります。

まずは、総合計から算出していきましょう。
総合計は、オートSUMボタンで簡単に算出できますね。
数式は、

=SUM(D2:D24)

次は、ケーキの合計を算出していきましょう。
条件が付いた合計を算出するときには、SUMIF関数を使用しますね。IFが条件でSUMが総和算なので、条件付き合計なので、SUMIFという覚え方がいいかなぁ～と。
さて、G3をクリックして、
SUMIF関数のダイアログボックスを挿入しましょう。

範囲。これがわかりにくいと、よくいわれます。この範囲は、次の検索条件が含まれている範囲を選択します。検索条件のボックスが先にあれば、わかりやすいのですがね。
なので、範囲は、A2:A24
検索条件は、ケーキと入力しましょう。セル範囲でもOKです。
合計範囲は、売上金額のD2:D24
これで、完成ですね。

そして、最後は、ケーキでローズの合計を算出してみましょう。
このように、複数条件の合計は、SUMIFS関数を使用します。IFが条件で、それが複数条件なのでSで、その合計なのでSUM。
では、SUMIFS関数のダイアログボックスを挿入しましょう。

SUMIFS関数は、SUMIF関数とボックスが異なっていて、最初に合計範囲を聞いてきます。あと、条件範囲と、SUMIF関数は範囲と何の範囲だかわからない表記から、条件範囲とわかりやすいように変わっていて、SUMIF関数も同じだと、関数初心者の方にもわかりやすいのですが…

合計対象範囲は、合計したい範囲なので、売上金額のD2:D24
条件範囲１は、商品区分のA2:A24
条件１は、ケーキと入力します。
条件範囲2は、メーカー名のB2:B24
条件2は、ローズと入力します。

これで完成ですね。