Excel。カレンダー土日祝日がわかるようにセルに色をつけてみよう MATCH関数+条件付き書式

Excel。カレンダー土日祝日がわかるように
セルに色をつけてみよう

MATCH関数+条件付き書式

Excel。カレンダーシリーズ。今回は、前回の祝日だったら、
オレンジ色でセルを塗りつぶすのにプラスして、
土曜日は青、日曜日は赤を塗りつぶしてみましょう。

前回も、書きましたが、この祝日が厄介でして、
昔は、10月10日は体育の日とか、固定だったのですが、
近年はラッキーマンデーの登場に伴い、不定日になってしまったので、
アレコレ考えないといけなくなった訳です。

それでは、カレンダーを作ります。
作成に関しては、以前ご紹介しておりますので、そちらで確認していただくとして、
まずは、土曜日。そして日曜日と設定していきます。

ちなみに、B列の曜日には、TEXT関数が設定されております。
A5には、

=TEXT(A5,"aaa")　

が設定されています。
最初に、条件付き書式を設定しますので、カレンダーのA5:B35までを範囲選択をして、
条件付き書式の新しいルールをクリックしましょう。

新しいルールの中の、【数式を使用して、書式設定するセルを決定】を選択して、
ルールの内容を編集してくださいのボックスに

=$B5="土"

と入力します。そして、書式ボタンをクリックして、青色で塗りつぶしの設定をしましょう。

つづけて、日曜日の設定をします。やり方は同じですね。
新しいルールの中の、【数式を使用して、書式設定するセルを決定】を選択して、
ルールの内容を編集してくださいのボックスに

=$B5="日"

と入力します。そして、書式ボタンをクリックして、赤色で塗りつぶしの設定をしましょう。

最後は、祝日の設定を行いましょう。
それでは、準備として、下記のような祝日一覧を作成しておきましょう。

条件付き書式を設定しますので、カレンダーのA5:B35までを範囲選択をして、
条件付き書式の新しいルールを選択します。

新しいルールの中の、【数式を使用して、書式設定するセルを決定】を選択して、
ルールの内容を編集してくださいのボックスに

=MATCH($A5,$E$5:$E$21,0)>0

と入力します。
これは、A5の日にちが、E5:E21までにあったら、その数値を返します。
その数値がゼロでなければ、祝日一覧に合致する日があるということになります。
こうすることによって、

祝日の判定

が可能になりますね。

あとは、書式ボタンをクリックして、書式を設定しましょう。

これで、OKボタンをクリックして完成です。

Excel。カレンダー祝日がわかるようにセルに色をつけてみよう MATCH関数+条件付き書式

Excel。カレンダー祝日がわかるようにセルに色をつけてみよう

MATCH関数+条件付き書式

仕事でつかる、Excel。カレンダーシリーズ。

今回は、祝日だったら、オレンジ色でセルを塗りつぶして、祝日だとわかるようにしてみましょう。

前回も、書きましたが、この祝日が厄介でして、
昔は、10月10日は体育の日とか、固定だったのですが、
近年はラッキーマンデーの登場に伴い、不定日になってしまったので、
アレコレ考えないといけなくなった訳です。

それでは、やっていきましょう。まず、準備として、下記のような祝日一覧を作成しておきましょう。

次に、カレンダーを作ります。
作成に関しては、以前ご紹介しておりますので、そちらで確認していただくとして、
下記のカレンダーがあります。

9月16日は敬老の日なので、セルを塗りつぶせればOKというわけです。
条件付き書式を設定しますので、カレンダーのA5:B35までを範囲選択をして、
条件付き書式の新しいルールを選択します。

新しいルールの中の、【数式を使用して、書式設定するセルを決定】を選択して、
ルールの内容を編集してくださいのボックスに

=MATCH($A5,$E$5:$E$21,0)>0

と入力します。
これは、A5の日にちが、E5:E21までにあったら、その数値を返します。
その数値がゼロでなければ、祝日一覧に合致する日があるということになります。
こうすることによって、祝日の判定が可能になりますね。

あとは、書式ボタンをクリックして、書式を設定しましょう。

これで、OKボタンをクリックして完成です。

Excel。祝日がわかるようにする自動表示するカレンダー

Excel。祝日がわかるようにする自動表示するカレンダー

