EXCEL SUMPRODUCT函數在會計上四捨五入之陷阱(注意事項)

假如有一試算表 數量*單價=複價，複價每一項皆為四捨五入取小數點下二位 這種狀況下，逐一相加結果是否會與使用SUMPRODUCT函數一致? 以下為試驗結果，下公式的方法很重要，否則看到的結果會是錯誤的

以這個例子來說，C、D才是正確答案。
通常ROUND函數我們通常會下在SUMPRODUCT外，如圖上B 但實際上結果是錯誤的

會計上，儘管差異僅在小數點下第二位，還是會被審退，要小心注意使用。

R語言-迴圈爬網被鎖定(連線被拒絕)解決方式

部分網站對於爬蟲會設有每幾秒鐘不得超過幾次之類的設定，如果網站很單純的只是為了防堵被暴力存取，一般就是使用"延遲"指令來解決。

在R語言中
Sys.sleep(x)代表延遲x秒，儘可能模擬為人正常存取的秒數
Sys.sleep(runif(1,3,5)) 以亂數產生3~5秒鐘的值來延遲(太規律有人說不好，容易被篩選鎖定)。

上面這種狀況很單純，只要延遲合理的秒數，挖掘資料就不成問題。

某網站很特別，我覺得我的秒數已調得很人性化還是把我黑掉(總不能抓20次，延遲30分鐘吧)，檢討原因可能是除了基本的 ［每幾秒鐘不得超過幾次］的設定外，還有做額外的限制，例如不能長時間存取。

為了解決連線被拒絕情形，我嘗試在迴圈程式碼裡面試過每10次或20次或更多次抓取時就額外增加延遲幾秒，結果最終還是失敗，也讓我放棄單純的以延遲的方式去爬網了。

變通方式："延遲"+"重新連線"
我的作業環境：win10+中華電信寬頻(非DHCP)

以下直接以程式碼說明：

    # 前面亂數等待時間，還是無法應付網站踢人的邏輯(連線被拒絕)，所以補上
    # try(getURLContent(url1))    if(inherits(webdata, "try-error")){} 邏輯，
    #自動斷網及連網，並重新從斷點的j值再次跑迴圈
    webdata<-try(getURLContent(url1))
    if(inherits(webdata, "try-error")){
      x<-j #將斷點的j值保存起來
      shell("z:/hinet-unconnect.cmd") #shell()：執行DOS指令-網路中斷連線，檔案放在Z槽
      Sys.sleep(runif(1,6,10))
      shell("z:/hinet-connect.cmd") #shell()：執行DOS指令-寬頻重新撥接，檔案放在Z槽
      Sys.sleep(runif(1,6,10))
      next #假如中斷情形發生，下面的code就跳過不執行，帶著x值跳回j迴圈開始處重新跑迴圈
    }

補充說明二個CMD檔-中華電信寬頻(非DHCP)的離線及連線(取得新的 ip)
1.hinet-connect.cmd
   rasdial 你的寬頻連線名稱 你的帳號  你的密碼
2.hinet-unconnect.cmd
   rasdial 你的寬頻連線名稱 /DISCONNECT

當連線被拒絕情形發生時，透過 try捕捉到錯誤訊息然後執行斷網重新連線取得新 ip ，就可以從斷線時的 j 值接續下去爬網，而不會被迫中斷，直到整個迴圈結束。

營造業的施工日誌設計意見徵詢---使用EXCEL或是GOOGLE試算表

小弟目前就職於營造業，現今是使用EXCEL2007，以類似資料庫的方式來填寫日報表，當然也試過使用GOOGLE試算表來輸入數據，其實EXCEL或GOOGLE試算表各有優劣處，例如：GOOGLE試算表為雲端工具，可以在有網路的環境隨時存取(好用的公式也不少，例如QUERY)，但又有單一檔案大小的限制(儲存格數目不可超過200萬個)。

假若工程項目多達7000個(土建+機電)，工期又多達800天，GOOGLE試算表便不可行，除非依年度拆檔，並建立主檔來彙總數據。

使用EXCEL雖無上述問題，但卻無法共同編輯(土建、機電不同人輸入，只能錯開時間)，要提供主管同時查詢必須建立副本(使用VBA可達到此功能)，否則主管先開啟檔案就會遭鎖定，作業人員無法輸入數據。

