##title##

顯示具有 軟體 | Excel 標籤的文章。 顯示所有文章
顯示具有 軟體 | Excel 標籤的文章。 顯示所有文章

2021年2月20日

Excel：目標搜尋、運算列表、分析藍本、規劃求解

Excel功能越來越方便，目標搜尋、運算列表、分析藍本、規劃求解都太厲害了...。



blogger: kslman 於 晚上8:29 0 意見
標籤：

2021年1月8日

Excel、Google Sheet：改善計算效能

 

Excel 效能：改善計算效能

https://docs.microsoft.com/zh-tw/office/vba/excel/concepts/excel-performance/excel-improving-calculation-performance


指定試算表的位置和計算設定

https://support.google.com/docs/answer/58515?co=GENIE.Platform%3DDesktop&hl=zh-Hant#zippy=%2C%E9%81%B8%E6%93%87%E5%85%AC%E5%BC%8F%E8%A8%88%E7%AE%97%E9%A0%BB%E7%8E%87

Google 雲端硬碟可存放的檔案（試算表欄位上限）

https://support.google.com/drive/answer/37603?visit_id=637456858488425003-3884791923&rd=1


Google 試算表的大小和複雜度上限（裡面描述欄位上限資料是舊的，僅參考其他資料）

http://blog.ilc.edu.tw/blog/index.php?op=printView&articleId=281359&blogId=18181

blogger: kslman 於 下午3:04 0 意見
標籤： ,

2020年12月24日

Excel：index+small+if陣列公式，查詢條件後回傳多個值（彌補vlookup或hlookup只能回傳單一值的遺憾）

要回傳值通常會用vlookup或hlookup函數，但只能回傳一個值。要回傳多個值，就必須用上index+small+if再加上陣列公式。

例如：

=INDEX('工作表'!$B:$B,SMALL(IF(('工作表'!$A$2:$A$9998='1221~1225'!C$2)*('工作表'!$D$2:$D$9998='1221~1225'!$B3),ROW('工作表'!$2:$9998),4^8),ROW(indirect("'工作表'!A"&$A3))))
（以上須使用ctrl+shift+enter作為陣列公式，google sheet則是要加ArrayFormula公式） 

「'工作表'!$B:$B」是要回傳的欄。

「('工作表'!$A$2:$A$9998='1221~1225'!C$2)*('工作表'!$D$2:$D$9998='1221~1225'!$B3)」是條件，這邊是透過布林值計算。。

符合條件會回傳對應的行號。不符合條件時，則直接返回4^8，也就是65536，一般的工作表這裏就沒有數據。

在Excel 2003版本，65536就是一列中的最大行號。2010以上是4^10，也就是1048576行， 
至於要回傳值而不是設定空的原因，是因為要供SMALL作排序。

所以數列的結果就會類似：
{1;65536;……;65536;12;13;65536;65536;65536}


參考：
blogger: kslman 於 上午10:49 0 意見
標籤： ,

2020年7月11日

Excel：Hyperlink做超連結或跳轉分頁或檔案、CELL的filename參數取得相對位置

A1填入分頁名稱，例如分頁名稱是0710，這樣的意思是用A1的分頁名稱0710來跳轉到0710分頁。例如：

=HYPERLINK("#"&A1&"!"&"S1",A1)

參考連結：
https://kknews.cc/zh-tw/tech/3x4egg.html


另外也可透過cell參數"filename"來取得當前檔案所在路徑來連結檔案。例如：

=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1))-1)


參考連結：
https://www.ptt.cc/bbs/Office/M.1468241550.A.CF4.html
blogger: kslman 於 晚上8:44 0 意見
標籤：

2020年7月7日

Excel：利用MOD計算下個星期幾的日期

找到一個方法，很巧妙的利用除法（MOD函數是除法傳回餘數）解決這個問題：https://www.mrexcel.com/board/threads/eoweek-formula-require-something-similar-to-eomonth-for-a-weekly-basis.871200/


例如：
=today()-mod(today(),7)+9

這樣就是計算下個星期一。


關於MOD函數： https://support.microsoft.com/zh-tw/office/mod-%E5%87%BD%E6%95%B8-9b6cd169-b6ee-406a-a97b-edf2a9dc24f3
blogger: kslman 於 下午3:43 0 意見
標籤：

2020年5月29日

