Stata软件使用教程用putexcel命令来创建Excel表格宏，图片，矩阵和公式表达式

今天，我们讲解一下如何写出更复杂的表达式，比如宏，图表和矩阵。甚至是如何在Excel中通过编写公式来创建计算单元。在Excel中，这些是我们实现 表自动生成的重要步骤。

在开始讲解案例前，我先创建一个putexcel2.xlsx的单独文件，并把每个案例写到单独的工作表中。putexcel set命令如下：

putexcel set putexcel2.xlsx, sheet(example1) replace

putexcel set putexcel2.xlsx, sheet(example2) modify

…

putexcel set putexcel2.xlsx, sheet(example8) modify

我可以为每个案例创建一个独立的Excel文件，但是我更喜欢一个文件中包含多个工作表。当你创建大型 表的时候我想你也会喜欢使用这种方法。

输入webuse nhanes2可以下载案例中所需的数据集。我将使用数据集的子集并且重新标记一些变量，所以不必介意你的数字是否与我的完全一致。

案例1将返回结果写入Excel

STATA的很多命令可以返回标量，宏和矩阵。我们可以使用putexcel命令将这些写到一个Excel表中。比如，我可能想把年龄的平均值写到Excel里，那么我可以在summarize age之后输入return list来查看返回结果列表。这时平均值被保存在标量r（mean）中。

summarize age

Variable | Obs Mean Std. Dev. Min Max

————-+———————————————————

age | 1,266 48.44076 16.98858 20 74

. return list

scalars:

r(N) = 1266

r(sum_w) = 1266

r(mean) = 48.44075829383886

r(Var) = 288.6119026656426

r(sd) = 16.98858153777539

r(min) = 20

r(max) = 74

r(sum) = 61326

设置putexcel2.xlxs中“example1”工作表的目标文件，然后可以在单元格A1中写入表达式”Mean Age = “，在单元格B1中写入表达式`r(mean) ‘。注意r(mean) 要用单引 括起来。这是告诉STATA我想把r(mean)值写在单元格B2中。

putexcel set putexcel2.xlsx, sheet(example1) replace

Note: file will be replaced when the first putexcel command is issued

. putexcel A1 = “Mean Age = “

file putexcel2.xlsx saved

. putexcel B1 = `r(mean)’

file putexcel2.xlsx saved

打开生成的Excel文件，发现平均值已经成功的写到了单元格B2中。

案例2：用Excel格式格式化数字

可以使用Excel格式中的nformat()选项来指定一个数字的显示格式。比如，可以使用nformat(“#.###”)这个选项使平均值显示到小数点后三位。

putexcel set putexcel2.xlsx, sheet(example2) modify

. putexcel A1 = “Mean Age = “

file putexcel2.xlsx saved

. putexcel B1 = `r(mean)’, nformat(“#.###”)

file putexcel2.xlsx saved

可以在[P]putexcel advanced选项部分查看到Excel格式选项的完整描述。

案例3：用STATA设计数字格式

STATA包含许多快捷格式代码可与nformat()一起使用。比如，可以使用number_d2选项使平均值显示到小数点后两位。

putexcel set putexcel2.xlsx, sheet(example3) modify

. putexcel A1 = “Mean Age = “

file putexcel2.xlsx saved

. putexcel B1 = `r(mean)’, nformat(number_d2)

file putexcel2.xlsx saved

可以在附录[P]putexcel中查看完整的数字格式代码表。

案例4：用string()功能格式化数字

我也可以让平均值和标准偏差输出在一个单元格内。这个功能可以通过2个步骤实现。首先，将r(mean)和r(sd)分别保存在本地宏meanage和sdage中。string()功能允许我指定到小数点后一位。第二步，创建一个名为meansd的本地宏，将meanage和sdage合并成一个表达式。注意使用putexcel B1 = “`meansd’”命令时必须使用双引 ，因为meansd是一个字符串。

. // put the mean of age in a cell using the string() function

. putexcel set putexcel2.xlsx, sheet(example4) modify

. putexcel A1 = “Mean (SD) = “

file putexcel2.xlsx saved

. local meanage = string(`r(mean)’,”%9.1f”)

. local sdage = string(`r(sd)’,”%9.1f”)

. local meansd = “`meanage’ (`sdage’)”

. putexcel B1 = “`meansd'”

file putexcel2.xlsx saved

案例5：在Excel文档中添加图表

我可能希望我的Excel文件中包含一个年龄的柱形图。首先，创建一个柱形图并且使用graph export命令将图表保存成 .png格式文件。

histogram age

(bin=31, start=20, width=1.7419355)

. graph export age.png, replace

(file age.png written in PNG format)

然后，使用picture(age.png)表达式将图表放到表格中。

. putexcel set putexcel2.xlsx, sheet(example5) modify

. putexcel A1 = picture(age.png)

file putexcel2.xlsx saved

案例6：为Excel编写矩阵

一些STATA命令可以返回矩阵。比如，我可以使用tabstat计算变量列表的描述性统计。Save选项会告诉tabstat将结果保存为矩阵。

. tabstat age height weight sbp dbp, stat(n mean sd min max) save

stats | age height weight sbp dbp

———+————————————————–

N | 1266 1266 1266 1267 1267

mean | 48.44076 167.1308 72.17252 130.6843 80.54854

sd | 16.98858 9.61487 16.28185 24.34302 13.99187

min | 20 144.199 39.12 65 35

max | 74 193.398 175.88 254 150

————————————————————

当输入teturn list命令时，会看到tabstat返回矩阵r（StatTotal）。

. return list

matrices:

r(StatTotal) : 5 x 5

. matlist r(StatTotal)

| age height weight sbp dbp

————-+——————————————————-

N | 1266 1266 1266 1267 1267

mean | 48.44076 167.1308 72.17252 130.6843 80.54854

sd | 16.98858 9.61487 16.28185 24.34302 13.99187

min | 20 144.199 39.12 65 35

max | 74 193.398 175.88 254 150

我更愿意创建一个Excel表，看起来像是从summarize输出的行的变量和列的统计。所以我创建了一个名为results的矩阵，等同于对r(StatTotal)进行置换。

. matrix results = r(StatTotal)’

. matlist results

| N mean sd min max

————-+——————————————————-

age | 1266 48.44076 16.98858 20 74

height | 1266 167.1308 9.61487 144.199 193.398

weight | 1266 72.17252 16.28185 39.12 175.88

sbp | 1267 130.6843 24.34302 65 254

dbp | 1267 80.54854 13.99187 35 150

然后使用matrix(results)表达式在Excel中写入矩阵results。我使用

matrix(r(StatTotal)’) 表达式，而不是去创建一个新的矩阵，在写入Excel之前我想向你们展示转置矩阵。矩阵的左上角将放在Excel表格的A1单元格中。names选项告诉putexcel运用矩阵写入Excel行和列的名称。nformat(number_d2) 选项告诉putexcel展示矩阵到小数点后两位。

. putexcel set putexcel2.xlsx, sheet(example6) modify

. putexcel A1 = matrix(results), names nformat(number_d2)

file putexcel2.xlsx saved

下一步，我想排版一下Excel表格，使它看起来更像一个结果表，而不是一个矩阵。不在单元格中写入任何内容就可以更改单元格的格式，甚至可以使用语法ul:br对单元格的范围进行设置，ul在单元格的左上角，br在单元格的右下角。

单元格B2:B6中每个变量的样本大小不需要显示到小数点后两位，所以我用nformat(number)选项使单元格B2:B6不显示小数点后两位。Overwritefmt选项告诉putexcel覆盖现有的单元格格式。

. putexcel B2:B6, nformat(number) overwritefmt

file putexcel2.xlsx saved

然后，设置A1：A6单元格。right选项可以使单元格的内容右对齐，border(right)选项可以为单元格的右侧添加边框。

. putexcel A1:A6, right border(right) overwritefmt

file putexcel2.xlsx saved

可以用类似的方法设置A1:F1单元格。hcenter选项可以使标签水平居中对齐，border(bottom)选项可以在单元格A1:F1下方增加边框。

. putexcel A1:F1, hcenter bold border(bottom) overwritefmt

file putexcel2.xlsx saved

最后，对B2:F6单元格的数字显示字体加粗，使表格看起来像summarize输出的结果。

. putexcel B2:F6, bol0064

file putexcel2.xlsx saved

排版后的Excel表格看起来是这样：

案例7：将回归系数写到Excel中

大多数Stata回归命令在r(table)的矩阵中返回系数表。比如，可以使用regress拟合以下线性回归模型。

. regress sbp age sex

Source | SS df MS Number of obs = 1,266

————-+———————————- F(2, 1263) = 236.23

Model | 204030.521 2 102015.261 Prob > F = 0.0000

Residual | 545432.459 1,263 431.854678 R-squared = 0.2722

————-+———————————- Adj R-squared = 0.2711

Total | 749462.98 1,265 592.460854 Root MSE = 20.781

——————————————————————————

sbp | Coef. Std. Err. t P>|t| [95% Conf. Interval]

————-+—————————————————————-

age | .7328247 .0343946 21.31 0.000 .6653479 .8003014

sex | 5.292591 1.170872 4.52 0.000 2.995522 7.58966

_cons | 92.6976 1.852992 50.03 0.000 89.06232 96.33289

——————————————————————————

并且输入matlist r(table)来查看系数矩阵。

. matlist r(table)

| age sex _cons

————-+———————————

b | .7328247 5.292591 92.6976

se | .0343946 1.170872 1.852992

t | 21.30641 4.520211 50.02589

pvalue | 2.65e-86 6.76e-06 6.9e-302

ll | .6653479 2.995522 89.06232

ul | .8003014 7.58966 96.33289

df | 1263 1263 1263

crit | 1.961844 1.961844 1.961844

eform | 0 0 0

r(table)包含df,crit和eform行，这些在回归输出时是不显示的。我想在Excel表中复制系数表，那么我要将r(table)保存到results矩阵中，提取results的前6行，然后对results进行置换。

. matrix results = r(table)

. matrix results = results[1..6,1…]’

. matlist results

| b se t pvalue ll ul

————-+——————————————————————

age | .7328247 .0343946 21.30641 2.65e-86 .6653479 .8003014

sex | 5.292591 1.170872 4.520211 6.76e-06 2.995522 7.58966

_cons | 92.6976 1.852992 50.02589 6.9e-302 89.06232 96.33289

现在，可以将results写入Excel文件中了。

. putexcel set putexcel2.xlsx, sheet(example8) modify

. putexcel A1 = matrix(results), names nformat(number_d2) hcenter

file putexcel2.xlsx saved

对字体和单元格格式的一些调整使矩阵看起来更像一个系数表。

. putexcel B2:G4, bold overwritefmt

file putexcel2.xlsx saved

. putexcel A1:A4, right border(right) overwritefmt

file putexcel2.xlsx saved

. putexcel A1:G1, hcenter border(bottom) overwritefmt

file putexcel2.xlsx saved

案例8：在Excel中写入交叉表

通过使用矩阵我们同样也可以将结果从tabulate写入Excel中。 matcell()选项保存矩阵tabulate中的单元格数量。比如，我可以在cellcounts矩阵下面保存tabulate命令结果。

. tabulate sex race, matcell(cellcounts)

| Race

Sex | Black Other White | Total

———–+———————————+———-

Female | 101 12 563 | 676

Male | 75 10 506 | 591

———–+———————————+———-

Total | 176 22 1,069 | 1,267

. matlist cellcounts

| c1 c2 c3

————-+———————————

r1 | 101 12 563

r2 | 75 10 506

通过以下步骤可以用sex的值标签来重新命名cellcounts的行名称。首先，sex保存为一个数值变量，因此可以使用decode来创建一个名为sex_s字符串变量。如果sex作为字符串变量保存的话那么我们可以省略掉这一步。第二步，使用levelsof保存sex_s的层级到本地宏sexlabels中。然后，使用matrix rownames标记cellcounts行，并把标签保存到sexlabels。

. decode sex, generate(sex_s)

. levelsof sex_s, local(sexlabels)

`”Female”‘ `”Male”‘

. matrix rownames cellcounts = `sexlabels’

. matlist cellcounts

| c1 c2 c3

————-+———————————

Female | 101 12 563

Male | 75 10 506

同样的步骤，使用race值标签来重新命名cellcounts列。

. decode race, generate(race_s)

. levelsof race_s, local(racelabels)

`”Black”‘ `”Other”‘ `”White”‘

. matrix colnames cellcounts = `racelabels’

. matlist cellcounts

| Black Other White

————-+———————————

Female | 101 12 563

Male | 75 10 506

就像上面2个例子一样可以把cellcounts写到Excel里。

. putexcel set putexcel2.xlsx, sheet(example9) modify

. putexcel A1 = matrix(cellcounts), names hcenter

file putexcel2.xlsx saved

这种方法是可行的，但是Excel表中没有行和列的合计数量。添加这个的方法就是使用formula()表达式把表中需要计算的单元格放进去。比如，putexcel下面第一行的单元格E2中放入Excel函数SUM(B2:D2)。这个就可以计算表中第一行的表格总数。下面的putexcel命令把公式放入表中计算出行和列的总量。

. putexcel E2 = formula(=SUM(B2:D2)) ///

> E3 = formula(=SUM(B3:D3)) ///

> B4 = formula(=SUM(B2:B3)) ///

> C4 = formula(=SUM(C2:C3)) ///

> D4 = formula(=SUM(D2:D3)) ///

> E4 = formula(=SUM(B2:D3)), hcenter

file putexcel2.xlsx saved

可以通过添加标签和边框直观的区分单元格中行和列的总数。同样也可以通过加粗字体来突出数字。

. putexcel A4 = “Total” E1 = “Total”, hcenter

file putexcel2.xlsx saved

. putexcel A1:A4 D1:D4, border(right) overwritefmt

file putexcel2.xlsx saved

. putexcel A1:E1 A3:E3, border(bottom) overwritefmt

file putexcel2.xlsx saved

. putexcel B2:E4, bold

file putexcel2.xlsx saved

Excel表格类似于tabulate输出的表格。

使用返回标量，宏，和矩阵，并用putexcel命令在Excel表格中重新创建的Stata输出是很容易。案例1-7中很好的概括了任意变量，但是在案例8中我硬编码了变量sex和race行和列的总数。