Hello everyone. This is Oppadu Excel. The first lecture is about the dynamic range. Let me just take a look at what the dynamic range. Please download the sample file from the homepage, If you look at the example file, there is a sample sheets. Two tables are prepared in the example sheet. Please go to the 'formulas' in Excel, click on the name manager of the formula If you press the hotkey Ctrl + F3, you have entered two custom ranges as you can see. The range RangeFuit1 is specified as a general range from A3 to A9 in the example sheet. In the case of RangeFruit2, we set the dynamic range using the OFFSET function. If there is no change in the range of the data, the two ranges point to the same range I will add a new fruit in the range. For example, suppose you sell Guava at 5,000 won and enter data. Press Ctrl + F3 again to enter the name manager again. If you click on RangeFruit1, the newly added data is not reflected in the range. In the case of the dynamic range, the newly added data is automatically reflected and recognized in the range. In this lecture, I will briefly describe what the dynamic range is and how to apply it, and I will briefly explain how to apply it to a practical application. Are you ready? Let's go together! In this tutorial, we will look at the reason why need the dynamic range. There are three ways to apply the dynamic range, I'll go through each of the three methods, and then I'll go over each pros and cons. And we will examine the dynamic range using the OFFSET function, which is one of the three methods of applying the dynamic range. So let's look at the basics of the OFFSET function and the basics of the COUNTA function. Then, based on these two functions, we will examine the dynamic range using OFFSET, and then we will see how to apply it in Excel. First, let me show you what the dynamic range is and why we need it. The dynamic range is the auto-extended range that newly added data is automatically reflected. Typically, you can use it with VLOOKUP or a range of lists with data validation to automatically update through this dynamic ranges. Typical examples include personnel management (where new employees are added daily), revenue management (when data is added on a daily basis), and inventory management (where new inventory registration numbers are added each time). When you register a dynamic range as a data range, you can register it in the VLOOKUP or data validation list and use it to update automatically without having to modify it every time. There are three main ways to apply dynamic range. The first is the dynamic range using the OFFSET function to look at in this lesson. The dynamic range using OFFET is universally used, but the OFFSET function itself takes up a large amount of memory and is suitable for small amounts of data usage. The second function is dynamic range using INDEX. The dynamic range with INDEX is suitable for large data use. Finally, dynamic range using table functions. In the case of the table function, since it uses the function built in Excel, it is not only fully possible to use it for the beginners of Excel, but also various additional functions can be used. There are advantages and disadvantages. I compared the dynamic range using the OFFSET function and the INDEX function, and the dynamic range using the table function. The dynamic range using the OFFSET function and the INDEX function has the advantage that the raw data can be edited more flexibly than the dynamic range using the table function. However, since it requires some basic knowledge of the function, it may be somewhat difficult for beginners. Second, there is the disadvantage that if you deal with a large amount of data, the editing speed can be slowed down. On the other hand, the dynamic range using the table function is considered to be the same table in continuous columns or continuous rows due to the nature of table function, Therefore, it has a disadvantage that it can be used within a limited range. It would be difficult to apply the dynamic range to table functions in non-normalized data on the same principle. In this tutorial, we will look at the dynamic range using the OFFSET function. Let's briefly review the OFFSET function. The OFSFET function itself means "offset" or "tradeoff". For example, when there is a start point, it is a function that fetches a position in a row by a certain number and a position in a column by a certain number as well. Let's look at a simple example. The OFFSET function moves a cell from A1 to a row, and recalls a cell where two columns are moved, that is, the value of cell C2. The second example similarly uses the OFFSET function to move down three cells from cell A1, and move four columns. Specify E4 cell as the result value, and then specify up-and-down width and left-to-right width as additional arguments. Height is 2 cells and width is 2 cells as well. Therefore, based on the E4 cell, the range from E4 cell to F5 will be the result of this function which is the range with two cells vertically and two cells horizontally widened. Next, let's look at the COUNTA function. The COUNTA function is a function that, when there is a certain range, finds the number in the range, excluding spaces. The first example uses COUNTA to count the number of blanks. The range is from A1 to A12. The number of cells except blank between A1 to A12 is 10. So the result of the first example is 10. The COUNTA function counts the total number of characters in column A excluding the blank spaces. Assuming there is no data under A10 cell, 10 is output as the result. In the third example, the number of cells is 7, except for the blanks from B1 to B7. In the fourth example, the total number of cells in column B excluding blank spaces is 9 in total. Through the COUNTA function, this time counts the number of data in the entire 1 row. The output value is 3. The number of data excluding the blank in the whole 7 rows is 3. Let's set the dynamic range using the OFFSET and COUNTA functions. There is an OFFSET function and you enter the start point in the OFFSET function, then put three 'comma'. And you can specify the upper and lower range and the left and right range through the COUNTA function. Let's take a look at the arguments of the OFFSET function we saw earlier. If you look at the OFFSET function, there are a total of five arguments. First enter the starting point. Based on the starting point, you can expand the vertical width and horizontal width through COUNTA function. Now, enter the starting point to specify the dynamic range using the OFFSET function. And as putting three 'comma', no row and columns will be moved. You can use the COUNTA function to widen the vertical and horizontal ranges. For example, fix the starting point to cell A2. Then, enter the number of data in column A through COUNTA function, and then count the number of data in the entire 1 row. If you do so, you can use the OFFSET function to widen the vertical range based on the starting point, and expand the left and right range as well. I set the A2 cell to the starting point through the OFFSET function. Then enter 3 comma. Next, the COUNTA function counts the number of data in the range from A2 cell to A100 cell. Vertical range will be input as 9. Similarly, the COUNTA function counts the number of data in the entire first line. 3 is entered as horizontal range. Likewise, the second specifies the same range. However, there is a difference from the first function. The first function counts the number of data in the specified range up to the cell A2 and the cell A100, whereas the second function counts the number of data in the whole column A. However, since the starting point is based on A2 cell, it subtracts 1. The reason for this is that the total number of data in column A contains the headers. However, because we will expand the range based on A2 cell, we specify 1 by subtracting it. As a result, the scope of the two functions is the same, but the second function allows more flexibility to handle large amounts of data. The second function, however, has the disadvantage of increasing memory usage. Therefore, you can use them according to your own standards. Now, let's look at how to apply dynamic range using OFFSET function through practice. You can download sample files and E-book from your 'OPPADU' homepage free of charge. And if the video was helpful, please subscribe my channel and like and comment below. If you look at the example file, you may see three sheets. The first is the data validation list, the second is the SUM function, and the third is the VLOOKUP function. Let's look at how to apply dynamic range based on these three sheets. Go to the first sheet and click cell C2. We have set up a list of departments. And as you see your department appears as drop-down lists. To check, click cell C2 and click [Data] - [Data Validation]. The target is a list, and the range is from A2 cell to A8 cell. The problem here is that if you add a new department, it is not automatically added to the data validity list. Dynamic range is used to solve this problem. Hit the CTRL + F3 key, or click the Name Manager tab in the formula to bring up the name manager. Click 'Add New' here to register a new custom scope with the name of 'rng department'. The reference target registers the dynamic range via the OFFSET function. Click cell F2 as the reference cell and enter 3 comma. Use the COUNTA function to specify the vertical range. Click F to select the entire F column. subtract 1 so we counts the number of actual data that does not contain the header. the dynamic range is registered. If you click here, you can see that the range entered now is registered. For example, after adding a new department, press CTRL + F3 to check the scope by clicking on the 'rng department' in the name manager. You can see that the range is automatically expanded in this way. This range is included in the list of data validation. In the sheet, click cell H2, then [Data] - [Data Validation List], and select [List] for the restriction list. Then click on the original and press the keyboard [F3] key to display the custom range you just registered in the name manager. Select ''rng department' and press OK. When you add a new department, the scope is automatically registered as you can see. If you delete the data, you can see that the range is reduced automatically. Let's apply the SUM function with the dynamic range. the sum of cells from A2 to A5 is calculated in Cell C2. The problem here is that when you add data, the range is limited to A2 to A5 cells, so the sum will not be automatically calculated. Again, we will solve this problem through the dynamic range. Press CTRL + F3 to bring up the Name Manager. 'Click New' to register the flow range with the number of 'rng sales'. This time, enter the COUNTA function assuming that the maximum data is registered from G2 cell to G100 cell without selecting the entire G column. In other words, you do not need to type '-1' because it counts the number of cells that do not contain the header. If you click here, you can see that the range is registered. Click cell I2 and enter SUM function. Then select the number of 'rng sales' to get the sum in the range. Then, when the data is added below the G5 cell, the sum is automatically increased, and when the data is deleted, the sum automatically decreases. Finally, apply the VLOOKUP function with dynamic range. Go to the VLOOKUP function sheet and click cell E3. We have registered the range referenced in the VLOOKUP function from cell A2 to cell C6. If you add new data here, you will get an 'N / A' error when you write the ID of the newly added employee. To solve this problem, we change the range argument of the VLOOKUP functions to dynamic range. Press CTRL + F3 to add a custom range from the Name Manager 'rngEmployee' Use the OFFSET function to select cell J2 as the reference cell. After entering the comma 3 times, COUNTA selects the entire J column for the vertical ranges. Since the number contains the header, adjust the vertical width to -1. This time, the horizontal wdith is increased by counting the number of data from J1 to M1 with COUNTA function. 'rng Employee list' is registered, you can see that the scope is registered when you click reference object. Unlike the previous one, the range registered this time is not only the upper and lower width but also the left and right widths. Apply this range with VLOOKUP. Click cell N3, then enter VLOOKUP. The value to find is N2 cells. Select the reference scope 'rng Employee List'. The number of the column to find is '2' to find the employee name in the second column and searches for the exact value. Even when new data is added, it can be applied through the VLOOKUP function and automatically expanding the dynamic range. In the next lesson, we will look at the dynamic range through the INDEX function. If this lecture is helpful, I would like to ask you to subscribe my channel and like and comment below. See you next time!
