Preskoči na glavno vsebino

Kako dobiti ali vrniti glavo stolpca na podlagi določene vrednosti vrstice v Excelu?

V tej vadnici bomo iskali določeno vrednost v vrsticah obsega podatkov in nato vrnili ustrezno glavo stolpca ujemajoče se celice vrstice.

Pridobite glavo stolpca na podlagi določene vrednosti vrstice s formulo

Pridobite glavo stolpca na podlagi določene vrednosti vrstice s formulo

Za pridobitev glave stolpca na podlagi določene vrednosti vrstice v Excelu vam lahko pomaga spodnja formula.

1. Izberite prazno celico, da izpišete glavo, kopirajte spodnjo formulo vanjo in pritisnite tipko Vnesite tipko, da dobite ustrezno glavo.

=INDEX($C$2:$G$2,SUMPRODUCT(MAX(($C$3:$G$6=I3)*(COLUMN($C$3:$G$6))))-COLUMN($C$2)+1)

Opomba: V formuli: $ 2 $: $ G $ 2 je obseg glave, $ 3 $: $ G $ 6 je obseg vsebuje določeno vrednost, v kateri boste iskali, I3 je celica vsebuje vrednost meril, ki ji boste ustrezali v obsegu $ C $ 3: $ G $ 6, in $ C $ 2 je prvi stolpec obsega $ C $ 3: $ G $ 6. Prosimo, spremenite jih glede na vaše potrebe.

Sorodni članki

Vlookup vrednost in v Excelu vrne true ali false / yes ali no
Če želite vlookup vrednosti na seznamu in prikazati True / False ali Yes / Ne za rezultat, vam bo metoda v tem članku naredila uslugo.

vrni vrednost v drugi celici, če celica vsebuje določeno besedilo v Excelu
Če celica E6 vsebuje vrednost "Da", se celica F6 samodejno izpolni z vrednostjo "odobri". Če ste v E6 spremenili "Da" na "Ne" ali "Nevtralnost", bo vrednost v F6 takoj spremenjena v "Zavrni" ali "Ponovno premisli". Kako lahko to storite? V tem članku je zbranih nekaj uporabnih metod za lažje reševanje.

  • Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
  • Združi celice / vrstice / stolpce in vodenje podatkov; Vsebina razdeljenih celic; Združite podvojene vrstice in vsoto / povprečje... prepreči podvojene celice; Primerjaj obsege...
  • Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
  • Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
  • Priljubljene in hitro vstavite formule, Obsegi, grafikoni in slike; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
  • Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
  • Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
  • Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
  • Razvrščanje vrtilne tabele po številka tedna, dan v tednu in še več ... Prikaži odklenjene, zaklenjene celice po različnih barvah; Označite celice s formulo / imenom...
zavihek kte 201905
  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
dno pisarniške mize
Comments (18)
Rated 4.5 out of 5 · 1 ratings
Raquel
Raquel
This comment was minimized by the moderator on the site
Muito obrigada pelo conteúdo!!! Excelente!
Reply
0
0
Algendar
Algendar
This comment was minimized by the moderator on the site
Muchas gracias por la información y el interés prestado!!
Saludos
Reply
0
0
Algendar
Algendar
This comment was minimized by the moderator on the site
Muchas gracias por todo!!
Una pregunta: ¿Cómo puedo regresar los encabezados de una tabla en una misma celda?
Reply
0
0
Rated 4.5 out of 5
crystal
crystal     Algendar
This comment was minimized by the moderator on the site
Hi Algendar,
Do you mean to search for multiple values and return matching headers in a single cell? If so, simply join the same formula containing different search values in one cell with the Ampersand symbol (&). See the screenshot below.
=INDEX($C$2:$G$2,SUMPRODUCT(MAX(($C$3:$G$6=I3)*(COLUMN($C$3:$G$6))))-COLUMN($C$2)+1) & " " & INDEX($C$2:$G$2,SUMPRODUCT(MAX(($C$3:$G$6=I4)*(COLUMN($C$3:$G$6))))-COLUMN($C$2)+1)
https://www.extendoffice.com/images/stories/comments/comment-picture-zxm/get-header.png
Reply
Report
0
0
Jerry
Jerry
This comment was minimized by the moderator on the site
So say that there will be entries where the values in I3 may not be found, for all of those cells it tries to output every column header in the range giving me a #ref error since it my sheet isnt long enough to accommodate all the titles again, is there any way for it to just output a blank cell in these cases?
Reply
0
0
crystal
crystal     Jerry
This comment was minimized by the moderator on the site
Hi Jerry,
The following formula can do you a favor. Please give it a try.
=IFERROR(INDEX($C$2:$G$2,SUMPRODUCT(MAX(($C$3:$G$6=I3)*(COLUMN($C$3:$G$6))))-COLUMN($C$2)+1),"")
Reply
Report
0
0
Deepak
Deepak
This comment was minimized by the moderator on the site
Hello,
If we have multiple I3 values will it give the second header which has the same value.
If there is anything to do so, please let me know.
Reply
0
0
crystal
crystal     Deepak
This comment was minimized by the moderator on the site
Hi Deepak,
Can you attach a screenshot to describe the problem you encountered more clearly?
Reply
Report
0
0
Mono
Mono
This comment was minimized by the moderator on the site
Hello, what if we have missing values ? why does it return the last header ?
Can it be arranged so we don't fill the cell with anything in case it's missing ?
Reply
0
0
crystal
crystal     Mono
This comment was minimized by the moderator on the site
Hi Mono,
The following formula may help. If no condition is specified, the result will be displayed as null even if there are missing values in the original range.
=IF(I3="", "", INDEX($C$2:$G$2,SUMPRODUCT(MAX(($C$3:$G$6=I3)*(COLUMN($C$3:$G$6))))-COLUMN($C$2)+1))
Reply
Report
0
0
Cyrous
Cyrous
This comment was minimized by the moderator on the site
How to modify this formula to return specific value if there's no match?
Reply
0
0
sam
sam
This comment was minimized by the moderator on the site
Amazing. This is very helpful,I was wondering if the value matches more than one header, how to get all headers
Reply
0
0
Dan
Dan     sam
This comment was minimized by the moderator on the site
Did you find a solution to this?
Reply
0
0
Sam
Sam
This comment was minimized by the moderator on the site
Hi. Awesome example. Any thoughts about if there are duplicate values?
Reply
0
0
Diogo
Diogo     Sam
This comment was minimized by the moderator on the site
Hey, were you able to find an answer to this problem ?
The formula here presented is working great for me except this particular matter of having duplicated valuesIs there a way to only take into account the first true value the formula encounters?
Reply
0
0
helo
helo     Diogo
This comment was minimized by the moderator on the site
hey, i don't know if there is a way, but something that may be of help is the =Unique command, which counts only the unique data (not replicates of the same data) Hope this helped or what you were looking for :)
Reply
0
0
Jim
Jim
This comment was minimized by the moderator on the site
Thank you for this. Very helpful :)
Reply
0
0
BreadLiker09092081
BreadLiker09092081     Jim
This comment was minimized by the moderator on the site
ur welcome 
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations