rnberson9的部落格

One of the record great features in Excel is called PivotTables, which allow a terrible treaty of compliance when superficial at duplex associated information weather condition. The PivotTable control provides a high-speed and elementary scheme for ever-changing the way you appearance at your accumulation. We'll originate a ascetic PivotTable, mistreatment numerous primary notes I've supplied (see knit in adjacent writing). If you prefer, you can manufacture up your own data, but it should be of analogous complexity and convolution. PivotTables have by a long way more than to submit than what we will see in this childlike example, but this will present you a grassroots notion of what PivotTables can do.

Populating the assemblage folio is e'er Step 1. .For this example, the information are in a tab-separated matter file, which requirements gross sales aggregation for two salespeople all over a length of 7 weeks in cardinal sales categories. Save the assemblage prime to your desktop, past amenable Excel and sound on the Open holdfast. At the bottom of the dialog box move the file form to article files, and voyage to the desktop wherever you should brainstorm the rescued file. Follow the wizardly done (you should not want to money any defaults). Once you have imported the background into Excel, choose an expand compartment and select the PivotTable word from the Data menu.

In this case, we're taking the accumulation from our Excel sheet, but information can also locomote from other sources like an external information well (e.g. an Access info), multiple non-contiguous ranges in an Excel leaf or even other PivotTable.

Once you've identified the facts source, Excel will impoverishment to know the ambit of the background. If your indicator was not inwardly the register of date when you started the PivotTable wizard, you will status to manually choice the continuum of information. Select the collection by clicking the left-most upper cell of the band and, spell retaining the rodent lever down, resistance the gnawing animal all over the total selection to call attention to it. When you limit the lower-right cranny of the range, let go of the gnawer fixing. Excel will go into the span numbers into the PivotTable Wizard.

In the finishing talking box, you can point whether you deprivation the array to initiate in the stirring worksheet, or in a new sheet. You're too given Layout and Option buttons that will help out you formatting your opening tabular array.

If you clutch the Layout button, a new duologue box will initiate and you'll be allowed to plan your table reported to the way in which you impoverishment to watch at your data. Buttons will be a symbol of all indian file in the notes range, and you'll cart one or more than of the buttons into the row or indian file constituency to reformat the table. In our case, we can elect to choose to gawp at the array by salesperson, by time period or by gross sales collection.

We'll gawp at the collection by week. Drag the "Week" button over and done with to the Column speciality of the Layout. Drag the Sales Person and Category all over to the Rows area, and cart the Amount control to the Data sphere of influence of the plan. Click "OK" and afterwards "Finish" to rank the table.

You'll be shown a tabular array unionised by Sales Person, afterwards Category on the near edge of the tabular array. Each week's information will be shown in a different column. This allows you to likeness the rite of all income soul in each of 3 categories by period. The totals for all category will be found on the straight broadside of the table, and the period sum for all being will be found at the foot of all gross revenue person's weekly indian file.

If you would like to watch at the table on a nothing like basis, in attendance are a digit of ways to produce your PivotTable. One mode is you can go in the PivotTable Wizard and make your table layout. You can enter the PivotTable Wizard through with the PivotTable toolbar nether the View > Toolbars bill of fare. You may likewise clink on one of the PivotTable weather condition to begin the PivotTable Toolbar.
Once you're wager on into the Wizard, choice "Layout." Move the Week control to the Row sphere and shove the "Sales Person" key to the Column swathe. Click OK, and then Finish. Your array will form according to the new design you've elected.

You can besides fix up your array exploitation more than than one facts round in the column area, although the more than columns you have the large your tabular array will be.

If the sextuple collection sets give too considerably information, or you impoverishment to simplify the display, you can go into the Layout Wizard once more and use the Page pen to additional segregated your collection. In this example, we'll go support into the Wizard and tow the Weeks fastener to the Page paddock. Put the Sales Person fixing in the Columns specialism and the Category in the Rows sphere. Click OK and Finish. This converts Weeks to a pull-down menu, and removes it from the leading array showing. Now, we can superior period income public presentation data, and we likewise have an picking to choice all weeks in our scope.

You can place the PivotTable any way that suits your desires. When you have all gone musical performance near the PivotTable layout, stockpile your information and the table in a file. In the adjacent post, I'll conceal PivotCharts, which will let you to graphically show your PivotTable facts in a database or PowerPoint promotion. You'll use the PivotTable you've retributive created, or you can construct a new PivotTable to hard work next to PivotCharts.