（办公常用表格）Excel可视化图表模板017（可编辑）.xlsx

《（办公常用表格）Excel可视化图表模板017（可编辑）.xlsx》由会员分享，可在线阅读，更多相关《（办公常用表格）Excel可视化图表模板017（可编辑）.xlsx（65页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Excel Datavis Contest - Chandoo.org - 2014ContestContest RulesRulesMake a dashboard or set of charts.Help us understand state to state migration trends and interesting factsUse any standard Excel feature (no add-ins, no Power Pivot etc.)You can use Macros & VBA to add interaction to charts. (Cod

2、e must be commented clearly and sources should be credited)You can add additional demographic or economic data as long as,You use publicly available data sourcesYou provide links to the data sources and credit themExamples: Percaptia income, weather or living conditionsYou may ignore the Margin of E

3、rror data (provided in extreme right in the data tables). You may submit more than one entry. For voting & selecting winners only 1 entry from each individual is considered.ImportantImportant DatesDatesContest start date9th of April, 2014Last date for submission20th20th ofof April,April, 2014201

4、4Deadline passed!HowHow toto submitsubmit youryour entry?entry?EmailEmailSend to: Subject: Datavis contest 2014Include: Your workbook, Your name, Email Address & Version of Excel used to make itFacebookFacebookJoin chandoo.org fan page (Click here)Send your entry as a message (with workbook as a

5、ttachment)Include: Your workbook, Your name, Email Address & Version of Excel used to make itWhatWhat dodo youyou get?get?First prizeiPad (Latest version) 16 GBSecond prizeiPad Mini (latest version) 16 GBThird prize (2 nos)Samsung Galaxy Tab 3HowHow winnerswinners areare selectedselectedWinners

6、will be selected by voting and a panel of judges (to be revealed). If there is a tie, Judges decision will be given more weightage.FineFine PrintPrintPrizes will be delivered thru A. If you are located in a country where shipping Amazon goods is not possible (orprohibitively expensive) you will be o

7、ffered a choice of either getting money payment or purchase from a local website.You also agree that Chandoo.org reserves the right to showcase your files, share them, use them in courses & trainingprograms or any other publication as deemed fit. You agree to share the copy-right of your workboo

8、k with Chandoo.orgindefinetly for no compensation.Chandoo.org may get in touch with you if we need any clarifications or we are unable to use your file.Chandoo.org reserves the right to disqualify a contest entry based on sole discretion.For updates on rules, dates & policies, please click below

9、 link:http:/chandoo.org/wp/2014/04/09/visualize-state-migration-contest/Visit Contest WebsiteVisit Chandoo.orgExcel Datavis Contest - Chandoo.org - 2014You can use Macros & VBA to add interaction to charts. (Code must be commented clearly and sources should be credited)You may ignore the Margin

10、of Error data (provided in extreme right in the data tables). You may submit more than one entry. For voting & selecting winners only 1 entry from each individual is considered.Deadline passed!Include: Your workbook, Your name, Email Address & Version of Excel used to make itInclude: Your wo

11、rkbook, Your name, Email Address & Version of Excel used to make itWinners will be selected by voting and a panel of judges (to be revealed). If there is a tie, Judges decision will be given more weightage.Prizes will be delivered thru A. If you are located in a country where shipping Amazon goo

12、ds is not possible (orprohibitively expensive) you will be offered a choice of either getting money payment or purchase from a local website.You also agree that Chandoo.org reserves the right to showcase your files, share them, use them in courses & trainingprograms or any other publication as d

13、eemed fit. You agree to share the copy-right of your workbook with Chandoo.orgindefinetly for no compensation.Chandoo.org may get in touch with you if we need any clarifications or we are unable to use your file.Chandoo.org reserves the right to disqualify a contest entry based on sole discretion.Da

14、taData visualizationvisualization ofof thethe internalinternal migrationmigration inin thethe U.S.U.S. fromfrom 20102010 toto 20122012Prepared by: Jorge SupelanoExcel Version: 2013Select a year20122012.and see the general trendsSelect a StateAlabamaAlabama.and see the trend for 2012 in that statePop

15、ulationPopulation decreaseddecreased 0.1%0.1%TopTop 5 5 statesstates representrepresent 51.9%51.9% ofof allall thethe arrivalsarrivalsTopTop 5 5 statesstates representrepresent 54.7%54.7% ofof allall thethe departuresdeparturesNote: Due to Margin of Errors (which were ignored), some numbers may not

16、add upTotal MovementsMost and least active statesCaliforniaFloridaSouth DakotaVermontCombined arrivals and departuresArrivalsDepartures1 10 08 8, ,8 82 23 3 1 10 05 5, ,5 56 65 5 ( (8 88 8, ,9 97 79 9) )( (1 13 35 5, ,8 81 11 1) )Net MigrationMost active statesFloridaTexasNew JerseyNew York0.1904397

