出産前後の痔にはご注意!

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

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

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

A 回答 (5件)

エクセルには「最後の該当」を検索する関数はありません。


無駄に複雑な数式を駆使すればもちろん「やればできます」が、添付図のようにちょっと工夫するだけで、普段使いの数式だけでやっつけるのがお勧めです。

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で入力する

説明は省略しますので、ブラックボックスで使ってください。
「Excelで、条件と一致する最後のセルを」の回答画像1
    • good
    • 6
この回答へのお礼

早々に有難うございます。
普段よく使う関数で出来るなんて感激です。
本当に助かりました。
有難うございます。

お礼日時:2013/03/08 00:25

添付図参照


E2: =A2&"_"&B2
セル E2 を下方にズズーッとドラッグ&ペースト
H2: =SUMPRODUCT(MAX((B$2:B$7=G2)*(A$2:A$7)))
I2: =INDEX(C:C,MATCH($H2&"_"&$G2,$E:$E,0))
セル I2 を右隣にドラッグ&ペースト
範囲 H2:J2 を下方にズズーッとドラッグ&ペースト
「Excelで、条件と一致する最後のセルを」の回答画像5
    • good
    • 9
この回答へのお礼

こんなに短い式で出来るとは思ってもいませんでした。
3日かけても全然わからなかったのに・・・。
有難うございました。

お礼日時:2013/03/08 00:46

配列数式や複雑で分かりにくい数式は計算に負担がかかります。


出来るだけやさしく処理するためには作業列を作って対応することです。
作業列が目障りでしたらその列を選択して右クリックし、「非表示」を選択すればよいでしょう。
お示しの表がシート1に有ってA1セルからD1セルには項目名が日付、社名、品名、個数と並んでいます。
E2セルには次の式を入力して下方にドラッグコピーします。

=IF(B2="","",IF(COUNTIF(B$2:B2,B2)=COUNTIF(B:B,B2),B2,""))

お望みの表をシート2に表示させるとしてA1セルには社名、B1セルには日付、C1セルには品名、D1セルには個数と入力します。
シート2のA2セルから下方には社名を入力します。
B2セルには次の式を入力して右横方向にD2セルまでドラッグコピーしたのちに下方にもドラッグコピーします。

=IF($A2="","",INDEX(Sheet1!$A:$D,MATCH($A2,Sheet1!$E:$E,0),IF(COLUMN(A1)=1,1,IF(COLUMN(A1)>=2,COLUMN(A1)+1))))

最後にB列のデータはシリアル値になっていますので日付の表示形式に変えます。
    • good
    • 2
この回答へのお礼

わかりやすいご説明有難うございます。
COLUMN関数がよくわからなかったので勉強しました。
ひとつ賢くなりました。
有難うございました。

お礼日時:2013/03/08 00:42

こんにちは!


一例です。

↓の画像でF列の社名は入力済みだとします。

G2セルに
=MAX(IF(B$1:B$100=F2,A$1:A$100))
これは配列数式になりますので、Shift+Ctrl+Enterで確定!
この画面からコピー&ペーストする場合はG2セルに貼り付け後、数式バー内で一度クリック!
編集可能になりますので、Shift+Ctrlキーを押しながらEnterキーで確定!
数式の前後に{ }マークが入り配列数式になります。

次にH2セル(配列数式ではありません)に
=INDEX(C$1:C$100,SUMPRODUCT(($B$1:$B$100=$F3)*($A$1:$A$100=$G3)*ROW($A$1:$A$100)))
という数式を入れ隣のI2セルまでオートフィルでコピー!

最後にG2~I2セルを範囲指定 → I2セルのフィルハンドルで下へコピーで
画像のような感じになります。

尚、エラー処理はしていません。m(_ _)m
「Excelで、条件と一致する最後のセルを」の回答画像3
    • good
    • 2
この回答へのお礼

配列数式がわからず、勉強不足ですみません。
これを機に、勉強します。
有難うございました。

お礼日時:2013/03/08 00:33

重複のない社名一覧の抽出を含めて、すべて関数だけで各社名の一番下の行を表示したいなら以下のような関数を使うことになります(セル位置は添付画像参照)。



G2セル(重複のない社名の抽出)

=INDEX(B:B,SMALL(INDEX((MATCH($B$2:$B$100&"",$B$2:$B$100&"",0)<>ROW($B$2:$B$100)-1)*1000+ROW($B$2:$B$100),),ROW(A1)))&""

F2セル(日付:セルの表示形式を日付に設定)
=IF($G2="","",INDEX(A:A,MAX(INDEX(($B$2:$B$100=$G2)*ROW($B$2:$B$100),))))

H2、I2セルはF2セルの数式をコピー貼り付け(セルの書式を「標準」にする)。
「Excelで、条件と一致する最後のセルを」の回答画像2
    • good
    • 0
この回答へのお礼

全て関数だけで出来てしまうなんて驚きです。
この式を使えるようになるよう勉強します。
有難うございました。

お礼日時:2013/03/08 00:29

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

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

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

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

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

QExcel MATCH関数で検索範囲内に同じ値の検索値が複数ある場合

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です。
よろしくお願いします。

Aベストアンサー

MATCH関数を使用しませんが、D9の書式を日付にして、
=MAX((A2:A8)*(C2:C8=1)*(B2:B8="肉類"))
と、入力してShift+Ctrl+Enterキーで入力完了してみてください(配列式になります)。

