Excel 技巧篇

Excel 技巧篇

讓不同類型數據用不同顏色顯示

大於等於2000元的工資總額以「紅色」顯示，大於等於1500元的工資總額以「藍色」顯示，低於1000元的工資總額以「棕色」顯示，其它以「黑色」顯示：格式――條件格式，按要求添加所需格式

建立常用文檔菜單欄：

1、  工具欄――自定義――命令――新菜單，拖動子菜單項到當前菜單欄

2、  將插入菜單中的「超鏈接」拖到新建的子菜單中，根據需要進行命名，如「工資表」

3、  編輯超鏈接，使其鏈接到所需的工資表，以後單擊其則可打開，可以多添加一些

用「視圖管理器」保存多個列印頁面：

1、  將不需要的行列隱藏，設置好面頁面

2、  視圖――視圖管理器――添加

3、  使用時打開視圖管理器――選擇――顯示即可

自定義排序，讓數據按所需要求排序：

1、  工具――選項――自定義序列――輸入所需序列

2、  使用：排序――選項――在下拉框中選擇輸入的序列即可

隱藏數據：

1、  選定區域――格式――數字――自定義：「；；；」

2、  切換到「保護」標籤下，勾選「隱藏」

3、  保護工作表，設置密碼

讓中英文輸入法智能出現：

選定區域――數據有效性――輸入法――模式，選擇所需的輸入法即可

用自動更正輸入統一文本

1、  自動更正――替換，輸入「pcw」，在替換為框中輸入「電腦報」，添加――確定

2、  以後輸入pcw，則可以顯示「電腦報」

使用監視窗口，同時查看多個多個單元格中的內容

1、  視圖――工具欄――監視窗口

2、  添加需監視的單元格，可以是其它表中的

小數點自動定位：

選項—編輯—自動設置小數點（兩位），輸入5則為0.05

將基數字轉換為序數詞

=A2&IF(VALUE(RIGHT(A2,2))={11,12,13},"th",IF(VALUE(RIGHT(A2))={1,2,3},CHOOSE(VALUE(RIGHT(A2)),"st","nd","rd"),"th"))

快速錄入文本文件中的內容 ： 數據—獲取外部數據—導入文本文件

特殊的雙擊功能

    1、在工具欄右側的空白處雙擊，可打開「自定義」對話框。

    2、在單元格中雙擊，單元格進入編輯狀態。

    3、在「格式刷」按鈕上雙擊，格式刷可以反覆多次使用。單擊「格式刷」或者按Esc鍵可以取消

    4、在標題欄上雙擊，Excel窗口由最大化（原始狀態）還原到原始狀態（最大化）大小。

    5、在窗口左上角Excel標誌上雙擊，則退出Excel（如果當前文檔沒有保存，系統會提示保存）。

    6、在滾動條上端/左端與編輯區交界處雙擊，可拆分窗口。

    7、在菜單上雙擊，即可將菜單中所有的菜單項（包括不常的菜單項）全部展開。

    8、某行/列有多個連續的空白或數據單元格時，在某個單元格邊上雙擊（十字形）可快速定位（最下/右必須有數據）

9、雙擊數據透視表中的數據, 可在新的工作表中列出該數據的明細

快速處理多個工作表   

   按住「Ctrl"或「Shift" 鍵選定工作表，然後批量處理，如設置相同的列寬，字體等

F1---F12 功能鍵的功用

F2:進入編輯狀態

F4:改變單元格引用方式（相對，絕對）

F5:定位

F6:在同一表格的不同分拆欄里切換

F7:拼寫檢查

F8:區域擴展

F9:活動工作表重算

F10: 同Alt，菜單選擇

F11:自動生成圖表

F12:另存為  

Application.OnKey "{F11}", "ccc"      禁用F11

批量替換批註：

修訂－－批註—替換

把載入宏內置到Excel文件里：

Private Sub Workbook_Open()

    Application.RegisterXLL Filename:= Application.Path & "LibraryAnalysisANALYS32.XLL"

