Excel公式应用大全
查询 总目录公式1 =VLOOKUP(甲一,甲一,111;甲三,222;甲五,333,2,0)公式1 111公式1 222公式1 333公式1 最后一个参数0或写False,也可以省略0或False,但逗号不能省略。公式1公式2 =VLOOKUP(甲一,甲一,111;甲三,222;甲五,333,2,)公式2 111公式2 222公式2 333公式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)产品 数量 单价 金额 产品 数量 单价AA 1000 2 2000 AA 1000 2BB 2000 2 4000 BB 2000 2CC 3000 2 6000 CC 3000 2VV 4000 2 8000 VV 4000 2部门输入查询姓名 部门 入厂日期 职务 丝印 姓名 部门丝印-1 AA 丝印 2006/5/1 主管 丝印-1 AA 丝印丝印-2 BB 丝印 2006/5/2 员工 丝印-2 BB 丝印丝印-3 CC 丝印 2006/5/3 员工 丝印-3 CC 丝印丝印-4 DD 丝印 2006/5/4 员工 丝印-4 DD 丝印办公-1 EE 办公 2006/5/5 主管 丝印-5办公-2 FF 办公 2006/5/6 员工 丝印-6包装-1 GG 包装 2006/5/7 主管 丝印-7包装-2 HH 包装 2006/5/8 员工 丝印-8包装-3 JJ 包装 2006/5/9 员工 丝印-9包装-4 KK 包装 2006/5/10 员工 丝印-10金额2000400060008000入厂日期 职务 丝印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)查询 总目录公式1 a1 1 a1 8 =SUMIF($B$2:$B$6,E2,C$2)公式1 a1 2公式1 a2 3公式1 a4 4 a1 8 =SUMIF(A3:A7,D5,B3:B7)公式1 a1 5公式1第十期函数题:彩票号码生成器 最大值 36将最大值调小,便于验证。题目:请在蓝色区域中,随机生成10组36选7的彩票号码1 2 3 4 5 6 7一 28 17 3 36 19 31 20二 7 16 9 17 4 34 24三 19 21 4 10 5 20 33四 25 1 8 31 30 26 19五 34 2 1 5 20 4 8六 15 16 30 26 22 28 13七 4 24 25 26 22 31 21八 4 26 27 8 19 12 36九 13 10 16 19 30 14 21十 35 4 34 7 16 17 12要求:1 使用函数与公式,必要时可以添加辅助单元格;2 兰色区域只能填入一个多单元格公式,或是可以拖曳复制的单个单元格公式;3 每组的7个号码不能重复,如下列的情况是不正确的:1 31 16 16 17 23 364 不能出现两组号码完全相同:1 31 15 16 17 23 361 31 15 16 17 23 365 不能出现上下组号码间有规律继承的情况,如下例:1 2 23 24 17 26 2923 24 17 26 29 34 417 26 29 34 4 8 216 号码必须在1-36之间的随机数;7 按F9键可更新一组号码。评分:1 所有要求都实现,可得2分;2 方法新颖且高效的,可加获加分,1个论坛ID最多可得5分。总目录按F9键第十期函数题:彩票号码生成器 总目录题目: 按F9键请在蓝色区域中,随机生成10组36选7的彩票号码1 2 3 4 5 6 7一 24 9 8 29 23 28 32二 10 1 24 29 35 4 5三 11 24 29 27 31 32 19四 2 33 31 4 27 21 28五 22 32 33 13 2 14 19六 7 8 18 27 15 32 35七 26 35 5 14 21 13 10八 9 32 18 5 24 14 13九 35 19 12 28 4 10 13十 29 3 36 30 19 21 61 2 3 4 5 6 70.356558920.757948260.788265830.203591740.361790920.248378130.157195670.73855630.912565080.167208390.105936260.026189790.796448430.795082250.732094990.263691040.088194090.147027920.075926290.04185873 0.47601340.929498360.134238350.153283780.876625850.26254952 0.56855270.219964640.458665520.149148040.089905180.610469990.97362607 0.59102430.503728447 0.723276480.714635310.54319151 0.27611810.571903740.094936960.042715187 0.27501370.081081380.891687610.588044810.388702540.606147460.654752417 0.746003580.082937070.525093770.903114710.268521660.591978130.596440217 0.106489960.491163770.690870980.221326910.89686673 0.75534950.687894967 0.27266790.948923020.009984050.243874620.553308340.540809410.86742325777778 9 10 11 12 13 14 15 160.624158250.378878540.97661415 0.8203640.397259820.342230820.202874940.081997310.348704720.132393960.653718490.788402730.033017770.86675987 0.0921380.673438930.344819210.475384920.006245220.701533640.887433680.981487170.63013179 0.42945880.332372080.71516244 0.88492230.623474360.585830390.89249289 0.21837990.059977230.624859590.342477220.46075342 0.15471780.765970750.913183390.453740030.723720960.164940760.163023410.381722380.49532148 0.03087090.618609290.163639120.413497780.386259040.984038180.556854060.06016843 0.34390460.646387260.478851280.123762520.585284490.727429820.109050780.629257690.828614750.194372990.106462120.127190120.155775390.881712280.560248960.047242580.873955070.218445120.712430050.944915460.323296570.920201730.95339539 0.2720799 0.14065640.494480380.216638890.239681450.739400820.83700590.607210760.656051790.54389936 0.07879450.984276440.610769520.485864260.1578950517 18 19 20 21 22 23 24 250.675029910.182535550.582763840.83723098 0.01451420.872496180.335561020.972479740.052659210.127630770.722526130.871229510.68777266 0.2601360.783776240.022268680.218562160.743695070.014262930.611980710.016345720.805275950.847382670.798681880.819806920.464131050.347977660.654990920.584561280.791790110.852713880.679234020.875136440.656037280.624579780.638046560.635709140.803190490.572343750.224912950.962246670.488177190.057752210.235041160.326799280.345837840.429041660.823166180.948479060.443123080.798273150.564603470.194440460.780584690.324381250.21557937 0.99658110.344947230.993990860.950283840.074616520.687573890.341828950.151261210.554534330.542050970.046732870.689435080.086325660.923823310.912895490.283213950.305005810.229119570.767465990.809609410.602787940.612485330.954935150.190479320.315801190.643935880.387480080.600412180.57498542 0.1176660.608555070.873644640.528959940.0865534926 27 28 29 30 31 32 33 340.644705540.890868890.558174310.871280030.521658750.084661140.740441240.393708980.704130370.048003870.516384790.624679610.075240320.295067140.641177910.784474540.141118050.094993390.824707670.906845070.113004040.079672290.710038850.665032750.232412770.030353710.262723080.076695020.135600360.605954520.56547328 0.26596440.035526050.629447130.734178870.637235030.512323140.424773130.957960810.536544720.873078780.29668993 0.67560220.553138930.822427910.08986380.25770998 0.61399130.456145740.615897140.989571940.68166126 0.02969440.176968730.124087350.298076740.88422037 0.6193970.09689288 0.46949140.568566410.925119540.417683880.039460330.465934390.411972730.076578020.152857450.101751690.909053540.750086160.487375320.75919119 0.45031150.265819510.208972050.64675082 0.86949390.057082040.133178330.821644110.403578480.557937380.906598870.959531990.078867670.744448730.523519490.68126359 0.739633735 360.674255760.670024840.318285840.122641070.811611350.584387190.522491430.942907550.063035490.993410840.608960820.456575770.211654140.416624360.445314830.721600250.597021750.178576530.313692670.42567115第十期函数题:彩票号码生成器 总目录题目: 按F9键请在蓝色区域中,随机生成10组36选7的彩票号码1 2 3 4 5 6 7一 7 26 18 2 33 17 6二 15 24 29 18 14 3 17三 27 2 15 11 8 4 19四 5 1 11 13 7 6 25五 23 19 15 2 6 8 21六 4 2 29 32 9 26 23七 1 32 26 15 24 3 19八 30 10 36 27 22 26 29九 12 16 15 29 27 11 10十 32 1 18 9 34 30 160.804812180.272213070.583297130.883562620.089909250.595950370.807034720.641982870.348085660.182698740.60538803 0.64431720.875804320.623832220.126489040.946808930.684453970.780312760.822908790.90652483 0.58779860.796575570.943607770.67790218 0.62462150.760719760.776468420.288534370.301832570.447163660.509501050.913544750.821273210.750149430.329441887 0.952197980.980025860.243154060.118634520.763146320.351927590.471726747 0.995091040.148025120.310010150.560654320.327752370.937677020.415574967 0.167037270.871618560.027222960.336024340.596325510.371243310.213923627 0.613601280.539445190.573347210.131468450.159415530.665310480.742062077 0.149499870.976334960.515240490.740842870.113485580.203481790.63310324777770.801363110.265757670.343759960.838249580.059682050.256151480.90681829 0.20261830.257764190.471803080.295501910.816412110.29493787 0.66726380.057059790.578888880.414914880.899625710.593245550.799548340.157785010.440716240.01473067 0.10565220.89661301 0.7067891 0.17832570.258680550.117358160.885253280.100828650.617920840.135486310.495978620.133547710.466493940.110035520.74432935 0.89921480.045611120.682777350.130726610.458682120.251933130.556626180.91985113 0.53807190.040611140.547552760.923347830.961794010.03495648 0.85292060.557651060.196533710.058037760.93490759 0.31811040.666557790.356061540.343491310.257133990.973153140.893134610.970859590.933984840.906870130.044734270.061943610.104624140.879807730.667321970.898772070.239197710.787246490.988777090.099849460.838396360.882847360.148958970.220528390.034524180.507695480.312737610.673063150.749338430.12400516 0.6592320.464207620.455470420.597170610.306825540.666143780.74930237 0.86537590.48830736 0.76941620.684500910.299520960.719482440.096690550.762809730.815171510.695163560.624945670.80676792 0.66210390.196347250.102669910.934425430.144130970.263767040.176936320.855438750.804097680.045482850.089307320.092982930.234761870.295118620.577372120.152785530.454761630.572734620.734037140.166201920.956055650.003248340.062132750.482782710.751830250.309600250.470696810.61696558 0.5535990.695607170.757647530.891899470.436653750.517566040.01227085 0.56381470.49894722 0.5412780.736884840.645851720.65951207 0.01276070.086449220.639213340.202410770.278502490.693864910.522396040.628002610.81266698 0.3043813 0.77323780.646780470.774733160.613522070.574340580.369706920.612843440.086404670.528575010.123121530.972864750.505214290.369538940.279835980.678155650.407053240.761327990.378774180.235847530.914698420.879288720.042887320.298311490.599884530.810588760.007266470.652439790.026227890.518254570.686122390.552262210.239699640.085947320.445212840.28211102 0.00294390.04561712 0.14029630.420389730.671461340.182418870.106657450.108614780.842636730.022016610.727425240.369708390.996917360.603748580.062186920.698882170.924005760.666411870.32683514 0.88276330.056427010.447370480.696205170.066801740.192676320.242577870.027146340.907126260.13584736 0.82259050.688943110.354273770.151760480.587623220.148549510.505578340.759550150.994419170.164481360.333494370.266470090.564309460.535955380.522156810.444393580.379883740.685549230.156012980.884233410.358006570.567888520.949581210.563546280.032922870.927880420.965050180.664115150.728059880.972894280.794958420.115517540.845632490.583986760.056198820.743671550.434174560.234883290.098529690.037374360.683210860.911967090.704962220.57510819 0.90416890.192898880.273855360.40495938 0.31144180.258834340.395702390.776626680.945962130.64087635 0.70450890.32297960.544272790.23169762 0.11586660.438096330.018425770.720661660.091068350.116669230.622302920.524034660.776849760.701762650.78003174第十期函数题:彩票号码生成器 总目录题目: 按F9键请在蓝色区域中,随机生成10组36选7的彩票号码1 2 3 4 5 6 7一 33 9 23 8 1 12 28二 35 7 25 34 11 18 26三 34 33 1 19 26 8 4四 4 23 16 2 14 5 34五 35 17 2 23 8 31 7六 16 13 9 29 27 8 21七 10 11 6 32 30 36 13八 14 9 36 19 31 5 11九 27 10 20 3 36 29 31十 1 10 34 9 23 24 19结果验证 6.3033801819.368724535.87871179.09259779 34.57867825.599222410.93947284.94901588 26.358664930.463451329.546312617.862565912.8442903 9.09829423.7148065632.8489857 3.6382804315.191584233.10758365.4330938522.954005233.546106832.40026385.42722598 11.96076272.2687363313.66258981.286655373.7281789729.27256284.2742893415.5096447 8.023740634.2615093515.406618423.561948813.083448213.93178825.433324384.58195262 9.4123125121.838705832.49446929.0455267515.817952929.642250222.6044469 7.7006357 19.03921215.385383734.693252822.5035098 34.8892674.4766351827.899700218.0072975 35.857788327.066420926.932904411.4422136 2.984182228.188116411.7178979 22.094479 22.814163812.04999954.8141672834.895992811.005799723.3536103 12.28403635.5307064 2.1682793434.7537335 11.50314628.706539830.252913521.721243534.192185610.25244513.46640942 30.22497827.0577744 6.313684435.404226926.853847228.016728133.686907519.434369626.628548226.97771795.5625926526.335003815.343262920.638871533.921055130.430404117.058423332.20554821.26713958.374976229.0310708313.852967613.310726314.873475328.461556813.470104814.68745185.6599694631.701251735.390221226.61049352.4147472721.75703411.9680432912.971006325.663870929.259669214.6986641 29.339588.4459441727.2078968 32.60538615.75112324.126946164.1401313523.286783420.191447224.18056953.9642578119.869544633.64328371.2696509432.756112222.17305551.615606561.6559953623.35695127.9304809231.870745933.9836654 29.07197235.92829311.9900069426.01672664.2091553911.017462135.99464861.484807284.303157037.6156121629.504142519.315339 3.677429530.790420535.872036933.09012339.4100219215.102765426.835826730.43235455.61499431 5.105331235.295821228.966379231.230198430.969840527.219722833.427368419.738949327.173967921.140411918.7396851 30.76073716.30757434.9177598112.329457130.493778435.82725856.5872761215.466003812.665496915.823826416.55618519.2218723411.77960113.783909847.0517868111.07344224.31842111 21.75160433.158333118.290481815.185457424.5053194 26.1004324.3570174211.054879317.349354833.17051048.3551890921.14336141.92522418 26.7904039.1832302320.630864619.241533312.858378321.115661334.4296007 34.9037564.3969852714.165130317.153355829.146100925.938625614.63403638.988123368.1406055329.7058767 18.6094418.726639519.095586524.698812919.819098221.160400727.713796828.791475729.516632429.331712712.870107832.91346479.1378401428.18948592.5727435418.463541522.055683935.3972825 25.305735.55696524.601493424.6718888321.2860220.9056355 4.150152825.344284619.203660327.975487933.844990530.659539416.155668413.47244910.113688117.668353611.473696713.16450076.695712568.1791607327.9241472 16.95808719.05138937.9879066620.040786921.349123624.432043134.27768921.3075592131.890498211.257565729.757798228.564361135.201257627.620150922.597544231.720784823.87322944.763484572.4358857616.9247945 17.52480625.2466405 21.8766216.484682834.58016153.278046621.7904350829.825444730.586729729.766176516.463758927.987296127.250973510.253567424.78679673.9467898814.397336330.183613327.330860915.307556221.1628951 4.993576125.758035818.7641737 24.8366142.096670674.8562991531.71014454.5519696135.751454818.340036214.0673495 27.67439135.190412231.775438412.3328699 13.09224121.97935096.3993197234.73324745.5423698927.566138412.761269618.249364727.57114559.389467689.7915972834.01515832.6463442334.087808827.379744216.934740135.86676852.0793071813.87135336.9624032924.96121927.0311604830.021840333.643992431.890823232.174954318.391207 34.87787913.300362927.869640615.337129230.683496224.39092325.3945052831.814682113.416072633.635995123.400220518.235380817.171892810.84524216.590279112.27332125.4295525727.6697642在bosco大师的解法 偶新增三表是在bosco大师解法基础上发展来的新解法满足指定条件的不重复值 满足指定条件模糊查找的不重复值查询条件本例按条件模糊查找的不重复值 名称 年度W 2005名称 下拉框ABC 2004/1/1 WVUCDE 2003/1/1 WGHWGH 2005/6/1 WVULMN 2003/1/2OPQ 2004/1/3QSTUVW 2007/1/8XYZ 2001/1/2ZYX 2005/1/9WVU 2005/1/6可 以 处 理多 种 数 据类 型总目录总目录 9本例抽取不连续单元的不重复值 本例抽取只有数值的不重复值 本例抽取字符串长度大于1的不重复值数据 辅助列 下拉框 数据 辅助列 下拉框1 1 1 1dfsdf dfsdf dfsdf 22 32 9 2 59 5 45 35 美丽3 4 3舒服美丽 美丽1 12 245 5舒服 舒服4 4C4=SUMPRODUCT(C$5:C$22)D4=D4=MATCH(1,1/($C$3:$C$20),0),)D列有效性序列=OFFSET($D$5,0,0,$E$4,)每例橙色部分不同这 三 个 灰色 单 元 格有 奥 妙 哦以此类推发挥你的想象吧。数据 辅助列 下拉框1 dfsdfdfsdf 23美丽2 舒服233美丽125舒服4本例抽取字符串长度大于1的不重复值C列=IF(ROW()-ROW(D$4),0),)品名 希望的结果 总目录A 是 1 A AB 和 2 B BC C 3 C C和 B 4 和是 A 5 是是 #N/A 5品名 希望的结果A 3 65 1 1B 2 66 2 2林 1 49622 3 31 林 49 A A2 B 50 B B3 A 51 林品名 希望的结果5 8 0 01 1000 0.5 0.50 0.5 1 10.5 0 5 51000 1 8 88 5 1000筛选数据是数值 第 (6) 种公式解法12数据1 123请用函数法筛选出不重复值982 456 893 78 1234 98 05 56 223 127 45 1118 789 7899 4510 111 22311 12 7812 5 4561314 8915 016 12317 8918 98数据区域如果出现 0 的话,总目录 第 (6) 种公式解法, 公式能返回 0第 (7) 种公式解法, 公式返回 #N/A第 (7) 种公式解法12辅助列98891235121117894522378456#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 湖塘长虹