Pengantar Teknologi Informasi – Pertemuan 13 – Materi Perkuliahan

Gede Surya Mahendra - Home

Pengantar Teknologi Informasi – Pertemuan 13 – Materi Perkuliahan

Stiki Logo - Short

Mata Kuliah : Pengantar Teknologi Informasi

Kode Mata Kuliah : MPB-201

Program Studi : Teknik Informatika

STMIK STIKOM Indonesia

PERTEMUAN 13

Untuk tampilan lebih baik, dapat mendownload file PDF pada link yang tersedia

Download File Materi PDF

 

TIM PENYUSUN:

I GUSTI AYU AGUNG MAS ARISTAMY, S.TI., M.KOM

CHRISTINA PURNAMA YANTI, S.KOM., M.KOM.

NI KADEK ARIASIH, S.KOM., M.T

 

Materi Ajar
Microsoft Office Specialis (MOS) Lanjutan

(Pertemuan 13)

Mg Ke- Kemampuan Akhir yang Diharapkan Bahan Kajian Metode Pembelajaran Waktu Pengalaman Belajar Penilaian dan Indikator Bobot Penilaian (%)
(1) (2) (3) (4) (5) (6) (7) (8)
12,13 Mahasiswa mampu memahami dan mengerjakan soal terkait sertifikasi Microsoft Office Specialist (MOS) Ketepatan mengerjakan soal-soal sertifikasi MOS, yang mana membahas tentang penggunaan Microsoft Excel dalam bekerja. Pembelajaran Project Based Learning dalam mengerjakan soal yang diberikan. [TM : 2x(2×50”)] Mampu menggunakan dan mengerjakan soal sertifikasi terkait dengan penggunaan Microsoft Excel dalam bekerja. Kemampuan dalam menggunakan dan mengerjakan soal sertifikasi terkait dengan penggunaan Microsoft Excel dalam bekerja, seperti membuat tabel, grafik, perhitungan di Excel dll. 10%

Create Tables

The practice file for these tasks is located in the MOSExcel2016\Objective3 folder. The folder also contains a subfolder of result files that you can use to check your work.

  1. Create and manage tables practice task

Get Ready. Open Excel_3-1 for this lesson

Step by step :

  1. Open the Excel_3-1 workbook. On the Sales worksheet, Convert the data range A2:M23 to a table that includes a header row and uses the default table style.
  2. Assign the name Toys2016 to the table.
  3. Move the July column so that it is between the June and August columns.
  4. Move the Linda, Max, and Nancy rows at one time so that they are between the Kay and Olivia rows.
  5. Add a row to the table for a salesperson named Raina, between the Quentin and Steve rows.
  6. Add a row to the end of the table for a salesperson named William.
  7. Add a column named Dec to the right end of the table.
  8. Manage table styles and options

Get Ready. Open Excel_3-2 for this lesson

Step by step :

  1. Open the Excel_3-2 workbook, on the Sales worksheet, do the
  2. following:
  3. Change the table style to Table Style Medium 19.
  4. Configure the table style options to format alternating rows with different fill colors.
  5. Configure the table style options to emphasize the first and last columns of the table.
  6. Add a total row to the table.
  7. Change the row name from Total to Average.
  8. Modify the cells in the row to calculate the average sales for each month and for the year.
  9. On the Bonuses worksheet, do the following: Remove the formatting from the table.
  10. Filter and sort tables

Get Ready. Open Excel_3-3 for this lesson

Step by step :

  1. Open the Excel_3-3 workbook, display the Bonuses worksheet, and Apply a filter to display only the bonuses that were less than $2,500.00.
  2. Display the Products worksheet and
  • Sort the data in descending order by category and, within each category, alphabetically by product name.
  • Remove duplicates so that there is only one entry for each supplier.

Perform Operations with Formulas and Functions

The practice file for these tasks is located in the MOSExcel2016\Objective4\Excel_4-1 folder.

  1. Summarize data by using functions

Get Ready. Open Excel_4-1 workbook for this lesson

