営業担当者別の売上を記録したスプレッドシートがあります。
A B C D
1 ID Name Product Sales($)
2 1 John Smith Toaster 250
3 2 Dina Caan Kettle 450
4 1 John Smith Kettle 450
5 1 John Smith Kettle 250
6 2 Dina Caan Toaster 250
7 3 Peter Hues TV 5400
8 3 Peter Hues Radio 480
9 2 Dina Caan Radio 100
10 4 Ralph Do TV 890
11 4 Ralph Do Radio 100
12 5 Ian Poe Watch 750
13 6 Tina Hood TV 450
14 7 Peter Maan Watch 99
F に、ID 名 TotalSales($) という見出しが付いた、売上高上位 n (たとえば 3) 人の営業担当者を表示する数式ベースのテーブルを作成したいです。
ピボット テーブルを使用してこれを簡単に実行し、上位 n をフィルター処理できますが、この例では数式を使用して実行したいと思います。
編集: 結果表から製品を削除しました。
答え1
Excel の新しい動的配列数式を使用すると、ピボット テーブルを構築しなくても結果を得ることができますが、いくつかのヘルパー セルが必要になります。次のスクリーンショットをご覧ください。
F2 の数式は =UNIQUE(B2:B14)コピーされません。
G2 の数式は=SUMIFS($D$2:$D$14,$B$2:$B$14,UNIQUE(B2:B14))コピーされません。
=INDEX(SORTBY(F2#,G2#,-1),{1;2;3})総売上高による上位 3 人の営業担当者の順位は、コピーダウンしないI2 の式を使用して導き出すことができます。
動的配列は数式で必要な範囲で「スピル」し、動的配列の数式セルを参照するとそのスピルが継承されます。つまり、次回新しい営業担当者が加わった場合、列 F の一意の名前のリストを更新する必要はありません。数式によって自動的に更新されます。
答え2
売上上位の順位を取得するには、次の配列数式を使用します。
=LARGE(MODE.MULT(IF(MATCH($B$2:$B$14,B:B,0)=ROW($B$2:$B$14),SUMIFS(D:D,B:B,$B$2:$B$14)*{1,1})),ROW(1:1))
配列数式であるため、編集モードを終了するときは Enter キーではなく Ctrl + Shift + Enter キーで確定する必要があります。
ID と名前を取得するには、別の配列数式を利用できます。
=INDEX(A:A,MATCH($I2,SUMIFS($D:$D,$B:$B,$B$2:$B$14),0)+1)
また、Ctrl + Shift + Enter を押して、上から下にコピーする必要があります。
