ウォーターサーバーとコーヒーマシンが一体化した画期的マシン >>

MATCH関数で、検索した値が複数ある場合に1つしか出なくて困っています。
(例)
   A B C D
1 年月日 種類 番号 備考
2 4月1日 肉類 1
3 4月2日 野菜 2
4 4月3日 肉類 1
5 4月4日 果物 2
6 4月5日 野菜 1
7 4月6日 果物 3
8 4月7日 果物 2
9 4月8日 肉類 1

この表で、D9の備考の欄に「種類:肉類、番号:1」と同じものがあった時の日付を、新しい日付で取り出したいのです。

INDEX(A2:A8,(MATCH(B9,B2:B8,0)+MATCH(C9,C2:C8,0))/2)
としたのですが、本当は4月3日を取り出したいのですが、4月1日が出てきます。
どのようにしたら良いでしょうか?
Excel2003です。
よろしくお願いします。

このQ&Aに関連する最新のQ&A

A 回答 (2件)

MATCH関数を使用しませんが、D9の書式を日付にして、


=MAX((A2:A8)*(C2:C8=1)*(B2:B8="肉類"))
と、入力してShift+Ctrl+Enterキーで入力完了してみてください(配列式になります)。
    • good
    • 10
この回答へのお礼

回答ありがとうございます。
早速試してみた所、うまく行きました。
またいろいろと聞く機会があると思いますので、よろしくお願いします。
ありがとうございました。

お礼日時:2009/01/20 17:25

match関数やlookup関数などの検索関数は、最初に見つけたものを返


すことになっています。2番目が欲しいとか全部欲しいとかはできま
せん。

INDEX(A2:A8,(MATCH(B9,B2:B8,0)+MATCH(C9,C2:C8,0))/2)
最初に種類が「肉類」だった行と最初に番号が「1」だった行の中間
の日付を返すって書いてありますね。「種類が肉類で番号が1」では
ないです。私なら、最近index関数がマイブームなので、

=index(A$1:A8,max(index((B$2:B8&C$2:C8=B9&C9)*row(B$2:B8),0)))

みたいな書き方をします。これはD列でドラッグして、それぞれの行
と同じ内容の直近の日付を返します。
    • good
    • 5
この回答へのお礼

回答ありがとうございます。

今回は、回答1の方法を使わせてもらうことにしました。
MAX関数を使うということが分かり、非常に助かりました。
また質問する機会があると思いますので、よろしくお願いします。
ありがとうございました。

お礼日時:2009/01/20 17:30

このQ&Aに関連する人気のQ&A

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

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

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

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

Q【Excel】 INDEX ,MATCH でいいのか。該当が複数ある場合

項目の検索で行き詰まっています。

A1 から A30 に名前
B1 から B30 に得点
が入力されています。
B31 に最高点を表示するのは、MAX 関数でできました。

B32 にその最高点をとった人物が誰かを表示するのに
=INDEX($A$1:$A$30,MATCH($B$31,$B$1:$B$30),1)
と入力したら、
該当者が1人しかいない時は問題ないのですが、複数いる場合でも1人しか表示されません。
該当が複数ある場合の出し方は他に方法があるのでしょうか?

どなたか力を貸してください。
よろしくお願いします。

Aベストアンサー

誰が最高点なのか確かめるだけであれば、
#1さんのご提案されたオートフィルタが恐らく最短ですね。

ただ、ご質問の例をみますと、
やはり特定のセルに値を返す必要があるのではないかとも思われます。
その場合、#2さんがご提案されたように作業列を使って引くのが順当な手順かと思います。

さて。
たまたまつい先日似た質問にお答えしたので、
正直あまりお奨めはしないのですが、「一発関数回答」を。

B32セル:
 =IF(ROW()-ROW(B$31)>COUNTIF($B$1:$B$30,B$31),"",INDEX($A$1:$A$30,MATCH(LARGE(($B$1:$B$30=B$31)/ROW($B$1:$B$30),ROW()-ROW(B$31)),1/ROW($B$1:$B$30),0)))

