• Home
• Access tips
• EXcel tips
• PowerPoint tips
• Word tips
• About

Copy the column width to retain the source of the Excel spreadsheet

In Excel, according to the paper size if the column width is set up, ready to print out a copy of the form, I would like to change the contents of some of its forms as another form, whether or not the form found in the contents of the stick has not changed, but the width of column has changed? Only once again in order to adjust the column width printout. In fact, as long as we do again and then will be able to stick out one form to retain the source column width. If selected in the worksheet cells ready to copy, copy to other cells, you will find a new visco-column width of the table although changed, but the lower right corner of the table but more out of a “Paste Options” button, if there is no found that “Paste Options” button, you can click the “Tools” → Options, in the open “Options” dialog box, select “Edit” tab, look at the “Show Paste Options” is checked on

Click the “Paste Options” button, select “reservations source column width”, stick to keep the same form from the original column width of the!

insert Word document into Excel

A friend of mine made a Email  asked me, Is there a way to insert Word document into  Excel spreadsheet? Answer  is yes. office series software is one of the major advantages of working together to each other, between different applications can easily exchange content. Use the Insert Object Excel function, you can easily insert in Word documents Excel. Here are the specific steps.

1. Open the word document to insert the Excel spreadsheet file.

2. Click word document to insert the cell, and then select the menu command “Insert” → “object.”

3. As shown in Figure 1 in the “target” dialog box “New” tab, select the object type “Microsoft word document” and click “OK” button.

4. At this time there will be an Excel document word edit box, directly inside the contents, methods and edit Word documents in exactly the same, as shown in Figure 2.

5. In accordance with the need to drag around the word document object control points, you can adjust the size of Word Document Object, you can mouse over the edge to change its position by dragging. Finished editing the object can be outside the cell, click the mouse, from the editors state. At this point Word document object if you click, you will see the control points around into a circle, drag the graphic object can be the same as dragging the Word object location, and change its size, as shown in Figure 3, operation with very convenient. Double-click the object to re-enter the edit state.

6. In addition to the above to create a new word document object methods, to be in Step 3, select the “Create from file” and then in the dialog box shown in Figure 4, click “View orders” have been found the existence of the Word document, select the finish and return to the dialog box, click “OK” button. Inserted into the Word document Excel document, you can double-click to open and edit at any time.

Figure 4 “from the file creation” tab

If you want direct access to stored on the hard drive or other storage on the word document, do not want to increase the size of Excel files, you can select the dialog box shown in Figure 4 of the box “link to file”, so that when the modified source file , the source file changes will be reflected in the Excel document.

Using the two methods has a check box “as icon”, if it is selected, Excel will only display a document icon, as shown in Figure 5.

Figure 5 shows the icon of the word for the Document Object

Double-click the icon, you can start editing a document word.

More than in the Excel spreadsheet can be inserted into word document object in Word can also be inserted in the Excel spreadsheet object, readers can learn by analogy, to try.

Application of Excel function tutorials: Statistical functions (d)

61.SKEW

Purposes: to return to a degree of asymmetry in the distribution. It reflects the average of the asymmetry in the distribution center the degree of asymmetry is that the degree of asymmetry of the distribution side of a time when even more difficult. That the asymmetry degree of negative asymmetry in the distribution side has become more negative.

Syntax: SKEW (number1, number2 ,…)。

Parameters: Number1, number2 … is the need to calculate the degree of asymmetry 1-30 parameters. Including comma-separated values, such as a single array and name.

Example: the formula “= SKEW ((22,23,29,19,38,27,25), (16,15,19,17,15,14,34))” return .854631382.

62.SLOPE

Purposes: to return to the set of data points after the linear regression equation fitting the slope of line (it is the straight line distance between any two points of vertical and horizontal distance ratio, that is, the rate of change of linear regression).

Syntax: SLOPE (known_y’s, known_x’s)

Parameters: Known_y’s dependent variable for the number of type array or range, Known_x’s data points as variables collection.

Example: the formula “= SLOPE ((22,23,29,19,38,27,25), (16,15,19,17,15,14,34))” return -0.100680934.

63.SMALL

Purposes: to return to the first k data of the minimum value, resulting in a specific location on the data values.

Syntax: SMALL (array, k)

Parameters: Array is a need to find the first k-minimum number of the array or data region, K for the return of data or data in the array where the location of the region (from small to large).

Examples: If If A1 = 78, A2 = 45, A3 = 90, A4 = 12, A5 = 85, while the formula “= SMALL (A1: A5, 3)” to return to 78.

64.STANDARDIZE

Purposes: to mean to the average return to standard-dev for the standard deviation of the distribution of normal values.

Syntax: STANDARDIZE (x, mean, standard_dev)

Parameters: X for the needs of normal values, Mean distribution of arithmetic mean, Standard_dev standard deviation for the distribution.

Example: the formula “= STANDARDIZE (62,60,10)” to return to 0.2.

65.STDEV

Purposes: to estimate the standard deviation of the sample. It reflects the data in relation to the average (mean) the degree of dispersion.

Syntax: STDEV (number1, number2 ,…)

Parameters: Number1, number2, … correspond to the overall sample for the parameter 1-30. Can use comma-separated parameter form can also use the array, that is, the reference cell array.

Note: STDEV function parameters by assuming that the sample overall. All the samples if the data is in general, they should be calculated using standard deviation STDEVP function. At the same time, ignore the parameters of function of logical values (TRUE or FALSE) and text. If you can not ignore the logic of value and the text should be used STDEVA function.

Example: Suppose a particular sample of test scores for A1 = 78, A2 = 45, A3 = 90, A4 = 12, A5 = 85, then to estimate the standard deviation of all results of formula “= STDEV (A1: A5)”, its results equal to 33.00757489.

66.STDEVA

Use: calculation of a given sample based on the standard deviation. STDEV function which is the difference between the value of the text and logical values (TRUE or FALSE) will also participate in the calculation.

Syntax: STDEVA (value1, value2 ,…)

Parameters: value1, value2, … as the overall sample parameters 1-30. Can use comma-separated form of parameters, you can use a single array, that is, the reference cell array.

Example: Suppose a particular part of the examination results for A1 = 78, A2 = 45, A3 = 90, A4 = 12, A5 = 85, then to estimate the standard deviation of all results of formula “= STDEVA (A1: A5)”, its results equal to 33.00757489.

67.STDEVP

Use: return of the overall sample standard deviation. It reflects the overall sample compared with the average (mean) the degree of dispersion.

Syntax: STDEVP (number1, number2 ,…)

Parameters: Number1, number2, … for the overall sample corresponds to the 1-30 parameters. Can use comma-separated form of parameters, you can use a single array, that is, the reference cell array.

Note: STDEVP function ignored in the calculation of the process of logical values (TRUE or FALSE) and text. If logical values and text can not be ignored, should be used STDEVPA function.

STDEVP function at the same time assuming that the overall parameters for the entire sample. If the data in a representative sample of the overall sample, STDEV function should be used to calculate the standard deviation. When the sample size were more, STDEV function and STDEVP small difference between the calculated results.

Example: If a second examination, only five students to participate in, score of A1 = 78, A2 = 45, A3 = 90, A4 = 12, A5 = 85, then calculated the standard deviation of all the formula “= STDEVP (A1: A5) “, the returned results equivalent to 29.52287249.

68.STDEVPA

Purposes: the calculation of standard deviation of the overall sample. STDEVP function it is the difference between the value of the text and logical values (TRUE or FALSE) to participate in the calculation.

Syntax: STDEVPA (value1, value2 ,…)

Parameters: value1, value2, … as a sample of the overall parameters of 1-30. Can use comma-separated form of parameters, you can use a single array (that is, cells of the array reference).

