ブログ管理人のtetsuです。
簡単に自己紹介ですが、元経理マンで今はWEBエンジニアをしています。
今回は仕事でも使えそうなGoogleスプレッドシートの便利機能をご紹介します。
はじめに
スプレッドシートを使って簿記検定受験者データからグラフを作成した際に、「検索と置換」という機能が便利だったのでご紹介します。
なお、受験者データは商工会議所HPにあるものを使用しています。
この機能は、その名の通りスプレッドシートで検索が出来たり、その検索結果を別の文字列に置換することができます。
仕事だと例えばシステムから出力されるデータを加工する業務などで役立つかもしれません。
使い方
まず、スプレッドシートにデータを準備（今回は外部からコピペ）します。
検索
最初に「検索と置換」の検索を使ってみましょう。
メニューの「編集」をクリックしてください。
その中に「検索と置換」がありますのでクリック。
すると、「検索と置換」が表示されます。
例えば今回は第140回の受験データを検索することにしましょう。
ちなみに、今回のデータはA列に第何回の試験なのか保存されています。
使い方は①検索キーワードに「140」と入力して、②検索ボタンをクリックするだけです。
カーソルが自動で「140（2015.6.14）」というA19セルに移動して検索できました。
ちなみに、検索キーワード「140」で他にも該当する結果がある場合は、もう一度検索ボタンをクリックすることで表示されます。今回だと想定外ではありますが「140,245名」という受験者数に関するデータも検索されました。
置換
次は「検索と置換」の置換を使ってみましょう。
今回のデータには既にE列に「合格率」がありますが、何故かグラフでそのまま数値として使えませんでした。
よって、「実受験者数(C列)」÷「合格者数(D列)」の計算式で自ら「合格率」を数値として算出する必要がありました。
ただし、ここで問題なのが「実受験者数(C列)」と「合格者数(D列)」のデータの中身です。
例えばD2セルが「40,129名」となっているように「40,129」という数値に「名」という文字列がくっついていますが、文字列があると割り算することができません。
手作業で一つずつ「名」を削除してもいいですが、この文字列を一括削除する際に置換が使えます。
そのためには、またメニューの編集から「検索と置換」を開いてください。
今度は「検索」に削除したい「名」と入力し、「置換後の文字列」を未入力の状態で、「すべて置換」ボタンをクリックしてください。
見ての通り「名」を一括削除(無へ置換)することができました。
これで、あとは数値となった「実受験者数」と「合格者数」を使用して「合格率」を算出するだけになりました。なお、合格率の算出については省力します。
ちなみに、例えば「置換後の文字列」に「人」などの文字列を入力することで、別の文字列に置換することも当然可能です。
正規表現
使う機会は少ないかもしれませんが、個人的にA列にある年月日を一括削除したかったのでその方法をご紹介します。
年月日はA2セルだと（2021.2.28）、A3セルだと（2020.11.15）というようにセルごとに異なります。
先ほどは「名」という同じ文字列を一括置換しましたが、異なる年月日の一括置換は正規表現を使うことで可能になります。
「正規表現は、文字列の集合を一つの文字列で表現する方法の一つである。」とWikipediaに書かれていますが、難しいと思いますのでこういうことが出来るんだ程度で大丈夫です。
興味がある方は是非調べてみてください！
そして、今回の年月日の正規表現は(\d{4}).(\d{1,2}).(\d{1,2})となります。また、今回のデータでは年月日が（）で囲まれているため（(\d{4}).(\d{1,2}).(\d{1,2})）とすることで括弧ごと一括置換できます。
それでは、実際にスプレットシートで正規表現による置換をしてみましょう！
「検索と置換」を開いて「正規表現を使用した検索」にチェックしてください。自動で「大文字と小文字の区別」にもチェックが入りますが、今回はそのままで大丈夫です。
「検索」には「（(\d{4}).(\d{1,2}).(\d{1,2})）」と入力し、「置換後の文字列」を未入力の状態で、「すべて置換」ボタンをクリックします。
見ての通りA列の年月日が括弧ごと一括削除（無へ置換）されました。
正規表現は難しいので使えなくてもよさそうですが、基本的な「検索と置換」が使えると業務効率化に役立つこともありそうですね！
今回はGoogleスプレッドシートの話でしたがエクセルにも同じ機能がありますので是非使ってみましょう！
他にもスプレッドシートの人気記事があるので、スキルアップしたい方は読んでみてください。
＞＞INDEX関数とMATCH関数で別シートの値を参照【スプレッドシート】
今回は以上です。
コメント