17、705544930.1074952198852770.1007552581261950.0713957934990440.0490726577437859People arrived from.Top 5 statesGeorgia Florida Tennessee Texas North Carolina 25,000 20,000 15,000 10,000 5,000 0 0.1703049171321310.1269480816775020.1109422214082960.09150260965113080.0470744437322589People departed to.To

18、p 5 statesFlorida Georgia Tennessee Texas Mississippi 20,000 15,000 10,000 5,000 0 Out of 310 million people in the U.S in 2012,roughly 7 million (or 2.3%) , settled in a new stateand 38 million (or 12.2%) moved within their state1723117655465Movements within a stateMost and least active statesNevad

19、aArizonaNew YorkNew Jersey% of total state population2.1% 1.1% (1.0%)(7.0%)Where the impact is bigger% of state population gained and lostNorth DakotaDelawareNew JerseyAlaskaI In nt te er rn na al l m mi ig gr ra at ti io on n5 59 90 0, ,3 32 26 6 1 12 2% %S Se et tt tl le er rs s f fr ro om m o ot

20、th he er r s st ta at te es s2 2% %Same residence 1 year agoLoss4,610G Ga ai in n0 0Arrivals and DeparturesArrivedDepartedGeneralGeneral trendstrends2012CounterTotal pop310,212,7551Same residence 1 year ago263,612,59685.5%2Different residence, same state 1 year ago 37,696,59712.2%3Intrastate migrati

21、on7,070,3452.3%4308,379,538100.0%56Out of 310 million people in the U.S in 2012,7roughly 7 million (or 2.3%) , settled in a new state8and 38 million (or 12.2%) moved within their state9101112131415161718192021222324252627282930313233343536373839404142434445464748495051AlphabeticalAlphabetical datada

22、taStatesArrivalsDepartures Net migration Pop 1 yr and overInternal migrationAlabama104,600109,210(4,610)4,764,428590,326Alaska33,41584,068(50,653)721,18690,613Arizona232,457206,84225,6156,468,907953,789Arkansas76,94864,96711,9812,912,680373,046California493,641566,986(73,345)37,572,7385,046,618Color

23、ado205,060161,53043,5305,123,944751,921Connecticut80,31187,023(6,712)3,555,319334,918Delaware34,75725,1499,608906,57686,003District of Columbia 53,83059,513(5,683)624,84761,992Florida537,148428,325108,82319,114,6202,380,288Georgia277,466252,26225,2049,796,5471,236,302Hawaii55,14561,509(6,364)1,374,8

24、52134,827Idaho59,28355,1914,0921,573,036210,151Illinois208,755277,953(69,198)12,725,1191,441,191Indiana134,137144,597(10,460)6,457,067805,228Iowa75,76073,3252,4353,035,469362,938Kansas88,28493,134(4,850)2,848,708381,695Kentucky112,787103,0049,7834,328,626521,511Louisiana91,21595,956(4,741)4,545,9145

25、28,406Maine27,52338,574(11,051)1,315,586151,438Maryland154,983157,664(2,681)5,816,472549,973Massachusetts142,577158,156(15,579)6,580,641626,380Michigan133,981175,733(41,752)9,778,9801,268,105Minnesota101,042115,946(14,904)5,315,228653,012Mississippi73,50066,9476,5532,947,696339,807Missouri162,930142

26、,75420,1765,951,913801,093Montana37,69033,8323,858995,544126,463Nebraska43,26648,816(5,550)1,829,420237,937Nevada124,28598,88225,4032,725,280481,496New Hampshire50,48438,69611,7881,309,203125,118New Jersey130,223219,202(88,979)8,772,744655,465New Mexico54,69363,921(9,228)2,060,595226,243New York270,

27、053405,864(135,811)19,352,1531,723,117North Carolina273,149238,66334,4869,640,4901,149,080North Dakota38,21323,95914,254689,83884,294Ohio196,391199,202(2,811)11,414,6351,440,815Oklahoma108,972102,5726,4003,762,311531,347Oregon118,925108,18210,7433,857,465560,673Pennsylvania215,500237,156(21,656)12,6

28、30,0821,252,378Rhode Island33,44630,4982,9481,040,527101,165South Carolina156,705127,41829,2874,668,886564,350South Dakota26,05122,5343,517821,669115,606Tennessee177,098163,84313,2556,378,278783,077Texas507,752402,187105,56525,711,7913,656,070Utah87,87082,1655,7052,805,440373,980Vermont24,43120,0564

