However, perhaps the most elegant method is the player0 / Ben Collins approach which unpivots a sheet using this beautiful formula : There are a few methods you could use, all of which involve building up a coordinate map of your data and then restructuring it in a new location, including this example using OFFSET, ROUND, MOD, COLUMN and ROW. Unpivoting in Google Sheets is a bit harder. Perhaps the year and/or college would be sufficient to do that, or maybe we're going to need a unique student ID. If we don't want these two entities mingling together into some frightening gestalt, we're going to need to provide some means of disambiguation. But perhaps we've got two of them (like the one from The Lower Third and the one from The Monkees ). Which is all well and good if we've only got one David Jones in our student cohort. So it will look at our data-set, spot three rows of David Jones es and start merging them. Pivot tables work by grouping like values together. These attributes should then be the additional columns used in the data set, so each amount has a corresponding activity, student, college and year. For example, college and year are technically attributes of the student, but since we are storing all the data in one table, these must also be regarded as attributes of the amount, and included with each value. Next, surround this with the collection of related attributes :Īlso include ‘attributes of attributes’ if you are likely to make use of them. We’ll continue to use the student fund-raising example.įirst, identify the data that provides the individual values that are most important to your analysis - in this case, it’s the amount raised at a specific activity undertaken by a specific student. Here’s one approach that may help you plan or check your data structure. Stick to one dataset per tab of a spreadsheet file month names) no matter how tempting it might be!ĭo not leave whole rows or columns empty (some blank cells are fine) text, number, date), and each cell should contain just one valueĮnter column headings in one row at the top of the list – never use more, and never merge cells for labelsĭo not repeat attributes across several columns (e.g.
the amount raised in the above example)Įach column should contain just one type of information (e.g. The rules we talked about regarding lists are doubly true here:ĭata should be entered down the page, each item occupying a new row, so that each row contains one instance of the value to be used in calculation (e.g. And the stuff we want to be able to measure (specifically the amount raised) is in the one easy-to-handle column. This may look odd, particularly the repetition, but it means that each row is a collection of separate data items including all relevant information for each instance of a student doing an activity.
0 kommentar(er)
