CN110377893A - Excel透视表结合VBA一键生成材料表的方法 - Google Patents

Excel透视表结合VBA一键生成材料表的方法 Download PDF

Info

Publication number
CN110377893A
CN110377893A CN201910627875.7A CN201910627875A CN110377893A CN 110377893 A CN110377893 A CN 110377893A CN 201910627875 A CN201910627875 A CN 201910627875A CN 110377893 A CN110377893 A CN 110377893A
Authority
CN
China
Prior art keywords
excel
vba
key
pivot table
mono
Prior art date
Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
Pending
Application number
CN201910627875.7A
Other languages
English (en)
Inventor
尹寅政
林卫
张剑军
杨茂盛
杨立瑞
Current Assignee (The listed assignees may be inaccurate. Google has not performed a legal analysis and makes no representation or warranty as to the accuracy of the list.)
CHINA CEC ENGINEERING Corp
Original Assignee
CHINA CEC ENGINEERING Corp
Priority date (The priority date is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the date listed.)
Filing date
Publication date
Application filed by CHINA CEC ENGINEERING Corp filed Critical CHINA CEC ENGINEERING Corp
Priority to CN201910627875.7A priority Critical patent/CN110377893A/zh
Publication of CN110377893A publication Critical patent/CN110377893A/zh
Pending legal-status Critical Current

Links

Classifications

    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F40/00—Handling natural language data
    • G06F40/10—Text processing
    • G06F40/166—Editing, e.g. inserting or deleting
    • G06F40/183—Tabulation, i.e. one-dimensional positioning
    • G—PHYSICS
    • G06—COMPUTING; CALCULATING OR COUNTING
    • G06F—ELECTRIC DIGITAL DATA PROCESSING
    • G06F40/00—Handling natural language data
    • G06F40/10—Text processing
    • G06F40/166—Editing, e.g. inserting or deleting
    • G06F40/186—Templates

Landscapes

  • Engineering & Computer Science (AREA)
  • Theoretical Computer Science (AREA)
  • Health & Medical Sciences (AREA)
  • Artificial Intelligence (AREA)
  • Audiology, Speech & Language Pathology (AREA)
  • Computational Linguistics (AREA)
  • General Health & Medical Sciences (AREA)
  • Physics & Mathematics (AREA)
  • General Engineering & Computer Science (AREA)
  • General Physics & Mathematics (AREA)
  • Management, Administration, Business Operations System, And Electronic Commerce (AREA)

Abstract

本发明涉及工程领域一种透视表结合Excel‑VBA对材料表Bill of Materials（简称BOM)进行数据处理，汇总并一键直接输出材料统计单Material take‑off（简称MTO）的方法。包括以下步骤：（1）建立BOM，对各类型材料分别新建工作表并建立数据透视表，对上述数据透视表进行排序及统计，值汇总依据为求和；（2）通过Excel公式，将上述材料通过excel公式引用到材料表，公式需包含判断透视表最后一行并返回空值的功能；（3）在材料表中的辅助列中加入上述不同类型起始行的标签；（4）通过Excel‑VBA编程，刷新整个工作簿，并自动删除不同类型分界处多余的空白行或增加缺失的材料行，保证不同类型接头处有若干空白行，增加材料采购辨识性。

Description

