返回列表 上一主題 發帖

[發問] 搜尋+轉置貼上的函數公式

[發問] 搜尋+轉置貼上的函數公式

未命名.png
2021-1-5 18:21

測試檔︰ 搜尋+轉置貼上的函數公式.rar (29.37 KB)
需求a︰
當工作表A的B2︰B51=空白時,則將其同列A欄的值,轉置貼上工作表B的B104︰AX104
當工作表A的C2︰C51=空白時,則將其同列A欄的值,轉置貼上工作表B的B105︰AX105


當工作表A的R2︰R51=空白時,則將其同列A欄的值,轉置貼上工作表B的B120︰AX120

需求b︰
當工作表A的U2︰U51=空白時,則將其同列A欄的值,轉置貼上工作表B的B153︰AX153
當工作表A的V2︰V51=空白時,則將其同列A欄的值,轉置貼上工作表B的B154︰AX154


當工作表A的AL2︰AL51=空白時,則將其同列A欄的值,轉置貼上工作表B的B170︰AX170

需求c︰
當工作表A的AO2︰AO51=空白時,則將其同列A欄的值,轉置貼上工作表B的B202︰AX202
當工作表A的AP2︰AP51=空白時,則將其同列A欄的值,轉置貼上工作表B的B203︰AX203


當工作表A的BE2︰BE51=空白時,則將其同列A欄的值,轉置貼上工作表B的B218︰AX218

需求d︰
當工作表A的BH2︰BH51=空白時,則將其同列A欄的值,轉置貼上工作表B的B251︰AX251
當工作表A的BI2︰BI51=空白時,則將其同列A欄的值,轉置貼上工作表B的B252︰AX252


當工作表A的BY2︰BY51=空白時,則將其同列A欄的值,轉置貼上工作表B的B268︰AX268

請問︰工作表B! B104,B153,B202,251的函數公式應如何編寫?
謝謝!

本帖最後由 luhpro 於 2021-1-6 23:09 編輯
測試檔︰
需求a︰
當工作表A的B2︰B51=空白時,則將其同列A欄的值,轉置貼上工作表B的B104︰AX104
當工 ...
ziv976688 發表於 2021-1-5 18:22

儲存格公式我始終不是看得很懂,
依據你短消息所說我這裡提供VBA程式的解決方式,
試試看.
  1. Sub Tran()
  2.   Dim iI1%, iI2%
  3.   Dim lSum&, lRow%
  4.   Dim shSou As Worksheet, shTar As Worksheet
  5.   
  6.   Set shSou = Worksheets("A")
  7.   Set shTar = Worksheets("B")
  8.   With shTar
  9.     .Range(.[B104], .[AX268]).ClearContents
  10.   End With
  11.   With shSou
  12.     For iI2 = 0 To 1
  13.       For lRow = 2 To 18
  14.         lSum = 0
  15.         For iI1 = 2 To 51
  16.           lSum = lSum + Cells(lRow, iI1)
  17.         Next
  18.         If lSum = 0 Then
  19.           Debug.Print iI2 * 39 + lRow & " , " & 102 + iI2 * 98 + lRow
  20.           .Cells(iI2 * 39 + lRow, 1).Resize(50).Copy
  21.           shTar.Cells(102 + iI2 * 98 + lRow, 2).PasteSpecial Paste:=xlPasteAll, Transpose:=True
  22.         End If
  23.       Next
  24.    
  25.       For lRow = 2 To 19
  26.         lSum = 0
  27.         For iI1 = 2 To 51
  28.           lSum = lSum + Cells(lRow, iI1)
  29.         Next
  30.         If lSum = 0 Then
  31.           Debug.Print iI2 * 39 + 19 + lRow & " , " & 151 + iI2 * 98 + lRow
  32.           .Cells(iI2 * 39 + 19 + lRow, 1).Resize(50).Copy
  33.           shTar.Cells(151 + iI2 * 98 + lRow, 2).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
  34.                   SkipBlanks:=False, Transpose:=True
  35.         End If
  36.       Next
  37.     Next
  38.   End With
  39. End Sub
複製代碼

TOP

本帖最後由 ziv976688 於 2021-1-7 00:58 編輯

回復 2# luhpro
測試檔 : 搜尋+轉置貼上(VBA).rar (35.85 KB)
luhpro大大:您好!
感謝回覆

