The 10 Spreadsheet Formulas That Handle 90% of Real Work \u2014 CSV-X.com

私が7年間スプレッドシートを誤って使っていたことに気付いた日を今でも覚えています。それは2019年のことです。私はクリーブランドの中規模物流会社の狭い会議室に座っていて、彼らのオペレーションマネージャーが、今月40回目になるであろう3つの異なるExcelファイルのデータを手動でコピー＆ペーストしている様子を見ていました。各ペースト操作に約90秒かかっていました。彼はおおよそ月に200回この作業を行っていました。それは年間300時間、ほぼ2ヶ月分の作業時間であり、1つの数式で自動化できるタスクに費やされていました。

私の名前はマーカス・チェンです。過去14年間、ビジネスシステムアナリストとして、フォーチュン500企業から5人の従業員を持つスタートアップまで、さまざまな方々と仕事をしてきました。キャリアの中で3,000以上のスプレッドシートをレビューしてきましたが、ほとんどの人が利用可能な機能の約5%しか使用していないと断言できます。しかし、すべての400以上の関数をExcelやGoogle Sheetsでマスターする必要はありません。正確に10個をマスターすれば良いのです。

これら10個の数式が、実際のビジネス作業の約90%を処理します。私は特殊な財務モデリングや統計分析について話しているのではありません。私は日々の業務、すなわち在庫管理、手数料計算、プロジェクトのタイムライン管理、売上データの分析、口座の照合について話しています。実際に請求書を支払うための作業です。

過去5年間で、私は23の業界で847人の専門家を訓練し、彼らの数式使用パターンを追跡してきました。そのデータは驚くほど一貫しています：これらの10個の数式が、生産スプレッドシートの全数式使用の87-93%を占めています。それ以外のものは、特化した作業か、より頻繁には複雑な数式を使っているが単純なもので済む場合です。

基礎：VLOOKUP（またはその現代版、XLOOKUP）

キャリアを変えた数式から始めましょう。VLOOKUPはスプレッドシート作業のスイスアーミーナイフであり、この記事から唯一学ぶべき数式があるとしたら、この数式です。私の追跡データによれば、VLOOKUPとその変種はビジネス用スプレッドシートの全数式使用の約31%を占めています。

VLOOKUPの機能は次の通りです：1つのテーブルから値を検索し、別の列から対応する値を返します。簡単に聞こえますが、これは多くの実世界の問題を解決します。顧客名をアカウント番号に照合する必要がありますか？ VLOOKUPです。マスタープロダクトリストから価格データを引き出したいですか？ VLOOKUPです。異なるシステムからの2つのレポートを照合しようとしていますか？ ご想像の通りです。

基本的な構文は次の通りです： =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

昨年働いたクライアントからの実際の例を挙げましょう。彼らは4,200のSKUを持つ卸売流通業を運営していました。毎週、商品コードはあるが商品名やカテゴリーはない売上データをPOSシステムから受け取っていました。誰かがマスタープロダクトリストで各コードを手動で検索して情報を入力していました。これには毎週約6時間かかっていました。

VLOOKUPを使って、これをゼロ時間に減らしました。この数式は自動的に各商品コードをマスタリストに照合し、名前、カテゴリー、サプライヤ、コストデータを引き出します。この数式は次のようになっていました：=VLOOKUP(A2,ProductMaster!A:E,2,FALSE)。この1つの数式が彼らに年間312時間の節約をもたらしました。

現在、Microsoft 365またはExcel 2021を使用している場合は、VLOOKUPのスマートな若い兄弟であるXLOOKUPを利用できます。これはより柔軟で、読みやすく、列を挿入しても壊れません。構文もさらに簡単です： =XLOOKUP(lookup_value, lookup_array, return_array)。アクセスできる場合はXLOOKUPを学ぶことをお勧めしますが、VLOOKUPも完璧に動作し、より普遍的に互換性があります。

VLOOKUPでよく見かける最も一般的な間違いは、4番目の引数をFALSE（または0）に設定することを忘れることです。この引数は、正確な一致が必要か近似一致が必要かを制御します。ビジネスの95%の使用ケースでは、正確な一致が必要なのでFALSEを使用します。TRUEを使うかこの引数を省略すると、キャッチしづらい非常に不正確な結果が得られる可能性があります。

メインの作業：SUMIFとSUMIFS

