Grouping Data in a PivotTable

Download the file used in the video tutorial:

GROUPING TEXT FIELDS OR INDIVIDUAL ELEMENTS

In fields with levels, you can only group elements that have the same sublevels. For example, if a field has two levels "Country" and "City", you cannot group cities from different countries.

RENAME THE DEFAULT GROUP
When grouping items, Excel sets default group names, such as Group1(Group1) for selected items or Kv-l1(Qtr1) for quarter 1 (if we work with dates). Giving a more descriptive name to a group is easy:

1. Select group name;
2. Press a key F2;
3. Enter a new group name.

UNGROUPING ELEMENTS

1. Select a group of elements that you want to ungroup;
2. On the tab Options in a group Group (Group) press the button Ungroup (Ungroup) (or click right click mouse and select from the context menu Ungroup).

For numeric or datetime fields, all groups for the field will be ungrouped. Selecting groups will ungroup only the selected items, and the group field will not be removed from the PivotTable Field List until all groups in the field have been removed.

Notes:

1. For sources OLAP data(Online Analytical Processing) that do not support the CREATE SESSION CUBE statement, item grouping is not possible.
2. If there is one or more grouped elements, use the Convert to formulas command ( Parameters- Service- OLAP tools) impossible. You must first remove the grouped elements before using this command.
3. For fast work with data groups, you need to select the cells in the area of ​​\u200b\u200bthe row or column names of the pivot table, right-click on any of the selected cells and select Expand / Collapse (Expand / Collapse)

See also:
[[Introduction to PivotTables]]
[[Multiple sheet PivotTable]]

