名簿の合体と整理（兼務の処理は？）

質問

現在、職場の名簿を２つの部署からデータを頂戴し、合体して個人１行の名簿に整理しようとしております。
１つは情報システム部からもらったメールアドレスが掲載されている名簿で、全職員が載っていますが、所属部署名が間違えていたりします。
もう１つは、人事課よりもらった名簿で、所属部署側から整理されており、所属部署名は間違えておりませんが、ある一部の職員しか載っておりませんし、メールアドレスもありません。

そして、最大の問題は兼務が多いため、兼務している方は複数回登場します。

情報システム部からもらった職員全員の名簿を正しい所属情報にし、
[メールID][氏名][ふりがな][所属１][所属２（兼務先）][所属３（兼務先）]・・・（多分所属欄は６つは必要だと思います。）という名簿を作るには、どのようにしたらよいのでしょうか？

所属部署名は置換で地道に置き換えた方が早いでしょうか？
情報システム部からいただいた名簿は整理して3000レコード、
人事課からいただいた所属部署数は140です。これに抜けている部署があるので、最終的には160程度でしょうか。
把握している限りでも同姓同名が何組かおります。

クエリの作り方をご指導いただくか、似たようなことを紹介しているサイトをご紹介下さい。

この名簿作りが本来の仕事ではなく、その完成した名簿を少し引用したデータ作りの作業をしており、それが切羽詰まって１週間も経っているため、皆さまにおすがりする次第です。

説明が下手なので、分かりづらいと思いますが、補足いたしますので、よろしくお願いいたします。

環境：Windows XP / MS-Office 2002

回答No.2

venzou · ベストアンサー

質問文に「クエリ」と言う言葉があるので、Accessかな？

------------------------------------------------------------
まず、質問です。
所属部署に関してですが、主な所属と兼務の所属は区別しますよね？
主な所属は必ず１つ、兼務は複数（多くて５つ位）　ですか？
人事課のデータでは、主な所属と兼務の所属の区別が出来ますか？

細かい仕様が分からないので、推測を含めながら回答を書きます。

------------------------------------------------------------
情報システム部からのデータを下記と仮定します。

　情報（[社員ID],氏名,ふりがな,メールアドレス,部署名）

表記は下記の形式です。

　テーブル名　（フィールド名, フィールド名, フィールド名, ・・・）
　[ ] の中が主キー

情報システム部が管理しているデータなら、
何らかのユニーク番号はありますよね？
仮に「社員ID」としておきます。
なければオートナンバーのフィールドを追加して下さい。

人事課のデータを下記と仮定します。

　人事（部署名,氏名）

こちらは、ユニーク番号なしと仮定しておきます。

------------------------------------------------------------
＞[メールID][氏名][ふりがな][所属１][所属２（兼務先）][所属３（兼務先）]・・・

目標のテーブルですが、繰り返し項目が多いので、
正規化した方が良いと思います。

　部署マスタ（[部署ID],部署名）
　社員マスタ（[社員ID],氏名,ふりがな,メールアドレス,部署ID）
　兼務部署（[社員ID,部署ID]）

非正規形のデータが欲しい場合でも、一旦、正規化した方が良いと思います。

------------------------------------------------------------
まず、部署マスタを作ります。
人事課の情報から、部署名の一覧を作り、IDをつけます。

重複のない、部署名の一覧は下記のクエリ

SELECT DISTINCT 人事.部署名 FROM 人事;

------------------------------------------------------------
次に、「情報」テーブルにフィールド「部署ID」を追加。

情報（[社員ID],氏名,ふりがな,メールアドレス,部署名,部署ID）

下記のクエリで、「部署ID」を更新。

UPDATE 情報 INNER JOIN 部署マスタ ON 情報.部署名 = 部署マスタ.部署名 SET 情報.部署ID = 部署マスタ.部署ID;

もし、「部署ID」にNullのデータがあれば、手作業で修正。
Null以外の間違いは、後ほど修正。

