Excel (一般機能)

Excelの一般機能に関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 8 : Excel 2016)
関数 複数条件
投稿日時: 19/08/22 21:52:19
投稿者: chokobanana

特定の文字を含む場合の複数条件が分かりません。
 
@B3に丸という文字が入力されている場合は○
AC3に青を含む文字が入力されている場合はB
BD3に文字が入力されている場合のみ
 
@且つA且つBであった場合は○Bとする。
 
それぞれでしたら分かるのですが全ての条件を兼ね備えた場合、どのように考えたらよろしいのでしょうか?
 
@=IF(COUNTIF(B3,"丸"),"○",)
A=IF(COUNTIF(C3,"*青*"),"B",)
B=IF(D3〈〉"","○",)

回答
投稿日時: 19/08/22 22:04:05
投稿者: WinArrow
投稿者のウェブサイトに移動

要望通りの数式は
=IF(OR(COUNTIF(B3,"*丸*"),D3<>""),"○","")&IF(COUNTIF(C3,"*青*"),"B","")
 
※IF関数の場合、「偽」となる場合もきちんと指定しましょう。
省略しないことです。

投稿日時: 19/08/23 12:59:37
投稿者: chokobanana

ありがとうございます。
 
>省略しないことです。
 
今後気を付けます。
ご指摘下さり感謝いたします。
 
もう少し教えていただけますでしょうか。
 
条件が@とAで増えていった場合で@且つA且つBであった場合
それと、別シートから参照する場合に列の名称と同じ名称に答えを返す方法はどのように考えたらよいのでしょうか?(Sheet@のD列の田中さんのをSheetAの田中さんの列に結果を返す方法。)
 
@-1 B3に丸という文字が入力されている場合は○
@-2 B3に三という文字が入力されている場合は△
@-3 B3に四という文字が入力されている場合は□
 
A-1 C3に青を含む文字が入力されている場合はB
A-2 C3に赤を含む文字が入力されている場合はA
A-3 C3に黄を含む文字が入力されている場合はC
 
BD3に文字が入力されている場合のみ
 
 
 

回答
投稿日時: 19/08/23 13:24:26
投稿者: WinArrow
投稿者のウェブサイトに移動

引用:

@-1 B3に丸という文字が入力されている場合は○
@-2 B3に三という文字が入力されている場合は△
@-3 B3に四という文字が入力されている場合は□

この条件を整理すると
一つのセルに2つ以上のデータは入りませんから
~=IF(B3="丸","○",IF(B3="三","△",IF(B3="四","□","???")))
 
のような数式になりますが、
説明されているのは、3パターンなので、それ以外は"???"としています。
???部分は、貴方が考えることです。
 
前レスで、省略しなようコメントしましたが、理解していますか?
全体的に説明が十分でありません。
特にBの説明は・・・・説明になっていません。
 
@且つAかつB…なんて難しく考えずに
分解して数式を組立し、・・・連結すればよいです。
 
今迄のレスを理解すれば自分で組立られるっはずです、
 
なお、条件が増えるとIF関数が複雑になります。
VLOOKUP関数などを使うとスッキリした数式になることもあります。
 
@の例
 
別シートを参照する場合は、
Sheet2!A1
の様にシート名で修飾すればよいだけです。
参照先が自セルと同じかどうかは関係ありません。
 

回答
投稿日時: 19/08/23 13:42:27
投稿者: WinArrow
投稿者のウェブサイトに移動

質問
 
Aの条件は、
C3セルに特定文字列が含まれている・・・その条件が3つあるという説明ですが
 
例えば
1111青22赤33黄444
のように、特定文字が2種類、(又は3種類)存在した場合は、どのように考えるのですか?

回答
投稿日時: 19/08/23 13:52:14
投稿者: WinArrow
投稿者のウェブサイトに移動

「数式」は、「数式」が入っているセルに「値」を返すものです。
他セル(勿論別シートを含む)に「値」を返すことはできません。
 
>Sheet@のD列の田中さんのをSheetAの田中さんの列に結果を返す方法
この説明は理解しにくいですが、雰囲気で
シート1のD3セルの「値」をシート2のD3セルに返す
という場合、
シート2のD3セルに
=Sheet1!D3
と入力します。
これはシート1のD3を参照していることになります。

回答
投稿日時: 19/08/23 14:24:11
投稿者: WinArrow
投稿者のウェブサイトに移動

ヒント
VLOOKUP関数を使った例・・・・@の条件の場合
 
任意のセル範囲(Ex:H1〜I3)に次のような表を作成します。
セルH1:丸、セルI1:○
セルH2:三、セルI2:△
セルH3:四、セルI3:□
 
