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]セルに貼り付けて、ご使用ください。

年月日関係

年　↓

月　↓

日　↓

セル参照用

2005

年月日関係

元号表示

シリアル値

月日表示

シリアル値

セル参照↓

[A6]と同じ　↓

[A6]と同じ↓

2005/8/1

平成17年8月1日

38565

8月1日

38565

曜日関係

セル参照↓

[B9]と同じ↓

WEEKDAY関数

曜日表示

左の標準値

2005/8/1

月

下記空色は全て元の値

数値関係

Ｂ：数列↓

Ｃ：数列↓

Ｄ：数列↓

Ｅ：数列↓

Ｆ：数列↓

元の数値

12345

12345.678

1234.5678

0.012345

-100.123

通貨＄表示

$12,345.00

$12,345.68

$1,234.57

$0.01

-$100.12

上の数値

12345

12345.678

1234.5678

0.012345

-100.123

桁区切り( , )

12,345

12,346

1,235

-100

上の数値

12345

12345.678

1234.5678

0.012345

-100.123

％表示

1234500%

1234568%

123457%

-10012%

上の数値

12345

12345.678

1234.5678

0.012345

-100.123

小数点以下２桁

12345.00

12345.68

1234.57

0.01

-100.12

上の数値

12345

12345.678

1234.5678

0.012345

-100.123

小数点以下４桁

12345.0000

12345.6780

1234.5678

0.0123

-100.1230

ROUND関数

12345

12345.678

1234.5678

0.012345

-100.123

桁数２の場合

12345

12345.68

1234.57

0.01

-100.12

ROUNDUP関数

12345

12345.678

1234.5678

0.012345

-100.123

桁数１の場合

12345

12345.7

1234.6

0.1

-100.2

ROUNDDOWN関数

12345

12345.678

1234.5678

0.012345

-100.123

桁数１の場合

12345

12345.6

1234.5

-100.1

TRUNC関数

12345

12345.678

1234.5678

0.012345

-100.123

桁数指定せずの場合

12345

1234

-100

INT関数

12345

12345.678

1234.5678

0.012345

-100.123

引数は[数値]のみ

12345

1234

-101

元の数値

12345

12345.678

1234.5678

0.012345

-100.123

数値：-1234適用

12345

12346

1235

-100

解説

