エクセル2007の使い方で質問です。

質問

以前にも同じ問題で質問をさせていただきましたが、少々ややこしかったようなので、出来るだけシンプルにしてみました。

　エクセル2007でデータ管理をするためのブックの作成をしたいのですが、ポイントは、シート１に入力したデータをシート２に反映させたいということなのですが、フォーマットは基本的にシート１、シート２、共に同じで、シート１にはいくつかのカテゴリーが行毎に混在しているのに対し、シート２では各カテゴリー別になっており、シート２－１、シート２－２、シート２－３、・・・。と、カテゴリーの数だけあることになります。
　※カテゴリー名はフォーマットの中の記入する列を設けています。

　以前は、シート１の完成後にオートフィルターを使ってデータを分けておりましたが、出来れば、入力期間中でも、データの推移状況が見て取れる方が良いということになり、その方法を教えていただきたく思い質問をさせていただきました。どうぞ宜しくお願いいたします。

　お詫び：以前質問したときに回答を下さった方の中で、kagakusuki様が物凄い手間をかけてその方法を虎の巻的に書き込んでくださったのですが、私の入力方法に問題があったのか一応コピペしただけだったのに、どうしてもエラーになってしまい、結局原因が解明できず、残念ながら、頓挫してしまいました。
　kagakusuki様には大変な手間をかけてお答えを頂いたにも拘わらず、その労を活かすことができず、大変申し訳ありませんでした。お詫びいたします。

回答No.3

kagakusuki · ベストアンサー

ANo.2です。

>作業と作業中ですか、気づきませんでした。

　「作業中」ではなく

作業用

です。ここを間違えてしまいますと、シート名を「作業」とした場合と同じ事になってしまいますので注意して下さい。

>ウィンドが開いて「条件　データの入力規則に他のワークシートまたはブックへの参照は使用できません。」というコーション表示がされるのですが、これは、どういう意味でしょうか。

　まず最初にお断わりしておきますが、何か問題が起きたのでしたら、「どのセルにおいて」、「どういった作業を行う段階で」、「実際にはどのような操作を行った時に」、「想定ではどうなる予定であったのが」、「実際にはどのようになり（例えば何という表示が現れた等）」、「それのどこが問題なのか」という事をきちんと説明する事が最低限の常識ではないでしょうか？
　頂いた補足には、「実際にはどのようになり」に関しては説明があるものの、「どのセルにおいて」、「どういった作業を行う段階で」、といった事に関しては、何も書かれていないも同然となっています。

>数式をコピペしてＯＫボタンをおしたところ、

としか説明がなく、どの数式の事なのかや、どこにコピペしたのかに関しては一切説明がないのでは、通常の問題であれば、何が起きているのかを相手に理解してもらう事は困難です。
　今回は、偶々、その現象に関係する可能性のある個所が１ヶ所だけでしたので原因を推測する事が出来ましたが、誰かにものを尋ねる場合には、最低限度の状況説明は、相手に言われなくとも自ら行うようでなければ、質問者様御自身も、質問された相手も困る事になります。（このサイトだけの話ではなく、仕事を始めとする社会生活においても同様です）

　さて本題ですが、その現象は、Excel2007ではなく、Excel2003以前のバージョンのExcelを使用した場合に起きる現象です。
　Excel2003等の古いバージョンのExcelでは、セル範囲を参照するドロップダウンリストを設定する際には、その入力規則を設定するセルが存在しているのと、同じSheetにあるセル範囲しか指定する事が出来なかった（←抜け道はあります）のですが、Excel2007からは、別のシートにあるセル範囲も指定する事が出来る様になりました。
　この御質問の表題は「エクセル2007の使い方で質問です。」となってはおりますが、おそらく質問者様は、QNo.7808309の御質問に対して私が回答した方法を試される際に、Excel2007ではなく、もっと古いバージョンのExcelを使用されておられるのではないでしょうか？
　そのため、ドロップダウンリストの設定において、他のシートのセル範囲を参照する事が出来なかったのではないかと思います。
　もし、Excel2003以前のバージョンでも同じExcelブックを使われるのでしたら、D4セル（「担当店」欄の1番上のセル）に入力規則を設定する際に、「データの入力規則」ダイアログボックスの「元の値」欄に入力する数式を

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

ではなく、

=INDIRECT("作業用!B2:B"&MATCH("*?",INDIRECT("作業用!B:B"),-1))

