Note: The other languages of the website are Google-translated. Back to English

Excelで動的なトップ10またはnリストを作成する方法は?

トップ リストは、値に基づいて企業または個人をランク付けするために使用されます。 クラスの学生の成績のリストがあると仮定すると、下のスクリーンショットに示すように、学生の動的なトップ 10 リストを作成したいとします。 この記事では、Excel ワークシートでトップ 10 または n リストを作成するための数式をいくつか紹介します。


Excel で動的なトップ 10 リストを作成する

Excel 2019 以前のバージョンで、トップ 10 リストまたは抽出条件付きのトップ 10 リストを抽出するには、次の数式を適用する必要があります。

動的なトップ 10 リストを作成する数式

1. まず、データ範囲から上位 10 個の値を抽出する必要があります。以下の式を空白セル - G2 に使用し、塗りつぶしハンドルを下にドラッグして上位 10 個の値を取得してください。スクリーンショットを参照してください。

=LARGE($B$2:$B$20,ROWS(B$2:B2))
注意:この式では、 B2:B20 は、上位 10 個の値を取得するデータ リストです。 B2 データリストの最初のセルです。

2. 次に、次の式をセルに適用し続けます-F2、そして押します Ctrl + Shift + Enter 最初の結果を得るために一緒にキーを押します。スクリーンショットを参照してください。

=INDEX($A$2:$A$20,SMALL(IF($B$2:$B$20=G2,ROW($B$2:$B$20)-ROW($B$1)),COUNTIF($G$2:G2,G2)))
注意:上記の式では、 A2:A20 上位 10 個の値の名前を取得するデータ リストです。 B2:B20 すべての値を含むデータ リストです。 G2 列から抽出された最大値のセル B および B1 値リストのヘッダー セルです。

3. 最初の結果を取得したら、数式セルを選択し、フィル ハンドルを下にドラッグして、他の名前を一度に取得します。スクリーンショットを参照してください。


条件付きの動的トップ 10 リストを作成する数式

場合によっては、基準に基づいて上位 10 のリストを取得する必要がある場合があります。 たとえば、以下のスクリーンショットのように Class10 の上位 1 名とスコアを取得するには、次のようにします。

1. 上位 10 のリストを取得するには、次の式で上位 10 のスコアを抽出する必要もあります。

=LARGE(IF($B$2:$B$25=$F$2,$C$2:$C$25),ROW(I2)-ROW(I$1))

2。 そして、 Ctrl + Shift + Enter キーをまとめて最初の結果を取得し、この数式を下にドラッグして他の値を表示します。スクリーンショットを参照してください。

3. 次に、次の式をコピーしてセル - I2 に貼り付け、押してください。 Ctrl + Shift + Enter キーを同時に押して最初の結果を抽出し、この数式をドラッグして他のセルに入力すると、下のスクリーンショットのように上位 10 個の名前が表示されます。

=INDEX($A$2:$A$25,SMALL(IF(($C$2:$C$25=J2)*($B$2:$B$25=$F$2),ROW($C$2:$C$25)-ROW($C$1)),COUNTIF(J2:$J$2,J2)))

Office 10 で動的なトップ 365 リストを作成する

上記の式は理解しにくい場合があります。Office 365 を使用している場合は、INDEX、SORT、および SEQUENCE 関数を使用して、このタスクを完了するための簡単な式を作成できます。

動的なトップ 10 リストを作成する式

データの 10 個のリストを取得するには、次の式を使用してください。

=INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2})

そして、押すだけです 入力します キーを押すと、トップ 10 リストのすべてのデータが一度に表示されます。スクリーンショットを参照してください。

ヒント:

ソート機能:

=SORT(配列、[並べ替えインデックス]、[並べ替え順]、[並べ替え順])

  • 配列: 並べ替えるセルの範囲。
  • [ソート_インデックス]: 配列をソートする列または行の番号。 たとえば、データ範囲の 2 番目の列で並べ替えるには、並べ替えインデックスは XNUMX になります。
  • [並べ替え順]: 数字の 1 (または省略) は、昇順で並べ替えることを示します。 数値 -1、降順でソート。
  • [by_col]: ソートの方向。 TRUE、列で並べ替え、FALSE または省略、行で並べ替え。