Note: STDEVPA function parameters for the assumption that the overall sample. If the data represent part of the overall sample, you must use STDEVA function to estimate the standard deviation.

Example: If a second examination, only five students to participate in, score of A1 = 78, A2 = 45, A3 = 90, A4 = 12, A5 = 85, then calculated the standard deviation of all the formula “= STDEVP (A1: A5) “, the returned results equivalent to 29.52287249.

69.STEYX

Purposes: to return through the linear regression method to calculate the forecast value of y generated by the standard error. According to the standard error of measurement used to calculate a single variable x in y amount of predictive value of the error.

Syntax: STEYX (known_y’s, known_x’s)

Parameters: Known_y’s data points as the dependent variable group or region, Known_x’s data points as variables or regional group.

Example: the formula “= STEYX ((22,13,29,19,18,17,15), (16,25,11,17,25,14,17))” return to 4.251584755.

70.TDIST

Purposes: student’s t-returned to the distribution of percentage points (probability), t distribution value (x) is the calculated t value (the calculation of its percentage points). t distribution for small sample data set of hypothesis testing, the use of this function can replace the critical value of t distribution table.

Syntax: TDIST (x, degrees_freedom, tails)

Parameters: X is the number of distribution requirements, Degrees_freedom integer degree of freedom to express, Tails of the distribution function specified in the return is a single-tailed or two-tailed distribution. If tails = 1, function return TDIST one-tailed distribution. If tails = 2, function to return to two-tailed distribution TDIST.

Example: the formula “= TDIST (60,2,1)” to return to .000138831.

71.TINV

Purposes: to return as a function of probability and the degree of freedom of the student’s t value of t distribution.

Syntax: TINV (probability, degrees_freedom)

Parameters: Probability for the corresponding two-tailed Student’s-t probability distribution, Degrees_freedom for the distribution of the degrees of freedom.

Example: the formula “= TINV (0.5,60)” to return to .678600713.

72.TREND

Purposes: to return to a linear regression line fitting a set of vertical coordinates (y value). Find that a given array known_y’s and known_x’s a straight line (least square method), and return to the specified array new_x’s value in a straight line on the corresponding y value.

Syntax: TREND (known_y’s, known_x’s, new_x’s, const)

Parameters: Known_y’s for the known relationship between the y = mx + b in the y value of the collection, Known_x’s for the known relationship between the y = mx + b in the optional set of x values, New_x’s need TREND function to return to the corresponding y value of the new x value , Const specified for the logical value of the constant term b is forced to 0.

73.TRIMMEAN

Purposes: to return to the internal data set on average. TRIMMEAN function from a data set of the head and tail to remove a certain percentage of data points, and then the average demand. When the hope that removed part of the analysis of calculation data, you can use this function.

Syntax: TRIMMEAN (array, percent)

Parameters: Array for the need for screening and for the average of the array or data region, Percent for the purpose of calculating when to remove the proportion of data points. If percent = 0.2, while removal of 20 data 4, that is, remove the two rear head removed 2. Percent = 0.1,30 if data points equal to 10 percent of the three data points. TRIMMEAN function will be symmetric in the data set to remove the head and tail of the data.

Example: If A1 = 78, A2 = 45, A3 = 90, A4 = 12, A5 = 85, while the formula “= TRIMMEAN (A1: A5, 0.1)” back to 62.

74.TTEST

Purposes: to return to s-t-student test associated probabilities. It can determine whether two samples come from two with the same overall average.

Syntax: TTEST (array1, array2, tails, type)

Parameters: Array1 is the first data set, Array2 is the second data set, Tails of the mantissa distribution curve specified. If tails = 1, TTEST function using one-tailed distribution. If tails = 2, TTEST function using two-tailed distribution. Type for the type of t test. If the type equivalent (1,2, 3) test (paired, two-sample test, such as variance, heteroscedastic two-sample test)

Example: the formula “= TTEST ((3,4,5,8,9,1,2,4,5), (6,19,3,2,14,4,5,17,1), 2,1 ) “to return to 0.196016.

75.VAR

Purposes: to estimate sample variance.

Syntax: VAR (number1, number2 ,…)

Parameters: Number1, number2, … correspond to the overall sample and 1-30 parameters.

Example: Suppose a particular examination taken in the five scores as a random sample, estimation results using VAR variance function, the sample value of A1 = 78, A2 = 45, A3 = 90, A4 = 12, A5 = 85 , then the formula “= VAR (A1: A5)” to return to 1089.5.

76.VARA

Uses: used to estimate the variance of a given sample. VAR function with the difference between the text and logical values (TRUE and FALSE) will also participate in the calculation.

Syntax: VARA (value1, value2 ,…)

Parameters: value1, value2, … as a general sample parameters 1-30.

Example: Suppose a particular examination taken in the five scores as a random sample, estimation results using VAR variance function, the sample value of A1 = 78, A2 = 45, A3 = 90, A4 = 12, A5 = 85 , then the formula “= VARA (A1: A5, TRUE)” to return to 1491.766667.

77.VARP

Use: calculation of the overall sample variance.

Syntax: VARP (number1, number2 ,…)

Parameters: Number1, number2, … for the overall sample corresponds to the 1-30 parameters. The logic values (TRUE and FALSE) and the text will be ignored.

Example: If a second make-up, only five students to participate in, score of A1 = 88, A2 = 55, A3 = 90, A4 = 72, A5 = 85, with results VARP variance function estimation, then the formula “= VARP (A1: A5) “back to 214.5.

78.VARPA

Use: calculation of the overall sample variance. VARP function with the difference between the text and logical values (TRUE and FALSE) will also participate in the calculation.

Syntax: VARPA (value1, value2 ,…)

Parameters: value1, value2, … as a sample of the overall parameters of 1-30.

Example: If a second make-up, only five students to participate in, score of A1 = 88, A2 = 55, A3 = 90, A4 = 72, A5 = 85, with results VARPA variance function estimation, then the formula “= VARPA (A1: A5) “back to 214.5.

79.WEIBULL

Purposes: the distribution of the return of Webber. Use this function can be reliability analysis, such as equipment MTBF.

Syntax: WEIBULL (x, alpha, beta, cumulative)

Parameters: X is used to calculate the value function, Alpha parameter distribution, Beta distribution parameters, Cumulative specified in the form of function.

Example: the formula “= WEIBULL (98,21,100, TRUE)” to return to 0.480171231, = WEIBULL (58,11,67, FALSE) to return to .031622583.

80.ZTEST

Purposes: to return to the two-tailed z test P value. Z test based on data sets or to generate x array of standard scores, and the return of the two-tailed probability distribution. You can use this function to return samples from a specific observation in the overall value of the likelihood estimation.

Syntax: ZTEST (array, x, sigma)

Parameters: Array for x as a test region of the array or data. X value to be tested. Sigma as a whole (known) standard deviation, if omitted, use the sample standard deviation.

Example: the formula “= ZTEST ((3,6,7,8,6,5,4,2,1,9), 4)” return 0.090574.

Application of Excel function tutorials: Statistical functions (c)

41.MAX

Purposes: to return to the greatest concentration of numerical data.

Syntax: MAX (number1, number2 ,…)

Parameters: Number1, number2, … is the need to find the greatest value of 1-30 values.

Example: If A1 = 71, A2 = 83, A3 = 76, A4 = 49, A5 = 92, A6 = 88, A7 = 96, while the formula “= MAX (A1: A7)” to return to 96.

42.MAXA

Purposes: to return to the greatest concentration of numerical data. With MAX value of the difference between the text and logical values (such as TRUE and FALSE) as a figure involved in the calculation.

Syntax: MAXA (value1, value2 ,…)

