Excel(エクセル)講座 Q&A(Tips)：計算関連 4/4(文字列など)

文字列の数式を計算する　　　　　　Topへ

ユーザー定義関数を使う方法

1. 【Alt】+【F11】でVBEを開き、標準モジュールに
   Function KEISAN(R As Range)
   　　　KEISAN = Evaluate(R.Value)
   End Function
   と入力します。
2. A列には文字列として数式が入力してあります。
   B1セルに定義した関数「keisan」を使って、『=keisan(A1)』と入力すると計算されます。
   

四則演算子が全角で使用されている場合

1. かけ算に「×」、割り算に「÷」が使われている場合には、それぞれの記号を「*」「/」に置換します。
2. 上記のコードを下のように変更します。
   
3. シートでの使用例です。
   

EVALUATEを名前定義で使用する方法

1. 挿入→名前→定義　で
   • 名前：keisan2
   • 参照範囲：=EVALUATE(Sheet1!A1)+NOW()*0
   として、追加します。
   
2. A列には文字列として数式が入力してあります。
   B1セルに定義した関数「keisan2」を使って、『=keisan2』と入力すると計算結果が表示されます。
   

文字列から数字を取り出す　　　Topへ

• 文字と数字が混在している文字列から数字を取り出す。

• 	Ｂ	Ｃ	Ｄ	Ｅ
  2	12円	12	約20	20
  3	25冊	25	Ｎｏ120	120

  • C2：=IF(COUNT(LEFT(B2)),LOOKUP(10^16,--LEFT(B2,ROW($1:$16))),"")
  • E2：=IF(COUNT(RIGHT(D2)),LOOKUP(10^16,--RIGHT(D2,ROW($1:$16))),"")
    • ROW($1:$16)の部分は、{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16}と配列になっています。
    • LOOKUP(検査値,配列)で検査値「10^16」は数値と認識できる値の最大値の意味で、検査値が見つからないと、配列の先頭行または先頭列に含まれている検査値以下の最大の値が返されます。
      よって、数字の文字数を得ることができます。

列/行の最終値を求める

• セル範囲の最終値を求める
• (例1)セル範囲A1:A10の一番下の行に入力されている数値を求める
  • =LOOKUP(10^16,A1:A10)
• (例2)セル範囲B1:B10の一番下の行に入力されている文字列を求める
  • =LOOKUP(10^9,CODE(B1:B10),B1:B10)
• 

SUM関数の答えが合わない？

• 加算の数式とSUM関数の数式とで答えが異なることがあるという例です。
• 滅多にないとは思いますが、こんなこともあるということで・・・

• (問題)
  • 下図のようにB1セルには『=SUM(A1:A4)』　B2セルには『=A1+A2+A3+A4』と数式が入力されています。
  • ところが、B1は「70」、B2は「100」と異なった値が表示されています。なぜ？？
    
• （解答)
  • C1セルに『=TYPE(A1)』と入力し、C4セルまでコピーします。
  • すると、C3セルは「2」となり、文字列になっているのが分かります。
  • つまり、SUM関数は文字列は無視しますが、「+」を使った加算では計算できる文字列は数値と見なして計算をする為です。
  • 
  • ※　この例では、一見しては、文字列なのか数値なのか区別できないように、
    A3セルの表示形式を文字列にして右揃えしています。　　

値の比較が思うようにならない　　　Topへ

• 記号や空白を含む全ての文字は数値よりも大きいと判断されます。
• 文字と文字の比較は、文字コード番号を基準に行われます。また、記号や空白も同様にコード番号によって大小の比較が行われます。
• 「""」は 0 バイトの文字列 (Null 値) として認識され、全ての数値よりも大きく、全ての文字よりも小さいと判断されます。
• 【参考】
  • [XL2000]比較演算子による比較結果について
    http://support.microsoft.com/kb/416983/JA/
  • [XL2000]数字の入力方法と並べ替え順序
    http://support.microsoft.com/kb/412061/JA/

　PageViewCounter
　
　Since2006/2/27