09 ms excel

30 %
70 %
Information about 09 ms excel
xml

Published on August 31, 2011

Author: fosterstac

Source: slideshare.net

Microsoft ® Office Excel 2003 Training Using XML in Excel Peace River Distributing presents:

Course contents Overview: XML in Excel 2003 Lesson 1: The Excel approach to XML Lesson 2: Opening XML files as XML lists (Continued on next slide.) Using XML in Excel

Overview: XML in Excel 2003

Lesson 1: The Excel approach to XML

Lesson 2: Opening XML files as XML lists

(Continued on next slide.)

Course contents, cont’d. Lesson 3: Creating XML maps with the XML Source task pane Lesson 4: Understand the XML Source task pane Using XML in Excel Each lesson includes a list of suggested tasks and a set of test questions.

Lesson 3: Creating XML maps with the XML Source task pane

Lesson 4: Understand the XML Source task pane

Two new tools in Excel 2003 — XML lists and the XML Source task pane — make it easier than ever to use XML. Overview: XML in Excel 2003 Using XML in Excel Learn the concepts behind these new tools, and how to use them to process XML data in Excel 2003.

Two new tools in Excel 2003 — XML lists and the XML Source task pane — make it easier than ever to use XML.

Course goals Create an XML list. Create an XML map. Import and export XML data to and from mapped data cells. Use the icons, controls, and options in the XML Source task pane. Using XML in Excel

Create an XML list.

Create an XML map.

Import and export XML data to and from mapped data cells.

Use the icons, controls, and options in the XML Source task pane.

Lesson 1 The Excel approach to XML

The Excel approach to XML An XML map is a set of links that you create between an XML schema and the cells in a worksheet. If you don't have an XML schema, all you need is an XML data file. Excel will infer a schema from the structure of the tags in your data file. Using XML in Excel When you work with XML in Excel, you use maps.

An XML map is a set of links that you create between an XML schema and the cells in a worksheet.

If you don't have an XML schema, all you need is an XML data file. Excel will infer a schema from the structure of the tags in your data file.

Getting started using XML with Excel To work with XML in Excel, you use an XML schema to create what Excel calls an XML map. XML maps link the cells in a worksheet to the items (also called elements) in a schema. You then enter or import data into the mapped cells, work with that data, and then either save or export the data to an XML file. Using XML in Excel Don't try to go anywhere in XML without a map.

To work with XML in Excel, you use an XML schema to create what Excel calls an XML map. XML maps link the cells in a worksheet to the items (also called elements) in a schema.

You then enter or import data into the mapped cells, work with that data, and then either save or export the data to an XML file.

Getting started using XML with Excel If you don't have a schema, don't worry. All you need is an XML data file. Excel infers a schema from the structure of the tags in the file and allows you to create a map. You'll see that process in detail later in this course. Using XML in Excel Don't try to go anywhere in XML without a map.

If you don't have a schema, don't worry. All you need is an XML data file. Excel infers a schema from the structure of the tags in the file and allows you to create a map.

You'll see that process in detail later in this course.

XML maps, schemas, and data files This illustration shows how a map links the items in a schema to cells in a worksheet. You can create a map by using any of these three sources: Using XML in Excel Maps link elements in a schema to cells in a worksheet. A separate schema file (uses the .xsd extension) A schema embedded in an XML data file (uses the .xml extension) An XML data file with no schema other than its own tag structure

This illustration shows how a map links the items in a schema to cells in a worksheet. You can create a map by using any of these three sources:

A separate schema file (uses the .xsd extension)

A schema embedded in an XML data file (uses the .xml extension)

An XML data file with no schema other than its own tag structure

Ways to create XML maps Excel 2003 gives you two ways to get started with an XML map: Using XML in Excel Open an XML data file as an XML list. When you use this option, Excel creates the map for you automatically, and then imports all the data from the file into the list. The XML Source task pane is the best option for putting XML to work in Excel 2003.

Excel 2003 gives you two ways to get started with an XML map:

Ways to create XML maps Create a map by using the XML Source task pane. This is a much more powerful and flexible option because you can create maps from schemas or data files. (When you create an XML list, you can only use a data file.) You can also select just the items from the schema or data file that you want to see. Using XML in Excel The XML Source task pane is the best option for putting XML to work in Excel 2003.

Create a map by using the XML Source task pane.

