Formula Excel - verifcare și autocompletare

Stimați colegi,
Am atașat o imagine pentru a explica mai bine situația.
Vă rog să mă ajutați să rezolv următoare cerință folosind Excel:

Tabelul A conține o așa zisă evidență. În coloana U1 sunt introduse valori unice, irepetabile. În coloanele U2-U4 sunt valori care se pot repeta.

Cerința:
Într-un alt fișier Excel  se creează tabelul B. La introducerea unei valori în coloana U1 (care se găsește în coloana U1 din Tabelul A) să se completeze automat Coloanele U2-U4 cu valorile corespondente din Tabelul A. Dacă valoare introdusă în coloana U1 din Tabelul B nu se regăsește în U1 din Tabelul A să se afișeze un mesaj.
Mulțumesc anticipat.

vlookup
iferror

Edited by sorin147, 27 May 2018 - 20:27.

Mă apuc de citit. Mulțumesc.

VBA gasesti ceva tutoriale de VBA care sa iti faca asta. Merge si cu formule dar trebuie sa aplici formula in fiecare celula.
In tabelul B in coloana U1 poti folosi un VLOOKUP sa vezi daca in tabelul A pe coloana aferenta (U1) ai valoarea din B de la U1. Iar in restul IF si alte mizerii.

Solutia simpla tot VBA ramane.

INDEX(coloana_din care_iei_valorile; MATCH ( coloana_in_care_cauti_cheia_unica ; celula_ce_contine_cheia_unica ;0) )

Adica in cazul tau, (nu vad numerele coloanei sa zic exact ce tre sa scrii)

INDEX( coloana_U2 ; MATCH ( coloana_U2 ; celula_cu_cheia ;0) )

Poti face si cu functii agregat, (formule CSE), cu mult mai elegant, dar mai intai trebuie s-o intelegi pe asta.

Iata un exemplu
 e049fad52ba89fd9aff87241fd1194b5.png   10.2K   26 downloads

Edited by maccip, 27 May 2018 - 20:32.

Vezi PM pentru o solutie de moment pana aprofundezi problema.

Mă duc să-mi fac o cafea. Soluția lui sorin147 cu vlookup a funcționat. Acum merg și pe celelalte soluții, în ordinea postării. Mulțumesc.

Edited by badmania98, 27 May 2018 - 20:57.

Eu zic sa iei helpul excelului, functie cu functie sa vezi ce face fiecare. Asa-ti faci o idee cam ce poti face cu excelul si ce nu poti face cu el si ai nevoie de VBA.
Nu sunt multe functii, majoritatea le vei citi si le vei uita, insa iti vei face o idee cum sa rezolvi o problema pe viitor.
Cu excelul poti face multe lucruri, avantajul e ca e foarte usor, dureaza foarte putin sa scrii o formula si de multe ori rezolvi probleme destul de complexe.
Pacat ca nu lucreaza cu expresii SQL inline sau cu regex, decat cu addonuri sau cu VBAuri facute de tine.

