MATCH LARGE などで重複を防ぐには

質問

いつもお世話になります。
WIN7 EXCELL2010 です。

次のような問題点があります。
シート「記入」から　シート「月別」AC列　AD列を作業列に。
この時、例えば　シート「月別」AD6　AD7 に同じ金額の20,000があり
Z5　Z6　に「佐々木」が重複し　「星野」が表示されません。

これを防ぐ方法で何かいい解決策はありませんでしょうか。
ご指導いただければ幸甚です。
よろしくお願いします。

参考
Y4 =IF(OR(記入!C2="",AA2=0),"",COUNTA(記入!$C$2:$C2))
Z4 =IF(COUNTIF($AC$4:$AC$100,"?*")<ROW(G1),"",INDEX(AC$4:AC$100,MATCH(LARGE($AD$4:$AD$100,ROW(G1)),$AD$4:$AD$100,0)))
AA4 =IF(AC4="","",SUMIF($AC$3:$AC$100,Z4,$AD$3:$AD$100))
AC4 =IF(COUNT(記入!$G$1:$G$3000)<ROW($A1),"",INDEX(記入!$C$1:$C$3000,SMALL(記入!G$1:G$3000,ROW(A1))))
AD4 =IF(AC4="","",SUMPRODUCT((記入!$B$2:$B$3000>=DATEVALUE($AB$2))*(記入!$B$2:$B$3000<DATEVALUE($AH$2))*(記入!$C$2:$C$3000=AC4),記入!$D$2:$D$3000))

回答No.16

MackyNo1 · ベストアンサー

>上の数式は　A が抜けていると私なりに考えて下記のようにしました。
=INDEX($AC$4:$AC$100,MATCH(LARGE(IF($AD$4:$AD$100="",0,$AD$4:$AD$100)+ROW($AD$4:$AD$100)/10000,ROW(1:1)),IF($AD$4:$AD$100="",0,$AD$4:$AD$100)+ROW($AD$4:$AD$100)/10000,))&""
#N/A が出ます。

ご指摘のようにセル範囲が間違えていました。
配列数式ですので、数式入力後、CtrlキーとShiftキーを押しながらEnterで確定してください（こちらでも検証し、文字列が含まれていてもうまく表示できることを確認してます）。

お礼

上手くできました。
本当に大変な思いでご指導いただき誠に有難うございました。
作業列がないのが良いですね。

回答No.17

mt2008

> 「他の式にも色々と手を加えたい箇所がありますが」
> 欲張り高望みですがご指導いたたけませんでしょうか

AB2やAH2に文字列で日付を入れずに日付のシリアル値を入れた方が良いとか、AD列の式はSUMIFSを使った方がスッキリする等です。
＃本日多忙につき、具体的な式を挙げることが出来ず、この程度で失礼。

お礼

本当に大変な思いで御指導いただき誠に有難うございます。
AB AH AD列は私なりに頑張ります。

回答No.15

MackyNo1

>それぞのセルにご指導の数式を下記のように入力した結果、
Y4 =RANK(LARGE(記入!$D$1:$D$3000,ROW(A1)),記入!$D$1:$D$3000)
#NUM!　表示される

記入!D1:D3000のデータは数字なのでしょうか？

＞Z4 =INDEX($AC$4:$AC$100,MATCH(LARGE($AD$4:$AD$100+ROW($4:$100)/10000,ROW(1:1)),$AD$4:$AD$100+ROW($AD$4:$AD$100)/10000,))
#VALUE!　表示される

私のところでは（通常の金額が入力されているなら）問題なく大きい順に名前が表示されますが、#VALUE!エラーが出るということは範囲に文字列が混入しているパターンと思われます。
AD列の数字が数式で表示している場合は、空白文字列が混入することになりますので、以下のように数式を変更してみてください。

=INDEX($C$4:$C$100,MATCH(LARGE(IF($D$4:$D$100="",0,$D$4:$D$100)+ROW($D$4:$D$100)/10000,ROW(1:1)),IF($D$4:$D$100="",0,$D$4:$D$100)+ROW($D$4:$D$100)/10000,))&""

ちなみに私の提示した数式は、N014の方の回答で補助列なしに金額の大きい順に名前を並べ替える数式です。

＞次に　シート「記入」より　顧客名/売上　を入力すると
Y4 1
Z4 #N/A
AA4 0
の表示になります。

私の提示した数式はAA４が０になることと関係がないと思うのですが・・・・