Parameters: value1, value2, … need to find the greatest value from the 1-30 parameters.

Example: If A1: A5 contains 0,0.2,0.5,0.4 and TRUE, then: MAXA (A1: A5) return 1.

43.MEDIAN

Purposes: to return to a given set of median values (it is in a group of data in the middle of a few. In other words, in this set of data, over half of the data than it is large, more than half of the data it small) .

Syntax: MEDIAN (number1, number2 ,…)

Parameters: Number1, number2, … is the need to find the median number of 1-30 parameters.

Examples: MEDIAN (11,12,13,14,15) return 13; MEDIAN (1,2,3,4,5,6) to return to 3.5, that is, the average of 3 and 4.

44.MIN

Purposes: to return to the table to set the minimum parameters.

Syntax: MIN (number1, number2 ,…)。

Parameters: Number1, number2, … are found from the minimum number of 1-30 parameters.

Example: If A1 = 71, A2 = 83, A3 = 76, A4 = 49, A5 = 92, A6 = 88, A7 = 96, while the formula “= MIN (A1: A7)” return 49; and = MIN (A1 : A5, 0, -8) return -8.

45.MINA

Purposes: to return to the list of parameters of the minimum value. MIN function with the difference between the value of the text and logical values (such as TRUE and FALSE) are also calculated as the number of participation.

Syntax: MINA (value1, value2 ,…)

Parameters: value1, value2, … need to find the smallest value from 1-30 parameters.

Example: If A1 = 71, A2 = 83, A3 = 76, A4 = 49, A5 = 92, A6 = 88, A7 = FALSE, then the formula “= MINA (A1: A7)” return 0.

46.MODE

Purposes: to return to the array or data in a particular area of the plural.

Syntax: MODE (number1, number2 ,…)。

Parameters: Number1, number2, … are calculated for the plural 1-30 parameters.

Example: If A1 = 71, A2 = 83, A3 = 71, A4 = 49, A5 = 92, A6 = 88, while the formula “= MODE (A1: A6)” to return to 71.

47.NEGBINOMDIST

Purposes: to return to the negative binomial distribution. When the success probability is constant probability_s, the function return NEGBINOMDIST successful in number_s prior to arrival, there number_f times the probability of failure. This function is similar with the binomial distribution, but its success is the number of fixed, test the total number of variables. With the binomial distribution is similar to test number was assumed to be independent variables.

Syntax: NEGBINOMDIST (number_f, number_s, probability_s)

Views Number_f is a failure, Number_s critical for the success of the number, Probability_s is the probability of success.

Example: If looking for 10 people who have reacted swiftly, and the known characteristics of the candidates have such a probability of 0.3. Then, to find qualified candidates 10 months before the need for qualified candidates for the probability of the formula “= NEGBINOMDIST (40,10,0.3)”, the calculation result is 0.007723798.

48.NORMDIST

Purposes: to return to a given mean and standard deviation of the cumulative normal distribution function.

Syntax: NORMDIST (x, mean, standard_dev, cumulative)

Parameters: X is used to calculate the normal distribution function of the interval point, Mean is the arithmetic mean of the distribution, Standard_dev is the distribution of standard deviation; Cumulative is a logical value, specified in the form of function. If cumulative is TRUE, then return to the cumulative distribution function NORMDIST function; If FALSE, the return probability density function.

Example: the formula “= NORMDIST (46,35,2.5, TRUE)” to return to .999994583.

49.NORMSINV

Purposes: to return the cumulative standard normal distribution function of the inverse function. The distribution of the average 0, standard deviation of 1.

Syntax: NORMSINV (probability)

Parameters: Probability is the probability distribution.

Example: the formula “= NORMSINV (0.8)” to return to .841621386.

50.NORMSDIST

Purposes: to return the cumulative standard normal distribution function, the distribution of average 0, standard deviation of 1.

Syntax: NORMSDIST (z)

Parameters: Z for the need to calculate the distribution of values.

Example: the formula “= NORMSDIST (1.5)” the results of the calculation of 0.933192771.

51.NORMSINV

Purposes: to return the cumulative standard normal distribution function of the inverse function. The distribution of the average 0, standard deviation of 1.

Syntax: NORMSINV (probability)

Parameters: Probability is the probability distribution.

Example: the formula “= NORMSINV (0.933192771)” to return to 1.499997779 (ie 1.5).

52.PEARSON

Purposes: to return to Pearson (Napier Health) the product moment correlation coefficient r, it is a range of between -1.0 to 1.0 (including -1.0 and 1.0 inclusive) of the non-dimensional index, reflecting the data collection between the two linear relevance.

Syntax: PEARSON (array1, array2)

Parameters: Array1 set as variables, Array2 set as the dependent variable.

Example: If A1 = 71, A2 = 83, A3 = 71, A4 = 49, A5 = 92, A6 = 88, B1 = 69, B2 = 80, B3 = 76, B4 = 40, B5 = 90, B6 = 81 , then the formula “= PEARSON (A1: A6, B1: B6)” to return to 0.96229628.

53.PERCENTILE

Purposes: to return to the K value of regional numerical point percentage. For example, examinations to determine the top 80 percentage points in the scores.

Syntax: PERCENTILE (array, k)

Parameters: Array for the definition of the relative positions of the array or the numerical values of regional, k for the array needs to be the value of their ranking.

Example: If a sub-test scores for A1 = 71, A2 = 83, A3 = 71, A4 = 49, A5 = 92, A6 = 88, while the formula “= PERCENTILE (A1: A6, 0.8)” to return to 88, that is, examination In order to place more than 80 percentage points, while scores of at least 88 points should be.

54.PERCENTRANK

Purposes: to return a value in a data set as a percentage of qualifying can be used to view data in the data location. For example, a calculation of scores of examination results in all position.

Syntax: PERCENTRANK (array, x, significance)

Parameters: Array for each other to determine the relative position of the data set, X for which the values need to be qualifying, Significance is optional, that the percentage of the value of the return of the median effective. If omitted, function to retain three decimal PERCENTRANK.

Example: If a sub-test scores for A1 = 71, A2 = 83, A3 = 71, A4 = 49, A5 = 92, A6 = 88, while the formula “= PERCENTRANK (A1: A6, 71)” The results of the 0.2 , that is, 71 points in six scores ranked 20%.

55.PERMUT

Purposes: to return from a given number of selected elements of the collection with a few number of elements.

Syntax: PERMUT (number, number_chosen)

Parameters: Number for the total number of elements, Number_chosen each with the number of elements.

Example: If a lottery number 9 number, each number ranging from 0-9 (including 0 and 9). With the number of all possible use of the formula “= PERMUT (10,9)” calculation, the result is 3628800.

56.POISSON

Purposes: to return to Poisson distribution. Poisson distribution is usually a period of time used to predict the number of incidents, such as within a minute through the toll booths of the number of cars.

Syntax: POISSON (x, mean, cumulative)

Parameters: X is a number of events, Mean is the expected value, Cumulative probability of return in order to determine the logic value of the distribution of the form.

Example: the formula “= POISSON (5,10, TRUE)” to return to 0.067085963, = POISSON (3,12, FALSE) to return to .001769533.

57.PROB

Purposes: to return to a group the probability of the incident falls within the designated area of the incident and the corresponding probability.

Syntax: PROB (x_range, prob_range, lower_limit, upper_limit)

Parameters: X_range is a probability value of their respective x values of regional, Prob_range with the numerical x_range corresponding to a set of probability values, Lower_limit for the numerical calculation of the probability of lower bound of summation, Upper_limit is used to calculate the probability summation of optional upper bound value.

