Excel技巧

06-05

Excel技巧

1、Excel圈注表格中的無效數據：數據輸入完畢後，為了保證數據的真實性，快速找到表格中的無效數據，我們可以借用Excel中的數據有效性和公式審核來實現。選中某列（如B列），單擊[數據有效性]對話框，切換到[設置]選項卡，輸入符合條件的數據必須滿足的條件範圍（如「=and(B1>=60,B1<90) 」）。點擊[工具]—[審核]—[顯示「審核」工具欄]，單擊工具欄中的[圈釋無效數據]按鈕，此時表格中的無效數據都被清清楚楚地圈注出來了。2、隱藏表格中的出錯信息：大家經常會發現表格在處理完數據後出現一些類似「#NAME？」等出錯信息，既不方便列印又影響了表格的整體美觀，在數據量比較大的時候手工刪除顯然是不現實的。解決方法是：列印時，打開[頁面設置]對話框，切換到[工作表]選項卡，將「錯誤單元格列印為」選項設置為「空白」或「-」就好了。計算時，可使用通用公式「=IF（ISERROR（公式）,」」,公式）」，也能使運算過程中出錯單元格填充為指定的字元或空白。3、兩個日期之間的天數、月數和年數：計算兩個日期」1995-5-12」和」2006-1-16」之間的天數、月數和年數可使用下面的公式：「=DATEDIF（A1,B1,」d」）」，其中A1和B1分別表示開始日期和結束日期，」d」表示天數，換為」m」或」y」就可以計算兩個日期相差的月數和年數了。4、Excel中限制重複數據錄入：在Excel中錄入數據時，有時會要求某列單元格中的數據具有唯一性，例如身份證號碼、發票號碼之類的數據。為了保證數據的唯一性，我們可以這樣做：選定目標單元格區域（這裡假設為A1：A10），依次單擊[數據]—[有效性]，打開[數據有效性]對話框，在[設置]選項卡中單擊[允許]下拉列表，選擇[自定義]，然後在[公式]中輸入「=COUNTIF($H$1:$H$10,$H1)=1」，接著，單擊切換到[出錯警告]選項卡，在[樣式]中選擇[停止]，然後分別在[標題]和[錯誤信息]中輸入錯誤提示標題和信息。設置完畢後單擊[確定]退出。此時，我們再在目標單元格錄入數據時，Excel就會自動對數據的唯一性進行校驗。當出現重複數據時，Excel中會出現前面設置的錯誤提示信息。5、Excel錄入時自動切換輸入法：在Excel單元格中，經常遇到中英文交替輸入的情況，如A列輸入中文而B列卻輸入英文，這時就要在中英文輸入法之間反覆切換，這樣非常麻煩而且嚴重影響錄入效率。其實可以先打開中文輸入法，選中需要輸入中文的列，執行菜單[數據]—[有效性]，在[數據有效性]中切換到[輸入法模式]標籤分頁，在[模式]下拉列表中選擇[打開]，確定退出。接著選擇需要輸入英文的列，同樣打開[輸入法模式]標籤分頁，在[模式]下拉列表中選擇[關閉（英文模式）]，確定後退出即可。6、Excel中粘貼時避免覆蓋原有內容：在工作表中進行複製或移動操作時，粘貼的內容將自動覆蓋工作表中的原有內容，怎樣避免這一現象呢？首先選中要複製或移動的單元格，單擊複製或剪切按鈕，選中要粘貼的起始單元格，按下「Ctrl+Shift+ +」組合鍵，在彈出的「插入粘貼」對話框中選擇活動單元格移動的方向，單擊「確定」按鈕就可以了。另外如果按下「Ctrl+Shift+ —」組合鍵即可完全刪除Excel中的單元格。7、Excel中排出並列的名次：Excel中排一個順序遞增1的名次是很簡單的，按關鍵字排序即可；在名次列的第一個單元格內輸入1，第二個單元格內輸入2；選中這兩個單元格，在選框右下角的填充柄上雙擊即可。但這種名次，當有數據並列時就不合理了。下面我們以某班學生總分名次為例（存在大量總分相同的學生），介紹一下我平時用的排名次方法：1）按總分「降序」排序；2）在「總分」欄位右側添加「名次」欄位，在「名次」欄位第1個單元格即D2中輸入1，在第2個單元格即D3中輸入下列公式：=IF(EXACT(C2,C3),D2,ROW(C3)-1)，回車確認，剩下的單元格用D3單元自動填充即可；3）選中「名次」列，複製，然後單擊菜單中的[編輯]—[選擇性粘貼]，選中[數值]項後[確定]，至此大功告成。

8、Excel中妙用「條件格式」讓行列更清晰：在用Excel處理一些含有大量數據的表格時，經常會出現錯行錯列的情況，其實可以將行或列間隔設置成不同格式，這樣看起來就清晰明了得多。利用[條件格式]可以輕鬆地達到目的。先選中數據區中所有單元格，然後選擇菜單命令[格式]—[條件格式]，在打開的對話框中，在最左側下拉列表中單擊[公式]，然後在其右側的輸入欄中輸入公式「=MOD(ROW(),2)」，然後單擊下方的[格式]按鈕，在彈出的[單元格格式]對話框中單擊[圖案]選項卡，為單元格加上外邊框，然後一路單擊[確定]按鈕即可。如果希望第一行不添加任何顏色，只須將公式改為「=MOD(ROW()+1,2)」即可。如果需要間隔兩行添加填充顏色只須將公式改為「=MOD(ROW(),3)」就可以，以此類推。如果我們希望讓列間隔添加顏色，那麼只須將上述公式中的「ROW」改為「COLUMN」就可以達到目的。　