Excel透视表结合VBA一键生成材料表的方法
发明领域
本发明涉及材料统计单方法，具体涉及一种透视表结合Excel-VBA编程方法进行数据处理，汇总并一键直接输出MTO成品的方法。
背景技术
材料表Bill of Materials（简称BOM)广泛应用于工程领域在装配图上便于从库房抽取材料进行施工。材料统计单Material take-off（简称MTO）则为采购阶段的主要文件。一般情况下，BOM含有若干类型材料，每种类型材料又含有不同的规格及材料属性。MTO可以通过三维软件定制直接生成，但目前CAD二维设计仍然广泛用于中小型设计项目。
主流的材料统计方法有纯手工即用计算器手工累加各个类型管道组成件（管子，阀门，其他阀门，法兰、弯头、三通、异径管、垫片、螺栓、螺母等），并手工按材质分类并按管径排序制成成品材料表。另一种是利用Excel的筛选功能，即代替第一种方法的计手工计算器累加步骤。第二种方法较第一种方法速度较快且准确性有所提高，但一个项目平均也需要几个小时的工作量，且一旦项目发生材料的修改，需要人工记录修改的内容，并人工去初步判断因此产生的材料量影响，然后针对以上影响区域的材料进行再次统计。因此上述方法均有很大的人工工作量，简而言之费时费力且易出错。
发明内容
本发明目的在于针对二维设计材料统计费时费力的现状，提供一种Excel中透视表结合Excel-VBA一键生成MTO的方法，制成模板后便于工程使用，节省工程设计周期。
本发明的技术方案如下：
Excel透视表结合VBA一键生成材料表的方法，包括以下步骤：
（1）建立BOM，对各种类型的材料分别新建工作表并建立数据透视表，对上述数据透视表进行排序及统计，值汇总依据为求和；
（2）通过Excel公式，将上述材料通过excel公式引用到材料表，公式需包含判断透视表最后一行并返回空值的功能；
（3）在材料表中的辅助列中加入上述不同类型起始行的标签；
（4）通过Excel-VBA编程，刷新整个工作簿，并自动删除上述类型分界处多余的空白行或增加缺失的材料行，保证上述类型接头处若干空白行，增加材料采购辨识性。
步骤（1）中所述的数据透视表按材质及标准号分类后按照规格进行“升序”并将“值筛选设置为不等于0”，汇总依据设置为“求和”，字段根据MTO风格设置“重复项目标签”及“在每个项目标签后插入空行” 与否。
步骤（2）中所述的公式对超出透视表实质内容范围的引用返回结果为空值。
步骤（3）中所述的标签可以是任何非空值。
步骤（4）中所述代码可以实现一键刷新整个工作簿且控制接头处空白行数量为2但不限于2。
本发明的有益效果：充分利用Excel本身的功能，减少编程的工作量，便于普通人操作及修改；制成模板后可以一键生成MTO，无需其他人工工作量，大大减轻设计人员工作负荷。
附图说明
图1为适用于生成透视表的管段一览表。
图2为管子的透视表。
图3为材料表成品的一部分截图。
图4为Excel-VBA编程代码截图。
图5为Excel-VBA代码的程序框图。
具体实施方式
下面结合附图和实施例（管道材料统计）对本发明进行进一步的描述。
如图1所示，管段一览表（BOM）按管道组成件类型依次排列，其中基本每种类型有材质、数量及规格的列。
如图2所示，管子的透视表按材质及标准号分类后按照管径或规格进行“升序”并将“值筛选设置为不等于0”，汇总依据设置为“求和”，字段根据材料表成品的风格设置“重复项目标签”及“在每个项目标签后插入空行”。
如图3所示，通过Excel公式引用各个透视表的数据（标准、规格及数量）至材料表，并利用Excel公式控制输出引用结果，一旦引用超出透视表范围，返回空值，并且一旦接头处数据未达到透视表最后一行（即未显示全所有数据），可以通过复制接头处最后一行，插入到此行后面可以显示下行未显示内容，此复制粘贴可以人工重复操作直至显示到最后一行（即出现空白行），但比较机械，因此有必要采用VBA代码自动控制。并在材料表中阀门及之后类型的首行加入“1”或其他非空字符作为起始行标签行，作为VBA代码控制接头处空白行的依据。右上角的按钮为该VBA代码运行按钮，点击即可运行该代码。
如图4所示，可参照其中代码，但不局限于图中所示代码。
如图5所示，介绍VBA工作的流程。
Excel透视表结合VBA一键生成材料表的方法，包括以下步骤：
（1）建立BOM，对各种类型的材料分别新建工作表并建立数据透视表，本例为生成管子、阀门、其他阀门等数据透视表，透视表中包括型号、材质、规格、数量等字段（其中数量放入值区域），对上述数据透视表进行排序及统计，值汇总依据为求和；
（2）通过Excel公式，将上述材料通过excel公式引用到材料表，公式需包含判断透视表最后一行并返回空值的功能；
（3）在材料表中的辅助列（本实施例为第一列）中加入上述不同类型起始行的标签；
（4）通过Excel-VBA编程，刷新整个工作簿，并自动删除上述类型分界处多余的空白行或增加缺失的材料行，保证上述类型接头处若干空白行，增加材料采购辨识性。
步骤（1）中所述的数据透视表管子的透视表按材质及标准号分类后按照管径或规格进行“升序”并将“值筛选设置为不等于0”，汇总依据设置为“求和”，字段根据MTO风格设置“重复项目标签”及“在每个项目标签后插入空行”与否。
步骤（2）中所述的公式对超出透视表实质内容范围的引用返回结果为空值。
步骤（3）中所述的标签可以是任何非空值。
步骤（4）中所述代码可以实现一键刷新整个工作簿且控制接头处空白行数量为2但不限于2。

Claims (5)

1.Excel透视表结合VBA一键生成材料表的方法，其特征在于包括以下步骤：
（1）建立BOM，对各种类型的材料分别新建工作表并建立数据透视表，对上述数据透视表进行排序及统计，值汇总依据为求和；
（2）通过Excel公式，将上述材料通过excel公式引用到材料表，公式需包含判断透视表最后一行并返回空值的功能；
（3）在材料表中的辅助列中加入上述不同类型起始行的标签；
（4）通过Excel-VBA编程，刷新整个工作簿，并自动删除上述类型分界处多余的空白行或增加缺失的材料行，保证上述类型接头处若干空白行，增加材料采购辨识性。
2.如权利要求1所述的Excel透视表结合VBA一键生成材料表的方法，其特征在于：步骤（1）中所述的数据透视表按材质及标准号分类后按照规格进行“升序”并将“值筛选设置为不等于0”，汇总依据设置为“求和”，字段根据材料表成品的风格设置“重复项目标签”及“在每个项目标签后插入空行”。
3.如权利要求1所述的Excel透视表结合VBA一键生成材料表的方法，其特征在于：步骤（2）中所述的公式对超出透视表实质内容范围的引用返回结果为空值。
4.如权利要求1所述的Excel透视表结合VBA一键生成材料表的方法，其特征在于：步骤（3）中所述的标签可以是任何非空值。
5.如权利要求2或3或4所述的Excel透视表结合VBA一键生成材料表的方法，其特征在于：步骤（4）中所述代码可以实现一键刷新整个工作簿且控制接头处空白行数量为2但不限于2。
CN201910627875.7A 2019-07-12 2019-07-12 Excel透视表结合VBA一键生成材料表的方法 Pending CN110377893A (zh)

