Excel で空白セルをスキップしながら、簡単に数値をランク付けする方法は？
Excel でデータを扱う際、空白セルを含むリストに遭遇することはよくあります。こうしたリストに標準のExcel ランク関数（)RANKまたはRANK.EQ）を適用すると、通常、空白セルに対してエラーや意図しない結果がランク出力に現れてしまいます。特に、エラー値や不適切なランクではなく、空白セルはそのまま空欄にしておきたい場合、これがデータの解釈を難しくします。空白セルを自動的に無視しながら効率よく数値をランク付けすれば、結果がより明確で使いやすく、スプレッドシートもプロフェッショナルで読みやすいものになります。
本記事では、このタスクを達成するための数式と VBA マクロを使ったステップバイステップの手順をご紹介します。さらに、より実践的な解決策として、パラメーターの説明や活用のヒント、そしてよくある落とし穴を回避するためのトラブルシューティング提案も含まれています。
数式を使用して空白セルをスキップしながら値を昇順でランク付けする
空白セルを無視しつつ昇順でランクを割り当てたい場合は、一般的に複数のヘルパー列と数式ロジックを組み合わせて、空の値がランク付けに含まれないようにします。
適用シナリオ：この方法は、空のセルの位置を維持しながら、増分的（小さい値から大きい値へ）にランク付けしたい場合に最適です。特に、欠損エントリがランク番号に影響を与えないようにしたい連続した範囲にぴったりです。
空白セルをスキップしながら昇順でランク付けを行うには、以下の手順に従って2 つのヘルパー列を使用して結果を構築してください：
1．値の隣接する空白セル（例：リストの開始セルが)A2の場合、セルB2）を選択し、次の数式を入力します：
=IF(ISBLANK($A2),"",VALUE($A2&"."&(ROW()-ROW($B$2))))この数式は、A2 が空の場合は空白を返し、そうでない場合は A2 の値に基づいて「。0」「。1」「。2」「。3」などの小数点以下の数値を生成します。フィルハンドルを下にドラッグしてデータを含むすべての行に数式をコピーすると、これらの小数が順次追加されていきます。
パラメーターの説明とヒント：
- $A2：ランク付けする最初のセルです。リストの開始行が異なる場合は、適宜調整してください。
- $B$2：このセルに数式を入力します。数式を下方向にコピーした際に正しく動作するよう、絶対参照（例：$A2 や $B$2）を適切に使用してください。
2．次の列（例：C2）に、ヘルパー値のソート済みリストを生成する次の数式を入力します：
=SMALL($B$2:$B$8,ROW()-ROW($C$1))この数式は、B2:B8（注：データ範囲が広い場合は範囲を変更してください）から、数式を下にコピーするにつれて、最小値、2 番目に小さい値、3 番目に小さい値……と順に抽出していきます。
パラメーターの説明：
- $B$2:$B$8：第1 段階のヘルパー数式が適用されているセル範囲です。
- $C$1：数式を入力するセルの直上にあるセルです。このオフセットによって、ランキングの順序が制御されます。
3．セルD2に、空白セルをそのままにしつつランクを割り当てる次の数式を入力します：
=IFERROR(MATCH($B2,$C$2:$C$8,0),"")この数式は、B2の値をC2:C8のソート済みリスト内で照合します。一致した場合はそのランクを表示し、一致しない場合（例：空白セル）は空白を返して、見た目をすっきりと保ちます。フィルハンドルを下にドラッグして、関連するすべての行に適用してください。
パラメーター：
- $B2：ランク付けに使うヘルパー値が入ったセルです。
- $C$2:$C$8：ソート済みのヘルパー値の範囲です。
注意事項：データを追加または削除した際は、各数式内のすべての範囲を新しいデータサイズに合わせて更新してください。特にリストが非常に大きい場合は、手動での範囲調整を減らすために、動的範囲やExcel テーブルの使用をご検討ください。
トラブルシューティング：ランクが欠落している、またはずれている場合は、すべてのヘルパー数式の範囲が正しく揃っているか確認してください。列間で範囲がずれていると、誤ったランク付けや意図しないエラーが発生します。
数式を使用して空白セルをスキップしながら値を降順でランク付けする
降順（最大値がランク1）でランク付けする際に、空白セルを無視したい場合は、より迅速な方法として単一の数式を使用できます。これは特にテストスコアや営業目標などのデータセットに便利で、空白セルが欠損値または未入力の情報を表しており、ランクに含まれたりエラーが表示されたりすることを避けたい場合に最適です。
最初のデータエントリと同じ行にある、結果を表示したいセルを選択し、次の数式を入力します：
=IF(ISNA(RANK(A2,A$2:A$8)),"",RANK(A2,A$2:A$8))数式を入力後、フィルハンドルを使ってその数式をデータの横方向および下方向にコピーします。この数式は、RANK 関数がエラーを返すかどうか（例：セル)A2が空白の場合など）をチェックし、エラー時は「#N/A」を表示せずに空白のままにします。セルに有効な値が含まれている場合は、適切なランクを表示します。
パラメーター：
- A2：ランク付けするセルです（データ範囲に応じて調整してください）。
- A$2:A$8：データ全体の範囲です（コピーする際は絶対参照をご使用ください）。
エラーに関する注意：「#N/A」エラーが引き続き表示される場合は、数式の参照範囲が意図したデータ範囲と一致していること、およびランク付け対象のセルに数値以外の値が含まれていないことを改めてご確認ください。
VBA を使用して空白セルをスキップしながら値をランク付けする
マクロに慣れており、空白セルを含む範囲を昇順または降順で自動的にランク付けしたいユーザーにとって、カスタム VBA マクロを使えば、複数のヘルパー列や数式の継続的なメンテナンスが不要となり、プロセスが大幅に簡素化されます。
使用方法：
1．開発タブに移動し、Visual Basicをクリックして、Microsoft Visual Basic for Applicationsエディターを開きます。「開発」タブが表示されていない場合は、次のガイドをご参照ください：Excel で開発タブを表示する。
2．新しいMicrosoft Visual Basic for Applicationsウィンドウで、挿入＞標準モジュールをクリックし、次のいずれかのコードをモジュールウィンドウに貼り付けます：
- 空白セルをスキップしながら昇順でランク付けするには：
Sub RankSkipBlank_Ascending() Dim WorkRng As Range Dim Cell As Range Dim NumArr() As Double Dim Ws As Worksheet Dim OutputCell As Range Dim i As Long, j As Long On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Please select the range to rank", xTitleId, WorkRng.Address, Type:=8) Set Ws = WorkRng.Worksheet Set OutputCell = Application.InputBox("Please select the first cell to output the ascending ranking", xTitleId, Type:=8) If OutputCell Is Nothing Then Exit Sub j = 0 ReDim NumArr(1 To WorkRng.Rows.Count) For Each Cell In WorkRng If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then j = j + 1 NumArr(j) = Cell.Value End If Next Cell Dim temp As Double Dim k As Long For i = 1 To j - 1 For k = i + 1 To j If NumArr(i) > NumArr(k) Then ' ← CHANGE HERE temp = NumArr(i) NumArr(i) = NumArr(k) NumArr(k) = temp End If Next k Next i Dim RankArr() As Double ReDim RankArr(1 To j) For i = 1 To j RankArr(i) = NumArr(i) Next i Dim RankValue As Long Dim r As Long: r = 0 For Each Cell In WorkRng r = r + 1 If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then RankValue = 0 For k = 1 To j If Cell.Value = RankArr(k) Then RankValue = k ' 1 = smallest Exit For End If Next k OutputCell.Offset(r - 1, 0).Value = RankValue Else OutputCell.Offset(r - 1, 0).Value = "" End If Next Cell End Sub - 空白セルをスキップしながら降順でランク付けするには：
Sub RankSkipBlank_Descending() Dim WorkRng As Range Dim Cell As Range Dim NumArr() As Double Dim Ws As Worksheet Dim OutputCell As Range Dim i As Long, j As Long On Error Resume Next xTitleId = "KutoolsforExcel" Set WorkRng = Application.Selection Set WorkRng = Application.InputBox("Please select the range to rank", xTitleId, WorkRng.Address, Type:=8) Set Ws = WorkRng.Worksheet Set OutputCell = Application.InputBox("Please select the first cell to output the descending ranking", xTitleId, Type:=8) If OutputCell Is Nothing Then Exit Sub j = 0 ReDim NumArr(1 To WorkRng.Rows.Count) For Each Cell In WorkRng If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then j = j + 1 NumArr(j) = Cell.Value End If Next Cell Dim temp As Double Dim k As Long For i = 1 To j - 1 For k = i + 1 To j If NumArr(i) < NumArr(k) Then temp = NumArr(i) NumArr(i) = NumArr(k) NumArr(k) = temp End If Next k Next i Dim RankArr() As Double ReDim RankArr(1 To j) For i = 1 To j RankArr(i) = NumArr(i) Next i Dim RankValue As Long Dim r As Long: r = 0 For Each Cell In WorkRng r = r + 1 If IsNumeric(Cell.Value) And Not IsEmpty(Cell.Value) Then RankValue = 0 For k = 1 To j If Cell.Value = RankArr(k) Then RankValue = k Exit For End If Next k OutputCell.Offset(r - 1, 0).Value = RankValue Else OutputCell.Offset(r - 1, 0).Value = "" End If Next Cell End Sub
3．F5キーを押してマクロを実行すると、まずランク付けしたい範囲を選択するようダイアログボックスが表示されます。その後、別のダイアログボックスが開き、ランク付け結果を配置する最初のセルを選択するよう求められます。マクロは選択したセルから順にランクを出力し、ソース範囲内の空白セルはそのまま空欄として保持します。
ヒント：
- 何も起こらない場合は、マクロが有効になっており、かつワークブック内でコードを実行する権限があることをご確認ください。
- VBA マクロの実行は元に戻せません。マクロを実行する前に、必ずデータをコピーまたはバックアップしてください。
最高の Office 業務効率化ツール
| 🤖 | KUTOOLS AI アシスタント：次に基づいてデータ分析を革新します：インテリジェント実行 ｜ コード生成｜ カスタム数式作成 ｜ データ分析とチャート生成｜ 拡張機能呼び出し… |
| 人気の機能：検索・ハイライト、または重複をマーキング ｜ 空白行を削除する ｜ データを失うことなく列の結合またはセルを ｜ 数式を使用しない四捨五入... | |
| スーパー LOOKUP：複数条件 VLookup ｜ 複数値 VLookup ｜ 複数シート間 VLookup ｜ ファジーマッチ.... | |
| 高度なドロップダウンリスト：ドロップダウンリストをすばやく作成 ｜ 連動型ドロップダウンリスト ｜ 複数選択可能なドロップダウンリスト.... | |
| 列マネージャー：指定した数の列を追加｜列の移動｜非表示列の表示状態を切り替え｜範囲および列の比較... | |
| 注目の機能：グリッドフォーカス ｜ デザインビュー ｜強化された数式バー ｜ ワークブックとシートマネージャー ｜ リソースライブラリ（オートテキスト）｜ 日付ピッカー ｜ ワークシートの統合 ｜ 暗号化／セルの復号化 ｜ リストからメール送信 ｜ スーパーフィルター ｜ 特殊フィルタ（太字のフォントを持つセルをフィルタリング／斜体／取り消し線。。。） 。。。 | |
| トップ15 ツールセット：12 テキストツール（テキストの追加、特定の文字を削除、...）｜ 50+チャートタイプ（ガントチャート、...）｜ 40+実用的関数（誕生日に基づいて年齢を計算します、...）｜ 19 挿入ツール（QR コードを挿入、パスから画像を挿入、...）｜ 12 変換ツール（単語に変換する、為替レートの変換、...）｜ 7 結合と分割ツール（高度な行のマージ、セルの分割、...）｜さらに多数 |
Kutools for Excel でExcel スキルを強化し、これまでにない効率を体験しましょう。Kutools for Excel は、生産性を高め、時間を大幅に節約できる高度な機能を300 以上提供します。最も必要な機能を今すぐ入手するにはこちらをクリック。。。
Office Tab は Office にタブインターフェースをもたらし、作業を大幅に簡単にします
- Word、Excel、PowerPoint でタブを使った編集と閲覧を有効にします。Publisher、Access、Visio、Project でもご利用いただけます。
- 複数のドキュメントを、新しいウィンドウではなく、同じウィンドウ内の新しいタブで開いたり作成したりできます。
- 日々の生産性を50％も向上させ、毎日数百回ものマウスクリックを削減します！
すべてのKutools アドインが、たった1 つのインストーラーで完結。
Kutools for Officeスイートには、Excel ・Word ・Outlook ・PowerPoint 用のアドインと Office Tab Pro が含まれており、複数の Office アプリを横断して作業するチームに最適です。
- オールインワンスイート— Excel、Word、Outlook、PowerPoint 用アドイン＋Office Tab Pro
- インストーラー1 つ、ライセンス1 つ— 数分でセットアップ可能（MSI 対応）
- 連携してさらにパワーアップ— Office アプリ全体で生産性が向上
- 30 日間のフル機能トライアル— 登録不要、クレジットカード不要
- 最高のお得感— 個別アドイン購入よりお得