 |
 |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ■ はじめに覚えることは ■ 表作成の考え方 ■ 一覧表作成(セル選択) ■ 一覧表作成(書式設定) ■ 一覧表作成(罫線) ■ 一覧表作成(表示形式) ■ 一覧表作成(数式と関数) ■ 一覧表作成(データ操作) ■ 一覧表作成(マクロ) ■ 一覧表作成(条件付書式) |
一覧表の作り方 [9]数式と関数 数式と関数を使わずして何のExcelかな。と言われるように、Excelは関数を使ってこそ、その魅力を輝かせます。 数式や関数を使う上で幾つか押さえ処があります。 (1)数式や関数はセル同士を計算させます。 「x=a+b」という数式では、x、a、bを変数といいます。Excelのセルは、この変数に相当します。 式 x=a+bで、a=3、b=5の時、x=8です。これをExcelを使って記述すると下図のようになります。  (黄色部は見出しです。)Aの値3(C3セル)と、Bの値5(D3セル)を加えた結果を、X(B3セル)に代入する式は、結果を納めるセルに記入します。結果はB3セルに表示しますので、B3セルに数式を入力します。 (ⅰ)B3セルを左クリック。(マウス) (ⅱ)＝を半角入力。 (キーボード) (ⅲ)C3セルを左クリック。(マウス) (ⅳ)＋を半角入力。 (キーボード) (ⅴ)D3セルを左クリック。(マウス) ※セルに数式を入力する場合、必ず「＝」で始まります。＝が入力されると「これから数式入力だな」と、Excelは身構えます。 数式入力で、キーボードから入力するものは、=、+、-、*(掛算)、/(割算) など演算記号だけです。数式の変数に相当するセルの選択は、マウスで左クリックします。数式は、マウスとキーボードを交互に使って入力します。 (2)数式は1つ作成し、コピーして他のセルへ広げます。 B3セルをオートフィルで、1つ下方へコピーします。  オートフィル後、B4セルの数式を見ると、式のC3、D3セルのアドレスは3行目から、C4、D4と4行目を指すように変わっています。 Excelの数式や関数は、セルをコピーするとセルアドレスを変えながらコピーされます。  ※Excelでワークシートに数式を入力する場合、1行(1セル)に数式を作成しコピーして広げるようにします。入力の手間が省けるばかりでなく、セルの追加や削除にも対応できるので運用上便利です。 (3)相対参照と絶対参照 B4セルの数式を少し変えてみました。式の中の「D4」を「D$4」と＄を付けて1つ下へオートフィルします。  B4セルを1つ下へオートフィルし、B5セルの数式を見ると、「=C5+D$4」とC列のセルは5行目を指していますが、D列のセルは4行目を指したまま変化しません。  数式や関数に使うセルアドレスに＄を付けると、セルをコピーしてもセルアドレスは(変化せず)固定されてコピーされます。これをセルの絶対参照といいます。 ※セルアドレスに＄記号付けるには、Ｆ４キーを押します。  上図のように、セルアドレスの上ををマウスで左クリックします。カーソルが点滅している状態でＦ４を押すと、$D$4に変わります。もう一度Ｆ４を押すと、D$4に変わります。Ｆ４を押すたびに、D4→$D$4→D$4→$D4→D4 と変わります。 (4)関数の使用例 数式だけでは解決できないものを関数を使って解決する。数式と関数を組合わせて使う。というように関数と数式は別のものです。 Excelワークシート関数は400以上もあり、技術計算から統計・分析、財務など幅広い分野に対応できるよう作られています。Excelの凄さを垣間見ることができます。 ＳＵＭ関数を使って、合計を求めてみましょう。
(ⅰ)B6セルを左クリック。 (ⅱ)2003はツールバーからΣをクリック。2007では数式タブの関数ライブラリ グループからΣオートSUMをクリックします。 (ⅲ)下図のように、SUM関数を代入するB6セルの周りの状況からExcelが自動判別し、合計範囲を点線で囲みます。  (2003と2007共同じ操作です。)=SUM(B3:B5) は、B3～B5セルの値を合計するという意味です。 自動判別された合計範囲が違っている場合は、この状態で正しい範囲をマウスで選択し直します。Enterで関数入力を確定します。 (ⅳ)B6セルのSUM関数を、オートフィルでC6～D6セルにコピーします。  下図は、C6セルとD6セルにコピーされたSUM関数です。  C6セル(SUM関数)の合計範囲はC3～C5、D6セル(SUM関数)の合計範囲はD3～D5に自動的に変わります。(2)と比べて分かるように、行方向(上下)にオートフィルすると行番号(セルアドレスの数字部)が変わり、列方向(左右)にオートフィルすると、列番号(セルアドレスの英字部)が変わります。 ※絶対参照で「$A$1」と、行・列番号に＄を付けると、上下、左右どちらにオートフィルしてもセルアドレスは変わりません。 「$A1」とすると、列方向(左右)のオートフィルには固定され変化しません。行方向(上下)のオートフィルには$A2、$A3・・・と変化します。 「A$1」にすると、列方向のオートフィルには変化しますが、行方向のオートフィルには固定されます。 ※Excelで、数式や関数を入力する場合、1つのセルに入力し他のセルにコピーして広げる。という方法で作り上げます。換言すればコピーすることを考えて数式を組み立てることになります。 絶対参照を効果的に使うことを求められます。 ●名簿作成上のポイント 名簿や住所録を作る時、使用目的に応じた項目とデータ入力方法があります。 (ⅰ)氏名や会社名など、名前順に並べる場合、ふりがな項目を設けます。 (ⅱ)苗字と名前、商号と種類の表示位置(前株・後株)を分かるようにしておきます。 (ⅲ)マスターファイル化することで、運用と保守の効率化をはかります。 下図のような住所録を作ってみましょう。  ◆入力項目は、黄色で塗りつぶした箇所です。白地の項目は、関数を使って自動表示します。 ふりがなは、PHONETIC関数を使って表示します。下図D5セルには、B5セルに入力された姓のふりがなを表示します。
◆IMEやATOKなど、日本語入力システムには、郵便番号を入力すると住所に変換する機能があります。住所と郵便番号項目は、この機能を使って入力を簡素化します。  上図は、住所欄に郵便番号を入力し、変換キーを押したところです。変換候補に住所が表示されたら、それを選択すれば入力完了です。入力する郵便番号は、XXX-XXXX 形式です。  郵便番号を入力するだけで住所に変換できますが、郵便番号そのものはPHONETIC関数で、住所から再表示させます。作っているときは良いのですが運用が始まると、住所欄に郵便番号を入力する、というところに戸惑いを感じることがあります。そのため、見出しを「住所(〒より変換)」としました。 ※大口ユーザの郵便番号は、328-8588 のように8000番台の枝番が付きます。IMEでは変換できません。 そもそも、8000番台は郵便番号だけで届けられるようになっているので、住所を明記する必要はありません。故に、住所へ変換できないのでしょうか・・。この場合、すべて手入力になります。 ◆下記の設定で、郵便番号による住所変換が可能になります。
◆会社名には、株式会社が前や後ろに付いています。条件で判別し、会社の種類を外して商号のみ、ふりがなを付けると、並べ替えや名寄せなどに効果的です。少し難しくなりますが、IF関数を使って商号を取り出してみましょう。 下図のように、会社名の入力には、セルを2つ使って商号と会社の種類に分けて入力します。入力された文字列を判別し「会社」か「法人」のどちらかの文字があれば、そのセルには会社の種類が入力されていると判断。商号のふりがなは、もう一方のセルから取り出します。(商号は2つのセルのどちらに入力されるか分かりません。) 条件で判定するには、IF関数を使いますが、IF関数だけでは解決できません。IF関数は、数式や他の関数と組み合わせて使います。他の関数のことも知らないとIF関数は使えないので、どうしてもIF関数は難しくなりがちです。考え方をまとめると以下のようになります。 (ⅰ)Find関数を使って、J5セルに「会社」が入力されているか検索します。 =Find("会社", J5, 1) これがFind関数の記述式です。J5セルに「会社」が入力されているか検索します。 (ⅱ)IsError関数を使って、J5セルで「会社」を検索した結果を判定します。 =IsError(Find("会社", J5, 1)) これがIsError関数の記述式です。Find関数の検索結果が、エラーか否か判定します。エラーになる時は「会社」が見つからなかったときです。IsError関数は、エラーのとき真になります。エラーが無ければ偽です。 検索文字は「会社」と「法人」の2つです。「会社」「法人」の両方無ければ商号。片方でも見つかれば会社の種類が入力されていることになります。 (ⅲ)AND関数を使って、「会社」と「法人」の検索結果を判定します。 =AND(IsError(Find("会社", J5, 1), IsError(Find("法人", J5, 1))) これがAND関数の記述式です。IsErrorが2つあり、両方ともエラーのときAND関数は真になります。IsErrorが2つともエラーになるときとは、J5セルに商号が入力されているときです。 (ⅳ)社名ふりがなを表示する(L5)セルに、IF関数を入力します。IF関数の引数は3つあります。はじめに論理式(条件で判断する数式)を入力します。入力する内容は(ⅲ)で示したAND関数です。 関数入力は、マウスの左クリックだけで完結できますが、クリックする位置が重要です。
◆関数を他のセルにのコピーすれば名簿作成は完了です。 コピーの仕方にもいろいろありますので、試してみましょう。  関数は、先頭データ(5行目)のみに入力しました。関数のみ、6行目以降にコピーします。 下図は、5行目全体をオートフィルしたところです。  結果は、関数以外の(黄色で塗りつぶした)セルは、データがコピーされてしまうのでダメでした。  下図左は、関数セル(D5、E5)のみオートフィルしたところです。罫線など書式もコピーされてしまうので、ショートカット ボタンで書式なしコピーにします。関数のみコピーされました。(下図左)
 ◆せっかく関数を入力しても、削除(DEL)キーを押してしまえば関数も消えてしまいます。不用意な削除から関数を守るために、ワークシートに保護をかけます。
|
●健康カード一覧表に、生徒氏名を入力しましょう。 図1のように、健康カード一覧表は1人3行で1セット(3年分)です。 データ件数は、1人3行ですから、学籍№～性別の項目は、総生徒数の3倍入力することになります。 ここは、一工夫して入力量を減らすことを考えなければなりません。  図1 入力データの並び方 入力作業を軽減するために、生徒名簿をつくります。生徒名簿は、一度作れば3年間使用できます。 進級やクラス替えの時は、その項目のみ変更するだけです。  図2 生徒名簿  図3 生徒名簿(ふりがなで並べ替え) 図2の氏名は、苗字と名前の間に空白を入れて入力しています。 図3の氏名は、苗字と名前に分かれています。更に、ふりがな項目をもっています。データ操作上、苗字と名前を分ける必要にかられることがあります。何かしら、苗字名前を分ける手立てを用意しておくと良いでしょう。 ExcelやWordは、漢字入力時のひらがな(ローマ字)を、漢字のふりがなとして使うことができます。特にExcelの場合、他のファイルからデータをコピーしてくると、(入力漢字の)ふりがなが無くなってしまいます。そのため、データ上にふりがなを用意しておくきます。 名簿入力の際、図4のようにPHONETIC関数をセットしておくと、姓と名を入力した時、自動的にふりがなを表示します。 他のファイルから氏名をコピーした場合は、PHONETIC関数を使ってもふりがなは表示されません。PHONETIC関数は、漢字入力字の「仮名入力」から、ふりがなを表示します。  図4 名簿入力 ●名簿作成と、名簿を使ったデータ入力をはじめます。 ①ふりがなを表示する(E4)セルを左クリックします。続けて、数式バーのfx(関数の挿入)を押します。(図5)  図5 関数入力 ②図6のように、関数の挿入ダイアログ ボックスが表示されたら、関数名リストからPHONETIC関数を探します。関数の分類(赤枠)をすべてにすると、アルファベット順に全ての関数が表示されます。  図6 関数の挿入ダイアログ ボックス ③図6でPHONETIC関数を選択し、OKを押すと図7のような関数の引数ダイアログ ボックスが表示されます。 PHONETIC関数は、ふりがなを表示しますので参照(赤丸)には、ふりがなの基になる漢字が入力されている(C4)セルを指定します。参照のボックスを左クリックし、続けて漢字名(C4)セルを左クリックします。参照には、C4とセルアドレスが表示されます。  図7 関数の引数ダイアログ ボックス OKを押すと、E4セルにPHONETIC関数が入力されます。 ④表示されたふりがな(図8 E4セル)は、カタカナ表示になっています。ひらがなに変える場合は、基の漢字セルの設定を変更します。 漢字入力されている(C4)セルを選択(左クリック)し、リボンのホームタブからフォントグループの、ふりがなの設定コマンドをクリックします。  図8 ふりがなの設定(Excel2007) 図8、ふりがなの設定ダイアログ ボックスで種類を、ひらがなに変更します。配置を見ると左寄せになっています。文字配置のサンプルのように、漢字に左揃えで詰まって表示されます。漢字と同じセルに、ふりがな表記ではないのでこのままにしておきます。 下図は、Excel2003でのふりがな設定です。2007のリボンと操作は異なりますが、ふりがなの設定ダイアログ ボックスは同じです。  図9 ふりがなの設定(Excel2003) 配置を均等割り付けにした場合は、赤角枠のように表示されます。 ⑤オートフィルで、ふりがな全体のセルへコピーします。  1行作ったら、全体へコピーします。  図10 オートフィルで関数をコピー ⑥健康カード一覧表に生徒氏名を入力します。 入力とは言っても生徒名簿のデータを一覧表に表示させるだけです。 図11(赤枠)のように、1人分のデータを入力します。学籍番号は、同じ番号を3行で1セットとし+1 しながら数式を使って人数分つくります。年次は1、2、3の繰り返しです。  図11 健康カード一覧表に入力  3セル範囲選択し、オートフィル。左ボタンを離すとき、Ctrlを押しながら離すと、123の繰り返し入力できます。  9行目の数式を下方へオートフィルします。 数字に前ゼロがありません。 数式の先頭に、"0"& を付けます。「＆」は、文字と文字を結合するときに使う演算記号です。0と7002を結合して強制的に、07002 にします。 ⑦学籍№が出来上がったので、数式を飛ばし値のみ保存します。 数式のままでも問題ありませんが、Excelは数式や関数があると、都度再計算を繰り返し動きが遅くなります。そのため不要な数式や関数は外します。 次年度の学籍№作成のために最後の数行に数式を残しておき、次年度はその数式をコピーするという方法もあります。  図12 数式を飛ばす 学籍№の範囲(B6～B95)を選択し、右クリックでダイアログ ボックスを表示します。コピーを選択するとダイアログ ボックスは消えるので再度、同じ(B6～B95)選択範囲の上で右クリックし、形式を選択して貼り付けを選択します。形式を選択して貼り付けダイアログ ボックスから値を選びます。 OKを押して完了すると、数式はなくなり値のみ保存されます。 ⑧学籍№をキーとして名簿を検索し、一致したデータの氏名と学年、組、番号、性別を、健康カード一覧表にVLOOKUP関数を使って表示します。  図13 名簿から氏名を表示 図14のように、氏名を表示する C6セルを左クリックします。数式バーのfx(関数の挿入)でダイアログ ボックスを表示し、VLOOKUP関数を選択します。  図14 VLOOKUPの引数ダイアログ ボックス VLOOKUP関数の検索値とは、名簿を検索するときのキー項目なので、学籍№(B6セル)です。範囲は、生徒名簿のデータ範囲(図15赤枠)です。 生徒名簿は別シートに作られているので、VLOOKUP関数ダイアログ ボックスの範囲ボックスを左クリックし、生徒名簿シートへ移り名簿のデータ範囲を選択という手順で指定します。(見出し部の選択は不要です。) 列番号とは、名簿範囲内の列順です。図15の赤枠内最左列が1列目です。氏名を表示するには、2列目の項目を参照するので、ダイアログボックスの列番号は2です。 検索の型には0か1が入ります。0の場合は、検索値が完全一致する場合です。学籍番号は、名簿上の学籍№と完全一致したものを参照しますので、0を指定します。  図15 生徒名簿の範囲 図16のC6セルを見ると、出来上がったVLOOKUP関数では、苗字しか表示されません。名簿の設定が苗字と名前に分かれているからです。文字列の結合で氏名を表示します。 健康カード一覧表のC6セルを左クリックし、図16の数式バーのようにVLOOKUP関数の後ろに &" "& を入力します。(＆は文字列結合の演算記号です。) 数式バーの最右端にカーソルが置かれている状態で、名前ボックス(左図参照)からVLOOKUP関数を選択します。関数の引数ダイアログ ボックスが表示されたら、図16のように入力します。列番号は、名前を表示するので3になります。  図16 氏名の表示  表示された氏名です。苗字と名前の間に、半角スペースが入っています。文字列結合したときに入力した、&" "& の " " が半角スペースの挿入です。 学年～性別まで、同じようにVLOOKUP関数で表示します。1ヶ所違うのは列番号です。VLOOKUP関数の引数、列番号は学年=7、クラス=8、番号=9、性別=6です。  図17 VLOOKUP関数をコピーして使う 図17は、名簿から学年を表示するVLOOKUP関数の書式です。この関数をG列までオートフィルし、その後列番号を変更すれば、関数のダイアログ ボックスを開かなくとも簡単に関数入力できます。 図18は、学年(D6セル)の関数をクラス(E6セル)にコピーしたものです。E6セルで #N/A とエラーが表示されています。  図18 関数コピー時のエラー E6セルにコピーされた関数を見ると、VLOOKUPの検索キーとなる学籍№の指定がC列に変わっています。これは、セルをコピーすると、入力されている数式や関数のセルアドレスが変わる相対参照になっているためです。 VLOKKUPの引数、検索値は$B6、範囲は$B$4:$J$31と、絶対参照にしなければなりません。B6が、$B$6ではなく$B6になるのは、行方向(下に)コピーするときは行番号に合わせて変化させるためです。セルアドレスの上でＦ４を押して絶対参照にします。 図19のように、数式バーに表示された関数名の上にカーソルを置き、fxを押すと関数の引数ダイアログ ボックスが表示されます。 セルアドレスの上にカーソルを置きＦ４を押すとセルアドレスに＄記号が付きます。F4を押す度に、B6→$B$6→B$6→$B6→B6と繰り返し変化します。  図19 セルの絶対参照 健康カード一覧表から学籍№で名簿を検索し、一致したデータの内容を表示するVLOOKUP関数の設定が終わりました。列方向にG列までコピーし、関数の列番号を変更します。1行できたら、人数分オートフィルします。 図20で、学籍№07029と07030の表示がエラーになっています。これは、名簿に一致する学籍番号が存在しないためです。  図20 健康カード一覧表完成 ※関数を入力するときは、セルの表示形式が文字列になっていると上手く動きません。文字列になっていたら、標準に戻してから関数を入力します。 入力後、表示形式を変えても問題ありません。図20の氏名は関数で表示した後ユーザ定義で半角1文字字下げしています。 ※下図の赤枠の位置を、名前ボックスといいます。  セル範囲を選択し、名前ボックスに任意の名前を入力します。Enterで確定すると生徒名簿!B4:J33とセルアドレスの代わりに付けた名前を使うことができます。  セル範囲名前を付け、VLOOKUPで引用した例です。  VLOOKUP関数は、別シートの範囲でも参照できましたが、関数によっては関数を入力するシートと同じシート内しか参照できないものがあります。そういう場合でも、名前を使えば解決できます。 名前ボックスは、登録は出来ますが名前の削除はできません。Excel2003ではメニューバーの挿入から名前に入り、定義に進みます。表紙されるダイアログ ボックスで範囲の変更や削除ができます。  Excel2007では、リボンの数式タブから名前の管理コマンドをクリックします。  |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