29、,375620,22461,242Virginia250,653238,54012,1138,085,389915,242Washington215,494180,46235,0326,815,763904,695West Virginia47,12547,425(300)1,837,518174,112Wisconsin99,19297,7241,4685,660,677693,737Wyoming31,14932,228(1,079)569,73477,324GainsGains andand losseslosses (absolute(absolute andand relative)

30、relative)States rankedNet mig - rankedABSPop 1 yr and over%Florida108,823 108,82319,114,6200.6%Texas105,565 105,56525,711,7910.4%Colorado43,53043,5305,123,9440.8%Washington35,03235,0326,815,7630.5%North Carolina34,48634,4869,640,4900.4%South Carolina29,28729,2874,668,8860.6%Arizona25,61525,6156,468,

31、9070.4%Nevada25,40325,4032,725,2800.9%Georgia25,20425,2049,796,5470.3%Missouri20,17620,1765,951,9130.3%North Dakota14,25414,254689,8382.1%Tennessee13,25513,2556,378,2780.2%Virginia12,11312,1138,085,3890.1%Arkansas11,98111,9812,912,6800.4%New Hampshire11,78811,7881,309,2030.9%Oregon10,74310,7433,857,

32、4650.3%Kentucky9,7839,7834,328,6260.2%Delaware9,6089,608906,5761.1%Mississippi6,5536,5532,947,6960.2%Oklahoma6,4006,4003,762,3110.2%Utah5,7055,7052,805,4400.2%Vermont4,3754,375620,2240.7%Idaho4,0924,0921,573,0360.3%Montana3,8583,858995,5440.4%South Dakota3,5173,517821,6690.4%Rhode Island2,9482,9481,

33、040,5270.3%Iowa2,4352,4353,035,4690.1%Wisconsin1,4681,4685,660,6770.0%West Virginia(300)3001,837,518(0.0%)Wyoming(1,079)1,079569,734(0.2%)Maryland(2,681)2,6815,816,472(0.0%)Ohio(2,811)2,81111,414,635(0.0%)Alabama(4,610)4,6104,764,428(0.1%)Louisiana(4,741)4,7414,545,914(0.1%)Kansas(4,850)4,8502,848,7

34、08(0.2%)Nebraska(5,550)5,5501,829,420(0.3%)District of Columbia (5,683)5,683624,847(0.9%)Hawaii(6,364)6,3641,374,852(0.5%)Connecticut(6,712)6,7123,555,319(0.2%)New Mexico(9,228)9,2282,060,595(0.4%)Indiana(10,460) 10,4606,457,067(0.2%)Maine(11,051) 11,0511,315,586(0.8%)Minnesota(14,904) 14,9045,315,2

35、28(0.3%)Massachusetts(15,579) 15,5796,580,641(0.2%)Pennsylvania(21,656) 21,65612,630,082(0.2%)Michigan(41,752) 41,7529,778,980(0.4%)Alaska(50,653) 50,653721,186(7.0%)(300)(1,079)1,468 2,435 (2,681)(2,811)Most Stable statesNet MigrationWest VirginiaWyomingWisconsinIowaMarylandOhioInternal migrationIl

36、linois(69,198) 69,19812,725,119(0.5%)California(73,345) 73,34537,572,738(0.2%)New Jersey(88,979) 88,9798,772,744(1.0%)New York(135,811)135,81119,352,153(0.7%)Absoute gains and lossesRelative gain and lossesRelative gains and lossesMost activeNorth Dakota2.1%Florida108,823Delaware1.1%North DakotaTexa

37、s105,565Nevada0.9%DelawareNew Hampshire0.9%New Jersey(88,979)Colorado0.8%New JerseyNew York(135,811)Vermont0.7%AlaskaSouth Carolina0.6%Florida0.6%Most stablesWashington0.5%West Virginia(300)South Dakota0.4%Wyoming(1,079)Arkansas0.4%Wisconsin1,468Texas0.4%Iowa2,435Arizona0.4%Maryland(2,681)Montana0.4

38、%Ohio(2,811)North Carolina0.4%Missouri0.3%Rhode Island0.3%Oregon0.3%Idaho0.3%Georgia0.3%Kentucky0.2%Mississippi0.2%Tennessee0.2%Utah0.2%Oklahoma0.2%Virginia0.1%Iowa0.1%Wisconsin0.0%West Virginia(0.0%)Ohio(0.0%)Maryland(0.0%)Alabama(0.1%)Louisiana(0.1%)Indiana(0.2%)Kansas(0.2%)Pennsylvania(0.2%)Conne

