select predict(DREAM,1) from [LIFE] 中文專頁: 2012

2012年12月1日星期六

MOS Excel 2007 Expert 考後感

已經好耐無考過MOS了, 考試中心做宣傳, 唔洗錢考, 就花小小時間玩玩.
老實講,張CERT價值有限, 香港邊有一間公司HR/老細知道MOS係咩?
不過如果係自學,想記熟o的,不妨考考. 當年都係經MOS慢慢學起.
(不過建議都係等超特價先考吧, 考試費可以用黎買一套Office...值得嗎...)

主要考了4科, Excel Expert, Word Expert, Access 和 Powerpoint.同一日進行...
每科大約45分鐘, Access/powerpoint兩科少題目o的,每廿分鐘就KO倒一科.
兩科Expert,用盡45分鐘都做唔哂. 估計係Expert無咩人考, 無人Tune過題目數目...
如果未考過,諗著去考的,記得練熟o的,
記下D style/顏色位置,搵黎搵去哂左好多時間.

其實今次淨係想講下Excel Expert, 如果想知道其他科, 可以留個comment.
簽左NDA,唔可以講太詳細o的.
大約有幾個範圍 (有o的唔記得左, 成日無考100條都係有80條, 記唔得哂...):

錄Macro,改Macro, 搬Control, 寫UDF:
真係由0開始寫一條UDF出黎,不過好短, 3行起,4行止
Advanced filter, Consolidate, Data Table: Data table印象中咁多年未見過佢出
Excel 2007 新/大路Formula: 所有M記考試原則, 新野一定考,爭在考幾多
External Data: Access/XML mapping (好懷疑後者係唔係真係有人用...)
Pivot table/chart:唔只係拉個field出黎,要加o的野咁.
3D reference (即係 [workbook]worksheet!cell),
formula Audit, validation (list/range), 加range name
Protect worksheet, hidden formula

印象中比Excel 2010 Expert無咁tricky. 跟著題目關鍵字眼照做就得.
最tricky果o的係搵format果o的, 如搵"色彩填滿,-橙色,輔色6", 大佬,鬼記得.
不過佢個Check Macro 評分engine應該進步左, 以前(XP/03)明明無寫錯,都扣左分...

最後都係果句, 如果上面個List 已經有7/8清楚點用的, 就無必要考啦

2012年11月4日星期日

IT見工奇遇記

天下之大無奇不有, 真人真事小許改篇(唔記得詳細:P):

Case 1: Hardware Technician, Online Scan 事件
面試官: 用D咩方法掃毒?
應徵者: 首先拔LAN線, 然後Online Scan.

Case 2: Programmer, Database 事件
面試官: 學校Final year project 用些什麼來做Database (DBMS)?
應徵者: VB.NET之類

Case 3: Programmer, 新鮮人事件
面試官: 你CV上面沒有特別寫工作經驗,有沒有做過D咩工 (有無關係都好)?
應徵者: 無
面試官: 咁你o係學校有無做過d咩Software project?
應徵者: 有, 但係唔係好記得.

本篇不日更新

2012年11月2日星期五

sp_helpdb 出現db owner錯誤

最近常常遇到這過情況,特此記錄一下.
用sp_helpdb 時出現了錯誤

Server: Msg 515, Level 16, State 2, Procedure sp_helpdb, Line 53
Cannot insert the value NULL into column 'owner',
table 'tempdb.dbo.#spdbdesc_..._000100443608';
column does not allow nulls. INSERT fails.
The statement has been terminated.

2012年10月31日星期三

Excel 表單設計小技巧4 預設中文輸入

選擇格子時,自動跳出中文輸入法

方法適用於地址,備註等文字欄位.
輸入法不是定死的, 輸入時也可以設回其他輸入法.

做法:

2012年10月29日星期一

Excel 表單設計小技巧 3 表單語言選擇

通過一個選單和一些公式,可以制作一個簡單的界面語言選擇效果

製作方法:

