エクセル MIN関数 COUNT関数

質問

下段キャプチャーSheet2に必要な関数（計算式）のご相談です。

Sheet1の集計結果を元にSheet2のリストを作成したいと考えています。
Sheet2は各商品毎に見積金額の安い業者をピックアップしたものです。
Sheet2の業者選定の条件は下記の通り。
・見積金額が同額の場合、最安単価の業者を抽出
・最安単価の業者が複数ある場合、その業者数をF列に記載（※１社の場合は"1"と記載）

・A列(A2以下)に必要な計算式をご教示ください。
→見積金額、単価共に最安の業者のＩＤ抽出
・F列(F2以下)に必要な計算式をご教示ください。
→見積金額、単価共に最安の業者数の抽出

※エクセルバージョン：2010　(2013でも可)
※Visual Basic不可
※関数を起動させるために設定変更など必要な場合は、併せてご指導ください。
※Sheet1の行数は3000行以上になる見込みです。

宜しくお願い致します。

回答No.6

bunjii · ベストアンサー

回答No.2に大きな誤りがありました。お詫びいたします。
Sheet2のB列はSheet1のB列をコピーして「データ」タブの「重複の削除」で処理してください。
最初にSheet2のD2へ見積金額の最小価格を抽出します。
=LARGE(INDEX((Sheet1!B$5:B$5000=B2)*Sheet1!D$5:D$5000,0),COUNTIF(Sheet1!B$5:B$5000,B2))
次にShet2のE2へ見積金額の最小価格の内から最小単価を抽出します。
=LARGE(INDEX((Sheet1!B$5:B$5000=B2)*(Sheet1!D$5:D$5000=D2)*Sheet1!E$5:E$5000,0),COUNTIFS(Sheet1!B$5:B$5000,B2,Sheet1!D$5:D$5000,LARGE(INDEX((Sheet1!B$5:B$5000=B2)*Sheet1!D$5:D$5000,0),COUNTIF(Sheet1!B$5:B$5000,B2))))
Sheet2のA2へ最小単価が最初に現れた行のIDを抽出します。
=MATCH(LARGE(INDEX((Sheet1!B$5:B$5000=B2)*(Sheet1!D$5:D$5000=D2)*Sheet1!E$5:E$5000,0),COUNTIFS(Sheet1!B$5:B$5000,B2,Sheet1!D$5:D$5000,LARGE(INDEX((Sheet1!B$5:B$5000=B2)*Sheet1!D$5:D$5000,0),COUNTIF(Sheet1!B$5:B$5000,B2)))),INDEX((Sheet1!B$5:B$5000=B2)*(Sheet1!D$5:D$5000=D2)*(Sheet1!E$5:E$5000=E2)*Sheet1!E$5:E$5000,0),0)
Sheet2のF2へ見積金額が最低で然も単価が最低の会社数を算出します。
=IF(A2="","",COUNTIFS(Sheet1!$B$4:$B$5000,B2,Sheet1!$D$4:$D$5000,D2,Sheet1!$E$4:$E$5000,E2))
最後にSheet2のC2へ商品名、見積金額、単価が一致する会社名を抽出します。
=VLOOKUP($A2,Sheet1!$A$5:$E$5000,COLUMNS(Sheet1!$A4:C4),FALSE)
尚、Sheet2のB列について空欄のチェックを行っていませんので必要のときはIF関数で補完してください。

回答No.2では最初にIDを引き出すために無理な数式を組み立てたことで誤りが発生しました。
簡単に引き出せる項目から順に抽出すれば、その結果を利用できますので数式を簡略化できます。
当方の環境はExcel 2013ですが、Excel 2010でも再現できます。

お礼

再現できました！
大変勉強になりました。
いつも本当にありがとうございます。

回答No.5

msMike

