Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

このQ&Aに関連する最新のQ&A

A 回答 (5件)

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す



 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。
    • good
    • 17
この回答へのお礼

ありがとうございました!できました!
VBAをいじり始めてみたのですが、今ひとつだったので大変有り難いです。
関数も使ったことのないものを教えていただき、勉強になりました。
こころから感謝申し上げます。

お礼日時:2008/01/15 13:37

#1です



やはり、ご質問の内容を、変更することをお勧めします。

ばらばらに並んでいる、現在の元データをVlookupで検索できるように並び替えることを考えたほうが早いかもしれません。

例での氏名とIDで抽出可能な条件はありませんか?
まずは行単位での抽出を考えましょう。
    • good
    • 1
この回答へのお礼

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

お礼日時:2008/01/15 13:38

エクセルでは関数を使ってでは、抜き出しは苦手(式が複雑)です。


この質問コーナーで、私はimogasi方式という作業列を使うやり方でたくさん答えています。Googleで「imogasi方式」で照会すれば、OKWAVEでの同類の質問が相当数でで、そこには私以外の回答も出てきます。参考にしてください。
>ユーザー定義で作りたいと
やってご覧よ。2つ以上の値を返すユーザー関数って、そうたやすく勉強できないよ。
(A)それよりある列の全セルをIF文で聞いて、該当かどうか調べて、該当なら、好みの列に1行ずつ下へずらして書き出す、を考えた方が格段に易しいよ。
上記(A)と絡めて、条件の第x番目の該当行を帰すユーザー関数を考えたら。
    • good
    • 1
この回答へのお礼

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

お礼日時:2008/01/15 13:47

INDEX関数とMATCH関数の組み合わせのことだと思います。



A   B
氏名 ID

であった場合
D1にID入れて、E1に
=INDEX(A:A,MATCH(D1,B:B),1)
それぞれの関数の意味はHelpで調べてみてください。
そのほか、色々と使い道のある関数です。
    • good
    • 4
この回答へのお礼

ありがとうございます。
私が扱っているデータの具体的な例を挙げさせて頂きます。
まさし 00038
ゆみこ 00025
ひろし 00041
たろう 00040
というような形ではなく、

まさし 00038
   ゆみこ 00025
      ひろし 00041
 欠席 たろう 00040
のように、バラバラに並んでいる形になっています。

関数の意味を見てみたのですが、どちらもA1:A200とかB1:B200という指定はできても、
A1:F200という指定はできないような気がするのです・・・。

氏名やIDがA列やB列にあるとは限らず、
ある人は氏名がC列にあったり、
またある人は氏名がD列にあったりします。
IDがそのすぐ隣にあることは保証されているのですが・・・。
どうぞよろしくお願いいたします。

お礼日時:2008/01/14 22:37

いちばん簡単なのはある範囲をVlookupで検索できるように変更することです。



ある範囲が数列ならそれぞれ名前定義してVlookupで探して、なければ次の名前、なければ次の名前とするのも手かもしれません。

ある範囲の規模や並び方がわかればもう少し有効な策もあるかもしれないですね。
    • good
    • 1
この回答へのお礼

どうもありがとうございます。
「ある範囲」とは列が6列、行が200行ぐらいです。
検索したい値がA列にあるのか、B列にあるのか、まったくのランダムで、
B列にその値がある時も、その隣のA列のセルが、空白なのか、何か値が入っているのか、それもランダムになります。
その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
どうぞよろしくお願いします。

お礼日時:2008/01/14 21:50

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Q結合セルのデータ消去でエラーになります。どのようにすればいいでしょうか?

Range(”A1:A6”)にデータが入っていますが、そのうちA3、A4、A5セルは
結合されています。Range(”A1:A6”)のデータをクリアするために以下
コーディングをしましたが、結合セルの先頭でエラー(実行時エラー 1004)に
なってしまいます。
結合セルのデータクリアはどのようにすればいいのでしょうか。
お分かりの方よろしくご教示ください。

