教員の仕事で使う関数

（１）学級ごと、グループごとに出席番号や連番を入力する　　　　　　　　サンプルファイル　出席番号
	学級ごとグループごとに１番から番号をふる関数です。セルＣ３に　　=COUNTIF(B$3:B3,B3) という関数を入力します。意味は、１組の１番目の１組は何個か、です。当然１個です。これを２番目にオートフィルで計算式を複写します。関数は　　=COUNTIF(B$3:B4,B4)　となります。意味は、１組の２番目の１組は１組の１番目から２番目までで何個か、です。２個になります。「$」は絶対参照で、B$3の3はオートフィルで複写しても変わりません。範囲の初めは変わらず、範囲の終わりは広がっていきます。とにかく、　　=COUNTIF(B$3:B3,B3)　という関数を番号の最初に入力して、あとはオートフィルで計算式をデータがあるところまで複写すれば、学級ごとに出席番号やグループごとに１番から番号をふることができます。		全学年の名簿になると、１組が３つあるので、同じようにやると、２年１組の１番目の生徒の番号が１年１組の最後の生徒の番号の続きになってしまいます。３年１組の１番目の生徒も２年１組の最後の生徒の番号の続きになっていしまします。そこで、学年を区別できるように、とりあえず、セルＦ３に「学年×10＋学級」、　　=B3*10+C3　という関数を入力します。その結果、１年１組は１１、２年１組は１２、３年１組は３１、というように区別されます。そして、セルＣ３に　　=COUNTIF(F$3:F3,F3)　と関数を入力し、オートフィルで計算式を最後の行まで複写します。各学年・各学級の出席番号がふられます。

（２）「最大値」「最小値」「何番目に大きい値」「何番目に小さい値」 ①最大値や最高点は、　=MAX(範囲)　です。範囲は（最初のセル：最後のセル）です。つまり、　=MAX(C2:C100)　というように入力します。 ②最小値や最小点は、　=MIN(範囲)　です。つまり、　=MIN(C2:C100)　というように入力します。 ③何番目に大きい値や大きい方から何番目の得点という場合は、　=LARGE(範囲,何番目)です。　=LARGE(C2:C100,10)　でセルC2～C100の中で大きい方から10番目の数値を表示します。 ④何番目に小さい値や小さい方から何番目の得点という場合は、　=SMALL(範囲,何番目)です。　=SMALL(C2:C100,10)　でセルC2～C100の中で小さい方から10番目の数値を表示します。（３）セル内の一部の文字や数字、記号を抽出する ①セル内の左から何文字という場合は、　=LEFT(セル,文字数)　です。セルA1に「３年２組」と入力されていれば、　=LEFT(A1,２)　で左の２文字の「３年」が表示されます。 ②セル内の右から何文字という場合は、　=RIGHT(セル,文字数)　です。セルA1に「３年２組」と入力されていれば、　=RIGHT(A1,２)　で右の２文字の「２組」が表示されます。 ③セル内の左から何番目から何文字という場合は、　=MID(セル,何番目,文字数)　です。セルA2に「静岡県富士市永田町」と入力されていれば、　=MID(A2,4,3)　で左から４番目から３文字の「富士市」が表示されます。　=MID(A2,7,2)　で左から７番目から２文字の「「永田」が表示されます。名簿や住所録作成などに便利だと思います。（４）文字列を標準に変えたり、全角を半角に変えたりする ①セルや選択した範囲の上でマウスを右クリックすると、メニューが表示され、そこに「セルの書式設定」があります。それを選択すると、その中に「表示形式」があります。「表示形式」には、標準や数値、時刻、文字列など、いろいろなものがあります。標準で「1-1」と入力すると「１月１日」となってしまいますが、文字列にしておけば「1-1」とそのまま表示されます。しかし、文字列で数字が入力されている場合は、関数はそれを数字とは認識してくれず、関数を入力しても計算してくれません。しかも、「表示形式」を標準に変えれば問題は解決されるかというと、そうではなく、とてもやっかいです。そんなときには、　=VALUE(文字列のセル)　という関数を利用すると便利です。セルA3に文字列で「３」と入力されていれば、　=VALUE(A3)　で標準の「３」が表示されます。セルA4に文字列で「５番」と入力されていれば、　=VALUE(LEFT(A4,1))　で左から１文字を標準に変えるということで、標準の「５」が表示されます。これで関数で計算できるようになります。 ②全角を半角に変える関数は、　=ASC(セル)　です。セルB1に「１２３４番地の５６」と全角で入力されている場合、　=ASC(B1)　で半角の「1234番地の56」と変わります。逆に、=JIS(B1)　で数字が全角に変わります。ちなみに、数字だけなら、　=NUMBERSTRING(セル,3)　で「１２３」が「一二三」、　=NUMBERSTRING(セル,1)　で「百二十三」　というように漢数字に変わります。
（５）結合したセルに連番を振る　　　　　サンプルファイル　結合セル連番　紹介動画youtubeshort 　大きさが違う結合されたセルに連番を振るのに困ったことはないでしょうか。結局１から順番に手打ちなんてことも・・・。実は「COUNT」関数か「MAX」関数で簡単にできます。
	①最初に連番を振りたいセルの範囲を選択します。	②数式バー（関数を入力する場所）に =COUNT($A$1: A1)+1 と入力します。（結合したい先頭のセルの１つ上のセルで式を作ります）	③Ctrlキーを押しながらEnterキーを押します。結合したセルに連番が振られました。 ※「COUNT」のかわりに「MAX」にしてもできます。

