Excel資料對比的思路與方法
資料對比在工作中是經常遇到:
客戶發來對賬清單,
供應商發來對帳單,與入庫清單做對比
諸如此類的場景有很多
我們簡單模擬一下,現實中可能會有很多的專案,諸如:單號、合同號號、客戶號等等。
資料的特點:物料號或物料名稱有可能重複出現
通常的做法是:
一、整理資料,確保資料整潔一致,
二、分別做透視表,關鍵資料列的唯一性
三、VLOOKUP將兩個表的資料做對比
今天我們來整理下這種思路
做資料對比,可以分為合併法與連立法
合併法:用Power Query或者VBA工具將兩個表的資料合併到一起,增加一清單的標誌,然後使用樞紐分析表來分析資料。
聯立法:先將兩個表關鍵列的資料複製到一列,刪除重複項保留唯一值作為輔助表,用Power Pivot建立三個表的關係,
合併法演示:
一、用Power Query合併資料
具體的合併過程,大家自己研究,如果Excel版本低可以用VBA進行資料合併
二、透視表分析:
差異一列是用公式來實現的:
聯立法演示:
一、建立輔助表
二、Power Pivot建立資料模型
三、Power Pivot建立樞紐分析表
四、轉換為OLAP公式,編寫差異公式
這兩種方法,基本都是通過點擊滑鼠來完成的
要編輯的公式也只是簡單的減法公式
Excel資料處理的方式在發生轉變
就是從儲存格時代向表的轉變