|
前 言 大家好，我是一丁老師。最近看了網(wǎng)上不少關于Excel知識小視頻，有一種沖動，就試著把這些年應用Excel知識的體會寫下來，與各個同仁分享。 本套課程作為一個完整的知識體系，適合操作Excel一年以上的辦公室人員學習，也就是說學習這套課程的朋友應該掌握了關于Excel的基本知識，至少包括熟悉Excel的基本界面、工具欄的基本操作如數(shù)據(jù)的輸入、復制、粘貼、篩選、匯總。 這套課程以WPS2019為操作平臺，并兼顧講解微軟表格在操作中的不同之處。我相信，如果能真正消化本套課程，扎實基礎，將有關Excel的知識串起來，能夠解決我們工作中遇到的至少80%與數(shù)據(jù)相關的問題。 為了便于學習和交流，特編寫了講義的wold版本，在wold版本中詳細地介紹了操作方法，大家可以下載學習。本講義分兩部分編寫，第一部分編寫了前10講，第二部分編寫了14講，共24講，并制作了24講的視頻講解資料。 第一講：Excel的主要功能和實現(xiàn)路徑概述 我們學習Excel，首先要知道Excel是干什么用的，如何才能實現(xiàn)這些功能，順著這個思路，能更好地將Excel的知識點串起來，避免知識的碎片化和學習的低效率。 Excel的主要功能和實現(xiàn)路徑為：
用Excel開發(fā)應用系統(tǒng)，就是用一套表格（包括若干個工作表）實現(xiàn)數(shù)據(jù)的錄入、傳遞、自動更新功能，最后形成我們所需要的表格或圖形。 一是設計應用系統(tǒng)的操作界面，這個界面應該簡潔、明晰，可理解性強，能夠?qū)崿F(xiàn)某種功能的自動進入和退出。 二是通過公式實現(xiàn)數(shù)據(jù)的計算、提取并能動態(tài)更新（或者說自動更新）。公式和數(shù)據(jù)導入能實現(xiàn)數(shù)據(jù)的動態(tài)更新（實際上公式就能實現(xiàn)數(shù)據(jù)的導入功能）；對于透視表，要通過刷新或者關閉文件之后再打開之后才能實現(xiàn)更新，不能算作完全的動態(tài)更新。 三是通過“鏈接”功能，實現(xiàn)表格跳轉(zhuǎn)。表格的跳轉(zhuǎn)，能夠?qū)⒃S多數(shù)據(jù)信息放在不同的工作表中，這樣把這些數(shù)據(jù)既獨立又聯(lián)系的組合為一個整體，能達到操作簡便，提高工作效率的目的。 四是通過Excel自帶的VBA宏語言，自行編寫和開發(fā)一些滿足自身管理需要的應用系統(tǒng)。開發(fā)Excel的應用系統(tǒng)中，大部分的都只需要應用Excel自身的功能，很少用到VBA宏語言，在此把它列為第四點。 任何自行編制的程序，都需要通過實際例子的檢驗，通過不斷的調(diào)整完善，才能成為真正可靠的工具。我們要盡可能地利用Excel自身的功能，來達到我們的目的。 為什么把Excel的主要功能和實現(xiàn)路徑作為第一講？Excel是一個軟件，我們要掌握一個軟件，首先是要了解這個軟件可以用來干什么？以及如何來實現(xiàn)這些功能。真正的關于Excel的教材，開始講的就是其功能。 任何一門知識，其功能和實現(xiàn)的路徑都是整個知識體系的“四梁八柱”的東西，就是這些梁和柱，將其具體的內(nèi)容編織在一起。作為一個學習Excel不深的人，看到我在這里講的功能和實現(xiàn)的路徑，會感到比較陌生，會覺得這個東西比較空泛，有些名詞還感覺枯燥難懂。當我們學完了這一整套知識，再回過頭來看這些東西的時候，應該有一種恍然大悟的感覺，會進一步加深對知識結(jié)構(gòu)和知識內(nèi)容的理解，達到知識系統(tǒng)化的目的。 第二講：基礎數(shù)據(jù)表的編制方法 基礎數(shù)據(jù)表的編制，是Excel中最基礎的工作，它就像是建筑的地基。在基礎數(shù)據(jù)錄入后，利用Excel自身功能管理這些數(shù)據(jù)時，應該會很方便，如果還要進行許多繁雜的操作，來滿足工作需要，其根源在于基礎數(shù)據(jù)表設計不合理，數(shù)據(jù)錄入不規(guī)范，這里用專門的一講來講基礎數(shù)據(jù)表的編制方法。 第一、Excel的“三表”概念 如果你有心的話，你會發(fā)現(xiàn)，Excel默認的新建工作簿時包含的工作表數(shù)量就是3?？梢钥闯?，其實“三表”這個概念，是被微軟Excel團隊承認的。 那這“三表”指的是哪三個表呢？ 他們是基礎數(shù)據(jù)表（數(shù)據(jù)源表）、參數(shù)表、報表，也有叫做記錄表、參數(shù)表、匯總表的，意思都是一樣的。 基礎數(shù)據(jù)表是記錄最基礎最原始數(shù)據(jù)用的。參數(shù)表（也就是下拉菜單）是用來提高基礎數(shù)據(jù)的錄入質(zhì)量用的，它能保證同一事項表述為同一字符。Excel識別的是字符，不同的字符，盡管其表達的含義相同，但會識別為不同的內(nèi)容，這樣會給我們分析匯總數(shù)據(jù)帶來影響。如“華中科技大學”和“華科”都用來表達同一所大學名稱的，但Excel會認為他們是兩所不同的大學。在基礎數(shù)據(jù)表中，特別是對分析數(shù)據(jù)有影響的字段，要使用下拉菜單的形式輸入。報表是基于基礎數(shù)據(jù)表而形成的分析結(jié)果。 在工作中，應將這三種不同功能的表格放在不同的工作表上，就是為了避免操作不同的表格時相互帶來負面影響，也讓我們查看不同的表格時感覺爽目。 我們在這里反復提到一個概念，就是“數(shù)據(jù)”，我們回顧一下這個重要概念。 在Excel中，數(shù)據(jù)包括：文字、數(shù)值、日期、時間、公式和函數(shù)，也就是說，Excel中的數(shù)據(jù)概念比起我們通常理解的數(shù)據(jù)概念，包括的內(nèi)容要廣得多。 就是數(shù)值這個概念也比我們通常理解要廣，其包括0到9組成數(shù)字和特殊字符：+，—，（），/，%，$，.，E，e中的任意字符。 第二、編制基礎數(shù)據(jù)表應遵循的幾個原則 第一個原則：一致性原則 一致性原則要求表格內(nèi)、表格之間的字段名稱、數(shù)據(jù)類型、表格結(jié)構(gòu)格式要保持一致，具體來講就是三個要求：同物同名稱，同表同格式、同列同格式。 同物同名稱，就是說同一對象要使用同一名稱，以便數(shù)據(jù)統(tǒng)計和表格間數(shù)據(jù)的引用。在“若航公司合同登記臺賬”表格中“安徽新世紀電子有限公司”與“新世紀電子有限公司”實際為同一公司，但寫成了兩種名稱，對于Excel來說就是兩家公司，在用數(shù)據(jù)透視表進行分類匯總時就會出現(xiàn)錯誤。 好用，Excel財務高手\全稿數(shù)據(jù)源\第1章\1.2\1.2.5掌握三類表格的設計要求\立航公司訂單統(tǒng)計.xlsx 同列同格式：同一列應該保持同一格式，不能某列的一些行設置成文本格式，其他行設置數(shù)值或者日期等格式。表格內(nèi)出現(xiàn)數(shù)據(jù)帶單位，計算時將作為文本數(shù)據(jù)被忽略，只有數(shù)據(jù)格式保持一致才能得出正確的計算結(jié)果。 我們通常意義上的數(shù)值，在Excel中既可以設置成數(shù)值，也可以設置為文本。表格的初始格式是將數(shù)值（也就是0到9組成的數(shù)字）設置為常量或數(shù)值，將非數(shù)值文本設置為文本，將標準的日期設置為日期格式。 同表同格式：相同的表格其表格結(jié)構(gòu)和格式必須保持一致，以方便公式或函數(shù)對數(shù)據(jù)進行管理。特別是同一類別的表格，由不同的部門或者人員統(tǒng)計填表時，必須制定統(tǒng)一的格式模板，方便最后數(shù)據(jù)的匯總。 好用，Excel財務高手\全稿數(shù)據(jù)源\第1章\1.2\1.2.3表格設計原則-統(tǒng)一意識\同列同格式.xlsx 第二個原則：數(shù)據(jù)矢量化和顆?；瓌t 矢量化：指的是如果數(shù)據(jù)中包含數(shù)量的含義，就應該盡量將這些含義具體量化，不要以文字或其他模糊的方式描述這些數(shù)量（比如半年、三個月、一倍都是錯誤示例），不能將數(shù)據(jù)的格式設置為文本。 顆?；褐傅氖敲總€單元格只存放單個有效數(shù)據(jù)，不要將數(shù)值與文字粘連。 非矢量化非顆?；臄?shù)據(jù)無法進行數(shù)據(jù)的統(tǒng)計和分析。 第三個原則：便于將表格區(qū)域轉(zhuǎn)換為“表”的原則 這里提出了一個概念，就是“表”，也有的稱為“超級表”，“表”和我們普通表比較起來，有許多優(yōu)點。關于“表”的知識，我們在下一講進行專門的講解。要將普通表轉(zhuǎn)換為“表”，需要遵循下列規(guī)定：
第四個原則：列字段要滿足我們統(tǒng)計分析的需要 一般地講，我們需要對什么內(nèi)容進行分析匯總，就將該內(nèi)容作為列字段的內(nèi)容。我們可以根據(jù)工作的需要添加列。 第五個原則：不要將基礎數(shù)據(jù)表分布在工作表中的不同區(qū)域 將基礎數(shù)據(jù)表分布在工作表中的不同區(qū)域，有時是這些區(qū)域隔行，有時是隔列，這樣不利于數(shù)據(jù)的分析和匯總，我們對這樣的表格進行適當?shù)母脑?，將分散的表統(tǒng)一到一個區(qū)域上來。（以2020年和2021年的21世紀不動產(chǎn)昌盛區(qū)域代理的工資表為例，進行講解） 02 優(yōu)居區(qū)域代理視頻素材改動版（2020年）.xlsx表12 在表12中，最后面的一個工資表添加了月份一列，就可以把全年的工資表放到一張工作表上了，這樣便于數(shù)據(jù)的分析和匯總，如對某個人全年的工資進行匯總，對各個月的工資發(fā)放情況進行比較等。如果要打印某個月的工資表，只需要篩選當月的月份，就可以打印某個月的工資表。
在實際工作中，我們的基礎數(shù)據(jù)表往往不是我們自己編制的表格，可能來源于其他人，也可能是從系統(tǒng)導出的，這些基礎數(shù)據(jù)往往不規(guī)則，需要我們對它進行整理。 其一、不規(guī)范文本的整理方法 不規(guī)范文本的表現(xiàn)形式有文本中含有空格、不可見字符、分行符號等。對于空格、不可見字符，是無法用眼睛觀察出來的，但在進行計算、查找、篩選時，會引發(fā)錯誤。 好用，Excel財務高手\全稿數(shù)據(jù)源\第3章\3.2\02 不規(guī)范文本的整理技巧.xlsx 一種方法是采用查找替換的方法，將空白符“ |”（即輸入空格鍵后，再輸入“|”，這個符號在backspace鍵的下面）替換為空白。 一種方法是采用函數(shù)的方法去掉空格符。Trim函數(shù)是去掉字符的尾部空白字符，substitute函數(shù)只能替換掉字符中的空格，要去掉單元格中的空格，需要將這兩個函數(shù)嵌套起來用，比較麻煩。 最優(yōu)的一種方法是將Excel表格粘貼到wold中去，然后再從wold中粘貼回來。Wold是不認可空格、不可見字符、分行符號的，我們利用這種功能能很好的處理這些不規(guī)則的字符。 其二、不規(guī)范日期的整理技巧 在Excel中必須按指定的格式輸入日期，Excel才會把其當作日期型數(shù)據(jù)，否則會理解為不可計算的文本，輸入以下4種日期格式，Excel均可識別： 一是以短橫線“-”分隔的日期，如“2017-4-1”、“2017-5-3”； 二是用斜杠“/”分隔的日期，如“2017/4/1”、“2017/5/3”； 三是中文年月日輸入的日期，如“2017年4月1日”、“2017年5月3日”： 四是使用包含英文月份或英文月份縮寫輸入的日期，如“April-1”、“May-17”。 其他符號間隔的日期或數(shù)字形式輸入的日期，如“2017.4.1”、“2017\4\1”、“20170103”等，Excel無法自動識別為日期數(shù)據(jù)，而將其視為文本數(shù)據(jù)。對于這種不規(guī)范的數(shù)據(jù)該如何處理？要根據(jù)具體情況來選擇不同的處理方法。 類似于“2017.4.1”、“2017\4\1”這類不規(guī)則的數(shù)據(jù)，我們可以通過使用查找和替換功能，將“.”或“\”替換為“-”或“/”即可。 類似于“20170103”這類數(shù)據(jù)日期可以使用分列功能快速批量處理。其操作步驟是，首先我們點擊菜單欄的“數(shù)據(jù)”，然后點擊“分列”，進入文本分列向?qū)υ捒?，選擇分隔符號，然后選擇“下一步”，在第二步中什么都不做，繼續(xù)點擊“下一步”，在第三步中將格式設置為日期格式，在日期格式后面有一個下拉菜單，我們在下拉菜單中選擇YMD（Y,M,D分別是年year月month日day英文單詞的第一個字母,表示按照年月日的順序進行排列），再點擊完成，即可。 下面我們舉個例子來說明分列的運用： 好用，Excel財務高手\全稿數(shù)據(jù)源\第3章\3.2\03 不規(guī)范日期的整理技巧.xlsx 值得注意的是，如果自定義單元格的格式為“0000-00-00”的方式，雖然我們顯示的形式是Excel認可的時間模式，如把19000102通過格式定義的操作變?yōu)椤?900-01-02”，但Excel不認可其為時間，它仍然把它看作是文本，是文本，就不能參與有關時間的計算，如計算年齡，提取出生年月，都無法實現(xiàn)。如果用手動的方式直接輸入1900-01-02，Excel會認可其為時間。 第三講：“表”（上） “表”的概念是全套課程中貫穿始終的，不管是基礎數(shù)據(jù)表、透視表還是圖表都與這個概念相關。將普通表轉(zhuǎn)換為“表”，不僅能夠起到美化表格的作用，還賦予它許多新的功能。
在Excel中，工作表、區(qū)域、“表”是三個不同的概念。 當我們新建立一個工作簿的時候，里面默認的電子表格是3個，并分別起名為sheet1、sheet2、sheet3這三個表，我們稱為工作表，它們是存放Excel數(shù)據(jù)的平臺。 區(qū)域，是指一個工作表上的一個范圍，這個范圍可能有數(shù)據(jù)，也可能沒有數(shù)據(jù)。 “表”，是將工作表中的一個填充了數(shù)據(jù)的區(qū)域設定為一個表，當Excel把一個區(qū)域認定一個“表”的時候，會賦予這個表許多功能。 第二、將一個區(qū)域轉(zhuǎn)換為一個“表”的方法 將一個區(qū)域認定為一個“表”的路徑為：點擊菜單欄的“開始”，再點擊其工具“表格樣式”，根據(jù)我們的喜好選定表格樣式，進入套用表格樣式對話框，在WPS表格中，在此對話框中，不能選擇“僅套用表格樣式”（這是原來對話框的默認選擇），而要選擇“轉(zhuǎn)換成表格，并套用表格樣式”，再點擊“確定”。這時在菜單欄中會有“表格工具”這個菜單，并在其相應的工具欄提供了許多功能。至此，表明我們將區(qū)域轉(zhuǎn)換為了“表”。 微軟表格中，在套用表格對話框中，沒有是否僅套用格式的選擇，直接點擊“確定”就可以了。這時菜單欄出現(xiàn)“表格設計”這個菜單。 將一個區(qū)域轉(zhuǎn)換為“表”以后，會給這個表取一個名稱，一般其初始名稱為“表1”、“表2”，這個名稱就代表這個區(qū)域。我們也可以更改這個名稱的名字。更改的方法是，點擊菜單欄的“表格工具”（WPS表格）或“表格設計”（微軟表格），再點擊其工具欄最左邊的“表名稱”下面的方框，就可以更改了。 對于已經(jīng)形成的“表”，“表格工具”下的功能鍵“鑲邊行”的作用是使相鄰的兩行數(shù)據(jù)顯示不同的顏色，“鑲邊列”的作用是使相鄰的兩列數(shù)據(jù)顯示不同的顏色，以增加閱讀性。對于微軟表格而言，這種功能更顯著，但對于WPS表格而言，主要通過表格樣式來實現(xiàn)這種功能，當我們的表格樣式是針對行標志不同的顏色，應該勾選“鑲邊行”，是否勾選“鑲邊列”的作用不大?；谠黾娱喿x性，應用“鑲邊行”或“鑲邊列”的功能，應該選擇表格樣式中顏色較深的樣式，這樣顏色對比度更高，閱讀爽目。 第三、將一個區(qū)域轉(zhuǎn)換為“表”的時應注意的問題
4、表中不能有斜線表頭和空白行、空白列。 第四講：“表”（下） 大家好，我是一丁老師。這一講，我們繼續(xù)講解“表”，或者說是超級表。在上一講，我們講了與“表”有關的三個問題，下面我們講解與表有關的第四個問題和第五個問題。 第四、“表”區(qū)別于“區(qū)域”的特色功能 “表”區(qū)別于“區(qū)域”具有許多特色功能。 銷售表（表和透視表）.xlsx 一是區(qū)域選擇和區(qū)域拓展功能。“表”不需要選擇區(qū)域，當我們把光標放在“表”中任一個單元格的時候，Excel默認為選擇全部區(qū)域；當我們不隔行的添加行或者不隔列的添加列時，“表”會自動地將這些行或列拓展為全部區(qū)域。只要我們在表格中相鄰列添加列字段，并按回車鍵，表格就會自動地向下添加一列。 “表”的自動區(qū)域拓展功能，極大地方便了我們的表格制作。我們在制作表格時，可以先畫一個行數(shù)和列數(shù)都很小的表，如4-5行和列，然后轉(zhuǎn)換為“表”，再根據(jù)需要直接不隔行地添加行或列，錄入數(shù)據(jù)即可。表格會自動地添加網(wǎng)格線（此時工具欄的鑲邊行，鑲邊列前面的小方框應打上√），自動地將新添加的行或列，設置成與前面格式相同的行或列。 采用插入的辦法在工作表的左邊添加列，“表”不會將左邊的插入列自動拓展為“表”的一部分。 二是同時提供篩選和排序功能。點擊列字段上的倒三角符號，在接下來的對話框中，這個對話框既提供了排序功能，又提供了篩選功能。 三是提供了便捷的統(tǒng)計功能。勾選“表格工具”下的功能鍵“匯總行”，會在表格底部加入一行，并且自動匯總出最右邊的數(shù)據(jù)，點擊底部匯總行的其他單元格，這些單元格的下拉菜單會提供“平均值”、“計數(shù)”、“求和”等功能，我們可以針對不同的列，選擇不同的匯總方式。 如果我們應用了篩選功能，匯總行可以統(tǒng)計出篩選后的數(shù)據(jù)匯總值。在“表”中執(zhí)行匯總的函數(shù)是SUBTOTAL讀作 [?s?b?t?ut?l] ，這個函數(shù)的意思是對可見單元格進行匯總，實際上就是對篩選出來的單元格進行匯總。 如果我們?nèi)サ魠R總行，繼續(xù)無間隔地（不能空行）向下添加行，“表”在執(zhí)行相關功能的時候，會把這些添加的部分，視為“表”的一部分。 四是上下并排的表可同時篩選排序，互不影響。如果在一個工作表里有兩個區(qū)域都有數(shù)據(jù)，而且把這兩個區(qū)域都變成了“表”，我們可以分別針對這兩個表進行排序篩選操作，互不影響。 五是添加計算列。我們可以根據(jù)需要在表的相鄰列（不能隔一列）輸入公式，形成對原表數(shù)據(jù)的計算，這時的“表”會把這一列包括到“表”中來，這一列也可以應用“表”的功能了。 在微軟表格中這個公式具有向下自動填充功能，而且這個公式的表達形式也具有特色，它把對單元格的引用漢字化，增強了公式的可閱讀性，如“=[@銷售金額]/[@銷售價格]”來表達銷售數(shù)量公式。但在WPS表格中，沒有向下自動填充功能，需要采取拖曳的方式向下填充。 例如：銷售表（表和透視表）.xlsx中的銷售記錄表I列的公式。 六是提供刪除重復項功能。這里的重復項，指的是兩行單元格的所有內(nèi)容相同，假如兩行表格各有5個單元格，這5個單元格的數(shù)據(jù)都在同一列內(nèi)相同。 其刪除重復項的步驟為：在表格轉(zhuǎn)換為“表”以后，將鼠標放在表中的任意一個單元格，在菜單欄就會出現(xiàn)“表格工具”菜單，在此菜單下有一個工具叫“刪除重復項”，點擊這個工具，進入“刪除重復項”對話框。根據(jù)需要請選擇一個或多個包含重復項的列，或者選擇全部，就可以確定是否有重復項了，點擊“確定”，就可以刪除重復項了。 顯示重復項的方法：要找打那個重復項，最簡單的操作方法是，第一，在表格的最右邊添加一列，用連接符號將一行中所有單元格的數(shù)據(jù)連接起來，再向下拖曳，形成一列。第二，將表格還原為區(qū)域。第三，點擊“數(shù)據(jù)”菜單下的“重復項”功能鍵，框選添加的一列，選擇“高亮度重復項”，就可以看到重復行的數(shù)據(jù)了。 七是基于“表”形成的透視表和圖表。我們在原“表”添加行或列以后，透視表能夠自動刷新，當然自動刷新要進行相關的操作，其具體方法我們在下面講解透視表時再講。 第五、“表”中選擇列或行的方法 一、選擇一列數(shù)據(jù)而不選擇整列的原因。不管是普通工作表還是我們這里講的“表”，選擇或者移動行和列時，應該遵循規(guī)范的方法。 直接移動表格中行或列的方法，會給表格的使用帶來許多不利的影響，一是這樣的操作會使表格所占的內(nèi)存變大，導致打開緩慢。二是選擇移動整列會導致這個表格以外的數(shù)據(jù)跟著移動，當我們的表格下面還有其他表格的時候，會破壞其他表格的結(jié)構(gòu)。三是選擇移動整列的操作會破壞我們當前“表”的結(jié)構(gòu)，導致許多功能不能使用。當然，這種操作還會帶來其他的一些負面影響，使工作表在運用中出現(xiàn)一些非正常的情況。 二、選擇并移動整列數(shù)據(jù)的方法。選擇要移動列的數(shù)據(jù)的方法是，將鼠標移到該列標題行頂部單元格劃線的位置，這時鼠標變成了一個黑色的向下方向鍵，點擊鼠標左鍵，即可選定該列的數(shù)據(jù)。這時我們可以看到選擇的區(qū)域，不包括該列的標題，在鼠標變成了一個黑色向下方向鍵的同時，再次點擊鼠標左鍵，就包括該列的標題了。我們將鼠標放在此列的邊框線上，當鼠標變成四個方向鍵圖標時，拖動鼠標，就可以移動這一列了。 選擇不包括標題行的數(shù)據(jù)區(qū)域，便于我們根據(jù)需要對數(shù)據(jù)區(qū)域進行操作，如同時加上一個數(shù)，修改數(shù)值金額格式等等。
如果我們選擇的多列需要和列標題一起移動，應該在選擇的起始列時，就將標題和內(nèi)容一起選定（選定方法同前面講的選定單列的方法一致），再拖動需要選擇更多的列。 這樣做的前提是，要選定的列沒有內(nèi)部的區(qū)域框選。 移動行和選擇多行的方法，與移動列和選擇多列的方法類似。 第五講：下拉菜單 我們在前面講解基礎數(shù)據(jù)表的時候，說到過下拉菜單是用來規(guī)范基礎數(shù)據(jù)的錄入的，它還能提高我們錄入數(shù)據(jù)的速度，下面我們來介紹下拉菜單的相關知識。 在微軟表格中，稱下拉菜單為數(shù)據(jù)的有效性。 建立下拉菜單講解表格.xlsx 第一、用手動輸入的方法建立下拉菜單 用手動方式建立下拉菜單的路徑與方法是： 其一，單個表格下拉菜單的設置方法。首先選定單元格，點擊菜單欄上的“數(shù)據(jù)”，再點擊其工具欄“下拉列表”，進入插入下拉列表對話框，在光標閃動處分行輸入需要的內(nèi)容，在輸入第二行時，點擊右上角的“+”號，輸入完成后，再點擊“確定”。 其二，批量表格下拉菜單的設置方法。將設置有下拉菜單的單元格向下拖曳填充，填充的表格也會有下拉菜單；將設置有下拉菜單的單元格向右拖曳，填充的表格也會有下拉菜單；在表格中插入行，這些插入的行相應的單元格也會有相同的下拉菜單。 我們還可以選定一個區(qū)域，針對這個區(qū)域設置下拉菜單，其設置下拉菜單的方法和單元格設置下拉菜單的方法一樣，不再需要進行拖曳填充，區(qū)域中的每一個單元格都會設置相同的下拉菜單。
采用導入的方式建立下拉菜單，直觀性強，操作簡單。 首先提前準備好下拉菜單表格（我們的這個表格可以適當留有空白，當我們框選包括空白單元格在內(nèi)的區(qū)域作為下拉菜單時，這些空白單元格可以在今后添加內(nèi)容，但這個區(qū)域只能是一行或一列，不能是多行或多列）； 接下來選定要設置下拉菜單的區(qū)域或單元格； 再依次點擊菜單欄的“數(shù)據(jù)”，進入其工具欄“下拉列表”，在插入下拉列表對話框中，選擇“從單元格選擇下拉選項”（而不是選擇“手動添加下拉列表”），這時鼠標會在“從單元格選擇下拉選項”下面的一個長方格中閃動，再框選提前準備好的下拉菜單表格； 最后點擊“確定”。
以上講述的建立下拉菜單方法，都是在WPS表格中適用的方法。在微軟表格中建立下拉菜單的方法，和其相似，具體的操作步驟是： 首先提前準備好下拉菜單表格（我們的這個表格可以適當留有空白，當我們框選包括空白單元格在內(nèi)的區(qū)域作為下拉菜單時，這些空白單元格可以在今后添加內(nèi)容，但這個區(qū)域只能是一行或一列，不能是多行或多列）； 接下來選定要設置下拉菜單的區(qū)域或單元格； 再依次點擊菜單欄的“數(shù)據(jù)”，進入其工具欄“數(shù)據(jù)驗證”，再點擊這個工具的下拉菜單“數(shù)據(jù)驗證”，進入“數(shù)據(jù)驗證對話框”，在設置頁面，設置驗證條件，在“允許”下面的方框中，點擊其下拉菜單，選擇“序列”。再將鼠標放置在“來源”下面的方框中，框選提前準備好的下拉菜單表格； 最后點擊“確定”。 如果下拉菜單的內(nèi)容很簡單，如下拉菜單為表達性別的“男”、“女”，我們可以不提前準備下拉菜單，直接在“來源”下面的方框中輸入“男，女”，類似于WPS表格中的手動輸入下拉菜單。 第四、下拉菜單的復制方法 下拉菜單的格式可以復制到其他表格的單元格里，可以直接復制，如果源單元格有內(nèi)容，可以點擊目標單元格，再點擊鼠標右鍵，選擇“清除內(nèi)容”選項，或者按下“delete”鍵清除內(nèi)容。有的版本使用僅粘貼格式的方法可以粘貼下拉菜單，但有的版本不行，要使用我們在這里講到的方法。 第五、下拉菜單的修改方法 采用導入方式設置下拉菜單，要修改下拉菜單時，我們可以通過修改先前準備好的下拉菜單列表的方法來進行修改，我們修改的范圍需要在當初框選的下拉菜單范圍內(nèi)，這種方法較之手動方式添加下拉菜單列表的修改更為簡捷。 采取手動的方式輸入的下拉菜單修改步驟是： 1、點擊菜單欄中的“數(shù)據(jù)”； 2、點擊“數(shù)據(jù)”菜單下的子目錄——“下拉列表”； 3、點擊已經(jīng)設置了下拉菜單列表的單元格； 4、在插入下拉列表對話框中，點擊右上角的寫字圖標； 5、點擊需要修改的內(nèi)容，并進行修改； 6、勾選對有同樣設置的單元格應用這些更改。 第六、清除下拉菜單的方法 先選定需要清除下拉菜單的區(qū)域，再執(zhí)行如下操作： 1、點擊菜單欄中的“數(shù)據(jù)”； 2、點擊“數(shù)據(jù)”菜單下的子目錄——“下拉列表”； 3、點擊已經(jīng)設置的單元格或區(qū)域； 4、在插入下拉列表對話框中，點擊"全部清除"。 在微軟表格中，修改和刪除下拉菜單的方法和WPS表格類似。 第六講：數(shù)據(jù)透視表（上） 數(shù)據(jù)透視表是根據(jù)選定的數(shù)據(jù)源生成的，可以動態(tài)改變其版面布局的互交式匯總表格。數(shù)據(jù)透視表不僅能夠按照改變后的版面布局自動重新計算數(shù)據(jù)，而且能夠根據(jù)更改后的基礎數(shù)據(jù)或數(shù)據(jù)源來刷新計算結(jié)果。 第一、創(chuàng)建數(shù)據(jù)透視表時，對基礎數(shù)據(jù)區(qū)域的選擇 我們在前面說過，“表”的概念是我們這套課程的一個核心知識點。我們對數(shù)據(jù)透視表的講述，也是以“表”為基礎進行展開。當我們的基礎數(shù)據(jù)區(qū)域已經(jīng)轉(zhuǎn)換為“表”，我們把鼠標放在“表”內(nèi)，實際上就選定了整個基礎數(shù)據(jù)區(qū)域，不需要再去選定區(qū)域了。不能將光標置于表格頂部，或拖動光標選擇整列的方法來選定整個區(qū)域，這樣會給應用Excel的其他功能帶來不利影響。 在有的微軟表格中，在創(chuàng)建數(shù)據(jù)透視表對話框中，需要輸入“表”的名稱，這個名稱是當初我們將區(qū)域轉(zhuǎn)換為“表”時，軟件給我們“表”的命名。找到這個名稱的方法是，點擊“表”中的任意一個單元格，點擊菜單欄下的“表格設計”，點擊工具欄最左邊的工具“表名稱”，就可以找到這個表的名稱了，我們在將區(qū)域轉(zhuǎn)換為“表”時，可以給這個“表”取一個個性化的名稱。 如果我們要更改基礎數(shù)據(jù)區(qū)域的選擇，可以把鼠標放在已經(jīng)形成的透視表中，點擊工具欄中的“數(shù)據(jù)透視表”，會顯示一個“更改數(shù)據(jù)透視表數(shù)據(jù)源”的對話框，在這個對話框中，可以修改數(shù)據(jù)透視表所顯示的區(qū)域。 第二、創(chuàng)建數(shù)據(jù)透視表時，對放置區(qū)域的選擇 生成的透視表放在哪里？Excel為我們提供了三種放置區(qū)域的方案。在創(chuàng)建數(shù)據(jù)透視表對話框中，可以根據(jù)需要進行選擇。 第一種是放置到基礎數(shù)據(jù)表中。在請選擇放置透視表的位置中，選擇“現(xiàn)有工作表”，我們可以點擊和基礎數(shù)據(jù)表同一個工作表中的一個單元格，作為透視表的起始區(qū)域。一般我們不選擇這種。選擇這種放置方式是違背我們前面講到的“三表思想”的。選擇這種放置方式，一般用在演示講解稿中。 第二種是放置到Excel重新生成的一張工作表上。在請選擇放置透視表的位置中，選擇“新工作表”，但是這一張新工作表是Excel自動生成的，在工作簿中工作表較少時可以使用。 第三種是放置到我們選定的工作表上。在請選擇放置透視表的位置中，選擇“現(xiàn)有工作表”，可以選擇我們指定工作表中的一個單元格，作為透視表的起始區(qū)域。在許多工作表組成的工作簿中，往往各個工作表的內(nèi)容都是事先指定的，放置到我們指定的工作表，便于我們對全局的掌控。 我們要正確理解“現(xiàn)有工作表”的界定，基礎數(shù)據(jù)所在的工作表和已經(jīng)出現(xiàn)在工作簿中的工作表都是“現(xiàn)有工作表”。 06 優(yōu)居區(qū)域代理視頻素材改動版（2021年）.xlsx講義工作表 第三、字段的選擇、母字段和子字段的關系，更改字段標題 1、字段的概念。這里首先講一個概念，就是字段。字段是基礎數(shù)據(jù)表中行或者列的標題，它是對行列數(shù)據(jù)內(nèi)容的概括。 2、拖動字段的方法。在“數(shù)據(jù)透視表”對話框中，我們可以將“字段列表”下面的字段，拖動到數(shù)據(jù)透視表區(qū)域中，形成不同的數(shù)據(jù)透視表。一般地，我們把反映期間或者日期的字段拖到這個對話框的篩選器方框中，把需要按照行分類的字段拖到行方框中，把需要按照列分類的字段拖到列方框中。如果我們要調(diào)整這四個方框中的字段，可以將方框中不需要的字段從方框中拖出，將需要的字段重新拖入。 3、母字段和子字段。在“數(shù)據(jù)透視表”對話框中，我們?nèi)绻蟿觾蓚€不同的字段到行，意味著形成的報表會依據(jù)這兩個字段進行分類，對話框列的字段中，放在前面的字段是我們報表行分類的第一依據(jù)，放在后面的字段是報表行分類的第二依據(jù)（也有的把放在前面的字段稱為母字段，把放在后面的字段稱為子字段）。上下變化字段在行中的位置，可以改變報表分類的依據(jù)。在對話框列字段中放置兩個字段的情形，也與之類似。 在一般表格中，既顯示文本，又顯示時間；既顯示類別，又顯示加盟商；在財務上既顯示大類，又顯示大類下的小類，都是母字段、子字段在具體事例中的應用。在應用母字段和子字段功能時，要注意到我們是母字段數(shù)據(jù)來源于子字段的數(shù)據(jù)。我們在編制基礎數(shù)據(jù)表時，可能會出現(xiàn)有的母字段有子字段的分類，有的母字段沒有子字段的分類，我們應該將母字段包含的字符設置為子字段包含的字符，才能保證數(shù)據(jù)的正確性。 06 優(yōu)居區(qū)域代理視頻素材改動版（2021年）.xlsx表B-1 4、在形成的數(shù)據(jù)透視表中對行（或列）字段包含的內(nèi)容進行篩選。在透視表中，行字段和列字段都是基礎數(shù)據(jù)表中文本內(nèi)容的概括，將行字段拖入行以后，我們會發(fā)現(xiàn)這些匯總的行或列，有些的我們不需要的。我們可以打開透視表行、列交叉處的排序篩選圖標，將我們不需要的行文本內(nèi)容，“空白”和“0”前面方框中的“√”去掉，也就是去掉這些選項，留下我們需要的選項。對于列字段包含的內(nèi)容可以使用同樣的方法，進行篩選。 5、數(shù)據(jù)透視表標題的更改。在形成的數(shù)據(jù)透視表中，可以更改字段標題。如將“總賬科目（貸方）”更改為“收入類別”，直接點擊數(shù)據(jù)透視表該字段所在的單元格進行修改，即可。 第四、更改數(shù)據(jù)透視表中的數(shù)據(jù)顯示格式 點擊數(shù)據(jù)透視表中的某一個單元格，再點擊鼠標右鍵，點擊“數(shù)字格式”，進入“單元格格式”對話框，在這里可以選擇數(shù)據(jù)的格式，而且數(shù)據(jù)透視表中的所有數(shù)據(jù)都會顯示這種格式。 不要點擊鼠標右鍵，在點擊“單元格格式”，進入“單元格格式”對話框的路徑來更改數(shù)據(jù)格式。這樣做的結(jié)果是，今后數(shù)據(jù)透視表添加數(shù)據(jù)的時候，添加的數(shù)據(jù)沒有使用新的單元格格式。 第五、透視表的更新設置與其相對應的明細表的更新 當基礎數(shù)據(jù)表的數(shù)據(jù)發(fā)生了更改或者添加刪除了內(nèi)容，透視表需要進行一定的設置，才能讓透視表跟隨著基礎數(shù)據(jù)表變化而變化。 一種方法是手動更新法。點擊數(shù)據(jù)透視表的一個單元格，點擊鼠標右鍵，再點擊“刷新”，這時數(shù)據(jù)透視表就隨著基礎數(shù)據(jù)表更新而更新了，需要注意的是，使用這種功能的前提是，基礎數(shù)據(jù)已經(jīng)轉(zhuǎn)換為“表”。 另一種方法是自動更新，其操作路徑為：點擊數(shù)據(jù)透視表的一個單元格，點擊鼠標右鍵，點擊“數(shù)據(jù)透視表選項”，點擊“數(shù)據(jù)”，勾選“打開文件時刷新數(shù)據(jù)”。同一張工作簿中，當基礎數(shù)據(jù)表更改后，再去打開工作簿中的數(shù)據(jù)透視表，數(shù)據(jù)透視表不會自動更新，Excel不把這種操作視為重新打開。一般情況下，在運用或者打印數(shù)據(jù)透視表的數(shù)據(jù)之前，還是選擇手動刷新的方法為宜。 數(shù)據(jù)透視表更新以后，根據(jù)數(shù)據(jù)透視表生成的明細表不會自動更新，需要重新生成。 我們還可以設置，數(shù)據(jù)透視表不隨著數(shù)據(jù)的更新調(diào)整列寬（往往我們的工作表上放有幾個數(shù)據(jù)透視表，如果某一個數(shù)據(jù)透視表的列寬發(fā)生了變化，會影響其他數(shù)據(jù)透視表的擺放）。其操作路徑為：點擊數(shù)據(jù)透視表的一個單元格，點擊鼠標右鍵，點擊“數(shù)據(jù)透視表選項”，將“隨著數(shù)據(jù)的更新調(diào)整列寬”前面的單元格去掉。 第七講： 數(shù)據(jù)透視表（中） 這一講，我們繼續(xù)講解數(shù)據(jù)透視表。 第六、更改數(shù)據(jù)透視表的統(tǒng)計方式 數(shù)據(jù)透視表的默認統(tǒng)計方式為求和，實際上數(shù)據(jù)透視表還提供了其他的統(tǒng)計方式。如：計數(shù)、乘積、最大值、最小值等等。其操作路徑為：點擊數(shù)據(jù)透視表的一個單元格，點擊鼠標右鍵，選擇“值匯總依據(jù)”，再根據(jù)需要選擇相應的匯總方式。 第七、通過數(shù)據(jù)透視表顯示明細表及基礎數(shù)據(jù)表的拆分 我們可以通過數(shù)據(jù)透視表，直接查找到每一個數(shù)據(jù)的詳細信息。這個詳細信息，在財務上就是明細表。其操作路徑為：點擊數(shù)據(jù)透視表的一個單元格，點擊鼠標右鍵，選擇“詳細信息”即可。 基礎數(shù)據(jù)表的拆分，實際上是透視表顯示明細功能的延伸。我們框選透視表中按行匯總的合計欄所有數(shù)據(jù)，再點擊鼠標右鍵，選擇“詳細信息”，這時就會在此工作簿中顯示幾個工作表，來放置反映合計欄數(shù)據(jù)相關的明細內(nèi)容。實際上就是按行分類字段，將工作表進行了拆分。這樣的操作有利于我們同時打印這幾個拆分的工作表。 因選擇“詳細信息”生成的表格，是在本工作簿中生成一個新的表格，這個表格的位置不能由我們指定。這些工作表，一般放置在我們放置透視表的工作表前面。 第八、數(shù)據(jù)透視表的排序 數(shù)據(jù)透視表可以對統(tǒng)計的數(shù)據(jù)進行排序。其操作路徑為：點擊數(shù)據(jù)透視表的一個單元格，點擊鼠標右鍵，選擇“排序”即可。我們將鼠標放在透視表的哪一列，就以哪一列為依據(jù)進行排序。 我們還可以用手動的方式，將數(shù)據(jù)透視表中的一行（列）內(nèi)容進行移動。其操作路徑是，選擇要移動行（列），將鼠標移到該列標題行頂部單元格劃線的位置，這時鼠標變成了一個黑色的向右（下）方向鍵，點擊鼠標左鍵，即可選定該行（列）的數(shù)據(jù)（包括字段）。我們將鼠標放在此列的邊框線上，當鼠標變成四個方向鍵圖標的時候，拖動鼠標，就可以移動這一列了。這比我們移動“表”中的行或列，要簡單點。（手動排序的功能，在wps表格中不支持，在微軟表格中支持這種功能） 第九、數(shù)據(jù)透視表的篩選 數(shù)據(jù)透視表可以對統(tǒng)計的數(shù)據(jù)（包括文本和數(shù)值）進行篩選。其操作路徑為：點擊數(shù)據(jù)透視表行和列交叉的單元格中的排序篩選功能方框，在選擇字段對話框中，根據(jù)需要選擇“標簽篩選”或“值篩選”，再根據(jù)需要選擇各種不同的方式。 標簽篩選，是針對文本而言的篩選，可以實現(xiàn)針對行字段中的內(nèi)容進行篩選，如是否包括某些內(nèi)容，是否以某些字符開頭，是否以某些字符結(jié)尾等；值篩選，是針對數(shù)值而言的篩選，如果有多列求和數(shù)據(jù)，我們的數(shù)值篩選是針對總計列而言的。 第十、數(shù)據(jù)透視表的第二次運算（比率和差異性分析） 在數(shù)據(jù)透視表中，提供了許多第二次運算方式，主要是各種百分比和差異，反映某一個數(shù)據(jù)在全局中的比例或與總計數(shù)據(jù)的差異等等。其操作路徑為：點擊數(shù)據(jù)透視表的一個單元格，點擊鼠標右鍵，選擇“值顯示方式”，再點擊其相應的下拉菜單即可。這種功能，可以讓我們在提供求和數(shù)值以后，再用一張表來反映各種比例值。 第八講： 數(shù)據(jù)透視表（下） 這一講，我們繼續(xù)講解數(shù)據(jù)透視表。 第十一、調(diào)整數(shù)據(jù)透視表的樣式和匯總行 1、樣式調(diào)整。在“報表布局”中，為我們提供了如下幾種形式的報表：以壓縮形式顯示；以大綱形式顯示；以表格形式顯示；重復所有項目標簽；不重復項目標簽等等，我們一般選擇“以表格形式顯示”。 其操作路徑為：點擊數(shù)據(jù)透視表的一個單元格，點擊菜單欄的“設計”，再點擊其工具欄的“報表布局”，再根據(jù)需要點擊報表布局形式。 對于涉及到母字段和子字段的透視表，改變母字段和子字段在“數(shù)據(jù)透視表”對話框中的相對位置，即將母字段移到子字段的下面，其實質(zhì)是將母字段變?yōu)樽幼侄危瑢⒆幼侄巫優(yōu)槟缸侄?，這時報表的排列方式會發(fā)生改變，但這種改變和我們這里講的調(diào)整數(shù)據(jù)透視表的樣式，不是同一個概念，它不需要用到報表布局功能。 2、分類匯總的設計。在“設計”菜單欄下，有一個功能鍵，叫“分類匯總”，這個功能鍵按照行字段的類別，為我們提供了如下幾種形式的分類匯總方式：不顯示分類匯總，在組的底部顯示分類匯總，在組的頂部顯示分類匯總。 06 優(yōu)居區(qū)域代理視頻素材改動版（2021年）.xlsx 第十二、針對數(shù)據(jù)透視表的日期或數(shù)字進行分類統(tǒng)計 我們要對基礎數(shù)據(jù)表的日期按月進行分類匯總，一個思路是在基礎數(shù)據(jù)表添加輔助列為月份（提取字符形成月份），再針對月份這個字段進行匯總，但這樣操作起來工作量比較大。還有一種方法可以更簡單：點擊透視表中的一個日期單元格，點擊鼠標右鍵，點擊彈出來的菜單中的“組合”，選擇“月”，再點擊“確定”。如果選擇“季度”，可以按季度進度分類匯總；如果既選擇月份又選擇季度，可以實現(xiàn)在月份匯總的基礎上再按季度匯總。 點擊鼠標右鍵，點擊彈出來的菜單中的“取消組合”，可以將月份或者季度匯總返回到日期明細或者數(shù)字明細。 我們要對求和列，分區(qū)間進行分類匯總，如分為0—1000,1001—2000,2001—3000，進行分類匯總。我們可以將數(shù)值先拖入到行方框，再拖入到求和方框，讓行方框和求和方框都有數(shù)值。點擊透視表中行字段的一個數(shù)值單元格，點擊鼠標右鍵，點擊彈出來的菜單中的“組合”，在“組合”對話框中設置“起始于”、“終止于”、“步長”的值后點擊“確定”，即可。起始于的值初始設定為求和值的最小值，終止于的初始設定為求和值的最大值，一般情況下我們不需要更改這些初始設置，步長值我們可以根據(jù)需要修改。 這種方法對數(shù)據(jù)進行分類求和，是對相同的間隔區(qū)間的數(shù)據(jù)進行求和。如果把透視表中的值顯示依據(jù)由“求和”變?yōu)椤坝嫈?shù)”，就可以求出每一區(qū)間的數(shù)據(jù)個數(shù)，常用來求出學生某個分數(shù)段的人數(shù)。 取消分類匯總的方法是，點擊行字段的一個數(shù)值，再點擊鼠標右鍵，選擇“取消組合”，即可。 銷售表（表和透視表）.xlsx 第十三、用切片器控制透視表 在透視表對話框中，有一個方框叫做“篩選器”，但篩選器只能控制一個透視表。切片器能夠同時控制多個透視表，實現(xiàn)多種形式的表格匯總，減少工作量。 用切片器控制多個透視表的方法是： 一是進入切片器，確定控制字段。這個字段和我們篩選器中的字段作用是一樣的。將光標放在一張透視表上，點擊菜單欄的“插入”，再點擊下面工具欄的“切片器”，進入“插入切片器”對話框，選擇相應的字段。 二是確定要控制的透視表。點擊切片器的下拉菜單“報表連接”（也有的版本叫做“數(shù)據(jù)透視表連接”），進入“數(shù)據(jù)透視表連接”對話框，選定透視表，關閉此對話框。 三是運用切片器實現(xiàn)對透視表的控制。進入插入的切片器，單選或多選切片器中字段包括的內(nèi)容，就可以用切片器控制多個透視表。假如切片器的字段是月份，我們選擇“一月”，透視表就可以顯示一月的數(shù)據(jù)；我們同時選定一月和二月，透視表就可以顯示這兩個月的合計數(shù)據(jù)。 切片器只能針對一個表格生成的不同類型透視表進行操作，不能控制多個表格生成的不同透視表。 06 優(yōu)居區(qū)域代理視頻素材改動版（2021年）.xlsx 第十四、數(shù)據(jù)透視表的復制和移動 數(shù)據(jù)透視表的復制和移動，是兩個不同的概念?？蜻x已經(jīng)形成的透視表，點擊鼠標右鍵，單擊復制菜單，可以將透視表復制到另外一個地方。但這個復制的透視表，是普通的表格，如果要讓復制的透視表具有透視表的功能，應該采取移動工作表并建立副本的辦法來復制數(shù)據(jù)透視表。 點擊數(shù)據(jù)透視表的一個單元格，點擊菜單欄的“分析”，點擊其下的功能鍵“移動數(shù)據(jù)透視表”，進入“移動數(shù)據(jù)透視表”對話框，點擊我們要重新放置數(shù)據(jù)透視表位置的起始區(qū)域，就改變了原來放置數(shù)據(jù)透視表的起始區(qū)域，達到了移動數(shù)據(jù)透視表的目的。 第十五、數(shù)據(jù)透視表的刪除 點擊數(shù)據(jù)透視表的一個單元格，點擊菜單欄的“分析”，點擊其下的功能鍵“刪除數(shù)據(jù)透視表”，就可以數(shù)據(jù)透視表了。 也可以選擇已經(jīng)形成的數(shù)據(jù)所在行，用刪除行的辦法來刪除數(shù)據(jù)透視表。 第十六、數(shù)據(jù)透視表的功能小結(jié) 數(shù)據(jù)透視表把表格制作、篩選、分類匯總、數(shù)據(jù)的比率和差異性分析等幾項功能統(tǒng)一起來了，并且能夠?qū)崿F(xiàn)快速刷新，能滿足絕大多數(shù)情況下對于數(shù)據(jù)分析的需要。 其篩選是通過點擊某一項數(shù)據(jù)的匯總值，自動彈出其明細而實現(xiàn)；分類匯總，是需要將匯總的字段列入透視表而實現(xiàn)，而且這種匯總在顯示具備結(jié)果的同時，還反映了各項匯總的整體情況；數(shù)據(jù)比率和差異性分析，主要是通過“值顯示方式”來實現(xiàn)。 網(wǎng)上有許多關于數(shù)據(jù)篩選、分類匯總、數(shù)據(jù)計算和分析的小視頻，但它們只是從局部來分析，而不是作為一個整體來分析。從這點上講，它們都不及數(shù)據(jù)透視表全面，從深入的角度，它們可能比數(shù)據(jù)透視表研究得更深。Excel作為一種工具，能夠滿足我們快速全面地進行數(shù)據(jù)分析，我們沒有必要就工具去研究工具。數(shù)據(jù)透視表滿足了我們提高工作效率和解決問題的需要，是一種簡潔實用的工具。 第九講：數(shù)據(jù)透視表與圖表 圖表是數(shù)據(jù)形象化的表示，圖表往往給人高大上的感覺。圖表作為Excel的一項重要功能，我們將這部分內(nèi)容作為單獨的一講。
圖表既可基于基礎數(shù)據(jù)表生成，也可直接基于數(shù)據(jù)透視表生成。直接基于基礎數(shù)據(jù)表生成圖表時，要框選基礎數(shù)據(jù)表的區(qū)域，而且要求表格中的行字段不重復，只適用于簡單的表格。透視表是在基礎數(shù)據(jù)表進行分類匯總后形成的，基于透視表生成的圖表，能正確反映我們需要的數(shù)量關系，也適合于復雜的基礎數(shù)據(jù)表，從本質(zhì)上講，還是反映基礎數(shù)據(jù)表的數(shù)量關系，是我們工作中經(jīng)常采用的方法。 點擊已經(jīng)生成的數(shù)據(jù)透視表，將光標放在透視表中，再點擊菜單欄的“插入”，進入其工具欄，點擊“全部圖表”，選擇恰當?shù)膱D形，即可形成圖表。 06 優(yōu)居區(qū)域代理視頻素材改動版（2021年）.xlsxB-1工作表 2、不同圖表的功能 一般用柱形圖來表示不同系列的比例，用折線圖來表示數(shù)據(jù)隨時間變化的趨勢，用餅圖來顯示局部與整體的占比情況。 3、圖表在本工作表內(nèi)的移動 將鼠標放在圖表中，當鼠標變成四方箭頭的十字架時，我們就可以移動圖表了。 4、圖表的大小變化 點擊圖形，這時會發(fā)現(xiàn)圖形被一個方框框起來了，這個方框的上下邊都有3個圓圈，再將鼠標放在圖表方框的邊角下頂處，當鼠標變?yōu)殡p向箭頭時，拖動鼠標可以改變圖形的大小。 5、給圖表添加標題 將鼠標放在圖表上，依次進入“圖表工具”、“添加元素”、“圖表標題”，可以為圖表添加標題。 6、給圖表添加數(shù)據(jù) 將鼠標放在圖表上，依次進入“圖表工具”、“添加元素”、“數(shù)據(jù)標簽”，可以為圖表添加數(shù)據(jù)?？梢愿鶕?jù)喜好，選擇不同的圖例，把數(shù)據(jù)放在圖表的不同地方，如柱形圖的上面，柱形圖的中間。 7、更改圖表顏色 將鼠標放在圖表上，依次進入“圖表工具”、“更改顏色”，可以更改圖表的顏色。 8、更改圖表樣式 將鼠標放在圖表上，進入菜單“圖表工具”，下面的各種圖示，可以更改圖表的樣式。 9、移動圖表到其他工作表 將鼠標放在圖表上，依次進入“圖表工具”、“移動圖表”，可以將圖表移動到另一個工作表的指定位置。 第十講：名 稱 這一講，我們來講解名稱。 第一、“名稱”的概念和使用范圍 名稱，就是把一個復雜的東西，給它取個名字，讓這個名字來代替這個復雜的東西，使復雜問題簡單化。我們?nèi)〉拿?，應力求個性化，能反映它所代表的東西。這樣便于我們以后在應用名稱時，看到該名稱，就知道它所代表的對象，特別是在完成表格一段時間以后，再次查閱表格時，會更加感覺到名稱取名的重要性。 名稱的使用范圍，也就是名稱所代表的內(nèi)容，在什么范圍內(nèi)得到電子表格軟件的認可。一般設置為本工作簿，也可以設置為工作簿中的一個工作表。點擊菜單欄的“公式”，再點擊“名稱管理器”，進入“名稱管理器”對話框，點擊“新建”，在“新建名稱”對話框中，在“范圍”旁邊的長方形方框中，點擊其下拉菜單，可以設置名稱的使用范圍。 第二、將一個區(qū)域表達為“名稱”的方法 將一個區(qū)域表達為“名稱”的方法是： 1、將一個區(qū)域表達為名稱的一般方法。點擊菜單欄“公式”，點擊其工具欄的“名稱管理器”，在“名稱管理器”對話框中，點擊“新建”，進入“新建名稱”對話框。在此對話框中，輸入適當?shù)拿Q，選擇或默認應用范圍，最后框選名稱代表的區(qū)域，或者在此對話框中的引用位置中輸入?yún)^(qū)域范圍的電子地址，如D1:D10。 2、批量設置名稱的方法。例如，我們的下拉菜單都建在一個工作表中，每個下拉菜單在首行都有一個標題，可以批量建立名稱。 在WPS表格中，進行此設置的方法是，框選包括標題行的下拉菜單區(qū)域，點擊菜單欄“公式”，點擊其工具欄的“名稱管理器”旁邊的“指定”工具，進入“指定名稱”對話框，勾選“名稱創(chuàng)建于首行”，即可。 在微軟表格中，進行此設置的方法是，框選包括標題行的下拉菜單區(qū)域，點擊菜單欄“公式”，點擊其工具欄的“名稱管理器”旁邊的“根據(jù)所選內(nèi)容創(chuàng)建”工具，進入“根據(jù)所選內(nèi)容創(chuàng)建名稱”對話框中，勾選“首行”，即可（與WPS表格的方法類似）。 3、“名稱對話框”中備注的作用，可用來描述這個名稱的具體含義。 4、應用區(qū)域名稱的方法是，在引用這個區(qū)域時，直接輸入設定的名稱即可。當我們輸入名稱的第一個字符時，系統(tǒng)會自動提示以這個字符開頭的名稱，避免輸錯名稱。 下面我們舉一個例子來說明如何將一個區(qū)域表達為“名稱”。 將一個區(qū)域表達為一個名稱.xlsx 我們在前面講解將區(qū)間轉(zhuǎn)換為“表”時，講過這時Excel會給“表”一個名稱，就區(qū)域引用來講，這兩個“名稱”的含義是相同的，“表”的名稱還代表這個區(qū)域是“表”，具有特殊的功能。 第三、將一個公式中的整體或片段表達為一個“名稱”的方法 給公式取一個“名稱”的方法是： 1、點擊“名稱管理器”，在名稱管理器對話框中點擊“新建”； 2、在“名稱管理器”對話框中的名稱欄，給這個公式取一個名字。 3、在“名稱管理器”對話框中的引用位置欄，點擊設置了公式的一個單位格，將這個單元格的公式寫到引用位置欄中（打開名稱對話框后，WPS表格不允許采取復制粘貼的辦法在這里直接輸入公式）；或者先點擊設置了公式的單元格，這時編輯欄會顯示設置的公式，在編輯欄復制公式，然后按下回車鍵，讓該單元格的公式不再處于編輯狀態(tài)，再打開“名稱管理器”，就可以在“引用位置”欄中復制公式了。 如下拉菜單與VLOOKUP函數(shù)的聯(lián)合應用.xlsx中，給元月工資查找公式取一個名稱。 第四、名稱在公式中的應用 其一，將一個公式表達為“名稱”，其引用單元格表達范圍的變化。 下面我們先舉一個例子來說明如何將一個公式表達為“名稱”。 下拉菜單與VLOOKUP函數(shù)的聯(lián)合應用.xlsx第一張表 K4單元格查找元月份工資的公式為： =VLOOKUP($J$4,$D$6:$F$24,MATCH(K$3,$D$5:$F$5,0),0) L4單元格查找2月份工資的公式為： =VLOOKUP($J$4,$D$6:$F$24,MATCH(L$3,$D$5:$F$5,0),0) L4單元格公式由K4單元格公式向右拖曳而成，僅僅是將“K$3”改為“L$3”，就是說其相對引用功能發(fā)揮了作用。如果將k4單元格的公式，設定為一個名稱，將這個公式名稱向右拖曳，依然可以得到L4單元格的公式。 在原來的WPS表格軟件中，名稱所對應的公式中，其引用的單元格，是不會隨著位置的變化而變化的。各位同仁可以發(fā)現(xiàn)，在2021年8月份，WPS的此項功能得到了改進，其引用的單元格，可以隨著位置的變化而變化的，給我們設置和應用公式帶來了很大地方便，但我們有時需要在另一單元格應用名稱所代表的單元格時，應該將其所引用的單元格設置為絕對引用。 我們在這里實際上講到了一個電子表格中很重要的一對概念，就是“相對引用”和“絕對引用”。簡單地說，相對引用，就是所引用單元格的范圍，會隨著公式的移動而移動，但相對位置不發(fā)生變化，我們通常引用的單元格都是相對引用；絕對引用，就是所引用單元格的范圍，不會隨著公式的移動而發(fā)生變化。我們在第十五講——函數(shù)與公式（二），再具體地對這些概念進行詳細的講解。 其二，將公式中的一部分參數(shù)表達為一個名稱。 我們可以將公式中的一部分參數(shù)設置為“名稱”，如上面例子中，“$D$6:$F$24”，它表達的是查找區(qū)間，我們可以給他取一個名稱叫“查找區(qū)間”。其方法是，在“名稱管理器”對話框中的名稱欄輸入“查找區(qū)間”，在引用位置欄，直接輸入“$D$6:$F$24”。 下拉菜單與VLOOKUP函數(shù)的聯(lián)合應用.xlsx第一張表 一般地，跨工作表引用區(qū)域，而且這些區(qū)域是絕對引用，采用名稱的功能來替換參數(shù)，可以增強了公式的閱讀性，簡化了公式，還保證了公式在拖曳時得到我們想要的公式。關于這一部分內(nèi)容的展開，我們在講解函數(shù)SUMIFS函數(shù)和vlookup函數(shù)時再講。 其三，將公式中比較復雜的數(shù)學物理公式表達為一個名稱。 將公式表達為“名稱”的另一個應用是，將公式中比較復雜的數(shù)學或物理公式設置為“名稱”，如將梯形的面積公式(a+b)h/2，定義為“梯形面積”，就會很方便我們閱讀，提高公式書寫速度。 其四，“表”與名稱定義的區(qū)域 我們知道“表”的區(qū)域具有拓展功能，當名稱定義的區(qū)域是“表”的一部分的時候，名稱定義的區(qū)域不會隨著“表”的拓展而拓展。在微軟表格中，名稱定義的區(qū)域可以隨著“表”的拓展而拓展。 銷售表（表和透視表）.xlsx“表”與名稱的應用 其五，下拉菜單與名稱的應用 我們在前面講解下拉菜單時，講過可用導入的方式建立下拉菜單，將下拉菜中“從單元格選擇下拉菜單”的一個區(qū)域取一個名字，但用這個名字應用到我們需要引用的區(qū)域時，是不可行的。名稱是菜單欄“公式”下面的一個功能鍵，只適用于公式中。 第五、確認、修改、刪除名稱的方法 確認、修改、刪除名稱的方法是： 1、確認建立名稱的方法。創(chuàng)建名稱完成后，我們點擊“名稱管理器”，進入名稱管理器對話框，可以看到已經(jīng)將我們創(chuàng)建的名稱列入其中了。 2、修改名稱的方法。點擊“名稱管理器”，進入“名稱管理器”對話框，點擊“編輯”可以對名稱進行修改。 3、刪除名稱的方法。點擊“名稱管理器”，進入“名稱管理器”對話框，選中名稱管理器中排列的任意一條名稱，點擊“刪除”可以刪除名稱。對“表”取的名稱不允許刪除。 |
|
|