2012年10月27日星期六

Excel 表單設計小技巧1 下拉選單

設定下拉選單

Excel 表單設計小技巧2 限制指定範圍可改

設定好指定範圍可改, 當不小心改動鎖定格子時,系統會彈出提示不能改.

按鍵盤的[TAB], 可以把游標移到沒有鎖定的格子

2012年10月17日星期三

Excel:快速喚醒記憶的Excel II

以下介紹的都是一些又小(又老)的技巧或習慣,
一打開Excel時,什至是在Windows預覽,
就可以知道Excel檔裡有些什麼.
而且多數只花數秒時間,但可惜會實行的人比較少.

1. 把游標放在當眼處

通常標題都是放在頂端的行數,
完成改動後按一下CTRL+HOME,就可以把游標放到左上方.
下次打開時就可以立即看到標題

2012年9月20日星期四

Excel: MINIF/MAXIF 及位置

Excel 自帶只有的SUMIF功能, 但是沒有MINIF或 MAXIF.
但是,用"陣列公式"方法,可以做出同樣效果.

當分類是Z3時,
最大=40 (C11/D11 公式)
最小=35 (C15/D15 公式) 不理空白, C14/D15 為處理空白

公式上{ } 不是直接輸入的, 而是在輸入公式如 =MAX(IF(B2:B9=”Z3”,C2:C9,0)) 後,
按著鍵盤的"CTRL", "SHIFT"然後按ENTER.
這個稱為"陣列公式", 下面會再詳細一點講解.
相同最大值, 最先,第二,最後位置,

解說:

2012年8月5日星期日

Excel 2013: 新功能簡介2 Timeline (Pivot Table)

Timeline 是一個新控件, 給客戶選出1段連續的時間, 用來篩選顯示.
如下圖下方結算日的選項.

在2010時用過Slicer(交叉分析篩選器)的朋友, Timeline可以稱為Slicer的日期版.
如下圖,Slicer用來選擇想看的 "售貨員", Timeline 則可以篩選結算日

2012年7月22日星期日

Excel 2013: 新功能簡介1 Flash Fill

資料 >資料工具> Flash Fill
根據第1個輸入的例子, 從資料欄自動拆到其他欄位.
基本上就是半自動版本的"資料剖析"

例如下圖,有一欄位"姓名"(B欄). 現在想把姓和名分拆出來.
1. 先在 C2 和 D2分別輸入 "陳"和 "小明" (配合B2的"陳小名")

2012年7月18日星期三

Excel: 雙擊Excel檔後,Excel程序打開了,但Excel檔沒有打開

雙擊Excel檔後時,Excel打開,但Excel檔沒有打開(Excel XP,03)
或是出現"傳送命令給程式時發生錯誤"信息(Excel 07,10).
但是,在Excel程序直接打開檔案卻沒問題.

可以試一下以下設定.

Office 2007/2010:
"檔案"->選項->進階->(一般)忽略其他使用動態資料交換(DDE)的應用程式
拿走DDE這個打勾, 確定後存一檔, 重安Excel即可.

Office 2000-03:
工具->選項->一般->忽略其他應用程式
拿走這個打勾, 確定後存一檔, 重安Excel即可.

參考 http://support.microsoft.com/kb/211494/en-us
本篇最新更新 2012-07-18

2012年7月2日星期一

Excel:VBA 連接SQL SERVER拿取數據

