SA2.docx 版本02

解释公式：

代码部分 Option Explicit Const key = "your key" # 这里写入我们在准备工作中拿到的API Key Sub SendQuestionToGPT3() 'Declare variables Dim request As Object Dim response As String Dim API As String API = key #这里我们把问题放到了B3里面 Dim question As String question = """" & Range("B3").Value & """" #创建HTTP request Set request = CreateObject("MSXML2.XMLHTTP") #设置HTTP 请求 request.Open "POST", "https://api.chatanywhere.com.cn/v1/chat/completions", False request.setRequestHeader "Content-Type", "application/json" request.setRequestHeader "Authorization", "Bearer " & API request.send "{""model"": ""gpt-3.5-turbo"",""messages"": [{""role"": ""user"", ""content"": " & question & "}],""temperature"": 0.7 }" #解析返回值，这里用到了我们刚刚import进去的library response = request.responseText response = Replace(response, "[{", "{") response = Replace(response, "}]", "}") Dim ParsedResponse As Object Set ParsedResponse = JsonConverter.ParseJson(response) #把返回的答案输入到excel里面 Range("B6").Value = ParsedResponse("choices")("message")("content") 'Clean up the object Set request = Nothing End Sub

可以利用GetSubtractRng这个计算差集的函数来得到右下角三角形的范围 Function GetSubtractRng(r1 As Range, r2 As Range) Dim r As Range, r3 As Range For Each r In r1 If Intersect(r, r2) Is Nothing Then If r3 Is Nothing Then Set r3 = r Else Set r3 = Union(r, r3) End If End If Next Set GetSubtractRng = r3 End Function Sub RBTriangle() Dim row As Integer Dim col As Integer row = Selection.Rows.Count col = Selection.Columns.Count Dim rg As Range Dim targetRg As Range Dim i As Integer Set rg = Selection.Resize(row, 1) Set targetRg = Selection.Resize(row, 1) For i = 1 To col - 1 If row - i > 0 Then Set targetRg = Union(targetRg, rg.Offset(0, i).Resize(row - i, 1)) Next i Set targetRg = GetSubtractRng(Selection, targetRg) targetRg.Select End Sub

@Sherry: csm和fullilment cf在initial recognition的时候通常会互为相反数。你可以把初始的csm理解为是在计算出fullilment cf并且知道合同组盈利的情况下把它变为不赚不赔状态的一个金额。 就像这张图第二行写的，csm本质上就是个unearned profit。 @Mengke 之所以在contract开始的时候合同组的financial要变成是0，是因为service还没有真正发生。 所以 CSM + fullilment cf 在一年的变化就是这一年提供保险服务所获得的利润。 又可以被分成insurance revenue， insurance service expenses，insurance finance income or expenses。

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

@Mengkelyu 注意到这个代码在工作簿中有公式或数字错误时会报 "type Mismatch"的错误。 可以在代码开头加入 On Error Resume Next 来跳过这些错误，继续运行代码。