9、Excel中單變數求解：單變數求解的意義是：已知公式的值，求產生此值的自變數值。界面非常簡單，實際上單變數求解的功能本質就是用於求解一元方程的自變數X值。明白了這個道理，只要是一元方程的問題的問題就都可以套用，無論是一元一次還一元多次方程都可以解決。選擇[工具]—[單變數求解]可以打開如圖1所示的窗口，其中，[目標單元格]指的是公式（方程）所在的單元格，[目標值]指的是方程的值，[可變單元格]指的是方程的自變數X所在的單元格。例1：求解方程：X2+2X+1=9。由圖2可以看出方程輸入在B6單元，X變數放在B5單元，這樣方程實際轉變為B5*B5+2*B5+1=9，得出圖3所示單變數求解窗口中所選擇的。通過上面例子可以看出單變數求解的確可以求解一元方程自變數X的值。下面再通過一個實際例子把它轉化為一元方程，然後利用單變數求解來解決。例2：計算機考試分為筆試和上機兩部分，都是百分制。其中筆試80分，佔總分的60%，若總分要達到90分以上，上機須多少分？總分是否能達到此要求？設上機成績為X，方程為：80*60%+X*（1-60%）=90。根據圖4各數據所在的單元格地址，方程轉化為B4*B5+B6*（1-B5）=90。根據上述經驗，圖4的公式中沒有具體的數值。如果筆試成績或筆試比率任何一個參數發生變化，公式的值都會相應地改變。其中方程的自變數X在B6單元格，方程輸入在B7單元格，目標值是90分，從而得出如圖5所示的單變數求解窗口。綜上所述，單變數求解的本質是求解一元方程，所以現實應用中凡是可以轉換為一元方程的問題都可以用單變數求解解決。

10、Excel中模擬運算：在Excel中模擬運算分為單變數模擬運算和雙變數模擬運算，模擬運算功能是指公式中一個或兩個變數變化時對公式值的影響。功能描述看似簡單，但是如何能把實際問題利用模擬運算解決呢？單變數模擬運算的功能本質是解決一元方程中自變數X在給定的取值集合中變化時，方程值的變化。雙變數模擬運算是解決二元方程的自變數X、Y在給定的取值集合中變化時，方程值的變化。這樣可以找到一個最佳方案。所以模擬運算仍然是求解方程問題，在使用模擬運算功能時模擬運算表的正確構造是關鍵。下面通過舉例來解釋：例1：計算機考試，分為筆試、上機兩部分，都是百分制。其中上機80分，筆試90分，上機佔總分比率可以分為：20%、30%、40%、50%、60%、70%、80%時，Z的值分別是多少？設上機比率為X，總分為Z，方程是：Z=80*X+90（1-X）。這樣問題就轉化為求當自變數X取值分別為20%、30%、40%、50%、60%、70%、80%時，Z的值分別是多少？如圖6所示，公式輸入在C4單元，公式在左側B4單元表示自變數X，並在B4單元正下方的單元中輸入了自變數X的取值集合，將會在C4正下方產生相應的方程的值。至於為什麼這麼安排，是按照Excel有關模擬運算表以及公式和求解結果在內的連續二維表。屏幕中的其他文字是為了表格的可讀性而輸入。方程轉化為C4=B1*B4+B2*（1-B4）。通過這樣問題的轉化就非常清晰，否則在使用模擬運算時，許多人會無從下手。模擬運算操作窗口的兩個選項不好理解。在模擬運算構造完畢後，選中包括自變數X和其取值集合以及方程和求解結果所在的矩形單元格區間，也就是選擇圖6中B4：C11這一矩形區域，然後再調用模擬運算功能。因為模擬運算表是列方向的，而橫方向是空值，所以產生如圖7所示的模擬運算表窗口。同樣道理，雙變數模擬運算如果能把問題轉化為二元方程也就迎刃而解了。例2：上機都是80分的同學，筆試分數分別為：95、90、85、80、75、70。如果上機佔總分比率分別為：20%、30%、40%、50%、60%、70%、80%，求解各個同學選用哪個比率總分最高？設筆試分數為X，上機成績佔總分比率為Y，總分為Z，則方程為：Z=X*（1-Y）+80*Y。這是一個典型的二元方程問題。由圖8可以看出公式輸入在B4單元，B4單元的正下方輸入第一個變數的取值集合，B4單元的正右側輸入另一個變數的取值集合，至此雙變數模擬運算表構造完畢。A5單元表示自變數X，C3單元格表示自變數Y，在雙變數模擬運算中X、Y變數的代表單元格除模擬運算表數據單元格外可以任意選定，所以選擇哪個單元格表示X、Y沒有特殊規定，在此選擇這兩個單元格的原因是它們在輸入的可讀性文字旁邊便於記憶。由此方程轉換為B4=A5*（1-C3）+B1*C3，其中A5的值在運算中分別被C4～H4的值所取代，C3的值在運算中將分別被B5～B11的值所取代，因為C4～H4在模擬運算表中的同一行，B5～B11在模擬運算表中的同一列，所以在模擬運算表操作窗口中的選擇如圖9表示。1）選擇B4：B11所在的矩形區域；2）單擊[數據]—[模擬運算表]，彈出如圖9所示；3）因為A5的值在運算中分別被C4～H4的值所取代，所以在輸入引用行的單元格中輸入$A$5；因為C3的值在運算中分別被B5～B11的值所取代，所以在輸入引用列的單元格中輸入$C$3。

