表計算ソフトLibreOffice Calcによるデータ集計

表計算ソフトウェア(一般にスプレッドシ−ト (Spread Sheet)と呼ばれる)とは,帳票形式で数値や数式を配置すること により, 各種のデータ処理や集計作業を行うソフトウェアである.ここでは,LibreOfficeに含まれる表計算ソフトLibreOffice Calcを用いる.

LibreOffice Calcの起動と終了

LibreOffice Calcの起動

LibreOffice Calcを起動するには,LibreOfficeを起動した後,「表計算ドキュメント」をクリックするか,或いは,「ファイル」メニュー -> 新規作成 -> 表計算と選択する.

以下のような新規表計算ウィンドウが開かれる.ここに表示されている表枠をワ−クシ−ト或いはシートと呼ぶ.

一つの表計算文書には,複数の表(シート)を重ねて作成する事ができ,上記の例では3枚のシートが重なった状態になっている.

シート名をダブルクリックすればシート名を適宜変更できる.

LibreOffice Calcの終了

Calcを終了するには「ファイル」メニュ−から「終了」を選ぶ.

このとき,作業したワークシートの保存を忘れていると「ファイルの保存」に関するダイアログ表示が出るので,適切に判断して対応する.

ワークシートの作成

セル

ワークシート中の升目を「セル」と呼ぶ.マウス左ボタンで任意のセルをクリックするとそのセルが太線で囲まれる. これを「セルポインタ」と呼ぶ.セルポインタは矢印キ−でも動かすことが出来る. 横の列位置をアルファベット,縦の行位置をアラビア数字で示し,各セルの位置はこれらを使って, 例えばB列3行目のセルはB3と表す.

ここで例題として,「学習塾の成績集計処理」をアルバイトとして作業すると想定する.

練習

定期模擬テストの結果は以下の通りであった.LibreOffice Calcを起動して,このような表となるように各セルを埋めてみよ.

各セルに文字を入力するには,入力したいセルをクリックするとカ−ソルが現れるので,そこへ文字をタイプインする.日本語・英語の切り替えは通常のMacOS Xと同じだが,数値を入力する際には英数モードにて入力するようにしないと,正しく数値として認識されない.

数値入力は数字ををタイプした後,Enterキ−をタイプすればよい.また,Enterキ−をタイプすると,通常は直下のセルへカ−ソル位置が移動する. また,Enterキーの代わりにTabキーをタイプ するとカーソルは右隣のセルへ移動する.

セルの幅や高さを変えるにはセルの区切り線をマウスポインタでつまんでドラッグする. 一旦入力したデ−タを修正するには,修正したいセルにセルポインタを移動させると「数式入力ボックス」にセルの内容が表示されるので, このボックス中の式をクリックしてカーソルを出し,適当に修正してからEnterキ−を押すと修正入力される. また,セルを選択しておいてから「編集」メニュ−から「内容の削除」を選ぶ(又はDeleteキーを押す)とセル内容が削除される.

表計算文書の保存

作成したワ−クシ−トを保存するには,LibreOffice Writerでの操作と同様の操作を行う.

「ファイル」 メニュ−から「名前を付けて保存」を選択すると次のようなウィ ンドウが表示される.ここで表示されるウィンドウはWriterの場合と全く同様である.

保存したい文書のファイル名を入力す る. 拡張子は .ods が自動的に付けられる.尚,既にファイルを一度済みの場合,「保存」を選択するだけで,元のファイルに上書き保存(以前の内容は消去)される.

ファイル名を入力した後,Enterをタイプするか「保存」ボタンをクリックすれば,ワ−クシ−トはファイルに保存される.

ワ−クシ−トの読み込み

保存してあるワ−クシ−トを読み込むには,「ファイル」メニュ−から「開く...」を選ぶと表示されるウィンドウより,開きたいファイル名をクリックして選択した後に「開く」ボタンをクリックする.あるいは,Finder上から当該ファイルのアイコンをダブルクリックして開いても良い.

練習

作成した成績表をresult.odsという名前で保存し,一旦,LibreOffice Calcを終了せよ. 再度Calcを起動し,保存しておいたresult.odsを開くことができることを確認してみよ.

ワークシートの編集

また,各セルはまとめて別の場所へ移動させる事が出来る.

例えば,各科目の列並びを「国語,英語,数学」の順番に変更してみよう.まず,「数学」の列名Eをクリックして選択してから「挿入」メニューより「列」を選ぶと,その左側に新たに列が加わる.

次に「英語」のC列を選択して,その列をドラッグ&ドロップしてE列へ落とすことで列の順番を入れ替える事が出来る.

不要になった元のC列は,それを選択してから「ファイル」メニューより「セルの削除」を選択すれば取り去る事が出来る.