を配列数式として入力し下方に十分な数だけフィル。(Excel2003で動作確認済)
※通常の数式は、数式を入力した後Enterキーで確定しますが、
 これは配列数式なので、CtrlとShiftを押しながらEnterで確定してください。

数式中に、B$31,$A$1:$A$30,$B$1:$B$30の3種類の参照が複数回登場しますが、
いずれも質問文の数式と同様のニュアンスで考えていただいて結構です。
位置が変わる場合はそれぞれ置き換えてください。
(ただし、最高点のセルの直下に、縦に並べて名前を表示することを想定しています)

数式の意味内容については、やや手前味噌の感もありますが、
先日した回答のURLを挙げておきますのでご参考ください。
http://oshiete1.goo.ne.jp/qa3328660.html
(このケースでは行列(縦横)が逆になっています)

誰が最高点なのか確かめるだけであれば、
#1さんのご提案されたオートフィルタが恐らく最短ですね。

ただ、ご質問の例をみますと、
やはり特定のセルに値を返す必要があるのではないかとも思われます。
その場合、#2さんがご提案されたように作業列を使って引くのが順当な手順かと思います。

さて。
たまたまつい先日似た質問にお答えしたので、
正直あまりお奨めはしないのですが、「一発関数回答」を。

B32セル:
 =IF(ROW()-ROW(B$31)>COUNTIF($B$1:$B$30,B$31),"",INDEX($A$1:$A$30,MATCH(LA...続きを読む

QExcelですが、同一データが複数あるとき、検索して、その全部を抽出する方法

Excelですが、検索キーに、同一データ(レコード)が複数あるとき、検索して、その全部を抽出表示する方法を教えてください。

Vlookupは、同一データが複数あるとき、最初の行(レコード)を1つだけ抽出してきます。2つ目、3つ目の行は抽出できませんが、その全部を抽出する方法がありますでしょうか。

例えば、以下の例で、「A株式会社」をキーに検索した場合、<検索結果>シートのように、該当のレコード3つ(行2~4)を抽出して表示するようにしたいのですが、方法はありますでしょうか。よろしくお願いします。

<データシート>
 列A  列B    列C 列C
行1 No 会社名   所属 担当者
行2 1 A株式会社  ○事業部  坂下順人
行3 2 A株式会社  △事業部  滿山友人
行4 3 A株式会社  △事業部  目標達子
行5 4 B株式会社  設計部山下清人
行6 5 B株式会社  営業部横浜美人
行7 6 C株式会社  営業部川崎次郎
行8・・・・・・・・・・・・

<検索結果:別のシートにおいて>
行1 会社名 所属  担当者
行2 A株式会社 ○事業部 坂下順人
行3 A株式会社 △事業部 滿山友人
行4 A株式会社 △事業部 目標達子

Excelですが、検索キーに、同一データ(レコード)が複数あるとき、検索して、その全部を抽出表示する方法を教えてください。

Vlookupは、同一データが複数あるとき、最初の行(レコード)を1つだけ抽出してきます。2つ目、3つ目の行は抽出できませんが、その全部を抽出する方法がありますでしょうか。

例えば、以下の例で、「A株式会社」をキーに検索した場合、<検索結果>シートのように、該当のレコード3つ(行2~4)を抽出して表示するようにしたいのですが、方法はありますでしょうか。よろしくお...続きを読む

Aベストアンサー

#03です
>最後のROW(T1)で、T1を参照しておりますが

ROW(T1)は「1番目」を意味しています。T1セルに何もなくても関係ありません。コピーしたの行はROW(T2)になりますが、これは2番目のという意味です

#REF!エラーになりますか?
私が示した条件と何か変えていませんか?
別シートのA1に「抽出する会社名」を入力してあれば、回答したような結果になるはずですが‥ (テストしてありますので)

ただし2000行もあるならVBAの方が良いかもしれません
きっと「シートが重くなる」と思います

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

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

たとえば、

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

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

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

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

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

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートの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行までとしていますが、必要に応じて変更して下さい

QExcelで、条件と一致する最後のセルを検索したい

Excelで、条件と一致する最後のセルを検索したいのですが、どの関数をどのように使えばいいかわかりません。
どなたかお力をお貸し下さい。

問)
日付  社名  品名  個数
1/1   A    あ    1
1/1   C    い    2
1/2   B    う     3
1/2   A    え    4
1/3   C    お    5
1/3   A    あ    3

答)
A社の最終購入日は  1/3 あ 3
B社の最終購入日は  1/2 う  3
C社の最終購入日は  1/3 お 5

となるようお願いします。

Aベストアンサー

エクセルには「最後の該当」を検索する関数はありません。
無駄に複雑な数式を駆使すればもちろん「やればできます」が、添付図のようにちょっと工夫するだけで、普段使いの数式だけでやっつけるのがお勧めです。

A2:
=IF(OR(C2="",COUNTIF($C$2:C2,C2)<COUNTIF(C:C,C2)),"",C2)
以下コピー

答えは簡単なVLOOKUP関数を使うだけです。
H2:
=IF(COUNTIF($A:$A,$G2),VLOOKUP($G2,$A:$E,2,FALSE),"")




まぁ一応ご参考に作業列を使わない方法:
H2:
=IF(COUNTIF($C:$C,$G2),INDEX(B:B,MAX(IF($C$2:$C$999=$G2,ROW($C$2:$C$999)))))
と記入し、必ずコントロールキーとシフトキーを押しながらEnterで入力する

説明は省略しますので、ブラックボックスで使ってください。

Qエクセルで条件に一致したセルの隣のセルを取得したい

下のような「得点」という名前のシートがあります。
(「田中」のセルがA1です。)

 [ 田中 ][ 10 ][ 200 ]
 [ 山田 ][ 21 ][ 150 ]
 [ 佐藤 ][ 76 ][ 250 ]
 [ 鈴木 ][ 53 ][ 350 ]

別のシートのA1セルに、「佐藤」と入力すると、

 [ 佐藤 ]

「得点」シートから「佐藤」の列を見つけて、B1、C1に

 [ 佐藤 ][ 76 ][ 250 ]

のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。
「得点」シートでは氏名が重複する事はありません。
IF文を使うと思うのですが、いまいち良く分かりませんでした。

よろしくおねがい致します。

Aベストアンサー

こんにちは!
VLOOKUP関数で対応できます。
IF関数と併用すればエラー処理が可能です。

Excel2007以降のバージョンであれば
B1セルに
=IFERROR(VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0),"")
としてC1セルまでオートフィルでコピー!
そのまま下へコピーすると行が2行目以降でも対応できます。

