久久综合丝袜日本网手机版,日韩欧美中文字幕在线三区,亚洲精品国产品国语在线,极品在线观看视频婷婷

      <small id="aebxz"><menu id="aebxz"></menu></small>
    1. 用access和excel進行數(shù)據(jù)分析總結(jié)

      時間:2022-07-10 20:15:45 總結(jié)范文 我要投稿
      • 相關(guān)推薦

      用access和excel進行數(shù)據(jù)分析總結(jié)

      #數(shù)據(jù)庫操作(如表的合并,跨表查詢,統(tǒng)計最大最小數(shù)值等)適合用access完成

      用access和excel進行數(shù)據(jù)分析總結(jié)

      #excel適合處理二維表,對單元格的簡單計算,如下是典型的二維表


      600600600601600602600603600604

      青島啤酒方正科技廣電電子ST興業(yè)二紡機
      2002-1-47.4613.0210.0411.089.27
      2002-1-77.3112.99.9211.18.85
      2002-1-87.312.999.9511.118.73
      2002-1-97.1912.9210.0411.448.25
      2002-1-107.3512.9410.0911.958.4
      2002-1-117.2812.669.7711.688.04
      2002-1-147.0812.019.511.547.73
      2002-1-156.9912.028.9511.347.67
      2002-1-167.1511.998.9311.157.88
      2002-1-176.9111.588.410.047.19
      2002-1-186.911.378.29.346.9

      #一般思路: 對于原始的excel表,先利用access的SQL語句進行分組,排序,跨表合并

      根據(jù)具體要求再利用excel的公式或VBA完成其他復(fù)雜的功能

      幾點經(jīng)驗:

      1.access中要進行跨表查詢,必須先建立"關(guān)系",可使用工具->關(guān)系菜單進行設(shè)置

      2. 常見SQL語句

      a)最大最小,排序處理

      SELECT AFE_statv2ACCURACY.forecast_Stkcd, AFE_statv2ACCURACY.YEAR, Min(AFE_statv2ACCURACY.RAW_DAYS_ELAPSED) AS RAW_DAYS_ELAPSED之最小值, Max(AFE_statv2ACCURACY.RAW_DAYS_ELAPSED) AS RAW_DAYS_ELAPSED之最大值

      FROM AFE_statv2ACCURACY

      GROUP BY AFE_statv2ACCURACY.forecast_Stkcd, AFE_statv2ACCURACY.YEAR

      ORDER BY AFE_statv2ACCURACY.forecast_Stkcd, AFE_statv2ACCURACY.YEAR;

      b)刪除數(shù)據(jù)

      DELETE *

      FROM AFE_statv2無空項

      WHERE AFE_statv2無空項.forecast_AFE最大值=AFE_statv2無空項.forecast_AFE最小值

      c) 表的連接:

      INNER JOIN保留公共數(shù)據(jù),LEFT JOIN和RIGHT JOIN分別完整保留左,右表數(shù)據(jù),必要時通過DISTINCT參數(shù)去除重復(fù)的記錄

      d)日期函數(shù) Year等

      3. excel VBA代碼分析

      Public Sub firstVBA()

      Dim thisdate As Date

      Dim wday As Integer

      Dim rno As Integer

      Dim stkcd As String

      Dim ananm As String

      Dim findStk1 As Range

      Dim findStk2 As Range

      Dim findStk3 As Range

      Dim findStk6 As Range

      Dim findStk7 As Range

      Dim findStk8 As Range

      Dim findStk9 As Range

      Dim findStk10 As Range

      Dim findIndex As Range

      Dim findDate As Range

      Dim TwoDayB As Integer

      Dim TwoDayA As Integer

      Dim OneDayB As Integer

      Dim OneDayA As Integer

      Dim FindCol As Integer

      Dim CR_FIVE As Double

      Dim CR_THREE As Double

      Dim Revp As Double

      Dim indexTwoDayB As Integer

      Dim indexTwoDayA As Integer

      Dim indexOneDayB As Integer

      Dim indexOneDayA As Integer

      Dim CR_Index_FIVE As Double

      Dim CR_Index_THREE As Double

      Sheet11是最終存放結(jié)果的表,Sheet1-3,6-10存放了各日各股票的價格,sheet5存放上證,深證指數(shù)

      For rno = 2 To 6864

      stkcd = Sheet11.Range("A" & rno).Value 查找股票代碼

      thisdate = Sheet11.Range("E" & rno).Value 查找日期

      Set findStk1 = Sheet1.Range("1:1").Find(stkcd)

      Set findStk2 = Sheet2.Range("1:1").Find(stkcd)

      Set findStk3 = Sheet3.Range("1:1").Find(stkcd)

      Set findStk6 = Sheet6.Range("1:1").Find(stkcd)

      Set findStk7 = Sheet7.Range("1:1").Find(stkcd)

      Set findStk8 = Sheet8.Range("1:1").Find(stkcd)

      Set findStk9 = Sheet9.Range("1:1").Find(stkcd)

      Set findStk10 = Sheet10.Range("1:1").Find(stkcd)

      查找股票在哪張表中

      If Not findStk1 Is Nothing Then

      Set findStk = findStk1

      Sheets("sz_stk1").Select

      ElseIf Not findStk2 Is Nothing Then

      Set findStk = findStk2

      Sheets("sz_stk2").Select

      ElseIf Not findStk3 Is Nothing Then

      Set findStk = findStk3

      Sheets("sz_stk34").Select

      ElseIf Not findStk6 Is Nothing Then

      Set findStk = findStk6

      Sheets("sh_stk1").Select

      ElseIf Not findStk7 Is Nothing Then

      Set findStk = findStk7

      Sheets("sh_stk2").Select

      ElseIf Not findStk8 Is Nothing Then

      Set findStk8 = findStk8

      Sheets("sh_stk3").Select

      ElseIf Not findStk9 Is Nothing Then

      Set findStk = findStk9

      Sheets("sh_stk4").Select

      ElseIf Not findStk10 Is Nothing Then

      Set findStk = findStk10

      Sheets("sh_stk5").Sele

      【用access和excel進行數(shù)據(jù)分析總結(jié)】相關(guān)文章:

      數(shù)據(jù)庫access的優(yōu)缺點總結(jié)04-21

      Access數(shù)據(jù)庫實驗報告03-01

      數(shù)據(jù)分析個人總結(jié)01-09

      數(shù)據(jù)分析工作總結(jié)06-26

      數(shù)據(jù)分析報告07-28

      大數(shù)據(jù)分析07-20

      銷售數(shù)據(jù)的分析方法07-25

      大數(shù)據(jù)分析07-25

      數(shù)據(jù)分析個人總結(jié)3篇01-09

      多維數(shù)據(jù)分析方法04-07