Excel：INDEX+MATCH，結合多個欄位的比對並回傳值

除了之前提過的：Excel：INDEX+MATCH=退著VLOOKUP

INDEX+MATCH還可以結合多個欄位的比對並回傳值。

例如A欄位、B欄位和C欄位結合一起，才是想要查找的目標C1與D1的結合。

可以用這樣的方式查找：
=index(要回傳的欄位,match(要結合的目標,要查找的目標,0))
例如。
=index(F:F,match(A1&B1&C1,D1&E1,0))
或可以用字串的方式，例如。
=index(F:F,match(A1&B1&"專案X"&C1,D1&E1,0))


參考資料：
https://junshienwu.pixnet.net/blog/post/347876995-%5Bexcel%5D-%E8%BC%B8%E5%85%A5%E5%85%A9%E5%80%8B%E6%AF%94%E5%B0%8D%E6%A2%9D%E4%BB%B6%EF%BC%8C%E5%B8%B6%E5%87%BA%E6%89%80%E5%B0%8D%E6%87%89%E7%9A%84%E6%AC%84%E4%BD%8D

blogger: kslman 於 下午5:58 1 意見
標籤：

2020年5月28日

Excel、Google Sheet：陣列公式

假設一個資料長這樣：

A B C
4 4 5
3 3 5
4 4 5
3 3 5
2 2 5
2 2 3


想要的目標是如果A等於B的時候，加總C。

Excel雖然有SUMIF函數，但如果公式是：

=SUMIF(A:A,B:B,C:C)

這樣是不能運作的，因為SUMIF的條件是等於某個值或字串。

同理，如果公式是：

=sum(if(A:A=B:B,C:C,""))

，這樣也不能運作

所以這時候可以改用陣列公式：
=sum(if(A:A=B:B,C:C,""))

陣列公式輸入後要按Ctrl+Shift+Enter才能作用。

至於在Google試算表上，按Ctrl+Shift+Enter則會自動將上述公式改為：
=ArrayFormula(=sum(if(A:A=B:B,C:C,"")))
blogger: kslman 於 中午12:35 0 意見
標籤： ,

2020年5月12日

Excel：INDIRECT應用，函數的引數使用欄位的值

有時候函數的引數，會希望包含在欄位的值（例如A1欄的值是ABC），可以用INDIRECT這樣寫。


INDIRECT(A1&"!O:O")

這樣就會導向ABC這個sheet中的O欄。


語法

INDIRECT(ref_text, [a1])

INDIRECT 函數語法具有下列引數：

    Ref_text    必要。單一儲存格的參照，其中包含 A1 樣式參照、R1C1 樣式參照、定義為參照的名稱，或定義為文字字串的儲存格參照。如果 ref_text 不是有效的儲存格參照，INDIRECT 會傳回 #REF! 錯誤值。

        如果 ref_text 參照另一個活頁簿 (外部參照)，則必須開啟另一個活頁簿。如果來源活頁簿未開啟，INDIRECT 會傳回 #REF! 錯誤值。

        附註    Excel Web App 不支援外部參照。

        如果 ref_text 參照的儲存格範圍超出 1,048,576 的列限制或 16,384 (XFD) 的欄限制，INDIRECT 會傳回 #REF! 錯誤。

        附註    在 Microsoft Office Excel 2007 之前的舊版 Excel 中，此行為是不同的，會忽略超出的限制並傳回值。

    A1    選用。指定 ref_text 儲存格中所包含參照類型的邏輯值。

        如果 a1 為 TRUE 或省略，則 ref_text 會被解釋成 A1 樣式參照。

        如果 a1 為 FALSE，則 ref_text 會被解譯成 R1C1 樣式參照。



https://support.office.com/zh-tw/article/INDIRECT-%E5%87%BD%E6%95%B8-474b3a3a-8a26-4f44-b491-92b6306fa261
blogger: kslman 於 晚上7:46 0 意見
標籤：

2020年1月7日

Excel：判斷斷行文字，CHAR(10)

有時候需要判斷文字是否有斷行，例如我想判斷文字內容是：
「
道具量
（萬）
」
因為有斷行，如果判斷是：

=if($D$2="道具量（萬）",'現有道具'!D2,0)/10000

的時候，會出現錯誤，但這時候如果是用：