This is a much more powerful and flexible option because you can create maps from schemas or data files. (When you create an XML list, you can only use a data file.) You can also select just the items from the schema or data file that you want to see.

Entering, saving, and exporting XML data Excel automatically loads all the data from the source file into your worksheet for you. However, when you create a map by using the XML Source task pane, you either enter data manually or import it from an existing file. Using XML in Excel Excel provides several ways to save and distribute your XML data.

Excel automatically loads all the data from the source file into your worksheet for you.

However, when you create a map by using the XML Source task pane, you either enter data manually or import it from an existing file.

Entering, saving, and exporting XML data Keep in mind that the underlying schema specifies the type of data you can enter into a mapped cell. For example, if a schema specifies numeric values for a given cell and you enter text, Excel displays an alert. Using XML in Excel Excel provides several ways to save and distribute your XML data.

Keep in mind that the underlying schema specifies the type of data you can enter into a mapped cell.

For example, if a schema specifies numeric values for a given cell and you enter text, Excel displays an alert.

Entering, saving, and exporting XML data When you're ready to save your data, Excel gives you several choices. You'll use these options the most: Using XML in Excel Excel provides several ways to save and distribute your XML data. Save your data as an Excel workbook You do this by saving the file using any of the standard methods that Excel provides.

When you're ready to save your data, Excel gives you several choices. You'll use these options the most:

Save your data as an Excel workbook You do this by saving the file using any of the standard methods that Excel provides.

Entering, saving, and exporting XML data Using XML in Excel Excel provides several ways to save and distribute your XML data. Save your data as a separate XML file You do this by using the Save As command and selecting the XML Data file format. Export the data Exporting is another way to create a standard XML data file. The export process also removes any formatting and objects such as images.

Save your data as a separate XML file You do this by using the Save As command and selecting the XML Data file format.

Export the data Exporting is another way to create a standard XML data file. The export process also removes any formatting and objects such as images.

Suggestions for practice List the ways you can create an XML map. List the ways you can save XML data in Excel. Using XML in Excel

List the ways you can create an XML map.

List the ways you can save XML data in Excel.

Test 1, question 1 You create an XML map when you need to: (Pick one answer.) Using XML in Excel Enter an array function (also called a CSE function). Link cells in a worksheet with items in a schema and make use of XML data. Use the XML Spreadsheet file format.

You create an XML map when you need to: (Pick one answer.)

Enter an array function (also called a CSE function).

Link cells in a worksheet with items in a schema and make use of XML data.

Use the XML Spreadsheet file format.

Test 1, question 1: Answer Link cells in a worksheet with items in a schema and make use of XML data. Using XML in Excel And to create the map, all you need is a schema or a data file.

Link cells in a worksheet with items in a schema and make use of XML data.

Test 1, question 2 You must create a map before you can save a spreadsheet as an XML data file. (Pick one answer.) Using XML in Excel True. False.

You must create a map before you can save a spreadsheet as an XML data file. (Pick one answer.)

True.

False.

Test 1, question 2: Answer True. Using XML in Excel You can create two types of maps, and the next lessons explain how.

True.

Lesson 2 Opening XML files as XML lists

Opening XML files as XML lists XML lists are new to Excel 2003. Opening an XML data file as an XML list is a quick way to browse the data in that file. XML lists also make it easy for you to sort and filter data and to insert common calculations, such as totals and averages. Using XML in Excel Find facts and answers quickly with an XML list.

XML lists are new to Excel 2003. Opening an XML data file as an XML list is a quick way to browse the data in that file.

XML lists also make it easy for you to sort and filter data and to insert common calculations, such as totals and averages.

Opening files as XML lists What is an XML list? An XML list has the same look, feel, and features as an Excel list. You can sort, filter, and add common calculations like sums and averages to your data. The only real difference between XML lists and Excel lists is the data source. Using XML in Excel XML lists have the same look and feel as Excel lists, but they use XML files as their data source.

What is an XML list?

An XML list has the same look, feel, and features as an Excel list. You can sort, filter, and add common calculations like sums and averages to your data.

The only real difference between XML lists and Excel lists is the data source.

Opening files as XML lists When do you use an XML list? You open an XML data file as an XML list when you want to view or work with the data in that file quickly, or when the file contains a small, straightforward data structure. Using XML in Excel XML lists have the same look and feel as Excel lists, but they use XML files as their data source.

When do you use an XML list?

You open an XML data file as an XML list when you want to view or work with the data in that file quickly, or when the file contains a small, straightforward data structure.

