「Excelでの関数サンプル集（その1）」
IF/COUNTIF/SUMIF/INDEX

作者　石山朋史
2000/8/18
ファイル名 xls-sp01.xls
ファイル形式 Excel95
連絡先 「画面を見ないでWindowsパソコン」
URL
http://www.lares.dti.ne.jp/~tomof/
E-Mail
stonemountain.office@gmail.com

1 サンプルファイルについての解説
サンプルファイルは文書と表で構成されています。
音声環境を基本にしていますので文書での解説はすべてセルAの列に記述しています。
関数のサンプルなので表のレイアウトや書式設定は省略しています。
このファイルのシートは、IF関数とCOUNTIF関数・SUMIF関数の基本・SUMIF関数とINDEX関数の応用の3シートです。
キー操作での別のシートへの移動は、Ctrl+PageupまたはCtrl+Pagedownで移動出来ます。
音声環境の数式確認方法は、確認したいセルにF2キーです。またCtrl+Shift+@(アットマーク）だと数式のセルはすべて数式表示になります。

2 シートIF関数とCOUNTIF関数についての解説
このシートは、条件によってセルの値を変更するIF関数と、ある値の個数を数えるCOUNTIF関数のサンプルです。
サンプルは成績表です。入力された点数に基づいて合否判定およびそれぞれの人数と平均点を関数で算出しています。
表は、セルA43からセルD58までです。
セルAの列には連番が入力されています。
セルBの列には名前が入力されています。
セルCの列には点数が入力されています。
セルDの列にはIF関数を用いてセルC列の点数が80以上なら合格、79以下なら不合格と表示させています。
セルB55はSUM関数で合計を表示しています。
セルB56はAVERAGE関数で平均を表示しています。
セルB57とセルB58はCOUNTIF関数でそれぞれ合格と不合格の人数を表示しています。
IF関数とCOUNTIF関数の解説
IF関数
条件によってセルの値を変更出来ます。例えばあるセルの値によってこのセルの値を変更するような時に使います。
論理式が成り立てば真の場合の値になり、論理式が成り立たなければ偽の場合の値になります。
ここではセルC列が80以上だと、セルD列が合格と表示され79以下だと不合格と表示されるようにしています。
書式 IF(論理式,真の場合,偽の場合)
IF(C44>=80,"合格","不合格") これはセルD44の数式。
ちなみに合格のように値が文字列の場合は""で囲みます。
また論理式C44>=80は、セルC44が80以上という意味です。
COUNTIF関数
ある値の個数を数えるのに使用します。これは指定されたセル範囲に条件に一致するセルの個数を表示します。
ここではセルD44からD53までの範囲で合格と表示されている個数を表示しています。
書式 COUNTIF(範囲,条件)
COUNTIF(D44:D53,"合格")
ちなみに合格のように値が文字列の場合は""で囲みます。

3 シートSUMIF関数の基本についての解説
このシートは、条件によってセルの値を加算するSUMIF関数のサンプルです。
サンプルは販売実績表です。入力された品名と販売数の一覧に基づいてその中からある品名の販売数量の合計だけをを関数で算出しています。
表は、セルA31からセルA43までです。
セルAの列には連番が入力されています。
セルBの列には品名が入力されています。
セルCの列には販売数量が入力されています。
セルB43にはSUMIF関数を用いてセルB列の中からパソコンのセルCの列の販売数量だけを加算して表示させています。
SUMIF関数の解説
SUMIF関数
条件によってセルの値を加算出来ます。例えば販売一覧表の中からある品名だけの数量の合計を求めるような時に使用します。
ここではB32からB41の中にパソコンと表示されている行のC列の値のみを加算してB43に表示しています。
SUMIF関数は範囲の中から検索条件に合致しているものがあれば、その合計範囲の値を加算します。
書式 SUMIF(範囲,検索条件,合計範囲)
SUMIF(B32:B41,"パソコン",C32:C41) これはセルD43の数式。
ちなみにパソコンのように値が文字列の場合は""で囲みます。

4 シートSUMIF関数とINDEX関数の応用についての解説
このシートは、SUMIF関数を利用して一覧のデータから条件に合ったデータのみを抽出して表示させるサンプルです。
ここはSUMIF関数の基本をマスターしたら読んでくださいね。
サンプルはIF関数とCOUNTIF関数のシートと同じ成績表を使い、その合格者だけを合格者リストで表示させています。
成績表は、セルA69からセルE85までです。
セルAの列には行番号が入力されています。
セルBの列には名前が入力されています。
セルCの列には点数が入力されています。
セルDの列にはIF関数を用いてセルCの点数が80以上なら合格、79以下なら不合格と表示させています。
セルEの列にはCOUNTIF関数を用いて一番上の人から自分まで合格者が何人いるかを算出してます。これで合格者の連番を表示してます。
セルB82はSUM関数で合計を表示しています。
セルB83はAVERAGE関数で平均を表示しています。
セルB84とセルB85はCOUNTIF関数でそれぞれ合格と不合格の人数を表示しています。
合格者リストは、セルA87からセルD98までです。
セルAの列には合格連番が入力されています。
セルBの列にはSUMIF関数で合格者の行番号を表示しています。
セルCの列にはINDEX関数で合格者の名前を算出しています。
セルDの列にはINDEX関数で合格者の点数を算出しています。
INDEX関数の解説
INDEX関数
あるセルの範囲の中から行数と列数を指定して値を抽出することが出来ます。
書式 INDEX(配列,行番号,列番号)
8 SUMIF関数を利用して一覧のデータから合格者のみを抽出して表示させる方法
理屈は、まずSUMIF関数で検索の条件に合致したデータが表の何行目または何列目に書いているかを表示させます。
そしてINDEX関数で行と列を指定してそのデータを表示させます。
A 成績表で事前に合格者の連番を作成、ちなみにサンプルだと成績表のセルE列
なぜわざわざ合格者の連番を作るかというとSUMIF関数だと同じ条件があると数値を加算しちゃいますのでその防止です。
簡単に言うとひとつの検索条件にひとつの合致したデータを抽出させるためにします。
B その合格者連番の数字が表示されているのが表の何行目または何列目に表示されているかをSUMIF関数で算出。
ちなみにサンプルでは合格者リストのBの列になります。
まず合格者リストの合格連番セルA列をSUMIF関数の検索条件にします。
そして成績表の合格連番D列で合致しているデータの成績表A列の行番号を表示させるようにしてます。。
試しに合格者リストの合格連番A列と行番号B列と成績表の行番号A列と合格連番E列を比較するとわかるでしょ。
ということで合格者リストのA列の数字によって成績表のデータもいろいろ表示させることが出来ます。
C INDEX関数で合格者リストの名前と点数の表示。
SUMIF関数で行番号を表示させてしまえば、あとはそのデータとINDEX関数を使って該当するデータを表示させることが出来ます。
ここでは合格者の名前を表示させるのに下記の通り計算してます。
INDEX関数で成績表の名前C列を範囲にして、合格者リストの行番号B列を指定すれば表示出来ます。
合格者リストの点数の表示も同じ仕組みになっています。
簡単にまとめますと
合格者の連番をCOUNTIF関数で表示。
SUMIF関数で合格者連番の行番号を表示。
合格者の名前と点数を行番号を使ってINDEX関数で表示。
ちなみになぜINDEX関数をわざわざ使うのかといいますと、
SUMIF関数では合致したデータを表示させるのに文字列は引っ張ってこれないんです。
ですのでわざわざINDEX関数を使用しています。
もし点数のように数値だけなら、INDEX関数を使う必要はないですね。

以上ご不明な場合は石山朋史まで