level 3
咋了
2016年04月23日 15点04分
level 12
![[狂汗]](/static/emoticons/u72c2u6c57.png)
level 3
不幸福的蛋
楼主
* Calculate IV for binned variables;
%macro IVcal_qcbnum(dsn=, tgt=, qvar=, keyv=);
data &dsn.;
set &dsn.;
TargetBinary = &tgt.;
good = &tgt.;
bad = 1-&tgt.;
run;
proc sort data = &dsn.; by &keyv.; run;
proc means data = &dsn. missing noprint;
class &keyv.;
var good bad &qvar.;
output out = &dsn._mean sum(good)=goodct sum(bad)=badct mean(&qvar.)=x_mean median(&qvar.)=x_median;
run;
proc sql noprint;
select goodct, badct into :_totgood, :_totbad
from &dsn._mean
where _type_ = 0;
quit;
/*%put <<<_totgood = &_totgood. and _totbad = &_totbad.>>>;*/
data &dsn._mean0 (drop=_type_ rename=(&keyv. = vgroup _freq_=volume));
/*retain IV_cum 0;*/
retain varname &keyv. _freq_ pvolume goodct badct pgood pbad goodrate badrate IV WOE logodds tgt_rate;
set &dsn._mean ;
format pvolume goodrate badrate pgood pbad percent10.2;
format logodds WOE IV 8.3;
format tgt_rate percent10.2;
if _type_=1;
varname = "&qvar.";
pvolume = _freq_/(&_totgood. + &_totbad.);
goodrate = goodct/(goodct+badct);
badrate = badct/(goodct+badct);
if goodrate ne 0 and badrate ne 0 then logodds = log(goodrate/badrate);
else logodds = .;
pgood = goodct/&_totgood.;
pbad = badct/&_totbad.;
WOE = 0;
if (pbad > 0 and pgood > 0) then WOE = log(pgood/pbad);
IV = (pgood-pbad)*woe;
Tgt_Rate = goodrate;
/*IV_cum = IV_cum + IV;*/
run;
proc sql noprint;
select sum(IV), count(*) into :&qvar._IV_tot, :&qvar._numgrp
from &dsn._mean0
quit;
data &dsn._IV;
varname = "&qvar.";
IV = &&&qvar._IV_tot.;
Num_Group = &&&qvar._numgrp.;
run;
%if %sysfunc(exist(&dsn._IV)) %then %do;
data IV;
set IV &dsn._IV;
run;
%end;
%if %sysfunc(exist(&dsn._mean0)) %then %do;
data vargroup;
set vargroup &dsn._mean0;
run;
%end;
/*Delete temperary data*/
proc datasets lib=work nolist;
delete &dsn._mean &dsn._mean0 &dsn._IV;
run;
quit;
%mend IVcal_qcbnum;
2016年06月09日 13点06分
8
%macro IVcal_qcbnum(dsn=, tgt=, qvar=, keyv=);
data &dsn.;
set &dsn.;
TargetBinary = &tgt.;
good = &tgt.;
bad = 1-&tgt.;
run;
proc sort data = &dsn.; by &keyv.; run;
proc means data = &dsn. missing noprint;
class &keyv.;
var good bad &qvar.;
output out = &dsn._mean sum(good)=goodct sum(bad)=badct mean(&qvar.)=x_mean median(&qvar.)=x_median;
run;
proc sql noprint;
select goodct, badct into :_totgood, :_totbad
from &dsn._mean
where _type_ = 0;
quit;
/*%put <<<_totgood = &_totgood. and _totbad = &_totbad.>>>;*/
data &dsn._mean0 (drop=_type_ rename=(&keyv. = vgroup _freq_=volume));
/*retain IV_cum 0;*/
retain varname &keyv. _freq_ pvolume goodct badct pgood pbad goodrate badrate IV WOE logodds tgt_rate;
set &dsn._mean ;
format pvolume goodrate badrate pgood pbad percent10.2;
format logodds WOE IV 8.3;
format tgt_rate percent10.2;
if _type_=1;
varname = "&qvar.";
pvolume = _freq_/(&_totgood. + &_totbad.);
goodrate = goodct/(goodct+badct);
badrate = badct/(goodct+badct);
if goodrate ne 0 and badrate ne 0 then logodds = log(goodrate/badrate);
else logodds = .;
pgood = goodct/&_totgood.;
pbad = badct/&_totbad.;
WOE = 0;
if (pbad > 0 and pgood > 0) then WOE = log(pgood/pbad);
IV = (pgood-pbad)*woe;
Tgt_Rate = goodrate;
/*IV_cum = IV_cum + IV;*/
run;
proc sql noprint;
select sum(IV), count(*) into :&qvar._IV_tot, :&qvar._numgrp
from &dsn._mean0
quit;
data &dsn._IV;
varname = "&qvar.";
IV = &&&qvar._IV_tot.;
Num_Group = &&&qvar._numgrp.;
run;
%if %sysfunc(exist(&dsn._IV)) %then %do;
data IV;
set IV &dsn._IV;
run;
%end;
%if %sysfunc(exist(&dsn._mean0)) %then %do;
data vargroup;
set vargroup &dsn._mean0;
run;
%end;
/*Delete temperary data*/
proc datasets lib=work nolist;
delete &dsn._mean &dsn._mean0 &dsn._IV;
run;
quit;
%mend IVcal_qcbnum;
这是什么代码![[乖]](/static/emoticons/u4e56.png)
2018年03月22日 20点03分
level 3
不幸福的蛋
楼主
***** Macros to calculate IV for QCB char Worst Status Variables *****;
* Macro Variable Binning for QCB Char Worst Status variables ;
%macro QCBWSBin(qvar);
length &qvar._f $40;
length &qvar._g $5;
if missing(&qvar.) then do;
&qvar._g = 'G1'; &qvar._f = 'G1: Missing';
end;
else if &qvar. in ('_', 'T') then do;
&qvar._g = 'G2'; &qvar._f = 'G2: default value (- T)';
end;
else if &qvar. in ('M','C','H','F','G','K') then do;
&qvar._g = 'G3'; &qvar._f = "G3: default value (M C H F G K)";
end;
else if &qvar. in ('R','V','D') then do;
&qvar._g = 'G4'; &qvar._f = "G4: R V D";
end;
else if &qvar. in ('6','B','5','4') then do;
&qvar._g = 'G5'; &qvar._f = "G5: 6 B 5 4";
end;
else if &qvar. in ('3','A','I','2','1') then do;
&qvar._g = 'G6'; &qvar._f = "G6: 3 A I 2 1";
end;
else if &qvar. in ('Q','U','N','Z','0','S','X','.','*') then do;
&qvar._g = 'G7'; &qvar._f = "G7: Q U N Z 0 S X . *";
end;
else do;
&qvar._g = 'G8'; &qvar._f = "G8: wrong";
end;
%mend QCBWSBin;
* Calculate IV for binned variables;
%macro IVcal_qcbchar(dsn=, tgt=, qvar=, keyv=);
data &dsn.;
set &dsn.;
TargetBinary = &tgt.;
good = &tgt.;
bad = 1-&tgt.;
run;
proc sort data = &dsn.; by &keyv.; run;
proc means data = &dsn. missing noprint;
class &keyv.;
var good bad;
output out = &dsn._mean sum(good)=goodct sum(bad)=badct;
run;
proc sql noprint;
select goodct, badct into :_totgood, :_totbad
from &dsn._mean
where _type_ = 0;
quit;
%put _totgood = &_totgood. and _totbad = &_totbad.;
data &dsn._mean0 (drop=_type_ rename=(&keyv. = vgroup _freq_=volume));
/*retain IV_cum 0;*/
retain varname &keyv. _freq_ pvolume goodct badct pgood pbad goodrate badrate IV WOE logodds tgt_rate;
set &dsn._mean ;
format pvolume goodrate badrate pgood pbad percent10.2;
format logodds WOE IV 8.3;
format tgt_rate percent10.2;
if _type_=1;
varname = "&qvar.";
pvolume = _freq_/(&_totgood. + &_totbad.);
goodrate = goodct/(goodct+badct);
badrate = badct/(goodct+badct);
if goodrate ne 0 and badrate ne 0 then logodds = log(goodrate/badrate);
else logodds = .;
pgood = goodct/&_totgood.;
pbad = badct/&_totbad.;
WOE = 0;
if (pbad > 0 and pgood > 0) then WOE = log(pgood/pbad);
IV = (pgood-pbad)*woe;
Tgt_Rate = goodrate;
/*IV_cum = IV_cum + IV;*/
run;
proc sql noprint;
select sum(IV), count(*) into :&qvar._IV_tot, :&qvar._numgrp
from &dsn._mean0
quit;
data &dsn._IV;
varname = "&qvar.";
IV = &&&qvar._IV_tot.;
Num_Group = &&&qvar._numgrp.;
run;
%if %sysfunc(exist(&dsn._IV)) %then %do;
data IV;
set IV &dsn._IV;
run;
%end;
%if %sysfunc(exist(&dsn._mean0)) %then %do;
data vargroup;
set vargroup &dsn._mean0;
format vgroup $40.;
run;
%end;
/*Delete temperary data*/
proc datasets lib=work nolist;
delete &dsn. &dsn._mean &dsn._mean0 &dsn._IV;
run;
quit;
%mend IVcal_qcbchar;
%macro QCBCharWSIV(dsn, tgt, qvar, qvarb);
data &qvarb.;
set &dsn. (keep=orangeid dial_month &tgt. &qvar.);
%QCBWSBin(qvar = &qvar.); /*Call Macro*/
run;
%IVcal_qcbchar(dsn=&qvarb., tgt=&tgt., qvar=&qvar., keyv=&qvar._f); /*Call Macro*/
%mend QCBCharWSIV;
/*%QCBCharWSIV(datainv.CA_QCB_orange_n5_s1, tgt=Tgt_sumpay_N60D_flag, qvar=CBT0316, qvarb=CBT0316);*/
/*%QCBCharWSIV(datainv.CA_QCB_orange_n5_s1, tgt=Tgt_sumpay_N60D_flag, qvar=CBT0456, qvarb=CBT0456);*/
***** Macros to calculate IV for QCB char Not Worst Status Variables *****;
* IV calculation for QCB char variables (not worst status);
%macro QCBCharNotWSIV(dsn, tgt, qvar, qvarb);
data &qvarb.;
set &dsn. (keep=LegacyID &tgt. &qvar.);
length &qvar._f $40;
if missing(&qvar.) then &qvar._f = 'Missing';
else &qvar._f = &qvar.;
run;
%IVcal_qcbchar(dsn=&qvarb., tgt=&tgt., qvar=&qvar., keyv=&qvar._f); /*Call Macro*/
%mend QCBCharNotWSIV;
/*%QCBCharNotWSIV(datainv.CA_QCB_orange_n5_s1, tgt=Tgt_sumpay_N60D_flag, qvar=CBT0324, qvarb=CBT0324);*/
* Macro to Run Batch to calculate IV;
%macro IVbatchChar(datadsn, listdsn, tgt, ivmacro);
proc sql noprint;
select count(*) into :nobs
from &listdsn.;
quit;
%put <<&listdsn.: nobs=&nobs.>>;
data _null_;
set &listdsn.;
call symput(varid, varname);
run;
%put <<&listdsn.: qcbv_1=&qcbv_1. & qcbv_2=&qcbv_2.>>;
%let i = 1;
%do %while (&i.<=&nobs.);
/*%do %while (&i.<=4);*/
%put <<<<date=%sysfunc(date(), date9.), time=%sysfunc(time(), time.)>>>>;
%put <<<<i = &i., qvar=&&qcbv_&i.>>>>;
%&ivmacro.(dsn=&datadsn., tgt=&tgt., qvar=&&qcbv_&i., qvarb=&&qcbv_&i.);
%let i = %eval(&i.+1);
%end;
%mend IVbatchChar;
2016年06月09日 13点06分
9
* Macro Variable Binning for QCB Char Worst Status variables ;
%macro QCBWSBin(qvar);
length &qvar._f $40;
length &qvar._g $5;
if missing(&qvar.) then do;
&qvar._g = 'G1'; &qvar._f = 'G1: Missing';
end;
else if &qvar. in ('_', 'T') then do;
&qvar._g = 'G2'; &qvar._f = 'G2: default value (- T)';
end;
else if &qvar. in ('M','C','H','F','G','K') then do;
&qvar._g = 'G3'; &qvar._f = "G3: default value (M C H F G K)";
end;
else if &qvar. in ('R','V','D') then do;
&qvar._g = 'G4'; &qvar._f = "G4: R V D";
end;
else if &qvar. in ('6','B','5','4') then do;
&qvar._g = 'G5'; &qvar._f = "G5: 6 B 5 4";
end;
else if &qvar. in ('3','A','I','2','1') then do;
&qvar._g = 'G6'; &qvar._f = "G6: 3 A I 2 1";
end;
else if &qvar. in ('Q','U','N','Z','0','S','X','.','*') then do;
&qvar._g = 'G7'; &qvar._f = "G7: Q U N Z 0 S X . *";
end;
else do;
&qvar._g = 'G8'; &qvar._f = "G8: wrong";
end;
%mend QCBWSBin;
* Calculate IV for binned variables;
%macro IVcal_qcbchar(dsn=, tgt=, qvar=, keyv=);
data &dsn.;
set &dsn.;
TargetBinary = &tgt.;
good = &tgt.;
bad = 1-&tgt.;
run;
proc sort data = &dsn.; by &keyv.; run;
proc means data = &dsn. missing noprint;
class &keyv.;
var good bad;
output out = &dsn._mean sum(good)=goodct sum(bad)=badct;
run;
proc sql noprint;
select goodct, badct into :_totgood, :_totbad
from &dsn._mean
where _type_ = 0;
quit;
%put _totgood = &_totgood. and _totbad = &_totbad.;
data &dsn._mean0 (drop=_type_ rename=(&keyv. = vgroup _freq_=volume));
/*retain IV_cum 0;*/
retain varname &keyv. _freq_ pvolume goodct badct pgood pbad goodrate badrate IV WOE logodds tgt_rate;
set &dsn._mean ;
format pvolume goodrate badrate pgood pbad percent10.2;
format logodds WOE IV 8.3;
format tgt_rate percent10.2;
if _type_=1;
varname = "&qvar.";
pvolume = _freq_/(&_totgood. + &_totbad.);
goodrate = goodct/(goodct+badct);
badrate = badct/(goodct+badct);
if goodrate ne 0 and badrate ne 0 then logodds = log(goodrate/badrate);
else logodds = .;
pgood = goodct/&_totgood.;
pbad = badct/&_totbad.;
WOE = 0;
if (pbad > 0 and pgood > 0) then WOE = log(pgood/pbad);
IV = (pgood-pbad)*woe;
Tgt_Rate = goodrate;
/*IV_cum = IV_cum + IV;*/
run;
proc sql noprint;
select sum(IV), count(*) into :&qvar._IV_tot, :&qvar._numgrp
from &dsn._mean0
quit;
data &dsn._IV;
varname = "&qvar.";
IV = &&&qvar._IV_tot.;
Num_Group = &&&qvar._numgrp.;
run;
%if %sysfunc(exist(&dsn._IV)) %then %do;
data IV;
set IV &dsn._IV;
run;
%end;
%if %sysfunc(exist(&dsn._mean0)) %then %do;
data vargroup;
set vargroup &dsn._mean0;
format vgroup $40.;
run;
%end;
/*Delete temperary data*/
proc datasets lib=work nolist;
delete &dsn. &dsn._mean &dsn._mean0 &dsn._IV;
run;
quit;
%mend IVcal_qcbchar;
%macro QCBCharWSIV(dsn, tgt, qvar, qvarb);
data &qvarb.;
set &dsn. (keep=orangeid dial_month &tgt. &qvar.);
%QCBWSBin(qvar = &qvar.); /*Call Macro*/
run;
%IVcal_qcbchar(dsn=&qvarb., tgt=&tgt., qvar=&qvar., keyv=&qvar._f); /*Call Macro*/
%mend QCBCharWSIV;
/*%QCBCharWSIV(datainv.CA_QCB_orange_n5_s1, tgt=Tgt_sumpay_N60D_flag, qvar=CBT0316, qvarb=CBT0316);*/
/*%QCBCharWSIV(datainv.CA_QCB_orange_n5_s1, tgt=Tgt_sumpay_N60D_flag, qvar=CBT0456, qvarb=CBT0456);*/
***** Macros to calculate IV for QCB char Not Worst Status Variables *****;
* IV calculation for QCB char variables (not worst status);
%macro QCBCharNotWSIV(dsn, tgt, qvar, qvarb);
data &qvarb.;
set &dsn. (keep=LegacyID &tgt. &qvar.);
length &qvar._f $40;
if missing(&qvar.) then &qvar._f = 'Missing';
else &qvar._f = &qvar.;
run;
%IVcal_qcbchar(dsn=&qvarb., tgt=&tgt., qvar=&qvar., keyv=&qvar._f); /*Call Macro*/
%mend QCBCharNotWSIV;
/*%QCBCharNotWSIV(datainv.CA_QCB_orange_n5_s1, tgt=Tgt_sumpay_N60D_flag, qvar=CBT0324, qvarb=CBT0324);*/
* Macro to Run Batch to calculate IV;
%macro IVbatchChar(datadsn, listdsn, tgt, ivmacro);
proc sql noprint;
select count(*) into :nobs
from &listdsn.;
quit;
%put <<&listdsn.: nobs=&nobs.>>;
data _null_;
set &listdsn.;
call symput(varid, varname);
run;
%put <<&listdsn.: qcbv_1=&qcbv_1. & qcbv_2=&qcbv_2.>>;
%let i = 1;
%do %while (&i.<=&nobs.);
/*%do %while (&i.<=4);*/
%put <<<<date=%sysfunc(date(), date9.), time=%sysfunc(time(), time.)>>>>;
%put <<<<i = &i., qvar=&&qcbv_&i.>>>>;
%&ivmacro.(dsn=&datadsn., tgt=&tgt., qvar=&&qcbv_&i., qvarb=&&qcbv_&i.);
%let i = %eval(&i.+1);
%end;
%mend IVbatchChar;
level 3
不幸福的蛋
楼主
data qcbnum_list0_v2;
input varname $30.;
datalines;
num1
num2
num3
num4
num5
num6
num7
num8
num9
num10
num11
num12
num13
exp_num1
exp_num2
exp_num3
exp_num4
exp_num5
exp_num6
exp_num7
exp_num8
exp_num9
exp_num10
exp_num11
exp_num12
exp_num13
exp_num14
exp_num15
exp_num16
exp_num17
exp_num18
exp_num19
exp_num20
exp_num21
exp_num22
exp_num23
exp_num24
exp_num25
exp_num26
;
run;
data qcbnum_list_v2;
set qcbnum_list0_v2;
length vartype $40;
length varid $10;
vartype = 'QCB Num Var';
varid = compress(catx('', "qcbv_", _n_));
run;
2016年06月09日 13点06分
10
input varname $30.;
datalines;
num1
num2
num3
num4
num5
num6
num7
num8
num9
num10
num11
num12
num13
exp_num1
exp_num2
exp_num3
exp_num4
exp_num5
exp_num6
exp_num7
exp_num8
exp_num9
exp_num10
exp_num11
exp_num12
exp_num13
exp_num14
exp_num15
exp_num16
exp_num17
exp_num18
exp_num19
exp_num20
exp_num21
exp_num22
exp_num23
exp_num24
exp_num25
exp_num26
;
run;
data qcbnum_list_v2;
set qcbnum_list0_v2;
length vartype $40;
length varid $10;
vartype = 'QCB Num Var';
varid = compress(catx('', "qcbv_", _n_));
run;
level 3
不幸福的蛋
楼主
/********** Numerical Variables **********/
data IV;
length varname $20;
varname = "NULL";
IV = .;
Num_Group = .;
run;
data vargroup;
length varname vgroup $40;
varname = 'NULL';
run;
data varpct;
length varname $40;
varname = 'NULL';
run;
%IVbatch(datadsn=All_data2, listdsn=qcbnum_list_v2, tgt=Target_flag, ivmacro=QCBNumIV);
Data IV_num_Int_gt24;
set IV;
run;
data vargroup_num_Int_gt24;
set vargroup;
run;
data varpct_num_Int_gt24;
set varpct;
run;
2016年06月09日 13点06分
11
data IV;
length varname $20;
varname = "NULL";
IV = .;
Num_Group = .;
run;
data vargroup;
length varname vgroup $40;
varname = 'NULL';
run;
data varpct;
length varname $40;
varname = 'NULL';
run;
%IVbatch(datadsn=All_data2, listdsn=qcbnum_list_v2, tgt=Target_flag, ivmacro=QCBNumIV);
Data IV_num_Int_gt24;
set IV;
run;
data vargroup_num_Int_gt24;
set vargroup;
run;
data varpct_num_Int_gt24;
set varpct;
run;
level 3
不幸福的蛋
楼主
proc surveyselect data=YourData out=outboot /* 1 */ seed=30459584 /* 2 */ method=urs /* 3 */ samprate=1 /* 4 */ outhits /* 5 */ rep=1000; /* 6 */ run;proc univariate data=outboot; var x; by Replicate; /* 7 */ output out=outall kurtosis=curt; run;proc univariate data=outall; var curt; output out=final pctlpts=2.5, 97.5 pctlpre=ci; run;
2016年06月12日 23点06分
12
level 3
不幸福的蛋
楼主
Sub LogoddsChart3()
Dim ActSheet As Worksheet
Dim R As Range
Set ActSheet = ActiveSheet
Set R = Selection
Dim RowS, RowE, RowE1 As Integer
RowS = R.Row
RowE = R.Count + R.Row - 1
' RowS = InputBox("please enter the First Row Number", "Input")
' RowE = InputBox("please enter the Last Row Number", "Input")
RowE1 = RowE + 3
' Insert two row.
ActiveSheet.Range("A" & RowE).Offset(1).EntireRow.Insert
ActiveSheet.Range("A" & RowE).Offset(1).EntireRow.Insert
ActiveSheet.Range("A" & RowE).Offset(1).EntireRow.Insert
' sChtName = ActiveSheet.Range("K" & RowS).Text
' drawing a chart for logodds vs groups
' Add chart.
ActiveSheet.Range("K" & RowS & ":K" & RowE).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Name = ActiveSheet.Range("I1")
ActiveChart.SeriesCollection(1).Values = ActiveSheet.Range("I" & RowS & ":I" & RowE)
ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("F" & RowS & ":F" & RowE)
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = ActiveSheet.Range("S1")
ActiveChart.SeriesCollection(2).Values = ActiveSheet.Range("S" & RowS & ":S" & RowE)
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ChartType = xlColumnClustered
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ChartType = xlLineMarkersStacked
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).AxisGroup = 2
' Apply Chart Layout 1 (title on top etc) and change title font,
ActiveChart.ApplyLayout (1)
ActiveChart.ChartTitle.Text = ActiveSheet.Range("C" & RowS)
ActiveChart.ChartTitle.Characters.Font.Size = 9
' Position and resize chart.
ActiveChart.Parent.Top = ActiveSheet.RowS(RowS).Top
ActiveChart.Parent.Left = ActiveSheet.Columns(33).Left
ActiveChart.Parent.Width = ActiveSheet.Range("ad" & RowS & ":aj" & RowE1).Width
ActiveChart.Parent.Height = ActiveSheet.Range("ad" & RowS & ":aj" & RowE1).Height
ActiveSheet.RowS(RowE).Borders(xlEdgeBottom).LineStyle = xlNone
ActiveSheet.RowS(RowE1).Borders(xlEdgeBottom).Weight = xlMedium
ActiveSheet.Range("W1") = "ln_median_x"
ActiveSheet.Range("X1") = "sqrt_median_x"
ActiveSheet.Range("Y1") = "linear_fitting"
ActiveSheet.Range("Z1") = "poly_fitting"
ActiveSheet.Range("aa1") = "log_fitting"
' ======================================================================================
On Error Resume Next
' Fitting Curves
2016年06月29日 14点06分
13
Dim ActSheet As Worksheet
Dim R As Range
Set ActSheet = ActiveSheet
Set R = Selection
Dim RowS, RowE, RowE1 As Integer
RowS = R.Row
RowE = R.Count + R.Row - 1
' RowS = InputBox("please enter the First Row Number", "Input")
' RowE = InputBox("please enter the Last Row Number", "Input")
RowE1 = RowE + 3
' Insert two row.
ActiveSheet.Range("A" & RowE).Offset(1).EntireRow.Insert
ActiveSheet.Range("A" & RowE).Offset(1).EntireRow.Insert
ActiveSheet.Range("A" & RowE).Offset(1).EntireRow.Insert
' sChtName = ActiveSheet.Range("K" & RowS).Text
' drawing a chart for logodds vs groups
' Add chart.
ActiveSheet.Range("K" & RowS & ":K" & RowE).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlLine
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Name = ActiveSheet.Range("I1")
ActiveChart.SeriesCollection(1).Values = ActiveSheet.Range("I" & RowS & ":I" & RowE)
ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("F" & RowS & ":F" & RowE)
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = ActiveSheet.Range("S1")
ActiveChart.SeriesCollection(2).Values = ActiveSheet.Range("S" & RowS & ":S" & RowE)
ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).ChartType = xlColumnClustered
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ChartType = xlLineMarkersStacked
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).AxisGroup = 2
' Apply Chart Layout 1 (title on top etc) and change title font,
ActiveChart.ApplyLayout (1)
ActiveChart.ChartTitle.Text = ActiveSheet.Range("C" & RowS)
ActiveChart.ChartTitle.Characters.Font.Size = 9
' Position and resize chart.
ActiveChart.Parent.Top = ActiveSheet.RowS(RowS).Top
ActiveChart.Parent.Left = ActiveSheet.Columns(33).Left
ActiveChart.Parent.Width = ActiveSheet.Range("ad" & RowS & ":aj" & RowE1).Width
ActiveChart.Parent.Height = ActiveSheet.Range("ad" & RowS & ":aj" & RowE1).Height
ActiveSheet.RowS(RowE).Borders(xlEdgeBottom).LineStyle = xlNone
ActiveSheet.RowS(RowE1).Borders(xlEdgeBottom).Weight = xlMedium
ActiveSheet.Range("W1") = "ln_median_x"
ActiveSheet.Range("X1") = "sqrt_median_x"
ActiveSheet.Range("Y1") = "linear_fitting"
ActiveSheet.Range("Z1") = "poly_fitting"
ActiveSheet.Range("aa1") = "log_fitting"
' ======================================================================================
On Error Resume Next
' Fitting Curves
level 3
不幸福的蛋
楼主
' Fitting Curves
' Calculate nonmissing median
Dim Len1, Len2, DLen, i As Integer
' remove any median and mean smaller than -99990
For i = 1 To 3
If ActiveSheet.Range("U" & RowS + i - 1).Value < -99990 Then
ActiveSheet.Range("U" & RowS + i - 1).Value = ""
End If
If ActiveSheet.Range("V" & RowS + i - 1).Value < -99990 Then
ActiveSheet.Range("V" & RowS + i - 1).Value = ""
End If
Next
Len1 = Application.WorksheetFunction.Count(ActiveSheet.Range("V" & RowS & ":V" & RowE))
Len2 = RowE - RowS + 1
DLen = Len2 - Len1
' the target array
' the x_median array
Dim Tagvec(), Medvec(), Medvecsqt(), PolyT(), ExpT(), LogT(), SStot(), SSpolyReg(), SSexpReg(), SSlogReg(), MedvecLN(), MedvecSQRT(), linR(), SSLinReg() As Double
Dim Uend As Integer
'Uend = UBound(Medvec, 1) - LBound(Medvec, 1) + 1
Uend = RowE - (RowS + DLen) + 1
ReDim Tagvec(1 To Uend)
ReDim Medvec(1 To Uend)
ReDim Medvecsqt(1 To Uend)
ReDim PolyT(1 To Uend)
ReDim ExpT(1 To Uend)
ReDim LogT(1 To Uend)
ReDim MedvecLN(1 To Uend)
ReDim MedvecSQRT(1 To Uend)
ReDim linR(1 To Uend)
ReDim SStot(1 To Uend)
ReDim SSpolyReg(1 To Uend)
ReDim SSexpReg(1 To Uend)
ReDim SSlogReg(1 To Uend)
ReDim SSLinReg(1 To Uend)
For i = 1 To Uend
Tagvec(i) = ActiveSheet.Range("S" & RowS + DLen + i - 1).Value
Medvec(i) = ActiveSheet.Range("V" & RowS + DLen + i - 1).Value
Medvecsqt(i) = ActiveSheet.Range("V" & RowS + DLen + i - 1).Value * ActiveSheet.Range("V" & RowS + DLen + i - 1).Value
If ActiveSheet.Range("V" & RowS + DLen + i - 1).Value > 0 Then
ActiveSheet.Range("W" & RowS + DLen + i - 1).FormulaR1C1 = "=ln(RC[-1])"
Else
ActiveSheet.Range("W" & RowS + DLen + i - 1).FormulaR1C1 = "=0"
End If
'MedvecLN(i) = Application.WorksheetFunction.Ln(ActiveSheet.Range("S" & RowS + DLen + i).Value)
MedvecLN(i) = ActiveSheet.Range("W" & RowS + DLen + i - 1).Value
If ActiveSheet.Range("V" & RowS + DLen + i - 1).Value < 0 Then
MedvecSQRT(i) = 0
Else
MedvecSQRT(i) = (ActiveSheet.Range("V" & RowS + DLen + i - 1).Value) ^ 0.5
End If
Next
' Coefficients
Dim a1, b1, b2 As Double
pob2 = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Tagvec, Array(Medvec, Medvecsqt)), 1)
pob1 = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Tagvec, Array(Medvec, Medvecsqt)), 2)
poa1 = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Tagvec, Array(Medvec, Medvecsqt)), 3)
'Ea1 = Application.WorksheetFunction.Exp(Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Application.WorksheetFunction.Ln(Tagvec), Medvec), 1, 2))
'Eb1 = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Application.WorksheetFunction.Ln(Tagvec), Medvec), 1)
La1 = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Tagvec, MedvecLN), 1)
Lb1 = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Tagvec, MedvecLN), 1, 2)
LinA1 = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Tagvec, Medvec), 1)
LinB1 = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Tagvec, Medvec), 2)
For i = 1 To Uend
PolyT(i) = poa1 + pob1 * Medvec(i) + pob2 * Medvecsqt(i)
linR(i) = LinB1 + LinA1 * Medvec(i)
'ExpT(i) = Ea1 * Application.WorksheetFunction.Exp(Medvec(i) * Eb1)
If ActiveSheet.Range("v" & RowS + DLen).Value > 0 Then
LogT(i) = La1 * Application.WorksheetFunction.Ln(Medvec(i)) + Lb1
End If
Next
' Calculate R square
2016年06月29日 14点06分
14
' Calculate nonmissing median
Dim Len1, Len2, DLen, i As Integer
' remove any median and mean smaller than -99990
For i = 1 To 3
If ActiveSheet.Range("U" & RowS + i - 1).Value < -99990 Then
ActiveSheet.Range("U" & RowS + i - 1).Value = ""
End If
If ActiveSheet.Range("V" & RowS + i - 1).Value < -99990 Then
ActiveSheet.Range("V" & RowS + i - 1).Value = ""
End If
Next
Len1 = Application.WorksheetFunction.Count(ActiveSheet.Range("V" & RowS & ":V" & RowE))
Len2 = RowE - RowS + 1
DLen = Len2 - Len1
' the target array
' the x_median array
Dim Tagvec(), Medvec(), Medvecsqt(), PolyT(), ExpT(), LogT(), SStot(), SSpolyReg(), SSexpReg(), SSlogReg(), MedvecLN(), MedvecSQRT(), linR(), SSLinReg() As Double
Dim Uend As Integer
'Uend = UBound(Medvec, 1) - LBound(Medvec, 1) + 1
Uend = RowE - (RowS + DLen) + 1
ReDim Tagvec(1 To Uend)
ReDim Medvec(1 To Uend)
ReDim Medvecsqt(1 To Uend)
ReDim PolyT(1 To Uend)
ReDim ExpT(1 To Uend)
ReDim LogT(1 To Uend)
ReDim MedvecLN(1 To Uend)
ReDim MedvecSQRT(1 To Uend)
ReDim linR(1 To Uend)
ReDim SStot(1 To Uend)
ReDim SSpolyReg(1 To Uend)
ReDim SSexpReg(1 To Uend)
ReDim SSlogReg(1 To Uend)
ReDim SSLinReg(1 To Uend)
For i = 1 To Uend
Tagvec(i) = ActiveSheet.Range("S" & RowS + DLen + i - 1).Value
Medvec(i) = ActiveSheet.Range("V" & RowS + DLen + i - 1).Value
Medvecsqt(i) = ActiveSheet.Range("V" & RowS + DLen + i - 1).Value * ActiveSheet.Range("V" & RowS + DLen + i - 1).Value
If ActiveSheet.Range("V" & RowS + DLen + i - 1).Value > 0 Then
ActiveSheet.Range("W" & RowS + DLen + i - 1).FormulaR1C1 = "=ln(RC[-1])"
Else
ActiveSheet.Range("W" & RowS + DLen + i - 1).FormulaR1C1 = "=0"
End If
'MedvecLN(i) = Application.WorksheetFunction.Ln(ActiveSheet.Range("S" & RowS + DLen + i).Value)
MedvecLN(i) = ActiveSheet.Range("W" & RowS + DLen + i - 1).Value
If ActiveSheet.Range("V" & RowS + DLen + i - 1).Value < 0 Then
MedvecSQRT(i) = 0
Else
MedvecSQRT(i) = (ActiveSheet.Range("V" & RowS + DLen + i - 1).Value) ^ 0.5
End If
Next
' Coefficients
Dim a1, b1, b2 As Double
pob2 = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Tagvec, Array(Medvec, Medvecsqt)), 1)
pob1 = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Tagvec, Array(Medvec, Medvecsqt)), 2)
poa1 = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Tagvec, Array(Medvec, Medvecsqt)), 3)
'Ea1 = Application.WorksheetFunction.Exp(Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Application.WorksheetFunction.Ln(Tagvec), Medvec), 1, 2))
'Eb1 = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Application.WorksheetFunction.Ln(Tagvec), Medvec), 1)
La1 = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Tagvec, MedvecLN), 1)
Lb1 = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Tagvec, MedvecLN), 1, 2)
LinA1 = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Tagvec, Medvec), 1)
LinB1 = Application.WorksheetFunction.Index(Application.WorksheetFunction.LinEst(Tagvec, Medvec), 2)
For i = 1 To Uend
PolyT(i) = poa1 + pob1 * Medvec(i) + pob2 * Medvecsqt(i)
linR(i) = LinB1 + LinA1 * Medvec(i)
'ExpT(i) = Ea1 * Application.WorksheetFunction.Exp(Medvec(i) * Eb1)
If ActiveSheet.Range("v" & RowS + DLen).Value > 0 Then
LogT(i) = La1 * Application.WorksheetFunction.Ln(Medvec(i)) + Lb1
End If
Next
' Calculate R square
level 3
不幸福的蛋
楼主
' Calculate R square
Dim tagmean, SumTotE, SumpolyE, SumexpE, SumlogE, polyR, ExpR, LogR, linearR, SumlinE As Double
tagmean = Application.WorksheetFunction.Average(Tagvec)
For i = 1 To Uend
SStot(i) = (Tagvec(i) - tagmean) ^ 2
SSpolyReg(i) = (Tagvec(i) - PolyT(i)) ^ 2
'SSexpReg(i) = (ExpT(i) - tagmean) ^ 2
SSLinReg(i) = (Tagvec(i) - linR(i)) ^ 2
SSlogReg(i) = (Tagvec(i) - LogT(i)) ^ 2
Next
SumTotE = Application.WorksheetFunction.Sum(SStot)
SumpolyE = Application.WorksheetFunction.Sum(SSpolyReg)
SumlinE = Application.WorksheetFunction.Sum(SSLinReg)
SumlogE = Application.WorksheetFunction.Sum(SSlogReg)
polyR = 1 - SumpolyE / SumTotE
linearR = 1 - SumlinE / SumTotE
LogR = 1 - SumlogE / SumTotE
For i = 1 To Uend
ActiveSheet.Range("W" & RowS + DLen + i - 1).Value = MedvecLN(i)
ActiveSheet.Range("X" & RowS + DLen + i - 1).Value = MedvecSQRT(i)
ActiveSheet.Range("Y" & RowS + DLen + i - 1).Value = linR(i)
ActiveSheet.Range("Z" & RowS + DLen + i - 1).Value = PolyT(i)
ActiveSheet.Range("AA" & RowS + DLen + i - 1).Value = LogT(i)
'ActiveSheet.Range("Y" & RowS + DLen + i - 1) = PolyT(i)
'ActiveSheet.Range("AB" & RowS + DLen + i - 1) = LogT(i)
Next
If Err.Number = 0 Then
ActiveSheet.Range("AB" & RowS + 1) = "Linear Fitting"
ActiveSheet.Range("AB" & RowS + 2) = "Polynomial Fitting"
ActiveSheet.Range("AB" & RowS + 3) = "logarithmic Fitting"
ActiveSheet.Range("ac" & RowS) = "coeff 1"
ActiveSheet.Range("ad" & RowS) = "coeff 2"
ActiveSheet.Range("ae" & RowS) = "coeff 3"
ActiveSheet.Range("af" & RowS) = "R2"
ActiveSheet.Range("ac" & RowS + 1) = La1
ActiveSheet.Range("ad" & RowS + 1) = Lb1
ActiveSheet.Range("ac" & RowS + 2) = poa1
ActiveSheet.Range("ad" & RowS + 2) = pob1
ActiveSheet.Range("ae" & RowS + 2) = pob2
If ActiveSheet.Range("v" & RowS + DLen).Value > 0 Then
ActiveSheet.Range("ac" & RowS + 3) = LinA1
ActiveSheet.Range("ad" & RowS + 3) = LinB1
ActiveSheet.Range("af" & RowS + 3) = LogR
End If
ActiveSheet.Range("af" & RowS + 1) = linearR
ActiveSheet.Range("af" & RowS + 2) = polyR
' drawing a chart for logodds vs x_median
ActiveSheet.Range("Q" & RowS & ":Q" & RowE).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.PlotArea.Select
' logodds vs x_median
ActiveChart.SeriesCollection(1).Name = ActiveSheet.Range("s1")
ActiveChart.SeriesCollection(1).Values = ActiveSheet.Range("s" & RowS + DLen & ":s" & RowE)
ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("v" & RowS + DLen & ":v" & RowE)
' Linear_est_logodds vs x_median
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = ActiveSheet.Range("y1")
ActiveChart.SeriesCollection(2).Values = ActiveSheet.Range("y" & RowS + DLen & ":y" & RowE)
ActiveChart.SeriesCollection(2).XValues = ActiveSheet.Range("v" & RowS + DLen & ":v" & RowE)
ActiveChart.SeriesCollection(2).ChartType = xlXYScatterSmoothNoMarkers
' Poly_est_logodds vs x_median
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Name = ActiveSheet.Range("z1")
ActiveChart.SeriesCollection(3).Values = ActiveSheet.Range("z" & RowS + DLen & ":z" & RowE)
ActiveChart.SeriesCollection(3).XValues = ActiveSheet.Range("v" & RowS + DLen & ":v" & RowE)
ActiveChart.SeriesCollection(3).ChartType = xlXYScatterSmoothNoMarkers
' Log_est_logodds vs x_median
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).Name = ActiveSheet.Range("aa1")
ActiveChart.SeriesCollection(4).Values = ActiveSheet.Range("aa" & RowS + DLen & ":aa" & RowE)
ActiveChart.SeriesCollection(4).XValues = ActiveSheet.Range("v" & RowS + DLen & ":v" & RowE)
ActiveChart.SeriesCollection(4).ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.ApplyLayout (1)
ActiveChart.ChartTitle.Text = ActiveSheet.Range("C" & RowS)
ActiveChart.ChartTitle.Characters.Font.Size = 10
'X axis name
ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = True
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = ActiveSheet.Range("v1")
'y-axis name
ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = ActiveSheet.Range("s1")
ActiveChart.Parent.Top = ActiveSheet.RowS(RowS).Top
ActiveChart.Parent.Left = ActiveSheet.Columns(40).Left
ActiveChart.Parent.Width = ActiveSheet.Range("ad" & RowS & ":aj" & RowE1).Width
ActiveChart.Parent.Height = ActiveSheet.Range("ad" & RowS & ":aj" & RowE1).Height
ActiveSheet.RowS(RowE).Borders(xlEdgeBottom).LineStyle = xlNone
ActiveSheet.RowS(RowE1).Borders(xlEdgeBottom).Weight = xlMedium
End If
End Sub
2016年06月29日 14点06分
15
Dim tagmean, SumTotE, SumpolyE, SumexpE, SumlogE, polyR, ExpR, LogR, linearR, SumlinE As Double
tagmean = Application.WorksheetFunction.Average(Tagvec)
For i = 1 To Uend
SStot(i) = (Tagvec(i) - tagmean) ^ 2
SSpolyReg(i) = (Tagvec(i) - PolyT(i)) ^ 2
'SSexpReg(i) = (ExpT(i) - tagmean) ^ 2
SSLinReg(i) = (Tagvec(i) - linR(i)) ^ 2
SSlogReg(i) = (Tagvec(i) - LogT(i)) ^ 2
Next
SumTotE = Application.WorksheetFunction.Sum(SStot)
SumpolyE = Application.WorksheetFunction.Sum(SSpolyReg)
SumlinE = Application.WorksheetFunction.Sum(SSLinReg)
SumlogE = Application.WorksheetFunction.Sum(SSlogReg)
polyR = 1 - SumpolyE / SumTotE
linearR = 1 - SumlinE / SumTotE
LogR = 1 - SumlogE / SumTotE
For i = 1 To Uend
ActiveSheet.Range("W" & RowS + DLen + i - 1).Value = MedvecLN(i)
ActiveSheet.Range("X" & RowS + DLen + i - 1).Value = MedvecSQRT(i)
ActiveSheet.Range("Y" & RowS + DLen + i - 1).Value = linR(i)
ActiveSheet.Range("Z" & RowS + DLen + i - 1).Value = PolyT(i)
ActiveSheet.Range("AA" & RowS + DLen + i - 1).Value = LogT(i)
'ActiveSheet.Range("Y" & RowS + DLen + i - 1) = PolyT(i)
'ActiveSheet.Range("AB" & RowS + DLen + i - 1) = LogT(i)
Next
If Err.Number = 0 Then
ActiveSheet.Range("AB" & RowS + 1) = "Linear Fitting"
ActiveSheet.Range("AB" & RowS + 2) = "Polynomial Fitting"
ActiveSheet.Range("AB" & RowS + 3) = "logarithmic Fitting"
ActiveSheet.Range("ac" & RowS) = "coeff 1"
ActiveSheet.Range("ad" & RowS) = "coeff 2"
ActiveSheet.Range("ae" & RowS) = "coeff 3"
ActiveSheet.Range("af" & RowS) = "R2"
ActiveSheet.Range("ac" & RowS + 1) = La1
ActiveSheet.Range("ad" & RowS + 1) = Lb1
ActiveSheet.Range("ac" & RowS + 2) = poa1
ActiveSheet.Range("ad" & RowS + 2) = pob1
ActiveSheet.Range("ae" & RowS + 2) = pob2
If ActiveSheet.Range("v" & RowS + DLen).Value > 0 Then
ActiveSheet.Range("ac" & RowS + 3) = LinA1
ActiveSheet.Range("ad" & RowS + 3) = LinB1
ActiveSheet.Range("af" & RowS + 3) = LogR
End If
ActiveSheet.Range("af" & RowS + 1) = linearR
ActiveSheet.Range("af" & RowS + 2) = polyR
' drawing a chart for logodds vs x_median
ActiveSheet.Range("Q" & RowS & ":Q" & RowE).Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmooth
ActiveChart.PlotArea.Select
' logodds vs x_median
ActiveChart.SeriesCollection(1).Name = ActiveSheet.Range("s1")
ActiveChart.SeriesCollection(1).Values = ActiveSheet.Range("s" & RowS + DLen & ":s" & RowE)
ActiveChart.SeriesCollection(1).XValues = ActiveSheet.Range("v" & RowS + DLen & ":v" & RowE)
' Linear_est_logodds vs x_median
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = ActiveSheet.Range("y1")
ActiveChart.SeriesCollection(2).Values = ActiveSheet.Range("y" & RowS + DLen & ":y" & RowE)
ActiveChart.SeriesCollection(2).XValues = ActiveSheet.Range("v" & RowS + DLen & ":v" & RowE)
ActiveChart.SeriesCollection(2).ChartType = xlXYScatterSmoothNoMarkers
' Poly_est_logodds vs x_median
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Name = ActiveSheet.Range("z1")
ActiveChart.SeriesCollection(3).Values = ActiveSheet.Range("z" & RowS + DLen & ":z" & RowE)
ActiveChart.SeriesCollection(3).XValues = ActiveSheet.Range("v" & RowS + DLen & ":v" & RowE)
ActiveChart.SeriesCollection(3).ChartType = xlXYScatterSmoothNoMarkers
' Log_est_logodds vs x_median
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(4).Name = ActiveSheet.Range("aa1")
ActiveChart.SeriesCollection(4).Values = ActiveSheet.Range("aa" & RowS + DLen & ":aa" & RowE)
ActiveChart.SeriesCollection(4).XValues = ActiveSheet.Range("v" & RowS + DLen & ":v" & RowE)
ActiveChart.SeriesCollection(4).ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.ApplyLayout (1)
ActiveChart.ChartTitle.Text = ActiveSheet.Range("C" & RowS)
ActiveChart.ChartTitle.Characters.Font.Size = 10
'X axis name
ActiveChart.Axes(xlCategory, xlPrimary).HasTitle = True
ActiveChart.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = ActiveSheet.Range("v1")
'y-axis name
ActiveChart.Axes(xlValue, xlPrimary).HasTitle = True
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = ActiveSheet.Range("s1")
ActiveChart.Parent.Top = ActiveSheet.RowS(RowS).Top
ActiveChart.Parent.Left = ActiveSheet.Columns(40).Left
ActiveChart.Parent.Width = ActiveSheet.Range("ad" & RowS & ":aj" & RowE1).Width
ActiveChart.Parent.Height = ActiveSheet.Range("ad" & RowS & ":aj" & RowE1).Height
ActiveSheet.RowS(RowE).Borders(xlEdgeBottom).LineStyle = xlNone
ActiveSheet.RowS(RowE1).Borders(xlEdgeBottom).Weight = xlMedium
End If
End Sub