hello_nika

HI，大家好，我是星光。 很久以前，咱们讲数据分析人员有三剑客，SQL获取数据、Excel分析数据、PPT呈现数据，SQL是数据分析过程中必不可少的一项技能。后来随着时代不断发展，数据量越来越大，又出现了BI等软件，Python等编程语言也愈发普及了—— 不过，看我小眼神，这不但没有削弱、反而更一步增强了SQL的作用。如果你有意转向数据分析行业，你的技能包里，Excel/BI未必有，但SQL不能缺失，认真脸。 在公众号，过往咱们陆续发布了一些关于SQL的教程系列，今天重新做个小结，希望对有需要学习的朋友有所帮助。 如何在Excel中使用SQL语言？ 01：认识SQL In Excel 02：认识ADO 03：什么是字段 04：什么是表 05：干掉重复项 06：排序和排名 07：基础条件查询 08：交互条件查询 09：什么是聚合函数 10：什么是分组聚合 11：什么是SELECT语句 12：什么是UNION语句 13：什么是子查询 14：什么是交叉连接 15：什么是内连接 16：什么是外连接 17：什么是交叉式报表 18：DELETE删除数据 19：UPDATE更新数据 20：INSERT插入数据 21：ADO常用连接字符串 22：什么是Execute方法 23：OpenSchema语句 24：认识RecordSet对象 25：将记录集转换为数组

大家好，今日我们继续VBA与数据库的学习，最近的讲解是ADO与EXCEL的连接，从而实现许多方便快捷的操作，这里的EXCEL是作为数据库应用的，在本书数据库的讲解中，连接详细讲解的就只有accdb数据库和excel数据库了，两者各有不同，对于EXCEL而言是非常大众化的数据处理软件，利用的非常广泛，而accdb数据库是一个专门的数据库。两个可以相互的取长补短，比如excel数据库我在讲解的时候，尽可能的不去执行实际打开的操作，只是提取数据的处理。这样可以发挥EXCEL的优点，而避开文件容易改动的缺点。 今日讲解的是如何利用ADO完成相对复杂的数据运算，我们先看下面的EXCEL数据： 这时几种药品在1到6月份的销售情况，记录在15年.xlsx的工作表sheet3中，我们要提出数据，但我们关心的不是每月的销售情况，而关心的是每月销售的增加情况，和每种药品在上半年的销售总额是多少,我们要把每月销售的增减和半年的销售总额汇总出来，而不打开文件，这个程序用ADO连接该怎么写呢？ 看下面的代码： ‘第36讲，利用ADO,实现EXCEL工作表数据的复杂计算 Sub mynzexcels_5() Dim cnADO, rsADO, Z As Object Dim strPath, strTable, strSQL As String Set cnADO = CreateObject(“ADODB.Connection”) strPath = ThisWorkbook.Path & “\” & “15年.xlsx” strTable = “[sheet3$a2:G9]” ‘建立连接,提取数据 cnADO.Open “provider=Microsoft.ACE.OLEDB.12.0;extended properties=’excel 8.0;hdr=no;imex=1’;data source=” &…

企业会计准则排版VBA代码 Sub 会计准则排版() Dim Mypath As String, Delaytime As Double Application.DisplayAlerts = False Application.ScreenUpdating = False With Application.FileDialog(msoFileDialogFilePicker) If .Show = -1 Then Mypath = .SelectedItems(1) End With Documents.Open FileName:=Mypath Selection.WholeStory Selection.Copy Documents.Add DocumentType:=wdNewBlankDocument Delaytime = Now + TimeValue(“00:00:01”) Do…

VBA 文件批量重命名：告别重复劳动！ 本篇知识点：Name 语句、数组、GetOpenFilename 等。 小白：阿夏，好难啊，求助！准备想走的时候，老板给了一千多份工作簿让我按序号重命名，哇，这得改到什么时候啊，我今晚不能找你吃饭了…… 阿夏：你不也是刚学了 VBA 吗，试着用 VBA 去做呗。 小白：对哦！……可是要怎么做呢？ 第一步：了解 Name 语句 阿夏：我们拆解一下步骤，先不要想这么难的……假设只是需要修改一个文件名，就以修改工作簿1为例。 小白：噢！我记得的，可以用 Name 语句。 Name oldpathname as newpathname Name “C:\Users\Admin\Desktop\新建文件夹\工作簿1.xlsx” As “C:\Users\Admin\Desktop\新建文件夹\入库表0001.xlsx” 第二步：动态获取旧文件名 阿夏：试着获取 old pathname，而不是直接复制文件路径？ 小白：唔……用 GetOpenFilename，还是 FileDialog 呢？ 小白：GetOpenFilename 只是返回选择的文件的完整路径和文件名到程序，不实质打开文件，不能选择文件夹，而 FileDialog 可以实质操作文件。一个文件，先用 GetOpenFilename…

递归是一个过程或函数在其定义或说明中有直接或间接调用自身的一种方法，递归函数就是直接或间接调用自身的函数，也就是自身调用自己的函数。 递归调用 Sub VBAHOMETOP_Test() Debug.Print recursionSum(100) ‘递归求和 Debug.Print recursionMulity(10) ‘递归求阶乘 Debug.Print Fibonacci(10) ‘递归求菲波那切数列 End Sub 示例1：递归求和 1+2+3+…+n Function recursionSum(n As Integer) If n = 1 Then recursionSum = 1 ‘和值为1，即n=1时 Else recursionSum = n + recursionSum(n – 1) ‘递归求和的原理就是n +…

