Excel 365非VBA实现跨工作表动态提取特定区域数据技术问询

开发者阿泽

2026-6-15

需求说明

我使用Microsoft® Excel® for Microsoft 365 MSO（版本2412 Build 16.0.18324.20092）64位版本，需要整理导入的空格分隔数据集，核心需求：

从Sheet1提取两个关键词之间的文本，内容占用单元格数（4-10个）和位置均不固定
支持同一关键词的多实例提取

我尝试过的公式：

=FILTER((Sheet1!A1:Sheet1!Z250 = "SearchTerm1")*(Sheet1!A1,Sheet1!Z250 = "SearchTerm2")), "No information")

针对多实例的尝试：

=FILTER((Sheet1!A1:Sheet1!Z250 = "the first occurrence of a word")*(Sheet1!A1,Sheet1!Z250 = "the second occurrence of the same word")), "No information")

想确认：能否不使用VBA实现该需求？

补充数据集

我的数据集为空格分隔格式，样例如下：

Project   66 Marshes Parade, Casula    Run 0
Casula  PC 2170  Lat  -33.90  Long  150.90   Climate File Climat28.TXT

Dwelling
D P Number:          26304
Lot Number:          134
Street Number:       66
Unit Number:         
Street Name:         Marshes Parade
Development Name:    
Suburb:              Casula
State:               NSW
Postcode:            2170
NCC Class:           2

Plan
Plan Reference:      052/24 Rev E
Prepared By:         RHIZ0ME ST/AK

Assessor Details
Assessor Name:       My Name
AAO:                 Design Matters National
Assessor Number:     DMN/11/1111

Summary
Conditioned Area                 131.5 m² (126.7 m²)
Unconditioned Area               10.2 m² (8.9 m²)
Glazed Common Area               0.0 m² (0.0 m²)
Total Floor Area                 141.7 m² (135.6 m²)
Total Glazed Area                35.6 m²
Total External Solid door Area   1.8 m²
Glass to Floor Area              25.1 %
Gross External Wall Area         185.8 m²
Net External Wall Area           148.4 m²

Window
 2.4 m²  ALM-004-01 A DEFAULTS  Uval 4.80  SHGC 0.59
              Glass  Air Fill Clear-Clear
              Frame  ALM-004-01 A Aluminium B DG Air Fill Clear-Clear
10.1 m²  BRD-033-003-001 BRADNAMS  Uval 4.42  SHGC 0.62
              Glass  4EA
              Frame  Aluminium Sliding Door SG
11.6 m²  BRD-030-013-001 BRADNAMS  Uval 4.47  SHGC 0.51
              Glass  6.38CPClr
              Frame  Aluminium Hinged Door SG
 4.1 m²  BRD-112-001-001 BRADNAMS  Uval 6.54  SHGC 0.67
              Glass  4Clr
              Frame  Aluminium Awning Window SG
 5.0 m²  BRD-001-021-001 BRADNAMS  Uval 4.59  SHGC 0.67
              Glass  4ET
              Frame  Aluminium Sliding Window SG
 2.4 m²  BRD-112-005-001 BRADNAMS  Uval 5.19  SHGC 0.55
              Glass  4ET
              Frame  Aluminium Awning Window SG

External Wall
148.4 m²  Cavity Brick   R0.90 Bulk Insulation

Internal Wall
69.0 m²  Cavity brick  No Insulation both sides of Shaft liner
89.4 m²  Single Skin Brick  No Insulation
38.4 m²  Cavity brick  R 0.9 Bulk Insulation in the centre

External Floor
94.8 m²  Concrete Slab, Unit Below 150mm  Cork Tiles or Parquetry 8mm  No Insulation
2.4 m²  Concrete Slab on Ground 100mm  Cork Tiles or Parquetry 8mm  No Insulation

External Ceiling
41.5 m²  Concrete, Plasterboard with Timber Frame  R1.0 Bulk Insulation Unventilated roofspace
59.7 m²  Plasterboard on Timber  R4.0 Bulk Insulation Unventilated roofspace

