Excel的一些使用技巧

如何分割文本有一列數據，全部是郵箱的，現在想將@前面的賬號與@後面的域名分割開，分為兩列，如何做？解答：採用函數分割：例如：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=A9SUM(INDIRECT(B1&:&B2))4.B1=A1:A9SUM(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佔位）。 例如：你要打的數字是007

，後6位是不定的，那你要打在格子裡面007

。這樣如果你輸入最後3位是012，那麼會顯示出007

；如果你輸入54321，那麼會顯示出007

。 如果你會變得部分是在數字的中間，比如我的item#會是969000

，後面的-0000002是不變的，那我就可以設置自定義格式為969000

,這樣當我鍵入502的時候就會顯示969000

。再次顯示出被隱藏掉了的行（第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－選可見單位格－複製－粘貼。如何將一個工作簿中的一個Sheet隱藏答：1、選「格式」---「工作表」----「隱藏」2、使用VBA這樣隱藏後在使用工作表保護。Alt+F11----Ctrl+G----出現立即執行窗口，在此窗口內執行Sheet1.Visible = xlSheetVeryHidden這樣隱藏後sheet在格式---工作表----取消隱藏是看不見的。問：方法2更好哦，如何恢復呢？答：sheet1.Visible =xlSheetVisible工具菜單與視圖中的工具欄不同屏蔽工具菜單宏sub notool()MenuBars(xlWorksheet).Menus(工具).Deleteend sub解除屏蔽sub yestool()MenuBars(xlWorksheet).resetend sub Alt+F11 進入VBA 編輯、插入模塊、將上面宏複製到模塊、運行宏。OK查找並填寫符合條件的單元格內容我在工作中需快速複製每行多個數據（單元格）中最小值所對應的「標題名」，如E6是C6：Y6中的最小值，所對應的標題是E5單元格「某某公司」，要將其（某某公司）複製到B6單元格中，以此類推的複製很多很多行的內容。如果是手工一個一個查找與複製，實在是太慢太笨了，能否使用一個簡單的公式計算呢？答：B6單元格=INDEX(C$5:Y$5,MATCH(MIN(C6:Y6),C6:Y6,0))　填寫空白行我有個同事在一張空白表依次輸入數據，為了省事她把和上一格內容相同的的省略不輸，輸了近200行。後來又覺得不夠正式，想把空白的地方補上。她來問我怎麼辦好。當然依次填充也行，但我覺得煩（如果有2000行怎麼辦呵呵）我想了一個不是辦法的辦法：在A列和B列旁各插入一列，現在就有ABCDE列，我在B2中複製了A2中的內容，然後在B3中輸入公式：IF(A3=0,B2,A3),然後往上往下複製公式。這樣就填滿了。如法炮製D列後隱藏AB列感覺就可以了。可是也煩啊，誰有更好的辦法？答：1、Sub feifjeifjeifjeifjeifjiefjiejfiejf()For i = 2 To ActiveSheet.Range(a1).CurrentRegion.Rows.CountIf IsEmpty(Cells(i, 1)) ThenCells(i, 1).FormulaR1C1 = Cells(i - 1, 1).ValueEnd If NextEnd Sub2、篩選出空白行，輸入公式=INDIRECT(a&ROW()-1)，填充制訂下月計劃並顯示為中文我在五月份做六月份的計劃，為減少工作量和更改的麻煩，我做模板並使用了公式=計劃期：&YEAR(NOW())&年&(MONTH(NOW())+1)&月，結果如A1所示 計劃期：2002年6月（現在的系統日期是2002年5月）。 如果我想自動得到如A2中的結果 計劃期：二○○年六月 ，請問要如何做才行，我設置了單元格的日期格式還是不行。解答：1、先設置單元格格式為二○○二年六月那種類型，然後用如下公式：=DATE(YEAR(NOW()),(MONTH(NOW())+1),20)就可以了。2、使用這個函數吧！ =EDATE(NOW(),1)。單元格格式應設置為：日期----一九九七年三月。3、設置單元格格式為：[DBNum1]計劃期：yyyy年m月 ，然後直接輸入日期值(如2002/11)即可。輸入公式也可以。如=today()+30，可以得到下個月的月份。&的用法有E44單元格，我希望 總計：=SUM(E45:E49) 就是想讓它經過自動求和後在一個單元格內顯示總計：120 。解答：有多種方法實現，詳細如下：1、=總計：&sum(e45:e49)2、把E44格式設為總計:#0.00;總計:-#0.00;總計:0.00;@3、將E44單元格格式自定義為 總計：0.000 即可,方便對E44的引用計算4、=CONCATENATE(合計：,SUM(e45:e49))有5行數據在每行上面個插入1行解答：1、在最左邊插入一列，然後輸入1、2、3、4、5、1、2、3、4、5，並以此列進行排序，在第一行上面再插入一行，刪除剛剛插入的列。2、使用Ctrl+滑鼠一行一行選定，然後插入行。3、sub 插入行（）for i=1 to 6if cells(i,1).value <> Cells(i + 1, 1) And Cells(i, 1) <> ) ThenRows(i + 1).Insertend ifnext iend sub 可以檢查一張表裡是否有漏重的數字嗎答：漏值：{=IF(SUM((R1C1:R10C4=)*1)>0,有漏值,無漏值)}重複值：{=SUM(SUM((漏值!R1C1:R10C4=漏值!RC)*1))}{=IF(MAX(R1C1:R10C4)>1,有重複值,無重複值)}使用下面公式更方便：找重複值-------{=IF(SUM((COUNTIF(R1C1:R10C4,R1C1:R10C4)>1)*1)>1,有重複值,無重複值)}找 漏 值-------{=IF(SUM((R1C1:R10C4=)*1)>0,有漏值,無漏值)}注意：這兩個公式均為數組，輸入時應同時按Ctrl+Shift+Enter。怎樣將單元格中的公式轉換為數值解答：選中公式的一部分，按F9鍵條件求和有這樣一個表格A　　　　B　　　　　C 1 2 3 /2 2 3 /3 2 3 /4 2 3 /5 2 3/6 2 3 /如何才能求出滿足A列中大於2且小於5的B列和C列數值的和，要求B列和C列的值相加。用sumif函數似乎條件中只能設定為＞2，而不能同時設定＜5，而且在求和時只能B列相加，不能把B列和C列滿足條件的值加起來。這個問題能不能只用函數，不用數組公式解決。請各位指教。解答：1用公式：=SUM(IF(($A$1:$A$6>2)*($A$1:$A$6<5),B1:C6))2用數組公式：{=SUM(IF($A$2:$A$7>2,IF($A$2:$A$7<5,$B$2:$B$7,0),0))+SUM(IF($A$2:$A$7>2,IF($A$2:$A$7<5,$C$2:$C$7,0),0))}A1單元格為出生日期，可用=DATEDIF(A1,NOW(),y)計算其年齡這個公式是什麼意思？K7=if(AND(R7>3000, Q7>0.5), , P7)意思是：如果R7單元格中的數值大於3000，並且Q7單元格中的數值大於0.5，則在K7單元格中顯示空白，否則顯示出P7單元格中的數據。統計數據問題一例各位朋友，如果我想統計50個數據中大於某個值的數據個數，（這個值是在使用時才輸入某個單元格的），請問用什麼函數，如何實現，謝謝。 如數據單元格為A1：E10，值的單元格為A11。答：1、使用下面的數組公式： {=SUM(IF($A$1:$E$10>$A$11,1))}2、輸入以下函數： =COUNTIF(A1：E10,>&A11)關於條件求和問題！有A，B，C，三列數據，如果A列符合要求，求B1*C1+B2*C2+......?答：使用數組公式： {=SUM((R2C1:R13C1=ab)*(R2C2:R13C2)*(R2C3:R13C3))}請教關於條件乖積的求和問題A列為部門名稱，B列為姓名，C列為日工資額(如20.00)，D列為月出勤天數，我想在另一匯總表中匯總出各部門員工月工資總額(即：相應部門對應的C*D之和)。請問如何解決？解答：1、=SUM((A4:A10=甲部門)*(C4:C13)*(D4:D13))假設你的a列存放部門名稱，你的b列存放員工姓名，C列存放日工資，D列存放天數。計算「甲部門」的工資總額。注意，這是數組公式，輸入完畢後按ctrl+shift+回車問：我試著把區域引用改為整列，出現錯誤，請指點!=SUM((date!A:A=甲部門)*(date!E:E)*(date!F:F))答：經試驗，不能用整列方式，你可以適當的調整一個比較大的區域如a2:a100 a1為標題行因為如果參與計算的e列和f列區域出現文本，也會發生錯誤。一定要把計算區域的標題行去掉，並且保證不再數值區域出現文本。=SUM((date!A2:A100=甲部門)*(date!C2:C100)*(date!D2:D100))這個公式沒有錯誤文件修復Excel文件是一個工作簿，一般可以包含255個工作表，每個工作表中可以包含大量的數據。如果一個Excel文件部分受損，不能正常打開，該怎麼辦呢？這裡向大家介紹兩種解救方法。　　手動處理　　進入Word，打開要修復的XLS文件，如果Excel只有一個工作表，會自動以表形式裝入Word，若文件是由多個工作表組成，每次只能打開一個工作表。打開後，先將文件中損壞的數據刪除。　　用滑鼠選中［表格］→［轉換］→［表格轉文本]，注意可用「，」間隔符或其它分隔符，另存為一個TXT文本文件。在Excel中直接打開該文本文件，在打開時，Excel會提示文本導入嚮導，一般情況下只要直接點擊［下一步］即可，打開後另存為其它的Excel文件即可。　　注意：這種修復的方法是利用Word的直接讀取Excel文件的功能實現，該方法在文件頭沒有損壞，只是文件內容有損壞的情況下比較有效；對文件頭已經損壞的Excel文件，此方法可能不成功，必須藉助於其它方法。　　用Excel修復工具　　Concept Data公司提供了一個專門用於修復Excel損壞文件的修復工具——ExcelRecovery，能夠對Excel 5.0/97/2000的文件進行恢復處理，大家可以先下載該軟體的免費演示版試用，僅有674KB，下載地址http://download.sina.com.cn。軟體安裝後，可以自動將Excel的修復程序加在Excel應用程序中，在「文件」菜單下多出一項「Recovery」選項，若有損壞的文件，可以用該選項進行修復。另一個下載地http://www.skycn.com/soft/7432.html　　使用方法是：　　1、打開Excel；　　2、單擊「文件」菜單下的「Recover」選項，Excel Recovery對話框將打開　　3、指定要修復的Excel文件，然後按[Recover]按鈕；　　4、自動修復；　　5、另存為一個新的文件名，即可完成文件的修復工作。　　注意：該演示版本只能修復普通文件，不能修復帶Visual Basic代碼、圖表以及包含口令的Excel文件，如果需要修復這些文件，請進行註冊。　　另外要提醒大家的是：保存在軟盤中的文件極易損壞，受損後可用以上方法修復，但如果損壞的文件位於磁碟0磁軌時，就必須先修復軟盤，再用上述方法修復Excel文件。另外，考慮到未註冊版本的使用限制，我找到如下破解頁面，你自己再研究研究Results 4 of about ExcelRecovery URL: http://www.cracks.am/cracks/e4.html ExcelRecovery v3.0 ExcelRecovery v2.2 URL: http://www.thecrack.net:8080/db/list.php... 2064. ExcelRecovery 2.2 12 Kb 2065. ExcelRecovery 2.2.1 12 Kb 2066. ExcelRecovery v3.0 (SirCrack) 12 Kb URL: http://crck.passwd.ws/e.htm ExcelRecovery v3.0 - 11 Kb URL: http://cracks.allseek.info/crc/?p=E5 1255. ExcelRecovery 3.0 by SirCrack [ 11 Kb ] 顯示隱藏的工作表有個朋友給了我一個EXCEL程序，裡面只有2個SHEET，但是我見到其中一個SHEET還引用了本文件另外一個工作表的內容，我用ALT+F11打開VB編輯器又可以看到那個隱藏了的工作表， 請問怎麼能看到那工作表呢？答：Sheets(name_of_sheet).Visible = True這樣一列如何篩選出含201的 縣一高2014555 /便民201號 /縣城301號/201414441/301745555/20145122 /柏良201 /柏良301答：假設你的資料在B列，且起始行為第四行，即B4起始單元格，終止與11行處，即B11，則在A列輸入公式IF(ISERROR(FIND(201,B4)),A3,A3+1),然後在D列輸入順序數字，比如1到10，在E列輸入公式vlookup(d4,$a$4:$b$11,2,1>2)。注意A3為0。或：自動篩選->單擊下拉列表->custom(自定義)->在條件中選包含,在右邊的文字框輸入201->OK兩個日期相差的月份數使用Datedif(日期1，日期2，m)函數。用函數實現連續相加我有一公式是這樣的：=sum(B1+B2+B5+B8+B11+B14+B17+B20) 也就是前兩個是連續相加，後邊的是每隔3個相加，不知有沒有簡單的公式。答：{=SUM((MOD(ROW(B1:B20),3)=2)*B1:B20)+B1}把計算結果為負值的顯示為紅色取整並在數字後面加上「仟元「把單元格格式改為#,##0仟元;[紅色]-#,##0仟元如果不要負號可以寫成#,##0仟元;[紅色]#,##0仟元比較A、B兩列數據並在A列中包含B列的數據刪除工作表中A列數據是原始數據，而B列是另一些數據，現在要比較兩列的情況，然後將A列中包含B列的數據刪除Sub wswx0041()Dim i&, j&On Error Resume NextFor j = Range(B65536).End(xlUp).Row To 1 Step -1For i = Range(A65536).End(xlUp).Row To 1 Step -1If Cells(i, 1) Like * & Cells(j, 2) & * And Not IsEmpty(Cells(j, 2)) Then Cells(i, 1).Delete shift:=xlUpNext iNext jEnd Sub怎樣讓我的圖表隨著數據透視表的更新我的問題是： 1、當有新月份的數據出現的時候，圖表無法包含新數據；2、但REGION選擇不是全部，比如CD,圖表中沒有數據。怎樣實現圖表隨數據透視表的更新而更新呢？解答：DATA=Data!$A$1:$G$129、固定的範圍,不值錢、DATA=OFFSET(Data!$A$1,,,COUNTA(Data!$A:$A),6)動態範圍、按數據透視表[!]按鈕,更新數據 Sales Date；改用年月日三個欄位；目的是當[索引],讓條件容易下；DATA=OFFSET(Data!$A$1,,,COUNTA(Data!$A:$A),8)。直接由[數據透視表]工具,[圖表精靈]出,[圖表]如此就是完美的三層式結構，分工完成工作，只不過是接條龍，很簡單吧! 又問：每次刷新數據，列寬等格式就需要重調，有沒有辦法將格式固定住？答：工具>>選項>>一般>>標準字型>>大小>>確定。設定好以後,開新檔案,列寬行高自動會調整。但這不是重點 [數據透視表]；滑鼠右鍵>>分頁顯示。此功能是用來列印整本活頁簿,出報表用雙擊總計欄位下之單元格；此功能是用來列出該筆合併數據所有明細；所以[自動篩選][進階篩選]就用不著了,也不必去寫複雜之[數組公式]；需要甚麼數據,直接找[數據透視表]要；操作非常簡單DATA可以轉到mbd檔；ACCESS可以匯入Excel數據,用精靈操作；匯入以後xls檔即可刪除Excel>>數據>>取得外部數據>>新增外部數據查詢>>>>>>轉入[數據透視表]；此物即為Microsoft Query第一次使用會要求放入光碟片,安裝ODBC驅動程序改用年月日三個欄位；是有道理的。別嫌麻煩循環引用問題vba中輸入公式，我想用變數代替RC地址，以便循環操作，如何做到？比如：在Range(am6).FormulaR1C1 = =SUM(R[-5]C:R[-5]C)中，如何才能用變數x來代替其中的10或-5等數字？ 解答：==SUM(R[ & x & ]C[ & y & ]:R[ & xx & ]C[ & yy & ]) 如何才能有條件的引用某一區的內容請教各位，怎樣才能引用符合條件的某一區域的內容。如下： A　　　B　　　C　　　 9-1　　1　　　9-15 /9-2　　2　　　9-16 /9-2　　2　　　9-17 /9-2　　1　　　9-18 /9-4　　2　　　9-18 請問，用什麼方法才能在另外的單元格中引用同時符合A列中「9-2」和B列中「2」的C列的內容。解答：先判斷個數，再列出符合的數據：{=IF(ROW()-ROW($A$18)+1>SUM(($A$2:$A$7=$B$16)*($B$2:$B$7=$C$16)),,TEXT(SMALL(IF(IF($A$2:$A$7=$B$16,$B$2:$B$7,)=$C$16,$C$2:$C$7,),ROW($A1)),m月d日))}Excel基本功1.引用同一工作薄中另外一工作表的單元格 = Worksheets(工作表名稱).Cells(1, 1) ------------------------------------------------------------ 如當前工作表為sheet1，想引用sheet2中的B2,則=sheet2!B2 2.如何使0值不顯示 方法1：if (A1<>, A., ) 方法2：對於整個工作表中的0值全不顯示，[工具]-[選項]-[視圖]，清除0值選項。 方法3：用自定義數字格式，其中#和?有屏蔽0值的效果。比如：G/通用格式;G/通用格式;#，G/通用格式;G/通用格式;?。 3.如何定義格式和Copy格式? 在EXCEL中,可不可以把某一範圍定義成一種格式,而其中某些數據又不是.並且又如把這種格式COPY到同一張sheet1中?如: 大製程 標準工時 狀況 2002/1/24 2002/1/25 二次 13 /焊接 20 .而其它空格就不包括在格式中.被填寫了的這張表格的格式又能重新COPY.如何做呢? 解答：新建一個只有一個工作表的工作簿 ,在這個工作表中設計你的格式另存為模板（*.xlt）。以後插入新工作表時，選擇剛才建的模板。 又問：但是假如我只有一張工作表,我如何在同一張工作表中COPY呢?如我的固定格式在sheet1的A1:Z15範圍內,我要把這種格式copy到A17:Z24,而其中的數據又不COPY進來.怎麼辦?解答：假設你要複製格式的範圍為A1:Z1和A1:B15兩個區域，則用下面的宏能實現你的複製要求。 你只要選定新區域左上角的單元格，然後執行宏即可。 Sub 宏1() aaa = ActiveWindow.RangeSelection.Address Range(A1:Z1).Select Selection.Copy Range(aaa).Select ActiveSheet.Paste Range(A1:B15).Select Application.CutCopyMode = False Selection.Copy Range(aaa).Select ActiveSheet.Paste Application.CutCopyMode = False Range(aaa).Select End Sub ------------------------------------------------------------ 如果A1:Z15中的數據全部都要清除， 可以[複製]A1:Z15，[選擇性粘貼]到A17，粘貼欄中選中格式。 ------------------------------------------------------------ 選定範圍後直接使用格式刷就可以了 4.如何實行列互換？ 解答：Transpose函數 或選中一行或一列，複製，選擇性粘貼－＞轉置。 ------------------------------------------------------------ 在某一單元格輸入公式「INDEX(reference,COLUMN(A1),ROW(A1))」 然後向下向右拖放。 5.EXCEL2000中視面管理器如何具體運用呀？ 請問高手EXCEL2000中視面管理器如何具體運用呀？ ------------------------------------------------------------ 其實很簡單呀，你把它想像成運動場上的一串照片（記錄不同時點的場景）， 一張照片記錄一個場景，選擇一張照片就把運動「拖」到照片上的時點。 不同的是只是場景回復，而值和格式不回復。解除officeXP 50 次限制1、 首先使用下面這個序號來安裝Office XP。 BMV8D-G272X-MHMXW-4DY9G-M8YTQ 2、安裝後，啟動新建一個Word文檔，程序會提示你激活，不管它！關閉Word。 3、打開註冊表編輯器REGEDIT.exe，進入到下面的子鍵裡面： [HKEY_LOCAL_MACHINESOFTWAREMicrosoftWindowsCurrentVersionInstallerProducts4080820900063D11C8EF00054038389CAlways Installed] 4、把右邊的這個DWORD類型的鍵刪除：Usage=dword:2adb0001 5、在右邊新建這樣一個字元串型的鍵：Usage，將其值改為AlwaysInstalled 即： Usage=AlwaysInstalled 6、最後使用法國人寫的位元組數為12,800的Crack.exe來破解Office XP後，即大功告成！ 我破解後，打開Word 70次，打開Excel 60次後，還是能夠新建文檔、表格，而且可以順利保存。 無論Win98或Win2000操作系統我都成功了,且已運行了200次以上! 小寫數字轉換成人民幣大寫方法1、Function UNumber(LNumber As Double)Dim NumberStr$Dim NumberLen%Dim DotLoc1%Dim DotLoc2%Dim NumberStr1$Dim NumberStr2$If LNumber = 0 ThenUNumber = 零元整Exit FunctionEnd IfNumberStr = Application.WorksheetFunction.Text(LNumber, [DBnum2])NumberLen = Len(NumberStr)DotLoc1 = InStr(1, NumberStr, ., vbTextCompare)DotLoc2 = NumberLen - DotLoc1If DotLoc1 = 0 ThenNumberStr = NumberStr & 元整ElseNumberStr = Replace(NumberStr, ., 元)If DotLoc2 = 2 ThenNumberStr = NumberStr & 分If Mid(NumberStr, DotLoc1 + 1, 1) <> 零 ThenNumberStr1 = Mid(NumberStr, 1, DotLoc1 + 1) & 角NumberStr2 = Right(NumberStr, 2)NumberStr = NumberStr1 & NumberStr2End IfElseNumberStr = NumberStr & 角整End IfEnd IfOn Error Resume NextUNumber = Replace(NumberStr, 零元, )End Function方法2、=IF(TRUNC(H16)=H16,TEXT(H16,[DBNum2]G/通用格式)&元整,TEXT(TRUNC(H16),[DBNum2]G/通用格式&元))&IF(AND(TRUNC(H16)<>H16,RIGHT(TRUNC(H16*10))<>0),TEXT(TRUNC(MOD(H16*10,10)),[DBNum2]G/通用格式)&角,)&IF(AND(RIGHT(TRUNC(H16*10))=0,TRUNC(H16)<>H16),零,)&IF(TRUNC(H16*10)<>H16,TRUNC(H16*10)=H16*10), 整,)方法3、=IF(F10=0,,CONCATENATE(IF(INT(F10)=0,,TEXT(INT(F10),[DBNum2]G/通用格式元)),IF(INT(MID(RIGHT(FIXED(F10,2,1),2),1,1))=0,IF(INT(MID(RIGHT(FIXED(F10,2,1),1),1,1))=0,,IF(INT(F10)=0,,零)),TEXT(INT(MID(RIGHT(FIXED(F10,2,1),2),1,1)),[DBNum2]G/通用格式角)),IF(INT(MID(RIGHT(FIXED(F10,2,1),1),1,1))=0,整,TEXT(INT(MID(RIGHT(FIXED(F10,2,1),1),1,1)),[DBNum2]G/通用格式分))))方法4、人民幣大寫的函數公式，可正負，最多兩位小數。=IF(A1<0,負,)&IF(TRUNC(A1)=A1,TEXT(IF(A1<0,-A1,A1),[DBNum2])&元整,IF(TRUNC(A1*10)=A1*10,TEXT(TRUNC(IF(A1<0,-A1,A1)),[DBNum2])&元&TEXT(RIGHT(A1),[DBNum2])&角整,TEXT(TRUNC(A1),[DBNum2])&元&IF(ISNUMBER(FIND(.0,A1)),零,TEXT(LEFT(RIGHT(A1,2)),[DBNum2])&角)&TEXT(RIGHT(A1),[DBNum2])&分))方法5修改一下4：根據劍魔兄的測試，發現有一個問題，如-100.05，現修正如下：=IF(A1<0,負,)&IF(TRUNC(A1)=A1,TEXT(IF(A1<0,-A1,A1),[DBNum2])&元整,IF(TRUNC(A1*10)=A1*10,TEXT(TRUNC(IF(A1<0,-A1,A1)),[DBNum2])&元&TEXT(RIGHT(A1),[DBNum2])&角整,TEXT(TRUNC(IF(A1<0,-A1,A1)),[DBNum2])&元&IF(ISNUMBER(FIND(.0,A1)),零,TEXT(LEFT(RIGHT(A1,2)),[DBNum2])&角)&TEXT(RIGHT(A1),[DBNum2])&分))方法6=IF(A1<0,負,)&TEXT(TRUNC(ABS(A1)),[DBNum2]G/通用格式)&元 &IF(ROUND(A1,3)=ROUND(A1,),整,TEXT(RIGHT(TRUNC(A1*10),1),[DBNum2]G/通用格式)&角&IF(ROUND(A1,3)=ROUND(A1,1),整,TEXT(RIGHT(ROUND((A1*100),),1),[DBNum2]G/通用格式)&分))方法7無條件捨去： =CONCATENATE(IF(A1<0,負,),TEXT(IF(TRUNC(A1)=0,零,TRUNC(ABS(A1))),[DBNum2])&元,IF(OR(AND(ABS(A1)<0.1,TRUNC(A1)=A1),RIGHT(INT(ABS(A1)*100),2)=00),,TEXT(RIGHT(TRUNC(A1*10),1),[DBNum2])),IF(RIGHT(TRUNC(A1*10),1)=0,,角),IF(OR(TRUNC(A1*10)-(A1*10)=0,RIGHT(TRUNC(A1*100),1)=0),整,TEXT(RIGHT(TRUNC(A1*100),1),[DBNum2])&分))小數點後兩位四捨五入: =CONCATENATE(IF(A1<0,負,),TEXT(IF(TRUNC(ROUND(A1,2))=0,零,TRUNC(ABS(ROUND(A1,2)))),[DBNum2])&元,IF(TRUNC(ROUND(A1,2))=ROUND(A1,2),,TEXT(RIGHT(TRUNC(ROUND(A1,2)*10),1),[DBNum2])),IF(RIGHT(TRUNC(ROUND(A1,2)*10),1)=0,,角),IF(OR(TRUNC(ROUND(A1,2)*10)-(ROUND(A1,2)*10)=0,RIGHT(ROUND(A1,2),1)=0,TRUNC(ROUND(A1,2))=ROUND(A1,2)),整,TEXT(RIGHT(ROUND(A1,2),1),[DBNum2])&分))方法8無條件捨去： =IF(A1<0,負,)&SUBSTITUTE(TEXT(TRUNC(A1),[DBNum2])&元&IF(ISNUMBER(FIND(.,TRUNC(A1,2))),TEXT(RIGHT(TRUNC(A1*10)),[DBNum2])&IF(ISNUMBER(FIND(.0,A1)),,角),)&IF(LEFT(RIGHT(TRUNC(A1,2),3),1)=.,TEXT(RIGHT(TRUNC(A1,2)),[DBNum2])&分,整),-,)小數點後兩位四捨五入: =IF(A1<0,負,)&SUBSTITUTE(TEXT(TRUNC(ROUND(A1,2)),[DBNum2])&元&IF(ISNUMBER(FIND(.,ROUND(A1,2))),TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),[DBNum2])&IF(ISNUMBER(FIND(.0,ROUND(A1,2))),,角),)&IF(LEFT(RIGHT(TRUNC(ROUND(A1,2),2),3),1)=.,TEXT(RIGHT(ROUND(A1,2)),[DBNum2])&分,整),-,)方法9再簡化如後，請大家試試。無條件捨去：=IF(A1<0,負,)&TEXT(TRUNC(ABS(A1)),[DBNum2])&元&IF(ISERR(FIND(.,TRUNC(A1,2))),,TEXT(RIGHT(TRUNC(A1*10)),[DBNum2]))&IF(RIGHT(TRUNC(A1*10))=0,,角)&IF(LEFT(RIGHT(TRUNC(A1,2),3))=.,TEXT(RIGHT(TRUNC(A1,2)),[DBNum2])&分,整)小數點後兩位四捨五入:=IF(A1<0,負,)&TEXT(TRUNC(ABS(ROUND(A1,2))),[DBNum2])&元&IF(ISERR(FIND(.,ROUND(A1,2))),,TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),[DBNum2]))&IF(ISERR(FIND(.0,TEXT(A1,0.00))),角,)&IF(LEFT(RIGHT(ROUND(A1,2),3))=.,TEXT(RIGHT(ROUND(A1,2)),[DBNum2])&分,整)方法10(好)Function RMB_DX(money As Variant)Dim m1 As Long, n1 As Long, n2 As Integer, n3 As Integerm1 = Application.WorksheetFunction.Round(money * 100, 0)n1 = Int(m1 / 100)n2 = Int(m1 / 10) - n1 * 10n3 = m1 - n1 * 100 - n2 * 10If n3 = 0 ThenRMB_DX = 整ElseRMB_DX = Application.WorksheetFunction.Text(n3, [DBnum2]) & 分End IfIf n2 = 0 ThenIf n1 <> 0 And n3 <> 0 Then RMB_DX = 零 & RMB_DXElseRMB_DX = Application.WorksheetFunction.Text(n2, [DBnum2]) & 角 & RMB_DXEnd IfIf n1 <> 0 Or m1 = 0 ThenRMB_DX = Application.WorksheetFunction.Text(n1, [DBnum2]) & 元 & RMB_DXEnd IfEnd Function方法11unction rmbdx(value, Optional m = 0)"支持負數,支持小數點後的第三位數是否進行四捨五入處理"默認參數為0,即不將小數點後的第三位數進行四捨五入處理"redwin增改 2002-10-11"Application.Volatile TrueOn Error Resume NextDim aIf value < 0 Thena = 負Elsea = End If"當參數m不輸入(默認為0)或為0時,小數點後的第三數不進行四捨五入處理"當參數m為1或其它數值時,小數點後的第三數進行四捨五入處理value = CCur(Abs(value))If m = 0 Thenvalue = Fix(value) + (Fix((value - Fix(value)) * 100)) / 100Elsevalue = Round(value, 2)End Ifstrrmbdx = Application.WorksheetFunction.Text(Int(value), [DBNum2]) & 元"防止出現零元的bug!If value >= 1 Thenstrrmbdx = strrmbdxElsestrrmbdx = End IfstrBal = Str(value)If Int(value) <> value ThenstrLastvalue = Left(Right(strBal, 2), 1)If strLastvalue = . ThenstrLastvalue = Right(strBal, 1)strLast = Application.WorksheetFunction.Text(strLastvalue, [DBNum2]) & 角整ElseIf strLastvalue = 0 And strLastvalue <> 0 ThenstrLast = 零Else"防止出現零角幾分的bug!If strrmbdx = And strLastvalue = 0 Then"strLast = Application.WorksheetFunction.Text(strLastvalue, [DBNum2]) & 角strLast = ElseIf strrmbdx <> And strLastvalue = 0 ThenstrLast = 零ElsestrLast = Application.WorksheetFunction.Text(strLastvalue, [DBNum2]) & 角End IfEnd IfEnd IfstrLastvalue = Right(strBal, 1)If strLastvalue = 0 ThenstrLast = 整ElsestrLast = strLast & Application.WorksheetFunction.Text(strLastvalue, [DBNum2]) & 分End IfEnd Ifstrrmbdx = strrmbdx & strLastElsestrrmbdx = strrmbdx & 整End Ifrmbdx = a & strrmbdxEnd Function方法12=IF(ISTEXT(C2),,人民幣：&TEXT(INT(C2),[dbnum2])&元&IF(INT(C2*10)-INT(C2)*10=0,,TEXT(INT(C2*10)-INT(C2)*10,[dbnum2])&角)&IF(INT(C2*100)-INT(C2*10)*10=0,整,TEXT(INT(C2*100)-INT(C2*10)*10,[dbnum2])&分))
推薦閱讀：