Note: The other languages of the website are Google-translated. Back to English

ExcelでVlookupを使用するときに、ルックアップセルのソースフォーマットをコピーするにはどうすればよいですか?

以前の記事では、Excelでvlookup値を指定するときに背景色を維持する方法について説明しました。 この記事では、ExcelでVlookupを実行するときに、結果のセルのすべてのセル形式をコピーする方法を紹介します。 次のようにしてください。

ユーザー定義関数を使用してExcelでVlookupを使用する場合のソースフォーマットのコピー


ユーザー定義関数を使用してExcelでVlookupを使用する場合のソースフォーマットのコピー

以下のスクリーンショットのようなテーブルがあるとします。 次に、指定された値(列E)が列Aにあるかどうかを確認し、対応する値を列Cにフォーマットして返す必要があります。これを実現するには、次のようにしてください。

1.ワークシートにvlookupする値が含まれている場合は、シートタブを右クリックして、 コードを表示 コンテキストメニューから。 スクリーンショットを参照してください:

2.オープニングで アプリケーション向け Microsoft Visual Basic ウィンドウの場合は、以下のVBAコードをコードウィンドウにコピーしてください。

VBAコード1:Vlookupとフォーマット付きの戻り値

Sub Worksheet_Change(ByVal Target As Range)
'Update by Extendoffice 20211203
    Dim I As Long
    Dim xKeys As Long
    Dim xDicStr As String
    On Error Resume Next
    Application.ScreenUpdating = False
    Application.CutCopyMode = False
    xKeys = UBound(xDic.Keys)
    If xKeys >= 0 Then
        For I = 0 To UBound(xDic.Keys)
            xDicStr = xDic.Items(I)
            If xDicStr <> "" Then
                Set xRg = Application.Range(xDicStr)
                xRg.Copy
                Range(xDic.Keys(I)).PasteSpecial xlPasteFormats
            Else
                Range(xDic.Keys(I)).Interior.Color = xlNone
            End If
        Next
        Set xDic = Nothing
    End If
    Application.ScreenUpdating = True
    Application.CutCopyMode = True
End Sub

3。 次に、をクリックします インセット > モジュール、および以下のVBAコード2をモジュールウィンドウにコピーします。

VBAコード2:Vlookupとフォーマット付きの戻り値

Public xDic As New Dictionary
'Update by Extendoffice 20211203
Function LookupKeepFormat(ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long)
    Dim xFindCell As Range
    On Error Resume Next
    Application.ScreenUpdating = False
    Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole)
    If xFindCell Is Nothing Then
        LookupKeepFormat = " "
        xDic.Add Application.Caller.Address, " "
    Else
        LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value
        xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address(External:=True)
    End If
    Application.ScreenUpdating = True
End Function

4。 クリック 工具 > リファレンス。 次に、 Microsoftスクリプトランタイム 内箱 参照– VBAProject ダイアログボックス。 スクリーンショットを参照してください:

5。 プレス 他の + Q 終了するためのキー アプリケーション向け Microsoft Visual Basic 窓。

6.ルックアップ値に隣接する空白のセルを選択し、数式を入力します =LookupKeepFormat(E2,$A$1:$C$8,3) 数式バー、を押して 入力します キー。

注意:式では、 E2 検索する値が含まれています。 $ A $ 1:$ C $ 8 はテーブルの範囲であり、数値は 3 返される対応する値がテーブルのXNUMX番目の列にあることを意味します。 必要に応じて変更してください。

7.最初の結果セルを選択し続け、次にフィルハンドルを下にドラッグして、以下のスクリーンショットに示すように、すべての結果とその書式を取得します。


関連記事:


最高のオフィス生産性ツール

