カテゴリー別アーカイブ: エクセル関数

オートサムボタンの横関数 COUNT関数 MAX関数 MIN関数

オートサムのボタンの右に下向き三角がありそこには合計、平均、数値の個数、最大値、最小値、その他の関数と6つあります。合計はSUM関数、平均はAVERAGE関数を参照してください。その他の関数はこれら5つ以外のすべてなので、数値の個数、最大値、最小値の3つの関数をご紹介します。

MAX関数(最大値)
一連の引数のうち、最大の数値を返します。
=MAX(数値 1, [数値 2], …)

MIN関数(最小値)
一連の引数のうち、最小の数値を返します。
=MIN(数値 1, [数値 2], …)

エクセルでリンク集 HYPERLINK関数

ホームページ上のリンクテキストをコピーペーストすると自動でハイパーリンクが挿入されますが、HYPERLINK関数をつかえば任意のリンク集が作成可能です。

リンクテキストを選択しコピーします。

エクセルに貼り付けます。

自動的にハイパーリンクが挿入されクリックでWebページが表示されます。

HYPERLINK関数

文字列の数値コード CODE関数 CHAR関数

文字列の先頭の文字の数値コード(10進数)を返す関数がCODE関数です。

さて、何のことかよくわかりませんね。さらに、使用頻度もほとんどないと思われます。

=CODE(文字列)

というような形になります。例えばアルファベットの大文字のAは

=CODE(A)で65という値が返ってきます。

アルファベットのAからZまでは数値コードが65から90まで連番で並んでいます。

数値で指定された文字を返すのがCHAR関数です。CODE関数の逆の働きです。

=CHAR(数値)

という形です。=CHAR(65)とすればAが返ってきます。

ここで無理やりCODE関数とCHAR関数を利用しようと思います。フィルを使った連続データの入力でご説明していますが、2つ数値を入力してオートフィルを使用するとその規則性に沿った数値が自動で入力されるというものがあります。

セルA1に1、セルA2に3と入力し2つのセルを選択し右下のフィルハンドルをドラッグすれば,5,7,9,11,13と自動的に入力される機能です。

画像はオートフィル後に再度セルA1からセルA2を選択しています。

これをアルファベットのAとBで試してみます。

残念ながらABCDEFGとはならずにABABABとなってしまいました。そこでCODE関数とCHAR関数の出番です。

まずCODE関数でアルファベットのAの数値コードを調べます。セルC1に=CODE(B1)(=CODE(A))と入力します。65が得られます。

セルD1に65セルD2に66と入力し、オートフィルをします。

65から始まる連番が得られます。これはアルファベットのAからの数値コードの連番と同じなのでこれをCHAR関数で文字に戻します。

セルE1にアルファベットのAの数値コードの入っているセルD1を参照するCHAR関数、=CHAR(D1)と入力します。これをフィルハンドルのダブルクリックでE列にコピーしておけばアルファベットのAからZを入力することができます。Zは90なので65から90までのれんばんが要ります。

アルファベットの小文字のa-zは97から122の数値コードとなっています。

エクセルヘルプから開くマイクロソフトのCHAR 関数のページではCHAR関数の引数は1-255までの数値となっていますが、5桁の漢字などの数値コードでも上手くいくようです。

また、数値コードはANSIというウィンドウズが使っている文字セットに対応するものとなります。

残念ながらひらがなは、促音や濁音が連番の間にありますのでアルファベットのときのようにはいきません。

あいうえおかきくけこのような連続データを作りたい場合はオートフィル4でご紹介したユーザー設定リストにいったん手作業で書き込んでおくのがいいと思います。

セルの数式を表示する FORMULATEXT関数

下の図はキーから値を抽出 VLOOKUP 2

の時の図と同じものです。VLOOKUP関数で第4引数をTRUEに設定したところです。TRUEの場合は、完全一致で検索し、一致するデータがない場合は、検索値未満の最大値が使用されます。画像の数式バーにはセルB2の数式が表示されています。

下のように値引き額お支払金額も数式で算出しておりこれを1画面で見てもらいたい場合はちょっと困りますね。

こんな時はファイルのオプションの詳細設定から計算脚気の代わりに数式をセルに表示するにチェックを入れます。

表示が左揃えになって少しおかしくなりますが計算式が表示されてわかりやすいです。いちいちオプションの詳細設定は面倒なのでCTRLキー+Shiftキー+@でオンオフできます。

これはこれでいいのですがこの状態だと計算結果が分かりません。なので3行目に文字列として式を入力しておく方法もあります。半角でシングルクォーテーションを入力後式をコピペしておけば文字として認識されて式が直接表示されます。

FORMULATEXT 関数
数式を文字列として返します。
=FORMULATEXT(参照)

セルB3に=FORMULATEXT(B2)と入力します。セルD3までオートフィルでコピーします。

B3には=FORMULATEXT(B2)と入力していますが表示されているのセルB2の式が表示されています。使う機会は少ないかもしれませんがとっても便利です。

ただし、Excel2013から追加された関数のようなので(エクセル2010も2007も使えませんでした。)エクセルのバージョンに気を付けてご利用ください。

エクセルでじゃんけんゲーム MOD関数

じゃんけんゲームはちょっと大げさですが、エクセルとじゃんけんで勝負したいと思います。マクロやVBAなどは使いません。

じゃんけんはご存じない方はいらっしゃらないでしょう。お互いにぐー、ちょき、ぱーのいづれかを選んで勝ち、負け、引き分けを決めます。

ぐーはちょきにに勝ち、ちょきはぱーに勝ち、ぱーはぐーに勝つという三つ巴ですね。もちろん同じものどうしは引き分けです。

エクセルで実行するのでぐーのままでは計算できませんのでぐー、ちょき、ぱーを数値化しておきます。

仮に
ぐーを1
ちょきを2
ぱーを3
としておきます。

自分がぐーを出したいと思えば、所定のセルに1と入力します。その後エクセルが乱数を使って1か2か3の数値を所定のセルに出力します。

そしてそれぞれのセルの値を参照して勝か負けか引き分けを表示してあげれば出来上がりです。

まず、自分がぐー、ちょき、ぱーの3通り、エクセルがぐー、ちょき、ぱーの3通りあるので都合9通りのパターンがあります。IF分のネストでできそうな気がします。ネストの限界超えてますか。それ以外の関数でも9パターンの総当たりができそうですね。

とりあえず、関数の使用は横に置いて単純に数値で考えたいです。自分の数値とエクセルの数値が同じ場合引き分けです。引き分けの3つのパターンは自分の手の数値からエクセルの手の数値を引いて0の時になります。

もし、自分がぱー(3)でエクセルがちょき(2)なら
3-2=1
なので自分の手から空いての手を引いて1なら負けでいいでしょうか。

もし、自分がぱー(3)でエクセルがちょき(1)なら
3-1=2
なので自分の手から空いての手を引いて2なら勝ちでいいでしょうか。

表にしてみました。自分の手がC列です。エクセルの手が3行目です。自分手からエクセルの手を引いてみました。価値の場合は青、負けは赤引き分けは塗りつぶしなしです。

計算結果が-2,-1,0,1,2の5パターンに減りました。しかし、じゃんけんは勝か負けか引き分けの3パターンです。

これを何とか3パターンにしてみたいです。数字を3で割ったときのあまりだけを考えるとすべての数値(整数)は3で割ったときのあまりは0か1か2の3つに分けられます。

自分の手からエクセルの手を引いた数値を3で割ったときのあまりを計算してみます。

MOD関数
数値を除数で割ったときの剰余を返します。 戻り値は除数と同じ符号になります。

時間計算3 FLOOR 関数

時間計算の流れ的に、勤務時間の表を作っていましたので、もう少しだけ勤務時間の表に必要と思われる計算をしてみます。

退勤時間から出勤時間を引いてさらに休憩時間を引くと勤務時間になるというのは既にやってきました。実際の勤務時間を計算する場合、何かの単位で丸めを行うと思います。

30分未満は、切り捨てとか、15分未満は切り捨てとかです。累計に対しての丸めだったら目で見て入力しなおしてもそう手間はかからないかもしれませんが、大抵は日々、丸めが発生すると思います。今回は15分未満は切り捨てという丸め処理を行います。

hour_21退社時間が8:08なら15分未満なので勤務時間を8:00にしたいと思います。

FLOOR 関数

FLOOR(数値, 基準値)

数値、基準値ともに必須です。数値を基準値の倍数で0に近い(少ない)方に丸めます。

A列の値をセルB1の値3で丸めた結果です。数式は

=FLOOR(A2,$B$1)

です。4.7は3を基準値にすると3の倍数の6より小さく3より大きいので3以上6未満が切り捨てられて3となります。

hour_20

excel2013にはfloor.mathという新しい関数があります。第1引数が負の場合振る舞いが変わるようです。おそらく、今後はfloor.math関数に統一されていくのでしょうが(単なる推測です。)、後方互換のため当分floor関数も使えると思います。

今回はfloor関数を使用します。FLOOR(数値, 基準値)の数値は勤務時間を計算する数式退社時間 – 出社時間 – 休憩時間ですね。基準値は15分ですので、0:15とします。ただし、今回は15:00をダブルクォテーション( ” と ” )で括ってやります。

hour_22

途中まで式が入っていた場合は手入力で追加が早いかもしれませんが、一から入れなおすならば数式タブの関数ライブラリの数学/三角にFLOOR.MATH関数(excel2013以前はありません)があります。エクセル2013でFLOOR関数を関数ライブラリから使うにはその他の関数の互換性の中にあります。

hour_23FLOOR.MATH関数もFLOOR関数も数値がマイナスでないときは同じなので引数は同じものとなります。FLOOR.MATH関数の第3引数のモードは省略可能ですので空白にしておきます。

hour_24

あとはこれをうまくコピーしてあげれば丸め処理の完了です。

逆に切りあげるばあいはCEILING関数というのがあります。

関数の挿入基本

四則計算以外は関数を利用することが多いと思います。今更ながら、関数の出し方です。

まずはオートサムのボタンの右隣の三角▼をクリックしてその他の関数から関数を選択する方法です。この方法は、よく使うと思われる関数5つがすぐに使えるところが便利です。そのかわり、その5つ以外はその他の関数から探さないといけません。(以下図はExcel2013)

ホームタブの編集にΣ(シグマ)のマークがあります。合計、平均、数値の個数、最大値、最小値の5つから選択できます。

function

この5つ以外の関数はその他の関数をクリックすると関数の挿入のダイアログボックスが表示されます。

 

function_1

数式バーのfxのボタンをクリックすると始めからその他の関数のダイアログが表示されています。オートサムの右の三角をクリックしたときの5つの関数以外を使う場合は少し便利です。 function_2

数式タブの関数から、使用する関数のカテゴリーから選択する方法もあります。これは、使いたい関数がどこに分類されているか覚えている(見当がつく)場合は素早く関数を呼び出せます。やりたいことが解っているならおおよそうまくいくと思いますが、どこに分類されているかわからない場合は不便です。

function_3

セルにイコールを手入力してから名前ボックスの右の三角(▼)をクリックすると最近使った関数が10個表示されますし、一番下にはその他の関数もあります。これが一番便利かもしれません。function_4

 

 

 

 

 

関数のヘルプが表示されない

エクセルで関数の使い方の詳細を調べる場合関数のヘルプを参照するとよくわかる場合があります。

help

関数のヘルプが表示され説明や書式などから詳しく関数の詳細を知ることが出来ます。

help_1

しかしながら、同じようにこの関数のヘルプをクリックしてもサポートが必要ですか?と表示されて、上記のようなヘルプが表示されないことがあります。

help_2

検索欄に関数名を入力し検索すればそれらしいものが表示されますが、最初に挙げた関数のヘルプの内容とはかなり違います。これはオンライン(インターネット接続)で検索した結果のようです。

help_3

 

 

最初のようなヘルプを表示したい場合はExcelのヘルプと書かれているところの右の三角(▼)をクリックしコンピューターのExcelのヘルプをクリックします。

help_4

Office.ComのExcelのヘルプっていらない気がするのですが。

平均 AVERAGE関数

AVERAGE関数

引数の平均を返します。

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

数値1は必須で、数値、セル参照、セルはんを指定できます。

以下のような表があった場合各個人の平均値を求める場合、AVERAGE関数を使用すると簡単です。(以下図はExcel2013)

average

AVEREGE関数はオートサムのボタンの右の三角(▼)をクリックすると平均と表示されていますのでクリックします。

average_1

平均を求める範囲が囲まれて表示されていますので確認してEnterキーを叩いてください。

average_2

ただ気を付けていただきたいのが、下の表のように各科目と平均の間に合計などの平均に関係のない列や行が間にある場合は正しい範囲が選択されません。

average_4

マウスポインターが白の十字の形をしている状態でドラッグして平均を求めたい範囲を選択しエンターキーを叩きます。

average_5

これで正しく平均値を求めることが出来ました。average_6

シート数を調べる SHEETS関数 INFO関数

エクセルファイルで沢山のシートを使用すると現在総シート数はいくつなのか知りたいことがあるかもしれません。

そこでシート数を数えてくれる関数のご紹介です。

SHEETS関数
範囲内のシート数を返します。

引数は取れますが、複数シートにまたがる範囲につけた名前だと思われますのでブックのすべてのシートの数が欲しいときは引数なしで

=SHEETS()

としてください。ただし、excel2013からの関数のようでエクセル2010では#NAME?のエラーが表示されます。

エクセル2007以降(2003でも使えます)で使用できるものとして

INFO関数があります。

=INFO(“NUMFILE”)

として使用します。引数はほかにも取れますが、シート数以外の情報が取得できます。

ただし、私の環境だけかもしれませんが、excel2013では、INFO関数でシート数を取得する場合は同時に開いている別のエクセルファイルのシート数もカウントするようです。エクセル2010では別のブックの影響を受けませんでした。