数式をわかりやすくするために
セルH1〜I3に、名前を定義します。
A1〜I3を選択しておいて、名前ボックスに@と入力します。
 
数式を入力するセル(Ex:E3)に
=VLOOKUP(B3,@,2,FALSE)
と入力します。
B3セルの「値」が丸、三、四のいづれかの場合は、対応する文字が返ります。
それ以外は#N/Aエラーになります。
 
条件が増えたら表@の範囲を広げデータ追加すれば、数式を変更することなく対応できます。

投稿日時: 19/08/23 14:32:30
投稿者: chokobanana

まだまだ理解ができていないようでご迷惑をおかけいたしております。
正しくは下記のとおりです。
 
@-1 B3に丸という文字が入力されている場合は○、それ以外は空白
@-2 B3に三という文字が入力されている場合は△、それ以外は空白
@-3 B3に四という文字が入力されている場合は□、それ以外は空白
  
A-1 C3に青を含む文字が入力されている場合はB 、それ以外は空白
A-2 C3に赤を含む文字が入力されている場合はA 、それ以外は空白
A-3 C3に黄を含む文字が入力されている場合はC 、それ以外は空白
 
BD3に文字が入力されている場合のみ@とAの結果を返す。
 
 
分解して考えると
@-1〜@-3については
=IF(B3="丸","○",IF(B3="三","△",IF(B3="四","□","")))
 
A-1〜A-3については
=IF(C3="*青*","B",IF(C3="*赤*","A",IF(C3="*黄*","C","")))
 
 
この2つを連結する考え方が理解できておりません。
 
 

投稿日時: 19/08/23 15:08:48
投稿者: chokobanana

>Aの条件は、
>C3セルに特定文字列が含まれている・・・その条件が3つあるという説明ですが
>例えば
>1111青22赤33黄444
>のように、特定文字が2種類、(又は3種類)存在した場合は、どのように考えるのですか?
 
特定の文字が混在することはありません。
 
>Sheet@のD列の田中さんのをSheetAの田中さんの列に結果を返す方法
 
分かりにくい説明でご迷惑をおかけして申し訳ありません。
 
Sheet@とSheetAで名前が同一で無い場合にその列を選ぶ方法をどうのように考えるのかが分からなかったのです。
 
例えば下記のSheet@のD列 田中さんの値を参照して条件@とAを考えて、SheetAのE列に結果を返す場合です。
 
(Sheet@)
D列   E列   F列   G列
田中   佐々木  山本  佐藤
 
 
(SheetA)
D列   E列   F列   G列
鈴木   田中   松田  佐藤
 
 
できれば関数で選ぶ方法がありましたらご指導頂けますでしょうか?
 
 
 
 
 
 

投稿日時: 19/08/23 15:30:09
投稿者: chokobanana

>VLOOKUP関数を使った例
 
このような使用があることを初めて知りました。
勉強になります。
 
こちらワイルドカードの場合はどのようになるのでしょうか?
同じように名前の定義をしてみましたがエラーになってしまいました。

回答
投稿日時: 19/08/23 15:41:27
投稿者: んなっと

Sheet1
 
   B   C   D
 3 丸 [青] 田中
 4 丸 [赤] 佐藤
 5 丸 [黄] 鈴木
 6 丸    田中
 7   [赤] 佐藤
 8 三 [青] 鈴木
 9 三 [赤]   
10 三 [黄] 田中
11 四    佐藤
12 四 [赤] 鈴木
 
Sheet2 A:D列に以下のような対応表
 
   A  B  C D   E   F   G
 2         田中 佐藤 鈴木
 3 丸 ○ 青 B  ○B      
 4 三 △ 赤 A     ○A   
 5 四 □ 黄 C        ○C
 6          ○      
 7             A   
 8               △B
 9                 
10         △C      
11             □   
12               □A
 
E3
=IF(Sheet1!$D3=E$2,IFERROR(VLOOKUP(Sheet1!$B3,$A:$B,2,FALSE),"")&IFERROR(LOOKUP(1,0/FIND($C$3:$C$10,Sheet1!$C3)/LEN($C$3:$C$10),$D$3:$D$10),""),"")
下方向・↓

投稿日時: 19/08/23 15:59:57
投稿者: chokobanana

BD3に文字が入力されている場合のみ@とAの結果を返す。
D3は仮に1としておきます。
 
 
(Sheet@)
  B列  C列   D列   E列   F列   G列
2         田中   佐々木  山本  佐藤
3 丸  青紫    1        1    1
4 三        1    1
5 四   赤鬼    1    1        1
  
(SheetA)
   D列   E列   F列   G列   H列    I列
