Each option has a different score, how to count the total score with excel.

This is better to use SPSS, professional questionnaire analysis is used SPSS, Excel's ** can also be directly copied in the past. Doing the above in SPSS is transforms-recode-intodifferent, and then turn 4 into 1, 3 into 2, 2 into 3, 1 into 4, so that you can get the result of the landlord

Name: vlookup column lookup function Format: vlookup (lookup value, region, column number, precision) Example sentence:

vlookup(200,b3:f9,2,false) function: In the specified area, the first column of the lookup is equal to the lookup.

It's this function.,But it's hard to use.,I haven't studied it thoroughly.,There's no detailed usage on the Internet.,Study it slowly.,I hope it helps you.,Hope!!

There is no pattern to the score of the options, so you might as well just enter the scores of each question and then count the total score.

First of all, the answer you choose must have the same properties (e.g. cell background color, which can be distinguished from other fractional cell attributes, although it is best to set all the values to the value color).

Then ctrl+f finds all the areas that are different from the score attribute of the answer, replaces all of them with 0, and then =sum (the rest is not zero). It feels a little troublesome.

I did it with the morning, it is not so rigorous, the formula is: =hlookup(h3,$b$2:$e$12,row()-1,0).

Because it's different from your table, you need to make some changes to my formula, and if the answer of the Divine Book student looks like the table I made, you also need to pay attention to the alignment of the question number.

Use the vlookup, or sumif function.

There should also be a diagram with row and column numbers.

If you don't understand, take the question as the content (the subject of the email must contain "excel", and I will use this as a basis to identify non-spam emails to avoid accidental deletion), and the excel sample file (try to detail the current situation and target effect) as an attachment to help you see.

C2 directly pastes the formula.

If you have questions, you can ask them, it's not easy to analyze, give me a motivation, thank you.

This can be solved with the rank function. Illustrate.

For example, there is a data table as follows.

Step 1: Enter the formula in C2: =rank(B2,B$2:B$14,0) Note: 1, parameter 0, means from reach.

genus to small ranking; 2, $2, and $14 indicate that the data region is fixed at rows 2 to 14, which is prepared for the next formula copying.

For example, if the scores are in column A, you will be ranked in order.

Rank formula b2=rank(a1,a:a) and then pull down.

After using the counting formula c2=countif(b:b,b1) drop-down formula to find duplicates, filter more than 1 for duplicates.

Let's say your score.

BAI is in column B with 100 results. The first fraction of the DU is the B2 cell, and the C2 cell shows the DAO

The return shown is the ranking of this score among all grades. Answer: Enter in cell C2: rank(b2,b$2:b$100) to select cell C2, move the mouse to the lower right corner of the mouse to + and then pull down.

The first copy knows if you are using version 07 or version 03.

Let's talk about 07 first, import (or enter) the data into Excel according to the whole column, and enter other information such as names in other columns.

Use the mouse to select the pure data content, right-click on the top, there is a sort, select the descending order under the sort, the dialog box will pop up, and the default selection is "Select Extension Area", OK. The whole content will be adjusted. Excel itself comes with the leftmost row number to indicate the ranking.

About the total score: For a student's grade, you enter the name language data English and then in the blank space at the back, click on the " under the formula to appear dotted line selected area, please use the mouse to select the Chinese mathematics English and other values, press enter. After all is done, use the above operations in the total score column to achieve ranking.

If your grades are B:G.

h1 input. sum(b1:g1)

i1 input. rank(h1,$h$1:$h$100) selects h1:i1

Drop down the formula together to fill.

Can. The 100 in the formula is assuming you have 100 lines, and you can change it yourself.

The total score of the statistical score can be summed using the sum function. The total score can be ranked using the rank function.

Here's a detailed description of the two functions and examples:

sum function.

Purpose: Returns the sum of all numbers in a range of cells.

