住所録の作成（エクセル利用）の場合

質問

住所録の作成（エクセル利用）の場合

こんにちは。業務で住所録を使っていますが、種類ごとに異なった住所録を使っており、
毎回住所の更新や確認作業を行っています。
例えばお中元用はお中元の住所リスト、年賀状は年賀状用の住所リスト・・・
といった感じで、中には重複している人も入るのですが、
毎回最新のデータに直ってないという現象が起こっています。

これを何とかすべく、住所録を一元管理したいと思っているのですが、
何かいい方法があれば、と思い、アドバイスを頂きたく・・・。
会社で使うため、いくつかの制限があります。

1）フリーソフト導入禁止。
2）アクセスをいじれる人が居ないので、エクセルで。（オフィス2003利用）
3）何を送ったか、という記録を残したい。（2009年お中元、年賀状…など記録は増える）
4）更新した記録を残したい（誰がいつ修正したかなど）
5）OSはWINXPです。

上記を踏まえ、添付画像のような形で作ってみようかと思っていますが、
他にもっといいやり方や、アドバイスなどをいただけたら幸いです。

誰にも相談できない状況で、これがベストかどうかという判断も出来ないため
こちらに質問させていただきました。

皆様のお知恵を拝借できれば幸いです。どうぞよろしくお願い致します。

回答No.8

GOCHISOUda · ベストアンサー

注意書きが足りなかったかもです。

履歴を使用するファイルは重たく（ファイルサイズが大きく）なります。
ファイルサイズやサーバー許容範囲等を考慮して検討する必要があります。
（ファイルを年毎に更新していくとか）
バックアップ用にデータだけは他に保存しておくと尚のこと良いでしょう。

二度と開けないとは、開いたままに出来ないという意味です。

だれも（言いたくても）言い出さなかった業務改善です。
どのような方法を参考にするにせよ、
この際、リーダーシップを執って無事にやりたかったことを成し遂げてください。

お礼

ご丁寧な回答ありがとうございました。
こちらこそ遅くなり申し訳ありません。

マクロで表示させるしかないのですね・・・＾＾；；

であれば、共有にせずに、ファイルにマクロをつける方向で
いこうかなと思っています。