=if($D$2="道具量"&CHAR(10)&"（萬）",'現有道具'!D2,0)/10000

才可正常判斷。

CHAR是excel字元集的內容，詳請可參閱Office的CHAR函數說明：

https://support.office.com/zh-tw/article/char-%E5%87%BD%E6%95%B8-bbd249c8-b36e-4a91-8017-1c133f9b837a
blogger: kslman 於 凌晨12:28 0 意見
標籤：

2019年10月9日

Excel：計算範圍內的字串數量

組合多個函數計算的結果以及陣列公式竟然可以在一個欄位中計算出來某個範圍內特定字串的數量，很有趣。

但網頁的說明已經很清楚了，就不贅述了：
https://docs.microsoft.com/zh-tw/office/troubleshoot/excel/formulas-to-count-occurrences-in-excel

類似這樣的方式也可以：

="檔名（共"&COUNTA(A2:A100)&"個檔案，如扣除修改則"&COUNTA(A2:A100)-SUM(LEN(A2:A100)-LEN(SUBSTITUTE(A2:A100,"修","")))/LEN("修")&"個）"


僅記錄。
blogger: kslman 於 下午5:26 0 意見
標籤：

2019年10月1日

Excel：LOOKUP

找接近數值時可以用的函數。而且比vlookup方便的是，他可以直接回傳往左邊欄位的內儲存格內容。但缺點是，如果找不到直，可能會傳回不完全相符的結果。


語法
LOOKUP(lookup_value, lookup_vector, [result_vector])

LOOKUP 函數的向量形式語法具有下列引數：


  • lookup_value    必要。 這是 LOOKUP 在第一個向量中要尋找的值。 Lookup_value 可以是數字、文字、邏輯值，或是參照某個值的名稱或參照。


  • lookup_vector    必要。 僅包含一列或一欄的範圍。 lookup_vector 中的值可以是文字、數字或邏輯值。

重要: lookup_vector 中的值必須以遞增順序排列：..., -2, -1, 0, 1, 2, ...、A-Z、FALSE、TRUE，否則，LOOKUP 可能不會傳回正確的值。 文字不區分大小寫。

  • result_vector    選用。 僅含一列或一欄的範圍。 result_vector 引數的大小必須與 lookup_vector 相同。 其大小必須一樣。


備註
如果 LOOKUP 函數找不到 lookup_value，就會比對 lookup_vector 中小於或等於 lookup_value 的最大值。

如果 lookup_value 小於 lookup_vector 中的最小值，LOOKUP 函數會傳回 #N/A 的錯誤值。


參考資料：
https://support.office.com/zh-hk/article/lookup-%E5%87%BD%E6%95%B8-446d94af-663b-451d-8251-369d5e3864cb
blogger: kslman 於 晚上10:39 0 意見
標籤：

2019年9月17日

Excel：OFFSET

之前常遇到一些固定錯位的表格，通常可以用OFFSET作便利的整理。

語法 OFFSET(reference, rows, cols, [height], [width])
OFFSET 函數語法具有下列引數：

  • Reference 必要。 這是用以計算位移的起始參照。 Reference 必須參照一個儲存格或相鄰的儲存格範圍，否則 OFFSET 會傳回 #VALUE! 錯誤值。
  • Rows 必要。 這是要左上角儲存格往上或往下參照的列數。 使用 5 做為 rows 引數，指出參照的左上角儲存格是 reference 下方的第五列。 Rows 可以是正數 (表示在起始參照下方) 或負數 (表示在起始參照上方)。
  • Cols 必要。 這是要結果的左上角儲存格向左或向右參照的欄數。 使用 5 作為 cols 引數，指出參照位址的左上角儲存格是 reference 右方的第五欄。 Cols 可以是正數 (表示在起始參照右方) 或負數 (表示在起始參照左方)。 [高度] 選擇性。 這是要傳回參照的列數高度。
  • Height 必須是正數。 寬度 選擇性。 這是要傳回參照的欄數寬度。
  • Width 必須是正數。


也可以透過一些計算來但判斷所要選取的範圍，例如：
=SUM(OFFSET(S3,0,$K$2,1,1):OFFSET(S3,0,$K$2+$L$2-1,1,1))

參考資料：https://support.office.com/zh-tw/article/offset-%E5%87%BD%E6%95%B8-c8de19ae-dd79-4b9b-a14e-b4d906d11b66
blogger: kslman 於 下午4:16 0 意見
標籤：