IFERROR＋IF＋MATCH関数

Excel。毎月のスケジュール管理などで、良く作るカレンダー。
今回は、祝日がわかるように、祭日の列に”祝”という字を表示させるようにしてみましょう。
実は、この祝日が曲者。
昔は、10月10日は体育の日とか、固定だったのですが、
近年はラッキーマンデーの登場に伴い、不定日になってしまったので、
アレコレ考えないといけなくなった訳です。

まず、前回まで作成していたカレンダーのC列に祭日という列を作りましょう。

そして、祝日を判定するために、祝日一覧を作成するのがポイントです。
下記のような表を作成します。

今回やりたいのは、
日付がこの祝日一覧にあったら、祝。
そうでなかったら空白ということをやりたい訳です。
こういう時は、MATCH関数の登場です。
MATCH関数は、検索して見つかれば、
何番目にそのデータがあるのかという数字を返してくれます。
すなわち、見つかれば、1以上の数字を返してくれるわけですね。

それでは、早速作成していきましょう。
まず、データが合致しないとエラーを返してしまうので、
IFERROR関数のダイアログボックスを表示しましょう。

先に、エラーの場合の値を入れておきましょう。””（空白）ですね。
それでは、値を作っていきましょう。IF関数のダイアログボックスを表示していきましょう。

これも先に、真の場合と偽の場合を先に設定しておきましょう。
真の場合には、”祝”
偽の場合には、”” （空白）
を入力しておきます。
そして、論理式には、MATCH関数を挿入していきますので、
MATCH関数のダイアログボックスを表示しましょう。

検索値は、A5を入力します。
検査範囲は、$F$5:$F$21。
照合の種類は、完全一致なので、0を入力します。
数式は、
=IFERROR(IF(MATCH(A5,$F$5:$F$21,0)>0,"祝",""),"")

あとは、OKボタンをクリックして、オートフィルで連続コピーしましょう。

 これで、完成ですね。次回は、土日祝に色を付けてみましょう。

Excel。月末28～31日に対応して日付を自動表示するカレンダー その2

Excel。
月末28～31日に対応して日付を自動表示するカレンダー　
その2

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

Excel。毎月のスケジュール管理などで、良く作るカレンダー類。
そこで、いつも面倒なのが、
月末が28日なのか29日なのか30日なのか31日なのかによって、
数式やら文字やらを削除する。
あるいは、文字を白で塗るなんてことも…

そこで、今回は、自動的に判断して、
翌月になったら空白にするカレンダーを作ってみましょう。
の続きの第2弾。
日付が自動的に10月1日と表示されずに、
空白になりましたと思いきや、B2の9月を2月に変更してみると、
なんと#VALUE!エラーが表示されちゃいました。

これは、A33が空白になってしまったことが原因です。
ですので、A34の数式を修正していきましょう。

IF関数を使ってもOKですが、ここは、IFERROR関数を使ってみましょう。
まずA34を削除して、IFERROR関数のダイアログボックスを表示しましょう。

値には、A33の数式をコピーしてもOKですが、
IF(MONTH(A32+1)>MONTH(A32),"",A32+1)
と入力します。
エラーの場合の値は、””　。
空白をいれて、OKボタンをクリックしましょう。
この数式をA35まで、オートフィルハンドルを使って、コピーしましょう。

これで、日付と曜日は完成しました。
次回は、C列に祝日だったら、祝という字を表示させるようにしてみましょう。

Excel。月末28～31日に対応して日付を自動表示するカレンダー その１

Excel。
月末28～31日に対応して日付を自動表示するカレンダー　
その１

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

Excel。毎月のスケジュール管理などで、良く作るカレンダー類。
そこで、いつも面倒なのが、
月末が28日なのか29日なのか30日なのか31日なのかによって、
数式やら文字やらを削除する。あるいは、文字を白で塗るなんてことも…

そこで、今回は、自動的に判断して、翌月になったら空白にするカレンダーを作ってみましょう。

A2に2013を　B2に9を入力します。このA2とB2にそれぞれ、年と月を入力したら、
自動的に日付に曜日も変わるカレンダーをつくっていきます。
まずは、A2とB2の表示形式を変更していきます。

2013と入力したA2をクリックして、
セルの書式設定ダイアログボックスを表示して表示形式のユーザー定義を選択します。

