Latihan Praktik Excel Untuk Admin Kantor #1

Summary

In this tutorial, the presenter walks viewers through practical Excel exercises, focusing on the use of various Excel formulas to solve real-world problems. The video includes instructions for filling out Excel tables based on ticket codes, using formulas like LEFT, RIGHT, VLOOKUP, and HLOOKUP to extract and match data. It covers class identification, ticket types, pricing, discounts, and final price calculations, while also introducing ways to manage large datasets using named ranges. Aimed at individuals seeking administrative roles, the tutorial provides valuable insights into improving Excel skills for job applications.

Takeaways

• 😀 Learn how to apply various Excel formulas in a practical exercise.
• 😀 The task involves filling empty columns using Excel formulas based on provided instructions.
• 😀 For the 'Class' column, use the LEFT function to extract the first three characters from the ticket code.
• 😀 The 'Class' value is then used to look up the class name (e.g., 'Economy') from a reference table using HLOOKUP.
• 😀 The 'Ticket Type' column requires extracting the fourth character from the ticket code using the RIGHT function and looking it up in the 'Ticket Type' table with VLOOKUP.
• 😀 To retrieve the ticket price, use a combination of VLOOKUP and RIGHT functions along with the IF function to determine which column (economy, business, or executive) to reference for the correct price.
• 😀 The 'Discount' column calculates 10% off the ticket price if the departure date is before September 1, 2023, using the DAYS function to calculate the difference between the departure date and September 1.
• 😀 The formula for 'Discount' checks if the departure date is before September 1 and applies the discount if the difference in days is greater than zero.
• 😀 The 'Net Price' is simply the ticket price minus any applicable discount.
• 😀 The video demonstrates the use of multiple Excel functions like LEFT, RIGHT, HLOOKUP, VLOOKUP, IF, and DAYS to solve practical problems in a real-world scenario involving ticket pricing and discounts.

Q & A

What is the main objective of the tutorial in the video?

The main objective of the tutorial is to help viewers learn how to solve Excel practice problems using various Excel formulas. The tutorial is especially beneficial for those planning to apply for administrative staff positions.

How do you extract the first three letters of a ticket code in the example?

To extract the first three letters from the ticket code, the LEFT formula is used. The formula is written as =LEFT(C2, 3), which takes the first three characters from the text in cell C2.

What formula is used to find the class from the ticket code, and how does it work?

The HLOOKUP formula is used to find the class from the ticket code. The formula searches for the three-letter code (e.g., 'Eco') in a predefined table and returns the corresponding class name (e.g., 'Economy').

Why is HLOOKUP used instead of VLOOKUP in the class search formula?

HLOOKUP is used instead of VLOOKUP because the search is based on a row (horizontal search) rather than a column. The formula looks for the class in the second row of the table.

What formula is used to find the ticket type, and how is it applied?

The RIGHT formula is used to extract the last character from the ticket code. This character is then used as a lookup value in a VLOOKUP formula to determine the ticket type (e.g., 'D' for 'Adult').

How do you determine whether a person is eligible for a discount?

To determine discount eligibility, the tutorial uses the DAYS formula to calculate the difference in days between the departure date and September 1, 2023. If the difference is greater than zero, the person qualifies for a 10% discount.

What is the purpose of the IF formula in calculating ticket prices?

The IF formula is used to select the correct column in the VLOOKUP function based on the class of the ticket (e.g., 'Eco' for Economy, 'Bis' for Business). It checks the class and applies the corresponding column number for ticket pricing.

What is the role of the VLOOKUP formula in determining ticket prices?

The VLOOKUP formula is used to find the ticket price based on the ticket type and class. It searches for the ticket type (e.g., 'Adult') and returns the corresponding price based on the selected class (Economy, Business, or Executive).

What is the significance of using the table names like 'table_kelas' and 'table_tiket'?

Using table names such as 'table_kelas' and 'table_tiket' simplifies the formula by replacing cell references with more descriptive names. This makes the formula easier to read and maintain, especially when dealing with large datasets.

How do you calculate the net price of the ticket after applying the discount?

The net price is calculated by subtracting the discount from the ticket price. The formula for this is =price - discount, where the price is retrieved using VLOOKUP and the discount is determined using the IF and DAYS functions.