• ベストアンサー
※ ChatGPTを利用し、要約された質問です(原文:エクセルのOR関数でのセル参照)

エクセルのOR関数でセル参照する方法

このQ&Aのポイント
  • エクセルのOR関数を使って他のセルを参照する方法について解説します。
  • エクセルのOR関数で、別の表のセルを参照する方法について考えます。
  • A1セルが早稲田、慶應、上智のいずれかであればTRUEを返すOR関数を、別の表のセルを参照して使う方法を説明します。

質問者が選んだベストアンサー

  • ベストアンサー
  • Chiquilin
  • ベストアンサー率30% (94/306)
回答No.6

「配列定数」ですから定数でしか指定できません。どうしても「セル」を 指定して比較したいなら =ISNUMBER(FIND(0&A1&0,0&F$1&0&G$1&0&H$1&0)) のようにセルを文字列として区切り文字を挟んで結合してから計算する か(範囲で指定したい場合は TEXTJOIN関数でないと無理です) =OR(COUNTIF(INDIRECT({"F1";"G1";"H1"}),A1)) のようにセルアドレスを文字列で表現して配列定数に組み込むかです。 因みに下方の式は単なる論理式では計算しません。 COUNTIFのような セルアドレスを値に変換する工程が1つ必要になります。

emaxemax
質問者

お礼

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

その他の回答 (7)

回答No.8

こんにちは。お邪魔します。 一応、追加補足にもお応えする形で、解決策を2点挙げてみます。 提示された条件「とびとびのセル範囲をテーブルとして扱うこと」 が、Excel的には特殊なものですから、難度が高い課題となっていて、 スマートな解、というのは、直接的には見つからないかな、と。 基本に立ち返って、テーブルをテーブルらしく単矩形範囲に纏める工夫をする、 というのが、最適解のような気もしますが、 きっと何らかの制約があってのこと、なのでしょうね。 ◆まずは、力技。  名前の定義で   複数矩形範囲のリスト(複数のテーブル)を参照して、    ("矩形範囲=テーブル"の数は可変。     多めに仕込んでおく意味で、例では、最大5矩形範囲)   矩形範囲毎のマッチング結果を配列として扱い、   得られた配列を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]には3つの矩形範囲しかありませんので、   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  数式の最後の2文字 )) の前に   ,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]の参照範囲を変更して、   上記マクロを原形のまま実行   ↓   結果、[学リスト]の書換え完了  という手順を徹底することになります。 解答(提案?)としては以上ですが、どこら辺を簡単にすればお気に召すか、 考え方によっては、よりニーズに即した提案も出来るのかも知れません。 一例めの数式については、特に推敲してませんから、マシな方法が見つかる可能性大です。 趣旨としては、運用面・設計面での提案、といった所になるかと思います。 冒頭にも述べましたが、複数矩形範囲を扱わずに済むような設計、ということに、 VBAの技術を活用するとか、可能であれば、数式を用いて単テーブルに纏める、とかも 一応、ご検討なさってみて下さい。 ちょっと気になったので、誤解のないように、つけたします。  =SUM(A1:B2,G6,L8)   第1から第3までの引数に、単矩形範囲参照を計3つ指定   カンマは引数の区切り  =SUM((A1:B2,G6,L8))   ひとつの引数に、3つの領域(単矩形範囲)を持つ参照を指定   カンマは参照演算子 どちらも返す結果は同じになりますが、 後者のように書いた場合は、複数矩形範囲を総当たりで演算していることになります。  =SUM(複数の領域を持つ定義された名前) のように書いた場合も同様になります。 SUM()関数だけは特別便利というか、気の利き具合が格別なんですよね。 長文多謝。以上です。

emaxemax
質問者

お礼

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

回答No.7

> 実際は行、列ともに不規則な参照先 そこそこよく見る表現ではありますが、これは 「作る(編集する)度に参照先がランダムに移動する・追加される」 という意味なのでしょうか? だとしたら、ひじょーーーーーーに実用的ではありませんね。 その度に式なり名前がついた範囲なりを変えてやらなきゃならないのですから。 さて、さらなる後出しを期待しつつ。 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 こんな感じでカンマ区切りしてやります。

emaxemax
質問者

お礼

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

emaxemax
質問者

補足

>「作る(編集する)度に参照先がランダムに移動する・追加される」 という意味なのでしょうか? 違います。 例えば、G1、J10、L2とかのように規則性がないということです。

回答No.5

>実際は行、列ともに不規則な参照先なのです。 「行や列が複雑な参照先」は、連続的に範囲指定(Ctrlキーを押しながらセルの選択)を行なって、複雑な形に範囲指定した上で「複数のセルに名前を付ける」ことができます。 名前を付けた「複数のセル」は =COUNTIF(定義した名前,A1)>0 のように、式の中で使う事ができます。 例えば「A3~F5、F7~G9、A2、B13、G15」の複数のセルを「Ctrlキーを押しながらセルの選択」をして「名前の定義」で「あいう」と言う名前を付けて =COUNTIF(あいう,A1)>0 と書けば「A1と同じ値が、A3~F5、F7~G9、A2、B13、G15のどこかに有るかどうか」が判ります。

emaxemax
質問者

お礼

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

  • bunjii
  • ベストアンサー率43% (3589/8249)
回答No.4

>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になりますので数式の簡略化にはなりません。

emaxemax
質問者

お礼

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

  • kagakusuki
  • ベストアンサー率51% (2610/5101)
回答No.3

 回答No.1です。  基本はCOUNTIFを使った方法ですが、別法として次の様な方法もあります。 =ISNUMBER(MATCH(A1,F1:H1,0))

emaxemax
質問者

お礼

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

回答No.2

=NOT(ISERROR(HLOOKUP(A1,F1:H1,1,0))) F1~H1のように「横1行の並び」なら、HLOOKUPが使えます。 F1~F3のように「縦1列の並び」なら、VLOOKUPが使えます。

emaxemax
質問者

お礼

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

  • kagakusuki
  • ベストアンサー率51% (2610/5101)
回答No.1

=COUNTIF(F1:H1,A1)>0 としてみて下さい。  因みにIF関数と組み合わせる場合には「>0」すら不要で =IF(COUNTIF(F1:H1,A1),"真の場合","偽の場合") でOKです。

emaxemax
質問者

お礼

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

関連するQ&A