Excel関数の勉強 １

主な内容
１．日付け、時刻の表記法
２．引数が１個の、[日付]、[時刻]の関数
３．引数が無い関数
４．小数点付近の数値を、取り扱う関数
５．数値を文字列に変える１　：　(数値の表現)-(数を表す文字列として表現)-(引き続き数値として使用できるような文字列表現)
６．数値を文字列に変える２　：　(TEXT 関数を使います。)

関数とは予め定義された数式のことで、計算に必要な[引数]と呼ばれる特定の値を使い、 定められた関数の書式[=関数名(引数1,引数2,,,,)]に従って入力するだけで、計算結果を求めることができる仕組みになっています。
関数を使用すると、単純な計算だけでなく複雑な計算も行うことができます。

というと、非常に堅苦しいのですが、
Excelの関数の本来の目的は、複雑な処理を簡単にできるように、予めプログラムで組み込まれている仕掛けと考えることができます。 ですから、易しい関数から１つづつ消化して行けば誰もが理解できるので、気軽に挑戦してみましょう。
とはいうものの、入門者にとって、ハードルが２〜３存在します。

第１のハードル「使用する関数」について

エクセルのワークシート関数（予め組み込まれている関数）が、Excel-2000で245個用意されています。（別途インストールを必要とする、アドイン関数を含めると338個）適材適所に、どの関数を使用して良いのか迷うところです。 １つ１つ地道に克服することが肝要です。２〜３０消化すれば、あとは、理解が容易になり、その都度応用すれば良いことになる筈です。

第２のハードル「引数」（ひきすう）という概念について

関数の書式は、[ =関数名(引数1,引数2,・,・,・) ] で表され、引数の無いもの(無くても[ =関数名() ]と表示する )から ３０個有るものも存在します。厄介なことに、使用する関数によって、入力する引数のタイプ（◇引数のタイプ一覧表◇参照）が変わることです。しかし、正当な入力方法→「数式パレット」を使用すると 注釈や指示があり、また、WinXPの場合は「この関数のヘルプ」で具体的説明もあります。これも関数の２〜３０個を消化すれば、 [引数]の何たるかは理解できるようになります。

◇引数のタイプ一覧表◇

引数のタイプ：、論理値、数値、文字、セル参照、エラー値、配列、他などが有ります。

1. 　論 理 値　：TRUE（真）とFALSE（偽）の二つがあります。
2. 　数　　値　：整数、少数、負数を含むあらゆる数値です。
3. 　文　　字　："日付"、"文字列"などの普通の文字ですが、関数の中で引数として使うときは、
   　　　　　　　"文字列"のように前後を["]（ダブルクォーテーションで囲んで使います。
4. 　セル参照　：(1)セル単独指定
   　　　　　　：(2)セル範囲指定　[A1セル番地〜D10セル番地]は[A1:D10]の様に[:]コロンで区切ります。
   　　　　　　；(3)セル不連続複数指定　セル番地を[,]カンマで区切ります。例えば「A1、C5、D10」です。
5. 　エラー値　：####, #VALUE!, #DIV/0!, #NAME?, #N/A, #REF!, #NUM!, #NULL! の８種類があります。
6. 　配　　列　：配列とは同じ条件で整頓された引数の範囲を[{ }]の括弧で囲んで指定します。
7. 　数　　式　：[ =B2+C2 ]または、[ =1+2 ]などの計算式
8. 　関　　数　：関数を入れ子（ネスト）にして引数に使えます。
9. 　名　　前　：定義された名前、セル範囲に付けられた名前など使えます

第２のハードルをクリアーしよう。

ということで、「関数の勉強１，２」では、「引数を主眼」にして学びます。」

易しい関数・身近な関数のピックアップ

その前に、日付け、時刻の表記法

WindowsXP、Word 2003、Excel 2003 から、日付、時刻の表示の基本を考えてみます。
第１図は、Word 2003 を開いて、
１．[２００５]と入力した場合、ポップアップメニューで「２００５年８月３日」と表示しますか？の問いに、OKなら[Enter]キーです。
２．[2005]と入力した場合、下図」のポップアップメニューが表示され、[Enter]キーで「2005/08/03」と表示されました。

第１図(Word-2003)　　（右側の図はExcel-2003の場合)　第２図　(平成17年8月3日と入力しても数式バーは[2005/8/3]です)
　　　　

