Powerpivot系列3:基礎篇-資料手工"貼上"Powerpivot

除了從檔案匯入資料, powerpivot還提供了一個方法,

直接從表格"抄上" 然後 "貼上"Powerpivot.
這個可以方便從網頁上取資料 ( 如匯率表,交通期表)

I. 貼上新數據表

1. 首先把表格複製

Powerpivot系列2:基礎篇-Excel資料放入Powerpivot和更新

I. 把Excel上數據表放進Powerpivot
1.首先把數據表化成"資料表"
常用->格式->格式化為表格->隨意選款格式

VirtualBox 增大Windows server 2008(64bit) VMDK盤

最近嘗試安裝sharepoint 2010, 原先在virtual box隨意設了20GB 的vmdk盤
後來發現只是 WIN2008+sql08已花光了18GB, 還得5GB才可以安裝WIN2008 SP2...
不知起初是否設錯隃盤的自動增大選項,VirtualBox的自動增大好像沒有反應.
只好找個方法手工增大一下. (好像是vdi才可自動增大, 因為要匯出備份,所以轉了為vmdk...)

使用了這裡提供的METHOD 2

1. 增大盤.在主機進行(非虛擬那部)
先另外install VMWARE server,借用指令工具vmware-vdiskmanager
vmware-vdiskmanager -x 50GB “Windows Server 2008.vmdk”
留意50GB 是總大小

也留意一下某些網站放了錯的指令: 50GB前是沒有減號"-"的 (以下是錯的例子)

vmware-vdiskmanager -x -50GB “Windows Server 2008.vmdk”

2. 把C盤的分割增大.進入虛擬主機
因為是64bit的win2008, 所以用了Easeus的Server版
如果只是32bit,可以用home版,免費的
想64bit而又免費的話, 可以試試跟隨method2介紹的knopper.

如果是用Easues的步驟,因為手動步驟不需要一分鐘,所以不多放圖了:
安裝->運行Easesus->版面上右一下盤->按resize->設一下新大小->按ok->關掉版面->
Easeus會提示重啟->按重啟->等收成->完成...

Powerpivot系列1:基礎篇-Powerpivot有什麼作用?

簡介

Powerpivot可以說是Excel 2010中主要新增功能之一.
在跟機版excel方面, Powerpivot配合樞紐分析表,
可以用來對大量的數據進行"隨心所欲"的組合分析.
數據可以是由不同來源/不同定義的表中抽出集合.

由非正式的technical preview到現在才面世一年多,
已經有不小的專門網站在討論Powerpivot功能.