式では、SORT 関数を次のように使用します。

ソート(A2:B20,2,-1): 2 列目のセル範囲 A20:AXNUMX を降順で並べ替えることを意味します。


シーケンス関数:

=SEQUENCE(行、[列]、[開始]、[ステップ])

  • : 返される行数、
  • [列]: 返される列の数。 省略した場合、単一の列が返されます。
  • [始める]: シーケンスの最初の番号。 省略した場合は 1 から始まります。
  • [ステップ]: 各数値間の増分。 除外した場合、各増分は 1 になります。

この式では、SEQUENCE(10) を使用して 1 から 10 までのリストを作成します。

最後に、SORT 関数と SEQUENCE 関数の両方を INDEX 関数に入れます。 =INDEX(SORT(A2:B20,2,-1),SEQUENCE(10),{1,2})、これはデータ範囲から最初の 10 レコードを返し、列 1 と 2 を返します。


条件付きの動的トップ 10 リストを作成する式

トップ 10 のリストを条件付きで表示するには、次のように FILTER 関数を SORT 関数に埋め込む必要があります。

=INDEX(SORT(FILTER(A2:C25,B2:B25=F2),3,-1),SEQUENCE(10),{1,3})

そして押すだけ 入力します キーを押すと、指定された基準に基づく上位 10​​XNUMX リストのすべてのデータが一度に表示されます。スクリーンショットを参照してください。

ヒント:

フィルター機能:

=FILTER(配列、インクルード、[if_empty])

  • 配列: フィルタリングするセルの範囲。
  • include: 配列をフィルタリングして TRUE または FALSE の結果の配列を取得し、TRUE 値がフィルタに保持されるようにするために使用する条件。
  • [空の場合]: 一致する結果が返されなかった場合に表示される値。

この式では: =FILTER(A2:C25,B2:B25=F2) 範囲 A2:C25 でフィルター処理するために使用されます。ここで、B2:B25 の値は特定のセル F2 に等しくなります。


最高のオフィス生産性ツール

Kutools for Excelはほとんどの問題を解決し、生産性を80%向上させます

  • 再利用: すばやく挿入 複雑な数式、チャート および以前に使用したものすべて。 セルを暗号化する パスワード付き。 メーリングリストを作成する そしてメールを送る...
  • スーパーフォーミュラバー (複数行のテキストと数式を簡単に編集できます); 読書レイアウト (多数のセルを簡単に読み取って編集する); フィルター範囲に貼り付け...
  • セル/行/列をマージする データを失うことなく; 分割セルコンテンツ; 重複する行/列を組み合わせる...重複セルを防止します。 範囲を比較する...
  • [複製]または[一意]を選択します 行; 空白行を選択 (すべてのセルは空です); スーパーファインドとファジーファインド 多くのワークブックで; ランダム選択...
  • 正確なコピー 数式参照を変更せずに複数のセル。 参照の自動作成 複数のシートに; 箇条書きを挿入、チェックボックスなど...
  • テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷。 セルの内容とコメントを変換する...
  • スーパーフィルター (フィルタースキームを保存して他のシートに適用します); 高度な並べ替え 月/週/日、頻度など。 特殊フィルター 太字、斜体...
  • ワークブックとワークシートを組み合わせる; キー列に基づいてテーブルをマージします。 データを複数のシートに分割; xls、xlsx、PDFをバッチ変換...
  • 300 以上の強力な機能. Office / Excel 2007-2021 および 365 をサポートします。すべての言語をサポートします。 企業や組織に簡単に導入できます。 フル機能の 30 日間無料トライアル。 60日間の返金保証。
kteタブ201905

Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
officetab下部
コメントを並べ替える
コメント (0)
まだ評価はありません。 最初に評価してください!
ここにはまだコメントが投稿されていません
あなたのコメントを残す
ゲストとして投稿
×
この投稿を評価:
0   文字
推奨される場所

フォローする

著作権©の2009 - WWW。extendoffice.com。 | | 全著作権所有。 搭載 ExtendOffice。 | サイトマップ
MicrosoftおよびOfficeのロゴは、米国MicrosoftCorporationの米国およびその他の国における商標または登録商標です。
SectigoSSLで保護