質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.54%
Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

1回答

620閲覧

勤務時間のオリジナル関数を作りたい。

sloth824

総合スコア2

Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2023/05/20 08:47

編集2023/05/22 00:01

実現したいこと

googleスプレッドシートで勤務時間や超過勤務時間、深夜勤務時間を個別に各月毎に算出したい。
ただし、勤務時間の計算は別会社で働いていたとしても同じ日であるならば、1日の勤務時間は合計され、1日の勤務時間が8時間以上の場合は休憩時間としてマイナス60分、6時間以上8時間未満なら45分マイナスされるような計算をしたいです。

前提

次のようなシートがあります。
1つ目は合計シートとして、
イメージ説明
A列、B列、C列に関しては手打ちです。
D列、E列、F列がオリジナル関数で数値をオートで求められるようにしたいと考えています。

その他に下記のような各月のシートがあります。
イメージ説明

A列、B列、D列~J列までは直接値を入力し、C列に関してはvlookupで合計シートから表示されるようにしています。またK列に関しましては、画像のように行毎にH列-E列+G列-F列となっています。

例えば、現状の4月の状態であれば
台帳番号897の太郎さんの場合は
4/1は1施設で9時間以上ということで 8時間
4/2は2施設で9時間以上ということで8時間
4/3は有給で9時間ということで8時間
合計4月の勤務時間は24時間ということで、合計シートのD3の答えは24になります。

当初D4の数式は、
=IF(sumifs('4月'!K:K,'4月'!B:B,A2,'4月'!A:A,"2023/4/1")*24>=9,sumifs('4月'!K:K,'4月'!B:B,A2,'4月'!A:A,"2023/4/1")*24-1,IF(sumifs('4月'!K:K,'4月'!B:B,A2,'4月'!A:A,"2023/4/1")*24>=6,sumifs('4月'!K:K,'4月'!B:B,A2,'4月'!A:A,"2023/4/1")*24-0.75,sumifs('4月'!K:K,'4月'!B:B,A2,'4月'!A:A,"2023/4/1")*24))
とし、これを月末まで作ろうとすると年間分を入れ込むと重くなると思いオリジナル関数を作成しようという運びになりました。

該当のソースコード

function CALCULATE_TOTAL(numberValue, monthValue, sheetName, dateColumn, numberColumn, valueColumn) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); var dataRange = sheet.getRange(dateColumn + ":" + valueColumn); var dataValues = dataRange.getValues(); var total = 0; for (var i = 0; i < dataValues.length; i++) { var row = dataValues[i]; var date = row[0]; var number = row[numberColumn.charCodeAt(0) - dateColumn.charCodeAt(0)]; var value = row[valueColumn.charCodeAt(0) - dateColumn.charCodeAt(0)]; if (number === numberValue && date.getMonth() + 1 === monthValue) { if (value * 24 >= 9) { total += value * 24 - 1; } else if (value * 24 >= 6) { total += value * 24 - 0.75; } else { total += value * 24; } } } return total; }

で、セルには
=CALCULATE_TOTAL(897, 4, "Sheet1", "A", "B", "K")
と入力しました。
結果は-159060412800000.00と答えがおかしくなりました。
その後もchatGPTとやりとりしていると

function CALCULATE_NUMBER_MONTHLY_TOTAL(numberValue, monthValue, sheetName, dateColumn, numberColumn, valueColumn) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); var dataRange = sheet.getRange(dateColumn + ":" + valueColumn); var dataValues = dataRange.getValues(); var total = 0; for (var i = 0; i < dataValues.length; i++) { var row = dataValues[i]; var date = row[0]; var number = row[numberColumn.charCodeAt(0) - dateColumn.charCodeAt(0)]; var value = row[valueColumn.charCodeAt(0) - dateColumn.charCodeAt(0)]; if (number === numberValue && date.getMonth() + 1 === monthValue) { if (value >= 0) { if (value * 24 >= 9) { total += value * 24 - 1; } else if (value * 24 >= 6) { total += value * 24 - 0.75; } else { total += value * 24; } } } } return total; }

など試してみました。

試したこと

ChatGPTとのやり取りの一部
以下の手順に従って関数を作成します:

スクリプトエディタを開きます。スプレッドシート上で「ツール」→「スクリプトエディタ」を選択します。