Example: the formula “= PROB ((0,1,2,3), (0.2,0.3,0.1,0.4), 2)” back to 0.1, = PROB ((0,1,2,3), (0.2,0.3 , 0.1,0.4), 1,3) return 0.8.

58.QUARTILE

Purposes: to return a set of data points quartile. Quartile test scores are commonly used in data such as the overall group, such as a group obtained scores in the top 25% of the scores.

Syntax: QUARTILE (array, quart)

Parameters: Array quartile for the need to find the value of the array or the number of reference regions, Quart decided to return the value of which quartile. If qurart check 0,1, 2,3 or 4, QUARTILE function is to return to the minimum, first quartile (25th percentile ranking), middle of the median (50th percentile ranking), the third quartile (75th percentile ranking) and the maximum value.

Example: If A1 = 78, A2 = 45, A3 = 90, A4 = 12, A5 = 85, while the formula “= QUARTILE (A1: A5, 3)” to return to 85.

59.RANK

Purposes: to return a numeric value in a group of ranking (if the data list has been ranked the sequence, then the value of the current ranking is its location).

Syntax: RANK (number, ref, order)

Parameters: Number is the need to calculate the ranking of a number; Ref is an array contains a set of figures or references (including the non-value-type parameters are ignored); Order for a number of ways specified in qualifying. If the order is 0 or omitted, according to the data in descending order of ranking list. If the order is not zero, ref as the data in ascending order according to the list of qualifying.

NOTE: RANK function to repeat the same numerical ranking. However, the existence of duplication of the number of follow-up will affect the ranking values. If an integer, if an integer 60 appears twice, the ranking of 5, then 61 qualifying for the 7 (not ranking the value of 6).

Example: If A1 = 78, A2 = 45, A3 = 90, A4 = 12, A5 = 85, while the formula “= RANK (A1, $ A $ 1: $ A $ 5)” to return to 5,8,2,10,4 .

60.RSQ

Purposes: to return to the set of data points of Pearson product moment correlation coefficient of the square.

Syntax: RSQ (known_y’s, known_x’s)

Parameters: Known_y’s for an array or data region, Known_x’s also an array or data.

Example: the formula “= RSQ ((22,23,29,19,38,27,25), (16,15,19,17,15,14,34))” return .013009334.

Application of Excel function tutorials: Statistical functions (b)

21.FINV

Purposes: to return to F of the inverse probability distribution function, that is, the critical value of F distribution. If p = FDIST (x, …), while FINV (p, …) = x.

Syntax: FINV (probability, degrees_freedom1, degrees_freedom2)

Parameters: Probability is the probability of the cumulative value of F distribution, Degrees_freedom1 molecular degrees of freedom, Degrees_freedom2 is the denominator degrees of freedom.

Example: the formula “= FINV (0.1,86,74)” to return to 1.337888023.

22.FISHER

Purposes: to return to point x of the Fisher transform. Generate a transformation similar to the normal distribution rather than a function of skew, use this function to complete the assumption that the correlation coefficient test.

Syntax: FISHER (x)

Parameters: X for a number of points in the transform.

Example: the formula “= FISHER (0.55)” to return to .618381314.

23.FISHERINV

Purposes: to return to the inverse Fisher transform function value, if y = FISHER (x), then FISHERINV (y) = x. Transform the above-mentioned data can be analyzed or the correlation between array.

Syntax: FISHERINV (y)

Parameters: Y as a value point in the inverse transform.

Example: the formula “= FISHERINV (0.765)” to return to .644012628.

24.FORECAST

Uses: In accordance with a linear regression line fit to return to a predictive value. This function can be used for future sales, demand or inventory to predict consumer trends.

Syntax: FORECAST (x, known_y’s, known_x’s).

Parameters: X to the needs of the data points to predict the X coordinate (variable value). Known_y’s is a straight line to meet the linear fit y = kx + b of the point set of a group of selected known y values, Known_x’s is a straight line to meet the linear fit y = kx + b of the point set of a group of elected known x value.

Example: the formula “= FORECAST (16, (7,8,9,11,15), (21,26,32,36,42))” return to 4.378318584.

25.FREQUENCY

Purposes: to return to a vertical array of data in a regional frequency distribution. It can be calculated in a given range and reception range of each interval contains the number of data.

Syntax: FREQUENCY (data_array, bins_array)

Parameters: Data_array is used to calculate the frequency of an array, or array element reference region. Bins_array receive interval data for a region array or array reference, set the frequency of data_array sub-point calculations.

26.FTEST

Purposes: to return to the results of F tests. It returned when the array 1 and array 2 had no significant variance between the single-tail probability, can determine whether the variance of the two different samples. For example, two classes are given the same subject area examinations in order to test whether there are differences.

Syntax: FTEST (array1, array2)

Parameters: Array1 is the first array or data region, Array2 is the second array, or data.

Example: If A1 = 71, A2 = 83, A3 = 76, A4 = 49, A5 = 92, A6 = 88, A7 = 96, B1 = 59, B2 = 70, B3 = 80, B4 = 90, B5 = 89 , B6 = 84, B7 = 92, while the formula “= FTEST (A1: A7, B1: B7)” to return to .519298931.

27.GAMMADIST

Purposes: to return to the gamma distribution. It can be used with a skewed distribution of study variables used in queuing analysis.

Syntax: GAMMADIST (x, alpha, beta, cumulative).

Parameters: X for the gamma distribution used to calculate the value, Alpha is the distribution of the parameters γ, Betaγ distribution of a parameter. If beta = 1, GAMMADIST function returns the standard gamma distribution. Cumulative is a logical value, the form of a decision function. If cumulative is TRUE, GAMMADIST function returns the cumulative distribution function; If FALSE, the return probability density function.

Example: the formula “= GAMMADIST (10,9,2, FALSE)” equivalent to the calculation 0.032639, = GAMMADIST (10,9,2, TRUE) return 0.068094.

28.GAMMAINV

Purposes: to return with a given probability distribution of the gamma-range point, there used to study the distribution of skewed variables. If P = GAMMADIST (x ,…), then GAMMAINV (p ,…)= x.

Syntax: GAMMAINV (probability, alpha, beta)

Parameters: Probability distribution for the probability of gamma value, Alphaγ distribution parameters, Betaγ distribution parameters. If beta = 1, return to the standard gamma function distribution GAMMAINV.

Example: the formula “= GAMMAINV (0.05,8,2)” to return to 7.96164386.

29.GAMMALN

Purposes: to return to the gamma function of the natural logarithm of Γ (x).

Syntax: GAMMALN (x)

Parameters: X to the needs of the numerical calculation GAMMALN function.

Example: the formula “= GAMMALN (6)” to return to 4.787491743.

30.GEOMEAN

Purposes: to return to positive data array or the geometric mean. Variable can be used to calculate the average growth rate of compound interest.

Syntax: GEOMEAN (number1, number2 ,…)

Parameters: Number1, number2, … for the need to calculate the average of 1-30 parameters, in addition to the use of the form of comma-separated values can also use the array or array reference.

Example: the formula “= GEOMEAN (1.2,1.5,1.8,2.3,2.6,2.8,3)” The result of this calculation is 2.069818248.

31.GROWTH

Purposes: to set the data value of the forecast growth index. According to the known x values and y values, GROWTH function to return a new set of x values corresponding y values. GROWTH fitting function used to satisfy a given x value and y value of the index curve.

Syntax: GROWTH (known_y’s, known_x’s, new_x’s, const)

Parameters: Known_y’s index return is to meet the curve y = b * m ^ x of a group known y values; Known_x’s index return is to meet the curve y = b * m ^ x of a set of known values of x collection (optional parameters); New_x’s is a new set of x values, GROWTH function can return to their corresponding y values; Const is a logical value specifying whether to set a coefficient b mandatory, if const is TRUE or omitted, b will be involved in the normal calculation. If const is FALSE, b will be set to 1, m values will be made to adjust y = m ^ x.

