[Excel]献立作成ソフトを作ろう 第7回 レシピ作成シートの作成(4)
献立作成ソフトを作ろう第7回目のテーマは、単純な関数の使用です。SUM関数、IF関数及びTEXT関数を使用します。
材料1食分の合計分量を計算する
前回、K10:K29セルを使って、材料1食分の分量を計算しました。次は、材料1食分の合計分量を計算します。Excelで単純な合計を求める場合、SUM関数を使います。本連載で初めて関数を使用するのに際し、関数についての基本的な事項を説明します。
関数とは、何らかの値を出力する、名前の付いた数式のようなものです。関数が出力する値を戻り値と言います。値とは、具体的には数値か文字列のことです。関数が戻り値を出力することを”関数が値を返す”といった言い方をします。知っている方も多いと思いますが、SUM関数は数値を返します。
関数を使用する際は、関数名にカッコ()を付けて書きます。カッコの中には、関数毎に定義された引数を指定します。引数とは、関数が使用する値のことです。引数には、定数、セル参照又は関数の戻り値のいずれか1つか、これらを使用した数式を指定します。中には、引数が無い関数も存在します。ここまでの説明が良く分からない方は、Excelの数式と仲良くなりたい人向けの話をご一読下さい。
ここで、SUM関数の構文を下記に示します。
構文
SUM(数値 1, [数値 2], …)
|
引数名 |
説明 |
| 数値 1 (必須) | 加算する最初の数。ここには 4 のような数値、B6 のようなセル参照、B2:B8 のようなセル範囲を指定できます。 |
| 数値 2 ～ 255 (省略可能) |
これは、加算する 2 番目の数値です。この方法で最大 255 個の数値を指定することができます。 |
SUM関数は、引数で指定した数値の合計を返す関数です。引数には、任意の数の数値を指定できます(最大255個)。引数が複数指定出来る場合は、引数を , (カンマ)で区切ります。引数は、左から順に、”第1引数”、”第2引数”、”第3引数”・・・第n引数といった言い方をします。
前置きが長くなりましたが、材料1食分の合計分量を計算しましょう。K30セルに「=SUM(K10:K29)」と入力してEnterキーを押します。この場合の引数の数は、セル参照K10:K29の1つです。セルの数は20個ですが、引数としては1個のセル参照として数えます。しかし、「=SUM(K10,K11,K12)」とした場合の引数は3つと数えます。
K30セルに「365.8」または「365.75」と表示されればOKです。Excelはバージョンや設定により、入力見たセルの真上のセルの書式を自動的に適用することがあります。この機能が働くと「365.8」となり、働かないと「365.75」となります。いずれにしても、小数点以下の桁数は書式設定で1桁に合わせておきましょう。
レシピの手順に自動的に連番を付ける
レシピの手順に連番を付けてみましょう。連番くらいオートフィルを使えば簡単に入力できますが、毎回入力するのも煩わしいものです。ここでは、IF関数を使用して、レシピの手順が入力されている行に、自動的に連番を表示してみます。IF関数の構文を下記に示します。
構文
IF( 論理式, 値が真の場合, [値が偽の場合])
|
引数名 |
説明 |
| 論理式 (必須) | テストする条件 |
|
値が真の場合 (必須) |
値が真の場合の結果が TRUE の場合に返す値 |
| 値が偽の場合 (オプション) |
値が偽の場合の結果が FALSE の場合に返す値 |
IF関数は、第一引数に論理式を指定します。論理式とは、例えば「A1 < 100」のように、2つの値を比較演算子( < , > , = など)で比較する数式のことです。また、関数を使った論理式もありますが、それはまたの機会に説明します。IF関数は、論理式が正しければ第2引数を、間違っていれば第3引数を返します。なお、論理式が正しい場合、論理式はTRUEという値を返します。逆に間違っているとFALSEという値を返します。IF関数については、IF関数などの論理関数と仲良くなりたい人向けの話で解説しておりますので、自信の無い方はこちらをご参照下さい。
まずはC10セルに「=IF( D10 = “" , “" , B10 )」と入力してEnterキーを押して下さい。C10セルが「1」となればOKです。
数式の中の「""」は空の文字列を意味する文字列定数です。空の文字列は、数値の「0」のようなもので、文字が無いことを意味しています。別の表現をすると、0文字の文字列のことです。Excelは、何も入力されていないセルの値と「""」は同じであると判断します。
数式「=IF( D10 = “" , “" , B10 )」のIF関数の第一引数(論理式)は「D10 = “"」ですね。この論理式は、”D10セルには文字が入力されていない"と主張しています。この主張が正しいかどうかは、D10セルの値を確認しないと分かりません。D10セルには、作り方の手順「 “（１）玉ねぎは８等分のくし形切りにし、にんじんは１．５ｃｍ幅のいちょう切りにする。じゃがいもは４等分に切る。さやいんげんは３ｃｍ長さに切る。" 」という文字が入力されているため、先ほどの主張は間違っています。言い換えると、この論理式は間違っているため、論理式はFALSEを返します。なので、第2引数「""」は無視され、第3引数「B10」を返します。B10セルには「1」が入力されているため、C10セルには「1」が表示されたという訳です。この動きを数式で見てみましょう。
- =IF( D10 = “" , “" , B10 )
- =IF( “（１）玉ねぎは(中略)長さに切る。" = “" , “" , B10)
=IF( “（１）玉ねぎは(中略)長さに切る。" = “" , “" , B10)
- =IF(FALSE , “" , B10 )
=IF(FALSE , “" , B10 ) - =B10
- =1
次に、C10セルをC29セルまで右クリックオートフィルで書式なしコピーをして下さい。
C11セルからC13セルまでは連番が表示されましたが、それ以降のC14セルからC29セルは何も表示されていませんね。これは、手順入力セル(D14:D29)が空欄(つまり「""」と等価)なので、C14:C29セルはIF関数から第2引数「""」が返された結果です。この動きをC14セルを例に見てみましょう。
- =IF( D14 = “" , “" , B14 )
- =IF( “" = “" , “" , B14 )
=IF( “" = “" , “" , B14 ) - =IF( TRUE , “" , B14 )
=IF( TRUE , “" , B14 ) - =“"
これがIF関数の動きです。実務ではIF関数も空の文字列「""」も多用するため、しっかり理解しておきましょう。
レシピの手順の連番に書式設定を加える
レシピの手順に自動的に連番を付けることができました。しかし、後から連番にカッコ(例：(1)など)を付けたくなった場合、IF関数の第3引数を直接編集しなければなりません。しかし、数式の編集はバグの元です。バグとは、プログラム上の間違いのことです。数式は1度作成したら、その後は可能な限り編集すべきではありません。なので、一度作成した数式を編集せずに、番号の表現を変更できるようにしましょう。これには様々な手法がありますが、今回はTEXT関数を使用します。
構文
TEXT(値, 表示形式)
| 引数名 | 説明 |
| 値 | テキストに変換する数値。 |
| 表示形式 | 指定された値に適用する書式を定義するテキスト文字列。 |
TEXT関数は、数値を文字列に変換する関数です。TEXT関数の特徴は、数値に任意の表示形式を適用して文字列に変換することができます。ここで言う表示形式とは、第6回で説明した＜セルの書式設定＞ダイアログボックス内にある＜表示形式＞タブの＜種類＞テキストボックスで設定できるものと同一です。下記に、第6回で使用した＜セルの書式設定＞ダイアログボックスの画像を表示しておきます。
余談ですが、TEXT関数を使用すると、数式により、Excelのメニューから設定する書式設定と同じ表現が出来るということです。それはつまり、書式設定を適用した文字列を、セルの値として使用できることを意味しています。ここら辺は難しい話なので、完全に理解できなくでも問題ありません。また、Excelの表示形式は奥が深いので、この場では必要最小限の説明に留めます。気になる方はこちらをご参照下さい。
本題に戻ります。TEXT関数の第1引数には、文字列に変換したい数値を指定します。第2引数には表示形式を文字列で指定します。早速使ってみましょう。C10セルには現在、数式「=IF( D10 = “" , “" , B10 )」が入力されていますが、この中の「B10」を「TEXT( B10 , $C$5 )」に書き換え、「=IF( D10 = “" , “" , TEXT( B10 , $C$5 ))」とします。数式中の「$」は第6回で説明した絶対参照です。C5セルは、 第5回で「(#)」を入力しましたが、実はこれが今回使用する表示形式です。"#"はTEXT関数の第2引数に指定することで、第1引数で指定した数値に置換され、これが戻り値となります。"()"は表示形式上は特別な意味を持たない、ただの文字です(特別な意味を持つ文字の方が圧倒的に少ないですが、念のため)。参考までに、「TEXT(B10 , $C$5) 」のセル参照を値に置き換えると「TEXT(1,"(#)")」となっています。
C10セルが「(1)」となればOKです。続けて、C10セルをC29セルまで書式なしコピーしましょう。
全ての番号にカッコが付きましたね。他の表示形式を試してみましょう。
C5セルに「<#>」と入力した場合は下記の通り。"<>"は表示形式上は特別な意味を持たない、ただの文字です。
C5セルに「'(00)」と入力した場合は下記の通り。番号が2桁になりましたね。"0″はTEXT関数の第2引数に指定することで、第1引数で指定した数値に置換されます。"#"と同じようですが、"0″の場合は、桁数を固定する効果があります。
なお、C5セルに「'(00)」を入力する際、先頭の「’」を忘れると、Excelが自動的に数値の「0」に変換してしまい、上手く入力出来ません。「’」は、セルの入力値が文字列定数であることをExcelに伝えるための記号です。Excelの自動変換を抑制する際に使用します。他にも、「="(00)")」と入力しても自動変換を抑制することができます。
C5セルに「◆」と入力した場合は下記の通り。"◆"は表示形式上は特別な意味を持たない、ただの文字です。表示形式上特別な意味を持つ文字(“#"や"0″など)を指定しないと、第1引数で指定した数値は無視されます。
今日はここまで。