excelで顧客の管理ファイルを作っています。
A~Dの2、3列、4、5列は各自上下で結合しており、
E,Fは上下分かれていて、上の段は、実際の入金。
下の段は、注文時に約束していた入金の予定となってます。
「山田さん」であれば、5万ずつ2回入金がある予定だが、2回目の入金がまだとわかる感じです。
A B C D E F G
1 日付 顧客 担当 品名 売上 入金1 入金2
----------------------------------------------------------
2 10/1 山田 佐々木 ○○ 100000 50000
---------------
3 上セルと結合 50000 50000
----------------------------------------------------------
4 10/3 高橋 伊藤 △△ 200000 150000
---------------
5 上セルと結合 100000 100000
------------------------------------------------------------
上記のようなデータが100件以上あり、これを別ブックの担当者別のシートに振り分けたいのですが、
オートフィルタをかけるとF,Gの上下に分かれている部分の下列が隠れてしまうので、
オートフィルタ→コピー→ペーストではうまくいきません。
以前、関数で担当別に分けるシートを作って利用していたのですが、
一時期担当別の管理をしていなかったのでファイルを削除してしまい、
どうやって作ったのかさえ解らなくなってしまいました。
おそらくVBAを使って作るのが一番なのでしょうが、
VBAは殆ど解りません。
関数でのやり方があれば助かります。
宜しくお願いいたします。
No.3ベストアンサー
- 回答日時:
せっかく補足の説明を頂いたのでまた書きます。
乗りかかった船ですね・・・。まず、簡単な解決策が浮かびました。難しく考えるからいけないので、要は、コピーする対象の行は「全て」担当者の名前を入れておけばよいのです。上の例だと、「佐々木」と言う担当者の名前が表示され、その後空白があり、更にその下に「上セルと結合」と書いてありますよね。その空白の行とか「上セル・・・」の行まで要は「佐々木」でカバーすべきところは全て「佐々木」と入れておくのです。
その上でオートフィルターを使うと、当然「佐々木」と選んだ場合は、10月 1日の顧客「山田」の行とその下の 2行が全て表示されるので、それをこぴぺします。
担当者の名前を何度もコピーするのは面倒ですが、関数を使い自動計算させる場合でも、例えば、「佐々木」と言うデータを探し、それに該当する行の別の列(例えば「売上」)を取って来なさい、と言う関数だと、「その行に『佐々木』と言うデータが入っていなければいけない」と言う事になり、矢張り行ごとに「佐々木」と入れる必要があると思います。
以上のように考えると、同じひとつの売上、支払い金額、支払い残高をみるのに「3行も必要かな?」と言う疑問が沸きます。こぴぺするにせよ、自動計算させるにせよ、1行にまとめた方がその後の処理が楽なのでは?下記を参照下さい。
日付 顧客 担当 品名 売上 入金1 入金2 入金3...残高・・OK
----------------------------------------------------------
10/1 山田 佐々木 ○○ 100000 50000 50000.... 0 済み
10/3 高橋 伊藤 △△ 200000 150000 50000
この場合、入金 X は、最高何回払いで代金を回収出来るかにより決めます。例えば、最高 6回払いまでしか対応しないのであれば「入金 1」から「入金 6」まで列を作ります。
「残高」は、「売上」から「入金 1」から「入金 6」までの総和を引いたものを数式として入れます。
そして、「OK」の列には、「残高」の列がゼロの場合に「済み」と出るように関数を入れます(IF 関数で簡単に出来ます)。
こうすれば、担当者により、オートフィルターで担当者名を選び、そのままコピー&ペーストし、各担当者に送る事が出来るでしょう。
以上で如何でしょうか。
何度もすみません。
2行使っている訳は、例には入力し忘れていましたが、入金、入金予定には日付が入ります。
下の段(入金予定)に10/1 \50000 とあった時、10/1を過ぎても上の段(実際の入金)に
入力がなければ、このファイルを見ているAさんが、担当に確認をとるためです。
残高や、支払い回数を見るだけなら、1行でも良かったのですが、
入金の予定と、実際の入金の遅れをチェックし、担当を促すために
一目でわかり、担当毎にわかれているファイルが欲しいとの要望だったため、
以前から使っていたフォーマットですが、このような形にしました。
すぐにでも欲しいようだったので、とりあえずAファイルの担当が「佐々木」だったら、
Bファイルの同一行、列に同じデータを貼り、「佐々木」でなかったら空白という関数で作り、
空白行を消しました。。
担当別に分けたデータも、納品が終ったものを消していき、納品していないものだけ残すことになり、、
オートフィルター&コピペだと、消したデータをまた貼り付けることになってしまうので、
関数にしました。
このような質問に付き合っていただき、また丁寧に説明していだき、本当にありがとうございました。
excelもVBAもアクセスも、少し・・・勉強しなきゃ!!
No.6
- 回答日時:
自分だったらどうするかなーって
少ない知恵で考えてみました。
やっぱりデータを扱うのは1行が便利だと思ったので
1行でどうやってわかりやすく表示できるかに
焦点を絞ってみました。
幼稚な表ですが、何かのヒントになれば幸いです。
せっかく回答いただいていたのに、すみません。
こういった感じのものがシンプルでいいと思うんです!
・・・でも実際に表を見るのは私ではないので(T_T)
とりあえず、担当ごとのシートをつくり、元ファイルから自動的にペーストするようにしました。
ありがとうございました。
No.5
- 回答日時:
こんばんわ。
内容をながめていたのですが、やはりネックはセルの結合に
あると思います。今までのデータ資源はそのまま?(なおせるなら
よりベターですが。。/Aブックだけでも)に、新規受注より
1行目は従来通りにして2行目A~Dを各=A1,=B1,=C1,=D1(数値は前の
行を指します)としておき、フォントの色を白色にして見えなく
しておいてはどうでしょうか?そうすればフィルタをかけても
偶数行目が隠れるということはなくなり、コピペ出来ると思うのですが。
多分結合セルも毎回前のものをコピペしているか、都度作成しているの
だと思いますので、この2行の形もA~Dの2行のみコピーすれば
作業性も従来とほぼ変わらないかと思うのですがいかがでしょうか?
又、エクセル2007(もしかするとその前のバージョンでも)は
アクセスソフトが無くても、エクセルをデータベースとして活用し
担当名を抽出条件にしして、受注日昇順でシートへ表示する方法も
あります。これはデータの更新ボタンを押すことにより、シートに追記
したものを適宜差し替え出来るなkなか使える便利な機能です。
そのためには従来データもやはり結合解除が必要になりますが。。。
回答ありがとうございます。
結合って便利なんだけど、不便ですよね(T_T)
2行目の色を白にするのは、以前やっていました。
今のAファイルも、担当のところだけ、何故かまだそうなってました(^_^;)
以前は、教えていただいたやり方で、コピペして不要な部分を非表示にして、
プリントアウトし、自分で担当に入金の遅れの報告をしていましたが、
今回は、私ではなく、別の人間がチェック&担当報告をするため、
(オートフィルタ&コピー&ペイント&不要列の非表示を数人分繰り返す)作業を
少しでも簡単にできないかとやり方を探していました。
担当別に分けたデータも、納品が終ったものを消していき、納品していないものだけ残すことになり、、
オートフィルター&コピペだと、消したデータをまた貼り付けることになってしまうので、
関数でとりあえず作成してみました。
空白行がでてしまうので、それを削除しなくてはいけませんが、
とりあえず、これで何とかしてみます。
ちなみに、私が使っているのはexcel2003ですが、その機能探して見ます。
あとは勉強です!!
ありがとうございましたm(__)m
No.4
- 回答日時:
済みません。
どんなイメージになるか、と言う画像をアップしたのですが、間違えていたのでやり直しです。尚、この場合は「入金 1」から「入金 3」までしかないケースです。また、小生 Excel ではなく、Open Office のカルクを使っているのでほんのちょっと見栄えが違うかも知れません(完全に互換性はあるのですが)。
No.2
- 回答日時:
ひとつコメントするのを失念していました。
この場合、大きな目的は代金回収管理と言う事だと思いますが、そうであれば、Excel を使う代わりに MS Access (ご存知でしょうが、リレーショナルデータベースソフトです)を使った方が簡単なのではないか、とふと思いました。
データベースと言うと難しく考えがちですが、Access を買うと、架空の会社の売り上げ管理のサンプルとか付いて来るので、それと同じようにデータベースを構築すれば良い訳です。勿論 Excel に比べ導入に手間隙が掛かりますが、一度導入(データベースを構築)してしまえば、ずっと作業が楽になる筈です(大量のデータを複数のテーブルに納め、それらの中で処理する事に適しています)。
この回答への補足
Accessは以前の会社で使ったことがあります。
すごく簡単に作られていたものだったのだと思いますが、
その時は、ただのデータの入れ物って感じで、見ずらいなって感じでした。
でもきっと、データベースを構築して、色々なことができるのでしょうね。
今の会社でも、本があった気がしますので、少し見てみます。
excelで悩んでしまう私には難しそう(>_<)
No.1
- 回答日時:
済みません。
ご質問の趣旨が今一はっきり判らないので(このような場合、実際に Excel の画面を見ながら質問・回答した方がずっと楽ですよね)、一般的なアドバイスと言う事で読み流して頂ければと思います。先ず、excel でしかも関数を使えば自動的に各種の計算が出来る、と言うメリットがありますが、どこで自動化を行いたいかはっきりさせる必要があると考えます。例えば、各担当が自分のデータファイルをアップデートし、サーバーにアップすると、自動的に一覧表のファイル内容が(開いた時に)アップデートされるとか。
後、担当者別の「シート」に分けるとありますが、その場合だと、同じひとつのファイルと言う事でしょうか。それも可能ですが、間違えて他の担当のシートの数字をいじってしまう、など弊害もあると思います。また、ある担当がデータを変更している間は他の担当者は自分のシートを変更出来ない、と言うデメリットもあります。小生であれば担当者毎にひとつの(同じフォーマットの)ファイルを作りますが。
また、「100件以上」と仰いますが、担当者の数が 100人以上と言う事でしょうか。そうであれば、矢張り 100枚のシートが必要となり、相当大きなファイルになりませんか。
担当者が数名、客先が 100件である、と言う場合、兎に角担当毎のシートに振り分けられれば良いと言うのであれば、全てを選択→コピー→ペーストする先のシートに移動→ペースト→その後不要な行を選択し、削除、と言う手順で出来ますが、当然この場合は関数など使われません。
関数を使う場合、データを探す、そのデータを基に何らかの処理を行う(例えば平均を出すとか、或いは日付けを文字列に変換するとか)、その結果を表示する、など関数にも色々あるので、何をされたいかがはっきりしないとちょっと回答出来ません。
また、「一覧表」のデータをアップデートし、その結果を担当に知らせる為のものか、逆に各担当がデータをアップデートし、その結果が「一覧表」に反映されるのか、それに依っても関数や数式の構成が異なるでしょう。
何れにせよ、このファイルの目的は何か、どのような手順で内容に変更がなされるか、結果はどのように表示されなければならないか、などの基本的な点をまず検討される事をお勧めします。
回答になっていないかも知れませんがご参考になればと考えます。
この回答への補足
担当者(10名)、私、Aさんがいて、
私が担当者から受け取った注文書をAファイルの一覧表に入力し、
AさんがBファイルの担当者毎に分けられたシートで入金のチェックをする。担当者は一覧表or担当毎で、自分の担当の状況を把握する。
という感じで使いたいのですが・・・。
フォーマットはA、Bファイルどちらも同じです。
(A)一覧表は月毎のシートを作成し、(B)担当別は月関係なく自分のデータが蓄積される。
担当者は見るだけであって、編集はしません。
コピペだと、3,4行目と9、10行目・・・など飛び飛びになるため
ちょっと面倒で自動で転記できないかと・・・。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) Excel VBA 最終行を取得しVlookup関数をコピーする方法をコーディングで教えてください。 3 2023/05/11 13:14
- Visual Basic(VBA) エクセルのマクロで対象ごとにシート分けしてその内容をセルに書き込みたい 9 2022/08/24 13:23
- その他(データベース) Excel VBA 転記について 1 2022/04/20 16:55
- Excel(エクセル) エクセルの条件付き書式 個人シートを参照して集計シートに色付けしたい 1 2023/06/22 00:39
- Visual Basic(VBA) VBA エクセル 条件の設定 1 2022/03/28 10:24
- Excel(エクセル) 関数EXACT(文字列,文字列)とexcelVBA 3 2022/04/14 15:07
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Visual Basic(VBA) ExcelのVBAを使い、複数シートの同一箇所を、同一条件にて一括でソルバーを回す方法について 1 2022/04/23 11:49
- Visual Basic(VBA) vbaについて 主に以下のような設定をしたいです。 Aブックの表の行数が20未満だったら Bブックの 1 2023/06/08 23:40
- Excel(エクセル) EXCEL マクロで 同じフォルダ内の複数ファイルの複数行全体を選択して1つのファイルに集約 4 2022/09/27 18:41
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルファイルのシート毎の容量
-
複数シートからデータを拾って...
-
Excelでシートの違うデータでグ...
-
excelの不要な行の削除ができな...
-
シート削除して同名シート追加...
-
EXCELで2つのファイルから重複...
-
VBAで CTRL+HOMEの位置へ移動...
-
Excelで日付変更ごとに、自動的...
-
トランジスタの選び方
-
エクセル 縦に長い表の印刷時...
-
EXCEL の表を一行ずつシートに...
-
ファンモータが作動しない。
-
エクセルで名簿を50音で切り分ける
-
EXCEL 複数行のデータを1行にま...
-
Excelマクロ 差分抽出の方法が...
-
(VBAにて)日付でデータを抽出す...
-
エクセルVBAで、特定文字から始...
-
オートフィルタで抽出したデー...
-
Excel 売上管理シートに入力し...
-
実行時エラー’438 の解消
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルファイルのシート毎の容量
-
複数シートからデータを拾って...
-
Excelでシートの違うデータでグ...
-
シート削除して同名シート追加...
-
excelの不要な行の削除ができな...
-
Excelで日付変更ごとに、自動的...
-
VBAで CTRL+HOMEの位置へ移動...
-
トランジスタの選び方
-
EXCELで2つのファイルから重複...
-
EXCEL 複数行のデータを1行にま...
-
他のシートの一番下の行データ...
-
オートフィルタで抽出したデー...
-
エクセルのカメラ機能について
-
(VBAにて)日付でデータを抽出す...
-
エクセルで名簿を50音で切り分ける
-
別々のシートの表をピボットテ...
-
Excel 売上管理シートに入力し...
-
Excelマクロ 差分抽出の方法が...
-
EXCEL の表を一行ずつシートに...
-
エクセルVBAで、特定文字から始...
おすすめ情報