Excel動(dòng)態(tài)圖表應(yīng)用

1、http:/ excel 動(dòng)態(tài)圖表應(yīng)用1 首先看一下數(shù)據(jù)源：根據(jù)分析需求，要制作可以 a列、 b列、月份選擇的動(dòng)態(tài)數(shù)據(jù)表。例如某月中某個(gè)城市各個(gè)組別的銷(xiāo)售圖表包含柱形圖及餅圖，柱形圖用以突出各個(gè)項(xiàng)目之間的對(duì)比情況，餅圖以用對(duì)各個(gè)項(xiàng)目在整個(gè)數(shù)據(jù)中的占比情況，完成后效果如下：某個(gè)城市某個(gè)組別在各個(gè)月的銷(xiāo)售圖表：本實(shí)例的圖表重點(diǎn)在于兩個(gè)選擇項(xiàng)的同時(shí)對(duì)下拉列表的控制互動(dòng)。為了便于理解及添加新的應(yīng)用要求，因此在【名稱(chēng)管理器】中新建名稱(chēng)來(lái)解決這類(lèi)問(wèn)題。此次實(shí)例中的名稱(chēng)管理器中的新建名稱(chēng)公式如下：如果覺(jué)得有些生澀難懂，可以參考我之前的博文：excel 動(dòng)態(tài)圖表應(yīng)用，與本文類(lèi)似但應(yīng)用的名稱(chēng)公式較為簡(jiǎn)單。這些名

2、稱(chēng)的主要功能是快速地、動(dòng)態(tài)地賦值給圖表的【系列】及【數(shù)據(jù)】、【標(biāo)簽】等。名稱(chēng)管理器中的部分名稱(chēng)解釋?zhuān)海?）按鈕 _分公司： =圖表 !$m$3 將三個(gè)分公司按鈕分為一組，設(shè)置其屬性【控制】單元格鏈接“圖表!$m$3”。按鈕 _下拉列表分類(lèi)鏈接至m1 。（2）標(biāo)簽： =choose( 圖表 !$m$1,choose( 圖表 !$m$3, 組別 _北京 , 組別 _上海 ,組別 _廣州 ), 月) 為圖表【水平（分類(lèi)）軸標(biāo)簽】做標(biāo)識(shí)。choose 選擇函數(shù)是本動(dòng)態(tài)圖表的核心部分，“北京、上海、廣州”這三個(gè)選項(xiàng)會(huì)鏈接至m3單元格，因此可以用m3單元格來(lái)控制標(biāo)簽標(biāo)識(shí)。（3）系列： =choose( 圖

3、表 !$m$1, 系列 _月, 系列 _組別 ) 服務(wù)于圖表的【圖例項(xiàng)（系列）】。它與其他幾個(gè)名稱(chēng)中的函數(shù)類(lèi)似嵌套了choose 函數(shù)。標(biāo)簽和系列在圖表中的位置【插入】選擇圖表類(lèi)型創(chuàng)建圖表，然后點(diǎn)擊右鍵【選擇數(shù)據(jù)】。如下圖所示：當(dāng)選擇編輯時(shí)需要輸入完整的工作簿路徑：（4）系列 _月： =offset(choose(圖表 !$m$3, 區(qū)域 _北京 , 區(qū)域 _上海 , 區(qū)域 _廣州 ),0,圖表!$m$2,counta(choose(圖表 !$m$3, 組別 _北京 , 組別 _上海 ,組別 _廣州 ),1) 這個(gè)函數(shù)比較長(zhǎng)，這里稍微解釋一下，如果對(duì)函數(shù)構(gòu)成不了解，可以按f1 幫助一下。off

4、set函數(shù)可以利用偏移量得到新的引用。范圍根據(jù)選擇按鈕的鏈接單元格m3為基礎(chǔ)， 用 choose 函數(shù)對(duì)其進(jìn)行選擇置頂區(qū)域范圍。后面的counta 函數(shù)同樣通過(guò)按鈕鏈接選擇組別，并對(duì)其計(jì)數(shù)。通過(guò)對(duì)名稱(chēng)的設(shè)置，就能很好的為圖表服務(wù)，動(dòng)態(tài)的體現(xiàn)數(shù)據(jù)的意義。當(dāng)然也可以通過(guò)其他函數(shù)來(lái)實(shí)現(xiàn)同樣的效果，本文下面有此圖表應(yīng)用附件，如有興趣歡迎交流。多坐標(biāo)軸圖表讓數(shù)據(jù)更生動(dòng)在 excel 中，我們常常用到圖表。因?yàn)樗梢灾庇^的讓數(shù)據(jù)更生動(dòng)，更容易讓人記住。直接生成的圖表往往生硬，不合我們想表達(dá)的內(nèi)容。有時(shí)候就需要做點(diǎn)小改變了，本博文簡(jiǎn)單的介紹下多坐標(biāo)軸的妙用。讓數(shù)據(jù)更生動(dòng)。數(shù)據(jù)源如下：直接根據(jù)數(shù)據(jù)源創(chuàng)建出來(lái)的