2 鈴木   田中   松田  佐藤  佐々木  山本
3 〇B       〇B        〇B
4 △            △
5 □A □A □A   

投稿日時: 19/08/23 16:19:02
投稿者: chokobanana

名前の定義@が条件@-1〜@-3とした場合
名前の定義Aが条件A-1〜A-3とした場合
但し名前の定義Aにワイルドカードを用いた場合はエラーです。
 
B列とD列に両方とも条件があるう場合は下記でした。
B列、D列どちらかに条件がある場合はエラーとなりました。
=IF(COUNTIF(D3,"<>"),VLOOKUP(B3,C,2,FALSE)&VLOOKUP(C3,B,2,FALSE),"")
 
ご指導頂けますでしょうか。

回答
投稿日時: 19/08/23 17:22:13
投稿者: WinArrow
投稿者のウェブサイトに移動

>A-1〜A-3については
>=IF(C3="*青*","B",IF(C3="*赤*","A",IF(C3="*黄*","C","")))
 
この数式はNGです。
 
 
IF関数だけで組み立てると
=IF(COUNTIF(C3,"*青*"),"B",IF(COUNTIF(C3,"*赤*"),"A",IF(COUNTIF(C3,"*黄*"),"C","")))
になります。
 
VLOOKUP関数は難しいね・・・

回答
投稿日時: 19/08/23 19:57:56
投稿者: WinArrow
投稿者のウェブサイトに移動

投稿日時: 19/08/23 15:08:48
の説明に対応した数式
 
Sheet2のE3セルに
 
=IF(ISERROR(MATCH(D$2,Sheet1!$D$2:$G$2,0)),"",IF(ISERROR(VLOOKUP(Sheet1!$B3,@,2,FALSE)),"",VLOOKUP(Sheet1!$B3,@,2,FALSE)&IF(COUNTIF(Sheet1!$C3,"*青*")>0,VLOOKUP("青",A,2,FALSE),IF(COUNTIF(Sheet1!$C3,"*赤*")>0,VLOOKUP("赤",A,2,FALSE),IF(COUNTIF(Sheet1!$C3,"*黄*")>0,VLOOKUP("黄",A,2,FALSE),"")))))
 
右へ、下へフィルドラッグ

回答
投稿日時: 19/08/23 20:33:29
投稿者: WinArrow
投稿者のウェブサイトに移動

追加レス
 
名前定義について
任意のセル範囲をブックレベルの名前として定義するので、どこでも良い
@
1列目 2列目
丸   ○
三   △
四   □
 
A
1列目 2列目
青   B
赤   A
黄   C
 
 
 
 

投稿日時: 19/08/29 17:19:51
投稿者: chokobanana

遅くなり申し訳ございません。
教えて頂いた式で@とAの条件は希望通りになりました。
条件Bが色々試してみましたが解決できません。
 
 
例えばD3に文字が入力されていた場合に〇とするなら
=IF(D3<>"","〇","")
となりますが、条件BをMACH関数で取得した列をどうのように式に表したらよいのか分かりません。
 
 
ご指導頂けますでしょうか
 
 
 

回答
投稿日時: 19/08/29 18:14:13
投稿者: んなっと

Sheet1とSheet2で、鈴木 田中 松田 佐藤 ... の順番は揃えておきましょう。
 
Sheet1
 
   B   C   D   E   F   G
 2      鈴木 田中 松田 佐藤
 3 丸 [青]   1      1   1
 4 丸 [赤]   1   1      
 5 丸 [黄]   1   1      1
 6 丸            1   
 7   [赤]      1      1
 8 三 [青]   1      1   
 9 三 [赤]      1      
10 三 [黄]         1   
11 四               1
12 四 [赤]   1         
 
Sheet2
 
   A  B  C D   E   F   G   H
 2         鈴木 田中 松田 佐藤
 3 丸 ○ 青 B  ○B     ○B  ○B
 4 三 △ 赤 A  ○A  ○A      
 5 四 □ 黄 C  ○C  ○C     ○C
 6                ○   
 7             A      A
 8         △B     △B   
 9            △A      
10               △C   
11                   □
12         □A         
 
E3
=IF(Sheet1!D3="","",IFERROR(VLOOKUP(Sheet1!$B3,$A:$B,2,FALSE),"")&IFERROR(LOOKUP(1,0/FIND($C$3:$C$10,Sheet1!$C3)/LEN($C$3:$C$10),$D$3:$D$10),""))
右方向・→下方向・↓

投稿日時: 19/09/02 13:04:33
投稿者: chokobanana

実際の様式は下記のとおりです。
 