------------------------------------------------------------
次に、「人事」テーブルに、「部署ID」「社員ID」のフィールドを追加。

　人事（部署名,氏名,部署ID,社員ID）

下記のクエリで、「部署ID」を更新。

UPDATE 人事 INNER JOIN 部署マスタ ON 人事.部署名 = 部署マスタ.部署名 SET 人事.部署ID = 部署マスタ.部署ID;

下記のクエリで、「社員ID」を更新。

UPDATE 人事 INNER JOIN 情報 ON 人事.氏名 = 情報.氏名 SET 人事.社員ID = 情報.社員ID;

「部署ID」「社員ID」にNullのデータがあれば、手作業で修正。

同姓同名の社員は、手作業で修正。同姓同名者のリストは下記のクエリ。

SELECT 情報.社員ID, 情報.氏名 FROM 情報 INNER JOIN 情報 AS X ON 情報.氏名 = X.氏名 GROUP BY 情報.社員ID, 情報.氏名 HAVING Count(X.社員ID)>1;

------------------------------------------------------------
次に、「情報」テーブルの所属部署の間違いの修正。

上記の質問の答えが分からないと、答え難いので、保留します。
恐らく、この部分が一番面倒な作業になると思います。

------------------------------------------------------------
「情報」テーブルをコピーし、「社員マスタ」にする。
「社員マスタ」から、不要なフィールド「部署名」を削除。

「人事」テーブルをコピーし、「兼務部署」にする。
「兼務部署」から、不要なフィールド「部署名」「氏名」を削除。
「社員ID」「部署ID」を主キーに設定。

以上でほぼ完成。

補足

venzou さん、ご回答ありがとうございました。

>主な所属は必ず１つ、兼務は複数（多くて５つ位）　ですか？
>人事課のデータでは、主な所属と兼務の所属の区別が出来ますか？
これはいずれもYesです。
しかし、#1さんへの補足にも書きましたが、
>　情報（[社員ID],氏名,ふりがな,メールアドレス,部署名）
の肝心の[社員ID]がありません。あれば、もう少し自分で考えられるかもしれなかったのですが...
これはオートナンバーでよいとのことで、よしとして、
部署マスタはすでにあります。重複・Nullのない部署IDもふってあります。
しかし、最悪なことに「正規化」がわかりません。
これを勉強して、教えてくださった方法にチャレンジしてみます。
細かく教えてくださってありがとうございます。

回答No.4

venzou

>>人事課のデータでは、主な所属と兼務の所属の区別が出来ますか？
>これはいずれもYesです。

どのように区別がつくのでしょうか？
具体的なテーブル構造か、サンプルデータなど示して下さい。

>肝心の[社員ID]がありません。

公式の社員ID等がなくても、今回の整理に使うだけなので、
取り合えずは、オートナンバーで全く問題ありませんよ。

>しかし、最悪なことに「正規化」がわかりません。

あまり難しく考える必要はありません。
具体例を見て頂ければ、分かりやすいと思います。

社員ID,氏名,ふりがな,所属,兼務１,兼務２,兼務３,・・・
001,桶　ウェブ太郎,おけ　うぇぶたろう,情報システム部,人事課,会計課,製造部
002,押絵　グー太郎,おしえ　ぐーたろう,営業部,広報部

上記の様なデータは、兼務のデータが横（カラム）方向に並んでいます。
これは、データベースでは扱い難い状態です。

兼務の情報を別のテーブルに分けます。

社員ID,氏名,ふりがな,所属
001,桶　ウェブ太郎,おけ　うぇぶたろう,情報システム部
002,押絵　グー太郎,おしえ　ぐーたろう,営業部

社員ID,兼務
001,人事課
001,会計課
001,製造部
002,広報部

こうすると、兼務の情報が縦（レコード）方向に並び、
データベースにとっては都合が良いのです。
（人間にとっては、わかりにくいデータになりますが・・・）

正規化については、下記が分かりやすいかな。
http://www.kogures.com/hitoshi/webtext/db-seikika/index.html