Internal Floor/Ceiling
53.3 m²  Concrete Timber Framed Above Plasterboard  No Insulation

Roof (Horizontal area)
59.3 m²  Corrugated Iron Timber Frame  R 1.3 Bulk, Reflective Side Down, No Air Gap Above  35° slope   Skillion roof
37.9 m²  Corrugated Iron Timber Frame  R 1.3 Bulk, Reflective Side Down, No Air Gap Above  2° slope   Skillion roof

期望输出格式

需在另一工作表生成以下格式内容：

Project (inc DP number/Lot Number/Unit Number/Street number/Street Name/Development Name/Suburb/state/postcode)
Conditioned area value
Unconditioned area value
Glass to floor area value as percentage
Wall to floor area calculated as a percentage 
Window code and types listed with Uval & SHGC
External Wall Types and areas rounded
Internal Wall Types and areas rounded
External Floor Types Types and areas rounded
External Ceiling Types and areas rounded
Roof Types and areas

额外要求：

若存在无装修地面类型，需从非空调面积中扣除对应数值，单独列为「Unconditioned excluding garage」
兼容不同项目格式变化（最多20种窗户类型、10种墙体/地面/天花板/屋顶类型）

无VBA解决方案

一、通用关键词间内容提取（支持多实例）

针对提取两个关键词间的内容，结合TEXTJOIN、FILTER、MATCH等函数实现，以下是按行分布数据的处理方案：

1. 提取单组关键词间的所有内容

例如提取"Window"和"External Wall"之间的所有行：

=TEXTJOIN(CHAR(10),TRUE,FILTER(Sheet1!A1:A250,(ROW(Sheet1!A1:A250)>MATCH("Window",Sheet1!A1:A250,0))*(ROW(Sheet1!A1:A250)<MATCH("External Wall",Sheet1!A1:A250,0))))

MATCH定位两个关键词的行号
FILTER筛选行号在区间内的内容
TEXTJOIN用换行符拼接结果

2. 处理同一关键词的多实例

若存在多个相同起始/结束关键词（如多个"Window"区块），可批量提取所有区间内容：

=LET(
    start_pos, FILTER(ROW(Sheet1!A1:A250),Sheet1!A1:A250="Window"),
    end_pos, FILTER(ROW(Sheet1!A1:A250),Sheet1!A1:A250="External Wall"),
    result, TEXTJOIN(CHAR(10)&"---"&CHAR(10),TRUE,BYROW(SEQUENCE(ROWS(start_pos)),LAMBDA(x,TEXTJOIN(CHAR(10),TRUE,FILTER(Sheet1!A1:A250,(ROW(Sheet1!A1:A250)>INDEX(start_pos,x))*(ROW(Sheet1!A1:A250)<INDEX(end_pos,x))))))),
    IF(result="","No information",result)
)

LET定义变量简化公式结构
FILTER获取所有起始/结束关键词的行号
BYROW遍历每个区间，提取内容后用分隔符区分不同区块

二、针对项目数据集的定制提取

根据你的期望输出，以下是各字段的具体提取公式：

1. 项目信息整合

=LET(
    dp, XLOOKUP("D P Number:",Sheet1!A1:A250,Sheet1!B1:B250,""),
    lot, XLOOKUP("Lot Number:",Sheet1!A1:A250,Sheet1!B1:B250,""),
    unit, XLOOKUP("Unit Number:",Sheet1!A1:A250,Sheet1!B1:B250,""),
    street_num, XLOOKUP("Street Number:",Sheet1!A1:A250,Sheet1!B1:B250,""),
    street_name, XLOOKUP("Street Name:",Sheet1!A1:A250,Sheet1!B1:B250,""),
    dev_name, XLOOKUP("Development Name:",Sheet1!A1:A250,Sheet1!B1:B250,""),
    suburb, XLOOKUP("Suburb:",Sheet1!A1:A250,Sheet1!B1:B250,""),
    state, XLOOKUP("State:",Sheet1!A1:A250,Sheet1!B1:B250,""),
    postcode, XLOOKUP("Postcode:",Sheet1!A1:A250,Sheet1!B1:B250,""),
    "Project: DP "&dp&", Lot "&lot&", Unit "&unit&", "&street_num&" "&street_name&", "&dev_name&", "&suburb&" "&state&" "&postcode
)

