Hoe u een Excel-formule schrijft die een waarde op een ander blad opzoekt op basis van welk product is geselecteerd. Gegevens ophalen uit een ander werkblad voor elk product.
Bekijk video
- Rhonda uit Cincinnati: Hoe zoek ik zowel rij als werkblad op?
- Gebruik de kolom Datum om erachter te komen welk blad u moet gebruiken
- Stap 1: Bouw een gewone VERT.ZOEKEN en gebruik FORMULATEXT om te zien hoe de referentie eruit moet zien
- Stap 2: Gebruik Concatenation en de TEXT-functie om een verwijzing te bouwen die eruitziet als de verwijzing naar de tabelmatrix in de formule
- Stap 3: Bouw uw VERT.ZOEKEN, maar gebruik voor de tabelmatrix INDIRECT (resultaten uit stap 2)
- Stap 4: Kopieer de formule uit stap 2 (zonder het gelijkteken) en plak deze in de formule uit stap 3
Videotranscriptie
Leer Excel van Podcast, aflevering 2173: zoek het blad en de rij op.
Hé, welkom terug bij de netcast, ik ben Bill Jelen. Ik was vorige week in Cincinnati en Rhonda in Cincinnati had deze geweldige vraag. Rhonda moet dit product opzoeken, maar de Look Up-tabel is anders, afhankelijk van de maand. Kijk, we hebben hier verschillende Look Up-tabellen voor januari tot en met april, en vermoedelijk ook voor de andere maanden. Oké.
Dus ik ga INDIRECT gebruiken om dit op te lossen, maar voordat ik INDIRECT doe, vind ik het altijd gemakkelijker om gewoon een directe VERT.ZOEKEN uit te voeren. We kunnen dus zien hoe het formulier eruit gaat zien. We zoeken dus A1 op in deze tabel in januari, en we willen de zevende kolom, komma FALSE, alle VERT.ZOEKEN eindigen op FALSE. (= VERT.ZOEKEN (A2, 'jan. 2018'! A1: G13,7, FALSE)). Oké.
En, nou, dat is het juiste antwoord. Waar ik echt in geïnteresseerd ben, is de formuletekst daarvan. Dus het laat me zien hoe de formule eruit gaat zien. En de hele truc hier is: ik probeer een Helper-kolom te bouwen die er precies zo uitziet als deze Referentie, toch? Dus dit deel, precies dat deel daar. Oké. Dus deze Helper-kolom moet eruitzien zoals dat ding eruitziet. En het eerste dat ik wil doen, is dat we de TEKST-functie van de Datum gaan gebruiken - de TEKST-functie van de datum - om mmm, spatie, jjjj te krijgen - dus "mmm jjjj" zoals dat- - die moet voor elk van de cellen teruggeven in welke maand we opzoeken. Nu moet ik dat in apostrofs verpakken. Als er geen spatie in had gestaan, had ik de apostrofs niet nodig, maar ik wel. Dus we gaan van tevoren concacteneren,de apostrof, dus dat is citaat - apostrof, citaat - ampersand, en dan hier een ander citaat, apostrof en uitroepteken, A1: G13, slotcitaat, ampersand daar.
Oké. Dus wat we nu met succes hebben gedaan hier in de Helper-kolom, is dat we iets hebben gebouwd dat er precies zo uitziet als de tabelarray in VERT.ZOEKEN. Oké. Ons antwoord is dus = VERT.ZOEKEN van deze cel, A2, komma, en als we bij de tabelmatrix komen, gaan we de INDIRECT gebruiken. INDIRECT is deze coole functie die zegt: "Hé, hier is een cel die eruitziet als een celverwijzing, en ik wil dat je naar F2 gaat, het ding neemt dat eruitziet als een celverwijzing, en dan alles in die celverwijzing gebruikt als het antwoord 'komma, 7, komma, ONWAAR', zoals dat. (= VERT.ZOEKEN (A2, 'Jan 2018'! A1: G13,7, FALSE)) Oké, dus nu kiezen we meteen een verschillende opzoektabel en retourneert de waarden, afhankelijk van of het april is of wat.
Oké. Dus laten we dit 24-4 nemen, ik zal het zo veranderen in 17-2-2018, en we zouden moeten zien dat 403 verandert in 203 - perfect. Het werkt. Oké. Nu hebben we deze twee kolommen hier natuurlijk niet nodig, en als je erover nadenkt, hebben we deze hele kolom niet nodig. We zouden dat hele ding kunnen nemen, behalve het gelijkteken, Ctrl + C om het te kopiëren, en dan waar we D2 hebben, gewoon plakken, zoals dat. Perfect. Dubbelklik om dat neer te schieten en dit te verwijderen. Daar is ons antwoord. Oké, we zullen onze formuletekst hier gebruiken, alleen om dat laatste antwoord te bekijken.
Ik moet je zeggen dat als ik die formule helemaal opnieuw zou moeten bouwen, ik het niet zou doen. Ik zou het niet kunnen. Ik zou het zeker verknoeien. Daarom bouw ik het altijd in stappen - ik zoek uit hoe de formule eruit zal zien en concacteneer dan de Helper-kolom die in de INDIRECT zal worden gebruikt, en dan eindelijk, misschien hier aan het einde, alles weer in elkaar.
Hé, veel tips zoals deze tip in het boek Power Excel met. Dit is de editie 2017 met 617 Excel-mysterie opgelost. Klik op die "I" in de rechterbovenhoek voor meer informatie.
Oké, afsluiting van deze aflevering: Rhonda uit Cincinnati - hoe je zowel de rij als het werkblad opzoekt. Ik gebruik de Datum-kolom om erachter te komen welk blad ik moet gebruiken; dus ik bouw een gewone VERT.ZOEKEN en gebruik formuletekst om te zien hoe de referentie eruit moet zien; en bouw dan iets dat op die referentie lijkt met behulp van de tekstfunctie om de datum naar een maand en jaar te converteren; gebruik concactenatie om iets te bouwen dat op de referentie lijkt; en als je dan je VERT.ZOEKEN bouwt voor het tweede argument, de tabelmatrix, gebruik dan INDIRECT; en wijs vervolgens naar de resultaten van stap 2; en dan de optionele vierde stap daar, kopieer de formule van stap 2, zonder het gelijkteken, en plak deze in de formule van stap 3, zodat je een enkele formule krijgt.
Nou, ik wil Rhonda bedanken voor het verschijnen op mijn seminar in Cincinnati, en ik wil je bedanken voor het langskomen. Ik zie je de volgende keer voor nog een netcast van.
Download bestand
Download het voorbeeldbestand hier: Podcast2173.xlsm
