エクセルで管理シートを作成したいのですが。

質問

エクセルの２００７を使って、会社の営業成績を管理するためのシートを作成したいのですが、どのようにすればよいか、教えていただけないでしょうか宜しくお願いいたします。
　まず、全国に点在する各支社の全ての担当物件リストがベースとなるシートがあります。これは、毎日入力され、日付順に入力されます。その中で受注できた物件には落札完了されたことと受注価格が毎週入力されます。このシートを元に、各支店毎のシートが作られますが、基本的には親シートを支社毎に分けるだけで、全てのデータは、親シートに入力すると全て、子になるシートに反映されるようにします。最終的に全てのデータの集計が表とグラフとしてまとめられるようにしたいのです。　　
　ということで、上記の管理シートを効率よく作成するためにはどのようにすればよいでしょうか。

回答No.4

kagakusuki · ベストアンサー

ANo.3の続きです。

　次に、以下の操作を行って下さい。

雛型シートのC3セルを右クリック
　　↓
現れた選択肢の中にある[セルの書式設定]をクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[表示形式]タブをクリック
　　↓
現れた「分類」欄の中にある[ユーザー定義]をクリック
　　↓
現れた「種類」欄に

0"件"

と入力
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
雛型シートのC3セルを右クリック
　　↓
現れた選択肢の中にある[コピー]をクリック
　　↓
雛型シートのC5セルを右クリック
　　↓
現れた選択肢の中にある[書式設定]（「％」と筆のイラストが描かれているボードのアイコン）をクリック

次に、以下の操作を行って下さい。

雛型シートのE11セルを右クリック
　　↓
現れた選択肢の中にある[コピー]をクリック
　　↓
雛型シートのC4セルを右クリック
　　↓
現れた選択肢の中にある[書式設定]（「％」と筆のイラストが描かれているボードのアイコン）をクリック
　　↓
雛型シートのC6セルを右クリック
　　↓
現れた選択肢の中にある[書式設定]（「％」と筆のイラストが描かれているボードのアイコン）をクリック

　次に、以下の操作を行って下さい。

雛型シートのC7～C8の範囲をまとめて範囲選択
　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせてから、マウスを右クリック
　　↓
現れた選択肢の中にある[セルの書式設定]をクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[表示形式]タブをクリック
　　↓
現れた「分類」欄の中にある[パーセンテージ]をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック

　次に、雛型シートのC3セルに次の数式を入力して下さい。

=IF(COUNT($A:$A),COUNTIF(INDEX($C:$C,ROW($A$10)+1):INDEX($C:$C,MATCH(9E+99,$A:$A)),"*?"),0)

　次に、雛型シートのC4セルに次の数式を入力して下さい。

=IF(COUNT($A:$A),SUM(INDEX($E:$E,ROW($A$10)+1):INDEX($E:$E,MATCH(9E+99,$A:$A))),0)

　次に、雛型シートのC5セルに次の数式を入力して下さい。

=IF(COUNT($A:$A),COUNTIF(INDEX($D:$D,ROW($A$10)+1):INDEX($D:$D,MATCH(9E+99,$A:$A)),"落札"),0)

　次に、雛型シートのC6セルに次の数式を入力して下さい。

=IF(COUNT($A:$A),SUMIF(INDEX($D:$D,ROW($A$10)+1):INDEX($D:$D,MATCH(9E+99,$A:$A)),"落札",INDEX($E:$E,ROW($A$10)+1):INDEX($E:$E,MATCH(9E+99,$A:$A))),0)

　次に、雛型シートのC7セルに次の数式を入力して下さい。

=IF($C$5>0,$C$5/$C$3,"")

　次に、雛型シートのC8セルに次の数式を入力して下さい。

=IF($C$4>0,$C$6/$C$4,"")

　次に、雛型シートのコピーシートを支店の数だけ複製して下さい。
　そして、各コピーシートのシート名を、作業シートのB列に入力した略称と同じ名称に、それぞれ変更して下さい。

