実践的な Excel スキルの共有: 複数の条件でナンバーワンの人を見つける方法

ランキングは簡単ですが、複数のプロジェクト カテゴリがあり、同じパフォーマンスがある可能性がある場合、各シェアで 1 位にランクされている人をすばやく見つけるにはどうすればよいでしょうか?希望のナンバー 1 を見つけるには、複数の条件を照合する必要があります。ここでは 2 つの解決策が提供されていますが、どちらも完璧ではありません。改善できますか?

#年次表彰会議がまもなく始まります。今年はどの同僚がセールス チャンピオンになるでしょうか?一緒に見つけてみましょう！

ある企業の電子商取引プラットフォームにおけるさまざまな種類の電化製品の販売データは図に示すとおりです。

データには売上のみが含まれます。注文番号、製品名、担当者名、販売数量を元に、製品の種類ごとに以下の形式で販売チャンピオンをカウントする必要があります。

この問題を見て、どんな方法が考えられるでしょうか？ピボット テーブル、MAX 関数、または VLOOKUP...

ベテランは 2 つの方法をお勧めします: 1 つ目は補助列の式、2 つ目はピボット テーブルの式です。

方法 1: 補助列の式

ステップ 1: 補助列の追加

最初の合計商品名で一人一人の売上をアップします。条件に従って合計します。ここでは統計に SUMIFS 関数を使用します。ピボット テーブルを使用しても同じ結果を得ることができますが、ピボット テーブルでは最終的な望ましい効果を一度に得ることができないため、補助列を使用する方が便利です。

式:

=SUMIFS(D:D,C:C,C2,B:B,B2)

##数式形式:

=SUMIFS(集計エリア, 条件エリア1, 条件1, 条件エリア2, 条件2...)

SUMIFSは複数条件の集計関数です。 、最初のパラメータは合計されるデータが配置されている列であり、次のパラメータはペアでグループ化されて一連の条件を形成します。この例では、最初の条件セットは営業担当者であるため、条件エリア 1 は列 C、条件 1 は C2、2 番目の条件セットは製品名、条件エリア 2 は列 B、条件 2 は B2 です。

補助列を使用して、次のステップは、各カテゴリの最大売上高を調べることです。ここで注意が必要なのは、統計結果の表では、販売チャンピオンの名前が最初に表示され、販売数量が最後に表示されていることです。実際の統計ではこの順番で数える必要はなく、都合の良い方から数えます。

ステップ 2: 最高販売量の統計

通常、最大値について話すとき、最初に思い浮かぶのは次のとおりです。 MAX機能。この関数の使用法は SUM と非常に似ており、一連の数値またはデータ領域を指定するだけで、この一連の数値の最大値を取得できます。

今日の例では、取得したいのは同じカテゴリ内の最大値、つまり条件に基づく最大値の統計なので、MAX関数を直接使用して結果を取得することはできません。

#これ 条件に基づいて最大値をカウントするための固定ルーチン式があります:

=MAX(データ領域*(条件領域 1=条件 1)*(条件領域 2=条件2)...)

この例には、製品名という条件が 1 つだけあるため、式は次のようになります:

=MAX($E$2:$E$750*($B) $2:$B$750=G2))

この式を使用するときは、次の 3 つの点に注意する必要があります。

(1)範囲は正確である必要があります。列全体を計算領域として選択することはお勧めできません。

(2) 数式には配列演算が含まれます。数式を入力した後、Ctrl Shift Enter キーを押す必要があります。キーを押すと、数式に中括弧が自動的に追加されます;

(3) 数式をプルダウンする必要があるため、計算範囲が変わるため、関係する範囲を変更する必要があります。絶対参照を使用します。

この式の具体的な原理には、論理値と配列の計算原理が関係しますが、これについては後ほど具体的に説明します。

この時点で、各種類の製品の最も高い売上に対応する営業担当者を見つけて、すべての統計を完了します。

ステップ 3: 優秀な人材を見つける売上に基づいて人材を確認します。これは実際には検索参照です。VLOOKUP または INDEX を使用します。他の参照関数によって実行されます。

