複数条件分岐に関して、お知恵を貸してください（A列

質問

こんにちは。

対処方法をご存知の方、お知恵を拝借いただけないでしょうか。

以下のようなデータがあります。

A 　　　　　B　　　　　C
2 名前 　　　　資格 　　　UID
3 MAEDA 　A資格　　 1
4 MAEDA 　B資格 2
5 MAEDA 　C資格 3
6 YAMADA 　B資格 4
7 YAMADA 　C資格 5
8 SUZUKI 　C資格 6

一番上のA～Cが列番号、一番左の数字が、行番号です。2行目は、表の列名になっています。

まず、例えば、MAEDAさんは、A～Cのすべての資格をもっているので、A資格の行のD列（D3）に、「抽出」を返し、D4、D5には、MAEDAさんのA資格のUID（セル：C3)を値を引っ張り、「UID1所有につき無視」を返す。
YAMADAさんの場合は、B資格が、持っているもので一番上なので、「D6」には「抽出」の値を返し、C資格の「D7」には「UID4所有につき無視」を返す。
SUZUKIさんは、C資格しか持っていないので、D8に「抽出」を返す。

上記のような処理がしたいのですが、VlookとIF、DGET、いろいろ組み合わせて試しても、エラー結果しか出ませんでした。。

解決できる処理方法をご存知の方がいらっしゃいましたら、ご指導いただきたく、どうぞよろしくお願いいたします。

回答No.3

kagakusuki · ベストアンサー

回答No.2です。

>イメージに近いものが出たのですが、出されるデータが少し違っていました。
>返しの結果が異なるのは、資格の並びが不順だからでしょうか。。

　はい、資格の並びが不順であるためです。
　とは言え、資格の名称が、「A資格」、「B資格」、「C資格」の様なパターンであれば、一旦、B列を基準にしてA～C列を昇順で並べ替えてから、A列基準で並べ替える、という方法も使えるのですが、実際の資格のレベルの順番は、必ずしも資格名の文字コードが若い順番と一致している訳ではないと思われますから、Excelの並べ替え機能に頼るのは得策ではないと思います。
　一応、隣の列に関数を使って資格のレベル順に数値を割り振ってから、その列を基準に並べ替えを行うという方法も無い訳では御座いませんが、並べ替えるのを忘れていた場合には、正しい結果が表示されないという難点があります。
　ですから、名前と資格の双方とも、順不同に並んでいても問題が無い、以下の様な方法は如何でしょうか？

　今仮に、御質問文の例の様な表が存在しているシートのシート名がSheet1であり、Sheet2のA列とB列を作業列として使用して、Sheet1のD列に結果を表示させるものとします。

　まず、Sheet2のA列に、上から順番に「A資格」、「B資格」、「C資格」という様に、レベルの高い順に全ての資格名を並べたリストを作成して下さい。(途中に空欄のセルが挟まっていても構いません)
　次に、Sheet2のB3セルに次の関数を入力して下さい。

=IF(OR(Sheet1!$A3="",Sheet1!$B3="",COUNTIF($A:$A,Sheet1!$B3)=0),"",COUNTIF(Sheet1!$A:$A,">"&Sheet1!$A3)*10000000+MATCH(Sheet1!$B3,$A:$A,0))

　次に、Sheet2のB3セルをコピーして、Sheet2のB4以下に貼り付けて下さい。

　次に、Sheet1のD3セルに次の関数を入力して下さい。

=IF(ISNUMBER(Sheet2!$B3),IF(COUNTIF(Sheet2!$B:$B,"<"&Sheet2!$B3)=COUNTIF(Sheet2!$B:$B,"<"&ROUNDDOWN(Sheet2!$B3,-7)),"抽出","UID"&INDEX($C:$C,MATCH(SMALL(Sheet2!$B:$B,COUNTIF(Sheet2!$B:$B,"<"&ROUNDDOWN(Sheet2!$B3,-7))+1),Sheet2!$B:$B,0))&"所有につき無視"),"")

　次に、Sheet1のD3セルをコピーして、Sheet1のD4以下に貼り付けて下さい。

　以上です。

補足

kagakusuki様

おはようございます。

昨日はあのままチェックできず、今、再度のご連絡をいただいたことに気づきました。

お礼が遅くなりまして申し訳ございません。

いただいた方法を早速試してみます！

ひとまず、取り急ぎ御礼まで。

回答No.8

bunjii

回答No.5の数式を一部修正して全体を簡略化してみました。

