行を挿入した後もNumbersの数式で最上段の固定アドレスを読み取るには、引用符で囲んだアドレスを指定してINDIRECTを使います。たとえば、ヘッダ行の下にある最初のデータ行には=INDIRECT("B2")を使います。まず、その位置を参照したいのか、元のセルを参照したいのかを決めてください。

この手順はMac版Numbersを対象とし、小さな架空のオブジェクト数テーブルを使います。目的は、新しい行が挿入されてその位置を占めた場合も、現在データの最上段にある数を表示することです。以下で使う関数と編集操作はAppleの資料に記載されています。行の挿入例は、資料に記載された動作を応用したものです。

「参照を維持する」の意味を決める

一見同じに思える作業でも、実際には2通りあります。セルが移動しても元の数を読み取り続けるのか、それともデータの最上段の位置にある数を常に読み取るのか、数式を変更する前に期待する結果を選んでください。

期待する結果 方法
B2など、特定のアドレスに現在入っている値を読む INDIRECTと引用符で囲んだアドレスを使う
数式を別の場所にコピーしたときも参照を変えない $B$2のような絶対参照を使う
集合内の特定の位置にある値を取得する 行と列のインデックスを明示してINDEXを使う
スクロール中も列ラベルを表示する ヘッダ行を固定する

Appleは絶対参照を数式のコピーに関する機能として説明しています。$B$2は列と行の両方を絶対参照にし、B$2は行だけを固定します。この資料は、ドル記号を付ければアドレスが構造上の編集の影響を受けなくなる、という一般的な保証を示してはいません。数式でセルを参照する。

またAppleの資料によると、参照先のセルを移動すると参照も更新されます。たとえば、参照先のセルをA1からD5に移動すると、数式内の参照もそれに応じて変わります。このことから、セルを追跡する場合と位置を指定する場合とでは、選ぶ方法が異なる理由がわかります。数式とその計算値をコピーまたは移動する。

データの最上段を固定参照する

ヘッダ行が1行、ヘッダ列がない小さなテーブルを用意します。「フォーマット」サイドバーで「テーブル」タブを開き、「ヘッダとフッタ」の設定でレイアウトを確認します。ヘッダ行が1行なら、1行目にラベルが入り、2行目が最初のデータ行です。Appleによると、ヘッダの行数を変更すると既存の行がヘッダ行に変わり、新しい行は挿入されません。MacのNumbersで行と列を追加または削除する。

次の例の値を入力します。結果を表示するD2を含め、ほかのセルはすべて空欄にしておきます。

行 列A 列B 列C 列D
1 オブジェクト 数 現在の最上段の数
2 立方体 12
3 円錐 8
4 球体 5

D2を選択し、次の数式を入力してReturnキーを押します。等号を入力すると「数式エディタ」が開きます。AppleのMac向けガイドでは、Returnキーを押して数式を確定する方法が説明されています。MacのNumbersで表のセルのデータを使って値を計算する。

=INDIRECT("B2")

期待される結果は12です。Appleによると、INDIRECTは文字列で指定されたアドレスの内容を返します。2つ目の引数を省略すると、列文字と行番号を使うアドレス形式が既定で使用されます。ここでは、"B2"が解決対象となる文字列のアドレスです。この変わらない文字列を使って、常に2行目を参照するのがこの方法の実用的な考え方です。INDIRECT。

テーブルにヘッダ行がない場合、最上段のデータ位置はB1になります。画面に表示されている行番号を見てアドレスを選んでください。ヘッダを固定するとスクロール中も見えるようになりますが、数式の参照先は変わりません。MacのNumbersで行と列を追加または削除する。

行を挿入して結果を確かめる

結果が元の値を追っているのか、新しい最上段の値を読み取っているのかを判別できるよう、異なる数を使います。Appleは行または列の挿入後に数式を確認し、参照先に意図したセルが含まれているかを確かめるよう勧めています。数式のエラーを確認し、ベストプラクティスに従う。

  1. 2行目の行番号にポインタを合わせ、表示された矢印をクリックします。
  2. その行の上に行を追加するオプションを選びます。
  3. 新しいA2に「プリズム」、新しいB2に20を入力します。
  4. 元の行とともに下へ移動した結果の数式を見つけ、値を確認します。
  5. 結果をダブルクリックして数式の内容を確認します。

