いつもお世話になります。
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ベストアンサー
- 回答日時:
>上の数式は 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.15
- 回答日時:
>それぞのセルにご指導の数式を下記のように入力した結果、
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
の表示になります。
私の提示した数式はAA4が0になることと関係がないと思うのですが・・・・
#ひとまず本題の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
- 回答日時:
これでどうかな?
「月別」シートに作業列を作ります。
#例として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
- 回答日時:
>試しました。
結果は
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
- 回答日時:
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
- 回答日時:
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
- 回答日時:
>下記の数式で試させていただきました。
=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
- 回答日時:
No.2です。
補足の
>AC4 より以下が空白になります。
>AC4 を下にオートフィルするとちゃんとした値が表示されます
の件ですが、
質問文の「参考」にすでにAC列の数式はお示しですよね?
アップされている画像まではちゃんとできている!という前提で回答しました。
せっかくAC・AD列もご自身で数式をお考えになり結果を出していらっしゃいますので
こちらで余計なお世話を焼いては失礼かと思い
すでに表示されている結果を利用して、
もう1列作業列を設ける方法を提案したまでです。m(_ _)m
No.8
- 回答日時:
>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位の順位が表示されます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excel 複数列のある文字を優先して1列に表示したいです 2 2022/12/03 12:07
- Excel(エクセル) エクセルVBAでオブジェクトが必要です 2 2022/09/10 16:37
- Visual Basic(VBA) Excel VBAの解読について質問があります。 概要は、マクロでチェックボックスにチェックすると日 1 2023/02/10 07:50
- Visual Basic(VBA) 【VBA】特定のワードが入っている行全体を塗りつぶしたい 4 2022/04/20 15:22
- Visual Basic(VBA) VBAコードが作動せず、どこに問題があるのか教えて下さい。 3 2023/06/13 13:20
- Visual Basic(VBA) 列と行の名前(重複あり)が交差するセルに、データを入力したい 2 2022/06/25 22:42
- Excel(エクセル) 週毎の集計 2 2023/08/04 16:56
- Ruby pandasでsqlite3にテーブル作成・追加・読み出しでindexの取り扱い方教えてください 5 2023/03/08 09:57
- その他(Microsoft Office) エクセルの数式で教えてください。 2 2023/01/12 13:51
- Visual Basic(VBA) 列と行の名前(重複あり)が交差するセルに、データを入力したい 3 2022/06/12 11:17
このQ&Aを見た人はこんなQ&Aも見ています
-
性格の違いは生まれた順番で決まる?長男長女・中間子・末っ子・一人っ子の性格の傾向
同じ環境で生まれ育っても、生まれ順で性格は違うものなのだろうか。家庭教育研究家の田宮由美さんに教えてもらった。
-
Excel MATCH関数で検索範囲内に同じ値の検索値が複数ある場合
Access(アクセス)
-
EXCEL関数:重複した値がある並び替えについて
Excel(エクセル)
-
エクセルで同率の順位の人が表示されない
Excel(エクセル)
-
-
4
ある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?
Excel(エクセル)
-
5
エクセルでの順位に同率がある場合の対処について
Excel(エクセル)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
在庫管理表に使うエクセルの関...
-
下記マクロでMsgBox "空白です...
-
REGEXREPLACE関数について、
-
エクセルでバーコード作成し使...
-
【マクロ】for next構文について
-
ユーザー定義関数をアドイン登...
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
エクセルで表
-
PDFの請求明細をエクセルにしたい
-
エクセルの関数について教えて...
-
Excelデータをコピペして、ペー...
-
スプレッドシート、Excelでの数...
-
LOOKUP関数を使えばいいのでし...
-
Excel VBについての質問です。
-
【マクロ】 IFERROR関数をマク...
-
エクセル日付 文字列の関数がエ...
-
【マクロ】読取専用のファイル...
-
【マクロ】その時、その時で変...
-
時間によってファイル名が変わ...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルでの作業計算方法について
-
時間によってファイル名が変わ...
-
【関数】適切な文字数の数字を...
-
Excelについて教えてください
-
エクセル初心者です 関数の入れ...
-
【マクロ】ファイル名の変更に...
-
UNIQUE関数が使えないバージョ...
-
エクセルの計算
-
【関数】先頭だけにある、半角...
-
Excelで、決まった行を繰り返し...
-
Excelでセルの値が同じか...
-
LOOKUP関数を使えばいいのでし...
-
Excel
-
はがきについて。
-
エクセルの条件付き書式につい...
-
エクセルのデーターが2か月前の...
-
エクセル②
-
エクセルで「-0.0」と表示さ...
-
Microsoft1Officeの互換ソフト...
おすすめ情報