=IF(B3="","",IF(MOD(SUMPRODUCT(MAX((A$3:A$1000=A3)*(B$3:B$1000={"A資格","B資格","C資格"})*ROW(A$3:A$1000)+(A$3:A$1000=A3)*(B$3:B$1000={"A資格","B資格","C資格"})*{20000,10000,0})),10000)=ROW(),"抽出","UID"&INDEX(C$1:C$1000,MOD(SUMPRODUCT(MAX((A$3:A$1000=A3)*(B$3:B$1000={"A資格","B資格","C資格"})*ROW(A$3:A$1000)+(A$3:A$1000=A3)*(B$3:B$1000={"A資格","B資格","C資格"})*{20000,10000,0})),10000))&"所有につき無視"))

資格のウェイトの値を逆順にしてLARGE関数の代わりにMAX関数を使いました。
また、IF関数の論理式で使っているMOD関数は文字列結合時に使うMOD関数と全く同じ引数なので式中でコピー&ペーストが使えます。

MOD(SUMPRODUCT(MAX((A$3:A$1000=A3)*(B$3:B$1000={"A資格","B資格","C資格"})*ROW(A$3:A$1000)+(A$3:A$1000=A3)*(B$3:B$1000={"A資格","B資格","C資格"})*{20000,10000,0})),10000)

お礼

bunjii様

いただいた式の資格を追加して行ったところ、出てきました！
資格の種類が15に上るので、今回は別の式にて対応いたしましたが、いただきました解も、今後に生かしたいと思います。

教えてくださって本当にありがとうございました。

補足

bunjii様

再度のご回答ありがとうございます！

こちらも試してみます！
１つのことを処理するのにも、色々な出し方が考えられるんですね。

とても勉強になります。

回答No.7

kagakusuki

実際の資格のレベルの順番は、必ずしも資格名の文字コードが若い順番と一致している訳ではない以上、御質問の処理を行うには、作業列と関数を併用するか、配列式を使うか、SUMPRODUCT関数を使うか、の何れしか方法は御座いません。
　但し、配列式やSUMPRODUCT関数は、データの数だけ繰り返し計算を行う事で結果を求めるという処理の仕方をしていますので、普通のやり方で作られた配列式やSUMPRODUCT関数の場合、式の中においてA列～C列のセル範囲として指定している行数が多ければ多いほど、繰り返し計算の回数が多くなり、処理に時間が掛かる様になるという短所があります。
　SUMPRODUCT関数の一例として、SUMPRODUCT関数を用いて、A3:B1000の範囲内で、A列の値がA3セルの値に等しく、B列の値がB3セルよりも大きな値となっているデータの数をカウントするという、複数条件のカウントを行う関数を挙げますと、

=SUMPRODUCT(($A$3:$A$1000=$A3)*($B$3:$B$1000>$B3))

という関数になりますが、この場合には、３行目のデータに関して計算を行ってから、4行目のデータに関して計算を行い、続いて5行目のデータに関して計算を行い、・・・
という事を繰り返して行きますので、似た様な計算を998回も繰り返す事になる訳です。

　そこで、同じくSUMPRODUCT関数を使用していながら、繰り返しの対象としているデータの数が、A列～C列の行数によってではなく、資格の種類の数によって決まる様にする事で、高速化を図った関数を考えてみました。
　但し、SUMIFSIFS関数を応用しておりますので、C列のUIDの値が数値で入力されている必要がありますし、ExcelのバージョンがExcel2007かそれ以降のものが必要となります。
　又、同一人物のデータの中で、B列の資格のレベルが最も高い資格となっている行が、万が一、重複して複数存在している場合には、正しい結果が得られませんので注意して下さい。(「無視」の場合に表示されるUIDの番号がおかしな値となるだけで、「抽出」と「無視」の区別自体は正しく行われます)

　まず、D3セルに次の関数を入力して下さい。

=IF(OR($A3="",ISNA(MATCH($B3,{"C資格","B資格","A資格"},0))),"",IF(MATCH($B3,{"C資格","B資格","A資格"},0)=SUMPRODUCT(MAX((COUNTIFS($B:$B,{"C資格","B資格","A資格"},$A:$A,$A3)>0)*MATCH({"C資格","B資格","A資格"},{"C資格","B資格","A資格"},0))),"抽出","UID"&SUMIFS($C:$C,$A:$A,$A3,$B:$B,INDEX({"C資格","B資格","A資格"},SUMPRODUCT(MAX((COUNTIFS($B:$B,{"C資格","B資格","A資格"},$A:$A,$A3)>0)*MATCH({"C資格","B資格","A資格"},{"C資格","B資格","A資格"},0)))))&"所有につき無視"))

　次に、D3セルをコピーして、D4以下に貼り付けて下さい。

　以上です。