(技術註釋: 與其說Powerpivot是新功能,
不如說是一個EXCEL的增益集, 類似規劃求解.
Powerpivot 的全名是"Microsoft SQL Server PowerPivot for Excel 2010)

以下將會詳細介紹一下Powerpivot對於普通樞紐有何分別

Powerpivot功能介紹
對傳統Excel方法作比較, Powerpivot的功能有以下幾方面.

I.資料壓縮技術 VertiPaq
經Powerpivot來處理大量數據(幾萬~幾百萬行),可以節省很多儲存空間.
也能減輕內存消耗.

Powerpivot可以把大量數據下載到Excel上, 在脫離原數據庫/文檔情況下,
已生成的樞紐表還可以繼續用最後更新的資料運作.
這個動作, 用傳統數據匯入的方法也可以做到.
但是如何行數列數多,檔案會相當的大, 用email等傳送,會比較煩.

以下為同一組資料表(約三萬多行),可見用上powerpivot的檔比傳統方法用更少空間.

II.一個樞紐表對應數個資料表
只要設定好資料表之間關係,就能把數個表的欄位,放到同一個樞紐表.

如果傳統樞紐表的方法,也可以先把表連接成同一個表 (如用vlookup等),再生成樞紐表.
powerpivot,相對來說,可以節省一個vlookup幫助欄,更新數據時,也不用手動/vba補上幫助欄.

III.製作"不規則"樞紐表 (集合)
除了可以選擇欄位,Powerpivot還可以用上集合來選擇某一個項目的某一個欄位不顯示
例如以下的樞紐報表,2010的資料只顯示平均值,總和數不顯示,但2012的要繼續顯示.
上面的圖顯示設定集合前的樞紐表, 下方的則是加上集合後的

 
(技術註釋:Powerpivot實際上是一個小型的OLAP服務器,較大型的有SQL Server Analysis Service SSAS.集合是Excel 2010樞紐表對應OLAP服務器的一項新功能. Excel2007只能對應在SSAS上已設定的集合. 以下是Powerpivot樞紐表的連線定義)

除上述主要的3點, Powerpivot還有一些用DAX,交叉分析器互動等等,在下一些篇章將會再詳細講解.

使用Powerpivot上暫時常見的問題
(本部份待日後補充)

系統要求和安裝方面

• 硬件要求:1GB RAM或以上(2GB較好) 個人建議也在2GB或以上.
• 軟體要求:EXCEL 2010
  如果是非WINDOWS 7: 要先安裝.NET FRAMEWORK 3.5
• 安裝過程Powerpivot不是跟Office 2010一起安裝的.要在另一位置下載和自行安裝.
  安裝過程只是選幾次"繼續","繼續"和"繼續", 所以不在這裡詳細寫

下一篇,將會介紹一下,如何從Excel數據表製作簡單的Powerpivot報表.

(最後更新:2010-SEP-19)

[啟示一則]ExcelHelp論壇->麻辣論壇

網友Carson的的excelhelp論壇已經結業了.

有興趣找小弟"聊天"的朋友
可以到麻辣論壇
http://forum.twbts.com/

Excel 2010: 樞紐分析表 新功能小介紹 (非olap篇)

以下是一些Excel 2010上 有關樞紐分析表的新功能 簡介
(由2000-2003 跳上2010的朋友, 可以到這裡參考一下2007的新功能)

I. 欄位新功能: 重複項目標籤

Excel VBA: "編譯VBAProject"用途 (Excel 1997-2010通用)

背景

某些VBAProject未經編譯時,程序仍然可以執行.

但是運行時後,出現不明的錯誤(當然是非邏輯性錯誤).

例如

"DLL 呼叫規格錯誤 (錯誤 49) sub no public"

編譯VBAProject的實際昨用

制作或更改VBA的時候,在出"偵錯"菜單出現的"編譯VBAProject",究竟有何作用?

1.匯入代碼問題

編譯VBAProject是一個經常被新手遺忘的功能. 因為大部份時間, VBA都會自動編譯.
但是,當代碼是直接匯入的時候(如mod/txt), 通常情況下"不會"自動編譯的,
如果子程序/自定函數等有重覆或缺失,
開始執行時不會出現任何提示, 當運行到子程序的位置. 就會出現錯誤信息.
所以通常在直接匯入後,應手工按一下編譯來除錯.


再者,如果匯入的是"寫死"的變數, 沒經編譯,"寫死"的變數還可能是舊的值.
 
2.快速手工除錯
當vba project存檔及關閉時, 就算代碼有錯誤,系統是"不會"提示的.
多按自動編譯(如每輸幾段入碼按一次),可以立即看到有沒有代碼出錯,
比起肉眼看代碼,偵錯來得更方便更快速.

例如,當Option Explicit開動的時候, 按一下"編譯",
就可知道有沒有變量是忘了定義的.

參考

如果想進更深層理解VBA編譯是什麼,可參考Chip Pearson的解答: ExCodes/OpCodes

How To:Excel 動態圖片顯示

簡介
以下將會介紹一下, 圖片自動跟隨選頂變更. 而且不經VBA.
如下圖, 在E5選定名稱後, 對應圖片自動在右方F5出現
(範例檔案連結, 範例檔案連結2)

How to:Excel VBA 使用SQL方法快速拿取數據 (設定篇)

以下將會簡單介紹一下為何要用SQL來拿取EXCEL數據,和如何做

用SQL而不用EXCEL上方法的條件

那些時候需要用SQL (ODBC/ADO) 方法?

S1. 利用SQL方法,可以簡單地選擇數據, 如用WHERE,GROUP BY,ORDER BY...等SQL的DML方法.節省開發時間
S2. 如要通過多頁連接數據, 用SQL的JOIN比寫VBA更為直接

S3. 進行較大型工作,如插入多行數據,所需運行時間比用上VBA為少

(對SQL沒太大認識的朋友:簡單來說,SQL在這裡的用處,是可以在同一時間,
對某多個表來做統計,或用欄位作條件,抽出所需要的列.)

那些時候要用EXCEL VBA?

E1. 原數據不是完全的表(TABLE)型式
E2. 需要對數據表進行格式設定(如轉顏色或conditional formating等)
E3. 程序和數據表,在同一EXCEL檔下.

當然,兩個方法可以渾合使用,如E3,用SQL方法的話,可以先把數據分到另一個檔(如條件許可)

VBA SQL拿取數據範例
以下將會一步一步介紹如何抽取數據

下載範例檔案

下載範例數據檔案

範例一:把數據用表列些抽出

1.開啟新Excel活頁簿

2.啟動巨集/宏編輯 (參考如何編輯巨集)

3.Data Access Library

3A)加入ADO兩個library.
如果library不存在,可在這裡下載MDAC新版 (目前是2.8版本)

