Home / Questions / 294628
Accepted
fedorqui
Asked: 2020-09-19 12:13:34 +0800 CST 2020-09-19 12:13:34 +0800 CST

How do I select different discontiguous columns to make a pivot table?

  • 772

I am working with pivot tables from Google Spreadsheets. It works well for me and I am extracting interesting data, basically thanks to what I learned in How do I see what is the most common value (mode) of a column? .

This time I have the following table of books read

 A  |  B    |   C   |  D   |   E    |   F
año | libro | autor | nota | idioma | país autor

With entries of the type

 A   |  B               |   C         | D | E   |   F
-------------------------------------------------------------
2004 | Hamlet           | Shakespeare | 8 | ES  | Reino Unido
2005 | Crimen y castigo | Dostoievski | 9 | CAT | Rusia
2011 | Dersu Uzala      | Arseniev    | 8 | EN  | Rusia

Now I want to create a pivot table that aggregates the countries the author comes from, tells me how many books I've read from each country, and what average grades they have.

In this specific case it would output something like:

país        | #libros | nota media
----------------------------------
Rusia       |    2    |     8,5
Reino Unido |    1    |     8

I have been able to do the first part, that of:

país        | #libros
---------------------
Rusia       |    2
Reino Unido |    1

With a pivot table where I simply choose the column F:F. However, to do the mean of the grades I also need the column Dand I don't know how to convert F:Fit to something like D:D;F:F. What would be the way?

google-hoja-de-cálculo

2 Answers

  1. Best Answer

    It makes no sense to select non-contiguous columns when the data source is a table (data set consisting of contiguous columns and rows). The recommendation is KISS (keep it super simple, keep it super simple)

    1. Prepare the data. In this case, add the headers in the first row and below these the values
    2. Select a cell of your data table, in this case any of the rangeA1:F4
    3. Click on the Data menu > Pivot Table
    4. Select destination (new sheet, same sheet, specify source cell for pivot table), let's sayA6

    5. Set up your pivot table

      • In rows, addpaís autor
      • In values, add book, specifyCOUNTA
      • Back in values, add nota, specifyAVERAGE

    6. Customize the headers,

      1. click B6and edit the cell value
      2. click C6and edit the cell value

    7. The result should be similar to the following:

    • 2

  2. You actually have to select all the information to put it in the table. Assuming you have:

    ![enter image description here

    You select it and click on Datos > Tabla dinamicawith the data of'Hoja 1'!A1:F4

    In the following image you would click:

    enter image description here

    1º  Filas > Añadir > pais autor

2º Valores > Añadir > libro

3º Valores > Añadir > nota

    In the latter you would have to change SUMtoAVERAGE

    And you would already have:

    ![![enter image description here

    If you want to change the name of the headers, click on one of them and write what you want to put in the upper bar (you will see that it already says COUNTA de libroor AVERAGE de nota, depending on the one you have selected).

    • 1