ほぼ成功したので、今度はリンゴの皮をむきます。リンゴの皮むきの特徴は、薄さと正確さです。

最初の詳細: データ ソース内の累積売上高は、ビジネスパーソンの右側にあります。

VLOOKUP を使用する場合、逆検索ルーチンを使用する必要があり、数式は比較的複雑です。 INDEX と MATCH を組み合わせることができ、式は難しくありません:

=INDEX($C$2:$C$750,MATCH(I2,$E$2:$E$750,0) )

#2 番目の詳細: 最大売上高は同じである可能性があります。

これら 2 つの機能を組み合わせたものを、クラシック パートナーと呼びます。しかし、まだ詳細はあります。2 つのカテゴリの製品の最大売上高が同じである可能性を排除することはできません。異なるカテゴリの最大売上高が同じ場合に発生する可能性のある検索エラーを回避するには、製品名と売上高の 2 つの条件に基づいて一致させる必要があります。式は次のようになります。

=INDEX($C $2:$C$750,MATCH(G2&I2,$B$2:$B$750&$E$2:$E$750,0))

複数条件一致の一般的なルーチンの 1 つは次のとおりです。接続記号 & を使用して、複数の条件を組み合わせてクエリの新しい条件を作成します。もちろん、クエリ領域も & で結合する必要があります。

もちろん、このような複数条件の検索を探していて、逆検索に Vlookup を使用したくない場合は、LOOKUP 関数を使用して検索を完了することもできます。それ:

=LOOKUP(1,0/(($E$2:$E$750=I2)*($B$2:$B$750=G2)),$C$2:$C $750)

複数条件一致の 2 番目の一般的なルーチンは、等号 = を使用して複数条件の検索領域を含む式を作成し、その式を乗算することです。

式は次のとおりです: =LOOKUP(1,0/(条件領域=条件), 対象領域)、複数の条件がある場合、ルーチンを次のように直接アップグレードできます: =LOOKUP(1,0/((条件領域 1=条件 1)*(条件領域 2=条件 2)*(条件領域 3=条件 3)..., ターゲット領域)

方法 2: ピボット テーブルの式

ステップ 1: 統計とランキング

# 製品名と営業担当者を行エリアにドラッグし、販売数量を値エリアに 2 回ドラッグして、Tribe Nest Education の昨年のチュートリアルに従って販売数量 2 を設定します。「マウスを 2 回ドラッグしてパフォーマンスを取得してください」数値の表示形式は「降順」、基本フィールドは「営業担当者」となっており、商品カテゴリごとの販売実績統計とランキングを取得できます。

## ステップ 2: ピボット テーブルを整理する

##ピボット テーブルをクリックし、「レポート」の「表形式で表示」と「表形式で表示」をクリックします。 「デザイン」タブの「レイアウト」オプショングループの「レイアウト」ドロップダウンメニュー 「すべてのプロジェクトラベル」コマンドを繰り返し、ピボットテーブル上で右クリックし、「小計「業務担当者」」を選択し、小計をキャンセルします

ステップ 3、チャンピオンの名前とパフォーマンスを取得する式を入力します。

セル G2 に数式を入力します:

=INDEX(L$2:L$200,MATCH($G2&1,$K$2:$K$200&$N$2) :$N$200,0))

入力後、Ctrl Shift Enter 3 キー End を押します。

次に、右クリックしてプルします。

# 今日のチュートリアルでは、SUMIFS、MAX、INDEX、MATCH、LOOKUP といういくつかの関数を学習しました。また、複数条件一致のための 2 つのルーチンも学習しました。 , これは、同様の問題が発生したときに直接使用できます。

ただし、今日の解決策は不完全です。「リンゴの皮をむく」ように自分自身に問いかけ、チュートリアルの詳細に注意を払うようにしましたが、依然として非常に重要な詳細を見逃していました。 - 同様の製品の最高売上も同じである可能性があります。

関連学習の推奨事項:

exceltutorial

以上が実践的な Excel スキルの共有: 複数の条件でナンバーワンの人を見つける方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。