Navigation

    精算后花园

    • Register
    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    1. Home
    2. Mengkelyu
    M
    • Profile
    • Following
    • Followers
    • Topics
    • Posts
    • Best
    • Groups

    Mengkelyu

    @Mengkelyu

    administrators

    3
    Reputation
    280
    Posts
    35
    Profile views
    5
    Followers
    1
    Following
    Joined Last Online

    Mengkelyu Follow
    administrators

    Best posts made by Mengkelyu

    • 如何用SQL语法处理Excel数据? ADO对象在Excel中的处理

      ActiveX Data Objects (ADO) 让我们能够用一个工具来操作SQL Server, Access DB, Worksheet

      ADO有很多用法,首先介绍一个:把excel中的表用SQL语法来处理

      使用ADO流程是

      • 建立连接
      • 写SQL语句
      • 执行SQL语句

      要使用ADO,首先要打开编辑VBA的界面,上面的菜单栏里面有一个是Tools,点击Tools, 里面找到reference。勾选上Microsoft ActiveX Data Objects 6.1 Library(如果没有6.1版本,也可以用2.8版本)

      建立连接的基本语法如下

      Dim Con as new ADODB.Connection
      Con.Open "Connection string" 
              '运行SQL语句
      Con.Close
      

      这里的connection string需要根据不同的Excel版本确定

      https://www.connectionstrings.com/ 这个网站提供了不同情况下需要用到的connection string

      我的excel版本是2016,我用的connection string如下

      strFileName=ThisWorkbook.Fullname
      "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strFileName & "; Extended Properties=""Excel 12.0 Macro;HDR=Yes"";"
      

      就可以成功连接到目前用的工作表啦

      posted in Excel & VBA
      M
      Mengkelyu
    • 如何安装Github中的R包

      大家都知道如何安装CRAN的R包,只要输入

      install.packages("包名称")
      

      就可以直接从CRAN下载包啦!
      但是有的包CRAN中没有,只有Github版本。这个时候我们需要用一个叫做devtools的工具帮我们的忙啦!
      一般的流程是

      install.packages('devtools')
      
      library(devtools)
      
      devtools::install_github("你要下载的包的github网页路径")
      

      但是我自己实际测试之中出现了如下的Bug:

      Error: Failed to install 'unknown package' from GitHub:
      schannel: failed to receive handshake, SSL/TLS connection failed

      好叭。
      只能手动从Github下载包辽。
      下载好zip文件解压之后之后在R中输入如下指令:

      install("解压后的文件路径")
      

      大功告成!

      posted in R
      M
      Mengkelyu
    • RE: 如何用SQL语法处理Excel数据? ADO对象在Excel中的处理

      第二步是运行SQL。首先讲讲SELECT要怎么用

      • 创建新对象:Recordset,用于储存SELECT运行出来的结果
      Dim rs As New ADODB.Recordset
      '如果你想要运行SQL的表是一个命了名的区域
      strSQL="SELECT * FROM range"
      ’如果要运行的表是一整个worksheet
      ‘如果数据超过65536行,由于一个excel bug,只能用下面这种方法引用表
      strSQL="SELECT * FROM [Sheet1$\$$]"
      '如果是一个未命名区域
      strSQL="SELECT * FROM [Sheet1$\$$A1:O60]"
      'con是刚刚用到的ADO connection
      rs.Open strSQL, con
      rs.Close
      
      • 把运行出来的结果保存到一个新建的excel表
      Dim ws As Worksheet
              Set ws = output 'Application.Sheets.Add
              ws.Cells.ClearContents
              'Write the header
              Dim i As Long
              For i = 0 To rs.Fields.Count - 1
                  ws.Cells(1, i + 1).Value2 = rs.Fields(i).Name
              Next i
              ws.Range("A2").CopyFromRecordset rs
      

      完整代码如下

      Sub ADOBD_SELECT()
      Dim strFileName As String
      Dim ConnectionString As String
      'Fullname means the folder path is included
      strFileName = ThisWorkbook.FullName
      
      'From website https://www.connectionstrings.com/
      'HDR=Yes -> my data set has heading
      ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & strFileName & "; Extended Properties=""Excel 12.0 Macro;HDR=Yes;"";"
      
      Dim conn As New ADODB.Connection
      'Recordset: where the result of the query will be placed
      Dim rs As New ADODB.Recordset
      Dim strSQL As String
      'Write the query
      strSQL = Range("Query")
      conn.Open ConnectionString
              rs.Open strSQL, conn
              Dim ws As Worksheet
              Set ws = output 'Application.Sheets.Add
              ws.Cells.ClearContents
              'Write the header
              Dim i As Long
              For i = 0 To rs.Fields.Count - 1
                  ws.Cells(1, i + 1).Value= rs.Fields(i).Name
              Next i
              ws.Range("A2").CopyFromRecordset rs
              rs.Close
      conn.Close
      Set conn = Nothing
      Set rs = Nothing
      End Sub
      
      posted in Excel & VBA
      M
      Mengkelyu
    • 如何利用Class module计算精算现值

      小珂珂是 ActuaryGarden 公司的精算实习生,这天,她在做精算表写VBA的时候遇到了一个问题。
      她手上目前有两套精算假设,想利用这两套假设计算年金现值a,还有定期寿险现值A。
      bb3fcfa7-b4f6-49e0-b71f-ed8960aa0982-image.png
      因为不想重复工作,她想到,每一套假设都可以看作一个Object呀!可以利用Class model来避免重复工作。
      可以把利息率和死亡率设置成Class module的属性,计算A和a设置成Class model的方法~

      posted in Excel & VBA
      M
      Mengkelyu

    Latest posts made by Mengkelyu

    • 如何把Workbook中的Formula都存成值

      把下面的Tool中的地址换成想要操作的Workbook的地址,点击星星键就可以了!

      Code Formulae to Values v3.zip

      posted in Excel & VBA
      M
      Mengkelyu
    • [小技巧] 如何快速批量复制粘贴并重命名文件(Windows)

      如果在工作中需要批量复制粘贴并重命名文件,可以使用这个小技巧

      首先,打开Windows的PowerShell
      94e4ffdf-c3ff-4669-a672-fd8010afd5a0-image.png

      在Powershell里面运行

      copy "需要复制的文件名地址" "目标文件名地址"
      

      例如

      copy "I:\my file.xlsx" "I:\my file (Copied).xlsx"
      

      如果要批量运行,可以先在Excel中用公式写好,然后复制粘贴到Powershell里面运行。

      例子如下,公式已经用红色标出
      6d0894bc-87bc-49e8-a382-22cb6c753f0a-image.png

      posted in 数据科学 (Data science)
      M
      Mengkelyu
    • VBA 常见快捷键,Windows VS Mac

      我整理了一些Windows和Mac我自己常用快捷键的对照表,大家可以来补充~

      参考链接
      VBA Keyboard Shortcuts List

      aa9d1c07-bb9b-43dd-a7a6-c585a2632088-image.png

      posted in Excel & VBA
      M
      Mengkelyu
    • RE: [Youtube学习笔记]Linux的50个实用指令
      cat file #print all contents of a file 输出一个文件中的全部内容
      cat file1 file2 #print all contents of file1 and file2 输出文件1和文件2的全部内容
      cat -n file1 #-n is to print out the line numbers 加入参数n,可以同时输出内容和行数
      less <file># it shows the content stored inside a file, in a nice and interactive UI; cat的漂亮形式
      echo #it prints to the output the argument passed to it 这个命令等于print
      wc #print number of lines; number of words; number of bits 输出基本信息,(多少行,多少数字,多少bit)
      |  #give the output of the first argument to the second argument 这个符号可以把符号前面的Output传递给符号后面的命令作为Input
      ls -l | wc #word count for the ls -l 比如这个例子,ls -l 输出了当前文件夹下所有文件的名字,然后这个文本传递给了wc输出文本的基本信息
      sort #sort the document 给文件排序
      sort -n file #sort everything numerically 按照数字顺序给文件排序
      uniq #report or omit repeated lines
      uniq -d #only report duplicated values
      uniq -u #only report non-duplicated values
      uniq -C #count how many times each value is duplicated
      *.txt #match all documents ending with txt
      ? #match one character
      {a,b,c}.txt #output a.txt, b.txt, c.txt
      Day{1..365} #output Day1 Day2 Day3 .... Day 365
      diff #compare the contents of two files
      diff -v file1 file2 #compare two files line by line
      find <directory> <critria>
      find . -name #*.txt#
      find . -iname #*.Txt# #i for case incensitive
      find . -type d #only search for directory
      find . -type f #only search for files
      find . -name #E*# -or -name #*E# 
      find . -type f exec cat {} \ #exec cat to every file.  {} is placeholder for each found result
      grep #help search inside files
      grep <string to be found> <text to search>
      grep -r <string to be found> <directory> #recursively found a string in all texts in a folder. -ri can make it case incensitive
      du #find the size of directories or files
      df #show how many space left
      history #history of all commands run. Combined with grep, it is possible to obtain the command with certain character 
      ps #view the process running on the computer
      top #display top 10 process
      kill #shut a process
      kill <PID>
      kill -9 #brutal kill
      kill -15 #gental kill
      killall #perform multiple kills
      jobs #print out commands 
      fg <job number> #run some command on the foreground
      bg <job number> #run some command on the background
      <command> & #run the command on the backgound
      jobs #see those commands
      gzip <file name>#compress a file
      gzip -k <file name> #compress a file while keeping the original file
      gzip -d <file name>#decompress a file
      gunzip <file name>#decompress a file
      tar #used to create an archive, grouping multiple files in a single file
      tar -cf archive.tar file1 file2 #the c option standas for create, the f option is used to write to file the archive
      tar -xf #extract to current folder 
      tar -czf archive.tar.gz file1 file2 #creating a tar achive and then running gzip on it
      nano #a friendly editor
      alias <new command name> = '<old command>'# Give some alias to a long command
      # difference of "" and '', "" allows variable in a string
      echo "home directory \$PWD" # 这里\$PWD被当作成了变量或参数
      echo 'home directory \$PWD' # 这里\$PWD没有被当作成变量或参数
      xargs # turn the output of first argument to the input of second arguement'
      find x -size +1M | xargs ls -lh
      ln # it is used to create links. Links are effectively a pointer to another file
      # Two types of links: hard links and soft links
      ln <file1> <file2> # create hard link to file1 
      ln -s <file1> <file2># create soft link
      # soft link will be removed when original file is removed. But hard link will not be removed
      
      posted in 数据科学 (Data science)
      M
      Mengkelyu
    • RE: 如何快速比较两个工作薄的不同

      @Mengkelyu

      注意到这个代码在工作簿中有公式或数字错误时会报 "type Mismatch"的错误。
      可以在代码开头加入

      On Error Resume Next
      

      来跳过这些错误,继续运行代码。

      posted in Excel & VBA
      M
      Mengkelyu
    • VBA字符串模糊匹配 Version 2 (支持中文)

      大家在工作过程中,可能会遇到需要模糊匹配字符串的情况;
      比如需要把简写疾病名称和一些已知疾病的名称进行匹配。

      珂珂帮大家整理了一个匹配函数matchName,可以直接复制到工作表的模块中使用哦。

      用法:第一个参数是匹配对象,第二个是匹配列表
      3feb3f2c-efdd-4601-8551-4101b14e1b89-image.png
      结果:
      748e6549-6aec-4654-925c-3893ee79707e-image.png

      代码如下, 匹配度计算参考这个博客

      Public Function matchName(Target As String, TargetRange As Range) As String
      
      On Error Resume Next
      
      Dim tr As Variant, i As Integer, Temp_value As Double, Temp_result As String, final_value As Double
      
      tr = TargetRange.Value
      
      Temp_result = tr(1, 1)
      
      For i = 1 To UBound(tr, 1)
      
          Temp_value = sim(tr(i, 1), Target)
          If Temp_value > final_value Then
              final_value = Temp_value
              Temp_result = tr(i, 1)
          End If
      
      Next i
      
      matchName = Temp_result
      
      End Function
      
      Private Function min(one As Integer, two As Integer, three As Integer)
      
      min = one
      
      If (two < min) Then
          min = two
      End If
      
      If (three < min) Then
          min = three
      End If
      
      End Function
      
      Private Function ld(str1 As String, str2 As String)
      Dim N, m, i, j As Integer
      Dim ch1, ch2 As String
      
      N = Len(str1)
      m = Len(str2)
      
      Dim temp As Integer
      
      If (N = 0) Then
          ld = m
      End If
      
      If (m = 0) Then
          ld = N
      End If
      
      Dim d As Variant
      ReDim d(N + 1, m + 1) As Variant
          For i = 0 To N
              d(i, 0) = i
          Next i
          For j = 0 To m
              d(0, j) = j
          Next j
          For i = 1 To N
              ch1 = Mid(str1, i, 1)
              For j = 1 To m
                  ch2 = Mid(str2, j, 1)
                  If (ch1 = ch2) Then
                  temp = 0
                  Else
                      temp = 1
                  End If
                  d(i, j) = min(d(i - 1, j) + 1, d(i, j - 1) + 1, d(i - 1, j - 1) + temp)
              Next j
          Next i
          ld = d(N, m)
      End Function
      
      Public Function sim(ByVal str1 As String, ByVal str2 As String)
          Dim ldint As Integer
          ldint = ld(str1, str2)
          Dim strlen As Integer
          If (Len(str1) >= Len(str2)) Then
              strlen = Len(str1)
          Else
              strlen = Len(str2)
          End If
          If strlen = 0 Then sim = 0 Else sim = 1 - ldint / strlen
      End Function
      
      
      posted in Excel & VBA
      M
      Mengkelyu
    • R Cox 分析可不可以计算生存时间?[To be Continued]

      参考:
      https://socialsciences.mcmaster.ca/jfox/Books/Companion-2E/appendix/Appendix-Cox-Regression.pdf
      https://cran.r-project.org/web/packages/survival/survival.pdf
      https://stackoverflow.com/questions/43173044/how-to-compute-the-mean-survival-time

      posted in R
      M
      Mengkelyu
    • 常用代码速查表
      1. 读取数据
      read.csv()
      read.table()
      
      1. lapply 应用于列表数据,类似Python的List Comprehension
      lapply(input, myfun, arg1=6)
      

      这里的arg1是myfun中另外的一些不用于循环的argument

      1. 列表转化为data.frame
      name <- c("Tom", "Aron", "Gary","Jeannie")
      gender <- c("Male", "Male", "Male", "Female")
      age <- c(18, 20, 17, 21)
      # Create a list from a vector
      student_list <- list(
        name = name,
        gender = gender,
        age = age
        )
      # Print the list of vectors  
      student_list
      
      # Convert the list to data frame
      student_data = as.data.frame(student_list)
      # Print the data frame
      student_data
      
      
      posted in R
      M
      Mengkelyu
    • RE: 如何用VBA做一个Weekly的Email Alert

      Email reminder public.zip

      posted in Excel & VBA
      M
      Mengkelyu
    • RE: 如何用VBA做一个Weekly的Email Alert

      更新:已经完全实现自动化了

      写了一个VBS文件,现在双击这个VBA文件就可以运行上面的宏

      'Input Excel File's Full Path
        ExcelFilePath = "My email Path"
      
      'Input Module/Macro name within the Excel File
        MacroPath = "Auto.AutoAll" '这里是运行的宏所在的Module+ 宏名称
      
      'Create an instance of Excel
        Set ExcelApp = CreateObject("Excel.Application")
      
      'Do you want this Excel instance to be visible?
        ExcelApp.Visible = False 'or "False"
      
      'Prevent any App Launch Alerts (ie Update External Links)
        ExcelApp.DisplayAlerts = False
      
      'Open Excel File
        Set wb = ExcelApp.Workbooks.Open(ExcelFilePath)
      
      'Execute Macro Code
        ExcelApp.Run MacroPath
      
      'Save Excel File (if applicable)
        wb.Save
      
      'Reset Display Alerts Before Closing
        ExcelApp.DisplayAlerts = True
      
      'Close Excel File
        wb.Close
      
      'End instance of Excel
        ExcelApp.Quit
      
      'Leaves an onscreen message!
        MsgBox "Your Automated Task successfully ran at " & TimeValue(Now), vbInformation
      

      然后找到Windows自带的Task Scheduler程序

      d9cc9026-6bdb-471e-9aa3-0b097ea58dd6-image.png

      这里点击Create Basic Task,按照指示操作即可

      32453763-1caa-490c-aa9a-8906e12d0aed-image.png

      posted in Excel & VBA
      M
      Mengkelyu