Chapter 14, Calc as a Database ノート

Introduction

LibreOffice Base 部品は包括的な RDB 機能を有している一方、Calc は単純かつ有能なデータベースのような土台として機能するのに十分な機能を有する。

当初は Calc スプレッドシートでデータを管理し、その後より包括的なデータベースシステムを使用することを決めた場合、Calc データを Base へ移行することは容易だ。反対に、Calc の機能の一部を利用してデータの分析や表現を行いたい Base 利用者にとっては、Calc 文書からリンクされたデータ範囲の作成、ピボット表分析、統計図表の原データとして Base を使用することが可能だ。

A database primer

一般的なデータベースでは、関連するデータは表に整理され、スプレッドシートに似た格子状の行と列に配列される。

  • 表の各行はデータ登録項目を表す。

  • 表の各列は各登録項目内のフィールドを表す。

    • フィールドの各升目は名前などといった個々のデータ項目または属性を含む。

    • 各登録項目は人などといった単一の実体に対応する関連属性で構成される。

表一つに対するフィールド数は一定であることが多いが、登録項目数は決まっていない。

表には何百、何千という行があるかもしれないが、指定された条件を満たす登録項目を検索する情報要求を使って、個々の登録項目を簡単に見出し、検索し、更新することが可能だ。スプレッドシートに対する利点の一つはこの検索性の良さにある。

読者ノート

情報要求というのは SQL でいう SELECT 文を指す。

このデータベース表の概念を説明するために、授業の成績表を例にとって考える。このシートでは、各行がクラスを受講している学生を表し、各列に学生の名前と成績が記載されいる。この表を使用すると、名前を検索するだけで個別の学生の成績をすばやく調べることができる。また、平均点が不合格の登録項目を絞り込むことで、どの学生がクラスに合格しているかを判断することができる。

Grading sheet example

Student

HW #1

HW #2

HW #3

Quiz #1

Quiz #2

Test #1

Average

Andrew

90

100

82

90

88

92

90.33

Bethany

95

100

82

80

88

93

89.67

Charles

80

93

73

80

75

84

80.83

David

75

86

91

40

88

79

76.50

Emily

100

100

81

100

75

94

91.67

Ferdinand

85

93

73

60

50

72

72.17

Georgia

70

80

55

39

75

67

64.33

Haley

85

93

82

70

75

76

80.17

Ian

100

100

91

90

100

96

96.17

Jennifer

85

93

73

80

100

90

86.83

読者ノート

Average フィールドはもちろん AVERAGE 関数の値だ。

LibreOffice Base は完全な機能を備えた RDB システムだが、Calc は RDB モデルに対応していない。

Calc as a database-like program

  • Calc は平坦な非 RDB 表に似ている。DB 表をシートに含めることも可能だ。

  • 幅広いコマンドや関数を用いてデータを深く分析することが可能だ。

    • 並び替え

    • 絞り込み

    • ピボット

    • 2D/3D 図式などによる視覚的表現

Calc は完全な機能を備えたデータベースアプリケーションに取って代わるものではないが、小規模な個人的あるいは業務上のさまざまな場面でデータを管理するのに役立つ。

Associating a range with a name

シート内にデータベース表を仕込むには、その表が占める領域が要る。その領域とは、一つ以上の升目が連続するグループであるような範囲により表される。表に対する範囲に出入りしやすくするために、範囲に意味のある名前を与えることが可能だ。

名前の利点を挙げると：

  • 範囲を特定しやすくなる。特に文書内で複数の範囲を扱う場合は。

  • 名前付き範囲は、単にアドレスで参照するのではなく、名前で参照することが可能。例えば、Scores という名前の範囲があれば、升目内で =SUM(Scores) のような数式で参照することができる。

  • 名前付き範囲への名前による参照が範囲のアドレスが変更されるたびに自動的に更新される。これにより、範囲の位置が変更されるたびに参照を変更する必要がなくなる。

  • 名前付き範囲はすべて Navigator (F5) から閲覧可能かつ入手可能。

Calc には二種類の名前付き範囲が存在する：

データベース範囲

データベースのような操作の設定を保存する名前付き範囲

