XLOOKUP neranda kliento, kuris aiškiai yra sąraše. Suvestinė rodo tą pačią prekę dviem eilutėmis. Filtras praleidžia pusę įrašų. Dažniausiai kalta ne formulė, o duomenys: jie atrodo tvarkingai žmogui, bet ne Excel.
Šiame straipsnyje surašiau, ką patikrinti ir pataisyti prieš skaičiuojant, ta tvarka, kuria pats tai darau.
1. Viena antraštės eilutė, be tuščių eilučių
Excel geriausiai dirba su lentele, kurioje pirma eilutė yra stulpelių pavadinimai, o po jos eina tik duomenys. Trukdo:
- dvi ar trys antraštės eilutės, kai viršuje sujungtas „2026 m. pardavimai“, o po juo mėnesiai;
- tuščios eilutės tarp grupių, kurias Excel laiko lentelės pabaiga;
- tarpinės sumos tarp duomenų eilučių, kurios vėliau suskaičiuojamos dar kartą.
Antraštę palikite vieną, tarpines sumas ištrinkite, o sumas skaičiuokite atskirai, pavyzdžiui, suvestine lentele.
2. Sujungti langeliai ir tušti langeliai po jais
Sujungtas langelis „Vilnius“ per penkias eilutes atrodo aiškiai, bet duomenų yra tik pirmoje eilutėje. Kitos keturios tuščios, todėl filtras ir SUMIFS jų nepriskiria Vilniui.
- Pažymėkite stulpelį ir paspauskite Merge & Center, kad langeliai būtų atskirti.
- Neatžymėdami paspauskite F5 → Special → Blanks. Bus pažymėti tik tušti langeliai.
- Įrašykite
=ir paspauskite rodyklę aukštyn, kad formulė rodytų į langelį virš jo. - Paspauskite Ctrl+Enter. Formulė įrašoma į visus pažymėtus langelius.
- Nukopijuokite stulpelį ir įklijuokite kaip reikšmes.
3. Pertekliniai ir nematomi tarpai
„UAB Medis“ ir „UAB Medis “ su tarpu gale Excel yra du skirtingi klientai. TRIM pašalina tarpus pradžioje, gale ir dvigubus tarpus viduryje. Iš eksportų dažnai ateina ir nepertraukiamas tarpas (CHAR(160)), kurio TRIM nepašalina, todėl patikimiau:
=TRIM(SUBSTITUTE(A2;CHAR(160);" "))
Jei tekste yra eilutės lūžių ar kitų nespausdinamų simbolių, pridėkite CLEAN:
=TRIM(CLEAN(SUBSTITUTE(A2;CHAR(160);" ")))
Didžiųjų ir mažųjų raidžių paieškos funkcijos neskiria, bet ataskaitoje jos atrodo netvarkingai. UPPER, LOWER ir PROPER suvienodina rašybą.
4. Kelios reikšmės viename stulpelyje
Dažnas atvejis: „Jonas Jonaitis, Kaunas“ viename langelyje arba prekės kodas ir pavadinimas kartu. Atskirti galima trimis būdais:
- Text to Columns (Data → Text to Columns): nurodote skirtuką, pavyzdžiui, kablelį ar tarpą, ir Excel padalija stulpelį.
- Flash Fill: šalia įrašykite, ką norite gauti iš pirmos eilutės, pereikite į kitą ir paspauskite Ctrl+E. Excel atpažįsta šabloną ir užpildo likusias eilutes. Patikrinkite kelias eilutes, nes šabloną jis kartais supranta ne taip.
- TEXTSPLIT (Microsoft 365):
=TEXTSPLIT(A2;", ")padalija tekstą į kelis stulpelius formule, todėl rezultatas atsinaujina pasikeitus šaltiniui.
5. Skaičiai ir datos kaip tekstas
Jei sumų stulpelis lygiuotas kairėje, o SUM rodo nulį, skaičiai išsaugoti kaip tekstas. Tai dažniausia eksportų problema, ir jai skyriau atskirą straipsnį: Excel skaičiai ir datos kaip tekstas.
6. Tas pats dalykas, užrašytas skirtingai
„Kaunas“, „KAUNAS“, „Kauno m.“ ir „Kaunas “ suvestinėje bus keturios eilutės. Po tarpų sutvarkymo dalis jų sutaps, o likusius variantus greičiausiai pamatysite su UNIQUE ar suvestine ir pakeisite per Ctrl+H. Kaip surasti pasikartojančius įrašus, aprašiau straipsnyje kaip rasti ir pašalinti dublikatus. Jei reikia tik išvalyti sąrašą, dublikatų šalinimo įrankis tai padaro be formulių ir nekreipia dėmesio į tarpus bei raidžių dydį.
7. Paverskite duomenis lentele
Kai duomenys sutvarkyti, pažymėkite juos ir paspauskite Ctrl+T. Lentelė pati išsiplečia pridėjus eilučių, formulės persikelia į naujas eilutes, o suvestinės ir sąrašai, nurodantys į lentelę, neišsibarsto. Kad tos pačios klaidos neatsirastų iš naujo, pridėkite duomenų tikrinimą.
Kai tą patį tenka daryti kas savaitę
Jei kiekvieną pirmadienį gaunate tą patį eksportą ir kartojate tuos pačius žingsnius, juos verta įrašyti Power Query. Jame galima pašalinti tarpus, padalyti stulpelius, užpildyti tuščius langelius žemyn (Fill Down) ir pakeisti tipus, o kitą savaitę užtenka paspausti Refresh. Kaip tai veikia, aprašiau straipsnyje Power Query: ataskaita, kuri atsinaujina vienu paspaudimu.