« 170514 赤ちゃん猫がもう一匹 | トップページ | 170516 関西ハイク山友会/例会参加者名簿の作成2 »

2017.05.15

170515 関西ハイク山友会/例会参加者名簿の作成

160515__2 関西ハイク山友会の例会は5年半前の2011年11月が第一回で、先月2017年4月までに1095回も計画されていて、途中雨天中止で開催できなかった例会を除いても約1000回近く開催されています。
 運営委員会などの話題で、過去の参加者名を名寄せして、例会参加者ランキングを作りたい、という要望があがりました。
 参加者名はHPの山行報告では個人名を掲載していないので、個人名は印刷物である会報から拾うしかありません。
 7月8月の計画書を提出して少し時間的に余裕が出来たので、今朝から参加者名簿の抜出しに挑戦してみました。
 添付画像の2行目に関数式を表示していますので、参考にして下さい。
 EXCELの関数は膨大な種類があって使いこなすのは大変ですが、困ったときのネット頼みで、ネットで検索すると素晴らしい解決策が出てきます。

◆ 参加者名抽出のためのEXCEL表の作成手順
Step1:参加者名簿からの参加者名簿抽出

1. 例会のリストはHPの例会一覧表をそのままコピーしてEXCELに貼り付けます。(セルの列A~K)
   
2. Wordの参加者数から参加者数合計を抜き出します。(L)
   参加数はHPの一覧表示とWordの参加者名の人数とで異なる場合があります。
   参加者数はWordの参加者数からコピーした人数と、L+SL+参加者の人数を合計した参加者計、HP記載の参加者数の差異を列Sでチェックします。

3. 開催年、開催月は一覧表のヘッダーから式で引っ張り、開催日と共に開催年月日を表示します。(列M~O)
   年/月/日の形式で表示しても日付書式にはならないので、セルの値+0にすると日付形式になります。(列P)

4. 例会中止の場合は中止理由が分かるので、列を作っておきます。(列Q)
   開催例会だけ抜き出す場合は、この列で振り分け出来ます。

5. 例会№はあとで並べ替えるので数値のまま一覧表からコピーします。(列R) 

6. 人数チェック列はHPの一覧表示とWordの参加者名の人数とで異なる場合があるの必要です。
   L、SL、参加者の合計がI列またはL列の参加者数と一致するかチェックします。(列S)

7. wordの例会報告(二ヶ月ごと)を開いて、各例会の参加者名をコピ&ペ-ストします。(列T)
   テキスト貼り付けしても自動的にセル内で折り返され行間が広がってしまいますので、取りあえず行間を狭くしておいて、あとで列単位でセルの書式からセルの配置→折返し無しにします。
   各セルには関数式が設定されているので実際の作業はこのステップだけです。

8. 半角文字を全角文字に一括変換 (列U)
   各セルの内容を輪切りにして別のセルに貼り付けますから、()、()のように見かけ上、区別が付かない文字は空白も含めて全て全角文字に統一します。
   EXCELでは各セルに入力できる文字は255文字という制約があります。
   参加者が多い例会では255文字の制限を超えますので、半角→全角変換のJIS()関数を文字数255字で分けて連結します。(セルU2)

9. リーダー名の抽出 (列W)
   参加者リストで(L)の後ろに続く氏名を抽出します。
   中止例会でも会報ではリーダー名だけ掲載されていますが、参加者数は0なので、リーダー名を空白にするために、会員参加者数(列AD)が空白の場合は、リーダー名は表示しないようにします。(セルW2)

10. リーダー人数 (列X)
   通常リーダーは一人ですが、二人以上になる場合と参加者数を計算するために、"、"(全角句読点)を意識して人数を表示します。 (セルX2)

11. サブリーダー名の抽出 (列Z)
   サブリーダー名は会報の例会報告では(SL)のあとに記載されています。
   中止例会でも会報ではSLとして氏名が掲載されていることがありますが、参加者数は0なので、サブリーダー名も空白にするために、会員参加者数(AD)が空白の場合は、サブリーダー名も表示しないようにします。(セルZ2)

12. サブリーダー人数 (列AA)
   サブリーダーは無しの場合、一人の場合、二人以上になる場合があります。
   参加者数を計算するために、(参加者)の項目から"、"(全角句読点)を区切りとして人数を表示します。 (セルAA2)
   サブリーダーがいない場合はSL人数欄は空白にしています。

13. 参加者名の抽出(列AC)
   参加者名は会報の例会報告では(参加者)のあとに記載されています。
   中止例会では参加者は無しなので会員参加者数(列AD)が空白の場合は、参加者名は空白です。(セルAC2)
   (添付画像ではL、SL以外の参加者の個人名は網掛けしています)

14. 参加者数(列AD)
   会報で参加者欄が無い場合は参加者数の欄は空白にします。

15. 全参加者リスト(列AF)
    全参加者のリストはL名、SL名、参加者名を&(アンド)と"、"(句読点)でつないで一つのセルに集約します。
    SLがいない例会ではL名と参加者名を同様に連結します。

◆ 今後の作業
Step2:参加者名簿の縦展開
・ 参加者名の抽出が終わると、一例会一セルに参加者が全て含まれます。
・ このままでは参加者個別データに展開できませんので、"、"(句読点)を意識して、個人名ごとに一人一つのセルに分解して、一例会一列の列データを作ります。
・ 具体的には"、"(句読点)を改行コード(CtlrP、またはCtlrL)に置換して、横データを縦データに変換します。
・ 縦データを名寄せするには、VLOOKUP関数を使って、全会員の名簿から会員番号を付加します。
・ 会員番号順に並べます。
・ 会員ごとの集計で参加回数や、リーダー別の参加者リストが作成できます。
・ etc

◆ 留意点
・ 参加者名簿は全てを一つのファイルにおさめるのはデータが大きくなりすぎるので、会報の単位(二ヶ月)で一つのファイルにします。
・ 年度ごと、通算の参加者リストを作成するときは、データだけを各二ヶ月単位ファイルから抽出して、出来るだけコンパクトなファイルにする必要があります。
・ 二ヶ月分を作るのにかかる時間はおそらく2時間程度で済むと思います。
・ 会報の参加者リストで例外が出ると思われるのは会員外参加者ですが、仮の会員番号、たとえば先頭にG
を付加した連番を追加で設定するのがよいと思います。

Posted on 5月 15, 2017 at 05:30 午後 |

コメント

コメントを書く