家計簿で家計の収支ポイントは分かりますか？GASで関数入りテンプレート作成

家計簿で分かる収支のポイント

企業では主に損益分岐点を出して、多い支出を見直すことが多々あります。
それを家計に置き換えてみました。
いちいち電卓をたたくよりは早いです。

【分岐点の計算方法】
収支ポイント=固定費÷(1-(変動費÷収入))
これを、算出する関数も入れています。
実行したら、年、月、収入見込み　を入れるとテンプレートは出来上がります。
それに色々な支出を打ち込めば出来ます。下の画像が参考です。

実行

質問

テンプレート完成

GASとは

GASは簡単に言えばグーグルが提供しているプログラミング言語です。無料で利用できます。

このサンプルコード

GASをある程度知っている方…下のコードをコピペすれば完了です。
GASをあまりよく分からない方…コードの下に説明を詳しく入れています。ご参照ください。

テンプレート作成　のコード

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('家計簿メニュー') // メニューのタイトルを指定
    .addItem('家計簿作成', '家計簿作成') // メニューに項目を追加し、クリック時に実行する関数を指定
    .addToUi(); // メニューをUIに追加
}

function 家計簿作成() {
  // 年と月を入力プロンプトで取得
  const year = Browser.inputBox("家計簿作成", "何年の家計簿を作成しますか？", Browser.Buttons.OK_CANCEL);
  if (year == "cancel") return;

  const month = Browser.inputBox("家計簿作成", "何月の家計簿を作成しますか？", Browser.Buttons.OK_CANCEL);
  if (month == "cancel") return;

  const incomeEstimate = Browser.inputBox("家計簿作成", "収入見込みを入力してください（例: 350000）", Browser.Buttons.OK_CANCEL);
  if (incomeEstimate == "cancel") return;

  // 新しいシート名を生成
  const newSheetName = `${year}年${month}月`;
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  
  // 同じ名前のシートが存在するか確認
  const existingSheet = spreadsheet.getSheetByName(newSheetName);
  if (existingSheet) {
    Browser.msgBox("エラー", "同じ名前のシートがすでに存在します。別の名前を使用してください。", Browser.Buttons.OK);
    return;
  }

  // 新しいシートを作成
  const newSheet = spreadsheet.insertSheet(newSheetName);

  // A1、B1にデータを書き込み
  const budgetHeader = `${year}年${month}月`;
  newSheet.getRange("A1").setValue(budgetHeader).setFontWeight("bold"); // A1を太字に設定
  newSheet.getRange("B1").setValue("家計簿").setFontWeight("bold"); // B1を太字に設定
  newSheet.getRange("A7").setValue("※日付(例:11月1日の場合 11-1)");

  // F1に収入見込みを設定し、フォーマットを適用
  const incomeCell = newSheet.getRange("G1");
  incomeCell.setValue(Number(incomeEstimate));
  incomeCell.setNumberFormat("#,##0円");

  // 支出と残金のラベルをF1、F2、F3に設定
  newSheet.getRange("F1").setValue("収入");
  newSheet.getRange("F2").setValue("支出");
  newSheet.getRange("F3").setValue("残金");
  newSheet.getRange("F4").setValue("変動費");
  newSheet.getRange("F5").setValue("固定費");
  newSheet.getRange("F6").setValue("分岐点");

  // F2に合計支出の関数を設定
  const expenditureCell = newSheet.getRange("G2");
  expenditureCell.setFormula("=SUM(F9:F100)");

 

  // G4にSUMIF関数を設定
  const sumifCell = newSheet.getRange("G4");
  sumifCell.setFormula("=SUMIF(D$9:D$100, \"変動費\", F$9:F$100)");
  sumifCell.setNumberFormat("#,##0円");

  // G5にSUMIF関数を設定
  const sumifCellFixed = newSheet.getRange("G5");
  sumifCellFixed.setFormula("=SUMIF(D$9:D$100, \"固定費\", F$9:F$100)");
  sumifCellFixed.setNumberFormat("#,##0円");

  // G6に計算式を設定
  const formulaCell = newSheet.getRange("G6");
  formulaCell.setFormula("=G5/(1-(G4/G1))");
  formulaCell.setNumberFormat("#,##0円");

  // D9:D100 に関数を設定
for (let i = 9; i <= 100; i++) {
  newSheet.getRange(`D${i}`).setFormula(`=IF(C${i}="","",IF(OR(C${i}="住居費",C${i}="養育費",C${i}="水道光熱費",C${i}="通信費",C${i}="保険料",C${i}="教育費",C${i}="医療費",C${i}="税金"),"固定費","変動費"))`);
}

 // F3に残金の関数を設定
  const balanceCell = newSheet.getRange("G3");
  balanceCell.setFormula("=G1-G2");
  // F列全体に「0,000円」形式のフォーマットを適用
  newSheet.getRange("F1:F100").setNumberFormat("#,##0円");

  // セルの背景色を薄いオレンジに設定
  const lightOrange = "#FFEBCC";
  newSheet.getRange("F1:G6").setBackground(lightOrange);
  newSheet.getRange("A8:G8").setBackground(lightOrange);

  // 残金がマイナスの場合にフォントカラーを赤に設定
  const rule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied('=G3<0')
    .setFontColor("red")
    .setRanges([balanceCell])
    .build();

  const rules = newSheet.getConditionalFormatRules();
  rules.push(rule);
  newSheet.setConditionalFormatRules(rules);

  // F3の表示形式を「0,000円」に設定
  balanceCell.setNumberFormat("#,##0円");

  // 5行目にヘッダーを書き込み
  newSheet.getRange("A8").setValue("日付");
  newSheet.getRange("B8").setValue("曜日");
  newSheet.getRange("C8").setValue("勘定科目");
  newSheet.getRange("D8").setValue("勘定科目");
  newSheet.getRange("E8").setValue("内容");
  newSheet.getRange("F8").setValue("金額");
  newSheet.getRange("G8").setValue("備考");

  // プルダウンリストの設定
  const categories = [
    "養育費", "住居費", "水道光熱費", "公租公課(税金)", "通信費", "保険料",
    "食費", "日用品費", "被服費", "美容費", "交際費", "趣味費",
    "交通費", "教育費", "医療費", "特別費", "雑費", "その他"
  ];

  const categoryRange = newSheet.getRange("C9:C100");
  const ruleForDropdown = SpreadsheetApp.newDataValidation()
    .requireValueInList(categories)
    .setAllowInvalid(false)
    .build();
  categoryRange.setDataValidation(ruleForDropdown);

  // B列に曜日の関数を設定
  for (let i = 9; i <= 100; i++) {
    newSheet.getRange(`B${i}`).setFormula(`=IF(A${i}="", "", TEXT(A${i}, "dddd"))`);
  }

  


  // 条件付き書式を追加: 土曜日（薄いスカイブルー）と日曜日（薄いピンク）
  const saturdayRule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo('土曜日')
    .setBackground("#B2E0F0")
    .setRanges([newSheet.getRange("A9:F100")])
    .build();

  const sundayRule = SpreadsheetApp.newConditionalFormatRule()
    .whenTextEqualTo('日曜日')
    .setBackground("#F8D1D1")
    .setRanges([newSheet.getRange("A9:F100")])
    .build();

  rules.push(saturdayRule);
  rules.push(sundayRule);
  newSheet.setConditionalFormatRules(rules);
}

GASの基本的な使い方

Dドライブにアクセスして、スプレッドシートを開きます。
拡張機能からAppScriptを選択します。

下のような画面が出てきたら、function myfunctionと書かれている部分をすべて消します。

次に上記のコードをすべて選択して貼り付けます。
保存を押し、実行を押します。

以下、赤印で囲んだところを選択

実行ログに実行完了と表示されたら、スプレッドシートを見て下さい。
メニューバーに家計簿メニューとありますので、家計簿作成を選択してください。テンプレートが出来上がります。

備考

特にないです。