ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ·j´M+Âà¸m¶K¤Wªº¨ç¼Æ¤½¦¡

[µo°Ý] ·j´M+Âà¸m¶K¤Wªº¨ç¼Æ¤½¦¡


´ú¸ÕÀÉ¡J ·j´M+Âà¸m¶K¤Wªº¨ç¼Æ¤½¦¡.rar (29.37 KB)
»Ý¨Da¡J
·í¤u§@ªíAªºB2¡JB51=ªÅ¥Õ®É¡A«h±N¨ä¦P¦CAÄ檺­È¡AÂà¸m¶K¤W¤u§@ªíBªºB104¡JAX104
·í¤u§@ªíAªºC2¡JC51=ªÅ¥Õ®É¡A«h±N¨ä¦P¦CAÄ檺­È¡AÂà¸m¶K¤W¤u§@ªíBªºB105¡JAX105
¡J
¡J
·í¤u§@ªíAªºR2¡JR51=ªÅ¥Õ®É¡A«h±N¨ä¦P¦CAÄ檺­È¡AÂà¸m¶K¤W¤u§@ªíBªºB120¡JAX120

»Ý¨Db¡J
·í¤u§@ªíAªºU2¡JU51=ªÅ¥Õ®É¡A«h±N¨ä¦P¦CAÄ檺­È¡AÂà¸m¶K¤W¤u§@ªíBªºB153¡JAX153
·í¤u§@ªíAªºV2¡JV51=ªÅ¥Õ®É¡A«h±N¨ä¦P¦CAÄ檺­È¡AÂà¸m¶K¤W¤u§@ªíBªºB154¡JAX154
¡J
¡J
·í¤u§@ªíAªºAL2¡JAL51=ªÅ¥Õ®É¡A«h±N¨ä¦P¦CAÄ檺­È¡AÂà¸m¶K¤W¤u§@ªíBªºB170¡JAX170

»Ý¨Dc¡J
·í¤u§@ªíAªºAO2¡JAO51=ªÅ¥Õ®É¡A«h±N¨ä¦P¦CAÄ檺­È¡AÂà¸m¶K¤W¤u§@ªíBªºB202¡JAX202
·í¤u§@ªíAªºAP2¡JAP51=ªÅ¥Õ®É¡A«h±N¨ä¦P¦CAÄ檺­È¡AÂà¸m¶K¤W¤u§@ªíBªºB203¡JAX203
¡J
¡J
·í¤u§@ªíAªºBE2¡JBE51=ªÅ¥Õ®É¡A«h±N¨ä¦P¦CAÄ檺­È¡AÂà¸m¶K¤W¤u§@ªíBªºB218¡JAX218

»Ý¨Dd¡J
·í¤u§@ªíAªºBH2¡JBH51=ªÅ¥Õ®É¡A«h±N¨ä¦P¦CAÄ檺­È¡AÂà¸m¶K¤W¤u§@ªíBªºB251¡JAX251
·í¤u§@ªíAªºBI2¡JBI51=ªÅ¥Õ®É¡A«h±N¨ä¦P¦CAÄ檺­È¡AÂà¸m¶K¤W¤u§@ªíBªºB252¡JAX252
¡J
¡J
·í¤u§@ªíAªºBY2¡JBY51=ªÅ¥Õ®É¡A«h±N¨ä¦P¦CAÄ檺­È¡AÂà¸m¶K¤W¤u§@ªíBªºB268¡JAX268

½Ð°Ý¡J¤u§@ªíB! B104,B153,B202,251ªº¨ç¼Æ¤½¦¡À³¦p¦ó½s¼g¡H
ÁÂÁ¡I

¥»©«³Ì«á¥Ñ luhpro ©ó 2021-1-6 23:09 ½s¿è
´ú¸ÕÀÉ¡J
»Ý¨Da¡J
·í¤u§@ªíAªºB2¡JB51=ªÅ¥Õ®É¡A«h±N¨ä¦P¦CAÄ檺­È¡AÂà¸m¶K¤W¤u§@ªíBªºB104¡JAX104
·í¤u ...
ziv976688 µoªí©ó 2021-1-5 18:22