("Bottom bar":("textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24," textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance" :30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600 ,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500," textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100% ; background-color:#333333; opacity:0.6; filter:a lpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive": "","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40))

To quickly find files and quickly navigate through them, it is not enough to put everything in the right folders. It is equally important to give your files easy-to-read and meaningful names. Dealing with each of them individually is counterproductive. Therefore, there are ways to group rename.

To work with a group of files, it is better to copy them to a separate folder. This will greatly simplify the process.

Using regular Windows tools

Changing file names

Consider the simplest case: we have the nth number of images with meaningless titles.

Let's bring the names of these files to a visual form Picture(n), where picture will be the name and n- serial number. This can be easily done right in the "Explorer".

Select all the pictures that we want to rename. To do this quickly, use the keyboard shortcut Ctrl + A. Then just press F2 or right-click on the first file and select "Rename". As a name for the selected image, enter the word picture and press Enter.

Windows will automatically assign the same name to all subsequent files and arrange all sequential numbers.

If necessary, you can cancel the bulk rename with the Ctrl + Z key combination.

If you want to rename a group of folders in Explorer, you can do it in the same way.

Changing file extensions

Let's say we have some text documents, which, after double-clicking, open with notepad. But we need them to be opened by the browser by default. To do this, you will have to change their extension from .txt to .html. Operations of this kind can be quickly performed using command line.

Click windows keys+ R, enter in the field that appears cmd and click OK - we see the command line window. Now we insert into it the path to the folder in which our files are located, immediately after the command cd: cd C:\Users\Max Volotsky\Desktop\Docs , and press Enter. Then we use a special command that will change their extensions: rename *.txt *.html , and press Enter again.

The first in the command is the original extension, the second is the new one. After entering the command, the system instantly makes changes.

To return everything as it was, you need to reuse the rename command, swapping the extensions.

Using third party utilities

No built-in tools can match the functionality of special programs, designed specifically for batch renaming. You can find many of these on the web. free utilities, just enter bulk file rename in the search.

We will consider renaming using the Bulk Rename Utility program as an example. With its help, it is very convenient to change the names of folders and files, as well as the extensions of the latter.

At the first start, it may seem that the utility interface came straight from hell, and perhaps there is some truth in this. But the user does not need to navigate through numerous tabs and menus: he gets access to all functions from one window.

So, let's say we have a group of audio files that we want to bring to a pleasing look. In this case, the numbers and the absence of the artist's name are annoying.

We launch the application and through the built-in file manager we go to the folder we need. Next, with the already familiar keyboard shortcut Ctrl + A, select all files. We need to remove the first 3 characters at the beginning of the name of each file and add the artist name at the very beginning. To do this, specify the necessary parameters on the Remove and Add panels, and then click Rename.

Now let's make sure that all extensions consist of lowercase characters: in the Extension panel, select the Lower option and click Rename.

As you can see, other panels are available in the program window. For example, Numbering allows you to number documents different ways, and using Case you can change the case of characters in file names. Thanks to the New Name column in the built-in explorer, which shows the result of a rename before it is applied, you can easily understand the rest of the Bulk Rename Utility tools.

The last operation with files and folders performed in the program can be easily canceled with the Ctrl + Z key combinations.

Instruction

Select the named area whose name you want to change. If it is a column, click on its heading with the mouse pointer. Above the table, above the column headings, there is a formula bar. The address of the current cell is displayed in the leftmost field of this line. After selecting a named range or a separate cell, the address in it should be replaced by the name assigned to the range. If this did not happen after selecting the column, then the name was assigned not to the entire column, but to a certain group of cells in it. Gradually highlighting the cells of the column from top to bottom, wait until the name appears in this field and proceed to the next step.

Select the column name in the left field of the formula bar and type a new one. Use the Russian or Latin alphabet, numbers and underscores, the total number of which should not exceed 255 characters. When finished with the input, press the Enter key, and the new name of the selected area will be fixed.

The second step can be replaced by using a dialogue with more detailed settings cell naming. To call it, go to the "Formulas" tab and in the "Defined names" group of commands, open the "Assign a name" drop-down list. The desired item of this list is named exactly the same - “Assign a name”.

In the "Name" field of the "Create Name" dialog, type a new name for the column. In the "Area" drop-down list, select an individual worksheet or the entire workbook whose formulas should be able to use this name. You can enter explanatory text in the Note field. There is no need to change the value in the "Range" field, because Excel has already put the address of the selected range into it. Click OK and the column name will be changed.

Often, changing the name of columns means a completely different operation - changing the addressing method. By default, columns in Excel tables are denoted by Latin letters, which are used as column addresses. Letter designations can be replaced by numbers. If you need to perform exactly this or the reverse operation, do it through the Excel settings. In version 2010, open the menu using the "File" button and select "Options" from it. In the 2007 version, this button is called Office, and to access the settings, you need to click on the “ Excel settings».

Go to the "Formulas" section of the editor settings window and in the "Working with formulas" section check or uncheck the "R1C1 link style" checkbox. Click the OK button and the way the column headings are addressed will change.

Sources:

• as in excel range names
• Sort data in a range or table

After filling rows and columns with data spreadsheets in editor Microsoft office Excel user various operations are available with them. This can be not only with the manipulation of the data of individual cells, but also of entire rows or columns. In particular, they can be duplicated, deleted, moved or swapped - such operations are quite simply implemented in Excel.

You will need

• Tabular Microsoft editor Office Excel versions 2007 or 2010.

Instruction

Use a combination of cut and paste operations to swap two columns of a table. To do this, first select one of them by clicking on the heading - a cell with a Latin letter above the top cell of the column. Then either press the key combination Ctrl + X or right-click on the selection and select the Cut command from the context menu.

Select by clicking on the header another column involved in the exchange operation. Open context menu, right-clicking the selected area, and select the "Paste Cut Cells" command. This command is duplicated in the "Insert" drop-down list placed in the "Cells" group of commands on the "Home" tab in the menu spreadsheet editor- you can use this insertion method.

If you want to swap not two columns, but, for example, six, then you can do the previous two steps as many times as you need, or you can cut and move a group of columns at once. For such a selection, click all the headings of the column group while holding down the Ctrl key. If there are many such columns, then it is not necessary to click each of them - select the first one, press the Shift key and click the title of the last one. And you can, after highlighting the first column, press the right arrow while pressing Shift key until the entire range of columns is selected. The remaining operations - and insertion - when manipulating a group of columns do not differ from those described in the previous two steps.

note

If formulas are placed in the table cells that use references to floating columns, then pay attention to the change in the values ​​calculated by the formulas. Some Excel functions is not able to automatically track the change in the positions of the cells involved in the calculations, especially if they are assigned their own names.

Sources:

• how to swap lines

Each cell in Microsoft program Excel has certain coordinates. The columns of the table are indicated by Latin letters, the rows by numbers. This allows you to very quickly find the desired data located in cells A6 or B8. The need to rename columns does not occur very often. For example, the user mastered the program, accidentally put a tick somewhere - and numbers appeared instead of Latin letters. A similar problem sometimes occurs when opening a spreadsheet made on another computer.

You will need

• - computer with Microsoft Excel.

Consolidated Excel tables have a field grouping tool with various types data: dates, numbers and text. For example, you can group order dates by year or month, see the number of tested students who scored a passing score. Manually select text items in a pivot table box and group them. This will allow you to see subtotals for a specific set of items in a pivot table.

In today's post, we'll look at grouping in .

Grouping by dates

To group by date, right-click on the date field in the pivot table. From the dropdown menu, select Group.

Grouping, select one or more items from the field with a step. To limit the start and end dates, you can change them in the fields Beginning with And By. Click OK to close the dialog box.

Grouping by week

To group dates by week, right-click on the date field. From the dropdown menu, select Group. In the dialog box that appears Grouping, select item days in field With a step and specify the number of days 7. If necessary, you can change the start date to another one. Click OK.

Grouping Numeric Values

First you need to create a pivot table, and place it in string field the data you want to group.

Please note that population figures are in string field.

Select the field you want to group and click the tab Working with pivot tables -> Group -> Group by field.

In the dialog box that appears Grouping, adjust the grouping range and step. In our case, we set the range from 0 to 12000 in increments of 1000.

In field Values put the data you want to roll up by grouped data. In our example, I want to see the number of regions in each population range.

The resulting summary table shows the frequency distribution of the population of Russian regions.

Grouping text fields

Paragraph Group will not be available from the drop down menu if you right click on the text field. They must be grouped manually.

Select the field you want to group. On the ribbon, click the tab Work with pivot tables -> Group -> Group by selection.

To rename the group name, left-click on the field and enter your name in the formula bar.

Probably, many faced such a problem when you a bunch of files, for example, pictures that usually have the following name: DSCF9163 or IMG_0065 or some other krakozyabra =). Do you want these pictures to be called New Year!

What to do?

Rename each photo one by one?

Well, if there are 10-20 of them, and if there are 100-1000 or more. You know what I'll tell you: you get sick of renaming them =)))

Rename a group of files you can do it very quickly, literally in a couple of seconds, well, for students, it will take a matter of minutes!

Since we do not want to rename a large group of files with handles, it means that some program will help us. And this program Total Commander. I think everyone knows what kind of animal it is, and who does not know, follow the link, download and get acquainted.

And here I will still say that Total Commander is a file manager with great features!

So, let's get started, whoever has this program we launch, who doesn't - download, install and run.

To start - select the group of files that we need to rename, if you need to select all the files in the folder, press CTRL + A

A window will appear:

So, we wanted to give all files the name "New Year". So let's do it. In the "Mask for: file name" field, write the name that we want to see, in this case "New Year" and also add a counter so that we do not have the same names, for this we click on the "[C] Counter" button .

Click "Run", and after execution, close this window.

Hooray! Everything worked out! This method can be used for absolutely any files, but most often it is used to rename images.

Well, now let's deal with some basic settings. batch rename files. First, completely clear the "Mask for: file name" field (for clarity). Please note that when you clear the field, the new file name will also become clear, that is, we can visually see what will happen to the name.

And so the “[N] name” button means inserting the old name into the new name.

You can use this button if you want to use the old one in the new name. Going further, "Date" inserts into the file name, its creation date.

The Time button works similarly to Date, only it inserts not the date, but the time the file was created.

"diap." inserts not the whole old name as "N", but only its selected part (range). For example, let's make it so that only the first three letters remain from the old name, for this we click on "Diap.", Select the first three characters in the window that appears and click "ok"

We see that in the "Mask for: file name" field it says , which means that the new name uses the first three characters of the old one (that is, from the first to the third character). If you need to use a different range, then simply select it in the "Select characters to insert" window.

Well, now let's move on to the "[C] Counter" button, in my opinion, it is the most important here. Since the previous buttons cannot be used separately, because the names of new files can be the same (except for the time the file was created), we need to use something else to make the names different, in this case numbers will be used.

This counter has some settings (see section "Counter [C] parameters"). In the "Start at" field, you specify the number from which the countdown will begin, for example 111

In the "Step" field, you can specify how many digits the next number will go, for example 3

In the "Numbers" field, we indicate the number of digits that we want to see in the number, in this case, for clarity, we will take more, for example 5

Well, with the last button “[=?] Plugin”, you can add additional information to the file name (size, path, attributes, weight). There is a huge amount of information that can be added using this button.

Everything, like with all the basic settings figured out. As you can see rename group of files at once quite simple, and I am sure that it will not be difficult for you.

But, if you still have questions - you are welcome in the comments! =) Good luck!