練習

各科目の並びを「国語,英語,数学」の順番にとなるように編集してみよ.

練習

各科目の並びを元の「英語,国語,数学」の順番に戻してみよ.

セルと計算式

セル番地

セルの位置を表すには絶対セル番地相対セル番地の2種類がある. 絶対セル番地は $ 列名 $ 行番号 と表現し,ワ−クシ−ト上のどの位置のセルであるかを表す.例えばD列3行目のセルの絶対セル番地は $D$3 と表す.

相対セル番地は計算式が入力されたセルの番地を基準として,その式が参照している(使っている)セルの相対的位置を表す. その表し方は,例えば基準となるセルの番地をD4とすると,そこより左に2つ離れたセルはB4,上に一つ離れたセルはD3というように表現される.

これらの違いは,セルを「コピー&ペースト」した時に明確になる.

ちなみに,セル座標とかセル参照と呼ぶ場合もある.

セル領域

複数のセルを囲む長方形領域をセル領域と呼び,左上角:右下角という表現をする. 例えばB3セルを左上角,E8セルを右下角とするセル領域は B3:E8 と表せる. また,1行や1列だけのセル領域は A2:F2C1:C6 というように表す.セル領域は,その左上セルから右下セルをドラッグすることで選択できる.

計算式の入力

セルに計算式を入力することで様々な集計計算ができる.計算式入力は文字入力と同様に行える. セルの内容が数値か文字列か計算式か何れであるのかは,先頭が = 記号で始まれば自動的に計算式であるとして判断される.または,パレット上の=ボタンをクリックしても良い.

演算記号はたし算 +,引き算 -,かけ算 *,割り算 /,べき乗 ^,カッコ ()などが使え,また,セルを参照したいときは式の入力途中で該当のセルをクリックすると, クリックされたセルの相対せル番地が式に入力される.上手に利用して入力間違いを防ぐように心がけて欲しい.

練習

計算式を入力して最初の受験生の合計点を求めてみよ. 次に,その求めた合計点のセルを利用して,最初の受験生(ドラエもん)の平均点を求めるようにしてみよ.

セル内容のコピ−&ペ−スト

セル内容(数値や計算式など)は,コピ−して他のセルに複写(ペ−スト)することができる. まず,コピ−したいセル領域を左ボタンでドラッグして選択しておき,「編集」メニュ−から「コピ−」を選ぶ. 次に,複写したいセル範囲をドラッグして「編集」メニュ−から「ペ−スト」を選ぶと内容が複写されて入力される. このとき,計算式に使われている相対セル番地は,それぞれ セル番地を適切にずらして修正した上で 入力されていることに注目すること.尚,$記号の付いた絶対セル番地は,ペーストされても修正されることはない.

練習

先に入力した合計点,及び,平均値を求める計算式が入ったセル領域を, 残りの受験生それぞれの合計点と平均値のセルにコピ−&ペ−ストしてみよ.

まず,最初の受験生の合計点セルと平均点セルをドラッグして一緒に選択し,「編集」メニューから「コピー」を選ぶ. 次に,残りの受験生の合計点セルと平均点セルを一気にドラッグして選択してから,「編集」メニューから「ペースト」を選ぶと, その選択しておいたセル領域にコピーしておいたセル領域の計算式が一気にコピーされるはずである.

セルの書式スタイルの変更

セルの書式スタイル(書体や文字揃えなど)を変更したい場合,まず該当のセルを選択する. 幾つかのセルをまとめて変更したい場合は,それらのセルを左ボタンでドラッグして領域選択する.一列まとめて全部変更したい場合は,

と表示されている列名部分を適当にドラッグして選択する.同様に行単位でも選択することができる.あるいは,セル領域単位で設定したければ当該セル領域を選択する.

次に,「書式」から「セル」を選ぶと次のウィンドウが表示される.

ここから設定したい書式状態を設定してから「OK」ボタンをクリックすれば,書式スタイルが変更される. 例えば貨幣記号を付けて「¥1,200」や「1,200円」という具合に表示させることも出来る.

練習

平均値を示す部分の書式スタイルを「少数点以下 1桁表示」スタイルに変更してみよ.

小数点以下桁数だけならパレット上のボタン操作でも出来る.

尚,スタイルを変更しても,表示が変るだけでセル内容の数値データが切り捨てられることは無い. 再度「Standard(標準)」に戻してみればわかるはずである.

また,セルの文字表示を右寄せや中央寄せに表示させるには,LibreOffice Writerでの段落スタイル設定と同様に行う.すなわち,まず変更したいセルを選択しておき, のボタンからから適切なものをクリックすれば良い.

練習