39、cticut(0.2%)Wyoming(0.2%)California(0.2%)Massachusetts(0.2%)Minnesota(0.3%)Nebraska(0.3%)Michigan(0.4%)New Mexico(0.4%)Hawaii(0.5%)Illinois(0.5%)New York(0.7%)(300)(1,079)1,468 2,435 (2,681)(2,811)Most Stable statesNet MigrationWest VirginiaWyomingWisconsinIowaMarylandOhioMaine(0.8%)District of Colu

40、mbia (0.9%)New Jersey(1.0%)Alaska(7.0%)ActivityActivity indexindex (total(total movements)movements) - - migrationmigration betweenbetween statesstatesStates -alphabetically Total movementsStates - rkdAlabama213,810California2.1%Alaska117,483Florida1.1%Arizona439,299TexasArkansas141,915New York(1.0%

41、)California1,060,627Georgia(7.0%)Colorado366,590North CarolinaConnecticut167,334VirginiaDelaware59,906IllinoisDistrict of Columbia 113,343PennsylvaniaFlorida965,473ArizonaGeorgia529,728WashingtonHawaii116,654OhioIdaho114,474ColoradoIllinois486,708New JerseyIndiana278,734TennesseeIowa149,085MarylandK

42、ansas181,418MichiganKentucky215,791MissouriLouisiana187,171MassachusettsMaine66,097South CarolinaMaryland312,647IndianaMassachusetts300,733OregonMichigan309,714NevadaMinnesota216,988MinnesotaMississippi140,447KentuckyMissouri305,684AlabamaMontana71,522OklahomaNebraska92,082WisconsinNevada223,167Loui

43、sianaNew Hampshire89,180KansasNew Jersey349,425UtahNew Mexico118,614ConnecticutNew York675,917IowaNorth Carolina511,812ArkansasNorth Dakota62,172MississippiOhio395,593New MexicoOklahoma211,544AlaskaOregon227,107HawaiiPennsylvania452,656IdahoRhode Island63,944District of Columbia South Carolina284,12

44、3West VirginiaSouth Dakota48,585NebraskaTennessee340,941New HampshireTexas909,939MontanaUtah170,035MaineVermont44,487Rhode IslandVirginia489,193WyomingRelative gains and lossesWashington395,956North DakotaWest Virginia94,550DelawareWisconsin196,916South DakotaWyoming63,377VermontTotal movements - rk

45、dMost and least stable states1,060,627965,473ArrivalsDeparturesTotal909,939California493,641566,98601,060,627675,917Florida537,148428,3250965,473529,728511,812South Dakota26,05122,534048,585489,193Vermont24,43120,056044,487486,708452,656439,299395,956395,593366,590349,425340,941312,647309,714305,684

46、300,733284,123278,734227,107223,167216,988215,791213,810211,544196,916187,171181,418170,035167,334149,085141,915140,447118,614117,483116,654114,474113,34394,55092,08289,18071,52266,09763,94463,377ActivityActivity indexindex (total(total movements)movements) - - migrationmigration betweenbetween stat

47、esstates62,17259,90648,58544,487InternalInternal MigrationMigrationStates -alphabeticallyTotal movementsPop 1 yr and over%Ranked states and %Alabama590,3264,764,42812.4%NevadaAlaska90,613721,18612.6%ArizonaArizona953,7896,468,90714.7%ColoradoArkansas373,0462,912,68012.8%OregonCalifornia5,046,61837,5

48、72,73813.4%TexasColorado751,9215,123,94414.7%OklahomaConnecticut334,9183,555,3199.4%South DakotaDelaware86,003906,5769.5%WyomingDistrict of Columbia 61,992624,8479.9%MissouriFlorida2,380,28819,114,62012.5%CaliforniaGeorgia1,236,3029,796,54712.6%KansasHawaii134,8271,374,8529.8%IdahoIdaho210,1511,573,

49、03613.4%UtahIllinois1,441,19112,725,11911.3%WashingtonIndiana805,2286,457,06712.5%NebraskaIowa362,9383,035,46912.0%MichiganKansas381,6952,848,70813.4%ArkansasKentucky521,5114,328,62612.0%MontanaLouisiana528,4064,545,91411.6%OhioMaine151,4381,315,58611.5%GeorgiaMaryland549,9735,816,4729.5%AlaskaMassa

50、chusetts626,3806,580,6419.5%IndianaMichigan1,268,1059,778,98013.0%FloridaMinnesota653,0125,315,22812.3%AlabamaMississippi339,8072,947,69611.5%MinnesotaMissouri801,0935,951,91313.5%TennesseeMontana126,463995,54412.7%WisconsinNebraska237,9371,829,42013.0%North DakotaNevada481,4962,725,28017.7%South CarolinaNew Hampshire125,1181,309,2039.6%Kentu