こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

締切り済みの質問

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

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

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

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

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

投稿日時 - 2018-01-08 18:49:01

QNo.9416345

困ってます

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(6)

ANo.6

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

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

投稿日時 - 2018-01-09 16:05:54

ANo.5

一例です。
各市のシートの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

投稿日時 - 2018-01-09 12:20:00

ANo.4

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

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

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

>(4)たとえば甲市の”A・X”の値をSheet4の”A・甲市”に表示したいときは、「Sheet1の行項目のなかに、Sheet4の行項目と同じ"A"があれば、Sheet1の"A・X"の値を入力、なければ0」のような数式を使えばいいのかと思ったが、このような数式があるのだろうか?
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:丙市」のシート名は夫々の市の名称(甲市、乙市、丙市)に変更しています。

投稿日時 - 2018-01-09 10:24:45

ANo.3

こういう(複数)表の組み換えに当たる作業は、エクセル関数でなく、
(1)VBAとか
(2)初めから設計時にアクセスを使うことにするものだ。
VBAやアクセスができなければ、本当はあきらめた方がよい。

1つの方法は
Shweet1、Sheet2、Sheet3の第1(左端列)列に空白列を1列挿入し、全行に各々、甲市、乙市、丙市を入れる(コピーで1発)
そして順次、甲のデータ下に乙、乙の下に丙のデータをコピーして、貼り付ける。
この後、X,Y,Zの列(第2ソートキーを市の列)でソートする。
すると、
X、Y,Z別の甲、乙、丙順のデータになるから、X、Y、Z別にデータのまとまりをコピーして、新しいシートに張り付けて、シート名・見出し等の文言の体裁を整えてはどうか。
ーー
データを入力する前にこういう点は、熟慮しないといけない点で、エクセルの経験が少ないのだろうが、あとで考えようというのはおかしいのだ。経験して失敗して、勉強しないとほかに手はない。
ーー
エクセルには「統合」という機能がある。知らないだろうが、WEBででも照会して、勉強して。本件の場合に、応用できるかどうか(今は確認する時間がないが)考えてみて。

投稿日時 - 2018-01-08 22:23:42

ANo.2

私も出来ません。

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

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

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

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

うーん、わかんないなー

投稿日時 - 2018-01-08 22:02:13

ANo.1

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

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

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

投稿日時 - 2018-01-08 21:10:15