Excel公式应用大全.xls

查查询询总总目目录录公式1=VLOOKUP(甲一,甲一,111;甲三,222;甲五,333,2,0)公式1111公式1222公式1333公式1最后一个参数0或写False，也可以省略0或False，但逗号不能省略。公式1公式2=VLOOKUP(甲一,甲一,111;甲三,222;甲五,333,2,)公式2111公式2222公式2333公式2=VLOOKUP($G17,$B$17:$E$20,2,0)=IF(ISNA(VLOOKUP($G17,$B$17:$E$20,2,0),VLOOKUP($G17,$B$17:$E$20,2,FALSE)产品数量单价金额产品数量AA100022000AA1000BB200024000BB2000CC300026000CC3000VV400028000VV4000部部门门输输入入查查询询姓名部门入厂日期职务丝丝印印姓名丝印-1AA丝印2006/5/1主管丝印-1AA丝印-2BB丝印2006/5/2员工丝印-2BB丝印-3CC丝印2006/5/3员工丝印-3CC丝印-4DD丝印2006/5/4员工丝印-4DD办公-1EE办公2006/5/5主管丝印-5办公-2FF办公2006/5/6员工丝印-6包装-1GG包装2006/5/7主管丝印-7包装-2HH包装2006/5/8员工丝印-8包装-3JJ包装2006/5/9员工丝印-9包装-4KK包装2006/5/10员工丝印-10单价金额22000240002600028000部门入厂日期职务丝印丝印2006/5/1主管办公丝印2006/5/2员工包装丝印2006/5/3员工丝印2006/5/4员工=IF(ISNA(VLOOKUP($G17,$B$17:$E$20,2,0),VLOOKUP($G17,$B$17:$E$20,2,FALSE)部部门门输输入入查查询询查查询询总总目目录录公式1a11a18=SUMIF($B$2:$B$6,E2,C$2)公式1a12公式1a23公式1a44a18=SUMIF(A3:A7,D5,B3:B7)公式1a15公式1第第十十期期函函数数题题：彩彩票票号号码码生生成成器器最大值36将最大值调小，便于验证。题目：请在蓝色区域中，随机生成10组36选7的彩票号码1234567一11353029182126二1418263530428三8918121296四1682827242620五853116232126六83519142226七2325216352422八3031151232178九1535217271319十142833163011要求：1使用函数与公式，必要时可以添加辅助单元格；2兰色区域只能填入一个多单元格公式，或是可以拖曳复制的单个单元格公式；3每组的7个号码不能重复，如下列的情况是不正确的：131161616161723364不能出现两组号码完全相同：131151617233613115161723365不能出现上下组号码间有规律继承的情况，如下例：12232324241717262629292323242417172626292934344 417172626292934344 48216号码必须在1-36之间的随机数；7按F9键可更新一组号码。评分：1所有要求都实现，可得2分；2方法新颖且高效的，可加获加分，1个论坛ID最多可得5分。总总目目录录按F9键第第十十期期函函数数题题：彩彩票票号号码码生生成成器器总总目目录录题目：按F9键请在蓝色区域中，随机生成10组36选7的彩票号码1234567一2434321611285二733241251116三1306138323四14132831172432五302127112146六4161832281219七18161012222013八2119614163034九26872911116十634141922331512345670.2457205 0.1032075 0.1072196 0.4179911 0.6524237 0.1653392 0.87051970.8045057 0.0754697 0.3025724 0.9817176 0.26858340.741837 0.62648590.8823074 0.1370067 0.7666986 0.4901518 07422458 0.0943878 0.80059650.5935122 0.6476501 0.2849149 0.0998665 0.4948109 0.3931046 0.08683040.0965304 0.2282564 0.1324991 0.9795441 0.6343317 0.56912870.81913270.9016652 0.6448578 0.5804871 0.1632036 0.3169515 0.75011490.55509970.53506080.570042 0.73826380.692282 0.39009240.504099 0.684887370.40018 0.4462415 0.7889435 0.5018396 0.4681517 0.1413201 0.036508770.2499714 0.7286578 0.7604438 0.1929776 0.9662996 0.6909575 0.621185170.854114 0.0670883 0.6226983 0.5690904 0.5057019 0.0954369 0.61216357777789101112131415160.1797565 0.31277580.15684 0.1044234 0.1193489 0.8123057 0.3943373 0.9039779 0.37890620.0856537 0.6551132 0.3820511 0.2144827 0.7464851 0.2110538 0.4727249 0.35114270.6200910.3769964 0.0937996 0.3636037 0.7841772 0.4269406 0.2573409 0.6828729 0.6624743 0.84143650.0414993 0.6865144 0.3742187 0.4741828 0.3638813 0.81113950.402309 0.4323327 0.74050310.1192091 0.4765066 0.3953211 0.8464256 0.6428995 0.0313313 0.1975549 0.0992385 0.39223360.5443895 0.2097616 0.8223757 0.1584656 0.37039210.438775 0.0962316 0.0092494 0.36343370.0392178 0.9871133 0.3248632 0.19877430.075352 0.2566612 0.0691231 0.13338770.7614720.0314243 0.8740886 0.0243412 0.0400127 0.3586994 0.5075415 0.3935695 0.3408153 0.65912590.6896874 0.1984818 0.5101389 0.5023728 0.0012541 0.8950482 0.67311380.414647 0.89128370.763875 0.2999417 0.9486858 0.0359116 0.1126307 0.5783167 0.6562785 0.5844949 0.27680531718192021222324250.5168537 0.8048456 0.2326677 0.2223647 0.4397427 0.4130295 0.2749948 0.9097918 0.43480570.2630414 0.9797111 0.7917865 0.0483056 0.90162770.936586 0.7900244 0.70600990.3028240.3881974 0.0004458 0.6149511 0.2926899 0.4641534 0.22711840.768801 0.0617485 0.34358780.7378995 0.4748202 0.79705860.933929 0.4083193 0.7958886 0.5050791 0.1491458 0.71813330.780707 0.76696420.052644 0.0398311 0.1346294 0.0148113 0.9177581 0.7729372 0.21502150.75802870.829375 0.5550624 0.7801409 0.4085738 0.8349154 0.3047462 0.0454984 0.54192140.3830176 0.5659643 0.73500980.087062 0.91071350.293663 0.9850636 0.2957844 0.41861630.7047081 0.4658752 0.5749864 0.3899643 0.1635305 0.5769667 0.8817115 0.5391668 0.13925780.27729350.493507 0.2508508 0.6384443 0.0101051 0.67329680.861884 0.3109192 0.04892160.4045016 0.6087329 0.7899456 0.9648702 0.2485585 0.4577951 0.88287530.831841 0.17027042627282930313233340.83942970.911343 0.1255171 0.2785441 0.66034740.591048 0.3467935 0.9337904 0.03543060.6476931 0.0471072 0.6342552 0.06231930.378168 0.18812490.186157 0.3459607 0.83932330.180183 0.2452315 0.1575257 0.35962880.747485 0.40860220.102016 0.4626836 0.73411090.0245925 0.2051802 0.56783270.889363 0.41964340.711476 0.0300731 0.0239157 0.87035270.1478646 0.0383395 0.4060276 0.0839304 0.5870095 0.2731564 0.9653624 0.2382198 0.16883370.6258633 0.6785438 0.9411559 0.9490094 0.8386907 0.30245120.483355 0.8679334 0.95888570.6595764 0.3462649 0.8416253 0.61100910.161039 0.8983682 0.1463864 0.9269409 0.87740480.8920029 0.2142036 0.5005132 0.8261666 0.7336571 0.9848426 0.1841248 0.4563486 0.43416960.79629650.164286 0.3275065 0.4017319 0.7129318 0.8763323 0.1327363 0.7092985 0.20251130.9020927 0.9740704 0.16146080.706654 0.4795301 0.3768334 0.5448586 0.7082267 0.553146335360.0271199 0.78391420.2119044 0.81255070.0214315 0.28269250.3711321 0.84746860.7245063 0.87553460.7080507 0.69189890.7514785 0.52811310.0453637 0.39524280.1663819 0.14025950.6780219 0.0161679第第十十期期函函数数题题：彩彩票票号号码码生生成成器器总总目目录录题目：按F9键请在蓝色区域中，随机生成10组36选7的彩票号码1234567一81819246107二21131621257三1814102851727四123319218265五291214214322六2443665819七331915427632八1532142129734九275291720233十7163012232240.88655670.622352 0.5763489 0.4343213 0.8897483 0.7846519 0.88726420.9857661 0.7713823 0.0685626 0.8896115 0.4342325 0.2425021 0.87909630.4654583 0.6081993 0.8186437 0.2116567 0.9410797 0.4691482 0.22251950.8910767 0.1513913 0.7466338 0.7037926 0.9436293 0.4819784 0.96379820.223493 0.5662626 0.5527067 0.3453716 0.8423885 0.9099225 0.343402370.2843654 0.8793115 0.0346226 0.84086690.8506980.802366 0.419075970.1633641 0.6420306 0.68910670.90807 0.3636546 0.8419953 0.173440470.6050689 0.0897661 0.6753845 0.4524235 0.1567432 0.8958714 0.065624570.2523884 0.8655511 0.1881122 0.6022644 0.5188123 0.9403592 0.100454570.87562890.68684 0.1948118 0.7678719 0.45528730.934674 0.4259689777770.8847744 0.63350430.319889 0.7116915 0.7223367 0.9743899 0.5090667 0.4312203 0.42770460.1176067 0.8350826 0.0309942 0.4285038 0.1890456 0.4067433 0.0576667 0.6527684 0.85794870.4503331 0.0607067 0.9811086 0.0503714 0.1588084 0.2384446 0.28193320.856814 0.26987550.5108314 0.8616194 0.9985483 0.9875202 0.2413107 0.42500810.793341 0.90455620.568910.75458390.642013 0.4325359 0.3212484 0.1894699 0.2244758 0.2217359 0.95841520.5487890.0990013 0.2906792 0.9909811 0.14802920.201208 0.9484689 0.0775444 0.4145809 0.25626680.7657249 0.3332405 0.9761289 0.6504578 0.7244629 0.7814629 0.8951767 0.6370381 0.29191990.9295971 0.1924483 0.0808367 0.5664274 0.0925106 0.5742386 0.9185903 0.2721318 0.02830910.2691397 0.6731177 0.7456943 0.6342732 0.0477642 0.9865736 0.4850139 0.1285467 0.52244740.07039260.194086 0.5432407 0.1770639 0.8476971 0.9255825 0.0345658 0.2440389 0.41978290.5407061 0.1863492 0.6731635 0.4034641 0.47244740.6973630.991776 0.4820733 0.94160330.4919735 0.7102577 0.6625379 0.9875697 0.98294170.4510780.813528 0.5543669 0.04150080.1439454 0.3778122 0.0894709 0.0627594 0.7905132 0.1412398 0.7867147 0.5274495 0.67849920.1096044 0.7454177 0.5294318 0.9553087 0.9814951 0.9077366 0.8721246 0.9576047 0.43740610.2964886 0.4452708 0.8207268 0.3707925 0.9985126 0.5895422 0.6993506 0.0203544 0.42937910.551948 0.80464570.491906 0.5004224 0.7530334 0.1754439 0.0659636 0.2967781 0.14892610.8261658 0.6797461 0.1474035 0.5400766 0.4526017 0.8094659 0.9206688 0.5148349 0.67771880.6009233 0.8074345 0.7138286 0.5631842 0.4921263 0.0289383 0.9633842 0.9430625 0.41720250.7602363 0.3134434 0.9120199 0.6594709 0.47730240.014666 0.13366280.035084 0.11861360.0051676 0.42563080.564458 0.7527371 0.9728506 0.2901081 0.9342801 0.6124489 0.71117220.27452710934908 0.2914752 0.3669572 0.9297328 0.71864430.206411 0.1259405 0.75748110.3873453 0.9039326 0.0788798 0.0356502 0.1072329 0.9065954 0.6371376 0.1062701 0.76190710.9687778 0.3025071 0.8429717 0.8966816 0.9353341 0.3041847 0.2788713 0.9958861 0.97649590.9027838 0.0722456 0.8715833 0.0057832 0.8821668 0.1733733 0.2711415 0.1988644 0.98164050.4315158 0.5635812 0.2680623 0.7307786 0.1219313 0.0129415 0.69597150.190829 0.02658360.7004459 0.6474173 0.3655504 0.76164150.490264 0.9865735 0.6979357 0.2629202 0.15617410.9747039 0.7530723 0.4822296 0.7277541 0.1778693 0.0012282 0.4867309 0.2308736 0.12431830.2524035 0.7874897 0.7795037 0.2974927 0.90927620.228211 0.1238608 0.8069722 0.33520820.7002734 0.4286026 0.61133890.669909 0.9327995 0.7366171 0.7938362 0.6003552 0.27620810.5994333 0.8512715 0.8157333 0.6404526 0.6060144 0.0503172 0.75181090.884196 0.90120660.2537542 0.32109670.7319827 0.04320590.1116617 0.58584050.4896618 0.79840890.2837224 0.33606290.4473739 0.14550780.8072848 0.55957090.9299549 0.81143270.834885 0.21835230.8152035 0.2459555第第十十期期函函数数题题：彩彩票票号号码码生生成成器器总总目目录录题目：按F9键请在蓝色区域中，随机生成10组36选7的彩票号码1234567一153351111026二203615262236三29161218102627四253282273631五1812836221113六833221262016七287183232726八7152035246九622202410123十242533381110结果验证2.9875144 11.539723 2.3748996 19.014767 7.9853013 17.432259 29.457826 11.62080225.719803 7.5057803 24.577265 30.473238 33.097213 9.7630027 27.317016 27.87658626.53803115.93259 10.063169 34.606956 31.428464 21.016014 28.190667 12.60332511.371113 29.622807 2.6007288 23.319573 35.985963 20.843632 4.2656148 9.36266751.3951581 29.078472 13.023364 9.9638841 9.5445721 22.700316 9.0821539 7.32987545.6499595 7.5801585 1.6301325 4.9966031 20.393715 19.293182 10.718072 1.254346727.394639 15.830755 6.9394846 35.919966 32.838242 19.222201 1.9186928 35.6108851.498809131.52142 34.533404 20.046893 1.6564865 3.7550013 1.3954023 13.51311516.59814411.7185411.32795 26.783946 14.508278 3.0361346 31.830851 31.63345633.197734 13.509782 6.7815229 29.887959 33.87406723.29068 19.3464766.8037427.55869 5.1075154 4.2961079 19.575521 33.370559 31.350332 1.5862844 27.25536333.27316.954421 10.971257 11.98099134.16215 13.532852 31.730764 5.2416165 16.62255213.893937.9394799 5.6068978 11.726165 4.5009915 9.5192142 14.675808 12.938526 3.5397813 34.78823317.903626 17.228901 30.013769 18.870479 32.00923913.06902 13.178752 29.744366 24.27492812.38461812.67928 6.9673337 35.704503 7.0207494 23.044069 12.419921 12.066598 31.36834919.716992 25.597441 16.379869 26.246466 14.330452 8.1547534 7.1234479 3.8929323 13.36684328.26694 16.70283113.27146 27.432836 19.124141 33.847064 21.443335 19.435566 15.44091424.129858 5.2678915 30.269448 18.849972 21.133795 28.759349 26.657396 8.5700203 34.10214730.125734 10.520855 23.414473 10.736718 24.033497 18.238805 17.655156 19.12220511.7743520.691327 9.6379209 7.583141535.30648 21.624382 19.289338 14.148397 25.075008 14.34005813.733631 17.039329 33.295898 19.20489923.81107 15.588102 11.364262 9.4043433 5.322124121.131753 32.541606 2.2271625 10.020033 12.555086 7.6827396 12.00322710.1715 6.46730344.7709176 30.667103 11.696222 32.809108 25.289065 17.493936 23.013936 18.0787925.8215330.799234 35.762212 8.675673825.17567 3.5629567 14.601381 35.628404 2.2859525 22.5471161.2847151 16.98645529.77692 7.8397955 5.809950410.0768317.69536 29.098435 7.058148535.50395 32.312135 3.83159582.327467 26.889917 21.260867 30.570154 5.2057011 2.58511562.2249886 23.645225 24.623165 21.453951 17.934638 22.461034 13.398254 21.527819 11.82410712.506635 3.8254247 2.4824486 28.718231 26.251336 20.330339 3.6220826 28.106673 34.44972828.390429 34.568758 3.9489002 12.269077 3.4057182 13.026281 6.6707648 11.686599 20.13118724.979858 21.425507 15.587856 10.935179 15.394206 11.540332 3.1211683 4.1877176 24.48365721.459566 12.170064 27.339877 5.6321591 31.482079 13.094602 29.388464 22.510362 2.605183611.516303 22.607691 13.57743830.91752 27.661628 15.64964629.14386 13.162021 26.7950556.5625331 11.388653 1.0225132 24.252684 15.752315 18.112591 21.997343 10.661205 26.47858933.397641 2.9391983 21.39956124.05533 8.5644626 25.611162 14.971175 13.210457 20.77830712.8219084.199664 12.191453 16.87025819.21541 21.755711 21.99872410.04458 27.26208416.074001 25.587437 32.249888 26.882536 11.716478 2.0435076 16.133243 16.644255 15.81078110.284671 1.0007195 12.369588 12.646449 20.381489 4.7423575 16.418097 34.901634 14.23489617.90798415.80207 8.6493194 25.961437 18.056232 27.150917 24.786441 25.428409 3.090294234.410183 23.771845 12.743504 21.710159 16.70290222.66254 24.437222 18.657135 11.93986917.916974 19.991936 30.32551633.73957 30.685668 14.903247 5.2392527 13.179563 31.33244411.3857592.993336628.591566.03018515.332892214.5459322.54227724.16822320.28503427.285469在bosco大师的解法偶新增三表是在bosco大师解法基础上发展来的新解法满足指定条件的不重复值满足指定条件模糊查找的不重复值查询条件本例按条件模糊查找的不重复值名称年度W2005名称下拉框ABC2004/1/1WVUCDE2003/1/1WGHWGH2005/6/1WVULMN2003/1/2OPQ2004/1/3QSTUVW2007/1/8XYZ2001/1/2ZYX2005/1/9WVU2005/1/6可以处理多种数据类型总总目目录录偶新增三表是在bosco大师解法基础上发展来的新解法总总目目录录9本例抽取不连续单元的不重复值本例抽取只有数值的不重复值数据辅助列下拉框数据辅助列下拉框1111dfsdfdfsdfdfsdf2232925954535美丽343舒服美丽美丽1122455舒服舒服44C4=SUMPRODUCT(C$5:C$22)/COUNTIF(C$5:C$22,C$5:C$22&)*(C$5:C$22)D4=D4=MATCH(1,1/($C$3:$C$20)C列=IF(ROW()-ROW(D$4)=C$4,INDEX(C$5:C$22,MATCH(1,-ISNA(MATCH(C$5:C$22,D$4:D4,0)*(C$5:C$22),0),)D列有效性序列=OFFSET($D$5,0,0,$E$4,)每例橙色部分不同这三个灰色单元格有奥妙哦本例抽取字符串长度大于1的不重复值以此类推发挥你的想象吧。数据辅助列下拉框1dfsdfdfsdf23美丽2舒服233美丽125舒服4C4=SUMPRODUCT(C$5:C$22)/COUNTIF(C$5:C$22,C$5:C$22&)*(C$5:C$22)C列=IF(ROW()-ROW(D$4)=C$4,INDEX(C$5:C$22,MATCH(1,-ISNA(MATCH(C$5:C$22,D$4:D4,0)*(C$5:C$22),0),)品名希望的结果总总目目录录A是1 AAB和2 BBCC3 CC和B4和是A5是是#N/A5品名希望的结果A36511B26622林149622331林49AA2B50BB3A51品名希望的结果5800110000.50.500.5110.50551000188851000筛选数据是数值第(6)种公式解法12数据1123请用函数法筛选出不重复值982456893781234980 055622312745111878978994510111223111278125456131489150 01612317891898数据区域如果出现 0 的话,总总目目录录第(6)种公式解法,公式能返回 0第(7)种公式解法,公式返回#N/A第(7)种公式解法12辅助列98891235121117894522378456#N/A#N/A第(6)种公式解法,公式能返回 0第(7)种公式解法,公式返回#N/A筛选数据是文本第(6)种公式解法13数据1机关事务三楼请用函数法筛选出不重复值湖塘长虹路上海大众汽车武进特约维修站2新苑宾馆湖塘镇花东社区活动中心3湖塘镇鸣凰邮电局办公楼东南汽车销售服务公司常州分公司4湖塘镇开发区远东安装公司湖塘工商银行支行5湖塘镇东兴郭家村周国兴家武进丰乐公寓大门口物管楼顶6湖塘华家站西武进国力摩托车配件厂办公楼7湖塘牛巷头/蒋建忠后楼前阳台湖塘牛巷头/蒋建忠后楼前阳台8湖塘华家站西9武进国力摩托车配件厂办公楼湖塘镇东兴郭家村周国兴家10武进丰乐公寓大门口物管楼顶湖塘镇开发区远东安装公司11湖塘镇鸣凰邮电局办公楼12湖塘工商银行支行新苑宾馆13东南汽车销售服务公司常州分公司机关事务三楼14东南汽车销售服务公司常州分公司15湖塘镇花东社区活动中心16湖塘镇花东社区活动中心17湖塘长虹路上海大众汽车武进特约维修站18湖塘长虹路上海大众汽车武进特约维修站0总总目目录录第6种,公式解法,是:=LOOKUP(2,1/(COUNTIF(D$2:D2,$B$3:$B$100&)=0),$B$3:$B$100)第7种,公式解法,是:=LOOKUP(2,1/(-(ISNA(MATCH($B$4:$B$100,F$3:F3,0)=1),$B$4:$B$100)如果筛选数据是文本,第6种&第7种公式,能返回正确第(7)种公式解法13辅助列湖塘长虹路上海大众汽车武进特约维修站湖塘镇花东社区活动中心东南汽车销售服务公司常州分公司湖塘工商银行支行武进丰乐公寓大门口物管楼顶武进国力摩托车配件厂办公楼湖塘牛巷头/蒋建忠后楼前阳台湖塘华家站西湖塘镇东兴郭家村周国兴家湖塘镇开发区远东安装公司湖塘镇鸣凰邮电局办公楼新苑宾馆机关事务三楼=LOOKUP(2,1/(COUNTIF(D$2:D2,$B$3:$B$100&)=0),$B$3:$B$100)=LOOKUP(2,1/(-(ISNA(MATCH($B$4:$B$100,F$3:F3,0)=1),$B$4:$B$100)如果筛选数据是文本,第6种&第7种公式,能返回正确工艺品合格公式1公式2总总目目录录衣服肉食品合格鞋类鞋类合格奶粉类合格化妆品洗衣机化妆品合格奶粉类空调机鞋类合格肉食品化妆品合格奶粉类合格肉食品合格OKOK倒排式顺排式利用公式从A列筛选出合格类产品,结果如C列所示.总总目目录录姓名数值其它说明1姓名数值其它说明2其它说明3姓名数值公式1数值名次赵11140赵31790赵611020赵814111赵21200赵32790赵621020赵513382赵31200赵33670赵631020赵613383赵41200赵34670赵64790赵713384赵51338赵35670赵65790赵913385赵61338赵361260赵66790赵1113386赵71338赵371260赵67790赵1213387赵81411赵381020赵68790赵1313388赵91338赵391020赵69972赵1412659赵10