
Excelã§æååã®éè€ããã§ãã¯ãããã
ã©ãããã°ãããã ããïŒ
ãããªçåãè§£æ¶ããŸãã
ã2ã€ã®ããŒã¿ãããéè€ããŠããããŒã¿ãæœåºããããæã£ãŠãããŸãããã
ãMATCH颿°ããISNUMBER颿°ããIF颿°ãã䜿çšããã°ç°¡åã«æœåºã§ããŸãã
ããã§ä»åã¯ããExcelã§æååãéè€ããŠããããã§ãã¯ããæ¹æ³ãã«ã€ããŠè§£èª¬ããŸãïŒ
ã» MATCH颿°ãšã¯
ã» ISNUMBER颿°ãšã¯
ã» IF颿°ãšã¯
ã» ãŸãšã
éè€ããŒã¿æœåºã®æµã
éè€ããŒã¿ãæœåºããã«ã¯ããMATCH颿°ããISNUMBER颿°ããIF颿°ãã®3ã€ã䜿çšããå¿
èŠããããŸãã
å颿°ã¯ã3ã€ã®STEPãã§ä»¥äžã®ããã«äœ¿çšããŸãã
ïŒïŒMATCH颿°ã䜿çšããŠãéè€ããŒã¿ã®çžå¯Ÿäœçœ®ãæœåº
ïŒïŒISNUMBER颿°ã䜿çšããŠãæ°å€ããŒã¿ãåŠããå€å®
ïŒïŒIF颿°ã䜿çšããŠãä»»æã®æååã«å€æ
ä»åã¯ããããŒã¿AãšããŒã¿Bã§éè€ããæååãæœåºãããå Žåãæ³å®ããåSTEPã«ã€ããŠè§£èª¬ããŠãããŸãã
ïŒïŒMATCH颿°ã䜿çšããŠãéè€ããŒã¿ã®çžå¯Ÿäœçœ®ãæœåº
éè€ããŒã¿ã®ã¿ãçžå¯Ÿäœçœ®ã§ãããæ°å€ããæœåºãããŠããŸãã

ããŒã¿Bã§ãå±±ç°è±åãã¯äžãã4çªç®ã§ãããããéè€æ¬ã«ã¯ã4ãã衚瀺ãããã®ã§ãã
ããã«å¯ŸããŠãéè€ããŠããªãããŒã¿ã«ã€ããŠã¯ãã#N/AããæœåºãããŠããŸãã
ïŒïŒISNUMBER颿°ã䜿çšããŠãæ°å€ããŒã¿ãåŠããå€å®
æ°å€ããŒã¿ã®ã¿ããTRUEããæœåºãããŠããŸãã

ããã«å¯ŸããŠãã#N/AãããŒã¿ã«ã€ããŠã¯ããFALSEããæœåºãããŠããŸãã
ïŒïŒIF颿°ã䜿çšããŠãä»»æã®æååã«å€æ
ãTRUEãããŒã¿ã®ã¿ããâããæœåºãããŠããŸãã
ããã«å¯ŸããŠããFALSEãããŒã¿ã«ã€ããŠã¯ããÃããæœåºãããŠããŸãã

ããã§å®äºã§ããéè€ããŒã¿ã«ã¯ãâããä»ããŸããã
MATCH颿°ãšã¯
MATCHã¯ããæå®ããæååãããç¯å²ã®äžã§æ€çŽ¢ãããã®ç¯å²å
ã®çžå¯Ÿçãªäœçœ®ãè¿ãã颿°ã§ãã