3B)OFFICE 2007 Data Connectivity Components

如果沒有安裝Office2007, 建議下載2007 Office System Driver: Data Connectivity Components
這個驅動可連接EXCEL2007,EXCEL2003或之前版本.

3C)在VBA編輯環境, 進入"工具"->"參考"
打勾ADO兩個LIBRARY

4.跟隨第二步的方法,加上以下代碼然後運行

sub subXlsSqlQuery()

'定義部份
Dim cn As ADODB.Connection 'Excel連接
Dim rs As ADODB.Recordset '得來的結果,會放到立這個rs的地方.
Dim strDataSrcXlsPath As String '數據庫EXCEL位置
Dim strQuery As String '用作拿數據的SQL
Dim strStartlocation as String '定義得來數據,放到那一個位置.


'I) 以下建立數據庫連接
strDataSrcXlsPath = "D:\Connection_data_many2.xlsm" '定立數據庫EXCEL位置

Set cn = New ADODB.Connection 
With cn
'確定連接所用的Library
.Provider = "Microsoft.ACE.OLEDB.12.0" 

'把連接指向指定Excel
'xtended properties為excel的版本, 通常為12 或 8, 
'12為2007Excel檔(如XLSX,XLSM). 8可相容1997-2003等excel(XLS)
.ConnectionString = "Data Source=" & strDataSrcXlsPath & _
";Extended Properties=Excel 12.0;"

'開始連接,這個時候ACE會打開excel
.Open

End With

'II) 以下運行SQL和回傳數據
'拿出一行資料,所用SQL架構大約為
'SELECT ... FROM ... WHERE
'
'SELECT 上的 * 用作指明拿出所有表上的欄位
'TOP 50 是指抽出頭50列數據
'
'FROM 從DATASHEET工作表抽出
strQuery = "SELECT top 50 * FROM [DataSheet$]" 
Set rs = cn.Execute(strQuery) '執行SQL

'III) 把數據抄至所需的位置
strStartlocation ="A1"

'III-1 把欄位名稱一個一地拿出
'rs.Fields.Count : 欄位數數
'rs.Fields(n).Name : 欄名
Dim lngColCounter as long
For lngColCounter = 0 To rs.Fields.Count - 1 
range(strStartlocation).offset(0,lngColCounter) = rs.Fields(lngColCounter).Name 
Next lngColCounter
'III-2 把數據庫資料列表拿出
ActiveSheet.Range(strStartlocation).offset(1,0).CopyFromRecordset rs

'IV) 關掉/清理連接
cn.Close
Set cn = Nothing
Set rs = Nothing

end sub

5.按執行(F5),效果將如下圖

範例二:抽出一行數據及自動填表

首先設定好範例一上1/2/3三個環境

4.在Excel上加入一新工作表,名為 Query
依如下格式和位置,配置工作表
D3為用戶輸入交易代號條件