種類のボックスに、G/標準と表示されていますので、その後ろにカーソルをいれて、
”年”と入力します。
そしてOKボタンをクリックすると、
2013年とA2が表示されます。普通に2013年と入力してしまうと、
文字になってしまい、数字として使用することができませんので、
ユーザー定義書式というテクニックをよく使いますので、覚えておくといいかと。

同じように、B2をクリックして、9月と表示するように、
G/標準の後ろに”月”を入力して、9月と表示されることを確認しましょう。

続いて、日付を作っていきます。
A4に日付。B4に曜日と入力して、A5にDATE関数で日付を作成していきましょう。
A5をクリックして、DATE関数のダイアログボックスを表示します。

年には、A2を入力します。
月には、B2を入力します。
日には、1を入力します。
OKボタンをクリックすると、9月1日と表示されます。
最初は当然1日なので、日には、1を入力しております。
年と月はセル番地を参照していますので、そのセルを替えることによって、
自動的にその年月の1日を作ることができます。

つづいて、A6をクリックして、=A5+1と入力します。
それによって、A5の翌日、つまり9月2日を作ることが出来ます。
オートフィルを使って、A35までコピーしましょう。

A35には、10月1日と表示されますが、
これは後程、表示されないようにアレンジしますので、そのままにしておきます。

次は、曜日を作っていきます。これは、以前紹介しました、TEXT関数を使います。
B5をクリックして、TEXT関数のダイアログボックスを表示します。

値には、A5を入力します。
表示形式は、”aaa”と入力します。
OKボタンをクリックすると、曜日が表示されますので、
オートフィルで連続コピーをして、B35まで算出しましょう。
このように、なりますね。

さて、本題はこれから、このままだと、10月1日が表示されてしまっていますね。
別にこれでも構わないのですが、やはり、9月は9月にしたい訳でして。

そこで、どうしたらいいのかを考えてみましょう。
問題になるのが28日以降ですね。
28日までは、上のセル+1でいいのですが、29～31日は月によって変わるわけですね。
なので、対象となるセルは、A33:A35までとなります。

さて、どのように判断させたらいいでしょうか？
注目すべきは月が替わるかどうか、すなわち、月が同じか、それより大きいのか？
を判断させればいいというのがわかります。

なので、A33は、
「もし、A33の月は、A32の月と比べて、大きければ、空白。そうでなければ、＋１」する数式を
作ってあげればいいわけですね。

それでは、29日が入っている、A33をクリックして、数式を削除して、
IF関数のダイアログボックスを表示しましょう。

論理式は、月を比べる数式になりますので、MONTH関数をネストしていきます。
それでは、論理式のボックスをクリックして、MONTH関数のダイアログボックスを表示しましょう。

シリアル値は、A32+1と入力します。
これでまず、A32の翌日の月を算出することができます。
IF関数にいったん戻りますので、数式バーのIFをクリックして、
IF関数のダイアログボックスに戻りましょう。

