Results of 1 - 1 of about 33508 for 関数 (0.006 sec.)
- [[20240408133223]]
- #score: 2647
- @digest: 5527009714c69517f2182e2aa4b48b0b
- @id: 96567
- @mdate: 2024-04-26T07:23:11Z
- @size: 23469
- @type: text/plain
- #keywords: 個xx (72681), lastrow1 (40472), 目コ (16634), 文番 (13823), 用] (13597), 入荷 (9165), 列: (7416), 注文 (7014), xxxx (6908), イベ (6803), 業用 (6752), 品目 (5705), ベン (5571), 花子 (4484), 2024 (4169), change (4166), シー (4008), worksheets (3733), ws2 (3592), ート (3110), トへ (2803), 転記 (2789), シス (2577), 抽出 (2309), cells (2098), 発生 (2079), 貼り (2045), worksheet (1975), テム (1956), vlookup (1908), 数量 (1894), 実行 (1837)
- 『別シートに変更があったらマクロを実行したい』（おにぎり花子）
質問失礼いたします。 とあるシステムよりコピーしたデータをBシートへ貼り付けたら、BシートのデータをもとにAシートへ該当データを抽出するようにしたいと思いましたが中々うまくいきません。 Bシートへctrl+VでコピーしてきたデータをBシートのA1セルを選択し、貼り付けた後のBシートからAシートへのデータの抽出する処理に飛ばせたいです。抽出処理自体(sub 抽出())は実行できましたが、Bシートに変更があったらsub 抽出に飛ぶように…と思い、 Bシートのコードに Private Sub Worksheet_SelectionChange(ByVal Target As Range) Call 抽出 End Sub と入力してみました。その場合、そうしますと他システムからコピーしてきたデータの貼り付けはどこに入力するべきでしょうか。 Bシートに Private Sub Worksheet_SelectionChange(ByVal Target As Range) Range("A1").Select Activesheet.paste Call 抽出 End Sub とも入力してみましたが失敗しました。 下はsub 抽出の処理の一部です。 ↓ sub 抽出 Dim ws1 As Worksheet Dim ws2 As Worksheet 〜 〜 Set ws1 = ThisWorkbook.Sheets("A") Set ws2 = ThisWorkbook.Sheets("B") With ws2 ↓Bシートへ貼り付けた後H列を切り取りA列へ移動させ〜 .Range("H:H").Cut .Range("A:A").Insert 〜 〜 End With 〜〜〜 続く 〜〜〜 分かりにくかったらすみません。。。 いずれ、行いたい処理は、データをBシートへ貼り付け → 抽出の処理 に上手く繋がるようにしたいです。 どのような修正が必要か何卒ご指導よろしくお願いいたします。 ＜ 使用 Excel:Excel2016、使用 OS:Windows10 ＞ ---- 貼り付けたら、勝手に抽出して欲しい、ということなら、 Worksheet_Changeイベントですね。 とあるシステムよりコピーしたデータが法則性のあるテキストファイルやExcelブックなら、 直接そのファイルを読み込みますが。 （tkit） 2024/04/08(月) 15:45:19 ---- 一部かぶっていますが何点か。 ■1 細かい話ですが、誤用があるのでツッコミ。 誤 Bシートのコード 正 Bシートの（シート）モジュール ですよね? ■2 そして、(提示したコードの）作業内容としては↓ということですよね? 【Bシート】の【H列】が書き換えられたら（貼り付けられたら）以下の処理を実行 ThisWorkbook.Sheets("B").Range("H:H").Cut ThisWorkbook.Sheets("B").Range("A:A").Insert このように、セルの内容が書きかわったら〜という条件は【SelectionChange】ではなく【Change】イベントのお仕事です。 ■3 上記で述べたように、セルの内容が書き換わったらという条件でマクロを実行するのは【Change】イベントですが、実装にはいくつか留意すべき点があります。 まず、今回はマクロによりA列に挿入するので、考えなしに実装すると、 貼り付けすることにより【Change】イベントが発生 ↓ （マクロがA列に）挿入することにより【Change】イベントが発生 ↓ （マクロがA列に）挿入することにより【Change】イベントが発生 ↓ （マクロがA列に）挿入することにより【Change】イベントが発生 ↓ .... のように無限に連鎖してしまうので一工夫しておく必要があります。 具体的には、A列に貼り付ける（挿入する）前に、一次的にイベントを無効にしておくとよいでしょう。 Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect(Target, Me.Range("H:H")) Is Nothing Then Application.EnableEvents = False 'イベント発生を無効化 Me.Range("H:H").Cut Me.Range("A:A").Insert Application.EnableEvents = True 'イベント発生を有効化 End If End Sub ■4 また、他の部分の提示がないのでわかりませんが、Changeイベントは、セルの書き換え（同じ値であっても書き換えとみなします）があるたびに発動し、貼り付けによる書き換えかどうかは区別しません。（普通に入力（クリア）しただけでも書き換えとみなします） したがって、貼り付けた後のデータそのものを加工しようと考えているならば、何らかの対策はしておいたほうが良いです。 たとえば、H列に貼り付けた場合のみ動作してほしいのであれば、上記のように【書き換えのあったセル】が【監視したいセル】に含まれているかどうかで処理分岐すればよいです。 なお、貼り付けたら勝手に動作するというのは、一見ラクチンに思えるかもしれませんが場合によっては、動作してほしくないときまで動作するという可能性があることは頭の片隅に置いておいた方が良いでしょう。 ■5 ちなみに、上記で無限連鎖が起きるのは"同じシート"に挿入しているからです。 一般的には"抽出"と言われたら、それこそ、Aシートなどの結果出力用の別シートに、特定のものをピックアップして出力するようなイメージになると思います。 【抽出の処理】と表現されている部分が提示されていないので、現時点でこれ以上のアドバイスは困難ですが、行列を踏まえたレイアウトで、元データと希望する結果を説明いただければアドバイスできることがあるかもしれません。 （もこな2） 2024/04/09(火) 07:30:02 ---- tkit様、もこな2様早速本当にありがとうございます。 それと、初歩的な事を質問させていただくところからで大変申し訳ございませんが、tkit様のおっしゃっているWorksheet_Changeイベントというのは モジュールの上部左上をGeneralではなくWorksheetに、右上はChangeを選択するということでよろしいでしょうか。 もこな2様、↓がBシートへデータを貼り付けた後に実行したい処理を記載いたします。 この処理はアドバイスをいただく前の状態のままです。ws2がBシートになります。シート名は大文字にしました。(A、B) Aシートは一覧になっており、フィルタにて未入荷の商品のみを表示しております。そこへBシートに貼り付けたコードと、 Aシートのリストを比較して重複しないデータをBシートからAシートへ転記していくものです。 コードや品名、品目コード、日付、場所、等転記したいデータがlookupResult1〜7に記載している部分です。 転記後、フィルタをかけ未完了品のみが表示されるように戻します、という処理です。 Sub 抽出() Dim ws2 As Worksheet Dim lastRow1 As Long Dim lastRow2 As Long Dim v As Variant Dim i As Long, j As Long Dim k As Long Dim flag As Boolean Dim lookupResult1 As Variant Dim lookupResult2 As Variant Dim lookupResult3 As Variant Dim lookupResult4 As Variant Dim lookupResult5 As Variant Dim lookupResult6 As Variant Dim lookupResult7 As Variant Dim lookupResult8 As Variant Set ws2 = ThisWorkbook.Sheets("B") With ws2 .Range("H:H").Cut ← H列に基準にしたいコードがあるため、 .Range("A:A").Insert ← H列からA列に貼り付け End With Worksheets("A").Unprotect k = 0 Worksheets("A").AutoFilterMode = False With Worksheets("A") lastRow1 = .Cells(Rows.Count, 3).End(xlUp).Row lastRow2 = ws2.Cells(Rows.Count, 1).End(xlUp).Row For i = 2 To lastRow2 flag = False v = ws2.Cells(i, "A").Value For j = 6 To lastRow1 If .Cells(j, "C").Value = v Then flag = True Exit For End If Next j If flag = False Then k = k + 1 .Cells(lastRow1 + k, "C").Value = v lookupResult1 = Application.VLookup(Worksheets("A").Cells(lastRow1 + k, "C").Value, ws2.Range("A:AG"), 8, False) lookupResult2 = Application.VLookup(Worksheets("A").Cells(lastRow1 + k, "C").Value, ws2.Range("A:AG"), 22, False) lookupResult3 = Application.VLookup(Worksheets("A").Cells(lastRow1 + k, "C").Value, ws2.Range("A:AG"), 19, False) lookupResult4 = Application.VLookup(Worksheets("A").Cells(lastRow1 + k, "C").Value, ws2.Range("A:AG"), 21, False) lookupResult5 = Application.VLookup(Worksheets("A").Cells(lastRow1 + k, "C").Value, ws2.Range("A:AG"), 33, False) lookupResult6 = Application.VLookup(Worksheets("A").Cells(lastRow1 + k, "C").Value, ws2.Range("A:AG"), 15, False) lookupResult7 = Application.VLookup(Worksheets("A").Cells(lastRow1 + k, "C").Value, ws2.Range("A:AG"), 7, False) Worksheets("A").Cells(lastRow1 + k, "E").Value = lookupResult1 Worksheets("A").Cells(lastRow1 + k, "H").Value = lookupResult2 Worksheets("A").Cells(lastRow1 + k, "J").Value = lookupResult3 Worksheets("A").Cells(lastRow1 + k, "K").Value = lookupResult4 Worksheets("A").Cells(lastRow1 + k, "L").Value = lookupResult5 Worksheets("A").Cells(lastRow1 + k, "N").Value = lookupResult6 Worksheets("A").Cells(lastRow1 + k, "P").Value = lookupResult7 End If Next i End With ActiveWorkbook.CustomViews("抽出後フィルタ").Show Cells.Select ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=False Worksheets("B").Cells.Clear Application.ScreenUpdating = False Worksheets("A").Cells(lastRow1 + k, "C").Select End Sub 初めはAシートにボタンを作り、Bシートへデータを貼り付けた後にボタンを押して【抽出の処理】を行うようにしました。 ですが、シートに変更があったら動くように出来ることを知り、Bシートへ貼り付けたらそのまま【抽出の処理】を 行うことが出来れば、Bシートへ貼り付け後にAシートへ戻りボタンを押す手間を省くことが出来るのではないかと思いご相談させていただきました。 長くなってしまい申し訳ございません。 何卒、ご指導の程よろしくお願いいたします。 （おにぎり花子） 2024/04/09(火) 09:51:04 ---- >Worksheet_Changeイベントというのはモジュールの上部左上をGeneralではなく >Worksheetに、右上はChangeを選択するということでよろしいでしょうか。 ⇒そうです。 注意点はもこな2さんが解説されています。 まずは、ご自身でWorksheet_Changeイベントの動作確認をしてください。 私は貼り付けること自体が面倒と思うので、直接読み込みを選択しますが、 そうではないようなので、退散します。 （tkit） 2024/04/09(火) 10:45:07 ---- 横入り失礼 イベントプロシージャは手続きだけではなく、 意味を確認したほうがいいでしょう。 ごく大雑把にいうと、 SelectionChange : セルの選択状態が変わると自動的に実行されるもの （カーソルを動かすだけでその都度実行されてしまう） Change :セルを変更したとき（実際に異なるものにならない場合も対象）に自動的に実行される というものです。 カーソルを動かしただけで起動するものは"もってのほか"ですし、 ひとつのセルを変更しただけでも再実行するようなものは危険でしょう。 コードを拝見すると、 With ws2 .Range("H:H").Cut '← H列に基準にしたいコードがあるため、 .Range("A:A").Insert '← H列からA列に貼り付け End With これは2回以上実行したら、A列には所期するものが挿入されませんよ。（最初のG列,F列等・・・がA列に来る） その結果、想定するものが得られない、ということになりがちです。 ですから、 ・何度も実行しても破綻しないようなコードに修正する ・安易に何度も実行されないように慎重に実行する などの方策が必要でしょう。 私見では、最初の方式のように、ボタンで自分の意思で動作を起動するようにしたほうが安全です。 マクロ実行は、元に戻すことができませんので、不慣れなイベントプロシージャを使うのは危険だと思います。 ボタンを押すくらいは甘受し、台無しにするようなコードの実行は起きないようにするべきです。 ■ コードを拝見すると、 VLOOKUPを使う → 一番左にKey項目を持ってこないといけない → H列をA列にカット挿入 ということのようですが、 H列をApplication.Matchで検索して、マッチするのは何行目かを調べ、 その何列目と指定して転記するように変更すれば、H列からA列への移動は不要です。 ■ 学習目的もあるので、どうしてもChangeを、ということなら、 TargetのRows.CountやColumns.Countがそれぞれ一定数以上のものという条件をつけて、 最初のデータ貼り付け以外は直ぐに脱出するようにすればいいでしょう。 また、すでに指摘のある Appliaction.EnableEvents をきちんと理解して使うことです。 （xyz） 2024/04/09(火) 11:27:26 ---- tkit様 ご返信ありがとうございます。直接読み込みとおっしゃっておりますが、初心者にはハードルが高すぎるのでは…と思いますが、tryできるものでしょうか。いつもは、system→検索→検索結果→結果のデータの上で右クリック→コピー→エクセルへ貼り付け、、で取得しています。 xyz様、ありがとうございます。ご丁寧に説明してくださいまして本当にありがとうございます。ご指摘いただきました「何度も実行しても破綻しないようなコードに…」というのは本当に難しいと思いました。少しずつ改善していきたいと思います。 VLOOKUPを使う → 一番左にKey項目を持って 〜 という箇所に関しても変更してみます。 ありがとうございます。 （おにぎり花子） 2024/04/09(火) 12:55:30 ---- 話が進んでいるようですが追加で。 ■6 Changeイベントについては、いろいろ言われておなかいっぱいかもしれませんが、皆さん大事なことをコメントされていると思いますので、それぞれちゃんと読み込んでわからなければ、追加で聞くなどして理解を深められるとよいとおもいます。 ■7 さて、「■5」で述べたように、「Worksheet_Changeイベント」でいろいろ気を使わなきゃいけないのは【同じシート】のデータを加工する場合です。 たとえば 0.[B]シートにデータを貼り付けしたら↓を実行 1.[作業用]シートにデータをそっくりコピーする 2.[作業用]シートのH列をA列に挿入する 3.[作業用]シートのA列最終行を調べる=データ件数がわかる 4.[A]シートのC列最終行を調べる 5.[A]シートのC列最終行+1に、[作業用]シートのA列1行目〜最終行までのデータを貼付する 6.[A]シートのE,H,J,K,L,N,P列それぞれに[作業用]シートを参照するVLOOKUP関数を使ったを書き込む 7.[A]シートのE〜P列をコピーして、そのまま値貼り付けする 8.[B]シートをクリアする 9.[作業用]シートをクリアする ↑のような処理手順にすれば、8番目の処理以外は（Bシートの）Changeイベントは関係なくなります。 ■8 また、上記でVLOOKUP関数を使うのはマストな処理なのでしょうか? こちらの勘違いであれば、ごめんなさいですが↓って結局【同じ行】のデータを見ることになってませんか? ws2.Range("H:H").Cut ws2.Range("A:A").Insert v = ws2.Cells(i, "A").Value ws2.Cells(lastRow1 + k, "C").Value = v Worksheets("A").Cells(lastRow1 + k, "C").Value = v Worksheets("A").Cells(lastRow1 + k, "E").Value = Application.VLookup(Worksheets("A").Cells(lastRow1 + k, "C").Value, ws2.Range("A:AG"), 8, False) このあたりは繰り返しになりますが、【行列を踏まえたレイアウトで、元データと希望する結果を説明】いただければアドバイスできることがあるかもしれません。 ※1 一般的には、【抽出】といわれたら、レコード状態になっているものから該当行の（必要な列）のデータをひっっぱって来るんじゃないかとおもいます。 ※2 同じ検索値で項目によって行が変わる（VLOOKUPで検索する必要がある）というのがしっくりこないです。 ■9 上記と関連しますが、↓のような判定をしているのが気になります。 If Worksheets("A").Cells(j, "C").Value = ThisWorkbook.Sheets("B").Cells(i, "A").Value Then 実際にはVLOOKUPは必要なくて、貼り付けたH列にキーコードが在って、そのキーコードが［A］シートに登録されていない場合に、必要な列だけ追加転記したいとかだったりしませんか? （もこな2 ） 2024/04/09(火) 17:25:47 ---- ■10（■9関係） 失礼。↓のように書いてありますね。 ＞Aシートは一覧になっており、フィルタにて未入荷の商品のみを表示しております。そこへBシートに貼り付けたコードと、 ＞Aシートのリストを比較して重複しないデータをBシートからAシートへ転記していくものです。 ＞コードや品名、品目コード、日付、場所、等転記したいデータがlookupResult1〜7に記載している部分です。 踏まえて2点ほど確認 Q1. Bシートに貼り付けるデータは何件くらいで、そのうちAシートに転記するデータは何件くらいでしょうか （1回あたりの処理量で教えてください） Q2. Bシートに項目行を入れるわけにはいかないのでしょうか? （転記したいデータをフィルタオプションで作業用シートに取り出すことを考えています） （もこな2） 2024/04/10(水) 06:19:53 ---- もこな2様、ありがとうございます。皆様から沢山ご指導をいただきましてありがたい一方、初心者はアップアップしながら確認させていただいております。 Bシートへ貼り付ける件数はシステムから取得時にある程度絞って取得しているため、現在のところ多くても20〜30件程度を貼り付けています。 Aシート C列:注文番号 D列:購入先 E列:品名 J列:数量 K列:単位 L列:場所 N列入荷予定日 P列:品目コード ↓ 18○○○ 18○○○ 19〇〇〇 〜 未入荷のみをフィルタにて表示させています。 Aシートは入荷済で検査が終了したものは都度非表示にしています。 Bシート(システムから取得し貼り付けたもの) A列〜特に必要ない項目〜 F列:品目コード G列:品名 H列注文番号 O列:入荷予定日 S列:数量 U列:単位 AG列:場所 〜特に必要ない項目 ↓ 18○○○ 18○○○ 19○○○ 20〇〇〇 初めは、せめて注文番号だけでもと思いAシートのC列とBシートのH列を比較して追加された注文情報だけをAシートのC列最下行に 出来るように、その後注文番号からその行の詳細情報をAシートに転記できるようになりたいと続けています。 ご指導をいただきながら、詳細情報を転記できるように試行錯誤する中で転記できた!と思ってもエラー値が挿入されたためネット検索していたところ、 検索値がA列(左端)にないことが原因では。。。と思いH列をA列に挿入したところエラー値にならず転記されましたので H列→A列に挿入の操作を増やすことに致しました。 そこからもし出来るならBシートへ貼り付けから【抽出の処理】まで指示が出せればいいのでは。。。と考え、今回ご相談させていただきました。 Bシートはまっさらなシートに取得したデータをctrl+Vで貼り付けているだけです。 もし可能でしたら、作成してみたいものが浮かんだ際にゴールにたどり着くためにおすすめの書籍や学習方法がありましたら お教えいただけませんでしょうか。 以上、何卒宜しくお願いいたします。 （おにぎり花子） 2024/04/10(水) 12:01:16 ---- ■11 いまいちわからないのですが、↓みたいな話でよいのですか? 【Bシート】（貼り付け直後） ... _____F____ ___G___ ___H____ .. ____O_____ .. ___S___ .. ___U___ .. ___AG__ 1 品目コード 品名 注文番号 入荷予定日 数量 単位 場所 2 A123456 あああ 1 XXXXX 99 個 xxxx 3 A123456 あああ 2 XXXXX 99 個 xxxx 4 B789012 いいい 3 XXXXX 99 個 xxxx 5 C345678 ううう 4 XXXXX 99 個 xxxx 【Aシート】（処理前） ... _____C____ ____D____ ___E__ .. __J__ __K__ __L__ .. ____N____ .. ____P____ 1 注文番号 購入先 品名 数量 単位 場所 入荷予定日 品目コード 2 1 xxxx あああ 99 個 xxxx XXXXX A123456 3 3 xxxx いいい 99 個 xxxx XXXXX B789012 ↓【Aシート】になかった、注文番号「2」と「4」のデータのみ追加される 【Aシート】（処理後） ... _____C____ ____D____ ___E__ .. __J__ __K__ __L__ .. ____N____ .. ____P____ 1 注文番号 購入先 品名 数量 単位 場所 入荷予定日 品目コード 2 1 xxxx あああ 99 個 xxxx XXXXX A123456 3 3 xxxx いいい 99 個 xxxx XXXXX B789012 4 2 あああ 99 個 xxxx XXXXX A123456 5 4 ううう 99 個 xxxx XXXXX C345678 なんとなくですが【Aシート】は在庫管理表みたいなものであって、単純に無いものだけ追加すればいいって話ではないような気もしますが.... （もこな2 ） 2024/04/10(水) 19:37:23 ---- もこな2様、ありがとうございます。もこな2様のご記載いただきました通り、Bシートにて新たに追加された注文情報を転記できるようにしたいです。 注文を行っている部署と当エクセルを使用している部署が別々の部署で、週に1〜2回注文情報を確認し追加転記している次第です。エクセルに追加しておき、入荷になった際に注文番号や数量等があっているかをチェックし、システム上では入力できない項目等を入力後、フィルタで未入荷だけを表示する作業を繰り返しています。 （おにぎり花子） 2024/04/11(木) 15:16:08 ---- ■12 まだよくわからない部分がありますが、既に述べたように一旦作業用シートに取り出してから必要なデータだけコピペしてはどうでしょうか? 事前準備として 1. Bシートの最終列のとなりにダミー項目を設定（例ではAH列に「-」という項目を設定） 2. 以下のマクロを実行 Sub 事前準備() '本コードは1度だけ実行すればOK With Worksheets.Add(after:=Worksheets("A")) .Name = "作業用" .Range("A1:N1").Value = Split("注文番号,購入先,品名,-,-,-,-,数量,単位,場所,-,入荷予定日,-,品目コード", ",") End With End Sub 3. Bシートのモジュールに以下を記述 Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Long Dim MyRNG As Range With Worksheets("作業用") Sheets("B").Range("F:AH").AdvancedFilter _ Action:=xlFilterCopy, _ CopyToRange:=.Range("A1:N1"), _ Unique:=False For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row If WorksheetFunction.CountIf(Worksheets("A").Range("C:C"), .Cells(i, "A")) = 0 Then If MyRNG Is Nothing Then Set MyRNG = .Cells(i, "A").Resize(, 14) Else Set MyRNG = Union(MyRNG, .Cells(i, "A").Resize(, 14)) End If End If Next i End With If Not MyRNG Is Nothing Then MyRNG.Copy Worksheets("A").Cells(Rows.Count, "C").End(xlUp).Offset(1) End If Application.EnableEvents = False 'イベント発生を無効化 Me.Rows("2:" & Rows.Count).Clear Application.EnableEvents = True 'イベント発生を有効化 End Sub これでBシートに貼り付けしたら、Aシートに登録のないものだけが追加転記されると思います。 （もこな2） 2024/04/12(金) 00:54:41 ---- 書き忘れ。 ・上記は説明用の提示であり完成品プレゼントの意図はありません。 採用する場合は仕組みや理屈が理解できてからにしてください。 ・Bシートの1行目は項目行です。 データを貼付する場合は2行目以降に貼付してください。 付随して、「1.」と「3.」の順番を逆にしないでください。 （既に述べたように、項目行の編集でもchangeイベントが発生してしまいます） （もこな2 ） 2024/04/12(金) 08:56:56 ---- もこな2様、返信遅くなりまして申し訳ございません。初心者のためにお時間を割いて下さいましてほんとうにありがとうございます。ここの所取り組めておりませんでしたので、GWの休暇期間に時間をかけて確認させていただきたいと思います。理解が追いつくか正直不安ですが。。。 （おにぎり花子） 2024/04/26(金) 16:23:11... - http://www.excel.studio-kazu.jp/wiki/kazuwiki/202404/20240408133223.txt - [detail] - similar
PREV
NEXT
Powered by Hyper Estraier 1.4.13, with 97040 documents and 608045 words.
訪問者：