- 締切済み
エクセルVBAを使用し、会社数やフォームに変更があった際にメンテナンスをする方法（初心者）
傘下会社に毎期エクセルでデータを配布し、決算の分析表を入力してもらって回収しています。 〈配布ファイル〉 A会社 実績 計画 計画差 計画差内訳… 売上高 ** 営業利益 ** 営業利益率 ↓ （続く） VBAを使ってこのエクセルファイル達をまとめて一つの表に集計したいと思っています。 各社のデータ（売上高、営業利益率等）を右に横1列に表示していき、 縦に会社を並べる表を、いくつかのシートに分けて最終作っています。 〈最終シート〉 （一枚目）総括表 売上高 営業利益 営業利益率 固定資産 … A社 ** * ** ** B社 C社 ↓ 合計 （二枚目）計画差内訳 人件費計画差 販管費計画差 A社 ** ** B社 C社 ↓ 合計 この際のメンテナンス方法について質問させて下さい。 （１）会社数が増加、減少したときに、VBAで簡単に全てのシートの行を メンテナンスする方法はありますでしょうか。 なお、各会社にコードを付していますので、メンテ後はその コードに従って縦に並べたいです。 （２）各会社に配布するエクセルフォームは同一なのですが、 総括表はその中の必要な数値のみを横に並べるのみの表で、 各社に配布するエクセルフォームとは異なります。 （利益率とかがあるので単純合計は出来ない） 今現在このような形でデータを集計しています。 １、回収したエクセルを貼付用フォームに貼付け ２、予め入力していた式で、必要なデータが横1列にくる様に式で飛ばす ３、２で横1列にしたデータを集計用ファイルに値貼付け ３はVBAでなんとかなるかと思うのですが、 ２の部分のメンテナンスが毎期フォーム変更があると大変で非常に困っています。 すべて手作業で式を入力しており式を入れた後のチェック作業も非常に苦痛です。 次の人にこのファイルを引き継ぐのも、申し訳ないです。 が、式を手入力して横1列に飛ばす以外の方法が恥ずかしながら思いつきません。。 VBA等で簡単になりませんでしょうか。 補足が必要であれば何度でもいたします。
- みんなの回答 （7）
- 専門家の回答
みんなの回答
no1です。第1ステップの作業、つまり、複数のシートに亘って特定の会社名のあるレコードをワンタッチで削除する方法は次のマクロで出来ました。参考にしてください。ここではその会社名はB社、シートは仮に3枚としています。 Sub Macro1() ' ' Macro1 Macro ' マクロ記録日 : 2009/8/31 ユーザー名 : ' Dim myfindrange As Range Set myfindrange = Range("A2 : A10").Find("B社") 'MsgBox myfindrange.Row Rows(myfindrange.Row).Select Selection.Delete Shift:=xlUp ' End Sub Sub Macro2() ' ' Macro2 Macro ' マクロ記録日 : 2009/8/31 ユーザー名 :' ' Dim myfindrange As Range Sheets("Sheet1").Select Call Macro1 Sheets("Sheet2").Select Call Macro1 Sheets("Sheet3").Select Call Macro1 End Sub
- rivoisu
- ベストアンサー率36% (97/264)
正直なところ、手作業と関数でやるのは時間の無駄だと思います。 VBAできっちり解決しておくほうが後々のためです。 あなたが勉強して作るのも結構ですが 数万円で専門家に作ってもらったほうがいいのでは、あなたの時間がもったいない。 (会社の社風によっては予算化しにくいところ丸かもしれませんが）
- cistronezk
- ベストアンサー率38% (120/309)
回答４の添付画像です。
- cistronezk
- ベストアンサー率38% (120/309)
第１ステップの表と第２ステップのウの表が同じに見えますが、両者の関係はどうなっているのでしょう？よくわからないままですが、マクロではなく数式による対応策を示します。 ■第１ステップの表について 確認ですが「売上高」「営業利益」などの列項目は固定で、会社の追加／削除があるわけですね。でしたら、関数を使って会社リストを参照できます。 ＜＜「会社リスト」シート＞＞ A B １ 会社名 住所 ２ A社 ・・・ ３ B社 ・・・ ４ C社 ・・・ ・・・ シート１～シート１０のA2以下（１００社未満ならA１００まで）選択して =OFFSET(会社リスト!$A$2,ROW()-2,0) をCtrl＋Enterで入力します。 A列を選択してセルの表示形式でユーザ定義に「#」を設定して０と表示されないようにします。これで、会社シートに追加／削除があっても会社名がそのまま反映します。 ちなみに、単に =会社リスト!$A2 や =INDEX(会社リスト!$A2:$A100,ROW()-2,1) では、会社リストに変更があると、エラーになってしまうのでこれらの方法は避けます。 ■回収シートからウのシートへの縦横変換 アのシートのどの列のデータをイに「飛ばして」いるのか不明ですが、 「実績」列（B列）のデータを変換しようとしているなら以下のようになります。 =VLOOKUP(B$1,INDIRECT($A2& "!$A$1:$E100"),2,FALSE) 【説明】 ・「A$1:$Z100」は、アのシートのデータの範囲を指定。 ・イのシートの項目名はアの項目名と同じでないといけません。 ・実績がB列（２列めなので、VLOOKUPの第3引数で２を指定）にある場合。 以上で、会社名と売上高などの管理をするだけで、自動的に回収シートからデータを引っ張ってくるはずです。 なお、データの集計などは、Excelの標準機能でかなりのことまでできます。データの形式を検討して、ピボットテーブルなどの使用も検討されるといいかもしれません（私はあまり詳しくありませんが）。
- cistronezk
- ベストアンサー率38% (120/309)
＞１、回収したエクセルを貼付用フォームに貼付け ＞２、予め入力していた式で、必要なデータが横1列にくる様に式で飛ばす ＞２の部分のメンテナンスが毎期フォーム変更があると大変で非常に困っています。 ＞ すべて手作業で式を入力しており式を入れた後のチェック作業も非常に苦痛です。 これだけの情報では、何が問題なのかわかりません。具体的な作業内容と問題点の説明をお願いします。
- hallo-2007
- ベストアンサー率41% (888/2115)
毎日の売上、計画、経費を傘下の会社から連絡してもらうのでしょうか？ 傘下の会社も沢山あるのであれば、基幹システムとしてそれなりのプロに依頼することをお勧めします。 取り合えず、報告されたファイルから、データベースにします。 データシート 日付 会社名 売上 計画 固定資産 人件費・・・ 9/10 Ａ社 9/10 Ｂ社 ・・・ といったように 一枚のシートにデータが自動で貼り付けられるようなＶＢＡを検討します。 （営業利益、利益率、計画差はデータとしては不要でしょう、計算で求められますので） 統括表のシートには 日付 2009/9/1 会社名 売上高 営業利益 営業利益率 固定資産 … と日付を指定すると、その日のデータの一覧表が表示されるようにすれば如何でしょうか？ データシートが必ず日付順に並んでいるのであれば、 統括表のB1セルに表示したいデータの日付を入れたとして、 会社名の列に =IF(ROW(A1)>COUNTIF(データシート!A:A,B$1),"",INDEX(データシート!B:B,MATCH(B$1,データシート!A:A,FALSE)+ROW(A1)-1)) といれて下へコピィすれば、指定した日付のデータ分だけ表示されるでしょう。 関数のHelpを参照していただければ、式の意味が理解できると思います。 わかってしまえは、な～んだ と云った感じの式です。 他の列、別のシートにも応用してください。
お礼
回答頂き、どうもありがとうございました。 一旦全てのデータをデータベースとして保存するという考え方よく分かりました。 （その項目が多くて、どうやってデータベースにしようか、といった感じではありますが、考えてみます。） 式の内容ちょっと確認してみます！
質問事項を絞り、もっと具体的に書いていただけませんか。困っているのはだい2ステップですか。第1ステップの計算内容がぜんぜんわかりません。 要するに、第1ステップでテータがどのように整理されるか。第2ステップでは作業は具体的にどうするのか。困っていることは何か、示してくださいませんか。
補足
分かりにくい質問で失礼しました。 第１ステップ シート1～シート10に、以下の様な表があります。 売上高 営業利益 営業利益率 固定資産 … A社 ** * ** ** B社 C社 ↓ 例えばB社を売却したときに、全10シートでBを削除する、という処理を する必要があります。D社を新規に追加したときも同様です。 会社名をリストで作っておいて、そのリストを修正したら全ての シートで最新の会社データになるような処理をVBAで作れればと思っています。 第２ステップ 今現在、以下の３つのシートで集計しています。 ア 回収シート イ 回収シートのデータのうち、欲しいデータを横1列に飛ばす 貼付け用シート ウ 全体の総括用シート アのシートは以下の様なものです。 A会社 実績 計画 計画差 計画差内訳… 売上高 ** 営業利益 ** 営業利益率 ↓ 次にイのシートは、以下の様な表にアのデータを式で飛ばしています。 売上高 営業利益 営業利益率 売上高計画 … A会社 ** ** ** ** その後横1列に飛ばしたイのシートのデータをウのシートに値貼付けします． 〈ウのシート〉※以下の様なものが１０枚程ある。 （一枚目）総括表 売上高 営業利益 営業利益率 固定資産 … A社 ** * ** ** B社 C社 ↓ 合計 問題点： ・このようにして全部の会社のデータを集計しているので、 会社数が多い場合は貼付けの手間が非常に煩雑です。 この貼付け作業を、自動的にやるマクロを作ろうと思っています。 ・また、イのところで横１列に毎期データを飛ばす式を入れる必要があります。（これはエクセルの式で一つずつ入れています。） ５０項目くらいあるので、変更があった場合のメンテが非常に煩雑です。 この方法以外で、ウのフォームに集計出来るやり方か、 もっと簡単に横１列にデータを集計出来るやり方がお分かりになれば、ご教授頂ければありがたいです。 折角回答頂いたのに、まだ分かりにくかったら、本当にすみません。
補足
分かりにくい質問で失礼しました。 第１ステップ シート1～シート10に、以下の様な表があります。 売上高 営業利益 営業利益率 固定資産 … A社 ** * ** ** B社 C社 ↓ 例えばB社を売却したときに、全10シートでBを削除する、という処理を する必要があります。D社を新規に追加したときも同様です。 会社名をリストで作っておいて、そのリストを修正したら全ての シートで最新の会社データになるような処理をVBAで作れればと思っています。 第２ステップ 今現在、以下の３つのシートで集計しています。 ア 回収シート イ 回収シートのデータのうち、欲しいデータを横1列に飛ばす 貼付け用シート ウ 全体の総括用シート アのシートは以下の様なものです。 A会社 実績 計画 計画差 計画差内訳… 売上高 ** 営業利益 ** 営業利益率 ↓ 次にイのシートは、以下の様な表にアのデータを式で飛ばしています。 売上高 営業利益 営業利益率 売上高計画 … A会社 ** ** ** ** その後横1列に飛ばしたイのシートのデータをウのシートに値貼付けします． 〈ウのシート〉※以下の様なものが１０枚程ある。 （一枚目）総括表 売上高 営業利益 営業利益率 固定資産 … A社 ** * ** ** B社 C社 ↓ 合計 問題点： ・このようにして全部の会社のデータを集計しているので、 会社数が多い場合は貼付けの手間が非常に煩雑です。 この貼付け作業を、自動的にやるマクロを作ろうと思っています。 ・また、イのところで横１列に毎期データを飛ばす式を入れる必要があります。（これはエクセルの式で一つずつ入れています。） ５０項目くらいあるので、変更があった場合のメンテが非常に煩雑です。 この方法以外で、ウのフォームに集計出来るやり方か、 もっと簡単に横１列にデータを集計出来るやり方がお分かりになれば、ご教授頂ければありがたいです。 折角回答頂いたのに、まだ分かりにくかったら、本当にすみません。