「Excel Tips」ではExcelを使用していて気付いたことを取り上げて行きます。
今回はセル列に入力されている一連のデータから、2つ置きに(2つ間を空けて)データを抽出する方法について記載いたします。
Excel 2016バージョン2007(ビルド 13029.20344)を使用しています。
抽出するデータに印を付ける
例えば左図のようにA列、B列に緯度・緯度のような対になる情報がセットされて並んでいる状態で、2つ置きにデータをピックアップするというのが、今回ご説明する内容になります。
左図のようにD列に抽出列を設定して、そこに印を入れることでデータを取り出す方法を考えて見ます。
手動で印を付ける
ピックアップする条件が「2つ置き」のように数式で表せるほど単純ではない場合は、手動で印を付けるしか方法が無いかもしれません。
数が少ない時は対応できるかもしれませんが、処理しなければならない数が大量の時は人海戦術を採るにしても大変な作業になります。
また数式化できる場合でも、Excelの数式に慣れているか?によって対応が異なるかもしれません。
「2つ置きに印を付ける」ための数式を考える
いろいろなやり方があることと存じますが、つぎのような考え方にします。
行番号を「3」で割った余りが「2」の時に「値」をセットし、それ以外では「空文字」にする。
行番号を「3」で割った余りは「0,1,2」・「1,2,0」・「2,0,1」のいずれかの繰り返しになるので、余りの数字を特定すれば「2つ置き」に印を付け事ができます。
考え方の要素を関数に置き換えて行きます。
| No. | 数式の要素 | 関数 | 関数の引数/論理式 |
|---|---|---|---|
| ① | 行番号 | ROW() | 引数は「なし」 |
| ② | 「3」で割った余り | MOD(数値、除数) | 数値は「ROW()関数」 除数は「3」 |
| ③ | 「2」の時に | IF(論理式,④,⑤) | 論理式は「MOD(ROW(),3)=2」 |
| ④ | 「値」をセット | IF(③,値,⑤) | 値は例えば数字の「1」 |
| ⑤ | それ以外は「空文字」 | IF(③,④,空文字) | 空文字「””」 |
実際に先の図の抽出列にセットしている数式は下記になります。
=IF(MOD(ROW(),3)=2,1,"")
なぜ『「2」の時に』するのか?
先の図の表は、1行目はヘッダー行になっており、データは2行目からになっています。
印を付ける最初のデータをどこにするか?気にしなくても良ければ『「0」の時』や『「1」の時』でも大丈夫です。
今回は先の図で「データの先頭に印を付ける」という仕様を実装しているために、「2行目から」つまり『余り「2」』の時という条件を採用しています。
印を付けた行を取り出す(その1:並べ替え)
今となっては出番は少ない方法ですが、印をつけた列で並べ替えをするやり方です。
「データ」タブ→並べ替えとフィルターから「並べ替え」・「フィルター」
ただ、この場合先の章の④での『「値」をセット』で数字の「1」にしたままでは並び順が保証されるわけではないので工夫が必要になります。
工夫の仕方は色々あると存じますが、例えば空いているE列に「オートフィルオプション」で「連続データ」を入力して、D列とE列を合わせて並べ替えをすることになります。
なお上右図「並び替えた結果」の抽出列は数式のために2つ置きに「1」が並んだ状態になります。
これは「数式」タブ→計算方法から「計算方法の設定」で「自動」がチェックされているためで、ここを手動にして並べ替えるとD列には「1」の印を付けたセルが並ぶことになります。
なお面倒臭い工夫として、「オートフィルオプション」は使わずに④での『「値」をセット』で数字の「1」を使わずに次の数式にするやり方もあります。
=IF(MOD(ROW(),3)=2,(ROW()-2)/3+1,"")
上記数式を使うと抽出列に「1」から連続した数値が2つ置きに表示されるので、抽出列だけで並べ替えをすることができます。
ちなみに値をセットする数式ですが、「行番号を3で割ると2余る」場合なので、行番号から2を引くと必ず3で割り切れます。
ただし最初の値は「0」になるので「+1」をすることで、1から順番に連番が附番されるようにしています。
印を付けた行を取り出す(その2:可視セルのコピー)
Excel 2016ではフィルターを設定した状態で画面に表示されている内容をコピーすることができます。
D列のヘッダーのセルを選択し「データ」タブ→並べ替えとフィルターから「フィルター」をクリックするとヘッダーのセルにフィルターマーク(下▼)が付きます。
そこで「(空白セル)」のチェックを外すとセルに印を付けた行だけが表示されます。
その状態でまず先にコピーするセル範囲を指定します。
次に下記の操作をします。
「ホーム」タブ→編集から「検索と選択」→「条件を選択してジャンプ」
左図の選択オプションのウィンドウが開かれますので、「可視セル」のクリックします。
その後選択済みのセル範囲をコピーをすると右図のようにフィルターされたセル範囲が破線で囲まれます。
後は、別シートなどに貼付けすれば印を付けたところだけを取り出すことができます。
まとめ
セルに保存されている一連のデータ群から2つ置きに取り出す方法についてご説明しました。
いろいろなやり方があることと存じますが、処理しなければならないデータ量に応じて一番やり易い方法を選択されるのが良いという認識です。
慣れていないやり方に試行錯誤したとしても十分に時間短縮効果が期待されるのであれば、新しいやり方に挑戦されるのも一考です。
以上最後までご一読いただき誠にありがとうございました。
