エクセルで一覧表から担当別シートへの転記

質問

excelで顧客の管理ファイルを作っています。
A～Dの２、３列、４、５列は各自上下で結合しており、
E,Fは上下分かれていて、上の段は、実際の入金。
下の段は、注文時に約束していた入金の予定となってます。
「山田さん」であれば、5万ずつ2回入金がある予定だが、2回目の入金がまだとわかる感じです。

　　　　 A　　 B 　　　 C 　　 　 D 　　　E 　　　 F 　　　 G
１　日付　 顧客　担当　　　品名　 　売上　 入金1　入金２
----------------------------------------------------------
２ 　10/1　 山田　佐々木　　○○　100000　　50000　
　　　　　 　　　　 　　　　　 　　　　　　　　　　 　　　 ---------------
３ 　 上セルと結合　　　　 　　　　　　　　　　　　　50000　 50000
----------------------------------------------------------
４ 　　10/3 高橋　伊藤　　　△△　200000　　150000　
　　　　　　　　　　　　　　　　　　　　 　　　　　　　　　 ---------------
５　　　　　　　上セルと結合　　　　　　　　　　　　100000　100000　
------------------------------------------------------------

上記のようなデータが１００件以上あり、これを別ブックの担当者別のシートに振り分けたいのですが、
オートフィルタをかけるとF,Gの上下に分かれている部分の下列が隠れてしまうので、
オートフィルタ→コピー→ペーストではうまくいきません。

以前、関数で担当別に分けるシートを作って利用していたのですが、
一時期担当別の管理をしていなかったのでファイルを削除してしまい、
どうやって作ったのかさえ解らなくなってしまいました。

おそらくＶＢＡを使って作るのが一番なのでしょうが、
ＶＢＡは殆ど解りません。
関数でのやり方があれば助かります。

宜しくお願いいたします。

回答No.3

ryo872 · ベストアンサー

せっかく補足の説明を頂いたのでまた書きます。乗りかかった船ですね・・・。

まず、簡単な解決策が浮かびました。難しく考えるからいけないので、要は、コピーする対象の行は「全て」担当者の名前を入れておけばよいのです。上の例だと、「佐々木」と言う担当者の名前が表示され、その後空白があり、更にその下に「上セルと結合」と書いてありますよね。その空白の行とか「上セル・・・」の行まで要は「佐々木」でカバーすべきところは全て「佐々木」と入れておくのです。

その上でオートフィルターを使うと、当然「佐々木」と選んだ場合は、10月 1日の顧客「山田」の行とその下の 2行が全て表示されるので、それをこぴぺします。

担当者の名前を何度もコピーするのは面倒ですが、関数を使い自動計算させる場合でも、例えば、「佐々木」と言うデータを探し、それに該当する行の別の列（例えば「売上」）を取って来なさい、と言う関数だと、「その行に『佐々木』と言うデータが入っていなければいけない」と言う事になり、矢張り行ごとに「佐々木」と入れる必要があると思います。

以上のように考えると、同じひとつの売上、支払い金額、支払い残高をみるのに「3行も必要かな？」と言う疑問が沸きます。こぴぺするにせよ、自動計算させるにせよ、1行にまとめた方がその後の処理が楽なのでは？下記を参照下さい。

日付 顧客 担当 品名　 　売上　 入金1　入金２ 入金3...残高・・OK
----------------------------------------------------------
10/1 山田 佐々木 ○○　100000　　50000　50000.... 0 済み
10/3 高橋　伊藤　　　△△　200000　　150000 50000

この場合、入金 X は、最高何回払いで代金を回収出来るかにより決めます。例えば、最高 6回払いまでしか対応しないのであれば「入金 1」から「入金 6」まで列を作ります。

「残高」は、「売上」から「入金 1」から「入金 6」までの総和を引いたものを数式として入れます。

そして、「OK」の列には、「残高」の列がゼロの場合に「済み」と出るように関数を入れます（IF 関数で簡単に出来ます）。

こうすれば、担当者により、オートフィルターで担当者名を選び、そのままコピー＆ペーストし、各担当者に送る事が出来るでしょう。

以上で如何でしょうか。

この投稿のマルチメディアは削除されているためご覧いただけません。

お礼

何度もすみません。