（１）SORT関数（並べ替えの関数）　　サンプルファイル　SORT関数
サンプルファイル	関数入力
左上がサンプルファイルの画像です。セルＪ２に並べ替えの関数を入力します。　　=SORT(A2:G21,7,-1,False) 　SORT(配列,並べ替えインデックス,並べ替え順序,並べ替え基準)です。　「配列」は並べ替えの範囲です。「＄」はつけません。　「並べ替えインデックス」は並べ替えの基準となる列、あるいは、行の番号です。　「並べ替え順序」は昇順か降順です。「１」が昇順、「ー１」が降順です。　「並べ替え基準」は「列」を並べ替えるか、「行」を並べ替えるかです。「列」は「TRUE」、「行」は「FALSE」です。　この関数の場合、合計がＧ列で、Ｇ列は７番目なので「７」、合計の高い順にしたいので「ー１」、行を並べ替えたいので「FALSE」です。　実行したのが下の表です。得点を変えると、自動で順番が入れ替わります。

（２）CONCAT関数（文字列を連結する関数）　　サンプルファイル　CONCAT関数

サンプルファイル

関数入力

　左上がサンプルファイルの画像です。
　Ａ列からＤ列の文字列を連結する場合、左下のセルＥ１のように、
　　=A1&B1&C1&D1
と入力していました。「＆」を何回も入力するのは面倒です。しかし、CONCAT関数を使うと、左下のセルＥ２のように、
　　=CONCAT(A2:D2)
だけですみます。とても楽です。
　スペースを入れる場合も、左下のセルＥ３のように、
　　=A3&&B3&"　"&C3&D3
と結構大変でしたが、CONCAT関数を使うと、左下のセルＥ４のように、
　　=CONCAT(A4:B4,"　",C4:D4)
と入力すればよく、少し楽になります。
　２つくらいの文字列なら「＆」でよいですが、複数の文字列を連結するなら、断然CONCAT関数の方が便利だと思います。

（３）UNIQUE関数（データを重複しないように表示する関数）　　サンプルファイル　UNIQUE関数
サンプルファイル	関数入力
左上がサンプルファイルです。セルＦ２からＩ２１に、データがあると表ができる条件付き書式を設定してあります。　右上の関数入力のセルＦ２に、　　=UNIQUE(D2:D21,FALSE,FALSE) を入力します。UNIQUE（配列,[列の比較],[回数指定])になります。　「配列」は重複データを含むセル範囲を指定します。この表の場合はセルD２からD２１にラーメンやカレーなどのデータが重複しながら表示されています。「＄」はつけません。　「[列の比較]」は行方向に検索するか、列方向に検索するかを指定します。行方向（右方向）は「TRUE」、列方向（下方向）は「FALSE」です。この表の場合は列のデータを検索したいので「FALSE」になります。　「[回数指定]」は１回のみのデータを表示したい場合は「TRUE」、重複したデータも含めて表示したい場合は「FALSE」です。この表の場合は重複したデータも含めて表示したいので、「FALSE」になります。　　=UNIQUE(D2:D21) だけでも大丈夫です。以降の「[列の比較]」と「[回数指定]」は自動的に「FALSE」が選択されます。　関数を入力した結果が左下の表です。
関数入力結果	人数を数える関数入力
右上の表に人数を数える関数を表示ました。好きな食べ物の人数を数える集計表になります。　実際の関数は、　セルＧ２に　=COUNTIF($D$2:$D$21,F2) 　セルＨ２に　=COUNTIFS($B$2:$B$21,$H$1,$D$2:$D$21,$F2) 　セルＩ２に　=COUNTIFS($B$2:$B$21,$I$1,$D$2:$D$21,$F2) になります。これらの関数を入力後、オートフィルで、下にコピーしてください。
（４）XLOOKUP関数（VLOOKUP関数に変わる検索の関数）　　サンプルファイル　XLOOKUP関数サンプルファイル関数入力　これまでの検索の関数「VLOOKUP関数」を使用すれば、セルＩ２に、　　=VLOOKUP(H2,$A$2:$G$20,5,0) と入力し、右へオートフィルでコピーし、セルＪ２の関数で「5」を「6」に変え、セルＫ３の関数で「5」を「7」に変えていました。また、検索値がないとエラーになってしまうので、　　=IFERROR(VLOOKUP(H2,$A$2:$G$20,5,0),"") とするのが最適でした。しかし、「XLOOKUP関数」使用すると、もっと簡単になります。セルＩ２に、　　=XLOOKUP(H2,$A$2:$A$20,$E$2:$G$20,"",0,1) だけで、下の図のように表示されます。　関数の構成は、XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])です。　「検索値」は「NO」なので、「H2」です。　「検索範囲」は、検索値が入力されている範囲です。「NO」なので「A2:A20」です。後でオートフィルでコピーするので「$」をつけます。「$A$2:$A$20」となります。　「戻り範囲」は表示したいデータの範囲です。「氏名」、「住所」、「電話番号」なので、「E2：G20」です。ここでも「$」をつけます。「$E$2:$G$20」となります。　「[見つからない場合]」は空欄にするのがほとんどだと思います。「""」です。　「[一致モード]」は完全一致にするのがほとんどなので「0」です。近似値の場合は「1」にします。　「[検索モード]」は、先頭からの場合は「1」、末尾からの場合は「-1」です。この場合は先頭からなので「1」です。　オートフィルでコピーした結果が下の図です。　ただ、「戻り範囲」は、連続でないといけないみたいです。「氏名」、「電話番号」にして、「住所」をとばすことはできないみたいです。残念ですが、その場合は「VLOOKUP」を使うことになると思います。