今使用している下記のような表があるのですが、同じシート内に上下に合計表があります。
SUBTOTALを使ってオートフィルタでB列が a またはb を選んだ時でも
A列が連番になるよう、C列の合計が絞り込み後もに計算させるように簡単ではありますが、こんな表があるのですが、
今後上下の表の一部データを入れ替えする作業もあり、普通にコピーをしていれかえさせたいのですが、うまくいかない為、
$絶対参照を変えたりと、いろいろ試しているのですが、うまくいきません。
知恵をお貸し頂きたく、質問させて頂きました。
可能であれば教えて頂きたいのですが、
どうかよろしくお願いします。
-------------------------------------------------------
A B C A1=SUBTOTAL(3,$B$1:B1)
1 1 a 1
2 2 b 1 A2~A4=コピー
3 3 a 1
4 4 b 1
5 合計 aまたはb C5=SUBTOTAL(3,C2:C4)
6--------------------------------------------------
7 1 a 1 A7=SUBTOTAL(3,$B$7:B7)
8 2 b 1 A8~A10=コピー
9 3 a 1
10 4 b 1
11 合計 aまたはb C11=SUBTOTAL(3,$B$7:B7)
-------------------------------------------
No.1ベストアンサー
- 回答日時:
的外れなら申し訳ありません。
以下、私の所感です。1行目にはオートフィルタのスイッチが出現する為、b だけを選んでも出現した侭になります。
もし b を選ぶ時には非常時にしたいのであれば、1行下げる必要があると思います。
C5及びC11で求めたい集計はデータ個数の合計ですか? データの足し算ですか?
もし足し算なら、SUBTOTALの集計方法は 3 ではなく 9 又は 109 だと思います。
絞込みはオートフィルタだけですか?
もし非表示も併用するなら、SUBTOTALの集計方法は 3 ではなく103 (足し算なら 109) だと思います。
C5の集計範囲が C2:C4 になっていますが、C1は集計対象外ですか?
1行目~4行目までが集計対象なら、範囲指定は C1:C4 だと思います。
C11は $B$7:B7 になっていますが、集計対象はB7だけですか?
もし7行目~10行目までが集計対象なら、範囲指定は $B$7:B10 だと思います。
この回答への補足
お忙しい中回答ありがとうございます。
質問と表がわかりづらくてすみません。
1行目は、オートフィルタがつく為、文字を左詰めにしています。
C5及びC11で求めたい集計はデータ個数の合計です。
絞り込みは、オートフィルタのみです。他に方法がわからないもので。。。
数式もまちがっていました。現在下記のようなものが入っています。C5=SUBTOTAL(3,C1:C4)
C11=SUBTOTAL(3,$C$7:C10)
集計方法が9とはわかりませんでした。
合計は上記のもので、なんとか対応できるのかなぁとおもっていましたが、A列がコピーセルの挿入などしてしまうと、1~の連番が崩れてしまうのです。
No.2
- 回答日時:
ど~も。
なかなか解りづらい質問ではありますが、
要するに、A列の連番を表示するのに、コピーしても崩れないようにしたいのですよね。
違うようならごめんなさい。
A列の連番は、縦に表が2つなので、
=MOD(ROW(),7)
で表示できます。
MOD関数は、数値を除数で割ったときの剰余を返します。
ROW関数は、行番号を取得します。
もし、2つめの表の行番号が「101」なら、
除数を「100」に設定すると、101行目が「1」になります。
合計以外のB列,C列は数式ではないですよね。
もし数式を入力する場合なら、補足しておいてください。
どうでしょう。
この回答への補足
回答ありがとうございます。
=MOD(ROW(),7)
こんな方法があるとは、とても勉強になります。
説明が足りませんでしたが。コピーしたセルの挿入の
動作をした時は可能なのでしょうか。。。
No.3
- 回答日時:
A1に =SUBTOTAL(3,INDEX(B:B,1):INDEX(B:B,ROW()))
A2~A4へコピィ
C5は =SUBTOTAL(9,INDEX(C:C,1):INDEX(C:C,ROW()-1))
A7に =SUBTOTAL(3,INDEX(B:B,7):INDEX(B:B,ROW()))
A8~A10へコピィ
C11は =SUBTOTAL(9,INDEX(C:C,7):INDEX(C:C,ROW()-1))
では如何でしょうか。的外れでしたらスルーしてください。
C5は
No.4
- 回答日時:
No.2補足への回答です。
「コピーしたセルの挿入」とは、下の表が下にずれると言うことでしょうか?
それなら、下表の位置を取得するためにB列などに(セルB6)にタイトル("第2表")などを入れ、
セルD1に[=MATCH("第2表",B:B)]を入力。下表の位置が取得できる。
A列の数式は、[=MOD(ROW(),$D$1)]と入力すれば、表がずれても大丈夫です。
複雑になりますけどね。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) SUBTOTAL SUMIF?? 2 2023/03/16 11:25
- Excel(エクセル) Excel(エクセル)でフィルター抽出後、非表示の行を計算しないで、合計を算出する方法 【内容】 添 4 2023/01/30 17:17
- Excel(エクセル) アウトラインの小計のやり方 1 2023/03/20 11:51
- Excel(エクセル) 特定文字(数字)で行挿入、挿入された行で合計したい 2 2023/03/13 14:30
- Excel(エクセル) 見えているセルの数字だけの合計を計算させたい 4 2022/09/11 17:23
- Excel(エクセル) [オートフィルター]機能について 3 2023/02/04 14:32
- Excel(エクセル) シート参照を含む数式を連続コピー 3 2022/12/10 11:42
- Excel(エクセル) 関数を用いて表示したセルの内容を、見えている形でコピーする方法 2 2022/09/14 16:36
- Excel(エクセル) Excel シート複数 金額日計表と日付 簡単にシートコピーしたら前日の残高と日付を変更させたい 1 2022/07/15 22:10
- Excel(エクセル) エクセルのマクロ作成について教えてください 5 2023/02/20 00:39
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセル初心者です 関数の入れ...
-
【関数】先頭だけにある、半角...
-
エクセル 白黒印刷で白線を印刷...
-
Excelのチェックボックスの使い...
-
【関数】適切な文字数の数字を...
-
Excelのpivotについて質問です
-
Excel ピボットテーブルで日付...
-
LOOKUP関数を使えばいいのでし...
-
エクセル関数を教えてください
-
エクセルのセルに同じ大きさの...
-
UNIQUE関数が使えないバージョ...
-
excelの不要な行の削除ができな...
-
エクセルで「-0.0」と表示さ...
-
時間によってファイル名が変わ...
-
WPS OFFICEでの縦書きについて
-
エクセルの関数について教えて...
-
Aというブックの1というシート...
-
【マクロ】シート名を取得する...
-
VBA Private Sub Worksheet_Cha...
-
VBA、Excelのworkbook.open に...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
Excel 2019 のピボットテーブル...
-
[関数得意な方]教えて下さい・...
-
Excelにてある膨大なデータを管...
-
[関数について]わかる方教えて...
-
Excel初心者です。 詳しい方、...
-
excelの不要な行の削除ができな...
-
エクセル関数に詳しい方教えて...
-
INDIRECTを使わず excelで複数...
-
[オートフィルタ]で抽出された...
-
エクセルの神よ、ご回答を! エ...
-
エクセル関数に詳しい方、教え...
-
各ページの1番上の表示について
-
Excelで写真のような表を作った...
-
エクセルで不等号記号(≠)が上に...
-
数学 Tan(θ)-1/Cos(θ)について...
-
Excel 2019 は、SPILL機能があ...
-
Excelで全角を半角にしたいので...
-
条件付き書式を教えてください
-
Excel フィルターを掛けた状態...
-
[オートフィルタ]の適用範囲の...
おすすめ情報