Sub Sample()
Dim i As Long
For i = 1 To 6
Cells(i, 1).ClearContents
Next i
End Sub

Aベストアンサー

横から失礼。
ちょっと不思議な展開のような気がしてます。

Range("A1").Value = ""
セルの表示形式が文字列の場合は本当の空白セルになりませんよね?
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
MsgBox IsEmpty(Range("A1"))
それに
Range("A65536").End(xlUp).Select

そもそも
Range("A1:A6").ClearContents

私が何か勘違いしてるのかしらん?

#ひょっとしてA3:A5が結合してるのではなくてそれぞれが横方向に結合してたりするのかなぁ。
#その場合でもRange("A1:A6").Value = Emptyで良いはずだけど。

QExcel;特定の値を入力するとその隣のセルの値を減算するようにしたい。

シートαに
  A     B  C   D
1 たまねぎ |4|豚肉肩|300 
2 にんじん |3|ひき肉|400
3 ジャガイモ|7|いわし|250

のように以下ずらずらっと材料を記入しています。

実は今回、任意の材料名を書くとその隣の値から減算される、というふうにしたいと思っています。

たとえばシートβの好きな場所に

     いわし|150

と書くと、シートαが

  A     B  C   D
1 たまねぎ |4|豚肉肩|300 
2 にんじん |3|ひき肉|400
3 ジャガイモ|7|いわし|100

というふうにしたいのですが、どういった関数を利用していけばいいんでしょうか・・・

Aベストアンサー

シートBに
材料名と引き算する値のリストを作成します。
引き算しない場合は、未入力か0を入れます。
シートAで
材料の数を入れる時に
=250-VLOOKUP(C3,B!$A$1:$B$6,2,FALSE)
の様に入力します。
説明:
シートBから該当値を探してきます
VLOOKUP(C3,B!$A$1:$B$6,2,FALSE)

VLOOKUP(いわしを検索,シートB!対応表の範囲,2列目から値を持ってくる,一致した場合だけ)

QExcelで結合したセルのデータをコピー

お世話になっています。
うまく活字にできそうもありませんが・・・。

A1:F1のセルを結合したものが5行ありますが、そのデータを1行ずらしたいんです。
ところが、コピーしたものを1行ずらして被せると、セルの結合が解除されてしまいます。
全く別のセルにコピーする場合は解除されませんが、1行上にずらしたいんです!
セルが結合したまま、データごとコピーする方法はありますか?
補足しますので、助けて下さい!

Aベストアンサー

>1行毎でしたらご紹介いただいた方法でできますが、複数行まとめますと
>うまくできません(>_<)
1行上にずらすんですね。
その場合は、A1(~F1)を選択して切り取り、A6~F6を選択して、「切り取ったセルの挿入」をすれば、一度にずらせます。
ちなみに、上にずらすのだから、1行目のデータは無くなるんですよね。

QINDIRECT関数で隣のセルの値を参照させるとエラー値#REF!が@excel(エクセル

エクセル初心者です。

「月次」というシートのC列に計算式が入ってるとします(←DATE関数とMONTH関数、IF関数の組み合わせ)。C列の見た目は、C1が1、C31が31、のように、縦に1~31の数字が入っているように見えます。
例えば、B1に月の数字を入れると、C列の数字がその月に対応して見た目が変わります。例えばB1が7月だとC列は31日まで表示され、2月だと28日まで、のような感じです。でも、計算式は31日分入っているので、例えばC31は、月によって、見た目がブランクになる場合と数字になる場合とがあります。参考ページ:http://kokoro.kir.jp/know/calendar2.html

ここで隣のD列に、見た目でC列に数字が表示されてるときは、別シートの数字を返したいとします。その参照させたいシートは31枚あり、例えば「1」というシートのD5の値を、「月次」のD1に返したい、という意味です。ただ、見た目でC31がブランクのときは、D31には何も返さず空白にしたいのです。

参照させたいシートが31枚あるため、オートフィル機能を使いたく、そこで、INDIRECT関数を使ってやろうとしてるのですが
参考ページ http://www.relief.jp/itnote/archives/001697.php

