前回、企業が入社テストとして出題した「Excel問題」のその2をやってみました。
これまでは数値の計算ばかりでしたが、今回は文字列の操作となります。
それでは一緒にやっていきましょう。
問題3を確認
出題されていたエクセルの画面と同じものを作成し、以下からダウンロードできるようにしてあります。
今回も実務でありがちな操作方法ですね。
今回のポイントは1つです。
- セルの形式を変更しないで回答する
実は今回の操作は、以下のような普段よく使う「セルの書式設定」でできる操作を関数でやっているだけとなります。
今回は、この「セルの書式設定」を一切使ってはダメだ、というわけですね。
それでは最初に左の「基データ」を「0埋め8桁」に変更する関数からやってみましょう。
数値の桁数を揃える
キーになる番号でデータを管理していたけど、データ数が多くなってきたため「キーの先頭に0をつけて桁数を揃えたい」と言う想定です。
こういった「数値の頭に〇桁の0を付けて揃える」というのはありがちな操作ですね。
これは、「TEXT関数」を使うと簡単に実現できます。
基データは「セルB6」なので、「値」の引数には「B6」が入ります。
次の引数である「表示形式」には、表示させたい形式を「文字列」で指定します。
文字列で指定する場合は、「""(ダブルクォーテーション)」で囲みます。
「"00000000"」と指定していますね。
これは数値を8桁で表示し、無い桁は0が表示されるようになり、0の場合も「00000000」と表示されます。
「セルB6」は「1」なので1桁しかありません。
そのため、無い桁には0が当てられ「00000001」と表示されるようになるわけです。
#を指定するとどうなる？
「0」と同じような指定方法で「#」があります。
これも数値を指定する方法ですが、無い桁は何も表示しません。
例として「表示形式」に「######00」と設定してみましょう。
すると、先頭6桁に指定されている「#」の部分は、何も表示されません。
最後の2桁は「0」を指定しているので、桁が無い場合でも「0」を表示してくれます。
したがって、「セルB6」は「00」と表示されるのです。
一方、「セルB9」の「11」という基データは、「"######00"」の指定の場合、同じく先頭6桁は何も表示されず、最後の2桁だけが表示されるので「11」となるわけです。
ではもし「セルB9」の表示形式を「"####0000"」と指定したら「セルD9」にはどのように表示されるでしょうか？
そうです。「0011」となるのです。
「セルの書式設定」でも「TEXT関数の表示形式の引数」でも、0と#はそのように使い分けます。
必要な桁数だけ表示形式の引数に「0」を指定すれば、その桁分は0で埋めてくれるのがこの「TEXT関数」となります。
後は、オートフィルで26行目まで関数をコピーしてください。
基データがすべて8桁で統一されました。
では次に日付の表示形式を見てみましょう。
日付の表示形式を変更する
セルに日付データが入力されている場合、その表示方法を「TEXT関数」によって変更できます。
そのやり方も「表示形式」引数に文字列を指定するだけです。
今回は「2016/9/19」のような年月日データを「yyyymm」(西暦4桁月2桁)の表示に変更します。
引数の「表示形式」に「"yyyymm"」と指定すれば、西暦4桁月2桁で表示できます。
結果に「201609」と表示されているのが確認できますね。
この指定の場合、例えば9月であれば「09」となりますし、10月であれば「10」と必ず2桁で表示してくれます。
後は、オートフィルで26行目までコピーしてください。
それでは最後に、小数点第二位まで表示するパーセンテージの表示に進みましょう。
パーセンテージを表示する
問題の指定として「割り算を使わない」で表示しなければなりません。
これも「TEXT関数」の表示形式で可能となります。
「表示形式」を「"0.00%"」としています。
この「0」は、先ほど8桁の桁合わせで指定した際の「0」と同じです。
「一の位」と「小数点第一位」、「小数点第二位」は必ず表示されます。
最終的に%表示にするので、「値」が100倍されます。
この「表示形式」にすると、「0」であっても「0.00%」となりますし、例え小数点がなくても必ず「.00」と少数第二位まで表示されるようになります。
今回はすべて「TEXT関数」によって、基データを自分の表示したい形式に変更できるかを問われたわけですね。
脱線その1：TEXT関数は表示が変わるだけ
ここからは、本題であるテスト問題からは少し逸れたお話を2つほどしていきます。
まず1つ目は、今回見てきたような「8桁で揃えた文字列」でも足し算ができる点です。
TEXT関数が返す値は、「文字列」です。
8桁で揃えるのに頭に「0」を付けて、「00000002」などの文字列となったわけです。
本来、文字列では足し算をできないのです。
しかしエクセル側では、これらの「数値文字列」を「2」や「3」のような数値として計算してくれるのです。
上のように3つのセルを足し算するとします。
つまり計算式は以下のように見えます。
00000002+00000003+00000011
文字列を足し算するとエクセル側はエラーを表示するはずです。
しかしこれら3つの文字列は実際に足し算をすると、「2+3+11」と勝手にエクセル側で判断してくれます。
その結果、「16」が表示されるのです。
ただし注意点があります。
この場合の足し算の計算では、「SUM関数」は使えません。
「SUM関数」の引数として「セルD7」や「セルD8」を指定しても正しく足し算をしてくれません。
脱線その2：RIGHT関数を使う
今回の操作は「TEXT関数」を使ってきました。
例えば数値の先頭に0を付けて、桁を揃えるという場合には「RIGHT関数」でも実現できます。
RIGHT関数とは
指定した文字や数値の右端から、指定した数だけ文字を抜き取る関数となります。
例えば「RIGHT("あいうえお",3)」と指定すると、「あいうえお」の文字列の右端から3つ分の文字を取得する、となります。
つまり「うえお」が返ります。
この性質を利用して、数値データの先頭に「0」を付けられるのです。
では、実際にやってみましょう。
最初の引数の「文字列」に「基データに100000000を足した数値」を指定します。
「セルB7」のように「2」であれば「100000002」になり、「セルB9」のように「11」であれば「100000011」になります。
RIGHT関数は「右端から何文字取得するか」を指定する関数なので、今回のように8桁欲しい場合は、9桁のきりのいい数と足し算させます。
そして、引数の「文字数」に「8」を指定すると、足し算された「100000002」の右端から8文字を取得してくれます。
その結果「00000002」が返って来るわけですね。
ちなみに「RIGHT関数」で返ってきた「00000002」のような値を足し算しても、先ほど同じようにエクセル側で勝手に「2」などの数値として計算してくれます。
もちろん「SUM関数」の引数に指定すると正しく計算できないのも同じです。
問題3まとめ
今回は文字列操作の関数でした。
簡単におさらいをしておきましょう。
- 「TEXT関数」で既存データの表示形式を変更できる
- 「表示形式」の引数は、""(ダブルクォーテーション)で囲んで指定する
- 桁の指定は「0」と「#」を使い分ける
- 数値文字列はエクセルで勝手に数値として判断して計算してくれる(SUM関数の引数には指定できない)
- RIGHT関数を使っても0の桁揃えができる
数値の計算以外にも、今回のような文字列を関数で処理する場面は多いですね。
次回は問題4に進んでいきます。