＃ひとまず本題のZ4セルの数式だけ検証して、正しい値（重複のない名前）が返るか調べてみてください。

補足

ご指導を感謝しています。
補足します。

記入!D1:D3000のデータは数字なのでしょうか？
数値です　ユーザー定義でも　数値　に確認しています。
念のために　AC 標準　AD 数値　ユーザー定義です

AD列の数字が数式で表示している場合は、空白文字列が混入することになりますので、以下のように数式を変更してみてください。
=INDEX($C$4:$C$100,MATCH(LARGE(IF($D$4:$D$100="",0,$D$4:$D$100)+ROW($D$4:$D$100)/10000,ROW(1:1)),IF($D$4:$D$100="",0,$D$4:$D$100)+ROW($D$4:$D$100)/10000,))&""
上の数式は　A が抜けていると私なりに考えて下記のようにしました。
=INDEX($AC$4:$AC$100,MATCH(LARGE(IF($AD$4:$AD$100="",0,$AD$4:$AD$100)+ROW($AD$4:$AD$100)/10000,ROW(1:1)),IF($AD$4:$AD$100="",0,$AD$4:$AD$100)+ROW($AD$4:$AD$100)/10000,))&""
#N/A が出ます。

回答No.14

mt2008

これでどうかな？
「月別」シートに作業列を作ります。
＃例としてAE列を作業列に使用した場合の式です。

AE4　=IF(AD4="","",AD4+ROW()/100)
Z4　=IFERROR(INDEX(AC$4:AC$100,MATCH(LARGE($AE$4:$AE$100,ROW(G1)),$AE$4:$AE$100,0)),"")

他の式にも色々と手を加えたい箇所がありますが、とりあえず問題となっている箇所のみとします。

補足

御指導ありがとうございます。
今まで試していました。
お陰様で問題が解決しました。
かなりの労をかけて申し訳ありません。
本当にありがとうございました。

「他の式にも色々と手を加えたい箇所がありますが」
欲張り高望みですがご指導いたたけませんでしょうか
もしお差し支えなければ有難いのですが。

回答No.13

MackyNo1

>試しました。
結果は
1
Z4 空白
Z5～　#VALUE
2

確認ですが、参照するセルは添付画像の通りなのですよね。
こちらでは実際にAC4セル以下に名前、AD4セル以下に金額データを入力して、検証して正常に値が返ることを確認しています。

＞AA AB 列の値も消えてしまいました。

私の提示した数式はこれらの列には何も影響を及ぼしません。
提示した数式は、単純に名前を金額のセルを参照し、金額が大きい順に（同じ金額がある場合でも）名前を表示する関数です。
煩雑な数式をたくさん使用されているので、何か勘違いの操作をしていないか落ち着いて確認してください（別のシートにAC4:AD100セルの値をコピーして、ご希望の表示ができることを確認してみてください）。

補足

私のためにご迷惑かけます。

新たに作り直して試しました。
それぞのセルにご指導の数式を下記のように入力した結果、
Y4 =RANK(LARGE(記入!$D$1:$D$3000,ROW(A1)),記入!$D$1:$D$3000)
#NUM!　表示される

Z4 =INDEX($AC$4:$AC$100,MATCH(LARGE($AD$4:$AD$100+ROW($4:$100)/10000,ROW(1:1)),$AD$4:$AD$100+ROW($AD$4:$AD$100)/10000,))
#VALUE!　表示される

次に　シート「記入」より　顧客名/売上　を入力すると
Y4 1
Z4 #N/A
AA4 0
の表示になります。

AC AD 正常に表示されます。

回答No.12

MackyNo1

No11の回答の数式（セル参照）に誤りがありましたので以下のように訂正してください。

=INDEX($AC$4:$AC$100,MATCH(LARGE($AD$4:$AD$100+ROW($4:$100)/10000,ROW(1:1)),$AD$4:$AD$100+ROW($AD$4:$AD$100)/10000,))

配列数式なのでCtrl+Shift+Enterで確定してください。

補足

何度もすみません。
捕捉するのが辛くて本当に申し訳ありません。

試しました。
結果は
1
Z4 空白
Z5～　#VALUE
2
AA AB 列の値も消えてしまいました。

回答No.11

MackyNo1

AC列に表示されているデータのレイアウトを少し勘違いして回答したようなので（元の数式を利用としたため）、単純に以下のようにその範囲のデータで、金額の大きい順に名前を並べ替える数式にするほうがよいようです。