EXCEL常用到的函數大約只有SUM()、OFFSET()、VLOOKUP()、HLOOKUP()、SUMIFS()、COUNTIFS()、陣列公式(主要是簡化公式數量)，一般的用法參考說明應該大致都可明白，端看如何運用而已，日後再以範例逐一解說吧

依據這個主題，請各位先進提供意見，我再考慮用何種試算表來提供設計分享
(以施工日誌來說，內容如果規劃得宜，連簡報中勞安的安全檢查數據也是可以一併統計的)

提取中文網頁亂碼之終極解決方案--使用自訂函數轉換編碼

前篇［IMPORTHTML提取中文網頁資料之亂碼解決方式］之自訂函數有個缺陷，當過度使用時，會出現 "Script invoked too many times per second for this Google user account" 的錯誤訊息(詳見官方網站)，要經過一段時間等限制解除後，自訂函數方能正常運作。

經參考說明，採用陣列整批轉換編碼大量降低自訂函數的運用頻率可避免上述問題發生，所以針對原先的自訂函數 encode_big5(s) 作部分修正

function encode_big5( s ){
  //使用mathod map屬性時，初次編譯會出現 "TypeError: 無法讀取 undefined 的「map」屬      性"，為正常現象，無需理會
  if (s.map) {            // 測試資料是否為陣列型態，假如是陣列型態就改為陣列輸出
    return s.map(encode_big5);
  } else{
    return Utilities.newBlob("").setDataFromString(s, "ISO-8859-1").getDataAsString("big5") ;
  }
}

若上述轉換編碼無法正確轉換為繁體中文，請比照上述方式修正自訂函數 encode_utf8(s)


部分網站會大量使用空白、換行的字符，這類網站如果直接使用 encode_big5()直接轉換編碼會將這些字符誤認為轉換"文字"的一部分，在轉碼的過程會取到錯誤的字節，造成轉碼後仍然會夾雜部分亂碼，無法完全正確轉換，須善用REGEXREPLACE()、SUBSTITUTE()這類函數先取代這些字符後，再運用 encode_big5(s)來轉換編碼。

IMPORTHTML提取中文網頁資料之亂碼解決方式(部分情形可解決)

近來都把試算表的使用重心放在Spredsheet上(暫時對EXCEL失去興趣了XD)，為了解決提取網頁資料亂碼問題，爬了許多文才找到下列這個自定函數的運用，此方式並無法完全解決所有網頁的問題

以Spredsheet IMPORTHTML()函數提取中文網頁資料，常會看到一堆亂碼，經比對網頁編碼方式，幾乎確定Spredsheet是預設將資料以"西歐語系"來呈現(網頁設計觀念我不是很懂，似乎是網頁以JvaScript或Ajax獲取資料的編碼設定有關)。

解決方式：請開啟  工具/指令碼編輯器  ，自創新的函數(要記得先編譯過，開放授權後續才能使用自定函數)，以下是我用的二個方式，針對不同情況測試

function encode_utf8( s ){

  return Utilities.newBlob("").setDataFromString(s, "ISO-8859-1").getDataAsString("UTF-8") ;

}

function encode_big5( s ){

  return Utilities.newBlob("").setDataFromString(s, "ISO-8859-1").getDataAsString("big5") ;

}

測試一、假如網站資料呈現是繁體中文(BIG5)，此方式轉換100%成功，例如台工銀網站證券
(渣打銀行匯率資料也是以繁體中文編碼，但無須任何轉換及可正常顯示，所以問題與網頁編碼形式似乎無多大關聯)

將要轉換的儲存格套上公式即可  =encode_big5( 儲存格 )

底下是轉換的範例

測試二、假如網站資料呈現是UTF-8(轉換方式如上所述方法，改用encode_utf8( )函數)，例如公開資訊觀測站資料，很不幸的，轉換後仍然還是有部分亂碼

將IMPORTHTML取得的資料與網頁資料核對(網頁以ISO-8859-1編碼)，發現部分內容不一致，才導致轉換無法完全成功，殘念....。