Appleの資料では、行番号のショートカットメニューから行を挿入する方法が説明されています。MacのNumbersで行と列を追加または削除する。

この例のテストでは、期待される結果は20、つまりB2にある新しい数です。元の12はB3に移動しています。作業中のテーブルにこの方法を適用する前に、表示値と引用符で囲んだアドレスの両方を確認してください。

作業で最上段に複数の項目を追加することが多い場合は、もう1行挿入してテストを繰り返してください。数式にエラーが出なくても、元の数を表示したままなら、この記事の目的は達成できていません。有効な回答かどうかだけでなく、回答の意味も確かめるテストが役立ちます。

Preserve RowやINDEXを使うのはどんなときか

テーブル内で数式を下方向にコピーするときに問題が起きる場合は、Preserve Rowを使います。数式のセルをダブルクリックし、参照トークンの三角形をクリックしてPreserve Rowを選びます。列も固定する必要がある場合は、Preserve Columnも選びます。参照を選択してCommand-Kを押すと、行と列の両方を固定できることもAppleの資料に記載されています。MacのNumbersで表のセルのデータを使って値を計算する。

INDEXは、指定した集合の特定の行と列にある値を調べるための別の方法です。たとえば元のサンプルデータを使い、参照範囲の外にある空のセルに次の数式を入力すると、12が返されます。

=INDEX(B2:B4, 1, 1)

インデックスはB2:B4の1行目、1列目を意味します。テーブルの2行目を直接意味するわけではありません。構造上の編集で指定した集合が変わる場合は、その集合を再確認してください。Appleの説明では、インデックスは範囲を基準とし、その範囲の大きさ以内でなければなりません。インデックスに0を指定すると行または列全体を配列として返すことがあるため、1つの値を得るには正の数を明示してください。INDEX。

数式、配列、グラフ、ハイライトの用途を区別する

この作業には単一セルの数式で十分です。アドレスを範囲に広げると、結果の形が変わります。Appleの資料によると、INDIRECTは範囲を表す文字列から配列を返し、その配列を別の関数で使用できます。現在のヘルプには、スピル配列についても説明があります。"B2"を複数セルのアドレスに置き換えても、数式が1つの数を返すとは限りません。INDIRECT。

グラフには独自のデータ参照設定があります。グラフに計算後の最上段の数を表示するには、補助セルで計算し、グラフの「データ参照を編集」コントロールからそのセルを選択します。Appleの資料では、表のセルを選択するか、既存の系列の選択範囲を調整してから「完了」をクリックする方法が説明されています。資料に記載されたグラフの操作には、この回避策の数式を入力する欄はありません。MacのNumbersでグラフデータを変更する。

条件付きハイライトも、ルールと比較値を使って設定します。数を計算済みの補助値と比較するには、その数のセルを選択し、「フォーマット」→「セル」→「条件付きハイライト」を開いて「ルールを追加」をクリックします。適切な数値ルールを選び、「セル参照」ボタンで補助セルを選択します。ルールに完全な数式を入力できると考えず、計算は補助セルで行ってください。複数のルールに一致する場合、Appleによると最初のルールで表示形式が決まります。MacのNumbersでセルの条件を使う。

予期しない結果を調べる

エラーアイコンが表示されたら、クリックして説明を読みます。アドレスの入力、二重引用符、数値の書式、数式が自分自身を参照していないかを確認してください。Appleはこれらをよくある数式の問題として挙げています。診断中はエラーを表示したままにし、誤ったアドレスを代わりの結果で隠さないようにしてください。数式のエラーを確認し、ベストプラクティスに従う。

数式が別のテーブルを参照する場合は、テーブル名による修飾を確認してください。Numbersでは、Objects::B2やSheet 2::Objects::B2のように、テーブル名とシート名を二重コロンで区切って指定できます。テーブルをまたぐ参照を作成するときは、スプレッドシート内の実際の名前を使い、参照先の名前を変更または再編成した後は固定アドレスも再確認してください。数式でセルを参照する。

データの最上段にあたるアドレスを選び、単一セルの数式を入力して、元の項目の上に新しい識別しやすい数を挿入して確認します。表示値が新しい最上段の行と一致すれば、その参照が意図した作業に役立つことを具体的に確認できます。

出典