Excel – 早新

Power Query 扩充行数的方法

参考：

Insert X Rows to Expand Table – Excel University (excel-university.com)

一张出运明细表中含有装箱率、件数、箱数，现在需要用Bartender制作外箱贴，外箱贴是有序号的，需要正好符合总外箱数。

在安排编号的时候并不会手动设定每一张外箱贴编号，我需要根据外箱数及不同SKU，自动生成外箱编号。举例：A有10箱，B有8箱。

SKU	箱数	装箱率
A	10	2
B	8	2

需要自动转化出另一张表：

编号	SKU	装箱率
1	A	2
2	A	2
3	A	2
4	A	2
5	A	2
6	A	2
7	A	2
8	A	2
9	A	2
10	A	2
11	B	2
12	B	2
13	B	2
14	B	2
15	B	2
16	B	2
17	B	2
18	B	2

我有预感Power Query能实现扩充行数。

实现方式为添加List列。

  = {1..[箱数]}

然后 Expand to new rows

然后添加索引列即可。

具体代码：

let
    Source = Excel.CurrentWorkbook(){[Name=\"Table1\"]}[Content],
    #\"Changed Type\" = Table.TransformColumnTypes(Source,{{\"SKU\", type text}, {\"EAN\", type text}, {\"UNIT\", Int64.Type}, {\"CTNS\", Int64.Type}, {\"PCS\", Int64.Type}, {\"PCStoPrint\", Int64.Type}, {\"CTNStoPrint\", Int64.Type}, {\"CTN_NUM_F\", type any}, {\"CTN_NUM_T\", type any}}),
    #\"Added Custom\" = Table.AddColumn(#\"Changed Type\", \"CTINS_LIST\", each {1..[CTNS]}),
    #\"Expanded CTINS_LIST\" = Table.ExpandListColumn(#\"Added Custom\", \"CTINS_LIST\"),
    #\"Added Index\" = Table.AddIndexColumn(#\"Expanded CTINS_LIST\", \"CTIN_ID\", 1, 1, Int64.Type),
    #\"Removed Columns\" = Table.RemoveColumns(#\"Added Index\",{\"CTINS_LIST\"}),
    #\"Reordered Columns\" = Table.ReorderColumns(#\"Removed Columns\",{\"CTIN_ID\", \"SKU\", \"EAN\", \"UNIT\", \"CTNS\", \"PCS\", \"PCStoPrint\", \"CTNStoPrint\", \"CTN_NUM_F\", \"CTN_NUM_T\", \"RANGE\"})
in
    #\"Reordered Columns\"

Excel VBA代码找回

开启了宏命令的Excel一不小心就会出现莫名奇妙的问题，轻则VBA代码丢失，重则工作薄损坏，无法打开或保存。确实很恶心。

在中文语言的Excel中新建了宏，在英文语言的Excel中很有可能就打不开了，甚至中文Excel保存后再次打开也会提示文件损坏，辛辛苦苦写的VBA代码就那样没了。

修复是不可能真正修复的，顶多就删除vbaProject.bin这样子，那么写的VBA代码就直接放弃了吗？显然不应该。

xlsx/xlsb实际上是一个压缩包文件，将其后缀改为zip解压，获得大致如下的目录结构。vbaProject.bin就存储了VBA工程文件，包括VBA代码。vbaProject.bin实际上也是一个压缩文件，还可以再解压，但是不能使用常规方式提取出代码。

这篇文章说明了office三件套的文件格式实际是ole文件（oledump.py | Didier Stevens）

DESKTOP-K65CV8V :: Users/Desktop/book % tree -L 3
.
├── [Content_Types].xml
├── _rels
├── customXml
│   ├── _rels
│   │   └── item1.xml.rels
│   ├── item1.xml
│   └── itemProps1.xml
├── docProps
│   ├── app.xml
│   └── core.xml
└── xl
    ├── _rels
    │   └── workbook.bin.rels
    ├── calcChain.bin
    ├── connections.bin
    ├── metadata.bin
    ├── printerSettings
    │   └── printerSettings1.bin
    ├── queryTables
    │   └── queryTable1.bin
    ├── sharedStrings.bin
    ├── styles.bin
    ├── tables
    │   ├── _rels
    │   ├── table1.bin
    │   ├── table2.bin
    │   └── table3.bin
    ├── theme
    │   └── theme1.xml
    ├── vbaProject.bin
    ├── workbook.bin
    └── worksheets
        ├── _rels
        ├── binaryIndex1.bin
        ├── binaryIndex2.bin
        ├── binaryIndex3.bin
        ├── sheet1.bin
        ├── sheet2.bin
        └── sheet3.bin

借助该文提供的oledump.py（oledump.py | Didier Stevens）可对ole文件进行分析。

以下简单介绍利用oledump.py提取VBA代码的用法。

1、查看vbaProject.bin结构

确认模块位置-以Module或模块开头的编号。

查看模块代码内容：

如何用Excel实现多重条件匹配与分段计价

有一个需求，需要对不同规格的包裹进行定价，在Excel里输入长宽高和重量，即可自动计算出定价信息。

但是包裹的尺寸和重量同时决定了定价。定价是分段计算的，包裹将根据尺寸和重量范围分为不同的包裹类型，比如有信件包裹、标准包裹、大尺寸包裹等。在每一个包裹类型下，对于不同的包裹重量，又有不同的定价标准。

继续阅读

前言

工作之后经常需要用到Excel，也让我对Excel有了更深的认识，以至于不得不感叹Excel实在是强大，我知道现在还有更高级的数据处理工具，如Python、R，但对于生活中的大多数计算问题，Excel完全胜任并能处理得很好（不过我还没接触过R，也说不定哪天就真香了），哪怕仅仅是用于加减乘除运算，也比计算器要直观方便些，配合各种函数可以达到编程的效果，我觉得Excel的优势之一就是计算过程非常直观。再加上VBA（当然现在还没有深入到这个地步，大学里教的落伍的VB语言没想到能在这里派上用场），就完全可以用于编程了。

继续阅读

标签： Excel

Power Query 扩充行数的方法

Excel VBA代码找回

如何用Excel实现多重条件匹配与分段计价

记用Excel模拟非等概率随机分布

前言