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

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

做法:

Excel: 動態超連結

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

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

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

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

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

做法:

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

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

製作方法:

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

設定下拉選單

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

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

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

Excel:快速喚醒記憶的Excel II

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

1. 把游標放在當眼處

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

 

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.
這個稱為"陣列公式", 下面會再詳細一點講解.
相同最大值, 最先,第二,最後位置,

解說:

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

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

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

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

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

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

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

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

Excel:快速橫向輸入小技巧

數據表通常是 1行+數欄為1組 (簡體版是1列+數行)
輸入的時候,通常會是先輸入1組(藍箭),然後換行輸入第2組(紅箭).
換行時,往往要按幾下方向鍵,或是換手用老鼠點過去.
有那些方法可以更省功夫?以下為大家介紹一下.

除了換行,很多時候數據會是重覆的.
不想用滑鼠點來點去做剪貼,
有些什麼方法可以自定下拉選單,鍵盤拉出來?以下也會為大家介紹一下.

A.換欄(藍箭):TAB鍵

如果在A2輸入"1"然後按ENTER, 選上的會變成A3,而不是B2.
要像藍矢這樣跳到右手面的話,可以按一下鍵盤的TAB鍵

B.換行(紅箭):自動表格/保護

如果想像紅箭這樣一鍵跳到下一行的第1格,可以用以下2種方法

Excel:儲存格格式,數值 括號格式 不見了

如果在儲存格格式找不到 括號 的表示方式

可以到windows的控制台 > "地區及語言選項”
在"地區及語言選項”裡,地區選項頁,選上 “自訂”
設定一下負數貨幣值格式為有括號的. 然後再重新啟動Excel即可

參考網址:http://www.excelbanter.com/showthread.php?t=182274

本編最後更新:2012-02-21

Excel小書評

 

這裡會放些小書評,大家可以參考參考或提意見.
以外文書為主.本地的實在太少.
本篇會不斷更新,看看我有多少時間看...
值得一看度:為必看為有興趣又有時間可看看
>2003: 有多個EXCEL版本2003,2007,2010

級別		書名
用戶:基礎/進階 Excel >2003	英	Microsoft Press Microsoft Excel 2010 Data Analysis and Business Modeling Wayne L. Winston	有大量操作實作例子,如快捷鍵的實用法,不只是如F1式的說明. 數統/商業應用上也有很詳細的介紹.
用戶:基礎 Excel 2007	英	Excel 2007 Miracles Made Easy Bill Jelen	介紹EXCEL07新功能為主
用戶:基礎/進階 Excel 2000,XP,2003	中	晋身EXCEL高手 鄭皓斌 (已絕版,但可在淘寶找到)	作者多年雜誌短篇結晶, Array formula等進階的用法也易看易懂, 中文轉
用戶:基礎 Excel >2007	英	First Look 2010 Microsoft Office-System Katherine Marray	顧名思義,介紹EXCEL07新功能為主.好處是免費和跟OFFICE發賣日更早出版的.
用戶:基礎/進階 Excel >2000	英	New perspectives on Microsoft Office Excel June Jamrich Parsons, Dan Oja, Roy Ageloff, Partrick Carey	小弟的EXCEL啟蒙老師, 所有功能(連Solver/Array formula)/基本公式都有詳細介紹. MOS考試書籍中最好的一本
用戶:VBA Excel >2000	英	VBA For Modelers Albright	這本是主要的對象是對編程沒有太多接觸的朋友. 全書有兩大部份, 第1部份是給學習基本操作. 第2部份是VBA在運籌學上的應用例子.懂VBA的朋友也可以在這裡學學運籌學.

 

最後更新日期 2011-07-17

Excel: 同一時間設定一組儲蓄格格式 - '儲蓄格樣式'

每次一頁一頁一格一格的去設定格式, 是不是很煩呢?
其實EXCEL提供了一個"樣式"的方法, 可以一次過設定同一類型儲蓄格格式.
而且是個工作簿也可一同設定.

如上圖效果,可以一次過設定一組格式.
這樣跟選上所有需要儲蓄格,然後設定格式不就是一樣嗎?
如果只設定一次,其實是沒有分別的.
但是,如果要做多次改動,用樣式的方法, 第2次改動時, 就不用再慢慢選上格子.

Excel:多叢頁面穿透式改動法

這個方法好像都沒有一個正式的名稱.唯有取其義自創一個.

A. 同一時間修改多個工作頁

當用EXCEL弄表格的時候,我們可以把同一類表格收集到同一個工作簿內
如下圖,除少量內容有分別,框架都是一樣的.

但是,當要改動格式的話,是不是要一頁一頁去改呢?

當然不用,只要先把多頁面一起選上,然後在當前頁做改動即可

Excel: 快速跳到任何一個儲存格/快速選上任何連續欄

A.快速跳到任何一個儲存格
1. 按下CTRL+G,版面會出現到的窗口

2. 在 "參考位址" 輸入儲存格位置,如H5000, 然後按ENTER

B.快速選上任何連續欄