以下將會介紹一下如何從SQL SERVER 的用SQL 或 Store procedure(有沒有#temp table) 拿出數據
I. 基本ADO連接
(關於User name/Password 或 Integrated 請看2.連接方法)
1. 到"工具"->"設定引用項目"
找出併打勾"Microsoft ActiveX Data Objects 2.8 Library" (2.7 也可)

Windows XP 已經預帶2.7版本(MDAC 2.7), 如果還是找不到, 可以到這個位置下載
2. 連接方法(connection)
引用好ADO後, 就可以在VBA使用ADO部件
以下是用User Name和password 連接數據庫的方法

Dim Conn As ADODB.Connection
Dim sConnect As String
Dim strSqlInstance As String
Dim strSqlDB As String
Dim strSqlUser As String
Dim strSqlPWD As String
strSqlInstance = "SERVER_NAME\INSTANCE" 'SQL SERVER實例
strSqlDB = "DATABASE NAME" '數據庫名稱
strSqlUser = "SA" 'SQL實例用戶名
strSqlPWD = "PASSWORD" 'SQL實例用戶密碼
sConnect = "PROVIDER=SQLOLEDB;"
sConnect = sConnect & "DATA SOURCE=" & strSqlInstance & ";INITIAL CATALOG=" & strSqlDB & ";"
sConnect = sConnect & " User ID=" & strSqlUser & ";Password=" & strSqlPWD & ";"
Set Conn = New ADODB.Connection
Conn.ConnectionString = sConnect
Conn.Open
…
Conn.Close

如果客戶機有設Active directory等, 可以用

sConnect = sConnect & " INTEGRATED SECURITY=sspi;"

來取代

sConnect = sConnect & " User ID=" & strSqlUser & ";Password=" & strSqlPWD & ";"

II. 拿出資料表(table)數據 (SQL SELECT)
把數據表 Information_Schema.tables 的數據
用sql “Select * From ..." 抽出放入工作頁 "DATA”

Sub subGetTableValues()
 
    Dim Conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim intColCounter As Integer
    Dim sConnect As String
 

    Dim strSqlInstance As String
    Dim strSqlDB As String
    Dim strSql As String
 

    strSqlInstance = "SERVER_INSTANCE"
    strSqlDB = "database"

    sConnect = "PROVIDER=SQLOLEDB;"
    sConnect = sConnect & "DATA SOURCE=" & strSqlInstance & ";INITIAL CATALOG=" & strSqlDB & ";"
    sConnect = sConnect & " INTEGRATED SECURITY=sspi;"
   
    'Establish connection
    Set Conn = New ADODB.Connection
    strSql = "SELECT * FROM Information_Schema.tables" 'SQL在這裡定義,可加上 where group by... 
    With Conn
        .ConnectionString = sConnect
        .CursorLocation = adUseClient
        .Open
        .CommandTimeout = 0
        Set rs = .Execute(strSql)
    End With

    Worksheets("Data").Cells.Clear
    If rs.RecordCount > 0 Then '如果有數據
        '寫出欄名
        For intColCounter = 0 To rs.Fields.Count - 1
           Worksheets("Data").Range("A1").Offset(0, intColCounter) = rs.Fields(intColCounter).Name
        Next
        '寫出內容 
       Worksheets("Data").Range("A2").CopyFromRecordset rs
    Else
        MsgBox ("找不到數據")
    End If
 

    rs.Close
    Conn.Close
    Set rs = Nothing
    Set Conn = Nothing
 
End Sub

另一個方法可代替CopyFromRecordset,一格一格寫上.

Application.ScreenUpdating = False '先關掉顯示,寫好再補上,可以省"點"時間
Dim intRow as long, intCol as long
intRow = 0
rs.MoveFirst
Do While rs.EOF = False
    For intCol = 0 To rs.Fields.Count - 1
        Range("A2").Offset(intRow, intCol).Value = rs.Fields(intCol).Value
    Next intCol
    rs.MoveNext
    intRow = intRow + 1
Loop
Application.ScreenUpdating = True

III. 拿出儲存程序(stored procedure, sp)結果集
假設要呼叫的SP. SP有3組參數:
1. 字串輸入: astring_input
2. 數字輸入: aint_input
3. 可變數字輸入: aint_output
最後SP會把 3組參數用1個結果集回傳出來, 和把aint_output(3)設定為999
例如, exec usp_test_vba 'abc',10,100 會回傳以下的表(第13-16行)

string input	integer input	integer output
abc	10	999

另一方面, SP的RETURN會回傳777的值 (第18行)

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO
CREATE PROCEDURE usp_test_vba
@astring_input varchar(50),
@aint_input integer,
@aint_output integer output
AS

set @aint_output = 999

select 
@astring_input as [string input], 
@aint_input as [integer input], 
@aint_output as [integer output]

RETURN 777
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

呼叫方法:

Dim Conn As ADODB.Connection
Dim ADODBCmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim sConnect As String
Dim intArgOutput As Integer
Dim intReturn As Integer
intArgOutput = 30
spName = "usp_test_vba"
Dim strSqlInstance As String
Dim strSqlDB As String
strSqlInstance = "SQL SERVER INSTANCE"
strSqlDB = "DATABASE NAME"

sConnect = "PROVIDER=SQLOLEDB;"
sConnect = sConnect & "DATA SOURCE=" & strSqlInstance & ";INITIAL CATALOG=" & strSqlDB & ";"
sConnect = sConnect & " INTEGRATED SECURITY=sspi;"
Set Conn = New ADODB.Connection
Conn.ConnectionString = sConnect
Conn.Open
 
Set ADODBCmd = New ADODB.Command
Conn.CommandTimeout = 0
With ADODBCmd
    .ActiveConnection = Conn
    .CommandText = spName
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters(1).Value = "abc" '放入第1個參數
    .Parameters(2).Value = 5 '放入第2個參數
    .Parameters(3).Value = intArgOutput '放入第3個參數
    Set rs = .Execute()
    intArgOutput = .Parameters(3).Value '回傳第3個參數,經SP處理後數值, 即999
    intReturn = .Parameters(0).Value '回傳SP RETURN出來的數值 即777
End With
Worksheets("Data").Cells.Clear
If Not (rs.EOF) Then
    For intColCounter = 0 To rs.Fields.Count - 1
        Worksheets("Data").Range("A1").Offset(0, intColCounter) = rs.Fields(intColCounter).Name
    Next
 
   Worksheets("Data").Range("A2").CopyFromRecordset rs
Else
    MsgBox ("沒有回傳表")
End If

rs.Close
Set rs = Nothing
Conn.Close
Set Conn = Nothing

IV. 拿出儲存程序(SP)結果集, SP上有臨時表 (#TEMP TABLE)的問題
假設要呼叫的SP,中間有使用臨時表.
用上III方法,運行時可能會出現這樣的錯誤:
"當物件關閉時, 不允許操作"

但是直接在QA/SSMS運行是沒有問題的.
這個問題的解決方法 ,可以在SP上要加上這一行

SET NOCOUNT ON

詳情可以參考這裡. (實際上我也沒有看詳情, 為了找出這個BUG,已經花了半天來Google...)
下一篇會說一下UPDATE/DELETE/INSERT等的
本篇最新更新 2012-07-02

2012年7月1日星期日

Excel:VBA:日期找出某年的第1個星期天日期

本篇原自 twbts問題: 每年的第一個星期六

由於懶的關係, 在google大神搜了一下,發現這個 First Monday Of The Year (YearStart)
測試一下後,發現原是有錯的
YearStart(2014)會出現 30/12/2013...
只好自己弄一個,以下可找出在某一年的第一個的某個星期幾

Public Function YearStartShort(WhichYear As Integer, WhichWD As Integer) As Date
    Dim wd As Integer
    Dim NewYear As Date
    YearStartShort = DateSerial(WhichYear, 1, 1)
    wd = WeekDay(NewYear)
    YearStartShort= DateAdd("d", WhichWD - wd + IIf(WhichWD - wd >= 0, 0, 7), NewYear)
End Function

例如想找 2015年第1個星期五是什麼日期:
YearStartShort(2015, 6)
或
YearStartShort(2015, vbFriday)

WhichYear 為年份
WhichWD 為星期幾:
1 = 星期日 = vbSunday
2 = 星期一 = vbMonday
3 = 星期二 = vbTuesday
4 = 星期三 = vbWednesday
5 = 星期四 = vbThursday
6 = 星期五 = vbFriday
7 = 星期六 = vbSaturday

本編最後更新:2012-07-01

2012年6月30日星期六

Excel:VBA: workbook_open事件小技巧 (打開Excel時跳過workbook_open/不影響改動提示)

I. 打開Excel檔時,跳過workbook_open執行
打開Excel時,不想執行workbook_open,但想繼續打開巨集來看.
可以在打開時, 按著鍵盤的"SHIFT"鍵即可.

II. workbook_open自動改動內容後,沒有用戶改動下,關閉時不提示"是否儲存?"方法
在workbook_open最後位置放上 ThisWorkbook.saved=True
無論之前在workbook做任何改動,workbook還是在"已儲存"狀態.

Private Sub Workbook_Open()
    ....
    ThisWorkbook.Saved = True
End Sub

但要注意workbook實際上是沒有儲存的. 要儲存的話,呼叫一下ThisWorkbook.save即可.

本編最新更新 2012-06-30

2012年5月20日星期日

Excel:快速喚醒記憶的Excel I(定義名稱篇)

有沒有試過,當打開一星期前的Excel檔來看時,不知從那裡看起?
又或是,明明是自己加上的公式,看過半天也不知公式是在攪什麼飛機.
以下將會介紹一些方法,可以在Excel裡留些記號. 其他人也可以容易看懂

A 定義名稱A I. 個別定義名稱
公作上很多時候會收到客戶寄來的xls.

看了一眼,除WTF之外,根本想不出什麼來.
(而且,實際上收到的公式不比這個簡單)
其實只要用上定義名稱,公式就可以變得清晰
如下面公式.

2012年5月8日星期二

Excel 2010: 列印預覽,顯示及拉動邊界

在列印預覽時想一併看看邊界和拉動,
可以先按下右手面下角這個位置(紅箭) "顯示邊界"按鈕
按下"顯示邊界"按鈕後,可以拉扯預覽上的虛線 (藍箭) 來調整邊界.

Office 2010 其中最大的一個改動,是把預覽功能放到了"檔案"菜單上.
但是檔案菜單可用範圍比較少,只好把部份放到不顯眼的位置.
"顯示邊界"相信就是其中一個.

本編最新更新:2012-05-08

2012年5月6日星期日

睡眠學短篇1: 夢與記憶

夢的作用,其實是大腦把你昨天遇過的事,同以往記憶做對比整理.
換句話說,睡覺時會做"無意識"的整理學習.
所以,夜晚讀書讀什麼也進不了腦袋? 早些睡吧.
睡也是學習的重要過程.

2012年5月3日星期四

Excel VBA:運用Excel內建的對話框

Excel VBA 內,有一個功能可以呼叫出EXCEL內部的對話框.

常用的有列印,尋找,格式,縮放,....

呼叫方法就是:

Application.Dialogs(對話框代號).show

例如下圖的列印對話框.

Application.Dialogs(xlDialogPrint).show

MSSQL >2000: 列出數據庫及日誌檔位置

頭6句是用來分辨2000還是較新版本.

DECLARE
 @li_sql_version int
 set @li_sql_version = convert(int,left(convert(varchar(100), SERVERPROPERTY('productversion')), 
  charindex('.', convert(varchar(100), SERVERPROPERTY('productversion')),1)-1))

if @li_sql_version >8
 --SQL >2005
 exec('
 SELECT db.name as db_name, mf.name as logical_name, mf.physical_name AS current_file_location 
 FROM  
 sys.master_files mf inner join sys.databases db on mf.database_id = db.database_id
 ')
else
 exec('
 --SQL 2000
 select db.name as db_name, mf.name as logical_name, mf.filename as current_file_location 
 from 
 master.dbo.sysaltfiles mf inner join master.dbo.sysdatabases db
 on mf.dbid = db.dbid
 ')

SQL Displaying data/log files location of all databases