Cine a lucrat în Excel și a folosit tabele (Tables) știe cât de folositoare sunt. Și cine a lucrat dar nu a folosit, să pună mâna și să folosească. Extindere / restrîngere automate, referințe structurate, propagarea formulelor sau a formatărilor când adaugi rânduri noi, utilizarea ca sursă pentru grafice, PivotTable sau PowerQuery, filtrări / sortări ușoare și consistente sunt doar câteva dintre avantaje. Singurul dezavantaj este că nu poți crea subtotaluri.
Din păcate aplicația echivalentă Excel din suita LibreOffice, Calc, nu oferă posibilitatea aceasta. Vedem mai departe cât de mult putem „fura” de la Măria-Sa, Microsoft.
În acest articol vom crea manual un tabel, vom vedea care sunt diferențele față de un tabel Excel (ce pierdem), urmând ca într-un articol viitor să automatizăm totul, folosindu-ne de o interfață și cod. Mult cod. Nu vă speriați, veți primi totul mură-n-gură 😀.
Câteva considerente teoretice
În Excel un tabel (Table) este, în primul rând, o combinație de mai multe regiuni de celule, bine definite și care pot fi accesate fiecare independent și cu ele (sau asupra lor) se pot executa diferite operațiuni de citire, scriere, calcule sau formatări.
Presupunând că avem un tabel cu numele Angajati și coloanele ID, Nume, Prenume, Varsta și Sex, aceste regiuni (Ranges) sunt următoarele:
| Regiunea | Explicație | Formulă accesare |
|---|---|---|
| Tabelul | Întregul tabel, cu tot ce ține de el | Angajati |
| Capul de tabel | Primul rând al tabelului. Poate lipsi, dar nu se recomandă. | Angajati[#Headers] |
| Totaluri | Ultimul rând al tabelului, conține rezultatele a diferite calcule pe coloane (sume, medii…). Poate lipsi. | Angajati[#Totals] |
| Datele | Întregul tabel din care scoatem capul de tabel și rândul cu totaluri. | Angajati[#Data] |
| Coloana „Nume” | Întreaga coloana ”Nume” | Angajati[[#All],[Nume]] |
| Datele din „Numele” | Întreaga coloană „Nume”, din care scoatem capul de tabel și rândul cu totaluri. | Angajati[[#Data],[Nume]] |
De exemplu, dacă dorim să aflăm căte femei sunt în firmă, folosim formula:
=COUNTIF(Angajati[[#Data],[Sex]];”F”)
în loc de formula
=COUNTIF(E$2:E$6;”F”)
Așa suntem siguri că, oricât se mărește sau se restrânge coloana pe verticală, rezultatul este consistent.
Cum creăm un tabel în LibreOffice Calc
Așa cum spuneam la început, nu vom putea implementa toate toate funcționalitățile. Vom lucra cu Named Ranges dinamice. Adică se vor actualiza dinamic când adăugăm sau ștergem coloane sau rânduri.
Ce sunt Named Ranges? Le avem de mulți ani și in Excel. Așa cum le spune numele, sunt regiuni de celule dintr-o foaie de calcul cărora le dăm un nume. Astfel va fi mult mai ușor să le folosim în formule. E mai ușor și „omenește” să scriu
=SUM(Profit)
decât
=SUM($C$121:$D$324)
În plus, dacă vreau ca regiunea Profit să se refere la o altă zonă de celule, schimb doar definiția și schimbarea se regăsește în toate rezultatele obținute pe baza Profit.
Pentru început creăm 2 zone, UltimulRand și UltimaColoana. Acestea ne vor returna numerele ultimului rând și a ultimei coloane din tabel. Da, Named Ranges pot returna și valori, nu doar zone de celule.
Presupunem că tabelul este în sheet-ul cu numele Sheet1.
- Mergem în meniul Sheet – Named Ranges and Expressions – Define…
- La Name scriem UltimulRand
- La Range or formula expression scriem formula de mai jos
- La Scope alegem Document (Global)
LOOKUP(2;1/($Sheet1.$A$2:$A$1048576<>””);ROW($Sheet1.$A$2:$A$1048576))
Pentru UltimaColoana procedăm la fel, doar formula se schimbă
LOOKUP(2;1/($Sheet1.$1:$1<>””);COLUMN($Sheet1.$1:$1))
Pentru întregul tabel, la fel ca mai sus, dar îi dăm numele Angajati și la formulă completăm cu:
$Sheet1.$A$1:INDEX($Sheet1.$A:$XFD;UltimulRand;UltimaColoana)
Mai departe, dacă vrem să facem ceva cu datele din coloana Sex, vom crea zona SexDate, cu formula
$Sheet1.$E$2:INDEX($Sheet1.$E:$E;UltimulRand)
Cum ne folosim de ceea ce am făcut
Dacă dorim să aflăm câte femei sunt în firmă, vom folosi formula
=COUNTIF(SexDate;”F”)
O foarte utilă funcționalitate în tabele din Excel sunt coloanele calculate. Adică coloane care au în fiecare celulă date calculate pe baza datelor din alte coloane, aflate pe același rând.
Pentru a obține într-o coloană valoarea din altă coloană, de pe același rând, folosim formula
INDEX(SexDate;ROW()-1)
Sau, ca să fim mai corecți (și să scriem mai mult 😀)
INDEX(SexDate;ROW()-ROW(Angajati))
pentru că acel 1 este de fapt numărul rândului pe care începe tabelul (numărul rândului pe care se află capul de tabel). Vezi și FAQ nr. 4 de ce e bine să folosim a doua variantă.
Ce trebuie să ținem cont. Sfaturi
- Formulele prezentate mai sus țin cont de faptul că tabelul începe în celula A1 și datele încep în celula A2.
- Formulele determină dimensiunea tabelului căutând din A1 în tot restul sheet-ului. Adică 17.179.869.184 de celule. Asta are avantajul că nu mă doare capul când adaug rânduri și coloane. Dezavantajele sunt următoarele:
- Pot avea un singur tabel în sheet
- Chiar dacă nu am folosit funcții volatile, orice scriu / modifc / șterg înafara tabelului determină o recalculare a tuturor formulelor
- Soluția este să rezerv un număr de coloane și rânduri pentru tabel. Adică formulele de definire se ocupă doar de zona rezervată. Astfel mă voi putea folosi de tot spațiul nerezervat. Pentru alte tabele sau orice altceva. Sfatul meu este ca acea zonă rezervată să o formatați cu o bordură exterioară. Astfel veți vedea foarte ușor când ați ajuns la marginile ei și puteți reface rezervarea dacă e nevoie.
- Soluția funcționează chiar dacă există celule necompletate în interiorul tabelului, chiar și în capul de tabel (cu toate că de ce nu ai completa toate celule de aici?). Practic, logica este următoarea:
- Pentru UltimaColoana, formula pleacă de la prima celulă (A1 în cazul nostru) si merge spre dreapta pana la ultima coloană a sheet-ului, reținând ultima celulă completată pe rândul capului de tabel
- Pentru UltimulRand, formula pleacă de la prima celulă a datelor tabelului (A2 în cazul nostru) și caută până la ultima celulă a sheet-ului în jos, reținând ultima celulă completată de pe prima coloană a tabelului
- Apoi, întregul tabel Angajati este creat plecând de la prima celulă A1, căutând în tot sheet-ul până întâlnește UltimaColoana și UltimulRand
- Pentru ca totul să funcționeze corect, trebuie ca:
- pentru UltimaColoana – în capul de tabel să avem completate toate celule (dacă doar adaug o coloană în dreapta, dar nu completez capul de tabel, nu va lua în considerare). Bine, treaba funcționează și dacă doar ultima celulă din capul de tabel e completată, dar ăla nu mai e cap de tabel!
- pentru UltimulRand – pe prima coloană să avem completat măcar ultima celulă
Să vedem ce fac toate aceste formule, pentru a știi ce să modificăm.
UltimulRand
LOOKUP(2;1/($Sheet1.$A$2:$A$1048576<>””);ROW($Sheet1.$A$2:$A$1048576))
$A$2 – celula de unde încep datele. Aici modific dacă tabelul meu începe în altă celulă decât $A$1
$A$1048576 – ultima celulă din sheet, pe coloana A. Aici modific dacă tabelul meu începe în altă coloană decât A sau dacă vreau să rezerv mai puține rânduri decăt până la rândul 1.048.576. Dacă vreau să rezerv 10 rânduri, formula este
LOOKUP(2;1/($Sheet1.$A$2:$A$10<>””);ROW($Sheet1.$A$2:$A$10))
UltimaColoana
LOOKUP(2;1/($Sheet1.$1:$1<>””);COLUMN($Sheet1.$1:$1))
$1:$1 – rândul cu capul de tabel. Aici modific din 1 în numărul rândului pe care începe tabelul. Atenție la formula pentru obținerea valorilor din alte coloane.
Dacă vreau să rezerv mai puține coloane decât are sheet-ul, sa zicem 10 coloane, formula se transformă în cea de jos, pentru că J este coloana a 10-a:
LOOKUP(2;1/($Sheet1.$A$1:$J$1<>””);COLUMN($Sheet1.$A$1:$J$1))
Tabelul Angajati
$Sheet1.$A$1:INDEX($Sheet1.$A:$XFD;UltimulRand;UltimaColoana)
$A$1 – celula de unde începe tabelul
$A:$XFD – toate coloanele din sheet
Dacă am rezervat 10 rânduri și 10 coloane, formula devine
$Sheet1.$A$1:INDEX($Sheet1.$A$1:$J$10;UltimulRand;UltimaColoana)
Datele din coloana Sex
$Sheet1.$E$2:INDEX($Sheet1.$E:$E;UltimulRand)
$E$2 – celula în care încep datele coloanei Sex
Dacă am rezervat 10 rânduri, formula devine
$Sheet1.$E$2:INDEX($Sheet1.$E$1:$E$10;UltimulRand)
Tabele în LibreOffice Calc – FAQ
1. Ce se întâmplă dacă adaug o coloană nouă în tabel?
Dacă adăugați o coloană în dreapta ultimei coloane pentru care ați creat Named Range, nu se întâmplă nimic, totul funcționează normal. Bineînțeles, dacă vă încadrați în zona rezervată
Dacă însă o adăugați în stânga, trebuie sa refaceți definiția tuturor Named Ranges a coloanelor de după cea inserată.
2. Ce se întâmplă dacă adaug rânduri deasupra tabelului?
Dacă se schimbă coordonatele celulei de unde începe tabelul va trebui să modificați toate Named Ranges, eventual să analizați dacă nu depășiți zona rezervată
3. Ce se întâmplă dacă adaug o coloană nouă în stânga tabelului?
Dacă se schimbă coordonatele celulei de unde începe tabelul va trebui să modificați toate Named Ranges, eventual să analizați dacă nu depășiți zona rezervată
4. Coordonatele celulei de început s-au modificat, am refăcut toate Named Ranges, dar formulele prin care obțin date din alte coloane nu mai funcționează
Aici pot fi două probleme:
- În definiția Named Range toate coordonatele trebuie date cu $ atât la coloană, cât și la rând. $A1 nu este același lucru cu $A$1. Corect este $A$1
- În formula din tabel poate ați folosit inițial ROW()-1 (tabelul începea pe rândul 1) și acum tabelul începe pe rândul 3. Ori modificați toate formulele din tabel ori folosiți de la început ROW()-ROW(nume_tabel), unde în loc de nume_tabel puneți numele real. Vezi aici
5. Unde găsesc Named Ranges?
În meniul Sheet - Named Ranges and Expressions - Manage... sau cu shortcut Ctrl+F3. Aici găsiți toate Named Ranges definite, fie pentru tabele, fie pentru altele.
Atenție! Nu le veți găsi în lista din stânga Formula Bar. Acolo apar cele definite direct ca range de celule, ale noastre sunt definite prin formule.
6. Am foarte multe Named Ranges, nu doar cele pentru tabele. Și la fiecare tabel am multe altele pentru coloane. Cum fac să le descopăr mai repede în listă?
Sfatul meu este să folosiți următoarea regulă la denumirea pentru tot ce ține de un tabel. Să zicem că pornim de la tabelul Angajati:
- Tabelul îl denumim tblAngajati
- Ultima coloana si ultimul rand le denumim tblAngajati_UC si tblAngajati_UR
- Pentru orice coloana folosim tblAngajati_NumeColoana, de exemplu tblAngajati_Nume sau tblAngajati_Sex
Folosind această regulă, veți vedea clar care sunt tabelele și fiecare coloană / UC / UL pentru tabelul respectiv. Bineînțeles că dacă aveți două coloane Data, range-urile vor trebui denumite tblAngajati_Data1 și tblAngajati_Data2.
7. Care e treaba cu Scope la definirea unui Named Range?
Scope poate avea două valori:
- Document (Global) - este vizibil în orice sheet. Adică îl pot folosi în formule în orice sheet din fișier. De aceea am ales în soluția noastră acest Scope
- Sheet (în listă apare numele sheet-ului) - este vizibil doar în sheet-ul unde a fost creat. Deci îl voi putea folosi în formule doar în acel sheet
8. Pot folosi același nume la două tabele?
LibreOffice Calc nu permite ca două Named Ranges având același Scope să aibă același nume. Dar permite în cazul în care Scope este diferit.
Ce se întâmplă dacă sunt pe un sheet unde am un Named Range cu Scope acel sheet (tblAngajati), dar există și un altul, undeva în fișier, cu același nume și Scope Global? Prioritate are cel din sheet, cu Scope Sheet. Dacă voi scrie o formulă care se referă la tblAngajati in acest sheet, va citi datele din tblAngajati de aici.
Sfatul este să nu utilizați același nume, chiar dacă Calc vă dă voie.
9. Ce se întâmplă dacă duplic un sheet cu tabele?
Se vor duplica și Named Ranges, cu aceleași nume și definiții, dar li se va acorda automat Scope la noul sheet. Asta înseamnă că orice formulă externă tabelului și care era în acel sheet, dacă se referea la tabel, va funcționa corect, dar va lua datele din tabelul nou, duplicat cu sheet-ul, nu din cel inițial. Pentru că, cum spuneam mai sus, acesta are prioritate.
10. Am un fișier central cu tabele, dar vreau să accesez datele într-un alt fișier. Se poate?
Adică simulați o bază de date centrală. Foarte frumos, dar nu aici. LibreOffice Calc nu oferă posibilitatea să accesăm Named Ranges din alte fișiere.
11. Ce se întâmplă dacă modific numele sheet-ului în care am tabelul ;i toate Named Ranges?
Dacă modificați numele sheet-ului, această modificare se va regăsi automat în toate formulele.
12. Am modificat numele sheet-ului din ”Sheet1” în ”Tabelul meu” înainte de a crea Named Ranges. După ce completez $Tabelul meu... apar erori
Când numele sheet-ului are spații, acesta se scrie între ghilimele simple $'Tabelul meu'
Avantaje și dezavantaje ale soluției
Pro
- Tabelul se extinde / restrânge automat
- Nu folosește în formule nici o funcție volatilă. Funcțiile volatile sunt cele care se recalculează automat la orice modificare, oriunde în fișier, chiar dacă modific o celulă care nu depinde de ele. La fișiere mari e dezastru
- Pot folosi coloane calculate
Contra
- Nu avem rând de totaluri
- Formulele din coloane calculate nu se autocopiază pe coloană
- Nu se păstrează formatările la adăugarea unui rând nou
- Nu am la dispoziție stiluri ca să formatez întreg tabelul
- Dacă mut tabelul în alt loc pe foaie sau pe altă foaie se pierd toate funcționalitățile, trebuind refăcute toate Named Ranges
Ce urmează?
Lucruri frumoase, ce să urmeze?
Până atunci,
Happy Coding!
Poate te interesează și:
Inserare automată cod QR în Microsoft Office, CorelDraw și LibreOffice
Cum inserăm automat coduri QR în aplicațiile Microsoft Office, CorelDRAW sau LibreOffice cu ajutorul unui macro VBA / Basic.
Surse: imagine reprezentativă realizată cu AI ChatGPT
