EXCEL 笔记

短命女 2022-12-29 09:55 65阅读 0赞

### EXCEL 笔记 ###

*  EXCEL常用公式：
 *   *  8位随机码
     *  多条件查找（在1个单位格内应用数组公式然后下拉）
     *   *  1. SUMPRODUCT
         *  2. SUM
         *  3. IF + VLookup
         *  4. Index + Match
         *  5. 更多求合公式请看：\[Excel 学习笔记：求和公式\](https://jerryjin.blog.csdn.net/article/details/108911620)
     *  去除收尾指定字符
 *  反向查找（从右向左查找）
 *   *  IF 配合 VLOOKUP
     *  OFFSET 配合 MATCH （这个其实往哪个方向都可以查找）
 *  自定义公式
 *   *  连接字符串公式
 *  参考资料

# EXCEL常用公式： #

## 8位随机码 ##

`=CONCATENATE(IF(INT(RAND()*3)=0,CHAR(INT(RAND()*10+48)),IF(INT(RAND()*2)=0,CHAR(INT(RAND()*25+65)),CHAR(INT(RAND()*25+97)))),IF(INT(RAND()*3)=0,CHAR(INT(RAND()*10+48)),IF(INT(RAND()*2)=0,CHAR(INT(RAND()*25+65)),CHAR(INT(RAND()*25+97)))),IF(INT(RAND()*3)=0,CHAR(INT(RAND()*10+48)),IF(INT(RAND()*2)=0,CHAR(INT(RAND()*25+65)),CHAR(INT(RAND()*25+97)))),IF(INT(RAND()*3)=0,CHAR(INT(RAND()*10+48)),IF(INT(RAND()*2)=0,CHAR(INT(RAND()*25+65)),CHAR(INT(RAND()*25+97)))),IF(INT(RAND()*3)=0,CHAR(INT(RAND()*10+48)),IF(INT(RAND()*2)=0,CHAR(INT(RAND()*25+65)),CHAR(INT(RAND()*25+97)))),IF(INT(RAND()*3)=0,CHAR(INT(RAND()*10+48)),IF(INT(RAND()*2)=0,CHAR(INT(RAND()*25+65)),CHAR(INT(RAND()*25+97)))),IF(INT(RAND()*3)=0,CHAR(INT(RAND()*10+48)),IF(INT(RAND()*2)=0,CHAR(INT(RAND()*25+65)),CHAR(INT(RAND()*25+97)))),IF(INT(RAND()*3)=0,CHAR(INT(RAND()*10+48)),IF(INT(RAND()*2)=0,CHAR(INT(RAND()*25+65)),CHAR(INT(RAND()*25+97)))))`

## 多条件查找（在1个单位格内应用数组公式然后下拉） ##

### 1. SUMPRODUCT ###

`=SUMPRODUCT(目标字段1 = "条件1" * 目标字段2 = "条件2" * 目标字段N= "条件N" * 结果字段)`  
结果字段必须是数字。数字维数要一至

### 2. SUM ###

同 SUMPRODUCT

### 3. IF + VLookup ###

`=VLOOKUP(B$2&$A3,IF({1,0},数据!$A$2:INDEX(数据!$A:$A,COUNTA(数据!$A:$A))&数据!$C$2:INDEX(数据!$C:$C,COUNTA(数据!$C:$C)),数据!$B$2:INDEX(数据!$B:$B,COUNTA(数据!$B:$B))),2,0)`

*  公式分解  
    =VLOOKUP(`条件1`&`条件2`, 搜索区域, 2, 0)
 *  搜索区域： 利用 if 返回一个数组 1列是条件，2列是结果  
    =IF(`{1,0}`, `条件1搜索字段` & `条件2搜索字段`, `结果字段`)

### 4. Index + Match ###

在查找结果为字符时用 SUMPRODUCT、SUM 是报错的，它们只认数字

`=INDEX(数据表!$C$1:$C$100,MATCH($A1&$B1,数据表!$A$1:$A$100&数据表!$B$1:$B$100,0))`  
解释：=INDEX(`最终要显示的数据列`, MATCH( `条件1`&`条件2`, `条件1对应的字段`&`条件2对应的字段`,`Match_type用0`))

*  **index(`引用字段`, `行号`)** ：显示引用字段中的第N行
 *  **MATCH( `条件`, `目标字段`, `0`)** ：在目标字段中找到满足条件的数据，返回行号

<table> 
 <thead> 
  <tr> 
   <th>Match_type值</th> 
   <th align="left">说明</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1 或省略</td> 
   <td align="left">MATCH 查找小于或等于 lookup_value 的最大值。lookup_array 参数中的值必须以升序排序。<br>例如：…-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE。</td> 
  </tr> 
  <tr> 
   <td>0</td> 
   <td align="left">MATCH 查找完全等于 lookup_value 的第一个值。lookup_array 参数中的值可按任何顺序排列。</td> 
  </tr> 
  <tr> 
   <td>-1</td> 
   <td align="left">MATCH 查找大于或等于 lookup_value 的最小值。lookup_array 参数中的值必须按降序排列。<br>例如：TRUE, FALSE, Z-A, …2, 1, 0, -1, -2, … 等等。</td> 
  </tr> 
 </tbody> 
</table>

**官方帮助**：

[Office：Index 公式][Office_Index]  
[Office：Match 公式][Office_Match]

如果需要可以添加数据源，可以返回一个 `A2:A变动` 的范围引用。`(A2:INDEX(A:A,COUNTA(A:A))`

这样找到A:A有内容的最大范围，然后返回A2到此位置的范围。（注意段返回的竟然是个范围）

### 5. 更多求合公式请看：[Excel 学习笔记：求和公式][Excel] ###

## 去除收尾指定字符 ##

=SUBSTITUTE(TRIM(SUBSTITUTE(单元格, "要去掉的字符", " ")), " ", "要去掉的字符")

去掉A1首位逗号

=SUBSTITUTE(TRIM(SUBSTITUTE(A1, ",", " ")), " ", ",")

# 反向查找（从右向左查找） #

VLOOKUP 虽然好用，但是有个弱点，条件列只能在左边。如果想 按B字段的内容 找 A 字段就不支持了。  
下面是网上比较常见的解决方案：

## IF 配合 VLOOKUP ##

=VLOOKUP( "条件XXX" , IF(  {1,0},  B1:B6,  A1:A6) , 2 ,0)
    =VLOOKUP( "条件XXX" , IF(  {0,1},  A1:A6,  B1:B6) , 2 ,0)

这里的 if 用一个数组做条件，所以返回的也是数组。 上面两种定法都一样，目的就是把 A1:A6, B1:B6 两列的位置调换。

## OFFSET 配合 MATCH （这个其实往哪个方向都可以查找） ##

=OFFSET($A$1,MATCH("条件XXX" , B:B)-1,0)

*  OFFSET ：用于引用 以$A$1为原点， 偏移N行，偏移N列 的单元格。（因为 OFFSET 的偏移是从 0 开始的 ）
 *  MATCH ：用返回 条件 在B:B 的第几行出现。

# 自定义公式 #

## 连接字符串公式 ##

用 A1：A10 就可以连接所有的字符串了

Function ConTxt(m As String, ParamArray args() As Variant) As Variant
    Dim tmptext As Variant, i As Variant, cellv As Variant
    Dim cell As Range
    tmptext = ""
     
    For i = 0 To UBound(args)
        If Not IsMissing(args(i)) Then
            Select Case TypeName(args(i))
                Case "Range"
                    For Each cell In args(i)
                        tmptext = tmptext & cell & m
                    Next cell
                    tmptext = Mid(tmptext, 1, Len(tmptext) - Len(m))
                Case "Variant()"
                    For Each cellv In args(i)
                        tmptext = tmptext & cellv & m
                    Next cellv
                    tmptext = Mid(tmptext, 1, Len(tmptext) - Len(m))
                Case Else
                    tmptext = tmptext & args(i) & m
            End Select
        End If
    Next i
     
    ConTxt = tmptext
    End Function

# 参考资料 #

[Office：Index 公式][Office_Index]  
[Office：Match 公式][Office_Match]

[Office_Index]: https://support.office.com/zh-cn/article/INDEX-%E5%87%BD%E6%95%B0-A5DCF0DD-996D-40A4-A822-B56B061328BD?ui=zh-CN&rs=zh-CN&ad=CN
[Office_Match]: https://support.office.com/zh-cn/article/MATCH-%E5%87%BD%E6%95%B0-E8DFFD45-C762-47D6-BF89-533F4A37673A?ui=zh-CN&rs=zh-CN&ad=CN
[Excel]: https://jerryjin.blog.csdn.net/article/details/108911620