Excel-2000计算毒力回归线和卡方表

编号浓度（μg/ml）剂量剂量对数x试虫数死虫数死亡率校正死亡率观察机率值y10.0000#NUM!#DIV/0!#DIV/0!#DIV/0!20.0000#NUM!#DIV/0!#DIV/0!#DIV/0!30.0000#NUM!#DIV/0!#DIV/0!#DIV/0!40.0000#NUM!#DIV/0!#DIV/0!#DIV/0!50.0000#NUM!#DIV/0!#DIV/0!#DIV/0!CK#DIV/0!求和0.00000.0000计算结果X均值Y均值abmLD50SmLD50下限#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!毒力测定与卡方检验计算表(0.497微升)药剂：供试昆虫：日期：备注：B列中的浓度单位应为微克/毫升，如有不同，均应换算为该单位。当百分比浓度带入本程序时，应×10000倍后输入B列。例如，当浓度为0.025%时，应为0.025%×1，000，000=250（微克/头），在B3中输入250即可；药剂浓度为毫克/毫升时，应×1000倍后输入B列，如0.0625(毫克/毫升）×1000=62.5(微克/毫升）输入。C3公式中的点滴器体积改为相应的体积值。权重系数wnwnwxnwx2nwxynwynwy2理论机率值理论死亡率#DIV/0!#DIV/0!#NUM!#NUM!#NUM!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#NUM!#NUM!#NUM!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#NUM!#NUM!#NUM!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#NUM!#NUM!#NUM!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#NUM!#NUM!#NUM!#DIV/0!#DIV/0!#DIV/0!#DIV/0!0.00000.00000.00000.00000.00000.0000#DIV/0!LD50上限相关系数rX2X20.05(df=3)#DIV/0!#NUM!#DIV/0!#DIV/0!毒力测定与卡方检验计算表(0.497微升)药剂：供试昆虫：日期：理论死虫数校正死虫数#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!毒力测定与卡方检验计算表(0.497微升)药剂：供试昆虫：日期：编号浓度（μg/ml）剂量剂量对数x试虫数死虫数死亡率校正死亡率观察机率值y11250.0621-1.206710089.120.89120.89126.2329262.50.0311-1.507810078.810.78810.78815.7998331.250.0155-1.808810069.190.69190.69195.5012415.6250.0078-2.109810049.830.49830.49834.995757.81250.0039-2.410910035.620.35620.35624.6314CK#DIV/0!求和-9.044027.1611计算结果X均值Y均值abmLD50SmLD50下限-1.87925.33887.84681.3346-2.13300.00740.05400.0058毒力测定与卡方检验计算表(0.497微升)药剂：供试昆虫：日期：备注：B列中的浓度单位应为微克/毫升，如有不同，均应换算为该单位。当百分比浓度带入本程序时，应×10000倍后输入B列。例如，当浓度为0.025%时，应为0.025%×1，000，000=250（微克/头），在B3中输入250即可；药剂浓度为毫克/毫升时，应×1000倍后输入B列，如0.0625(毫克/毫升）×1000=62.5(微克/毫升）输入。C3公式中的点滴器体积改为相应的体积值。当对照死亡率为零时使用本方法。权重系数wnwnwxnwx2nwxynwynwy2理论机率值理论死亡率0.358935.8949-43.315652.2703########223.7304#######6.23630.89180.502750.2651-75.7879114.2702########291.5297#######5.83450.79800.580758.0726#########189.9980########319.4712#######5.43280.66740.636663.6616#########283.3802########318.0365#######5.03100.51240.605860.5840#########352.1275########280.5869#######4.62920.35542.6848268.4781#########992.0462#######################LD50上限相关系数rX2X20.05(df=3)0.00940.99780.4126符合毒力测定与卡方检验计算表(0.497微升)药剂：供试昆虫：日期：理论死虫数校正死虫数89.182589.120079.800778.810066.740369.190051.236049.830035.540035.6200毒力测定与卡方检验计算表(0.497微升)药剂：供试昆虫：日期：编号浓度剂量剂量对数x试虫数死虫数死亡率校正死亡率观察机率值y162.50.0025-2.60216090.15000.12073.828521250.0050-2.301050190.38000.35864.637932500.0100-2.000060330.55000.53455.086545000.0200-1.699068490.72060.71105.5562510000.0400-1.397959560.94920.94746.6201CK6020.0333求和-10.000025.7292计算结果X均值Y均值abmLD50SmLD50下限-2.02105.06419.16502.0292-2.05260.00890.04130.0074SAS数据9.16492.0291-2.052570.00890.04130.0074毒力测定与卡方检验计算表药剂：供试昆虫：日期：权重系数wnwnwxnwx2nwxynwynwy2理论机率值0.380122.8077-59.3469154.4243-227.207087.3181334.29353.88500.606930.3446-69.8239160.6668-323.8327140.7338652.70264.49580.634938.0933-76.1867152.3734-387.5269193.7635985.58635.10670.568438.6528-65.6699111.5713-364.8735214.76161193.25225.71750.231413.6516-19.084126.6784-126.339590.3755598.29876.32842.4217143.5500-290.1116605.7142-1429.7797726.95253764.1333LD50上限相关系数rX2X20.05(df=3)0.01070.98702.8689符合0.0107毒力测定与卡方检验计算表药剂：供试昆虫：日期：理论死亡率理论死虫数校正死虫数0.13247.94547.24140.307115.353617.93100.542532.548632.06900.763551.916148.34480.908053.570255.8966毒力测定与卡方检验计算表药剂：供试昆虫：日期：编号浓度剂量剂量对数x试虫数死虫数死亡率校正死亡率观察机率值y162.50.0025-2.60216090.15000.12073.828521250.0050-2.301050190.38000.35864.637932500.0100-2.000060330.55000.53455.086545000.0200-1.699068490.72060.71105.5562510000.0400-1.397959560.94920.94746.6201CK6020.0333求和-10.000025.7292计算结果X均值Y均值abmLD50SmLD50下限-2.00005.14589.46552.1598-2.06750.00860.03900.0072ABCDEFGHI0.950010.957.8157.81472797.815符合沈晋良数据毒力测定与卡方检验计算表药剂：供试昆虫：日期：权重系数wnwnwxnwx2nwxynwy理论机率值理论死亡率理论死虫数0.380122.8077-59.3469154.4243-227.207087.31813.84550.12417.44880.606930.3446-69.8239160.6668-323.8327140.73384.49570.307015.35060.634938.0933-76.1867152.3734-387.5269193.76355.14580.558033.47840.568438.6528-65.6699111.5713-364.8735214.76165.79600.787053.51500.231413.6516-19.084126.6784-126.339590.37556.44620.925954.63022.4217143.5500-290.1116605.7142-1429.7797726.9525LD50上限相关系数rX2X20.05(df=3)0.01020.98703.5080符合JKLM符合0.0390毒力测定与卡方检验计算表药剂：供试昆虫：日期：校正死虫数卡方X2x2xy7.24140.00666.7707-9.961917.93100.62595.2947-10.671832.06900.13424.0000-10.173148.34482.34492.8865-9.439855.89660.39631.9542-9.254620.9062-49.5011毒力测定与卡方检验计算表药剂：供试昆虫：日期：计算方法回归方程（Y=）LD50及95%置信限卡方∑X2最小二乘法9.4579+2.1560x0.0086(0.0072～0.0102)3.4351最小二乘法（excel软件）9.4655+2.1598x0.0086(0.0072～0.0102)3.5080机率值分析法9.2004+2.0445x0.0088(0.0072～0.0106)3.0503机率值分析法（excel软件）9.1650+2.0292x0.0089(0.0074～0.0107)2.8689SAS软件BA系统9.2134+2.0509x0.0088(0.0073～0.0106)2.8904POLO软件表1几种方法的计算结果比较标准误Sm数据来源0.0359文献20.0390图20.0414文献20.0413图3文献2表1几种方法的计算结果比较编号浓度(μg/ml)剂量(μg/头)logc试虫数死虫数死亡率校正死亡率10#NUM!#DIV/0!#DIV/0!20#NUM!#DIV/0!#DIV/0!30#NUM!#DIV/0!#DIV/0!40#NUM!#DIV/0!#DIV/0!50#NUM!#DIV/0!#DIV/0!60#NUM!#DIV/0!#DIV/0!70#NUM!#DIV/0!#DIV/0!80#NUM!#DIV/0!#DIV/0!CK#DIV/0!求和0计算结果X均值Y均值abmLD50Sm#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!#DIV/0!