Office 2007又は2010を使われる場合、ファイルを「Excel 97-2003 Workbook (*.xls)」型式としてください。
Office 2007又は2010の”xlsx”型式では以下のテクニックが機能しません。
前章の「設計進捗データーと進捗度入力の計算例の基礎編」の「予定・実行進捗データー表」と「予定・実行進捗日付データー入力表」だけの各カテゴリー毎に独立したファイルを作成しています。
- 設計進捗データー_土木建築.xls
- 設計進捗データー_配管.xls
- 設計進捗データー_計装.xls
- 設計進捗データー_電気.xls
次の画像は「設計進捗データー_計装.xls」のデータを表示しています。
データの内容は「設計進捗データーと進捗度入力の計算例の基礎編」と同じです。
B8:G8の黄色の部分が少し違っていて、各セルはユニーク（同じではない）な名前（フィールド名）になっています。
次にB8:G20の範囲にD_Dataの名前を付けています。（データの追加を考慮して、空白行を含めています。）
1.セルやセル範囲を選択
2.名前ボックス（数式バーの左横）をクリック
3.D_Dataのを入力
4.Enterキーを押す
以上で、D_Dataの名前の範囲はデータベースになり、他のファイルから外部データとして取り込むための準備ができました。
下図は上記の独立ファイルのデータを外部データとして取り込み全体の進捗計算をするファイルです。
B9:G9の黄色の部分を除いて、「設計進捗データーと進捗度入力の計算例の基礎編」のデータと同じで、H列以降の内容は計算式を含めて同じです。
B9:G9に追加した黄色の部分に取り込んだ外部データのフィールド名を表示します。このため、H列以降を1段下げています。
L列以降の画像は省略します。
取り込み手順：
１） 抽出データを表示を開始するB10をクリックして、「データ」-「外部データの取り込み」-「新しいデータベース クエリ」をクリック。（上図参照）
２） 「データソースの選択」で「Excel Files*」を選択し「OK」。
３） データのあるフォルダー内の「設計進捗データー_土木建築.xls」を選択して「OK」。
４） クエリーウィザード - 列の選択：クエリーに含むデータ「D_Data」を選んで、全データ取り込みの「＞」をクリックし「次へ(N)」をクリック。
必要なアイテムだけを選ぶことも出来ます。
５） クエリーウィザード - データの抽出：抽出する列で「コード」を選んで、「次の条件に一致する行だけを含む」の「コード」で、「Not Null」を選んで「次へ(N)」をクリック。]
このクエリーで、データがある分だけ取り込みます。 この設定をしないと前述のD_Dataの範囲のデータがない部分を含めて全てを取り込んでしまいます。
６） クエリーウィザード - 並べ替え順序の設定：ここは何もせず、「次へ(N)」をクリック。
７） クエリーウィザード - 完了：ここは何もせず、「完了」をクリック。
８） データのインポート：プロパティ(R)をクリック
９） 外部データ範囲のプロパティー：列の幅を調整する(A)のチェックを外して「OK].。
１０） データのインポート：ここに戻って、既存のワークシート(E)の左上の開始場所セル位置を確かめて「OK]。
１１） 上部欄に「外部データ」ツールバーを表示すると便利です。
外部データがあるセルを選択すると、外部データ・ツールバーがアクティブになるのが判ります。
以上で、土木建築の設計進捗データーを取り込みました。黄色のセルにフィールド名が表示されています。
１） 表-１と同じ手順で、配管、計装、電気の設計進捗データを取り込みます。
２） 但し、2つ目以降の外部データにフィールド名が取り込まれますと不都合ですので、2番目以降の外部データはフィールド名を表示させないようにします。
外部データ範囲のプロパティーで、「フィールド名を含む(E)」のチェックを外します。
３） 取り込んだ後にフィールド名を消す方法。
取り込んだ外部データの任意の位置でマウスを右クリックし、外部データ範囲のプロパティーをクリックして、「フィールド名を含む(E)」のチェックを外して「OK]。
４） 設計進捗外部データの全てを取り込んだ状態です。
５） この後の操作は前述の「応用編−１」と同じです。
設計進捗データ入力用の独立ファイルにデータを入力・更新後、全データ進捗計算表の外部データを以下の手順で更新します。
１） 各外部データ部分の任意のセルをクリックして右クリック、そして、「データの更新(R)」をクリックします。
２） 各外部データを右クリックするのも面倒です。 そこで、Excel マクロで一発で更新しましょう。
VBAのコードは下記の通りです。
Worksheets("シート名").QueryTables(1).Refresh BackgroundQuery:=False （１つ目の外部データを更新します。）
Worksheets("シート名").QueryTables(2).Refresh BackgroundQuery:=False （2つ目の外部データを更新します。）
以下同様にコードを書きます。
外部データを作り直したいことがあります。 ２つの外部データ終端部と先頭部が重なり、両方或いは片方の一部が見えないなどのトラブルが発生することもあります。
以下の対処方法を試してみてください。
１） ２つの外部データが重なっている場合の簡易対策
（１） 重なっている部分に余分になる数の行を追加します。
（２） 前後の外部データを更新します。
（３） 正しく表示されることを確認して、外部データがない部分の行を削除します。
２） 外部データ・エラーの万全対策
（１） エラーになっている外部データ以降全てを削除して、外部データを作成し直します。
（２） 外部データの範囲を選択して削除します。但し、外部データのクエリー名が残っています。
（３） 外部データのクエリー名の削除
メニューの「挿入」→「名前」→「定義」
Query_from_Excel Filesのクエリー名を削除します。
（４） 外部データを再構築します。
オフィス2010の場合の外部データ取り込み；
1)クリック「データ」リボン
2)クリック「その他のデータソース」
3)クリック「Microsoft Query」
4)後は上記と同じ手順です。
H列からK列の計算式には、基礎編で一般的なセル参照関数を使っていました。 一般的なセル参照は曲者で、これは相対参照ではなく絶対参照です。
従い、参照元のデータの一部データ部をセル削除（行削除ではない）した場合、絶対参照のためにセル参照の行がずれてしまいます。
上述しました「表示されたフィールド名を消す」操作をしますと、セル参照の行がずれてしまいます。
一般のセル参照の家計簿・残高計算式の場合、追加行前の計算式をデータの最後までコピー・貼り付けしなければなりませんでした。
この問題を簡単に解決できるOFFSET関数を使いましょう。サンプルファイル「プロジェクト管理例その6.xls」のシート「全データ・進捗計算-3」でOFFSET関数を使っています
このOFFSET関数は利用価値が高く、家計簿の残高計算式にOFFSET関数を使えば、、途中に行を追加してデータを増やしてもその行の前の式をコピーし追加行に貼り付けするだけで済みます。
サンプルファイルのダウンロード