不好意思,答案都不對,列251:列268沒有答案。
當A!B2:B51的儲存格(其餘...A!C2:C51....類推)=""""時,才將其同列的$A$2A$51值橫置到B!的指定列,
不知道以0和1作判別 ,會不會有bug?

麻煩您再賜正。謝謝您

TOP

本帖最後由 ziv976688 於 2021-1-7 02:08 編輯

回復 2# luhpro
儲存格公式我始終不是看得很懂,

不好意思,範例檔的B工作表~以人工填入的數字有很多筆誤(EX︰106列少了個2,即D106=2,其餘順退1格;
另107列和109列都少了0,即B107=0,其餘順退1格和B109=0,其餘順退1格)~尚請見諒^^"

再將B!各列儲存格數字的需求邏輯,以實例做說明︰
EX︰
當A!的B2=""時,則將A!的A2=0顯示在B!的B104
當A!的B20:B51=""時,則將A!的A20:A51=18~49顯示在B!的C104:AH104

當A!的C2:C3=""時,則將A!的A2:A3=0~1顯示在B!的B105:C105
當A!的C28:C51=""時,則將A!的A28:A51=26~49顯示在B!的D105:AA105

當A!的D2:D5=""時,則將A!的A2:A5=0~3顯示在B!的B106:E106
當A!的D8=""時,則將A!的A8 =6顯示在B!的F106
當A!的D32=""時,則將A!的A32 =30顯示在B!的G106
當A!的D34=""時,則將A!的A34=32顯示在B!的H106
當A!的D36:D51=""時,則將A!的A36:A51=34~49顯示在B!的I106:X106

當A!的E2:E5=""時,則將A!的A2:A5=0~3顯示在B!的B107:E107
當A!的E7:E11=""時,則將A!的A7:A11=5~9顯示在B!的F107:J107
當A!的E38:E51=""時,則將A!的A38:A51=36~49顯示在B!的K107:X107


其餘….同理類推。

TOP

本帖最後由 ziv976688 於 2021-1-7 14:01 編輯

以函數公式而言~
我的問題是TRANSPOSE()函數,我不會用
EX :
B104
=IF(ISERROR(SMALL(IF(B$2:B$51="",$A$2A$51),ROW(A1))),"",SMALL(IF(B$2:B$51="",$A$2A$51),ROW(A1)))
B105
=IF(ISERROR(SMALL(IF(C$2:C$51="",$A$2A$51),ROW(B1))),"",SMALL(IF(C$2:C$51="",$A$2A$51),ROW(B1)))
但如何讓"公式所產生的值"直接橫置在B104:AX105?
我一直不能編寫成功。
請各位大大不吝指教為禱!謝謝

TOP

B104/陣列
=IF(COLUMN(A$1)>SUM(N(INDEX(A!$B$2:$R$51,,ROW($A1))="")),"",SMALL(IF(INDEX(A!$B$2:$R$51,,ROW($A1))="",A!$A$2:$A$51),COLUMN(A$1)))

其它自行畫畫~~

TOP

回復 6# 准提部林
版主大大 : 您好 !
謝謝您出手相助和指導~感恩
如果沒有您~日子怎麼過?~~~~~~~

TOP

回復 6# 准提部林
未命名.png
5 天前 06:09

測試檔 : 公式=''''修改為大於10.rar (60 KB)
B104/陣列
=IF(COLUMN(A$1)>SUM(N(INDEX(A!$B$2R$51,,ROW($A1))="")),"",SMALL(IF(INDEX(A!$B$2R$51,, ...
准提部林 發表於 2021-1-7 20:49

版主大大 :
不好意思,如果想要將A!$B$2:$R$51=""的同列A欄值轉置到B104的貴解公式
改為
將A!$B$2:$R$51>10的同列A欄值轉置到B104

請問 : B104的公式應該如何編修?
敬請您賜教為禱。謝謝您^^

TOP

回復 8# ziv976688
=IF(COLUMN(A$1)>SUM(N(--(0&INDEX(A!$B$2:$S$51,,ROW($A1)))>10)),"",SMALL(IF(--(0&INDEX(A!$B$2:$S$51,,ROW($A1)))>10,A!$A$2:$A$51),COLUMN(A$1)))

TOP

回復 9# 准提部林
版主大大 : 您好!
沒有您的幫忙,這個公式如果是我自學~在會之前一定是先去撞牆
萬分感激您的耐心指導(鞠躬拜謝)~ ~ ~

TOP

        靜思自在 : 【生命在呼吸間】佛陀說:「生命在呼吸間。」人無法管住自己的生命,更無法擋住死期,讓自己永住人間。既然生命去來這麼無常,我們更應該好好地愛惜它、利用它、充實它,讓這無常、寶貴的生命,散發它真善美的光輝,映照出生命真正的價值。
返回列表 上一主題