有的時(shí)候一個(gè)工作簿中有十多個(gè)工作表，想把格式或者數(shù)據(jù)進(jìn)行批量處理，如果逐個(gè)修改，即便使用復(fù)制粘貼功能，依然很是浪費(fèi)時(shí)間，很麻煩。其實(shí)，微軟為我們提供了很簡(jiǎn)單的解決方法，那就是先選中多個(gè)目標(biāo)工作表，再進(jìn)行操作，就可以完成批量修改啦。今天來(lái)和小編一起來(lái)學(xué)習(xí)如何為多張工作表同時(shí)進(jìn)行批量編輯吧。

一、選中多個(gè)工作表

如果是連續(xù)的工作表，則點(diǎn)擊第一個(gè)目標(biāo)工作表標(biāo)簽，按住【Shift】鍵，同時(shí)再點(diǎn)擊末尾的目標(biāo)工作表標(biāo)簽，這樣就選中了區(qū)間所有的工作表。如果不是連續(xù)的工作表，則點(diǎn)擊第一個(gè)工作表標(biāo)簽后，按住【Ctrl】鍵，依次點(diǎn)擊目標(biāo)工作表，這樣就選中了不連續(xù)工作表。

二、批量編輯

完成工作表的選中后，在目前單元格中的任意操作都將運(yùn)用到被選中的每一個(gè)工作表中。例如：（1）輸入數(shù)據(jù)：當(dāng)我在A1中輸入數(shù)字1，則后面的所有表格中都將在單元格A1中完成數(shù)字1的輸入。（2）格式設(shè)置：比如調(diào)整單元格A1中的數(shù)字大小、顏色、填充色等。（3）單元格設(shè)置：合并單元格、插入、刪除、調(diào)整單元格大小等；（4）插入公式：自制公式、插入函數(shù)等。

PS：完成批量操作后，需要在任意工作表標(biāo)簽上單擊鼠標(biāo)右鍵，在彈出的列表中，點(diǎn)擊【取消成組工作表】按鈕，再次操作時(shí)就會(huì)只應(yīng)用到當(dāng)前工作表。

歡迎關(guān)注，以上。

之前寫過(guò)一篇文章，介紹如何合并一個(gè) Excel 文件中的多個(gè)工作表。

以后如果碰到要合并12個(gè)月的

每一年的

全部學(xué)院的

還有來(lái)自五湖四海的

各種五花八門成千上萬(wàn)行的數(shù)據(jù)，都可以在幾秒內(nèi)輕松搞定。

不用函數(shù)公式，不用編寫VBA代碼，還能自動(dòng)更新。

體驗(yàn)了這一個(gè)功能，我就對(duì) Excel 2016 的查詢功能愛(ài)不釋手了！還能不能更牛C一點(diǎn)呢？

很多時(shí)候，我們要匯總合并的表格不在一個(gè)文件里頭。

比如，做好報(bào)名人員信息登記表模板，群發(fā)給了100個(gè)訓(xùn)練班的班主任。他們登記好表格表格發(fā)回給我，肯定是分開一個(gè)個(gè)文件的呀。有沒(méi)有辦法把他們自動(dòng)合并到一個(gè)匯總表里頭呢？

再比如，我每周都要匯總一次公司所有產(chǎn)品的銷售明細(xì)，拿到手的卻只有單周數(shù)據(jù)。難道我還要每周復(fù)制粘貼，每周重做一遍統(tǒng)計(jì)工作嗎？

這不科學(xué)

……

幸好幸好，查詢這個(gè)功能還可以合并多個(gè)文件中的多個(gè)表格。操作起來(lái)和匯總單個(gè)文件中的多個(gè)表差不多，只是多了幾步而已。

以每個(gè)業(yè)務(wù)員發(fā)出的贈(zèng)品明細(xì)表為例。每人提交的表格文件，統(tǒng)一放入一個(gè)文件夾中。

接下來(lái)我們就看如何，用5個(gè)步驟，將這個(gè)文件夾里的所有表格數(shù)據(jù)全部提取出來(lái)合并到一個(gè)新的匯總表中。

第一步：導(dǎo)入文件夾

在數(shù)據(jù)選項(xiàng)卡下，【新建查詢】-選擇【從文件】-【文件夾選項(xiàng)】。

導(dǎo)入文件夾后，跟隨提示進(jìn)入查詢編輯器。

上一篇文章，在導(dǎo)入單個(gè)Excel文件中的多個(gè)工作表后，直接就開始對(duì)數(shù)據(jù)進(jìn)行整理操作了。

