Spesso in Excel devi elaborare stringhe di testo in un modo o nell'altro. È molto difficile eseguire tali operazioni manualmente quando il numero di righe è superiore a cento. Per comodità, Excel ha un buon set di funzioni per lavorare con un set di dati di stringhe. In questo articolo descriverò brevemente le funzioni necessarie per lavorare con le stringhe della categoria "Testo" e ne considererò alcune con esempi.
Figura 1 - Esempio di formula con doppio segno meno
Tuttavia, fornisce alcune opzioni piuttosto interessanti e potenti. Ma cosa fanno i due segni meno nella formula? La sintassi di questa formula sarà come mostrato di seguito. Questa formula funziona creando una matrice di dati larga quattro colonne. La quarta colonna dell'array è il prodotto della moltiplicazione delle prime tre colonne. Ciò è mostrato nella Figura 3. Per visualizzare un video di questo suggerimento, visitare.
Uno di modi migliori Migliorare la leggibilità dei fogli di lavoro significa visualizzare i dati in un formato logico, coerente e diretto. La formattazione degli importi in valuta con i simboli del dollaro iniziali, i tassi di interesse con i segni di percentuale meno e i numeri grandi con le virgole sono alcuni modi per migliorare lo stile del tuo foglio di calcolo.
Funzioni nella categoria "Testo"
Quindi, consideriamo le funzioni principali e utili della categoria "Testo", puoi familiarizzare con il resto.
- BATTESTO(Valore) - una funzione che converte un numero in un tipo di testo;
- DLSTR(Valore) è una funzione di supporto, molto utile quando si lavora con le stringhe. Restituisce la lunghezza della stringa, ad es. numero di caratteri contenuti nella riga;
- SOSTITUIRE(Vecchio testo, posizione iniziale, numero di caratteri, nuovo testo) - sostituisce il numero specificato di caratteri da una determinata posizione nel vecchio testo a uno nuovo;
- SIGNIFICATIVO(Testo) - converte il testo in un numero;
- SINISTRA(Stringa, Numero di caratteri) - una funzione molto utile, restituisce il numero specificato di caratteri, a partire dal primo carattere;
- GIUSTO(Stringa, Numero di caratteri) - analogo della funzione SINISTRA, con l'unica differenza che il ritorno dei caratteri dall'ultimo carattere della stringa;
- TROVARE(testo da cercare, testo in cui stiamo cercando, posizione iniziale) - la funzione restituisce la posizione da cui inizia l'occorrenza del testo cercato. I caratteri fanno distinzione tra maiuscole e minuscole. Se devi ignorare il caso dei caratteri, usa la funzione RICERCA. Viene restituita solo la posizione della prima occorrenza nella stringa!
- SOSTITUIRE(testo, vecchio testo, nuovo testo, posizione) - una funzione interessante, a prima vista sembra una funzione SOSTITUIRE, ma la funzione SOSTITUIRE in grado di sostituire tutte le occorrenze nella stringa con una nuova sottostringa se l'argomento "posizione" viene omesso;
- PSTR(Testo, Posizione iniziale, Numero di caratteri) - la funzione è simile a SINISTRA, ma è in grado di restituire i caratteri dalla posizione specificata:
- COLLEGARE(Testo1, Testo 2 .... Testo 30) – la funzione consente di collegare fino a 30 linee. Puoi anche usare il " & ”, sarà simile a questo “=”Text1” & ”Text2” & ”Text3””;
Queste sono fondamentalmente funzioni comunemente usate quando si lavora con le stringhe. Ora diamo un'occhiata a un paio di esempi che dimostreranno il funzionamento di alcune funzioni.
Imparerai anche come creare i tuoi formati per darti il massimo controllo sull'aspetto dei tuoi dati. Un'eccezione a questa regola si verifica quando si immette un numero più grande della cella. Quando crei un foglio di lavoro, ogni cella utilizza questo formato, noto come formato numerico generale, per impostazione predefinita.
Funzioni di ricerca e ricerca
I formati numerici hanno tre componenti: il numero di posizioni decimali, se viene utilizzato un separatore delle migliaia e come vengono visualizzati i numeri negativi. Per i numeri negativi, puoi visualizzare il numero con un segno meno, in rosso tra parentesi o in rosso tra parentesi. Formati numerici. . Questo vale anche per le percentuali. I formati percentuale visualizzano un numero moltiplicato per 100 con un simbolo di percentuale a destra del numero. È possibile visualizzare fino a 14 cifre decimali. Formati di frazione. I formati frazione consentono di esprimere i valori decimali come frazioni. Ci sono nove formati di frazione in totale, inclusa la visualizzazione di numeri come metà, quarti, ottavi, sedicesimi, decimi e centesimi. formati scientifici. I formati scientifici visualizzano il numero più significativo a sinistra del decimale, 2-30 cifre decimali a destra del decimale e quindi l'esponente. Ecco un elenco di formati speciali, con alcuni esempi.
- Tutte le voci negative sono mostrate tra parentesi.
- formati percentuali.
- Per esempio,. 506 viene visualizzato come 6%.
Esempio
1
Dato un insieme di linee:
È necessario estrarre date, numeri di fattura da queste righe e aggiungere anche il campo del mese per filtrare le righe per mese.
Estrai i numeri di fattura nella colonna B. Per fare questo, troviamo il cosiddetto simbolo o parola chiave. Nel nostro esempio, puoi vedere che ogni numero di fattura è preceduto da un "#" e la lunghezza del numero di fattura è di 6 caratteri. Usiamo le funzioni FIND e MID. Scriviamo la seguente formula nella cella B2:
Ecco alcuni altri esempi di questa tecnica. Si noti che è possibile immettere un valore negativo utilizzando un segno negativo o parentesi. Includere sempre uno zero iniziale seguito da uno spazio se si desidera inserire la parte semplice dalla barra della formula.
In ogni caso, non hai accesso a tutti i formati disponibili. Per superare queste limitazioni, puoi selezionare i formati numerici da un elenco.
- Seleziona la cella o l'intervallo di celle a cui vuoi applicare nuovo formato.
- Vai alla scheda "Home".
- Estrarre un elenco di numeri di formato.
= PSTR(LA2; TROVARE("№";A2)+1;6)
Diamo un'occhiata alla formula. Dalla riga A2 dalla posizione successiva al segno trovato "No", estraiamo 6 caratteri del numero.
Ora estraiamo la data. Tutto è semplice qui. La data si trova alla fine della riga e occupa 8 caratteri. La formula per C2 è la seguente:
= GIUSTO(LA2;8)
ma la data estratta sarà una stringa, per convertirla in una data è necessario dopo l'estrazione, convertire il testo in un numero:
Scorciatoie da tastiera per la selezione dei formati numerici
Seleziona la cella o l'intervallo che desideri formattare e utilizza una delle scorciatoie da tastiera elencate nella tabella. È possibile utilizzare i controlli nel gruppo Numero di casa come un altro metodo per selezionare i formati dei numeri. L'elenco Formato numero visualizza tutti i formati. Ecco gli altri controlli che appaiono in questo gruppo.
Funzioni nella categoria "Testo"
Ad esempio, nessun formato integrato consente di visualizzare un numero, ad esempio 5 senza uno zero iniziale, o visualizzare temperature, ad esempio con un simbolo di grado. Per superare queste e altre limitazioni, è necessario creare i propri formati numerici. Puoi farlo modificando un formato esistente o inserendo il tuo da zero. La sintassi ei caratteri di formattazione sono descritti in dettaglio più avanti in questa sezione.
= SIGNIFICATIVO(GIUSTO(LA2;8))
e quindi, impostare il formato di visualizzazione nella cella, come descritto nell'articolo "".
Infine, per comodità di filtrare ulteriormente le righe, inseriremo la colonna del mese, che otterremo dalla data. Solo per creare un mese, dobbiamo scartare il giorno e sostituirlo con "01". Formula per D2:
= SIGNIFICATIVO(COLLEGARE("01"; GIUSTO(A2;6))) o = SIGNIFICATIVO("01"& GIUSTO(LA2;6))
Formato positivo, formato negativo, formato zero, formato testo. . Le quattro parti, separate da punto e virgola, definiscono come vengono rappresentati i vari numeri. La prima parte determina come viene visualizzato un numero positivo, la seconda parte determina come viene visualizzato un numero negativo, la terza parte determina come viene visualizzato lo zero e la quarta parte determina come viene visualizzato il testo. Se lasci una o più di queste parti, i numeri sono controllati, come mostrato qui.
Funzioni di sostituzione e sostituzione
La Tabella 7 elenca i caratteri speciali utilizzati per definire ognuna di queste parti. Prima di guardare alcuni esempi, diamo un'occhiata alla procedura di base. Per impostare il formato del numero, seleziona la cella o l'intervallo che desideri formattare, quindi segui questi passaggi:
Imposta il formato della cella su " MMMM AAAA". Risultato:
Esempio 2
In linea " Un esempio di come lavorare con le stringhe in Excel" è necessario sostituire tutti gli spazi con il segno "_", basta aggiungere "MS" prima della parola "Excel".
La formula sarà:
=SOSTITUIRE(SOSTITUIRE(A1; RICERCA("excel";A1);0;"MS ");" ";"_")
Se hai modificato un formato esistente, il formato originale rimarrà intatto e il nuovo formato verrà aggiunto all'elenco. Puoi scegliere i formati personalizzati nello stesso modo in cui scegli i formati integrati. Per utilizzare il tuo formato in altre cartelle di lavoro, copia la cella contenente il formato in quella cartella di lavoro. mostra una dozzina di esempi di formati personalizzati. Esempi di formati numerici personalizzati.
Ecco una breve spiegazione per ogni esempio. Questi formati mostrano come puoi ridurre grande numero a uno più piccolo e più leggibile, utilizzando il separatore delle migliaia. Per esprimere un numero maggiore in milioni, è sufficiente aggiungere un secondo separatore di migliaia. Esempio 2: utilizzare questo formato se non si desidera visualizzare zeri iniziali o finali. Esempio 3 Questi sono esempi di formati in quattro parti. La quarta parte visualizza il messaggio "Inserisci un numero" se l'utente inserisce del testo nella cella. Esempio 4 Questo esempio utilizza un segno di centesimo dopo il valore. Ricorda che il carattere asterisco riempie la cella con qualsiasi carattere che lo segue. Esempio 1. . I fogli di lavoro sembrano meno disordinati e più facili da leggere se nascondi zeri non necessari.
Per capire questa formula, dividerlo in tre colonne. Inizia con CERCA, l'ultimo sarà SOSTITUTO.
Tutto. Se avete domande non esitate a chiedere
Excel ha funzioni speciali per trovare, selezionare e sostituire i dati nelle celle. Informazioni su tutte le possibilità di queste funzioni nell'articolo "Trova e sostituisci in Excel".
Qui consideriamo un altro modo, come cambiare i numeri in Excel in lettere, punto in virgola, sostituire testo, parole, valori in Excel, ecc. Per questo useremo la funzione SUBSTITUTE. Questa funzione cambia il vecchio testo in quello nuovo.
Prima opzione.
Come sostituire il testo in una cellaEccellere.
Dobbiamo sostituire una parola (più parole) con un'altra. Abbiamo una proposta del genere in una cella.
Dobbiamo sostituire la parola "neve" con le parole "semole di neve".
Nella cella B9 scriviamo la seguente formula. =SOSTITUTO(A9,"neve","palline di neve")
Con questa formula diciamo: sostituisci tutte le parole "neve" nella cella A9 con le parole "semole di neve".
Fare clic su "OK". È andata così.
La formula può essere impostata utilizzando una funzione. La funzione SOSTITUISCI si trova nella scheda Formule nella sezione Libreria funzioni. Fare clic sul pulsante "Testo" e selezionare la funzione "SOSTITUISCI".
La finestra di dialogo è compilata in questo modo.
Seconda opzione.
Come trasformare i numeri in lettereExcel, lettere in numeri.
Dobbiamo cambiare la lettera "C" nelle celle A11 e A12 con il numero 67. Nella cella B11 scriviamo la seguente formula. =SOSTITUTO(A11,"C","67") Copia la formula per colonna. È andata così.
Terza opzione.
Come cambiare le lettere nelle celleEccellere.
Nella cella B11 abbiamo scritto la seguente formula. =SOSTITUTO(A11,"C","A") Sostituita la lettera "C" con la lettera "A".
Come cambiare i numeriEccellere.
Nella cella B12, scrivi la seguente formula. =SOSTITUTO(A12,"12","67") Vedi celle A12 e B12 - Sostituito codice 12C-PE con codice 67C-PE.
Quarta opzione.
Come cambiare punto in virgola inEccellere.
Dobbiamo sostituire il punto con una virgola nella cella A29 con un numero. Nella cella B29 scriviamo la seguente formula. =SOSTITUTO(A29;".";",") È andata così.
Quinta opzione.
Come modificare una parola specifica in una cellaEccellere.
Abbiamo solo bisogno di cambiare la prima parola nella frase. Quindi, nella cella B9, scrivi la seguente formula.
=SOSTITUTO(A9,"neve","palline di neve",1)
Abbiamo aggiunto "1" alla prima formula - significa sostituire la prima parola "neve". È andata così.Sesta opzione.
Come rimuovere gli spazi inEccellere.
Abbiamo un tavolo del genere. Dobbiamo rimuovere gli spazi nelle celle A14 e A15. Nella cella B14 scriviamo la seguente formula. =SOSTITUTO(A14;" ";"")
Le prime virgolette nella formula sono state scritte con uno spazio, le seconde senza spazio. Copia la formula per colonna. È andata così.
Come rimuovere spazi aggiuntivi in Excel, vedere l'articolo "Come rimuovere gli spazi extra in Excel"
Funzioni simbolo destro e simbolo sinistro
Per nascondere gli zeri nelle celle selezionate, crea un formato personalizzato che utilizzi il seguente formato di sintassi. Formato positivo, formato negativo. Il punto e virgola in più alla fine funge da segnaposto per il formato null. Poiché non esiste una definizione per il valore null, non viene visualizzato nulla.
L'effetto dei formati visti finora dipendeva dal fatto che il contenuto della cella fosse positivo, negativo, nullo o testo. Sebbene ciò sia utile per la maggior parte delle applicazioni, a volte è necessario formattare una cella in base a condizioni diverse. Ad esempio, potresti aver bisogno solo di determinati numeri, o numeri all'interno di un certo intervallo, per prendere un formato particolare. Con questo simbolo si impostano istruzioni condizionali utilizzando gli operatori logici =, = e e i numeri corrispondenti.