２行使っている訳は、例には入力し忘れていましたが、入金、入金予定には日付が入ります。
下の段（入金予定）に10/1 \50000　とあった時、10/1を過ぎても上の段（実際の入金）に
入力がなければ、このファイルを見ているＡさんが、担当に確認をとるためです。

残高や、支払い回数を見るだけなら、1行でも良かったのですが、
入金の予定と、実際の入金の遅れをチェックし、担当を促すために
一目でわかり、担当毎にわかれているファイルが欲しいとの要望だったため、
以前から使っていたフォーマットですが、このような形にしました。

すぐにでも欲しいようだったので、とりあえずＡファイルの担当が「佐々木」だったら、
Ｂファイルの同一行、列に同じデータを貼り、「佐々木」でなかったら空白という関数で作り、
空白行を消しました。。

担当別に分けたデータも、納品が終ったものを消していき、納品していないものだけ残すことになり、、
オートフィルター＆コピペだと、消したデータをまた貼り付けることになってしまうので、
関数にしました。

このような質問に付き合っていただき、また丁寧に説明していだき、本当にありがとうございました。
excelもVBAもアクセスも、少し・・・勉強しなきゃ！！

回答No.6

sekkii

自分だったらどうするかなーって
少ない知恵で考えてみました。
やっぱりデータを扱うのは１行が便利だと思ったので
１行でどうやってわかりやすく表示できるかに
焦点を絞ってみました。
幼稚な表ですが、何かのヒントになれば幸いです。

お礼

せっかく回答いただいていたのに、すみません。
こういった感じのものがシンプルでいいと思うんです！
・・・でも実際に表を見るのは私ではないので(T_T)

とりあえず、担当ごとのシートをつくり、元ファイルから自動的にペーストするようにしました。
ありがとうございました。

回答No.5

tag1701