5、圖表如下。可見(jiàn)1 處顯示的內(nèi)容根本無(wú)法表達(dá)ab組的對(duì)比情況，因此點(diǎn)擊2處的“ 切換行 / 列”。得到圖表如下，但可以看到由于只有一個(gè)縱坐標(biāo)軸，發(fā)貨次數(shù) 明細(xì)超越 發(fā)貨延時(shí) 很多，無(wú)法顯示延時(shí)的真實(shí)情況。故此選中圖表中發(fā)貨延時(shí)ab組數(shù)據(jù)，然后按鍵盤(pán)“ctrl+1 ”彈出“ 設(shè)置數(shù)據(jù)系列格式”窗口。選中“系列選項(xiàng)”，分別對(duì)主次坐標(biāo) 進(jìn)行“ 分隔 ”、“ 無(wú)間距 ”進(jìn)行設(shè)置。設(shè)置完成后得到下圖，ab小組的發(fā)貨次數(shù)及發(fā)貨延時(shí)對(duì)比圖表就這樣完成了。如果覺(jué)得圖表有點(diǎn)單調(diào)的話(huà)，那你可以選擇你喜歡的“圖表樣式 ”、“ 設(shè)置圖表區(qū)格式”進(jìn)行美化，最終得到下面的圖表。統(tǒng)計(jì)時(shí)間出現(xiàn)次數(shù)某表內(nèi)有電話(huà)撥打時(shí)間，需要統(tǒng)計(jì)

6、8:00 至 11:59 、12:00 至 16:59 、17:00 至 7:59 這三個(gè)時(shí)間段的撥打次數(shù)填入 d2至 d4單元格內(nèi)。此時(shí)我們b2單元格，輸入公式：=hour(a2) 。然后下拉填充至單元格a15，建立輔助列。然后在 d2中輸入公式：=countif($b$2:$b$15,<12)-countif($b$2:$b$15,<7)8:00 至 11:59 12 點(diǎn)前的撥打次數(shù)減去小于7 點(diǎn)的撥打次數(shù)，得到6 次。d3中輸入公式：=countif($b$2:$b$15,<17)-countif($b$2:$b$15,<12)12

7、:00 至 16:59 17 點(diǎn)前撥打的次數(shù)減去小于12 點(diǎn)的次數(shù)，得到5 次。d4中輸入公式：=countif($b$2:$b$15,<24)-countif($b$2:$b$15,<17)+countif($b$2:$b$15,<8)17:00 至 7:59 24 點(diǎn)前撥打的次數(shù)減去小于17 點(diǎn)的次數(shù)，加上0 點(diǎn)至 7 點(diǎn)撥打的次數(shù)，得到3 次。vlookup 函數(shù)簡(jiǎn)單應(yīng)用“合并計(jì)費(fèi)”2011-01-24 00:23:29標(biāo)簽： vlookupexcel用戶(hù)原創(chuàng)作品，允許轉(zhuǎn)載，轉(zhuǎn)載時(shí)請(qǐng)務(wù)必以超鏈接形式標(biāo)明文章原始出處、作者信息和本聲明。否則將追究法

8、律責(zé)任。http:/ excel 報(bào)表，含每月出賬用戶(hù)。如圖，每個(gè)月用戶(hù)不同。需要將用戶(hù)合計(jì)入“合計(jì)”表內(nèi)按每月排列。這里先將用戶(hù)名全部復(fù)制在“總計(jì)”表“用戶(hù)名”下，然后選擇“數(shù)據(jù)”選項(xiàng)卡中“數(shù)據(jù)工具”，“刪除重復(fù)項(xiàng)”。得到所有出賬用戶(hù)名。然后使用vlookup 函數(shù)在 b2單元格中輸入“=vlookup($a2,1月!a1:b4,2,false)”，在 c2單元格中輸入 “=vlookup($a2,2月!a1:b4,2,false)” ， 在 d2單元格中輸入 “=vlookup($a2,3月!a1:b4,2,false)” ，向下拖動(dòng)得到每月出賬金額。這里出現(xiàn)的 #n/a，表示該函數(shù)中每月

