FC2ブログ

TEXTJOIN 関数の使い方 ちょっと複雑

TEXTJOIN 関数の便利?な 使い方

このような表があった場合

 201905170001.png

例えば、「経済学部」 の 人の名前だけを
  1つのセルに、抜き出す
  横に一覧表を作成する

 201905170002.png

結論から
 F2 セルに、以下の数式
 {=TEXTJOIN("、",TRUE,IF($C$2:$C$15=E2,$B$2:$B$15,""))}
 下へ、オートフィル

 201905170003.png

 区切り文字が、「、」
 空白の場合は、連結の対象としない・・・ TRUE
 C2:C15 = 学部名が、
 E2 = 経済学部 の場合 
 B2:B15 = スタッフ
 違う場合は、空欄
作成した関数が
 =TEXTJOIN("、",TRUE,IF($C$2:$C$15=E2,$B$2:$B$15,""))

なのですが、

IF 関数の内容が、配列 なので、
数式を 有効にするため
[Ctrl]+[Shift]+[Enter]キーの同時押し


その結果、前後に 【 { 】 ・ 【 } 】 が付き
 {=TEXTJOIN("、",TRUE,IF($C$2:$C$15=E2,$B$2:$B$15,""))}
 配列数式 と なる

これも TEXTJOIN 関数 だから 可能になった
 * 空欄を無視して、結合できる
 * セル範囲で、結合するセルを選択できる

 
スポンサーサイト

新しい関数について CONCAT 関数 TEXTJOIN 関数

※ CONCAT 関数・ TEXTJOIN 関数 が、使えるもの
  Office 365
  Excel Online
  Excel Mobile
  Excel for Android
  Excel 2019

  WPS Office Ver 10.8.2.6709
  LibreOffice

   Microsoft Excel 2013 以前では、使用できない
   Excel 2016 でも 永続版=買取タイプでは、使用できません
   定額料金(サブスクリプション)型の Office
   「Office 365」でないと 使えません

CONCAT 関数

セル内の文字列を結合する場合
今までは、
を 使用するか
CONCATENATE 関数 を使うしかありませんでした
それが、この新しい関数
便利です 下の画像のように使える

 201905160015.png

A1 セルから D1 セルまでの文字列を結合するのに
CONCAT 関数の引数に、A1:D1 と、セル範囲を選択するだけで済む
=CONCAT(A2:D2)
今まででしたら
& を使うと
=A2&B2&C2&D2
CONCATENATE 関数では
=CONCATENATE(A2,B2,C2,D2)
このように、セルを 1つ1つ選択しなければならなかった

 201905160016.png

TEXTJOIN 関数 テキストジョイン関数

こっちの関数は、ただ単に、文字列を結合するのではなく
文字列と文字列の間に、指定した文字列等を入れることが出来る

区切り記号を 挿入しながら 複数の文字列を連結する

指定した文字列等=区切り記号


TEXTJOIN ( 区切り記号, 空の文字列を無視, 文字列1, 文字列2, ..., 文字列252 )

 201905160017.png

よくある 
セルに「2019」・「5」・「16」と入力されている
これを 日付にしたい
こういった場合に、この関数は便利
文字列を結合するだけでなく
間に「/」を入れて、「2019/5/16」にすることが簡単にできる
=TEXTJOIN("/",TRUE,A1:C1)

「区切り文字」=「/」
A1 セルから C1 セルの文字列

 201905160018.png

 201905160019.png

ただ このままでは、文字列なので 」
日付とする場合は、1 を かけてシリアル値に変換する
=TEXTJOIN("/",TRUE,A1:C1)*1

別の使用例

 201905160021.png

区切り文字を 1つではなく
複数順番に、違う文字列を挿入させる

東 京 の後ろに、「都」
新 宿 の後ろに、「区」
歌舞伎の後ろに、「町」
=TEXTJOIN({"都","区","町"},TRUE,A1:D1)

 201905160020.png

 201905160022.png


