2020年5月22日 星期五

用Do While迴圈尋找不定數量結果以範例字串切割為例

用Do While迴圈尋找不定數量結果以範例字串切割為例

練習檔 [下載]
這個範例是學員工作上的問題,
每天都需要將儲存格中的超連結取出到B欄中,
若儲存格中只有一個超連結還好解決,
可以用Find函數找中括弧位置,再用Mid函數切割,
剛好這個範例裡面不只一個超連結,
可能有兩個、三個甚至更多,
也就是數量不定,如果要用For迴圈,也要知道數量範圍,
所以只能用 Do While 迴圈了,
從第一個字找起,之後再從找到的位置加一再找了,該如何做。
預覽影片:

一、函數

=FIND(C$1,A2)

=FIND(D$1,A2)

=MID(A2,C2+1,D2-C2-1)


如果用VBA撰寫的程式

一、階段一,先撰寫只取一個超連結

外面的For迴圈是跑每一列,用 Instr函數找"【<"和">】",

分別放在將找到位置的值放在 a和b 中,

如果a或b為0,表示找不到。


Sub 字串切割()
    '1.迴圈範圍
    For i = 2 To Range("A2").End(xlDown).Row
        '2.取得頭尾位置與切割字串
        a = VBA.InStr(Cells(i, "A"), "【<")
        b = VBA.InStr(Cells(i, "A"), ">】")
        If a <> 0 Then
            '5.輸出結果
            Cells(i, "B") = Mid(Cells(i, "A"), a + 1, b - a)
        End If
    Next
End Sub

如果多個超連結,可以先多產生 a1和b1變數,預設值為 1,

即從頭找起,找到之後再把  a1和b1 加1之後繼續找,

直到找不到為止,Do While 後面就是邏輯,為 True 就繼續找,

反之就離開迴圈了。


Sub 字串切割_所有超連結()
    '1.迴圈範圍
    For i = 2 To Range("A2").End(xlDown).Row
        '兩個位置初始值,從1開始找
        a1 = 1
        b1= 1
        '2.取得頭尾位置與切割字串
        '當找到關鍵字就執行以下程序
        Do While InStr(a1, Cells(i, "A"), "【<") <> 0
            a = InStr(a1, Cells(i, "A"), "【<")
            b = InStr(b1, Cells(i, "A"),  ">】")
            S = S & Mid(Cells(i, "A"), a + 1, b - a) & Chr(10)
            a1 = a + 1
            b1 = b + 1
        Loop
        '輸出到B欄
        Cells(i, "B") = S
        '清空變數資料
        S = ""
    Next
End Sub

以下是清除資料的程式碼


Public Sub 清除()
    Range("B2:B" & Range("B2").End(xlDown).Row).ClearContents
End Sub

以上範例主要學會如何用 VBA的 Instr與Mid函數取出要的資料,

如果範圍不定,一定要懂得使用 Do While迴圈了。


教學影音(完整版在論壇):
<iframe width="560" height="315" src="https://www.youtube.com/embed/Prxi3BpBCMk" frameborder="0" allow="accelerometer; autoplay; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>

教學影音完整版在論壇:
https://groups.google.com/forum/#!forum/scu_excel_vba2_107

EXCEL VBA進階班的課程規劃

主要是延伸入門課,延伸資料庫、多工作表、工作簿、網路爬蟲、視覺化報表等應用並與Python程式協同應用
單元01_資料拆解相關(VBA)
單元02_輸入自動化與表單設計
單元03_用ADO匯入與匯出資料庫
單元04_大量工作表合併與分割
單元05_資料查詢(篩選與分割工作表)
單元06_下載網路資料(YAHOO股市)
單元07_活頁簿與檔案處理(工作表分割與合併活頁簿)
單元08_視覺化報表與快速匯入圖片

