Excel - impartire coloane si inlocuire header coloana cu valori

Salut,

Am incercat sa fac titlul cat mai concludent, dar nu cred ca am reusit. Problema este in felul urmator :

Acum ceva vreme am facut rost de un fisier in Excel cu niste candidati. Fisierul contine nume, prenume, data cand s-a inregistrat pe site, profesie, limbi cunoscute, etc.

  [ https://image.prntscr.com/image/LLIeyb3rTDCWv3FyKh1U7g.png - Pentru incarcare in pagina (embed) Click aici ]

Ce ma intereseaza pe mine sunt limbile stiute, care, dupa cum se observa, nu sunt intr-un format tocmai usor de parcurs. M-ar interesa sa pot sa le modific in asa fel incat sa imi fie usor sa le caut/gasesc. Manual nu pot face asta, deoarece sunt peste 3000 de linii, dar stiu ca s-ar putea automatiza din Power Query, insa nu stiu cum.

Ma gandeam sa dispun datele in felul urmator :

[ https://image.prntscr.com/image/ih9t-8yGTOiJW7gOv8KIsw.png - Pentru incarcare in pagina (embed) Click aici ]

Ce parere aveti? Cam ce as putea sa fac? Momentan am reusit sa le "impart" pe coloane in felul urmator :

[ https://image.prntscr.com/image/0V7Fd65mQWWqS9_ryvvuKw.png - Pentru incarcare in pagina (embed) Click aici ]

Stiu ca am nevoie sa creez cate o coloana pentru fiecare limba, iar apoi coloana respectiva sa o populez cu nivelul limbii respective, iar in cazul in care nu stie limba, las gol celula respectiva.

Astept cateva solutii de la cei mai avizati .

Multumesc frumos !
[ http://prntscr.com/jv7pf2 - Pentru incarcare in pagina (embed) Click aici ]

mother tongue?

Observ ca toate limbile fiecarui candidat (sic!) sunt trecute in aceeasi celula. Ca sa le cauti usor, poti introduce filtru pe coloane, si sa filtrezi cu text, de fiecare data cu ce text ai nevoie, pana apuci sa le spargi/exporti in celule diferite.

-mediu in excel, dar lucrez zilnic similar cu ce ai tu nevoie acolo.


LE: nevermind, in prostia mea, m-am uitat doar la prima poza si mi-am etalat necunostintele.

Edited by dimripper, 15 June 2018 - 10:09.

spe_ripper, on 15 iunie 2018 - 10:00, said:

Sa spunem ca este cunoasterea limbii respective la nivel nativ.

Pai si cate limbi diferite sunt in acest excel? Nu merge facuta o filtrare si completare manuala? Asta in caz ca nu sunt multe.
Poti scoate un raport cu pivot table sa vezi ce rezulta.

Edited by shadow_x3x, 15 June 2018 - 10:24.

presupunand ca in celula A2 ai textul din poza initiala si ca in celula B1 ai limba dorita (ex: Russian), poti pune formula asta in celula B2 si ar trebui sa-ti afiseze nivelul limbii respective

=SUBSTITUTE(IF(ISNUMBER(SEARCH("|"; IF(ISNUMBER(SEARCH(B1;A2)); RIGHT(A2;LEN(A2) - SEARCH(B1;A2)+1); ""))); LEFT(IF(ISNUMBER(SEARCH(B1;A2)); RIGHT(A2;LEN(A2) - SEARCH(B1;A2)+1); ""); SEARCH("|";IF(ISNUMBER(SEARCH(B1;A2)); RIGHT(A2;LEN(A2) - SEARCH(B1;A2)+1); ""))-1); IF(ISNUMBER(SEARCH(B1;A2)); RIGHT(A2;LEN(A2) - SEARCH(B1;A2)+1); "")); B1;"")

C1,D1, E1... - Limba
B2 - Conținutul celulei analizate (ex.: English C2|French C1|Swedish Mother tonque|Norwegian B2|Danish B2)
În C2 introduci formula de mai jos:

=IF(ISERROR(SEARCH(C$1,$B2)),"",IF(MID($B2,SEARCH(C$1,$B2)+LEN(C$1)+1,6)="mother","M",MID($B2,SEARCH(C$1,$B2)+LEN(C$1)+1,2)))

1. Pentru a face asta automat, in primul rand tre sa desparti limba de nivel pe coloane separate.
Pentru fiecare candidat tre sa ai cate o linie cu numar dublu de coloane in care sa scrie pe fiecare coloana ... French - B2 - Danish - A1 - Romanian - C3... etc
Adica pe alea impare ai limba, pe alea pare ai nivelul...

2. Intr-un sheet separat, faci o linie header cu fiecare limba.
Pe acest sheet, pe fiecare linie corespunzatoare unui candidat, pe A2 sa zicem, bagi o formula cu ceva de genul

=INDIRECT(TEXT(ROW())&TEXT(MATCH(Sheet'A$1;Sheet'A2;0)+1))

Adica, ideea de baza e sa pui in acea coloana valoarea nivelululi, functie de pozitia lui in tabelul initial, functie de continului liniei header(acolo e limba).


Pentru etapa 1, poti folosi notepad++ pentru a insera un tab, utilizand regular expressions. in excel e mai greu.
Copii tot tabelul in Notepad++ (copy/paste)
in notepad++ dai Find/replace la textul (fara ghilimele, am pus ghilimele ca e un spatiu acolo pe prima pozitie)

" +([A-Z][[0-9])"

la textul (fara ghilimele, am pus ghilimele ca e un spatiu acolo pe prima pozitie)

"\t\1"

Apoi dai copy/paste la loc in Excel.
O sa ai numar dublu de coloane. Pe alea impare va fi limba, pe alea pare va fi nivelul.


Formula e pusa de mine aproximativ, ca idee.
Daca nu te descurci, te ajut eu cu formula exacta.
Insa tre sa -mi trimiti documentul
In felul asta ti-l fac eu, nu mai ai nevoie sa faci nimic.


LE: E mai buna si mai eleganta solutia colegului @X41Xu1, n-am vazut ca mai ai si mother tongue acolo.

Edited by maccip, 15 June 2018 - 10:48.

Kain_12, on 15 iunie 2018 - 10:15, said:

Native language. Mother tongue este o expresie ce poate provoca doar rasete si amuzament ca ia conotatii...

Edited by andreic, 15 June 2018 - 11:22.

@initiator, cred ca cel mai simplu e sa spargi celula cu limbile vorbite prin Text to Column. O sa-ti rezute in dreptul fiecarui candidat cate o coloana pentru fiecare limba cunoscuta.

Limba din gura este "tongue", iar "limba vorbita/ scrisa" este "language".
Expresia "mother tongue" in loc de "first language" sau "native language" este utilizata pur informal sau de catre strainii care fac aceeasi confuzie.
Sensul de "limba vorbita" (nu si scrisa) atribuit lui "tongue" este, deci, un sens rar utilizat (se utilizeaza, mai curand, metaforic).

Edited by _-_, 15 June 2018 - 11:28.

andreic, on 15 iunie 2018 - 11:02, said:

In engleza chiar asa ii spune limbii materne, mother tongue. Nu o sa rada decat prim-ministrul si aia care au invatat engleza la CartoonNetwork.

Si acum va rog sa lasati off-topicul!
Multumesc.

un_dac, on 15 iunie 2018 - 11:15, said:

varianta asta e mai complicat de utilizat decat cea in care textul e parsat cu formule. o fi usor de implementat (parca are Excel-ul o optiune de genul), dar dupa aceea o sa-i fie foarte greu sa sorteze datele.

Edited by _Smiley_, 15 June 2018 - 11:24.