<menu id="suuyg"><code id="suuyg"></code></menu>
  • <menu id="suuyg"><nav id="suuyg"></nav></menu>
    <tt id="suuyg"><tt id="suuyg"></tt></tt>
  • <xmp id="suuyg">
  • <nav id="suuyg"></nav>
    <nav id="suuyg"></nav>
    Question to say "I can!"

    一對多查詢,神器index+small

    2016-12-12

    參考Excel之家ExcelHome

    我們對數據進行查詢時,經常會使用VLOOKUP函數。

    但有時,我們會碰到這樣的問題,提取符合條件的結果是多個,而不是一個,這時候VLOOKUP就犯難了。

    舉個例子,如下圖,左側A1:C10是一份學員名單表,現在需要根據F1單元格的“EH圖班”這個指定的條件,在F2:F10單元格區域中,提取該班級全部學員名單。

    e1

    今天說一個函數查詢方面的萬金油套路:Index+Small。

    F2單元格輸入以下數組公式,按住Ctrl+Shift鍵不放,再按回車鍵,然后向下填充:

    =INDEX(B:B,SMALL(IF(A$1:A$10=F$1,ROW($1:$10),4^8),ROW(A1)))

    這個公式看起來可就比上面那個VLOOKUP的解法苗條養眼多了。

    1、公式講解

    IF(A$1:A$10=F$1,ROW($1:$10),4^8)

    這部分,先判斷A1:A10的值是否等于F1,如果相等,則返回A列班級相對應的行號,否則返回4^8,也就是65536,一般情況下,工作表到這個位置就沒有數據了。

    結果得到一個內存數組:

    {65536;2;3;65536;65536;65536;65536;8;65536;10}

    SMALL函數對IF函數的結果進行取數,隨著公式的向下填充,依次提取第1、2、3……n個最小值,由此依次得到符合班級條件的行號。

    隨后使用INDEX函數,以SMALL函數返回的行號作為索引值,在B列中提取出對應的姓名結果。

    當SMALL函數所得到的結果為65536時,意味著符合條件的行號已經被取之殆盡了,此時INDEX函數也隨之返回B65536單元格的引用,結果是一個無意義的0,為了避免這個問題,可以在公式后面加上一個小尾巴 &""

    利用&””的方法,很巧妙的規避了無意義0值的出現,只是當查找結果為數值或日期時,這個方法會把數值轉變為文本值,并不利于數據的準確呈現以及再次統計分析。

    2、如果是要做成橫向的,又要怎么寫函數呢?

    這里是隱藏的:=INDEX($B:$B,SMALL(IF(A$1:A$10=$F2,ROW($1:$10),4^8),Column(A$1)))

    作者:admin | Categories:辦公能手 | Tags:

    發表評論

    電子郵件地址不會被公開。 必填項已用*標注

    *

    日彩网平台日彩网主页日彩网网站日彩网官网日彩网娱乐 www.781367.com | 80850v.com | www.087b.com | www.0860p.com | jjjj19927.com | www.0270c.com | www.62830.com | 5309h.com | www.912486.com | www.607967.com | dzjcp779.com | x9983.com | www.b991qp.com | www.60582.com | 3844mm.com | www.c4527.com | www.958443.com | vns345999.com | 73999w.com | www.4809x.com | www.88807p.com | p888ab.com | www.560726.com | www.bwinyz05.com | www.pjshandong.com | 33599bb.com | www.gai31.com | www.521138.com | 228888z.com | 3435o.com | www.36166l.com | www.kj639.com | 8030q.com | 4018ww.com | www.3416e.com | www.0849.com | 88850xx.com | 86611e.com | www.849798.com | www.q32031.com | www.hg313.com | bcw1111.com | www.178311.com | www.55717n.com | www.2605505.vip | bwin8d.com | 99399.cc | www.c1350.com | www.4058c.com | www.6686tk.cc | 5856zzz.com | www.324246.com | www.456333.net | www.302848.com | 922310.com | 3222r.cc | www.466518.com | www.xpj11885.com | www.448868.com | 500000719.com | 8547qq.com | www.847377.com | www.y94600.com | www.2221188.com | www.50054q.com | www.901148.com | www.402776.com | 8557y.com | www.217556.com | www.910057.com | www.80.tt | www.ssd1117.com | 4255a.com | 1916q.com | www.2373j.com | www.1754u.com | www.068500.com | 3180.com | am776.cc | 87665v.com | www.kv10.com | www.19222.com | www.88166f.com | www.63877i.com | hg886.com | zhcp42.com | www.407872.com | www.87668m.com | www.js79903.com | www.1681688888.com | 33115i.com | 66150.com | ww3822.com | www.2632i.com | www.63606.com | www.909022.com | www.kb7070.com | 4289d.com | 6600.net | 66648a.com | www.876061.com | www.065795.com | www.4694y.com | www.3775488.com | 444hg.com | 51133z.com | 3616b.com | 80579g.com | www.843552.com | www.0270z.com | www.70976.net | www.644577.com | www.8768011.com | 538.cc | 469.com | 6699734.com | www.298730.com | www.3479q.com | www.zpw100.com | www.i32939.com | www.3688mhd.com | www.an6988.com | 2613z.com | lj553.com | m2146.com | 70118w.com | 2381rrr.com | www.530534.com | www.5091a.com | www.292200.com | www.xpj1123.com | www.qmc0077.com | www.betwd2.com | www.40818f.com | uu2205.com | qifa168.net | 3788f.com | 776052.com | 048.com | 2544b1.com | www.324877.com | www.946380.com | www.9478j.com | www.blr9944.com | www.35252i.com | www.38345v.com | www.796778.com | www.xpj178.com | www.7777wns.com | 3245j.com | 8722uuuu.com | 8569811v.com | df8r.com | 55899i.com | 7742o.com | 8722ssss.com | 3640x.com | 3967l.com | 888.vip | 3404d.cc | bocaiyule.cn | 159666u.com | 390390.com | 5651q.com | feicai0454.com | 0109js.com | 36508w.com | www.77802v.com | www.901576.com | www.914907.com | www.cp8013.cc | www.7782d.com | www.3479q.com | www.2408c.cc | www.064wy.com | www.22czt.com | www.5091f.com | www.61233x.com | www.7335xx.com | www.542511.com | www.66653v.com | 22ll8331.com | mg4151.vip | 356.com | www.47272x.com | www.6880pp.com | www.0194003.com | www.586840.com | 3544.am | 93922x.com | 55984t.com | www.207622.com | www.1851113.com | www.50024x.com | www.799417.com | 65335.com | 365102d.com | www.yh8009.com | www.365279c.com | www.8998885.com | www.550304.com | 7894e.com | 6766ss.com | www.330099n.com | www.cn365l.com | www.41518p.com | www.66653i.com | 3726k.com | www.70088hg.com | www.85857p.com | www.6889772.com | www.310383.com | 7742ww.com | qq2649.com | www.895095.com | www.flb677.com | www.371477.com | 228888f.com | 2267y.com | www.sg8886.com | www.30393.com | www.177542.com | 4018v.com | www.6491e.com | www.bet63n.com | www.hy5503.com | 2245dl.com | 1079.cc | www.www-222299.com | www.ya2019d.com | hg8893.com | afcp09.com | www.y6007.com | www.11475.com | www.275073.com | 8685151.com | www.vns62999.com | www.69567m.com | www.629293.com | 0289v.com | www.hg00895.com | www.ag6666.com | www.439863.com | 1h-7.cc | www.hg1733.com | www.js56771.com | www.96386v.com | 4370.cc | www.cmssjm.com | www.99968z.com | 11443885.com | laohu88.vip | www.644886.com | www.715777a.com | 6195jp.com | www.66654666.com | www.48330r.com | amblr87570.com | 7945ii.com | www.98msxfpt4.com | www.565hc.com | i444000.com | www.9374e.com | www.86339j.com | P35.com | 3122pp.com | www.hyi3.com | www.037169.com | 55797h.com | www.jxcp3333.com | www.cb2388.com | 54146611.com | www.bai3311.com | www.cp208.com | 7720r.com | www.97321f.com | www.31847.com | 5592958.com | www.88837o.com | www.51515x.com | 4593.com | www.hg763.com | www.80767j.com | bwinkkk.com | www.msc77.net | www.50989j.com | 5804v.com | www.sun7707.com | www.vn888567.cc | 7003f.com | www.xinsezhan.com | www.6889779.com | 1305777.com | www.29886z.com | www.50054t.com | 11018j.com | www.hk278.com | www.860958.com | 908080i.com | www.80969w.com | www.097067.com | 500000878.com | www.7737ee.com | 025647.com | www.dh03035.com | www.4245.com | 8827cc.com | www.2090922.com | www.87668f.com | ylg2001.com | www.js0js0.com | www.gei30.com | 66300vip20.com | www.178659.com | www.99677u.com | 500000975.com | www.7240x.com | 5betbet365.com | www.aw6789.com | www.08czj.com | r7742.com | www.115527f.com | js777js.com | www.7111v.com | www.ck6587.com | wnsr8816.com | www.88553.com | 22296aa.com | www.11088l.net | www.935506.com | yhjilin.vip | www.91779w.com | 444365ggg.com | www.6347799.com | www.013ac.com | c86811.com | www.86339s.com | 4647a.com | www.c5c33.com | love102.com | www.3844f.com | www.c1336.com | 3245.com | www.db373.com | 69619.com | www.59505e.com | yy559.com | www.777444l.com | www.1035d.com | 56988qq.com | www.7920t.com | 5099uu.com | www.pj88r.com | yh55.com | www.twcp55.com | js26732.com:9885天天代理.com | www.77537g.com | www.827737.com | cp088w.com | www.5441d.com | vv8825.com | www.10888.com | 98345ee.com | www.6668ylg.com | long2268.com | www.5349.com | p88890.com | www.xpj1535.com | 7196hh.com | www.7111b.com | www.338996.com | www.h22365.com | www.895465.com | www.taobaobo5.com | www.21202m.com | zhcp66.com | www.35155r.com | 76368888.cn | www.36584444.com | 33772007.com | www.4323f.com | vic82.com | www.6000hd.com | be9458.com | www.7239u.com | 0069.com | www.60108m.com | 0033e.cc | www.2221102.com | 2104.com | www.12455p.com | 998cpdl.com | www.77210d.com | 3178h.com | www.582010.com | 55bb8332.com | www.126ll.com | i2306.com | www.07xyc.com | www.81866i.com | www.c300.vip | www.hg123888.com | www.106396.com | www.v445566.com | www.177564.com | www.27177c.com | 4195e.com | www.61msxfpt2.com | 22117m.com | www.590561.com | 0015.com | www.9989577.com | lixingyoulun.cc | www.50052p.com | 2846u.com | www.843980.com | www.770144.com | www.29277o.com | www.vipxl.3009300930093009.com | 1294z.com | www.cn365n.com | v997d.com | www.r98478.com | 512534.com | www.sxyl2.com | www.hg67888.com | www.799343.com | www.pj5506.com | 9411fff.com | www.808888l.com | 7508l.com | www.540066.com | 5589y.com | www.808466.com | www.365815d.com | www.55885i.com | www.138cpv.com | 2012bet88.com | www.2228229.com | jjjj.com | www.849689.com | www.7764.com | 9679r.com | www.557579.com | swin6.com | www.7782g.com | www.888999yh.com | 588hhh.cc | www.531789.com | 66671h.com | www.36166x.com | 4066j.com | www.339281.com | www.860389.com | 4775544.com | www.80075d.com | www.v32126.cc | 29918jj.com | www.750620.com | 574747com | www.36166hh.com | www.bet056365.com | 2602004.com | www.00772h.com | 3522f.vip | www.763605.com | www.pj9001.com | 69448866.com | www.33112l.com | www.pj7019.com | gbhs.AM | www.369401.com | 55331kk.com | www.99jti.com | www.c32126.cc | 1227011.com | www.47jsc.com | 2998z.com | 11qq8331.com | www.704907.com | jinshabet.com | www.755690.com | www.688558.com | caipiao99.com | www.73736f.com | www.8694h.com | 58222bb.com | www.4102j.com | 55tt8331.com | bwin940.co | www.s69096.com | 22299bb.com | www.547099.com | www.50999m.com | 22tt8332.com | 89892qq.com | www.614986.com | www.mg4355888.com | 2269jc.com | www.73990r.com | 588vvv.cc | j1759.com | www.w84r.com | www.5554dhy.com | yh55.com | www.39500y.com | www.9380l.com | 4123qq.com | www.66653k.com | www.135suncity.com | 8159kk.cc | www.786819.com | www.3029.com | 3024m.com | www.178762.com | www.9895x.com | 2003.com | 1495f9.com | www.62118c.com | www.7036ee.com | 58jzs.com | www.622by.com | www.5agcpw.com | 159666q.com | www.66332o.com | www.68365v.com | www.p78888.com | 12742h.com | www.22qxc.com | www.58atv.com | 44077m.com | 23800r.com | www.62778855.com | www.hg285.com | 0485.com | www.372918.com | www.10999v.com | www.18735528058.com | 66648v.com | www.hong663.com | www.157234.com | 7779j.cc | 8577o.cc | www.3890v.com | www.28000j.com | www.ven0000.com | 5622w.com | www.108013.com | www.858466.com | www.789msc.com | 1665p.com | 54443.com | www.187152.com | www.0601m.com | www.o4042.com | www.a8a8456.com | p888ab.com | 15856i.com | www.26299r.com | www.bet91486.com | www.q27229.com | 55331kk.com | 56988h.vip | www.967699.com | www.883399d.com | www.hg1810.com | 131aa.net | 3066aaa.com | 4116d.com | www.3478o.cc | www.161184.com | www.hg67.cc | 3838jsc.com | 579336.com | 822365.cc | www.8000hc.com | www.y6098.com | www.656888.com | 50028822.com | mmm01234.com | 168c5.com | www.663948.com | www.hg78777.com | www.222c7.cc | www.mgm3838.com | hcp5555.com | 2709n.com | ylg12.com | www.jx68.com | www.55717n.com | www.77731i.com | www.56520h.com | 62222z.com | 00773hh.com | 6137w.net | www.931669.com | www.1234168.com | www.365099.bet | www.71088k.com | 00665004.com | 55572949.com | 206402.com | spsgz.com | www.981060.com | www.902cq.com | www.16181y.com | www.33352055.com | www.0055hg.com | 99882007.com | 123888.me | f8381.com | www.310295.com | www.2632l.com | www.v774477.com | www.sb5503.com | www.66667666.com | www.bet365601.com | 111122qq.com | 86611x.com | 32355dd.com | 0241h.com | www.506477.com | www.5854i.cc | www.16065d.com | www.yh234b.com | www.5966ppp.com | www.pj0007.com | www.5588hg.com | 123456vip.cc | bb32355.com | 4323d.com | m88.cn | 4116t.com | www.558414.com | www.1368x.cc | www.77016.net | www.js18456.com | www.9737ff.me | www.466669.com | www.138ppp.com | www.330192.com | www.915196.com | www.34736.com | www.106795.com | www.js89w.vip | www.531789.com | www.93036511.com | www.814c.com | www.hg773999.com | www.77788807.com | www.86611p.com | 3844xx.com | 6150f.com | 15856s.com | hg10d.com | 5622l.com | 6033.com | 73055h.com | qjdy032.com | 93936q.com | www.86267a.com | www.354977.com | www.758022.com | www.c2798.com | www.2875c.com | www.50026d.com | www.0055wd.com | www.663189.com | www.52062d.com | www.33678bb.com | www.g69096.com | www.365022.bet | www.85857w.com | www.lfcp111.com | www.82142.com | www.428585.com | www.hg2809.com | www.058600.com | www.10050755.com | www.98am444.com | www.32666m.com | www.333133k.com | www.7893w54.com | www.939393.com | www.0636b.com | www.pj7987.com | www.6491u.com | www.414133.com | www.kb8854.com | www.yddc09.com | www.pjyunnan.com | www.717xpj.com | www.wnsr828.com | www.h5505.com | www.pj444222.com | www.777444c.com | www.7772558.com | www.566333.com | www.88837r.com | www.cs11666.com | www.b0008.com | www.7811b.com | www.aa56108.com | www.js6767r.com | www.5588135.com | www.rcw058.com | www.hga66600.com | www.4996sz.com | www.73990b.com | www.1764h.com | www.5952122.com | www.yh76a.com | www.50051n.com | www.1035v.com | www.932312.com | www.602742.com | www.178952.com | kjzf224.com | 76548.com | 013311.com | 5622l.com | 6723888.com | 0088457.com | feicai0813.com | www.hg0118.com | www.9996bb.com | www.551324.com | www.1111365.com | www.6776cc.com | www.73990h.com | www.1466f.com | www.71399t.com | www.66kcw.com | www.611949.com | www.68568i.com | 2677ooo.com | ag80.vip | 6487ccc.com | yh123456.com | 599369.com | www.h7788m.com | www.lhcbm.com | www.007ggb01.com | www.631609.com | www.5677758.com | www.77114n.com | www.w931.com | www.390685.com | 5804i.com | 15a2.net | sss4255.com | 6868rr.cc | www.223456j.com | www.xpj8812.com | www.361cp.cc | www.241099.com | www.77114c.com | www.861803.com | 3131c.cc | 5855cc.com | 43330.com | 51200aa.com | www.t8003.com | www.d9008.com | www.3983119.com | www.8582bb.com | www.igcp4.com | www.030953.com | lego.vip | 40033t.com | 496.ma | www.2228303.com | www.175702.com | www.jj4625.com | www.6539y.com | www.320477.com | 7703gg.com | 38365r.com | 69990c.com | www.377088.com | www.90644.com | www.6787758.com | www.071wy.com | www.035168.com | 20053366.com | 9694s.com | www.vns991.cc | www.378878.com | www.60886j.com | www.9928f.com | www.192880.com | 3522n.vip | tcdtf.com | www.99955545.com | www.55526h.com | www.js27666.com | www.aa3479.com | 168c6.com | 22nn145.com | 4812555.com | www.822822.com | www.44118u.com | www.022sd.cc | 7773657.com | 58222v.com | www.1869e.com | www.988306b.com | www.3421k.com | www.hw8889.com | 0747oo.com | 3559llll.com | www.ylhg1818.com | www.166663.com | www.55717d.com | www.727079.com | 77225002.com | 35oo.vip | www.am7773.com | www.296395.com | www.662by.com | 6137c.com | dede58.com | www.1310909.com | www.88166d.com | www.39500z.com | 78119988.com | hjcp222.com | www.wnsr038.com | www.cp9508.com | www.65707m.com | 19991t.com | 35222g.com | www.xpj3398.com | www.1064d.com | www.9356l.com | 3156eee.com | 4289k.com | www.330099c.com | www.704906.com | www.cb3188.com | feicai0516.com | 2373n.com | www.552772.com | www.8998933.com | www.257233.com | www.1434r.com | www.517075.com | 2618g.com | www.a1a222.cc | www.4972w.com | www.2632t.com | 3202l.com | zhcp76.com | www.328466.com | www.16878a.com | 80188g.com | 88851.com | www.tyc5955.com | www.5856876.com | www.040425.com | 4488nn.com | www.88837v.com | www.xpj66123.com | www.369908.com | 71372225.com | www.333133t.com | www.g2894.com | www.493381.com | 00773oo.com | www.www998sun.com | www.38775nn.com | www.346977.com | 4025h.com | www.hg3385.com | www.55717y.com | 89777s.com | tjhyncw.com | www.113470.com | www.50026m.com | aa38648.com | 56988.com | www.sbd026.net | www.896010.com | bet577z.com | www.88807l.com | www.9971003.com | 22291155.com | 2864x.com | www.s456x.com | www.c3004.com | 3936x.com | www.98717s.com | www.833296.com | 3156www.com | zhcp87.com | www.9737rr.com | www.603154.com | d3405.com | www.53js.com | www.55587.com | 00229193.com | www.hg87488.com | www.37jsc.com | LXYL365.com | 3049l.com | www.173151.com | www.217630.com | 26668d.com | www.c44uu.com | www.912254.com | 3568h.com | www.js421.com | www.5522v.cc | 61328833.com | www.40033.1940033.com | www.ya2019z.com | 001122331.com | www.617705.com | www.yh8899.bet | 996622xx.com | www.yh40.net | www.23456789.pw | 6220ll.com | www.81866u.com | www.86339k.com | 3122h.com | 88535u.com | www.883399d.com | pujingzaixianyule.com | x2913.com | www.98vs88.com | 88553885.com | www.y613377.com | www.zzyl60.com | 131mmm.net | www.hg2269.com | www.665534.com | 67877l.com | www.3tgpd.com | www.26299f.com | 37770775.com | www.442336.com | www.987957.com | www.0055wd.com | hg8081.com | www.xh538.com | www.fo54.com | 3817.com | www.sj52288.com | www.257188.com | 3169p.com | www.288582.com | 38365p.com | www.365815k.com | www.50052y.com | 99909v.com | www.303365.com | www.551412.com | 44488v.com | www.32031x.com | 3121ww.com | www.7111p.com | www.9960.cc | 3404j.cc | www.pj88n.com | 2004874.com | www.amh04.com | www.96386r.com | 0241b.com | www.55526o.com | www.201882.com | p888.com | www.954321c.com | x777dl.com | www.5858564.com | www.355242.com | 131ii.net | www.9422j.com | 838388g.com | www.08bwin.com | www.202427.com | www.00038js.com | www.16878v.com | 3473w.com | www.0662777.com | 12274411.com | www.21365dd.com | www.4cpb.com | wnsfw.com | www.5446c.com | 55984b.com | www.333777k.com | www.173880.com | www.wns8818.com | www.34788s.com | am99914.com | www.46630.vip | blhvip9.com | www.kj88822.com | www.290252.com | www.2882389.com | www.89939.cc | nn500e.com | www.12136c.com | wns789.com | www.5049w.com | 68228m.com | www.40033.1340033.com | www.760250.com | 272h.net | www.54400q.com | 4809o.com | www.0612g.com | 66578.com | www.6482.com | kkk5657.com | www.006785.com | 2021ddd.com | www.msc128.com | ibet888.net | www.155439.com | www.499428.com | www.38200s.com | www.ac3322.com | zz3189.com | www.84499l.com | 88851s.com | www.amjs339.com | 14683344.com | www.357116.com | h21148.com | www.720c51.com | 8577e.cc | www.k2341.com | 6446tt.com | www.88993s.com | www.045445.com | www.330099u.com | www.455745.com | www.056987.com | www.840774.com | www.56733b.com | www.qucw4.com | 908080c.com | www.3126y.com | 6220o.com | www.9478q.com | 111122jj.com | www.50080l.com | 2649i.com | www.77114s.com | 80368ll.com | www.80767s.com | df8e.com | www.66332h.com | 7779k.cc | www.36166p.com | 8577d.cc | www.8499c.com | x2306.com | www.5854t.cc | 2381k.com | www.ya190.com | 5855.com | www.980158.com | www.pj8458.com | www.649677.com | www.4107w.com | www.298980.com | www.35441777.com | www.43818v.com | www.js60044.com | sypk10.com | www.49936.com | 35856app.com | www.38345f.com | w11.vip | www.3032ee.com | 86611x.com | www.919699.com |