32.HARMEAN

Purposes: to return to the harmonic average of the data collection. Countdown to reconcile with the arithmetic average of the average of each countdown. Reconcile the total is less than the average of the geometric mean, and geometric mean less than the arithmetic average of the total.

Syntax: HARMEAN (number1, number2 ,…)

Parameters: Number1, number2, … is the need to calculate their average parameters 1-30. Can use comma-separated form of parameters, you can also use the array or array reference.

Example: the formula “= HARMEAN (66,88,92)” to return to 80.24669604.

33.HYPGEOMDIST

Purposes: to return to hypergeometric distribution. A given sample size, sample volume and sample the overall success of the overall number of, HYPGEOMDIST function to return to sample the frequency of a given probability of success.

Syntax: HYPGEOMDIST (sample_s, number_sample, population_s, number_population)

Parameters: Sample_s for the success of the number of samples, Number_sample for the sample size. Population_s for the whole sample the number of successful, Number_population capacity for the overall sample.

Example: If a class has 42 students. Of which 22 were male and 20 were women. 6 If the randomly selected people, then one of just three girls in the probability formula is: “= HYPGEOMDIST (3,6,20,42)”, return to the results of 0.334668627.

34.INTERCEPT

Use: Using the known x value and y value of a straight line with the y-axis intercept. When the known variables to zero, the intercept can be obtained using the value of the dependent variable.

Syntax: INTERCEPT (known_y’s, known_x’s)

Parameters: Known_y’s dependent variable is a set of data or data sets, Known_x’s argument is a set of data or data sets.

Example: If A1 = 71, A2 = 83, A3 = 76, A4 = 49, A5 = 92, A6 = 88, A7 = 96, B1 = 59, B2 = 70, B3 = 80, B4 = 90, B5 = 89 , B6 = 84, B7 = 92, while the formula “= INTERCEPT (A1: A7, B1: B7)” to return to 87.61058785.

35.KURT

Purposes: to return to the peak data set. It reflects a comparison with the normal distribution of the degree of sharp or flat, is that the relatively sharp peak of the distribution, the negative peak of the distribution that is relatively flat.

Syntax: KURT (number1, number2 ,…)

Parameters: Number1, number2, … for the need to calculate the peak parameters 1-30. They can use the form of comma-separated parameters, you can use a single array, that is, the reference cell array.

Example: If a particular student test scores for A1 = 71, A2 = 83, A3 = 76, A4 = 49, A5 = 92, A6 = 88, A7 = 96, while the formula “= KURT (A1: A7)” to return to -1.199009798 shows the relative performance of the normal distribution is a relatively flat distribution.

36.LARGE

Purposes: to return to a focus on a maximum value of data. Query can use the LARGE function test scores focus on first, second, third, etc. scores.

Syntax: LARGE (array, k)

Parameters: Array for the need to query the first k-value of the array or data region, K for the return value or data in the array position in the range (ie, ranking).

Examples: If B1 = 59, B2 = 70, B3 = 80, B4 = 90, B5 = 89, B6 = 84, B7 = 92,, the formula “= LARGE (B1, B7, 2)” back to 90.

37.LINEST

Uses: Use the least square method known data on the best fitting straight line and return to describe the linear array.

Syntax: LINEST (known_y’s, known_x’s, const, stats)

Parameters: Known_y’s is the expression y = mx + b in the y value of the collection known, Known_x’s is the relationship between the expression y = mx + b in the known set of optional x value, Const is a logical value specifying whether or not mandatory to make constant b is 0, if const is TRUE or omitted, b will be involved in the normal calculation. If const is FALSE, b will be set to 0, and at the same time making the adjustment m value y = mx. Stats is a logical value specifying whether or not to return additional regression statistics. If stats is TRUE, the return of function LINEST additional regression statistics. If stats is FALSE or omitted, only to return LINEST function m and the constant term coefficient b.

Example: If A1 = 71, A2 = 83, A3 = 76, A4 = 49, A5 = 92, A6 = 88, A7 = 96, B1 = 59, B2 = 70, B3 = 80, B4 = 90, B5 = 89 , B6 = 84, B7 = 92, the array formula “(= LINEST (A1: A7, B1: B7))” return -0.174244885, -0.174244885, -0.174244885, -0.174244885, -0.174244885, -0.174244885, -0.174244885.

38.LOGEST

Uses: In the regression analysis, the calculation of the best observational data group index regression curve fitting, and return to describe the curve of the array.

Syntax: LOGEST (known_y’s, known_x’s, const, stats)

Parameters: Known_y’s are a group of line with the y = b * m ^ x function of the y value of the collection, Known_x’s are a group of line with the y = b * m ^ x computing the relationship between the value of the optional x collection, Const is to specify whether you want to set up regular the number of b logical value of 1 if the const is set to TRUE or omitted, then the constant term b will be obtained by calculating.

Example: If a company’s sales of new products, exponential growth, followed by A1 = 33100, A2 = 47300, A3 = 69000, A4 = 102000, A5 = 150000 and A6 = 220000, while B1 = 11, B2 = 12, B3 = 13, B4 = 14, B5 = 15, B6 = 16. Using the array formula “(= LOGEST (A1: A6, B1: B6, TRUE, TRUE))”, in C1: D5 cells be the result of the calculation is: 1.463275628, 495.3047702,0.002633403,0.035834282,0.99980862,0.011016315,20896.8011 , 4, 2.53601883 and 0.000485437.

39.LOGINV

Purposes: to return x to the cumulative log-normal distribution function of the inverse function, where the ln (x) are those which are mean (average) and standard-dev (standard deviation) parameters of the normal distribution. If p = LOGNORMDIST (x ,…), then LOGINV (p ,…)= x.

Syntax: LOGINV (probability, mean, standard_dev)

Parameters: Probability is a log-normal distribution associated with the probability, Mean for ln (x) the average, Standard_dev for ln (x) the standard deviation.

Example: the formula “= LOGINV (0.036,2.5,1.5)” to return to .819815949.

40.LOGNORMDIST

Purposes: to return x to the cumulative log-normal distribution function, in which ln (x) is subject to parameters of the normal distribution mean and standard_dev. Use this function can be analyzed after a logarithmic transformation of data.

Syntax: LOGNORMDIST (x, mean, standard_dev)

Parameters: X is used to calculate the value function, Mean is the ln (x) the average, Standard_dev is ln (x) the standard deviation.

Example: the formula “= LOGNORMDIST (2,5.5,1.6)” to return to .001331107.

Excel Function Application Tutorial: Statistical Functions

1.AVEDEV

Purposes: to return a set of data with the average absolute deviation of the average, this function can be measured data (for example, a student examination results) of the dispersion.

Syntax: AVEDEV (number1, number2 ,…)

Parameters: Number1, number2, … are used to calculate the average absolute deviation of a set of parameters, the number can be between 1 to 30.

Example: If A1 = 79, A2 = 62, A3 = 45, A4 = 90, A5 = 25, while the formula “= AVEDEV (A1: A5)” to return to 20.16.

2.AVERAGE

Purposes: the calculation of the arithmetic mean of all parameters.

Syntax: AVERAGE (number1, number2 ,…)。

Parameters: Number1, number2, … is to calculate the average of 1 to 30 parameters.

Example: If A1: A5 region named Score, in which numerical 100,70,92,47 and 82, respectively, then the formula “= AVERAGE (Score)” to return to 78.2.

3.AVERAGEA

Purposes: the list of calculated parameters of the average value. AVERAGE function with not only the difference between the figures, and text and logical values (such as TRUE and FALSE) are also involved in the calculation.