こんばんわ。
内容をながめていたのですが、やはりネックはセルの結合に
あると思います。今までのデータ資源はそのまま？（なおせるなら
よりベターですが。。／Ａブックだけでも）に、新規受注より
1行目は従来通りにして2行目Ａ～Ｄを各=A1,=B1,=C1,=D1(数値は前の
行を指します）としておき、フォントの色を白色にして見えなく
しておいてはどうでしょうか？そうすればフィルタをかけても
偶数行目が隠れるということはなくなり、コピペ出来ると思うのですが。

多分結合セルも毎回前のものをコピペしているか、都度作成しているの
だと思いますので、この2行の形もＡ～Ｄの2行のみコピーすれば
作業性も従来とほぼ変わらないかと思うのですがいかがでしょうか？

又、エクセル２００７（もしかするとその前のバージョンでも）は
アクセスソフトが無くても、エクセルをデータベースとして活用し
担当名を抽出条件にしして、受注日昇順でシートへ表示する方法も
あります。これはデータの更新ボタンを押すことにより、シートに追記
したものを適宜差し替え出来るなｋなか使える便利な機能です。
そのためには従来データもやはり結合解除が必要になりますが。。。

お礼

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

結合って便利なんだけど、不便ですよね(T_T)
２行目の色を白にするのは、以前やっていました。
今のＡファイルも、担当のところだけ、何故かまだそうなってました(^_^;)

以前は、教えていただいたやり方で、コピペして不要な部分を非表示にして、
プリントアウトし、自分で担当に入金の遅れの報告をしていましたが、
今回は、私ではなく、別の人間がチェック＆担当報告をするため、
（オートフィルタ＆コピー＆ペイント＆不要列の非表示を数人分繰り返す）作業を
少しでも簡単にできないかとやり方を探していました。

担当別に分けたデータも、納品が終ったものを消していき、納品していないものだけ残すことになり、、
オートフィルター＆コピペだと、消したデータをまた貼り付けることになってしまうので、
関数でとりあえず作成してみました。
空白行がでてしまうので、それを削除しなくてはいけませんが、
とりあえず、これで何とかしてみます。

ちなみに、私が使っているのはexcel2003ですが、その機能探して見ます。
あとは勉強です！！
ありがとうございましたm(__)m

回答No.4

ryo872

済みません。どんなイメージになるか、と言う画像をアップしたのですが、間違えていたのでやり直しです。尚、この場合は「入金 1」から「入金 3」までしかないケースです。

また、小生 Excel ではなく、Open Office のカルクを使っているのでほんのちょっと見栄えが違うかも知れません（完全に互換性はあるのですが）。

回答No.2

ryo872

ひとつコメントするのを失念していました。

この場合、大きな目的は代金回収管理と言う事だと思いますが、そうであれば、Excel を使う代わりに MS Access （ご存知でしょうが、リレーショナルデータベースソフトです）を使った方が簡単なのではないか、とふと思いました。

データベースと言うと難しく考えがちですが、Access を買うと、架空の会社の売り上げ管理のサンプルとか付いて来るので、それと同じようにデータベースを構築すれば良い訳です。勿論 Excel に比べ導入に手間隙が掛かりますが、一度導入（データベースを構築）してしまえば、ずっと作業が楽になる筈です（大量のデータを複数のテーブルに納め、それらの中で処理する事に適しています）。

補足

Accessは以前の会社で使ったことがあります。
すごく簡単に作られていたものだったのだと思いますが、
その時は、ただのデータの入れ物って感じで、見ずらいなって感じでした。

でもきっと、データベースを構築して、色々なことができるのでしょうね。
今の会社でも、本があった気がしますので、少し見てみます。
excelで悩んでしまう私には難しそう(>_<)

回答No.1

ryo872

済みません。ご質問の趣旨が今一はっきり判らないので（このような場合、実際に Excel の画面を見ながら質問・回答した方がずっと楽ですよね）、一般的なアドバイスと言う事で読み流して頂ければと思います。

先ず、excel でしかも関数を使えば自動的に各種の計算が出来る、と言うメリットがありますが、どこで自動化を行いたいかはっきりさせる必要があると考えます。例えば、各担当が自分のデータファイルをアップデートし、サーバーにアップすると、自動的に一覧表のファイル内容が（開いた時に）アップデートされるとか。

後、担当者別の「シート」に分けるとありますが、その場合だと、同じひとつのファイルと言う事でしょうか。それも可能ですが、間違えて他の担当のシートの数字をいじってしまう、など弊害もあると思います。また、ある担当がデータを変更している間は他の担当者は自分のシートを変更出来ない、と言うデメリットもあります。小生であれば担当者毎にひとつの（同じフォーマットの）ファイルを作りますが。

また、「100件以上」と仰いますが、担当者の数が 100人以上と言う事でしょうか。そうであれば、矢張り 100枚のシートが必要となり、相当大きなファイルになりませんか。

担当者が数名、客先が 100件である、と言う場合、兎に角担当毎のシートに振り分けられれば良いと言うのであれば、全てを選択→コピー→ペーストする先のシートに移動→ペースト→その後不要な行を選択し、削除、と言う手順で出来ますが、当然この場合は関数など使われません。

関数を使う場合、データを探す、そのデータを基に何らかの処理を行う（例えば平均を出すとか、或いは日付けを文字列に変換するとか）、その結果を表示する、など関数にも色々あるので、何をされたいかがはっきりしないとちょっと回答出来ません。

また、「一覧表」のデータをアップデートし、その結果を担当に知らせる為のものか、逆に各担当がデータをアップデートし、その結果が「一覧表」に反映されるのか、それに依っても関数や数式の構成が異なるでしょう。

何れにせよ、このファイルの目的は何か、どのような手順で内容に変更がなされるか、結果はどのように表示されなければならないか、などの基本的な点をまず検討される事をお勧めします。

回答になっていないかも知れませんがご参考になればと考えます。

補足

担当者（１０名）、私、Aさんがいて、
私が担当者から受け取った注文書をAファイルの一覧表に入力し、
AさんがBファイルの担当者毎に分けられたシートで入金のチェックをする。担当者は一覧表ｏｒ担当毎で、自分の担当の状況を把握する。
という感じで使いたいのですが・・・。

フォーマットはＡ、Ｂファイルどちらも同じです。
（Ａ）一覧表は月毎のシートを作成し、（Ｂ）担当別は月関係なく自分のデータが蓄積される。
担当者は見るだけであって、編集はしません。

コピペだと、３，４行目と９、１０行目・・・など飛び飛びになるため
ちょっと面倒で自動で転記できないかと・・・。