スクリプトエディタが開かれたら、以下の関数を入力します:

function calculateTotal(hoursRange, ledgerRange, monthRange) { var hours = hoursRange.getValues(); var ledgers = ledgerRange.getValues(); var months = monthRange.getValues(); var total = 0; for (var i = 1; i < hours.length; i++) { var month = new Date(months[i][0]).getMonth() + 1; if (month === new Date().getMonth() + 1) { var ledger = ledgers[i][0]; var hour = hours[i][0]; if (ledger === "" || hour === "") { continue; } var timeValue = hour * 24; if (timeValue >= 9) { total += timeValue - 1; } else if (timeValue >= 6) { total += timeValue - 0.75; } else { total += timeValue; } } } return total; }

上記の関数を保存し、スクリプトエディタを閉じます。

シート「合計」に戻り、合計の計算を行いたいセルに以下の式を入力します(例: C2セル):

less
Copy code
=calculateTotal('4月'!K2:K, '4月'!B2:B, '4月'!A2:A)
この式は、'4月'シートの列Kを時間の範囲、列Bを台帳番号の範囲、列Aを日付の範囲として指定しています。適宜、これらの範囲を修正してください。

入力した式に基づいて、特定の月の合計が自動的に計算されます。この式を他のセルにコピーして、他の月に対しても適用することができます。
以上の手順に従って、各月に応じて修正されるオリジナル関数を作成しました。この関数は現在の月を基準に合計を計算します。

User
TypeError: hoursRange.getValues is not a function(行 2)が発生しました。
ChatGPT
申し訳ありません、エラーが発生しました。おそらくhoursRangeが範囲ではなく、単一のセルを参照しているためです。

以下の修正を行い、再度試してみてください:

スクリプトエディタを開きます。

calculateTotal関数の以下の行を修正します:

Copy code var hours = hoursRange.getValues();

修正後の行は以下のようになります:

Copy code var hours = hoursRange.getValue();

修正を保存し、スクリプトエディタを閉じます。
これでエラーが修正され、関数が正常に動作するはずです。再度試してみてください。お手数ですが、もし再びエラーが発生した場合はお知らせください。

User
修正したようには見えませんが…

ChatGPT
申し訳ありません、修正が不十分でした。もう一度修正を行います。

以下の修正を行ってください:

スクリプトエディタを開きます。

calculateTotal関数の以下の行を修正します:

Copy code var hours = hoursRange.getValue();

修正後の行は以下のようになります:

Copy code var hours = hoursRange.getValues();

修正を保存し、スクリプトエディタを閉じます。
お手数ですが、もう一度修正後のスクリプトを試してみてください。もし再度エラーが発生した場合は、再度お知らせください。

User
var hours = hoursRange.getValues();とvar hours = hoursRange.getValue();なにがちがうのですか?
ChatGPT
hoursRange.getValues()とhoursRange.getValue()は、スプレッドシート上のデータの取得方法に違いがあります。

hoursRange.getValues()は、指定した範囲内の複数のセルの値を二次元の配列として返します。つまり、範囲内のすべてのセルの値を取得します。たとえば、K2:Kのような範囲を指定すると、K2セルからK列の最後のセルまでの値を取得します。

一方、hoursRange.getValue()は、指定した範囲の単一のセルの値を取得します。つまり、範囲内の最初のセルの値を取得します。たとえば、K2のような単一のセルを指定すると、そのセルの値を取得します。

修正すべきはhoursRange.getValues()です。お手数ですが、もう一度修正後のスクリプトを試してみてください。もし再度エラーが発生した場合は、再度お知らせください。

User
TypeError: hoursRange.getValue is not a function(行 2)が発生しました。
ChatGPT
申し訳ありません、再度エラーが発生しました。おそらくhoursRangeが範囲ではなく、単一のセルを参照しているためです。

以下の修正を行ってください:

スクリプトエディタを開きます。

calculateTotal関数の以下の行を修正します:

Copy code var hours = hoursRange.getValues();

修正後の行は以下のようになります:

Copy code var hours = [[hoursRange.getValue()]];

修正を保存し、スクリプトエディタを閉じます。
お手数ですが、もう一度修正後のスクリプトを試してみてください。もし再度エラーが発生した場合は、再度お知らせください。

