excel電子表格應用-飛 雪-ZOL博客

excel電子表格應用2007-11-09 21:57:31 其他 | 評論(0) | 瀏覽(7609) 在EXECL中建立下拉列表按鈕選定你要設置下拉列表的單元格，點「數據」->「有效性」->「設置」，在「允許」下面選擇「序列」，在「來源」框中輸入你的下拉列表內容，各項之間用半形逗號隔開，如：A,B,C,D 選中「提供下拉前頭」，點「確定」。八、阿拉伯數字轉換為大寫金額假定你要在A5輸入阿拉佰數字,B5轉換成中文大寫金額(含元角分),請在B5單元格輸入如下公式: =IF((INT(A5*10)-INT(A5)*10)=0,TEXT(INT(A5),"[DBNum2]G/通用格式")&"元"&IF((INT(A5*100)-INT((A5)*10)*10)=0,"整","零"&TEXT(INT(A5*100)-INT(A5*10)*10,"[DBNum2]G/通用格式")&"分"),TEXT(INT(A5),"[DBNum2]G/通用格式")&"元"&IF((INT(A5*100)-INT((A5)*10)*10)=0,TEXT((INT(A5*10)-INT(A5)*10),"[DBNum2]G/通用格式")&"角整",TEXT((INT(A5*10)-INT(A5)*10),"[DBNum2]G/通用格式")&"角"&TEXT(INT(A5*100)-INT(A5*10)*10,"[DBNum2]G/通用格式")&"分"))九、EXECL中怎樣批量地處理按行排序假定有大量的數據，需要將每一行按從大到小排序，如何操作？由於按行排序與按列排序都是只能有一個主關鍵字,主關鍵字相同時才能按次關鍵字排序。所以，這一問題不能用排序來解決。解決方法如下： 1、假定你的數據在A至E列，請在F1單元格輸入公式： =LARGE($A1:$E1,COLUMN(A1)) 用填充柄將公式向下複製到相應行。 2、用滑鼠選定F列，用「查找/替換」的方法，將該列的"$A"替換成"$A$"，"$E"替換成"$E$"。 3、用滑鼠選定F列所有有公式的單元格，用填充柄將公式向右複製到J列。 你原有數據將按行從大到小排序出現在F至J列。如有需要可用「選擇性粘貼/數值」複製到其他地方。 註：第1步的公式可根據你的實際情況（數據範圍）作相應的修改。十、巧用函數組合進行多條件的計數統計例：第一行為表頭，A列是「姓名」，B列是「班級」，C列是「語文成績」，D列是「錄取結果」，現在要統計「班級」為「二」，「語文成績」大於等於104，「錄取結果」為「重本」的人數。統計結果存放在本工作表的其他列。公式如下：=SUM(IF((B2:B9999="二")*(C2:C9999>=104)*(D2:D9999="重本"),1,0))輸入完公式後按Ctrl+Shift+Enter鍵,讓它自動加上數組公式符號"{}"。十一、EXECL中某個單元格內文字行間距調整方法。當某個單元格內有大量文字時，很多人都覺得很難將其行間距按自己的要求進行調整。現介紹一種方法可以讓你任意調整單元格內文字的行間距：右擊單元格,點"設置單元格格式"->"對齊",將"水平對齊"選擇"靠左",將"垂直對齊"選擇"分散對齊",選中"自動換行"，點「確定」。你再用滑鼠將行高根據你要求的行距調整到適當高度即可。註：綠色內容為關鍵點，很多人就是這一點設置不對而無法調整行間距。十二、如何在EXCEL中引用當前工作表名如果你的工作薄已經保存,下面公式可以得到單元格所在工作表名: =RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))十三、相同格式多工作表匯總求和方法假定同一工作薄有SHEET1至SHEET100共100個相同格式的工作表需要匯總求和，結果放在SHEET101工作表中，請在SHEET101的A1單元格輸入：=SUM(單擊SHEET1標籤，按住Shift鍵並單擊SHEET100標籤，單擊A1單元格，再輸入：）此時公式看上去內容如下：=SUM("SHEET1:SHEET100"!A1)按回車後公式變為=SUM(SHEET1:SHEET100!A1)所以，最簡單快捷的方法就是在SHEET101的A1單元格直接輸入公式：=SUM("SHEET1:SHEET100"!A1)然後按回車。十四、如何判斷單元格里是否包含指定文本？假定對A1單元格進行判斷有無"指定文本",以下任一公式均可: =IF(COUNTIF(A1,"*"&"指定文本"&"*")=1,"有","無") =IF(ISERROR(FIND("指定文本",A1,1)),"無","有")十五、如何替換EXECL中的通配符「?」和「*」？在EXECL中查找和替換時，?代表任意單個字元，*代表任意多個字元。如果要將工作表中的"?"和"*"替換成其他字元，就只能在查找框中輸入~?~和~*~才能正確替換。十六、EXECL中排名次的兩種方法：（一）、用RANK()函數： 假定E列為成績，F列為名次，F2單元格公式如下： =RANK(E2,E:E) 這種方法，分數相同時名次相同，隨後的名次將空缺。 例如：兩個人99分，並列第2名，則第3名空缺，接下來是第4名。（二）、用排序加公式： 1、先在後面用填充柄增加一列（假定為G列）與行號相同的序列數。 2、將全表按分數列（E列）排序，在F2單元格輸入1，在F3單元格輸入公式： =IF(E3=E2,F2,F2+1) 將公式向下複製到相應行。 3、選定公式列，點「複製」，在F1單元格點右鍵，點「選擇性粘貼/數值」，點「確定」。 4、將全表按最後一列（G列）排序，刪除最後一列。 第二種方法分數相同的名次也相同，不過隨後的名次不會空缺。十七、什麼是單元格的相對引用、絕對引用和混合引用？相對引用、絕對引用和混合引用是指在公式中使用單元格或單元格區域的地址時，當將公式向旁邊複製時，地址是如何變化的。 具體情況舉例說明： 1、相對引用，複製公式時地址跟著發生變化，如C1單元格有公式：=A1+B1 當將公式複製到C2單元格時變為：=A2+B2 當將公式複製到D1單元格時變為：=B1+C1 2、絕對引用，複製公式時地址不會跟著發生變化，如C1單元格有公式：=$A$1+$B$1 當將公式複製到C2單元格時仍為：=$A$1+$B$1 當將公式複製到D1單元格時仍為：=$A$1+$B$1 3、混合引用，複製公式時地址的部分內容跟著發生變化，如C1單元格有公式：=$A1+B$1 當將公式複製到C2單元格時變為：=$A2+B$1 當將公式複製到D1單元格時變為：=$A1+C$1 規律：加上了絕對地址符「$」的列標和行號為絕對地址，在公式向旁邊複製時不會發生變化，沒有加上絕對地址符號的列標和行號為相對地址，在公式向旁邊複製時會跟著發生變化。混合引用時部分地址發生變化。注意：工作薄和工作表都是絕對引用，沒有相對引用。十八、求某一區域內不重複的數據個數例如求A1:A100範圍內不重複數據的個數，某個數重複多次出現只算一個。有兩種計算方法：一是利用數組公式：=SUM(1/COUNTIF(A1:A100,A1:A100))輸入完公式後按Ctrl+Shift+Enter鍵,讓它自動加上數組公式符號"{}"。二是利用乘積求和函數：=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))十九、EXECL中如何動態地引用某列的最後一個單元格？在SHEET2中的A1單元格中引用表SHEET1中的A列的最後一個單元格中的數值(SHEET1中A列的最後一個單元格的數值不確定,隨時會增加行數)：=OFFSET(Sheet1!A1,COUNTA(Sheet1!A:A)-1,0,1,1) 或者: =INDIRECT("sheet1!A"&COUNTA(Sheet1!A:A)) 注:要確保你SHEET1的A列中間沒有空格。二十、如何在一個工作薄中建立幾千個工作表右擊某個工作表標籤,點"插入",選擇"工作表",點"確定",然後按住Alt+Enter鍵不放,你要多少個你就按住多久不放,你會看到工作表數量在不斷增加,幾千個都沒有問題。排名問題在A1:F6區域有下面一個表格： 班級 姓名 政治 語文 數學 總分 1 小東 90 90 90 270 /2 明明 95 92 90 277 /3 小英 96 89 91 276 /4 小劉 95 90 92 277 /5 小紅 95 91 92 278 /要在K1:K3的單元格中分別顯示總分最高的同學的班級、姓名、總分/在L1:L3的單元格中分別顯示總分第二的同學的班級、姓名、總分 /在M1:M3的單元格中分別顯示總分第三的同學的班級、姓名、總分 /注意期中277分的有兩人,不要出現第二名與第三名都是"明明"的結果. A: dongmu 定義A2:A6區域為班級 ；定義B2:B6區域為姓名；定義F2:F6區域為總分 K1={INDEX(班級,MATCH(LARGE(總分+1-ROW(總分)/100,ROW($A1)),總分+1-ROW(總分)/100,0))} L1={INDEX(姓名,MATCH(LARGE(總分+1-ROW(總分)/100,ROW($A1)),總分+1-ROW(總分)/100,0))} M1={INDEX(總分,MATCH(LARGE(總分+1-ROW(總分)/100,ROW($A1)),總分+1-ROW(總分)/100,0))}註：只要向下複製，便可得到1~6名的情況了。該公式可以無限排列。 研究彩票，從統計入手Q：我有一個VBA編程的問題向你請教。麻煩你幫助編一個。我一定厚謝。 有一個數組列在EXCEL中如： 01 02 03 04 05 06 07 /和01 04 12 19 25 26 32 /02 08 15 16 18 24 28 /01 02 07 09 12 15 22/09 15 17 20 22 29 32/比較，如果有相同的數就在第八位記一個數。如 :01 04 12 19 25 26 32 2 /02 08 15 16 18 24 28 1 /01 02 07 09 12 15 22 2 /09 15 17 20 22 29 32 0 .這個數列有幾千組，只要求比較出有幾位相同就行。 解答：把「01 02 03 04 05 06 07 」放在表格的第一行，「01 04 12 19 25 26 32 2」放第二行。 把以下公式貼到第二行第八個單元格「A9」中，按F2,再按CTRL+SHIFT+ENTER. =COUNT(MATCH(A2:G2,$A$1:$G$1,0)) 去掉XLS文件頭上菜單欄的「MIcrosoft EXCEL 」字樣 A: Private Sub Workbook_Open()Application.Caption = "程香宙專用表格"End Sub 替換數據

