Dim fso,pdOne,f
Dim OneLineTxt As String
Dim iT,jT As Integer
Dim iPos1,iPos2,iIndex As Integer
Dim iTSheet1,jTSheet1 As String
Dim StringTemp1,StringTemp2 As String
Dim StrFileName,StrMachineNo As String
Dim strTemp() As String
Dim strTemp() As String
Dim DateTimeng As String
Dim DateTimeAdd1Min As Date
Dim DateTimeMinus1Min As Date
Dim DataFlg As Integer
Private Sub CommandButton1_Click()
Call SelectFile2
End Sub
‘ファイルパスからファイル名称の取得
Private Function GetFileName(ByVal s As String)
Dim sname() As String
sname = Split(s,”\”)
GetFileName = sname(UBound(sname))
End Function
‘CSVファイルの取得
Sub SelectFile2()
‘www.okexcel.com.cn
With Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = False
,Filters.Clear
,Filters.Add “CSV Files”,”.csv” ,Filters.Add “All Files”,”.*”
If .Show = -1 Then
Sheet2.Cells(3,4) = .SelectedItmes(1)
StrFileName = GetFileName(Sheet2.Cells(3,4))
StrMachineNo = Mid(StrFileName,7,1)
Sheet2.Cells(4,4) = Mid(StrFileName,7,1) + “面”
End If
End With
End Sub
Private Sub commandButton2_Click()
Call ReadCSVFile
End Sub
Sub ReadCSVFile()
Set fso = CreateObject(“Scripting.FileSystemObject”)
pd1 = fso.FileExist(Cells(3,4).Value)
If 0 = pd1 Then
MsgBox “指定されたCSVファイルが存在しません。”
Exit Sub
End If
StrFileName = GetFileName(Sheet2.Cells(3,4))
StrMachineNo = Mid(StrFileName,7,1)
Sheet2.Cells(4,4) = Mid(StrFileName,7,1)+”面”
Set f = fso.OpenTextFile(Cells(3,4).Value,1,False)
‘予め、前データのクリア
iT = 8
jT = 2
DataFlg = 0
StringTemp1 = “”
StringTemp2 = “”
Do While f.atendofstream <> True
OneLineTxt =f.ReadLine()
If DataFlg = 0 Then
iPos1 = InStr(OneLineTxt,"CTRLTIME")
If iPos1 > 0 Then
DataFlg = 1
strTemp = Split(OneLineTxt,",")
iIndex = 0
Do While strTemp(iIndex) <> ""
If strTemp(iIndex) = "CTRLTIME" Then
Exit Do
End If
iIndex = iIndex +1
Loop
End IfElse
If Len(OneLineTxt) > 100 Then
If Mid(OneLineTxt,1,1) <> “-” Then
strTemp = Split(OneLineTxt,”,”)
Cells(iT,jT) = strTemp(iIndex)
Cells(iT,jT+1)=”×”
DateTimeAdd1Min = DateAdd("n",1,Cells(iT,jT))
DataTimeMinus1Min = DateAdd("n",-1,Cells(iT,jT))
iTSheet1 = 8
iTSheet1 = 2
Do While Trim(Sheet1.Cells(iTSheet1,jTSheet1)) <> "" Or Trim(Sheet1.Cells(iTSheet1,jTSheet+1)) <> "" Then
iPos1 = Instr(Sheet1.Cells(iTSheet1,jTSheet1+1),StrMachineNo)
If iPos1 > 1 Then
If Sheet1.Cells(iTSheet1,jTSheet1) <= DataTimeAdd1Min And Sheet1.Cells(iTSheet1,jTSheet1) >= DataTimeMinus1Min Then
Sheet2.Cells(iT,jT+1) = "○"
Exit Do
End If
End If
iTSheet1 = iSHeet1 +1
Loop
iT = iT +1
End If
Else
Set f = Nothing
Set fso = Nothing
Exit Sub
End if
End IfLOOP
‘Set f = f.Close()
Set f = Nothing
Set fso = Nothing
End Sub
Sub ClearAllData()
iT = 8
jT= 2
Do While iT < 200
Cells(iT,jT) = “”
Cells(iT,jT +1)= “”
iT = iT +1
LOOP
End Sub
Private Sub CommandButton3_Click()
Call ClearAllData2
End Sub
VB Command
❶
=COUNTIF(E6:E27,”N”)
❷
=IF(SUM(E28:E32)=0,0,E28/SUM(E28:E32))
❸
=COUNTIF(E6:E27,”E”)
❹
=COUNTIF(E6:27,”I”)
❺
=SUM(E28:E32)
❼
=IF(E13>=”A”,TEXT(COUNTIF(E$6:E13,”>=A”),”000″),””)
❽
=IF(E18>=”A”,TEXT(COUNTIF(E$6:E18,”>=A”),“000”),””)
❾
VLOOUP FIND
❿
excel的功能十分强大,vlookup函数的作用也很强大,是excel函数中最重要的函数之一,可以帮助我们在很多数据中找到我们想要的答案,那这个函数该怎么用呢?有没有实例可以参考?有!excel中vlookup函数的使用方法请看下面实例!
⓫
计算符合多个条件的数据的个数，我们通常会用到的就是COUNTIFS函数，或COUNTIF。
COUNTIFS既能解决多个条件的计数，也能解决单个条件的计数，而COUNTIF函数只能解决单个条件的计数，所以，我们一般只需要掌握COUNTIFS函数就可以了。
具体的用法请看下方敏丝图解。
