Excel で、Zoom のチャットから発言者とコメントの一覧を作る。

この記事は自分用の覚書です。

■MacBook Pro macOS Catalina
バージョン 10.15.6

■Microsoft 365 Apps for businessサブスクリプション
(旧称 Office 365 Business)
Excel for Mac バージョン 16.39 (20071300)

K.M さん。K.K さん。

テキスト保存した Zoom のチャットから、発言者名と正味のチャット部分というか、コメントというかの一覧を、Excel で作るやり方です。

■課題

保存した Zoom のチャットを開くと次のようになっていることが多いですよね。
macOS 用のテキストエディタ「mi」（えむあい）の画面です。
（通常は「テキストエディット」で開かれます。）

これをエクセルにコピー貼り付けすると、こうなります。

結果として、こうしたいということですよね。

■考え方

次図のB列の文字列で、
・" 開始 "（"開始" の左右に半角スペースがある。）
・" : " （半角コロンの左右に半角スペースがある。）
が不要なわけですが、発言者名とコメントの境目にある ":" （半角コロン）は左右を分ける目印として生かしたいと思います。

そこで、
・" 開始 " をスペースごと取り除く。
・" : " からは、左右のスペースを取り除く作業をまずします。スペースを無くしておいた方が、このあと関数の数式を作りやすいので。

SUBSTITUE関数で、" 開始 " の部分を無くす置換をします。
また、 " : " は左右のスペースがない ":" （半角コロンのみ）に置換します。

そうすると、

Mitsuhide Akechi:6月2日の本能寺の件、よろしくお願いします。

ということになります。

これができれば、FIND関数・LEN関数・LEFT関数・MID関数を使って、":" の左右を分けて抜き出すことができます。

・FIND関数
元の文字列の中で ":" が左から何文字目にあるかを調べます。

・LEN関数
元の文字列の全文字数を調べます。

・LEFT関数
FIND関数の結果を使って、元の文字列の左端から ":" の1文字前まで（つまり、発言者名）を抜き出します。

・MID関数
FIND関数の結果を使って、":" の1文字右隣からコメントを抜き出します。
何文字抜き出すかの指定には、LEN関数の結果を使います。
LEN関数の結果を使うことで、コメントが何文字あっても全部抜き出すことができます。
（32,767文字まで抜き出せます。セルに入れられるこの文字数は Excel の仕様です。）

■実際の作業

次のようにしてみました。
図のH列とI列で、発言者名とコメントが分かれています。
（1行目の列見出しは適当につけてます。）

作業列を使っています。

先の処理を1つのセルでやってしまうと、何をしているのかを理解しづらい数式になってしまいます。
作業列を使って、処理の過程を複数に分けておくと、うちの社内では誰がいつ見ても理解しやすくしておけます。

詳しくは解説しませんが、何をしているかは見ればわかると思います。

関数の中に関数を挟んだ数式（「関数のネスト」といいます。）を、作ってはいけないということではないです。
ネストするしかない場合もありますから。

仕事で作るファイルは誰のものでもありません。職場の全員のものです。
引き継ぐかもしれませんし、流用するかもしれません。そんなときに、何をしているのかを理解しづらいファイルを渡されても、渡された方が困ります。

■チャットが改行されているとき。

次のようにチャットが改行されているときもありますよね。

これをエクセルにコピー貼り付けするとこうなります。

本来だと、A列の3行目から5行目までの文字列は、B列に入っていてほしいものです。

幸いなことに、このパターンには法則性があります。
B列のセルが空である場合は、A列に文字列が入っているという法則性です。

こういうときは、条件によって処理を分けるやり方が適用できます。

C列のセルに、「B列のセルが空欄ではないなら、そのセルの値を表示する。B列のセルが空欄ならA列のセルの値を表示する」という式を入れてやればいいんです。

図では、IFS関数を使っています。
（Excel2016以前だと、IF関数になりますね。）

そして、D列以降で上記と同様のやり方をします。

ただし、今回は、":" がなくて、「・」（中黒）があります。
また、この場合は「・」も含めて抜き出したいので、列「発言者とコメントの間の値の位置」は 0 になっていることが必要です。
さらに、列「発言者」では、発言者名がそもそも無いのでエラーが起きます。

このあたりも条件分岐で処理できます。

行頭に中黒を付けるときは、たいていそれも含めて抜き出したいでしょうから、下図のG列では ":" ならその位置を、中黒なら 0 を返すようにしてみました。

中黒以外が行頭に付くことは、そんなにないでしょうから、そこはもう手入力で対応してください。（下図F列）
行頭文字も含めて抜き出すなら、G列のセルに 0 を入力します。

列「発言者」（I列）のセルは、エラーの場合は非表示にしてみました。
エラーが見えたままよりも非表示にしておいた方が、一覧表としてより見やすくなるかなと思ったので。
IFERROR関数の引数として、LEFT関数を使っています。「関数のネスト」ですね。

最終的には、非表示のままにしておくのではなく、発言者名を入れておいた方がよいと思います。
発言者名で絞り込むこともできますから。