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

帖子: 835
主題: 6
精華: 0
積分: 915
點名: 16
作業系統: Win 10,7
軟體版本: 2019,2013,2003
閱讀權限: 50
性別: 男
註冊時間: 2010-5-3
最後登錄: 2024-11-14

2^#

發表於 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程式的解決方式,
試試看.

Sub Tran()
Dim iI1%, iI2%
Dim lSum&, lRow%
Dim shSou As Worksheet, shTar As Worksheet
Set shSou = Worksheets("A")
Set shTar = Worksheets("B")
With shTar
.Range(.[B104], .[AX268]).ClearContents
End With
With shSou
For iI2 = 0 To 1
For lRow = 2 To 18
lSum = 0
For iI1 = 2 To 51
lSum = lSum + Cells(lRow, iI1)
Next
If lSum = 0 Then
Debug.Print iI2 * 39 + lRow & " , " & 102 + iI2 * 98 + lRow
.Cells(iI2 * 39 + lRow, 1).Resize(50).Copy
shTar.Cells(102 + iI2 * 98 + lRow, 2).PasteSpecial Paste:=xlPasteAll, Transpose:=True
End If
Next
For lRow = 2 To 19
lSum = 0
For iI1 = 2 To 51
lSum = lSum + Cells(lRow, iI1)
Next
If lSum = 0 Then
Debug.Print iI2 * 39 + 19 + lRow & " , " & 151 + iI2 * 98 + lRow
.Cells(iI2 * 39 + 19 + lRow, 1).Resize(50).Copy
shTar.Cells(151 + iI2 * 98 + lRow, 2).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
End If
Next
Next
End With
End Sub

複製代碼

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

3^#

發表於 2021-1-7 00:54 | 只看該作者

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

回復 2# luhpro
測試檔 :

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

不好意思，答案都不對，列251:列268沒有答案。
當A!B2:B51的儲存格(其餘...A!C2:C51....類推)=""""時，才將其同列的$A$2

A$51值橫置到B!的指定列，
不知道以0和1作判別，會不會有bug?

麻煩您再賜正。謝謝您

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

4^#

發表於 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
︰
︰
其餘….同理類推。

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

5^#

發表於 2021-1-7 13:46 | 只看該作者

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

以函數公式而言~
我的問題是TRANSPOSE()函數，我不會用

EX :
B104
=IF(ISERROR(SMALL(IF(B$2:B$51="",$A$2

A$51),ROW(A1))),"",SMALL(IF(B$2:B$51="",$A$2

A$51),ROW(A1)))
B105
=IF(ISERROR(SMALL(IF(C$2:C$51="",$A$2

A$51),ROW(B1))),"",SMALL(IF(C$2:C$51="",$A$2

A$51),ROW(B1)))
但如何讓"公式所產生的值"直接橫置在B104:AX105?
我一直不能編寫成功。
請各位大大不吝指教為禱!謝謝

版主 Rank: 7 Rank: 7 Rank: 7

准提部林

帖子: 2833
主題: 10
精華: 0
積分: 2889
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-12

6^#

發表於 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)))

其它自行畫畫~~

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

7^#

發表於 2021-1-7 21:58 | 只看該作者

回復 6# 准提部林
版主大大 : 您好 !
謝謝您出手相助和指導~感恩

如果沒有您~日子怎麼過?~~~~~~~

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

8^#

發表於 2021-1-12 06:10 | 只看該作者

回復 6# 准提部林

測試檔 :

公式=''''修改為大於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的公式應該如何編修?
敬請您賜教為禱。謝謝您^^

版主 Rank: 7 Rank: 7 Rank: 7

准提部林

帖子: 2833
主題: 10
精華: 0
積分: 2889
點名: 0
作業系統: 〔略〕
軟體版本: 〔略〕
閱讀權限: 100
性別: 男
來自: 〔略〕
註冊時間: 2013-5-13
最後登錄: 2024-11-12

9^#

發表於 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)))

帖子: 678
主題: 147
精華: 0
積分: 799
點名: 0
作業系統: win 8
軟體版本: MS 2003
閱讀權限: 50
性別: 男
註冊時間: 2016-2-9
最後登錄: 2022-1-20

10^#

發表於 2021-1-12 16:57 | 只看該作者

回復 9# 准提部林
版主大大 : 您好!
沒有您的幫忙，這個公式如果是我自學~在會之前一定是先去撞牆

萬分感激您的耐心指導(鞠躬拜謝)~

~