　尚、関数中の各所に記述されている{"C資格","B資格","A資格"}の部分は、必ず資格のレベルが低いものから順に記述する様にして下さい。

※画像に関しては、回答No.3で添付した画像から、Sheet2の部分を削除しただけのものと同様のものとなりますので、今回は割愛致します。

補足

kagakusuki様

再度のご回答ありがとうございます！

Excel、、Version、かなり古いのです。
自分のPC（まだOfficeが入っていない）に、Office買っていれようかと思っているので、そのとき、新しいVersionで、ぜひ試してみたいと思います。

先にご教授いただいたものを、まず、試してみたいと思います。

１つのことを処理するのにも、色々な出し方が考えられるんですね。

とても勉強になります。

回答No.6

MackyNo1

>ちなみに、結果は、D2はOK
D3は#N/A（ここには「UID1所有につき無視」をしたい）
D4は、「抽出」（ここは、「UID1所有につき無視」を返したい）
D5は、#N/A（ここには「抽出」を返したい）
D6は、#N/A（ここには「UID4所有につき無視」をしたい）
D7は、表示なし（ここには「抽出」を返したい）
という感じです。

例示のデータはD3セルから始まっていますが、D2セルからなのでしょうか？

実際のデータと同じレイアウトならD3セルに回答をそのままコピー貼り付けすれば、問題なく表示できるはずです。

＞例の表では、A資格が一番上になっていますが、実際のデータでは、特にABCと並んでいるわけではないです。

この条件がある場合は（特に資格の数が決まっていない場合）簡単な関数ではできません。
並べ替えで名前を第一優先順位、資格を第二優先順位にして（これが昇順で並べ替えられない場合はユーザー設定リストを利用した並べ替えをする必要があります）並べ替えてください。

お礼

MackyNo1様

補足の内容も確認、再度の回答をいただきましてありがとうございました。

今回は別の式にて対応いたしましたが、いただきました解も、今後に生かしたいと思います。

教えてくださって本当にありがとうございました。

補足

MackyNo1様

ご連絡ありがとうございます！

資格の数は、現時点でも１５種類登録があります。

並び替えと資格名を増やして試してみます。

どうもありがとうございます！

回答No.5

bunjii

>データのソートは、A列の人物順になっており、資格ではソートされていません。例の表では、A資格が一番上になっていますが、実際のデータでは、特にABCと並んでいるわけではないです。
条件が難しいので関数式が長くなります。

=IF(B3="","",IF(MOD(SUMPRODUCT(LARGE((A$3:A$1000=A3)*(B$3:B$1000={"A資格","B資格","C資格"})*ROW(A$3:A$1000)+(A$3:A$1000=A3)*(B$3:B$1000={"A資格","B資格","C資格"})*{10000,20000,30000},COUNTIF(A$3:A$1000,A3))),10000)=ROW(),"抽出","UID"&INDEX(C$1:C$1000,MOD(SUMPRODUCT(LARGE((A$3:A$1000=A3)*(B$3:B$1000={"A資格","B資格","C資格"})*ROW(A$3:A$1000)+(A$3:A$1000=A3)*(B$3:B$1000={"A資格","B資格","C資格"})*{10000,20000,30000},COUNTIF(A$3:A$1000,A3))),10000))&"所有につき無視"))

貼付画像はExcel 2013で検証した結果です。
基本的には配列の中から名前と資格で抽出し、A資格、B資格、C資格の順にウェイトを掛けた行番号から抽出要件を満たすものを選別しています。
検索範囲の最大行番号と資格ごとのウェイトは目的に合わせて変更してください。
数式はExcel 2003にも組み込まれた関数の範囲にと思います。
尚、MAEDAさんは資格を逆順にしてありますので結果を確認してください。

補足

bunjii様

おはようございます。

昨日はあのままチェックできず、今、再度のご連絡をいただいたことに気づきました。

お礼が遅くなりまして申し訳ございません。

いただいた方法を早速試してみます！

ひとまず、取り急ぎ御礼まで。

回答No.4

tom04

こんばんは！
すでに適切な回答は出ていますが・・・
VBAでの一例です。
資格は3つだけで優先順位は　A資格　B資格　C資格　の順とします。

元データはSheet1にあるとします。
Sheet2を作業用のSheetとして使用していますので、Sheet2は使用していない状態にしておいてください。

画面左下のSheet1のSheet見出し上で右クリック　→　コードの表示　→　VBE画面（カーソルが点滅しているところ）に
↓のコードをコピー＆ペースト　→　Excel画面に戻りマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, lastRow As Long, c As Range, r As Range
Dim myRange1 As Range, myRange2 As Range, wS As Worksheet
Set wS = Worksheets("Sheet2")
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

