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

[µo°Ý] ¦p¦ó¤ÀÃþ¨Ã¤À§O²Î­p¸ê®Æ?

[µo°Ý] ¦p¦ó¤ÀÃþ¨Ã¤À§O²Î­p¸ê®Æ?

AÄæ¦ÜDÄ欰¸ê®Æ°Ï¡A¦p¦ó³]©w¤½¦¡±N¸ê®Æ°Ï¤ÀÃþ¨Ã¤À§O¤p­p¡A¦p¡G¢ÔÄæ¦ÜHÄæ

3Q

¬¡­¶Ã¯1.rar (7.9 KB)

H2 ¤U©Ô=SUMPRODUCT((B$2:D$9=G2)*(A$2:A$9=LOOKUP(1,0/(F$2:F2<>""),F$2:F2)))
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

F2 °}¦C¤½¦¡¿é¤J=IF(MOD(ROW(A9),SUM(1/COUNTIF(B$2:D$9,B$2:D$9))),"",INDEX(A$2:A$9,MATCH(,COUNTIF(F$1:F1,A$2:A$9),)) )


G2 °}¦C¤½¦¡¿é¤J
=IF(ROW(A1)>SUM(1/COUNTIF(B$2:D$9,B$2:D$9)),INDIRECT("R-9C",),
INDIRECT(TEXT(MOD(MIN(IF(COUNTIF(G$1:G1,$B$2:$D$9)=0,ROW($2:$9)*100+COLUMN(B:D)+COLUMN(B:D)*10^4,999999)),10^4),"!r0c00"),))
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦pªGAÄæ¦ÜDÄæ¸ê®Æ¦³¼Æ¦Êµ§¬Æ¦Ü¤W¤dµ§¡A¦Ó¢ÕÄæ¸ê®Æ«¬ºA¦³¤W¦ÊºØ¤§¦h¡Aªþ¥ó©Ò¥Ü¸ê®Æ«¬ºA(GÄæ)¥u¦³¢¸­Ó
¨º»ò¢ÔÄæ¦Ü¢ÖÄ檺¤½¦¡­n¦p¦ó§ó§ï©O¡H
3Q

TOP

¦^´_ 4# q1a2z5

À³¸Ó¤£¥Î§ï§a!¡A·|¦Û°Ê½Õ¾ã¡A¥ý®M¤J¸Õ¸Õ
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

§Ú±NAÄæ¦ÜDÄ檺¸ê®Æ¤º®e°µ¤F§ó§ï¡A±µµÛ±NF2,G2,H2Ä檺¤½¦¡©¹¤U©Ô¡A¤@©Ôµ²ªG´NÅܤF¡]¦p¬¡­¶Ã¯¢±¡^
½Ð¦U¦ì¤j¤jÀ°À°¦£¡I
¢²¢ß

¬¡­¶Ã¯2.rar (13.29 KB)

TOP

¦^´_ 6# q1a2z5


¦p¦ó¤ÀÃþ¨Ã¤À§O²Î­p¸ê®Æ.rar (24.84 KB)
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

        ÀR«ä¦Û¦b : ¨Ã«D¦³¿ú¾{¬O§Ö¼Ö¡A°Ý¤ßµL·\¤ß³Ì¦w¡C
ªð¦^¦Cªí ¤W¤@¥DÃD