どうも、坂津です。
仕事で
基本的には商品マスタ、つまり『取扱商品の一覧』と、分析用の集計資料で使います。
特に私の仕事と言うわけでは無いのですが、いつの間にか『得意でしょ』というレッテルを貼られてしまい、Excelを使った業務が回ってくるようになりました。
それでも、今は息をするように関数を入力して必要な作業を行っていますが、よく考えたら入社当時はさっぱりでした。
必要に迫られ我流で覚えたExcel術で、どうにか対応しているような現状です。
その我流も『こんな作業が必要だけど、どうすれば良いのか』みたいなことを検索し、使えそうな技術を試し、望んだ結果になるかどうかトライ＆エラーを繰り返すようなやり方でした。
そんなとき『うおマジか！コレだよコレ！ドンピシャ！』という小技は往々にして、個人ブログの記事だったりしたものです。
Excelに関する知識を専門的に羅列したサイトもたくさん活用させていただきましたが、例えば関数の複合技で実務を解決するような方法は、結局自分で考えなければなりません。
料理で例えるならば、食材別で最適な切り方や加熱の方法などは詳細に記載されているけれど、それらを駆使したレシピは自分で考えてねって感じなのです。
と言うわけで、いつどこで誰の役に立つのか、立たないのか、全く分かりませんが、過去の私を救ってくれたブログ記事に倣ってみようと思います。
『セルの中から文字列を抽出する』
これ、検索するときに『抽出』って単語が出てこなかったりするんですよね。
『エクセル 文字 取り出し』とか『抜き出し』とかで検索して、なかなか思うような結果が出なかった記憶があります。
んで、抽出したい場合『mid』というのがよく出てくるんですが、こいつは単体で使用するにはちょっと制限があって、実務的に使う場合は他の関数と組み合わせる場合が多いんじゃないかと思います。
『mid』がどんな関数かと言えば、こんな感じ。
※コチラは『2018年4月期-最新回視聴率ランキング』から元データをお借りしました。
mid関数とは、エクセルの１マスの中にある文字列の中から任意の部分を取り出す関数です。
(エクセルでは区切られた１マスのことを『セル』と呼びます。ぶるぁぁぁぁ！)
上の図では、番組名の列に『順位』『視聴率』『番組名』が一緒に入っています。
今回はその中から『番組名』だけを取り出したいと思います。
試しに『Ｄ４』にmid関数を入れてみました。
『＝ＭＩＤ（Ａ４，８，３）』と入力しています。
すると表示は見事に番組名だけになっていますよね。
関数の意味としては、上の図で色分けしているのを見てください。
『＝ＭＩＤ（どのセルの，左から何文字目から，何文字）』ということになります。
つまり『Ａ列の４行目』にある『1位13.8%特捜9』という文字列から『特捜9』だけを取り出そうとした場合は、『特』が８文字目(ピリオドやスペースなども１文字として数える)から３文字分を取り出すよ、って意味になります。
ですから例えば９位のところで同じことをしようとすると、セルの位置はＡ列の12行目、番組名の始まりは７文字目、番組名の文字数は14文字ですのでこうなります。
『＝ＭＩＤ（Ａ１２，７，１４）』
これでmid関数についてはなんとなく伝わったでしょうか？
さて、こんな感じの説明はどこの教科書にも書かれていることです。
しかしこれだけだと私の業務には全く使えない関数でした。
上の図でもそうですが、取り出したい文字列がどこにあるのか、何文字なのかはバラバラなのです。
それらを全部数えて関数を入力していたら、自分でその文字を入力した方がはるかに速いってことになっちゃいます。
そこで、私が頻繁に使うのは『find』『len』との組み合わせです。
まず『find』とは、セルの中から任意の文字列を発見して、それが何文字目にあるのかを教えてくれる関数です。
番組名の列をよく見ると、必ず『順位』『視聴率』『番組名』の順番に並んでいますよね。
なので、取り出したい番組名は必ず『％』の後ろから始まるという法則を利用するのです。
そのためにはまず『％』が何文字目にあるのかを知ることが必要ですから『find』を使うのです。
試しに『Ｅ４』にfind関数を入れてみました。
『＝ＦＩＮＤ（Ａ４，”％”，１）』と入力しています。
すると『％』は７文字目にありますから、Ｅ列の４行目には『７』と表示されていますね。
関数の意味はこんな感じです。
『＝ＦＩＮＤ（どのセルの，”なんという文字列を”，何文字目から数えて）』となります。
※関数は基本的に数字の計算をするものであり、関数の中に数字以外の言葉や文字(記号)を扱いたい場合は、その文字を『”(
次に『len』ですが、これはセルの中が何文字なのかを教えてくれる関数です。
すごく簡単です。
試しに『Ｆ４』にlen関数を入れてみました。
『＝ＬＥＮ（Ａ４）』と入力しています。
するとＡ列４行目は10文字ありますから、Ｆ列の４行目には『10』と表示されていますね。
さて、これで準備は万端です。
今回やりたいことと、条件を箇条書きにしてみました。
・セルの中から番組名だけを取り出したい
・取り出すために『mid』を使う
・何文字目から何文字分が番組名なのかを数えなければならない
・何文字目から何文字分を取り出すのかはセルによってバラバラ
・番組名の直前には必ず『％』が存在する
・『find』で『％』の位置を数える
・『len』で総文字数を数える
ここまでくればお分かりでしょうか？
番組名の開始位置は必ず『％』の１つ後ろであり、番組名の文字数は総文字数から『％』の位置を引いた数になるのです。
ですので『find』で数えた数字に１を足すことと、『len』で数えた文字数から『find』で数えた数字を引くことを別のセルでやってみましょう。
Ｇ列４行目には『＝Ｅ４+１』
Ｈ列４行目には『＝Ｆ４－Ｅ４』
すると、最初に『mid』を使ったときに入力した『何文字目から』『何文字分』と同じ数字になりました。
さぁ、mid関数の中にＧ列とＨ列のセルを入れてみましょう。
さっきは直接数字を『８(文字目)』『３(文字分)』と入れましたが、その場所にセルの座標(アルファベットと数字)を入れてやることで、関数が完成します。
『＝ＭＩＤ（Ａ４，Ｇ４，Ｈ４）』と入力します。
あとはこれを下までコピーすると、表の中から一気に番組名だけを取り出すことができます。
こんな感じに。
※念のためＤ列に入っている関数の中身をＩ列に表示させています。
さて、これで目的は達成されたわけですが、しかし随分と不格好な表になってしまいました。
実質的にＥ～Ｈ列は番組名を取り出すためだけに必要なのであって、実際の表としては不必要なものです。
そこで使うのが『入れ子』という小技です。
簡単に言えば『関数の中に関数を入れちゃう』ということです。
必要な関数は
『＝ＭＩＤ（Ａ４，Ｇ４，Ｈ４）』
ですので、Ｇ列とＨ列に入っている関数をそのままその場所に入れちゃいます。
『＝ＭＩＤ（Ａ４，Ｅ４+１，Ｆ４－Ｅ４）』
そしてＥ列とＦ列の部分も、同じようして関数をそのまま入れちゃいましょう。
『＝ＭＩＤ（Ａ４，ＦＩＮＤ（”％”，Ａ４，１）+１，ＬＥＮ（Ａ４）－ＦＩＮＤ（”％”，Ａ４，１））』
これでスッキリ。
余分な列も無くなって目的が果たせました。
さて、ご理解いただけたでしょうか？
なにせ我流でやってきたものですから、どのくらいの説明でどのくらい分かって貰えるのかがさっぱりでして。
自分では最大限に分かりやすく説明したつもりですが「それは前提としてこの知識が必要でしょ」みたいな落とし穴がいっぱいある気もします･･･。
