【Excel】CSVファイルが文字化けする時
必要なデータをCSVファイルとしてダウンロードした時、時々文字化けして困ることがある。 これは、文字コードの違いであることがほとんど。 文字コードの違いが原因かどうか確認する方法 文字化けの原因が文字コードの違いによるものかどうかを確認するために、ワードパットなどのテキストエディタで開いてみる。 テキストエディタでは文字化けしていない。 文字化け解消方法①：テキストエディタで文字コードを […]
TAG
Excel
【Excel】カット（コピー）したセルを挿入する時のショートカット
エクセルでセルをカット（コピー）して別の場所に挿入する場合、 今までは、 １．セルをカット「Ctrl + X」or コピー「Ctrl + C」 ２．挿入先を選択 ３．「Shift + F10」 (右クリック) ４．「Alt + I」（カット or コピーしたセルを挿入） ５．元からあったセルを下に移動 としていたけど、もっと簡単にできる方法を見つけたのでその備忘録。 カット（コピー）したセル […]
【Excelマクロ／VBA】PERSONAL.xlsm/PERSONAL.SLXB を開く
エクセルでマクロを personal.xlsm に記録・呼び出しするためには、PERSONAL.xlsm を開いておかなければならない。 その保存先についての備忘録。 1. 「Win + E」でファイルエクスプローラーを開く。 2. 「Ctrl + L」でアドレスバーに移動 3. 「C:\Users\ユーザー名\AppData\Roaming\Microsoft\Excel\XLST […]
【Excelマクロ／VBA】開発タブの表示
マクロ登録する時に、開発タブ（Developer）があったほうが便利。 でも、デフォルトでは非表示になっている…… ので、表示させる方法 ↓ 開発タブの表示 １．「ファイル（File）」 ２．「オプション（Options)」 ３．「リボンのカスタマイズ（Customize Ribbon）」 ４．右側にある「開発（Developer）」にチェックを入れる。 […]
【Excelマクロ／VBA】Can’t Execute Code in Break Mode
家で作ったVBAをクラウドに上げて、職場で実行しようとした時に出てきたこの表示。 あれ？と思って調べた時の備忘録。 前置き：別の場所で作ったVBAの参照元を変更した まず、クラウドに上げておいたVBAは、自宅PCの保管場所を指定しているはず。 試しにそのまま RUN したら、普通にストップした。 VBAを開いて、下記部分のデータ参照先を 新たに保存したフォルダに変更。 フォルダの場所は、下記方法で […]
【Excelマクロ/VBA】マクロ実行ボタンの設置
ボタンをポンっと押すだけで自動でマクロを実行できるようにしたい……という場合。 こんな感じでボタンを作っておくと便利 ↓ マクロ実行ボタンの設置 １．「開発」タブ → 「挿入」→ 「ボタンマーク」を選択。 ２．任意の場所にボタンを挿入する。 ３．どのマクロを実行するか選択。 ４．できあがり。ボタンを押すと、指定したマクロが実行される。 複数のマクロ毎にボタ […]
【Excel】数式を列全体に一括で適応
普段からオートフィルにはお世話になっているけど、100セル以上とかオートフィルするのは、地味に時間がかかる。 ので、数式を列全体（とか長いセルとか）に一瞬でオートフィルする方法↓ 数式を列全体に一括で適応 方法１：数式を先に入れておく方法 例えば、下記のように、S7に数式を入れておいて、S100くらいまで相対参照でコピーしたい場合。 １．S7に数式を入れる。 ２. 「Ctrl + G」もしくは「F […]
【Excelマクロ/VBA】Personal Macro Workbook in Startup Folder must stay open for recording
マクロを登録しようとしていたら、下記表示が出て先に進めなくなった。 Personal Macro Workbook in Startup Folder must stay open for recording ……ちょっと前に別のウィンドウを消していってて、誤って Personal Macro Workbook も消してしまったらしい。 で、再度 open させる方は下記。 &nbs […]
【Excel】01, 001…という形で記入する方法
エクセルで、「01」と記入しても、自動的に「1」と変換されて困る事がある。 特に、ID番号とか付けたい場合は、順番がずれないように「01」とか「001」にしたい。 この場合は、「書式設定」を変更する。 01, 001… という形で記入する方法 問題のセル（もしくは行や列）を選択し、 右クリック（もしくは「Shift + F10」）。 「セルの書式設定（Format Cells̷ […]
【Excel】セルをドラッグできなくなったら……
ある時期から、自宅PCのエクセルでセルのドラッグができなくなった。 オートフィルとか、ページセクションとか、トラックボールでできなくって結構不便……。 で、知らべたら解決方法が。 セルをドラッグできるようにする設定方法 １．「ファイル（File）」 ２．「オプション（Options)」 ３．「詳細設定（Advanced）」 ４．「フィル ハンドルおよびセル […]
【Excel】セルの装飾で、違うセルの値を参照する方法
幾つかの遺伝子改変マウスを管理するときに、セルを色分けしているとわかりやすい。 ただこの場合、例えばバイジェニックだと２個のセル（DFとDG）を参照して別セル（DD）をハイライトする事になる。 今回はその方法の備忘録。 違うセルを参照にして特定のセルを装飾する １．装飾したいセル（私の場合は列）を選択した状態で、「ホーム（Home）」タブ → 「書式（Style）」 → 「条件付き書式」→ 「新し […]
【Excel】オートフィル etc. のショートカット
マウス（動物の方）の管理をExcelで行っているけど、私は基本マウス（PCの方）操作が嫌いなので、キーボードのショートカットを多用している。 前回は相対参照⇔絶対参照のショートカットキー「F4」を書き留めたので、今回は次によく使う「オートフィル」と、その他よく使うショートカットキーについて書いておく。 オートフィル（AutoFill）とは ある上下のセルに数値を入れ、そのセルを選択して右下にカーソ […]
【Excel】数式の相対参照⇔絶対参照を切り替えるショートカット：F4
エクセルを使っている人達ならほぼほぼみんなお世話になっているであろう「相対参照」と「絶対参照」。 相対参照、絶対参照とは 通常、関数をコピペしたりオートフィルしたりすると、そのセルの場所から移動した分だけ自動的に計算して参照セルを変更してくれるけど、これを「相対参照（relative cell reference）」という。 で、場所を移動しても自動計算しないで同じセルを参照してほしいときには、「 […]
【Excel】頭文字や後ろの文字など、任意の文字を抽出
エクセルでマウスの管理をしているけど、マウスIDとは別に、実験用のID “experimental ID” を付けたい。 例えば、野生型マウス（WT）でオス（M）でSham手術（S）でインジェクションマテリアルがAD-tau（A）で3ヶ月後に観察（03）で、p03-32 というIDのマウスの場合、 「WTMSA03-0332」 とゆー感じ。 でも、いちいち手動でナンバリング […]
【Excel】大文字、小文字、先頭のみ大文字
エクセルには、ワードに備えられている 「大文字、小文字、先頭のみ大文字 etc.」 を選択するボタンやショートカットがない。 ではどうするかというと、関数を使う。 大文字に変換：UPPER 関数 例えば、A1に “mom neuroscience” と入れて、全部大文字にしたものをB1に出力したい場合。 B1に =UPPER(A1) と入れる。 すると、全部大 […]
【Excel】グラフの形式（フォーマット）をコピーする
一つのテーマで複数のグラフを作るときには、フォーマットをあわせたくなるのが心情。 でも、一つ一つフォーマットを作り直していたら時間が勿体ない。 そんな時便利なのが、「グラフのフォーマットをコピペする」方法。 グラフのフォーマットをコピペする方法 例えば、下記のような２つのグラフを作製し、左にある ”Hippocampal Sclerosis” のグラフ形式を右にある ”Brain We […]
【Excel】COUNTIF (COUNTIFS) で、A ∪ B（A集合 OR B集合）の数をカウントする
例えば、下記のように、臨床診断 (Clinical Dx) が PD (Parkinson’s disease), PDD (Parkinson’s disease with Dementia), DLB (Dementia with Lewy Bodies) の患者さん２０人のデータがあったとして、 Dementia with Lewy Bodies の患者さんの何人が病 […]
【Excel】プルダウンリストから選択し、リスト毎に文字やセルを修飾する
エクセルで情報管理しているときに、時々「これはリストから選べるようにした方が効率的だな」と思う時があります。 例えば私の場合、マウスのジェノタイプを入力するときに、 +/+ +/- -/- と入れていますが、エクセルでは計算式と間違わないために、直前に「’（シングル・クオート）」を入れて下記のように入力しなければなりません。 ‘+/+ ‘+/- ‘- […]
【Excel】IF関数とAND関数の組み合わせ
先日、下記のような一塩基多型データを受け取った。 その場所のSNPは C or T。 このデータから、 a1/a2 ともに C だったら「CC」 a1/a2 の片方が C で片方が T だったら「CT」 a1/a2 ともに T だったら「TT] それ以外（空欄）だったら「NA」 と入力したい。 これを Excel で作業するときには、「IF関数」と「AND関数」を組み合わせる。 IF関数 IF関数 […]
【Excel】フィルター & ソート（並べ替え）機能
今更だけど、エクセルのフィルターとソート機能って便利だな－と思うので、必要な誰かのために記録。 フィルターで要らない行を隠す 例えば下記データがあって、”D: FreezerLocation” が空欄の行は使わない場合 ▼ 必要なセルを選択した状態で、Ctrl + Shift + L もしくは、リボンから Data > Filter でフィルター機能をO […]
【Excel】空行を一括削除する方法
先日同僚から、下記のように１行空きの状態の Excel データが送られてきた。 このまま R に取り入れるんだったら問題ないんだけど、今回は Excel のままで作業するために、空行を削除。 1,2行の場合：”Ctrl + – “ 1,2行だったら、普通に「Ctrl + -」で行削除すればいい。 ……けど、今回は1000行近くあったので、これだと時間と労力がかか […]
【Excelマクロ/VBA】Excelマクロを使って、作業時間を大幅に短縮する
仕事をしていると、結構同じ作業が必要なことが多い。 例えば私の場合、 ラボのデータベースから情報を取り出して、エクセルでダウンロードする。 このとき、色々なDx名がやたら長いので、全部省略したい。 e.g. Alzheimer’s disease -> AD でも、Dx名は十数種類あるので、全部書き換えてたら時間がかかる。 こんなときに、エクセルマクロで瞬時に作業を完成させると気 […]
【Excel】全ての空欄を抽出し、文字を入力する方法
Excelで、空欄は「空欄ですよ」と明記したい……。 そんな時、私は下記方法で空欄に「NA」と一括で記入しています。 全ての空欄を抽出し、「NA」と入力する方法 1. まず、どこかのセルに「NA」と入れておいて、 2. Ctrl + Cでクリップボードにコピーしておく。 3. 下記データの空欄を「NA」で埋めたい場合、 空欄のある列を選択しておき、Ctrl + G で「ジャンプ（Go To）」画 […]
【Excel】重複する行を削除
例えば、下記のようなデータがあった場合 最左の”ID”の列をみてみると、いくつか重複する数字がある。 これら重複する行を選出し、１つだけにしたい場合の方法。 重複する箇所をハイライトする 実際、どこが重複しているかこのままでは見にくいので、まずは重複箇所をハイライトする。 （しなくてもいいんだけど、私はこうした方が、ちゃんとworkしているか確認できるので、安心。） A:I […]
【Excel】条件つきの個数・平均・標準偏差（SD）・標準誤差（SEM）の出し方：COUNTIF/COUNTIFS構文・AVARAGEIF/AVERAGEIFS構文
例えば、下記のようなデータがあった場合 条件が1つの場合 条件付きの個数を求める：COUNTIF =COUNTIF(参照範囲,”条件”) eg. Genotype (B) が “CC”, “CT”, “TT” とあるデータの中から、”CT”は何個あるか求める場合 ▼ =COUNT […]
【Excel】ショートカットキー
エクセルのショートカットキー集。 随時更新予定。 ※ US配列で「:」→ JIS配列で「+」 リボン リボンのショートカット（Alt → …）は、一段目と、よく使う項目を覚えておく。 目的 ショートカットキー リボンの表示/非表示 Ctrl + F1 入力中 目的 ショートカットキー 選択中のセルを編集状態にする F2 セル内での改行 Alt + Enter 入力確定し、下のセルに移動 […]
【Excel】外れ値の検定（Smirnov-Grubbsの棄却検定）
データの中に、一つだけ他のデータとかけ離れている値がある場合、それを外れ値として棄却するべきかどうか、検定する方法。 スミルノフ・グラブス棄却検定 帰無仮説：この値は外れ値ではない 対立仮説：この値は外れ値である 1. 検定統計量Tを求める エクセルの場合、 eg. A1:A10の中にあるデータ中、A5の値が外れ値かどうか検証したい場合 作業セル：= (A5-(average(A1:A10))/S […]
【Excel】INDEX関数とMATCH関数を使って、任意のデータを抽出
違う時期に抽出したデータで、 各行の並びがバラバラになっている事がある。 「嗚呼、こっちのデータとあっちのデータを照合したい…」 という時に便利な関数。 例題 eg. 下記のようなDeta AとData Bがあるとする。 データA： A B C D 1 Data A data A-1 data A-2 data A-3 2 apple 21 32 21 3 orange 31 44 32 4 gr […]
【Excel】ピアソン相関係数（r値, t値, p値）
エクセルでピアソン相関係数を算出する方法 例題 eg. 下記のようなデータがあった場合 r value =PEARSON(データ1の範囲,データ2の範囲) =PEARSON(A2:A28,B2:B28) r = 0.62 という数値が返る t value t valueは、 =相関係数*SQRT(n数-2)/SQRT(1-相関係数^2) =E14*SQRT(27-2)/SQRT(1-E14^2) […]
【Excel】各セルの名前を一括で追加/削除
Data analysisから抽出したエクセルデータで、時々各セルの名前を一括変更したい時がある。 名前を削りたい時 例えば、 “D:\Slide Scanner\Scan-200825-BF-409-Hipp-FN-CPI\1986-089_409.mrxs” という名前がずらっと並んでいた場合、 “1986-089” という名前だけにしたい時。 […]
【Excel】オッズ比とP値
オッズ比とP値はエクセルで計算可。 X1 X2 Total Y1 a b a+b Y2 c d c+d Total a+c b+d a+b+c+d 上記のようなデータがあった場合、期待値は、 期待値 X1 X2 Total Y1 (a+c)*(a+b) /(a+b+c+d) (a+b)*(b+d) /(a+b+c+d) a+b Y2 (a+c)*(c+d) /(a+b+c+d) (b+d)*(c+ […]