　これで、各支店のシートのB1セルに、シート名に相当する支店の正式名称が自動的に表示され、
入力シートに入力した各支店のデータが、各支店のシートに、その支店のデータのみが抽出されて、11行目以下に表示され、
C3セルには、その支店が入札に参加した件数が表示され、
C4セルには、その支店が入札に参加した物件における受注金額の総計が表示され、
C5セルには、その支店が落札した件数が表示され、
C6セルには、その支店が落札した物件における受注金額の総計が表示され、
C7セルには、その支店の「『落札した件数』÷『入札に参加した件数』」の値が表示され、
C8セルには、その支店の「『落札した物件における受注金額の総計』÷『入札に参加した物件における受注金額の総計』」の値が表示されます。

　尚、

>最終的に全てのデータの集計が表とグラフとしてまとめられるようにしたいのです。

と仰いますが、Excelにどの様な形で集計を行わせたいのか、どの様な形式のグラフにされたいのか、どの様な形にまとめられたいのか、という事が一切述べられていないのでは回答のしようが御座いません。

お礼

入力文字数限界を超えた長い回答をいただき本当に有り難く感謝です。

これだけの文字数を分かりやすく、系統立てて入力する労力は並大抵なことでは無いですよね。

正直、自分だったらここまで出来ないだろうなと思うと、回答者様の器の大きさに頭が下がります。

しかしながら、私はもっと簡単にできると考えていたのですが、このように実際のプロセスを拝見するととんでもなく大変なことをしようとしていたのだということで、他の回答者様からも指摘がありましたが、我ながら、とんでもねーなー、と反省しております。

ですが、折角教えていただいたので是非これを精査し、無駄にしないように頑張りたいと思います。

まずは、回答をワードに貼り付けてプリントアウトしてから作業を開始さえていただきます。

お礼が遅れてしまい大変失礼いたしました。

回答No.3

kagakusuki

様々なやり方があり、以下はその一例に過ぎない事を最初にお断りしておきます。

　今仮に、「全国に点在する各支社の全ての担当物件リストがベースとなるシート」が、

入力

というシート名のシートであり、「各支店毎のシート」のシート名は各支店の略称となっているものとします。
　又、入力シートでは、A3セルに「No.」、B3セルに「日付」、C3セルに「担当物件」、D3セルに「担当店」、E3セルに「受注」、F3セルに「受注金額」、等々の各項目名が3行目に入力されていて、各項目毎の実際のデータは、4行目以下に入力されていて、受注できた物件の場合には、E3セルに「落札」と入力されるものとします。

　まず、入力シートの他に、「作業」というシート名（鉤括弧は不要）のシートと、「雛型」というシート名のシートと、「集計」というシート名のシートを作成して下さい。

　次に、作業シートのA1セルに「担当店名」、作業シートのA2セルに「正式名称」、作業シートのB2セルに「略称」と入力して下さい。
　次に、作業シートのA2以下に「○○市JR▲▲駅前支店」といった具合に、各支店毎の正式名称を入力して下さい。
　次に、作業シートのA2以下に「▲▲店」といった具合に、A列に入力した支店の略称を入力して下さい。（A列とB列が同じ名称となっていても構いません）

　次に、以下の操作を行って下さい。

入力シートのD4セル（「担当店」欄の1番上のセル）を選択
　　↓
[データ]タブをクリック
　　↓
「データツール」グループの[データの入力規則]ボタンをクリック
　　↓
現れた選択肢の中にある[データの入力規則]をクリック
　　↓
現れた「データの入力規則」ダイアログボックスの[設定]タブをクリック
　　↓
「入力値の種類」欄をクリック
　　↓
現れた選択肢の中にある[リスト]をクリック
　　↓
「元の値」欄に次の数式を入力

=OFFSET(作業用!$B$2,1,,MATCH("*?",作業用!$B:$B,-1)-ROW(作業用!$B$2))

　　↓
「データの入力規則」ダイアログボックスの[OK]ボタンをクリック
　　↓
入力シートのE4セル（「受注」欄の1番上のセル）を選択
　　↓
[データ]タブをクリック
　　↓
「データツール」グループの[データの入力規則]ボタンをクリック
　　↓
現れた選択肢の中にある[データの入力規則]をクリック
　　↓
現れた「データの入力規則」ダイアログボックスの[設定]タブをクリック
　　↓
「入力値の種類」欄をクリック
　　↓
現れた選択肢の中にある[リスト]をクリック
　　↓
「元の値」欄に

入札中,落札,不成立

