如何迅速複製公式？十多萬條數據向下拉太費力了。

數據量大，Excel公式處理確實會非常慢，那麼Excel是不是不能處理幾十萬行或者上百萬行的數據，當然不是，現在的Excel已經不僅僅是利用Excel公式或者VBA來進行數據處理了。

Excel中還有兩個超級強大的數據處理模塊：PowerQuery和PowerPivot。

題主的問題主要在於用Excel公式速度會變的很慢，那這個問題使用PowerQuery就可以解決。

PowerQuery在哪裡？

Excel2010和Excel2013需要單獨裝PowerQuery插件，Excel2016及其之後的版本已經內置在Excel中，見下圖：

然後就可以進入Powerquery編輯器中，窗口是這樣的，

是不是感覺完全不像Excel，而像是進入了另外一個軟體中，其實花半天時間熟悉了PQ這些界面的功能，你的Excel水平將大幅提升，輕鬆處理各種在excel中很難處理或者無法處理的事情。

關於界面功能的介紹，請參考：

採悟：我處理過上億行數據，發現PQ這十三招最有用！?

zhuanlan.zhihu.com圖標

也可以通過這篇文章瞭解一下PQ的強大：

採悟：財務人員必學，使用Powerquery是一種什麼體驗？?

zhuanlan.zhihu.com圖標

關於上面提到的PowerPivot，是非常強大的數據建模分析模塊，適合建立模型，多表分析，遠遠超過Excel數據透視的分析功能。

並且現在Excel中這兩個模塊還被微軟整合進了更強大的工具：PowerBI，無論是數據清洗、數據建模、數據可視化，都可以在這裡輕鬆完成，更重要的是，還是免費的。

關於PowerQuery、PowerPivot以及PowerBI的學習，可以關注知乎專欄：

Power BI星球?

zhuanlan.zhihu.com圖標

所以數據處理遇到問題，不能只侷限在Excel現有認知的框架內，還要多瞭解一些Excel的最新功能，是不是有更適合的工具。

思路打開、認知提升才能實現真正的效率提升。

以上

.

公式複製這個操作，100個人中，96.3個人的做法是選中模範單元格，然後向下（假定是向下複製）拖動句柄（就是單元格右下角的小點點），如果是要向下拖動成千上萬行，的確會拖到手軟，甚至誘發腱鞘炎。

但也有3.7個人是這樣做的：點擊模範單元格，然後遊標放在句柄處，以優雅的速度（腦補一下你敲門時的手指動作）雙擊，清脆的「噠噠」兩聲未落，就可以見證下面奇蹟的時刻了。
謝邀。您好，我是一名專註研究圖表、軟體、演算法的商業智能數據分析師，從事電子商務領域已經超過6年。十多萬條數據，如果還用Excel的公式的話，實在覺得不靠譜，肯定會很卡。加上大多數公司的電腦都很渣，配置不行，不能靠原生態的Excel。

解決這個問題，也沒必要用到VBA，因為對大多數人來說，我是來解決問題的，不是來創造新問題的。還是得回到Excel本身來，實際上微軟在這個問題上有一個解決方案，就是PowerBI組件，不要說十多萬，就算是上千萬都不會卡。原因是為什麼呢？

因為PowerBI組件當中的PowerPivot本身就是個資料庫，數據用資料庫來存儲沒有行的概念，只有列，或者叫做欄位的概念。數據查詢效率高，可支持千萬級的數據量。並且，用公式也不靠譜，應該用DAX函數，這種函數比Excel本身的函數公式都要運行速度快，方便。具體可以去學點PowerBI方面的知識，您的這種情況主要是如何快速處理速度，計算公式，即使面對十多萬條也不會導致電腦卡。希望我的回答能對您有所幫助，謝謝！

關於數據

我平日裏會分享一些電商數據文章，橫跨淘寶天貓、京東、拼多多、唯品會、網易嚴選、亞馬遜、速賣通，趁現在有時間剛好分享一點進來觀看，如果您不喜歡，可直接忽略！

前言

年底將至，一夜之間由秋入冬。

最近這段時間，除了忙項目之外，最重要的就是堅持更新課程。PowerBI已經更新了76節課，從數據獲取、數據清洗、數據轉換、數據建模、數據報告，一條龍學習數據分析。

那麼今天，我將用一份訂單數據，用PowerBI+Excel的處理思路，來分析如何將訂單數據玩出水平。數據分析也好，電商數據分析也好，其實本質是一樣的，只不過數據源不同，意味著業務邏輯不同。

正文

這是一家賣電器的店鋪後臺的訂單數據，來源於已賣出的寶貝。隱私數據都被我刪除了，無論從手機號碼、家庭地址，還是會員名，真實姓名，統統去除。

這裡可以分享一個小函數：Replace()，如果需要隱藏一部分文本，可以使用這個函數，正如數據源當中的「買家會員名」，我把前3個文本都用「*」來代替，既不會影響這個欄位的使用，又可以合理隱藏真實信息。