11、用Excel打造單詞默寫本：在Excel中有個Exact函數，可以比較字元串是否完全相同（能夠區分大小寫），如果完全相同就顯示為True,否則顯示為False，我們完全可以用它來做一個單詞默寫本來幫我們背單詞。1）運行Excel，分別在A1、B1、C1、D1單元格輸入「單詞」、「詞義」、「拼寫」、「結果」；2）在「單詞」和「詞義」兩列中，輸入你要默寫的單詞和相應單詞的詞義；3）選中D2單元格，點擊「插入」菜單下的「函數」命令，在彈出的對話框中選擇「文本」序列中的Exact函數，點擊「確定」按鈕。然後在打開的Exact函數對話框中，分別在Text1和Text2中輸入A2、C2，表明此函數用於比較A2單元格與C2單元格中的字元串是否完全相同，C2單元格中沒有輸入單詞或兩個單元格中的字元串不相同時，D2單元格的值是False；如果C2單元格與A2單元格中的單詞完全相同（包括字母大小寫），D2單元格的值為True；4）選中D2單元格，拖動填充柄，填充D列以下的單元格；5）選中A列，右鍵單擊，選擇「隱藏」命令將A列隱藏。至此，單詞默寫本就算做好了，如果你想為默寫本添加新的單詞，只要點擊A、B兩列中間位置，選擇「取消隱藏」，重複第二步和第四步驟操作即可。還等什麼，還不動手做一個單詞默寫本在暑假裡抓緊時間背單詞。12、顯示星號原來如此簡單：在電視抽獎時，屏幕上顯示的通常是隱藏了中間四位的手機號碼。其實，只要請出CONCATENATE函數即可以方便地在Excel工作表中實現這樣的效果。比如，如果在B2單元格中存儲了手機號碼13807480818，那麼公式「=CONCATENATE(LEFT(A1,3),"****",RIGHT(A1,4)」可將138****0818顯示在所需要的單元格中。以上公式用到了三個函數，其中CONCATENATE函數是將幾個文本字元串合併為一個文本字元串，其語法格式是CONCATENATE（text1,text2…）,這些文本項可以為文本字元串、數字或對單個單元格的引用。LEFT函數是根據所指定的字元數返迴文本字元串中最左邊的一個或多個字元。RIGHT函數是根據所指定的字元數返迴文本字元串中最右（後）邊的一個或多個字元。雖然，這三個函數聲明是處理文本字元串，但實際上無論單元格數字的格式是文本、數字還是特殊的社會保險號碼格式，你都可以使用這三個函數。除手機號碼外，作為常見的安全措施，假設你希望只能顯示身份證、社會保險號碼、信用卡號或其他號碼的部分數字，而用星號代替其餘位，CONCATENATE函數同樣能夠幫你大忙。比如，對5555-5555-5555-5555這個信用卡號碼來說，公式「=CONCATENATE(REPT("****-",3),RIGHT(D1,4)」，將得到****-****-****-5555的結果。其中REPT("****-",3)是重複"****-"這一文本字元串三次，即得到****-****-****-。原來要Excel顯示星號竟然如此簡單，你不試試？當然，在使用公式顯示星號之後，別忘記把公式引用到的源數據隱藏起來，否則可就起不到保密的作用了。

13、Excel中快速插入系統時間與日期：在用Excel進行報表處理時，經常需要在表格的前端或者未尾插入當天的時間與日期。若用數字輸入的話顯得比較繁瑣。其實可以這樣來快速輸入：首先選中需要時間的單元格，同時按下「Ctrl+Shift+；」組合鍵即可；若要輸入系統日期則按下「Ctrl+；」組合鍵即。14、徹底隱藏Excel工作表：在Excel中可以通過執行[格式]—[工作表]—[隱藏]將當前活動的工作表隱藏起來，在未執行進一步的工作薄設置的情況下，可以通過執行[格式]—[工作表]—[取消隱藏]來打開它。其實還可以通過通過設置工作表的隱藏性來徹底隱藏。按下「Alt+F11」組合鍵進入VBA編輯窗口，在左側選中需要隱藏的工作表，按下F4鍵打開「屬性」對話框，切換到「按分類序」標籤分頁，將「雜項」下的「Visable」的值選擇改為「2-xlSheetVeryHidden」或「0-xlSheetVeryHidden」退出後返回Excel即可。這樣就將選定的工作表隱藏起來，且「取消隱藏」也不起作用，這樣就能徹底隱藏工作表了。將Visable值改還原即可取消隱藏。15、快速切換Excel工作表：如果一個Excel工作薄中有大量的工作表，要是一個一個去切換查找很麻煩，其實可以在工作表標籤左側的任意一個按鈕上右擊，在彈出的工作表下拉列表中選中需要切換的工作表即可快速切換到該工作表。另外也可以按下「ctrl+pagedown」組合鍵從前往後快速按順序在各個工作表之間切換，按下「ctrl+pageup」組合鍵可以後往前依次快速地在各個工作表之間切換，這樣也能快捷地切換到需要的工作表。16、不讓Excel單元格中的零值顯示：如果你在Excel中使用某此函數統計出該單元格的值為零什，它會顯示出一個數字「0」，這看上去很不爽，列印出來也會包含這個「0」。怎樣才能不讓它顯示呢？下面以求和函數sum為例為看看如何不顯示零值。例如，在某工作表中對A2到E2單元格進行求和，其結果填寫在F2中，由於結果可能包含0，因此，為讓0不顯示則在F2單元格中輸入計算公式：「=IF(SUM(A2:E2)=0,"",SUM(A2:E2))」，即如果對A2到E2求和結果為0就不顯示，否則顯示其結果。17、Excel中巧選擇多個單元格區域：在編輯工作表時，如果要選擇不相鄰的單元格或單元格區域，大家通常採用的方法是：選擇一個單元格或單元格區域，然後按住Ctrl鍵的同時選擇其他單元格或區域。其實，除此之外，Excel還提供了另外一種選擇多個單元格區域的方法，筆者感覺更為順手，該方法是：選擇第一個單元格或單元格區域，然後按「Shift+F8」鍵，並拖動滑鼠選中其他不相鄰的單元格或區域將它添加到選定區域中，要停止向選定區域中添加單元格或區域，請再將按「Shift+F8」鍵。18、SUM函數也做減法：財務統計中需要進行加減混合運算，如果有一個連續的單元格區域B2：B20，在統計總和時需要減去B5和B10的值，用SUM函數計算時可用公式「=SUM(b2:B20,－B5, －B10)」來表示，這樣顯然比用公式「=SUM(b2:B4,b6:B9,b11:B20)」要來得方便些了。19、Excel公式與結果切換：Excel公式執行後顯示計算結果，按「Excel+`」鍵（位於鍵盤左上角），可使公式在顯示公式內容與顯示公式結果之間切換，方便了公式編輯和計算結果查看。20、Excel粘貼時跳過空白單元格：如果你只對大塊區域中含有數據的單元格進行粘貼，可以選中「選擇性粘貼」對話框下面的「跳過單元格」複選框。粘貼時只會將含有數據的單元格粘貼出來，而複製時的含有的空白單元格將不會覆蓋表格中的原有數據，這在需要改寫數據的場合非常有用。21、重複記錄巧刪除：當Excel工作表中有重複記錄時，可以利用「高級篩選」功能刪除重複記錄，具體操作是：選中Excel工作表中的所有記錄。在「數據」菜單中，指向「篩選」，單擊「高級篩選」命令；單擊「將篩選結果複製到其他位置」，然後在「複製到」框中，輸入單元格引用；選中「選擇不重複的記錄」複選框，單擊「確定」按鈕。22、Excel文本和函數也能一起「計算」：在要統計的單元格中輸入公式「=」當月累計」&SUM(A1:A30)」按回車，最終運算的單元格中會顯示結果「當月累計XXX」（XXX為求和的結果），此時文本和公式就一起被「計算」出來了。23、Excel複製、插入一位到位：在編輯Excel工作表的過程中，當我們將某一單元格中的內容複製到另一個單元格中時，目標單元格的內容便會被覆蓋掉。但有很多時候，我們所需要的操作是插入而不是替換。選選擇單元格，按下「Ctrl+c」，將源單元格中的內容送入剪切板備用。然後移動滑鼠至目標單元格，按下「Ctrl+Shift+ +(加號)」組合鍵，這時會彈出「插入」對話框，根據實際需要選擇「活動單元格右移」或「活動單元格下移」，點「確定」按鈕即可將源單元數據插入到目標單元格之前或之上了。

24、巧用Excel批量對比數據：每個月公司的會計都會從財務管理軟體中把資料庫中一個月的數據導出生成Excel，然後用每天的單據與導出的表中的數據進行比較，核對數據是否正確。上述方法比較繁瑣，筆者有更簡單的方法，通過兩個Excel表自動對比，智能得到結果以便核對。下面就來看看怎麼操作。1）首先導出數據，這個導出的Excel表由4列組成。這4列分別是「單號」、「部門」、「員工編號」和「金額」（如圖1）；2）會計按照同樣的格式做一張單據表並手工輸入單據信息，用來和導出的表比較差異。對比的要求是在「單號」、「部門」、「員工編號」都相等的情況下比較「金額」是否相同，如果相同的話提示「金額相同」，否則顯示「金額不同」；如果「單號」、「部門」、「員工編號」有一個不相等則應該提示「查無此人」；3）在導出的表中第A列前新插入一列，在新插入的列的A2中輸入公式：=B2&"_" &C2&"_" &D2（第一行為標題行所以沒有數據）；4）然後在數據區拖拉填充公式，為每一「單號」都建立一個這樣的字元串。用同樣的方法在單據表中生成一個這樣的列。在導出的表中，新建一個Sheet2的工作表，把單據表中的數據複製到導出表的Sheet2中；5）接著在Sheet1中按「F2」鍵輸入公式：=IF(ISERROR(VLOOKUP(A2,Sheet2!$A$2:$A$7,1,FALSE)),"查無此單",IF(EXACT(E2,VLOOKUP(A2,Sheet2!$A$2:$E$7,5,FALSE)),"金額相同","金額不同"))25、Excel2003快速輸入有相同特徵的數據：我們經常會輸入一些有相同特徵的數據，比如員工的廠證編號、單位的職稱證書號，都是前面幾位相同，後面的數字不一樣。我們可以快速輸入有相同特徵的數據，選定要輸入共同特徵數據的單元格區域，單擊滑鼠右鍵，在彈出的快捷菜單中選擇「設置單元格格式」命令，打開「單元格格式」對話框，選中「數字」選項卡，選中「分類」下面的「自定義」選項，然後在「類型」下面的文本框中輸入2006080000（注意：後面有幾位不同的數據就補幾個0），單擊「確定」按鈕即可。最後在單元格中只須輸入後幾位數字，如「2006083451」只要輸入「3451」，系統就會自動在數據前面添加「200608」。

26、Excel2003「照相機」的妙用：如果要讓Sheet2中的部分內容自動出現在Sheet1中，用Excel的照相機功能也是一種方法。操作方法如下：首先點擊「工具」菜單，選擇「自定義」命令，在彈出的對話框的「命令」選項卡下的「類別」中選擇「工具」，在右邊「命令」列表中找到「照相機」，並且將它拖到工具欄的任意位置。接著拖動滑鼠選擇Sheet2中需要在Sheet1顯示的內容，再單擊工具欄上新增加的「照相機」按鈕，於是這個選定的區域就被「拍」了下來。最後打開Sheet1工作表，在要顯示「照片」的位置上單擊滑鼠左鍵，被「拍攝」的「照片」就立即粘貼過來了。在Sheet2中調整「照片」的各種格式時，粘貼到Sheet1中的內容會同步發生變化，而且因為插入的是一幅自動更新的圖像文件，所以「圖片」工具欄對這個照片也是有效的，可以進行各種各樣的調整。27、在Excel中如何計算兩個日期之間的天數：你可以使用DATEDIF函數來計算，具體方法是：在A1單元格輸入前面的日期「2005-1-1」，在B1單元格輸入後面的日期「2007-04-02」。接著單擊C1單元格，輸入公式「=DATEDIF(A1,B1,」d」)」並回車，即可得到計算結果。有一點要注意，公式中的A1和B1分別代表前後兩個日期，順序是不能顛倒的。28、Excel2007快速隱藏Excel中的部分數據：在Excel中經常遇到某行或某列中部分數據不能夠被刪除而需要隱藏的情況，我們不能選中整行或整列用「隱藏」命令將它隱藏，因為會隱藏數據的單元格，右擊執行「設置單元格格式」命令，然後切換到「自定義」選項卡，在「類型」輸入框中輸入三個分號「；；；」，確定後即可隱藏數據。29、Excel2003製作能夠自動更新標題日期的工作表：在日常處理數據工作中，一般習慣於通過在一些已有的模板或者工作表基礎上，創建新的文件來提高工作效率。但是使用這些源文件時，往往會修改其中的一些不適合內容，特別是這些文件的標題中包含的日期，如2007年4月份各部門交接的商品明細表，而到5月份時則必須進行修改。針對這個問題，可以在存儲標題的單元格輸入以下公式：=YEAR(TODAY()&」年」 &MONTH(TODAY()&」月份各部門交接商品明細表」，這樣在5月份打開工作表時，標題中的日期會自動更新為新年份、月份，而不會出現錯誤了。30、Excel2003讓數值的單位自動輸入：在記賬時，經常要往固定的表格中輸入「X張」、「Y把」和「Z套」等物品單位，每次輸入數值後還要再輸入單位，實在累人。下面介紹一個簡單方法，操作如下：首先選中需要輸入數據的單元格區域，接著單擊「格式→單元格」命令，在彈出的對話框中切換到「數字」選項卡，選中「分類」列表中的「自定義」項，然後在「類型」中輸入：「[=1]G/通用格式」張」； [=6]G/通用格式」把」；G/通用格式」套」」，最後單擊「確定」即可。

31、Excel2003表格快速變圖片：Excel中的一些表格或圖表經常需要轉換成圖片的形式，下面為大家介紹簡單的轉化方法，選中設計好的表格或圖表，按住「Shift」鍵的同時點擊「編輯」菜單，原來的「複製」命令就會變成「複製圖片」，然後再到目標位置進行「粘貼圖片」即可快速完成轉換。

32、Excel2003/2007快速舍掉多餘的小數位：當我們在Excel中進行數據處理時，有時候需要對某列(行)數據作小數位數的取捨，比如將某列所有數據保留兩位小數，而舍掉多餘的小數位而不是四捨五入，則可以利用ROUNDDOWN函數來輕鬆實現。例如在A1單元格中輸入77.8775，然後在B1單元格中輸入公式「=ROUNDDOWN(A1,2)」後回車，則B1中顯示為77.87，然後利用填充柄對餘下的單元格進行公式填充即可。33、快速刪除Excel分表：在Excel2007中打開「人員清單總表」，在表格的最後增加一列（G），在G2中輸入公式「COUNTIF（參加勞保者名單!A：A，A2）」，此公式用於統計在參保名單A列與A2單元格相同的單元格個數。選中G2單元格雙擊其填充柄，把公式向下填充至最後一個記錄，此時所有參加勞保的人G列的值顯示1，其他沒參加勞保的人員G列的值則顯示0。右擊G列中值為0的單元格，從彈出菜單中依次選擇「篩選→按所選單元格的值篩選」，就可以看到表格中所有已參加勞保的人員記錄消失了。然後只要把剩下的數據記錄複製粘貼到另一個工作表中保存，就得到所有未參加勞保的人員清單了，最後，只要右擊G列選擇「刪除」即可令總表恢復原狀。

32、Excel2003/2007選中同一類數據單元格：在一個Excel工作表中，通常會包含多種類型的數據，諸如常見的文本、數值、公式等，有時我們需要從這些不同類型的數據中選中某種類型的數據，如何快速準確的選中呢？可以使用「定位」命令實現，具體操作步驟如下：選擇菜單命令「編輯」菜單→「定位」命令，打開「定位」對話框，單擊「定位條件」按鈕，在「定位條件」對話框中，選擇「常量」單選項，然後選中需要的類型數據複選框，如「文本」，選中後單擊「確定」按鈕即可。

33、Excel2003在Excel 中隱藏部分工作表：在有些時候，由於保密的目的，我們可能希望將Excel工作簿中的一些工作表保護起來，不讓部分來授權的用戶看到。雖然在Excel中提供了工作表保護的命令，雖然在Excel中提供了工作表保護的命令，但是只能限制對內容的改動，並不能防止未授權用戶看到此工作表。而隱藏工作表命令雖然可以使用戶暫時看不到此工作表，卻不能提供密碼保護。為此我們需要將工作薄保護和工作表隱藏兩個命令結合起來實現我們的目的。具體操作如下：首先定位於要保護的工作表，選擇「格式→工作表→隱藏」，將此工作表隱藏起來。然後再選擇「工具→保護→保護工作薄」，並按照提示輸入密碼，這樣就可以將此工作薄用密碼保護起來，使未授權的用戶不能取消對相應工作表的隱藏，也就看不到此工作表了。34、用妙法打標準工資條：職工工資構成非常複雜，往往超過10項，因此每月發工資時要向職工提供一包含工資各構成部分的項目名稱和具體數值的工資條。列印工資條時要求在每個職工的工資條間有一空行便於彼此裁開。本模板就是用EXCEL函數根據工資清單生成一便於分割含有工資細目的工資條表格。本工資簿包含兩張工資表。第1張工資表就是工資清單，稱為"清單"。它第一行為標題行包括職工姓名、各工資細目。第2張工作表就是供列印的表，稱為"工資條"。它應設置為每三行一組，每組第一行為標題，第二為姓名和各項工資數據，第三行為空白行。就是說整張表被3除餘1的行為標題行，被3除餘2的行為包括職工姓名、各項工資數據的行，能被3整除的行為為空行。在某一單元格輸入套用函數"=MOD(ROW(),3)"，它的值就是該單元格所在行被3除的餘數。因此用此函數能判別該行是標題行、數據行還是空行。在A1單元格輸入公式"=IF(MOD(ROW(),3)=0,"",IF(MOD(ROW(),3)=1,清單!A$1,"-if-false"))"並往下填充，從A1單元格開始在A列各單元格的值分別為清單A1單元格的值即姓名、-if-false、空白，姓名、-if-false、空白，……。其中-if-false表示MOD(ROW(),3)既不等於0又不等於1時，即它等於2時應取的值。它可用如下函數來賦值："INDEX(清單!$A:$G,INT((ROW()+4)/3),COLUMN())"。INDEX()為一查找函數它的格式為：INDEX(reference,row-num,col-num),其中reference為查找的區域，本例中為清單表中的A到G列，即函數中的"清單!$A:$G"，row-num為被查找區域中的行序數即函數中的INT((ROW()+4)/3)，col-num為被查找區域中的列序數即函數中的COLUMN()。第2、5、8…….行的行號代入INT((ROW()+4)/3)正好是2、3、4……，COLUMN()在A列為1。因此公式"=INDEX(清單!$A:$G,INT((ROW()+4)/3),COLUMN())"輸入A列後，A2、A5、A8……單元格的值正好是清單A2、A3、A4……,單元格的值。這樣，表的完整的公式應為"=IF(MOD(ROW(),3)=0,"",IF(MOD(ROW(),3)=1,清單!A$1,INDEX(清單!$A:$G,INT((ROW()+4)/3),COLUMN())))"。把此公式輸入A1單元格，然後向下向右填充得到了完整的工資條表。為了表格的美觀還應對格式進行設置，一般習慣包括標題、姓名等文字在單元格中要取中，數字要右置，數字小數點位數也應一致，還有根據個人的愛好設置邊框。本表格只需對一至三行的單元格進行設置，然後通過選擇性格式設置完成全表的設置。本工作簿的特點是1、不對清單表進行操作保持清單工作表的完整，2、全工作表只有一個公式通過填充得到全表十分方便。————————————————補充：根據使用經驗，只要在第一個單元格寫入如下代碼：=IF(MOD(ROW(),3)=0," ",IF(MOD(ROW(),3)=1,Sheet1!A$1,INDEX(Sheet1!$A:$G,INT((ROW()+4)/3),COLUMN())))裡面的主要參數說明：「Sheet1」為所要列印的數據表名，「$A:$K」中的K值為最後列印的列，如果不止於K列，請自行改變。先縱向複製第一個單元格的函數到第三行單元格。在第一列1、2行兩個單元格全部劃表格線第3行不劃表格線，然後選擇三個單元格，橫向複製、縱向複製到全部數據出現。

35、Excel2003將數據一分為二：在Excel表格中，有時要對一列數據進行分列，如取後幾位排序、文字提取等，這在Excel中操作非常簡單。方法如下：選中要分列的那一列，在菜單中點擊「數據」然後單擊「分列」，選擇「固定寬度」再在合適位置建立「分列線」點「下一步」、「完成」即可。另外，若選擇分隔符號，則是指對含有逗號、空格等分隔符號的列進行的分列操作。它的界面與導入文本文件相同。36、Excel2003顯示被隱藏的下劃線：在Excel中「填空型」下劃線是我們經常用到的。那麼你是否發現當下劃線後無字元時，你所輸入的下劃線只有在編輯其單元格時才能顯示，正常情況下根本無法顯示並被列印。下面就讓我們來解決這個問題，將游標定位在要輸入下劃線的地方，根據所需長度按鍵盤上的Shift+—（注意：在英文輸入法狀態下操作）。好了，看下劃線乖乖的出現在眼前了。37、Excel2003/2007巧輸含小數點的數字：在Excel中，我們經常會遇到需要輸入大量帶有小數點的數字，例如0.0123，如按常規方法輸入，不但容易出錯，而且效率也很低。這裡介紹一種方法，大家不妨一試：就是利用Excel中的自動設置小數點的功能。打開[工具]—[選項]—[編輯]標籤頁，首先勾選「自動設置小數點」複選框，再在「位數」編輯框中設置小數點右邊您所需的位數。這樣，在輸入帶小數點的數字時，就只需要直接輸入數字，從而可以省略小數點的輸入，它會在按回車鍵後自動定位。38、批處理多個Excel工作表：這個很簡單，按住Ctrl鍵或Shift鍵不放，單擊選中多個工作表。然後你就可以像平常一樣進行設置，比如單擊[文件]—[頁面設置]，將選中的多個工作表設置成相同的頁面。39、Excel2003/2007讓複製Excel表格列寬不變化：從別的Excel工作薄或工作表複製數據到當前工作表後，你會發現原來正常列寬的表格，卻需要重新調整列寬了。其實，這個手動調整列寬的工作可以由Excel自動完成，即在粘貼操作後，立即指向屏幕上出現的「粘貼選項」按鈕，並單擊按鈕旁的箭頭，然後單擊「保留源列寬」。另外還有一個辦法：那就是選擇需要粘貼的單元格複製，然後在[選擇性粘貼]對話框中點選[列寬]後[確定]，最後再將數據複製進去就可以了。40、Excel表格內容之對齊法寶：1）選擇需要對齊文字的列、行或單元格；2）點擊[格式]—[單元格]—[水平對齊]—[分散對齊]；3）按住「Ctrl」鍵分別選擇插入的前後兩列，在彈出的「列寬」對話框中輸入適當的數值；4）如果只是要讓該列或行中的文字稍微拉開點距離，可以點擊[格式]—[單元格]—[水平對齊]—[分散對齊]，並勾選[增加縮進]即可。41、Excel2007快速批量去除超鏈接：從網上複製資料到Excel2007表格中，如果某列單元格均含有超鏈接，用手工一一去除超鏈接比較麻煩，我們可以點擊「選擇性粘貼」，勾選「數值」選項即可。42、Excel快速移動表格列或行：在編輯表格的過程中，有時需要調整列和行的位置，只需將滑鼠移動到此列和行的表格線上並按住左鍵向目標位置拖動即可，如果拖動的目標列和行中有數據，則會覆蓋該列和行的數據。43、在Excel中巧做複雜表頭：在Excel2003中，製作表頭是常有的事，但鑒於中美文化的差異，中文表格中的表頭部分有時很複雜，製作起來比較困難，如果遇到要製作像下圖中這樣的表頭，那就更麻煩了，下面介紹一下筆者從工作中總結出來的方法：1）首先做斜線表頭部分，確定一個4×4區域，畫上斜線，填入文字；2）調整文字的對齊方式及表格各列的寬度，得到理想的表格斜線頭部部分，選定該4×4區域，設置框線，完成該部分製作；3）製作橫向表格頭部，合理合併單元格，填入文字，確定對齊方式，完成製作；4）製作縱向表格頭部，合理確定表格線，完成表格製作。44、Excel2003防止公式被誤刪和修改：在Excel2003中的各種表格中，特別是統計表格中會設置許多公式進行計算，有時會因為誤操作或文件共享被他人修改公式造成不必要的損失，而如果對整張工作表進行保護，需要修改的單元格又因密碼問題不能進行。是否有既可以修改單元格，又不會誤刪操作帶有公式的單元格？筆者是這樣設置的：按住「Ctrl」鍵用滑鼠左鍵依次選定允許修改的單元格區域，然後依次點擊[工具]—[保護]—[允許用戶編輯區域]，在跳出的對話框中選擇[新建]，會在[修改區域]對話框中顯示你剛才選中的或輸入你要修改的單元格區域，單擊[確定]關閉，回到[允許用戶編輯區域]對話框，點擊左下方按鈕[保護工作表]，選擇[保護工作表及鎖定的單元格內容]選框及下方的允許修改的操作內容複選框，並設定保護密碼，確定後即可。45、快速計算某日期處於一年中的第幾個星期：如果需要在Excel中，計算某個或者一系列日期，如2007-11-26，處於一年中的第幾個星期，可以在單元格中輸入日期，如F40，然後在計算結果的單元格中輸入公式：=INT((F40-DATE(YEAR(F40),1,0)+WEEKDAY(DATE(YEAR(F40),1,0),1)+7-WEEKDAY(F40,1))/7)，即可得到此日處於2007年中的第48個星期，此方法對於一些安排工作計劃日程的表格非常有用。46、讓Excel不顯示錯誤值「#DIV/0！」：在Excel2003表格中，如果單元格A3中輸入「=A1/A2」後，在單元格A2未輸入數值時，單元格A3就會顯示錯誤值「#DIV/0！」，有什麼方法可以讓單元格A3在單元格A2未輸入數值是變成空白的，不顯示錯誤值，而在單元格A2中輸入數值時又能正常顯示呢？利用「ISERROR」函數可以解決這個問題。在單元格A3里使用「=IF（ISERROR（A1/A2）,」」,A1/A2）」取代「A1/A2」。這樣，當單元格A2的值是空的，則返回空值，否則，返回「A1/A2」的值。

47、調整利率後房貸怎麼還：今年銀行連續幾次加息，讓「房奴」肩上的擔子更加地沉重，利率發生變化，水漲船高，每個月固定的還款額也要隨之發生變化，增加或減少多少？房貸人如果想提前還貸，所需款額如何計算？例如某企業貸款30萬元，年利率為5.6%，還款期限為10年，按年或者月付款方式進行等額付款，現計算各個時期的本金和利息。先讓大家了解一下PPMT函數，PPMT函數的功能是基於固定利率及等額分期付款方式，返回投資在某一給定期間內的本金償還額。其語法為：PPMT（rate,per,nper,pv,fv,type）。其中rateo為各期利率：per為用於計算其本金數額的期數，必須介於1到nper之間；nper為總投資期，即該項投資的付款期總數；pv為現值，即從該項投資開始計算時已經入賬的款項或一系列未來付款當前值的累積和，也稱為本金；fv為未來值或在最後一次付款後希望得到的現金餘額；type為數字1或者0，用以指定各期的付款時間是在期初還是期末。1）計算第一年年初應付的本金(如圖)：在單元格C5中輸入以下公式：=PPMT(D2,1,C2,B2,0,1)；2）計算第一年年末應付的本金：在單元格C6中輸入以下公式：=PPMT(D2,1,C2,B2,0,0)；3）計算最後一年年初應付的本金：在單元格C7中輸入以下公式：=PPMT(D2,10,C2,B2,0,1)；4）計算最後一年年末應付的本金：在單元格C8中輸入以下公式：=PPMT(D2,10,C2,B2,0,0)；5）計算第一個月月初應付的本金：在單元格C8中輸入以下公式：=PPMT(D2/12,1,C2,B2,0,1)；6）計算第一年年初和最後一年年初應付的本利和，在單元格E5和E7中輸入以下公式：=PMT(D2,C2,B2,0,1)。如果D2單元格中的利率發生變化，那麼相應的單元格也會發生變化。48、用Excel2007計算應收黨費：有的地方會計製作黨費交納表，利用Excel2007的IF函數就可以輕鬆完成這項工作，除此之外，還可以用這種方法計算年終獎和評先進等需要用到超率累進計算的地方，具體操作如下：首先，在Excel2007中製作好基礎表格，「稅後工資收入」採用引用的方式輸入數據，這樣當工資收入發生變更時數據就可以自動更新。選定D3單元格，輸入公式「=IF(C3<=400,"0.5",IF(AND(C3>400,C3<=600),"1",IF(AND(C3>600,C3<=800),"1.5",IF(AND(C3>800,C3<=1500),"2",IF(1500<C3,"3",)))))」。[小提示：每月工資收入在400元（含400元）以下者，交納月工資收入的0.5%；400元以上至600元（含600元）者，交納1%；600元以上至800元（含800元）者，交納1.5%；800元以上（稅後）至1500元（含1500元）者，交納2%；1500元以上（稅後）者，交納3%。上述公式執行後會立即計算出第1位黨員應交納黨費的比率，然後向下拖曳右下角的填充柄，很快就可以獲得所有數據。最後選中E3單元格，輸入「C3*D3/100」，這裡除以100的原因是因為D列所顯示的都是百分比值。回車後即可得到相應結果，然後同樣採取拖曳填充柄的方法獲得其他黨員應交納的黨費數據。

Excel輕鬆實現自動換行

方法一：如果有大量的單元格需要這樣做，採取此種設置格式的方法：選中需要這種格式的單元格，執行「格式→單元格」命令，打開「單元格格式」對話框，切換到「對齊」標籤下，選中「自動換行」選項，確定返回即可。

以後，在此類單元格中輸入超過列寬的字元時，系統自動按列寬分多行進行排列。

方法二：如果只有少量單元格需要這樣做，採取此種手動的方法來實現：在輸入過程中，需要換行時，直接按下「Alt+Enter」組合鍵，繼續輸入就可以了。

注意：如果已經將數據全部輸入完成了想換行，只要將游標定在換行處，按下上述組合鍵即可。

Excel處理數據之便捷眾人皆知，可在其單元格內換行就略顯不便，不知你是否也遇到過此類問題？通過摸索，以下四法便能輕鬆實現單元格內的自動換行。

1. 輸入數據隨時換行

用戶若要在輸入數據時換行，只要通過Alt+Enter組合鍵即可輕鬆實現。此方法同樣可使已輸入內容的單元格在游標所在處換行。

2. 單元格區域內換行

將某個長行轉成段落並在指定區域內換行。例如：A10內容很長，欲將其顯示在A列至C列之內，步驟是：選定區域A10：C12（先選A10），選擇「編輯→填充→內容重排」，A10內容就會分布在A10：C12區域中。此法特別適合用於表格內的注釋。

3. 調整單元格格式換行

選定單元格，選擇「格式→單元格」，在彈出的對話框中單擊「對齊」，選中「自動換行」複選框，單擊[確定]按鈕即可。

一個單元格里一行打不下文字時自動另起一行,還在本格；.右鍵--設置單元格格式--對齊--自動換行打勾.

4. 文本框的巧用

單擊「視圖」菜單，在「工具欄」命令中，選中「繪圖」工具欄，單擊該工具欄的「文本框」，為了保證文本框的邊界與工作表網格線重合，需按住Alt鍵的同時插入文本框，然後，就可以在文本框中任意輸入內容了。