Kutools for Excelはほとんどの問題を解決し、生産性を80%向上させます

  • 再利用: すばやく挿入 複雑な数式、チャート および以前に使用したものすべて。 セルを暗号化する パスワード付き。 メーリングリストを作成する そしてメールを送る...
  • スーパーフォーミュラバー (複数行のテキストと数式を簡単に編集できます); 読書レイアウト (多数のセルを簡単に読み取って編集する); フィルター範囲に貼り付け...
  • セル/行/列をマージする データを失うことなく; 分割セルコンテンツ; 重複する行/列を組み合わせる...重複セルを防止します。 範囲を比較する...
  • [複製]または[一意]を選択します 行; 空白行を選択 (すべてのセルは空です); スーパーファインドとファジーファインド 多くのワークブックで; ランダム選択...
  • 正確なコピー 数式参照を変更せずに複数のセル。 参照の自動作成 複数のシートに; 箇条書きを挿入、チェックボックスなど...
  • テキストを抽出、テキストの追加、位置による削除、 スペースを削除する; ページング小計の作成と印刷。 セルの内容とコメントを変換する...
  • スーパーフィルター (フィルタースキームを保存して他のシートに適用します); 高度な並べ替え 月/週/日、頻度など。 特殊フィルター 太字、斜体...
  • ワークブックとワークシートを組み合わせる; キー列に基づいてテーブルをマージします。 データを複数のシートに分割; xls、xlsx、PDFをバッチ変換...
  • 300 以上の強力な機能. Office / Excel 2007-2021 および 365 をサポートします。すべての言語をサポートします。 企業や組織に簡単に導入できます。 フル機能の 30 日間無料トライアル。 60日間の返金保証。
kteタブ201905

Officeタブは、タブ付きのインターフェイスをOfficeにもたらし、作​​業をはるかに簡単にします

  • Word、Excel、PowerPointでタブ付きの編集と読み取りを有効にする、パブリッシャー、アクセス、Visioおよびプロジェクト。
  • 新しいウィンドウではなく、同じウィンドウの新しいタブで複数のドキュメントを開いて作成します。
  • 生産性を 50% 向上させ、毎日何百回もマウス クリックを減らすことができます!
officetab下部
コメントを並べ替える
コメント (42)
まだ評価はありません。 最初に評価してください!
このコメントは、サイトのモデレーターによって最小化されました
それは私にコンパイルエラー、構文エラーを与えます

助けてください
このコメントは、サイトのモデレーターによって最小化されました
グッド·デイ、
コードは記事で更新されています。 コメントありがとうございます。
このコメントは、サイトのモデレーターによって最小化されました
コンパイラエラーも発生しました。
次の変数を実際の "" で変更すると修正されます。 いいえ ';' 途中で。
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、
間違いをお詫びします。コードは記事内で更新されています。
間違い " " は XNUMX つの引用符 " " にする必要があります。 コメントありがとうございます。
このコメントは、サイトのモデレーターによって最小化されました
同じエラーが発生しました。

「;」なしで、「」を実際の「」に変更する必要があります。 以下に示すように
LookupKeepFormat = " "
xDic.Add Application.Caller.Address, " "

LookupKeepFormat = ""
xDic.Add Application.Caller.Address ""
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、
間違いをお詫びします。コードは記事内で更新されています。 共有してくれてありがとう。
このコメントは、サイトのモデレーターによって最小化されました
これは素晴らしいです、ありがとう! 唯一の問題は、同じシートを検索している場合は正常に機能することがわかりましたが、別のシートでソース データを検索しようとすると機能しないことです。 努力し続けます
このコメントは、サイトのモデレーターによって最小化されました
ジュリア、この行を修正してください:
関数 LookupKeepFormat で:
xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" & LookupRng.Parent.Name

サブ Worksheet_Change で:
Sheets(Split(xDic.Items(I), "|")(1)).Range(Split(xDic.Items(I), "|")(0)).Copy
このコメントは、サイトのモデレーターによって最小化されました
ねえ、ヒューゴ


私はジュリアと同じ問題を抱えています。 他のシートでは機能しません。 関数全体とサブワークシートのコードを書くのを手伝ってもらえますか? xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address & "|" を置換/挿入する場所がわかりません& LookupRng.Parent.Nam and Sheets(Split(xDic.Items(I), "|")(1)).Range(Split(xDic.Items(I), "|")(0)).Copy


見返りに感謝
このコメントは、サイトのモデレーターによって最小化されました
フォローアップのヒューゴに大感謝!
残念ながら、Vi と同様に、私はあまりにも初心者なので、提案されたコード修正をどこに挿入すればよいかわかりません...

改めまして、素晴らしい一日をありがとう:)
このコメントは、サイトのモデレーターによって最小化されました
こんにちは


コードを使用しようとしましたが、添付の写真にエラーが表示されます。 どんな支援も大歓迎です。
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、
間違いをお詫びします。コードは記事内で更新されています。 コメントありがとうございます。
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、

エラーは発生せず、ルックアップは実行されますが、ルックアップ値が別のワークシートにあるため (より可能性の高いシナリオ)、書式設定がプルされません。 そのために作成できるコードの微調整はありますか? (私はコーディングの初心者なので、どこに変更を加える必要があるかを具体的に説明してください) ありがとうございます! この機能をスプレッドシートの XNUMX つに追加できることに興奮しています!!
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、この質問で運が良ければ、シート間で書式設定を検索するにはどうすればよいですか?
このコメントは、サイトのモデレーターによって最小化されました
また、微調整を求めています。
このコメントは、サイトのモデレーターによって最小化されました
また、「If」ステートメントの一部として式を追加すると(以下を参照)、LOLが必要な場合でもセルをフォーマットします(または、少なくともそう思われます。XNUMXつのセルで、テキストが影付きになり、上部の境界線が太字になりましたセル; 別のセル、中央揃えのテキスト)


=IF($F19 = "", "",LookupKeepFormat(F19,'Item #s'!$A$1:$M$1226,2))
このコメントは、サイトのモデレーターによって最小化されました
これと、色の背景だけを引っ張って同じエラーが発生するものを試しました。 コンパイル エラー: あいまいな名前が検出されました。 [OK] をクリックすると、xDic が強調表示されます。 助言がありますか? 私はこれらすべてに精通しているわけではないので、助けて/説明してください:)事前に感謝します
このコメントは、サイトのモデレーターによって最小化されました
こんにちは。
手順 4 で説明したように、Microsoft Script Runtime オプションを有効にすることを忘れないでください。
このコメントは、サイトのモデレーターによって最小化されました
こんにちは。 空白のスプレッドシートを作成し、Excel 2013 で例を複製しましたが、コンパイル エラーが発生し続けます: 構文エラーと Dim I As Long が強調表示されます。 足りないものはありますか? これを機能させたいと思います。 ありがとうございました。
このコメントは、サイトのモデレーターによって最小化されました
こんにちはローラ、
手順 4 で説明したように、Microsoft Script Runtime オプションを有効にすることを忘れないでください。
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、私は Excel 2010 で上記のコードを使用してきましたが、これまで問題はありませんでした。 しかし、最近 Office 2016 にアップグレードしたところ、複数の行に入力しようとするたびにコードが Excel をクラッシュさせてしまいました。 残念ながら、「Microsoft Excel が動作を停止しました」以外のエラーは表示されません。 以前にこの問題に遭遇したことがあるかどうか、また 2016 年に問題を解決するために何かする必要があるかどうかを知りたいと思っていました。ありがとうございます。
このコメントは、サイトのモデレーターによって最小化されました
こんにちは。
コードは私の Excel 2016 でうまく動作します。問題を解決するためにコードをアップグレードしようとしています。 コメントありがとうございます。
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、コードをありがとう。 エラー メッセージは表示されませんが、式は通常の vlookup と同じようにしか機能しません。 助けていただけますか? 御時間ありがとうございます。
このコメントは、サイトのモデレーターによって最小化されました
こんにちは

私はまったく同じ問題を抱えています。解決方法はわかりましたか?

ありがとうございます!
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、「コンパイル エラー: あいまいな名前が検出されました: xDic」というエラーが表示されました
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、「コンパイル エラー: あいまいな名前が検出されました: xDic」というエラーが表示されました
このコメントは、サイトのモデレーターによって最小化されました
こんにちは、私は VBA を初めて使用し、スプレッドシートでこのコードを使用しようとしましたが、ルックアップを使用すると、[Rec2] タブのテキスト書式設定が [Rec] タブに反映されません。 どんな助けでも大歓迎です。 ありがとうパット
このコメントは、サイトのモデレーターによって最小化されました
ファイルと写真はこちら
このコメントは、サイトのモデレーターによって最小化されました
同じ Ambiguous name エラーが発生します - 誰かがそれを解決できましたか?
このコメントは、サイトのモデレーターによって最小化されました
同じ Ambiguous name エラーが発生します - 誰かがそれを解決できましたか?
ここにはまだコメントが投稿されていません
もっと読む
あなたのコメントを残す
ゲストとして投稿
×
この投稿を評価:
0   文字
推奨される場所

フォローする

著作権©の2009 - WWW。extendoffice.com。 | | 全著作権所有。 搭載 ExtendOffice。 | サイトマップ
MicrosoftおよびOfficeのロゴは、米国MicrosoftCorporationの米国およびその他の国における商標または登録商標です。
SectigoSSLで保護