{"id":4026,"date":"2013-11-09T12:00:54","date_gmt":"2013-11-09T03:00:54","guid":{"rendered":"http:\/\/fujiitoshiki.com\/improvesociety\/?p=4026"},"modified":"2014-07-31T13:43:28","modified_gmt":"2014-07-31T04:43:28","slug":"post-4026","status":"publish","type":"post","link":"https:\/\/www.fujiitoshiki.com\/improvesociety\/?p=4026","title":{"rendered":"\u591a\u5909\u91cf\u89e3\u6790\u306e\u591a\u91cd\u5171\u7dda\u6027\u3092\u8abf\u3079\u308b\u305f\u3081\u306b\u76f8\u95a2\u884c\u5217\u304b\u3089\u504f\u76f8\u95a2\u884c\u5217\u3092Excel\u3067\u6c42\u3081\u308b\u65b9\u6cd5"},"content":{"rendered":"

\u3000\u591a\u5909\u91cf\u89e3\u6790\u306b\u304a\u3044\u3066\u5909\u6570\u9593\u306e\u591a\u91cd\u5171\u7dda\u6027\u3092\u8abf\u3079\u308b\u65b9\u6cd5\u306e\u4e00\u3064\u306b\u504f\u76f8\u95a2\u884c\u5217\u304c\u3042\u308a\u307e\u3059\uff0e\u76f8\u95a2\u884c\u5217\u3092 R = (rij<\/sub>) \u3068\u3057\uff0c\u305d\u306e\u9006\u884c\u5217\u3092 R-1<\/sup> = (rij<\/sup>) \u3068\u3059\u308b\u3068\uff0cxi<\/sub> \u3068 xj<\/sub> \u4ee5\u5916\u306e\u3059\u3079\u3066\u306e\u5909\u6570\u3092\u4e0e\u3048\u305f\u6642\u306e xi<\/sub> \u3068 xj<\/sub> \u306e\u504f\u76f8\u95a2\u4fc2\u6570 rij rest<\/sub> \u306f\u4e0b\u5f0f\u3067\u8868\u73fe\u3067\u304d\u307e\u3059\uff0e<\/p>\n

\\displaystyle r_{ij\\cdot rest} = - \\frac{r^{ij}}{\\sqrt{r^{ii}r^{jj}}}<\/p>\n

\u3000\u9006\u884c\u5217\u306e\u5bfe\u5fdc\u3059\u308b\u8981\u7d20\u30922\u3064\u306e\u5bfe\u89d2\u8981\u7d20\u306e\u7a4d\u306e\u5e73\u65b9\u6839\u3067\u5272\u3063\u3066\u57fa\u6e96\u5316\u3057\uff0c\u7b26\u53f7\u3092\u53cd\u8ee2\u3057\u307e\u3059\uff0e\u3053\u306e\u504f\u76f8\u95a2\u4fc2\u6570\u3092\u5168\u3066\u306e\u5909\u6570\u306e\u5bfe\u306b\u3064\u3044\u3066\u884c\u5217\u306e\u5f62\u306b\u307e\u3068\u3081\u305f\u3082\u306e\u3092\u504f\u76f8\u95a2\u884c\u5217\u3068\u8a00\u3044\u307e\u3059\uff0e<\/p>\n

\\displaystyle R=\\left( \\begin{array} {cccccc} 1 \\\\ r_{21} & 1 \\\\ \\vdots & \\ddots & 1 \\\\ r_{i1} & \\ldots & r_{ij} & 1 \\\\ \\vdots & & \\vdots & \\ddots & 1 \\\\ r_{n1} & \\ldots & r_{nj} & \\ldots & r_{nn-1} & 1 \\\\ \\end{array} \\right)<\/p>\n

\\displaystyle R^{-1}=\\left( \\begin{array} {cccccc} r^{11} \\\\ r^{21} & \\ddots \\\\ \\vdots & \\ddots & r^{jj} \\\\ r^{i1} & \\ldots & r^{ij} & r^{ii} \\\\ \\vdots & & \\vdots & \\ddots & \\ddots \\\\ r^{n1} & \\ldots & r^{nj} & \\ldots & r^{nn-1} & r^{nn} \\\\ \\end{array} \\right)<\/p>\n

\u3000\u76f8\u95a2\u884c\u5217\u3068\u504f\u76f8\u95a2\u884c\u5217\u306e\u7b26\u53f7\u304c\u4e00\u81f4\u3057\u306a\u3044\u5834\u5408\u306f\u591a\u91cd\u5171\u7dda\u6027\u306e\u53ef\u80fd\u6027\u304c\u3042\u308a\u307e\u3059\uff0e\u307e\u305f\uff0c\u5909\u6570\u9593\u306b\u7dda\u5f62\u306e\u95a2\u4fc2\u304c\u3042\u308b\u5834\u5408\u306f\u76f8\u95a2\u884c\u5217\u306e\u9006\u884c\u5217\u304c\u6c42\u307e\u3089\u306a\u3044\u3053\u3068\u3082\u3042\u308a\u307e\u3059\uff0e<\/p>\n

\u3000Excel\u3067\u504f\u76f8\u95a2\u884c\u5217\u3092\u6c42\u3081\u308b\u65b9\u6cd5\u306f\u4e0b\u8a18\u306e\u901a\u308a\u3067\u3059\uff0e\u65e2\u306b\u76f8\u95a2\u884c\u5217\u306f\u6c42\u307e\u3063\u3066\u3044\u308b\u3082\u306e\u3068\u3057\u307e\u3059\uff0e<\/p>\n

    \n
  1. \u76f8\u95a2\u884c\u5217\u306e\u9006\u884c\u5217\u3092\u6c42\u3081\u308b<\/li>\n
  2. \u9006\u884c\u5217\u306e\u5404\u8981\u7d20\u30922\u3064\u306e\u5bfe\u89d2\u8981\u7d20\u306e\u7a4d\u306e\u5e73\u65b9\u6839\u3067\u5272\u308a\uff0c\u7b26\u53f7\u3092\u9006\u8ee2\u3059\u308b<\/li>\n<\/ol>\n

     <\/p>\n\n\n<\/colgroup>\n\n\n\n\n\n
    \u3000<\/span><\/td>\nA<\/span><\/td>\nB<\/span><\/td>\nC<\/span><\/td>\n<\/tr>\n
    1<\/span><\/td>\n1.000<\/span><\/td>\n0.800<\/span><\/td>\n0.300<\/span><\/td>\n<\/tr>\n
    2<\/span><\/td>\n0.800<\/span><\/td>\n1.000<\/span><\/td>\n-0.700<\/span><\/td>\n<\/tr>\n
    3<\/span><\/td>\n0.300<\/span><\/td>\n-0.700<\/span><\/td>\n1.000<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    1. \u76f8\u95a2\u884c\u5217\u306e\u9006\u884c\u5217\u3092\u6c42\u3081\u308b<\/h3>\n

    \u3000\u9006\u884c\u5217\u3092\u6c42\u3081\u308b\u30ef\u30fc\u30af\u30b7\u30fc\u30c8\u95a2\u6570\u306f Excel \u306b\u6a19\u6e96\u88c5\u5099\u3055\u308c\u3066\u3044\u307e\u3059\uff0eMINVERS \u95a2\u6570\u3092\u7528\u3044\u308b\u6642\u306e\u6ce8\u610f\u70b9\u3068\u3057\u3066\uff0c\u95a2\u6570\u306e\u5f15\u6570\u3068\u3057\u3066\u76f8\u95a2\u884c\u5217\u3092\u6307\u5b9a\u3057\uff0c\u78ba\u5b9a\u3059\u308b\u969b\u306b Control \u30ad\u30fc\u3068 Shift \u30ad\u30fc\u3068 Enter \u30ad\u30fc\u3092\u540c\u6642\u306b\u62bc\u4e0b\u3059\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\uff0e<\/p>\n

    {=MINVERS($A$1:$C$3)}<\/span><\/p>\n\n\n<\/colgroup>\n\n\n\n\n\n
    \u3000<\/span><\/td>\nA<\/span><\/td>\nB<\/span><\/td>\nC<\/span><\/td>\n<\/tr>\n
    5<\/span><\/td>\n-0.197<\/span><\/td>\n1.817<\/span><\/td>\n1.547<\/span><\/td>\n<\/tr>\n
    6<\/span><\/td>\n1.817<\/span><\/td>\n-1.637<\/span><\/td>\n-1.691<\/span><\/td>\n<\/tr>\n
    7<\/span><\/td>\n1.547<\/span><\/td>\n-1.691<\/span><\/td>\n-0.647<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    2. \u9006\u884c\u5217\u306e\u5404\u8981\u7d20\u30922\u3064\u306e\u5bfe\u89d2\u8981\u7d20\u306e\u7a4d\u306e\u5e73\u65b9\u6839\u3067\u5272\u308a\uff0c\u7b26\u53f7\u3092\u9006\u8ee2\u3059\u308b<\/h3>\n

    \u3000\u6c42\u307e\u3063\u305f\u9006\u884c\u5217\u306e\u5404\u8981\u7d20\u304b\u30892\u3064\u306e\u5bfe\u89d2\u8981\u7d20\u306e\u30a2\u30c9\u30ec\u30b9\u3092\u6c42\u3081\u308b\u306b\u306f\u5c11\u3005\u5de5\u592b\u304c\u5fc5\u8981\u3067\u3059\uff0eINDEX \u95a2\u6570\u3068 ROW \u95a2\u6570\u304a\u3088\u3073 COLUMN \u95a2\u6570\u3092\u7d44\u307f\u5408\u308f\u305b\u307e\u3059\uff0e\u4e0b\u5f0f\u3092\u8a72\u5f53\u30bb\u30eb\u306b\u30da\u30fc\u30b9\u30c8\u3057\u307e\u3059\uff0eROW \u95a2\u6570\uff08\u304a\u3088\u3073 COLUMN \u95a2\u6570\uff09\u304b\u3089\u5dee\u3057\u5f15\u3044\u3066\u3044\u308b\u6570\u5024\u306f INDEX \u95a2\u6570\u306e\u7b2c 1 \u5f15\u6570\u306e 2 \u6b21\u5143\u914d\u5217\u306e\u884c\u756a\u53f7\uff08\u3068\u5217\u756a\u53f7\uff09\u3092\u6307\u5b9a\u3059\u308b\u3082\u306e\u3067\u3059\u306e\u3067\uff0c\u72b6\u6cc1\u306b\u3088\u3063\u3066\u6570\u5024\u306f\u5909\u5316\u3057\u307e\u3059\uff0e\u5404\u81ea\u3067\u5bfe\u5fdc\u3057\u3066\u4e0b\u3055\u3044\uff0e<\/p>\n

    =-INDEX($A$5:$C$7, ROW()-8,COLUMN())\/SQRT(INDEX($A$5:$C$7, ROW()-8, ROW()-8)*INDEX($A$5:$C$7, COLUMN(),COLUMN()))<\/span><\/p>\n\n\n<\/colgroup>\n\n\n\n\n\n
    \u3000<\/span><\/td>\nA<\/span><\/td>\nB<\/span><\/td>\nC<\/span><\/td>\n<\/tr>\n
    9<\/span><\/td>\n1.000<\/span><\/td>\n-1.483<\/span><\/td>\n-2.007<\/span><\/td>\n<\/tr>\n
    10<\/span><\/td>\n-1.483<\/span><\/td>\n1.000<\/span><\/td>\n1.642<\/span><\/td>\n<\/tr>\n
    11<\/span><\/td>\n-2.007<\/span><\/td>\n1.642<\/span><\/td>\n1.000<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n