QVLOOKUP関数で、一番下の値を拾うには?

以下のような表があるとします。

いちご 50
りんご 30
りんご 40
りんご 70
さとう 20
みかん 10
みかん 20

これで、例えばりんごの値をVLOOKUPで求めた場合
一番上である30が答えとして表示されます。
これを、一番下の70を出すには、どのようにしたら良いのですか?
 

Aベストアンサー

#3の回答者です。

#1様の数式について、私は、#3で詳しくは書きませんでした。すみません。ご質問者さんがエラーが発生するとしたら、このような状況が考えられます。

Excel 2003以下の仕様にはこのような問題があります。

Excel の仕様および制限
計算の仕様 --ワークシートの配列
http://office.microsoft.com/ja-jp/excel/HP051992911041.aspx?pid=CH062527721041

#1の数式は、INDEXは、配列数式です。
「セルの範囲 = 値」というスタイルは、配列数式といいます。一般数式は「ひとつのセル = 値」です。
それは、一旦、キャッシュの中に、計算ステージができて、そこで計算されます。そこから値を取り出すには、INDEX関数やSUMPRODUCT関数を使ったり、配列の確定(F2を押して、『ShiftとCtrlを押しながらEnterキー』を押す)をしたりします。

ですから、INDEX 関数等を使わない場合は、配列の確定をしても同様になります。Office 2007 では、その仕様が変わり、列全体をして指定しても、配列の制限がありません。ただし、広範囲にすると計算が遅くなることがあります。下位バージョンでは、範囲を指定しないと配列は不可能なのです。

ご興味のある方は、以下をごらんください。

Excel で配列を使用する処理の制限について
http://support.microsoft.com/kb/166342/ja

#3の回答者です。

#1様の数式について、私は、#3で詳しくは書きませんでした。すみません。ご質問者さんがエラーが発生するとしたら、このような状況が考えられます。

Excel 2003以下の仕様にはこのような問題があります。

Excel の仕様および制限
計算の仕様 --ワークシートの配列
http://office.microsoft.com/ja-jp/excel/HP051992911041.aspx?pid=CH062527721041

#1の数式は、INDEXは、配列数式です。
「セルの範囲 = 値」というスタイルは、配列数式といいます。一般数式は「ひとつのセル = ...続きを読む

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行までとしていますが、必要に応じて変更して下さい

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列にいろいろデータが入っているとして、データが入っている一番下(最終行)の行番号をB1のセルに表示させるにはどういう計算式で出来るでしょうか?
A列のデータは数値だけでなく文字列も含みます。また途中空白セルもあります。
A1~A20にデータが入っているときはB1に20と表示させる。
よろしくお願いします。

Aベストアンサー

=MAX(IF(COUNTIF(A:A,"*"),MATCH("",A:A,-1)),IF(COUNT(A:A),MATCH(MAX(A:A)+1,A:A,1)))

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エクセルの関数(最後の値を返す)

A1からJ65までの範囲のなかで (計算式などが入ってるセルも含まれてます)
J列の最後に表示されている値を返したいのですが どんな計算式をしていいか 調べてもわからず困っています。

J35のときもあればj40のときもあります。
ちなみにJ列にはすべて 計算式がはいってます。
その計算結果が表示されているセルと空白のセルがあります。
(J1からJ40まで値表示J41からしたは空白表示のときJ40を返したい
このときA列はA1からA40までは値が入力されてますがA41からしたはまったくの空白です)

Aベストアンサー

計算結果が数値なのであれば
=INDEX(J:J,MATCH(MAX(J:J)+1,J:J,1))
でJ列の最後に表示されている値を返します。 

Q値が入っている一番右のセル位置を返す方法

たとえばA行に値(文字列)が横に並んでいるとして
値が入っている一番右のセルの位置を返すようにするにはどのようにすればいいでしょう?

Aベストアンサー

セルの位置を返したいのであれば
=CHAR(MATCH(MAX(1:1)+1,1:1,1)+64)&1
でどうでしょうか。

Q条件に合った最初の行番号を表示したい。

excelで、Sheet1に、Aに丁目、Bに番地、Cに号を割り振りました。
  A  B  C
1  1  1  15
2  1  1  18
3  1  1  21
4  1  2  7
5  1  2  14
6  2  1  33
7  2  1  36
8  2  1  37
9  3  1   15
そこで、条件に合った最初の行番号を表示したいのです。
例えば、2丁目1番地の最初は、6行目なので、F1などに6と表示する方法を知りたいのです。
1丁目2番地だったら、F1は4になるようにしたいのです。
よろしくお願いします。

Aベストアンサー

こんばんは!

>例えば、2丁目1番地

D1sるに○丁目 の○の数値、E1セルに△番地 の△数値を入力するとします。

F1セルに
=MIN(IF((A1:A1000=D1)*(B1:B1000=E1),ROW(A1:A1000)))

これは排列数式になりますので、Shift+Ctrl+Enterで確定してください。

この画面からコピー&ペーストする場合は
上記数式をドラッグ&コピー → F1セルを選択 → 数式バー内に貼り付け → そのまま(編集可能なまま)
Shift+Ctrlキーを押しながらEnterキーで確定!
数式の前後に{ }マークが入り配列数式になります。m(_ _)m


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

人気Q&Aランキング