Excel2003までの場合は
=IF($A1="","",VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0))

としてみてください、m(_ _)m

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

Qエクセルの複数条件に一致したセルを抽出したいのですが、関数で教えてくだ

エクセルの複数条件に一致したセルを抽出したいのですが、関数で教えてください。

               A列   B列    C列
Aファイルには 行 【注文月日】、【氏名】、【商品金額】が記入されています。
Bファイルには 行 【注文月日】、【氏名】、【税金額】が記入されています。

AファイルのD列に、Aファイルの【注文月日】かつ【氏名】がBファイルの【注文月日】かつ【氏名】と一致する場合のBファイルC列の【税金額】を表示したいのです。

VLOOKUP関数では条件が1つだけなので使用できませんが、似たような関数で複数条件ができる関数があるなら教えてください。

Aベストアンサー

こんばんは!
一例です。
↓の画像で右側(Bファイル)の方に作業用の列を設けています。
作業列D2セルを
=A2&B2
としてオートフィルで下へずぃ~~~!っとコピーします。

そして、AファイルのD2セルに
=IF(OR(A2="",COUNTIF([Bファイル.xls]Sheet1!D$2:D$1000,A2&B2)=0),"",INDEX([Bファイル.xls]Sheet1!C$2:C$1000,MATCH(A2&B2,[Bファイル.xls]Sheet1!D$2:D$1000,0)))
という数式を入れ、オートフィルで下へコピーすると
画像のような感じになります。
尚、数式はBファイルの1000行目まで対応できるようにしていますが、
データ量によって範囲指定の領域はアレンジしてみてください。

