Excel 有哪些和 vlookup 一樣重要的函數或功能？

12-27

可以實現什麼功能？用法？請舉例。

我決定寫一些我覺得重要的功能
(很多都是我在工作中積累的經驗，不一定適用於所有人，希望能夠給大家帶來分解問題的新角度)

從B2單元格開始一個新的表
這樣的好處是，列印的時候可以通過：

第一行的高度調整上邊距
第一列的寬度調整左邊距

F4：重複上次操作
在Office系列軟體中，F4的意思就是執行上次操作，相當於Ctrl+Y
多嘗試一下吧~

F4：固定索引維度
經常在使用公式索引的時候會遇到希望索引某一個單元格的值，自動公式填充的時候Excel會自己匹配位置：

使用F4固定維度之後填充：

$之後的那一維度被鎖定，可抑只鎖定一個維度，也可以鎖定2個維度

自定義名稱
還是用上述例子，公式看起來讓人眼花繚亂，單元格右鍵，定義名稱：

支持寫備註：

使用名稱的時候出現備選，並且會自動出現設置的備註ScreenTip：

注意：部分名稱可能會和Excel的自有名稱重合，需要替換為合法名稱
數組也可以使用名稱：

VLookUp的第二個參數LookUpTable就可以直接寫上PeopleInfo了

最有一個也是最重要的是VBA可以直接引用、賦值自定義的名稱：

運行結果

自定義函數：
無恥的安利一下我專欄的文章：
Excel 自定義公式 - 有關 Excel 的一些總結與交流 - 知乎專欄

大殺器VBA
對工程狗來說VBA的好處就是直接可以在Office裡面用，輸出的東西可以直接分發給別人，還可以做很多傻瓜化的小工具之類。

隱藏（表單或行列）
正常的右鍵隱藏可以保護一些數據不被誤修改
這裡再說一下一種表單的隱藏，隱藏之後對方無法通過右鍵恢復：
Alt+F11進入VBE界面，Ctrl+G調出立即窗口：

xlSheetHidden = 普通的隱藏
xlSheetVeryHidden = 不能通過右鍵取消隱藏的隱藏
xlSheetVisible = 可見

也可以設置Sheet的屬性(屬性窗口的快捷鍵剛好也是F4)

效果如下：

再次安利一下：
Excel 有哪些可能需要熟練掌握而很多人不會的技能？ - 知乎用戶的回答看心情不定期更新……

一、基本功能
1、Ctrl+任意方向鍵：定位神器；
2、Ctrl+shift+任意方向鍵：選擇神器，針對超大數據；
3、選中一個區域，滑鼠移到選區邊框，按滑鼠右鍵：移動、複製、插入一鍵搞定；
4、文本導入：當複製文本時，「粘貼」命令下會出現「使用文本導入嚮導」，可以按照指定字元分拆字元串；
5、當多行數據中存在幾行自動換行數據時，可以全選數據後，選擇「自動換行」，再取消「自動換行」；
6、條件格式(使用公式)：使用公式標註特定記錄，有時很方便

7、全選工作表，將滑鼠放置在兩列(行)中間，待滑鼠變為雙向箭頭圖標時，雙擊鼠標，可以實現所有列(行)達到最適列寬(行高)；
8、「選擇性粘貼」》「運算」：可是將文本數值轉換為數值；
9、「分列」：
1)將數值轉換為文本；
2)按照指定字元將字元串分拆；函數：
1、「計算選項」》「手動」：
在公式計算量太大時，可以選擇手動計算，避免稍作修改，電腦假死；
2、「選擇性粘貼」》「數值」：
當公式的值不會發生變動，而且重新計算需要耗費大量時間時，可以選中已經計算完成的公式，將結果粘貼為值；
3、vlookup函數:
1)結合if函數，更改range列順序；
2)使用通配符；

4、countif函數統計重複次數；

數據透視表、圖表、宏——簡單易上手，功能強大

說一些平時自己會用到的吧，比較基礎：

1.
MATCH 和 OFFSET 配套使用，彌補VLOOKUP只能查找在首列的不足（HLOOKUP只能查首行）：先用MATCH找到查找值在列中的相對位置，第n個，返回數字n。再用OFFSET選中需要的數據列的列首作為reference，移動Match-1列，會返回移動後單元格的值。

