Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 10 Pro : Excel 2016)
関数内のリンク先の指定について
投稿日時: 21/08/17 18:27:22
投稿者: TI

こんばんわ。
 
シートA、シートBがあり、シートBに
 
=SUMIFS('\\サーバー\テスト\[シートA.xls]支払'!$B$1:$B,
 '\\サーバー\テスト\[シートA.xls]支払'!$F$1:$F,">=" & $A6,
 '\\サーバー\テスト\[シートA.xls]支払'!$F$1:$F,"<=" & $A7-1)
 
という数式があります。
 
「\\サーバー\テスト\[シートA.xls]」
この部分をセルに入力した値を使用する方法があれば、
教えていただけますでしょうか。
 
「シートA」に該当するブックが複数あるので、ブックのパスを
セルに貼り付け、それを使用することができないかと思案して
おります。
 
よろしくお願いします。
 

回答
投稿日時: 21/08/17 19:33:04
投稿者: んなっと

とりあえずはINDIRECT関数をGoogle検索してみてください。
おそらく完全に希望通りとはいかないと思います。
 
INDIRECT関数を試したあとでこのスレッドに追加質問してください。

投稿日時: 21/08/18 15:16:33
投稿者: TI

ご回答ありがとうございます。
 
Indirect関数を調べて、以下のように記述をしました。
 
A1セル・・・ファイル名以外のパス
A2セル・・・ファイル名
A3セル・・・シートA
 
と入力をしておき、
 
=INDIRECT("'"&$A$1&"["&$A$2&"]"&$A$3&"'!C" & ROW()-2 & "")
 
と記述することで、別ブックのデータを表示することができました。
 
質問ではSUMFS関数としていたのですが、単純化をするために
「=」でつなぐ形でテストをしました。
 
表示はされたのですが、参照先のファイルが開いていないと、
上記の数式が入ったファイルを開き直した際に「#REF!」と
なってしまいました。
 
SUMIFやSUMIFSでも同じようにファイルが開いていないと
ダメだったと思います。
 
また、少々わかりにくいので、自分だけが触るものであれば、
良いのですが、メンテナンスのことを考えるとあまり、よくないかな
と思っています。
 
ということで、現在はリンク元ファイルに集計用シートを
追加し、その内容をリンク先ファイルに「リンク貼り付け」で
対応を考えていますが、そうなると元ファイルの保存先を
変更されないような対策が必要かな、と思っています。
 
他に良い方法があれば、ご教授いただければ幸いです。
 

回答
投稿日時: 21/08/18 15:51:13
投稿者: んなっと

「取得と変換」がおすすめです。
PowerQueryを使うと、簡単に外部ブックのデータ取り込み・集計ができます。
データ取り込みの手順だけ。
先に Excel 名前ボックス でGoogle画像検索して、名前ボックスの位置を確認してください。
 
手順は次の1.〜3.
 
1.
 どこかのセルにパス \\サーバー\テスト\シートA.xls を入力
→上のセルを選択した状態で、名前ボックスに FName と入力
 
2.
 どこかのセルにシート名 支払 を入力
→上のセルを選択した状態で、名前ボックスに SName と入力
 
3.
 データ→データの取得→その他のデータソースから→空のクエリ
→詳細エディター
→以下の構文に書き換え
 
let
    Fnm = Excel.CurrentWorkbook(){[Name="FName"]}[Content]{0}[Column1],
    Snm = Excel.CurrentWorkbook(){[Name="SName"]}[Content]{0}[Column1],
    Src = Excel.Workbook(File.Contents(Fnm), null, true),
    Dat = Src{[Item=Snm,Kind="Sheet"]}[Data]
in
    Dat

 
→完了
→閉じて読み込む

回答
投稿日時: 21/08/18 16:20:21
投稿者: んなっと

4.
何か変更があったら
データ→すべて更新
 
さらに「集計」したいとき。ここからは簡単です。
データ→クエリと集計 で画面右端のクエリをダブルクリック
→あとは普通のExcelと同じく▼をクリックしてフィルターや数値フィルター

回答
投稿日時: 21/08/18 17:42:57
投稿者: んなっと

さらに短い手順。
  
1.
 どこかのセルにパス \\サーバー\テスト\シートA.xls を入力
 その右隣りのセルにシート名 支払 を入力
→上の2つのセルを選択した状態で、名前ボックスに FName と入力
  
2.
 データ→データの取得→その他のデータソースから→空のクエリ
→詳細エディター
→以下の構文に書き換え
  
let
    Fnm = Excel.CurrentWorkbook(){[Name="FName"]}[Content]{0}[Column1],
    Snm = Excel.CurrentWorkbook(){[Name="FName"]}[Content]{0}[Column2],
    Src = Excel.Workbook(File.Contents(Fnm), null, true),
    Dat = Src{[Item=Snm,Kind="Sheet"]}[Data],
    Hed = Table.PromoteHeaders(Dat, [PromoteAllScalars=true])
in
    Hed

  
→完了
→閉じて読み込む

投稿日時: 21/08/22 18:34:04
投稿者: TI

ご返信が遅くなり申し訳ありません。
 
ご提示いただいたものを試してみましたが、
 
Expression.Error: キーがテーブルのどの行とも一致しませんでした。
 
というエラーが出ました。
 
私もパワークエリを使用することが初めてなので、調べてはいるのですが
エラーの内容がまだわかっていないのですが、現状の報告です。
 
もう少し、自分でも調べてみます。
 
自宅の環境がExcel2013なので、業務中しかテストができないので、
ご返信のペースが遅くなることをご容赦ください。
 
よろしくお願いします。
 
 

回答
投稿日時: 21/08/22 19:02:39
投稿者: んなっと

シート名が違っている可能性が高いと思います。
ネットワーク上のファイルだとやりにくいですね。
いったんローカルのファイルで、ブック名・シート名が確実に存在するものに変えて試してみましょう。

回答
投稿日時: 21/08/22 19:05:26
投稿者: んなっと

いつも思うのですが、なぜこんなに簡単で便利なPowerQueryを使わないのでしょう。
ネット上にはいくらでも情報があるのに、それを積極的に学ぼうとしない。
自分で一から勉強するのが面倒なのでしょうか。

回答
投稿日時: 21/08/23 21:39:34
投稿者: んなっと

ブックの種類が*.xlsの場合、こちらになるかもしれません。
これも試してみてください。名前定義はFName一つだけの方のやり方で。
 
let
    Fnm = Excel.CurrentWorkbook(){[Name="FName"]}[Content]{0}[Column1],
    Snm = Excel.CurrentWorkbook(){[Name="FName"]}[Content]{0}[Column2],
    Src = Excel.Workbook(File.Contents(Fnm), null, true),
    Dat = Src{[Name=Snm]}[Data],
    Hed = Table.PromoteHeaders(Dat, [PromoteAllScalars=true])
in
    Hed

投稿日時: 21/09/07 11:14:35
投稿者: TI

んなっと様
 
せっかく、アドバイスいただいていながら、
ご返信ができておらず、申し訳ありません。
 
本業が忙しく、こちらに時間を割くことが
難しい状況になってしまい、教えていただいた
内容のご報告が遅くなりそうなので、一度
スレッドを閉じたいと思います。
 
作業が再開できたのちに改めてご報告
いたします。
 
この度は、ありがとうございます。