集計やら整形やらで何かと使うことの多いExcelですが、○○をする時ってどういう関数を使うんだっけ、
どういう構文だっけということが増えてきたので使った関数などを記載してメモ化していく。
基本的にExcelはこの記事に追記していく方式で進める。
指定した範囲に指定の文字列があるかどうかの検索
指定した範囲に目的の文字や数値が入力されていれば「○」を、
入力されていない場合は「×」を表示する様にするにはIF関数にCOUNTIF関数をネストして使用する。
=IF(COUNTIF(<検索する範囲>,<検索ワード>),"○","×")
指定した範囲に指定の文字列がある場合、その文字列と同じ行の特定のセルを表示
VLOOKUPは少し複雑で使い方を覚えるまでが面倒だけど、
要は「指定した列と同じ行の指定した列値を返す」関数。
これを先程の指定範囲内に、特定の文字列があるかどうか特定する関数と組み合わせて、
検索する範囲に指定の文字列がある場合、その行で対応する列値を取り出す、ということも可能。
=IF(COUNTIF(<検索する範囲>,<検索ワード>),VLOOKUP(<検索ワード>,<検索する範囲>,<列番号>,FALSE),"X")
個人的にはAWRのSQL実行時間の比較とかでよく使う関数。
見えているデータだけを対象に計算をする。
フィルタリングを行った状況で範囲指定で計算を行おうとしても、
非表示にしてあるセルも対象となってしまって求めたい値を計算できない、
ということが結構あった。
そんな時用に見えていないセルは計算から除外する、
要は表示されているセルだけを対象に計算を行う、という関数が用意されている。
=SUBTOTAL(集計方法,集計範囲始点：集計範囲終点)
| 集計方法(引数) | 集計機能 | 同等の関数 |
|---|---|---|
| 1 OR 101 | 平均値を求める | AVERAGE |
| 2 OR 102 | 数値の個数を求める | COUNT |
| 3 OR 103 | データの個数を求める | COUNTA |
| 4 OR 104 | 最大値を求める | MAX |
| 5 OR 105 | 最小値を求める | MIN |
| 6 OR 106 | 積を求める | PRODUCT |
| 7 OR 107 | 不偏標準偏差を求める | STDEV.S |
| 8 OR 108 | 標本標準偏差を求める | STDEV.P |
| 9 OR 109 | 合計値を求める | SUM |
| 10 OR 110 | 不偏分散を求める | VAR.S |
| 11 OR 111 | 標本分散を求める | VAR.P |
絶対参照
数式などでセル番地を指定していると、コピーをした時にセル番地がズレていってしまう。
そういうときは絶対参照という方法を使用するとセル番地のズレが生じずにコピーが出来る。
絶対参照は＄マークをセル番地の列、行の前にそれぞれ付ける事で有効になる。
下記の様な数式が設定されていた場合、コピー等をしてもズレるのは「A6」の部分だけで、
「B1」の箇所は固定されたままとなる。
＝A6＊$B$1
改行の置換
検索と置換のダイアログを表示させ、[検索する文字列]、或いは[置換後の文字列]に「Ctrl+j」を入力すると、入力バーには何も文字は入らないが、改行が入力された状態になる。
この状態で置換をすると、文字が改行に置換されたり、改行を文字に置換したりすることが可能になる。