<menuitem id="cxwpg"></menuitem>
    <menuitem id="cxwpg"><optgroup id="cxwpg"><thead id="cxwpg"></thead></optgroup></menuitem>
  1. <option id="cxwpg"></option>
    <div id="cxwpg"><td id="cxwpg"></td></div>
        1. <samp id="cxwpg"><strong id="cxwpg"></strong></samp>
        2. <track id="cxwpg"><span id="cxwpg"></span></track>
          <bdo id="cxwpg"><optgroup id="cxwpg"></optgroup></bdo>
          返回首頁
          當前位置: 主頁 > Excel教程 > Excel2016教程 >

          excel跨工作簿提取數據 excel不同工作簿數據匯總

          時間:2018-09-16 23:44來源:Office教程學習網 www.12042044.com編輯:麥田守望者

          本文分享excel多個工作簿查詢數據提取匯總方法,使用到Power Query插件來完成Excel不同工作簿數據匯總.

          小王所在的公司在全國各地都有分部,每到年底小王都很頭疼。各個地區的銷售數據需要匯總,盡管工作簿模板一致,但是全國那么多城市,工作簿也要逐一打開復制粘貼數據。工作簿容量有的大有的小,一個個打開要花費大量的時間。那有沒有什么好方法可以不用打開工作簿直接提取數據呢?今天給大家介紹了兩種方法來實現。
          如圖,在桌面這個文件夾中舉例說明了五個城市的12個月的銷售數據。
          excel多個工作簿查詢數據
          其中每個工作簿在“銷售額”工作表下存儲的是該城市1-12月的數據,現在要不打開工作簿批量提取各個城市12月份的合計值,也就是“銷售額”工作表C14單元格的值。
          Excel練習課件請到QQ群:537870165下載
          一、設置引用公式法提取
          1.在該文件夾下,新建一個記事本,輸入代碼dir *.xlsx /b >1.txt ,保存類型選擇“所有文件”,另存為bat文件。
          excel多個工作簿查詢數據
          2.雙擊新建好的bat文件,該文件夾就會生成1.txt文件,打開文件就能看到當前文件夾下的所有xlsx文件的文件名。通過這種方式我們就獲取到了該文件夾所有的工作簿名稱。
          excel跨工作簿提取數據
          3.新建一個工作簿用來存儲提取到的數據。如下圖所示,把獲取到的工作簿名稱輸入A列,現在要把各個工作簿C14的值放入對應的B列。在B1單元格列輸入
          ="'C:\Users\Administrator\Desktop\銷售\["&A1&"]銷售額'!C14" ,在單元格顯示為'C:\Users\Administrator\Desktop\銷售\[北京.xlsx]銷售額'!C14 ,也就是文件夾下“北京”工作簿的“銷售額”工作表的C14單元格,然后下拉填充。
          不同工作簿數據匯總
          4.選中B列復制然后粘貼為值
          5.按住Ctrl+H,打開“查找和替換”窗口,把 'C 替換成 ='C ,點擊“全部替換”。
          excel多個工作簿查詢數據
          這樣單元格的值就變成各工作簿的合計值。
          這種方法在實際操作中很方便,上面獲取文件夾工作簿名稱的方法也很實用。但是局限性就是提取的值必須在所有表格的同一單元格內。那有沒有什么方法可以不按單元格直接提取出月份為合計那一行的銷售額呢?之前給大家的介紹的Power Query就可以實現。
          二、Power Query提取
          1.點擊數據選項卡下,新建查詢—從文件—從文件夾。
          2.瀏覽窗口找到文件夾的路徑,點擊確定。
          文件夾窗口點擊編輯。
          3.在Power Query編輯器呈現的就是該文件夾下的所有內容,這個之前給大家介紹過,把【content】這列binary格式轉換成table格式提取data就可以提取文件夾各個表格的數據。我們這里只列出步驟,具體介紹可以點擊這里查看:插入鏈接
          點擊添加列選項卡下的自定義列。
          在自定義列窗口的列公式下輸入 =Excel.Workbook([Content]) 點擊確定。
          4.把除【Name】和【自定義】兩列以外的其他列刪除。按住Ctrl選中兩列,右鍵選擇刪除其他列。
          5.點擊【自定義】列右側的展開按鈕,展開Data這列,不勾選“使用原始列名作為前綴”。
          然后再點擊展開的【Data】列右側的展開按鈕,展開所有列。不勾選“使用原始列名作為前綴”
          展開結果如下:
          6.那現在要做的就是把【Column2】這列的合計篩選出來就可以了。點擊右側的篩選按鈕,勾選“合計”,點擊確定。
          展開結果如下:
          7.接下來要做的就是把這個上載到表格。點擊開始選項卡下的關閉并上載。
          表格如下:
          使用Power Query就比較智能,這種方法不限定單元格位置,根據條件批量提取跨工作簿的單元格值。更加實用快捷。

           

          ------分隔線----------------------------
          標簽(Tag):excel excel2016 excel跨工作簿提取數據
          ------分隔線----------------------------
          推薦內容
          猜你感興趣
          内蒙古11选5计划
          <menuitem id="cxwpg"></menuitem>
            <menuitem id="cxwpg"><optgroup id="cxwpg"><thead id="cxwpg"></thead></optgroup></menuitem>
          1. <option id="cxwpg"></option>
            <div id="cxwpg"><td id="cxwpg"></td></div>
                1. <samp id="cxwpg"><strong id="cxwpg"></strong></samp>
                2. <track id="cxwpg"><span id="cxwpg"></span></track>
                  <bdo id="cxwpg"><optgroup id="cxwpg"></optgroup></bdo>
                  <menuitem id="cxwpg"></menuitem>
                    <menuitem id="cxwpg"><optgroup id="cxwpg"><thead id="cxwpg"></thead></optgroup></menuitem>
                  1. <option id="cxwpg"></option>
                    <div id="cxwpg"><td id="cxwpg"></td></div>
                        1. <samp id="cxwpg"><strong id="cxwpg"></strong></samp>
                        2. <track id="cxwpg"><span id="cxwpg"></span></track>
                          <bdo id="cxwpg"><optgroup id="cxwpg"></optgroup></bdo>