帖子: 16
主題: 4
精華: 0
積分: 20
點名: 0
作業系統: Win7
軟體版本: office2007
閱讀權限: 10
註冊時間: 2014-11-27
最後登錄: 2015-1-3

1^# 跳轉到 » 倒序看帖

字體大小: tT

發表於 2014-11-27 23:00 | 只看該作者

[發問] VBA 搜尋資料列對應的最大值或最小值

各位前輩大家好:
小弟剛開始自學EXCEL VBA 程式一段時間
每次從網路上搜尋問題解答都會搜尋到這個論壇
知道這個論壇的前輩都身懷絕技....
剛好最近有個問題
從網路上及自己買的書上都搜尋不到好的解法
因此就註冊會員來向各位前輩請教
希望往後也能在這個版上和大家一起學習^^

問題如下:
如照片中的例子, 左表為從ACEESS匯入的資料
小弟希望能將資料整理整理成左表的形式
就是將"班級"那欄所對應到所有的"年紀"作最大值及最小值的的搜尋
也就是像ACEESS 中『查詢精靈』的功能

以下兩種方式是小弟想到的, 都可以解決我的問題:
1. 從ACCESS匯入資料時就直接將原始資料用『查詢精靈』先做整理後再匯入EXCEL工作表 (不知道VBA有沒有這種功能可以呼叫查詢精靈 ?)

2. 將原始資料如照片先匯入EXCEL工作表後再做整理

再麻煩各位前輩指教了! 感激不盡~~!

樓主熱帖

owen06

　暱稱: 阿軒

中學生 Rank: 2

帖子: 396
主題: 12
精華: 0
積分: 425
點名: 0
作業系統: XP
軟體版本: OFFICE 2003
閱讀權限: 20
性別: 男
來自: 台中
註冊時間: 2013-7-2
最後登錄: 2022-7-26

2015羊年全勤獎章 2014馬年全勤獎章熱烈參與勳章

2^#

發表於 2014-11-28 10:45 | 只看該作者

本帖最後由 owen06 於 2014-11-28 10:47 編輯

回復 1# junkwei

找尋符合條件的最大值：
在F2輸入=MAX(($A$2:$A$16=E2)*$C$2:$C$16)，此為陣列公式，輸入完成後要按CTRL+SHIFT+ENTER，
再把公式往下拉即可。

找尋符合條件的最小值：
在G2輸入=MIN(IF($A$2:$A$16=E2,$C$2:$C$16,FALSE))，此為陣列公式，輸入完成後要按CTRL+SHIFT+ENTER，
再把公式往下拉即可。

TOP

joey0415

　暱稱: joey0415

中學生 Rank: 2

帖子: 361
主題: 57
精華: 0
積分: 426
點名: 0
作業系統: win7
軟體版本: 2003,2010
閱讀權限: 20
性別: 男
註冊時間: 2010-5-13
最後登錄: 2022-12-8

3^#

發表於 2014-11-28 11:19 | 只看該作者

回復 1# junkwei

sql語法即可完成
stock.mdb=>換成你的資料庫名稱
某某表=>換成你資料庫中的資料表名稱

以上是mdb語法，新資料庫請自行搜尋替換

Sub ex()
Worksheets("1").Activate
Set cn = CreateObject("adodb.connection")
cn.Open ("Driver={Microsoft Access Driver (*.mdb)};dbq=" & ThisWorkbook.Path & "\stock.mdb")
Set rs = cn.Execute("select 班級,max(年紀) as 最大年紀,min(年級) as 最小年紀 from 某某表 group by 班級")
w = 1
For Each tt In rs.Fields '顯示表單名稱
Cells(w) = tt.Name
w = w + 1
Next
Cells(2, 1).CopyFromRecordset (rs)'叫出資料
cn.Close
Set cn = Nothing
Set rs = Nothing
End Sub

複製代碼

TOP

stillfish00

大學生 Rank: 4

帖子: 1018
主題: 15
精華: 0
積分: 1058
點名: 0
作業系統: win7 32bit
軟體版本: Office 2016 64-bit
閱讀權限: 50
性別: 男
來自: 桃園
註冊時間: 2012-5-9
最後登錄: 2022-9-28

4^#

