Iegūstiet vairākas uzmeklēšanas vērtības vienā šūnā (ar atkārtojumu un bez tā)

Vai mēs varam meklēt un atgriezt vairākas vērtības vienā Excel šūnā (atdalot ar komatu vai atstarpi)?

Šo jautājumu mani kolēģi un lasītāji ir uzdevuši vairākas reizes.

Programmā Excel ir dažas pārsteidzošas uzmeklēšanas formulas, piemēram, VLOOKUP, INDEX/MATCH (un tagad XLOOKUP), taču neviena no tām nepiedāvā veidu, kā atgriezt vairākas atbilstošas ​​vērtības. Tas viss palīdz identificēt pirmo atbilstību un to atgriezt.

Tāpēc es nedaudz kodēju VBA, lai programmā Excel izveidotu pielāgotu funkciju (sauktu arī par lietotāja definētu funkciju).

Atjaunināt: Pēc tam, kad Excel izlaida dinamiskus masīvus un lieliskas funkcijas, piemēram, UNIQUE un TEXTJOIN, tagad ir iespējams izmantot vienkāršu formulu un atdod visas atbilstošās vērtības vienā šūnā (apskatīts šajā apmācībā).

Šajā apmācībā es parādīšu, kā to izdarīt (ja izmantojat jaunāko Excel versiju - Microsoft 365 ar visām jaunajām funkcijām), kā arī veidu, kā to izdarīt, ja izmantojat vecākas versijas ( izmantojot VBA).

Tātad, sāksim!

Atrodiet un atgrieziet vairākas vērtības vienā šūnā (izmantojot formulu)

Ja izmantojat Excel 2016 vai iepriekšējās versijas, pārejiet uz nākamo sadaļu, kurā es parādīšu, kā to izdarīt, izmantojot VBA.

Izmantojot Microsoft 365 abonementu, jūsu Excel tagad ir daudz jaudīgākas funkcijas un funkcijas, kas nav pieejamas iepriekšējās versijās (piemēram, XLOOKUP, dinamiskie masīvi, UNIKĀLAS/FILTRA funkcijas utt.)

Tātad, ja izmantojat Microsoft 365 (agrāk pazīstams kā Office 365), varat izmantot šajā sadaļā aprakstītās metodes, lai vienā Excel šūnā varētu uzmeklēt un atdot vairākas vērtības.

Un, kā redzat, tā ir patiešām vienkārša formula.

Tālāk man ir datu kopa, kurā A slejā ir cilvēku vārdi un B slejā veiktās mācības.

Noklikšķiniet šeit, lai lejupielādētu parauga failu un sekotu tam

Katrai personai es vēlos uzzināt, kādas apmācības viņi ir pabeiguši. D slejā man ir unikālo nosaukumu saraksts (no A slejas), un es vēlos ātri sameklēt un iegūt visu apmācību, ko katra persona ir veikusi, un iegūt tos vienā komplektā (atdalot ar komatu).

Zemāk ir formula, kas to darīs:

= TEXTJOIN (",", TRUE, IF (D2 = $ A $ 2: $ A $ 20, $ B $ 2: $ B $ 20, ""))

Pēc formulas ievadīšanas šūnā E2 kopējiet to visām šūnām, kurās vēlaties iegūt rezultātus.

Kā šī formula darbojas?

Ļaujiet man dekonstruēt šo formulu un izskaidrot katru daļu, kā tā sanāk kopā, dod mums rezultātu.

Loģiskais tests IF formulā (D2 = $ A $ 2: $ A $ 20) pārbauda, ​​vai nosaukuma šūna D2 ir tāda pati kā diapazonā A2: A20.

Tas iet caur katru šūnu diapazonā A2: A20 un pārbauda, ​​vai šūnā D2 nosaukums ir vienāds vai nē. ja tas ir tāds pats nosaukums, tas atgriež TRUE, pretējā gadījumā atgriež FALSE.

Tātad šī formulas daļa sniegs jums masīvu, kā parādīts zemāk:

{TRUE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}]

Tā kā mēs vēlamies apgūt tikai Boba apmācību (vērtība šūnā D2), mums ir jāapgūst visas atbilstošās apmācības šūnām, kuras iepriekšējā masīvā atgriež TRUE.

To var viegli izdarīt, norādot IF formulas [value_if_true] daļu kā diapazonu, kuram ir apmācība. Tādējādi tiek nodrošināts, ka, ja nosaukums šūnā D2 atbilst nosaukumam diapazonā A2: A20, IF formula atgriezīs visu šīs personas apmācību.

