月別アーカイブ: 2019年2月

名前の定義(名前ボックス)1

どこかでご説明したとばっかり思い込んでいましたが、改めて見直すとそれらしいページのタイトルがありませんでしたので改めてエクセルのセル範囲につける名前についてご説明します。

エクセルではセルを表す場合にセル番地を使います。A1とか、A1:B5のような感じです。 R1C1参照形式という方法もありますが。

これとは別にセルに名前を付けることができます。

名前を付け方ですがセルまたはセル範囲(複数セル)を選択します。名前ボックスをクリックします。名前を入力します。

セルB2に税率という名前が付きました。これで数式でセルを名前で参照することができるようになります。

オートフィルで数式をコピーする際、セルに名前を付けておくと通常F4キーで絶対参照にするところをF4キーを使わなくてもよくなります

セル範囲(複数セル)に対して名前を付けることも可能です。

セルB3からセルB5に価格という名前を付けました。税額は価格×税率となりますのでセルC3の式を=B3*税率から=価格×税率に変更します。

セルB3もセルB4もセルB5もすべて価格という名前がついていますので、どのセルの値が計算対象か不安になるかもしれません。今回の 計算対象は同じ列の値になりますので正しい計算結果となります。

下図はセルB2からセルB4までに単価、セルC2からセルC4までに数量という名前を付けてあります。セルD2に=単価*数量という式を入力してオートフィルで下にコピーしてあります。

それぞれ同じ列の値をかけているので正しい答えが表示されています。また、適切な名前を付けた場合数式を見た際に数式の意味が把握しやすくなる事もあります。

リストから入力の際もセル範囲に名前を付けておくと便利です。

特に意味はありませんが、セルF2はプルダウンで値(単価から)選択できるようになりました。 エクセル2007ではシートを跨いでリストから入力する際は、名前を付けるしかありませんでした。

名前の管理は数式タブの定義された名前から行えます。数式を作成する際も数式の定義された名前から入力できます。もちろん直接文字入力しても構いません。

数式の中で名前を使いたい時は直接名前を入力してもいいですが、数式タブの定義された名前から数式で使用をクリックします。一覧が表示されますので使いたい名前をクリックします。

名前を付けるときの注意点ですが、同じブックの中で同じ名前は使用できません(例外あり)。また、名前の一文字目に数字は使えません。名前の1文字目は文字(多分アルファベットと日本語の多くの文字)、 下線 (_)、円記号 (¥) しか使用できません。それ以降は文字、数値、ピリオド、および下線を使用できます。大文字と小文字の “C”、”c”、”R”、”r” を1文字目にして2文字目意向を数値にすることはできないようです。おそらく R1C1参照形式と競合するからでしょう。同じ理由からA1などのセル番地を表すものも使用できません。エクセル2016では列番号はXFDまでありますので、NO1という名前はセル番地と競合するので使用できません。注意が必要です。文字数は255文字まで。大文字小文字は区別されません。参考数式内の名前

MATCH関数

MATCH 関数は、範囲 のセルの範囲で指定した項目を検索し、その範囲内の項目の相対的な位置を返します。

表現としてはわかりにくいですね。わかっていれば簡単です。以下の表でセルB1の値と同じものはセルA1からセルA4の中で上から数えて何番目かというのを出してくれる関数です。

算数は国語、算数、理科、社会の並びでは2番目にでてきますので2を返します。

MATCH関数
範囲 のセルの範囲で指定した項目を検索し、その範囲内の項目の相対的な位置を返します。
=MATCH(検査値, 検査範囲, [照合の型])

検査値は文字列、数値、真偽値が使えます。真偽値の場合はTRUEとFALSEを使ってください。1と0は数値です。

照合の型は省略可能です。-1、0、1 の数値のいずれかを指定します。 省略時は1が指定されたものとみなされます。0の場合は一致する値の位置を返します。 -1の時は検索値以上の最小値の位置 、1の時は検索値以下の最大値の位置を返します。-1の時は昇順に1の時は降順に検索の範囲の値を並べ替えておかなければ正しい位置が返されませんので注意が必要です。

下図は25より小さい数値で最大のものの位置を探しています。この際は検索範囲は昇順に並べ変えておく必要があります。

該当がなければ #N/A エラーが返されます。

MATCH関数は検査の範囲は縦だけではなく横向きに指定することも可能です。ただし、複数列、複数行は無理なようです。

また、該当する値が複数ある場合は先に見つかった方になります。

R1C1参照形式

通常あまりかかわりになることはないと思いますが、R1C1参照形式というものがあります。

通常エクセルでは列名はA,B,Cという感じでアルファベットで表示されていますが、 R1C1参照形式 というものを使えば列名が数値になります。

列番号が数値

ではどのようなときに使用するかですが、使用する局面は「ほぼない」といってもいいと思います。

VBAと呼ばれるスクリプトの中では使われることがあるようです。何列先とか今の列から5列分とかを指定するときアルファベットの場合は指折り数えないとわかりづらいですが、数値だと計算で簡単な場合があります。

特定の関数で R1C1参照形式 を指定できる場合もあるようです。

オプションの数式の数式の処理で R1C1参照形式 を使用するにチェックを入れると列番号が数値になります。

RはRow(行)でCはColumn(列)になるのでセルA1はR1C1で、セルB1はR1C2となり列と行の純が逆になります。更に通常の相対参照は数式の入っているセルからのオフセット位置(差し引き)になります。

セルE3からセルA1への参照は2行上の4列左という意味で=R[-2]C[-4]のようになります。計算式より上、左のセルは負の数となります。また下、右は正の数となります。同一列や、同一行の場合は数値は指定せず括弧もなくなります。

F4キーを使って絶対参照にした場合はかっこがなくなり=R1C1の様なセル番地の表記になります。

ちなみに R1C1の読み方としましては私は、「あーるいちしーいち」と発声していますが、VBAの中でFormulaR1C1プロパティというものがありこの場合は「フォーミュラーアールワンシーワン」ということもあり「 アールワンシーワン 」が正解かもしれません。