いろいろな関数　８−２−２

主な内容
１．名前の定義　 ： (セルに名前を付ける)
２．入力規則 　　： (リスト入力の設定)
３．Excel表示形式
４．ユーザー定義の表示形式の作成について ： (数値の書式記号)
５．条件付の書式 ： (色の書式記号)-(日付や時刻の書式記号)-(文字列やスペースの書式記号)

名前の定義

セルに名前を付ける

セルを参照する見出しの使用（セルに名前を付けない場合でも）

セルを参照する見出しの使用 ワークシートのデータを参照する数式を作成する場合、 列(または行)の見出しを使用してデータを参照できます。

どういうことかと言いますと、例えば、下記表が有ったとします。
列(または行)の見出しを使用してデータを参照できるのです・

ところで、既定の設定では、数式内の見出しは認識されません。
数式で見出しを使用する場合は、次の設定を必要とします。
[ツール] メニューの [オプション] をクリックし、[計算方法] タブをクリックします
ブック オプション] の [数式でラベルを使用する] チェック ボックスをオンにします。

　　　Ａ　　　　　Ｂ
１　月別　　　　売上高
２　４月　　　　230,000
３　５月　　　　300,000
４　６月　　　　250,000
５　　　　　　アクティブ セルに

[B5]または、数式バーに「 =SUM(売上高) 」を入力すると[B2:B4]の合計が得られます。
注意：「売上高」を次に説明する「名前を定義」しなくても、結果が得られ、逆に、「名前を定義」すると結果が得られません。

１．「見出し」または「セル」に名前を付ける。
２．「選択範囲」に名前を付ける。

数式で使用する見出しと名前について
通常、ワークシートの各列の一番上と各行の一番左には見出し (ラベル) があり、データの内容を 説明しています。
これらの見出しを数式で使って、関連するデータを参照することができます。
ワークシートの見出し以外にも、データの内容を表す名前を作成して、セル、セル範囲、数式、 または定数を参照することができます。

１．の場合の設定の仕方

1. 名前を付けたい「見出し」、または「セル」を選択します。
2. 名前ボックスの▼をクリックします。
3. そのまま「付けたい名前」を入力します。
4. [Enterキー]を１〜２度押して完了です。
   削除は下記リスト４の（注：削除もこのボックス・・・）をご覧ください。
   
   その他にも設定があります。既定の設定では、数式内の見出しは認識されません。
   数式で見出しを使用する場合は
5. [ツール] メニューの [オプション] をクリックし、[計算方法] タブをクリックします
6. ブック オプション] の [数式でラベルを使用する] チェック ボックスをオンにします。

見出し、セルに名前をつける効果１
セルを参照する見出しの使用 ワークシートのデータを参照する数式を作成する場合、 列(または行)の見出しを使用してデータを参照できます。

どういうことかと言いますと、例えば、Sheet1の項目（見出し）の「売上高」に名前を定義しておくと、他のSheet（同一Sheetではエラーになります。）に
次の数式を入力しますと「 =SUM(売上高!C20:C30) 」合計額が参照できるのです。（返されるのです。）
「!」の記号は「〜セルを参照」の意味のようです。（半日がかりで、MSヘルプや他のEXCEL関係ホームページ１００個近く当たりましたが、探し方が下手なのか、回答がみつかりませんでした。）

見出し、セルに名前をつける効果２

例：(注釈：年月日各セルには「次ページ下段の表」で示すような 「DATE関数」の設定はなくてただの数値です。）

[A1]セルに「年表示」[A2]セルに「月表示」[A4]セルに「日表示」と名前を付け、
[C1]セルで「=年表示/月表示」の数式を実行した時[1002]の戻り値があった図です。
読みづらいセル番地でなく「日常語」が「数値」として使えるので「膨大なデータの場合」有効です。

２．の場合は「セル範囲（複数セル）に対し、セル範囲を選択することをしなくても、名前で選択ができるのです。

「名前の定義」の設定　その１

1. 「名前を付けたい」セル範囲を選択します。
2. メニューバー[挿入(I)]⇒[名前(N)]をポイント⇒サブメニュー[定義(D)]をクリックします。
   「名前の定義」ダイアロクボックスが現れます。
3. 上段の小窓に出ている文字を削除します。
4. その後に「付けたい名前」を入力します。
   （注：重要事項：削除もこのボックスで行います。削除は下の大きな窓の文字を選択し、削除ボタン、ＯＫボタンを押します。これ以外では削除ができません。）
5. ＯＫボタンを押します。

「名前の定義」の設定　その２（次ページで使用する方法です。）

1. 「名前を付けたい」セル範囲を選択します。
   　（「名前ボックス」に直接記入します。）
2. 名前ボックスの▼をクリックします。
3. アクティブの状態になります。
4. そのまま、「付けたい名前」を入力します。
5. [Enterキー]を押して完了です。

使用例２．上図で[A1]から[A4]を範囲選択します。名前ボックス▼をクリックし、アクテブになったところで「売上」と入力して[Enterキー]です。（セル範囲に名前が付けられました。）
試しに[A5]セルを選択し[Σ]ボタンを押しますと「1」と返ってきます。[A3]が空白セルだからです。
そこで、[A5]セルに「 =SUM(売上) 」と入力してください。[5]と返ってくる筈です。これは、名前で範囲選択したことを意味しています。

利点：「データの内容を表す名前」を数式で使用すると、「数式の計算目的が分かり易くなります。」
たとえば、数式 =SUM(第一四半期売上) は、上で取り上げた数式 =SUM(売上高!C20:C30) よりもわかりやすい数式に なります。"第一四半期売上" は、"売上高" というワークシートのセル範囲 C20:C30 を参照しています。

特徴１．名前を付けたセルは「絶対参照」表示になるので、数式のコピーミスも防げます。
特注２．book内のどのSheetでも通用します。例えばSheet５「名前ボックス▼をクリックし」「売上」をクリクすると、たちまち、[Sheet１]の「A1]セルに戻ります。ジャンプ機能もあるのです。