而我們做電商數據分析，一定要記住，是先分析，後數據。除非剛入門的時候，不知道分析什麼好，所以看到有什麼數據就從數據當中去提取有價值的信息。

但真正的高手，都是先分析後數據，先勝而後戰。而分析靠的是什麼，其實就是運營經驗，歸根到底做電商數據分析，不懂電商的業務邏輯，分析出來的信息，是不適合用來做商業決策的。因為信息是錯誤的，那麼決策的成本機會增加。

往往一條有重要指導意義的錯誤信息，會導致一家店鋪，一家公司走向死亡。所以，沒把握之前，就要做微調型決策，一步一步向前走。

那麼，我們可以先分析什麼呢？

既然有「買家會員名」這個欄位，那麼能否知道，有多少買家在過去一段時間進行了二次復購呢，甚至多次復購呢？

分析這個問題的時候，要注意，什麼是復購？

復購，就是說一個客戶在過去一段時間有來店鋪買過兩次以上的產品，也就是下過兩次訂單。由於訂單也有可能是一個客戶在同一時間拍下2個產品，每個產品1個訂單，那為了方便統計，我們也默認這算是復購。

而由於數據源是取決於不同的下單日期的，如果我取的日期是最近一週，那麼很有可能是沒有任何復購的可能性。這個要看這個類目的產品，重複購買的週期有多久，這個一定有行業均值，而如果不知道，憑藉自己的行業經驗也是可以的。

為了回答這個問題，我對數據源進行了處理，處理結果如下。

藉助了PowerPivot的度量值計算，分別統計有多少的「買家會員名」，以及去重之後，還剩下多少數，兩者相減，剩下來的就是「復購會員數」，然後基於度量值計算，去分析復購率。

很明顯，如果我以省份作為分析維度的話，那麼上面這份數據透視表就可以看出不同的省份下哪些復購率高。

如果從這段時間整家店鋪來算，整體復購率是16.30%，而如果細化到各個省份，每個省份都有各自的復購率計算，比如廣東省，復購率16.56%，和整體復購率持平。

通過「復購會員數」最起碼知道哪些人重複購買的次數有多少，數值是2，代表買過3次，其中有2次是復購；數值是10，代表買過11次，其中有10次是復購。

復購次數多，不見得消費的金額就高，所以要看其總金額，看為這家店鋪貢獻了多少人民幣。那麼從數據分析的角度來說，到這裡為止，能給的決策其實就是圍繞這些高復購率的省份，高復購率的會員進行重點維護。

畢竟，現在一次性生意是越來越難做的，要做就要做可以二次復購甚至多次復購的生意。

接下來，我想問，過去一段時間的訂單增長率如何，環比同比的情況是怎樣的？

這裡，就不得不提到最近微店課程一直在說的日曆表，我們接下來結合日曆表，進行日期維度數據的提取。有了日期數據，才能計算環比同比的訂單增長率。

為了回答這個問題，我對數據源進行了處理，處理結果如下。

通過計算訂單的環比、同比數據，可以快速瞭解店鋪訂單增長率走勢如何。

如果純粹靠Excel的函數嵌套來計算同比環比的話，那麼無論是從手工效率，還是數據計算正確率來說，都會大打折扣。而如果藉助PowerPivot的關於同比環比計算的DAX函數，那麼就可以快速實現分析。

這個方法可以分析訂單增長率，難不成就不能分析客戶增長率嗎？當然可以，這裡就不一一演示。

鑒於時間關係，這裡再分析一個問題，任務額完成度。由於我的數據源這邊只有2019年7月和8月的訂單數據，所以我們只針對這2個月的數據進行分析。

假設之前制定今年的7月份銷售額指標要達到10萬，8月份要20萬，這時候如何通過自動化的方式來分析這個任務額完成度？所謂完成度，就是用實際銷售額比銷售額指標，要的就是看是否能達標。

接下來基於這份任務表，結合我們之前做的訂單分析模型，來對數據源進行處理。

為了回答這個問題，我對數據源進行了處理，處理結果如下。

這樣子，只需要選擇年份數據，就可以快速分析出每一年的實際訂單任務額完成度是多少，超過90%，就算達標。

那麼，這裡給每個月份指定的任務額是固定的嗎？其實我們只需要修改一下數字，刷新之後，這些數據都會自動變化，自動計算。

而它計算的背後，是有一個數據模型在控制的，這個也不是Excel的函數嵌套能辦到的事情。

簡單三個問題，通過PowerBI+Excel就可以輕鬆獲取，那麼，今天的分析就到這裡為止。

希望我今天的分享能對大家有所幫助，謝謝！不廢話，關注知乎專欄花隨花心，送數據分析工具箱！

如果十萬條數據的公式計算邏輯比較簡單，其實還是很輕鬆的~下圖中有奇偶數判斷和正弦值，基本上秒刷新。

當我隨機1-100並試圖對10萬條隨機數據使用VLOOKUP函數在1-100匹配時也基本上是秒刷新。

如何填充?

題主在題目中明確說：「下拉」太費力氣？

