Bună ziua tuturor fanilor Excel. Am pentru voi o provocare, spun eu interesantă, dar probabil și utilă pentru mulți.
Mulțumesc tuturor celor care au participat la rezolvarea problemei sau sunt interesați de ea. Sper să fie utile. Soluțiile primite sunt prezentate în partea de jos a articolului.
În modelul de date din imaginea de mai jos, se cere ca Task-urile (sau alte tipuri de înregistrări de nivel 1, sau Heading 1 pentru cei familiarizați cu aplicațiile Word) trebuie să fie numerotate automat consecutiv. Condiția ca numele din coloana B să înceapă cu Task este obligatorie de respectat în date. Sau oricare alt identificator pentru înregistrările de nivel 1.
În celula A2 este singura care are scris pur și simplul nr. 1. Puteam face și dinamic acel număr cu funcția ROW()-1 în cazul meu. Restul numerelor sunt prin formulă.
Tot ceea ce nu este introdus ca Task (cele de nivel 2 sau Heading 2) trebuie să aibă numere în mod dinamic cu 1.1, 1.2 în funcție de numărul lor. În soluția mea, acel 1.1 nu este chiar un număr.
Pentru cei care vor să participe la această provocare, vă rog să trimiteți soluția prin e-mail la adresa: valy.greavu@outlook.com până la data de 22.05.2020 ora 14:00. Dacă sunt studenți de ai mei de la Master AA care rezolvă problema în termenul specificat le echivalez proba practică de laborator, de săptămâna viitoare cu 10 (primul care trimite cu soluția).
Aștept cu interes! :)
Update ora 22.05.2020 H16:00
Soluția mea
Chiar dacă pare o problemă simplă eu m-am complicat destul de mult în formulă.
Formula mea din celula A3 este:
=IF(LEFT(B3;4)=”Task”;MAX($A$2:A2)+1;MAX($A$2:A2)&”.”&(COUNTA(INDIRECT(CONCATENATE(ADDRESS(MATCH(MAX($A$2:A2);$A$2:A2;0)+1;1);”:”;ADDRESS(ROW()-1;1;4))))))
În această formulă compar dacă începutul textului din coloana B este cu Task, dacă da, adun valoarea 1 la maximul tuturor coloanelor de mai sus.
Dacă nu este task, scrie orice altceva fac concatenare cu & cu numărul maxim de mai sus, semnul „.” și rezultatul numărării (COUNTA) a celulelor de pe blocul dinamic de mai sus, format din adresa celulei in care se află valoarea maximă de până acum ADDRESS(MATCH(MAX($A$2:A2);$A$2:A2;0)+1;1) cu adresa celulei de mai sus de poziția curentă (ADDRESS(ROW()-1). Ca să pot face COUNTA() folosesc funcția INDIRECT() pentru a compune dinamic blocul de celule de căutare.
Avantajul este că funcționează și la mai mult de 10 task-uri și mai mult de 10 subtask-uri.
Dezavantajul este la sortare unde apare eroare dacă faci sortare după coloana de numere curente, sau coloana de activități.
Soluția este funcțională și în Foi de calcul Google, cu mențiunea că având un format regional diferit a trebuit să mai adaug un punct la finalul formulei ca să nu apară rezultatul de tip dată calendaristică.
Rezultatul formulei din Google.
Soluție Aurora
Mă refer la un prenume nu la un cod de soluție. :)
Aurora o fostă studentă de pe la începutul mileniului acesta, mi-a trimis prima soluție.
Formula ei din A3 este:
=IF(LEFT(B3;3)=”Sub”;IF(ISERROR(FIND(„.”;A2));A2&”.1″;LEFT(A2;FIND(„.”;A2))&(VALUE(RIGHT(A2;LEN(A2)-FIND(„.”;A2)))+1));IF(ISERROR(FIND(„.”;A2));A2+1;VALUE(LEFT(A2;FIND(„.”;A2)-1))+1))
Soluția propune folosirea repetitivă a funcțiilor FIND(), LEFT() și RIGHT() cu adunări de numere consecutive. Testat 100% funcțional la valori de peste 10 task-uri și subtask-uri.
Dezavantajul modelului este că dacă un subtask nu începe cu valoarea Sub, atunci nu mai merge formula.
Soluție Adrian B
Soluția lui Adrian folosește o paletă mai variată de funcții, dar rezultatul este identic cu soluțiile de mai sus.
=IF(B3<>””;IF(LEFT($B3;4)=”Task”;MAX($A$2:$A2)+1;IF(NOT(ISERROR(FIND(„.”;A2)));MID(A2;1;FIND(„.”;A2)-1)&”.”&MID(A2;FIND(„.”;A2)+1;LEN(A2)-FIND(„.”;A2))+1;A2&”.1″));””)
Formula este testată pentru mai mult de 10 task-uri și subtasuri. De asemenea, poate funcționa și pentru subtask-uri care nu au aceasta etichetă.
Soluție Alexandru N
Alexandru a trimis o soluție la fel de funcțională ca și celelalte doar că folosește altă abordare și altă combinație de formule. Îmi place la formula lui Alexandru faptul că are o complexitate mai redusă și poate fi mai ușor de înțeles și replicat. Asta dacă reușiți să vă „adaptați” la SUMPRODUCT().
=(IF(LEFT(B3;4)=”Task”;SUMPRODUCT((LEFT($B$2:B3;4)=”Task”)*1);SUMPRODUCT((LEFT($B$2:B3;4)=”Task”)*1)&”.”&IFERROR(RIGHT(A2;LEN(A2)-FIND(„.”;A2;1));0)+1))
Formula testată și funcțională.
Soluție Ciprian S
Ciprian, un vechi prieten al acestui gen de probleme și care a venit permanent cu soluții surprinzătoare, a trimis o soluție foarte interesantă ca primă versiune:
=IF(LEFT(B2;4)=”Task”; FLOOR.MATH(SUM(A1))+1; SUM(A1)+0,1)
Formula se scrie în B2 și funcționează pentru versiunile peste 10 task-uri dar cu maxim 9 task-uri.
Formula este testată și funcționează și în Google Sheets.
Pentru versiunea cu peste 10 subtaskuri Ciprian a folosit o variantă asemănătoare cu cele de mai sus:
Formula din A2:
=IF(LEFT(B2;4)=”task”;TEXT(IFERROR(VALUE(LEFT(A1;(FIND(„.”;A1)-1)))+1;IFERROR(VALUE(A1);0)+1);”0″);IFERROR(LEFT(A1;(FIND(„.”;A1)));A1&”.”)&IFERROR(MID(A1;(FIND(„.”;A1)+1);100);0)+1)
Este o abordare mult mai corectă decât cea prezentată în soluția mea, pentru că începe cu calculul fix din prima celulă de date (A2) și nu doar din celula A3.
O soluție cu VBA
Pentru cei care mă cunosc știu că nu sunt adeptul VBA. Totuși soluția este foarte interesantă și merită exploatată.
În principiu se creează o funcție în VBA cu Range.IndentLevel. Mai multe detalii puteți consulta aici: https://professor-excel.com/how-to-return-the-indentation-of-a-cell-in-excel/
Frumusețea acestei probleme este diversitatea soluțiilor și combinațiilor de formule plus ingeniozitatea oamenilor în a le combina în vederea obținerii rezultatelor asemănătoare. Mulțumesc frumos și ținem legătura! :)
Sper să fie util cuiva.
Valy Greavu - Live Blog 