2. 核心数值提取

空调面积：=LEFT(XLOOKUP("Conditioned Area",Sheet1!A1:A250,Sheet1!B1:B250,""),FIND(" ",XLOOKUP("Conditioned Area",Sheet1!A1:A250,Sheet1!B1:B250,""))-1)
非空调面积：=LEFT(XLOOKUP("Unconditioned Area",Sheet1!A1:A250,Sheet1!B1:B250,""),FIND(" ",XLOOKUP("Unconditioned Area",Sheet1!A1:A250,Sheet1!B1:B250,""))-1)
玻璃占比：=XLOOKUP("Glass to Floor Area",Sheet1!A1:A250,Sheet1!B1:B250,"")
墙地占比：=ROUND((LEFT(XLOOKUP("Net External Wall Area",Sheet1!A1:A250,Sheet1!B1:B250,""),FIND(" ",XLOOKUP("Net External Wall Area",Sheet1!A1:A250,Sheet1!B1:B250,""))-1)/LEFT(XLOOKUP("Total Floor Area",Sheet1!A1:A250,Sheet1!B1:B250,""),FIND(" ",XLOOKUP("Total Floor Area",Sheet1!A1:A250,Sheet1!B1:B250,""))-1))*100,1)&"%"

3. 窗户信息提取

=TEXTJOIN(CHAR(10),TRUE,FILTER(Sheet1!A1:Z250,(LEFT(Sheet1!A1:A250,1)*1>=0)*(ROW(Sheet1!A1:A250)>MATCH("Window",Sheet1!A1:A250,0))*(ROW(Sheet1!A1:A250)<MATCH("External Wall",Sheet1!A1:A250,0))))

筛选Window和External Wall之间首字符为数字的行（即窗户主数据行），自动包含面积、型号、Uval、SHGC信息。

4. 墙体/地面/天花板/屋顶信息提取（以External Wall为例）

=TEXTJOIN(CHAR(10),TRUE,FILTER(Sheet1!A1:Z250,(ROW(Sheet1!A1:A250)>MATCH("External Wall",Sheet1!A1:A250,0))*(ROW(Sheet1!A1:A250)<MATCH("Internal Wall",Sheet1!A1:A250,0))))

其他类别只需替换MATCH中的关键词即可，如需四舍五入面积，可嵌套ROUND函数提取数值部分。

5. 非空调面积扣除无装修地面

=LET(
    uncond_val, VALUE(LEFT(XLOOKUP("Unconditioned Area",Sheet1!A1:A250,Sheet1!B1:B250,""),FIND(" ",XLOOKUP("Unconditioned Area",Sheet1!A1:A250,Sheet1!B1:B250,""))-1)),
    garage_area, SUM(FILTER(VALUE(LEFT(Sheet1!A1:A250,FIND(" ",Sheet1!A1:A250)-1)),(ROW(Sheet1!A1:A250)>MATCH("External Floor",Sheet1!A1:A250,0))*(ROW(Sheet1!A1:A250)<MATCH("External Ceiling",Sheet1!A1:A250,0))*(ISNUMBER(SEARCH("No Insulation",Sheet1!B1:B250))))),
    "Unconditioned excluding garage: "&ROUND(uncond_val-garage_area,1)&" m²"
)

自动识别External Floor中含No Insulation的行，求和后从非空调面积中扣除。

内容的提问来源于stack exchange，提问作者marcello belcastro