Syntax: AVERAGEA (value1, value2 ,…)

Parameters: value1, value2, … as the need to calculate the average of the 1-30 cells, or numerical range.

Example: If A1 = 76, A2 = 85, A3 = TRUE, the formula “= AVERAGEA (A1: A3)” to return to 54 (that is, 76 +85 +1 / 3 = 54).

4.BETADIST

Purposes: to return to the cumulative distribution function Beta function. Beta cumulative distribution function is usually used to study samples of the collection of certain things and the changes occurred. For example, it is time to watch television a day rate.

Syntax: BETADIST (x, alpha, beta, A, B)

Parameters: X used for calculating the value function to be living in an optional upper and lower bounds of (A and B) between. Alpha distribution parameters. Beta distribution parameters. A value x is the optional lower bound of their interval, B is the numerical range of x-owned optional upper bound.

Example: the formula “= BETADIST (2,8,10,1,3)” to return to .685470581.

5.BETAINV

Purposes: to return to the cumulative beta distribution function of the inverse function. That is, if the probability = BETADIST (x ,…), then BETAINV (probability ,…)= x. beta cumulative distribution function can be used for project design, completion expected in the given time and changes in the parameters, the simulation of possible completion time.

Syntax: BETAINV (probability, alpha, beta, A, B)

Parameters: Probability distribution for the Beta probability value, Alpha parameter distribution, Beta distribution of parameters, A value x of the optional lower bound of their interval, B Numerical x-owned sector, the range of options.

Example: the formula “= BETAINV (0.685470581,8,10,1,3)” to return to 2.

Read the rest of this entry »

insert powerpoint into word

excel summation

In the use of Excel, we can use the excel summation function, can greatly enhance the efficiency of our work, the specific methods used are as follows:

(1) how to seek out data and. To assume that cell A2 to A6 of the data and fill in A7 cells, the operation is as follows:

First selected cell A7, double-click the Edit column “=”, then double-click the toolbar】 【common summation symbol “Σ”; then use the mouse to click the cell B2 and has been drag to B5, the election B2_B5 in the whole region, then in the edit field and B6 can be seen from the formula “= sum (B2: B5)”, click the Edit column “√” (or press Enter button) to confirm that, the formula to establish the end. At this point B2 to B5 in the cell of arbitrary input data, and immediately they will appear in the cells in B6.

(2) how to seek and peer data. If you want cell B2 to D2 and enter the data in cell E2, is similar, but attention should be paid to horizontal operation: the establishment of the cell formula (the case of E2) 【unit must Format Cells dialog box】 【】 to select the level of alignment “conventional” manner, so that the cell shown in the formula will not affect the next cell.

(3) how to seek a separate line (or column) of data and. If you want cell B2, C5 and D4 of the data and fill in E6, the operation is as follows:

First selected cell E6, double-click the Edit column “=”, then double-click the toolbar】 【common summation symbol “Σ”; then click the cell B2, type a comma “,” and click C5, type “,”, and then click the D4, then in the edit field and E6 can see the formula “= sum (B2, C5, D4)” confirmed that the establishment of finished formula.

Between two tables in the summation

Assume that n Zhang and schedule the same table format, if the location of these tables the same cell (for example, B2) all the data in aggregate, the results will fill in another form of B2 the same format, the operation is as follows:
Summary of the first selected cell B2, double-click the Edit column “=”, then double-click the toolbar】 【common summation symbol “Σ”; then click the tab Table 1, do not hold down the Shift Kin release, then click the tab Table n (Note: the tables have been aggregated should be included in tables 1 and between the location of n), release the Shift and click the cell Kin B2, this can be seen in the editorial column to the formula “= sum ( ‘Table 1: Table n’! B2)”, confirmed that the establishment of finished formula.

If you want table 1 to table n the corresponding cells to aggregate all the data in the corresponding matrix to the cell, you can use methods similar to Example 1, a one-time selection of the B2 matrix to the entire E6 rectangular area, the original cell B2 in the formula to create a copy from.

4 If the number of cases to vary the format of the table data (for example, Table 1 B3, Table 2 of the C5, Table 3 of the D3, Table 4, E4 … …) Summary table to another in cell B2, operation are as follows:

Summary of the first selected cell B2, double-click the Edit column “=”, then double-click the toolbar】 【common summation symbol “Σ”; then click the tab Table 1, and then click its cell B3, type “,”, and then click the tab Table 2 and its cell C5, type “,” … … and so on for each of the table tag and the selected cell 11, the final table after the cell type, “”, then edit the column appears in the formula is: “= sum (Table 1! B3, Table 2! C5, Table 3! D3, Table 4! E4 … …)”, confirmed formula to establish the end.

insert word document into powerpoint

Word is a commonly used word processing software, PowerPoint is a presentation software, we can through the following three ways to quickly switch to insert word document into powerpoint.

direct drag

1, start Word (other versions may also be, the same below), open the corresponding document.

2, start PowerPoint, a new blank presentation.

3, so that the two windows side by side with on the desktop.

4, in the Word window, select the text of the need to convert, and then drag to the PowerPoint slides in the editor window area, release the mouse, to copy text from instantly.

This method also applies to the Excel content in the transition to PowerPoint.

Rapid send

1, start Word, open the corresponding document.

2, the implementation of “File → Send → Microsoft PowerPoint” command, the system immediately start PowerPoint, and the establishment of a number of slides and Word documents in order to copy the text of each paragraph to one slide of each text box.

3, adjustments to the text box and click the text editor will do.

Tips: If you do not need the Word version will be removed, and then converted to the above-mentioned operation, the better.

use link

1, start PowerPoint2003, a new blank presentation.

2, the implementation of the “Insert → Object” command, open the “Insert Object” dialog box .

3, select “Create from file” option, and then click the “Browse” button, open the “Browse” dialog box .

4, positioning to be the conversion of Word documents where the angle between the document, select the corresponding document and click “OK” button to return to the “Insert Object” dialog box.

5, according to the “OK” button, the corresponding Word document instantly be inserted into the slide .

Tips: ① If the contents of Word document with a slide just enough to show that this method is more appropriate to use. ② Insert the Word document, although the picture format, but double-click the picture to make a Word states to amend the text. ③ If you select “Insert Object” dialog box the “link” option, and then insert the document in which the future of the Word document was revised again to start the presentation, the system will pop-up in Figure 4 shown in the prompt dialog box, click the “Update link” button to update the document in the Word document link.

Fill data in Excel to use

Office Excel 2003 in the use of the time, we may often use Excel 2003 to fill the data function to improve their work efficiency. Filled with features generally divided into two categories: First, a copy of the data; the other one is filling sequence. In this paper, we mainly on their use.

Method I: arithmetic sequence and fill copy

Method is commonly used cell type in the values, such as “1″, and then move the mouse to the lower right corner of the cell, the mouse into a bold “+” sign along the row or column and drag the mouse to a certain cells, release the mouse after the value “1″ copy to other cells. If the left side of the cell is a continuous region, double-click the mouse can also automatically copy the data to the last row of a cell; If you drag the process of holding down the Ctrl key, then arithmetic sequence filled way , such as “2,3,4 … …”;

Method 2: Continuous Copy

In two consecutive cell type “1,2″ after, mouse over the “2″ in the lower right corner, the mouse into a bold “+” sign, the drag the mouse to a cell, and so on will be poor way to fill sequence, such as “3,4,5,6″; If you drag the process of holding down the Ctrl key will be to reproduce the form of value copied to other cells, such as “1,2,1 , 2,1,2 “;

Method three: Use dialog box sequence

