將資料自動分類功能 - Excel程式區

iceandy6150

中學生 Rank: 2

帖子: 129
主題: 25
精華: 0
積分: 159
點名: 0
作業系統: win7
軟體版本: office2010
閱讀權限: 20
性別: 男
註冊時間: 2011-12-24
最後登錄: 2022-12-12

21^# 跳轉到 »

發表於 2014-2-6 21:43 | 顯示全部帖子

回復 33# GBKEE

我終於完成囉，附上檔案

零用金清單-上傳用.rar (47.84 KB)

感謝G大及其他熱心版友的幫忙

哈囉~大家好呀

TOP

iceandy6150

中學生 Rank: 2

帖子: 129
主題: 25
精華: 0
積分: 159
點名: 0
作業系統: win7
軟體版本: office2010
閱讀權限: 20
性別: 男
註冊時間: 2011-12-24
最後登錄: 2022-12-12

22^#

發表於 2014-2-7 23:00 | 顯示全部帖子

本帖最後由 iceandy6150 於 2014-2-7 23:06 編輯

回復 38# Hsieh

感謝很多大大熱心回復，感動到快哭了
來不及消化之前，先發問一下

我原本的程式裡面加了防呆功能，Sheet1最右邊篩選出來的，如果參照表沒有該類別，會出錯
於是加了下列程式
         With Sheets("參照表")
         M = Sheets("Sheet1").Cells(i, .Columns.Count).Value
         Set Rng = .Range("A2:A30").Find(What:=M)

         If Rng Is Nothing Then
            MsgBox ("找不到<<" & M & ">>相對應類別，請增修參照表")
            MsgBox ("請記得去總表把本次資料刪除，以免重覆")
            Sheets("Sheet1").AutoFilterMode = False
            Application.ScreenUpdating = True
            Me.Activate
            Exit Sub
         End If

         Sh.[C2] = Rng.Offset(, 2)
         Sh.Name = Rng.Offset(, 1)
         End With

然後Sheet1的[類別]欄為了怕輸入參照表沒有的東西，所以設了下拉式選單
還有為了清除乾淨，讓第二次、第三次使用時，.usedrange.rows.count不會出錯
(明明下面列都沒東西了，還是抓很下面的列)
所以用程式去做輕除，設了按鈕2，如下

Private Sub CommandButton2_Click()
   Worksheets("參照表").Activate
   Worksheets("參照表").Range("A1:A30").Select
   Worksheets("參照表").Range("A30").Activate
   Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:= _
   False
'其實A1:A30應該是A1到A欄有資料的最後那一列，但我不會設
'只好先預設30個類別，以上是錄製巨集再複製貼上的

   Worksheets("Sheet1").Activate
Sheets("Sheet1").Range("A2:G150").Delete (xlShiftUp)
'這兩行是清空資料，一樣是預設到150，絕對夠用
'如果要~有多少資料就刪除多少資料，我又不會了

With Sheets("Sheet1")
With .Range("G2:G150").Validation
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
      xlBetween, Formula1:="=類別"
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .IMEMode = xlIMEModeNoControl
      .ShowInput = True
      .ShowError = True
End With
End With
End Sub
'這邊是設定下拉式選單，一樣錄巨集再貼過來用

最後想設一個防呆，檢查Sheet1的G欄(類別)，是否有[空白]
如果沒輸入，就不能篩選分類，也沒辦法去<參照頁>對照
大概知道要設定範圍~~Sheet1.Range(G2到G欄最後一列)
如果此範圍有空白欄，警告，並Exit sub
但我試不出來，請教各位大大解答了 <按鈕4>

Private Sub CommandButton4_Click()
Dim i
'方法一，失敗
For Each c In Sheets("Sheet1").UsedRange("G:G")
If c = "" Then i = 1
Else i = 0
End If
Next
'方法二，失敗
'If IsEmpty(Sheets("Sheet1").UsedRange("G:G")) Then
'方法三，失敗
'If Sheets("Sheet1").UsedRange("G:G").SpecialCells(xlCellTypeBlanks) Is Nothing Then
'方法四，失敗
'i = Sheets("Sheet1").UsedRange("G:G").SpecialCells(xlCellTypeBlanks)
'MsgBox (i)

If i = 1 Then MsgBox ("有空格")
Else
MsgBox ("無空格")
End If
End Sub

其實好像應該先檢查Sheet1.usedrange.rows.count
(先知道輸入幾筆資料，假設23筆)
再去數G欄有幾筆，小於23筆就知道有某幾筆沒填類別
有時候1~18筆都有選類別，19.20.21沒選，22.23有選

以上發問，感謝

哈囉~大家好呀

TOP

iceandy6150

中學生 Rank: 2

帖子: 129
主題: 25
精華: 0
積分: 159
點名: 0
作業系統: win7
軟體版本: office2010
閱讀權限: 20
性別: 男
註冊時間: 2011-12-24
最後登錄: 2022-12-12

23^#

發表於 2014-2-10 09:51 | 顯示全部帖子

