Vai Excel VLOOKUP var atgriezt visus rezultātus un savienot tos ar komatu starplaikos?
Skatīties video
- Mērķis ir savienot visas teksta atbildes no VLOOKUP
- Bila metode: izmantojiet VBA funkciju ar nosaukumu GetAll
- Unikāls saraksts, izmantojot opciju Noņemt dublikātus
- Maika metode:
- Unikāls saraksts, izmantojot papildu filtru
- Office 365 pievienota funkcija TEXTJOIN
TEXTJOIN(", ",,IF(OilChangeData(ID)=D2,OilChangeData(Comment),""))- Funkcijas IF dēļ formulas rediģēšanas laikā ir nepieciešami taustiņi Ctrl + Shift + Enter
- Alt AQOR Enter atkārtoti palaidīs papildu filtru!
Video atšifrējums
183. sērija: Pievienojieties visām VLOOKUP spēlēm
Bils Jelens: Hei, laipni gaidīti. Ir pienācis laiks kārtējam Dueling Excel Podcast. Es esmu Bils Jelen no, man pievienosies Maiks Girvins, kurš darbojas programmā Excel Is Fun. Šī ir mūsu 183. sērija: Pievienojieties visām VLOOKUP spēlēm.
(Mūzika)
Labi, šodienas jautājums no Meta. Vai VLOOKUP var atgriezt visus rezultātus un savienot tos kopā ar komatu atstarpi starp katru. Piemēram, 109876, kas šeit ir šie divi, vai tas var atgriezt zema komata atstarpi Pārbaudīts 12/12. Un, protams, ja būtu vairāk, tas atgrieztos vairāk. Labi, tāpēc mans risinājums šeit būs izmantot kādu VBA. Labi, tāpēc pārliecinieties, vai tas ir saglabāts kā xlsm, vai arī jūs nevarat palaist VBA vai xlsb, bet ne xlsx - xlsx ir viens fails, kurā nevar palaist VBA. Mēs nospiedīsim Alt + F11, pārliecinieties, vai izmantojat Dual183 vai kāds ir jūsu darbgrāmatas nosaukums. Ievietojiet moduli tukšajā modulī, un mēs labi ielīmēsim šo kodu.
Apskatīsim šo funkciju GetAll, un šeit ir ID numurs, kuru meklējam, un pēc tam diapazons, kuru mēs vēlamies meklēt. Un mēs sākam, mēs atgriezīsim mainīgo ar nosaukumu GetAll, tāpēc mēs sākam ar to, ka tas ir vienāds ar tukšu tukšu. Katrai šūnai manā diapazonā, ja šūnas vērtība ir tā, ko mēs meklējam, mēs ņemsim GetAll = GetAll & “” un pēc tam Cell.Offset (0 rindas, 1 kolonna), citiem vārdiem sakot, vērtība tas ir tikai blakus šim ID numuram, jo, atrodoties VBA, šeit ir ID numurs. Ja atrodam atbilstošo ID numuru, mēs vēlamies pāriet uz vienu kolonnu. Ko darīt, ja jūs vēlaties pāriet 2 kolonnām vai 3 kolonnām, labi, tad jūs maināt šīs 0 rindas un 1 kolonnu uz 2. Labi, pārbaudiet arī, vai - mēs neliekam komatu atstarpi, ja tas ir pirmais.Tātad, ja mainīgais GetAll pašlaik ir “”, tad komatu atstarpi neliksim, labi?
Tāpēc tagad, kad mums ir šī funkcija, skatieties, cik viegli ir atrisināt Meta problēmu. Mēs ieradīsimies šeit un paņemsim viņa ID, Ctrl + C un ielīmēsim Ctrl + V tā. Dati, Noņemt dublikātus, noklikšķiniet uz Labi. Tātad ir unikāls ID saraksts, un tad mēs vēlamies teikt = getall, un mēs meklējam šo vērtību E2 komatā. Pārskatot šo diapazonu šeit, es nospiedīšu F4. F4 darbojas tāpat kā parasta funkcija. Un atkal novirzot Metta jautājumu no ceļa, veiciet dubultklikšķi, lai to nošautu. Tas darbosies.
Un mēģināsim tikai, izmēģināsim šeit kaut ko traku. Izdarīsim 1. frāzi un vienkārši ievietosim tās, piemēram, no 1. līdz 10. frāzei. Mēs tos visus parakstīsim uz 109999. Ielīmējiet un pēc tam ielīmējiet šeit. Kopējiet šo formulu uz leju, rediģējiet formulu, lai tā, protams, ietu līdz apakšai. Jā. Un tas atgriezīs visas šīs frāzes. Labi, tāpēc tas ir mans risinājums, VBA, tur neliela funkcija. Maik, paskatīsimies, kas tev ir.
Maiks Girvins: Paldies ,. GetAll, tā ir lieliska VBA funkcija. Labi, es eju šeit pie lapas. Es to jau esmu pārveidojis par Excel tabulu, lai, pievienojot ierakstus zemāk, cerams, ka lietas tiks atjauninātas.
Tagad vispirms darīšu to divās daļās. Es šeit varētu izdarīt formulu unikāla saraksta iegūšanai, bet es vēlos apskatīt citu iespēju: Papildu filtram ir unikāla unikāla saraksta opcija, un to var atjaunināt. Es izcelšu tikai ID kolonnas datus, pāriet uz Advanced Filter vai izmantošu tastatūru Alt, A, Q. Tagad filtrējiet sarakstu vietā, nekādā gadījumā. Es vēlos to nokopēt uz citu vietu. Tas ieguva tikai A kolonnu un tāpēc, ka tā ir Excel tabula, kas vēlāk tiks paplašināta. Man nav nekādu kritēriju, es vēlos to nokopēt uz D1 un pārbaudīt tikai unikālos ierakstus. Noklikšķiniet uz Labi.
Tagad es eju šeit, ievadiet visus komentārus un es izmantošu funkciju, kas darbojas tikai programmā Excel 2016 Office 365: = TEXTJOIN. Tikai šī funkcija ir vērts iegūt jaunāko Excel versiju. Tas ir tik izplatīts uzdevums, kuru cilvēki vēlas paveikt, apvienot daudzas lietas kopā. Tagad mūsu atdalītājs “,”, un lieliski par šo funkciju varam pateikt Ignorēt tukšās šūnas. Tagad es varu ievietot TRUE, 1 vai atstāt, izlaist. Tātad, es to atstāšu, izlaidiet. Un šeit ir vajadzīgs mūsu teksts. Mēs izmantosim IF funkciju, lai filtrētu un iegūtu tikai vēlamos priekšmetus. Es teikšu, ka apskatiet visu šo kolonnu šeit: Tabulas nosaukums un pēc tam lauka nosaukumā () ir kāds no jums = uz šo relatīvo šūnu atsauci, tas ir loģiskais tests. Ja es noklikšķinātu uz šī un nospiediet taustiņu F9, lai novērtētu,jūs jau tagad redzējāt, ka mums ir tikai 2 PATIESĪBAS, Ctrl + Z. Tagad es ievadu komatu un ar virkni Trues and Falses, tagad es varu dot tam vienumus, kurus izvēlēties. Tāpēc tagad no šī diapazona izvēlēsimies tikai tos priekšmetus, kuriem šeit ir PATIESĪBA. Mēs ar komatu vēlamies pārliecināties, vai ir ievietots “” - tas tiks parādīts kā tukša šūna attiecībā uz otro argumentu TEXTJOIN.
Tagad es aizvēršu iekavas, un tagad funkcija IF izveidos šo virkni Trues and Falses, faktiskie vienumi no šī diapazona tiks uzņemti, ja tas redzēs to True, un visiem pārējiem vienumiem būs tukša šūna. Un uzmini ko? TEXTJOIN pilnībā ignorēs visas šīs tukšās šūnas un atgriezīs tikai vienumus, kas atbilst šim ID, un pēc tam pievienosies tam ar šo atdalītāju. Tagad šī noteikti ir masīva formula, kurai nepieciešama īpaša taustiņu kombinācija Ctrol + Shift + Enter. Loģiskais testa arguments satur mūsu masīva darbību, un šis arguments nevar pareizi aprēķināt šo masīva darbību, ja vien mēs neizmantojam tastatūru Ctrl + Shift + Enter. Tagad es aizvēršu iekavas. Patiesībā mēs varētu pierādīt 1 tieši šeit, 1. tekstā, ja es F9 to visu redzētu, ka mēs iegūstam 2 vienumus, pārējās šīs tukšās šūnas tiks ignorētas. Ctrl + Z. Tagad ļaujiets ievadiet to šūnā, izmantojot Ctrl + Shift + Enter. Nekavējoties uzmeklējiet Formulas joslu. Šīs cirtainās iekavas ir programma Excel, kas jums to saprot un aprēķina kā masīva formulu. Tagad es varu veikt dubultklikšķi un nosūtīt to uz leju. Tas izskatās labi.
Dodos uz pēdējo šūnu un nospiedīšu F2, lai pārbaudītu, vai visi diapazoni izskatās pareizi. Tagad es nevēlos darīt, ka es nevēlos nospiest taustiņu Enter, jo šī formula pēc tam, kad to būsim ievietojuši rediģēšanas režīmā, pareizi aprēķinās tikai tad, ja mēs izmantosim Ctrl + Shift + Enter; vai, tā kā mēs jau esam ievadījuši formulu, mēs varam vienkārši izmantot taustiņu Esc, lai atgrieztos šūnā, pirms mēs to ievietojam rediģēšanas režīmā.
Tagad pārbaudīsim to. Es noklikšķināšu šeit esošajā pēdējā šūnā un nospiediet Tab un pēc tam ierakstiet jaunu ID, Tab, Tab. Vēl viens jauns ieraksts Tab un es jau redzu, ka man šeit nepietika darba. Es esmu, mēs noliksim - Perfect un pēc tam Enter. Tagad tas netiks automātiski atjaunināts, piemēram, ja mums ir virkne formulu, kuras mēs saskaitām unikālus vienumus un pēc tam iegūstam unikālus vienumus, taču nav problēmu. Skatīties šo. Mēs varam atjaunināt šo unikālo ierakstu sarakstu, jo mēs izmantojām papildu filtru, un nav svarīgi arī no kuras šūnas jūs sākat, jo, kad tiek izsaukts papildu filtrs, tas iegaumē izvilkuma diapazonu un diapazonus, kurus sākotnēji skatījās. Jūs varat noklikšķināt uz Papildu filtrs vai izmantot tastatūru Alt + A + Q. Mums patiešām ir jāizvēlas Kopēt uz citu vietu, bet paskatieties uz to.Excel tabulas funkcijas dēļ tas pilnībā atcerējās un paplašinājās līdz A13. Tas atcerējās ekstraktu diapazonu. Man ir jāpārbauda tikai unikālie ieraksti, bet noklikšķiniet uz Labi.
Tagad man nākas pārnest šo formulu uz leju. Tur jūs ejat, izmantojot masīvu operētājsistēmu Advanced Filter un apbrīnojamo funkciju TEXTJOIN, lai iegūtu tikai atbilstošos priekšmetus. Labi, meties atpakaļ uz.
Bils Jelens: Hei, Maik, tas ir lieliski. Labi, ietin šo epizodi. Es izmantoju VBA funkciju ar nosaukumu GetAll, un manu unikālo sarakstu izveidoja Remove Duplicates, kas ir daudz vieglāk nekā Advanced Filter, taču problēma ir tā, ka tā ir vienreizēja lieta. Tas neatceras iepriekšējos iestatījumus. Maiks izveidoja savu unikālo sarakstu, izmantojot uzlaboto filtru, kas nozīmē, ka viņš vēlāk to varēja pārveidot, nenorādot ievades diapazonu un izvilkuma diapazonu. Un tad TEXTJOIN, skaista jauna funkcija, pievienoja Office 365. Maiks saka, ka tas vien ir iemesls, lai iegūtu jaunāko Office. Es teicu, ka TEXTJOIN mainīs dzīvi. TEXTJOIN ir lielisks, jo tas var apstrādāt masīvus.
Alright, so here's the formula that Mike wrote: putting an IF in there and the “ ” returning the equivalent of an empty cell; and here we're saying Ignore empty cells. Ahh, that's beautiful but because of the IF function, the formula requires Ctrl+Shift+Enter to create the formula, or any time you edit the formula, all that Mike used to Esc to get out. And this section about IF forces you into Ctrl+Shift+Enter is a topic in Mike's awesome, awesome book, An Array Formulas Ctrl+Shift+Enter. Check that out at Amazon or elsewhere, your favorite bookseller. And then, the beautiful thing is that because Advanced Filter remembers the old settings, Mike used Alt+A+Q and then could have used O+R Enter, will rerun the Advanced Filter, copy the formula down for the new cells and it works. That is beautiful, alright.
Ak, hei, es gribu pateikties visiem, kas apstājās. Tiksimies nākamreiz, lai skatītu vēl vienu Dueling Excel Podcast no un Excel ir jautri.
Lejupielādēt failu
Lejupielādējiet faila paraugu šeit: Duel183.xlsm