User
TypeError: hoursRange.getValue is not a function(行 2)が発生しました。
ChatGPT
申し訳ありません、再度エラーが発生しました。おそらくhoursRangeが範囲ではなく、単一のセルを参照しているためです。

以下の修正を行ってください:

スクリプトエディタを開きます。

calculateTotal関数の以下の行を修正します:

Copy code var hours = hoursRange.getValues();

修正後の行は以下のようになります:

Copy code var hours = [[hoursRange.getDisplayValue()]];

修正を保存し、スクリプトエディタを閉じます。
お手数ですが、もう一度修正後のスクリプトを試してみてください。もし再度エラーが発生した場合は、再度お知らせください。

User
上記で試しています
ChatGPT
申し訳ありません、再度エラーが発生しました。もう一度修正を行います。

以下の修正を行ってください:

スクリプトエディタを開きます。

calculateTotal関数の以下の行を修正します:

var hours = hoursRange.getValues();

修正後の行は以下のようになります:

var hours = [[hoursRange.getValue()]];

修正を保存し、スクリプトエディタを閉じます。
お手数ですが、もう一度修正後のスクリプトを試してみてください。もし再度エラーが発生した場合は、再度お知らせください。

User
function calculateTotal(hoursRange, ledgerRange, monthRange) {
var hours = [[hoursRange.getValue()]];

var ledgers = ledgerRange.getValues();
var months = monthRange.getValues();
var total = 0;

for (var i = 1; i < hours.length; i++) {
var month = new Date(months[i][0]).getMonth() + 1;

if (month === new Date().getMonth() + 1) { var ledger = ledgers[i][0]; var hour = hours[i][0]; if (ledger === "" || hour === "") { continue; } var timeValue = hour * 24; if (timeValue >= 9) { total += timeValue - 1; } else if (timeValue >= 6) { total += timeValue - 0.75; } else { total += timeValue; } }

}

return total;
}
の状態でエラーが出ています
ChatGPT
申し訳ありません、エラーが発生しているようです。修正が必要です。

以下の修正を行ってください:

スクリプトエディタを開きます。

calculateTotal関数の以下の行を修正します:

var hours = [[hoursRange.getValue()]];

修正後の行は以下のようになります:

var hours = hoursRange.getValues();

修正を保存し、スクリプトエディタを閉じます。
お手数ですが、修正後のスクリプトを試してみてください。もし再度エラーが発生した場合は、再度お知らせください。

User
最初に戻っています
ChatGPT
申し訳ありません、混乱を招いてしまいました。おっしゃる通り、最初の修正で正しい修正内容を提供しました。

以下の修正を行ってください:

スクリプトエディタを開きます。

calculateTotal関数の以下の行を修正します:

var hours = [[hoursRange.getDisplayValue()]];

修正後の行は以下のようになります:

var hours = hoursRange.getValues();

修正を保存し、スクリプトエディタを閉じます。
お手数ですが、もう一度修正後のスクリプトを試してみてください。もし再度エラーが発生した場合は、再度お知らせください。

補足情報(FW/ツールのバージョンなど)

このような状況でありAIとは意思疎通が難しかったため、投稿させていただきました。
上記の勤務時間のオリジナル関数の他、深夜勤務(22時以降の勤務時間)や超過勤務時間(休憩を除いた8時間以上の時間)の出し方をご教授いただければ幸いです。
お手数をお掛け致しますが、よろしくお願いいたします。

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2023/05/22 12:54 編集

追記しました
guest

回答1

0

ベストアンサー

function CALCULATE_TOTAL(numberValue, monthValue, sheetName, dateColumn, numberColumn, valueColumn) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); var dataRange = sheet.getRange(dateColumn + ":" + valueColumn); var dataValues = dataRange.getValues(); var subtotal = {}; for (var i = 0; i < dataValues.length; i++) { var row = dataValues[i]; var date = row[0]; if (date === '' || Object.prototype.toString.call(date) !== '[object Date]') continue; var dateindex = Utilities.formatDate(date, "Asia/Tokyo", "yyMMdd"); if (subtotal[dateindex] == null) subtotal[dateindex] = 0; var number = row[numberColumn.charCodeAt(0) - dateColumn.charCodeAt(0)]; var value = row[valueColumn.charCodeAt(0) - dateColumn.charCodeAt(0)]; value = value.getHours() + value.getMinutes() / 60; if (number === numberValue && date.getMonth() + 1 === monthValue) { subtotal[dateindex] += value; } } var total = 0; for (var dateindex of Object.keys(subtotal)) { var rest = 0; var perday = subtotal[dateindex]; if (perday >= 9) { rest = 1; } else if (perday >= 6) { rest = 0.75; } total += (perday - rest); } return total; }

