エクセルのOR関数でのセル参照

質問

エクセル2010です。
仮にA1セルが、早稲田、慶應、上智の何れかであればTRUEを返すOR関数は
1.）=OR(A1="早稲田",A1="慶應",A1="上智")
または、
2.）=OR(A1={"早稲田","慶應","上智"})
でいけるのはわかります。
質問は、これら早慶上智といった文字列が、別の表（F1:H1）にあり、それを参照したい場合です。
1.）なら　=OR(A1=F1,A1=G1,A1=H1)　と書き換えればすみます。
しかし2.）は　=OR(A1={F1,G1,H1})　と書き換えるとエラーになってしまいます。
何度もA1=を入れるのが面倒なので2.）のような方法で他のセルを参照させる方法はないでしょうか？

回答No.6

Chiquilin · ベストアンサー

「配列定数」ですから定数でしか指定できません。どうしても「セル」を
指定して比較したいなら

=ISNUMBER(FIND(0&A1&0,0&F$1&0&G$1&0&H$1&0))

のようにセルを文字列として区切り文字を挟んで結合してから計算する
か（範囲で指定したい場合は TEXTJOIN関数でないと無理です）

=OR(COUNTIF(INDIRECT({"F1";"G1";"H1"}),A1))

のようにセルアドレスを文字列で表現して配列定数に組み込むかです。

因みに下方の式は単なる論理式では計算しません。 COUNTIFのような
セルアドレスを値に変換する工程が１つ必要になります。

お礼

ありがとうございます！
=OR(COUNTIF(INDIRECT({"F1";"G1";"H1"}),A1))
これ使えます。たすかりました。

回答No.8

real beatin（@realbeatin）

こんにちは。お邪魔します。
一応、追加補足にもお応えする形で、解決策を２点挙げてみます。
提示された条件「とびとびのセル範囲をテーブルとして扱うこと」
が、Excel的には特殊なものですから、難度が高い課題となっていて、
スマートな解、というのは、直接的には見つからないかな、と。
基本に立ち返って、テーブルをテーブルらしく単矩形範囲に纏める工夫をする、
というのが、最適解のような気もしますが、
きっと何らかの制約があってのこと、なのでしょうね。

◆まずは、力技。
　名前の定義で
　　複数矩形範囲のリスト（複数のテーブル）を参照して、
　　　（"矩形範囲=テーブル"の数は可変。
　　　　多めに仕込んでおく意味で、例では、最大５矩形範囲）
　　矩形範囲毎のマッチング結果を配列として扱い、
　　得られた配列をOR関数に掛けて結果を得る
　という方法。

シートイメージ
　Sheet4　（テーブル）
　　G1:H1　：　早稲田　慶應
　　J1　：　上智
　　L1　：　立教
　検索値を設定するシート
　　A1:A13(各単セルが参照先)　
　　　立教
　　　学習院
　　　関西
　　　関西学院
　　　慶應
　　　上智
　　　青山学院
　　　早稲田
　　　中央
　　　同志社
　　　法政
　　　明治
　　　立命館

準備
　名前の定義
　｜　名前　:　学リストR
　｜　(適用)範囲　：　ブック
　｜　参照範囲（セル参照）　：　=Sheet4!$G$1:$H$1,Sheet4!$J$1,Sheet4!$L$1
　などのように、「とびとびのセル範囲に名前を付けてやってみる」。

数式
　=OR(CHOOSE(ROW($A$1:INDEX($A$1:$A$5,AREAS(学リストR),)),COUNTIF(INDEX(学リストR,,,1),A1),COUNTIF(INDEX(学リストR,,,2),A1),COUNTIF(INDEX(学リストR,,,3),A1),COUNTIF(INDEX(学リストR,,,4),A1),COUNTIF(INDEX(学リストR,,,5),A1)))
　CSE配列数式として確定した後に必要なだけフィルドラッグ。

ちょこっと説明
　ひとつの関数内で、複数矩形範囲を総当たりで演算させることは
　（総和を求めるSUM関数以外では）できません。ので、
　各単矩形範囲ごとの計算結果を配列として一旦返しておいて
　それらをCHOOSEとROWを使って総当たりでORに掛けて解を求めます。
　上記の例では[学リストR]には３つの矩形範囲しかありませんので、
　　COUNTIF(INDEX(学リストR,,,4),A1)　COUNTIF(INDEX(学リストR,,,5),A1)
　は部分的に#REF!エラーを返します。このエラーにORが邪魔されないように、
　　ROW($A$1:INDEX($A$1:$A$5,AREAS(学リストR),))
　の部分で、何番目の領域（矩形範囲）までを計算させるか指定しています。
　テーブル相当の複数矩形範囲を追加する時に、数式を書き直さなくても済む、
　という一点に特化した設計ですので、その他の面で長所は見つけ難いとは思います。

