Prerequisite

 Excel fuction

  . DCOUNT

  . INDEX

  . RANDBETWEEN

 VBA

Excel : 4_DECISION_TREE_CASE_STUDY_마케팅엔지니어코리아_이정훈.xlsm

First of all, Let's suppose that we have some information and whether they buy computer or not about customers so that we want to extract good factor to predict for customer to buy computer like the following.  Like the decision tree, we want to make many rules that also get a percentage of people who bought the computer. .

In this case, we can make 4 variables to save a value from RANDBETWEEN(). First two variables, the value's range would be between 1 and 4 and will point out the index of the column to be used. (the two values above the table below). Next two variables's range would between 1 and 14 and point out a index of rows number to be used as a condition's value. (the two values under the table below)Then we can use DCOUNT function to count up rows that has 1 value in the last column where it was converted bit value that means whether or not a customer boiught a computer.

we almost to finish a preparation to generate the rule. The last step is to make VBA macro that clear the region where it displays and copy the rule increasing row number. The vba logic is like the followings.

Sub RULE_GENERATOR()

    Range("O2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("O2").Select

For K = 1 To 1000
    Range("O2").Offset(K - 1, 0) = K
    Range("O2").Offset(K - 1, 1) = Range("M3")
Next K

End Sub

It's done!

※ 멀티캠퍼스에서 진행되는 이정훈의 데이터분석과정 자료임을 밝힙니다.