其他相關學習:
    函數東吳進修推廣部, EXCEL, EXCEL VBA 函數,程式設計,線上教學

    2020年2月9日 星期日

    單元01_建置Python開發環境(用懶人包快速建立)

    單元01_建置Python開發環境(用懶人包快速建立)


    開發環境和EXCEL VBA相較,Python真的太麻煩了,
    VBA只要開啟開發人員標籤,就可以開始寫程式了,

    另外EXCEL本身就可以充當資料庫來使用,資料也可以隨時存放
    但這些都是Python無法做到的
    原因應該和商用軟體(OFFICE)自由軟體(PYTHON)的差異,
    商用軟體有收費自然就有必要幫大家把需要的環境做好,
    但衍生的就是需要不斷收費,
    自由軟體因為不用收費,只要不是商業用途,都沒有費用問題,
    但也就需要什麼都自己來,有點像自助餐的概念,什麼都要自己來,
    所有建置環境也就需要自己準備好
    甚至,建置環境的方法至少幾十種,
    那一種最好?
    我想為了Python的教學便利,不讓大家太麻煩,
    所以幫大家準備好Pyhon建置環境懶人包
    只要將兩個檔案解壓縮到D碟(或C碟)中
    在直接執行Eclipse的執行檔就好,
    Eclipse免安裝程式,可是是為Python相當好用的編輯器,
    開啟後在和Pyhton連結就好,再設定好編碼方式為UTF-8
    就可以開始寫Python程式了,
    至於後面的VBA可以把EXCEL當成存取資料資料庫來用,
    後面會再講解對應到Python的檔案資料庫使用,
    以下說明如何使用。

    教學影音(完整版在論壇):


    使用懶人包

    先下載
    eclipse_python.zip  [下載]
    Python37-32.zip  [下載]


    1.下載到C碟或D碟中

    2.解壓縮(不可以有多一層目錄)

    3.開啟ECLIPSE與設定

    (若出現JAVA開頭訊息,只要安裝JDK或JRE就可以)

    設定PYTHON路徑

    找Pydev的Python Interpreter 並瀏覽 Python37-32資料夾裡的 Python.exe

    正確結果畫面

    4.新增專案

    選Other-->PyDev-->PyDev Project

    輸入專案名稱,版本3.6,並Create src folder(產生src資料夾)

    5.新增Moudle(在src上按右鍵)

    6.輸入程式並執行

    7.修改編碼為UTF-8:

    8.字型放大與縮小

    CTRL+中間的+和-


    結語:

    和EXCEL VBA相較之下,Pyhton的開發環境麻煩太多,
    但帶來的好處是費用上的節省,
    和未來有用不完的外掛可以擴充,所以如果你的應用需求很簡單,
    建議用VBA就好,但如果需要更複雜的處理工作,
    也不想付錢給軟體公司,Python是很好的選項。

    課程理念與課程介紹:

    因應大數據分析、物聯網、工業4.0的需求,能更容易的學會網路爬蟲、機器學習、物聯網、影像辨識、自動圖像報表等需求,其中以EXCEL VBA與Python程式開發最為熱門,因此將VBA的自動化延伸到PYTHON設計,讓學員能夠比較兩個工具的長處,並能相互協同應用。


    課程內容

    單元01_建置Python開發環境與程式測試
    單元02_基本語法與結構控制件
    單元03_迴圈資料結構與自訂函數
    單元04_串列、字典與檔案與資料庫處理
    單元05-1_開放資料處理CSV和JSON資料處理(停車與PM2.5)
    單元05-2_開放資料處理練習題_新北市開放資料JSON
    單元05-3_GOOGLE雲端當CSV來源與CSV處理
    單元05-4_網頁資料擷取基礎與外匯
    單元05-5_網頁資料擷取台彩與股市資料
    單元05-6_擷取網頁上櫃股票行情
    單元06_使用Pandas與處理_Excel_試算表
    單元07_VBA與Phython連結MYSQL資料庫

    單元08_視覺化報表使用圖表繪製Matplotlib


    完整教學影音DVD申請:http://goo.gl/ZlBZE

    相關學習: