# eng

**Repository Path**: isencher/eng

## Basic Information

- **Project Name**: eng
- **Description**: 开发 “用 Excel 制作工程台账系统" 课程
- **Primary Language**: Python
- **License**: MIT
- **Default Branch**: master
- **Homepage**: None
- **GVP Project**: No

## Statistics

- **Stars**: 0
- **Forks**: 0
- **Created**: 2023-08-30
- **Last Updated**: 2023-12-25

## Categories & Tags

**Categories**: Uncategorized

**Tags**: None

## README

# eng

<span style="color:red; font-size: 48px; font-weight: bold">3 个函数构建一套 Excel 台账系统</span>

#### 介绍
开发 “**3 个函数构建一套 Excel 台账系统**" 课程

本课程以工程台账为例，利用 3 个 Excel 函数，构建一套行之有效的管理系统。当然，构建系统肯定不止用到这 3 个函数，但这 3 个却是关键核心。

![alt text](.\img\main.png)

#### 制作环境
1.  windows 11 Home 64位
2.  micorosft office 家庭和学生版 2021

#### 建议环境
1.  window 10 及以上
2.  microsoft office 2016 及以上

#### 创建步骤

1.  拆分 **表** - 基于对业务的理解
2.  定义表 **结构** - 每张表的标题
3.  创建 [**Table**](####Table), 并取有意义的名称
4.  创建 **汇总** 表，使统计自动化
5.  创建 **驾驶仓** 表，一切尽收眼底
6.  前后端 **分离**，数据切换更灵活
7.  **输入** 或 **切换** 数据，**更新** 分析结果

#### Table
Excel 中的 Table 是一种特殊的数据结构，与普通的 Range 有所不同。以下是 Table 的一些特性：
- 1.自动扩展：当您向 Table 中添加新的行或列时，Table 会自动扩展以包含新的数据。
- 2.自动筛选：Table 中的数据可以自动应用筛选器，使您可以轻松地筛选和排序数据。
- 3.列标题：Table 中的每一列都有一个标题，使您可以更轻松地识别和引用列。
- 4.公式自动填充：当您在 Table 中输入公式时，Excel 会自动将公式填充到整个列中。
- 5.样式和格式：Table 具有自己的样式和格式，使其更易于识别和区分。
- 6.结构化引用：Table 中的数据可以使用结构化引用进行引用，例如 Table1[Column1]、Table[@Column1]。
- 7.数据验证：Table 中的数据可以使用数据验证进行验证，以确保数据的准确性和完整性。
- 8.快速汇总：Table 中的数据可以使用快速汇总功能进行汇总，例如计算平均值、总和等。

#### 关键技术讲解
1. match 函数 

从一个**序列**（行 或 列）中查找某个给定**值**，并返回该值位于序列的位**置**。
```
MATCH(查找值， 查找范围， 匹配类型) -> 相对位置
```
![alt text](.\img\match_row.png)
![alt text](.\img\match_col.png)

2. index 函数
```
INDEX(查找范围， 行号， 列号) -> 定位单元格的值
```
![alt text](.\img\index.png)

3. sumif 函数
```
SUMIF(查找范围， 查找值， 求和范围) -> 汇总值
```
![alt text](.\img\sumif.png)

#### 动手实现

1.  创建 Table
<video src=".\media\01_range_to_table.mp4" controls></video>
    - Ctrl + T
    - 公式 -> 名称管理器 -> 编辑 -> 在 **名称** 框输入 **有意义的名称**

2.  创建 **汇总** 工作表

- 2.1 创建
<video src='.\media\02.1_total_sheet.mp4' controls></video>
    - **无重复**的 - index 结合 match 直接取数
    - **有重复**的，且为数值 - sumif 条件汇总

- 2.2  修正 汇总 中的 合同金额 - 遗漏了补充协议
<video src='.\media\02.2_fix_contract_amount_sum.mp4' controls></video>
    - left 函数从左边起取指定位数子串

3.  创建 **驾驶仓** 工作表
- 3.1 全局总览
<video src='.\media\03.1_summary.mp4' controls></video>
    - indirect 函数动态引用 Table 列

- 3.2 费用结构
<video src='.\media\03.2_structure.mp4' controls></video>
    - 透视表

- 3.3 年度支出
<video src='.\media\03.3_year_amount.mp4' controls></video>
    - year 函数取年度值
    - if 函数处理异常

- 3.4 年度费用结构
<video src='.\media\03.4_year_structure.mp4' controls></video>
    - 图数据源隐身

#### 技术清单

1.  **创建** Table, 为 Table **重命名**
2.  **搜索** 行 或 列 **位置**用 match
3.  从指定**单元格**中**取值**用 index 
4.  **条件汇总**用 sumif
5.  **counta** 函数**计数**统计 
6.  **iferror** 函数处理**异常**
7.  **left** 函数从左起取**子串**
8.  **透视表**聚合数据
9.  Table **套取样式** 及 **条件格式**
10. **引用** Table 标题、列、每行数据
11.  查看公式 **Ctrl + `**
12.  **图表**制作

#### 注意事项

1.  使用 match 时，查找范围 内的值**没有重复**
2.  使用 sumif 时，查找范围 内的值**重复**才有意义

#### 局限

1.  excel 无法对 Table 进行保护，限制了约束保护能力

#### 项目地址
[gitee](https://gitee.com/sencher/eng)