 |
 |
||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| ■ はじめに覚えることは ■ 表作成の考え方 ■ 一覧表作成(セル選択) ■ 一覧表作成(書式設定) ■ 一覧表作成(罫線) ■ 一覧表作成(表示形式) ■ 一覧表作成(数式と関数) ■ 一覧表作成(データ操作) ■ 一覧表作成(マクロ) ■ 一覧表作成(条件付書式) |
一覧表の作り方 [10]データ操作 Excelは、データベースとしても十分に機能します。リレーショナル型データベースのAccess。カード型データベースのFileMaker。Excelは表型データベースです。 データベースとしての基本要件は、検索と抽出機能です。テーブルからビューを作る。つまり、2つの表を結合し3つ目の表を作成するという操作がデータベース操作の基本なのです。 OracleやSQL Serverなど本格的データベースにはセキュリティや障害対策など、様々な機能が組込まれており大規模システムに対応できるようになっています。大規模システムとは、一度に複数ユーザの大量データを処理するものです。これに対しExcelは、自分の仕事を処理する目的で使われます。 ここでは、自分自身の仕事を便利に進めるためのExcelデータ操作を解説いたします。 ●オートフィルターとフィルターオプション。 Excelではフィルターを使ってデータ抽出します。フィルターには、オートフィルターとフィルターオプションという2つの機能があります。似ていますが、この2つは別のものと考えたほうが良いでしょう。どちらも、データ項目名は必要です。
 上図は、抽出された範囲をコピーし、他の(B92～)セルへ貼り付けたところです。非表示のセルは選択から除外されでコピーされますので、抽出データだけを使って別な処理をすることもできます。 ◆抽出データをコピーしなくても、自動的に他のセルへ貼り付ける方法があります。それがフィルターオプションです。フィルターオプションは、オートフィルタより複雑な条件での抽出も可能です。
 見出し項目を左クリックし、フィルタ オプションの設定ダイアログを開くと、リスト範囲(データ範囲)が自動選択されます。抽出先が選択範囲内になっています。この状態ですと、オートフィルタ同様、抽出データが表示され抽出されなかったデータは非表示になります。  抽出先を指定した範囲に切り替え、抽出範囲のボックス(赤角丸)を左クリック後、抽出先セル(赤角枠)を左クリックすると、抽出先セルアドレスが表示されます。OKを押して完了です。 下図は抽出結果です。N6セルを起点にデータがコピーされています。  ここでは、抽出条件を設定していなかったので、全データがコピーされました。 3年A組の3年次データを抽出してみましょう。フィルタ オプションでの抽出条件は、任意の位置に自分で指定します。 下図、D2～H3セル範囲が抽出条件を指定する範囲です。D2～H2セルの項目名は、データ項目名に一致しています。D3～H3セルに抽出条件を入力します。 学年欄に3、学級欄にA、年次欄に3 と入力しました。項目名と一致する列のデータが、指定した内容と同じものを抽出します。  フィルタオプションの設定ダイアログ ボックスの記述です。OKを押して抽出します。  下図は、抽出結果です。  2年生の2年次と、1年生の1年次データを抽出する設定です。抽出条件範囲が3行になっています。抽出条件は、列方向にAND(且つ)、行方向にOR(又は)という意味があります。 2年生で且つ2年次、又は1年生で且つ1年次という意味です。  下図は、抽出結果です。  下図は、リスト範囲(データ範囲)を氏名に限定しています。抽出条件は設定しません。重複レコードは無視にチェックを付けて実行したとごです。氏名は1人3行ありましたが、先頭の１行のみ表示されています。  ※重複レコードは、指定したリスト範囲内でチェックします。リスト範囲を学籍番号から年次までに広げると、その項目範囲で重複しているデータはありませんから、全て表示されます。 ※下図は、(上図の)4行目の空白行を削除し、B4セルを選択してフィルタ コマンド(オートフィルタ)を押したところです。抽出ボタンは選択した4行目ではなく、表の最上端の2行目に付いています。フィルタや並べ替えは、Excelがデータ範囲を勝手に決めてしまうので見出し項目の作り方に一工夫必要です。  ◆ワイルドカードの使い方。 下図の健診データの中から、ワイルドカードを使って男女別に疾患数を求めてみましょう。
 上図のように、検索条件を空白にすると、全てのデータを抽出することになります。B3～D3セルで抽出条件を指定しても、Ｂ4～Ｄ4で全てのデータを指定しているので、結果は何も抽出されません。 フィルタ オプションの設定ダイアログボックスの検索条件範囲に気を付けてください。(上図の設定は、耳を含む女子、又は、全てのデータを抽出するという意味になります。) ※ワイルドカードの指定で、＝”＝＊耳＊”とイコールが2つあります。 ＜＞の場合は問題ありませんが、先頭に＝があると、セルは数式が入力される準備をします。＝＊耳＊だと数式にならないので入力エラーになります。そこで、=*耳* を " " で囲み入力しています。＜＞の場合は＝とは違い、問題ありません。 ※数値を範囲で抽出するときは、＞＝50 (50以上)、＜80 (80未満)と不等号を使って入力します。 マクロの使い方は、次のページで詳しく解説いたします。 |