Sheet@の名前が必ずしもSheetAにあるわけではありません。
順番も違っております。
 
んなっと様が教えて頂いた式ですとSheet@とSheetAの名前と順番が完全に一致していないと
いけないのではないのでしょうか。
 
(Sheet@)
  B列  C列   D列   E列   F列   G列
2         田中   佐々木  山本  佐藤
3 丸  青紫    1        1    1
4 三        1    1
5 四   赤鬼    1    1        1
   
(SheetA)
   D列   E列   F列   G列   H列    I列
2 鈴木   田中   松田  佐藤  佐々木  山本
3      〇B       〇B        〇B
4      △            △
5      □A            □A    □A   

回答
投稿日時: 19/09/02 13:39:29
投稿者: んなっと

Sheet1
 
   B   C   D    E   F   G
 2      田中 佐々木 鈴木 佐藤
 3 丸 [青]   1       1   1
 4 丸 [赤]   1    1      
 5 丸 [黄]   1    1      1
 6 丸             1   
 7   [赤]       1      1
 8 三 [青]   1       1   
 9 三 [赤]       1      
10 三 [黄]          1   
11 四                1
12 四 [赤]   1          
 
Sheet2 A:D列に対応表
 
   A  B  C D   E   F   G   H    I
 2         鈴木 田中 松田 佐藤 佐々木
 3 丸 ○ 青 B  ○B  ○B     ○B    
 4 三 △ 赤 A     ○A         ○A
 5 四 □ 黄 C     ○C     ○C   ○C
 6          ○             
 7                   A    A
 8         △B  △B          
 9                      △A
10         △C             
11                   □    
12            □A          
 
E3
=IF(IFERROR(INDEX(Sheet1!3:3,MATCH(E$2,Sheet1!$2:$2,0)),"")="","",IFERROR(VLOOKUP(Sheet1!$B3,$A:$B,2,FALSE),"")&IFERROR(LOOKUP(1,0/FIND($C$3:$C$10,Sheet1!$C3)/LEN($C$3:$C$10),$D$3:$D$10),""))
右方向・→下方向・↓

投稿日時: 19/09/03 09:08:20
投稿者: chokobanana

んなっと様 ありがとうございます。
氏名が一致した時のみ無事に答えが出ました。
 
19/08/29 18:14:13 投稿で教えて頂いた式にIF(MATCH)関数で試してみましたが
上手くいきませんでした。
 
「Sheet1!3:3」は初めて知りました。
とても勉強になります。
後学の為、教えて頂いた式で色々試してみようと思います。

回答
投稿日時: 19/09/03 11:41:01
投稿者: んなっと

シートのレイアウトは、こちらの指定したものと全く同じにしていますか?
少しでも位置が違っていたら、式だけ調整しようとしても無理です。
まずは以下のレイアウトに変更してください。
 
Sheet1
  
   B   C   D    E   F   G
 2      田中 佐々木 鈴木 佐藤
 3 丸 [青]   1       1   1
 4 丸 [赤]   1    1      
 5 丸 [黄]   1    1      1
 6 丸             1   
 7   [赤]       1      1
 8 三 [青]   1       1   
 9 三 [赤]       1      
10 三 [黄]          1   
11 四                1
12 四 [赤]   1          
  
Sheet2 A:D列に対応表
  
   A  B  C D   E   F   G   H    I
 2         鈴木 田中 松田 佐藤 佐々木
 3 丸 ○ 青 B  ○B  ○B     ○B    
 4 三 △ 赤 A     ○A         ○A
 5 四 □ 黄 C     ○C     ○C   ○C
 6          ○             
 7                   A    A
 8         △B  △B          
 9                      △A
10         △C             
11                   □    
12            □A   
 
E3
=IF(IFERROR(INDEX(Sheet1!3:3,MATCH(E$2,Sheet1!$2:$2,0)),"")="","",IFERROR(VLOOKUP(Sheet1!$B3,$A:$B,2,FALSE),"")&IFERROR(LOOKUP(1,0/FIND($C$3:$C$10,Sheet1!$C3)/LEN($C$3:$C$10),$D$3:$D$10),""))
右方向・→下方向・↓

投稿日時: 19/09/03 12:54:41
投稿者: chokobanana

指定されたテストデータでは大丈夫でした。
 
残念ながらシートのレイアウトはんなっと様のご指定どおりには変更できません。
教えて頂いた式を参考にレイアウトに沿って修正し、希望通りの結果にすることができました。
 
ありがとうございます。

投稿日時: 19/09/03 12:57:42
投稿者: chokobanana

無事に解決いたしました。
 
ご指導頂きました皆様どうもありがとうございました。