Excel2003 Match関数について

前へ 次へ
このQ&Aのポイント
  • Excel2003のMatch関数について教えてください。VBAを使用してデータベース検索を行いたいのですが、特定のキーでの検索がうまくいきません。
  • Excel2003のMatch関数は、指定した値を検索範囲内で探し、該当する位置を返します。しかし、希望するキーでの検索ではエラーが発生してしまいます。
  • Vlookup関数では、検索する値は検索範囲の最初の列になければなりませんが、Match関数でも同様の制約があるのでしょうか？他の検索方法も教えていただけると助かります。
回答を見る
  • ベストアンサー

Excel2003　Match関数について

WINXP　Excel2003　VBA　データベース検索について教えて下さい。 Sub データ表示() Dim no As Long With Sheets("データ入力") On Error GoTo エラー処理 no = WorksheetFunction.Match( _ .[B1], [データ].Resize(, 1), 0) On Error GoTo 0 .[B5] = [データ].Cells(no, 2) .[D5] = [データ].Cells(no, 3) .[E5] = [データ].Cells(no, 4) .[F5] = [データ].Cells(no, 5) .[B7] = [データ].Cells(no, 6) .[C7] = [データ].Cells(no, 7) .[D7] = [データ].Cells(no, 12) .[B9] = [データ].Cells(no, 8) .[B11] = [データ].Cells(no, 9) .[B13] = [データ].Cells(no, 10) .[D13] = [データ].Cells(no, 11) Exit Sub エラー処理: MsgBox "該当するNo.のデータはありません" .[B5:F5].ClearContents .[B7:F7].ClearContents .[B9:F9].ClearContents .[B11:F11].ClearContents .[B13:F13].ClearContents End With End Sub というプログラム（EXCEL極意6VBAという参考文献の一部を利用)で、1枚のシート(”データ”という 名前のデータベース）51列ほどあります。からキー（一番右端の列をキー）の値と一致したデータを 別のシートへ呼び出す方法です。 そこで、こまっているのが、あるキーだとちゃんと呼び出してくるのに、肝心の希望のキーだと、エラーになって該当データ無となってしまうのです。 簡単にかくと ”データ”というデータベース A列　　　　　B列　　　　　　C列　　　　　　　D列・・・・・・・・　AY列 001　　　　あああ　　　　　AAAA　　　　　2012/08/20　　　120821002　←このAY列をキーとして検索 をかけるとエラー処理にいってしまう。 B列”あああ”だとうまくデータを検索してくる。 Vlook関数だとキーとなるデータは一番左端にないといけないということなのですが、Match関数もそうなのでしょうか？ ちなみにオートフィルターでのVBAだとうまくいくのですが、・・・ もしこのつたない文で、御理解いただき、おわかりになる方が、いらっしゃれば、ご指導ください。 もしくは、Match関数以外での検索方法でもけっこうです。 最終的に希望する結果は、D列の日付(重複する日付有）で絞り込み、AY列のキー（重複するデーターは無）でその行のデーターを別シートに表示させること

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

  • ベストアンサー
回答No.2

データの範囲を明示的に指定すれば、別の列でも検索できるようにはなります。理由はよく判りません。 それと[データ]の範囲は拡張してますか？ 例えば、キーがＦ列の場合、 Hit = WorksheetFunction.Match( _ .[B1], [データ].Range("F1:F99"), 0) On Error GoTo 0

muuuug
質問者

補足

たびたびありがとうございます。 昨日より、ずっと頭を抱えております。 このプログラムでＢ１の値と[データ]の値についてなのですが、[データ]にある値をＢ1にコピーしても 一致する値がないと返ってきてしまいます。 御指摘のようにしても、同様なのです。 単純なこと(例えば変数の文字列宣言しているとか。(確認済みですが)）なのでしょうが 理解できません。 デバッグでＢ1の値と[データ]の値を見てもどう見ても同じ数字なのですが、認識しません。 [データ]の値を手打ちで直してみても同じなのですが。 おわかりになれば、御指導下さい。 宜しくお願い致します。

その他の回答 (3)

  • _Kyle
  • ベストアンサー率78% (109/139)
回答No.4

#3です。 すみません。 どうも動作仕様を誤解していたようです。 検索したいのは [データ]範囲の【51列目】 すなわち [データ].Columns(,51)　　'[データ]の51列目 なのですね。 質問文のコードは 「行と列が逆」なのではなく 「検索する列を指定できていない」でした。 no = WorksheetFunction.Match( _ .[B1], [データ].Columns(,51), 0) として、 [データ]の【51列目】を検索するようにしてやれば通るかと。 --------- 先述のとおり [データ].Resize(, 1)　　'[データ]の1列目 A列のことなので てっきり [データ].Resize(1)　　'[データ]の1行目 を検索する場面かと思ってました。 勘違い怪答大変失礼いたしました。　<(_ _)>

画像を拡大する
muuuug
質問者

お礼

締め切り後にもかかわらず、わざわざありがとうございます。 ためしてみますね(*^^)v 本当にありがとうございました。 また、多分いろいろ質問すると思いますが宜しくお願い致します。<m(__)m>

  • _Kyle
  • ベストアンサー率78% (109/139)
回答No.3

ぇっと、行と列が逆になってますけど？ ------------------------------------- 　[データ].Resize(, 1) というのは 　[データ]範囲の【　１列目　】 つまり、Ａ列のことです。 検索したいのは 　[データ]範囲の【　１行目　】　 すなわち 　[データ].Resize(1) なのでは？ ------------------------------------- 転記部分も 　.[B5] = [データ].Cells(no, 2) というのは 　[データ]範囲の第no【行】目の値をB5に転記する 記述になってます。 以上ご参考まで。

muuuug
質問者

お礼

ありがとうございます。本当ですね 訂正して、うまくいけば本当に単純なことだったんですね。 う～ん。でもやっぱり症状はかわらないので、MATCH関数を使わない別の方法でやってみます。 どうもありがとうございました。

muuuug
質問者

補足

その後、いろいろ試してみたところ、[データ].Range("AY1:AY65531"))と".Resize(1)をはずすと うまく値をひろってきました。”AY65531はシートの最後の値65536から一番上の空白行3行とフィールド行の4行分をひいた値でうまくいきました。 いろいろありがとうございました。 これで一応解決(？)とさせていただきます。

回答No.1

gokui6.exeのどのファイル？ AY列、これは日付？

muuuug
質問者

補足

jazzcorpさんありがとうございます。 補足します。 極意の5章5です。AY列は、請求番号になります。 宜しくお願いします。

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. その他MS Office製品

関連するQ&A

  • excel関数の計算について

    office2016 oracleのテーブル1からデータ取得し、データ加工してoracleのテーブル2にデータを登録という流れの中で、データ加工処理に時間がかかっています。 下記excel　VBAの内容は sheet1のＣ列：シリアルデータ(8桁文字列　例ABCD0001） sheet1のＡＥ列：日付データ（8桁文字列　例20200612） sheet1のＣ列全体をsheet2のＡ列へコピー sheet1のＡＥ列全体をsheet2のＢ列へコピー sheet2のＦ列に開始時刻データ(14桁文字列） sheet2のＧ列に終了時刻データ(14桁文字列） を設定 という内容になっています。 Ｆ列は日付データの稼働日３日前で時刻が0845　例20200609084500 Ｇ列は日付データの時刻が1700　例20200612170000 という計算を実施します。 T_非稼働日シートには工場の非稼働日データ（2020/05/05みたいな休日等）をＡ列に設定しています。 excel上で計算方法の設定は”手動”にしておき700件くらいのデータに関して下記マクロ実行すると約30[s]程度かかります。 sub test() Application.Calculation = xlCalculationManual 'sheet1 最終行の取得 Dim LASTROW As Long LASTROW = Worksheets("sheet1").Cells(Rows.Count, 3).End(xlUp).ROW 'sheet1のシリアルデータ(Ｃ列）をsheet2 A列へコピー Worksheets("sheet1").Range(Worksheets("sheet1").Cells(1, 3), Worksheets("sheet1").Cells(LASTROW, 3)).copy _ Destination:=Worksheets("sheet2").Cells(1, 1) 'sheet1の納期データ(ＡＥ列）をsheet2 B列へコピー Worksheets("sheet1").Range(Worksheets("sheet1").Cells(1, 31), Worksheets("sheet1").Cells(LASTROW, 31)).copy _ Destination:=Worksheets("sheet2").Cells(1, 2) 'Ｆ列　日付データの稼働日３日前で時刻が084500 Worksheets("sheet2").Range("F1:F" & Range("A" & Rows.Count).End(xlUp).ROW).Value = "=TEXT(WORKDAY(DATE(MID(RC[-4],1,4), MID(RC[-4],5,2), MID(RC[-4],7,2)),-3,T_非稼働日!C[-5]),""yyyymmdd"")&""084500""" 'Ｇ列 日付データの時刻が170000　例20200612170000 Worksheets("sheet2").Range("G1:G" & Range("A" & Rows.Count).End(xlUp).ROW).Value = "=RC[-5]&""170000""" Application.Calculation = xlCalculationAutomatic end sub Ａ列　　　Ｂ列 ABCD0001 20200612 ABCE0001 20200608 ABCE0002 20200610 ABDD0001 20200616 ABDD0002 20200623 … 約600行 これを end sub Ａ列　　　Ｂ列 　　　　Ｆ列　　　　　　Ｇ列 ABCD0001 20200612　20200609084500 20200612170000　 ABCE0001 20200608　20200603084500 20200608170000 ABCE0002 20200610　20200605084500 20200610170000 ABDD0001 20200616　20200611084500 20200616170000 ABDD0002 20200623　20200618084500 20200623170000 … の様にするのに30[S]くらいの時間がかかっている。Ｆ列データ作成が遅い状況。 ところが、 Application.Calculation = xlCalculationManual Application.Calculation = xlCalculationAutomatic の内容を削除し、計算方法の設定を”自動”で実施すると8[S]程度で終了します。 計算方法は手動の方が早いはずなのに自動の方が速いのは何故なのでしょう？ また600行の計算（Ｆ列データを作成する部分です）に関して、もう少し早く出来る構成がありましたら教えていただきたく。

  • エクセルのMATCH関数について

    エクセルのMATCH関数についての質問ですが、どうしてもエラーが表示され悩んでいます。どうか教えていただければと思います。 たとえば下記のような場合なぜかエラーが発生してしまいますがどうしてでしょうか？ 　　　　　A列　　　B列　　　C列　　　D列　　　E列　　　F列 1行　　　　　　　　　　　　　　A　　　　　B　　　　A+B　　戻り値 2行　　　　2.0　　　　　　　　1.0　　　　1.2　　　2.2　　　2 3行　　　　2.2　　　　　　　　0.9　　　　1.1　　　2.0　　　1 4行　　　　2.4　　　　　　　　1.1　　　　1.3　　　2.4　　　#N/A 5行　　　　2.6　　　　　　　　1.0　　　　1.4　　　2.4　　　3 使用している関数は、 E列2行 =C2+D2　　F列2行 =MATCH(E2,$A2:$A5,0) E列3行 =C3+D3　　F列3行 =MATCH(E3,$A2:$A5,0) E列4行 =C4+D4　　F列4行 =MATCH(E4,$A2:$A5,0) E列5行 =C5+D5　　F列5行 =MATCH(E5,$A2:$A5,0) です。ここでどうしても、F列4行にエラー値(#N/A)が発生してしまいます。どうしてでしょうか？ E列4行とE列5行は同じ値にもかかわらず戻り値が違うのはどうしてでしょうか？ また、不思議なことにE列4行に手入力で2.4の値を入力した場合は戻り値は3となり、正しい値を表示します。どうしてでしょうか？ 以上、よろしくお願いします。

  • Ｍａｔｃｈ関数がうまく機能していない？？

    すみません。また教えて下さい。 過去ログを見てシート１にあったデータをシート４にあるデータと照らし合わせてすでにあれば書き換え、なければ追加というようにできるようにしたく過去ログを参考にしてやったのですが、どうしてもエラーが出てしまいます。 Private Sub aa() Dim intlastrow1 As Integer Dim strb As String Dim longlastrow1 As Long intlastrow1 = Sheets(1).Range("A7").End(xlDown).Row longlastrow1 = Sheets(4).Range("A1").End(xlDown).Row Dim c As Object Dim rtn As Variant Dim d As Integer With Sheets(4) .Select For Each c In .Range("A1", "A" & longlastrow1) rtn = Application.Match(c.Value, Sheets(1).Range("A7:A" & intlastrow1), 0) d = c.Row strb = Cells(d, "A").Value If IsError(rtn) Then With Sheets(4).Cells(longlastrow1 + 1, "A") .Value = strb With .Font .Name = "ＭＳ Ｐゴシック" .Bold = False .Size = 8 End With End With Sheets(4).Cells(longlastrow1 + 1, "B").Value = Sheets(1).Range("A2").Value Sheets(4).Cells(longlastrow1 + 1, "F").Value = ShowFormula(Sheet1.Range(Cells(d, "J"), Cells(d, "N"))) longlastrow1 = longlastrow1 + 1 End If If Not IsError(rtn) Then Exit Sub End If Next c End With End Sub 以上のように組んだのですがうまくいきません。 具体的に言うとシート１のＡ７よりしたに名前が並んでいる（山田、鈴木・・・）とお考え下さい（シート４のＡ２以下にも同様に名前が並んでいる）。字数の関係で判定後の処理が不十分になっています。

  • VBAでのMATCH関数の使用

    現在，VBAにてシートの比較プログラムを行っておりますが，その中で検索範囲を示すRangeに名前を使用したいのですがエラーでうまくいきません。 エラーは 「実行時エラー：1004 WorksheetFunctionクラスのMatchプロパティが取得できません」 とでます。 記述は Application.WorksheetFunction.Match(Cells(r, 132), Range("リース型具Key1"), 0) としており，”リース型具Key1”が事前に名前登録してある名前です。 方法をご存知の方，よろしくお願いします。

  • Ｅｘｃｅｌ関数について

    Ａ列の1行目に A C D F X のデータが入ってきます。 Ｂ列の1行目に 0 1 2 9 T V のデータが入ってきます。 条件はＡ列の1行目がAまたはFでＢ列の1行目が0の場合は10％ 条件はＡ列の1行目がAまたはFでＢ列の1行目がTの場合はブランク 条件はＡ列の1行目がDの場合はブランク 条件はＡ列の1行目がXの場合は100％ 上記以外は0％ の条件を満たすための関数の設定の仕方を教えてください。

  • アクティブでない複数のシートのセルのコンテンツを一度でクリアしたい

    エクセルVBAで、アクティブでない複数のシートの所定のセルのコンテンツを一度でクリアしたいのですが、 現在アクティブではないシートをアクティブにしないでclearcontentsするとアプリ定義、オブジェクト定義エラーになってしまいます。 それぞれのシートをアクティブにしないとclearcontentsできないのでしょうか？ どなたか教えてください。よろしくお願いします。 ------------------------------------------------------------------------------- Sub CLEAR_CONTENTS() Sheets(\"1\").Range(Cells(6, 3), Cells(6, 8)).ClearContents Sheets(\"2\").Range(Cells(6, 3), Cells(6, 8)).ClearContents Sheets(\"3\").Range(Cells(6, 3), Cells(6, 7)).ClearContents End Sub

  • Excel関数　２つの条件で集計するには？

    エクセル関数についての質問があります。 Ａ列に赤・黄・青のいずれか Ｂ列に○・△・□のいずれか Ｃ列に数値のデータが入力されているとします。 このとき、Ａ列が赤且つＢ列が○となっている 数値を集計する関数もしくは方法はありますか？ ＳＵＭＩＦでは条件は一つしか使えませんよね？ 現在はＣ列の数値を、Ｂ列が○のときはＤ列に Ｂ列が△のときはＥ列に、Ｂ列が□のときはＦ列に 表示させるようにし、Ｂ列をキーにＤ～Ｆ列の数値 をそれぞれ集計しています。 件数が多いときはめんどうなので、いい方法が ありましたら、教えてください。 よろしくお願いします。

  • Matchの処理について

    下記の処理がどうしてもうまくいかなくて、 皆様のお知恵を拝借できればありがたいです。 Sheet1に下記のように縦に３列データがならんでいます。 A　　あ　　10 A　　い　　12 A　　う　　16 B　　あ　　19 B　　い　　15 B　　う　　 7 これをもとにSheet2に下記の通りマトリクス形式に 変換する。 　　あ　　い　　う A 　10　　12　　16 B 　19　　15　　 7 これを処理しようと以下の通り記述したのですが、 マッチする項目がなかった場合、どうも行（列）が ずれてヒットしているようです。 On Error Resume Nextが原因のような気がするのですが。 これを回避するにはどうしたらよろしいでしょうか？ お助けください～。 よろしくお願い致します。 Dim i As Long Dim j As Long Dim k As Long Dim 検索値A As Variant Dim 検索値B As Variant On Error Resume Next i = 2 Do While (Sheets("SHEET1").Cells(i, 1) <> "") 検索値A = Sheets("SHEET1").Cells(i, 1).Value 検索値B = Sheets("SHEET1").Cells(i, 2).Value j = Application.Match(検索値A, Sheets("Sheet2").Range("範囲A"), 0) k = Application.Match(検索値B, Sheets("Sheet2").Range("範囲B"), 0) Sheets("Sheet2").Cells(j, k).Value =Sheets("SHEET1").Cells(i, 3) i = i + 1 Loop End Sub

  • アプリケーション定義またはオブジェクト定義のエラー

    ＶＢＡを勉強し始めて１週間ほどになります。 そこで、掲題のエラーが出てしまい、何が原因か分からず途方にくれてます。 掲題のエラーで検索すると、同じようなエラーで悩んでいる人がいますが、 私の事例を解決する案を見つけることが出来ませんでしたので、 今回質問させていただきます。 まず、下記で示すプロシージャとは別のＳＵＢプロシージャで計算し、表を 作成します。表は、計算条件によって行数が変わります。 その表をクリアさせるのに、下記で示すSUBプロシージャを作成しました。 Public Sub 値のクリア() Dim a_clr As Integer 'Ａ列の縦の値 Dim b_clr As Integer 'Ｂ列の縦の値 Dim MaxRow As String '表の最終行を取得 Dim MaxCol As String '表の最終列を取得 MaxRow = Cells(Rows.Count, 4).End(xlUp).Row '表の行の最終行を取得 MaxCol = Cells(7, Columns.Count).End(xlToLeft).Column '表の列の最終列を取得 MsgBox (MaxRow) MsgBox (MaxCol) Worksheets("計算").Range("d7", Cells(MaxRow, MaxCol)).ClearContents ←　エラーになる。 Worksheets("計算").Range("d7", Cells(MaxRow, 16)).ClearContents 　　　←問題なく動作します。 End Sub これを動作させると、掲題のエラーが出ます。 エラーが出る箇所は、Rangeプロパティの行です。 プログラム中にも書いてますが、書き方により動作したり しなかったりします。 Range("d7", Cells(MaxRow, MaxCol)).ClearContents ←　エラーになります。 Range("d7", Cells(MaxRow, 16)).ClearContents　　　　←　問題なく動作します。 デバッグモードの時に、Cells(MaxRow, MaxCol))の中の変数（MaxRow, MaxCol）にマウス を持っていくと、数値が表示されます。 その数値は、私が必要としている数値がきちんと入っています。 それなのに、なぜここでとまっているのか分かりません。 また、Cells(MaxRow, MaxCol)をCells(MaxRow, 16)の用に数値にすると 問題なく動作する理由もよく分かりません。 変数の指定の仕方などが悪いのか、今一理解しきれていないのが原因かも 知れませんが、アドバイスをいただけると助かります。 よろしくお願いします。

  • UsedRangeを使ってソートすることは可能？

    UsedRangeを使ってソートすることは可能ですか？ エクセルですが 学年　　組　　出席番号 2　　　　A　　　　1 1　　　　B　　　　2 と言うデータがあり、 これにフィルタをかけつつ、Ａ列で昇順に並び替えるＶＢＡを作ってるのですが Sub さんぷる() オートフィルタをかける Range(Cells(1, 1), Cells(1, Cells(1, Columns.Count).End(xlToLeft).Column)).AutoFilter UsedRange.Sort Key1:=Cells(1, 1), order1:=xlAscending End Sub とすると、 UsedRangeでコンパイルエラーになるのですが どうすればよろしいでしょうか？

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する