Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Excel 2010)
テーブル機能で行追加時、A列最終行-1行目限定で書式と関数参照範囲が崩れる
投稿日時: 18/05/08 01:21:40
投稿者: tokuto

お世話になっております。
 
Excel標準のテーブル機能を使用しています。
 
最終行の直下にデータを記入すると、テーブルが自動的にオートコレクト機能により表の書式や関数が適用されます。
 
とても便利なのですが、問題発生2つ
 
 
B列に値があれば
A列に連番を関数で振っています。
 
=SUBTOTAL(103,$B$2:B2)
 
(1)数式がずれる
 
99行目に=SUBTOTAL(103,$B$2:B99) A列には連番98
 
100行目をテーブル拡張すると当然A列の数式も引き継がれますが・・・実際には
 
99行目のA列の数式がなぜか→=SUBTOTAL(103,$B$2:B100)参照範囲が1行下へずれ、連番99と表示されてしまいます。
 
参考として
100行目のA列の数式を見ると→=SUBTOTAL(103,$B$2:B100)数式自体は良いのですが
連番表示は、99行目のA列の数式がおかしいので連番99と表示されます。
 
(2)新規追加するともともと最終行であったA列セルに書式設定が反映されない。
99行目から新たに100行目を新規追加するとセルA99の書式が崩れる
101行目を新規追加するとセルA100の書式が崩れる
 
といった感じに新規追加行は、書式が引き継がれますが、A列の最終行-1行目限定で崩れていきます。
 
 
 
 
すみません、質問が難しいのですが、解決策をお願い致します。

回答
投稿日時: 18/05/08 07:47:09
投稿者: Suzu

私も同じ事が起きています。
 
SUBTOTALを使っているという事は、オートフィルターを使用していますよね。
画面上には、結局必要な部分のみを抽出して表示すれば良い。
 
なので、テーブルの自動拡張機能を使わずに、予め下方向に
関数や書式を設定したセルを用意しておいて対応しています。
 
 
SUBTOTAL関数は、オートフィルターとセットで使う物なのですが
どうも、オートフィルターと相性が良くないので。。
 
http://www.moug.net/faq/viewtopic.php?t=76944
 
 

引用:
テーブルが自動的にオートコレクト機能により表の書式や関数が適用されます。

オートコレクト は意図している機能と違う機能だと思いますよ。

回答
投稿日時: 18/05/08 10:37:01
投稿者: ゆーたん

>99行目に=SUBTOTAL(103,$B$2:B99)
この、SUBTOTALで範囲が「B2:B99」なのがいかにも集計行ぽいので、
「集計行でしょ?」って気を利かせてくれているのだと思います。(^_^;)
すこし誤解されにくいような数式に変更したらいいのかも。
 
A2 =N(A1)+SUBTOTAL(103,B2)
 
というかんじでどうかしら?
 
※オートコレクトオプションでオートコレクトの中に入力オートフォーマットタブがあります。
用語がややこしい。(T_T)

投稿日時: 18/05/08 11:16:25
投稿者: tokuto

Suzu さん
ゆーたん さん
 
ありがとうございます。やはりそうですか、Microsoftの意図する用途と私の欲張り感のギャップなのですね。
 
なお、=N(A1)+SUBTOTAL(103,B2) の関数すごいアイディアです。
 
恐る恐る、実行してみると・・・キタ━━(゚∀゚)━━ !!!!ー
 
「表が崩れず&参照先もズレずにバッチリ動作しております」
 
これ、同じ悩みの人に拡散したいくらいですよこれ、テーブル機能との相性バッチリです。
 
解決です(^o^)
ありがとうございました。