エクセルの数式の使い方について

質問

Excelデータの取りまとめ方についてご教授ください。
説明下手なので、何がしたいのか箇条書きします。
画像データも添付していますので、ご覧くだされば幸いです。

(1)１つのブックに各市のデータを各シート毎に入力してあり、そのデータを参照したとりまとめのシートを作りたい。
(2)列の項目は全市同じだが、行の項目や数がそれぞれの市で違う。
(3)実際は30行×100列くらいあるので、なるべく入力がコピペでできるか最低限の労力でやりたい。
(4)たとえば甲市の”A・X”の値をSheet4の”A・甲市”に表示したいときは、
「Sheet1の行項目のなかに、Sheet4の行項目と同じ"A"があれば、Sheet1の"A・X"の値を入力、なければ０」
のような数式を使えばいいのかと思ったが、このような数式があるのだろうか？

経緯として、これまで各市のデータをそれぞれ１ファイルずつで集めて、30ファイルのエクセルデータにひとつひとつ手で入力していたのですが、
開いてはぽちぽち入力して閉じて、開いてはぽちぽち入力して閉じて…の繰り返しで毎回頭が発狂しそうでした。
エクセルなんだからもっと手間を省く方法があるのではないかと、１つのブックにシートごとにとりまとめてみたまではよかったのですが、
何しろ初心者なもので、IFやVLOOKUP、フィルターなどを調べて使ってみてもうまくいかず…。

複数ブック×複数ブックを１つのブックで集計するというのが無謀な挑戦なのかもしれませんが、
少しでも手間を省くような方法がありましたら教えていただけると幸いです。

回答No.6

pc_net_sp

＞複数ブック×複数ブックを１つのブックで集計するというのが無謀な挑戦なのかもしれませんが、少しでも手間を省くような方法がありましたら教えていただけると幸いです。

エクセルファイル１.xlsのB4に
=[エクセルファイル２.xls]Sheet1!$B$4
と入れれば、ファイル間データが引っ張ってこれます。
計算時集計が面倒になりますが、「複数ブック×複数ブックを１つのブックで集計する」方法です。
エクセルファイル２.xlsを甲市.xlsとし、エクセルファイル１.xlsを各市の統計.xlsとでもすれば、ファイル整理は必要ないはず。
ただし、エクセルファイル１.xls（各市の統計.xls）を開く時、各市のデータファイル（エクセルファイル２.xls・甲市.xls）にアクセスしますので、かなっり重くなると思います。

回答No.5

mt2015

一例です。
各市のシートのA列に作業列を挿入し、「データ名_シート名」を入れます。
手入力でもいいですし、↓こんな感じのセル式でもいいです。
=B2&"_"&RIGHT(CELL("filename",A2),LEN(CELL("filename",A2))-FIND("]",CELL("filename",A2)))

後はまとめ用シートにデータの結合を行い、最後に並び替えを行えばお望みの状態に近いデータになると思います。
手作業でも良いと思いますが、シートが多い場合を考慮して以下の様なマクロを作成してみました。

Sub Sample()
　　'データの統合
　　nShtCount = ThisWorkbook.Sheets.Count
　　ReDim MyAry(nShtCount - 2)
　　For i = 2 To ThisWorkbook.Sheets.Count
　　　　MyAry(i - 2) = Sheets(i).Name & "!R1C1:R4C6"　'←R4C6は実際の最大データに合わせて変更
　　Next i
　　Sheets(1).Cells.ClearContents
　　Sheets(1).Range("A1").Consolidate Sources:=MyAry, Function:=xlSum, TopRow:=True, LeftColumn:=True, CreateLinks:=False

　　'並び替え
　　With Sheets(1).Sort
　　　　.SortFields.Clear
　　　　.SortFields.Add Key:=Sheets(1).Range("A1")
　　　　.SetRange Sheets(1).Cells
　　　　.Header = xlYes
　　　　.Apply
　　End With
End Sub

回答No.4

bunjii

