Ide: Lòt lang yo se Google-tradui. Ou ka vizite a English vèsyon nan lyen sa a.
Ouvri yon sesyon
x
or
x
x
enskri
x

or

Ki jan yo jwenn valè ki pi wo a epi retounen valè selil adjasan nan Excel?

Doc-jwenn-pi gwo ki gen rapò-valè-1 1

Si ou gen yon seri de done tankou D 'yo montre, kounye a, ou vle jwenn valè a pi gwo nan kolòn A epi pou yo jwenn kontni selil adjasan li yo nan kolòn B. Nan Excel, ou ka fè fas ak pwoblèm sa a ak kèk fòmil.

Jwenn valè ki pi wo a epi retounen valè selil adjasan ak fòmil

Jwenn epi chwazi valè ki pi wo a epi retounen valè selil adjasan ak Kutools pou Excel


Jwenn epi chwazi valè a pi gwo nan yon kolòn epi retounen korespondan done:

avèk Kutools pou Excel'S Chwazi Selil ak Max & Min valè Utiltiy, ou ka byen vit chwazi nimewo a pi gwo oswa pi piti nan yon kolòn oswa yon seri ke ou bezwen.

Doc-jwenn-pi gwo ki gen rapò-valè-7 7


flèch ble jarèt dwat Jwenn valè ki pi wo a epi retounen valè selil adjasan ak fòmil


Pran done yo pi wo a pou egzanp, yo ka resevwa valè a pi gwo nan done korespondan li yo, ou ka itilize fòmilè sa yo:

Tanpri tape fòmil sa a: = VLOOKUP (MAX ($ A $ 2: $ A $ 11), $ A $ 2: $ B $ 11, 2, FALSE) nan yon selil vid ou bezwen, ak Lè sa a, peze Antre nan kle yo retounen rezilta ki kòrèk la, al gade Ekran:

Doc-jwenn-pi gwo ki gen rapò-valè-2 2

nòt:

1. Nan fòmil ki anwo la a, A2: A11 se seri a done ke ou vle konnen valè a pi gwo, ak A2: B11 endike seri a done ou te itilize, nimewo a 2 se nimewo a kolòn ki valè matche ou an retounen.

2. Si gen plizyè valè pi gwo nan kolòn A, fòmil sa a sèlman jwenn premye valè korespondan an.

3. Avèk valè ki pi wo a, ou jis ka retounen valè selil la nan kolòn dwat la, si ou bezwen retounen valè a ki nan kolòn gòch la, ou ta dwe aplike fòmil sa a: =INDEX(A2:A11,MATCH(MAX(B2:B11),B2:B11,0))( A2: A11 se seri a done ke ou vle jwenn valè relatif la, B2: B11 se seri a done ki gen valè a pi gwo), ak Lè sa a, peze Antre nan kle. Ou pral jwenn rezilta sa a:

Doc-jwenn-pi gwo ki gen rapò-valè-3 3


flèch ble jarèt dwat Jwenn epi chwazi valè ki pi wo a epi retounen valè selil adjasan ak Kutools pou Excel

Fòmil ki anwo yo sèlman kapab ede ou retounen premye done korespondan yo, si gen pi gwo nimewo kopi, li pa pral ede. Kutools pou Excel's Chwazi Selil ak Max & Min valè sèvis piblik ka ede w chwazi tout nimewo a pi gwo, ak Lè sa a, ou ka fasilman wè done yo korespondan ki adjasan nan nimewo a pi gwo.

Apre enstale Kutools pou Excel, tanpri fè jan sa a :( Download Kutools pou Excel Kounye a! )

Kutools pou Excel : ak plis pase 300 sou la men Excel ajoute-ins, gratis eseye ki pa gen okenn limit nan jou 60.

1. Chwazi kolòn nan nimewo ke ou vle jwenn epi chwazi valè yo pi gwo.

2. Lè sa a, klike sou Kutools > chwazi > Chwazi Selil ak Max & Min valè, gade D:

Doc-jwenn-pi gwo ki gen rapò-valè-4 4

3. Nan Chwazi selil ak Max & valè lajan bwat dyalòg, chwazi Maksimòm valè ki soti nan Ale nan seksyon, epi chwazi Selil opsyon nan la Sèvi seksyon, Lè sa a, chwazi Tout selil yo or Premye selil sèlman ke ou vle chwazi valè a pi gwo, klike sou OK, nimewo a pi gwo nan kolòn Yon te chwazi ak Lè sa a, ou ka jwenn done yo korespondan ki adjasan yo, wè Ekran:

Doc-jwenn-pi gwo ki gen rapò-valè-5 5  2 Doc-jwenn-pi gwo ki gen rapò-valè-6 6

Klike sou Download Kutools pou Excel ak gratis jijman Koulye a,!


Atik ki gen rapò:

Ki jan yo jwenn pi wo valè nan yon header ranje ak retounen header nan Excel?



Rekòmande Zouti Productivity

Biwo Tab

lò star1 Pote onglè sou lamen pou Excel ak lòt biwo lojisyèl, jis tankou Chrome, Firefox ak nouvo Internet Explorer.

Kutools pou Excel

lò star1 Etonan! Ogmante pwodiktivite ou nan minit 5. Pa bezwen okenn ladrès espesyal, sove de zè de tan chak jou!

lò star1 300 karakteristik nouvo pou Excel, fè Excel anpil fasil ak pwisan:

  • Rantre selilè / ranje / Kolòn san yo pa pèdi done.
  • Konbine ak Konsolide Pliye Plizyè ak Manyèl.
  • Konpare Range, Kopi plizyè chif, Konvèti tèks nan dat, inite ak konvèsyon lajan.
  • Konte pa Koulè, Paging subtotals, Avanse Triye ak Super Filter,
  • Plis Chwazi / Insert / Efase / Tèks / Fòma / Link / Kòmantè / Workbooks / Worksheets Zouti ...

Piki ekran nan Kutools pou Excel

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Maurice · 11 months ago
    I GET #NA what am I doing wrong? Thanks
  • To post as a guest, your comment is unpublished.
    Jon · 1 years ago
    Thank you very much for sharing, I was having real trouble with this function. Of course in hindsight, it's as clear as glass!

    Thanks Again & Best Regards,

    Jon
  • To post as a guest, your comment is unpublished.
    Mary · 1 years ago
    Hello,

    I need your help. For this table:

    A B C
    2002 1 4 7
    2003 2 5 8
    2004 3 6 9


    I want that excel returns the year with the highest value for each column. For example, for column A, I want a formula that returns "2004". Can you, please, help me?


    Thanks!
  • To post as a guest, your comment is unpublished.
    Viet · 1 years ago
    any way we can find the minimum of the cells selected and return the value in the cell adjacent. kutools only allow for data range selection.
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hi, Viet,

      As you said, the Kutools only can select the max or min values in selection.
      To find the minimum value and return the data in adjacent cell, you just need to apply the following formula:
      =INDEX(A2:A11,MATCH(MIN(B2:B11),B2:B11,0)).
      Please try it, Thank you!
  • To post as a guest, your comment is unpublished.
    Damrod · 2 years ago
    Like the 2 above me already asked, how can we get multiple values if there are multiple max ones?
    For example, you have 2 items in a list of 100 items that are both 92% and the rest is below that, how does one get both items with that percentage instead of just one?