エクセルで特定の値を含む行を抽出したい

Question

エクセルで特定の値を含む行を別シートに抽出して表示させたいのですがやり方がわかりません。
ほかの方の回答を見ても上手くいかなかったのでどなたか教えて頂きたいです。
やりたいのは複数のＥＴＣカード・車両がある（シート１）ので車両毎に抽出して表にしたい（シート２×車の台数分）のと、
更にそこから各日毎の使用金額を合算したものを計算したもの（シート３）が作りたいのです。

シート１には
Ａ　　／Ｂ　／Ｃ　　／Ｄ　／Ｅ　　　　／Ｆ　　　／Ｇ　／Ｈ　／Ｉ
利用日／時間／利用日／時間／入ったＩＣ／出たＩＣ／料金／車番／カードＮＯ．
4/1　／　　／　　　／　　／東京　　　／西宮　　／8000／1234／
4/1　／　　／　　　／　　／東京　　　／名古屋／5000／5678／
4/1　／　　／　　　／　　／西宮　　　／東京　／8000／1234／
4/2　／　　／　　　／　　／京都東　　／小矢部　／3000／1234／
4/2　／　　／　　　／　　／沼津　　　／和気／5000／5678／
4/3　／　　／　　　／　　／栗東　　　／川越／7000／1234／
4/3　／　　／　　　／　　／高松檀紙／長岡　／9000／5678／
4/3　／　　／　　　／　／敦賀　　　／八王子／4000／5678

シート２には
Ａ　　／Ｂ　／Ｃ　　／Ｄ　／Ｅ　　　　／Ｆ　　　／Ｇ　／Ｈ　／Ｉ
1234の利用状況
利用日／時間／利用日／時間／入ったＩＣ／出たＩＣ／料金／車番／カードＮＯ．
4/1　／　　／　　　／　　／東京　　　／西宮　　／8000／1234／
4/1　／　　／　　　／　　／西宮　　　／東京　／8000／1234／
4/2　／　　／　　　／　　／京都東　　／小矢部　／3000／1234／
4/3　／　　／　　　／　　／栗東　　　／川越／7000／1234／

シート３には
Ａ　　／Ｂ　　／Ｃ　　／
　　／1234　／5678　／
4/1／￥16000／￥5000／
4/2／￥3000　／￥5000／
4/3／￥7000　／￥13000／

このようにシート１にカード情報をコピペすれば自動で計算してくれるように関数を組もうとしたのですが、ほかの方の回答をみて挑戦してもＮ/Ａなどになってできません。

定期的に報告の必要があり、私以外はパソコンの操作が危なっかしく（今までの担当者は電卓で計算してやっていた模様）、また入力や計算の時間が十分に取れないので関数を使えば１度作れば後は時間短縮になるかと思い挑戦しています。

どなたかやり方を教えていただけませんでしょうか。
よろしくお願いします。

tom04 · Accepted Answer

こんばんは！

一例です。
↓の画像では
まず、Sheet2のA1セルに表示したい「車番」を入力し
A3セルに
=IFERROR(INDEX(Sheet1!A$1:A$1000,SMALL(IF(Sheet1!$H$1:$H$1000=$A$1,ROW($A$1:$A$1000)),ROW(A1))),"")

配列数式なので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　A3セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
A3セルのフィルハンドルでI列までコピー　→　各セルの表示形式はココで変更し
A3～I3セルを範囲指定　→　I3セルのフィルハンドルで下へずぃ～～～！っとコピー！

Sheet3のB2セル（配列数式ではありません）に
=IF(COUNTIF(Sheet1!$A:$A,$A2),SUMIFS(Sheet1!$G:$G,Sheet1!$A:$A,$A2,Sheet1!$H:$H,B$1),"")

という数式を入れフィルハンドルで列・行方向にコピー！

これで画像のような感じになります。m(_ _)m

yuji3690 · Answer

抽出する場合に、該当データが複数ある場合（今回なら車番）
その該当データと、それが上から何個目の一致か、をつないで、IDとして表示させるのが一番かと思います。
具体的には、J2=H2&"-"&COUNTIF(J$2:J2,J2)とすることで、J2には1234-1と表示されます。
可能なら左端にこういったIDの列を設けておけば、VLOOKUPで簡単に抽出できるのですが。

シート2にはA1に抽出したい車番が入っているとします。
不可能であれば、どこか別のセルに入力し、そのセルに合わせて変更してください。

表の形式を変更できな場合のために、VLOOKUPではない方法で。
A3=IF(COUNTIF(シート1!$H:$H,$A$1)>ROW()-2,"",INDIRECT("シート1!"&ADDRESS(MATCH($A$1&"-"&ROW()-2,シート1!$J:$J,FALSE),COLUMN()))
シート1のH列でA1と一致するデータの数がそのセルの行番号-2を超えている場合(3行目から表示するとしているので-2して、3行目を1行目とかぞえています)には空白、そうでなければ、シート1の「シート1のJ列で(A1のデータ)"-"(そのセルの行番号-3)に一致するデータの表示されたセルの行番号」行目で、この式の入力されたセルと同じ列のデータを表示する。
という式になっています。
例の場合、1234-1と一致するのがJ2なので、A3にはシート1のA2のデータが表示されるわけです。
この式をI列まで及び、一致するデータの数以上の行(最大でシート1のデータと同じ数まで可能性としてはあります)までコピーしてください。

シート3は複数の条件に一致するデータの合計、ということですので、SUMIFSを用いれば良いでしょう。
B2=SUMIFS(シート1!$G:$G,シート1!$A:$A,$A2,シート1!$H:$H,B$1)
シート1のG列の内、「シート1のA列がA列のデータに一致し、シート1のH列が1行目のデータと一致するもの」を合計する。
という内容になっています。
これを必要な範囲にコピーしてください。

銀鱗 · Answer

(´・ω・`)
無理を言ってはいけません。
一朝一夕でできるものじゃない。
少しずつ覚えるしかありませんよ。

・・・本題・・・
まずは、やりたいことを正確に伝える努力をしましょう。
それにより問題点を把握しやすくなります。
（分かりにくいんだな。質問文の表現は）
…
Sheet1にはすべてのETCカード（車両台数分）の使用履歴が日付順に並んでいる。
Sheet3以降のシートに車両毎の使用履歴を表示させ、
Sheet2には日付と車両毎の使用料金の一覧表を作りたい。
…
のような書き方をしてくれたほうが分かりやすい。
（なんかデジャブな気分だ。どこかで同じことを指摘されませんでしたか？）

・・・
自分なら、
「並べ替え」と「フィルタ」機能、さらに「SUBTOTAL関数」を使って集計し、
併せて「ピボットテーブル」でやっつける。

言っちゃなんだけど、Excelはツールなんです。
便利な計算機です。
帳票出力にも対応している計算機と思えば、関数やらマクロやら使わなくても仕事はできます。

ですので、今までの担当者さん同様に電卓を使って数字を書き込むのもアリです。
便利に使いたいのであれば、自身が理解をして使うようにしましょう。
（作成したBookには使い方の説明や改善案を示したシートを別に作成するなどして第三者が見ても分かるようにしておきましょう）

・・・妥協点・・・
Sheet1を丸ごと参照する形でSheet2以降を車両台数分作りましょう。
そしてSheet毎に車両でフィルタを掛ける。
こうすれば何も考えず一つの車両の履歴を作成できます。

集計はピボットテーブルをお勧めしますが、ピボットテーブルの使い方は自身で調べてください。
使い方の説明をすると1冊の本が書けるくらいですので、実際の説明は省略します。
この機能はExcelで一番もっとも応用ができ便利に使える機能ですので是非身に着けてください。

・・・余談・・・
このように関数とか使わずにできちゃうもんなんですよ。
まずは目の前にあるExcelというツールで何ができるのかを「確認」して、「これは使える！」というものを見つけてください。
あとはそれを使えるように努力するだけで、いつのまにかExcelを使いこなせるようになります。

勉強するつもりがないのでしたら上司に相談して、ソフトウェア会社に集計用のExcelファイルを作ってもらうようにしましょう。
これ、自身にも会社にもプラスですので…マジ提案です。

エクセルで特定の値を含む行を抽出したい

抽出する場合に、該当データが複数ある場合（今回なら車番）

(´・ω・`)

こんばんは！

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング