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

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

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

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

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

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

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

7、:00 至 16:59 17 點前撥打的次數(shù)減去小于12 點的次數(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 點前撥打的次數(shù)減去小于17 點的次數(shù)，加上0 點至 7 點撥打的次數(shù)，得到3 次。vlookup 函數(shù)簡單應(yīng)用“合并計費”2011-01-24 00:23:29標(biāo)簽： vlookupexcel用戶原創(chuàng)作品，允許轉(zhuǎn)載，轉(zhuǎn)載時請務(wù)必以超鏈接形式標(biāo)明文章原始出處、作者信息和本聲明。否則將追究法

8、律責(zé)任。http:/ excel 報表，含每月出賬用戶。如圖，每個月用戶不同。需要將用戶合計入“合計”表內(nèi)按每月排列。這里先將用戶名全部復(fù)制在“總計”表“用戶名”下，然后選擇“數(shù)據(jù)”選項卡中“數(shù)據(jù)工具”，“刪除重復(fù)項”。得到所有出賬用戶名。然后使用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)” ，向下拖動得到每月出賬金額。這里出現(xiàn)的 #n/a，表示該函數(shù)中每月

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

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

11、ce指的是要檢索的范圍；row_num指的是指定返回的行序號，如超出指定檢索范圍，返回錯誤值#ref!;column_num 指的是指定返回的列序號，如超出指定檢索范圍，返回錯誤值#ref!;area_num 指的是返回該區(qū)域中行和列的交叉域?？墒÷?，默認(rèn)1。如小于1時返回錯誤值#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必須為升序排列，否則無法得到正確結(jié)果。0 指查找等于lookup_value的第一個數(shù)值，如果不是第一個數(shù)值則返回#n/a-1 指查找大于或等于lookup_value的最小值， lookup_array必須為降序，否則無法得到正確結(jié)果。- - 那么在這里是用match 函數(shù)來定位“用戶分析”表中故障號碼 在“號段檢索” 起始號段 或結(jié)束號段 的所在行序號。如下圖： =match( 用戶分析 !k2, 號段檢索 !b:b,1)。但是為什

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

14、cel 中多條件查詢函數(shù)公式2010-07-25 12:34:12標(biāo)簽： 函數(shù) 查詢excel 公式條件收集一下，以備不時只需。=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ǒng)非常的老，可能有 8 年了吧。 但神奇的是它們的生命力異常強(qiáng)大，至今依然在服役。雖然它們老了，許多事情已經(jīng)跟不上時代也力不從心了，但是工作還是要做的。要不它們怎么來養(yǎng)活這個公司大家庭呢。所以很多時候許多數(shù)據(jù)都需要人工來統(tǒng)計，這個是非?？膳碌氖虑椤榱瞬蛔尨蠹蚁萑肴斯な謩咏y(tǒng)計工作的汪洋大海中，我不得不找了個函數(shù)使其中一個需要手動查詢地址并按地址進(jìn)行分類的excel 表格變?yōu)樽詣?。這個表格我隱藏和摘取了部分?jǐn)?shù)據(jù)。需要填寫的就是根據(jù)“聯(lián)系地址”然后再填寫每個地址所在的公司劃分的“區(qū)域”，黃色部分。這些表格數(shù)據(jù)原本應(yīng)該由系統(tǒng)完成，可惜系統(tǒng)玩不轉(zhuǎn)，沒有數(shù)據(jù)庫維護(hù)人員還是什么其他原因。我們只

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

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

21、 , 未知區(qū)域 )。 測試一下，果然需要的區(qū)域名稱就可以自動得出了。為了是頁面更加整潔，最后我們可以將輔助列中的內(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)容就會自動根據(jù)“聯(lián)系地址”進(jìn)行更改。免去了我們手動輸入的苦惱。excel 橫向查找函數(shù)2010-03-15 16:04:53標(biāo)簽： 函數(shù) excel原創(chuàng)作品，允許轉(zhuǎn)載，轉(zhuǎn)載時請務(wù)必以超鏈接形式標(biāo)明文章原始出處、作者信息和本聲明。否則將追究法律責(zé)任。http:/ 中

22、應(yīng)用較多，收集了一部分做個記錄。歡迎補(bǔ)充。呵呵。excel 動態(tài)圖表應(yīng)用2010-02-02 00:23:22標(biāo)簽： exceloffice2010原創(chuàng)作品，允許轉(zhuǎn)載，轉(zhuǎn)載時請務(wù)必以超鏈接形式標(biāo)明文章原始出處、作者信息和本聲明。否則將追究法律責(zé)任。http:/ ”，用自定義名稱配合窗口控件 來展現(xiàn)動態(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)題?！伴_發(fā)工具”選項卡“插入”“選項按鈕”和“組合框”窗體控件。將這兩個窗體控件放置圖表適當(dāng)位置，并對選項按鈕重新命名。然后在“公式”選項卡中選擇“名稱管理器”來新建之后將要使用的各個自定義名稱。創(chuàng)建以下名稱，并在引用位置內(nèi)輸入各自定義名稱的定義。設(shè)定名稱管理器完成后，在圖表中選擇“選項按鈕”右鍵“設(shè)置控件格式”。選擇“控制”，在單元格鏈接內(nèi)輸入$h$1，兩個“選項按鈕”都做如此操作。右鍵“組合框” 控件后， 選擇“控制” 在數(shù)據(jù)源區(qū)域內(nèi)輸入“下拉列表” 名稱，并在單元格鏈接內(nèi)輸入$h$2?，F(xiàn)在你就可以根據(jù)控件選擇具體“區(qū)域”