Q：請教各位如何用將一組數據， 如：6550894， 9852547， 2656032， 7461136， 0505867， 5564892， 7235580，0421077，我需要把數據中的數字1，3，5換為符號A表示，2，4，6換為符號B表示，依此類推將數據中的阿拉伯數字0~9分為幾類用其它符號替換。 A： leaf 用VBA處理比較方便。 只用EXCEL函數，感覺代價太大。 假設：B13值為9550894在B14中輸入=IF(ISERROR(FIND(MID($B$13,1,1),"135")),IF(ISERROR(FIND(MID($B$13,1,1),"246")),IF(ISERROR(FIND(MID($B$13,1,1),"79")),IF(ISERROR(FIND(MID($B$13,1,1),"80")),"","D"),"C"),"B"),"A")，C14中MID()第二個參數為2，以此類推...最後在目標單元格中輸入：=CONCATENATE(B14,C14,D14,E14,F14,G14,H14) dongmu ：表一: AB... 11234567890 、2ABABABCCCD 、=SUBSTITUTE(A4,A$1,A$2) 說明:先列一個替換表,如表一,在A4處填如數據,在B4處填如上述公式=SUBSTITUTE(A4,A$1,A$2),並向右拖動9個同樣的公式,最後一個便是結果.在將該10個相同的公式向下拖,便得到其它的結果.好處:可以修改表一,產生變化. ACCESS ：你可以把全部數據拷貝到WORD中，再用替換命令，想怎麼換就怎麼換，然後在拷貝回來。 複製數據再轉置,不複製轉置被隱藏的行或列