入力規則

入力規則

「入力規則」を設定しておけば、セルに入力できる値を一定の種類や範囲に制限することができます。

リスト入力の設定

1. リスト入力を行いたいセルを選択します。
2. メニューバー [データ]⇒[入力規則]をクリックし、[設定]タブをクリックします。
3. 下図 [データ]→[入力規則]をクリックし、設定のタブをクリック
4. [入力値の種類]の中から[リスト]をクリックします。

データ入力規則の図

5. （図では日付・時刻の陰になっていますが） [元の値]の小窓です。
   そこに入力できるデータを記入します。複数個の場合は「,」カンマで区切ります。
   たとえば、或る会社の社員名簿の統一を図るため、項目欄（行を選択）に「氏名、年齢、所属部署、郵便番号、住所、電話番号」のみの制限を掛け、更に所属部署（下の行から列を選択）には改めて「企画、総務、経理、製造、営業」というデータしか入力できないように制限する。
6. 制限が設定されたセルをクリックしたときに入力項目のリストからデータを選択できるようにするには、
   [ドロップダウン リストから選択する] チェック ボックスをオンにします。
7. [ＯＫ]ボタンを押して設定完了です。

制限されたデータ以外のデータを入力すれば「入力した値は正しくありません」と拒否」され、入力ミスは防げます。
また、フィルハンドルで「行」「列」夫々をコピーすると「セル」クリックするごとに▼ボタンが現れ、それをクリックすれば、データ一覧がでますので、その中からデータを拾い出すことができます。マウス操作で入力できることになります。
（シリーズ８−３の場合ですと、「元の値」小窓に「=年リスト」と入力します。）

Excel表示形式

１．[標準] 表示形式

新規ワークシートでは、すべてのセルに [G/標準] の表示形式が設定されています。 通常 [G/標準] の表示形式では、入力された数値はそのままの書式で表示されます。
ただし、セル幅が数値全体を表示するのに十分でない場合は小数が四捨五入され、また、大きな 数値は指数で表示されます。[G/標準] の表示形式では、数値は 11 桁まで表示されます。

２．組み込みの表示形式

Excel では多くの組み込みの表示形式から選択することができます。これらの表示形式の一覧を表示す るには、[書式] メニューの [セル] をクリックし、[表示形式] タブをクリックします。 [会計]、[日付]、[時刻]、[分数]、[指数] 、[文字列] などの表示形式が、左側の [分類] ボックスに 一覧表示されます。[その他] 表示形式には、郵便番号や電話番号の書式があります。各分類のオプシ ョンは [分類] ボックスの右側に表示されます。

数値の表示方法を変更する
Excel では表示形式を使用して、日付や時刻を含む数値の表示方法を変更することができます。 表示方法を変更しても、!!元の数値が変更されることはありません。!! 例えば、.08 という数値 を 8% と表示できます。

変更方法
表示形式を変更するセルを選択します。
[書式] メニューの [セル] をクリックし、[表示形式] タブをクリックします。
[分類] ボックスの一覧からアイテムをクリックし、必要に応じたオプションを選択します。
※希望どおりのオプションが見つからない場合は、下記、ユーザー定義の表示形式を作成します。

３．ユーザー定義の表示形式

