Conas gach cás comhoiriúnaithe de luach in Excel a liostáil?
Mar a thaispeántar an pictiúr ar chlé, ní mór duit gach cás meaitseála de luach “Linda” a aimsiú agus a liostáil sa tábla. Conas é a bhaint amach? Bain triail as na modhanna san alt seo le do thoil.
Leis an bhfoirmle eagar seo a leanas, is féidir leat gach cás meaitseála de luach a liostáil i dtábla áirithe in Excel. Déan mar a leanas le do thoil.
1. Roghnaigh cill bhán chun an chéad chás comhoiriúnaithe a aschur, cuir an fhoirmle thíos isteach, agus ansin brúigh an Ctrl + Shift + Iontráil eochracha ag an am céanna.
nótaí: San fhoirmle, is é B2: B11 an raon a aimsíonn na cásanna comhoiriúnaithe. A2: Is é A11 an raon ina bhfuil an luach áirithe a liostóidh tú gach cás bunaithe air. Agus tá an luach áirithe i D2.
2. Coinnigh ort an chill toradh a roghnú, ansin tarraing an Láimhseáil Líon isteach chun na cásanna comhoiriúnaithe eile a fháil.
Is féidir leat an chéad chás comhoiriúnaithe de luach leis an Cuardaigh luach ar an liosta feidhm de Kutools le haghaidh Excel gan cuimhneamh ar fhoirmlí. Déan mar a leanas le do thoil.
Roimh iarratas a dhéanamh Kutools le haghaidh Excel, Le do thoil é a íoslódáil agus a shuiteáil ar dtús.
1. Roghnaigh cill bán cuirfidh tú an chéad ásc comhoiriúnaithe, ansin cliceáil Kutools > Cúntóir Foirmle > Cúntóir Foirmle.
2. Sa Cúntóir Foirmlí bosca dialóige, ní mór duit:
Leideanna: Is féidir leat an scagairí bosca, cuir an eochairfhocal isteach sa bhosca téacs chun an fhoirmle atá uait a scagadh go tapa.
Leideanna: Tá uimhir an cholúin bunaithe ar líon roghnaithe na gcolún, má roghnaíonn tú ceithre cholún, agus is é seo an 3ú colún, ní mór duit uimhir 3 a iontráil sa Colún bosca.
Ansin liostaítear an chéad chás comhoiriúnaithe den luach tugtha mar atá thíos an pictiúr a thaispeántar.
You are guest
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 4 months agoQuestion, I have a sheet laid out opposite of this where "Linda" would be the column header I would like to flag on and the value "90" or 89" I would like to return. Would I change the row function to a column function?
To post as a guest, your comment is unpublished.· 5 months agoHow would I use this formula but instead of filling down I could fill across but the formula would continue moving down as I fill across (ie. the formula changes from ROW(1:1) to ROW(2:2)
To post as a guest, your comment is unpublished.· 5 months agoHi Luke,The below array formula can do you a favor.Note: After entering the formula, please press the Ctrl + Shift + Enter key to apply it. And then drag its Fill Handle right across the cells you need.=INDEX($B$2:$B$11, SMALL(IF($D$2=$A$2:$A$11, ROW($A$2:$A$11)-ROW($A$2)+1), COLUMN(A1)))
To post as a guest, your comment is unpublished.· 1 years agoA very useful and educational formula. I am matching against a set numeric value and it works a treat. How can I use >= rather than just = (i.e. IF($D$2>=$A$2:$A$11) to perform the match so it is looking for values greater than a set value (in $D$2). I assume as it doesn't work as one of the functions in the formula is matching against specific text rather than working with numbers?
To post as a guest, your comment is unpublished.· 2 years agoThis was super useful, thanks! I'm trying to take this one step further and be able to return all match instances of a certain value while having to search through more than a single-column array. To work through this using your example, I added a second column of test scores and modified your formula to look up a given test score and return the names that match that score. I got this to work with INDEX(SMALL()) and can pull all of the names from both test columns. I've also managed to return only names with that score on Test 2 using INDEX(MATCH(INDEX(MATCH))), however this can only find the first instance in the array. What I'm really trying to do is a combination of these: return all of the names with the given array, while narrowing the search to a specific column within the array. Do you have any tips for this?
To post as a guest, your comment is unpublished.· 1 years agoGood day,
Would you please provide a screenshot of your spreadsheet showing what you are exactly trying to do? Thank you for your comment.
To post as a guest, your comment is unpublished.· 3 years agoIs there a similar formula that will work in google sheets?