コラボレーション

「Excel」で上位n項目の値を合計する--売り上げ上位の計算で使える2つの方法

「Excel」で売り上げが上位5位の顧客や製品を確認するのは難しくない。だが、上位5位の売り上げの合計を出すのは少しやっかいだ。どのような方法を選ぶかは、計算結果の利用目的によって変わってくる。この記事では、次の2つの方法を紹介する。

 「Excel」で売り上げが上位5位の顧客や製品を確認するのは難しくない。だが、上位5位の売り上げの合計を出すのは少しやっかいだ。どのような方法を選ぶかは、計算結果の利用目的によって変わってくる。この記事では、次の2つの方法を紹介しよう。

  • 入力値nを参照する式を使って、動的に計算する。この方法では、他の式で簡単に参照できる1つの値が返される。
  • フィルタを使ったソリューションで、合計値とその内訳を表示する。

 筆者は64ビット版の「Windows 10」で「Excel 2016」のデスクトップ版を使っているが、どちらの方法も「Excel 2007」で利用できる。式を使う方法は、「Office 365」のブラウザ版で利用できる。フィルタを使う方法もブラウザ版で利用できるが、この記事では取り上げない。自分のデータで作業してもいいし、「.xlsx」形式のデモファイルをダウンロードしてくれてもかまわない。

言葉の意味の確認

 この記事で使われる「上位」および「大きい」という言葉の意味を理解しておくことが重要だ。「上位」とは項目の値についての話であり、データセット内での位置を意味する言葉ではない。たとえば、「0、1、……9、10」というデータセットがあったら、10が最上位の値または最も大きい値となる。10がデータセット内でどの位置にあるかは関係ない。この記事では、Excelに合わせて、この両方の言葉を使用する。また、「トップテンフィルタ」とLARGE()関数を使用する。したがって、この記事では「上位」と「大きい」が同じ意味になる。

式を使う方法

 図Aの単純なデータセットでは、以下の式を使用して、「Sold」(売り上げ)列の上位n項目の値の合計を計算する。

=SUMIF(D4:D11,">="&LARGE(D4:D11,$C$1))

 この式は、その列の中でn番目に大きい数を返すLARGE()関数を使用している。この関数の構文は以下のとおりだ。

LARGE(データ範囲, n)

 この構文では、評価の対象とする値を「データ範囲」で指定し、そのデータ範囲の中で何番目に大きい値かを「n」で指定する。nデータ範囲内にある値の個数より大きくすると、0が返される。この関数をIFERROR()関数の中で使うこともできるし、条件付き書式を使って、0が返される場合に警告が出るようにすることもできる(注:IFERROR()は、「Excel 2003」以前のバージョンでは利用できない)。データ範囲が固定されている場合は、データの入力規則を代入セルに適用できる(n番目に小さい数が返されるようにするには、SMALL()を使用する)。

図A:SUMIF()は、「Sold」列内のLARGE()によって返される値と同じか、それより大きい値のみを合計する。
図A:SUMIF()は、「Sold」列内のLARGE()によって返される値と同じか、それより大きい値のみを合計する。

 上の例では、nはC1に入力されている値を参照する。この場合は1だ。そのため、LARGE()関数が返す値は、87の1つのみとなる。nを2に変えると、LARGE()は87と82を返し、SUMIF()は169を返す。

 データ範囲に行を追加する場合は、それに合わせてC2の式を変更する必要がある。このような変更を不要にするには、データセットをテーブルオブジェクト(Excel2007以降で利用可)に変換し、式がこのテーブルを参照するようにすればいい。データセットをテーブルに変換するには、以下の操作を行う。

  1. データ範囲内の任意の場所をクリックする。
  2. 「挿入」タブをクリックする。
  3. 「テーブル」グループから「テーブル」を選択する。
  4. 表示されたダイアログで、「先頭行をテーブルの見出しとして使用する」オプションにチェックを入れる(図B)。
  5. データ範囲を「=$B$1:$D$11」から「=$B$3:$D$11」に変更する。具体的には、最初の1を3に変えればよい。行1と行2は見出しではないが、行2と行3の間に空白行がないため、Excelは行1と行2を含めてしまうのだ。
  6. 「OK」をクリックする。
図B:見出しセルの行がデータに含まれるように指定する。
図B:見出しセルの行がデータに含まれるように指定する。

あなたにおすすめの記事

関連記事

ホワイトペーパーランキング

  1. リモートワーク許可はすでに4割超え!? 意外な結果から見えてきた、働き方改革で本当に必要なもの
  2. 「顧客の今」がなぜビジネスに貢献するのか - HBR調査で見る「リアルタイムアナリティクス」の効果
  3. マンガで綴るシステム運用自動化への道~あの企業はいかにして情シス部門の残業を激減させたのか?
  4. 導入担当SEが語る クラウドシフトで後悔しないために、事前に確認すべきこととは?
  5. 漏洩事件の3割、ウェブアプリへの攻撃が糸口に!?脅威トップ10を知り有効な対策を

編集部おすすめ

トレンドまるわかり![PR]

サーバ
PC・モバイル
ストレージ
ネットワーク
仮想化
クラウドサービス
OS・ミドルウェア
開発
データベース
運用
セキュリティ
新興技術
財務・経理
人事・労務
マーケ・営業
購買・調達
生産・製造
データ分析
コミュニケーション
通信・通話
文書・コンテンツ
サイト構築
PCソフト
学習

ベンダー座談会

Follow TechRepublic Japan

このサイトでは、利用状況の把握や広告配信などのために、Cookieなどを使用してアクセスデータを取得・利用しています。 これ以降ページを遷移した場合、Cookieなどの設定や使用に同意したことになります。
Cookieなどの設定や使用の詳細、オプトアウトについては詳細をご覧ください。
[ 閉じる ]