１．Sheet1(添付図上段)の列Ｂを Sheet2(添付図下段)の列Ｂにコピペ
これ以降は Sheet2 における操作です。
２．列Ｂを選択して、[データ]→[データツール <重複の削除>]
３．"先頭行をデータの見出しとして使用する"にチェック入れて
￣￣[OK]をツン
４．次式を入力したセル A2 を下方にオートフィル
￣￣ =INDEX(Sheet1!A:A,MATCH($B2,Sheet1!$B:$B,0))
５．セル A2 をセル C2 にコピペ
６．次の"配列"数式を入力したセル D2 を右隣にオートフィル
￣￣ =MIN(IF(Sheet1!$B$2:$B$7=$B2,Sheet1!D$2:D$7,""))
７．セル F2 に次式を入力
￣￣ =SUMPRODUCT((Sheet1!B$2:B$7=B2)*(Sheet1!E$2:E$7=E2))
８．範囲 C2:F2 を下方にオートフィル

お礼

まだ動作検証が十分にできていませんが、これからいろいろ参考にさせていただきます。エラーなどがでたら別の質問で相談させてください。アドバイスありがとうございました。

回答No.4

Nouble

お待たせしました
仕様です。

と、
其の、前に
ご注進

countif系は、確か
SUM、同様
置き位置の
依存が、有る
等の
挙動変化が
過去
確認されて、いる筈

式の
置き位置、次第では
挙動が
想像を、超える

其の、様な
ものは
-GOUF-様に、おきましては
其の、旨を
コメントした上で、使うか

使用を、取りやめられた方が
良いか
と、思います

蛇足ですね
済みません

さて、では
先ず、重要
なのは

今回
此の、私作成の
アプリでは

Sheet1の、商品名から
項を、取得する
際の
並び替えを、機能内から
省きました

やれば、可能
なのですが

式が
やたら、長くなり
処理の、負荷が
高く、なる
ため
手を、引きました

ですが
今回の、方法では
商品名の、連続性が
「キモ」です

此は
VLOOKUP等を、使われている
他の方に、付いても
同様と、思われます

ですので
Sheet1での
商品名の、ソートは
今回の、仕様上の
第1条件と
させて、頂きます

ソート無し、でも
可能では、あります
ので

ご要望が、あればお伝えください

第二条件と、しては
Sheet1の
全レコードに、対する
IDの
数値での
漏れのない、付与
です

本アプリ、では
ID欄の
数値の、数で
レコード数を、検知
しています

