ドロップダウンリストの選択肢に基づき、関連する列を自動生成する方法　

（この記事は関数の好きな人向けです。特に後半は式の羅列です。単にこの方法を使いたい方は、一番後ろにダッシュボードのリンクがありますので、式をコピーして試してみてください。）

Smartsheetコミュニティーの未回答質問の中に、以下のようなものがありました。

4つのカラムがあります

  • 1 教員名（複数選択可能なドロップダウンを設定します。）
  • 2 教員の電子メール（Text/Numberでなければならず、別の理由でContact list型ではない）
  • 3 管理アシスタント名（テキスト/数字タイプ）
  • 4 管理アシスタントのEメール（テキスト/番号または連絡先タイプのいずれかを選択できます。）

ドロップダウン選択（複数選択可）に基づき、2〜4列を自動入力することは可能でしょうか。今は手動で追加する必要がありますが、2-4はすべて#1の選択肢に依存しています。

この自動入力の方法を考えてみましたので、以下に解説します。

複数のドロップダウンリスト選択に基づき関連する列を自動生成する

方法の要旨

  1. ドロップダウンリストの複数選択の名前からそれぞれの名前を切り出す
  2. 別に名前とメール、関連する人の名前およびそのメールのリストを用意する
  3. １で取得した名前を元にメール、関連者名、関連者メールを取得する
  4. 取得した情報を一つのセルにまとめる
    • メールについては単純にJOINする
    • 関連者については、複数の選択した名前に一人の関係者が設定されている可能性があるので、DISTINCT関数を使い、ユニークな情報のみをセルにまとめる
      • これは、例えば、教官１、教官２の共通秘書として、秘書１が指定されている、といった場合を想定しています。
      • 例えば、デモのLook UPリストでは教官のOlivia、Liamの共通管理アシスタントがAmeliaとなっています

なお、選択した名前から関連する人の名前およびメールを参照するための表は以下のようなイメージです。

関連名前、メール参照表

ドロップダウンリストの複数選択の名前からそれぞれの名前を切り出す方法

ドロップダウンリストはCHAR(10)で区切られているので、その位置を調べる

  • デモ例ではc1, c2,  c3, c4と4か所目まで調べています
  • COUNTM()関数でドロップダウンリストの複数選択の数を調べる(c#)
  • c#の数を利用して、c1～c4の値を求めます。　
  • 式は以下のようになります
    • c1 =IF([c#]@row >= 2, FIND(CHAR(10), [Selected names]@row))
    • c2 =IF([c#]@row >= 3, FIND(CHAR(10), [Selected names]@row, [c1]@row + 1))
    • c3 =IF([c#]@row >= 4, FIND(CHAR(10), [Selected names]@row, [c2]@row + 1))
    • c4 =IF([c#]@row >= 5, FIND(CHAR(10), [Selected names]@row, [c3]@row + 1))
  • 式の意味は
    • 選択肢が１以上であれば最初の区切り文字の位置を探す
    • 選択肢が２以上であれば、最初の区切り文字の次の位置から初めて次の区切り文字の位置を探す
    • 例えば、選択肢が２個、３個、４個等の場合、c2はこの式で求めます
    • FIND(CHAR(10), [Selected names]@row, [c1]@row + 1))
    • c3以下も同様です

その位置情報をもとに、文字関数（ここではMID（）を用いています）を使って、個々の名前を切り出す

  • 式は以下のようになります
    • n1: =IF([c#]@row <= 1, [Selected names]@row, LEFT([Selected names]@row, [c1]@row – 1))
    • n2; =IF([c2]@row > [c1]@row, MID([Selected names]@row, [c1]@row + 1, [c2]@row – [c1]@row), IF([c2]@row < [c1]@row, MID([Selected names]@row, [c1]@row + 1, LEN([Selected names]@row) – [c1]@row + 1)))
    • n3: =IF([c3]@row > [c2]@row, MID([Selected names]@row, [c2]@row + 1, [c3]@row – [c2]@row), IF([c3]@row < [c2]@row, MID([Selected names]@row, [c2]@row + 1, LEN([Selected names]@row) – [c2]@row + 1)))
    • n4: =IF([c4]@row > [c3]@row, MID([Selected names]@row, [c3]@row + 1, [c4]@row – [c3]@row), IF([c4]@row < [c3]@row, MID([Selected names]@row, [c3]@row + 1, LEN([Selected names]@row) – [c3]@row + 1)))
  • 式の意味は
    • 選択肢が１以下の場合は、選択肢の値そのもの、１以上の場合は、最初の区切り文字の左側
    • 選択肢が２以上の場合は以下のように考えます
      • c2 > c1　の時　（選択肢２の次に選択肢３以降が続いている）
        • MID([Selected names]@row, [c1]@row + 1, [c2]@row – [c1]@row)
        • c1の次の場所からc2-c1文字をMIDで切り出す
      • c2<c1  の時　（区切りが１個しかなく、c2 が空白の場合、つまり、後に選択肢がない場）
        • MID([Selected names]@row, [c1]@row + 1, LEN([Selected names]@row) – [c1]@row + 1)
        • c1の次の場所から選択肢のセルの長さからc1を引いた値に１を足しただけMIDで切り出す
      • c2=c1 の時　（あるいは次のサイクルで c3=c2の時）
        • 区切りがないものが続いているので何もしない

取得した名前を元にメール、関連者名、関連者メールを取得する

これは普通にVLOOKUP()とかINDEX(), MATCH()で別のシートから持ってきます

取得した情報を一つのセルにまとめる

  • email
    • 単純にJOINで繋げます
    • なお、縦に改行して表示する場合は、JOIN(範囲, CHAR(10))　とCHAR(10）の区切り文字を使います
  • 関連者、関連者メールの場合
    • 上で説明したように、何人かの教授、教官を一人の共通秘書が担当している場合が考えられるので、DISTINCT関数でユニークな値のみJOINでつなぎます
    • 式の例
    • =JOIN(DISTINCT([rn1]@row:[rn4]@row), CHAR(10))

まとめ

今回の記事はやや難しかったかもしれませんが、関数をマスターすると色々なことができるということがご理解いただければ幸いです。

ポイントは複数選択のドロップダウンがCHAR(10)で区切られていること、文字関数を利用して複数選択の各項目を切り出すための位置情報をシステマティックに計算すること、です。

こちらのリンクにデモのダッシュボード（英語）があります。英語が苦手な方は、Google翻訳とかで日本語にしてみてください。

Auto-generate columns based on drop-down choices – Smartsheet.com

コメントを残す