End Sub

提取單元格中的文字：

=LEFT(A1,(SEARCHB("?",A1)-1)/2)，查找第一個半形字元出現的位置

=RIGHT(A1,LENB(A1)-LEN(A1))

關於宏和程序

問：編了一個較完整的程序，能夠給源程序加密碼，實現"工程不可見"，但在vba里還能看到大部分宏，雖然不能編輯，但能運行，如何隱藏起

答：不用模塊函數,重寫成類或放到workbook中,或在程序中直接將菜單宏隱藏。

問：已經屏蔽alt+F11 鍵，雖然不能看到宏程序，但依然可以運行宏，如何隱藏宏。

答：在宏的聲明前加Private。

禁止輸入空格：                有效性公式。=COUNTIF(A1,"* *")=0

固定數據輸入時的焦點：       「Ctrl」鍵的同時單擊選擇該單元格。按「Enter」鍵游標不會移動

利用公式求值檢查錯誤：        在自定義中將「公式求值」拉到菜單欄上，單步執行公式

在工具按鈕之間設置分隔線：    按住Alt，單擊並稍稍往右拖動即可，取消時向左即可。

同時打開相關聯的所有工作簿：  將所有相關的表打開，然後選擇「菜單」—文件—保存工作區即可

避免數據顯示誤差：            [工具]→[菜單]→[選項]→[重新計算]--「以顯示值為準」

數組：用｛｝括起來，如果是多維，則多維之間用「;」分隔

    1、數組中不能逐個列出引用，但可以使用區域，｛A1,B1｝錯，｛A1:B1｝正確

    2、數組單元格不能單獨編輯，需要「定位」—「當前數組」才能編輯

3、刪除數組，選定要刪除的數組，按[Ctrl]+[Delete]或選擇編輯菜單中的「清除」命令

√ 輸入： 按住ALT鍵輸入41420後放開ALT鍵

HYPERLINK("#表1！L2","輕客支撐")，#加在表名前指當前工作簿，加在單元格前是指當前工作表

把Excel 表格轉換為圖片  

按住 Shift 鍵點擊「文件」菜單，原來的「關閉」菜單項就會變成「全部關閉」

按下 Shift 鍵的同時點擊「編輯」菜單，原來的複製和粘貼就會變成「複製圖片」和「粘貼圖片」

在B1中同步顯示A列中最後一行的內容  

最後一行為文本： =offset($b$1,MATCH(CHAR(65535),b:b)-1,) 

最後一行為數字： =offset($b$1,MATCH(9.9999E+307,b:b)-1,)

或者：=lookup(2,1/(b1:b1000<>""),b1:b1000)

怎樣很簡單的判斷最後一位是字母

 right(a1)*1 出錯的是字母

=IF(ISNUMBER(--RIGHT(A1,1)),"數字","字母")

=IF(ISERR(RIGHT(A1)*1),"字母","數字")

工具——選項——1-2-3幫助——轉換 lotus 123 公式

有無打勾？去掉

原函數=a1+a4+a7+a10+a13+a16+a19+a22... ，數組公式：｛=SUM(N(OFFSET(A1,(ROW(1:10)-1)*3,)))｝

奇數行求和 =SUMPRODUCT((A1:A1000)*MOD(ROW(A1:A1000),2))  

偶數行求和 =SUMPRODUCT((A1:A1000)*NOT(MOD(ROW(A1:A1000),2)))

對日期進行上中下旬區分：=LOOKUP(DAY(A1),{0,11,21,31},{"上旬","中旬","下旬","下旬"})

=--A1>50000中的--代表什麼意思，轉變為數值.與+0,*1,是一樣的效果。

列印行號列標：     文件菜單-----頁面設置---工作表----在列印選項中的行號列標前打勾。

列印不連續區域：   按CTRL鍵不松，選取區域，再點文件菜單中的列印區域--設置列印區域。

列印時自動隱去被0除的錯誤提示值：   頁面設置—工作表，錯誤值列印為空白

