Board logo

標題: [發問] 搜尋+轉置貼上的函數公式 [打印本頁]
作者: ziv976688    時間: 2021-1-5 18:22     標題: 搜尋+轉置貼上的函數公式

[attach]32941[/attach]
測試檔︰[attach]32942[/attach]
需求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:06

本帖最後由 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
複製代碼
作者: ziv976688    時間: 2021-1-7 00:54

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

回復 2# luhpro
測試檔 : [attach]32950[/attach]
luhpro大大:您好!
感謝回覆

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

麻煩您再賜正。謝謝您
作者: ziv976688    時間: 2021-1-7 02:04

本帖最後由 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
︰
︰
其餘….同理類推。
作者: ziv976688    時間: 2021-1-7 13:46

本帖最後由 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?
我一直不能編寫成功。
請各位大大不吝指教為禱!謝謝
作者: 准提部林    時間: 2021-1-7 20:49

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)))

其它自行畫畫~~
作者: ziv976688    時間: 2021-1-7 21:58

回復 6# 准提部林
版主大大 : 您好 !
謝謝您出手相助和指導~感恩
如果沒有您~日子怎麼過?~~~~~~~
作者: ziv976688    時間: 2021-1-12 06:10

回復 6# 准提部林
[attach]32966[/attach]
測試檔 : [attach]32967[/attach]
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的公式應該如何編修?
敬請您賜教為禱。謝謝您^^
作者: 准提部林    時間: 2021-1-12 16:13

回復 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)))
作者: ziv976688    時間: 2021-1-12 16:57

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

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

回復 9# 准提部林
版主大大 : 您好 !
下列問題 :
http://forum.twbts.com/thread-22984-1-1.html
請您再幫忙解惑賜教為禱。
謝謝您



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