私はスプレッドシートをあまり使いません。作家として、ワープロやテキストエディタに人生を費やしてきました。もちろん、スプレッドシートの使い方は知っていますし、微調整や更新が必要な計算のためにシンプルなスプレッドシートを作成することを強く信じています。最近まで、スプレッドシートを開かずに何ヶ月も過ごすこともありました。私がスプレッドシートをあまり使わないのは、周りの人たちがページレイアウトアプリで同じ文書を作成するためのツールやスキルがないため、スプレッドシートをレイアウトプログラムとして使っていたからです。適切なセルに入力すれば「ページ」上のどこにでもテキストを配置できるので、確かに魅力的ですが、特別な場合を除いて、Excelをドライバー代わりにしてページレイアウトの壁に釘を打ち込むのは、最終的には悲惨な結果に終わるでしょう。
しかし、ここ1、2年で私の考え方は変わりました。私の中に眠るスプレッドシートの達人を解き放つには、まさにうってつけのプロジェクトが必要だっただけなのだと気づきました。めったにスプレッドシート以外のことに手を出さない人間からSUM、AVERAGEマルチシートルックアップの素晴らしさを発見した人間へと進化した私の軌跡を、この非常に具体的な物語を通してお伝えすることで、現代のスプレッドシートという驚異的なツールを、皆さんももっと有効活用するためのヒントを得られることを願っています。
ツールといえば、私がやったことはAppleのNumbersやMicrosoft Excelでもできると思うのですが、今回はGoogleスプレッドシートを使うことにしました。Googleスプレッドシートが必ずしも最も強力なスプレッドシートだからというわけではありません。どれほど優れているかは分かりませんが、スプレッドシートに複数の人が簡単にアクセスできるのが最大のメリットです。最近、ほとんどの文章作成をGoogleドキュメントで行っているのも、まさにこのためです。また、オンラインドキュメントを他のデバイスからでも開けるのも便利です。
予算ツールの設計
2020年、私はフィンガーレイクスランナーズクラブ（FLRC）の会長に就任しましたが、パンデミックの影響で2月以降のレースをすべて中止せざるを得なくなり、クラブの財務運営は完全に混乱しました。2020年末、当時の会計担当者は、一部のレースが再開され、他のレースは再開されなかったため、まったくの推測に基づいて2021年度の予算を作成し、年間を通じて更新する必要がありました。その会計担当者は年内に辞任し、私は友人のチャーリーを説得してその役割を引き受けてもらいました。私たちの最初の仕事は、2022年度の予算を作成することでした。調査の結果、前の会計担当者は、手入力した数字を足し合わせるだけの単純なスプレッドシートを使用していたことがわかりました。私は数字を再入力するのが嫌いです（それがコンピューターの役割です！）。そこで、より良い方法をブレインストーミングし始めました。
最初の質問は、予算の目的についてでした。FLRCは年間約20レースを開催しており、そのほとんどで若干の利益、あるいは少なくとも損益分岐点に達すると見込んでいます。クラブの収入の大部分は、2つの大きなレースから得られ、運営費、慈善事業、貯蓄に充てられます。レースディレクターの支出額は、クラブ全体にとって実行可能な範囲内であれば、厳密には問題ではないことに気づきました。過去には、レースディレクターが軽率な計画を立て、大きな損失を被るという問題が時折発生しました。そのため、レースディレクターが想定外の支出を検討する際に、予算が適切な範囲に収まるように、ガイドラインとなるようにしたいと考えました。
当初の考えは、クラブの会計に使用しているQuickBooks Onlineに予算作成機能があるかもしれないということでした。確かに機能はありましたが、2つの理由から実現には至りませんでした。まず、そのインターフェースには、翌年の予算見積もりに役立つ重要な履歴情報がありませんでした。次に、予算は様々な方法でエクスポートできましたが、レースディレクターが収入と支出を記録する際にレースの状況を確認できるように、定期的にエクスポートする必要がありました。そこで私はGoogleスプレッドシートに注目しました。どのレースディレクターでも、リンクをクリックするだけで自分のレースの予算と最新の数字を確認できるようにしたかったのです。
レースの現在の数字を確認するための重要なレポートは、QuickBooks Onlineの活動報告書です。各レースはクラスであり、エクスポートすると列に変換され、各行は会計チャートのアカウント（登録、賞、タイミングなど）です。表形式のデータは見やすいのですが、レースとアカウントの数が多いため、縦横にスクロールしなければならず、どの形式でも読みにくいです。また、各レースを個別に表示したかったので、レースディレクターに膨大なPDFやスプレッドシートからレースデータを解析してもらうのは無理でした。
その時、私は次のひらめきを得ました。私はスプレッドシートの専門家でもプログラマーでもありませんが、抽象化は理解しています。プログラミングでは、変数を使ってデータのハードコーディング（入力内容に応じて変化する）を避けます。スプレッドシートでは、セル参照を使ってセルや列myVariableの内容を参照します。さらに素晴らしいことに、1つのスプレッドシートに複数のシートを内部で持つことができ、あるシートのセル範囲を別のシートから参照できます。C32M
突然、構築する必要のあるスプレッドシートの構造が目に浮かびました。QuickBooks Onlineから直接エクスポートした活動明細書を1つのシートにまとめ、そこから各レースのデータを抽出するための別々のシートを作成します。さらに、2016年以降の各年の活動明細書をインポートすれば、各レースでそれらの年の履歴データも表示できるようになります。この履歴データは実際の予算編成プロセスに活用され、チャーリーと私は前年（および全体の平均）のデータを参照しながら、翌年の収入と支出を予測できるようになります。
魔法の公式の構築
難しかったのは、自分のビジョンを実現することでした。あるシートからデータを取得して別のシートに表示できることはわかっていましたが、やり方がわかりませんでした。すぐにハードコードされた概念実証を思いつきました。これは良いことでした。なぜなら、それを抽象化する方法を見つけるのにかなり時間がかかったからです。最終的には、Web検索を何度も行い、例を徹底的に調べ、ヘルプ > 関数一覧でGoogleスプレッドシートの機能を確認した結果、この数式にたどり着きました。
=IFERROR(HLOOKUP($A$2,INDIRECT("'"&E$4&" Overall'!$A$5:$X$250"), Utility!$A5, FALSE))
説明するには時間がかかりますが、ここで魔法が起こります。
IFERROR:このように数式全体をIFERROR関数で囲むと、数式の残りの部分が失敗したときのようなエラーではなく、空白が表示されます#N/A。これは通常、かなり昔の年にデータがないために発生します。HLOOKUP:このHLOOKUP関数は、キーを範囲検索し、見つかった列のセルの値を返すという、大部分の処理を実行します。この関数を適切に設定するのに最も時間がかかりました。$A$2最初のタスクは、の検索キーを指定することでした。HLOOKUP私の目標は特定のレースの情報を調べることだったので、最初はこの場所にレース名をハードコードして、HLOOKUP年間活動明細書で見つけられるようにしました。これはうまくいきましたが、各レースシートの数式をカスタマイズする必要がありました。可能ですが、面倒で不安定でした。代わりに、レース名をセルに入力しA2、絶対参照を使用して$A$2検索することで、一種の変数を作成しました。 ($文字により、数式を他のセルにコピーしても、指定した列と行が変更されないことが保証されます。) レース用に新しいシートを作成したいときは、そのセルに新しいレース名を入力するだけで、数式が正しいデータを検索できるようになりました。INDIRECT次に必要な情報は、HLOOKUP検索範囲です。2023 Overall!A5:X250今年の活動報告書にある必要なデータを参照するようにハードコードすることはできましたが、それを特定の年に抽象化するのは困難でした。（QuickBooks Onlineのエクスポートでは最初の4行が結合セルとして取り込まれるため、A5ではなくから開始する必要がありました 。）関数は文字列で指定されたセル参照を返すため、これが鍵となりました。正確さが求められたため、文字列の構築には長い時間がかかりました。A1A1INDIRECT"'"&E$4&" Overall'!$A$5:$X$250":それぞれの履歴列は、関連する年のシートからデータを検索する必要がありましたが、年をハードコーディングすることは避けたかったのです。私の解決策は、を使用して'"&E$4&" Overall'セルから年を引き出しE4、それを単語にマッシュアップしてOverall正しいシートのタイトルを指定することでした。Eは相対的であるため( が付いていない$)、数式を他の列にコピーしても、行から年が検索され続けました4( が付いていたので絶対的でした$)。 の絶対範囲を指定するのは!$A$5:$X$250簡単でした。一番難しかったのは、二重ハッシュ記号と一重ハッシュ記号の組み合わせを理解し、&(文字列連結演算子) を適切な場所に置くことでした。試行錯誤におそらく 1 時間かかりました。今ではなぜそれが機能するのか理解していますが、機能するまでは理解していませんでした。私は恥知らずにも例を盗む人間ですが、そのうちの 1 つは、これが正しいアプローチだと私を確信させたに違いありません。Utility!$A5:この Utility シートとは何でしょうか。これには数字の列が 1 つ含まれており、 では 1A1、 では 2A2、などとなり、 では 250 までとなりますA250。これが存在するのは、返される値の行番号であるインデックスを指定する方法が必要だったのですHLOOKUPが、セルの内容を参照せずに数字を増分する方法が他に思いつかなかったからです。ややこしいことに、5検索範囲は であるため、インデックスは row から始まる必要がありますA5:X250。つまり、 row のインデックスは51、6row のインデックスは 2、などになる必要があります。これは頭を悩ませる作業で、正直なところ、正しい結果が返されるまで特定のインデックス番号を挿入する試行錯誤を通してようやく解決できました。 の$前の によりA、その列に焦点が当てられるように絶対的になりますが、前にドル記号がないため、5相対的になり、必要に応じて増分できます。FALSE最後に、オプションFALSEはHLOOKUP検索対象の行がソートされていないことを指定します。これが何を意味するのかは分かりませんが、私のデータはソートされていないので、そのままにしました。
この数式の素晴らしいところは、必要なデータをすべて一気に取り込めることです。まず、レース名をA2と列を2016からまで入力したシートを作成します2023。次に、 の下の最初のセルに数式を入力し2023、それを右にコピーして各年の最初の行を埋め、最後に下にコピーして残りの行を埋めます。
(スプレッドシートをあまり使わない人のために、数式をコピーするためのコツがあります。Google Sheets と Excel では、セルの右下隅のハンドルをドラッグします。Numbers では、セルの目的の側にある黄色のハンドルをドラッグします。これにより、数式がコピー先のセルにコピーされ、相対的な行と列の参照が適切に増加します。セル参照を増加せずに、あるセルから別のセルに数式を入力したい場合は、行と列の参照の両方に をプレフィックスとして付けるか、$数式を含むセルではなく、数式のテキストをコピーして貼り付ける必要があります。)
データのフィルタリングとフォーマット
この記事の冒頭にある活動明細書のスクリーンショットをよくご覧になった方は、多くの口座の末尾に(deleted)や が付いていることにお気づきかもしれません(deleting)(deleted)。これは、QuickBooks Online の特殊な仕様によるもので、多くの手間がかかりました。その理由は次のとおりです。
会計帳簿は時間の経過とともに変化し、会計担当者や経理担当者が勘定科目を追加したり削除したりしています。中には、予算ツールで初めて取り込んだ2016年以降、使われていないものもあります。しかし、私の魔法の公式を考えてみると、セル参照を使って計算していることがわかります。食料費勘定科目を、87各年度の全体シートのどの行に配置する必要があります。QuickBooks Onlineの標準的な活動明細書では、活動のある行のみが表示されますが、私がそのようなレポートを作成すると、どの年度に食料費勘定科目がどの行に配置されるかを予測することはできません。
幸いなことに、QuickBooks Onlineにはレポートにすべての口座を表示するオプションがあり、これを使うと、取引明細レポートの各カテゴリーが常に同じ行に表示されます。これは、QuickBooks Onlineが口座を削除することはなく、削除済みとしてマークして非表示にするだけなので可能です。
しかし、生データに不要な行が散らばっていることで、別の問題が発生しています。ほとんどのレースでは、最大でも10～20のアカウントで活動しているのに、活動報告書にはなんと204行も含まれています。これは表示上、深刻な問題です。
なぜこんなにたくさんのアカウントが必要なのか？今年に入って、通常の QuickBooks Online アカウントから、非営利団体向けに割引価格でテクノロジーを提供している TechSoup 経由のアカウントに切り替えることで年間 800 ドル以上も節約できることに気づいた。何年も前に Jeff Porten が TidBITS で記事を書いていたので、私は TechSoup のことは何となく知っていた (2018 年 10 月 18 日の記事“TechSoup: 非営利団体向けのテクノロジーを大幅割引で手に入れよう”参照) のだが、年間 75 ドルの QuickBooks Online 購読が提供されているとは知らなかった。既存の QuickBooks Online 購読を TechSoup アカウントに切り替えるには、新しい「会社」を設立し、データを移行し、そして以前の購読をキャンセルする必要があった。これは複雑でストレスフルな作業で、約束された返金を受けるために Intuit サポートの助けも必要だったが、最終的にはうまくいった。
残念ながら、QuickBooks Onlineで新しい会社を設立すると、バックアップからデータをすぐにインポートしているにもかかわらず、定型的な勘定科目表が作成されます。これらの定型勘定科目はすべて削除済みとしてマークされ、活動報告書を乱雑にする削除済み勘定科目の数が大幅に増加しました。これらの勘定科目は全く活動していなかったので、完全に消したいと思っていましたが、完全に削除するのに最も近い方法は、それらを統合することでした。理論上は、すべての勘定科目を1つの勘定科目に統合し、削除済みとしてマークすることもできました。しかし実際には、これは面倒でエラーが発生しやすく、たまにしか機能しなかったため、諦めて、見苦しい活動報告書を我慢することにしました。
以前の予算ツールでは、各レースを手作業で確認し、関連データを含まない行を非表示にしていましたが、これは面倒な作業であり、非表示にしていた未使用のアカウントでレースがアクティビティを発生させた際に混乱を招くことがありました。QuickBooks Onlineの定型勘定科目表から削除された余分な行をすべて処理できるように予算ツールをリファクタリングしていたところ、より良い方法を思いつきました。
Googleスプレッドシートには、行の表示/非表示を切り替えるフィルタリング機能があります。そこで、2016年から2023年までの各年の数値を合計する「合計」列を作成しました。合計値自体は気にしませんが、その列にフィルタ（「データ」>「フィルタを作成」を選択）を作成し、ゼロ以外の行だけを表示するようにしました。すると、不要なデータがすべて消えました。
結局、一つ欠点が見つかりました。私のシートにはすべて「収入」と「支出」のヘッダー行があります。「収入」のヘッダー行はすべてのデータの上に表示されるためフィルターの影響を受けませんが、「支出」はシートの中央にあり、関連するデータもないため、フィルターによって非表示になってしまいます。そのため、ヘッダー行を非表示にしないよう、列の1つに1を入力して表示しています。もっと良い解決策があるかもしれません。
青色の「収入」と「支出」のヘッダー行に加えて、灰色の「総収入」と「総支出」の行、そして黒色の「純収入」の行があることに気づくでしょう。シートを見やすくするために、これらの行の書式をそれぞれ異なるものにしたいのです。当初は各シートごとに手動で書式を設定していましたが、これは面倒ですが、Googleスプレッドシートには「編集」＞「形式を選択して貼り付け」＞「書式のみ」コマンドがあるので、思ったほど複雑ではありません。必要な書式に設定されたセルをコピーし、行全体を選択してCommand+Option+Vを押すことで、コピーしたセルの書式のみを貼り付けることができました。
それでも、抽象化を追求する中で、もっと良い方法があることに気づきました。Googleスプレッドシートには条件付き書式設定機能があり、数式に基づいてセル範囲の書式を設定できます。私は長年、負の数値を赤く表示するためにこの機能を使ってきましたが、特定の行の書式設定ルールも設定できることに気付きました。ヒント：書式 > 条件付き書式を選択すると、現在選択されているセルに適用されるルールのみが表示されます。そのため、このコマンドを選択しても何も表示されない場合は、対象となる範囲内のセルを選択してください。
これをうまく動作させるコツは、式にありました。. は=REGEXMATCH($A2:$A210,"Net Revenue")、範囲内のテキストを検索する正規表現（grep）関数です。このプロジェクトの他の多くの部分と同様に、うまく動作させるにはいくつかの実験が必要でした。なぜ一部のルールが奇妙な範囲になってしまったのかは分かりませんが、表示されているルールをより単純なものに変更したところ、問題なくREGEXMATCH動作しました。A4:N211
これを終えたら、チャーリーと私が予算計算に使えるように、いくつか列を追加しました。スクリーンショットのオレンジ色の列です。これらの列は、収入と支出を簡単な計算で合計し、純収入を計算します。メモ欄は、なぜその数字を見積もったのかを思い出すのに役立ちます。また、時には、異なる見積もりがレースの収益にどのような影響を与えるかを事前に確認するためのメモ帳の列も作成しました。これは、予算と同じ計算が書かれたオレンジ色の列です。
すべてのレース、、を合わせて 22 のシートがあるため、非表示の行や書式設定などを抽象化しようとしてきましたGeneral。Total変更を加えることにした場合、22 回変更する必要があり、面倒です。前回のメジャー リビジョンでは、非表示の行と書式設定を抽象化したときに、各シートを再作成する必要がありました。これは簡単でした。シートを 1 つ複製し、で表示名とレース名を変更しA2、フィルターを更新しました。しかし、それを行った後、さらに 22 回の変更を加える必要が生じました。たとえば、2024来年は各シートに列を追加する必要があります。理想的な世界では、抽象化機能はスプレッドシート内の複数のシートに簡単に適用できます。おそらくそれは可能で、私がまだ発見していないだけです。
通常の使用
私たちが予算ツールを日常的にどのように使用しているかについて、いくつかメモを残しておきたいと思います。
まず、経理担当者がQuickBooks Onlineに1ヶ月分のデータをすべて入力した後、すべての行を含む活動報告書を作成し、Excel形式にエクスポートします。これをGoogleスプレッドシートにインポートして、特定のシートの内容を上書きすることも可能ですが、Excelでエクスポートを開き、データをコピーしてGoogleスプレッドシートに切り替え、該当する年度のシートに貼り付ける方が簡単です。
(QuickBooks Online と Google Sheets 間でデータを自動的に移動するオンライン統合はありますか? 最も近いのは Skyvia ですが、Intuit ではレポートに必要な API アクセスが提供されていません。)
Googleスプレッドシートに新しいデータが入ると、すべて（本当にすべて）が瞬時に更新されるので、スプレッドシートをざっと確認して各レースの状況を確認できます。予算見積もりと大きく異なる数値がある場合は、その理由を突き止めます（たいていは単なる分類ミスです）。
唯一の問題は、以前使用していなかったアカウントのデータを取得する際に発生する競合です。フィルターを更新するために、すべてを選択してから再度ゼロの選択を解除する必要がありました。今のところ、そのようなことは一度しか発生していません。
レースディレクターとデータを共有するにあたって、最後にもう1つGoogleスプレッドシートの便利なテクニックをご紹介します。Googleスプレッドシートの右上にある青い「共有」ボタンをクリックすると、Total私たちの場合、最初のシートへのリンクが表示されます。これはこれで悪くありませんが、レースシートに直接リンクできる方が便利です。そのためには、A1特定のシートのセルをControlキーを押しながらクリックし、下部にある「その他のセルアクションを表示」＞「このセルへのリンクを取得」を選択します。これで、適切なカスタムリンクがクリップボードにコピーされ、他の場所で共有できるようになります。
誰かがうっかり、あるいは悪意を持って、私が丹念に作成したシートや数式を削除したり編集したりすることを心配しているのではないかと思われたかもしれません。私の経験では、ほとんどの人は共同作業で作成したドキュメントに恐怖を感じ、何も触ろうとしません。しかし、共有スプレッドシートを保護するには、次のような3つの方法があります。
- 共有アクセス：私は強引な手段を選び、私とチャーリー以外の全員のアクセスをコメント権限に制限することにしました。変更したい場合は変更可能ですが、承認または拒否は私が行います。この点はまだ検討されていません。
- シートと範囲の保護：よりきめ細かな制御が必要な場合は、「データ」>「シートと範囲の保護」オプションを使用して、スプレッドシートの特定の部分に対して誰が何を実行できるかを正確に指定できます。今のところ、このようなきめ細かな制御は必要ありません。
- 以前のバージョン： Googleスプレッドシートはすべての変更履歴を完全に保持するため、必要に応じてスプレッドシート全体を以前のバージョンに簡単に戻すことができます。誰かが望ましくない変更を加えた場合でも、簡単に復元できます。唯一の問題は、すぐに気づかず、後から正当な変更が多数加えられてしまうことです。
この話はすべて私のプロジェクトに特化していることは承知していますが、現代のスプレッドシートは単なる計算以上のことができるということをご理解いただければ幸いです。そして、皆さん自身の仕事で同様のテクニックを活用するためのヒントも得られるかもしれません。スプレッドシートは本格的なプログラミング環境とそれほど変わりません。実際、行や列の枠を超えたマクロやスクリプトをサポートしているケースも少なくありません。しかし、それはまた別の機会にお話ししましょう。