XNUMX つのテーブル (たとえば、価格表の古いバージョンと新しいバージョン) があり、これらを比較して違いをすばやく見つける必要があります。
新しい価格表に何かが追加されたこと (ナツメヤシ、ニンニクなど)、何かが消えたこと (ブラックベリー、ラズベリーなど)、一部の商品 (イチジク、メロンなど) の価格が変更されたことはすぐにわかります。 これらすべての変更をすばやく見つけて表示する必要があります。
Excel のどのタスクでも、ほとんどの場合、複数のソリューションがあります (通常は 4 ～ 5)。 この問題では、さまざまなアプローチを使用できます。
- function VPR （VLOOKUP） — 古い価格表の新しい価格表から製品名を探し、新しい価格の隣に古い価格を表示して、違いをキャッチします。
- XNUMX つのリストを XNUMX つにマージし、それに基づいてピボット テーブルを作成すると、違いがはっきりとわかります
- Excel 用 Power Query アドインを使用する
それらをすべて順番に見てみましょう。
方法 1. VLOOKUP 関数を使用してテーブルを比較する
この素晴らしい機能にまったく慣れていない場合は、まずここを見て、ビデオチュートリアルを読んだり見たりしてください.数年の命を救うことができます.
通常、この関数は、いくつかの共通パラメーターを照合することによって、あるテーブルから別のテーブルにデータをプルするために使用されます。 この場合、これを使用して古い価格を新しい価格にプッシュします。
#N/A エラーが発生した製品は、古いリストにない、つまり追加された製品です。 価格の変化もはっきりと見えます。
メリット この方法は、彼らが言うように、シンプルで明確な「ジャンルの古典」です。 どのバージョンの Excel でも動作します。
デメリット もあります。 新しい価格表に追加された製品を検索するには、同じ手順を逆方向に実行する必要があります。つまり、VLOOKUP を使用して新しい価格を古い価格に引き上げます。 テーブルのサイズが明日変更された場合は、数式を調整する必要があります。 まあ、そして非常に大きなテーブル (> 100 行) では、このすべての幸せはかなり遅くなります。
方法 2: ピボットを使用してテーブルを比較する
テーブルを上下にコピーして、価格表の名前の列を追加して、後でどのリストからどの行を理解できるようにします。
次に、作成したテーブルに基づいて、次の手順で集計を作成します。 挿入 – ピボットテーブル (挿入 — ピボット テーブル). フィールドを投げよう プロダクト ライン、フィールドのエリアへ 価格 列エリアとフィールドへ ЦENA 範囲に：
ご覧のとおり、ピボット テーブルは古い価格表と新しい価格表からすべての製品の一般的なリストを自動的に生成し (繰り返しはありません!)、製品をアルファベット順に並べ替えます。 追加された製品 (古い価格はありません)、削除された製品 (新しい価格はありません)、および価格の変更がある場合は明確に確認できます。
このようなテーブルの総計は意味がなく、タブで無効にすることができます コンストラクタ – 総計 – 行と列を無効にする (設計 — 総計).
価格が変更された場合 (ただし、商品の数量は変更されません!)、作成された概要を右クリックして更新するだけで十分です – Refresh.
メリット: このアプローチは、VLOOKUP よりも大きなテーブルで桁違いに高速です。
デメリット: データを相互に手動でコピーし、価格表の名前を含む列を追加する必要があります。 テーブルのサイズが変わると、すべてを最初からやり直す必要があります。
方法 3: Power Query を使用してテーブルを比較する
Power Query は、Microsoft Excel 用の無料のアドインであり、ほぼすべてのソースから Excel にデータを読み込んで、このデータを任意の方法で変換できます。 Excel 2016 では、このアドインは既定で既にタブに組み込まれています。 且つ （データ）、 Excel 2010-2013 の場合は、Microsoft の Web サイトから個別にダウンロードしてインストールする必要があります。新しいタブを取得してください。 パワークエリ.
価格表を Power Query に読み込む前に、まずそれらをスマート テーブルに変換する必要があります。 これを行うには、データのある範囲を選択し、キーボードの組み合わせを押します Ctrlキー+T またはリボンのタブを選択します ホーム – 表としてフォーマットする (ホーム — テーブルとしてフォーマット). 作成されたテーブルの名前は、タブで修正できます コンストラクタ （基準はお任せします テーブル1 и テーブル2、デフォルトで取得されます)。
ボタンを使用して Power Query に古い価格を読み込む テーブル/範囲から (テーブル/範囲から) タブから 且つ （日にち） またはタブから パワークエリ （エクセルのバージョンにもよります。） 読み込み後、次のコマンドで Power Query から Excel に戻ります。 閉じてロード – 閉じてロード… (クローズ & ロード — クローズ & ロード先…):
…そして、表示されるウィンドウで次を選択します 接続を作成するだけ (接続のみ).
新しい価格表で同じことを繰り返します。
次に、前の XNUMX つのデータを組み合わせて比較する XNUMX 番目のクエリを作成しましょう。 これを行うには、タブの Excel で選択します。 データ – データの取得 – 結合 リクエスト – 結合 (データ — データの取得 — クエリのマージ — マージ) またはボタンを押します 組み合わせる （マージ） タブ パワークエリ.
結合ウィンドウで、ドロップダウン リストからテーブルを選択し、商品の名前が含まれる列を選択し、下部で結合方法を設定します。 完全な外部 (フルアウター):
をクリックした後 OK XNUMX 列のテーブルが表示されます。XNUMX 番目の列では、ヘッダーの二重矢印を使用してネストされたテーブルの内容を展開する必要があります。
その結果、両方のテーブルからデータがマージされます。
もちろん、より理解しやすいものをダブルクリックして、ヘッダーの列名の名前を変更することをお勧めします。
そして今、最も興味深い。 タブに移動 列を追加 (列を追加) そしてボタンをクリックしてください 条件付き列 (条件付き列). 開いたウィンドウで、いくつかのテスト条件とそれに対応する出力値を入力します。
クリックする必要があります OK 同じボタンを使用して、結果のレポートを Excel にアップロードします。 閉じてダウンロード （クローズ＆ロード） タブ ホーム （ホーム）:
美しさ。
さらに、将来、価格表に変更が発生した場合 (行の追加または削除、価格の変更など)、キーボード ショートカットを使用してリクエストを更新するだけで十分です。 Ctrlキー+他の+F5 またはボタンで すべて更新 (すべて更新) タブ 且つ （日にち）.
メリット: おそらく最も美しく便利な方法です。 大きなテーブルでスマートに動作します。 テーブルのサイズを変更するときに手動で編集する必要はありません。
デメリット: Power Query アドイン (Excel 2010 ～ 2013) または Excel 2016 をインストールする必要があります。 ソース データの列名は変更しないでください。変更しないと、「Column such and such was not found!」というエラーが発生します。 クエリを更新しようとしたとき。
- Power Query を使用して特定のフォルダー内のすべての Excel ファイルからデータを収集する方法
- Excel で XNUMX つのリスト間の一致を見つける方法
- 重複のない XNUMX つのリストのマージ