エクセルでのデータの表示

こんにちは。　FarEyesです。

#2の数式がお役に立てたようで、こちらとしても幸いです。

#2の補足内容について、お答え致します。

１．キャプチャー映像のデータの件について

> それと、キャプチャー映像の中で、一つ、気になることがありました。14行の「101」「3」「◎」のこと
> なんですが、既述のように、今回の例示の場合、B列の数字データは、常に、「2」と「6」のみで、
> それ以外の数字データが使われることはありません。そして、キャプチャー画面では、14行に於いて、
> 「101」「3」「◎」という結果が表示されています。
> この部分だけは、わたしの希望の仕様とは異なる結果なので、困惑しています。
> A列の「101」は、既に、「A1～A3」で出ており、また、14行辺りでは重複はしていず、それなのに、
> C列に「◎」がついています。
> これは、どのように考えれば宜しいのでしょうか？

#2の添付画像での、14行目のC列が"◎"になっている理由は、
　　A列のデータが重複していて、
　　　※ここでいう重複とは、連続行ではなく、行が離れていても同じ値だった場合は
　　　　重複としています。
　　かつ、B列のデータの種類の数が、『２種類以上』
　　　※つまり、２種類でも、３種類でも同一の条件ということです。
の条件に該当するからです。

　　A列のデータは101ですので、これは、以下のように、A3～A5のデータ（=101）
　　と重複しています。
　　※行が離れていても、A列の値は同じなので、重複としています。
　　　　　　　 A列　　 B列
　　　　 3　　 101　　　6
　　　　 4　　 101　　　2
　　　　 5　　 101　　　6
　　　　 ：　　　：　　　　：
　　　　14　　101　　　3
　　そして、このA列の値が同じ値になっている行の、B列のデータの種類は、
　　　　　6、　2、　3
　　の３種類となります。
　　そして、数式では、A列のデータが同じものについて、B列のデータが
　　　　　『２種類以上』
　　の場合に、"◎"を表示するようになっていますので、結果として、
　　　　　　　 A列　　 B列　　C列
　　　　 3　　 101　　　6　　　◎
　　　　 4　　 101　　　2　　　◎
　　　　 5　　 101　　　6　　　◎
　　　　 ：　　　：　　　　：　　　 ：
　　　　14　　101　　　3　　　◎
　　上記のように、3行目～5行目と、14行目のC列は、"◎"になっている
　　ということです。

言い換えますと、3行目～5行目のC列が、"◎"になっているのは、
　　　　B列のデータが『２種類』のために、　　　"◎"になっている
のではなく、
　　　　B列のデータが『２種類以上』のために、"◎"になっている
ということです。
ですので、14行目のデータも、同一条件になるため、"◎"になっています。


２．空白データの件について

> >>◆１）A列、または、B列のセルが空白の場合　　　　　　 →　空白
>
> なお、上の件ですが、「空白」ということは、決して、ありません。
> それを勘案した場合、ご教示頂いた式から、「空白」に関する部分を削除することはできますか？
> それが可能でしたら、より一層、式が簡潔になりますので、その「空白」に関する部分だけ、削除
> したいのですが・・・。

#2の数式から、「空白」の処理を削除するだけなら、

　=IF(SUMPRODUCT((A$1:A$20=A1)*(B$1:B$20<>B1))>0,"◎","×")

という数式になります。

ただし、この場合もしも、A列、または、B列のデータに空白があった場合、
空白もひとつの「データ」として扱われますので、
そのパターン（A列のみ空白、B列のみ空白、両方空白）及び、B列のデータ
によって、C列には、"◎"が表示されたり、"×"が表示されることになります。


３．下記の件について

> >>◆だとした場合で、さらに確認なのですが、
> 　１）A列のデータが重複していない場合（＝単独の値だった場合）
> 　２）A列のデータが重複していて、かつ、B列のデータの種類が『３種類以上』
> 　　　だった場合
> については、それぞれどう表示すれば良いのでしょうか？
>
> １）については、「×」で結構です。
> ２）については、現在のところ、「２種類」のみを扱っていますが、近い将来、「３種類」の数字データ
> をも扱う予定が在ります。「３種類」で、「３種類以上」ということは、決して、ありません。
> もし、宜しければ、この場合のも式も、ご提示・ご教示頂けると、大変、有り難いです。
> これは、「近い将来（多分、来月辺り）」のことで、急ぎません。お時間に余裕の有る時で結構です
> ので、宜しくお願い致します。
>

前項１．で述べたように、#2の数式では、『２種類以上』の場合に"◎"を表示
するようにしていますので、#2の数式を使用した場合は、『３種類』の場合でも、
"◎"が表示されることになります。