如果你苦惱的地方是「如何對一個公式下拉填充」，那麼你有兩個選擇：

  1. 快捷鍵，Ctrl+方向，配合Shift可以實現選擇
  2. 名稱框，就在編輯欄的左側，比如下面的示例

Excel快速跳轉和選擇（必學）

Excel利用定位空值插入空行（2/2）

上面看起來平安無事，但實際上的使用場景要比這個複雜的多。各種函數套用，各種來回引用會導致性能下降的很快。

如果考慮兼容只能使用公式的話，那麼你只能優化你的計算邏輯，儘可能少的使用查找和轉換函數。

如果你不需要考慮兼容，且版本足夠高，比如2016~當然2010和2013可以安裝插件，那麼你可以愉快的使用Power Query和Power Pivot。

Power Query

PQ主要負責導入和簡單的計算，下面給你一個效率參考

5個文件，42列，共計41MB，15萬條。

  • 導入，類型轉換，40秒不到
  • 導入，不進行類型轉換，35秒左右

所以基本上時間都浪費在了文件讀取和解析上，更慘的是之前使用Pandas解析1萬條同樣的數據用了10秒多~

利用PQ你可以從xls和xlsx標準文件讀取，也可以從csv，pdf，xml，資料庫，網路等進行數據獲取。總體來看PQ上手難度很小, 它的入口在這裡~

下面我們搞個簡單案例試試水, 首先我們創建一個配送人員訂單表

不懂表格的可以參考:

Excel表格之初探"表格"

然後我們把該表導入到PQ中

得到一個類型這樣的界面

左側為查詢區域, 類似工作簿中的工作表, 右側為步驟區域, 我們每一步操作都會顯示在其中. 注意觀察右側實際上有"更改類型"這一步驟, 看下面的圖, 我們可以發現列標題都加了對應的類型. 當然這是自動轉換, 你可以刪除這一步驟, 手動轉換類型

當你滑鼠放到叉叉會變紅, 單擊就可以刪除這一步驟

刪除後效果

需要注意的是, 如果刪除操作是不可以恢復的~這裡我們可以重新點擊小圖標自己設定類型

下面我們加一個準時率列試試

這時候我們只需要上傳到Excel表格中就行啦~

如果我們原來的表格出現變動, 只需要刷新該表就行了. 有人說為什麼設置的百分比變成了小數?emmm...沒關係, 重新設置一次就行了, 以後刷新就會自動設置了

這裡為了防止弄混, 請注意藍色為原始表, 綠色為PQ導出的表

當然這只是簡單的使用, 在你深入學習後你可能會寫自定義的函數或者手動書寫每個查詢過程~

PowerPivot

PQ側重數據的導入和清洗, PP則注重模型的關聯和計算. 為了方便我們繼續使用上述的數據進行演示

先來一個簡單的需求, 給人員分組, 我們把A分到一組, B分到二組. 如果只是如此簡單, 我們完全可以使用數據透視表中的"分組"或者在原表中VLOOKUP一下, 但是實際工作數據量非常巨大...那麼優雅的做法是這樣的~注意: 實際情況中我們會利用唯一標識符(比如員工ID)進行身份確認, 而不是姓名

首先需要載入PP

如果你沒有"開發工具"選項卡, 可以右鍵任意選項卡, 選擇"自定義功能區", 勾選"開發工具"

好~我們把PQ導出的數據導入到PP中, 你可以點擊一下PQ導出表的任意位置, 然後在"Power Pivot"選項卡中選擇"添加到數據模型"

然後實際情況中並不推薦上面的方式, 由於我們根本不需要查看源數據, 你也可以在右側的"工作簿"中右鍵, 選擇"載入到...".如果沒有"工作簿查詢", 點擊"數據"-"顯示查詢"顯示

在上圖中, 我們選擇了"僅創建連接", 即不顯示錶, 並且將連接添加到了數據模型, 我們可以點擊"Power Pivot"選項卡 - "管理"查看到數據表

下面我們在Excel中在找個地方建一個"分組"表進行分組(這裡只是示例, 實際情況中最好單獨建立人員信息工作簿), 並添加到數據模型, 然後對人員建立關聯

需要注意: Excel僅支持一對多(*)的關係, 即我們的"分組"表中不能存在重複

下面在PP中創建一個透視表

創建好透視表以後,我們發現不太一樣~其中"活動"是指參與透視表的表

我們隨便拉幾個數據試試

那麼如果我們想要修改A的分組到三組, 只需要修改原始數據並刷新透視表就好了~如果原始表通過PQ導入, 那麼你可能還需要刷新PQ導出的表

至此其實都是簡單的玩法, 即使不會PP也能做, 那麼再提個需求, 插入日期切片器, 在查看每天訂單的同時查看當月的總訂單數~如果不考慮月度的話, 透視表大概是這樣的

那麼如何做出月度累計匯總?此時就需要"智能時間"函數登場啦~由於對初學者很不友好, 這是看看就好~

大概就是這樣~展示下現在做的...保密保密, 所以打碼了~哈哈哈

不是有自動填充麼

或者使用VBA解決此問題

推薦閱讀：
相關文章