求A1:B10中A列等於1的對應B列中的最小值  ｛=min(if(a1:a10=1,b1:b10)) ｝

定義名稱的妙處：

引用位置：可以是工作表中的任意單元格，可以是公式，也可以是文本。

妙處1：減少輸入量  DATA = 「I LOVE YOU， EXCEL！」， 「=DATA」顯示「I LOVE YOU， EXCEL！」

妙處2：出現多次的欄位： 例如 =IF(ISERROR(IF(A1>B1,A1/B1,A1)),」」,  IF(A1>B1,A1/B1,A1))

將IF(A1>B1,A1/B1,A1)定義「A_B」，公式便簡化為=IF(ISERROR(A_B),」」,A_B)

妙處3：解決公式嵌套限制和單元格字元數限制

妙處4：某些函數只能在名稱中使用， A1=1+2+3，定義名稱 RESULT = EVALUATE(Sheet1!$A1)

在B1輸入=RESULT，B1就會顯示6了。

妙處5：圖片的自動更新，例如你想要在一周內每天有不同的圖片出現在你的文檔中，具體做法是：

找7張圖片分別放在A1至A7單元格中，調整單元格和圖片大小，使之恰好合適

定義名稱MYPIC = OFFSET(SHEET1!$A$1,WEEKDAY(TODAY(),1)-1,0,1,1)

控制項工具箱--文字框，在編輯欄中將EMBED("Forms.TextBox.1","")改成MYPIC 就大功告成了。

此外，名稱和其他，例如數據有效性的聯合使用，會有更多意想不到的結果。

假如A欄里有任一單元格有"$"字元串，則等於1，否則等於0