正規化したテーブルの例は、No2に有りますので、
それを真似て頂ければ問題ないと思います。

補足

お返事，大変遅くなり申し訳ございません。
結局，時間が足りなくなり，力業で兼務を無視した形で合体してしまいました。
もう少し勉強してから，お礼を記入させていただきます。
ありがとうございました。

回答No.3

hallo-2007

これは、参考にされるのであれば、現在、
社会保険庁が、どうなっているか調べてみては、いかがでしょうか。
参考までに
重複あり氏名と部署名から
一人、1行の氏名で重複した部署名を横列へ広げる方法
A　　　B　　　C
2　　石川　営業課長
2.1　石川　所長
3　　富山　平
4　　滋賀　社長
4.1　滋賀　製造部長
4.1　滋賀　営業部長

氏名順にきちんとソードします。
A2列に　=IF(B2=B1,A2+0.1,INT(A2+1))
下フィルして、氏名の順に１つ数字増える、同じ名前の場合、0.1増える作業列を準備（一人、最大9部署まで兼任）

D　　E　　F　　G　　H
　　　　　0　0.1　 0.2・・・・
2
3
4
5
と準備
E列には、=VLOOKUP($D2,$A$2:$C$10,,2,FALSE)
下フィルして氏名表示
F列には、=VLOOKUP($D2+F$1,$A$2:$C$10,3,FALSE)
右フィル、下フィルして部署名表示
できると思います。（確認はしていません）
VLOOKUP関数をご理解いただけていると思うので、何を実行しようと
しているか、ご理解いただき、応用してください。
このデータができれば、全ての氏名があるデータに、部署名をつけることができると思います。
（但し同姓、同名では、まだ、問題ありますが）

補足

お返事，大変遅くなり申し訳ございません。
結局，時間が足りなくなり，力業で兼務を無視した形で合体してしまいました。
もう少し勉強してから，俺を記入させていただきます。
ありがとうございました。

回答No.1

imogasi

質問者はプログラムやシステム経験者で無いらしく、（ユニークな＝１人１個の）社員コードの存在、かつ両ファイル内項目での存在について触れていません。
もし両ファイルに氏名コードがあれば、社員コードで両ファイルをソートし、社員コードで両ファイルをマッチングし（術語、特別なアルゴリズムが存在する）、両者の同一項目の食い違いのチェックや、一方しかない項目の集約化をします。
このロジックが一番簡単で確実なんですが、１週間やそこらで習得できるほど易しくない。
ーー
本件課題はプログラムを組んで処理する類の課題で、できなければ、それなりに関数や手作業でやるより他ない。もどかしくても、力が無ければできる方法でやることです。誰かに聞いて操作や関数で目の覚めるような方法があると思うのは、まずありません。省力化はできません。
ーー
関数ではVlookup関数（MATCH関数）などの両ファイルからの他ファイルへ検索する方法の応用を考えてください。VLOOKUP関数の利用実例はWEBで照会すれば沢山でてきます。
ーー
意外に有効と思うのは、少ないファイルを多い方の下部に張り付け
社員コードや「部別＋氏名漢字順」などにソートすることです。
収録項目数や内容が両ファイルで違う場合は列位置を修正して後同じ列には同じ内容のデータが来るようにして、貼り付けてください。
同一人は隣接した行に位置づけられるので、相互にチェックしやすくなります。後はサイト（目視）チェックですが意外とすぐ済みます。

補足

imogasiさん、ありがとうございます。ご指摘の個人を特定できるコードですが、どちらのファイルにもありません。
そのため、"最大の問題は兼務が多いため、兼務している方は複数回登場します。"という文章で表現したつもりになっておりました。

確かに、人事課から頂いたデータであるにもかかわらず、明らかに同一人の名前の漢字が間違えていることもあるので、１つ１つチェックするべきかも知れません。

vlookup 関数で探すと、同一人でも最初の行しか引っかからない＝兼務先は見つけられない、と思い、質問させていただきました。

補足になりましたでしょうか？