[µo°Ý] INDIRECT¨ç¼Æ¤£¯à°ÊºA°Ñ·Óªº°ÝÃD

ziv976688

¤U¸ü (115.65 KB)

2020-12-16 20:46

INDIRECT.rar (8.88 KB)

¤U¸ü¦¸¼Æ: 7

2020-12-16 20:46

=IF(MAX((COUNTIF(OFFSET($B$1,,,,COLUMN($A:$AW)),"<>")=COUNTIF($B$1:B$1,"<>"))*$B4:$AX4)=B4,B4,"")  
·Q±N¤W­z°}¦C¤½¦¡¦³ÃC¦âªºÀx¦s®æ¦ì§}¥HINDIRECT¨ç¼Æ§@°Ñ·Ó¡A
¥H§Q¤½¦¡¯à¤@¦¡¤U©Ô¹F¨ì»Ý¨Dµ²ªG(¸Ô¦pB24:AX29ªºµª®×)¡C
EX¡JB16
=IF(MAX((COUNTIF(OFFSET($B$1,,,,COLUMN($A:$AW)),"<>")=COUNTIF($B$1:B$1,"<>"))*INDIRECT("B"&$A16&":AX"&$A16))=INDIRECT("B"&$A16),INDIRECT("B"&$A16),"")
¦ý¦]INDIRECT¨ç¼Æ¤£¯à§@°ÊºA°Ñ·Ó¡A©Ò¥H¤½¦¡¥k©Ô¶ñº¡µL®Ä¡C

½Ð°Ý¡J
B16ªº¤½¦¡À³¸Ó¦p¦ó­×¥¿¬°©y¡H
ÁÂÁ¡I

¼B¤j­G

¨Ï¥ÎColumn + Char §t¼Æ·f°t¤£´N¥i¥H¤F?! CHAR(66)="B" ....??

ziv976688

¦^´_ 2# ¼B¤j­G
ÁÂÁ«ü¾É!
¦³±NB16¤½¦¡§ï¬°
=IF(MAX((COUNTIF(OFFSET($B$1,,,,COLUMN($A:$AW)),"<>")=COUNTIF($B$1:B$1,"<>"))*INDIRECT("B"&$A16&":AX"&$A16))=INDIRECT(CHAR(64+COLUMN(B:B))&$A16),INDIRECT(CHAR(64+COLUMN(B:B))&$A16),"")
¦ýÀx¦s®æÅã¥Ü"#VALUE!"
¤£ª¾­þ¸Ì¿ù¤F?
½Ð½ç¥¿!ÁÂÁÂ!

¼B¤j­G

Here you are ~

=IF(MAX((COUNTIF(OFFSET($B$1,,,,COLUMN($AAW)),"<>")=COUNTIF($B$1:B$1,"<>"))*INDIRECT("B"&$A16&":AX"&$A16))=INDIRECT(CHAR(64+COLUMN(B16))&$A16),INDIRECT(CHAR(64+COLUMN(B16))&$A16),"")

ziv976688

¦^´_ 4# ¼B¤j­G
ÁÂÁ¦^ÂÐ:D
¥u¬OB16Àx¦s®æ¤]¬OÅã¥Ü"#VALUE!":funk:
©|½Ð¦A½ç¥¿~ÁÂÁ±z:lol

­ã´£³¡ªL

°}¦C¤½¦¡:
=IF(MAX((SUBTOTAL(3,OFFSET($B$1,,,,COLUMN($A:$AW)))=COUNTA($B$1:B$1))*OFFSET($B$1,$A16-1,,,49))=INDEX(B$1:B$13,$A16),INDEX(B$1:B$13,$A16),"")

=IF(MAX((SUBTOTAL(3,OFFSET($B$1,,,,COLUMN($A:$AW)))=COUNTA($B$1:B$1))*OFFSET($B$1,$A16-1,,,49))=OFFSET(B$1,$A16-1,),OFFSET(B$1,$A16-1,),"")

ziv976688

¦^´_ 6# ­ã´£³¡ªL
­ã´£ª©¥D:
¶Q¤½¦¡´ú¸Õ¦¨¥\~·P®¦^^

=IF(MAX((SUBTOTAL(3,OFFSET($B$1,,,,COLUMN($A:$AW)))=COUNTA($B$1:B$1))*OFFSET($B$1,$A16-1,,,49))=INDEX(B$1:B$13,$A16),INDEX(B$1:B$13,$A16),"")
½Ð±Ð¶Q¤½¦¡¤¤ªº3¬O¥Nªí¤°»ò·N«ä?
·q½Ð½ç±Ð!ÁÂÁ±z!

ziv976688

¦^´_ 6# ­ã´£³¡ªL
­ã´£ª©¥D:±z¦n!
¦³¤WGoogleª¦¤å¡A¤wÁA¸Ñ3¬OSUBTOTAL¨ç¼Æªº¥N½X¤§¤@¡A¤]ª¾¹D¥¦ªº§@¥Î¤F¡C
¦A¦¸·PÁ±zªº¼ö¤ß«ü¾É©MÀ°¦£

ziv976688

¦^´_ 4# ¼B¤j­G
¼B¤j¤j:±z¦n!
ÁöµM¥»µo°ÝÃD¤w¸Ñ¨M¡A¦ýÁÙ¬O·Q¬ã²ß±zªº¸ÑÃD¤½¦¡¡C
·q½Ð«ü±Ð!ÁÂÁ±z

¥HB16
=INDIRECT(CHAR(64+COLUMN(B:B))&$A16)
©Î
=INDIRECT(CHAR(64+COLUMN(B16))&$A16)
´ú¸Õ«áªºµ²ªG:B16ªºµª®×³£¬O=58¡Aµª®×¥¿½T¡F

¦ý¾ã¬q°}¦C¤½¦¡ªºB16(«öF9À˵øINDIRECT(CHAR(64+COLUMN(B16))&$A16)¡A½s¿è¦CÅã¥Ü{58}~¥¿½T)¡F
¦ý¾ã¬q°}¦C¤½¦¡ªºB16µª®×«oÅã¥Ü"#VALUE!"(¤½¦¡¤¤¬Y­È¸ê®ÆÃþ«¬¿ù»~)
¦A«öF9À˵ø¤½¦¡¨ä¥¦ªº¦U¤ù¬q¡A§ä¤F³\¤[ÁÙ¬O¤£ª¾¤½¦¡¤¤¦³­þ¤@­Ó¬O¿ù»~­È

¼B¤j­G

¦^´_ 9# ziv976688
Hello, ziv976688

¨Sª`·N¨ì§A¬O°}¦Cªº¤½¦¡, ­è¬Ý¤F¤@¤U, «e«á¤G­Ó¤½¦¡, ¦U¦Û¥Î³£¨S°ÝÃD, ¦ý¤@¬Û­¼´NFail,
©Î³\CountIFµLªk¨Ï¥Î©ó°}¦C§a?
­n¤£µM·Ç¤jÀ³¸Ó¤£·|¥ÎCountA¨Ó°µ.
³o¬O§Úªº²z¸Ñ.....Thanks ~

1. (COUNTIF(OFFSET($B$1,,,,COLUMN($A:$AW)),"<>")=COUNTIF($B$1:B$1,"<>"))
2. INDIRECT("B"&$A16&":AX"&$A16))