標準的範囲

データベース範囲でない名前付き範囲

利用者ノート

この分類はどういうことだ？

Named ranges

技術的には、名前付き範囲は名前付き数式であり、その内容は文字列として設定される。よく使われる式は、

$Sheet1.$A$1:$E$15

のような絶対升目範囲だ。しかし、他の式型も可能だ。例えば、

$Sheet1.$A$1:$A$4~$Sheet1.$B$1:$B$4

という式は、二つの別々の升目範囲を含む（チルダ文字は連結演算子）。あるいは、半径を指定して円の面積を計算するために、

PI()*B1*B1

のような数式を定義することもできる。

以下、単一の行列のような（連結成分が一つしかない）セル範囲として定義された名前付き範囲について述べられる。

新しい名前付き範囲を作成する簡単な方法は：

  1. シートで関連する升目を選択

  2. Formula バー の左にある Name Box 欄に名前を入力

  3. 入力中に表示される Define Name for Range ツールチップに注目し、入力が終わったら Enter を押す

Define Name ダイアログボックスを使う方法もある。次のどちらかで開く：

  • Menu バー Sheet‣Named Ranges and Expressions‣Define… 実行

  • Manage Names ダイアログボックス で Add… を押す

Manage Names ダイアログボックス の開き方は：

  • Ctrl + F3 を押す

  • Formula バー の左にある Name Box ドロップダウンリストで Manage Names… を実行

Paste Names ダイアログボックスを使えば、定義済み名前付き範囲を入力するのが楽になる。開き方は次のどちらかを実行：

  • Menu バー Insert‣Named Ranges or Expressions…

  • Menu バー Sheet‣Named Ranges and Expressions‣Insert…

項目を選択して Paste ボタンを押せば、現在のキャレット位置に選択した名前付き範囲が貼り付く。

範囲の作成と編集について詳しくは Chapter 7, Printing, Exporting, Emailing, and Signing, Chapter 8, Using Formulas and Functions を参照しろ。

Creating named ranges using row or column headers

Create Names ツールでは、表のヘッダーから複数の名前付き範囲を同時に作成できる。これらのヘッダーは表の境界（上下の行、左右の列）から描画することができ、ヘッダーに対応する各行または各列は、名前付き範囲自体の作成に使用される。例えば、表のいちばん上の行に含まれるヘッダーから範囲を作成することを選択した場合、各範囲は各ヘッダーラベルに対応する列から個別に生成される。

Note

ヘッダー升目は Create Names ツールを使って生成された名前付き範囲には含まれない。これらの各升目のラベルが範囲の名前付けに使われるからだ。

Create Names ツールを使う手順：

  1. シートにて名前付き範囲を作成する表を選択する。ヘッダーの行や列も選択に含めろ。

  2. Menu バー Sheet‣Named Ranges and Expressions‣Create… を選択して Create Names ダイアログボックスを開く。

  3. Calc はどの行または列にヘッダーが含まれているかを自動的に識別し、該当するチェックボックス（最上行、左列、最下行、右列）にマークを付ける。この選択を変更したい場合は、この時点でいずれかのボックスを手動でオンオフ可能。

  4. OK ボタン を押してダイアログボックスを閉じて新しい名前付き範囲を作成する。

Tip

この挙動があるので、複数の行や列に同じラベルを付けてはいけない。

Database ranges

データベース範囲はデータベースの表のように使用することを意図して設計されている。各行は登録項目を表し、各セルは登録項目内のフィールドを表す。データベース範囲は名前付き範囲と以下の点で異なる：

  • データベース範囲は数式ではあり得ない。

  • データベース範囲は単一の矩形セル範囲でしかあり得ない。

  • データベース範囲では最初の行と最後の行を見出しと小計用途としてそれぞれ書式を有する。表のフィールドそれぞれに対して、セルの書式を保持することもできる。

  • データベース範囲はシート内のアドレスからの相対参照不可。

  • データベース範囲は、並び替え、絞り込み、小計、データインポートの設定を記述子と呼ばれるデータ構造に格納し、マクロを使用してアクセスすることが可能（データベース操作が実行されると記述子は更新される）。

  • データベース範囲は外部データに接続することが可能（そこからデータをスプレッドシートに取り込む）。