回復 40# GBKEE

G大，有錯誤產生

01.Option Explicit

02.Private Sub CommandButton2_Click()

03. Sheets("參照表").UsedRange.Columns(1).CreateNames True

04. With Sheets("Sheet1").Range("G2:G150").Validation

05. .Add Type:=xlValidateList, Formula1:="=" & Sheets("參照表").UsedRange.Cells(1)

06. End With

07.End Sub

第05行，錯誤1004，應用程式或物件定義上的錯誤
不曉得為什麼會這樣
謝謝

哈囉~大家好呀

TOP

iceandy6150

中學生 Rank: 2

帖子: 129
主題: 25
精華: 0
積分: 159
點名: 0
作業系統: win7
軟體版本: office2010
閱讀權限: 20
性別: 男
註冊時間: 2011-12-24
最後登錄: 2022-12-12

24^#

發表於 2014-2-10 12:45 | 顯示全部帖子

回復 42# GBKEE

G大可以了耶
這也太神奇了，為什麼會這樣呢? 可以指導說明一下嗎?謝謝

哈囉~大家好呀

TOP

iceandy6150

中學生 Rank: 2

帖子: 129
主題: 25
精華: 0
積分: 159
點名: 0
作業系統: win7
軟體版本: office2010
閱讀權限: 20
性別: 男
註冊時間: 2011-12-24
最後登錄: 2022-12-12

25^#

發表於 2014-2-10 22:41 | 顯示全部帖子

回復 44# GBKEE

我可不可以在請問一下 UsedRange的用法

我發現如果一個工作表的A1~A10有畫框線 (或稱為表格也行)

然後只有A1~A5填入資料，A6~A10是空格

這樣情況下使用with sheets("工作表")

.UsedRange.Rows.count會算到10，而不是5

若針對.UsedRange這個範圍去使用CountA會數出5個

但如果我不想計算框線，也不是要算這個範圍中有數值的是幾個
只想找到有填入數值的最後一個格子
而且中間可能會有空格，該怎麼辦呢?

如下圖，我想找G欄的最後一筆資料，目前是G55
可是每個月增加資料後，可能會變G58、G63等等的

哈囉~大家好呀

TOP

iceandy6150

中學生 Rank: 2

帖子: 129
主題: 25
精華: 0
積分: 159
點名: 0
作業系統: win7
軟體版本: office2010
閱讀權限: 20
性別: 男
註冊時間: 2011-12-24
最後登錄: 2022-12-12

26^#

發表於 2014-2-11 23:52 | 顯示全部帖子

本帖最後由 iceandy6150 於 2014-2-11 23:54 編輯

回復 46# GBKEE

大大，您寫得程式正常運作無誤，感謝
只是我有點好奇 .Range("G" & .Rows.Count).End(xlUp).Address
我是改成.Range("G" & .Rows.Count).End(xlUp).Value
因為我需要那一格的值
好奇的地方是為什麼您用 xlup ?
我們不是要找某範圍最下面一格儲存格，應該是xldown?

另外想在請問一個頭痛的問題
如下圖
是一個類似會計計帳的EXCEL表格
很令人傻眼的是，月份中間居然會有空白處 (資料不連續)
然後日期也只有打月、日
真正會用到的數字是在紅色格線的上面
以人眼看，是還算清楚，但是要用程式抓資料，很頭大捏
因為不是每一排都有年月日可以參考

還有更傻眼的如下圖

把EXCEL當作簿子使用，連頁數都有，但是102年只有一格
下面的就通通代表102年發生的了
然後不是每個月都有資料，本圖只有1、6、12月才有資料
而且本圖是要抓F欄位，紅色格線上方828,170那個數據

所需功能概述：
假設有A,B,C,D,E五種會計科目，分別是五張工作表
另外每個月結算需要一個結算工作表
需要到A,B,C,D,E去抓資料，到<結算>工作表，再去加加減減

那A,B,C工作表可能是像圖1，每個月一定有資料，但會有空格
我只要找G欄最下方一個數值，貼過去<結算>，就完成了
(這邊也使用G大教學，已可執行)

但D,E工作表可能是像圖2
資料不是每個月都有
比如我要結103年02月的資料
ABC是沒問題
DE如果有103年02月的資料才抓到<結算>
如果如圖2，沒有資料則不抓

要是我使用.Range("F" & .Rows.Count).End(xlUp).Value
勢必會抓錯，把102年12月31日的828170這筆資料誤貼過去<結算>
而那種怪異的年月日格式，如何讓程式不會誤判呢?

謝謝

哈囉~大家好呀

TOP

iceandy6150

中學生 Rank: 2

帖子: 129
主題: 25
精華: 0
積分: 159
點名: 0
作業系統: win7
軟體版本: office2010
閱讀權限: 20
性別: 男
註冊時間: 2011-12-24
最後登錄: 2022-12-12

27^#

發表於 2014-2-12 17:11 | 顯示全部帖子

回復 48# GBKEE