發表於 2014-11-28 13:27 | 只看該作者

回復 1# junkwei
Excel 從選單的資料>取得外部資料(從Access)>選檔案>匯入資料視窗: 檢視方式勾選樞紐分析表，然後拉一下欄位就可以了。

表達不清、題意不明確、沒附檔案格式、沒有討論問題的態度~~~~~~以上愛莫能助。

TOP

junkwei

小學生 Rank: 1

帖子: 16
主題: 4
精華: 0
積分: 20
點名: 0
作業系統: Win7
軟體版本: office2007
閱讀權限: 10
註冊時間: 2014-11-27
最後登錄: 2015-1-3

5^#

發表於 2014-12-3 22:45 | 只看該作者

謝謝 owen06 及 stillfish00前輩的指導，不過小弟所希望的方式是可以由VBA程式自動匯入的, 但還是謝謝你們撥空指導, 讓我學到EXCEL不同的分析方法 ~ ^^

TOP

junkwei

小學生 Rank: 1

帖子: 16
主題: 4
精華: 0
積分: 20
點名: 0
作業系統: Win7
軟體版本: office2007
閱讀權限: 10
註冊時間: 2014-11-27
最後登錄: 2015-1-3

6^#

發表於 2014-12-3 23:11 | 只看該作者

回復 3# joey0415

謝謝前輩的指導~
由於小弟VBA資歷尚淺
是否可以請前輩撥空稍加說明用法及語法呢
以便小弟方便應用及查詢相關的資訊呢??

其中有幾行小弟不太了解的地方如下:
1.
Set rs = cn.Execute("select 班級,max(年紀) as 最大年紀,min(年級) as 最小年紀 from 某某表 group by 班級")
其中的『最大年紀』及『最小年紀』是要自己先輸入的嗎?
P.S.小弟的需求是希望G2:I4的儲存格都是VBA自動分析產生的

2.
For Each tt In rs.Fields '顯示表單名稱
請問tt所代表的是工作表的名稱嗎?

不好意思~~~
再麻煩前輩撥空指導了
感激不盡~~!!

TOP

GBKEE

超級版主 Rank: 8 Rank: 8

帖子: 5923
主題: 13
精華: 1
積分: 5986
點名: 0
作業系統: win10
軟體版本: Office 2010
閱讀權限: 150
性別: 男
來自: 台灣基隆
註冊時間: 2010-5-1
最後登錄: 2022-1-23

2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章 2011兔年全勤獎章

7^#

發表於 2014-12-4 07:01 | 只看該作者

回復 5# junkwei

Option Explicit
Sub Ex()
Dim Rng As Range, i As Integer, Ar()
Set Rng = Range("iv1")
'進階篩選出"班級"欄不重複的值
Range("a:a").AdvancedFilter xlFilterCopy, , Rng, True
ReDim Ar(1 To 3, 1 To 1) '陣列重置
Ar(1, 1) = "班級"
Ar(2, 1) = "最大年紀"
Ar(3, 1) = "最小年紀"
i = 2
Do While Rng.Cells(i) <> "" '"班級"欄不重複的值(各班級)
With Range("a:a")
.Replace Rng.Cells(i), "=EX", xlWhole '各班級換置為錯誤值
With .SpecialCells(xlCellTypeFormulas, xlErrors)
ReDim Preserve Ar(1 To 3, 1 To i)
Ar(1, i) = Rng.Cells(i)
Ar(2, i) = Application.Max(.Cells.Offset(, 2))
Ar(3, i) = Application.Min(.Cells.Offset(, 2))
.Value = Rng.Cells(i) '錯誤值換置回為各班級
End With
i = i + 1
End With
Loop
Rng.EntireColumn.Clear '清除:進階篩選出"班級"欄不重複的值
Range("g1").Resize(UBound(Ar, 2), UBound(Ar, 1)) = Application.WorksheetFunction.Transpose(Ar)
'UBound(Ar, 2)陣列第2維的元素上限值
'UBound(Ar, 1)陣列第1維的元素上限值
End Sub

複製代碼

感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限, 一天1元的贊助,人人有能力.

TOP

junkwei

小學生 Rank: 1

