EXCEL和数据库公式

Excel 常用公式

查找域名

=VLOOKUP(C5,'D:\DayFile\20211221年数据\[全国在职名单20220112user.xlsx]ad'!$B$1:$J$1300,1,FALSE)

判断是否存在

=IF(ISBLANK(C5),"",IF(ISTEXT(VLOOKUP(C5,'D:\DayFile\20211221年数据\[全国在职名单20220112user.xlsx]ad'!$B$1:$J$1300,1,FALSE)),"是","否"))

返回域名

=IF(ISBLANK(C5),"",IFNA(VLOOKUP(C5,'D:\DayFile\20211221年数据\[全国在职名单20220112user.xlsx]ad'!$B$1:$J$1300,1,FALSE),""))

截取某字符后字符串

=RIGHT(B5,LEN(B5)-FIND("e\",B5)-1)

截取最后一个指定字符后的内容

=MID(B11,-LOOKUP(,-FIND("-",B11,ROW(A:A)))+1,LEN(B11))
=IF(ISBLANK(B11),"",MID(B11,-LOOKUP(,-FIND("-",B11,ROW(A:A)))+1,LEN(B11)))

容量单位转换（TB/GB/MB → MB）

=IF(RIGHT(C2,2)="TB",SUBSTITUTE(C2,"TB","")*1024^2,IF(RIGHT(C2,2)="GB",SUBSTITUTE(C2,"GB","")*1024,NUMBERVALUE(SUBSTITUTE(C2,"MB",""))))

透视表数据转GB

=FLOOR(GETPIVOTDATA("求和项:换成MB",$K$2,"日期","0715")/1024,0.1)&"GB"

日期格式转换（12 9月 2017 / 02-Nov-12 → 2017年9月）

=IF(ISNUMBER(FIND("月",H2)),RIGHT(H2,4)&"年"&MID(H2,4,3),"20"&RIGHT(H2,2)&"年"&VLOOKUP(MID(H2,5,3),$G$102:$H$113,2,FALSE)&"月")

Excel 公式

=ROUND(G2/1024^3,1)&" GB"

=IF(ROUND(G2/1024^4,1)>1,ROUND(G2/1024^4,1)&" TB",ROUND(G2/1024^3,1)&" GB")

=IF(ROUND(B3/1024^4,1)>1,ROUND(B3/1024^4,1)&" TB",IF(ROUND(B3/1024^3,1)>1,ROUND(B3/1024^3,1)&" GB",ROUND(B3/1024^2,1)&" MB"))

=DAYS(K10,J10)

=INT(N39/365)&"年"&INT((N39-INT(N39/365)*365)/30)&"个月"&(N39-INT(N39/365)*365-INT(((N39-INT(N39/365)*365)/30))*30&"天")

SQL Server

连接SQLEXPRESS：
```
sqlcmd -s 10.2.24.55\sqlexpress -e
```

常用查询：

-- 模糊匹配
SELECT s2.b,s2.c FROM s2 WHERE s2.c LIKE '3%' ORDER BY s2.c DESC;
-- 更新
UPDATE s2 SET s2.c='46' WHERE s2.b='36';

IP-Guard 数据库操作：

-- 查询用户与终端别名对应关系
SELECT [USER].USR_NAME,AGENT.AGT_ALIAS FROM [USER],AGENT WHERE [USER].USR_LAST_AGENTID=AGENT.AGT_ID;
-- 查询备注为非汉字的终端
SELECT * FROM AGENT WHERE ASCII(AGENT.AGT_ALIAS)<127 ;
-- 批量修改终端备注的Excel公式
="UPDATE AGENT set AGENT.AGT_ALIAS='"&I2&"',AGENT.AGT_GRP_ID='"&J2&"' WHERE AGENT.AGT_ID='"&A2&"';"

Excel 常用公式

单位转换

=IF(RIGHT(E2,2)="TB",SUBSTITUTE(E2," TB","")*1024,SUBSTITUTE(E2," GB",""))
=IF(RIGHT(E2,2)="TB",SUBSTITUTE(E2," TB","")*1024,IF(RIGHT(E2,2)="GB",SUBSTITUTE(E2," GB",""),SUBSTITUTE(E2," MB","")/1024))

字符串处理

=ASC(A1)                    # 全角转半角
=IF(LEFT(IF(RIGHT(I1,1)=" ",MID(I1,1,LEN(I1)-1),I1),1)=" ",MID(IF(RIGHT(I1,1)=" ",MID(I1,1,LEN(I1)-1),I1),2,LEN(IF(RIGHT(I1,1)=" ",MID(I1,1,LEN(I1)-1),I1))),IF(RIGHT(I1,1)=" ",MID(I1,1,LEN(I1)-1),I1))   # 去空格

时间转换

=(B2/86400,"dd天hh小时mm分钟ss秒")   # 秒转时分秒
=TEXT(B2,"[S]")                     # 时间转秒
=TEXT(B2/3600/24,"[H]:mm:ss")       # 秒转小时分钟

Excel 实用VBA脚本

批量将选中的工作表另存为独立工作簿：

Sub SaveSheetAsWorkbook()
    Dim theName As String
    On Error GoTo Line1
    For Each sht In ActiveWindow.SelectedSheets
        sht.Copy
        theName = ThisWorkbook.Path & "_" & sht.Name & ".xls"
        ActiveWorkbook.SaveAs Filename:=theName, FileFormat:=xlNormal
        ActiveWindow.Close
    Next
Line1:
End Sub

合并当前工作簿下所有工作表：

Sub 合并当前工作簿下的所有工作表()
    Application.ScreenUpdating = False
    For j = 1 To Sheets.Count
        If Sheets(j).Name <> ActiveSheet.Name Then
            X = Range("A65536").End(xlUp).Row + 1
            Sheets(j).UsedRange.Copy Cells(X, 1)
        End If
    Next
    Range("B1").Select
    Application.ScreenUpdating = True
    MsgBox "当前工作簿下的全部工作表已经合并完毕！", vbInformation, "提示"
End Sub

工作表激活时自动取消筛选：

Private Sub Worksheet_Activate()
    ActiveSheet.AutoFilterMode = False
End Sub

宏筛选示例：

Range("M2:M50").Select
Selection.AutoFilter
ActiveSheet.Range("$m$2:$m$50").AutoFilter field:=1, Criteria1:="20"

数据库相关 (MySQL/MSSQL)

1. MySQL 常用查询

-- 查找钉钉内不存在但域账号已启用的用户
SELECT * FROM [1011] WHERE [1011].Description in (SELECT [1011].Description FROM [1011] WHERE [1011].Enabled=1 EXCEPT SELECT [ding].工号 FROM [ding]);

-- 多条件查找
SELECT * FROM [1011] WHERE [1011].Description in ('BJT010018','JN010007','HZ010030','CQ010222','SH020009','HZ010035','ZZ010220','SZ010004');

-- 查找重复的员工号
SELECT * FROM [1011] WHERE [1011].Description in (SELECT [1011].Description FROM [1011] GROUP BY [1011].Description HAVING COUNT([1011].Description) > 1);

-- 按创建时间倒序排序
SELECT str_to_date(ad1014.Created,'%Y/%m/%d %H:%i:%S') FROM ad1014 ORDER BY str_to_date(ad1014.Created,'%Y/%m/%d %H:%i:%S') DESC;

-- 去除结果重复，或使用 group_concat 去重
SELECT distinct test1 FROM test;
SELECT id, group_concat( DISTINCT test1 ) FROM test GROUP BY test1;

-- 创建新表（注意空值处理）
CREATE TABLE 2yueding(SELECT ding.DUserID,ding.Name,ding.bumen1,ding.bumen2,ding.bumen3,ding.gonghao,STR_TO_DATE(IF(ding.ruzhi="",null,ding.ruzhi),'%Y-%m-%d') AS rztim,ding.ruzhi FROM ding WHERE STR_TO_DATE(IF(ding.ruzhi="",null,ding.ruzhi),'%Y-%m-%d') > '2022-07-31' ORDER BY rztim DESC);

2. IPGuard 数据库常用关联查询

-- AGENT 表添加域账号字段
ALTER TABLE AGENT ADD SamAccountName VARCHAR(255);
UPDATE AGENT INNER JOIN USER ON AGENT.AGT_LAST_USERID=USER.USR_ID SET AGENT.SamAccountName=USER.SamAccountName;

-- 查询在线且没有域账号登录的机器（IP已转换）
SELECT DISTINCT AGENT.AGT_ID,AGENT.AGT_NAME,AGENT.AGT_ALIAS,AGENT.AGT_IP,INET_NTOA(AGENT.AGT_IP) AS ipd,AGENT.AGT_OS_STR,DATEDIFF(DATE(NOW()),DATE(AGENT.AGT_ONLINE_TIME)) AS zxsj,AGENT.AGT_ONLINE_TIME,USER.USR_NAME FROM AGENT,USER WHERE DATEDIFF(DATE(NOW()),DATE(AGENT.AGT_ONLINE_TIME))=0 AND AGENT.AGT_LAST_USERID IN (SELECT DISTINCT USER.USR_ID FROM USER WHERE USER.USR_NAME NOT LIKE '%HXSD%') AND AGENT.AGT_LAST_USERID=USER.USR_ID ORDER BY ipd;

-- 统计AD和IPGuard每月新增账号数量
SELECT aasj,SUM(adjg) AS 'adjg',SUM(ipjg) AS 'ipjg' FROM (SELECT DATE_FORMAT(AD.Created,'%Y-%m') AS 'aasj',COUNT(AD.Created) AS 'adjg','' AS 'ipjg' FROM AD WHERE DATE_FORMAT(AD.Created,'%Y-%m') >= '2022-01' GROUP BY DATE_FORMAT(AD.Created,'%Y-%m') UNION ALL SELECT DATE_FORMAT(AGENT.AGT_INSTALL_TIME,'%Y-%m') AS 'aasj','' AS 'adjg',COUNT(AGENT.AGT_INSTALL_TIME) AS 'ipjg' FROM AGENT WHERE DATE_FORMAT(AGENT.AGT_INSTALL_TIME,'%Y-%m') >= '2022-01' GROUP BY DATE_FORMAT(AGENT.AGT_INSTALL_TIME,'%Y-%m'))testa GROUP BY testa.aasj ORDER BY testa.aasj DESC;

3. MSSQL 链接服务器与IP转换

-- 创建链接服务器
exec sp_addlinkedserver '203AD' , '' , 'SQLOLEDB' , '10.2.20.23'
exec sp_addlinkedsrvlogin '203AD' , 'false' , null , 'z\administrator' , '123.com'

-- 查询
SELECT * FROM OPENQUERY(ADSI,'SELECT cn FROM "LDAP://10.2.20.23/DC=Z,DC=C"C WHERE name="a"' )

-- 删除链接服务器
EXEC sp_dropserver 'mysql','droplogins'

-- INT 转 IP 地址
declare @a int;
select @a = -1062731418;
if @a < 0
select '' + cast(((@a & 0xFF000000) /16777216 + 256) as varchar) + '.' + cast(((@a & 0x00FF0000) / 65536) as varchar) + '.' + cast(((@a & 0x0000FF00) / 256) as varchar) + '.' + cast((@a & 0x000000FF) as varchar)
else
select '' + cast(((@a & 0xFF000000) /16777216) as varchar) + '.' + cast(((@a & 0x00FF0000) / 65536) as varchar) + '.' + cast(((@a & 0x0000FF00) / 256) as varchar) + '.' + cast((@a & 0x000000FF) as varchar)

EXCEL和数据库公式

Excel 常用公式

查找域名

判断是否存在

返回域名

截取某字符后字符串

截取最后一个指定字符后的内容

容量单位转换（TB/GB/MB → MB）

透视表数据转GB

日期格式转换（12 9月 2017 / 02-Nov-12 → 2017年9月）

Excel 公式

SQL Server

Excel 常用公式

单位转换

字符串处理

时间转换

Excel 实用VBA脚本

数据库相关 (MySQL/MSSQL)

1. MySQL 常用查询

2. IPGuard 数据库常用关联查询

3. MSSQL 链接服务器与IP转换

添加新评论

分类