Enter values in the unit “1″, select “Edit” → “fill” → “sequence” in the pop-up dialog box can be done more choices. Can choose “sequence generated in the” rows or columns, you can choose the type of filler is the arithmetic or geometric, is a date or auto-fill, you can choose the date of the unit, based on our request to set the step value can be set up scheduled to fill the final size of a value, save a large area of trouble to choose;

Method Four: Right-drag sequence

Sequence dialog box provides more options, but need to select the menu can be implemented, so many times too cumbersome to use. In fact, this feature is also a shortcut. We also enter the cell value, will be moved to the mouse to drag the bottom right corner of the cell, but in the process of dragging the note, press and hold the right mouse button instead. Drag to a specific location right after the release, you can quickly pop-up menu to choose and sequence the same dialog box options. And an increase of whether or not filled with the format option, is not it much more convenient!

Method five: from the definition of sequence

Several methods described above, the main numerical sequence for the filling, then how does the text of the filling sequence, which involves a custom sequence. In Excel 2003, has built a number of text sequences, such as the common weeks, Heavenly Stems and Earthly Branches, such as. We enter “Monday”, the drag the mouse, you can appear “Tuesday, Wednesday …” and so on. This setting provides us with a great convenience, you can always use our name, department, classification methods such as self-defined sequence, enter only need to enter a phrase, drag the mouse can be realized after all the phrase filled. Sequence for custom text, select “Tools” → “Options” → “custom sequence” in the right sequence of the region can enter text, or directly from the worksheet in the selected region.

WORD and EXCEL spreadsheet table to share the first method

Today for everyone to gather to share some of the ways to form the first!

1, look at how to share Excel header:

Divided into two kinds of circumstances, one at the entry table in different table at the same time as the first, that is real, the method is very simple, such as your current SHEET1, you hold down the CTRL key while you click it SHEET2 2 At the same time, the selected table, you SHEET1 in the current input, after the completion of return. You can see the table SHEET1 and SHEET2 is the same as the first.
Another situation is not input, but different page printout of the table is the same as the first, the fact there are two ways to achieve, the first is under the File menu click Page Setup, switch to the tool table in the “Print the title of “choice of the top header line you want to share the first ranks of the table to print can be. The second way to judge the whole a bit mean. Also just set up this page to switch to the “header footer”, and then customize the header footer, hey is not the same effect?

If the only operation that can facilitate the sharing of the first watch, there was no need to print out, in fact, to tell you there are officeBA method can be used, by using the “Window” menu in the “frozen window”, specifically not to say, we operate about on the cicada. Kazakhstan.

2, then take a look at how to share the table the first word.

This is even more simple: to share the title selected, and then click menu “Table”, select “to repeat the header row” can be realized!

Table for everyone to share the first page of the long type is more convenient and practical it!

IE plug-ins page with rapid import Excel spreadsheet software

As the working relationship, I often need to be a number of pages to import Excel spreadsheet saved in the past, are a direct way to use copy, paste it into Excel but sometimes form in the not too satisfactory, after the need to change manually, trouble is, is no better way on it? course with, let HTML Table Extractor The IE plug-in tools to help you!

In IE open http://www.download.com/HTML-Table-Extractor/3000-2378_4-10462296.html this site, click on “Download Now” to download, after download, all the way “Next” to install or can be.

Step 1: Restart IE, then the toolbar would be more of “HTML Table Extractor” icon, when the need to import the form page, first click the icon.

Step 2: At this time the bottom of the window will pop up a small dialog box, click on “Search Table” button, and then move the mouse pointer to the form, the form will be red at this time, which said it has been selected, and then click the right mouse button, and then in the shortcut menu, select “Export to Excel → Create New Table” command.

Step 3: In this way, the page will be directly into the form in Excel and in the end be saved and you’re done.

EXCEL calculation error message

Error calculation

Reasons: Excel sometimes count also does not add and subtract. In fact, when entering numbers because Excel has the following agreement: If the use of “cell” command will have entered the number of cells as text, then Excel will remain as numerical data. If you enter a number in the cell before the text format as the future of the digital input is regarded as text, of such cells will be carried out in terms of summation error. For example, calculation of H1 = SUM (A1: G1), if A1: G1 For these reasons the region is set to text format, then H1 = 0.

Solution: The best use of the new worksheet to the number of air operations. Has been used as a worksheet, even if the original data has been deleted, but still retains its original formatting, may be regarded as the number of calculation errors caused by the text, and sometimes hard to find. However, there is a know-how (if listed as “the most appropriate column width” null and void after this Act), or text data by default left-justified, and the default value data for the right alignment, and so we can see whether there is any number of is set to the text.

Reasons: the formula or function or operator of the regional cell reference is not correct. Such as the use of SUM function on the A1: A5 and B1: B5 sum of the two regions, and, in applying the two did not cross the use of a cross-regional operator (space). Written in “SUM (A1: A5 B1: B5)” (Note: A5 and B1 spaces between) will be # NULL!ERROR s here: cross-operator region in order to prevent double counting when summing introduced. For example, the formula: “= SUM (A1: A3, A1: C1)” will repeat the sum cell A1 (plus two), while the formula “= SUM (A1: A3 A1: C1)” only to cell A1 for and one.

Solution: do not cross-sum of the region, such as not using the cross-operator operator, and to use a comma-separated regions summation. For example, the formula: “= SUM (A1: A5 B1: B5)” should be written: “= SUM (A1: A5, B1: B5)”.

Read the rest of this entry »

Change Excel default save directory

Usually after editing the document, during the “Save” operation, usually in the “Save As” dialog box “Save position” on, there is the “default” is set to “C: \ My Documents” folder . As a result there are two bad: First, C is usually set as a system tray area, this is often a “right and wrong” place, if too many documents stored here will certainly planted the seeds for the future; second is inconvenient classification of centralized management. 

Now introduce a method to change the save location: Start Excel, click “Tools → Options”, the “Options” dialog box, and then click the “General” tab, in the “default working directory” text box enter with Save Excel files in the default path.

Frequently Asked Questions Excel solution

1, How to quickly select a specific area?

Use F5 key to quickly select a specific area. For example, to select A2: A1000, the most simple way is to press F5 key, the “location” window, in the “reference” column enter the region need to select A2: A1000.

2, How to quickly return to check the region?

By Ctr + BacksPae (that is, backspace).

3, how to quickly navigate to the cell?

Method one: Press F5 key, the “location” dialog box, enter the reference column cells Skip To address, a single city “OK” button.

Method 2: Click the left column to edit the cell address box, enter the cell address.

4, “Ctrl + *” special function

In general, when dealing with a work table there are many forms of data through the selected cell of a table, and then press Ctrl + * key to select the entire table. Ctfl + * the selected region is determined: Based on the selected cell to the surrounding radiation data involved in the largest regional cell.

5. How to quickly select all worksheet cells that contain formulas?

Sometimes, the need for a worksheet that contains the formula for all the units in order to protect, or fill in with other cells of different colors to remind users to not have this color in the region of the input data.

6, how quickly different cells enter the same number of content?

Selected range, enter the value, and then press Ctrl + Ener key, can be realized in selected range in a one-time importation of the same value.

7, only remember the name of function, but do not remember the parameters of the function, how should I do?

If you know the function name to be used, but do not remember all the parameters of its format, then can use a keyboard shortcut to paste the parameters to edit the column.

The specific method is: In the editorial column then enter a function name later to equate, and then press Ctr + A button, Excel will automatically enter the “Function Guide - Step 2 of 2.” When using the easy-to-remember name and has a very long string of parameters of function, this method seems particularly useful.

8, how to select one or more cell drag and drop to a new location?

Hold down the Shift key can quickly modify the contents of the order of the cell.

