1、多角度分析數(shù)據(jù)
2、更改值得匯總依據(jù)
3、排序讓數(shù)據(jù)更加直觀
4、原來手工也能排序
5、對銷售額進(jìn)行排名
6、篩選銷售額前5名客戶
一個優(yōu)秀的財會人,是可以熟練操作Excel的財會人,至少是能做透視表的財會人。小編應(yīng)熱衷進(jìn)步的讀者的要求,整理了制作透視表的動圖教程,簡單直觀,還不快轉(zhuǎn)發(fā),收藏:
▎本文來源中國黑客聯(lián)盟,由財會學(xué)堂整理發(fā)布,轉(zhuǎn)載請注明公眾號來源。關(guān)注財會學(xué)堂(caikuaixuetang),每天學(xué)點(diǎn)財會知識。
什么是動態(tài)圖表?就是可以根據(jù)選項(xiàng)變化,生成不同數(shù)據(jù)源的圖表。
高頓財務(wù)培訓(xùn)《EXCEL在財務(wù)管理中的高級應(yīng)用》
先睹為快:
動態(tài)圖表一般是函數(shù)公式配合窗體控件來完成的,下面跟高頓網(wǎng)校小編一起制作吧。
材料: 一張分月份的銷售報表
制作步驟:
1、添加輔表并設(shè)置公式
公式:
=INDEX(C3:G3,C$9)
2、根據(jù)輔助表添加圖表
3、添加下拉選擇框。
開發(fā)工具 - 插入 - 點(diǎn)擊組合框,拖放到圖表上部。
(2010開發(fā)工具選項(xiàng)卡添加方法:文件 - 選項(xiàng) - 自定義功能區(qū) - 選取開發(fā)工具,2007在選項(xiàng)窗口的常用里)
4、設(shè)置填充的內(nèi)容和鏈接單元格。
在K列輸入1月,2月..數(shù)據(jù)源區(qū)域選取該區(qū)域。單元格鏈接選取C9,將來在下拉框選取時,會把選取的數(shù)字輸入到C9里(選取第幾個選項(xiàng)就輸入數(shù)字幾)
設(shè)置完成后,就可以實(shí)現(xiàn)本文開頭的動態(tài)圖表效果了。
▎本文作者excel第一教室。來源“財會學(xué)堂”。轉(zhuǎn)載或引用請尊重原創(chuàng)注明來源。
一般的Excel用戶中,能了解frequency函數(shù)用法的人很少,因?yàn)檫@個函數(shù)教程少,理解起來有點(diǎn)困難。但它卻是Excel高手的最愛,但為有些Excel問題非它不可。例如數(shù)據(jù)的最大連續(xù)個數(shù)問題。
先了解一下FREQUENCY函數(shù)的統(tǒng)計(jì)原理。
FREQENCY(數(shù)據(jù)區(qū)域,用于設(shè)置區(qū)間分隔點(diǎn)的數(shù)組)
作用:
簡單的說就是在設(shè)置好的各個數(shù)值區(qū)間內(nèi),存在幾個數(shù)。(頻率分布)
【例】:A列有35,45,68,78,75,90,要分別計(jì)算 0~60、61~80、80以上這三個區(qū)域分別有幾個數(shù)。公式就可以這樣寫。
=FREQUENCY(A1:A6,B1:B2)
分析:
雖然我們要設(shè)置三個區(qū)域,我們只需要設(shè)置兩個數(shù)即可,即60和80,它就代表著0~60 、61~80、大于80三個區(qū)間。也就是說FREQUENCY上函數(shù)返回的結(jié)果總比第二個參數(shù)的數(shù)字個數(shù)大1個。
注意:
因?yàn)檫@個是一個數(shù)組函數(shù),所以在輸入時要選取大于分段點(diǎn)的一個的單元格數(shù)量,輸入公式,按CTRL+SHIFT+ENTER結(jié)束輸入。
了解了FREQUENCY的計(jì)算原理,下面我們看一下它在計(jì)算最大連續(xù)數(shù)中的應(yīng)用。
【例】如下圖所示,B列中含1的表示張三的已簽到,留空的為未簽到。要求計(jì)算張三最大連續(xù)簽到的次數(shù)。
解題過程及思路:
第一步:從上圖我們可以看到,各個連續(xù)的1被空行分隔成了多個獨(dú)立的連續(xù)區(qū)域。要想計(jì)算連續(xù)1的個數(shù),我們先要判斷空行的位置。即:
第二步:從第一步的結(jié)果,我們發(fā)現(xiàn),我們要想計(jì)算每一個連續(xù)1區(qū)域的個數(shù),就演變成了3之前有幾個數(shù),4~8之間有幾個數(shù),8~11之間有幾個數(shù)。這不正好是FREQUENCY計(jì)算根據(jù)分段點(diǎn)計(jì)算各個分段區(qū)間的個數(shù)模式了嗎?。分段點(diǎn)就是這些空行的序數(shù),分段數(shù)據(jù)源是1~12的連續(xù)數(shù)字。如下圖所示:
使用FREQUEN計(jì)算后的結(jié)果如下圖所示。
注意:由于FREQUENCY函數(shù)分段時,下面分界點(diǎn)要算一個,如:4~8之間的數(shù)會被FREQUENCY函數(shù)計(jì)算成8-4=4個數(shù),而我們不需要包含分界點(diǎn)的個數(shù),即我們需要的是8-4-1=3個。所以最終的結(jié)果我們計(jì)算最大值后再減1,才是我們所要的結(jié)果。
最終的公式為:
=MAX(FREQUENCY(ROW(1:12),(B2:B13<>1)*ROW(1:12)))-1
作者:錢糧胡同
更新:謝謝大家的支持。第五步顏色漏寫了命名方法,已補(bǔ)充。如果還有問題可以參看問答答案或這篇文章的評論區(qū),里面的討論基本可以解決大部分人遇到的問題。再次抱歉,08年做的,這次直接放上知乎,漏了一步,確屬粗心,記性差,檢討。
看了知乎上大部分關(guān)于如何用excel做數(shù)據(jù)地圖的回答,感覺最費(fèi)勁的地方都在地圖素材上,有用多邊形描出來的(有點(diǎn)兒累),有推薦excel插件的(不喜歡依賴插件或軟件包),這樣多多少少缺少了彈性(當(dāng)然你也可以網(wǎng)上找一個人家做好的)。我個人比較喜歡從零DIY,學(xué)會了整套方法,以后遇到任何問題都可以解決。
正好有串子在我的一些回答中問過我,我的那些熱感地圖怎么做出來的。借這個機(jī)會,我毅然決定,專寫一篇文章來說說我的數(shù)據(jù)地圖是怎么畫的。
很簡單,按照我的方法,六步你就可以畫出自己的熱感地圖,不需要GIS軟件,各種地圖都適用。
前提:你要有Excel這個軟件啊(否則搞毛),還要能上網(wǎng)啊(能看到這回答說明正在上網(wǎng)吧?)
第一步:需要一張普通的地圖圖片
這個很簡單,網(wǎng)上到處都有。中國的省級地圖,地級市地圖,甚至縣級地圖都可以找到。這里兩個小要求:
1)盡量找張干凈的圖,干凈指的是地圖的邊界清晰;
2)svg格式的圖(Scalable Vector Graphics)- 別慌,推薦你個下載svg格式地圖模板的好去處: Wikimedia Commons
在這個網(wǎng)站搜索China provinces,可以得到下面這些圖(上百張),第一張圖就很符合我們的要求:
第二步:打開下載的svg圖。這里推薦個免費(fèi)開源的軟件:Inkscape。用這個軟件打開svg的地圖,然后另存為Enhanced Meta File(emf)格式的文件。
第三步:用excel導(dǎo)入emf的文件,然后ungroup這個地圖變成freeform的圖形,如下圖。
這樣我們就得到了excel里面的地圖素材。
第四步:每個省現(xiàn)在對應(yīng)的名字都沒有,我們需要重新命名。簡單的方法:鼠標(biāo)點(diǎn)擊選一個省,在左上角公式旁邊的文本框里輸入省的名字,如下圖:
第五步:在excel里另建一個worksheet,按照下圖來設(shè)計(jì)數(shù)據(jù)版面:
更新:F列每個顏色的單元格漏寫了對應(yīng)的命名,比如白色的單元格,將其命名為對應(yīng)的J列的值(classpro0)
漸變顏色可以自己選擇,目前這個例子是從0到5六種類別(顏色),根據(jù)你的研究需要(比如各省人均收入水平你按照高低分了10類,那就10種),隨便改多少種都可以,唯一注意的就是命名的區(qū)域要跟著變。再啰嗦一下:根據(jù)你自己的分類標(biāo)準(zhǔn),篩選你的數(shù)據(jù),將篩選后的分類結(jié)果(比如6種 0-5)對應(yīng)到之前提到的類別中,這樣也就對應(yīng)好了顏色。
第六步:這些都做完了,在地圖那頁worksheet里面加個按鈕,把下面這段VBA程序copy進(jìn)去:
補(bǔ)充說明:Data_Province!D中Data_Province是數(shù)據(jù)表(worksheet)的名字,D指的是省份名字所在的列(見上面那張截圖)。根據(jù)自己的需要可以改成你喜歡的名字或位置。
第七步:這步不算了啊!就是六步~
點(diǎn)擊按鈕,享受成果,不謝!
把剛才的地圖換成紫色漸變色,效果如下:
我過去的研究用過的一些樣圖(ps: 數(shù)據(jù)原因,有些圖未包含臺灣):
地級市層面經(jīng)濟(jì)集群圖,請參考:中國是否真的需要均衡發(fā)展? - 錢糧胡同的回答
神經(jīng)網(wǎng)絡(luò)算法分類中國地級市,請參考:機(jī)器學(xué)習(xí)(machine learning)在經(jīng)濟(jì)學(xué)領(lǐng)域是否有應(yīng)用前景? - 錢糧胡同的回答
同樣方法制作縣級地圖
如果制作過程中遇到問題,請留言。
via:zhihu
文章來源36大數(shù)據(jù),www.36dsj.com ,微信號dashuju36 ,36大數(shù)據(jù)是一個專注大數(shù)據(jù)創(chuàng)業(yè)、大數(shù)據(jù)技術(shù)與分析、大數(shù)據(jù)商業(yè)與應(yīng)用的網(wǎng)站。分享大數(shù)據(jù)的干貨教程和大數(shù)據(jù)應(yīng)用案例,提供大數(shù)據(jù)分析工具和資料下載,解決大數(shù)據(jù)產(chǎn)業(yè)鏈上的創(chuàng)業(yè)、技術(shù)、分析、商業(yè)、應(yīng)用等問題,為大數(shù)據(jù)產(chǎn)業(yè)鏈上的公司和數(shù)據(jù)行業(yè)從業(yè)人員提供支持與服務(wù)。
大數(shù)據(jù)到底離我們有多遠(yuǎn)? 大數(shù)據(jù)能給我們帶來什么?
帶著這些問題,我們開始進(jìn)入今天的大數(shù)據(jù)分析入門,首先我要說的是:大數(shù)據(jù)與我們每個人的生活和工作,創(chuàng)業(yè),投資等等密切相關(guān),基于大數(shù)據(jù)上的分析,可以讓我們不再猶豫,茫然,會時刻對自己要做的,應(yīng)做的,時機(jī)與機(jī)會的把握都剛剛好。
如果你想升職,如果你想做成功的生意,都離不開大數(shù)據(jù)的思維模式,如果你之前從沒有接觸過這些,那么今天就是一個好的機(jī)會,首先讓我們進(jìn)入下面的案例:
背景介紹:
小C是一個技術(shù)人員,做計(jì)算機(jī)工作很多年,最近他想辭職創(chuàng)業(yè),下海,首先想到的就是,做老本行,在網(wǎng)上發(fā)布自己的視頻課程,比如JAVA如何學(xué)習(xí)的課程,但是他拿不定注意,他不確定,這樣的想法是否正確,比如錄制了JAVA到底有沒有人買,定價多少錢,等等。
解決思路:
從網(wǎng)上找相關(guān)的資料,比如去有視頻課程的網(wǎng)站,搜索信息,看看別人都定什么價格,到底銷量如何?
遇到的問題:
搜集的信息都太雜,太多,比如價格,價格很多種,每種都有銷售數(shù),而且課程類別很多,從事這方面的機(jī)構(gòu)和個人也很多了,小C經(jīng)過幾天搜集信息,發(fā)現(xiàn)自己更迷茫了,因?yàn)楦杏X還是沒有對這個行業(yè)有整體的認(rèn)識,比如風(fēng)險,機(jī)會,等等,還是感覺很迷茫。
另外也耽誤了很多時間,因?yàn)樗鸭畔ⅲ芾?,看信息也很累,感覺信息量太多,不知道如何處理這些信息,很難得到些比較明確的判斷。
上面小C的情況,我想現(xiàn)實(shí)中,大家都遇到過,比如求職,創(chuàng)業(yè),求醫(yī)等等,在遇到迷茫的時候,不知道怎么做的時候,請看下面的完整解決方案:
步驟一: 采集數(shù)據(jù),從互聯(lián)網(wǎng)上,采集時效性強(qiáng)的數(shù)據(jù)
這個過程需要借助工具,因?yàn)槟阆氲玫浇Y(jié)論,就必須抽取大量的數(shù)據(jù)做樣本,而這個過程,如果靠人力手動來做,是無法想想的。
比如這里我可以采集到小C關(guān)心的所有相關(guān)課程的數(shù)據(jù),比如價格,銷量,發(fā)布單位或個人,發(fā)布時間等等。
步驟二 ,數(shù)據(jù)導(dǎo)入與清洗
這一步看上去,好像很難,其實(shí)很簡單,對于普通人來說,你要處理的數(shù)據(jù)所謂的大,相對企業(yè)數(shù)據(jù)來說也是很小的,這樣小容量的數(shù)據(jù),你只需要導(dǎo)入到EXCEL文件就可以。
因?yàn)樵诘谝徊轿覀円呀?jīng)把數(shù)據(jù)導(dǎo)出成了EXCEL兼容的TXT文本文件,這一步,只需要在EXCEL里找到相關(guān)的數(shù)據(jù)導(dǎo)入功能 按鈕,直接導(dǎo)入我們的數(shù)據(jù)就可以。
導(dǎo)入以后,我們需要對數(shù)據(jù)進(jìn)行清晰,例如剛開始的時候,我們看到導(dǎo)入后的數(shù)據(jù)是這樣的:
其實(shí)對數(shù)據(jù)清洗的目的就是讓數(shù)據(jù)分析的時候更流暢,比如這里的第一列,就需要用查找,替換功能,把索引號前面的紀(jì)錄時間:【16:35:44】這些內(nèi)容刪除,而第三列的人這個字也應(yīng)該刪除,而且需要把1萬換成10000,而最后一列的免費(fèi)應(yīng)該換成0,通過上面的數(shù)據(jù)清洗,就可以為下步的數(shù)據(jù)分析做好鋪墊了,如果數(shù)據(jù)清晰的工作量和復(fù)雜度很高,可以借助EXCEL VBA編程的方式來處理,VBA的編程,我前面的文章和視頻都有介紹,大家可以參考。
步驟三,對清洗后的數(shù)據(jù)進(jìn)行數(shù)據(jù)透視表
數(shù)據(jù)透視分析是進(jìn)行數(shù)據(jù)分析的基礎(chǔ),但是數(shù)據(jù)透視分析可以滿足普通人,甚至一般企業(yè)分析任務(wù)的百分之九十以上的需求,數(shù)據(jù)透視表操作不是一篇文章可以介紹完的,如果你沒有基礎(chǔ),可以找這些方面的資料看看,我這里也有些資料,可以微信我elearn007.
步驟四,輸出報表
經(jīng)過數(shù)據(jù)透視表分析后,我們可以輸出類似下面的幾個基礎(chǔ)性報表,這些報表完全支持小C進(jìn)行正確的決策了:
這些都是很不好的消息,因?yàn)檫@說明開始的時候,有報名的人學(xué)習(xí)課程,但是至少最近1個月,幾乎沒有報名的(最近報名是最近1個月內(nèi)的報名數(shù),而購買人數(shù)則是累計(jì)的總量,如果最近報名人數(shù)為0,則顯示購買人數(shù))。
上面的圖表也反映了剛才的現(xiàn)象,因此可以做出這樣的結(jié)論小C想在網(wǎng)上退出視頻課程進(jìn)行銷售,在JAVA技術(shù)這塊,不成熟,因?yàn)檎n程有百分之九之八都是免費(fèi)的,只有很少的是收費(fèi)課程,而且最近幾乎都沒人光顧購買。
因此小C做網(wǎng)上視頻課程錄制,并銷售的想法,目前看時機(jī)不成熟,沒有嘗試的價值,如果他想短期有收入的話,再進(jìn)一步,小C辭職做,就更不可取了。
通過剛才的例子,大家應(yīng)該對數(shù)據(jù)分析的過程有了大概的了解,其實(shí)普通人,用到的數(shù)據(jù)量是很小的,因此普通人完全可以用大數(shù)據(jù)的思維,來解決自己現(xiàn)實(shí)里遇到的各種問題。
對了,如果需要累計(jì)歷史數(shù)據(jù),比如小C想分析,一個季度內(nèi),技術(shù)課程的銷售量,和課程量的增長情況,那么他需要每天采集數(shù)據(jù),并把數(shù)據(jù)保存起來,所以如果我們想用大數(shù)據(jù)為工具,我們還需要數(shù)據(jù)的保存與分發(fā)平臺,其實(shí)也有免費(fèi)的,就是liferay,這個我的文章里也做了介紹,大家有空可以看看。
如果你對大數(shù)據(jù)分析,對創(chuàng)業(yè),對營銷感興趣,可以加微信elearn007讓我們一起交流吧。
文/胡定祥
Excel分列功能非常強(qiáng)大,不僅僅可以將單元格數(shù)據(jù)拆分成多列,而且還可以將利用列數(shù)據(jù)格式來規(guī)范數(shù)據(jù)。
一、將單列單元格數(shù)據(jù)輕松拆分成多列
Excel分列主要功能就是將單列數(shù)據(jù)拆分成多列,它提供兩種方式,第一種利用“分隔符號”進(jìn)行拆分;第二種就是利用“固定寬度”進(jìn)行拆分。
1.利用“固定寬度”進(jìn)行拆分單元格
下圖的數(shù)據(jù)是從網(wǎng)絡(luò)拷貝過來的,現(xiàn)在所有的數(shù)據(jù)都放在了A列,我們?nèi)绾螌⑦@些數(shù)據(jù)按照不同的字段拆分成多列呢?下面我們先利用“固定寬度”的方式來進(jìn)行拆分。
1
選擇“固定寬度”
在需要的位置上單擊以建立分列線
不需要的數(shù)據(jù)可直接選中,然后單擊“不導(dǎo)入此列(跳過)“忽略掉,另外可選擇分列后數(shù)據(jù)的存放位置
拆分完畢,完全按照不同的字段排列了
2.利用“分隔符號”進(jìn)行拆分
要求同上,下面我們來演示如何利用“分隔符號”進(jìn)行同樣的拆分。所謂“分隔符號”,通常包括了空格,符號,以及文字。我們通過觀察發(fā)現(xiàn),此栗中利用“空格”這個分隔符號來進(jìn)行拆分可以達(dá)到需要的而結(jié)果。
1
2
勾選“連續(xù)分隔符號視為單個處理”這點(diǎn)非常重要
位置可選擇放在它處
殊途同歸,也很方便
二、輕松規(guī)范日期格式
通常在工作中,關(guān)于日期格式最為常見的錯誤有下圖中的4種,這些錯誤會導(dǎo)致excel表格無法統(tǒng)計(jì)?,F(xiàn)在我們利用分列功能就可以輕輕松松解決這些問題了。
很眼熟吧
對于錯誤日期一, 很多同學(xué)都可以輕松解決。直接選中這列數(shù)據(jù),Ctrl+H打開替換對話框,查找對話框類輸入英文半角句號“.",替換框內(nèi)填入”-",單擊全部替換即可。此種錯誤日期格式利用分列來做會非常復(fù)雜。
1.利用分列轉(zhuǎn)換錯錯誤日期2 和錯誤日期3
1
2
3
4
5
對于6位日期,操作方法同上面是一樣的。
2.利用“分列”轉(zhuǎn)換錯誤日期4
由于各個國家的習(xí)慣不同,有些國家習(xí)慣的日期是月日年。但這種日期格式Excel是沒法做統(tǒng)計(jì)的,因此我們需要將這樣的日期轉(zhuǎn)換成Excel可識別的樣式。
1
2
3
這一步根據(jù)原始日期的格式進(jìn)行選擇。這里是月日年,即MDY
5
三、輕松轉(zhuǎn)換單元格格式的技巧
1.巧妙將文本型數(shù)字轉(zhuǎn)換成數(shù)字型格式
文本型數(shù)字是無法求和的,因此在遇到需要統(tǒng)計(jì)的時候,通常需要將文本型數(shù)字轉(zhuǎn)換成數(shù)字型數(shù)字,方法很多,今天我們利用分列來達(dá)成這一效果。
1
2
3
2.巧妙去除單元格中的多余空格
在Excel中,空格很不受待見,那么如何去除單元格的多余空格呢?下面我們來演示分列的方法。
軟件中導(dǎo)出的Excel文件尤其需要去除類似的空格
忽略空格那一列
最終效果
分享完畢。
手機(jī)銷量和利潤統(tǒng)計(jì)表
如果用圖表展示出來,一般情況下是用柱形圖來展示的。
是不是感覺有些單調(diào),還不是太直觀。
其實(shí),表示這種三維關(guān)系用泡泡圖是最好的選擇。先展示一下效果:
泡泡的銷量用Y軸大小表示,而利潤的大小則用泡泡的大小表示。如iphone6的銷售雖不如 華為mate8 銷售高,但從泡泡大小看利潤卻比前者大。
制作方法:
步驟1:選取A:C的數(shù)據(jù),插入 - 其他圖表 - 氣泡圖。
步驟2:雙擊X軸,設(shè)置X軸最大值為8(數(shù)據(jù)為8行),最小值為0,然后刪除X軸。
步驟3:添加數(shù)據(jù)標(biāo)志,顯示X和Y值。
步驟4:顯示網(wǎng)格線并把網(wǎng)格線設(shè)置為灰色,再設(shè)置氣泡的填充色。最后把字體設(shè)置成微軟雅黑。
設(shè)置基本完成,其他細(xì)節(jié)同學(xué)們可以根據(jù)自已喜好再調(diào)整。
圖表的另類展示往往很容易讓閱讀的人第一時間獲取到相應(yīng)的信息,因此制作差異化圖表的技能是很有必要的,今天分享瀑布圖制作,過程比較簡單,但是會制作的人也比較少。
下面是我們想要達(dá)到的效果:
制作過程:
附加解釋說明:
1、關(guān)鍵其實(shí)就是添加輔助列,然后使用堆積柱形圖。輔助列的數(shù)值是總和逐一減去各值(也就是圖上面的各柱形圖之間首尾相連接效果)。
2、過程中有直接在公式中調(diào)整了數(shù)據(jù)順序,實(shí)際上等同于在“右鍵-->選擇數(shù)據(jù)”中調(diào)整系列值的順序:
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$7,Sheet1!$B$2:$B$7,2)。
本文出自微信公眾號:高級辦公技能之家,轉(zhuǎn)載請注明出處。同時非常歡迎轉(zhuǎn)發(fā),若您還未關(guān)注微信公眾號,請搜索關(guān)注。
一、什么是回歸分析法
“回歸分析”是解析“注目變量”和“因于變量”并明確兩者關(guān)系的統(tǒng)計(jì)方法。此時,我們把因子變量稱為“說明變量”,把注目變量稱為“目標(biāo)變量址(被說明變量)”。
清楚了回歸分析的目的后,下面我們以回歸分析預(yù)測法的步驟來說明什么是回歸分析法:
1.根據(jù)預(yù)測目標(biāo),確定自變量和因變量明確預(yù)測的具體目標(biāo),也就確定了因變量。如預(yù)測具體目標(biāo)是下一年度的銷售量,那么銷售量Y就是因變量。通過市場調(diào)查和查閱資料,尋找與預(yù)測目標(biāo)的相關(guān)影響因素,即自變量,并從中選出主要的影響因素。
2.建立回歸預(yù)測模型依據(jù)自變量和因變量的歷史統(tǒng)計(jì)資料進(jìn)行計(jì)算,在此基礎(chǔ)上建立回歸分析方程,即回歸分析預(yù)測模型。
3.進(jìn)行相關(guān)分析什么是回歸分析法回歸分析是對具有因果關(guān)系的影響因素(自變量)和預(yù)測對象(因變量)所進(jìn)行的數(shù)理統(tǒng)計(jì)分析處理。只有當(dāng)變量與因變量確實(shí)存在某種關(guān)系時,建立的回歸方程才有意義。因此,作為自變量的因素與作為因變量的預(yù)測對象是否有關(guān),相關(guān)程度如何,以及判斷這種相關(guān)程度的把握性多大,就成為進(jìn)行回歸分析必須要解決的問題。進(jìn)行相關(guān)分析,一般要求出相關(guān)關(guān)系,以相關(guān)系數(shù)的大小來判斷自變量和因變量的相關(guān)的程度。
4.檢驗(yàn)回歸預(yù)測模型,計(jì)算預(yù)測誤差回歸預(yù)測模型是否可用于實(shí)際預(yù)測,取決于對回歸預(yù)測模型的檢驗(yàn)和對預(yù)測誤差的計(jì)算。回歸方程只有通過各種檢驗(yàn),且預(yù)測誤差較小,才能將回歸方程作為預(yù)測模型進(jìn)行預(yù)測。
5.計(jì)算并確定預(yù)測值利用回歸預(yù)測模型計(jì)算預(yù)測值,并對預(yù)測值進(jìn)行綜合分析,確定最后的預(yù)測值。
二回歸分析的目的
回歸分析的目的大致可分為兩種:
第一,“預(yù)測”。
預(yù)測目標(biāo)變量,求解目標(biāo)變量y和說明變量(x1,x2,…)的方程。
y=a0+b1x1+b2x2+…+bkxk+誤差(方程A)
把方程A叫做(多元)回歸方程或者(多元)回歸模型。a0是y截距,b1,b2,…,bk是回歸系數(shù)。當(dāng)k=l時,只有1個說明變量,叫做一元回歸方程。根據(jù)最小平方法求解最小誤差平方和,非求出y截距和回歸系數(shù)。若求解回歸方程.分別代入x1,x2,…xk的數(shù)值,預(yù)測y的值。
第二,“因子分析”。
因子分析是根據(jù)回歸分析結(jié)果,得出各個自變量對目標(biāo)變量產(chǎn)生的影響,因此,需要求出各個自變量的影響程度。
希望初學(xué)者在閱讀接下來的文章之前,首先學(xué)習(xí)一元回歸分析、相關(guān)分析、多元回歸分析、數(shù)量化理論I等知識。
根據(jù)最小平方法,使用Excel求解y=a+bx中的a和b。那么什么是最小平方法?
分別從散點(diǎn)圖的各個數(shù)據(jù)標(biāo)記點(diǎn),做一條平行于y軸的平行線,相交于圖中直線(如下圖)
平行線的長度在統(tǒng)計(jì)學(xué)中叫做“誤差”或者'殘差”。誤差(殘差)是指分析結(jié)果的運(yùn)算值和實(shí)際值之間的差。
接這,求平行線長度曲平方值??梢园哑椒街悼醋鲞呴L等于平行線長度的正方形面積(如下圖)
最后,求解所有正方形面積之和。確定使面積之和最小的a(截距)和b(回歸系數(shù))的值(如下圖)。
使用Excel求解回歸方程;“工具”→“數(shù)據(jù)分析”→“回歸”,具體操作步驟將在后面的文章中具體會說明。
三、回歸分析案例
接著上節(jié)的回歸分析的目的,我們來根據(jù)一個二手車的實(shí)例來繼續(xù)說回歸分析。
二手車價格的決定因素有:空調(diào)類型有無附加品(TV導(dǎo)航導(dǎo)航SR、天窗、空氣囊LD TV AW) 行駛距離,顏色車檢剩余有效月數(shù)、評分拍賣會地點(diǎn)等。
在這14個因子(說明變量)中,最影響價格(目標(biāo)變量)的是什么?最不影響價格的是什么?通過定量求出影響度,然后根據(jù)多個因子(說明變量)預(yù)測二手車價格(目標(biāo)變量)。
可以用方程2表示。二手車價格“這個目標(biāo)變量數(shù)據(jù),既(“空調(diào)類型(AC WC)” “有無TV導(dǎo)航”、 、“行駛距離”、 。車檢剩余有效月數(shù)”, “評分”)。
混合模型
混臺模型是指因子巾既包含定性數(shù)據(jù)也包含定量數(shù)據(jù)的模型。在混臺模型中.把“空調(diào)”、 “TV導(dǎo)航”等定性因子叫做項(xiàng)目,把數(shù)據(jù)群(空調(diào)的“AC”,“WAC”,TV導(dǎo)航的“有”、“無”)叫做類別。
接下來,根據(jù)表l進(jìn)行回歸分析。
這節(jié)我們主要告訴大家回歸分析前,我們需要先根據(jù)自己的思維來了解分析,把這些需要注意的先分析出來,這樣對我們接下來的回歸分析有很大的幫助。
四、Excel回歸分析需要注意的事項(xiàng)
經(jīng)過上節(jié),我們了解了回歸分析前,我們要先通過思維分析出來需要注意的事項(xiàng),那么今天接著上一節(jié)的課來了解下Excel回歸分析需要注意的事項(xiàng)。包含的定性數(shù)據(jù),不能直接使用Excel分析,需要將其轉(zhuǎn)換成虛擬變量(也叫O,1數(shù)據(jù))。例如, “空調(diào)(AC、WAC)”的數(shù)據(jù),“AC”用“1”,“WAC"用“O”表示。同樣地,“導(dǎo)航(有導(dǎo)航、無導(dǎo)航)”的數(shù)據(jù), “有導(dǎo)航”用“1”, “無導(dǎo)航”用“O”表示。表1是根據(jù)這種方法轉(zhuǎn)換的(0,1)數(shù)據(jù)表。
直接使用Excel的對表1進(jìn)行回歸分析時,運(yùn)算結(jié)果不理想。理由如下;
表1
以“導(dǎo)航”為例,各行
“有導(dǎo)航”+“無導(dǎo)航”=1
此式成立。把公式變形,
“有導(dǎo)航”=1-“無導(dǎo)航”
所以“有導(dǎo)航”是“0”或是“1”,由“無導(dǎo)航。自動決定。
線性代數(shù)中發(fā)生秩(矩陣秩)虧時,不能正確求出必要的逆矩陣。因此也不能求出回歸系數(shù)。
由于上述原因,進(jìn)行回歸分析時,需要從各個項(xiàng)目中刪除—列因子(表2)。
表2
根據(jù)表2的數(shù)據(jù)進(jìn)行回歸分析,操作步驟如下:
1、“工具”一“數(shù)據(jù)分析”
2、在彈出的“數(shù)據(jù)分析”對話框中選擇“回歸”,單擊“確定”(圖1)。
圖13、點(diǎn)擊“回歸”對話框的“Y值輸入?yún)^(qū)域”,選擇“二手車價格”的列數(shù)據(jù),包括項(xiàng)目名稱;接著點(diǎn)擊“X值輸入?yún)^(qū)域”,選擇從“AC”到“中國、四國、九州”的區(qū)域,包括項(xiàng)目名稱;選中。標(biāo)志”,單擊“確定”。(圖2)
圖2系統(tǒng)彈出錯誤信息,不能進(jìn)行回歸分析(圖3)。這是因?yàn)镋xcel回歸自由度的最大上限是16(P62小知識)。這里的回歸自由度是22,因此不能進(jìn)行回歸分析。
圖3
統(tǒng)計(jì)學(xué)中經(jīng)常出現(xiàn)“自由度”,即有效信息的數(shù)量。
前面已經(jīng)提到,在Excel的回歸分析中,回歸自由度的最大上限是16?;貧w自由度在(多重)回歸分析、數(shù)量化理論|、混合模型中具有不同意義。表3是對回歸自由度的不同意義的總結(jié)。
表3
五、分兩次進(jìn)行回歸分析
我們在前面提到過,當(dāng)回歸自由度在17以上時,Excel無法進(jìn)行回歸分析,那么就需要分兩次進(jìn)行回歸分析。第一次,把“空調(diào)”、“TV導(dǎo)航”、“導(dǎo)航”、“SR”、“天窗”、“空氣囊”、“LD”、“TV”、“AW”作為說明變量(表1),第二次,把“顏色”、“拍賣會地點(diǎn)”、“行駛距離”、“車檢剩余有效月數(shù)”、“評分”作為說明變量(表2),目標(biāo)變量都是“二手車價格”。
表1
表2對表1、表2進(jìn)行回歸分析?;貧w分析的結(jié)果分別如表3、表4所示(具體操作步驟將在下一節(jié)詳細(xì)說明)。
表3
表4
內(nèi)容來源:Excel學(xué)習(xí)網(wǎng)
【燈塔大數(shù)據(jù)】微信公眾號介紹:中國電信北京研究院通過大數(shù)據(jù)技術(shù)創(chuàng)新,自主研發(fā)了業(yè)內(nèi)領(lǐng)先的“燈塔”大數(shù)據(jù)行業(yè)應(yīng)用創(chuàng)新平臺,燈塔面向市場研究、廣告營銷、商業(yè)地理、金融征信、人力資源等諸多行業(yè)領(lǐng)域,提供零售研究、消費(fèi)者研究、店鋪選址、精準(zhǔn)營銷、泛義征信,背景調(diào)查等服務(wù),助力企業(yè)在大數(shù)據(jù)時代揚(yáng)帆遠(yuǎn)航。
花費(fèi)了一些時間,根據(jù)銷售表制作了一個感覺還算滿意的折線圖。如下圖所示:
可,你看了下面動態(tài)演示的Excel圖表效果,你還能滿意你上面的嗎?
嘿嘿,想學(xué)了吧!想學(xué)就跟小編一起學(xué)習(xí)制作方法吧。
制作步驟:
1、定義名稱。公式 - 定義名稱 - 在打開的定義名稱窗口中定義
XX =OFFSET(Sheet4!$A$2,,,Sheet4!$D$1,1)
YY =OFFSET(Sheet4!XX,,1)
注意:范圍選當(dāng)前工作表名稱
步驟2插入面積圖。插入 - 圖表 - 面積圖。步驟多看演示吧。
步驟3 插入滾動條控件。從開發(fā)工具選項(xiàng)卡 - 插入 - Activex控件中拖畫出滾動條控制,并進(jìn)行如下圖所示的幾項(xiàng)設(shè)置。
步驟4、文件另存為啟用宏的工作簿格式。
▎本文內(nèi)容來自Excel精英培訓(xùn),“財會學(xué)堂”。每天學(xué)點(diǎn)財會知識,讓你的職場up起來!歡迎關(guān)注微信號財會學(xué)堂(caikuaixuetang)