[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『条件に応じた表示』（daddy）
投稿の応用が効かずアドバイス願います。
（例表の説明）
・Ｌ列にＭ列の項目別の合計を入力済みです
・Ｏ列は計算式で、ある条件で求めたＭ列の同一項目内での順位が表示されます（同順位飛び）
・全体で１０００行までのデータです
（やりたいこと）
Ｎ列に、Ｏ列の順位を以下の条件に従って表示したい （関数式で）
（条件）
・上位□位までを「BV●（●は順位）」と表示する（該当するもの全て、□：後述）
・ワースト△位までを「ｂｖ●（●は順位）」と表示する（△：後述）
ただし、表示する個数には下記制限あり
※ワースト１位が△個以上の時は、ワースト1位のみ表示する
※ワースト２位～△位までで△個を超える場合は、その前の順位までを表示する
※上位□位までとワースト△位までが重複する場合は、「--」を表示する
・□、△はＬ列の合計数に応じて変わるため、別シートからLOOKUP関数で「表引き」するものとします
別シート名「TBL」の「B20～D23」セルに下表があるとしてください
B(Ｌ列) C(□) D(△) 20 5 2 1 21 8 3 3 22 12 3 3 23 16 5 4 ・上位、ワーストの何れにもに該当しない場合は「--」を表示する
（例表）
L M N O 12 a BV3 3 12 a -- 6 12 a BV1 1 12 a -- 6 12 a bv10 10 12 a -- 8 12 a bv12 12 12 a -- 4 12 a bv10 10 12 a -- 9 12 a BV1 1 12 a -- 4 16 b -- 7 16 b -- 8 ---------------------------
よろしくお願いします。
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
質問の説明がなっていないんでしょうか？・・；
（daddy） 2014/08/16(土) 23:47
12個全部が「1」とか、
11個が「1」で、1個が「12」 だったら・・・・
現実にはそんなデータはないのでしょうけど、 そのルールを適用すると、どんな結果になるのでしょうか？
（半平太） 2014/08/17(日) 17:41
お尋ねの件は正直想定してませんでしたが、以下のルールを追加、補足させていただきます。
（追加）
※順位（Ｏ列）の種類数が「ＴＢＬ」表の□と△の合計数未満の場合は“全て”「--」とする
（補足）
※上位□位までとワースト△位までが重複する場合は、「--」を表示する
→．．重複する場合は、“ワーストは”「--」とする
これにより、お尋ねのケースでは「--」表示となります。（３＋３＝６個未満なので）
よろしくお願いします。
（daddy） 2014/08/17(日) 23:17
↑は矛盾がありそうなので一旦（追加）と（補足）は取消させていただき、
追って改めて「条件」を提示させていただきます。
申訳ありません。(2014.8.18.9:03)
B(Ｌ列) C(□) D(△) 20 5 2 1 21 8 3 3 22 12 3 3 23 16 5 4
・上位□位、ワースト△位それぞれの合計数が「ＴＢＬ表」の数値を超える場合は、
“超えない順位まで”を表示する
－例－
※Ｌ列＝１２の時、上位（ワースト）１位が４個以上であれば必然的に全てが「--」表示となります
※同様の時、１位が２個、２位が２個以上であれば、表示するのは１位のみとなります
※同様の時、１位、２位が各１個で３位が２個以上であれば、表示するのは２位までとなります
・上位、ワーストの何れにもに該当しない場合は「--」を表示する
（補足）
Ｎ列に表示する目的は“差別化”にあるので、いわゆる“どんぐりの背比べ”の場合は、
敢えて何も表示しないようにしたいです。
よろしくお願いします。
（daddy） 2014/08/18(月) 10:17
１行目を挿入して、タイトルを記入
行 _L_ __M__ __N__ _O_ __P__ __Q__ _____R_____ _____S_____ 1 G数 項目 判定 値 best Worst best限界値 Worst限界値 2 12 a BV3 3 3 3 3 10
(1) P2セル =IF($L2,IF(M1=M2,P1,LOOKUP(L2,TBL!B$21:C$24)),"") (2) Q2セル =IF($L2,IF(M1=M2,Q1,LOOKUP(L2,TBL!B$21:D$24)),"")
(3) R2セル =IFERROR(IF($L2,IF(M1=M2,R1,SMALL(OFFSET(O2,0,0,L2),LOOKUP(P2,COUNTIF(OFFSET(O2,0,0,L2),"<="&ROW(INDIRECT("1:"&L2)))))),""),0)
(4) S2セル =IFERROR(IF($L2,IF(M1=M2,S1,LARGE(OFFSET(O2,0,0,L2),LOOKUP(Q2,COUNTIF(OFFSET(O2,0,0,L2),">="&(L2+1-ROW(INDIRECT("1:"&L2))))))),""),L2+1)
(5) N2セル =IF(P2="","",IF(O2<=MIN(N(R2),S2-1),"BV"&O2,IF(O2>=MAX(S2,R2+1),"bv"&O2,IF(OR(R2=0,S2>L2),"","--"))))
（半平太） 2014/08/18(月) 16:37
・「Ｏ列の値の中でＸ番目に小さい値（＝限界値）」のＸをＬＯＯＫＵＰ式で求めてると思っていますが、合ってますか？
・ＣＯＵＮＴＩＦ式で求まる配列{2;2;3;5;5;7;7;8;9;11;11;12}と上位□位（Ｐ列）からＸを求める．．で合ってますか？
・だとして、上記配列を“言葉”にするとどうなるでしょうか？
というか、何をしているのでしょうか？（ここが“モヤ～”なんです．．＾＾；）
お時間が許す時で構いませんので、よろしくお願いします。
（daddy） 2014/08/19(火) 14:44
＞・「Ｏ列の値の中でＸ番目に小さい値（＝限界値）」のＸをＬＯＯＫＵＰ式で求めてると思っていますが、合ってますか？
＞・ＣＯＵＮＴＩＦ式で求まる配列{2;2;3;5;5;7;7;8;9;11;11;12}と上位□位（Ｐ列）からＸを求める．．で合ってますか？
いずれも合っています。
＞・だとして、上記配列を“言葉”にするとどうなるでしょうか？
順位(以上) 1 2 3 4 5 6 7 8 9 10 11 12 (昇順)
実在個数配列 2 2 3 5 5 7 7 8 9 11 11 12 (昇順。上段の順位を下回ることはない)
↑ ↑ ↑ ↑ ↑ ↑ ↑ ↑
限界個数順位
直接的には「x順位以上の実在個数配列」です・・・けど、
意味するところは、 個数が単独の場合は、実在順位であると共に限界個数順位でもある。 個数が複数同じ場合、並びの左端が実在順位で、右端が限界個数順位である。
Lookupの検索値をP値(限界個数）にすると、「同じ順位」か、それが存在しなければ 「内輪の最大順位」がヒットする。その値が限界個数順位となる。
(上例でいうと、4が限界個数なら3位が限界個数順位、5なら5位が限界個数順位となる）
もやっとしている原因は、多分、実在順位での限界を求めていないからですね。
私としては、個数オーバーしない範囲と云う縛りなので、全体的にはLookupを使う流れとなっており、 実質的に同じものが求められればそれでいいのではないかと、 つまり、2個までの場合、1位を限界順位とするのが正しいでしょうが、 実在しない2位を限界順位としても結果は同じなので。。。。 ご容赦を m(__)m
（半平太） 2014/08/19(火) 21:00
これでスッキリしました！．．と書きたいのですが、まだ格闘中でして．．（苦笑）
書かれている“個々の”ご説明は理解できるのですが、全体通すと
まだ何かひっかかるといいますか、あとちょっとのところと思っていますが、
どう訊いたらいいのかすらわからないモドカシサがあります．．(_ _;)
これ以上ひっぱれないので、あとは“自らの成長”（笑）に期待しつつ
先ずはお礼とさせていただきます。
（daddy） 2014/08/20(水) 18:53
＞・だとして、上記配列を“言葉”にするとどうなるでしょうか？
対応順位 1 2 3 4 5 6 7 8 9 10 11 12 限界個数 2 2 3 5 5 7 7 8 9 11 11 12
P値(限度個数）と順位の対応表(1対多あり。無の場合は左のゾーンを適用)
限度が1個なら対応順位なし。 限度が2個なら1位と2位が対応。 ： ：
（半平太） 2014/08/20(水) 21:33
>順位→対応順位、実在個数配列→限界個数 >限度が1個なら対応順位なし。限度が2個なら1位と2位が対応。 >4が限界個数なら3位が限界個数順位（＝対応順位）、5なら5位が限界個数順位（＝対応順位）
→わかります！ どうも「実在」という文字にひっかかっていたようです（＾＾；）
>2個までの場合、1位を限界順位とするのが正しいでしょうが、実在しない2位を限界順位としても結果は同じ．．
→言わんとされている意味わかります。
ここまででＣＯＵＮＴＩＦ式内の理解として、
・対応順位をＲＯＷ関数で配列にし、その順位以下を検索条件としてＯ列（値）をカウントして限界個数の配列としている
．．でよろしいでしょうか？
と、ここまで書いてきて、見知ったハズの関数の使い方がわかってないことに気づきました．．（_ _；）
ROW(INDIRECT("1:"&L2))
・ＩＮＤＩＲＥＣＴの引数が、セル参照でない．．というか、この式単独では成立しないと思うのですが、ここのカラクリ（笑）は
どうなっているのでしょうか？
・ＣＯＵＮＴＩＦの「検索条件」にＲＯＷ以下の配列？を使用するやり方もわかるようでよくわかりません．．
いずれも「配列」がよくわかってないからだと思いますが、ネットで調べたりしていても満足できるのがヒットしません。
解説いただけると嬉しいのですが．．よろしくお願いします。
（daddy） 2014/08/22(金) 10:50
＞ここまででＣＯＵＮＴＩＦ式内の理解として、 ＞・対応順位をＲＯＷ関数で配列にし、その順位以下を検索条件としてＯ列（値）をカウントして限界個数の配列としている ＞ ．．でよろしいでしょうか？ それでいいと思います。
＞ROW(INDIRECT("1:"&L2))
＞・ＩＮＤＩＲＥＣＴの引数が、セル参照でない．．というか、この式単独では成立しないと思うのですが、
行参照です。
A1セルとかのアルファベットはあってもなくても同じ結果です。
どうせ揮発性関数ならばと、行参照にしました。
※通常は、Row(A1:A2)もROW(1:2)も 同じ{1;2}と云う配列を返すとは云うものの、
前者は2つのセルの変更時しか再計算されませんが、
後者は1～2行のどのセルを変更しても再計算されます。
なので出来ることなら、前者を使うことによって再計算に入る機会を減らすべきなのですが、
Indirectを使うとなると、どのセルが変更されても再計算になりますので(揮発性関数)、
毒を食らわば皿までもって感覚で、行参照にしております。
それより、LOOKUPの使い方はお分かりなんですよね？ 昇順に並んでいる数列が対象の場合、検索値(P値）以下の最大値をヒットする。 つまり、ジャストがあればその値(=順位)、無ければ直近の左サイドの値をヒットする。
そして、もしヒットすべき値が重複しているなら、同じ中でも、右端の値をヒットする。 ・・・と言っても同じ数値ですから、どこをヒットしたかを詮索する必要は(今回は）ありません。
（半平太） 2014/08/22(金) 12:19
>通常は、Row(A1:A2)もROW(1:2)も 同じ{1;2}と云う配列を返す．．
→これは、INDIRECT にもいえるということですね？
「揮発性関数」（不思議なネーミングですが）共々初めて知りました。
>もしヒットすべき値が重複しているなら、同じ中でも、右端の値をヒットする
→これはすっかり忘れておりました（＾＾；）
今回、自分としてはかなり深くまで教えていただいた“充実感”があり満足しています。
貴重なお時間、ありがとうございました！ これからもよろしくお願いします。
（daddy） 2014/08/22(金) 16:51
>Indirectを使うとなると、どのセルが変更されても再計算になります．．
これは「計算方法」を「手動」にしている場合も、でしょうか？
実は最近作成したブックでかなり動作が遅いのがありまして、「手動」計算にしても
他のブックと比べて大きな効果が感じられません。
数式の中で参照セルを可変にするのにＩＮＤＩＲＥＣＴ関数を多用しているからかな？
と思ってるのですが、確信がもてず．．
他の同程度の容量？（処理するセルの数、計算列の数など）に比べても明らかに遅いです。
一般論として、ご見解いただければ．．よろしくお願いいたします。
（daddy） 2014/08/23(土) 10:15
＞ >Indirectを使うとなると、どのセルが変更されても再計算になります．． ＞これは「計算方法」を「手動」にしている場合も、でしょうか？
実験してみた限りでは、一切、再計算は行われておりませんでしたけど。
＞ブックでかなり動作が遅いのがありまして
手動計算にしているのに「遅い」というのがよく分からないのですけど、
そちらでも実験してみたら如何ですか？
これを、ThisworkBookのモジュールに貼り付けて、メッセージが出るか、出ないか。
↓
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
MsgBox Sh.Name & "で再計算が発生しました"
End Sub
（半平太） 2014/08/23(土) 15:56
マクロの知識に乏しく、適当に「マクロ記録」したヤツを「編集」で記載のPrivate以下を
コピペして実行しましたが、コンパイルエラー「End Subが必要です」とメッセージが。
やり方がまずいのでしょうか？ （低レベルですみません．．（_ _；）
（daddy） 2014/08/24(日) 18:11
＞マクロの知識に乏しく、適当に「マクロ記録」したヤツを「編集」で記載のPrivate以下を
１．書き込む場所が違います。 マクロの記録で書かれる場所は「標準Module」と云う場所です。
今回はThisworkBookと云うモジュールです。
VBEの左端にプロジェクトエクスプローラが出ていますので、 その中のThisworkBookをダブルクリックして中央の白い画面に 提示したプロシージャを貼り付けてください。
＞実行しましたが
２．実行は人間がするのではなく、「人間がセルに何か入力」したとき、自動実行になります。 先ず、数式タブから「自動計算」に切り替えてから、 空いたセルに何かを入力してみてください。 「再計算が発生しました」と出てくると思います。
次に、手動に切り替えて同じ実験をしてみてください。
（半平太） 2014/08/24(日) 19:34
試したところ、「手動」の時はセル入力での再計算はなかったです。
“遅い”と感じているのは「再計算」操作それ自体と「保存」操作の時ですね。
ブックを添付できればいいんですが、例えば使用している式を列挙してどの式が“元凶”か
．．なんて見解をいただくことは可能でしょうか？
（どのような情報を開示すればアドバイスいただけるものか、よくわからないもので．．）
もし可能であれば、新たに質問を掲示したく思います．．
（daddy） 2014/08/24(日) 22:18
＞・Ｌ列にＭ列の項目別の合計を入力済みです
正直いって、これを算出した数式がどんなものなのか、気にはなっていました。 けど、余計なことを詮索するとギクシャクすることもあるのでスルーしました。
＞ブックを添付できればいいんですが、例えば使用している式を列挙してどの式が“元凶”か ＞．．なんて見解をいただくことは可能でしょうか？ ＞ （どのような情報を開示すればアドバイスいただけるものか、よくわからないもので．．）
数式の列挙とその数式が埋められている範囲（セル数とほとんど同意)
対象データを昇順で並替えしてもいいのか否か。 (項目別には固まっているが、各項目内の並びはランダムのままじゃないと使いにくいのかどうか）
M列の項目別個数の最多数は決まっているのか否か。 (B列の最大値と同じ？）
そんな情報があれば答えられるかもです。
いずれにしても、テーマが違い過ぎるので、新たに質問を立ててください。
（半平太） 2014/08/25(月) 10:51
>そんな情報があれば答えられるかもです。 >いずれにしても、テーマが違い過ぎるので、新たに質問を立ててください。
何だか“脈がありそう”なので、キチンと説明できるように整理して
テーマアップさせていただこうかと思います。
これからもよろしくお願いします。
（daddy） 2014/08/25(月) 12:51
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.