2.
命名和INDIRECT配套使用。選中表格區域，左上角直接改命名，INDIRECT可以將文本轉換成以文本命名的區域，在文本的組成中可以用例如INDIRECT(「TEXT」C1)，如果C1單元格里的值是數字1，它就會返回之前命名為「TEXT1」的區域。比如整理一年的數據時，把12個月的數據黏貼在一張sheet裡面，每個月的數據表格命名一下，之後用這種方法匯總整理就可以一個公式搞定啦。此外，命名可以用數據-名稱管理器來批量管理。

3.
INDEX。在一個區域內，輸入行列，就可以返回所選單元格的值。一般也是行和列是嵌套了其他函數的，比如MATCH。

4.
SUMIF,SUMIFS。對所選區域內符合一定條件的單元格求和，用公式解決了要用數據透視表做的分類求和。

5.
SEARCH和MID配套使用。從長度不等但有固定格式的長文本中選出所需文本。

6.
「全部查找，全部選中，標色」，然後對被標色的列進行篩選，選「按顏色篩選」。要分類求和的另一種方法。和SUMIF比略麻煩，好處是可以分類匯總一些值不完全一樣，但含相同關鍵字的情況。

7.
「數據-假設分析-單變數求解」。可以設定一個單元格的值，反推出有公式聯繫著的另一個單元格應該等於的值。更高級的功能用「規劃求解」

8.
此外檢查公式可以用「公式-公式審核-公式求值」來做。

9.
如果做展示什麼的想把表格做得只管漂亮一點，可以用「條件格式」，其中的「新建規則」，打開的窗口最底下一欄選項「使用公式確定要設置格式的單元格」，選這一項可以用公式實現EXCEL自帶的裡面沒有的選擇方式。

10.
編輯公式時，「F4」鎖定，「F9」將公式轉化為值。

很多啊，那些常用的都重要。
功能方面：
查找、定位、排序、篩選、分列、快速填充、條件格式、自定義格式、圖表、數據透視表......
函數方面：
Sumif、Countif、Vlookup、Sumproduct、Lookup、Match、Index、Offset......
這些功能和函數就象我們的手指，各有各的特點、各有各的用途，都是不可或缺的。
這些在其他回答都有列舉或說明，我站在另外的角度來談談我的理解

上述常用的功能和函數對我們是否重要，取決於二點：
一是取決於我們是否掌握其基本用法
一些工作多年的職場人員，連SUMIF、VLOOKUP都不會用，但他們一樣把製表工作搞得熱火朝天，這些函數對他們來說就不重要。對他們來說，重要的是因循守舊、不脫離舒適區、按自己習慣的方法製作表格，因為它是純手工製作的，綠色天然，沒有工業機器的金銅之氣。
但是，如果我們掌握了這二個函數的基本用法，就知道這二個函數很重要。它們可以極大地提高我們的工作效率。同理，其他常用的函數，也是很重要的，只是因為我們沒有掌握，不知道其功能的強大，沒有認識到其重要性。
比如Vlookup適用於單條件查找，但是如果要多條件查找就勉為其難了，這時使用LOOKUP多條件查找就非常方便。
多條件查找的經典格式

＝LOOKUP（1,0/（條件1*條件2*條件3）,引用區域）

Sumif、Countif適用於單條件求和、計數，如果要多條件求和、計數，在2007版新增SUMIFS、Countifs函數之前，我們要多條件求和計數，通常都是用Sumproduct來多條件求和和計數的：

SUMPRODUCT(條件1*條件2*條件3*求和區域)

SUMPRODUCT(條件1*條件2*條件3)

二是要看你是否深入探索並擴展出其他用途。如果你將一項功能掌握得非常熟練，那麼另外某項類似的功能就相對不重要了。
比如SUMIF和VLOOKUP這二個函數，對大部分人來說，應該是最常用，最重要的。我們感受到Excel函數的強大，被Excel函數所吸引並著迷，通常是這二個函數將我們帶入Excel華麗而宏大的殿堂的。
但是如果你掌握了其他函數，那這二個的重要性就相對下降了，比如Sumproduct大部數情況下能替代Sumif和Countif，而Index+Match組合也被替代Vlookup，甚至Lookup也能替代Vlookup。
從功能來講，如果我們深入掌握了快速填充，知道其功能和特點，在很多情況下就能讓分列和文本函數下崗待業。如果我們深入掌握了查找和替換，那麼，在很多情況下，也能替補定位功能出場。
下面舉例說明：
案例一：快速填充是如何讓分列功能和文本函數下崗待業的