帖子: 16
主題: 4
精華: 0
積分: 20
點名: 0
作業系統: Win7
軟體版本: office2007
閱讀權限: 10
註冊時間: 2014-11-27
最後登錄: 2015-1-3

8^#

發表於 2014-12-11 21:15 | 只看該作者

回復 junkwei
GBKEE 發表於 2014-12-4 07:01

真的很感謝GBKEE前輩的指導!!
這個code正是我要的功能!
vba果然功能強大...也看到好多沒看過的語法...0rz
但小弟研究前輩的code還是有些地方無法了解...上網查詢似乎也還是不太懂...
希望前輩可以不吝指導..
1. Set Rng = Range("iv1")
請問這樣定義的range是怎麼樣的range呢?? iv1 不是表示舊版excel的第一欄的最後一格嗎?

2. Range("a:a").AdvancedFilter xlFilterCopy, , Rng, True
請問a:a是怎麼樣的範圍呢??這行code的邏輯我想半天還是想不通...

3.請問為什麼code中要把Ar()矩陣先以轉置後的位置輸入篩選，再以轉置後的位置輸出呢???

不好意思如果有敘述不好的地方請多見諒
再麻煩前輩指導了~謝謝~!!

TOP

GBKEE

超級版主 Rank: 8 Rank: 8

帖子: 5923
主題: 13
精華: 1
積分: 5986
點名: 0
作業系統: win10
軟體版本: Office 2010
閱讀權限: 150
性別: 男
來自: 台灣基隆
註冊時間: 2010-5-1
最後登錄: 2022-1-23

2017 雞年全勤獎章 2016猴年全勤獎章 2015羊年全勤獎章 2014馬年全勤獎章 2013蛇年全勤獎章 2012龍年全勤獎章論壇貢獻勳章熱烈參與勳章 2011兔年全勤獎章

9^#

發表於 2014-12-12 07:13 | 只看該作者

回復 8# junkwei

Range("a:a").AdvancedFilter xlFilterCopy, , Rng, True
"a:a" -> A欄整欄
AdvancedFilter 方法 expression.AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)
CriteriaRange 沒有指定範圍,為不設下篩選條件,
Ar([相對為工作表的欄數], [相對為工作表的列數]) ,與工作表的Cells(列,欄)顛倒
ReDim Preserve Ar(1 To 3, 1 To i)
選擇性引數。當改變原有陣列最後一維的大小時，仍然保有原來的資料的關鍵字。

感恩的心......(在麻辣家族討論區.用心學習會有進步的)
但資源無限,後援有限, 一天1元的贊助,人人有能力.

TOP

junkwei

小學生 Rank: 1

帖子: 16
主題: 4
精華: 0
積分: 20
點名: 0
作業系統: Win7
軟體版本: office2007
閱讀權限: 10
註冊時間: 2014-11-27
最後登錄: 2015-1-3

10^#

發表於 2014-12-14 20:51 | 只看該作者

回復 junkwei

Range("a:a").AdvancedFilter xlFilterCopy, , Rng, True
"a:a" -> A欄整欄
AdvancedF ...
GBKEE 發表於 2014-12-12 07:13

原來如此~~真的謝謝GBKEE版主的指導!!

關於您的code小弟尚有幾個問題想向您再確認一下:
1. 請問一開始的 Set Rng = Range("iv1") 是不是隨便指定一個range就可以了呢??
因為不是後面就被AdvancedFilter的結果取代掉了??

2.
         .Replace Rng.Cells(i), "=EX", xlWhole '各班級換置為錯誤值
         With .SpecialCells(xlCellTypeFormulas, xlErrors)
            .Value = Rng.Cells(i)             '錯誤值換置回為各班級
         End With
   這幾行的目的似乎是將各班級的年紀值挑出來後, 再對其取MAX跟MIN
   有爬到版主之前的文章對SpecialCells也都有類似的用法..
   但看了半天跟上網搜尋還是無法完全掌握正確的用法...
   想請問的第二個參數的 xlErrors是如何判定為錯誤的呢?  為什麼取代為"=EX"  就是錯誤值呢?
   xlCellTypeFormulas不是表示"使用公式的儲存格"嗎??

以上兩個問題再麻煩您撥空解惑了!!  謝謝~~~!

TOP