先日のエクセル研修で、受講生の方から都道府県の抽出方法について質問を受けました。

ネットを検索すれば出てくる話なのですが、分かりやすく説明するために、

・FIND関数で「県」を検索する列を作成する

・FIND関数の列が数字だったらLEFT関数で数字分だけ切り取る

・FIND関数の列が数字でなかったらLEFT関数で3文字切り取る

という方法を実演しました。

説明しながら、これはLET関数が使えるのではないか、と思いつきやってみたところ、想像どおりプログラマー垂涎の関数だったのでご報告します。

LET関数とスピルを組み合わせると、ほとんどプログラミングの世界が実現できます。

今回作成した数式はこんな感じです。

=LET(data,B:B&C:C,slice,FILTER(data,B:B<>""),n,FIND("県",slice),num,IF(ISNUMBER(n),n,3),LEFT(slice,num))

実験のため、少し冗長にしています。

長い数式ですが、構造は単純なので、慣れれば分かりやすそうです。

詳細はファイルをダウンロードしてご利用ください。

解説は以下のとおりです。

=LET(

    data,B:B&C:C, ← B列とC列を結合して「data」という名前にします

    slice,FILTER(data,B:B<>""), ← 「data」から空白行を取り除き「slice」とします

    n,FIND("県",slice), ← 「slice」の各行から「県」を探し、その位置を「n」とします

    num,IF(ISNUMBER(n),n,3), ← nが数字ならn、そうでなければ3を「num」とします

    LEFT(slice,num) ← 「slice」の左から「num」文字切り取ります

)

待ちに待ったlet関数が365でも使えるようになっていました！ エクセルはさらにプログラミングに近付きます。 実務における活用法を見つけたら、またレポートします。 https://news.mynavi.jp/article/20201118-1506245/

先日NewsPicksの取材を受けました！

とても楽しい企画で、２時間の動画収録もあっという間でした。

２時間の収録でしたが、12分に集約されています。

プロの編集で楽しくご覧いただけます。

ぜひご覧ください。

【実践】あなたは「エクセルの凄さ」を使えていますか

コメントの中で、動画と同じようにできない、

という声もありましたので、

・エクセルファイル

・ショートカットキー一覧

を公開します。

ダウンロードしてご利用ください。

ただし最新版のエクセルでなければ、

動的配列（スピル）は使えません。

共有フォルダ

数式セルに色を付ける方法

数式セルに色を付けるとメンテナンスしやすい

エクセルやGoogleスプレッドシートをメンテナンスするときに、数式の入ったセルに色を付けたいことがあります。

特にGoogleスプレッドシートを配列で作成すると、どこに数式が入っているのか分かりにくくなってしまいます。

数式の入ったセルに手作業で色を塗る人もいますが、数式が追加されると手作業が増えますし、メンテナンス漏れも起きます。

なんとか自動で色を付けたいものです。

そんなときに威力を発揮するのが、

「isformula関数」と「条件付き書式」

の組み合わせです。

Googleスプレッドシートで解説しますが、エクセルでもやることは同じなので、ぜひ実際にやってみてください。

「is」のついた関数はたくさんありますが、私は「～ですか？」と訳しています。

「formula」というのは数式のことですから、「isformula」は「数式ですか？」と訳されることになります。

「数式ですか？」ときくと、スプレッドシートは「はい」か「いいえ」で答えてくれます。

「はい」のときは「TRUE」、「いいえ」のときは「FALSE」という値が返ってきます。

これは難しく考えず、そういうものだと思ってください。

すべてのセルで「数式ですか？」ときいて、「TRUE」と返してくれたセルに条件付き書式で色を塗れば完成です。

具体的な手順の解説

まず、数式の入ったスプレッドシートを用意します。

下の図ではB4セルに数式が入っています。

次にシート全体を選択します。

左上をクリックするとよいでしょう。

条件付き書式を起動します。

「書式ルール」の「セルの書式設定の条件」で「カスタム数式」をクリックします。

式にisformula関数を入力します。

「A1セルは数式ですか？」という式です。

この「A1」には絶対参照がついていませんから、最初に選んだ範囲に数式が適用されるとともに参照も変化することになります。

=isformula(a1)

好きな色に変更して、完了ボタンを押せば完成です。

数式の入ったセルだけ色が付きました。

セルに「=」から始まる数式を入力すると、そのセルに色が付きます。

色をオンオフするアイデア

確認が終わったら色を消したいけど、また確認するときのために条件付き書式は削除したくない、ということがあります。

そんなとき私はこんな方法を使っています。

どこかのセルに「数式on」と入力します。

このとき入力規則を設定しておくと分かりやすいです。

今回はA1セルに入力します。

AND関数を使って条件付き書式の式に「A1セルが空白ではない」という条件を付け加えます。

ここには絶対参照をつけるので注意してください。

=and(isformula(A1),$A$1<>"")

このようにしておけばA1セルを切り替えるだけで、数式の入ったセルに色を付けたり消したりすることができます。