Excelで指定した行から特定のステップ行ごとにデータを抽出する方法
Excelはデータの分析や整理に非常に有用なツールですが、特定のパターンに基づいてデータを抽出する際には少々複雑な操作が必要になることがあります。例えば、「配列や範囲から指定した行から特定のステップ数ごとに行を抽出する」というタスクは、通常のフィルター機能では実現が難しいものです。しかし、新しい関数のCHOOSEROWS関数とSEQUENCE関数を組み合わせることで、このような特定の抽出が簡単に実現できます。さらに「テーブル」と組み合わせるとより強力な抽出システムとなります。
この記事の前に(奇数 or 偶数行・レコードを抽出)
この記事では3飛び、10飛び、など自在なステップで行抽出を行う方法を紹介します。
ただ単に奇数行・偶数行の抽出をしたい場合は、このページで紹介する手順より簡単な方法があります。次のリンクではその式を紹介しています。
奇数行のみを抽出
偶数行のみを抽出
解説: 自在なステップで行を抽出する際に使用する関数
自在なステップで行を抽出する方法には、主に以下の二つの新しい関数を使用します。
- CHOOSEROWS(配列, 行番号○): 指定した配列から特定の行を選択します。ただし規則正しい行番号をたくさん入力するのは手間がかかります。そこで次のSEQUENCE関数を使って適切な数列を生成します。
配列から「2,4,6,8」行目を抜き出す設定の例
- SEQUENCE(行, 列, 開始, 目盛り): 指定された数値シーケンス(連続データ)を生成します。目盛りはステップ間隔です。
10個(10×1)の連続データを作成する設定例。初期値は2、ステップ間隔は3の例。
具体的な式の説明
行抜き出しを実現する式は以下のように構成されます。
=CHOOSEROWS(配列, SEQUENCE((ROWS(配列) - 最初の行) / ステップ数 + 1, 1, 最初の行, ステップ数))
この式の各部分を詳細に説明します。
ROWS(配列): これは指定された配列の総行数を返します。ROWS関数は配列内の行数を取り出します。最初の行: ここから抽出を開始したい行番号です。(ROWS(配列) - 最初の行) / ステップ数 + 1: この計算で、生成されるシーケンス(連続数値)の長さが決定されます。ROWS(配列) - 最初の行は、最初の行から配列の終わりまでの行数を計算します。- これをステップ数で割り、1を加えることで、必要な行数が求まります。
- 本来はこの数値を切り捨て処理すべきです。しかしSEQUENCE関数の行や列では自動的に端数処理が考慮されるので、処理を省略できます。
SEQUENCE(...): この関数は、上記の計算に基づいて数値のシーケンスを生成します。このシーケンスはCHOOSEROWS関数によって使用される行番号を表します。CHOOSEROWS(配列, ...): 最後に、この関数が指定された配列から、SEQUENCE関数によって生成された行番号に対応する行を抽出します。
事例1: サンプルテーブルでの取り出し例(標準：拡張なし)
サンプルテーブルA2:E32を使用して、実際にこの式を適用する例を考えてみましょう(1行目は見出しなので抽出しない)。
| 商品番号 | 商品分類 | 価格 | メーカー | カラー |
|---|---|---|---|---|
| 10001 | カラープリンター | 52000 | CEIRO | アイボリー |
| 10002 | デスクパソコン | 53000 | HERT | ブラック |
| 10003 | デスクパソコン | 58000 | HERT | アイボリー |
| 10004 | モノクロプリンター | 27000 | HERT | ブラック |
| 10005 | カラープリンター | 17000 | CEIRO | アイボリー |
| 10006 | ノートパソコン | 21000 | THOM | グレー |
| 10007 | モノクロプリンター | 53000 | CEIRO | アイボリー |
| 10008 | ノートパソコン | 39000 | HERT | ブラック |
| 10009 | デスクパソコン | 39000 | HERT | ブラック |
| 10010 | フルカラースキャナー | 58000 | HERT | アイボリー |
| 10011 | モノクロプリンター | 46000 | HERT | グレー |
| 10012 | ノートパソコン | 38000 | HERT | ブラック |
| 10013 | カラープリンター | 61000 | THOM | グレー |
| 10014 | カラープリンター | 63000 | THOM | ブラック |
| 10015 | モノクロプリンター | 21000 | HERT | ブラック |
| 10016 | モノクロプリンター | 16000 | CEIRO | グレー |
| 10017 | カラープリンター | 42000 | CEIRO | ホワイト |
| 10018 | ノートパソコン | 61000 | THOM | グレー |
| 10019 | カラープリンター | 36000 | THOM | グレー |
| 10020 | カラープリンター | 35000 | HERT | ホワイト |
| 10021 | ノートパソコン | 54000 | HERT | ホワイト |
| 10022 | モノクロプリンター | 48000 | THOM | ホワイト |
| 10023 | デスクパソコン | 63000 | HERT | ブラック |
| 10024 | ノートパソコン | 22000 | THOM | ブラック |
| 10025 | カラープリンター | 41000 | HERT | ブラック |
| 10026 | フルカラースキャナー | 52000 | HERT | グレー |
| 10027 | モノクロプリンター | 21000 | CEIRO | アイボリー |
| 10028 | ノートパソコン | 30000 | CEIRO | ホワイト |
| 10029 | フルカラースキャナー | 63000 | CEIRO | ブラック |
| 10030 | フルカラースキャナー | 45000 | HERT | ブラック |
| 10031 | フルカラースキャナー | 45001 | HERT | ブラック |
例えば、7行目から始めて8行ごとにデータを抽出したい場合、式は以下のようになります。
=CHOOSEROWS(A2:E32, SEQUENCE((ROWS(A1:E32) - 7) / 8 + 1, 1, 7, 8))
この式をテーブルに適用すると、7行目、15行目、23行目、31行目のデータが抽出されます。
事例2: サンプルテーブルでの取り出し例(動的・自動拡張)
リストを「テーブル」として登録しておくと、レコードを増やした際に抽出結果へも反映されるようになります。また「最初の行」や「ステップ数」をセルへ入力しておけばより使いやすくなります。
たとえば「テーブル名：テーブル商品」「最初の行→セルH1」「ステップ数→セルH2」とするならば以下の式になります。
=CHOOSEROWS(テーブル商品,SEQUENCE((ROWS(テーブル商品)-H1)/H2+1,1,H1,H2))
上記の式はテーブルを元にしているので、リストの拡張にも動的に対応します。
コメント