Calc Guide Chapter 8, Using Formulas and Functions ノート¶
Introduction¶
数式は数とテキストを評価するセルデータと考えられる。
関数とはセルに入る定義済みの計算のことだと考えられる。引数を入れるだけで自動的に計算が行われる。関数を使って、求める結果を得るために必要な数式を作成する。
Setting up a spreadsheet¶
数式に定数を入力するのは避けろ。
何をするシートであるのかを記述する文書、メモ、コメントを含める。
数式が意図どおりに働くことを確認するために、エラーチェックを仕込む。
The trap of fixed values¶
例えば一つのセルに為替レートと金額を乗じる数式を含めるとして、為替レートを生の数値で表現するのではなく、乗算式が為替レート単品を含むセルを参照するように書くほうが良い。
為替レートが変化した場合、計算式の編集が不要になる。
複雑な計算式を管理しやすい部分に分割することで、失敗を最小限に抑える。
Lack of documentation¶
文書化がないと、当初の目的や方法論が不明確になり、解読が困難になりがちだ。そうなると最初からやり直す方が簡単で、以前の作業が無駄になる。セルにコメントを挿入し、ラベルや見出しを使用すれば、スプレッドシートは後で簡単に修正でき、時間と労力を節約できる。
Error-checking formulas¶
スプレッドシートにチェックを設けると便利だ。
Options ダイアログボックス ページ Formula indicator and hint をオンにしろ。これにより、数式を含むセルの左下に小三角形が描かれ、ツールチップには数式が示される。数式バー非表示派の利用者の使い勝手が向上する。
Creating formulas¶
数式を入力する方法：
セルに対して直接入力
数式は記号
=で始める必要がある数式が前置単項演算子から始まる場合、等号は自動的に追加される
そうしないとテキスト扱いになる
Function Wizard を利用
実行
Ctrl + F2 押し
Sidebar Functions 甲板 を利用 (Alt + 5)
Operators in formulas¶
シートの各升目はデータ置場または計算場所として使用可能。データを入力するには、升目に単に入力し、次のセルに移動するか、Enter を押す。数式では、等号 = はその升目が計算に使用されることを示す。
左の計算（読者注※ =15+46 の例）では升目を一つしか使っていないが、データを升目に入れ、その升目を参照して計算を行っている右の計算（※： =B3+B4 の例）が真の力を示している。この場合、升目 B3 と B4 がデータ置場で、B5 が計算を実行する升目だ。計算式の加算記号は升目 B3 と B4 の内容の和が数式を持つ升目に入ることを示している。数式すべてがこの概念に基づいている。その他の数式の使い方を次に示す：
数式 |
意味 |
|---|---|
|
升目 A1 に 10 を加えた内容を表示する。 |
|
升目 A1 の内容の 16% を表示する。 |
|
升目 A1 と A2 の内容の積を表示する。 |
|
升目 A1 の内容を小数点第一位で四捨五入して表示する。 |
|
年 5% の名目金利を年 12 回支払う場合の実効金利を計算する。 |
|
升目 B8 から升目 B10 から B14 の和を差し引いた値を計算する。 |
|
升目 B10 から B14 の和を計算し、その値を B8 に加える。 |
|
B 列のすべての数値を合計する。 |
|
BloodSugar という名前で定義された名前付き範囲の平均を表示する。例えば BloodSugar は B3:B10 のような範囲を表す。 |
|
論理関数は、IF 文に代表されるように、特定された升目のデータに基づいて条件付き応答を行うこともできる。この例では C31 の内容が 140 より大きければ HIGH と表示され、そうでなければ OK と表示される。 |
Note
この章で説明する数式の作成以外の目的で等号 = を入力する場合は、この記号の前にアポストロフィーまたはシングルクォーテーションを入れろ。Calc はシングルクォーテーション以降をすべてテキストとして扱う。
Operator types¶
Calc では演算子は算術演算子、比較演算子、テキスト演算子、参照演算子に分類されているようだ。
Arithmetic operators¶
演算子
%は後置単項演算子として働く（意味は百分率）べき乗のための演算子はキャレット記号
^を用いる。古の Basic 由来？
Comparative operators¶
比較演算の結果は
TRUEまたはFALSEを返すと考えてよい不等式は
<>を用いる
Text operators¶
二項演算子
&は文字列連結演算
テキストはスプレッドシート上のさまざまな場所にある断片を連結することができる。関数``CONCATENATE`` を用いても連結可能だ。
Reference operators¶
セルの識別方法ついて述べられている：
An individual cell is identified by the column identifier (letter) located along the top of the columns and a row identifier (number) found along the left-hand side of the spreadsheet. On spreadsheets read from left to right, the reference for the upper left cell is A1.
最も単純な形では参照はセル一つを参照するが、矩形や立方体の範囲を参照したり、参照リストの中にある参照を参照することもできる。このような参照を作るには参照演算子を用いる。
Reference range operator¶
コロンは左参照で参照されるセルと右参照で参照されるセルの両方を含む最小範囲への参照を形成する。見慣れぬ範囲指定を Table 12 から引用しておく：
Example |
Description |
|---|---|
(A2:B4):C9 |
セル A2 とセル C9 がそれぞれ左上と右下の矩形範囲 |
Sheet1.A3:Sheet3.D4 |
シート Sheet1, Sheet2, Sheet3 すべての A3:D4 |
1:20 |
行 1 から 20 までのセルすべて |
例 |
意味 |
|---|---|
|
二列×三行、六升の矩形範囲への参照。入力行の数式内の参照をクリックすると枠線が矩形を示す。 |
|
升目 A2 と C9 がそれぞれ左上と右下の矩形範囲を参照。つまり、この範囲には三列×八行の 24 個の升目が含まれる。このアドレス指定法は最初の範囲を A2:B4 から A2:C9 に拡張する。 |
|
24 升、四列×二行高さ×三層の立方体範囲を参照。シートタブエリアに Sheet1, Sheet2, Sheet3 がこの順番で表示されていると仮定すれば。 |
|
B 列のすべての升目への参照。 |
|
A 列から D 列の升目全部への参照。 |
|
20 行目の升目全部への参照。 |
|
一行目から 20 行目までにある升目全部への参照。 |
B4:A2, B2:A4, A4:B2 を直接入力すると、Calc はそれを A2:B4 に変換する。つまり、範囲の左上の升目はコロンの左、右下の升目はコロンの右になる。しかし、例えば B4 に _start, A2 に _end と名前をつければ
_start:_end をエラーなく使用できる。セル名の付け方については
Named ranges を見ろ。
Reference concatenation operator¶
セル union 演算子 ~, e.g. A1:C3~B2:D2.
この式の評価は参照リストという型の値だと考えられる。
関数によっては引数に参照リストを取り得る。
上に union と記したが、集合和の意味は薄い。
COUNT(A1:C3~B2:D2)は 10 セルで構成されるにも関わらず 12 を返す。SUM(A1:C3~B2:D2)とSUM(A1:C3,B2:D2)は同じ値を返すが、処理が違う。
利用者ノート
この演算子が活躍する場面が思い浮かばない。
Reference intersection operator¶
セル intersection 演算子 !, e.g. A2:B4 ! B3:D6
結果は左辺の参照と右辺の参照の両方に含まれるセルすべてを含む。
結果が単一範囲を指す場合、単一範囲への参照として評価される。
オペランドが参照リストであってもかまわない。
一般には、評価が参照リストになるらしい。
Options ダイアログボックス ページ の Automatically find column and row
labels をオンにしてあると、行や列に名前を付けておいて row_name !
column_name のような式でクロス集計のセルをわかりやすく参照することができる。
Examples¶
Todo
時間が余ったらやる
Tip
最初に計算する部分を括弧で囲め。
Relative and absolute references¶
参照は、Calc 内の特定の升目の位置を参照する方法で、相対（現在の升目に対して）または絶対（一定量）のいずれかを指定できる。
読者ノート
相対参照と絶対参照について、これらの概念を理解しろ。セルのコピーやリンクに欠かせない。
Relative referencing¶
いきなりだが例：
スプレッドシートのセル C3 と C4 にそれぞれ数字 4 と 11 を入力する。
セル B5 に数式
=B3+B4を入力する。セル B5 をセル C5 にコピーする。これを行うには、単純なコピー＆ペーストを使うか、B5 をクリックして C5 にドラッグする。
セル C5 をクリックする。Formula バー には
=B3+B4ではなく=C3+C4が表示され、C5 の値は C3 と C4 の値である 4 と 11 の合計である 15 になる。
セル B5 のセル B3 とセル B4 への参照は相対参照だ。これは、Calc が B5 の数式を解釈し、それを B 列のセルに適用し、その結果を数式を保持しているセルに入れることを意味する。数式を別のセルにコピーしたときも、同じ手順でそのセルに入れる値を計算した。今回はセル C5 の数式がセル C3 と C4 を参照している。
相対参照は現在のセルに対するオフセットの対と考えられる。
以前のスプレッドシートでは、オフセットペア表記方法を数式で使用可能だった。
この数式をセル B5 から別のセルにコピーすると、結果は常に、数式を含むセルの一行上と行上の二つのセルから取得した数値二つの合計になる。
Calc でアドレスを参照する既定の方式は相対参照だ。
利用者ノート
オフセット方式のほうがコピー＆ペーストで参照を含む式を操作するときの動作を説明しやすいから良いと思う。
Absolute referencing¶
数字の列に一定の金額を掛けたい場合がある。数字の列は米ドルで表示されるかもしれない。これらの金額をユーロに変換するには、各ドルの金額に為替レートを掛ける必要がある。 $10.00 をユーロに換算するには 0.75 を掛けることになり、この場合は €7.50 となる。次の例は、為替レートを入力し、そのレートを使用して列の金額を米ドルからユーロに変換する方法だ：
セル D1 に為替レート Eur:USD (0.75) を入力する。セル D2, D3, D4 に米ドル金額を入力する。10, 20, 30 でいい。
セル E2 に数式
=D2*D1を入力する。結果は 7.5 となり、正しく表示される。セル E2 の数式をセル E3 にコピーする。結果は 200 で、明らかに誤りだ。Calc は相対参照を使って数式をコピーした。E3 の数式は
=D3*D2であり、望まれる=D3*D1ではない。セル E2 で数式を
=D2*$D$1に編集し、セル E3 と E4 にコピーする。結果は 15 と 22.5 となり、正しい。
D と 1 の前の $ 記号は、セル D1 への参照を相対から絶対すなわち固定に変換する。この数式を別のセルにコピーすると、二番目の部分は常に $D$1 と表示される。この数式の解釈は「同じ行の一列左のセルの値を取り、セル D1 の値を掛ける」だ。
セル参照は次の表に示す四つの方法で表すことができる：
参照例 |
記述 |
|---|---|
D1 |
相対。升目 E3 から見て左に一列、上に二行の升目。 |
$D$1 |
絶対。升目 D1 そのもの。 |
$D1 |
部分的に絶対。升目 E3 からは D 列の升目であり、二行上の升目。 |
D$1 |
部分的に絶対。升目 E3 から左一列目、一行目の升目。 |
Tip
数式の参照を変更するには、セルをハイライトし、F4 を押して四種類の参照を循環させろ。数式の一部だけを循環させるには、Formula バー でセルを選択し、F4 で循環させる。 を選択することは F4 を押すことと等価だ。
数式をコピー＆ペーストしたり、スプレッドシートをリンクしたりするのに、相対参照と絶対参照の理解が必須だ。
Named ranges¶
升目やその範囲には名前を付けることができる。升目や範囲に名前を付けると、数式の読みやすさや文書の保守性が向上する。簡単な例としては、升目範囲 B1:B10 に Weight
という名前を付け、範囲内の値すべてのを合計するようなものだ。数式は
=SUM(B1:B10) だ。これが名前を付けることで数式を =SUM(Weight) に変換可能だ。数式の読みやすさの点で、この利点は明らかだ。
もう一つの利点は、名前付き範囲の場所や規模が変更されると、名前付き範囲を引数として持つ数式すべてが更新されることだ。例えば、Weight の範囲が P10:P30 になった場合、Weight を引数として持つどの数式も見直す必要はない。
名前を与える方法：
コマンドを実行する。Define Name ダイアログボックス で対象升目範囲と名前を指示する。
または、シートから升目範囲を選択し、Formula バー の左にある Name 欄で名前を入力する。
名前付き範囲を修正するには Manage Names ダイアログボックス を用いる。このダイアログは Menu バー から を選択するか、Ctrl + F3 を押すことで開く。
Named expressions¶
数式に名前をつけることも可能だ：
Degine Name ダイアログボックスを開く
Name 欄に名前を与える
Range or formula expression 欄に数式を入力する
Add を押す
例として、C1 から C10 までの升目で円周を計算する必要があり、B1 から B10 までの升目でそれらの半径が与えられているとする。式 =2*PI()*B1 で名前付き式
CIRCUMFERENCE を定義する。升目 C1 に =CIRCUMFERENCE と入力して Enter を押す。数式が升目 C1 に適用される。これをコピーして C2 から C10 までの残りの升目に貼り付けると、すべての円の円周が得られる。範囲 C1:C10 の升目全ては
=CIRCUMFERENCE という式を持つ。
利用者ノート
名前付き数式の定義に間接参照セルを含ませる場合、定義時のセルアドレスが影響する。
名前付き表現は、升目のアドレス指定、つまり絶対参照と相対参照に同じ規則を使うことに気をつけろ。
Order of calculation¶
Order of operations - Wikipedia に準拠する。
Calc は数式全体を評価した後、プログラミングの優先順位に基づいて数式を分解し、乗算と除算を他の演算の前に実行する。したがって、数式を作成するときは、数式をテストして、期待される正しい結果が得られることを確認する必要がある。
Note
括弧を使用して、意図した順序で操作をグループ化しろ。例えば、
=B4+G12*C4/M12 は =((B4+G12)*C4)/M12 となる。
Calculations linking sheets¶
複数のシート間でデータを連動させることが可能だ。シートの名前は特定のデータがどこにあるかを特定するのに役立つ。Payroll や Boise Sales のような名前は Sheet1 よりもずっと意味がある。SHEET という関数はシートコレクション内のシート番号（位置）を返す。各文書にはシートが複数あり、左から順に番号が付けられる。Sheet1, Sheet2, … といった具合だ。シートをドラッグしてタブの異なる位置に移動すると、この関数はこのシートの現在の位置を示す番号を返す。Calc の新規ファイルでは、既定ではシートは一枚だ。
たとえば、Sheet1 の A1 に =SHEET() という数式を入力すると、値 1 が返される。
Sheet 1 をドラッグして Sheet2 と Sheet3 の間に配置すると値は 2 に変わる。
他の業績からデータを取得する計算の例は業務の場面で見ることができる。業務では各支店の業績の収益と費用を単一の合算表にまとめる。
読者ノート
Branch1, Branch2, Branch3 の三枚のシートそれぞれが各支店の業績を同じ間取りで持っている。合算表を Combined シートで持っている。そこの升目には例えば
=Branch1.K7 + Branch2.K7 + Branch3.K7 のような数式がある。
シートは同じ構造にしろ。最も簡単な方法は、新しいスプレッドシートを開き、最初の分岐シートを設定し、データを入力し、升目を書式設定し、行と列のさまざまな合計の数式を準備することだ。その後、最初のシートから次のようにコピーを作成する：
シートタブで右クリックし、 を選択。Branch1 と入力する。再度タブを右クリックし、 を選択。
Move/Copy Sheet ダイアログボックス で Copy を選択し Insert before 領域で -move to end position- を選択。 New name の項目を Branch2 に変更する。Copy をクリック。これを繰り返して、Branch3 と Combined シートを作成。
Branch2 と Branch3 のデータをそれぞれのシートに入力する。それぞれのシートは独立し、個々の支店の業績を報告する。
Combined シートでセル K7 をクリックする。Branch1 のタブをクリックし、 + を押し、Branch2 と Branch3 のシートでも同じことを繰り返し、 Enter を押す。これで、セル K7 に三支店の売上高を加算する数式ができた。
計算式をコピーし、K7:N17 の範囲をハイライトして、Paste Special [1] を実行する。ダイアログの Paste 区画にある All と:guilabel:For&mats をオフにし、その領域にある他のすべてのオプションをオンにして OK ボタン を押す。
Yes ボタン を押す。これで、元のシートで設定した書式を維持したまま、各セルに数式がコピーされた。この例では書式設定されていない行のゼロを削除して、シートを整頓する必要がある。
Function Wizard を使用してリンクすることもできる。使い方は Using the Function Wizard を見ろ。
Understanding functions¶
Calc にはデータの分析や参照に役立つ関数が 500 以上用意されている。これらの関数の多くは数値を扱うものだが、日付や時刻、テキストを扱うものもある。関数は、二つの数値を足し合わせたり、数値の一覧の平均を求めるような単純なものから、標本の標準偏差や数値の双曲正接を計算するような複雑なものまである。
通常、関数の名前はその関数が何をするのかを省略したものだ。例えば、FV 関数は投資の将来価値を示し、BIN2HEX は二進数を 16 進数に変換する。Calc では関数は大文字、小文字、または混合で入力することができる。
基本的な関数のいくつかは演算子と多少似ている。演算子 + に対する関数 SUM
や、演算子 * に対する関数 PRODUCT といったものだ。
各関数には計算に使われるいくつかの引数がある。これらの引数にはそれぞれ名前があったりなかったりする。場合によっては、引数には定義済み選択肢があり、それを理解するために Function Wizard や Functions 甲板 のテキスト、あるいは Help を参照する必要がある。しかし、多くの場合、引数は手動で入力する値か、スプレッドシートの升目、升目範囲にすでに入力されている値だ。Calc では他の升目から値を入力するには、その升目の名前や範囲を入力するか、マウスで升目を選択する。升目の値が変更されると、関数の結果が自動的に更新される。
Tip
利用可能な各関数の詳細については次の Wiki ページを見ろ。
Documentation/Calc Functions - The Document Foundation Wiki
これらの Wiki ページは利用可能な文書に最近追加されたものであり、継続的に改善されている。
Compatibility with other spreadsheet applications¶
Calc が MS Excel のスプレッドシートを開くと、特定の関数で発生する非互換性を回避する措置が自動的に働く。
読者ノート
この節は真面目に読んでいない。
Understanding the structure of functions¶
関数呼び出しは等号、関数名、引数リストから構成される。
すべての関数は似たような構造を持っている。関数の入力に適切なツールを使えば、この構造を学ぶことから逃れられるが、それでもトラブルシューティングのために知っておく価値はある。
典型的な例として、入力された検索条件に合致する升目を見つける関数の構造は次のようになる：
=DCOUNT(Database, Database field, Search criteria)
関数は単体では存在できず、常に数式の一部でなければならない。したがって、関数が数式全体を表している場合でも、数式の最初に = がなければならない。関数が数式のどこにあるかにかかわらず、関数は上の例の DCOUNT のように、その名前で始まる。す。関数名の後には引数が続く。引数は、特にオプションと記載されていない限り、すべて必須だ。
引数は括弧内に追加され、カンマで区切られる。Calc 関数は最大 255 個の引数を取ることができる。引数には数値や一つの升目だけでなく、数個から数百個の升目を含む配列や升目範囲も指定できる。
関数の性質に応じて、表7のように引数を入力することができる。
実引数 |
意味 |
|---|---|
“text data” |
引用符は、テキストまたは文字列データが入力されていることを示す。 |
9 |
数として 9 が入力されている。 |
“9” |
数字の 9 がテキストとして入力されている。 |
A1 |
セル A1 のアドレスが入力される。 |
B2:D9 |
セル範囲を入力している。 |
Nested functions¶
関数は他の関数の中で引数として使うこともできる。これを入れ子関数と呼ぶ。
=SUM(2,PRODUCT(5,7))
入れ子関数で何ができるかを知るために、自己学習モジュールを設計していると想像するがいい。モジュール中、学生は小テストを三つ行い、結果を升目 A1, A2, A3 に入力しする。A4 では =AVERAGE(A1:A3) という式で小テストの結果を平均することから始まる入れ子式を作成できる。この数式は IF 関数を使用して、小テストの平均評点に依存するフィードバックを学生に与える：
=IF(AVERAGE(A1:A3)>85, "Congratulations! You are ready to advance to the next module", "Failed. Please review the material again. If necessary, contact your instructor for help")
平均点次第で合格か不合格のメッセージが学生に送られる。
平均の入れ子式には等号が必要ないことに注意。どちらの式も、式の最初にある等号だけで十分なのだ。
スプレッドシートに慣れていないうちは関数をスクリプト言語として考えるのがいちばんだ。ここでは簡単な例を挙げたが、関数を入れ子にすることで、Calc の数式はすぐに複雑になる。
Note
Calc は、数式を入力するときに便利な記憶補助として、升目の横にあるツールチップに表示される数式の構文を保持する。
Using the Functions deck¶
より確実な方法は Sidebar Functions 甲板 を使用することだ。この甲板は を選択するか、Sidebar がすでに表示されている場合は右にあるタブパネルの Functions 図像をクリックすることで表示される。
Functions 甲板 には各関数とその引数の簡単な記述がある。関数を強調表示し、小窓の下部を見ると記述が表示される。必要であれば、マウスポインターを一覧と記述の間の区切りに合わせろ。ポインターが両方向矢印になったらそれを上にドラッグして記述の紙幅を広げろ。関数名をダブルクリックすると、関数の各引数の仮置場とともに、その関数が現在の升目に入る。
Functions 甲板 の使用は手入力とほぼ同じ速さで、使いたい数式を覚えておく必要がないという利点がある。エラーの可能性も低くなるはずだ。もう一つは、最後に使用した数式を表示する機能だろう。
Using the Function Wizard¶
関数入力は Sidebar よりも Function Wizard のほうが使用頻度が高いらしい。
最もよく使われる入力方法は Function Wizard だ。これを開くには次のいずれかを行う：
Menu バー から を選択
Formula バー の Function Wizard 図像をクリック
キーバインド Ctrl + F2
Function Wizard は Functions 甲板 と同じヘルプ機能を備えているが、完成した関数の結果や、その関数の一部である大きな数式の結果を見ることができるフィールドを追加する。
リストを短くするために関数の区分を選択し、名前の付いた関数をスクロールダウンして、必要な関数をダブルクリックして選択しろ。Function Wizard で利用可能な区分と、各区分で利用可能な関数の数は表8だ。
Todo
table 8
関数を選択すると、その説明がダイアログの右側に表示される。オプションとして Search 欄関数名を入力することができ、文字が打ち込まれるたびに検索結果が絞り込まれる。
テキストボックスに手動でデータを入力できるか、Shrink ボタンを押して Function Wizard を縮小し、シートから升目を選択できるようになる領域が Function Wizard の右側に表示されるようになる。
升目を選択するには、それを直接クリックするか、マウスの左ボタンを押したままドラッグして必要な領域を選択する。
領域が選択されたら Expand ボタンを押して再びウィザードに戻る。
複数の引数が必要な場合は、次のテキストボックスをクリックし、次の升目、升目範囲について選択工程を繰り返す。この作業を必要なだけ繰り返せ。ウィザードは SUM 関数の範囲または引数を 255 個まで受け付ける。
OK ボタン を押して関数を受け入れ、セルに追加し、結果を得る。
Note
一覧で関数をダブルクリックして選択し、気が変わって別の関数を再度ダブルクリックして選択した場合、二番目に選択した数式が Formula 欄の一番目に選択した数式に追加される。Formula 欄を消去してから、関数をダブルクリックして追加する必要がある。
この付加機能により Formula 欄で数式を積み上げて複雑な数式を作成することができる。
また、Structure タブを選択すると、数式の部位を木表示することができる。 Functions 甲板 の主な利点は、各引数がそれぞれのフィールドに入力され、管理が容易になることだ。この信頼性の代償として入力が遅くなるが、スプレッドシートを作成する際には、一般的に高速度よりも精度の方が重要だ。
Function Wizard の構造ビューは、とても長く、入れ子である複雑な数式のデバッグや修正に重要だ。このビューでは数式が解析され、各数式部品がより単純な関数呼び出しまたは算術演算によって計算され、計算規則に従って結合される。計算式の解析された各要素を視覚化し、中間結果が正しいかどうかを、間違いが見つかるまで検査することができる。
関数は Input 行 に入力することができる。Input 行 に関数を入力した後 Enter を押すか、Formula バー の ✓ を押すと升目に関数が追加され、その結果が表示される。
値ではなく数式がセルに表示される場合は Options ダイアログボックス ページ の Formulas がオンになっていやしないかを確認しろ。
Tip
Menu バー の やキーバインド Ctrl + @ でも数式のオンオフが切り替えられる。
Array formulas¶
What is an array formula?¶
配列数式を理解しろ。配列式は一般に複数の値を同時に扱う。
複数の値を処理できるだけでなく、複数の値を返すこともある。結果も配列になる。
Calc が数式を更新すると、影響を受ける各升目が読み込まれ、その数式が再計算される。同じ数式（式は計算するデータしか変更しない）を持つ升目が一列に千個あるとすると、解釈と実行に千個の同一の数式が必要になる。
配列式は計算式を一度評価し、配列の規模と同じ回数だけ計算を実行するため、各升目の計算式を解釈する時間を節約できる。計算式自体の記憶領域節約にもなる。
仮に A1:C5 範囲にある値を十倍して別の範囲 E1:G5 に出力したいとする。配列を利用するならば次のようになる：
E1:G5 を選択する
数式
=10*A1:C5を入力し、キーバインド Ctrl + Shift + Enter でこの入力を確定する。
その結果 3x5 の配列ができる。
When do you use array formulas?¶
異なる値を使って計算を繰り返す必要がある場合は、配列式を使え。
配列式を追加するには、配列範囲全体を選択してから、配列式に必要な変更を加える。
配列二つを乗算する MMULT 関数など、配列のためのさまざまな数学関数がある。
Creating array formulas¶
Function Wizard を使用して配列式を作成する場合は、結果が配列で返されるように Array を毎回オンにしろ。
配列式をセルに直接入力することもできる。その結果、自動的にセルの配列が作成される。
Note
配列数式は中括弧の中に表示される。中括弧を手動で入力して配列数式を作成することは不可能だ。
結果配列の升目は変更に対して自動的に保護される。しかし、配列の升目範囲全体を選択することで、配列式を編集またはコピーすることが可能だ。
Implicit intersection of array formulas¶
引数などの参照範囲が配列式を示す「通常の」数式を作成することもできる。この式は配列式の「暗黙の交差」としても知られている。結果は、参照範囲と数式が見つかった行または列の交点から得られる。交点がない場合、または交点の範囲が複数の行または列にまたがる場合は、#VALUE! エラーメッセージが現れる。次の例でこの概念を説明する。
A1:C3 に適当に数値を入れておく。次の手順を実施するとする：
升目 D1 に配列式
{=A1:A3 + 100}を入れる。D1:D3 のそれぞれに期待する和が出力される。升目 D2 に非配列式
=A1:A3 + 100を入れる。D1 と D3 は空のままで、D2 には値 195 が入っていることを確認する。これが配列数式の暗黙の交差だ。入力された数式は内部的には値を三つ返すが、表示されるのはそのうちの二つ目だけだ。読者ノート
この入力は不可能。前述の仕様により D2 は保護されている。
升目 D4 に非配列式
=A1:A3 + 100を入れる。すると D4 はエラーを表示する。これは行 4 が数式の範囲 A1:A3 から外れているからだ。
読者ノート
この節は要旨がよくわからない。
Strategies for creating formulas and functions¶
スプレッドシートを自分以外が使用する場合は特に、どこでどのような入力が必要であるかを容易に理解するように示せ。表計算シートの目的、入出力仕様は最初のシートに記載することが多い。
論理式や条件付き書式を使用してエラーを捕捉するのは良い考えだ。
Place a unique formula in each cell¶
使い捨てスプレッドシートであれば、適切なセルそれぞれに一意となる数式を配置しろ。
Break formulas into parts and combine the parts¶
分割統治法に則れ。長い数式を小さな部分に分解し、それらを組み立てるように全体を構成しろ。
Speeding up calculations¶
数千から数十万の行と数列を持つシートは財務部や研究所で頻繁に見られる。このような生データセットに対して行われる計算は時間がかかり、数分から数時間、場合によっては数日に及ぶこともある。
よくある間違いは、升目ごとに数式を入れ、何千もの数式の解釈と計算を行うことだ。ここでは計算高速化に与する事項を見ていく。
Use array formulas on massive data¶
配列式は一つの式が大量データに適用される。大きなデータ集合の場合、計算量を大幅に削減する。
Use consolidation functions¶
連結関数はデータセットに対して計算を実行する。SUM, SUMIF, SUMIFS, SUMPRODUCT が連結関数の例だ。例えば、べらぼうに長い部品表があり、そこに数量を単価で乗算し、合計して原価を計算する必要がある場合、部品表の各登録項目に数式を適用して合計する代わりに、
SUMPRODUCT(quantity, unitprice)
という数式を使用することができる。SUMPRODUCT は数量データセットの各升目に単価の対応する升目を乗算し、製品すべてを合計する。
同じような状況は、元のデータセットの部分集合を合計しなければならないときにも起こる。 そのような場合は、各登録項目にテストを適用して合計の一部とする必要がある。例えば、値が厳密に正の場合だ。
SUMIF(data_to_test; ">0"; data_to_sum)
を使用する。ここで、data_to_test は正の値をテストするデータセット、
data_to_sum はテストに応じて値を合計する列、">0" はテストそのものだ。
その他の連結関数には AVERAGEIF, COUNTIF, MINIFS, MAXIFS などがある。
Use macros to create functions¶
独自の関数やマクロを作成するという戦略もある。末端利用者によるスプレッドシートの使用を大幅に簡素化し、数式を単純に保つことでエラーを回避できる可能性が高い場合にこの方法論が採用される。また、修正や更新を一元管理することで、管理も容易になる。
マクロの使用については Chapter 13, Macros で説明する。
マクロや独自関数を多用することの危険性は、スプレッドシートの基礎となる原則が、元の作成者以外の使用者には、時には作成者にも、見えづらくなってしまうことだ。
Multi-threading¶
現代の計算機の多くは複数コア処理器を搭載し、複数スレッドを搭載している。コアは CPU 内の物理的なハードウェア部品だ。スレッドは CPU の仕事量と仕事を効率的に管理するための仮想部品だ。CPU は一度に複数スレッドとやりとりすることができ、複数スレッドによって CPU はより効率的になり、全体的な能率が向上する。
Calc は複数スレッドを支援しており、使用中の計算機で利用可能な並列処理をスプレッドシートで利用る。この機能は Options ダイアログボックス ページ の Enable multi-threaded calculation で制御可能だ。これをオフにすることは勧められない。
Calc はスプレッドシートで並行作業が有効な箇所を自動的に特定し、それに応じて処理を行う。一般的に、スレッドが使用されるのは、数式グループに使用され、列内の隣接するセルが同じ数式を使用するが、相対的升目アドレス割当のために結果が異なる場合だ。最適化が列ベースであるため、行ベースのレイアウトでは効率が悪くなる可能性がある。
Finding and fixing errors¶
数式とその数式が参照しているセルを調べるため、エラーメッセージ、入力の色分け、検出各機能が用意されている。
Error messages¶
数式のエラーメッセージは、通常 501 から 540 までの数、あるいは #NAME? エラーメッセージの場合はセルに表示され、エラーの簡単な説明がステータスバーの右側に示される。
ほとんどのエラーメッセージは、数式の入力方法に問題があることを示しているが、いく Calc またはその現在の設定の制限にぶつかっていることを示すものもある。
エラーメッセージは間違いを正すための貴重な手がかりとなる。付録 B および Help で、エラーコードを検索すると、詳細な説明を見つけることができる。最も一般的なものを表 9 に示す。
コード |
意味 |
|---|---|
#NAME? |
Err:525. 引数に有効な参照がない。 |
#REF! |
Err:524. 参照されている升目に対する列、行、シートのいずれかが失われている。 |
#VALUE! |
Err:519. 引数の一つの値が要求される型でない。例えば、値の周囲に二重引用符がない場合など。また、使用されている升目や範囲の書式が間違っている場合もある。 |
#DIV/0! |
Err:532. ゼロによる除算。 |
#NUM! |
Err:503. 計算の結果、定義された値域をオーバーフローする。 |
509 |
等号などの演算子が数式にない。 |
510 |
数式に変数がない。 |
Examples of common errors¶
#DIV/0! division by zero¶
このエラーは、数値をゼロまたは空白のセルで割った結果生じる。この種の問題を避ける簡単な方法がある：
ゼロまたは空白のセルが表示されている場合は、条件関数を使用する。例えば、関数
IF を使用して =IF(C3>0, B3/C3, "No Report") のように処理する。
#VALUE! no result and #REF! incorrect references¶
エラー #VALUE! は升目に正しくない値の型が含まれている場合によく起こるエラーだ。
エラー REF! は参照の欠落が原因だ。数式が削除されたシートを参照しているなどの場合がある。
Color coding for input¶
入力の色分けというのは、数式中の升目参照、範囲参照、関数呼び出しの各引数の文字列色のことを指す。
Calc では参照される升目の輪郭線に八色を使用する。最初は青で始まり、相異なる七色が続き、再び青から順番に循環する。
Value highlighting¶
出力にも色分けがあり、字の並びとしては同一でも、型が異なる場合（例：数値とテキスト）を見分けるときに有用だ。
(Ctrl + F8) をオンにすると、中身の文字に異なる色を割り当てることで、テキストと数値のデータ型を区別する。テキストは黒文字、数値は青文字だ。Chapter 2, Entering and Editing Data も参照。
The Detective¶
長いか複雑なスプレッドシートでは、色分けがあまり役に立たなくなる。
升目参照の連鎖をたどるには 各種コマンドを実行する。これで矢印がシート内に描かれる。
対象セルにカーソルを置き Shift + (F9 or F5) を押すのが早い
矢印の向きが気に入らない
矢印を消去するには コマンドを実行
の使い方を習得しろ。
Examples of functions¶
初心者は算術、統計に区分されている関数から学べ。
Basic arithmetic and statistics¶
最も基本的な関数は、基本的な算術計算やセル範囲の数値を評価する数式を作る。
Basic arithmetic¶
算術演算子が用意されているのに、関数 SUM, PRODUCT などを敢えて使う理由としては、オペランドがセル範囲にわたる場合などが考えられる。=A1+A2+A3+A4+A5 よりも =SUM(A1:A5) のほうが明らかに良い。
Simple statistics¶
表計算関数のもう一つの一般的な使い方は、クラスでの一連のテストの点数や、企業の四半期ごとの利益の概要など、リストから有用な情報を引き出すことだ。
COUNT は指定されたセル範囲内の記載項目の総数を返す。
MIN, MAX はセル範囲か、個別の入力された一連のセルから最小または最大の記載項目を返す。
MINA, MAXA は上記関数の変種であって、数値でない値をゼロとして扱う。
他にも、名前が A で終わる関数は、値がテキストである場合にこのような措置を講じる。
SMALL, LARGE は n も与えて第 n 位の値を返す。
AVERAGE は算術平均を返す。
MEDIAN は中央値を返す。
MODE は最頻値を返す。
QUARTILE は入力配列から指定四分位数の項目を返す。最小値、最大値は 0, 4 をそれぞれ指示する。
RANK は値の配列における順位を返す。昇順にも降順にも列挙させることが可能。
これらの関数には機能が重複するものがある。例えば、MIN と MAX は両方とも QUARTILE でカバーされている。他の事例では、カスタムソートや絞り込みでほとんど同じ結果が得られることがある。どちらを使うかは好みや要望次第だ。MIN と MAX は覚えやすいから使いたいという人もいるし、QUARTILE は汎用性が高いから使いたいという人もいる。
Using these functions¶
一般的には、関数は絞り込みや並び替えよりも簡単に調整でき、柔軟だ。
時には、便利な空白の升目に数式を一時的に入力し、入力が終わったら削除したいと思うかもしれない。しかし、常に同じ関数を使用している場合は、雛形を作成し、使用するすべての関数を入力し、その左側の升目をラベルとして使用するのがよい。いったん雛形を作成すれば、登録項目の変更に応じて、自動的かつ即興的に、または F9 を押して選択したすべての升目を更新することで、各計算式を簡単に更新することが可能だ。
Rounding off numbers¶
Calc には統計や数学目的で数字を丸めるさまざまな方法がある。Help を見ろ。
ROUND 関数は呼び出し有無の比較を検討すると安心だ。例えば、
=ROUND((SUM(A1,A2))をセル A3 に、=ROUND(A3)をセル A4 に
置き、それぞれの関数をラベル付けするというやり方もある。
Volatile / non-volatile functions¶
Functions that are always recalculated whenever a recalculation occurs are termed volatile functions.
揮発性関数の動作の一部を理解するために、空のスプレッドシートを作成し、升目 A1 に
=RAND() という数式を入力した簡単な例を考えてみよう。RAND は揮発性関数の一つだ。升目 A1 に 0 から 1 の間の乱数が生じる。その後、升目 B2 に任意の値を入力して Enter を押すと、A1 に表示されていた値が更新され、別の乱数が表示されることに気づく。使用者が A1 の数式を変更していないにもかかわらず、また更新された B2 が
A1 と連動していないにもかかわらず、Calc は A1 の乱数を再計算する。まとめると、
RAND 関数は、以下のいずれかのときに新しい値を生成する：
を選択
F9 を押す
任意の入力イベントで任意の升目が更新された
揮発性関数を理解することは、特に大規模なスプレッドシートを作成する場合に重要だ。スプレッドシートを作成する際には、揮発性関数を適切に使用するように設計しろ。
例えば乱数生成関数やタイムスタンプ関数は揮発性だ。
RAND 関数と RANDBETWEEN 関数に関しては、不揮発性の等価物である RAND.NV と RANDBETWEEN.NV が用意されている。これらは関数の値をそれほど頻繁に更新する必要がない場合に便利だ。不揮発性関数は、関数を含む升目が選択されている場合を除き、新しい入力イベントで再計算されず、上記の操作のいずれによっても再計算されない。ファイルを開くときに再計算される。
Using wildcards and regular expressions in functions¶
関数には、その実引数に正規表現またはワイルドカードを使用可能であるものがある。
正規表現は、テキスト文字列を検索する最も強力な方法だ。例を含む正規表現の詳細については Chapter 2, Entering and Editing Data の当該節を見ろ。
MS Excel はこのような正規表現を扱っていない。Calc 文書を変換して提出するような状況では使用を避けろ。
正規表現引数に対応している関数例：
名前が D から始まるデータベース関数
平均値、勘定関数、最大値、最小値、和を得る各関数
表探索関数
当然ながら検索・置換関数
Options ダイアログボックス ページ に関連設定項目がある：
Formula Wildcards ではワイルドカードのみが有効になっている
General Calculate 項目の一部が正規表現の関係する動作に影響する。
読者ノート
チュートリアルのような記述があるが、割愛。
Advanced functions¶
Calc 文書はユーザー定義関数またはアドインによって機能拡張可能だ。
ユーザー定義関数は、マクロ (Chapter 13, Macros) を使用するか、個別のアドインや拡張機能を記述することで設定可能。マクロは Basic, BeanShell, JavaScript, Python のいずれかで記述される。
章末注
