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

[µo°Ý] ½Ð°Ý¦p¦óŪ¨úÀx¦s®æ¸Ìªºµüªº¦¸¼Æ?

[µo°Ý] ½Ð°Ý¦p¦óŪ¨úÀx¦s®æ¸Ìªºµüªº¦¸¼Æ?

¤j®a¦n¡A

·Q½Ð±Ð¤j®a¤@­Ó°ÝÃD¡G


¬ÛÃö»¡©ú¡G
(1) A4:B6¡A³o­Ó¬O¸ê®Æ
(2) E4,E5 ¬O·QŪ¨úªºµü
(3) F4,F5 ¬OŪ¨úªºµüªº¦¸¼Æ
(4) G4,G5 ¬O¸ÓŪ¨úªºµü¥X²{¦bÀx¦s®æA4~A6¹ïÀ³ªº­þ¤@­Ó­È(Q1,Q2,Q3)

§Æ±æ¯à§â(3)(4)¡A¥Î¤½¦¡¼g¥X¨Ó¡C(§Ú¦³¸Õ¤F¥Îcountif¡A¦ý¦n¹³¤£¯à¦¨¥\¹B§@><)

³Ì«á¡Aªþ¤WªþÀɵ¹±z°Ñ¦Ò¡G ¬¡­¶Ã¯2.zip (6.84 KB)

¦pªG¬Ý¤£À´°ÝÃDªº´y­z¥i¥H°Ý§Ú¡A¦A³Â·Ð¤j®a¨ó§UÀ°¦£¡AÁÂÁ¤j®a¡C

¥»©«³Ì«á¥Ñ samwang ©ó 2022-1-19 08:04 ½s¿è

¦^´_ 1# gaishutsusuru
¥u·|VBA¡A¶È¨Ñ°Ñ¦Ò¡AÁÂÁÂ
Sub test()
Dim Arr, Brr, a, xD, T$, i&, i1&
Brr = Range([e4], [e65536].End(3).Offset(, 2))
Arr = Range([a4], [b65536].End(3))
For i1 = 1 To UBound(Brr)
    T = Brr(i1, 1)
    For i = 1 To UBound(Arr)
        a = Split(Arr(i, 2), " ")
        For j = 0 To UBound(a)
            If InStr(a(j), ",") Then a(j) = Replace(a(j), ",", "")
            If InStr(a(j), ".") Then a(j) = Replace(a(j), ".", "")
            If a(j) = T Then
                Brr(i1, 2) = Brr(i1, 2) + 1
                Brr(i1, 3) = IIf(Brr(i1, 3) = "", Arr(i, 1), Brr(i1, 3) & "," & Arr(i, 1))
            End If
        Next
    Next
Next
Range("e4").Resize(UBound(Brr), 3) = Brr
End Sub

1.JPG (18.62 KB)

1.JPG

TOP

¦^´_ 2# samwang


±z¦n¡AÁÂÁ±zªºÀ°¦£¡A

¦ý§Ú¹ê»Ú´ú¤F¥H«á¡A¥X²{¦p¤U¹Ï¡G
(«ö¤@¦¸¥¨¶°)

(«ö¤G¦¸¥¨¶°)


¥t¤@³¡¥÷¡A
(1) ¹ê»Úªº¸ê®Æ¡A·|¶W¹LA4:B6ªº¤Tµ§¸ê®Æ(¦Ó¨Ò¤l¤¤¥u¦³¦CÁ|3µ§)
(2) ¹ê»Ú·QŪ¨úªºµü¡A¤]·|¶W¹LE4,E5 ªº¤G­Óµü(¦Ó¨Ò¤l¤¤¥u¦³¦CÁ|2­Ó)

(¦pªG¥i¥H¡AÁÙ¬O§Æ±æ¯à¦³¤½¦¡ªº¸Ñªk><)

ÁÂÁ±zªº©âªÅÀ°¦£¡C

TOP

Hello gaishutsusuru,

³o­Ó¬Ý°_¨Ó, «ÜÃø¥Î¤½¦¡ªº¤è¦¡¹F¦¨, ´N¦ppen¨Ó»¡, ¦³¦b¦P¤@­Ó¥y¤l¸Ì, ¥X²{¹L2¦¸,  ¦ý¥þ³£­ncount¥X¨Ó, ¨Ì§Úªº²z¸Ñ, ³o­Ó¥u¯à¥Î¥¨¶°ªº¤è¦¡¶i¦æ¤F!!
Thanks ~
·s¤â¤W¸ô¡A½Ð¦h¥]²[¡C

TOP

¦^´_ 4# ¼B¤j­G

Hello ¼B¤j­G¡A

ÁÂÁ±zªº¸Ñ»¡¡C

¦pªG°ÝÃD§ï¦¨¡G¦b¦P¤@­ÓÀx¦s®æ¸Ì¡AÁöµM¥X²{¹L2¦¸¥H¤W¡A¦ý¤´¥u­n­p¤J1¦¸´N¦n¡C
(¦]¦¹½d¨Ò¤¤¡AÀx¦s®æF4ªºpenªº­È´N¤£­nÅã¥Ü4¡A¦Ó¬OÅã¥Ü3´N¦n¤F¡C)

½Ð°Ý³o¼Ë¥i¥H¥Î¤½¦¡¹F¦¨¶Ü¡H ÁÂÁ±z¡C

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 6# hcm19522

hcm¤j¤j±z¦n¡A


ÁÂÁ±zªºÀ°¦£¡A¤½¦¡¥i¥H¥Î¡A¤Ó·PÁ±z¤F¡C

(¦ý¹ê´ú«á¦³­Ó¤p¤pªº°ÝÃD¡A¦]¬°¹ê»Ú¤W¸ê®Æ¥i¯à·|¤j©ó3µ§¡A©Ò¥HG3¦V¥k©Ôªº¸Ü¡A­n©Ô«Ü¦h­ÓÀx¦s®æ¤~¯à¦bG3¥¿½TÅã¥Ü¡C½Ð°Ý³o­Ó°ÝÃD¦³¤èªk¥i¥H¸Ñ¨M¶Ü?)

TOP

Hello gaishutsusuru,

§Úªº¤èªk¤ñ¸û¤gªk½m¿û, ¦ý~¤]¬O¹B¥Î¾î¶b¨Ó°µ¦U§Oªº§PÂ_, µM«á¦ASummary°_¨Ó.
Thanks ~

