監査を改善するために複雑な Excel の数式を簡素化する方法

多くの人はコンピューター用に Excel の数式を作成しますが、専門家は人間のために数式を作成します。ロジックがテキストの壁のように見える場合、それは責任です。これら 5 つの簡単な習慣の変更は、構文を整理し、ロジックを簡素化し、スプレッドシートを監査しやすくするのに役立ちます。

名前付き範囲とテーブルを使用する

Excel テーブルと名前付き範囲に切り替えることは、スプレッドシートに対して実行できる最善の方法の 1 つです。整理整頓には無限のメリットがあり、数式を整理したい場合は最初のステップとして行う必要があります。

デフォルトで A1 や C500 などの座標を使用すると、スプレッドシートを読む人はロジックを理解するためだけにソースを見つける必要があります。テーブルと名前付き範囲を使用すると、これらの抽象的なグリッド参照が人間が理解できる言語に置き換えられます。

透明度の違いを考えてみましょう。以下は、さまざまなワークシートからのセル参照を含む数式です。

=SUM('Sales Data'!C2:C500)*(1-'Rates'!G2)

データに名前を付けると、同じ計算は次のようになります。

=SUM(T_Sales[Amount])*(1-Tax_Rate)

2 番目の式の方が理解しやすいことに注目してください。数値がどこに存在するかだけでなく、何が計算されているかについても説明します。このアプローチでは、データのグローバル スコープも与えられます。Excel はブック全体でこれらの名前を認識するため、通常、数式バーを詰まらせる長くて乱雑なワークシート参照を取り除くことができます。

最後に、値のハードコーディングは避けてください。税率や割引などの数値が変更される可能性がある場合は、その数値を独自のセルに入力し、名前を付けて参照します。これにより、ロジックが透過的になり、今後の更新にかかる時間はわずか 1 秒だけになります。

二重括弧を削除します

Excel テーブルを使い始めると、Excel が構造化参照を作成することに気づくでしょう。これは、テーブルの名前と列ヘッダー (Sales など) によってデータを参照する特別な方法です。[Amount]) (C2 など) セルアドレスによる代わりに。これらは読みやすさには優れていますが、ヘッダーにスペースや特殊文字が含まれている場合、すぐに目障りになる可能性があります。

ヘッダーが 販売台数 そして ユニットあたりの価格Excel では、ロジックをまとめるために二重括弧構文を使用する必要があります。これにより、次のような数式が得られます。

=[@[Units sold]]*[@[Price per unit]]

同様に、特殊文字を使用する場合、Excel は句読点が計算の一部として誤って解釈されないように、ヘッダーを追加の括弧レイヤーで囲む必要があります。この場合、スラッシュ 価格/単位 犯人は：

=[@Sold]*[@[Price/Unit]]

英数字を使用し、スペースを避けることで、構造化参照をできるだけ短くして読みやすくすることができます。

関連している

Microsoft Excel で括弧、角括弧、中括弧を使用する方法

シンプルなシンボルがこれほど強力なものになるとは誰が予想したでしょうか。

長い数式を書くときは垂直方向に考える

Excel の数式を 1 つの水平方向のテキストの壁として記述するのは標準的な習慣ですが、複雑なロジックを扱う場合は大きなハードルになります。したがって、作業をより適切に制御するには、垂直レイアウトを採用してください。

Alt+Enter を使用すると、数式バーに直接改行を挿入できます。これにより、IFS ステートメントのさまざまな条件や LET 式の変数など、式のさまざまな部分を積み重ねることができます。

=IFS(
[@Revenue]>2000,"High",
[@Revenue]>1000,"Mid",
[@Revenue]>0,"Low",
TRUE,"No Sales"
)

この垂直方向のアプローチは、単にビューを整理するだけではありません。欠落しているカンマや間違って配置された括弧がすぐに目立ちます。また、閉じ括弧を開始括弧に合わせて配置できるため、括弧のバランスをとるのにも役立ちます。

関数の使用法を最新化する

従来の関数に依存することは、数式スパゲッティを作成する最も簡単な方法の 1 つです。 Excel で最も一般的な障害 (インデックス番号の破損や無限の入れ子ループなど) の多くは、より効率的な新しい関数によって解決されています。ロジックをクリーンで回復力のある状態に保つには、数式の語彙をアップグレードすることが不可欠です。

関連している

Microsoft Excelの使い方を変えた6つの機能

動的配列関数はゲームチェンジャーでした。

XLOOKUP と VLOOKUP

数式が読めなくなる最大の原因の 1 つは、VLOOKUP 数式の列インデックス番号です。数式の終わりを見たことがあるなら:

,4,FALSE)

4 が実際にどの列を指しているのかを思い出そうとするのがイライラするのはご存知でしょう。 XLOOKUP は、直接範囲参照を使用することでこれを排除します。さらに、データに列を挿入すると、XLOOKUP は自動的に調整しますが、VLOOKUP は単に中断します。

IFS とネストされた IF

ネストされた IF ステートメントは、括弧の悪夢、つまり数式の最後に手動で数えなければならない長い閉じ括弧の文字列があることで有名です。 IFS 関数を使用すると、単一の線形リストで複数の条件をテストできます。

関連している

Excel でネストされた IF および IFS 式を記述するのをやめ、代わりに SWITCH を使用してください

繰り返しの長い数式を排除して、よりクリーンな Excel ロジックを作成します。

させて

LET 関数は、時間のかかる計算を大きく変えるものです。計算結果に名前を一度割り当てれば、その名前を式全体で再利用できます。 1 つの文字列内で複雑な VLOOKUP を 3 回繰り返す代わりに、名前を付けてその名前を参照することができます。

ヘルパー列を恐れないでください

ワークフロー全体を 1 つのマスター式に凝縮して、自分の Excel の腕前を証明したいという誘惑がよくあります。ただし、公式が 500 文字の長さである場合、それは傑作ではなく、欠点です。膨大な計算を 2 つまたは 3 つのヘルパー列に分割することは、複雑さよりも透明性を優先する熟練したビルダーの特徴です。

ここでは、この単一の長い式を使用して延滞料金を計算するのではなく、次のようにします。

=IF([@Status]="Unpaid",IF(TODAY()>[@Due],[@Amount]*0.05,0),0)

列 E、F、G で 3 つの別々の数式を使用しました。

これにより、次のようないくつかの利点がもたらされます。

• 特定のステップを分離すると、最終結果の検証がはるかに簡単になります。

• データが間違っているように見える場合でも、長い数式のデバッグに時間を無駄にする必要はありません。ヘルパー列を確認するだけで、ロジックがどこで破綻しているかを正確に確認できます。

• ヘルパー列を使用すると、単一の列よりも多くの情報が得られます。

数式を読みやすくすることは素晴らしい最初のステップですが、専門的なワークブックを作成する場合はそれだけが優先事項ではありません。ロジックを整理したら、視覚的な書式を調整してスプレッドシートを読みやすくします。チームで作業している場合は、共有スプレッドシートを簡単に操作できるようにして、他のユーザーが頭を悩ませることなく必要なものを見つけられるようにする必要もあります。

OS

Windows、macOS、iPhone、iPad、Android

無料トライアル

1ヶ月

Microsoft 365 には、最大 5 台のデバイスで Word、Excel、PowerPoint などの Office アプリ、1 TB の OneDrive ストレージなどへのアクセスが含まれています。