第３図(Excel-2003でショートカット・キーで、日付、時刻を表示)

第４図(セルの書式設定で、標準を選択すると、
第５図の[C4]、[C5]のシリアル値になります。

第５図([C4]、[C5]のシリアル値については、こちらを参照ください。)

上の事柄から、Windowsでの、日付、時刻の表示は下記のとおりであることがわかります。
　　　　西暦何年,何月,何日
例えば、　　2005　/ 8　/ 3
　　　　　　何時,何分,何秒
　　　　　　 17 : 52 ; 00 　 (17:　52;　00 の「 時、分、秒」の表示は第３図の数式バーから)

また、上記の説明を待たずとも、タスクトレイの時計の表示、および、「日付けと時刻のプロパテイ」を見れば、一目瞭然です。
日付、時刻の表記法が分かったところで、これらを、関数を使って個別に取り出すことができるのです。

引数が１個の、[日付]、[時刻]の関数から

書式：「 =関数名(引数1) 」です。「(引数1)は[シリアル値]」

[YEAR] 関数を使って、[年(西暦年)]のみを取り出す。

操作手順(Excel-2003の場合)

1. 回答を表示させるセル(戻り値表示セル)を選択します。ここでは、[C2]です。
2. 数式バーの[fx]関数挿入ボタンをクリックします。
   (関数の挿入ダイアログが現れます。)
3. 関数の分類(C)欄のテキストボックスの右端の▼をクリックし、日付/時刻を選択します。
4. 関数名(N)一覧から[YEAR]を選択すると、下に、注釈が表示されます。
   YEAR（シリアル値）
   シリアル値を年に変換した結果(1900〜9999年の範囲の整数)を返します。
   となっています。
5. [OK]をクリックすると、「関数の引数」ダイアログが現れます。
6. シリアル値テキストボックスにカーソルが点滅状態です。ここで、セル参照のセルをクリックしても良いのですが、 ダイアログでセルが隠れた場合の折りたたみボタンが右端に有りますので、そのボタンをクリックしてみましょう。 (ダイアログの上辺の帯を掴んで移動する手段もありますが、折りたたみボタンを使う習慣を付けたいものです。)
7. その、折りたたみボタンをクリックします。関数の引数」ダイアログが小さくなりました。
8. セル参照で、ここでは、[A2]セルをクリックします。[A2]セルがムービングボ−ダー状態になり、選択されたことを現しています。
9. 小さくなった関数の引数」ダイアログの「折りたたみボタン」(下図で、白色斜め矢印)をクリックして、元のダイアログに戻します。数式の結果が表示されています。
10. [OK]ボタンを押して完了します。

[MONTH] 関数を使って、[月]のみを取り出す。

上記操作手順の「４」関数名(N)一覧から[MONTH]を選択します。その他は全く同じです。

[DAY] 関数を使って、[日]のみを取り出す。

上記操作手順の「４」関数名(N)一覧から[DAY]を選択します。その他は全く同じです。

[HOUR] 関数を使って、[時]のみを取り出す。

上記操作手順の「４」関数名(N)一覧から[HOUR]を選択します。その他は全く同じです。

[MINUTE] 関数を使って、[分]のみを取り出す。

上記操作手順の「４」関数名(N)一覧から[MINUTE]を選択します。その他は全く同じです。

[SECOND] 関数を使って、[秒]のみを取り出す。

上記操作手順の「４」関数名(N)一覧から[SECOND]を選択します。その他は全く同じです。

上図で、時、分、秒の表示が「8:16」になっていますが、３つ共「秒」が埋め込まれています。[A7]をクリックした状態の時、数式バーには、「59（秒）」と表示されています。

引数が無い関数

書式：「 =関数名() 」で()内は無記入です。

[TODAY] 関数を使って、[今日の日付]を取り出す。

上は「今日は”いくにち”です。」を求めました。引数はありません。
すると今度は今は”なんどき”かが有ってもよいことになります。有るとしたら、多分引数はないと思います。

有りました。[N0W]関数です。

思ったとおり、引数はありませんでした。[TODAY] 関数と違って「日付と時刻」を同時に表示します。任意のセルを選択して、[N0W] 関数を開いただけで、[2005/8/4 21:01]と表示されました。
そのセルを選択して、セルの書式設定[表示形式]タブの「標準」をクリックしたところ、[38568.87617]のシリアル値に変換されました。パソコンの内部時計が 時々刻々とシリアル値を刻んでいるのです。

[TODAY] 関数と[N0W]関数で日付･時刻を入力し。日付･時刻の夫々を取り出す

下図入力関数で、[TODAY] 関数を使わず、[N0W] 関数だけでもできますが。（８/１０追記）

シリアル値と無関係で「引数」無しは

段々、興味が湧いてきました。円周率の「π」は「引数」が無さそうです。数式バーの[fx]ボタンを押して現れる「関数の挿入」 ダイアログの関数の検索(S)欄に「円周率」と記入し、検索開始ボタンを押すと選択状態で[PI]とでました。
なお、注釈で
PI()
円周率Π (3.14159・・・・・・)を返します。となっていまして、[PI()]は、書式を意味し、引数の無いことが分かりました。
[OK]→[0K]ボタンで、セルに回答が表示されました。

ちなみに、同様の手段で「平方根」を検索したところ、「 SQRT(数値) で、数値の正の平方根を返します。」となっていました。

[数学/三角]関連の関数の７０〜８０％位は、引数１個で、引数のタイプは「 (数値) 」になっています。その中から、一番親しまれている[SUM] 関数を取り上げました。何と言っても数ある関数のなかで、 唯一、ツールバーに[Σ]のアイコンが存在するのですから。ただし、引数１個でしかも、範囲指定で使用することが多いのですが、 引数が３０個まで使用できるのです。

[SUM] 関数を使って、集計する

Excel-2003の場合[Σ]のアイコンの右の小さな▼ボタンをクリックすると、よく使われる関数の[平均],[データの個数],[最大値],[最小値]が即使えるようになっています。 更に[その他の機能]があって、これを押すと、数式バーの[fx]関数挿入ボタンをクリックしたのと同様に、関数の挿入ダイアログが現れます。

連続した[セル範囲]を指定するだけなら、[Σ]のオートSUMボタンで合計をだしますが、 複数の[セル範囲]の指定、飛び地のセル(値)を合計に入れたい場合には、数式パレットを使用した方が分かり易いので、正攻法での説明にいたします。

操作手順(Excel-2003の場合で、選択箇所が３箇所有る場合)

1. 回答を表示させるセル(戻り値表示セル)を選択します。全く任意のセルが選べます。
2. 数式バーの[fx]関数挿入ボタンをクリックします。
   (関数の挿入ダイアログが現れます。)
3. 関数の分類(C)欄のテキストボックスの右端の▼をクリックし、[数学/三角]を選択します。 種別が分からないときは、「すべて表示」を選択します。
4. 関数名(N)一覧から[SUM]を選択すると、下に、注釈が表示されます。
   SUM(引数1,引数2,,,,)
   引数の合計を返します。
   となっています。[,,,,]は引数が沢山あるしるしです。
   なお、引数1,引数2,,,,等全て[数値]です。
5. [OK]をクリックすると、「関数の引数」ダイアログが現れます。（Excel-2000までは、数式パレットと呼ばれています。）
6. [数値1]テキストボックスには、予測されるセル範囲が書き込まれています。(または、カーソルが点滅状態)ここで、セル参照のセルをクリックしても良いのですが、 ダイアログでセルが隠れた場合の折りたたみボタンが右端に有りますので、そのボタンをクリックしてみましょう。 (ダイアログの上辺の帯を掴んで移動する手段もありますが、折りたたみボタンを使う習慣を付けたいものです。)
7. その、折りたたみボタンをクリックします。関数の引数」ダイアログが小さくなりました。
8. セル参照で、例えば、[A2]セルから[A15]セルまで、ドラッグで選択します。 パレットの(引数1)の[数値1]には、[A2：A15]と表示されます。[A2：A15]セルがムービングボ−ダー状態になり、 選択されたことを現しています。
9. 再び「折りたたみボタン」を押すと、「関数の引数」ダイアログに戻ります。
10. [Tab]キーを押します。カーソルが[数値2]に移動します。
11. また、「折りたたみボタン」を押してダイアログを小さくして、例えば、[D2]セルから[D15]セルまで、ドラッグで選択します。パレットの引数1には、[D2：D15]と表示されます。[D2：D15]セルがムービングボ−ダー状態になり、選択されたことを現しています。
12. 「折りたたみボタン」を押して関数の引数」ダイアログに戻します。
13. [Tab]キーをおして、[数値3]のボックスにカーソルを移動させます。
14. [数値3]のボックスの右端の「折りたたみボタン」を押してダイアログを小さくして、例えば、[F15]セルを選択します。
15. 「折りたたみボタン」を押して、元のダイアログに戻します。数式の結果が表示されています。
16. [OK]ボタンを押して完了します。

数値を自在に操る

Excelの「書式設定ツールバー」には、数値に、[\]を付けたり、千単位で[,]で区切ったり、 小数点以下何位で表示[.***]したりする機能がボタン化されています。並び順から挙げると下記になります。

• 通貨スタイル
• パーセントスタイル
• 桁区切りスタイル
• 小数点表示桁上げ
• 小数点表示桁下げ

単なる数値を、上のような表示に変えても[数値]として計算に使用できます。
(上記ボタンで設定した書式の解除は「ボタン」からはできません。[セルの書式設定],[表示形式]ダイアログで、[標準]を選択 して、解除します。）

小数点付近の数値を、取り扱う関数について

下記()内は「 =関数名(引数1,引数2) 」です。

数値は、対象となる数値です。
桁数の指定に、「正の数」、「0（ゼロ）で指定」、「負の数で指定」の３つの方法があります。
(1)正の数で指定すると、小数点以下で指定数の桁数になるよう４捨５入されます。
(2)0（ゼロ）で指定すると、整数表示になるよう小数点以下を４捨５入します
(3)負の数で指定すると、整数部で指定数の桁数部を丸めるよう４捨５入されます。
例えば、[ROUND] 関数の場合、[155]の数値に、桁数[-2]を指定すると、百位で変換が行われ[200]と返されます。
[ROUNDUP] 関数の場合、[155]の数値に、桁数[-2]を指定すると、[200]と返されます。 また、[ROUNDDOWN] 関数の場合、[155」の数値に、桁数[-2]を指定すると、[100]と返されます。
ちなみに、十位で変換する場合は、桁数[-1]の指定になります。

桁数を決めて数値を四捨五入する。

[ROUND] 関数

書式は「 ROUND（数値，桁数） 」です。

桁数を決めて数値を切り上げる。

[ROUNDUP] 関数

書式は「 ROUNDUP（数値，桁数） 」　引数２個で、タイプは[ROUND]関数と同じです。

桁数を決めて数値を切り捨てる。

[ROUNDDOWN] 関数

書式は「 ROUNDDOWN（数値，桁数） 」引数２個で、タイプは[ROUND]関数と同じです。

専ら切り捨てます。

[TRUNC] 関数

書式は「 TRUNC(数値, 桁数) 」　　　引数２個で、タイプは[ROUND]関数と同じです。

数値の小数点以下を切り捨てたり、または指定した桁数になるよう切り捨てることができます。

専ら整数化します。

[INT] 関数

書式は「 INT(数値) 」です。　　　　引数１個で、タイプは[数値]です。

ここでは、引数を中心に記述しています。詳しくはいろいろな関数４を参照ください。

通貨、％、桁区切りスタイルについて

範囲を指定してツールバーのボタンで処理するか、
[書式(O)]メニュー→[セル(E)]→[表示形式]タブから、[通貨]を選択。[パーセンテージ]を選択。[数値]を選択して、 「桁区切り(,)を使用する(U)」にチエックをいれる。などの方法があります。
また、「小数点以下の桁数」の設定もここでできます。

数値を文字列に変える　１

数値の表現

• Excelでは、セル内に全角数字を入力しても、半角数字で右寄せに表示されます。これは、[数値」です。
• とにかく、数字だけの羅列は、自動的に半角数字になり、右寄せに表示されるのが、[数値]の特徴です。
• 日付けを「2005/8/10」と入力すると、右寄せにそのまま表示されますが、これも「シリアル値」という[数値]です。
  元号(明治(33年以降),大正,昭和,平成)の入った年月日をすべて記入した場合
• 日付けを「平成７年８月１０日」と全角で入力しても、[Enter]キーを押すと「平成7年8月10日」のように、 数字は自動的に半角に修正され右寄せに表示されます。これも「シリアル値」という[数値]です。

数を表す文字列として表現

• 数字だけの羅列だけでなく、郵便番号のように、間に「-」を入れると「数を表す文字列」として表示されます。全角、半角共通
  たとえば、[123-1]または[１２３−１]と入力しても「右寄せ表示」にはなりません。しかし、オートフィルすると、 [123-2],[123-3]のように、序列に表示されます。
• コードナンバーの[No.1]または[Ｎｏ１]の場合も全く上と同様です。

数字を全角で表示するには

1. ['](アポストロフィー)を先頭に打って入力します。全角数字そのままで表示されます。
2. セルを選択したまま、「セルの書式設定」の[表示形式]タブ画面で、[文字列]を選択し、書式を埋めておくと、全角数字が入力できます。
   ただし、上の2つの方法では、セルの左上隅に小さな三角印が付きます。
3. 数式扱いで、[=]から「 ="１２３・・・" 」と["](ダブルクォーテーション)で囲んで入力します。 完全文字列化で、半角数字も文字列データとして、左寄せに表示されます。

引き続き数値として使用できるような文字列表現

よろしかったら、下表をコピーし、EXCELの[A1]セルに貼り付けて、ご使用ください。

解説

• [ROUND] 関数：書式は ROUND（数値，桁数）
  桁数を決めて数値を４捨５入します。
  
• [ROUNDUP] 関数：書式は ROUNDUP（数値，桁数）
  指定桁数で数値を「４捨５入」します。
• [ROUNDDOWN] 関数：書式は ROUNDDOWN（数値，桁数）
  指定桁数で数値を切り捨てます。
• [TRUNC] 関数：書式は TRUNC(数値, 桁数)
  数値の小数点以下を切り捨てたり、または指定した桁数になるよう切り捨てることができます。
  上の例では、桁数入力を省力し、整数化を図りました、
• [INT] 関数 ：書式は INT(数値)
  数値の小数点以下を切り捨て、整数を返します。 言い換えると、指定した数値を超えない整数を求める関数です。
  ですから、負数の場合、[-100]＞[-101]の関係になり、戻り値が[-101]と表示されています。

数値を文字列に変える　２

TEXT 関数を使います。

数式バーの[fx]関数挿入ボタンをクリックすると、関数の挿入ダイアログが現れます。

1. 回答を表示させるセル(戻り値表示セル)を選択します。全く任意のセルが選べます。
2. 数式バーの[fx]関数挿入ボタンをクリックします。
   (関数の挿入ダイアログが現れます。)
3. 関数の分類(C)欄のテキストボックスの右端の▼をクリックし、[文字列操作]を選択します。（文字列 操作関係だけで、３３個ありました。）種別が分からないときは、「すべて表示」を選択します。
4. 関数名(N)一覧から[TEXT]を選択すると、下に、注釈が表示されます。
   TEXT(値,表示形式)
   数値に指定した書式を設定し、文字列に変換した結果を返します。
   となっています。
5. 左下に「この関数のヘルプ」がありますので、ヘルプをクリックしてみました。
6. 下図は、ヘルプのサンプルを実習した結果です。

第１の図

第１の図「 =A2&"さんの売上は"&TEXT(B2,"\#,##0")&"分" 」

説明

1. 「A2」は、セル参照(吉田)
2. 始めの「&」は、「A2」セル内容と次の「さんの売上は」を連結しています。
3. 次の「&」は、「さんの売上は」とTEXT(値,表示形式)を連結しています。
4. [値]は、「B2」のセル参照(2800)
5. [表示形式]は、通貨表示が[\]、３桁区切りが[#,##0]の書式記号で設定されています。数値データ[2800]は、["]で囲まれた書式記号が適用され文字データになります。
6. 3番目の「&」は、「TEXT関数」と文字列「分」を連結しています。
7. ["](ダブルクォーテイション)は、「文字」に表示したいときに["]で囲みます。TEXT関数の目的でもあります。
   逆にいうと、["]で囲まれた部分は全て文字表現になり、数値ではなくなります。従って計算には使用できなくなります。
   仕上がりは、[A5]になります。

また、「&」は、離れ離れの「セル」の[文字列]や[数値]を連結して表示するときにも使われます。
たとえば、ページ下段の[F40]の合計数値を、上段の[B2]の「請求金額」右隣の[C2]に使う場合、「 ="金"&F40&"円也" 」といった使い方です。

第２の図

第１の図「 =A2&"さんの売上は"&TEXT(B2,"\#,##0")&"分" 」
第２の図「 =A3&"さんの売上は総売上高の"&TEXT(B3,"0%") 」
の両者の比較で、「"0%"」に「&」が付かないのは、TEXT関数の[表示形式]だからです。
また、「"0%"」の[0]は、「B3」の数値を呼び込むための[0]です。[0]が無いとただの「%」表示になります。「B3」の「40%」の % は見かけ上の % で数値は[0.4]ですので、%の重複にはなりません。
仕上がりは[A6]になります。

TEXT 関数使用で、いろいろ表示

第３の図

TEXT関数「引数２」の[表示形式]設定に使用する「書式記号」とは

[書式(O)]メニュー→[セル(E)]→[表示形式]タブ→[分類(C)]欄→[ユーザー定義]を クリックして現れる[種類(T)]欄にリストアップされた、特定の書式や記号を「書式記号」と言います。
この中から適したものを使用するとか、または編集して、目的の表示ができるようにして、 実行する段階では「表示形式」となります。TEXT 関数の[表示形式]で使用する場合は、[""]のダブルクォーテイションで囲んで使用します。
ただし、「書式記号」は基本パターンの表示ですので、一番似通ったものを選択し、[種類(T)]のテキストボックスでユーザーが、[#]や[0]または[?]などを 適宜、追加、削除して、[種類(T)]の上の「サンプル窓」の表示を見ながら、桁数表示を図ったりします。テキストボックスに直記入で作っても構いません。

[書式]メニューから、[セルの書式設定]ダイアログ[表示形式]タブ→[分類(C)]一覧リストの夫々にも、「書式記号」が使われています。 こちらは、[""]のダブルクオーテイションは不用です。

[数値]は元の数値データのままで、計算に使用できます。表示が変わっているに過ぎません。
これに対して、TEXT関数の[引数２]の[表示形式]として、使用した場合は、文字列データになり、計算はできなくなります。

日付・時刻に関する書式記号は、こちらをご覧ください。
その他に関する書式記号は、こちらをご覧ください。

トップへ戻る     いろいろな関数目次へ戻る
いろいろな関数１１へ戻る     関数の勉強 ２へ進む