º£²ó¤Ï¡¢¥¢¥ó¥±¡¼¥È½¸·×·ë²Ì¤ò¥°¥é¥Õ²½¤¹¤ëºÝ¡¢¥Õ¥£¥ë¥¿¤ÇÃê½Ð¤·¤¿·ë²Ì¤òÈ¿±Ç¤µ¤»¤ëÊýË¡¤Ë¤Ä¤¤¤Æ½ñ¤¤Þ¤¹¡£
¥¨¥¯¥»¥ë¤Î¥·¡¼¥È¤Ë¥¢¥ó¥±¡¼¥È¤Ç½¸¤á¤é¤ì¤¿¥Ç¡¼¥¿¤òÆþÎϤ¹¤ë¤Î¤Ç¤¹¤¬¡¢
Î㤨¤Ð²¼µ¤Î¤è¤¦¤ËÆþÎϤµ¤ì¤¿¥·¡¼¥È¤¬¤¢¤Ã¤¿¤È¤·¤Þ¤¹¡£
AÎó¤ËÀÊ̤Υǡ¼¥¿¡ÊA1¤¬ÃËA2¤¬½÷A3¤¬Ãˤߤ¿¤¤¤Ê¡Ë
BÎó¤ËÃÏ°è¤Î¥Ç¡¼¥¿¡ÊB1¤¬ÅìµþÅÔB2¤¬ºë¶Ì¸©B3¤¬ÀéÍÕ¸©¤ß¤¿¤¤¤Ê¡Ë
CÎó¤Ë¹¥¤¤Ê¥¹¥Ý¡¼¥Ä¤òÊ£¿ô²óÅú²Ä¡ÊC1¤¬Ìîµå ¥µ¥Ã¥«¡¼C2¤¬¥µ¥Ã¥«¡¼ C3¤¬¥µ¥Ã¥«¡¼ ¥Ð¥¹¥±¤ß¤¿¤¤¤Ê¡Ë
¤³¤¦¤¤¤Ã¤¿¥Ç¡¼¥¿¤Ç¥°¥é¥Õ¤òºî¤ë»þ¤Ë¡¢¤Þ¤º²ÝÂê¤È¤Ê¤ë¤Î¤¬¡¢Ê£¿ô²óÅú¤ÎÉôʬ¤Ç¤¹¡£
CÎó¤Ç¥µ¥Ã¥«¡¼¤ò½¸·×¤¹¤ëºÝ¡¢=countif(C1:C3,"¥µ¥Ã¥«¡¼")¤ÈÆþÎϤ¹¤ë¤È¡¢
C1¤ÎÌîµå ¥µ¥Ã¥«¡¼¤ÈC3¤Î¥µ¥Ã¥«¡¼ ¥Ð¥¹¥±¤ò¥«¥¦¥ó¥È¤·¤Æ¤¯¤ì¤Þ¤»¤ó¡£
¤½¤³¤Ç¡¢½¸·×¤¹¤ë¥»¥ë¤Ë=countif(C1:C3,"*¥µ¥Ã¥«¡¼*")¤È¤¤¤¦¤è¤¦¤Ë¡¢
¥ï¥¤¥ë¥É¥«¡¼¥É¤ò»È¤Ã¤Æ¥«¥¦¥ó¥È¤¹¤ì¤Ð¡¢C1¤âC3¤â¥«¥¦¥ó¥È¤·¤Æ¤¯¤ì¤Þ¤¹¡£
¤¿¤À¡¢ÅìµþÅÔ¤ÎÃËÀ¤À¤±¤Î½¸·×¤ò¸«¤¿¤¤¤È»×¤¤¡¢¸µ¤Î¥Ç¡¼¥¿¤Ë¥Õ¥£¥ë¥¿¤ò¤«¤±¤Æ
ɽ¼¨¤·¤Æ¤â¡¢countif¤ÏÈóɽ¼¨¤ÎÉôʬ¤â¥«¥¦¥ó¥È¤·¤Æ¤·¤Þ¤¦¤Î¤Ç¡¢
½¸·×¥Ç¡¼¥¿¤Ë¤ÏÈ¿±Ç¤µ¤ì¤Þ¤»¤ó¡£
¥Õ¥£¥ë¥¿¤ò¤«¤±¤¿¾õÂÖ¤ò¥«¥¦¥ó¥È¤¹¤ë´Ø¿ô¤È¤·¤Æsubtotal¤È¤¤¤¦¤â¤Î¤¬¤¢¤ê¤Þ¤¹¤¬¡¢
¤³¤ì¤À¤ÈÆÃÄê¤Îʸ»ú¤òÃê½Ð¤·¤Æ¥«¥¦¥ó¥È¤¹¤ë»ö¤¬½ÐÍè¤Þ¤»¤ó¡£
¤½¤³¤Ç¡¢sumproduct´Ø¿ô¤ò»È¤¤
=SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW(INDIRECT("1:"&ROWS(C1:C3))),))*(C1:C3="¥µ¥Ã¥«¡¼"))
¤ß¤¿¤¤¤Êµ½Ò¤ò¤·¤Æ¤ß¤¿¤Î¤Ç¤¹¤¬¡¢¤³¤Î¼°¤Ç¥µ¥Ã¥«¡¼¤ÎÁ°¸å¤Ë¥¢¥¹¥¿¥ê¥¹¥¯¤ò»È¤¦¤È¥«¥¦¥ó¥È¤µ¤ì¤Þ¤»¤ó¤Ç¤·¤¿¡£
·ë¶É¥Þ¥¯¥í¤ò»È¤Ã¤Æsubtotal_countif¤È¤¤¤¦¤â¤Î¤òÄêµÁ¤·¤Æ¥«¥¦¥ó¥È¤·¤Þ¤·¤¿¡£
°Ê²¼¤¬¤½¤Î¥Þ¥¯¥í¤Îµ½Ò¤Ë¤Ê¤ê¤Þ¤¹¡£
Public Function subtotal_countif(rgSelect As Range, moji As String)
Dim rg As Range '¥»¥ë
Dim cot As Long '¥«¥¦¥ó¥¿
For Each rg In rgSelect
If Rows(rg.Row).Hidden = False Then 'ɽ¼¨¤µ¤ì¤Æ¤¤¤ë¹Ô¤À¤±Âоݤˤ¹¤ë
If rg Like moji Then
cot = cot + 1
End If
End If
Next
subtotal_countif = cot
End Function
¥¨¥¯¥»¥ë¤ÎVBE¥á¥Ë¥å¡¼(ALT+F11)¤Ç¡¢ÁÞÆþ→ɸ½à¥â¥¸¥å¡¼¥ë¤ÇÁÞÆþ¤¹¤ì¤Ð
=subtotal_countif(ÈÏ°Ï,”*Ãê½Ðʸ»ú*”¡Ë¤È¤¤¤¦¼°¤¬»È¤¨¤ë¤è¤¦¤Ë¤Ê¤ê¤Þ¤¹¡£
¤³¤ì¤Ç¡¢¥Õ¥£¥ë¥¿¤ò¤«¤±¤¿¾õÂÖ¤ÇÆÃÄê¤Îʸ»ú¤òÃê½Ð¥«¥¦¥ó¥È¤¹¤ë»ö¤¬½ÐÍè¤ë¤è¤¦¤Ë¤Ê¤ê¡¢
¥°¥é¥Õ¤â¤½¤Î¥Õ¥£¥ë¥¿¤ò¤«¤±¤¿¾õÂ֤Τâ¤Î¤òºî¤ë»ö¤¬½ÐÍè¤Þ¤¹¡£