Excel 複数の記号から選んで別シートに表示方法

質問

Office2013　Excelを使用しています。
シート1のD12セルに複数の記号（9種の☆★〇◎※×△▲などの記号）の1記号のみ表示されています、　5種(☆★〇◎※)だけはその記号を　シート2の D12セルに表示する関数をご指導お願いいたします。
　☆は☆と表示、　△は表示しない、　×は表示しない。
　シート1のセルには　☆×　のように2っの記号（≒２文字）はなく1記号だけです。。
　表全体は40 × 60程の大きい表です。　D12が出来れば E12、D13など全体にコピーはできると思っています。

回答No.7

MackyNo1 · ベストアンサー

ＯＲ関数を利用するならすでに回答した配列委定数を利用するのがお勧めですが、他の関数で対応するなら以下のような関数が簡便です。

=IF(SUMPRODUCT((シート1!D12={"☆","★","〇","◎","※"})*1),シート1!D12,"")

上記の式は、配列定数の中にシート1!D12の値が含まれていれば（SUMPRODUCT関数の部分が1を返すのでTRUEと同じと判断されるので）シート1!D12の値を返し、該当値がない場合は0を返すので（FALSEと判断され）空白を返す式になっています。

=IFERROR(HLOOKUP(シート1!D12,{"☆","★","〇","◎","※"},1,0),"")

上記の式は、HLOOKUP関数の1行目のデータ、すなわち配列自身
（行配列）の中にシート1!D12の値が含まれていれば、その値自身を返し、データがない場合はN/Aエラーとなるので空白を返す式になっています（第4引数の0はFALSEと同じで完全一致で検索していますので、配列定数を昇順などに並べ替える必要がありません）。

回答No.6

MackyNo1

>=IF(OR(シート1!d12="☆","★","〇","◎","※",),シート1!d12,"")のように短く指定は無理でしょうか。

以下のような配列定数を使用した数式にします。

=IF(OR(シート1!D12={"☆","★","〇","◎","※"}),シート1!D12,"")

お礼

配列定数という短い数式で判り易いご指導ありがとうございました。
活用させていただきます・

回答No.5

kagakusuki

>理解不足でしょうか、シート2のD12セルに、
>=IF(OR(シート1!d12="☆",シート!d12="★",シート1!d12="〇",シート1!d12="◎",シート1!>d12="※",),シート1!d12,"")
>とコピーしますと、都度シート1の確認・指定するダイアログが出てきます。

　それは質問者様の御質問文の

>シート1のD12セル

という部分に書かれているシート名が間違っていて、実際のシート名は

シート1

ではないためではないかと思われます。
　ですから、回答No.1様の御回答にある

=IF(OR(シート1!d12="☆",シート1!d12="★",シート1!d12="〇",シート1!d12="◎",シート1!d12="※",),シート1!d12,"")

という関数の中の

シート1

となっている箇所を実際のシート名に合わせて修正されると良いと思います。
　例えば実際のシート名が

シート1

ではなく、

Sheet1

である場合には、

=IF(OR(Sheet1!D12="☆",Sheet1!D12="★",Sheet1!D12="〇",Sheet1!D12="◎",Sheet1!D12="※",),Sheet1!D12,"")

となりますし、実際のシート名が

Sheet1 (2)

である場合には

=IF(OR('Sheet1 (2)'!D12="☆",'Sheet1 (2)'!D12="★",'Sheet1 (2)'!D12="〇",'Sheet1 (2)'!D12="◎",'Sheet1 (2)'!D12="※",),'Sheet1 (2)'!D12,"")

になります。
　又、一見すると

シート1

の様に見えていても、シート名の末尾の「1」が半角数字の「1」ではなく、全角数字の「１」となっている

シート１

である場合には、

=IF(OR(シート１!D12="☆",シート１!D12="★",シート１!D12="〇",シート１!D12="◎",シート１!D12="※",),シート１!D12,"")

になります。
　因みに、ORを使わない次の様な方法もあります。

=IF(ISNUMBER(MATCH(Sheet1!D12,{"☆","★","〇","◎","※"},0)),Sheet1!D12,"")

=IF(ISNA(MATCH(Sheet1!D12,{"☆","★","〇","◎","※"},0)),"",Sheet1!D12)