AC列とAD列に表示されている名前を値の大きい順に並べ替えたいなら、以下の数式を入力して下方向にオートフィルしてみてください(適宜エラー処理をしてください）。

=INDEX($AC$4:$AC$100,MATCH(LARGE($AD$4:$AD$100+ROW($4:$100)/10000,ROW(1:1)),$D$4:$D$100+ROW($AD$4:$AD$100)/10000,))

補足

何度もすみません。
御指導の数式を入力してみました。

結果は　空白　になりました。

回答No.10

MackyNo1

>下記の数式で試させていただきました。

=IF(COUNTIF($AC$4:$AC$100,"?*")<ROW(G1),"",INDEX(AC$4:AC$100,MATCH(LARGE($AD$4:$AD$100,ROW(G1))+COUNTIF($Y$4:Y4,Y4)-1,$AD$4:$AD$100,0)))

やはり問題の解決にはなりませんでした。
同じ　合計　があるとき　顧客名は　同じになります。

Y列の順位を示す関数も、No3で回答したように修正したのでしょうか？（同じ金額の場合は同じ順位になっていますか）

補足

お忙しいところを私のためにご指導有難うございます。
すこし諦めかけていたところ元気がでます。

Y4
=RANK(LARGE(記入!$D$1:$D$3000,ROW(A1)),記入!$D$1:$D$3000)
Z4
=IF(COUNTIF($AC$4:$AC$100,"?*")<ROW(G1),"",INDEX(AC$4:AC$100,MATCH(LARGE($AD$4:$AD$100,ROW(G1))+COUNTIF($Y$4:Y4,Y4)-1,$AD$4:$AD$100,0)))

確実に入れました。
その結果、
参照図で言いますと　シート「月別」　Z6(佐々木) 　に　(星野)が表示されなければならないのに　「#N/A」　が表示されます。

回答No.9

tom04

No.2です。
補足の
＞AC4 より以下が空白になります。
＞AC4 を下にオートフィルするとちゃんとした値が表示されます

の件ですが、
質問文の「参考」にすでにAC列の数式はお示しですよね？
アップされている画像まではちゃんとできている！という前提で回答しました。
せっかくAC・AD列もご自身で数式をお考えになり結果を出していらっしゃいますので
こちらで余計なお世話を焼いては失礼かと思い
すでに表示されている結果を利用して、
もう1列作業列を設ける方法を提案したまでです。m(_ _)m

回答No.8

bunjii

>AC列、AD列の検証も必要であればシート「記入」のG列の値を開示してください。
シート「記入」のG2の値=IF(COUNTIF($C$2:C2,C2)=1,ROW(),"") と言うことでしょうか？
質問の貼付画像のAB2とAH2に文字列として日付の情報が読み取れませんがどのようになっているのでしょうか？
AB2以上でAH2未満の日付（シリアル値）に該当する売上の合計と言うことであればAB2セルとAH2セルに的確な値があれば問題ないようです。
実際のデータでAC列とAD列が正しく表示されているか否かはあなた自身で確認できます。
誤りがあれば原因を自主的に追及してください。

Z列とAA列については回答No.7で再確認してください。
尚、Y列についてはRANK関数に変更して引数をAA列にするよう訂正された方が良いでしょう。
AA4=RANK(AA4,AA$4:AA$100,0)
提示の模擬データでは1位、2位、2位、4位、5位の順位が表示されます。

回答No.7

bunjii

>シート「記入」で1件目を入力した時は　Z4 は空白　
> 2件目を入力すると　Z4 Z5 は　#VALUE　になります。
回答の1部に誤りがありました。
ROW(G2） → ROW(G1)

=IF(COUNTIF($AC$4:$AC$100,"?*")<ROW(G1),"",INDEX(AC$4:AC$100,MATCH(LARGE(($AD$4:$AD$100)+1-(ROW(A$4:A$100))/100,ROW(G1)),($AD$4:$AD$100)+1-(ROW(A$4:A$100))/100,0)))
上記は提示の数式を添削した結果です。
しかし、当方での検証ではシート「記入」に関しては参照していませんのでエラーの原因と別問題です。

尚、AC列、AD列の数式については今回の質問とは関連が無いものとして提示の画像からAC列とAD列の値を手入力しています。
AC列、AD列の検証も必要であればシート「記入」のG列の値を開示してください。

補足

AC列、AD列の検証も必要であればシート「記入」のG列の値を開示してください。

=IF(COUNTIF($C$2:C2,C2)=1,ROW(),"")