Step by step :

  1. On the Multiplication Table worksheet, create a formula in cells B2:T20 to complete the multiplication table of the numbers 1 through 20.
  2. Open the Excel_4-1b workbook. On the Summary worksheet, display the total sales for each period in cells B2:B5 by referencing the corresponding worksheets.
  3. Open the Excel_4-1c workbook, display the Seasonal worksheet, and do the following:
  4. In cell B18, create a formula that returns the number of non-empty cells in the Period range.
  5. In cell C18, create a formula that returns the average value in the Sales range.
  6. In cell D5, create a formula that returns the lowest Sales value for the Fall period.
  7. In F19, return the highest sales amount
  8. On the Sales By Category worksheet, do the following:
  9. On NetSales column return the net sales of each product sales after discount. The discount amount displayed on G1
  10. In cell D51, calculate the total of Net Sales, using a relative cell range reference.
  11. Perform conditional operations by using functions

Get Ready. Open Excel_4-2 workbook for this lesson

Step by step :

On the Expense Statement worksheet, do the following:

  1. In cell C25, create the formula to determine whether the Entertainment total is less than or equal $200.00 and the Misc. total is less than or equal $100.00. The return value is True or False.
  2. In cell C26, create the formula to determine whether the Entertainment total is more than $200.00 or the Misc. total is more than $100.00.
  3. In cell C27, create formula to display the text “Expenses are okay” if C25 evaluates to TRUE and “Expenses are too high” if C25 evaluates to FALSE.
  4. In cell C28, create formula to display the text “Expenses are okay” if C26 evaluates to NOT TRUE and “Expenses are too high” if C26 evaluates to NOT FALSE.
  5. In cell G28, create formula to return the number of cells in the range D10:I19 that contain values greater than zero.
  6. Perform conditional operations by using functions

Get Ready. Open Excel_4-3 workbook for this lesson

Step by step :

On the Book List worksheet, do the following:

  1. In the File By column, create a function that inserts the first letter of the author’s last name.
  2. In the Locator column, create a function that insert the author’s area code (three digits in the middle of the phone number).
  3. In the Biography column, create a function to insert text in the form: example for row 1 : Joan Lambert is the author of Microsoft Word 2016 Step by Step, which was published by Microsof

Create Charts and Objects

The practice file for these tasks is located in the MOSExcel2016\Objective5 folder.

  1. Create charts

Get Ready. Open Excel_5-1 workbook for this lesson

Step by step :

Open the Excel_5-1 workbook and do the following:

  1. Plot the data on the Seattle worksheet as a 2-D Pie chart on that worksheet.
  2. Plot the data on the Sales worksheet as a simple two-dimensional clustered column chart.
  3. On the Fall Sales worksheet, do the following:
  4. Switch the rows and columns of the chart.
  5. Change the October sales amount for the Flowers category to 888.25 and ensure that the chart reflects the change.
  6. Expand the data range plotted by the chart to include November, so that you can compare sales for the two months.
  7. Create charts

Get Ready. Open Excel_5-2 workbook for this lesson

Step by step :

Display the Sales worksheet, and do the following:

1. Change the pie chart to a 3-D Clustered Column chart.

2. Apply Style 7 to the chart.

3. Apply the Subtle Effect – Olive Green, Accent 3 shape style.

4. Increase the size of the chart until it occupies cells A1:L23.

5. Move the chart to a new chart sheet named Sales Chart.

6. On the Seattle worksheet, do the following:

  1. Add the title Air Quality Index Report to the chart.
  2. Add data labels that show the percentage of the whole that is
  3. represented by each data marker.
  4. Insert and format objects

Get Ready. Open Excel_5-3 workbook for this lesson

Step by step :

  1. Display the Summary sheet and Insert the Excel_5-3b logo in the upper-left corner of the sheet.
  2. Insert a text box on the sheet. Configure the text box to be three inches wide and three inches high, and align it below the heading “Our Prediction.”
  3. Insert the content of the Excel_5-3c text file into the text box. Format the text in 20-point orange Candara font, and center it in the text box.

Add alternative text to the logo, using the title Company logo and the description Cartoon image of a person wear

 

 439 total views,  5 views today

Tags: ,

Leave a Reply

Your email address will not be published. Required fields are marked *