使用する書式が組み込みの表示形式にない場合は、書式を組み合わせてユーザー定義の表示形式を作成することができます。
作成された表示形式はブックのすべてのシートで使用することができます。
新規ブックの初期状態では、組み込み表示形式だけが使用できます。

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

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

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;"データ : "@　　3月    　データ : 3月
　　　　　　　　　　　　　　　　　　　　　　　　123            123.00
　　　　　　　　　　　　　　　　　　　　　　　 -123          (123.00)
　　　　　　　　　　　　　　　　　　　　　　　　　0             0.00

数値の書式記号

数値、日付や時刻、通貨、パーセンテージ、指数、文字列やスペースの表示形式を指定します。

小数点以下の桁と有効桁数

• 分数または小数点が付いた数値の書式を設定するには、次の書式記号を指定します。
  小数部に指定した書式記号の数よりも小数部の桁数が多い場合は、書式記号の数に合わせて小数部の桁数が四捨五入されます。
  
• また、整数部に指定した書式記号の数よりも整数部の桁数が多い場合は、多い分の桁も表示されます。
• 整数部に数値記号 (#) だけが含まれている場合は、1 以下の数字が小数点の右に表示されます。

その他、留意点

• 小数点以下の桁数を揃えるためには、桁数分だけの「0」を使います。
• 整数部は「#」１個でもＯＫですが出来るだけ「#」を使います。
• 「?」は小数点以下の桁数を揃えるとき、小数点の位置を揃えた上、以下の桁数分だけ「0」の代わりに並べます。

使用例

　表示形式　　　　　　　　入力　　　　　　　表示
-------------------------------------------------------------------
 ####.#　　　　　　　　　1234.56　　　　　1234.6
  #.000　　　　　　　　　1234.56         1234.560
  0.#　　　　　　　　　　　　.123　　　　　　0.1
  #.0#　　　　　　　　　　 12　　　　　　　 12.0
　#.0#　　　　　　　　　 1234.567　　　　 1234.57


  ???.???　　　　　　　　　12.345　　　　　12.345　　* 桁を揃えて表示
　　　　　　　　　　　　　　 12.34　　　　　12.34　　 * 桁を揃えて表示
　　　　　　　　　　　　　　　12.3　　　　　12.3　　　* 桁を揃えて表示

　# ???/???　　　　　　　　　5.25　　　　　5 1/4
　　　　　　　　　　　　　　　5.3　　　　　 5 3/10　　*除算表示を替える

上記記号を直接「記入窓」に打ち込んだ場合です。下窓の記号を利用し、訂正した場合には上手く行かない場合が 生じることがあります。（私のExcel2000では）
小数点以下の桁数が長い場合「???.???」の最後の?で四捨五入されます。必要があればもっと右側へ増やします。 整数部は余り影響がないようです。
# ???/???の場合「# ??/??」と「# ?/?」は標準で用意されています。？が多いと整数部と分数部の間隔が広がります。

桁区切り記号

[,]カンマを 1000 単位の区切り記号として表示(下表１行目)したり、 数値を 1000 で割って小数部を四捨五入して表示（下表２行目）するには、カンマを指定します。

桁区切り記号の書式記号カンマを 1000 単位の区切り記号として表示したり、数値を 1000 で割って小数部を四捨五入して表示するには、カンマを指定します。

　表示形式　　　　　　　　入力　　　　　　　 　表示
----------------------------------------------------
  #,###　　　　　　　　　 12000　　　　　　　12,000
  #,　　　　　　　　　　　12000　　　　　　　　　12
  0.0,,　　　　　　　　12230000　　　　　　　　12.2

３番目ですが、書式記号の末尾に「カンマ」が２つ付いています。１つの[,]が千単位、2つの[,]では「1/1,000×1/1,000＝1/1,000,000」ということで[12.2]と表示されました。
末尾[,]1個ではどうなるか、は「セル書式設定ー表示形式の図」に例示してあるように[12200.0]と表示されます。
なお、図では下窓の[0.00]を元に[0.0,,]としましたが、上手く行きました。

表示形式作成の基準は

ユーザー定義の書式記号は、一つの表示形式で、最高 4 つのセクションまで指定で、
各セクションはセミコロンで区切ります。

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

1 つのセクションを指定した場合は、その表示形式がすべての数値に適用されます。
セクションを省略する場合は、そのセクションの後ろのセミコロンだけを入力します。 このように最高で4つの定義をまとめて、一つの表示形式として設定できる。でした。

条件付の書式

条件値を角括弧で囲むことによって、表示形式の各書式に条件を設定することができます。
条件は、比較演算子と値で指定します。たとえば、次の書式では、100 以下の数値は赤で、100 より大きい数値は青で表示されます。
[赤][<=100];[青][>100]
セルの値に応じて色の明暗などのセルの書式をワークシートに設定するなど、条件付きで書式を指定するには、 [書式] メニューの [条件付き書式] コマンドを使用します。

　　　　　　表示形式　　　　　　　　　　　　　　入力　　　　表示
--------------------------------------------------------------------
　[>10][青]#,##0;[<-10[赤]#,##0;[緑]#,##0　　　20　　　　　20  
　　　　　　　　　　　　　　　　　　　　　　　　-20　　　　　20 
　　　　　　　　　　　　　　　　　　　　　　　　　5　　　　　 5

例えば、上記の表示形式で最初の書式は 10 より大きい数値の表示形式、2 番目は -10 より小さい数値の表示形式、3 番目は 1 番目と 2 番目の書式で表示されないその他のデータの表示形式が設定されています。
<, >, =, >=, <=, <> などの記号を数値と組み合わせて使用することができます。

色の書式記号

表示形式で色を設定するには、色の名前 (以下の 8 色) のいずれかを角かっこ ([ ]) で囲んで入力します。 これらの色コードは書式部分の先頭に指定する必要があります。

例題をやってみます。
セル[A1]に[1234.567]をセル[A2]に[-234.5678]を入力し、２つのセルを範囲選択します。
正数を緑色、負数を赤色にするには「 [緑]#.###;[赤]-#.##0 」にすると、「 1234.567 」「 -234.568 」と返ってくる筈です。
ただし、[A2]を単独で行うと[-]は不用になり、両方を選択し一度で行うと入用になりました。？です。

日付や時刻の書式記号

　　　　書式記号　　　　　　　　　　　　入力　　　　　　　表示
---------------------------------------------------------------
  yy"年"mm"月"dd"日"               00/8/10          00年08月10日
  gge"年"mm"月"dd"日"(aaa)         00/8/10    平12年08月10日(木)
  ggge"年"mm"月"dd"日"aaa          00/8/10    平成12年08月10日木
  ge.mm.dd                         00/8/10            H.12.08.10
  mmmm d,yyyy                      00/8/10        August 10,2000
  mmm d,yyyy                       00/8/10           Aug 10,2000
  mmmm d ddd                       00/8/10         August 10 Thu
  mmmm d dddd                      00/8/10    August 10 Thursday
  hh:mm AM/PM                      13:45                01:45 PM
  h:mm                             25:02                    1:02
  [h]:mm                           25:02                   25:02
  [mm]:ss                          1:03:46                 63:46

日付や時刻の書式記号について（MSヘルプより）

年、月、日　　年、月、および日を表示するには、次の書式記号を使います。 "h" または "hh" の直後、あるいは "ss" の直前に"m" を指定し場合は、月ではなく分が表示されます。

時、分、秒　　時、分、および秒を表示するには、次の書式記号を使います。

表示形式に AM、PM、A または P の文字が含まれる場合、時刻は 12 時間表示で表示されます。
"AM" および "A" は午前 0 時から正午までの時間を表し、"PM" および "P" は正午から午前 0 時までの時間を表します。
これらの文字が含まれていない場合は、24 時間表示を基準にします。また、"m" または "mm" は、"h" または "hh" の直後、あるいは "ss" の直前に指定します。これ以外の位置に指定すると、分ではなく月が表示されます。

時間の＋算

左図３列とも
3:00
8:30
14:00
と入力しました。これの合計を求めたいのですが、
いきなりΣ(オートサム)では誤った回答になります。
なぜなら、合計が２４時間を超えていて、その、
２４時間が無くなり１時間３０分の回答でした。

このような場合には上の表の「書式記号」
25.02 など経過時間を時で表示　　　　　　　　　　[h]:mm
63:46 など経過時間を分で表示　　　　　　　　　　[mm]:ss
経過時間を秒で表示　　　　　　　　　　　　　　　[ss]
を使用、単位を統一しておかなければなりません。

パーセンテージ

数値に 100 を掛けてパーセント文字を追加した形式で表示するには、 パーセント文字 (%) を指定します。たとえば、.08 は "8%"、2.8 は "280%" と表示されます。

文字列やスペースの書式記号

文字列の書式部分は、常に表示形式の末尾に指定し、入力した文字列を表示する部分に @ 記号を指定します。
文字列の書式部分に @ 記号がない場合、入力した文字列は表示されません。
入力した文字列と共に特定の文字列を表示する場合は、表示する文字列をダブル クォーテーションで囲みます。
表示形式に文字列の書式がない場合、その表示形式はセルに入力する文字列には影響しません。

"一金"#,0"円也"
"一金"#,000"円也"
"一金"#,#"円也"
"一金"#,###"円也"

トップへ戻る     いろいろな関数目次へ戻る
いろいろな関数８-２へ戻る     いろいろな関数８-２-３へ進む