但是這一次導(dǎo)入的是文件夾中的多個(gè)Excel文件，目前為止獲取到的數(shù)據(jù)，都是Excel工作簿的名稱、格式、創(chuàng)建日期等文件基本信息，還沒(méi)有文件夾、工作簿的“外殼”包裹著。

所以需要額外做的是穿透文件夾、工作簿，提取到每個(gè)工作簿中的表格和數(shù)據(jù)。

第二步：提取工作簿

要穿透工作簿提取出工作表，需要在查詢編輯器中創(chuàng)建一個(gè)輔助列。所以，先選擇【添加列】-【添加自定義列】。

然后添加自定義對(duì)話窗中，寫入一條公式。

（別擔(dān)心，很短，只要一模一樣復(fù)制過(guò)去就可以了）

=Excel.Workbook([Content])

注意，一定要一模一樣，包括字母大小寫。

一定要用英文符號(hào)！一定要用英文符號(hào)！一定要用英文符號(hào)！

重要的事情說(shuō)三遍

點(diǎn)擊確定以后，就將文件夾中的全部 Excel 工作簿放入編輯器中。

第三步：提取工作表

點(diǎn)擊自定義列旁邊的擴(kuò)展按鈕，展開按鈕工作表列表。

再繼續(xù)提取工作表中的詳細(xì)數(shù)據(jù)。

第四步：提取數(shù)據(jù)

點(diǎn)擊Custom.Data列旁的擴(kuò)展按鈕，就能展開明細(xì)數(shù)據(jù)。

第五步：清洗數(shù)據(jù)

后面的操作就和上一篇操作步驟一樣了。再簡(jiǎn)單復(fù)習(xí)一遍。選中需要保留的數(shù)據(jù)列，然后刪除其他列。

將第一行設(shè)為標(biāo)題行。

去除標(biāo)題同名數(shù)據(jù)行、Null空行，篩選出最終需要的匯總數(shù)據(jù)。

第六步：完成合并

將加工完成的數(shù)據(jù)加載至工作表中。以后再添加新工作表，直接丟進(jìn)文件夾，然后打開合并中刷新查詢就行。

Tips:如果從管理系統(tǒng)中導(dǎo)出的文件是 CSV、TXT 等文本格式的文件就更加簡(jiǎn)單。從文件夾導(dǎo)入數(shù)據(jù)到查詢編輯器之后，不需要寫公式提取工作表，直接將 Content 列擴(kuò)展即可。

擴(kuò)展得到數(shù)據(jù)列表后，繼續(xù)擴(kuò)展得到詳細(xì)數(shù)據(jù)，再按照上述步驟清洗數(shù)據(jù)，就能達(dá)到同樣的查詢效果。

看，就是簡(jiǎn)單的點(diǎn)擊操作，最復(fù)雜的也就是一行固定不變的簡(jiǎn)短代碼，=Excel.Workbook([Content])

意思是來(lái)源于 Excel 軟件的工作簿內(nèi)容。

有了這一招，只要搭建好統(tǒng)計(jì)報(bào)表的框架，數(shù)據(jù)引用自合并以后的匯總表。以后有新的數(shù)據(jù)表，就丟進(jìn)文件夾里 100 多份表格匯總、統(tǒng)計(jì)分析、別人幾天的工作量，每月來(lái)一次。

就這樣輕輕松松點(diǎn)擊一下刷新，搞定！可以喝咖啡去咯~

其實(shí)，多表合一方法有很多，這里我只介紹兩種最快捷的方法!

將下圖一月、二月、三月3張表數(shù)據(jù)合并匯總到一季度表中。

方法非常簡(jiǎn)單，選擇C2：E5區(qū)域，輸入SUM('*'!C2), 按Ctrl+Enter。

3個(gè)月的數(shù)據(jù)瞬間合并完畢

反思一下，如果只合并1月和2月兩個(gè)表的數(shù)據(jù)呢？

很簡(jiǎn)單！在C2單元格輸入SUM('1月:2月'!C2)，即可

再問(wèn)：合并1月和3月的兩個(gè)表格，函數(shù)應(yīng)該怎么寫呢？

=SUM('1月'!C2,'3月'!C2)

下面把表格改一下，看看另一種合并方式

三個(gè)表格分公司A、分公司B、分公司C這三列，進(jìn)行匯總。

在B2單元格輸入 =INDIRECT(B$1“!b”ROW()) 即可

NDIRECT(B$1“!b“ROW())文本引用，即引用分公司AB2單元格數(shù)據(jù)。

注意：INDIRECT()參數(shù)不加引號(hào)情況下只做地址引用，只返回單元格地址。

希望大家可以通過(guò)以上幾種合并效果，可以舉一反三，做出更有效的函數(shù)應(yīng)用。