Sub ExtractDataFromFoldersRecursively() Dim SourceFolder As String Dim DestWorkbook As Workbook Dim DestWorksheet As Worksheet Dim Conn As Object Dim rs As Object Dim strSQL As String Dim SourceFiles() As String Dim i As Integer ‘ 设置源文件夹的路径 SourceFolder = “C:\\YourParentFolderPath\\” ‘…

Excel 数据库如下，包含行标题及对应数据，本篇使用的 Excel 数据库均为此格式。 除了 Excel 数据库，还有 Access 数据库等。本节课学习如何用 VBA 操作专业数据库，共讲解 5 节内容。 一、基本概念 如何操作数据库？ 使用 ADO 建立与数据库的连接，然后通过 ADO 对象和 SQL 语言操作数据库。 SQL 是什么？ SQL（Structured Query Language）是一种查询语言，可用于查询、更新数据库中的数据。 SQL 可以查询哪些数据库？ SQL 是通用的查询语言，可查询 Excel、Access、SQL Server 等多种数据库。 ADO 是什么？ ADO 是新的数据库访问技术，可建立与各种数据库的连接，并支持添加、更新、删除等操作。 学习 SQL+ADO…

上节学习了如何遍历一个文件夹的文件，那么如果文件夹中还有子文件夹，如何获取某文件夹及其子文件夹下所有文件的路径呢？ 根据之前所学，一旦定义了路径，Dir 函数只会在该路径下循环查找文件，而不会进入子文件夹。 回顾一小段代码： Filename = Dir(mypath, vbDirectory) Do k = k + 1 Cells(k, 1) = Filename Filename = Dir Loop Until Filename = “” 当文件名为空后，Dir 函数结束运算，不会继续进入子文件夹（如“1月”、“2月”、“3月”、“测试新建文件夹”）查找文件。 那么，如何实现这一功能？ 有两种方法：第一种是 FileSearch，但此方法似乎仅在 Excel 2003 版中有效，其他版本会出错，因此略过不记录。第二种是父子转换法，结合 Dir 函数和数组的应用。 父子转换法 1. 思路：先找出所有文件夹路径，再找出所有文件夹下的文件路径 设置数组…

今天学习 11 个程序优化与提速的小技巧： 1. 减少对象的调用 调用对象非常耗费资源，因此要尽可能减少对象调用，包括工作簿、工作表、单元格及外引用对象。 在循环内外调用对象对程序运行速度也有影响，尽量在循环外调用对象。 2. 减少计算次数 程序运算速度与计算次数密切相关，因此要尽量减少计算次数。 能在循环外计算出结果的，不要在循环内计算。 3. 禁止闪屏 设置 Application.ScreenUpdating 为 False 可禁止程序运行过程中的屏幕闪烁，从而提高运行速度。 注意：仅对会引起闪屏的操作有效，否则可能拖慢程序速度。关闭屏幕闪烁后，需在操作完成或程序末尾将 Application.ScreenUpdating 设置为 True，否则日常 Excel 操作也会禁止屏幕刷新。 Application.ScreenUpdating = False ‘关闭屏幕闪动 Application.ScreenUpdating = True ‘开启屏幕闪动 4. 增加变量的声明类型 声明变量时尽量指定变量类型，否则计算机默认将其视为 Variant 类型，并根据赋值自动判断类型，容易造成内存浪费。 数组尤其需要声明变量类型，养成习惯为每个变量声明类型。 复习数据类型： (1)…

入坑开始的前因后果： 前不久，由于内人工作上的需要，开始接触并研究 Excel VBA 的使用。 一张表格里有几十家甚至上百家业务往来单位，要按照对应的单位名称生成单独的一张对账单表格。如果只是机械地复制粘贴来处理，不仅工作效率低，而且非常浪费时间，严重影响其他工作的开展。 于是在网上搜索教程时，看到一个视频是通过 VBA 来批量生成对账单，于是便开始入坑研究起来。 从零接触到零基础，边解读代码边学习基础知识，渐渐熟悉每一行代码的含义和可以实现的功能，然后根据实际工作需求调试原视频的代码，并自行编写满足需求的代码。经过无数次调试失败、修改代码，最终实现了通过 VBA 一键批量生成对账单。 需要实现的功能 通过 VBA 可以一键批量生成对账单，并另存为以单位名称命名的独立对账单。 下图是需要生成对账单的磅单明细表格，为保护隐私数据已做相应处理。 下图是模板表格，按照此表格格式自动生成对应的对账单。 运行程序后，生成对账单的效果如下： 部分代码解读（完整代码附后） 操作软件：Microsoft Office Excel 2021 使用功能：Visual Basic 主程序开始执行前的预处理 屏幕刷新（ScreenUpdating），值为 False 时关闭，作用是使 Excel 停止刷新，提高代码执行速度。 Application.ScreenUpdating = False 显示警报（DisplayAlerts），值为 False 时关闭，作用是屏蔽弹窗提示。 Application.DisplayAlerts =…

在编写 VBA 代码时，有时需要将变量的值拷贝到剪贴板，或者从剪贴板读取已拷贝的内容。 这时可以使用 DataObject 对象来实现对剪贴板的读写操作。 以下是一个通用的对剪贴板进行读写的 VBA 代码： Sub GetDataFromClipboard() Dim sContent Dim oDataObject sContent = “测试文本” ‘对该对象进行后期绑定 Set oDataObject = CreateObject(“new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}”) With oDataObject ‘给 DataObject 对象写入内容 .SetText sContent ‘将 DataObject 对象的内容存入剪贴板 .PutInClipboard ‘将剪贴板中的内容存入 DataObject 对象 .GetFromClipboard ‘获取剪贴板中的内容 sCB…