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

[µo°Ý] ²Õ´ªí

[µo°Ý] ²Õ´ªí

¤j¤j­Ì¦n,

1. ³o¬O¤@¥÷²Õ´ªí,¥H"·¨´¹´¹"¬°¥D,¦bDÄ榳¤@­Ó"¸u¶¥",°²³]AAA¬O³Ì§C¶¥,BBB«h¬O¤É¤@¶¥¡A¥H¦¹Ãþ±À,¸u¶¥·|ÀHµÛ·~ÁZ¦Ó¦³¤É­°,¦]¬°¸u¶¥¤É­°¤º®e¤ñ¸û½ÆÂø¡A©Ò¥H§Ú¤£¥Î¤½¦¡¡A¦Óª½±µ¦bÄæ¦ì¥Î¤â°Êkey-in

2. ·¨´¹´¹«á­±ªº¼Æ¦r§Ç¸¹,¥u­n¬O§Ç¸¹¤£¦Pªº,§â¥¦·í¦¨¤£¦Pªº¦W¦r,¤£¥Î²z·|

3. BÄæ¦ì·Q¥[¤J¸u¶¥ªº¦WºÙ¡A¨C·íDÄ榳¤@­Ó·s¸u¶¥¥X²{,BÄ檺Àx¦s®æ´N¥H·s¥X²{ªº¸u¶¥¥[¤W"·¨´¹´¹",ª½¨ì¤U¤@­Ó·sªº¸u¶¥¥X²{¤~·|¨ú¥N¥¦¡A­Y¬O"µL",ªí¥Ü§¹¥þµL¸u¶¥,´N¥X²{"·¨´¹´¹"§Y¥i.

¥H¤W,½Ð°Ý¦p¦ó¦bBÄæ¦ì¥Î¤½¦¡±a¥XDÄ檺¸u¶¥?

²Õ´ªí.rar (11.35 KB)

D2=IF(LEFT(B2,3)=E2,"µL",LEFT(B2,3))

§A­nªº¬O³o¼Ë¶Ü?
excel¾Ç²ß¤¤!¿v¥X¹Ú·Q
https://www.youtube.com/channel/UCPAiRNsHX3k_omp0YDgfDiA

TOP

¦^´_ 2# kkooo15

¶Ù,

³o¼Ë¥X¨Óªºµª®×¤£¥¿½T¡A§Ú­nªº¬OBÄæ¥H¤½¦¡±a¥Xªºµ²ªG,»PBÄ檺¤å¦r¤@¼Ë(²{¦bBÄæ¬O¨S¤½¦¡ªº)¡C

TOP

¥»©«³Ì«á¥Ñ kkooo15 ©ó 2016-5-12 20:24 ½s¿è

¦^´_ 3# PJChen

=IF(D2="µL",E2,D2&E2)

OR

=IF(D5="µL",E5,D5&LEFT(E5,3))

DÄæ­n¶ñº¡
excel¾Ç²ß¤¤!¿v¥X¹Ú·Q
https://www.youtube.com/channel/UCPAiRNsHX3k_omp0YDgfDiA

TOP

¦^´_ 4# kkooo15

±z¦n,

§Ú­nªºBÄæµ²ªG¬O³o¼Ëªº¡G
§õ¹Å³ß
·¨´¹´¹
·¨´¹´¹
·¨´¹´¹
·¨´¹´¹
AAA·¨´¹´¹
AAA·¨´¹´¹
AAA·¨´¹´¹
AAA·¨´¹´¹
AAA·¨´¹´¹
AAA·¨´¹´¹
AAA·¨´¹´¹
AAA·¨´¹´¹
AAA·¨´¹´¹
AAA·¨´¹´¹
AAA·¨´¹´¹
AAA·¨´¹´¹
BBB·¨´¹´¹
BBB·¨´¹´¹
BBB·¨´¹´¹
BBB·¨´¹´¹
BBB·¨´¹´¹
BBB·¨´¹´¹
BBB·¨´¹´¹
BBB·¨´¹´¹
BBB·¨´¹´¹
CCC·¨´¹´¹
CCC·¨´¹´¹
CCC·¨´¹´¹
CCC·¨´¹´¹
CCC·¨´¹´¹
CCC·¨´¹´¹
AAA·¨´¹´¹
AAA·¨´¹´¹
AAA·¨´¹´¹
AAA·¨´¹´¹
·¨´¹´¹
·¨´¹´¹
·¨´¹´¹
·¨´¹´¹

2­Ó¤½¦¡±a¥X¨Óªº³£¤£¹ï¡AÁÂÁ§A¤F¡I

TOP

¢°¡D¥H¡e·¨´¹´¹¡f¬°¥D¡A¬°¦ó¢Ð¢±¬O¡e§õ¹Å³ß¡f¡H
¢±¡D¢Ò¢²¢·¬°¡eµL¡f¡A¬°¦ó¨ú¡e·¨´¹´¹¡f¡A¦Ó¤£¬O¡e·¨´¹´¹12¡f¡H

TOP

a2 =IF(LOOKUP("¼_",D$2:D2)="µL","",LOOKUP("¼_",D$2:D2))&LEFT(E2,3)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 7# ML089

ÁÂÁ¤j¤j¡A
±zªºµª®×¥¿½T¡I
§Ú·Q§ó¶i¤@¨B½Ð±Ð....
²Õ´ªínew.rar (14.81 KB)
1) ¥HBÄ檺¤H¦W(¤@©w­n¨Ì¤H¦W¡A¦]¬°¤é«á¥i¯à¦P®É¦³¦h­Ó¤H¦W¥X²{)¬°­pºâHÄæª÷ÃBªº¨Ì¾Ú¡A§@¬°¸u¶¥ªº®Ê¤É
2) ·íHÄ檺ª÷ÃB¹F¨ì¸u¶¥®Ê¤Éªºª÷ÃB®É¡A¦Û°Ê¦bDÄæ¥X²{¬Û¹ïÀ³ªº¸u¶¥
3) ¹F¦¨¥H¤U·~ÁZ­n¨D´N¦³¬Û¹ïÀ³ªº¸u¶¥ (ª÷ÃB¼Ð¥Ü¦b"¸u¶¥sheet¤¤")

¸u¶¥        ¹F¦¨·~ÁZ
ÂÅÄ_¸g²z        $250,000
¬õÄ_°ÆÁ`        $1,000,000
¤@Æp°ÏÁ`        $1,500,000
¤GÆp°ÏÁ`        $1,750,000
¤TÆp°ÏÁ`        $2,000,000
¥|Æp°ÏÁ`        $2,250,000
¤­Æp°ÏÁ`        $2,500,000
¤»Æp°ÏÁ`        $2,750,000
¤CÆp°ÏÁ`        $3,000,000
¤KÆp°ÏÁ`        $3,250,000
4) ½Ð°Ý¦p¦óÅýDÄæ¦Û°Êshow¥X³o¨Ç¸u¶¥¡H
¸u¶¥ (DÄæ)
µL
µL
µL
µL
µL
ÂÅÄ_¸g²z
ÂÅÄ_¸g²z
ÂÅÄ_¸g²z
ÂÅÄ_¸g²z
ÂÅÄ_¸g²z
ÂÅÄ_¸g²z
ÂÅÄ_¸g²z
ÂÅÄ_¸g²z
ÂÅÄ_¸g²z
ÂÅÄ_¸g²z
ÂÅÄ_¸g²z
ÂÅÄ_¸g²z
ÂÅÄ_¸g²z
ÂÅÄ_¸g²z
ÂÅÄ_¸g²z
¬õÄ_°ÆÁ`
¬õÄ_°ÆÁ`
¬õÄ_°ÆÁ`
¬õÄ_°ÆÁ`
¬õÄ_°ÆÁ`
¬õÄ_°ÆÁ`
¬õÄ_°ÆÁ`
¬õÄ_°ÆÁ`
¬õÄ_°ÆÁ`
¬õÄ_°ÆÁ`
¤@Æp°ÏÁ`
¤@Æp°ÏÁ`
¤@Æp°ÏÁ`
¤@Æp°ÏÁ`
¤@Æp°ÏÁ`
¤GÆp°ÏÁ`
¤GÆp°ÏÁ`
¤GÆp°ÏÁ`
¤GÆp°ÏÁ`
¤GÆp°ÏÁ`
¤TÆp°ÏÁ`
¤TÆp°ÏÁ`

TOP

¦^´_ 8# PJChen

D2 =IFERROR(LOOKUP(SUMIFS(H$2:H2,B$2:B2,B2),¸u¶¥!O:O,¸u¶¥!M:M),"µL")
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 9# ML089

·PÁ¤j¤j,

¥¿½TµL»~¡I

TOP

        ÀR«ä¦Û¦b : ª¾ÃÑ­n¥Î¤ßÅé·|¡A¤~¯àÅܦ¨¦Û¤vªº´¼¼z¡C
ªð¦^¦Cªí ¤W¤@¥DÃD