という数式に変更して下さい。
　只、OFFSET関数やその他の多くの関数であれば、元の表を移動させた際には、Excelの方で表の移動先に合わせて自動的に数式を変更してくれるのですか、INDIRECT関数を使用して参照先を指定した場合には、元の表を移動させても数式が自動的に変わる事はないため、部署名の表の位置を変える場合には、一々、入力規則に設定した数式の方も変更せねばならないため、OFFSET関数と比べて若干不便です。

お礼

まず、「入力」「作業用」（作業中と私は間違って表現しておりましたが、作業用と指示通りに修正してありました。）「雛型」「集計」の四つのシートを作成し、指示された通り作業用シートのＡ１、Ａ２並びにＡ２以下、及びＢ２の各セルに名称等を入力。

その後、入力シートのＤ４セルに数式をインプットするために

入力シートのD4セル（「担当店」欄の1番上のセル）を選択
　　↓
[データ]タブをクリック
　　↓
「データツール」グループの[データの入力規則]ボタンをクリック
　　↓
現れた選択肢の中にある[データの入力規則]をクリック
　　↓
現れた「データの入力規則」ダイアログボックスの[設定]タブをクリック
　　↓
「入力値の種類」欄をクリック
　　↓
現れた選択肢の中にある[リスト]をクリック
　　↓
「元の値」欄に数式：=OFFSET(作業用!$B$2,1,,MATCH("*?",作業用!$B:$B,-1)-ROW(作業用!$B$2))

を入力し「ＯＫ」ボタンを押したところ、黄色い三角の中に「！」マークが入ったウィンドが開き
条件データの入力規則に他のワークシートまたはブックへの参照は使用できません。と表示されてお

りましたが、これについては、新たに頂いた数式：=INDIRECT("作業用!B2:B"&MATCH("*?",INDIRECT("作業用!B:B"),-1))

を入力したところ、うまくいきました。感謝です。

その後も全て旨く行きました。本当に感謝です。いろいろとお世話になりましたが、最後までお付き合い頂き何とも御礼の言いようがありません。本当に有難う御座いました。

補足

kagakusuki 様

いろいろとご指摘いただいて有り難く思います。仰る通り、私は間違っておりました。分からない事があれば簡単に質問して簡単に答えを教えてもらえて、これは便利であるということで、安易に捉えてしまい、結果、親身になって貴重な時間を費やして回答いただいている方々に対して大変失礼な対応をしてしまいました。

＞誰かにものを尋ねる場合には、最低限度の状況説明は、相手に言われなくとも自ら行うようでなければ、質問者様御自身も、質問された相手も困る事になります。（このサイトだけの話ではなく、仕事を始めとする社会生活においても同様です）

このようなまるで子供が言われるようなことを書かれるなんて恥ずかしい話しであり、残念でなりませんが、確かにその通りです。反省します。今後は、このような愚かしくも無責任な対応は一切取ることがないように努めます。顔が見えないと言えども、相手は、生身の人間とのコミュニケーションをはかるためのサイトですので、その人達が払わなければならないコストがあることを肝に銘じて利用していこうと思います。

肝心のファイルの不具合については、早急に検証し対応させていただくつもりにしておりますので、もう少し、お時間をいただきく思いますが、とりあえず、お詫びだけでもと思い補足のフォームからで失礼しますが、宜しくお願いいたします。

回答No.2

kagakusuki

丁重な御詫びを頂きまして恐縮です。
　只、このサイトに投稿されて来ます質問は膨大な数になりますから、私も含めまして、このサイトで回答を行っている人間は、全ての回答に目を通す事などはとても出来ませんので、折角、御詫び頂いても、相手に伝わらない恐れも少なからず御座います。
　今回は、偶然にも、私がこの御質問を見つける事が出来ましたが、別な御質問投稿を御使用になられての御詫びや御礼等は、このサイトの本来の使用方法ではなく、また、このサイトのシステムは、その様な目的を達成するには適していないものである事を御承知願います。
　少々きつい物言いと受け取られかねない文面となってしまいましたが、決して気分を害している訳ではなく、その様な使い方をしましても、無駄になる恐れが高いという事を申し上げたかったのです。

　処で、

>どうしてもエラーになってしまい、

との事ですが、何処に（どのセルにおいて）どの様なエラー表示が現れているのでしょうか？
　もしかしますと、作業シートにはエラー表示等は無いにも関わらず、雛型シートのB1セルが#VALUE!エラーとなってはいないでしょうか？
　申し訳御座いません、それはおそらくは私が回答文を入力した際の入力ミスで、数式の方は

作業用

というシート名のシートに対応するものであるにも関わらず、回答文中での指示を

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

という様に、誤ったシート名でシートを作成する様に指示した事が原因かも知れません。
　ですから、「作業」というシート名を