データベース範囲の定義には Define Database Range ダイアログボックスを使う。

データベース範囲を作成する手順：

  1. （全範囲を自動決定させる場合）データベース表のセル領域内の升目を一つ選択する

  2. Menu バー Data‣Define Range… 実行

  3. Name 欄に範囲名を入力する（変な字は使うな）

  4. 必要なら Options を展開してオプションを指定する

    Contains column labels

    最上段をフィールドの見出し用に確保するかどうかを示す。

    Contains totals row

    一番下の行を合計用に確保するかどうかを示す。

    Insert or delete cells

    このオプションが有効な場合、給源に新しい登録項目が追加されると、データベース範囲に新しい行と列が入る。外部データベース給源が範囲にリンクされている場合のみ関係がある。手動でデータベース範囲を更新するには、Menu バー の Data‣Refresh Range を使用する。

    Keep formatting

    最初のデータ行の既存の升目書式をデータベース範囲全体に適用する。

    Don’t save imported data

    このオプションを選択すると、原データベースへの参照のみが保存され、範囲の升目の内容は維持されない。

    Source

    現在のデータベース給源が存在する場合は、その情報を表示する。

    Operations

    データベース範囲にどのような操作が適用されたかを示す。例えば Sort, Filter, Subtotals など。

  5. Add を押してデータベース範囲一覧に範囲を追加する

  6. OK ボタン

既存データベース範囲を変更する手順：

  1. Menu バー Data‣Define Range… を選択。

  2. 範囲一覧から項目を一つ選ぶ（または名前を直接指定）とボタンのラベルが Modify に変化する。

  3. ダイアログボックス内 Range や Options で変更を加える。

  4. Modify を押して範囲を更新する。

  5. OK ボタン を押してダイアログボックスを閉じ、変更したデータベース範囲を保存する。

既存データベース範囲を削除する手順：

  1. Menu バー Data‣Define Range… 実行

  2. 範囲一覧から項目を一つ選ぶ（または名前を直接指定）

  3. Delete ボタンを押す

  4. 確認に対して Yes ボタン を押す

  5. OK ボタン を押してダイアログボックスを閉じる

既存データベース範囲を選択する手順：

  1. Menu バー Data‣Select Range… 実行

  2. 一覧から項目を選択

  3. OK ボタン を押す

データ源からデータを取得して新規データベース範囲を作成する手順：

  1. Data Sources Explorer を開く

  2. 左窓で目的データ源ツリーを展開

  3. 必要な表または問い合わせをクリックし、その構成データを右窓に表示する

  4. 右窓の左上にある空白の矩形領域をクリックし、表示されている表または質問内の全データを選択する。

  5. データの左上隅になるスプレッドシート升目にデータをドラッグ＆ドロップ

これでインポートされたデータの升目範囲を包含し、Import1, Import2 などの形式の既定の名前を持つ新規データベース範囲が自動的に生成する。

Menu バー Data‣Refresh Range を選択すると、関連するデータ源のデータが更新されればデータベース範囲の内容が更新される。シートのデータは外部データベースのデータと一致するように更新される。リンク方法については Chapter 11, Linking Data を参照しろ。

Sorting

Sorting is the process of rearranging data in a range or a sheet according to a specified sort order.

単一列の値に基づいてデータベース表を並び替えるもっとも単純な方法は、次のように Sort Ascending と Sort Descending ツールを使うものだ：

  1. 列のセルをどれでもよいので選択

  2. 昇順に並び替えるならば次のいずれかを実行（降順の場合は対応する UI を使用）：

    • Data‣Sort Ascending を実行

    • Standard ツールバー Sort Ascending 図像をクリック

    • AutoFilters を有効にしている場合は、関連列の AutoFilter コンボボックスで昇順並び替えを選択してもよい

これらの並べ替えコマンドは自動的に表が占めるセル範囲すべてを識別し、指示された列の値のみに基づいて表全体を並べ替える。ただし、ヘッダー行はそれと認識され、並べ替えの対象から外れる。