>(1)１つのブックに各市のデータを各シート毎に入力してあり、そのデータを参照したとりまとめのシートを作りたい。
新規に取りまとめ用のシートを作成するのですね？

>(2)列の項目は全市同じだが、行の項目や数がそれぞれの市で違う。
画像で提示の形式ですか？

>(3)実際は30行×100列くらいあるので、なるべく入力がコピペでできるか最低限の労力でやりたい。
1つの数式をコピペすれば済むようにしたいと言うことですよね？
可能ですが、複雑な数式を解読できる知識が無いと修正できなくなりますのでご自身で考えるか有償で処理システムを外注された方が良いと思います。

>(4)たとえば甲市の”A・X”の値をSheet4の”A・甲市”に表示したいときは、「Sheet1の行項目のなかに、Sheet4の行項目と同じ"A"があれば、Sheet1の"A・X"の値を入力、なければ０」のような数式を使えばいいのかと思ったが、このような数式があるのだろうか？
INIECT関数で範囲を指定する方法を使えばSUMPRODUCT関数で目的の配置へ代入できます。
添付画像は提示の画像を元にExcel 2013で検証した結果です。
「Sheet4:Xとりまとめ」のB4セルへ次の数式を設定してF6セルまでコピーしています。
=SUMPRODUCT((INDIRECT($A4&"!$A$4:$A$6")=$A$3)*(INDIRECT($A4&"!$B$3:$F$3")=D$3)*INDIRECT($A4&"!$B$4:$F$6"))
「Sheet5:Yとりまとめ」と「Sheet6:Zとりまとめ」は「Sheet4:Xとりまとめ」の数式をコピーしてあります。
「Sheet1:甲市」、「Sheet2:乙市」及び「Sheet3:丙市」のシート名は夫々の市の名称（甲市、乙市、丙市）に変更しています。

回答No.3

imogasi

こういう（複数）表の組み換えに当たる作業は、エクセル関数でなく、
（１）VBAとか
（２）初めから設計時にアクセスを使うことにするものだ。
VBAやアクセスができなければ、本当はあきらめた方がよい。
ー
１つの方法は
Shweet1、Sheet2、Sheet3の第１（左端列）列に空白列を１列挿入し、全行に各々、甲市、乙市、丙市を入れる（コピーで１発）
そして順次、甲のデータ下に乙、乙の下に丙のデータをコピーして、貼り付ける。
この後、X,Y,Ｚの列（第２ソートキーを市の列）でソートする。
すると、
Ｘ、Ｙ，Ｚ別の甲、乙、丙順のデータになるから、Ｘ、Ｙ、Ｚ別にデータのまとまりをコピーして、新しいシートに張り付けて、シート名・見出し等の文言の体裁を整えてはどうか。
ーー
データを入力する前にこういう点は、熟慮しないといけない点で、エクセルの経験が少ないのだろうが、あとで考えようというのはおかしいのだ。経験して失敗して、勉強しないとほかに手はない。
ーー
エクセルには「統合」という機能がある。知らないだろうが、ＷＥＢででも照会して、勉強して。本件の場合に、応用できるかどうか（今は確認する時間がないが）考えてみて。

回答No.2

noname#232800

私も出来ません。

データの種類をご提示くだされば、詳しく進められますが、表計算は２次元です。聞くところ、自治体ごと、A、Bとか値の出方。日付。

自治体はいくつあるのか？　自治体数固定なのでしょうか？
１０自治体ということで、進めたら２０に増えたのを対処して欲しい。と質問されても答えられません。

まず、自治体数です。
次に日付。年度ごとなのですか？　３年分とか、５年分など動的に変わるのでしょうか？

私はまだ受けることができませんが、ビットコインで報酬は受けられるのでしょうか？

うーん、わかんないなー

回答No.1

HohoPapa

関数では期待のことはできないと思います。
少なくとも私にはできません。

提示の資料であれば
一般には（あるいは私なら）、
添付画像のようなレイアウトにして
１ブック、１シートに集約します。

そうすれば、期待の検索や抽出は
フィルターや並べ替えを使うことで容易に
可能となりましょう。