化学系のための覚えておきたいExcel操作

夜更けまでマイクロピペットを振り回す実験第一主義の化学者にもデータ処理の時間はやってきます。データ処理に使うソフトはきっとエクセル。それなのに、エクセルの使い方を調べても会計や在庫管理の話ばかり。それもそのはず、ほとんどのユーザーはビジネスに使っているのだから。

ワード、エクセル、パワーポイントが理系大学生のマストアイテムになっているにも関わらず、使い方を教わる機会はほとんどありません。大学の授業に演習があるか、配属された研究室の先輩や先生に教わるか。化学系の人は情報系に疎い場合もあり、知っていれば便利な機能を知らずに気合でどうにかしてもらうことも。それじゃあ悲しいので、私が研究に使う機能をまとめようと思います。

エクセル編

書いてあることがよくわからなかったら、まずは書いてある通りに手を動かしてみよう。使っているうちにわかる。

・キー操作、ショートカット

マウスでの操作は簡単で直感的だが、いちいちメニューを何回もクリックしたり、興味があるセルにカーソルを移動させるために机の上で腕を大きく動かさないといけない。これに対して、キー操作なら曖昧さのない操作をほとんど腕を動かさずにできる。エクセルを仕事で使うなら、キー操作に慣れるべきである。

一般に、xx + yyという表現を、xxキーとyyキーを同時に押すという意味で用いる。なお、Ctrl, Shift, Altキーについては押す順番を問わないので、順番に押していけばよい。文字のキーは、Ctrlキーなどを押した後に押すこと。

Ctrl + F

検索と置換ボックスの検索タブを開く。検索ボックスに語句を入力すれば、シート全体から一致する箇所をピックアップしてくれる。
コンピュータ上のデータはコンピュータに探させればいい。目で追っていくのは愚かだ。このページの文章についても、検索をつかって効率的に欲しい情報を見つけてほしい。

Ctrl + HまたはCtrl + F > 置換

検索と置換ボックスの置換タブを開く。
ある語句をある語句で置き換えたいとき、セルをひとつずつ探しているのは効率が悪い。置換タブで検索語と置換語を指定すれば、自動で置き換えられる。
「すべて置換」を押すと条件に合う検索語はすべて置換される。検索でひっかかったセルを確認しながら置換を行いたいときは、置換していい場合に「置換」置換したくない場合に「次を検索」を押せばいい。

Enter

下隣のセルに移動する。
Shift + Enterなら上隣のセルに移動する。

Tab

右隣のセルに移動する。
Tabを押して移動したセルに値を入力し、さらにTabを押して右隣に移動することができる。この状態でEnterを押すと、最初にTabキーで移動し始めたセルの下隣りのセルに移動する。
ノートの手書きの数字をエクセルに入力する際に便利。
Shift + Tabなら左隣のセルに移動する。

PageUp, PageDwon

画面1ページ分だけ上または下に移動する。
長い配列のデータを見ながら移動したいときに画面の移動の仕方が見やすい。

Ctrl + 矢印キー

もともと選択されていたセルの隣が空欄なら、矢印方向の値が入っているセルに飛ぶ。
もともと選択されていたセルの隣が空欄でないなら、矢印方向の値が入っている連続した配列の最後に飛ぶ。

Shift + 矢印キー

選択されていたセルから、矢印方向に選択範囲が広がる。複数のセルを選択したいときに使う。
マウスで選ぶのよりも早くて正確。

Ctrl + Shift + 矢印キー

選択したセルから連続する値が入った領域の最後まですべて選択する。
マウスで選ぶのよりも早くて正確。

Ctrl + A

全選択。値が入っているセルを選択している場合、連続する値が入っている領域をすべて選択する。一見Ctrl + Shift + 矢印キーより便利そうだが、サンプル名やデータラベルなどの文字列もすべて選択するので、装置が出したファイルの配列のうち、一部のみを選択したいときには少し使いづらい。
なんでもいいから全部選択したいときはとても便利。

応用例: スペクトルデータの全選択