2019年8月14日

Excel：VBA，使用變數選擇範圍（Range）

自己試著從零開始寫的第一個VBA
Sub ()
v1 = Cells(5, 1)    '對應的列、欄位置，變數訂為v1
v2 = Cells(6, 1)    '對應的列、欄位置，變數訂為v2
'MsgBox (v1)    '訊息彈窗可以用來檢查變數v1
Range(v1 & ":" & v2).Select '選擇目標欄位（已變數回傳值所指定的範圍）
Selection.Copy  '複製所選
End Sub




其中我原本A4位置的公式=COUNTA(1:1)-1，是用來計算A5和A6的=ADDRESS(3,5,4)及=ADDRESS(3,5+A4-1,4)，所以可以改寫如下：


v1 = WorksheetFunction.CountA(Range("1:1")) - 1 '計算COUNTA(1:1)-1的值，將變數訂為v1
v2 = Cells(3, 5).Address    '計算ADDRESS(3,5,4)的值，將變數訂為v2
v3 = Cells(3, 5 + v1 - 1).Address    '計算ADDRESS(3,5 +v1 -1,4)的值，將變數訂為v3
Range(v2 & ":" & v3).Select '選擇目標欄位（已變數回傳值所指定的範圍）
Selection.Copy  '複製所選






參考資料：

EXCEL VBA從頭來過-基本語法(上篇)
https://medium.com/@weilihmen/excel-vba%E5%BE%9E%E9%A0%AD%E4%BE%86%E9%81%8E-%E5%9F%BA%E6%9C%AC%E8%AA%9E%E6%B3%95-%E4%B8%8A%E7%AF%87-c2bc76065ecd

提到cells、range


MsgBox 函式
https://docs.microsoft.com/zh-tw/office/vba/language/reference/user-interface-help/msgbox-function


如何在Excel VBA使用Excel工作表中的函數
http://wordpress.bestdaylong.com/blog/archives/2547


Thread: Using "Address" function in VBA?
https://www.mrexcel.com/forum/excel-questions/341078-using-address-function-vba.html
blogger: kslman 於 下午5:16 0 意見
標籤：

2019年8月7日

Excel：INDEX+MATCH=退著VLOOKUP

用vlookup的時候常常會遇到一個問題，只能傳回查的目標資料右方的資料。

如果用INDEX+MATCH就可以達到查目標資料左方的資料了。例如


=INDEX(C:C,MATCH(A2,D:D,0),1)

這樣的意思是在D欄查A2的資料，如果查到，回傳的值則當作INDEX函數中的列，並找出C欄對應列的值。


MATCH(lookup_value, lookup_array, [match_type])

MATCH 函數語法具有下列引數：

lookup_value    必要。 這是要在 lookup_array 中比對的值。 例如，當您在通訊錄中查閱某個人的號碼時，您是以那個人的姓名作為查閱值，但是電話號碼才是您要的值。

lookup_value 引數可以是一值 (數字、文字或邏輯值)，也可以是數字、文字或邏輯值的儲存格參照。

lookup_array    必要。 這是要搜尋的儲存格範圍。

match_type    選擇性。 數字 -1、0 或 1。 match_type 引數會指定 Excel 如何將 lookup_value 與 lookup_array 中的值相比對。 這個引數的預設值是 1。

下表將描述該函數如何根據 match_type 引數的設定來尋找值。

MATCH函數參考：
https://support.office.com/zh-hk/article/match-%E5%87%BD%E6%95%B8-e8dffd45-c762-47d6-bf89-533f4a37673a


INDEX(array, row_num, [column_num])

陣列形式的 INDEX 函數具有下列引數：

array    必要。 儲存格範圍或陣列常數。

如果陣列包含只有一個資料列或欄，則相對應的 row_num 或 column_num 引數是選擇性的。

