Cara membuat pencarian di excel (how to make a search in excel) Tulisan sederhana ini saya buat hanya sekedar untuk sharing pengetahuan, terutama bagi temen temen pengemar Mr. Kali ini saya mau buat contoh pembuatan kolom pencarian di excel.
This example instructs you how to develop your very ownsearch packageinExcel. lf you are usually in a hurry, simply download the Excel document.
This is usually what the spreadsheet looks like. If you get into a search concern into cell W2, Excel queries through line At the and the outcomes show up in column B.
To create this search package, carry out the subsequent actions.
1. Select cell N4 and put the Lookup function proven below. Create an complete reference to mobile B2.
2. Double click on the correct part of mobile Deb4 to quickly copy the functionality to the other tissue.
Explanation: the SEARCH function discovers the placement of á substring in á chain. The Lookup function is definitely case-insensitive. For Tunisia, line 'uni' can be discovered at place 2. For United Says, string 'uni' will be discovered at position 1. The lower the number, the increased it should end up being rated.
3. Both United Expresses and United Empire return the value 1. To return unique ideals, which will assist us when we use the Position functionality in a instant, slightly alter the formulation in mobile Deb4 as proven below.
4. Again, double click on the correct corner of cell M4 to quickly copy the formulation to the additional tissues.
Description: the Line function comes back the row number of a mobile. If we divide the row number by a large number and include it to the outcome of the Research functionality, we constantly have exclusive values. However, these small increments gained't influence the search search positions. United Claims offers a value of 1.00006 right now and United Empire offers a worth of 1.00009. We also added the IFERROR functionality. If a cell includes an error (when a string cannot be found), an clean string (') is shown.
5. Select cell D4 and put the Position function proven below.
6. Double click on the correct part of cell D4 to quickly copy the formulation to the some other cells.
Description: the Standing function results the rank of a number in a list of amounts. If the third argument can be 1, Excel rates the smallest amount first, second smallest number second, etc. Because we included the Line functionality, all ideals in column D are usually unique. As a outcome, the ranks in column C are unique too (no connections).
7. We are almost now there. We'll make use of the VLOOKUP function to return the nations discovered (lowest rank first, second lowest rank second, etc.) Select cell B4 and insert the VLOOKUP function demonstrated below.
8. Increase click the right corner of cell T4 to rapidly duplicate the method to the various other cells.
9. Switch the text color of the amounts in line A to white and hide columns C and Deb.
Outcome. Your own search package in Excel.
This example educates you how to make your very ownsearch containerin
Excel. lf you are in a rush, simply down load the Excel file.
This is usually what the spreadsheet appears like. If you enter a search query into cell M2, Excel queries through line At the and the outcomes show up in line B.
To develop this search box, implement the subsequent methods.
1. Choose cell Deb4 and put the Research function demonstrated below. Create an complete benchmark to mobile B2.
2. Two times click the right part of cell D4 to quickly duplicate the functionality to the some other tissue.
Explanation: the SEARCH function discovers the placement of á substring in á thread. The SEARCH function is certainly case-insensitive. For Tunisia, thread 'uni' can be found at position 2. For United Claims, chain 'uni' is usually discovered at placement 1. The lower the quantity, the higher it should be positioned.
3. Both United Says and United Kingdom return the worth 1. To return unique beliefs, which will assist us when we make use of the Position function in a minute, slightly change the formulation in mobile N4 as proven below.
4. Once again, double click the right part of mobile G4 to rapidly duplicate the method to the additional cells.
Explanation: the Line function returns the row number of a mobile. If we divide the row amount by a large quantity and add it to the result of the Lookup function, we generally have exclusive values. Nevertheless, these small increments received't influence the search search rankings. United Claims has a value of 1.00006 now and United Kingdom has a worth of 1.00009. We furthermore included the IFERROR functionality. If a mobile contains an mistake (when a line cannot end up being discovered), an unfilled string (') is shown.
5. Choose cell G4 and insert the Position function shown below.
6. Two times click on the correct part of cell M4 to rapidly duplicate the method to the various other cells.
Description: the RANK function comes back the position of a amount in a checklist of numbers. If the third argument is certainly 1, Excel ranks the smallest quantity first, 2nd smallest number second, etc. Because we included the Line function, all ideals in line D are distinctive. As a outcome, the rates in column C are unique as well (no ties).
7. We are usually almost presently there. We'll use the VLOOKUP functionality to return the countries found (lowest rank very first, second most affordable rank 2nd, etc.) Select mobile N4 and place the VLOOKUP function shown below.
8. Two times click the right part of cell T4 to quickly duplicate the formulation to the other tissue.
9. Switch the text colour of the figures in column A to white and hide columns G and M.
Outcome. Your personal search container in Excel.