注意
 この関数を使用したファイルを
 Microsoft Office を 使用している方に
 データとして渡す場合
 どのバージョンを使用しているか? 
 確認してください
 不安な場合は、PDFにするか 
 新関数の部分を コピーし 「値」で 貼り付けする
 Office365 以外の方の場合
 この関数は、使用できません
 ※ Personal は、永続版なので 新しい関数は、使用できません
 
  関数を 文字列 として 認識するので
  「 #NAME? 」 と 表示される
  このようになります

Microsoft の サポートページ

 数式の前に _xlfn. という接頭辞が表示される 
.
要点だけ
 現 象
  数式を計算すると、#NAME? が表示されます
 原 因
  使用している Excel のバージョンで、
  サポートされていない機能(=新関数)が 含まれています
 解決方法
  サポートされていない機能(=新関数)を 削除するか、
  可能な場合は、
  サポートされている機能(=従来の関数)と
  サポートされていない機能(=新関数)を 置き換えます
 注 意
  これらの新しい関数は、
  Office 365 サブスクリプションがある場合に使用できます
 

新しい関数について SWITCH 関数

SWITCH 関数 が、使えるもの
 Office 365
 Excel Online
 Excel Mobile
 Excel for Android
 Excel 2019

 WPS Office
 LibreOffice

 複数の値を検索して
 一致した値に 組み合わせられた結果を返す

(検索値, 値1, 結果1, 値2, 結果2, ..., 値126, 結果126, 既定の結果)
 201905120009.png

