【エクセルはあまりに機能が多すぎるので、知られていない便利機能・便利技があります】

仕事柄、色々なところで色々なエクセルの活用の仕方に出会います

エクセルの活用者と話していて気づくのは、意外にも知られていない機能や便利技が結構あることです

今回の記事では「便利」だけど意外に「あまり知られていない」機能や便利技について、私の独断と偏見でピックアップして紹介します

計算の手動化

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

これで、入力したセルの行位置及び列位置を取得できます

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRow As Long
Dim myCol As Long

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

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

実際のVBAコードは次のようになります

If myCol = 3 Then
ActiveSheet.Cells(myRow, myCol + 1).Calculate

End If

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

するわけです

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

テーブル機能

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

クエリの関数化

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

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

数学でいうと、Xを使った（X＋１）ｘ（Ｘ＋２）などの公式が関数と言えます

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

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

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

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

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

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

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

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

ところが、

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

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

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

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

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

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

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

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

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

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

フリーアドイン

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

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

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

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

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

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

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

＜まとめ＞

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

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

ところで、

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

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

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

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

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

にほんブログ村