17の高度なExcel数式–すべての専門家にとって知っておくべきこと
公開: 2019-07-28手を曲げたり、Excelで手動で作業するために時間を無駄にしたりすることがよくある場合は、Excelを正しい方法で使用する方法を知っていれば、Excelがいかに強力であるかを見逃しています。 Microsoft Excelは、プロセスに手作業をあまり必要とせずに、セル内の多数のデータに対して効率的に機能できる数式で知られています。
長くてありふれた手動のタスクを数秒の作業に変換する素晴らしい能力を備えた17の高度なExcel式をまとめました。 これらは、すべての専門家が自分の貴重な時間を節約したり、重要な瞬間に上司に感銘を与えたりするために指先で持つべき公式です。
リストに目を通し、あなたのお気に入りを教えてください！
学習者は平均58％の昇給を受け、最高は400％まで引き上げられます。目次
1.インデックスマッチ
式=INDEX（C3：E9、MATCH（B13、C3：C9,0）、MATCH（B14、C3：E3,0））
ExcelのVLOOKUPまたはHLOOKUP数式の優れた代替手段であり、ルックアップタスクの実行にいくつかの欠点があります。 INDEX MATCHは、2つの別々の関数の組み合わせ式です。
INDEX：この数式は、列と行番号に基づいてテーブル内のセルの値を返します。
MATCH：この数式は、行または列のセルの位置を返します。
INDEX式とMATCH式を組み合わせた例は、次のとおりです。名前に基づいて人の身長を検索して返す場合、この式を使用して、INDEX MATCH式を使用して両方の変数（この場合は名前と身長）を変更できます。 。
ソース
それを段階的に分解してみましょう：
INDEXとMATCHを組み合わせる方法
- タイプINDEX
- インデックスを作成するエリアを選択します
- F4でエリアをロックする
- データを検索する行を見つけます
- 一連の情報でMATCHと入力し、F4でエリアをロックします
- 完全一致の場合は0と入力し、角かっこを閉じます
- Enterキーを押します
これで、シートに完全に動的で機能的な列と行のセットが作成され、適切なデータのセルと行のすべてのコーミングが自動的に実行されます。
2.平均
平均式を実行して任意の範囲の数値の平均を見つけるには、次の手順に従う必要があります。
計算する値、セル、またはセルのスケールをフォーマットで入力します。
数式は=AVERAGE（number1、number 2など）で始まります。
シート内のセルの範囲の平均を実行する場合は、次の手順に従う必要があります。
- 上記と同じように、計算している値、セル、またはセルのスケールをフォーマットで入力します
- 式は=AVERAGE（開始値：終了値）のようになります。
セルの領域の値を入力する方法がわからない場合は、マウスでシート上の領域を選択し、F4でロックすることができます。 これにより、グループ内のアイテム数で数値を合計したり除算したりすることなく、Excelに残りの作業を実行させることができます。
3.SUMIF
このExcelの式は、SUMIF（範囲、基準、[合計範囲]）として表されます。 これにより、設定した要件を満たすセルの目的の範囲内の値の合計が得られます。 たとえば、= SUMIF（C3：C12、“> 70,000）は、値が70,000を超えるセルのみからC3とC12のセル間の値の合計を返します。
財務、給与、さらにはコストの計算の場合は、設定した条件によって決定される特定の従業員に関連付けられているリードの値が必要です。 これを手動で行うと、非常に時間がかかり、処理速度が低下します。
上記の条件の式は、= SUMIF（range、criteria、[sum_range]）にすることができます。ここで、Rangeは、値を選択する必要があるシートの範囲として定義できます。
[sum_range]は、最初に入力した範囲に加えて、合計する追加またはオプションの範囲として定義されます。次に例を示します。
ソース
4.合計と組み合わせたオフセット
単独では、OFFSET関数は便利ではないかもしれませんが、他のサービスと組み合わせると、任意の可変数のセルを合計できる動的な役割、つまり通常のSUM式を作成する場合に、より高速な結果をもたらす複雑な式を取得できます。 staticはOFFSET関数と組み合わせる必要があります。
したがって、可変セルの値の合計を求めるには、数式は次のようになります。
= SUM（B4：OFFSET（B4,0、E2-1））
ソース
ここで、SUM関数の終了参照はOFFSET関数に置き換えられます。 B4で始まるSUM式は変数で終わり、B4で始まり、E2の値（「3」）から1を引いた値まで続くOFFSET式です。 これは、メソッドが2つのセルを移動し、3年間のデータを合計するのに役立ちます。 上記の参照では、セルF7がセルB4の合計を保持していることがわかります：D4=15。
5.および
この式は、データのヒープをくまなく調べるために特定の条件を作成する必要がある場合に便利です。 IFステートメントは、既存のトラックでこれらの条件に基づいて新しいフィールドを作成するための高度なExcelIF関数の使用に役立ちます。
たとえば、給与が5万を超え、従業員IDが3を超える従業員をマークする場合、式は次のようになります。
IF（AND（E4> 3、F4> 50000）1,0）
ソース
上記のデータには実際のケースがないため、数式は値0を返します。
6.連結
データシートに多数のテキスト文字列があり、データを1行で表示したい場合は、この数式が最適です。 たとえば、従業員IDと従業員名を1つの列で表す場合、メソッドは次のようになります。
= CONCATENATE（B3、C3）
ソース
7. PMT
この関数は、特定の利率、元本、およびローンの期間を指定して、ローンの将来の支払いを把握するのに役立ちます。 開始する必要があるものは次のとおりです。
- ローンの期間
- ローンの開始元本（お金）
- 将来価値
- ローンの種類
ここで、元本の月々の支払いを検索する場合、同じ式は次のようになります。
= PMT（レート、あたり、PV、[fv]、[タイプ]）
例を挙げてこれを理解しましょう。
ソース
= PMT（C2 / 12.B2.A2）
そして最良の部分は、PMTセルの右下隅をフィールド間でドラッグして、すべてのフィールドの毎月の支払い額を自動的に計算できることです。
人気のあるタイプのデータサイエンスジョブ8.トリム
これは、フィールド内の不要なスペースをクリーンアップするExcelで最も使用されている数式の1つです。 例：名前の先頭のスペースを削除する必要がある場合は、TRIM関数を使用して削除できます。
= TRIM（C6）
ソース
これにより、前面または端に余分なスペースを付けることなく、チャンダンケールの価値を得ることができます。
9.レン
これは、テキスト文字列の文字数を通知する関数です。 これを使用する最もエキサイティングな方法の1つは、たとえば、寄付列の桁数を数えて1,000ドルを超える寄付をした寄付者を強調したい場合、式は次のようになります。
= LEN（B2）
ソース
また、[寄付]列のすべてのセルを強調表示する場合は、次のことを行う必要があります。
- メニューの[ホーム]タブを選択します
- （ツールバーの）条件付き書式をクリックします
- [数式を使用]を選択して、フォーマットするセルを決定します
ソース
ここで、条件を「> 3」にすると、1,000ドルを超えるものはすべて、[フォーマット]オプションをクリックして選択できる一意のフォーマットを受け取ります。
10.選択する
これは、財務モデリングのシナリオ分析に最適な機能です。 特定の数のオプションから選択して、選択した「選択肢」を返すことができます。 たとえば、仮定に基づいて来年の収益成長率に3つの異なる値がある場合、CHOOSE関数を使用して、要件に応じて金額を返すことができます。
式は次のとおりです。
= CHOOSE（C7、D3、D4、D5）
ソース
11.セル、左、中、右
上記のすべての関数をさまざまな方法で組み合わせて、高度な数式を取得できます。
- CELL関数は、セルの内容に関するさまざまなタイプの情報を返すために使用されます
- LEFTサービスは、セルの先頭からテキストを置き換えるために使用されます。
- MID関数は、セルの任意の開始点からテキストを返すことができます。
- RIGHT関数は、セルの終わりからのテキストのみを返します。
ソース
12.XNPVおよびXIRR
投資銀行業務、株式調査、またはキャッシュフローの割引を必要とするその他の企業金融の分野に出くわすすべてのアナリストにとって、これらの公式はあなたに多くの時間と不平を節約することは確実です！
データサイエンスインタビューの質問と回答たとえば、指定された割引率と不規則な間隔で発生するキャッシュフローを使用して投資の現在正味価値（NPV）を計算する場合は、次の関数を使用します。
= XNPV（割引率、キャッシュフロー、日付）
ソース
一方、XIRR関数は、次のような不規則な間隔で発生する一連のキャッシュフローの内部収益率（流出=流入）を示します。
13. COUNTA（）
アナリストは、値（数値、エラー、テキスト、論理値）を持つセルと空のセルの数を見つけるのに苦労していることがよくあります。 COUNTA（）関数は、選択した範囲内の空でないセルの名前を見つけるのに役立ちます。 たとえば、列A1〜A10を持つデータシートの値をカウントする式は次のとおりです。
= COUNTA（A1：A10）
ソース
14. FV
この式は、何かにお金を投資してその価値を知りたい場合に便利です。 FV式の要件は次のとおりです。
- ローンの金利
- 支払い回数
- 各期間の支払い
- 現在の開始残高
- ローンの種類
たとえば、複数の空のCDを比較したい場合、CDに投資するための2万ドルの相続があります。 金利は10進形式で表されます。 支払いはゼロです。 シナリオの式は次のようになります。
= FV（A2 / 12、B2、C2、D2）
ソース
結果は次のようになります。
ソース
15. RANDBETWEEN
この関数は、事前定義された数値範囲内の数値をランダムに選択するのに役立ちます。 数式に最小値と最大値を入力すると、Excelは、範囲内の名前が付加されているフィールドから適切なデータを選択し、それらからランダムに選択できます。 シナリオの方法は次のとおりです。
= RANDBETWEEN（開始点、終了点）
ソース
16.小さい
ExcelのSMALL関数は、重要度でランク付けされたリスト内の値の位置に基づいて数値を返します。 この関数は、最小値、2番目に小さい値、3番目に小さい値など、セルの配列または範囲から「n番目に小さい値」を取得するのに役立ちます。
式の構文は次のとおりです。
= SMALL（回、範囲）
例えば、
ソース
SMALL関数は自動であるため、ランク付けされた値を指定するには、「n番目」に範囲と整数を指定する必要があります。 これらの引数の正式な名前は「配列」と「k」です。
17.四分位数
この関数は、指定されたデータセットの四分位数（4つの等しいグループのそれぞれ）を返し、最小値、最初の四分位数、2番目の四分位数の最大値を返すことができます。 この関数は、配列内のフィールドの四分位数をもたらします。 この関数は、要求されたパーセンタイルに従って数値を返します。
構文：= QUARTILE（配列、クォート）
ソース
結論：
21世紀の職場では、Microsoft Excelは非常に避けられませんが、その秘訣は、それほど困難である必要はないということです。 それをあなたの友達にして、あなたの生産性が急上昇するのを見てください！