Svetlana Demaretskaya

Saved 1/7/18 10:49 AM to LESSONS EXCEL

Excel 16 trick. VPR for a set of values In a lesson are considered two ways of creation of the list from all values corresponding to the specified criterion (multiple VPR). The first way – with the help kombinats...  See details »

 
Video subtitles:
  • 00:03: welcome CDF probably one of most discussed functions to excel in First, this formula extremely useful and Second bit whimsical all you need for heated debate there are many variations of it use and different alternatives for solving related problems to the first peer in its pure as searches columns to the right of criterion but look it is also possible and the left if you use combination of functions their positions and Yandex
  • 00:31: but another task that can arise in connection with pulling data on the key field there are criteria CDF find and pull only one value which comes first while watching a column top if the lower column also meets criteria CDF is simply not see and respectively second third value will be ignored by logical because data pulled out one cell to another however, sometimes the task of is precisely is to find
  • 01:00: withdraw all matching value some advance allotted column consider this the situation there received list goods in front each Set appropriate accompanying document say invoice need for product name withdraw all related to him invoices that is to define all deliveries he this product He presents himself product is natural will search criterion we shall take But in this location yet
  • 01:31: time on your toes here have the goods apples you need to bring it here all column invoices which was the product that is here this invoice here and this Now that you have more time To make sure the CDF chemically pure not here a suitable criteria for belt table anchoring column number say 2 and accurate
  • 02:02: coincidence it is clear that the first value was found well here it is and here Everything else is just We also repeated and will not help function combination search positions and Yandex array sought an invoice me fasten the further we indicate the number lines to be found using the Search position looking
  • 02:30: apples anchoring since formula will be be pulled down; peruse an array is a column where are our apples more precisely a column where we We are looking for apples. point exact match half brace and we We get the same result in short, both of these option allows find the first If a match below from the other, they will be and so ignored you want to list supply our case
  • 03:00: invoices that corresponds to selected criteria ie product the basic idea no matter how strangely taken from features a combination of from the position and Yandex Only instead of one specific number position to find All rooms and line paste them Yandex formula which in turn turn will give us adequate the value of the invoice index of function specified number the cell returns contents of this cells in said and the main column problem just is the list these numbers so the formula
  • 03:30: start writing from the mid to first find serial numbers cell which coincide with criteria to look for Now it will be value here in this column mother and paste numbers Yandex function we get final result I will show two ways to this task consider all step by step first thing we We need to get list sequence rooms for apples here This column for that we we use the following function if that's the
  • 04:03: cell anchoring Here are the criterion anchoring let us axxel will give number appropriate line as well do absolute link that the formula can be It was to reach down let piecemeal analyze how It will work well we first need to only get numbers of those lines which coincide with criteria, and if we
  • 04:30: impose any restrictions that result of this formula will be simple list sequence numbers for corresponding range we need Naturally, not all but only those numbers which coincide with criteria so we this vector serial numbers impose logical limit is take it from here expression looks but in the following manner excel view all cell and compare them with our criteria if the cell is not same here 1 case of cucumbers not chayka coincides with apples
  • 05:00: to excel issue lies 2 Gull potatoes also It does not coincide with the apples excel value is formed Box 3 lies apples coincides excel give truth and so check all cell where we apples we obtain from where hay is not apples We get lies and such we get vector logic the values of this vector booleans we put on vector sequence rooms comparison It made for all relevant rooms so there
  • 05:31: where the values lie serial number will also be It transformed into a lie Tandy logical the true value serial number It is saved in result here this whole the combination will give us the following result lies lies here It was the truth and now stayed sequence Room also for all the remaining matches however, we need not line number within total sheet excel and the cell number in within a specified band
  • 06:00: so here we vnesom small adding from each line number we subtract line number the first cell this range and add another unit It could of course just take a room cell that is but on the one above then if table will reside not with and the second cell 1 formula will generate an error that is so we will make the formula more versatile Now, regardless of of which will arranged table
  • 06:31: This initial data construction will to give correct the result of that is, serial number cells within specified range let's recheck So for the first two cells which do not have our criterion we get lies below the apple is the third top cell then fifth and 9 and so we got list sequence rooms but here yet contains extra elements lies from them should be do to get rid
  • 07:01: it follows use function and excel at less with the help of specified array extracted the lowest value specified order if the smallest value of the order of 1 it will just minimum value if the smallest value of the order of 2 it will be the next for a minimum value and so on write here least look at tips here all We began to understand indicated array Array is the
  • 07:30: we just We got on; and Specifies the order here If we now give some specific number we respectively extract the smallest this value here but about us interested none and the number of their whole sequence that is February 1 3 and so forth and this we need That's about the same design Topics take number line and take away all that is above so the row number This cell can be not specify any
  • 08:00: argument for this where checks written formula and subtract hence the line number for the first cell this range we fix it, and add yedinichku to once again not to go of range where it is indicated So the formula for Specifically this cell we have indicated all arguments and here there is an array as the Set specific Now this number formula can be inducing already in the cell but as you
  • 08:30: probably noticed calculation process excel not formed separate intermediate the value of a whole arrays excel and Baraba Now the entire array at once in one formula so this formula It called formula arrays and for his I need push it any keys enter a control shift enter So we got a room 3 for the first cell coincidence if we forget conduct this formula as Formula
  • 09:00: array then there is nothing we We not gain entry into the editing formula and press simply enter you can see 0 is turned one of the common mistakes so it's worth remember when We are working with formulas array, and so once again enter the mode of edit Formula and enter stretch formula down and if we all done correctly it Now we will see here is a list serial numbers for birds which Apples contain all correctly May 9 chayka well
  • 09:30: well almost ready Now these numbers can be inserted into the function Yandex Yandex specify the array which is the desired value invoices anchoring. point and the following options: is the line number and line number we just found us It remains only close the outer bracket, do not forget pro array formula so we got
  • 10:00: list of all invoices small shtrishok order in order to get rid of error for that we use the function if an error if that this expression gives mistake then let us nothing is shown cabbage and introducing like formula Array copy and stretching can be the usual way All the problem is solved change here apples for example on a potato and obtain appropriate scroll Invoices
  • 10:30: which came potatoes in general work well there also a second way solve this problem the condition is the same, and here we are again we use the function but the search Yandex cells for relevant criteria we carry on another all at once to consider first practice on the same principle with using functions we will form a line list sequence numbers for matching criteria again
  • 11:06: subtract line number for the first checks and add one so we still have need braces while we pleased all the cells and Now these serial numbers we need to take away only those in which value coincide with criteria ie with apples
  • 11:31: therefore divide this array by the vector booleans we again compare This column criteria here we are again taught vector booleans what would happen maybe he does not know Boolean value lies and truth are in excel number equivalent to zero and a unit that is a lie is 0 and this wall
  • 12:00: unit remember formula CDF is the latest setting where you need to specify the exact search or interval we view specify feet and either yedinichku just You can specify a lie any truth in reference by the way, and so it is written that the last parameter to be false or true but instead of the word lie much faster but just press the toe so instead of the truth much faster press yedinichku so it is usually well Now we are doing here and we use this property and so each number will divided either 0
  • 12:31: lies either on the unit Zero can not be divided so the result this division will but on error yedinichku can share so it number cell whose there match will divided by one and respectively leave your the original the value of combining these two vectors we we obtain the following secretaire tour does not coincide with the lumpy were divided zero respectively Get the error and the criteria are
  • 13:01: in line with the specified They were divided into unit and respectively order number preserved so we We have 3 5 and element 9 that it remains just here to pick up in the previous formula we did it with function the least even taken very small rooms order to increase their 1 2 3, and so on here do we have an array in which there These are the mistakes and not the least function work but This function can be also cause differently for this
  • 13:31: use universal functions which is called If the unit does not it is wrong appeared in excel 2010 the unit is not a separate function and a list of features that is, in itself the unit is not anything He considers works selected function which is located inside it made all necessary Case of the first them is function which will directly calculate and here we see there is a whole list of average value score marginally maximally we work interested least that is
  • 14:01: function number 15 selected. point and now is the interested in the following argument we specify how to excel should come with Hidden Cells and also all that there is an error we are interested in the option number 6 skip erroneous that is, those values erroneous values that appeared in by dividing by 0 should be ignored so choose number 6 next
  • 14:32: argument is an array in which is interested his appointment has formed and Recently, it kind has a number the lowest value here we do as well as in the previous version just specify current line number subtract the number 1 ki and add yedinichku remains right close bracket and lead this
  • 15:02: formula and now the most interesting as We see here function also deals with arrays but all unit functions that start at the number 14 them to the end that is most the smallest to last by default treated as arrays ie additionally indicate that Axel this array formula not required All program itself understand why a entered by pressing enter key Now we have 3D
  • 15:30: number that is in the cell, we will refund invoice for first match which is located in third from the top of the cell more September 5 and errors but that almost all willing to stay add function Index invoice cleaning here line number here it is closing parenthesis and as soon as possible prescribe if an error if the function outputs
  • 16:00: mistake that returns void everything is fine It works here We summarize the whole both methods are equally good and as you can see are functioning however, the function unit allows avoid the use Formula array that good impact on when the speed of a large volume of data as it is known formula solid work slower than usual form small
  • 16:31: tables is not noticeable but if your table consists of several thousand lines it will affect speed so both formula working properly but If you are working with large amounts of data that can be you should pay It is the second option where we used function set for today all if you I enjoyed the tutorial share it with friends in social networks Click also on Like and subscribe to channel all goods see you soon meetings