VLOOKUPがスイスアーミーナイフなら、SUMIFはスプレッドシート数式の信頼できるピックアップトラックです。私のデータセットでは約18%の数式使用を占めており、理由は明白です：特定の基準を満たすすべての数値を合計するという非常に一般的な問題を解決します。

23の業界にわたって3,000以上のスプレッドシートを分析した結果、パターンは否定できないものでした：10個の数式が実際のビジネス作業の90%を処理します。他は特化または過剰に複雑です。

基本的なSUMIFの構文は次の通りです： =SUMIF(range, criteria, [sum_range])

私が常に遭遇するシナリオがあります：500の取引がある売上レポートがあり、北東地区の総売上を知る必要があります。SUMIFがなければ、手動で数値を足す（エラーが発生しやすく、時間がかかる）か、データを別の場所にフィルタリングしてコピーする（混乱しやすく、維持が難しい）ことになります。SUMIFを使えば、1つの数式で済みます：=SUMIF(B:B,"Northeast",C:C)。

しかし、真の力はSUMIFS（Sに注意）にあります。これは複数の基準を使用できます。私が働いた製造クライアントは、製品ラインごと、四半期ごと、施設ごとの総生産コストを計算する必要がありました。彼らには8,700行の生産データを含むスプレッドシートがありました。SUMIFSの前には、彼らは必要に応じてあらゆる組み合わせのピボットテーブルを作成していました。SUMIFSを使えば、必要に応じてあらゆる組み合わせを取得できます。

この数式は次のようになっていました：=SUMIFS(E:E,A:A,"Widget A",B:B,"Q2",C:C,"Facility 3")。この1つの数式は、フィルタリング、コピー、計算という45分のプロセスを置き換えました。彼らは月に約30回この計算を実行する必要があったため、月に約22時間の作業を節約できるのです。

私が何年もかけて理解したプロのヒントがあります：基準にワイルドカードを使用できます。「PRO」で始まるすべての製品を合計する必要がありますか？ =SUMIF(A:A,"PRO*",B:B)を使います。アスタリスクは任意の文字にマッチするワイルドカードです。これは不整合なデータ入力を扱う際に非常に便利です。

もう1つの強力な技法は、基準にハードコーディングする代わりにセル参照を使用することです。=SUMIF(A:A,"Northeast",B:B)の代わりに、=SUMIF(A:A,D2,B:B)を使用します。ここでD2には「Northeast」が含まれています。これにより、スプレッドシートが動的になり、更新が簡単になります。この単一の変更により、分析基準を定期的に変更する必要がある組織でスプレッドシートの保守時間が40%削減されるのを見てきました。

意思決定者：IF文

IF文はスプレッドシートの論理エンジンであり、数式使用の約16%を占めています。条件に基づいて意思決定を行うことができ、これはほぼすべてのビジネスプロセスの基本です。基本構文は美しくシンプルです： =IF(logical_test, value_if_true, value_if_false)

しかし、そのシンプルさにだまされないでください。私はIF文が基本的な手数料計算から複雑な承認ワークフローまでの問題を解決するのを見てきました。私のお気に入りの例の一つは、2021年にコンサルティングを行った不動産会社から来ています。彼らは、階層型構造に基づいて代理店の手数料を計算する必要がありました：$500K未満の販売に対して3%、$500Kから$1Mの売上に対して3.5%、$1M以上の販売に対して4%でした。

その数式は次の通りです：=IF(B2<500000,B2*0.03,IF(B2<1000000,B2*0.035,B2*0.04))。このネストされたIF文は、すべての3つの階層を自動的に処理しました。これを導入する前は、彼らの会計チームが手動で各取引を分類し、正しいレートを適用していました。月に340の取引があったため、これは約8時間の作業を要していました。この数式はゼロに削減しました。

私が学んだ重要なことがあります：ほとんどの人は単純なIF文で止まりますが、実際の力はIFと他の関数を組み合わせることから生まれます。たとえば、IFにANDやORを組み合わせれば、複数の条件をテストできます。「$10,000を超え、なおかつ新規顧客からの注文」をフラグ付けする必要がありますか？ =IF(AND(B2>10000,C2="新規"),"優先","標準")です。

私は、顧客層（プレミアム、スタンダード、ベーシック）、問題の深刻度（高、中、低）、および提出からの時間に基づいてサポートチケットを優先する必要があるカスタマーサービスチームと仕事をしました。私たちは、IF文をネストしてAND関数と組み合わせた数式を構築しました。