Un visur, kur masīvs atgriež FALSE, mēs esam norādījuši [value_if_false] vērtību kā “” (tukšs), tāpēc tas atgriež tukšu.

Formulas IF daļa atgriež masīvu, kā parādīts zemāk:

{"Excel"; ""; ""; "PowerPoint"; ""; ""; "" ";" ";" ";" ";" " ; ""; "" ";" "}

Tur, kur ir Boba veiktās apmācības nosaukumi un tukšas vietas, kur vārds nebija Bobs.

Tagad viss, kas mums jādara, ir apvienot šo apmācības nosaukumu (atdalot ar komatu) un atdot to vienā šūnā.

Un to var viegli izdarīt, izmantojot jauno TEXTJOIN formulu (pieejama programmā Excel2021-2022 un Excel programmā Microsoft 365)

TEXTJOIN formulai ir trīs argumenti.

• atdalītājs - mūsu piemērā tas ir “”, jo es vēlos, lai apmācība tiktu atdalīta ar komatu un atstarpi
• TRUE - kas liek TEXTJOIN formulai ignorēt tukšās šūnas un apvienot tikai tās, kuras nav tukšas
• Formula If, kas atgriež tekstu, kas jāapvieno

Ja izmantojat Excel programmā Microsoft 365, kurai jau ir dinamiski masīvi, varat vienkārši ievadīt iepriekš minēto formulu un nospiest taustiņu Enter. Un, ja izmantojat Excel2021-2022, jums jāievada formula, turiet nospiestu taustiņu Control un Shift un pēc tam nospiediet taustiņu Enter

Noklikšķiniet šeit, lai lejupielādētu parauga failu un sekotu tam

Iegūstiet vairākas uzmeklēšanas vērtības vienā šūnā (bez atkārtošanās)

Tā kā UNIKĀLĀ formula ir pieejama tikai programmā Excel programmā Microsoft 365, šo metodi nevarēsit izmantot programmā Excel2021-2022

Ja jūsu datu kopā ir atkārtojumi, kā parādīts zemāk, jums ir nedaudz jāmaina formula, lai unikālā vērtība tiktu parādīta tikai vienā šūnā.

Iepriekš minētajā datu kopā daži cilvēki ir mācījušies vairākas reizes. Piemēram, Bobs un Stens divas reizes ir apmeklējuši Excel apmācību, bet Betija - MS Word. Bet mūsu rezultātā mēs nevēlamies, lai apmācības nosaukums tiktu atkārtots.

Lai to izdarītu, varat izmantot zemāk esošo formulu:

= TEXTJOIN (",", TRUE, UNIQUE (JA (D2 = $ A $ 2: $ A $ 20, $ B $ 2: $ B $ 20, "")))

Iepriekš minētā formula darbojas tāpat, ar nelielām izmaiņām. mēs esam izmantojuši IF formulu UNIQUE funkcijā, lai gadījumā, ja formulas if rezultātā būtu atkārtojumi, funkcija UNIQUE to noņemtu.

Noklikšķiniet šeit, lai lejupielādētu parauga failu

Vairāku vērtību meklēšana un atgriešana vienā šūnā (izmantojot VBA)

Ja izmantojat Excel 2016 vai iepriekšējās versijas, jums nebūs piekļuves formai TEXTJOIN. Tātad labākais veids, kā pēc tam meklēt un iegūt vairākas atbilstošas ​​vērtības vienā šūnā, ir izmantot pielāgotu formulu, kuru varat izveidot, izmantojot VBA.

Lai vienā šūnā iegūtu vairākas uzmeklēšanas vērtības, mums ir jāizveido funkcija VBA (līdzīga funkcijai VLOOKUP), kas pārbauda katru kolonnas šūnu un, ja tiek atrasta uzmeklēšanas vērtība, pievieno to rezultātam.

Šeit ir VBA kods, kas to var izdarīt:

'Kods pēc Sumita Bansāla (https://trumpexcel.com) Funkcija SingleCellExtract (Lookupvalue kā virkne, LookupRange kā diapazons, ColumnNumber kā vesels skaitlis) Dim i Kā Long Dim Rezultāts kā virkne i = 1 līdz LookupRange.Columns (1). .Skaitīt, ja LookupRange.Cells (i, 1) = Lookupvalue then Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," End If Next i SingleCellExtract = Left (Result, Len (Result) - 1) Beigu funkcija

Kur likt šo kodu?