[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関数の[引数２]の[表示形式]として、使用した場合は、文字列データになり、計算はできなくなります。

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

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

続きます。

トップへ戻る　総目次へ戻る

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

Excel関数の勉強　２

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

主な内容

１．IF 関数について

２．AND 関数について　

３．OR 関数について　

４．AND 関数、OR 関数の「判定の結果」に対して「指示を与える」には： (IF 関数に、組み込む(ネストする)

５．IF 関数を使って、引数タイプの１つ１つを検証

６．セルやセル範囲に名前を付ける

７．専ら[IF]関数を使って、７種別の会費算出

８．[IF]関数に[AND]関数をネストして会費算出

９．[VLOOKUP] 関数を使って会費算出

１０．引数に、配列（行方向や列方向に連続した値を一纏めしたデータ）を使う

前ページで勉

()内が[引数]です。

引数が無い関数

[TODAY] 　関数　書式は TODAY() です。（注：引数が無くても「 TODAY() 」と記述）

[N0W] 　　関数　書式は N0W() 　です。（注：上と同様）

[PI]　　　関数　書式は PI() です。（注：上と同様）

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

[YEAR]　　関数　書式は YEAR(シリアル値) 　です。

[MONTH] 　関数　書式は MONTH(シリアル値)　です。

[DAY] 　　関数　書式は DAY(シリアル値) です。

[HOUR]　　関数　書式は HOUR(シリアル値) 　です。

[MINUTE]　関数　書式は MINUTE(シリアル値) です。

[SECOND]　関数　書式は SECOND(シリアル値) です。

その他引数が１個の関数

[SQRT]　　関数　書式は SQRT(数値) です。

[INT]　　関数　書式は INT(数値)　です。

引数が２個の関数

[ROUND]　関数　書式は ROUND(数値,桁数) 　です。

[ROUNDUP] 関数　書式は ROUNDUP(数値,桁数) です。

[ROUNDDOWN] 関数　書式は ROUNDDOWN(数値,桁数) です。

[TRUNC] 　関数　書式は TRUNC(数値,桁数) 　です。

[TEXT]　　関数　書式は TEXT(値,表示形式)　です。

引数が３０個の関数

[SUM] 　　関数　書式は SUM(数値1,数値2,・・・) です。

◇引数のタイプ一覧表◇

1. 　論理式　：左辺と右辺を、下記比較演算子を使って比較します。(IF,AND,OR,NOT関数で使用されます。)

2. 　論理値　：TRUE（真）とFALSE（偽）の二つがあります。(IF,TRUE,FALSE関数で使用されます。)

3. 　数　　値　：整数、少数、負数を含むあらゆる数値です。

4. 　文　　字　："日付"、"文字列"などの普通の文字ですが、関数の中で引数として使うときは、

　　　　　　　"文字列"のように前後を["]（ダブルクォーテーションで囲んで使います。

5. 　セル参照　：(1)セル単独指定

　　　　　　：(2)セル範囲指定　[A1セル番地～D10セル番地]は[A1:D10]の様に[:]コロンで区切ります。

　　　　　　；(3)セル不連続複数指定　セル番地を[,]カンマで区切ります。例えば「A1、C5、D10」です。

6. 　数　　式　：[ =B2+C2 ]または、[ =1+2 ]などの計算式

7. 　関　　数　：関数を入れ子（ネスト）にして引数に使えます。

8. 　名　　前　：定義された名前、セル範囲に付けられた名前など使えます

9. 　配　　列　：配列とは同じ条件で整頓された引数の範囲を[{ }]の括弧で囲んで指定します。

10. 　エラー値　：####, #VALUE!, #DIV/0!, #NAME?, #N/A, #REF!, #NUM!, #NULL! の８種類があります。

エラー値の詳細については、こちらを参照ください。

論理関数

Excelのワークシート関数（標準で装備）には、論理関数が６個存在しています。

1. [IF]　　関数　書式：IF(論理式(引数1), 真の場合(引数2), 偽の場合(引数3)

　　　　　　　　　　　　　　論理式の結果に応じて、指定された値を返します。

2. [AND] 　関数　書式：AND(論理式1(引数1),論理式2(引数2),・・・)　

　　　　　　　　　　　　　　すべての引数が TRUE のと、き論理値 TRUE を返します。

3. [OR]　　関数　書式：OR(論理式1(引数1),論理式2(引数2),・・・)

　　　　　　　　　　　　　　引数に１つでも TRUE があれば TRUE を返します。

4. [NOT] 　関数　書式：NOT(論理式)

　　　　　　　　　　　　　　引数が FALSE の場合は TRUE、 TRUE の場合は FALSE を返します。

5. [TRUE]　関数　書式：TRUE()

　　　　　　　　　　　　　　論理値 TRUE を返します。

6. [FALSE] 関数　書式：FALSE()

　　　　　　　　　　　　　　論理値 FALSE を返します。

IF 関数について

条件判断と処理の分岐。（YesかNoかを判定し、処理1、処理2を行う。）

書式：IF(論理式, 真の場合, 偽の場合)

(引数１)論理式とは(=条件式)

AとBの２つの事象(数値表示)が有った場合、下記、比較演算子を用いて、条件(条件式)を提示します。

比較演算子(記号)　　　　内容　　　　　　　　　セル参照表示例

[=] 「等号」　　　　：　左辺と右辺が等しい。　　A1=B1

[>] 「～より大きい」：　左辺が右辺より大きい。　A1>B1

[<] 「～より小さい」：　左辺が右辺よりも小さい。A1<B1

[>=]「～以上」　　　：　左辺が右辺以上である。　A1>=B1

[<=]「～以下」　　　：　左辺が右辺以下である。　A1<=B1

[<>]「不等号」　　　：　左辺と右辺が等しくない。A1<>B1 (A1<>0　は条件式で「A1が0でない」という式です。)

(引数２)真の場合とは

論理式(=条件式)に適った場合(TRUEの場合)は、「数式パレット」引数２に「もし、真の場合なら、こうしなさい」と指示を与えます。

(引数３)偽の場合とは

論理式(=条件式)に不適合の場合(FALSEの場合)は、「数式パレット」引数３に「そうでない偽の場合なら、こうしなさい」と指示を与えます。

しかし、偽の場合の戻り値が欲しい場合が生じた場合には、真の場合の戻り値を[""]で空欄にする手法もあります。

IF 関数の使用法については、こちらのページと、こちらのページを参照ください。

AND 関数につ

書式：AND(論理式1,論理式2,論理式3, ・・・論理式n)

論理式の組み立て方はAND 関数、下記OR 関数共IF 関数の論理式と同様です。

注釈では、「論理式1,論理式2・・・には、結果が TRUE または FALSE になる１～３０個の条件式を指定できます。」となっています。

「あれも、これも」の、全てが「条件」を満たしているかどうかを判定する関数です。

ただし、判定は[TRUE(真)]か[FALSE(偽)]のみで、だから、「どうしろ、こうしろ」とまでは、指示していません。

OR 関数につい

書式：OR(論理式1,論理式2,論理式3,・・・論理式n)

注釈では、「論理式1,論理式2・・・には、結果が TRUE または FALSE になる１～３０個の値または論理式を指定できます。」となっています。

「あれか、これか」の、どれか一つでも「条件」を満たしているかどうかを判定する関数です。

ただし、AND 関数同様、判定は[TRUE(真)]か[FALSE(偽)]のみで、だから、「どうしろ、こうしろ」とまでは、指示していません。

AND 関数、OR

IF 関数に、組み込む(ネストする)

IF(OR) 関数の使用法については、こちらのページを参照ください。

また、IF(AND) 関数の使用法については、こちらのページを参照ください。

IF 関数を使っ

IF 関数では、「引数１」に[論理式]、「引数２」に[論理値：真の場合]、「引数３」に[論理値：偽の場合]を使用します。

引数に数値を使

　　A　　　　B　　　 C　　　　D　　　　E　　　　F　　　　G H　　　　I

1　　　　　国語　　算数　　　合計　AVERAGE関数　　　　　　　　合格基準

2　山田　100　　　80　　　 180　　　90　　　　　　　合計 180　　　以上

3　川田　　　90　　　70　　　 160　　　80　　　　　　　AVERAGE 90　　　以上

問題１．

合計点180以上を合格、それ以外は不合格です。数値と数式で計算しなさい。

山田君の場合

IF(100+80>=180,"合格","不合格")

川田君の場合

IF(90+70>=180,"合格","不合格")

(セル参照で、済む場合は、数値の使用はできるだけ、避けた方が良いです。セル数値に変更が生じた場合、既に出来上がった数式には反映されません。セルは変数の入れ物と考えましょう。)

引数にセル参照

問題２．

上の表で、合計点180以上を合格、それ以外は不合格です。セル参照と数式で計算しなさい。

山田君の場合

IF(B2+C2)>=H2,"合格","不合格")

川田君の場合

IF(B3+C3>=H2,"合格","不合格")

引数に関数を使

問題３．

上の表で、AVERAGE90以上を合格、それ以外は不合格です。セル参照とAVERAGE関数をネストして計算しなさい。

山田君の場合

IF(AVERAGE(B2:C2)>=H3,"合格","不合格")

川田君の場合

IF(AVERAGE(B3:C3)>=H3,"合格","不合格")

引数に名前(セ

セルやセル範囲に名前を付ける

１．セル範囲[B2:C2]に、「山田成績」と名前をつけます。

付ける方法

1. セル範囲[B2:C2]を選択して、名前ボックスに、例えば、山田成績と書き込むだけです。

同様に、セル範囲[B3:C3]に、「川田成績」と名前をつけます。

2. 名前の定義ダイアログボックスで付ける方法

セル範囲[B2:C2]を選択して、挿入(I)メニュー →名前(N)ポイント→定義(D)をクリック→ 「名前の定義ダイアログボックス」で「名前(W)テキストボックス」に名前を入力し、[OK]ボタンをクリックします。

なお、作成する名前は、既定では絶対参照になります。

3. 名前の作成ダイアログボックスで付ける方法(列見出しを含めた場合)

「見出し(国語)」を含め[B1:Bn]を選択して、挿入(I)メニュー →名前(N)ポイント→作成(C)をクリック→「名前の作成」ダイアログボックスには、既に「上端行(T)」にチェックが入っているので、[OK]ボタンをクリックします。名前は「国語」になります。

4. その他、「数式や定数に名前を付ける方法」などがあります。

5. 名前の削除方法

挿入(I)メニュー →名前(N)ポイント→定義(D)をクリック→「名前の定義ダイアログボックス」で削除したい名前を選択し、[削除]ボタンをクリックします。

２．セル[H3]の90を選択して、「基準値」と名前を付けます。

　　A　　　　B　　　 C　　　　　D　　　　　　　　E　　　　　　　F　　　　G H　　　　I

1　　　　　国語　　算数　　　合計のSUM関数　　　AVERAGE関数　　　　　　　　　合格基準

2　山田　100　　　80　　　=SUM(山田成績) 　=AVERAGE(山田成績)　　　合計　 180　　　以上

3　川田　　　90　　　70　　　=SUM(川田成績)　 =AVERAGE(川田成績) 　　 AVERAGE 　基準値　　　以上

すると

山田君の合計点は[=SUM(山田成績)]、アベレージは、[=AVERAGE(山田成績)]で求まります。

川田君も同様です。

IF 関数を使って、合格、不合格の判定をします。

山田君の場合(結果は合格)

=IF(AVERAGE(山田成績)>=基準値,"合格","不合格")

川田君の場合(結果は不合格)

=IF(AVERAGE(川田成績)>=基準値,"合格","不合格")

専ら[IF]関数を使って、７種別(実際は８種別になります。)の会費算出

直接「年齢」列を対象にすると、うまく行きません。そこで、「年齢性別区分」列を作り、この列を対象にして、会費を算定しました。「複数の値を基準にし条件判断をおこなうには、IF 関数を組み合わせます。こちらを参照ください。[IF]文は下図から読み取り願います。

また、[D3]に、「 =IF(C3<74,"一般",IF(C3<=87,"老人",IF(C3<=99,"高齢","100以上"))) 」を

また、[E3]に、「 =IF(B3="女性",B3&D3,D3) 」が埋め込まれています。

[IF]関数に[AND]関数をネストして会費算出

条件に「性別」「年齢」「区分」と分類されていますが、「AND」関数の場合、３０個の条件が設定できます。

[IF-AND]文は下図から読み取り願います。他のところに埋め込み書式はありません。また、特に年齢順に揃える必要はありません。自由です。

[VLOOKUP] 関数を使って会費算出

IF関数のみより、大幅に、書式が簡単になります。引数は従来のスタイルとは異なります。

検索値(引数1),範囲(引数2),列番号(引数3),検索の型(引数4)

書式：検索値,範囲,列番号,検索の型

下表をコピーして[A1]セルに貼り付けお試しください。また、年齢と性別を変えてみてください。

なお、[D4]に、「=IF(C4<74,"一般",IF(C4<=87,"老人",IF(C4<=99,"高齢","100以上")))」

また、[E4]に、「=IF(B4="女性",B4&D4,D4)」が、埋め込まれています。「B4&D4」で使用の「文字列演算子 &(アンバサンド)」は、２つのセルの文字列を結合、または連結して、1 つの連続する文字列の値を作成しています。

注意点：参照表は、昇順に並べ替えしておくこと。

VLOOKUP関数の使用法については、こちらを参照ください。

VLOOKUP関数　　=VLOOKUP($D$5:$D$18,$F$5:$G$12,2,FALSE)

都区・茨城県人会年会費

名前

性別

年齢

年齢区分

年齢性別区分

年会費

参照1列目

参照２金額

山田太郎

男性

一般

10000

女性100以上

3000

西野　明

男性

一般

10000

100以上

5000

南野　茜

女性

一般

女性一般

8000

女性高齢

5000

中田夏男

男性

一般

10000

女性老人

6000

東野　肇

男性

一般

10000

高齢

7000

地脇智子

女性

一般

女性一般

8000

老人

8000

下田秋子

女性

一般

女性一般

8000

女性一般

8000

草田一郎

男性

老人

8000

一般

10000

北野　文

男性

老人

8000

木田一美

女性

老人

女性老人

6000

川田花子

女性

高齢

女性高齢

5000

上田照夫

男性

高齢

7000

天野和雄

男性

101

100以上

5000

人見ゆり

女性

102

100以上

女性100以上

3000

引数に、配列（

配列数式：行方向や列方向に連続した値を一纏めしたデータを参照し、配列に含まれる値ごと（複数の）の計算結果を配列範囲（戻り値のセル範囲）に返す数式のことです。複数の計算を行い、1 つまたは複数の結果を返す数式で、計算を1つの数式で行うことができます。

配列数式については、こちらを参照ください。

本ページ上段の

エラー値８種類を直接引数として使う例は見当たりません。エラー値は戻り値として現れ、その現れたセル、または、データが未入力のため当然エラー表示が予想されるセルを参照して、非表示の対策を講じているのです。

エラー値の解消法は、こちらを参照ください。

続きます。

トップへ戻る　総目次へ戻る

関数の勉強１へ戻る　いろいろな関数総目次へ　関数の勉強３へ進む

Excel関数の勉強３

文字列操作関

ワークシート関数全部で33種類ありますが、同じ関数名の末尾に「B(バイトの意味)」の付いたものが７種程ありまして、戻り値が「全角文字も、半角文字」も１字扱いか、「全角は２バイト、半角は１バイト」扱いにするだけの違いの、同じタイプになりますので、種類としては、２６種類と言うことができます。

この中の代表的なのが(一寸異色かな？)[TEXT] 関数で、多用されています。[TEXT] 関数は数値を指定した表示形式で文字列に表現する関数です。

他の文字列操作関数は、文字列から必要な文字を抽出したり、文字列の長さを調べたり、くっつけたり、不必要部分を切り取ったり、または、大文字と小文字を切り替えたりするなど、文字どおり文字列の操作をする関数です。

指定した文字を抜き出す関数に、[RIGHT] 関数、[LEFT] 関数、[MID] 関数があります。

RIGHT 関数

書式「 RIGHT(B1,n) 」

文字列の右端から[n文字]を抜き出します(B1は文字列セルの座標)

LEFT 関数

書式「 LEFT(B2,n) 」

文字列の左端から[n文字]を抜き出します(B2は文字列セルの座標)

MID 関数

書式「 MID(B3,N,n) 」

文字列の左端から数え[N]番目から、ｎ個の文字を抜き出します

下図をご覧ください。[B]と[C]列に、ご覧の表が作成されています。これを、[RIGHT],[LEFT]関数を利用して、分類して合計を算出したものです。

前回は、少し表の造りが違っていますが[MOD] 関数で「奇数行」と「偶数行」の違いで処理しました。（MOD 関数を参照ください。）これを、[RIGHT]と[LEFT] 関数を[IF] 関数にネストして解決した例です。

解説

「男子」と「女子」文字列の右から２文字ですので、[RIGHT]関数で、[B3]セルのｎは２(文字)になります。

「小学」「中学」は文字列の左から２文字ですので、[LEFT]関数で、[B3]セルのｎは２(文字)になります。

夫々を、[IF] 　関数にネストすると

「 =IF(RIGHT(B3,2)="男子",C3,"") 」又は、「 =IF(RIGHT(B3:B24,2)="男子",C3:C24,"") 」

「 =IF(LEFT(B3,2)="小学",C3,"") 」又は、「 =IF(LEFT(B3:B24,2)="小学",C3:C24,"") 」

H列は余禄で、「 =IF(B3="小学生男子",C3,"") 」になります。このように、個別に抜き出せますので詳細な分析が可能になります。

検索と置換の

FIND 関数

書式：「 FIND(検索文字列,(検索対象を含む)文字列,開始位置(数値)) 」

条件にあてはまる文字の位置を探します。

数式パレットの注釈：条件にあてはまる文字の位置を返します。大文字小文字は区別されます。

検索文字列には検索したい文字列を指定します。ワイルドカードは使用できません。

数式例「 =FIND("文字列",A5,20) 」

文中から「小文字」の位置を探します。検索開始位置を「読点」の次の[20]からにしました。戻り値は[23]文字目(左から数えて)になりました。下記の[SEARCH] 関数と変わりません。「条件にあてはまる文字の位置」と「指定した文字の位置」と、どのように、ニュアンスが異なるのか、私には分かりません。ただ、こちらのような使い道があります。

SEARCH 関数

書式：「 SEARCH(検索文字列,(検索対象を含む)文字列,開始位置(数値) 」

文字列の中から指定した文字の位置を検索します。

数式パレットの注釈：指定した文字の位置を返えします。

大文字、小文字は区別されません。半角、全角の区別なく１文字を１とします。

検索文字列には検索したい文字を指定します。　とあります。

数式例：「 =SEARCH("Ｄ",B1,1) 」

[SEARCHB] 関数と一緒に比較すると分かり易いです。下図をご覧ください。

1. [SEARCH] 関数の場合、 [c(半角)]の次の[Ｄ(全角)]を検索したところ、[4]と返ってきました。

2. [SEARCHB] 関数の場合、[Ｃ(全角)]の次の[d(半角)]を検索したところ、[5]と返ってきました。

3. (１)の場合、半角[c]も１文字として数えています。

4. (２)の場合、全角[Ｃ]は２バイト文字のため、[d]は[5]バイト目ということになります。

5. なお、ワイルドカード[*]＆[?]使用可になっています。

ワイルドカードとは

[?]と[*]の文字を使って「検索」することを言います。[スタート]→[検索]→[フアイル名のすべて、または一部]欄に例えば[検?]や[検*]を入力すると検の付く「検索○○○○」「検定○○○」のフアイルやフォルダを探してくれます。名前の全部を忘れたときに使います。

「*」は複数の文字を「?」は一文字の代用をします。編集メニュー[検索と置換]で　

「槍ヶ岳と２字の焼岳とも１つの聖岳と穂高岳」

を[?]を2個使った[??岳]で検索し、「３文字」に置換し色付けした例です。

焼岳の前の「の」と聖岳の前の「の」が「３文字」に含まれました。(Wordでも同じ結果になります。)

なお、詳しくは、こちらを参照ください。

[FIND]関数と[SEARCH]関数の違い(EXCELヘルプより)

FIND 関数では、

指定された文字列 (検索文字列) を他の文字列 (対象) の中で検索し、その文字列が他の文字列内で最初に現れる位置を左端から数え、その番号を返します。

SEARCH 関数と同じような働きをしますが、FIND 関数では英字の大文字と小文字を区別できる代わりに、ワイルドカード文字を使用することができません。(SEARCH 関数は、この逆)

SEARCH 関数は、開始位置を先頭にして、

指定された文字列 (検索文字列) をほかの文字列 (対象) の中で検索し、その文字列が最初に現れる位置の文字番号を返します。

SEARCH 関数を使用すると、ある文字列に含まれる特定の文字列の位置を調べることができ、さらに MID 関数や REPLACE 関数と組み合わせて、その文字列を置き換えることができます。

SEARCH 関数では、半角と全角の区別なく 1 文字を 1 として処理が行われます。

SEARCH 関数でワイルドカードを使用して実験しました。

最初の[Ａ]は全角、次の[A]は半角大文字、eの次は全角スペース、都の次は半角スペース、茨城県の後は全角スペースにしてあります。

千葉県の「千」の文字の位置が[16]文字目と表示されました。

SUBSTITUTE

書式：「 SUBSTITUTE(文字列,検索文字列,置換文字列,置換対象(何番目の同名文字かを数値表示)) 」

指定した文字列を別な文字列に置換します。

数式パレットの注釈：SUBSTITUTE(文字列,検索文字列,置換文字列,置換対象)

文字列中の指定した文字を新しい文字で置き換えた結果を返します。

文字列には文字列、または置き換えたい文字列が入力したセルに対する参照を指定します。

置換対象には＜文字列＞に含まれるどの＜検索文字列＞を置き換えるのかを表す数値を指定します。

数式例：「 =SUBSTITUTE(A2,"-","",2) 」

置換例１(関数使用)

某機器メーカーが、マイナーモデルチエンジを識別するため、年式型番の第２番目の[-]をカットしたとします。

年式型番　　　新型名　　　　　製　品　名

05-XA-01　　　　　　　　　05年式○○○1月製品

05-XB-03 05年式△△△3月製品

05-XC-05　　　　　　　　　05年式◇◇◇5月製品

05-XD-07　　　　　　　　　05年式●●●7月製品

05-XE-09　　　　　　　　　05年式▲▲▲9月製品

05-XF-11　　　　　　　　　05年式◆◆◆11月発売予定

数式パレットの２番目で[-]を指定し、３番目で[文字無し]を指定し(何らかの文字を入れればその文字に置換されます)、４番目で[左から数えて２番目の-]を置換対象にしています。

後は、[B2]セルをオートフィル機能で、ドラッグして完成です。

「置換機能は Word のもあります(下記例ーExcel[編集]メニュー →[置換]も同じこと)が、この関数使用例のように第１[-]と第２[-]の区別はできないようで、全部一緒に消されてしまいます。「Word の機能には無い置換」と言うことができます。

置換例２(Word の置換)

市町村合併で、仮に「利根町」が「龍ヶ崎市」に編入合併をしたときのことを考えます。

当然、「町立小中学校名」が「市立小中学校名」に変わります。市の電子例規集(町の電子例規集写し部分)の名称を例えば[Word] の「置換」を使用して変えるところもあろうかと思います。「町立施設」は、余り無いので、他への影響は少ないことと考えられます。

利根町保健所

利根町立文小学校

利根町立布川小学校

利根町立文間小学校

利根町立東文間小学校

利根町立太子堂小学校

利根町立利根中学校

利根町立新館中学校

利根町教育委員会

↓

利根町保健所

龍ヶ崎市立文小学校

龍ヶ崎市立布川小学校

龍ヶ崎市立文間小学校

龍ヶ崎市立東文間小学校

龍ヶ崎市立太子堂小学校

龍ヶ崎市立利根中学校

龍ヶ崎市立新館中学校

利根町教育委員会

REPLACE 関数

書式：「 REPLAE(文字列,開始位置,文字数,置換(に使われる)文字列) 」

文字列の中の指定された文字範囲を別の文字列に置換します。

注釈：文字列中の指定した位置の文字列を置き換えた結果を返します。半角と全角の区別なく、１文字を１として処理します。

文字列には置き換えたい文字列が含まれる文字列を指定します。

例１

文中の第２のウインドウズをWindowsに直しなさい。

例文：ウインドウズは進化しています。現在はウインドウズXPが主流になりつつあります。

数式例：「 =REPLACE(A1,19,6,"Windows") 」

結果：ウインドウズは進化しています。現在はWindowsXPが主流になりつつあります。

これは、いちいち第２の[ウ]までの文字数を数えてから行った方法です。これが数十、数百字はなれていると大変です。そんな時、[FIND]関数をネストして(何字目かを取得するため)使ってみました。結果がうまく行きましたので、紹介いたします。

[REPLACE] 関

数式：「 =REPLACE(A1,FIND("ウインドウズ",A1,10),6,"Windows") 」

例文:下の文章をExcelの[A1]セルにはりつけて、適当な、別のセルをアクテイブにして、数式バーに、書式を貼り付けてみてください。下記赤文字部分が「Windows」になります。

「ウインドウズで使うワイルドカードとは、[スタート]→[検索]→[フアイル名のすべて、または一部]欄に例えば[検?]や[検*]を入力すると検の付く「検索○○○○」「検定○○○」のフアイルやフォルダを探してくれます。名前の全部を忘れたときに使います。私は現在ウインドウズXPを快適に使用しています。」

解説

REPLACE 関数の「文字列」は[A1]セルです。

「開始位置」は

前項の例１の書式では「 =REPLACE(A1,19,6,"Windows") 」の[19]でした。

今回のは[19]番目の[19]の替りに

FIND("ウインドウズ",A1,10)を使用して、お目当ての第２「ウインドウズ」の位置を検索しております。

ラストの[10]は最初の「ウインドウズ」を避けるため１０字以降を検索の対象としました。

「書き換える文字数」は「ウインドウズ」の[6] 文字です。

「置換(に使われる)文字列)」は「Windows」で、文字列をダブルコーテイションで囲みます

ところがです、「 FIND("ウインドウズ",A1,10) 」に替えて、同じ「引数」を持つ「 SEARCH("ウインドウズ",A1,10) 」を使用しても、全く同じ結果になりました。

ますます、[FIND]と[SEARCH]の違いが分からなくなりました。

続きがあります。「 =REPLACE(A1,FIND("ウインドウズ",A1,10),6,"Windows") 」で、うまくできたのですが、 [SUBSTITUTE] 関数を使って「 =SUBSTITUTE(A1,"ウインドウズ","Windows",2) 」を試したところ、全く同様な結果になりました。「FIND 関数」をわざわざネストする必要がない分、こちらが本命だったのでした。ラストの[2]を[1]に替えると、最初の「ウインドウズ」のみが「Windows」に変換されます。

もう一度整理して、「検索と置換」の４つの関数を表示します。

指定する「文字列」の「位置」を探すには

FIND関数　　　：「 =FIND(検索文字列,(検索対象を含む)文字列,開始位置(数値)) 」

SEARCH関数　　：「 =SEARCH(検索文字列,(検索対象を含む)文字列,開始位置(数値) 」

指定する「文字列」を、他の文字列に「置換」するには

SUBSTITUTE関数：「 =SUBSTITUTE(文字列,検索文字列,置換文字列,置換対象(何番目の同名文字かを数値表示)) 」

REPLACE関数　：「 =REPLACE(文字列,開始位置,文字数,置換(に使われる)文字列) 」

文字種を変換

>ASC 関数

書式：「 ASC(文字列) 」

注釈：＜文字列＞内の全角(2バイト)の英数カナ文字を、半角(1バイト)の英数カナ文字に変換します。

文字列には全角の英数カナ文字を含む文字列を指定します。

JIS 関数

書式：「 JIS(文字列) 」

注釈：＜文字列＞に含まれる半角(1バイト)の英数カナ文字を、全角(2バイト)の英数カナ文字に変換します。

文字列には半角の英数カナ文字を含む文字列を指定します。

どちらも、英字,数字,カナ,記号,スペースなどに有効です。但し、全角文字の漢字とひらがなはそのまま返されます。

UPPER 関数

書式：「 UPPER(文字列) 」

注釈：文字列を大文字に変換した結果を返します。

文字列には大文字に変換したい文字列を指定します。

使用例：「 =UPPER(A2) 」セル A2 に入力されている英字を全て大文字に変換します。

LOWER 関数

書式：「 LOWER(文字列) 」

注釈：文字列の大文字を小文字に変換した結果を返します。

文字列には小文字に変換したい文字列を指定します。

使用例：「 =LOWER(A3) 」セル A3 に入力されている英字を全て小文字に変換します

LEN 関数

書式：「 LEN(文字列) 」

注釈：文字列(調べたい)の長さ(文字数)を返します。半角と全角の区別なく、１文字を１として処理します。

文字列には長さを求めたい文字列を指定します。(なお、空白も文字の内に入ります。)

CONCATENAT

書式：「 CONCATENATE(文字列1,文字列2,文字列n) 」

「コンカティネイト」と読みます。

注釈：引数に指定した文字列をすべてつなげた文字列を返します。

文字列1:文字列1,文字列2,・・・には、1つにまとめたい文字列を指定します。引数は１から30個まで指定できます。

１枚文書の文字列の「つなぎ」なら、その都度[&](アンバサンド)を用いて手作業でつなげますが、テンプレート化した書式の場合、あちこちの抜き出し、くっつけには関数を用います。

使用例

あいうえお

TRIM 関数

書式：「 TRIM(文字列) 」

注釈：指定した文字列から不要なスペースを削除した結果を返します。

文字列には不要なスペースを削除したい文字列を指定します。

TRIMは写真用語のトリミングのことでしょうか。文書中の不必要なスペースをカットします。

字下げ用の先頭の空白はすべて削除されます。但し、文字間に連続して空白があった場合１個は残します。

YEN 関数

書式：「 YEN(数値,桁数) 」

注釈：＜数値＞を＜桁数＞まで四捨五入して、円マークを付けた文字列に変換します。

数値には数値、数値を含むセルの参照、または結果が数値となる数式を指定します。

桁数には、小数点以下の桁数を指定します。(指定が無ければ、[０]と見做します。)

円マーク[\]と四捨五入は、「書式設定」ツールバーの「通貨スタイル」と「小数点桁下げ」からでもできますが、例えば関数を多く用いた請求書書式などに用いるとき、抜け落ちがなくて済みます。

続きます。

トップへ戻る　総目次へ戻る　関数の勉強２へ戻る

　関数の勉強４へ進む　いろいろな関数総目次へ戻る

Excel関数の勉強４

文字列操作関

[TEXT] 関数

書式　「 TEXT(値,表示形式) 」

数値を書式設定した文字列に変換する関数です。

数式パレット-注釈　：数値に指定した書式を設定し、文字列に変換した結果を返します。

値-注釈　　　　　　：値には数値、結果が数値となる数式、または数値が入力されているセルへの参照を指定します。

表示形式-注釈　　　：表示形式には[表示形式]ダイアログボックスに表示されている数値形式を、文字列として指定します。

補記　　　　　　　：[書式]→[セル] コマンドの [表示形式] タブ→[分類] ボックスに表示される数値書式を、半角の二重引用符 (") で囲んで指定します。

解説(MS-ヘルプより)

表示形式にアスタリスク (*) を使用することはできません。

[表示形式]タブのオプションを使用し、数値を含むセルに数値書式を設定しても、表示が変わるだけで、文字列には変換されません。

TEXT 関数を使用すると、数値は書式設定された文字列に変換され、その計算結果は数値として計算に利用できなくなります

補記１：但し、数値として変換できる形式のものは演算が可能です。

例えば、[']付き全角数字、文字列書式全角数字でも、演算ができました。

補記２：年月日の[2005/9/13]を直接引数として使用する場合は、半角の二重引用符 (") で囲む必要があります。

「 =TEXT(2005/9/13,"yyyy年m月d日") 」の場合は、1900年1月1日の日付表示になります。

「 =TEXT("2005/9/13","yyyy年m月d日") 」にすると、2005年9月13日 (セル参照の場合は、そのまま参照することができます。)

値について

数値：「1234567.89」「-123.045」「0.0123」などは当然数値です。

また、[2005/9/13]で表示される「シリアル値」も数値です。「2005年9月13日」は[38608]という数値です。

結果が数値となる数式：[WEEKDAY]関数では、設定にもよりますが、日曜は１、月曜は２、・・・土曜は７

のように、数値で返されます

そこで、「 =TEXT(WEEKDAY("2005/9/13",1),"aaaa") 」と入力すると、「火曜日」と返ってきます。

[WEEKDAY("2005/9/13",1)]の部分が数値の[3]になるからです。

「表示形式」の["aaaa"]で「火曜日」と表示される訳です。

数値が入力されているセルへの参照を指定：上記で[A1]セルに「2005/9/13」が入力されていれば、

セル参照で「 =TEXT(WEEKDAY(A1,1),"aaaa") 」でも良いことになります。

わざわざ、[WEEKDAY]関数を使わずとも、「 =TEXT(A1,"aaaa") 」でも、「火曜日」と返ってきます。

その他考えられるものに、数学・三角関数の戻り値、例えば、万年暦で使用した[MOD]関数、円周率を求める[PI]等々有りますが、「日付けのシリアル値」と「数値」が主な対象になるのではないでしょうか。

「日付･時刻のシリアル値」については、年月日・時刻のいろいろな表示法(下段)

「数値」のいろいろな処理については、下記または、こちらのページを参照ください。

表示形式について

1. メニューバー[書式]⇒[セル]⇒（セル書式設定ダイアログ）で⇒[表示形式]タブをクリック、ここの種類一覧で間に合わないときには、末尾の「ユーザー定義」で設定を行います。

（１）の種類一覧の図

(1)表示形式を「文字列」で設定すると、唯一、数値ではなくなります。表示位置も「左揃い」で表示されます。他は全て「数値表示」なので、計算式にそのまま使用できます。

(2)分数の表示ですが、「1/4」と入力すると「1月4日」になりますので「0 1/4」で記入しました。

(3)指数欄の数式の表示には、['](アポストロフィー)を頭に付けました。従って左寄せに表示されています。なお、指数のベキ乗根は、「 =8^(1/3) 」のように記述します。答えは[2]と表示されます。

2. ⇒（分類（C）で）[ユーザー定義]をクリック⇒【ユーザー定義書式設定ダイアログ】（種類（T）の一覧表の中から適当な表示例を探し、加工します。　下図のセル書式設定ー表示形式の図参照

3. これらは全て「半角のダブルクォーテーション ["] 」で囲んで「関数の数式」に組み込みます。

4. また、ダブルクォーテーションは、戻り値が文字列であることを表しています。

（２）のユーザー定義の書式設定

「ユーザー定義の表示形式」の作成について

使用する書式が組み込みの表示形式にない場合は、書式を組み合わせてユーザー定義の表示形式を作成することができます。

作成された表示形式はブックのすべてのシートで使用することができます。

新規ブックの初期状態では、組み込み表示形式だけが使用できます。

ユーザー定義の表示形式を作成するには、決められた書式記号を使う必要があります。書式記号には、数値、日付や時刻、通貨・パーセンテージ・指数、文字列やスペースなどがあります。　

1. 書式を設定するセル範囲を選択します。

2. [書式] メニューの [セル] をクリックし、[1.表示形式] タブをクリックします。

3. [分類(C)] ボックスの一覧で最下部の [2.ユーザー定義] をクリックします。

4. 右側に[種類(T)] ボックスが現れます。

下段のリストボックスには、「0.00」や「#,##0.00」などといった書式記号・基本パターンの一覧が用意されております。

5. この一覧で目的の表示形式に「最も近い表示形式」をクリックすると、

中段の「3.記入ボックス」に入ります。（「最も近い表示形式」とはあいまいな表現ですが、下表「小数点以下の桁と有効桁数」を参考に）

6. 上段の「4.サンプル欄」に「或る形式を持った数値」表示されます。

7. 「3.記入ボックス」で、一覧から選択した表示形式を編集して「新しい表示形式を作成」します。

8. 上段の「4.サンプル欄」に「目的の形式を持った数値」が表示されたら[5.ＯＫ]ボタンです。

9. すると、[種類(T)] ボックスの末尾（6）に、新しい「ユーザー定義の表示形式」が追加されます。

削除は、削除したい記号を選択（アクッティブ化）して[削除]ボタンです。

注）ユーザー定義の表示形式の登録は、使用可能なメモリに依存します。従って、登録数は必要最小限にとどめましょう。（予め組み込まれた表示形式を編集しても、その表示形式が削除されることはありません。）

セル書式設定ー表示形式の図

表示形式の作成

ユーザー定義の書式記号は、一つの表示形式で 3 つまでの数値の書式と、文字列用に 4 つ目の書式を持つことができます。

各書式は次のようにセミコロン (;) で区切って指定します。

例 : #,###.00;[赤](#,###.00);0.00;"データ : "@

セミコロンで区切った 4 つの書式はそれぞれ <整の数>、<負の数>、<ゼロ>、<文字列> に対応しています。

4つすべてを定義した表示形式を作成する場合は、この構成順に作成する必要がありますが、一般的には１つか２つ程度と考えられます。

2 つのセクションだけを指定した場合、最初のセクションは正の数とゼロの表示形式になり、 2 番目のセクションは負の数の表示形式になります。

1 つのセクションを指定した場合は、その表示形式がすべての数値に適用されます。

セクションを省略する場合は、そのセクションの後ろのセミコロンだけを入力します。

４セクションを指定した例

　　　　　　　　表示形式　　　　　　　　　　　　　　　入力　　　　　　表示

　　　　------------------------------------------------------------------------

　　　　#,###.00;[赤]-#,###.00;0.00;" データ "@　　　 123 123.00

　　　　　　　　　　　　　　　　　　　　　　　　　　　-123 -123.00

　　　　　　　　　　　　　　　　　　　　　　　　　　　　 0 0.00

　　　　　　　　　　　　　　　　　　　　　　　　　　　 3月　　　3月

上記実際の設定は「 #,###.00;[赤]#,###.00;0.00;""@ 」です。[@]を付けないと入力データが表示されません。

「ユーザー定義書式記号」の見方

0を使って位取りを表示する。

0.0で、コンマ以下１桁まで表示します。（下表参照）小数点の両側に表示する桁数を指定します。

#を使って位取りを表示する。(#は井桁の桁です。）

) 小数部に表示する桁数を指定します。

書式[#,###]は整数部の1000の位ごとにカンマを表示します。

0 を入力したら 0 と表示し、さらに桁区切りのカンマも表示するには、[#,##0]という書式を使用します。

([#,##0]と末尾を[0]にするのは、0 を入力した時に 0 を表示させるためです。0 を表示しない場合は[#,###]とします。)

上は図です。コピー用の表は、こちらに有ります。

なお詳しくは、こちらのページを参照ください。

後半に「書式記号を使用してユーザー定義の表示形式を作成する」があります。

[値]が[シリアル値]の場合

シリアル値とは1900年1月1日を１と定め、その日からの連続日数と時刻を表す値のことです。

　　　１日　は　　1　　　　　　に決めてあります。

　　　１時間は　　0.041666667

　　　３０分は　　0.020833333

　　　１分　は　　0.000694444

　　　１秒　は　　0.000011574

2005/9/13 日は　　38608　　　　1900年1月1日の[1]から数えて[38608]日目になります。

日付関係及び曜日の表示法

＜表２＞

=TEXT(シリアル値,"表示形式")　　　　表示結果

=TEXT(シリアル値,"yy")　　　　　　西暦の下２桁（00～99）

=TEXT(シリアル値,"yyyy")　　　　　西暦 (1900～9999)

=TEXT(シリアル値,"e") 和暦 (1～99)

=TEXT(シリアル値,"ee")　　　　　　和暦 (01～99)

=TEXT(シリアル値,"g") 元号略号（M・T・S・H）

=TEXT(シリアル値,"gg")　　　　　　元号略号（明・大・昭・平）

=TEXT(シリアル値,"ggg") 元号（明治・大正・昭和・平成）

=TEXT(シリアル値,"m") 月　 (1～12）

=TEXT(シリアル値,"mm")　　　　　　月 (01～12）

=TEXT(シリアル値,"mmm") 　月　 (Jan～Dec)

=TEXT(シリアル値,"mmmm")　　　　　月 (January～December)

=TEXT(シリアル値,"d") 日　 (1～31)

=TEXT(シリアル値,"dd")　　　　　　日 (01～31）

=TEXT(シリアル値,"ddd") 曜日 (Sun～Sat)

=TEXT(シリアル値,"dddd")　　　　　曜日（Sanday～Saturday）

=TEXT(シリアル値,"aaa") 曜日 (日～土）

=TEXT(シリアル値,"aaaa")　　　　　曜日 (日曜日～土曜日)

時刻の表示法

TEXT関数を使って、文字列に変換したものは、計算に使うことができなくなります。

そこで、[VALUE] 関数で「数値を表す文字列を数値に変換」することができます。これですと、計算に使用することができます。

VALUE 関数

書式：「 VALUE(文字列) 」

注釈：文字列として入力されている数字を数値に変換した結果を返します。

文字列-注釈：文字列には文字列をダブルクォーテーションマークで囲んで指定するか、または変換したい文字列が入力したセルへの参照を指定します。

MS-解説　　：文字列には、Excel が認識できる数値、日付、または時刻を指定してください。それ以外の値を指定すると、エラー値 #VALUE! が返されます。

Excel では、数式中の値は必要に応じて自動的に変換されるため、通常は数式の中で VALUE 関数を使用する必要はありません。VALUE 関数は、他の表計算アプリケーションとの互換性を維持するために用意されている関数です。

補記　　　：なお、裏技的ですが、「 =セル番地*1 」と１を掛けることによって、数値データに戻す方法もあります。

TEXT 関数の使用例

例題１

セル[G4]に[TODAY]関数で日付けを取得(2005/9/13)しておいて、それを「元号年月日」で表示する。

「 =TEXT(TODAY(),"ggge年m月d日") 」で、平成17年9月13日(入力当日)と表示されます。(gggでも可)

例題２

セル[G4]に[TODAY]関数で日付けを取得(2005/9/13)しておいて、それを「西暦年月日」で表示し、曜日を付ける

「 =TEXT(TODAY(),"yyyy年m月d日(aaa)") 」で、2005年9月13日(火)と表示されます。

例題３

下段[F16]にある合計数値を、上段[B3]の「請求金額」の右セル[B4]に「一金○○円也」と表示する。

[B4]セルをアクティブにして「 =TEXT(F16,"一金00円也") 」と入力する。

例題４

9月12日まで、長期休暇、9月13日以降から出勤を関数で表示しました。([IF]関数に[TEXT]関数をネストして)

関数表示の良いところは、いちいち「表示形式」を開かずとも、１行1回で表現できるので、オートフィル機能が使えることにあります。現実にそぐわない例ですが、こんなことができますの例です。(38608は 2005年9月13日のシリアル値です。、)

続きます。

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

　いろいろな関数総目次へ戻る　 EXCEL関数５へ進む

EXCEL関数の勉強５

住所録・年賀

１．WORDで住

WinXPの場合

1. Wordを起動します。

2. [フアイル(Ｆ)]メニュー[新規作成(Ｎ)]をクリックします。

3. 右側の作業ウインドウから、「このコンピュータ上のテンプレート」をクリックします。

4. 現れた「テンプレート」ダイアログから、[差し込み印刷]タブをクリックします。

5. アドレス帳１～３から、何れかを選択します。

6. 作業ウインドウで、「新しいリストの入力」にチエックを入れ、作成ボタンをクリックします。

下図は「エントリー１～４」の例です。

7. 記入が終わったら、My Documents 内 My Data Sourcesに保存してください。

但し、ファイルの種類: Access database file で、保存形式は「Microsoft Office アドレス(*.mdb)」に限られます。このドキュメントは、Microsoft Access のデータベースです。Accessでないと開けないようなので、編集に難儀しそうです。

下図は、その一覧表です。(未だ数行ですが)

マイクロソフトWordの住所録テンプレートは、下記のスタイルになっていることが分かりました。

２．EXCELで一

下の図は、上記テンプレートに従って「姓」と「名」を分離して、EXCELで作りました。(但し、会社関係はカット)

作成手順

1. [姓]と[名]に名前を入力します。そうして、「ふりがな」を付けてみましょう。(会社、お役所では重要な作業です。)

2. [姓]を選択します。

3. [書式]メニュー →[ふりがな]→[設定]をクリックします。(まだ、この段階ではふりがなは表示されません。)

4. 再度[書式]メニュー →[ふりがな]→[表示/非表示]をクリックするか[編集]をクリックすると表示されます。

私のWinXPの場合[姓]と[名]別々に行いました。

5. 最初の「山田太郎」にふりがなが付いたら、「山田太郎」を選択して、太郎の右下の「フィルハンドル」で必要な行数にコピーします。(ふりがな付きの「山田太郎」のコピーが沢山できました。)

6. 折角コピーした分を[Del]キーで消します。文字は消えても書式は残るのです。

7. 氏名欄に次々と名前を入力します。終ったら、[書式]メニュー →[ふりがな]→[表示/非表示]をクリックすると一発で表示されます。

8. 姓名欄にふりがなが付いたら、未だ空欄の山田太郎の右欄[D4]に関数PHONETICを

「 =PHONETIC(B4:C4) 」の形で埋め込みます。数式バーに直記入でも、また、数式バー[fx]をクリックして、関数を選択し、範囲を[B4:C4]とドラッグしてもできます。(WinME の場合は書式設定ツールバーの[fx]ボタンから)

9. [D4]セルに「ふりがな」ができたら、オートフィル機能で、下にドラッグすると全部に付きます。

10. 姓名欄のふりがをの削除します。方法は、[書式]メニュー →[ふりがな]→[表示/非表示]をクリックすると削除されます。

11. 郵便番号を「半角数字で入力します。(通常は数字は自動的に「半角化」しますが[ー]が付くと全角のまま表示されてしまうので、言語バーを「直接入力」に切り替えます。

12. 住所記入欄で空白になっている[F4]から[F13]まで選択します。

13. [Ctrl]キーを押しながら[R]きーを押します。意味は、左の文字列を右にコピーしなさい。です。

([E列]と[F列]が全く同じになりました。)

14. 言語バー(IME)の「般」をクリックして、「人名/地名」に切り替えます。

15. 次に、１行ごとに郵便番号を選択して、スペースキーの右の「変換」キーを押して住所を選択します。

住所禄の並べ替え

[データ(D)]メニュー →[並べ替え(S)]で下記が選択できます。大人数の場合に威力を発揮します。

１．５０音順　２．郵便番号順（＝県名順になります。）３．元に戻しかったらNo 順です。

オートフィルタで「選択範囲」をしぼる」

若し、役所、銀行、企業等で、「性別、年齢、生年月日等個人情報満載のデータでしたら、絶対に必要になります。

項目欄を選択し、[データ(D)]メニュー →[フィルタ(F)]→[オートフィルタ(F)]を選択すると、選択した１つ項目または、全項目にボタンがつきます。

特に、備考欄に、級友、趣味友達、親戚などを記入しておくと、ふるいにかけることができて便利です。「フィルタ」の使用法はこちらをご覧ください。

３．年賀状用の

上記住所録を訂正して使用する

姓名の[ふりがな]を[書式]メニュー →[ふりがな]→[表示/非表示]をクリックして、カットします。

姓と名が別々のセルに有っても差し支えありません。姓名の間にカーソルを置いて[Del]か[Backspace]キーで縮められます。気になったら下記方法で同一セルに纏めます。

姓名の右側に、列を挿入し、２列に分かれた姓と名を「 =CONCATENATE(姓,名) 」で一つのセル内に纏め、２列表示を削除します。

テンプレートを利用する。

フォルダから開く

(前述による方法がありまあすが）WindowsXPでは：[マイドキュメント]→[My Data Sources] フォルダ内の、[Address.xls]を開くと下記雛形が現れます。

(Word2000では、ファイル(F)]メニューから[新規作成]で、[その他の文書]の中の[Address.xls]からになります。)

この様式の必要欄に書き込み住所録を完成させておきます。

作成した住所録

[CSV 形式]で保存しても、[xls形式]保存と同等の働きをします。

CSV形式にする場合には「フアイルの種類」で「 CSV(カンマ区切り)(*.csv) 」を選択します。保存したフアイルを開くとき、開くアプリケーションを指定しないと、開けません。この場合EXCELを指定します。

(EXCELで作った[CSV 形式]住所録は、他の市販年賀ソフトでも使えます。現に私はＭＳ以外の年賀ソフト２～３で利用しました。)

はがき宛名印刷

Wordの「はがき宛名印刷ウイザード」で作る

Word[ツール(T)]メニュー →[はがきと差込印刷(E)]→[はがき印刷(J)]「宛名/文面の選択」で宛名面の作成をクリックすると、「はがき宛名印刷ウイザード」が起動します。用法については、こちらを参照ください。

Word2002-2003の場合、簡素化されて、ウイザードも短くなり、１分位で終了します。失敗してもすぐやり直せます。

注意すべき点は、ウイザードの「差込み印刷を指定します」で、「他の住所録フアイル」にチエックします。参照で、作成した住所録フアイルを選択します。これで、表示される筈です。

その他

No、ふりがな、性別などの情報記入

上記雛形に[No],[氏名(半角スペース入れ)],[連名],[敬称],[ふりがな],[性別]・・・の順で記入すると、あとあと「並べ替え」や整理に便利です。

多人数の住所録を作成する場合には、入力規則で、各列ごとに全角文字使用か、半角英数使用するかを設定しておくと、能率がアップします。

作例です。

[D2]は関数[ =CONCATENATE(B2,C2) ]で、氏名を一つのセルにまとめました。「ふりがな」は、[D2]からは取れません。 [B2][C2]から上記の手段で取り入れます。

差出人電話番号は、ウイザード差出人記入欄で漢数字で入力し、編集で、記号から電話機を選んで頭に付けました。横書きを回避するためです。

各枠線は、テキストボックスですので、自由に編集できます。ただ、差出人電話番号には苦労させられます。「差出人欄は、テキストボックス３個を使った方が良さそうです。」何故か、連名が表示されません。有料のソフトを使いなさいの意味かも知れません？。それなら、それで、テキストボックスで入れる手段がありますが。

IF関数に[HOUR

その前に、[FLOOR] 関数についての予備知識が必要です。

FLOOR 関数

書式：「 FLOOR(数値,基準値) 」

数式パレット注釈：指定した数値よりも０に近い数値に丸めて値を返します。

数値注釈　　　　：数値には丸めたい数値を指定します。

基準値-注釈：基準値には計算の最小単位となる数値を指定します。

MS-ヘルプより

指定された基準値の倍数のうち、最も近い値に数値を切り捨てます。

基準値倍数の基準となる数値を指定します。

引数に数値以外の値を指定すると、エラー値 #VALUE! が返されます。

数値と基準値の符号が異なる場合、エラー値 #NUM! が返されます。

数値の符号に関係なく、切り捨てられた値の絶対値は、数値より小さくなります。

指定された数値が基準値の倍数と等しい場合は、数値は丸められずにその値が返されます。

用語：丸め

コンピュータは２進数です。2進数では表現できない小数(例えば10進数の0.1があります)を扱うとき、通常、コンピュータは適当な”2進数”に変換します。このように近似値に変換することを「丸め」といいます。

CEILING 関数

書式：「 CEILING(数値,基準値) 」

[FLOOR] 関数＆[CEILING] 関数の使用例

上の、注釈では、未だなんのことやら分かりかねると思います。下図をご覧ください。

FLOOR は床、CEILING は天井を意味します。指定された[基準値]の倍数には、0倍、1倍、2倍、3倍等が、対象とする[A]列の[数値]に合わせて自動的に設定されます。図では、夫々、[0],[50],[100],[150],[200]が戻り値になっており、端数は切り捨てられてしまいます。

この戻り値の夫々をを「床」としたとき、[A]列の数値（この場合個数）が何個積み上げられたかを[C列]に数式で示しました。

[CEILING] 関数の場合は、基準値の0、1、2、3、4倍の[0],[50],[100],[150],[200]の夫々を天井にした場合、後何個で天井に届くかを[E列]に数式で示しました。

非常勤職員の賃金＋残業手当計算

[IF],[HOUR],[FLOOR],[MINUTE]と場合によっては[OR],[VALUE]関数を使います。

[FLOOR] 関数の[規定値]を[15(分)]に設定して、就労時間数を求めます

残業時間には、「 =IF(OR(B4="",E4=""),"",HOUR(F4-E4)+FLOOR(MINUTE(F4-E4),15)/60) 」と[OR]関数を使用し、２つの「条件式」を組み入れました。(また、昼食時30分も賃金に入れてあります。除きたかったら「正規時間」から[0.5]を差し引きます。)

未入力セル,入力無しのセル[空欄]の表現[""]：=IF(B4="","",でなければ、何々しなさい。)

若し[B4]セルが[未入力セル(空欄)]ならば、正規時間のセルは「空欄」にしなさい。でなければ何々しなさい。

[IF関数]で「空白行」を[0値]にし、でなければ、○○を計算せよとします。

「空白行」を[0値]にしないと、数式「 =I4+J4 」の結果が「エラー」になります。

「正規手当」、「残業手当」とも、同数式で、「参照セル」が異なるだけです。

　　[基準値]に設定した[15分]の倍数

　　　1時間は 0.041666667 (シリアル値)

　　　45分は 0.031250000

　　　30分は 0.020833334

　　　15分は 0.010416667

　　　0分は　 0

　　参考

　　　1 分は 0.0006944445　　(シリアル値)　　

　　　シリアル値については、こちらを参照ください。

　　　「川田次郎」の場合の時間表示検証　　　就労開始:[9:10]、就労終了:[17:00]

　　　時間の計算　　17:00 (17時)

　　　　　　　　　-　1　　(シリアル値上での計算ですので、繰り入れの考えは不要です。

　　　　　　　　　　　　　ここでは、解説のため用いました。)

　　　　　　　　　-　9:10（ 9時)

　　　--------------------------------------

　　　　　　　　　　 7　(時間)

　　　--------------------------------------

　　　分の計算１

　　　　　0.041666667(1時間を分に繰り入れ) + 0.041666667

　　　　　　　　　　　　　　　　　　　　 - 0.000694444*10

　　　-------------------------------------------------------------

　　　分の計算２

0.041666667

-0.006944444

　　　------------------------------------------------------------

　　　分の計算結果　　　　　　　　　　　　 = 0.034722223

　　　------------------------------------------------------------

「分の計算結果」に対して[基準値]の倍数　45分の[0.031250000]　(シリアル値)が自動的に適用されます。

１時間が１なら、30分は[0.5時間],15分は[0.25時間],45分は[0.75時間]となることは、容易に想像できるところです。

（計算：戻り値は[0.031250000]　を「1(時間):0.041666667=x:0.031250000」で計算し、時単位に直しますと[0.75]時間）

合計時間：7.75時間

関数式の詳細解説

=IF((B10="","",HOUR(C10-B10)+FLOOR(MINUTE(C10-B10),15)/60)

上の関数式を分解します。

=IF(　条件式 ,引数2,　　　　　　　引数３　　　　　　　　　　)：IF関数の書式です。

　B4=""　　　　　　　　　　　　　　　　　　　　　　　　　：IF関数引数1の[条件式]です。

,　　　　　　　　　　　　　　　　　　　　　　　：引数1の終了の[,]です。

""　　　　　　　　　　　　　　　　　　　　；IF関数引数2です[条件式]がTRUEならば、こうしなさい。

　　　　　　　　 ,　　　　　　　　　　　　　　　　　　　　　；IF関数引数2の終了の[,]です。　　　　　　　　　　　　　　　　　　　　　　　　

HOUR(C10-B10)+FLOOR(MINUTE(C10-B10),15)　：IF関数引数3の内容で[条件式]がFALESならば、こうしなさい。

HOUR(C10-B10)　　　　　　　　　　　　　　：時間数を求めます。　

　　　　　　　　　　　　　　　　+ ：+

FLOOR(MINUTE(C10-B10),15)　：FLOOR( ,15）で15(分)倍数の「基準値」を求めます。

FLOOR(MINUTE(C10-B10),15)/60：[MINUTE]関数は分単位です。60で割って時単位に直します。

アルバイトの出

下の図は、毎日２名のアルバイト確保のための、一つの方法です。私情をはさまないよう「乱数１桁表示」('=ROUNDDOWN(RAND()*10,0))を使って、機会均等主義で全員に同じ機会を与える努力をしました。

関数を使ってカレンダーに写しました、

関数を使用した

使用した関数：TODAY,CONCATENATE,VLOOKUP,ISBLANK,TEXT,&,SUBTOTAL,＆数式

TEXT関数については、前ページをご覧ください。

下記をデスクにダウンロードして、ご使用ください。オリジナルですので、自由に編集してご使用ください。

EXCELで請求書を作る

[H1]セルに「 =TODAY() 」

TODAY関数：書式は「 TODAY() 」です。

表示は[2005/9/28]のスラッシュ型です。それを、[セルの書式設定]→[表示形式]→[分類]→[日付]で元号年月日にしてあります。

TODAY関数は、EXCELを開いた日付けをリアルタイムで表示します。もし日付部分を固定しておきたい場合は、TODAY関数を使わずに、セルに直接「2005/9/23」のように入力します。

[B5]セルに「 =CONCATENATE(VLOOKUP(A5,$I$12:$J$22,2,TRUE),I23) 」

CONCATENATE関数：書式は「 CONCATENATE(文字列1,文字列2,文字列n) 」です。

引数に指定した文字列をすべてつなげた文字列にします。半角スペース、全角スペースも文字列です。VLOOKUP関数で引き出したものも文字列です。文字列を連結して、１つの文字列にするのに、文字列演算子[&](アンバサンド)がありますが、こちらの方が使い勝手が良さそうです。

書類では、半角スペースを挿入して、くっつけました。

VLOOKUP関数：書式は「 VLOOKUP(検索値、範囲、列番号、検索の型) 」です。

VLOOKUP関数は、データを検索する関数で、別に作成してある表を参照して、コードNo．で文字列(例えば商品名、価格など)を引き出してくれます。

本来は、請求書に載せるものでなく、別シートに置くべきものですが、参考的に乗せました。

VLOOKUP関数については、こちらを参照ください。

[A5]の数値を１字変えると、右辺の表からお得意様を引き出します。書類から言って会社名が適当なのですが、１ページに表示するため、個人名を使用しました。

[B7]セルに「 =CONCATENATE("一金 ",TEXT(F45,"#,##0"),J23) 」

"一金 "は文字列、[TEXT]関数の表示形式で操作した文字も、文字列です。セル参照の[I23](I,は1でなくiです。)も文字列です。

ISBLANK関数：書式は「 ISBLANK(テスト対象) 」

注釈：セルの内容が空白の場合にTRUEを返します。

テストの対象注釈：テストの対象にはテストしたいデータを指定します。

テストの対象が空白セルを参照した場合が [TRUE]になり、空白セルが無ければ、[FALSE]になります。

[FALSE]の表現は感じが良くありませんが、[IF]関数の場合でも、[TRUE]の場合は[""](空欄)にしておいて、 [FALSE]の処理で「こうして欲しい」とまともな処理を指示するのが「常套手段」になっています。

[ISBLANK(テスト対象)]は、そのまま、[IF]関数引数1の「論理式」になります。

関数式の構造

=IF(　論理式　　,　引数2　,　引数3　)　「若し、AとBが[=]か[<]の関係か[>]の関係か[<>]・・・等の関係に

あるときTRUEならば[引数2]に、FALESならば[引数3]にしなさい。」

　　ISBLANK(E12)　　""　　　E12*F12　　「若し,テスト対象がブランクだったら[E12*F12]を実行しなさい。」　　　

対象は[E12]ですが、オートフィル機能でE列全般に設定します。これは、金額欄に[０]や[エラー]表示を避けるために使用しています。（「=IF(OR(E12=""),"",E12*F12)」でも一応できますが、 [OR]関数を使って1個では気が引けます。D 列 E 列の2箇所指定して[AND]関数を使用したところ[0]が表示されたり、されなかったりでした。？）

[G6] ="請求No．"&TODAY()&"A-"

請求書No．をどのようにしたら良いか、の１方法として、当日のシリアル値(TODAY())に担当者Aの何番にしました。なお、文字列のつなぎに、[&]を使用しました。

[G16,G19,G28,G37,G42]各セルに「 =SUBTOTAL(9,集計するセル範囲)

SUBTOTAL関数：書式は「 SUBTOTAL(集計方法, 範囲1, 範囲2, ...) 」です。

SUBTOTAL関数で集計方法で使用する[引数＊]とは

引数：9 （SUM関数使用=合計）

引数：4 （MAX関数使用=最大値）

引数：1 （AVERAGE関数使用＝アベ）

引数：5 （MIN関数使用=最小値）

引数：2 （COUNT関数使用=数値の個数）

引数：3 （COUNTA関数使用=空白除く個数）

引数：6 （PRODUCT関数使用=積の計算）

引数の番号によって「集計方法」を選択します。　１１個有るうち主な７個を挙げました

いろいろな関数の代用になりますが、今回は、すべて[9]のSUM関数に使用を使いました。便利なところは、各小計をだしておいても、範囲指定で小計欄を含めても小計欄は合算されないことです。[G43]セルの場合がそうです。

[G43]に「 =SUBTOTAL(9,G12:G42) 」

[F45]に「 =G43*1.05 」

合計値に５％の消費税を加えた数式です。これが、上の請求金額欄に、呼び込まれる仕組みになっています。

続きます。

トップへ戻る　総目次へ戻る

関数総目次へ戻る　関数の勉強４へ戻る　関数の勉強６へ進む

EXCEL関数の勉強６

条件を基にデ

というと、難しそうですが、実際にやって見ると意外と簡単で、非常に便利な関数です。

SUMIF 関数

書式：「 SUMIF(範囲,検索条件,合計範囲) 」

SUMIF 関数は「指定した条件」の合計を計算します。

注釈　　：範囲内の、検索条件に一致する数値の合計を求めます。

範囲注釈：範囲には評価の対象となるセルの範囲を指定します。

(言葉を変えると、「指定した条件に合うデータだけを合計する」ということです。)

MS-ヘルプより

検索条件は、計算の対象となるセルを定義する条件を、数値、式、または文字列で指定します。

式および文字列を指定する場合は、">32"、"Windows" のように、半角の二重引用符 (") で囲む必要があります。

合計範囲実際に計算の対象となるセル範囲を指定します。

合計範囲に含まれるセルの中で、範囲内の検索条件を満たすセルに対応するものだけが計算の対象となります。

　　　A 　　　　　　　　B

　1　エアコンの価格　取り付け手数料

　2　　100,000 　　　　　7,000

　3　　200,000　　　　　 14,000

　4　　300,000 　　　　　21,000

　5　　400,000 　　　　　28,000

　数式　　　　　　：「 =SUMIF(A2:A5,">160000",B2:B5) 」

　説明 (計算結果) ：エアコン価格が \160000 を超える場合の取り付け手数料の合計を求めます (63,000)

　----------------------------------------------------------------------------------------

数式：「 =SUMIF(A2:A5,">160000",B2:B5) 」の解説

=SUMIF(　　　　) ：SUMIF関数です。

A2:A5　　　　　　：引数1の「指定した条件の範囲」です。[,]で区切ります。

">160000"　　　　：引数2の 160000より大きい条件です。[""]で囲みます。[,]で区切ります。

B2:B5　　　　　　：引数3の合計する範囲です。

([A2:A5]と[B2:B5]は列が異なるだけで、選択する行数は同じです。

左図は、一般的な家計簿です。実際出費科目は１０～２０倍はあろうかと思いますが例題ですのでご勘弁願います。

また、１月、２月のみですが１年でも、同じことです。科目ごとに列を増やして整然と記帳しようとすると、横に広がり過ぎてかえって散漫になろうかと思います。科目数を限定することなど、好みにもよりますが。

そんなとき、SUMIS 関数の出番です。

手順説明

1. 例えば、E 列に集計したい科目名を書きます。

2. その右セル、この場合は[F4]を選択します。

3. 数式バーの[fx]ボタンをクリックします。(WinMeでは、標準ツールバーの[fx])

「関数の挿入ダイアログ」から、SUMIF 関数を選択します。

4. 範囲：「D3からD23」まで、ドラッグして選択します。

5. 検索条件：電気料と記入して、[Tab]キーを押すと電気料は自動的に「""」で囲まれます。

6. 合計範囲：「C3からC23」まで、ドラッグして選択します。

7. [ＯＫ]ボタンで終了します。

8. 次の「水道料」に移りますが、「電気料の数式をコピー」しておきます、

9. 水道料の右[F5]をクリックして、数式バーに「コピーしたものを」貼り付け、電気料を水道料に書き改めます。

２つ目の方法：[編集]メニュー →[形式を選択して貼り付け]→[テキスト]でも構いません。

３つ目の方法：電気料の右下隅の小さい□を右クリック、ポインターが[＋]で下にドラッグすると、暫くしてポップアップメニューがでます。 [書式なしコピー(フィル)(O)] (書式なしコピーとは＝テキストのことです。)をクリックした上で、”改めて「水道料」のセルを選択” し、数式バーの「電気料」を「水道料」に書き改めます。

10. 上の作業の繰り返しで、２０～３０科目は簡単に集計できます。

11. 例えば１年分の3000行分の集計にしても、[Ctrl＋Shift＋End]キー、ノートパソコンでは、[[Ctrl＋Fn＋Shift＋End]を押すと、瞬時に「記入欄末尾」に到達します。

12. または、名前ボックスに[A3000]などと記入する方法もありますので、わざわざ、スクロールバーでページをめくらなくても、一発で目的地へ到達します。

13. 範囲指定を手書きでするのも、一つの方法です。

COUNTIF 関数

書式：「 COUNTIF(範囲,検索条件) 」

COUNTIF 関数は、「指定した条件」のセルの個数を数えます。

注釈　　：指定した＜範囲＞に含まれる空白以外のセルのうち、＜検索条件＞に一致するセルの個数を返します。

範囲注釈：範囲にはセルの個数を求めたいセルの範囲を指定します。

MS-ヘルプより

検索条件計算の対象となるセルを定義する条件を、数値、式、または文字列で指定します。

式および文字列を指定する場合は、">32"、"Windows" のように、半角の二重引用符 (") で囲む必要があります。

第１図

[COUNTIF] 関数を使うと、複雑なカウントも容易に求めることができます。

ほかの関数と組み合わせたりして、あらゆる条件でデータをカウントします。

月別の件数を求めます。

月を跨いだ特定の期間内の件数を求めます。

重複データを省いて人数を求めます。(COUNTIF+COUNTIF)アンケートを実施してその集計をするような場合に使用します。

等々

簡単な例題です。６人の男女が居ます。男性の数、女性の数を求めなさい。

女性の数を求めるとき、前項の手順９－３の方法を図示しました。(Excel2003の場合です。)単にオートフィルすると、書式もコピーされ、ややっこしくなります。

第２図

２重申し込みなどの重複を発見する

第３図

左図数式バーの「Criteria(クライテリア)」にご注目ください。[B4]から[B19]まで「範囲選択」したのですが、 [$B$4:$B$19]と表示せずに、EXCELが勝手に記述したのです。翻訳では「評価の基準」ですが、EXCEL上では、「検索条件」になっているようです。(データベース関係関数、例えば[DCOUNT]などでは、引数3で出現しています。) 第１図と第２図の場合には出なかった現象です。どうしてなのか、分かりません。

仔細に検討すると、[人見賢治]の手前までは、[$B$4:$B$18]と範囲選択ができ、結果も正しくでました。また、「人見賢治」の後に何名かを追加して行ったところ、行列座標で「範囲選択」されるのです。「人見賢治」のところを[Del]で消したり、「書式のクリア」をしても駄目でした。ここで、分かったことは、[Criteria]で範囲指定すると絶対参照の[$]を付けなくても上手くいくことがあるようです。

「B列」が空欄のところは、[0]が表示されますので、[ツール]メニュー →[オプション]→[表示]タブ→[ゼロ値]のチエックを外しました。

一応重複が２とか３とかで表示されました。「条件付書式設定」で、[次の値がより大きい][1]で色付けすれば、大きな表でも一目瞭然になります。

若しくは、「表示された数値」欄４行目のの右隣をアクティブにしておいて、 [IF]関数を使って、

「 =IF(COUNTIF($B$4:$B$19,B4)>1,"重複",B4) 」(意味：カウントが１以上なら「重複」と表示しなさい。そうでなければ、カウント[1]者の氏名を表示しなさい。です。)

そうしてから、元の氏名欄の末尾から、重複者の名前を削除して行くと、新しい氏名欄は重複はなくなり。全てが[1]表示になります。

全国住みよい町ランキングで、北相馬郡が６位にランク付けされました。

算出方法

１.基本構成　　　　　　　　　　　　　　( )内数値は全国平均値である

　　Ａ県総合満足度　＝＋Ａ県ゴミ処理　　　　(0.31)

　　　　　　　　　　　＋〟　病院　　　　　　(0.17)

　　　　　　　　　　　＋〟窓口サービス　　(0.15)

　　　　　　　　　　　＋〟図書館　　　　　(0.10)

　　　　　　　　　　　＋〟公園緑化　　　　(0.09)

　　　　　　　　　　　＋〟下水道　　　　　(0.07)

　　　　　　　　　　　＋〟国道　　　　　　(0.05)

　　　　　　　　　　　＋〟地方道　　　　　(0.05)

　　　　　　　　　　　＋〟河川砂防海岸　　(0.02)

合計　(1.00)

(対象外行政サービス：消防署,水道,公民館,防災,公立小,中,保育園幼稚園,成人教育,公営バス

　高齢者福祉,障害者福祉,公営住宅,公営駐車場,市議会議員,公営スポーツ施設　等15)

その他、但し書きや例外処理あり。

私は、利根町は「気候が良く」「自然災害」や「犯罪」の少なさが全国でも、トップクラス(かな？)なので、６位になったのだと思っていたところ、事実は上記のとおり、人為的産物の内容でありました。

そこで、私の独断と偏見で仮説の「アンケート」表を作成し、[COUNTIF] 関数で、いろいろと「アンケートの集計」を行いました。集計実例のための表です。

(100年に1度かという卵大の降雹が町の半分を襲いましたが、保険賠償でたっぷり形がついたようなので省略しました。)

(私の家も15～20万円の損害を蒙りました。70有余年、この町で生活して、天災は昔の堤防決壊２度とこの雹害の３度だけです。)

問１．ここ数年暴風の脅威を感じましたか。　　　　　　「Yes」「No」で答えなさい。

問２．ここ数年地震災害に遭われましたか。　　　　　　「Yes」「No」で答えなさい。

問３．ここ数年30mm以上の豪雨を経験しましたか。　　　「Yes」「No」で答えなさい。

問４、ここ数年落雷の恐怖を感じたことが有りましたか。「Yes」「No」で答えなさい。

問５．ここ数年町内で殺人事件を耳にしましたか。　　　「Yes」「No」で答えなさい。

問６．ここ数年町内で発砲騒ぎを耳にしましたか。　　　「Yes」「No」で答えなさい。

問７．利根川栄橋の渋滞で困っていますか。　　　　　　「Yes」「No」で答えなさい。

問８．鉄道を敷設して欲しいですか。　　　　　　　　　「Yes」「No」で答えなさい。

第４図

上の表で、「同地区複数名」の設定セル番地[N14]は誤りで[N15]が正しいです。

続きます。

トップへ戻る　総目次へ戻る

関数総目次へ戻る　関数の勉強５へ戻る関数の勉強７へ進む

EXCEL関数の勉強７

指定した数値

一番利用されている関数に、足し算の[SUM]関数があります。では、掛け算の関数は？が。

また、行番号、列番号の座標から「セル値」を求めるのが今回の課題です。

PRODUCT 関数

書式：「 PRODUCT(数値1,数値 2,...) 」

PRODUCT関数は、指定した数値の積（掛算の結果）を求める関数です。

注釈　　：引数の値を返します。

数値1注釈：数値 1,数値 2,...には積を求めたい数値を、１から30個まで指定できます。

MS-ヘルプより

解説

引数に指定した数値、論理値、数値を表す文字列は、計算に使用されますが、エラー値、数値に変換できない文字列を引数に指定すると、エラーになります。

引数が配列またはセル範囲である場合、その中に含まれる数値だけが計算の対象となります。

配列あるいはセル範囲に含まれる空白セル、論理値、文字列、またはエラー値はすべて無視されます。

使用例

　　1　　　　データ

　　2　　　　　 5

　　3　　　　　15

　　4　　　　　30

　　　　数式　　　　　　　　　　説明　　　　　　　　　(計算結果)

　　=PRODUCT(A2:A4) 　　上の数値の積を求めます　　　　　 (2250)

　　=PRODUCT(A2:A4, 2) 上の数値と 2 とを乗算します　　　(4500)

　　-------------------------------------------------------------------

補記：B 列に数値があった場合でも[A2:B4]で掛け算が成立します。また、個別指定で[A2,A4]も成立します。

SUMPRODUCT

書式：「 SUMPRODUCT(配列1, 配列2, 配列3, ...) 」

注釈　　　：配列(計算の対象となる要素を含む)の対応する要素の積を合計した結果を返します。

配列1注釈：配列1, 配列2,..には要素の積の合計を求めたい配列を２から30個まで指定できます。

解説

引数となる配列は、行数と列数が等しい配列である必要があります。行数と列数が等しくない場合、エラー値 #VALUE! が返されます。数値以外の配列要素は、0 であると見なされます。

MS-ヘルプより

解説（使用例、図、解説共、表現が適宜編集し直してあります。)

SUM(A2:A7*B2:B7) という数式が「配列」として入力されていた場合、(補記：Ctrl キーと Shift キーを押しながら Enter キーを押しますと。) この使用例と同じ計算結果を得ることができます。(これは、「配列数式」です。)

(補記注意：「配列数式」にした場合のトラブル発生で、これを解除したい時、数式バーの[×]を押す以外に方法はなさそうです。正規の終了さえ、できかねるのです。)

(上図で、配列１を「数量」、配列２を「単価」置き換えると、各行の積算をしないで、一遍に合計額が算出されることが分かります。)

配列を使用することによって、より一般的な方法で SUMPRODUCT 関数と類似の計算を実行できます。

たとえば、セル範囲 A2:A7 に入力されている要素の 2 乗の和を計算するには、SUM(A2:A7^2) という数式を入力し、 Ctrl キーと Shift キーを押しながら Enter キーを押します。(この３つのキーを押して、始めて「配列数式」になるのです。)

　　　-------------------------------------------------------------------------------------

SUMPRODUCT 関数は、[引数1]として指定した配列(計算の対象となる要素を含む)の対応する要素の「積」を先ず計算します。その上で[SUM] 関数で、「和」を求め合計額を返します。前項の[PRODUCT] 関数と[SUM] 関数を組み合わせた関数です。配列とは、データが入力されている一塊の範囲のことです。

[INDEX] 関数

書式：「 INDEX() 」

「表」や選択した「セル範囲」内での、行番号と列番号の座標を指定してデータ(値)を求める関数です。

注釈：インデックスを使って、範囲、又は配列から抽出した値を返します。

MS-ヘルプより

テーブルまたはセル範囲にある値、あるいはその値のセル参照を返します。

INDEX 関数には、配列形式とセル範囲形式の 2 種類があります。

配列形式の戻り値は値または値の配列となり、セル範囲形式の戻り値はセル参照となります。

INDEX(配列,行番号,列番号) の形式では、配列内にある、指定したセルまたはセルの配列の値が返されます。

INDEX(範囲,行番号,列番号,領域番号) の形式では、範囲内にある、指定したセルまたはセル範囲の参照が返されます。

行番号と列番号で指定されるテーブルまたは配列の要素の値を返します。

INDEX 関数の 1 番目の引数が配列定数のときは、配列形式を使います。

書式 1 配列形式 :「　INDEX(配列,行番号,列番号)　」

(注釈)配列：インデックスを使って、範囲　または配列から抽出した値を返します。

(注釈)配列：配列には配列として入力したセル参照を指定します。

(注釈)行番号には＜配列＞の中にある、値を返す行を指定します。

(注釈)列番号には＜配列＞の中にある、値を返す列を指定します。

MS-ヘルプより

配列セル範囲または配列定数を指定します。

配列が 1 行または 1 列のみの場合、それぞれ行番号または列番号を省略することができます。

配列が複数行および複数列で構成され、行番号または列番号のどちらか一方しか指定されていない場合、配列の中にある行または列全体の配列が返されます。

行番号配列の中にあり、値を返す行を数値で指定します。

行番号を省略した場合は、必ず列番号を指定する必要があります。

列番号配列の中にあり、値を返す列を数値で指定します。

列番号を省略した場合は、必ず行番号を指定する必要があります。

解説

行番号と列番号の両方が使用されている場合は、行番号と列番号が交差する位置にあるセルの値が返されます。

行番号または列番号に 0 を指定すると、それぞれ列全体または行全体の値の配列が返されます。

配列として返される値を利用するには、INDEX 関数を配列数式として横方向のセル範囲に入力するか

、縦方向のセル範囲に入力します。配列数式を入力するときは、Ctrl + Shift + Enter キーを押します。

行番号と列番号には、配列内のセルを指定します。配列の範囲外のセルを指定すると、エラー値 #REF! が返されます

INDEX 関数の 1 番目の引数が範囲のときは、範囲形式を使います。

書式 2 範囲形式：「 INDEX(範囲,行番号,列番号,領域番号) 」

(注釈)　　　　：インデックスを使って、範囲、または配列から抽出した値を返します。

(注釈)範囲　　：範囲には１つ、又は複数のセル範囲への参照を指定します。

(注釈)行番号　：行番号は＜配列＞の中にある、参照を返す行を指定します。

(注釈)列番号　：列番号は＜配列＞の中にある、参照を返す列を指定します。

(注釈)領域番号：＜範囲＞に複数の領域が含まれている場合、どの領域から

　　　　　　　　＜行番号＞と＜列番号＞の交点をかえすかを表す数値を指定します。

MS-ヘルプより

書式 2

指定された行と列が交差する位置にあるセルの参照を返します。

隣接しない複数のセル範囲を指定した場合、その中から任意の領域を選択できます。

セル範囲形式書式「 INDEX(範囲,行番号,列番号,領域番号) 」

範囲 1 つまたは複数のセルの参照を指定します。

範囲として複数選択された領域を指定する場合は、範囲をかっこ () で囲み、複数選択を構成するそれぞれの領域を半角のカンマ (,) で区切ります。

範囲で指定した各領域が 1 行または 1 列である場合、行番号または列番号はそれぞれ省略することができます。

たとえば、範囲が 1 行のみである場合は、INDEX(範囲,列番号) と指定できます。

行番号範囲の中にあり、セル参照を返すセルの行位置を数値で返します。

列番号範囲の中にあり、セル参照を返すセルの列位置を数値で返します。

領域番号範囲に複数選択された領域を指定した場合、その中の 1 つの領域を数値で指定します。

指定した領域の中から行番号と列番号が交差する点にあるセルの参照が返されます。

最初に選択または入力された領域の領域番号が 1 となり、以下、2 番目の領域は 2、3 番目の領域は 3 と続きます。

領域番号を省略すると、1 であると見なされます。

たとえば、範囲として (A1:B4,D1:E4,G1:H4) のような複数選択領域が指定されている場合、領域番号の 1 は A1:B4、領域番号の 2 は D1:E4、領域番号の 3 は G1:H4 となります。

解説

INDEX 関数では、まず範囲と領域番号を使って特定の範囲が選択され、次に行番号と列番号から特定のセルが選択されます。

行番号の 1 は選択された範囲の先頭行 (上端行) を指定し、列番号の 1 は選択された範囲の先頭列 (左端列) を指定します。

INDEX 関数が返すセル参照は、行番号と列番号が交差する点にあるセルの参照となります。

行番号または列番号に 0 (ゼロ) を指定すると、列または行全体の参照がそれぞれ返されます。

行番号、列番号、領域番号には、範囲内にあるセルまたは領域を指定します。

範囲外の対象を指定してしまうと、エラー値 #REF! が返されます。

行番号と列番号を省略すると、領域番号で指定されるセル範囲内の領域が返されます。

INDEX 関数の計算結果はセル参照となり、他の数式はこれを通常のセル参照と同様に扱います。

INDEX 関数の計算結果は、セル参照または値として使用されます。

たとえば、CELL("width",INDEX(A1:B2,1,2)) という数式は、CELL("width",B1) と同じ結果になります。

これは CELL 関数が、INDEX 関数の計算結果を通常のセル参照と同じであると解釈するためです。

一方、2*INDEX(A1:B2,1,2) のような数式では、INDEX 関数の計算結果はセル B1 に入力されている数値に変換されます。

「範囲形式」使用例 (エクセルヘルプ例ですが、若干編集し直してあります。)

下記エクセルフアイルをダウンロードしてください。説明より実例の方が分かり易いと思います。

INDEX 関数使用例

[INDEX] 関数に

書式：「 MATCH(検査値,検査範囲,照合の型) 」

(注釈)範囲　　：または配列から値を検索して返します。

(注釈)検査値　：検査値にはテーブルから必要な項目を検索するために使用する値を指定します。

(注釈)検査範囲：検査範囲には＜検査値＞が入力されている、連続したセル範囲を選択します。

(注釈)照合の型：総合の型には数値 -1、0、または1を指定します。＜照合の型＞には＜検索範囲＞の中で＜検査値＞をお検索する方法を指定します。

MS-ヘルプより

指定された照合の型に従って検査範囲内を検索し、検査値と一致する要素の、配列内での相対的な位置を表す数値を返します。

検査値で指定したセルに含まれる値ではなく、検査値の範囲内での位置を調べる場合は、その他の検索/行列関数の代わりに、MATCH 関数を利用してください。

検査値表の中で必要な項目を検索するために使用する値を指定します。

検査値には、実際に検索する値ではなく、検査範囲の中で照合する値を指定します。たとえば、電話番号帳を使ってある人の電話番号を調べるとき、検査値としてその人の氏名を指定しますが、実際に検索するのは電話番号です。

検査値には、数値、文字列、論理値、またはこれらの値に対するセル参照を指定できます。

検査範囲は、検査値を含む隣接したセル範囲を指定します。検査範囲は、配列または配列に対するセル参照を指定してもかまいません。

照合の型 -1、0、1 の数値のいずれかを指定します。照合の型には、検査範囲の中で検査値を探す方法を指定します。

照合の型に 1 を指定すると、検査値以下の最大の値が検索されます。

このとき検査範囲のデータは、昇順に並べ替えておく必要があります。

昇順の並べ替えでは、数値は 1 ? 9、アルファベットは A ? Z、かなは "あ" ? "ん"、ブール型の値は FALSE から TRUE に配置されます。

照合の型に 0 を指定すると、検査値に一致する値のみが検索の対象となります。このとき検査範囲を並べ替えておく必要はありません。

照合の型に -1 を指定すると、検査値以上の最小の値が検索されます。このとき検査範囲のデータは、降順に並べ替えておく必要があります。降順の並べ替えでは、数値は 9 ? 1、アルファベットは Z ? A、かなは "ん" ? "あ"、ブール型の値は TRUE から FALSE に配置されます。

照合の型を省略すると、1 を指定したと見なされます。

解説

MATCH 関数では、指定したセルに含まれている値ではなく、検査範囲内にある検査値に一致する値の【位置】が返されます。

たとえば、MATCH("b",{"a","b","c"},0) は 2 を返します。これは、配列 {"a","b","c"} の中での "b" の相対位置を表します。

MATCH 関数では、英字の大文字と小文字は区別されません。検査値が見つからない場合は、エラー値 #N/A が返されます。

照合の型に 0 を指定し、検査値が文字列の場合、検査値の中で、アスタリスク (＊)、疑問符 (？) のワイルドカード文字を使うことができます。

ワイルドカード文字のアスタリスクは任意の文字列を表し、疑問符は任意の 1 文字を表します。

　　使用例

　　 A　　　　 B

　　1　　製品　　　個数

　　2　　バナナ　　25

　　3　　オレンジ　38

　　4　　リンゴ　　40

　　5　　ナシ　　　41

　　　　　数式　　　　　　　　　　　　　　説明　　　　　　　　　　　　　(計算結果)

　　=MATCH(39,B2:B5,1) 　　B2:B5 のセル範囲から 39 を検索しますが、一致する値がないため、

この値以下の最大の値 38 の位置が返されます　　計算結果 (2)

　　=MATCH(41,B2:B5,0) 　　B2:B5 のセル範囲から 41 の位置を調べます　　　計算結果 (4)

　　=MATCH(40,B2:B5,-1) B2:B5 のセル範囲は降順に並べ替えられていないため、エラーが返されます (#N/A)

　　　(下記フアイルにあります。)

エクセルフアイルです。宜しかったら、ダウンロードしてください。

INDEX(MATCH) 関数使用例

上記表解説

書式 2 範囲形式：「 INDEX(範囲,行番号,列番号,領域番号) 」

(注釈)　　　　：インデックスを使って、範囲、または配列から抽出した値を返します。

(注釈)範囲　　：範囲には１つ、又は複数のセル範囲への参照を指定します。

(注釈)行番号　：行番号は＜配列＞の中にある、参照を返す行を指定します。

(注釈)列番号　：列番号は＜配列＞の中にある、参照を返す列を指定します。

(注釈)領域番号：＜範囲＞に複数の領域が含まれている場合、どの領域から

　　　　　　　　＜行番号＞と＜列番号＞の交点をかえすかを表す数値を指定します。

---------------------------------------------------

書式：「 MATCH(検査値,検査範囲,照合の型) 」

(注釈)範囲　　：または配列から値を検索して返します。

(注釈)検査値　：検査値にはテーブルから必要な項目を検索するために使用する値を指定します。

(注釈)検査範囲：検査範囲には＜検査値＞が入力されている、連続したセル範囲を選択します。

(注釈)照合の型：総合の型には数値 -1、0、または1を指定します。＜照合の型＞には＜検索範囲＞の中で＜検査値＞をお検索する方法を指定します。

---------------------------------------------------

先ず、求めたい「運賃」を[C11]に決め、ここに、数式を入力します。

　(結果的には、「 =INDEX(B3:G8,MATCH(A11,A3:A8,0),MATCH(B11,B2:G2,0)) 」になりますが。)

[INDEX] 関数で、対象となる範囲(=領域)を、１つ決めます。

この例題の場合は「データ領域全体」です。(B3:G8)

[INDEX] 関数の行番号の指定で、[MATCH] 関数をネストします。

[MATCH] 関数の[検査値]で、「発駅名」のセルをセル参照の形で指定します。(A11)

[MATCH] 関数の[検査範囲]で、Ａ列駅名全てを「範囲指定」します。(A3:A8)

[MATCH] 関数の[照合の型]は、当然「完全一致」の[0]にします。

　以上で[INDEX] 関数の「行番号」の指定は終了しました。

　　(続いて、[INDEX] 関数の「列番号」の指定に入ります。)

[INDEX] 関数の列番号の指定で、[MATCH] 関数を再びネストします。

[MATCH] 関数の[検査値]で、「着駅名」のセルをセル参照の形で指定します。(B11)

[MATCH] 関数の[検査範囲]で、２行駅名全てを「範囲指定」します。(B2:G2)

[MATCH] 関数の[照合の型]は、当然「完全一致」の[0]にします。

　以上で[INDEX] 関数の「列番号」の指定は終了しました。

[INDEX] 関数の[領域番号]に戻ります。[範囲]は1つなので省略しました。[1]と見做されます。

上記の図式です。

数式：「 =INDEX(B3:G8,MATCH(A11,A3:A8,0),MATCH(B11,B2:G2,0)) 」

(1)=INDEX( )

(2) B3:G8,

(3) MATCH(A11,A3:A8,0),

(4) MATCH(B11,B2:G2,0)

(5)省略

解説　　　　　　範囲形式：INDEX(範囲,行番号,列番号,領域番号

(1)INDEX 関数( 　　)

(2)範囲 [B3]～[G8]

(3)MATCH 関数をネスト（行を検索する）

(3-1)　　検査値　　　[A11]

(3-2)　　検査範囲　　　　　 [A3]～[A8]

(3-3)　　照合の型　(0は[検査値]と一致)　[0]　　

(4)MATCH 関数をネスト（列を検索する）

(4-1)　　検査値　　　　　　　　　　　　　　　[B11]

(4-2)　　検査範囲　　　 [B2]～[G2]

(4-3)　　照合の型　(0は[検査値]と一致)　　　　　　　　　　　 [0]　

(5)領域番号　　　　(省略の場合は第１選択範囲の[1]と見做す) [省略]

CHOOSE 関数

書式:「 CHOOSE(インデックス,値 1,値 2,...) 」

(注釈)　　　　　　：インデックスを使って、引数リストから特定の値を１つ選択します。

(注釈)インデックス：インデックスには引数リストの値の位置を表す数値を指定します。

(注釈)値1 ：値1,値2には１から２９個の引数を指定します。ここからインデックスで指定した値が返されます。

MS-ヘルプより

値のリストから指定した値を取り出します。CHOOSE 関数を利用すると、インデックスに指定した番号に基づいて、最大 29 個の値の中から 1 つの値だけを選択できます。

たとえば、値 1 から値 7 が 1 週間の曜日になっている場合、インデックスとして 1 から 7 までの任意の数値を指定すると、いずれかの曜日が返されます。

インデックス引数リストの何番目の値を選択するかを指定します。インデックスには、 1 ～ 29 までの数値、または 1 ～ 29 までの数値を返す数式またはセル参照を指定します。

インデックスが 1 の場合は値 1 が返され、2 の場合は値 2 が返されます。インデックスと値との関係は、以下についても同様です。

インデックスが 1 より小さいか、引数リストの値の個数よりも多い場合、エラー値 #VALUE! が返されます。

インデックスに小数点以下の値が含まれていても、整数部分だけが計算に使われます。

値 1,値 2,... インデックスに基づいて選択される値または動作を 1 ～ 29 個まで指定します。これらの引数には、数値、セル参照、名前、数式、関数、または文字列を指定できます。

解説

インデックスが配列の場合、CHOOSE 関数によって検証されるときに、すべての値が選択されます。

CHOOSE 関数に指定する値として、1 つの値だけでなく、セル範囲への参照を使うこともできます。

たとえば、次の数式は

次のように計算され

計算結果として返される値は、セル範囲 B1:B10 に含まれる値の合計になります。

この数式では、まず最初に CHOOSE 関数が計算され、セル範囲 B1:B10 が返されます。次に、SUM 関数が、CHOOSE 関数の結果であるセル範囲 B1:B10 を引数として合計を計算します。

　　　---------------------------------------以上、ＭＳ-ヘルプより

上図の「数式３」の「 =SUM(A13:CHOOSE(3,A13,A14,A15)) 」で、「CHOOSE(3,A13,・・・」の[A13]を除き、インデックスを[2]にし、「 =SUM(A13:CHOOSE(2,A14,A15)) 」としても、同じ結果になります。 (どうやら、[A13]は重複のように思われますが。？　詳しいことは分かりません。)

CHOOSE 関数の特徴

１．[範囲選択(指定)]が、ありません。

２．セル１個づつの個別指定になります。個別指定ですから、不連続セルでもＯＫです。

３．２９個の引数(セル値)が指定できます。

４．値を数個指定した(並べた)内から「何番目」かを取り出す数値を示すのが、「インデックス」です。

続きます。

トップへ戻る　総目次へ戻る

関数総目次へ戻る　関数の勉強６へ戻る関数の勉強８へ進む

今まで学習した関数・内容の一覧表

最下段の「EX

いろいろな関数○○を、EXCEL関数の勉強 0-○○に直してあります。

既学習全関数

-------------------------------------------------------------------

EXCEL関数の勉強0-KANSU

AVERAGE　　　平均を求めめる　　　　　　　　　　　AVERAGE(数値1,数値2,...)

MAX　　　　　最大値を求める　　　　　　　　　　　MAX(数値1,数値2,...)

MIN　　　　　最小値を求める　　　　　　　　　　　MIN(数値1,数値2,...)

LARGE　指定した＜順位＞番目に大きな値を求める　　LARGE(範囲,順位)

SMALL　指定した＜順位＞番目に小さな値を求める　　SMALL(範囲,順位)　　　　　　　　　　　　　　　　　　　　　　　

RANK 順位を求める　　　　　　　　　　　　RANK(数値,範囲,順序)

-------------------------------------------------------------------

EXCEL関数の勉強0-2

COUNT　　　　数値データだけをカウント　　　　　　COUNT(値1,値2,...)

COUNTA 空白でないセルの個数をカウント　　 COUNTA(値1,値2,...)

COUNTBLANK 空白セルの個数をカウント　　　　　 COUNTBLANK(範囲)

SUM　　　　合計を求める(不連続セル値の合計可)　　SUM(数値1,数値2,...)

IF　　条件判断と処理の分岐(YesかNoかを判定し,処理1,処理2を行う) IF(論理式,真の場合,偽の場合)

　　　　　　　　-----------------------------

EXCEL関数の勉強0-2-2

IF　　　　　　　　　　　　　　　　　　　　　　　IF(論理式,真の場合,偽の場合)

-------------------------------------------------------------------

EXCEL関数の勉強0-3

AND　全てが[条件]を満たしているかどうかを判定する　　　　　AND(論理式1,論理式2,...)

OR どれか一つでも[条件]を満たしているかどうかを判定する　OR(論理式1,論理式2,...)

-------------------------------------------------------------------

EXCEL関数の勉強0-3-2

IF(OR) IF(OR(論理式1,論理式2,...),値1,値2）

IF(AND)　　　　　　　　　　　　　　　　　　　　　IF(AND(論理式1,論理式2,...),値1,値2）

VLOOKUP　　検索値に対する製品名や金額を求める　　VLOOKUP(検索値, 範囲,列番号,検索の型)

検索値と完全に一致する値だけを検索する　　　　　　 (検索の型：FALES の場合)　

-------------------------------------------------------------------

EXCEL関数の勉強0-4

P1 円周率　　　　　　　　　　　　　　PI　(引数なし)だけで、3.141592654と表示されます。

SQRT 平方根　　　　　　　　　　　　　　SQRT(数値)

ROUND　　　　　桁数を決めて数値を四捨五入する　　ROUND（数値,桁数）

ROUNDUP　　　　桁数を決めて数値を切り上げる　　　ROUNDUP（数値,桁数）

ROUNDOWN 　桁数を決めて数値を切り捨てる　　　ROUNDDOWN（数値,桁数）

TRUNC　　数値の小数部を切り捨て指定した桁数に変換する　TRUNC(数値,桁数)

INT　　　数値を任意の桁で切り捨て、整数にする　　　　　INT(数値)

-------------------------------------------------------------------

EXCEL関数の勉強0-5

TODAY　　　パソコン内部の時計から現在の日付を表示する　TODAY()

NOW　　　　パソコン内部の時計から現在の時刻を表示する　NOW()

DATE 1900～9999年までの指定日を表示　　　　　　　　　　　DATE(年,月,日)

HOUR シリアル値や時刻形式の文字列から[時]のみを表示する　HOUR(シリアル値)

MINUTE シリアル値や時刻形式の文字列から[分]のみを表示する　MINUTE(シリアル値)

SECOND シリアル値や時刻形式の文字列から[秒]のみを表示する　SECOND(シリアル値)

-------------------------------------------------------------------

EXCEL関数の勉強0-5-2

TIME　　　　　タイム表示　　　　　　　　　　　　TIME（時,分,秒）

YEAR　　　　　シリアル値入力で年のみを表示　　　YEAR(シリアル値)

MONTH シリアル値入力で月のみを表示　　　MONTH(シリアル値)

DAY シリアル値入力で日のみを表示　　　DAY(1～31までの整数)

DATEDIF ２つの日付の間の長さを求める　　　DATEDIF(開始日,終了日,"DATEDIF関数の単位")

　　　　　　　　　　　　　　　　　　　　　　　　DATEDIF(開始日,終了日,単位)

WEEKDAY シリアル値を曜日に(但し,数値)変換する　　WEEKDAY(シリアル値,種類)

-------------------------------------------------------------------

EXCEL関数の勉強0-5-3

TEXT　　　　　数値に指定した書式を設定し,文字列に変換する　　TEXT(値,表示形式)

　　　　　　　DATEDIF]関数使用の実際編

-------------------------------------------------------------------

EXCEL関数の勉強0-6

串刺し算

SUBTOTAL　　　方法を指定して集計する　　SUBTOTAL(集計方法,範囲1,範囲2,...)

　　　　　　　合計や平均などの集計方法を１～１１までの数字で指定して集計する

-------------------------------------------------------------------

EXCEL関数の勉強0-6-2

続[SUBTOTAL]　

-------------------------------------------------------------------

EXCEL関数の勉強0-7

VLOOKUP

　検索の型　FALSEの場合　VLOOKUP(検索値,範囲,列番号,検索の型)

　検索の型　TRUE　の場合　VLOOKUP(検索値,範囲,列番号,検索の型)　

HLOOKUP

　検索の型 TRUE の場合-入力省略可 HLOOKUP(検索値,範囲,列番号,検索の型)

LOOKUP

　その１　　　　　　　　　　　　　　　　LOOKUP(検査値,検査範囲,対応範囲)

　その２　　　　　　　　　　　　　　　　LOOKUP(検査値,配列)

-------------------------------------------------------------------

EXCEL関数の勉強0-8

[IF]関数にネストする関数

　　AND、OR、IF[ROUND関数]をネストして、構成比を小数点以下1桁まで求める

-------------------------------------------------------------------

EXCEL関数の勉強0-8-2

主な内容

１．[TEXT]関数

２．[WEEKDAY]関数

３．[IF]関数で空白セルを作り[TEXT]関数、[WEEKDAY]関数をネストさせます。

４．フィルタを使ってリストの値を抽出する方法

５．オートフィルタ

６．フィルタオプションの設定

-------------------------------------------------------------------

EXCEL関数の勉強0-8-2-2

主な内容

１．名前の定義　　　　(セルに名前を付ける)

２．入力規則　　　　　(リスト入力の設定)

３．Excel表示形式

４．ユーザー定義の表示形式の作成について： (数値の書式記号)

５．条件付の書式： (色の書式記号)-(日付や時刻の書式記号)-(文字列やスペースの書式記号)

-------------------------------------------------------------------

EXCEL関数の勉強0-8-2-3

主な内容

１．条件付き書式機能

２．条件付き書式の設定

-------------------------------------------------------------------

EXCEL関数の勉強0-8-3

MOD　　　数値を除数で割ったときの剰余を求める　　　MOD(数値,除数)

主な項目

１．[MOD]関数とは

２．[MOD]関数を[IF]関数にネストする

３．年月日から曜日を取得し[TEXT]関数で文字列に変換する

４．[条件付書式の設定]に[MOD]関数を使い奇数行、偶数行を塗りつぶす

５．[MOD]関数を使い偶数行(or 奇数行)の合計計算

-------------------------------------------------------------------

EXCEL関数の勉強0-8-3-2

主な項目

１．Sheet１枚で３０年暦を作る試み

２．カレンダーを作る（通称：万年カレンダー）

-------------------------------------------------------------------

EXCEL関数の勉強0-8-3-3

七曜型万年？暦を作る

主な項目

１．[WEEKDAY] 関数

２．[DATE] 関数

３．[TEXT] 関数

４．作成の手順

-------------------------------------------------------------------

EXCEL関数の勉強0-9

主な内容

１．複数の値を基準に判断する

２．IF 関数では条件判断できない場合

３．IF関数ネスト例題

４．エラーについて

-------------------------------------------------------------------

EXCEL関数の勉強0-9-2

IS関数の書式：引数は１つのみで、テストするデータを指定します。以下同じ。

　関数　　　　　　　　　　　　　　　　　働き

ISBLANK　　ISBLANK(テストの対象)　　テストの対象が空白の場合に TRUE を返します。

ISERR　　　ISERR(テストの対象)　　　テストの対象が #N/A 以外のエラー値の場合に TRUE を返します。

ISERROR　　ISERROR(テストの対象)　　テストの対象がエラー値

　　　　　　　　　(#N/A,#VALUE!,#REF!,#DIV/0!,#NUM!,#NAME?,または#NULL!)の場合に TRUE を返します。

ISLOGICAL　ISLOGICAL(テストの対象)　テストの対象が論理値の場合に TRUE を返します。

ISNA ISNA(テストの対象)　　　テストの対象がエラー値#N/A(値がない)の場合にTRUE を返します。

ISNONTEXT　ISNONTEXT(テストの対象)　テストの対象が文字列以外の値である場合に TRUE を返します

(テストの対象が空白セルを参照するときも TRUE になりますので注意してください)。

ISNUMBER ISNUMBER(テストの対象)　テストの対象が数値の場合に TRUE を返します。

ISREF　　　ISREF(テストの対象)　　テストの対象が参照である場合 TRUE を返します。

ISTEXT　　 ISTEXT(テストの対象) テストの対象が文字列である場合 TRUE を返します。

その他のIS 関数

ISEVEN　　数値が偶数のときに TRUE を返します。

ISODD　　　数値が奇数のときに TRUE を返します。

　上記の逆の場合には、FALSE を返します。

----------------------------

[0]表示を回避し、空欄にする法

ISBLANK 使用 IF(ISBLANK(A2),"再試験",IF(A2>=80,"A","B"))

[""]　　使用 IF(A3="","",C3*D3)

-------------------------------------------------------------------

EXCEL関数の勉強0-10

SUMIF 関数　指定した条件に一致するマス目 (セル) の値を合計する　　SUMIF(範囲,検索条件,合計範囲)

COUNTIF関数指定した範囲内で検索条件に一致したセルの個数を求める　COUNTIF(範囲, 検索条件)

　　　　　　　　　　　　COUNTIF 関数とSUMIF関数の使い分け

DATEDIF 関数　　指定された期間内の日数、月数、または年数を求める　DATEDIF(開始日,終了日,単位)

-------------------------------------------------------------------

EXCEL関数の勉強0-10-2

主な内容

１．文字列演算子「&」

２．SUMIF 関数

３．SUM(IF　)ネスト関数に配列数式を使用

４．配列数式

FREQUENCY 　　FREQUENCY(データ配列,区間配列)

-------------------------------------------------------------------

EXCEL関数の勉強0-11

主な内容

１．参照元のトレース、参照先のトレース

２．PMT 関数について

３．ゴールシーク

-------------------------------------------------------------------

EXCEL関数の勉強 0

主な内容

１．日付け、時刻の表記法

２．引数が１個の、[日付]、[時刻]の関数

３．引数が無い関数

４．小数点付近の数値を、取り扱う関数

５．数値を文字列に変える１　：　(数値の表現)-(数を表す文字列として表現)-

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

６．数値を文字列に変える２　：　(TEXT 関数を使います。)

-------------------------------------------------------------------

EXCEL関数の勉強 2

主な内容

１．IF 関数について

２．AND 関数について　

３．OR 関数について　

４．AND 関数,OR 関数の[判定の結果]に対して[指示を与える]には:IF 関数に,組み込む

５．IF 関数を使って、引数タイプの１つ１つを検証

６．セルやセル範囲に名前を付ける

７．専ら[IF]関数を使って、７種別の会費算出

８．[IF]関数に[AND]関数をネストして会費算出

９．[VLOOKUP] 関数を使って会費算出

10．引数に、配列（行方向や列方向に連続した値を一纏めしたデータ）を使う

====================================================================

EXCEL関数の勉強 3

文字列操作関数

指定した文字を抜き出す関数

RIGHT　　　　　　　　RIGHT(B1,n) 　　文字列の右端から[n文字]を抜き出します(B1は文字列セルの座標)

(半角と全角の区別なく 1 文字を 1 として処理が行われます。)

LEFT　　　　　　　　　LEFT(B2,n)　　　文字列の左端から[n文字]を抜き出します(B2は文字列セルの座標)

MID　　　　　　　　　MID(B3,N,n) 文字列の左端から数え[N]番目位置からｎ個の文字を抜き出します

(半角と全角の区別なく 1 文字を 1 として処理が行われます。)

検索と置換の関数

FIND　　　　　　　　　FIND(検索文字列,(検索対象を含む)文字列,開始位置(数値))

　　　　　　　　　　　　　　　　　　　(条件にあてはまる文字の位置を探す)

SEARCH　　　　　　　　SEARCH(検索文字列,(検索対象を含む)文字列,開始位置(数値)

　　　　　　　　　　　　　　　　　　　(文字列の中から指定した文字の位置を検索)

SUBSTITUTE　　　　　　SUBSTITUTE(文字列,検索文字列,置換文字列,置換対象(何番目の同名文字かを数値表示))

　　　　　　　　　　　　　　　　　　　(指定した文字列を別な文字列に置換する)

REPLACE REPLACE(文字列,開始位置,文字数,置換(に使われる)文字列)

　　　　　　　　　　　　　　　　　　　(文字列の中の指定された文字範囲を別の文字列に置換する)

(REPLACE関数にFIND関数をネストして,何字目かを取得し,文字列を置換する)

　　　　　　　　　　　　数式：「 =REPLACE(A1,FIND("ウインドウズ",A1,10),6,"Windows") 」

　　　　　　　-----------------------------

文字種を変換する関数

ASC　　　　　　　　　ASC(文字列) 　

　　　　　　　　　　　　　文字列内の全角(2バイト)の英数カナ文字を、半角(1バイト)の英数カナ文字に変換

　(文字列には全角の英数カナ文字を含む文字列を指定)

JIS　　　　　　　　　JIS(文字列) 　

文字列が含む半角(1バイト)の英数カナ文字を,全角(2バイト)の英数カナ文字に変換

(文字列には半角の英数カナ文字を含む文字列を指定)

　　　　　　---------------------------

UPPER　　　　　　　　UPPER(文字列)　文字列に含まれる英字をすべて大文字に変換します

　　　　　　　　　　　　　　　　　　　(文字列には大文字に変換したい文字列を指定)

　使用例：　　　　　　　　=UPPER(A2) セル A2 に入力されている英字を全て大文字に変換します

LOWER　　　　　　　　LOWER(文字列)　文字列に含まれる英字をすべて小文字に変換します。

　　　　　　　　　　　　　　　　　　　(文字列には小文字に変換したい文字列を指定)

　使用例　　　　　　　　　 =LOWER(A3) 　セル A3 に入力されている英字を全て小文字に変換します

　　　　　　-----------------------------

[LEN]　　　　　　　　LEN(文字列) 文字列の文字数を返します

[CONCATENATE]　　　　CONCATENATE(文字列1,文字列2,文字列n)

[TRIM]　　　　　　　 TRIM(文字列)

[YEN]　　　　　　　　YEN(数値,桁数)

==================================================================-

EXCEL関数の勉強 4

数値を書式設定した文字列に変換する関数

TEXT　　　　　　　　　　　　　　　TEXT(値,表示形式)

VALUE　　　　　　　　　　　　　　VALUE(文字列)

-------------------------------------------------------------------

EXCEL関数の勉強 5

賃金計算・アルバイト手配・請求書・住所録 &年賀状

FLOOR　　　　　　　　　　　　　　　FLOOR(数値,基準値)

CEILING CEILING(数値,基準値)

PHONETIC　ふりがなの文字列を取り出します。

-------------------------------------------------------------------

EXCEL関数の勉強 6

SUMIF　　　　　　　　　　　　　　　SUMIF(範囲,検索条件,合計範囲)

COUNTIF 　　　　　　　　COUNTIF(範囲,検索条件)

-------------------------------------------------------------------

EXCEL関数の勉強 7

指定した数値の積を求める。

PRODUCT　　　　　　　　　　　　　　PRODUCT(数値1,数値2,...)

SUMPRODUCT SUMPRODUCT(配列1,配列2,配列3,...)

行番号、列番号からセル値を求める。

INDEX　　　　　　　　　　　　　　 INDEX()

　　　　　　　　　　　配列形式 :　INDEX(配列,行番号,列番号)

　　　　　　　　　　　範囲形式： INDEX(範囲,行番号,列番号,領域番号)

[INDEX] 関数に[MATCH] 関数をネストして、運賃を計算する

MATCH 　　　　　　　　　　　　　MATCH(検査値,検査範囲,照合の型)

CHOOSE　　　　　　　　　　　　　　CHOOSE(インデックス,値1,値2,...)

　CHOOSE 関数の特徴

　１．[範囲選択(指定)]が、ありません。

　２．セル１個づつの個別指定になります。個別指定ですから、不連続セルでもＯＫです。

　３．２９個の引数(セル値)が指定できます。

　４．値を数個指定した(並べた)内から「何番目」かを取り出す数値を示すのが[インデックス]です。

-------------------------------------------------------------------

トップへ戻る　総目次へ戻る

関数総目次へ戻る　関数の勉強７へ戻る　関数の勉強９へ進む

配列数式

配列数式とし

戻り値が配列となるため、配列形式として入力しなければならない関数が、沢山あります。そのうち、私のページで取り上げたものに下記があります。

FREQUENCY

書式：「 FREQUENCY(データ配列,区間配列) 」

範囲内でのデータの頻度分布を、縦方向の数値の配列として返します。この関数では、値は配列として返され、配列数式として入力されます。

こちらをご覧ください。

INDEX 関数

INDEX 関数については、前々ページをご覧ください。

(書式1)セル範囲形式　　書式：「 INDEX(範囲,行番号,列番号,領域番号) 」

範囲内にある、指定したセルまたはセル範囲の参照が返されます。

指定された行と列が交差する位置にあるセルの参照を返します。セル参照が複数選択となる場合は、その中から任意の領域を選択できます。

（INDEX 関数には、セル範囲形式と配列形式の 2 つがあります。セル範囲形式の INDEX 関数は指定されたセルの参照を返し、配列形式の INDEX 関数は値または値の配列を返します。）

(書式2)配列形式　　　　書式：「 INDEX(配列,行番号,列番号) 」

行番号と列番号で指定されるテーブルまたは配列の要素の値を返します。

[配列数式]についてはこちらをご覧ください。

配列数式　その２

SUMIF 関数

ＭＳ－技術資料より

エクセルフアイルです。宜しかったら、ダウンロードしてください。(ウイルススキャン　すみ)

「SUMIF 関数使用の配列数式」例１

実用例

エクセルフアイルです。宜しかったら、ダウンロードしてください。(ウイルススキャン　すみ)

右の端、金額欄をクリックすると、数式バーに「数式」が表示されます。また、数式バーの適当な箇所をクリックすると、「配列箇所」が枠線で囲まれて表示されます。

「SUMIF 関数使用の配列数式」例２

INDEX、MATCH

ＭＳ－例題より

原文はこちらから

INDEX および MATCH ワークシート関数を使用し、条件を複数指定して値を検索する例

(1) =INDEX )

(2) $C$2:$C$5,

(3) MATCH(D2, )

(4) IF($B$2:$B$5=E2,$A$2:$A$5),

(5) 0

(6) ,1 省略

数式説明

(1)はINDEX 関数です。

(2)はINDEX 関数の引数1の「範囲」です。

　　　　　(INDEX 関数の 1 番目の引数が範囲のときは、範囲形式を使います。)

　　　　　範囲形式書式：「 INDEX(範囲,行番号,列番号,領域番号) 」

この関数は、指定された行と列が交差する位置にあるセルの参照を返します。

(3)はINDEX 関数の引数2の「行番号」ですが、MATCH 関数をネストして、その引数1の[検査値]が[D2]です,

　 INDEX 関数の引数3の「列番号」ですが、

　「範囲で指定した各領域が 1 行または 1 列である場合、

行番号または列番号はそれぞれ省略することができます。」

　により省略

(4)はMATCH 関数の引数2の[検査範囲]は「IF」文になっています。

　　　　　　　　　　　　　　　　　　$B$2:$B$5=E2, までが、条件式

$A$2:$A$5　は[TRUE]の場合で、[FALSE]は省略、

(5)はMATCH 関数の引数3の照合の型で、検査方法を指定しています。[0]は、

　　　　　　　　　　　　　　　　　　　　　　　　　　検査値に一致する値のみが検索の対象となります。

(6)INDEX 関数の引数4の「領域番号」ですが、

　最初に選択された領域の領域番号が 1 となり、以下2 番目の領域は 2、3 番目の領域は 3 と続きます。

　「領域番号を省略すると、1 であると見なされます。」により省略しましたのでで[1]となります。

ですから、上の表で「 =INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0)) 」とあるのを、

　　INDEX 関数の引数4を入力して「 =INDEX($C$2:$C$5,MATCH(D2,IF($B$2:$B$5=E2,$A$2:$A$5),0),1) 」

　　としても、同じ結果が得られます。

解説

[INDEX] 関数の引数1[範囲]で指定した配列の中から引数2[行番号]、引数3[列番号]で指定した「座標」の[値]を「ずばり」求めます。

その範囲(第１の)は、[C2]～[C5]です。

[MATCH] 関数では、指定したセルに含まれている値を求めるのではなく、一寸遠回りして、[検査範囲]内にある[検査値]に一致する[値]の「位置番号」を求めます。このようにして、選択肢を増やすことができるのです。というより、元々「条件を複数指定して値を検索する」解法でした。

[INDEX] 関数は行番号と列番号が必要ですが、この行と列の番号を[MATCH] 関数で下記のように取り出すのです。

検査値として、[D2]を設定します。

検査範囲は、[B2]～[B5]が[E2]と等しい場合、[A2]～[A5]になります。

領域は、第１の範囲になります。

検査値に一致する値のみが検索の対象となります。

前々ページ「常総線の運賃表」もご覧ください。

AVERAGE,IF

「配列数式」を使うと一辺にできます。(女子の場合、男子の数式をコピー貼り付けし[男]を[女]に替えてもできます。)

数式：「 {=AVERAGE(IF(B2:B8="男",F2:F8))} 」

手順

1. 回答を求めるセル[C10]をアクテイブにします。

2. 数式バーの[fx]ボタンをクリックします。(WinXPの場合)

3. [AVERAGE]を選択します。

4. 名前ボックス右の▼をクリックし[IF]関数を選択します。

IF数式パレット>に変わります。

5. [B2]から[B8]まで、ドラッグして選択します。B2:B8の後に="男"と直接入力します。

折り畳みたかったら、折り畳みボタンを押してパレットを小さくして作業します。

6. [Tab]キーを押して、下の「真の場合」に移動します。

7. 折り畳みボタンで、パレットを小さくします。

8. マウスで[F2]～[F8]までドラッグします。

9. 折り畳みボタンで、元にもどし、[OK]ボタンをクリックして入力は完了します。

(偽の場合は、省略可能なので、省略します。)

10. 数式バーの中を一旦クリックし、[Ctrl＋Shift＋Enter]キーを押しますと、数式が{　}で挟まれます。

11. 次が大事なことです。[Ctrl]キーを押しながら[Enter]キーを押して確定します。

私は、EXCELの確定時には、常にこの方法で確定しています。ちなみに、ショートカットキー[Ctrl＋Enter]は、データ入力後セルを移動しないで確定します。

12. 回答を求める[C10]に、「 =AVERAGE(IF(B2:B8="男",F2:F8)) 」と入力しても結構です。

13. また、数式バーに直接「 =AVERAGE(IF(B2:B8="男",F2:F8)) 」と入力しても結構です。

14. いずれにしても、最後は、[Ctrl＋Shift＋Enter]キーを押しませんと、配列数式になりません。キー入力では無効です。

SUM、IF 関数で

上の数式で[AVERAGE]を[SUM]に、範囲を[F2:F8]から[C2:E8]の空色部分に替えるだけで、求まります。

数式：「 {=SUM(IF(B2:B8="男",C2:E8))} 」

トップへ戻る　総目次へ戻る

関数総目次へ戻る　関数の勉強８へ戻る