各位先進大家好,目前遇到個資料統計彙整的問題(應該算是吧)首頁大表如下
這張表示我平時使用的格式,但是項目更多,我只是列舉幾項,然後我要做一張表讓他變成下列這樣子
目前都是呆呆的用=過去,有沒有可能像上圖的牛肉,直接輸入雞肉時,他會帶出雞肉的資料
附加壓縮檔: 201904/mobile01-db783bd2fc408f81a568d3168c4d4f0a.zip
先謝謝各位高手
https://www.mobile01.com/topicdetail.php?f=511&t=5056987&p=1#63233105
https://www.mobile01.com/topicdetail.php?f=511&t=5138523
https://www.mobile01.com/topicdetail.php?f=511&t=5672738&p=1
https://www.mobile01.com/topicdetail.php?f=511&t=5056987&p=1#63233105
or
'======================================
Sub test()
Call delsheet
Dim temparray() As String, col As String, row As Integer,i as Integer, criteriadata As Variant
Dim wb As Workbook, ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Sheets("工作表1")
Application.ScreenUpdating = False
col = "b"
row = 3
With ws
.Range(col & ":" & col).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Set allcriteria = .Range(ws.Cells(row, col), ws.Cells(row, col).End(xlDown)).SpecialCells(xlCellTypeVisible)
ReDim temparray(1 To allcriteria.Count)
For Each criteriadata In allcriteria
i = i + 1: temparray(i) = criteriadata
Next
row = row - 1
For Each criteriadata In temparray
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "_" & criteriadata & "_"
.Range(ws.Cells(row, col), ws.Cells(row, col).End(xlDown)).AutoFilter Field:=1, Criteria1:=criteriadata
.UsedRange.Copy Sheets("_" & criteriadata & "_").Cells(1, 1)
Next
.AutoFilterMode = False
.Select
End With
Application.ScreenUpdating = True
End Sub
Sub delsheet()
Application.DisplayAlerts = False
Dim delsheet() As Variant, protect() As Variant, i As Integer, j As Integer
ReDim delsheet(1 To Worksheets.Count)
protect = Array("工作表1") '注意:只有名稱在protect陣列內的工作表不會被刪除
For i = 1 To Worksheets.Count
If Join(Filter(protect, Worksheets(i).Name)) = "" Then
j = j + 1
delsheet(j) = Worksheets(i).Name
End If
Next
If j = 0 Then Exit Sub
ReDim Preserve delsheet(1 To j)
Worksheets(delsheet).Delete
Worksheets(1).Select
Application.DisplayAlerts = True
End Sub
'======================================
用EXCEL 增加一個基本資料登錄<data base>工作表,作為搜尋資料庫
另設一個查詢<inquire>工作表,作為指定食材,以下拉式表單查詢各分店每日指定食材用量,並匯總日/月用量
看看是否為版主所需要的內容,壓縮檔隨附~
附加壓縮檔: 201904/mobile01-737db3cae7c57a49ab46631e63a13b29.zip
tppr3695a wrote:
可否不要用到VBA...(恕刪)
https://www.mobile01.com/topicdetail.php?f=511&t=4692248&p=1
https://www.mobile01.com/topicdetail.php?f=511&t=4688446&p=1#58992753
一、定義名稱
名稱:data
公式:=OFFSET(工作表1!$B$2,1,0,COUNTA(工作表1!$B:$B),1)
二、
查詢的工作表
c1=工作表1!C1
c2=工作表1!C2
公式向右拉
三、
查詢的工作表
a3=IFERROR(OFFSET(工作表1!$B$2,SMALL(IF(data=$A$1,ROW(data),FALSE),ROW(1:1))-2,COLUMN(工作表
1!A:A)-2),"")
(陣列公式)
公式向右、向下,拉到最大可能範圍
附加壓縮檔: 201904/mobile01-fd1249b6e49f79efd3d3a302e73b9f4e.zip