1. Atveriet darbgrāmatu un noklikšķiniet uz Alt + F11 (tas atver VBA redaktora logu).
2. Šajā VBA redaktora logā kreisajā pusē ir projektu pētnieks (kur ir uzskaitītas visas darbgrāmatas un darblapas). Ar peles labo pogu noklikšķiniet uz jebkura darbgrāmatas objekta, kurā vēlaties, lai šis kods darbotos, un dodieties uz Ievietot -> Modulis.
3. Moduļa logā (kas parādīsies labajā pusē) nokopējiet un ielīmējiet iepriekš minēto kodu.
4. Tagad jūs esat gatavs. Dodieties uz jebkuru darbgrāmatas šūnu un ierakstiet = SingleCellExtract un pievienojiet nepieciešamos ievades argumentus (t.i., LookupValue, LookupRange, ColumnNumber).

Kā šī formula darbojas?

Šī funkcija darbojas līdzīgi funkcijai VLOOKUP.

Ievadam nepieciešami 3 argumenti:

1. Uzmeklēšanas vērtība - Virkne, kas mums jāmeklē šūnu diapazonā.
2. LookupRange - Šūnu masīvs, no kura mums jāiegūst dati ($ B3: $ C18 šajā gadījumā).
3. ColumnNumber - Tas ir tabulas/masīva kolonnas numurs, no kura jāatgriež atbilstošā vērtība (šajā gadījumā 2).

Izmantojot šo formulu, tā pārbauda katru šūnu uzmeklēšanas diapazona kreisajā kolonnā un, atrodot atbilstību, tas pievieno rezultātu šūnā, kurā izmantojāt formulu.

Atcerieties: Saglabājiet darbgrāmatu kā makro iespējotu darbgrāmatu (.xlsm vai .xls), lai vēlreiz izmantotu šo formulu. Turklāt šī funkcija būtu pieejama tikai šajā darbgrāmatā, nevis visās darbgrāmatās.

Noklikšķiniet šeit, lai lejupielādētu parauga failu

Uzziniet, kā automatizēt garlaicīgus atkārtotus uzdevumus, izmantojot programmu VBA programmā Excel. Pievienojieties Excel VBA kurss

Iegūstiet vairākas uzmeklēšanas vērtības vienā šūnā (bez atkārtošanās)

Pastāv iespēja, ka datos var būt atkārtojumi.

Ja izmantojat iepriekš izmantoto kodu, tas arī atkārtos rezultātu.

Ja vēlaties iegūt rezultātu, ja nav atkārtojumu, kods ir nedaudz jāmaina.

Šeit ir VBA kods, kas sniegs jums vairākas uzmeklēšanas vērtības vienā šūnā bez atkārtojumiem.

'Kods pēc Sumita Bansāla (https://trumpexcel.com) Funkcija MultipleLookupNoRept (Lookupvalue kā virkne, LookupRange kā diapazons, ColumnNumber kā vesels skaitlis) Dim i Kā Long Dim rezultāts kā virkne i = 1 līdz LookupRange.Columns (1). .Skaitīt, ja LookupRange.Cells (i, 1) = Uzmeklēšanas vērtība, tad J = 1 Uz i - 1 Ja LookupRange.Cells (J, 1) = Meklēšanas vērtība Tad, ja LookupRange.Cells (J, ColumnNumber) = LookupRange.Cells (i, ColumnNumber) Pēc tam GoTo Izlaist beigas, ja beigas, ja nākamais J Rezultāts = Rezultāts & "" & LookupRange.Cells (i, ColumnNumber) & "," Skip: End If Next i MultipleLookupNoRept = Left (Result, Len (Result) - 1) End Funkcija

Kad esat ievietojis šo kodu VB redaktorā (kā parādīts iepriekš apmācībā), jūs varēsit izmantot MultipleLookupNoRept funkciju.

Šeit ir momentuzņēmums par rezultātu, ko jūs iegūsit ar šo MultipleLookupNoRept funkciju.

Noklikšķiniet šeit, lai lejupielādētu parauga failu

Šajā apmācībā es apskatīju, kā programmā Excel izmantot formulas un VBA, lai atrastu un atgrieztu vairākas uzmeklēšanas vērtības vienā Excel šūnā.

Lai gan to var viegli izdarīt ar vienkāršu formulu, ja izmantojat Excel programmā Microsoft 365, ja izmantojat iepriekšējās versijas un jums nav piekļuves tādām funkcijām kā TEXTJOIN, to joprojām var izdarīt, izmantojot VBA, izveidojot sava pielāgota funkcija.