[[フローサイトメトリー支援]]

アクセス数 &counter();   このページの最終更新 &lastmod();

**DBアクセス支援 − ExcelでJoinもどき [#uffc79fb]
Excelで、2つのシートがあって、シートA(欲しい要素が書いてあるシート)に出てくるデータを含む行のみを、シートB(元の大きなデータ)上で選ぶ、という作業をする方法を探す。

シート Sheet1
|  A   |  B  |  C  |
| abc  | 357 | ppp |
| def  | 233 | qqq |
| ghi  | 989 | rrr |

シート Sheet2
|  A   |  B  |  C  |
| uuu  | def | lll |
| vvv  | jkl | mmm |
| www  | stu | nnn |
| xxx  | abc | ooo |

のとき、~
Sheet1のA欄にある abc, def, ghi のどれかに対して、~
Sheet2のB欄がマッチするような[行]だけを抽出した表を作りたい。


ExcelのMATCH関数と、フィルター機能と、並べ替え機能を使うと、できる。
-Sheet2のそっくり同じコピーを作り(Sheet3と名付ける)そこへ操作をする。(シート内容を上書きして書き直してしまうため)
-Sheet3上の追加の列を使う。(右側に空いている列でもよいし、列を挿入してもよい)(下記の例では列Dとする)~
シート Sheet2
|  A   |  B  |  C  | D  |
| uuu  | def | lll |  |
| vvv  | jkl | mmm |  |
| www  | stu | nnn |  |
| xxx  | abc | ooo |  |
-セルD1に、関数MATCHを書く。(後で、それを他のすべての行のD列にコピーする)
--関数MATCHは3つのパラメータを取る。1つ目は、このシート上の「検査」(=比較)の対象となる欄の指定で、具体的にはSheet2のB欄になるので、B1(1行目なのでB1)である。
--2つ目のパラメータは、Sheet1の方にある比較対象で、書き方は Sheet1!A:A (シートSheet1の欄Aの最初から最後まで)である。
---シート名の書き方であるが、Sheet1のように英数字で特殊文字や空白が無ければ、そのままSheet1と書けば良いが、特殊文字や空白がある場合、シート名の両端を一重引用符(')で囲む。たとえば、'Data for Another Experiment (2)!A:A' のように、である。
--3つ目のパラメータは、比較の仕方で、-1か0か1であるが、一致するものを選択するのであれば0を書く。詳しくはMATCHのヘルプを参照。ワイルドカードもできるらしい。
--例: =MATCH(B1,Sheet1!A:A,0) と書く。第1パラメータはB1、第2パラメータはSheet1!A:A、第3パラメータは0である。 各パラメータの間はコンマのみで、余分な空白を置かないこと。 ~
Enterキーを押すと計算結果の値として、数字か#N/Aが表示される。数字が表示されるのはSheet1にマッチするものがある場合でSheet1上の何行目のエントリーとマッチしたかの番号(1, 2, 3, ...)が表示される。#N/Aが表示されるのはSheet1にマッチするものが無い場合である。(どちらでもない場合はMATCH関数の引数の書き間違いである)
|  A   |  B  |  C  | D  |
| uuu  | def | lll | 2 |
| vvv  | jkl | mmm |  |
| www  | stu | nnn |  |
| xxx  | abc | ooo |  |
-セルD1を残りのD列のセルにコピーする。簡単なのは、セルD1を選択した状態でそのセルの右下の角の小さな黒四角■を下方向へドラッグする(マウスの左クリックを押したままで下へ引っ張る)のがよい。クリックを放したところまでコピーされている。~
コピーした結果は関数が計算(評価)されているので、表示される値が、数字か#N/Aになっているはずである。~
なお、余談だが、よく見ると(コピーした先のセルの内容を見ると)、行番号に合った形でコピーされていることに注意。つまり2行目であれば =MATCH(D2,Sheet1!A:A,0 になっているはずである。Excelではこうなる。
なお、余談だが、よく見ると(コピーした先のセルの内容を見ると)、行番号に合った形でコピーされていることに注意。つまり2行目であれば =MATCH(B2,Sheet1!A:A,0) になっているはずである。
|  A   |  B  |  C  | D  |
| uuu  | def | lll | 2 |
| vvv  | jkl | mmm |#N/A|
| www  | stu | nnn |#N/A|
| xxx  | abc | ooo | 1 |
-次に、「フィルター機能」を使って、数字が出ている行だけを抜き出す。
--まず今作った表(Sheet3)全体を選択しておき、Excelの「データ」タブを選んで、「フィルター」をクリックし、「オートフィルタ」を選ぶと、各列の右肩にオートフィルタ矢印(選択マーク)が出る。これを左クリックするとプルダウンメニューが出て、その中から「数値フィルター」⇒「指定の範囲内」を選ぶ。
--条件の指定のメニューが出るので、たとえば1以上、1000以下(考えられる最大値=シートSheet1での行数の最大値)を指定する。OKする。
--その結果、範囲内の数値の行のみが残り、他は(#N/Aも含めて)消えてしまう。~
これで、Sheet1とマッチする行のみが残ったはずである。
|  A   |  B  |  C  | D  |
| uuu  | def | lll | 2 |
| xxx  | abc | ooo | 1 |
-次に、シートSheet1と合体するために、今抽出されたSheet3の行を、Sheet1の行の順番にそろえる。幸い、MATCHの結果はSheet1の方の行番号に相当する整数が入っているので、これを基準にしてソートする。
--対象となるSheet3のデータ全体を選択しておき、Excelのデータタブで、並べ替えを選択する。
--先ほど追加した、MATCHでの一致行番号を示す列(例ではD列)をソートのキーに設定する。昇順とする。
--これでソートすると、一致行番号の順に並ぶ。
|  A   |  B  |  C  | D  |
| xxx  | abc | ooo | 1 |
| uuu  | def | lll | 2 |
もしSheet3のデータが、Sheet1に対して抜けがなければ (つまり、一致行番号が1から順にすべての番号がそろっていれば)、Sheet1とSheet3は、そのままコピー&ペーストで合体できる。上記の例では1と2しかないので、Sheet1の3行目に相当する部分は存在しない。~
念のため、Sheet1とSheet3の行数を比較しておくとよい。
-運よくSheet3とSheet1の行が一致していれば、そのままコピー&ペーストで表を合体できる。一致していなければ、行の順番は同じになっているので、Sheet1には存在するがSheet3に存在しない行を探し出して、位置を調節しなければならない。
-抜けがあっても行位置を併せるには、ExcelのマクロMergeが使えそうだが、未調査。

トップ   編集 差分 バックアップ 添付 複製 名前変更 リロード   新規 一覧 単語検索 最終更新   ヘルプ   最終更新のRSS