解答：選擇需要轉置的單元區域，按下F5－－＞定位條件－－＞可見單元格－－＞複製－－＞選擇性粘貼－－＞轉置。 如何始終打開默認的工作表

可不可以作到每次保存工作表時，無論保存時是在哪一個 SHEET，但是當下次再打開時，還是原來默認的那張工作表。比如SHEET1。謝謝！解答：Private Sub Workbook_Open()Worksheets("sheet1").ActivateEnd Sub 如何分割文本

有一列數據，全部是郵箱的，現在想將@前面的賬號與@後面的域名分割開，分為兩列，如何做？解答：採用函數分割：例如：A1: name@163.comB1:=LEFT(A1,FIND("@",A1)-1) --> nameC1:=RIGHT(A1,LEN(A1)-FIND("@",A1)) --> 163.com或：數據-分列-分列-分隔符號-@就可以了 兩列合一列

現有兩列數據A列與B列，我想把B列的數據合併到A列但必須是B1單元格的數放到A1的下面，B2放到A2的下面依此類推，有什麼辦法呢？解答：=INDIRECT("r"&INT((ROW()+1)/2)& "c"&MOD(ROW()+1,2)+1,0)解釋:(一)EXCEL表中的列、行樣式有兩種：一種標記樣式為:列(欄位)以A,B,C,D......行(記錄)以數值1,2,3,4,5......第一列第一行的單元格為A1另一種標記樣式為(取ROW和COLUMN的首位字母):列(欄位)以R1,R2,R3,R4,R5......行(記錄)以C1,C2,C3,C4,C5......第一列第一行的單元格為R1C1(二)請參閱INDIRECT函數的幫助說明!!!公式:=INDIRECT("r"&INT((ROW()+1)/2)& "c"&MOD(ROW()+1,2)+1,0)等同於:=INDIRECT("r"&INT((ROW()+1)/2)& "c"&MOD(ROW()+1,2)+1,FALSE)(三)工具-->選項-->常規-->設置,還可選取R1C1引用樣式 每次清除數據時能否作到跳過隱藏的單元格

解答：F5----定位條件----常量----確定----Del或：F5－＞定位條件->可見單元格->確定->DEL 也就是單擊Sheet2時，在Sheet1的A列的最後一個記錄的下一行自動填上「End」

在sheet2:Private Sub Worksheet_Activate()dim i as integeri = Sheets("Sheet1").Cells(1, 1).CurrentRegion.Rows.CountSheets("Sheet1").Cells(i + 1, 1) = "End" End Sub 用函數將輸入的月份轉換為這個月所包含的天數

假設A1單元格為月份：＝TEXT((DATE(YEAR(NOW()),A1+1,1)-1),"d")或：=DAY(DATE(YEAR(NOW()),A1+1,0)) 介紹經驗：就SUM函數來講,以下動態地址可行

1.SUM($A$1:A2),SUM(A$1:A2)2.B2="A9",SUM(INDIRECT("a1:"&B2))3.B1="A1",B2="A9"SUM(INDIRECT(B1&":"&B2))4.B1="A1:A9"SUM(INDIRECT(B1))5.SUM(INDIRECT("A1:"&"A"&ROW()-1))6.SUM(INDIRECT("A1:"&ADDRESS(ROW()-1,COLUMN()))) 在EXCEL中如何統計字數

用{=SUM(LEN(範圍))}試試 如何自動填充內容

A1：A20是編號，B1：B20是姓名，C1：C20是性別，當我在A21單元格輸入A1：A20範圍內的任意一個編號時，B21出現對應的姓名，C21出現對應的性別。該如何做，請幫忙。解答：B21單元格公式「=IF(A21=0,"",VLOOKUP(A21,A1:C20,2,FALSE))」；C21單元格公式「=IF(A21=0,"",VLOOKUP(A21,A1:C20,3,FALSE))」這個公式也適用於A列編號不排序的情況，如果升序的話會更簡單一點。問：以上公式中的"false"有什麼用？能否省略？答：false參數主要是用它以後在A列中的數據可以不是升序排列。不然如果A列不是升序排列，公式會出錯的。 工作表的標籤的字體和大小可以更改嗎答：在桌面上點右鍵─內容─外觀，相關的設定都在此更改。 自定義格式的體會在format cell的時候，選了custom後在格子里輸入你想要的位數，不變的部分就照著打進去，會變得部分打0就好了，（用0佔位）。 例如：你要打的數字是00715834123456，後6位是不定的，那你要打在格子裡面00715834000000。這樣如果你輸入最後3位是012，那麼會顯示出00715834000012；如果你輸入54321，那麼會顯示出00715834054321。 如果你會變得部分是在數字的中間，比如我的item#會是9690000001-0000002，後面的-0000002是不變的，那我就可以設置自定義格式為9690000000"-0000002",這樣當我鍵入502的時候就會顯示9690000502-0000002。 再次顯示出被隱藏掉了的行（第1行）