ããªãåããã«ãããšæãã®ã§ãå ·äœäŸã亀ããŠèª¬æããŸãã
äŸãã°ããããååãããŒã¿ã®äžã«ååšããã調ã¹ãããå ŽåãèããŠã¿ãŸãããã
MATCH颿°ã䜿çšããã°ããããŒã¿ã®äžã«ããååãæ€çŽ¢ãããã®ååãããŒã¿ã®äœçªç®ã«ãããæœåºãããããšãå¯èœã§ãã
MATCH颿°ã®äœ¿ãæ¹
MATCH颿°ã®åŒæ°ã¯ã以äžã®3ã€ãšãªã£ãŠããŸãã
MATCH(æ€æ»å€, æ€æ»ç¯å², [ç §åã®å])
åŒæ° | 説æ |
æ€çŽ¢å€ | æ€çŽ¢ãããå€ãŸãã¯ã»ã«åç §ãæå®ããã |
æ€çŽ¢ç¯å² | æ€çŽ¢ç¯å²ãšããã»ã«ãæå®ããã |
ç §åã®çš®é¡ | -1ã0ã1 ã®æ°å€ã®ãããããæå®ããã0ãæå®ãããšãæ€æ»å€ãšå®å šäžèŽããæåã®å€ãæ€çŽ¢ããã |
ããããã®åŒæ°ã«ã€ããŠãå
·äœäŸãèžãŸããŠèª¬æããŠãããŸãã
ãããååãããŒã¿ã®äœçªç®ã«ããã調æ»ããããšããäœæ¥ã§èããŠã¿ãŸãããã
æ€çŽ¢å€
ããŒã¿ã®äœçªç®ã«èª¿æ»ãããååããããæ€çŽ¢ããå Žåã«ã¯ãã調æ»ãããååãã«è©²åœããŸãã

ä»åã¯ãå±±ç°è±åãããã§ãã
æ€çŽ¢ç¯å²
ããŒã¿ã®äœçªç®ã«èª¿æ»ãããååããããæ€çŽ¢ããå Žåã«ã¯ããããŒã¿ãã«è©²åœããŸãã

ä»åã¯ãããŒã¿ã®æ°åãã§ãã
ç §åã®çš®é¡
æ€çŽ¢å€åŸããŒã¿å€ããå®å šã«äžèŽããå Žåã«ã®ã¿å¿ èŠããŒã¿ãæœåºãããéã¯ã0ããæå®ããŸãã
ããã«å¯ŸããŠãæ€çŽ¢å€ãšããŒã¿å€ããå®å šã«äžèŽããªãå Žåã«æ€çŽ¢å€ã«æãè¿ãããŒã¿ãæœåºãããéã¯ã1ããŸãã¯ã-1ããæå®ããŸãã

ä»åã¯ãå®å šã«äžèŽããå Žåã«æœåºããããšãèããŠã0ããšããŸãã
çµæã®äŸ
æ€çŽ¢ãããå€ã§ãããå±±ç°è±åãã¯ãããŒã¿ã®4çªç®ã«ããããã4ãšããæ°åãæœåºãããŠããŸãã
ãã®ããã«ãMATCH颿°ã¯ãæ€çŽ¢ããããŒã¿ã®çžå¯Ÿäœçœ®ããæœåºã§ããŸãã
ISNUMBER颿°ãšã¯
ISNUMBERã¯ãæå®ããã»ã«ã®å€ããæ°å€ãåŠããå€å®ããã颿°ã§ãã

ããã¯ã€ã¡ãŒãžããããã§ããããããã«ãå ·äœäŸã亀ããŠèª¬æããŠãããŸãã
äŸãã°ãããã»ã«ã«å ¥åãããå€ããæ°å€ãåŠã調ã¹ãããå ŽåãèããŠã¿ãŸãããã
ISNUMBER颿°ã䜿çšããã°ã以äžã®ããã«ãã»ã«ã®å€ãåç
§ãããããæ°å€ãåŠãå€å®ãããããšãå¯èœã§ãã
ISNUMBER颿°ã®äœ¿ãæ¹
ISNUMBER颿°ã®åŒæ°ã¯ã以äžã®1ã€ãšãªã£ãŠããŸãã
ISNUMBER(ãã¹ãã®å¯Ÿè±¡)
åŒæ° | 説æ |
ãã¹ãã®å¯Ÿè±¡ | æ°å€ãåŠãå€å®ãããã»ã«ãæå®ããã |
åŒæ°ã«ã€ããŠãå ·äœäŸãèžãŸããŠèª¬æããŠãããŸãã
ã調ã¹ããã»ã«ã®å€ãæ°å€ãªãTRUEãè¿ãããšããäœæ¥ã§èããŠã¿ãŸãããã
ãã¹ãã®å¯Ÿè±¡
調ã¹ããã»ã«ã®å€ãæ°å€ãåŠãå€å®ããå Žåã«ã¯ãã調ã¹ããã»ã«ãã«è©²åœããŸãã