沒(méi)有Excel2016的伙伴，可以私聊回復(fù)“2016”獲?。?/p>

也可回復(fù)“考勤表”、“倉(cāng)管系統(tǒng)”、"分享"等關(guān)鍵字，獲取更多免費(fèi)資源

在Excel表格中，我們經(jīng)常需要填充一些數(shù)據(jù)，填充數(shù)據(jù)分為有手動(dòng)填充，自動(dòng)填充，。

一、填充相同數(shù)據(jù)；輸入數(shù)字，直接用鼠標(biāo)拖動(dòng)，四個(gè)方向任意拖動(dòng)填充。

二、填充序列號(hào)；輸入，分別在兩個(gè)單元格輸入1，2，然后用鼠標(biāo)拖動(dòng)。

三、用填充標(biāo)記，真需要在單元格輸入一個(gè)數(shù)字，選擇，填充序列，會(huì)自動(dòng)填充號(hào)。

大多數(shù)人對(duì)于Excel的印象，可能就是無(wú)限的表格、表格跟表格……但你相不相信下面這張風(fēng)景圖，竟然是用Excel畫出來(lái)的呢？！

這幅畫就是日本群馬縣高齡77歲的老爺爺堀內(nèi)辰男用Excel所創(chuàng)作出來(lái)的作品！

他原本就十分喜愛(ài)繪畫，退休后想學(xué)著用photoshop等等繪圖軟體來(lái)作畫，但發(fā)現(xiàn)好難喔……

結(jié)果意外發(fā)現(xiàn)在辦公軟體中的Excel超好用！他笑說(shuō)自己有點(diǎn)摳，能用電腦作畫就不用買畫具，也不用裝水來(lái)洗畫筆了太可愛(ài)了吧～

從2000年開始用Excel作畫，一開始曾被譏笑既沒(méi)用又落伍，根本是浪費(fèi)時(shí)間！但最后他不但奪得了日本自動(dòng)圖形藝術(shù)大賽的冠軍，也開了畫展、作品還被收藏到美術(shù)館，甚至開課當(dāng)起老師，報(bào)名的學(xué)生更是場(chǎng)場(chǎng)都爆滿！

堀內(nèi)辰男所畫出的日本傳統(tǒng)風(fēng)景畫，不但保留了藝術(shù)的韻味也混合了現(xiàn)代感

人像畫也很拿手！

堀內(nèi)辰男一開始作畫時(shí)就訂下目標(biāo)，他要在十年內(nèi)畫出能夠展示給別人看的作品！

而堀內(nèi)辰男也表示，畫東西能讓他獲得成就感，讓他想持續(xù)畫下一個(gè)作品，就這樣一直一直畫下去！

雖然老爺爺早已不是第一個(gè)使用Excel來(lái)作畫的人，但是如此高齡還能畫出這么精致的作品，實(shí)在令人對(duì)他卓越的藝術(shù)天分感到敬佩??！

你也有很想做的事情嗎？爺爺?shù)睦诱嬖V大家，繼續(xù)在喜歡的事情上投入熱情耕耘，一定能收獲最美好的果實(shí)！

下面為各種產(chǎn)品1~9月份的出口數(shù)據(jù)。由于數(shù)據(jù)太多，很難看出每個(gè)產(chǎn)品一年來(lái)的數(shù)量變化情況。

我們可以借助2個(gè)excel工具，表格馬上變得直觀起來(lái)。（2010以前版本無(wú)法使用）

1、條件格式的色階

根據(jù)顏色深淺，可以很清晰的看出某種產(chǎn)品在一年中的出口變化情況。

制作演示：

2、迷你圖

同樣，通過(guò)在單元格中添加微型折線圖，同樣可以看出每一種產(chǎn)品在一年中的出口變化情況。

制作演示：

這兩個(gè)Excel小技巧適合數(shù)據(jù)分析新手，很簡(jiǎn)單的操作就能讓表格變得漂亮且實(shí)用，領(lǐng)導(dǎo)看了肯定以為你用了多么多么高深的功能。所以同學(xué)們一定要學(xué)會(huì)，有機(jī)會(huì)在領(lǐng)導(dǎo)面前露一手。

EXCEL數(shù)據(jù)處理時(shí)，最頭疼的事，就是碰到不規(guī)范的數(shù)據(jù)格式、文本格式，甚至不規(guī)范的混合文本，需要花較多時(shí)間先規(guī)范數(shù)據(jù)。今天，通過(guò)函數(shù)公式為大家提供“從混合文本中提取漢字、數(shù)字、字母”的2個(gè)小技巧。