1： 選中隱藏的上、下行，右擊滑鼠，選「取消隱藏」(作者註：此法可行) 2：Ctrl+A-----格式-----行-----取消隱藏(可以，能夠一次顯示所有隱藏的行或列)3：另一法(工作表處於未保護狀態):假如 A1 被隱藏了在名稱框中鍵入A1,回車按 Ctrl+Shift+0 或 Ctrl+Shift+9(只顯示選定的隱藏列或行)4：游標移到行號 4 上部變成 上下箭頭狀, 按住了, 拖也要把它拖出來!(慢，不好操作)5：選擇整個工作表（點擊左上角），然後再選擇菜單中的行，選擇最適合的行高，然後就OK!,同樣可以把隱藏的列顯示出來。(這個辦法最好，能夠一次顯示所有隱藏的行或列) 如何定義有效數字

例：取兩位有效數是從第一個不是零的數字起，取兩位。0.0023666取兩位有效數是0.0023 。0.2366取兩位有效數是0.23。解答：用函數可如下: =FLOOR(A1, SIGN(A1)*10^(INT(LOG(ABS(A1)))-1)), +/- 小數有效,0無效.其它形式的數據, 自行擴展. sheet1工作表的A1、A2、A3單元格分別鏈接到sheet2、sheet3、sheet4 解答：1、 =indirect("sheet"&row()+1&"!a1")《程香宙的解釋：indirect是把文本變為單元格引用的函數row()是取當前行號。例如在a1輸入該公式，則row()=1，公式里的值變為indirect("sheet2!a1"),跟＝sheet2!a1同效，在a2輸入該公式，則row()=2，公式里的值變為indirect("sheet3!a1")》2、使用插入----超級鏈接----書籤----(選擇)----確定 用SUMIF函數進行條件求和，不限於一個條件時如何設置參數

例如：有一個表格登記麵粉、米粉、糯米、梗米、綠豆、早米……等等的進出流水帳，如果對滿足單一條件的如麵粉、糯米、綠豆等分別求和是沒有問題的，但如果要將同一類的求和，例如將糯米、梗米、早米的數值加在一起，應該怎麼辦？ 解答：提供以下公式供參考，設A列為名稱、B列為數量：=SUMIF(A:A,"糯米",B:B)+SUMIF(A:A,"梗米",B:B)+SUMIF(A:A,"早米",B:B)如B1:D1為求和條件項，即B1="糯米",C1="梗米",D1="早米",上述公式還可改為:=SUMIF(A:A,B1,B:B)+SUMIF(A:A,C1,B:B)+SUMIF(A:A,D1,B:B) 如何在excel中已有的數值前加零變成六位

