no subject

[taimatsu]

Could any one of you who knows how to use Excel PivotTables tell me what they might be used for? I would like to learn them, and can probably teach myself successfully with the aid of the help files, but knowing what an appropriate dataset for such things looks like would be very helpful.

I interviewed at an agency today, and once again scored very highly on all my IT tests (Word/Excel/Powerpoint) though my typing is down to c. 50WPM rather than the 59 it was last time I was tested.

I *must* get a job soon. The cheque for the English course has been cashed, which is good - the Japanese one hasn't, and I am running low on funds to meet it. This is why I hate cheques with a passion and never use them if I have a choice. Dammit.

Comments

[crocodilewings.livejournal.com]

Let's say you've got lots and lots of data, with different things charted against different things. A pivot table lets you view any two axes and the correlation between them.

Best used in conjunction with a datacube. As a result, you're probably best getting into databases first.

[undyingking.livejournal.com]

Difficult to describe without an example, but basically I use them when I have a load of rows of data and want to analyse something about subsets of those rows. Like say you've got a load of login activity records where each row is a particular person doing a particular type of thing on a particular some other thing at a particular time etc. You can use sorts and filters to pick out patterns from it, but if you convert it to a pivot table then that effectively automates presentation of the results of all the different sorts and filters that you might want to do.
(Hmm, that's not very clear at all! Well, I think the help is pretty good actually and it won't take long to see how it works... or there are probably online tutorials as well.)

[chrisvenus]

In googling I found http://www.ucas.ac.uk/figures/about/pivot.html which discusses the use of pivot tables with their datasets (downloadable from a nearby link).

Pivot tables have useful features of being able to aggregate data whcih is the key thing in my view. For example if you have a dataset of what courses are being taken at an evening class you might have courses split over categories like languages, sciences, arts, etc. You might also have date ranges categorised by month, quarter and year.

The data table might have colums:

Course Code
Course Name
Course Section
Month
Quarter
Year
Attendees

You could then create a pivot table from this data that had the course details as rows and the timeframe as columns. You would be able to set it up such that you could drill down into the coarser categories or see the rolledup statistics.

So your initial view might have just years down the left and the course sections along the top. You might then click on a year to expand it to see the figures split out into 1st, 2nd, 3rd Quarter etc. You might then click on a course section to see the courses in that section.

If courses were run daytime and evening there might be another column to indicate this and you might then use that as a filter to say view all, just daytime or just nighttime.

This is roughly how I see them being used but there is probably quite a lot you can do with them including the more advanced stuff using datacubes and other such buzzwordy stuff. :)

[flick.livejournal.com]

I use them quite a lot at work (accountancy stuff).

There are two main ways of using them: to count the number of instances of things (which tends to be the default) and to add up the total of things-that-are-numbers.

For example, say you were selling four distinct products, and had a set of invoice data with the following column headings:
Year
Month
Name of person being invoiced
Item bought
Amount
Paid (yes/no)

You could arrange that in a pivot table to show you total sales to different people, or of each item, or of a each item to each person. You could also use it to count the number of each item sold each year (2001, 2002, 2003), or each month (all the Januaries, all the Februaries, etc), or each month by year (January 2001, February 2001, etc).

Another example:
If you produce a listing of all the transactions on a company's payroll account, you'll end up with column headings like:
Year
Month
Name
Basic pay
NICs
PAYE
Employee's pension payment
Employer's pension payment
Health insurance
Student loan deduction
etc

You can then use a pivot table to make it easier to see what has been paid for each person in each of the categories.

Helpful? Not helpful?

Pivot tables are quite fun, and not nearly so hard as people think.

[omentide.livejournal.com]

Pivot tables are incredibly useful to me. I doubt if I could do my job in less than twice the time without pivot tables.

They tell me things like, how many patients had which disease on what ward. they count things for you.

You can do them in more than 2 dimensions, if you need to.

To take a relatively trivial example, with the Cam database, I can find out the number of members in each Domain and get those into a nice, easy to understand, table without doing loads and loads of tedious counting.

[hairyears.livejournal.com]

Hints for pivot table users:

1. Dont.
2. Take aspirin and lie down, the urge will pass.
3. If you must, use the Pivot Table Wizard.