もしも、「２種類」の場合と、「３種類」の場合とで、C列の表示パターンを別々に
したい場合は、#2の数式（これと似たような数式も含む）では対応できません。

これを、ワークシート関数のみで行うには、ピボットテーブルの使用とか、配列
数式などの応用及び組合せ、または、作業用セルの使用等を行う必要がある
と思います。

申し訳ありませんが、当方は、ピボットテーブルとか、配列数式の応用とかには、
あまり詳しくなく、良い数式が思い浮かびません。（すみません）

しかし、代替案として、VBA（マクロ）による『ユーザー関数』を作ってみました。

以下の手順で、下記のマクロを、Excelブックに追加して下さい。

■マクロ(ユーザー関数）の追加手順
１）Excelブックを開いて、「Visual Basic Editor」（VBE）を起動して下さい。
　注）現在使用しているExcelブックにマクロを追加する場合は、一旦、別名で保存
　　　しておいて下さい。（マクロ追加前のバージョンを残しておきます。）

２）「VBA Project」に、「標準モジュール」を追加して下さい。

３）追加した「標準モジュール」のコード画面に、以下のマクロを貼り付けて下さい。

４）マクロ追加後は、念の為、コンパイルを行って、エラーがないか確認して下さい。

５）マクロ追加後は、Excelブックを、一旦保存して下さい。

■マクロ(ユーザー関数）
/////↓ここから//////////////////////////////////////////////////
'===============================================================
'関数名： GetPatern
'機　能： 特定条件のデータ種類のパターン取得
'引　数： ByRef rngArea As Range
'　　　　 ・検索対象のセル範囲(Rangeオブジェクト)
'　　　　　 セル範囲の1列目が第1の検索範囲、2列目が第2の検索範囲
'　　　　 ByVal vCmpKey As Variant
'　　　　 ・検索キー(1列目の検索キー)
'戻り値： Variant
'　　　　 ・種類パターン別の識別文字
'　　　　　="×"：1列目のデータが複数かつ2列目のデータが1種類のみ
'　　　　　　　　 　または、
'　　　　　　　　 1列目のデータが単独
'　　　　　="◎"：1列目のデータが複数かつ2列目のデータが2種類のみ
'　　　　　="△"：1列目のデータが複数かつ2列目のデータが3種類以上
'　　　　　=空白：上記以外(1列目or2列目が空白の場合)
'===============================================================
Function GetPatern(ByRef rngArea As Range, _
　　　　　　　　　 ByVal vCmpKey As Variant) As Variant

　　Dim i As Long　　　　　 '汎用ループ変数
　　Dim cnt As Long　　　　 '汎用カウンタ
　　Dim nRowMax As Long　　 '行数取得用
　　Dim vDicKey As Variant　'パターン辞書のキー
　　Dim objDic As Object　　'パターン辞書のオブジェクト

　　'検索対象のセル範囲が2列未満の場合は戻る
　　If rngArea.Columns.Count < 2 Then
　　　　GetPatern = ""
　　　　Exit Function
　　End If

　　'パターン判定用の辞書オブジェクトを作成
　　Set objDic = CreateObject("Scripting.Dictionary")

　　'パターン辞書を初期化
　　objDic.RemoveAll
　　objDic.CompareMode = vbTextCompare

　　'== パターン辞書に種類パターンを登録 ==
　　nRowMax = rngArea.Rows.Count　'検索セル範囲の行数取得
　　cnt = 0　　　　　　 '1列目データの一致カウンタをクリア
　　For i = 1 To nRowMax
　　　　'セルが空白でなく、かつ、1列目データが一致？
　　　　If Trim(rngArea(i, 1).Text) <> "" And _
　　　　　 Trim(rngArea(i, 2).Text) <> "" And _
　　　　　 rngArea(i, 1).Value = vCmpKey Then
　　　　　　cnt = cnt + 1　 '1列目データの一致カウンタを+1
　　　　　　'2列目データをパターン辞書に登録＆更新
　　　　　　vDicKey = rngArea(i, 2).Value
　　　　　　objDic.Item(vDicKey) = objDic.Item(vDicKey) + 1
　　　　End If
　　Next

　　'== 種類パターン別の識別文字を戻り値にセット ==
　　If cnt > 1 Then
　　　　'1列目データが重複の場合のパターン判定
　　　　Select Case objDic.Count
　　　　Case 1　　　　　　'パターン種類が1種類
　　　　　　GetPatern = "×"
　　　　Case 2　　　　　　'パターン種類が2種類
　　　　　　GetPatern = "◎"
　　　　Case Else　　　　 'パターン種類が3種類以上
　　　　　　GetPatern = "△"
　　　　End Select
　　ElseIf cnt = 1 Then
　　　　GetPatern = "×"　'単独データの場合
　　Else
　　　　GetPatern = ""　　'空白データの場合
　　End If

　　'パターン辞書のオブジェクトを解放
　　objDic.RemoveAll
　　Set objDic = Nothing

End Function
/////↑ここまで//////////////////////////////////////////////////

上記マクロの追加後、下記のように、ワークシートに数式を設定します。
※下記は、#2で例に上げたワークシート構成の場合の設定例です。

　　C1セルに以下の数式を設定します。

　　　=GetPatern($A$1:$B$20,A1)

　　C列の他のセルには、C1セルの数式をコピーして下さい。

■マクロ（ユーザー関数）の補足
１）関数の引数について

　　・１番目の引数には、A列＋B列のセル範囲（＝セルアドレス）を
　　　指定して下さい。
　　　注）アドレスは、「絶対参照」形式のアドレスを指定して下さい。

　　・２番目の引数には、その行のA列のセル（＝セルアドレス）を
　　　指定して下さい。
　　　関数には、指定セルの値が渡されます。
　　　注）アドレスは、「相対参照」形式のアドレスを指定して下さい。

２）関数の処理について

　　この関数では、
　　　　1. A列のデータが重複していて、かつ、
　　　　　 B列のデータの種類の数が「１種類のみ」の場合　　　→　"×"
　　　　2. A列のデータが重複していて、かつ、
　　　　　 B列のデータの種類の数が「２種類のみ」の場合　　　→　"◎"
　　　　3. A列のデータが重複していて、かつ、
　　　　　 B列のデータの種類の数が「３種類以上」の場合　　　→　"△"
　　　　4. A列のデータが単独（重複なし）の場合　　　　　　　　 →　"×"
　　　　5. 上記以外の場合　　　　　　　　　　　　　　　　　　　　　 →　空白
　　　　　　・これは、『A列、または、B列のセルが空白の場合』
　　　　　　　に該当します。
　　をそれぞれ表示するようにしています。

３）上記２）の「判定条件」及び「表示パターン」を変更する場合

　　「判定条件」及び「表示パターン」を変更する場合は、マクロ内の下記部分
　　の「条件式」及び「表示文字」を希望のものになるように変更して下さい。

　　'== 種類パターン別の識別文字を戻り値にセット ==
　　If cnt > 1 Then
　　　　'1列目データが重複の場合のパターン判定
　　　　Select Case objDic.Count
　　　　Case 1　　　　　　'パターン種類が1種類
　　　　　　GetPatern = "×"
　　　　Case 2　　　　　　'パターン種類が2種類
　　　　　　GetPatern = "◎"
　　　　Case Else　　　　 'パターン種類が3種類以上
　　　　　　GetPatern = "△"
　　　　End Select
　　ElseIf cnt = 1 Then
　　　　GetPatern = "×"　'単独データの場合
　　Else
　　　　GetPatern = ""　　'空白データの場合
　　End If

　　＜上記部分の各変数の意味・内容＞
　　◎変数 cnt の値は、A列のデータの重複個数になっています。
　　　　　[cntの値]　　　[A列データの状態]
　　　　　　　0　　　　　　 A列、または、B列のセルが空白のとき
　　　　　　　1　　　　　　 A列のデータが単独（重複なし）の時
　　　　　　　2以上　　　 A列のデータが重複ありで、それが２行以上（２箇所以上）ある

　　◎変数 objDic.Count の値は、B列のデータの種類の数になっています。
　　　 この値は、A列のデータが重複しているものについてセットされます。
　　　　　[objDic.Countの値]　　　[B列のデータの種類]
　　　　　　　1　　　　　　　　　　　　 １種類のみ
　　　　　　　2　　　　　　　　　　　　 ２種類のみ
　　　　　　　3以上　　　　　　　　　 ３種類以上

　　◎変数 GetPatern には、この関数の戻り値（＝C列に表示される文字）
　　　をセットします。

※添付画像は、上記マクロを実装したExcelシートのキャプチャ画像です。

■参考サイト
Excel VBAの操作、マクロの構文などについては、下記サイトが参考になるかと
思います。　宜しければ、ご覧になってみて下さい。

Excelでお仕事！「VBA基本」
http://www.asahi-net.or.jp/~ef2o-inue/menu/menu04.html

Excel(エクセル)VBA入門：目次
http://www.eurus.dti.ne.jp/~yoneyama/Excel/vba/index.html

以上です。

投稿日時 - 2010-02-08 03:47:45