Spreadsheet Formulas Cheat Sheet: The 20 You Actually Need — csv-x.com

私はまだ、7年間スプレッドシートを間違って使っていたことに気づいた瞬間を覚えています。それは2019年で、中規模の物流会社で私のチームと一緒に会議室に座っており、CFOが四半期報告書を生成するために3つの異なるExcelファイル間でデータを手動でコピーペーストしているのを見ていました。そのプロセスには彼女に4時間かかりました。彼女の時給150ドルの4時間が、誰もVLOOKUPを学ぶことに興味を持たなかったため、毎四半期必要でした。

その日は私のためにすべてを変えました。私はサラ・チェンです。過去11年間、私は財務業務コンサルタントとして、200社以上と協力してデータワークフローを最適化してきました。私は、スプレッドシートの作業に数え切れないほどの時間を無駄にしている企業を見てきました。それらはほんの数式で自動化できるものでした。皮肉なことに、多くの人が「Excelが得意である」ためには何百もの関数をマスターしなければならないと思っていますが、そうではありません。私の経験では、20の数式が現実のビジネスシナリオの約95%を処理します。

これは、これまでに作成されたすべてのスプレッドシート関数の包括的な百科事典ではありません。これは、私がクライアントのために推定12,000時間以上の手作業を節約するために使用してきた戦闘テスト済みの武器庫です。これらは、帳簿を締める、販売データを分析する、または在庫数が一致しない理由を見つけようとしているときに実際に重要な数式です。

基礎：すべてを支える基本的な算術

豪華なことに行く前に、私がこれまでに構築したすべての財務モデルの骨格を形成する3つの数式について話しましょう。これらは明らかに思えるかもしれませんが、私は上級アナリストが会社に実際のお金を失わせるような方法でこれらをめちゃくちゃにするのを見たことがあります。

SUMは作業馬です。構文は非常にシンプルです：=SUM(A1:A10) はその範囲内のすべてを加算します。しかし、ほとんどの人が知らないことは、SUMを使って複数の非連続的な範囲を使用できるということです。=SUM(A1:A10, C1:C10, E5) は完璧に機能し、個別のセル参照を書き出すよりもはるかにクリーンです。私はかつて、誰かがSUMを使用する代わりに=A1+A2+A3+A4+A5+A6+A7+A8+A9+A10と書いた財務モデルを監査しました。行を挿入する必要があるとき、数式は壊れました。そのような人にならないでください。

AVERAGEも同様に簡単ですが、驚くほど強力です。=AVERAGE(B2:B50) はデータセットの平均を提供します。ここでの重要な洞察は、AVERAGEを使用する場合とAVERAGEIFを使用する場合を理解することです（これは後で説明します）。私のコンサルティング作業で、私は人々がAVERAGEを使う場合の約60%で、実際には条件付き平均が必要であることがわかりました。平均売上を計算する場合で、ゼロまたは外れ値を除外したい場合は、普通のAVERAGEは誤解を招く結果をもたらします。

COUNTとCOUNTAは異なる人格を持つ双子です。COUNTは数値を持つセルのみをカウントし、COUNTAは非空セルをカウントします。この区別は、あなたが思う以上に重要です。データ品質を監査しているとき、私は=COUNTA(A:A)-COUNT(A:A)を使用して、A列の何セルがテキストを含んでいるかを瞬時に見つけます。その数が予想以上に高い場合、私はデータ入力の問題があることを知っています。この単純なチェックで、私のクライアントに何千ドルもの会計エラーを見つけました。

検索関数：s

もし、私が誰かに3つの数式だけを教えられるとしたら、そのうちの2つは検索関数です。これらは、データを手動で30分間マッチさせるのと、瞬時にそれを行う数式を書くのに30秒かかるかの違いです。

"200以上の企業での経験から、20の数式が現実のビジネスシナリオの約95%を処理します。ほとんどの人が決して使用しない何百もの関数をマスターしようとして時間を無駄にしています."

