知らないと損するExcelの便利機能・裏技 ~ガッツの日記 第13回~

Excelには数百を超える機能があります。

そのため、多くの方が毎日のようにExcelを使っていても、実は便利な機能や時短テクニックを知らないまま作業していることがあります。

私自身、業務改善やExcel研修、Power QueryやPower Platformの活用支援を行う中で、

「こんな便利な機能があったの?」

「もっと早く知りたかった」

と言われる機能に何度も出会ってきました。

この記事では、私が実務で特に役立つと感じた「意外と知られていないExcelの便利機能・裏技」を厳選して紹介します。

Excelの処理速度改善、テーブル機能の活用、Power Queryの応用テクニックなど、知っているだけで作業効率が大きく変わる内容をまとめていますので、ぜひ最後までご覧ください。

この記事でできること

✓ Excelが重くなる原因と対処法を理解できる

✓ 手動計算による処理高速化を実践できる

✓ F9を使った再計算を活用できる

✓ VBAを利用した部分再計算の考え方を理解できる

✓ Excelテーブル機能を活用できる

✓ Ctrl+Tによるテーブル化を実践できる

✓ 構造化参照(列名参照)を利用できる

✓ データ量が増えても壊れにくい表を作成できる

✓ Power Queryのクエリ関数化を理解できる

✓ 複数シートのデータを効率的に統合できる

✓ VBAを使わずに大量データ処理を自動化できる

✓ Excelアドインの活用方法を知ることができる

✓ 複雑な数式を効率よく管理できる

✓ 実務で役立つExcelの裏技を習得できる

詳細

計算の手動化

ワークシート内に数式を膨大に使用している場合、セルに入力するたびに膨大な計算が走ります

ですので、ワークシートが所謂「重たい」という状態になります

この場合、解決方法としては本来はデータベース等を導入することが考えられます

ただすぐに導入できる方法もあります

それが「計算の手動化」です

文字通り、数式の計算を止めます

そして、必要な時だけ計算します

数式タブには「計算方法の設定」というメニューがあり、こちらで数式の計算を止めることができます

ちなみに、通常は「自動」になっています

そして必要な時だけ「F9」ボタンを押せばファイル内(Shiftを押しながらだとシート内)の関数が計算されます

必要な箇所だけ自動計算

いや、入力するたびに計算結果が見たいんです、もしくは数式の計算をシート内に絞っても計算が遅いんです、という場合もあると思います

この場合は、VBAを使う方法があります

詳細はこの記事では割愛させていただきますが、VBAコードを入力というイベントに応じて動かす方法があります(イベントプロシージャーと言います)

イベントプロシージャーは、該当シートで右クリックすると表示される「コードの表示」で設定できます

次に開いた画面では、下の画像のように画面上の2つの欄を「Worksheet」「Change」に設定します

次に、以下のコードを上の画像の箇所に書きます

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRow As Long
Dim myCol As Long

myRow = Target.Row ←行位置
myCol = Target.Column ←列位置

これで、入力しているカーソルの行位置と列位置を自動で取得できます

実際に必要箇所だけ計算するコードは次の通りとなります

If myCol = 3 Then

  ActiveSheet.Cells(myRow, myCol + 1).Calculate

End If

入力位置が3列目(C列)だった場合、該当シートの入力位置がの一つ右を再計算(Calculate)

するわけです

下の画像の事例では「手動で計算」の設定のままC列に入力した場合、1つ右のセルだけ計算されます

これであれば、入力位置の行だけを計算できますので、かなり計算は軽くなります

テーブル機能

概要

この機能を知ったのは恐らく5年ぐらい前だと思いますが、意外と浸透していないという実感があります

データが集まった表をテーブルにすると、何かと表内のデータを便利に扱えるようになります

下の画像では、1つのセルに関数を入力したことで、全ての行に関数が反映されています

つまり、データを扱う単位がセルではなく行単位になっています

テーブル機能には、上記のような便利な機能が満載なのです

セル単位からの解放

この「満載」を具体的な一言で言い換えると、位置から名前への変換ということかもしれません

従来、エクセルを使おうとしたらセル単位という縛りの中でしか動けません

A列の5行目、C列の10行目といった感じでセルの位置をいちいち指定するしかありません

例えば、以下の画像のB列の合計を計算しようとしたら、マウスを動かしてC列の該当範囲を選択するしかありません

しかも、C列の範囲が拡大したら選択し直す必要があります

