「日々記録している表の中から、特定の項目だけを抽出した表を別シートに出力したい。入力すれば即反映されるのが望ましい」といった要望を受けました。
そうですね、その都度、集計するならフィルターオプションを利用するのが手軽ですが、自動で反映させるなら別の方法が良いですね。方法としては、作業列を用意したり、配列数式を利用したりと、マクロを利用したりと色んなやり方がありますが、ここでは比較的すっきりした形で使える方法を紹介しておきます。
例として、下のSheet1にあるような表から、鉛筆に該当する行だけをSheet2に抽出する形で説明します。
条件に合う行を順番に取り出す
まずSheet1が次のように表になっているとします。
最初に、Sheet2に項目名を用意します。
次にA2セルに次の数式を入力します。
=INDEX(Sheet1!A$1:A$20,SMALL(INDEX((Sheet1!$B$2:$B$20<>”鉛筆”)*1000+ROW(Sheet1!$B$2:$B$20),),ROW(A1)))
INDEX 指定範囲の指定された行列にある値を求める
SMALL 指定範囲で小さい値から指定順位の値を取り出す
ROW 行番号を求める
1行目が「鉛筆」なら「1」、「鉛筆」じゃなければ「1001」とかにして順番を後ろにし、少ない番号から表示（つまり「鉛筆」のある行だけを上から順番に表示）する仕組みです。
これを右方向と下方向へオートフィルします。
これで、鉛筆がある行だけが抽出されました。ただ、鉛筆が記載されている行以上の行は「#REF!」になってしまうので、これを修正します。
該当する行数以上は空白にする
A2セルの数式を下記に修正します。赤字部分を追加しています。
=IF(ROW(A1)>COUNTIF(Sheet1!$B$2:$B$20,”鉛筆”),””,INDEX(Sheet1!A$1:A$20,SMALL(INDEX((Sheet1!$B$2:$B$20<>”鉛筆”)*1000+ROW(Sheet1!$B$2:$B$20),),ROW(A1))))
IF 条件により処理を分ける
COUNTIF 条件に合う個数を数える
鉛筆のある行数以上の行は空白にするように指定しています。これをオートフィルすれば次のようになります。
これで、見た目もすっきりしました。Sheet1の表を修正したり追加したりすれば、自動的にSheet2の表にも反映されるはずです。
ちなみに、別のやり方として「配列数式」を使う方法は、下記を参考にしてみて下さい。
コメント
始めまして。私はとある会社員で、総務を担当しています、藤中と申します。エクセルはそこそこ理解できるつもりでいます。マクロは分かりません。
会社がまだ、設立から間がなくて、会計管理システムが導入されておりません。
なので、エクセルで色んなものの管理をしておりますが・・・
先日、ある問題にぶつかりました。いろいろ試してみているうちに、あなたのページに、まさに私が今やろうとしていることがありましたので、早速試してみましたが、うまく行きまません。それで、「特定の項目だけを抽出して別シートに表示する」のサンプルをそのまま関数なども入力してみましたが、これまたうまく行きませんでした。私がどこかで間違っているのだと思います。
それで、私がやるべきことを簡単な表にしています。(実物は、取引会社名や取引額などが表示されているので、そのままでは色々問題がありますので)
この表から、別ページに、「抽出結果表」のように表示する方法をご教授願えませんでしょうか。どうぞよろしくお願いします。
「セルD1と同じものを表の中から抽出して、抽出結果表のように表示したいのです。
ただし、ピボットテーブルや、フィルターは使用せずに、関数で表示させたいのです。
なんとかなりませんでしょうか。どうぞよろしくお願いします。
A B C D
1 No 社名 金額 るるる
2 1 PPP 50
3 1 せせせ 19000
4 1 るるる 12000
5 2 PPP 80
7 2 せせせ 20000
8 1 JJJ 7890
9 2 るるる 26600
10 3 PPP 900
11 1 KKK 15900
12 3 るるる 55555
抽出結果表
1 No 社名 金額
2 1 るるる 12000
3 2 るるる 26600
4 3 るるる 55555
5
6
7
・
・
・
お問い合わせをありがとうございます。早速シート1に該当の表を入れ、シート2に結果を入れてみましたが、問題なく出来ましたね。
具体的には、シート2のA2セルに下記を入れました。
=INDEX(Sheet1!A$1:A$20,SMALL(INDEX((Sheet1!$B$2:$B$20<>Sheet1!$D$1)*1000+ROW(Sheet1!$B$2:$B$20),),ROW(A1)))
記事内容とほぼ同じで、シート1のD1に求めたい言葉（るるる）があるので、シート1のD1を参照するようにしているくらいですね。後は右方向と下方向にオートフィルしているだけです。こちらの式はExcel2002以降であれば使えると思うので、恐らくバージョンも問題なさそうなんですけれど……。どうでしょうか。
早速のご返答ありがとうございます。こんなに早く返答が来るとは予想しておりませんでした。びっくりです。
やってみているのですが、どうもうまく行きません。もう少し粘ってみます。
余談ですが、私も若いころ、福岡から職場の転勤で、広島の戸坂に５年ほど住んでおりました。福岡に戻って３０年以上経過しましたから、もうほとんど記憶がかすんでいます。(笑)
戸坂は市内中心部に近くていいところですよね。ちょっと渋滞しますが。
それで、サンプルファイルをメールでお送りしましたので、試してみて下さい。
やりたい内容に合っているかは分かりませんけれど。
至れり尽くせりのご対応ありがとうございます。サンプルを見させて頂きました。
美しいです。なんだか、積年の便秘が解消されたような気分です。
また、困ったことに出くわしたら、相談させて下さい。本当にありがとうございました。
藤中
はじめまして。
Sheet1の「鉛筆」の部分に例えば「ロケット鉛筆」等を入力し，Sheet2の関数の「鉛筆」の部分を「*鉛筆*」として，「鉛筆」を含む文字列が入力されているセルを含む行を抽出するにはどうしたらよいのでしょうか。
突然の質問で恐縮ですが，ご教示いただければ幸甚でございます。
とても助かりました。
もう一つ教えてほしいです。
販売価格は”０”としたら、別シートにセルをブランクで表示したい場合どうすればいいでしょうか。
失礼しました。
販売価格はブランクの場合には、抽出して別シートに同じように金額をブランクで表示する場合どうしたらよいでしょうか。