麻辣家族討論版版 - Powered by Discuz! Board

標題: 姓名按起或止及日期排列顯示 [打印本頁]

作者: shan0948 時間: 2023-2-20 20:11 標題: 姓名按起或止及日期排列顯示

請問先進，小弟有個檔案(如附檔)，需要將左表的資料，顯示成右表的排列，請問函數公式要如何設定，謝謝先進！[attach]35861[/attach]

作者: Andy2483 時間: 2023-2-21 09:39

回復 1# shan0948

謝謝前輩發表此主題與範例
後學藉此主題練習VBA陣列的建議方案下,請前輩試試看

執行前:
[attach]35862[/attach]

執行結果:
[attach]35863[/attach]

Option Explicit
Sub TEST()
Dim Brr, i&, N&, T1&, T6$, T7$
Brr = Range([G4], Cells(Rows.Count, 1).End(3))
For i = 1 To UBound(Brr)
T1 = Val(Brr(i, 1)): T6 = Brr(i, 6): T7 = Trim(Brr(i, 7))
Brr(i, 2) = "": Brr(i, 3) = ""
If T7 = "" Then GoTo 111
If T1 > 0 Then
   N = i
   If T7 = "起" Then Brr(N, 2) = T6
   If T7 = "止" Then Brr(N, 3) = T6
   ElseIf T7 = "起" Then
      Brr(N, 2) = IIf(Brr(N, 2) = "", T6, Brr(N, 2) & "、" & T6)
   ElseIf T7 = "止" Then
      Brr(N, 3) = IIf(Brr(N, 3) = "", T6, Brr(N, 3) & "、" & T6)
End If
111
Next
[J4].Resize(N, 3) = Brr
End Sub

作者: shan0948 時間: 2023-2-21 10:07

回復 2# Andy2483

感謝前輩的指導，因小弟需要在不同表內使用，可否使用函數公式較為簡單，感恩謝謝！

作者: Andy2483 時間: 2023-2-21 10:13

回復 3# shan0948

謝謝前輩回復
後學函數公式很弱,函數公式比VBA難,後學努力學習中,等函數公式厲害的前輩指導
一起學習

作者: Andy2483 時間: 2023-2-21 11:27

本帖最後由 Andy2483 於 2023-2-21 11:30 編輯

回復 3# shan0948

謝謝前輩發表此主題,讓後學有練習此情境的機會
以下是資料表與結果表不同工作表的方案,請前輩參考

Option Explicit
Sub TEST()
Dim Brr, i&, N&, T1&, T6$, T7$, Sh1 As Worksheet, Sh2 As Worksheet
'↑宣告變數:Brr是通用型變數,(i,N,T1)是長整數變數,(T6,T7)是字串變數,
'(Sh1,Sh2)是工作表變數
Set Sh1 = Sheets("資料表")
'↑令Sh1這工作表變數是名為"資料表"的工作表
Set Sh2 = Sheets("結果表")
'↑令Sh2這工作表變數是名為"結果表"的工作表
Brr = Range(Sh1.[G4], Sh1.Cells(Rows.Count, 1).End(3))
'↑令Brr是二維陣列!以資料表[G4]到A欄最後一個有內容儲存格,這範圍值帶入陣列裡
For i = 1 To UBound(Brr)
'↑設順迴圈!i從1到Brr陣列縱向最大索引列號數
T1 = Val(Brr(i, 1)): T6 = Brr(i, 6): T7 = Trim(Brr(i, 7))
'↑令T1這長整數變數是 i迴圈列第1欄Brr陣列值,以Val()轉化回傳數值,
'↑令T6這字串變數是 i迴圈列第6欄Brr陣列值
'↑令T7這字串變數是 i迴圈列第7欄Brr陣列值,以Trim()去除頭尾空白字元後的新字串
Brr(i, 2) = "": Brr(i, 3) = ""
'↑令i迴圈列第2欄Brr陣列值是空字元:令i迴圈列第3欄Brr陣列值是空字元
If T7 = "" Then GoTo 111
'↑如果T7變數是空字元!就跳到 111位置繼續執行
If T1 > 0 Then
'↑如果T1變數大於 0??
   N = i
   '↑令N這長整數變數是i迴圈數
   If T7 = "起" Then Brr(N, 2) = T6
   '↑如果T7變數是 "起"!就令i迴圈列第2欄Brr陣列值是 T6變數
   If T7 = "止" Then Brr(N, 3) = T6
   '↑如果T7變數是 "止"!就令i迴圈列第3欄Brr陣列值是 T6變數
   ElseIf T7 = "起" Then
   '↑否則如果T1是 0或小於0!而且T7變數是 "起"字串
      Brr(N, 2) = IIf(Brr(N, 2) = "", T6, Brr(N, 2) & "、" & T6)
      '↑令i迴圈列第2欄Brr陣列值是 IIf()回傳值:
      '如果i迴圈列第2欄Brr陣列值是空字元,IIf()回傳值是T6變數,
      '否則,IIf()回傳值是i迴圈列第2欄Brr陣列值連接"、",再連接T6變數後的新字串
   ElseIf T7 = "止" Then
   '↑否則如果T1是 0或小於0!而且T7變數是 "止"字串
      Brr(N, 3) = IIf(Brr(N, 3) = "", T6, Brr(N, 3) & "、" & T6)
      '↑令i迴圈列第3欄Brr陣列值是 IIf()回傳值:
      '如果i迴圈列第3欄Brr陣列值是空字元,IIf()回傳值是T6變數,
      '否則,IIf()回傳值是i迴圈列第3欄Brr陣列值連接"、",再連接T6變數後的新字串
