¹Ù·Î°¡±â

Áñ°Üã±â 

[ÄÄÇ»ÅÍ/ÀÎÅͳÝ]

[¿¢¼¿] ÃßÃâÇؼ­ Ç¥½ÃÇÏ´Â ¸ÅÅ©·Î°¡ ±Ã±ÝÇÕ´Ï´Ù

rank Æä·¯¸® 2018-08-10 (±Ý) 10:54 Á¶È¸ : 1211
ĸó.JPG


¸ÅÅ©·Î°¡ ±Ã±ÝÇÕ´Ï´Ù ¤Ð
´ÜÃ߸¦ ´©¸¦¶§¸¶´Ù »óÇ°¸í¿¡ ½áÁ®ÀÖ´Â »óÇ°À» Àç°í¿¡¼­ ã¾Æ¼­ ³ªÅ¸³ª°Ô ÇÏ°í ½Í½À´Ï´Ù.
¹öÆ°À» ´©¸¦¶§¸¶´Ù Àç°í ¼ö·®¿¡¼­ ÃßÃâÇؼ­ Ç¥½ÃÇÏ°í ½ÍÀºµ¥ Àß ¾ÈµÇ³×¿ä ¤Ð
¿äûÀÚ°¡ ÀÚ½ÅÀÇ 500Æ÷ÀÎÆ®¸¦ °É¾ú½À´Ï´Ù. ´äº¯ÀÌ Ã¤ÅõǸé 250Æ÷ÀÎÆ®¸¦ µå¸³´Ï´Ù.
´ñ±Û 6°³ ´ñ±Û¾²±â
rank³ªµµÀ͸í 2018-08-10 (±Ý) 13:20
B2 °ªÀ» ã¾Æ H¿¡ »Ñ·ÁÁÖ´Â VBA
Lookup Table ÀÇ ¹üÀ§°¡ Å©¸é Á¤È®È÷ Á¦¾î¸¦ ÇؾßÇÏÁö¸¸ ¸î°³ ¾ÈµÇ´Ï ±¸¿ªÀ¸·Î Á¤ÀÇ
Á¤È®ÇÑ Á¦¾î´Â ÀÌ·¸°Ô..
For Each cel In Range("E2", "E" & Cells(Rows.Count, 5).End(xlUp).Row ' Lookup Table

Sub Lookup()
Range("H2", "I" & Rows.Count).Clear ' °á°ú°ª ºñ¿ì±â
FindString = [B2] ' ãÀ» ¹®ÀÚ
For Each cel In Range("E2").CurrentRegion ' Lookup Table
    If InStr(cel.Value, FindString) Then ' ãÀ» ¹®ÀÚ¸¦ Æ÷ÇÔÇϸé
        Cells(Rows.Count, 8).End(xlUp).Offset(1, 0) = cel.Value ' ÇöÀç °ªÀ» HÀÇ ¸Ç ¸¶Áö¸·¿¡ º¹»ç
        Cells(Rows.Count, 9).End(xlUp).Offset(1, 0) = cel.Offset(0, 1).Value ' ¼ö·® º¹»ç
    End If
Next
End Sub
     
       
rankÆä·¯¸® ±Û¾´ÀÌ 2018-08-10 (±Ý) 14:39
°¨»çÇÕ´Ï´Ù!! ±×·±µ¥ Áú¹®ÀÌ ÀÖ½À´Ï´Ù.

Á¤È®ÇÑ  Á¦¾î¸¦ À§ÇÑ ±¸¹®
For Each cel In Range("E2", "E" & Cells(Rows.Count, 5).End(xlUp).Row ' Lookup Table

ÀÌ°É For Each cel In Range("E2").CurrentRegion ' Lookup Table  ¶û ġȯ Çß´õ´Ï

ÄÄÆÄÀÏ ¿À·ù ÇÊ¿äÇÑ¿ä¼Ò:¸ñ·Ï ±¸ºÐ¼± ¶Ç´Â )    <-- ÀÌ·¸°Ô ³ª¿À´Âµ¥ ÀÌ°Ç ¿Ö ±×·±°É±î¿ä?

±×¸®°í Áö±Ý ÇöÀç ¹èÃß(´ã¾ç) ¼ö·®¸¸ ³ª¿À´Âµ¥ ÀÌ·¸°Ô 2ÁÙ¸¸ÃßÃâµÇ¾î ³ª¿À´Âµ¥ 4ÁÙÀÌ ÃßÃâµÇ¼­ ³ª¿À°Ô ÇÒ¼ø ¾øÀ»±î¿ä? ½ÇÁ¦ Á¶°ÇÀº ¼ö·® ¸»°í 2°¡Áö ¿É¼ÇÀÌ ´õ Àְŵç¿ä
rank³ªµµÀ͸í 2018-08-10 (±Ý) 14:44
¸¶Áö¸· ¹®ÀÚ°¡ ºüÁ³³×¿ä.
For Each cel In Range("E2", "E" & Cells(Rows.Count, 5).End(xlUp).Row)

2ÁÙ ÃßÃâ?  4ÁÙ ÃßÃâ? À̶ó ÇϽÉÀº///

À§ ÄÚµå·Î ÀÀ¿ëÀÌ ¾î·Á¿ì¸é ºñ½ÁÇÑ ¹®¼­, ¶Ç´Â Áö±Ý ÀÛ¾÷ ÁßÀÎ ¹®¼­¸¦ ´ñ±Û¿¡ ÷ºÎÇØ º¸¼¼¿ä.

Cells(Rows.Count, 8) Àº H ¸¦ ¸»ÇÏ°í
Cells(Rows.Count, 9) ´Â I ¸¦ ¸»ÇÕ´Ï´Ù.

¼¿À§Ä¡.Offset(1, 0) Àº ¼¿À§Ä¡¿¡¼­ ÇÑÄ­ ¾Æ·¡
¼¿À§Ä¡.Offset(0, 1) Àº ¼¿À§Ä¡¿¡¼­ ÇÑÄ­ ¿À¸¥ÂÊÀÔ´Ï´Ù.
     
       
rankÆä·¯¸® ±Û¾´ÀÌ 2018-08-10 (±Ý) 15:05
ÇØ°áÇß½À´Ï´Ù.!!!

Cells(Rows.Count, 12).End(xlUp).Offset(0, 1) = cel.Offset(0, 2).Value
ÀÌ·¸°Ô ÇÑÁÙ ³Ö¾îÁÖ´Ï ¿·¿¡ ÁÙ±îÁö °°ÀÌ Ç¥½ÃµÇ³×¿ä!
°Ü¿ì ÀÀ¿ëÇß½À´Ï´Ù.!
--------------------

Á˼ÛÇѵ¥ Çϳª¸¸´õ ¿©ÂÞ¾îºÁµµµÉ±î¿ä?¤Ð

FindString = [B2] ' ãÀ» ¹®ÀÚ    ÀÌ°Ô B2¿¡ ÀÖ´Â ±ÛÀÚ¸¦ ã´Â°Å¶ó°í ÇÑ´Ù¸é

Á¦°¡ ¼±ÅÃÇÑ ¼¿¿¡ Àִ¹®ÀÚ¸¦ ã´Â °Ç ¾î¶»°Ô ÇϸéµÉ±î¿ä?
          
            
rankÆä·¯¸® ±Û¾´ÀÌ 2018-08-10 (±Ý) 15:19
¤Ñ¤Ñ¤Ñ¤Ñ¤Ñ¤Ñ¤Ñ¤Ñ¤Ñ¤Ñ¤Ñ¤Ñ¤Ñ¤Ñ¤ÑÀ̰͵µ ÇØ°áÇß½À´Ï´Ù!

B2 ¿¡ Selection À» ³Ö¾îÁÖ¸éµÇ³×¿ä!!!
rank³ªµµÀ͸í 2018-08-10 (±Ý) 15:20
¹®¼­°¡ ¾øÀ¸´Ï ¸ð¸£°ÚÁö¸¸ ÀÌ·¸°Ô ÇØ¾ß Çϴ°Š¾Æ´Ñ°¡¿ä?
Cells(Rows.Count, 12).End(xlUp).Offset(0, 2) = cel.Offset(0, 2).Value

Á» ´õ ÀÀ¿ëÇØ º¸ÁÒ
https://goo.gl/MpWpGH

´äÀº ¸¹ÀÌ ÀÖÁö¸¸ »óȲ¿¡ µû¶ó ÇÊ¿äÇÑ ´ë·Î »ç¿ëÇÕ´Ï´Ù.
FindString = Selection.Value
FindString = ActiveCell.Value

¹øÈ£ Á¦¸ñ ±Û¾´ÀÌ »óÅ Æ÷ÀÎÆ® ³¯Â¥ Á¶È¸
[°øÁö]  ¡Ø Áö½ÄiN °Ô½ÃÆÇ ÀÌ¿ë¾È³» rankeToLAND
0 03-28
[°øÁö]  ¡Ø Å䷻Ʈ»çÀÌÆ®Áú¹®,ÀúÀÛ±Ç ÀÚ·á¿äû ±ÝÁö rankeToLAND
0 08-25
[Áß°íÂ÷»ó´ã]  ¹Ù·Î ¹Ø¿¡±Û¾´ÀÌ Àä.. À͸í
129292 [°Ç°­/ÀÇÇÐ]  ¤· ÀÓÇöõÆ® ±Ý¾× ¾î´ÀÁ¤µµ¸é Àû´çÇÒ±î¿ä ??? (5) rankÁ¤»ó¿¡¼­¸¸³³½Ã´Ù
369 01-10 256
129291 [Ãë¹Ì/»ýÈ°]  Ã³À½À¸·Î ¿ø·ë ¿ù¼¼ ±¸ÇÏ·Á´Âµ¥ µµ¿ÍÁÖ¼¼¿ä (2) rank¹Ù²ô·¯³ª°¡¹ö¸®°í
1000 01-10 259
129290 [ÄÄÇ»ÅÍ/ÀÎÅͳÝ]  [Áú¹®]Åë½ÅÁÖ¿¡ ±¤ÄÉÀ̺í´ÜÀÚÇÔ ºñ¹Ù¶÷¿¡ ³ëÃâµÇ¿¡µµ ÀÌ»ó¾øÀ»±î¿ä? (1) À̹ÌÁö rank¹ü¾ß
500 01-10 181
129289 [Ãë¹Ì/»ýÈ°]  Æ÷ÀåÀÌ»ç ÀÚÁÖÇغ¸½Ã°Å³ª À߾ƽôºР°è½Å°¡¿ä?.txt (4) rank¿©Àڿ;ÆÀ̴³öÁà
5000 01-09 246
129288 [Ãë¹Ì/»ýÈ°]  ¾È±ñ¸¶´Ã º¸°ü¹ý (1) rankÇÔ³ÄÇÔ³ÄÇÔ
2000 01-09 256
129287 [±âŸ]  ÇÏÀÌÆнº ´Ü¸»±â ÃßõºÎŹµå·Á¿ä rankpobtong
1000 01-09 165
129286 [TV/¿µ»ó]  ÀÌ °É±×·ì ´©±º°¡¿ä? (2) À̹ÌÁö rankÀçÁîÀè·¡ºø
500 01-08 1446
129285 [±âŸ]  ´©Àü È®ÀÎ ÇÏ´Â ¹æ¹ý Á» ¾Ë·ÁÁÖ¼¼¿ä. (1) À̹ÌÁö rank¸ÞÀÌÆ®·±
500 01-08 206
129284 [±âŸ]  ´ã±ÝÁÖ À̹ÌÁö rankball
200 01-07 187
129283 [±³À°/Çй®]  ÄÄÈ°2±Þ ÇÔ¼ö ÀÌÇØ Çϱ⠽±°Ô ¾Ë·ÁÁֽǺÐ.. (4) rank±×¸®¿î±×³¯
500 01-06 286
129282 [°æÁ¦/ÀçÅ×Å©]  1500¸¸¿ø Á¤µµ »ý°å½À´Ï´Ù! (2) rankpagua
300 01-06 428
129281 [±âŸ]  ½Çºñ º¸Çè °¡ÀԽÿ¡ º¸Áö ¸øÇÑ º´¸í ÀÌ·ÂÀÌ ÀÖÀ»¶§ ¾î¶»°Ô ÇØ¾ß µÇ³ª¿ä?? (1) rank³ª´Â¿©±âÀÖ´Ù
200 01-04 216
129280 [Ãë¹Ì/»ýÈ°]  ¼ö¿ø Àΰ赿¿¡ ¹«·á ÁÖÂ÷ÇÒ¼ö ÀÖ´Â °÷ Ãßõ ºÎŹµå¸³´Ï´Ù (1) rank¼¾Å©¸®
300 01-04 330
129279 [À½¾Ç]  ³ë·¡ Çϳª ã½À´Ï´Ù. (2) rankiodine
500 01-04 198
129278 [°æÁ¦/ÀçÅ×Å©]  ¹Ì±¹ÀÇ ±Ý¸®ÀÎÇÏ¿¡ ´ëÇØ ¾Ë°í ½Í½À´Ï´Ù.. (3) rank·°Å°³Ñ¹ö¼¼ºì
300 01-04 271
129277 [Ãë¹Ì/»ýÈ°]  °¡±Û¾× ´ë¿ë·® ´ëüǰ ÀÖÀ»±î¿ä ??? (6) rankÁ¤»ó¿¡¼­¸¸³³½Ã´Ù
369 01-04 243
129276 [±âŸ]  ¼öÀÔ ¾ø´Â »óÅ·Π´ãº¸´ëÃâ ¸ø ¹Þ³ª¿ä?.txt (4) rank¿©Àڿ;ÆÀ̴³öÁà
5000 01-03 286
129275 [±âŸ]  ÀÌ°Å ¾îµð¼­ Æijª¿ä? (3) rank¿À·»Áö¸Ç
300 01-03 302
129274 [±âŸ]  ±¸µÎ Áõ°Å , ¹®¼­ Áõ°Å ¾î¶²°Ô ´õ È¿·ÂÀÖ³ª¿ä?.txt (3) rank¿©Àڿ;ÆÀ̴³öÁà
5000 01-02 255
129273 [ÀüÀÚ±â±â]  Àüµî Àü±â¼± »ç¿ëÇصµ ±¦ÂúÀ»±î¿ä? (5) rank±×³àÀǺ°
200 01-02 298
129272 [±âŸ]  ½Ãºñ·® °è»ê¹®Á¦ µµ¿ÍÁֽǼöÀÖÀ¸½Ç±î¿ä¤Ì¤Ì (4) rank¿­½ÉÀÎÆÒ´õ
200 01-01 230
129271 [Ãë¹Ì/»ýÈ°]  ÀÌ ²É À̸§ÀÌ ¹º°¡¿ä? ¾îµð¼­ »ì¼ö ÀÖ´ÂÁö ±Ã±ÝÇÕ´Ï´Ù. (1) À̹ÌÁö rank¼¾Å©¸®
300 12-31 267
129270 [À½¾Ç]  ÆËÀ½¾ÇÀ» ã´Âµ¥ 2015³â (À¯·Â) ¶Ç´Â 2018³â ¶ß´ø °î (4) rank´ëÁ¶¼±¾ð´Ï
586 12-30 180
129269 [±âŸ]  ¾ÆÆÄÆ® ´ãº¸ ´ëÃâ ¹®ÀÇ µå¸³´Ï´Ù. .txt (2) rank¿©Àڿ;ÆÀ̴³öÁà
2000 12-30 230
129268 [TV/¿µ»ó]  Àü°ÝzÀÛÀü (Knight Rider) º¼ ¼ö Àִ°÷? (3) rankº£ÁöŸ¸Æ½º
2000 12-30 356
129267 [Ãë¹Ì/»ýÈ°]  Ãâ±¹±ÝÁöÀÚ °øÇ׿¡¼­ ½Å°íµé¾î°¨ ??? (2) rank´ëÁ¶¼±¾ð´Ï
369 12-29 282
129266 [ÄÄÇ»ÅÍ/ÀÎÅͳÝ]  ½Ã³î·ÎÁö ¿ÜºÎÁ¢¼Ó (2) rankÁÒ¸®Æþ±¹¹°
200 12-28 255
129265 [±³À°/Çй®]  °í3 ´ëÇÐ ¼±Åà °í¹Î.txt (4) rankkburo
300 12-27 301
129264 [ÄÄÇ»ÅÍ/ÀÎÅͳÝ]  ÀÏ·¯½ºÆ®·¹ÀÌÅÍ¿¡¼­ °£´ÜÇÑ ±â´É Çϳª Áú¹®ÇÕ´Ï´Ù. (2) rankÁִܹ«
200 12-26 257
129263 [°Ç°­/ÀÇÇÐ]  ¹ÐÅ©¾¾½½ µÎ Á¦Ç° Áß ¾î¶² °Ô ´õ ÁÁÀº°É±î¿ä? (3) rank³ªº°
5000 12-26 262
129262 [±âŸ]  ¿ò© Ãâó ¾Ë·ÁÁÖ¼¼¿ä! (3) rank³×¹ös2
200 12-26 299
129261 [°Ç°­/ÀÇÇÐ]  º´¿ø ¾à±¹ ¿µ¼öÁõÀ¸·Î ½Çºñº¸Çèºñ ȯ±Þ ¹ÞÀ» ¼ö ÀÖ³ª¿ä?? (4) rankDown
3000 12-25 309
129260 [±âŸ]  ´ñ±Û¿¡ Áú¹® ÀÖ½À´Ï´Ù. ±Û°ú »çÁøÀÌ ¾È¿Ã¶ó °¡³×¿ä. (3) rank¸ÞÀÌÆ®·±
1500 12-25 212
129259 [ÄÄÇ»ÅÍ/ÀÎÅͳÝ]  È¤½Ã ¹ÙÀ̵Πºñ·Î±×ÀÎÀ¸·Î ´Ù¿î °¡´ÉÇÑ ¹æ¹ýÀÌ ÀÖ³ª¿ä? (1) rankŽ»ö
4000 12-24 269
129258 [Ãë¹Ì/»ýÈ°]  Áö°© ¾î¶² Á¦Ç°ÀÎÁö Áú¹®ÀÔ´Ï´Ù. (1) rank¹¦ÀÌ
500 12-23 207
129257 [°Ç°­/ÀÇÇÐ]  ´ÏÄÚƾÀÌ Ä¡°ú¿¡¼­´Â º¸À̳ª¿ä ???? (7) rank´ëÁ¶¼±¾ð´Ï
586 12-22 317
129256 [±âŸ]  Æ®À§ÅÍ°¡ÀÌ»óÇØ¿ä µµ¿ÍÁÖ¼¼¿ä rank³ª°í°Åµµ
300 12-21 216
129255 [ÄÄÇ»ÅÍ/ÀÎÅͳÝ]  ÈÞ´ëÆùÈ­¸é pc·Î ¿¬°á. (2) rankÇÔ³ÄÇÔ³ÄÇÔ
2000 12-19 357
129254 [±âŸ]  ´ë±¸³ª °æºÏÂÊ¿¡ ¿î¼¼³ª »çÁÖ Àß º¸´Â Á¡Áý ÀÖ³ª¿ä? (1) rank¾ß¿Ë±³ÁÖ
1000 12-19 230
129253 [ÄÄÇ»ÅÍ/ÀÎÅͳÝ]  À̹ÌÁö ¿ë·® ÁÙÀ̴¹ý (1) rank¹Ð¸®Ä«µåȯŸ
200 12-19 238

Áú¹®°ú´äº¯ ¿ù°£ ÃÖ´ÙäÅà ¿ì¼ö´äº¯È¸¿ø

  • rank±×±îÀ̲¨¹¹¶ó°í äÅô亯¼ö (11)
  • rank¿©Àڿ;ÆÀ̴³öÁà äÅô亯¼ö (6)
  • rankÀÌÈ­¿¡¿ù´ãÇÏ°í äÅô亯¼ö (3)
  • rankdasari äÅô亯¼ö (3)
  • rank¹ÐÁö¸¶ äÅô亯¼ö (2)
  • rank35mm äÅô亯¼ö (1)
  • rankNarxia äÅô亯¼ö (1)
  • rankÀû¿ì»ï äÅô亯¼ö (1)
  • rankÃÖ´ë8ÀÚ Ã¤Åô亯¼ö (1)
    óÀ½  1  2  3  4  5  6  7  8  9  10  ´ÙÀ½
    ¸Ç À§·Î
    ¸Ç ¾Æ·¡·Î

    °øÀ¯Çϱâ

    ÀÌÅä·£µå ·Î°í

    °èÁ¤ ã±â ȸ¿ø°¡ÀÔ
    ¼Ò¼È·Î±×ÀÎ