也想過是否誤判提取的資料預設編碼為ISO-8859-1，但更換數種編碼結果仍是失敗，所幸轉換過的內容大致可辨讀。

Spreadsheet提取公開資訊觀測站現金流量表資料

公開資訊觀測站現金流量表並不是正規的TABLE表格，即使使用EXCEL WEB查詢，資料仍會無法正確顯示。
原先想利用IMPORTXML()去提取裡面有用的資訊，不知為何，嘗試多次均失敗，可是部分網站又可以(例如http://www.marketwatch.com/)，有傳聞GOOGLE試算表改為新版後就發生這個問題了。不管如何，山不轉路轉，我使用另外一個方式來提取資料

首先，要善用瀏覽器的插件來分析網頁(小弟我是用 firefox 的 httpfox)，找到所需文件正確位置，本例是提取股票代號6184大豐電100年第四季的現金流量表資料，正確位置是
http://mops.twse.com.tw/mops/web/ajax_t05st36?firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=6184&year=100&season=04

單純使用IMPORTXML()抓到的資料長相如下

(資料亂碼應是編碼問題，小弟仍無法解決)

看似很整齊的資料單純利用SPLIT卻被分割的七零八亂，原因是換行及空白代號的問題，這個資料如果貼到WORD裡頭去觀看(要到選項設定裡打開顯示字元符號及空白)，會看到換行符號為向下的箭頭

這個換行符號的代碼是CHAR(10)，裡頭看不見的空格代碼是CHAR(32)

完整語法請參考

=transpose(split(IMPORTHTML("http://mops.twse.com.tw/mops/web/ajax_t05st36?firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=6184&year=100&season=04","table",5),char(10)))

因為我只是要提取部分資訊，所以我使用QUERY()來查詢資料(中文字雖然是亂碼，但是有對應，所以可以當搜尋KEY。注意，這種亂碼使用Vlookup是無法正確搜尋的)

以本例來說，我要查詢的是"營業活動之淨現金流入(流出)"的數據，所以搜尋KEY對應的亂碼就是 營業活動之淨現金流入(流出)

=QUERY(B1:B300,"select * where B like '%營業活動之淨現金流入(流出)%'")

取出的資料仍然放在同一格，再利用SPLIT分離一次

如果只要100年度的數值，可配合使用 index()

=index(split(QUERY(B1:B300,"select * where B like '%營業活動之淨現金流入(流出)%'"),char(32)),1,2)

99年度數值==index(split(QUERY(B1:B300,"select * where B like '%營業活動之淨現金流入(流出)%'"),char(32)),1,3)

魔鬼藏在細節裡--銀行的境外基金與境外ETF費用比較

假設境外基金及境外ETF的內扣費用我們都可以接受，只在乎銀行收取的佣金多寡，那要注意了，銀行也跟你玩起數字遊戲，透過銀行買境外ETF的費用並沒有比較省(以下所言均是指境外基金或ETF)。

看過綠角寫的投資理財文章，大部分人應該都認為買ETF的費用可以節省很多，但某些銀行所推出的理財產品卻不盡然是這樣，原因何在?

一般來說，銀行基金收取的佣金有<申購手續費+信託保管費(A類基金贖回無手續費)>，而ETF則有<申購交易費+賣出交易費+信託保管費+證交費)

問題出在ETF的信託保管費，是按照天數來計價的，計算的公式是
   信託保管費=賣出的金額 * 0.2% * 持有天數/365

我手上這家銀行的基金手續費是3%，超過3年以上免基金信託管理費(按天數級距設定收取費率，超過3年免收)，而ETF並沒有免收的優惠

經過試算表的推算(假設投資美金1萬元，投資報酬率12%)，基本上持有五年以上，ETF的佣金反而會大過基金

持有200天

持有五年

這還不算甚麼，假如基金3%的手續費，美其名給你優惠打五折，這時你會發現，不管持有時間長短，持有ETF的佣金均會大於基金的佣金

持有100天

持有1826天(五年)

這應該是利用大家喜歡買ETF而設計出來的產品，跟銀行買ETF時請詳細瞭解收費的細節，不然是會吃悶虧的，當然，如果可以自行去跟美國網路券商開戶就可以省掉許多費用了。

祝大家投資順利