カテゴリー別アーカイブ: 自習用

必要なサンプルサイズの求め方

測定には不確かさが付きまとう。n回測定したデータを平均して、実験値とするのが一般的である。n回とは具体的に何回測定したらいいのかを見積もりたい。

n回の測定値を単純平均して得られた値をAverage (ExcelでAVERAGE)、不偏標準偏差をSD (ExcelでSTDEV) とすると、標準不確かさは SD/sqrt(n) で求めることができる。したがって、実験値の求め方としては Average ± SD/sqrt(n) となる。

不確かさを持った値を用いて、何かしらの理論式に代入するなどの計算を行う場合は、不確かさの伝搬を考える必要がある。具体的な計算は東北大多元研のページを見ると便利。

実験値が求まったら、次は実験値の不確かさを目標以下に小さくすること考えたい。母平均の区間推定における必要なサンプルサイズの計算フォームを参考にすると、平均値μから±δの区間にあることを有意水準5%で示すのに必要なサンプルサイズnは

n > (1.96 * (STDEV/δ))^2

物理ではa±bという実験値に説明がなければ、通常bは「1シグマ」である。有意水準5%というと95%信頼区間を求めていることになるが、1シグマだと68%信頼区間を求めることになる。

n > (1.0 * (STDEV/δ))^2 = (STDEV/δ)^2

 

統計やるのは久しぶりすぎて理解してないので、要復習。サンプルサイズの決め方自然科学の統計学あたりを見てみるとしっかり学べそう。

そもそも実験誤差って何?というレベルの人には誤差の理論(東京医科歯科大)を見せるのが楽そう。

なお、雰囲気で統計をやっている人のお約束として、サンプルサイズ (標本の大きさ) とサンプル数 (標本数) を混同するというのがある。使いやすい踏絵である。

日付の加算減算 (VBA)

毎月更新する帳簿に使用期間を自動で記入するプログラムを作成することを考えた。エクセルのVBAでは日付をDateで取得できる。さらに、年、月、日、時、分、秒のどの部分を取り出したいかはFormatで選択できる。たとえば Format(Date, “yyyy.mm”) とすれば、現在の年月が取得できる。

今月の21日から来月の20日までという表示をしたい場合はどうすればよいか。今月の21日は文字列の結合を使って  Format(Date, “yyyy.mm”) & “.21″と書ける。それでは、来月の20日はどうするか。単純に数を加算する式 Format(Date, “yyyy.mm”) + 0.01 & “.20” を書いてみるとそれっぽく動作するが、繰り上がりが起きる9月から10月や、12月から1月がうまく動作しない。
ここで安易に場合分けをして書いてはいけない。DateAdd関数を使えば、日付や時刻の計算は簡単にできる。使用例はDateAdd VBAで検索すればたくさん出てくる。加算する値を負にすれば減算にもなるので、前の月や前の年といった過去の日時も取得できる。

例: 先月21日から今月の20日
Dim date_previous As Date
Dim date_now As Date

date_previous = DateAdd("m", -1, Now)
date_now = Date

Sheets("hoge").Cells(1, 1).Value = Format(date_previous, "yyyy.mm") & ".21~" & Format(date_now, "yyyy.mm") & ".20"

化学系のための覚えておきたい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大モデル

GoogleフォームとGASでつくる同報メール送信システム

特定のグループのメンバー全員に連絡をしたいことがあったときに、いちいち名簿を見て誰がメンバーだったかを確認するのは面倒です。さらに、複数のグループや個人が入り乱れて使用する部屋の使用者全員に連絡したいときは、より面倒が増します。そこで、Googleフォームから宛先のグループを選択すれば名簿からメールアドレスを取得して、該当者にメールを送ってくれるシステムをつくってみました。

仕事の効率化のためにプログラミングを勉強しはじめたというレベルなので、動けばよいくらいの適当なコードです。

Googleフォームでの準備