VLOOKUPは古典的なもので、新しい代替手段があっても、まだ驚くほど便利です。構文は=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])です。こちらが先月の実際の例です：顧客名を取引IDに一致させる必要がある5,000件の顧客記録を1つのシートに、5,000件の取引記録を別のシートに持っているクライアントがいました。=VLOOKUP(A2, Customers!A:D, 3, FALSE) は手動で数時間かかるものを3秒で実現しました。

皆が間違える重要な点：その4番目の引数です。FALSEは完全一致を意味し、TRUEは近似一致を意味します。ビジネスの使用例の99%では、FALSEが必要です。誰かがそれをTRUEにしたままにしたために、または省略したために（デフォルトでTRUE）、おおよその一致が得られて、完全一致が得られなかったために、財務報告書全体が破損するのを見たことがあります。

XLOOKUPは現代の代替で、正直なところ、ほぼすべての面で優れています。=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])は複雑に聞こえますが、実際にはクリーンです。最大の特徴は？VLOOKUPができない左側を探すことができます。私は最近、クライアントの報告システム全体をVLOOKUPからXLOOKUPに移行し、XLOOKUPが欠損データをより優雅に処理するため、約40%の数式エラーを削減しました。

INDEX-MATCHはパワーユーザーの選択です。実際には2つの数式を組み合わせたものです：=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))。このコンボはVLOOKUPよりも柔軟で、大規模なデータセットではより高速です。50,000行を超えるスプレッドシートを扱っているときは、INDEX-MATCHが顕著に優れたパフォーマンスを発揮します。学習曲線は急ですが、一度理解すれば、決して戻ることはありません。私は、同時に2つの基準に基づいて値を参照する必要がある複雑な財務モデルにこれを使用しています。

条件付き論理：スプレッドシートを考えさせる

ここでは、スプレッドシートが電卓から意思決定ツールに変わります。条件付き数式を使用すると、データに論理を組み込むことができ、これは自動化された報告に絶対に必要です。

IFはスプレッドシート自動化への最初のステップです。=IF(logical_test, value_if_true, value_if_false)を使うことで、分岐する論理を作成できます。シンプルな例：=IF(B2>1000, "高価値", "標準")は購入額に基づいて顧客を分類します。しかし、真の力はIFをネストすることから来ます。私は、複数の基準に基づいて営業担当者ボーナスを自動的に決定する5つのネストされたIF文を持つコミッション計算機を構築しました。鍵はそれらを読みやすく保つことです—適切なインデントとコメントを使用してください。

IFSは新しいスプレッドシートバージョンで使用可能なネストされたIFのクリーンな代替です。=IF(A1>90, "A", IF(A1>80, "B", IF(A1>70, "C", "F")))の代わりに、=IFS(A1>90, "A", A1>80, "B", A1>70, "C", TRUE, "F")と書きます。これはより読みやすく、括弧のエラーが少なくなります。私は約3年前にIFSに切り替え、数式のデバッグ時間が半分に減りました。

SUMIFとSUMIFSは条件付き合計の強力な数式です。=SUMIF(range, criteria, [sum_range])は条件を満たす値を合計します。=SUMIFSはこれを複数の条件に拡張します。実際の例として、小売業のクライアントが地域と製品カテゴリーごとの総売上を計算する必要がある場合、=SUMIFS(Sales!C:C, Sales!A:A, "西", Sales!B:B, "電子機器")は彼らに正確にそれを提供しました。SUMIFSを学ぶ前は、彼らはすべてにピボットテーブルを使用しており、それはオーバーキルで更新が遅かったのです。

COUNTIFとCOUNTIFSは同様の方法で機能しますが、合計ではなくカウントします。私はこれらをデータ品質チェックのために常に使用しています。=COUNTIF(A:A, "エラー")は、A列の何セルが「エラー」という言葉を含んでいるかを教えてくれます。=COUNTIFS(A:A, ">100", B:B, "<50")は、A列が100を超え、かつB列が50未満の行をカウントします。これらの数式は、手動データ検証の無数の時間を節約しました。