Main appointment office program Excel is the implementation of calculations. The document of this program (book) may contain many sheets with long tables filled with numbers, text or formulas. Automated quick search Allows you to find the necessary cells in them.

Simple search

To search for value in the Excel table, you must open the "Home and Replace" tool drop-down list and click the "Find" item. The same effect can be obtained using the Ctrl + F key combination.

In the simplest case, in the "Find and Replace" window that appears, you must enter the desired value and click "Find all".

As you can see, the search results appeared at the bottom of the dialog box. The found values \u200b\u200bare shaped in red in the table. If instead of "finding everything" click "Find Next", first the first cell search will be made with this value, and when you repeatedly click the second.

Similarly, the text is searched. In this case, the search string is recruited in the search bar.

If the data or text is not looking for in the entire Excel table, the search area must first be highlighted.

Advanced Search

Suppose that you need to find all values \u200b\u200bin the range from 3000 to 3999. In this case, in the search string, you should dial 3 ???. Wildcard "?" Replaces any other.

Analyzing the search results, it can be noted that, along with the correct 9 results, the program also issued unexpected shut-covered in red. They are associated with the presence of figures in the cell or formula 3.

You can be sought by most of the results obtained, ignoring the wrong. But the search function in Excel 2010 is capable of working much more accurate. For this purpose, the "Parameters" tool in the dialog box is intended.

Climbing "Parameters", the user gets the ability to extended the search. First of all, we will pay attention to the "Search Area" item, in which the default value "Formula" is set.

This means that the search was made, including in those cells where there is no meaning, but formula. The presence of figures in them 3 gave three incorrect results. If you select "Values" as a search area, you will only search for data and incorrect results associated with formulas will disappear.

In order to get rid of the only remaining wrong result on the first line, in the extended search window you need to select the item "Cell entirely". After that, the search result becomes accurate by 100%.

Such a result could be provided by immediately by selecting the "cell entirely" item (even leaving the "Formula" to the "search area").

Now let's turn to the item "Search".

If instead of the default "on the sheet" to select the value "in the book", then there is no need to be on the sheet of the desired cells. In the screenshot, it can be seen that the user initiated the search by being on empty sheet 2.

The next extended search window is "viewing", having two values. By default, "on strings" is set, which means the sequence of scanning the cells on the rows. The choice of another value is "by columns", only the search direction and the sequence of issuing results will change.

When searching in microsoft documents Excel, you can use another wildcard - "*". If considered "?" Meaning any character, then "*" replaces not one, but any number of characters. Below is a search screenshot for the word Louisiana.

Sometimes you need to consider the symbol register. If the word Louisiana is written with a small letter, the search results will not change. But if you choose "take into account the register" in the extended search window, the search will be unsuccessful. The program will consider the words Louisiana and Louisiana different, and, of course, will not find the first one.

Variety of search

Search for coincidences

Sometimes it is necessary to detect repeating values \u200b\u200bin the table. To search for coincidences, you first need to highlight the search range. Then, on the same tab "Home" in the "Styles" group, open the "Conditional Formatting" tool. Next, sequentially select the items "Morning Rules" and "Repeating Values".

The result is presented in the screenshot below.

If necessary, the user can change the color of the visual display of the coincided cells.

Filtration

Another search variety is filtering. Suppose that the user wants to find in column numerical values ranging from 3000 to 4000.

As can be seen, only strings satisfy the entered condition were displayed. All others were temporarily hidden. To return to the initial state, step 2 should be repeated.

Various search options were considered on excel Example 2010. How to search for excel other versions? The difference in the filtering transition is in version 2003. In the "Data" menu, you should sequentially select the "Filter" command, "Auto Filter", "Condition" and "Custom Auto Filter".

Video: Search in Excel Table

Among thousand rows and dozens of data columns manually in the Excel table to find something almost impossible. The only option is to use some kind of search function, and then we will look at how the search is performed in the Excel table.

To search for data in the Excel table, you must use the menu item "Find and highlight" On the tab "The main"in which you need to choose the option "To find" Or use to call a key combination "Ctrl + F".