AIは一切つかってません。

投稿2023/05/20 13:36

編集2023/05/20 14:05
退会済みユーザー

退会済みユーザー

総合スコア0

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

sloth824

2023/05/22 00:16

ご回答ありがとうございます! =CALCULATE_TOTAL(A3,4,"4月",'4月'!A:A,'4月'!B:B,'4月'!K:K)という入力式で会っていますでしょうか。 その場合、Exception: Range not found(行 3)が発生しました。と出ました。 ちなみにChatgptに function CALCULATE_TOTAL(numberValue, monthValue, sheetName, dateColumn, numberColumn, valueColumn) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName); var dataRange = sheet.getRange(dateColumn + ":" + valueColumn); var dataValues = dataRange.getValues(); var subtotal = {}; for (var i = 0; i < dataValues.length; i++) { var row = dataValues[i]; var date = row[0]; if (date === '' || Object.prototype.toString.call(date) !== '[object Date]') continue; var dateindex = Utilities.formatDate(date, "Asia/Tokyo", "yyMMdd"); if (subtotal[dateindex] == null) subtotal[dateindex] = 0; var number = row[numberColumn.charCodeAt(0) - dateColumn.charCodeAt(0)]; var value = row[valueColumn.charCodeAt(0) - dateColumn.charCodeAt(0)]; value = value.getHours() + value.getMinutes() / 60; if (number === numberValue && date.getMonth() + 1 === monthValue) { subtotal[dateindex] += value; } } var total = 0; for (var dateindex of Object.keys(subtotal)) { var rest = 0; var perday = subtotal[dateindex]; if (perday >= 9) { rest = 1; } else if (perday >= 6) { rest = 0.75; } total += (perday - rest); } return total; } は、具体的には数式として同入力したらよいでしょうか。 numberValueはsheet合計のA2、 monthValueは4月なので4, sheetNameは4月を参照したく、 dateColumnはsheet4月のA列、 numberColumnはsheet4月 のB列,valueColumnはsheet4月のK列です。 と質問してみたら、 数式として同じように入力する場合、以下のようになります。 =CALCULATE_TOTAL(A2, 4, "'4月'", "'4月'!A:A", "'4月'!B:B", "'4月'!K:K) この数式は、A2セルの値をnumberValueとして、4をmonthValueとして、'4月'をsheetNameとして、'4月'シートのA列をdateColumn、B列をnumberColumn、K列をvalueColumnとして関数に渡します。 ただし、数式を直接入力する場合は、Google スプレッドシートの数式として認識されるため、スクリプトとして実行されるわけではありません。もし数式をスクリプトとして実行したい場合は、スクリプトエディタにコードを貼り付け、関数を呼び出す必要があります。 と言われ、実際に入力してみるとTypeError: Cannot read properties of null (reading 'getRange')(行 3)が発生しました。 と言われました。 本当に色々理解できておらず、申し訳ありません。
退会済みユーザー

退会済みユーザー

2023/05/22 13:57 編集

> =CALCULATE_TOTAL(A3,4,"4月",'4月'!A:A,'4月'!B:B,'4月'!K:K)という入力式で会っていますでしょうか。 いいえ。 私の書いたコードを使いたいならば =CALCULATE_TOTAL(A3, 4, "4月", "A", "B", "K") という式を書けば動くと思います。 chatGPTの回答は間違ってます。
sloth824

2023/05/23 02:04

ありがとうございました! お蔭様で答えが出ました。 cm67809733様のお蔭で、やはりAIより人の方が素晴らしいことも改めて学ぶことが出来ました。 ありがとうございます!
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.54%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

同じタグがついた質問を見る

Google スプレッドシート

Google スプレッドシートは、フリーで利用できる表計算ソフト。Webアプリのためインターネットに接続することで利用できます。チャートやグラフの作成のほか、シートを他のユーザーと共有したり、同時に作業を進めることも可能です。

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。