例えば
書類の入力欄で、リストから選択する設定がされているセル
選択されたら、それに応じて表示する設定に使える
簡単な例
 性別欄 
 「A」 を 選択すると 「男性」
 「B」 を 選択すると 「女性」
 選択されていない場合
 「選択するような文章」
 =SWITCH(D2,"A","男性","B","女性","必ず入力してください")

 201905120010.png

 これは。簡単にするために、男女の2つからですが
 VLOOKUP 関数の代わりに
 「」の位置へ、検索の左列に相当するもの
 「結果」の位置へ 求めるものを入力すれば
 元となる表が無くても 使える
 なおかつ
 検索に無い場合の表示を
 「既定の結果」へ 設定できる


 「 ID 」の選択を、A~E で、選択させる

 201905120016.png
 
 「 ID 」 を 選択しない場合は、
 「インストラクター」は、決まった人が表示される
 =SWITCH(H3,"A","小井出圭吾","B","斎藤隆文","C","有賀由紀子","D","星野良一","栗原千里")
 この数式では、以下のような感じ

 201905120015.png

 201905120011.png

 今まででしたら
 インストラクターの一覧表を作成して
 =VLOOKUP(H3,$K$3:$L$7,2,0)

 201905120012.png

 2つの違い
 例えば、「 ID 」欄が空欄の場合
 SWITCH 関数では、空欄の場合でも 
 「それ以外の場合」 という設定が あるので、表示される
 
 201905120014.png

 VLOOKUP 関数の場合は、このままでは、エラー(#N/A)になる
 ※ IF 関数を組み合わせ、空欄の場合は、** という設定が必要
 201905120013.png

ここまでは、「検査値」欄は、セルをそのまま指定しましたが
 関数を使った式も、使えます
 例えば、RANK 関数を使用し、
 求められる順位で、決めた文字列を表示させることも可能です

 ちょっと変わった方法で
 成績の300以上を「優」
 200 以上を 「秀」
 100 以上を 「可」
 それ以外を 「不可」
 ※ 合計点を 100で割り、商で判断する
 =SWITCH(QUOTIENT(I3,100),3,"優",2,"秀",1,"可","不可")

 201905120017.png
 ※ SWITCH 関数のために、 急遽作成したので、満点(400)を無視しました

  いろいろな使い方が、出来そうな SWITCH 関数

 Excel 2019 では、使えるという情報がありますが
 私は、Excel 2019 を 持っていないので
 未確認です
 
 WPS Office で、作成したものを
 Excel で、開いた場合 
 新しい関数を、使用できないバージョン(ほとんど)では
  関数を 文字列 として 認識するので
  「 #NAME? 」 と 表示される
  数式バーの表示は
   =_xlfn. から、始まるものに変化します
  
Microsoft の サポートページ
 数式の前に _xlfn. という接頭辞が表示される 

要点だけ
 現 象
  数式を計算すると、#NAME? が表示されます
 原 因
  使用している Excel のバージョンで、
  サポートされていない機能(=新関数)が 含まれています
 解決方法
  サポートされていない機能(=新関数)を 削除するか、
  可能な場合は、
  サポートされている機能(=従来の関数)と
  サポートされていない機能(=新関数)を 置き換えます
 注 意
  これらの新しい関数は、
  Office 365 サブスクリプションがある場合に使用できます
  

新しい関数について IFS 関数

IFS 関数 (イフ・エス関数)

 ※ Microsoft Excel 2013 以前では、使用できない
   Office 2016 でも 永続版=買取タイプでは、使用できません
   定額料金(サブスクリプション)型のOfficeである「Office 365」でないと使えません

   しかし、WPS Office Spreadsheets では、使用できます Ver 10.8.2.6709

新関数 「IFS 」 ( イフ・エス )
複数の条件を順に調べた結果に応じて異なる値を返す

[論理式1] が 真であれば [真の場合1]の値を 返し
  偽であれば [論理式2] を 調べます
以後は
[論理式2] が 真であれば [真の場合2]の値を 返し
  偽であれば [論理式3] を 調べる......
というように
複数の条件を 順に調べた結果に 応じて、異なる値を返します

例えば、下のような成績表があり
合計点により、ランク分けを行う場合に使える

 ※ ランク条件
  A : 260以上 
  B : 240以上
  C : 210以上
  D : 180以上
  E : それ以外


  201905120001.png

 「ランク」を表示する J3 セルをクリック
 [関数の挿入]ダイアログ IFS (イフエス)を表示
 「論理式」欄で、「合計」が入力されているセル I3 セルを 選択
 ランク最初の「A」の条件 260以上 「 >=260 」を 追加する
 ※ I3>=260

 201905120002.png
 ※ 論理式1 には、結果が、TRUE または、FALSE になる値 もしくは、数式を指定します

 次は、条件が、正しい場合の結果 「A」を入力する

  201905120003.png
  ※ 真の場合 には、論理式の結果が TRUE で あった場合に 返される値を 指定する

 あとは、条件を 順番に入力し、結果を入力する

 201905120004.png

 ランク D の条件 180以上まで、設定しました
 最後の「それ以外」ですが、
 この部分だけ、注意してください
 それ以外の位置の「論理式」欄には、「TRUE」と 入力します
 「真の場合」欄には、結果この場合は「E」と入力する


   =IFS(I3>=260,"A",I3>=240,"B",I3>=210,"C",I3>=180,"D",TRUE,"E")

 201905120005.png

 あとは、下へオートフィルするだけです

 201905120006.png

これを Microsoft Excel ファイル (*.xlsx) 形式で、保存
Excel 2016 Personal で、開くと

  201905120007.png

 Excel 2016 ですが、Personal は、永続版なので
 新しい関数は、使用できません
 そこで、
  関数を 文字列 として 認識するので
  #NAME? 」 と 表示される
  数式バーの表示は
   =_xlfn.IFS(I3>=260,"A",I3>=240,"B",I3>=210,"C",I3>=180,"D",TRUE,"E")
  このようになります

Microsoft の サポートページ

 数式の前に _xlfn. という接頭辞が表示される 
.
要点だけ
 現 象
  数式を計算すると、#NAME が表示されます
 原 因
  使用しているExcel のバージョンで、
  サポートされていない機能が含まれています
 解決方法
  サポートされていない機能を 削除するか、
  可能な場合は、
  サポートされている機能
  サポートされていない機能を 置き換えます
 注 意
  これらの関数は、
  Office 365 サブスクリプションがある場合に使用できます

 

新しい関数について MAXIFS ・ MINIFS 関数

 MAXIFS 関数

 ※ Microsoft Excel 2013 以前では、使用できない
   Excel 2016 でも 永続版=買取タイプでは、使用できません
   定額料金(サブスクリプション)型のOfficeである「Office 365」でないと使えません

   しかし、WPS Office Spreadsheets では、使用できます Ver 10.8.2.6709


 以下のような一覧表が あった時に
 性別が、「男」で、「10代」の最高(最低)金額を求める
 ※ 条件が、一つではなく、複数の条件の場合に使用する

  201905110001.png

 求める値を表示するために、表を作成する

  201905110002.png

 最高金額の P2 セルに、MAXIFS 関数を設定する
 「関数の引数」を表示すると
 第一引数は、「最大範囲」です
 ※ 下に書かれているように、最大値を求めるセル範囲
 今回の場合、「金額」が入力されている J列の範囲
 
 201905110003.png

 J2 セルを選択
 [Ctrl]キーと[Shift]キーを押しながら、[↓]キーを押す
 データがある最後のセルまで選択できる
 関数を設定するのが、他にもあるので
 絶対参照にする( [F4] キーを押す)

 201905110004.png

 第二引数は、「範囲1」
 説明では、「評価の対象となるセル範囲」とあります
 条件の対象となるセル範囲です
 最初は、「性別」が 「男」の場合にします
 なので、セル範囲は、「性別」の K列となります
 最初の K2 セルをクリック 
 [Ctrl]キーと[Shift]キーを押しながら、[↓]キーを押す
 同様に、絶対参照にする

 201905110005.png

 第三引数は、「検索条件1」
 計算の対象となるセルを定義する条件を、
 数値・式または 文字列で指定
 今回は、「男」なので、
 直接 ”男” と入力しても 良いのですが
 表示する表を利用して、N2 セルを指定します

 201905110006.png

 第四引数の「範囲2」は、条件の2つ目のセル範囲
 今回のケースでは、「年齢層」のセル範囲
 そして、検索条件は、表示する表の「10代」と入力されている O2

 201905110007.png

もちろん、もっと条件があった場合は、同様の操作で追加していけばよい
今回のケースは、これで {OK] クリック

201905110008.png

このまま、下へオートフィルすれば良い

 MINIFS 関数

 「 MAXIFS 」 が、「 MINIFS 」 に なっただけで
 範囲・条件は、まったく同じ

 201905110009.png

 201905110010.png


  こういった関数では、セル範囲の幅が
  すべて同じでなければならない
  位置が違っても、良いのですが 375行の範囲でなければならない
  376-2+1=375 セル範囲    ※ 2行目から 376行目


注意
 この関数を使用したファイルを
 Microsoft Office を 使用している方に
 データとして渡す場合
 どのバージョンを使用しているか? 
 確認してください
 不安な場合は、PDFにするか 
 新関数の部分を コピーし 「値」で 貼り付けする
 Office365 以外の方の場合
 この関数は、使用できません
 ※ Personal は、永続版なので 新しい関数は、使用できません
 
  関数を 文字列 として 認識するので
  「 #NAME? 」 と 表示される

  201905120008.png

  数式バーの表示は
   =_xlfn.MAXIFS($J$2:$J$376,$K$2:$K$376,N2,$L$2:$L$376,O2)
  このようになります

Microsoft の サポートページ

 数式の前に _xlfn. という接頭辞が表示される 
.
要点だけ
 現 象
  数式を計算すると、#NAME? が表示されます
 原 因
  使用している Excel のバージョンで、
  サポートされていない機能(=新関数)が 含まれています
 解決方法
  サポートされていない機能(=新関数)を 削除するか、
  可能な場合は、
  サポートされている機能(=従来の関数)と
  サポートされていない機能(=新関数)を 置き換えます
 注 意
  これらの新しい関数は、
  Office 365 サブスクリプションがある場合に使用できます