[[20150302000524]] 『F列の番号の個数(合計数)を表示させたい』(ハワイ) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『F列の番号の個数(合計数)を表示させたい』(ハワイ)

すみませんが教えて下さい。

具体例を記します。

   (F列)     (G列)     (I列)
1  1−2     −3.6    3番4個
2  3−99   −11.1   99番3個
3  21−55  +10.0   22番2個
4  5−45   −10.3    4番1個
5  3−22   −79.1    5番1個
6  3−99   −19.4   45番1個
7  1−79    −7.8
8  8−9    +59.8
9  3−22  −172.0
10 4−99   −21.0

上記の例で言いますと、
「2、4、5、6、9、10」の6つがワースト6つになります。

ワーストと言うのは、G列のマイナスの大きい順のワーストです。

この6つのワーストの、F列の番号の個数(何個あるのか?)を
表示させたいのです。例:I列。

G列の数字(−3.6や、−11.1など)を見て頂くと分かると思うのですが、
最もマイナスが大きいのは、上から9番目の「−172.0」になります。

そして、この「−172.0」のすぐ左横(F列)にある、文字列を見ると、
「3−22」と記載があります。この時点で、「3番が1個」、
「22番が1個」選ばれたことになります。

そのようにして、G列のマイナスの「大きいほうから順に」6つ選択します。
そうすると、上から順番に書きますと
「2、4、5、6、9、10」の6つがワースト6つになります。

ただしF列で「3−3」のこともあるので、その場合は、1通りにもかかわ
らず2個になります。

下記までやってみたのですが、具体例のようには表示されませんでした・・・。

=ROW()&"番"&
SUMPRODUCT((RANK($F$2:$F$29,$F$2:$F$29,1)<=6)*(LEFT($G$2:$G$29,1)*1=ROW()))+
SUMPRODUCT((RANK($F$2:$F$29,$F$2:$F$29,1)<=6)*(RIGHT($G$2:$G$29,1)*1=ROW()))&"個"

表示させたい場所はI列の「I16」から「I21」になります。

なにぶんあまり詳しくないため、数式等を、「どこ」と「どこ」
のセルに入力するのかなど、具体的に指示して頂くと大変助かります。

お手数をお掛けしますが、アドバイスの程よろしくお願い致します。

< 使用 アプリ:EXcel2000、使用 OS:WindowsXP >


 たとえば、G列最小値 -100として、これが6つあった場合、-100の6つだけ対象なの?
 -100を「1個」として処理するの?
 
(GobGob) 2015/03/02(月) 07:49

 あと、I列の並びは「多い順」になるの?
 
(GobGob) 2015/03/02(月) 08:08

 数式長くなりそう・・・ ○個●番 でいいなら

 =IFERROR(SUBSTITUTE(SUBSTITUTE(TEXT(LARGE(INDEX((0&REPT(FREQUENCY((0&TRIM(MID(SUBSTITUTE(REPT(F$2:F$29,COUNTIF(G$2:G$29,"<="&G$2:G$29)+((G$2:G$29="")*7)<7),"-",REPT(" ",20)),{1,20},20)))*1,ROW($1:$101)-1),ROW($1:$102)-1>0))+(ROW($1:$102)-1)/1000,),ROW(A1)),"[<1]#;0個.000番"),".00",),".0",),"")

 下へコピー。

 ※A-BのA,Bは 0以上、100未満のこと。
 ※G列ダブりなしのこと。
 
(GobGob) 2015/03/02(月) 08:50 ⇒ 9:54修正

 >使用 アプリ:EXcel2000

 あっ。。。 Excel2000っすか・・・
 IFERROR無しでよろしくです。

 ※てか、ネストでNGかも知れませんな。
 
