第4回 - 2018/10/19

関数を利用した処理

数値処理

統計処理については、別のときに説明をします。以下に、代表的なものを紹介します。

• INT(値), TRUNC(数値, 桁数)：数値を指定した桁で切り捨てる
• ROUND(値, 丸める桁), ROUNDUP(...), ROUNDDOWN(...)：四捨五入、切り捨て、切り上げ
• PI()：円周率
• MOD(数値, 除数)：割り算の余り
• POWER(数値, 指数)：累乗（数値を指数の数だけ掛け合わせたもの）
• RAND(), RANDBETWEEN(開始値, 終了値)：乱数。引数のないものは0〜1の数値を生成。
• SQRT(数値)：平方根
• SUM(範囲1, 範囲2, ...)：合計
• SUMPRODUCT(範囲1, 範囲2)：範囲1の1番目のセル×範囲2の1番目のセル＋範囲1の2番目のセル×範囲2の2番目のセル＋範囲1の3番目のセル×範囲2の3番目のセル＋・・・＋範囲1の最後のセル×範囲2の最後のセル（利用例：単価と個数の合計を計算）

文字列を処理する関数

• LEFT(セル, 文字数)：セルの中の文字列について、左から指定した文字数を取り出す
• RIGHT(セル, 文字数)：セルの中の文字列について、右から指定した文字数を取り出す
• MID(セル, 開始位置, 文字数)：セルの中の文字列について、指定した位置から文字数分の文字を取り出す
• LEN(セル)：セルにある文字列の文字数
• SEARCH(検索文字列, 検索対象[, 開始位置]), FIND(...)：検索対象から検索文字列を探し、何文字目にあるかを得る。FINDは大文字小文字を区別し、SEARCHはしない
• REPLACE(文字列, 開始位置, 文字数, 置換文字列)：文字を位置と長さを指定して置き換える
• SUBSTITUE(文字列, 検索文字列, 置換文字列, 置換対象)：検索した文字列を別の文字列に置き換える

日付を処理する関数

日付時刻データについて

• 1900年1月1日 0:00を「1」として、1日を「1.0」としてカウントした「数値」。従って、1時間＝1.0÷24=0.0416...
• 数値なので、加算、減算ができる
• 基準日に何種類かある
• 時差、あるいはタイムゾーンは考慮されていない
• 入力時に年を2桁で入力すると、ある場所で20世紀と21世紀に分かれる
• 関数ではないが、control+; 、control+: を知っておくと便利

日付を処理する関数

• TODAY()：今日の日付の数値（時刻は0:00）
• NOW()：今現在の日付時刻の数値
• YEAR(...), MONTH(...), DAY(...), HOUR(...), MINUTE(...), SECOND(...)：日付時刻の数値を引数に取り、年、月、日、時、分、秒のそれぞれの数値を求める
• WEEKDAY(...), WEEKNUM(...)：日付時刻数値から、曜日を1〜7の数値で求める／その年の何週目かを求める
• DATE(...), TIME(...)：年月日時分秒をそれぞれ別々に与えて日付時刻を求める
• DATEVALUE(...), TIMEVALUE(...)：文字列から日付時刻の数値を求める

条件判断を行う関数

IF(条件式, TRUEのときの値, FALSEのときの値)

• 条件式に使われる演算子：<　>　=　<=　>=　<>
• 例：値1 > 値2　→　値1が値2よりも大きい場合はTRUE、それ以外はFALSE
• 演算子の両辺は、定数、セル参照を指定できる

その他に条件式に使われる関数

• AND(条件1, 条件2, ...)：2つ以上の条件を指定し、条件がすべてTRUEなら、結果がTRUEになる。そうでない場合はFALSE
• OR(条件1, 条件2, ...)：2つ以上の条件を指定し、条件が１つでもTRUEなら、結果がTRUEになる。条件が全部FALSEの場合のみFALSE
• ISERROR(セル)：セルがエラーならTRUE

ワークシート作成例

本日の演習

練習問題4-1

以下のような結果を得られる計算式を作成する

• 今日が21世紀になってから何日目なのかを表示する式を入力する
• 今現在が、今年になって何秒経過したのかを表示する式を入力する
• 文字列の中の半角のスペースが何文字目かを表示する式を入力する（適当なセルに文字列を入れて、そのセルを参照すれば良い）
• 1つ上の式の結果を利用して、文字列の最初から、半角スペースの前までの文字列を取り出す
• 1つ上の式は、つまりは名前（一般的なアジア系の記述）から「姓」を取り出す式となっている。では、名前から「名」を取り出す式を入力してみる

練習問題4-2

日付を入力したセルを参照して、その翌月の月末の日を求める式を入力すること。ヒントはEOMONTH関数。年を越してもきちんと計算する事を確認すること。さらに、「翌月の初日」を求める式を入力すること。

練習問題4-3

名前から姓と名を取り出す式を関数を使って作成するが、以下の動作を実装すること。