編集能
　CHOOSE()関数は理論上、254まで引数を指定できるのですが、
　どれほどまで"とびとび"になることを想定するのか判断した上で、
　最大の矩形範囲（テーブル）数に合わせて数式を追加・修正する必要があります。
　　$A$5
　の、5 の部分が最大の矩形範囲数に相当しますので、
　これを 6 に増やす場合は、この部分を
　　$A$6
　数式の最後の２文字 )) の前に
　　,COUNTIF(INDEX(学リストR,,,6),A1)
　を挿入、といった具合に追加・修正します。
　検索値の参照先変更については置換機能を使って A1 をその他のセル参照に書換える
　とか、一般的な方法で済みます。

◆次は、名前の定義の活用に重きをおいて、数式を単純なものにする方法です。
　シートイメージ、準備については、前項を踏襲した上で、ひとつだけ追加。

準備
　名前の定義
　｜　名前　:　学リスト
　｜　(適用)範囲　：　ブック
　｜　参照範囲（配列定数）　：　={"早稲田","慶應","上智","立教"}
　などのように、直接、配列定数を定義しておきます。

数式
　=OR(A1=学リスト)
　CSE配列数式として確定した後に必要なだけフィルドラッグ。

説明
　>何度もA1=を入れるのが面倒なので2.）のような方法で他のセルを参照させる方法はないでしょうか？
　ある種の"面倒"を減らす方向で考えたものですが、
　テーブル相当の複数矩形範囲を追加する時に、
　もう一手間掛ける、という運用になります。