「受験番号」の表示位置を左寄せとし, 「氏名」の表示位置を中央寄せに変更してみよ.

関数による計算

受験生全員の合計点数を求めるとすると,C2+C3+C4+C5+C6+C7+C8+C9+C10+C11+C12+C13などといった非常に長い複雑な式になってしまう.このように複雑な計算式は入力間違いも起こりやすい. そこで,一般にスプレッドシ−トには幾つかの便利な集約関数(集計計算を行う)数学関数(sin, cos, tanなど)などが用意されており,例えば,セル領域中の各セル内容の合計を求める関数としてsum()といったものが使える.

練習

英語のクラス合計を求めてみよ.数式としてsum(セル領域)を用いよ.

関数ウイザードの利用

また,その他の集約関数として,平均値average(), 最大値 max(), 最小値min()といったものがある. 使用可能な関数を覚えていない場合,関数を入力したい部分にて,「挿入」メニューから「関数...」を選択すると「関数ウィザード」が表示される.

この中から必要なものを選び,「次へ」ボタンをクリックして必要な項目を指示してゆけば簡単に関数が入力できる.関数があまりに多すぎて見辛い場合は,「分類項目」から「統計」など適当に選択すれば,関係する関数だけに絞って表示出来る.

練習

セル内容をコピ−&ペ−ストして,各科目の平均点やクラス合計と平均点など全て求めて集計表を完成させよ. 尚,セルの内容が修正された時は自動的に計算し直す(再計算)ようになっている.各受験生の成績デ−タを各自の好きなように様々に変更してみて,再計算が起きていることを確認せよ.

練習

少々風変わりな関数としては,再計算した時点の日付時刻を返すnow()という関数がある. 各項目欄の「個人平均」のの右隣のセルにこの関数を入力して日付が表示されるようにしてみよ.また,セルの表示幅が足りないときは「###」といった表示になってしまう. その場合は,セル幅を広げてやると正しく表示される.

ここで,完成したワ−クシ−トは忘れずに保存しておくこと.

作業が一区切りついたら保存操作というのは,予期しないソフトウエアのハングアップなどの対策として有効だ.

データの並べ替え

各種データを集計する場合,データを並べ替えしておくと集計結果が分かりやすい事がある.これを「データのソート」などと呼ぶ.

作成したワークシートの各受験生データを「英語点数の低い順」に並べ替えしてみよう.まず,並べ替えしたい行を行番号の部分をドラッグして選択し,「データ」メニューから「並べ替え」を選択する.

「並べ替え」ダイアログにて並べ替えしたい列名を「並べ替えキー1」から選択する.ここでの例では英語点数の「列C」を選ぶ.

この時,右側の「昇順」「降順」とは,その列が数値情報の場合,それぞれ,「小さい方から大きいほう」「大きいほうから小さい方」を意味する.(文字情報の場合は,アルファベット順やアイウエオ順で考える.)OKボタンをクリックすると並べ替え処理が施される.

並べ替えキーを指定するとき,列Cとか列Bなど列名で指示するのはウッカリ指示間違いしやすい.集計表の最上列にはデータ項目名(列ラベル)が作られている事が多いので,これを含めて選択することで,少し分かりやすくする事が出来る.

集計表を受験番号順に並べ替えてみよう.列ラベルを含めて並べ替えしたい範囲行を選択して,

データ -> 並べかえを選択する.

ここで表示されるダイアログの「オプション」タブをクリックする.

ここから「対象範囲は列ラベルを含む」にチェックを入れてから「並べ替え条件」タブをクリックする.

今度は並べ替えキーのメニューに列ラベルの各項目が表示されているだろう.少し分かりやすくなったはずだ.ここから「受験番号」を選択してOKボタンをクリックして受験番号順にデータを並べ替えよう.

尚,並べ替えキーは複数指定できるが,これは例えば「まず,英語点数の高い順番で並べて,もし英語が同点の場合には国語の点数の低い方を上にする」といった場合,並べ替えキー1に英語,並べ替えキーに国語の列を指示すれば良い.また,この並べ替え操作は「個人合計」など計算結果となる列であっても問題なく行える.

グラフの作成

集計した結果は,グラフ化することができる.ここでは,練習として各受験生の成績を縦棒積算グラフとして表示させてみる. グラフを描画するには,グラフにしたいセル領域をまず選択する.

次に,「挿入」メニューから「グラフ」を選択する.すると,次のような「グラフウイザード」のダイアログが表示される.

まず,描画したいグラフの種類を訪ねてくる.ここでは「縦棒」を選択して,さらに「積み上げ」をクリックすれば「縦棒積み上げグラフ」が描画できる.

「次へ」ボタンをクリックすると,グラフとして描画するデータ範囲の確認を求めてくる.