論理式は、=IF(MONTH(A32+1)となっていますので、
＞を入力した後に、再度、MONTH関数のダイアログボックスを挿入します。

今度は、A32とシリアル値に入力して、IF関数のダイアログボックスに戻りましょう。
これで、論理式は完成です。

論理式は、MONTH(A32+1>MONTH(A32)
真の場合は、””
偽の場合は、A32+1
と入力してOKボタンをクリックしましょう。
この数式をオートフィルで、A35まで連続コピーしてみましょう。

そうすると、10月1日が表示されずに、空白になりましたね。
これで、翌月になった場合は、空白にすることが出来たと思いきや、
B2の9月を2月に変更してみましょう…。
残念ながら…。

ここから先は、その２で書きたいと思います。

Excel。アラビア数字をローマ数字に変換する方法。ROMAN関数

Excel。アラビア数字をローマ数字に変換する方法。

ROMAN関数

講義の合間に、使用頻度が少ないだろうけど…
という、面白い関数を紹介しております。
その中から、今回は、アラビア数字をローマ数字に変換する。
ROMAN関数をご紹介しましょう。

ROMAN関数のダイアログボックスを表示しましょう。

数値には、セル番地、あるいは、数字を入力します。
書式は、基本的に0か省略します。
この書式には、0～4までに数字が入ります。
なんでも、ローマ数字には、正式な書式から、簡略化された書式まであり、
正式な書式が0で、略式が4となっております。

下記には、アラビア数字をローマ数字に変換した一覧表を作成しております。

ご覧のように書式によって、3999のローマ数字が変わっておりますね。
あと、このROMAN関数は、3999までしか、変換できません。
ですので、4000はエラーが表示されております。
4000以上は、自力でということでしょうか…
それと、ローマ数字は、0（ゼロ）という概念がありませんので、
空欄になります。
当然、負数もありません。
試しに、-1を変換すると、
4000と同じように、
#VALUE!エラーが表示されます。

なんか、中途半端で、使えるんだか、使えないんだか…

Excel。オートフィルでABC…Zまで連続コピーする方法。CHAR+CODE関数

Excel。オートフィルでABC…Zまで連続コピーする方法。

CHAR+CODE関数

講義で使用していた問題集に、A～Zという項目名がある表を作成する問題があって、
生徒さんから、A～Zって、オートフィルタ出来ないんですね。
といわれて、「ユーザー設定リスト」に登録してないと、
出来ないですよとお伝えしたのですが、
よく考えれば、関数でいけるんじゃない？
ということで、今回は、その時ご紹介した、
オートフィルでA～Zまで連続コピーで作れる方法をご紹介します。

ただ…先にエクスキューズしちゃいますけど、
CHAR+CODE関数のネストを作りますが、
多分、自力でA～Zまで入力したほうが、早いんじゃないかなぁ～と。

ということで、やり方をご紹介しましょう。A3にAと入力しておきます。

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

このCHAR関数は、文字に割り当てられているコード番号を入力すると、その文字を返す関数です。ちなみに、65と入力すると、Aを返します。

それでは、65を入力しないで、CODE関数をネストしていきます。
CODE関数のダイアログボックスを表示しましょう。

文字列にA3をクリックします。
このCODE関数は、文字列に割り当てられている数値コードを調べることが出来ます。
A3をクリックすると、65を返します。
そうしたら、CHAR関数に戻ります。
数式バーのCHAR関数をクリックすると、ダイアログボックスがCHAR関数に戻りますね。

数値ボックスにCODE(A3)とありますので、その後ろに+1と入力します。
+1する。Aの次の文字を表示させる。つまりBを表示することが出来るわけですね。

あとは、オートフィルで連続コピーすればいいわけですね。

で、ここで、A3を”A”じゃなくて、”あ”にすれば、五十音順が出来るんじゃないかと思うでしょう。
残念ながら、うまくいかないんですね。
実際にやってみましょう。
A3を”A”を”あ”に変えてみます。
”あ”の次は、なんと、”ぃ”なんですね。小さい”い”。ということで、

残念…。

Excel。DSUM関数。その４ 複合条件は複数列×複数行です。

Excel。DSUM関数。その４ 複合条件は複数列×複数行です。

DSUM関数

データベース関数の代表格である、DSUM関数。
今回は、その第4弾。複数条件で算出する方法をご紹介しましょう。
ただ、これが、思うような結果にならないことがありますので、
ちょっと、知っておく必要があります。
準備は、条件を複数列と複数列にするだけでOKです。

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

今回も、DSUM関数を使うの為に、事前に準備する必要があります。
それが、
F1:G3にある。条件範囲。これを準備しておきましょう。
F2にケーキがG2にローズ。F3に和菓子がG3に京都本舗が、入力されております。

ここが、ポイントなのですが、商品区分が、ケーキと和菓子。
メーカー名がローズと京都本舗が抽出条件なのではなく、
商品区分がケーキでメーカー名がローズ。
または、商品区分が和菓子でメーカー名が京都本舗という意味の抽出条件なのです。
ですので、

商品区分を逆にしたら、条件がことなってしまいます。
ちなみに、結果は０になります。
これは、商品区分が和菓子でメーカー名がローズ。
または、商品区分がケーキでメーカー名が京都本舗という意味の
抽出条件になってしまいます。
ですので、注意が必要です。

また、F1とG1の商品区分とメーカー名いう文字は、
データのフィールド名と合致する必要があります。
空白列があってはダメです。

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

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

フィールドは、"売上数量"
最後の条件ですが、これが、F1:G3。
入力が終わったら、OKボタンをクリックして完成。

結果は、102個となりますね。