Application.ScreenUpdating = False
If lastRow > 2 Then
Range(Cells(3, "D"), Cells(lastRow, "D")).ClearContents
End If
Range("A2").AutoFilter field:=2, Criteria1:="A資格"
Range(Cells(3, "D"), Cells(lastRow, "D")).SpecialCells(xlCellTypeVisible) = "抽出"
AutoFilterMode = False
Range("A:A").AdvancedFilter Action:=xlFilterInPlace, unique:=True
Range(Cells(3, "A"), Cells(lastRow, "A")).Copy wS.Range("A1")
ShowAllData
For i = 1 To wS.Cells(Rows.Count, "A").End(xlUp).Row
Range("A2").AutoFilter field:=1, Criteria1:=wS.Cells(i, "A")
Set myRange1 = Range(Cells(3, "D"), Cells(lastRow, "D"))
Set c = myRange1.Find(what:="抽出", LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
myRange1.SpecialCells(xlCellTypeBlanks) = "UID" & c.Offset(, -1) & "所有につき無視"
Else
Set myRange2 = Range(Cells(3, "B"), Cells(lastRow, "B"))
Set r = myRange2.Find(what:="B資格", LookIn:=xlValues, lookat:=xlWhole)
If Not r Is Nothing Then
r.Offset(, 2) = "抽出"
myRange1.SpecialCells(xlCellTypeBlanks) = "UID" & r.Offset(, 1) & "所有につき無視"
Else
myRange1.SpecialCells(xlCellTypeBlanks) = "抽出"
End If
End If
Next i
AutoFilterMode = False
wS.Range("A:A").Clear
Application.ScreenUpdating = True
End Sub 'この行まで

※　関数でないのでデータ変更があるたびに
マクロを実行する必要があります。m(_ _)m

お礼

tom04様

こんにちは。

VBA知識がまだまだなため、今回教えていただいたものはまだ試せていないのですが、いただきました解も、今後に生かしたいと思います。

教えてくださって本当にありがとうございました。

回答No.2

kagakusuki

例えば、同じ人物のデータの中で、C資格が一番上に来ていて、A資格が後になっている場合には、C資格の行のD列の所を「抽出」とし、A資格の行のD列の所は「UID○所有につき無視」とすると考えて宜しいのでしょうか？

　それで宜しければ、以下の様な方法となります。

　まず、D３セルに次の関数を入力して下さい。

=IF($A3="","",IF(COUNTIF($A$2:$A3,$A3)=1,"抽出","UID"&VLOOKUP($A3,$A:$C,3,FALSE)&"所有につき無視"))

　そして、D3セルをコピーして、D4以下に貼り付けて下さい。

補足

kagakusuki様

こんにちは。ご回答ありがとうございます。

イメージに近いものが出たのですが、出されるデータが少し違っていました。
返しの結果が異なるのは、資格の並びが不順だからでしょうか。。

A資格、B資格、C資格とあり、それぞれ人物毎に取得している資格は異なります。
B資格だけを持っている人もいれば、AとCを持っている、Aだけ、あるいはCだけ持っているなどばらばらです。

データのソートは、A列の人物順になっており、資格ではソートされていません。例の表では、A資格が一番上になっていますが、実際のデータでは、特にABCと並んでいるわけではないです。

得たい結果は、
(1)それぞれ、少なくとも1つは資格は持っていますが、各人物の所有の資格から、その所有のうちもっとも高いレベルのものを明確化したい。
(2)複数持っている人の場合は、一番上レベルの資格以外の行は「上レベルのものを持っているので、この行はいったん無視した」ことを示すメッセージを表示したい。※そのために、その行を無視する理由になった、所有中の最上位の資格を登録しているUIDを返したい

の2点です。

どうぞよろしくお願いいたします。

回答No.1

MackyNo1

元データが例示のレイアウトなら以下の式でご希望の表示ができます。

=IF(A3="","",IF(COUNTIF($A$3:A3,A3)=1,"抽出","UID"&MATCH(A3,$C$3:$C$100,0)&"""所有につき無視"))

補足

MackyNo1様

こんにちは。ご回答ありがとうございます。

試してみたのですが、最初の1行以外、Error、または異なる返しになってしまいました。。絶対参照とかを一部はずさないといけないのでしょうか。。

ちなみに、結果は、D2はOK
D3は#N/A（ここには「UID1所有につき無視」をしたい）
D4は、「抽出」（ここは、「UID1所有につき無視」を返したい）
D5は、#N/A（ここには「抽出」を返したい）
D6は、#N/A（ここには「UID4所有につき無視」をしたい）
D7は、表示なし（ここには「抽出」を返したい）

という感じです。