count(…
が
此に、当たります

COUNTA(…
に、変えても
動作、しますが

見出し分、1件
多く、捉え
処理が
無駄に、なります

また、
他の、文字記載が
あった、場合も
其の、件数分

レコードを
多く、捉え
処理が、増えます

COUNTA(…
に、変えられても
運用上
問題は、ない
と、思いますが

此の、まま
お使いの、場合は
IDは、
数値、または式で、
もれなく、付与ください

次に
本アプリは
Sheet2に、おける
商品名の、検出
ありきで
動作して、います
手動入力でも、構いませんが
商品名の、表示は
欠落させない、運用を
お願い、します

動作データ容量、ですが
Sheet１側は
3000件、等と
けちくさい事は、言わず

コンピューターの
演算能力、上限次第で
(詰まり、PCを超え、ワークステーションや、サーバーマシンでは、と、いう事)
Sheet1丸々、使えます

其の、レコード件数は
確か
10^７を、超えた筈

また、
仕様を、変更すれば
此が
何列にも、渡り
可能に、なります
ので
ほぼ無制限に、使える
で、しょう

が、
其処まで、使い尽くす
なら
アクセスに、移転して
コンパイルアプリケーションに、した方が

簡素な、PCでも
使える、ように
なります、ので
コスト減で、しょうね

Sheet２側は
取りあえず
千件分、までは
フィル済み、です

より多くの、レコードが
必要な、場合は
追加に、フィルして
ください

作業列は
A:F列中の、当該行
以外、では
使って、いない
ので

A:Fの、範囲で
行を、フィル
頂けば
其れで、構いません

所で、１つ
ご了承、頂きたい点
と、して

空欄に、見えている
箇所、にも

実は、０が
出力されて、いるものが
あります

此を、表示形式にて
#,##0.###,#;#,##0.###,#;"";@
と
0の、表示に
対し
""を、当てる事に
より
消して、います

故に
此の、Sheet2には
10や、100は、
表示、されますが
0は
表示、されません

同様の、理由で
数値には
小数点が、付加されます

此の、表示形式は
B列に、必要な
だけで
他は、ついでです

また、
B列も、含め
見た目の、問題
ですので
変えて、頂いて
動作支障は、出ません

さて、
新式の、動作原理
ですが

今までは
「条件合致する、ものに
評価対象値を、与え
他は、0に
する」
と
「条件非合致な、ものに
極大値を、与え
他は、0に
する」
を、加えて
昇順に、並べて
必要分
小さい、順に
取り出して、いました

然し
「条件合致する、ものに
評価対象値を、与え
他は、0に
する」
と、言う処理は

極大値に、評価値を
加えても
評価値が
余りに、極小で
ない、限り
極大値の、まま

1億の、上の
1京の
其の
また、上の
1垓(ガイ)が、10^20程で
ある事、から

極大値に
10^30程も、当てれば
Excelの
指数演算時、有候保持桁数を
鑑みても
十分で、有り

よって
条件合致する、ものに
評価対象値を、与え
他は、0に
する
と、いう処理は
不要！！

一律に
評価対象値を、加えれば
良い
との
新境地に、至りました

動作ですが
先に
示した、通り
先ず、B列を
評価、します

B列を、元に
求められた、仕様通り
D列を
評価、します

B列と、D列に、
合致する、E列を
求め

更に、
此の、3列に
違えない
A列、C列、F列を、
求めます

間違っても
C列の式を
D列、E列に、
転用、しては
いけません

今回
偶々、行ける
其れ、だけで

与える、データ内容
例えば
ボールペン、見積金額12000円、単価20円、
等と、いう
レコードを、想定
すれば

此は
依頼仕様、上
弾かなくては、いけない
レコードで、有り

弾けない、式転用方式
では
ぐちょぐちょに、なる事は
明白
信頼性は
皆無に、墜ちます

依頼仕様、にも
外れます

駄目ですよ？

式の、内容に
ついて、触れます
C列の、式ですが

見易い、ように
インデントを
付けた、ものが
此です

=IFERROR(
　OFFSET(
　　Sheet1!$B$4,
　　MIN(
　　　INDEX(
　　　　(
　　　　　($B5
　　　　　　<>OFFSET(
　　　　　　　Sheet1!$A$4
　　　　　　　,1
　　　　　　　,1
　　　　　　　,COUNT(Sheet1!$A:$A)
　　　　　　　,1
　　　　　　)
　　　　　)
　　　　　+($D5
　　　　　　<>OFFSET(
　　　　　　　Sheet1!$C$4
　　　　　　　,1
　　　　　　　,1
　　　　　　　,COUNT(Sheet1!$A:$A)
　　　　　　　,1
　　　　　　)
　　　　　)
　　　　　+($E5
　　　　　　<>OFFSET(
　　　　　　　Sheet1!$D$4
　　　　　　　,1
　　　　　　　,1
　　　　　　　,COUNT(Sheet1!$A:$A)
　　　　　　　,1
　　　　　　)
　　　　　)
　　　　)*10^30
　　　　+(
　　　　　ROW(
　　　　　　OFFSET(
　　　　　　　Sheet1!$B$4
　　　　　　　,1
　　　　　　　,1
　　　　　　　,COUNT(Sheet1!$A:$A)
　　　　　　　,1
　　　　　　)
　　　　　)
　　　　　-ROW(
　　　　　　Sheet1!$C$4
　　　　　)
　　　　)
　　　,
　　　,
　　　)
　　)
　　,1
　　,1
　　,1
　)
　,""
)

ご覧頂けるように
　　　　　　<>OFFSET(
　　　　　　　Sheet1!$A$4
　　　　　　　,1
　　　　　　　,1…
此の、辺りが
何度も、繰り返され
単調さを、醸しています

此の、箇所で
各条件審査を、して
一致しない、ものに
フラグを、立てて
います

其の、後に
フラグを、合計し
10^30を、掛け
フラグが
0で、ない
ものを
極大値に
跳ね上げて、います

此に、評価値
今回は、
セルの行No.を、加えて
結果を
完成させて、います

謂わば
1つの、値に
幾つもの、意味を
持たせる

と、いう
暗号化技術の、初歩を
行って、います

さて、対象外処理
ですが

A列、C列、
極大値に
では、

跳ね上げられた、値は
OFFSETの、アドレス可能範囲外に
出て、しまい
エラーを、起こします

其れを
IFERRORが、キャッチして
""に、しています

D列、E列、F列、
では、
B列中の
当該行の、値を
みて

0，または""、
で、ない
場合に
表示を、させて
います

さてさて、ほぼ以上です

追加で、何か
ご質問が
ある、場合は
其の、旨
お知らせ、下さい

如何ですか？

最後に、筆末ですが
本件に、関し
著作権放棄は、しません

理由は
過去、私が
開発した、ものを
其れと、知らず

無断で
使って、いる
方が、おられ

私の、
修正、技術更新、
目的での、
善意の、改変に
対し

ネットの、ものを
勝手に、改変して
良いのか！！
的な

異を、私に
唱えられ

「いえ、此は
私の
開発した、技術
で、有り
開発物、です、」
との、申し立てを
一切、信じなかった

と、いう経験を
経た、ため
です

直ちに
金を、要求する
等と
ケチ臭い、事は
言いませんし
無断使用も
余り、文句は
言わない
かも、知れません

然し、出典を
明らかに、して
頂きたい、事と

少なくとも
改変権を
初めと、する
全件を
放棄、して
いない事を
ご理解の、上

適正な、運用を
される、事を
お約束、頂きたい

余りに、ずさんな
場合は

私の、態度が
変わる、可能性も
お考え、下さい

因みに、
特段の断り
無きものは
過去のものも
著作権放棄を、認めません

お礼

内容を拝読させていただきましたが、まだ十分に理解できておりません。
これから時間をかけて理解を深め、いろいろ参考にさせていただこうと思います。貴重なご意見をいただきありがとうございました。

回答No.3

Nouble

出来ました
完璧かも？

所でご質問のお陰で
此のサイト初の式を完成できたかも知れません

思えば、確か
品名の、変わり目を
フェッチして
一覧を、作る
技術も、

最小値を
得る、際に
条件、非一致を
膨大な、数に
して
Smallの、スコープ対象外に
してしまう
のも、
私が、考えた事

然し、
そんな、技術は
もう
過去の、もの
と、なりました

私の、
見聞き、する
限り

此が
最新の、式です
ご笑味、あれ

Sheet2A2
=IFERROR(OFFSET(Sheet1!$A$4,MIN(INDEX((($B2<>OFFSET(Sheet1!$A$4,1,1,COUNT(Sheet1!$A:$A),1))+($D2<>OFFSET(Sheet1!$C$4,1,1,COUNT(Sheet1!$A:$A),1))+($E2<>OFFSET(Sheet1!$D$4,1,1,COUNT(Sheet1!$A:$A),1)))*10^30+(ROW(OFFSET(Sheet1!$A$4,1,1,COUNT(Sheet1!$A:$A),1))-ROW(Sheet1!$C$4)),,)),0,1,1),"")

Sheet2B2
=IFERROR(OFFSET(Sheet1!$A$4,SMALL(INDEX((OFFSET(Sheet1!$A$4,1,1,COUNT(Sheet1!$A:$A),1)=OFFSET(Sheet1!$A$4,0,1,COUNT(Sheet1!$A:$A),1))*10^8+(ROW(OFFSET(Sheet1!$A$4,1,1,COUNT(Sheet1!$A:$A),1))-ROW(Sheet1!$B$4)),,),ROW(A1)),1,1,1),"")

Sheet2C2
=IFERROR(OFFSET(Sheet1!$B$4,MIN(INDEX((($B2<>OFFSET(Sheet1!$A$4,1,1,COUNT(Sheet1!$A:$A),1))+($D2<>OFFSET(Sheet1!$C$4,1,1,COUNT(Sheet1!$A:$A),1))+($E2<>OFFSET(Sheet1!$D$4,1,1,COUNT(Sheet1!$A:$A),1)))*10^30+(ROW(OFFSET(Sheet1!$B$4,1,1,COUNT(Sheet1!$A:$A),1))-ROW(Sheet1!$C$4)),,)),1,1,1),"")

Sheet2D2
=IF(OR($B2=0,$B2=""),"",MIN(INDEX(((OFFSET(Sheet1!$A$4,1,1,COUNT(Sheet1!$A:$A),1)<>$B2)*10^8+OFFSET(Sheet1!$C$4,1,1,COUNT(Sheet1!$A:$A),1)),,)))

Sheet2E2
=IF(OR($B2=0,$B2=""),"",MIN(INDEX(((($B2<>OFFSET(Sheet1!$A$4,1,1,COUNT(Sheet1!$A:$A),1))+($D2<>OFFSET(Sheet1!$C$4,1,1,COUNT(Sheet1!$A:$A),1)))*10^30+OFFSET(Sheet1!$D$4,1,1,COUNT(Sheet1!$A:$A),1)),,)))

Sheet2F2
=IF(OR($B2=0,$B2=""),"",SUMPRODUCT(($E2=OFFSET(Sheet1!$D$4,1,1,COUNT(Sheet1!$A:$A),1))*($D2=OFFSET(Sheet1!$C$4,1,1,COUNT(Sheet1!$A:$A),1))*($B2=OFFSET(Sheet1!$A$4,1,1,COUNT(Sheet1!$A:$A)

如何ですか？

おっと！
仕様を、書かねば
後で、書いて
良いですか？

お礼

大変参考になりました。

回答No.2

bunjii

Sheet2!A2の数式が複雑になり理解できないと思います。
=IF(B2="","",MATCH(LARGE(INDEX((Sheet1!$B$5:$B$10=B2)*(Sheet1!$D$5:$D$10=LARGE(INDEX((Sheet1!$B$5:$B$10=B2)*Sheet1!$D$5:$D$10,0),COUNTIF(Sheet1!$B$4:$B$10,B2)))*Sheet1!$E$5:$E$10,0),COUNTIFS(Sheet1!$B$4:$B$10,B2,Sheet1!$D$4:$D$10,LARGE(INDEX((Sheet1!$B$5:$B$10=B2)*Sheet1!$D$5:$D$10,0),COUNTIF(Sheet1!$B$4:$B$10,B2)))),Sheet1!$E$5:$E$10,0))

Sheet2!C2はSheet2!A2の値を使えるので数式が短くできます。
=IF($A2="","",VLOOKUP($A2,Sheet1!$A$5:$E$10,COLUMNS(Sheet1!$A4:C4)))

Sheet2!D2とE2はC2をコピーすれば良いようにしてあります。

Sheet2!F2はチェック項目が3つのCOUNTIFS関数で計数できます。
=IF(A2="","",COUNTIFS(Sheet1!$B$4:$B$10,B2,Sheet1!$D$4:$D$10,D2,Sheet1!$E$4:$E$10,E2))

複数の業者を列記する場合は同じ行のG列以降へ抽出することになるでしょう。

回答No.1

msMike

》 最安単価の業者が複数ある場合、その業者数をF列に記載
その場合、列Ａ、列Ｃは最初の１社の情報だけで構わないと？
要は貴方の期待する表ができれば好いのなら、タイトル中の「MIN関数 COUNT関数」は邪魔ですね！
ソコントコ、キッチリね！

お礼

ご指摘ありがとうございます。