と入力
　　↓
「データの入力規則」ダイアログボックスの[OK]ボタンをクリック
　　↓
入力シートのD4～E4のセル範囲をまとめて範囲選択
　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせてマウスを右クリック
　　↓
現れた選択肢の中にある[コピー]をクリック
　　↓
入力シートのD列～E列の5行目以下をまとめて範囲選択
　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせてマウスを右クリック
　　↓
現れた選択肢の中にある[形式を選択して貼り付け]をクリック
　　↓
現れた「形式を選択して貼り付けダイアログボックスの中にある[入力規則]と記されている箇所をクリックして、チェックを入れる
　　↓
「形式を選択して貼り付けダイアログボックスの[OK]ボタンをクリック

　次に、入力シートのA4セルに次の数式を入力して下さい。

=IF(INDEX($C:$C,ROW())="","",ROWS($4:4))

　次に、入力シートのA4セルをコピーして、入力シートのA5以下に貼り付けて下さい。

　次に、作業シートのD4セルに次の数式を入力して下さい。

=IF(COUNTIF($B:$B,INDEX(入力!$D:$D,ROW())),INDEX($A:$A,MATCH(INDEX(入力!$D:$D,ROW()),$B:$B,0))&"■"&COUNTIF(入力!$D$3:INDEX(入力!$D:$D,ROW()),INDEX(入力!$D:$D,ROW())),"")

　次に、作業シートのD4セルをコピーして、作業シートのD5以下に貼り付けて下さい。

　次に、雛型シートの
C1セルに「営業成績管理シート」、
B3セルに「総入札件数」、
B4セルに「総入札額」、
B5セルに「総落札件数」、
B6セルに「総落札額」、
B7セルに「落札率（件数）」、
B8セルに「落札率（金額）」、
と入力して下さい。
　次に、入力シートのA3～C3の範囲（「担当店」と入力されているセルよりも左側にあるセル範囲）をコピーして、雛型シートのA10～C10の範囲に貼り付けて下さい。
　次に、入力シートのE3（「担当店」と入力されているセルの右隣のセル）から右側にあるセル範囲（添付画像の例ではE3～H3）をコピーして、雛型シートのD10セルに貼り付けて下さい。
　次に、雛型シートのB1セルに次の数式を入力して下さい。

=IF(COUNTIF(INDEX(作業用!$B:$B,ROW(作業用!$B$2)+1):INDEX(作業用!$B:$B,MATCH("*?",作業用!$B:$B,-1)),REPLACE(CELL("filename",B1),1,FIND("]",CELL("filename",B1)),)),INDEX(作業用!$A:$A,MATCH(REPLACE(CELL("filename",B1),1,FIND("]",CELL("filename",B1)),),作業用!$B:$B,0)),"")

　次に、以下の操作を行って下さい。

雛型シートのB2セルを選択
　　↓
[ホーム]タブをクリック
　　↓
「フォント」グループの中にある[文字の色]ボタン（「A」の文字の下に色付きの横棒が描かれているボタン）の右端にある「▼」（逆三角）マークをクリック
　　↓
現れた色のサンプルの中にある赤い色の四角形をクリック

　次に、雛型シートのB2セルに次の数式を入力して下さい。

=IF($B$1="","※シート名が正しくありません","")

　次に、雛型シートのA11セルに次の数式を入力して下さい。

=IF(ISERROR(1/(INDEX(入力!$A:$H,MATCH($B$1&"■"&ROWS($11:11),作業用!$D:$D,0),MATCH(A$10,入力!$A$3:$H$3,0))<>"")),"",INDEX(入力!$A:$H,MATCH($B$1&"■"&ROWS($11:11),作業用!$D:$D,0),MATCH(A$10,入力!$A$3:$H$3,0)))

　次に、雛型シートのA11セルをコピーして、次に、雛型シートのB11から右側にあるセル範囲（添付画像の例ではB11～H11）に貼り付けて下さい。
　次に、以下の操作を行って下さい。

雛型シートのB11セルを右クリック
　　↓
現れた選択肢の中にある[セルの書式設定]をクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[表示形式]タブをクリック
　　↓
現れた「分類」欄の中にある[日付]をクリック
　　↓
現れた「種類」欄の中にある

2001/3/14

と記されている箇所をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
雛型シートのE11セルを右クリック
　　↓
現れた選択肢の中にある[セルの書式設定]をクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[表示形式]タブをクリック
　　↓
現れた「分類」欄の中にある[通貨]或いは[会計]をクリック
　　↓
現れた「記号」欄をクリック
　　↓
現れた選択肢の中にある「\」マークをクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック

　次に、雛型シートの11行目全体（添付画像の例ではA11～H11の範囲）をまとめてコピーして、12行目以下に貼り付けて下さい。

※そろそろ回答欄の入力可能文字数の限界を超えそうですので、残りは又後で投稿させて頂きます。

回答No.2

tsubuyuki

> 上記の管理シートを効率よく作成するためにはどのようにすればよいでしょうか。

http://www.amazon.co.jp/%E3%81%93%E3%82%8C%E3%81%8B%E3%82%89%E3%81%AF%E3%81%98%E3%82%81%E3%82%8B-%E3%82%A8%E3%82%AF%E3%82%BB%E3%83%AB%E8%B6%85%E5%85%A5%E9%96%80%E3%81%AE%E6%9C%AC-%E8%87%AA%E5%88%86%E3%81%A7%E9%81%B8%E3%81%B9%E3%82%8B%E3%83%91%E3%82%BD%E3%82%B3%E3%83%B3%E5%88%B0%E9%81%94%E7%82%B9-%E3%81%9F%E3%81%8F%E3%81%95%E3%81%8C%E3%82%8F-%E3%81%A4%E3%81%AD%E3%81%82%E3%81%8D/dp/4774137359
この手の本を一冊用意し、隅々まで読破しましょう。

あるいは
http://www.aviva.co.jp/course/excel.html
この手の教室に通うのも手段の一つです。

正直、ご要望のシステムを「完璧に」作ろうと思ったら、
この質問の仕方しかできないレベルの方がエクセルで作ろうとしても
間違いなく追いつかないレベルの話です。
https://www.google.co.jp/#hl=ja&safe=off&tbo=d&output=search&sclient=psy-ab&q=%E3%82%A8%E3%82%AF%E3%82%BB%E3%83%AB+%E3%82%B7%E3%82%B9%E3%83%86%E3%83%A0%E9%96%8B%E7%99%BA%E4%BC%9A%E7%A4%BE&oq=%E3%82%A8%E3%82%AF%E3%82%BB%E3%83%AB+%E3%82%B7%E3%82%B9%E3%83%86%E3%83%A0%E9%96%8B%E7%99%BA%E4%BC%9A%E7%A4%BE&gs_l=hp.12...1971.1971.0.3233.1.1.0.0.0.0.121.121.0j1.1.0...0.0...1c.1.iy4OcdpnUS8&pbx=1&bav=on.2,or.r_gc.r_pw.r_cp.r_qf.&fp=84771b03c405e768&bpcl=38897761&biw=1366&bih=669
外注してしまう方が早いかもしれません。

お礼

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

ご親切に参考ＵＲＬまで添付いただきありがとうございます。

＞正直、ご要望のシステムを「完璧に」作ろうと思ったら、
＞この質問の仕方しかできないレベルの方がエクセルで作ろうとしても
＞間違いなく追いつかないレベルの話です。

おーっ、きっつう～い、

ひょっとして、気分を害されましたか？

もしも、そうなら、お赦しいただきたいです。

まさか、自分がそんな大変なことを低レベルの質問で対処しようとしていたとは、夢にも思いませんでした。

とにかく、浅はかでした。

回答者様は、こんな質問者の愚行をスルーせずに貴重な時間を割いて指摘いただいて本当に感謝です。

顔を洗って出直してきます。

回答No.1

K Kazz（@JazzCorp）

トランザクションのブックが１つ
マスタと支店は別々のブック
トランザクションからマスタと支店のブックを更新（マスタだけ？）
ブック内のシート名は日付でモディファイ
これだけ？？

お礼

早速回答いただきありがとうございます。

ですが、非常に申し訳ないことに、これでは雰囲気は何とはなしに伝わって来ますが、さっぱり分かりません。折角お答えをいただきましたが、残念なことに私の知識が足りず、問題が解決できません。

まぁ、真意は理解できましたけど・・・。

　ところで、全然関係無い話しですが、回答者様のハンドルネーム（でいのかな？）を拝見したところJAZZがお好きなのでしょうか。
　私はモダンジャズが好きで特にジェリー・マリガンのファンです。

有難う御座いました。