以下のようなフォームを作成する。送信者のメールアドレス、宛先、メールのタイトル、メールの本文という順番を前提として後述のプログラムを作成した。

Googleスプレッドシートでの準備

フォームの回答が保存されるスプレッドシートを用意する。フォームの編集画面から、「回答」タブを開くとスプレッドシートへのリンクが出てくる。

フォームの回答が保存されているスプレッドシートに、グループの名簿(groupシート)と部屋使用者の名簿(roomシート)を作成する。シートの構成は、1行目にグループ名や部屋名、2行目以降にグループ名か個人のメールアドレス。1列目に説明、2列目以降に中身。フォームからの回答が記録されるシートの名前はanswerに変更する。このあたりはコードの対応が取れていればいいのでお好みで。

スプレッドシートのメニューからスクリプトエディタを開き、以下のコードをコピペ。トリガーにフォームの値送信時を設定する。コードの中のスプレッドシートのidを変更する。

<br />
function sendmail() {<br />
  var file = SpreadsheetApp.openById(&quot;スプレッドシートのid&quot;);<br />
  var answer_sheet = file.getSheetByName('answer');<br />
  var group_sheet = file.getSheetByName('group');<br />
  var room_sheet = file.getSheetByName('room');</p>
<p>  //フォームに入力された値を読み取る<br />
  var rowtoread=answer_sheet.getDataRange().getLastRow(); //フォームの回答が記入された行を求める<br />
  var answer = answer_sheet.getRange(rowtoread,2,1,4).getValues(); //フォームの回答を取得</p>
<p>  var sendfrom = answer[0][0];<br />
  var sendto = answer[0][1];<br />
  var title = answer[0][2];<br />
  var message = answer[0][3];</p>
<p>  //グループのアドレスリストを入手する<br />
  var groupnum = group_sheet.getDataRange().getLastColumn()-1; //グループ数を取得<br />
  var grouplist = group_sheet.getRange(1,2,1,groupnum).getValues(); //グループ名のリストを取得</p>
<p>  var group_lastrow = group_sheet.getDataRange().getLastRow();</p>
<p>  //宛先がGroupの場合。宛先リストにメールアドレスを追加する。<br />
  var sendtolist = [];<br />
  appendfromgroup(sendto, sendtolist, group_sheet, groupnum, group_lastrow);</p>
<p>  var roomnum = room_sheet.getDataRange().getLastColumn()-1; //room数を取得<br />
  var roomlist = room_sheet.getRange(1,2,1,roomnum).getValues(); //room名のリストを取得</p>
<p>  var room_lastrow = room_sheet.getDataRange().getLastRow();</p>
<p>  //roomシートのroom構成員を宛先にいれる。<br />
  for(var k = 2; k &lt;= roomnum + 1; k++) {<br />
    if(sendto === room_sheet.getRange(1,k).getValue()) {</p>
<p>      for(var l = 2; l &lt;= room_lastrow; l++) {<br />
        //roomの構成員がgroupの場合<br />
        var temp = room_sheet.getRange(l,k).getValue();<br />
        if (grouplist[0].indexOf(temp) &gt; -1){<br />
        appendfromgroup(temp, sendtolist, group_sheet, groupnum, group_lastrow);<br />
        }<br />
        //roomの構成員がgroupでない場合、メールアドレスが入力されていると判定<br />
        else{<br />
          sendtolist.push(temp);<br />
        }<br />
        }<br />
      }<br />
    }</p>
<p>  //重複をのぞく<br />
  var sendtolist_true = sendtolist.filter(function (x, i, self) {<br />
            return self.indexOf(x) === i;<br />
        });</p>
<p>  //メール本文の修正<br />
  message = &quot;宛先: &quot; + sendto + &quot;\n送信者: &quot; + sendfrom + &quot;\n\n&quot; + message;</p>
<p>  //メール送信<br />
  MailApp.sendEmail(<br />
      sendtolist_true, //toアドレス<br />
      title,  //メールタイトル<br />
      message, //本文<br />
      {<br />
        from: sendfrom, //fromアドレス<br />
        cc: sendfrom, //送信依頼者にccで送る<br />
        name: &quot;同報メール送信フォーム&quot; //差出人<br />
      }<br />
    );<br />
}</p>
<p>function appendfromgroup(sendto, sendtolist, group_sheet, groupnum, group_lastrow){<br />
  //宛先リストに該当するメールアドレスを追加する<br />
  i = 0;<br />
  for(var i = 2; i &lt;= groupnum + 1; i++) {<br />
    if(sendto === group_sheet.getRange(1,i).getValue()) {<br />
      for(var j = 2; j &lt;= group_lastrow; j++) {<br />
        sendtolist.push(group_sheet.getRange(j,i).getValue());<br />
      }<br />
    }<br />
  }<br />
  return sendtolist;<br />
}</p>
<p>

