第4回Office基本講座は、7月30日、16名の参加で、新型コロナウイルスの感染拡大防止として、相変わらず泉山拠点の使用ができず、止む無く「日立市会瀬清少年の家」の体育室での開催となりました。今回は、Excel関数の条件判定、データ集計、抽出とうについての学習です。この講座の講師は安田出世話人が担当します。
講座の内容梗概は下記のとおりです。
60分で学ぶExcel関数 条件判定編
◆IF関数；IF（論理式、値が真の場合、値が偽の」場合）…条件の真偽に応じて異なる処理を行う。
◆論理式には比較演算子（=、<>、<、>、>=、<=など）を使うことが多い。
◆ＩF関数は「数式」タブの「論理」から選択できる。「ｆｘ」をクリック、あるいは、数式バーに「=」を入力しても選択できる。
◆具体例として、IF（C4>B4,"Up","Down"）はC4>B4がTRUEの場合「Up」を、FALSEの場合「Down」を示す。
◆2つの条件で3通りの値を求めるには、ＩFの中にＩFを設定すればよい（オネスト）。例として、
IF（C4<B4,"Up",IF(C4=B4,"Stray","Down")）
◆セルの比率を求める場合、セルが文字列の場合エラー（#Vallue!）表示となる。エラーを表示させない関数として
IFERROR関数がある。たとえば、IFERROR(B4/C4,"-")は、、エラーは「-」で表示される。
◆比較演算子を使った2つの論理式を使った場合、下記の関数を使うことが多い。
ＡND関数；=AND(論理式1,論理式2…)…すべての条件を満たす場合、TRUEを返す。
OR関数；=OR(論理式1,論理式2…)…1つ以上の以上の条件を満たす場合にTRUEを返す。
NOT関数；=NOT(論理式)…論理式のTRUE/FALSEの逆を返す。
ISNUMBER関数；=ISNUMBER(テストの対象)…テストの対象が数値であればTRUEを返す。
COUNT関数；=COUNT(値1,値2)…数値の個数を返す。
関数は「数式」タブの「論理」から選択できる。
◆B4もC4もＤ4より大きい条件では…AND（B４>=D4,C4>=D4)
B4かC4がD4より大きい条件では…OR(B４>=D4,C4>=D4)
NOT(B４>=D4,C4>=D4)は、B４>=D4,C4>=D4の場合は、FALSEが返ってくる。
◆IF関数と組合わせて指定した値を表示させるには、上記の場合「IF(AND(B４>=D4,C4>=D4)、"〇"、"-"」となる。
◆セルのデータが数値かどうか調べるにはISNUMBER関数を使う。IF関数と組み合わせて、「IF(ISNUMBER(C4),C4/B4,"
-")」と表示できる。対象セルが特定の種類かどうか調べるにはISで始まる関数が多数用意されている。
◆COUNT関数で複数のセルを纏めて判定することができる。すなわち、「IF（AND（ISNUMBER（B4）,ISNUMBER（C4）） ,C4/B4,"-"」が、「IF(COUNT(B4:C4)=2,C4/B4,"-")」に変えてもよい。
◆2つの内いずれかを選ぶのであればIF関数が便利、ただ、3つの内1つを選ぶ場合は、IF関数のネストを使えば可能、ただ
し 、この場合式が複雑になり、分かり難い。このような場合は、下記の関数が便利である。
CHOOSE関数；=CHOOSE(インデックス,値1,値2)…指定した順番に当たる値を返す。
IFS関数；=IFS(論理式1,値が真の場合1,論理式2,値が真の場合2)…真になる最初の論理式に対応する値を返す。
SWITCH関数；=SWITCH(式,値1,結果１，規定または値2,結果2)…式に最初に一致した値に対する結果を返す。
◆たとえば、順位によって賞品を表示する場合、IF関数では「IF(B4=1,"電気自動車"、IF（B4=2,"ノートPC",IF(B4＝３，
"AIスピーカー"、"参加賞")）)」となるが、CHOOSE関数では「CHOOSE(B4,"電気自動車","ノートPC","AIスピーカー"、
"参加賞")」となり、簡潔な式となる。
◆ただし、CHOOSE関数では4番目まで指定できるが、5番目があるとエラー（#VALUE!）となる。このような場合はIFS関数を
使った方が良い。IFS（B4=1,"電気自動車",B4=2,"ノートPC",B4=3,"AIスピーカー"、B4>＝４,"参加賞"）となる。
◆得点範囲に「A」～「D」のランクを表示したい場合、
IF関数の場合は IF(B4>=200,"A",IF(B4>=150,"B",IF(B4>=120,"C","D")となり、構造がやや複雑である。
IFS関数では、 IFS(B4>=200,"A",B4>=150,"B",B4>=120,"C",B4<120,"D")
SWITCH関数では、SWITCH（TRUE,B4>=200,"A",B4>=150,"B",B4>=120,"C","D"）となる。
60分で学ぶExcel関数 データ集計編
- ◆表データの小計と総計を求める関数として、SUBTOTAL関数とAGGREGATE関数がある。
◆SUBTOTAL関数；=SUBTOTAL(集計方法,参照1,参照2)…表のデータを集計する。
AGGREGATE関数；=AGGREGATE(集計方法,オプション,参照1,参照2)…指定データを除外して計算する。
AGGREGATE関数；=AGGREGATE(集計方法,オプション,配列,順位)…指定データを除外して計算する。
◆SUBTOTAL関数では、引数「集計方法」で計算方法を指定できる。たとえば、１：平均（=AVERAGE）、２：数値の個数（
=COUNT）、３：データの個数（=COUNTA）、４：最大値（=MAX）、５：最小値（=MIN）、9：合計（=SUM）など指定できる。
引数の指定値は1～11、100を加えた101～111もあるが、非表示の行が集計対象から除外される。
◆小計にはSUBTOTAL関数、総計もSUBTOTAL関数を使用する。総計では小計のセルの値が含まれない。具体的には
小計を求める場合は、SUBTOTAL（9,B4:B6）とSUBTOTAL（9,B8:B１0）で、総計はSUBTOTAL（9,B4:B11）となる。
◆最大値や平均を求める場合は、SUBTOTAL（4,B4:B11）およびSUBTOTAL（1,B4:B11）となる。
◆フィルター機能で非表示になった行は、SUBTOTAL関数の集計対象から除外される。
◆AGGREGATE関数を使えば、SUBTOTAL関数よりさらに細かな集計方法を指定することができる。1～11はSUBTOTAL
関数と同じで、12～19で指定する。これで、オプションの指定で除外の対象を指定することができる。SUBTOTAL関数の
書式はAGGREGATE(指定の集計方法、指定のオプション、配列、順位)がある。
◆たとえば、AGGREGATE（14,0,B4:B11,2）は、「14」で〇番目の大きな値、「0」で関数SUBTOTAL/AGGREGATEを除
く、Ｂ４～B11の範囲、「2」で2番目の値ということになる。
◆AGGREGATE（14,1,B4:B11,2）は、1のオプションの指定により、非表示の行は含まないということになる。
◆条件を満たすデータを集計する関数として名前が「○○IF」という関数である。具体的には
COUNTIF関数；=COUNTIF(範囲,検索条件)…条件に合うセルの個数を数える。
SUMIF関数；=SUMIF(範囲,検索条件,合計範囲)…条件に合うデータの合計を求める。
AVERAGEIF関数；=AVERAGEIF（範囲,検索条件,平均対象範囲）…条件に合うデータの平均を求める。
MAXIFS関数；=MAXIFS（最大範囲,条件範囲1,条件1,条件範囲2,条件2）…複数の条件に合うデータ最大値を求める
ＭINIFS関数；=MINIFS（最小範囲,条件範囲1,条件1,条件範囲2,条件2）…複数の条件に合うデータの最小値を求める
◆COUNTIF($B$4:#B$18,F4)は、F4の値をB4～B18の範囲で検索して、その個数（行の数）を返す。
◆COUNTIF(D4:D18,">200")のように、検索したい値そのもではなく演算子と値を組合わせて指定することもできる。この場
合、200以上の数値のあるセルをD4～Ｄ18の範囲で検索し、個数を返す。
◆SUMIF(D4:D18,">="&F4)は、D4～D18の範囲で、F4の数値以上のセルの値の合計を返す。
◆SUNIF($B$4:$B$18,F4,$D$4:$d$18)は、Ｄ4と同じ内容のセルをB4～B18で検索し、その検索と一致するＤ4～D18の合
計を返す。
◆AVERAGEIF(C4:C18,"<"&F4)はF4の値より少ないC4～C18のセルの中の平均値を返す。
◆AVERAGEIF($B$4:$B$18,F4,$C$4:$C$18)は、F4の値をB4～B18から検索し、これに対応するC4～C18の中のセルの
平均値を返す。
◆検索条件にワイルドカードが利用できる。 COUNTIF(A3:A18,"*営業所")は、営業所の名前が付くセルをA3～A18の中
から個数を返す。
◆ワイルドカードに代って任意の1文字を表す「？」も使用できる。COUNTIF(A3:A18,"？？？支店")は、3文字＋支店。
◆SUMIF($A$4:$A$18,"*"&F4,$C$4:$C$18)は、*+F4の値を、Ａ４～A18で検索し、対応するＣ４～C18の合計を返す。
◆AVERAGEIF(A4:A18,"<>*本店"、C4:C18)は、*本店と同じ値をA4～A18で検索し、対応するC4～C18の平均値を返す
◆複数の条件を指定したい場合は、各関数名に「Ｓ」を付ければよい。COUNTIFＳ、ＳＵＭＩＦＳなど。
◆COUNTIFS(A4:A18,"東京都",C4:C18,"男")は、検索範囲がA4～A18で東京都を検索し、かつ、検索範囲がC4～C18
で男を検索した結果の個数を返す。
◆COUNTIFS(B4:B18,">=30",B4:B18,"<=40"F4:F18,">=50000")は、検索範囲がB4～B18で30以上40未満で、かつ、
検索範囲がＦ4～F18で値が50000以上の行を返す。
◆COUNTIFS(A4:A18,"*県",D4:D18,"<10")は、ワイルドカードを使った例で、検索範囲がA4～A18で、県の名前の付い
た、かつ、検索範囲がD4～D18で10未満の行を返す。
◆SUMIFS（E4:E18,A4:A18,"神奈川県"、C4:C18,”男”）は、合計対象範囲がＥ4～E18で、検索対象範囲のA4～A18で神
奈川県を検索し、かつ、検索対象範囲がC3～C18の男を検索した結果の合計を返す。
◆SUMIFS（E4:E18,B4:B18,">=40"、B4:B18,"<50",D4:D18,”<10”）は、合計対象範囲がＥ4～E18で、検索対象範囲の
B4～B18で40以上50未満、かつ、検索範囲のD3～D13が10未満の合計を返す。
◆SUMIFS（$E$4:$E$18,$A$4:$A$18,"$G4"、$C$4:$C$18,"H$3"）は、合計対象範囲がＥ4～E18で、検索対象範囲の
Ａ4～A18でG4を検索し、かつ、検索範囲のC4～C18でH3を検索した結果の合計値を返す。この場合はオートフィルでコ
ピーが可能である。
◆複数条件のAVERAGEIFS関数も同様である。AVERAGEIFS（D4:D18,A4;A18,"東京都"、C4:C18,"女"）は、平均対象範 囲がD4～D13で、範囲A4～A18で東京都を検索し、かつ、範囲C4～C18で女である場合の平均を返す。
◆AVERAGEIFS（D4:D18,B4;B18,">40"、E4:E18,">=30000"）は、平均対象範囲がD4～D13で、範囲B4～B18で40を越
すものを検索し、かつ、範囲E4～E18で30000以上である場合の平均を返す。
◆MAXIFSやMINIFSも同様である。MAXIFS(D4:D18,A4:A18,"神奈川県",B4:B18,">=40")は、最大値を検索する範囲は D4～D18で、神奈川県を範囲Ａ4～A18で検索し、かつ、範囲B4～B18で40騎乗を検索した結果の最大値を返す。
◆MINIFS(D4:D18,A4:A18,"東京都",E4:E18,">=30000")は、最小値を見つける対象範囲はD4～D18で、範囲A4～A18で
東京都を検索し、かつ、範囲E4～E18で30000以上の検索した結果の最小値を返す。 -
60分で学ぶExcel関数 データ抽出編
- ◆表を検査して情報を取り出す関数として次の関数がある。
VLOOKUP関数；=VLOOKUP(検索値,範囲,列番号,検索方法)…表を検索して値を取り出す。
ＩFERROR関数；=IFERROR(値,エラーの場合の値)…式がエラーなら別の値を返す。
◆VLOOKUP(F4,A4:D11,4,FALSE)は、Ａ4の値をA4～D11の範囲で検索し、一致した範囲の左から4番目の値を返す。
◆VLOOKUP(F4,B4:C11,2,FALSE)は、Ｆ4の値をB4～C11の範囲で検索し、検索範囲の左から2番目のセルの値を返す。
◆VLOOKUP(F4,$A$4:$C$11,4,FALSE)は、Ｆ4の値をA4～C11の範囲で検索し、検索範囲の左から4番目のセルの値を
返す。この式はオートフィルを可能としている。
◆VLOOKUP(F6,$A$4:$C$11,4,FALSE)は、Ｆ6の値をA4～C11の範囲で検索し、検索範囲の左から4番目のセルの値を
返す。この式はオートフィルを可能としている。
◆名前の1字から名前を検索することができる。VLOOKUP("*"&$F$4&"*",$A$4:$C$11,4,FALSE)は、マジックカードを
F4の前後に付けて、A4～ C11の範囲で検索し、検索範囲の4番目の列のセルの値を返すもの。
◆VLOOKUP("*"&$F$4&"*",$B$4:$C$11,3,FALSE)は、検索範囲がB列からなので、B列の3番目の列の値を返す。
◆VLOOKUP関数の引数「検索方法」の「FALSE」は完全一致、「TRUE」は数値が昇順に並べられた検索対象の列の中で、
検索値以下の最大値が検索される。VLOOKUP(D6,$G$4:$H$7,2,TRUE)は、D6の値を検索範囲のG4～H7で検索し、
検索範囲の左から2番目の列のセルを返すが、検索方法が「TRUE」なので、検索値以下の最大値で検索する。
◆検索範囲にないものを検索するとエラー表示されるが、これを防ぐには
IFERROR（VLOOKUP（F4、$A$4:$D$11,4,False）、”-”） となり、エラー表示は「-」となる。
◆表からデータを取り出す関数としてVLOOKUPがあるが、これでは取り出せない場合もあり、下記の関数を使う。
◆HLOOKUP関数；HLOOKUP(検索値,範囲,行番号,検索方法)…上端行を検索して値を取り出す。
INDEX関数；=INDEX(参照,行番号,列番号,領域番号)…番号で指定した位置の値を取り出す。
MATCH関数；=MATCH(検査値,検査範囲,照合の種類)…範囲内での位置を調べる。
◆HLOOKUP(H4,B3:D9,7,FALSE)は、H4のデータをB3～D9の範囲の上端行で検索し、上端行から7番めのセルのデータ
を返す。
◆INDEX(A4:A8,MATCH(H4,F4:F8,0))は、H4のデータをF4～ F8の行で検索し、A4～A8の行から一致した列のデータを
返す。
◆ＶLOOKUP関数では、数値が昇順に並んだ列を対象に、各値を区切りとする区間を検索することができるが、降順に並ん
だ値の区間は検索できない。INDEX関数とMATCH関数で、降順でも検索が可能となる。
◆MATCH関数の引数「照合の種類」に「0」を指定すると完全一致、「1」は検査値以下の最大値、「-1」は検査値以上の最小
値を検索する。「-1」で降順を選択でき、INDEX関数の引数「行番号」を指定することができる。
◆INDEX($I$4:$I$7,MATCH(E4,$H$4:$H$7,-1))は、E7のデータを降順で示されているH4～H7の中から検索し、検索さ
れた行のＩ4～I7の範囲の行の値を返す。
◆INDEX(B4:D8,MATCH(H4,A4:A8,0),MATCH(I4,B3:D3,0))は、H4のデータをA4～A8の列で行を検索し、I4のデータを
B3～Ｄ3の行から列を検索して、B4～D8の範囲で検索した行、列番号のデータを返す。
◆上記をVLOOKUP関数とMATCH関数で表示すると、下記のように表現でき、MATCH関数は1回だけ使用すればよい。
VLOOKUP(H4,A4:D8,MATCH(I4,A3:D3,0),FALSE)。すなわち、H4データをA4～D8の中の1番左側の列で行を検索し
、I4データをA3～D3行の検索番目の列のデータを返す。
◆ＩNDEX関数の引数「列番号」に「0」を指定すると、「行番号」を指定した行全全体が取り出される。
MAX(INDEX(B4:D8,MATCH(H4,A4:A8,0),0) )は、H4のデータをA4～A8の中から行を指定し、列を「0」に指定すること
でB4～D8の範囲の検索された行のＭAX値のデータを返す。
◆MIN(INDEX(B4:D8,0,MATCH(H4,A4:A8,0)) )は、ＩNDEX関数の引数「行番号」に「0」を指定すると、「列番号」を指定し
た列全全体が取り出される。すなわち、H4のデータをMATCH関数で検索して該当する列全体を取り出し、それをMIN関
数の引数で最小値を取り出している。
◆文字列のセル参照で情報を取り出す関数として、INDIRECT関数がある。
◆INDIRECT（H4）は、H4セルのデータ（列番号と行番号）のセル番号のデータを取り出す。
◆セル参照を「R1C1形式」で指定したい場合は、引数「参照形式」に「FALSE」を指定する。
INDIRECT（"R"&H4&"C"&I4,FALSE）は、H4の行でＩ4の列のセルのデータを取り出す。
◆INDIRECT関数では、セルの名前を表す文字列もセル参照に変換可能。たとえば、B9セルを選択し、名前ボックスに「国
語平均」と入力して「Enter」キーを押して名前を設定し、同様にC9セルを「数学平均」、D9セルを「英語平均」という名前
を付ける。INDIRECT（H4&"平均"）とすれば、Ｈ4のデータ＋平均の名前のセルデータを取り出すことができる。
◆表の列や行の見出しを利用して、各セルに一括でその見出しを名前として設定できる。これを利用した例として、行の見
出しとそのデータを選択して、「数式」タブの「選択範囲から作成」をクリックして、上端行にチェックを付ける。表示したいセ
ルを選択し、INDIRECT(見出しの名前)を入力することで、そのデータを取り出すことができる。
◆表の上端行および左端列に見出しがある表を選択し、「数式」タブの「選択範囲から作成」をクリック、表示される画面で「上
端行」および「左端列」にチェックマークを付ける。表示させるセルを選択し、
INDIRECT(左端の見出しの名前) INDIRECT(上端の見出しの名前）でデータを取り出すことができる。
世話人・文責：安田 出
会瀬青少年の家における第4回Office基本講座
第4回Office基本講座の参加者