5.加入以下新模組和代碼
由於大部份代碼跟範例的類似, 所以只介紹不同的部份

Sub subRetrieveByField()

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
strDataSrcXlsPath = "D:\Connection_data_many2.xlsm"

Dim strDataSrcXlsPath As String

'I) 以下建立數據庫連接
Set cn = New ADODB.Connection
With cn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & strDataSrcXlsPath & _
";Extended Properties=Excel 12.0;"
.Open

cn.CursorLocation = adUseClient

End With

'II) 以下運行SQL

'II-1) 準備SQL
Dim strQuery As String
Dim strQueryWorksheet As String
strQueryWorksheet = "Query" '數據查詢頁面位置

'拿出一行資料,所用SQL架構大約為
'SELECT ... FROM ... WHERE
'
'SELECT 用作指明拿出的欄位
'留意 (Order_Qty * Product_Price) as TotalAmt 是直接用SQL算出的欄位.
'TOP 1 為指定只拿一行數據
'也留意一下欄位大小寫,要跟EXCEL數據庫上的相同.
'
'FROM 從DATASHEET工作表抽出
'
'WHERE 抽出的行數條件,這裡用了 交易代號 Transaction_ID
strQuery = "SELECT top 1 " & _
" Transaction_Date, " & _
" Order_Qty, " & _
" Product_Name, " & _
" Product_Number, " & _
" Product_Color, " & _
" Product_Size, " & _
" (Order_Qty * Product_Price) as TotalAmt, " & _
" Product_Price " & _
" FROM [DataSheet$] " & _
" WHERE Transaction_ID = " & _
ThisWorkbook.Worksheets(strQueryWorksheet).Range("D3")

'II-2)執行SQL
Set rs = cn.Execute(strQuery)

'III) 把數據抄至所需的位置
Dim strStartlocation As String
Dim lngRowCounter As Long

If rs.RecordCount <= 0 Then 'rs.RecordCount 為有多少列數據,沒的話就顯示MSGBOX提示
MsgBox ("Transaction not found")
Else
With ThisWorkbook.Worksheets(strQueryWorksheet)
'把資料依名放入左方指明的格上
.Range("D6") = rs.Fields("Transaction_Date").Value
.Range("D7") = rs.Fields("Order_Qty").Value
.Range("D8") = rs.Fields("TotalAmt").Value

.Range("D11") = rs.Fields("Product_Number").Value
.Range("D12") = rs.Fields("Product_Name").Value
.Range("D13") = rs.Fields("Product_Color").Value
.Range("D14") = rs.Fields("Product_Size").Value
.Range("D15") = rs.Fields("Product_Price").Value
End With
End If

'IV) 關掉/清理連接
cn.Close
'rs.Close
Set cn = Nothing
Set rs = Nothing

End Sub

6.按運行後效果

How To: Excel 1997-2010 手工建立VB代碼

以下將會介紹一下,如果在EXCEL, 建立一段簡單顯示信息的代碼.

建立巨集代碼方法

A.顯示"開發人員"索引標籤"(只適用於2007/2010)

A-I) EXCEL2007:
1.Office按鈕->選項

2.常用->打勾"在功能區顯示[開發人員]索引標籤"->確定

A-II) EXCEL2010:
1..檔案->選項

2.索引標籤->打勾 [開發人員] 索引標籤->確定

B.開啟Visual Basic編輯器 (ALT+F11)

BI) EXCEL1997-2003:
工具->巨集->Visual Basic編輯器

BII) EXCEL2007:
開發人員->Visual Basic

BIII) EXCEL2010:
開發人員->Visual Basic

C.加入"新模組"及 "MSGBOX對話框"測試
1.在專案右擊正在使用的活頁簿,插入模組

2.在右方新加模組編輯,加入以下3行代碼

Sub test()
MsgBox ("Hello World!!")
End Sub

3.按"執行"->執行Sub或UserForm

4.如果出現"巨集"這個對話框, 可按下執行

5."HELLO WORLD!!"信息會顯示

如果需要儲存,直接在Excel儲存即可