Home » Excel(エクセル)の使い方 » データベース・検索 » フィルタ実行時に連番を表示させたい−SUBTOTAL関数

フィルタ実行時に連番を表示させたい−SUBTOTAL関数

対象:Excel97,Excel2000,Excel2002,Excel2003

Excelは元来表計算ソフトですが、データベース機能も充実しています。特にデータの絞込みをするためのフィルタ機能は、データベースソフトであるAccessのクエリやフィルタ機能より便利に感じることも少なくありません。

そのフィルタ機能を使いこなしている方から、抽出されたデータに連番が振られるような列を先頭に作っておくにはどうすればいいのか、という質問をいただくことがあります。

[スポンサード リンク]

以下の設定を行うとA列に連番が表示され、フィルタを実行すると表示されているデータの連番が振られるようになります。


▼操作手順:フィルタ実行時に連番を表示させる列を作成する
(1行目がフィールド名、2行目からデータが表示されているリストのA列に連番を表示させる例、B列には必ずデータが入力されているものとします)
A2セルに「=SUBTOTAL(3,B$2:B2)」と入力後[Enter]キーを押す
 ↓
データの入力されている行までA2セルをコピー

SUBTOTAL関数はリストの集計値を取得する関数で、第1引数でどんな値を取得したいのか集計方法を指定します。こここではデータの個数を数えたいので「3」を指定しています。合計・平均・最大値・最小値なども求めることができます。
ここで指定した「3」以外に、どんな値を指定したときにどんな集計値を得ることができるのかは、SUBTOTAL関数のヘルプに記載されていますから、是非一度ご覧いただくことをおすすめします。

第2引数で集計する範囲を指定するのですが、ここでは「B$2:B2」という複合参照を利用した指定をしたので、A列で下に向かってコピーしたときに「B$2:B3」「B$2:B4」と変化します。つまりB列の先頭行からのデータの個数を取得することになり、結果として連続した数値が表示されるわけです。

そしてSUBTOTAL関数は、フィルタリングをしたときに表示されていない行は無視して集計を行ってくれるため、フィルタをかけると表示されている行のデータ個数を数え直し、連番を振りなおしてくれるわけです。

但しちょっと問題があって、この連番を表示する列を追加すると、リストの一番下のレコードが抽出条件に合致していなくても表示されてしまうという不具合があります。解決策をご存知の方は是非教えてください。

■不具合の回避方法■
このネタをご覧いただいた方から、不具合の回避方法を教えていただきました。ありがとうござます。
「=SUBTOTAL(3,B$2:B2)」と入力してコピーする方法をここではご紹介しているわけですが、この数式をちょっとだけ修正すればいいようです。
数式を、
「=IF(B2="","",SUBTOTAL(3,B$2:B2))」
としてデータのある行にコピーをします。すると不具合が発生しなくなります。
なぜこれで回避できるのか考えてみたのですが、残念ながら理由はわかりません。
理由・原因がわからないのは気持ち悪いのですが、そもそも不具合がなぜ発生しているのかもよくわからない(バグと言っていいものじゃないかと考えてます)ので、IF関数をかませれば回避はできるということで納得し、ご紹介させていただきます。
ご連絡いただきどうもありがとうございました。
(2005.05.19追記)

関連語句
オートフィルタで連番をふる, 可視セルだけに連番を振る, 連続数字, 連続番号

Home » Excel(エクセル)の使い方 » データベース・検索 » フィルタ実行時に連番を表示させたい−SUBTOTAL関数

[スポンサード リンク]
[スポンサード リンク]

TrackBack:5

TrackBack URL
抽出データの平均値が知りたい−SUBTOTAL関数 from インストラクターのネタ帳
対象:Excel Excelで算術平均・相和平均を求める関数は、AVERAGE(アヴェレージ)関数です。 SUM関数と使い方は同じで、SUM関数が引数の合計を返すのに対して、AVERAGE関数では引数の平均(算術平均・相和平均)を返します。 実務でオートフィルタなどを使ったデータの抽...
フィルタで何件抽出されたのか−SUBTOTAL関数 from インストラクターのネタ帳
対象:Excel Excelのフィルタ機能についてこれまで何度かご紹介してきました。 ・オートフィルタのオプション設定を簡単に ・オートフィルタをより使いやすく ・*を抽出したい ・フィルタ実行時に連番を表示させたい ・抽出データの平均値が知りたい フィルタ機能が便利...
SUBTOTAL関数の引数一覧 from インストラクターのネタ帳
対象:Excel 97,2000,2002,2003 これまでSUBTOTAL関数について何度かご紹介してきました。 ・フィルタ実行時に連番を表示させたい ・フィルタで何件抽出されたのか ・抽出データの平均値が知りたい 第1引数に集計方法を数字で指定するわけですが、何を指定すればいいのか...
連番を振る関数は? from インストラクターのネタ帳
対象:Excel97,Excel2000,Excel2002,Excel2003 「連番を振る関数は何ですか?」 Excelで数式・関数を利用しはじめた...
×サブトータル関数、○SUBTOTAL関数 from インストラクターのネタ帳
対象:Excel97,Excel2000,Excel2002,Excel2003,Excel2007 ワークシート関数のうち、カタカナで検索されやすいも...

Home » Excel(エクセル)の使い方 » データベース・検索 » フィルタ実行時に連番を表示させたい−SUBTOTAL関数

「データベース・検索」の記事一覧

検索

最近じっくりと読まれている記事

.