編集能
　数式に関しては、説明の必要はないでしょう。
　テーブル追加時の"一手間"に相当するマクロが以下。
　　Dim c As Range
　　Dim sBuf As String
　　For Each c In ThisWorkbook.Names("学リストR").RefersToRange
　　　　sBuf = sBuf & ",""" & c & """"
　　Next
　　sBuf = "={" & Mid$(sBuf, 2) & "}"
　　Debug.Print "ソース", sBuf; vbLf; "↓"
　　ThisWorkbook.Names("学リスト").RefersToLocal = sBuf
　　Debug.Print "名前定義", ThisWorkbook.Names("学リスト").RefersToLocal
　ソーステーブル群をセル範囲参照する[学リストR]は、
　この運用では必要ありませんから、
　　For Each c In Sheets("Sheet4").Range("G1:H1,J1,L1")
　みたいに、マクロの記述の中で、テーブルの追加・削除・修正を行うようにすれば、
　それ程、煩わされることもないのかな、とは思います。
　テーブルを編集する権限のある方が、マクロの編集を出来ない場合は、
　例示のまま、
　　[学リストR]の参照範囲を変更して、
　　上記マクロを原形のまま実行
　　↓
　　結果、[学リスト]の書換え完了
　という手順を徹底することになります。

解答（提案？）としては以上ですが、どこら辺を簡単にすればお気に召すか、
考え方によっては、よりニーズに即した提案も出来るのかも知れません。
一例めの数式については、特に推敲してませんから、マシな方法が見つかる可能性大です。
趣旨としては、運用面・設計面での提案、といった所になるかと思います。
冒頭にも述べましたが、複数矩形範囲を扱わずに済むような設計、ということに、
ＶＢＡの技術を活用するとか、可能であれば、数式を用いて単テーブルに纏める、とかも
一応、ご検討なさってみて下さい。

ちょっと気になったので、誤解のないように、つけたします。
　=SUM(A1:B2,G6,L8)
　　第１から第３までの引数に、単矩形範囲参照を計３つ指定
　　カンマは引数の区切り
　=SUM((A1:B2,G6,L8))
　　ひとつの引数に、３つの領域（単矩形範囲）を持つ参照を指定
　　カンマは参照演算子
どちらも返す結果は同じになりますが、
後者のように書いた場合は、複数矩形範囲を総当たりで演算していることになります。
　=SUM(複数の領域を持つ定義された名前)
のように書いた場合も同様になります。
SUM()関数だけは特別便利というか、気の利き具合が格別なんですよね。

長文多謝。以上です。

お礼

ありがとうございます。
今回は
=OR(COUNTIF(INDIRECT({"G1";"J10";"L2"}),A1))
で対応できました。

回答No.7

tsubu-yuki

> 実際は行、列ともに不規則な参照先

そこそこよく見る表現ではありますが、これは
「作る（編集する）度に参照先がランダムに移動する・追加される」
という意味なのでしょうか？
だとしたら、ひじょーーーーーーに実用的ではありませんね。
その度に式なり名前がついた範囲なりを変えてやらなきゃならないのですから。

さて、さらなる後出しを期待しつつ。

COUNTIF関数は「連続しない範囲を受け付けない（2013&2010で検証）」ですよ。
だって、不連続範囲の指定は「カンマ区切り」ですから。
なので、COUNTIF関数では一手では困難というか、一手では事実上不可能です。
なお、COUNTIFS関数は範囲と条件が交互にくるので、やはり無理です。

出来る可能性があるとしたら、
各行に作業列（=COUNTIF(行範囲,A1)）、
集計したいセルに式（=IF(SUM(作業列範囲)>0,"TRUE","FALSE")
でしょうか。
ただこれだと、参照したい範囲外の「早稲田」もカウントしますし、
部分一致（例えば「早稲田卒」とか）だとカウントしませんから、
本題の主旨には添わないかもしれませんね。

あとはお好みで
　　　　If Range("Sheet1!名前").Find(What:=Range("A1"), _
　　　　　　　　　　　　　　　　LookIn:=xlValues, _
　　　　　　　　　　　　　　　　LookAt:=xlPart) Is Nothing Then
　　　　　　　　Range("B1") = "False"
　　　　Else
　　　　　　　　Range("B1") = "True"
　　　　End If
参照したい不連続の範囲に「名前」という名前つき範囲を事前に定義し、
部分一致で値を検索するときの例です。
完全一致にするときは、xlPart ⇒ xlWhole に書き換えます。
名前付き範囲の参照範囲は
　=Sheet1!$D$3,Sheet1!$G$8,Sheet1!$E$19,Sheet1!$G$13:$G$17
こんな感じでカンマ区切りしてやります。

お礼

VBAではなくエクセル関数での方法を探していました。
ありがとうございます。
今回は
=OR(COUNTIF(INDIRECT({"G1";"J10";"L2"}),A1))
で対応できました。

補足

>「作る（編集する）度に参照先がランダムに移動する・追加される」
という意味なのでしょうか？

違います。
例えば、G1、J10、L2とかのように規則性がないということです。

回答No.5

chie65536（@chie65535）

＞実際は行、列ともに不規則な参照先なのです。

「行や列が複雑な参照先」は、連続的に範囲指定（Ctrlキーを押しながらセルの選択）を行なって、複雑な形に範囲指定した上で「複数のセルに名前を付ける」ことができます。

名前を付けた「複数のセル」は

=COUNTIF(定義した名前,A1)>0

のように、式の中で使う事ができます。

例えば「A3~F5、F7～G9、A2、B13、G15」の複数のセルを「Ctrlキーを押しながらセルの選択」をして「名前の定義」で「あいう」と言う名前を付けて

=COUNTIF(あいう,A1)>0

と書けば「A1と同じ値が、A3~F5、F7～G9、A2、B13、G15のどこかに有るかどうか」が判ります。

お礼

ありがとうございます。
名前の定義でできるときき、早速試しました。
連続する一塊のセル範囲の場合はOKでしたが、とびとびのセル範囲に名前を付けてやってみるとVALUEエラーになってしまいました。

回答No.4

bunjii

>2.）=OR(A1={"早稲田","慶應","上智"})
>しかし2.）は　=OR(A1={F1,G1,H1})　と書き換えるとエラーになってしまいます。
前者は定数の配列値を数式に定義するときの書式で、後者には使えません。
配列のセルを参照する場合は次のように記述します。
=OR(A1=F1:H1)
但し、カッコ内の値が配列値なので数式を確定するときにCtrl+Shift+Enterの打鍵が必要です。

>参照先をF1:H1と連続しているように書きましたが、実際は行、列ともに不規則な参照先なのです。
後出しジャンケンはいけませんね。
=OR(A1=F1:F1:G1:G1:H1:H1)
=OR(A1=F1,A1=G1,A1=H1)と等価になります。
カッコ内の比較動作はA1=F1:F1とA1=G1:G1とA1=H1:H1になりますので数式の簡略化にはなりません。

お礼

ありがとうございます。
すみませんでした。

回答No.3

kagakusuki

回答No.1です。
　基本はCOUNTIFを使った方法ですが、別法として次の様な方法もあります。

=ISNUMBER(MATCH(A1,F1:H1,0))

お礼

何度もありがとうございます。
すみませんでした。

回答No.2

chie65536（@chie65535）

=NOT(ISERROR(HLOOKUP(A1,F1:H1,1,0)))

F1～H1のように「横１行の並び」なら、HLOOKUPが使えます。

F1～F3のように「縦１列の並び」なら、VLOOKUPが使えます。

お礼

ありがとうございます。確かに質問の例ではこれで行けますね。
ごめんなさい。質問を簡単にするために、参照先をF1:H1と連続しているように書きましたが、実際は行、列ともに不規則な参照先なのです。
最初からそう書けばよかったですね、すみません。

回答No.1

kagakusuki

=COUNTIF(F1:H1,A1)>0

としてみて下さい。
　因みにIF関数と組み合わせる場合には「>0」すら不要で

=IF(COUNTIF(F1:H1,A1),"真の場合","偽の場合")

でOKです。

お礼

なるほど！
ありがとうございます。
ただ、質問を簡単にするために、参照先をF1:H1と連続しているように書きましたが、実際は行、列ともに不規則な参照先なのです。
最初からそう書けばよかったですね、すみません。