実務で使えるエクセルテクニック集(ショートカット、保護、フィルター時の計算)

お仕事などでお馴染みの表計算ソフト、エクセルに付属しているExcelVBA(エクセルブイビーエー)というプログラミング言語を使って初心者・入門者向けにプログラミングのキホンを解説していきます！

ExcelVBAができるようになれば業務内容によってはボタン一つで大幅な業務効率化ができる他に、普段のエクセルの使い方も段違いにレベルアップする事ができます！

ベテランの方も新人さんも興味のある方はぜひ挑戦してみてください！

今回は実務で使える知ってそうで知らない！？エクセルテクニックをいくつか紹介したいと思います。

超定番のショートカットキーを紹介します。

エクセルだけではなく色々なアプリで共通して使える上に慣れてくればマウスを使うよりずっと早く操作できるので覚えておいて損はないと思います。

コピー・・・「ctrl + c」

切り取り・・・「ctrl + x」

ペースト(貼り付け)・・・「ctrl + v」

全て選択・・・「ctrl + a」

上書き保存・・・「ctrl + s」

一つ動作を戻す・・・「ctrl + z」

入力窓などの移動・・・「tab」

ウィンドウの切り替え・・・「alt + tab」

定番中の定番をざっと紹介しました。

ぜひマスターしましょう！

エクセルで作ったシステムを管理者以外のいろいろな人が触る事により誤って計算式を消してしまうみたいな事ってあると思います。

セルを保護する事によってそのような特定のセルの編集を防ぐ事ができます。

セルの保護

まずは編集しても良いセルを選んで「セルの書式設定」→「保護」項目を選択→「ロック」のチェックを外して「ＯＫ」します。

次に「校閲」タブの「シートの保護」を選択

表示された「シートの保護」画面の「ロックされたセル範囲の選択」のチェックを外して「ＯＫ」を押せば完了です！(パスワードは設定しなくて大丈夫です)

※この時にパスワードを設定すると解除の際必ず必要になるので注意です！

先ほど選んだセル以外選択できなくなりました！

セルの保護解除

保護の解除は先ほどの「校閲」タブを開くと「シートの保護」項目が「シート保護の解除」項目に変化しているのでそちらを選択すればすぐ解除できます。

たとえばこんな表があったとして

普通にフィルターをかけて

何か値を抽出すると

合計欄まで一緒に隠れてしまいます。

これを解消します。

やり方は簡単でフィルターを外して、合計欄とデータの間に一ます以上の空白セルを挿入します。(何もデータは入れないでください)

こんな感じですね！

あとはフィルターをかけなおせば・・・、

合計欄も表示する事ができました！

空白が気になる方は行の高さの調整で高さをなくしてしまうのが良いかと思われます。

フィルターが「合計欄も含めてデータが続いている」と判断してしまう事でこのような事が起こるらしいです。

なのでデータの途切れ目を作ってあげる事でそれを解消します。

フィルターをかけた時の合計欄ですが、「SUM」関数を使って合計を出すとフィルター関係なく全ての合計を表示してしまいます。

どちらかといえばフィルターに反映された合計を表示したいですよね。

そんな時は「SUM」関数の代わりに「SUBTOTAL」関数を使います。

使い方は

=SUBTOTAL(109,E3:E12)

	↓

=SUBTOTAL(集計方法,計算範囲)

のように使います。

集計方法に指定した値で合計・平均・カウントなど集計方法を変更する事ができます。

代表的なものをのせておきます。

101・・・「AVERAGE(平均)」

102・・・「COUNT(数値が入っているセルの個数)」

103・・・「COUNTA(空白ではないセルの個数)」

109・・・「SUM(合計)」

抽出された値を反映する事ができました！

「SUBTOTAL」関数を上手に使う事によって、フィルター時に崩れない連番などを振る事もできます。

このような状態の時にきちんと連番を振る事ができます。

連番を振りたい先頭のセルに以下のように「SUBTOTAL」関数を指定してあとは連番の終わりまでオートフィルでドラッグしてください。

=SUBTOTAL(103,$A$2:A3) - 1

意味としては「A2セル」から「A3セル」まで空白でないセルの個数を数えて、その値に「-1」をするみたいな意味ですね。

詳しく説明すると少し話が長くなるのでカタチで覚えてしまいましょう。

オートフィルとか「$」とかよくわからないという方の為に具体的には連番を振りたいセルに

=SUBTOTAL(103,$A$2:A3) - 1

=SUBTOTAL(103,$A$2:A4) - 1

=SUBTOTAL(103,$A$2:A5) - 1

=SUBTOTAL(103,$A$2:A6) - 1

=SUBTOTAL(103,$A$2:A7) - 1

・

・

・

のように振っていきます。

上記の例では「A」列のセルを使っていますが、「空白でないセルの個数を数える」集計方法を用いて連番を振っているのでデータが連続して入っている列を指定してください。

連番を振る事ができました！