Sorting in Word. Sorting data in Excel by rows and columns using formulas
Let's sort a table consisting of 2 columns using formulas. We will sort by one of the columns of the table (we will solve 2 problems: sorting the table by numeric and sorting by text column). Let's set up the sorting formulas so that when new data is added to the source table, the sorted table changes dynamically. This will allow you to always have a sorted table without user intervention. We will also do a two-level sorting: first by numeric, then (for repeating numbers) by text column.
Let there be a table consisting of 2 columns. One column is text: List of fruits; and the second one is numeric Volume of sales(see example file).
Problem 1 (Sorting a table by a number column)
You need to sort the table rows by the contents of the numeric column (by Sales Volume). It is assumed that the user is constantly filling out the rows of the table, so it is necessary to write formulas taking into account the added values.
For clarity, the values in the column Volume of sales highlighted with (). Repeated values are also highlighted in yellow.
Note: The problem of sorting a single column (list) was solved in articles and.
Solution1
If numeric column is guaranteed not to contain values, then the problem can be solved easily:
- Sort the numeric column using the LARGE() function (see article);
- The VLOOKUP() function or a bunch of functions INDEX()+MATCH() select values from a text column by its corresponding numeric value.
However, in real problems, a number column may contain repetitions, and since VLOOKUP function() in the case of repetitions, always selects only the first value from above (see article), then this approach is not suitable (the names of the Fruits will be displayed incorrectly).
Therefore, the sorting mechanism will have to be implemented differently.
INDEX(Sales;
ROUND(REMAT(LARGEST(
--(COUNTIF(Sales;"<"&Продажи)&","&ПОВТОР("0";3-ДЛСТР(СТРОКА(Продажи)-СТРОКА($E$6)))&СТРОКА(Продажи)-СТРОКА($E$6));
ROW()-ROW($E$6));1)*1000;0)
)
This formula sorts the column Volume of sales(dynamic range Sales) in descending order. Gaps in the source table are not allowed. The number of rows in the source table must be less than 1000.
Let's look at the formula in more detail:
- Formula COUNTIF(Sales;"<"&Продажи) returns the array (4:5:0:2:7:1:3:5). This means that the number 64 (from cell B7 the original table, i.e. first number from the range Sales) more than 4 values from the same range; number 74 (from cell B8 the original table, i.e. second number from the range Sales) more than 5 values from the same range; the next number 23 is the smallest (it is no larger than anyone), etc.
- Now let’s turn the above array of integers into an array of numbers with a fractional part, where the fractional part will contain the position number of the number in the array: (4,001:5,002:0,003:2,004:7,005:1,006:3,007:5,008). This is implemented by the expression &","&REPEAT("0";3-LENGTH(ROW(Sales)-ROW($E$6)))&ROW(Sales)-ROW($E$6)) It is in this part of the formula that there is a restriction of no more than 1000 rows in the source table (see above). It can be easily changed if desired, but this is pointless (see the section on computational speed below).
- The LARGE() function sorts the above array.
- The ROD() function returns the fractional part of a number, which is position numbers/1000, such as 0.005.
- The ROUND() function, after multiplying by 1000, rounds to an integer and returns the position number. Now all position numbers correspond to column numbers Sales volumes, sorted in descending order.
- The INDEX() function, given a position number, returns the number corresponding to it.
A similar formula can be written to display values in a column Fruits=INDEX(Fruit,ROUND(...))
In the example file, due to calculation speed considerations (see below), the same type of part of the formula, i.e. everything inside the ROUND() function is placed in a separate column J . Therefore, the final formulas in the sorted table look like this: =INDEX(Fruit,J7) And =INDEX(Sales,J7)
Also, by changing the function LARGE() to SMALL() in the array formula, we get sorting in ascending order.
For clarity, the values in the column Volume of sales highlighted with ( Home/ Styles/ Conditional Formatting/ Histograms). As you can see, sorting works.
Testing
Now let's add a new row to the original table. In dynamically sorted tables, we must get the appropriate sorting.
1. To cell A15
source table enter the word Carrot;
2. To cell B15
enter Volume of sales Carrots = 25;
3. After entering the values in the columns D
And E
a table sorted in descending order will be automatically displayed;
4. In a sorted table, the new row will be displayed second to last.
Formula calculation speed
On an “average” computer in terms of performance, recalculation of a pair of such array formulas, located in 100 lines, is practically invisible. For tables with 300 rows, the recalculation time takes 2-3 seconds, which is inconvenient. Or you need to disable automatic sheet recalculation ( Formulas/Calculations/Calculation Options) and press the key periodically F9, either abandon the use of array formulas, replacing them with columns with corresponding formulas, or completely abandon dynamic sorting in favor of using standard approaches (see the next section).
Alternative approaches to sorting tables
Let's sort the rows of the source table using a standard filter (select the headers of the source table and click CTRL+SHIFT+L). Select the required sorting from the drop-down list.
We will get a version of the table identical to ours, but when adding new values to the table, we will have to apply the filter again.
You can also use the Sort tool ( Data/Sort and Filter/Sort). To do this, you need to select all the values of the source table, not including the header, call the Sort tool, select the column by which you want to sort and the sorting option.
We will get a version of the table identical to ours, but when adding new values, we will also have to apply the filter again.
As in the previous problem, let’s assume that there are repetitions in the column by which sorting is being carried out (the names of Fruits are repeated).
To sort the table you will have to create 2 service columns (D and E).
=COUNTIF($B$7:$B$14;"<"&$B$7:$B$14)+1
This formula is analogous to text values (the position of the value relative to other values in the list). A text value lower in the alphabet has a higher "rank". For example, the value Apples corresponds to a maximum “rank” of 7 (including repetitions).
In column E, enter the usual formula:
=COUNTIF($D$6:D6,D7)+D7
This formula takes into account repetitions of text values and adjusts the "rank". Now different values of Apples correspond to different “ranks” - 7 and 8. This allows you to display a list of sorted values. To do this, use the formula (column G):
=INDEX($B$7:$B$14,MATCH(ROW()-ROW($G$6),$E$7:$E$14,0))
A similar formula will display the corresponding sales volume (column H).
Problem 2.1 (Two-level sorting)
Now let's sort the original table again by Sales Volume. But now for repeating values (in column A there are three values 74), we will display the corresponding values in alphabetical order.
To do this, we will use the results of Problem 1.1 and Problem 2.
Details in the example file on the sheet Task2.
Creating and formatting tables. Sorting in tables.
Calculations in tables. Framing and filling.
The main menu item commands are intended for working with tables. Table.
Exercise 1. Creating tables.
Create a journal (table) recording the current progress of students in your computer science subgroup in September and October, as follows:
Faculty
Course 1 Subject name Subgroup
| № | FULL NAME | February | March | April | May |
||||
| 14 | 28 | 14 | 28 | 11 | 25 | 9 | 23 |
||
| 1. | |||||||||
| 2. | |||||||||
| 3. | |||||||||
| 4. | |||||||||
| 5. | |||||||||
| 6. | |||||||||
| 7. | |||||||||
| 8. | |||||||||
| 9. | |||||||||
Performance.
To do this, create a new document, set the font to Times New Roman and size 14. In the first line, enter the name of the department, aligned to the center. To type the next line, place tab characters on the ruler at positions 5.5 (left aligned) and 14.4 (right aligned) and set the size to 12. Type “Course 1,” then press the tab key and enter the name of the subject, press the key again tabs and indicate the group number.
Execute menu command Table/Add table , in the dialog box Inserting a table indicate the number of columns – 11 and the number of rows – 10.
Select columns numbered 3-11 and execute the menu command Table / Cell height and width . In the dialog box Height and width of cells set the width of columns 3-11 to 1.2 cm, the width of column 2 to 3.8 cm. and the width of the 1st column is 1cm.
Select the top two cells of the first column and execute the menu command Table/Merge Cells and set the alignment to center. Follow these steps by sequentially selecting the top two cells of the second column, the next five cells of the first row, and the last 4 cells of the first row.
Enter data into the appropriate table cells. When entering titles No. and full name. to align them vertically use the commands Format/Paragraph and in the dialog box Paragraph set the desired field value Interval before To automatically enter values in the first column, use the command Format/List.
Selecting the desired areas of the table using the command Format/Borders and Shading give the table the desired appearance
Task 2.
Creating and sorting tables.
1. Create a table that looks like this:
| Last name I.O. | Job title | Salary |
|
| 1. | Sergeev V.V. | director | 20000000 |
| 2. | Petukhov V.V. | driver | 2000000 |
| 3. | Petrov V.V. | deputy directors | 12000000 |
| 4. | Mishina V.V. | cashier | 12000000 |
| 5. | Ivanov V.V. | deputy directors | 12000000 |
| 6. | Dubkov V.F. | accountant | 15000000 |
| 7. | Venik V.V. | driver | 2000000 |
| 8. | Vanin V.V. | driver | 2300000 |
| 9. | Vanin V.P. | driver | 2000000 |
| 10. | Sychev T.T. | driver | 2300000 |
2. Sort the table rows by last name in alphabetical order.
Methodical instructions.
To organize the table, do the following:
select rows in the table, starting from the second, and columns, starting from the second;
execute the menu command Table/Sorting , in the dialog box Sorting set in the list Sort Column 2 (sort by 2nd column), sort method - Text, press the button Options and check the box Columns only(so that cells with line numbers are not rearranged) and press the button OK. Save the resulting table in a file called lab.2_1.doc.
3. Sort the rows of the table in descending order of salaries and save the resulting table in a file called lab.2_2.doc.
4. Sort the rows of the table by position and for the same positions in ascending order of salary. Save the resulting table in a file called lab.2_3.doc.
5. Combine the documents recorded in the files into one document. To do this, use the command Insert/File. Number the tables in the merged document using the command Insert/Title.
6. Save the received document in a file Lobar_work_2_2.doc.
Task 3. Business card.
A business card is a small document containing basic information about the owner. The following information is most often entered into it:
The size of the business card is approximately 8 cm horizontally and 5 cm vertically. The structure of a business card is shown below:
Methodical instructions.
Here's how you can create a business card:
Create a new document
Insert a table of 2 rows and 2 columns
Set the length of the first and second column to 4 cm.
Select the first row of the table and run the command “Merge Cells”. The result will be a table consisting of three cells 1.2 and 3, looking like this:
1
2
3
Enter in cell No. 1 your place of work, position, last name, first name and patronymic. In cell No. 2 home address, in cell No. 3 - work and home telephone numbers, fax and email address.
Select the necessary fonts and their sizes. The style of the surname should stand out in relation to other information. Center the text in cell #1, cell #2. align left and cell #3 right.
Select the entire table and run the commands “ Format, Borders and Shading ”, In the dialog box, select the “Frame” mode so that your business card is framed.
Execute the commands “ Format, Columns” and set the sheet to two columns for text.
Select the table and copy it to the clipboard.
Place the cursor one line below the table.
Paste the contents of the clipboard (command “ Edit, Paste"). Repeat these steps five times. If the fifth copy does not fit in the first column, or if there is space left in the first column, resize the top and bottom margins of the page. Fill out the right column in the same way.
Task 4. Calculation in tables.
Performance.
Prepare the following document:
Intelligence
on the income and expenses of the company "Rhythm" for January-March 1997.
| January | February | March | Sum |
|
| Volume of sales | 45000000 | 50000000 | 48000000 | 143000000 |
| Purchase costs | 15000000 | 12000000 | 18000000 | 45000000 |
| Shipping costs | 6000000 | 8000000 | 10000000 | 24000000 |
| Income | 24000000 | 30000000 | 20000000 | 74000000 |
Table/Formula ">Chairman of the Board
company "Rhythm" I. I. Ivanov
2. To calculate the amounts located in the fifth column, you need to use the command Table/Formula enter the formulas in the cells of this column: =b2+c2+d2, =b3+c3+d3, =b4+c4+d4 or the formula: =SUM(LEFT).
To calculate the income located in the fifth line, you need to use the command Table/Formula enter the formulas into the cells of this column: =b2-(b3+b4), =c2-(c3+c4), =d2-(d3+d4).
3. Frame and fill the cells with the original data using the panel Tables And Borders or using the command Format/Border and Fill. Change the numbers in the cells with the original data and recalculate the table. Save the document as a file.
Task 5. Prepare the following type of advertisement:
Methodical instructions.
Create a table, making the borders, information locations invisible, and fill in the cells necessary information in the appropriate format.
For curly text, use Wordart objects, the button for working with which is located on the drawing panel.
If you need to find a specific value in a table, then it is most convenient to do this in sorted data. Next, we'll look at how to sort a table in Excel by descending or ascending order, text or cell color, and more.
To sort tables in Excel, use the menu item "Sorting and Filter" on the home tab. The most simple sorting given in Excel spreadsheet, this is an ascending or descending sort that can be applied to both text values, and to numbers.
For proper sorting, all cells being sorted must have the same format, for example, all cells with numbers must be in numeric format. Also, the values should not be preceded by extra spaces and there should not be any in the range being sorted hidden rows and columns.
Sorting values in an Excel table begins with selecting the column to be sorted. It is enough to even place the cursor in the first cell of the column. In our example, select the first cell of the second column and select from the menu "Sorting and Filter", and indicate "Sort Ascending".
As a result of sorting the data in the table, the values in the second table are sorted in ascending order, but the first cell remains in its place. This is because by default the first row in the table is considered headers, i.e. column names, and is not sorted.
The range of sorted data expands automatically, i.e. Excel automatically selects the entire table and sorts the data into rows according to the selected column. If you select all the cells in a column that you want to sort and select ascending or descending sort, Excel will display a message asking you to select a sort option. Option will be available "Automatically expand the selected range", which will select the entire table, and "Sort within specified selection", which will allow you to sort only the selected column without affecting the data in adjacent columns. In this case, the first cell will again not be taken into account when sorting.
To sort tables in Excel completely by all selected cells, you should go to the menu "Sorting and Filter" select item "Custom sorting...".
In this case, we will immediately see which part of the table Excel selects for further sorting.
To select the entire data area, in the window that appears, "Sorting" remove the check mark from the item "My data contains headers".
Now in this window you can configure the sorting of our data. In the first column "Column" in line "Sort by" select the column in which you want to sort the data. In the second column "Sorting" You must select the condition by which the sorting will be performed. Options available "Values", "Cell Color", "Font Color" And "Cell Icon". In our case, we will sort the values. Well, in the last column "Order" you can select the sort order of values "Ascending", "Descending" And "Custom List". Let's choose in ascending order. As a result, the middle column values are sorted.
You can also customize the sorting of an individual column. In addition, you can configure several sorting levels in Excel. For example, let's sort the first column in ascending order, and then sort the result of sorting the second column in ascending order. And we will sort the last third column first by cell color, and then by font color. To add a new level you need to in the window "Sorting" press the button "Add level", and the order of the levels matters.
About what's in word processor Microsoft Word you can create tables, almost all more or less active users of this program know. Yes, everything here is not as professionally implemented as in Excel, but for everyday needs it is possible text editor more than enough. We have already written quite a lot about the features of working with tables in Word, and in this article we will look at another topic.
How to sort a table alphabetically? Most likely, this is not the most popular question among Microsoft users, but not everyone knows the answer to it. In this article, we'll show you how to sort the contents of a table alphabetically, as well as how to sort a specific column.
1. Select the table with all its contents: to do this, position the cursor at its left top corner, wait for the table moving sign to appear (a small cross located in a square) and click on it.
2. Go to the tab "Layout"(chapter "Working with tables") and click on the button "Sorting" located in the group "Data".
Note: Before you start sorting the data in the table, we recommend cutting or copying the information contained in the header (first row) to another location. This will not only make sorting easier, but will also allow you to keep the table header in its place. If the position of the first row of the table is not important for you, and it should also be sorted alphabetically, select it too. You can also simply select the table without a header.
3. Select the required data sorting options in the window that opens.
If you want the data to be sorted relative to the first column, in the Sort By, Then By, Then By sections, set Columns 1.
If each column of the table should be sorted in alphabetical order, regardless of the other columns, you need to do this:
- "Sort by"— “Columns 1”;
- "Then by"— “Columns 2”;
- "Then by"— “Columns 3.”
Note: In our example, we only sort the first column alphabetically.
In the case of text data, as in our example, the parameters "Type" And "By" for each line should be left unchanged ( "text" And "paragraphs", respectively). Actually, it is simply impossible to sort numerical data alphabetically.
The last column in the window " Sorting" is actually responsible for the sorting type:
- "Ascending"— in alphabetical order (from “A” to “Z”);
- "descending"- in reverse alphabetical order (from “Z” to “A”).
4. After setting the required values, click "OK" to close the window and see the changes.
5. The data in the table will be sorted alphabetically.
Don't forget to return the hat to its place. Click in the first cell of the table and click "CTRL+V" or button "Insert" in Group "Clipboard"(tab "Home").
Sort a single table column alphabetically
Sometimes you need to sort data in alphabetical order from only one column of a table. Moreover, this must be done in such a way that the information from all other columns remains in its place. If it concerns exclusively the first column, you can use the method described above, doing it exactly the same way as we did in our example. If this is not the first column, do the following:
1. Select the table column that you want to sort alphabetically.
2. In the tab "Layout" in the tools group "Data" click the button "Sorting".
3. In the window that opens, in the section "First by" select initial sort option:
- data of a specific cell (in our example this is the letter “B”);
- please indicate serial number selected column;
- repeat the same step for the “Then By” sections.
Note: Which sort type to choose (parameters "Sort by" And "Then by") depends on the data in the column cells. In our example, when the cells of the second column contain only letters for alphabetical sorting It’s enough just to indicate in all sections "Columns 2". In this case, there is no need to perform the manipulations described below.
4. At the bottom of the window, select the option switch "List" to the required position:
- "Title Row";
- "No title line."
Note: The first parameter “attracts” the header to sorting, the second allows you to sort the column without taking into account the header.
5. Click the button below "Options".
6. In section "Sort Options" check the box next to the item "Columns only".
7. Closing the window "Sort Options"(“OK” button), make sure that the checkbox next to all sorting type items is checked "Ascending" (alphabet order) or "descending"(reverse alphabetical order).
8. Close the window by clicking "OK".
The column you select will be sorted alphabetically.
That's all, now you know how to sort a Word table alphabetically.
Review: Sorting
Word sorts paragraphs of text and rows/columns of tables.
Each line of the text fragment that is to be sorted must end with an end-of-paragraph character.
Word program recognizes tables automatically.
IN Word table distinguishes between sorting by rows (paragraphs, records) and by columns (fields):
In the case of sorting by rows (paragraphs, records), each complete table row (full paragraph) is considered as a single whole, and the paragraphs are sorted in the specified way, regardless of what part of the row was selected before sorting.
Column sorting sorts only the selected columns (fields) by the contents of the specified column (field) and leaves the rest of the table unchanged.
You can specify up to three keys for each sort procedure. The order is first sorted by the first key, then by the second, and finally by the third. Word distinguishes several types of sortable information: Text data includes letters and numbers (dates are treated as numbers), Number data includes only numbers (other characters are ignored), and Date data includes correctly formatted date values. The sort order is either Ascending (A...Z; 0...9; earlier date...later date) or Descending (Z...A; 9...0; later date... earlier date). For security reasons, it is recommended that you save the text before performing a sort, even though the sort operation can be undone normally.
Procedure. Sorting by rows (sorting lists)
1. Select the text to be sorted. The selected part should include the first field (first column) or the beginning of paragraphs of the lines (records) being sorted.
2. Execute the Table/Sort Text or Table/Sort command. A dialog box opens.
3. Set the first sort key.
In the first list, for the data presented in the form of a table, you can select a field (column) by whose contents the sorting will be performed. For example, Field 3 sorts by the third column.
In the Type field, select the type of information to be sorted.
Define the sort order: Ascending or Descending.
4. Click command button Options and in the Field Separator group, select the desired option(selection is possible if text is sorted, i.e. a list, not a Word table).
The Tab character option should be activated if individual elements lines (fields) are separated from one another by tab characters.
The Semicolon option should be activated if individual line elements (fields) are separated from each other by semicolons.
By selecting the Other option, you can specify any other character as a separator for line elements (fields). In the Sort Options dialog box, in the Sort option group, you can specify Extra options sorting.
You can use the Case Sensitive option to force Word to sort words written in capital letters, from the words written down lowercase letters, and display the first ones before the second ones.
The Columns Only option allows you to sort only individual columns—sorting by column.
Close the Sort Options dialog box by clicking OK. If necessary, specify other sort keys. If not all elements of the column specified as the first key are different from one another, then multiple keys must be used to perform a full hierarchical sort. In the List group, select required value options. If the first row (record, paragraph) of the selected ones is used as a table header (header of a list of records), then it can be excluded from sorting by selecting the With header row option. Click OK or the key to start the sorting process. If you are not satisfied with the result, you can cancel the sorting operation, like any other action, using the Edit/Undo command.
Procedure. Sorting by Columns
In the case of sorting by columns, only the fields (cells) contained in the selected columns are taken into account (and rearranged), in fact we're talking about about sorting only part of the table.
Select the desired field(s) in the list of records using ++ or by dragging the mouse while holding down the key and left mouse button. In the table, select the table columns to be sorted. You can sort several adjacent columns (fields) at the same time, but you should make sure that the entire contents of all sorted columns (fields) are selected. Execute the command Table/Sort Text or Table/Sort. A dialog box opens. Define the first sort key and click the Options button. The Sort Options dialog box appears. Select the Columns Only option. If necessary, set other parameters and confirm the installation by clicking OK. Word returns to the previous dialog box. Click OK or the key to start the sorting process.