The specific method is: the selected cell, press the Shift key, move the mouse pointer to the edge of the cell, until the emergence of drag-and-drop the arrow pointer (hollow arrow), and then press and hold the left mouse button to drag and drop. Drag the mouse up and down at the border between the cells will become a standard of “workers”-like logo, will be dragging around a vertical “workers”-shaped logo, release the mouse button to complete the operation, the selected one or more cells by drag and drop on to a new location.

9, how to make working space on the screen larger?

Can be used to hide the toolbar, you can maximize the Excel window, or in the “View” menu, select “full screen” command.

10, how to use pop-up menu?

Pop-up menu, including a number of operations in the most commonly used commands, using them can greatly improve the operational efficiency. Select a region first, and then click the right mouse Kin can pop-out menu, in accordance with the need to choose a different order of operations.

Back up your custom dictionary in Word

Learning in their daily work, most people will use the word of the text be edited, and in the use of the process, often there will be some special terminology or special characters, they are all correct, but Word will do a red wavy line markings to remind to the attention of users or modify such characters. Want to cancel this “unnecessary” to remind users that they need to be added to the dictionary.

Before reinstalling the system if the user failed to back up the custom dictionary, then the need to re-add, it is too cumbersome. In fact, word from the dictionary definition can be backed up, users only need to backup file: C: \ Documents and Settings \ current user name \ Application Data \ Microsoft \ Proof \ CUS TOM.DIC (the same directory if there are other dictionaries, also with backup).

Re-installed in the system, back up the above-mentioned documents will be copied to the original path, and then select “Tools - Options - Spelling and Grammar - Custom Dictionary”, click “Add”, add all the current directory under the custom dictionary ( *. DIC) can be.

EXCEL easily filter the telephone number of repeat

Now the hands of a large number of users Excel format telephone numbers of resources, the problem is selected to repeat the numbers, how should it operate?

Access to Excel, click menu - Data - Filter - Advanced Filter.

In the “Advanced Filter” dialog box, “a list of regional” and “the conditions of the region” are telephone numbers where the scope, at the same time to “choose not to repeat the record” in front of the box marked “√”, click “OK” .
And then select the entire table and copy to another empty table, the contents of the new table on the line with your request and in the end, delete the original table, OK.

In addition, we also can use the formula all the way out to repeat the phone tag. A hypothesis as a phone number, you can set out in the B input to the other blank out the formula “= IF (COUNTIF (A: A, A: A)> 1,” duplication “,” No Repeat “)”, and then down Copy this formula. To sort out on the B (descending), repeat the number on the display in front of.

Word 2007 create XML documentation

If it is from the word 2003 to upgrade to Word 2007, then may notice some changes on the XML. On the one hand, you will not be able to from the “XML document” the beginning of a new XML document template. Because Word 2007 does not contain “XML document” template.

Instead, “any” documents into XML documents. Simply select “office button” - “Save as” and “Save as type” set to “word XML document.” Attention to “Word 2003 XML Document” option. It has a “Word XML Document” option does not have the additional options. We talk about later.

When saved as a word XML (Word 2007) format, if you want to with the joint, please complete the save before, that is, use the “Tools” function of the “framework” tool, as in the “framework” part of the discussion. Can also open the XML document at the first application of the conversion, but because we are discussing here to create a new XML document, so this is not optional, unless the open and then save the file.

Once the assembly of the structure, “XML Structure” task pane will be displayed. Click the right to use the current document element of all these elements.

Click the task pane at the bottom of the “XML Options” to access additional options. Attention to “save data only” option, it does not work. Even if this option is chosen, the file is still generated by the wordXML filled.

Applications can choose to customize the conversion. A conversion can be a list, another conversion can be transferred back to generate a notification, then a notice of the scheduled items can be available now, and so on.

If the “Save data only” does not work, then how can we spin-off to obtain a more concise wordXML of XML documents? If the “office button” - “Save as” and “Save as type” is set to another XML option - “Word 2003 XML documents.” In doing so, additional options are available, including the “conversion application” and “data only.” At this point, “save data only” option that can really work (although that may save the file will be notified of this document constitutes a confusion).

Multiple Excel data table method together

Excel 2007 and reports in order to aggregate more than the result of a separate worksheet, you can work for each individual table of data together into a master worksheet. These worksheets with the main work table in the same workbook, but also in other workbooks. The data is the combination of data together in order to be able more easily to the data of regular or ad hoc basis to update and summary.

For example, if you have one for each regional office expenses worksheet data, use the combined data of these expenses into the company’s expenditure on the work table. The main worksheet that can be included in total sales across the enterprise and on average, the current inventory levels and product sales.

To the data together, please use the “Data” tab on the “Data Tools” group “together” command.

Together according to location - in chronological order according to the same table all the data and their location on the same

1, in a separate worksheet for each set of data to be combined.

How to set up data

To ensure that data used for each list (list: contains a series of data lines, or use the “Create a list of” command as a data table assigned to the function of a series of lines.) Format: the first line of each column has a label the same column contains similar data, and not in the list, blank lines or empty out.

, Respectively, each region will be placed in a separate worksheet. Do not put on any regional merger of the work table.

To ensure that each region have the same layout.

Name of each region: Select the entire region, and then in the “formula” tab of the “named cell” group, click “named range” next to the arrow, and then in the “Name” box, type in the region name.

2, contains the main work to show the merger of the table data range, click the upper left cell.

Note: to ensure that the cells in the lower right side to leave enough data to merge cells. “Together” command under the need to fill in the region.

3, in the “data” tab of the “Data Tools” group, click the “merger.”

4, in the “Function” box, click Microsoft office Excel data used to calculate the aggregate merger function (summary function: is a calculation of the type used in PivotTable or combined together form source data, or in the list or database to insert automatic subtotals. Examples of summary functions Sum, Count and Average.).

5, if the worksheet in another workbook, click the “Browse” to find documents, and then click “OK” to close the “Browse” dialog box.

In the “quote” box, enter the file path followed by an exclamation point.

6, type the name of the regional designation, and then click “Add.” To repeat this step for each region.

7, to determine together how to renew hope. Do one of the following:

To set up together, so that it changes the source data in the Automatic Updates, select “Create a link to the link source data” check box.

Points only when the worksheet is located in the middle of the other workbook in order to select the check box. Once you select this check box, then the merger can not be included in the calculation of the cell and regional change.

To set up together, so that you can change the calculation of the merger, including the cells and the region together to manually update, clear the “Create links to source data links” check box.

8, Please “tab position” under the box blank. Excel is not the source region of the row or column labels are copied to the combined calculation. If you need to merge the data for labeling, please copy from a source region or manually enter them their.

PowerPoint2007 slide to add the voice of non-stop

If you feel that the “illustrations” of the slide is no longer fresh, it may wish to add the voice of the slide, so that you can beautiful music to accompany a presentation under. However, if the only voices in the insert page, so that when the show to the next slide after the sound will stop, the adoption of the following settings so that the music can keep playing until the end of presentation.

PowerPoint2007 run and opened the PPT we need to edit the document, and then “Insert” tab, select the “Media Clips” - “the voice” - “the voice of the paper,” Open the “Insert Sound” dialog box to find that you want to add their own After the point of music files “to determine” (PowerPoint2003 in select “Insert → → Video and audio voice files”), in the following window if you select “click the” then only in the clicking of the mouse will be shown to play a sound So here, select “Automatic.”
In order to achieve the cycle need to be further set up to play, PowerPoint2003 documents in the right-click on the “little horn” choose “Edit Sound Object” and check “Loop until stopped”, PowerPoint2007 can be selected in the “election item “in check” Loop until stopped “, and also can set the volume, such as slide show. This method is well suited for soft slide adds the use of background music.

Copyright Microsoft Office tips | DBT Themes