• 姓と名の間は、半角ないしは全角のスペースを利用できる
• 姓と名を区切る空白がない場合は、「姓と名を区切る空白がありません」というメッセージのみが表示される

練習問題4-4

1つのセルに今日の日付を入力し、オートフィルで下に広げると、1日ずつの日付が自動的に入力される。20個以上のセルに広げておく。そして、その範囲に条件付き書式を設定して、日曜の場合のセルの背景を赤いものに、土曜の場合のセルの背景を青いものにすること。

条件付き書式は、「ホーム」タブの「スタイル」グループにある「条件付き書式」から機能を呼び出します。「ルールの管理」「新しいルール」あたりを利用すると良いでしょう。

解答

この章の解答のワークシートはダウンロードできるようにしておきました。こちらからダウンロードしてください。

練習問題4-1〜4-3については以下の通りです。まず、正解はこれ１つではありません。ここの回答も状況に不正解になる可能性もあります。100%の解答が常にあるとは限りません。特に、21世紀から何日目かを求める解答については、「1秒追加」するのはやりすぎかもしれませんが、ここではROUNDUP関数をともかく使うということを優先してこうしました。なぜ、1秒追加するのかは、0時0分0秒で間違いが発生するからです。また、姓名から名前を取り出す場合、RIGHT関数でももちろんかまいませんが、MID関数を使えば「最初の空白より後の文字すべて」という取り出し方ができます。取り出す長さをLEN関数で求めている点にも注意を払いましょう。4-3はちょっと難しいですが、こういう場合は途中の結果を順次セルに分けると分かりやすいかもしれません。ポイントは、C19のセルのIF文でしょう。もちろん、OR関数を思いついたかもしれませんが、ここでは「スペースがない」「半角である」「全角である」の3通りにしかならないことを考え、それぞれの場合でどの値が欲しいのかを考えてみると、これが１つの解答になるでしょう。

問題文に「ないしは」があるのでORかなと思うかもしれません。この式をORあるいはANDを使って書けるのですが、前の回答の方がシンプルでわかりやすいでしょう。なぜ、「ないしは」なのにORじゃないのか？　ORは、複数の条件から判定するときに使います。ここでの「もしくは」は、半角ないしは全角ということで、値のいずれかを使うということです。半角の場合もあれば、全角の場合もある、という状況が変わりますということであり、複数の条件の判定をしているのではありません。つまり、ORは直接的な値の選択といった用途には使えないのです。いきなりフェイクな練習問題ですみません。なお、<>は、=の逆の結果を得る論理演算子で、左右が異なる値ならTRUEになります。2つの条件式のANDは、同様にORでも記述できます。ド・モルガンの法則です。

練習問題4-4はいきなり「条件付き書式」をやってもらいました。ある意味、こうした機能は習うより慣れろ…なんではありますが、Excelのこの機能はいきなり使えと言われても分かりにくいと思いますので、手順を示します。条件付き書式は、セルの書式を条件に応じて設定するという機能ですが、ここでは条件が「100以上」とかいったシンプルなものではなく、WEEKDAY関数の結果をもとに考えないといけないという点があります。そして、そのときに設定する式のセル参照の方法をどうすればいいのかということも問題になるでしょう。

まず、日付が入力されたセルを選択しておいて、「ホーム」タブの「スタイル」にある「条件付き書式」をクリックしてメニューを表示し、「ルールの管理」を選択します。

すると、ルールの管理のウインドウが開きます。ここで「新規ルール」ボタンをクリックします。

新たなルールを定義しますが、上部のリストからは「数式を使用して、書式設定するセルを決定」を選択します（明らかに日本語がおかしいですね。きっと正しくは「数式を使用して、セルの書式設定を決定」でしょう…）。

下半分のところには、式として「=WEEKDAY(A1)=1」と入力します。奇妙な式と思うかもしれません。最初の=は「式の始まり」を示し、後の=は条件判断のための演算子です。つまり、「A1の曜日が1かどうか」を判定しているのです。今、セル範囲はA1:A26ですが、設定する式は左上のセルのセル番地を相対参照で記述します。すると、A2のセルの書式は「WEEKDAY(A2)=1」の結果に従い、A10のセルの書式は「WEEKDAY(A10)=1」の結果に従い…という感じで、結果的には「それぞれのセル」に対しての式が計算されて指定された書式が設定されたりされなかったりということになります。式の最初の=がないと、「="weekday(a1)=1"」のように、式を文字列として扱ってしまいます。

「書式」ボタンをクリックして、文字の色が赤になるように設定して、OKボタンをクリックします。

土曜なら青になるという設定も、同じ手順で追加します。もちろん，式は「=WEEKDAY(A1)=7」です。「適用」をクリックすると、OKでダイアログボックスを消さなくても結果を見る事ができます。

OKをクリックして、ダイアログボックスを消すと、確かにセルの値に応じて書式が設定されています。