1、“文字+數(shù)字”、“文字+字母”的分離法

上圖，A列單元格為姓名和電話號(hào)碼混合的文本，觀察這一列數(shù)據(jù)的特點(diǎn)，發(fā)現(xiàn)左邊都為文字，右邊都為數(shù)字，所以在提取內(nèi)容時(shí)，我們首先應(yīng)想到的是用“Left”、“Right”函數(shù)提取，在B列“漢字”欄，B2單元格寫入公式：=LEFT(A2,LENB(A2)-LEN(A2))，這里的“LENB(A2)”計(jì)算的是A2單元格的字節(jié)數(shù)，結(jié)果為“12”（電腦中，一個(gè)漢字算2個(gè)字節(jié)），“LEN(A2)”計(jì)算的是A2單元格的字符個(gè)數(shù)，結(jié)果為“9”，12-9=3，所以，運(yùn)用LEFT函數(shù)提取A2單元格，左邊3個(gè)字符，就是“劉滬陽(yáng)”，依次向下復(fù)制。

在C列“數(shù)字”欄，C2單元格寫入公式：=RIGHT(A2,2*LEN(A2)-LENB(A2))，根據(jù)一個(gè)漢字算2個(gè)字節(jié)的規(guī)則，運(yùn)用一個(gè)簡(jiǎn)單的邏輯運(yùn)算，2倍的字符數(shù)-字節(jié)數(shù)=數(shù)字部分的個(gè)數(shù)，用Right函數(shù)提取A2單元格，右邊6個(gè)字符(2*9-12=6)，就是“660228”,依次向下復(fù)制。

同樣，如果文本結(jié)構(gòu)為“文字+字母”，一樣可以用這個(gè)方法實(shí)現(xiàn)提取功能。

2）“數(shù)字+單位”、“數(shù)字+字母”、“數(shù)字+文字”的分離法

上圖，每個(gè)單元格數(shù)字長(zhǎng)度不一致，用分列功能無(wú)法實(shí)現(xiàn)數(shù)字和單位分開。如果是成百上千行數(shù)據(jù)，不可能一個(gè)個(gè)單元格分別復(fù)制、粘貼，再一個(gè)個(gè)刪除不需要的部分。在"提取數(shù)字"列G2單元格輸入公式：=-LOOKUP(1,-LEFT(F2,ROW($1:$9)))，向下復(fù)制，就OK了。

解釋一下這個(gè)比較難懂的公式：

ROW($1:$9)，生成{1;2;3;4;5;6;7;8;9}常量數(shù)組

LEFT(F2,ROW($1:$9))，分別取F2單元格的左邊1個(gè)，2個(gè)，3個(gè)……9個(gè)，結(jié)果如下：{"1";"12";"120";"120公";"120公里";"120公里/";"120公里/小";"120公里/小時(shí)";"120公里/小時(shí)"}

-LEFT(F2,ROW($1:$9))，步驟2取出的是數(shù)值，加負(fù)號(hào)就是對(duì)其進(jìn)行減法運(yùn)算，從而將其轉(zhuǎn)化為文本，對(duì)字母或漢字進(jìn)行加減會(huì)出錯(cuò)，結(jié)果如下：

{-1;-12;-120;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

LOOKUP(1,-LEFT(F2,ROW($1:$9)))解釋：

Lookup有三大特點(diǎn)：特點(diǎn)1：按二分法查找；特點(diǎn)2：忽略錯(cuò)誤值；特點(diǎn)3：要求數(shù)據(jù)按升序排列，如果沒(méi)有按升序排列，也會(huì)認(rèn)為后邊的數(shù)字比前邊的大，所以

＝LOOKUP(1,{-1;-12;-120;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!})，等同于

=-LOOKUP(1,{-1;-12;-120})，等于--120，也就等于120

SUBSTITUTE函數(shù)功能是將字符串中的部分字符串用新字符串替換，公式的意思是將F2單元格中的數(shù)字部分替換為空，那么，剩下的就是單位了。當(dāng)然，還可以寫成：=RIGHT(F2,LEN(F2)-LEN(G2))

文中最復(fù)雜的，可能是理解幾個(gè)公式，其實(shí)不太懂也沒(méi)關(guān)系，只要關(guān)注此文的百家號(hào)，或者收藏一下文章，遇到類似問(wèn)題時(shí)復(fù)制公式，稍微修改幾個(gè)參數(shù)就OK了。

每天學(xué)習(xí)一點(diǎn)點(diǎn)，每天進(jìn)步一點(diǎn)點(diǎn)，積硅步，定能致千里，得大成。

小小表格，威力無(wú)窮。我很喜歡表格這個(gè)功能，但愿你也能喜歡。

1.隔行填充顏色，結(jié)合切片器實(shí)現(xiàn)動(dòng)態(tài)篩選

詳見昨天的文章《只需兩招讓你的Excel瞬間高大上》

2.智能填充公式

數(shù)據(jù)源每天都會(huì)新增記錄，為了讓公引用到更多的單元格，一般都是輸入一個(gè)公式以后，然后就一直往下拉。不過(guò)這種有一個(gè)弊端，因?yàn)樘鄦卧袷褂霉?，?huì)變得有點(diǎn)卡頓。