Àx¦s®æ¤½¦¡§Ú©l²×¤£¬O¬Ý±o«ÜÀ´,
¨Ì¾Ú§Aµu®ø®§©Ò»¡§Ú³o¸Ì´£¨ÑVBAµ{¦¡ªº¸Ñ¨M¤è¦¡,
¸Õ¸Õ¬Ý.
  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
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-1-7 00:58 ½s¿è

¦^´_ 2# luhpro
´ú¸ÕÀÉ : ·j´M+Âà¸m¶K¤W(VBA).rar (35.85 KB)
luhpro¤j¤j:±z¦n!
·PÁ¦^ÂÐ

¤£¦n·N«ä¡Aµª®×³£¤£¹ï¡A¦C251:¦C268¨S¦³µª®×¡C
·íA!B2:B51ªºÀx¦s®æ(¨ä¾l...A!C2:C51....Ãþ±À)=""""®É¡A¤~±N¨ä¦P¦Cªº$A$2A$51­È¾î¸m¨ìB!ªº«ü©w¦C¡A
¤£ª¾¹D¥H0©M1§@§P§O ¡A·|¤£·|¦³bug?

³Â·Ð±z¦A½ç¥¿¡CÁÂÁ±z

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-1-7 02:08 ½s¿è

¦^´_ 2# luhpro
Àx¦s®æ¤½¦¡§Ú©l²×¤£¬O¬Ý±o«ÜÀ´,

¤£¦n·N«ä¡A½d¨ÒÀɪºB¤u§@ªí~¥H¤H¤u¶ñ¤Jªº¼Æ¦r¦³«Ü¦hµ§»~(EX¡J106¦C¤Ö¤F­Ó2¡A§YD106=2¡A¨ä¾l¶¶°h1®æ¡F
¥t107¦C©M109¦C³£¤Ö¤F0¡A§YB107=0¡A¨ä¾l¶¶°h1®æ©MB109=0¡A¨ä¾l¶¶°h1®æ)~©|½Ð¨£½Ì^^"

¦A±NB!¦U¦CÀx¦s®æ¼Æ¦rªº»Ý¨DÅÞ¿è¡A¥H¹ê¨Ò°µ»¡©ú¡J
EX¡J
·íA!ªºB2=""®É¡A«h±NA!ªºA2=0Åã¥Ü¦bB!ªºB104
·íA!ªºB20:B51=""®É¡A«h±NA!ªºA20:A51=18~49Åã¥Ü¦bB!ªºC104:AH104

·íA!ªºC2:C3=""®É¡A«h±NA!ªºA2:A3=0~1Åã¥Ü¦bB!ªºB105:C105
·íA!ªºC28:C51=""®É¡A«h±NA!ªºA28:A51=26~49Åã¥Ü¦bB!ªºD105:AA105

·íA!ªºD2:D5=""®É¡A«h±NA!ªºA2:A5=0~3Åã¥Ü¦bB!ªºB106:E106
·íA!ªºD8=""®É¡A«h±NA!ªºA8 =6Åã¥Ü¦bB!ªºF106
·íA!ªºD32=""®É¡A«h±NA!ªºA32 =30Åã¥Ü¦bB!ªºG106
·íA!ªºD34=""®É¡A«h±NA!ªºA34=32Åã¥Ü¦bB!ªºH106
·íA!ªºD36:D51=""®É¡A«h±NA!ªºA36:A51=34~49Åã¥Ü¦bB!ªºI106:X106

·íA!ªºE2:E5=""®É¡A«h±NA!ªºA2:A5=0~3Åã¥Ü¦bB!ªºB107:E107
·íA!ªºE7:E11=""®É¡A«h±NA!ªºA7:A11=5~9Åã¥Ü¦bB!ªºF107:J107
·íA!ªºE38:E51=""®É¡A«h±NA!ªºA38:A51=36~49Åã¥Ü¦bB!ªºK107:X107
¡J
¡J
¨ä¾l¡K.¦P²zÃþ±À¡C

TOP

¥»©«³Ì«á¥Ñ ziv976688 ©ó 2021-1-7 14:01 ½s¿è

¥H¨ç¼Æ¤½¦¡¦Ó¨¥~
§Úªº°ÝÃD¬OTRANSPOSE()¨ç¼Æ¡A§Ú¤£·|¥Î
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)))
¦ý¦p¦óÅý"¤½¦¡©Ò²£¥Íªº­È"ª½±µ¾î¸m¦bB104:AX105?
§Ú¤@ª½¤£¯à½s¼g¦¨¥\¡C
½Ð¦U¦ì¤j¤j¤£§[«ü±Ð¬°Ã«!ÁÂÁÂ

TOP

B104/°}¦C
=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)))

¨ä¥¦¦Û¦æµeµe~~

TOP

¦^´_ 6# ­ã´£³¡ªL
ª©¥D¤j¤j : ±z¦n !
ÁÂÁ±z¥X¤â¬Û§U©M«ü¾É~·P®¦
¦pªG¨S¦³±z~¤é¤l«ç»ò¹L?~~~~~~~

TOP

¦^´_ 6# ­ã´£³¡ªL

´ú¸ÕÀÉ : ¤½¦¡=''''­×§ï¬°¤j©ó10.rar (60 KB)
B104/°}¦C
=IF(COLUMN(A$1)>SUM(N(INDEX(A!$B$2R$51,,ROW($A1))="")),"",SMALL(IF(INDEX(A!$B$2R$51,, ...
­ã´£³¡ªL µoªí©ó 2021-1-7 20:49

ª©¥D¤j¤j :
¤£¦n·N«ä¡A¦pªG·Q­n±NA!$B$2:$R$51=""ªº¦P¦CAÄæ­ÈÂà¸m¨ìB104ªº¶Q¸Ñ¤½¦¡
§ï¬°
±NA!$B$2:$R$51>10ªº¦P¦CAÄæ­ÈÂà¸m¨ìB104

½Ð°Ý : B104ªº¤½¦¡À³¸Ó¦p¦ó½s­×?
·q½Ð±z½ç±Ð¬°Ã«¡CÁÂÁ±z^^

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# ­ã´£³¡ªL
ª©¥D¤j¤j : ±z¦n!
¨S¦³±zªºÀ°¦£¡A³o­Ó¤½¦¡¦pªG¬O§Ú¦Û¾Ç~¦b·|¤§«e¤@©w¬O¥ý¥h¼²Àð
¸U¤À·P¿E±zªº­@¤ß«ü¾É(Áù°`«ôÁÂ)~ ~ ~

TOP

        ÀR«ä¦Û¦b : ­ì½Ì§O¤H´N¬Oµ½«Ý¦Û¤v¡C
ªð¦^¦Cªí ¤W¤@¥DÃD