12
返回列表 上一主題 發帖

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

ziv976688

帖子
678 
主題
147 
精華
0 
積分
799 
點名
0  
作業系統
win 8 
軟體版本
MS 2003 
閱讀權限
50 
性別
男 
註冊時間
2016-2-9 
最後登錄
2022-1-20 
1# 跳轉到 » 倒序看帖
打印
tT
發表於 2021-1-5 18:22 | 只看該作者

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


測試檔︰ 搜尋+轉置貼上的函數公式.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

帖子
835 
主題
6 
精華
0 
積分
915 
點名
13  
作業系統
Win 10,7 
軟體版本
2019,2013,2003 
閱讀權限
50 
性別
男 
註冊時間
2010-5-3 
最後登錄
2024-10-25 
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程式的解決方式,
試試看.
  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

帖子
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$2A$51值橫置到B!的指定列，
不知道以0和1作判別 ，會不會有bug?

麻煩您再賜正。謝謝您

TOP

ziv976688

帖子
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
︰
︰
其餘….同理類推。

TOP

ziv976688

帖子
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$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

准提部林

帖子
2830 
主題
10 
精華
0 
積分
2886 
點名
0  
作業系統
〔略〕 
軟體版本
〔略〕 
閱讀權限
100 
性別
男 
來自
〔略〕 
註冊時間
2013-5-13 
最後登錄
2024-11-3 
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)))

其它自行畫畫~~

TOP

ziv976688

帖子
678 
主題
147 
精華
0 
積分
799 
點名
0  
作業系統
win 8 
軟體版本
MS 2003 
閱讀權限
50 
性別
男 
註冊時間
2016-2-9 
最後登錄
2022-1-20 
7#
發表於 2021-1-7 21:58 | 只看該作者
回復 6# 准提部林
版主大大 : 您好 !
謝謝您出手相助和指導~感恩
如果沒有您~日子怎麼過?~~~~~~~

TOP

ziv976688

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

TOP

准提部林

帖子
2830 
主題
10 
精華
0 
積分
2886 
點名
0  
作業系統
〔略〕 
軟體版本
〔略〕 
閱讀權限
100 
性別
男 
來自
〔略〕 
註冊時間
2013-5-13 
最後登錄
2024-11-3 
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)))

TOP

ziv976688

帖子
678 
主題
147 
精華
0 
積分
799 
點名
0  
作業系統
win 8 
軟體版本
MS 2003 
閱讀權限
50 
性別
男 
註冊時間
2016-2-9 
最後登錄
2022-1-20 
10#
發表於 2021-1-12 16:57 | 只看該作者
回復 9# 准提部林
版主大大 : 您好!
沒有您的幫忙，這個公式如果是我自學~在會之前一定是先去撞牆
萬分感激您的耐心指導(鞠躬拜謝)~ ~ ~

TOP

        靜思自在 : 原諒別人就是善待自己。
12
返回列表 上一主題