Important points about XML lists Using XML in Excel XML lists have the same look and feel as Excel lists, but they use XML files as their data source. You don't see the process, but Excel creates a map for you automatically when you open the data file as a list. The map becomes part of the workbook, and Excel saves any changes or new data to the workbook in the standard Excel file format (.xls). You can't export the data from the list, but you can import new or changed data into the list.

You don't see the process, but Excel creates a map for you automatically when you open the data file as a list.

The map becomes part of the workbook, and Excel saves any changes or new data to the workbook in the standard Excel file format (.xls).

You can't export the data from the list, but you can import new or changed data into the list.

Suggestions for practice Name the only real difference between XML lists and Excel lists. Describe when you would use an XML list. Describe at least two important points about XML lists. Using XML in Excel Online practice (requires Excel 2003)

Name the only real difference between XML lists and Excel lists.

Describe when you would use an XML list.

Describe at least two important points about XML lists.

Test 2, question 1 Excel creates an XML map for you when you open a data file as an XML list. (Pick one answer.) Using XML in Excel True. False.

Excel creates an XML map for you when you open a data file as an XML list. (Pick one answer.)

True.

False.

Test 2, question 1: Answer True. Using XML in Excel Remember that Excel does all the work for you, you have no control over the map, and the map becomes part of the workbook.

True.

Test 2, question 2 You open an XML data file as an XML list when you need to: (Pick one answer.) Using XML in Excel Apply a transform to it. Quickly browse, sort, or filter data. Export it for use by others.

You open an XML data file as an XML list when you need to: (Pick one answer.)

Apply a transform to it.

Quickly browse, sort, or filter data.

Export it for use by others.

Test 2, question 2: Answer Quickly browse, sort, or filter data. Using XML in Excel You can also add totals, averages, and other common calculations to the list.

Quickly browse, sort, or filter data.

Lesson 3 Creating XML maps with the XML Source task pane

Using the XML Source task pane Using the XML Source task pane to create a map is the most powerful and flexible way to use XML in Excel 2003. The options in this task pane enable you to be selective about which data you view. Using XML in Excel The XML Source task pane is how you really put XML to work in Excel.

Using the XML Source task pane to create a map is the most powerful and flexible way to use XML in Excel 2003.

The options in this task pane enable you to be selective about which data you view.

Why create XML maps with the task pane? Creating an XML map with the XML Source task pane takes a bit of time and effort, but the results are much more powerful. You can: Using XML in Excel Maps link the items in a schema with the cells in a worksheet. Display only the data you want to see by dragging the elements you need onto your worksheet.

Creating an XML map with the XML Source task pane takes a bit of time and effort, but the results are much more powerful. You can:

Display only the data you want to see by dragging the elements you need onto your worksheet.

Why create XML maps with the task pane? Using XML in Excel Maps link the items in a schema with the cells in a worksheet. Save or export the mapped data as standard XML, which means that other users or systems can process it. Refresh the data in mapped cells as needed.

Save or export the mapped data as standard XML, which means that other users or systems can process it.

Refresh the data in mapped cells as needed.

An example of a map at work Here's a common example of how you might use a map. Say that one of your customers sends purchase orders in the form of XML files created in Microsoft Office Word 2003. Using XML in Excel Maps can help you use XML data from any number of sources.

Here's a common example of how you might use a map. Say that one of your customers sends purchase orders in the form of XML files created in Microsoft Office Word 2003.

An example of a map at work Your shipping department appreciates the use of XML, but it uses Excel to track all orders and shipments. So, you create a map to make the data usable in Excel. Using XML in Excel Maps can help you use XML data from any number of sources.

Your shipping department appreciates the use of XML, but it uses Excel to track all orders and shipments. So, you create a map to make the data usable in Excel.

An example of a map at work In this particular case, the items in the file created with Word are mapped to specific cells in a worksheet. The employees in the shipping department then put the worksheets to use as needed to retrieve ordered items and track shipments. Using XML in Excel Maps can help you use XML data from any number of sources.

In this particular case, the items in the file created with Word are mapped to specific cells in a worksheet.

The employees in the shipping department then put the worksheets to use as needed to retrieve ordered items and track shipments.

Suggestions for practice List a few results you can achieve by creating an XML map with the XML Source task pane. Describe a scenario in which you might use an XML map. Using XML in Excel Online practice (requires Excel 2003)