比如說 25、369、1569等，操作後變成000025、000369、00156解答：如果直接輸入的話，可以在數值前面加「"」，如「"002020」； 如果處理現成的數據，或者從別處（比如從A1單元格）鏈接來的數據，可以用公式：＝RIGHT("00000"&A1,6) 如何提取工作表中的背景圖片解答：找個乾淨的地方, 去掉網紋等不需要的東西, PrintScreen 再編輯 繪製有三條斜線的表頭解答：1、用繪圖工具畫出斜線>>畫方框>>內添加文字>>去邊框2、引用WORD中的，然後再複製過來就可以！ 在A列有一組數據不是按照大小順序排列在B列中排名解答：方法1、將A列COPY到B列，再排序。2、rank函數(=RANK(A2:A11,$A$2:$A$11,0)(假設數據在A2：A11單元格，下同)3、使用contif函數進行排列「=countif(a$2:a$11,">"&a2)+1" 有無辦法讓B2所在行都呈紅色字體解答：假設你有一個B列和一個A1的值，你的目的是，如果B2＝A1的話，整個B列都為紅色顯示！設置如下：先選定整個b列，也就是在B列列標處單擊（廢話～^_^），選擇格式－條件格式出現條件格式對話框，單擊左邊的下拉列表，裡面只有兩項，單元格數值和公式，選中公式，右邊就可以輸入任何可以返回邏輯值的公式了。輸入這個公式=($B$2=$A$1)。千萬注意要用絕對引用，因為如果是相對的，excel又自作主張的一個一個判斷了，就沒有作用了。(絕對正確並且好用) 現有12個工作表,是12張發票,建立一個匯總表,將發票號和金額匯總顯示在一張表裡(發票號和金額在每張表的相同位置).解答：在A1輸入　=INDIRECT("sheet"&ROW()&"!d3")在B1輸入　=INDIRECT("sheet"&ROW()&"!d10")再選擇A1：B1往下複製到第12行。 經驗技巧按「Ctrl+~」可以一次顯示所有公式(而不是計算結果)。再按一次回到計算結果。(程香宙) 在一個不對稱的區域中如（b1:G7）中找到A行一組數據中的某個數並自動變紅解答：其實也很簡單,你只要選定你的b1:g7，設置它的條件格式為=(COUNTIF($A$1:$A$7,b1))注意,b1為相對引用,這裡輸入所選區域的第一個取值，那樣你的所選區域會自動填充.達到你要的效果。(好) 不藉助第三列而直接用函數或公式一步得到sum(a2/b2,a3/b3,…)的結果解答：輸入=sum(a1:a100/b1:b100)，按ctrl+shift+Enter。 請問要如何算出每個月有幾個星期一、二、三….日解答：為簡單起見,表格需作一下調整,將 "星期日" 移到 C1,其後依次,這也符合規則(請參閱函數: WEEKDAY()). 。在 C2 鍵入數組公式: {=SUM(IF(WEEKDAY(DATE($A2,$B2,ROW(INDIRECT("$A$1:$A$" & DAY(DATE($A2,$B2+1,1)-1)))))=COLUMN()-2,1))}，向右複製、向下複製。公式解釋一點:ROW(INDIRECT("$A$1:$A$" & DAY(DATE($A2,$B2+1,1)-1)))實際上是從 1 號測試到本月的最後一天.如需要,公式可再作精簡。 讓隱藏的列或行不參預計算解答：使用subtotal函數,詳細用法參見幫助。 一次刪完Excel裡面多出很多的空白行解答：1、用分面預覽看看2、用自動篩選然後刪除3、用自動篩選，選擇一列用非空白，空白行就看不到了，列印也不會打出來。但是實際上還是在的，不算刪除。或者用自動篩選選擇空白將空白行全顯出來一次刪完也可以。4、先插入一列，在這一列中輸入自然數序列，然後以任一列排序，排序完後刪除數據後面的空行，再以剛才輸入的一列排序，排序後刪除剛才插入的一列。 表1、表2分別有20個人的基本情況和其中10個人的名字，讓表1的數據自動填充到表2答：1、用lookup函數即可。要保證20人不重名；2、假設表1的D列對應表2的E列。E2的公式：=VLOOKUP(B2,Sheet1!B:D,3,FALSE) 使用vlookup函數返回#N/A符號時將此符號用0或空格來代替答：這樣處理： =IF(ISNA(VLOOKUP(C13,A1:B10,2)),0,VLOOKUP(C13,A1:B10,2))或：IF(ISERROR(vlookup(a1,e1:g10,2,0)),0,vlookup(a1,e1:g10,2,0))。 通過條件格式將小計和總計的行設為不同的顏色答：輸入=RIGHT(RC,1)="計"；設定字體、邊框、圖案；確定。 複製隱藏後的表格到一個新表格中使被隱藏的內容不顯示答：crtl+g－選可見單位格－複製－粘貼。

推薦閱讀：