Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(Windows 10 Pro : Excel 2016)
ネットワーク上のブックから集計 関数
投稿日時: 21/03/16 16:50:46
投稿者: みみ1212

お世話になります。
 
ネットワーク上の「テスト」フォルダにエクセルブックが50ほど
それぞれのブックにシートが10ほどあります。
 
フォルダ名:テスト
ブック名:test_詳細(1号) ※50ほど
シート名:A-1       ※10ほど
 
集計用ブック
    B列            C列      D列      E列
    ブック名         シート名    カウント    集計
4行目 test_詳細(1号).xlsx    A-1
5行目 test_詳細(1号).xlsx    A-2
     ・             ・
     ・             ・
     ・             ・
 
D4 
=COUNT('\\192.168.0.10\share\テスト\[test_詳細(1号).xlsx]A-1'!$E$6:$E$201)
E4
=SUMPRODUCT((MOD(ROW('\\192.168.0.10\share\TEST\[test_詳細(1号).xlsx]A-1'!$E$6:$E$201),2)=1)*('\\192.168.0.10\share\テスト\[test_詳細(1号).xlsx]A-1'!$E$6:$E$201))
 
と入力しています。
全ての数式を書き換えるのが大変なので
[test_詳細(1号).xlsx]A-1'!
の部分をセル参照でできないものかといろいろ調べていますが
解決できずにおります。
 
どなたかご教示よろしくお願いいたします。

回答
投稿日時: 21/03/16 17:43:29
投稿者: んなっと

よくあるのは...
 
●右のほうあいているところ(下の例ではG:H列)に数式文字列を作成
                                                G4
="=COUNT('\\192.168.0.10\share\テスト\["&B4&"]"&C4&"'!$E$6:$E$201)"
下方向・↓
H4
="=SUMPRODUCT((MOD(ROW($E$6:$E$201),2)=1)*'\\192.168.0.10\share\テスト\["&B4&"]"&C4&"'!$E$6:$E$201)"
下方向・↓
 
●上の数式文字列の範囲をコピー
→D4に「値の貼り付け
 
●Ctrl+H で置換
検索する文字列:=
置換後の文字列:=
→すべて置換
 
※工夫すればPowerQueryが使えるかもしれません。

投稿日時: 21/03/22 10:42:36
投稿者: みみ1212

んなっと 様
 
ご回答いただきありがとうございます。
頑張って理解して・・と思ったのですが、いまだできずにおります。
 
G4
="=COUNT('\\192.168.0.10\share\テスト\["&B4&"]"&C4&"'!$E$6:$E$201)"
●上の数式文字列の範囲をコピー
→D4に「値の貼り付け
 
?数式文字列の範囲とはどの部分なのかがわかりません。
 
1、"=COUNT('\\192.168.0.10\share\テスト\["&B4&"]"&C4&"'!$E$6:$E$201)"
・・・貼り付ける形式が値の貼り付けができません。
 
2、["&B4&"]"&C4&"
・・・数式の一部を変更することしかできず、0になり正確なカウントがされません。
 
3、="=COUNT('\\192.168.0.10\share\テスト\["&B4&"]"&C4&"'!$E$6:$E$201)"
・・・セルのコピーで値の貼り付けはできるものの#REF! のエラーが出ます。
 
 
●Ctrl+H で置換
検索する文字列:=
置換後の文字列:=
→すべて置換
 
上記の検索する文字と置き換えする文字が「=」で同じものと思うのですが、
これもどのような意味があるのかわからずにおります。
 
 
教えていただいたので、どうにかと思い、他にもヒントがあればとネットで探してみましたが
結局わかりませんでした。
 
何度も申し訳ありませんが、今一度教えていただきたく存じます。
よろしくお願いいたします。
 

回答
投稿日時: 21/03/22 13:32:40
投稿者: んなっと

置換の方法はそちらで頑張っていただくとして...
一応PowerQueryの例も書いておきます。
集計対象のE列の見出しが下のように「金額」だった場合...
 
   A    B    C   D   E
5 NO   日付 得意先 商品 金額←ここ
6  001 7月20日   B AA   2
7  002 7月21日   B AB   8
8  003 7月22日   C AA  30
9  004 7月23日   D AC   4
 
 データ
→データの取得
→その他のデータソースから
→空のクエリ
→詳細エディター
→「let ... in ..」の部分を全選択して削除
→以下の文字列を貼り付け
  
let
    Src = Folder.Files("\\192.168.0.10\share\テスト"),
    Fld = "金額",
    Sel = Table.SelectColumns(Src,{"Name","Content"}),
    Add = Table.AddColumn(Sel, "Tbl", each Excel.Workbook([Content],true)),
    Del = Table.RemoveColumns(Add,{"Content"}),
    Exp = Table.ExpandTableColumn(Del, "Tbl", {"Data", "Item"}, {"Data", "Item"}),
    Add2 = Table.AddColumn(Exp, "Custom", each Table.AddIndexColumn([Data],"Index",1,1)),
    Del2 = Table.RemoveColumns(Add2,{"Data"}),
    Add3 = Table.AddColumn(Del2, "Custom.1", each Table.AddColumn([Custom],"OddSum", each Record.Field(_, Fld)*Number.Mod([Index]+1,2) ,Int64.Type)),
    Add4 = Table.AddColumn(Add3, "Custom.2", each Table.AddColumn([Custom.1],"Isnum", each if Value.Type(Record.Field(_, Fld))=Number.Type then 1 else 0 ,Int64.Type)),
    Del3 = Table.RemoveColumns(Add4,{"Custom", "Custom.1"}),
    Agr = Table.AggregateTableColumn(Del3, "Custom.2", {{"Isnum", List.Sum, "個数"},{"OddSum", List.Sum, "奇数合計"}})
in
    Agr
 
→ 金額 の部分はそちらにあわせて変更してください
→完了
→閉じて読み込む
 
        A    B   C     D
1      Name  Item 個数 奇数合計
2 ファイル1.xlsx Sheet1   4     2
3 ファイル1.xlsx Sheet2   2    35
4 ファイル2.xlsx Sheet1   4     7
5 ファイル2.xlsx Sheet2   4    20
6 ファイル3.xlsx Sheet1   4     7
7 ファイル3.xlsx Sheet2   5    200

トピックに返信