Microsoft が新しい Excel 関数をリリースするたびに興奮しますが、締め切りに追われているときは、古典的な関数に手を伸ばします。式が常に期待どおりに機能することがわかっていると、ある種の安心感があります。
これらの「古風な」ツールを学ぶことで、私はより良いオタクになりました。これらのロジックは透過的であるため、データの基礎となる構造を理解する必要があり、Power Query などのより高度なシステムに飛び込むことがはるかに直感的で、威圧感が少なくなりました。
ここでは、すべてのパワー ユーザーがポケットに入れておくべき昔ながらの Excel 関数を紹介します。
SUMPRODUCT: 数学のスイスアーミーナイフ
現代の煩雑な作業なしで配列を処理する
最新の Excel で条件付き計算を構築しようとした結果、複数のヘルパー列や広大な動的配列数式が作成されてしまったことはありませんか?そこで SUMPRODUCT が再び会話に加わります。結果がシート全体に広がることなく、単一のステップでロジックと数学を組み合わせます。
Excel ではデータをフィルター処理して集計するためのより現代的な方法が提供されていますが、SUMPRODUCT ではすべてが 1 つの数式に含まれています。流出や再構築はなく、「流出したものはどこへ行ったのか?」ということもありません。問題。
これを特に強力にしているのは、ロジックの処理方法です。 “Department = Design” のような条件では TRUE/FALSE 配列が生成され、Excel は算術コンテキストで暗黙的に 1 と 0 に強制します。この静かな動作により、単純な比較が柔軟なフィルタリング ロジックに変わります。
SUMPRODUCT は現代的な意味ではエレガントではありませんが、安定していてコンパクトで、非常に壊れにくいです。
SUBTOTAL: フィルターを考慮した合計
行が消えても結果を正確に保つ
SUBTOTAL は、ユーザーが実際に見ているものに応答するため、信頼できるレポート ツールです。
通常の SUM はフィルターを無視します。これは静的モデルでは問題ありませんが、対話型レポートでは経験豊富な Excel のプロでもつまずくのを見たことがあります。 SUBTOTAL は、表示されている行のみを自動的に計算することでこの問題を修正します。
それが、ダッシュボードや運用スプレッドシートで今でも好まれている理由です。ユーザーの意図に合わせて計算を行うことができます。フィルターによって画面上の内容が変更されると、数値も変更されるはずです。SUBTOTAL は追加のロジックなしでそれを保証します。
Excel では SUBTOTAL を使用しないでください。代わりに AGGREGATE を使用してください
より柔軟に小計を生成します。
INDEX と MATCH: 柔軟なルックアップ デュオ
制御性と透明性の点で最も優れた選択肢
経験豊富な Excel ユーザーにルックアップをどのように実行しているかを尋ねると、INDEX と MATCH という同じ答えが返ってくることがよくあります。 XLOOKUP の存在を知らないからではなく、この組み合わせを何年も使用しており、今でも期待どおりに動作するからです。
MATCH は値の位置を検索し、INDEX はその位置から対応する値を取得します。この分離は、他人のロジックをデバッグすることになる大規模なスプレッドシートや継承されたスプレッドシートでは特に重要です。
新しい機能が常に置き換えられるわけではないということはよく知られています。 INDEX と MATCH はもはや単なる技術的な選択ではありません。これらはスプレッドシートでの長年の作業を通じて蓄積された筋肉の記憶であり、考えなくてもすでに確実に仕事を行っています。
最新の機能はシンプルさを優先します。ただし、INDEX と MATCH は制御を優先し、その親しみやすさとともに制御を維持します。
選択: ネストされたロジックを使用しない単純な分岐
小規模モデルにおける迅速な論理的オプション
CHOOSE は、予想以上に多くのロジックを処理するため、私のお気に入りの関数の 1 つです。条件を評価する代わりに、位置に基づいて出力を選択するだけです。
CHOOSE が興味深いのは、ロジックは固定されているものの重要な実際の運用モデルに常に現れることです。コミッション階層、SLA 目標、給与ボーナス、価格設定ルールなど、小規模モデルにおける単純なルックアップ テーブルや基本的な条件付きマッピングを置き換えることがよくあります。このような場合、安定したルールが式に直接埋め込まれます。
これは拡張性はありませんが、適切な状況においては、依然として最も迅速な考え方の 1 つです。
テキスト: 数式内の値の書式設定
数値を読み取り可能な文字列に変換する
私はデータの見た目にこだわる人間なので、完全に正しい日付が 5 桁のシリアル番号に変わってしまう乱雑な連結ほど最悪なものはありません。これは Excel の最も厄介な習慣の 1 つです。値をテキストと結合すると、セルの書式設定が削除されてしまいます。
TEXT は、生データをインターセプトして、数式内で直接特定の形式に強制できるため、生成された出力をクリーンに保つために私が頼りにしています。細かいことですが、これがプロ仕様のダッシュボードと基本的なダッシュボードを分けるものです。
「mmmm」や「$#,##0」などのコードを手動で定義すると、日付が月のままになり、通貨の記号も保持されます。最新のレポート ツールがすべて利用可能になったとしても、プレゼンテーションを修正するためだけに追加のヘルパー列でワークブックを乱雑にすることなく、作業を洗練させる最も直接的な方法は TEXT 関数です。
Excel でテキストと格闘するのはやめましょう: これら 8 つのツールはゲームチェンジャーです
Excel に組み込まれている従来のツールと最新のツールを使用して、「偽の」番号を修正し、隠れた Web スペースを削除し、テキストを専門的に結合します。
IFERROR: 究極の欠陥クリーナー
ダッシュボードをプロフェッショナルな外観に保ちます
#DIV/0 の壁よりも早くダッシュボードを壊すものはほとんどありません。または #N/A エラー。
IFERROR が今でもどこにでも存在するのはそのためです。数式エラーは即座にフォールバック値に置き換えられるため、シートはクリーンで読みやすい状態に保たれます。私は、特に何も考えずに多くの数式を IFERROR でラップする習慣があることに気づきました。特にレポート シートでは、単にきれいな出力が必要で、エッジケースのエラーが表面化したくない場合がそうです。
より正確なエラー処理は存在しますが、IFERROR は即時的かつ汎用的なため、依然として人気があります。数式をラップすると、出力はプレゼンテーションに安全になります。
IFERROR は注意して使用してください。これは包括的なものであるため、実際に修正する必要がある本物のロジック エラーや壊れた参照を簡単に隠すことができます。
OFFSET: ダッシュボードを引き続き強化する従来のダイナミック レンジ
構造化テーブルがなくても範囲を更新し続ける
古い Excel モデルは、データ範囲の移動の処理方法によって古さを示すことが多く、OFFSET が依然として機能する理由の 1 つとなっていることがよくあります。データの成長に応じて変化する範囲を構築するため、構造化テーブルが拡大するデータセットを管理するデフォルトの方法になる前は非常に便利でした。
とはいえ、構造化テーブルが現代の標準になっているのには理由があります。これらはより安定しており、監査が容易であり、OFFSET と同じように揮発性の再計算動作に依存しません。ただし、テーブルには頑丈で壊れない構造が必要です。空白のスペーサー行や不連続なデータ ブロックなどの「休憩スペース」が必要な、プレゼンテーション用のレポートを作成している場合、表が問題になります。
このようなシナリオでは、OFFSET はグリッドを外科的にナビゲートする残りの方法の 1 つです。最後の行を「ハント」する検索機能と組み合わせることで、視覚的なレイアウトを改善するためにいくつかの空の行を追加したからといって壊れることのない動的なダッシュボードを維持できます。これは古いツールですが、柔軟性が柔軟性に勝る驚くほど多くの現実世界のスプレッドシートに今も力を与えています。
それで、あなたはどの Excel ユーザーですか?
あなたは最新の機能を追い求めるタイプですか、それとも古いツールに何も考えずに手を伸ばすタイプですか?私は真ん中のどこかに座ります。私は新しい機能が好きですが、面倒なときや時間がないときは、今でも何年も使っている機能をデフォルトで使います。結局のところ、派手な Excel 関数を使用しても誰も報われません。本当のメリットは、最適なツールの組み合わせを使用して Excel に必要な作業を実行させることです。
- OS
-
Windows、macOS、iPhone、iPad、Android
- 無料トライアル
-
1ヶ月
Microsoft 365 には、最大 5 台のデバイスで Word、Excel、PowerPoint などの Office アプリ、1 TB の OneDrive ストレージなどへのアクセスが含まれています。