AutoFilter コンボボックスは次節で述べられるが、Sort Ascending, Sort Descending に加え、升目の背景色やフォント色で並び替える Sort by Color 項目もある。

複雑な並び替えを実現するには、Menu バー Data‣Sort… を実行して Sort ダイアログボックス を開く。開く前に表内の升目を一つ選択しておけ。

Sort Criteria タブ では並び替えを三段階指定することが可能。Sort Key キー番号の小さいものから大きいのものへ順次並び替えられる。

Options タブ にはさらなる並び替えオプションが用意されている。これらについては Chapter 2, Entering and Editing Data を参照しろ。

Filtering

A filter is a tool that hides or displays records within a sheet based on a set of filtering criteria.

絞り込みは長いデータ一覧から特定の項目を発見するのに便利だ。三種類の絞り込みがある：

  • Data‣AutoFilter

  • Data‣More Filters‣Standard Filter…

  • Data‣More Filters‣Advanced Filter…

データベース表に適用されている絞り込みを解除したい場合には Data‣More Filters‣Reset Filter を実行。

Chapter 2, Entering and Editing Data も参照しろ。

AutoFilter

  • もっとも簡単な絞り込み

  • データ列の上部にある▼ボタンからコンボボックスにアクセス

データベース表の列すべてに AutoFilters を追加するには、

  1. 表内のセルをどれでもいいからクリック

  2. 次のいずれかを実行：

    • Data‣AutoFilter

    • Standard ツールバー AutoFilter 図像クリック

    • Ctrl + Shift + L 押し

AutoFilter 全解除手順は次のいずれかを実行：

  • Data‣AutoFilter

  • Data‣More Filters‣Hide AutoFilter

  • Standard ツールバー AutoFilter 図像クリック

  • Ctrl + Shift + L 押し

コンボボックスのオプション：

  • 昇順か降順で並び替え

  • 背景色か文字色で並び替え

  • 背景色か文字色で絞り込み

  • Filter by Condition‣ 以下にある絞り込み項目

    • Empty

    • Not Empty

    • Top 10

    • Bottom 10

  • チェックボックス各種は値の絞り込みに対応

  • 列に絞り込みが適用中の場合、Clear Filter で解除

AutoFilter のコンボボックスとそのオプションの使い方の詳細については Chapter 2, Entering and Editing Data を見ろ。

Standard filters

  • 標準絞り込みは AutoFilters よりも複雑で、最大八つの絞り込み条件を設定可能。

  • 強力絞り込みは正規表現により設定可能。

  • 標準絞り込みはダイアログボックスを使用する。開き方は：

    • Menu バー から Data‣More Filters‣Standard Filter…

    • AutoFilter コンボボックスの Standard Filter… オプション

Chapter 2, Entering and Editing Data 参照。

Advanced filters

高度な絞り込みの判定基準は、ダイアログボックスに入力するのではなく、シートに保存される。そのため、使う前にまず絞り込み基準を含むセル範囲を設定する必要がある。

判定基準範囲を設定する手順：

  1. 絞り込む範囲の列見出しをシートの空き地にコピー（別シートでかまわない）

  2. 判定基準範囲列見出しの下に絞り込み判定を入力する。

    • 同じ行の判定は AND で接続される

    • 各行の判定グループは OR で接続される

    • 空セルは無視される

    • 絞り込み一つあたり最大八行定義可能

Tip

基準領域には定義された絞り込み基準を持つ列の見出しだけを含めることも可能だが、簡単のために、データベース表の見出しすべてを基準領域にコピーすることも可能だ。

ここまでやって高度な絞り込みを設定することになる：

  1. 絞り込み対象セル範囲を選択（データベース表ならばセル一つでいい）。

  2. Menu バー から Data‣More Filters‣Advanced Filter… を選択して Advanced Filter ダイアログボックスを開く。

  3. Read Filter Criteria From 欄にドロップダウンから名前付き範囲を選択するか、参照を入力するか、シートから升目を選択して、基準範囲のアドレスを入力する。

  4. OK ボタン を押して絞り込みを適用し、ダイアログボックスを閉じる。