ここでは,選択したセル領域の最初の行と最初の列をグラフ中の項目名として使用するので,それぞれ該当するチェックボックスがチェックされてレ印が付いている事を確認して,「次へ」ボタンをクリックする.

ここでは,グラフ化する各データ系列(英語,国語,数学)について,データ範囲を細かく設定できるが,ここでは「次へ」をクリックして先へ進む.

最後にグラフのタイトルに「定期試験成績表」と入力して「完了」をクリックすると積み上げ棒グラフが表示される.

グラフはドラッグ操作にて表示位置や大きさを調整できる.

また,このグラフはワークシートの再計算に応じて自動的に修正される.「のび太」の英語の点数を10点,数学の点数を15点に修正してみて, それが合計値や平均値を表すセルの内容やグラフの状態に反映されることを確認せよ.

罫線の設定

日本には,どうしても表には罫線(枠線)を付けないと気が済まないような文化・風習がある.Calcにも罫線を付ける機能が備わっている.

罫線を付けるには,まず付けたい範囲を選択してから,ツールパレット上の「外枠」メニューから選択する.

もっと枠線状態を細かく指定したい場合は,セル範囲を選択してから,「書式」メニューの「セル」を選択し,ダイアログの「外枠」タブにて,きめ細かく調整する事も出来る.

「ユーザ定義」に示されている枠線の引き方を示す箇所にて,各線をクリックすることで,既に引かれている罫線を消去する事も出来る.色々試してみよう.

尚,セルの書式設定にはセルの文字色や背景色といった設定もある.ツールパレット上のボタンで設定できるし,セルの「書式」として設定する事も出来る.

練習

以下のような罫線や背景色となるように書式設定してみよ.

ワークシートの印刷

印刷プレビュー

作成したワークシートを印刷するのはLibreOffice Writerでのプリントアウト操作と同様であるが、ワークシートの大きさによっては用紙からはみ出て印刷されたりすることも多い.印刷用紙を無駄にしないために,予め印刷プレビュー機能を用いて適切に印刷されるかどうか画面上で確認しよう.

印刷プレビューを行う前に,ページ書式を設定しておこう.「書式」メニューから「ページ」を選択し,表示されるページスタイルダイアログの「ページ」タブをクリックする.

ここで,印刷したい用紙のサイズや向き(縦向き・横向き)を選択する.ここでは例として「A4サイズ・横向き」としてみよう.設定したらOKボタンをクリックする.

次に,「ファイル」メニューから「印刷プレビュー」を選択すると画面がプレビュー表示に切り替わり,どのような雰囲気で印刷されるのか画面上で確認する事が出来る.

プレビュー結果が納得できれば,「印刷プレビューを閉じる」ボタンをクリックして元の表示に戻る.

ワークシートの印刷

印刷プレビューにより印刷具合を確認したら印刷してみよう.尚,印刷操作前には保存操作をしておく事を強く勧める.

印刷するには,まず,「ファイル」メニューから「プリンタの設定」を選択する.

プリンタ設定ダイアログが表示されるので,印刷に使いたいプリンタを選択してOKボタンをクリックする.

ここで「オプション」ボタンをクリックすると若干の追加オプション設定もあるが,通常は特に設定しなくても大丈夫だろう.

プリンタ設定を行った後,「ファイル」メニューから「印刷」を選択するとWriterの場合と同様のダイアログが表示される.

特定ページのみ印刷など印刷条件を細かく設定したければ,「詳細を表示」ボタンをクリックする.

ここで,印刷に利用するプリンタが正しく選択されている事を確認した上で,OKボタンをクリックすると印刷が開始される.

注意: 現時点のLibreOffice 4.0.Xはプリントボタンが押されて印刷処理を開始するところに不具合を抱えているようで,そのままハングアップ状態になってしまう事が時々あるようだ.その際には,一旦,LibreOfficeを強制終了させて,再度LibreOfficeを立ち上げてから印刷操作をやり直すこと.

アプリケーションがハングアップしたときの強制終了操作については,以下のページを参照してください.

操作不能になった場合への対処

尚LibreOfficeを再立ち上げしたときに「ドキュメントの回復」を行ってくれるはずだ.その回復が効果的になるためにも,印刷開始前に予め保存操作をしておくべきだろう.

提出課題

上記で練習した「模擬試験成績集計」に関するグラフ入りワークシートを完成させ,どこか空いているセルに各自の学生証番号と氏名を入力せよ.

印刷プレビューにて,学生証番号と氏名が表示されていることを確認してから,ワークシートをプリントアウトして提出すること.

締め切り: 教員の指示による

基礎プログラミング演習Iの表紙ページへ戻る