エクセルに関するヒント集
GenomeMatcherは比較ゲノム機能をメインに、いろいろな便利機能を追加したソフトウエアです。GenomeMatcherの機能を十分にご利用いただくために、ここではマイクロソフト社のExcellについて、知っていると便利な使い方を紹介します。またエクセルでは解決しがたい問題を解決するための機能についても紹介します。
セルからセルへのジャンプ
多量のデータを一続きで含むエクセルシートで、1番下の行に(例えば10000行目)に移動するだけで大変です。楽に移動するにはセルを一つ選択した状態で、コントロールキーを押しながら矢印キーを押します。どこにジャンプするかは、どのようなセルが選択されていたかの3つのケースによって変わります。下図を参照してください。
1) 空白セルが選択されていた場合：データが入力されているセルまで矢印の方向にジャンプ(そのようなセルがなければシートの端までジャンプ)。A4から→でC4。B4から→でC4。D1から↓でD4。
2) データがあるセルが選択されていた場合で、矢印を押した方向に少なくとも一つデータが入力されたセルがある場合：矢印の方向について隣が空白であるセルまでジャンプ。C4から→でF4。F4から→でIV4(シートの右端)。C7から↓でC11。
3) データがあるセルが選択されていた場合で、矢印の方向について隣が空白のセルの場合：データが入力されているセルまでジャンプ(そのようなセルがなければシートの端までジャンプ)。C11から↓でC13。F11から→でIV11(シートの右端)。
複数のセルが選択されている場合、選択の開始点となっているセルを起点としてジャンプします。
多数セルの選択
数百行あるいは数千行といったたくさんの行にわたっているデータを簡単に選択する方法です。これはエクセルシートを使って多量のデータを処理するには必須の方法です。
セルからセルへジャンプするときにシフトキーを同時に押します(つまり「シフト」+「コントロール」+「矢印」)。このとき通過したセル全部が選択されます。例を下に示します。C4を選択(下図左)、「シフト」+「コントロール」+「→」(下図中)。さらに、「シフト」+「コントロール」+「↓」をするとC4からF11が選択できます(下図右)。続けて「シフト」+「コントロール」+「↓」すれば、C4からF13が選択されます。
また、選択後に選択範囲を微調整する方法を紹介します。C4からF11が選択された状態から、「シフト」+「↑」するたびに選択が1行ずつ下から解除されていきます。逆に、「シフト」+「↓」するたびに選択が1行ずつ下に向かって増えていきます。
応用問題
C列1行目から1000行目までにデータが入力されており、D1からF1までにもデータが入っている。D1からF1の内容を、下の行にコピーしたい場合どうすればよいか？
解：D1からF1を選択してコピーする。次いでC列に連続してデータがあることを利用して、C1000へジャンプ(C1を選択して、「コントロール+↓」)。「→」を押すことでD1000を選択してペースト。そのまま「シフト」+「コントロール」+「↑」でD1からF1000を選択し、再度ペーストする。
ここではコピー後、セルの中身は同一となるので何が嬉しいのか分からないかも知れません。関数を使うようになるとこの操作が重要になります。
データには管理用の数字列をつける
エクセルでは「並び替え」の機能を使ってデータを並び替えることができます。並び換えを元に戻せるように、管理番号を各行に付けること、並び替えの機能を使うときには常に管理番号列を含めることを推奨します。10000行よりなるデータに1から続く管理番号を付けるには、以下のようにします。
一番上のセルに1を入力する。下のセルに=(イコール)に続けて「1を入力した番地+1」を入力し、このセルをコピーしてA2-A10000にペーストする。A2-A10000を素早く選択するには、一度B2を選択、「コントロール+↓」、「←」、「シフト+コントロール+↑」、とすると良い。このままだと数式が残ってしまうので、A1からA10000をコピーし、「形式を選択してペースト」して値のみにする。「形式を選択してペースト」は右クリックから、あるいは「編集」メニューから選択する。
エクセル使い方の初歩 式と値
エクセルでは、=から始まる文字列は「式」として評価され、式の「値」が表示されます。
セルに、
=1+2
と入力すると1+2が計算されて、その結果である3が表示されます。ここで、1+2を式、その結果である3を値と呼びます。では
=(1+2)*4
はどうでしょうか？括弧の中が優先ですのでまず1+2が計算され、括弧の中の値は3となり、それに4が乗じられて式の値は12となります。このように計算には優先順序があります。
式には、他のセルに入力されている値を含めることができます。つまり、どこかのセルに入力されている値をもとに演算を行いその値(結果)を出力させることができます。セルはその番地で指定します。仮にB1のセルに、5が入力されているとき、
=B1*6
とすればこの式は=5*6と等価となり、30が表示されます。なお、このとき、「この式はセルB1を参照している」と表現します。「参照」という言葉は使い慣れない言葉ですが、覚えましょう。
以上のように式の中では、数字、あるいはセルの番地はそのまま書くことができますが、それ以外の文字についてはダブルクオーテーションでくくる必要があります。例えば文字列の長さを返すLEN関数に文字列：ABCDの長さを返させるには、
=LEN("ABCD")
とする必要があります(式の値は4)。
エクセルでは1つの関数によって得られた値を、さらにべつの式に組み込んで計算させることができます。例えば、セルに入力されている値の和を求める関数、SUM関数がありますが、
=SUM(A1,A2,A3)
とすればA1とA2とA3のセルに入力されている数値の合計が表示されます。この合計値を3で割って平均を求めるには、
=SUM(A1,A2,A3)/3
とします。ここでA1とA2とA3の合計値が12であれば、=SUM(A1,A2,A3)/3は
=12/3
と入力するのと等価です。このように関数を式に組み込むことでより複雑な計算を実行することができます。
セルに入力されている値の参照の仕方についてもう少し説明します。
仮にセルA1に７が入力されているとします。セルB1に、
=A1*8
と入力すると56が出力されます。ではセルB1をコピーしてセルC1にペーストしてみるとどうでしょうか？表示される値は0です。セルの中身を見てみると、
=A2*8
となっています。A2には値が入っていないので、=0*8となって0と表示されているのです。セルをコピーしてペーストすると、コピーされた内容が自動的に置き換わることに注意して下さい。セルB1に=A1*8と書かれているとき、この式はセルA1を相対参照しているといいます。セルB1に入力されているこの式は、自分の番地を原点にして、参照先のセルの相対的な位置を参照しています。相対的とはこの場合「左にあるセル」というような参照の仕方です。相対参照しているセル番地を含むセルをコピーしてペーストすると、コピー先の番地にあわせて、セル番地が置き換わります。
では、下図のように、B1からB5に数値が入力されており、それぞれの数値にA1に入力された値を乗じた結果を、C1からC5に表示したい時にはどうすれば良いでしょうか？
このような場合には絶対参照を使います。絶対参照ではセルの相対的な位置関係ではなくセルの番地そのものを指定することになります。絶対参照するには、
=B1*$A$1
とします。列を示すアルファベットと、行を示す数字の両方にドルマーク"$"を付けたことに気をつけて下さい(なお、=B1*A1と打った直後に「コマンド+T」とすると、=B1*$A$1に置き換わります。さらに「コマンド+T」を押す度に、A$1、$A1、A1の順に切り替わります)。
C1に入力すると、式の値である9が表示されます。C1をコピーしてC2からC5にペーストしてセルの中身を確認すると、期待通りにA1の番地が絶対参照されていることがわかります。
相対参照と絶対参照を使いこなせるようになると何が良いのでしょうか？上の例のような場合で、いまセルA1には9が入力されていましたが、これを10に変更すると、A1を参照している全てのセルで再計算がおこなわれます。式の中の一部に、後で変更するかもしれない値がある場合、その部分を別のセル(ここではセルA1)に入力することにして、式の中ではそのセルを参照するようにしておきます。そうしておくと、後で値を変更したい時に、一カ所だけ(ここではセルA1)を変更すれば済むことになります。
また、データの量が増えるに従って、省力化のメリットが際立ってきます。うまく参照を使えば、式を一つ作り、それをコピーペーストすれば処理が済むようになります。
よく使う「IF」関数
エクセルにはSUM関数以外にも便利な関数が多数用意されています。ここではIF関数を紹介します。IF関数を使った式は以下のように書かれます。
=IF(X,A,B)
この式では、条件式Xが評価され、条件が満たされていれば式Aの値を、満たされていなければ式Bの値を表示します。言い換えると、この式は条件式Xの値がTURE(真)の時には式Aの値を、FALSE(偽)の時式には式Bの値を返します。条件式の値がTRUEである、あるいはFALSEであるとは一体どういうことでしょうか？確かめるために、セルに以下のように入力してみます。
=1<2
表示は、「TRUE」です。つまり式 1<2 の値はTRUEです。=1>2とすれば「FALSE」が表示されます。つまり条件式の値はTRUEまたはFALSEのいずれかです。つまり条件式が評価されてTRUEだったときの式は、=IF(TRUE,A,B)と等価です。実際に試してみると、
=IF(TRUE,1,0)
の式の値は1になります。同様に
=IF(FALSE,1,0)の式の値は0です。
例: A列とB列を比較して、B列の方が大きければ1を、小さければ-1をC列に表示したいと思います。
C1に入力すべき式は以下の通りです。
=IF(A1<B1,1,-1)
ここで、
=1000<1200という式の値はTRUEですから、上の式は
=IF(TRUE,1,-1)と等価です。条件式がTRUEですので式の値として"1"が表示されます。
=IF(A1<B1,"大きい","小さい")
とすれば「大きい」あるいは「小さい」が出力されます。
条件式には「空白でない場合」を指定したいことがあるかと思います。この場合の条件式は例えば
A1<>""
とします。複数の条件を指定したい場合はAND関数を使って記述します。AND関数は、与えられた全ての条件式が真のときにTRUEを、そうでないときにFALSEを返します。
=IF(AND(A1>100,B1>200),1,-1)
A1が100より大きくかつ、B1が200よりも大きければ、という条件になります。条件式はカンマで区切って複数指定できます。複数の条件のうち1つを満たせばよいのであればOR関数を使います。OR関数は与えられた式の少なくとも一つが真であるときにTRUEを、そうでないときにFALSEを返します。
OR(A1>100,B1>200)とすれば、A1が100より大きいか、B1が200より大きければ、との式になります。
文字列の連結
複数のセルにまたがっている文字列を連結するには&演算子を使います。セル番地を「&」でつなげばその内容が繋がったものが表示されます。下の例では"ABC"が返り値となります。
任意の文字を連結するには、任意の文字列を"(ダブルクオーテーション)で挟んだものを&で連結します。例えば
=A2&"_"&B2&"_"&C
とすれば返り値は「A_B_C」となります。セルの値と任意の文字列を連結できることに気を付けてください。
文字列の取り扱いに便利な関数
ゲノム情報をエクセルシートで編集していると、どうしても文字列を思った通りに取り扱いたくなるシーンが出てきます。以下の関数を上述の文字列連結演算子"&"と組み合わせてうまく使うと大体の用は足せるのではないでしょうか？いくつかの関数について説明した後にゲノム解析に有用と思われる応用例について説明します。GenomeMatcherの付属機能「StringFormatter」と併せてご利用下さい。
FIND関数: 探す対象の文字列が、何文字目にあるか返します。A5のセルにある文字列について、"_"が何文字目にあるかどうかを知るには、
=FIND("_",A5)
とします。
文字列のある位置から探したい場合は開始位置を指定します。
=FIND("_",A5,5)
とすれば5文字目から探します。
"_"がないとエラーコード「#VALUE!」が返されます。"_"があったりなかったりで、"_"があるときだけ何文字目であるかを返しないときは空白を返すには、ISERROR関数を使います。ISERROR関数はエラーの時に真を、そうでないときに偽を返します。IF文の組み合わせると以下の様になります。
=IF(ISERROR(FIND("_",A5)),"",FIND("_",A5))
まずFINDの結果がエラーかどうかを調べ、エラーならば空白を、エラーでないならFINDした結果を返しています。
LEFT関数：指定した文字列の左から、指定した文字数を抜き出します。
=LEFT(A5,3)
A5のセルに「abc_1234」とあれば「abc」が返ります。
RIGHT関数：指定した文字列の右から指定した文字数を抜き出します。
=RIGHT(A5,4)
A5のセルに「abc_1234」とあれば「1234」が返ります。
MID関数：指定した文字列の指定した位置から指定した長さの文字列を取り出します。
=MID(A5,2,3)
A5のセルに「abc_1234」とあれば「bc_」が返ります(2番目から3文字)。文字数は実際の文字列より長くなる分を指定しても構いません。=MID(A5,2,100)とすると「bc_1234」が返ります。
LEN関数：文字列が何文字であるか返します。
=LEN(A5)
A5のセルに「abc_1234」とあれば「8」が返ります(8文字)。
REPT関数：指定した文字列を指定回数繰り返したものを返します。
=REPT("0",3)
000が返ります。
<応用例1>A列に1ケタから4ケタの数字があって、4ケタに満たない部分を0で埋める式は何か？(123を0123に、55を0055に、等)。
=REPT("0",4-LEN(A1))&A1
A1が789の時、4-LEN(A1)の値は1です。REPT("0",1)の値は"0"、&演算子の前は"0"で後ろは"789"ですので、全体として"0789"が式の値となります。
<応用例2>アンダーバー("_")があったりなかったりする文字列がA列に指定されている。アンダーバーがない場合は文字列全体を、アンダーバーがある場合はアンダーバー以降を、返り値とする式は何か？
=IF(ISERROR(FIND("_",A1)),A1,MID(A1,FIND("_",A1)+1,LEN(A1)))
まずIF文で処理を分岐しています。FIND("_",A1)の結果がエラーであれば、A1が、エラーでなければ MID(A1,FIND("_",A1)+1,LEN(A1))が結果として返ります。FIND("_",A1)+1は"_"が見つかった位置の右隣の位置です。
<応用例3>A列にある文字列には「*」が2つある。「*」と「*」に挟まれた文字列を抜き出す式は何か？
=MID(A1,FIND("*",A1)+1,FIND("*",A1,FIND("*",A1)+1)-FIND("*",A1)-1)
FIND("*",A1,FIND("*",A1)+1)-FIND("*",A1)-1 の部分をよく見ると前半部分、FIND("*",A1,FIND("*",A1)+1)では 一つめの「*」の隣の位置から「*」を、つまり2つめの「*」の位置を調べています。 FIND("*",A1)は1つめの「*」の位置です。2つの位置の差からさらに1をひいた値が抜き出すべき文字列の長さとなります。
<応用例4>A列に、「123..456」あるいは「complement(789..1000)」のような形式の文字列がある。連続した2つのドットの前後にあるのはゲノム中の位置情報であり、前の数字が開始位置、後ろの数字が終了位置である。complementでない場合に1を、そうである場合に-1を返す式は何か(向きを1または-1で返す式)？また開始位置および終了位置をそれぞれ抽出する式は何か？
向きを返す式
=IF(ISERROR(FIND("complement(",A1)),1,-1)
開始位置を抽出する式
=IF(ISERROR(FIND("complement(",A1)),LEFT(A1,FIND("..",A1)-1),MID(A1,12,FIND("..",A1)-12))
IF文の条件式は、ISERROR(FIND("complement(",A1)) です。この式は「complement(」がA1にあればFALSEを返し、なければTRUEを返します。
TRUEの時は、LEFT(A1,FIND("..",A1)-1)の値、FALSEの時は、MID(A1,12,FIND("..",A1)-12)の値が、式の値になります。FIND("..",A1)-1の値は、「..」が見つかった位置の一つ前までを意味します。
終了位置を抽出する式
=IF(ISERROR(FIND("complement(",A1)),RIGHT(A1,LEN(A1)-FIND("..",A1)-1),MID(A1,FIND("..",A1)+2,LEN(A1)-FIND("..",A1)-2))
条件式は開始位置を抽出する式と同じです。TRUEの時は、RIGHT(A1,LEN(A1)-FIND("..",A1)-1)の値が式の値になります。この式ではLEN関数で文字列の長さを求め、そこから「..」の位置を減算し、さらに1を引いています。たとえばA1にある文字列が「123..456」であれば、LEN(A1) = 8、FIND("..",A1) = 4、なのでRIGHT(A1,8-4-1)となり、これはRIGHT(A1,3)となります(つまり、456)。FALSEの時は、MID(A1,FIND("..",A1)+2,LEN(A1)-FIND("..",A1)-2)が式の値になります。
この式では、抽出する開始位置をFIND("..",A1)+2で求め、抽出する長さを、LEN(A1)-FIND("..",A1)-2で求めています。
なお、GenomeMatcherの検索置換機能を使う前提で、あれば、まず向き(1または-1)のみを求めておき、次いで、
- 「complement(」を消去
- 「)」を消去、
- 「..」をタブ文字に変換
という手があります。
便利なエクセルですが、いくつかの問題については関数などを利用しても効率良く解くことができません。GenomeMatcherはそのような問題を解決するための機能を3つ備えています。RecordMatcher、DataCounter、そしてStringFormatterです。ここではこの3つの機能について簡単に解説します。
＜RecordMatcher> 例えば、郵便番号と住所の関係をエクセルシート上で持っているとします。そこには全ての郵便番号が網羅されており仮に20000件あるとします。また一方で、3000件の郵便番号を持っておりそれぞれに対応する住所を知りたいとします。こんな問題を解決してくれるのがRecordMatcherです。まずエクセルシート上で記述されている2者の関係(郵便番号-住所)をコピーペーストして入力します。次に、調べたい方(郵便番号)をエクセルシートからコピーペーストして実行すると、対応する住所を全部知ることができます。ゲノム解析ではこのような問題はしばしば登場します。例えば、1) 種名と属名の関係を知っており、1000個の種名に対応する属名を知りたい、2) ある生物種のローカスタグとアミノ酸配列の関係を知っており、1000個のローカスタグについてそのアミノ酸配列を知りたい、3)COG番号とプロダクト名を知っており、1000個のCOG番号に対応するプロダクト名を知りたい、などです。これらについて、様々なアプリケーションについているFIND機能を繰り返せば基本的には解決できますが、1000件ともなるとその労力は計り知れず1日が終わってしまうかも知れません。
<DataCounter>同じ文字列がいくつあるか数えてくれる機能です。1,1,1,1,2,2,2,2,2,2,3,3,4とあるとき1が4個、2が6個、3が2個、4が1個あります。数が少ないので簡単に数えられますが、ゲノム解析では数千件のデータについてどのような値がそれぞれがいくつあるかを数える必要があるときもあります。例えば、全部で5000個のCDSをblastpにかけたときに、トップヒットとなった配列が由来する生物種名についてその分布を調べるような時です。これについてはエクセルをつかって解決できない訳ではありませんが*、DataCounterを使うとずっと簡単に解決します。
*エクセルではこんなやり方があろうかと思います。参考までに以下に示します。
- 1: 解析したいデータをA列に納め、「並び替え」で昇順に並び替える。
- 2: B1に「1」を、B2に「=IF(A1=A2,B1+1,1)」を入力して下のセルにコピーペースト。
- 3: C1に「=IF(OR(B2=1,B2=""),1,"")」を入力して下のセルにコピーペースト。
- 4：データ全体を数値に置き換える(コピーして、形式を選択してペースト)。全部を選択してD列を昇順に並べ替え。必要なデータが上の方に集まる。
またDataCounterでは、2つのデータ集合があるときにどちらかのみに属するデータ、あるいは両方に属するデータを仕分けすることができます。集合A{A,B,C}、集合B{A,B,D}があるとき、Aのみに含まれる{C}、Bのみに含まれる{D}、共通して含まれる{A,B}を返します。5000件ぐらいのデータを順番に処理し、処理したデータについて他のシートにまとめているときに、未処理のデータを抽出することなどに使えます。この機能に関してはエクセルでは処理しがたいと思います。
<StringFormatter>エクセルシートでは1行当たり1つのデータを管理したいものです。そうでないと、関数が使いにくい、並び替えができない、等エクセルを使う上でのメリットがかなりなくなってしまいます。ではいざ1行当たり1データとすると問題が出てきます。そうですFASTA形式です。FASTA形式は
>配列の名前1
MCDSFHIRREWQPOLLRRR・・・・・
>配列の名前2
MDSKIIIYWCARRRRARRRR・・・・・
のようになっていますが、エクセルシートで編集したデータをFASTA形式にするのは結構大変です。1000個とかあれば手作業ではとても終わりません。StringFormatterはエクセルシートで１行1データで編集したデータをFASTA形式に変換する機能がついています。エクセルシートからコピーペーストしてボタンを押すだけでFASTA形式に変換できます。つまりこの機能を使うことを前提にすれば、エクセルシートで1行1データで、データを管理できることになります。
またStringFormatterは複数同時検索置換機能がついています。よく使う使い方としては、アクセッション番号が枝の末端に記された系統樹があるとき、その系統樹の元ファイルをテキスト形式で開き、アクセッション番号をその配列が由来する種名に一括で検索置換する使い方が挙げられます(どのアクセッション番号をなにに変換するかをまとめておく必要があります)。