コラボレーション

「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. 報告: IT部門と事業部門の「境目」が消え始めた!?世界のITリーダーが回答
  2. 実はオンプレより、クラウドの方が安全!?情シス マサシと一緒に考えるクラウドのメリット/デメリット
  3. “収益10億円への道”を具体的に描く、世界のSaaSビジネス成功企業からの指南書
  4. 日揮やグリーも活用--世界7万6千社で使われるBox事例集
  5. Boxをビジネスに活⽤するために-業種、職種別のユースケース

編集部おすすめ

ピックアップ製品

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

ベンダー座談会

Follow TechRepublic Japan