Excel VBA もしくは Google Apps Script で、複数のシートの最終列を取得し新たな1つの列にまとめる方法

Question

初めに以下の Sheet をご確認ください。

https://docs.google.com/spreadsheets/d/1AlwgQLEZoZz_vWz0ZqKld528ViLAl8x2nR4SZz0VoHA/edit#gid=0

↑のような形で各商品の毎月の在庫数が記録される Excel File があるのですが、
以下のように各商品の最新月の在庫数のみを1つのテーブルに自動でまとめる方法がございませんでしょうか？

https://docs.google.com/spreadsheets/d/1AlwgQLEZoZz_vWz0ZqKld528ViLAl8x2nR4SZz0VoHA/edit#gid=1267878017

留意点
・各商品シートの最新月の在庫数が入る列はシートによってバラバラです。
・各商品シートの最新月の在庫数は最終列に追加されていくようになっています。

商品数が100以上あり手動でまとめるのがとても手間です。。。
Excel VBA、もしくは GAS で自動処理する方法はございませんでしょうか？？

どうぞよろしくお願いいたします。

Comments

[papinianus]

vlookupとかでできそうな気もするのですが、そもそもの管理の話としてワンピースは16巻が増えないのですか？今現在の表を拝見するとある月から数値が入っているものがありますがそれらは発生した月にまとめ表で行挿入しているのでしょうか?

Answer 1

追記依頼に回答がないのですが、GASでの回答を。

どう考えても新刊があるから、まとめシートは増減せざるを得ないはず。なので、まとめシートを更新するという発想には無理がある(完了済みのマンガやあらかじめ巻数のわかる百科事典しかあつかわないなら別だが、そういう事業はないと思う)。

一方で、巻が減ることはない(減らす必然性はない)ので、特定の名前のシートを除外した全シートの1列目と最終列をとってきて、取ってきたデータでまとめシートの2行目以降を全面上書きする、というのが(明示されていない部分も含めた)仕様に対応する最も簡素な設計だと思いました。

なお、動作の都合上、まとめシートの名前に4月とかはいっているととてもとても面倒です(毎月↓の各タイトルの在庫部分を編集するなら可能)。またまとめシートのC1あたりに4月の在庫となっていますが、これもしんどいです(最新の在庫とかでいいんじゃね？)
あと私にとって学びがないので、罫線も引いてません。最初からまとめシートはひろめに罫線を引いといてください(消しはしないはず)。

function mergeSheet() {
  const fullset = mergeAllSheetsData();
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("各タイトルの在庫").getRange(2, 1, fullset.length, 3).setValues(fullset);
}
function mergeAllSheetsData() {
  return SpreadsheetApp.getActiveSpreadsheet().getSheets().filter(function(s){return s.getSheetName() !== "各タイトルの在庫";}).map(function(sheet){
    return sheet.getDataRange().getValues().map(function(row){ const n = sheet.getSheetName(); return [n, row[0], row[row.length -1]]});
  }).reduce(function(prev, curr) { return prev.concat(curr);});
}

蛇足

• めんどう、しんどい、と書いている点に対応したい場合は誰かに委託して対応してもらってください
• 考えかたとして、もし一ブックが一ヶ月を表現しておらず、「各タイトルの4月の在庫」「各タイトルの5月の在庫」と12ヶ月分が1つのブックに含まれるのであれば、ワンピースやドラえもんは必ず左端から1月、2月…となっているべきだと思います。またまとめシートもそうなっていればよいと思います。最終列の見出し行でわかるじゃん、と思うかもしれませんが、偶然ドラえもんは4月の取り扱いがなかったので最終列が3月という場合を想定しないとプログラムとしては不十分ということになりますので、初心者には手強い課題になると思います。

Answer 2

With ThisWorkbook.WorkSheets(1)
	LastColumn = .Cells( 1 , .Columns.Count ).End(xlToLeft).Column
End With

上記のコードでワークシートの1行目のデータが入っている最後の列が取れるのでその値を別のシートに保存すれば残せるはずです
withを使用しなくてもかけますが長くなるので今回はwithを使用してます
シートの最大数に関しては

ThisWorkbook.worksheets.Count

でシートの数が取れるはずなのでその回数ループさせる事で複数シート対応できます