Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


6097 / 13645 ツリー ←次へ | 前へ→

【47202】数式ではなく値MATCHさせたい わさび 07/3/5(月) 13:09 質問[未読]
【47209】Re:数式ではなく値MATCHさせたい Jaka 07/3/5(月) 14:40 発言[未読]
【47211】数式ではなく値でMATCHさせたい わさび 07/3/5(月) 14:50 質問[未読]
【47221】Re:数式ではなく値でMATCHさせたい Jaka 07/3/5(月) 16:00 発言[未読]
【47224】Re:数式ではなく値でMATCHさせたい わさび 07/3/5(月) 16:47 質問[未読]
【47239】Re:数式ではなく値でMATCHさせたい ichinose 07/3/5(月) 23:07 発言[未読]
【47240】Re:数式ではなく値でMATCHさせたい わさび 07/3/6(火) 9:21 お礼[未読]
【47225】Re:数式ではなく値MATCHさせたい Kein 07/3/5(月) 16:49 発言[未読]
【47231】Re:数式ではなく値MATCHさせたい わさび 07/3/5(月) 18:05 お礼[未読]
【47235】Re:数式ではなく値MATCHさせたい Kein 07/3/5(月) 18:47 発言[未読]
【47241】Re:数式ではなく値でMATCHさせたい わさび 07/3/6(火) 9:28 お礼[未読]
【47244】おまけ Jaka 07/3/6(火) 10:20 発言[未読]
【47245】Re:おまけ わさび 07/3/6(火) 10:23 お礼[未読]

【47202】数式ではなく値MATCHさせたい
質問  わさび  - 07/3/5(月) 13:09 -

引用なし
パスワード
   いつもお世話になっております。
今回、集計表の仕様変更に伴い、従来のコードに支障が出てしまいました。
どうかお力をお貸しください。


他のブックのTOTALというシートのA列に、元のブックのセルK1と同じ値があるかどうか調べ、
もしあれば、その値のある行に対して諸々の処理をしようとしています。
この「他のブック」というものがワークシート関数だらけでとても重く、
しかもそれを多いときには100以上ものブックを順次調べなければなりません。
そこでブックを開かないですむよう、
以下のようなコードで同じ値がある行番号を調べて処理していました。
今まではこのA列に値そのものが入っていたので、このコードで処理できていましたが、
仕様変更に伴い、A列には数式が入るようになってしまい、MATCHしなくなってしまいました…

With .Cells(i, 3)
  .Formula = "=MATCH(K1,'×××\[○○○.xls]TOTAL'!A:A,0)"
  .Value = .Value
End Wit

これを、数式ではなくその値でMATCHさせたいのですが、
どうしたらよいのかアイディアが浮かびません。
MATCH関数は範囲だけでなく、配列に対しても行なえるようですが、
私のスキルでは、ブックを開かずに配列に取り込むことができずに断念。

なお、A列の最終行は変動し、該当する値が入っていない場合もあります。


MATCH関数にこだわることはなく、他の方法も調べてみたのですが、
好ましい情報になかなか当たりません。
何か道標になるようなことがありましたら、どうかお与え下さい。
宜しくお願い致します。

*Windows2000, Excel2000

【47209】Re:数式ではなく値MATCHさせたい
発言  Jaka  - 07/3/5(月) 14:40 -

引用なし
パスワード
   ▼わさび さん:
>With .Cells(i, 3)
>  .Formula = "=MATCH(K1,'×××\[○○○.xls]TOTAL'!A:A,0)"
>  .Value = .Value
>End Wit
>
>これを、数式ではなくその値でMATCHさせたいのですが、
意味がよく解りませんでした。
??。
.Formula = "=MATCH(" & "検索したい文字" & ",'×××\[○○○.xls]TOTAL'!A:A,0)"

【47211】数式ではなく値でMATCHさせたい
質問  わさび  - 07/3/5(月) 14:50 -

引用なし
パスワード
   ▼Jaka さん:

うまく説明できずに申し訳ありません。