F4=COUNTIFS($B$4B$6,"* " & E4 & "*")
G4=IF(LEN(H4 & I4 & J4)>0,LEFT(H4 & I4 & J4,LEN(H4 & I4 & J4)-1),"")
H4=IF(COUNTIFS(INDIRECT("$B" & H$3 & "B" & H$3),"* " & $E4 & "*")>0,INDIRECT("$A" & H$3) & ",","")
I4=IF(COUNTIFS(INDIRECT("$B" & I$3 & "B" & I$3),"* " & $E4 & "*")>0,INDIRECT("$A" & I$3) & ",","")
J4==IF(COUNTIFS(INDIRECT("$B" & J$3 & "B" & J$3),"* " & $E4 & "*")>0,INDIRECT("$A" & J$3) & ",","")
·s¤â¤W¸ô¡A½Ð¦h¥]²[¡C

TOP

¦^´_ 7# gaishutsusuru
§Ú¬O2007ª© ¸û·sª©¥»¦³«ü¥O  µL¶·¥k©Ô
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 9# hcm19522

hcm¤j¤j±z¦n¡A

½Ð°Ý±z»¡ªº«ü¥O¬O¤°»ò©O¡H¦³°Ñ¦Òªº¸ê®Æ¶Ü¡H
ÁÂÁ±z

TOP

        ÀR«ä¦Û¦b : ¤@¥y·Å·xªº¸Ü¡A´N¹³©¹§O¤H¨­¤WÅx­»¤ô¡A¦Û¤v·|ªg¨ì¨â¤Tºw¡C
ªð¦^¦Cªí ¤W¤@¥DÃD