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

ai
こんにちは「ai」です。

「前任者から引き継いだExcelファイル、ちょっと手を加えただけで上手く動かない…」

こんな経験、よくありますよね?

関数が複雑で、行を追加するだけで何が何だか分からなくなるなんてこと、一度や二度ではないはずです。

またGoogleスプレッドシートなら共同編集も楽で良さそうだけど、Excelで使われている関数が使えない…なんてトラブルが発生することも。

どちらも、わたしに降りかかったトラブルなのですけどね。

そんな難解な関数を抱えているExcelファイルを、ChatGPTの助けを借りてGoogleスプレッドシートに移行した方法をご紹介します。

謎の関数との遭遇

Excel関数

引継ぎされたファイルに設定されたExcel関数

今回、前任者から引き継いだExcelファイルに含まれていた関数は以下です。

=AGGREGATE(14, 6, ROW(INDIRECT($D5 & "!$B:$B"))/(INDIRECT($D5 & "!$B:$B")<>""), 1)-MATCH("動作確認", INDIRECT($D5 & "!$C:$C"), 0)+1

長いですね!

一目見ただけで読み解くためのやる気が湧いてきません。

しかもINDIRECT関数での参照があるので値を追うのが面倒くさいです。

ChatGPTに丸投げしてみる

早々に自分で読み解くことを放棄して、ChatGPTに解説してもらうことにします。

ai
ai

Excelの関数なのですが、現在動いているもので「=AGGREGATE(14, 6, ROW(INDIRECT($D5 & “!$B:$B”))/(INDIRECT($D5 & “!$B:$B”)<>“”), 1)-MATCH(“動作確認”, INDIRECT($D5 & “!$C:$C”), 0)+1」というものがあります。
説明してください。

ChatGPT
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スプレッドシートで開いてみる

いまいちピンとこないですが、取り合えずGoogleスプレッドシートで開いてみました。

スプレッドシートエラー

Excelに設定された関数がスプレッドシートではエラーに…

なんかエラーが発生して使えません。

‘不明な関数: 「AGGREGATE」’だそうです。

ChatGPTに報告しよう

ai
ai

GoogleスプレッドシートではAGGREGATEが使えませんでした。

ChatGPT
ChatGPT

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

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

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

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

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

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

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

こういう置き換えが発生するのがGoogleスプレッドシートへの移行を妨げているんですよね。

ChatGPTに修正してもらう

もう読み解くのも面倒くさいので、ChatGPTにすべての修正をお願いしてしまいましょう。

ai
ai

Googleスプレッドシートで使えるように修正してください。
=AGGREGATE(14, 6, ROW(INDIRECT($D5 & “!$B:$B”))/(INDIRECT($D5 & “!$B:$B”)<>“”), 1)-MATCH(“動作確認”, INDIRECT($D5 & “!$C:$C”), 0)+1

ChatGPT
ChatGPT
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 スプレッドシートにおいて、非表示の行を無視することは簡単ではありませんので、この式はすべての行を対象としている点に注意が必要です。非表示の行を無視したい場合は、より複雑なスクリプトやフィルタに働きかける必要があります。

動作確認をしてみる

ChatGPTから回答を貰ったので、さっそく動作確認してみましょう。

先ほどGoogleスプレッドシートでエラーになっていた関数を提示してもらった関数に置き換えます。

スプレッドシート

修正したらスプレッドシートで動いた

おぉっExcel関数と同じ結果になりました。

色々と行数を変更して試してみましたが、正常に動いているようです。

おわりに

人の目で見ると長くて読み解くのも面倒くさそうなExcelの関数も、ChatGPTは正しく読み解き、Googleスプレッドシートで使えない関数を置き換えて使えるようにしてくれました。

正直、こんなにスムーズに問題解決するとは思っていなかったので、驚きました。

もし、よくわからないExcel関数が設定されたファイルを引き継いだときはChatGPTを利用してみてはいかがでしょうか。

その際はくれぐれも機密事項を含まないように気を付けてくださいね。

この記事を書いた人

ai

散歩とひなたぼっこが好きです。
自然の美しさや季節の移り変わりを感じながら、のんびりとした時間を過ごすのが日々の楽しみになっています。

開発と実験