[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『条件に一致する項目の最大値を検索』（アオイ）
はじめまして いつも参考にさせていただいています。
今回検索してもよくわからなかったので質問させていただきましたのでよろしくお願いします。
品名 入荷数 りんご 15 りんご 20 ばなな 15 ばなな 30 ぶどう 10 りんご 25 ぶどう 30 ばなな 10 ばなな 15 いちご 40
以上の様なリストから「りんご」の最大値をもとめる数式は何を使用すればよいでしょうか？
ご回答のほどよろしくお願いいたします
＜ 使用 Excel：Excel2003、使用 OS：WindowsXP ＞
A B C D E 1 品名 入荷数 品名 最大値 2 りんご 15 りんご 25 3 りんご 20 4 ばなな 15 5 ばなな 30 6 ぶどう 10 7 りんご 25 8 ぶどう 30 9 ばなな 10 10 ばなな 15 11 いちご 40 12
E2 =DMAX(A:B,B1,D1:D2) （GobGob） 2014/08/07(木) 12:31
GobGob様、回答有り難うございます。
ちなみにここからもう一歩進んで、全部の品名の最大値をそれぞれ表示させるにはどうしたらよいでしょうか？
今回の例なら
品名 最大値 りんご 25 ばなな 30 ぶどう 30 いちご 40 ・ ・ ・ （品名は増える可能性あり）
後追いになってしまい申し訳ありませんが宜しくお願い致します。 （アオイ） 2014/08/07(木) 12:52
ピボットテーブルでもできますよ。 （コナミ） 2014/08/07(木) 13:29
コナミ様 回答有り難うございます
このリストは日々増えていくので、追加していってもその時点での最大値が品名毎にすぐ判るようにしたいと思っています。 さらに、この表を使用するのはあまりパソコンに触れた事のない人もいるので、出来れば更新とかしないでも表示される数式でやりたかったのですが、複雑になりすぎでしょうか？ （アオイ） 2014/08/07(木) 14:30
E2 =MAX(INDEX((A$2:A$100=D2)*B$2:B$100,)) 下へコピー。 （GobGob） 2014/08/07(木) 14:48
A B 1 商品 数量 2 りんご 1 3 ばなな 2 4 みかん 3 5 りんご 4 6 ばなな 5 7 みかん 6 8 りんご 7 9 ばなな 8 10 みかん 9 11 すいか 11 12 メロン 12 13 もも 13
C2=IF(COUNTIF($A$2:$A2,A2)>1,"重複","○")
D2={INDEX($A$1:$A$1000,SMALL(IF($C$1:$C$1000="○",ROW($A$1:$A$1000)),ROW(C1)))}
E2={INDEX($B$1:$B$1000,LARGE(IF($A$1:$A$1000=$D2,ROW($A$1:$A$1000)),ROW($A$1)))}
※ D2,E2セルには{}内の数式を打ち込んだ後にCtrl+Shuft+Enterで入力します
これで
A B C D E 1 商品 数量 商品 数量 2 りんご 1 ○ りんご 7 3 ばなな 2 ○ ばなな 8 4 みかん 3 ○ みかん 9 5 りんご 4 重複 すいか 11 6 ばなな 5 重複 メロン 12 7 みかん 6 重複 もも 13 8 りんご 7 重複 9 ばなな 8 重複 10 みかん 9 重複 11 すいか 11 ○ 12 メロン 12 ○ 13 もも 13 ○
（ryopo^2） 2014/08/07(木) 15:57
別のサンプルデータで試したところ、これではダメなことがわかりました。
もう少し考えてみます。。。
GobGob様 どうもありがとうございました！ 希望どおりに出来ました。
ryopo~2様も回答ありがとうございました。 今回は短い式（自分が理解できた方(；一_一)）でやらせていただきましたが、もう少し勉強してryopo様のも理解できるようになりたいです。 （アオイ） 2014/08/07(木) 16:09
（ryopo^2） 2014/08/07(木) 16:23
D列も数式で持ってくる場合。
D2 =IF(SUMPRODUCT((MATCH(A$2:A$100&"",A$2:A$100&"",)=ROW(A$2:A$100)-1)*(A$2:A$100<>""))<ROW(A1),"",INDEX(A:A,SMALL(INDEX((MATCH(A$2:A$100&"",A$2:A$100&"",)<>ROW(A$2:A$100)-1)*10^16+ROW(A$2:A$100),),ROW(A1))))
E2 =IF(D2="","",MAX(INDEX((A$2:A$100=D2)*B$2:B$100,)))
D2:E2 下へコピー。 （GobGob） 2014/08/08(金) 07:55
質問者とは違いますが失礼します。
（GobGob）さんがご教示されている
＞D2 =IF(SUMPRODUCT((MATCH(A$2:A$100&"",A$2:A$100&"",)=ROW(A$2:A$100)-1)*(A$2:A$100<>""))<ROW(A1),"",INDEX(A:A,SMALL(INDEX((MATCH(A$2:A$100&"",A$2:A$100&"",)<>ROW(A$2:A$100)-1)*10^16+ROW(A$2:A$100),),ROW(A1))))
の計算結果は私が探し求めていたもので本当に嬉しいです。
ありがとうございました。
（ちぃさん） 2014/08/08(金) 14:58
>の計算結果は私が探し求めていたもので本当に嬉しいです。
うん。でもryopo^2さんのような作業列使った方が重くないし、いいっすよ。
c2 =If(a1="","",if(countif(A$2:A2,A2)=1,row(A1),"")) d2 =if(count(c:c)<row(a1),"",index(a:a,small(c:c,row(a1))))
（GobGob） 2014/08/09(土) 00:07
納得の出来る結果でしたので嬉しすぎてお礼の言葉をと思い書き込みましたのに更に回答頂きまして感謝感謝です。
もし御迷惑でなければもう少し、あと2点程教えて頂きたいのですが(汗)
続けて書き込みます。
まず1点目は
c2 =If(a1="","",if(countif(A$2:A2,A2)=1,row(A1),""))
↑こちらの新しくご教授頂きました式の事です。
試してみたのですが6行目、セル番で言いますとA6を検索した際に何故か数字の5が表示されます。
何故でしょうか？
2点目は
個人的には最初にご教授いただいた式が好みです。
質問者のサンプルですとA2からの検索となり今のままでいいのですが、A3から検索をするとD3には何も表示されません。(汗)
どの部分をどのように訂正すればいいでしょうか？
図々しくて申し訳ないです。
時間がある時でいいので宜しくお願いいたします。
（ちぃさん） 2014/08/09(土) 11:42
まず1点目は c2 =If(a1="","",if(countif(A$2:A2,A2)=1,row(A1),"")) ↑こちらの新しくご教授頂きました式の事です。 試してみたのですが6行目、セル番で言いますとA6を検索した際に何故か数字の5が表示されます。 何故でしょうか？
結論として数字の5が表示されるのは正しいです。
ご指摘の式の意味はＡ列の重複チェックおよびデータ行のうち何行目のデータなのを表示するもの
です。
ちなみに
c2 =If(a2="","",if(countif(A$2:A2,A2)=1,row(A1),""))
の方がわかりやすいとは思いますが。
オリジナルの質問でのサンプルデータを使って説明します。
まず、
C6 =IF(A6="","",IF(COUNTIF(A$2:A6,A6)=1,ROW(A5),""))
ですが
COUNTIF(A$2:A6,A6)=1
⇒A2～A6の範囲とする時にA6の値：『ぶどう』の入ったセルが１個ならば（A1～A5までに『ぶどう』
の入ったセルがなければ）
ROW(A5)
⇒A2を起点（1番目）として『ぶどう』が入ったセルが何番目かをセットする
という意味です。『ぶどう』はA6のセルにありますので、A2を1番目として5番目のデータです。
ちなみに『ぶどう』はA8セルにも入っていますが、A1～A7までに『ぶどう』が入っていますので
C8=IF(A8="","",
によって空白となります
他の商品名でも同じようになっているはずですので確認してみてください。
2番目の質問については後で。。。
（ryopo^2） 2014/08/13(水) 16:42
＜16:50頃訂正＞
C8セルが空白になる理由の説明文に誤りがありました。
誤）C8=IF(A8="","", によって空白となります。
正）IF(COUNTIF(A$2:A8,A8)=1,ROW(A7),""))によって空白となります。
1点目だけ。
D2の式 > =if(count(c:c)<row(a1),"",index(a:a,small(c:c,row(a1)))) これを変更せず、そのままにしておくのであれば
C2の式は
=IF(A2="","",IF(COUNTIF(A$2:A2,A2)=1,ROW(),"")) ~~~ ~~~~~ こんな感じかな？ （笑） 2014/08/13(水) 17:24
C行の式理解しました。
D行の値が何故か変だったので何故だろ？と思ってたのですが...
C6 =IF(A6="","",IF(COUNTIF(A$2:A6,A6)=1,ROW(A5),""))
↑は
C6 =IF(A6="","",IF(COUNTIF(A$2:A6,A6)=1,ROW(A6),""))
としなければいけないのですね。スッキリしました。
やはり式を分けた方がいいのですかね？(汗)
＞D2 =IF(SUMPRODUCT((MATCH(A$2:A$100&"",A$2:A$100&"",)=ROW(A$2:A$100)-1)*(A$2:A$100<>""))<ROW(A1),"",INDEX(A:A,SMALL(INDEX((MATCH(A$2:A$100&"",A$2:A$100&"",)<>ROW(A$2:A$100)-1)*10^16+ROW(A$2:A$100),),ROW(A1))))
↑
悲しい事にこちらの式、不具合が見つかりました。
A列の途中に空白部分があればその空白より下の値はD列に表示されません。
A列には値は必ず入る予定なのですが、もしもの事を考えて手直しってできますでしょうか？
おんぶに抱っこで申し訳ないですが手直しが可能でしたらお助け下さい。
宜しくお願いいたします。
（ちぃさん） 2014/08/14(木) 06:35
1点目。
> C6 =IF(A6="","",IF(COUNTIF(A$2:A6,A6)=1,ROW(A6),"")) > としなければいけないのですね。
それでもいいけど、数式を入れてるセルの行番号を返すのであれば、 前回の回答のように（見てくれたのかな？）ROW の引数を省略してもいい。
ROW(A6) → ROW() 2点目。
データは文字列ということでいいのかな？ Excelのバージョンは？
データは文字列で、A3セルが開始行だとして。
D3 =INDEX(A:A,SMALL(INDEX(((A$3:A$100<>"")*(MATCH(A$3:A$100&"",A$3:A$100&"",0))<>ROW(A$3:A$100)-2)*100+ROW(A$3:A$100),0),ROW(A1)))&"" こんな感じかな？
数式にある *100 の100は、データ範囲の最終行のこと。 範囲を変更するときは、その100も変更してください。
ただし、指定した範囲がA$3:A$100だとすれば、 範囲外のA101（厳密にはA103）から下には何も入力しないことを前提にしている。 それは大丈夫かな？
バージョンが2007以降ならIFERROR関数を使ってもいいね。
D3 =IFERROR(INDEX(A:A,SMALL(INDEX(((A$3:A$100<>"")*(MATCH(A$3:A$100&"",A$3:A$100&"",0))<>ROW(A$3:A$100)-2)*10^10+ROW(A$3:A$100),0),ROW(A1))),"")
こんな感じ。 （笑） 2014/08/14(木) 11:46
＞それでもいいけど、数式を入れてるセルの行番号を返すのであれば、
＞前回の回答のように（見てくれたのかな？）ROW の引数を省略してもいい。
＞ROW(A6) → ROW()
申し訳ございません。
ROW関数の部分、入力し忘れていられるものと勝手に勘違いしておりました。すみません（汗）
ROW() で試してみまして、驚きました。()内が未入力でも動くのですすね。（汗）
無知ですみません。（照）
＞データは文字列で、A3セルが開始行だとして。
＞D3 =INDEX(A:A,SMALL(INDEX(((A$3:A$100<>"")*(MATCH(A$3:A$100&"",A$3:A$100&"",0))<>ROW(A$3:A$100)-2)*100+ROW(A$3:A$100),0),ROW(A1)))&""
コチラの式、完璧な仕上がりです。
現時点でご教授いただいた式を使う際に思いつくトラブル事を試した結果、全く問題なくパーフェクトな仕上がりで感動しました。
＞数式にある *100 の100は、データ範囲の最終行のこと。
＞範囲を変更するときは、その100も変更してください。
ありがとうございます。
選択セルの変更時は変更を心がけます。
＞ただし、指定した範囲がA$3:A$100だとすれば、
＞範囲外のA101（厳密にはA103）から下には何も入力しないことを前提にしている。
＞それは大丈夫かな？
はい。大丈夫でございます。
現在のサンプルは同じシートのものを検索して..という内容ですが、ご教授いただいた式を私が使う際はシート1に生産するたび、最下行に入力されている生産条件の生データーから検索させるつもりです。
＞バージョンが2007以降ならIFERROR関数を使ってもいいね。
現時点では使用エクセルバージョンは2003を使用でございます。
PCによっては2010を使う事もあるかもしれないのでIFERROR関数のものも試させていただきましてコチラも全く問題なかったです。
お盆の忙しい中、私のために時間を作っていただき感謝いたします。
本当にありがとうございました。
（ちぃさん） 2014/08/14(木) 20:59
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.