2019.03.30この記事は約11分で読めます。「知っていると得するかもしれないExcelの癖」シリーズ第九回、テーマは「テーブルでの数式利用」になります。2016バージョン1902を使用して確認しています。「テーブルとして書式設定」の機能につきましては「行を1行おきに色分けする方法」のテーマで説明していますので合わせてご一読いただければ幸いです。Excelのテーブル機能は、表計算ソフトのセル参照機能の延長として捉えるのではなく、データベースソフトのテーブル機能として捉えた方が理解し易いことと存じます。そんなこと言われても「ちょっと何を言っているのか良く解らない」と感じられることと存じますが、まずはテーブルに数式をセットする際に一番重要となる「構造化参照」という言葉について掘り下げてみたいと思います。数式入力時にオートコレクト機能が働く場合と、働かない場合1数式でない値が列に設定されているとオートコレクト機能は働かない2ショートメニューからの「集計列の自動作成を停止」はブック全体に反映する3オートコレクトで設定された数式を2行以上削除した後、別の数式を入力した時はオートコレクトは働かないテーブルに数式をセル参照と構造化参照で設定する1列1に行番号表示の数式を入力する2列1の累計を表示する数式を3パターンで列2から列4に入力するまとめ「構造化参照」とは?「構造化参照」については、既に説明をされているページがございます。Users」サイトのページは解り易くまとめられていますので、まずはご一読いただければ幸いです。テーブルが関わる数式に注目!【Excel・エクセル】テーブル内のセルを使って数式を組むと、その数式は、普段とは違う「構造化参照」という形式の数式になります。テーブルでの構造化参照の使い方」のページの中では、テーブル内に数式を入力する場合はセル参照(相対参照や絶対参照)ではなく、「テーブルの名前」や「列の名前」を使用する構造化参照を利用することが推奨されています。この構造化参照のポイントは下記の2点、「@」と「#」の使い方になる認識です。Users」ページには出てきませんが、『「#(シャープ)」が付いた表現』という表現で書かれており、[#集計]とか[#見出し]という形で数式の中で使用されます。ここで「@」や「#」を使用した数式の説明に入る前にテーブルの持つ特徴についてまとめて置きたく存じます。テーブルの持つ特徴1テーブルの範囲は名前で管理されるテーブルを設定すると、数式タブ→「定義された名前」リボングループの「名前の管理」にデフォルトで「テーブル1」という名前が自動的に設定されます。この自動設定された名前は、テーブル全体が削除されるか、「範囲に変換」されるかしないと削除することはできません。(左図のように削除ボタンが無効化される)なおこの名称は、テーブルのどこかをクリックした時に表示されるデザインタブ→「プロパティ」リボングループのなかのテーブル名として表示されます。2テーブルには見出し行が設定される構造化参照で使用される「列の名前」は必須になり、見出し項目を持たない表では、見出し行が自動的に挿入されて表自体が1行下に移動します。そのためテーブル設定された見出し項目を含む行を行範囲で選択し、マウス右ボタンでショートカットメニューを表示した時には、「削除」が無効化されています。逆説的な表現をすると、「名前の管理」も含めて「テーブルのために本来の表計算ソフトの機能を制限している」ことになります。3テーブル限定のオートコレクト機能オートコレクト機能は、文章入力時のスペルチェック機能が良く知られているという認識ですが、テーブルのためにだけ追加されているオートコレクト機能があります。ファイルタブ→オプション→(Excelのオプション画面左側のメニューから)文書校正→オートコレクトのオプションボタン→入力オートフォーマットタブかなり奥まったところにあるのですが、この「入力オートフォーマット」タブの中の左図赤枠の設定はテーブルに限定した機能になります。この機能を説明するMicrosoftのページ「入力オートフォーマットを構成するかオフにする」は存在するのですが、Wordにおける説明がメインで構成されています。そのページに貼り付けられている画面もWordの入力オートフォーマットの画面になっておりますが、Excelの説明も含まれていますのでご安心ください。混ざりあっていると解り難いので冗長的ではありますが下記に転記致します。隣接する行または列にデータを入力すると、既存のテーブルに新しい行または列が追加されます。列は自動的に既存のテーブルの一部として書式設定されます。セルに単一の数式を適用します。セルに適用されるよう自動的に調整されます。最初のオプションはテーブルの書式に関するものであることが解ります。このチェックボックスのチェックを外した状態で、テーブル内に行または列を追加しても、追加した行または列は書式を引き継ぎますが、隣接した行または列でデータを入力した時には自動的にテーブルに取り込まれる(テーブルの範囲が拡張される)ことはなくなります。二番目のオプションはExcelの機能の中でも、かなり強権的といいますか、有無を言わさずといいますか、合っていようが無かろうがまず適用されてしまいますので、適用除外の仕方も含めて解ったうえで使用するのが良い認識です。値のクリア」が制限される数式には直接関係しない特徴ですが、テーブルの配置を決める際に知っていた方が良いと存じます。値のクリア」が無効化されます。行が重ならなければ、赤枠の無効化は解除されますが、テーブルを配置するシートのデザインを考える時は注意が必要です。個々のテーブルを選択すればテーブルごとでの行の挿入・削除はできますのでご安心ください。できないのはシートに対しての一行挿入です。テーブルでの数式はカレントレコードを処理するのが基本まずは、構造化参照を使った数式の基本的な考え方を整理したいと思います。一般的にデータベースからレコードを読み出して処理をする時には、テーブルに格納されているデータからキーとなる項目を使って一つのレコードを取り出します。これに対してExcelのテーブルにはキー項目は存在しません。それはテーブルは、すべてのデータが目に見えている形で存在するために、いちいち取り出す必要がないためです。そのようなテーブルの中でもカレントレコードにあたる考え方は存在し、それがExcelシートでフォーカスを持ったセルの行になります。行指定で複数行選択していたとしても、その行範囲のなかでフォーカスを持ったセルは最初の行になっています。これは先頭行の最初のセルだけ背景色は無く、他のセルと背景色のかかり方が異なることで解ります。テーブルではクリックしたセルの含まれる行がカレントレコード従って、クリックさえすればテーブル内のどこの行でもカレントレコードになるため、構造化参照を使うと[@列見出し名]でテーブル内のセルの値を取り出すことができるようになります。この点がセル参照の場合と大きく異なるところです。使うと[@列見出し名]でテーブル内のセルの値を取り出すことができる』という説明に納得がいかない人は、「前行のセルの情報を見る必要がある時はどうしたらよいのか?」ということが気にかかっていることと存じます。データベースソフトを使用する場合だと、「一つ前のレコード」を意識する事はありませんが、表計算ソフトの場合は確かにそういった処理をすることもあり得ます。なるとテーブル内でセル参照を利用することは推奨されていない以上、前行を参照する方法としてはOFFSET関数を使うしかありません。一般的に馴染みが薄いOFFSET関数と聞くと皆様の気持ちが折れてしまう事と存じます。実際にテーブル内でセル参照を使用したらどのような動きになるのか?を見て行きたいと思います。数式入力時にオートコレクト機能が働く場合と、働かない場合なかなか具体的な数式設定の話題に入れず恐縮ですが、テーブルに数式を入力するやり方により、オートコレクト機能が働く場合と、働かない場合がありますので、「同じ数式を設定しているのに動きが違う」といった混乱をおこさないように、注意が必要です。「テーブルに数式を入力するのに、どれだけ前提知識が必要なのか?」というお怒りはごもっともでございますが、本テーマではこれが最後になります。1数式でない値が列に設定されているとオートコレクト機能は働かないセルに値が入っていると、オートコレクト機能により上書きされることはありません。値が下記の形(=を付けてた形)で入っている場合は上書きされますので注意が必要です。=100逆な言い方をしますと、「既に数式が入っていたとしても、後から入力した数式で上書きされてしまいます」ので注意が必要です。2ショートメニューからの「集計列の自動作成を停止」はブック全体に反映するオートコレクトのオプションアイコンをクリックすることで表示される左図のショートカットメニューで「集計列の自動作成を停止」を選択すると、すべてのシートでオートコレクトが働かなくなります。これを元に戻すには前段の「テーブルの持つ特徴」見出しの3に記載した手順でオートコレクトのオプション設定を元に戻す必要があります。従いまして、その列にだけ設定されたオートコレクト機能を元に戻すしたい時は、左図ショートカットメニューの一番上の「元に戻す-集計列」を選択してください。3オートコレクトで設定された数式を2行以上削除した後、別の数式を入力した時はオートコレクトは働かない上記の「元に戻す-集計列」のほかに、手動で2ヶ所以上、オートコレクトで設定された数式を削除した場合、空白になったセルに数式を入力してもオートコレクトは働きません。逆な言い方をしますと、一つのセルだけを削除して、そこに新しい数式を入力すると今まで設定されていた数式がすべて上書きされてしまいますので注意が必要です。オートコレクト機能を復活させたい場合は、一度その列のすべての数式を削除してから再度数式を入力してください。テーブルに数式をセル参照と構造化参照で設定するようやく本テーマの本題に入ります。ここではオートコレクト機能を使用して実際の動き方を確認したいと思います。1列1に行番号表示の数式を入力するA1:D11のセル範囲に「テーブル1」という名前のテーブルを設定し、A2セルに連番を表示するための数式を入力します。数式としては下記を使用します。=ROW()-ROW(見出しが設定されている行)見出しが設定されている行の設定は、絶対参照であればROW($A$1)ROW($1:$1)などの形になりますが、構造化参照では下記のような設定になります。=ROW()-ROW(テーブル1[#見出し])A2セルに数式を入力すると、オートコレクト機能により、対象列のすべての行に数式がセットされます。2列1の累計を表示する数式を3パターンで列2から列4に入力するオートコレクトを働かせるためには、一列すべて同じ数式にする必要があります。そのため、累計計算のためには前行を参照する必要がありますが、先頭行の数式だけを変えることはできません。そこで、先頭行では前行となる見出し行は文字列になることを判定条件として、「前行が数値であるか?」をCOUNT関数で確認するためにIF関数を数式に含めています。=IF(COUNT(OFFSET([@列4],-1,0))=0,0,OFFSET([@列4],-1,0))+[@列1]C列とD列の数式の違いは、構造化参照かどうかになりますが、構造化参照の場合は、すべての行の数式がまったく同じ数式になるのに対して、C列のセル参照の場合は、セル参照のところが各行で異なります。結果としては下左図のように列2から列4は同じになります。この状態で4行と5行の間に1行挿入をしてみます。結果は下右図になります。5行目に1行挿入されたことで、オートコレクト機能によりすべての列で数式が自動的にセットされていますが、B列の赤枠のところをよく見ると計算が正しくありません。B6セルの左上隅にエラーインジケーターが表示されていますが、それ以外はExcelは何もメッセージを出してくれません。B5セルの数式=IF(COUNT(B4)=0,0,B4)+A5オートコレクト機能により数式は正しく自動設定されています。B6セルの数式=IF(COUNT(B4)=0,0,B4)+A6カレント行であるA5セルはA6セルに自動調整されていますが、前行参照のB4セルについてはそのまま放置されます。まとめテーブルに数式を設定する際に、いろいろと知ってなければならないことが多くあり長文になってしまったこと、それに対してまとめが短いこと、ご容赦ください。テーブルでの数式を構造化参照にした方が良いかは、意見が分かれるところだと思いますが、ただしテーブル内で前行参照が必要な場合は、セル参照のままで数式に設定する事は避けるべきです。思わぬ計算間違いを引き起こす可能性があります。以上、最後までご一読いただきありがとうございました。「知っていると得するかもしれないExcelの癖」シリーズ第一回、テーマは「日付型とセル結合について」になります。2016バージョン1902を使用し...知っ得かもしれないExcelの癖-その1Excelで行を追加削除してもメンテナンスがほぼ要らない行番号を付ける「知っていると得するかもしれないExcelの癖」シリーズ第三回、テーマは「行番号について」になります。「知っていると得するかもしれないExcelの癖」シリーズ第十回、テーマは「1行おきの行番号」になります。「知っていると得するかもしれないExcelの癖」シリーズ第四回、テーマは「名前の管理について」になります。2016バージョン1902を使用していま...知っ得かもしれないExcelの癖-その1セル左上隅のエラー表示(エラーインジケーター)を有効に使う「知っていると得するかもしれないExcelの癖」シリーズ第八回、テーマは「セル左上のエラー表示」になります。「知っていると得するかもしれないExcelの癖」シリーズ第六回、テーマは「元表を参照する数式」になります。「知っていると得するかもしれないExcelの癖」シリーズ第九回、テーマは「テーブルでの数式利用」になります。2016バージョン1902を使用して確認しています。...2019.03.302019.04.25セル左上隅のエラー表示(エラーインジケーター)を有効に使う「知っていると得するかもしれないExcelの癖」シリーズ第八回、テーマは「セル左上のエラー表示」になります。2016バージョン1902を使用して確認しています。「知っていると得するかもしれないExcelの癖」シリーズ第十回、テーマは「1行おきの行番号」になります。2016バージョン1902を使用して確認しています。...2019.04.012019.04.24人気記事(全カテゴリー全期間)Excel条件付き書式の数式における相対参照と絶対参照の使い分け「知っていると得するかもしれないExcelの癖」シリーズ第十七回、テーマは「条件付き書式の参照設定」になります。2016バージョン1903を使用して確認しています。...2019.04.142019.12.25Cocoonでスニペット付き新着記事と人気記事一覧の作成ver1.9.1Cocoonではver1.8.9.4からver1.9.1にかけて、新着記事ウィジェット・人気記事ウィジェットに加えて新たにナビカードウィジェットの機能が追加されました。この新機能...2019.02.122019.12.25Excelで動的にシート内ハイパーリンクを設定する「知っていると得するかもしれないExcelの癖」シリーズ第十五回、テーマは「ハイパーリンクの設定」になります。2016バージョン1902を使用して確認しています。...2019.04.102019.12.25Cocoonヘッダー(グローバル)ナビのサブメニュー名を2行表示にするサイト開始から1ヵ月強が過ぎ、全体的にこれまで書いた記事の見直しを進めています。AdSenseの認定で「質の低いサイト」と言...2019.03.082019.11.19Cocoonでカテゴリー別にサイトマップを表示するプログラム無料のワードプレス(以下WP)のテーマCocoonで作成者の「わいひら」様が下記のページでショートコードを公開されています。...2019.02.202019.05.17Cocoonアピールエリアの表示スタイルをいじる最後と言いながら1点、標準のアピールエリアのプロクラムを修正せていただきました。これは全くの好みというか、見た目だけの修正なので本来は控えるべき話ではありますが、トップページの部...2019.02.222019.04.18Cocoonでカテゴリーページに一般的なパンくずリストを表示するワードプレス(以下WP)の無料のテーマCocoonでは、設定だけではカテゴリーページ(カテゴリーをクリックした時に表示される当該カテゴリーの一覧のページ)に一般的なパンくずリスト...2019.02.112019.04.18Excelピボットテーブルの自動更新でセルとは別にデータを保存する「知っていると得するかもしれないExcelの癖」シリーズ第二十回、テーマは「ピボットテーブルへのデータ保存」になります。2016バージョン1903を使用して確認して...2019.04.252019.04.27数独向けの条件付き書式とは何ですか?9×9の数独をご存知でしょうか?「数独」もしくは「ナンプレ」という名称で呼ばれていますが以下「数独」と表記いたします。「数独向けの条件付き書式とは何ですか?」の記事を書いてから、2ヵ月半が過ぎてしまいました。「知っていると得するかもしれないExcelの癖」シリーズ第九回、テーマは「テーブルでの数式利用」になります。2016バージョン1902を使用して確認しています。...2019.03.302019.04.25セル左上隅のエラー表示(エラーインジケーター)を有効に使う「知っていると得するかもしれないExcelの癖」シリーズ第八回、テーマは「セル左上のエラー表示」になります。2016バージョン1902を使用して確認しています。「知っていると得するかもしれないExcelの癖」シリーズ第十回、テーマは「1行おきの行番号」になります。2016バージョン1902を使用して確認しています。...2019.04.012019.04.24人気記事(全カテゴリー全期間)Excel条件付き書式の数式における相対参照と絶対参照の使い分け「知っていると得するかもしれないExcelの癖」シリーズ第十七回、テーマは「条件付き書式の参照設定」になります。2016バージョン1903を使用して確認しています。...2019.04.142019.12.25Cocoonでスニペット付き新着記事と人気記事一覧の作成ver1.9.1Cocoonではver1.8.9.4からver1.9.1にかけて、新着記事ウィジェット・人気記事ウィジェットに加えて新たにナビカードウィジェットの機能が追加されました。この新機能...2019.02.122019.12.25Excelで動的にシート内ハイパーリンクを設定する「知っていると得するかもしれないExcelの癖」シリーズ第十五回、テーマは「ハイパーリンクの設定」になります。2016バージョン1902を使用して確認しています。...2019.04.102019.12.25Cocoonヘッダー(グローバル)ナビのサブメニュー名を2行表示にするサイト開始から1ヵ月強が過ぎ、全体的にこれまで書いた記事の見直しを進めています。AdSenseの認定で「質の低いサイト」と言...2019.03.082019.11.19Cocoonでカテゴリー別にサイトマップを表示するプログラム無料のワードプレス(以下WP)のテーマCocoonで作成者の「わいひら」様が下記のページでショートコードを公開されています。...2019.02.202019.05.17Cocoonアピールエリアの表示スタイルをいじる最後と言いながら1点、標準のアピールエリアのプロクラムを修正せていただきました。これは全くの好みというか、見た目だけの修正なので本来は控えるべき話ではありますが、トップページの部...2019.02.222019.04.18Cocoonでカテゴリーページに一般的なパンくずリストを表示するワードプレス(以下WP)の無料のテーマCocoonでは、設定だけではカテゴリーページ(カテゴリーをクリックした時に表示される当該カテゴリーの一覧のページ)に一般的なパンくずリスト...2019.02.112019.04.18Excelピボットテーブルの自動更新でセルとは別にデータを保存する「知っていると得するかもしれないExcelの癖」シリーズ第二十回、テーマは「ピボットテーブルへのデータ保存」になります。2016バージョン1903を使用して確認して...2019.04.252019.04.27数独向けの条件付き書式とは何ですか?9×9の数独をご存知でしょうか?「数独」もしくは「ナンプレ」という名称で呼ばれていますが以下「数独」と表記いたします。「数独向けの条件付き書式とは何ですか?」の記事を書いてから、2ヵ月半が過ぎてしまいました。BIのTips1サイト構築に関する事柄1会社設立に関する事柄1サイト全般22ワードプレスのテーマCocoonについて12ワードプレスでの基本ルールを考える7会社全般4会社紹介1書庫(アーカイブ)2スポンサーリンク。