エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

A 回答 (2件)

同じ質問が結構よく出てますが、そんなに初歩的でもありません


別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい
    • good
    • 121
この回答へのお礼

ありがとうございます!教えていただいた方法で見事再現ができました。
(ちなみに同一シート内で抽出したかったので、
A1部分を相当するセル番号に置き換えたら無事にできました)

けっこう複雑な式なんですね・・・。
昨日からずっと悩んでいたので助かりました。
ありがとうございました。

お礼日時:2007/07/26 14:30

> 抽出するだけならオートフィルターでもできますが、


> 抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

では、オートフィルターで抽出したものをコピーし、フィルターをもとにもどしてから別の場所に貼り付けたら?
マクロでも可能ですが。
    • good
    • 16
この回答へのお礼

それでは手作業になってしまいますよね。
元の表を更新するたび、抽出結果の方も自動的に反映されるようにしたいのです。
マクロを使えば可能なのでしょうか?

お礼日時:2007/07/26 13:40

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qエクセル プルダウンの作り方

お世話になります。
エクセルでプルダウンの作り方を教えていただけませんでしょうか?
住所を▼のボタンでクリックしたら【北海道,青森,秋田,岩手・・・】などの選択ができるようにしたいのです。
宜しくお願いします。

Aベストアンサー

メニューから「データ」、「入力規則」、「設定」で「入力値の種類」を「リスト」を選択します。
そうすると「元の値」という表示がでますので、そこで前もって作っておいたリストの範囲を指定します。
多くないのでしたら、そのままそこにカンマで区切って入力しても出来ます。

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。
似たような質問はたくさんあったのですが、初心者の為なかなか理解できません。

シート1が下のような表の時

   <A列> <B列> <C列> <D列> <E列> <F列> <G列>
15行 まぐろ   100   ㎏  20000   円    2    匹 
16行 さんま   100   ㎏  50000   円   1000   匹
17行 くじら    0   ㎏    0    円    0    匹
18行 まだい   100   ㎏  80000   円    100   匹

この表のF列の数字が0より大きい場合という条件で、A列、F列、G列をシート2に抜き出し
下のような表にしたいです。

   <A列> <B列> <C列>
16行 まぐろ   2    匹     
17行 さんま  1000   匹   
18行 まだい  100    匹

似たような質問で、関数があったのですが理解できず応用ができなかったので、この通りになる
関数を教えていただけると助かります。

どうぞ、よろしくお願いいたします。

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。
似たような質問はたくさんあったのですが、初心者の為なかなか理解できません。

シート1が下のような表の時

   <A列> <B列> <C列> <D列> <E列> <F列> <G列>
15行 まぐろ   100   ㎏  20000   円    2    匹 
16行 さんま   100   ㎏  50000   円   1000   匹
17行 くじら    0   ㎏    0    円    0    匹
18行 まだい...続きを読む

Aベストアンサー

関数で、少し説明付き。(計算速度の速い二分探索にすることも可能ですが、初心者ということでわかりやすい線形探索で)
1. H1セルに 「条件」
2. H2セルに
 =SUM(F2>0,INDEX(H:H,ROW()-1))
 下へオートフィル
○条件が一致した場合に、一つ上のセルに加算する
○INDEX関数やROW関数を使っているのは並べ替えに対応するため
○and条件やor条件が発生した場合、AND関数やOR関数で対応できる

3.Sheet2!A1セルに 連番
4.Sheet2!A2セルより下へ1から連番を入力する
5.Sheet2!B2セルに
 =IF(MAX(Sheet1!H:H)<A2,"",MATCH(A2,Sheet1!H:H,0))
 下へオートフィル
○上から順次検索して、見つかった行を返す
○不要な検索はしないよう処理
6.Sheet2!C2セルに
 =IF($B2="","",INDEX(Sheet1!A:A,$B2))
 右へ、下へオートフィル
○目的の値を持ってくる(Sheet2!J列は参考まで)

作業列を使わないで行うこともできますが、理解できないと思うのでパス

関数で、少し説明付き。(計算速度の速い二分探索にすることも可能ですが、初心者ということでわかりやすい線形探索で)
1. H1セルに 「条件」
2. H2セルに
 =SUM(F2>0,INDEX(H:H,ROW()-1))
 下へオートフィル
