教員の仕事でよく使うと思われる関数や
   新しい関数
(2020年以降)で「これは便利」と思ったものを紹介します 【新しい関数へ】

(1)学級ごと、グループごとに出席番号や連番を入力する        サンプルファイル 出席番号 

 学級ごとグループごとに1番から番号をふる関数です。セルC3に
  =COUNTIF(B$3:B3,B3)
という関数を入力します。意味は、1組の1番目の1組は何個か、です。当然1個です。これを2番目にオートフィルで計算式を複写します。関数は
  =COUNTIF(B$3:B4,B4) 
となります。意味は、1組の2番目の1組は1組の1番目から2番目までで何個か、です。2個になります。「$」は絶対参照で、B$3の3はオートフィルで複写しても変わりません。範囲の初めは変わらず、範囲の終わりは広がっていきます。とにかく、
  =COUNTIF(B$3:B3,B3) 

という関数を
番号の最初に入力して、あとはオートフィルで計算式をデータがあるところまで複写すれば、学級ごとに出席番号やグループごとに1番から番号をふることができます。
 全学年の名簿になると、1組が3つあるので、同じようにやると、2年1組の1番目の生徒の番号が1年1組の最後の生徒の番号の続きになってしまいます。3年1組の1番目の生徒も2年1組の最後の生徒の番号の続きになっていしまします。そこで、学年を区別できるように、とりあえず、セルF3に「学年×10+学級」、
  =B3*10+C3 

という関数を入力します。その結果、1年1組は11、2年1組は12、3年1組は31、というように区別されます。そして、セルC3に
  =COUNTIF(F$3:F3,F3) 
と関数を入力し、オートフィルで計算式を最後の行まで複写します。各学年・各学級の出席番号がふられます。

(2)「最大値」「最小値」「何番目に大きい値」「何番目に小さい値」

①最大値や最高点は、 =MAX(範囲) です。範囲は(最初のセル:最後のセル)です。つまり、 =MAX(C2:C100) というように入力します。
最小値や最小点は、 =MIN(範囲) です。つまり、 =MIN(C2:C100) というように入力します。
③何番目に大きい値や大きい方から何番目の得点という場合は、 
=LARGE(範囲,何番目)です。 =LARGE(C2:C100,10) でセルC2~C100の中で大きい方から10番目の数値を表示します。
④何番目に小さい値や小さい方から何番目の得点という場合は、 =SMALL(範囲,何番目)です。 =SMALL(C2:C100,10) でセルC2~C100の中で小さい方から10番目の数値を表示します。



(3)セル内の一部の文字や数字、記号を抽出する

①セル内の左から何文字という場合は、 =LEFT(セル,文字数) です。セルA1に「3年2組」と入力されていれば、 =LEFT(A1,2) で左の2文字の「3年」が表示されます。
セル内の右から何文字という場合は、 =RIGHT(セル,文字数) です。セルA1に「3年2組」と入力されていれば、 =RIGHT(A1,2) で右の2文字の「2組」が表示されます。
セル内の左から何番目から何文字という場合は、 =MID(セル,何番目,文字数) です。セルA2に「静岡県富士市永田町」と入力されていれば、 =MID(A2,4,3) で左から4番目から3文字の「富士市」が表示されます。 
=MID(A2,7,2) で左から7番目から2文字の「「永田」が表示されます。名簿や住所録作成などに便利だと思います。


(4)文字列を標準に変えたり、全角を半角に変えたりする

①セルや選択した範囲の上でマウスを右クリックすると、メニューが表示され、そこに「セルの書式設定」があります。それを選択すると、その中に「表示形式」があります。「表示形式」には、標準や数値、時刻、文字列など、いろいろなものがあります。標準で「1-1」と入力すると「1月1日」となってしまいますが、文字列にしておけば「1-1」とそのまま表示されます。しかし、文字列で数字が入力されている場合は、関数はそれを数字とは認識してくれず、関数を入力しても計算してくれません。しかも、「表示形式」を標準に変えれば問題は解決されるかというと、そうではなく、とてもやっかいです。そんなときには、 =VALUE(文字列のセル) という関数を利用すると便利です。セルA3に文字列で「3」と入力されていれば、 =VALUE(A3) で標準の「3」が表示されます。セルA4に文字列で「5番」と入力されていれば、 =VALUE(LEFT(A4,1)) で左から1文字を標準に変えるということで、標準の「5」が表示されます。これで関数で計算できるようになります。
②全角を半角に変える関数は、 =ASC(セル) です。セルB1に「1234番地の56」と全角で入力されている場合、 
=ASC(B1) で半角の「1234番地の56」と変わります。逆に、=JIS(B1) で数字が全角に変わります。ちなみに、数字だけなら、 =NUMBERSTRING(セル,3) で「123」が「一二三」、 =NUMBERSTRING(セル,1) で「百二十三」 というように漢数字に変わります。