=IF(COUNTIF(A:A,"*$*")>0,1,0)  或者  ＝(countif((A:A,*$*)>0)+0

在查找中使用通配符： ？和 * ，各代表一個字元和一串字元，可用於任何查找，如Vlookup中

圖片批註 ：編輯批註--邊框上右擊--設置批註格式--顏色與線條--顏色--填充效果--圖片--選擇圖片.

if函數的另類用法  =IF(OR(VALUE(RIGHT(A1,2))={11,12,13}),"包含","不包含")

EXECL運算符及單元格引用

運算符的優先順序：

三維引用：在同一文件的多個工作表上的相同單元格或單元區域中的數據使用

示例：在工作表Sheet4單元格B2中統計Sheet1——Sheet3中單元格C3值之和，操作步驟如下：

1、打開工作表，選定Sheet4中的B2單元格，鍵入「＝sum()」

2、然後單擊Sheet1的標籤，這時系統提示出錯，單擊確定 ，再次單擊工作表Sheet1的標籤，然後按住 Shift，單擊工作表Sheet3的標籤，然後單擊C3單元格。完成上述操作後按 Eeter，則在 B2單元格中出現「＝Sheet1：Sheet3！C3」

同一文件中引用其它表的單元格，其格式為： 工作表名稱！（空格）單元格名。

引用其它文件中的單元格，格式為：路徑名【文件名】工作表名！單元格和單元格區域的地址

Excel 快捷鍵

常用快捷鍵：

Shift + F11               插入新工作表，或者使用Alt+Shift+F1

Ctrl + PageDown           移動到工作簿中的下一張工作表。

Ctrl + PageUp             移動到工作簿中的上一張工作表。

Shift + Ctrl + PageDown   選擇多張工作表。取消選擇多按 Ctrl PageDown，

Shift + Ctrl + PageUp     選擇當前工作表和上一張工作表。

Alt+OHR                   對當前工作表重命名（「格式」菜單的「工作表」子菜單上的「重命名」命令）。

Alt+EM                    移動或複製當前工作表（「編輯」菜單上的「移動或複製工作表」命令）。

Alt+EL                    刪除當前工作表（「編輯」菜單上的「刪除工作表」命令）。

Shift + Tab               向左移動一個單元格。

Shift + Enter             向上移動一個單元格。

Ctrl + →                 移動到當前數據區域的邊緣。

Alt + PageDown            向右移動一屏。

Alt + PageUp              向左移動一屏。

F6                        切換到已拆分（「窗口」菜單上的「拆分」命令）的工作表中的下一個窗格。

Shift + F6                切換到已拆分的工作表中的上一個窗格。

Ctrl + Backspace          滾動以顯示活動單元格。

F5                        顯示「定位」對話框。

Shift + F5                顯示「查找」對話框。

Shift + F4                重複上一次「查找」操作（等同於「查找下一個」）。

Ctrl + 句號               按順時針方向移動到選定區域的下一個角。

Ctrl + Alt + →           在不相鄰的選定區域中，向右切換到下一個選定區域。

Ctrl + Alt + ←           向左切換到下一個不相鄰的選定區域。

單元格及對象操作快捷鍵：

Ctrl + 減號               刪除選定的單元格。

Ctrl + 加號               插入空白單元格。

Ctrl + 空格鍵             選擇整列。

Shift + 空格鍵            選擇整行。

Ctrl + A                  選中當前數據區域（四周為空），再次按 Ctrl + A 可選中整個工作表。

Shift + Backspace         在選擇了多個單元格的情況下，只選擇活動單元格。

Ctrl + Shift + 空格鍵    作用同Ctrl+A ，但它同時會選中表中的對象

Ctrl + 6                  在隱藏對象、顯示對象和顯示對象佔位符之間切換。

Ctrl + Shift + *         選擇活動單元格周圍的數據區域（四周為空）

Ctrl + /                  選擇包含活動單元格的數組

Ctrl + Shift + O（字母）  選擇含有批註的所有單元格

Ctrl +                   在選定的行中，選擇與活動單元格中的值不匹配的單元格。

Ctrl + Shift + |         在選定的列中，選擇與活動單元格中的值不匹配的單元格。

Ctrl + [                  選擇由選定區域中的公式直接引用的所有單元格。

Ctrl + Shift + {          選擇由選定區域中的公式直接或間接引用的所有單元格。

Ctrl + ]                  選擇包含直接引用活動單元格的公式的單元格。

Ctrl + Shift + }          選擇包含直接或間接引用活動單元格的公式的單元格。

Alt + ;                   選擇當前選定區域中的可見單元格。

F8                        打開或關閉擴展模式。在擴展模式中，箭頭鍵可擴展選定區域。

Shift + F8                將其他區域的單元格添加到選定區域中，或使用箭頭鍵移動到所要添加的區域的起始處，然後按 F8 和箭頭鍵以選擇下一個區域。

Shift + 箭頭鍵            將選定區域擴展一個單元格。

Ctrl + Shift + 箭頭鍵    將選定區域擴展到與活動單元格在同一列或同一行的最後一個非空單元格。

Shift + Home              將選定區域擴展到行首。

Ctrl + Shift + Home          將選定區域擴展到工作表的開始處。

Ctrl + Shift + End       將選定區域擴展到工作表上最後一個使用的單元格（右下角）。

Shift + Page Down         將選定區域向下擴展一屏。

Shift + Page Up           將選定區域向上擴展一屏。

End + Shift + 箭頭鍵     將選定區域擴展到與活動單元格在同一列或同一行的最後一個非空單元格。

End + Shift + Home       將選定區域擴展到工作表的最後一個使用的單元格（右下角）

End + Shift + Enter          將選定區域擴展到當前行中的最後一個單元格。在 「Lotus 1-2-3 常用鍵」不起作用

ScrollLock + Shift + Home 將選定區域擴展到窗口左上角的單元格。

ScrollLock + Shift + End  將選定區域擴展到窗口右下角的單元格。

數據操作快捷鍵：

Alt + Enter               在單元格中換行

Ctrl + Enter              用當前輸入項填充選定的單元格區域

F4 或 Ctrl + Y            重複上一次操作

Ctrl + Shift + F3        由行列標誌創建名稱

Ctrl + D                  向下填充

Ctrl + R                  向右填充

Ctrl + F3                 定義名稱

Ctrl + K                  插入超鏈接

Ctrl + ;（分號）          輸入日期

Ctrl + Shift + :（冒號）  輸入時間

Alt + 向下鍵              顯示區域當前列中的數值下拉列表。

Ctrl + Z                  撤消上一次操作

Ctrl + Delete             刪除插入點到行末的文本

F2                        編輯當前單元格

Shift + F2                編輯單元格批註

輸入特殊字元：

Alt + 0162                輸入分幣字元 ￠。

Alt + 0163                輸入英鎊字元 ￡。

Alt + 0165                輸入日圓符號 ￥。

Alt + 0128                輸入歐元符號 €。

公式操作快捷鍵：

"=(等號)"                 鍵入公式。

Ctrl + Shift + Enter     將公式作為數組公式

Shift + F3                在公式中，顯示「插入函數」對話框。

Ctrl + A                  當插入點位於公式中公式名稱的右側時，顯示「函數參數」對話框。

Ctrl + Shift + A         當插入點位於公式中函數名稱的右側時，插入參數名和括弧。

F3                        將定義的名稱粘貼到公式中。

Alt + =（等號）           插入自動求和函數

Ctrl + "（雙引號）        將活動單元格上方單元格中的數值複製到當前單元格或編輯欄。（也可再加Shift鍵）

Ctrl + "（撇號）          將活動單元格上方單元格中的公式複製到當前單元格或編輯欄。

Ctrl + `（左單引號）      在顯示單元格值和顯示公式之間切換。

F9                        重新計算打開工作薄中的所有工作表，如果選擇了一部分公式，則計算選定部分。

按 Enter 或 Ctrl + Shift + Enter（數組公式）後用計算出的值替換選定部分

Shift + F9                計算活動工作表。

Ctrl + Alt + F9           計算所有打開的工作簿中的所有工作表，無論其在上次計算之後是否進行了更改。

Ctrl + Alt + Shift + F9      重新檢查從屬公式，然後計算所有打開的工作簿中的所有單元格

設置數據的格式：（以下數字為鍵盤區數字，不是小鍵盤數字）

Alt + "（撇號）           顯示「樣式」對話框

Ctrl + 1                  顯示「單元格格式」對話框

Ctrl + Shift + ~         「常規」格式

Ctrl + Shift + 1         使用千位分隔符且不帶小數的格式（-515，151）

Ctrl + Shift + 2         應用含小時和分鐘並標明上午或下午的「時間」格式。

Ctrl + Shift + 3         日期格式（2011-2-21）

Ctrl + Shift + 4         兩位小數的「貸幣」格式（負數在括弧中）

Ctrl + Shift + 5         不帶小數的「百分比」格式

Ctrl + Shift + 6         兩位小數的「科學記數」格式

Ctrl + Shift + 7         對選定單元格應用外邊框

Ctrl + (                 隱藏當前單元格所在行

Ctrl + )                 隱藏當前單元格所在列

Ctrl + Shift + (          取消所有隱藏行

Ctrl + Shift + )          取消所有隱藏列

Ctrl + Shift + _         取消選定單元格的外邊框

Ctrl + B                  加粗

Ctrl + I                  傾斜

Ctrl + U                  下劃線

Ctrl + 5                  刪除線

自動篩選中的快捷鍵：

Alt +↓                   在包含下拉箭頭的單元格中，顯示當前列的「自動篩選」列表。

Alt +↑                   關閉當前列的「自動篩選」列表。

Home                      選擇「自動篩選」列表中的第一項（「（全部）」）。

End                       選擇「自動篩選」列表中的最後一項。

Enter                     根據「自動篩選」列表中的選項篩選區域。

分級顯示數據中的快捷鍵：

Alt + Shift + →         對行或列分組。

Alt + Shift + ←         取消行或列分組。

Ctrl + 8                  顯示或隱藏分級顯示符號。

圖形對象操作快捷鍵：

Ctrl + Shift + C         複製對象屬性

Ctrl + Shift + V         粘貼對象屬性到本對象中