フリーの動画変換ソフトffmepgの使い方

仕事で動画を撮っていると、データのファイルサイズが大きすぎて困る。他人に見せるときはファイルサイズが小さい方がいいし、imageJなんかでデータ解析するときには非圧縮の方が都合が良い。動画の変換で良いソフトウェアはないものかと探していると、ffmepegに出会った。

インストールの仕方

windowsにffmpegをインストールする

自分のPCにあったファイルをダウンロード、解凍して適当な場所に置けばOK。マイドキュメントなど。

とりあえず、スタートメニューからコマンドプロンプトを起動する。C:\users\hoghogeのように現在のフォルダのパスが表示されるので、ffmpeg.exeのあるフォルダに移動する。コマンドプロンプトでフォルダを移動する場合はcd hogehogeとする。ちなみに”\”は”¥”と表示されるけれど気にしない。

例 cd c:\ffmpeg\bin

網羅的な説明

ffmpegの使い方

最新ffmpegのオプションまとめ コマンドの意味を調べる辞書として使える

実施例

windows7でも再生できる動画を作るためのffmpeg / avconv の cheat sheet

動画処理の定番ツール「FFmpeg」ことはじめ

古いWindowsで再生できない

会社の支給PCだと最新のOSを使えるとは限らない。たとえば、標準のWindows7ではH.264のmp4を必ず再生できるわけではない。

また、H.264の動画をPowerPointに埋め込む場合も、PowerPoint2010以降である必要がある。

YUVフォーマットもちゃんと設定してやる。

結局のところなんて打てばいい?

ffmpeg -i example.avi -c:v libx264 -movflags +faststart -vf format=yuv420p example.mp4

仮にtest1フォルダに入っているexample.aviをtest2フォルダに変換したい場合は以下のように書ける。あるいは、C:\からファイルのパスを書いてもいい。

ffmpeg -i test1\example.avi -c:v libx264 -movflags +faststart -vf format=yuv420p test2\example.mp4

これでおそらくWindows 7のエクスプローラでサムネイルが表示され、Media playerで再生できる形式の動画になる。非圧縮に比べて数十分の1のファイルサイズになる。

環境が古くてWMV形式にしたい場合は、wmvでエンコードする(PowerPoint用動画)を参照する。ビットレートのデフォルト設置値が低いので、必ず自分でビットレートを設定する。

AndroidやiOSのスマホ、タブレットで再生したい場合は、とりあえず上の通りのコマンドで再生できるファイルができるはず。

動画をPowerPointに埋め込む

もっと手軽にしたい。batファイルでドラッグアンドドロップ

決まった形式に変換するだけなら、batファイルを作っておくと楽。元動画ファイルをbatファイルにドラッグアンドドロップすれば変換された動画が出力されるようにできる。

[Windows]ffmpegとbatファイルで快適ドラッグ&ドロップ変換が複数ファイルの一斉変換に対応している。
FFmpegの使い方 ドラッグアンドドロップするは単一ファイル処理に絞って短いコマンドで書いていている。

リンク先で紹介されているコマンドのうち、ffmpegの設定に関わるところを自分の好きなように変更する。