E∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ あなたの技術をぐ〜んと高めるマガジン ★
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞L
クイズで学ぼう!『エクセル』の小技 266
∞∞∞∞∞∞∞\(^o^)\∞∞∞(*_*);∞∞∞§^O^§∞∞∞∞∞∞∞∞∞∞
◆ 今日の問題 「名簿に五十音のインデックスをつけるには?」
三重:先生、名簿に五十音のインデックスを付けたいのですが。
藤原:五十音の?
三重:ハイ、相沢とか青木とかは、ア、伊沢とか伊藤とかは、イという具
合に名簿に記すようにしたいのです。
佳代:電話帳みたいにしたいってことね。
三重:そうよ。
藤原:わかったよ。そんなに難しくないよ。それじゃあ、下図のように、
名前を入れたら、左側に、五十音が入るようにしよう。
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃ ア│相沢博光│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃ ア│青木一郎│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃3┃ イ│伊沢次郎│ │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃ イ│伊藤敏弘│ │ │ ┃
〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜〜
佳代:わかりました。それでは問題です。上の表のB列に名前を入れると
自動的に、A列に名前の頭文字を表示させるようにするにはどうし
たらいいでしょう?みなさんも一緒に考えてくださいね。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ 解答を見る前に,少しだけ考えてね!
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
○ 登場人物紹介
・藤原清盛:塾の先生で,このマガジンの指導役。パソコン歴12年。
・大場佳代:藤原先生の生徒で,パソコン歴3年。
・山形三重:佳代の同級生で,パソコン歴2年半。藤原先生のいとこ。
※ このマガジンは,Microsoftのエクセル2000,2002 の Windows版
のみを扱っています。(たいていの場合、97でも使えます)
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
◆ 解 説 <難易度・・・中級>
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
藤原:これは、二つの関数を組み合わせれば、すぐにできるね。
三重:えーっと、ふりがなを表示するフォネティック関数ですか?
藤原:もちろん、そうだ。
佳代:名前の頭文字ということは、ふりがなの一番左の一文字ということ
ですね。
藤原:そういうこと。
三重:すると、レフト関数を使うのですか?
藤原:もちろん。
佳代:わかりました。フォネティック関数で、ふりがなを取り出し、レフ
ト関数で左から一文字取るようにすればいいわけですね。
藤原:そのとおり。後は、きちんと式を作ればいいだけだ。
三重:えーっと、フォネティック関数は、=PHONETIC(セル番地)でしたね。
藤原:うん。
佳代:レフト関数は、=LEFT(セル番地,文字数)でした。
藤原:そうだね。セル番地の部分は、直接文字列でもいい。
三重:すると、文字列のところに、フォネティック関数を入れ子にすれば
いいわけですね。
藤原:そうだね。
佳代:式を作って見ます。とりあえず、A1に、=LEFT(PHONETIC(B1),1)
ってやればいいのではないですか?
藤原:正解だ。後は、フィルハンドルでコピーすればいい。
三重:そんなに難しくなかったですね。
藤原:そうだね。これまでに、もっと難しいのをやっているから、それを
覚えていれば簡単だろう。ただ、インデックスを付けておくと、後
で、見やすくする工夫もできるから、活用方法を考えてほしい。
佳代:わかりました。ちょっとした応用ですが、みなさんもいろいろ試し
てみてください。それでは、今日は、この辺で。
-----------------------------------------------------------------
<まとめ> 今回の問題に対する手順のみを示します
-----------------------------------------------------------------
セル「A1」に、=LEFT(PHONETIC(B1),1)
フィルハンドルで、必要なだけコピー
-----------------------------------------------------------------
<補足>
-----------------------------------------------------------------
=LEFT(PHONETIC(B1),1) を言葉で表すと、「B1」のセルに入ってい
る文字列のフリガナを左から1文字取り出しなさいということです。
LEFT関数の代わりに、MID関数を使うことも可能です。その場合は、
=MID(PHONETIC(B1),1,1)とします。
-----------------------------------------------------------------
<参考> 今回出てきた技に関連したものを示します
-----------------------------------------------------------------
【LEFT関数・MID関数関係】
http://www.pat.hi-ho.ne.jp/hirosilk/exb241.htm#245
【PHONETIC関数関係】=PHONETIC(セル番地)
http://www.pat.hi-ho.ne.jp/hirosilk/exb056.htm#60
http://www.pat.hi-ho.ne.jp/hirosilk/exb256.htm#259
E∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ あなたの技術をぐ〜んと高めるマガジン ★
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞L
クイズで学ぼう!『エクセル』の小技 267
∞∞∞∞∞∞∞\(^o^)\∞∞∞(*_*);∞∞∞§^O^§∞∞∞∞∞∞∞∞∞∞
◆ 今日の問題 「数字を1つずつ上げていくには」
三重:先生、同じセルで数字を1つずつあげていく方法はありませんか?
藤原:1つずつ?
三重:ハイ、例えば、1567を1568にしたいのです。
佳代:ああ、そういう場合があるわね。定期的に増えていくものを入れて
いるような時ね。
藤原:なるほど、確かに、数字が大きい場合、たった1あげるために、打
ち直すのは面倒だね。
三重:ハイ、何かいい方法がないかと思って・・・。
藤原:わかったよ。それじゃあ、今日は、クリック1つで、数字を上げ下
げできる方法をやろう。
佳代:わかりました。それでは問題です。セルに入力された数字をクリッ
ク1つで上げ下げできるようにするにはどうしたらいいでしょう?
みなさんも一緒に考えてくださいね。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ 解答を見る前に,少しだけ考えてね!
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
○ 登場人物紹介
・藤原清盛:塾の先生で,このマガジンの指導役。パソコン歴12年。
・大場佳代:藤原先生の生徒で,パソコン歴3年。
・山形三重:佳代の同級生で,パソコン歴2年半。藤原先生のいとこ。
※ このマガジンは,Microsoftのエクセル2000,2002 の Windows版
のみを扱っています。(たいていの場合、97でも使えます)
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
◆ 解 説 <難易度・・・中級>
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
藤原:これは、ちょっとテクニックが必要だ。
三重:難しいのですか?
藤原:いや、それほど難しくはないけど、慣れていない人は、ちょっと大
変かもしれない。
三重:どんな感じなのですか?
藤原:概要を話すと、ボタンを作って、それをクリックすることによって
実現する。
三重:ボタンですか?
藤原:そう。例えば、印刷のダイアログボックスを開いてごらん。
三重:ハイ。[ファイル]の[印刷」で開きます。
藤原:そこにある「ページ指定」や「部数」の隣に、ボタンがあるよね。
三重:あります。細い三角が上下になっています。
藤原:このボタンは、上の三角をクリックすると数字が1つ上がり、下の
三角をクリックすると数字が1つ上がるのはわかっているよね。
佳代:もちろんです。よく使います。
藤原:うん。このボタンをスピンボタンという。このボタンをセルに配置
すればいいんだ。
三重:なるほど、数字が上下できるスピンボタンを配置すればいいわけで
すね。
藤原:そういうこと。
佳代:それでは、先生、手順を教えてください。
藤原:わかったよ。まずは、ツールバーの上を右クリックして[フォーム]
のツールバーを出してほしい。
三重:えーっと、ツールバーの上を右クリックしてフォームですね。開き
ました。何やら、いろいろなものが並んでいます。
藤原:うん。その中から、上下が三角のマークがあるよね。マウスを近づ
けると、スピンボタンというポップアップが出るだろ。
三重:ハイ、出ました。
藤原:それをクリックしてから、マウスを編集画面に動かすと、カーソル
が、小さな十字架のマークになる。
三重:なりました。
藤原:それで、四角形を描くようにドラッグしてごらん。
三重:ハイ、あ!ボタンができました。
藤原:うん。大きさは、自由に調節できるから、各自やってもらうものと
して、次は、そのボタンをダブルクリックする。
三重:えっと、[コントロールの書式設定]というダイアログが出ました。
藤原:そのダイアログの「コントロール」というタブを開く。
三重:開きました。ああ、ここで設定するのですね。
藤原:そういうこと。後は、「現在値」「最大値」「最小値」「変化の増
分」を設定する。
佳代:後は、どのセルにリンクするかを決めるだけですね。
藤原:そう。このボタンは、特定のセルに働くようにできているから、一
番下の「リンクするセル」に直接入力してもいいし、入力欄にカー
ソルを置いて、セル自体をクリックしてもいい。
三重:これならば、上の三角マークをクリックすると、1つずつ数字が上
がりますね。
藤原:そういうこと。このような操作に慣れていない人は、何回か練習し
て慣れてほしい。そうすれば、エクセルの活用法に幅が出るように
なるから。
佳代:わかりました。大きな数字を細かく動かす場合は、非常に便利です
ね。みなさんも、是非、使ってみてください。それでは、今日は、
この辺で。
-----------------------------------------------------------------
<まとめ> 今回の問題に対する手順のみを示します
-----------------------------------------------------------------
ツールバーの上を右クリックして[フォーム]のツールバーを出す
上下に三角マークのある「スピンボタン」をクリック
エクセルの画面上に、四角形を描くようにドラッグ
(これでボタンができます)
できたボタンの上をダブルクリックして[コントロールの書式設定]
ダイアログボックスを出す
ダイアログボックスの「コントロール」のタブを開く
「現在値」「最大値」「最小値」「変化の増分」を設定する
「リンクするセル」の入力欄にカーソルを置き、変化させるセル自体
をクリック
[OK]ボタンをクリックして完了
-----------------------------------------------------------------
<補足>
-----------------------------------------------------------------
例えば、「現在値」を「1000」「最大値」を「10000」最小値を「0」
「変化の増分」を「1」に設定すれば、上の三角をクリックすれば、数
字が、「1001、1002・・・」と上がっていきます。反対に下の三角を
クリックすれば、数字が1つずつ下がります。
尚、設定を変更したい場合は、スピンボタンの上を右クリックして、
[コントロールの書式設定]を選び、再設定してください。
E∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ あなたの技術をぐ〜んと高めるマガジン ★
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞L
クイズで学ぼう!『エクセル』の小技 268
∞∞∞∞∞∞∞\(^o^)\∞∞∞(*_*);∞∞∞§^O^§∞∞∞∞∞∞∞∞∞∞
◆ 今日の問題 「列幅を変更できないようにするには」
佳代:先生、今回は、読者さんの質問からお願いします。
藤原:いいよ。
佳代:それでは読みます。「初歩的な質問で申し訳ありません。私は、そ
そっかしくて、よく列幅を広げてしまいます。そのたび直している
のですが、何とか、列を固定する方法はないでしょうか?教えてく
ださい」
三重:列幅ねぇ〜。確かに、何回か、間違って広げちゃったことがあるけ
ど・・・。
佳代:それに、ちょっと広げただけで、次のページなっちゃったこともあ
るわ。
三重:ああ、あるある。あれってムカつくのよね。印刷するまで気づかな
いと。
藤原:いろいろ悩みがあるようだね。それじゃあ、今日は、それを問題に
しよう。
佳代:それでは問題です。作業中に列幅が絶対に広がらないようにするに
はどうしたらいいでしょう?みなさんも一緒に考えてくださいね。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ 解答を見る前に,少しだけ考えてね!
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
○ 登場人物紹介
・藤原清盛:塾の先生で,このマガジンの指導役。パソコン歴12年。
・大場佳代:藤原先生の生徒で,パソコン歴3年。
・山形三重:佳代の同級生で,パソコン歴2年半。藤原先生のいとこ。
※ このマガジンは,Microsoftのエクセル2000,2002 の Windows版
のみを扱っています。(たいていの場合、97でも使えます)
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
◆ 解 説 <難易度・・・中級>
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
藤原:絶対に広がらないようにするとなると、ちょっと工夫が必要になる
ね。
三重:そうですね。列幅よりも大きな数字を入れると自動的に広がってし
まいます。
藤原:うん。だから、その点も考慮に入れよう。
佳代:確か、フォントサイズがセル幅にあわせて小さくなるやつがありま
したね。
藤原:そうだね。まず、そこから行こうか。
三重:任せてください。多分、[セルの書式設定]にあったはずです。
藤原:そのとおり。
三重:それじゃあ、Ctrl+Aで、すべてを選択してから、Ctrl+1で、セ
ルの書式設定を出します。
佳代:そして、「配置」のタブを開くのね。
三重:そうよ。確か、ここにあったと思うの。あ!ありました。
藤原:そうだね。この「縮小して全体を表示する」にチェックを入れる。
とりあえず、これで、桁数が大きい場合でも勝手にセル幅が広がる
ことはなくなる。
佳代:そうですね。後は、シートの保護ですね。
藤原:そうだね。そこで、同じ[セルの書式設定]の「保護」のタブを開
き、「ロック」を解除しておく。
三重:そうですね。保護するといっても、セルの操作はするわけですから、
ロックを外さなければなりませんね。
藤原:そのとおり。ここまでできたら、OKをクリックして、ダイアログ
ボックスを閉じ、[ツール]の[保護]から[シートの保護]を設
定する。これで、行や列の幅を変えることができなくなる。
佳代:わかりました。特に、ギリギリで1ページの表を作っている場合な
どは役に立つかもしれません。必要なときにご利用ください。それ
では、今日は、この辺で。
-----------------------------------------------------------------
<まとめ> 今回の問題に対する手順のみを示します
-----------------------------------------------------------------
Ctrl+Aなどで、すべてのセルを選択する
Ctrl+1(テンキーは不可)などで、[セルの書式設定]を出す
([書式]−[セル]でも同じです)
「配置」のタブを開き、「縮小して全体を表示する」にチェックを
入れる
「保護」のタブを開き、「ロック」のチェックを外す
[OK]ボタンをクリック
[ツール]−[保護]−[シートの保護]をクリック
[シートの保護]の項目を必要に応じて設定し[OK]
-----------------------------------------------------------------
<補足>
-----------------------------------------------------------------
単に、列幅を広げたくないというのならば、行列番号を非表示にする
という方法もあります。
[ツール]−[オプション]の「表示」タブを開く
「ウィンドウオプション」の「行列番号」のチェックを外す
こちらの場合、行番号、列番号がないため、手動で幅を広げることが
できません。ですから、「縮小して表示する」にチェックをつけてお
けば、保護をしなくとも幅が広がることがありません。
E∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ あなたの技術をぐ〜んと高めるマガジン ★
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞L
クイズで学ぼう!『エクセル』の小技 269
∞∞∞∞∞∞∞\(^o^)\∞∞∞(*_*);∞∞∞§^O^§∞∞∞∞∞∞∞∞∞∞
◆ 今日の問題 「株式会社を取り除いてフリガナを振るには」
佳代:先生、今回も読者さんの質問からお願いします。
藤原:わかったよ。
佳代:それでは読みます。「会社名のふりがなを、対象文字列から取り出
したい。PHONETIC関数を使うことはわかっているものの、株式会社
(前株も後株も)を抜かした会社名のみのふりがなを取り出したい。
株式会社をぬかした会社名であいうえお順に並び替えたいため。ま
た、このシートは差込印刷の元資料とするため、会社名に必ず株式
会社をつけて書いておく必要がある。」
三重:何か問題みたいな質問ね。
佳代:そうよ。メールにも問題って書いてあったわ(笑)
藤原:それじゃあ、それを問題にしよう。
佳代:みなさんも、一緒に考えてくださいね。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ 解答を見る前に,少しだけ考えてね!
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
○ 登場人物紹介
・藤原清盛:塾の先生で,このマガジンの指導役。パソコン歴12年。
・大場佳代:藤原先生の生徒で,パソコン歴3年。
・山形三重:佳代の同級生で,パソコン歴2年半。藤原先生のいとこ。
※ このマガジンは,Microsoftのエクセル2000,2002 の Windows版
のみを扱っています。(たいていの場合、97でも使えます)
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
◆ 解 説 <難易度・・・中級>
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
藤原:言葉だけではわかりにくいから、簡単な表を使ってやろう。
三重:ハイ。こんなのはどうですか?
┏━┳━━━━━━━━━━┯━━━━━━━━━━━━━━┓
┃ ┃A │B ┃
┣━╋━━━━━━━━━━┿━━━━━━━━━━━━━━┫
┃1┃会社名 │ふりがな ┃
┠─╂──────────┼──────────────┨
┃2┃株式会社やまだ工業 │ ┃
┠─╂──────────┼──────────────┨
┃2┃西山産業株式会社 │ ┃
┠─╂──────────┼──────────────┨
┃2┃株式会社ニチエイ │ ┃
┗━┻━━━━━━━━━━┷━━━━━━━━━━━━━━┛
佳代:本当に簡単な表ね。
藤原:そうだね。でも、わかればいいのだから。
三重:そうです。シンプル・イズ・ベストです。
藤原:問題にもあるように、PHONETIC関数は使うね。
三重:ハイ。そうでないと、ふりがなが取り出せません。
佳代:問題は、どう使うかということね。
藤原:そうだね。これは、SUBSUTITUTE 関数を使う。
三重:サブスティテュート関数ですか?
藤原:うん。この関数は、指定された文字を他の文字に置き換える。この
場合は、PHONETIC関数と併用する。
三重:えーっと、どうやるのですか?
藤原:それほど難しくないネストだ。B1のセルに
=SUBSTITUTE(PHONETIC(A1),"カブシキガイシャ","")
佳代:なるほど、カタカナで入れるのですね。
藤原:そう。基本的にふりがなはカタカナだからね。ただし、設定でひら
がなになっている場合は、ひらがなで入れる必要がある。
三重:入れてみます。そして、フィルハンドルでコピーしてみます。
┏━┳━━━━━━━━━━┯━━━━━━━━━━━━━━┓
┃ ┃A │B ┃
┣━╋━━━━━━━━━━┿━━━━━━━━━━━━━━┫
┃1┃会社名 │ふりがな ┃
┠─╂──────────┼──────────────┨
┃2┃株式会社やまだ工業 │ヤマダコウギョウ ┃
┠─╂──────────┼──────────────┨
┃2┃西山産業株式会社 │ニシヤマサンギョウ ┃
┠─╂──────────┼──────────────┨
┃2┃株式会社ニチエイ │ニチエイ ┃
┗━┻━━━━━━━━━━┷━━━━━━━━━━━━━━┛
佳代:これで、株式会社のないふりがなリストができたわね。
藤原:そうだね。細かいことを言うと、いろいろあるけど、とりあえず、
この方法を覚えてほしいね。
佳代:わかりました。それでは、今日は、この辺で。
-----------------------------------------------------------------
<まとめ> 今回の問題に対する手順のみを示します
-----------------------------------------------------------------
セル「B2」を選択
=SUBSTITUTE(PHONETIC(A1),"カブシキガイシャ","") と入力
フィルハンドルで下方向にコピー
-----------------------------------------------------------------
<補足>
-----------------------------------------------------------------
・この関数の意味は、A1のセルに入力されている文字列のふりがなか
ら「カブシキガイシャ」を探し、あった場合は、「空白」に置換え
なさいということです。
つまり「カブシキガイシャ」が含まれていない部分には何もおきま
せん。
・「カブシキガイシャ」の部分を「カブシキカイシャ」と入力してい
ると、その部分はうまくいきません。その場合、ふりがなを直して
ください。( ↓ を参考にしてください)
http://www.pat.hi-ho.ne.jp/hirosilk/exb056.htm#60
たくさん混在している場合は、いちいち直すのが面倒ですので、
=SUBSTITUTE(SUBSTITUTE(PHONETIC(A1),"カブシキカイシャ","カブ
シキガイシャ"),"カブシキガイシャ","")
のようにして、両方に対応できるようにしてください。
(注意)上の式は、紙面の関係上改行が入っていますが、実際は、
続いています。
-----------------------------------------------------------------
<参考>
-----------------------------------------------------------------
【SUBSTITUTE関数】=SUBSTITUTE(文字列,検索文字列,置換文字列)
http://www.pat.hi-ho.ne.jp/hirosilk/exb181.htm#182
【PHONETIC関数】=PHONETIC(セル番地) フリガナを取り出す関数
http://www.pat.hi-ho.ne.jp/hirosilk/exb056.htm#60
E∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ あなたの技術をぐ〜んと高めるマガジン ★
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞L
クイズで学ぼう!『エクセル』の小技 270
∞∞∞∞∞∞∞\(^o^)\∞∞∞(*_*);∞∞∞§^O^§∞∞∞∞∞∞∞∞∞∞
◆ 今日の問題 「最初のゼロを連結して表示させるには」
三重:先生、ちょっと質問を受けたのですが。
藤原:何だい?
三重:ハイ、例えば、表1ように、市外局番と番号を別々にして、A列と
B列をCONCATENATE 関数を使い連結します。すると表2のようにゼ
ロで始まる番号がうまく連結できないということです。
【表1】
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃市外局番│番号 │電話番号│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃ 72│ 2243│=CONCATENATE(A2,"-",B2) ┃
┠─╂────┼────┼────╋↓───┼────┨
┃3┃ 73│ 4444│ │↓ │ ┃
┠─╂────┼────┼────┼↓───┼────┨
┃4┃ 76│ 0803│ │コピー │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
【表2】
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃市外局番│番号 │電話番号│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃ 72│ 2243│72-2243 │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃3┃ 73│ 4444│73-4444 │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃ 76│ 0803│76-803 ←先頭のゼロが表示 ┃
┗━┻━━━━┷━━━━┷━━━━┷されない┷━━━━┛
佳代:ああ、これは、B列の番号の表示形式をユーザー定義で、0000にし
ているのね。
三重:そうなのよ。これも、わたしが教えてあげたのだけど、つなげると
うまく行かないの。
藤原:そうだね。このままではね。表示形式を全部文字列にしておけば、
問題はなかったけど、0000でやった場合は数値として認識するから
つなげると先頭のゼロがなくなっちゃうね。
三重:もう、すでに全部入力してあるそうです。何とかなりませんか?
藤原:わかったよ。それじゃあ、それを問題にしよう。
佳代:わかりました。それでは問題です。B列の番号は、セルの書式設定
で表示形式をユーザー定義0000を使って4桁表示しています。A列
とB列を CONCATENATE関数を使ってつなげるとき、B列に入力され
た先頭のゼロもきちんと表示させるには、どのような式を作ったら
いいでしょう?
三重:みなさんも一緒に考えてくださいね。
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
★ 解答を見る前に,少しだけ考えてね!
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
○ 登場人物紹介
・藤原清盛:塾の先生で,このマガジンの指導役。パソコン歴12年。
・大場佳代:藤原先生の生徒で,パソコン歴3年。
・山形三重:佳代の同級生で,パソコン歴2年半。藤原先生のいとこ。
※ このマガジンは,Microsoftのエクセル2000,2002 の Windows版
のみを扱っています。(たいていの場合、97でも使えます)
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
◆ 解 説 <難易度・・・中級>
∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞∞
藤原:今回は、予備知識がないとできないから、ちょっとそれを説明して
おこう。まずは、CONCATENATE 関数について。
三重:ハイ、これは、文字列をつなげる関数です。公式は、
=CONCATENATE(文字列1,文字列2,文字列3・・・)
という感じです。もちろん、文字列にはセル番地が使えますし、直
接入れることもできます。
藤原:そうだね。文字列を直接入れる場合は、「""」で囲む。
三重:ハイ、それがエクセルの原則です。
藤原:うん。それから、頭にゼロがついたとき、普通に入力すると頭のゼ
ロは表示されないよね。
佳代:ハイ。標準の形式では、0234なら234 のように頭のゼロは表示され
ません。
藤原:これを表示させる方法は、いくつかあるけど、今回は、ユーザー形
式で表示させたわけだね。
佳代:そうです。[書式]−[セル]で[セルの書式設定]を出し「表示
形式」のタブで、「ユーザー定義」を選択して、「種類」の上の細
長いボックスに「0000」と入力します。すると、頭のゼロも表示さ
れます。
藤原:うん。ここまでの知識がないと問題の意味もわかりにくいよね。
三重:そうですね。
藤原:それでは、解答を言おう。TEXT関数を使うんだ。
三重:ああ!思い出しました。随分前にやりましたね。
藤原:そのとおり。TEXT関数は、=TEXT(セル番地,"表示形式")で表すこと
ができる。
三重:なるほど、わかりました。すると、正解は、C2のセルに
=CONCATENATE(A2,"-",TEXT(B2,"0000"))
と入れればいいのですね。
藤原:正解だ。後は、フィルハンドルでコピーすればいい。
【解答】
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃市外局番│番号 │電話番号│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃ 72│ 2243│=CONCATENATE(A2,"-",TEXT(B2,"0000"))
┠─╂────┼────┼────╋↓───┼────┨
┃3┃ 73│ 4444│ │↓ │ ┃
┠─╂────┼────┼────┼↓───┼────┨
┃4┃ 76│ 0803│ │コピー │ ┃
┗━┻━━━━┷━━━━┷━━━━┷━━━━┷━━━━┛
↓
【結果】
┏━┳━━━━┯━━━━┯━━━━┯━━━━┯━━━━┓
┃ ┃A │B │C │D │E ┃
┣━╋━━━━┿━━━━┿━━━━┿━━━━┿━━━━┫
┃1┃市外局番│番号 │電話番号│ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃2┃ 72│ 2243│72-2243 │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃3┃ 73│ 4444│73-4444 │ │ ┃
┠─╂────┼────┼────┼────┼────┨
┃4┃ 76│ 0803│76-0803 ←先頭のゼロも表示 ┃
┗━┻━━━━┷━━━━┷━━━━┷されている━━━━┛
佳代:CONCATENATE関数を使わなくとも「&」でつないでも同じですね。
藤原:うん。その場合は、=A2&"-"&TEXT(B2,"0000") ほぼ同じ意味だね。
佳代:わかりました。セルをつなぐと表示形式が崩れてしまう場合は、テ
キスト関数を使ってください。それでは、今日は、この辺で。
-----------------------------------------------------------------
<まとめ> 今回の問題に対する手順のみを示します
-----------------------------------------------------------------
セルC2に、=CONCATENATE(A2,"-",TEXT(B2,"0000"))と入力
フィルハンドルでコピー
-----------------------------------------------------------------
<補足>
-----------------------------------------------------------------
B列の表示形式を「文字列」にした場合は、問題文のように
=CONCATENATE(A2,"-",B2)
で、頭のゼロも問題なく表示されます。ですから、式のほうを変え
ず、B列の表示形式を変える方法もいいでしょう。
-----------------------------------------------------------------
<参考> 今回出てきた技に関連したものを示します
-----------------------------------------------------------------
【TEXT関数関係】
http://www.pat.hi-ho.ne.jp/hirosilk/exb121.htm#125
http://www.pat.hi-ho.ne.jp/hirosilk/exb141.htm
★ エクセルの小技へ ★ | ★ TOPのページへ ★ |