« 2020年7月 | トップページ | 2020年9月 »

Excel で、整数Xと整数Yの比を求める。

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

■MacBook Pro macOS Catalina
バージョン 10.15.6

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

整数Xと整数Yの比は、整数Xと整数Yの最大公約数でそれぞれを割ると求めることができる。

整数Xと整数Yの最大公約数は、GCD関数で求めることができる。

X_y_no_hi_20200831163801

| | コメント (0)

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」(えむあい)の画面です。
(通常は「テキストエディット」で開かれます。)

01zoom_chat

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

02_1zoom_chat

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

02_2zoom_chat

■考え方

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

02_1zoom_chat

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

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行目の列見出しは適当につけてます。)

03zoom_chat

作業列を使っています。

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

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

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

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

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

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

04zoom_chat

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

05zoom_chat

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

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

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

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

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

06zoom_chat

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

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

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

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

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

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

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

07zoom_chat

| | コメント (0)

« 2020年7月 | トップページ | 2020年9月 »