Board logo

標題: [發問] Excel如何以關鍵字選取整列資料並複製到新工作表？ [打印本頁]
作者: 金貝貝    時間: 2013-2-2 14:03     標題: Excel如何以關鍵字選取整列資料並複製到新工作表？

各位前輩好，不好意思，我用Excel製作了一個記帳表活頁簿，如同以下這個連結所示(連結為Google雲端硬碟來源，請安心連結)


我以一個月為單位設立一個工作表，每個工作表內又細分日期、大項、小項、細節、(支出方式)、(收入方式)等等，若無法連結圖片，簡單以下示意圖：

表1
　　A　　Ｂ　　Ｃ　　Ｄ
1　日期　大項　小項　細節
2　5/01　伙食　早餐　飯糰
3　5/01　伙食　午餐　乾麵
4　5/02　印刷　名片　京畝名片
5　5/03　伙食　早餐　飯糰

表2
　　A　　Ｂ　　Ｃ　　Ｄ
1　日期　大項　小項　細節
2　6/01　伙食　早餐　飯糰
3　6/02　伙食　午餐　乾麵
4　6/04　印刷　大圖　京畝大圖
5　6/05　伙食　早餐　飯糰

我希望能在同一個活頁簿裡新開一個工作表(如圖中藍框)，
當我輸入某一個關鍵字，例如「京畝」(如圖中紅框)，
Excel可以自動幫我選取所有月份的工作表內的一整列資料，
包含日期、大項、小項、細節....(如圖中綠框)，
並複製到新工作表內，以幫助我快速統計這幾個月來所有和「京畝」有關的帳目。

想學起來之後可以應用在，例如說，我之後想統計這幾個月的早餐菜色和支出，可以不用一個一個去搜尋~
感謝各位前輩賜教~
作者: sunnyso    時間: 2013-4-10 22:15

回復 1# 金貝貝

[attach]14625[/attach]
[attach]14626[/attach]
作者: sunnyso    時間: 2013-4-11 11:06

回復 1# 金貝貝

Version 2
Simplified VBA code[attach]14635[/attach]
作者: ML089    時間: 2013-4-11 11:09

回復 2# sunnyso


    建議修改一行，增加查詢彈性，可以查 1月、早餐、伙食 ...等

    原 ActiveCell.FormulaR1C1 = "=ISERR(FIND(R1C2,RC[-1]))"
    改 ActiveCell.FormulaR1C1 = "=ISERR(FIND(R1C2,PHONETIC(RC[-4]:RC[-1])))"
作者: sunnyso    時間: 2013-4-11 12:03

回復 4# ML089

請問使用=PHONETIC()有什麼限制嗎?

它比用 =CONCATENATE( A2 ,  ,  ,  ,  ,  ,Z2) 和 = A2,&...,..., & Z2 簡潔很多
作者: ML089    時間: 2013-4-11 12:08

回復  ML089

請問使用=PHONETIC()有什麼限制嗎?

它比用 =CONCATENATE( A2 ,  ,  ,  ,  ,  ,Z2) 和 = ...
sunnyso 發表於 2013-4-11 12:03



    僅取文字格接合，數字格忽略
作者: 金貝貝    時間: 2013-4-11 16:26

哇，太感謝前輩們了，今晚就回家來試試看！
作者: sunnyso    時間: 2013-4-24 21:38

回復 1# 金貝貝

添加非VBA法
[attach]14772[/attach]
作者: 金貝貝    時間: 2013-4-28 21:27

哈哈，我也不能下載~~我只好每天上來領積分任務，希望早點升級下載回去用囉>U</
也祝你早早升級喔~一起加油吧XD~~
作者: smallpoper    時間: 2013-6-20 13:05

似乎也遇到跟樓樓一樣的問題~努力增加積分去(淚奔)
作者: 金貝貝    時間: 2013-7-4 21:18

一起加油吧各位QUQ，我每次想到就會來這裡點點積分，
猛一看是2月發問的，到現在還不能下載XDDDDDDDDDDDDD

我只差3個積分就可以下載了!!!!!!加油!!!!!!
作者: whyloveabby    時間: 2013-8-1 21:18

好想知道怎麼解喔
作者: b7307024    時間: 2013-10-11 16:08

嗨嗨~ 各位前輩好!!!
我將此find record1.rar下載回去研究了一下
請問是否一定要先將資料從各sheet複製出來存到某些地方
再從這些地方，複製到我想要顯示的欄位呢？

不能各sheet有符合的資料，直接顯示在我想要擺放的欄位嗎？
並且依據標題顯示，如：我只要顯示日期跟細節
謝謝喔！
作者: Vawan    時間: 2014-2-1 22:26

請問這類型的實作是否一定得借用到VBA的語法？
作者: GBKEE    時間: 2014-2-2 09:43

本帖最後由 GBKEE 於 2014-2-2 09:45 編輯

回復 14# b7307024
擺放資料工作表模組的程式碼
  1. Option Explicit                    '在模組層次中強迫每個在模組裏的變數都必須明確的宣告。
  2. Option Base 1                      '在模組層次中用來宣告陣列索引的預設下限->為 1
  3. Private Sub Worksheet_Change(ByVal Target As Range)
  4.     If Target.Address = "$B$1" Then
  5.          Ext_data Target
  6.     End If
  7. End Sub
  8. Private Sub Ext_data(ByVal Target As Range)
  9.     Dim mnth As Worksheet, all_month(), Rng As Range, AR(), R As Range, S As Integer
  10.     S = 1
  11.     Application.ScreenUpdating = False
  12.     Set Rng = Target.Parent.Range("A5")
  13.     Rng.CurrentRegion.Offset(1).Clear
  14.     all_month = Array("Jan", "Feb", "Mar")
  15.     For Each mnth In Sheets(all_month)
  16.         With mnth
  17.             .Range("A1").AutoFilter 4, "*" & Target & "*"            '自動篩選
  18.             For Each R In .AutoFilter.Range.SpecialCells(xlCellTypeVisible).Rows
  19.                 If R.Row > 1 Then
  20.                     ReDim Preserve AR(1 To S)
  21.                     'AR(S) = R   '整列
  22.                     AR(S) = Array(R.Cells(1).Value, R.Cells(4).Value)  '日期,細節
  23.                     S = S + 1
  24.                 End If
  25.             Next
  26.             .AutoFilterMode = False
  27.         End With        
  28.     Next
  29.     Rng.Offset(1).Resize(S - 1, UBound(AR(1))) = Application.Transpose(Application.Transpose(AR))
  30.     Application.ScreenUpdating = True
  31. End Sub
複製代碼
作者: jacks1130    時間: 2014-2-6 19:59

謝謝您~我正好需要這樣的需求~3Q
作者: kkinger    時間: 2014-2-21 15:09

謝謝分享，剛好有需要
作者: sisi056    時間: 2015-1-29 22:39

回復 3# sunnyso


    可以SHARE嘛, 本人也有相同雖然此VBA CODE...謝謝~
作者: funny0615    時間: 2019-4-18 19:48

回復 15# GBKEE


請教一下如果要將拉出來的資料做修改並回存原儲存格要怎麼做呢？



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)