作業用

に変更して頂く様、御願い致します。
　それから、もしも、数式中の「作業用!」となっている箇所の一部のみを「作業!」に変更してしまい、「作業!」に変更済みの箇所と、「作業用!」のままとなっている箇所が混在している状態となってしまっている場合には、
「『作業用!』となっている箇所を全て『作業!』に変更した上で、作業用シートのシート名も『作業』に変更する」
か、或いは
「『作業!』となっている箇所を全て『作業用!』に変更した上で、作業シートのシート名も『作業用』に変更する」
のどちらか操作を行って下さい。
　その際には、Excelの置換機能を使用されると便利です。（尚、もしも置換のやり方が判らない場合には、この回答欄の下に補足等を追加される等の方法で御問い合わせ下さい）

　或いは若しかしますと、雛型シートのB2セルに「※シート名が正しくありません」と表示されている件でしょうか？
　もしも、その件の事を仰っておられるのでしたら、それはエラー等では御座いません。
　雛型シートでは、シート名が「雛型」となっていますが、「雛型」という略称を持つ部署などは存在しませんので、当然、作業用シートのB列にも「雛型」等という略称は記載等されておりませんから、単に

「その様なシート名と同じ略称を持つ部署は、作業用シートのB列の中には記載されていません」

という報告を行っている表示に過ぎませんので、気にせずに、次の作業に入って下さい。

お礼

確かに再び回答を寄せていただき大変ラッキーでした。

さて、修正箇所を教えていただいて感謝です。作業と作業中ですか、気づきませんでした。
ですが、シート名を作業中に変更し、初めからやり直してみようと思ったら、今度は、数式をコピペしてＯＫボタンをおしたところ、ウィンドが開いて「条件　データの入力規則に他のワークシートまたはブックへの参照は使用できません。」というコーション表示がされるのですが、これは、どういう意味でしょうか。何度もお騒がせし、手間ばかりお掛けしますが、差し支えなければ教えていただけないでしょうか。宜しくお願いいたします。

回答No.1

KURUMITO

次のようにすることで計算に負担も少なく多量のデータに対応できます。
シート１の表は元のデータとなるもので例えばA１セルに項目１、B１セルに項目２の項目名が有り、C１セルにはカテゴリーの項目名が、さらにD１セルやE１セルにも項目名が入力されているとします。
F列を作業列としてC列のデータをカテゴリー別に振り分け整理するための数値を表示させることにします。
F２セルには次の式を入力して下方にドラッグコピーします。

=IF(C2="","",IF(COUNTIF(C$2:C2,C2)=1,ROUNDDOWN(MAX(F$1:F1),-4)+10001,ROUNDDOWN(INDEX(F$1:F1,MATCH(C2,C:C,0)),-4)+COUNTIF(C$2:C2,C2)))

このデータをもとにカテゴリーごとのシートにデータを表示させることにします。
カテゴリーごとのシートはSheet2-1、Sheet2-2、Sheet2-3・・・・のようになっているとします。
カテゴリーごとのシートはCtrlキーやShiftキーを押しながらそれらのシート名をクリックすることで同じ作業グループを構成することができます。
作業グループを設定した後でシート２－１のA１セルにはシート名を表示させるために次の式を入力します。

=TRIM(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,10))

また、A２セルから右横方向にはシート１での１行目を作業グループが設定されている状態でコピーして貼り付けます。

作業グループが設定されている状態でシート２－１のA３セルには次の式を入力して右横方向にD３セルまでドラッグコピーしたのちに下方向にもオートフィルドラッグコピーします。

=IF(ISERROR(INDEX(Sheet1!$A:$E,MATCH(MID($A$1,FIND("-",$A$1)+1,3)*10000+ROW(A1),Sheet1!$F:$F,0),COLUMN(A1))),"",INDEX(Sheet1!$A:$E,MATCH(MID($A$1,FIND("-",$A$1)+1,3)*10000+ROW(A1),Sheet1!$F:$F,0),COLUMN(A1)))

これで一連の作業は終わりですのでシート１を選択し作業グループを解除します。
シート２のそれぞれのシートにはカテゴリーごとに表が表示されていることでしょう。
シート１のデータが追加されれば即座にシート２の表は刷新されます。

お礼

回答いただけて大変助かりました。
とりあえず、教えていただいた内容でモデルを作成してみましたが、旨く行きそうな感じです。
何しろ実際のデータベースが少々ややこしくて、ちょいとばかり頭を捻らなければならないのですが、何とか頑張ってみようと思えるところまで来ました。有難う御座いました。感謝します。