Excelの複雑な関数をGoogleスプレッドシートで使いたい！前任者が作った関数もChatGPTを使って解決よ

もちろんです。このExcelの関数は、特定の条件を満たすデータの位置を検索し、その位置を返すものです。以下に、各部分の説明を行います。

関数全体の目的：
この関数は、指定されたシート（セル$D5に格納されたシート名）内のデータに対して、「動作確認」という値を検索し、見つかった場合にその行の位置を返します。

各部分の詳細：

1. AGGREGATE関数
• ‘=AGGREGATE(14, 6, …)’はAGGREGATE関数で、データの集計を行います。
• ’14’は「最大値」を示すオプションです。
• ‘6’は「エラーを無視して計算」するためのオプションです。
2. ROW(INDIRECT($D5 & “!$B:$B”))/(INDIRECT($D5 & “!$B:$B”)<>“”), 1)
• ‘INDIRECT($D5 & “!$B:$B”)’は、セル$D5に格納されたシート名で指定されたシートのB列のデータを参照します。
• ‘ROW(…)’は各行の行番号を取得します。
• ‘/ (INDIRECT($D5 & “!$B:$B”)<>“”)’はB列が空でない行のみを対象としています。
• ‘1’はAGGREGATE関数において「最大値」を求めるための引数です。
3. MATCH(“動作確認”, INDIRECT($D5 & “!$C:$C”), 0)
• ‘INDIRECT($D5 & “!$C:$C”)’はセル$D5に格納されたシート名で指定されたシートのC列のデータを参照します。
• ‘MATCH(“動作確認”, …, 0)’は「動作確認」という値がC列内で最初に見つかる行の位置を返します。
4. 最終的な計算
• ‘AGGREGATE(14, 6, …)’で得られた最大値（行番号）から、’MATCH(“動作確認”, …)’で得られた位置を引いています。これにより、「動作確認」という値が見つかった行の位置が得られます。
• ‘+1’はExcelの行番号は1から始まるため、見つかった行の位置に1を加えています。

例：
もしも「動作確認」という値がシート内で3行目にあれば、この関数は3を返します。

注意：この関数はエラー処理が行われているため、対象のデータが見つからない場合やエラーが発生した場合でもエラーを返さずに処理が続行されます。

はい、Google スプレッドシートではExcelのAGGREGATE関数に相当する関数は直接提供されていません。AGGREGATE関数は複数の集計方法を選択し、エラー値や隠れている行を無視するオプションが特徴ですが、Google スプレッドシートでは異なるアプローチを取る必要があります。 Google スプレッドシートで類似の処理を行うには、次のような方法が使われます：

1. SUBTOTAL関数:

ExcelのAGGREGATEの一部機能である隠れたセルを無視する機能は、Google スプレッドシートのSUBTOTAL関数によって提供されています。SUBTOTALは隠れている行を無視しながら、様々な集計処理（合計、平均、最大値、最小値など）を行えます。

2. フィルターを使った計算:

データセットから特定の条件を満たすデータのみを集計したい場合、FILTER関数を使用して条件に合うデータのみを抽出し、その上でSUM、AVERAGE、MAX、MINなどの集計関数を使用します。

3. QUERY関数:

SQLに似たクエリを書くことで、データセットを集計する複雑な操作が可能です。これにより、非表示の行を無視するなどの特定の条件に基づいた集計が行えます。

4. ARRAYFORMULA関数:

複数のセルにわたる計算を配列計算として一度に実行する場合、ARRAYFORMULA関数が利用できます。

5. カスタム関数（Google Apps Script）:

特定の集計作業が標準の関数では対応できない場合は、Google Apps Scriptを使用してカスタム関数を作成することができます。

ExcelのAGGREGATE関数の機能を完全に模倣することはできませんが、上記のような組み合わせや代替機能を利用することで、似たような結果を得ることは可能です。各関数の文法や使用方法については、Google スプレッドシートのヘルプページや公式ドキュメントで詳細が確認できます。

Google スプレッドシートではAGGREGATE関数は利用できないため、異なる関数で代替する必要がありますが、まずはAGGREGATE関数のこの使用例が行っていることを理解しましょう。 ExcelでAGGREGATE(14, 6, …)は、非表示の行を無視するLARGE関数の働きをしており、配列内で最大の値（ここでは1番目の最大値）を返します。その後、その値から特定の文字列「動作確認」が見つかる行をMATCH関数を用いて探し、その位置を差し引いた後、1を加えています。 Google スプレッドシートでは、以下のように代替の式を作成することになります：

=ARRAYFORMULA(MAX(IF(INDIRECT(D5 & "!$B:$B")<>"", ROW(INDIRECT(D5 & "!$B:$B")))) - MATCH("動作確認", INDIRECT(D5 & "!$C:$C"), 0) + 1)

これは以下のステップを行います：

1. INDIRECTを使用して参照するシートからB列の全データを取得します。
2. 非空白のセルの行番号を取得します。（ARRAYFORMULA(ROW(INDIRECT…))で処理されます）
3. その中で最大の値（最も下の非空白セルの行番号）をMAXで見つけます。
4. MATCHで「動作確認」という文字列がC列にある行を見つけます。
5. 最終的に1番目のステップで見つかった最大行から2番目のステップで見つかった行番号を差し引き、さらに1を加えます。

これにより、ExcelのAGGREGATE関数と同様の結果が得られるはずです。ただし、Google スプレッドシートにおいて、非表示の行を無視することは簡単ではありませんので、この式はすべての行を対象としている点に注意が必要です。非表示の行を無視したい場合は、より複雑なスクリプトやフィルタに働きかける必要があります。