Excelでアンケート等を集計する場合に知っておくと便利な基本機能や関数のメモです。
計算関連
セルの参照
セルには「相対」と「絶対」2種類の参照方法があります。
相対参照
「B2」「C2」のようにセルを指定する方法です。Σボタンで「合計」を求める場合は、相対参照でセルが指定されます。
=SUM(B2:C2)
絶対参照
行や列の前に「$」をつけると絶対参照になります。行と列のそれぞれで絶対参照できます。
絶対参照は後述の「オートフィル」と組み合わせて使用する場合に便利です。
=SUM($B$2:$C2)
=SUM($B$2:C$2)
数式バーでF4キーを押すと絶対参照をトグル切り替えできます。
シートの参照
別のシートのセルを参照する場合は「シート名!」を使用します。
Excelデフォルトの「Sheet1」を参照する場合は「Sheet1!」になります。
=SUM(Sheet1!B2:C2)
オートフィル
セルを選択して右下の■をドラッグすると、セルの数式をコピーできます。
特定のセルを参照したオートフィル
計算では「税率」のように適当な値を計算に含めたい場合があります。
=SUM(B2:C2)*D2
この計算をオートフィルすると「D2」の部分が自動的に連番に変わってしまい計算に失敗します。
絶対参照「$D$2」を使用すると、特定のセルを参照した状態でオートフィルできます。
=SUM(B2:C2)*$D$2
文字列を集計
文字列を集計したい場合は「=COUNTIF(A2:A9,"文字列")」を使用します。
例えば「バター」の数が知りたい場合は以下の通りです。
=COUNTIF(A2:A9,"バター")
文字列のフィルター
文字列を含む
「Aバター」「BバターB」「バターC」のように「バター」の前後に別の文字が追加されている文字列を集計したい場合は、ワイルドカードを使用します。
=COUNTIF(A2:A9,"*バター*")
ワイルドカードの検索条件は以下のように変更できます。
- *バター* 「～を含む」
- バター*「～ではじまる」
- *バター「～で終わる」
文字列を含まない
文字列を含まない数を集計したい場合は、「=COUNTIF(A2:A9,"<>文字列")」を使用します。
例えば「バター」以外の数が知りたい場合は以下の通りです。
=COUNTIF(A2:A9,"<>*バター*")
文字列の代わりにセルを参照する
文字列を集計する場合に、毎回数式バーに文字列を入力するのは面倒です。
「名前」のセルを参照する場合は以下の通りです。
=COUNTIF(A2:A9,"*"&C2&"*")
空白を詰めて表示
行の空白を詰めて、入力された文字列だけまとめたい場合があります。
例えばA列から文字列だけ取り出す場合は、数式バーに以下の通り入力して Ctrl+Shift+Enterで確定します。
Ctrl+Shift+Enterで確定すると配列数式という高度な計算ができる物になるらしいです。配列数式は数式が{}で囲まれます。
=IFERROR(INDEX(A:A,SMALL(IF(A:A<>"",ROW(A:A)),ROW())),"")
グラフ関連
グラフの軸を反転する
Excelで横棒のグラフを作成すると、行の順番が上下反転したグラフが作成されます。
グラフの軸を反転したい場合は、グラフをダブルクリックして「軸のオプション」から「軸を反転する」をONにします。
表示関連
列のソート
列を値でソートしたい場合は「並べ替えとフィルター」を使用します。
「昇順」「降順」でセルの並び替えです。「フィルター」を使用すると、セルのボタンを使用して並べ替えが行えるようになります。
ホーム / 並べ替えとフィルター / フィルター
1行目をスクロールに追従
表をスクロールした場合に、見出しを常に表示したい場合があります。
「先頭行の固定」を使用すると表をスクロールしても1行目を常に表示することができます。
表示 / ウィンドウ枠の固定 / 先頭行の固定
線を消す
デフォルトで表示されるセルのグレーの線を消したい場合は、「目盛線」をOFFにします。
表示 / 目盛線
重複表示
データを整理する場合に同名の入力がないか確認したい場合があります。
「重複する値」を使用すると、同じ値が入ったセルの背景色を変えることができます。
ホーム / 条件付き書式 / セル強調表示ルール / 重複する値
Excelに不慣れながらデータを集計しようと思ったのですが、Excel難しすぎて挫折しそうになりました。
Google スプレッドシートでは入力された文字列を何も考えずに集計してグラフ化することができたのですが、Excelでは文字列を直接集計できないようです。Excelで文字列を集計する場合は一度文字列を数えてから、その値をグラフ化する必要がありました。
Excelは世界的に普及してるはずなのにどうしてこんな不便なんだと思いつつ、せっかく色々調べたのでExcelの機能をまとめてメモしておきます。