=IF(ISERROR(MATCH(Sheet1!D12,{"☆","★","〇","◎","※"},0)),"",Sheet1!D12)

=IF(AND(LEN(Sheet1!D12)=1,ISNUMBER(FIND(Sheet1!D12,"☆★〇◎※"))),Sheet1!D12,"")

=IF(AND(LEN(Sheet1!D12)=1,ISNUMBER(SEARCH(Sheet1!D12,"☆★〇◎※"))),Sheet1!D12,"")

=IF(Sheet1!D12>"〇","",IFERROR(LOOKUP(Sheet1!D12,{"◎","☆","★","※","〇"}),""))

　尚、最後のLOOKUP関数を利用する方法の場合は、{ }内に並んでいる文字列の順番が必ず昇順となる様にして下さい。

お礼

複数の数式の詳しいご指導ありがとうございました。。

補足

ご指導を実際に活用させていただくさいに、勘違いや見落とししそうな、
『一見すると シート1　の様に見えていても、シート名の末尾の「1」が半角数字の「1」ではなく、全角数字の「１」となっている』　や　
『LOOKUP関数を利用する方法の場合は、{ }内に並んでいる文字列の順番が必ず昇順となる様に』
という事例まで詳しいご指導でたいへん助かります。
順に体験させてください。　ありがとうございます。

回答No.4

bunjii

シート1はSheet1に置き換えて良いでしょうか？

>シート2の D12セルに表示する関数をご指導お願いいたします。
「D12セルに表示する関数」と言う言い方は好ましくありません。
「D12セルへ設定する関数」とでも言い換えてください。
Sheet2!D12=IF(SUMPRODUCT(({"☆","★","〇","◎","※"}=Sheet1!D12)*1),Sheet1!D12,"")

お礼

内容を活用させていただきます。
また質問の表現のご指導まで頂きありがとうございました。

回答No.3

dogs_cats

SUBSTITUTEを使用した方法
=IF(AND(SUBSTITUTE(Sheet1!D12,{"☆","★","○","◎","※"},)=Sheet1!D12)=FALSE,Sheet1!D12,"")

sheet3のA１~A5に検索する文字を入力し、Vlookupで検索すると数式は短く出来ます。（セル範囲は任意のセル範囲に移動して結構です。）
数式のコピーを前提する場合は、検索元のセル範囲を絶対参照にする事です。
（セル番地のアルファベットを$で囲む）

=IFERROR(VLOOKUP(Sheet1!D12,Sheet3!$A$1:$A$5,1,FALSE),"")

回答No.2

shintaro-2

>理解不足でしょうか、シート2のD12セルに、
>=IF(OR(シート1!d12="☆",シート!d12="★",シート1!d12="〇",シート1!d12="◎",シート1!d12="※",),シート1!d12,"")
とコピーしますと、都度シート1の確認・指定するダイアログが出てきます。
> 都度ダイアログ（小画面）で指定せずに、自動的に参照・表示は無理でしょうか。

あまりその手のサジェッションが出てきた覚えがないので、
各人のエクセルの設定な気がします。
プルダウンで普通にコピーできたと思います。

>また
>=IF(OR(シート1!d12="☆","★","〇","◎","※",),シート1!d12,"")のように短く指定は無理でしょうか。

やってみればわかります。

回答No.1

shintaro-2

何をされたいのかが不明ですが

記載されたことを実現するだけであれば
例えば
=IF(OR(シート1!d12="☆",シート!d12="★",シート1!d12="〇",シート1!d12="◎",シート1!d12="※",),シート1!d12,"")
とでも入力しておけば済みます。

でも、コピーしてフィルタをかけて表示するのでも良いのかもしれませんし、
そもそもシート１でフィルタ表示でも良いのかもしれません。

補足

早速のご指導で助かります。ありがとうございます。

理解不足でしょうか、シート2のD12セルに、
=IF(OR(シート1!d12="☆",シート!d12="★",シート1!d12="〇",シート1!d12="◎",シート1!d12="※",),シート1!d12,"")
とコピーしますと、都度シート1の確認・指定するダイアログが出てきます。
都度ダイアログ（小画面）で指定せずに、自動的に参照・表示は無理でしょうか。

また
=IF(OR(シート1!d12="☆","★","〇","◎","※",),シート1!d12,"")のように短く指定は無理でしょうか。

お手数ですがご指導をお願いいたします。