コミュニケーションは、Excel の非常に便利な機能です。 結局、多くの場合、ユーザーは他のファイルからの情報を使用する必要があります。 しかし、状況によっては、善よりも害を及ぼす可能性があります。 たとえば、これらのファイルをメールで送信すると、リンクが機能しません。 今日は、このような問題を回避するために何をすべきかについて詳しく説明します。
Excel のリレーションシップとは
Excel のリレーションシップは、次のような関数と組み合わせて使用されることがよくあります。 VPR別のブックから情報を取得します。 セルだけでなく、データが配置されているブックのアドレスを含む特別なリンクの形をとることができます。 その結果、そのようなリンクは次のようになります。 =VLOOKUP(A2;'[Sales 2018.xlsx]Report'!$A:$F;4;0). または、より簡単に表現するには、アドレスを次の形式で表します。 ='[売上高 2018.xlsx]レポート'!$A1. このタイプの各リンク要素を分析してみましょう。
- 【2018年販売.xlsx】. このフラグメントには、情報を取得するファイルへのリンクが含まれています。 ソースとも呼ばれます。
- 写真. 次の名前を使用しましたが、これは本来あるべき名前ではありません。 このブロックには、情報を検索する必要があるシートの名前が含まれています。
- $A:$F と $A1 – このドキュメントに含まれるデータを含むセルまたは範囲のアドレス。
実際、外部ドキュメントへのリンクを作成するプロセスはリンクと呼ばれます。 別のファイルに含まれるセルのアドレスを登録すると、「データ」タブの内容が変わります。 つまり、「接続の変更」ボタンがアクティブになり、ユーザーはこれを使用して既存の接続を編集できます。
問題の本質
原則として、リンクを使用するために追加の問題は発生しません。 セルが変更される状況が発生した場合でも、すべてのリンクが自動的に更新されます。 ただし、既にブック自体の名前を変更したり、別のアドレスに移動したりすると、Excel は無力になります。 したがって、次のメッセージが生成されます。
ここで、ユーザーには、この状況でどのように行動するかについて XNUMX つの可能なオプションがあります。 [続行] をクリックして変更を更新しないか、[関連付けの変更] ボタンをクリックして手動で更新することができます。 このボタンをクリックすると、リンクを変更できる追加のウィンドウが表示され、現在の正しいファイルの場所とその名前が示されます。
さらに、「データ」タブにある対応するボタンからリンクを編集できます。 ユーザーは、アドレス自体が無効なため、特定のアドレスにある情報に Excel がアクセスできない場合に表示される #LINK エラーによって、接続が切断されていることを確認することもできます。
Excelでリンクを解除する方法
リンクされたファイルの場所を自分で更新できない場合に上記の状況を解決する最も簡単な方法の XNUMX つは、リンク自体を削除することです。 これは、ドキュメントに含まれるリンクが XNUMX つだけの場合に特に簡単です。 これを行うには、次の一連の手順を実行する必要があります。
- 「データ」メニューを開きます。
- 「接続」セクションがあり、そこに「接続の変更」オプションがあります。
- その後、「リンク解除」をクリックします。
この本を他の人に郵送する場合は、事前に郵送することを強くお勧めします。 結局、リンクを削除した後、別のドキュメントに含まれるすべての値が自動的にファイルに読み込まれ、数式で使用され、セル アドレスの代わりに、対応するセルの情報が値に変換されます。 .
すべての本のリンクを解除する方法
ただし、リンクの数が多くなりすぎると、手動で削除すると時間がかかる場合があります。 この問題を一気に解決するには、特別なマクロを使用できます。 VBA-Excel アドオンに含まれています。 それをアクティブにして、同じ名前のタブに移動する必要があります。 「すべてのリンクを解除」ボタンをクリックする必要がある「リンク」セクションがあります。
VBAコード
このアドオンを有効にできない場合は、自分でマクロを作成できます。 これを行うには、Alt + F11 キーを押して Visual Basic エディターを開き、コード入力フィールドに次の行を書き込みます。
サブUnlinkWorkBooks()
薄暗い WbLinks
暗くて長い
Select Case MsgBox("他の書籍への参照はすべてこのファイルから削除され、他の書籍を参照する数式は値に置き換えられます。" & vbCrLf & "続行しますか?", 36, "リンクを解除しますか?" )
ケース 7' いいえ
サブを終了
選択して終了
WbLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
IsEmpty(WbLinks)でない場合は、
i = 1 の場合、UBound(WbLinks) へ
ActiveWorkbook.BreakLink 名前:=WbLinks(i)、種類:=xlLinkTypeExcelLinks
Next
ほかに
MsgBox 「このファイルには他の書籍へのリンクはありません。」、64、「他の書籍へのリンク」
終了する場合
End Subの
選択範囲のみ同点解消する方法
時々、リンクの数が非常に多くなり、ユーザーはそのうちの XNUMX つを削除した後、余分なものがあればすべてを元に戻すことができないのではないかと心配しています。 しかし、これは簡単に回避できる問題です。 これを行うには、リンクを削除する範囲を選択してから削除する必要があります。 これを行うには、次の一連のアクションを実行する必要があります。
- 変更する必要があるデータセットを選択します。
- VBA-Excel アドオンをインストールし、適切なタブに移動します。
- 次に、「リンク」メニューを見つけて、「選択した範囲でリンクを解除する」ボタンをクリックします。
その後、選択したセルのセット内のすべてのリンクが削除されます。
関係が壊れていない場合の対処方法
上記のすべては良さそうに聞こえますが、実際には常にいくつかのニュアンスがあります。 たとえば、タイが壊れていない状況があるかもしれません。 この場合、リンクを自動的に更新できないことを示すダイアログ ボックスが引き続き表示されます。 この状況で何をすべきか？
- まず、名前付き範囲に情報が含まれているかどうかを確認する必要があります。 これを行うには、キーの組み合わせ Ctrl + F3 を押すか、[数式] タブの [名前マネージャー] を開きます。 ファイル名が完全な場合は、編集するか完全に削除するだけです。 名前付き範囲を削除する前に、間違った手順を実行した場合に元のバージョンに戻れるように、ファイルを別の場所にコピーする必要があります。
- 名前を削除しても問題が解決しない場合は、条件付き書式を確認できます。 別のテーブルのセルは、条件付き書式ルールで参照できます。 これを行うには、「ホーム」タブで対応する項目を見つけて、「ファイル管理」ボタンをクリックします。
通常、Excel では、条件付き書式で他のブックのアドレスを指定することはできませんが、別のファイルへの参照で名前付き範囲を参照する場合は指定できます。 通常、リンクが削除されても、リンクは残ります。 リンクは実際には機能していないため、このようなリンクを削除しても問題はありません。 したがって、削除しても何も悪いことはありません。
「データチェック」機能で不要なリンクがないかチェックすることもできます。 「リスト」タイプのデータ検証が使用されている場合、通常、リンクは残ります。 しかし、細胞がたくさんある場合はどうすればよいでしょうか? それぞれを順番にチェックする必要がありますか？ もちろん違います。 結局のところ、それには非常に長い時間がかかります。 したがって、特別なコードを使用して大幅に節約する必要があります。
Option Explicit
'——————————————————————————————
' 作者: The_Prist(Shcherbakov Dmitry)
複雑さを問わず、MS Office 用のアプリケーションの専門的な開発
' MS Excel のトレーニングを実施
' https://www.excel-vba.ru
' [メール保護]
'WebMoney—R298726502453; Yandex.Money — 41001332272872
' 目的：
'——————————————————————————————
サブ FindErrLink()
'データを調べる必要があります - ソース ファイルへのリンクの変更
'ここでキーワードを小文字で入力します (ファイル名の一部)
'アスタリスクは任意の数の文字を置き換えるだけなので、正確な名前について心配する必要はありません
Const sToFndLink$ = “*売上高 2018*”
範囲としての Dim rr、範囲としての rc、範囲としての rres、s$
'データ検証を使用してすべてのセルを定義します
エラーで次の再開
rr = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation) を設定します。
rr が何もない場合
MsgBox "アクティブ シートにデータ検証のあるセルがありません", vbInformation, "www.excel-vba.ru"
サブを終了
終了する場合
エラー時GoTo0
'各セルのリンクをチェック
rc の rr ごとに
念のため、エラーをスキップします – これも発生する可能性があります
しかし、私たちのつながりはそれらなしでなければならず、それらは間違いなく見つかります
s = «»
エラーで次の再開
s = rc.Validation.Formula1
エラー時GoTo0
'found – すべてを別の範囲に収集します
LCase(s) が sToFndLink のような場合
rres が何もない場合
設定 rres = rc
ほかに
rres = Union(rc, rres) を設定します。
終了する場合
終了する場合
Next
'接続がある場合は、そのようなデータ チェックですべてのセルを選択します
そうでない場合は何もありません
rres.Select
「rres.Interior.Color = vbRed」色で強調したい場合
終了する場合
End Subの
マクロ エディタで標準モジュールを作成し、そこにこのテキストを挿入する必要があります。 その後、キーの組み合わせ Alt + F8 を使用してマクロ ウィンドウを呼び出し、マクロを選択して [実行] ボタンをクリックします。 このコードを使用する際は、次の点に注意してください。
- 関連性がなくなったリンクを検索する前に、そのリンクがどのように作成されているかをまず判断する必要があります。 これを行うには、「データ」メニューに移動し、そこにある「リンクの変更」項目を見つけます。 その後、ファイル名を確認し、引用符で囲んで指定する必要があります。 たとえば、次のようにします。 Const sToFndLink$ = “*売上高 2018*”
- 名前を完全にではなく、不要な文字をアスタリスクに置き換えるだけで書くことは可能です。 引用符で囲み、ファイル名を小文字で書きます。 この場合、Excel は末尾にそのような文字列を含むすべてのファイルを検索します。
- このコードは、現在アクティブなシート内のリンクのみを確認できます。
- このマクロを使用すると、見つかったセルのみを選択できます。 すべてを手動で削除する必要があります。 すべてを再確認できるので、これはプラスです。
- セルを特別な色で強調表示することもできます。 これを行うには、この行の前にあるアポストロフィを削除します。 rres.Interior.Color = vbRed
通常、上記の手順で説明した手順を完了すると、不要な接続はなくなります。 ただし、ドキュメントにそれらの一部があり、何らかの理由でそれらを削除できない場合 (典型的な例は、シート内のデータのセキュリティです)、別のアクション シーケンスを使用できます。 この命令は、バージョン 2007 以降でのみ有効です。
- ドキュメントのバックアップ コピーを作成します。
- アーカイバーを使用してこのドキュメントを開きます。 ZIP 形式をサポートするものならどれでも使用できますが、Windows に組み込まれている WinRar も機能します。
- 表示されるアーカイブで、xl フォルダーを見つけて、externalLinks を開く必要があります。
- このフォルダーにはすべての外部リンクが含まれており、それぞれが externalLink1.xml 形式のファイルに対応しています。 それらはすべて番号付けされているだけなので、ユーザーはこれがどのような接続であるかを理解する機会がありません。 接続の種類を理解するには、_rels フォルダーを開いて、そこを確認する必要があります。
- その後、externalLinkX.xml.rels ファイルの内容に基づいて、すべてのリンクまたは特定のリンクを削除します。
- その後、Excel を使用してファイルを開きます。 「本の内容に一部誤りがあります」などのエラー情報が表示されます。 同意します。 その後、別のダイアログが表示されます。 閉じます。
その後、すべてのリンクを削除する必要があります。