List a few results you can achieve by creating an XML map with the XML Source task pane.

Describe a scenario in which you might use an XML map.

Test 3, question 1 You export data when you need to: (Pick one answer.) Using XML in Excel Empty the workbook to make room for new data. Make the data available to other users or systems. Create tables in a relational database.

You export data when you need to: (Pick one answer.)

Empty the workbook to make room for new data.

Make the data available to other users or systems.

Create tables in a relational database.

Test 3, question 1: Answer Make the data available to other users or systems. Using XML in Excel You export data to an XML file so that other systems or users can process it.

Make the data available to other users or systems.

Test 3, question 2 When you first create a map, the mapped cells remain empty because: (Pick one answer.) Using XML in Excel Networks aren't engineered to provide data while you create a map. Excel requires you to filter the data first. Schemas don't contain data.

When you first create a map, the mapped cells remain empty because: (Pick one answer.)

Networks aren't engineered to provide data while you create a map.

Excel requires you to filter the data first.

Schemas don't contain data.

Test 3, question 2: Answer Schemas don't contain data. Using XML in Excel Schemas contain rules for what can and can't reside in an XML data file. That's why you have to import data into the mapped cells after you create the map.

Schemas don't contain data.

Lesson 4 Understand the XML Source task pane

Understand the XML Source task pane Now that you've had a chance to use the XML Source task pane, take a deeper look at the controls it provides. You’ll see what the icons in the element list mean, and how to use the various XML Source options. Using XML in Excel Learn the ins and outs of the XML Source task pane.

Now that you've had a chance to use the XML Source task pane, take a deeper look at the controls it provides.

You’ll see what the icons in the element list mean, and how to use the various XML Source options.

The controls in the XML Source task pane You can open the task pane in several ways: Using XML in Excel Controls and other elements in the XML Source task pane Press SHIFT + F1. From the Data menu ( XML submenu, XML Source command). With the Use the XML Source task pane command in the Open XML dialog box (File menu, Open command).

You can open the task pane in several ways:

Press SHIFT + F1.

From the Data menu ( XML submenu, XML Source command).

With the Use the XML Source task pane command in the Open XML dialog box (File menu, Open command).

The controls in the XML Source task pane List of XML maps that you or others have added to the workbook. Use the list to select a given map. Element list. A hierarchical list of the elements in an XML schema. Using XML in Excel Controls and other elements in the XML Source task pane Controls and elements in the XML Source task pane:

List of XML maps that you or others have added to the workbook. Use the list to select a given map.

Element list. A hierarchical list of the elements in an XML schema.

The controls in the XML Source task pane Set options for using the XML Source task pane. Open the XML Maps dialog box. Before you export data from mapped cells, this option validates your data against the schema used to create the map. Using XML in Excel Controls and other elements in the XML Source task pane

Set options for using the XML Source task pane.

Open the XML Maps dialog box.

Before you export data from mapped cells, this option validates your data against the schema used to create the map.

What do all those icons mean? There are a number of icons in the XML Source task pane. What icons you see in the task pane depends on the schemas that you use to create maps. The following table lists and describes all of the possible icons in the XML Source task pane. Using XML in Excel The icons in the element list indicate the type of element you're adding to a map.

There are a number of icons in the XML Source task pane. What icons you see in the task pane depends on the schemas that you use to create maps.

The following table lists and describes all of the possible icons in the XML Source task pane.

Icons in the elements list Using XML in Excel Icon Element type Icon Element type Parent element Repeating child element Required parent element Required repeating child element Repeating parent element Attribute Required repeating parent element Required attribute Child element Simple content in a complex structure Required child element Required simple content in a complex structure

Understand the task pane options Using XML in Excel Options for the XML Source task pane Preview Data in Task Pane When checked, displays sample data in the element list. Hide Help Text in the Task Pane When checked, hides the Help text that appears below the element list in the task pane. To see these options, click the Options button in the XML Source task pane.

Preview Data in Task Pane When checked, displays sample data in the element list.

Hide Help Text in the Task Pane When checked, hides the Help text that appears below the element list in the task pane.

Understand the task pane options Using XML in Excel Options for the XML Source task pane Automatically Merge Elements When Mapping When checked, Excel creates an XML list when you place repeating elements next to each other on a row. My Data Has Headings When checked, Excel uses the existing column headings in a worksheet when you map repeating elements.

