以MS-Excel VBA彩繪森林圖

Drawing Color Forest Plots Using MS-Excel VBA

李英誠(Ying-Cheng Lee) ； 蔡崇煌(Chung-Huang Tsai) ； 王俊堯(Jiun-Yao Wang)

《澄清醫護管理雜誌》 17卷1期 (2021/01) Pp. 8-16

摘要

目的：大部份套裝軟體所畫之森林圖（Forest Plot）少能做變化，微軟Excel可以重製且多富變化，但過程較繁瑣，本研究嘗試使用Excel 2019及其VBA及自訂表單（UserForm）功能，設計一套程式，以快速彩繪森林圖。方法：以PubMed及思博網中文電子期刊（CEPS）搜尋題目關鍵詞為“Excel＂ AND ＂Meta Analysis或Meta-Analysis＂、“Excel＂ AND ＂Forest Plot＂。有關已發表文獻所呈現的森林圖，主要參考新英格蘭醫學雜誌（NEJM）、刺胳針（Lancet）及英國醫學雜誌（BMJ）等期刊。所用統計分析軟體為Review Manager 5.3。結果：此方面相關的文獻較缺乏，大部分皆是探討如何使用Excel計算效應量及95％CI，畫森林圖皆非其重點。由期刊上搜尋到的圖形皆可用微軟Excel重製，但過程繁瑣，因此我們進一步以Excel VBA及UserForm功能，設計一套作圖程式，可輕易繪製彩色、不同形狀、不同權重、可鏤空的標記（Marker）、及彩色次族群分析線之森林圖，亦可修改X軸，並自動加上箭頭及取Log等功能。結論：使用Excel製圖可以符合國際學術期刊的呈現法，再加上VBA設計，可在數秒內快速繪製不同型式且易修改的透明背景彩色森林圖。

關鍵字

英國醫學雜誌 ； 森林圖 ； 刺胳針 ； 統合分析 ； 新英格蘭醫學雜誌

並列摘要

Purposes: Few variations can be made in forest plots drawn with most currently available software packages. In contrast, Microsoft Excel allows forest plots to be reproduced and modified in various ways. However, complex procedures are involved. In this research, the VBA and UserForm functions of Excel 2019 were adopted to design a program in order to create forest plots quickly. Methods: Previous studies were found via PubMed and Chinese Electronic Periodical Services (CEPS) using topic keyword combinations including ＂Excel＂ AND ＂Meta-Analysis (or Meta Analysis)＂ and ＂Excel＂ AND ＂forest plot＂. Forest plots were adopted from papers published in the New England Journal of Medicine, the Lancet, and the British Medical Journal. Review Manager 5.3 was used for statistical analysis. Results: Unfortunately, related studies are scarce. Most research mainly examined how to calculate the effect size and the 95% confidence interval (CI), but not how to draw forest plots. Although all figures found in journals can be reproduced using Microsoft Excel, the procedures required are extremely complex. Therefore, Excel VBA and Userform were utilized to design a drawing program. Markers in various colors and shapes and with different weights or even hollow markers can be created. Forest plots with subgroup lines in different colors can also be drawn. In addition, the x-axis can be modified, the outlier arrows can be added automatically, and log axes can be created. Conclusions: Forest plots drawn with Excel can satisfy the requirements of international academic journals. Moreover, with the help of VBA, easily modifiable color forest plots of different types with transparent backgrounds can be created quickly within a few seconds.

並列關鍵字

The British Medical Journal ； Forest plot ； The Lancet ； Meta-analysis ； The New England Journal of Medicine