エクセルの機能

質問

エクセルの機能について質問です。
あるシステムからエクセルにインポートしたデータにサブトータルの行が数値としてインポートされているので、並び替えをする際にサブトータルの行がうまくグループにくっついてくれません。
うまく説明できないのですが、例えば
リンゴ　 　10円　グループ1
オレンジ　10円　グループ1
----------------------
　　　　　　20円
ケーキ　　10円　グループ2
お菓子　　10円　グループ2
-----------------------
20円
こんな感じにデータが並んでいる場合に、例えばグループ名の昇順に並べたい場合、
サブトータルもそのグループに付随するものとして並び替える方法を教えてください。

またインポートした際にサブトータルが数値としてインポートされています。
一番下の行のトータルの行を作ってサブトータルの合計を出したいのですが、
一括でサブトータルの行を数値ではなく数式に変更することはできますか？
分かりづらい質問で申し訳ありませんがよろしくお願いいたします。

回答No.1

keithin · ベストアンサー

＞サブトータルもそのグループに付随するものとして並び替える方法

小計行にもグループ番号を同じように記入するしか、方法はありません。

手順：
グループ列がＣ列だとすると
Ｃ列を上から下まで選択する
Ｃｔｒｌ＋Ｇを押す
現れたダイアログでセル選択を押す
現れたダイアログで空白セルをマークしてＯＫする

添付図：
最初の小計行（Ｃ４）を先頭に、いま空欄になっている各セルが飛び飛びで選択される
飛び飛び選択状態を崩さないよう注意しながら、
今はたまたまＣ４セルが先頭になっているので、
数式バーに
=C3
と今のアクティブのセルの「一つ上のセルを参照する式」を記入、
必ず「コントロールキーを押しながらＥｎｔｅｒ」で入力する

とグループ列が埋まるので、そのままグループ列で全体を並べ替えます。

お礼

ありがとうございます。エクセルの機能を使いこなせていないので一つの作業にすごく時間がかかってしまって。参考にさせていただきます。

回答No.2

KURUMITO

並べ替えの方法についてはkeithinさんの方法でできることでしょう。
ところでサブトータルの数値については式が無い状態で数値のみであるとのことですね。
式を入れるとしたら次のようにしてはどうでしょう。
例えばB列にその小計の金額がB５セル、B１０セル、B２０セル、B５０セルに有るとしたらそれらのセルを上から順番にCtrlキーを押しながら選択します。最後のセルがB５０セルとして数式バーには次の式を入力して、式を確定する段階ではCtrlキーを押しながらEnterキーを押します。

=SUBTOTAL(9,INDIRECT("B"&MATCH(C50,C:C,0)):B49)

これで選択したセルにはそれぞれのセルに適した式の形となって同時に入力が行われます。
なお、上の式ではMATCH関数で例えばC列でのグループ名をよりどころにしてそのグループの先頭の行を求めています。
総計についてはB列の最下行にあるとします。サブトータルのある行にA列のセルには主計の文字が有るとしたら次のような式にすることで総計を求めることができますね。

=SUMIF(A2:A列の総計表示の一つ前までの行,"<>小計",B2:B列の総計表示の一つ前までの行)

小計が無く空白となっている場合には次の式でもよいでしょう。

=SUMIF(A2:A列の総計表示の一つ前までの行,"<>",B2:B列の総計表示の一つ前までの行)

勿論B列で小計のセルに数式が入力されている場合には次の式でもよいでしょう。

=SUBTOTAL(9,B2:B列の総計表示の一つ前までの行）

お礼

ありがとうございます。エクセルの機能を使いこなせていないので一つの作業にすごく時間がかかってしまって。参考にさせていただきます。