odake's web pageホーム | パソコンで楽しむ | Excelで遊ぶ | 出品一覧表から受賞者一覧表へ
Wordで展覧会出品作品につけるラベルを作成する差し込み印刷の方法を掲載してあります。参照
その時に使ったExcelのデーターファイルを利用して、受賞者一覧表を作る方法をここで検討してみます。作業を進めながら、Excelの色々な機能を身につけます。
大量のデーターを扱うときは、入力したデーターと同じものを再入力すると、間違う可能性が増します。最初に入力したものの間違いがないことを点検したら、そのデーターを使って他のドキュメントを作る方が効率的です。
Wordの差し込み印刷のときと同様にExcel 2007 を中心に説明し、Excel 2003 と Excel 2010については必要なときに補足します。
差し込み印刷に使ったデーターファイルは、Seet1に次の通り入力されています。
いずれの場合も両方のファイルをパソコンに保存してから開いてください。
Excel 2003 の場合
Excelのデーターファイル sampledata.xls 23.50KB
Excel 2007/2010 の場合
Excelのデーターファイル sampledata.xlsx 11.64KB
作成する「受賞者一覧表」は、上位から下位の賞の順に、同位の賞は学年の下級から上級の順に学校・園別に並べ替えるため、差し込み印刷に使ったデーターファイルを編集します。
Sheet1はWordの差し込み印刷に使用するのでこのまま残し、コピーしたシートで作業を進めます。
以上で、Sheet2の前へ[Sheet1(2)]というシートが作成されます。Sheet1(2)のタブをダブルクリックすると、シート名が選択され名前を変更できますので「作業シート」と変更します。
タブにマウスポインタを乗せて右クリックしメニューの[名前の変更]をクリックしてもシート名を変更できます。
データーが沢山あり、データーファイルを分割して作成していた場合で「受賞者一覧表」を作るときは、データー範囲をコピー&貼り付けで一つのシートに纏めておかなければなりません。
作業シートの表では全体が一連番号になっていますが、データーが大量にあるときは、校園名ごとに一連番号をつけた方が整理しやすくなります。
たとえば小学校は「A+番号」、保育園は「B+番号}、幼稚園は「C+番号」と順序のある文字で区別します。入賞者一覧表には同一賞内はこの文字列の順に並べ替えられます。
例:A1-1、A1-2、B1-1、C1-1、C2-1、C2-2・・・
こうすることにより追加の出品があったり、同一学校・園同じ賞を受賞した作品がが複数あっても、校園名ごとに整理できます。
セルに直接入力したりオートフィルを使って複写したりでも出来ますが、このページの終わりの付録2 番号の入力で学校・園の符号と学校・園内の一連番号を結合する方法を説明しています。
作業シートで列番号の[B]をクリックしてB列を選択し、右クリックしてメニューの[挿入]をクリックすると、新しい列が挿入されますので、セルB1の項目名に[賞名]と入力します。
賞名は「特別賞」「金賞」「銀賞」「入選」などと順序ががありますが、このままではパソコンは判断できませんので、順序のある[コード]の列も挿入します。
作品の審査が終わったら、受賞した作品の「賞名」と「コード」に入力します。コードは例えば特別賞は「1」、金賞は「2」、銀賞は「3」、入選は「4」と定めます。
賞名をセルに直接入力しても良いのですが、関数を使いコードを入力して賞名を表示させる方法を、付録1 賞名の入力方法で説明しています。
小学校の学年と保育園・幼稚園の才児を同じ列に入力してあるので、このままでは並べ替えすることが出来ません。
「変換」の列を作り、保育園・幼稚園については「4歳児」「5歳児」のままで「4」か「5」、小学生は列「学年」の値に「5」を加えた数値を入力します。
データー数が多いときは列[変換]のセルに一つずつ入力するのは大変ですから、少し複雑になりますが数式を入力します。
例えば変換列が「H」列の場合、セルH2に[=IF(E2="小学校",F2+5,F2)]と入力します。入力したセルH2をH3以下データーのある行にコピーします。
IF関数を使っています。数式は =IF(E2="小学校",F2+5,F2)
セルE2の値が「小学校」であれば、セルF2の値に5を加えた数値を入力し、「小学校」でなければ(保育園または幼稚園の場合)、セルF2の値を入力する。
キーボードから上記の数式を直接入力してもよいが、Excelにはダイアログボックスで入力できる機能があります。[関数の引数]ダイアログボックスで下の[この関数のヘルプ]をクリックするとヘルプがインストールされていれば使い方が表示されます。
作業シートの編集が終わり、並べ替えて入賞者一覧表を作成が出来る状態になりました。
ここまでのサンプルファイル。
いずれの場合も両方のファイルをパソコンに保存してから開いてください。
Excel 2003 の場合
並べ替え前のファイル sampledata2.xls 29.0KB
Excel 2007/2010 の場合
並べ替え前のファイル sampledata2.xlsx 14.5KB
以上で受賞した作品のデーターが表の上部に並べ替えられました。このままでも受賞者一覧表になりますが、次にこの並べ替えられたシートを使ってシート体裁を整えます。
前項は「受賞者」の一覧表でした。審査前の出品一覧表を作る必要のある時は、表の[番号]の列のどれかのセルを選択して、昇順並べ替えをすれば、小学校・保育園・幼稚園の順になります。
[並べ替え]ダイアログボックスを使うときは、[最優先されるキー]に[番号]を選んで[OK]ボタンを押すと同じ結果になります。幼稚園・保育所を先にならべるには、[番号を校園名ごとに]の項の[番号]の A・B・C の順を変える必要があります。
受章者一覧表は、同じブックのShrrt2を使い、作業シートのセルを参照して表示させます。
Sheet2のシート名(タブ)をダブルクリックして、[受章者一覧]と入力します。
シート[受章者一覧]のB3に「賞名」と表示されています。このセルをオートフィルで下にコピーすれば特別賞から入選まで表示されます。[作業シート]のセルを参照しています。
[受章者一覧]のC3には、[作業シート]のF1を参照すれば「学年」と表示されますが、C4には「2年」と[作業シート]のF2とG2の2つのセルの内容を表示させる必要があります。
数式バーには、[=作業シート!F2&作業シート!G2]と表示されているが、「年」の前へ半角のスペースを入れるときは、[=作業シート!F2&" "&作業シート!G2]と["](ダブルクォーテーション)で囲んだスペースを入れると間隔をあけることが出来ます。校園名も同じ処理をします。
受賞者の通し番号が必要な時は、A列に番号の列を作るなど、必要な体裁を整えて印刷します。
入賞者数が多く用紙1枚に入らず複数枚になったときは、各ページの上部にこの場合ならシートの3行目を表示させた方が見やすくなります。
いずれの場合も両方のファイルをパソコンに保存してから開いてください。
Excel 2003 の場合
並べ替え前のファイル sampledata3.xls 35.5KB
Excel 2007/2010 の場合
並べ替え前のファイル sampledata3.xlsx 17.5KB
Excelの関数で[VLOOKUP]という表引きの関数があります。これを使うとコードに数字を入力すると別のセルに賞名を表示することが出来ます。少し難しいですが、入力する件数が多いときに使うと便利です。
VLOOKUP(検索値, 範囲, 列番号,[検索の型])
Excelのヘルプには、指定された範囲の 1 列目で特定の値を検索し、その範囲内の別の列の同じ行にある値を返します。
とありますが、Excelのヘルプに馴れないと判り難いです。
L | M | |
---|---|---|
20 | コード | 賞名 |
21 | 1 | 奨励賞 |
22 | 2 | 金賞 |
23 | 3 | 銀賞 |
24 | 4 | 入選 |
前記の「データーファイルの編集」の「賞名・コードの列を作り入力する」で、[賞名]と[コード]の列を作ってから、作業シートの使用しない範囲(例えばL20:M24)に右のように入力します。
列・行共表に使わない範囲に入力します。L1:M5などでは並べ替えると機能しなくなります。
右の例で、VLOOKUP関数の2校目の引数の[範囲]は、項目名の1行目は入らずセルL21を左上、セルM24を右下の範囲(Excelではセル名を半角のコロンで結ぶ[L21:M24]と表します)です。この範囲に名前を付けて、引数に範囲名を入力することも出来ます。
範囲に名前を付けるには、範囲を選択して右クリックし、メニューから[範囲に名前を付ける]をクリックしてダイアログボックスで設定します。[数式]タブの[定義された名前]グループの[名前の定義]でも同じ。Excel 2003の場合は、メニューバーの[挿入][名前]をポイントして[定義]をクリック。。
VLOOKUP関数を入力するセルは、前記「賞名・コードの列を作り入力する」で作ったB列の「賞名」のセルです。
コードを入力するだけで賞名を入力するため、セルB2の数式をB列の他のセルにコピーするが、範囲を[L21:M24]と相対参照のままではコピー先に異なる値が入力されるので、絶対参照にする必要があります。
セル番地の入ったセルを他のセルに複写(コピー&貼り付け)するには、コピー元のセルにあるセル番地の列番号・行番号の前に[$]を入力します。
数式を [=VLOOKUP(C2,$L$21:$M$24,2)] にする方法は、数式バーで[L21]と[M24]の列番号と行番号にキーボードから$を入力するか、セル名の中にカーソルを入れF4キーを押します。
例えば[L21]の場合Lと21の間・Lの前・21の後のいずれかにカーソルを入れて、F4キーを押します。最初は列番号と行番号の両方に、次に押したときは行番号のみに、次は列番号のみに[$]を付けます。4回目は両方とも[$]が付かない元の値になります(4種類の繰り返し)。
[$]をつけた部分が絶対参照になり、他のセルに複写しても変化しません。
前項でVLOOKUP関数を入力したB列には、C列にコードを入力してないセルには、[#N/A]というエラーが表示されています。これは印刷されますので、表示しないように「学年と才児の変換」の項で使った、IF関数を使います。ここでは少し詳しく説明します。
書式 IF(論理式, [真の場合], [偽の場合])
括弧の中の引数の[論理式]が真(TRUE)の時は引数の[真の場合]を表示し、偽(FALS)の時は引数の[偽の場合]を表示します。
この場合セルB2の場合は、「C列に賞名のコードが入力されていない時は、B列に何も表示しない」ようにするので、IF関数の論理式は「C2=""」、真の場合は[""]、偽の場合は[VLOOKUP(C2,$L$21:$M$24,2)]とします。
実際の入力は関数のダイアログボックスを使うより、キーボードから直接入力する方が簡単と思います。
入力の結果は =IF(C2="","",VLOOKUP(C2,$L$21:$M$24,2))となります。IF関数の引数の論理式にISBLANK関数を使い、=IF(ISBLANK(C2),"",VLOOKUP(C2,$L$21:$M$24,2)) でも結果は同じです。
作業シートの[番号]の入力は、「データファイルの編集」の「番号を校園名ごとに」で、[種類]と[学年]別に符号を作り一連番号を入力するようにしていました。これをデーターごとに一つずつ入力するのは大変ですのでここで検討します。
Excelの表はリスト形式になっていれば、データーベースとして使用できます。[番号]がなくても並べ替えやフィルタの機能を使うことが出来ますが、データの中に他のデーターとは違う独特の(一意・ユニークの)項目があると元の順に戻すときなど便利です。
作業シートを作ってから、並べ替えのキーを[最優先]に[種類]、[次に優先]に[校園名]、さらに[次に優先]に[学年]を指定して並べ替えると、[種類][校園名]の文字(文字コード?)の順になりますが、それぞれの学校・園内は学年ごとにまとまります。もし学校・園の順序を五十音順にしたいときは、「校園名の読み」の列を作って並べ替えのキーにして並べ替えます。
[読み]の列を作って並べ変える方法は、同一学校・園内の氏名の順にも使用できます。[読み]の列の入力の方法に、PHONETIC関数を使うことが出来ます。参照:付録3 [読み]の入力方法
A列を選択し新しい列をA列の前へ挿入し、新しいセルA1に[校園コード]と入力します。
校園コード列にA1、A2、A3、B1、C1などと入力します。校園毎にまとまっていますから同じコードはオートフィルで複写をすれば大くても簡単です。
B列を選択しB列の前へ新しい列を作り、新しいセルB1に[校内No]と入力します。この列に同じ学校・園内の一連番号を入力します。氏名の五十音順にしたいときは、予め[氏名の読み]の列を作り並べ替えておきます。
セルC2に[=A2&"-"&B2]と数式が入力され、セルA3の[番号]列に[A1-1]などと表示されています。この数式を[番号]列の他のセルに複写すれば、ユニークな番号が入力されます。
氏名などを並べ替える時に、漢字の氏名をキーにして並び替えると、漢字の順(多分文字コードの順)になります。読みをキーにすれば五十音順にすることが出来ます。新しい列を作ってキーボードから入力するのはデータが多いときは大変です。ここでは PHONETIC 関数を使って漢字のふりがなを使う方法を使います。
漢字の入力されているセルを選択して、[ホーム]タグの[フォント]グループ[ふりがなの表示・非表示]をクリックすると、選択したセルの漢字の上にふりがなが表示されます。続いてもう一度クリックするとふりがなが消えます。[ふりがなの表示・非表示]アイコンの右のボタン(▼)をクリックすると、メニューが出て[ふりがなの表示][ふりがなの編集][ふりがなの設定]が選べます。
Excelでは、「ふりがな」と表示していますが、表示されるのは漢字を変換する前の入力した「かな」文字です。氏名や地名などで本当の「読み」と入力したときの「かな」と違うときは、[ふりがなの編集]で修正する必要があります。
例えばセルに地名の「谷根」(タンネ)をたにねとキーボードで入力して変換すると、ふりがなは「たに ね」になりますので、修正しなければなりません。
Excel の「ふりがな」は、漢字の入力されたセルに表示しますので、並べ替えのキーに使えないので新しい列を作りこの関数で「ふりがな」を表示させます。
書式:=PHONETIC( 範囲 )
引数の[範囲]には、漢字が入力されているセルを指定します。
複数のセルを指定するとセル範囲の左上のセルのふりがなを表示します。隣接しない複数のセルを指定した場合はエラーになり[#N/A]が表示されます。
並べ替えを予定する場合は、漢字の入力されているセルの隣に新しい列を作り、必ず同じ行のセルを指定します。
以上でセルI2の氏名の振り仮名がセルJ2んじひょうじされます。セルJ2を下のセルにオートフィルを使ってコピー&貼り付けをすれば、氏名全体の振り仮名を表示できます。列幅が足りないときは、列番号のJとKの間の縦線にマウスポインターをあて、左右の矢印に変わったところでダブルクリックすると、最も長いふりがなの幅に広がります。
PHONETIC関数のふりがなは、漢字を入力した時のキーボードで打った「かな」が表示されます。正しい読みと違うときは、漢字のふりがなを修正します。
PHONETIC関数を入力したセルで修正すると、文字列が入力されます。
修正した結果は、直ちに[ふりがな]列に反映されます。