知乎回答禁止傳GIF動圖，一張張截圖、傳圖片太累了，本寶寶手累心也累，請大家移步本人的專欄看操作動圖。
Excel 有哪些和 vlookup 一樣重要的函數或功能？ - Excel偷懶的技術 - 知乎專欄

案例二：查找功能替補定位功能出場，效果如何？

用查找實現「定位－批註」功能；

用查找實現「定位－常量」功能；

用查找實現「定位－公式」功能；

用查找實現「定位－空值」功能；

用查找實現「定位－可見單元格」功能；

用查找實現「定位－從屬單元格」部分功能

詳細操作就不一一貼圖和碼字了，請大家觀看視頻，免費的

Excel偷懶的技術：查找替換居然還能這樣用

案例三：不加V的LOOKUP是如何讓加了V的VLOOKUP自愧不如的

1、多條件查找

http://weixin.qq.com/r/lUSagj3EaGAXrXnh9xGH (二維碼自動識別)

2、提取字元串中的數字

3、返回最後一個值

http://weixin.qq.com/r/lUSagj3EaGAXrXnh9xGH (二維碼自動識別)

4、返回第一個值

http://weixin.qq.com/r/lUSagj3EaGAXrXnh9xGH (二維碼自動識別)

5、取字元串的最後一節

http://weixin.qq.com/r/lUSagj3EaGAXrXnh9xGH (二維碼自動識別)

6、查找某客戶的交易的第一筆最後一筆

http://weixin.qq.com/r/lUSagj3EaGAXrXnh9xGH (二維碼自動識別)

7、查找某商品的最新單價（源數據是亂序）
http://weixin.qq.com/r/lUSagj3EaGAXrXnh9xGH (二維碼自動識別)

8、根據不規則簡稱查找全稱

9、查找最接近的數、大於X的最小值、小於X的最大值

http://weixin.qq.com/r/lUSagj3EaGAXrXnh9xGH (二維碼自動識別)

LOOKUP的實用的公式還有更多，比如提取唯一值列表、單條件篩選明細、多條件篩選明細、指定返回第幾條（單條件、多條件）。這些就不一一列舉了。下次就LOOKUP的應用專門寫一篇文章介紹，歡迎大家關注我的微信公眾號：Excel偷懶的技術。

本公眾號堅持分享Excel原創文章，求實用、接地近、不炫技。

EXCEL的功能只有不常用的，沒有不重要的。
你把所有函數的使用方法全都背下來，也不能說函數會用了。
哪怕是最基本的操作、功能當中也有許多你不知道的東西。
-----------------------------------------------------------------------------------
比如如何批量替換單元格當中的回車符。

圖例當中將回車符替換成空格。
用函數可以解決，用宏也可以解決，
但是，最最稀鬆平常的替換功能也能解決。（alt+10）
------------------------------------------------------------------------------------
還想寫點什麼的，但算了，不吐槽了。

我認真的。

其次就是絕對，相對，混合三個引用的概念。

其餘的功夫就在excel之外了。

補充最高贊答案，countif計數函數，計算重複單元格的個數。

index+match函數一起使用，可以彌補vlookup函數查找信息列必須在首列的缺點，代替vlookup的功能。

必須是分列！Text to column！清洗數據好用的不得了

保存！！！！！沒有比這更重要的技巧了。如果要加個條件，那就是經常保存！！！

我來答一個吧。
if函數，理論上來說，if函數可以完成所有函數能完成的任務（因為計算機本身就是個if函數的集合）。
—---------分割線-----------—
手機答題，未完待續。

剛剛做完噁心的MGTS作業，怒答！難道不是what if analysis，solver，pivot?

countifs 可以用這個函數統計你想得到的某種條件下的數值的數量。

index+match

lookup
index match
indirect match
offset match

所有能接收區域的和所有能返回區域的

例如sumifs 注意結尾的s，類似函數很多，好用

sum！