(GobGob) 2015/03/02(月) 08:53

 関数が希望みたいですが、私には作業列使ってもできなかった/^o^\

 色々考えたけど、すごくめんどくさいですね。
 力技案
    Sub test()
        Dim tbl
        Dim sml
        Dim i As Long
        Dim x
        Dim c
        Dim dic As Object
        Dim ans As Object
        Dim cnt As Object
        With Range("G1", Range("G" & Rows.Count).End(xlUp))
            sml = Evaluate("TRANSPOSE(MATCH(SMALL(" & .Address & ",ROW(1:" & .Rows.Count & "))," & .Address & ",0))")
            tbl = .Offset(, -1).Resize(, 2).Value
        End With
        Set dic = CreateObject("Scripting.Dictionary")
        Set ans = CreateObject("Scripting.Dictionary")
        Set cnt = CreateObject("Scripting.Dictionary")

        '番号の個数をカウント
        For i = 1 To UBound(tbl, 1)
            For Each x In Split(tbl(i, 1), "-")
                If dic.exists(x) Then dic(x) = dic(x) + 1 Else dic.Add x, 1
            Next x
        Next i

        'ワースト1〜6までの番号をansに入れる
        For i = 1 To 6
            For Each x In Split(tbl(sml(i), 1), "-")
                If Not ans.exists(x) Then ans.Add x, dic(x)
            Next x
        Next i

        '個数別に番号を入れる
        For Each x In ans.keys
            If cnt.exists(ans(x)) Then cnt(ans(x)) = cnt(ans(x)) & "-" & x Else cnt.Add ans(x), x
        Next x

        '個数が大きい順にansに入れ直す
        ans.RemoveAll
        For i = 1 To cnt.Count
            c = Application.Large(cnt.keys, i)
            For Each x In Split(cnt(c), "-")
                ans.Add x, x & "番" & c & "個"
            Next x
        Next i
        Range("I:I").ClearContents
        Range("I1").Resize(ans.Count).Value = Application.Transpose(ans.items)
    End Sub
(稲葉) 2015/03/02(月) 10:59

皆様、ご回答ありがとうございました。

不明点をご指摘されましたので、補足させて頂きます。

>たとえば、G列最小値 -100として、これが6つあった場合、-100の6つだけ対象なの?
>-100を「1個」として処理するの?

同じ数字が6つ(6通り)あった時ですが、(−100が6通りなど)、
その時も、従来通り「ワースト6通り」だけを選択させたいもので
すから、「−100の6通りだけ」が対象となります。

その場合でも、F列の数字は合計で12個選ばれることになります。

>あと、I列の並びは「多い順」になるの?

I列の並びは数(個数)が「多い順」になります。
また、個数が同じ時は、番号が若い順に表示させます。

例:2番4個
  5番4個
  7番4個

数字が入っているセルは
「G2」から「G29」と
「F2」から「F29」
になります。

1行目(G1)と(F1)は、国名や人名といった文字列(漢字)が入っており、
数字は全く入っていません。

数式等を書いて下さり、とても感謝しております。
時間がないため、まだ検証できていません。
確認次第、結果をご報告いたします。
(ハワイ) 2015/03/03(火) 03:31


 では、 G列が

 -100
 -90
 -80
 -70
 -60
 -50
 -50
 -50
 -50
 -50
 -40

 と、6番目がたくさんあった場合、どうすんの?

 あと、F列の数値は 1〜99でいいの?
 
