Come scrivere una formula Excel che cercherà un valore su un foglio diverso in base al prodotto selezionato. Come estrarre dati da un foglio di lavoro diverso per ogni prodotto.
Guarda un video
- Rhonda di Cincinnati: come cercare sia la riga che il foglio di lavoro?
- Usa la colonna Data per capire quale foglio usare
- Passaggio 1: crea un VLOOKUP regolare e usa FORMULATEXT per vedere come dovrebbe apparire il riferimento
- Passaggio 2: utilizzare la concatenazione e la funzione TESTO per creare un riferimento che assomigli al riferimento all'array della tabella nella formula
- Passaggio 3: crea il tuo CERCA.VERT, ma per l'array di tabelle utilizza INDIRETTO (risultati dal passaggio 2)
- Passaggio 4: copia la formula dal passaggio 2 (senza il segno di uguale) e incollala nella formula dal passaggio 3
Trascrizione del video
Impara Excel da Podcast, episodio 2173: Look Up the Sheet and Row.
Ehi, bentornato al netcast, sono Bill Jelen. Ero a Cincinnati la scorsa settimana e Rhonda a Cincinnati aveva questa grande domanda. Rhonda ha bisogno di cercare questo prodotto ma la tabella Look Up è diversa, a seconda del mese in cui si trova. Vedi, abbiamo diverse tabelle di ricerca qui da gennaio ad aprile e presumibilmente anche per gli altri mesi. Tutto a posto.
Quindi userò INDIRETTO per risolvere questo problema, ma prima di fare INDIRETTO, trovo sempre più semplice eseguire semplicemente un CERCA.VERT. Quindi, possiamo vedere come sarà il modulo. Quindi stiamo cercando A1 in questa tabella a gennaio e vogliamo che la settima colonna, virgola FALSE, tutti i CERCA.VERT terminino con FALSO. (= CERCA.VERT (A2, 'gennaio 2018'! A1: G13,7, FALSO)). Tutto a posto.
E, beh, questa è la risposta giusta. Quello che mi interessa davvero è ottenere il testo della formula. Quindi mi mostra come sarà la formula. E l'intero trucco qui è che sto cercando di creare una colonna Helper che assomigli esattamente a questo Riferimento, giusto? Quindi questa parte … solo quella parte proprio lì. Tutto a posto. Quindi questa colonna Helper deve assomigliare a quella cosa. E la prima cosa che voglio fare è che useremo la funzione TESTO della data, la funzione TESTO della data, per ottenere mmm, spazio, aaaa, quindi "mmm aaaa" in questo modo- - che dovrebbe restituire, per ciascuna cella, il mese che stiamo cercando. Ora, devo racchiuderlo in apostrofi. Se non ci fosse stato un nome di spazio lì dentro, non avrei bisogno degli apostrofi, ma lo faccio. Quindi ci concentreremo in primo piano,l'apostrofo, quindi questa è citazione-- apostrofo, citazione-- e commerciale, e poi qui, un'altra citazione, apostrofo e punto esclamativo, A1: G13, virgoletta di chiusura, e commerciale lì.
Tutto a posto. Quindi ora, quello che abbiamo fatto con successo qui nella colonna Helper, è che abbiamo creato qualcosa che assomiglia esattamente all'array di tabelle in CERCA. Tutto a posto. Quindi la nostra risposta, quindi, sarà = CERCA.VERT di questa cella, A2, virgola, e poi quando arriveremo all'array della tabella, useremo INDIRETTO. INDIRETTO è questa fantastica funzione che dice: "Ehi, ecco una cella che sembra un riferimento di cella e voglio che tu vada a F2, prenda la cosa che sembra un riferimento di cella e quindi usi tutto ciò che è in quel riferimento di cella come la risposta, "virgola, 7, virgola, FALSO", in questo modo. (= CERCA.VERT (A2, 'gennaio 2018'! A1: G13,7, FALSO)) Va bene, quindi ora, al volo, scegliamo un tabella di ricerca diversa e restituire i valori a seconda che sia aprile o cosa.
Tutto a posto. Quindi prendiamo questo 24/4, lo cambierò in 17/2/2018, in questo modo, e dovremmo vedere che 403 cambia in 203, perfetto. Funziona. Tutto a posto. Ora, non abbiamo bisogno di queste due colonne qui, ovviamente, e davvero, se ci pensi, non abbiamo bisogno dell'intera colonna. Potremmo prendere l'intera cosa, ad eccezione del segno di uguale, Ctrl + C per copiarlo, e poi dove abbiamo D2, incolla, così. Perfetto. Fai doppio clic per abbatterlo e sbarazzartene. Ecco la nostra risposta. Va bene, useremo il nostro testo della formula qui, solo per dare un'occhiata a quella risposta finale.
Devo dirtelo, se dovessi costruire quella formula da zero, non lo farei. Non sarei in grado di farlo. Lo farei un casino, di sicuro. Ecco perché lo costruisco sempre per fasi: capisco come sarà la formula e poi concateno la colonna Helper che verrà utilizzata all'interno di INDIRETTO, e poi finalmente, forse qui alla fine, rimetto tutto insieme.
Ehi, molti suggerimenti come questo suggerimento nel libro, Power Excel con. Questa è l'edizione 2017 con 617 misteri di Excel risolti. Fare clic sulla "I" nell'angolo in alto a destra per ulteriori informazioni.
Va bene, riepilogo da questo episodio: Rhonda da Cincinnati - come cercare sia la riga che il foglio di lavoro. Uso la colonna Data per capire quale foglio usare; quindi creo un VLOOKUP regolare e utilizzo il testo della formula per vedere come dovrebbe apparire il riferimento; e quindi creare qualcosa che assomigli a quel riferimento utilizzando la funzione di testo per convertire la data in un mese e un anno; usa la concattenazione per costruire qualcosa che assomigli al riferimento; e poi quando crei il tuo CERCA.VERT per il secondo argomento, l'array della tabella, usa INDIRETTO; e quindi puntare ai risultati del passaggio 2; e poi il quarto passaggio facoltativo lì, copia la formula dal passaggio 2, senza il segno di uguale, e incollala nella formula dal passaggio 3, così finisci con una singola formula.
Bene, voglio ringraziare Rhonda per essersi presentata al mio seminario a Cincinnati, e voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.
Download file
Scarica il file di esempio qui: Podcast2173.xlsm