または、履歴を何とか表示させることが出来ないか、
もう少し試してみようかと。
(値で貼りなおして保存とかダメかしら。挑戦します）

激励のメッセージもありがとうございました。
みんな不便だとは思っていても、時間をかければ出来ないことはないし・・・
という感じでダラダラやってきたみたいです。

頑張ります。ありがとうございました。

回答No.9

GOCHISOUda

ごめんさい

訂正するところ
MsgBox MyName

どうするか
削除・消して・無くして（わたしは途中の人生もしたいＷ）

訂正するところ
Macro4

どうするか
Macro1

回答No.7

GOCHISOUda

遅くなりすいませんでした。補足があったとは存じませんでした。

＞・・・が、別シートに履歴を残す、にしたいのですが、保存すると消えてしまいます。
これが残るように出来ないものでしょうか・・・。
結論からいいますと
どっちつかずです。
出来ないの意味
変更履歴の履歴シートはファイルを保存する際に消えてしまいます。何故だかは知る由もなく、ただ、表示しないだけで記録自体は残っていますので都度見たいときに出すことになります。

出来るの意味
シートを残せはしませんが、（準備が面倒ですが）開くと同時に表示させる方法をご案内します。（長いですよＷ）

全体の流れ
マクロを使います。
ただし、共有ファイルはマクロを使えませんので、別のファイルから呼び出します。
呼び出しファイルは即時に閉じてしまいます。
尚、呼び出しファイルは共有にしてはいけません！！！（共有ファイルはマクロを使えません）
また、マクロのセキュリティを『中』か『低』にする必要がありますので、ＰＣのシステム管理担当者と相談してください。

ここで終わりにしたいですが、乗りかかった舟ですので

やり方
まず共有ファイルを一旦閉じておきます。
新規ファイルを作成し、ファイル名は（仮に）「住所録呼び出し」とでもしておきましょう。
（一度名前を付けて保存してください）
「住所録呼び出し」ファイルで”マクロの記録”をスタートさせます。（やり方は調べてください）
そのまま、共有ファイルを開いて
開いたらＡＬＴキー＋Ｔキー
Ｔキー
Ｈキー
”新しいシートに変更箇所一覧を作成”にチェックして[ＯＫ]をクリック
最後に”記録終了”をクリック

ＶＢＥを開いて（やり方は調べてください）
ThisWorkbookをクリック
Genneral　となっているところをWorkbookを選んで
（その右はOpen）
Private Sub Workbook_Open()とEnd　Sub
の間が１行空いていると思います。そこに
MyPath = ActiveWorkbook.Path
MyName = ActiveWorkbook.Name
ChDrive MYPATH
ChDir MyPath
MsgBox MyName
Call Module1.Macro4
Workbooks(MyName).Close
と入力

（確認!）
Private Sub Workbook_Open()
MyPath = ActiveWorkbook.Path
MyName = ActiveWorkbook.Name
ChDrive MYPATH
ChDir MyPath
MsgBox MyName
Call Module1.Macro4
Workbooks(MyName).Close
End Sub

となりましたか？
「住所録呼び出し」ファイルを上書き保存して閉じます。
共有ファイルも閉じます。
先ほどの「住所録呼び出し」ファイルを再び開くと
共有ファイルの履歴が現れると思います。

（※注意！）
「住所録呼び出し」ファイルは開いたと思ったら即、閉じてしまいます。
二度と開けないのではと思われますが、マクロのセキュリティを”高”にすると停止できます。

おまけ
オートフィルタと数値化の区別ですが、
わたしの構想では
フィルターは分類分け（見たい項目として）に使い、数値化は進捗状況の結果として使うというものです。
例：フィルタの並びでは新年・お中元になっているのに数値は１。
５になっていないならお中元がまだなのか（お中元スミの）入力がまだなのかと判断する。という使い方です。

回答No.6

layy

お中元、年賀状、
これから実際行うときに整理しながら保存する。

これをマスターデータとして、
来年同じことを行うときに、比較したら良いと思います。

随時最新にする
か
必要なときに比較して最新にする
か
どちらかでしょうが、
構想、仕組みができるまではとりあえず後者で。

エクセルでデータを比較するのはなんとかなります。

補足

回答ありがとうございます。

私の理解能力が足りなくて申し訳ないのですが、
教えていただいた方法が分かりません…。本当にすみません。

ただ、全てのフォーマットは並びが全く異なっており、
去年の年賀状と今年の年賀状のデータは比較できますが、
その間にあったお中元のデータや年賀状のデータは比較が困難
（出来なくはないですがちょっと手間）
なのです・・・。

お中元で直したのに、年賀状では古いからまた直さないといけない、とかが
ちょっと厄介だなあと思っておりました。

回答No.5

mu2011

NO1です。
>利用するPC分購入しなくてはならず、30人以上いる部署なので
>全員にそのソフトを導入する必要が発生するため。
　⇒小規模の部門と想定していましたので、わずらわしい機能をエクセルに組み込むより市販のソフト利用した方が良いと思いました。

　私見です。
　この情報は機密情報に値するものではないでしょうか。
　ならば、部署の全員が更新できること自体、情報セキュリティ上に問題がありませんか。
　例えば、情報更新発生⇒メール等により管理者に報告⇒確認後、担当者で更新のようなルールだとしたら必要ソフトも全員分必要ではなく担当者のみで済みます。
　ご質問者がエクセルでテンプレートや場合によっては関数、マクロ等を考えるよりはるかにコストが少ないのではないでしょうか。
　因みに情報開示する場合、この様なソフトにはＣＳＶ編集機能が備わって下りますのでエクセルで参照が可能になります。

補足

回答ありがとうございます。

部員全員が持ち寄った住所録なので、機密情報という認識はあまり持っておりませんでした。
各自が確認するのに、データを見てもらったほうが早いと思っておりましたし、
確かに総括管理は4人ほどなので、ソフトもありかも知れないですね。

データのかきだし等が出来れば有料ソフトでも問題ないのですが、
担当が替わるたびにソフトの導入申請～等
（許可からインストールまで一週間ほどかかる）を考えると、
既存のソフト（エクセル等）を使えるのが一番いいのかなと思っておりました。

ソフトを入れる場合は、私の一存では何も出来ないので、上長などにも再度相談してみます・・・。

ありがとうございました。

回答No.4

kagakusuki

一元化する方法の一案です。
　今仮に、元のリストが存在するシートがSheet2だとして、Sheet1のA1セルに「年賀状」と入力すると、その下に年賀状を出す相手先のみのデータが表示される様にする方法です。
　尚、添付画像の文字が潰れて読めないため、リストの右端の列が、仮にL列だとして話を進めさせて頂きます。（もし違っていたら適時数式等を変更して下さい）

　まず、Sheet2において、M列～R列は空いていますから、M列～R列の1行目に「年賀状」、「お中元」、「行事1」、「行事2」、「行事3」、「行事4」、という具合に住所を確認する必要のある行事の名称を入力しておきます。
　そして、年賀状を出す相手先が入力されている行には、「年賀状」と入力されている列に「○」を入力しておくという具合に、各行事毎の相手先の行に「○」を入力しておきます。
　その際には、次の様な操作を行って、入力規則のドロップダウンリストで「○」と「-」のみが入力可能になる様にしておいても良いかも知れません。

M2セルを選択
　　↓
メニューの[データ]をクリック
　　↓
現れた選択肢の中にある[入力規則]をクリック
　　↓
現れた「データの入力規則」ウィンドウの[設定]タグをクリック
　　↓
「入力値の種類」欄をクリック
　　↓
現れた選択肢の中にある[リスト]をクリック
　　↓
「元の値」欄に

○,-

と入力
　　↓
[OK]ボタンをクリック
　　↓
M2セルをコピーして、N2～R2の範囲に貼り付け
　　↓
M2～R2の範囲をコピーして、同じ列の3行目以下に貼り付け

　これで入力規則の設定が出来ます。

　次に、Sheet3を作業用Sheetとして用意し、A1セルに

=Sheet2!M1

と入力して下さい。
　次に、Sheet3のA2セルに次の数式を入力して下さい。

=IF(INDEX(Sheet2!M:M,ROW(A2))="○",COUNTIF(OFFSET(Sheet2!M$1,,,ROW(A2)),"○"),"")

　続いて、Sheet3のA1～A2の範囲をコピーして、同じ行のB列～F列の範囲に貼り付けて下さい。
　そして、Sheet3のA2～F2の範囲をコピーして、同じ列の3行目以下に貼り付けて下さい。

　次に、Sheet1のA3セルに

=Sheet3!A1

と入力して下さい。
　次にSheet1のA4セルに次の数式を入力して下さい。

=IF($A$1="","",IF(ROWS($1:1)>MAX(OFFSET(Sheet3!$A:$A,,MATCH($A$1,Sheet3!$A$1:$F$1,0)-1)),"",INDEX(Sheet2!A:A,MATCH(ROWS($1:1),OFFSET(Sheet3!$A:$A,,MATCH($A$1,Sheet3!$A$1:$F$1,0)-1)))))

　そして、Sheet1のA3～A4の範囲をコピーして、同じ行のB列～L列の範囲に貼り付けて下さい。
　続いて、A4～L4の範囲をコピーして、同じ列の5行目以下に貼り付けて下さい。
　次に、Sheet1のA1セルを選択してから、以下の操作を行って下さい。
メニューの[データ]をクリック
　　↓
現れた選択肢の中にある[入力規則]をクリック
　　↓
現れた「データの入力規則」ウィンドウの[設定]タグをクリック
　　↓
「入力値の種類」欄をクリック
　　↓
現れた選択肢の中にある[リスト]をクリック
　　↓
「元の値」欄に次の数式を入力
=INDIRECT("Sheet2!M1:R1)

　　↓
[OK]ボタンをクリック

　最後に、以下の操作を行って、空欄が0と表示される事がない様にします。

Sheet1を開く
　　↓
メニューの[ツール]をクリック
　　↓
現れた選択肢の中にある[オプション]をクリック
　　↓
現れた「オプション」ウィンドウの[表示]タグをクリック
　　↓
「ウィンドウ オプション」欄の「ゼロ値」と記されている箇所のチェックを外す
　　↓
[OK]ボタンをクリック

以上で準備は完了です。
　後は、Sheet1のA1セルに行事の名称を入れると、その行事の際に関係する、相手先のリストが表示されます。

　尚、Sheet2のリストの編集で、1行目以外の行におけるセルの削除・切り取り・貼り付けを行っても、行が変化するだけであれば、上記の数式を使用している箇所の動作に影響はありません。（列がズレる場合には、エラーが発生する事があります）

お礼

回答ありがとうございます。

数式でもフィルタと同様のことが出来るのですね(゜o゜)
これはすごい！
非常に勉強になりました。

ただ、列が今後増えないかどうかわからないため、
高尚な関数を使ってしまうと、修正が出来ない恐れがありまして・・・。
（SUM関数くらいしか使えない人がメインで触るので）

でも、勉強になるアドバイス、本当にありがとうございました。

回答No.3

GOCHISOUda

一元化したいとのことなので、単純に一覧表にオートフィルタをかければよいかと思います。
年始・中元などそれぞれの列を設けて数値なり文字でフィルタリングします。

１）と２）は答えが決まっているようです。
３）は例えば数値化するのはどうでしょう。１・２・４・８・１６・３２（倍々になっています）の組み合わせを利用します。
１＝年賀状、２＝お中元、４＝残暑見舞い、８＝弔い（1回目）・・・などなど
そして、何を送ったかで足し算します。年賀状と弔いだけ得るかなら１＋８＝９になるので記録するセルには９を入れます。見直す時は逆にどうしたら９になるか考えると答えはでます。チョッと面倒ですが、馴れれば分かります。送ったか否かだけならこの方法だとファイルの大きさは抑えられるかもしれません。まあ、年毎のデータが増えるのは否めません。
先に述べたオートフィルタはあくまでも今見ている表は、何の表なのかで、数値化は何をしたかの結果です。同じと扱うかどうかはご自身で決めてください。

あと細かくしたいなら個人ファイルをひとつひとつ作る。このとき、一覧表をクリックすると個人ファイルやシートに飛ぶようにします。飛び先が大きかったり、集計するファイルが多いと読み込みに時間がかかるのでデータ数とかと見合うやり方を選んだほうがいいです。
４）は[ツール]の[変更履歴の記録]を利用します。

補足

数値化という考えに目からウロコでした！！

オートフィルタの方がすぐに見えるので、フィルタを使おうと思っています。

また、ツールの変更履歴の記録、思いつきませんでした。
この機能は非常に便利だと思います！！
・・・が、別シートに履歴を残す、にしたいのですが、保存すると消えてしまいます。
これが残るように出来ないものでしょうか・・・。

回答No.2

hayasi456

3）何を送ったか、という記録を残したい。（2009年お中元、年賀状…など記録は増える）

２０１１年年賀　２０１０年お歳暮　２０１０年お中元

最新の記録が右側に来るようにと列を挿入してはどうでしょう。

4）更新した記録を残したい（誰がいつ修正したかなど）
本体はパスワードで保護し担当者のみ修正できるようにして
他の方々には修正依頼書を提出してもらい
同じフォルダ内に修正依頼書を保存するフォルダを作ってはどうでしょう。

添付された画像では確認できないので
会社名、人名などの読み（ふりがな）も入力しておいた方が良いでしょう。
sweetness さんの会社の担当部署、担当者の項目もあった方が多目的に使えるでしょう。

年賀状作成ソフトを顧客管理に使っている個人店がありました。
アクセスのように自由にレイアウトは出来ないようですが簡単で結構使えるとようです。
確かエクセルの住所録からエンコード出来たと思います。
http://fudemame.net/products/hagaki/fude21/
http://www.fudeoh.com/products/

お礼

回答ありがとうございます。

>２０１１年年賀　２０１０年お歳暮　２０１０年お中元
>最新の記録が右側に来るようにと列を挿入してはどうでしょう。

私もそう思っておりましたので、ちょっと自信が持てました。
これで記録を残したいと思います。

画像つぶれてしまっているようで、申し訳ありません。
担当部署の項目は作っており、フラグを立てようかと考えておりました。

ありがとうございます。＾＾

回答No.1

mu2011

一般的な年賀状ソフトで要件は充足していると思いますが如何でしょうか。
ソフト比較ＵＲＬを添付していますのでご確認下さい。

参考URL：

http://d.hatena.ne.jp/softnenga/

補足

回答ありがとうございます。

私の言い方が悪かったのですが、
フリーソフトも禁止ですが、有料でもソフトはNGです。
利用するPC分購入しなくてはならず、30人以上いる部署なので
全員にそのソフトを導入する必要が発生するため。

ありがとうございます。