9、可用數(shù)值。因?yàn)橛行┯脩?hù)在某個(gè)月無(wú)金額產(chǎn)生。那么為了便于計(jì)算及美觀。我們稍稍修改一下：=if(iserror(vlookup($a2,1 月!a:b,2,false),vlookup($a2,1月!a:b,2,false) 還有很多方法可用實(shí)現(xiàn)上述結(jié)果，甚至更簡(jiǎn)單。此處為了演示vlookup 函數(shù)而用，歡迎大家一起交流。excel 中 index 和 match 函數(shù)的應(yīng)用實(shí)例2010-10-04 20:41:27標(biāo)簽： 函數(shù) 實(shí)例excel indexmatch原創(chuàng)作品，允許轉(zhuǎn)載，轉(zhuǎn)載時(shí)請(qǐng)務(wù)必以超鏈接形式標(biāo)明文章原始出處、作者信息和本聲明。否則將追究法律責(zé)任。http:/ 中常被用到的一種函數(shù)

10、，本篇來(lái)介紹一下index 與 match 在實(shí)際工作中的應(yīng)用實(shí)例。先看一下這個(gè)excel 工作簿。要求：將“用戶(hù)分析”工作表中機(jī)房名稱(chēng) 列中輸入函數(shù)，向下拖動(dòng)使其自動(dòng)選擇對(duì)應(yīng)“號(hào)段檢索”工作表中備注 的機(jī)房名稱(chēng)。其中故障號(hào)碼為“號(hào)段檢索”表中起始、結(jié)束號(hào)段中的碼號(hào)。因此這里需要利用index 與 match 函數(shù)來(lái)完成檢索號(hào)段歸屬機(jī)房查詢(xún)工作。想到了 index 與 match 函數(shù)了吧，可以先回顧一下。- -index- index 函數(shù)的意義：返回指定行列交叉處引用的單元格。公式： =index(reference,row_num,column_num,area_num) referen

11、ce指的是要檢索的范圍；row_num指的是指定返回的行序號(hào)，如超出指定檢索范圍，返回錯(cuò)誤值#ref!;column_num 指的是指定返回的列序號(hào)，如超出指定檢索范圍，返回錯(cuò)誤值#ref!;area_num 指的是返回該區(qū)域中行和列的交叉域?？墒÷?，默認(rèn)1。如小于1時(shí)返回錯(cuò)誤值#value!- -match - match 函數(shù)的意義：返回指定方式下查找指定查找值（可以是數(shù)字、文本或邏輯值）在查找范圍1 行或 1列的位置。公式： =match(lookup_value,lookup_array,match_type) lookup_value指指定查找值；lookup_array指的是 1

12、行或 1 列的被查找連續(xù)單元格區(qū)域。match_type 指的是查找方式，1 或省略指查找小于或等于lookup_value的最大值， lookup_array必須為升序排列，否則無(wú)法得到正確結(jié)果。0 指查找等于lookup_value的第一個(gè)數(shù)值，如果不是第一個(gè)數(shù)值則返回#n/a-1 指查找大于或等于lookup_value的最小值， lookup_array必須為降序，否則無(wú)法得到正確結(jié)果。- - 那么在這里是用match 函數(shù)來(lái)定位“用戶(hù)分析”表中故障號(hào)碼 在“號(hào)段檢索” 起始號(hào)段 或結(jié)束號(hào)段 的所在行序號(hào)。如下圖： =match( 用戶(hù)分析 !k2, 號(hào)段檢索 !b:b,1)。但是為什

13、么檢索出來(lái)的行號(hào)會(huì)是錯(cuò)誤值呢？別忘了， match_type 為-1 時(shí)， lookup_array必須為升序排列，也就是“號(hào)段檢索”表中起始號(hào)段 應(yīng)按升序排序。ok ，這次查詢(xún)正確，也可以抽一個(gè)驗(yàn)證一下無(wú)誤即可。最后 index 函數(shù)利用match 定位的行序號(hào)，來(lái)引用“號(hào)段檢索”表中該行序號(hào)所在備注 的機(jī)房名稱(chēng)。輸入公式如下：index(號(hào)段檢索 !a:g,match(用戶(hù)分析 !k2, 號(hào)段檢索 !b:b,1),6)。最后向下拖動(dòng)公式，完成機(jī)房名稱(chēng) 檢索。在實(shí)際應(yīng)用中可能常用的是精確查找，但是如果match 函數(shù)需要應(yīng)用-1 和 1 時(shí)，特別要注意lookup_array的排序問(wèn)題。ex

14、cel 中多條件查詢(xún)函數(shù)公式2010-07-25 12:34:12標(biāo)簽： 函數(shù) 查詢(xún)excel 公式條件收集一下，以備不時(shí)只需。=vlookup(g2&h2,if(1,0,a2:a7&c2:c7,b2:b7),2,) =vlookup($g$2&$h$2,choose(1,2,3,$a$2:$a$7&$c$2:$c$7,$b$2:$b$7,$d$2:$d$7),column(b1),) =vlookup(h2,if(a2:a7=g2,c2:d7,0),2,) =hlookup(g2&h2,if(1;0,transpose(a2:a7&c2:c7)

15、,transpose(b2:b7),2,) =hlookup($g2&$h2,choose(1;2;3,transpose($a2:$a7&$c2:$c7),transpose($b2:$b7),transpose($d2:$d7),column(b1),) =hlookup(h2,transpose(if(a2:a7=g2,c2:d7,0),2,) =lookup(match(g2&h2,a2:a7&c2:c7,),row(1:6),b2:b7) =lookup(1,0/(a2:a7&c2:c7=g2&h2),b2:b7) =lookup(2,

16、1/(a2:a7&c2:c7=g2&h2),b2:b7) =lookup(2,1/(a2:a7=g2)*(c2:c7=h2),b2:b7) =index(b2:b7,match(g2&h2,a2:a7&c2:c7,) =index(b2:b7,match(1,(a2:a7=g2)*(c2:c7=h2),) =index(b2:b7,match(true,a2:a7&c2:c7=g2&h2,) =index(b2:b7,max(if(a2:a7&c2:c7=g2&h2,row(1:6) =choose(match(g2&h2

17、,a2:a7&c2:c7,),b2,b3,b4,b5,b6,b7) =choose(match(1,(a2:a7=g2)*(c2:c7=h2),),b2,b3,b4,b5,b6,b7) =choose(match(true,a2:a7&c2:c7=g2&h2,),b2,b3,b4,b5,b6,b7) =choose(max(if(a2:a7&c2:c7=g2&h2,row(1:6),b2,b3,b4,b5,b6,b7) 利用 excel 函數(shù)偷懶2010-04-21 23:19:35標(biāo)簽： 函數(shù) exceloffice2010偷懶最近真的很忙， 那些遠(yuǎn)程

18、工單平臺(tái)的系統(tǒng)非常的老，可能有 8 年了吧。 但神奇的是它們的生命力異常強(qiáng)大，至今依然在服役。雖然它們老了，許多事情已經(jīng)跟不上時(shí)代也力不從心了，但是工作還是要做的。要不它們?cè)趺磥?lái)養(yǎng)活這個(gè)公司大家庭呢。所以很多時(shí)候許多數(shù)據(jù)都需要人工來(lái)統(tǒng)計(jì)，這個(gè)是非?？膳碌氖虑?。為了不讓大家陷入人工手動(dòng)統(tǒng)計(jì)工作的汪洋大海中，我不得不找了個(gè)函數(shù)使其中一個(gè)需要手動(dòng)查詢(xún)地址并按地址進(jìn)行分類(lèi)的excel 表格變?yōu)樽詣?dòng)。這個(gè)表格我隱藏和摘取了部分?jǐn)?shù)據(jù)。需要填寫(xiě)的就是根據(jù)“聯(lián)系地址”然后再填寫(xiě)每個(gè)地址所在的公司劃分的“區(qū)域”，黃色部分。這些表格數(shù)據(jù)原本應(yīng)該由系統(tǒng)完成，可惜系統(tǒng)玩不轉(zhuǎn)，沒(méi)有數(shù)據(jù)庫(kù)維護(hù)人員還是什么其他原因。我們只

19、能完全采用手工輸入的方式，費(fèi)時(shí)又費(fèi)力，實(shí)在是個(gè)頭痛的工作。如果我們只是抱怨，那痛苦的依然還是自己。怎么辦呢？哎，人是要學(xué)會(huì)偷懶的啦。函數(shù)是死的但人是活的。光會(huì)一個(gè)個(gè)死記硬背的函數(shù)是不行的，要學(xué)會(huì)聯(lián)想。找到每個(gè)所需數(shù)據(jù)的特點(diǎn)性格，然后讓其對(duì)號(hào)入座。區(qū)域的劃分主要是根據(jù)具體的路名，這個(gè)路名屬于哪個(gè)區(qū)域就是哪個(gè)區(qū)域。在這個(gè)表格中“博興路、滬東新村、楊高北路、加太路、浙橋路、金新路、川橋路、新金橋”這些是我們需要的重點(diǎn)內(nèi)容。因此需要采用函數(shù)mid來(lái)找出這些路名。如下圖，s列的內(nèi)容就是根據(jù)mid（l2，5，3）得出的。最后摘取的是3 個(gè)字符，所以“楊高北路”得出的是“楊高北”，不過(guò)不影響我們最終需要的結(jié)

20、果。有了 s列做輔助我們就成功了一小半。如下圖所示，還需要一個(gè)單元格做所有路名的集中存放點(diǎn)。并且將路名有意識(shí)的按照區(qū)域順序進(jìn)行排放，另外還對(duì)于不同區(qū)域的路名進(jìn)行了字體設(shè)置。這樣我們就能夠更容易的區(qū)分他們了。我們將綠色為“a區(qū)域”，紅色為“b區(qū)域”，黑色為“c區(qū)域”。為什么這樣做呢？呵呵，相信有些朋友已經(jīng)猜到了。首先在t 列中用 search （s2，$u$1)函數(shù)讓剛才s列摘取的路名對(duì)u1單元格進(jìn)行搜索，將得出s列中路名在u1中的字符位置。最后根據(jù)所在字符的位置來(lái)判斷屬于哪個(gè)區(qū)域。下圖中t列得到的就是該結(jié)果。然后我們將輔助的s列、 t 列中的內(nèi)容帶入if 函數(shù)中 if(t28,t216,c區(qū)域

21、 , 未知區(qū)域 )。 測(cè)試一下，果然需要的區(qū)域名稱(chēng)就可以自動(dòng)得出了。為了是頁(yè)面更加整潔，最后我們可以將輔助列中的內(nèi)容帶入最終的if 函數(shù)中，if(search(mid(l2,5,3),$u$1)8,search(mid(l2,5,3),$u$1)16,c區(qū)域 , 未知區(qū)域 )。輔助列也完成了它們的使命，可以刪除了。如下圖最終“區(qū)域”內(nèi)容就會(huì)自動(dòng)根據(jù)“聯(lián)系地址”進(jìn)行更改。免去了我們手動(dòng)輸入的苦惱。excel 橫向查找函數(shù)2010-03-15 16:04:53標(biāo)簽： 函數(shù) excel原創(chuàng)作品，允許轉(zhuǎn)載，轉(zhuǎn)載時(shí)請(qǐng)務(wù)必以超鏈接形式標(biāo)明文章原始出處、作者信息和本聲明。否則將追究法律責(zé)任。http:/ 中

22、應(yīng)用較多，收集了一部分做個(gè)記錄。歡迎補(bǔ)充。呵呵。excel 動(dòng)態(tài)圖表應(yīng)用2010-02-02 00:23:22標(biāo)簽： exceloffice2010原創(chuàng)作品，允許轉(zhuǎn)載，轉(zhuǎn)載時(shí)請(qǐng)務(wù)必以超鏈接形式標(biāo)明文章原始出處、作者信息和本聲明。否則將追究法律責(zé)任。http:/ ”，用自定義名稱(chēng)配合窗口控件 來(lái)展現(xiàn)動(dòng)態(tài)圖表。本例依然采用 excel2010beta 。數(shù)據(jù)內(nèi)容如下：最終我們利用窗口控件達(dá)到的效果如下：首先要將橫向的“第一周”, “第四周”轉(zhuǎn)換成縱向的數(shù)據(jù)，便于之后數(shù)據(jù)引用。這里用到的函數(shù)是=transpose(b1:e1) ，輸入按ctrl+shift+enter完成轉(zhuǎn)制。第二步，“插入”圖表，并適當(dāng)調(diào)整位置，為該圖表創(chuàng)建標(biāo)題。“開(kāi)發(fā)工具”選項(xiàng)卡“插入”“選項(xiàng)按鈕”和“組合框”窗體控件。將這兩個(gè)窗體控件放置圖表適當(dāng)位置，并對(duì)選項(xiàng)按鈕重新命名。然后在“公式”選項(xiàng)卡中選擇“名稱(chēng)管理器”來(lái)新建之后將要使用的各個(gè)自定義名稱(chēng)。創(chuàng)建以下名稱(chēng)，并在引用位置內(nèi)輸入各自定義名稱(chēng)的定義。設(shè)定名稱(chēng)管理器完成后，在圖表中選擇“選項(xiàng)按鈕”右鍵“設(shè)置控件格式”。選擇“控制”，在單元格鏈接內(nèi)輸入$h$1，兩個(gè)“選項(xiàng)按鈕”都做如此操作。右鍵“組合框” 控件后， 選擇“控制” 在數(shù)據(jù)源區(qū)域內(nèi)輸入“下拉列表” 名稱(chēng)，并在單元格鏈接內(nèi)輸入$h$2?，F(xiàn)在你就可以根據(jù)控件選擇具體“區(qū)域”