●健康カード一覧表にデータを入力します。 健康カード一覧表入力は、3年間の計測データを記録します。 図1のように、現在の学年に一致した年次行へデータ入力します。 連続で入力する場合、数行おきに入力することになりますが、これでは入力作業が捗りません。そこで、図2のように入力する行のみ抽出し、データを入力します。入力しない行は非表示ですので、画面もすっきりします。  図1 学年と一致した年次に入力 図2は、学年、クラス、番号で並べ替えています。  図2 学年と一致した年次を抽出 ①年次データ抽出は、図3のように抽出条件範囲(D2～H3セル)に入力します。抽出する際、学年と年次は必ず等しいので、年次(H3セル)に、学年(D3セル)を参照する数式を入力します。これで、D3セルに学年を入力すると、H3セルに同じものが表示されます。  図3 セルの参照 (ⅰ)H3セルを左クリックします。 (ⅱ)H3セルに ＝ を入力し、続けてD3セル(赤角枠)を左クリック。H3セルには、＝Ｄ３ と表示されます(赤丸)。これで、D3セルに入力するとH3セルにも同じものが表示されます。これをセルの参照といいます。 ②学年に入力するものは、1～3(小学校なら1～6)に決まっていますから、リストで選べるようにすると便利です。リスト選択は、入力規則で設定します。   図4 入力規則(上が2003、下は2007) (ⅰ)リスト選択を設定する(D3)セルを左クリックします。 (ⅱ)Excel2003は、メニューバーのデータから入力規則を選びます。2007では、リボンのデータ タブからデータ ツール グループにあるデータの入力規則 コマンドを選び、更にデータの入力規則へ進みます。表示されるダイアログ ボックスは2003、2007とも同じです。  図5 データの入力規則 (ⅲ)データの入力規則ダイアログ ボックスから、設定タブの入力値の種類をリストにします。 (ⅳ)入力値の種類をリストにすると、図6のように元の値を入力するボックスが表示されます。学年はデータに半角入力されているので、半角数字で１，２，３ と入力します(赤角丸枠)。コンマは必ず半角です。  図6 元の値 学年をリスト選択(赤角枠)できるようになりました。 ③クラスもリスト選択にします。クラスは、学年により学級数が異なるので、クラス名を表にします。この表はどこに作っても良いのですが、図7のように、生徒名簿の側に置いておくと、新年度にまとめて処理できるので便利です。  図7 クラス名表 ④学年を選択すると、表のカラムを自動的に切り替えることで、その学年に合ったクラス表示をできるようにします。 クラス名のセル範囲を選択(図8赤枠)し、名前ボックスで学年3と入力します。(名前は「1学年」のように数字が先頭にあるとエラーになります。)  図8 クラス名に名前を付ける S2～W2セルの範囲を、学年1。 S3～W3セルの範囲を、学年2。 S4～W4セルの範囲を、学年3と名付けました。 この名前、学年の項目名(D2セル)と、D3セルでリスト選択した学年を結合した名前なのです。文字の結合は＆で行います。 D2&D3とすると、クラス範囲の名前になります。クラス範囲に付ける名前に、リスト選択で選ばれた値(学年1～3)を使うことで、学年によりクラス名を切り替えることができるのです。 ⑤図9のように、学級をリスト選択にしましょう。 (ⅰ)E3セルを左クリックし、データの入力規則ダイアログボックスを表示します。 (ⅱ)設定タブの、入力値の種類をリストにし、元の値のボックスに、＝ＩＮＤＩＲＥＣＴ（ と入力し、D2セルを左クリックします。続けて＆を入力し、D3セルを左クリックします。最後に ） かっこを閉じて終了です。(=INDIRECTから一連の操作です。) OKを押すと、エラーメッセージが表示されることがあります。  はいを押します。 図9 INDIRECT関数を使う INDIRECT関数は、引数に指定された名前の示す内容を返します。  図10 フィルタ オプションで抽出 フィルタ オプションを使って3年生のデータを抽出したところです。 ◆抽出したデータをクラス、番号順に並べ替えましょう。 図11と、図12は同じデータを学年、組、番号順に並べたものです。 図11は、番号を見るとA組1番から始まり、E組まで学級でまとまっています。当然、学籍№は順不同です。図12は、学籍番号順に並び、学級でまとまっていません。 計測データを見ると、図11と図12に違いはありません。並べ替えが正しく行われていないのでしょうか。  図11 データの並べ替え  図12 データの並べ替え(関数セル) セルには、値、数式、書式、の3つが別々に記録されています。並べ替えはセルを並べ替えるのではなく、セルの値を並べ替えます。 数式や関数はセルに記録されているので、セルが動かない限り移動しません。行や列を挿入すると、セルそのものが動きますので、数式や関数も一緒に移動します。 並べ替えは、セルの値が移動し、数式や関数はセルに固定されたままなのです。  図12の学籍№は数式表示です。図12の学籍№には、数式が入力されているので並べ替えを行っても表示される値は変化しません。(図11は値です。) 図11の学籍№ 07001～07010 は値入力されています。図12は、学籍№ 07001 のみ値入力で、07002～07010は数式で表示しています。図11の学籍№は並び替わりますが、図12の学籍№は数式表示です。数式が並び変わらないので表示も替わりません。 氏名～性別までの項目は、図11、図12共にVLOOKUP関数で名簿の内容を表示しています。VLOOKUPの検索キーは学籍№です。 学籍№の2行目(B11セル)に、07006が並べ替えられてくると、C11セルのVLOOKUP関数は 07006 を検索キーにして氏名(千明 守)を表示します。一方、学籍№が数式だと並べ替えられませんから2行目(B11セル)は 07002 のままです。氏名～性別まで表示するVLOOKUP関数のキーが変わらないので、VLOOKUPが表示する内容も変わりません。計測テー他は全て値ですから並べ替えられています。 年度が変わり、新入学に対応する場合、学籍№のみ追加すれば、氏名～性別までの項目はVLOOKUP関数をコピーするだけで良いのです。(卒業生は当該行を削除するだけです。) ⑥並べ替えは、Excel2003ではメニューバーのデータから並べ替え。2007ではリボンのデータから、並べ替えコマンドを選択します。表示されるダイアログ ボックスも2003と2007で操作が異なります。 図13のように、任意の見出し項目を選択してから並べ替えダイアログ ボックスを表示すると、図14のように、データの選択範囲は2行目からデータの最終行までを指します。先頭行をタイトル行として認識します。(先頭からデータ行が始まる場合、データ範囲の先頭行のボタンををデータにします。)  図13 Excel2003の並べ替え Excel2003では、並べ替えに指定できる項目は3つです。項目は、リストより選択します。昇順(小～大)、降順のオプションもあります。  図14 並べ替えダイアログ ボックス(2003) Excel2007でも、データ見出しの任意の項目を左クリックし並べ替えコマンドを選択します。表示される並べ替えダイアログ ボックスで指定できる項目数は64です。追加ボタンを押して項目を選びます。  図15 Excel2007の並べ替え  図16 並べ替えダイアログ ボックス(2003) ◆マクロの登録 フィルタ オプションを使ったデータ抽出をマクロに登録することで、運用がとても楽になります。 Excel2003では、メニューバーのツールからマクロを選択し、新しいマクロの記録に進みます(図17)。2007では、リボンの開発タブからコード グループのマクロの記録コマンドをクリックします。(図18)  図17 Excel2003 新しいマクロの記録  図18 Excel2007 マクロの記録 Excel2007のリボンに開発タブが表示されていないときは、オフィス ボタン(赤丸)を押し、ダイアログ ボックスの最下端に表示されているExcelのオプションの基本設定で、図19開発タブを表示するにチェック(赤丸)を付けます。   図19 リボンに開発タブを表示する方法 ⑦マクロの記録方法は、2003と2007に違いはありません。 途中の操作を間違えると、始めからやり直しになりますので、ゆっくりで良いですから間違わずに行ってください。 抽出条件として図20のように、学年に(1～3の何れかを)入力します。  図20 学年に抽出条件入力(年次は自動表示) 以下の手順で記録を開始します。 (ⅰ)マクロの記録ダイアログ ボックスを表示。  図20 マクロの記録ダイアログ ボックス (ⅱ)OKを押して、記録開始。ここからは、間違えないように。 (ⅲ)データの任意の見出し項目(B5セル=学籍№)を左クリック。  図21 任意の見出し項目を選択 (ⅳ)2003では、メニューバーのデータ→フィルタ→フィルタ オプション。2007では、データ タブ→詳細設定。フィルタ オプションの設定ダイアログ ボックスを表示。  図22 フィルタ オプション ダイアログボックス 赤枠の、リスト範囲と検索条件の範囲を確認し、違っていたら再度選択しなおす。(ボックス内をドラッグしで黒く反転させ、正しいセル範囲を選択) (ⅴ)OKを押して、抽出実行。 (ⅵ)Excel2003では、メニューバーのツールからマクロ→記録終了。2007では、リボンの開発タブからコード グループの記録終了。  図23 Excel2003マクロの記録終了  図24 Excel2007マクロの記録終了 これで、完了です。途中、操作を間違えたら一旦、記録を終了(ⅸ)させ、再度(ⅰ)に戻ってやり直します。 ※登録したマクロを確認してみましょう。 (ⅰ)Excel2003では、メニューバーのツールからマクロ→マクロ。2007では、リボンの開発タブからコード グループのマクロです。  図25 Excel2003登録されているマクロ  図26 Excel2007登録されているマクロ  図27 マクロ ダイアログ ボックス |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||