Syntax: sum(number1,number2,..

Parameters: number1, number2,..Be the numeric value (including logical values and text expressions), regions, or references that need to be summed.

Note: The numbers, logical values, and text expressions of the numbers in the parameter table can be used in the calculation, where the logical values are converted to 1 and the text is converted to numbers. If the parameter is an array or reference, only the numbers in it will be evaluated, and blank cells, logical values, text, or error values in the array or reference will be ignored.

Example: If a1=1, a2=2, and a3=3, the formula =sum(a1:a3) returns 6. =sum("3",2, true) returns 6 because"3"is converted to the number 3, while the logical value true is converted to the number 1.

rank function.

Purpose: Returns the rank of a value in a set of values (if the list is already in order, the rank of the value is its current position).

Syntax: rank(number,ref,order).

Parameter: number is a number that needs to be calculated for its ranking; ref is an array or reference containing a set of numbers (where non-numeric arguments are ignored); order is a number that indicates how you are ranked. If the order is 0 or omitted, the list of data is ranked in descending order.

If the order is not zero, the ref is ranked as an ascending list of data.

Note: The rank function ranks the same value for the duplicate values. However, the presence of a number of replicates will affect the ranking of subsequent values. For example, if the integer 60 appears twice in a column of integers and its position is 5, then 61 is in position 7 (there is no value in rank 6).

Example: If a1=78, a2=45, a3=90, a4=12, and a5=85, the formula =rank(a1,$a$1:$a$5) is returned.

You can use the sum function for the total score, and the rank function for the ranking. The specific operation will be prompted in Excel. Follow the prompts and you're good to go.

For the total score, sum the scores of each subject, =sum(xx:xx), and then arrange the total scores in descending order, enter 1 for a new ranking on the side, and drag to fill it.

First sum is counted as the total score of the score, and then sort by the total score of the score.

It's been sent to you, you can take a look. - Wind.

The total score of each subject is summed and the ranking is obtained.

1. Open the Excel** application software, enter the source subject name and total score in column A, and enter the score of each subject in column B.

2. Enter [=] to the right of the total score.

3. Find and click the icon in the upper right corner, and then select [Average].

4. Select cells, that is, all cells with fractions.

5. Press [Enter] on the keyboard, that is, the enter key, so that the result will come out.

In the process of performance analysis, due to different du

The credits of subjects are different, so in the process of obtaining the average grade in DAO, it is necessary to weight different subordinate subjects according to the proportion of credits, and then obtain the average credit grade.

Grade Point Average = (Course 1 Grade Course 1 Credit + Course 2 Course 2 Credit + ......Course n course n credit) total credits.

1. Select the K5 cell, according to the formula for calculating the average credit grade, directly type "=(2*C5+1*D5+3*E5+2*F5+2*G5+3*H5+1*I5) 14" in the formula column (or cell), and then press the Enter key to find the average credit score of the student in K5.

2After completing step 1, you will find that the contents of cell k5 retain multiple decimal places, under normal circumstances, we only keep two decimal places. Select the K5 cell, right-click and select the "Format Cell (F)" option in the drop-down menu.

3In the new pop-up dialog box, select "Number" "Numeric" "Number of Decimal Places (D)", and enter "2" in the dialog box behind "Number of Decimal Places (D)", and then OK.

4. After obtaining the average credit score of the first student, select the K5 cell, move the cursor to the lower right corner of the K5 cell, and when the hollow cross becomes a solid cross, press the left mouse button and drag the mouse down to the K14 cell to obtain the average credit score of all students.

Use the countif function to calculate the number of people in the score band, assuming the score is in column A, example 1, count the number of people with 80 points and above, the formula is:

countif(a:a, ">=80")

The first parameter a: a means that the statistical area is column A, and the second parameter "=80" means 80 points and above.

Example 2: Count the number of people with a score of 60 or less:

countif(a:a, "<60")

Same as Example 1, except that <60 is used to indicate failure, and the number of people with 60 points is not included in the statistical results.

Example 3, counting the number of people with 100 points:

countif(a:a, 100)

The second parameter uses a fixed value to represent the number of people who have just that many points.

Example 4, statistics "= 80 and < 90 people, the statistical interval can not use two conditions, only to do the difference, the formula is:

countif(a:a, ">=80")-countif(a:a, ">=90")

The first formula calculates 80 and above, and the second formula calculates 90 and above, with a difference between 80 and 90 (including 80 and excluding 90).

excel has a special function to count the frequency distribution frequency, such as =frequency(a1:a10,) will count the number of a1:a10 less than or equal to 59, for example, a will count a1:

The number of numbers greater than 59 and less than or equal to 80 in a10, for example, b will count the number of numbers greater than 80 and less than or equal to 90 in a1:a10, for example, c will count the number of numbers greater than 90 in a1:a10, for example, d will return an array at the end.

=countif(a:a,">90"）

The number of items in column A is greater than 90.

countifs(a:a,">90"，a:a,"<95"In column A, the number of large 90 is less than 96.

If it's scattered, all I can say is, please die!

There is no actual function method that can be applied (except with macros).

For example, the data is a1:a10

Enter the formula in b1:

30)*(a$1:a$10<80)>0,row(a$1:a$10)),row(a1)))

ctrlshift

Enter by pressing the three key to enter.

Then pull down.