○条件が一致した場合に、一つ上のセルに加算する
○INDEX関数やROW関数を使っているのは並べ替えに対応するため
○and条件やor条件が発生した場合、AND関数やOR関数で対応できる

3.Sheet2!A1セルに 連番
4.Sheet2!A2セルより下へ1から連番を入力する
5.Sheet2!B2セルに
 =IF(MAX(Sheet1!H:H)<A2,""...続きを読む

Qエクセルでプルダウンメニューの作り方

  エクセルの画面で、よく三角形を逆さまにした形をクリックするといくつかメニューが出てき、どれかを選べるようになっていますが、その作り方を教えてください。
 会社で人事を担当していますが、三角形(プルダウンボタン)をクリックすると社員氏名一覧が表示され、そこから選択できるようにしたいのです。
 しばらく自力でいろいろやってみましたが、さっぱり見当がつかず、どうやればいいのか分かりませんでした。よろしくお願いします。

Aベストアンサー

こんばんは!
当方使用のExcel2003での一例です!

↓の画像のようにSheet2に名簿表を作成しておきます。
画像ではSheet2のA2セル以降を範囲指定 → 名前ボックスに仮に「名簿」と入力しOK
これで範囲指定したセルが「名簿」と名前定義されましたので、

Sheet1のリスト表示させたいセルを範囲指定 → メニュー → データ → 入力規則
→ リスト → 「元の値」の欄に
=名簿
としてOK

これでSheet1のセルをアクティブにすると右側に下向き▼が表示されますので、そこをクリック!
これで希望に近い形にならないでしょうか?
Excel2007の場合は↓のURLが参考になるかもしれません。

http://www.eurus.dti.ne.jp/~yoneyama/Excel2007/excel2007-ny_kis2.html

尚、同一Sheetに「名簿表」を作成する場合は名前定義する必要はなくて
「元の値」の右側の四角をクリックし、リスト表示したいセルをそのまま範囲指定すればOKです。

以上、お役に立てば良いのですが・・・m(_ _)m

こんばんは!
当方使用のExcel2003での一例です!

↓の画像のようにSheet2に名簿表を作成しておきます。
画像ではSheet2のA2セル以降を範囲指定 → 名前ボックスに仮に「名簿」と入力しOK
これで範囲指定したセルが「名簿」と名前定義されましたので、

Sheet1のリスト表示させたいセルを範囲指定 → メニュー → データ → 入力規則
→ リスト → 「元の値」の欄に
=名簿
としてOK

これでSheet1のセルをアクティブにすると右側に下向き▼が表示されますので、そこをクリック!
これで希望に近い形にならない...続きを読む

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、
Sheet1
<A列> <B列> <C列> <D列>
7/1 A店 りんご 100円
7/1 C店 みかん 100円
7/2 A店 ぶどう 200円
7/2 B店 すいか 300円
7/3 C店 みかん 150円
:
:
このような表があって、そのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

Sheet2
A店             B店            C店
7/1 りんご 100円     7/2 すいか 300円    7/1 みかん 100円
7/2 ぶどう 200円                   7/3 みかん 150円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

一番簡単なのはA列の左側にIDようの列を挿入し、
A1=C1&"-"&COUNTIF(C$1:C1,C1)
(Aは新しく挿入した列で、Cは元々のBつまり店名の入っている列です)
これをデータの数だけ下にコピーします。
「店名-その行までにその店名が出てきた回数」という表示になります。

VLOOKUPで抽出したいので左端に入れましたが、どうしても左端が無理であるなら、
E列以降でもかまわないです。
その場合VLOOKUPではなく、MATCHによって一致する行を検索し、
その行番号を用いてINDIRECTで該当するセルのデータを抽出することになり、
式の手間は増えます。

仮にAA1に「A店」とし、2行目以降に該当データを表示させます。
AA2=IF(COUNTIF(C:C,$AA$1)<ROW()-1,"",VLOOKUP($AA$1&"-"&ROW()-1,$A:$E,COLUMN()-COLUMN($AA1)+2,FALSE))
これをAD列まで&下に必要なだけコピーしてください。
AB列には店名(当然A店ばかりのはずです)も表示されます。
表示上消したければ列を非表示としてください。

非表示は不可、日付の右のセルは品物が表示されていなければならない。というのでしたら、
COLUMN()-COLUMN($AA1)+2の部分を修正してください。
COLUMNというのは列番号を取得する関数で、右に1つずれたら抽出するデータの位置も1つずれる。という計算式にしています。
個別にAB、AC列は+2を+3にする。といった対応が必要です。

一番簡単なのはA列の左側にIDようの列を挿入し、
A1=C1&"-"&COUNTIF(C$1:C1,C1)
(Aは新しく挿入した列で、Cは元々のBつまり店名の入っている列です)
これをデータの数だけ下にコピーします。
「店名-その行までにその店名が出てきた回数」という表示になります。

VLOOKUPで抽出したいので左端に入れましたが、どうしても左端が無理であるなら、
E列以降でもかまわないです。
その場合VLOOKUPではなく、MATCHによって一致する行を検索し、
その行番号を用いてINDIRECTで該当するセルのデータを抽出することにな...続きを読む

Qエクセル(Excel) 納品書の作り方【画像修正版

昨日http://oshiete.goo.ne.jp/qa/7348426.htmlで質問させていただき、詳しくご回答いただき少し進んだのですが、状況が変わったので改めて質問させていただきます。

■エクセル(Excel)で納品書の作成をしています。
シート1に納品書、シート2に商品マスタ(一覧)を作っていて、シート2の一覧を反映させて
納品書に番号を打ち込むだけで、商品名・単価までが出るシステムを作りたいのですが、
昨日のご回答の中の「VLOOKUP」?を入れて、自分なりにマス目の数字を変えてやってみたのですが
反映されずN/?のようなエラーになってしまいます。

※画像が見にくかったのでシート<CENTER></CENTER>だけにしました。

1、上記のように、シート2との関連付けの係数を、写真の場合の数字で教えてください。

2、合計と、合計から20%を引いた数値を割り出す関数も、写真の数字で御願いします。

宜しくご教授お願い致します。

Aベストアンサー

こんばんは!
前回投稿した者です。

当方もかなり古い(人間も古い!なぁ~んちゃって!)Excel2003を使用しています。
↓の画像のようにSheet2にデータを作成しておきます。

#N/A というエラーは、「検索値」がない!ということですので
お示しの画像のB列にSheet2のA列にないデータを入力するとそういったエラーが表示されます。

画像のセル配置ですと
C4セルに
=IF($B4="","",VLOOKUP($B4,Sheet2!$A:$C,COLUMN(B1),0))
(「$」マークの位置に気を付けてください)
という数式を入れD4セルまでオートフィルでコピー!
そのまま最後の24行目までコピーしておきます。

F4セルには
=IF(COUNTBLANK(B4:E4),"",D4*E4)
という数式を入れ、F24までオートフィルでコピー!

これでB列に商品番号を入力すればSheet2のデータが反映され、
E列に数量を入力でF列に金額が表示されると思います。

最後に合計金額のF26セルは
=IF(COUNT(F4:F24),SUM(F4:F24),"")
手数料のF27セルは
=IF(F26="","",F26*0.2)

これで何とか形にならないでしょうか?

※ 振込金額の欄は不明ですので手を付けていません。

参考になりますかね?m(_ _)m

こんばんは!
前回投稿した者です。

当方もかなり古い(人間も古い!なぁ~んちゃって!)Excel2003を使用しています。
↓の画像のようにSheet2にデータを作成しておきます。

#N/A というエラーは、「検索値」がない!ということですので
お示しの画像のB列にSheet2のA列にないデータを入力するとそういったエラーが表示されます。

画像のセル配置ですと
C4セルに
=IF($B4="","",VLOOKUP($B4,Sheet2!$A:$C,COLUMN(B1),0))
(「$」マークの位置に気を付けてください)
という数式を入れD4セルまでオートフィルで...続きを読む

Qエクセルで、条件に一致する行の指定のセルだけを他のセルに合計で表示するにはどうしたらいいですか?(>

エクセルで、条件に一致する行の指定のセルだけを他のセルに合計で表示するにはどうしたらいいですか?(>_<)
知識があまりないので、入力する数式で教えて頂けたら助かります。

A1からN10までの表があります。
Jの列に送料を入力しています。
Nの列に発送日を入力しています。
商品別に入力しているので、同じ発送日(8/1には○の商品、8/2には△と□の商品、それぞれ送料が発生)で違う商品を入力する事があります。

その表とは別にP1からR31までの表があります。
Pの列に日付を入力してあります。
Qの列に日付別に同じ発送日の商品の送料の合計を表示したいです!

説明がわかりにくかったらすいません。

宜しくお願い致します(>_<)

Aベストアンサー

8/1分の送料、8/2分の送料をそれぞれ別のセルに表示させたいということですか?
商品名関係なく…。

それならSUMIF関数を使って集計すればよいと思いますがいかがでしょう。
集計する範囲(検索範囲)に発送日の列を指定し、
集計する条件(検索条件)に日付を指定し、
集計範囲(合計範囲)に送料の列を指定すれば良い。

 =SUMIF(検索範囲,検索条件,合計範囲)
のようにして使います。

商品名もまとめたいというのであれば、発送日でA1セルからN10セルの範囲を「並べ替え」をしたら良いと思います。

・・・余談・・・
最終的に関数の使い方を理解して問題の解決になると自分は考えていますので、セルに入力する数式まで示しません。
とりあえず試行錯誤してみてください。難しいことではないと思いますよ。
どうしても思うように動作しないのであれば、実際に試した関数式を「補足」に書き込むと、何を間違っているのかを教えてもらえると思います。

Qエクセル(Excel) 納品書の作り方【改めて】

昨日http://oshiete.goo.ne.jp/qa/7348426.htmlで質問させていただき、詳しくご回答いただき少し進んだのですが、状況が変わったので改めて質問させていただきます。

■エクセル(Excel)で納品書の作成をしています。
シート1に納品書、シート2に商品マスタ(一覧)を作っていて、シート2の一覧を反映させて
納品書に番号を打ち込むだけで、商品名・単価までが出るシステムを作りたいのですが、
昨日のご回答の中の「VLOOKUP」?を入れて、自分なりにマス目の数字を変えてやってみたのですが
反映されずN/?のようなエラーになってしまいます。

※画像が貼り付けてあります。商品名は1番以外伏せさせていただいています。
くっつけてありますが、左側がシート1・右側がシート2です。

1、上記のように、シート2との関連付けの係数を、写真の場合の数字で教えてください。

2、合計と、合計から20%を引いた数値を割り出す関数も、写真の数字で御願いします。

宜しくご教授お願い致します。

Aベストアンサー

画像がいまいちよく見えないのですが、納品書の項目は左から、No、商品番号、商品名、単価、数量、金額でいいのでしょうか(名前は多少違っていても意味があっていればもんだいないです)

でしたら、
C1セルに=IF(ISBLANK(B2),"",VLOOKUP(B2,Sheet2!$A$2:$C$200,2,FALSE))
D1セルに=IF(ISBLANK(B2),"",VLOOKUP(B2,Sheet2!$A$2:$C$200,3,FALSE))
E1セルは空白で
F1セルに=IF(D2="","",D2*E2)
といれて、C1からF1までをコピーしてその下の行にタテに貼り付ければ出来ますよ。
おそらくエラーが出たのは、コピーしたときにVLOOKUP関数の最初のセルの指定がずれてしまっているのでは無いかと思いますよ。     

Q特定の文字を含むセルとそのセルを含む4行下のセルまで同じ色をつけたい。

ヨガのクラススケジュール表を作っています。
クラスの種類がいろいろあって、所要時間もそれぞれ違います。
添付画像のようにクラス名によって4行分だったり3行分のセルに色をつけたい場合、条件付き書式や関数で行えるでしょうか?
行える場合そのやり方、関数式を教えて頂けると嬉しいです。
宜しくお願い致します。

Aベストアンサー

いろいろ考えてみました。
条件付き書式でよいのかと思いましたが、
「ヨガ1」「ヨガ2」と数字が入っていますから、それで判別してみたらどうでしょうか。

>4行分だったり3行分のセルに色をつけたい
これ自体は、問題はありません。

例えば、その表が、B56からあるとすれば、色塗りの範囲を全部含めて、その先頭のセルの文字列に対して、

それぞれの設定で、COUNTIFで、結合セルの中の単語や文字を探します。

=COUNTIF(B56,"*1*")>0  として、書式-色の塗りつぶしの設定をします。

=COUNTIF(B56,"*2*")>0

=COUNTIF(B56,"*3*")>0

また、結合セルのセルの行数を取る方法を、マクロでは、MergeAreaというプロパティは知られていますが、マクロなしでは、今のところ見当たらないです。

Qエクセル2007でプルダウンで選んだものに反応

Excel2007でプルダウンで選んだものに反応して隣のセルが自動入力される方法(エクセル2007)
A1をプルダウンで「猫」「犬」から選べるようにし、「猫」を選んだ場合B1に自動に「111」が、「犬」を選んだ場合B1に自動に「222」と入力されるようにしたいです。
ご教授の程、宜しくお願いします。

Aベストアンサー

VLOOKUP関数での方法です。
(1)別シートに入力文字列と対応コード表を作成。(仮にSheet2のA:B列範囲で順不同)
(2)B1に=IF(COUNTIF(Sheet2!A:A,A1),VLOOKUP(A1,Sheet2!A:B,2FALSE),"")を設定
   入力文字列が存在しない場合は空白としています。

Qエクセルで別シートにリスト化した文字列を含むセルを持つ行を削除する方法を教えてください。

エクセルである文字列を含むセルを持つ行を削除したいです。

例えば、

Sheet1に5列(AからE)×5行のデータがあり
その中のA、B、Dの列から
Sheet2のA列にリスト化した文字列"a""b""c""d""e"という文字列を含む行を探し出し、
その行だけを削除する

といったことがしたいです。

Sheet2のリストは随時増えていくので拡張性のある方法を教えてください。
よろしくお願いします。

Aベストアンサー

以下のマクロを標準モジュールに登録し、実行してください。
削除対象の行があると、メッセージボックスに表示し、削除します。
------------------------------------------------
Option Explicit

Dim Sh1, sh2 As Worksheet
Dim MaxRow As Long ' Sheet2の最終行

Public Sub Macro()
Dim row1 As Long 'sheet1の行番号
Dim row2 As Long 'sheet2の行番号
Dim row As Long '行番号(sheet2一時作業用)
Dim delf As Boolean
Set Sh1 = Worksheets("sheet1") '
Set sh2 = Worksheets("sheet2") ' 削除リスト
MaxRow = sh2.Cells(Rows.Count, 1).End(xlUp).row ' 最終行を求める
'sheet1を5行->1行に順に検索
For row1 = 5 To 1 Step -1
delf = True '削除する
If CheckDuplicate(Sh1.Cells(row1, 1)) = False Then
If CheckDuplicate(Sh1.Cells(row1, 2)) = False Then
If CheckDuplicate(Sh1.Cells(row1, 4)) = False Then
delf = False '削除しない
End If
End If
End If
If delf = True Then
MsgBox (row1 & "行を削除します")
Sh1.Rows(row1).Delete
End If
Next
End Sub

'重複チェック
Public Function CheckDuplicate(ByVal str As String)
CheckDuplicate = False '重複なし
Dim row As Long
For row = 1 To MaxRow
'str中(sheet1のA,B,D列の何れかの値)にsheet2のA列の文字が含まれるなら
If InStr(str, sh2.Cells(row, 1).Value) <> 0 Then
CheckDuplicate = True '重複あり
Exit Function
End If
Next
End Function

------------------------------------------------

以下のマクロを標準モジュールに登録し、実行してください。
削除対象の行があると、メッセージボックスに表示し、削除します。
------------------------------------------------
Option Explicit

Dim Sh1, sh2 As Worksheet
Dim MaxRow As Long ' Sheet2の最終行

Public Sub Macro()
Dim row1 As Long 'sheet1の行番号
Dim row2 As Long 'sheet2の行番号
Dim row As Long '行番号(sheet2一時作業用)
Dim delf As Boolean
Set Sh1 = Worksheets("sheet1") '
Set sh2 = Wor...続きを読む


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング