Copilot プロンプトを含む 15 の重要な Excel の数式と関数

正確で信頼性の高いデータは、適切な基盤から始まります。 手動エラーを減らし、間違いを早期にキャッチし、Microsoft Excel 数式を使用してデータが変更されるたびに保持するロジックを構築します。 単純な集計から複数条件の概要、テーブル間参照まで、それぞれ Excel に組み込まれており、インストールなしでブラウザーで使用できます。

インポートされたデータのクリーンアップからデータセットの分析と集計まで、15 の重要な Excel の数式と関数について説明します。 各セクションには、実際の例と Copilot が含まれています。 構文を記憶せずに作業を開始するための Microsoft Excel アクション。

正確な数式のデータ クリーンアップ

一貫性のある使用可能な状態にデータを取得することは、分析を実行する前の最初の手順です。 これらの関数を使用して、重複の削除、間隔の修正、テキストの標準化を行い、数式、参照、ピボットテーブルの概要によって信頼性の高い結果が得られます。

• TRIM: テキスト文字列の先頭と末尾から余分なスペースを削除し、単語間の複数のスペースを 1 つのスペースに減らします。 別のシステムの名前または ID を貼り付け、TRIM によって、VLOOKUP 式と COUNTIF カウントを中断する間隔の不整合がクリーンアップされます。

• CONCAT と TEXTJOIN: テキストを個別の列から 1 つのフィールドに結合します。 TEXTJOIN では、値間に選択した区切り記号が追加されるため、分割されたソース データから完全な住所、製品ラベル、または表示名を迅速に作成できます。

• 左と右: テキスト文字列の両端から固定文字数をプルします。 LEFT を使用して、製品 ID の先頭からリージョン コードを抽出するか、RIGHT を使用して、ファイル拡張子または日付コンポーネントをフィールドの末尾から分離します。

• SORT: 元のデータに触れることなく、範囲の並べ替えられたバージョンを返します。 並べ替え プロジェクト計画一覧 を期日別に並べ替えたり、 在庫トラッカー を在庫レベル別に確認してから、補充が必要なものを確認します。

• FILTER: 設定された条件を満たす行のみを返します。 クライアントの完全な一覧からアクティブなアカウントをプルし、期限切れのタスクのみを表示します。 プロジェクト トラッカー、または 1 か月間のエントリを通年データセットから分離します。

• [重複の削除]: 選択した範囲から 1 つのステップで繰り返される行を削除します。 キャンペーンの送信前に連絡先リストで実行し、マージされた顧客エクスポートで一意のレコードを取得するか、複数の SEO ツールから取得したキーワード (keyword)リストで実行します。

次の Copilot のプロンプトの例を試してください

重複を削除し、この表のテキスト書式を標準化する便利な方法を提案します。クリックしてこの Copilot プロンプトをコピーする

スプレッドシート データの合計、平均、およびカウント

範囲の合計、平均値、カウント、および最大値と最小値は、一般的にデータ スプレッドシートで使用されます。 ビジネス スプレッドシートの数値範囲には、次の 7 つの関数を適用できます。

• SUM: 選択した範囲内のすべての値を から追加します。 個人的な予算プランナー、 への毎月の送信を追跡する 貸借対照表 の調整資産と負債。

• AVERAGE: 範囲全体の平均を計算します。 四半期の平均注文値を追跡します。 損益計算書、サポート チーム全体の平均応答時間、またはアンケート回答列からの平均スコア。

• MIN と MAX: 範囲内の最小値と最大値を返します。 それらをペアにして、経費データセット内の外れ値を見つけるのに役立ちます。チェック、データ入力が想定範囲内に留まるか、シート全体を並べ替えずに売上列で最適かつ最悪のパフォーマンスを示します。

• COUNT と COUNTA: COUNT は、数値を含むセルを集計します。COUNTA は、空白以外のセルをカウントします。 サインアップ列で COUNTA を使用して、応答の数を確認するか、収益列の COUNT を使用して、これまでに入力されたトランザクションの数をチェックします。

• COUNTIF: 1 つの条件を満たすセルをカウントします。 "有料" の状態を持つ請求書の数はいくつですか? オプション B を選択したアンケート回答の数はいくつですか? COUNTIF は、最初にフィルター処理や並べ替えを行う必要なく、数値を返します。

• SUMIF: 別の列の条件に基づいて、ある列の値の合計。 タイムシート全体で 1 人のチーム メンバーによってログに記録された時間を追加します。1 つのサプライヤーからの合計コストは、 ビジネス予算作成 スプレッドシート、1 週間に 1 人のエージェントによって閉じられた合計サポート チケット、または旅行申請のカテゴリ別の合計経費。

• ランク。EQ: リスト内の数値の位置を返します。最大値から最小値、または逆の位置を返します。 売上者を収益別にランク付けし、キャンペーンの結果をコンバージョン率で注文するか、基になるデータを変更せずに結果列からランキングを作成します。

次の Copilot のプロンプトの例を試してください

このシートで完了とマークされているタスクの数をカウントするには、どの数式を使用する必要がありますか? クリックしてこの Copilot プロンプトをコピーする

条件付きロジックとエラー処理

ロジック式は、静的セルを条件付きセルに変え、問題が発生したときにスプレッドシートを読み取り可能に保ちます。 数式を設定して、データに含まれる内容に基づいて異なる結果を返すか、複数の条件を 1 つのテストに結合するか、数式エラーを壊れたセルではなくクリーン値に置き換えます。

• IF: 条件が true の場合は 1 つの結果を返し、false の場合は別の結果を返します。 で期限切れの請求書にフラグを設定する プロジェクト管理 トラッカー、アンケートスコアに「目標より上」または「ターゲットの下」とラベルを付けるか、 状態列の値に基づいて、 to-do リスト が完了しました。

• IFERROR: 数式が返すエラーをキャッチし、代わりにダッシュ、0、またはプレーン言語のメモなど、指定した値に置き換えます。 結果を読みやすくするために、必ずしも一致するものが見つからない可能性がある VLOOKUP 数式の周りにラップします。

• AND と OR: 複数の条件を 1 つの論理テストに結合します。 AND は、すべての条件が満たされた場合にのみ true を返します。OR は、少なくとも 1 つが の場合に true を返します。 IF 数式内のどちらかを入れ子にして、リージョンと状態の両方がターゲットと一致する場合にのみレコードにフラグを付けるなど、より正確なルールを構築します。

次の Copilot のプロンプトの例を試してください

列Bの値が80%を超える場合は列Cに'オントラック'、そうでない場合は'確認が必要'を表示します。どのような数式を使用する必要がありますか? クリックしてこの Copilot プロンプトをコピーする

データ参照と照合

ルックアップ式は、あるテーブル内の値を検索し、別のテーブルから関連情報を返します。 これらを使用して、注文 ID から顧客名をプルし、製品コードを価格レベルに一致させ、大規模な特定の値を見つけます 予算プランナー、分類シートから意図カテゴリにキーワードをマップする、または 別のリストからアカウントの詳細を含む 請求書ジェネレーター 。

• VLOOKUP: テーブルを左から右に検索し、一致が見つかった場合に指定した列から値を返します。 共有ブックやレガシ ファイルではまだ広く使用されています。

• XLOOKUP: 任意の方向で検索し、一致するものが見つからない場合に指定された値を返すことができるため、VLOOKUP よりも柔軟性が高くなります。 Excel for the webで使用できます。

• MATCH: 範囲内の値の位置を検索します。 それを INDEX と組み合わせて、VLOOKUP に到達できない検索列の左側にある列を含む、テーブル内の任意の列からデータを取得します。

• INDEX: テーブル内の特定の位置から値を取得します。 MATCH と組み合わせて、XLOOKUP も VLOOKUP も直接解決できない参照を処理します。たとえば、2 つの一致する条件に基づいて個別の列間で値を返すなどです。

次の Copilot のプロンプトの例を試してください

列 A の製品コードと列 D と E の価格表を照合する VLOOKUP 式を記述します。クリックしてこの Copilot プロンプトをコピーする

データ分析と要約

SUMIFS とピボットテーブルは、Excel で大規模な分析を処理します。 SUMIFS では、複数の条件にわたる合計が計算され、ピボットテーブルはデータをグループ化して、読みやすく共有しやすいビューに集計します。

• ピボットテーブル: 行をグループ化し、合計を計算し、ソース データに触れずにビューを並べ替えることで、大規模なデータセットを集計および探索します。 ピボットテーブルを使用して、リージョン間の月次売上を比較する ビジネス予算 作成シート、ソースとデバイス別にトラフィックを分析する、またはカテゴリ別に経費を分割する ガント チャートベースのプロジェクト ファイル。 Copilot とチャットして、ピボットテーブルの設定に役立ちます。

• SUMIFS: 一度に 2 つ以上の条件を満たす値の合計。 特定のリージョンの特定の製品の収益の合計、設定された週の 1 人のチーム メンバーのタイムシートからの合計時間、または 1 つのリージョンの特定のアイテムに対する週単位の支出 予算プランナー。 1 つの条件で適切な行を分離できない場合に使用します。

次の Copilot のプロンプトの例を試してください

ピボットテーブルを作成して、このデータから地域と月別の売上を集計します。クリックしてこの Copilot プロンプトをコピーする

注: Excel の Copilot には、Copilot を含む Microsoft 365 サブスクリプションが必要です。 Filesは、自動保存が有効になっている OneDrive または SharePoint に保存する必要があります。 Copilot は、.xlsx、.xlsb、および .xlsm ファイルで動作します。

既製から始める Excel スプレッドシート テンプレート を使用して、現在の実際のデータにこれらの数式を適用します。

Excel の Copilot が 1 つのブックで分析、グラフ、およびデータ クリーンアップをサポートする方法を確認するには、 AI を使用して Excel スプレッドシートを作成および分析する 5 つの方法。 さまざまな業種の経費を体系的に計画する方法について説明します。 Excel で財務を管理するための 5 人の予算プランナー。