ä»åã¯ã1ãã§ãã
çµæã®äŸ
ãã¹ãã®å¯Ÿè±¡ãã1ãã®å Žåã¯ãTRUEãããã¹ãã®å¯Ÿè±¡ããæååãã®å Žåã¯ãFALSEããæœåºãããŠããŸãã
ãã®ããã«ãISNUMBER颿°ã¯ã調ã¹ããã»ã«ã®å€ãæ°å€ãåŠãå€å®ãã§ããŸãã
IF颿°ãšã¯
IFã¯ãããã»ã«ã®å€ãæå®ããæ¡ä»¶åŒã«å¯ŸããŠãçã®å Žåã¯å€Aã衚瀺ããåœã®å Žåã¯å€Bã衚瀺ããã颿°ã§ãã

å°ãåããã«ããã§ããããå ·äœäŸã亀ããŠèª¬æããŸãã
äŸãã°ãåç°¿ã®äžã§ãå±±ç°è±åããã ãâãä»ããããå ŽåãèããŠã¿ãŸãããã
IF颿°ã䜿çšããã°ã以äžã®ããã«ãããŒã¿ã®äžã«ããååã«ãããŠãæå®ããååã«âãããã以å€ã«Ããä»ãããããšãã§ããŸãã
IF颿°ã®äœ¿ãæ¹
IF颿°ã®åŒæ°ã¯ã以äžã®3ã€ãšãªã£ãŠããŸãã
IF(è«çåŒ, [å€ãçã®å Žå], [å€ãåœã®å Žå])
åŒæ° | 説æ |
è«çåŒ | æ¡ä»¶åŒã§ãããå€ãæ¯èŒããã |
[å€ãçã®å Žå] | æ¡ä»¶åŒã«å¯ŸããŠãçµæãçã§ããå Žåã«è¿ãå€ã |
[å€ãåœã®å Žå] | æ¡ä»¶åŒã«å¯ŸããŠãçµæãåœã§ããå Žåã«è¿ãå€ã |
ããããã®åŒæ°ã«ã€ããŠãå
·äœäŸãèžãŸããŠèª¬æããŠãããŸãã
ãåç°¿ã®äžã§å±±ç°è±åããã ãã«âãä»ããããšããäœæ¥ã§èããŠã¿ãŸãããã
è«çåŒ
åç°¿ã®äžã§å±±ç°è±åããã ãã«âãä»ããå Žåã«ã¯ããå±±ç°è±åãããåŠãå€å®ããåŒãã«è©²åœããŸãã

ä»åã®äŸã§ã¯ããB4=$E$S4ãšããåŒãã§ãã
[å€ãçã®å Žå]
åç°¿ã®äžã§å±±ç°è±åããã ãã«âãä»ããå Žåã«ã¯ããâãã«è©²åœããŸãã

ä»åã¯ããâãã§ãã
[å€ãåœã®å Žå]
åç°¿ã®äžã§å±±ç°è±åããã ãã«âãä»ããå Žåã«ã¯ããÃãã«è©²åœããŸãã

ä»åã¯ããÃãã§ãã
çµæã®äŸ
調ã¹ããæååããå±±ç°è±åãã®å Žåã¯ãâãããã以å€ã®å Žåã¯ãÃããæœåºãããŠããŸãã
ãã®ããã«ãIF颿°ã¯ãããŒã¿ã®äžã«ããååã«ãããŠãæå®ããååã«âãããã以å€ã«Ããä»ãããããšãã§ããŸãã
ãŸãšã
ãããã§ããã§ããããã
3ã€ã®é¢æ°ã䜿çšããã°ãExcelã§æååãéè€ããŠããããã§ãã¯ãã§ããããšãåãããŸããã
ç§èªèº«ãæååã®éè€ãã§ãã¯ã¯æ¥åã§ãããè¡ããŸãã

æååã®éè€ããããå Žåã¯ãä»å玹ä»ããæ¹æ³ãäžžãã¯ãªããŠã¿ãŠäžããã
ãã®èšäºããçæ§ã®ã圹ã«ç«ã€ããšãé¡ã£ãŠããŸãã
ä»äºå¹çåã®æ¹æ³ã玹ä»ããŠããŸãïŒ
人æ°èšäºããWindowsãã©ã«ãæäœãã·ã§ãŒãã«ããããŒã§äœæ¥ã¹ããŒãã¢ããïŒ
人æ°èšäºããæã®éå€é»è»ã§ã¯å¯ãã¹ããä»äºã®ããã©ãŒãã³ã¹ãäžãããŸãïŒ