End If
111
Next
Sh2.[J4].Resize(N, 3) = Brr
'↑令結果表[J4]擴展向下N列,向右擴展3欄範圍的儲存格以Brr陣列值帶入
Application.Goto Sh2.[J4]
'↑令儲存格游標跳到結果表[J4]位置
End Sub

作者: hcm19522 時間: 2023-2-21 14:33

https://blog.xuite.net/hcm19522/twblog/590732675

作者: shan0948 時間: 2023-2-22 14:15

回復 5# Andy2483

感謝先進的指導！

作者: shan0948 時間: 2023-2-22 15:05

回復 6# hcm19522

非常感謝先進的指導，經測試後符合需求，助益甚大，感恩謝謝！

作者: shan0948 時間: 2023-3-13 11:24

回復 6# hcm19522

先進好，之前的資料顯示函數公式，因實際需要增加條件判斷，原函數公式請問要如何修正，詳細說明如附檔，謝謝！[attach]35919[/attach]

作者: Andy2483 時間: 2023-3-14 11:59

回復 9# shan0948

謝謝前輩
後學練習VBA方式的解決方案如下,請前輩參考

執行結果:
[attach]35938[/attach]

Option Explicit
Sub TEST()
Dim Brr, i&, N&, T1&, T6$, T7$, T8$
Brr = Range([H4], Cells(Rows.Count, 1).End(3))
For i = 1 To UBound(Brr)
T1 = Val(Brr(i, 1)): T6 = Trim(Brr(i, 6)): T7 = Trim(Brr(i, 7))
T8 = Trim(Brr(i, 8))
Brr(i, 2) = "": Brr(i, 3) = "": Brr(i, 4) = "": Brr(i, 5) = ""
If T8 = "" Then GoTo 111
If T1 > 0 Then
   N = i
   If T8 = "起" Then Brr(N, 2) = T6
   If T8 = "止" Then Brr(N, 3) = T6
   ElseIf T8 = "起" Then
      Brr(N, 2) = IIf(Brr(N, 2) = "", T6, Brr(N, 2) & "、" & T6)
   ElseIf T8 = "止" Then
      Brr(N, 3) = IIf(Brr(N, 3) = "", T6, Brr(N, 3) & "、" & T6)
      If T7 = "離職" Then
         Brr(N, 5) = IIf(Brr(N, 5) = "", T6, Brr(N, 5) & "、" & T6)
      End If
End If
If T8 = "起" And (T7 = "新進" Or T7 = "他訓轉入") Then
   Brr(N, 4) = IIf(Brr(N, 4) = "", T6, Brr(N, 4) & "、" & T6)
End If
If T8 = "止" And T7 = "離職" Then
   Brr(N, 5) = IIf(Brr(N, 5) = "", T6, Brr(N, 5) & "、" & T6)
End If
111
Next
[K4].Resize(N, 5) = Brr
End Sub

作者: shan0948 時間: 2023-3-14 12:22

回復 10# Andy2483

感謝先進的指導，因之前是使函數公式，故為維持一致性，可否仍使用函數公式設定，謝謝！

作者: Andy2483 時間: 2023-3-14 12:26

回復 11# shan0948

謝謝前輩回復
公式(函數)比VBA難,後學暫幫不上忙

作者: ML089 時間: 2023-3-14 15:32

回復 9# shan0948

[attach]35942[/attach]

公式L4=SUBSTITUTE(TRIM(IF($H4=L$3,$F4,"")&IF($A5=""," "&L5,""))," ","、") 右拉下拉
公式N4=SUBSTITUTE(TRIM(IF(ISERROR(FIND($G4,N$3)),"",$F4)&IF($A5=""," "&N5,""))," ","、") 右拉下拉

條件式格式設定
選擇 L4:O50
工具列 - 條件式格式設定 - 新增規則 - 使用公式來決定... - 公式 = $A4=""
將填滿與字型的顏色設為背景顏色(白色)

作者: shan0948 時間: 2023-3-15 07:49

回復 13# ML089

M 大好，感謝指導，經測試公式設定後結果似未完全符合增加的需求，詳細說明如附檔案，謝謝！[attach]35943[/attach]

作者: ML089 時間: 2023-3-15 16:16

回復 14# shan0948
問題：
日期 1 起：
梁鎮魯、葉漢章 2人僅顯示在 L4 ，
L5、L6、L7、L8、L9 均不顯示，
日期 1 止也是同樣方式

回覆：
(1) 有日期位置 L4，公式結果是否符合你要的答案 ?
(2) L5、L6、L7、L8、L9 均不顯示，可以使用白色文字及填充白色來遮蓋文字，設定上次已經說明，如下
條件式格式設定
選擇 L4:O50
工具列 - 條件式格式設定 - 新增規則 - 使用公式來決定... - 公式 = $A4=""
將填滿與字型的顏色設為背景顏色(白色)

作者: shan0948 時間: 2023-3-15 19:21

回復 15# ML089

M 大好，感謝您的回覆，您的公式有得到需要的結果，也瞭解您的意思，這方式也不失為另一種途徑，謝謝您！

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