How to use the COMBIN function in EXCEL forms

A, how to use the COMBIN function in EXCEL form

A, combin the syntax of the combining function

Function function: calculates the number of combinations of a number of objects from a given number of objects to extract a number of objects.

Using the function combin can determine the number of combinations of all possible combinations of a set of objects.

Syntax

combin(number,number_chosen)

number is the total number of objects.

numberchosen is the number of objects in each combination.

Description

The number parameter truncates and rounds.

If the parameter is non-numeric, the function combin returns the error value #value! If number Second, the combin function example

For example, to get from six players, selected two players to participate in the game, then, the total number of combinations, that is, the total number of possibilities?

We can use the function formula: =combin (6,2) will be able to get the number of combinations, the function returns 15;

That is to say, from the 6 players, selected two players to participate in the game, the total number of 15 kinds of collocation.

Below, and then a simple example:

There are three balls, respectively, red, green, blue, and now to select two balls from these three balls, *** how many kinds of collocation, combination?

Using the function can be obtained: = combin (3,2) function returns the result is 3;

Note that the combin function only returns the total number of combinations, did not give the specific in the end which kinds of combinations, the following, we artificially give the combination of the three balls such as the above program, respectively:

1, red, green 2, red, blue 3, green blue On these three kinds of. .

Third, the knowledge of combin extends

If you want to know or get the combination of each of the number of combinations in the way, have to use vba, the following provides several codes for your reference.

① Combination of combin(6,2)

sublistcombin()

dimx,yasinteger

forx=1to5

fory=x1to6

activecell.formular1c1=x &","&y

activecell.offset(1,0).select

nexty

nextx

endsub

② Combination of combin(8,6)

sublistcombin( )

dimh,i,j,k,l,masinteger

forh=1to3

fori=h1to4

forj=i1to5

fork=j1to6

forl=k1to7

form= l1to8

activecell.formular1c1=h&"-"&i&"-"&j&"-"&k&"-"&l&"-"&m

activecell.offset (1,0).select

nextm

nextl

nextk

nextj

nexti

nexth

endsub

Two, Project 2TheraBank bank loan Case

I. Case Background

TheraBank is a bank with a growing customer base. Most of the customers in this bank have deposits of different sizes. Since the number of customers in the loan business is very small, the bank wants to effectively convert the deposit users into loan users as a way of expanding the base of loan business volume in order to bring in more loan business and in the process, earn more through interest on loans.

So last year, the bank launched a campaign to convert deposit users who had not yet taken out a personal loan to take out a personal loan, and some of them have already done so through the campaign. At this point, the retail marketing department wants to develop a better strategy for targeting campaigns to increase the success rate with a minimal budget. The department would like to identify prospects who are more likely to purchase a loan, increase the conversion rate, and reduce the cost of marketing.

Second, data understanding

This dataset has a total of 5000 rows and 14 columns of data, and the corresponding fields are understood as follows:

Overall, this dataset consists of the PersonalLoan loan results and a series of user identity asset information, etc.

Third, data observation and cleansing

This paper's data cleansing process is still divided into two parts: initial observation and cleansing. On the still divided into two steps of preliminary observation and cleaning, data observation is mainly through the following steps:

Amendment for abnormal data types and data problems, corrected and re-descriptive statistics, observation of the overall characteristics of the data

Overall idea:

1 How effective is the campaign? How many people were prompted to take out loans?

2 What kind of people are more inclined to take out a loan

The effect of the campaign is even if that goal is reached , in this case the goal is to take out a loan, so it is only necessary to count the number of people who took out a loan and those who did not take out a loan in the PersonalLoan

Through this campaign, 480 out of 5,000 customers have opened a loan, which is about 10% of the total. About, and the activity is only for users who did not take out a loan, overall, the business boosted more than 10%, the effect has been quite good

To analyze the potential impact of loan users, is whether the loan PersonalLoan this field and other fields to find the correlation (corr () function), to find out the correlation coefficient of the largest value and show

Based on this further division of the drawing board, the columns and personalLoan (whether the loan) correlation coefficient visualization show the following results

From the figure can be found:

1. Strong correlation variables affecting the loan are: income, monthly credit card spending, whether there is a deposit account

2. Weakly correlated variables that affect loans are: education, mortgage value, and household size

3. The remaining factors such as zip code, whether or not one has Internet banking, credit cards, and securities accounts do not have a significant effect on loans

4. Although age and years of experience do not have a significant effect, because they are continuous variables, it cannot be ruled out that there is a greater demand for loans within a certain interval, which needs to be further analyzed in the future

In the previous step on the basis of the variables affecting the loan will be further analyzed, according to the nature of the variables are handled separately

The variables in this case are divided into qualitative and quantitative, respectively, to explore the impact of its and whether the loan, the case of the directional variables are the existence of a bank deposit account, the level of education, the number of people in the family, etc.

From the results of the user opened a deposit account to apply for a loan compared with the possibility of opening a deposit account than not open The likelihood of applying for a loan is six times worse than that of users who do not have a deposit account, so finding ways to get customers to open a deposit account is not a means of increasing the loan rate

With higher education, the proportion of applications for loans increases, indicating that highly educated users are more inclined to become users of the loan, which side by side reflects that the higher the level of education, the more acceptable the concept of consumption of overspending, and the easier it is to become potential users of loans

The results of the directional variables in this case are the existence of bank deposit accounts, household size, and so on. The higher the education level, the more receptive to forward-looking consumption, and the more likely to become loan users

Customers with a family size of 3-4 are more likely to apply for a loan than those with a family size of 1-2, reflecting the fact that with the increase in the number of family members and the gradual increase in economic pressure, the potential demand for loans increases, and there is a greater tendency to use the business

This case mainly focuses on the relationship between quantitative variables, such as the yearly collars, incomes, credit card repayment amounts, and house mortgages, and whether or not to open a loan. As quantitative variables, the intervals are consecutive intervals, as opposed to qualitative variables, in order to understand the full picture of the data, such variables should be counted separately for different intervals

In summary, the age gap between loan and non-loan users is not large, and in terms of the specific age group, customers in the 32.0-39.0 age group are more inclined to take out a loan

The rest of the quantitative variables such as Income, home mortgage value, monthly credit card spending, the analysis process is basically the same as the age of the variable

Overall, high-income groups are more inclined to take out loans than low-income groups, when the income is more than 82, the number of people who take out a loan will be five times the number of people who took out a loan before, when it is more than 98, the loan willingness reaches more than 17%, and when it is more than 170, the loan willingness is more than half of the number of people who took out a loan, so the higher the income, the stronger the loan business intention is stronger

When the value of home mortgage exceeds 109.5, the willingness to loan significantly increased, in general, the higher the value of home mortgage, the more customers tend to loan

Most of the loan users of credit card spending on average is close to 4, while the unloaned users is close to 2, a full twice as much, and in terms of the user stratification, after the credit card spending more than 2.8 thousand U.S. dollars

The probability of getting a loan is increased by 4 times, and over $6,000, it will go back down to about 0.3, which is a significant increase compared to the $2.8k before, so we should focus on grasping the customers whose monthly credit card spending is more than $2.8k

Three: How can I get some preparatory information for the QP Case Study Competition?