Note

個々の名前付き範囲に対して、Define Name ダイアログボックス と Manage Names ダイアログボックス で Filter にチェックを入れることができる。この方法で絞り込みのためにマークされた名前付き範囲しか Advanced Filter ダイアログボックスの Read Filter Criteria From 欄のドロップダウンボックスで選択できない。データベースの範囲はドロップダウンボックスで選択できない。

高度なオプションは標準オプションと同じであり、Chapter 2, Entering and Editing Data でさらに詳しく説明されている。

例を再現するには次の内容を別のシートに用意しておく：

Advanced filter criteria range

Student

HW #1

HW #2

HW #3

Quiz #1

Quiz #2

Test #1

Average

>75

>75

>75

Ferdinand

読者ノート

前掲のデータベース表を選択状態にして Advanced Filter を実行する。それからこの表をドロップダウンボックスに指定する。その結果、元のデータベース表の行が絞り込まれて、Ferdinand 自身の行と、それよりも成績の良い行だけに絞られる。

Useful database-like functions

Database category functions

Overview

Database 区分にある関数 12 個はスプレッドシート内の矩形領域を占める単純なデータベースを分析するのを助けることを目的としており、データは各登録項目ごとに一行として整理されている。各列のヘッダーセルには列の名前が表示され、通常、その列の各セルの内容を示す。

Database 区分の関数は次の三つの引数を取る：

Database

データベースのセル範囲

DatabaseField

関数の計算に用いるデータを含む列

SearchCriteria

検索条件を含む別領域セル範囲

データベース区分の関数すべては同じ単純な操作の考え方を持つ。まず、指定された SearchCriteria を使用して、後続の計算で使用するデータベース内の登録項目の部分集合を識別する。それからデータ値を抽出し、特定の関数（平均、合計、積など）に関連付けられた計算を実行する。処理されるのは選択された登録項目の DatabaseField 列の値だ。

Database function arguments

Database

  • 範囲の最初の行はフィールド名からなり、それ以降の行は対応するフィールド値を持つ登録項目だ。

  • 升目範囲定義としては、矩形の左上と右下のセル参照をコロンで結合する。A1:E10 のように。

  • 名前付き範囲またはデータベース範囲名を指定してもよい。こちらのほうが可読性と保守性を向上する。

DatabaseField

  • 検索条件が適用されデータ行が選択された後、関数が計算に使用する列を指定する。

  • この引数を指定する方法はいろいろある。

    • データベース領域内のヘッダー升目への参照あるいは（有効な）名前を入力する。

    • データベース領域内の列を 1 から開始する（有効な）番号で指定する。

    • データベース範囲の最初の行から見出し名をリテラル文字列で指定する。

  • DCOUNT, DCOUNTA 以外の Database 関数ではこの引数は入力必須だ。

SearchCriteria

  • 検索条件を含む升目範囲を指定する。

  • Database 引数と同様に、最初の行もフィールド名。それ以降の行は関連フィールドについての条件だ。

  • Database 領域と SearchCriteria 領域は隣接している必要はない。同一シート内にある必要もない。

  • セル範囲定義方法は Database 引数と同様。

Defining search criteria

  • SearchCriteria 領域が占める列数は Database 領域の幅に一致する必要はない。

  • SearchCriteria の最初の行に現れる見出しすべては Database のそれと一致する必要がある。

  • 条件式は SearchCriteria 領域の二行目以降のセルに入力される。

  • 比較演算子を用いて SearchCriteria 領域セルに条件を作成する。セルが空でなく、比較演算子で始まらないものは = とみなされる。

一行に複数の条件を書くと、それらは AND で結ばれる。別々の行に書くと OR で結ばれる。

Options ダイアログボックス LibreOffice Calc‣Calculate ページ の Enable wildcards in formulas が選択されていれば、ワイルドカードを使用して SearchCriteria を作成できる。スプレッドシートで Microsoft Excel との相互運用性が重要な場合はこれを選択する必要がある。

Enable regular expressions in formulas が選択されていれば、正規表現を使用してさらに強力な条件を作成できる。

Tip