大大，感謝解說

檔案我刪掉一些東西後，上傳
裡面最右邊是<損益表>
藍色範圍是要去找的相關類別工作表
我有設一個按鈕，寫了部分的程式
橘色是要填入資料的地方

其餘沒顏色的部分就暫不處理
事後我再修就好

我想是不是要設一個輸入區
詢問使用者想產生哪一個月的損益表
例如103年01月

那程式就去各類工作表，如果有103年01月的資料
就放到<損益表>相關欄位去
如果沒有，就不放

只是各類工作表真的長得很怪....

總分類帳-上傳用.rar (101.29 KB)

哈囉~大家好呀

TOP

iceandy6150

中學生 Rank: 2

帖子: 129
主題: 25
精華: 0
積分: 159
點名: 0
作業系統: win7
軟體版本: office2010
閱讀權限: 20
性別: 男
註冊時間: 2011-12-24
最後登錄: 2022-12-12

28^#

發表於 2014-2-13 23:24 | 顯示全部帖子

回復 50# GBKEE

感謝G大，太神了

這麼詭異的表格也能找到方法統計
還不是很懂程式原理
但是自己測試是能正常運作
明天找使用者問看看

再次感謝!

哈囉~大家好呀

TOP

iceandy6150

中學生 Rank: 2

帖子: 129
主題: 25
精華: 0
積分: 159
點名: 0
作業系統: win7
軟體版本: office2010
閱讀權限: 20
性別: 男
註冊時間: 2011-12-24
最後登錄: 2022-12-12

29^#

發表於 2014-2-15 18:51 | 顯示全部帖子

本帖最後由 iceandy6150 於 2014-2-15 18:56 編輯

回復 50# GBKEE

G大，不好意思，又來發問了

壹、請問您的程式中，
      Rng_Ar(i) = Rng_Ar(i) + Rng(2).Range("F1")                'Range("F1"):金額位置
   是去抓取下圖中ABCD哪一個位置呢?

   雖然四個位置的值都一樣，實際使用上要選C
   不過只有A，是有月日可以對照
   好奇問一下

貳、其他的部分也需要加進程式中，可是我不會改

      右上方黃色的格子，是要填入 <銷貨收入47-48>的F欄的倒數第2個值，也就是圖1的C
      但我上次弄錯了，以為是最後一個格子
      所以原本程式如下：
      Dim j
      With Sheets("銷貨收入47-48").Range("F:F")

   'Sheets("損益表").Range("C6").Value = .Cells(.Count).End(xlUp).Value 這行無法直接動作，故放棄

   j = .Cells(.Count).End(xlUp).Value
   Sheets("損益表").Range("C6").Value = j
End With

   那我改成
      j = .Cells(.Count - 1).End(xlUp).Value  失敗，還是抓最下一格
      j = .Cells(.Count).End(xlUp).Offset(-1).Value 失敗，J值是空的

   [問題一]該怎麼改成倒數第二格呢?

[問題二]如果要使用G大的程式去動作，怎麼加呢?
另外，有些範圍也想加

圖2的藍色部分
期初存貨 B8 = <存貨5-6>的F欄的倒數第二格的值(其實C=ABD)
進貨 B9 = <進貨51-52>的F欄的倒數第二格的值

*減：期末存貨 B11 = <存貨5-6>的F欄的最後一格 (圖1的位置E)
(這邊就真的可以用 .Cells(.Count).End(xlUp).Value)

圖2下面有橘色部分，B35、B36、B37
其他收入：目前沒建立工作表，因為很少用到，但為了程式順利運作，可增設一空工作表名為<其他收入>
利息收入 B36 = <利息收入93-94>的G欄倒數第二格(圖1位置D)
拥金收入B37 = <佣金收入95-96>的G欄倒數第二格(圖1位置D)

原本G大的程式是設定A18~A30，實際可能是到A32，我可微調
(因為要上傳，有些東西我先刪掉)
但多了B8.B9.B11這三個不連續的，而且B11找的地方不一樣
B35、B36、B37，也是
如何讓這幾個也能判斷年月日，正常運作放資料，我就不會改了

哈囉~大家好呀

TOP

iceandy6150

中學生 Rank: 2

帖子: 129
主題: 25
精華: 0
積分: 159
點名: 0
作業系統: win7
軟體版本: office2010
閱讀權限: 20
性別: 男
註冊時間: 2011-12-24
最後登錄: 2022-12-12

30^#

發表於 2014-2-16 11:28 | 顯示全部帖子

本帖最後由 iceandy6150 於 2014-2-16 11:33 編輯

回復 54# GBKEE

感謝G大

再次上傳檔案，需求如#53篇所述

圖中紅色箭頭要取的值較為特殊，是該工作表最下面的總結

謝謝

附上檔案

總分類帳-上傳用.rar (111.31 KB)

哈囉~大家好呀

TOP

[發問] 將資料自動分類功能

[收藏此主題] [關注此主題的新回復]

[通過 QQ、MSN 分享給朋友]