インターネットから Excel にデータをインポートし、その後の自動更新を行う方法を繰り返し分析してきました。 特に：
- 以前のバージョンの Excel 2007 ～ 2013 では、これは直接 Web 要求で行うことができました。
- 2010 年以降、これは Power Query アドインを使用して非常に便利に行うことができます。
最新バージョンの Microsoft Excel のこれらのメソッドに、組み込み関数を使用してインターネットから XML 形式でデータをインポートする別のメソッドを追加できるようになりました。
XML (eXtensible Markup Language = Extensible Markup Language) は、あらゆる種類のデータを記述するために設計されたユニバーサル言語です。 実際、これはプレーン テキストですが、データ構造をマークアップするために特別なタグが追加されています。 多くのサイトでは、誰でもダウンロードできるように、データのストリームを XML 形式で無料で提供しています。 特に、我が国の中央銀行 (www.cbr.ru) の Web サイトでは、同様の技術を利用して、さまざまな通貨の為替レートに関するデータが提供されています。 モスクワ証券取引所の Web サイト (www.moex.com) から、同じ方法で株式、債券、およびその他の多くの有用な情報の相場をダウンロードできます。
バージョン 2013 以降、Excel には、XML データをインターネットからワークシートのセルに直接読み込むための XNUMX つの関数があります。 ウェブサービス （ウェブサービス） и フィルター.XML (フィルターXML). それらはペアで機能します-最初に機能 ウェブサービス 目的のサイトへのリクエストを実行し、そのレスポンスを XML 形式で返し、関数を使用して フィルター.XML この回答をコンポーネントに「解析」し、そこから必要なデータを抽出します。
古典的な例を使用してこれらの関数の操作を見てみましょう - 国の中央銀行のウェブサイトから特定の日付間隔に必要な通貨の為替レートをインポートします。 次の構造をブランクとして使用します。
ここに：
- 黄色のセルには、関心のある期間の開始日と終了日が含まれています。
- 青いものには、コマンドを使用した通貨のドロップダウン リストがあります。 データ – 検証 – リスト (データ — 検証 — リスト).
- 緑色のセルでは、関数を使用してクエリ文字列を作成し、サーバーの応答を取得します。
- 右側の表は通貨コードへの参照です (後で必要になります)。
行こう！
ステップ 1. クエリ文字列の作成
サイトから必要な情報を取得するには、正しく質問する必要があります。 www.cbr.ru にアクセスし、メイン ページのフッターにあるリンクを開きます。 技術リソース'- XML を使用してデータを取得する (http://cbr.ru/development/SXML/)。 少し下にスクロールすると、2 番目の例 (例 XNUMX) に必要なものが表示されます。特定の日付間隔の為替レートを取得します。
例からわかるように、クエリ文字列には開始日 (date_req1) と語尾 (date_req2) と通貨コード (VAL_NM_RQ)、取得したいレート。 以下の表に主要な通貨コードを示します。
通貨 | Code | | 通貨 | Code |
| 豪ドル | R01010 | リトアニアリタス | R01435 | |
オーストリアシリング | R01015 | リトアニアクーポン | R01435 | |
アゼルバイジャンのマナト | R01020 | モルドバレイ | R01500 | |
パウンド | R01035 | РќРμРјРμС † РєР ° СЏ РјР ° СЂРєР ° | R01510 | |
アンゴラのニュークワンザ | R01040 | オランダのギルダー | R01523 | |
アルメニア·ドラム | R01060 | ノルウェークローネ | R01535 | |
ベラルーシルーブル | R01090 | ポーランドズロチ | R01565 | |
ベルギーフラン | R01095 | ポルトガルのエスクード | R01570 | |
ブルガリアのライオン | R01100 | ルーマニアレイ | R01585 | |
ブラジルレアル | R01115 | シンガポールドル | R01625 | |
ハンガリーフォリント | R01135 | スリナム・ドル | R01665 | |
香港ドル | R01200 | タジク ソモニ | R01670 | |
ギリシャのドラクマ | R01205 | タジク ルーブル | R01670 | |
デンマーククローネ | R01215 | トルコリラ | R01700 | |
米ドル | R01235 | トルクメンマナット | R01710 | |
ユーロ | R01239 | 新トルクメンマナト | R01710 | |
インドルピー | R01270 | ウズベキスタン・スム | R01717 | |
アイルランドポンド | R01305 | ウクライナのグリブナ | R01720 | |
アイスランドクローネ | R01310 | ウクライナのカルボバネット | R01720 | |
スペインのペセタ | R01315 | フィンランドのマーク | R01740 | |
イタリアリラ | R01325 | フランスフラン | R01750 | |
カザフスタン テンゲ | R01335 | チェココルナ | R01760 | |
カナダドル | R01350 | スウェーデンクローナ | R01770 | |
キルギスソム | R01370 | スイスフランク | R01775 | |
中国元 | R01375 | エストニアクローン | R01795 | |
クウェートディナール | R01390 | ユーゴスラビア新ディナール | R01804 | |
ラトビアラッツ | R01405 | 南アフリカランド | R01810 | |
レバノンポンド | R01420 | 大韓民国ウォン | R01815 | |
日本円 | R01820 |
通貨コードの完全なガイドは、中央銀行の Web サイトでも入手できます。http://cbr.ru/scripts/XML_val.asp?d=0 を参照してください。
次に、シートのセルに次のクエリ文字列を作成します。
- 結合するためのテキスト連結演算子 (&)。
- 特徴 VPR （VLOOKUP）ディレクトリで必要な通貨のコードを見つける。
- 特徴 TEXT （テキスト）これは、指定されたパターン日月年に従って日付をスラッシュで変換します。
="http://cbr.ru/scripts/XML_dynamic.asp?date_req1="&ТЕКСТ(B2;"ДД/ММ/ГГГГ")& "&date_req2="&ТЕКСТ(B3;"ДД/ММ/ГГГГ")&"&VAL_NM_RQ="&ВПР(B4;M:N;2;0)
ステップ 2. リクエストを実行する
次に、関数を使用します ウェブサービス （ウェブサービス） 生成されたクエリ文字列を唯一の引数として使用します。 答えは XML コードの長い行になります (全体を表示する場合は、ワード ラップをオンにしてセル サイズを大きくすることをお勧めします)。
ステップ 3. 回答の解析
応答データの構造を理解しやすくするには、オンライン XML パーサー (たとえば、http://xpather.com/ または https://jsonformatter.org/xml-parser) のいずれかを使用することをお勧めします。 XMLコードを視覚的にフォーマットし、インデントを追加し、構文を色で強調表示できます. その後、すべてがより明確になります。
これで、コースの値がタグによってフレーム化されていることがはっきりとわかります
それらを抽出するには、シートの空のセルを 10 個 (余白がある場合はそれ以上) の列を選択し (XNUMX 日間の日付間隔が設定されているため)、数式バーに関数を入力します。 フィルター.XML （フィルターXML）:
ここで、最初の引数はサーバー応答 (B8) を含むセルへのリンクであり、XNUMX 番目の引数は XPath のクエリ文字列です。XPath は、必要な XML コード フラグメントにアクセスしてそれらを抽出するために使用できる特別な言語です。 XPath 言語の詳細については、たとえばこちらを参照してください。
数式を入力した後は、 を押さないでください。 入力します、およびキーボード ショートカット Ctrlキー+シフト+入力しますつまり、配列数式として入力します (中括弧は自動的に追加されます)。 Excel で動的配列をサポートする最新バージョンの Office 365 を使用している場合は、単純な 入力します、事前に空のセルを選択する必要はありません。関数自体は、必要な数のセルを取得します。
日付を抽出するには、同じことを行います。隣接する列でいくつかの空のセルを選択し、同じ関数を使用しますが、別の XPath クエリを使用して、Record タグから Date 属性のすべての値を取得します。
=FILTER.XML(B8;”//レコード/@日付”)
今後、元のセル B2 と B3 の日付を変更するか、セル B3 のドロップダウン リストで別の通貨を選択すると、クエリは自動的に更新され、中央銀行のサーバーを参照して新しいデータが取得されます。 手動で更新を強制するには、さらにキーボード ショートカットを使用できます。 Ctrlキー+他の+F9.
- Power Query を使用してビットコイン レートを Excel にインポートする
- 以前のバージョンの Excel でインターネットから為替レートをインポートする