Finally, 2018 is not much left. It was a year when there were so many things. As the day goes by, this ... it looks like the forehead is getting wider. I realize that day is going into day. I am firmly committed to my intention to raise Excel lecture more hard. First of all, the sample files used for lectures can be downloaded free of charge from the two homepages. The first one is the example sheet, the second one is the perfect sheet. From the example sheet, select from column F to column Z. After right clicking, I will show hidden cells to hide. After choosing a scope that is not used in this lecture, I will hide it with a right click. Let's see if you can add labels. The LEN function distinguishes the case where the manufacturer's character is more than 5 characters. Because the printer can output up to 5 letters, it distinguishes whether or not to add a label, assuming that additional labels need to be printed. The number of characters is output for each selected cell. Next, through the IF function, if the result of the LEN function that you just entered is greater than 5, you need to output additional labels. If this is the case, output "O"; otherwise, output "X". Because "O" and "X" are text, they must be enclosed in double quotation marks ("). If the mouse cursor changes to a black cross, fill it with auto fill. Here you go! Auto fill can also be entered by double clicking. I will cut 4 digits from the 6th digit of the unique number through the MID function in the unique number. Then, instead of dragging, move the mouse to the right and down to make a black cross, then double click on it. This way, AutoFill automatically fills up to the last cell. There are two things to note here. If there is a function in the middle, if you double-click to fill it, it will fill only until the cell where the function is located. Please note that the function does not auto fill after the part. If this is the case, fill it once with autofill, then double-click it again to fill it with autofill. You can not use double-click autofill when there are spaces on both sides of the part you want to autofill. There is no response. If so, fill the cell directly with an autofill, or remove the right or left area and fill it with autofill. Then add the column again. Please refer to this method. The label information assumes 5 letters of the company name and 4 digits of the company's unique number. There are two ways. First, let's enter it as an IF function. If the name of the manufacturer is more than 5 characters, use the LEFT function to cut off 5 characters from the manufacturer's name and then enter the last 4 digits of the unique number. If not, simply enter the company name and the last four digits of the unique number. In the case of Mercedes-Benz, only 5 letters of Mercedes are called, and the unique number 5381 is called after the company. Let's think about it one more time. LEFT function will cut 5 characters of selected cell. Because the number of characters in the cell is already less than 5 characters, only 4 characters of 'Volkswagen' are output. Even if you do not use the IF function, you can only get 5 characters cleanly like this. You can also enter label information without the IF function more concisely than before. Next, we distinguish motor companies. This time we use the FIND function. When the name of the motor is included in the name of the manufacturer, it is divided into 'motor manufacturer'. There is no 'motor' in the name of 'Volkswagen', so '#VALUE!' It does not matter if you get an error. I will fill it with autofill. For companies that contain the word motor, numbers are printed. The position of the string that begins with the letter 'motor' in the selected string. Since the motor starts from the third character, a value of 3 is output. Through the IF function, it identifies if the result of the FIND function is an error. If the result of the FIND function is an error through the ISERROR function, the output is blank. If not, it is classified as 'motor company'. Please fill it in with auto fill. Only 'motor companies' are distinguished. Please do not hide the scope that is not written. VLOOKUP is a function that you know a lot. Let's try it simple. The business name is put into the data validation list for selection. Please select a range of manufacturers for the list scope. You can select the business name as a selection box. I'm looking for a business name. The VLOOKUP function has the most caveat. The range referencing the find value must always be at the far left of the entire selected range. So we select reference range from column B to column F. Suppose you have selected the range from column A to column F. The range of business names that we need to look for is not at the far left. At the very left of the range, there is a unique number, so it can not be viewed. So choose from column B to column F. I'm looking for a country. The number of the column representing the country is second. Match option '0' with exact match. Copy the function and paste it to get the number of employees. The number of employees is third, so please put 3. Every time you select a business, the information for that business is called. Next, MIN and MAX functions are used to obtain the highest and lowest sales. First, the best sales use the MAX function. The MIN function returns the minimum sales for the entire range of total sales. We will get top sales by country. Use the MAXIFS function. Select the range for which you want to get the highest value. Select the first reference range. Please select country range as reference range. Please select a country to refer to. There are multiple values for 'Korea' in the country range. The country compares the total sales of each of Koreans and outputs the highest value among them. Please fill it with autofill. We got top sales for each country. Again, use the MINIFS function to obtain the lowest sales by country. We've got top / bottom sales by country. Finally, based on a number of conditions, we will get both total sales and average sales that meet both conditions. Please select the country range above the country. Number of Employees Please select the above number of employees through data validation. You can select the country and the number of employees as a list box. Use the SUMIFS function. Please select the range for which you want to sum. Secondly, please select the country range that is the first tranche range. Select the country cell that is the first truncation range. Select the range of the number of employees, the second condition range, and then select the number of employees. We have gross total sales for businesses with countries in Germany and fewer than 250 employees. This time we use the AVERAGEIFS function. Here, the sales value is in general number format, so I do not see it, so I will make it look better. After selecting two cells, please format cell. Please change the display format in the cell format to Accounting. I will check because there is no symbol. Please change to the right alignment with the above header. Previously, only sales by month / manufacturer were shown. Based on the previous lecture and the essential functions we learned in this lecture, we have drawn up the necessary content. You should always make sure that these materials are visualized and attached to the report. Let's create a simple chart. Let's create a chart that shows the top / bottom sales by country. Hold down the Ctrl key on your keyboard and select the lowest sales range. When you click on a referral chart in an insert, a "bundled column" will appear as a referral chart. First, clear the axis value of the vertical axis. I will also select the scale line and delete it. I will add a 'data label' after pressing the plus button next to it. We will change the title of the chart to "top / bottom sales by country." Let's look at the number of companies by country. This time, I will put map chart. Please change the chart title to 'Number of companies by country'. This time, we will change the font size to a smaller size. We will show average sales by country. After selecting the country, I will hold down the Ctrl key on the keyboard and select the average sales range. I'll click on the chart in Insert and put in a column chart. Please delete vertical axis value and grid line. Then we will add a data label. Let's change the data fill color of the chart to green. This lecture was followed by the previous lecture, and I learned the essential functions for the practitioners and then made a simple chart. Please leave a comment below regarding your questions during the lecture. If this lecture is helpful, I would like to ask you to subscribe.