(GobGob) 2015/03/03(火) 08:11

 Excel2003以前の環境がないんでネストでNGかも?

 =TEXT(MOD(LARGE(INDEX(FREQUENCY((0&TRIM(MID(SUBSTITUTE(REPT(F$2:F$30,(G$2:G$30<=SMALL(G$2:G$30,6))*(G$2:G$30<>"")),"-",REPT(" ",5)),{1,5},5)))*1,ROW($1:$101)-1)*(ROW($1:$102)-1>0)+((ROW($1:$102)-1)/1000),),ROW(A1))*1000,1000),"[>99]"""";0番")&
  TEXT(LARGE(INDEX(FREQUENCY((0&TRIM(MID(SUBSTITUTE(REPT(F$2:F$30,(G$2:G$30<=SMALL(G$2:G$30,6))*(G$2:G$30<>"")),"-",REPT(" ",5)),{1,5},5)))*1,ROW($1:$101)-1)*(ROW($1:$102)-1>0),),ROW(A1)),"[<1]#;0個")

 ※A-B のAとBは 1〜99の範囲。
 ※6番目の数値が複数の場合は全て反映。
 
(GobGob) 2015/03/03(火) 09:48

 >また、個数が同じ時は、番号が若い順に表示させます。 

 あら、見逃し。。。 またややこしい条件がw。
(GobGob) 2015/03/03(火) 10:10

 やっぱりネストが怪しいけど。。。

 =TEXT(LARGE(INDEX(FREQUENCY((0&TRIM(MID(SUBSTITUTE(REPT(F$2:F$30,(G$2:G$30<=SMALL(G$2:G$30,6))*(G$2:G$30<>"")),"-",REPT(" ",5)),{1,5},5)))*1,ROW($1:$101)-1)*(ROW($1:$102)-1>0),),ROW(A1)),"[<1]#;!"&101-MOD(LARGE(INDEX(FREQUENCY((0&TRIM(MID(SUBSTITUTE(REPT(F$2:F$30,(G$2:G$30<=SMALL(G$2:G$30,6))*(G$2:G$30<>"")),"-",REPT(" ",5)),{1,5},5)))*1,ROW($1:$101)-1)*(ROW($1:$102)-1>0)+((102-ROW($1:$102))/1000),),ROW(A1))*1000,1000)&"番0個")

 ※考えてる内に、Excel2007以降のIFERRORは便利すぎるなぁ・・・と思ってきたw。
 
(GobGob) 2015/03/03(火) 10:24

GobGob様、及び ご回答を下さった方々ありがとうございます。
多忙のためお返事が遅くなり申し訳ございません。

>では、 G列が
> と、6番目がたくさんあった場合、どうすんの

考えていませんでした。。おっしゃる通りです。

>※6番目の数値が複数の場合は全て反映

全て反映で大丈夫です。

>あと、F列の数値は 1〜99でいいの?

はい、1〜99の範囲で大丈夫です。3桁になることはありません。

>あら、見逃し。。。 またややこしい条件がw。

すみません。

03/03(火) 03:31の投稿でお示ししました通り、データ(数字)が入っている
セルは、
「G2」から「G29」と
「F2」から「F29」
になります。

他のセルには入っていません。
なので、G列(28通り)・F列(28通り)の「28通りづつ」になります。

GobGob様の「03/03(火) 10:24」の投稿分の「=TEXT(LARGE(INDEX・・・」を
自分のExcel2000に入力してみました。

入力する場所は、左上の「C列の真上」にある「=」と記されている
「右側の中(バーの中)」で正しいでしょうか?

入力した所、「入力した数式にはエラーがあります。」と表示されました。

自分の上記のやり方で正しかったでしょうか?
もし、操作方法が間違っていましたら、ご指摘頂きたいと思います。

>やっぱりネストが怪しいけど。。。

ネスト(入れ子)ですが、Excel2003以前では最大7個ですが、
Excel2007,Excel2010では64個となっているらしいですね。
正しく動作しないのは、それが原因でしょうか?

もしExcel2007以降で、正常に動作するようなら、私のExcel2000では
この数式は使うことはできないということになるのでしょうか・・・。

お手数ですがご回答のほどよろしくお願い致します。
(ハワイ) 2015/03/04(水) 23:54


 ん〜。 まず。

 >入力する場所は、左上の「C列の真上」にある「=」と記されている 
「右側の中(バーの中)」で正しいでしょうか? 

 ってか、I列に結果を出すんだよね? I列結果を出す先頭列セルで入力となるね。

 >入力した所、「入力した数式にはエラーがあります。」と表示されました。 

 やっぱりネストだね。

 Excel2003以前で試してないけど。。。

 ●STEP 1

 挿入 ⇒ 名前 ⇒ 名前の定義 で

 名前を 入れ子
 数式を 
   =FREQUENCY((0&TRIM(MID(SUBSTITUTE(REPT($F$2:F$29,$G$2:$G$29<=SMALL($G$2:$G$29,6)),"-",REPT(" ",5)),{1,5},5)))*1,ROW($1:$101)-1)*(ROW($1:$102)-1>0)

 と入力し、OK。

 ●STEP 2

 I列先頭セルに

 =TEXT(LARGE(INDEX(入れ子,),ROW(A1)),"[>0]"""&INT(101-MOD(LARGE(INDEX(入れ子+((102-ROW($1:$102))/1000),),ROW(A1))*1000,1000))&"""番0個;")

 下へコピー。

 ※名前定義ならINDEXいらないかもね。
 
(GobGob) 2015/03/05(木) 08:35 ⇒ 8:39修正

GobGob様、及び ご回答を下さった方々アドバイスありがとうございます。

作成して下さった数式を早速自分のExcel2000に入力してみました。
                                

●STEP 1 OK。

●STEP 2 で I列先頭セル(この場合I列の「I16」と受け取りました)
 に入力しました。

すると、「#N/A」と表示されました。

他にも複数のセル(「I1」や「J6」など)にも入力したのですが、結果
は同じでした。

それで、表示された「#N/A」をダブルクリックすると、エクセル画面
上に、青い細い線が出てきて、「F2からF29」と「G2からG29」
を範囲選択しています。範囲選択されたセルの中にデータ(数字)が入って
いるため、選択されている(青い線がかこっている)範囲は正しいことに
なります。

>※名前定義ならINDEXいらないかもね。

アドバイスに従いまして、数式の中の「INDEX」を削除して入力してみま
した。

「INDEXを削除したもの」
=TEXT(LARGE(入れ子,),ROW(A1)),"[>0]"""&INT(101-MOD(LARGE(入れ子+((102-ROW($1:$102))/1000),),ROW(A1))*1000,1000))&"""番0個;")

「INDEX」を削除した上記の数式をセルに入力したところ、数式がその
ままセル上に表示されました。「#N/A」にはなりません。
それで、そのセル(数式が表示されているセル)をダブルクリックしても、
「F2からF29」と「G2からG29」は範囲選択されませんでした。
青い線は全くでてきません。

となると、「INDEX」は削除してはいけないのかな・・・と思いました。

「INDEX」の削除の仕方は、上記で正しいでしょうか?
もし、削除の仕方が間違っていましたらご指摘下さい。
                              
                               
>ってか、I列に結果を出すんだよね? I列結果を出す先頭列セルで入力となるね。

認識不足で申し訳ございません。

>やっぱりネストだね。

そうかも知れません。

私のExcelの操作方法に何か間違いがあるようでしたら、遠慮なくご指摘
頂きたいと思います。 私のほうが何か間違っている可能性もあるため。
                                
                                

Excelの本で調べてみたのですが、「VLOOKUP関数では、検索した結果、
該当する値がない場合も『#N/A』が表示されます。」と記載があり
ました。

それでふと思ったのですが、F列の数字の分類が「ユーザー定義」に
なっており、「種類」が「m−d」になっています。
これが原因で「#N/A」と表示されてしまうのでしょうか?

申し遅れていましたが、一番初め(一番上)の「具体例」を見て頂くと
分かると思うのですが、F列の数字は、必ず左側が小さくなります。

ハイフン(−)をはさんで、右側の数字は左側より大きくなります。
ただしF列で「3−3」のこともあるので、同じ数字のこともありま
すが、右側の数字が左側の数字より大きくなることはありません。

また、そうならないようにわざわざ「種類」で「m−d」を指定して
います。

これが、「#N/A」と表示されてしまうことと何か関係があるでし
ょうか?

GobGob様が丁寧に数式を作成して下さっているにもかかわらず、なか
なか表示させることができずに申し訳ございません。

どのようにすれば良いか、対処方法をお教え下さい。
よろしくお願い致します。
(ハワイ) 2015/03/05(木) 23:09


 >ハイフン(−)をはさんで、右側の数字は左側より大きくなります。 
 >ただしF列で「3−3」のこともあるので、同じ数字のこともありますが、
 >右側の数字が左側の数字より大きくなることはありません。 

 >また、そうならないようにわざわざ「種類」で「m−d」を指定しています。 

 ここ、ちょっと意味が分からないのですけど、
 参考までに、冒頭掲示されたデータは、セルの書式を標準にすると何になるんですか?(変わるんですか?)

    (F列)    → F列の種類をm-dから標準に戻すと?
  1  1−2   →
  2  3−99  →
  3  21−55 →
  4  5−45  →
  5  3−22  →
  6  3−99  →
  7  1−79  →
  8  8−9   →
  9  3−22  →
  10 4−99  →

(半平太) 2015/03/05(木) 23:50


 そりゃ日付表示形式と文字列が混在したら無理ですわ。

 てか、なんでF列1つで処理する仕様にするんでしょうな。E、F列に別けて大小は入力規則かなんかで
 処理すればいいのに。

 まぁ、さらにめんどくさくなってるのは確かなんで。。。

 名前定義 「入れ子」 の参照範囲変更

 =FREQUENCY((0&TRIM(MID(SUBSTITUTE(TEXT(REPT(ASC($F$2:$F$29),$G$2:$G$29<=SMALL($G$2:$G$29,6)),"m-d"),"-",REPT(" ",5)),{1,5},5)))*1,ROW($1:$101)-1)*(ROW($1:$102)>1)

 数式

 =TEXT(LARGE(入れ子,ROW(A1)),"[>0]"""&101-MOD(INT(LARGE(INDEX(入れ子+(102-ROW($1:$102))/1000,),ROW(A1))*1000),1000)&"""番0個;")

 ※INDEXは1個目だけっす。説明不足でした。
 ※F列実際は半角文字ならASC関数不要。
(GobGob) 2015/03/06(金) 08:19

 一応結果。

		F		G	H	I
1		データ		数値		個数
2		1月2日		-3.6		3番4個
3		3−99	-11.1		99番3個
4		21−55	10		22番2個
5		5−45	-10.3		4番1個
6		3月22日		-79.1		5番1個
7		3−99	-19.4		45番1個
8		1−79	-7.8		
9		8月9日		59.8		
10		3月22日		-172		
11		4−99	-21		
12						
13						

 ※m-dのところ、m月d日で表現してま。 シリアル値でないところは
   文字列。
 
(GobGob) 2015/03/06(金) 08:52

GobGob様、半平太様 及び ご回答を下さった方々アドバイス感謝致します。

>ここ、ちょっと意味が分からないのですけど、
>参考までに、冒頭掲示されたデータは、セルの書式を標準にすると何になるんですか?(変わるんですか?)

m−dから標準に戻してみました。するとすべて「5桁の数字」に変化しました。

1−2  → 42012
3−99 → 42075
21−55→ 42351

などです。
                              

もともと、
「G2」から「G29」と
「F2」から「F29」
に入っているデータ(数字)は、別のソフトから整形テキスト方式でエクセル
に出力したものです。

いったん「Sheet3」に出力して、その後「Sheet2」の
「G2」から「G29」と
「F2」から「F29」
に入力される形になっています。

「Sheet3」に出力した時点で、「m−d」になるようにしてあります。

ごく簡単なマクロが組んであり、その「m−d」で表示されているもの
(3−99)など、をF列(「F2」から「F29」)に入力と言うか、出力
しているわけです。キーボードから手入力しているわけではありません。

標準に戻した時に変化した「5桁の数字」ですが、これが何を意味するのか、
私にも分かりません。意味があるのは、「3−99」や「21−55」と
いった数字であり、その数(個数)になります。

3番(3)がいくつあるのか? とか、99番がいくつあるのか? などです。
全部で28通りもあり、それが1種類とすると、他にも何十種類もあるのです。
なので、1種類づつ自分で数えていたのでは大変な時間がかかってしまい、
それで自動で表示させることができないか・・・と考え、試行錯誤していたのです。

その結果、ここで「GobGob様」や「半平太様」など非常に優秀で優しい方々に
めぐり会えたのは、本当に幸運だったと思います。

                                                                                                                    

>そりゃ日付表示形式と文字列が混在したら無理ですわ。

説明不足で申し訳ございません。

>てか、なんでF列1つで処理する仕様にするんでしょうな。E、F列に別けて大小は入力規則かなんかで
>処理すればいいのに。

私の知識不足で、大変なお手数とご苦労をおかけ致しましたことを心よりお詫び申し上げます。

>まぁ、さらにめんどくさくなってるのは確かなんで。。。

本当に申し訳ございません。ごめんなさい。

                                        
                                  

>※INDEXは1個目だけっす。説明不足でした。

とんでもございません。

>※F列実際は半角文字ならASC関数不要。

了解致しました。

                                     
                                   

>一応結果。
>※m-dのところ、m月d日で表現してま。 シリアル値でないところは
>文字列。

                                 

わざわざ結果まで記して頂きまして本当にありがとうございます。

自分のExcel2000で実行したところ、正常に表示させることに成功しました。

ここまでして下さったGobGob様には本当に感謝致します。

GobGob様、このたびは本当に本当にありがとうございました。
(ハワイ) 2015/03/07(土) 05:32


 21-55 とか 3-99 が なぜ「五桁数値(シリアル値)」になるのか?
 んで、ホンマに正常に計算されてるか? よーわからんけど。

 まぁ、正常に結果がでてるのならOKっすなー。

 ※ネストチェック面倒なんで丸投げ解答すんませんでした。
(GobGob) 2015/03/07(土) 07:52

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.