検索基準文字列が正規表現である関数を使用する場合、最初の試みは基準文字列を数値に変換することだ。例えば、.0 は 0.0 に変換される。成功すれば、正規表現マッチではなく数値マッチとなる。しかし、小数の区切り文字が . でないロケールに切り替えると、正規表現の変換が働くようになる。数値表現ではなく正規表現として強制的に評価するには、.[0] や .\0, (?i).0 など、数値として誤読されない表現を使用する。

Search criteria = and <> must apply to whole cells オプションはデータベース関数に設定する検索条件が升目全体に正確に一致するかどうかを制御する。スプレッドシートで Microsoft Excel との相互運用性が重要な場合はオンにする。

Example of Database function use

本書のスクリーンショットに倣え。

Example usage of a Database function (database table)

Name

Grade

Age

Distance (meters)

Weight (kg)

Andy

3

9

150

40

Betty

4

10

1000

42

Charles

3

10

300

51

Daniel

5

11

1200

48

Eva

2

8

650

33

Frank

2

7

300

42

Greta

1

7

200

36

Harry

3

9

1200

44

Irene

2

8

1000

42
Example usage of a Database function (criteria table)

Name

Grade

Age

Distance (meters)

Weight (kg)

> 600

データベース区分の関数の使い方を示す簡単な例だ。選択された升目 E15 の数式は Formula バー で見ることができ、DCOUNT 関数の呼び出しで構成されてる。この関数呼び出しの引数は：

Database 引数

この例で使用するデータベース表はヘッダー行から Irene の行まで。

DatabaseField 引数

DCOUNT 関数はさらに計算することなく条件に一致する登録項目を数えるので、この引数に値を指定する必要はないが、ないことを示すために引数リストの区切り文字は指定しないといけない。

SearchCriteria 引数

この例で使用される検索条件領域は A12:E13 に及ぶ。D13 の条件 >600 は DCOUNT に Distance (meters) 列に 600 m より大きい値を持つすべての登録項目を数えさせる。多くの場合、検索条件領域内にデータベース表の列見出しを複製すると便利だ。しかし、これは必須ではなく、式 =DCOUNT(A1:E10,,D12:D13) は全く同じ値 5 を与える。

Help で database functions を検索するか、 <https://wiki.documentfoundation.org/Documentation/Calc_Functions> の Calc Functions Wiki 内の各関数の関連ページにアクセスすると多くの例が見つかる。

List of Database functions

Note

これらの関数は計算中、日付や論理値を数値として扱う。

DAVERAGE

指定された検索条件に一致するすべての行について、指定された列のセル（フィールド）の数値の平均を計算する。

DCOUNT, DCOUNTA

指定された検索条件に一致するすべての行に対して、指定された列のうち

  • 数値を含むセルの数を返す。

  • 空でないセルの数を返す。

列が指定されていない場合、内容に関係なく、指定された検索条件に一致するすべての登録項目の数を返す。

DGET

指定された検索条件に一致する唯一の行に対して、指定された列のセル内容を返す。

DMAX, DMIN

指定された検索条件に一致するすべての行について、数値を含む指定された列のセル全体の最大値、最小値を計算する。

  • 空白セルや数値以外の文字を含むセルは含まれない。

  • 一致する登録項目が見つからない場合など、変な場合には 0 を返す。

DPRODUCT

すべての数値の積を返す。

DSTDEV, DSTDEVP

指定された検索条件に一致するすべての行について、指定列のセル内の数値に基づいて標本標準偏差、母集団標準偏差を計算する。数値以外の値は無視。

DSUM

すべての数値の和を返す。

DVAR, DVARP

標本分散、母集団分散。

Other database-like functions

Calc に用意されている関数の中には、表形式のデータで使用することを意図したもの (e.g. HLOOKUP, VLOOKUP) もあれば、どのような状況でも使用できるものもある。

本書の表はデータベースに Calc の表を使用する場合に役立つ関数の一覧だ。その多くは、データベース以外の状況で使用される典型的なスプレッドシート関数としておなじみのものだが、中にはデータベース表テーブルで特に役立つものもある。

Todo

Table 1: Some useful database-like functions の実習を考える。