加载中…关联债务网络图可视化分析模型
(2015-07-02 15:02:58)
关联债务网络图可视化分析模型
会员朋友询问如何制作这样的图表（这里），他想用来反映16个子公司之间的相互债务往来情况。这些关联公司之间的关联债务错综复杂，令人头疼。
这是chandoo老师制作的一个朋友圈关系链分析图，用来反映三角债务关系还真是不错。于是，我弄出了本贴的关联债务网络图可视化分析模型。
模型构思
这个网络图的制作方法主要是散点图的高级运用，chandoo的具体做法没太看懂，我自己的做法可能更简单易懂，基于之前这几个帖子中的技术：
先在草稿纸上构思大致的样式和功能，如下图。（略）
模型功能可分为：
1、输入：输入债务关系数据。
2、计算：为模型的图表、表格准备数据。
3、输出：模型呈现给用户的界面，包括图表、表格、交互等。
图表是一种网络图，各公司呈环形分布，用连线显示各公司间的债务关系。表格是债务明细数据。交互设计是指，当选择某个公司时，模型突出显示该公司的所有债务关系。
有了模型思路和技术方案，就可以开始制作模型了。具体开发过程还是比较复杂的
，我自己用时3个整晚上，写这篇教程又是1个晚上。这里简述一下大致步骤，详细大家可对照xlsx源文件去研究。
模型制作
一、准备数据输入区。
Excel建模时，我们要有按功能分工作表或区域的思路。一般包括输入表、计算表、展现表3个工作表或区域，其他还可能会有参数表、帮助表、备忘表等。
在数据表设置一个20*20的表格区域，用来输入债务关系数据。注意债务关系方向，左侧为债权方，上方为债务方。上方的公司名称可用transpose从左侧转置过去。20表示最大支持到20个公司之间的债务关系。
这里，我定义了一个动态名称mydata，根据输入的公司个数（用counta计算得出），动态返回相应的数据区域，例如有16个公司，则mydata为自D7起16*16的区域。这个名称将极大地方便后续计算引用。
二、规划界面功能区。
根据草图，在 图表 工作表里规划布局，上方是标题区，左中右分别是选择框、图表、表格。
在左侧插入一个列表框控件，数据源为数据表里的公司名称列，结果链接到计算表的C12单元格，命名为cur_item，方便后续引用，并驱动模型。
其他留待后面再完成。
三、准备模型作图数据。
这是整个过程最复杂、最费脑的部分，如何构建出需要的序列，除了要具备一定的函数基础，更重要的是思路，还有一些数据知识。请对照源文件研究。
1、计算各公司位置点坐标xy值。我们把各公司的散点呈圆环式地分布在一个圆上。公司个数用counta动态得出，那么两个公司间的角度就是
360/公司个数，每个公司散点的（x，y）坐标就是（cosα，sinα）。这个坐标值xy将是后面反复用到的参数。
这个计算过程很智能，使模型能自动适应3~20个公司个数的情况，网络图始终是均匀圆环分布。
其中α的计算，使用公式=90-(F8-1)*$C$10，是为了让各公司散点从12点钟位置开始顺时针分布。
2、准备显示各公司名称的序列。
将刚才的xy数据*1.2引用过来，作为一组散点图序列，用来显示各公司名称。*1.2是为了让公司名称显示在网络图的外圈，不与网络图覆盖，显示效果最佳。
3、准备绘制所有债务关系连线的序列。
根据我们在物流地图里的技术，可以用一个散点图序列绘制出所有的连线，灰色线条，作为网络图的背景。这个序列的准备有一定难度。有些细节我注释在工作表里，可自行琢磨。
公式复制完后，需要利用Q列，筛选出为3的行，把v、w列的这些行删掉清空，这样散点图的连线才会断开。后面会反复用到这个筛选、清空、使连线断开的方法。
4、准备绘制当前所选公司的债务关系连线的序列。
当用户选择了某个公司时，这个序列用来突出显示该公司债务关系线条。可分为债权线和债务线，分别用绿色和红色，因此需要两个序列。
公式复制完后，也需要利用Q列，筛选出为3的行，把AD、AE、AI-AL列的这些行删掉清空，以使连线断开。
考虑到两个公司之间可能同时存在债权和债务关系，为避免债权线和债务线重合，对这种情况下的债务线坐标xy值做了一点偏移，见AK、AL列，避开债权线。这个是后续完善时才发现和实现的。
5、准备在线条上标示金额的序列。
我们为模型增加一个功能，当用户勾选 线上显示金额 时，模型就在线条上显示数值。
先在界面区插入一个复选框控件，结果链接到计算表的C16，表示是否显示金额标签。
这个序列的xy值可按每对债务关系公司的散点xy值的中间或1/3处计算而来。对应第4步，也需要用两个序列来做。并分别准备标签文本。
6、准备所选公司的债权债务数据明细表，用来显示在界面区右侧。
根据当前所选公司引用即可。
四、制作图表，完成模型。
数据准备好后，可以开始制作图表，现在反倒是不难了。
1、用3.2的公司名称序列做散点图，添加数据标签为公司名称，居中位置。无线条、无标记。
2、图表添加3.3的所有关系序列，绘制所有关系连线。灰色线条、蓝色标记点。
3、继续添加3.4的所选债权连线序列、所选债务连线序列，分别绘制所选公司的债权和债务关系连线。分别设置为绿色、红色线条，末端为箭头，代表资金流方向。
4、继续添加3.5的所选债权标签序列、所选债务标签序列，分别添加数据标签显示债权和债务的金额。无线条、无标记。
5、在图表中添加一个空序列，类型改为饼图，无填充色隐藏。这个技巧可使绘图区保持绝对正方形，那么各公司散点也就呈绝对圆环分布。
6、现在图表已完成，将图表复制放置到界面区的中间位置，调整大小合适。
7、将准备的明细数据引用到界面区的右侧位置，并对债权列、债务列分别设置数据条可视化。其中，设置数据条时要包含一个隐藏的max值单元格，以使所有数据条刻度成比例。
五、调试模型，确认正确。
调试和修改模型，检查功能和数据的正确性。前述各步骤是个反复调试、不断修改的过程。
六、优化细节，完善模型。
模型大体完成后，可以再逐步完善细节，特别是把界面区格式化好。除此以外，我还做了以下完善工作：
1、在3.2的数据位置旁，准备一个单点序列，xy为当前所选公司的xy，加入图表做散点图，添加数据标签显示为公司名称，标签为白色填充、红色字体，挡住之前的标签。这样可以响应用户选择，突出显示当前公司名，方便阅读识别。
2、用明细数据表的合计行，做一个小柱形图，放在图表区的左上角，对当前所选公司的债权债务进行显示和比较。其中，图表中顺便加入了隐藏的max序列，可使切换公司时柱形图能尽量保持刻度比例一致。
3、写好公式备注、模型备忘、使用说明、联系方式等。
4、隐藏计算表，保护工作表，避免用户误动模型。保护前要先设置数据输入区取消勾选 锁定。
七、发布模型。
现在模型已确认正确，细节也已完善，就可以发布模型了。
当你用这个网络图模型来呈现和演示众多子公司之间错综复杂的三角债务关系，一定是非常清晰、一目了然，可以帮助理解纷繁复杂的债务关系，做出更好的财务决策。
模型运用
模型设计为自动支持3~20个公司之间的债务关系呈现，用户不需要了解模型的制作原理和过程，只需正确输入数据就可以了。
这种形式的模型制作，已经是一个完整的建模过程，基本运用到了Excel绝大部分方面的知识。这种方法论可以用来开发各种Dashboard仪表板模板。
本例的债务关系网络图，也可以用来反映其他业务问题，如简单的社交网络关系分析、电信流量流向分析等，大家可以找找自己业务上的适用场景。
此外，我记得网上曾流传一个娱乐圈里各种乱搞、各种复杂的关系图，也可以用这个模型的方法试一试哦。
如何获得模型源文件
刘万祥
微博：@刘万祥ExcelPro
微信：iamExcelPro
博客：ExcelPro.blog.sohu.com
邮箱：ExcelPro2008@gmail.com
喜欢
0
赠金笔