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

2013年1月20日星期日

Excel: 動態超連結

以下將會介紹一下如何製作跟隨EXCEL內容自動變動的超連結.
例如下圖, C10的超連結會計算現時最高銷售額,然後找出相應銷售員的頁面.

2013年1月19日星期六

EXCEL/SQL祕技集

A	快速Excel使用技巧		最後更新
	檔案管理	一按打開所有需要的Excel檔及起始頁面>儲存工作環境儲存工作環境
		Excel 2007/2010:固定最近使用活頁簿
	輸入處理	快速跳到任何一個儲存格/快速選上任何連續欄到...
		快速橫向輸入小技巧工作頁內移動快捷鍵,自定清單	12-04-29
		多叢頁面穿透式改動法動態超連結	13-01-20
	表格/報表處理	同一時間設定一組儲蓄格格式 - '儲蓄格樣式'
		快速喚醒記憶的Excel I(定義名稱篇) 定義名稱 /動態定義名稱快速喚醒記憶的Excel II "目錄"/群組/凍結窗格/儲存縮圖/註解/... Excel 表單設計小技巧 5 限制輸入及主動被動提示 Excel 表單設計小技巧 4 預設中文輸入資料驗證 Excel 表單設計小技巧 3 表單語言選擇 Excel 表單設計小技巧 2 限制指定範圍可改 Excel 表單設計小技巧 1 下拉選單資料驗證	12-05-20 12-10-17 12-10-23 12-10-31 12-10-29 12-10-27 12-10-27
B	Excel 公式	MINIF/MAXIF 及位置	12-09-30
C	Excel Barcode運用	如何列印Barcode
D	Excel疑難排解	儲存格格式,數值括號格式不見了雙擊Excel檔後,Excel程序打開了,但Excel檔沒有打開Excel	12-07-18
E	VBA 儲物室	運用Excel內建的對話框	12-05-03
		"編譯VBAProject"用途
		workbook_open事件小技巧 (打開Excel時跳過workbook_open/不影響改動提示)
F	VBA 與 SQL	使用SQL方法快速拿取Excel數據連接SQL SERVER拿取數據	12-06-30 12-07-02
G	Excel 2010-2013相關	Excel 2013: 新功能簡介1 Flash Fill Excel 2013: 新功能簡介2 Timeline (Pivot Table) Excel 2010: 列印預覽,顯示及拉動邊界	12-07-22 12-08-05 12-05-09
H	SQL 工具箱	MSSQL: 列出數據庫及日誌檔位置	12-05-03

待定項目
Powerpivot, EXCEL項目管理

最新更新:2012-10-17

2012年12月1日星期六

已經好耐無考過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

訂閱：文章 (Atom)

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

2013年1月20日星期日

Excel 表單設計小技巧5 限制輸入及主動被動提示

被動提示:輸入錯誤數值時彈出提示訊息盒, 選上格子時提示輸入選項.
主動提示:顯示訊息在附近格子上

Excel: 動態超連結

2013年1月19日星期六

EXCEL/SQL祕技集

2012年12月1日星期六

MOS Excel 2007 Expert 考後感

2012年11月4日星期日

IT見工奇遇記

2012年11月2日星期五

sp_helpdb 出現db owner錯誤

2012年10月31日星期三

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

2012年10月29日星期一

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

2012年10月27日星期六

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

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

2012年10月17日星期三

Excel:快速喚醒記憶的Excel II

2012年9月20日星期四

Excel: MINIF/MAXIF 及位置

2012年8月5日星期日

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

2012年7月22日星期日

Excel 2013: 新功能簡介1 Flash Fill

2012年7月18日星期三

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

2012年7月2日星期一

Excel:VBA 連接SQL SERVER拿取數據

2012年7月1日星期日

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

2012年6月30日星期六

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

2012年5月20日星期日

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

2012年5月8日星期二

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

2013年1月20日星期日

被動提示:輸入錯誤數值時彈出提示訊息盒, 選上格子時提示輸入選項.主動提示:顯示訊息在附近格子上

2013年1月19日星期六

2012年12月1日星期六

2012年11月4日星期日

2012年11月2日星期五

2012年10月31日星期三

2012年10月29日星期一

2012年10月27日星期六

2012年10月17日星期三

2012年9月20日星期四

2012年8月5日星期日

2012年7月22日星期日

2012年7月18日星期三

2012年7月2日星期一

2012年7月1日星期日

2012年6月30日星期六

2012年5月20日星期日

2012年5月8日星期二

被動提示:輸入錯誤數值時彈出提示訊息盒, 選上格子時提示輸入選項.
主動提示:顯示訊息在附近格子上