MS excel – assegnazione di "categorie" basate sulle parole chiave

Ho file excel con spese (la quantità di denaro speso è in una colonna) e nella colonna successiva ho una breve descrizione che è per lo più fatta di parole multiple. Voglio "semplificare" la descrizione e assegnare una singola parola o due a ciascuna descrizione, che sarebbe in un'altra colonna accanto ad essa. Il problema è che la descrizione non è "unificata", ad esempio posso avere stringhe come "pranzo aziendale", "cena d'affari al ristorante XXX", "caffè con giornalisti" ecc. E vorrei assegnare queste descrizioni "cibo "etichetta. Ci sono anche categorie diverse che seguono un model simile.

La mia idea era quella di creare un altro tavolo (in un foglio diverso) – in una colonna ho parole chiave come "caffè", "pranzo", "cena" e in colonna accanto a loro ho etichette che voglio avere assegnato, che è "cibo ". Ho usato la function vlookup con corrispondenza approssimativa, ma mi restituisce risultati non corretti. Per qualche ragione l'ordine delle parole nell'elenco sembra interessare i risultati e anche se c'è una corrispondenza parziale (esatta in una parola della string), il vlookup lo ignora e restituisce qualcos'altro. Per esempio ho "parcheggio in hotel xxx" e nella tabella ho "parcheggio" – "spese di viaggio", il vlookup restituisce l'etichetta "cibo".

Puoi aiutarmi a risolvere questo problema? (esiste un approccio diverso da suggerire?)

Si desidera la function FIND() e / o SEARCH() . Uso:

FIND( find_text , within_text )
restituisce la posizione iniziale della prima string di text
all'interno della seconda string di text (a partire dalla posizione 1)

Quindi FIND("lunch", "lunch with customer") restituisce 1 e FIND("lunch", "business lunch") restituisce 10. Se la prima string non si trova nel secondo, restituisce un valore #VALUE! valore di errore. SEARCH() è come FIND() tranne il fatto che FIND() è sensibile alla distinzione tra maiuscole e SEARCH() non lo è. Così

FIND("lunch", "Lunch with customer") restituisce #VALUE!
ma
SEARCH("lunch", "Lunch with customer") restituisce 1

Suppongo che si desidera utilizzare SEARCH() , il caso insensato.

Vorresti impostare una matrix come questa:

elenco di parole chiave

Probabilmente è meglio farlo in un foglio separato; chiamiamo la Key-Sheet . Quindi, nella scheda di dati: Se la tua descrizione a forma libera si trova nella colonna A (che inizia nella cella A1 ), inserisci quanto segue nella cella B1 :

 =MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$7,$A1),LEN($A1)+1)), SEARCH('Key-Sheet'!$A$1:$A$7,$A1)) 

e premere Ctrl + Shift + Enter per renderla una "formula di matrix". (Verrà visualizzato nella barra delle formule nelle parentesi graffe.) Spiegazione:

  • SEARCH('Key-Sheet'!$A$1:$A$7,$A1) – per each parola chiave della colonna A del foglio chiave ("caffè", "pranzo", "cena", ecc …), cercare nella descrizione nella row corrente, colonna A , della scheda tecnica (ad esempio, "pranzo aziendale"). Questo creerà un arrays contenente { #VALUE! ; 10 ; #VALUE! ; …} (sette elementi (in questo esempio), uno per parola chiave, la seconda mostra il risultato per "lunch", che è in 'Key-Sheet'!A2 ).
  • IFERROR(…,LEN($A1)+1) – sostituire #VALUE! con 15 , che, essendo LEN("business lunch")+1 , non può essere un valido valore di return da SEARCH() (e che in realtà è superiore a qualsiasi valore valido restituito da SEARCH() ), che è un numero valido. Così ora la nostra matrix è { 15 ; 10 ; 15 ; …}.
  • MIN(…) – estrarre il valore minimo dall'arrays: in questo esempio, 10 . In generale, questo sarà il (primo) return di successo da SEARCH() .
  • =MATCH(…, …) – notare che il secondo parametro a MATCH() è uguale al primo puntatore, sopra. Quindi stiamo cercando 10 nell'arrays { #VALUE! ; 10 ; #VALUE! ; …}. Ciò restituisce la posizione del 10 , che è 2, corrispondente al fatto che A1 sulla scheda di dati ("business lunch") contiene "pranzo", che è nella seconda row del foglio di chiave.

Per get la categoria di spesa, è una semplice questione di indexing nella colonna B della tabella delle chiavi. Impostare la cella C1 a =OFFSET('Key-Sheet'!$B$1,B1-1,0) . (Questo non deve essere una formula a matrix.)

dati di spesa

Nota (come previsto in precedenza) che, se una descrizione delle spese contiene più parole chiave, troverà solo la prima.

Se non si desidera preoccuparsi del valore intermedio, è ansible calcolare solo

 =OFFSET('Key-Sheet'!$B$1,MATCH(MIN(IFERROR(SEARCH('Key-Sheet'!$A$1:$A$6,$A1),LEN($A1)+1)),SEARCH('Key-Sheet'!$A$1:$A$6,$A1))-1,0) 

Questo ha bisogno di essere una formula di matrix.


PS le funzioni FIND() e SEARCH() dispongono di un terzo argomento facoltativo:

SEARCH( find_text , within_text , [ start_num] )

Così

SEARCH("cigar", "Sometimes a cigar is just a cigar.") restituisce 13
ma
SEARCH("cigar", "Sometimes a cigar is just a cigar.", 17) restituisce 29

Non vedo alcun motivo per utilizzarlo.

Come ha detto Tyson, il "close / ca." la partita non è destinata alle parole. Per citare il file di aiuto:

 If range_lookup is either TRUE or is omitted, an exact or approximate match is returned. If an exact match is not found, the next largest value that is less than lookup_value is returned. 

Ciò significa che se si cerca il valore "7" in "1,2,5,8,12", il valore restituito sarebbe "5", il valore più vicino a 7 che non è superiore a 7.

Non esiste un modo semplice per fare quello che vuoi senza una sorta di vasta programmazione e valutazione di singole parole e di analisi grammaticale.

Quello che dovresti fare è addestrarsi ad entrare in qualche tipo di "codice categoria" quando inizialmente inserisci i dati e quindi utilizzare una colonna di memo per "ulteriori dettagli" .. come "01-Food and Drink", "ha preso il boss cena per il suo compleanno ".

Se hai già una grande quantità di dati in cui questo potrebbe essere difficile da fare, puoi eseguire alcuni trucchi per accelerare le cose (anche se dovrai ancora fare molte cose manualmente).

Inizia con l'aggiunta di una colonna che controlla la descrizione per la parola "park" e restituisce 0 se non viene trovata, 1 se trovata .. qualcosa come "= If (Search (" park ", A1)> 1,1,0) (e poi copia automaticamente la formula in giù tutte le righe dei tuoi dati). Quindi, puoi ordinare l'integer tavolo per quella colonna, in modo che i tuoi dati siano suddivisi in due gruppi: descrizioni con "parco" in esse e quelle senza. Aggiungi un'altra colonna per dire, quelli con "cibo" in loro. Quindi, tra "cibo" e "parco", puoi ordinare (utilizzando entrambe le colonne) in quattro gruppi: quelli senza parole, quelli con "cibo", quelli con "parco" e quelli con entrambi.

Facendo questo ripetutamente, puoi rapidamente ordinare gruppi che sono chiaramente una categoria o un altro, contrassegnarli con un codice categoria e ignorarli da allora in avanti mentre effettui ulteriori ricerche di parole, finché tutto non è stato classificato.