以上、参考になれば良いのですが
他に良い方法があれば読み流してくださいね。m(__)m

Qセル番号を返す関数

エクセルで、セル番号を返す関数はありますか??
セルの内容ではなくて、番号です。(A1、B3など)
調べてみましたがなかなか見つからなかったので、
わかる方教えてください。
宜しくお願い致します。

Aベストアンサー

文字として"C4"を返す。
=ADDRESS(ROW(C4),COLUMN(C4),4)

式を入力したセルの位置を文字列で返す。
=ADDRESS(ROW(),COLUMN(),4)

ADDRESSの3つ目の引数を変えると
$C$4
$C4
C$4
C4
の4つの形に出来ます。

Qエクセル 複数条件での検索を関数を使って解決したいです。

エクセル関数を使って複数の条件を満たしたときの
特定のセルの文字列を返す方法を考えています。

例を挙げると

   A    B    C     D    E    F
1  金額  日付  名称   金額  日付 名称
2  100  1月5日  a    100  2月1日 =???
3  200  1月5日  b
4  300  1月6日  c
5  400  2月1日  d
6  100  2月1日  XYZ
7  100  2月3日  e
8  200  2月3日  f

上のような表のときに
金額=「100」 かつ 日付=「2月1日」 という2つの条件を
満たしたときのC列の文字列「XYZ」を返すということをやりたいのです。

金額だけですとVLOOKUP関数を使って
=VLOOKUP(D2,A2:C8,3,false)
とやって「a」という文字列はかえってくるのですが
どうやっても「XYZ」という文字列が返せません。
(日付の条件を入れていないので当たり前ですが...)

なにかいい方法がありましたらお願いします。

エクセル関数を使って複数の条件を満たしたときの
特定のセルの文字列を返す方法を考えています。

例を挙げると

   A    B    C     D    E    F
1  金額  日付  名称   金額  日付 名称
2  100  1月5日  a    100  2月1日 =???
3  200  1月5日  b
4  300  1月6日  c
5  400  2月1日  d
6  100  2月1日  XYZ
7  100  2月3日  e
8  200  2月3日  f

上のような表のときに
金額=「100」 かつ 日付=「2月...続きを読む

Aベストアンサー

◆こんな方法もありますよ
=INDEX(C2:C8,MATCH(D2&E2,INDEX(A2:A8&B2:B8,),0))

Qエクセルで重複しているデータの抽出のしかたを教えてください。

エクセルで重複しているデータを抽出したいのですが・・。なにぶん初心者なもので簡単な方法があればよいのですが・・。
A列に日付 B列に企業名このデータが300件ほどあります。

 Å列      B列
1月 1日    AAAA社
1月 4日   BBBB社
1月 8日   CCCC社
2月 1日   BBBB社
2月20日    DDDD社
3月 2日   AAAA社

と、あるとしてB列の企業名が重複しているものを抽出したいのですが・・・。この場合 AAAA社とBBBB社ですが。
これを簡単に抽出する方法はないでしょうか?
教えてください お願いいたします。

Aベストアンサー

企業名がセルB2からセルB300まで入力されていると仮定します。
セルC2に IF(COUNTIF($B$2:B2,B2)>1,"*","") を入力し、セルC300までコピーして下さい。すると重複したデータの二番目以降に "*" のマークが付きます。
(注) 数式を下のセルまでコピーするため、COUNTIF($B$2 は絶対参照にして下さい。


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

人気Q&Aランキング