Priority Applications (1)

Application Number Priority Date Filing Date Title
CN201910627875.7A CN110377893A (zh) 2019-07-12 2019-07-12 Excel透视表结合VBA一键生成材料表的方法

Applications Claiming Priority (1)

Application Number Priority Date Filing Date Title
CN201910627875.7A CN110377893A (zh) 2019-07-12 2019-07-12 Excel透视表结合VBA一键生成材料表的方法

Publications (1)

Publication Number Publication Date
CN110377893A true CN110377893A (zh) 2019-10-25

Family

ID=68252855

Family Applications (1)

Application Number Title Priority Date Filing Date
CN201910627875.7A Pending CN110377893A (zh) 2019-07-12 2019-07-12 Excel透视表结合VBA一键生成材料表的方法

Country Status (1)

Country Link
CN (1) CN110377893A (zh)

Citations (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US20070022128A1 (en) * 2005-06-03 2007-01-25 Microsoft Corporation Structuring data for spreadsheet documents
CN104541271A (zh) * 2012-08-10 2015-04-22 微软公司 从电子表格数据生成场景和游览
CN109669933A (zh) * 2018-12-10 2019-04-23 平安科技（深圳）有限公司 交易数据智能处理方法、装置及计算机可读存储介质

Patent Citations (3)

* Cited by examiner, † Cited by third party
Publication number Priority date Publication date Assignee Title
US20070022128A1 (en) * 2005-06-03 2007-01-25 Microsoft Corporation Structuring data for spreadsheet documents
CN104541271A (zh) * 2012-08-10 2015-04-22 微软公司 从电子表格数据生成场景和游览
CN109669933A (zh) * 2018-12-10 2019-04-23 平安科技（深圳）有限公司 交易数据智能处理方法、装置及计算机可读存储介质

Similar Documents

Publication Publication Date Title
Sandberg Knowledge based engineering: In product development
JPH11134386A (ja) 材物取扱いシステムをモデル化する方法及び装置
CN109885967B (zh) 一种抗震支吊架设计的深化方法
CN1763774A (zh) 一种流程工业可视化生产工艺流程描述的建模方法
CN107688710A (zh) 基于Revit平台的阀门参数化建族方法
CN105320806A (zh) PML结合Excel-VBA处理数据汇总核电工艺管道材料的方法
CN101763066A (zh) 飞机复杂构件数控加工工装典型件管理系统及方法
CN116821223A (zh) 基于数字孪生的工业可视化控制平台及方法
CN107256315A (zh) 一种管道应力分析电算图的智能绘制方法
Napolskikh et al. Modeling of regional economic development based on innovative clusters
CN106408178A (zh) 基于多维度数据快照的产品bom管理方法与装置
CN110377893A (zh) Excel透视表结合VBA一键生成材料表的方法
Cheu Procedures for shape optimization of gas turbine disks
Ram et al. Stochastic design exploration with rework of flexible manufacturing system under copula-coverage approach
Guo et al. Development of bulk material management system and research on material balance applications based on business intelligence
Zhang et al. Augmented reality-based auxiliary device for workshop logistics delivery
Ma et al. The design of JLAMT: an aided tool for large-scale complex physical modeling
Ristevski et al. Technical aspect of CAD and BIM technology in the engineering environment
Song et al. Establishment of Inspection Information Model Based on Secondary Development with CATIA
Paprotny et al. Reducing model creation cycle time by automated conversion of a CAD AMHS layout design
Haberl et al. An overview of 3-D graphical analysis using DOE-2 hourly simulation data
Zhongyi et al. Research on knowledge-based system for typical aircraft composite component design
Yu The Research of Integration of Design and Analysis Based on 3D Design Platform of NPP
Kinnucan Computer-Aided Manufacturing aims for integration
SHENTU et al. Framework and data management for digital nuclear power design system

Legal Events

Date Code Title Description
PB01 Publication
PB01 Publication
SE01 Entry into force of request for substantive examination
SE01 Entry into force of request for substantive examination
WD01 Invention patent application deemed withdrawn after publication
WD01 Invention patent application deemed withdrawn after publication

Application publication date: 20191025