Google sheets are SQL (QUERY()  si regex (REGEXEXTRACT(), REGEXMATCH(), REGEXREPLACE() )
Cu functiile astea devine extrem de puternic, nu inteleg de ce microsoft nu le-a implementat.

Incearca Google Sheets sa vezi ce fain este. Excelul are in plus charturi mai destepte si poate lucra cu mai multe date, insa lipsa SQL si REGEX il face mai slab in multe situatii practice.
REGEX e o chestie extrem de faina si puternica in recunoasterea patternurilor de text (regular expressions) Dupa ce o inveti, nu te mai poti desparti de acest feature, o recomand oricui, se invata usor(foarte usor, e o singura pagina de wikipedia basically) si este foarte folositoare.

Edited by maccip, 27 May 2018 - 21:14.

Am încercat varianta propusă de maccip. Funcționează dar trebuie editată formula în fiecare celulă de pe primul rând. Eu am 12 coloane și consider că e cam mult. Odată editate se poate trage în jos pentru celelalte rânduri.

Varianta trimisă de razvan_u se mulează pe toate cerințele emise, cu formula =IF(I4>0;IFERROR(VLOOKUP(I4;C:F;2;0);"lipsa");"").

Soluția postată de către Kagaroth bănuiesc că necesită mai mult studiu.

Mulțumesc încă o dată.

Edited by badmania98, 27 May 2018 - 21:55.

Eu nu stiu asa bine sa lucrez cu Excel, fug de calcul tabelar in orice forma ar fi el M$, Libre, Open, Google sau altceva. Doar ca in ultimile 3 luni am fost nevoit sa folosesc Excelul pentru niste magarii. Nu a fost fun sa am un fisier xlsx de 12 MiB cu N formule si mii de inregistrari. De asta am si mentionat de VBA ca din putinul care il stiu am zis ca asta e alegerea usoara. Sa nu ajungi ca mine sa ai o formula intinsa pe 3 - 4 randuri doar intr-o celula.

Edited by MembruAnonim, 27 May 2018 - 22:30.

Daca nu vrei sa se modifice referinta, tre sa pui $ inaintea cifrei sau literei daca vrei sa "tragi in jos" sau "tragi la dreapta"
Sau apesi F4 succesiv in timp ce esti cu cursorul pe una din referinte si alegi ce varianta vrei.
Asta-i la toate formulele in excel, nu numai in cazul asta.

Modifici formula doar intr-o singura celula si apoi "tragi in jos" sau " tragi la dreapta". E o chestie fundamentala.

Dublu click in coltul din dreapta jos a celulei in care ai formula. Autocompleteaza celulele de pe aceiasi coloana. Nu stiu care e algoritmul dupa care lucreaza dar se opreste intr-un punct nu sare pana la celula 1Milion.

@maccip: Corect, am rezolvat și prin varianta ta.

MembruAnonim, on 27 mai 2018 - 22:29, said:

In chestii ingineresti e eficient calculul tabelar pentru ca e rapid si simplu.

Dar l-am folosit mult in planificarea atacurilor si in general in planificarea unor operatiuni strategice intr-un joc de strategie, travian se cheama.

De exemplu, sheetul urmator are in spate 2 baze de date,
- una cu atacatori(adica aliati) in care sunt trecute chestii.. caabilitati, coordonate etc..
- una cu posibilele tinte pe care le atacam in mod frecvent, ale adversarilor.
Si ajuta la planificarea (pe mai multe pagini pentru usurinta) a unor atacuri sincronizate care sa induca adversarul in eroare(atacuri FAKE) si sa creeze panica. si damage.
Foile ajutau sa se planifice corect operatiunea (celulele mai albe la culoare sunt editabile, celelalte trebuiau sa fie blocate pentru planificatori)
Pentru ca apoi, sa se genereze cate un mesaj personalizat catre fiecare jucator cu ce are de facut, la ce ore trebuie sa lanseze atacurile, si catre cine.

Aici am o copie. desi toate campurile sunt editabile, doar alea mai deschise la culoare ar trebui sa fie.
https://docs.google....#gid=1147451392
E destul de complicat planificatorul si asta nu e singurul, am si mai complicate, dar si mai greu de inteles pentru ca nu-s user friendly.
Dar cam asa jucam noi jocul ala. hahahaha
Cu ocazia asta mai invatam si noi excel(de fapt google sheets mai mult)
https://gyazo.com/64...5a65c4b51517a00

BTW, foaia e editabila daca vreti sa va jucati pe-acolo.
Adica am o copie a ei, in cazul in care voi mai juca vreodata jocul ala.

maccip, on 27 mai 2018 - 22:49, said:

Iisuse inca imi fac ochii in cap ca la pacanele de la foia aia tabelara. Tot respectul ca te descurci cu el. Nu am fost nevoit sa folosesc des excel asa ca in afara de =A1+B1 si de tras de coltul celulei sa copiez valoarea sau sa o incrementez sau sa aplic formula nu stiam mai nimic . Nu ca acum as sti ca daca ma pune cineva sa generez un grafic din niste date ma uit ca boul la excel si el la mine si nu iese nimic. Prefer sa  ma complic un pic, poate sa ma complic, si sa parsez un xlsx folosind un script in python sau perl depinde pe care il invat mai repede.

Nu fac foi asa de complicate decat pentru jocuri.
Acolo era necesar deoarece o foloseam cativa insi si fiecare trebuia sa stie ce are de facut.
Si trebuia sa fie utilizabila, sa fie rapida, altfel n-o folosea nici dracu si eram nevoit sa fac tot eu planul de atac, nu aveam chef de asa ceva.

Am jucat vreo 3-4 ani jocul ala. Si sheetsurile alea ajutau enorm la strategia de atac, sau la cea de defence.
Mai erau si altii care faceau sheetsuri, tot asa, tematice, pe un anume aspect al jocului.
Usurau munca si scurtau timpul de planificare a unei actiuni de atac/defence coerente.

Deci jocuri frate. De la jocuri inveti. Ca pentru serviciu, n-as face asa ceva nici sa ma omori. Fac si acolo, dar chestii simple. Maxim un Lookup.
hahahah


Dar REGEX folosesc des, mai ales cand am de cautat in date sub un anume format, sa le pun sub  alt format spre a le trimite intr-un device de exemplu.
Si nu intotdeauna deschid Visual Studio pentru asta. De cele mai multe ori reusesc sa fac in google sheets tot ce-mi trebuie, macar o chestie semiautomata, sau automata dar asistata de utilizator.

Am invatat-o si pe nevasta-mea. Se mira aia pe la ea pe la serviciu de ce stie sa faca in excel, inclusiv ITstii. Desi n-are nicio treaba cu computerele in general, e grefiera, a invatat niste chestii sa-si automatizeze munca acolo. Are tot felul de balarii care merg automatizate. Si in primul rand e destul de greu sa gresesti daca faci corect "conditional formattings"urile pentru ca-ti arata cu rosu tot ce nu-i OK.
REGEX si SQL in Google Sheets. <- simplu de invatat si foarte necesar in multe chestii.
Se invata 80% diin full-power in max o saptamana de catre cineva care abia stie sa editeze ceva simplu in Word, asa cum era nevasta-mea acum juma de an.

REGEX e misto, nu aveam idee ca Google Docs stie de el. SQL la fel. Deja ma gandesc serios ca as putea renunta la LibreOffice pentru Google Docs.
In rest ce sa zic, cine sta pe excel si il butoneaza posibil sa faca lucruri interesante cu el. Mie mi sa parut un pic contraintuitiv dar dupa cum am spus mai sus prefer sa ma complic lucrurile mai ales in cazurile in care "complicat" = ceva ce imi place sau la care ma descurc. Bine pe alta parte nici nu am avut nevoie sa lucrez cu suita de birotica pana am inceput munca la vechiul angajator in februarie.