Ð
å®è·µçãªéšåã¯ã¹ãããã®åœ¢ã§ç€ºãããŠããŸãã æãäžè¬çãªããŒã«ãšèª¬æãããŠããæäœã¯ Excel ãç解ããŠããã»ãšãã©ã®å°é家ãç¹°ãè¿ãããšãã§ããããããã¹ãŠã®ã¯ãªãŒãã³ã°ã¯ Excel ã§å®è¡ãããŸããã ãããŠæäœæ¥ã«éåžžã«é©ããŠããŸãã
ãŒã段éã¯ãã¡ã€ã«ã®èµ·åãšä¿åã®äœæ¥ã«ãªããŸãããã¡ã€ã«ã®ãµã€ãºã¯ 100 MB ã§ããããããããã®æäœã®æ°ã¯æ°åãæ°çŸã«ããªããããªãã®æéãããããŸãã
ãªãŒããã³ã°ã¯å¹³åã㊠30 ç§ã§ãã
ä¿å â 22ç§
æåã®æ®µéã¯ãããŒã¿ã»ããã®çµ±èšææšã決å®ããããšããå§ãŸããŸãã
è¡š 1. ããŒã¿ã»ããã®çµ±èšææš
ãã¯ãããžãŒ 2.1.
è£å©ãã£ãŒã«ããäœæããŸããAY ãšããçªå·ã®äžã«ãããŸãã åãšã³ããªã«å¯ŸããŠãåŒã=LENGTH(F365502)+LENGTH(G365502)+âŠ+LENGTH(AW365502)ãã圢æããŸãã
ã¹ããŒãž 2.1 ã«è²»ãããåèšæé (ã·ã¥ãŒãã³åŒã®å Žå) t21 = 1 æéã
ã¹ããŒãž 2.1 ã§èŠã€ãã£ããšã©ãŒã®æ° (ã·ã¥ãŒãã³å
¬åŒã®å Žå) n21 = 0 åã
第äºæ®µéã
ããŒã¿ã»ããã®ã³ã³ããŒãã³ãã確èªããŠããŸãã
2.2. ã¬ã³ãŒãå
ã®ãã¹ãŠã®å€ã¯ãæšæºã®èšå·ã䜿çšããŠåœ¢æãããŸãã ããã§ãçµ±èšãã·ã³ãã«ã§è¿œè·¡ããŠã¿ãŸãããã
è¡š 2. ããŒã¿ã»ããå
ã®æåã®çµ±èšçææšãšçµæã®äºååæã
ãã¯ãããžãŒ 2.2.1.
è£å©ãã£ãŒã«ããalpha1ããäœæããŸãã åã¬ã³ãŒãã«å¯ŸããŠãæ°åŒã=CONCATENATE(Sheet1!B9;...Sheet1!AQ9)ããäœæããŸãã
åºå®ããããªã¡ã¬ 1 现èãäœæããŸãã ãã®ã»ã«ã«Windows-1251ã«æºæ ããæåã³ãŒãã32ãã255ãŸã§äº€äºã«å
¥åããŠãããŸãã
è£å©ãã£ãŒã«ããalpha2ããäœæããŸãã åŒã=FIND(SYMBOL(Omega,1); âalpha1â,N)ãã䜿çšããŸãã
è£å©ãã£ãŒã«ããalpha3ããäœæããŸãã åŒã=IF(ISNUMBER(âalpha2â,N),1)ãã®å Žå
æ°åŒã=SUM(âalpha2âN3: âalpha1âN3)ãã䜿çšããŠåºå®ã»ã«ãOmega-365498ããäœæããŸãã
è¡š 3. çµæã®äºååæã®çµæ
è¡š 4. ãã®æ®µéã§èšé²ããããšã©ãŒ
ã¹ããŒãž 2.2.1 ã«è²»ãããåèšæé (ã·ã¥ãŒãã³åŒã®å Žå) t221 = 8 æéã
ã¹ããŒãž 2.2.1 ã§èšæ£ããããšã©ãŒã®æ° (ã·ã¥ãŒãã³åŒã®å Žå) n221 = 0 åã
3ã¹ãããã
2.2.2 çªç®ã®ã¹ãããã¯ãããŒã¿ã»ããã®ç¶æ
ãèšé²ããããšã§ãã åã¬ã³ãŒãã«äžæã®çªå· (ID) ãšåãã£ãŒã«ããå²ãåœãŠãããšã«ãã£ãŠã ããã¯ãå€æãããããŒã¿ã»ãããå
ã®ããŒã¿ã»ãããšæ¯èŒããããã«å¿
èŠã§ãã ããã¯ãã°ã«ãŒãåæ©èœãšãã£ã«ã¿ãªã³ã°æ©èœãæ倧éã«æŽ»çšããããã«ãå¿
èŠã§ãã ããã§åã³è¡š 10 ã«æ»ããããŒã¿ã»ããã§äœ¿çšãããŠããªãã·ã³ãã«ãéžæããŸãã å³ XNUMX ã«ç€ºãå
容ãåŸãããŸãã
å³10ã èå¥åã®å²ãåœãŠã
ã¹ããŒãž 3 ã«è²»ãããåèšæé (ã·ã¥ãŒãã³åŒã®å Žå) t3 = 0,75 æéã
ã¹ããŒãž 3 ã§èŠã€ãã£ããšã©ãŒã®æ° (ã·ã¥ãŒãã³å
¬åŒã®å Žå) n3 = 0 åã
ã·ã¥ãŒãã³ã®å ¬åŒã§ã¯ããšã©ãŒãä¿®æ£ããããšã«ãã£ãŠæ®µéãå®äºããå¿ èŠãããããã ã¹ããŒãž2ã«æ»ããŸãããã
2.2.2ã¹ãããã
ãã®ã¹ãããã§ã¯ãäºéã¹ããŒã¹ãšäžéã¹ããŒã¹ãä¿®æ£ããŸãã
å³11ã äºéã¹ããŒã¹ã®æ°ã
è¡š 2.2.4 ã§ç¹å®ããã誀ããä¿®æ£ã
è¡š 5. ãšã©ãŒèšæ£ã¹ããŒãž
æåãeããŸãã¯ãeãã®äœ¿çšãªã©ã®åŽé¢ãéèŠã§ããçç±ã®äŸãå³ 12 ã«ç€ºããŸãã
å³12ã æåãeãã®ççŸã
ã¹ããã 2.2.2 t222 ã§è²»ãããåèšæé = 4 æéã
ã¹ããŒãž 2.2.2 ã§èŠã€ãã£ããšã©ãŒã®æ° (ã·ã¥ãŒãã³å
¬åŒã®å Žå) n222 = 583 åã
第4ã¹ããŒãž
ãã£ãŒã«ãã®åé·æ§ã®ãã§ãã¯ã¯ããã®æ®µéã«ããåœãŠã¯ãŸããŸãã 44 ãã£ãŒã«ãã®ãã¡ã6 ãã£ãŒã«ã:
7 - æ§é ã®ç®ç
16 â å°äžéæ°
17 - 芪ãªããžã§ã¯ã
21 - æè°äŒ
38 â æ§é ãã©ã¡ãŒã¿ãŒ (説æ)
40 â æåéºç£
ãšã³ããªããããŸããã ã€ãŸãããããã¯åé·ã§ãã
ãã£ãŒã«ãã22 â Cityãã«ã¯ 13 ã€ã®ãšã³ããªããããŸã (å³ XNUMX)ã
å³13ã ãCityããã£ãŒã«ãã®å¯äžã®ãšã³ããªã¯ Z_348653 ã§ãã
ãã£ãŒã«ãã34 - 建ç©åãã«ã¯ãæããã«ãã£ãŒã«ãã®ç®çã«å¯Ÿå¿ããªããšã³ããªãå«ãŸããŠããŸã (å³ 14)ã
å³14ã éæºæ ãšã³ããªã®äŸã
ãããã®ãã£ãŒã«ããããŒã¿ã»ããããé€å€ããŸãã ãããŠããã®å€åã 214 件ã®ã¬ã³ãŒãã«èšé²ããŸãã
ã¹ããŒãž 4 ã«è²»ãããåèšæé (ã·ã¥ãŒãã³åŒã®å Žå) t4 = 2,5 æéã
ã¹ããŒãž 4 ã§èŠã€ãã£ããšã©ãŒã®æ° (ã·ã¥ãŒãã³å
¬åŒã®å Žå) n4 = 222 åã
è¡š 6. 第 4 段é以éã®ããŒã¿ã»ããææšã®åæ
äžè¬ã«ãææšã®å€å (è¡š 6) ãåæãããšã次ã®ããšãèšããŸãã
1) æšæºåå·®ã¬ããŒã«å¯Ÿããå¹³åã·ã³ãã«æ°ã®æ¯ã¯ 3 ã«è¿ããã€ãŸãæ£èŠååž (ã·ãã¯ã¹ ã·ã°ã ã«ãŒã«) ã®å
åããããŸãã
2) å¹³åã¬ããŒããã®æå°ã¬ããŒãšæ倧ã¬ããŒã®å€§å¹
ãªåå·®ã¯ããšã©ãŒãæ¢ãéã«ããŒã«ã®ç 究ãææãªæ¹åã§ããããšã瀺åããŠããŸãã
ã·ã¥ãŒãã³ã®æ¹æ³è«ã䜿çšããŠãšã©ãŒãèŠã€ããçµæã調ã¹ãŠã¿ãŸãããã
ã¢ã€ãã«ã¹ããŒãž
2.1. ã¹ããŒãž 2.1 ã«è²»ãããåèšæé (ã·ã¥ãŒãã³åŒã®å Žå) t21 = 1 æéã
ã¹ããŒãž 2.1 ã§èŠã€ãã£ããšã©ãŒã®æ° (ã·ã¥ãŒãã³å
¬åŒã®å Žå) n21 = 0 åã
3. ã¹ããŒãž 3 ã«è²»ãããåèšæé (ã·ã¥ãŒãã³åŒã®å Žå) t3 = 0,75 æéã
ã¹ããŒãž 3 ã§èŠã€ãã£ããšã©ãŒã®æ° (ã·ã¥ãŒãã³å
¬åŒã®å Žå) n3 = 0 åã
å¹æçãªã¹ããŒãž
2.2. ã¹ããŒãž 2.2.1 ã«è²»ãããåèšæé (ã·ã¥ãŒãã³åŒã®å Žå) t221 = 8 æéã
ã¹ããŒãž 2.2.1 ã§èšæ£ããããšã©ãŒã®æ° (ã·ã¥ãŒãã³åŒã®å Žå) n221 = 0 åã
ã¹ããã 2.2.2 t222 ã§è²»ãããåèšæé = 4 æéã
ã¹ããŒãž 2.2.2 ã§èŠã€ãã£ããšã©ãŒã®æ° (ã·ã¥ãŒãã³å
¬åŒã®å Žå) n222 = 583 åã
ã¹ããã 2.2 ã§è²»ãããåèšæéã¯ãt22 = 8 + 4 = 12 æéã§ãã
ã¹ããŒãž 2.2.2 ã§èŠã€ãã£ããšã©ãŒã®æ° (ã·ã¥ãŒãã³å
¬åŒã®å Žå) n222 = 583 åã
4. ã¹ããŒãž 4 ã«è²»ãããåèšæé (ã·ã¥ãŒãã³åŒã®å Žå) t4 = 2,5 æéã
ã¹ããŒãž 4 ã§èŠã€ãã£ããšã©ãŒã®æ° (ã·ã¥ãŒãã³å
¬åŒã®å Žå) n4 = 222 åã
ã·ã¥ãŒãã³ ã¢ãã«ã®æåã®ã¹ããŒãžã«å«ããå¿ èŠãããã¹ããŒãžã¯ãŒãã§ãããäžæ¹ã§ãã¹ããŒãž 2.2 ãš 4 ã¯æ¬è³ªçã«ç¬ç«ããŠãããããã·ã¥ãŒãã³ ã¢ãã«ã¯ãã§ãã¯ã®æéãé·ãããããšã§ç¢ºçããšã©ãŒã®æ€åºçãæžå°ãããã€ãŸããããŒã«ãã£ãŠé害ãæžå°ããå Žåããã®ãããŒã調ã¹ãããšã§ã©ã®ã¹ããŒãžãæåã«é 眮ãããã決å®ããŸããã«ãŒã«ã«åŸã£ãŠãé害å¯åºŠãããé »ç¹ã«çºçããå Žåã¯ããã®ã¹ããŒãžãæåã«é 眮ããŸãã
Ris.15ã
å³ 15 ã®åŒãããèšç®ã§ã¯ç¬¬ 2.2 ã¹ããŒãžãã¹ããŒãž XNUMX ã®åã«é 眮ããããšã奜ãŸããããšãããããŸãã
ã·ã¥ãŒãã³ã®å ¬åŒã䜿çšããŠãæšå®ãããåæãšã©ãŒæ°ã決å®ããŸãã
Ris.16ã
å³ 16 ã®çµæãããäºæž¬ãšã©ãŒæ°ã¯ N2 = 3167 ã§ãããæå°åºæºã® 1459 ãè¶ ããŠããããšãããããŸãã
ä¿®æ£ã®çµæã805 件ã®ãšã©ãŒãä¿®æ£ãããäºæž¬æ°ã¯ 3167 â 805 = 2362 ãšãªããããã¯äŸç¶ãšããŠèš±å®¹ãããæå°ãããå€ãè¶ ããŠããŸãã
ãã©ã¡ãŒã¿ãŒ Cãã©ã ããä¿¡é Œæ§é¢æ°ãå®çŸ©ããŸãã
Ris.17ã
åºæ¬çã«ãã©ã ãã¯å段éã§æ€åºããããšã©ãŒã®åŒ·åºŠã瀺ãå®éã®ææšã§ãã äžãèŠããšããã®ã€ã³ãžã±ãŒã¿ãŒã®ä»¥åã®æšå®å€ã¯ 42,4 æéããã 1 ãšã©ãŒã§ãããããã¯ã·ã¥ãŒãã³ ã€ã³ãžã±ãŒã¿ãŒãšéåžžã«å¹æµããŸãã ãã®è³æã®æåã®éšåã«æ»ããšãéçºè
ããšã©ãŒãçºèŠããå²åã¯ã250,4 åããã 1 ã¬ã³ãŒãããã§ãã¯ããå ŽåãXNUMX ã¬ã³ãŒãããã XNUMX ãšã©ãŒä»¥äžã§ããå¿
èŠãããããšãå€æããŸããã ãããã£ãŠãã·ã¥ãŒãã³ ã¢ãã«ã®ã©ã ãã®èšçå€ã¯æ¬¡ã®ããã«ãªããŸãã
60 / 250,4 = 0,239617ã
ã€ãŸããã©ã ããæ¢åã® 38,964 ãã 0,239617 ã«æžå°ãããŸã§ããšã©ãŒæ€åºæé ãå®è¡ããå¿ èŠããããŸãã
ãŸãã¯ãææš N (æœåšçãªãšã©ãŒæ°) ãã n (ä¿®æ£ããããšã©ãŒæ°) ãåŒããå€ãã(æåã®éšåã§) åãå ¥ãããããå€ - 1459 åãäžåããŸã§æžå°ããŸãã
åºæïŒ habr.com