Tutti i segreti di Excel funzione CDF (CERCA.VERT) per la ricerca di dati in una tabella e recuperarli in un altro
Consigli Della Tecnologia / / December 19, 2019
Batyanov Denis sui diritti del l'ospite autore spiega in questo post su come trovare i dati in un foglio di calcolo Excel ed estrarli ad un altro, e scopre tutti i segreti della funzione di visualizzazione verticale.
Quando si lavora in eccellere molto spesso v'è la necessità di trovare i dati in una tabella e rimuoverli ad un altro. Se non sai come farlo, poi ho letto l'articolo, potrete non solo imparare, ma anche scoprire in quali condizioni si sarà in grado di spremere il massimo delle prestazioni del sistema. Considerato tecniche più altamente efficace che devono essere utilizzati in combinazione con la funzione della CDF.
Anche se si dispone di anni di utilizzo della funzione CDF, con elevata probabilità questo articolo sarà utile a voi e non vi lascerà indifferenti. Io, per esempio, come specialista, e poi il capo in esso, usato CERCA.VERT 15 anni, ma Per far fronte a tutte le sfumature accaduto poco fa, quando sono a titolo professionale ho cominciato a insegnare persone Excel.
CDF
- l'abbreviazione di inComposizione verticale, ecceteraispezione. Allo stesso modo, CERCA.VERT - verticale di RICERCA. Il nome della funzione ci suggerisce che cerca le righe della tabella (verticale - linea di smistamento e fissaggio asse), e non nelle colonne (orizzontale - ordinamento delle colonne e fissaggio line). Va notato che la CDF ha una sorella - il brutto anatroccolo che non potrà mai diventare un cigno - è una funzione del PGR (CERCA.ORIZZ). PGR, in contrasto con la CDF, produce una ricerca orizzontale, ma il concetto di Excel (e in effetti il concetto organizzazione dei dati) significa che le tabelle hanno un piccolo numero di colonne e molti altri linee. È per questo che la ricerca di stringhe, abbiamo bisogno di molte volte più che le colonne. Se si eccellere troppo spesso utilizzano HLOOKUP, è molto probabile che ci sia qualcosa che non si capisce in questa vita.sintassi
CERCA.VERT ha quattro parametri:
PPS = (
Scommetto che molti di coloro che conoscono la funzione CDF come peeling, leggete la descrizione del quarto parametro possono sentirsi a disagio perché utilizzato per vederlo in un modo leggermente diverso: di solito c'è un discorso sulla esatto accordo con la ricerca (FALSO o 0), o circa la stessa gamma di visualizzazione (TRUE o 1).
Ora è necessario stringere e leggere il paragrafo successivo un paio di volte fino ad ottenere una sensibilità per il significato di ciò che è stato detto fino alla fine. V'è importante per ogni parola. Gli esempi aiutano a capire.
Come la stessa formula CDF funziona particolarmente
- Tipo di formula I. Se l'ultimo parametro viene omesso o specificato come 1, le aspetta CDF che la prima colonna viene disposto in ordine crescente, così la ricerca si arresta sulla linea che precede immediatamente la riga in cui il valore maggiore del desiderato. Se questa linea non viene trovato, restituisce l'ultima riga della gamma.
- Tipo di formula II. Se quest'ultima opzione viene specificata come 0, allora il CDF analizza sequenzialmente la prima colonna della matrice e si ferma immediatamente cercare quando prima trovato una corrispondenza esatta con il parametro
, altrimenti un codice di errore viene restituito # N / A (# N / A).
le formule del regime
CDF tipo I
CDF tipo II
Implicazioni per le specie di formula I
- Le formule possono essere utilizzati per la distribuzione della gamma di valori.
- Se la prima colonna
contiene i duplicati e allineati correttamente, verrà restituita l'ultima delle righe con valori duplicati. - Se guardiamo per il valore, ovviamente, più che si può contenere la prima colonna, si può facilmente trovare l'ultima riga della tabella, che può essere molto prezioso.
- Questa vista restituirà un errore # N / A soltanto quando trova un valore inferiore o uguale alla desiderata.
- Capire che la formula restituisce un valore non corretto, se la matrice non è ordinata, abbastanza difficile.
Implicazioni per la specie di formula II
Se il valore si trova nella prima colonna della matrice più volte, la formula selezionerà la prima fila per il successivo recupero.
produttività VLOOKUP
Hai raggiunto il culmine del luogo di questo articolo. Sembrerebbe, così, qual è la differenza se faccio notare come ultimo parametro zero o uno? Fondamentalmente, tutto il punto, naturalmente, zero, dato che è molto pratico: non è necessario preoccuparsi di smistamento della prima colonna della matrice, si può vedere, il valore trovato o no. Ma se si dispone di un elenco di diverse migliaia di formule CDF (CERCA.VERT), si noterà che il tipo CDF II gira lentamente. Di solito è iniziato tutto a pensare:
- Ho bisogno di un computer più potente;
- Ho bisogno di una formula rapida, ad esempio, molte persone conoscono l'INDEX + Compatibilita '(indice + MATCH), che si suppone sia più veloce misero 5-10%.
E poche persone pensano che non appena iniziare a utilizzare il CDF di tipo I, fornisce alcun modo per ordinare la prima colonna come la velocità dell'operazione CDF aumenterà di 57 volte. Scrivo parole - in cinquanta sette volte! Nel 57% e 5700%. Questo fatto ho controllato abbastanza affidabile.
Il segreto per tali menzogne di lavoro rapidi nel fatto che è possibile applicare al array ordinato è estremamente algoritmo di ricerca efficiente, che è noto come un metodo di ricerca binaria (bisezione, dicotomia). Così ho tipo di CDF lo usa, e il tipo CDF II mira, senza alcuna ottimizzazione in generale. Lo stesso vale per il MATCH (MATCH), che comprende lo stesso parametro, e anche alla funzione GUARDANDO (ricerca), che funziona solo su array ordinati e attivato in Excel per la compatibilità con Lotus 1-2-3.
svantaggi formula
Svantaggi del CDF sono evidenti: in primo luogo, esso cerca solo la prima colonna della matrice, e in secondo luogo, appena a destra di questa colonna. E come si sa, può ben essere che la colonna che contiene le informazioni necessarie per la sinistra della colonna in cui cerchiamo. Privo di questa lacuna già detto un mucchio di formule INDEX + Compatibilita '(indice + MATCH), che lo rende la soluzione più flessibile per estrarre i dati dalle tabelle in confronto con il CDF (CERCA.VERT).
Alcuni aspetti della applicazione della formula nella vita reale
Ricerca gamma
illustrazione classica del campo di ricerca - il problema di determinare la dimensione delle Sconti di ordine.
Ricerca di stringhe di testo
Naturalmente, il CDF non cerca solo numeri, ma anche il testo. Va tenuto presente che la formula di maiuscole e minuscole non distingue. Se si utilizzano caratteri jolly, è possibile organizzare una ricerca fuzzy. Ci sono due jolly "?" - sostituisce ogni singolo carattere nella stringa di testo "*" - sostituisce qualsiasi numero di caratteri.
Combatti con spazi
Spesso la questione è sollevata, come risolvere il problema degli spazi in più nella ricerca. Se la tabella di look-up è ancora possibile pulirli, il primo parametro della formula CDF non sempre dipende da voi. Pertanto, se il rischio di intasamento delle celle spazio è presente, è possibile utilizzare la funzione TRIM (TRIM) per la pulizia.
Un formato di dati diversi
Se la prima funzione parametro CDF si riferisce a una cella che contiene un numero, ma che è memorizzato in cella come testo, e la prima colonna della matrice contiene i numeri nel formato corretto, la ricerca sarà senza successo. La situazione inversa. Il problema è facilmente risolvibile parametro di trasferimento 1 nel formato richiesto:
PPS = (- D7; Prodotti $ A $ 2: $ C $ 5;! 3; 0) - se D7 contiene il testo e il tavolo - i numeri;
= CDF (D7 e «»); Prodotti $ A $ 2: $ C $ 5;! 3; 0) - e viceversa.
Tra l'altro, per tradurre il testo in numero può essere diversi modi, scegli:
- Doppia -D7 Negative.
- unità di moltiplicazione D7 * 1.
- Inoltre con lo zero D7 + 0.
- La costruzione del primo grado D7 ^ 1.
Traduzione del testo prodotto da accoppiamento con una stringa vuota, che provoca Excel per convertire il tipo di dati.
Come sopprimere l'emissione # N / A
E 'molto comodo a che fare con la funzione IFERROR (IFERROR).
Ad esempio: = IFERROR (CDF (D7; Prodotti $ A $ 2: $ C $ 5;! 3; 0); «»).
Se il CDF restituirà un codice di errore # N / A, allora sarà intercettare IFERROR sostituto e parametro 2 (in questo caso, una stringa vuota), e Se si è verificato alcun errore, questa funzione finta che non lo è affatto, ma solo il CDF, restaurato alla normalità risultato.
Array
Spesso trascurato un riferimento ad array è fatto assoluto, e quando tira una matrice di "galleggianti". Ricordate che, invece di A2: C5 dovrebbe essere usato $ A $ 2: $ C $ 5.
Una buona idea è di mettere una matrice di riferimento su un foglio separato del lavoro. Non c'è niente sotto i piedi, e sarà memorizzato.
Ancora più buona idea di annuncio della matrice come un intervallo denominato.
Molti utenti sono utilizzati per specificare la struttura del tipo matrice A: C, che indica le colonne del tutto. Questo approccio ha il diritto di esistere, dal momento che si sono risparmiati di dover tenere traccia del fatto che il vostro array contiene tutte le corde. Se si aggiunge una linea su un pezzo della matrice originale, la gamma specificata come A: C, non sarà necessario regolare. Naturalmente, questa sintassi provoca Excel per trascorrere un po 'più di lavoro che con l'esatta indicazione della gamma, ma l'overhead dei dati è trascurabile. Stiamo parlando di centesimi di secondo.
Ebbene, sul punto di genio - al problema sotto forma di un array sMART table.
Utilizzando la funzione di colonna per specificare colonna di estrazione
Se la tabella in cui si estrarre i dati utilizzando il CDF, ha la stessa struttura della tabella di look-up, ma solo comprende un numero minimo di righe nel dominio WRT può essere utilizzata la funzione COLONNA () per calcolare automaticamente i numeri di recuperabile colonne. In questo caso tutti i CDF formula sarà lo stesso (rettificato per il primo parametro, che cambia automaticamente)! Si noti che il primo parametro è la coordinata assoluta della colonna.
Creazione di una chiave composta da & »|» &
Se v'è la necessità di guardare più di una colonna alla volta, è necessario fare una chiave composta per la ricerca. Se il valore restituito non è un testo (come qui nel caso del campo "Codice"), e numerica, per la Questo è venuto ad un comodo SOMMA.PIÙ.SE formula (SOMMA.PIÙ.SE) e una colonna chiave integrale non sarebbe necessario a tutti.
Questo è il mio primo articolo per Layfhakera. Se ti è piaciuto, allora vi invito a visitare il mio sitoE volentieri leggere i vostri commenti sui segreti di usare VLOOKUP e simili. Grazie. :)