上記のとおりやろうとすると、「月次」のD列に#REF!がでます。
(エラーの出る計算式:INDIRECT(C1&"!D5")」

試しに「月次」のK列あたりに、1~31まで手入力して数字をいれ、そのセルを参照させるとうまくいくのですが、
(この場合の計算式:INDIRECT(K1&"!D5")」

ただ、30日の月や28日の月もあり、毎回K列を変えないといけないので、何とかC列を参照させたいのですが、何かよい解決方法はないでしょうか?

エクセル初心者です。

「月次」というシートのC列に計算式が入ってるとします(←DATE関数とMONTH関数、IF関数の組み合わせ)。C列の見た目は、C1が1、C31が31、のように、縦に1~31の数字が入っているように見えます。
例えば、B1に月の数字を入れると、C列の数字がその月に対応して見た目が変わります。例えばB1が7月だとC列は31日まで表示され、2月だと28日まで、のような感じです。でも、計算式は31日分入っているので、例えばC31は、月によって、見た目がブランクになる場合と数字になる場合...続きを読む

Aベストアンサー

#1です。あ、そうですね。月末処理を忘れてました。

 =IF(C1="","",INDIRECT(DAY(C1)&"!D5"))

に修正します。

Qエクセルでデータをコピーした時にセルの結合

エクセルでデータをコピーした時にセルの結合はできませんか?

たとえば、以下のリンクの方法をやった後にセルの結合はできませんか?
そのセルの結合を手作業でやると1時間かかってしまいます。
これをどうにか、もっとかんたんに方法にできませんか?
セルの結合をしたい物は図みたいになっています。
※セルの結合したい物はもっとたくさんあります。

ワードとエクセルのバージョンは2003です。

http://oshiete.goo.ne.jp/qa/6437543.html

図の説明をします。
赤い矢印と赤い矢印のセルを結合したいのです。
※結合したい物は1000ぐらいあります。

回答宜しくお願いします。

Aベストアンサー

連続した横方向の2つのセルを結合したいということでしょうか?

その場合は、1つだけ結合セルを作成し、そのセルを右クリックでオートフィルコピーして「書式のみコピー(フィル)」を選択して下さい。

Qマクロ「A1の値は、セル範囲でA1:AB39の右2文字が YR と書いてあるセルの右隣の値」

よろしくお願いします
いつもgooの皆さんに大変お世話になっています
エクセルは2013です

マクロで
「A1の値は、セル範囲でA1:AB39の値(文字列)の右端2文字が YR と書いてあるセルの右隣の値」
はどういうコードになるでしょうか

例えば セル D10 の値(文字列)が、「2月合計yr」でしたら
セル A1 には 右隣のセル E10 の値を表示したいのです

右2文字は Ucase(Right(セル,2)) ですが
「セル範囲A1:AB39で、そのセルの右端2文字がYRと書いてあるセルの右隣のセル」を
探す方法が分かりません

Aベストアンサー

本来、以下は省略して書くことが多いのですが、明示的に書かせていただきます。

Ucase(Right(セル,2)) ->MatchCase:=False
全角半角の違いは、MatchByte:=False
末尾のyr を探すのは、What:="*yr"  (LookAt:=xlWholeも必要)
現在のコードは1回限り After:=Range("A1") 続けるなら、ActiveCell

私は、ここのカテゴリでは、質問者さんから、答えだけ書いてりゃいいのだ、というので、みなさんから評判が悪いようです。
どなたかの回答をお待ちになっても良いかと思います。

'//
Sub SearchChars()
 Dim c As Range
 With Range("A1:AB39")
 Set c = .Find( _
  What:="*yr", _
  After:=Range("A1"), _
  LookIn:=xlValues, _
  LookAt:=xlWhole, _
  SearchOrder:=xlByRows, _
  MatchCase:=False, _
  MatchByte:=False)
    If Not c Is Nothing Then
     c.Offset(, 1).Value = Range("A1").Value
    End If
 End With
End Sub
'//

本来、以下は省略して書くことが多いのですが、明示的に書かせていただきます。

Ucase(Right(セル,2)) ->MatchCase:=False
全角半角の違いは、MatchByte:=False
末尾のyr を探すのは、What:="*yr"  (LookAt:=xlWholeも必要)
現在のコードは1回限り After:=Range("A1") 続けるなら、ActiveCell

私は、ここのカテゴリでは、質問者さんから、答えだけ書いてりゃいいのだ、というので、みなさんから評判が悪いようです。
どなたかの回答をお待ちになっても良いかと思います。

'//
Sub SearchChars()
 Dim c...続きを読む

Q「エクセル」で、「セルの結合」をしても2つ目のデータが消えないようにしたいのですが

「エクセル2000」で、別々のセルに入力しているデータ(文字や数字)を、
それぞれ生かしたままセルを統合することは出来るのでしょうか?

「セルの書式設定」で「セルの結合」をすると、左上端の文字は残りますが、
それ以外のデータは消されてしまいますよね?

例えば、A1のセルに「20」、B1のセルに「30」と入って
いる場合、結合すると「20」は残って「30」のデータは消えて
しまいますよね。
それを、一つのセルに「2030」と統合出来るように
したいのですが…
(出来れば「20/30」のように、
間に「/」等が入るようにしたいのですが…)

無理でしょうか?

Aベストアンサー

「セルの結合」では無理です。
もう一つセルを使って、くっつけて表示するしかないです。

> 例えば、A1のセルに「20」、B1のセルに「30」と入って
> いる場合
(snip)
> 出来れば「20/30」のように間に「/」等が入るようにしたい

を C1 に表示するには、

  =A1 & "/" & B1

と式を入力します。元のA1やB1を見たくなければ、
列を選択して「表示しない」とすれば良いでしょう。

Q(COUNTIFS ?)B列に任意の値があり、D列からF列の間にも任意の値がある場合に値を返す関数式

会社で修理業者に物品の修理を依頼する仕事をしているのですが、修理の際に出張費がかかる場合があり、余計な出費を避けるため、エクセルで素早くチェックできるようにしたいと思っております。

添付ファイルのとおり、任意の行のB列と同じ値が他の行のB列にあって(業者名が同じで)、かつ、その行の修理日・修理日2・修理日3のいずれかの値から前後6日以内の値が他の行において修理日・修理日2・修理日3にあった場合に、G列に「調整」という文字が入るようにしたいと思っております。

どのように関数式を組めばよいのか、どなたかご教授願います。

Aベストアンサー

VBA で良かったでしょうか?
ダメならスルーということで

処理範囲の行は A3 ~ A列データのある行とします
行の範囲を求めたら、B列 ~ F列を配列に読み込みます
(C列は非表示?)
修理業者をベースに Dictionary にデータを覚えていきます
Dictionary は3段構成
1段目キー:修理業者名
2段目キー:行番号
3段目キー:日付

Dictionary に展開できたら、
各修理業者の異なる行番号での日付差をグルグルチェックしていきます

※ B列 ~ F列を読み込んだ配列 vA は、
Dictionary に覚えたら不要になるので、
vA の1列目を結果書き出し用に再利用しています


Public Sub Samp1()
  Dim dic As Object
  Dim vA As Variant, vK As Variant
  Dim vRp As Variant, vRc As Variant
  Dim vDp As Variant, vDc As Variant
  Dim i As Long, j As Long
  Dim bSkip As Boolean
  Const CDAYW As Long = 6
  Const CMOJI As String = "調整"

  Set dic = CreateObject("Scripting.Dictionary")

  With Range("A3", Cells(Rows.Count, "A").End(xlUp))
    vA = .Columns("B:F").Value
    For i = 1 To UBound(vA)
      If (vA(i, 1) <> "") Then
        For j = 3 To UBound(vA, 2)
          If (vA(i, j) <> "") Then
            If (Not dic.Exists(vA(i, 1))) Then
              dic.Add vA(i, 1) _
                , CreateObject("Scripting.Dictionary")
            End If
            If (Not dic(vA(i, 1)).Exists(i)) Then
              dic(vA(i, 1)).Add i _
                , CreateObject("Scripting.Dictionary")
            End If
            dic(vA(i, 1))(i)(vA(i, j)) = Empty
          End If
        Next
        vA(i, 1) = ""
      End If
    Next

    For Each vK In dic.Keys
      For Each vRp In dic(vK).Keys
        If (vA(vRp, 1) = "") Then
          bSkip = False
          For Each vDp In dic(vK)(vRp).Keys
            For Each vRc In dic(vK).Keys
              If (vRp <> vRc) Then
                For Each vDc In dic(vK)(vRc).Keys
                  i = Abs(DateDiff("d", vDp, vDc))
                  If (i <= CDAYW) Then
                    vA(vRp, 1) = CMOJI
                    vA(vRc, 1) = CMOJI
                    bSkip = True
                    Exit For
                  End If
                Next
                If (bSkip) Then Exit For
              End If
            Next
            If (bSkip) Then Exit For
          Next
        End If
      Next
    Next
    .Columns("G").Value = vA
  End With

  Set dic = Nothing
End Sub

VBA で良かったでしょうか?
ダメならスルーということで

処理範囲の行は A3 ~ A列データのある行とします
行の範囲を求めたら、B列 ~ F列を配列に読み込みます
(C列は非表示?)
修理業者をベースに Dictionary にデータを覚えていきます
Dictionary は3段構成
1段目キー:修理業者名
2段目キー:行番号
3段目キー:日付

Dictionary に展開できたら、
各修理業者の異なる行番号での日付差をグルグルチェックしていきます

※ B列 ~ F列を読み込んだ配列 vA は、
Dictionary に覚えたら不要になるので、
...続きを読む

Q"結合をしていない複数セルの中央にデータを表示"同じことを縦でやりたい

「データ並べ替え」をするシートの際、結合せずに"結合をしていない複数セルの中央にデータを表示"を使うことはよくあることだと思います。

しかしこの機能は、横のセルにしか使用出来ません。

同じようなことを、縦書きのセルで実現したいと考えています。

何かいい方法は無いでしょうか?
ご教授頂ければと思います。

Aベストアンサー

別のシートで「縦にセルを結合し、その縦の中央に表示」を作成しておいて、それを当該シートに、図のリンク貼り付けするのはいかがでしょうか?

メニューバー/ツールバーの表と裏
http://www2.odn.ne.jp/excel/waza/toolbar.html#SEC5

参考URL:http://www2.odn.ne.jp/excel/waza/toolbar.html#SEC5

Qエクセル 条件が一致する複数のセルを検索して、その隣のセルにある文字をつなげる関数はなんでしょうか?

エクセルでチェックボックスをつけた後、リンクしたセルが「TRUE」になった横のセルの文章をつなげて一つの文章にしたいのですが、どのような関数にすればよいのでしょうか。
イメージは、画像のA列のチェックボックスに、チェックした横のC列のセルの言葉を、セル「E2」につなげて表示したいです。
IF関数、VLOOKUP関数、CONCATENATE関数か「&」などを使うといいのかなとは考えましたが、どうしてよいかわからないので教えていただきたいです。
よろしくお願いいたします。

Aベストアンサー

[No.1お礼]へのコメント、
申し訳ない、説明が不十分でした。
提示した式は配列数式として入力しなければなりません。

「配列数式として入力」の意味
此処では式を例えば =SUM((A1:A10)*(B1:B10)) としておく
1.該当式をコピーして指定のセルに入力
 ̄ ̄(でも、まだ Enterキーを叩かない!)
2.Ctrl+Shift+Enter を「エイヤッ!」と叩き付け
3.[数式バー]を眺めて、下記のように、入力した式が { }で
 ̄ ̄囲まれていることを確認
 ̄ ̄ {=SUM((A1:A10)*(B1:B10))} ←配列数式!


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング