Excelの様々な関数の読み方や引数などを紹介。今回は、VALUE関数～VAR.S関数です。【dictionary】

Excelの様々な関数の読み方や引数などを紹介。今回は、VALUE関数～VAR.S関数です。

＜Excel関数辞典：VOL.87＞

今回は、VALUE関数～VAR.S関数までをご紹介しております。

VALUE関数

読み方： バリュー　　

VALUE(文字列)

文字列を数値に変換する　

VALUETOTEXT関数

読み方： バリュートゥテキスト　　

VALUETOTEXT(値,[書式])

値のテキスト表現を返す　

VAR関数

読み方： バリアンス　　

VAR(数値1,[数値2],…)

数値群を標本データとみなした不偏分散を算出します　

VAR.P関数

読み方： バリアンス・ピー　　

VAR.P(数値1,[数値2],…)

数値群を母集団全体とみなした分散を算出します　

VAR.S関数

読み方： バリアンス・エス　　

VAR.S(数値1,[数値2],…)

数値群を標本データとみなした不偏分散を算出します　

Excel。VLOOKUP関数で「&””」で0表示は消せるが、数値型だとうまくいきません。【numeric type】

Excel。VLOOKUP関数で「&””」で0表示は消せるが、数値型だとうまくいきません。

＜IFERROR+VALUE+VLOOKUP関数＞

VLOOKUP関数で、検索値はあるけども、抽出する値が無い場合には、「0（ゼロ）」と表示されてしまいます。

B2に設定した数式は、

=IFERROR((VLOOKUP(A2,$A$7:$B$9,2,FALSE)),"")

この数式を、オートフィルでコピーしています。

検索値が無い場合は、IFERROR関数を組み合わせることで、「#N/A」のエラーを消すことができます。

ただ、B3のように、検索値はあるけども、抽出する値が無い場合には「0（ゼロ）」が表示されてしまいます。

そこで、VLOOKUP関数のあとに「&””」をつけることで、非表示にすることができます。

B2の数式は、

=IFERROR((VLOOKUP(A3,$A$7:$B$9,2,FALSE)&""),"")

と修正して、オートフィルで数式をコピーしています。

これで、「0（ゼロ）」を非表示にすることができました。

さて、ここまでは、抽出する値が「文字型」だったので問題はなかったのですが、B7の値を「鉛筆」から、わざと「100」という数値に変更してみましょう。

B4の値は、「100」と表示されていますが、左揃えになっています。

つまり、数値型ではなく「数値文字型」という文字型に変わってしまっています。

「&””」はたしかに、「0（ゼロ）」を非表示にしてくれますが、数値型を「文字型」に変えてしまいます。

そこで、VALUE関数を追加することで、数値型のままにすることができます。

=IFERROR(VALUE(VLOOKUP(A2,$A$7:$B$9,2,FALSE)&""),"")

これで、数値型のままにすることができました。

しかし、数値型に対応した数式なので、この数式を使って、文字型を抽出しようとしても、表示することができません。

このようなことから、抽出するデータが数値の場合と文字の場合で「VALUE関数」の使い分けをする必要がありそうです。

なお、VALUE関数は、文字列として入力されている数値を、数値列に変換する関数です。

Excel。Slash。入力されている日付の”/”（スラッシュ）をなくして数値化したい。

Excel。入力されている日付の”/”（スラッシュ）をなくして数値化したい。

SUBSTITUTE関数とTEXT関数とVALUE関数

先日ちょっとした質問がありまして、日付の”/”（スラッシュ）をなくして、
数字化して使いたいんだけど、どうしたらいいかなぁ～とのこと。

要するに、2015/8/1を201581としたいそうなんですね。

だったら最初から、そのように入力すればよかったのにね。
と思いつつも、ちょっと考えてみましたので、今回はそれを紹介いたします。

下記の表があります。

C列の誕生日をD列に”/”（スラッシュ）をなくして、数字化していきます。

実は、コレなかなか、厄介な処理でして、そもそも日付は、シリアル値ですので、
見た目は、日付ですが、中身は数値なわけです。

ですから単純に、”/”（スラッシュ）を削除することや置換することが出来ないのです。

そこで、【日付文字列】に変換する必要がありますので、TEXT関数が登場してきます。

では、D3をクリックして、まずは、SUBSTITUTE関数ダイアログボックスを表示しましょう。SUBSTITUTE関数は、文字を置換することが出来る関数ですね。

文字列には、TEXT関数を挿入します。それでは、文字列のボックス内をクリックして、
TEXT関数ダイアログボックスを表示しましょう。

値はC3を入力します。
表示形式は、【日付文字列】にしますので、”yyyy/mm/dd”と入力します。

“ （ダブルコーテーション）を忘れずに、これを付けることによって、【日付文字列】に変換できます。

そして、どっちみちならば、
2015/8/1を201581とするのではなく、
2015/8/1を20150801と桁数を0（ゼロ）を使って揃えたほうが汎用性もアップするので、
mm dd　としております。

さて、まだ、SUBSTITUTE関数をつくっている途中でしたので、
SUBSTITUTE関数ダイアログボックスに戻りましょう。

文字列には、TEXT(C3,"yyyy/mm/dd")　と入力されていますね。
検索文字列には、"/"
置換文字列には、””
と入力しましょう。

そして、OKボタンをクリックしましょう。

あとは、数式をオートフィルハンドルを使ってコピーしましょう。

これで、完成ですね。日付から”/”（スラッシュ）をカットして数値化してみました。

C3の数式は、

=SUBSTITUTE(TEXT(C4,"yyyy/mm/dd"),"/","")

となります。

けど、これ数値化しましたが、
文字列なので、コードなどで使用するにはいいのですが、
この後に計算式に使いたいということになりますと、
文字列を数値に変えるVALUE関数を使う必要があります。

先程完成した数式にVALUE関数を加えてあげるだけですので、E3に数式を作ってみましょう。
数式は、

=VALUE(SUBSTITUTE(TEXT(C3,"yyyy/mm/dd"),"/",""))

ですね。
あとは、オートフィルハンドルを使って数式をコピーしましょう。

このように、関数の組み合わせで、色んなことができる例の一つですので、
挑戦してみてはどうでしょうか？