ところが、テーブル機能を使えば名前で複数のセル位置を一括で指定できます

テーブル機能では、作成したテーブルに名前を付けることができ、テーブル内の列名も自動的に活用できるからです

実は列名の指定もワンクリックでできます

上の画像のように、テーブルの該当「列名」の上にカーソルを置き、すっと上に少し動かすと、下向きの矢印がでます

これでテーブルの列名が指定できます

テーブルの列名で指定すれば、列を移動しようが、行の範囲が拡大しようが適切な範囲を合計できます

このようにテーブル機能は便利な機能が満載ですが、表をテーブルにするのは「Ctrl+T」のショートカットキー一つだけで行えます

とにかく簡単にテーブル機能は活用できますので、ぜひ今日から使用してみてください(→詳細記事)

表をテーブルにしたら、テーブルに名前を付けるのもお忘れなく!

他機能との連携

最後に、もう一点だけ付け加えさせてください

表をテーブルにするとMicrosoft のメニュー自体をうまく扱えるようになるという点です

これは具体的にはどういうことかというと、Microsoft のPower Platformではエクセルのデータをテーブルで使用するように設計されているからです

Power Appsなんかもテーブルからアプリがワンクリックで作れるようになっています→関連記事

ぜひテーブル機能は使いこなしていきましょう!

クエリの関数化

そもそも関数とは?

こちらはPower Queryの活用の仕方の話です

関数というと、SUM関数とかVLOOKUP関数を思い浮かべる方が多いと思います

数学でいうと、Xを使った(X+1)x(X+2)などの公式が関数と言えます

Xの値が何になっても一度作成した関数を使いまわして計算が行えます

クエリの関数化とは、一度作成したクエリをXを絡める関数にして使いまわすという意味です

Power Queryは大分、浸透したと思いますが、こちらのクエリの関数化はまだ知名度はいまいちという感じです

使用場面

どういう場面で使うかというと、一番使えるのがシート別もしくはファイル別にデータが散らばっているケースです

例えば、以下のようにシート別にデータが散らばっていたとします

しかも、欲しいデータの上に無駄なデータがあります

これがもし、100シートあったとしたらデータをまとめあげる作業はどう行いますか?

手動であればお手上げだと思います

ところが、

クエリの関数化であれば1シートに対して行った処理を、ほぼワンクリックで複数シートに使いまわすことにより、簡単にデータの一元化が行えます

関数化の概念

詳細についてはこちらの記事(→クエリの関数化)を参照して頂くとして、今回の記事では概念だけ解説いたします

上の画像は1シートからデータを取得し、無駄な2行を取り除いてテーブルを出力するクエリのソースコードです

実は黄色の箇所に「A」というシート名が入っています

つまり、ファイルの中のシートを取得した中からAシートを処理しているという訳です

この黄色の箇所を数学の関数でいうXに代えるわけです

以下の画像は関数化したクエリの一部です

このXにどういう風に各シートを連続的に割り当てるかというと、このソースステップの列を使用します

今回の事例では、実際にはItem列にある各シート名をフィルターして使用します

これでシート別などにデータが分散している問題をVBAなしで一気に解決できます

フリーアドイン

フリーアドイン機能もあまり知られていないと思います

スマホの便利アプリを利用するような形で、外部から便利機能を補って使用できます

こちらについては、アドインが沢山あるので「Formula Forge」というアプリの概要だけ今回は紹介します

例えば、こんな複雑なIF関数があったとします

読者の皆さんの中には「こんなの序の口だよ」と思われた方もいらっしゃると思います

Formula Forgeを使うと複雑な数式でも図解を一瞬でしてくれます

前任者の複雑なエクセルを引きついで苦しんでいる方にはおすすめのアドインです

<まとめ>

今回は意外と知られていない便利機能・便利技を大きく分けて4種類を紹介しました

とにかく知っているか知っていないかでこの辺りは差が大きく出ますのでぜひ押さえておいて欲しいところです

ところで、

私が思うに、エクセルの機能を最大限使いこなすために一番大事なことは「エクセルの限界」を知ることだと思います

どうしてもエクセルの方が相対的に劣る機能があります

例えば、エクセルは表計算ソフトなので大量のデータを扱うのはあまり得意でないですし、Power Platformなどかなり便利なクラウド機能も出てきています

ですので、最終的にはエクセルの機能を深堀するだけでなく「正しい脱エクセル」も意識する必要があると思っています

この点はまた別途詳しく書きたいと思います

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です