如何使用excel快速查找數據
啊南 1981閱讀 2020.06.17
【導語】: Excel中的數據太多,如果一個個的來查找,不僅眼睛看不過來,浪費時間,而且準確率不高,不過這時使用查找功能就能很快的找到自己想要的內容。但是,查找功能怎么使用呢?
利用 Excel 中的“查找和替換”功能在工作簿中搜索某些內容,例如特定的數字或文本字符串等。 你可以找到要引用的搜索項,也可以將其替換為其他內容。 可以在搜索詞中包含問號、波形符、星號或數字等通配符。 可按行和列搜索、在批注或值中搜索,還可在工作表或整個工作簿中搜索。
查找
若要查找內容,請按Ctrl + F,或轉到 "開始" >編輯>查找 & 選擇">查找"。
注意: 在以下示例中,已單擊 "選項" >>按鈕以顯示整個 "查找" 對話框。 默認情況下,它將顯示 "隱藏" 選項。
在 "查找內容: " 框中,鍵入要查找的文本或數字,或單擊 "查找內容: " 框中的箭頭,然后從列表中選擇最近的搜索項。
提示: 你可以在搜索條件中使用
通配符(問號(?)、星號(*)、波形符(~))。
使用問號(?)查找任何單個字符,例如 s? t 找到 "sat" 和 "set"。
使用星號(*)查找任意數量的字符,例如 s * d 可以找到 "sad" 和 "已啟動"。
使用波形符(~),后跟?、* 或 ~ 查找問號、星號或其他波形符(例如,fy91 ~?) 找到 "fy91?"。
單擊 "查找全部" 或 "查找下一處" 以運行搜索。
提示: 單擊 "查找全部" 時,將列出所搜索條件的每個匹配項,然后單擊列表中的特定匹配項將選擇其單元格。 您可以通過單擊列標題對“查找全部”搜索的結果進行排序。
單擊 "選項">>以根據需要進一步定義搜索:
之內 若要在工作表或整個工作簿中搜索數據,請選擇 "工作表" 或 "工作簿"。
搜索 你可以選擇按行(默認)或列搜索。
查找范圍: 若要搜索具有特定詳細信息的數據,請在框中單擊 "公式"、"值"、"注釋" 或 "批注"。
注意: 公式、值、注釋和批注僅在 "查找" 選項卡上可用;"替換" 選項卡上僅有 "公式" 可用。
區分大小寫-如果要搜索區分大小寫的數據,請選中此項。
匹配整個單元格內容-如果要搜索僅包含您在 "查找內容: " 框中鍵入的字符的單元格,請選中此選項。
如果要搜索具有特定格式的文本或數字,請單擊 "格式",然后在 "查找格式" 對話框中進行選擇。
提示: 如果你要查找只符合特定格式的單元格,則可以刪除“查找內容”框中的所有條件,然后選擇特定單元格格式作為示例。 單擊“格式”旁邊的箭頭,單擊“從單元格選擇格式”,然后單擊具有要搜索的格式的單元格。
替換
若要替換文本或數字,請按Ctrl + H,或轉到 "開始" >編輯> "查找" & 選擇">替換"。
注意: 在以下示例中,已單擊 "
選項" >>按鈕以顯示整個 "查找" 對話框。 默認情況下,它將顯示 "隱藏" 選項。
在 "查找內容: " 框中,鍵入要查找的文本或數字,或單擊 "查找內容: " 框中的箭頭,然后從列表中選擇最近的搜索項。
提示: 你可以在搜索條件中使用通配符(問號(?)、星號(*)、波形符(~))。
使用問號(?)查找任何單個字符,例如 s? t 找到 "sat" 和 "set"。
使用星號(*)查找任意數量的字符,例如 s * d 可以找到 "sad" 和 "已啟動"。
使用波形符(~),后跟?、* 或 ~ 查找問號、星號或其他波形符(例如,fy91 ~?) 找到 "fy91?"。
在 "替換為: " 框中,輸入要用于替換搜索文本的文本或數字。
單擊“全部替換”或“替換”。
提示: 單擊 "全部替換" 時,你要搜索的條件的每個匹配項都將被替換,而 "替換" 將一次更新一個匹配項。
單擊 "選項">>以根據需要進一步定義搜索:
之內 若要在工作表或整個工作簿中搜索數據,請選擇 "工作表" 或 "工作簿"。
搜索 你可以選擇按行(默認)或列搜索。
查找范圍: 若要搜索具有特定詳細信息的數據,請在框中單擊 "公式"、"值"、"注釋" 或 "批注"。
注意: 公式、值、注釋和批注僅在 "查找" 選項卡上可用;"替換" 選項卡上僅有 "公式" 可用。
區分大小寫-如果要搜索區分大小寫的數據,請選中此項。
匹配整個單元格內容-如果要搜索僅包含您在 "查找內容: " 框中鍵入的字符的單元格,請選中此選項。
如果要搜索具有特定格式的文本或數字,請單擊 "格式",然后在 "查找格式" 對話框中進行選擇。
提示: 如果你要查找只符合特定格式的單元格,則可以刪除“查找內容”框中的所有條件,然后選擇特定單元格格式作為示例。 單擊“格式”旁邊的箭頭,單擊“從單元格選擇格式”,然后單擊具有要搜索的格式的單元格。
通配符是Excel表格中一個非常強大的符號,主要運用于查找和統計。
Excel表格中常用的通配符有兩個,分別是“?”和“*”。
通配符“?”代表任意單個字符,通配符“*”代表任意多個字符。
下面給大家演示通配符的用法。
具體方法與步驟如下:
使用查找快捷鍵Ctrl+F,此時彈出查找對話框,在查找內容中輸入“可樂***”。
需要注意的是,通配符“?和*”需要在英文狀態下輸入。
點擊查找全部,即可查找出所有包含“可樂”的單元格。
在查找內容中輸入“可樂???”,點擊查找全部,即可查找“可樂”后有三個字符的單元格。
本部分描述了用來檢驗數值或引用類型的九個工作表函數。
這些函數,概括為 IS 類函數,可以檢驗數值的類型并根據參數取值返回 TRUE 或 FALSE。
例如,如果數值為對空白單元格的引用,函數 ISBLANK 返回邏輯值 TRUE,否則返回 FALSE。
語法
ISBLANK(value)
ISERR(value)
ISERROR(value)
ISLOGICAL(value)
ISNA(value)
ISNONTEXT(value)
ISNUMBER(value)
ISREF(value)
ISTEXT(value)
?VALUE 為需要進行檢驗的數值。
分別為:空白(空白單元格)、錯誤值、邏輯值、文本、數字、引用值或對于以上任意參數的名稱引用。
說明
? IS 類函數的參數 value 是不可轉換的。例如在其他大多數需要數字的函數中,文本值“19”會被轉換成數字 19。
然而在公式 ISNUMBER("19") 中,“19”并不由文本值轉換成別的類型的值,函數 ISNUMBER 返回 FALSE。
? IS 類函數在用公式檢驗計算結果時十分有用。當它與函數 IF 結合在一起使用時,可以提供一種方法用來在公式中
查出錯誤值。
生活和工作中我們常常使用信息函數ISBLANK來查看表格是否存在空格,下面教大家如何快速的查看表格是否存在空格。
以此產品銷售表為例。
具體方法與步驟如下:
將光標放在(G3)處,點擊插入函數-信息-ISBLANK函數。
此時彈出對話框,共一項參數。
“值”指需要檢查的單元格。
在“值”中輸入(F3),意思是我們要檢查(F3)是否為空格。
檢查為空格返回“TRUE”,否則返回“FLASE”。
點擊確定,就能得出此單元格是否是空格了。
將光標放在(G3)右下角,呈+形時下拉填充公式就可以快速的查看表格是否存在空格。
使用WPS Office打開表格,快捷鍵“Ctrl+H”打開替換功能。
在“查找內容”,點擊“及“替換為”中輸入相同內容。
點擊“選項”,點擊“替換為”后的“格式”。
選擇“格式設置”,在彈出框選擇“圖案”,并選擇一種高亮顏色。
點擊“全部替換”即可實現查找內容高亮。
在使用表格的過程中,遇到重復的數據很麻煩。
本期教大家:如何快速找出重復數據、避免錄入重復數據
具體方法與步驟如下:
一、快速找出重復數據
首先選中需要查找的數據區域,在菜單欄“數據”-“高亮重復項”設置該區域
點擊確定,即可看到重復的數據被突出顯示了。
在“高亮重復項”的旁邊有一個“刪除重復項”。
選擇要刪除的包含重復項的列,點擊一下,重復項就被刪除了。
二、避免錄入重復數據
選中要錄入數據的單元格區域,點擊“數據”-“拒絕錄入重復項”設置該區域。
設置完畢后,一旦輸入相同數據,就會彈出提示。
但此時再按一次回車,又可以輸入了。
怎樣不允許輸入呢?
選中單元格區域,點擊“數據”-“有效性”,修改“出錯警告”樣式為“停止”。
此時輸入重復值,再按回車鍵,也無法輸入了。
本文介紹 Microsoft Excel 中 EXACT 函數的公式語法和用法。
說明
比較兩個文本字符串,如果它們完全相同,則返回 TRUE,否則返回 FALSE。 函數 EXACT 區分大小寫,但忽略格式上的差異。 使用 EXACT 可以檢驗在文檔中輸入的文本。
語法
EXACT(text1, text2)
EXACT 函數語法具有下列參數:
Text1 必需。 第一個文本字符串。
text2 必需。 第二個文本字符串。
示例
復制下表中的示例數據,然后將其粘貼進新的 Excel 工作表的 A1 單元格中。 要使公式顯示結果,請選中它們,按 F2,然后按 Enter。 如果需要,可調整列寬以查看所有數據。
第一個字符串 |
第二個字符串 |
|
---|---|---|
word |
word |
|
Word |
word |
|
w ord |
word |
|
公式 |
說明 |
結果 |
=EXACT(A2,B2) |
測試第一行中的兩個字符串是否完全相同 |
TRUE |
=EXACT(A3,B3) |
測試第二行中的兩個字符串是否完全相同(A3 中的 "W" 為大寫形式) |
FALSE |
=EXACT(A4,B4) |
測試第三行中的兩個字符串是否完全相同(A4 的 "w" 和 "ord" 之間包含一個空格) |
FALSE |
您可以快速找到您正在尋找在較長的列表的字段中無需滾動,在數據透視表功能中使用新的搜索字段。
單擊您想要搜索的字段的數據透視表。
在數據透視表字段窗格中,輸入要查找的字段名稱。
要搜索的字段現在將顯示在列表的頂部。
假設您需要在一段較新的公式中更改某些公式,但您是工作簿的新工作簿,并且不知道這些公式的位置。 下面介紹了如何查找它們。
選擇一個單元格或單元格區域。
如果選擇一個單元格,則搜索整個工作表。 如果選擇區域,則只需搜索該區域。
單擊 "開始>查找" & 選擇">"轉到特殊"。
單擊 "公式",如果需要,請清除 "公式" 下方的任何復選框。
4. 單擊"確定"。
如果想要顯示可能駐留在隱藏行或列中的單元格,可能很難找到它們。 方法是先選擇工作表中的所有可見單元格,這也會顯示隱藏的行和列。
查找隱藏的單元格
請按以下步驟操作:
選擇包含要查找的隱藏行和列的工作表,然后使用下列方法之一訪問該特殊功能:
按F5 > "特殊"。
按Ctrl + G > "特殊"。
或者在 "開始" 選項卡上的 "編輯" 組中,單擊 "查找" & 選擇">轉到特殊"。
在“選擇”下,單擊“可見單元格”,然后單擊“確定”。
所有可見單元格均已選中,并且與隱藏的行和列相鄰的行和列的邊框將顯示為白色邊框。
注意: 單擊工作表上的任意位置,取消選定可見單元格。 如果需要顯示的隱藏單元格位于可見工作表區域之外,請使用滾動條在文檔中移動,直到包含這些單元格的隱藏行和列可見。
假設您想要使用他們的徽章號碼來查找員工的電話分機號碼, 或使用銷售金額的傭金的正確費率。 查找數據以快速有效地查找列表中的特定數據, 并自動驗證您是否正在使用正確的數據。 查找數據后, 可以執行計算或顯示返回值的結果。 可通過多種方法在數據列表中查找值并顯示結果。
使用精確匹配在列表中垂直查找值
若要執行此任務, 可以使用 VLOOKUP 函數或 INDEX 和 MATCH 函數的組合。
VLOOKUP 示例
索引和匹配示例
用簡單的漢語表達其意思:
=INDEX(我希望在 C2 至 C10 單元格中查找返回值,返回值符合(Kale,它位于 B2 至 B10 單元格數組中,返回值是 Kale 對應的第一個值))
該公式查找 C2: C10 中的第一個值, 該值對應于Kale (在 B7 中), 并返回值在 C7 (100) 中, 該值是與Kale匹配的第一個值。
使用近似匹配在列表中垂直查找值
若要執行此操作, 請使用 VLOOKUP 函數。
重要: 請確保第一行中的值已按升序排序。
在上面的示例中, VLOOKUP 在 A2: B7 區域中查找具有6個 tardies 的學生的名字。 表中沒有6 tardies 的條目, 因此 VLOOKUP 查找下一個小于6的最大匹配, 并查找值5與第一個名稱Dave相關聯, 從而返回Dave。
使用精確匹配在未知大小的列表中垂直查找值
若要執行此任務, 請使用 OFFSET 和 MATCH 函數。
注意: 當數據位于每天刷新的外部數據區域中時, 請使用此方法。 您知道價格在列 B 中, 但不知道服務器將返回的數據行數, 并且第一列不按字母順序排序。
C1是區域左上方的單元格 (也稱為起始單元格)。
MATCH ("Oranges", C2: c7, 0)查找 C2: c7 范圍中的 Oranges。 不應在區域中包含起始單元格。
1是返回值應位于的起始單元格右側的列數。 在我們的示例中, 返回值來自 D 列, 即 "銷售額"。
使用精確匹配在列表中水平查找值
若要執行此任務, 請使用 HLOOKUP 函數。 請參閱下面的示例:
HLOOKUP 查找 "銷售額" 列, 并返回指定區域中第5行的值。
使用近似匹配在列表中水平查找值
若要執行此任務, 請使用 HLOOKUP 函數。
重要: 請確保第一行中的值已按升序排序。
在上面的示例中, HLOOKUP 將在指定區域的第3行中查找值11000。 它不會找到 11000, 因此查找小于1100的下一個最大值, 并返回10543。
使用查閱向導創建查找公式 (僅限Excel 2007 )
注意: Excel 2010 中的查閱向導外接程序已停用。 此功能已由函數向導和可用的查找和引用函數 (參考)取代。
在Excel 2007 中, "查閱向導" 基于具有行標簽和列標簽的工作表數據創建查找公式。 當您知道某一列中的值時, "查閱向導" 可幫助您查找該行中的其他值, 反之亦然。 查閱向導在所創建的公式中使用索引和匹配。
單擊區域中的單元格。
在 "公式" 選項卡上的 "解決方案" 組中, 單擊 "查找"。
如果 "查找" 命令不可用, 則需要加載查閱向導加載項 程序。
如何加載查閱向導加載項程序
單擊 " Microsoft Office 按鈕 , 單擊 " Excel 選項", 然后單擊 "加載項" 類別。
在“管理”框中,單擊“Excel 加載項”,然后單擊“轉到”。
在 "可用加載項" 對話框中, 選中 "查閱向導" 旁邊的復選框, 然后單擊"確定"。
按照向導中的說明操作。
注意: Power Query 在 Excel 2016 中稱為“獲取和轉換”。 在此處提供的信息適用于兩者。
在 Power Query 中,一旦由數據專員或其他商業用戶使用 Power BI for Microsoft 365 共享查詢,這些共享查詢就會在 Power BI 元數據存儲庫中可用。 組織中的用戶可以通過使用 Power Query 中的 "聯機搜索" 選項使用 Power Query 中的 "聯機搜索" 選項來查找和使用這些共享查詢(如果與他們共享),以便在其數據分析和報告中使用查詢中的基礎數據。
重要: 共享查詢可能需要長達五分鐘才能通過 Power Query 中的聯機搜索獲得。 這是因為搜索索引更新可能需要長達五分鐘才能完成。
要使用 Power Query 查找和使用共享查詢,請執行以下操作:
從 Power Query 登錄到 Power BI(如果尚未登錄)。 在 Excel 中,在“Power Query”選項卡上單擊“登錄”。 在 "登錄" 對話框中,單擊 "登錄",然后輸入用于注冊 Power BI 的組織帳戶信息。
重要: 使用你的組織帳戶登錄 Power BI 可使你查找和使用已為組織內的用戶共享的查詢。 如果未登錄 Power BI,則無法在 Power Query 中搜索組織內共享的查詢。
在 " POWER QUERY " 選項卡上,單擊 "聯機搜索"。
在單擊 "聯機搜索" 時,將顯示 "搜索" 功能區和 "聯機搜索" 窗格,使你能夠范圍和篩選查詢搜索。
注意: 范圍和篩選查詢搜索是可選的;它僅可幫助您縮小搜索范圍以獲得快速結果。 如果不想使用任何范圍或篩選參數,則可以直接在 "聯機搜索" 窗格的框中鍵入搜索關鍵字,然后按 enter 或單擊 " "。
在 "搜索" 功能區的 "范圍" 區域或 "聯機搜索" 窗格中的下拉列表中,選擇查詢搜索的范圍。 選項包括:
全部:搜索所有源,包括由你共享的查詢,以及企業內其他用戶共享的查詢。 這是默認選擇。
我的共享:僅搜索已由你共享的查詢。
組織:僅搜索已使用 Power Query 在組織內共享的查詢。
根據 "搜索" 功能區的 "優化" 區域中的以下參數優化查詢搜索:
名稱:根據查詢名稱優化搜索。
說明:基于查詢說明優化搜索。
自:優化搜索以返回僅由指定別名或用戶帳戶共享的查詢的結果。
數據源:優化搜索以僅從指定的基礎數據源名稱返回結果。
上次修改時間:根據上次修改查詢的日期優化搜索。 您可以從 "今天" 到 "去年"的下拉列表中選擇預先指定的時間間隔。
列名稱:優化搜索以僅從指定的列名稱返回結果。
認證:優化搜索以返回經鑒定的查詢。
單擊精簡參數時,"搜索" 功能區會在 "聯機搜索" 窗格的框中插入參數關鍵字。 您也可以在 "搜索" 框中手動鍵入 "精簡關鍵字",然后在 "聯機搜索" 窗格的框中指定關鍵字的必需值。
例如,若要在組織中搜索其名稱中包含單詞 "Sample" 的共享查詢,請單擊 "范圍" 區域中的 "組織",然后單擊 "搜索" 功能區的 "優化" 區域中的 "名稱"。 這將設置作用域并在 "聯機搜索" 窗格的 "搜索" 框中,插入 name:(name) 的關鍵字以按名稱篩選結果。
接下來,在 name:(Sample)中鍵入name關鍵字的 "Sample",然后按 ENTER 或單擊 " " 以搜索共享查詢。 正在進行搜索時,如果要停止搜索,可以單擊 "搜索" 框中的 " x "。
搜索結果將顯示在 "聯機搜索" 窗格中。
由你執行的查詢搜索歷史記錄存儲在 Power Query 中,你可以單擊 "搜索" 功能區中的 "最近的搜索" 以快速查看和選擇過去的查詢搜索之一。
將鼠標指向或單擊搜索結果中的共享查詢名稱時,將在左窗格中看到共享查詢的預覽。
"預覽" 彈出屏幕顯示查詢引用的數據、數據集中的列、上次修改的時間戳、共享查詢的用戶以及指向文檔的鏈接(如果提供查詢)的快照。 可以單擊 "預覽" 對話框中的列名稱,跳轉到預覽中的相應數據列。
注意: 對于在組織內共享的查詢,只有當用戶在開始共享查詢時選擇包括預覽時,才會在 "預覽" 彈出屏幕中顯示數據快照。
此外,指定的關鍵字值在 "飛出" 屏幕的預覽中突出顯示。 例如,突出顯示 "sample" 一詞。 關鍵字突出顯示適用于共享查詢元數據(如標題和說明)以及共享查詢引用的基礎數據。
單擊 "添加到工作表" 將共享查詢所引用的數據導入為新工作表。 或者,也可以單擊 "篩選 & 形狀" 以在將引用的數據導入工作表之前進一步對其進行改進。
如果您不具有共享查詢引用的基礎數據源的訪問權限,則會出現一個對話框,讓您請求對數據源的訪問權限。 在出現的對話框中,單擊 "請求訪問" 鏈接。 根據為數據源設置的訪問請求信息的方式,系統將提示你向別名發送電子郵件或請求訪問 URL。
如果共享查詢中的基礎數據已被合并(合并或追加)使用多個數據源,則必須先指定隱私級別,然后才能使用共享查詢中的基礎合并數據。
重要:
數據目錄棄用通知
Excel 和 Power Query 中的數據目錄功能將在2019年3月4日停止工作。 在該日期之前,用戶將能夠使用已發布到數據目錄中的內容。 但是,從2018年12月3日起,用戶將無法將新的或更新的查詢發布到數據目錄中。 從2018年8月1日起開始,我們將停止加入新客戶(租戶)以使用此功能。 我們建議下載你的共享查詢,以便你可以繼續在數據目錄之外使用2019年3月4日之外的版本。 你可以從 "我的數據目錄查詢" 任務窗格中使用 "打開" 選項從每個查詢中下載查詢的完整定義。