エクセル講座７回目ではオートフィルタで住所録から抽出の練習をしました。
今回は文字列関数を使って似たようなことをしてみます。
教材用住所録はエクセル講座のものを使用します。こちらにございますので、
ページを開いて、文字列を選択し、エクセルに貼りつけてご使用ください。
1.全角、半角(JIS ASC)
住所録をつくっていて、人によって番地が全角になっていたり、
半角にになっていたりして、統一したいとお思いになったことはありませんか。
すくないデータだと手で修正してもたいした手間ではありませんが、
大量のデータだと大変、全角の英数カタカナ文字を半角にするのがＡＳＣ
半角の英数カタカナ文字を全角にするのがＪＩＳ関数です。
今回の住所録では番地まで入っていないので、
半角でいれた年齢を全角にしてみます。
1）Ｊ2をクリックしてｆｘの関数貼りつけボタンを押し、JISをクリック。
2）文字列の欄にカーソルを置いて、Ｇ2をクリック、ＯＫボタンをおします。
Ｊ2に=JIS(G2)とはいり、42が４２に変換されました。
2.左から3文字を抜き出す(LEFT)
都道府県名で３文字なのは神奈川、和歌山、鹿児島の3つです。
住所録から県名を抜き出すには左から3文字を抜き出します。
1）Ｋ2をクリックし、ｆｘの関数貼りつけボタンを押しＬＥＦＴをクリック。
2）文字列の欄にカーソルを置いてＤ2をクリックし、文字数の欄に3といれ
ＯＫボタンを押します。
=LEFT(D2,3)とはいり、愛知県名古屋市から愛知県だけがとりだされました。
3）そのままでもわかりますが、より親切に表示するなら、
=IF(K3="神奈川",K3&"県",IF(K3="和歌山",K3&"県",IF(K3="鹿児島",K3&"県",K3)))
としておきます。
3.単位をつける(TEXT)
G列の年齢に歳という文字をつけてみます。
1）Ｍ2をクリックし、ｆｘの関数貼りつけボタンを押しＴＥＸＴをクリック。
2）値の欄にカーソルを置いてG2をクリックし、表示形式の欄に００歳と
いれます。=TEXT(G2,"００歳")となり、42歳と表示されました。
例えばＮ2に郵便番号を数字だけ書いてあって、頭に〒記号をつけたい場合
などは=TEXT(N2,"〒000-0000")とすると、1234567という数字が〒123-4567と
わかりやすくなります。セルの書式設定の表示形式のユーザー設定は
同じセルに表示しますが、こちらは別のセルにあらたに表示しなします。
このTEXT関数はとても便利で重宝しています。スケジュール表等を作る場合、
日付が入っていて、その隣に曜日をいれる場合などは=TEXT(B4,"aaa")と
いれると日と表示され、aaaaといれると日曜日と表示されます。
4.文字の繰り返し(REPT)
エクセルのグラフ機能を使った絵グラフの書き方はこちら
にありますが、ここではREPT関数を使って絵グラフをかいてみます。
メルマガエクセル関数講座の回数をＡ列に、読者数をＢ列にいれます。
講座回数 読者数
1 1588
2 1612
3 1645
4 1637
5 1645
6 1656
7 1657
8 1658
9 1679
10 1692
Ｃ列には１回目を100％とした場合の伸び率をいれます。
Ｃ2の１回目のみ0、Ｃ3の２回目のところに=B3/$B$2*100-100といれて、
Ｃ11までコピーします。D2に=REPT("☆",C2)といれて、
Ｄ11までコピーします。２回目は1％増えたので、星は1つ、１０回目は
6％増えたので、星が６つになりました。でも、3.1％も3.6も☆3つで
同じになってしまいます。そこで、この表をＡ13からにもう一つコピーして、
Ｃ15に=B15/$B$14*1000-1000として下へコピーしてみました。
星の数が１０倍になるので、Ｄ列はフォントサイズを6にしました。
Ｃ列の伸び率は数字がちがいますので、非表示にしておきました。
次回はエクセル関数講座の最終回、情報関数をつかってフリガナをいれます。
========================= 応用してみましょう =====================
スケジュール表を作成して、曜日をMondayと英語表示させてみましょう。
TEXT関数を用いて、表示形式をddddにします。
dddなどいろいろに試してみましょう。
=========================== ご質問コーナー =======================
前回のvol.46の2.DAVERAGEの１）表の作成のところのこのままコピーして
エクセルに貼りつけることもできますね。をやってみたところうまくいき
ませんでしたとmapleさんからご指摘いただきました。
原稿を書いている時点ではウインドウズのメモ帳にタブをいれて書いており
ましたので、そのままコピーできたのですが、メールにうつした段階で
タブが消えてしまったようです。データをメモ帳にコピーして保存し、
エクセルからファイル開くで、ファイルの種類をすべてのファイルにして、
先ほどのメモ帳をひらいていただくと、ちゃんとＡ列からＤ列にならびました。
この方法だとワークスやロータスからデータをもってくるのも簡単ですね。
一旦テキスト形式で保存して、エクセルで開きます。
Back Next エクセル関数トップ