=IF(F2="","",F2*G2)

還有一種比較常用的做法就是，每次更新記錄，公式就用鼠標(biāo)向下拖動(dòng)幾行。這樣比上一種運(yùn)行速度更快，不過(guò)每次拖動(dòng)公式這個(gè)操作也是很繁瑣。

而插入表格以后，就不一樣了，只要你輸入第一行公式，以后每次增加記錄，公式都會(huì)自動(dòng)生成，非常方便。

除了新增加的數(shù)據(jù)可以新增加公式，插入的行也可以新增加公式。

3.為透視表提供動(dòng)態(tài)數(shù)據(jù)源

普通的數(shù)據(jù)源，創(chuàng)建透視表以后，新增加記錄，在透視表中是沒(méi)辦法更新的。

現(xiàn)在新增加一條北京，Excel不加班的記錄。

刷新透視表后依然沒(méi)有任何變化。

現(xiàn)在透視表是根據(jù)表格創(chuàng)建，同樣也是新增加一條記錄。

刷新透視表后就立即更新統(tǒng)計(jì)，非常智能。

很多用戶都被excel如何拆分單元格的問(wèn)題難住了，接下來(lái)，小編就為各位用戶們講解在excel中拆分單元格的具體操作方法。

excel系列軟件最新版本下載

Excel表格下載器軟件版本：2014 官方免費(fèi)版應(yīng)用工具立即查看

excel2007軟件版本：免費(fèi)完整版應(yīng)用工具立即查看

方法一

1首先選中要拆分的單元格，然后點(diǎn)擊開始選項(xiàng)卡中的合并后居中。

2這時(shí)單元格就已經(jīng)拆分完畢。

方法二

右鍵單擊要拆分的單元格，選擇設(shè)置單元格格式。

在設(shè)置單元格格式窗口中去掉合并單元格前面的對(duì)鉤。

這時(shí)單元格就拆分好了，兩種方法都很簡(jiǎn)單。

上文就是小編講解的在excel中拆分單元格的具體操作方法，你們都學(xué)會(huì)了嗎?

在excel中我們有時(shí)候需要將多個(gè)圖表組合在一起，以便我們更清楚地查看其中的數(shù)據(jù)。以下面的“成本費(fèi)用預(yù)算表”為例，看看如何應(yīng)用組合圖。

首先我們插入“簇狀柱形圖”，選擇數(shù)據(jù)源，設(shè)置圖例、標(biāo)題等得到如下圖表。

這時(shí)我們發(fā)現(xiàn)：圖表中沒(méi)有出現(xiàn)“增減率”的圖。因?yàn)椤霸鰷p率”的數(shù)據(jù)遠(yuǎn)遠(yuǎn)小于“上年實(shí)際”、“本年預(yù)算”的數(shù)據(jù)，所以在圖表中，“增減率”數(shù)據(jù)不能有效地呈現(xiàn)出來(lái)。如何解決？我們想到設(shè)置次坐標(biāo)軸的方法，來(lái)顯示增減率數(shù)據(jù)，也即創(chuàng)建組合圖來(lái)呈現(xiàn)。

方法：1、選擇“增減率”圖例項(xiàng)，鼠標(biāo)右鍵選擇更改圖表類型，得到下圖所示：

2、選擇次坐標(biāo)軸“增減率”，圖表類型選擇“帶數(shù)據(jù)標(biāo)記的折線圖”，下圖所示：

單擊確定，組合圖創(chuàng)建完畢，最終效果如下：

“增減率”數(shù)據(jù)清楚地呈現(xiàn)出來(lái)了。

office微信公眾號(hào)

相關(guān)鏈接：

制作九九乘法口訣表

頻數(shù)分析||Excel在統(tǒng)計(jì)中的應(yīng)用

Word也能制作工資條

AND、OR、NOT函數(shù)使用介紹

EXCEL中獲取外部數(shù)據(jù)

15個(gè)模式化的EXCEL表格公式

超簡(jiǎn)便的個(gè)稅計(jì)算方法