在Excel(Excel)中有多种搜索方式。第一个选项是如果您有大量的数据电子表格，并且需要在一个单元格或一组单元格中查找特定的数据。
第二组选项包括使用VLOOKUP或HLOOKUP等搜索功能，让您可以在一张工作表中搜索数据并将结果输出到第二个单元格位置或不同的工作表中。
在本文中，您将了解在Excel中搜索的所有可能方法，以便您可以根据自己的情况选择合适的方法。
在 Excel 中使用查找
使用Excel打开包含数据的电子表格，您可以使用直词搜索或使用特殊通配符在电子表格中查找任何内容。
1. 选择主(Home)菜单。在功能区的编辑(Editing)组中，选择查找并选择(Find & Select)，然后选择查找(Find)。
您将看到一个简单的查找(Find)和替换窗口，但您可以通过选择(Replace)选项(Options)来查看所有高级选项。
您可以使用以下选项来微调您的搜索：
- 查找内容(Find what)：键入要查找的文本或数字
- 格式：选择此按钮以仅搜索具有您在“(Format)查找格式(Find Format)”窗口中定义的格式的文本
- 范围内(Within)：选择工作表(Sheet)或工作簿(Workbook)以在当前工作表内或整个工作簿内搜索
- 搜索(Search)：选择“按行”(By Rows)或“按列(By Columns)”以一次搜索一行或一次搜索一列（并在找到结果时停止该行或该列）。
- 查找范围：您可以通过让搜索查找(Look in)公式(Formulas)、值(Values)、注释(Notes)或注释(Comments)来加深对电子表格其他区域的搜索
- 匹配大小写(Match case)：这将使用区分大小写的文本匹配进行搜索
- 匹配整个单元格内容(Match entire cell contents)：这将搜索仅包含您键入的文本而不包含其他内容的单元格
如果您选择Find Next，您将在电子表格中看到突出显示文本的每个事件。再次选择该按钮以转到下一个结果。如果您想一次查看所有内容，只需选择Find All。
这会在窗口底部的列表中显示所有结果，以及找到它们的工作表和单元格编号。只需(Just)选择其中任何一个即可查看工作表中的该单元格。
Excel 搜索通配符
在“查找内容(Find)”字段中键入搜索字符时，了解通配符的工作原理很重要。这些使您可以自定义更高级的搜索，以便您可以在Excel中搜索并在工作表中准确找到您要查找的内容。
- 问号 (?)(Question mark (?))：替换任何单个字符或数字。例如，键入c?t可查找以 c 开头并以 t 结尾的任何三个字母的单词，例如 cat、cut 或 cot。
- Asterisk (*)：替换部分单词。例如，键入c*t以从 cart 中查找任意长度的单词并转换为 count 和 court。
- Tilde (~)：使用此字符覆盖上面的特殊字符。例如，如果您真的想找到“用户”这个词？（包括问号）你可以输入user~?这将覆盖特殊的？字符来实际进行搜索，包括问号。
在 Excel 中使用替换
在同一个查找(Find)和替换(Replace)窗口中，如果您选择替换(Replace)选项卡，您将看到其他选项，这些选项不仅可以让您在电子表格中查找文本或数字，还可以用不同的文本或数字替换它们。
您还可以通过选择主(Home)菜单、从功能区的编辑组中选择(Editing)查找和替换(Find & Replace)，然后选择替换，在(Replace)替换(Replace)选项卡上打开此窗口。就像(Just)在上一节中一样，您需要选择选项(Options)才能查看所有高级搜索功能。
所有高级搜索功能的工作方式与它们在“查找”(Find)选项卡中的工作方式完全相同。唯一的区别是现在您将看到一个替换为(Replace with)字段。
此字段的工作方式是您键入要替换您在“查找内容”(Find what)字段中输入的文本的文本或数字。然后，您按照以下步骤搜索和替换您正在搜索的所有实例。
- 选择查找下一个(Find Next)以突出显示您正在搜索的文本或数字的第一个位置。
- 选择替换(Replace)以替换该实例。如果您找到了不想替换的内容，只需选择“查找下一个”(Find Next)即可继续。
- 继续循环查找下一个(Find Next)和替换(Replace)，直到找到并替换您搜索的所有实例。
如果您非常勇敢，您可以选择全部替换(Replace All)，而不是使用上述步骤逐个检查。但是，如果在某些情况下，您要搜索的单词或数字是您不想替换的单元格中的一段文本，则可能会丢失不想替换的数据。
在 Excel 中使用函数进行搜索
Excel中还有许多有用的功能，可让您搜索列、行或表以查找信息并返回相关信息。
以下是可用于在Excel中搜索的最常用函数的几个示例。
- VLookup：在表的一列中搜索数据以返回同一行中另一列的值。
- Index-Match：执行与VLookup相同，但搜索数据不必是搜索表中最左边的列。
- 过滤器(Filters)：您可以使用过滤器过滤掉您不关心的数据并仅显示您正在搜索的数据。
- 查找匹配值(Find Matching Values)：excel 中有多个函数可以让您在Excel中查找重复值，例如Exact和Match。
在Excel(Excel)中搜索数据并不一定很复杂。如果您知道要查找的内容以及要对数据执行的操作，则可以使用一个选项来完成搜索。
How To Search In Excel
There arе multiple ways to search іn Excel. The first options are if you have a masѕiνe spreadsheet of data and need to find a particular piece of data in a cеll or group of cells.
The second set of options include using search functions like VLOOKUP or HLOOKUP that let you search one sheet for data and output the results in a second cell location or in a different worksheet.
In this article, you’ll learn all possible ways to search in Excel so you can choose the right one for your situation.
Using Find In Excel
With Excel open to your spreadsheet with data, you can find anything on the spreadsheet using a straight word search, or using special wildcard characters.
1. Select the Home menu. In the Editing group in the ribbon, select Find & Select then select Find.
You’ll see a simple Find and Replace window, but you can see all of the advanced options by selecting Options.
You can use the following options to fine tune your search:
- Find what: Type the text or number that you want to find
- Format: Select this button to only search for text that has the formatting you define in the Find Format window
- Within: Select Sheet or Workbook to search inside the current sheet or across the entire workbook
- Search: Select By Rows or By Columns to conduct your search a row at a time or a column at a time (and stop on that row or column when it finds a result).
- Look in: You can deepen your search to other areas of your spreadsheet by telling the search to look in Formulas, Values, Notes or Comments
- Match case: This will conduct the search using case sensitive text matching
- Match entire cell contents: This will search for cells that only contain the text you type and nothing else
If you select Find Next, you’ll see each incident in the spreadsheet where the text is found highlighted. Select the button again to move on to the next result. If you want to see them all at once, just select Find All.
This displays all of the results, along with the sheet and cell number where they’re found, in a list at the bottom of the window. Just select any one of them to see that cell in the sheet.
Excel Search Wildcard Characters
When you’re typing search characters into the Find What field, it’s important that you understand how wildcard characters work. These let you customize more advanced search so you can search in Excel and find exactly what you’re looking for in the worksheet.
- Question mark (?): Replace any single character or number. For example, type c?t to find any three letter word that starts with c and ends with t like cat, cut, or cot.
- Asterisk (*): Replace parts of words. For example, type c*t to find any length word from cart and cast to count and court.
- Tilde (~): Use this character to override the special characters above. For example, if you actually want to find the word “user?” (including the question mark) you can type user~? and this will override the special ? character to actually do the search including the question mark.
Using Replace In Excel
In the same Find and Replace window, if you select the Replace tab, you’ll see additional options that’ll let you not only find text or numbers in your spreadsheet but you can replace those with different text or numbers.
You can also open this window at the Replace tab by selecting the Home menu, selecting Find & Replace from the Editing group in the ribbon, and then selecting Replace. Just like in the previous section, you’ll need to select Options to see all of the advanced search features.
All of the advanced search features work exactly like they do in the Find tab. The only difference is that now you’ll see a Replace with field.
The way this field works is you type the text or number you’d like to replace the text you’ve entered into the Find what field with. Then, you follow the steps below to search and replace all instances of what you’re searching for.
- Select Find Next to highlight the first location with the text or number you’re searching for.
- Select Replace to replace that instance. If you’ve found one you don’t want to replace, just select Find Next instead to move on.
- Continue cycling through Find Next and Replace until all of the instances of what your searching for have been found and replaced.
If you’re very brave, you could select Replace All rather than stepping through every one using the procedure above. However if there are any instances where the word or number you’re searching for is a section of text in a cell you don’t want to replace, you could risk losing data that you don’t want to replace.
Using Functions To Search In Excel
There are also lots of useful functions in Excel that let you search columns, rows, or tables to find information and return related information.
The following are a few examples of the most popular functions you can use to search in Excel.
- VLookup: Search for data in one column of a table to return the value of another column in the same row.
- Index-Match: Performs the same as VLookup, but the search data doesn’t have to be the leftmost column in the search table.
- Filters: You can use filters to filter out data you don’t care about and display only the data you’re searching for.
- Find Matching Values: There are multiple functions in excel that let you find duplicate values in Excel, like Exact and Match.
Searching for data in Excel doesn’t have to be complicated. If you know what you’re looking for and what you want to do with the data, there is an option available to accomplish the search.
