엑셀 동적차트 만들기, 초보자용 기초부터 INDEX 함수응용 고급까지! | 동적차트 총정리 | 엑셀고급 2강 — Transcript

Full transcript

1. 0:00You can make the automaion chart through this lecture.
2. 0:04Once the data is updated in this way, even though it is a merged cell, the chart will be updated automatically
3. 0:27Hello everyone. This is Oppadu Excel.
4. 0:29In the previous lecture, I briefly discussed some ways to apply the dynamic range and dynamic range using the OFFSET function.
5. 0:38In this lecture, we will briefly describe how to create graphs and charts that are updated automatically with the dynamic range using the INDEX function.
6. 0:49Since we will create a dynamic range using the INDEX function, first of all, we will look at the basics of the INDEX function.
7. 0:58Next, we will look at the dynamic range using the INDEX function, and finally we will create a chart that will be automatically updated based on the dynamic range using the INDEX function by step by step.
8. 1:14Note that not only the dynamic range through the INDEX function learned today, but also the dynamic range using the OFFSET function discussed in the previous lecture can be applied as well.
9. 1:30The INDEX function is quite similar to the OFFSET function we learned in the previous lesson.
10. 1:37The INDEX function contains three arguments.
11. 1:41The first is the reference range, the second is the row number, and the third is the column number.
12. 1:48The column number is an optional argument and is not compulsory.
13. 1:54There are actually two types of INDEX functions. Of the two types, we are going to use the INDEX function as the 'reference type' when applied to dynamic range. In practice, 'array type' is often used.
14. 2:06If you are wondering about this content, please refer to the MS website for the link below.
15. 2:18So, I mentioned that the INDEX function is quite similar to OFFSET, but there is one thing to note.
16. 2:27In the case of the OFFSET function, if you enter 1 as a line shift, the target cell moves directly down one column, whereas for the INDEX function, if you enter 1 as a line number, it refers to the value in the first row.
17. 2:54Therefore, please note that the numbers starting with OFFSET are different.
18. 3:04Line numbers and column numbers start at 1. If you enter 0 in row number and column number, it will refer to whole row and whole column.
19. 3:17This is the first example. Through the INDEX function, the value of [C1] cell in the first row and the third column in the range of A1 and D4 is retrieved.
20. 3:49So, the result of the function is 3.
21. 3:54This is the second example. Use the INDEX function to call the value of the cell where the third row and third column meet, so the result will be 11.
22. 4:13This is the third example. Through the INDEX function, the values ​​in the fourth row and the fifth column in the same range up to cells A1 and D4 are retrieved.
23. 4:25Now, you may see the fourth row, but the fifth column is column E, it is out of range, so this function will print '#REF' error.
24. 4:51Let's look at a second example. This time, I will see a case where we enter 0 to refer to the entire row and the entire column.
25. 4:59This is the first example. In the same range, we will refer to the entire column C.
26. 5:20So the result is a whole cell range of 3,7,11,15, which is the entire C column.
27. 5:31The INDEX function is applicable as the SUM function. For example, in the range we just called which are 3,7,11,15 will be entered in the SUM function.
28. 5:48Then, this SUM function calculates the sum of the ranges called by INDEX. The result is 3 + 7 + 11 + 15.
29. 6:06On the same principle, we will refer to the first row through the INDEX function and to the INDEX function that refers to the entire row range without moving to the column.
30. 6:21As a result, the entire range of first row is output, and the result of 1 + 2 + 3 + 4 is output through the SUM function.
31. 6:40The last example. Likewise, the selected range refers to the entire row and the entire column, and the sum of all the numbers in the selected range is output as the result.
32. 7:11Let's look at dynamic range using INDEX function in earnest.
33. 7:17As you all know, if you type [A1], you refer to cell A1, and if you type [B3], it refers to cell B3.
34. 7:35What if you want to fetch multiple cell ranges instead of referring to each cell?
35. 7:43In Excel, it specify the range through the colon (:), if [A1: B3] is input, the range from A1 to B3 is output.
36. 7:59So, for the dynamic range using the INDEX function, the dynamic range is applied by designating the end point by the output from INDEX function.
37. 8:23I have an employee list table. Exclude the heading from the employee list, and specify the starting point as cell A2.
38. 8:37From cell A2, we will specify the last cell. This last cell is called via the INDEX function.
39. 8:48Through the INDEX function, the line number from the entire range from A to C is retrieved. The row number counts the number of non-empty cells in the entire A column.
40. 9:13So the number of non-empty cells through the COUNT function is 10.
41. 9:25And the value to be referred to by the column is called 3.
42. 9:30Then, in the entire range of column A through column C, as we just calculated, the number of cells except empty cell in column A is 10, so we refer to row 10. after that the third column will be fetched.
43. 9:50So the result will refer to cell C10.
44. 9:56So when this INDEX function is entered, it refers to the range up to [A2: C10].
45. 10:20On the same principle, suppose that new data is added to the table.
46. 10:34In the range from column A to column C, the value of 11 is output when the number of data in column A is calculated by COUNTA function.
47. 10:51Therefore, we refer to the 11th row and refer to the 3rd column. That is, [C11] is output to the INDEX function and expanded to a range from A2 cell, like [A2: C11] as the result.
48. 11:16Next, let's create a dynamic range that can reference all the data, assuming that there is only one table in a sheet with dynamic range using the INDEX function.
49. 11:32This is equally applicable to the dynamic range using the OFFSET function learned in the previous lesson.
50. 11:38So, based on cell A1, it refers to all the ranges from line 1 to line 1048576, that is, the maximum line provided by version 2003 or later.
51. 12:09Counts the number of data in column A through the COUNTA function. And the COUNTA function counts the number of data in one line.
52. 12:27The number of data in column A is 9, and the number of data in the first row is 7.
53. 12:40In the range, it refers to the 9th row and the 7th column, and [G9] is output as the result.
54. 12:50Likewise, as the data grows, the position of the last cell will be moved by INDEX Function.
55. 13:14Let's create graphs and charts that will be updated automatically based on the actual Excel file.
56. 13:20If the content is helpful as you watching, please subscribe and comment below. [Thanks a lot!]
57. 13:27When you come to the sample file, I put two sheets. The first is [Automatic Update Chart - Dynamic Range]. The second sheet is [Auto Update Chart - Table].
58. 13:40The reason I split it into two sheets is that there is a very simple way to create the dynamic charts or graphs which is much easier than they way using Dynamic Range.
59. 13:48But there are some limitations, so I split it into two sheets to show the difference between two situation.
60. 13:56Go to the Automatic Update Chart - Table sheet.. After clicking on any data, press [CTRL + T] to create the table.
61. 14:17After switching to a table, insert the graph from [Insert] - [Reference Chart] in the table.
62. 14:27Since the graph is now linked to the table that is entered, the chart is automatically updated each time the table is updated.
63. 14:39Excel's built-in table function has already including the dynamic range function, so when data is added in this way, the chart is updated automatically. (Very simple!)
64. 15:01However, as noted in the previous lecture, the table function is only treated as a single table of consecutive columns and consecutive rows and also has the most fatal drawbacks THAT....
65. 15:16There are many restrictions on using the table function when using in practice, such as cell merging function.
66. 15:30Move to the [Dynamic Range] sheet and press [CTRL + T] to create the table.
67. 15:41In this way, you can see the problem of unmerging as the existing cells are detached.
68. 15:47So, basically I recommend you to use the table function, but if you have a lot of cell merging and you can not change the format of the document, you can create a chart that will automatically updated by the dynamic range you learned in this lesson.
69. 16:15Let's creates a dynamic range. Here is one thing to keep in mind when creating dynamic ranges with cell merged cells.
70. 16:27If you click on cell D2, [D2: E2] is clicked on the actual clicked cell, but the cell on which the actual data is input is cell [D2].
71. 16:43If you click on cell B5, the range from B5 to C5 will be clicked, but the cell where actual data is input is cell [B5].
72. 16:59On the same principle, even if you select D1 to E9, the actual data input range is [D1: D9].
73. 17:23So, press [CTRL + F3] to bring up the name manager and create three dynamic ranges.
74. 17:35The first dynamic range is the [rngDate]. The [rngDate] refers to the date column.
75. 17:43If you click on cell A1 and type a colon(":"), the range of A1:A1 is set automatically. After deleting the range shown afterwards, you can use the INDEX function to specify the last cell.
76. 18:17If you click on the reference target with a dynamic range called [rngDate], you can see that the range has been set.
77. 18:24Likewise, when new data is added, you can see that the range is expanded.
78. 18:38Second, we are going to create [rngSales] as a dynamic range that refers to sales.
79. 18:52For reference, the starting cell is cell B1. When you click a merged cell, the range is automatically assigned to [B1:C1]. However, since the data we want is in cell B1 only, the C1 cell range shown after the column needs to be deleted.
80. 19:15After entering the colon, use the INDEX function to set the last cell.
81. 19:36Since the range in which actual data is included is column B, please change it to refer to column B only.
82. 19:46Similarly, the COUNTA function changes the number of data in column B.
83. 20:02Finally, let's make the [rngSalesNumber] dynamic range.
84. 20:10This time, I will make it through the OFFSET function, not the INDEX function.
85. 20:21Deletes the cells set to the range after the column(":").
86. 20:32Since you only have to refer to column D, the range of column E need to be modified.
87. 20:44For dynamic range using OFFSET, please refer to the previous lesson.
88. 21:01Click anywhere on the table and insert the chart by clicking [Insert] - [Reference Chart].
89. 21:14Change the title of the chart to "Daily Sales".
90. 21:34Right-click on the chart and select [Change Chart Type] from [Combo] and check the secondary axis to print number of sales on another side of chart.
91. 22:09The chart you entered now is not updated automatically even if new data is added because the range is not linked to the table range but linked to the normal range.
92. 22:24So I will change the data range of the table to the dynamic range just entered.
93. 22:28Right-click on the chart and click [Select Data].
94. 23:02In fact, the range of values ​​in the chart is from the 2nd row.
95. 23:19Press CTRL + F3 to change the starting point of the dynamic range to D2 instead of D1.
96. 23:36Similarly, for sales, change the date so that it starts at B2, not B1, also the rngDate is also starts at A2 instead of A1.
97. 24:13Removes empty data series.
98. 24:23The series value will be set to 'rngSales' just entered as dynamic range.
99. 24:44Click [Edit] to set the number of sales and the date to each dynamic range.
100. 25:36In the next lesson, we will see how we can handle the error due to the empty cells in the dynamic range and show you the solution to handle it.