Excel公式应用大全.xls

查查询询总总目目录录 公式1VLOOKUP“甲一“,{“甲一“,111;“甲三“,222;“甲五“,333},2,0 公式1111 公式1222 公式1333 公式1最后一个参数0或写False，也可以省略0或False，但逗号不能省略。 公式1 公式2VLOOKUP“甲一“,{“甲一“,111;“甲三“,222;“甲五“,333},2, 公式2111 公式2222 公式2333 公式2 VLOOKUPG17,B17E20,2,0 IFISNAVLOOKUPG17,B17E20,2,0,““,VLOOKUPG17,B17E20,2,FALSE 产品数量单价金额产品数量单价 AA100022000AA10002 BB200024000BB20002 CC300026000CC30002 VV400028000VV40002 部部门门输输入入查查询询 姓名部门入厂日期职务丝丝印印姓名部门 丝印-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 金额 2000 4000 6000 8000 入厂日期职务丝印 2006/5/1主管办公 2006/5/2员工包装 2006/5/3员工 2006/5/4员工 IFISNAVLOOKUPG17,B17E20,2,0,““,VLOOKUPG17,B17E20,2,FALSE 查查询询总总目目录录 公式1a11a18SUMIFB2B6,E2,C2 公式1a12 公式1a23 公式1a44a18SUMIFA3A7,D5,B3B7 公式1a15 公式1 第第十十期期函函数数题题彩彩票票号号码码生生成成器器最大值36 将最大值调小，便于验证。 题目 请在蓝色区域中，随机生成10组36选7的彩票号码 1234567 一15131830343233 二2472209194 三23373511364 四12202231321936 五11223125171326 六16223626181512 七101372133411 八27492582228 九1127342530617 十5432272033 要求 1 使用函数与公式，必要时可以添加辅助单元格； 2 兰色区域只能填入一个多单元格公式，或是可以拖曳复制的单个单元格公式； 3 每组的7个号码不能重复，如下列的情况是不正确的 13116161616172336 4 不能出现两组号码完全相同 1311516172336 1311516172336 5 不能出现上下组号码间有规律继承的情况，如下例 1223232424171726262929 2323242417172626292934344 4 17172626292934344 4821 6 号码必须在1-36之间的随机数； 7 按F9键可更新一组号码。 评分 1 所有要求都实现，可得2分； 2 方法新颖且高效的，可加获加分，1个论坛ID最多可得5分。 总总目目录录 按F9键 第第十十期期函函数数题题彩彩票票号号码码生生成成器器总总目目录录 题目按F9键 请在蓝色区域中，随机生成10组36选7的彩票号码 1234567 一528328301318 二1882630251216 三27627351226 四33152614132019 五352452582023 六2620252315277 七2531282352012 八191221277125 九35333419291217 十2425111952926 1234567 0.92883392 0.19174931 0.12546329 0.89726831 0.18489218 0.60907811 0.50571721 0.55174785 0.65328649 0.36768229 0.25353977 0.37621862 0.58545341 0.56446441 0.35803286 0.94750941 0.96122801 0.93247278 0.08685149 0.88838789 0.39894061 0.1265297 0.54311751 0.26663078 0.55888582 0.65176786 0.37287558 0.37477396 0.11068135 0.58615122 0.93760886 0.54472624 0.88068928 0.70194632 0.60884286 7 0.18862466 0.33612846 0.25808782 0.26847982 0.45889211 0.16154662 0.80573254 7 0.277049150.1919990.24489860.3056421 0.92737819 0.380925810.6014497 7 0.46543762 0.59663666 0.40890198 0.25908316 0.692272090.970494 0.31145847 7 0.16318182 0.26887305 0.21613444 0.57133428 0.34201737 0.81091238 0.72127498 7 0.28326049 0.27352216 0.74651376 0.51442567 0.918665710.1215011 0.21803661 7 7 7 7 7 8910111213141516 0.40314382 0.49455537 0.17707086 0.92844691 0.03963616 0.99182771 0.44791822 0.99925255 0.68280698 0.87760149 0.13250629 0.58171806 0.08683263 0.95035614 0.57985018 0.66193074 0.60000577 0.87748655 0.29641752 0.90887836 0.58799488 0.95373003 0.31641165 0.64515303 0.875643990.8664916 0.92453135 0.80476349 0.28764925 0.33755323 0.72915058 0.379963270.8489931 0.32632731 0.71177655 0.89443267 0.71868023 0.67306377 0.91951946 0.73368314 0.164722940.030135 0.76415565 0.62600765 0.97555803 0.4348813 0.29870565 0.34684516 0.26793751 0.77327814 0.59832385 0.96938448 0.57142486 0.60098021 0.31719801 0.18869461 0.75721668 0.77828667 0.91499091 0.25470397 0.96519605 0.35452055 0.58304657 0.49374697 0.176868310.8414984 0.91968741 0.58548438 0.67926884 0.07571236 0.04465716 0.33320846 0.566494640.8034107 0.305938420.863575 0.43872874 0.84014429 0.517412390.9956434 0.43303705 0.85405752 0.72041242 0.06417909 0.05816576 0.95282828 0.21642192 0.34914137 0.01268794 0.52880832 171819202122232425 0.33849413 0.18524329 0.58463184 0.585774660.4840851 0.79092906 0.47813377 0.38166652 0.67733816 0.61215085 0.320510360.3467864 0.50732735 0.43069674 0.50696639 0.24883021 0.91163315 0.52446216 0.91803636 0.29542704 0.02361546 0.41041131 0.617699410.822177 0.80210811 0.95752099 0.14157714 0.71292122 0.27086224 0.04306215 0.18807769 0.25249886 0.829723040.1268683 0.98357946 0.40865179 0.75723415 0.72542336 0.81972891 0.78844848 0.84027233 0.14313235 0.51029325 0.73726638 0.95044326 0.11481824 0.97877418 0.95675547 0.45197091 0.08587981 0.05400819 0.45384962 0.16098328 0.90380927 0.39621803 0.06470002 0.97947871 0.39840886 0.263082620.6235954 0.03145031 0.44794544 0.20848228 0.94296412 0.64757381 0.34851925 0.45887691 0.82499658 0.32179512 0.15501526 0.23162334 0.05476217 0.9904186 0.76925015 0.52058841 0.940683150.7808064 0.37530038 0.90424222 0.82659372 0.95093543 0.95517916 0.14257517 0.11153123 0.99714255 0.11478715 0.74669844 0.745449560.3657372 0.91202021 262728293031323334 0.93858259 0.23628367 0.85481302 0.10770577 0.02481741 0.97020381 0.91974432 0.47588394 0.54120381 0.89815463 0.29108021 0.44903313 0.015709720.832034 0.04616177 0.577993450.3977607 0.60645926 0.87116862 0.25086891 0.75169918 0.258822050.3157352 0.93053648 0.97566033 0.95567378 0.83820702 0.96749148 0.18817433 0.84667649 0.73285381 0.72647209 0.12472587 0.09049692 0.17145577 0.29434151 0.99356732 0.89589437 0.44346995 0.47896136 0.78592832 0.52208618 0.21233436 0.97173331 0.71361011 0.15801231 0.31025491 0.69834259 0.66837386 0.949196730.1035981 0.07113989 0.15318807 0.79272282 0.08034161 0.40601521 0.58103465 0.28064031 0.97993304 0.18767563 0.64765099 0.66426468 0.42080378 0.1547307 0.675313240.6138567 0.59379967 0.55547916 0.17560693 0.90485027 0.56660137 0.47985873 0.38009886 0.51915584 0.016800120.2734129 0.30208967 0.87953706 0.85921701 0.41865919 0.93879389 0.74012199 0.034373340.9036997 0.32008604 0.95418882 0.86826209 0.64760436 0.55091063 0.71145837 3536 0.55585593 0.07972163 0.13074155 0.55851126 0.59476441 0.42590387 0.483079560.2022061 0.43927349 0.19809085 0.06238396 0.96996655 0.972341320.214876 0.25728970.3015356 0.80287626 0.68063191 0.38621308 0.09016524 第第十十期期函函数数题题彩彩票票号号码码生生成成器器总总目目录录 题目按F9键 请在蓝色区域中，随机生成10组36选7的彩票号码 1234567 一7182635313436 二20372510285 三301413412521 四35241216193222 五335292536304 六1914252212932 七22182613293432 八1511202310217 九827142963511 十102573212423 0.80976984 0.53833648 0.33025498 0.09586806 0.24090366 0.151911340.0397719 0.433066870.9044677 0.77227912 0.33450917 0.74402396 0.168363520.8719883 0.0619126 0.57644733 0.67639049 0.935769450.9754775 0.15600826 0.27531886 0.10951861 0.33350614 0.66601097 0.49712969 0.42068871 0.17092333 0.35205692 0.08272257 0.90426209 0.21150375 0.34966435 0.03506142 0.19633209 0.91155541 7 0.43541541 0.56462811 0.238915980.9095086 0.31261798 0.157011130.1246035 7 0.52582541 0.59630243 0.43322625 0.832200710.2432826 0.06695406 0.17701025 7 0.481263560.6815723 0.36565632 0.24636873 0.73876083 0.89141019 0.46177308 7 0.74491175 0.27248877 0.58181857 0.17641209 0.77760486 0.04173307 0.63699338 7 0.60924751 0.22610549 0.744684990.0620206 0.56826078 0.85314524 0.31694551 7 7 7 7 7 0.267825170.93707460.94932 0.83829556 0.45747492 0.79974052 0.76496692 0.408278580.8069827 0.76720802 0.00177065 0.56706667 0.42626816 0.71554483 0.06170131 0.97267986 0.83836185 0.12203624 0.08930164 0.95371163 0.55723531 0.56278603 0.86450163 0.68318671 0.73571567 0.13150166 0.36599831 0.91905056 0.523359820.8203043 0.39964178 0.30348945 0.38011026 0.84769808 0.313917820.6446997 0.60273452 0.77787893 0.33067238 0.35390916 0.53956908 0.32570472 0.96638685 0.63312645 0.66818201 0.17945627 0.71265535 0.07232614 0.43272111 0.61260668 0.74540043 0.08909126 0.07903943 0.50761477 0.56952248 0.91527468 0.18942273 0.90388875 0.84628171 0.93062742 0.78668976 0.21591502 0.91577402 0.02382721 0.13800628 0.10346027 0.86485316 0.819623780.0433371 0.47662388 0.19500061 0.79576075 0.77297598 0.49918919 0.45217413 0.39328015 0.22485709 0.69937315 0.03266387 0.99023941 0.82585624 0.44425411 0.03472919 0.72895589 0.32247409 0.51871919 0.98916094 0.43177476 0.16249184 0.94781048 0.41437777 0.67596829 0.58129698 0.167375670.7328825 0.25576945 0.79921407 0.34912419 0.31637335 0.93230838 0.62305628 0.09556631 0.05737133 0.23904577 0.68524241 0.37764562 0.36594001 0.03066038 0.16215081 0.33558242 0.06734421 0.03443365 0.01159285 0.05969763 0.28191331 0.19267108 0.02277446 0.50318638 0.25732647 0.95429237 0.48397514 0.30618763 0.11622051 0.95398683 0.95801223 0.84340524 0.2614669 0.96780674 0.62969406 0.53232563 0.70183426 0.95232744 0.65713951 0.47252938 0.14713581 0.885920370.59665410.1623189 0.26903106 0.014097720.7059135 0.55541302 0.83028746 0.53562682 0.91663495 0.92391136 0.48297745 0.72226219 0.85144434 0.57462288 0.05455072 0.34058852 0.13936811 0.14912421 0.89019778 0.12576694 0.81910288 0.40125159 0.54910219 0.34906534 0.22361706 0.63425427 0.94596104 0.54743984 0.93446728 0.40904429 0.06198702 0.38205665 0.32981525 0.05747612 0.14287915 0.42540798 0.99949181 0.01592471 0.14655248 0.512521440.5350458 0.78439633 0.03740714 0.44459936 0.53758519 0.286032930.2395974 0.62579689 0.33061575 0.74320679 0.87242445 0.82944641 0.90123508 0.07985147 0.17965764 0.43385129 0.40984752 0.74982539 0.06251407 0.87244988 0.49983612 0.72711703 0.14388518 0.00425267 0.75312034 0.25748652 0.77229464 0.78669453 0.93117878 0.81891101 0.01627673 0.95897474 0.340453860.29362370.769231 0.00719049 0.25552913 0.49223517 0.13220727 0.78509853 0.77890452 0.36129111 0.39546548 0.08774345 0.64461447 0.83855413 0.07096801 0.045130930.394245 0.929355 0.14026364 0.31176104 0.55634297 0.900115090.1340786 0.62567262 0.64733367 0.29505967 0.4591468 0.47298013 0.91895877 0.06026898 0.78043732 0.575972380.8373131 0.60108751 0.99232273 0.206394550.0685694 0.13829141 0.84765688 0.98257721 0.14393952 0.79011964 0.074430130.2565339 0.57128176 0.37872884 0.66900448 0.16515299 0.12269849 0.938165080.6087821 0.63261086 0.44051937 0.46676131 0.12481346 0.122505540.5632179 0.60875553 0.25750185 0.18128753 0.83978423 0.64510487 0.72664840.4063961 0.44321909 0.70514708 0.95555103 0.49685721 0.96768409 0.30819726 0.704773960.5884141 0.20303325 0.67337048 0.31600281 0.89896428 0.61663295 0.40953358 0.52976571 0.55422477 0.1821677 0.04382633 第第十十期期函函数数题题彩彩票票号号码码生生成成器器总总目目录录 题目按F9键 请在蓝色区域中，随机生成10组36选7的彩票号码 1234567 一13941961528 二2511133311918 三3212161024236 四3319281272335 五271432923203 六22163427303519 七841311212726 八51624222514 九1335168103220 十16353015192227 结果验证 √ 10.07686410.018792 14.9649375 2.08676903 28.3824589 6.2181108232.962302 25.2278158 √ 3.15727673 24.5991794 8.71262856 23.4662335 14.7589756 32.2073913 31.3451328 7.17340977 √ 35.0052387 4.98560415 11.7415616 4.41230745 27.1757506 2.69498758 26.7779825 19.1603759 √ 3.86581031 34.3488384 28.7895924 10.937052529.563374 17.231461615.189965 31.8132777 √ 34.9310791 34.9784406 6.53907799 25.5923272 32.5705355 19.848280732.300992 31.3601745 √ 14.5241183 24.1805679 26.0414282 27.8742627 30.4406934 25.0179393 25.2929635 25.3113048 √ 23.9141282 25.1656493 33.8092813 3.08626938 10.1797217 6.09137599 10.3683047 2.09071741 √ 26.5108732 3.77240066 17.9938728 7.21127894 1.6050044918.81840810.113522 25.7368747 √ 20.807584729.274627 11.0976568 34.4601034 31.8392186 25.1863828 28.6896685 4.25607669 √ 35.2085611 18.8220212 4.79558628 29.5550098 24.7938185 5.76186853 31.1073517 33.4794537 1.66470207 11.0953191 11.1051769 18.3253787 1.38226703 33.10292647.8112224 13.6883086 9.01540969 32.9009037 34.5761062 2.08724363 11.1269632 17.7139639 14.944995622.456969 17.114187223.514762 25.78192871.7410852 13.4194622 1.71448761 22.1695538 11.214863312.630344 1.73761933 3.27541922 30.34995 20.0215834 17.0236691 26.699738526.837396 11.2770313 21.6113533 17.5568161 13.0273415 5.34768703 29.8659545 23.2014612 12.9265394 23.4811344 5.24605848 9.48692735 7.86271019 25.0411967 25.7018989 20.9102122 15.1736947 27.4487836 35.6833546 23.8325096 11.5229798 4.06431161 30.6923855 6.10349917 6.47025096 4.41797503 19.5393605 3.69831013 17.9914082 29.5311204 31.5943745 7.19957459 9.24743993 25.6448688 29.3135014 34.8210881 24.9589608 9.20666187 14.87576172.340021 24.5142401 18.872443 4.37238196 32.7499161 31.0903565 2.25160535 13.5004243 17.9427521 2.55076933 17.4797553 6.8933661 15.5265393 18.5689482 32.8548654 14.6871184 21.7915283 3.83176515 1.7212428325.135618 9.11010677 4.27363074 28.8909753 13.628719334.56681413.885166 14.2128485 32.6837579 22.3512345 5.64478807 5.51457422 9.31721008 27.4126847 15.8074221 13.4795406 24.6888882 1.51304098 18.1675485 9.08331864 5.19573073 3.54527536 17.1278647 27.3745278 1.97151772 1.79510204 33.1021495 17.4044722 32.31802091.6791745 18.4826484 27.3811243 31.97091326.4920268 26.7978524 18.5426414 15.9823573 21.6592119 34.4072129 5.46694445 27.6946674 30.4673268 5.42050707 34.720537821.170208 35.3532686 20.9578069 9.96962117 25.3092055 26.3849541 3.04209867 28.3279024 24.7523604 34.9307702 30.3105514 24.2165892 21.1099104 28.7996251 4.58505334 34.2747742 12.2107084 6.91603138 13.4525251 5.83251108 16.5491274 31.8425002 19.2354486 21.7310434 7.54213198 13.4247074 32.8347832 8.22138725 34.8944055 33.2316998 10.9837891 5.30629921 11.1265637 23.4738206 14.2551934 14.5475951 11.1833156 24.8626131 32.6966494.5880356 11.21108489.8021526 4.64177174 4.65120789 27.791640710.986652 5.22800105 10.8425674 8.51834545 12.6505628 33.3918328 20.2593703 18.0175896 9.09140431 27.0924136 25.0790698 24.734333 19.8831029 11.9777608 31.7073308 4.21504528 20.0525966 3.37132614 8.96187219 15.3219507 20.7864763 16.3087781 29.4367526 22.9681153 34.7535288 1.51619874 23.5992164 32.9242694 14.6038527 5.32134275 1.92746649 34.8698058 19.7742492 18.6975841 13.3010161 1.38029042 31.5657729 10.5152488 2.3220138915.122242 13.4190117 24.8605036 8.10262875 5.33493462 14.448535812.40615510.016509 6.91801667 14.0803529 10.5571156 6.97211684 12.8641584 29.2717894 10.3379044 5.10201821 8.26768529 4.86665152 22.7834229 18.7652698 12.6880041 28.3395212 20.1986829 30.3546035 12.8903423 9.89551848 26.9548799 19.8962452 24.2138525 15.5050261 32.0844315 12.916782510.741017 15.8026269 10.6448155 22.190852 9.42413296 13.4883277 15.0396003 14.3714476 5.21573184 17.3036598 22.5542159 2.48911037 4.64729803 35.6547458 22.3458202 3.28427461 9.98984488 35.7071693 12.339145620.237867 1.84161001 30.5247998 24.5946458 9.7002685 22.0276202 25.2227457 21.2894357 11.1521205 9.45372642 32.9427365 32.3418659 在bosco大师的解法 偶新增三表是在bosco大师解法基础上发展来的新解法 满足指定条件的不重复值满足指定条件模糊查找的不重复值 查询条件 本例按条件模糊查找的不重复值 名称年度 W2005 名称下拉框 ABC2004/1/1WVU CDE2003/1/1WGH WGH2005/6/1 WVU LMN2003/1/2 OPQ2004/1/3 QST UVW2007/1/8 XYZ2001/1/2 ZYX2005/1/9 WVU2005/1/6 可以处理 多种数据 类型 总总目目录录 总总目目录录9 本例抽取不连续单元的不重复值本例抽取只有数值的不重复值本例抽取字符串长度大于1的不重复值 数据辅助列下拉框数据辅助列下拉框 1111 dfsdfdfsdfdfsdf2 23 2925 954 53 5美丽 343 舒服 美丽美丽 11 22 4 55 舒服舒服 44 C4SUMPRODUCTC5C22““/COUNTIFC5C22,C5C222;3;}这这样样的的数数组组 A3C38CB13 xIFTOFFSETdata,MODr_s-1,ROWSdata,INTr_s- 1/ROWSdata,1,1““,NOFFSETdata,MODr_s-1,ROWSdata,INTr_s- 1/ROWSdata,1,1,TOFFSETdata,MODr_s-1,ROWSdata,INTr_s- 1/ROWSdata,1,1 A2C4B12CC 1C5B2A32C1 2C6B35B1C2 34B45CC3生生成成单单列列数数组组 按按先先列列后后行行的的顺顺序序，，结结果果如如右右C4注注意意是是用用OFFSET函函数数进进行行区区域域转转换换的的，，但但转转换换后后的的区区域域是是一一个个三三维维的的数数组组 不不能能用用辅辅助助单单元元格格C5或或可可称称作作在在Z轴轴上上分分布布的的数数组组，，必必须须用用N函函数数对对数数值值和和T函函数数对对文文本本 不不能能用用自自定定义义函函数数C6转转换换为为平平面面上上的的二二维维数数组组是是一一个个真真正正的的内内存存数数