(5)結合したセルに連番を振る     サンプルファイル 結合セル連番  紹介動画youtubeshort

 大きさが違う結合されたセルに連番を振るのに困ったことはないでしょうか。結局1から順番に手打ちなんてことも・・・。
実は「COUNT」関数か「MAX」関数で簡単にできます。
                           ①最初に連番を振りたいセルの範囲を選択します。 ②数式バー(関数を入力する場所)に
=COUNT($A$1:
A1)+1

と入力します。(結合したい先頭のセルの1つ上のセルで式を作ります)
Ctrlキーを押しながらEnterキーを押します。結合したセルに連番が振られました。

※「COUNT」のかわりに「MAX」にしてもできます。

「これは便利」と思った新しい関数 【ページの先頭へ】

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

(2)CONCAT関数(文字列を連結する関数)  サンプルファイル CONCAT関数  
サンプルファイル

関数入力

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

  

(3)UNIQUE関数(データを重複しないように表示する関数)  サンプルファイル UNIQUE関数
サンプルファイル
関数入力
 左上がサンプルファイルです。セルF2からI21に、データがあると表ができる条件付き書式を設定してあります。
 右上の関数入力のセルF2に、
  
=UNIQUE(D2:D21,FALSE,FALSE)
を入力します。UNIQUE(配列,[列の比較],[回数指定])になります。
 「配列」は重複データを含むセル範囲を指定します。この表の場合はセルD2からD21にラーメンやカレーなどのデータが重複しながら表示されています。「$」はつけません。
 「[列の比較]」は行方向に検索するか、列方向に検索するかを指定します。行方向(右方向)は「TRUE」、列方向(下方向)は「FALSE」です。この表の場合は列のデータを検索したいので「FALSE」になります。
 「[回数指定]」は1回のみのデータを表示したい場合は「TRUE」、重複したデータも含めて表示したい場合は「FALSE」です。この表の場合は重複したデータも含めて表示したいので、「FALSE」になります。
  
=UNIQUE(D2:D21)
だけでも大丈夫です。以降の「[列の比較]」と「[回数指定]」は自動的に「FALSE」が選択されます。
 関数を入力した結果が左下の表です。
関数入力結果
人数を数える関数入力
 右上の表に人数を数える関数を表示ました。好きな食べ物の人数を数える集計表になります。
 実際の関数は、
 セルG2に 
=COUNTIF($D$2:$D$21,F2)
 セルH2に 
=COUNTIFS($B$2:$B$21,$H$1,$D$2:$D$21,$F2)
 セルI2に 
=COUNTIFS($B$2:$B$21,$I$1,$D$2:$D$21,$F2)
になります。これらの関数を入力後、オートフィルで、下にコピーしてください。

(4)XLOOKUP関数
(VLOOKUP関数に変わる検索の関数)  サンプルファイル XLOOKUP関数

サンプルファイル

関数入力

 これまでの検索の関数「VLOOKUP関数」を使用すれば、セルI2に、
  
=VLOOKUP(H2,$A$2:$G$20,5,0)
と入力し、右へオートフィルでコピーし、セルJ2の関数で「5」を「6」に変え、セルK3の関数で「5」を「7」に変えていました。また、検索値がないとエラーになってしまうので、
  =IFERROR(VLOOKUP(H2,$A$2:$G$20,5,0),"")
とするのが最適でした。しかし、「XLOOKUP関数」使用すると、もっと簡単になります。セルI2に、
  
=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」を使うことになると思います。

                【ホームへ】