TEACHING EXCEL FOR DATA ANALYSIS: HANDS ON 20 Using table references

 

List of database functions

This table lists the available database functions, giving you the flexibility to perform many common operations on a specified subset of your data.

DAVERAGE	Calculates the average of values in a field of a list or database, that satisfy specified conditions
DCOUNT	Returns the number of cells containing numbers in a field of a list or database that satisfy specified conditions
DCOUNTA	Returns the number of non-blank cells in a field of a list or database, that satisfy specified conditions
DGET	Returns a single value from a field of a list or database, that satisfies specified conditions
DMAX	Returns the maximum value from a field of a list or database, that satisfy specified conditions
DMIN	Returns the minimum value from a field of a list or database, that satisfy specified conditions
DPRODUCT	Calculates the product of values in a field of a list or database, that satisfy specified conditions
DSTDEV	Calculates the standard deviation (based on a sample of a population) of values in a field of a list or database, that satisfy specified conditions
DSTDEVP	Calculates the standard deviation (based on an entire population) of values in a field of a list or database, that satisfy specified conditions
DSUM	Calculates the sum of values in a field of a list or database, that satisfy specified conditions
DVAR	Calculates the variance (based on a sample of a population) of values in a field of a list or database, that satisfy specified conditions
DVARP	Calculates the variance (based on an entire population) of values in a field of a list or database, that satisfy specified conditions

Using table references

You have been asked to create a workbook to analyze the daily SKU sales, by SKU number, and by order type. To complete this task, you decide to use database functions with structured references.

1. Click on the link below to open the Microsoft Excel exercise document you can use to complete this exercise.

https://github.com/alcadelina/Excel-

2. First select the table, then click Table Design, and type “Daily_Orders” in the Table Name field:

3. Press Enter to update the table name, then select cell D4 and type “=DCOUNT(“ in the Formula Bar:

4. Now begin typing the table name, “Daily_Orders” to display table name suggestions:

5. With the Daily_Orders table highlighted, press Tab to accept the suggestion:

6. Next, type an open square bracket ([) to show suggestions for table elements. Use the arrow key to select “#All” from the list, then press Tab to accept:

7. You can now type a closed square bracket (]) and a comma (,) to complete the database argument:

8. To define the field argument, click the table header “Order Number.” Excel enters the structured reference automatically:

9. To define the criteria argument, and complete the formula, first type a comma (,) to move to the next argument, then select the range A3:B4:

10. Press the F4 key to make this an absolute reference, then press Enter:

11. You will see that the formula returns the value 99, as there are 99 rows in the table, and no criteria have been entered:

12. Now type “Online” in cell A4. You will see that 50 of the order types were Online:

13. Type the SKU number “0406” in cell B4. While you would expect the count value to change, it has not:

14. This is because the heading in cell B3, “SKU No.”, does not match the column name in your table. Change the text in cell B3 to “SKU” to match the column name. Now you will see that the count value has changed:

15. You can now select cell E4 and follow the same steps, but this time use the DSUM function and use the table header “Order Price” for the field argument:

16. You will now see the sum of orders where SKU 0406 had an order type of Online:

17. Save the current workbook as Activity 3-3 Complete and then close Microsoft 365 Excel to complete this exercise.

18.Now, you can check out an example of a completed document in the link below:

https://github.com/alcadelina/Excel-