For example, try to find the required number among the data of our table, since it is when searching for numbers it is necessary to consider some search subtleties. We will search in the table Excel Number "10".

After selecting the required menu item in the search box that appears, we enter the desired value. We have two options for finding values \u200b\u200bin the Excel table, it is to find all the coincidences at once by clicking the button. "Find all" Or immediately browse each found cell, pressing each time the button "Find further". When using the button "Find further" It should also be taken into account the current location of the active cell, as the search will begin with this position.

Let's try to find all the values \u200b\u200bat once, with all the found it will be listed in the window under the search setting. If you leave all the default settings, the search result will not be exactly the way we expected.

To properly search for data in the Excel table, press the button "Parameters" And set up the search area. Now the desired value is designed even in the formulas used in cells for calculations. We need to specify the search only in the values \u200b\u200band, if you wish, you can still specify the format of the required data.

When searching for words in the Excel table, you should also take into account all these subtleties and for example, you can even consider the case of letters.

Well, lastly consider how to make the search for data in Excele only in the required area of \u200b\u200bthe sheet. As can be seen from our example, the desired value "10" It occurs immediately in all data columns. If it is necessary to find this value, allow, only in the first column, you must select this column or any area of \u200b\u200bthe values \u200b\u200bin which you want to search, and then start searching.

Work with VB Project (12)
Conditional formatting (5)
Lists and ranges (5)
Macros (VBA Procedures) (63)
Miscellaneous (39)
Excel bugs and glitches (3)

Find in the cell any word from the list

Suppose you get from the supplier / customer / customer completed table with a list of products:

And this table must be compared with the articles / codes of the product in the product directory you have:

As can be seen - only articles without names in our catalog. The customer in addition to the articles also the name of the goods, i.e. Many too much. And you need to understand which goods are present in your catalog, and which is not:


There are no standard formulas in Excel for such a search and comparison. Of course, you can try to apply an HDP with wildcard symbols first to one table, and then to another. But if such an operation must be done over time, then you can register for several formulas to each table directly say - not Comilfo.
Therefore, I decided today to demonstrate a formula that without any additional. Manipulations will help such a comparison. To figure yourself out on your own, I recommend Download File:
Download file:

(49.5 KIB, 13 249 downloads)

On the "order" sheet in this file, a table received from the Customer, and on the sheet "Catalog" our articles.
The formula itself on the example of the file will look like this:

View (2; 1 / Search (Catalog! $ A $ 2: $ A $ 11; A2); Catalog! $ A $ 2: $ A $ 11)
\u003d Lookup (2.1 / Search (Catalog! $ A $ 2: $ A $ 11, A2), Catalog! $ A $ 2: $ A $ 11)
This formula will return the name of the article if there is at least one article from the catalog and # N / D (# N / A) If the article is not found in the catalog.
Before referring to this formula with all sorts of additions (like the form of removing unnecessary # H / D) let's understand how it works.
The Viewing Function (Lookup) is looking for a specified value (2) in the specified range (array - the second argument). As a range, an array of cells is usually given, but the view feature has the first feature necessary for us - it tries to transform directly into the array of any expression recorded by the second argument. In other words, she calculates the expression in this argument than we use, substituting as the second argument. Expression: 1 / Search (Catalog! $ A $ 2: $ A $ 11; A2). Part of the search (Catalog! $ A $ 2: $ A $ 11; A2) is searched by each value from the list of the directory in the A2 cell (name from the customer's table). If the value is found, the position number of the first symbol of the found value is returned. If the value is not found - the value of the error # value is returned! (# Value!). Now the second feature: the function requires the location of the data in the array in ascending order. If the data is located otherwise, the function will view an array until it finds the value more than the desired, but the closest to it is approximate (although the data allows - for a more accurate search, it is still better to sort the list ascending). Therefore, first we divide the search for expression (Catalog! $ A $ 2: $ A $ 11; A2) to get an array of the form: (0.0181818181818182: # mean!: # Mean!: # Mean! !: # Mean !: # mean!: # Mean !: # mean !}
Well, as a desired value we will apply the function 2 - Obviously greater number than can meet in the array at all (because the unit divided into any number will be less than two). And as a result, we get a position in the array, which meets the last coincidence from the catalog. After that, the View feature will remember this position and return the value from the array directory! $ A $ 2: $ A $ 11 (third argument) recorded in this array for this position.
You can view the stages of calculating the function yourself for each cell, I just give the steps a little in an extended form:

1. \u003d View (2; 1 / Search (Catalog! $ A $ 2: $ A $ 11; A2); Catalog! $ A $ 2: $ A $ 11)
2. \u003d View (2;
   1 / (55: # mean!: # Meaning!: # Mean!: # Mean !: # meaning!: # Meaning!: # Mean!: # Meaning !: # mean;!}
   Catalog! $ A $ 2: $ A $ 11)
3. \u003d View (2; (0.018181818181818181818182: # mean !: # meaning!: # Meaning!: # Mean !: # meaning!: # Mean!: # Mean!: # Mean!;Каталог!$A$2:$A$11)!}
4. \u003d View (2;
   1;
   ("Fm2-3320": "q5949x": "CE321A": "CE322A": "CE323A": "00064073": "CX292708": "CX292709": "CX292710"))
5. \u003d "FM2-3320"

Now we have a little referring to the function and make a couple of implementations
Sales 1:
Instead of articles and # n / d, they withdraw for the found positions "there is", and for missing "not found in the catalog":
\u003d If (Und (view (2; 1 / Search (Catalog! $ A $ 2: $ A $ 11; A2))); "not found in the catalog"; "there is")
\u003d If (ISNA (Lookup (2.1 / Search (Catalog! $ A $ 2: $ A $ 11, a2))), "not found in the catalog", "there is")
The operation of the function is simple - with viewing (Lookup) figured out, so only EAN have remained and if.
EAN (ISNA) returns Truth (True) If the expression inside it returns the error value # N / D (# N / A) and FALSE (FALSE) If the expression inside does not return the value of this error.
If (if) returns what is indicated by the second argument if the expression in the first is Truth (True) and what is indicated by the third argument if the expression of the first argument FALSE (FALSE).

Sales 2:
Instead # n / d, they withdraw "not found in the catalog", but if the articles are found - we will bring the names of these articles:
\u003d If utility (view (2; 1 / Search (Catalog! $ A $ 2: $ A $ 11; A2); Catalog! $ A $ 2: $ A $ 11); "No in the catalog"
\u003d IFERROR (LOOKUP (2.1 / SEARCH (Catalog! $ A $ 2: $ A $ 11, A2), Catalog! $ A $ 2: $ A $ 11), "No in the catalog")
I described in detail in detail in this article in detail in this article: as in a cell with a formula instead of error to show 0.
If briefly, if the expression specified by the first argument of the function returns the value of any error, the function will return what is written by the second argument (in our case, this text "is not found in the catalog"). If the expression does not return the error, then the function if the error is recorded that the expression was obtained in the first argument (in our case it will be the name of the article).

Sales 3.
It is necessary not to simply determine what article corresponds to, but also to withdraw the price for the name for this article (the prices themselves must be located in the sheet b column directory):
\u003d If utility (2; 1 / Search (Catalog! $ A $ 2: $ A $ 11; A2); Catalog! $ B $ 2: $ B $ 11); "")
\u003d IFERROR (Lookup (2.1 / Search (Catalog! $ A $ 2: $ A $ 11, A2), Catalog! $ B $ 2: $ B $ 11), ""

A couple of important comments:

• data on the sheet with articles should not contain empty cells. Otherwise, with a large share of the likelihood of the formula will return the value of the empty cell, and not that suitable for the search terms
• the formula searches in such a way that it is seen by any coincidence. For example, a figure is recorded as an article 1 and in the name of the names can meet beyond the whole 1 more and 123 , 651123 , FG1412NM. etc. For all these items, article 1 may be selected, because It is contained in each name. As a rule, it can occur if the article 1 is located at the end of the list

Therefore, desirable before using the formula, sort the list Ascending (from smaller to more, from A to Z).

In the example attached at the beginning, you will find all disassembled options.

If you need to output all the names, then you can use the function contains_Od_iz from my add-in MULTEX.

The article helped? Share a link with friends! Video lessons

("Bottom Bar" :( "TextStyle": "Static", "TextPositionStatic": "Bottom", "TextAutohide": True, "TextPositionMarginStatic": 0, "TextPositionDynamic": "Bottomleft", "TextPositionMarginleft": 24, " textpositionmarginright ": 24," textpositionmargintop ": 24," textpositionmarginbottom ": 24," texteffect ":" slide "," texteffecteasing ":" easeOutCubic "," texteffectduration ": 600," texteffectslidedirection ":" left "," texteffectslidedistance " : 30, "TEXTEFFECTDELAY": 500, "TEXTEFFECTSEPARATE": FALSE, "TEXTEFFECT1": "SLIDE", "TEXTEFFECTSLIDIRECTION1": "RIGHT", "TEXTEFFECTSLIDISTANCE1": 120, "TEXTEFFECTEASING1": "EASEOUTCUBIC", "TEXTEFFECTDURATION1": 600 , "TexTeffectDelay1": 1000, "TexTeffect2": "Slide", "TexTeffectSlidedIrection2": "Right", "TexTeffectSlidedistance2": 120, "TexTeffectEasing2": "EaseoutCubic", "TexTeffectDuration2": 600, "TexTeffectDelay2": 1500, " TextCSS ":" Display: Block; Padding: 12px; text-align: left; "," textbgcss ":" display: block; position: absolute; top: 0px; left: 0px; width: 100%; Height: 100% ; background-color: # 333333; Opacity: 0.6; Filter: A LPHA (opacity \u003d 60); "," titlecss ":" display: block; Position: relative; Font: Bold 14px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", Sans-Serif, Arial; Color: #FFF; "," DescriptionCSS ":" Display: Block; Position: relative; font: 12px \\ "Lucida Sans Unicode \\", \\ "Lucida Grande \\", Sans-Serif, Arial; Color: #FFF; margin-top: 8px; "," Buttoncss ":" Display: Block; Position: relative; margin-top: 8px; "," TexteffectResponsive ": True," TexTeffectResPonsize ": 640," TitleCsSResponsive ":" Font-Size: 12px; "," DescriptionCSSResponsive ":" Display: None! Important; "," ButtonCsSResponsive ": "", "addgoooglefonts": false, "googlefonts": "", "TextLeftRightPerCentForStatic": 40))

Optimization of values excel tablessatisfying certain criteriamay be a complex process. Fortunately, Microsoft offers add-in Solution of problemsfor numerical optimization. Although this service can not solve all problems, it can be useful as a tool what if.This post is dedicated to the add-on Solution of problemsin Excel.

Superstructure Solution of problemsavailable in all excel versions. Please note that screenshots may not match your version. Despite the fact that some functions can change their location depending on the superstructure version, the functionality remains almost unchanged.

What is the search for solutions

Searching of decisions - Excel add-in, which helps to find a solution by changing the values \u200b\u200bof target cells. The goal may be minimizing, maximizing or achieving some target value. The problem is solved by adjusting the input criteria or restrictions defined by the user.

Where in Excel search for solutions

Superstructure Searching of decisionscomes with Excel, but default is disabled. To enable it, go to the tab. Filegroup Parameters.In the dialog box that appears dialog Parameters,choose Add-in -\u003e Control: Add-inExcel -\u003e Go.In the window Superstructureinstall a tick opposite the field Search for solutions, zhmem. OK.

Now in the tab Dataa new group appeared Analysiswith button Search solutions.

An example of how to search for solutions

This post is based on the use example. . The file is compatible with all Excel versions.

Defining a problem

Suppose we have a data set consisting of 8 points, each of which corresponds to its value.

... And we need to combine the values \u200b\u200binto two groups so that the sums of the values \u200b\u200bof these groups approximately coincide.

First you want to define each item to some group.

To indicate the attachment of the paragraph to the group, we will marry them (1), otherwise zero (0).

In the next column, we will summarize the values \u200b\u200bof each item in the group, and then summarize at the end of the column.

We also need to handle the value of each item in each group, for this we multiply the value of the item to the value of the group corresponding to this item.

Finally, we need to reduce the amount of groups and work with the difference between them.

Our task minimize the difference between the sums of groups.

Now we can assign to each group items, for this manually put the units in columns C and D. Excel will display the difference in the sums of groups in the G11 cell.

The problem is that the number of possible combinations 2 8, i.e. 256 probable answers to the question. If you spend 5 seconds for each of them, it will take from us 21.3 minutes, assuming that we can withstand the pace and remember the best combination.

That's where Search for solutionsfinds application.

Search for the optimal solution in Excel

To apply the service Solution Solution We need to identify a number of requirements, rules and restrictions that will allow the superstructure to find the correct answer.

Our rules

Our basic requirement is to minimize the difference between the two groups. In our example, it is located in the G11 cell - group B minus group A. We need a value in the G11 cell as much as possible, but more than or equal to 0.

We also know that the item can be either in group A, or in group B, it cannot be fractional. Thus, we have two limitations for each element:

First: the value of the element in the column Outcomemust be equal to one.

Secondly: the values \u200b\u200bof elements in groups must be integer.

We also know that the total number of elements 8 is another restriction. How to use these restrictions we will discuss in the next section.

Solution Search dialog box

This section describes the add-in window. Search for solutionsand its use to determine the problem.

Empty Solution Say Window

Filled solution search window

Optimize target feature

This is a target cell in which we are trying to solve the problem. Our G11 target cell is the difference in groups.

Before

Here we indicate what results we want to achieve from the target function.

We want the amounts of both groups to coincide, i.e. In order for the amount of amounts to be 0. It may seem strange, but we do not need to minimize the difference, because all the elements will be placed in a group A, which will lead to the value of the G11 cell.

Another way to impose restrictions is to change G11 on \u003d ABS (G10-F10).At the same time, we can install a marker on Minimum,as a result of achieving the target function.

But for now we will focus on the formula \u003d G10-F10.and set the marker to value equal to 0.

Changing the cells of variables

Changeable cells - cells that superstructure will try to change to solve the task. In our case, this is a binding element to a specific group: $ C $ 2: $D $ 9.

In accordance with the restrictions

Restrictions are rules that limit possible solutions Problems.

We need to add several restrictions on our list:

1. In a collumn TOTALeach element should be equal to 1
2. Group elements must be an integer
3. The sum of the column values TOTALmust be 8.

To impose restrictions, click the button Add

You can Changeor Deleterestriction, if you made an error by selecting a specific restriction and pressing the appropriate buttons in the dialog box.

Download / save solutions search options

Solutions Search service allows you to save and upload the superstructures. To do this, in the window there is a button Download / Save.Parameters are saved in the range that you indicated earlier. This approach allows you to quickly configure and change parameters. Finding a solution.

Run search for optimal solution in Excel

A warning!!! Add-in Solution Search is a complex computing superstructure, so before starting, save the workbook.

Before starting the model, you need to set several more parameters to ensure that the service will work correctly. In the main dialog box, make sure that the marker is opposite the field Make variables without restrictions non-negative.In the same window, click Parameters.

Two parameters that will need to change from time to time:

Accuracy Restrictions:value from 0 to 1, where, the larger the figure, the greater the restriction

Integer optimality:shows how far from the integer restriction has the right to be.

Running model

To start the add-in click Find a solutionbasic window.

In the status bar, you will see a number of static data that will display the internal work of the superstructure. As a rule, they change rapidly, and it is difficult to read them. If the model is complicated, then work can stop for a while, the add-in is usually restored from these problems itself.

After Search for solutionsfinishes its work, Excel will display the dialog box Results search results With some information. The first thing to pay attention is the inscription The solution is found within the allowable deviation.If the solution is found, the working book cells will change with the proposed decision.

Now you have 4 options to choose from:

- restore the source values

Run report

You can create a report by selecting the reports available from the list. A new sheet will be created. Report on results1.

Please note that depending on the restrictions you have installed, various reports will be available.

Save script

Where you want to enter the name of your model script and click the button OK.

All scenarios are available in Script dispatcherwhich is in the tab Datain a group Work with data -\u003e Analysis What-if -\u003e Script Manager.

In documents Microsoft Excel.which consist of large number Fields often need to find certain data, the name of the string, etc. Very uncomfortable when you have to view great amount Rows to find the desired word or expression. Save time and nerves will help the built-in search Microsoft. Excel. Let's figure it out how it works, and how to use it.

The search function in Microsoft Excel offers the ability to find the desired text or numeric values \u200b\u200bthrough the "Find and Replace" window. In addition, the application has the ability to enhanced data search.

Method 1: Simple search

A simple data selection in the Excel program allows you to find all the cells that contain the character set in the search window (letters, numbers, words, etc.) without registering the register.

Method 2: Search at the specified interval of cells

If you have a rather large-scale table, then in this case it is not always convenient to search all over the sheet, because in search results There may be a huge number of results that are not needed in a particular case. There is a way to limit the search space only with a certain range of cells.

Method 3: Advanced Search

As mentioned above, with the usual search for issuing results, absolutely all cells containing a serial set of search symbols in any form are not dependent on the register.

In addition, not only the contents of a particular cell, but also the address of the element to which it refers can be released. For example, in the cell E2 contains a formula that is the sum of the cells A4 and C3. This amount is 10, and this number is displayed in the E2 cell. But, if we ask in the search for the number "4", then among the results of issuing there will be the same cell E2. How could this happen? Simply in the E2 cell, the formula contains an address to the A4 cell, which just includes the desired figure 4.

But, how to cut off such, and other knowingly unacceptable search results? It is for these purposes that there is an advanced search Excel.

1. After opening the window "Find and replace" In any way described in the way, click on the button "Parameters".



2. A whole range appears in the window additional tools To manage the search. By default, all these tools are in a state, as with normal search, but if necessary, you can adjust.

   

   Default function "Consider the register" and "Cells entirely" Disabled, but if we put the ticks near the relevant items, then in this case, when the result is generated, the entered register will be taken into account, and the exact coincidence. If you enter a word with a small letter, then in search issuance, cells containing the writing of this word with a capital letter, as it would be by default, will no longer fall. In addition, if the function is enabled "Cells entirely", Only elements containing accurate name will be added to the issuance. For example, if you specify search query "Nikolaev", then the cells containing the text "Nikolaev A. D." will not be added to the issuance.

   

   By default, the search is made only on active excel sheet. But if the parameter "Search" You will transfer to the position "In the book"The search will be made on all open files.

   

   In parameter "Browse" You can change the search direction. By default, as mentioned above, the search is carried out in order line. Rearming the switch to the position "By columns", You can set the procedure for the formation of the results of issuing, starting from the first column.

   

   In graf "Search area" Determined, among which specific items are searched. By default, these are formulas, that is, those data that when clicking on the cell is displayed in the formula string. It may be a word, a number or link to the cell. At the same time, the program, performing the search, sees only the reference, and not the result. This effect was discussed above. In order to search precisely on the results, the data that is displayed in the cell is displayed, and not in the formula string, you need to rearrange the switch from the position "Formulas" To position "Values". In addition, there is a possibility of searching for notes. In this case, the switch is rearranged to the position "Notes".

   

   Even more accurately, the search can be set by clicking on the button. "Format".

   

   This opens the cell format window. Here you can set the format of the cells to participate in the search. You can set restrictions on a numerical format, alignment, font, border, fill and protection, one of these parameters, or combining them together.

   

   If you want to use a specific cell format, then at the bottom of the window, click on the button. "Use the format of this cell ...".

   

   After that, a tool appears in the form of a pipette. With it, you can select that cell that you are going to use the format.

   

   After the search format is configured, click on the button Ok.

   

   There are cases when you need to search not for specific phrases, but find cells in which are located search words In any order, even if other words and symbols are separated. Then these words should be allocated on both sides the "*" sign. Now in the search results will be displayed all cells in which these words are located in any order.



3. As soon as the search settings are installed, click on the button "Find all" or "Find further"To go to search results.

As you can see excel program It is quite simple, but at the same time a very functional search for search tools. In order to produce the simplest squeak, it is enough to call a search box, enter a query in it, and click on the button. But, at the same time, it is possible to configure individual search from large quantity Different parameters and additional settings.