Automatically Merge Elements When Mapping When checked, Excel creates an XML list when you place repeating elements next to each other on a row.

My Data Has Headings When checked, Excel uses the existing column headings in a worksheet when you map repeating elements.

Understand the task pane options Using XML in Excel Options for the XML Source task pane Hide Border of Inactive Lists When checked, this option hides the border of a list or a single-mapped cell when you select a cell outside of the list or the single-mapped cell.

Hide Border of Inactive Lists When checked, this option hides the border of a list or a single-mapped cell when you select a cell outside of the list or the single-mapped cell.

Suggestions for practice Name both ways that you can open the XML Source task pane. Describe how you can view the options for the XML Source task pane. List a few of the options for the task pane, and what they do. Using XML in Excel

Name both ways that you can open the XML Source task pane.

Describe how you can view the options for the XML Source task pane.

List a few of the options for the task pane, and what they do.

Test 4, question 1 This icon represents a required child element: (Pick one answer.) Using XML in Excel True. False.

This icon represents a required child element: (Pick one answer.)

True.

False.

Test 4, question 1: Answer False. Using XML in Excel This is a basic child element icon. (If it were required, there would be a red star in one corner.) Chances are, you’ll map child elements more than any others.

False.

Test 4, question 2 You can add any number of XML maps to a worksheet. (Pick one answer.) Using XML in Excel True. False.

You can add any number of XML maps to a worksheet. (Pick one answer.)

True.

False.

Test 4, question 2: Answer True. Using XML in Excel Keep in mind that adding dozens of maps can make the workbook hard for others to use.

True.

Quick Reference Card For a summary of the tasks covered in this course, view the Quick Reference Card . Using XML in Excel

For a summary of the tasks covered in this course, view the Quick Reference Card .

USING THIS TEMPLATE See the notes pane or view the full notes page (View menu) for detailed help on this template.

Add a comment

Related presentations

Related pages

Microsoft Office Home: Bürosoftware-Anwendungen für ...

Excel; PowerPoint; Outlook; Access; Exchange; OneDrive for Business; Project; SharePoint; Skype for Business; Visio; Alle Office-Produkte anzeigen; Weiter ...
Read more

Microsoft Excel 2010 - Preis ab 232,09 € kaufen | BestCheck

Microsoft Excel 2010 kaufen. Preis ab 232,09 € (22.11.16) bei ausgewählten Shops im Preisvergleich von BestCheck.
Read more

09 MS Excel - základní vzorce - YouTube

Video tutoriály Excel 2010: Rychlé kopírování dat nebo vzorců do celé délky sloupce - Duration: 3:13. vime 6,352 views
Read more

Excel Schulung | Excel Kurs | Excel Seminar | Excel Kurse ...

MS Excel 2007 / Excel 2010 / Excel 2013 / Excel 2016 - Basics für Einsteiger in Excel MS ... 07.12. - 09.12.16. Weitere Angebote in Hannover! Koblenz.
Read more

Microsoft Excel - MS-Office-Forum

Web ms-office-forum.net MS-Office-Forum > Microsoft Office: Microsoft Excel Benutzername: Angemeldet ... (22.11.2016 09:48) 22.11.2016 16:42
Read more

Lektion 09/ MS Excel 2010: Absolute Adressierung Teil 1 ...

Lektion 09/ MS Excel 2010: Absolute Adressierung Teil 1 | Informatik | Informationstechnik NachhilfeInformatik. Subscribe Subscribed Unsubscribe ...
Read more

Bewertungsbogen - MS Excel 2010 Aufbaukurs (15.09.2014 ...

Uni Intern: Uni Intern - Bewertungsbogen - MS Excel 2010 Aufbaukurs (15.09.2014)
Read more

MS Excel 2010 / Excel 2013 / Excel 2016 - PowerPivot in ...

MS Excel 2010 / Excel 2013 / Excel 2016 - PowerPivot - Seminare Schulungen Kurse und Trainings in Deutschland Österreich und in der Schweiz
Read more

VBA Code pur...: MS-Project -> Excel!

Mit Excel VBA Daten aus MS-Project ziehen. ... 01.09.2012. MS-Project -> Excel! Frage: Aus MS-Project sollen Daten nach Excel eingelesen werden.
Read more

Function Improvements in Excel 2010 - Office Blogs

Thanks to Jessica Liu for putting together the next few posts on function improvements. In Excel 2010, we made many improvements to Excel’s function ...
Read more