here is the link to the excel file https://www.dropbox.com/home?preview=Excel+As
ID: 3740623 • Letter: H
Question
here is the link to the excel file
https://www.dropbox.com/home?preview=Excel+Assignment+2.xlsx
Assume that you started a business named Healthy Cooking two years ago to provide prepared gourmet meals to retail food stores, living facilities for seniors, and businesses. You are using Excel to track orders and have entered customer order data for the first half of the year in the worksheet provided. You now want to analyze this data in several ways. Complete the following steps:
1. Open the workbook and then save it.
. On the “Order Amount Filter” worksheet create a filter to display invoices with order amounts that are either less than $100 or greater than $5000
3. Make a copy of the Orders worksheet, and then rename the copied worksheet as Sort.
4. On the “Sort” worksheet, sort the data in ascending order by Customer Type, then in ascending order by Delivered To, and then in ascending order by Customer.
5. Using Conditional Formatting, highlight all orders in the sorted table that have a Net Amount greater than $4000 in the format of your choice.
6. Make a copy of the Orders worksheet, and then rename the copied worksheet as Filter with Total Row. Filter the copied worksheet table to display orders with a June 2016 order date that included a discount.
7. Insert a Total row on the Filter with Total Row worksheet that calculates the totals for the Order Amount, Discount, and Net Amount columns for the filtered data. Format the totals for 2 decimal places.
8. Remove all Subtotals and Filters from the Customer Type Subtotal worksheet.
9. Using this unfiltered worksheet data, create a PivotTable on a new worksheet, named Customer Type PivotTable.
10. This new PivotTable should show the average Order Amount by Customer Type and Customer (as Rows).
11. Apply a Report Filter so that only data for the month of March is shown in the PivotTable.
12. Format the PivotTable data to show dollars and cents for all numeric values.
13. Then apply PivotTable Style Medium 3 for Design purposes. Band the Rows and Columns in the PivotTable.
14. Insert a Slicer to filter Customer only for the PivotTable results, then eliminate The Goode Horn in the Filter criteria.
15. Add a PivotChart for this PivotTable, placing it in a Pie Chart format below the PivotTable and Slicer. Add data labels to show total dollars and cents for these five Customers.
16. Save your file and submit it in Blackboard using the link provided.
Explanation / Answer
#following is the python code, I am attaching the output graph of the code as well
#You have to install xlrd and matplotlib.pyplot (if not allready installed)
# please set the name of given sheet as s1 i.e there can be more than 1 sheets in Excel.xlsx file.
# We have only one sheet in it in this example, set the name of that sheet as s1
import xlrd
import matplotlib.pyplot as plt
workbook = xlrd.open_workbook("Excel.xlsx")
worksheet = workbook.sheet_by_name("s1") # please set the name of given sheet as s1
rows = worksheet.nrows
cols = worksheet.ncols
# Students_withStraightA --> column 109
# Students_DeanList% --> column 110
x =[]
y =[]
j = 109
for i in range(1,rows):
x.append(worksheet.cell(i,j).value)
j = 110
for i in range(1,rows):
y.append(worksheet.cell(i,j).value)
plt.plot(x,y,'ro')
plt.ylabel('Students_DeanList%')
plt.xlabel('Students_withStraightA')
plt.axis([1, 23, 0, 14])
plt.show()
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.