以前まで、参照ブックのA列には文検索したい字列そのものが入っていました。
(例「JP00001」)
ところが仕様が変わり、参照ブックのA列には数式(関数)が入り、
(例「=IF(C16="",(以下略)」
その結果の値が検索したい文字列としてシート上には表示されているのですが、
この状態でMATCH関数を利用すると、
検索したい文字列「JP00001」と、数式「=IF(C16="",(以下略)」との比較になり、
値は一致しているのに、MATCH関数の返り値としてはFalseとなってしまうのです。

なので、「=IF(C16="",(以下略)」という文字列で比較してしまうのではなく、
この結果の値同士で、なんとか比較できないものかと思い、質問させていただいた次第です。

【47221】Re:数式ではなく値でMATCHさせたい
発言  Jaka  - 07/3/5(月) 16:00 -

引用なし
パスワード
   すみません。間違えてました。
.Formula = "=MATCH(" & """検索したい文字""" & ",'×××\[○○○.xls]TOTAL'!A:A,0)"

>MATCH関数の返り値としてはFalse
Falseが返るんですか??
#N/Aじゃなくて....。
なんか、他が間違っている世にも思えます。
まず、その関数を直接書き込んでその関数が会っているのか確認された方がいいと思います。
今のところこのぐらいしか返事ができないです。

【47224】Re:数式ではなく値でMATCHさせたい
質問  わさび  - 07/3/5(月) 16:47 -

引用なし
パスワード
   ▼Jaka さん:

>すみません。間違えてました。
>.Formula = "=MATCH(" & """検索したい文字""" & ",'×××\[○○○.xls]TOTAL'!A:A,0)"
ありがとうございます。
この書き方は検索したい文字の決め打ちになってしまうのですが、
セルK1の値は変動してしまうので、この場合はどう表現すればよいのでしょうか?
間違ってると分かってても、つい、
.Formula = "=MATCH(" & """K1""" & ",'" & FOLDERpath & "\[" & nowID & ".xls]TOTAL'!C:C,0)"
で試してみましたが、当然、K1という値はないわけで…藁にもすがる思いです。


>>MATCH関数の返り値としてはFalse
>Falseが返るんですか??
>#N/Aじゃなくて....。
すみません。Falseは私の勘違いで、ちゃんと#N/Aが返ってきます。
申し訳ありません。


>なんか、他が間違っている世にも思えます。
>まず、その関数を直接書き込んでその関数が会っているのか確認された方がいいと思います。
>今のところこのぐらいしか返事ができないです。
関数を直接書き込んでも確認してみましたが、同様の結果です。
そしてこの結果というのは、MATCH関数として正常な結果です。
私自身はJakaさんがおっしゃってることを理解できているつもりなのですが、
だからこそ私の質問内容がうまく伝えられなくて、とてももどかしいです…

もう1度、もう1度だけ説明させてください。

<<操作しているブック>>
・検索したい文字はセルK1の値(変動する)…例:JP00001
・MATCH関数の返り値を入れるセルも、このブック

<<参照ブック>>
・操作しているブックのセルK1の文字(例:JP00001)が、
 このブックのTOTALというシートのA列にあるかどうかを調べたい。
 あれば、それが何行目にあるかを返したい。
・TOTALのA列の各セルには、値(文字)ではなく数式(関数)が入っている
  →表示されている文字:関数の結果の値(例:JP00001)
              …VBAでいうところのRange(○○).Text??
   実際に入力されている内容:関数そのもの(例:=IF(C16="",…(以下略)
・このブックは開くときにとても重いので、開かないで処理したい

この状態で、操作しているブックに直接
=MATCH(K1,'×××\[○○○.xls]TOTAL'!A:A,0) と書き込んでも、
VBAで書き込んでも、#N/Aが返ってきます。

この結果というのが、MATCH関数では当然のもの…つまり、
参照ブックに直接入力されている「=IF(C16="",…(以下略)」を文字列のように扱ってるようで、
いくらその関数の結果の値が「JP00001」だったとしても、
操作しているブックのセルK1の値「JP00001」とは一致しないとなってしまうようです。
なので、MATCH関数に限らず、数式そのもので検索されるのではなく、
数式の結果を見てくれるようにできないものかと悩んでおります。

…結局、何度書いても同じことしか書けなくてすみません(-_-;

【47225】Re:数式ではなく値MATCHさせたい
発言  Kein  - 07/3/5(月) 16:49 -

引用なし
パスワード
   >「他のブック」というものがワークシート関数だらけでとても重く、
>しかもそれを多いときには100以上ものブックを順次調べなければなりません。
ブックを開かないで「値を引っ張る」ぐらいは容易にできます。リンクを使うのです。
>もしあれば、その値のある行に対して諸々の処理
さらに、作られている表がデータベース形式なら、DAO等で編集も可能になります。
もちろんセルの書式を弄るなどは出来ませんが、値は変更できるということです。
で、どのような方法をとるにせよ、数式をたくさん抱えたままブックを保存するのは
好ましくありません。せっかくマクロを使うのだから「数式の入力〜計算結果を
値のみに変換する」というプロシージャを作るべきです。場合によってはブックを
閉じるときは、計算結果さえクリアして保存することも考えます。いずれ又、開いて
仕事をするときに計算プロシージャを実行すれば良いからです。
データ量が多すぎると感じたら、それはCSVファイルなどに分離・退避して保存する
ようにします。もっとも例のExcel2007だと、2003以前より飛躍的にセル数が
増えているので、データベースソフトを導入するかどうか検討するぐらいだったのが、
必要なくなるかも知れないらしいです。マシンスペックを考慮しなければ、という
条件は付くでしょうけど。

【47231】Re:数式ではなく値MATCHさせたい
お礼  わさび  - 07/3/5(月) 18:05 -

引用なし
パスワード
   ▼Kein さん:
アドバイスいただき、ありがとうございます!!


>ブックを開かないで「値を引っ張る」ぐらいは容易にできます。リンクを使うのです。

リンクを使うというのは、こういうことでしょうか?
FormulaR1C1 = "='×××\[○○○.xls]TOTAL'!R■C□"
これを使って、操作しているブックに値を引っぱってきて、
それでいろいろ処理を行なおうとも考えたのですが、
参照ブックの最終行の取得を、ブックを開かずに行なうことができずに断念してしまいました…


>で、どのような方法をとるにせよ、数式をたくさん抱えたままブックを保存するのは
>好ましくありません。

まったくもって同感です。
ひとつのブックを開くのに、20秒もかかるなんて…
機会があれば仕様の変更を提案したいところなんですが、
(会社の組織上で)上のほうの人が作ったソフトに、
こんなペーペーがケチをつける機会なんて、そう滅多にないので…
今はまだこの好ましくない仕様をひたすらフォローするソフトを作るしかありません(-_-;

【47235】Re:数式ではなく値MATCHさせたい
発言  Kein  - 07/3/5(月) 18:47 -

引用なし
パスワード
   >参照ブックの最終行の取得を、ブックを開かずに行なうことができず
その問題は「リンク式を入力する範囲を、予想される最大データ量より大きくとる」
ことで解決するはずです。つまり、そこにおいても「数式の結果をすぐに値のみに戻す」
ことによって、大量の数式を抱えることを回避する考え方を用いるのです。
具体的には以下のようなコードにします。

With Range("A1:A1000")
  .Formula = "='×××\[○○○.xls]TOTAL'!A1"
  .Copy
  .PasteSpecial xlPasteValues
  .Cells(1).Select
End With
Application.CutCopyMode = False

値のみに戻す範囲が大きいときは、なるべくコピペを使います。
値の代入をするコードだとメモリーの消費量が多く、不安定になる懸念が
あるからです。
で、結局
>それでいろいろ処理を行なおう
が、どういうものか分かりませんが、リンク元を編集するのでなければ、
それで簡単に検索できるようになりますよね。

【47239】Re:数式ではなく値でMATCHさせたい
発言  ichinose  - 07/3/5(月) 23:07 -

引用なし
パスワード
   ▼わさび さん:
こんばんは。

>もう1度、もう1度だけ説明させてください。
仰っていることは何となく分かりますけどね・・・、
再現ができません。

まず、「ほら、このとおりにの手順で二つのブックを作成すると
申し上げたとおりの現象になりますね!!」

と記述できるような再現手順書が必要です。


例えば、新規ブックの標準モジュールに

'==============================================================
Sub ready()
  With Workbooks.Add
    With .Worksheets("sheet1")
     With .Range("c1:c30")
       .Formula = "=""JP"" & text(row(),""000000"")"
       .Value = .Value
       End With
     With .Range("a1:a30")
       .Formula = "=if(c1="""","""",c1)"
       End With
     End With
    Application.DisplayAlerts = False
    .SaveAs ThisWorkbook.Path & "\Refsample.xls"
    .Close False
    Application.DisplayAlerts = True
    End With
  With ActiveSheet
    .Range("a1").Value = "JP000005"
    .Range("b1").Formula = "=match(a1,'" & ThisWorkbook.Path & "\[Refsample.xls]Sheet1'!A:A,0)"
    End With
End Sub


上記のコードをコピーし、一度適当な名前で保存した後に上記のreadyを
実行してみて下さい(仮にsmp1.xlsというブック名で適当なフォルダ下に保存します)。


その後、プロシジャーreadyを実行してみて下さい。


参照ブックをsmp1.xlsと同じフォルダ下に「Refsample.xls」というブック名で
作成します。

Refsample.xlsのSheet1のセルA1からA30に
数式「=IF(C1="","",C1)」を設定してあります。
(C1からC30には文字列を入力しています)


このブックのSheet1のA列を開かずに参照することを考えます。

smp1.xlsのアクティブシートのA1に"JP000005"、
B1に 

=MATCH(A1,'D:\xxxx\[Refsample.xls]Sheet1'!A:A,0)

       ↑ここは、実際のフォルダ名

という数式が設定されますが、正常な値(5)を返していますよね?

わさびさんが今度は、#N/Aになる再現手順を提示してください。

【47240】Re:数式ではなく値でMATCHさせたい
お礼  わさび  - 07/3/6(火) 9:21 -

引用なし
パスワード
   ▼ichinose さん:

サンプルファイルを作成するコードまでご提示いただき、恐縮のしきりです。
動作を確認させていただきましたが、
参照先が数式でも、問題なく検索できるんですね。
数式を本来のものに変更しても試しましたが、こちらも問題なく動作しました。

#N/Aとなる再現サンプルを提示しようとしたのですが、
単純なファイルに直すと、こちらも問題なく動作してしまい、再現ができません…
ということは、単純ではない部分が悪さをしているということであり、
「元凶は参照ファイルである」と焦点を絞って、原因究明をしていきたいと思います。

何はともあれ、参照先が数式でもちゃんと検索できるということがわかっただけでも、
本当に助かりました。ありがとうございました。
何か原因がわかりましたら、改めてご報告させていただきます。

【47241】Re:数式ではなく値でMATCHさせたい
お礼  わさび  - 07/3/6(火) 9:28 -

引用なし
パスワード
   ▼Kein さん:

貴重なアイディアをいただき、本当にありがとうございます。
コピペだとこんなに早いのかと、目から鱗が落ちる思いです。

>>それでいろいろ処理を行なおう
>が、どういうものか分かりませんが、リンク元を編集するのでなければ、
>それで簡単に検索できるようになりますよね。
参照ブックのA列に検索文字列があれば、
同じ行のE〜Z列を操作ブックに引っぱってきて、
あとは操作ブックでその値を利用して処理を行ないます。

直接MATCH関数で何行目かを求められない原因追究と並行し、
このコピペを利用するアルゴリズムへの変更で再検討しようと思います。
一縷の望みが出てきて、本当に助かりました。
ありがとうございました。

【47244】おまけ
発言  Jaka  - 07/3/6(火) 10:20 -

引用なし
パスワード
   ▼わさび さん:
>コピペだとこんなに早いのかと、目から鱗が落ちる思いです。
たとえば、
=""
などの場合、完全な空白にはならない事を把握していた方が良いかも。
もっとも、データ→区切り位置→そのまま完了で完全な空白になるけど。
因みに
.Value = .Value
は、空白になります。

【47245】Re:おまけ
お礼  わさび  - 07/3/6(火) 10:23 -

引用なし
パスワード
   ▼Jaka さん:

>=""
>などの場合、完全な空白にはならない事を把握していた方が良いかも。
>.Value = .Value
>は、空白になります。

なるほど、知りませんでした。
これは今後かなりキーポイントになりそうな情報です。
ありがとうございます!

6097 / 13645 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free