「エクセル関数」カテゴリーアーカイブ

INDEX関数

INDEX 関数はテーブルまたはセル範囲にある値、あるいはその値のセル参照を返します。

セル範囲の中で何行何列目かを指定するとその値を返してくれます。マイクロソフトさんのエクセル関数の説明は毎度難しいですね。正確に記述するためには仕方ないのでしょうけれど。

下のような表で吉田さんの理科の得点を取得したい場合、点数の部分をセル範囲とすると(セルB2からセルE7)2行目の3列目が吉田さんの理科の点数になりますね。INDEX関数を使えばセル範囲と何行何列目が分かればセルの値が取得できます。

INDEX関数
行番号と列番号で指定されるテーブルまたは配列の要素の値を返します。
INDEX(配列, 行番号, [列番号])
INDEX(参照, 行番号, [列番号], [領域番号])

同じ関数なのに2通りの書き方があります。ちょっとややこしいですね。上図の様に関数の挿入でINDEX関数を選択しても更にどちらかを選択するよう求められます。しかし、一応のところ同じものだと思ってもらっていいかと思います。2つの定義で違うところは、最初の引数の配列と参照ですね。あとは領域の第4引数を取るかとらないかです。

第1引数の参照はセル範囲のことですね。上図ならセルB2:セルE7になります。このデータを引っ張り出したい表が1つの場合は引数の少ない方の INDEX(配列, 行番号, [列番号]) を選択しておけばよいでしょう。ふつうはこちらですね。

吉田さんの理科の点数をINDEX関数で求める際の第1引数としてセルB2からセルE7を選択します。第2引数は選択したセル範囲の2行目にあたるので2とします。第3引数は3列目になるので3とします。

=INDEX(B2:E7,2,3)

では、配列とは何のことでしょう。突き詰めると私も自信がありませんが、セル範囲以外に配列定数 が取れるということでしょう。エクセルの表にないデータを直接数式に入れられるということです。

上の例で行けば各自の成績を直接配列定数としてINDEX関数の中に入れます。

=INDEX({88,78,79,86;85,81,85,95;69,73,86,82;74,55,72,69;58,93,82,87;91,100,94,49},2,3)

B2:E7 が生のデータになっています。ここの部分が配列定数です。

INDEX関数は2つ種類があり、基本的に同じものだと理解しているのですが、4つ目の引数の領域のご説明がまだですね。これは、第1引数を複数指定した場合、その複数のどれですかということです。

=INDEX((B3:E8,B12:E17),2,3,2)

第1引数を小かっこで括ってセル範囲とセル範囲をカンマで区切ります。上図の場合は中間試験結果であるセルB3からセルE8までを領域1とします。期末試験結果であるセルB12からセルE17までを領域2とします。(B3:E8,B12:E17)が第1引数となります。第2引数はどちらの領域化は別として領域の2行目、第3引数は領域の3列目となります。第4引数が1の場合は領域1の方なので セルB3からセルE8まで の2行3列目の85ですが上の式の第4引数は2となっています。なので第2領域の セルB12からセルE17の2行3列の65となります。

第4引数を取る INDEX(参照, 行番号, [列番号], [領域番号]) の方は第1引数には複数の領域を設定できますが、配列定数を使い場合には複数の領域は設定できません。

COUNTA関数

範囲内の空白ではないセルの個数を調べる関数。オートサムボタンの横関数 COUNT関数 MAX関数 MIN関数のCOUNT関数は数値のセルを調べます。

文字、数値、エラーメッセージ、数式、空白文字を数えます。空白以外をカウントするの方が正確かもしれません。

セルE1 には=COUNT(B1:B8)が入力されています。COUNT関数は数値だけを数えますので数値の123と日付の2月20日とゼロの3つがカウントされています。日付は文字列に見えますがデータとしては数値ですのでカウントされています

セルE2には=COUNTA(B1:B8)が入力されています。COUNTA関数は空白以外を数えます。空白文字のセルB6には=””が入力されていますのでカウントされます。

上図はセルに数式を表示するの方法で数式を表示しています。空白以外は何かしらの値が入っていますね。

COUNTA関数はデータが入力されている件数を調べるために使われることが多いです。また、ほかの関数の中で使われることもよくあります。

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

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

条件を指定してカウントするCOUNTIF関数

数値を含むセルの個数をカウントする場合はオートサムボタンの横関数 COUNT関数 MAX関数 MIN関数を参照してください。

今回はある条件を満たしたものだけをカウントするCOUNTIF関数のご紹介です。

例えばアンケート集計をエクセルで行いたい場合があるか無いかは分かりませんが例にしようと思います。手集計をしたものをエクセルで加工する場合があるかもしれませんが、今回は生データをエクセル上に入力してからエクセルで集計してみます。

5段階評価で、楽しかったか、役に立ったか、また来たいかの3項目についてアンケートを取りました。匿名アンケートなのでアンケート用紙の通し番号と5段階の数値をデータとして入力しました。

5が満足度が高く1が低いとしておきましょうか。

この表から各項目に5と評価したした人が何人いて4と評価した人が何人いてという風に、各項目に対する評価の人数を関数で引っ張り出したいですね。

このような集計表になるでしょうか。楽しかったかの項目に1の評価とした人の合計をセルG3に集計してあげればよいでしょう。このような場合にCOUNTIF関数を使います。

答えを出したいセルG2を選択しCOUNTIF関数を選択します。

COUNTIF関数
1つの検索条件に一致するセルの個数を返します。

COUNTIF(範囲,検索条件)

範囲は集計元のデータのある範囲です。今回は楽しかったかの項目で1と答えた人の数が欲しいので楽しかったかの項目のデータ範囲であるセルA3からセルA17となります。

検索条件は1と答えた人の1となりますのでセルF3となります。

=COUNTIF(B3:B17,F3)

という数式になりますが、あとでオートフィルで指揮をコピーした場合範囲のB3:B17がずれてしまうのでF4キーで絶対参照にしておきます。

=COUNTIF($B$3:$B$17,F3)

このような式にしておけばオートフィル後も正しい結果を表示してくれます。あとは同じことを役に立ったかのセルH3,また来たいかのセルI3ですればよいでしょう。

セルH3には
=COUNTIF($C$3:$C$17,F3)

セルI3には
=COUNTIF($D$3:$D$17,F3)

となります。これの数式の引数はよく似ていますね。範囲の列がB列からC、Dと変わっていますが行は3行目から17行目というのは変わっていません。

検索条件はすべてF3です。

絶対参照のつけ方をうまくすればセルG3 に入れた式1つをオートフィルすればすべての答えを出すことができます。絶対参照2(九九表作成)を参照してみてください。

セルG3に
=COUNTIF(B$3:B$17,$F3)

と入力しておきます。範囲のB$3:B$17はあとでオートフィルで縦横にコピーしたときに列番号は変化するが行番号は変化しないということです。

検索条件の$F3は同じように列のFは変化しないが行の3は変化するということです。

縦方向のオートフィルをします。

更にそのまま横方向にオートフィルを行います。

1つの数式を入力し、縦横にオートフィルをすることでうまくいきました。

F4キーを複数回叩くことで片方は絶対参照、もう片方は相対参照とすることでうまくいきました。(複合参照言うらしいです。)

オートサムボタンの横関数 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関数というのがあります。