如果陣列有一個以上的列與多個欄中，使用 [僅限 row_num 或 column_num 設，INDEX 會傳回整列或欄的陣列陣列中。

row_num    必要。 選取列中的值傳回的陣列。 如果省略 row_num，column_num 是必要的。

column_num    選用。 選取要從中傳回值的陣列中的欄。 如果省略 column_num，row_num 是必要的。

註解
如果使用了 row_num 與 column_num 引數，INDEX 會傳回 row_num 與 column_num 交集處儲存格中的值。

row_num 及 column_num 必須對應到陣列; 內的儲存格否則，INDEX 會傳回 #REF ！ 錯誤的非數字 (文字) 值，您的公式則會中斷。

如果將 row_num 或 column_num 設為 0 （零），INDEX 會分別傳回整欄或整列的值的陣列。 若要使用以陣列方式傳回的值，INDEX 函數輸入為陣列公式。

INDEX函數參考：
https://support.office.com/zh-hk/article/index-%E5%87%BD%E6%95%B8-a5dcf0dd-996d-40a4-a822-b56b061328bd
blogger: kslman 於 下午3:35 0 意見
標籤：

2019年6月20日

Excel：MID&SEARCH擷取特定字元間的文字

如果有一串文字是：
板主：xxx | 昨日人氣：1787211 | 昨日文章：3474

如果我想要的值是「1787211」，而我發現它前後有「氣：」和「 | 昨日文」這個字串是固定的。那麼我可以透過計算來知道目標值所在文字的起始數字來擷取目標值。假設該字串位置在D2，則範例如下：


=MID(D2,SEARCH("氣：",D2)+2,SEARCH(" | 昨日文",D2)-(SEARCH("氣：",D2)+2))



函數參考：

MID、MIDB 函數
https://support.office.com/zh-hk/article/mid%E3%80%81midb-%E5%87%BD%E6%95%B8-d5f9e25c-d7d6-472e-b568-4ecb12433028

SEARCH 與 SEARCHB 函數
https://support.office.com/zh-tw/article/search-%E8%88%87-searchb-%E5%87%BD%E6%95%B8-9ab04538-0e55-4719-a72e-b6f54513b495
blogger: kslman 於 上午11:01 0 意見
標籤：

2019年5月28日

Excel：TEXT &VALUE 函數，可把日期變為文字

如果用&去連結某個儲存格中的日期，會變成一串數字。（Excel紀錄日期的方式）

但如果用 TEXT 函數，就可把日期變為文字：

例如A1是日期，則

="今天是"&TEXT(A1, "M/D")

可讓儲存格顯示為：今天是1/12

而不會是：今天是43477

其他可參考：
https://support.office.com/zh-tw/article/text-%E5%87%BD%E6%95%B8-20d5ac4d-7b94-49fd-bb38-93d29371225c
blogger: kslman 於 晚上11:40 0 意見
標籤：

2017年11月24日

Excal：VBA，插入股票公開資料v1.3

插入股票公開資料的檔案我做了更新，供大家參考：
stock_sample_v1.3.xlsm
https://drive.google.com/open?id=1g5saowjYAeXNi73a0pss96-xZeXPUTXr

使用方式：
1. 「關注」的分頁C列填入股票代碼。
2.  點擊「關注」分頁左上「refresh」按鈕就可以刷新全部。
（判斷方式是16:00以前只刷新興櫃，16:00以後則全部更新。如果想假日更新最新的前一交易日，可以改TWN分頁的A10~A12）

調整內容：
1. 新增更新全部分頁的方式
2. 修正了櫃買中心的興櫃csv連結為：http://www.gretai.org.tw/storage/emgstk/ch/new.csv

目前問題：
1. 興櫃股票我抓的政府資料開放平臺的資料沒有前一天價格，所以沒有辦法算漲跌、漲跌幅和昨收。（如果有人知道哪邊抓的資料有漲跌或前一天價格可以跟我說一下）
2. P/E只有上市有。
3. 美股不知道哪邊有資料，有人知道那邊有美股類似證交所這樣一個表有全部股價資料的網站嗎？

新增的巨集內容如下。 
Private Sub CommandButton1_Click()

Sheets("TWN").Select
'宣告變數
    Dim QuerySheet As Worksheet
    Dim DataSheet As Worksheet
    Dim qurl As String
    Dim NOW, BN, LTH As Long
    NOW = Sheets("TWN").Range("A4")
    BN = Sheets("TWN").Range("A9")
    LTH = Sheets("TWN").Range("A8")
'告訴Excel不要每更新一格就重新計算
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
'將現在的工作表設為資料表
    Set DataSheet = ActiveSheet
    qurl = "http://www.gretai.org.tw/storage/emgstk/ch/new.csv"
'選擇TWN sheet
    Sheets("TWN").Range("B:Z").Clear
        
If NOW >= BN Then
    If LTH < 16 Then
'如果時間是16:00之前
'抓取資料（TWN sheet）
QueryQuote:
             With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("B1"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
                .RefreshStyle = xlInsertEntireRows
                .Delete
            End With

'讓Excel重新活回來，讓資料能夠顯示
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
'切數據（TWN sheet）
        Sheets("TWN").Columns("B:B").Select
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
        TrailingMinusNumbers:=True

'否則（如果時間是16:00之後）
Else


'抓取資料（TWN sheet）
QueryQuote2:
             With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("B1"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
                .RefreshStyle = xlInsertEntireRows
                .Delete
            End With

'讓Excel重新活回來，讓資料能夠顯示
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
'切數據（TWN sheet）
        Sheets("TWN").Columns("B:B").Select
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
        TrailingMinusNumbers:=True
'更新上一交易日（TWN sheet的BN變數）
        Sheets("TWN").Range("A4:A7").Select
        Selection.Copy
        Sheets("TWN").Range("A9").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

'選擇TWO sheet
Sheets("TWO").Select

'告訴Excel不要每更新一格就重新計算
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
'將現在的工作表設為資料表
        qurl = "http://www.tpex.org.tw/web/stock/aftertrading/otc_quotes_no1430/stk_wn1430_print.php?l=zh-tw&d=" + Sheets("TWO").Range("A9") + "/" + Sheets("TWO").Range("A10") + "/" + Sheets("TWO").Range("A11") + "&se=EW&s=0,asc,0"
    Sheets("TWO").Range("B:Z").Clear
'抓取資料（TWO）
QueryQuote3:
             With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=Sheets("TWO").Range("B1"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
                .RefreshStyle = xlInsertEntireRows
                .Delete
            End With
  
'讓Excel重新活回來，讓資料能夠顯示
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True

'選擇TW sheet
Sheets("TW").Select

'將現在的工作表設為資料表
    Set DataSheet = ActiveSheet
    qurl = "http://www.tse.com.tw/exchangeReport/MI_INDEX?response=csv&date=" + Sheets("TW").Range("A9") + Sheets("TW").Range("A10") + Sheets("TW").Range("A11") + "&type=ALLBUT0999"
    Sheets("TW").Range("B:Z").Clear
'抓取資料（TW sheet）
QueryQuote4:
             With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("B1"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
                .RefreshStyle = xlInsertEntireRows
                .Delete
            End With

'讓Excel重新活回來，讓資料能夠顯示
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
'切數據（TW sheet）
        Sheets("TW").Columns("B:B").Select
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
        TrailingMinusNumbers:=True
        
        End If
End If

'選擇關注 sheet
Sheets("關注").Select

End Sub

紀錄：

office 2010 64bit、2016 64bit可使用
blogger: kslman 於 晚上8:16 14 意見
標籤：

2017年11月7日

Excal：VBA，插入股票公開資料

google試算表跑資料很慢，而且抓不到上櫃和興櫃資料。

而且如果要跟我原本電腦裡的資料連結還要下載下來。

因為有用Excel做紀錄的習慣，所以還是直接匯在Excel的檔案裏面比較方便。


研究了幾天，把之前yahoo那個VBA，改成抓證交所、櫃買中心、政府資料開放平臺的方案，供大家參考：
https://drive.google.com/open?id=1Ekn1MIGolNi3bqoH-xfv16mD9Z_D0OoF

這是舊的，請改看新版：Excal：VBA，插入股票公開資料v1.3

（政府開放平台的興櫃資料我是查到這個：https://data.gov.tw/dataset/11398
因為我不知道怎麼解析櫃買中心的興櫃csv下載連結，有人知道的話可以跟我說下）

使用方式：
1. 「關注」的分頁C列填入股票代碼。
2.  點擊「TW」、「TWO」、「TWN」各分頁左上「refresh」按鈕就可以刷新。

目前問題：
1. 興櫃股票我抓的政府資料開放平臺的資料沒有前一天價格，所以沒有辦法算漲跌、漲跌幅和昨收。（如果有人知道哪邊抓的資料有漲跌或前一天價格可以跟我說一下）
2. P/E只有上市有。
3. 目前我只會每個分頁各自加一個按鈕，但我不知道怎麼樣做可以一個按鈕直接刷新三個分頁，如果有人知道怎麼做可以教我一下，我可以調整一下。
4. 美股不知道哪邊有資料，有人知道那邊有美股類似證交所這樣一個表有全部股價資料的網站嗎？
5. 我不知道要怎麼判斷最近交易日，所以如果假日用會沒資料。
（暫時先找了一個土炮的方法，目前的連結已經先更新）


巨集內容如下。

抓上市股票：

Private Sub CommandButton1_Click()

'宣告變數
    Dim QuerySheet As Worksheet
    Dim DataSheet As Worksheet
    Dim qurl As String
'告訴Excel不要每更新一格就重新計算
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
'將現在的工作表設為資料表
    Set DataSheet = ActiveSheet
    qurl = "http://www.tse.com.tw/exchangeReport/MI_INDEX?response=csv&date=" + Range("A9") + Range("A10") + Range("A11") + "&type=ALLBUT0999"
    Range("B:Z").Clear
'抓取資料
QueryQuote:
             With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("B1"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
                .RefreshStyle = xlInsertEntireRows
                .Delete
            End With

'讓Excel重新活回來，讓資料能夠顯示
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
'切數據
        Columns("B:B").Select
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
        TrailingMinusNumbers:=True
End Sub
抓上櫃股票： 
Private Sub CommandButton1_Click()

'宣告變數
    Dim QuerySheet As Worksheet
    Dim DataSheet As Worksheet
    Dim qurl As String
'告訴Excel不要每更新一格就重新計算
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
'將現在的工作表設為資料表
    Set DataSheet = ActiveSheet
    qurl = "http://www.tpex.org.tw/web/stock/aftertrading/otc_quotes_no1430/stk_wn1430_print.php?l=zh-tw&d=" + Range("A9") + "/" + Range("A10") + "/" + Range("A11") + "&se=EW&s=0,asc,0"
    Range("B:Z").Clear
'抓取資料
QueryQuote:
             With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("B1"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
                .RefreshStyle = xlInsertEntireRows
                .Delete
            End With
  
'讓Excel重新活回來，讓資料能夠顯示
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
End Sub
抓興櫃股票： 
Private Sub CommandButton1_Click()
'宣告變數
    Dim QuerySheet As Worksheet
    Dim DataSheet As Worksheet
    Dim qurl As String
'告訴Excel不要每更新一格就重新計算
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Application.Calculation = xlCalculationManual
'將現在的工作表設為資料表
    Set DataSheet = ActiveSheet
    qurl = "http://www.gretai.org.tw/storage/emgstk/ch/new.csv"
    Range("B:Z").Clear
'抓取資料
QueryQuote:
             With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=DataSheet.Range("B1"))
                .BackgroundQuery = True
                .TablesOnlyFromHTML = False
                .Refresh BackgroundQuery:=False
                .SaveData = True
                .RefreshStyle = xlInsertEntireRows
                .Delete
            End With

'讓Excel重新活回來，讓資料能夠顯示
    Application.Calculation = xlCalculationAutomatic
    Application.DisplayAlerts = True
'切數據
        Columns("B:B").Select
    Selection.TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1)), _
        TrailingMinusNumbers:=True
End Sub



blogger: kslman 於 晚上11:19 1 意見
標籤：

2017年11月2日

Excel：樞紐分析表，資料分組

用樞紐分析表快速把資料分組的方式。

例如把1~5000，5001~10000，10001~15000....每個區間的人數，快速統計出來。

方式如下：
 





blogger: kslman 於 晚上8:54 0 意見
標籤：

2017年8月30日

Excel：VBA，將函數回傳值改為變數 

Sub addlog()
'
' addlog 巨集
' 自動貼即時
'
' 快速鍵: Ctrl+Shift+A
'
    Rows("3:3").Select
    Selection.Copy
    Dim x As Long
    x = Evaluate("=counta(A:A)")
'用Evaluate回傳=counta(A:A)的結果，並將值定義為變數x，在此處為3
    y = x + 2
    Rows(y & ":" & y).Select
'選擇第5行（3+2=5）
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub
研究半天，人生第一個自己寫成功的VBA。
blogger: kslman 於 晚上9:34 0 意見
標籤：
訂閱： 文章 (Atom)