スペクトルの測定データはたいてい数値が一列に並んだデータ形式になっている。
装置が吐き出したcsvなどのファイルをデータ処理用のエクセルファイルにコピペすることを考える。
スペクトルの先頭のセルを選択して、Ctrl + Shift + 下矢印キーでまず列方向のデータを全選択する。さらにCtrlを押し続けたまま、Ctrl + Shift + 右矢印キーで選択範囲を複数列に広げる。
複数列のデータを選択するのに、マウスで列のラベル(A列、B列と書かれているところ)を押せばいいのだけれど、列選択は列を丸ごとコピーするときにしか使えない。コピー先のシートの先頭行にサンプル名を入れたりするとコピペできないというトラブルにつながる。したがって、必要なセルだけを選択するこの方法が良い。

Ctrl + C

選択したセルをコピーする。最も有名なショートカットかもしれない。このとき、セルの値だけでなく、セルの色や枠といった書式についてもコピーされていることに注意する。

Ctrl + V

コピーしたものをペーストする。コピー元のセルが数式だったり、色や枠付だったりすると、丸ごとペーストされる。数式がコピーされることを認識していないと、相対参照がズレてエラーになることがある。
エクセル以外からコピーすることもできる。数値、文字列、なんでもござれ。

Ctrl + Alt + V

コピーしたものを、形式を選択してペーストする。値だけ、数式だけ、書式だけといったコピペが可能なので、意外と重宝する。
特に、値だけコピペはよく使うことになるだろう。たとえば、Googleで「ボルツマン定数」を検索すると、文字サイズの書式情報が付いた数値が表示される。そのままコピペすると大きな文字がセルに入るので、「テキストとして貼り付け」を行う。まあ、このときは「=」を入力したあとにCtrl + Vでもいいのだけれど。
他には、別のシートで計算した結果の値だけを入力したいとき、他人に見せるために色や枠を丁寧に設定した表を作るときにも使える。

F4

相対参照と絶対参照を入れ替える。F4を押すたびに、行列両方絶対参照、行だけ絶対参照、列だけ絶対参照、行列両方相対参照と変わる。
エクセルでセルに数式をつくったとき、どこのセルの値を参照するかは相対位置で決まっている。これを相対参照といい、セルの中には「=C3」のように記入されている。一方で、計算に使う定数のように、とあると値を複数のセルから同じように参照したいときがある。このときは数式をコピペしても、セル番地が変わらないような参照方法である絶対参照を用いる。絶対参照をするには、固定したい行または列に$マークをつける。A1セルに絶対参照したければ、「=$A$1」と入力する。これをいちいちセルをクリックして文字列を修正するのは面倒なので、F4キーを押してやればいい。

応用例: モル吸光係数の算出

モル吸光係数は、光路長1 cmの吸光度をモル濃度で割ることで求められる。吸収スペクトルのデータからモル吸光スペクトルのデータを算出するには、「=[吸光度のセル]/[モル濃度のセル]」とセルに入力すればよい。
ここで、試料のモル濃度をB1セル、試料の吸光度がB2セルからB100セルに入っている場合を考えよう。まず、C2セルに「=B2/$B$1」と入力する。C2セルを選択した状態でセル右下の黒四角をダブルクリックして、オートフィル機能を使う。すると、C2セルに入力した数式と同じような数式が自動で入力され、C2からC100セルにモル吸光係数のスペクトルが計算される。
さらに、複数の試料のデータがある場合を考えよう。サンプルXのモル濃度がB1セル、吸光度がB2セルからB100セルに入っており、サンプルYのモル濃度がC1セル、吸光度がC2セルからC100セルに入っているとする。まず、D2セルに「=B2/B$1」と入力する。B$1のBには$がついていないので、行は絶対参照で列は相対参照になっている。D2セルをコピーしてE2セルにペーストすると「=C2/C$1」になる。D2セルとE2セルを選択した状態で、オートフィルを使うと、B列からD列へ、C列からD列へ、計算する式ができる。

F6, F7, F8, F9, F10

日本語を打つ時のように全角モードで入力している際に、ファンクションキーを押すと文字の種類を変えられる。
F6はひらがな、F7はカタカナ、F8は半角カタカナ、F9は全角アルファベット、F10は半角アルファベット。F7とF10はよく使うと思う。会社のルールなどのくだらない理由で半角カナや全角アルファベットの入力強いられた場合は、F8やF9を思い出してほしい。

F1

ヘルプの表示。F1でヘルプは多くのソフトで共通の挙動。
周囲にコンピュータに詳しい人が居ない場合、こんなことができないかな、と思ったら、ヘルプを見るかGoogleで検索すると、案外解決する。

Ctrl + N

新しいまっさらなファイルをつくる。

Ctrl + WまたはAlt + F4

開いているファイルを閉じる。

Ctrl + S

上書き保存。まだ保存されていないファイルは名前を付けて保存ダイアログがでる。
PCがフリーズしてデータが飛ぶと悲しいので、こまめにCtrl + Sで保存する習慣をつけよう。

Ctrl + O

ファイルを開く。
csvをインポートするときに、Ctrl + Oでファイルを開くダイアログを出して、データの入っているフォルダを開いて、Ctrl + Aでまとめて選択して開くと楽かもしれない。

Ctrl + B

太字にする。

Ctrl + I

斜字にする。

・簡単な計算

四則演算

数値セルと数値セルの間で計算式をつくればいいだけ。
セルの先頭に「=」を入力して、そのあとに数式を続ける。
計算記号は演算子と呼ばれる。足し算は「+」、引き算は「-」、掛け算は「*」、割り算は「/」を用いる。

指数、対数

指数の演算子について、べき乗は「^」を用いる。
特に、自然指数関数の場合はexp関数を使い、「=exp()」の()の中に式(引数)を入れる。

対数の場合は、log関数を用いる。
特に、常用の対数のためにLOG10関数、自然対数のためにLN関数がある。

文字列の結合

異なるセル間の文字列を結合した文字列をつくるには、計算の演算子と同じように「&」でセルや文字をつないでやればよい。
たとえば、A1セルに「サンプル」という文字列、A2セルに「1」という数字が入っているときに、「=A1&A2」という式をつくると、「サンプル1」という文字列が出る。数字が自動で文字列になっている。
数式では数字を直接入力できるが、文字列は””で囲んだ部分が文字列として認識される。上の例でいうと、「=A1&”#”&A2」という式にすると、「サンプル#1」という文字列が出る。

応用例 グラフの系列名を実験条件から生成する

文字列の結合を使うと便利なのが、グラフに凡例を表示する場合である。凡例にデータの系列名を表示させるとき、サンプル名や測定条件を系列名にすることが多いだろう。あとからデータ処理をすることを考えると、試料名、試料の作成条件、測定条件、測定番号などを別々の行なり列なりに入れておくべきである。一方で、グラフに表示させる系列名としては、これらの情報をまとめて表示させたいものである。そこで、系列名用にセルを空けておき、そのセルに文字列の結合をつかって試料名などの情報をまとめた文字列を生成するとよい。いったん文字列を結合する式を作ってしまえば、あとはコピペでよいので、たくさんの試料を測定する際にはかえって楽になる。統一されたスタイルで系列名をつくれて、かつ後から修正する際も容易である。

たとえば、系列名のセルに「フルオレセイン 励起波長488 nm」と書かれていたとする。同じ条件でフルオレセインイソチオシアネートを測定した場合は、系列名をコピペした後にセルを選択して、「フルオレセイン」を「フルオレセインイソチオシアネート」に書き換えなければならない。
さらに、あとから「励起波長」を「Ex.」に書き換えたいと思ったら、いちいちセルの中身を書き換えるか、検索と置換ダイアログを使って置換をする必要がある。
ここでもし系列名のセルが「=[試料名のセル]&” 励起波長”&[励起波長のセル]&” nm”」と書かれていれば、セルの式を「=[試料名のセル]&” Ex.”&[励起波長のセル]&” nm”」に書き換えてコピペするだけですべてのサンプルの系列名を容易に修正することができる。

余談だが、文字列の結合は文字列の分解よりもはるかに容易である。姓と名が別のセルに入っているところからフルネームを算出するのは簡単だが、その逆はどこで姓と名が切れるのかコンピュータには判断が難しい。このようなデータ処理の性質を認識したおくと、コンピュータを使って仕事の効率を上げるのに役立つ。

・関数

SUM, PRODUCT

MAX, MIN

Average, STDEV

VLOOKUP

INDEX, MATCH

IF

SUMIF

COUNT, COUNTA

COUNTIF

LINEST 線形回帰の結果、傾きや切片を配列として出力する。切片は0など、回帰の条件を付けられる。

SLOPE 線形回帰の傾きを求める。回帰の条件は付けられない。

INTERSEPT 切片を求める。

・データ分析

・VBA

関連情報

化学工学会 Excelで解く化学工学10大モデル

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください